Là một kỹ sư, có lẽ bạn đang sử dụng Excel hầu như hàng ngày. Bạn đang ở trong ngành nào không quan trọng; Excel được sử dụng ở MỌI TÁC VỤ trong kỹ thuật.
Excel là một chương trình khổng lồ với nhiều tiềm năng lớn, nhưng làm cách nào để biết liệu bạn có đang sử dụng nó với khả năng tối đa hay không? 9 mẹo này sẽ giúp bạn bắt đầu khai thác tối đa Excel dành cho kỹ thuật.
1. Chuyển đổi đơn vị mà không cần công cụ bên ngoài
Nếu bạn giống tôi, bạn có thể làm việc với các đơn vị khác nhau hàng ngày. Đó là một trong những khó chịu lớn của đời sống kỹ thuật. Tuy nhiên, nó trở nên ít khó chịu hơn nhiều nhờ một hàm trong Excel có thể thực hiện công việc khó khăn cho bạn: CONVERT. Cú pháp của nó là:
CONVERT(number, from_unit, to_unit)
Trong đó number là giá trị mà bạn muốn chuyển đổi, from_unit là đơn vị của number và to_unit là đơn vị kết quả bạn muốn lấy.
Giờ đây, bạn sẽ không còn phải sử dụng các công cụ bên ngoài để tìm các yếu tố chuyển đổi hoặc viết các yếu tố khó vào bảng tính của mình để gây nhầm lẫn sau này. Chỉ cần để chức năng CHUYỂN ĐỔI thực hiện công việc cho bạn.
Bạn sẽ tìm thấy danh sách đầy đủ các đơn vị cơ sở mà Excel công nhận là “from_unit” và “to_unit” tại đây (cảnh báo: không phải tất cả các đơn vị đều có sẵn trong các phiên bản Excel trước), nhưng bạn cũng có thể sử dụng hàm nhiều lần để chuyển đổi phức tạp hơn các đơn vị phổ biến trong kỹ thuật.
2. Sử dụng vùng được Đặt tên để Tạo Công thức Dễ hiểu hơn
Kỹ thuật là đủ thách thức, mà không cần cố gắng tìm ra phương trình như (G15 + $C$4) / F9-H2 có nghĩa là gì. Để loại bỏ vấn đề liên quan đến tham chiếu ô Excel, hãy sử dụng vùng được đặt tên để tạo các biến mà bạn có thể sử dụng trong công thức của mình.
Chúng không chỉ giúp nhập công thức vào bảng tính dễ dàng hơn mà còn giúp hiểu các công thức dễ dàng hơn RẤT NHIỀU khi bạn hoặc ai đó mở bảng tính sau đó vài tuần, vài tháng hoặc nhiều năm.
Có một số cách khác nhau để tạo vùng được đặt tên, nhưng hai cách này là cách tôi yêu thích nhất
1. Đối với biến “một lần”, hãy chọn ô mà bạn muốn gán tên biến, sau đó nhập tên của biến vào hộp tên ở góc trên bên trái của cửa sổ (bên dưới ruy-băng) như hình trên.
2. Nếu bạn muốn gán biến cho nhiều tên cùng một lúc và đã bao gồm tên biến trong cột hoặc hàng bên cạnh ô chứa giá trị, hãy thực hiện điều này: Đầu tiên, chọn ô chứa tên và ô bạn muốn để gán tên. Sau đó, điều hướng đến Formulas> Defined Names> Create from Selection. Nếu bạn muốn tìm hiểu thêm, bạn có thể đọc tất cả về cách tạo vùng được đặt tên từ các lựa chọn tại đây.
3. Tự động cập nhật Biểu đồ với Tiêu đề, Trục và nhãn động
Để dễ dàng cập nhật tiêu đề biểu đồ, tiêu đề trục và nhãn, bạn có thể liên kết chúng trực tiếp với các ô. Nếu bạn cần tạo nhiều biểu đồ, đây có thể là một công cụ tiết kiệm thời gian thực và cũng có thể giúp bạn tránh lỗi khi quên cập nhật tiêu đề biểu đồ.
Để cập nhật tiêu đề, trục hoặc nhãn biểu đồ, trước tiên hãy tạo văn bản mà bạn muốn đưa vào một ô trên trang tính. Bạn có thể sử dụng hàm CONCATENATE để tập hợp các chuỗi văn bản và giá trị ô số thành các tiêu đề phức tạp.
Tiếp theo, chọn thành phần trên biểu đồ. Sau đó, chuyển đến thanh công thức và nhập “=” và chọn ô chứa văn bản bạn muốn sử dụng.
Bây giờ, thành phần biểu đồ sẽ tự động cập nhật khi giá trị ô thay đổi. Bạn có thể thỏa sức sáng tạo tại đây và kéo tất cả các loại thông tin vào biểu đồ mà không phải lo lắng về việc cập nhật biểu đồ sau này. Tất cả đều được thực hiện tự động!
4. Đạt mục tiêu với Goal Seek
Thông thường, chúng tôi thiết lập bảng tính để tính toán một kết quả từ một loạt các giá trị đầu vào. Nhưng điều gì sẽ xảy ra nếu bạn đã thực hiện việc này trong bảng tính và muốn biết giá trị đầu vào nào sẽ đạt được kết quả mong muốn?
Bạn có thể sắp xếp lại các phương trình và đặt kết quả cũ là đầu vào mới và đầu vào cũ là kết quả mới. Bạn cũng có thể chỉ đoán ở đầu vào cho đến khi đạt được kết quả mục tiêu.
May mắn thay, cả hai đều không cần thiết vì Excel có một công cụ gọi là Goal Seek để thực hiện công việc đó cho bạn.
Đầu tiên, hãy mở công cụ Goal Seek: Data>Forecast>What-If Analysis>Goal Seek.
Trong mục nhập liệu ô “Set Cell:”, hãy chọn ô kết quả mục tiêu. Trong ô “To value:”, hãy nhập giá trị mục tiêu.
Cuối cùng, trong ô “By changing cell:” chọn đầu vào duy nhất mà bạn muốn sửa đổi để thay đổi kết quả. Chọn OK và Excel lặp lại để tìm đầu vào chính xác để đạt được mục tiêu.
5. Bảng dữ liệu tham chiếu trong tính toán
Một trong những điều khiến Excel trở thành một công cụ kỹ thuật tuyệt vời là nó có khả năng xử lý cả phương trình và bảng dữ liệu. Và bạn có thể kết hợp hai chức năng này để tạo ra các mô hình kỹ thuật mạnh mẽ bằng cách tra cứu dữ liệu từ các bảng và đưa nó vào tính toán.
Có thể bạn đã quen thuộc với các hàm tra cứu VLOOKUP và HLOOKUP. Trong nhiều trường hợp, nó có thể làm mọi thứ bạn cần.
Tuy nhiên, nếu bạn cần linh hoạt hơn và kiểm soát tốt hơn các tra cứu của mình, hãy sử dụng INDEX và MATCH để thay thế. Hai hàm này cho phép bạn tra cứu dữ liệu trong bất kỳ cột hoặc hàng nào của bảng (không chỉ ở cột đầu tiên) và bạn có thể kiểm soát xem liệu giá trị trả về là lớn nhất hay nhỏ nhất tiếp theo không.
Bạn cũng có thể sử dụng INDEX và MATCH để thực hiện nội suy tuyến tính trên một tập dữ liệu. Điều này được thực hiện bằng cách tận dụng tính linh hoạt của phương pháp tra cứu này để tìm các giá trị x- và y ngay trước và sau giá trị x mục tiêu.
6. Điều chỉnh chính xác các phương trình phù hợp với dữ liệu
Một cách khác để sử dụng dữ liệu hiện có trong phép tính là kết hợp một phương trình với dữ liệu đó và sử dụng phương trình để xác định giá trị y cho một giá trị nhất định của x.
Nhiều người biết cách trích xuất một phương trình từ dữ liệu bằng cách vẽ nó trên biểu đồ phân tán và thêm một đường xu hướng. Điều đó có thể chấp nhận được nếu bạn nhận được một phương trình nhanh và dễ hiểu hoặc hiểu loại chức năng nào phù hợp nhất với dữ liệu.
Tuy nhiên, nếu bạn muốn sử dụng phương trình đó trong bảng tính của mình, bạn cần phải nhập nó theo cách thủ công. Điều này có thể dẫn đến lỗi do chính tả hoặc quên cập nhật phương trình khi dữ liệu bị thay đổi.
Cách tốt hơn để lấy phương trình là sử dụng hàm LINEST. Đây là một hàm mảng trả về các hệ số (m và b) xác định dòng phù hợp nhất thông qua một tập dữ liệu. Cú pháp của nó là:
LINEST(known_y’s, [known_x’s], [const], [stats])
Trong đó:
• known_y’s là mảng giá trị y trong dữ liệu của bạn,
• known_x’s là mảng các giá trị x,
• const là một giá trị logic cho Excel biết liệu có buộc chặn y bằng 0 hay không, và
• stats chỉ định có trả về thống kê hồi quy, chẳng hạn như R bình phương, v.v..
LINEST có thể được mở rộng ra ngoài các tập dữ liệu tuyến tính để thực hiện hồi quy phi tuyến tính trên dữ liệu phù hợp với các hàm đa thức, hàm mũ, logarit và lũy thừa. Nó thậm chí có thể được sử dụng cho nhiều hồi quy tuyến tính.
7. Tiết kiệm thời gian với các chức năng do người dùng định nghĩa
Excel có nhiều hàm tích hợp theo ý của bạn theo mặc định. Tuy nhiên, nếu bạn giống tôi, có nhiều phép tính bạn phải thực hiện lặp đi lặp lại mà không có một hàm cụ thể trong Excel.
Đây là những tình huống hoàn hảo để tạo một Hàm do Người dùng Xác định (UDF) trong Excel bằng Visual Basic for Applications hoặc VBA, ngôn ngữ lập trình tích hợp cho các sản phẩm Office.
Tuy nhiên, đừng lo lắng khi bạn đọc “lập trình”. Tôi KHÔNG PHẢI là lập trình viên, nhưng tôi sử dụng VBA mọi lúc để mở rộng khả năng của Excel và tiết kiệm thời gian cho bản thân.
Nếu bạn muốn tìm hiểu cách tạo các Hàm do Người dùng định nghĩa và mở khóa tiềm năng to lớn của Excel với VBA, hãy nhấp vào đây để đọc về cách tôi tạo một UDF từ đầu để tính toán ứng suất uốn.
8. Thực hiện các phép toán tính toán
Khi bạn nghĩ về Excel, bạn có thể không nghĩ đến “giải tích” “. Nhưng nếu bạn có bảng dữ liệu, bạn có thể sử dụng phương pháp phân tích số để tính đạo hàm hoặc tích phân của dữ liệu đó.
Các phương pháp cơ bản tương tự này được sử dụng bởi phần mềm kỹ thuật phức tạp hơn để thực hiện các thao tác này và chúng rất dễ sao chép trong Excel.
Để tính toán các công cụ phái sinh, bạn có thể sử dụng chênh lệch tiến, lùi hoặc chênh lệch trung tâm. Mỗi phương pháp này sử dụng dữ liệu từ bảng để tính toán dy / dx, điểm khác biệt duy nhất là điểm dữ liệu nào được sử dụng để tính toán.
• Đối với sự khác biệt tiến, hãy sử dụng dữ liệu tại điểm n và n + 1
• Đối với các khác biệt lùi, sử dụng dữ liệu tại điểm n và n-1
• Đối với sự khác biệt trung tâm, sử dụng n-1 và n + 1, như được hiển thị bên dưới
Nếu bạn cần tích hợp dữ liệu trong bảng tính, quy tắc hình thang sẽ hoạt động tốt. Phương pháp này tính diện tích dưới đường cong giữa xn và xn + 1. Nếu yn và yn + 1 là các giá trị khác nhau, thì khu vực này sẽ tạo thành một hình thang, do đó có tên như vậy.
9. Khắc phục sự cố Bảng tính bị lỗi với Công cụ Kiểm toán Excel
Mọi kỹ sư đều được thừa hưởng một bảng tính “bị hỏng”. Nếu đó là của đồng nghiệp, bạn luôn có thể yêu cầu họ sửa và gửi lại. Nhưng điều gì sẽ xảy ra nếu bảng tính đến từ sếp của bạn, hoặc tệ hơn là một người không còn ở công ty?
Đôi khi, đây có thể là một cơn ác mộng thực sự, nhưng Excel cung cấp một số công cụ có thể giúp bạn điều chỉnh bảng tính hoạt động sai. Mỗi công cụ này có thể được tìm thấy trong tab Formulas của ribbob, trong phần Kiểm tra Công thức:
Như bạn có thể thấy, có một vài công cụ khác nhau ở đây. Tôi sẽ giải thích hai trong số đó.
Đầu tiên, bạn có thể sử dụng Trace Dependents để định vị các đầu vào cho ô đã chọn. Điều này có thể giúp bạn theo dõi tất cả các giá trị đầu vào đến từ đâu, nếu nó không rõ ràng.
Nhiều lần, điều này có thể dẫn bạn đến nguồn gốc của lỗi. Khi bạn hoàn tất, hãy nhấp vào xóa mũi tên để xóa các mũi tên khỏi bảng tính của bạn.
Bạn cũng có thể sử dụng công cụ Đánh giá Công thức để tính toán kết quả của một ô – từng bước một. Điều này hữu ích cho tất cả các công thức, nhưng đặc biệt đối với những công thức có chứa hàm logic hoặc nhiều hàm lồng nhau:
Xem thêm
[Excel tips] Cách gửi email trực tiếp từ Excel bằng hàm Hyperlink
Excel Tips: Các công cụ phân tích dữ liệu thông dụng trong Excel
