Hướng Dẫn Tạo Dashboard KPI Trên Google Sheets 2026

// SHEET: DuLieuBanHang (Data Source)
// Cot A: Ngay (Date)         -- Format: YYYY-MM-DD, KHONG dung DD/MM/YYYY
// Cot B: MaDon               -- Unique ID cho moi don hang
// Cot C: KenhBan             -- Online / Offline / Shopee / Lazada
// Cot D: SanPham             -- Ten san pham (chuan hoa, khong viet tat)
// Cot E: DanhMuc             -- Nhom san pham
// Cot F: SoLuong             -- So nguyen, khong chuoi
// Cot G: GiaBan              -- So thap phan, khong co ki tu tien te
// Cot H: DoanhThu            -- = F * G (tinh tu dong)
// Cot I: NhanVien            -- Nhan vien phu trach
// Cot J: TrangThai           -- PAID / PENDING / CANCELLED

// NGUYEN TAC:
// 1. Moi hang = 1 giao dich
// 2. Khong merge cells
// 3. Khong de o trong o giua data
// 4. Header o hang 1, data bat dau tu hang 2
// 5. Khong dung mau sac de phan biet loai data

-- DOANH THU THANG NAY
=SUMPRODUCT((MONTH(DATA_BanHang!A2:A)=MONTH(TODAY()))*(YEAR(DATA_BanHang!A2:A)=YEAR(TODAY()))*(DATA_BanHang!J2:J="PAID")*DATA_BanHang!H2:H)

-- SO SANH VS THANG TRUOC (%)
=(DoanhThuThangNay - DoanhThuThangTruoc) / DoanhThuThangTruoc

-- DOANH THU THANG TRUOC
=SUMPRODUCT((MONTH(DATA_BanHang!A2:A)=IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1))*(YEAR(DATA_BanHang!A2:A)=IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY())))*(DATA_BanHang!J2:J="PAID")*DATA_BanHang!H2:H)

-- SO DON HANG HOM NAY
=COUNTIFS(DATA_BanHang!A2:A, TODAY(), DATA_BanHang!J2:J, "PAID")

-- GIA TRI DON TRUNG BINH
=IFERROR(SUMIFS(DATA_BanHang!H2:H,DATA_BanHang!J2:J,"PAID",DATA_BanHang!A2:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))/COUNTIFS(DATA_BanHang!J2:J,"PAID",DATA_BanHang!A2:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1)), 0)

-- TY LE DAT MUC TIEU
=DoanhThuThangNay / CONFIG!B2   -- B2 la muc tieu thang nay trong sheet CONFIG

-- SPARKLINE co ban: bieu do duong 7 ngay gan nhat
=SPARKLINE(QUERY(DATA_BanHang!A:H,"SELECT SUM(H) WHERE J='PAID' GROUP BY A ORDER BY A DESC LIMIT 7 LABEL SUM(H) ''"),{"charttype","line";"color","#10b981";"linewidth",2})

-- SPARKLINE dang cot (bar)
=SPARKLINE(DoanhThu30Ngay, {"charttype","bar";"color1","#3b82f6";"color2","#e5e7eb"})

-- SPARKLINE voi mau thay doi theo xu huong (xanh tang, do giam)
=SPARKLINE(DoanhThu7Ngay, {"charttype","line";"color",IF(DoanhThuHomNay>DoanhThuHomQua,"#10b981","#ef4444");"linewidth",2})

-- SPARKLINE win/loss (gia tri +/-)
=SPARKLINE(TangTruong30Ngay, {"charttype","winloss";"color1","#10b981";"color2","#ef4444"})

-- Tao mang du lieu 7 ngay cho SPARKLINE
-- Dat vung nay o CALC sheet, an di:
=ARRAYFORMULA(MMULT((ROW(INDIRECT("A1:A"&COUNTA(DATA_BanHang!A2:A)))),0)+
SUMIFS(DATA_BanHang!H2:H,DATA_BanHang!A2:A,TODAY()-{6,5,4,3,2,1,0}))

-- Ket noi du lieu tu file CRM sang Dashboard:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit", "CRM!A2:J")

-- Khi lan dau dung IMPORTRANGE:
-- Google se yeu cau "Allow access" -- click vao o va chon Allow

-- Ket noi nhieu nguon va gop lai (QUERY + IMPORTRANGE):
=QUERY(
  {IMPORTRANGE("[ID_1]","BanHang!A2:H");IMPORTRANGE("[ID_2]","BanHang!A2:H")},
  "SELECT Col1, Col3, Col8 WHERE Col10='PAID' ORDER BY Col1 DESC"
)

-- Kiem tra IMPORTRANGE co dang hoat dong khong:
=IFERROR(IMPORTRANGE("[ID]","Sheet1!A1"), "KET NOI LOI - KIEM TRA LAI")

-- Tan suat refresh: IMPORTRANGE tu dong cap nhat moi 1-30 giay
-- De refresh ngay lap tuc: File > Spreadsheet settings > Recalculation: On change and every minute