Cách dùng hàm MOD trong Excel kèm ví dụ minh họa chi tiết

Hàm MOD trong Excel là một trong những hàm tính toán cơ bản được sử dụng được trên các phiên bản Excel 2007, 2010, 2013, 2016, 2017, 2018, 2019 trở lên. Bên cạnh đó, trên phiên bản Excel cho máy tính bảng hay trên điện thoại iPhone hoặc điện thoại Android thì bạn vẫn có thể sử dụng hàm này. 

Vậy chi tiết về hàm MOD ra sao, cách sử dụng hàm này thế nào, Gitiho mời các bạn tham khảo chi tiết trong bài viết dưới đây.

Hàm MOD là hàm gì?

Trong các bảng tính số liệu trên bảng tính Excel, một số bài toán cần dùng đến số dư của phép chia để kết hợp với nhiều hàm khác để thực hiện tính toán. Để lấy được phần dư của phép chia trong Excel chúng ta sử dụng Hàm MOD hàm chia lấy phần dư trong Excel. 

Bài viết dưới đây sẽ giúp bạn hiểu rõ hơn về cú pháp cũng như cách sử dụng hàm MOD dùng để lấy số dư trong Excel thông qua các ví dụ cụ thể. 

Thành thạo hàm Mod và 150 hàm Excel khá nhờ Tuyệt đỉnh Excel

Công thức hàm MOD Excel

=MOD(number, divisor)

Trong cú pháp hàm MOD có các đối số sau đây:

Number: Số bị chia mà bạn muốn (số chia để tìm số dư).

Divisor: Số chia mà bạn muốn.

Ý nghĩa của hàm MOD: Trả về số dư sau khi số bị chia chia cho số chia. Kết quả hàm trả về sẽ cùng dấu với số chia (không quan tâm tới dấu số bị chia). 

Chú thích về hàm MOD Excel

• Nếu số bị chia (số chia để tìm số dư) bằng 0, thì hàm MOD trả về giá trị lỗi là #DIV/0.
• Ngoài hàm MOD có thể được sử dụng bằng các số hạng của Hàm INT:

MOD (n, d) = n-d * INT (n/d)

Ba điều bạn nên biết về MOD trong Excel

• Kết quả của hàm MOD có cùng dấu với ước số.
• Nếu số chia là 0, MOD trả về lỗi #DIV/0! vì không thể chia cho số không.
• Nếu số hoặc số chia là giá trị văn bản, công thức MOD của Excel sẽ trả về lỗi #VALUE!.

Xem thêm: 3 cách sửa lỗi VALUE trong Excel nhanh

Ứng dụng hàm MOD trong Excel qua các bài toán cụ thể

Hàm MOD dùng để lấy phần dư trong Excel

Ví dụ 1: Tìm số dư của phép chia trong bảng dưới đây:

Hướng dẫn: Tại ô J6, nhập công thức = MOD(H8,I8). Trong đó H8 là số bị chia  (số chia để tìm số dư), I8 là số chia.

Sau đó nhấn Enter, chúng ta có kết quả:

Sao chép công thức xuống các ô còn lại được kết quả

Như vậy, ở đây thì kết quả số dư mang dấu của số chia.

• Chia 19 cho -17=> ta được số dư là -15, mang dấu của số chia là trừ
• Chia -21 cho 13=> ta được số dư là 5, mang dấu của số chia là cộng
• Chia -25 cho -6=> ta được số dư là -1, mang dấu của số chia là trừ

Đối với trường hợp Divisor=0 thì trả về giá trị lỗi #DIV/0!

Ứng dụng hàm MOD dùng để đánh dấu các hàng

Ví dụ 2: Hãy đánh dấu các hàng thứ hai trong bảng sau:

Để thực hiện đánh dấu, chúng ta làm theo các bước sau:

Bước 1: Vào Conditional Formating, chọn New Rule

Bước 2: Khi bảng New Formatting Rule mở ra, chọn Use a formula to determine which cells to format

Bước 3: Nhập công thức =MOD(ROW(A1),2)=0 vào ô Edit the rule như hình dưới.

Bước 4 : Vào ô Format để chọn kiểu chữ, màu chữ, màu nền đánh dấu

Sau đó nhấn OK chúng ta có kết quả, các hàng thứ 2 đã được đánh dấu.

Xem thêm: Cách dùng hàm MOD để tô màu các dòng xen kẽ

Ứng dụng hàm MOD dùng để tìm số ngày lẻ

Ví dụ 3: Tính số ngày lẻ dựa vào ngày check in và check out trong bảng sau.

Số ngày lẻ là số ngày trong khoảng thời gian đó không đủ 7 ngày (1 tuần). Đồng nghĩa với việc là số dư của phép tính.

(Thời gian Check out trừ đi thời gian Check in): 7

Sử dụng hàm MOD chúng ta có công thức: Tại D2 =MOD(B2-A2,7)

Sao chép công thức ở D2 xuống các ô còn lại có kết quả:

Ứng dụng hàm MOD dùng để tính số ngày lẻ

Chú ý: Trong ví dụ này để tính số ngày chẵn (số tuần) thì chúng ta có thể dùng hàm INT.

Hàm MOD dùng để tính toán các ô

Trong các bảng tính thực tế, hàm MOD của Excel hiếm khi được sử dụng riêng. Thông thường nó sẽ là một phần của các công thức lớn, thực hiện các phép tính khác nhau dựa trên phần còn lại của phép chia.

MOD tính tổng mỗi hàng thứ N hoặc cột

Điều đáng ngạc nhiên Excel không có chức năng tính tổng mỗi hàng thứ 2, 3 … trong một bảng tính. Tuy nhiên, điều đó có thể dễ dàng thực hiện bằng cách sử dụng một trong các công thức sau.

Tính tổng mỗi hàng

Để thêm các giá trị trong mỗi hàng thứ hai, hãy sử dụng hàm MOD kết hợp với ROW và SUMPRODVEL:

Lúc này công thức tổng các hàng chẵn sẽ là:

SUMPRODUCT((MOD(ROW(range),2)=0)(range))

Công thức tổng các hàng lẻ sẽ là:

SUMPRODUCT((MOD(ROW(range),2)=1)(range))

Giả sử B2:B7 là các ô cần tính tổng thì các công thức sẽ như sau:

Tổng các hàng chẵn:

=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=0)($B$2:$B$7))

Tổng các hàng lẻ:

=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=1)*($B$2:$B$7))

Như kết quả hiển thị ở hình ảnh dưới đây:

Trong các công thức này, tổ hợp MOD và ROW xác định các hàng cần tính tổng và SUMPRODVEL sẽ cộng các giá trị lại.

Cách thực hiện sẽ là: ROW cung cấp một mảng các số hàng cho đối số số của hàm MOD. MOD chia mỗi số hàng cho 2 và bạn kiểm tra kết quả:

• Để tính tổng các hàng chẵn, kết quả phải là 0 vì các số chẵn chia hết cho 2, không có phần dư.
• Để tính tổng các hàng lẻ, kết quả phải là 1 vì các số lẻ chia cho 2 còn lại 1.

Thay vì SUMPRODVEL, bạn có thể sử dụng hàm IF để đánh giá điều kiện và SUM để cộng các số:

=SUM(IF(MOD(ROW($B$2:$B$7),2)=0,$B$2:$B$7,0))

Tổng mỗi hàng thứ N

Để thêm hàng thứ 3, 4, 5 … hãy sử dụng công thức chung này:

SUMPRODUCT((MOD(ROW(range)-ROW(first_cell)+1,n)=0)(range))

Ví dụ: Để tổng hợp mọi ô thứ 3 trong phạm vi C2:C10, công thức sẽ như sau:

=SUMPRODUCT((MOD(ROW($C$2:$C$10)-ROW($C$2)+1,3)=0)($C$2:$C$10))

Tổng mỗi cột thứ N

Để tính tổng các số trong mỗi cột thứ 2, 3 … chỉ cần thay hàm ROW bằng COLUMN trong công thức trên, như sau:

SUMPRODUCT((MOD(COLUMN(range)-COLUMN(first_cell)+1,n)=0)(range))

Ví dụ: Để thêm số vào mỗi cột thứ 4 trong phạm vi B2:I2, bạn thực hiện theo công thức sau:

=SUMPRODUCT((MOD(COLUMN($B2:$I2)-COLUMN($B2)+1,4)=0)($B2:$I2))

Công thức MOD để nối N ô

Trong các tình huống khi các chi tiết liên quan đến cùng một mục được chia thành nhiều ô, việc kết hợp nội dung của N số ô vào một ô có thể là một thách thức.

Trong ví dụ này, chúng tôi có chi tiết thứ tự trong ba ô khác nhau trong cột B, vì vậy cần tìm cách ghép từng khối của ba ô, bắt đầu bằng ô B2. Điều này có thể được thực hiện với công thức sau:

=IF(MOD(ROW()-1, 3)=0, CONCATENATE(B2, “-“, B3, “-“, B4), “”)

Ứng dụng hàm MOD để đếm các ô có chứa số lẻ hoặc số chẵn

Cách dễ nhất để xác định một số nguyên chẵn là chia số đó cho 2 và kiểm tra phần còn lại bằng không. Để xác định một số lẻ, bạn kiểm tra phần còn lại của 1.

Đếm các ô có số lẻ bằng công thức:

SUMPRODUCT((MOD(range,2)=1)1)

Đếm các ô có số chẵn bằng công thức:

SUMPRODUCT((MOD(range,2)=0)1)

Với các số trong các ô từ A2:A8, các công thức có dạng như sau:

Đếm các ô chứa số lẻ :

=SUMPRODUCT((MOD(A2:A8,2)=1)1)

Đếm các ô chứa số chẵn:

=SUMPRODUCT((MOD(A2:A8,2)=0)1)

Sử dụng MOD trong Excel để tô sáng các ô

Ngoài việc tính toán các ô dựa trên một phần còn lại cụ thể, hàm MOD trong Excel có thể được sử dụng để làm nổi bật các ô đó. Để thực hiện, bạn sẽ phải tạo một quy tắc định dạng có điều kiện dựa trên công thức.

Đánh dấu số lẻ và số chẵn

Để tô sáng các ô chứa số lẻ hoặc số chẵn, hãy sử dụng công thức MOD sau đây để chia số đó cho 2 và sau đó kiểm tra phần còn lại lần lượt là 1 và 0:

Làm nổi bật các ô có số lẻ :

=MOD(A2,2)=1

Làm nổi bật các ô có số chẵn:

=MOD(A2,2)=0

Trong đó A2 là ô ngoài cùng bên trái có dữ liệu.

Làm nổi bật số nguyên và số thập phân

Để xác định các số nguyên, chỉ cần thực hiện theo chuỗi logic sau: Bất kỳ số nào chia cho 1 bằng chính nó, có nghĩa là một số nguyên chia cho 1 luôn luôn là cùng một số nguyên, có nghĩa là phần còn lại bằng 0 và chúng ta có thể sử dụng công thức MOD đơn giản như sau:

=MOD(A2,1)=0

Nếu phần còn lại lớn hơn 0, bạn đang xử lý một phân số (bao gồm cả số thập phân):

=MOD(A2,1)>0

Làm nổi bật bội số của một số

Để làm nổi bật bội số của một giá trị cụ thể, chỉ cần chia một số cho giá trị đó và kiểm tra phần còn lại bằng không.

Ví dụ: Để làm nổi bật các ô chứa 10 và tất cả bội số của nó, hãy sử dụng công thức sau:

=MOD(A3,10)=0

Trong đó A3 là ô trên cùng bên trái trong phạm vi mà bạn tạo quy tắc (A3:C9 trong ảnh chụp màn hình bên dưới).

Cách sử dụng MOD để xác thực dữ liệu

Một cách sử dụng khác của hàm MOD là có thể ngăn việc nhập một số dữ liệu nhất định vào các ô được chọn, chẳng hạn như số lẻ, số chẵn hoặc số thập phân. Điều này có thể được thực hiện với cùng các công thức MOD mà chúng ta đã sử dụng để làm nổi bật các ô, nhưng lần này chúng ta sẽ xác định quy tắc xác thực dữ liệu.

Để tạo quy tắc xác thực dữ liệu dựa trên công thức, bạn truy cập vào menu Data > Data Validation. Trên tab Settings, chọn Custom trong hộp Allow và nhập công thức của bạn vào hộp Formula.

Ví dụ: Để chỉ cho phép các số nguyên trong các ô A2:A8, hãy chọn các ô này và cấu hình quy tắc với công thức sau:

=MOD(A2,1)=0

Theo cách tương tự, bạn có thể giới hạn mục nhập dữ liệu ở số chẵn hoặc số lẻ:

Chỉ cho phép số lẻ:

=MOD(A2,2)=1

Chỉ cho phép số chẵn:

=MOD(A2,2)=0

Trong đó A2 là ô trên cùng bên trái của phạm vi đã chọn.

Chú ý: Tùy theo cài đặt của từng máy tính mà bạn dùng dấu phân cách là dấu phẩy (,) hoặc chấm phẩy (;) nhé.

Tổng kết

Bài viết trên đây là những hướng dẫn chi tiết về cú pháp, cách sử dụng, những lưu ý và cả ví dụ cụ thể của hàm MOD trong Excel. Hy vọng bài viết này đã giúp các bạn thực hiện tốt các thao tác tính toán trong ứng dụng tin học văn phòng Microsoft Excel của mình. 

Chúc các bạn thành công