สร้างลูป FOR ใน Excel ด้วยสูตร: 3 ตัวอย่างปฏิบัติจริง

คุณไม่ต้องการใช้ Excel VBA และอยากสร้าง FOR Loop ใน Excel โดยใช้ สูตร (Formula)? ในบทความนี้ แสดงวิธีสร้าง FOR Loop โดยใช้สูตร

ถ้าคุณรู้จักเขียนโค้ดกับ Excel VBA คุณโชคดี 🙂 แต่ถ้าคุณไม่เคยเขียนโค้ดใน VBA หรือต้องการให้สมุดงาน Excel ไม่มีโค้ด VBA มักจะต้องคิดนอกกรอบเพื่อสร้าง ลูป (loop) ให้เรียบง่าย

ดาวน์โหลดไฟล์ทำงาน (Download Working File)

ดาวน์โหลดไฟล์ทำงานจากลิงก์ด้านล่าง:

3 ตัวอย่างการสร้าง FOR Loop ใน Excel ด้วยสูตร

ที่นี่จะสาธิต 3 ตัวอย่างในการสร้าง FOR Loop ใน Excel โดยใช้สูตร มาดูรายละเอียด

1. ใช้ฟังก์ชันร่วมกันเพื่อสร้าง FOR Loop ใน Excel (Applying Combined Functions to Make FOR Loop in Excel)

ตอนนี้ ให้รู้พื้นหลังที่ทำให้ผมเขียนตัวอย่างนี้

ผมเป็นผู้เขียนหลายคอร์สบน Udemy หนึ่งในคอร์สคือ Excel Conditional Formatting ชื่อคอร์ส: Learn Excel Conditional Formatting with 7 Practical Problems [คลิกที่นี่เพื่อเข้าถึงฟรี]

บนกระดานถาม-ตอบของคอร์ส นักเรียนคนหนึ่งถามคำถามดังนี้ [ภาพหน้าจอ]

คำถามจากนักเรียนใน Udemy

อ่านคำถามข้างต้นอย่างละเอียดและลองแก้ไขดู…

ขั้นตอนการแก้ปัญหาข้างต้น:

ที่นี่จะใช้ฟังก์ชัน OR, OFFSET, MAX, MIN, และ ROW เป็นสูตร Excel เพื่อสร้าง FOR Loop

• ขั้นแรก เปิดสมุดงานใหม่ และใส่ค่าด้านบนทีละค่า vào เวิร์กชีต [เริ่มจากเซลล์ C5]
• ขั้นที่สอง เลือกช่วงทั้งหมด [จากเซลล์ C5:C34]
• ขั้นที่สาม จากริบบอน Home >> คลิกคำสั่ง Conditional Formatting
• ขั้นสุดท้าย เลือกตัวเลือก New Rule จาก drop-down

ในขณะนี้ กล่องโต้ตอบ New Formatting Rule จะปรากฏ

• ในหน้าต่าง Select a Rule Type >> เลือก Use a formula to determine which cells to format
• ในช่อง Format values where this formula is true พิมพ์สูตรนี้:

=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)

• เลือกประเภทรูปแบบที่เหมาะสมโดยคลิกปุ่ม Format… ในกล่องโต้ตอบ

จะมีกล่องโต้ตอบ Format Cells ปรากฏ

• จากตัวเลือก Fill >> เลือกสีใดสีหนึ่ง ที่นี่เลือกพื้นหลัง Light Blue ยังเห็น ตัวอย่าง (sample) ทันที ในกรณีนี้ ลองเลือกสี อ่อน (light) เพราะสีเข้มอาจปิดบังข้อมูลที่ใส่ไว้ แล้วอาจต้องเปลี่ยน Font Color
• กด OK เพื่อใช้รูปแบบ

• กด OK บนกล่องโต้ตอบ New Formatting Rule ที่นี่เห็นตัวอย่างทันทีในกล่อง Preview

• สุดท้าย จะได้ตัวเลขที่จัดรูปแบบแล้ว

มาแสดงอัลกอริทึมในการแก้ปัญหาข้างต้น:

• เพื่อให้เข้าใจอัลกอริทึมง่ายขึ้น จะอธิบายทั้งหมดด้วยเซลล์อ้างอิงสองเซลล์: เซลล์ C11 และ C17 ในเซลล์ C11 และ C17 ค่าคือ 10 และ 20 ตามลำดับ (ภาพด้านบน) หากคุณคุ้นเคยกับสูตร Excel คุณจะ聞到กลิ่น (smell) ฟังก์ชัน 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) สูตรนี้จะคืนค่าอะไร? OFFSET แรกคืนอาร์เรย์: {10; 11; 12; 15} OFFSET ที่สองคืนอาร์เรย์ {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} = {False; True; True; False} เมื่อใช้ฟังก์ชัน OR บนอาร์เรย์นี้: OR({False; True; False; True} ได้ TRUE ดังนั้นเซลล์ C11 ได้ค่า TRUE
• คิดว่าคุณเข้าใจแนวคิดแล้วว่าอัลกอริทึมนี้ทำงานอย่างไร ตอนนี้มีปัญหา สูตรนี้ทำงานได้จากเซลล์ 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) [สูตรทั่วไป]
• สุดท้าย คุณพบรูปแบบจากสูตรข้างต้นไหม? อาร์กิวเมนต์ rows ของ OFFSET แรกลดจาก 3 เป็น 0 อาร์กิวเมนต์ height เพิ่มจาก 1 เป็น 4 อาร์กิวเมนต์ rows ของ OFFSET ที่สองลดจาก 0 เป็น -3 และ height เพิ่มจาก 1 เป็น 4
• ขั้นแรก อาร์กิวเมนต์ rows ของ OFFSET แรกปรับเป็น: MAX(ROW(C$5)-ROW(C5)+3,0)
• ขั้นที่สอง อาร์กิวเมนต์ rows ของ OFFSET ที่สองปรับเป็น: MAX(ROW(C$5)-ROW(C5),-3)
• ขั้นที่สาม อาร์กิวเมนต์ height ของ OFFSET แรกปรับเป็น: MIN(ROW(C5)-ROW(C$5)+1,4)
• ขั้นที่สี่ อาร์กิวเมนต์ height ของ OFFSET ที่สองปรับเป็น: MIN(ROW(C5)-ROW(C$5)+1,4)
• ลองเข้าใจการปรับเปลี่ยนข้างต้น ไมยากเลย ทั้ง สี่ การปรับเปลี่ยนนี้ทำงานเป็น FOR LOOP ของ Excel VBA แต่นี่สร้างด้วยสูตร Excel
• ดังนั้น คุณได้วิธีการที่สูตรทั่วไปทำงานสำหรับเซลล์ C5:C34

ดังนั้น การทำลูป (Looping) ในสเปรดชีต Excel นี่คือตัวอย่างที่สมบูรณ์แบบของการทำลูปใน Excel ที่นี่ ทุกครั้งที่สูตรนำ 7 เซลล์ ไปทำงานเพื่อหาค่าเฉพาะ

2. ใช้ฟังก์ชัน IF และ OR สร้าง FOR Loop ใน Excel (Use of IF & OR Functions to Create FOR Loop in Excel)

ในตัวอย่างนี้ สมมติว่าคุณต้องการตรวจสอบว่าเซลล์มีค่าหรือไม่ นอกจากนี้ ด้วย Excel VBA FOR Loop ทำได้ง่าย แต่ที่นี่จะทำด้วยสูตร Excel

ตอนนี้ คุณสามารถใช้ ฟังก์ชัน IF และ OR เป็นสูตร Excel เพื่อสร้าง FOR Loop อีกทั้ง คุณสามารถปรับสูตรนี้ตามต้องการ ขั้นตอนด้านล่าง

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ที่ต่างกัน E5 ที่ต้องการดู สถานะ (Status)
• ขั้นที่สอง ใช้สูตรที่สอดคล้องในเซลล์ E5

=IF(OR(B5="",C5="",D5=""),"Info Missing","Done")

• กด ENTER เพื่อได้ผลลัพธ์

การแยกส่วนสูตร (Formula Breakdown)

ที่นี่ ฟังก์ชัน OR จะคืนค่า TRUE หากตรรกะใดๆ ของที่กำหนดกลายเป็น TRUE

• ขั้นแรก B5=”” เป็น ตรรกะที่ 1 ตรวจสอบว่าเซลล์ B5 มีค่าหรือไม่
• ขั้นที่สอง C5=”” เป็น ตรรกะที่ 2 ตรวจสอบว่าเซลล์ C5 มีค่าหรือไม่
• ขั้นที่สาม D5=”” เป็น ตรรกะที่ 3 ตรวจสอบว่าเซลล์ D5 มีค่าหรือไม่

ฟังก์ชัน IF จะคืนค่าผลลัพธ์ที่ตรงตามเงื่อนไขที่กำหนด

• เมื่อฟังก์ชัน OR ให้ TRUE จะได้ "Info Missing" เป็น Status มิฉะนั้น จะได้ "Done" เป็น Status

• ลากไอคอน Fill Handle เพื่อ AutoFill ข้อมูลที่สอดคล้องในเซลล์ที่เหลือ E6:E13 หรือดับเบิลคลิกที่ไอคอน Fill Handle

สุดท้าย จะได้ผลลัพธ์ทั้งหมด

3. ใช้ฟังก์ชัน SUMIFS สร้าง FOR Loop ใน Excel (Employing SUMIFS Function to Create FOR Loop in Excel)

สมมติว่าคุณต้องการคำนวณยอดบิลรวมสำหรับบุคคลเฉพาะ ในกรณีนั้น อาจใช้ FOR Loop ด้วยสูตร Excel ที่นี่จะใช้ ฟังก์ชัน SUMIFS สร้าง FOR Loop ใน Excel ขั้นตอนด้านล่าง

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ที่ต่างกัน F7 ที่ต้องการดู สถานะ (Status)
• ขั้นที่สอง ใช้สูตรที่สอดคล้องในเซลล์ F7

=SUMIFS($C$5:$C$13,$B$5:$B$13,E7)

• กด ENTER เพื่อได้ผลลัพธ์

การแยกส่วนสูตร (Formula Breakdown)

• $C$5:$C$13 คือช่วงข้อมูลที่ SUMIFS จะทำการรวม
• $B$5:$B$13 คือช่วงข้อมูลที่ SUMIFS จะตรวจสอบเกณฑ์ที่กำหนด
• E7 คือเกณฑ์ (criteria)
• ดังนั้น SUMIFS จะรวมยอดชำระสำหรับค่าในเซลล์ E7

• ลากไอคอน Fill Handle เพื่อ AutoFill ข้อมูลในเซลล์ที่เหลือ F8:F10

สุดท้าย จะได้ผลลัพธ์

สรุป

หวังว่าบทความนี้จะมีประโยชน์ ที่นี่อธิบาย 3 ตัวอย่างที่เหมาะสมในการสร้าง FOR Loop ใน Excel โดยใช้สูตร คุณสามารถเยี่ยมชมเว็บไซต์ Exceldemy เพื่อเรียนรู้เนื้อหาเกี่ยวกับ Excel เพิ่มเติม โปรดแสดงความคิดเห็น ข้อเสนอแนะ หรือคำถามในส่วนความคิดเห็นด้านล่าง

อ่านเพิ่มเติม

• How to Use the Do While Loop in Excel VBA
• For Next Loop in VBA Excel (How to Step and Exit Loop)