Hướng dẫn này chỉ cho bạn cách tính phần trăm thay đổi giữa các số, cột, hàng cũng như phần trăm tăng và giảm trong Excel.
Nếu bạn làm việc với các con số rất nhiều trong công việc hàng ngày của mình, bạn thường sẽ cần tính toán tỷ lệ phần trăm. Excel làm cho điều này trở nên thực sự dễ dàng bằng cách giúp bạn tính toán các loại tỷ lệ phần trăm khác nhau bằng các công thức và phương pháp khác nhau. Tính toán Phần trăm trong một trang tính Excel tương tự như tính toán trong các bài toán ở trường của bạn, nó rất dễ hiểu và dễ sử dụng.
Một trong những phép tính phần trăm phổ biến nhất mà mọi người thực hiện trong Excel là tính phần trăm thay đổi giữa hai giá trị trong một khoảng thời gian. Phần trăm Thay đổi hoặc Phương sai Phần trăm được sử dụng để hiển thị tỷ lệ thay đổi (tăng trưởng hoặc suy giảm) giữa hai giá trị từ thời kỳ này sang thời kỳ khác. Nó có thể được sử dụng cho các mục đích tài chính, thống kê và nhiều mục đích khác.
Ví dụ: nếu bạn muốn tìm sự khác biệt giữa doanh số bán hàng của năm ngoái và doanh số bán hàng của năm nay, bạn có thể tính toán nó với phần trăm thay đổi. Trong hướng dẫn này, chúng tôi sẽ trình bày cách tính phần trăm thay đổi cũng như phần trăm tăng và giảm giữa hai số trong Excel.
Tính toán phần trăm thay đổi trong Excel
Tính toán Phần trăm Thay đổi giữa hai giá trị là một nhiệm vụ dễ dàng. Bạn chỉ cần tìm sự khác biệt giữa hai số và chia kết quả với số ban đầu.
Có hai công thức khác nhau mà bạn có thể sử dụng để tính toán phần trăm thay đổi. Cú pháp của chúng như sau:
= (new_value - original_value) / original_valuehoặc là
= (Giá trị mới / Giá trị ban đầu) - 1- new_value - là giá trị hiện tại / cuối cùng của hai số bạn đang so sánh.
- giá trị trước đây - là giá trị ban đầu của hai số bạn đang so sánh để tính phần trăm thay đổi.
Thí dụ:
Chúng ta hãy xem danh sách đơn đặt hàng trái cây giả định này với cột B chứa số lượng trái cây được đặt hàng Tháng trước và cột C chứa số lượng trái cây được đặt hàng trong tháng này:
Ví dụ: bạn đã đặt một số lượng trái cây nhất định vào tháng trước và tháng này bạn đặt nhiều hơn những gì bạn đã đặt tháng trước, bạn có thể nhập công thức dưới đây để tìm phần trăm chênh lệch giữa hai đơn đặt hàng:
= (C2-B2) / B2Công thức trên sẽ được nhập vào ô D2 để tìm phần trăm thay đổi giữa C2 (new_value) và B2 (original_value). Sau khi nhập công thức vào một ô, nhấn Enter để thực thi công thức. Bạn nhận được phần trăm thay đổi trong các giá trị thập phân như hình dưới đây.
Kết quả chưa được định dạng dưới dạng phần trăm. Để áp dụng định dạng phần trăm cho một ô (hoặc một dải ô), hãy chọn (các) ô, sau đó nhấp vào nút ‘Kiểu phần trăm’ trong nhóm Số của tab ‘Trang chủ’.
Giá trị thập phân sẽ được chuyển đổi thành phần trăm.
Tính phần trăm thay đổi giữa hai cột số
Bây giờ, chúng ta biết tỷ lệ phần trăm thay đổi giữa hai đơn đặt hàng cho Táo, hãy tìm tỷ lệ phần trăm thay đổi cho tất cả các mặt hàng.
Để tính toán phần trăm thay đổi giữa hai cột, bạn cần nhập công thức vào ô đầu tiên của cột kết quả và Tự động điền công thức xuống toàn bộ cột.
Để làm điều đó, hãy nhấp vào chốt điền (hình vuông nhỏ màu xanh lục ở góc dưới bên phải của ô) của ô công thức và kéo nó xuống phần còn lại của ô.
Bây giờ bạn đã có các giá trị phần trăm thay đổi cho hai cột.
Khi giá trị mới cao hơn giá trị ban đầu, phần trăm kết quả sẽ là số dương. Nếu giá trị mới giá trị thấp hơn giá trị ban đầu, thì kết quả sẽ là số âm.
Như bạn có thể thấy, tỷ lệ phần trăm thay đổi đối với ‘Apples’ được tăng lên 50%, vì vậy giá trị là số dương. Tỷ lệ phần trăm cho ‘Avocodo’ giảm 14%, do đó kết quả là âm tính (-14%).
Bạn cũng có thể sử dụng định dạng tùy chỉnh để làm nổi bật phần trăm âm bằng màu đỏ, do đó, bạn có thể dễ dàng xác định nó khi đang tìm kiếm.
Để định dạng ô, hãy chọn ô bạn muốn định dạng, nhấp chuột phải và chọn tùy chọn ‘Định dạng ô’.
Trong cửa sổ Định dạng Ô xuất hiện, nhấp vào "Tùy chỉnh" ở cuối menu bên trái và nhập mã bên dưới vào hộp văn bản "Loại:":
0,00%; [Đỏ] -0,00%Sau đó, nhấp vào nút ‘OK’ để áp dụng định dạng.
Bây giờ, kết quả âm tính sẽ được tô màu đỏ. Ngoài ra, định dạng này làm tăng số lượng vị trí thập phân để hiển thị tỷ lệ phần trăm chính xác.
Bạn cũng có thể sử dụng một công thức khác để tính toán phần trăm thay đổi giữa hai giá trị (Thứ tự Tháng trước và Thứ tự Tháng này):
= (C2 / B2) -1Công thức này chia new_value (C2) cho giá trị ban đầu (B2) và lấy kết quả trừ đi ‘1’ để tìm phần trăm thay đổi.
Tính phần trăm thay đổi theo thời gian
Bạn có thể tính toán tỷ lệ phần trăm thay đổi theo chu kỳ (thay đổi tháng này sang tháng khác) để hiểu tốc độ tăng hoặc giảm từ giai đoạn này sang giai đoạn tiếp theo (cho mỗi tháng). Điều này rất hữu ích khi bạn muốn biết tỷ lệ phần trăm thay đổi hàng tháng hoặc hàng năm trong một khoảng thời gian cụ thể.
Trong ví dụ dưới đây, chúng ta có giá trái cây của tháng 3 trong cột B và giá của tháng 7, 5 tháng sau.
Sử dụng công thức chung này để tìm phần trăm thay đổi theo thời gian:
= ((Giá_trị hiện tại / Giá_trị gốc) / Giá_trị ban đầu) * NỞ đây, N là viết tắt của số khoảng thời gian (năm, tháng, v.v.) giữa hai giá trị ban đầu và hiện tại.
Ví dụ: Nếu bạn muốn hiểu tỷ lệ lạm phát hoặc giảm phát giá hàng tháng trong hơn 5 tháng trong ví dụ trên, bạn có thể sử dụng công thức dưới đây:
= ((C2-B2) / B2) / 5Công thức này tương tự như công thức chúng ta đã sử dụng trước đây, ngoại trừ, chúng ta cần chia giá trị phần trăm thay đổi cho số tháng giữa hai tháng (5).
Tính toán phần trăm tăng trưởng / thay đổi giữa các hàng
Giả sử bạn có một cột số liệt kê giá xăng hàng tháng trong hơn một năm.
Bây giờ, nếu bạn muốn tính toán phần trăm tăng hoặc giảm giữa các hàng để bạn có thể hiểu những thay đổi hàng tháng của giá, thì hãy thử công thức dưới đây:
= (B3-B2) / B2Vì chúng ta cần tìm phần trăm tăng trưởng của tháng 2 (B3) so với giá của tháng 1 (B2), chúng ta cần để trống hàng đầu tiên vì không so sánh tháng 1 với tháng trước. Nhập công thức vào ô C3 và nhấn Enter.
Sau đó, sao chép công thức vào các ô còn lại để xác định phần trăm chênh lệch giữa mỗi tháng.
Bạn cũng có thể tính toán phần trăm thay đổi cho mỗi tháng so với tháng 1 (B2). Để làm điều này, bạn cần đặt ô đó thành tham chiếu tuyệt đối bằng cách thêm dấu $ vào tham chiếu ô, ví dụ: $ C $ 2. Vì vậy, công thức sẽ giống như sau:
= (B3- $ B $ 2) / $ B $ 2Cũng giống như trước, chúng ta bỏ qua ô đầu tiên và nhập công thức vào ô C3. Khi bạn sao chép công thức vào phần còn lại của ô, tham chiếu tuyệt đối ($ B $ 2) không thay đổi, trong khi tham chiếu tương đối (B3) sẽ thay đổi thành B4, B5, v.v.
Bạn sẽ nhận được tỷ lệ phần trăm lạm phát hoặc giảm phát cho mỗi tháng so với tháng Giêng.
Tính toán phần trăm tăng trong Excel
Tính toán phần trăm tăng tương tự như tính toán phần trăm thay đổi trong Excel. Phần trăm tăng là tỷ lệ tăng lên giá trị ban đầu. Bạn sẽ cần hai con số để tính toán phần trăm gia tăng, số Gốc và số New_number.
Cú pháp:
Phần trăm tăng = (New_number - Original_number) / Original_numberTất cả những gì bạn phải làm là trừ số ban đầu (đầu tiên) cho số mới (thứ hai) và chia kết quả cho số ban đầu.
Ví dụ: bạn có một danh sách các hóa đơn và số tiền của chúng trong hai tháng (tháng 4 và tháng 5). Nếu hóa đơn của bạn cho tháng 4 được tăng vào tháng 5, thì mức tăng phần trăm từ tháng 4 đến tháng 5 là bao nhiêu? Bạn có thể sử dụng công thức dưới đây để tìm hiểu:
= (C2-B2) / B2Ở đây, chúng tôi trừ hóa đơn tiền điện tháng 5 (C2) với hóa đơn tháng 4 (B2) và chia kết quả cho hóa đơn tháng 4. Sau đó, sao chép công thức sang các ô khác bằng cách sử dụng chốt điền.
Nếu bạn nhận được kết quả dương tính ($ 24,00%), tỷ lệ phần trăm sẽ tăng lên giữa hai tháng và nếu bạn nhận được kết quả âm tính (ví dụ: - $ 13,33%), thì tỷ lệ phần trăm thực tế sẽ giảm chứ không phải tăng.
Tính toán phần trăm giảm trong Excel
Bây giờ, hãy xem cách tính tỷ lệ phần trăm giảm giữa các số. Cách tính phần trăm giảm rất giống với phép tính tăng phần trăm. Điểm khác biệt duy nhất là số mới sẽ nhỏ hơn số ban đầu.
Công thức gần giống với phép tính tăng phần trăm, ngoại trừ ở điểm này, bạn lấy giá trị mới (số đầu tiên) trừ giá trị mới (số thứ hai) trước khi chia kết quả cho giá trị ban đầu.
Cú pháp:
Phần trăm giảm = (Original_number - New_number) / Original_numberGiả sử bạn có danh sách thiết bị lưu trữ này và giá của chúng trong hai năm (2018 và 2020).
Ví dụ: giá thiết bị lưu trữ cao hơn vào năm 2018 và giá giảm vào năm 2020. Tỷ lệ giảm giá của năm 2020 so với năm 2018 là bao nhiêu? Bạn có thể sử dụng công thức này để tìm hiểu:
= (B2-C2) / B2Ở đây, chúng tôi trừ giá năm 2018 (B2) với giá năm 2020 (C2) và chia tổng cho giá năm 2018. Sau đó, chúng tôi sao chép công thức xuống các ô khác để tìm phần trăm giảm cho các thiết bị khác.
Nếu bạn nhận được kết quả dương tính ($ 20,00%), tỷ lệ phần trăm sẽ giảm giữa hai năm và nếu bạn nhận được kết quả âm tính (ví dụ - $ 13,33%), thì tỷ lệ phần trăm thực sự tăng lên chứ không phải giảm.
Các lỗi thường gặp bạn mắc phải khi sử dụng các công thức trên
Khi sử dụng các công thức trên, đôi khi bạn có thể gặp phải danh sách các lỗi phổ biến sau:
- # DIV / 0!: Lỗi này xảy ra khi bạn cố gắng chia một số cho không (0) hoặc cho một ô trống. Ví dụ. Giá trị B6 trong công thức ‘= (B6-C6) / B6’ bằng 0, do đó giá trị # DIV / 0! lỗi.
Khi bạn nhập ‘không’ ở định dạng tiền tệ, nó sẽ được thay thế bằng dấu gạch ngang (-) như hình trên.
- #GIÁ TRỊ: Excel sẽ đưa ra lỗi này khi bạn đã nhập một giá trị không phải là loại được hỗ trợ hoặc khi các ô bị bỏ trống. Điều này thường có thể xảy ra khi giá trị đầu vào chứa khoảng trắng, ký tự hoặc văn bản thay vì số.
- NUM!: Lỗi này xảy ra khi công thức chứa một số không hợp lệ dẫn đến một số quá lớn hoặc quá nhỏ để hiển thị trong Excel.
Chuyển một lỗi thành 0
Nhưng có một cách chúng ta có thể loại bỏ những lỗi này và hiển thị ‘0%’ ở vị trí của nó khi lỗi xảy ra. Để làm điều đó, bạn cần sử dụng hàm ‘IFERROR’, hàm này trả về kết quả tùy chỉnh khi công thức tạo ra lỗi.
Cú pháp của hàm IFERROR:
= IFERROR (giá trị, giá trị_if_error)Ở đâu,
- giá trị là giá trị, tham chiếu hoặc công thức để kiểm tra.
- value_if_error là giá trị chúng ta muốn hiển thị nếu công thức trả về giá trị lỗi.
Hãy áp dụng công thức này trong một trong các ví dụ lỗi (lỗi # DIV / 0!):
= IFERROR ((B6-C6) / B6,0%)Trong công thức này, đối số ‘value’ là công thức thay đổi phần trăm và đối số ‘value_if_error’ là ‘0%’. Khi công thức thay đổi phần trăm gặp lỗi (lỗi # DIV / 0!), Hàm IFERROR sẽ hiển thị ‘0%’ như được hiển thị bên dưới.
Đó là mọi thứ bạn cần biết về cách tính phần trăm thay đổi trong Excel.