ARRAYFORMULA & LAMBDA: Xử Lý Dữ Liệu Hàng Loạt Không Cần VBA
Tuân Hoang
ARRAYFORMULA và LAMBDA — hai hàm mảng mạnh nhất Google Sheets giúp xử lý hàng nghìn dòng dữ liệu chỉ với một công thức duy nhất, không cần VBA hay Apps Script.
Thay vì kéo công thức xuống 1000 dòng (rủi ro quên, sai, chậm) — một ARRAYFORMULA xử lý cả cột trong tích tắc. LAMBDA đi xa hơn: tạo hàm tùy chỉnh có thể tái sử dụng.
Mục lục:
1. ARRAYFORMULA Là Gì Và Tại Sao Nên Dùng?
Thông thường, khi bạn nhập công thức =B2*C2 vào ô D2, bạn phải kéo xuống D3, D4... D1000 để tính cho các dòng còn lại. Vấn đề:
- Dễ quên kéo công thức khi thêm dòng mới
- File nặng vì có 1000 công thức riêng lẻ
- Ai đó xóa công thức ở một dòng mà không ai biết
- Không áp dụng được cho dòng mới tự động
ARRAYFORMULA giải quyết tất cả: một công thức duy nhất ở D2, tự động áp dụng cho toàn bộ cột D, tự động mở rộng khi có dòng mới.
Lợi ích của ARRAYFORMULA
- File nhẹ hơn (1 công thức thay 1000 công thức)
- Tự động cập nhật khi thêm dòng mới
- Không thể bị xóa nhầm ở dòng giữa
- Dễ maintain và debug
2. Cú Pháp Và Ví Dụ Cơ Bản
// Nhân hai cột — đặt ở D2, tự áp dụng cho D3:D1000
=ARRAYFORMULA(B2:B1000 * C2:C1000)
// Tính VAT 10% cho cả cột
=ARRAYFORMULA(IF(B2:B <> "", B2:B * 0.1, ""))
// Nối họ và tên
=ARRAYFORMULA(IF(A2:A <> "", A2:A & " " & B2:B, ""))
// Shortcut: Ctrl+Shift+Enter tự thêm ARRAYFORMULA
Mẹo: Dùng cột mở (B2:B thay vì B2:B1000)
Dùng B2:B thay vì B2:B1000 — sẽ áp dụng đến hết cột, tự động nhận dòng mới. Nhớ thêm IF(B2:B <> "", ..., "") để tránh công thức hiển thị ở ô trống.
3. ARRAYFORMULA Với IF Và Điều Kiện
// Phân loại theo doanh thu
=ARRAYFORMULA(
IF(E2:E >= 10000000, "🌟 VIP",
IF(E2:E >= 5000000, "⭐ Thường",
IF(E2:E > 0, "🔸 Mới", ""))))
// Tính hoa hồng theo mức doanh số
=ARRAYFORMULA(
IF(E2:E >= 50000000, E2:E * 0.05,
IF(E2:E >= 20000000, E2:E * 0.03,
IF(E2:E > 0, E2:E * 0.01, ""))))
4. ARRAYFORMULA Với VLOOKUP/XLOOKUP
VLOOKUP thông thường không hoạt động với mảng. Dùng ARRAYFORMULA để tra cứu cả cột cùng lúc:
// Tra cứu tên sản phẩm từ mã sản phẩm — cả cột cùng lúc
=ARRAYFORMULA(
IF(A2:A <> "",
IFERROR(VLOOKUP(A2:A, DanhMuc!A:C, 2, 0), "Không tìm thấy"),
""))
// XLOOKUP với ARRAYFORMULA (Google Sheets hỗ trợ tốt hơn)
=ARRAYFORMULA(
IF(A2:A <> "",
XLOOKUP(A2:A, DanhMuc!A:A, DanhMuc!B:B, "N/A"),
""))
5. LAMBDA — Tạo Hàm Tùy Chỉnh Không Cần Script
LAMBDA cho phép định nghĩa hàm tùy chỉnh ngay trong ô công thức. Kết hợp với Name Manager (Defined Names), bạn có thể đặt tên hàm và dùng lại nhiều lần:
// LAMBDA inline — tính thuế GTGT
=LAMBDA(gia_tri, gia_tri * 1.1)(A1)
// LAMBDA với nhiều tham số
=LAMBDA(don_gia, so_luong, chiet_khau,
don_gia * so_luong * (1 - chiet_khau)
)(B2, C2, D2)
// Đặt tên hàm qua Data → Named ranges (hoặc Name Manager):
// Tên: TINH_THANH_TIEN
// Giá trị: =LAMBDA(don_gia, sl, ck, don_gia * sl * (1-ck))
// Sau đó dùng:
=TINH_THANH_TIEN(B2, C2, D2)
Tại sao LAMBDA quan trọng?
Trước LAMBDA, để tái sử dụng logic phức tạp, bạn phải lặp lại công thức dài ở nhiều ô — rất khó maintain. Với LAMBDA đặt tên, bạn định nghĩa một lần, dùng mọi nơi, chỉnh sửa một chỗ là cập nhật toàn bộ.
6. MAP, REDUCE, SCAN — Xử Lý Mảng Nâng Cao
MAP — Áp Dụng Hàm Cho Từng Phần Tử
// Tăng 10% mỗi giá trị trong cột B
=MAP(B2:B100, LAMBDA(x, x * 1.1))
// Xử lý text — viết hoa chữ cái đầu mỗi từ
=MAP(A2:A100, LAMBDA(ten, PROPER(ten)))
// MAP với 2 mảng — tính thành tiền
=MAP(B2:B100, C2:C100, LAMBDA(gia, sl, gia * sl))
REDUCE — Tính Tổng Hợp Dồn
// Tính tổng cộng dồn (running total)
=SCAN(0, B2:B100, LAMBDA(acc, x, acc + x))
// Tìm giá trị lớn nhất cộng dồn
=REDUCE(0, B2:B100, LAMBDA(acc, x, MAX(acc, x)))
// Nối chuỗi từ mảng
=REDUCE("", A2:A10, LAMBDA(acc, x, IF(acc="", x, acc & ", " & x)))
7. BYROW Và BYCOL — Tính Theo Hàng/Cột
// BYROW: tính tổng mỗi hàng (doanh thu theo tháng cho từng SP)
=BYROW(B2:M10, LAMBDA(hang, SUM(hang)))
// BYCOL: tính trung bình mỗi cột (doanh thu trung bình theo tháng)
=BYCOL(B2:M100, LAMBDA(cot, AVERAGE(cot)))
// BYROW tìm giá trị lớn nhất mỗi hàng
=BYROW(B2:M10, LAMBDA(hang, MAX(hang)))
8. Case Study — Bảng Lương Tự Động
Xây dựng bảng lương tự động với ARRAYFORMULA + LAMBDA — từ lương cơ bản tính ra các khoản trừ, phụ cấp và lương net:
// Cột E: Lương thực nhận (sau khi trừ BHXH 10.5% và thuế TNCN)
=ARRAYFORMULA(
IF(A2:A <> "",
LET(
luong_cb, B2:B,
phu_cap, C2:C,
ngay_cong, D2:D,
luong_gross, (luong_cb + phu_cap) * ngay_cong / 26,
bhxh, luong_gross * 0.105,
thu_nhap_chiu_thue, luong_gross - bhxh - 11000000,
thue, IF(thu_nhap_chiu_thue <= 0, 0, thu_nhap_chiu_thue * 0.05),
luong_gross - bhxh - thue
),
""))
Template Bảng Lương Google Sheets Tự Động
Tại SheetStore.vn có template bảng lương đầy đủ: tính BHXH, thuế TNCN, phụ cấp theo ca, xuất phiếu lương tự động — dùng ARRAYFORMULA và LAMBDA đã được tối ưu sẵn.
Xem Template Bảng Lương →9. FAQ
ARRAYFORMULA có làm chậm Google Sheets không?
Không — thực ra nhanh hơn kéo công thức xuống nhiều dòng. Với dữ liệu cực lớn (>500K ô) thì dùng Apps Script thay thế.
LAMBDA có hoạt động với Excel không?
Có — Excel 365 hỗ trợ LAMBDA từ 2022. Cú pháp tương tự nhưng có thể có sự khác biệt nhỏ.
Khi nào dùng ARRAYFORMULA thay vì Apps Script?
ARRAYFORMULA khi logic có thể viết bằng công thức. Apps Script khi cần gửi email, gọi API, logic phức tạp nhiều bước, hoặc dữ liệu >100K dòng.
Bài Tiếp Theo: Kết Nối Google Sheets Với API
Học cách kết nối Google Sheets với dữ liệu bên ngoài qua ImportJSON, ImportXML và UrlFetchApp — biến Sheets thành trung tâm dữ liệu thực sự.
Đọc bài: Kết Nối API →Chia sẻ bài viết:
Tuân Hoang
Đội ngũ SheetStore
Bạn thấy bài viết hữu ích?
Đăng ký nhận thông báo khi có bài viết mới.