Đánh giá lequocthai.com:
Hàm COLUMNS còn có thể dùng để cấu hình xác thực dữ liệu và, khi kết hợp với các hàm Excel khác, tạo ra các công thức phức tạp hơn.
Cú pháp
Cú pháp của hàm COLUMNS như sau.
=COLUMNS(array)Đối số
- array – Đối số bắt buộc, nhận một mảng, một tham chiếu ô hoặc một phạm vi ô mà chúng ta muốn đếm số cột. Giá trị đầu vào cũng có thể là công thức trả về mảng.
Đặc điểm quan trọng của hàm COLUMNS
Các đặc điểm đáng chú ý của hàm COLUMNS:
- Giá trị của đối số array cũng có thể là một mảng hằng.
- Nếu đối số array được gán giá trị 0, hàm COLUMNS sẽ trả về 1.
Ví dụ về hàm COLUMNS
Để minh họa, chúng tôi sẽ dùng các loại giá trị đầu vào khác nhau cho đối số array.
- Trong ví dụ đầu tiên, đối số array là tham chiếu ô A1; hàm trả về 1 vì chỉ có một cột.
- Tiếp theo, hàm đếm số cột trong phạm vi A1:C1, kết quả là 3 (các cột A, B, C).
- Ví dụ thứ ba cho thấy khi phạm vi thay đổi thành A1:C999, số cột vẫn là 3.
- Ví dụ tiếp theo sử dụng phạm vi A:Z, hàm COLUMNS trả về 26, tương ứng với tổng số cột từ A đến Z.
- Một mẹo nhanh để tính tổng số cột trong toàn bộ worksheet: công thức lấy tất cả các ô ở hàng đầu tiên, vì vậy kết quả là số cột tối đa của Excel hiện tại là 16384.
- Cuối cùng, chúng tôi truyền một mảng hằng có 2 hàng và 3 cột vào hàm COLUMNS; hàm trả về 3, tức là số cột của mảng.
Bây giờ chúng ta đã nắm rõ cách hoạt động của hàm COLUMNS, hãy xem một số ứng dụng thực tế.
Ví dụ 1 – Tính trung bình bằng hàm COLUMNS
Giả sử có bảng doanh số hàng ngày cho 7 khu vực bán hàng. Muốn tính trung bình doanh số mỗi ngày, có thể dùng hàm AVERAGE, nhưng ở đây chúng ta sẽ dùng SUM kết hợp COLUMNS:
=SUM(C3:I3)/COLUMNS(C3:I3)Hàm COLUMNS(C3:I3) trả về số cột (số ngày), sau đó SUM chia cho số cột cho kết quả trung bình.
Nếu muốn đếm tổng số ô trong cùng phạm vi, có thể dùng ROWS kết hợp COLUMNS (xem ví dụ tiếp theo).
Ví dụ 2 – Tìm tổng số ô trong phạm vi bằng hàm COLUMNS
Giả sử bảng dữ liệu có 3 khu vực bán hàng, mỗi khu vực có nhiều nhân viên. Để tính tổng số nhân viên, ta nhân số khu vực (được tính bằng COLUMNS) với số nhân viên mỗi khu vực (được tính bằng ROWS):
Số khu vực:
=COLUMNS(B3:D9)Số nhân viên mỗi khu vực:
=ROWS(B3:D9)Tổng số nhân viên:
=COLUMNS(B3:D9)*ROWS(B3:D9)Ví dụ 3 – Tạo dãy ngày trong cột
Muốn tạo chuỗi ngày tăng dần mỗi 3 ngày, có thể dùng AutoFill hoặc công thức sau kết hợp DATE và COLUMNS:
=DATE(2023,1,1)+(COLUMNS($B$2:B2)-1)*3Công thức này lấy ngày bắt đầu (1‑1‑2023) và cộng thêm số cột đã tính (trừ 1) nhân với bước 3 ngày.
Ví dụ 4 – Sử dụng Xác thực Dữ liệu (Data Validation) với hàm COLUMNS
Bạn có thể giới hạn số cột nhập vào bằng Data Validation. Ví dụ, trong một bảng đăng ký đội, mỗi đội tối đa 4 thành viên. Thực hiện các bước sau:
- Chọn phạm vi ô cần áp dụng giới hạn.
- Vào Data → Data Validation → Settings, chọn Custom trong Allow.
- Nhập công thức sau vào trường Formula:
=COLUMNS($B1:$I1)<=4- Tùy chỉnh thông báo lỗi nếu muốn.
Công thức kiểm tra số cột từ B tới I có không vượt quá 4, do đó không cho phép nhập hơn 4 thành viên cho mỗi đội.
Hàm COLUMNS vs Hàm ROWS
Hàm COLUMNS trả về số cột trong một phạm vi hoặc mảng; hàm ROWS trả về số hàng. Ví dụ minh họa:
- COLUMNS(B2:F5) → 5 cột.
- ROWS(B2:F5) → 4 hàng.
Hàm COLUMNS vs Hàm COLUMN
- COLUMNS đếm số cột trong một mảng.
- COLUMN trả về vị trí cột của một ô cụ thể.
Ví dụ:
- COLUMNS(B2:D4) → 3 (cột B, C, D).
- COLUMN(B2) → 2 (vị trí cột B).
Cả hai hàm đều hữu ích khi thực hiện các phép tính trên các cột hoặc tập dữ liệu cụ thể trong worksheet.
Hãy thực hành với các bộ dữ liệu và kịch bản khác nhau để khám phá thêm các ứng dụng của hàm COLUMNS và nâng cao kỹ năng Excel của bạn. Trong thời gian tới, chúng tôi sẽ giới thiệu các hàm Excel khác giúp tăng năng suất và hiệu quả công việc.
