Tải xuống sổ làm việc mẫu
Hướng dẫn Excel này trình bày cách sử dụng Hàm MOD trong Excel để tính phần còn lại sau khi chia.
Tổng quan về chức năng MOD
Hàm MOD trả về phần còn lại, hoặc mô đun, của một số sau khi thực hiện phép chia. Tuy nhiên, chức năng MOD không hoàn toàn để giúp chúng tôi giải quyết các vấn đề về phân chia. Nó thậm chí còn trở nên mạnh mẽ hơn khi chúng ta muốn tìm kiếm mọi mục thứ N trong danh sách hoặc khi chúng ta cần tạo một mẫu lặp lại.
Để sử dụng chức năng trang tính MOD Excel, hãy chọn ô và nhập:
Cú pháp và đầu vào của hàm MOD:
| 1 | = MOD (số, số chia) |
con số - Một số.
số chia - Một số chia hết.
Chức năng MOD là gì?
Hàm MOD trả về phần còn lại, hoặc mô đun, của một số sau khi thực hiện phép chia. Tuy nhiên, chức năng MOD không hoàn toàn để giúp chúng tôi giải quyết các vấn đề về phân chia. Nó thậm chí còn trở nên mạnh mẽ hơn khi chúng ta muốn tìm kiếm mọi mục thứ N trong danh sách hoặc khi chúng ta cần tạo một mẫu lặp lại.
MOD Toán cơ bản
Khi bạn cố gắng chia 13 cho 4, bạn có thể nói câu trả lời là 3 dư 1. Chữ “1” trong trường hợp này được gọi cụ thể là mô đun (do đó có tên Hàm MOD). Sau đó, trong một công thức, chúng ta có thể viết
| 1 | = MOD (13, 4) |
Và đầu ra sẽ là 1.
Nhìn vào bảng này cung cấp thêm một số minh họa về cách đầu vào / đầu ra của MOD sẽ hoạt động.
| 1 | = MOD (A2,3) |
Lưu ý rằng khi đầu vào là 3, không có phần dư và do đó đầu ra của công thức là 0. Ngoài ra, trong bảng của chúng tôi, chúng tôi đã sử dụng hàm ROW để tạo ra các giá trị của chúng tôi. Phần lớn sức mạnh của MOD sẽ đến từ việc sử dụng hàm ROW (hoặc COLUMN) như chúng ta sẽ thấy trong các ví dụ sau.
MOD Tổng tất cả các hàng khác
Hãy xem xét bảng này:
Với mục đích minh họa, cột thứ hai có công thức
| 1 | = MOD (A2, 2) |
Để thêm tất cả các hàng chẵn, bạn có thể viết công thức SUMIF và có tiêu chí tìm kiếm các giá trị 0 trong cột B. Hoặc, để thêm tất cả các hàng lẻ, có tiêu chí là tìm kiếm 1 giá trị.
Tuy nhiên, chúng tôi hoàn toàn không cần tạo cột trợ giúp. Bạn có thể kết hợp sức mạnh của MOD trong SUMPRODUCT để thực hiện tất cả trong một bước. Công thức của chúng tôi cho điều này sẽ là
| 1 | = SUMPRODUCT (A2: A5, - (MOD (B2: B2, 2) = 0) |
Vì nó nằm trong SUMPRODUCT, nên hàm MOD sẽ có thể xử lý đầu vào mảng của chúng tôi. Chúng tôi đã thấy đầu ra đã có trong cột trợ giúp, nhưng mảng từ MOD của chúng tôi trong công thức này sẽ là {0, 1, 0, 1}. Sau khi kiểm tra các giá trị bằng 0 áp dụng hệ số một ngôi kép, mảng sẽ là {1, 0, 1, 0}. Sau đó hàm SUMPRODUCT thực hiện phép thuật hoặc nhân các mảng để tạo ra {2, 0, 4, 0} và sau đó cộng lại để có được kết quả mong muốn là 6.
MOD Tổng mỗi hàng thứ N
Vì công thức MOD (x, N) sẽ xuất ra 0 ở mọi giá trị thứ N, chúng ta có thể sử dụng công thức này để giúp các công thức chọn và chọn giá trị nào sẽ sử dụng trong các hàm khác. Nhìn vào bảng này.
Mục tiêu của chúng tôi là lấy các giá trị từ mỗi hàng được đánh dấu "Tổng". Hãy lưu ý rằng Tổng số xuất hiện sau mỗi 3rd hàng, nhưng bắt đầu từ hàng 4. Do đó, hàm MOD của chúng tôi sẽ sử dụng 3 làm 2NS và chúng ta cần trừ 1 khỏi đối số đầu tiên (vì 4 -1 = 3). Bằng cách này, các hàng mong muốn mà chúng ta muốn (4, 7, 10) sẽ là bội số của 3 (3, 6, 9). Công thức tính tổng các giá trị mong muốn của chúng tôi sẽ là
| 1 | = SUMPRODUCT (C2: C10, - (MOD (ROW (A2: A10) +2, 3) = 0)) |
Mảng được tạo ra sẽ biến đổi như thế này:
| 12345 | {2, 3, 4, 5, 6, 7, 8, 9, 10}{1, 2, 3, 4, 5, 6, 7, 8, 9}{1, 2, 0, 1, 2, 0, 1, 2, 0}{Sai, Sai, Đúng, Sai, Sai, Đúng, Sai, Sai, Đúng}{0, 0, 1, 0, 0, 1, 0, 0, 1} |
Mảng tiêu chí SUMPRODUCT của chúng tôi hiện đang được thiết lập theo cách chúng tôi cần lấy mỗi 3rd và chúng tôi sẽ nhận được kết quả mong muốn là 90 đô la.
Tổng MOD trên các cột
Cho đến nay, chúng tôi đã sử dụng các ví dụ theo chiều dọc và sử dụng ROW, nhưng bạn cũng có thể sử dụng theo chiều ngang với hàm COLUMN. Hãy xem xét bố cục này:
Chúng tôi muốn tổng hợp tất cả các mục. Công thức của chúng tôi cho điều này có thể là
| 1 | = SUMPRODUCT (B2: E2 * (MOD (COLUMN (B2: E2), 2) = 0) |
Trong trường hợp này, chúng tôi sẽ thiết lập để lấy cứ 2NS trong phạm vi của chúng tôi, vì vậy SUMPRODUCT sẽ chỉ giữ lại các giá trị khác 0 cho các cột B và D. Để tham khảo, đây là bảng hiển thị số cột và giá trị tương ứng của chúng sau khi thực hiện MOD 2.
Đánh dấu mọi hàng thứ N
Một nơi phổ biến khác để sử dụng chức năng MOD là khi bạn muốn có một hàng được đánh dấu xuất hiện ở mỗi hàng thứ N. Hình thức chung cho việc này sẽ là
| 1 | = MOD (ROW () ± Chênh lệch, N) = 0 |
Ở đâu n là số hàng giữa mỗi hàng được đánh dấu (nghĩa là đánh dấu mỗi 3rd hàng, N = 3), và Bù lại là số tùy chọn mà chúng ta cần thêm hoặc trừ để hàng được đánh dấu đầu tiên của chúng ta phù hợp với N (nghĩa là đánh dấu mỗi 3rd nhưng bắt đầu từ hàng 5, chúng ta cần trừ 2 vì 5 -2 = 3). Lưu ý rằng với hàm ROW, bằng cách bỏ qua bất kỳ đối số nào, nó sẽ trả về số hàng từ ô có công thức.
Hãy sử dụng bảng của chúng ta từ trước:
Để áp dụng điểm đánh dấu cho tất cả Tổng số hàng, chúng tôi sẽ tạo quy tắc Định dạng có điều kiện mới với công thức là
| 1 | = MOD (ROW () - 1, 3) = 0 |
Khi định dạng có điều kiện áp dụng công thức này, hàng 2 sẽ thấy
| 1234 | = MOD (2-1, 3) = 0= MOD (1, 3) = 0= 1 = 0= Sai |
Hàng 3 sẽ gặp kết quả tương tự, nhưng sau đó hàng 4 sẽ thấy
| 1234 | = MOD (4-1, 3) = 0= MOD (3, 3) = 0= 0 = 0= Đúng |
Do đó, chúng tôi có quy tắc của chúng tôi hoạt động chính xác, như được hiển thị ở đây:
Đánh dấu số nguyên hoặc số chẵn
Thay vì đánh dấu các hàng cụ thể, bạn cũng có thể kiểm tra các giá trị thực tế trong các ô. Điều này có thể hữu ích khi bạn muốn tìm các số là bội số của N. Ví dụ: để tìm bội số của 3, công thức định dạng có điều kiện của bạn sẽ là
| 1 | = MOD (A2, 3) = 0 |
Cho đến thời điểm này, chúng tôi đang giải quyết các con số nguyên. Tuy nhiên, bạn có thể có đầu vào là số thập phân (ví dụ: 1,234) và sau đó chia cho 1 để chỉ nhận được phần thập phân (ví dụ: 0,234). Công thức này trông giống như
| 1 | = MOD (A2, 1) |
Biết rằng, để chỉ đánh dấu các số nguyên, công thức định dạng có điều kiện sẽ là
| 1 | = MOD (A2, 1) = 0 |
Nối mỗi N ô
Trước đây, chúng tôi đã sử dụng MOD để cho máy tính biết khi nào cần lấy ở giá trị ở mỗi mục thứ N. Bạn cũng có thể sử dụng nó để kích hoạt một công thức lớn hơn được thực thi. Hãy xem xét bố cục này:
Chúng tôi muốn nối các tên với nhau, nhưng chỉ trên mỗi 3rd hàng bắt đầu từ hàng 2. Công thức được sử dụng cho điều này là
| 1 | = IF (MOD (ROW () + 1, 3) = 0, CONCATENATE (A2, "", A3, "", A4), "") |
Hàm MOD của chúng tôi là hàm đóng vai trò là tiêu chí cho hàm IF tổng thể. Trong ví dụ này, chúng tôi cần thêm 1 vào ROW của mình, vì chúng tôi đang bắt đầu ở hàng 2 (2 + 1 = 3). Khi đầu ra của MOD bằng 0, công thức sẽ thực hiện nối. Nếu không, nó chỉ trả về trống.
Đếm giá trị chẵn / lẻ
Nếu bạn đã từng cần đếm có bao nhiêu giá trị chẵn hoặc lẻ trong một phạm vi, bạn sẽ biết rằng COUNTIF không có khả năng thực hiện điều này. Tuy nhiên, chúng ta có thể làm điều đó với MOD và SUMPRODUCT. Hãy xem bảng này:
Công thức chúng tôi sẽ sử dụng để tìm các giá trị lẻ sẽ là
| 1 | = SUMPRODUCT (1 * (MOD (A2: A7, 2) = 1)) |
Thay vì tải ở một số số hàng, MOD của chúng tôi sẽ tải các giá trị của ô thực tế vào mảng. Sự chuyển đổi tổng thể sau đó sẽ tiến triển như vậy:
| 1234 | {5, 5, 3, 3, 2, 1}{1, 1, 1, 1, 0, 1} <- Lấy mod của 2{True, True, True, True, False, True} <- Được kiểm tra nếu giá trị là 0{1, 1, 1, 1, 0, 1} <- Nhân với 1 để chuyển đổi từ True / False thành 1/0 |
Sau đó SUMPRODUCT cộng các giá trị trong mảng của chúng ta, đưa ra câu trả lời mong muốn là: 5.
Mô hình lặp lại
Tất cả các ví dụ trước đã được kiểm tra đầu ra của MOD cho một giá trị. Bạn cũng có thể sử dụng MOD để tạo ra một mẫu số lặp lại, điều này có thể rất hữu ích.
Đầu tiên, giả sử rằng chúng tôi đã có một danh sách các mục mà chúng tôi muốn lặp lại.
Bạn có thể thử sao chép và dán theo cách thủ công bất cứ lúc nào bạn cần, nhưng điều đó sẽ trở nên tẻ nhạt. Thay vào đó, chúng tôi sẽ muốn sử dụng hàm INDEX để truy xuất các giá trị của chúng tôi. Để INDEX hoạt động, chúng ta cần đối số hàng là một dãy số đi kèm {1, 2, 3, 1, 2, 3, 1, v.v.}. Chúng tôi có thể thực hiện điều này bằng cách sử dụng MOD.
Đầu tiên, chúng ta sẽ bắt đầu chỉ với hàm ROW. Nếu bạn bắt đầu với
| 1 | = ROW (A1) |
Và sau đó sao chép nó xuống dưới, bạn sẽ nhận được dãy số cơ bản là {1, 2, 3, 4, 5, 6,…}. Nếu chúng ta áp dụng hàm MOD với 3 là số chia,
| 1 | = MOD (ROW (A1), 3) |
chúng tôi sẽ nhận được {1, 2, 0, 1, 2, 0,…}. Chúng tôi thấy rằng chúng tôi đã có một mẫu lặp lại là “0, 1, 2”, nhưng chuỗi đầu tiên thiếu số 0. Để khắc phục điều này, hãy sao lưu một bước và trừ 1 từ số hàng. Điều này sẽ thay đổi trình tự bắt đầu của chúng tôi thành {0, 1, 2, 3, 4, 5,…}
| 1 | = MOD (ROW (A1) -1, 3) |
Và sau khi nó ra khỏi MOD, chúng ta có {0, 1, 2, 0, 1, 2,…}. Điều này đang tiến gần đến những gì chúng ta cần. Bước cuối cùng là thêm 1 vào mảng.
| 1 | = MOD (ROW (A1) -1, 3) +1 |
Bây giờ tạo ra một dãy số {1, 2, 3, 1, 2, 3,…}. Đây là trình tự mong muốn của chúng tôi! Cắm nó vào một hàm INDEX, chúng tôi nhận được công thức của
| 1 | = INDEX (MyList, MOD (ROW (A1) -1, 3) +1) |
Đầu ra bây giờ sẽ giống như sau:
Ví dụ về MOD trong VBA
Bạn cũng có thể sử dụng hàm LINEST trong VBA.
Trong VBA, MOD là một toán tử (giống như các toán tử cộng, trừ, nhân và chia). Vì vậy, thực hiện các câu lệnh VBA sau
| 123456 | Phạm vi ("C2") = Phạm vi ("A2") Phạm vi điều chỉnh ("B2")Phạm vi ("C3") = Phạm vi ("A3") Phạm vi Mod ("B3")Phạm vi ("C4") = Phạm vi ("A4") Phạm vi điều chỉnh ("B4")Phạm vi ("C5") = Phạm vi ("A5") Phạm vi Mod ("B5")Phạm vi ("C6") = Phạm vi ("A6") Phạm vi điều chỉnh ("B6")Phạm vi ("C7") = Phạm vi ("A7") Phạm vi điều chỉnh ("B7") |
sẽ tạo ra kết quả sau
Đối với các đối số của hàm (known_y’s, v.v.), bạn có thể nhập chúng trực tiếp vào hàm hoặc xác định các biến để sử dụng thay thế.
Quay lại danh sách tất cả các hàm trong Excel
Chức năng MOD của Google Trang tính
Hàm MOD hoạt động hoàn toàn giống trong Google Trang tính như trong Excel:
