Bài viết: Hàm MOD và một số ứng dụng

[BNTT]

Hàm MOD()

Dùng để lấy số dư của một phép chia

Cú pháp: = MOD(number, divisor)number: Số bị chia​

divisor: Số chia​

Ví dụ: MOD(24, 10) = 4 (24 chia 10 được 2, dư 4)​

Một số ví dụ ứng dụng của hàm MOD()

Cộng các ô cách nhau một số hàng cố định

Có một bài toán như sau:

Trong một cột dữ liệu, người ta muốn tính tổng của cách ô cách nhau mỗi n hàng nào đó.
Ví dụ, trong dãy A1:A20, tính tổng của các ô cách nhau 5 ô, nghĩa là lấy A1 + A6 + A11 + A16
Nói tới hàng, ta nghĩ đến hàm ROW(): ROW(A1) = 1, ROW(A6) = 6, v.v...

Và ở ví dụ vừa nêu trên đây, ta để ý các con số 1, 6, 11, 16 khi đem chia cho 5 đều dư 1, vậy ta nghĩ đến hàm MOD(), lấy số thứ tự của hàng chia cho 5: MOD(ROW(A1), 5) = 1, MOD(ROW(A6), 5) = 1, v.v...

Vậy ta sẽ lập công thức cho mảng A1:A20:
Xét trong khối A1:A20, ô nào có số thứ tự của hàng chia cho 5 mà dư 1, thì lấy các ô đó cộng lại:

{= SUM(IF(MOD(ROW(A1:A20), 5) = 1, A1:A20, 0))}​

Hay cụ thể hơn, gọi dãy ô để tính tổng là range, và số hàng cách nhau giữa các ô là n, ta sẽ có công thức:

{= SUM(IF(MOD(ROW(range), n) = 1, range, 0))}​

Con số 1 ở đây không phải là con số cố định, mà nó là số dư của phép chia của số thứ tự cho n. Do đó, tùy vào điều kiện của bài toán mà ta thay bằng con số thích hợp.
Chẳng hạn, cũng ví dụ trên đây, như ta muốn tính tổng của các ô A2, A7, A12, A17 thì công thức sẽ là:

{= SUM(IF(MOD(ROW(A1:A20), 5) = 2, A1:A20, 0))}​

(2 là số dư của 2, 7, 12, 17 cho 5)​

Cộng các ô có số thứ tự hàng là chẵn, hoặc có số thứ tự hàng là lẻ

Từ bài toán trên đây, ta có công thức để cộng các ô có số thứ tự hàng làm chẵn:

{= SUM(IF(MOD(ROW(Range), 2) = 0, Range, 0))}​

Và công thức để cộng các ô có số thứ tự hàng làm lẻ:​

{= SUM(IF(MOD(ROW(Range), 2) = 1, Range, 0))}​

Xem một năm có phải là năm nhuận hay không

Nếu bạn muốn có một công thức để xem thử một năm nào đó có phải là năm nhuận hay không, bạn có thể dùng hàm MOD().
Năm nhuận, là năm chia hết cho 4, hay phép dư của phép chia số năm cho 4 thì bẳng 0 ở công thức sau đây:= MOD(year, 4)
Số năm phải là một con số có 4 chữ số.

Công thức trên chỉ đúng trong khoảng từ năm 1901 đến 2099 (có lẽ chúng ta cũng sống tối đa trong khoảng thời gian này thôi). Công thức này không đúng với năm 1900 và 2100, bởi vì điều kiện để tính năm nhuận còn có chuyện: Năm nhuận là năm chia hết cho 4, nếu năm này tận cùng bằng hai con số 0 thì nó phải chia hết cho 400 (ví dụ, năm 2000).

Vậy, để có một công thức đúng cho tất cả mọi năm, ta làm một công thức luận lý như sau:= (MOD(year, 4) = 0) - (MOD(year, 100) = 0) + (MOD(year, 400) = 0)
Nếu công thức này cho đáp số là 1 (TRUE), thì đó là năm nhuận, còn nếu cho đáp số là 0 (FALSE), thì đó không phải là năm nhuận.

Ứng dụng của hàm MOD() trong định dạng bảng tính

Chắc cũng có một lúc nào đó, bạn thích định dạng cho bảng tính của mình theo kiểu Ledger Shading. Ledger Shading là kiểu định dạng các hàng có màu xen kẽ nhau (ví dụ, xanh và trắng). Loại định dạng này thích hợp cho những danh sách dài, có nhiều cột, giúp ta phân biệt được hàng này với hàng kia cách dễ dàng, ít khi bị nhìn lầm dữ liệu giữa các hàng...​

​

Hình sau đây là một ví dụ:​

​

Dĩ nhiên là việc này rất dễ thực hiện, chúng ta chỉ cần chọn các hàng xen kẽ nhau và tô màu cho nó. Tuy nhiên, nếu làm thủ công bằng tay, thì sẽ có một số bất tiện sau:​

- Rất lâu cho những bảng dữ liệu lớn.​

​

- Mỗi khi chèn thêm một hàng hoặc xóa bớt một hàng, thì phải định dạng lại.​

​

Để tránh những bất tiện đó, bạn có thể dùng một liên kết giữa hàm MOD() và chức năng Conditional Formatting (định dạng có điều kiện) của Excel.​

​

Cách làm như sau:​

1. Chọn vùng bạn muốn định dạng
2. Gọi hộp thoại Conditional Formatting, và chọn New Ruler để mở hộp thoại New Formatting Ruler
3. Chọn Use a Formula to Determine Which Cells to Format
4. Trong khung Format values where this formular is true, gõ công thức = MOD(ROW(), 2)
   
   

   ​
5. Nhấn nút Format... để mở hộp thoại Format Cells
6. Chọn tab Fills, chọn màu bạn thích tô cho các hàng xen kẽ với màu trắng, rồi nhấn OK để quay về hộp thoại New Formatting Ruler
7. Nhấn OK

Công thức = MOD(ROW(), 2) sẽ trả về 1 cho những hàng có số thứ tự lẻ và trả về 0 cho những hàng có số thứ tự chẵn. Bởi vì 1 thì tương đương với TRUE, nên Excel sẽ áp dụng màu tô mà bạn đã chọn cho tất cả những hàng có số thứ tự lẻ trong vùng bạn muốn định dạng, và bỏ qua những hàng có số thứ tự chẵn.​

​

Tương tự,​

• Nếu muốn định dạng các cột có màu xen kẽ, bạn chỉ cần thay công thức ở trên thành:
  = MOD(COLUMN(), 2)
• Nếu thích định dạng cho hàng có số thứ tự lẻ thì không tô màu, còn hàng có số thứ tự chẵn mới tô màu, bạn thay đổi công thức trên một tí:
  = MOD(ROW() + 1, 2)

----------------------- Xem thêm: Hàm ROW(), Hàm COLUMN()​

Ngoài ra bạn có thể xem thêm ứng dụng hàm MOD trong việc xóa dòng chẵn lẻ tại GPE tại đây.

Một số bài viết có liên quan:
1/ Offset truyền kỳ (kỳ 2)
2/ Offset truyền kỳ (kỳ 1)
3/ Kết hợp các chữ ở nhiều ô thành 1 dòng chỉ trong tíc tắc
4/ SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện
5/ Sử dụng Excel để ra quyết định hằng ngày
6/ Excel nâng cao: Sử dụng sự lặp lại và các tham chiếu tuần hoàn
7/ 10 lý do để học công thức Excel
8/ Làm việc với công thức mảng trong Excel
9/ VLOOKUP với Cột động
10/ Sử dụng hàm Subtotal