Cách sử dụng hàm MOD của Excel để giải quyết các vấn đề thực tế

[Starlink]

MOD linh hoạt hơn nhiều, bạn có thể nghĩ vậy.

Hàm MOD trong Microsoft Excel cho phép bạn tính toán xem một số có chia hết cho số khác không. Nếu không, hàm sẽ cho bạn biết số còn lại là bao nhiêu. Ví dụ, nếu bạn chia 3 cho 2, hàm MOD sẽ trả về 1. Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách sử dụng hàm này để giải quyết các vấn đề thực tế.

Để theo dõi khi bạn đọc hướng dẫn này, hãy tải xuống bản sao miễn phí của sổ làm việc Excel được sử dụng trong các ví dụ. Sau khi nhấp vào liên kết, bạn sẽ thấy nút tải xuống ở góc trên bên phải màn hình.

1. Cú pháp hàm MOD

Trước khi tôi giới thiệu cho bạn cách hoạt động của hàm MOD, hãy dành chút thời gian để xem nó hoạt động như thế nào:

Mã nguồn [Chọn] Expand

=MOD(n,d)
trong đó n là số bạn muốn chia, và d là số chia. Vì vậy, nếu bạn muốn chia 3 cho 2, 3 là số, và 2 là số chia.

Nếu số chia là số âm, kết quả cũng sẽ là số âm. Mặt khác, nếu số chia là 0, Excel sẽ trả về lỗi #DIV/0!, vì bạn không thể chia một số cho số không.

Tuy nhiên, hàm MOD hiếm khi được sử dụng riêng lẻ—giá trị thực sự của nó chỉ được phát huy khi được sử dụng cùng với các hàm và công cụ Excel khác.

2. Ví dụ 1: Sử dụng hàm MOD để giảm thiểu phần còn lại

Giả sử bạn có nhiều mặt hàng khác nhau mà bạn muốn đóng gói và phân phối. Cột A chứa ID sản phẩm, cột B cho bạn biết bạn có bao nhiêu sản phẩm và hàng 2 chứa các kích cỡ gói khác nhau. Mục tiêu của bạn là tìm ra kích cỡ gói tối ưu cho từng sản phẩm, tùy thuộc vào số lượng của từng sản phẩm bạn có.

Trong trường hợp này, bạn có thể sử dụng hàm MOD để cho bạn biết liệu số lượng sản phẩm trong cột B có chia hết cho kích thước gói hàng ở hàng 2 hay không và nếu không thì bạn còn lại bao nhiêu sản phẩm.

Vì vậy, trong ô C3, hãy nhập:

Mã nguồn [Chọn] Expand

=MOD($B3,C$2)
Ký hiệu đô la ($) được đặt trước tham chiếu hàng hoặc cột—còn gọi là tham chiếu hỗn hợp —yêu cầu Excel khóa tham chiếu đó để công thức có thể được áp dụng cho các ô khác trong phạm vi.

Khi bạn nhấn Enter (hoặc Ctrl+Enter để ở lại cùng một ô), bạn có thể thấy rằng nếu bạn sử dụng gói có 10 khoảng trống cho sản phẩm 2805, thì sẽ có bốn khoảng trống chưa mở.

Bây giờ, khi bạn nhấp và kéo nút điền vào ô C3 sang bên phải để hoàn thành hàng, bạn có thể thấy rằng sử dụng các gói có tám hoặc ba khoảng trống là lý tưởng cho sản phẩm này.

Tiếp theo, trong ô G3, bạn muốn Excel cho bạn biết kích thước gói tốt nhất để sử dụng cho sản phẩm đó. Để thực hiện việc này, hãy sử dụng INDEX và XMATCH:

Mã nguồn [Chọn] Expand

=INDEX($C$2:$F$2,1,XMATCH(SMALL(C3:F3,1),C3:F3))
trong đó INDEX($C$2:$F$2,1 yêu cầu Excel trả về giá trị từ ô C2 đến F2 và XMATCH(SMALL(C3:F3,1),C3:F3)) tra cứu giá trị nhỏ nhất trong các ô C3 đến F3 để xác định số cột cung cấp cho phần INDEX của công thức.

Cuối cùng, chọn các ô từ C3 đến G3 và nhấp đúp vào nút điền để sao chép tất cả các công thức vào các hàng còn lại trong phạm vi.

Bây giờ, nhờ hàm MOD được sử dụng trong các ô từ C3 đến F12, cột G sẽ cho bạn biết kích thước bao bì tốt nhất để sử dụng cho từng sản phẩm và bạn có thể xem trong các cột từ C đến F của từng sản phẩm để xem bạn sẽ còn lại bao nhiêu (nếu có) sau khi sử dụng kích thước bao bì tối ưu này.

Nếu giá trị nhỏ nhất xảy ra hai lần liên tiếp, XMATCH sẽ sử dụng trường hợp đầu tiên từ trái sang phải theo mặc định. Trong trường hợp này, điều này là lý tưởng vì bạn muốn sử dụng càng ít bao bì càng tốt.

Điểm tuyệt vời của việc sử dụng tổ hợp các hàm này là nếu số lượng sản phẩm trong cột B thay đổi, kích thước gói tối ưu sẽ được cập nhật theo công thức MOD bạn đã sử dụng trong các ô từ C3 đến F12. Ví dụ, nếu ban đầu bạn đếm nhầm sản phẩm 2805 và thực tế là bạn có 25 thay vì 24, khi bạn cập nhật giá trị trong ô B3, bạn thấy rằng kích thước gói tối ưu sẽ thay đổi thành năm.

3. Ví dụ 2: Sử dụng hàm MOD để làm nổi bật một số ô nhất định

Hàm MOD của Excel không nhất thiết phải được sử dụng để trả về giá trị mà còn có thể được sử dụng để xác định ô nào sẽ được định dạng thông qua định dạng có điều kiện.

Trong ví dụ này, hãy tưởng tượng bạn muốn tạo hoạt động cho mười nhóm người. Tuy nhiên, các hoạt động khác nhau đòi hỏi số lượng nhóm phụ khác nhau, vì vậy bạn cần xác định nhóm nào có thể thực hiện hoạt động nào, đảm bảo tất cả thành viên đều tham gia.

Đầu tiên, trong ô B1, bạn muốn tạo danh sách thả xuống các hoạt động trong cột D. Để thực hiện việc này, khi đã chọn ô B1, hãy đi đến tab "Dữ liệu" trên ruy-băng và nhấp vào nửa trên của nút "Xác thực dữ liệu".

Thứ hai, trong trường Allow của hộp thoại Data Validation, chọn "List". Tiếp theo, đặt con trỏ vào trường Source và chọn các ô chứa các giá trị bạn muốn xuất hiện trong danh sách thả xuống. Sau đó, nhấp vào "OK".

Bây giờ, khi bạn chọn ô B1, bạn có thể thấy danh sách thả xuống các hoạt động và bạn đã sẵn sàng áp dụng định dạng cho các ô từ A4 đến B13 để cho bạn biết liệu mỗi nhóm có thể thực hiện từng hoạt động hay không.

Để thực hiện việc này, hãy chọn các ô từ A4 đến B13 và trong tab Trang chủ trên ruy-băng, hãy nhấp vào Định dạng có điều kiện > Quy tắc mới.

Sau đó, trong hộp thoại Quy tắc định dạng mới, chọn "Sử dụng công thức để xác định ô nào cần định dạng" và trong trường công thức, nhập:

Mã nguồn [Chọn] Expand

=MOD($B4,XLOOKUP($B$1,$D$2:$D$9,$E$2:$E$9))=0
Trong đó:

    $B4 là ô đầu tiên trong cột B đóng vai trò là số cần chia,
    XLOOKUP($B$1,$D$2:$D$9,$E$2:$E$9) tra cứu giá trị trong ô B1 và trả về kích thước nhóm con tương ứng từ bảng tra cứu (bộ chia) và
    =0 áp dụng định dạng cho các ô có số thành viên trong một nhóm chia hết cho số người cần có trong nhóm con của hoạt động đó.

Đặt ký hiệu đô la trước tham chiếu cột ($B4) trong công thức này cho Excel biết rằng bạn muốn định dạng có điều kiện trên tất cả các ô được chọn trong cột B.

Nói cách khác, bạn muốn Excel định dạng các nhóm trong đó toàn bộ quy mô nhóm chia hết cho quy mô nhóm con cần thiết cho hoạt động được chọn trong ô B1.

Cuối cùng, nhấp vào "Định dạng" để chọn định dạng cho các ô đáp ứng các điều kiện này. Trong trường hợp của tôi, tôi đã chọn nền ô màu xanh lá cây nhạt.

Nhấp vào "OK" để đóng hộp thoại.

Bây giờ, hãy chọn một hoạt động trong menu thả xuống ở ô B1 và các nhóm có thể tham gia vào hoạt động đó sẽ được tô sáng theo định dạng bạn đã chọn. Trong ví dụ này, bạn có thể thấy rằng các nhóm 1, 2, 3, 5 và 8 có thể tham gia bài kiểm tra vì tổng số thành viên trong các nhóm đó chia hết cho quy mô nhóm con cần thiết cho hoạt động này.

Mặt khác, khi bạn chọn "Đi bộ đường dài" từ danh sách, chỉ có nhóm 5 được xác định là có thể thực hiện hoạt động này vì đây là nhóm duy nhất có tổng số thành viên chia hết cho quy mô nhóm con.

4. Ví dụ 3: Sử dụng hàm MOD để trích xuất giá trị ngày-giờ

Hàm MOD của Microsoft Excel có thể được sử dụng để trích xuất phần thập phân của một số. Trong ví dụ này, hãy nhập:

Mã nguồn [Chọn] Expand

=MOD(A2,1)
vào ô B2 chia 10,2 cho 1 và cho bạn biết phần dư của phép chia này là 0,2.

Bạn cũng có thể trích xuất số nguyên từ số này bằng hàm INT, hàm này làm tròn giá trị xuống số nguyên gần nhất. Vì vậy, hãy nhập:

Mã nguồn [Chọn] Expand

=INT(A2)
vào ô C2 sẽ xóa phần thập phân khỏi giá trị trong ô A2, chỉ trả về số nguyên.

Tương tự như vậy, bạn có thể sử dụng MOD và INT để phân tách các giá trị ngày-giờ.

Tại đây, ô A2 chứa ngày và giờ, còn ô A3 chứa số sê-ri tương ứng.

Excel lưu trữ ngày tháng và thời gian dưới dạng số sê-ri, có thể thấy khi bạn thay đổi định dạng số thành "Chung". Vì vậy, trong ví dụ này, giá trị của ô A2 và A3 giống hệt nhau, nhưng định dạng số cho ô A2 là định dạng số ngày giờ tùy chỉnh, trong khi định dạng số cho ô A3 là định dạng số chung.

Trong số sê-ri ở ô A3, 45782 biểu thị ngày và 0,4375 biểu thị thời gian và bạn có thể sử dụng MOD và INT để tách chúng ra.

Đầu tiên, chọn ô B2 và thay đổi định dạng số thành "Thời gian" trong nhóm Số của tab Trang chủ trên ruy-băng.

Sau đó, chọn ô C2 và thay đổi định dạng số thành "Ngày ngắn".

Sau đó, quay lại ô B2 và nhập:

Mã nguồn [Chọn] Expand

=MOD(A2,1)
Thao tác này trích xuất số thập phân (thời gian) từ biểu diễn số sê-ri của giá trị ngày-giờ trong ô A2 và vì bạn đặt định dạng số ô thành Thời gian nên nó chuyển đổi số thập phân sê-ri thành giá trị thời gian.

Cuối cùng, quay lại ô C2 và nhập:

Mã nguồn [Chọn] Expand

=INT(A2)
Hàm này sẽ trích xuất số nguyên (ngày) từ số sê-ri trong ô A2 bằng cách làm tròn xuống số nguyên gần nhất và vì bạn đặt đây là định dạng số ngày ngắn nên hàm này sẽ chuyển đổi số nguyên sê-ri thành giá trị ngày.

Một nửa cuộc chiến khi sử dụng hàm trong Microsoft Excel là biết hàm nào là tốt nhất để giải quyết vấn đề của bạn. Ví dụ, nếu bạn muốn tinh chỉnh các phép tính của mình, hàm AGGREGATE có thể sẽ giải quyết được vấn đề, và nếu bạn muốn tạo danh sách các số, hàm SEQUENCE là lựa chọn tốt nhất của bạn. May mắn thay, Excel ở đây để giúp bạn tìm hàm bạn cần —bạn có thể nhấp vào nút "fx" bên cạnh thanh công thức hoặc tìm kiếm hàm trong hộp thoại Chèn hàm, và các công cụ hàm của Excel sẽ giúp bạn trong suốt quá trình tạo công thức hoàn hảo.