Ba năm làm việc với Excel, xử lý hàng chục bảng tính mỗi ngày, nhưng tôi chỉ mới phát hiện hàm COLUMNS cách đây sáu tháng. Trước đó, mỗi lần cần biết một vùng dữ liệu có bao nhiêu cột, tôi ngồi đếm bằng mắt từ cột A đến cột cuối. Với bảng rộng đến cột CH hoặc DZ, việc đếm này tốn cả phút đồng hồ và thường sai số.
Hàm COLUMNS xuất hiện từ Excel 2007, có mặt trong mọi phiên bản hiện đại, nhưng ít được nhắc đến. Microsoft không đưa nó vào danh sách hàm phổ biến, các khóa học Excel cơ bản bỏ qua, và ngay cả thanh công thức gợi ý cũng ưu tiên các hàm khác. Kết quả là hầu hết người dùng không biết công cụ này tồn tại cho đến khi tình cờ gặp trong một công thức phức tạp.
Hàm COLUMNS trả về số lượng cột trong vùng dữ liệu
Cú pháp đơn giản: =COLUMNS(array) trong đó array là vùng ô bạn muốn đếm. Nhập =COLUMNS(A1:F10) sẽ cho kết quả là 6, vì vùng này chứa 6 cột từ A đến F. Nhập =COLUMNS(B5:B20) trả về 1 vì đó chỉ là một cột duy nhất.
Cách sử dụng cơ bản:
- Chọn ô muốn hiển thị kết quả
- Nhập
=COLUMNS( - Chọn vùng dữ liệu bằng chuột hoặc gõ địa chỉ
- Đóng ngoặc và nhấn Enter
Hàm này không quan tâm nội dung bên trong các ô, chỉ đếm số lượng cột mà vùng đã chọn chiếm giữ. Vùng trống hay đầy dữ liệu đều cho kết quả giống nhau.
Phân biệt COLUMNS và COLUMN để không nhầm lẫn
Đây là điểm gây confusion lớn nhất. Excel có hai hàm với tên gần giống nhau nhưng chức năng hoàn toàn khác biệt.
COLUMNS (có chữ S):
- Đếm tổng số cột trong một vùng
=COLUMNS(B4:D10)trả về 3 (ba cột B, C, D)- Dùng để biết kích thước vùng dữ liệu
COLUMN (không có S):
- Trả về số thứ tự của cột trong bảng tính
=COLUMN(D5)trả về 4 (cột D là cột thứ 4 tính từ A)- Dùng để biết vị trí tuyệt đối
Ví dụ minh họa sự khác biệt: Với vùng B4:D10, hàm =COLUMNS(B4:D10) cho kết quả 3, trong khi =COLUMN(B4) cho kết quả 2 vì B là cột thứ hai trong bảng tính Excel.
Tôi từng mắc lỗi dùng COLUMN khi cần COLUMNS trong công thức tính toán, dẫn đến kết quả sai hoàn toàn. Mất hai tiếng debug mới phát hiện nhầm chữ S.
Đếm tổng số ô bằng cách kết hợp COLUMNS với ROWS
Một ứng dụng thực tế: Excel không có hàm tính tổng số ô trong vùng, nhưng kết hợp COLUMNS và ROWS giải quyết vấn đề này.
Công thức: =COLUMNS(vùng)*ROWS(vùng)
Ví dụ cụ thể:
Để đếm tổng ô trong vùng A1:Z100, dùng: =COLUMNS(A1:Z100)*ROWS(A1:Z100)
Kết quả: 26 cột × 100 hàng = 2,600 ô
Tôi dùng công thức này khi làm báo cáo cần validate kích thước dữ liệu import. Trước khi xử lý, tôi kiểm tra nhanh xem file có đúng số lượng ô mong đợi không. Với bảng lớn hàng nghìn dòng, việc đếm thủ công không khả thi.
Tạo công thức VLOOKUP động tự động điều chỉnh
VLOOKUP truyền thống yêu cầu nhập số thứ tự cột cố định. Khi thêm hoặc xóa cột trong bảng tra cứu, công thức bị sai. COLUMNS giải quyết bằng cách tính số cột tự động.
Công thức cũ (tĩnh): =VLOOKUP(A2,Bảng,3,FALSE)
Nếu thêm cột vào vị trí 2, cần sửa thủ công 3 thành 4.
Công thức mới (động): =VLOOKUP(A2,Bảng,COLUMNS(Bảng[[#Headers],[Cột1]:[CộtCần]]),FALSE)
Với Named Range hoặc Table, công thức này tự động điều chỉnh khi cấu trúc bảng thay đổi. Tôi dùng kỹ thuật này cho các báo cáo hàng tháng với cấu trúc cột biến động, tiết kiệm hàng giờ sửa công thức thủ công.
Setup đơn giản hơn:
Nếu bạn biết cột cần lấy cách cột đầu tiên bao nhiêu cột, dùng: =VLOOKUP(A2,Bảng,COLUMNS(Bảng)-2,FALSE)
Trong đó “-2” điều chỉnh để lấy đúng cột mong muốn.
Tạo số thứ tự tự động theo chiều ngang
Khi cần tạo dãy số tăng dần theo hàng ngang (1, 2, 3, 4…), COLUMNS kết hợp với COLUMN tạo công thức tự động mở rộng.
Công thức: =COLUMN()-COLUMN($A$1)+1
Nhập vào ô B2 rồi kéo sang phải, mỗi ô tăng thêm 1. Nhưng dùng COLUMNS cho phép tạo dãy số không phụ thuộc vị trí ô.
Ví dụ nâng cao – tạo dãy số nhân:
Trong ô B2: =A2*COLUMN() Kéo sang sẽ tạo: 10, 20, 30, 40 (nếu A2 = 10)
Hoặc dùng COLUMNS để tạo pattern phức tạp: =A2*(COLUMNS($B2:B2))
Công thức này mở rộng khi copy sang ô bên phải, tự động tính toán vị trí tương đối.
Xác định địa chỉ ô cuối cùng trong vùng dữ liệu
Khi làm việc với dữ liệu động, cần biết ô cuối cùng của vùng để tham chiếu. COLUMNS kết hợp với ADDRESS và ROW thực hiện điều này.
Giả sử vùng dữ liệu được đặt tên là “DuLieu” (B4:E10).
Công thức tìm ô cuối: =ADDRESS(MAX(ROW(DuLieu)),COLUMN(DuLieu)-1+COLUMNS(DuLieu))
Phân tích công thức:
MAX(ROW(DuLieu))tìm hàng cuối (10)COLUMN(DuLieu)tìm cột đầu (2 cho cột B)COLUMNS(DuLieu)đếm số cột (4)- ADDRESS kết hợp thành địa chỉ ô
Kết quả: $E$10
Tôi dùng kỹ thuật này trong công thức tổng hợp báo cáo từ nhiều sheet. Thay vì update thủ công khi dữ liệu mở rộng, công thức tự động nhận diện vùng mới.
Kiểm tra tính nhất quán của bảng dữ liệu
Khi import dữ liệu từ nhiều nguồn, đôi khi các bảng có số cột khác nhau gây lỗi khi xử lý. COLUMNS giúp validate nhanh.
Công thức kiểm tra: =IF(COLUMNS(Bảng1)=COLUMNS(Bảng2),"Khớp","Không khớp")
Hoặc cảnh báo khi số cột không đúng mong đợi: =IF(COLUMNS(A1:Z100)<>26,"CẢNH BÁO: Thiếu cột","")
Trong hệ thống báo cáo tự động của tôi, công thức này đặt ở đầu mỗi sheet. Nếu file nguồn thay đổi cấu trúc, cảnh báo xuất hiện ngay lập tức trước khi xử lý dữ liệu sai.
Tính toán với mảng động không cần VBA
Excel 365 và Excel 2021 hỗ trợ dynamic arrays. COLUMNS kết hợp với SEQUENCE tạo mảng số tự động.
Tạo dãy cột: =SEQUENCE(1,COLUMNS(A1:J1))
Tạo hàng ngang chứa: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Ứng dụng thực tế:
Tạo header tự động cho bảng tính: ="Tuần " & SEQUENCE(1,COLUMNS(B2:M2))
Kết quả: Tuần 1, Tuần 2, Tuần 3… Tuần 12
Khi thêm cột mới vào vùng B2:M2, header tự động cập nhật. Tôi áp dụng kỹ thuật này cho báo cáo theo tuần, tháng, quý thay đổi số cột thường xuyên.
Tối ưu hiệu suất công thức với phạm vi chính xác
Công thức Excel tính toán nhanh hơn khi vùng tham chiếu nhỏ hơn. Thay vì dùng cột nguyên (A:A), dùng COLUMNS để xác định vùng cần thiết.
Cách cũ (chậm): =SUM(A:A) – Excel phải scan 1,048,576 hàng
Cách mới (nhanh):
Đầu tiên tìm vùng dữ liệu thật: =ADDRESS(COUNTA(A:A),1)
Sau đó dùng vùng cụ thể: =SUM(A1:A1000) nếu dữ liệu chỉ đến hàng 1000
COLUMNS giúp tự động hóa việc này trong các công thức phức tạp. Với file lớn, tôi thấy tốc độ tính toán tăng 40-60% chỉ bằng cách giới hạn phạm vi chính xác.
Tương thích và giới hạn cần biết
Hàm COLUMNS hoạt động từ Excel 2007 trở lên, bao gồm Excel 2010, 2013, 2016, 2019, 2021 và Excel 365. Không có trên Excel 2003 hoặc cũ hơn.
Điểm cần lưu ý:
- Tham số array chỉ chấp nhận một vùng duy nhất, không thể nhập nhiều vùng cách nhau bằng dấu phẩy
- Kết quả luôn là số nguyên dương
- Không tính được cột ẩn riêng biệt, cột ẩn vẫn được đếm như cột thường
Nếu bạn thường xuyên đếm cột thủ công hoặc sửa số cột trong VLOOKUP mỗi khi bảng thay đổi, hàm COLUMNS giải quyết hai vấn đề này. Kết hợp với ROWS, bạn có công cụ mạnh mẽ để phân tích kích thước và tự động hóa công thức. Bắt đầu với việc thay thế số cố định trong VLOOKUP bằng COLUMNS để thấy lợi ích ngay lập tức.
