Hướng dẫn cách lập công thức tính phần trăm % trên Excel chi tiết nhất

Đơn vị phần trăm (%) là một trong những đơn vị thường gặp khi làm việc trên Excel. Tuy nhiên, cách tính phần trăm lại rất đa dạng và phức tạp. Vậy hãy cùng Học Excel Online tìm hiểu cách lập công thức tính phần trăm % trên Excel một cách chi tiết nhé.

Định dạng dữ liệu phần trăm (%) trong Excel

Để hiểu và tính toán dễ dàng, dữ liệu phần trăm trong Excel cần được định dạng riêng. Trong Format Cells của Excel, chúng ta có thể chọn định dạng là Percentage.

Định dạng dữ liệu phần trăm trong Excel

Khi định dạng dữ liệu như vậy, số 0.05 sẽ được hiển thị dưới dạng 5%.

Nếu muốn biểu diễn số thập phân trong %, chúng ta có thể chọn số chữ số phần thập phân muốn hiển thị.

Nên nhớ rằng, để dữ liệu hiển thị ở dạng %, chúng ta phải định dạng cho ô dữ liệu đó. Nếu không, Excel sẽ coi đó chỉ là một con số thông thường.

Cách tính phần trăm (%) trên Excel

Công thức tính phần trăm của tổng chung (% of Grand Total)

Khi muốn biết mỗi giá trị trong mỗi phần tử chiếm bao nhiêu phần trăm của tổng số, chúng ta sử dụng công thức tính phần trăm của tổng chung (% of Grand Total).

Cách tính như sau:

Bước 1: Xác định tổng chung tại ô B8 bằng hàm SUM.

B8 = SUM(B2:B7)

Bước 2: Chia từng phần tử cho tổng chung.

C2 = B2/B8

Lưu ý rằng chúng ta cố định ô B8 thành $B$8 để có thể áp dụng công thức cho các ô khác.

Bước 3: Áp dụng công thức ở C2 cho các ô từ C3 đến C7 (sử dụng kỹ thuật Filldown).

Bước 4: Định dạng dữ liệu về dạng % bằng Format Cells. Có thể chỉnh số lẻ thập phân, ví dụ hai chữ số sau dấu phẩy.

Vậy là chúng ta đã có:

  • Công thức tổng quát: Gọi giá trị một phần tử là A, tổng toàn bộ các phần tử là Sum_A.
% of Grand Total = (A/Sum_A)*100%

Có thể thay việc nhân với 100% bằng cách định dạng dữ liệu là Percentage.

Công thức tính phần trăm của tổng nhóm (% of Parent Total)

Khi dữ liệu được chia thành các nhóm, chúng ta muốn biết mỗi phần tử chiếm bao nhiêu % trong nhóm đó, ta tính theo cách sau:

Bước 1: Xác định tổng của nhóm đó bằng hàm SUM.

B2 = SUM(B3:B6)

Bước 2: Chia từng phần tử trong nhóm cho tổng của cả nhóm. Lưu ý cố định vị trí ô tính tổng.

C3 = B3/$B$2

Áp dụng công thức chỉ trong nhóm, từ C3 đến C6.

Với các nhóm khác, lặp lại Bước 1 và Bước 2.

Bước 3: Định dạng dữ liệu thành dạng Percentage.

Để kiểm tra, chúng ta có thể tính tổng của từng nhóm bằng hàm SUM. Kết quả sẽ luôn là 100% (ở các ô C2 và C7).

Công thức tổng quát:

Gọi từng phần tử là A.

Tổng của nhóm chứa phần tử A là Subtotal_A.

% of Parent Total = (A/Subtotal_A)*100%

Để tính tổng của từng nhóm, bạn có thể tìm hiểu thêm về hàm SUBTOTAL.

Công thức tính phần trăm chênh lệch (% of Difference)

Phần trăm chênh lệch là tỷ lệ chênh lệch của hai đối tượng liền kề. Thường được sử dụng để so sánh để xác định tỷ lệ chênh lệch.

Cách tính như sau:

Bước 1: Xác định phần chênh lệch giữa các chỉ tiêu trước và sau. Ví dụ:

Chỉ tiêu trước là Hà Nội có số lượng 100. Chỉ tiêu sau là Hải Phòng có số lượng 85.

Phần chênh lệch là hiệu của chỉ tiêu sau trừ chỉ tiêu trước.

Bước 2: Lấy phần chênh lệch chia cho chỉ tiêu trước để ra tỷ lệ chênh lệch.

Khi đó, nếu chỉ tiêu sau < chỉ tiêu trước, sẽ có số âm. Chỉ tiêu sau > chỉ tiêu trước sẽ có số dương.

Công thức tổng quát:

Gọi chỉ tiêu trước là A.

Chỉ tiêu sau là B.

% of Difference A và B = ((B-A)/A)*100%

Một ví dụ tiêu biểu của dạng này là để tạo báo cáo bằng biểu đồ thác nước (Waterfall) để quản lý chênh lệch chi phí dự án trong Excel 2016.

Công thức tính phần trăm lũy tiến (% of Running Total)

Phần trăm lũy tiến là tỷ lệ đạt được sau mỗi lần ghi nhận kết quả. Tỷ lệ này tăng dần và cuối cùng đạt đến 100% khi hoàn thành công việc.

Cách tính như sau:

Bước 1: Tính tổng toàn bộ quá trình. Tại ô C7, ghi nhận tổng sản lượng bằng hàm SUM.

C7 = SUM(C2:C6) = 22

Bước 2: Tính tổng sau mỗi lần ghi nhận kết quả.

=SUM($C$2:C2)

Sử dụng hàm SUM để tính tổng. Lưu ý cố định điểm bắt đầu là ô C2, vùng tính tổng mở rộng dần ra theo mỗi dòng.

Bước 3: Lấy tổng từng lần chia cho tổng toàn bộ quá trình để ra tỷ lệ % lũy tiến sau mỗi lần.

E2 = SUM($C$2:C2)/$C$7

Tại mỗi lần, chúng ta chia tổng cho tổng toàn bộ (ô C7). Do đó, cần cố định ô C7 trong công thức.

Công thức tổng quát:

Gọi tổng từng đoạn là các giá trị A1, A2, A3…

Tổng toàn bộ các đoạn là Sum_A.

% of Running Total tại A1 = (A1/Sum_A)*100%

% of Running Total tại A2 = (A2/Sum_A)*100%

Trên đây là những cách tính % thường gặp trong các báo cáo trên Excel. Để bổ sung thêm kiến thức, bạn có thể tìm hiểu các bài viết khác trên Trang Tin Đời Sống.

Trang Tin Đời Sống

Rate this post