Bạn không muốn sử dụng Excel VBA và muốn tạo Vòng lặp FOR trong Excel bằng cách sử dụng Công thức? Trong bài viết này, tôi đã chỉ ra cách bạn có thể tạo FOR Loop sử dụng công thức.
Nếu bạn biết cách viết mã bằng Excel VBA , bạn thật may mắn 🙂. Tuy nhiên, nếu bạn chưa bao giờ viết mã trong VBA hoặc muốn giữ cho sổ làm việc Excel của bạn không có VBA Excel mã, thì hầu hết thời gian bạn phải nghĩ ra để tạo một vòng lặp đơn giản .
Tải xuống tệp làm việc
Tải xuống tệp làm việc từ liên kết bên dưới:
3 Ví dụ để Tạo Vòng lặp FOR trong Excel Sử dụng Công thức
Ở đây, tôi sẽ chứng minh 3 ví dụ để tạo FOR Loop trong Excel bằng cách sử dụng một công thức. Hãy xem các ví dụ chi tiết.
1. Áp dụng các hàm kết hợp để tạo vòng lặp FOR trong Excel
Bây giờ, hãy cho tôi biết bối cảnh khuyến khích tôi viết ví dụ này.
Tôi là tác giả của một số khóa học trên Udemy. Một trong những khóa học về Định dạng có điều kiện trong Excel. Tiêu đề khóa học là: Học Định dạng có Điều kiện trong Excel với 7 Vấn đề Thực tế. [ để có quyền truy cập miễn phí vào khóa học này, hãy nhấp vào đây ].
Trong bảng thảo luận của khóa học, một sinh viên đã hỏi tôi một câu hỏi như bên dưới [ảnh chụp màn hình].
Câu hỏi của một sinh viên ở Udemy.
Đọc kỹ câu hỏi ở trên và cố gắng giải quyết nó…
Các bước giải quyết vấn đề trên:
Ở đây, tôi sẽ sử dụng HOẶC , OFFSET , TỐI ĐA , PHÚT và ROW các hàm dưới dạng Công thức Excel để tạo Vòng lặp FOR .
- Đầu tiên, công việc của bạn là mở một sổ làm việc mới và nhập lần lượt các giá trị trên vào trang tính [bắt đầu từ ô C5 ].
- Thứ hai, chọn toàn bộ phạm vi [từ ô C5:C34 ].
- Thứ ba, từ Trang chủ ruy-băng>> nhấp vào Định dạng có điều kiện lệnh.
- Cuối cùng, chọn Quy tắc mới từ menu thả xuống.
Tại thời điểm này, Quy tắc định dạng mới hộp thoại xuất hiện.
- Bây giờ, trong phần Chọn loại quy tắc window>> chọn Sử dụng công thức để xác định ô cần định dạng tùy chọn.
- Sau đó, trong Định dạng giá trị mà công thức này đúng trường, nhập 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ờ, hãy chọn loại định dạng thích hợp bằng cách nhấp vào Định dạng… trong hộp thoại.
Tại thời điểm này, một hộp thoại có tên Định dạng ô sẽ xuất hiện.
- Bây giờ, từ Điền tùy chọn>> bạn phải chọn bất kỳ màu nào. Ở đây, tôi đã chọn Màu xanh lam nhạt lai lịch. Ngoài ra, bạn có thể xem mẫu ngay lập tức. Trong trường hợp này, hãy thử chọn bất kỳ đèn nào màu sắc. Vì màu tối có thể làm ẩn dữ liệu đã nhập. Sau đó, bạn có thể cần thay đổi Màu phông chữ .
- Sau đó, bạn phải nhấn OK để áp dụng sự hình thành.
- Sau đó, bạn phải nhấn OK trên Quy tắc định dạng mới hộp thoại. Tại đây, bạn có thể xem mẫu ngay lập tức trong Xem trước hộp.
Cuối cùng, bạn sẽ nhận được các số được định dạng.
Hãy để tôi chỉ cho bạn thuật toán để giải quyết vấn đề trên:
- Ở đây, để giúp bạn dễ hiểu thuật toán, tôi sẽ giải thích toàn bộ điều này bằng hai ô tham chiếu:ô C11 và C17 . Trong ô C11 và C17 , các giá trị là 10 và 20 tương ứng (hình trên). Nếu bạn đã quen với các công thức Excel, thì bạn có thể ngửi thấy OFFSET chức năng, dưới dạng OFFSET chức năng hoạt động với các điểm tham chiếu.
- Bây giờ, hãy tưởng tượng tôi đang lấy các giá trị của phạm vi ô C8:C11 & C11:C14 và C14:C17 & C17:C20 cạnh nhau [hình ảnh bên dưới]. Các ô tham chiếu là C11 và C17 và tôi đang lấy tổng cộng 7 các ô xung quanh ô tham chiếu. Bạn sẽ nhận được một bức tranh tưởng tượng như sau. Từ phần đầu tiên, bạn có thể tìm thấy một mẫu từ hình ảnh. C9 – C12 =3 , C10-C13 =3 , có một mô hình. Nhưng đối với phần thứ hai, không có mô hình như vậy.
- Vì vậy, chúng ta hãy xây dựng thuật toán với việc lưu ý đến mô hình trên. Trước khi xây dựng công thức chung, tôi sẽ chỉ ra công thức sẽ là gì cho các ô C11 và C17 và sau đó sẽ sửa đổi công thức để làm cho công thức chung cho tất cả mọi người. Đối với điểm tham chiếu (như C11 hoặc C17 ), Tôi sẽ lấy tổng cộng là 7 các ô xung quanh nó (bao gồm cả điểm tham chiếu) và đặt chúng cạnh nhau trong công thức tạo mảng. Sau đó, tôi sẽ tìm ra sự khác biệt của các mảng nếu bất kỳ sự khác biệt nào bằng 3 rằng ô tham chiếu sẽ là TRUE có giá trị.
- Tại đây, tôi có thể làm điều đó dễ dàng bằng cách sử dụng OFFSET hoạt động như OFFSET hàm trả về một mảng. Nói cho tham chiếu ô C11 , Tôi có thể viết công thức như sau: =OR (OFFSET (C11, 0, 0, 4, 1) -OFFSET (C11, -3, 0, 4, 1) =3) . Công thức này sẽ trả về cái gì? Hàm bù đầu tiên của công thức sẽ trả về mảng: {10; 11; 12; 15} , hàm bù thứ hai sẽ trả về mảng {5; số 8; Số 9; 10} . Và bạn biết {10; 11; 12; 15} - {5; số 8; Số 9; 10} ={10-5; 11-8; 12-9; 15-10} ={5; 3; 3; 5} . Khi mảng này được kiểm tra logic với =3 thì Excel sẽ tính toán nội bộ như sau: {5 =3; 3 =3; 3 =3; 5 =3} ={Sai; ĐÚNG VẬY; ĐÚNG VẬY; Sai} . Khi HOẶC hàm được áp dụng trên mảng này: OR ({False; True; False; True} , bạn nhận được TRUE . Vì vậy, ô C11 nhận các giá trị đúng như được trả về.
- Vì vậy, tôi nghĩ bạn đã có toàn bộ khái niệm về cách thức hoạt động của thuật toán này. Bây giờ có một vấn đề. Công thức này có thể hoạt động từ ô C8 , phía trên ô C8 , có 3 tế bào. Nhưng đối với các ô C5, C6, và C7 công thức này không thể hoạt động. Vì vậy, công thức nên được sửa đổi cho các ô này.
- Bây giờ, đối với các ô C5 đến C7 , chúng tôi muốn rằng công thức sẽ không tính đến 3 trên tế bào. Ví dụ:đối với ô C6 , công thức của chúng tôi sẽ không giống công thức cho ô C11 : =HOẶC (OFFSET (C11, 0, 0, 4, 1) -OFFSET (C11, -3, 0, 4, 1) =3) .
- Đây, cho ô C5 , công thức sẽ như sau: OR (OFFSET (C5, 3, 0, 1, 1) -OFFSET (C5, 0, 0, 1, 1) =3) .
- Sau đó, đối với ô C6 , công thức sẽ như sau: OR (OFFSET (C6, 2, 0, 2, 1) -OFFSET (C6, -1, 0, 2, 1) =3) .
- Sau đó, đối với ô C7 , công thức sẽ như sau: OR (OFFSET (C7, 1, 0, 3, 1) -OFFSET (C7, -2, 0, 3, 1) =3) .
- Một lần nữa, đối với ô C8 , công thức sẽ như sau: OR (OFFSET (C8, 0, 0, 4, 1) -OFFSET (C8, -3, 0, 4, 1) =3) ; [đây là công thức chung].
- Sau đó, đối với ô C9 , công thức sẽ như sau: OR (OFFSET (C9, 0, 0, 4, 1) -OFFSET (C9, -3, 0, 4, 1) =3) ; [đây là công thức chung].
- Cuối cùng, bạn có tìm thấy một số mẫu từ các công thức trên không? OFFSET đầu tiên đối số hàng của hàm đã giảm từ 3 đến 0 ; đối số chiều cao đã tăng từ 1 đến 4 . OFFSET thứ hai đối số hàng của hàm đã giảm từ 0 thành -3 và đối số chiều cao đã tăng từ 1 đến 4 .
- Đầu tiên, OFFSET đầu tiên đối số hàng của hàm sẽ được sửa đổi như sau: MAX (ROW (C $ 5) -ROW (C5) +3,0)
- Thứ hai, OFFSET thứ hai đối số hàng của hàm sẽ được sửa đổi như sau: MAX (ROW (C $ 5) -ROW (C5), - 3)
- Thứ ba, OFFSET đầu tiên đối số chiều cao của hàm sẽ được sửa đổi như sau: MIN (ROW (C5) -ROW (C $ 5) +1,4)
- Thứ tư, OFFSET thứ hai đối số chiều cao của hàm sẽ được sửa đổi như sau: MIN (ROW (C5) -ROW (C $ 5) +1,4)
- Bây giờ, hãy cố gắng hiểu sửa đổi ở trên. Đây không phải là khó hiểu. Tất cả bốn này sửa đổi đang hoạt động như FOR LOOP của Excel VBA nhưng tôi đã xây dựng chúng bằng Công thức Excel.
- Vì vậy, bạn đã biết cách hoạt động của công thức chung cho các ô từ C5:C34 .
Vì vậy, tôi đã nói về Vòng lặp trong bảng tính Excel. Vì vậy, đây là một ví dụ hoàn hảo về lặp trong Excel. Ở đây, mỗi khi công thức chiếm 7 và hoạt động trên các ô để tìm ra một giá trị cụ thể.
2. Sử dụng các 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 xem các ô có chứa bất kỳ giá trị nào hay không. Hơn nữa, với Excel VBA FOR Loop, bạn có thể làm điều này một cách dễ dàng nhưng ở đây, tôi sẽ làm điều đó bằng công thức Excel.
Bây giờ, bạn có thể sử dụng the NẾU và HOẶC hàm dưới dạng Công thức Excel để tạo Vòng lặp FOR . Hơn nữa, bạn có thể sửa đổi công thức này theo sở thích của mình. Các bước được đưa ra bên dưới.
Các bước:
- Trước tiên, bạn phải chọn một ô khác E5 nơi bạn muốn xem Trạng thái .
- Thứ hai, bạn nên sử dụng công thức tương ứng trong E5 ô.
=IF(OR(B5="",C5="",D5=""),"Info Missing","Done") - Sau đó, nhấn ENTER để nhận được kết quả.
Phân tích Công thức
Đây, HOẶC hàm sẽ trả về TRUE nếu bất kỳ logic nào trong số các logic đã cho trở thành TRUE .
- Đầu tiên, B5 =”” là đầu tiên logic, sẽ kiểm tra xem ô B5 có chứa bất kỳ giá trị nào hay không.
- Thứ hai, C5 =”” là thứ 2 logic, sẽ kiểm tra xem ô C5 có chứa bất kỳ giá trị nào hay không.
- Thứ ba, D5 =”” là thứ 3 Hợp lý. Tương tự, điều này sẽ kiểm tra xem ô D5 có chứa bất kỳ giá trị nào hay không.
Bây giờ, IF hàm trả về kết quả sẽ đáp ứng một điều kiện nhất định.
- Khi HOẶC hàm đưa ra TRUE thì bạn sẽ nhận được “ Thiếu thông tin ”Dưới dạng Trạng thái . Nếu không, bạn sẽ nhận được “ Hoàn tất ”Là Trạng thái .
- Sau đó, bạn phải kéo Xử lý điền biểu tượng để Tự động điền dữ liệu tương ứng trong phần còn lại của các ô E6:E13 . Hoặc bạn có thể nhấp đúp vào Xử lý điền biểu tượng.
Cuối cùng, bạn sẽ nhận được tất cả các kết quả.
3. Sử dụng hàm SUMIFS để tạo vòng lặp FOR trong Excel
Giả sử, bạn muốn lập tổng hóa đơn cho một người nào đó. Trong trường hợp đó, bạn có thể sử dụng FOR Loop sử dụng công thức Excel. Ở đây, tôi sẽ sử dụng hàm SUMIFS để tạo Vòng lặp FOR trong Excel. Các bước được đưa ra bên dưới.
Các bước:
- Trước tiên, bạn phải chọn một ô khác F7 nơi bạn muốn xem Trạng thái .
- Thứ hai, bạn nên sử dụng công thức tương ứng trong F7 ô.
=SUMIFS($C$5:$C$13,$B$5:$B$13,E7) - Sau đó, nhấn ENTER để nhận được kết quả.
Phân tích Công thức
- Đây, $ C $ 5:$ C $ 13 là phạm vi dữ liệu mà từ đó SUMIFS hàm sẽ thực hiện phần tổng kết.
- Sau đó, $ B $ 5:$ B $ 13 là phạm vi dữ liệu từ nơi SUMIFS chức năng sẽ kiểm tra các tiêu chí đã cho
- Cuối cùng, E7 là tiêu chí.
- Vì vậy, SUMIFS chức năng sẽ thêm các khoản thanh toán cho E7 giá trị ô.
- Sau đó, bạn phải kéo Xử lý điền biểu tượng để Tự động điền dữ liệu tương ứng vào phần còn lại của các ô F8:F10 .
Cuối cùng, bạn sẽ nhận được kết quả.
Kết luận
Chúng tôi hy vọng bạn thấy bài viết này hữu ích. Ở đây, chúng tôi đã giải thích 3 các ví dụ phù hợp để tạo FOR Loop trong Excel bằng cách sử dụng công thức. Bạn có thể truy cập trang web của chúng tôi Exceldemy để tìm hiểu thêm nội dung liên quan đến Excel. Vui lòng để lại nhận xét, đề xuất hoặc truy vấn nếu bạn có bất kỳ câu hỏi nào trong phần bình luận bên dưới.
Đọc thêm
- Cách sử dụng Vòng lặp Do While trong Excel VBA
- Đối với Vòng lặp Tiếp theo trong VBA Excel (Cách Bước và Thoát khỏi Vòng lặp)