Công thức tính phần trăm tăng trong Excel là =(new-old)/old, tức (mới trừ cũ) chia cho cũ. Với giá trị năm ngoái ở B2 và năm nay ở C2, gõ =(C2-B2)/B2 rồi định dạng ô theo phần trăm. Kết quả dương là tăng, kết quả âm là giảm.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Last year | This year | Change |
| 2 | Coffee | $12,400 | $14,880 | 20.0% |
| 3 | Tea | $8,600 | $7,740 | -10.0% |
| 4 | Juice | $5,200 | $5,460 | 5.0% |
| 5 | Water | $3,100 | $4,650 | 50.0% |
Coffee tăng 20,0% còn Tea giảm 10,0%, hiện thành -10,0%. Water tăng 50,0%. Đổi một số ở cột C thì phần trăm cập nhật theo. Dấu ngoặc rất quan trọng: thiếu chúng, =C2-B2/B2 sẽ chia trước rồi lấy doanh số năm nay trừ đi 1.
Hai cách viết cùng một công thức
=(C2-B2)/B2 the change divided by the old value
=C2/B2-1 the new value as a share of the old, minus 100%
Cả hai cho cùng kết quả. Cách đầu đọc giống định nghĩa, nên sau này dễ kiểm tra hơn. Dù viết cách nào, số chia luôn là giá trị cũ. Chia cho giá trị mới là lỗi thường gặp nhất, và nó cho ra con số khác: từ 80 lên 100 là +25%, nhưng 20 chia 100 lại là 20%.
Khi không số nào là giá trị cũ, chẳng hạn so sánh hai cửa hàng với nhau, hãy chia phần chênh lệch cho trung bình của hai số. Đó là phần trăm chênh lệch, và với 80 và 100, kết quả là 22,2% dù số nào đứng trước:
=ABS(B2-C2)/AVERAGE(B2,C2)
Phần trăm thay đổi so với tháng trước
Với một danh sách theo thời gian, so sánh mỗi hàng với hàng phía trên. Tháng đầu tiên không có gì để so, nên công thức bắt đầu từ hàng thứ hai.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Visitors | Change |
| 2 | Jan | 4200 | |
| 3 | Feb | 4620 | 10.0% |
| 4 | Mar | 4389 | -5.0% |
| 5 | Apr | 5047 | 15.0% |
| 6 | May | 4795 | -5.0% |
| 7 | Jun | 5754 | 20.0% |
Tháng 2 tăng 10,0% so với tháng 1 và tháng 3 giảm 5,0%. Để so mọi tháng với tháng 1, hãy khóa giá trị gốc bằng dấu đô la: =(B3-$B$2)/$B$2. Trang tham chiếu tuyệt đối giải thích dấu $.
Phần trăm thay đổi khi giá trị ban đầu bằng 0
Khi giá trị cũ bằng 0, công thức chia cho 0 và trả về #DIV/0!. Tăng lên từ con số 0 thì không có phần trăm, nên hãy quyết định ô sẽ hiện gì thay vào đó và kiểm tra trước:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Last year | This year | Plain | Checked |
| 2 | Coffee | 12400 | 14880 | 20.0% | 20.0% |
| 3 | Cocoa | 0 | 2100 | #DIV/0! | new |
| 4 | Soda | 0 | 640 | #DIV/0! | new |
D3 và D4 hiện #DIV/0!; cột có kiểm tra hiện "new". =IFERROR((C2-B2)/B2,"new") cho cùng kết quả ở đây, nhưng nó cũng che luôn các lỗi thật, như có chữ trong cột doanh số. Trang về lỗi chia cho 0 trình bày vấn đề này tổng quát hơn.
Luyện tập: viết công thức phần trăm thay đổi
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Old price | New price | Change |
| 2 | Bread | $2.40 | $2.76 |
Đến lượt bạn: Trong D2, tính phần trăm thay đổi từ giá cũ ở B2 sang giá mới ở C2. Ô đã được định dạng phần trăm sẵn.
Phần trăm thay đổi và điểm phần trăm
Khi bản thân các giá trị đã là phần trăm, có hai đáp án khác nhau cùng được gọi là "mức thay đổi". Tỷ lệ chuyển đổi đi từ 4% lên 5% là tăng 1 điểm phần trăm, đồng thời cũng là tăng 25%.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Page | Before | After | Points | Percent change |
| 2 | Signup | 4.0% | 5.0% | 1.0% | 25.0% |
D2 lấy hai tỷ lệ trừ nhau và hiện 1,0%, con số bạn sẽ báo cáo là "1 điểm phần trăm". E2 hiện 25,0%. Hãy nói rõ bạn đang nói tới con số nào, vì câu "tỷ lệ tăng 1%" có thể hiểu theo cả hai nghĩa.
Giá trị cũ là số âm
Khi giá trị cũ là số âm, chẳng hạn lỗ chuyển thành lãi, công thức thông thường cho ra sai dấu: từ -200 lên 100, =(C2-B2)/B2 trả về -150%. Hãy chia cho giá trị tuyệt đối của số cũ:
=(C2-B2)/ABS(B2)
Từ -200 lên 100, công thức này trả về 150%, tức là tăng. Với mức tăng trưởng qua nhiều năm, tốc độ trung bình mỗi năm hữu ích hơn một con số phần trăm lớn: =(C2/B2)^(1/5)-1 biến mức thay đổi sau năm năm thành tốc độ tăng trưởng kép hằng năm (CAGR).
Câu hỏi thường gặp
Công thức tính phần trăm tăng trong Excel là gì?
=(C2-B2)/B2, trong đó B2 là giá trị cũ và C2 là giá trị mới, với ô được định dạng phần trăm. =C2/B2-1 cho cùng kết quả. Từ 80 lên 100, kết quả là 25%.
Làm sao tính phần trăm giảm trong Excel?
Dùng đúng công thức đó, =(C2-B2)/B2. Khi giá trị mới nhỏ hơn, kết quả là số âm: từ 100 xuống 80 là -20%. Excel không có công thức riêng cho phần trăm giảm.
Làm sao tính phần trăm thay đổi khi giá trị cũ bằng 0?
Thay đổi từ 0 không có phần trăm, và =(C2-B2)/B2 trả về #DIV/0!. Hãy cho ô hiện nội dung khác trong trường hợp này: =IF(B2=0,"n/a",(C2-B2)/B2).
Làm sao tính phần trăm thay đổi với số âm?
Chia cho giá trị tuyệt đối của số cũ: =(C2-B2)/ABS(B2). Khi đó một khoản lỗ -200 chuyển thành lãi 100 sẽ hiện +150%, tức là tăng, thay vì -150% gây hiểu nhầm như công thức thông thường.
