Điều gì xảy ra khi bạn đã tạo các công thức và sau đó tạo một dải ô đã đặt tên mà bạn muốn sử dụng trong số đó? Không có gì khó thực sự ở đây, Excel sẽ không thực hiện bất kỳ thay đổi nào đối với các công thức hiện có của bạn hoặc đề nghị tự động áp dụng các tên phạm vi mới. Tuy nhiên, có một cách để áp dụng tên phạm vi cho các công thức hiện có.
Chỉ cần chọn công thức bạn muốn áp dụng tên, sau đó sử dụng tính năng Apply Names. Khi cửa sổ Apply Names mở ra, hãy chọn tên bạn muốn áp dụng và bấm nút OK. Excel sẽ thay thế mọi tham chiếu tương ứng bằng tên bạn đã chọn.
Lưu một công thức chưa hoàn thành
Nếu bạn đang làm việc với một công thức phức tạp hơn, rất có thể sẽ mất một lúc để công thức hoạt động đúng. Nhưng bạn có thể thiếu thời gian và cần phải quay lại công thức sau để làm cho nó hoạt động như bạn muốn. Thật không may, Excel sẽ không cho phép bạn nhập công thức đang làm dở dang. Nếu bỏ dở, Excel sẽ báo lỗi và sẽ không cho phép bạn tiếp tục cho đến khi giải quyết xong mọi vấn đề.
Tuy nhiên, có một cách giải quyết dễ dàng: Chỉ cần tạm thời chuyển đổi công thức thành văn bản, để làm điều này, bạn chỉ cần thêm một dấu nháy đơn vào đầu công thức (trước dấu =), hoặc chỉ cần xóa hoàn toàn dấu bằng. Trong cả hai trường hợp, Excel sẽ ngừng hiển thị thông báo lỗi và cho phép bạn nhập công thức nguyên trạng. Sau đó, bạn có thể quay lại công thức và tiếp tục công việc.
Nhận biết các hàm mà Excel cung cấp
Các hàm tồn tại để giải quyết các vấn đề cụ thể. Bạn có thể coi một hàm như một công thức được tạo sẵn với một tên cụ thể, mục đích và giá trị trả về. Ví dụ, hàm PROPER chỉ có một mục đích: nó viết hoa các từ. Khi nhập vào hàm trên nội dung văn bản như "nEW york CiTY" và hàm này sẽ trả về kết quả là "New York City". Các hàm cực kỳ hữu ích khi chúng giải quyết một vấn đề mà bạn gặp phải, vì vậy nên tự làm quen với các hàm có sẵn trong Excel.
Lưu ý: Mọi người thường bị nhầm lẫn bởi các thuật ngữ được sử dụng để nói về các hàm và công thức. Một cách dễ dàng để phân biệt đó là: mọi thứ bắt đầu bằng dấu bằng trong Excel đều là công thức. Theo định nghĩa này, tất cả các hàm cũng là công thức và công thức có thể chứa nhiều hàm.
Sử dụng F4 để chuyển đổi các tham chiếu tương đối và tuyệt đối
Chìa khóa để xây dựng các công thức mà bạn có thể sao chép sang các vị trí mới và vẫn hoạt động chính xác, đó là sử dụng kết hợp đúng các tham chiếu tuyệt đối và tương đối. Lý do điều này rất quan trọng là vì nó cho phép bạn sử dụng lại các công thức hiện có thay vì tạo công thức mới và việc sử dụng lại cùng một công thức làm giảm đáng kể khả năng xảy ra lỗi trong sổ làm việc bằng cách giới hạn số lượng công thức cần được kiểm tra.
Tuy nhiên, việc chuyển đổi các tham chiếu qua lại giữa các tham chiếu tương đối và tuyệt đối có thể gây phiền toái, nhất là việc nhập vào tất cả các ký hiệu đô la ($) rất mất thời gian và dễ xảy ra lỗi. Tuy nhiên có một phím tắt cho phép bạn nhanh chóng chuyển đổi qua 4 tùy chọn có sẵn cho mỗi tham chiếu: (Windows: F4, Mac: Command + T).
Chỉ cần đặt con trỏ vào tham chiếu bằng phím tắt. Mỗi lần bạn sử dụng nó, Excel sẽ "xoay" sang tùy chọn tiếp theo theo thứ tự sau: Hoàn toàn tương đối (A1)> hoàn toàn tuyệt đối ($A$ 1)> Hàng tuyệt đối (A$1)> cột tuyệt đối ($A1).
Hãy nhớ rằng các công thức và hàm trả về cùng một giá trị
Khi bạn làm việc với một công thức, đôi khi là do bạn nghĩ rằng một phần của công thức đang trả về một giá trị nhất định nhưng thực tế nó đang trả về một giá trị khác. Để kiểm tra xem những gì thực sự được trả về bởi một hàm hoặc một phần của công thức, hãy sử dụng mẹo F9 bên dưới.
Sử dụng F9 để đánh giá các phần của công thức
Phím tắt F9 (Fn + F9 trên máy Mac) có thể giải các phần của công thức trong thời gian thực. Đây là một công cụ tuyệt vời để gỡ lỗi các công thức lớn hơn, khi bạn cần xác minh rằng kết quả của một phần cụ thể của công thức là những gì bạn đang mong đợi.
Để sử dụng mẹo này, hãy chỉnh sửa công thức và chọn biểu thức hoặc hàm bạn muốn đánh giá. Sau đó nhấn F9 sẽ thấy rằng một phần của công thức được thay thế bằng giá trị mà nó trả về.
Khi việc sử dụng F9 để đánh giá một công thức trở nên quá tẻ nhạt, đã đến lúc bạn nên thử một giải pháp lớn hơn đó là Đánh giá công thức (Evaluate Formula). Đánh giá công thức giải quyết một công thức từng bước một, mỗi lần bạn bấm vào nút Evaluate, Excel sẽ giải quyết phần gạch chân của công thức và hiển thị cho bạn kết quả.
Bạn có thể tìm thấy tính năng Evaluate Formula trên thanh công cụ ribbon, trong nhóm Evaluate Formula. Để sử dụng Evaluate Formula, hãy chọn một công thức và bấm vào nút này. Khi cửa sổ mở ra, bạn sẽ thấy công thức được hiển thị trong một hộp văn bản có nút Evaluate bên dưới.
Một phần của công thức sẽ được gạch chân - đây là phần hiện đang được "đánh giá". Khi bạn bấm vào Evaluate, phần gạch chân của công thức sẽ được thay thế bằng giá trị mà nó trả về. Bạn có thể tiếp tục kích vào Evaluate cho đến khi công thức được giải quyết hoàn toàn.
Lưu ý: Đánh giá công thức chỉ có sẵn trong phiên bản Excel cho Windows. Phiên bản dành cho máy Mac có một cách tiếp cận khác, được gọi là Formula Builder sẽ hiển thị kết quả khi bạn tạo công thức. Nó không hoàn toàn giống chức năng, nhưng vẫn rất hữu ích và bạn có thể sử dụng với các công thức đã tồn tại.
Xây dựng các công thức phức tạp theo từng bước nhỏ
Khi bạn cần xây dựng một công thức phức tạp hơn và không chắc chắn về cách thực hiện, hãy bắt đầu với cách tiếp cận chung và một số giá trị được mã hóa cứng. Sau đó, thêm nhiều logic hơn để thay thế các giá trị được mã hóa cứng từng bước một.
Ví dụ: Giả sử bạn muốn viết công thức chiết xuất tên đầu tiên từ tên đầy đủ. Chắc chắn có thể sử dụng hàm LEFT, để kéo văn bản từ bên trái, nhưng lại không chắc chắn cách tính số ký tự cần trích xuất. Bắt đầu với LEFT (full_name, 5) để công thức hoạt động.
Sau đó, tìm cách thay thế số 5 bằng một giá trị được tính toán. Trong trường hợp này, bạn có thể tính ra số lượng ký tự cần trích xuất bằng cách sử dụng hàm FIND để xác định vị trí của ký tự khoảng trắng đầu tiên.
Sử dụng các phạm vi được đặt tên như biến
Trong nhiều trường hợp, bạn nên sử dụng các phạm vi được đặt tên như biến để làm cho công thức linh hoạt hơn và dễ làm việc hơn. Ví dụ: nếu đang thực hiện nhiều phép nối (nối các giá trị văn bản với nhau), bạn có thể muốn tạo các phạm vi được đặt tên của riêng mình cho các ký tự dòng, ký tự tab mới …
Bằng cách này, bạn chỉ cần tham khảo trực tiếp các phạm vi đã đặt tên thay vì thêm nhiều cú pháp phức tạp vào công thức. Nếu các phạm vi được đặt tên của bạn chứa các giá trị văn bản thì không cần sử dụng bất kỳ dấu ngoặc kép nào xung quanh văn bản khi thêm chúng vào công thức.
Sử dụng cách ghép nối trong các nhãn để làm rõ các giả định
Khi bạn tạo một trang tính dựa trên các giả định nhất định, có thể là một thách thức để thể hiện rõ ràng các giả định bạn đang đưa ra. Thông thường, bạn sẽ có một khu vực nhất định trên trang tính cho đầu vào và một khu vực khác cho đầu ra và không có chỗ để hiển thị cả hai cùng một lúc.
Một cách để đảm bảo các giả định chính là rõ ràng là nhúng chúng trực tiếp vào các nhãn xuất hiện trên trang tính bằng cách sử dụng nối, thường là với hàm TEXT.
Thêm ngắt dòng vào hàm IF lồng nhau để dễ đọc hơn
Khi bạn đang tạo công thức với hàm IF lồng nhau, việc theo dõi các đối số đúng và sai trong dấu ngoặc đơn có thể rất khó hiểu. Khi sử dụng các dấu ngoặc đơn, bạn rất dễ mắc sai lầm về logic. Tuy nhiên, có một cách đơn giản giúp công thức có nhiều câu lệnh IF dễ đọc hơn nhiều: Đó là chỉ cần thêm dấu ngắt dòng vào công thức sau mỗi đối số TRUE. Điều này sẽ làm cho công thức đọc giống như một bảng hơn.
Nhập các hàm với tính năng AutoComplete
Khi nhập một hàm, Excel sẽ cố gắng đoán tên của hàm mà bạn muốn và hiển thị danh sách tự động (AutoComplete) điền để chọn. Vậy làm thế nào để bạn chấp nhận một trong các tùy chọn được hiển thị mà vẫn ở chế độ chỉnh sửa? Bí quyết là sử dụng phím tab.
Khi nhấn tab, Excel sẽ thêm hàm hoàn chỉnh và để con trỏ hoạt động trong dấu ngoặc đơn để bạn có thể điền các đối số khi cần. Trên máy Mac, trước tiên bạn cần sử dụng phím mũi tên xuống để chọn hàm muốn thêm, sau đó nhấn Tab để chèn chức năng.
Sử dụng AutoSum để nhập các công thức SUM
Tính năng tự động cộng gộp (AutoSum) hoạt động cho cả hàng và cột. Chỉ cần chọn một ô trống ở bên phải hoặc bên dưới các ô bạn muốn tính tổng và nhập tổ hợp phím Alt + = (Mac: Command + Shift + T). Excel sẽ đoán phạm vi mà bạn đang cố gắng tính tổng và chèn hàm SUM vào để tính tổng. Nếu bạn muốn cụ thể hơn, trước tiên hãy chọn phạm vi định tính tổng, bao gồm ô mà bạn muốn hàm SUM ở đó.
AutoSum thậm chí sẽ chèn nhiều hàm SUM cùng một lúc. Để tính tổng nhiều cột, hãy chọn một dải ô trống bên dưới các cột. Để tính tổng nhiều hàng, hãy chọn một dải ô trống trong cột bên phải các hàng.
Cuối cùng, bạn có thể sử dụng AutoSum để thêm cả tổng hàng và cột cùng một lúc cho toàn bộ bảng. Chỉ cần chọn một bảng đầy đủ các số, bao gồm các ô trống bên dưới bảng và ở bên phải bảng, và sử dụng phím tắt. Excel sẽ thêm các hàm SUM thích hợp vào các ô trống, cung cấp cho bạn tổng cột, tổng hàng và tổng số trong một bước duy nhất.
Nhập cùng một công thức vào nhiều ô cùng một lúc
Thông thường, bạn sẽ cần nhập cùng một công thức vào một nhóm ô và để thực hiện chỉ cần bấm tổ hợp phím tắt Control + Enter. Chỉ cần chọn tất cả các ô cùng một lúc, sau đó nhập công thức bình thường như cách bạn làm cho ô đầu tiên. Sau khi hoàn tất, thay vì nhấn Enter hãy nhấn Control + Enter. Excel sẽ thêm cùng một công thức vào tất cả các ô trong vùng chọn, điều chỉnh các tham chiếu nếu cần.
Khi bạn sử dụng phương pháp này thì không cần phải sao chép, dán hay điền công thức vào các hàng thủ công như trước. Bạn cũng có thể sử dụng kỹ thuật tương tự này để chỉnh sửa nhiều công thức cùng một lúc.
Chỉ cần chọn tất cả các công thức cùng một lúc, thực hiện thay đổi bạn cần và nhấn Control + Enter.
Trên đây Gitiho vừa giới thiệu với các bạn 29 cách làm việc với Excel nhanh hơn và dễ dàng hơn, giúp tiết kiệm tối đa thời gian khi bạn phải làm việc với bảng dữ liệu Excel lớn. Nếu thấy bài viết hay và bổ ích hãy chia sẻ với bạn bè của mình nhé. Chúc các bạn thành công!.