Bảng tính 8 — Thực chiến bảng tính trong ngân hàng

Từ kỹ năng rời rạc đến năng lực thực chiến

Bảy bài trước của series đã trang bị cho bạn từng mảnh ghép: công thức & hàm nâng cao, PivotTable, Power Query, data model & DAX, dashboard và tự động hoá. Nhưng trong công việc thật, không ai giao cho bạn một bài "hãy dùng SUMIFS". Người ta giao một bài toán: "tuần sau họp Ban điều hành, cần báo cáo huy động — dư nợ — CASA — nợ xấu của 12 chi nhánh, bấm một nút là ra".

Bài kết này gom mọi kỹ năng quanh năm bài toán thực chiến mà data analyst ngân hàng gặp thường xuyên. Mỗi bài toán trình bày theo bốn lát: mục tiêu → dữ liệu → kỹ thuật bảng tính → kết quả. Xuyên suốt là một use case cụ thể — báo cáo kết quả kinh doanh (KQKD) cấp chi nhánh. Cuối bài là checklist kỹ năng, lộ trình trưởng thành, và khi nào nên bỏ bảng tính để lên SQL/warehouse/BI.

Nguyên tắc giữ suốt bài: các khối công thức Excel/DAX chỉ để minh hoạ tư duy, không chạy trên SQL Builder. Chỉ câu SQL đúng schema sandbox mới đánh dấu ▶ Chạy được.

Bản đồ: kỹ năng nào giải bài toán nào

Nhìn bản đồ này bạn sẽ thấy: Power Query và data model là xương sống của báo cáo định kỳ; công thức là công cụ chủ lực của mô hình tài chính và đối soát; tự động hoá cắt ngang mọi bài toán cần lặp lại.

Bài toán 1 — Báo cáo quản trị định kỳ

Mục tiêu. Mỗi đầu tháng, Khối Tài chính cần bộ báo cáo hợp nhất 12 chi nhánh gồm bốn chỉ tiêu cốt lõi: huy động (số dư tiền gửi), dư nợ (số dư cho vay), CASA (tỷ lệ tiền gửi không kỳ hạn trên tổng huy động) và nợ xấu (dư nợ nhóm 3-5 trên tổng dư nợ). Yêu cầu: refresh một nút, không sửa công thức thủ công.

Dữ liệu. Mỗi chi nhánh xuất một file HĐ_KD_<mã CN>_<tháng>.xlsx với cùng cấu trúc: tài khoản, loại sản phẩm, số dư, nhóm nợ. Có thêm bảng danh mục chi nhánh (mã → tên → vùng) và bảng phân loại sản phẩm (sản phẩm → nhóm chỉ tiêu). Dữ liệu bẩn kinh điển: tên chi nhánh viết hoa/thường lẫn lộn, số dư có định dạng text, có dòng tổng cộng lẫn vào dữ liệu.

Kỹ thuật bảng tính. Đây là bài toán mà Power Query sinh ra để giải:

1. Get Data → From Folder: trỏ vào thư mục chứa 12 file, Power Query tự append (gộp dọc) thành một bảng phẳng. Mỗi tháng chỉ cần thả file mới vào thư mục.
2. Làm sạch trong Query: Trim/Clean tên, ép kiểu số dư về decimal, lọc bỏ dòng tổng cộng (Text.StartsWith "Tổng"), thêm cột nguồn từ tên file để biết dữ liệu thuộc tháng nào.
3. Nạp vào Data Model thay vì vào sheet — dữ liệu lớn không phình file, và có thể tạo quan hệ.
4. Data model & DAX (bài 5): dựng quan hệ giữa bảng giao dịch, bảng chi nhánh, bảng sản phẩm; viết measure:

Huy động = CALCULATE(SUM(SoDu[GiaTri]), SanPham[NhomChiTieu] = "Tiền gửi")
CASA = DIVIDE(
    CALCULATE(SUM(SoDu[GiaTri]), SanPham[KyHan] = "Không kỳ hạn"),
    [Huy động]
)
Tỷ lệ nợ xấu = DIVIDE(
    CALCULATE(SUM(SoDu[GiaTri]), SoDu[NhomNo] IN {3,4,5}),
    [Dư nợ]
)

5. Dashboard (bài 6): PivotChart + slicer theo vùng/chi nhánh, thẻ KPI, và nút Refresh All để cập nhật toàn bộ.

Kết quả. Quy trình đóng file thủ công mỗi tháng (2-3 ngày, hay sai) rút xuống còn: thả 12 file mới vào thư mục → bấm Refresh All → 30 giây có dashboard. Logic làm sạch nằm trong Query, ai kế nhiệm cũng đọc được các bước.

Bài toán 2 — Mô hình tài chính

Mục tiêu. Dựng các mô hình tính toán tài chính mà nghiệp vụ hay cần: lịch trả nợ (amortization) một khoản vay, lãi dồn sổ tiết kiệm, đánh giá một dự án đầu tư bằng NPV/IRR, và phân tích kịch bản để trả lời "nếu lãi suất tăng 1% thì sao".

Dữ liệu. Ít, nhưng nhạy: gốc vay, lãi suất, kỳ hạn, phương thức trả (đều/giảm dần), dòng tiền dự án theo năm.

Kỹ thuật bảng tính. Chủ lực là công thức tài chính:

• Lịch trả nợ trả đều dùng PMT cho kỳ trả cố định, IPMT/PPMT để tách phần lãi và phần gốc từng kỳ:

' Khoản vay 500 triệu, lãi 10.8%/năm, 24 tháng, trả đều hằng tháng
=PMT(10.8%/12, 24, -500000000)   ' -> khoản trả mỗi kỳ ~ 23,23 triệu
=IPMT(10.8%/12, 1, 24, -500000000)  ' phần lãi kỳ 1
=PPMT(10.8%/12, 1, 24, -500000000)  ' phần gốc kỳ 1

• Lãi dồn (compound) sổ tiết kiệm tái tục: FV(lãi_kỳ, số_kỳ, 0, -gốc).
• NPV/IRR dự án: NPV(tỷ_lệ_chiết_khấu, dòng_tiền...) cộng vốn đầu tư ban đầu; IRR(dải_dòng_tiền) cho tỷ suất hoàn vốn nội bộ. Nếu dòng tiền không đều ngày thì dùng XNPV/XIRR.
• Phân tích kịch bản:
  • Data Table (What-If) một/hai biến: quét NPV theo lãi suất và tăng trưởng doanh thu, ra ma trận độ nhạy.
  • Scenario Manager: lưu bộ giả định "Cơ sở / Thận trọng / Lạc quan" và chuyển qua lại.
  • Goal Seek: "cần lãi suất bao nhiêu để NPV = 0" — Excel tự dò ngược.

Kết quả. Một file mô hình duy nhất cho phép trả lời câu hỏi của lãnh đạo ngay tại cuộc họp: kéo Data Table thấy khoản vay chịu được lãi suất tối đa bao nhiêu, Goal Seek ra điểm hoà vốn của dự án. Đây là vùng bảng tính vượt trội so với SQL/BI — logic tài chính tương tác, không cần triển khai.

Bài toán 3 — Phân tích tín dụng

Mục tiêu. Hỗ trợ ra quyết định tín dụng ở mức sơ bộ: chấm điểm hồ sơ theo bộ tiêu chí đơn giản, phân loại nhóm nợ, và dựng bảng phân tầng tuổi nợ (aging) cho công nợ quá hạn.

Dữ liệu. Danh sách khoản vay: khách hàng, dư nợ, ngày đến hạn, số ngày quá hạn, và các biến chấm điểm (thu nhập, lịch sử trả nợ, tỷ lệ dư nợ/thu nhập).

Kỹ thuật bảng tính.

• Chấm điểm sơ bộ: mỗi tiêu chí một trọng số, dùng SUMPRODUCT để tính điểm tổng hợp; IFS/tra bảng (XLOOKUP khớp gần đúng) để ánh xạ điểm → hạng (A/B/C). Đây là bản "bảng tính hoá" của một scorecard — muốn hiểu bản chất mô hình, xem chấm điểm tín dụng & scorecard.
• Phân loại nhóm nợ theo số ngày quá hạn:

=IFS(D2<=10,"Nhóm 1", D2<=90,"Nhóm 2", D2<=180,"Nhóm 3", D2<=360,"Nhóm 4", TRUE,"Nhóm 5")

• Bảng aging: dựng cột "dải tuổi nợ" (0-30 / 31-60 / 61-90 / >90 ngày) rồi PivotTable tổng dư nợ theo dải × chi nhánh, tô conditional formatting cho các dải xấu.

Kết quả. Cán bộ tín dụng có một view nhanh: bao nhiêu dư nợ đang ở mỗi dải tuổi nợ, khách nào tụt hạng so với tháng trước. Lưu ý ranh giới nghiệp vụ: kết quả bảng tính này là sơ bộ, tham khảo; phân loại nợ chính thức chạy trên core banking theo quy định.

Bài toán 4 — Đối soát (reconciliation)

Mục tiêu. So khớp hai nguồn dữ liệu lẽ ra phải trùng nhau — ví dụ sổ cái nội bộ với sao kê ngân hàng đối tác — để tìm giao dịch lệch, thiếu, hoặc lặp.

Dữ liệu. Hai file: mỗi dòng có mã giao dịch, ngày, số tiền. Vấn đề: mã có thể khác định dạng, một bên gộp nhiều giao dịch, chênh lệch làm tròn.

Kỹ thuật bảng tính.

• Cách nhanh (một chiều): đưa nguồn A làm chuẩn, dùng XLOOKUP tìm mỗi mã của A trong B; IFNA đánh dấu "thiếu ở B". Làm ngược lại để bắt "thừa ở B". So số tiền bằng phép trừ, lọc |chênh| > ngưỡng làm tròn.
• Cách bền (Power Query merge): nạp cả hai nguồn vào Power Query, làm sạch mã (Trim/Upper), rồi Merge Queries:
  • Full Outer Join trên mã → những dòng null một bên chính là lệch.
  • Thêm cột [SoTienA] - [SoTienB] để phát hiện lệch số tiền dù mã khớp. Ưu điểm: lặp lại hằng ngày chỉ cần Refresh, không kéo công thức.

Kết quả. Đối soát cuối ngày từ thao tác dò tay hàng giờ (dễ sót) thành một Query cho ra ngay bảng "danh sách chênh lệch". Kết hợp tự động hoá có thể lên lịch chạy và gửi kết quả.

Bài toán 5 — Kiểm soát chất lượng báo cáo

Mục tiêu. Đảm bảo con số trong file đúng và kiểm soát được rủi ro khi bảng tính trở thành công cụ tính toán quan trọng (EUC — End User Computing).

Kỹ thuật bảng tính.

• Kiểm tra công thức: dùng Formulas → Trace Precedents/Dependents, Evaluate Formula để dò logic; bọc IFERROR/IFNA để lỗi không lan âm thầm; dựng ô "kiểm tra chéo" (tổng theo hàng = tổng theo cột) và cảnh báo nếu lệch.
• Ràng buộc dữ liệu: Data Validation giới hạn giá trị nhập (danh sách chi nhánh hợp lệ, số dư ≥ 0), tô đỏ ô ngoài ngưỡng.
• EUC governance: khoá vùng công thức, tách rõ vùng input — tính toán — output, ghi log phiên bản, có người review độc lập. Đây chính là điểm giao với chất lượng dữ liệu: một file Excel tính sai lan ra báo cáo còn nguy hiểm hơn lỗi trong pipeline vì không ai kiểm soát.

Kết quả. File báo cáo có thể tin cậy để trình lãnh đạo, và khi có sự cố thì truy được nguồn gốc con số.

Giới hạn của bảng tính — khi nào phải lên cấp

Bảng tính vô địch ở vùng nhanh, linh hoạt, tương tác. Nhưng nó có trần cứng. Hãy chuyển sang SQL/warehouse/BI khi gặp các dấu hiệu sau:

Nói cách khác: bảng tính là nơi khám phá và tạo mẫu (prototype); khi một báo cáo đã ổn định và cần dùng lâu dài cho nhiều người, hãy chuẩn hoá nó lên nền tảng — dữ liệu vào warehouse, chỉ tiêu định nghĩa một lần (xem chỉ số & KPI), phân phối qua BI.

Nguồn dữ liệu cho báo cáo — kéo từ đâu

Trước khi đổ vào Power Query, dữ liệu thô thường được truy vấn từ kho. Ví dụ, để lấy tổng số dư và số dư trung bình theo loại tiền tệ (một lát của báo cáo huy động) từ sandbox:

-- ▶ Chạy được
SELECT
    a.currency,
    COUNT(*)                       AS so_tai_khoan,
    SUM(a.balance)                 AS tong_so_du,
    ROUND(AVG(a.balance)::numeric, 2) AS so_du_tb
FROM accounts a
GROUP BY a.currency
ORDER BY tong_so_du DESC;

Câu này minh hoạ nguyên tắc từ bài 2: ép AVG(...)::numeric trước ROUND(...,2) vì AVG trả double precision. Muốn báo cáo theo chi nhánh/khu vực thực tế thì JOIN thêm bảng khách hàng và bảng chi nhánh — nhưng ý tưởng không đổi: SQL kéo dữ liệu sạch, bảng tính lo trình bày và tính toán tương tác.

Checklist kỹ năng & lộ trình trưởng thành

Một analyst bảng tính trưởng thành qua bốn nấc, mỗi nấc thêm một năng lực:

1. Công thức — tra cứu, tổng hợp, xử lý ngày/text/lỗi thành thạo. (Không còn copy-paste tay để tổng hợp.)
2. Mô hình hoá — PivotTable, data model, DAX, mô hình tài chính. (Nghĩ theo bảng và quan hệ, không theo ô rời.)
3. Tự động hoá — Power Query, Office Script/macro, refresh một nút. (Việc lặp lại được máy làm.)
4. Chuẩn hoá lên nền tảng — biết khi nào rời bảng tính, đẩy dữ liệu vào warehouse, dựng BI dùng chung. (Tạo tài sản dữ liệu cho tổ chức, không phải file cá nhân.)

Checklist tự đánh giá nhanh:

• Viết được lookup không dùng số cột cứng (INDEX/MATCH hoặc XLOOKUP)?
• Dựng được data model nhiều bảng với measure DAX thay vì công thức lồng?
• Gộp và làm sạch nhiều file bằng Power Query, refresh một nút?
• Đối soát hai nguồn bằng merge thay vì dò tay?
• Có vùng kiểm tra chéo và IFERROR trong mọi báo cáo quan trọng?
• Nhận ra lúc nào bài toán đã vượt bảng tính và cần lên SQL/BI?

Use case thực tế

Bối cảnh. Chị Hà, analyst Khối Tài chính NCB, phụ trách báo cáo KQKD tháng cho 12 chi nhánh vùng Đông Nam Bộ.

Trước khi chuẩn hoá. Mỗi tháng chị mở lần lượt 12 file, copy từng vùng dán vào một sheet tổng, tự gõ công thức SUMIFS cho từng chỉ tiêu — mất 2,5 ngày, tháng 3 từng sai CASA của một chi nhánh vì dán nhầm cột.

Sau khi áp dụng bài này.

1. Bài toán 1: dựng Power Query From Folder gộp 12 file, làm sạch, nạp vào data model; measure DAX cho Huy động, Dư nợ, CASA, Tỷ lệ nợ xấu; dashboard slicer theo chi nhánh + nút Refresh All. Tháng mới chỉ thả file → bấm nút.
2. Bài toán 4: thêm một Query merge đối soát tổng số dư trên dashboard với số kéo trực tiếp từ warehouse (câu SQL ▶ ở trên là nguồn), lệch > 1.000 đồng thì tô đỏ.
3. Bài toán 5: thêm ô kiểm tra chéo "tổng 12 chi nhánh = tổng toàn vùng" và Data Validation cho danh mục chi nhánh.

Số liệu minh hoạ. Sau chuẩn hoá: tổng huy động toàn vùng 8.420 tỷ, CASA 21,3%, dư nợ 6.950 tỷ, tỷ lệ nợ xấu 1,8%. Thời gian đóng báo cáo: từ 2,5 ngày → 20 phút, không còn lỗi dán cột.

Điểm lên cấp. Cuối năm, ba khối cùng cần báo cáo này, mỗi khối một bản Excel lệch nhau. Đây đúng là dấu hiệu trong bảng giới hạn: chị Hà đề xuất đẩy dữ liệu vào warehouse, định nghĩa CASA/nợ xấu một lần, dựng Power BI dùng chung — bảng tính chuyển vai trò từ "công cụ đóng báo cáo" sang "nơi prototype chỉ tiêu mới".

Ghi nhớ

• Công việc thật giao bài toán, không giao kỹ năng; hãy tư duy theo lát mục tiêu → dữ liệu → kỹ thuật → kết quả.
• Power Query + data model + dashboard là bộ ba xương sống của báo cáo quản trị định kỳ, refresh một nút.
• Công thức tài chính (PMT/IPMT/NPV/IRR + Data Table/Scenario/Goal Seek) là vùng bảng tính vượt trội SQL/BI: mô hình tương tác, trả lời "nếu... thì".
• Đối soát nên làm bằng Power Query Merge (full outer join) thay vì dò tay để lặp lại được và không sót.
• Kiểm soát chất lượng & EUC governance không phải tuỳ chọn: kiểm tra chéo, IFERROR, tách input/tính/output, review độc lập.
• Biết giới hạn: dữ liệu quá lớn, nhiều người dùng, cần audit/phân quyền → chuyển sang SQL/warehouse/BI; bảng tính là nơi prototype.
• Lộ trình trưởng thành: công thức → mô hình hoá → tự động → chuẩn hoá lên nền tảng.
• SQL kéo dữ liệu sạch (nhớ ép ::numeric trước ROUND khi dùng AVG), bảng tính lo trình bày và tính toán.