Có thể bạn quan tâm: Lỗi #value! Trong Excel Là Gì? Nguyên Nhân, Cách Khắc Phục Và Những Lưu Ý Quan Trọng
Mở Đầu: Tại Sao Việc Tính Tỉ Lệ Trong Excel Lại Quan Trọng?
Trong môi trường làm việc hiện đại, Excel không chỉ là một công cụ tính toán đơn thuần mà còn là “điểm tựa” giúp chúng ta phân tích, trực quan hoá dữ liệu và đưa ra quyết định nhanh chóng. Một trong những thao tác thường gặp và vô cùng thiết yếu là tính tỉ lệ (ratio) – từ việc tính phần trăm doanh thu, tỉ lệ tăng trưởng, tỉ lệ phần trăm chi phí đến việc so sánh các chỉ số kinh doanh, học tập, y tế,…
Việc nắm vững cách tính tỉ lệ trong Excel không chỉ giúp bạn:
- Tiết kiệm thời gian: Tự động hoá các phép tính lặp đi lặp lại.
- Đảm bảo độ chính xác: Tránh sai sót do tính tay.
- Trực quan hoá dữ liệu: Dễ dàng tạo biểu đồ, báo cáo.
- Nâng cao kỹ năng phân tích: Hiểu sâu hơn về mối quan hệ giữa các biến số.
Bài viết này sẽ hướng dẫn chi tiết, từng bước một, từ các công thức cơ bản đến các kỹ thuật nâng cao, kèm theo ví dụ thực tế và mẹo tối ưu hoá. Đọc xong bạn sẽ có thể tự tin tính tỉ lệ trong mọi tình huống.
Có thể bạn quan tâm: Sử Dụng Excel Cơ Bản: Hướng Dẫn Chi Tiết Từ A Đến Z
1. Kiến Thức Cơ Bản Về Tỉ Lệ
1.1 Định Nghĩa Tỉ Lệ
- Tỉ lệ là một mối quan hệ giữa hai đại lượng, thể hiện dưới dạng phần trăm hoặc tỷ lệ (ví dụ: 3:4, 0.75).
- Công thức chung:
\text{Tỉ lệ} = \frac{\text{Giá trị phần}}{\text{Giá trị tổng}} \times 100\%
1.2 Các Đơn Vị Thường Gặp
| Đơn vị | Mô tả | Ví dụ |
|---|---|---|
| % | Phần trăm | 25% = 0.25 |
| ‰ | Phần nghìn | 5‰ = 0.005 |
| Ratio | Tỷ lệ (a:b) | 3:5 |
| Decimal | Thập phân | 0.75 |
1.3 Khi Nào Nên Dùng Tỉ Lệ?
- So sánh các phần trong một tổng thể (doanh thu, chi phí, điểm số).
- Đánh giá mức độ thay đổi (tăng trưởng, giảm sút).
- Chuẩn hoá dữ liệu để so sánh giữa các nhóm có kích thước khác nhau.
Có thể bạn quan tâm: Phím Tắt Chuyển Sheet Excel: Tối Ưu Hoá Công Việc Và Tăng Tốc Độ Làm Việc
2. Các Cách Tính Tỉ Lệ Cơ Bản Trong Excel
2.1 Sử Dụng Công Thức Thủ Công
Giả sử có bảng dữ liệu:
| A | B | C |
|---|---|---|
| 1 | Danh mục | Doanh thu (VND) |
| 2 | Sản phẩm A | 120,000,000 |
| 3 | Sản phẩm B | 80,000,000 |
| 4 | Sản phẩm C | 100,000,000 |
| 5 | Tổng | 300,000,000 |
Bước 1: Tính tổng (ô C5) bằng công thức =SUM(C2:C4).
Bước 2: Tính tỉ lệ cho mỗi sản phẩm:
- Ô D2:
=C2/$C$5→ Kết quả là 0.4 (40%). - Sao chép công thức từ D2 xuống D4.
Bước 3: Định dạng cột D dưới dạng Phần trăm (Ctrl+Shift+% hoặc vào Home → Number → Percentage).
Kết quả:
| D |
|---|
| 40% |
| 26.67% |
| 33.33% |
2.2 Sử Dụng Hàm PERCENTAGE (Không có trong Excel, nhưng có hàm TEXT để định dạng)
Nếu muốn hiển thị tỉ lệ dưới dạng chuỗi (ví dụ “40%”), dùng:
=TEXT(C2/$C$5, "0.00%")
2.3 Tính Tỉ Lệ Tăng/Giảm (Growth Rate)
Giả sử có doanh thu năm 2026 và 2026:
| Năm | Doanh thu |
|---|---|
| 2026 | 150,000,000 |
| 2026 | 180,000,000 |
Công thức tính tăng trưởng:
=(B2-B1)/B1
- B1: doanh thu năm trước.
- B2: doanh thu năm hiện tại.
Kết quả: 0.2 → 20% (định dạng %).
2.4 Tính Tỉ Lệ Phần Trăm Trên Tổng Tổng (Grand Total)
Khi có nhiều cấp độ (ví dụ: khu vực → cửa hàng), bạn có thể tính tỉ lệ phần trăm trên tổng tổng bằng cách sử dụng PivotTable (phần 3) hoặc công thức SUMIFS.
3. Tính Tỉ Lệ Với PivotTable (Bảng Tổng Hợp)
PivotTable là công cụ mạnh mẽ giúp tính tỉ lệ nhanh chóng mà không cần viết công thức phức tạp.
3.1 Tạo PivotTable Cơ Bản
- Chọn dữ liệu →
Insert → PivotTable. - Chọn vị trí (một sheet mới hoặc hiện tại).
- Kéo trường:
Rows: Danh mục (Sản phẩm).Values: Doanh thu → Đặt Value Field Settings → Sum.- Thêm Tỷ lệ:
- Kéo lại trường
Doanh thuvào khu vựcValueslần nữa. - Click chuột phải →
Show Values As → % of Grand Total.
3.2 Kết Quả
| Sản phẩm | Tổng doanh thu | % of Grand Total |
|---|---|---|
| A | 120,000,000 | 40% |
| B | 80,000,000 | 26.67% |
| C | 100,000,000 | 33.33% |
| Grand Total | 300,000,000 | 100% |
PivotTable tự động cập nhật khi dữ liệu thay đổi.
3.3 Tính % of Column / Row
Bạn có thể thay đổi Show Values As thành:
% of Column Total– tỉ lệ trong cột.% of Row Total– tỉ lệ trong hàng.% of Parent Row Total– tỉ lệ so với cấp cha (đối với dữ liệu phân cấp).
3.4 Định Dạng Số
Chọn cột tỉ lệ → Home → Number → Percentage → Đặt số chữ số thập phân (2 hoặc 1 tùy nhu cầu).
4. Các Hàm Excel Hữu Ích Cho Tính Tỉ Lệ
| Hàm | Mô tả | Cú pháp | Ví dụ |
|---|---|---|---|
SUM | Tổng hợp | =SUM(range) | =SUM(C2:C4) |
SUMIF | Tổng có điều kiện | =SUMIF(range, criteria, sum_range) | =SUMIF(A2:A10,"A",C2:C10) |
SUMIFS | Tổng có nhiều điều kiện | =SUMIFS(sum_range, criteria_range1, criteria1, …) | =SUMIFS(C2:C10, A2:A10, "A", B2:B10, "2026") |
COUNT | Đếm số ô chứa số | =COUNT(range) | =COUNT(C2:C10) |
COUNTIF | Đếm có điều kiện | =COUNTIF(range, criteria) | =COUNTIF(A2:A10,"A") |
AVERAGE | Trung bình | =AVERAGE(range) | =AVERAGE(C2:C10) |
TEXT | Định dạng chuỗi | =TEXT(value, format_text) | =TEXT(C2/$C$5,"0.00%") |
IF | Điều kiện | =IF(logical_test, value_if_true, value_if_false) | =IF(C2/$C$5>0.5,"High","Low") |
PERCENTILE | Phần trăm vị trí | =PERCENTILE(array, k) | =PERCENTILE(C2:C10,0.75) |
4.1 Tính Tỉ Lệ Với SUMIFS
Giả sử có bảng chi phí theo phòng ban và năm:
| A | B | C |
|---|---|---|
| 1 | Phòng | Năm |
| 2 | Kinh doanh | 2026 |
| 3 | Kinh doanh | 2026 |
| 4 | Marketing | 2026 |
| 5 | Marketing | 2026 |
| 6 | Tổng |
Chi phí nằm ở cột D.
Tính tổng chi phí Kinh doanh 2026:
=SUMIFS(D2:D5, A2:A5, "Kinh doanh", B2:B5, 2026)
Tính tỉ lệ chi phí Kinh doanh 2026 trên tổng chi phí 2026:
=SUMIFS(D2:D5, A2:A5, "Kinh doanh", B2:B5, 2026) / SUMIFS(D2:D5, B2:B5, 2026)
Sau đó định dạng kết quả thành %.
5. Tính Tỉ Lệ Trong Các Tình Huống Thực Tế
5.1 Tính Tỉ Lệ Lợi Nhuận Gộp
| Sản phẩm | Doanh thu | Giá vốn bán |
|---|---|---|
| A | 150,000,000 | 90,000,000 |
| B | 200,000,000 | 120,000,000 |
| C | 250,000,000 | 150,000,000 |
Bước 1: Tính lợi nhuận gộp: =Doanh thu - Giá vốn.
- Ô D2:
=B2-C2→ 60,000,000. - Sao chép xuống D4.
Bước 2: Tính tỉ lệ lợi nhuận gộp trên doanh thu:
- Ô E2:
=D2/B2→ 0.4 → 40%.
Kết quả:
| Sản phẩm | Lợi nhuận gộp | Tỉ lệ Lợi nhuận |
|---|---|---|
| A | 60,000,000 | 40% |
| B | 80,000,000 | 40% |
| C | 100,000,000 | 40% |
5.2 Tính Tỉ Lệ Tham Gia Khảo Sát
Khi thực hiện khảo sát, bạn có thể muốn biết tỷ lệ trả lời đúng so với tổng số câu hỏi.
| Người | Số câu trả lời đúng | Tổng câu hỏi |
|---|---|---|
| 1 | 45 | 50 |
| 2 | 38 | 50 |
| 3 | 50 | 50 |
Công thức: =B2/C2
- Kết quả: 90%, 76%, 100%.
Bạn có thể dùng Conditional Formatting để tô màu các tỷ lệ <80% (đánh dấu cần cải thiện).
5.3 Tính Tỉ Lệ Chi Phí Quảng Cáo Trên Doanh Thu
Có thể bạn quan tâm: Mẫu To‑do List Excel: Hướng Dẫn Tạo, Sử Dụng Và Tối Ưu Hoá Công Việc Hàng Ngày
| Tháng | Doanh thu | Chi phí quảng cáo |
|---|---|---|
| Jan | 120,000,000 | 12,000,000 |
| Feb | 150,000,000 | 15,000,000 |
| Mar | 130,000,000 | 13,500,000 |
Công thức: =C2/B2
- Kết quả: 10% cho mỗi tháng.
Bạn có thể tạo biểu đồ Line để theo dõi xu hướng chi phí quảng cáo.
6. Trực Quan Hóa Tỉ Lệ Bằng Biểu Đồ
6.1 Biểu Đồ Cột % Stacked
- Chọn dữ liệu tỉ lệ (cột tỉ lệ và tổng).
- Vào Insert → Column → Stacked Column.
- Định dạng Data Labels → Show Percentage.
6.2 Biểu Đồ Waterfall (Thác Nước)
Dùng để hiển thị tăng/giảm tỉ lệ qua các giai đoạn.
- Chuẩn bị cột “Giá trị” và “Tỉ lệ thay đổi”.
- Chọn Insert → Waterfall.
6.3 Biểu Đồ Pie (Tròn)
Thích hợp cho % of Grand Total.
- Chọn cột danh mục + tỉ lệ.
- Insert → Pie → 2-D Pie.
- Kích hoạt Data Labels → Percentage.
Lưu ý: Tránh dùng biểu đồ tròn khi có quá nhiều mục (hơn 7) vì sẽ mất độ rõ ràng.
7. Mẹo Nhanh Và Thủ Thuật Tối Ưu Khi Tính Tỉ Lệ
| Mẹo | Mô tả | Cách thực hiện |
|---|---|---|
| Absolute Reference | Khi sao chép công thức, giữ cố định ô tổng ($C$5). | Dùng phím F4 khi chọn ô. |
| Quick Percentage | Nhấn Ctrl+Shift+% để chuyển nhanh sang định dạng %. | Áp dụng cho ô đã có kết quả thập phân. |
| AutoFill | Kéo góc dưới phải để sao chép công thức nhanh. | Đảm bảo công thức đúng relative/absolute. |
| Conditional Formatting | Đánh dấu tỉ lệ > 80%/ < 50% bằng màu. | Home → Conditional Formatting → New Rule → Format only cells that contain. |
| Paste Special → Values | Khi muốn giữ kết quả tỉ lệ mà không để lại công thức. | Copy → Paste Special → Values. |
| Data Validation | Ngăn người dùng nhập giá trị >100% trong cột tỉ lệ. | Data → Data Validation → Whole number ≤100. |
| Dynamic Named Ranges | Tự động mở rộng phạm vi khi thêm dữ liệu. | Formulas → Name Manager → Define =OFFSET($C$2,0,0,COUNTA($C:$C)-1,1). |
| Power Query | Khi dữ liệu lớn, tính tỉ lệ trong Power Query trước khi load. | Data → Get Data → From Table/Range → Add Custom Column =Doanh thu/Tổng. |
8. Xử Lý Các Lỗi Thường Gặp Khi Tính Tỉ Lệ
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
#DIV/0! | Chia cho 0 (tổng = 0). | Kiểm tra dữ liệu; dùng IFERROR hoặc IF để tránh: =IF($C$5=0,0,C2/$C$5). |
#VALUE! | Ô chứa văn bản không phải số. | Đảm bảo dữ liệu là số; dùng VALUE để chuyển: =VALUE(C2)/$C$5. |
| Định dạng sai | Kết quả hiển thị 0.4 thay vì 40%. | Định dạng ô thành Percentage hoặc dùng TEXT. |
| Sai phạm vi | Công thức tham chiếu sai ô hoặc cột. | Kiểm tra $ và phạm vi trong công thức. |
| Khi dùng PivotTable, % không cập nhật | Dữ liệu nguồn thay đổi nhưng Pivot chưa refresh. | Click chuột phải trên Pivot → Refresh. |
9. Tính Tỉ Lệ Nâng Cao Với Power Pivot & DAX
Nếu bạn làm việc với Excel 2026 trở lên hoặc Microsoft 365, Power Pivot cho phép tạo mô hình dữ liệu và dùng DAX (Data Analysis Expressions) để tính tỉ lệ phức tạp.
9.1 Kích Hoạt Power Pivot
File → Options → Add-ins.- Chọn COM Add-ins → Go.
- Tick Microsoft Power Pivot for Excel → OK.
9.2 Tạo Bảng Dữ Liệu
Import dữ liệu vào Data Model (Power Pivot). Giả sử bảng Sales có các cột: Product, Year, Revenue, Cost.
9.3 Công Thức DAX Để Tính Tỷ Lệ Lợi Nhuận
Profit Ratio :=
DIVIDE( SUM(SalesRevenue) - SUM(SalesCost), SUM(SalesRevenue), 0
)
DIVIDEgiúp tránh lỗi chia cho 0.- Kết quả là một cột tính toán (Calculated Column) hoặc Measure.
9.4 Tạo Measure Tỷ Lệ % of Total
Revenue % of Total :=
DIVIDE( SUM(SalesRevenue), CALCULATE(SUM(SalesRevenue), ALL(Sales)), 0
)
ALL(Sales)bỏ mọi filter, trả về tổng doanh thu toàn bộ.- Khi đặt vào PivotTable, tỷ lệ sẽ tự động thay đổi theo filter (năm, sản phẩm).
9.5 Sử Dụng Measure Trong PivotTable
- Thêm
Revenue % of Totalvào Values. - Đặt Show Values As → % of Grand Total (cũng có thể để mặc định vì DAX đã tính).
10. Tổng Kết Và Những Bước Tiếp Theo
10.1 Tổng Kết Các Kiến Thức Đã Học
| Chủ đề | Điểm mấu chốt |
|---|---|
| Định nghĩa tỉ lệ | Phần trăm = (phần / tổng) × 100% |
| Công thức cơ bản | Sử dụng =C2/$C$5 + định dạng % |
| PivotTable | Tính % of Grand Total nhanh, tự động cập nhật |
| Hàm Excel | SUMIFS, IFERROR, TEXT, … |
| Trực quan hoá | Biểu đồ Column Stacked, Pie, Waterfall |
| Power Pivot & DAX | Measure DIVIDE, % of Total cho dữ liệu lớn |
| Mẹo tối ưu | Absolute reference, conditional formatting, data validation |
10.2 Bước Tiếp Theo Để Nâng Cao Kỹ Năng
- Thực hành: Tạo bảng dữ liệu cá nhân (doanh thu, chi phí, KPI) và áp dụng các công thức.
- Khám phá Power Query: Làm sạch dữ liệu, tính tỉ lệ ngay trong quá trình ETL.
- Học DAX sâu hơn: Các hàm thời gian (
TOTALYTD,SAMEPERIODLASTYEAR) để tính tỉ lệ tăng trưởng theo năm. - Tham gia cộng đồng: Các forum như MrExcel, Reddit r/excel để học hỏi các trường hợp thực tế.
- Sử dụng VBA (Macro): Khi cần tự động hoá tính tỉ lệ trên nhiều bảng cùng lúc.
11. FAQ – Các Câu Hỏi Thường Gặp
Q1: Làm sao để tính tỉ lệ mà không làm mất dữ liệu gốc?
A: Dùng công thức trong cột mới, hoặc sao chép kết quả và Paste Special → Values để giữ giá trị tĩnh.
Q2: Khi dùng PivotTable, tại sao % of Grand Total không khớp với công thức tính thủ công?
A: Kiểm tra xem có Filter hoặc Slicer nào đang áp dụng không; Pivot tính % dựa trên dữ liệu đã lọc.
Q3: Có thể tính tỉ lệ cho nhiều tiêu chí đồng thời không?
A: Có, dùng SUMIFS để lấy tổng theo nhiều tiêu chí, sau đó chia cho tổng chung.
Q4: Excel trả về 0% thay vì giá trị thực tế, nguyên nhân?
A: Có thể vì dữ liệu là text (số được lưu dưới dạng chuỗi). Dùng VALUE hoặc chuyển định dạng sang Number.
Q5: Cách tạo báo cáo tỉ lệ tự động gửi email mỗi tuần?
A: Dùng Power Automate hoặc VBA SendMail để xuất file Excel (hoặc PDF) và gửi qua Outlook.
12. Kết Luận
Tính tỉ lệ trong Excel không chỉ là một phép toán đơn giản mà còn là một kỹ năng quan trọng giúp bạn phân tích, đánh giá và trình bày dữ liệu một cách chuyên nghiệp. Từ các công thức cơ bản, PivotTable, đến Power Pivot và DAX, mỗi công cụ đều có ưu điểm riêng, phù hợp với các mức độ phức tạp và khối lượng dữ liệu khác nhau.
Hãy bắt đầu áp dụng ngay những kiến thức vừa học vào công việc hoặc dự án cá nhân của bạn. Khi đã thành thạo, bạn sẽ nhận ra rằng việc tính tỉ lệ không còn là nỗi lo, mà trở thành một phần tự nhiên trong quy trình làm việc với Excel.
Chúc bạn thành công và luôn khám phá thêm nhiều tính năng mới của Excel!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.