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 trở lên. Không chỉ vậy, trên phiên bản Excel cho Tablet hay trên Smartphone iPhone hoặc Smartphone Android thì bạn vẫn có thể sử dụng hàm này. Vậy cụ thể về hàm này ra sao, cách sử dụng hàm này thế nào, Gitiho mời các bạn tham khảo cụ thể trong nội dung bài viết tại đây.

Bạn Đang Xem: Cách dùng hàm MOD trong Excel kèm ví dụ minh họa chi tiết

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 phù 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 tất cả chúng ta sử dụng Hàm MOD hàm chia lấy phần dư trong Excel.

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

Thuần thục 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ó những đối số sau đây:

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

Divisor (Bắt buộc): Số chia mà bạn muốn.

Ý nghĩa của hàm MOD: Trả về số dư sau thời điểm 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àm lấy phần dư trong Excel

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

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

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

Sao chép công thức xuống các ô sót 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ừ

Khi đối chiếu 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 để làm khắc ghi các hàng

Ví dụ 2: Hãy khắc ghi các hàng thứ hai trong bảng sau:

Để thực hiện khắc ghi, tất cả chúng ta tuân 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 khắc ghi

Sau đó nhấn OK tất cả chúng ta có kết quả, các hàng thứ hai đã được khắc ghi.

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 để làm tìm số ngày lẻ

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

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

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

Sử dụng hàm MOD tất cả 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 ô sót lại có kết quả:

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

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

Hàm MOD dùng để làm 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 đa số 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 sót lại của phép chia.

Xem Thêm : Công việc nhập mã Momo có lừa đảo không? Có thật không?

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ứ hai, 3 … trong một bảng tính. Tuy nhiên, điều đó có thể dễ dàng thực hiện bằng phương pháp 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 phù 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ì những 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 tại đây:

Trong các công thức này, tổng 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à một trong vì các số lẻ chia cho 2 sót lại 1.

Thay vì SUMPRODVEL, chúng ta cũng có thể sử dụng hàm IF để nhận định ĐK 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ứ hai, 3 … chỉ có 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 lúc các cụ thể liên quan đến cùng một mục được chia thành nhiều ô, việc phối hợp nội dung của N số ô vào trong 1 ô có thể là một thử thách.

Trong ví dụ này, chúng tôi có cụ thể trật 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 ô, mở màn bằng ô B2. Điều này còn 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 sót lại bằng không. Để xác định một số lẻ, bạn kiểm tra phần sót lại của một.

Đế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:

Xem Thêm : NHŨ DỊCH LÀ GÌ

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

Với những 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 sót lại cụ thể, hàm MOD trong Excel có thể được sử dụng để làm nổi trội các ô đó. Để thực hiện, các bạn sẽ phải tạo một quy tắc định dạng có ĐK dựa trên công thức.

Khắc ghi 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 sót lại tuần tự là một trong và 0:

Làm nổi trội các ô có số lẻ :

=MOD(A2,2)=1

Làm nổi trội các ô có số chẵn:

=MOD(A2,2)=0

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

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

Để xác định các số nguyên, chỉ có thực hiện theo chuỗi logic sau: Bất kỳ số nào chia cho một bằng chính nó, có tức là một số nguyên chia cho một xoành xoạch là cùng một số nguyên, có tức là phần sót lại bằng 0 và tất cả 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 sót lại to ra thêm 0, bạn đang xử lý một phân số (gồm có cả số thập phân):

=MOD(A2,1)>0

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

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

Ví dụ: Để làm nổi trội 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 số đó 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 hiển thị phía dưới).

Cách sử dụng MOD để xác thực tài 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ố tài liệu nhất định vào các ô được chọn, ví dụ như số lẻ, số chẵn hoặc số thập phân. Điều này còn có thể được thực hiện với cùng các công thức MOD mà tất cả chúng ta đã sử dụng để làm nổi trội các ô, nhưng lần này tất cả chúng ta sẽ xác định quy tắc xác thực tài liệu.

Để tạo quy tắc xác thực tài liệu dựa trên công thức, bạn truy cập vào thực đơn 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ỉ được cho phép các số nguyên trong các ô A2:A8, nên lựa 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 phong cách tương tự, chúng ta cũng có thể giới hạn mục nhập tài liệu ở số chẵn hoặc số lẻ:

Chỉ được cho phép số lẻ:

=MOD(A2,2)=1

Chỉ được cho phép số chẵn:

=MOD(A2,2)=0

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

Lưu ý: Tùy theo tùy chỉnh thiết lập 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

Nội dung bài viết trên đây là những hướng dẫn cụ thể 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 nội dung bài viết này đã hỗ trợ 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