29 cách tiết kiệm thời gian tối đa khi làm việc với công thức trong Excel

Nội dung chính

1 Không thêm dấu ngoặc đơn cuối cùng vào một hàm
2 Di chuyển công thức và giữ cho các tham chiếu không thay đổi
3 Sao chép công thức và giữ cho các tham chiếu không thay đổi
4 Kích đúp vào ô công thức để sao chép công thức
5 Sử dụng bảng để nhập công thức tự động
6 Sử dụng tính năng AutoComplete + Tab
7 Sử dụng Control + kích chọn để nhập đối số
8 Sử dụng cửa sổ mẹo công thức (Formula tip) để chọn các đối số
9 Chèn chỗ dành sẵn đối số hàm bằng phím tắt
10 Di chuyển "điều công thức" theo cách của bạn
11 Chuyển đổi hiển thị tất cả các công thức cùng một lúc
12 Chọn tất cả các công thức trong một trang tính cùng một lúc
13 Sử dụng dán đặc biệt để chuyển đổi công thức thành giá trị tĩnh
14 Sử dụng Paste Special để điều chỉnh các giá trị tại chỗ
15 Sử dụng các phạm vi đã đặt tên để làm cho công thức dễ đọc hơn
16 Tự động áp dụng tên cho các công thức hiện có
17 Lưu một công thức chưa hoàn thành
18 Nhận biết các hàm mà Excel cung cấp
19 Sử dụng F4 để chuyển đổi các tham chiếu tương đối và tuyệt đối
20 Hãy nhớ rằng các công thức và hàm trả về cùng một giá trị
21 Sử dụng F9 để đánh giá các phần của công thức
22 Sử dụng tính năng Formula Evaluator để giải quyết từng bước một công thức
23 Xây dựng các công thức phức tạp theo từng bước nhỏ
24 Sử dụng các phạm vi được đặt tên như biến
25 Sử dụng cách ghép nối trong các nhãn để làm rõ các giả định
26 Thêm ngắt dòng vào hàm IF lồng nhau để dễ đọc hơn
27 Nhập các hàm với tính năng AutoComplete
28 Sử dụng AutoSum để nhập các công thức SUM
29 Nhập cùng một công thức vào nhiều ô cùng một lúc

29 cách tiết kiệm thời gian tối đa khi làm việc với công thức trong Excel

Không thêm dấu ngoặc đơn cuối cùng vào một hàm

Hãy bắt đầu với một cái gì đó rất dễ dàng và đơn giản. Khi bạn tự nhập một hàm (SUM, AVERAGE … ) thì không cần nhập dấu ngoặc đóng cuối cùng. Ví dụ: bạn chỉ cần nhập:

=SUM(A1:A10

và nhấn phím Enter. Excel sẽ tự động thêm dấu ngoặc đóng cho bạn. Đó là một điều nhỏ, nhưng vô cùng tiện lợi.

Lưu ý: Điều này sẽ không hoạt động nếu công thức của bạn chứa nhiều hơn một bộ dấu ngoặc đơn.

Di chuyển công thức và giữ cho các tham chiếu không thay đổi

Một trong những tính năng mạnh mẽ nhất của Excel là địa chỉ tương đối, khi bạn sao chép công thức đến một vị trí mới, tất cả các địa chỉ tương đối sẽ thay đổi. Thường thì đây chính xác là những gì bạn muốn, bởi vì việc sử dụng lại các công thức là một trong những kỹ thuật quan trọng nhất giúp cho công việc của bạn nhanh và dễ dàng hơn nếu không có gì thay đổi.

Nhưng đôi khi bạn cần di chuyển hoặc sao chép công thức đến một vị trí mới và không muốn công thức thay đổi chút nào. Có một số cách bạn có thể làm điều này.

Nếu bạn chỉ di chuyển một công thức đến một vị trí gần đó, hãy thử kéo và thả. Kéo sẽ giữ nguyên tất cả các địa chỉ và không thay đổi.

Nếu bạn di chuyển công thức đến một vị trí xa hơn, hãy sử dụng tính năng cắt và dán. (với máy tính chạy Windows sử dụng tổ hợp phím: Ctrl + X và Ctrl + V. Với máy Mac là: Cmd + X và Cmd + V). Khi bạn cắt công thức, các tham chiếu của nó không thay đổi.

Sao chép công thức và giữ cho các tham chiếu không thay đổi

Lưu ý: bạn có thể thay đổi tất cả các tham chiếu ô để làm cho chúng tuyệt đối, nhưng mẹo này giả định rằng bạn không muốn làm điều đó, vì bất kỳ lý do gì.

Nếu bạn chỉ cần sao chép một công thức, hãy chọn toàn bộ công thức trong thanh công thức và sao chép vào bộ nhớ tạm Clipboard. Sau đó, có thể dán tại một vị trí mới, kết quả sẽ là một công thức giống hệt với công thức ban đầu.

Để sao chép một nhóm công thức đến một vị trí mới mà không ảnh hưởng đến các tham chiếu, bạn có thể sử dụng chức năng Find and Replace. Chọn công thức bạn muốn sao chép, sau đó tìm kiếm và thay thế dấu bằng (=) trong công thức bằng ký tự thăng (#).

Điều này sẽ chuyển đổi các công thức thành văn bản, bây giờ sao chép và dán các công thức vào một vị trí mới. Sau khi dán các công thức và với tất cả các công thức đã chọn, hãy đảo ngược quá trình tìm kiếm và thay thế. Tìm kiếm ký tự thăng (#) và thay thế dấu bằng (=) để khôi phục các công thức về đúng quy luật làm việc của nó.

Kích đúp vào ô công thức để sao chép công thức

Khi bạn thêm công thức vào bảng, một trong những việc bạn thường làm nhất là sao chép công thức từ hàng đầu tiên của bảng xuống hàng cuối cùng của bảng. Nếu bạn biết các phím tắt để điều hướng dữ liệu trong Excel, có thể sử dụng chúng để nhanh chóng dán toàn bộ cột công thức chỉ trong một vài thao tác gõ phím.

Tuy nhiên Excel vẫn có một cách khác để thực hiện nhanh hơn mà không cần phải điều hướng đến cuối bảng. Tại vị trí ô công thức bất kỳ, bạn sẽ thấy một hình chữ nhật nhỏ nằm ở góc dưới bên phải của ô được lựa chọn trong Excel. Miễn là công thức nằm trong cột đó, bạn chỉ cần kích đúp chuột vào ô điều khiển để sao chép toàn bộ công thức xuống cuối bảng.

Lưu ý: Cách này sẽ không hoạt động nếu không có cột dữ liệu đầy đủ ở bên trái của công thức bạn đang nhập. Excel sử dụng dữ liệu này để tìm ra khoảng cách trang tính cần sao chép công thức. Tuy nhiên, bạn có thể sử dụng phương pháp tiếp theo này ...

Nếu vùng chọn không quá lớn, bạn cũng có thể điền các công thức xuống cuối trang tính bằng cách sử dụng phím tắt của tính năng Fill Down (Control + D). Chỉ cần đảm bảo chọn công thức gốc và các ô đích trước. Điều này nhanh hơn thao tác sao chép/dán.

Sử dụng bảng để nhập công thức tự động

Một cách nhanh hơn nữa để nhập công thức là trước tiên chuyển đổi bảng của bạn thành một bảng Excel chính thức. Nghe có vẻ hơi khó hiểu, vì bất kỳ dữ liệu nào có nhiều hơn một cột về mặt kỹ thuật là một "bảng" nhưng Excel có cấu trúc chính thức được gọi là Bảng (Tablet) với nhiều lựa chọn hơn.

Sau khi bạn chuyển đổi dữ liệu của mình sang bảng (Cả Windows và Mac đều bấm tổ hợp phím: Ctrl + T), tất cả các công thức bạn nhập vào hàng đầu tiên sẽ được tự động sao chép toàn bộ chiều dài của bảng. Điều này tiết kiệm rất nhiều thời gian và cũng giúp ngăn ngừa sai sót.

Như vậy, khi bạn cập nhật một công thức trong một bảng, Excel sẽ cập nhật lại tất cả các công thức tương tự trong cùng một cột.

Lưu ý: Các công thức trong bảng sẽ tự động sử dụng tham chiếu có cấu trúc (nghĩa là trong ví dụ trên =[@ Hours]*[@ Rate] thay vì = C4*D4. Tuy nhiên, bạn có thể nhập trực tiếp địa chỉ ô và chúng sẽ được giữ nguyên.

Sử dụng tính năng AutoComplete + Tab

Khi bạn nhập một dấu bằng và bắt đầu nhập, Excel sẽ bắt đầu khớp văn bản bạn nhập với danh sách khổng lồ các hàm có sẵn. Khi nhập, bạn sẽ thấy danh sách các hàm "ứng cử viên" xuất hiện bên dưới. Danh sách này sẽ thu hẹp với mỗi chữ cái bạn nhập. Khi chức năng bạn muốn được chọn trong danh sách, bạn có thể "yêu cầu" Excel nhập hàm đó cho bạn bằng cách nhấn phím tab.

Trên Windows, các chức năng được chọn tự động khi bạn nhập. Trên máy Mac, các tùy chọn được hiển thị nhưng không được chọn, vì vậy bạn cần thực hiện thêm một bước: đó là sử dụng phím mũi tên để chọn chức năng bạn muốn, sau đó bấm phím Tab hoặc Enter để Excel nhập hàm cho bạn.

Sử dụng Control + kích chọn để nhập đối số

Nếu bạn không thích gõ dấu phẩy giữa các đối số? Excel có thể làm điều đó cho bạn. Khi đang nhập đối số trong một hàm, chỉ cần giữ Control (Máy Mac bấm phím: Command) khi kích vào từng tham chiếu và Excel sẽ tự động nhập dấu phẩy cho bạn.

Ví dụ: bạn có thể nhập công thức như: = SUM(A1,B10,C5:C10) bằng cách nhập "= SUM("rồi nhấn Control khi nhấp vào từng tham chiếu). Điều này sẽ hoạt động với bất kỳ hàm nào bạn đang cung cấp tham chiếu làm đối số.

Sử dụng cửa sổ mẹo công thức (Formula tip) để chọn các đối số

Bất cứ khi nào bạn đang làm việc với một công thức có chứa một hàm Excel, hãy nhớ rằng bạn luôn có thể sử dụng cửa sổ gợi ý để chọn các đối số. Điều này có thể tiết kiệm thời gian nếu công thức phức tạp, đặc biệt nếu nó chứa nhiều dấu ngoặc đơn lồng nhau.

Để chọn đối số, hãy làm theo hai bước. Trong bước một, bấm để đặt con trỏ bên trong hàm có đối số mà bạn muốn chọn. Sau đó, Excel sẽ hiển thị một gợi ý cho hàm đó hiển thị tất cả các đối số. Trong bước hai, hãy nhấp vào đối số bạn muốn chọn. Excel sẽ chọn toàn bộ đối số, ngay cả khi nó chứa các hàm hoặc công thức khác. Đây là một cách hay để chọn đối số khi sử dụng F9 để gỡ lỗi công thức.

Chèn chỗ dành sẵn đối số hàm bằng phím tắt

Thông thường, khi bạn nhập một hàm, Excel sẽ hiển thị các mẹo về từng đối số khi bạn thêm dấu phẩy. Nhưng đôi khi bạn có thể muốn Excel thêm chỗ dành sẵn cho tất cả các đối số của hàm cùng một lúc. Nếu vậy, bạn sẽ rất vui khi biết rằng có một phím tắt cho điều đó.

Khi đang nhập một hàm, sau khi Excel đã nhận ra tên hàm, hãy nhấn tổ hợp phím Control + Shift + A (cả hai nền tảng). Ví dụ: nếu bạn nhập "=DATE("và sau đó sử dụng Control + Shift + A, Excel sẽ cung cấp cho bạn "= DATE (năm, tháng, ngày)". Sau đó, bạn có thể bấm đúp vào từng đối số (hoặc sử dụng mẹo Hàm để chọn từng đối số) và thay đổi nó thành giá trị bạn muốn.

Di chuyển "điều công thức" theo cách của bạn

Đôi khi khi bạn đang nhập một công thức, cửa sổ gợi ý công thức hiển thị sẽ chặn chế độ xem đối với các ô khác mà bạn muốn xem trên trang tính. Khi điều đó xảy ra, hãy nhớ rằng bạn có thể di chuyển cửa sổ gợi ý theo cách của mình.

Chỉ cần di chuột qua cạnh cửa sổ cho đến khi bạn thấy con trỏ thay đổi, sau đó nhấp và kéo đến vị trí mới. Sau đó, có thể tiếp tục nhập hoặc chỉnh sửa công thức của mình.

Tùy thuộc vào cấu trúc trang tính của bạn, một cách khác để quản lý vấn đề này là chỉnh sửa công thức trong thanh công thức thay vì thực hiện trực tiếp trong ô.

Chuyển đổi hiển thị tất cả các công thức cùng một lúc

Bất cứ khi nào bạn chỉnh sửa ô có chứa công thức, Excel sẽ tự động hiển thị công thức thay vì kết quả. Nhưng đôi khi bạn có thể xem tất cả các công thức trên trang tính cùng một lúc. Để thực hiện việc này, chỉ cần sử dụng phím tắt để hiển thị công thức: Control + ~ (đó là dấu ngã).

Với phím tắt này, bạn có thể nhanh chóng chuyển đổi hoặc tắt hiển thị tất cả các công thức trên trang tính. Đây là một cách hay để xem tất cả các công thức cùng một lúc và kiểm tra tính nhất quán của các công thức.

Chọn tất cả các công thức trong một trang tính cùng một lúc

Một cách khác để xem tất cả các công thức trong trang tính là chọn chúng. Bạn có thể thực hiện việc này bằng cách sử dụng các tính năng mạnh mẽ hơn (và ẩn) trong Excel: Đó là truy cập tới tùy chọn Go To > Special (Ctrl + G).

Với lệnh này, bạn có thể chọn tất cả các điều thú vị trong Excel, bao gồm ô trống, ô chứa số và hơn thế nữa. Một trong những tùy chọn đó là các ô chứa công thức. Để chọn tất cả các ô có chứa công thức trên trang tính, chỉ cần gõ Ctrl + G để hiển thị hộp thoại Go To, sau đó bấm nút Special rồi chọn Formulas. Khi bạn bấm OK, tất cả các ô chứa công thức sẽ được chọn.

Nếu bạn chỉ muốn chọn một tập hợp con các công thức trong trang tính, hãy thực hiện lựa chọn trước, sau đó sử dụng lệnh tương tự.

Xem thêm: Hướng dẫn cách sử dụng công thức SUMIF trong EXCEL qua những ví dụ cụ thể

Sử dụng dán đặc biệt để chuyển đổi công thức thành giá trị tĩnh

Một vấn đề phổ biến trong Excel là cần ngăn các giá trị được tính toán thay đổi. Ví dụ: có thể bạn muốn đơn giản hóa trang tính bằng cách xóa các cột "trợ giúp" mà bạn đã sử dụng để tạo các giá trị nhất định. Nhưng nếu xóa các cột này với các công thức vẫn tham chiếu đến chúng, bạn sẽ nhận được một loạt lỗi #REF.

Giải pháp trước tiên là chuyển đổi công thức thành giá trị, sau đó xóa các cột thừa. Cách đơn giản nhất để làm điều đó là sử dụng tính năng Paste Special. Đầu tiên, chọn công thức bạn muốn chuyển đổi và Copy chép vào Clipboard.

Xem thêm: Cách sử dụng Paste Special trong bài thi MOS Excel hiệu quả nhất

Tiếp theo, với các công thức vẫn được chọn, hãy mở hộp thoại Paste Special (Win: Ctrl + Alt + V, Mac: Ctrl + Cmd + V) và sử dụng tùy chọn Values. Điều này sẽ thay thế tất cả các công thức bạn đã chọn bằng các giá trị mà chúng đã tính toán.

Sử dụng Paste Special để điều chỉnh các giá trị tại chỗ

Một vấn đề phổ biến khác trong Excel là cần phải thay đổi nhiều giá trị tại chỗ. Ví dụ: Bạn có một danh sách 500 giá sản phẩm và bạn cần tăng giá tất cả lên 5%. Hoặc có một danh sách 100 ngày cần chuyển sang “tương lai” là một tuần tới?

Trong những trường hợp như vậy, bạn có thể thêm cột trợ giúp (Help) vào bảng của mình, thực hiện phép tính cần thiết, chuyển đổi kết quả thành giá trị, sau đó sao chép chúng qua cột gốc. Nhưng nếu bạn chỉ cần một phép tính đơn giản, Paste Special sẽ đơn giản và nhanh hơn nhiều, vì bạn có thể thay đổi giá trị trực tiếp mà không cần bất kỳ công thức phụ nào.

Ví dụ: Để chuyển đổi một nhóm ngày tại chỗ thành một tuần sau, hãy thực hiện việc này: Thêm số 7 vào bất kỳ ô nào trong trang tính, sau đó sao chép nó vào clipboard. Tiếp theo, chọn tất cả các ngày bạn muốn thay đổi. Sau đó, sử dụng tùy chọn aste Special > Operations > Add. Khi bạn bấm nút OK, Excel sẽ thêm số 7 vào ngày bạn đã chọn, di chuyển chúng về phía trước trong thời gian 7 ngày mà không cần tạo cột trợ giúp.

Để tăng bảng giá của sản phẩm lên 10%, hãy sử dụng phương pháp tương tự. Nhập 1.10 vào một ô và sao chép nó vào clipboard. Sau đó, chọn giá bạn muốn thay đổi và sử dụng tùy chọn Paste Special > Operations > Multiply để chuyển đổi tất cả các giá tại chỗ.

Lưu ý: Cách này chỉ hoạt động với các giá trị. Còn không thể áp dụng với các công thức.

Sử dụng các phạm vi đã đặt tên để làm cho công thức dễ đọc hơn

Một trong những mẹo chuyên nghiệp sử dụng từ khá lâu đó là sử dụng các phạm vi được đặt tên trong công thức để làm cho chúng dễ đọc hơn. Ví dụ: giả sử bạn có một trang tính đơn giản hiển thị số giờ làm việc cho một nhóm nhỏ. Đối với mỗi người, bạn muốn nhân số giờ làm việc của họ với tỷ lệ mỗi giờ. Giả sử tỷ lệ hàng giờ nằm trong ô A1, công thức của bạn có thể trông giống như sau:

=B3 * $A$1

=B4 * $A$1

=B5 * $A$1

Nhưng nếu bạn đặt tên cho ô A1 là "hourly_rate", công thức của bạn sẽ giống như sau:

=B3 * hourly_rate

=B4 * hourly_rate

=B5 * hourly_rate

Đặt tên cho phạm vi theo cách này làm cho các công thức dễ đọc hơn và tiết kiệm việc nhập nhiều ký hiệu đô la ($) để tạo tham chiếu tuyệt đối.

Đặt tên cho phạm vi rất dễ dàng. Chỉ cần chọn (các) dải ô/ô bạn muốn đặt tên, sau đó nhập tên vào hộp tên ở góc trên cùng bên trái và nhấn Enter. Bây giờ bạn đã đặt tên cho một dải ô, Excel sẽ sử dụng nó bất cứ khi nào bạn trỏ và nhấp vào dải ô khi bạn đang xây dựng công thức hay khi bấm vào một dải ô đã đặt tên, bạn sẽ thấy tên của nó được tự động chèn vào công thức.

Bạn cũng có thể dễ dàng điều hướng đến phạm vi được đặt tên bất cứ khi nào mình muốn. Chỉ cần chọn tên từ menu thả xuống xuất hiện bên cạnh hộp tên.

Tự động áp dụng tên cho các công thức hiện có

Đ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 đề.

Xem thêm: Hướng dẫn tính giá trị trung bình theo ngày trong tuần trên Excel

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.

Xem thêm: Hướng dẫn dùng tham chiếu ô tuyệt đối và tương đối

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.

Xem thêm: Sử dụng hàm COLUMNS trả về số lượng cột của tham chiếu trong Excel

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ề.

Sử dụng tính năng Formula Evaluator để giải quyết từng bước một công thức

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ả.

Xem thêm: Hướng dẫn Evaluate Formula – Đánh giá công thức trong Excel

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.

Xem thêm: Hướng dẫn cách sử dụng hàm TEXT trong Excel chi tiết nhất

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 ở đó.

Xem thêm: Cách sử dụng AutoSum trong Excel

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.

Xem thêm: Hướng dẫn cách AutoSum nhiều cột/hàng/trang tính trong Excel

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!.

Cuối cùng, đừng quên tham khảo khóa học EXG01 - Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ của Gitiho bạn nhé! Hẹn gặp lại các bạn trong những bài viết tiếp theo.