=MOD(A2,B2) trả về số dư khi chia A2 cho B2: =MOD(17,5) bằng 2, vì 17 chia 5 được 3 lần và còn dư 2. =ABS(A2) trả về số đã bỏ dấu, nên -42 thành 42.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Number | Divisor | MOD | Times it fits |
| 2 | 17 | 5 | 2 | 3 |
| 3 | 20 | 5 | 0 | 4 |
| 4 | 7 | 2 | 1 | 3 |
| 5 | 100 | 7 | 2 | 14 |
| 6 | -3 | 2 | 1 | -1 |
| 7 | 3 | -2 | -1 | -1 |
Cột D cho thấy nửa còn lại của phép chia: QUOTIENT trả về số lần nguyên mà số chia nằm vừa trong số bị chia, nên 17 là 3 lần 5 cộng 2. Đổi B2 thành 0 thì MOD trả về #DIV/0!, giống như khi chia cho 0.
Cú pháp MOD
=MOD(number, divisor)
MOD cũng dùng được với số thập phân: =MOD(7.5,2) bằng 1,5, còn =MOD(A2,1) chỉ trả về phần thập phân của một số, ví dụ 0,75 từ 3,75. Với giá trị ngày có kèm giờ, =MOD(A2,1) giữ lại phần giờ và bỏ phần ngày.
MOD với số âm
Hàng 6 và 7 của trang tính trên cho thấy quy tắc của Excel: kết quả mang dấu của số chia. =MOD(-3,2) bằng 1 chứ không phải -1, còn =MOD(3,-2) bằng -1. Excel tính MOD theo công thức number - divisor * INT(number / divisor), và INT luôn làm tròn xuống.
Đây là chỗ bảng tính và code không giống nhau. Trong JavaScript, Java, C và C#, -3 % 2 bằng -1; trong Python, -3 % 2 bằng 1, giống Excel. Google Sheets làm theo Excel. Nếu bạn cần kết quả mang dấu của số bị chia, hãy dùng =A2-B2*TRUNC(A2/B2).
Chẵn hay lẻ, và tô màu xen kẽ hàng
Một số là số chẵn khi MOD(number,2) bằng 0. Từ đó bạn có nhãn "Even" hoặc "Odd", và khi kết hợp với ROW, bạn có quy tắc tô màu cách một hàng. Excel cũng có ISEVEN và ISODD, trả về TRUE hoặc FALSE trực tiếp.
| A | B | C | |
|---|---|---|---|
| 1 | Order | Qty | Even or odd |
| 2 | 1001 | 4 | Even |
| 3 | 1002 | 7 | Odd |
| 4 | 1003 | 10 | Even |
| 5 | 1004 | 3 | Odd |
| 6 | 1005 | 8 | Even |
| 7 | 1006 | 5 | Odd |
Quy tắc định dạng có điều kiện =MOD(ROW($A2),2)=0 tô sáng hàng 2, 4 và 6, tức các hàng có số thứ tự chẵn. Trong Excel, vào Home > Conditional Formatting > New Rule > "Use a formula to determine which cells to format", và dạng ngắn hơn =MOD(ROW(),2)=0 cũng dùng được ở đó. Đổi số 2 thành 3 để tô màu cứ ba hàng một lần.
Mỗi hàng thứ n: cộng mỗi giá trị thứ ba
MOD(ROW()-ROW(first),n)=0 cho TRUE ở hàng đầu tiên của vùng và sau đó cứ mỗi n hàng một lần. Đặt trong SUMPRODUCT, nó lấy ra mỗi giá trị thứ ba trong một cột, ví dụ tháng cuối của mỗi quý.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | Every 3rd | ||
| 2 | Jan | 120 | 310 | ||
| 3 | Feb | 135 | |||
| 4 | Mar | 150 | |||
| 5 | Apr | 110 | |||
| 6 | May | 125 | |||
| 7 | Jun | 160 |
Độ lệch của các hàng chạy từ 0 đến 5, và =2 giữ lại độ lệch 2 và 5: tháng 3 và tháng 6, tức 150 cộng 160. Đổi =2 thành =0 để lấy tháng 1 và tháng 4.
Đổi phút ra giờ và phút
MOD và phép chia lấy phần nguyên tách một tổng thành các đơn vị: giờ và số phút lẻ, tuần và số ngày lẻ, số thùng và số món lẻ.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Minutes | Hours | Minutes left | Label |
| 2 | 135 | 2 | 15 | 2 h 15 min |
| 3 | 59 | 0 | 59 | 0 h 59 min |
| 4 | 240 | 4 | 0 | 4 h 0 min |
| 5 | 1000 | 16 | 40 | 16 h 40 min |
1000 phút là 16 giờ 40 phút. Với giá trị thời gian thực sự (8:30, 17:45) và ca làm việc kéo qua nửa đêm, =MOD(end-start,1) là công thức chuẩn; xem tính toán thời gian.
ABS: độ chênh lệch giữa hai số
=ABS(number) bỏ dấu trừ. Công dụng chính của hàm là lấy độ lớn của một hiệu số khi bạn không quan tâm giá trị nào lớn hơn: mỗi dự báo lệch bao xa so với kết quả thực tế, hay một số đo có nằm trong dung sai hay không.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Week | Forecast | Actual | Off by | Within 10? |
| 2 | W1 | 120 | 112 | 8 | TRUE |
| 3 | W2 | 95 | 109 | 14 | FALSE |
| 4 | W3 | 140 | 138 | 2 | TRUE |
| 5 | W4 | 80 | 93 | 13 | FALSE |
| 6 | W5 | 110 | 104 | 6 | TRUE |
B2-C2 bằng 8 với W1 và -14 với W2; ABS biến cả hai thành khoảng cách. Để cộng các khoảng cách lại, =SUMPRODUCT(ABS(B2:B6-C2:C6)) chạy được trên mọi phiên bản Excel; ở đây kết quả là 43, tức tổng của cột D. Chia tổng đó cho số lượng giá trị sẽ ra sai số tuyệt đối trung bình.
Thử ngay: MOD và ABS
| A | B | C | |
|---|---|---|---|
| 1 | Eggs | Per carton | Left over |
| 2 | 350 | 12 |
Đến lượt bạn: Trong C2, tính xem còn dư bao nhiêu quả trứng sau khi xếp số trứng ở A2 vào các hộp đầy, mỗi hộp chứa số quả ghi ở B2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | City | Morning | Evening | Change |
| 2 | Oslo | 14 | 6 |
Đến lượt bạn: Trong D2, cho biết nhiệt độ đã thay đổi bao nhiêu độ giữa B2 và C2, dưới dạng số dương bất kể lần đo nào cao hơn.
MOD, QUOTIENT và INT khác nhau thế nào
| Bạn cần | Công thức | Với 17 và 5 | Với -17 và 5 |
|---|---|---|---|
| Số dư | =MOD(A2,B2) | 2 | 3 |
| Số lần chia nguyên, làm tròn về 0 | =QUOTIENT(A2,B2) | 3 | -3 |
| Số lần chia nguyên, làm tròn xuống | =INT(A2/B2) | 3 | -4 |
| Phép chia đầy đủ | =A2/B2 | 3,4 | -3,4 |
Với số dương, QUOTIENT và INT(A2/B2) cho cùng kết quả, và B2*INT(A2/B2)+MOD(A2,B2) luôn dựng lại được số ban đầu. Với số âm, chỉ cặp INT mới cộng lại khớp: -4 nhân 5 cộng 3 bằng -17. Dùng lẫn QUOTIENT với MOD trên số âm là nguyên nhân thường gặp của lỗi lệch một đơn vị khi lập lịch hay chia phần.
Câu hỏi thường gặp
Hàm MOD trong Excel dùng để làm gì?
Hàm trả về số dư của phép chia: =MOD(17,5) bằng 2, vì 17 chia 5 được 3 lần và còn dư 2. Nếu phép chia hết, MOD trả về 0.
Làm sao kiểm tra một số là số chẵn trong Excel?
Dùng =MOD(A2,2)=0, kết quả là TRUE với số chẵn, hoặc dùng =ISEVEN(A2). Để hiện nhãn: =IF(MOD(A2,2)=0,"Even","Odd").
Vì sao MOD trả về số dương với một giá trị âm?
Hàm MOD của Excel lấy dấu theo số chia: =MOD(-3,2) bằng 1 và =MOD(3,-2) bằng -1. Nhiều ngôn ngữ lập trình cho -3 % 2 bằng -1, nên kết quả có thể khác với code.
Làm sao lấy giá trị tuyệt đối trong Excel?
Dùng =ABS(A2): -42 thành 42 và 42 vẫn là 42. Để lấy độ lớn của một hiệu số mà không có dấu, dùng =ABS(B2-C2).
Làm sao tính tổng các giá trị tuyệt đối trong Excel?
Dùng =SUMPRODUCT(ABS(A2:A7)), công thức chạy được trên mọi phiên bản. Trong Excel 365 và 2021, =SUM(ABS(A2:A7)) cũng dùng được.
