Bài viết: Cách viết hàm hiệu quả

[kyo]

CÁCH VIẾT HÀM HIỆU QUẢ

Bạn biết đấy, Hàm là 1 trong những tính năng hữu ích nhất của Excel. Nhưng nó cũng là vấn đề muôn thuở với mọi người, nhất là những người mới học Excel. Làm thế nào để viết Hàm hiệu quả nhất có thể? "Hiệu quả" nghĩa là hiệu quả cho bạn và công việc của bạn, chứ không phải hiệu quả cho Excel để tính toán.

Xem ví dụ dưới đây về 1 Hàm hiệu quả. Trong hình, cột A và B là 2 cột chứa dữ liệu về nhân viên. Cột E và F được thiết lập như 1 bảng tóm tắt số lượng nhân viên trong từng bộ phận. Ta sử dụng hàm COUNTIF để tính (xem hàm cụ thể tại ô G).

Xét về mặt kỹ thuật, hàm này được viết ra đúng, vì các kết quả được trả về chính xác. Nhưng nó không phải là cách viết hiệu quả. Vì sao vậy? Vì điều kiện để đếm (được đóng khung màu đỏ trong hình trên), đã được nhập dưới dạng văn bản. Điều này có nghĩa là, mỗi công thức trong cột F phải được sửa đổi đối với từng bộ phận. Thật mất công.

Cách hiệu quả hơn là đặt mỗi điều kiện đếm vào mỗi ô tương ứng như bạn có thể thấy trong ví dụ tại cột H trong hình dưới đây (đậc biệt trong ví dụ này, các điều kiện đếm đã được đặt sẵn trong cột E).

Hàm được viết theo cách này sẽ:
- Nhanh: với cách viết hàm hiệu quả tại cột H như trong hình, bạn chỉ việc nhập vào cột F2, sau đó Copy xuống các dòng ở dưới và công việc của bạn được thực hiện chớp nhoáng
- Dễ dàng cập nhập: nếu bạn cần thay đổi tên Bộ phận trong cột E, hàm của bạn sẽ tự động nhận tên mới mà không cần phải thêm hay sửa hàm.
- Trực quan: người dùng Excel có kinh nghiệm hy vọng rằng các hàm có thể được Copy và Paste cho tất cả các ô liền kề với ô chứa hàm. Vì vậy, nếu 1 người nào đó được thừa hưởng tập tin của bạn và họ chỉnh sửa hàm, họ sẽ làm như vậy tại các ô trên cùng bên trái trong vùng của hàm, và sau đó Copy - Paste vào các ô còn lại trong bảng. Tuy nhiên, điều này có thể dẫn đến sai sót, nếu họ không chú ý đến hàm của bạn, giả sử 15 dòng ở dưới và 3 cột về bên phải là khác nhau vì bạn đã nhập thêm vào một số điều kiện.

Bên dưới là ví dụ cho việc Copy và Paste một hàm vào nhiều ô được xem là hữu dụng, hàm SUMIFS ở ô G2 có thể được viết một lần và được Copy cho 14 ô còn lại trong bảng tổng hợp:

Trái ngược với cách viết không hiệu quả:
=SUMIFS($D$2:$D$64,$A$2:$A$64;"Giám đốc",$C$2:$C$64,2012)

Chú ý rằng sự khác nhau giữa một hàm hiệu quả với hàm không hiệu quả là: Hàm không hiệu quả đòi hỏi phải nhập 15 hàm khác nhau, trong khi hàm hiệu quả chỉ nhập một lần và sau đó chỉ việc Copy và Paste vào các ô còn lại trong bảng tính.

ĐỊA CHỈ TUYỆT ĐỐI VÀ ĐỊA CHỈ TƯƠNG ĐỐI

Bên cạnh đó, một hàm hiệu quả là một hàm có thể tận dụng các địa chỉ tuyệt đối và tương đối. Các ký hiệu $ trong công thức được quy định khi một địa chỉ là tuyệt đối hoặc tương đối.

Khi Copy và Paste một công thức bao gồm các địa chỉ dòng/cột được dẫn trước bởi ký hiệu $ thì không thay đổi (nghĩa là tuyệt đối), trong khi bất kỳ địa chỉ mà không có ký hiệu $ sẽ bị thay đổi tương đối đến số và hướng của ô được di chuyển.

Chú ý rằng các địa chỉ ô có cột và dòng chỉ địa chỉ tuyệt đối sẽ ký hiệu là $D$2. Một số chỉ có địa chỉ cột tuyệt đối: $F2. Và những ô khác chỉ có địa chỉ dòng tuyệt đối: G$1.

ĐẶT NAME VÙNG

Các mẹo trên sẽ giúp bạn tân dụng Excel và các địa chỉ tuyệt đối/tương đối để làm nhiều việc, và đó là bước khởi đầu tốt nhưng các địa chỉ ô này làm cho hàm phức tạp trong việc đọc và viết.

Bạn có thể làm tốt hơn nữa bằng cách sử dụng Name vùng thay cho các địa chỉ ô. Lấy lại ví dụ trên, hàm SUMIFS có thể được viết như sau:

Bây giờ, đây có phải là cách dễ hơn để đọc? Nó cũng sẽ nhanh hơn cho việc viết khi chỉ đánh Name vùng vào hàm hoặc nhấn F3 để chọn từ danh sách tên.

Name vùng là cách thức đơn giản nhất cho phép ta đưa các vùng, ô vào một Name mà sau đó, có thể được dùng ở vị trí các ô tham chiếu thực. Ví dụ sau đưa ra cách đặt Name cho các vùng được dùng trong hàm SUMIFS: $D$2:$D$22 tên là "Luong".

1/ Kéo chọn vùng D222 -> chọn thẻ Formulas -> Define Name.

2/ Trong hộp thoại New Name, đặt tên Luong cho mục Name, ngoài ra bạn có thể thêm Comment để ghi chú ý, tránh bị nhầm lẫn với những Name khác. Sau đó bạn bấm OK.

3/ Làm tương tự với 2 vùng còn lại :
- $A$2:$A$64 tên là "BoPhan".
- $C$2:$C$64 tên là "Nam".

Vì vậy công thức dưới đây:
=SUMIFS($D$2:$D$64,$A$2:$A$64;$F2,$C$2:$C$64,G$1)

Có thể được viết lại như sau:
=SUMIFS(Luong,BoPhan;$F2,Nam,G$1)

Khi bạn đã nắm vững Name vùng, bạn có thể quan tâm đến Name động. Đây là vùng sẽ tự động mở rộng/thu hẹp khi bạn thêm/bớt dữ liệu hoặc dựa trên các điều kiện mà bạn quy định. Bạn có thể tạo ra 1 vùng động bằng cách sử dụng hàm OFFSET hoặc hàm INDEX. Nếu bạn thấy khó khăn và có hơi chút sợ sệt trong việc sử dụng 2 hàm trên, vẫn còn cách khác dễ dàng hơn. Đó là sử dụng Excel Tables.

EXCEL TABLES

Bạn có thể sẽ bỏ lỡ cách này nếu không quen thuộc với Excel Tables. Excel Tables là 1 trong những tính năng hữu ích nhất mà chỉ có ở phiên bản Excel 2007 trở về sau, và nó là 1 trong những tính năng ít được sử dụng nhất.

Các tính năng sau của Excel Tables sẽ cách mạng hóa cách bạn viết công thức :
- Tham chiếu cấu trúc (Structured References): Loại tham chiếu này cho phép bạn sử dụng tên Table/cột trong hàm thay vì tham chiếu ô như A1 hoặc R1C1. Tham chiếu cấu trúc hoạt động giống như Name vùng, và vì nó là 1 phần của Table, bạn không cần phải cài đặt chúng cách thủ công.
- Name động: Các Tham chiếu cấu trúc luôn động, điều này có nghĩa là khi bạn thêm dữ liệu mới vào Table, vùng Table sẽ tự động nới rộng ra và kết nạp các dữ liệu mới vào.

Tham chiếu cấu trúc là 1 thay thế tuyệt vời khi hàm OFFSET và INDEX cứ quay vòng vòng trong đầu bạn.

Làm việc với Tham chiếu cấu trúc
Có rất nhiều cách để tham chiếu đến các bộ phận của Table, và trong ví dụ dưới đây, bạn có thể thấy các tham chiếu cụ thể được tạo thành từ các tên trong Table (Table1), và tiêu đề cột, mà chúng ta có thể gõ hoặc chọn từ danh sách.
Tính năng lựa chọn từ danh sách là 1 trong những tính năng tuyệt vời của Excel Table. Khi bạn vừa gõ 1 ít chữ cái trong tên Table/cột, 1 danh sách các tên Table/cột sẽ hiện ra (tương tự như Name vùng), và bạn có thể dễ dàng lựa chọn chúng.

Với Tham chiếu cấu trúc, hàm của chúng ta tại ô G2 sẽ trở thành :
=SUMIFS(Table1[Tiền lương],Table1[Bộ phận],$F2,Table1[Năm],G$1)

Những Name vùng đã được thay thế bằng Tham chiếu cấu trúc của Table (ví dụ : Table[Tiền lương],…)

THAM SỐ ROW_NUM VÀ COLUMN_NUM

Một số hàm có các tham số là row_num hoặc column_num. Ví dụ :
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
INDEX(array, row_num, column_num)

Bạn đang làm việc với hàm VLOOKUP cho bảng dữ liệu lớn. Bạn muốn chèn thêm cột vào bảng dữ liệu của bạn. Như vậy, tham số "col_index_num" trong hàm VLOOKUP bạn đang dùng sẽ không còn đúng nữa, và tất nhiên, bạn cũng sẽ không nhận được kết quả chính xác. Cũng vậy, thật không hiệu quả khi phải tự cập nhật lại các tham số "col_index_num". Hơn nữa, tự cập nhật lại sẽ phá vỡ quy tắc "viết 1 lần và Copy - Paste cho tất cả" trong việc viết hàm hiệu quả.

Thay vào đó, chúng ta có thể sử dụng hàm COLUMN hoặc COLUMNS và kết hợp khéo léo giữa địa chỉ tuyệt đối và tương đối để kết quả "col_index_num" được tự động trả về. Hàm COLUMNS sẽ trả kết quả là số đếm của cột trong vùng mà bạn vạch ra, tương tự như vậy, hàm ROWS sẽ trả kết quả là số đếm của dòng trong vùng đó.

Ví dụ, hàm được viết tại ô C3 dưới đây : =COLUMNS($B$1:B1) và kết quả là 1. Nghĩa là độ rộng của cột B1:B1 là 1. Và khi copy qua các cột D và E, vùng tham chiếu sẽ tăng lên 1 cột (tức là $B$1:C1 và $B$11) và do đó kết quả trả về là 2 và 3 tương ứng, như chúng ta có thể thấy trong hình dưới đây:

Vì vậy, thay vì từ từ mã hóa từng tham số col_index_num trong hàm VLOOKUP, bạn có thể sử dụng hàm COLUMNS để có số cột tự động tăng lên, khi bạn Copy hàm COLUMNS vào các cột về bên phải.

Mẹo : Vì bạn chỉ đếm cột mà không cần phải tham chiếu đến các dòng trong vùng dữ liệu, bạn có thể viết công thức ngắn gọn : =COLUMNS($A:B) và kết quả là 2.

Chú ý : bạn cũng đừng quên tham số row_num (trong hàm HLOOKUP hoặc INDEX). Vì thế bạn sẽ sử dụng hàm ROWS thay cho hàm COLUMNS.

KẾT LUẬN
Bài viết này được viết ra với hy vọng có thể giúp ích cho bạn trong việc viết cũng như giải thích và cập nhật hàm cách nhanh chóng có thể.
Bạn nên hướng đến viết 1 công thức cho các ô trên cùng bên trái trong bảng tính của bạn, hoặc ô đầu tiên trong cột chứa hàm, và sau đó Copy hàm đi khắp nơi nó cần phải đến. Bất kỳ sự thay đổi về vùng hoặc các điều kiện, … cũng tự động cập nhật trên những nơi bạn Paste hàm.

Chúc bạn thành công,
Nguyễn Bảo Khanh.

Một số bài viết có liên quan:
1/ Chiêu thứ 13: Sao chép công thức giữ nguyên tham chiếu tương đối
2/ Tham chiếu trong Excel (1)
3/ Tham chiếu trong Excel (2)

 

[vu_tuan_manh_linh]

Bổ sung: Để sử dụng hiệu quả các hàm thì cần lưu ý các yếu tố sau:
1. Viết hàm mang tính tổng quan, hạn chế tham chiếu tĩnh (ngay cả đối với chỉ số Col_index_Num của hàm Vlookup() cũng có thể tham chiếu động).
2. Lưu ý việc cố định địa chỉ tham chiếu (cố định hàng, cột).
3. Sử dụng hàm bẫy lỗi
4. Dùng hàm kết hợp
5. Bố trí dữ liệu khoa học theo ma trận hàng _ cột, Sheet..

 

[huỳnh thi tĩnh]

Cảm ơn bài viết của bạn