Hàm MOD trong Excel là hàm chia lấy số dư cho ước số và kết quả trả về sẽ cùng dấu với ước số. Trong toán học, phép toán này được gọi là modulo hoặc modulu nên hàm có tên là MOD. Bài viết này sẽ hướng cách sử dụng hàm MOD qua ví dụ cụ thể.Bạn đang xem: Hàm chẵn lẻ trong excel
Hàm MOD có cú pháp sau
MOD(number, divisor)
Trong đó
Number (bắt buộc) – Số được chia.Divisor (bắt buộc) – Số để chia cho.
Đang xem: Hàm lấy số chẵn trong excel
Ví dụ: MOD (10,3) sẽ trả về 1 vì 10 chia cho 3 sẽ có thương số là 3 và phần còn lại là 1 (10 = 3*3 + 1). Công thức MOD (10,5) sẽ trả về 0 vì 10 chia được cho 5 mà không có phầ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!.
Công thức MOD để 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 công thức 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 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), “”)
Công thức 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é.
Trên đây là cách bạn sử dụng hàm MOD trong Excel với những ví dụ cụ thể dễ hiểu nhất. Bạn cũng có thể tham khảo thêm các bài viết về cách sử dụng các hàm khác trong Excel trên huannghe.edu.vn hoặc đăng ký một khóa học tin học bổ ích của chúng tôi tại tại đây. Cảm ơn bạn đã theo dõi bài viết.