Khi chia 75 chai nước vào thùng 12 chai, bạn cần biết đóng được mấy thùng và còn lẻ mấy chai. Hàm MOD trong Excel trả về phần dư của phép chia, tức là số chai còn lẻ. Bài viết này hướng dẫn bạn dùng hàm MOD từ cú pháp, kết quả với số âm cho tới các việc văn phòng hay gặp.

Bạn gõ =MOD(số bị chia, số chia) để lấy số dư. Ví dụ, =MOD(22,5) trả về 2, vì 22 chia 5 được 4 và dư 2. Với 75 chai và thùng 12 chai, =INT(75/12) trả về 6 thùng, còn =MOD(75,12) trả về 3 chai lẻ.

Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các ô màu vàng nhạt là nơi bạn gõ công thức. Mỗi bảng ví dụ nằm ở một sheet riêng và bắt đầu từ ô A1, nên bạn tạo sheet mới cho mỗi bảng. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy, ví dụ =MOD(A2;B2).

Hàm MOD trong Excel dùng để làm gì?

Hàm MOD trả về số dư sau khi chia một số cho một số khác. Tên MOD lấy từ chữ modulo, là phép chia lấy dư trong toán học. Hàm này có trên mọi phiên bản Excel, kể cả Excel 2010, 2016, 2019, 2021 và Microsoft 365.

Bản thân số dư ít khi là kết quả cuối cùng. Người đi làm thường dùng hàm MOD cho các việc sau:

  • Bạn quy đổi số lượng lẻ ra thùng và chiếc, kết hợp với hàm INT.

  • Bạn đổi tổng số phút ra giờ và phút, hoặc đổi số ngày ra tuần và ngày.

  • Bạn tính số giờ làm của ca qua đêm, khi giờ ra nhỏ hơn giờ vào.

  • Bạn tô màu dòng xen kẽ cho bảng dài bằng định dạng có điều kiện.

  • Bạn kiểm tra một số là số chẵn hay số lẻ.

Hàm MOD có cú pháp thế nào?

=MOD(number, divisor)

Tham số

Ý nghĩa

number

Tham số này là số bị chia, tức là số bạn muốn lấy phần dư.

divisor

Tham số này là số chia. Nếu số chia bằng 0, hàm báo lỗi #DIV/0!.

Cả 2 tham số đều bắt buộc. Theo trang hỗ trợ của Microsoft, hàm MOD tính theo công thức MOD(n, d) = n - d*INT(n/d). Hàm INT trong công thức này lấy phần nguyên của thương. Ví dụ, =MOD(22,5) bằng 22 - 5 x INT(4.4) = 22 - 5 x 4 = 2.

Bạn lấy số dư của từng phép chia bằng MOD thế nào?

Sheet Số dư có số bị chia ở cột A và số chia ở cột B. Bạn chọn ô C2, gõ công thức sau rồi nhấn Enter:

=MOD(A2,B2)

Bạn đang gõ vào ô C2 công thức =MOD(A2,B2)
Công thức lấy số ở ô A2 chia cho số ở ô B2 rồi trả về phần dư

Sau đó, bạn đưa chuột tới chấm vuông ở góc dưới bên phải ô C2. Khi con trỏ thành dấu cộng đen mảnh, bạn kéo xuống ô C7.

Cột C có kết quả 2, 3, 0, 1.5, 0 và lỗi #DIV/0! ở ô C7
Ô C4 bằng 0 vì 36 chia hết cho 12, còn ô C7 báo lỗi vì số chia bằng 0

Bảng kết quả cho thấy 4 điểm cần nhớ:

  • Phép chia hết, ví dụ 36 chia 12, cho số dư bằng 0.

  • Số thập phân vẫn dùng được. Ô C5 bằng 1.5, vì 7.5 chia 2 được 3 và dư 1.5.

  • Số bị chia bằng 0 cho số dư bằng 0.

  • Số chia bằng 0 làm hàm báo lỗi #DIV/0!. Ô số chia để trống cũng báo lỗi này, vì Excel coi ô trống là số 0.

Hàm MOD trả về kết quả gì với số âm?

Kết quả của hàm MOD luôn cùng dấu với số chia, theo trang hỗ trợ của Microsoft. Dấu của số bị chia không quyết định dấu của kết quả. Bạn gõ số âm trực tiếp vào hàm, ví dụ =MOD(-7,3), mà không cần thêm dấu ngoặc.

Sheet Số âm có 4 phép chia của 7 và 3 với các dấu khác nhau. Bạn chọn ô C2, gõ công thức sau rồi nhấn Enter:

=MOD(A2,B2)

Bạn đang gõ vào ô C2 của sheet Số âm công thức =MOD(A2,B2)
Bốn dòng có cùng con số 7 và 3, chỉ khác dấu

Sau đó, bạn kéo công thức từ ô C2 xuống ô C5.

Cột C có kết quả 1, 2, -2, -1
Ô C3 bằng 2 chứ không bằng -1, dù số bị chia là -7

Kết quả 2 ở ô C3 làm nhiều người bất ngờ. Bạn hiểu kết quả này qua hàm INT. Bạn chọn ô D2, gõ công thức sau để lấy phần nguyên của thương, rồi nhấn Enter:

=INT(A2/B2)

Bạn đang gõ vào ô D2 công thức =INT(A2/B2)
Hàm INT làm tròn thương xuống số nguyên nhỏ hơn gần nhất

Tiếp theo, bạn gõ =A2-B2*D2 vào ô E2 rồi nhấn Enter. Công thức ở ô E2 tính lại số dư theo cách Microsoft mô tả. Sau đó, bạn quét chọn D2:E2 và kéo xuống dòng 5.

Cột D có 2, -3, -3, 2 và cột E có 1, 2, -2, -1 giống hệt cột C
Cột E tính tay cho ra đúng các số ở cột C

Ở dòng 3, -7 chia 3 bằng -2.33. Hàm INT làm tròn xuống nên trả về -3, không trả về -2. Số dư là -7 - 3 x (-3) = -7 + 9 = 2. Dòng 4 cũng tính tương tự: 7 - (-3) x (-3) = 7 - 9 = -2.

Bài hàm TRUNC và INT trong Excel khác nhau ở số âm giải thích kỹ hơn vì sao INT làm tròn -2.33 thành -3.

Hàm INT và hàm MOD khác nhau thế nào?

Hàm INT và hàm MOD trả về 2 phần khác nhau của cùng một phép chia:

  • Hàm INT(a/b) trả về phần nguyên của thương, tức là chia được bao nhiêu lần.

  • Hàm MOD(a,b) trả về phần dư, tức là còn lại bao nhiêu sau khi chia.

Hai phần này ghép lại đúng bằng số ban đầu: a = b x INT(a/b) + MOD(a,b). Ví dụ, 17 chia 5 được 3 lần và dư 2, vì 5 x 3 + 2 = 17.

Excel còn có hàm QUOTIENT(a,b) cũng trả về phần nguyên. Với số dương, hàm QUOTIENT và hàm INT cho cùng kết quả. Với số âm, hai hàm cho kết quả khác nhau. Sheet INT và QUOTIENT có 17 chia 5 và -17 chia 5. Bạn gõ =INT(A2/B2) vào ô C2, =QUOTIENT(A2,B2) vào ô D2, =MOD(A2,B2) vào ô E2, rồi kéo cả 3 ô xuống dòng 3.

Dòng 2 có INT 3, QUOTIENT 3, MOD 2; dòng 3 có INT -4, QUOTIENT -3, MOD 3
Với -17, hàm INT trả về -4 còn hàm QUOTIENT trả về -3

Với -17 chia 5, cách ghép INT và MOD cho đúng số ban đầu: 5 x (-4) + 3 = -17. Cách ghép QUOTIENT và MOD cho 5 x (-3) + 3 = -12, không khớp. Vì vậy, khi dữ liệu có số âm, bạn ghép hàm MOD với hàm INT. Khi dữ liệu chỉ có số dương, bạn dùng INT hay QUOTIENT đều được.

Excel có hàm DIV như trong lập trình không?

Trong lập trình, DIV là phép chia lấy phần nguyên và MOD là phép chia lấy phần dư. Excel không có hàm DIV. Nếu bạn gõ =DIV(7,3), ô báo lỗi #NAME?. Bạn dùng =INT(7/3) hoặc =QUOTIENT(7,3) để lấy phần nguyên là 2, và dùng =MOD(7,3) để lấy phần dư là 1.

Bạn quy đổi số lượng lẻ ra thùng và chiếc bằng INT và MOD thế nào?

Thủ kho cần ghi phiếu xuất theo dạng "6 thùng 3 chiếc" thay vì "75 chiếc". Sheet Quy đổi thùng có số lượng ở cột C và số chiếc mỗi thùng ở cột D.

Bạn chọn ô E2, gõ công thức sau rồi nhấn Enter:

=INT(C2/D2)

Bạn đang gõ vào ô E2 công thức =INT(C2/D2)
Hàm INT bỏ phần lẻ của 75/12 = 6.25 để ra 6 thùng đầy

Tiếp theo, bạn chọn ô F2, gõ công thức sau rồi nhấn Enter:

=MOD(C2,D2)

Bạn đang gõ vào ô F2 công thức =MOD(C2,D2), ô E2 đã có 6
Hàm MOD lấy số chiếc còn lại sau khi đóng đủ 6 thùng

Sau đó, bạn quét chọn E2:F2 và kéo xuống dòng 6.

Cột E có 6, 5, 0, 8, 11 và cột F có 3, 0, 18, 4, 3
Mì gói chỉ có 18 chiếc, chưa đủ 1 thùng 30 chiếc, nên ra 0 thùng và 18 chiếc lẻ

Bạn kiểm tra lại bằng phép nhân: 6 thùng x 12 + 3 chiếc = 75 chiếc. Dòng sữa hộp có 240 chiếc chia hết cho 48, nên ra đúng 5 thùng và 0 chiếc lẻ.

Để ghi gọn vào phiếu, bạn chọn ô G2, gõ công thức sau rồi nhấn Enter:

=E2&" thùng "&F2&" chiếc"

Sau đó, bạn kéo công thức từ ô G2 xuống ô G6.

Cột G có 6 thùng 3 chiếc, 5 thùng 0 chiếc, 0 thùng 18 chiếc, 8 thùng 4 chiếc, 11 thùng 3 chiếc
Dấu & nối số thùng, số chiếc với chữ "thùng" và "chiếc"

Bạn đổi tổng phút ra giờ và phút bằng MOD thế nào?

Bảng tăng ca thường ghi tổng số phút, ví dụ 135 phút. Một giờ có 60 phút, nên bạn chia cho 60. Phần nguyên là số giờ, phần dư là số phút.

Sheet Đổi phút có tổng phút tăng ca ở cột B. Bạn gõ =INT(B2/60) vào ô C2 rồi nhấn Enter để lấy số giờ. Tiếp theo, bạn chọn ô D2, gõ công thức sau rồi nhấn Enter:

=MOD(B2,60)

Bạn đang gõ vào ô D2 công thức =MOD(B2,60), ô C2 đã có 2
135 phút chia 60 được 2 giờ và dư 15 phút

Sau đó, bạn gõ =C2&" giờ "&D2&" phút" vào ô E2, rồi quét chọn C2:E2 và kéo xuống dòng 6.

Cột E có 2 giờ 15 phút, 1 giờ 30 phút, 0 giờ 45 phút, 8 giờ 0 phút, 10 giờ 15 phút
Mỗi dòng tách đúng tổng phút thành giờ và phút

Bạn dùng cùng cách này cho các đơn vị khác:

  • Bạn đổi số ngày ra tuần và ngày bằng =INT(B2/7) và =MOD(B2,7). Ví dụ, 45 ngày là 6 tuần và 3 ngày.

  • Bạn đổi số tháng ra năm và tháng bằng =INT(B2/12) và =MOD(B2,12). Ví dụ, 30 tháng là 2 năm và 6 tháng.

Nếu bạn chỉ cần hiển thị dạng 2:15, bạn có thể gõ =TEXT(B2/1440,"[h]:mm"). Một ngày có 1440 phút, nên B2/1440 đổi số phút thành giá trị thời gian của Excel.

Bạn tính giờ làm ca qua đêm bằng MOD(giờ ra - giờ vào, 1) thế nào?

Ca làm bắt đầu lúc 22:00 và kết thúc lúc 06:00 sáng hôm sau. Nếu bạn lấy giờ ra trừ giờ vào, Excel không ra 8 giờ.

Bạn thấy lỗi gì khi trừ thẳng giờ ra cho giờ vào?

Sheet Ca đêm có giờ vào ở cột B và giờ ra ở cột C. Bạn chọn ô D2, gõ công thức sau rồi nhấn Enter:

=C2-B2

Bạn đang gõ vào ô D2 công thức =C2-B2
Phép trừ thẳng chỉ đúng khi giờ ra lớn hơn giờ vào

Sau đó, bạn kéo công thức từ ô D2 xuống ô D6.

Ô D3, D4, D6 hiện toàn dấu #, ô D2 và D5 hiện 09:00 và 08:00
Ba ca qua đêm cho kết quả âm nên Excel hiện dấu #

Excel lưu thời gian dưới dạng một phần của ngày. Ví dụ, 06:00 là 0.25 ngày và 22:00 là khoảng 0.9167 ngày. Phép trừ 0.25 - 0.9167 cho số âm, và Excel không hiển thị được giờ âm nên hiện dấu #. Bạn nới rộng cột cũng không hết dấu #.

Bạn sửa bằng hàm MOD như thế nào?

Bạn chọn lại ô D2, gõ công thức sau rồi nhấn Enter:

=MOD(C2-B2,1)

Bạn đang gõ vào ô D2 công thức =MOD(C2-B2,1)
Số 1 trong công thức là 1 ngày, tức là 24 giờ

Sau đó, bạn kéo công thức từ ô D2 xuống ô D6.

Cột D có 09:00, 08:00, 07:45, 08:00, 08:30
Ca 22:00 tới 06:00 ra đúng 8 giờ

Hàm MOD với số chia là 1 cộng thêm 1 ngày khi kết quả âm, và giữ nguyên khi kết quả dương. Ở dòng 3, -0.6667 + 1 = 0.3333 ngày, tức là 8 giờ. Ở dòng 2, ca ngày từ 08:00 tới 17:00 vẫn ra 9 giờ như phép trừ thường. Nhờ vậy, bạn dùng một công thức cho cả ca ngày và ca đêm.

Bảng công thường cần số giờ dạng số thập phân để nhân với đơn giá. Bạn gõ =D2*24 vào ô E2, rồi kéo xuống ô E6. Nếu muốn số hiện 2 chữ số thập phân như ảnh, bạn chọn E2:E6, nhấn Ctrl + 1 và chọn định dạng Number với 2 chữ số thập phân.

Cột E có 9.00, 8.00, 7.75, 8.00, 8.50
Ca 18:30 tới 02:15 là 7 giờ 45 phút, tức là 7.75 giờ

Bạn lưu ý 2 điểm với công thức này:

  • Công thức chỉ đúng khi ca làm ngắn hơn 24 giờ. Nếu giờ ra trùng giờ vào, kết quả là 0 giờ chứ không phải 24 giờ.

  • Nếu ô giờ ra còn trống, MOD(0-B2,1) vẫn ra một số giờ. Ví dụ, giờ vào 22:00 và giờ ra trống cho kết quả 02:00. Bạn dùng =IF(C2="","",MOD(C2-B2,1)) để ô kết quả để trống khi chưa có giờ ra.

Bạn tô màu dòng xen kẽ bằng MOD(ROW(),2) thế nào?

Bảng dài khó dò theo dòng nếu mọi dòng cùng màu nền. Bạn tô màu các dòng chẵn bằng định dạng có điều kiện với công thức =MOD(ROW(),2)=0.

Hàm ROW() không có tham số trả về số thứ tự dòng của ô đang xét. Hàm MOD chia số dòng cho 2. Dòng chẵn cho số dư 0 nên điều kiện đúng và Excel tô màu. Dòng lẻ cho số dư 1 nên Excel không tô.

Sheet Đơn hàng có 10 đơn hàng từ dòng 2 tới dòng 11. Bạn làm theo các bước sau:

  1. Bạn quét chọn vùng A2:D11, không chọn dòng tiêu đề.

  2. Bạn bấm thẻ Home, bấm Conditional Formatting, rồi chọn New Rule.

Menu Conditional Formatting đang mở, con trỏ chỉ vào New Rule
Mục New Rule nằm gần cuối menu Conditional Formatting
  1. Trong hộp thoại New Formatting Rule, bạn chọn Use a formula to determine which cells to format.

  2. Bạn gõ =MOD(ROW(),2)=0 vào ô Format values where this formula is true, rồi bấm nút Format.

Hộp thoại New Formatting Rule có công thức =MOD(ROW(),2)=0, con trỏ chỉ vào nút Format
Công thức trả về TRUE ở các dòng 2, 4, 6, 8, 10
  1. Trong hộp thoại Format Cells, bạn mở thẻ Fill, chọn một màu nhạt, rồi bấm OK.

Thẻ Fill của hộp thoại Format Cells, đã chọn màu xanh nhạt, con trỏ chỉ vào nút OK
Màu nhạt giúp chữ trong ô vẫn dễ đọc
  1. Bạn bấm OK ở hộp thoại New Formatting Rule.

Bảng đơn hàng có dòng 2, 4, 6, 8, 10 tô màu xanh nhạt, các dòng lẻ để trắng
Các dòng chẵn được tô xen kẽ với các dòng lẻ

Khi bạn chèn thêm dòng vào giữa bảng, Excel tự giãn vùng áp dụng và màu vẫn xen kẽ đúng. Bạn đổi công thức thành =MOD(ROW(),2)=1 để tô dòng lẻ, hoặc =MOD(ROW(),3)=0 để tô mỗi dòng thứ 3.

Một số bài hướng dẫn dùng =MOD(ROW(A1),2)=0. Công thức này phụ thuộc ô đầu vùng bạn chọn. Khi vùng chọn bắt đầu từ dòng 2, ROW(A1) ở ô A2 trả về 1, nên Excel tô các dòng 3, 5, 7 thay vì các dòng chẵn. Hàm ROW() không có tham số tránh được nhầm lẫn này.

Bạn lưu ý: khi lọc dữ liệu, công thức vẫn tính theo số dòng thật. Các dòng bị ẩn làm 2 dòng cùng màu đứng cạnh nhau. Nếu bảng thường xuyên phải lọc, bạn nên đổi vùng dữ liệu thành Table bằng phím Ctrl + T, vì Table tự tô xen kẽ theo dòng đang hiện. Bài tô màu có điều kiện trong Excel hướng dẫn thêm các kiểu quy tắc khác.

Bạn kiểm tra số chẵn, số lẻ bằng MOD thế nào?

Một số chia 2 dư 0 là số chẵn, dư 1 là số lẻ. Nhân viên giao hàng thường chia tuyến theo dãy nhà số chẵn và dãy nhà số lẻ của một con đường.

Sheet Chẵn lẻ có số nhà ở cột B. Ô E2 ghi nhãn Số khách dãy lẻ, để dùng ở bước đếm phía sau. Bạn chọn ô C2, gõ công thức sau rồi nhấn Enter:

=IF(MOD(B2,2)=0,"Dãy chẵn","Dãy lẻ")

Bạn đang gõ vào ô C2 công thức =IF(MOD(B2,2)=0,"Dãy chẵn","Dãy lẻ")
Hàm IF xét số dư của phép chia cho 2

Sau đó, bạn kéo công thức từ ô C2 xuống ô C6.

Cột C có Dãy chẵn, Dãy lẻ, Dãy chẵn, Dãy lẻ, Dãy chẵn
Số nhà 37 và 5 thuộc dãy lẻ

Để đếm số khách ở dãy lẻ, bạn chọn ô F2, gõ công thức sau rồi nhấn Enter:

=SUMPRODUCT(MOD(B2:B6,2))

Ô F2 có công thức =SUMPRODUCT(MOD(B2:B6,2)) và kết quả 2
Có 2 khách ở dãy lẻ là số nhà 37 và số nhà 5

Hàm MOD trả về 1 cho mỗi số lẻ và 0 cho mỗi số chẵn. Hàm SUMPRODUCT cộng các số 1 lại nên ra số lượng số lẻ. Công thức này chỉ đúng khi cột B là số nguyên.

Excel còn có 2 hàm ISEVEN và ISODD để kiểm tra số chẵn, số lẻ. Công thức =IF(ISEVEN(B2),"Dãy chẵn","Dãy lẻ") cho cùng kết quả với dữ liệu trong bài.

Bạn cần cẩn thận với ô trống. Excel coi ô trống là số 0, nên MOD(B2,2) ra 0 và công thức ghi "Dãy chẵn" cho dòng chưa có số nhà. Bạn dùng =IF(B2="","",IF(MOD(B2,2)=0,"Dãy chẵn","Dãy lẻ")) để bỏ qua ô trống.

Bạn hay gặp lỗi gì khi dùng hàm MOD?

Lỗi

Nguyên nhân

Cách sửa

Ô báo #DIV/0!

Số chia bằng 0 hoặc ô số chia để trống.

Bạn kiểm tra lại ô số chia. Với cột có ô trống, bạn dùng =IF(B2="","",MOD(A2,B2)).

Ô báo #VALUE!

Ô số bị chia hoặc số chia có chữ, ví dụ số nhà "15A".

Bạn tách phần số ra khỏi chữ. Ô chứa số dạng chữ như "15" thì hàm MOD vẫn tính được.

Ô hiện toàn dấu # khi tính giờ làm

Bạn trừ thẳng giờ ra cho giờ vào ở ca qua đêm, nên kết quả là giờ âm.

Bạn dùng =MOD(C2-B2,1).

Ca chưa có giờ ra vẫn hiện số giờ

Ô giờ ra trống được coi là 0 giờ.

Bạn dùng =IF(C2="","",MOD(C2-B2,1)).

Kết quả hiện dạng 0.33 thay vì 08:00

Ô kết quả đang có định dạng số thường.

Bạn chọn ô, nhấn Ctrl + 1 và chọn định dạng Time, hoặc nhân với 24 để lấy số giờ.

Ô trống được xếp vào số chẵn

Excel coi ô trống là số 0, mà 0 chia 2 dư 0.

Bạn thêm điều kiện IF(B2="","",...).

=MOD(1.2,0.1) ra 0.1 thay vì 0

Excel lưu 1.2 và 0.1 dưới dạng số nhị phân gần đúng, nên số dư bị lệch.

Bạn đổi về số nguyên trước khi chia, ví dụ =MOD(1.2*10,1) trả về 0.

Kết quả mang dấu âm ngoài ý muốn

Số chia là số âm, mà kết quả luôn cùng dấu với số chia.

Bạn dùng số chia dương, ví dụ =MOD(A2,ABS(B2)).

Ô báo #NUM!

Số bị chia lớn gấp số chia quá nhiều lần, ví dụ =MOD(1E+14,7). Trên Excel 365, lỗi xuất hiện khi thương lớn hơn khoảng 2.000 tỷ. Mốc này là kết quả thử, Microsoft không công bố.

Bạn tính số dư bằng =A2-B2*INT(A2/B2), là công thức Microsoft dùng để mô tả hàm MOD. Ví dụ, =1E+14-7*INT(1E+14/7) trả về 2.

Bạn muốn học Excel bài bản hơn?

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.