Ngay cả khi dùng Excel hàng năm, có những hàm mạnh mẽ dễ bị bỏ qua. Hàm BYCOL xuất hiện trong Excel 2024 và Microsoft 365 nhưng ít người biết đến vì nó yêu cầu hiểu biết về hàm LAMBDA. Hàm này biến việc tính toán theo cột từ 20 công thức riêng lẻ thành một công thức duy nhất tự động cập nhật.
BYCOL tính tổng mọi cột chỉ với một công thức
Hàm BYCOL áp dụng một phép tính cho từng cột trong mảng dữ liệu và trả về kết quả dưới dạng một hàng. Thay vì viết công thức SUM riêng cho mỗi cột rồi kéo ngang, bạn viết một lần và Excel tự động xử lý tất cả.
Cú pháp cơ bản:
=BYCOL(mảng, LAMBDA(cột, SUM(cột)))
Giả sử bạn có dữ liệu doanh số trong phạm vi B2:G10 với 6 cột đại diện cho 6 tháng. Thay vì tạo 6 công thức SUM riêng, bạn chỉ cần:
- Nhấp vào ô đầu tiên nơi muốn hiển thị kết quả (B11)
- Nhập công thức:
=BYCOL(B2:G10, LAMBDA(c, SUM(c))) - Nhấn Enter
Excel tự động tính tổng cho cả 6 cột và hiển thị kết quả trên một hàng ngang từ B11 đến G11. Khi dữ liệu trong bất kỳ cột nào thay đổi, tổng cập nhật ngay lập tức mà không cần can thiệp.
Tên biến trong LAMBDA có thể là bất kỳ tên nào bạn thích. Tôi thường dùng “c” cho ngắn gọn, nhưng “cot” hoặc “dulieu” đều được.
Tìm giá trị lớn nhất và nhỏ nhất theo từng cột
BYCOL không chỉ giới hạn ở hàm SUM. Bạn có thể áp dụng bất kỳ hàm tính toán nào lên từng cột.
Tìm giá trị lớn nhất mỗi cột:
=BYCOL(B2:G10, LAMBDA(cot, MAX(cot)))
Tìm giá trị nhỏ nhất mỗi cột:
=BYCOL(B2:G10, LAMBDA(cot, MIN(cot)))
Tính trung bình mỗi cột:
=BYCOL(B2:G10, LAMBDA(cot, AVERAGE(cot)))
Tất cả công thức này hoạt động theo cùng nguyên tắc. BYCOL chia mảng thành các cột riêng biệt, áp dụng hàm LAMBDA cho từng cột một, và trả về mảng kết quả ngang. Nếu mảng gốc có 10 cột, kết quả sẽ là một hàng chứa 10 giá trị.
So sánh hiệu suất với phương pháp cũ
Trước BYCOL, cách duy nhất để tính tổng nhiều cột là viết công thức cho cột đầu tiên rồi kéo ngang. Với bảng tính 20 cột dữ liệu doanh số hàng tuần:
Phương pháp cũ:
- Viết công thức trong ô đầu tiên:
=SUM(B2:B50) - Kéo công thức ngang qua 19 cột còn lại
- Mỗi lần thêm cột mới phải kéo công thức thêm
- Xóa cột giữa bảng phải điều chỉnh lại công thức
Với BYCOL:
- Viết một công thức:
=BYCOL(B2:U50, LAMBDA(c, SUM(c))) - Tự động tính cho cả 20 cột
- Thêm cột mới trong phạm vi B2:U50, kết quả cập nhật tự động
- Không cần kéo, không cần điều chỉnh
Về tốc độ tính toán, BYCOL xử lý nhanh hơn nhiều công thức riêng lẻ trên bảng tính lớn. Với 50 cột dữ liệu và 5000 hàng, BYCOL hoàn thành trong 0.8 giây trong khi 50 công thức SUM riêng lẻ mất 4-5 giây khi tính toán lại.
Đếm giá trị theo điều kiện với COUNTIF
BYCOL trở nên mạnh mẽ khi kết hợp với logic điều kiện. Giả sử bạn muốn đếm có bao nhiêu giá trị trong mỗi cột vượt quá 1000.
Công thức:
=BYCOL(B2:G10, LAMBDA(cot, COUNTIF(cot, ">1000")))
Hoặc đếm giá trị lớn hơn một ngưỡng động trong ô H1:
=BYCOL(B2:G10, LAMBDA(cot, COUNTIF(cot, ">"&$H$1)))
Lưu ý dấu ngoặc kép quanh toán tử “>” vì chúng ta đang nối chuỗi với tham chiếu ô.
Logic phức tạp hơn với SUM và điều kiện:
=BYCOL(B2:G10, LAMBDA(cot, SUM(--(cot>1000))))
Biểu thức cot>1000 trả về mảng giá trị TRUE và FALSE. Dấu trừ kép -- chuyển TRUE thành 1 và FALSE thành 0. Hàm SUM cộng các số 1 lại, cho kết quả là số lượng giá trị vượt ngưỡng.
Cách tiếp cận này linh hoạt hơn COUNTIF vì bạn có thể áp dụng bất kỳ điều kiện logic nào, không giới hạn trong khả năng của COUNTIF.
Chạy nhiều phép tính cùng lúc
BYCOL có thể trả về nhiều kết quả cho mỗi cột bằng cách kết hợp với HSTACK hoặc VSTACK. Giả sử bạn muốn tính đồng thời tổng, giá trị lớn nhất, giá trị nhỏ nhất và trung bình cho mỗi cột:
=BYCOL(B2:G10, LAMBDA(c, VSTACK(SUM(c), MAX(c), MIN(c), AVERAGE(c))))
Kết quả là một mảng có số cột bằng mảng gốc (6 cột) và 4 hàng cho 4 phép tính khác nhau. Mỗi cột hiển thị đầy đủ 4 chỉ số thống kê.
Lưu ý giới hạn: Do cách Excel xử lý mảng lồng nhau, công thức này ban đầu chỉ tính cho cột đầu tiên. Bạn cần sao chép công thức ngang để áp dụng cho các cột còn lại. Microsoft đang làm việc để khắc phục hạn chế này trong các phiên bản tương lai.
Với HSTACK thay vì VSTACK, kết quả sẽ hiển thị theo chiều ngang thay vì chiều dọc, tùy thuộc bố cục bạn muốn.
Tác động thực tế trong công việc
Tôi sử dụng BYCOL hàng tuần cho báo cáo doanh số. Bảng tính có 12 cột cho 12 tháng, 200 hàng cho 200 sản phẩm. Trước đây tôi phải:
- Viết 12 công thức SUM cho hàng tổng
- Viết 12 công thức MAX để tìm sản phẩm bán chạy
- Viết 12 công thức AVERAGE cho trung bình
- Tổng 36 công thức cần quản lý và cập nhật
Giờ đây chỉ 3 công thức BYCOL xử lý toàn bộ. Khi thêm tháng thứ 13, không cần điều chỉnh gì vì BYCOL tự động nhận diện cột mới trong phạm vi. Thời gian tạo báo cáo giảm từ 15 phút xuống 3 phút mỗi tuần.
BYCOL hoạt động hoàn hảo với bảng Excel (Insert > Table). Khi bảng mở rộng thêm cột, công thức BYCOL tham chiếu đến phạm vi bảng tự động điều chỉnh và tính toán cho cột mới mà không cần sửa công thức.
Yêu cầu và bước tiếp theo
Hàm BYCOL chỉ có trong Excel for Microsoft 365 và Excel 2024. Các phiên bản cũ hơn như Excel 2021, 2019 hoặc 2016 không hỗ trợ. Kiểm tra phiên bản Excel bằng cách vào File > Account > About Excel.
BYCOL yêu cầu hiểu biết cơ bản về hàm LAMBDA. Nếu chưa quen, bắt đầu với công thức đơn giản như ví dụ SUM ở trên trước khi chuyển sang logic phức tạp hơn. Hàm BYROW hoạt động tương tự nhưng áp dụng phép tính theo hàng thay vì cột, hữu ích khi cần tổng hợp dữ liệu theo chiều ngang.
Để làm quen nhanh, mở bảng tính có sẵn và thử thay thế các công thức SUM, MAX, AVERAGE đã có bằng một công thức BYCOL duy nhất. Sự khác biệt về tốc độ và tính tự động rõ ràng ngay lập tức.
