Đánh giá lequocthai.com:
Cần lưu ý rằng hàm COLUMN trả về số cột tương đối so với cột đầu tiên của bảng tính. Ô ở cột A có giá trị 1, cột B là 2 và tiếp tục như vậy.
Cú pháp
Cú pháp của hàm COLUMN như sau:
=COLUMN([reference])Đối số
Hàm COLUMN chỉ chấp nhận một đối số duy nhất.
`reference` – Đối số này là tùy chọn, chứa ô hoặc dải ô mà bạn muốn lấy số cột. Lưu ý rằng đối số `reference` không thể chứa nhiều tham chiếu ô khác nhau.
Các đặc điểm quan trọng của hàm COLUMN
- Nếu bỏ qua đối số `reference`, hàm COLUMN sẽ trả về số thứ tự cột của ô chứa công thức.
- Nếu thêm ký hiệu @ vào đầu hàm COLUMN, Excel sẽ bỏ qua đầu vào dạng mảng và chỉ xét giá trị đơn là phần tử đầu tiên của mảng (được gọi là implicit intersection operator – toán tử giao nhau ngầm).
Ví dụ về hàm COLUMN
Hàm COLUMN rất hữu ích trong việc phân tích dữ liệu lớn. Dưới đây là một số ví dụ minh họa.
Ví dụ 1 – Sử dụng cơ bản của hàm COLUMN
Chúng ta sẽ thử các giá trị đầu vào khác nhau cho đối số `reference` để quan sát cách hàm COLUMN phản hồi.
- Trong ví dụ đầu tiên, vì để trống đối số, hàm trả về số thứ tự cột của ô chứa công thức (C3), vì vậy kết quả là 3.
- Ví dụ tiếp theo, công thức trả về số thứ tự cột của ô A4, tức là 1.
- Hai ví dụ sau cho thấy cách sử dụng dải ô làm đối số. Ở ví dụ thứ ba, mặc dù đầu vào là dải ô A1:A1, công thức chỉ trả về số thứ tự cột của ô đầu tiên (A1) – giá trị 1 – do có sự can thiệp của implicit intersection operator.
- Ở ví dụ cuối cùng, hàm COLUMN trả về số thứ tự cột của mỗi ô trong dải A1:D1, cho kết quả 1,2,3,4.
Ví dụ 2 – Xác định cột cuối cùng trong một dải
Giả sử bạn có một bảng dữ liệu bán hàng theo tháng cho các sản phẩm, mỗi hàng là một sản phẩm, mỗi cột là một tháng.
Để vẽ biểu đồ tổng doanh thu theo tháng, trước tiên cần biết cột cuối cùng của bảng. Có thể kết hợp hàm COLUMN và COLUMNS để tìm cột bên phải nhất.
- Tìm số thứ tự cột nhỏ nhất trong dải:
=MIN(COLUMN(B2:H7))`COLUMN(B2:H7)` trả về mảng `{2,3,4,5,6,7,8}` và `MIN` sẽ cho ra giá trị nhỏ nhất là 2.
- Cộng số cột và trừ 1 để có cột cuối cùng:
=MIN(COLUMN(B2:H7))+COLUMNS(B2:H7)-1
Ví dụ 3 – Thêm giá trị vào mỗi vị trí thứ ba
Giả sử bạn có một bảng thu nhập cố định 2 500 USD mỗi tháng trong một năm, và muốn mỗi tháng thứ ba cộng thêm 1 200 USD từ việc bán cổ phiếu.
Sử dụng công thức:
=IF(MOD(COLUMN(C2),3)=0,1200,0)Giải thích:
- `COLUMN(C2)` trả về 3 (cột C).
- `MOD(COLUMN(C2),3)` cho phần dư khi chia số 3 (cột C) cho 3, kết quả 0 → thỏa mãn điều kiện, trả về 1 200 USD; nếu không thì trả về 0.
Công thức này có thể mở rộng để tự động hoá cho bất kỳ vị trí “n‑th” nào bằng cách điều chỉnh giá trị 3.
Ví dụ 4 – Tính tổng giá trị của cột thứ n
Bạn có dữ liệu bán hàng hàng ngày cho nhiều sản phẩm và muốn tính tổng doanh thu cho mỗi sản phẩm vào các ngày cụ thể (ví dụ: mỗi ngày thứ hai, thứ ba,…).
Các bước thực hiện:
- Đặt cột trái nhất thành giá trị 1:
=COLUMN(C3:I3)-COLUMN(C3)+1`COLUMN(C3:I3)` trả về `{3,4,5,6,7,8,9}`; trừ `COLUMN(C3)` (=3) → `{0,1,2,3,4,5,6}`; cộng 1 → `{1,2,3,4,5,6,7}`.
- Dùng `MOD` để xác định cột thứ n (giá trị n được lưu trong ô I3):
=MOD(COLUMN(C3:I3)-COLUMN(C3)+1,I3)- Kiểm tra phần dư có bằng 0 không:
=MOD(COLUMN(C3:I3)-COLUMN(C3)+1,I3)=0- Chuyển TRUE/FALSE thành 1/0 và dùng `SUMPRODUCT` để tính tổng:
=SUMPRODUCT(--(MOD(COLUMN(C3:I3)-COLUMN(C3)+1,I3)=0),C3:I3)
Kết quả: Tổng doanh thu cho sản phẩm A là 985 (n = 1), và các sản phẩm B, C… được tính theo các giá trị n tương ứng trong cột I.
Hàm COLUMN so với hàm COLUMNS
- COLUMN: Trả về số thứ tự cột của một ô tham chiếu. Ví dụ, `=COLUMN(B3)` trả về 2.
- COLUMNS: Trả về số lượng cột trong một mảng hoặc dải ô. Ví dụ, `=COLUMNS(A1:C3)` trả về 3.
Mặc dù tên gần giống nhau, hai hàm phục vụ mục đích khác nhau; hiểu rõ sự khác biệt sẽ giúp tránh các lỗi tính toán.
Hãy thực hành hàm COLUMN và khai thác khả năng của nó trong phân tích dữ liệu. Nó có thể tiết kiệm thời gian và công sức khi làm việc với dữ liệu lớn và phức tạp, đồng thời nâng cao năng suất và hiệu quả. Hãy đón chờ các bài viết về các hàm Excel thú vị khác sắp tới.
