6 Ứng Dụng Tuyệt Vời Của Hàm MOD Trong Excel Bạn Cần Biết
Trong bài viết này mình sẽ giới thiệu cho các bạn về hàm MOD và 6 ứng dụng tuyệt vời khi sử dụng hàm MOD trong Excel, hãy cùng mình tìm hiểu nhé.
Tổng quan về hàm MOD
Hàm MOD là hàm trả về số dư của phép chia. Cú pháp của hàm MOD bao gồm MOD(number, divisor), trong đó number là số bị chia và divisor là số chia. Ví dụ nhập vào =MOD(5,2) bạn sẽ nhận được kết quả là một.
Lấy phần thập phân của một số
Giả sử mình có một số thập phân rất dài là 17.57142857. Để có thể lấy được phần thập phân sau dấu chấm bạn chỉ cần nhập vào công thức =MOD(B5,1). Trong đó B5 là ô chứa số thập phân, bạn sẽ nhận được kết quả là 0,57142857 khi enter.
Tách mốc thời gian
Giả sử mình có một mốc thời gian bao gồm ngày, tháng, năm và giờ như 4/1/2021 11:05. Bản chất của mốc thời gian này chính là một con số. Bạn có thể kiểm tra bằng cách vào thẻ Home, chọn vào mục Custom sau đó đổi định dạng về number là có thể nhận ra.
Sau khi đã hiểu được bản chất bây giờ chỉ cần áp dụng công thức như ví dụ trên là đã có thể tách phần giờ ra một ô riêng. Bạn nhập vào =MOD(B9,1) với B9 là ô chứa mốc thời gian, hoàn tất ấn Enter để hiện kết quả.
Do ô kết quả lấy định dạng của ô B9 tức là ô chứa mốc thời gian nên bạn cần phải chuyển đổi định dạng về dạng giờ sau khi tách. Cách làm là click chuột phải sau đó chọn vào Format Cells.
Ở cửa sổ Format Cells bạn sẽ chọn thẻ Number sau đó chọn vào mục Custom, cuối cùng là lựa chọn kiểu định dạng là h:mm cho ô kết quả. Hoàn tất chọn OK.
Tìm thứ ngày gần nhất
Trong ví dụ này giả sử mình có một ngày là 04/01/2021 tương ứng với thứ 5. Để tìm ngày thứ hai gần nhất của ngày 04/01/2021 tức là ngày 29/03/2021 thì bạn nhập vào công thức như sau: =B13-MOD(B13-2,7). Trong đó B13 là ô chứa ngày cần kiểm tra. Bên trong hàm MOD số bị chia là ngày kiểm tra trừ đi số thứ tự của ngày cần tìm (do là tìm thứ hai gần nhất nên nhập là 2, chủ nhật thì bạn nhập 1 nhé), số chia là 7 tương ứng cho 7 ngày trong tuần.
Kết quả sẽ trả về là dạng số nên bạn cần format lại về định dạng ngày (có thể tham khảo cách làm ở ví dụ trên). Để đổi định dạng của ô một cách nhanh chóng bạn chỉ cần chọn vào ô chứa định dạng ngày (ô B13), sau đó vào thẻ Home chọn Format Painter để sao chép định dạng, cuối cùng chọn vào ô kết quả để thay đổi định dạng.
Chuyển đổi đơn vị giây
Đến với ví dụ số 4, giả sử một người thu thập kết quả chạy điền kinh của các vận động viên với đơn vị tính là giây. Để chuyển đổi đơn vị này về giờ, phút, giây chúng ta sẽ sử dụng hàm Time để lấy số giờ, phút, giây và kết hợp cùng hàm INT lấy để số nguyên khi chia. Bạn nhập vào công thức sau =TIME(INT(B17/3600),INT(B17/60),MOD(70,60)). Trong đó B17 là ô chứa số giây (70 giây), tính giờ ta dùng hàm INT để ô cần tính chia cho 3600 và lấy phần nguyên, tương tự cho tính phút là chia 60. Đối với tính giây ta dùng hàm MOD để chia lấy dư của ô cần tính với 60.
Sau khi thu được kết quả bạn sẽ ấn Ctrl + 1 để truy cập nhanh vào cửa sổ Format Cells. Tại cửa sổ Format Cells bạn chọn thẻ Number, chọn tiếp mục Custom cuối cùng là thay đổi định dạng thành h:mm:ss. Kết quả cho công thức trên là 0:01:10, vẫn còn hai ô chưa được tính bạn có thể tải file thực hành và áp dụng công thức để tìm ra đáp áp nhé.
Ràng buộc điều kiện khi nhập
Đến với ví dụ 5, giả sử có một vùng dữ liệu gồm hai trường là sản phẩm và số lượng. Để ràng buộc chỉ cho phép nhập số nguyên dương vào trường số lượng, và sẽ báo lỗi khi nhập vào là số âm, số thập phân hoặc ký tự. Cách làm là tô đen toàn bộ ô thuộc trường số lượng, sau đó vào thẻ Data chọn Data Validation.
Ở cửa sổ Data Validation bạn sẽ chọn thẻ Setting, ở mục Allow chọn Custom, ở phần Formula bạn sẽ nhập vào công thức: =AND(MOD(C23,1)=0, C23>0). Trong đó C23 là ô nằm trong trường số lượng, mình dùng hàm AND để ràng buộc hai biểu thức logic là ô C23 chia lấy dư cho 1 phải bằng 0 và ô hai 23 phải lớn hơn 0.
Bạn có thể điều chỉnh khi nhập sai sẽ hiện lên thông báo gì ở thẻ Error Alert, ví dụ mình sẽ hiện thông báo là "Nhập sai định dạng" khi nhập sai (tham khảo hình).
Đếm và tính tổng các số
Đến với ví dụ cuối cùng chúng ta sẽ áp dụng hàm MOD để đếm và tính tổng các số chẵn trong một vùng dữ liệu. Đầu tiên là đếm các số chẵn của vùng dữ liệu chúng ta sẽ dùng hàm MOD kết hợp với hàm SUMPRODUCT bạn nhập vào công thức sau: =SUMPRODUCT((MOD(E30:G35,2)=0)*1). Trong đó E30:G35 là vùng dữ liệu chứa các số, chúng ta dùng hàm MOD để kiểm tra các số trong vùng dữ liệu số nào chia lấy dư cho 2 mà bằng 0 (tức số chẵn), kết quả sẽ trả về là true nếu đó là số chẵn nên phải nhân với 1 để chuyển định dạng về số (lúc này kết quả là 1). Cuối cùng hàm SUMPRODUCT sẽ tính tổng số lần trả về true.
Đối với việc tính tổng các số chẵn trong vùng dữ liệu chúng ta chỉ cần sửa lại công thức trên một chút là hoàn tất. Bạn nhập vào công thức =SUMPRODUCT((MOD(E30:G35,2)=0)*1,E30:G35), vẫn là dùng hàm MOD để kiểm tra số chẵn sau đó nhân với 1 để chuyển về định dạng số, tiếp tục nhân cho chính số đó trong vùng dữ liệu. Hàm SUMPRODUCT sẽ tính tổng các số và trả về kết quả.
Bạn có thể áp dụng công thức trên để đếm các số lẻ và tính tổng các số lẻ nhé. Chúc may mắn.
Như vậy mình đã hoàn thành bài hướng dẫn 6 ứng dụng tuyệt vời của hàm MOD trong Excel. Đây là một hàm rất thường hay dùng trong học tập và công việc nên khi bạn thành thạo nó sẽ giúp ích được rất nhiều. Nếu muốn học thêm các bài học Excel miễn phí thì hãy tìm kiếm trên website này nhé.
Video hướng dẫn
CHO ĐIỂM BÀI VIẾT NÀY
