Đăng nhập

Excel từ con số không: công thức, hàm và những cạm bẫy không ai cảnh báo bạn

Phương pháp làm việc hoàn chỉnh cho Excel như một công cụ phân tích — dấu bằng, tham chiếu ô và khóa đô la, phạm vi, các họ hàm quan trọng, tổng có điều kiện, tra cứu khớp chính xác, bảng, bảng xoay (pivots), biểu đồ trung thực và mã lỗi cho bạn biết điều gì thực sự hỏng.

Cập nhật lần cuối: 2026-08-19

Phòng thí nghiệm Excel tương tác: mở phòng thí nghiệm song ngữ — một công cụ tính toán công thức thực sự với 64 hàng dữ liệu trực tiếp, nơi mọi công thức bạn gõ đều chạy và các ô bạn tham chiếu sẽ sáng lên.

Đọc bản tiếng Việt: Excel từ gốc — công thức, hàm, và những cái bẫy không ai cảnh báo. This guide pairs with four auto-graded practice courses: Excel basics, Formulas and references, Functions and intro statistics, and Data analysis.

Hầu hết mọi người học Excel như một đống mẹo rời rạc. Ai đó chỉ cho họ VLOOKUP, họ ghi lại, và sáu tháng sau họ vẫn không thể giải thích tại sao đôi khi nó trả về sai hàng. Hướng dẫn này đi theo con đường khác. Có lẽ có khoảng sáu ý tưởng bên dưới toàn bộ Excel, và một khi bạn nắm giữ chúng, vài trăm hàm sẽ ngừng là một danh sách để ghi nhớ và trở thành một từ vựng mà bạn có thể tư duy.

Ý tưởng duy nhất: trỏ chuột, không gõ

Một bảng tính không phải là một chiếc máy tính bỏ túi với giao diện đẹp hơn. Sự khác biệt chính là điểm cốt lõi của công cụ này.

Loại =25012 vào một ô và bạn nhận được 3000, và bạn chẳng thu được gì hơn so với điện thoại. Gõ =B312 và bạn đã xây dựng một cỗ máy nhỏ: khi số trong B3 thay đổi, câu trả lời của bạn cũng thay đổi theo. Bạn không tính toán một kết quả, bạn mô tả một mối quan hệ. Mọi thứ khác trong Excel đều được xây dựng dựa trên động thái duy nhất đó.

Điều này mang lại cho bạn quy tắc làm việc đầu tiên, và nó có giá trị hơn bất kỳ hàm nào: nếu một số xuất hiện ở bất kỳ đâu khác trên trang tính, hãy trỏ vào ô của nó thay vì gõ lại. Ngoại lệ là một hằng số phổ quát — mười hai tháng trong một năm, một trăm xu trong một đô la — bởi vì những thứ đó sẽ không bao giờ thay đổi, nên việc gán cho chúng một ô là không mang lại lợi ích gì.

Quy tắc nghe có vẻ hiển nhiên nhưng liên tục bị phá vỡ. Các con số được mã hóa cứng ẩn bên trong công thức là lý do phổ biến nhất khiến bảng tính bị sai mà không ai nhận ra. Có một bài kiểm tra hai giây: Ctrl và phím dấu huyền. Mọi ô sẽ chuyển từ hiển thị kết quả sang hiển thị công thức, và các số được gõ vào sẽ nổi bật ngay lập tức. Nhấn lại để quay lại.

Tham chiếu và dấu đô la cố định chúng

Mỗi ô đều có một địa chỉ, được viết theo thứ tự cột-rồi-hàng: E2 là cột E, hàng 2. Trỏ đến một trang tính khác bằng cách thêm tên của nó ở tiền tố: Data!E2.

Khi bạn sao chép một công thức, Excel không sao chép văn bản. Nó sao chép mối quan hệ. Viết =B312 trong D3, kéo xuống D6, và Excel viết =B612 cho bạn, bởi vì những gì bạn thực sự nói là "ô trên hàng của tôi, ở cột B". Đây là lý do tại sao bạn không bao giờ gõ lại công thức năm trăm lần, và đó là ý tưởng cốt lõi thứ hai.

Đó cũng là nguồn gốc của sự cố phổ biến nhất trong các bảng tính cơ bản. Giả sử bạn muốn mỗi hàng là tỷ lệ phần trăm của tổng số nằm ở B12. Bạn viết =B3/B12điền xuống, và nó sụp đổ — bởi vì tham chiếu thứ hai cũng di chuyển, và bây giờ trỏ tới B13, B14, B15, vốn là các ô trống. Giải pháp là một dấu đô la, cố định một tham chiếu để việc điền không thể di chuyển nó:

Câu hỏi cần đặt ra trước mỗi lần điền đơn giản là: khi công thức này di chuyển, những gì được phép di chuyển theo nó? Câu trả lời cho bạn biết chính xác vị trí của các dấu đô la.

Toán tử và thứ tự thực thi của chúng

Excel sử dụng +, -, *, / và ^. Không có x cho phép nhân và không có số mũ trên cho lũy thừa, và việc gõ một trong hai sẽ tạo ra lỗi thay vì phép tính.

Các phép toán chạy theo thứ tự toán học tiêu chuẩn: dấu ngoặc trước, sau đó đến số mũ, tiếp theo là nhân và chia từ trái sang phải, cuối cùng là cộng và trừ từ trái sang phải. Dấu ngoặc không phải là trang trí; chúng là cách duy nhất để ghi đè lên thứ tự đó.

Minh họa kinh điển là phần trăm thay đổi. Giữa năm ngoái ở E3 và năm nay ở D3, công thức đúng là =(D3-E3)/E3. Bỏ dấu ngoặc đơn và viết =D3-E3/E3 và Excel sẽ ngoan ngoãn chia E3 cho chính nó trước, ra 1, rồi trừ đi — tạo ra một con số lên đến hàng trăm nghìn phần trăm. Nó không báo lỗi. Nó chỉ trả lời một câu hỏi khác với câu bạn vừa hỏi, và nó sẽ tiếp tục làm thế một cách âm thầm miễn là bạn còn cho phép.

Điều đó đưa ra nguyên tắc quan trọng hơn bất kỳ cú pháp nào: một công thức chạy được chưa chắc đã là một công thức đúng. Mọi kết quả đều đáng được kiểm tra nhanh trong hai giây dựa trên những gì bạn biết về dữ liệu. Nếu năm ngoái và năm nay gần như y hệt mà bảng tính báo thay đổi 299.900%, bảng tính đó không nói gì về việc kinh doanh của bạn cả.

Phạm vi và dấu hai chấm tạo nên chúng

B3:B11 có nghĩa là "từ B3 đến B11". Dấu hai chấm đó biến chín ô riêng lẻ thành một khối duy nhất mà bạn có thể đưa vào một hàm.

=B3+B4+B5+B6+B7+B8+B9+B10+B11 và =SUM(B3:B11) trả về cùng một số ngày hôm nay. Chúng không tốt ngang nhau. Phiên bản khoảng ô ngắn hơn, ít có khả năng sai hơn rất nhiều, và vẫn sống sót nếu bạn chèn thêm một hàng ở giữa — điều mà chuỗi dấu cộng âm thầm không làm được.

Một thói quen nên rèn luyện: đừng bao giờ để một khoảng ô nuốt mất hàng tiêu đề. =SUM(B2:B11) với B2 chứa từ "Revenue" tình cờ hoạt động được, vì SUM bỏ qua văn bản. =COUNT(B2:B11) trên cùng một khoảng ô thì không, và sự khác biệt đó sẽ không tự thông báo cho bạn biết.

Các hàm thực sự xứng đáng có chỗ đứng

Có hàng trăm hàm. Trên thực tế, một nhà phân tích dùng khoảng hai mươi hàm, và những hàm dưới đây bao phủ phần lớn công việc thực tế.

Mô tả một cột

Trước khi bạn vẽ dù chỉ một biểu đồ, hãy mô tả dữ liệu của mình. SUM, AVERAGE, MEDIAN, COUNT, COUNTA, MAX, MIN và STDEV mất khoảng một phút giữa các hàm và sẽ bắt được phần lớn các lỗi ẩn giấu trong một tập dữ liệu mới.

Ba điểm khác biệt trong danh sách đó quan trọng hơn vẻ bề ngoài của chúng:

MAX và MIN đáng được nhắc đến đặc biệt vì chúng là cách kiểm tra chất lượng dữ liệu rẻ nhất tồn tại. Một số âm trong khi số âm là điều bất khả thi, một con số cao vô lý do gõ thừa một số không, một ngày tháng đi lạc vào cột tiền bạc — tất cả đều nổi lên trong hai giây.

Để bảng tính tự quyết định

IF gồm ba phần, luôn theo thứ tự này: phép kiểm thử, giá trị trả về khi đúng, và giá trị trả về khi sai. =IF(D3>1000,"High","Low"). Văn bản bạn trả về phải nằm trong ngoặc kép; số thì không. Phần thứ ba bị thiếu là lỗi kinh điển của người mới bắt đầu — bỏ quên nó và nhánh sai sẽ trả về từ FALSE vào dữ liệu của bạn.

AND và OR lắp vào vị trí kiểm thử khi một điều kiện là chưa đủ. =IF(AND(C2="Beach",A2=2025),"Yes","No") chỉ kích hoạt khi cả hai đều thỏa mãn. Lưu ý điểm bất đối xứng khiến ai cũng sập bẫy: phép so sánh văn bản cần dấu ngoặc kép, phép so sánh số thì tuyệt đối không được có, và đổi chỗ hai cái đó là cách bạn viết ra một điều kiện không bao giờ có thể đúng.

Tính tổng những gì thực sự quan trọng

Đây là bước chuyển từ biết dùng Excel sang có thể phân tích bằng Excel.

=SUMIF(criteria_range, criteria, sum_range) chỉ tính tổng các hàng khớp điều kiện. =COUNTIF(criteria_range, criteria) đếm chúng. Tiêu chí có thể là một giá trị, "Beach", hoặc một phép so sánh được viết dưới dạng văn bản, ">50000".

Thứ tự đối số chính là cái bẫy. Trong SUMIF khoảng ô bạn kiểm thử đứng trước và khoảng ô bạn cộng đứng sau. SUMIFS — phiên bản số nhiều nhận nhiều điều kiện — thứ tự bị đảo ngược, và khoảng ô bạn cộng đứng đầu tiên, theo sau là các cặp khoảng-và-tiêu chí. Chuyển đổi một hàm SUMIF đang chạy thành SUMIFS mà không đảo ngược các đối số thì Excel vẫn chạy. Nó chỉ trả về một con số khác, không lỗi và không cảnh báo.

Nối hai bảng

Một hàm tra cứu trả lời câu hỏi "với khóa này, giá trị tương ứng ở đằng kia là gì".

=XLOOKUP(value, lookup_range, return_range) là hàm hiện đại. Nó khớp chính xác theo mặc định, có thể nhìn sang trái lẫn sang phải, và có thêm đối số thứ tư tùy chọn để trả về khi không có gì khớp.

VLOOKUP là hàm cũ hơn và mang một mặc định thực sự nguy hiểm. Đối số cuối cùng của nó điều khiển chế độ khớp, và nếu bạn bỏ trống, VLOOKUP sẽ thực hiện khớp tương đối: nó duyệt dọc cột và trả về giá trị cuối cùng nhỏ hơn hoặc bằng khóa của bạn. Trên dữ liệu chưa sắp xếp, đó thực chất là một hàng ngẫu nhiên. Nó không báo lỗi. Luôn kết thúc hàm VLOOKUP với FALSE (hoặc 0), mọi lúc mọi nơi.

Sống sót khi tiếp xúc với dữ liệu thực

Hai hàm làm phần lớn công việc làm sạch. TRIM loại bỏ khoảng trắng thừa, và LEN đếm ký tự để bạn có thể tìm ra khoảng trắng thừa từ đầu. Điều này quan trọng vì "Beach " có kèm khoảng trắng thừa, đối với Excel, là một giá trị hoàn toàn khác biệt so với "Beach". Hàm COUNTIF của bạn bị đếm thiếu, biểu đồ trục của bạn sinh ra một danh mục trùng lặp, và không có lỗi nào xuất hiện cả. UPPER và LOWER làm công việc tương tự cho việc viết hoa bất nhất.

IFERROR(calculation, fallback) là công cụ sinh tồn còn lại. =E2/F2 vẫn ổn cho đến khi F2 trống hoặc bằng không, lúc đó cột sẽ tràn ngập #DIV/0! và một ô lỗi sẽ làm độc hại mọi hàm SUM phía sau nó. =IFERROR(E2/F2,"") tính toán bình thường và trả về ô trống khi không thể tính được.

Bảng không phải là định dạng

Nhấn Ctrl+T trên một khối dữ liệu thực hiện một thay đổi về cấu trúc, chứ không phải hình thức. Vùng dữ liệu trở thành một đối tượng được đặt tên, tự động mở rộng khi bạn thêm hàng, mọi công thức và biểu đồ trỏ tới nó đều cập nhật theo, và cho phép bạn viết =[@Revenue]/[@NumBikes] thay vì =E2/F2.

Phần cuối cùng đó có giá trị hơn vẻ bề ngoài của nó. Một tham chiếu có cấu trúc đọc giống như một câu văn. Sáu tháng sau, =[@Revenue]/[@NumBikes] vẫn cho bạn biết nó làm gì, trong khi =E2/F2 lại bắt buộc bạn phải vào xem.

Hai thiết lập khi bạn tạo một bảng: bật Header Row (Hàng tiêu đề) và Banded Rows (Hàng so le), và để nguyên Total Row (Hàng tổng) trừ khi có yêu cầu cụ thể, vì hàng tổng sẽ chèn thêm một hàng vào vùng dữ liệu và làm hỏng các công thức đếm một cách âm thầm.

Sắp xếp và cú nhấp chuột phá hủy dữ liệu

Sắp xếp là thao tác thông thường nguy hiểm nhất trong Excel.

Chọn một cột đơn lẻ, nhấn Sort (Sắp xếp), và Excel sẽ hỏi có muốn mở rộng vùng chọn không. Chọn "Continue with the current selection" (Tiếp tục với vùng chọn hiện tại) và cột đó sẽ bị xáo trộn trong khi mọi cột khác đứng yên. Mọi hàng trong tập dữ liệu của bạn giờ đây đã bị đứt gãy — doanh thu ở hàng 40 thuộc về một địa điểm khác với địa điểm nằm cạnh nó — và không có cách nào khôi phục lại cái nào đi với cái nào. Undo (Hoàn tác) là giải pháp duy nhất, và chỉ khi bạn nhận ra ngay lập tức.

Luôn luôn mở rộng vùng chọn. Tốt hơn hết là hãy sắp xếp bên trong một Bảng Excel (Excel Table), cấu trúc của nó vốn dĩ không cho phép xảy ra lỗi này.

Đối với việc sắp xếp nhiều cấp độ — theo loại xe, sau đó theo năm trong mỗi loại, sau đó theo quý trong mỗi năm — hãy thực hiện trong một lần. Mở Data (Dữ liệu), rồi chọn Sort (Sắp xếp), thiết lập cấp độ đầu tiên, và sử dụng Add Level (Thêm cấp độ) cho cấp độ thứ hai và thứ ba. Ba lần sắp xếp riêng lẻ không xếp chồng lên nhau; mỗi lần sẽ xóa đi thứ tự của lần trước đó, nên bạn chỉ giữ lại lần cuối cùng.

Ngược lại, các bộ lọc rất an toàn: bộ lọc ẩn các hàng chứ không xóa chúng. Dù sao vẫn có hai lưu ý. Các hàng bị ẩn vẫn được tính trong SUM, vì vậy hãy sử dụng SUBTOTAL khi bạn muốn tính tổng chỉ những gì hiển thị. Và không bao giờ gửi một file vẫn đang bật bộ lọc, vì người mở nó sẽ đọc phần dữ liệu bạn cắt ra như thể đó là toàn bộ tập dữ liệu.

PivotTables trong bốn vùng thả

Một PivotTable tóm tắt hàng nghìn dòng chỉ trong vài giây, và toàn bộ sức mạnh của nó nằm ở một quyết định: trường nào nằm ở vùng nào.

Viết ra bốn yếu tố đó trước khi bạn tạo là một thói quen kỷ luật tốt, bởi vì danh sách đó chính là câu hỏi của bạn, được nêu ra một cách chính xác.

Một cái bẫy. Khi bạn kéo một trường vào Values, Excel sẽ đoán cách tổng hợp, và nếu cột đó chứa dù chỉ một ô trống hoặc ô văn bản, nó sẽ đoán là Count (Đếm) thay vì Sum (Tổng). Khi đó bạn đọc số "64" và tin rằng mình đang xem doanh thu. Nhãn ở góc của bảng pivot sẽ cho bạn biết bạn nhận được kết quả nào. Hãy đọc nó trước khi đọc con số.

Biểu đồ và nơi chúng nói dối

Loại biểu đồ được chọn dựa trên câu hỏi, không phải theo sở thích. So sánh các danh mục cần dùng biểu đồ cột. Thay đổi theo thời gian cần dùng biểu đồ đường. Mối quan hệ giữa hai biến số cần dùng biểu đồ phân tán. Các phần của một tổng thể có thể dùng biểu đồ tròn, nhưng chỉ với rất ít phần — quá bốn hoặc năm phần là mắt người không thể so sánh các góc, và biểu đồ cột sẽ làm tốt hơn.

Sau đó là câu hỏi về tính trung thực, thực chất là cốt lõi của việc am hiểu dữ liệu. Lấy một biểu đồ cột và bắt đầu trục giá trị của nó từ 48 thay vì 0, sự chênh lệch hai phần trăm sẽ chiếm nửa biểu đồ. Không có gì bạn vẽ là sai cả. Bức tranh dù sao cũng là một lời nói dối, bởi vì mắt người đọc chiều cao cột là số lượng, nên các biểu đồ cột bắt buộc phải bắt đầu từ số không. Biểu đồ đường có thể bị cắt bớt, vì mắt người đọc độ dốc thay vì chiều cao và không có phép so sánh ẩn dụ nào như vậy được thực hiện.

Thêm hai quy tắc không tốn gì: đặt đơn vị lên nhãn trục, và trích dẫn nguồn bên dưới biểu đồ. Một biểu đồ không có đơn vị chỉ là vật trang trí, và một biểu đồ không có nguồn chỉ là một lời khẳng định.

Mã lỗi là chẩn đoán

Các lỗi của Excel không phải là thất bại, chúng là một từ vựng chẩn đoán nhỏ. Hãy học năm lỗi này và bạn sẽ khắc phục hầu hết các sự cố mà không cần tra cứu gì.

Đi đâu tiếp theo

Đọc phần này một lần rồi bạn sẽ quên hầu hết, điều đó hoàn toàn bình thường và ổn. Những phần đọng lại chính là những phần bạn tự tay gõ vào.

Mở Excel Lab, nơi chạy công cụ công thức thực tế trên 64 hàng dữ liệu trực tiếp — hãy nhập công thức, xem cách nó tính toán và quan sát các ô bị tác động sáng lên trên lưới. Sau đó, hãy hoàn thành bốn khóa học thực hành, nơi mỗi bài học là một bộ bài kiểm tra cho bạn biết chính xác công thức của bạn còn thiếu gì: cơ bản, công thức và tham chiếu, hàm và thống kê, và phân tích dữ liệu.

Bốn mươi sáu bài học, đều được chấm điểm tự động, hoàn toàn trên trình duyệt. Không cần cài đặt gì cả.

Thấy hữu ích chứ? Nhận hướng dẫn mới mỗi tuần

Một email mỗi tuần: hướng dẫn mới, bài hát mới, một câu lệnh hay. Không spam — hủy đăng ký chỉ với một phản hồi.

← Excel từ gốc — công thức, hàm, và những cái bẫCẩm nang kênh YouTube ẩn danh →
🌐 Tiếng ViệtEnglishTiếng ViệtEspañol日本語Bản dịch bằng AI · bản gốc