Google Sheets Nang Cao: QUERY, ARRAYFORMULA, INDEX/MATCH & Ky Thuat Pro [2026]

Google Sheets nang cao giup ban bien bang tinh thanh he thong quan ly du lieu manh ngang cac phan mem chuyen dung — ma hoan toan mien phi.

Bai viet nay huong dan cac tinh nang nang cao nhat: ham QUERY, ARRAYFORMULA, IMPORTRANGE, Conditional Formatting nang cao, Apps Script co ban va ky thuat xay dashboard tu dong cap nhat.

Muc luc:

1. Ham QUERY — SQL ngay trong Google Sheets
2. ARRAYFORMULA — Cong thuc mang manh me
3. INDEX/MATCH — Thay the VLOOKUP chuyen nghiep
4. IMPORTRANGE — Ket noi du lieu da file
5. Conditional Formatting nang cao
6. Apps Script co ban: Tu dong hoa voi Google Sheets
7. Xay dashboard kinh doanh tu dong cap nhat
8. FAQ

1. Ham QUERY — SQL Ngay Trong Google Sheets

Ham QUERY la mot trong nhung tinh nang nang cao manh me nhat cua Google Sheets, cho phep ban truy van du lieu bang cu phap tuong tu SQL ma khong can cai dat gi them. Day la cong cu ly tuong de tao bao cao dong, loc du lieu phuc tap va tong hop thong tin theo nhieu chieu.

Cu Phap Co Ban

=QUERY(du_lieu, cau_truy_van, [tieu_de])

Tham so	Mo ta	Vi du
du_lieu	Vung o chua du lieu nguon	A1:E500
cau_truy_van	Chuoi SQL-like (SELECT, WHERE, ORDER BY...)	"SELECT A, B WHERE C > 100"
tieu_de	So dong tieu de (tuy chon, mac dinh -1)	1

5 Vi Du QUERY Thuc Te Nhat

Loc don hang theo khu vuc va trang thai:

=QUERY(DonHang!A:F, "SELECT A, B, D, F WHERE C='Ha Noi' AND E='Hoan thanh' ORDER BY F DESC")

Tong doanh thu theo nhan vien moi thang:

=QUERY(A1:D500, "SELECT B, SUM(D) WHERE A >= DATE '2026-01-01' GROUP BY B ORDER BY SUM(D) DESC LABEL SUM(D) 'Doanh thu'")

Lay top 10 san pham ban chay:

=QUERY(A:C, "SELECT A, SUM(C) GROUP BY A ORDER BY SUM(C) DESC LIMIT 10")

Dem don hang theo trang thai:

=QUERY(A:E, "SELECT E, COUNT(A) GROUP BY E LABEL COUNT(A) 'So luong'")

Lay du lieu khong trung lap (DISTINCT):

=QUERY(A:B, "SELECT A WHERE B IS NOT NULL ORDER BY A")

Menh de QUERY hay dung nhat:

SELECT — chon cac cot can hien thi (A, B, C hoac *)
WHERE — dieu kien loc (AND, OR, NOT, LIKE, CONTAINS, IS NULL)
GROUP BY — nhom theo gia tri cot, ket hop voi SUM/COUNT/AVG/MIN/MAX
ORDER BY ... ASC/DESC — sap xep ket qua
LIMIT / OFFSET — lay N ket qua dau, bo qua N ket qua dau
LABEL — doi ten cot ket qua theo y muon

2. ARRAYFORMULA — Cong Thuc Mang Manh Me

ARRAYFORMULA la cong cu giup ban ap dung mot cong thuc cho hang nghin dong du lieu chi bang mot o duy nhat. Thay vi keo cong thuc xuong 1000 dong — ton thoi gian va de mat o quan trong — ban chi can viet mot ARRAYFORMULA o dong dau tien la xong.

So Sanh: Khong Dung vs Dung ARRAYFORMULA

Khong co ARRAYFORMULA (phai keo xuong):

C2: =A2*B2

C3: =A3*B3

C4: =A4*B4

... keo xuong 1000 dong

Co ARRAYFORMULA (1 cong thuc duy nhat):

C2: =ARRAYFORMULA(

IF(A2:A="","",

A2:A*B2:B))

Cac Ung Dung ARRAYFORMULA Pho Bien Nhat

Tinh thanh tien toan cot (co xu ly dong trong):

=ARRAYFORMULA(IF(A2:A="","",A2:A*B2:B*(1-C2:C)))

Tinh so luong x don gia x (1 - chiet khau). Tu dong cho toan bo cot, bo qua dong trong.

Them so thu tu tu dong (khong can fill down):

=ARRAYFORMULA(IF(B2:B="","",ROW(B2:B)-ROW(B2)+1))

Noi chuoi ho ten (First Name + Last Name):

=ARRAYFORMULA(IF(A2:A="","",A2:A&" "&B2:B))

Xep hang theo doanh so (RANK toan cot):

=ARRAYFORMULA(IF(C2:C="","",RANK(C2:C, C2:C, 0)))

ARRAYFORMULA ket hop voi cac ham khac nhu ham IF, VLOOKUP, LEFT, RIGHT, TEXT tao ra nhung cong thuc xu ly du lieu hang loat vo cung hieu qua. Day la nen tang de xay dung cac ham nang cao trong Google Sheets.

3. INDEX/MATCH — Thay The VLOOKUP Chuyen Nghiep

VLOOKUP la ham tra cuu quen thuoc nhung co nhieu han che khi lam viec voi du lieu thuc te. INDEX/MATCH giai quyet toan bo nhung van de do va la lua chon cua cac chuyen gia Google Sheets.

Cu Phap INDEX/MATCH

=INDEX(cot_ket_qua, MATCH(gia_tri_tim, cot_tim_kiem, 0))

Tieu chi so sanh	VLOOKUP	INDEX/MATCH
Huong tim kiem	Chi trai → phai	Moi chieu
Khi chen them cot	Bi sai ket qua	Van chinh xac
Hieu nang du lieu lon	Cham hon	Nhanh hon
Do phuc tap cu phap	Don gian hon	Phuc tap hon chut
Tra cuu 2 dieu kien	Kho thuc hien	De voi cong thuc mang

Vi Du Thuc Te: Tra Cuu 2 Dieu Kien

// Tim don gia theo san pham VA khu vuc (2 dieu kien dong thoi)

=INDEX(C2:C500, MATCH(1, (A2:A500=F1)*(B2:B500=G1), 0))

// Nhan Ctrl+Shift+Enter de nhap dang cong thuc mang

// Tim gia tri ben trai cot khoa (VLOOKUP khong the lam duoc)

// Bang: [Ten nhan vien | Ma nhan vien | Phong ban]

// Tim ten nhan vien theo ma nhan vien (ten o ben trai ma)

=INDEX(A2:A500, MATCH(F1, B2:B500, 0))

Xem them huong dan chi tiet ve VLOOKUP va INDEX/MATCH trong Google Sheets de nam vung ky thuat tra cuu du lieu theo moi truong hop thuc te.

Muon Ap Dung Ngay Vao Cong Viec?

SheetStore cung cap template Google Sheets san cong thuc QUERY, ARRAYFORMULA, INDEX/MATCH — chi can nhap du lieu la dung duoc ngay

Xem Template Mien Phi

4. IMPORTRANGE — Ket Noi Du Lieu Da File

IMPORTRANGE cho phep ban lay du lieu tu mot Google Sheets khac vao file hien tai theo thoi gian thuc. Day la nen tang de xay dung he thong quan ly da sheet, phan quyen truy cap du lieu va tao bao cao tong hop tu nhieu nguon.

=IMPORTRANGE("URL_cua_sheet_nguon", "TenSheet!A1:E500")

Mo Hinh Su Dung Pho Bien Nhat

Mo hinh 1: Data Nhap + Bao Cao Rieng Biet

File data_nhap: nhan vien nhap lieu hang ngay. File bao_cao: IMPORTRANGE tu data_nhap, QUERY → Dashboard. Nhan vien khong truy cap duoc file bao cao, giam doc chi xem file bao cao.

Mo hinh 2: Tong Hop Da Chi Nhanh

File TP.HCM, Ha Noi, Da Nang: moi chi nhanh nhap lieu rieng. File Tong Hop: IMPORTRANGE ca 3 file → QUERY tong hop toan quoc tren 1 dashboard duy nhat.

Luu y quan trong khi dung IMPORTRANGE:

Lan dau import can cap quyen "Allow access" — chi chu so huu file nguon moi cap duoc
Chia se file nguon voi quyen "Viewer" de IMPORTRANGE hoat dong on dinh
IMPORTRANGE lam cham file neu du lieu nguon qua lon — nen chi import nhung cot can thiet
Ket hop IMPORTRANGE + QUERY de loc du lieu ngay tai nguon, giam tai file dich

5. Conditional Formatting Nang Cao Voi Cong Thuc Tuy Chinh

Conditional Formatting (Dinh dang co dieu kien) giup bang tinh tro nen truc quan — o quan trong tu dong doi mau, de beo text, hien thi icon. Phien ban nang cao dung cong thuc tuy chinh (Custom Formula) mo ra vo so kha nang.

Cach Mo: Format → Conditional Formatting → Custom Formula Is

1. To mau ca hang khi trang thai = "Het hang":

Vung ap dung: A2:E500

Cong thuc: =$D2="Het hang"

// Dau $ truoc D: co dinh cot, khong co dinh hang — ap dung cho tung dong

2. Canh bao don hang sap het han trong 3 ngay:

=AND($F2<>"", $F2-TODAY()<=3, $F2>=TODAY())

3. To mau hang co doanh thu top 10%:

=$C2>=PERCENTILE($C$2:$C$500, 0.9)

4. Xen ke hang cho de doc (zebra stripes):

=ISEVEN(ROW())

5. Danh dau dong trung lap (duplicate rows):

=COUNTIF($A$2:$A2, $A2) > 1

Ket hop Conditional Formatting voi cac ham nang cao giup phat hien ngay lap tuc nhung van de trong du lieu. Xem them cach tu dong hoa bao cao doanh nghiep voi Google Sheets de ap dung vao thuc te kinh doanh.

6. Apps Script Co Ban: Tu Dong Hoa Voi Google Sheets

Google Apps Script la ngon ngu lap trinh (dua tren JavaScript) tich hop san vao Google Sheets, giup ban tu dong hoa nhung tac vu lap lai ma cong thuc thong thuong khong xu ly duoc — nhu gui email tu dong, tao bao cao PDF, dong bo du lieu voi he thong ngoai.

Mo Editor: Extensions → Apps Script

3 Script Thuc Te Hay Dung Nhat

Script 1: Tu dong gui email khi don hang moi duoc tao

function guiEmailDonHangMoi(e) {

const sheet = e.source.getActiveSheet();

if (sheet.getName() !== "DonHang") return;

const row = e.range.getRow();

const email = sheet.getRange(row, 5).getValue();

const maDon = sheet.getRange(row, 1).getValue();

if (!email) return;

MailApp.sendEmail(email,

"Xac nhan don hang " + maDon,

"Don hang da duoc tiep nhan. Ma don: " + maDon);

}

// Trigger: on form submit hoac on edit

Script 2: Tao bao cao PDF dinh ky gui email

function guiBaoCaoNgay() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const url = ss.getUrl().replace("/edit", "/export?format=pdf");

const blob = UrlFetchApp.fetch(url, {

headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() }

}).getBlob().setName("BaoCao_" + new Date().toLocaleDateString("vi-VN") + ".pdf");

GmailApp.sendEmail("giamdoc@company.com",

"Bao cao ngay " + new Date().toLocaleDateString("vi-VN"),

"Dinh kem bao cao kinh doanh ngay hom nay.", {attachments: [blob]});

}

// Trigger: Time-driven → Day timer → 8am-9am

Script 3: Sao luu du lieu hang ngay vao sheet moi

function saoluuDuLieu() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const source = ss.getSheetByName("DuLieu");

const ngay = Utilities.formatDate(new Date(), "Asia/Ho_Chi_Minh", "dd-MM-yyyy");

const backup = ss.insertSheet("Backup_" + ngay);

source.copyTo(backup);

}

Nhung viec Apps Script lam duoc ma cong thuc khong the:

Gui email, SMS tu dong khi co su kien (don hang moi, canh bao ton kho duoi nguong)
Tao, doi ten, xoa sheet tu dong theo lich hang ngay/tuan
Ket noi API ben ngoai: CRM, phan mem nhan su, chuong trinh tich diem
Tao PDF bao cao dinh ky, gui email cho giam doc theo lich
Dong bo du lieu hai chieu giua Google Sheets va he thong khac

7. Xay Dashboard Kinh Doanh Tu Dong Cap Nhat

Ket hop tat ca cac ky thuat tren, ban co the xay mot dashboard kinh doanh hoan chinh tu dong cap nhat moi khi co du lieu moi — khong can thao tac thu cong, khong can refresh, giam doc mo dien thoai la thay ngay toan canh kinh doanh.

Kien Truc 3-Layer Cho Dashboard Chuyen Nghiep

Layer 1 — RAW DATA (Sheet: data_nhap)

Nhap lieu truc tiep hoac import tu he thong ban hang. Khong chua cong thuc — chi co du lieu thu. Bao ve sheet chi cho phep sua cac cot nhap lieu can thiet.

↓ ARRAYFORMULA tinh toan / QUERY loc du lieu

Layer 2 — PROCESSED DATA (Sheet: data_xu_ly)

ARRAYFORMULA tinh thanh tien, loi nhuan, phan loai khach hang. IFERROR xu ly loi. Cac cot bo tro phuc vu tinh toan KPI cho dashboard.

↓ QUERY / SUMPRODUCT / COUNTIFS lay KPI

Layer 3 — DASHBOARD (Sheet: dashboard)

The KPI hien thi doanh thu, don hang, loi nhuan. Bieu do tu dong. QUERY tao bang top san pham, top nhan vien. Slicers loc theo khoang thoi gian va khu vuc.

Cong Thuc KPI Cards Hay Dung Nhat

KPI	Cong thuc
Doanh thu thang nay	=SUMPRODUCT((MONTH(A2:A)=MONTH(TODAY()))(YEAR(A2:A)=YEAR(TODAY()))D2:D)
So don hoan thanh	=COUNTIFS(A2:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),E2:E,"Hoan thanh")
Doanh thu trung binh/ngay	=Doanh_thu_thang/DAY(TODAY())
Top 5 san pham ban chay	=QUERY(data_xu_ly!B:D,"SELECT B, SUM(D) GROUP BY B ORDER BY SUM(D) DESC LIMIT 5")

SheetStore xay san tren nen tang Google Sheets voi cac dashboard kinh doanh chuyen nghiep, giup ban co ngay he thong quan ly ma khong can tu xay tu dau. Xem them cach xu ly 10 loi pho bien trong Google Sheets de tranh nhung bay de gap khi xay dashboard.

Trai Nghiem Google Sheets Nang Cao Voi SheetStore

He thong quan ly kinh doanh day du — QUERY, ARRAYFORMULA, dashboard tu dong — san sang su dung ngay, khong can tu xay

Dang Ky Demo Mien Phi Xem Bang Gia

Bai viet lien quan:

8. Cau Hoi Thuong Gap (FAQ)

Ham QUERY trong Google Sheets dung de lam gi?

Ham QUERY cho phep ban truy van du lieu bang cu phap tuong tu SQL. Ban co the loc, sap xep, nhom du lieu, tinh tong va tao bao cao dong theo dieu kien ma khong can code. Vi du: =QUERY(A1:D100, "SELECT A, B, SUM(D) WHERE C='Ha Noi' GROUP BY A, B").

ARRAYFORMULA la gi va khi nao dung?

ARRAYFORMULA bien mot cong thuc don le thanh cong thuc mang, tu dong ap dung cho toan bo cot ma khong can keo xuong tung o. Dung khi muon tinh toan hang tram dong du lieu chi bang mot cong thuc duy nhat. Vi du: =ARRAYFORMULA(IF(B2:B>0, B2:B*C2:C, 0)).

Khac biet giua VLOOKUP va INDEX/MATCH trong Google Sheets?

VLOOKUP chi tim theo cot ben phai va bi loi khi chen them cot. INDEX/MATCH linh hoat hon: tim theo moi chieu, khong bi anh huong khi them/xoa cot, nhanh hon voi du lieu lon. Khi du lieu thuong xuyen thay doi cau truc, nen dung INDEX/MATCH.

Google Sheets Apps Script co kho hoc khong?

Apps Script dung JavaScript nen neu ban biet co ban JS la co the bat dau. Nhieu tac vu tu dong hoa don gian nhu gui email, tao bao cao co the lam duoc sau 1-2 ngay hoc. Editor tich hop san trong Sheets tai Extensions > Apps Script.

Lam the nao de tao dashboard tu dong cap nhat?

Ket hop 4 ky thuat: (1) QUERY hoac FILTER de loc du lieu nguon; (2) ARRAYFORMULA de tinh toan hang loat; (3) SUMPRODUCT/SUMIFS de tinh KPI; (4) Chart tu dong lien ket voi du lieu. Khi du lieu nguon thay doi, dashboard tu cap nhat toan bo ma khong can thao tac thu cong.

Google Sheets co gioi han bao nhieu dong du lieu?

Google Sheets gioi han 10 trieu o (cells) tren moi file. Voi du lieu lon hon, nen dung Google BigQuery ket noi qua Connected Sheets, hoac chia nho du lieu theo nhieu sheet/file rieng biet de dam bao hieu nang.

Google Sheets Nang Cao: QUERY, ARRAYFORMULA, INDEX/MATCH va Ky Thuat Pro 2026