คุณไม่ต้องการใช้ Excel VBA และต้องการสร้าง FOR Loop ใน Excel โดยใช้สูตร? ในบทความนี้ ฉันได้แสดงวิธีสร้าง FOR Loop โดยใช้สูตร
หากคุณรู้วิธีเขียนโค้ดด้วย Excel VBA , คุณมีความสุข🙂 แต่ถ้าคุณไม่เคยเขียนโค้ดใน VBA หรือต้องการให้เวิร์กบุ๊ก Excel ของคุณปราศจาก Excel VBA โค้ด ดังนั้นส่วนใหญ่คุณต้องคิดนอกกรอบเพื่อสร้างวนซ้ำ .
ดาวน์โหลดไฟล์การทำงาน
ดาวน์โหลดไฟล์การทำงานจากลิงค์ด้านล่าง:
3 ตัวอย่างการทำ FOR Loop ใน Excel โดยใช้สูตร
ที่นี่ฉันจะสาธิต 3 ตัวอย่างการทำ FOR Loop ใน Excel โดยใช้สูตร มาดูตัวอย่างแบบละเอียดกัน
1. การใช้ฟังก์ชันรวมเพื่อสร้าง FOR Loop ใน Excel
ตอนนี้ ให้ฉันรู้เบื้องหลังที่กระตุ้นให้ฉันเขียนตัวอย่างนี้
ฉันเป็นผู้เขียนหลักสูตรเกี่ยวกับ Udemy หนึ่งในหลักสูตรคือการจัดรูปแบบตามเงื่อนไขของ Excel ชื่อหลักสูตรคือ: เรียนรู้การจัดรูปแบบตามเงื่อนไขของ Excel พร้อมปัญหาในทางปฏิบัติ 7 ข้อ [หากต้องการเข้าใช้หลักสูตรนี้ฟรี คลิกที่นี่ ].
ในกระดานสนทนาของหลักสูตร นักเรียนถามคำถามตามด้านล่าง [ภาพหน้าจอ]
คำถามที่ถามโดยนักเรียนใน Udemy
อ่านคำถามข้างต้นอย่างละเอียดแล้วพยายามแก้ไข…
ขั้นตอนในการแก้ปัญหาข้างต้น:
ที่นี่ ฉันจะใช้ OR , ออฟเซ็ต , MAX , ขั้นต่ำ และ ROW ทำหน้าที่เป็นสูตร Excel เพื่อสร้าง FOR Loop .
- ประการแรก งานของคุณคือเปิดสมุดงานใหม่และป้อนค่าด้านบนทีละรายการลงในแผ่นงาน [เริ่มจากเซลล์ C5 ].
- ประการที่สอง เลือกทั้งช่วง [จากเซลล์ C5:C34 ].
- ประการที่สาม จากหน้าแรก ribbon>> คลิกที่ การจัดรูปแบบตามเงื่อนไข คำสั่ง
- สุดท้าย เลือก กฎใหม่ จากเมนูแบบเลื่อนลง
ขณะนี้ กฎการจัดรูปแบบใหม่ กล่องโต้ตอบจะปรากฏขึ้น
- ตอนนี้ ใน เลือกประเภทกฎ window>> เลือก ใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ ตัวเลือก
- จากนั้น ใน ค่ารูปแบบที่สูตรนี้เป็นจริง ฟิลด์ พิมพ์สูตรนี้:
=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) - ตอนนี้ เลือกประเภทรูปแบบที่เหมาะสมโดยคลิกที่ รูปแบบ... ในกล่องโต้ตอบ
ในขณะนี้ กล่องโต้ตอบชื่อ จัดรูปแบบเซลล์ จะปรากฏขึ้น
- ตอนนี้ จาก เติม ตัวเลือก>> คุณต้องเลือกสีใดก็ได้ ที่นี่ ฉันเลือก สีฟ้าอ่อน พื้นหลัง. นอกจากนี้ คุณยังสามารถดูตัวอย่าง ทันที ในกรณีนี้ พยายามเลือก แสง any สี. เนื่องจากสีเข้มอาจซ่อนข้อมูลที่ป้อน จากนั้น คุณอาจต้องเปลี่ยน สีแบบอักษร .
- จากนั้นคุณต้องกด ตกลง เพื่อใช้รูปแบบ
- หลังจากนั้นต้องกด ตกลง ใน กฎการจัดรูปแบบใหม่ กล่องโต้ตอบ ที่นี่ คุณสามารถดูตัวอย่างได้ทันทีใน ดูตัวอย่าง กล่อง.
สุดท้ายคุณจะได้รูปแบบตัวเลข
ให้ฉันแสดงอัลกอริทึมในการแก้ปัญหาข้างต้นให้คุณดู:
- ที่นี่ เพื่อให้คุณเข้าใจอัลกอริทึมได้ง่าย ฉันจะอธิบายสิ่งทั้งหมดด้วยเซลล์อ้างอิงสองเซลล์:เซลล์ C11 และC17 . ในเซลล์ C11 และC17 ค่าคือ 10 และ 20 ตามลำดับ (ภาพด้านบน) หากคุณคุ้นเคยกับสูตร Excel คุณจะได้กลิ่น OFFSET ฟังก์ชั่นเป็น OFFSET ฟังก์ชันทำงานร่วมกับจุดอ้างอิง
- ตอนนี้ ลองจินตนาการว่าฉันกำลังหาค่าของช่วงเซลล์ C8:C11 &C11:C14 และ C14:C17 &C17:C20 เคียงข้างกัน [ภาพด้านล่าง] เซลล์อ้างอิงคือ C11 และ C17 และฉันกำลังรับทั้งหมด 7 เซลล์รอบๆ เซลล์อ้างอิง จะได้ภาพสมมติดังนี้ จากส่วนแรก คุณจะพบรูปแบบจากภาพ C9–C12=3 , C10-C13=3 ,มีแบบ. แต่สำหรับส่วนที่สองไม่มีรูปแบบดังกล่าว
- งั้น มาสร้างอัลกอริทึมโดยคำนึงถึงรูปแบบข้างต้นกัน ก่อนสร้างสูตรทั่วไป ผมจะแสดงสูตรสำหรับเซลล์ C11 และ C17 แล้วจะปรับเปลี่ยนสูตรให้เป็นปกติสำหรับทุกคน สำหรับจุดอ้างอิง (เช่น C11 หรือ C17 ) ฉันจะเอาทั้งหมด 7 เซลล์รอบๆ (รวมถึงจุดอ้างอิง) และวางไว้เคียงข้างกันในสูตรที่สร้างอาร์เรย์ จากนั้นฉันจะหาความแตกต่างของอาร์เรย์ถ้าความแตกต่างใด ๆ เท่ากับ 3 ว่าเซลล์อ้างอิงจะเป็น TRUE มีค่า
- ในที่นี้ ฉันทำได้อย่างง่ายดายโดยใช้ OFFSET ทำหน้าที่เป็น OFFSET ฟังก์ชันส่งคืนอาร์เรย์ พูดสำหรับการอ้างอิงเซลล์ C11 ฉันสามารถเขียนสูตรดังนี้:=OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3) . สูตรนี้จะได้อะไรกลับมา? ฟังก์ชันออฟเซ็ตแรกของสูตรจะคืนค่าอาร์เรย์:{10; 11; 12; 15} ฟังก์ชันออฟเซ็ตที่สองจะคืนค่าอาร์เรย์ {5; 8; 9; 10} . และคุณรู้ {10; 11; 12; 15} – {5; 8; 9; 10} ={10-5; 11-8; 12-9; 15-10} ={5; 3; 3; 5} . เมื่ออาร์เรย์นี้ถูกทดสอบอย่างมีเหตุผลด้วย =3 จากนั้น Excel จะคำนวณภายในดังนี้: {5=3; 3=3; 3=3; 5=3} ={ผิด; จริง; จริง; เท็จ . เมื่อหรือ ฟังก์ชันนี้ใช้กับอาร์เรย์นี้:OR({False; True; False; True} คุณจะได้รับ TRUE . ดังนั้นเซลล์ C11 รับค่าจริงตามที่ส่งคืน
- ฉันคิดว่าคุณมีแนวคิดทั้งหมดแล้วว่าอัลกอริธึมนี้ทำงานอย่างไร ตอนนี้มีปัญหา สูตรนี้สามารถทำงานได้จากเซลล์ C8 , เหนือเซลล์ C8 มี 3 เซลล์. แต่สำหรับเซลล์ C5, C6 และ C7 สูตรนี้ใช้ไม่ได้ ดังนั้นควรแก้ไขสูตรสำหรับเซลล์เหล่านี้
- ตอนนี้ สำหรับเซลล์ C5 ถึงC7 เราต้องการให้สูตรไม่คำนึงถึงส่วนบน 3 เซลล์. ตัวอย่างเช่น สำหรับเซลล์ C6 สูตรของเราจะไม่เหมือนกับสูตรสำหรับเซลล์ C11 :=OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3) .
- ที่นี่ สำหรับเซลล์ C5 สูตรจะเป็นดังนี้:OR(OFFSET(C5, 3, 0, 1, 1)-OFFSET(C5, 0, 0, 1, 1)=3) .
- จากนั้น สำหรับเซลล์ C6 สูตรจะเป็นดังนี้:OR(OFFSET(C6, 2, 0, 2, 1)-OFFSET(C6, -1, 0, 2, 1)=3) .
- หลังจากนั้น สำหรับเซลล์ C7 สูตรจะเป็นดังนี้:OR(OFFSET(C7, 1, 0, 3, 1)-OFFSET(C7, -2, 0, 3, 1)=3) .
- อีกครั้ง สำหรับเซลล์ C8 สูตรจะเป็นดังนี้:OR(OFFSET(C8, 0, 0, 4, 1)-OFFSET(C8,-3, 0, 4, 1)=3); [นี่คือสูตรทั่วไป].
- จากนั้น สำหรับเซลล์ C9 สูตรจะเป็นดังนี้:OR(OFFSET(C9, 0, 0, 4, 1)-OFFSET(C9,-3, 0, 4, 1)=3); [นี่คือสูตรทั่วไป].
- สุดท้าย คุณพบรูปแบบบางส่วนจากสูตรข้างต้นหรือไม่ OFFSET . ตัวแรก อาร์กิวเมนต์แถวของฟังก์ชันลดลงจาก 3 ถึง 0; อาร์กิวเมนต์ความสูงเพิ่มขึ้นจาก 1 ถึง 4 . OFFSETตัวที่สอง อาร์กิวเมนต์แถวของฟังก์ชันลดลงจาก 0 ถึง -3 และอาร์กิวเมนต์ความสูงเพิ่มขึ้นจาก 1 ถึง 4 .
- อย่างแรก OFFSET อาร์กิวเมนต์แถวของฟังก์ชันจะถูกแก้ไขดังนี้:MAX(ROW(C$5)-ROW(C5)+3,0)
- ประการที่สอง OFFSET . ที่สอง อาร์กิวเมนต์แถวของฟังก์ชันจะถูกแก้ไขดังนี้:MAX(ROW(C$5)-ROW(C5),-3)
- ประการที่สาม OFFSETครั้งแรก อาร์กิวเมนต์ความสูงของฟังก์ชันจะถูกแก้ไขดังนี้:MIN(ROW(C5)-ROW(C$5)+1,4)
- ประการที่สี่ OFFSET ที่สอง อาร์กิวเมนต์ความสูงของฟังก์ชันจะถูกแก้ไขดังนี้:MIN(ROW(C5)-ROW(C$5)+1,4)
- ตอนนี้ พยายามทำความเข้าใจการแก้ไขข้างต้น สิ่งเหล่านี้ไม่ยากที่จะเข้าใจ ทั้งหมดนี้สี่ การแก้ไขกำลังทำงานเป็น FOR LOOP ของ Excel VBA แต่ฉันได้สร้างมันขึ้นมาด้วยสูตร Excel
- คุณจะได้ทราบวิธีการทำงานของสูตรทั่วไปสำหรับเซลล์จาก C5:C34 .
ฉันกำลังพูดถึงการวนรอบในสเปรดชีต Excel นี่เป็นตัวอย่างที่สมบูรณ์แบบของการวนซ้ำใน Excel ที่นี่ทุกครั้งที่สูตรใช้เวลา 7 เซลล์และทำงานในเซลล์เพื่อหาค่าเฉพาะ
2. การใช้ฟังก์ชัน IF &OR เพื่อสร้าง FOR Loop ใน Excel
ในตัวอย่างนี้ สมมติว่าคุณต้องการตรวจสอบว่าเซลล์มีค่าใดๆ หรือไม่ นอกจากนี้ ด้วย Excel VBA FOR Loop คุณสามารถทำได้ง่ายๆ แต่ที่นี่ ฉันจะทำโดยใช้สูตร Excel
ตอนนี้คุณสามารถใช้ the ถ้า และ หรือ ทำหน้าที่เป็นสูตร Excel เพื่อสร้าง FOR Loop . นอกจากนี้ คุณสามารถแก้ไขสูตรนี้ตามความต้องการของคุณ มีขั้นตอนดังนี้
ขั้นตอน:
- ขั้นแรก คุณต้องเลือกเซลล์อื่น E5 ที่คุณต้องการดูสถานะ .
- ประการที่สอง คุณควรใช้สูตรที่สอดคล้องกันใน E5 เซลล์
=IF(OR(B5="",C5="",D5=""),"Info Missing","Done") - จากนั้น กด ENTER เพื่อให้ได้ผลลัพธ์
รายละเอียดสูตร
ที่นี่หรือ ฟังก์ชันจะส่งกลับ TRUE ถ้าตรรกะใด ๆ ที่กำหนดกลายเป็น จริง .
- ประการแรก B5="” เป็น ที่ 1 ตรรกะซึ่งจะตรวจสอบว่าเซลล์ B5 มีค่าใด ๆ หรือไม่
- ประการที่สอง C5="” เป็น ที่ 2 ตรรกะซึ่งจะตรวจสอบว่าเซลล์ C5 มีค่าใด ๆ หรือไม่
- ประการที่สาม D5="” เป็นที่3 ตรรกะ. ในทำนองเดียวกันซึ่งจะตรวจสอบว่าเซลล์ D5 มีค่าใด ๆ หรือไม่
ตอนนี้ IF ฟังก์ชันส่งคืนผลลัพธ์ซึ่งจะเป็นไปตามเงื่อนไขที่กำหนด
- เมื่อ OR ฟังก์ชั่นให้ TRUE จากนั้นคุณจะได้รับ “ข้อมูลหายไป ” เป็น สถานะ . มิฉะนั้น คุณจะได้รับ “เสร็จสิ้น ” เป็น สถานะ .
- หลังจากนั้น คุณต้องลาก Fill Handle ไอคอน ป้อนอัตโนมัติ ข้อมูลที่เกี่ยวข้องในส่วนที่เหลือของเซลล์ E6:E13 . หรือดับเบิลคลิกที่ Fill Handle ไอคอน
สุดท้ายคุณจะได้ผลลัพธ์ทั้งหมด
3. การใช้ฟังก์ชัน SUMIFS เพื่อสร้าง FOR ลูปใน Excel
สมมติว่าคุณต้องการทำยอดรวมสำหรับบุคคลใดบุคคลหนึ่ง ในกรณีนั้น คุณสามารถใช้ FOR Loop โดยใช้สูตร Excel ที่นี่ ฉันจะใช้ ฟังก์ชัน SUMIFS เพื่อสร้าง FOR Loop ใน Excel มีขั้นตอนดังนี้
ขั้นตอน:
- ขั้นแรก คุณต้องเลือกเซลล์อื่น F7 ที่คุณต้องการดูสถานะ .
- ประการที่สอง คุณควรใช้สูตรที่สอดคล้องกันใน F7 เซลล์
=SUMIFS($C$5:$C$13,$B$5:$B$13,E7) - จากนั้น กด ENTER เพื่อให้ได้ผลลัพธ์
รายละเอียดสูตร
- ที่นี่ $C$5:$C$13 คือช่วงข้อมูลที่ SUMIFS ฟังก์ชันจะทำการหาผลรวม
- จากนั้น $B$5:$B$13 คือช่วงข้อมูลที่ SUMIFS ฟังก์ชันจะตรวจสอบเกณฑ์ที่กำหนด
- สุดท้าย E7 เป็นเกณฑ์
- ดังนั้น SUMIFS ฟังก์ชั่นจะเพิ่มการชำระเงินสำหรับ E7 ค่าของเซลล์
- หลังจากนั้น คุณต้องลาก Fill Handle ไอคอนเพื่อป้อนข้อมูลที่เกี่ยวข้องในส่วนที่เหลือของเซลล์โดยอัตโนมัติ F8:F10 .
สุดท้ายคุณจะได้ผลลัพธ์
บทสรุป
เราหวังว่าคุณจะพบว่าบทความนี้มีประโยชน์ เราได้อธิบายไปแล้ว 3 ตัวอย่างที่เหมาะสมในการทำ FOR Loop ใน Excel โดยใช้สูตร คุณสามารถเยี่ยมชมเว็บไซต์ของเรา Exceldemy เพื่อเรียนรู้เพิ่มเติมเกี่ยวกับเนื้อหาที่เกี่ยวข้องกับ Excel โปรดแสดงความคิดเห็น ข้อเสนอแนะ หรือข้อสงสัยหากมีในส่วนความคิดเห็นด้านล่าง
อ่านเพิ่มเติม
- วิธีใช้ Do while Loop ใน Excel VBA
- สำหรับวนรอบถัดไปใน VBA Excel (วิธีการก้าวและออกจากลูป)
