Bảng tính 2 — Công thức & Hàm nâng cao

Công thức là ngôn ngữ phân tích trong bảng tính

Trong bài Tổng quan bảng tính nâng cao chúng ta đã thấy Excel và Google Sheets là công cụ phân tích đầu tiên của hầu hết data analyst ngân hàng — nhanh, trực quan, ai cũng đọc được. Nhưng ranh giới giữa một người "dùng Excel" và một người phân tích bằng Excel nằm ở công thức. Kéo chuột tô màu thì ai cũng làm được; viết được một công thức tra lãi suất theo kỳ hạn, phân loại nhóm nợ, hay tính lãi dồn cho cả sổ tiết kiệm — đó mới là năng lực phân tích.

Bài này đi sâu vào bộ công thức mà một analyst ngân hàng cần dùng thành thạo hằng ngày: tra cứu, hàm điều kiện & tổng hợp, xử lý văn bản và ngày tháng, xử lý lỗi, và bộ hàm mảng động hiện đại. Xuyên suốt, ta lấy ví dụ từ nghiệp vụ thật: bảng lãi suất, danh mục tài khoản, sao kê giao dịch, phân loại nợ theo Thông tư 11/31.

Một quy ước cần nắm ngay: công thức Excel không phải SQL. Trong bài này các khối công thức bảng tính chỉ để minh hoạ — không chạy được trên SQL Builder. Cuối bài có một câu SQL nguồn (đúng schema sandbox) đánh dấu ▶ để bạn thấy dữ liệu tổng hợp thường được kéo ra từ đâu trước khi đổ vào bảng tính.

Tra cứu (lookup) — trái tim của mọi báo cáo

Tra cứu là thao tác "lấy giá trị ở bảng B theo khoá ở bảng A": có mã tài khoản, tra ra tên chi nhánh; có kỳ hạn, tra ra lãi suất; có nhóm nợ, tra ra tỷ lệ trích lập dự phòng. Có ba cách chính, và chọn đúng cách quyết định cả tốc độ lẫn độ bền của file.

VLOOKUP — quen thuộc nhưng nhiều bẫy

VLOOKUP tìm một giá trị ở cột đầu tiên của vùng, rồi trả về giá trị ở cột thứ n tính từ trái.

' Tra tên chi nhánh theo mã chi nhánh (bảng ChiNhanh: cột 1 = mã, cột 3 = tên)
=VLOOKUP(A2, ChiNhanh!$A$2:$C$500, 3, FALSE)

Tham số cuối FALSE (hay 0) nghĩa là khớp chính xác — luôn dùng nó cho tra cứu khoá. TRUE là khớp gần đúng, chỉ dùng khi bảng đã sắp xếp tăng dần (ví dụ tra bậc lãi suất theo số dư).

Ba nhược điểm khiến VLOOKUP hay gây lỗi:

• Chỉ tra được sang phải: khoá phải nằm ở cột trái nhất của vùng. Muốn tra "ngược" (có tên, tìm mã ở cột bên trái) là bó tay.
• Cột đích tính bằng số thứ tự cố định (3). Chèn/xoá một cột giữa vùng là công thức trả sai giá trị mà không báo lỗi — cực nguy hiểm.
• Chậm khi tra nhiều lần trên bảng lớn, vì mỗi công thức quét lại từ đầu.

INDEX/MATCH — linh hoạt hơn

Cặp INDEX + MATCH tách việc "tìm vị trí" khỏi việc "lấy giá trị", nên tra được mọi hướng và không vỡ khi chèn cột.

' Có mã chi nhánh (A2), lấy tên ở cột TenCN — MATCH tìm dòng, INDEX lấy ô
=INDEX(TenCN, MATCH(A2, MaCN, 0))

MATCH(A2, MaCN, 0) trả về số thứ tự dòng nơi tìm thấy A2 trong cột mã; INDEX(TenCN, ...) lấy đúng ô đó ở cột tên. Vì hai cột được tham chiếu độc lập, khoá có thể ở bên phải giá trị cần lấy, và chèn cột không làm sai kết quả. Đây là chuẩn "an toàn" trước khi có XLOOKUP.

XLOOKUP — hiện đại, nên là mặc định

XLOOKUP (Excel 365, Google Sheets) gộp mọi ưu điểm và bỏ mọi nhược điểm trên:

' Cú pháp: XLOOKUP(giá_trị_tìm, mảng_tra, mảng_trả, [nếu_không_thấy], ...)
=XLOOKUP(A2, MaCN, TenCN, "Không rõ CN")

Điểm mạnh:

• Mặc định khớp chính xác — không cần nhớ gõ FALSE, hết một lớp lỗi im lặng.
• Có tham số if_not_found ngay trong hàm ("Không rõ CN") — thay cho việc bọc IFERROR bên ngoài.
• Tra được cả hai chiều, mảng tra và mảng trả tách rời như INDEX/MATCH.
• Trả về nhiều cột cùng lúc: nếu mảng_trả là vùng nhiều cột, kết quả spill (tràn) ra nhiều ô. Ví dụ tra một mã CIF trả về cả tên, chi nhánh, phân khúc trong một công thức.
• Tra từ dưới lên, khớp theo khoảng (bậc lãi suất) qua các tham số match_mode, search_mode.

So sánh nhanh ba cách

Nguyên tắc: dùng XLOOKUP nếu môi trường hỗ trợ; nếu file phải mở trên Excel cũ (nhiều ngân hàng còn 2016/2019), lùi về INDEX/MATCH; chỉ giữ VLOOKUP khi kế thừa file cũ.

Tham chiếu tuyệt đối/tương đối & named range

Khi copy công thức, Excel tự dịch tham chiếu. A1 là tương đối: kéo xuống một dòng thành A2. Thêm $ để khoá:

• A1 — tương đối cả cột lẫn dòng.
• $A$1 — tuyệt đối, không đổi khi copy đi đâu (dùng cho vùng bảng tra, hằng số).
• $A1 / A$1 — khoá một chiều (khoá cột / khoá dòng) — mấu chốt để viết một công thức rồi kéo cho cả bảng ma trận.

Trong ví dụ VLOOKUP ở trên, vùng ChiNhanh!$A$2:$C$500 khoá tuyệt đối để khi kéo xuống 500 dòng vùng tra vẫn cố định.

Named range (đặt tên vùng) làm công thức đọc như tiếng người: thay Sheet2!$B$2:$B$5000 bằng SoDu. Đặt qua Name Box hoặc Formula → Define Name. Named range luôn tuyệt đối, nên =SUMIFS(SoDu, ChiNhanh, "HN") vừa gọn vừa tự tài liệu hoá. Trong ngân hàng, nên đặt tên cho bảng tham số (bảng lãi suất, ma trận nhóm nợ) để công thức khắp file trỏ về một nguồn duy nhất.

Hàm điều kiện & tổng hợp

IF và IFS

IF(điều_kiện, giá_trị_đúng, giá_trị_sai) là hàm rẽ nhánh cơ bản. Lồng nhiều IF để phân loại thì khó đọc; IFS giải quyết:

' Phân loại nhóm nợ theo số ngày quá hạn (DPD) — Thông tư 11/31
=IFS(B2<=0, "Nhóm 1", B2<=90, "Nhóm 2", B2<=180, "Nhóm 3",
     B2<=360, "Nhóm 4", TRUE, "Nhóm 5")

IFS xét lần lượt từng cặp điều_kiện/kết_quả và trả kết quả đầu tiên đúng; TRUE ở cuối là nhánh "còn lại". Rõ ràng hơn nhiều so với lồng năm tầng IF.

SUMIF(S), COUNTIF(S), AVERAGEIF(S)

Bộ *IF tính tổng/đếm/trung bình có điều kiện. Dạng số nhiều (*IFS) hỗ trợ nhiều tiêu chí:

' Tổng số dư tài khoản VND tại chi nhánh Hà Nội
=SUMIFS(SoDu, LoaiTien, "VND", ChiNhanh, "HN")

' Đếm số tài khoản có số dư > 1 tỷ, còn hoạt động
=COUNTIFS(SoDu, ">1000000000", TrangThai, "Active")

' Số dư trung bình của khách phân khúc Priority
=AVERAGEIFS(SoDu, PhanKhuc, "Priority")

Điều kiện có thể dùng toán tử trong chuỗi (">1000000000"), ký tự đại diện ("HN*"), hoặc trỏ tới một ô (">"&Nguong). Với SUMIFS, vùng tính (SoDu) đứng trước, sau đó là các cặp vùng/điều kiện.

SUMPRODUCT — con dao đa năng

SUMPRODUCT nhân từng phần tử của các mảng rồi cộng lại. Nó mạnh ở hai việc: tính có trọng số và đếm/tính theo điều kiện phức tạp (kể cả trên Excel cũ không có *IFS).

' Lãi suất bình quân gia quyền theo số dư (weighted average)
=SUMPRODUCT(SoDu, LaiSuat) / SUM(SoDu)

' Tổng số dư tài khoản VND có số dư > 1 tỷ (điều kiện dạng mảng logic)
=SUMPRODUCT((LoaiTien="VND") * (SoDu>1000000000) * SoDu)

Ở ví dụ hai, các biểu thức so sánh trả mảng TRUE/FALSE, nhân với nhau ép thành 1/0, rồi nhân với SoDu — chỉ những dòng thoả mọi điều kiện mới góp giá trị. Đây là mẫu "SUMIFS bằng tay" rất linh hoạt.

Xử lý văn bản — làm sạch mã tài khoản, CIF

Dữ liệu ngân hàng đầy mã: số tài khoản, CIF, mã sản phẩm — và chúng thường bẩn (thừa khoảng trắng, sai độ dài, lẫn ký tự ẩn).

=TRIM(A2)                     ' bỏ khoảng trắng thừa đầu/cuối/kép
=CLEAN(A2)                    ' bỏ ký tự điều khiển không in được
=LEFT(A2, 3)                  ' 3 ký tự đầu (mã chi nhánh trong STK)
=RIGHT(A2, 4)                 ' 4 số cuối
=MID(A2, 4, 6)               ' 6 ký tự từ vị trí 4
=SUBSTITUTE(A2, "-", "")     ' bỏ dấu gạch trong mã
=TEXT(A2, "0000000000")      ' ép số về chuỗi 10 chữ số, giữ số 0 đầu
=CONCAT(B2, "-", C2)         ' nối chuỗi
=TEXTJOIN("; ", TRUE, B2:B10) ' nối một dải, bỏ ô rỗng

Một mẫu chuẩn hoá CIF hay dùng: =TEXT(TRIM(CLEAN(A2)),"00000000") — làm sạch rồi ép về đúng 8 chữ số có số 0 dẫn đầu, để khoá tra cứu giữa các file luôn khớp. Số 0 đầu là thủ phạm kinh điển khiến VLOOKUP "không thấy" dù mắt nhìn giống hệt.

Ngày tháng — kỳ báo cáo, kỳ hạn, lãi

Ngày trong Excel là số sê-ri (số ngày kể từ 1/1/1900), nên có thể cộng trừ như số. Bộ hàm cần thuộc:

=DATE(2026, 7, 1)            ' dựng ngày từ năm/tháng/ngày
=EOMONTH(A2, 0)             ' ngày cuối tháng của A2 (chốt kỳ báo cáo)
=EOMONTH(A2, -1)+1          ' ngày đầu tháng
=EDATE(A2, 6)              ' cộng 6 tháng (ngày đáo hạn kỳ hạn 6 tháng)
=DATEDIF(A2, B2, "d")      ' số ngày giữa hai mốc (DPD quá hạn)
=YEARFRAC(A2, B2, 3)       ' phần năm giữa hai ngày (base 3 = Actual/365)
=NETWORKDAYS(A2, B2, Le)   ' số ngày làm việc, trừ cuối tuần & ngày lễ

• EOMONTH/EDATE là bộ đôi của kế toán: chốt cuối kỳ, tính đáo hạn, dịch kỳ.
• YEARFRAC cho phần năm để tính lãi đơn: lãi = gốc × lãi_suất × YEARFRAC(ngày_gửi, ngày_tính, 3). Chọn đúng basis (Actual/365 vs 30/360) rất quan trọng vì nó đổi số tiền lãi.
• NETWORKDAYS để đếm ngày làm việc cho SLA xử lý hồ sơ, giá trị ngày công.

Xử lý lỗi — IFERROR / IFNA

Tra cứu không thấy trả #N/A, chia cho 0 trả #DIV/0!. Để báo cáo không "rỗ" lỗi:

=IFERROR(VLOOKUP(A2, Bang, 3, FALSE), "Chưa có")   ' bắt MỌI lỗi
=IFNA(XLOOKUP(A2, Ma, Ten), "Không tìm thấy")      ' chỉ bắt #N/A

Ưu tiên IFNA cho tra cứu: nó chỉ nuốt #N/A (không tìm thấy), còn để lộ các lỗi thật khác (#REF!, #VALUE!) — tránh việc IFERROR che luôn cả lỗi công thức sai mà bạn cần biết. Với XLOOKUP thì tốt nhất dùng thẳng tham số if_not_found.

Hàm mảng động — thay hàng loạt thao tác thủ công

Excel 365 và Google Sheets có nhóm dynamic array: một công thức trả về nhiều ô, tự spill (tràn) xuống dải kết quả, và tự co giãn khi dữ liệu đổi. Chúng thay cho rất nhiều thao tác lọc/sắp/copy thủ công:

=FILTER(BangTK, (SoDu>1000000000)*(LoaiTien="VND"))  ' lọc theo nhiều điều kiện
=SORT(FILTER(BangTK, TrangThai="Active"), 4, -1)     ' lọc rồi sắp giảm theo cột 4
=UNIQUE(ChiNhanh)                                     ' danh sách chi nhánh không trùng
=SEQUENCE(12, 1, DATE(2026,1,31),,)                   ' sinh dãy (kết hợp EDATE cho 12 kỳ)

Sức mạnh nằm ở kết hợp: =SORT(UNIQUE(FILTER(...))) tạo ra một bảng động chỉ bằng một ô — trước kia phải Remove Duplicates + Sort + Filter bằng tay và làm lại mỗi lần dữ liệu thay đổi. Tham chiếu tới toàn vùng spill bằng dấu #: =A2# lấy cả dải mà công thức ở A2 tràn ra.

LAMBDA & LET — công thức tái sử dụng, dễ đọc

LET đặt tên cho biểu thức con ngay trong công thức: tính một lần, dùng nhiều nơi, đọc dễ hơn và chạy nhanh hơn (không tính lặp).

' Lãi đơn: gọn và không lặp phép tính YEARFRAC
=LET(goc, B2, ls, C2, ky, YEARFRAC(D2, E2, 3), ROUND(goc*ls*ky, 0))

LAMBDA cho phép tự định nghĩa hàm: gói một công thức thành hàm có tham số, đặt tên qua Name Manager rồi tái dùng khắp file.

' Định nghĩa TinhLai (đặt tên trong Name Manager):
=LAMBDA(goc, ls, tu, den, ROUND(goc*ls*YEARFRAC(tu,den,3), 0))
' Dùng: =TinhLai(B2, C2, D2, E2)

Nhờ vậy logic tính lãi chỉ viết một chỗ; sửa quy tắc thì sửa một nơi thay vì rà hàng trăm ô.

Hiệu năng & audit công thức

Khi file phình lên vài chục nghìn dòng, công thức xấu làm Excel treo. Vài nguyên tắc:

• Tránh hàm volatile (NOW, TODAY, OFFSET, INDIRECT, RAND): chúng tính lại mỗi lần bất kỳ ô nào đổi, kéo cả file chậm theo cấp số nhân.
• Hạn chế tham chiếu toàn cột (A:A): dùng vùng có giới hạn hoặc Table (bảng có tên tự co giãn) thay vì quét cả triệu dòng.
• Ưu tiên SUMIFS/INDEX-MATCH/XLOOKUP hơn mảng SUMPRODUCT khổng lồ khi có thể; và tra cứu một lần rồi tham chiếu lại thay vì tra lặp.
• Tắt tính tự động (Formula → Calculation → Manual) khi đang sửa file nặng, bật lại khi xong.

Audit (soi công thức) trên tab Formulas:

• Trace Precedents/Dependents: vẽ mũi tên ô nào nuôi ô nào.
• Evaluate Formula: chạy từng bước để thấy công thức tính ra sai ở đâu.
• Show Formulas (Ctrl+`): hiện toàn bộ công thức thay vì kết quả — rà nhanh cả sheet.

Cạm bẫy kinh điển

• VLOOKUP cột trái: khoá phải ở cột trái nhất; muốn tra ngược thì INDEX/MATCH hoặc XLOOKUP.
• Số lưu dạng text: mã/số nhập từ hệ thống hay là chuỗi (góc ô có tam giác xanh). 12345 (text) không khớp 12345 (số) trong tra cứu. Chuẩn hoá bằng VALUE() hoặc TEXT() cho cả hai vế trước khi so.
• Số 0 dẫn đầu của STK/CIF bị mất khi đổ ra số → dùng TEXT(...,"000...") giữ nguyên độ dài.
• Ngày lệch locale: 01/07/2026 là 1 tháng 7 (dd/mm) hay 7 tháng 1 (mm/dd)? Import từ file nước ngoài rất hay lệch. Luôn kiểm bằng cách canh phải (ngày thật là số nên canh phải) và dùng DATE() khi dựng ngày thay vì gõ chuỗi.
• Khớp gần đúng vô tình: quên FALSE trong VLOOKUP trên bảng chưa sắp xếp trả giá trị sai mà không báo lỗi.

Ví dụ thực chiến gộp lại

Tra lãi suất theo kỳ hạn (bảng LaiSuat: cột kỳ hạn tháng, cột lãi suất), khớp bậc:

=XLOOKUP(KyHan, KyHan_Bang, LS_Bang, "Ngoài biểu", -1, 1)

-1 = nếu không khớp đúng lấy bậc nhỏ hơn gần nhất; 1 = tìm tăng dần — đúng logic "kỳ hạn rơi vào bậc nào".

Phân loại nợ + trích lập trong một chuỗi công thức:

' Nhóm nợ theo DPD rồi tra tỷ lệ dự phòng từ bảng tham số
=LET(dpd, DATEDIF(NgayQuaHan, NgayChot, "d"),
     nhom, IFS(dpd<=0,1, dpd<=90,2, dpd<=180,3, dpd<=360,4, TRUE,5),
     tyle, XLOOKUP(nhom, NhomNo_Bang, TyLeDP_Bang),
     ROUND(SoDuNo * tyle, 0))

Tính lãi dồn cho cả sổ (kéo công thức xuống, hoặc dùng LAMBDA TinhLai ở trên): mỗi dòng lấy gốc × lãi_suất tra được × phần năm YEARFRAC — kết quả là bảng lãi phải trả theo từng khách.

Dữ liệu nguồn thường đến từ SQL

Bảng tính hiếm khi là nguồn gốc — analyst thường kéo dữ liệu tổng hợp từ kho rồi mới xử lý bằng công thức. Ví dụ, số dư gộp theo thành phố và loại tiền (đúng schema sandbox) thường là điểm khởi đầu đổ vào file để tra cứu và tính toán tiếp:

-- ▶ Chạy được
SELECT
  c.city                                   AS thanh_pho,
  a.currency                               AS loai_tien,
  COUNT(DISTINCT a.id)                     AS so_tai_khoan,
  SUM(a.balance)                           AS tong_so_du,
  ROUND(AVG(a.balance)::numeric, 2)        AS so_du_tb
FROM accounts a
JOIN customers c ON c.id = a.customer_id
GROUP BY c.city, a.currency
ORDER BY thanh_pho, loai_tien;

Kết quả này đổ vào bảng tính, rồi bạn dùng XLOOKUP/SUMIFS để đối chiếu, TEXT/TRIM để chuẩn hoá khoá, và FILTER/SORT để dựng báo cáo động — đúng luồng "SQL kéo thô, bảng tính tinh chỉnh".

Ghi nhớ

• Tra cứu: mặc định dùng XLOOKUP (exact sẵn, if_not_found, trả nhiều cột spill); lùi về INDEX/MATCH trên Excel cũ; hạn chế VLOOKUP (không tra ngược, vỡ khi chèn cột, dễ quên FALSE).
• $ và named range: khoá tham chiếu đúng chỗ trước khi copy; đặt tên bảng tham số để công thức tự tài liệu hoá và trỏ một nguồn.
• Điều kiện & tổng hợp: IFS thay IF lồng; SUMIFS/COUNTIFS/AVERAGEIFS cho nhiều tiêu chí; SUMPRODUCT cho tính có trọng số và điều kiện dạng mảng.
• Văn bản & ngày: TRIM/CLEAN/TEXT chuẩn hoá mã tài khoản/CIF (giữ số 0 đầu!); EOMONTH/EDATE/YEARFRAC/NETWORKDAYS cho kỳ báo cáo, đáo hạn, tính lãi, SLA.
• Lỗi: ưu tiên IFNA cho tra cứu (chỉ nuốt #N/A), hoặc dùng thẳng if_not_found của XLOOKUP.
• Mảng động: FILTER/SORT/UNIQUE/SEQUENCE + spill thay hàng loạt thao tác tay; kết hợp lồng nhau cho báo cáo tự co giãn.
• LET/LAMBDA: đặt tên biểu thức con và tự định nghĩa hàm để logic (như tính lãi) viết một nơi, tái dùng khắp file.
• Hiệu năng & bẫy: tránh volatile và tham chiếu toàn cột; coi chừng số-lưu-dạng-text, số 0 đầu, và ngày lệch locale.

Đọc tiếp

• Bảng tính 1 — Tổng quan
• Bảng tính 3 — PivotTable & phân tích
• Bảng tính 4 — Power Query trong Excel
• Bảng tính 8 — Thực hành ngân hàng