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

Trong Excel, đơn vị phần trăm (%) là khái niệm thông dụng, tuy nhiên việc xác định giá trị % lại khá đa dạng và không hề giản đơn. Chúng ta hãy cùng nhau khám phá cách để xây dựng công thức tính phần trăm % trên Excel một cách tỉ mỉ qua bài viết của Siêu Marketing.

Cách định dạng phần trăm (%) trong Excel

Trong Excel, phần trăm được hiểu là số bé hơn 0. Để tiện thao tác và tính toán, số này cần có một định dạng đặc biệt. Excel cho phép ta sử dụng định dạng Percentage qua chức năng Format Cells.

Khi áp dụng định dạng này, số 0.05 sẽ được thay đổi thành 5% với biểu tượng %.

Để hiển thị phần số sau dấu phẩy trong số phần trăm, ta chọn số chữ số cần thể hiện tại Decimal places.

Do đó, để dữ liệu hiện ra với ký tự %, ta cần định dạng ô dữ liệu đó thành phần trăm. Nếu không, Excel sẽ chỉ xem đây là một con số thông thường mà không có ký tự % đi kèm.

Biểu thức tính phần trăm (%) trên Excel

Biểu thức tính phần trăm của tổng cộng (% of Grand Total)

Để xác định xem mỗi giá trị chiếm bao nhiêu phần trăm so với tổng chung, công thức dùng là phần trăm của tổng cộng (hay % of Grand Total). Cụ thể như sau:

 

 

Cách tính:

Bước 1: Xác định tổng số ở ô B8 bằng hàm SUM

B8=Sum(B2:B7)

Bước 2: Mỗi ô trong cột B sẽ được chia cho tổng cộng tại B8

C2=B2/B8

Chúng ta nên giữ cố định ô B8 bằng cách sử dụng $B$8 để tỷ lệ mới đúng

Bước 3: Sao chép công thức ở ô C2 xuống các ô từ C3 tới C7 (kỹ thuật Filldown)

Bước 4: Điều chỉnh thành định dạng % trong Format Cells. Lưu ý cần giữ nguyên hai số sau dấu phẩy nếu có.

Kết quả thu được là:

  • Dạng công thức chung: Giả sử một phần tử là A, tổng số là Sum_A
  • Định dạng dữ liệu thành Percentage thay vì nhân với *100%

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

Trước dữ liệu được phân thành những nhóm khác nhau, để biết mỗi giá trị chiếm bao nhiêu % của tổng một nhóm, cách tính như sau:

Cách tính:

Bước 1: Xác lập tổng của nhóm bằng SUM

B2=SUM(B3:B6)

Bước 2: Phần tử nào cũng chia cho tổng nhóm đó. Lưu ý giữ nguyên vị trí ô chứa tổng.

C3=B3/$B$2

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

Nếu có nhóm khác, thực hiện lại bước 1 bước 2

Bước 3: Chọn định dạng Percentage cho dữ liệu

Kiểm tra bằng cách tính tổng % trong mỗi nhóm với hàm SUM. Tổng của mỗi nhóm phải đạt 100% (tại các ô C2 và C7)

Dạng công thức chung:

Mỗi phần tử gọi là A

Tổng số của nhóm chứa A là Subtotal_A

Để làm tròn tổng số cho mỗi nhóm, các bạn có thể

Hãy nghiên cứu về hàm SUBTOTAL

Các bước tính tỷ lệ phần trăm chênh lệch (% of Difference)

Tỷ lệ phần trăm chênh lệch thường được sử dụng để so sánh và xác định sự chênh lệch phần trăm giữa hai mục tiêu liên tiếp.

Quy trình tính toán:

Bước thứ nhất: Tính ra số lượng chênh lệch giữa hai chỉ tiêu. Ví dụ:

Hà Nội có chỉ tiêu ban đầu là 100, trong khi Hải Phòng có chỉ tiêu là 85 sau đó.

Chênh lệch được tính là chỉ tiêu sau trừ đi chỉ tiêu trước = B3-B2

Bước thứ hai: Chia phần chênh lệch cho chỉ tiêu ban đầu để có tỷ lệ phần trăm chênh lệch.

=(B3-B2)/B2

Chỉ tiêu giảm so với trước sẽ cho số âm, còn tăng thì sẽ cho số dương.

Mô hình công thức chung:

Giả sử chỉ tiêu ban đầu là A, chỉ tiêu sau đó là B.

Một ứng dụng thực tế của mô hình này là thiết kế các báo cáo bằng biểu đồ Water fall.

Giới thiệu cách tạo biểu đồ Water fall cho việc quản lý sự chênh lệch chi phí dự án trong Excel 2016

Công thức tính tỷ lệ lũy tiến (% of Running Total)

Tỷ lệ phần trăm lũy tiến phản ánh tỷ lệ hoàn thành công việc theo từng giai đoạn, tỷ lệ này sẽ tăng dần đến khi đạt 100%.

Cách thực hiện tính toán:

Bước thứ nhất: Xác định tổng sản lượng của cả quá trình tại ô C7 với công thức = SUM(C2:C6) = 22

Bước thứ hai: Tính tổng lũy tích sau mỗi lần ghi nhận:

=SUM($C$2:C2)

Tính tổng lũy tích sử dụng hàm SUM và giữ cố định ô C2, mở rộng dần vùng tính tổng theo từng dòng.

Bước thứ ba: Phần trăm lũy tiến tính được bằng cách chia tổng của mỗi giai đoạn cho tổng cả quá trình.

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

Điểm cố định trong công thức này là ô C7.

Biểu thức công thức chung:

Giả sử các giá trị A1, A2, A3… là tổng từng đoạn và Sum_A là tổng cả quá trình.

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

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

Đây là phương pháp tính % thường dùng trong báo cáo trên Excel. Để mở rộng kiến thức, bạn có thể tham khảo các thông tin sau:

1. Áp dụng hàm làm tròn số khi cần làm tròn kết quả tính toán %, bởi kết quả thường xuyên có số lẻ.

Tìm hiểu về hàm làm tròn số trong Excel và nguyên tắc làm tròn

2. Phương pháp hiển thị giá trị % khi làm báo cáo với Pivot Table

Khám phá cách thể hiện giá trị phần trăm qua báo cáo với Pivot Table

3. Khám phá về kỹ năng làm báo cáo trên Excel

Tại sao lại cần học kỹ năng sắp xếp dữ liệu và làm báo cáo trên Excel

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop