Template Quản Lý Công Nợ Khách Hàng Google Sheets [Tự Động Nhắc Hạn 2026]

Dòng Tiền Bị "Ăn Mòn" Vì Quản Lý Công Nợ Thủ Công

Bạn đã từng gọi điện nhắc nợ khách hàng, rồi nhận ra mình… quên mất khoản nợ đó từ 3 tháng trước? Hoặc cuối tháng ngồi đối chiếu sổ mới phát hiện một đối tác đang nợ hơn 200 triệu quá hạn mà không ai theo dõi?

Đây là câu chuyện phổ biến ở các doanh nghiệp vừa và nhỏ khi quản lý công nợ bằng Excel thủ công — không có cảnh báo tự động, không có tổng quan aging, không ai chịu trách nhiệm nhắc khách hàng đúng hạn. Kết quả là dòng tiền bị ảnh hưởng, phải đi vay vốn ngắn hạn trong khi tiền thực ra đang "nằm" ở công nợ chưa thu.

Bài viết này hướng dẫn bạn xây dựng template Google Sheets quản lý công nợ hoàn chỉnh — 4 sheet liên kết, công thức tự động tính aging, và Apps Script gửi email nhắc nợ mỗi sáng. Không cần phần mềm kế toán đắt tiền, chỉ cần Google Sheets và 30 phút thiết lập.

Tổng Quan Template: 4 Sheet — 1 Hệ Thống

Template được thiết kế theo nguyên tắc tách biệt dữ liệu và báo cáo, giúp dễ duy trì và mở rộng:

• Sheet 1 — Danh Sách Khách Hàng: Master data khách hàng, hạn mức tín dụng, phân nhóm A/B/C.
• Sheet 2 — Hóa Đơn & Công Nợ: Sheet nhập liệu chính, tính toán tự động trạng thái và aging.
• Sheet 3 — Dashboard: Tổng quan tình hình công nợ, biểu đồ, cảnh báo vượt hạn mức.
• Sheet 4 — Lịch Sử Nhắc Nợ: Log mọi lần liên hệ nhắc nợ để tra cứu và tránh nhắc trùng.

Sheet 1: Danh Sách Khách Hàng (Master Data)

Sheet này là nguồn dữ liệu gốc cho các sheet khác. Mỗi khách hàng chỉ nhập một lần, các sheet còn lại tra cứu qua Mã KH bằng hàm VLOOKUP hoặc XLOOKUP.

Nhóm KH (A/B/C): A = khách hàng lớn, ưu tiên hạn mức cao và xử lý nhanh; B = khách hàng trung bình; C = khách hàng mới hoặc có lịch sử nợ xấu, áp dụng hạn mức thấp và theo dõi sát.

Sheet 2: Hóa Đơn & Công Nợ — Trái Tim Của Template

Đây là sheet nhập liệu hàng ngày. Mỗi hóa đơn là một dòng. Kế toán chỉ cần nhập 6 cột thủ công (Số HĐ, Mã KH, Ngày HĐ, Ngày đến hạn, Số tiền HĐ, Đã thu) — các cột còn lại tự động tính.

Bảng Mẫu Hóa Đơn (5 dòng thực tế)

Công Thức Tự Động Cho Từng Cột

Cột H — Còn lại:

=F2-G2

Cột I — Trạng thái (tự động theo màu):

=IF(H2=0,"✅ Đã thu",IF(E2<TODAY(),"🔴 Quá hạn","🟡 Chưa đến hạn"))

Cột J — Số ngày quá hạn:

=MAX(0,TODAY()-E2)

Lưu ý: Công thức này trả về 0 nếu chưa đến hạn, không trả số âm — giúp bảng aging sạch hơn.

Cột K — Nhóm Aging (phân loại tự động theo số ngày):

=IFS(H2=0,"Đã thu",J2=0,"Chưa đến hạn",J2<=30,"1-30 ngày",J2<=60,"31-60 ngày",J2<=90,"61-90 ngày",TRUE,">90 ngày")

Cột C — Tên KH (tra tự động từ Sheet 1):

=IFERROR(VLOOKUP(B2,DanhSachKH!A:B,2,0),"")

Sheet 3: Dashboard — Nhìn Một Cái Biết Ngay

Dashboard tổng hợp toàn bộ tình hình công nợ theo thời gian thực. Mỗi lần kế toán cập nhật số liệu "Đã thu", dashboard tự động cập nhật.

Các Chỉ Số Chính

• Tổng công nợ hiện tại: =SUMIF(HoaDon!H:H,">0",HoaDon!H:H)
• Tổng quá hạn: =SUMIF(HoaDon!I:I,"🔴 Quá hạn",HoaDon!H:H)
• Tỷ lệ thu hồi: =SUMIF(HoaDon!G:G,">0",HoaDon!G:G)/SUM(HoaDon!F:F)
• Số hóa đơn quá hạn: =COUNTIF(HoaDon!I:I,"🔴 Quá hạn")

Bảng Aging Report (Mẫu)

Công thức COUNTIFS và SUMIFS cho bảng aging:

// Đếm số HĐ nhóm "1-30 ngày"
=COUNTIF(HoaDon!K:K,"1-30 ngày")

// Tổng tiền nhóm "1-30 ngày"
=SUMIF(HoaDon!K:K,"1-30 ngày",HoaDon!H:H)

// Tỷ lệ (chia tổng công nợ)
=SUMIF(HoaDon!K:K,"1-30 ngày",HoaDon!H:H)/SUMIF(HoaDon!H:H,">0",HoaDon!H:H)

Top 10 Khách Nợ Nhiều Nhất

Dùng hàm QUERY để lấy top 10 khách hàng có tổng nợ lớn nhất:

=QUERY(HoaDon!B:H,"SELECT B,SUM(H) WHERE H>0 GROUP BY B ORDER BY SUM(H) DESC LIMIT 10 LABEL B 'Mã KH',SUM(H) 'Tổng Còn Lại'")

Cảnh Báo Vượt Hạn Mức Tín Dụng

=QUERY({HoaDon!B:H,DanhSachKH!F:F},"SELECT Col1,SUM(Col7),Col8 WHERE Col7>0 GROUP BY Col1,Col8 HAVING SUM(Col7)>Col8")

Cột cuối cùng trong bảng này sẽ hiển thị màu đỏ nếu tổng nợ vượt hạn mức đã thiết lập trong Sheet 1.

Sheet 4: Lịch Sử Nhắc Nợ

Mỗi lần gọi điện, gửi email, hoặc gặp mặt khách hàng để nhắc nợ — ghi lại vào sheet này. Điều này giúp tránh tình trạng hai nhân viên gọi trùng, và có bằng chứng nếu khách hàng tranh chấp sau này.

Apps Script: Tự Động Nhắc Nợ Mỗi Sáng

Đây là phần quan trọng nhất — thay vì kế toán phải nhớ nhắc từng khách, Apps Script sẽ tự động chạy mỗi sáng 8h, quét toàn bộ hóa đơn và gửi email nhắc nhở.

Code Apps Script Đầy Đủ

/**
 * Hàm tự động nhắc nợ quá hạn — chạy trigger mỗi sáng 8h
 * Gửi email cho từng khách hàng quá hạn <= 3 ngày
 * Gửi email tổng hợp cho kế toán (danh sách tất cả quá hạn)
 */
function nhacNoQuaHan() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetHD = ss.getSheetByName('HoaDon');
  const sheetKH = ss.getSheetByName('DanhSachKH');

  if (!sheetHD || !sheetKH) {
    Logger.log('Không tìm thấy sheet HoaDon hoặc DanhSachKH');
    return;
  }

  const dataHD = sheetHD.getDataRange().getValues();
  const dataKH = sheetKH.getDataRange().getValues();

  // Tạo map: Mã KH → Email KH
  const emailMap = {};
  for (let i = 1; i < dataKH.length; i++) {
    if (dataKH[i][0]) {
      emailMap[dataKH[i][0]] = dataKH[i][4]; // cột E = Email
    }
  }

  const emailBody = [];
  const today = new Date();

  for (let i = 1; i < dataHD.length; i++) {
    const soHD    = dataHD[i][0]; // cột A
    const maKH    = dataHD[i][1]; // cột B
    const tenKH   = dataHD[i][2]; // cột C
    const ngayHan = dataHD[i][4]; // cột E
    const conLai  = dataHD[i][7]; // cột H
    const soNgayQH = dataHD[i][9]; // cột J

    if (!soHD || conLai <= 0) continue;

    // Chỉ xử lý hóa đơn quá hạn
    if (soNgayQH > 0) {
      const emailKH = emailMap[maKH];

      // Gửi email trực tiếp cho KH nếu mới quá hạn (1-3 ngày)
      if (emailKH && soNgayQH <= 3) {
        try {
          const ngayHanStr = ngayHan instanceof Date
            ? ngayHan.toLocaleDateString('vi-VN')
            : ngayHan;

          GmailApp.sendEmail(
            emailKH,
            `[Nhắc thanh toán] Hóa đơn ${soHD} đã đến hạn`,
            `Kính gửi ${tenKH},\n\nHóa đơn ${soHD} đã đến hạn thanh toán ngày ${ngayHanStr}.\nSố tiền còn lại: ${conLai.toLocaleString('vi-VN')} VNĐ.\n\nVui lòng thanh toán để tránh phát sinh phí trễ hạn.\n\nTrân trọng.`
          );
          Logger.log(`Đã gửi email nhắc KH: ${tenKH} | ${soHD}`);
        } catch (e) {
          Logger.log(`Lỗi gửi email KH ${tenKH}: ${e.message}`);
        }
      }

      // Thêm vào danh sách tổng hợp gửi kế toán
      const nhomAging = dataHD[i][10]; // cột K
      emailBody.push(
        `${soHD} | ${tenKH} | ${conLai.toLocaleString('vi-VN')} VNĐ | Quá hạn ${soNgayQH} ngày [${nhomAging}]`
      );
    }
  }

  // Gửi email tổng hợp cho kế toán nếu có công nợ quá hạn
  if (emailBody.length > 0) {
    const subject = `[Công Nợ] ${emailBody.length} hóa đơn quá hạn - ${today.toLocaleDateString('vi-VN')}`;
    const body = [
      `Xin chào,\n`,
      `Hệ thống ghi nhận ${emailBody.length} hóa đơn quá hạn tính đến hôm nay:\n`,
      `----- DANH SÁCH CÔNG NỢ QUÁ HẠN -----`,
      emailBody.join('\n'),
      `\n----- HẾT -----`,
      `\nVui lòng xử lý sớm để tránh ảnh hưởng dòng tiền.\n`,
      `Email được gửi tự động từ Google Sheets lúc ${today.toLocaleTimeString('vi-VN')}.`
    ].join('\n');

    GmailApp.sendEmail(
      Session.getActiveUser().getEmail(),
      subject,
      body
    );
    Logger.log(`Đã gửi email tổng hợp: ${emailBody.length} hóa đơn quá hạn`);
  } else {
    Logger.log('Không có hóa đơn quá hạn hôm nay.');
  }
}

/**
 * Thiết lập trigger tự động chạy mỗi sáng 8h
 * Chạy hàm này một lần để tạo trigger
 */
function taoTrigger() {
  // Xóa trigger cũ nếu có
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(t => {
    if (t.getHandlerFunction() === 'nhacNoQuaHan') {
      ScriptApp.deleteTrigger(t);
    }
  });

  // Tạo trigger mới chạy mỗi ngày lúc 8-9h sáng
  ScriptApp.newTrigger('nhacNoQuaHan')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();

  Logger.log('Đã tạo trigger: nhacNoQuaHan chạy mỗi sáng 8h');
}

Cách Thiết Lập Apps Script

1. Trong Google Sheets, vào Extensions > Apps Script
2. Xóa code mặc định, paste toàn bộ code trên vào
3. Đổi tên sheet trong code khớp với tên sheet thực tế của bạn (HoaDon, DanhSachKH)
4. Nhấn Save (Ctrl+S)
5. Chạy hàm taoTrigger() một lần để đăng ký lịch chạy tự động
6. Cấp quyền truy cập Gmail khi được hỏi (chỉ cần làm một lần)

Sau đó, mỗi sáng 8h, script sẽ tự chạy, quét toàn bộ công nợ và gửi email tổng hợp vào hộp thư của bạn.

Conditional Formatting: Nhìn Là Biết Ngay

Thiết lập màu sắc tự động để kế toán nhận biết mức độ nghiêm trọng ngay khi mở file:

Cách thiết lập: Chọn toàn bộ vùng dữ liệu → Format > Conditional formatting → Chọn "Custom formula is" → Nhập công thức tương ứng và chọn màu.

Ví dụ công thức cho màu đỏ (hàng có nợ >90 ngày):

=AND($K2=">90 ngày",$H2>0)

Mẹo Vận Hành Hiệu Quả

1. Protect Sheet Để Tránh Nhập Sai

Chỉ cho phép chỉnh sửa cột "Đã thu" (cột G) để kế toán không vô tình xóa công thức:

• Chuột phải vào tên sheet "HoaDon" → Protect Sheet
• Chọn Except certain cells → Nhập G:G
• Thiết lập quyền chỉ cho phép kế toán chỉnh sửa

2. Data Validation Cho Cột Phương Thức Nhắc Nợ

Trong Sheet 4, cột "Phương thức" nên dùng dropdown để nhập liệu nhất quán:

• Chọn cột D (Sheet 4) → Data > Data validation
• Chọn List of items → Nhập: Gọi điện,Email,Gặp mặt,Zalo,Viber

3. Freeze Row Đầu Và Cột Đầu

Vào View > Freeze > 1 row và 1 column để khi cuộn xuống vẫn thấy header và mã hóa đơn.

4. Lên Lịch Kiểm Tra Định Kỳ

• Hàng ngày: Xem email tổng hợp từ Apps Script, xử lý nợ trong nhóm "mới quá hạn"
• Thứ 2 đầu tuần: Mở Dashboard, kiểm tra tỷ lệ thu hồi và top khách nợ nhiều nhất
• Cuối tháng: Export báo cáo aging, báo cáo giám đốc
• Hàng quý: Rà soát nhóm KH, điều chỉnh hạn mức tín dụng nếu cần

5. Thêm Cột "Ưu Tiên" Để Triage

Thêm cột cuối với công thức ưu tiên kết hợp aging và số tiền:

=IF(AND(J2>90,H2>100000000),"🔥 Khẩn cấp",IF(J2>30,"⚠️ Cao",IF(J2>0,"📌 Trung bình","✅ OK")))

Kết Luận

Template Google Sheets quản lý công nợ với 4 sheet liên kết, công thức aging tự động và Apps Script gửi email nhắc nhở — đây là giải pháp đủ mạnh cho hầu hết doanh nghiệp SMB mà không tốn chi phí phần mềm.

Điểm mấu chốt không phải ở template, mà ở kỷ luật vận hành: cập nhật "Đã thu" ngay khi nhận tiền, đọc email tổng hợp mỗi sáng và xử lý từng trường hợp. Template chỉ là công cụ — quy trình mới tạo ra kết quả thực.

Nếu doanh nghiệp của bạn có trên 200 hóa đơn/tháng hoặc nhiều nhân viên kế toán, hãy xem xét thêm sheet "Phân Công" để gán trách nhiệm theo dõi cho từng kế toán, và thêm trường "Escrow date" khi nợ vượt ngưỡng cần can thiệp pháp lý.

Bắt đầu ngay hôm nay — chỉ cần sao chép cấu trúc 4 sheet, nhập dữ liệu hóa đơn hiện tại và thiết lập trigger Apps Script. Trong vòng 30 phút, bạn sẽ có hệ thống theo dõi công nợ hoàn chỉnh chạy tự động.