Để tính phần trăm (%) chênh lệch trong Excel, ta sẽ áp dụng công thức sau:
% Chênh lệch = (Giá trị mới - Giá trị cũ)/Giá trị cũ
Dưới đây mình sẽ hướng dẫn bạn cách để áp dụng công thức này để tính % chênh lệch trong các trường hợp o sánh hai giá trị, so với mục tiêu, theo thời gian,… Đặc biệt, mình sẽ chỉ bạn 1 mẹo sử dụng Pibot Table để tính % chênh lệch cực kỳ nhanh chóng và chuyên nghiệp:
1. Cách tính % chênh lệch trong Excel
Áp dụng công thức % Chênh lệch = (Giá trị mới - Giá trị cũ)/Giá trị cũ, ta sẽ đi vào từng trường hợp thực tế sau:
TH1: Tính % chênh lệch để so sánh 2 giá trị
Ví dụ ta có bảng dữ liệu với:
- A2: Là giá trị cũ.
- B2: Là giá trị mới.
=> % Chênh lệch giữa 2 giá trị là: =(B2 - A2)/A2
Để tránh lỗi khi A2 = 0 khiến công thức không thể tính được, ta sẽ sử dụng hàm IF như sau:
=IF(A2=0,"",(B2-A2)/A2)
TH2: Tính % chêch lệch giữa mục tiêu và thực tế
Ta áp dụng công thức sau:
- % Đạt mục tiêu:
=IF(Muctieu=0,"",Thucte/Muctieu) - % chênh lệch so mục tiêu:
=IF(Muctieu=0,"",(Thucte - Muctieu)/Muctieu) - Chênh lệch (đơn vị tiền/số lượng):
=Thucte - Muctieu
Ví dụ ta có dữ liệu sau:
- A2: là mục tiêu.
- B2: là số thực đạt.
=> Áp dụng công thức ta có:
- Tỷ lệ hoàn thành mục tiêu:
=IF(A2=0;"";B2/A2) - % Chênh lệch với mục tiêu:
=IF(A2=0;"";(B2-A2)/A2) - Chênh lệch so với mục tiêu:
=B2-A2
TH3: Tính % chênh lệch so sánh theo khoảng thời gian
Áp dụng khi cần tính % chênh lệch doanh thu, chi phí, lợi nhuận,… tháng này so với tháng trước, năm này so với năm trước.
Ví dụ:
B2 = giá trị tháng 1, B3 = tháng 2, …
=> % chênh lệch tháng này so với tháng trước ở C3: =IF(B2=0,"",(B3-B2)/B2)
2. Mẹo sử dụng Pivot Table để tính % chênh lệch siêu nhanh
Ngoài cách sử dụng công thức, ta có thể sử dụng Pivot Table trong Excel để tính phần trăm chênh lệch siêu nhanh và đẹp mắt như dưới đây:
Dưới đây mình sẽ hướng dẫn bạn cách thực hiện chi tiết:
Bước 1: Chuyển vùng dữ liệu thành Table
Ví dụ ta có một bảng dữ liệu doanh thu của một công ty trong năm 2020, gồm các cột ngày bán hàng, tên khách hàng và cột số tiền như sau:
Để sử dụng PivotTable dễ dàng, ta sẽ chuyển vùng dữ liệu trên thành dạng bảng bằng cách:
Bôi đen vùng dữ liệu > chọn Insert > chọn Table > khi cửa sổ Creat Table mở ra, kiểm tra lại phạm vi và tích vào ô My table has header (nếu có tiêu đề tại dòng đầu) > chọn Ok.
Tiếp theo, ta sẽ đặt tên cho bảng để thuận tiện cho việc tham chiếu. Ta thực hiện:
=> Tại thẻ Design trong Table Tools, nhập tên vào mục Table Name. Ở ví dụ này bảng được đặt tên là Doanhso.
Bước 2: Tạo PivotTable để tính phần trăm thay đổi
Để tạo PivotTable ta vào: Insert → PivotTable → nguồn là bảng vừa tạo → OK.
Bước 3: Sắp sếp trường
Để PivotTable hiển thị dữ liệu theo ý muốn, ta cần sắp xếp trường:
- Kéo Ngày bán hàng vào Rows (Excel tự group theo Month).
- Kéo Số tiền vào Values 2 lần (đều là Sum).
Bước 4: Tính tỷ lệ phần trăm thay đổi
Để tạo cột phần trăm thay đổi từ cột Sum of Số tiền 2 , ta thực hiện:
- Chọn cột Sum of Số tiền (thứ 2) → Right-click Show Values As → % Difference From.
- Đặt Base field = Month, Base item = Previous (so tháng hiện tại với tháng trước).
- Đổi tên tiêu đề cột cho dễ hiểu (ví dụ: % Chênh lệch MoM).
3. Tùy chỉnh để hiển thị % chênh lệch 1 cách trực quan
3.1. Định dạng kết quả ở dạng %
Nếu các phép tính áp dụng ở mục 1 cho kết quả về dạng 1 số thập phân thay vì hiển thị phần trăm. Thì ta thực hiện định dạng với Format Cells như sau:
- Bôi đen vùng dữ liệu
- Mở Format cells:
- Cách 1: Nhấn tổ hợp phím tắt Ctrl + 1
- Cách 2: Click chuột phải > chọn Format Cells.
- Ở mục Category chọn Number
- Ở mục chi danh sách chọn Percentage > tùy chỉnh số lượng số sau dấu phẩy > nhấn Ok.
3.2. Tạo mũi tên tăng giảm dựa trên kết quả % chênh lệch
Để hiển thị kết quả % chênh lệch 1 cách trực quan hơn, ta có thể thêm các dấu mũi tên vào trong kết quả như sau:
Để thực hiện ta làm như sau:
Bước 1: Chọn 1 ô trong cột % Chênh lệch.
Bước 2: Trên thẻ Home, chọn Conditional Formatting, tại danh sách xổ xuống, chọn New Rule.
Bước 3: Chỉnh sửa trong cửa sổ New Formatting Rule.
+ Chọn All cells showing “% Chênh lệch” values for “Months”.
+ Chọn Icon Sets trong mục Format Style.
+ Chọn biểu tượng hình tam giác đỏ, xanh và thanh ngang trong danh sách Icon Style.
+ Thay đổi Percentage thành Number trong mục Type để thay đổi cột Value về giá trị 0.
Sau đó ấn OK để hoàn tất.
Kết quả sẽ được hiển thị với mũi tên tăng giảm như sau:
Trên đây là hướng dẫn chi tiết cách tính % chênh lệch trong Excel. Chúc các bạn áp dụng thành công!
