Bạn không muốn dùng Excel VBA và muốn tạo Vòng Lặp FOR trong Excel bằng Công Thức? Trong bài này, tôi chỉ cách tạo Vòng Lặp FOR dùng công thức.
Nếu bạn biết lập trình VBA Excel, bạn thật may mắn 🙂. Nhưng, nếu chưa bao giờ viết code VBA hoặc muốn sổ tay Excel không có mã VBA, thì hầu hết thời gian bạn phải tư duy "out of the box" để tạo một vòng lặp đơn giản.
Tải Tệp Làm Việc
Tải tệp làm việc từ liên kết dưới:
3 Ví Dụ Tạo Vòng Lặp FOR Trong Excel Dùng Công Thức
Ở đây, tôi sẽ minh họa 3 ví dụ tạo Vòng Lặp FOR trong Excel dùng công thức. Hãy xem chi tiết.
1. Áp Dụng Kết Hợp Hàm Để Tạo Vòng Lặp FOR Trong Excel
Bây giờ, để tôi cho biết bối cảnh khuyến khích tôi viết ví dụ này.
Tôi là tác giả một số khóa học trên Udemy. Một khóa là về Định Dạng Có Điều Kiện Excel. Tiêu đề khóa: Học Định DORMAT Có Điều Kiện Excel Với 7 Bài Toán Thực Tế. [để truy cập miễn phí khóa này, nhấp vào đây].
Trong bảng thảo luận khóa, một học viên hỏi câu hỏi như dưới [ảnh chụp màn hình].
Câu hỏi của học viên trên Udemy.
Đọc kỹ câu hỏi trên và thử giải quyết…
Các Bước Giải Quyết Vấn Đề Trên:
Ở đây, tôi sẽ dùng hàm OR, OFFSET, MAX, MIN, và ROW như Công Thức Excel để tạo Vòng Lặp FOR.
- Trước hết, việc của bạn là mở workbook mới và nhập các giá trị trên lần lượt vào bảng tính [bắt đầu từ ô C5].
- Thứ hai, chọn toàn bộ phạm vi [từ ô C5:C34].
- Thứ ba, từ ribbon Home >> nhấp lệnh Conditional Formatting.
- Cuối cùng, chọn tùy chọn New Rule từ menu thả xuống.
Lúc này, hộp thoại New Formatting Rule xuất hiện.
- Bây giờ, ở cửa sổ Select a Rule Type >> chọn Use a formula to determine which cells to format.
- Sau đó, ở trường Format values where this formula is true, gõ công thức này:
=OR(OFFSET(C5,MAX(ROW(C$5)-ROW(C5)+3,0),0,MIN(ROW(C5)-ROW(C$5)+1,4),1)-OFFSET(C5,MAX(ROW($C$5)-ROW(C5),-3),0,MIN(ROW(C5)-ROW(C$5)+1,4),1)=3)
- Bây giờ, chọn định dạng phù hợp nhấp nút Format… trong hộp thoại.
Lúc này, hộp thoại Format Cells sẽ hiện ra.
- Bây giờ, từ tùy chọn Fill >> chọn bất kỳ màu nào. Ở đây, chọn nền Light Blue (Xanh nhạt). Cũng thấy sample ngay. Trong trường hợp này, hãy chọn màu nhạt. Vì màu đen có thể che dữ liệu nhập. Sau đó, có thể cần đổi Font Color.
- Sau đó, phải nhấn OK để áp dụng định dạng.
- Sau đó, nhấn OK trên hộp thoại New Formatting Rule. Ở đây, thấy mẫu ngay ở hộp Preview.
Cuối cùng, bạn sẽ nhận được các số được định dạng.
Để tôi chỉ cho bạn thuật toán giải quyết vấn đề trên:
- Ở đây, để bạn hiểu thuật toán dễ dàng, tôi giải thích toàn bộ với hai ô tham chiếu: ô C11 và C17. Ở ô C11 và C17, giá trị là 10 và 20 tương ứng (hình trên). Nếu bạn quen công thức Excel, bạn sẽ ngửi ra hàm OFFSET, vì OFFSET làm việc với điểm tham chiếu.
- Bây giờ, hãy tưởng tượng tôi lấy giá trị phạm vi ô C8:C11 & C11:C14, và C14:C17 & C17: C20 cạnh nhau [hình dưới]. Điểm tham chiếu là C11 và C17 và tôi lấy tổng 7 ô quanh điểm tham chiếu. Bạn sẽ có hình dung như sau. Từ phần đầu, tìm ra mẫu từ hình. C9–C12=3, C10-C13=3, có mẫu. Nhưng phần hai, không có mẫu như vậy.
- Vậy, hãy xây thuật toán ghi nhớ mẫu trên. Trước khi xây công thức chung, tôi chỉ công thức cho ô C11 và C17 rồi sửa để chung cho tất cả. Với điểm tham chiếu (như C11 hoặc C17), tôi lấy tổng 7 ô quanh nó (kể cả điểm tham chiếu) đặt cạnh nhau trong công thức tạo mảng. Sau đó tìm hiệu các mảng nếu bất kỳ hiệu nào bằng 3 thì ô tham chiếu sẽ giá trị TRUE.
- Ở đây, tôi làm dễ dàng dùng hàm OFFSET vì OFFSET trả về mảng. Giả sử tham chiếu ô C11, tôi viết công thức: =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3). Công thức này trả về gì? Hàm OFFSET đầu trả mảng: {10; 11; 12; 15}, OFFSET thứ hai trả mảng {5; 8; 9; 10}. Bạn biết {10; 11; 12; 15} – {5; 8; 9; 10} = {10-5; 11-8; 12-9; 15-10} = {5; 3; 3; 5}. Khi mảng này kiểm tra logic =3 thì Excel tính nội bộ: {5=3; 3=3; 3=3; 5=3} = {False; True; True; False}. Khi hàm OR áp dụng lên mảng: OR({False; True; False; True}, bạn nhận TRUE. Vậy ô C11 nhận giá trị true.
- Vậy tôi nghĩ bạn đã hiểu toàn bộ khái niệm thuật toán này hoạt động thế nào. Bây giờ có vấn đề. Công thức này hoạt động từ ô C8, trên C8 có 3 ô. Nhưng cho ô C5, C6, và C7 công thức này không hoạt động. Vậy công thức phải sửa cho các ô này.
- Bây giờ, cho ô C5 đến C7, chúng ta muốn công thức không xét 3 ô trên. Ví dụ, ô C6, công thức không như ô C11: =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3).
- Ở đây, cho ô C5, công thức như: OR(OFFSET(C5, 3, 0, 1, 1)-OFFSET(C5, 0, 0, 1, 1)=3).
- Sau đó, cho ô C6, công thức như: OR(OFFSET(C6, 2, 0, 2, 1)-OFFSET(C6, -1, 0, 2, 1)=3).
- Tiếp theo, cho ô C7, công thức như: OR(OFFSET(C7, 1, 0, 3, 1)-OFFSET(C7, -2, 0, 3, 1)=3).
- Lại cho ô C8, công thức như: OR(OFFSET(C8, 0, 0, 4, 1)-OFFSET(C8,-3, 0, 4, 1)=3); [đây là công thức chung].
- Sau đó, cho ô C9, công thức như: OR(OFFSET(C9, 0, 0, 4, 1)-OFFSET(C9,-3, 0, 4, 1)=3); [công thức chung].
- Cuối cùng, bạn tìm ra mẫu từ các công thức trên? Tham số hàng của OFFSET đầu giảm từ 3 đến 0; tham số height tăng từ 1 đến 4. Tham số hàng của OFFSET thứ hai giảm từ 0 đến -3 và height tăng từ 1 đến 4.
- Thứ nhất, tham số hàng OFFSET đầu sửa thành: MAX(ROW(C$5)-ROW(C5)+3,0)
- Thứ hai, tham số hàng OFFSET thứ hai sửa thành: MAX(ROW(C$5)-ROW(C5),-3)
- Thứ ba, tham số height OFFSET đầu sửa thành: MIN(ROW(C5)-ROW(C$5)+1,4)
- Thứ tư, tham số height OFFSET thứ hai sửa thành: MIN(ROW(C5)-ROW(C$5)+1,4)
- Bây giờ, hãy hiểu các sửa đổi trên. Chúng không quá khó hiểu. Tất cả bốn sửa đổi này hoạt động như VÒNG LẶP FOR của Excel VBA nhưng tôi xây bằng Công Thức Excel.
- Vậy, bạn đã biết cách công thức chung hoạt động cho ô từ C5:C34.
Vậy tôi đang nói về Lặp trong bảng tính Excel. Vậy đây là ví dụ hoàn hảo về lặp trong Excel. Ở đây, mỗi lần công thức lấy 7 ô và làm việc trên các ô để tìm giá trị cụ thể.
2. Dùng Hàm IF & OR Tạo Vòng Lặp FOR Trong Excel
Trong ví dụ này, giả sử bạn muốn kiểm tra ô có chứa giá trị hay không. Hơn nữa, với VBA Excel FOR Loop, bạn làm dễ dàng nhưng ở đây, tôi làm bằng công thức Excel.
Bây giờ, bạn có thể dùng hàm IF, và OR như Công Thức Excel tạo Vòng Lặp FOR. Hơn nữa, bạn có thể sửa công thức theo ý thích. Các bước dưới đây.
Các bước:
- Trước hết, chọn ô khác E5 nơi bạn muốn thấy Trạng Thái.
- Thứ hai, dùng công thức tương ứng trong ô E5.
=IF(OR(B5="",C5="",D5=""),"Info Missing","Done")
- Tiếp theo, nhấn ENTER ra kết quả.
Phân Tích Công Thức
Ở đây, hàm OR trả về TRUE nếu bất kỳ logic nào cho trước thành TRUE.
- Thứ nhất, B5="" là logic 1, kiểm tra ô B5 có giá trị hay không.
- Thứ hai, C5="" là logic 2, kiểm tra ô C5 có giá trị hay không.
- Thứ ba, D5="" là logic 3. Tương tự, kiểm tra ô D5 có giá trị hay không.
Bây giờ, hàm IF trả về kết quả thỏa mãn điều kiện cho trước.
- Khi hàm OR cho TRUE thì nhận "Info Missing" làm Trạng Thái. Ngược lại, nhận "Done" làm Trạng Thái.
- Sau đó, kéo biểu tượng Fill Handle để AutoFill dữ liệu tương ứng cho các ô còn lại E6:E13. Hoặc nhấp đúp vào biểu tượng Fill Handle.
Cuối cùng, bạn nhận được tất cả kết quả.
3. Dùng Hàm SUMIFS Tạo Vòng Lặp FOR Trong Excel
Giả sử, bạn muốn tính tổng hóa đơn cho người nhất định. Trong trường hợp đó, có thể dùng Vòng Lặp FOR dùng công thức Excel. Ở đây, tôi dùng hàm SUMIFS tạo Vòng Lặp FOR trong Excel. Các bước dưới.
Các bước:
- Trước hết, chọn ô khác F7 nơi bạn muốn thấy Trạng Thái.
- Thứ hai, dùng công thức tương ứng trong ô F7.
=SUMIFS($C$5:$C$13,$B$5:$B$13,E7)
- Tiếp theo, nhấn ENTER ra kết quả.
Phân Tích Công Thức
- Ở đây, $C$5:$C$13 là phạm vi dữ liệu SUMIFS sẽ cộng.
- Sau đó, $B$5:$B$13 là phạm vi dữ liệu SUMIFS kiểm tra tiêu chí.
- Cuối cùng, E7 là tiêu chí.
- Vậy, SUMIFS sẽ cộng thanh toán cho giá trị ô E7.
- Sau đó, kéo biểu tượng Fill Handle để AutoFill dữ liệu cho các ô còn lại F8:F10.
Cuối cùng, bạn nhận được kết quả.
Kết Luận
Chúng tôi hy vọng bài viết hữu ích. Ở đây, giải thích 3 ví dụ phù hợp tạo Vòng Lặp FOR trong Excel dùng công thức. Bạn có thể truy cập trang web Exceldemy học thêm nội dung Excel. Hãy để bình luận, góp ý, hoặc thắc mắc ở phần bình luận dưới.
Đọc Thêm
- Cách Dùng Vòng Lặp Do While Trong Excel VBA
- Vòng Lặp For Next Trong VBA Excel (Cách Step Và Exit Loop)