Cheat sheet Excel
Cập nhật lần cuối
Công thức cơ bản
Mọi công thức đều bắt đầu bằng dấu bằng. Excel tính toán và hiển thị kết quả trong ô.
| Thao tác | Cú pháp |
|---|---|
| Bắt đầu một công thức | = rồi đến biểu thức, ví dụ =2+2 |
| Tham chiếu tới ô khác | =A1 |
| Phép tính số học | + - * / và ^ cho lũy thừa |
| Điều khiển thứ tự phép tính | =(A1+A2)*B1 |
| Nối văn bản | =A1&" "&B1 hoặc =CONCAT(A1," ",B1) |
| Toán tử so sánh | = <> > < >= <= |
| Phần trăm của một giá trị | =A1*15% |
| Thêm chú thích vào công thức | =SUM(A1:A9)+N("monthly total") |
| Hiện công thức thay cho kết quả | Ctrl + ` (bật/tắt) |
| Biến công thức thành kết quả | Sao chép, rồi Paste Special → Values |
Tham chiếu ô và vùng
Dấu $ khóa hàng hoặc cột để chúng không bị dịch khi bạn sao chép công thức, đây là điều hữu ích nhất cần hiểu trong Excel.
| Tham chiếu | Ý nghĩa |
|---|---|
A1 | Tương đối: bị dịch khi sao chép theo bất kỳ hướng nào |
$A$1 | Tuyệt đối: không bao giờ bị dịch |
$A1 | Khóa cột, hàng được dịch |
A$1 | Khóa hàng, cột được dịch |
A1:A10 | Một vùng gồm mười ô dọc theo một cột |
A1:C10 | Một khối hình chữ nhật |
A:A | Toàn bộ cột A |
1:1 | Toàn bộ hàng 1 |
Sheet2!A1 | Một ô ở trang tính khác |
'My Sheet'!A1 | Trang tính khác có tên chứa khoảng trắng |
[Book2.xlsx]Sheet1!A1 | Một ô trong sổ làm việc khác |
Bật/tắt $ khi đang sửa | F4 (Windows), Cmd + T (Mac) |
Hàm toán học và tổng hợp
Các phép tính tổng hằng ngày. Tất cả đều nhận một vùng, một danh sách ô, hoặc kết hợp cả hai.
| Hàm | Tác dụng |
|---|---|
=SUM(B2:B20) | Cộng mọi số trong vùng |
=AVERAGE(B2:B20) | Giá trị trung bình |
=MEDIAN(B2:B20) | Giá trị trung vị |
=MIN(B2:B20) / =MAX(B2:B20) | Giá trị nhỏ nhất / lớn nhất |
=PRODUCT(B2:B5) | Nhân các giá trị với nhau |
=SUMPRODUCT(B2:B20,C2:C20) | Nhân từng cặp rồi cộng lại: tính tổng có trọng số |
=ABS(B2) | Giá trị tuyệt đối |
=POWER(B2,3) | B2 lập phương (giống =B2^3) |
=SQRT(B2) | Căn bậc hai |
=MOD(B2,2) | Số dư: bằng =0 với số chẵn |
=SUBTOTAL(109,B2:B20) | Chỉ cộng các hàng đang hiển thị (bỏ qua hàng bị lọc ẩn) |
=RAND() / =RANDBETWEEN(1,100) | Số thập phân ngẫu nhiên / số nguyên ngẫu nhiên |
Hàm logic
IF là hàm chủ lực. IFS và IFERROR giúp công thức dài vẫn dễ đọc.
| Hàm | Tác dụng |
|---|---|
=IF(B2>1000,"Over","OK") | Một điều kiện, hai kết quả |
=IF(B2>1000,"Over",IF(B2>500,"Watch","OK")) | IF lồng nhau cho ba kết quả trở lên |
=IFS(B2>1000,"Over",B2>500,"Watch",TRUE,"OK") | Cách viết phẳng thay cho IF lồng nhau |
=AND(B2>0,C2>0) | TRUE chỉ khi mọi điều kiện đều đúng |
=OR(B2>0,C2>0) | TRUE khi có ít nhất một điều kiện đúng |
=NOT(B2>0) | Đảo ngược TRUE/FALSE |
=IFERROR(A2/B2,0) | Thay lỗi bằng một giá trị dự phòng |
=IFNA(VLOOKUP(...),"Not found") | Chỉ bắt lỗi #N/A |
=ISBLANK(B2) | TRUE với ô trống |
=ISNUMBER(B2) / =ISTEXT(B2) | Kiểm tra kiểu: hữu ích khi xác thực dữ liệu nhập vào |
=SWITCH(B2,1,"Low",2,"Mid",3,"High","Other") | So khớp một giá trị với danh sách các trường hợp |
Đếm và tính tổng có điều kiện
Nhóm hàm *IF và *IFS trả lời câu hỏi "bao nhiêu hàng" và "tổng bao nhiêu" cho những hàng khớp một quy tắc.
| Hàm | Tác dụng |
|---|---|
=COUNT(B2:B20) | Đếm các ô chứa số |
=COUNTA(B2:B20) | Đếm các ô không trống, mọi kiểu dữ liệu |
=COUNTBLANK(B2:B20) | Đếm các ô trống |
=COUNTIF(B2:B20,">100") | Đếm các hàng khớp một điều kiện |
=COUNTIF(B2:B20,"*north*") | Ký tự đại diện: * là chuỗi ký tự bất kỳ, ? là một ký tự |
=COUNTIFS(B2:B20,">100",C2:C20,"Paid") | Đếm các hàng khớp nhiều điều kiện |
=SUMIF(C2:C20,"Paid",B2:B20) | Cộng cột B ở những hàng mà C khớp |
=SUMIFS(B2:B20,C2:C20,"Paid",D2:D20,"EU") | Tính tổng với nhiều điều kiện |
=AVERAGEIF(C2:C20,"Paid",B2:B20) | Trung bình có điều kiện |
=MAXIFS(B2:B20,C2:C20,"Paid") | Giá trị lớn nhất trong các hàng khớp |
=COUNTIF($A$2:A2,A2)>1 | Đánh dấu giá trị trùng lặp khi đi dần xuống cột |
=SUMPRODUCT((C2:C20="Paid")*(B2:B20)) | Tổng có điều kiện mà không cần SUMIFS |
Hàm tra cứu và tham chiếu
Lấy một giá trị từ bảng khác. XLOOKUP là bản thay thế hiện đại cho VLOOKUP; INDEX/MATCH chạy trên mọi phiên bản Excel.
| Hàm | Tác dụng |
|---|---|
=VLOOKUP(A2,$F$2:$H$50,3,FALSE) | Tìm A2 ở cột đầu tiên, trả về cột thứ 3. FALSE = khớp chính xác |
=XLOOKUP(A2,$F$2:$F$50,$H$2:$H$50,"Not found") | Vùng tra cứu và vùng trả về tách riêng, nên tra được sang bên trái |
=INDEX($H$2:$H$50,MATCH(A2,$F$2:$F$50,0)) | Cách viết kinh điển chạy ở mọi nơi |
=MATCH(A2,$F$2:$F$50,0) | Vị trí của A2 trong vùng |
=HLOOKUP(A2,$F$1:$Z$4,3,FALSE) | Giống VLOOKUP nhưng dò theo hàng |
=INDEX(B2:D20,2,3) | Ô ở hàng 2, cột 3 của khối |
=XLOOKUP(A2,F:F,H:H,,-1) | Khớp gần đúng: lấy mục nhỏ hơn liền kề (tra theo bậc/mức) |
=OFFSET(A1,2,1) | Ô nằm dưới A1 2 hàng và sang phải 1 cột |
=INDIRECT("Sheet"&B1&"!A1") | Tạo tham chiếu từ văn bản |
=CHOOSE(B2,"Low","Mid","High") | Chọn mục thứ N trong danh sách |
=UNIQUE(A2:A100) | Các giá trị khác nhau trong vùng (tràn kết quả) |
=FILTER(A2:C100,C2:C100="Paid") | Các hàng khớp điều kiện (tràn kết quả) |
Hàm văn bản
Phần lớn bảng tính thực tế bắt đầu bằng văn bản lộn xộn. Đây là các công cụ làm sạch.
| Hàm | Tác dụng |
|---|---|
=LEN(A2) | Số ký tự |
=LEFT(A2,3) / =RIGHT(A2,3) | 3 ký tự đầu / cuối |
=MID(A2,4,5) | 5 ký tự bắt đầu từ vị trí 4 |
=TRIM(A2) | Xóa khoảng trắng ở đầu, ở cuối và khoảng trắng lặp lại |
=CLEAN(A2) | Loại bỏ ký tự không in được trong dữ liệu nhập vào |
=UPPER(A2) / =LOWER(A2) / =PROPER(A2) | Đổi chữ hoa, chữ thường |
=SUBSTITUTE(A2,"-","") | Thay mọi lần xuất hiện của một chuỗi con |
=REPLACE(A2,1,3,"NEW") | Thay theo vị trí thay vì theo nội dung |
=FIND("@",A2) / =SEARCH("@",A2) | Vị trí của chuỗi con (FIND phân biệt chữ hoa, chữ thường) |
=TEXTSPLIT(A2,",") | Tách văn bản ra nhiều ô theo ký tự phân cách |
=TEXTJOIN(", ",TRUE,A2:A9) | Nối một vùng bằng ký tự phân cách, bỏ qua ô trống |
=TEXT(A2,"0.00") | Định dạng số thành văn bản theo một mẫu |
=VALUE(A2) | Chuyển chuỗi chứa số thành số thực sự |
=EXACT(A2,B2) | So sánh có phân biệt chữ hoa, chữ thường |
Hàm ngày và giờ
Excel lưu ngày dưới dạng số, vì vậy bạn có thể trừ hai ngày để ra số ngày.
| Hàm | Tác dụng |
|---|---|
=TODAY() / =NOW() | Ngày hôm nay / ngày và giờ hiện tại |
=YEAR(A2), =MONTH(A2), =DAY(A2) | Lấy một phần của ngày |
=DATE(2026,8,6) | Tạo ngày từ các phần |
=B2-A2 | Số ngày giữa hai ngày |
=DATEDIF(A2,B2,"m") | Số tháng tròn giữa hai ngày ("y", "m", "d") |
=EDATE(A2,3) | Cùng ngày, ba tháng sau |
=EOMONTH(A2,0) | Ngày cuối cùng trong tháng của A2 |
=WEEKDAY(A2,2) | Thứ trong tuần, 1 = Thứ Hai khi có đối số 2 |
=NETWORKDAYS(A2,B2) | Số ngày làm việc giữa hai ngày |
=WORKDAY(A2,10) | Ngày sau A2 10 ngày làm việc |
=TEXT(A2,"yyyy-mm-dd") | Định dạng ngày thành văn bản |
=HOUR(A2), =MINUTE(A2) | Các phần của thời gian |
Hàm làm tròn và hàm số
Làm tròn để hiển thị là việc của định dạng; làm tròn để tính toán là việc của hàm.
| Hàm | Tác dụng |
|---|---|
=ROUND(A2,2) | Làm tròn đến 2 chữ số thập phân |
=ROUNDUP(A2,0) / =ROUNDDOWN(A2,0) | Luôn làm tròn lên / luôn làm tròn xuống |
=MROUND(A2,5) | Làm tròn đến bội số gần nhất của 5 |
=CEILING(A2,1) / =FLOOR(A2,1) | Làm tròn lên / xuống đến một bội số |
=INT(A2) | Bỏ phần thập phân |
=TRUNC(A2,1) | Cắt bớt chữ số thập phân mà không làm tròn |
=RANK(B2,$B$2:$B$20) | Thứ hạng của một giá trị trong vùng |
=PERCENTILE(B2:B20,0.9) | Phân vị thứ 90 |
=STDEV.S(B2:B20) | Độ lệch chuẩn của một mẫu |
=CORREL(B2:B20,C2:C20) | Hệ số tương quan giữa hai cột |
Mã lỗi và ý nghĩa
Mỗi lỗi chỉ ra một sai sót cụ thể, đọc hiểu chúng giúp bạn đỡ phải đoán mò.
| Lỗi | Nguyên nhân | Cách sửa thường gặp |
|---|---|---|
#DIV/0! | Chia cho 0 hoặc cho một ô trống | Bọc trong IFERROR, hoặc chặn trước bằng IF(B2=0,...) |
#N/A | Tra cứu không tìm thấy gì | Kiểm tra khoảng trắng thừa (TRIM) và kiểu dữ liệu có khớp không |
#VALUE! | Sai kiểu đối số: văn bản ở chỗ cần số | Kiểm tra các ô được tham chiếu; thử VALUE() |
#REF! | Công thức trỏ tới một ô đã bị xóa | Tạo lại tham chiếu |
#NAME? | Viết sai tên hàm hoặc văn bản không đặt trong dấu nháy | Sửa chính tả; thêm dấu nháy quanh văn bản |
#NUM! | Kết quả số mà Excel không biểu diễn được | Kiểm tra đối số không hợp lệ, ví dụ SQRT(-1) |
#NULL! | Hai vùng không giao nhau | Kiểm tra xem có thiếu dấu phẩy giữa các đối số không |
#SPILL! | Mảng động không còn chỗ để tràn ra | Xóa trống các ô bên dưới hoặc bên phải |
#### | Không phải lỗi: cột quá hẹp | Kéo rộng cột |
| Tham chiếu vòng | Công thức chứa chính ô của nó | Bỏ phần tự tham chiếu |
Sắp xếp, lọc và công cụ dữ liệu
Lúc tập dữ liệu thôi là một lưới giá trị và bắt đầu trở thành thứ bạn đọc được.
| Việc cần làm | Cách làm |
|---|---|
| Sắp xếp một vùng | Data → Sort, hoặc Alt + A rồi S |
| Thêm nút lọc thả xuống | Ctrl + Shift + L |
| Định dạng thành bảng | Ctrl + T: có vùng được đặt tên và công thức tự mở rộng |
| Xóa dữ liệu trùng lặp | Data → Remove Duplicates |
| Tách một cột thành nhiều cột | Data → Text to Columns |
| Flash Fill (điền theo mẫu) | Ctrl + E |
| Cố định hàng tiêu đề | View → Freeze Panes → Freeze Top Row |
| Định dạng có điều kiện | Home → Conditional Formatting: tô màu ô theo quy tắc |
| Xác thực dữ liệu (danh sách thả xuống) | Data → Data Validation → List |
| Đặt tên cho một vùng | Chọn vùng, rồi gõ tên vào Name Box |
| Truy vết đầu vào của công thức | Formulas → Trace Precedents |
| Goal Seek (tìm giá trị đầu vào) | Data → What-If Analysis → Goal Seek |
Pivot table trong năm bước
Cách nhanh nhất để tóm tắt vài nghìn hàng dữ liệu.
| Bước | Thao tác |
|---|---|
| 1. Làm sạch dữ liệu nguồn | Một hàng tiêu đề, không có hàng trống hay ô đã gộp |
| 2. Chèn | Chọn dữ liệu → Insert → PivotTable |
| 3. Hàng | Kéo trường bạn muốn nhóm theo vào Rows |
| 4. Giá trị | Kéo cột số bạn muốn tính tổng vào Values |
| 5. Tóm tắt | Nhấp vào trường giá trị → Summarize Values By → Sum / Count / Average |
| Thêm chiều thứ hai | Kéo một trường vào Columns |
| Lọc toàn bộ bảng | Kéo một trường vào Filters, hoặc thêm Slicer |
| Hiển thị phần trăm | Trường giá trị → Show Values As → % of Grand Total |
| Làm mới sau khi dữ liệu thay đổi | Alt + F5 |
| Đọc một ô của pivot trong công thức | =GETPIVOTDATA("Sales",$A$3,"Region","EU") |
Phím tắt: những phím cần biết nhất
Khoảng chục phím tắt giúp tiết kiệm thời gian nhất.
| Thao tác | Windows | Mac |
|---|---|---|
| Sửa ô đang chọn | F2 | Ctrl + U |
| Xác nhận và ở lại ô hiện tại | Ctrl + Enter | Ctrl + Enter |
| Xuống dòng trong một ô | Alt + Enter | Ctrl + Option + Enter |
| AutoSum | Alt + = | Cmd + Shift + T |
Bật/tắt $ trong tham chiếu | F4 | Cmd + T |
| Điền xuống từ ô phía trên | Ctrl + D | Cmd + D |
| Điền sang phải | Ctrl + R | Cmd + R |
| Paste Special | Ctrl + Alt + V | Cmd + Ctrl + V |
| Chèn ngày hôm nay | Ctrl + ; | Cmd + ; |
| Lặp lại thao tác vừa làm | F4 | Cmd + Y |
| Hoàn tác / làm lại | Ctrl + Z / Ctrl + Y | Cmd + Z / Cmd + Shift + Z |
| Hiện công thức | Ctrl + ` | Ctrl + ` |
Phím tắt: di chuyển và chọn vùng
Di chuyển trong một trang tính lớn mà không cần chạm tới chuột.
| Thao tác | Windows | Mac |
|---|---|---|
| Nhảy tới mép vùng dữ liệu | Ctrl + phím mũi tên | Cmd + phím mũi tên |
| Chọn tới mép vùng dữ liệu | Ctrl + Shift + phím mũi tên | Cmd + Shift + phím mũi tên |
| Chọn cả cột / cả hàng | Ctrl + Space / Shift + Space | Ctrl + Space / Shift + Space |
| Chọn vùng dữ liệu hiện tại | Ctrl + A | Cmd + A |
| Về ô A1 | Ctrl + Home | Fn + Ctrl + Left |
| Đi tới một ô cụ thể | Ctrl + G | Ctrl + G |
| Trang tính tiếp theo / trước đó | Ctrl + PgDn / PgUp | Option + Right / Left |
| Chèn hàng hoặc cột | Ctrl + Shift + + | Cmd + Shift + + |
| Xóa hàng hoặc cột | Ctrl + - | Cmd + - |
| Ẩn một cột / hàng | Ctrl + 0 / Ctrl + 9 | Cmd + 0 / Cmd + 9 |
| Tìm / thay thế | Ctrl + F / Ctrl + H | Cmd + F / Ctrl + H |
| Chỉ chọn các ô đang hiển thị | Alt + ; | Cmd + Shift + Z |
Phím tắt: định dạng
Các định dạng số là những phím đáng học thuộc nhất, vì bạn sẽ dùng chúng liên tục.
| Thao tác | Windows | Mac |
|---|---|---|
| Hộp thoại Format Cells | Ctrl + 1 | Cmd + 1 |
| In đậm / in nghiêng / gạch chân | Ctrl + B / I / U | Cmd + B / I / U |
| Định dạng tiền tệ | Ctrl + Shift + $ | Ctrl + Shift + $ |
| Định dạng phần trăm | Ctrl + Shift + % | Ctrl + Shift + % |
| Định dạng số với 2 chữ số thập phân | Ctrl + Shift + ! | Ctrl + Shift + ! |
| Định dạng ngày | Ctrl + Shift + # | Ctrl + Shift + # |
| Định dạng General (bỏ định dạng) | Ctrl + Shift + ~ | Ctrl + Shift + ~ |
| Kẻ viền bao ngoài | Ctrl + Shift + & | Cmd + Option + 0 |
| Xóa viền | Ctrl + Shift + _ | Cmd + Option + - |
| Sao chép định dạng (Format Painter) | Ctrl + Shift + C, rồi Ctrl + Shift + V | Cmd + Shift + C, rồi Cmd + Shift + V |
Các công thức, hàm và phím tắt Excel mà bạn dùng nhiều nhất, gom trong một trang. Cheat sheet Excel này là bảng tra cứu nhanh cho những việc thực sự gặp trong bảng tính hằng ngày: viết công thức, tham chiếu ô tuyệt đối và tương đối, IF và các hàm đếm, VLOOKUP và XLOOKUP, làm sạch văn bản, ngày tháng, ý nghĩa của từng mã lỗi, và những phím tắt đáng để học thuộc.
Mọi thứ ở đây đều chạy trên Excel cho Windows và Mac, và gần như tất cả đều dùng được nguyên vẹn trong Google Sheets và LibreOffice Calc. Tên hàm được viết bằng tiếng Anh, vì Excel lưu chúng như vậy bên trong tệp, dù bản Excel không phải tiếng Anh sẽ hiển thị tên đã được dịch.
Câu hỏi thường gặp về cheat sheet Excel
Cheat sheet Excel này có miễn phí không?
Có. Toàn bộ trang này miễn phí, không cần đăng ký và không phải tải gì về. Bạn có thể sao chép bất kỳ công thức nào ngay từ các bảng.
Những công thức Excel quan trọng nhất cần biết là gì?
Nếu chỉ học mười hàm, hãy học: SUM, AVERAGE, IF, COUNTIF, SUMIF, XLOOKUP (hoặc VLOOKUP), INDEX kết hợp MATCH, TRIM, TEXT và IFERROR. Chúng bao quát việc tính tổng, logic điều kiện, lấy giá trị từ bảng khác, làm sạch văn bản lộn xộn, và ngăn lỗi làm hỏng báo cáo.
Dấu $ trong công thức Excel có nghĩa là gì?
$A$1 luôn trỏ tới A1; $A1 giữ cột A nhưng cho phép hàng thay đổi; A$1 giữ hàng 1 nhưng cho phép cột thay đổi. Nhấn F4 (hoặc Cmd + T trên Mac) khi đang sửa tham chiếu để lần lượt chuyển qua bốn kiểu kết hợp.Nên dùng VLOOKUP hay XLOOKUP?
Dùng XLOOKUP nếu Excel của bạn có hàm này (Microsoft 365 và Excel 2021 trở đi). Hàm nhận cột tra cứu và cột trả về là hai đối số riêng, nên tra được sang bên trái, không bị hỏng khi ai đó chèn thêm cột, và có sẵn đối số cho trường hợp "không tìm thấy". VLOOKUP vẫn đáng biết vì nó có mặt trong mọi sổ làm việc cũ mà bạn sẽ tiếp nhận. INDEX/MATCH là cách viết chạy được trên mọi phiên bản Excel.
Các công thức này có dùng được trong Google Sheets không?
Gần như tất cả đều dùng được nguyên vẹn: công thức, tham chiếu ô, IF, COUNTIF/SUMIF, các hàm văn bản và ngày tháng, VLOOKUP, INDEX/MATCH, UNIQUE và FILTER. Phím tắt thì khác nhiều hơn, và một vài hàm chỉ có trong Excel (như một số hàm mảng động mới), nên hãy kiểm tra những gì khác thường trước khi dựa vào chúng.
Vì sao tên hàm trong Excel đôi khi trông khác?
Excel dịch tên hàm theo ngôn ngữ giao diện, nên bản tiếng Đức hiển thị SUMME thay cho SUM, còn bản tiếng Pháp là SOMME. Bản thân tệp vẫn lưu tên tiếng Anh, vì vậy tài liệu, cheat sheet này và các sổ làm việc được chia sẻ đều dùng tên tiếng Anh. Gõ tên đã dịch trong Excel của bạn thì ý nghĩa vẫn hoàn toàn như nhau.
Làm sao để lỗi như #N/A không hiện ra trong báo cáo?
IFERROR, ví dụ =IFERROR(VLOOKUP(A2,F:H,3,FALSE),"Not found"). Dùng IFNA khi bạn chỉ muốn bắt lỗi tra cứu thất bại mà vẫn thấy các vấn đề thật như #VALUE!, vì ẩn mọi lỗi sẽ khiến công thức hỏng trở nên vô hình.Có thể luyện tập các công thức này ở đâu?
Khóa học Excel tương tác miễn phí của Coddy chạy một bảng tính thật ngay trên trình duyệt: mỗi bài học đưa cho bạn một tập dữ liệu nhỏ và một nhiệm vụ, rồi kiểm tra công thức bạn gõ. Không cần cài đặt gì, và cuối khóa có giấy chứng nhận hoàn thành miễn phí.
Tài nguyên liên quan
Thêm cheat sheet
