Biểu đồ Excel làm tròn số

Tôi thường xuyên thấy chú thích cuối trang trên biểu đồ hoặc bảng Excel trong bản trình bày có nội dung “các giá trị không cộng thành 100% do làm tròn. ” Đôi khi các giá trị không cộng thành 100% và không có chú thích cuối trang, có vẻ như đó là một lỗi toán học. Cả hai tình huống này đều gây nghi ngờ trong tâm trí người xem. Nếu chúng ta không thể lấy các số để cộng tới 100% thì chúng ta đã mắc lỗi toán học nào khác?

Trong video này, tôi giải thích lý do điều này xảy ra khi chúng ta tạo biểu đồ hình tròn, lý do điều này không bao giờ xảy ra khi chúng ta để Excel tạo nhãn dữ liệu tỷ lệ phần trăm và bốn giải pháp cho vấn đề (bao gồm một giải pháp tận dụng tính năng của Excel để chèn dữ liệu từ ảnh)

Dave Paradi

Dave Paradi có hơn 22 năm kinh nghiệm tổ chức các hội thảo đào tạo tùy chỉnh để giúp các chuyên gia kinh doanh cải thiện bài thuyết trình của họ. Ông đã viết 10 cuốn sách và hơn 600 bài báo về chủ đề thuyết trình hiệu quả và những ý tưởng của ông đã xuất hiện trên các ấn phẩm trên khắp thế giới. Trọng tâm của anh ấy là giúp các chuyên gia của công ty truyền đạt một cách trực quan các thông điệp trong dữ liệu của họ để chúng không gây choáng ngợp và gây nhầm lẫn cho các giám đốc điều hành. Dave là một trong số ít hơn mười người ở Bắc Mỹ được Microsoft công nhận với Giải thưởng Chuyên gia có giá trị nhất vì những đóng góp của anh ấy cho cộng đồng Excel, PowerPoint và Teams. Các bài báo và video của anh ấy về thuyết trình ảo đã được xem hơn 3. 5 triệu lần và hơn 14.000 lượt thích trên YouTube

Cách chọn hàm trong Excel làm tròn số, làm tròn lên hoặc xuống. Hoặc định dạng một số để làm tròn nhưng giữ nguyên giá trị ban đầu trong ô. Khắc phục sự khác biệt nhỏ trong tổng cột

Làm tròn trong Excel

Để xem tổng quan nhanh về 7 cách làm tròn trong Excel, bạn có thể xem slide trình chiếu ngắn này. Có chi tiết và hướng dẫn từng bước, bên dưới slide show

Ngoài ra, hãy lấy , để bạn có thể theo dõi trình chiếu. Tệp nén có định dạng xlsx và không chứa bất kỳ macro nào

Làm tròn có định dạng

Với định dạng, một số có thể được làm tròn trên trang tính, nhưng giá trị ô không bị ảnh hưởng. Trong ảnh chụp màn hình bên dưới, tổng có vẻ không chính xác vì 2+3 không bằng 4

Tuy nhiên, nếu bạn bấm vào bất kỳ ô nào, bạn có thể thấy giá trị thực của nó trong thanh công thức. Ví dụ: giá cà phê ở ô B3 là $1. 54

Các số trong ô B3. B5 được định dạng là tiền tệ, sử dụng trình đơn thả xuống Định dạng số trên tab Trang chủ của Dải băng

Sau đó, nút Giảm số thập phân được bấm hai lần để đặt số thập phân thành 0

Nếu định dạng Số tiền tệ được áp dụng lại, nó sẽ hiển thị định dạng mặc định là 2 số thập phân, vì vậy tổng số tiền sẽ chính xác -- 1. 54 + 2. 54 =4. 08

Khi các số được làm tròn theo định dạng, mỗi số tiền được hiển thị dưới dạng số tiền gần nhất

• giá cà phê được làm tròn lên tới 2 đô la
• bánh sandwich được làm tròn lên đến $3
• tổng số tiền được làm tròn xuống còn $4

Tự động làm tròn số dài

Nếu bạn để định dạng số mặc định -- Chung -- và nhập các số dài trên trang tính, Excel có thể tự động làm tròn một số số đó

Để biết cách Excel có thể tự động làm tròn số với định dạng General, hãy xem video ngắn này và chi tiết bên dưới video

Tối đa 11 ký tự

Trong các thử nghiệm của tôi, tối đa 11 ký tự (bao gồm cả số thập phân) được hiển thị trong một ô, ngay cả khi cột đủ rộng để hiển thị nhiều số hơn

• Nếu một số dài hơn 11 ký tự, số thập phân sẽ được làm tròn để hiển thị tối đa 11 ký tự
• Nếu một số dài hơn 11 ký tự và không có số thập phân, số đó sẽ được đổi thành định dạng Khoa học

Để ngăn làm tròn/định dạng tự động này, hãy áp dụng một trong các định dạng số khác, chẳng hạn như Số, và đặt số thập phân mà bạn muốn hiển thị

Cột quá hẹp

Nếu bạn giảm chiều rộng cột, với Định dạng chung được áp dụng, các số sẽ tự động được làm tròn để cố gắng khớp

• Số thập phân được làm tròn để chỉ hiển thị số vừa với cột
• Số sẽ được làm tròn thành số nguyên, nếu không có chỗ cho số thập phân
• Số sẽ thay đổi thành định dạng Khoa học, nếu số nguyên được làm tròn sẽ không vừa
• Ô sẽ hiển thị các ký hiệu số, nếu nó không thể hiển thị ở định dạng Khoa học

Làm tròn với định dạng số tùy chỉnh

Trong một báo cáo tóm tắt, với những con số rất lớn, có lẽ không cần chính xác đến hàng thập phân. Rất khó để có được một cái nhìn tổng quan nhanh, các con số quá chi tiết, như hình dưới đây

Để làm tròn các số lớn và giúp chúng dễ đọc hơn, bạn có thể sử dụng Định dạng số tùy chỉnh để làm tròn sang bên trái của dấu thập phân

Ví dụ: hiển thị 123456. 78 ở một trong các định dạng sau

Áp dụng định dạng tùy chỉnh

Để áp dụng định dạng tùy chỉnh cho các ô

Trên tab Trang chủ của Dải băng, bấm vào công cụ khởi động hộp thoại, ở dưới cùng bên phải của nhóm Số

Trên tab Số, trong danh sách Danh mục, bấm vào Tùy chỉnh

Sau đó, chọn một trong các định dạng tùy chỉnh hiện có hoặc xóa hộp để tạo định dạng của riêng bạn

Ví dụ: nhập định dạng tùy chỉnh -- #, -- để chỉ hiển thị hàng nghìn được làm tròn trong một số lớn

Sau đó, nhấp vào nút OK, để đóng hộp thoại và áp dụng định dạng

Dưới đây là một vài định dạng số tùy chỉnh khác mà bạn có thể sử dụng

Bạn cũng có thể sử dụng định dạng số tùy chỉnh để sắp xếp các số có vị trí thập phân khác nhau. Dấu chấm hỏi (?) là một trình giữ chỗ có thêm khoảng trắng để các số thẳng hàng ở dấu thập phân

Có nhiều ví dụ hơn về các định dạng Tùy chỉnh trên trang web của Microsoft. Tạo định dạng số tùy chỉnh

Hàm VÒNG

Hàm ROUND có thể được sử dụng để làm tròn một số đến một số vị trí cụ thể từ dấu thập phân. Ví dụ

1. Làm tròn số tiền bán hàng đến xu gần nhất
2. Làm tròn tuổi đến thập kỷ gần nhất
3. Tổng số tiền có chênh lệch nhỏ

Ví dụ về thuế bán hàng

Trong ảnh chụp màn hình bên dưới, thuế bán hàng (HST) được làm tròn đến xu gần nhất, bằng cách sử dụng 2 làm số vị trí thập phân để làm tròn

=ROUND(B5*D6,2)

Tuổi theo thập kỷ Ví dụ

Trong ảnh chụp màn hình bên dưới, tuổi được làm tròn đến thập kỷ gần nhất, bằng cách sử dụng -1 làm số vị trí thập phân để làm tròn. Vì số âm nên nó làm tròn về bên trái chữ số thập phân

=ROUND(B4,-1)

Tổng số tiền có chênh lệch nhỏ

Trong bảng tính hiển thị bên dưới, có một sự khác biệt nhỏ giữa tổng của cột Tổng cộng và cột Đã nhận

• Trong cột C, thuế được tính bằng cách nhân giá bán với TaxRate (ô F1)
• Khi nhận được thanh toán, số tiền đó được nhập thủ công vào cột E
  • Số tiền đã nhập luôn được kiểm tra để đảm bảo khớp với Tổng số tiền đến hạn

Tại sao tổng số lại khác nhau, khi số tiền được nhập cẩn thận và được kiểm tra độ chính xác?

• Trong cột F, một công thức tìm thấy sự khác biệt giữa Tổng số tiền và Số tiền đã nhận
• Mặc dù chúng trông giống nhau trên trang tính nhưng có một sự khác biệt nhỏ ở hầu hết các hàng
• Điều này là do các vị trí thập phân bị ẩn trong cột Thuế

Khắc phục sự khác biệt về SUM với chức năng ROUND

Để khắc phục bạn sử dụng hàm ROUND trong cột Tính thuế

• =ROUND([@Chi phí]*Mức thuế,2)

Với thuế được làm tròn đến 2 chữ số thập phân, nó khớp chính xác với số tiền được nhập vào cột Đã nhận

• Trong mỗi hàng, phép tính Chênh lệch hiển thị kết quả bằng 0
• Trong hàng Tổng, tổng ở hai cột hoàn toàn giống nhau

Hàm ROUNDDOWN

Excel ROUNDDOWN làm tròn một số xuống, đến một số vị trí cụ thể từ dấu thập phân. Ví dụ

• Tròn một năm tính đến thập kỷ gần nhất

Trong ảnh chụp màn hình bên dưới, năm xuất bản được làm tròn xuống thập kỷ gần nhất, bằng cách sử dụng -1 làm số chữ số trong hàm ROUNDDOWN. Vì số âm nên nó làm tròn về bên trái chữ số thập phân

=ROUNDDOWN(B4,-1)

Hàm ROUNDUP

Hàm Excel ROUNDUP làm tròn một số lên một số vị trí cụ thể từ dấu thập phân. Ví dụ

• Làm tròn giá đến đồng xu gần nhất

Trong ảnh chụp màn hình bên dưới, giá được làm tròn đến đồng xu gần nhất, bằng cách sử dụng 1 làm số chữ số trong hàm ROUNDUP

=ROUNDUP(B4,1)

Chức năng MROUND

Hàm MROUND làm tròn một số đến bội số được chỉ định gần nhất. Ví dụ

• Làm tròn số tiền bán hàng đến niken gần nhất

Trong ảnh chụp màn hình bên dưới, tổng số tiền bán hàng được làm tròn đến niken gần nhất, bằng cách sử dụng 5 làm bội số trong hàm MROUND. Thay vì mã hóa cứng bội số vào công thức, nó được nhập vào ô B9, nơi có thể thay đổi dễ dàng

=MROUND($B$7,$B$9)

SÀN chức năng

Hàm Excel FLOOR làm tròn các số xuống, về 0, dựa trên bội số của ý nghĩa mà bạn chỉ định

FLOOR(số, ý nghĩa)

Ví dụ

• Tròn một năm tính đến thập kỷ gần nhất

Trong ảnh chụp màn hình bên dưới, năm được làm tròn xuống thập kỷ gần nhất, bằng cách sử dụng 10 làm ý nghĩa trong hàm FLOOR

=SÀN(B4,10)

GHI CHÚ. Trong hàm FLOOR, nếu số là dương và ý nghĩa là âm, kết quả sẽ là #NUM. lỗi. Hàm FLOOR hoạt động tốt trong ví dụ âm nhạc ở trên, vì năm của bài hát luôn là một số dương

TẦNG LẦU. CHÍNH XÁC hoặc SÀN. Hàm MATH

Đối với Excel 2010, Microsoft khuyên bạn nên sử dụng FLOOR. Chức năng PRECISE, thay vì FLOOR

Đối với Excel 2013, hãy sử dụng FLOOR. TOÁN, thay vì SÀN

Cả SÀN. CHÍNH XÁC và SÀN. MATH trả về một số được làm tròn xuống số nguyên gần nhất hoặc bội số gần nhất. Không phụ thuộc vào dấu của số, số được làm tròn xuống

TẦNG LẦU. CHÍNH XÁC (số lượng, ý nghĩa)

hoặc là

TẦNG LẦU. TOÁN(số, ý nghĩa)

Ba chức năng được so sánh trong bảng sau. Kết quả từ FLOOR. CHÍNH XÁC và SÀN. MATH giống nhau, nhưng FLOOR thì khác -- nó làm tròn về 0 và trả về lỗi nếu số dương và ý nghĩa là âm

CHỨC NĂNG TRẦN

Hàm CEILING trong Excel làm tròn các số lên, về 0, dựa trên bội số của ý nghĩa mà bạn chỉ định. Ví dụ

• Làm tròn giá đến phần tư gần nhất

Trong ảnh chụp màn hình bên dưới, giá được làm tròn đến phần tư gần nhất, bằng cách sử dụng 0. 25 là bội số trong hàm TRẦN

=TRẦN(B4,0. 25)

Tổng các số được làm tròn

Nếu một cột có số thập phân, bạn có thể sử dụng hàm ROUND để làm tròn từng số riêng lẻ, TRƯỚC KHI tính tổng. Điều này có thể cho kết quả khác với làm tròn số SAU KHI tính tổng.

Trong ví dụ này, có các số thập phân trong ô A1. A5

Trong ô A6, công thức làm tròn tổng số đến 0 chữ số thập phân, SAU KHI tính tổng. Nó trả về kết quả là 21. 0