เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Excel Array FormulaHighlights : บทความแนะนำ

Excel Array Formula ตอนที่ 1 : พื้นฐาน

Excel Array Formula ตอนที่ 1 : พื้นฐาน 1

ผมเคยเขียนบทความเกี่ยวกับ Array Formula เมื่อหลายปีมาแล้ว และนี่ก็คงถึงเวลาที่ต้อง Refresh เนื้อหาใหม่ซักหน่อย เพราะใน Excel ยุคใหม่อย่าง Excel 365 ที่รองรับ Dynamic Array นั้นทำให้การเขียนสูตรแบบ Array ง่ายขึ้นและเจ๋งขึ้นมากๆ เลย

Array Formula คืออะไร?

สำหรับคนที่ยังไม่รู้จักว่า Array Formula คืออะไร ผมก็คงจะต้องอธิบายก่อนแหละ ว่ามันคือวิธีการเขียนสูตรที่มีลักษณะประมาณนี้

  • สูตรที่ให้ผลลัพธ์ออกมาหลายค่า
  • สูตรที่เราใส่ Input แบบหลายค่าลงไปใน Argument ของ Function ที่ปกติแล้วเราจะใส่แบบค่าเดียว

ซึ่งข้อดีของมันคือการที่เราเขียนสูตรคำนวณที่ซับซ้อนได้ในช่องเดียว แบบไม่ต้องทดออกมาใน Cell อื่น แบบตอนที่เขียนสูตรตามปกติ

ฟังแล้วก็ยังงงๆ เนอะ ไปดูตัวอย่างดีกว่า…

วิธีการเขียนสูตรแบบ Array

ถ้าเป็น Excel 365 ที่รองรับ Dynamic Array

หากผมเขียนสูตรแบบนี้ ในช่อง E7 แล้วกด Enter

=B2:B5-C2:C5

จะสามารถแสดงผลลัพธ์แบบพุ่งพรวดออกมาทีเดียว 4 ช่องได้เลย โดยที่ ผลลัพธ์แต่ละตัวเกิดจาก สมาชิกแต่ละตัวของ Range แรก ลบด้วย สมาชิกแต่ละตัวของ Range ที่สอง ทำให้มีผลลัพธ์ออกมา 4 ตัว นั่นเอง และใน Excel 365 มันสามารถเอาผลลัพธ์ 4 ตัวโปรยลงไปแสดงในหลายๆ ช่องได้ (เรียกว่า Spill โดยที่สูตรจริงๆ อยู่ในช่อง E7 ช่องเดียวนะ ช่องอื่นไม่ได้มีสูตรอยู่จริง)

Excel Array Formula ตอนที่ 1 : พื้นฐาน 2

แต่ถ้าเป็น Excel version เก่า

จะต้องลากคลุมพื้นที่ E7:E10 ก่อน แล้วเขียนสูตร

=B2:B5-C2:C5

แล้วค่อยกด Ctrl+Shift+Enter ถึงจะได้ผลเหมือนกัน ซึ่งจะเห็นใน Formula Bar มีเครื่องหมายปีกกา { } มาครอบสูตรโดยอัตโนมัติ

ถ้าเผลอกด Enter ธรรมดาก็จะขึ้น Error แบบ #VALUE! ทันที เพราะว่า Excel version เก่าไม่สามารถแสดงผลลัพธ์หลายค่าได้จากการใส่สูตรในช่องเดียว

ซึ่งจะเห็นว่าใน Excel version 365 นั้นง่ายกว่าเยอะมากๆ เลย

เราสามารถซ้อนสูตรเพื่อรวบคำตอบให้เหลือค่าเดียวก็ได้

Array ที่ให้ผลลัพธ์หลายค่า เราสามารถซ้อนสูตรเพื่อรวบคำตอบให้เหลือค่าเดียวก็ได้ เช่น เขียนฟังก์ชันสรุปข้อมูลเช่น SUM, AVERAGE, MAX.. พวกนี้ครอบลงไป

=MAX(B2:B5-C2:C5)

ซึ่งถ้าเป็น Excel Version เก่า เราก็ยังต้องกด Ctrl+Shift+Enter อยู่ดีนะ ถึงจะไม่ Error

Excel Array Formula

การตรวจสอบสูตรด้วยการกด F9

เวลาเขียนสูตรใน Excel ยิ่งเขียนซับซ้อนมากขึ้น เราก็ยิ่งจะต้องตรวจสอบสูตรให้เป็น ไม่งีั้นจะยิ่งงงหนักเลย ซึ่งวิธีการตรวจสอบก็คือ Hightlight สูตรส่วนที่ต้องการแล้วกด F9

เช่น Highlight แบบนี้

Excel Array Formula ตอนที่ 1 : พื้นฐาน 3

กด F9 แล้วจะเห็นข้อมูลออกมา 4 อันอยู่ในเครื่องหมายปีกกา (สัญลักษณ์ของ Array) ข้อมูลแต่ละอันคั่นด้วย semicolon แปลว่าอยู่คนละบรรทัด (ถ้าคั่นด้วย comma แสดงว่าอยู่คนละคอลัมน์)

Excel Array Formula ตอนที่ 1 : พื้นฐาน 4

ตรวจสอบเสร็จอย่าลืมกด Esc เพื่อ Cancel ไม่งั้นมันจะบันทึกค่าคงที่นี้ลงไปในสูตรเลย

มิติของ Array

หากเราเอา Array 2 ตัวที่มีมิติหลายๆ แบบ มาทำอะไรกันซักอย่างจะให้ผลยังไง มาดูกัน

1 มิติแนวตั้งทั้งคู่

ผลลัพธ์ก็เป็นแนวตั้ง

Excel Array Formula ตอนที่ 1 : พื้นฐาน 5

1 มิติแนวนอนทั้งคู่

ผลลัพธ์ก็เป็นแนวนอน

Excel Array Formula ตอนที่ 1 : พื้นฐาน 6

เดี๋ยวลองกด F9 ให้ดู จะเห้นว่าคราวนี้คั่นด้วยเครื่องหมาย Comma แล้ว เพราะอยู่คนละคอลัมน์

Excel Array Formula ตอนที่ 1 : พื้นฐาน 7

1 มิติแนวตั้ง กับ 1 มิติแนวนอน

ผลลัพธ์กลายเป็นตาราง ขนาดเท่ากับแนวตั้ง x แนวนอน

Excel Array Formula ตอนที่ 1 : พื้นฐาน 8

ถ้าอันนี้กด F9 จะเห็นว่าคั่นด้วย Comma ก่อนเพื่อขึ้นคอลัมน์ใหม่ พอขึ้นบรรทัดใหม่ก็จะคั่นด้วย Semicolon

Excel Array Formula ตอนที่ 1 : พื้นฐาน 9

ตาราง 2 มิติทั้งคู่ ขนาดเท่ากัน

ผลลัพธ์ก็เป็นตารางขนาดเท่าเดิม

Excel Array Formula ตอนที่ 1 : พื้นฐาน 10

อย่างไรก็ตาม หากตารางขนาดไม่เท่ากัน มันจะ Error ในส่วนที่จับคู่กันไม่ได้

ตาราง 2 มิติ กับข้อมูลแนวตั้ง

ข้อมูลแนวตั้งเหมือนจะถูกขยายขนาดให้มีคอลัมน์เท่ากับตาราง 2 มิตินั้น โดยทุกคอลัมน์มีค่าเท่ากัน

Excel Array Formula ตอนที่ 1 : พื้นฐาน 11

อย่างไรก็ตาม ข้อมูลแนวตั้งต้องมีจำนวนแถวเท่ากับข้อมูลในตารางนะ ไม่งั้นมันจะ Error ในส่วนที่จับคู่กันไม่ได้

ตาราง 2 มิติ กับข้อมูลแนวนอน

ข้อมูลแนวนอนเหมือนจะถูกขยายขนาดให้มีจำนวนแถวเท่ากับตาราง 2 มิตินั้น โดยทุกแถวมีค่าเท่ากัน

Excel Array Formula ตอนที่ 1 : พื้นฐาน 12

อย่างไรก็ตาม ข้อมูลแนวนอนต้องมีจำนวนคอลัมน์เท่ากับข้อมูลในตารางนะ ไม่งั้นมันจะ Error ในส่วนที่จับคู่กันไม่ได้

กรอก Input แบบ Array ลงไปในฟังก์ชัน

เราสามารถใส่ Input แบบ Array ลงไปในฟังก์ชันที่ปกติรับค่าค่าเดียวได้ครับ เช่นอันนี้เราจะหาว่าชื่อที่ยาวที่สุดมีกี่ตัวอักษร

ปกติ LEN จะรับ input แค่ตัวเดียว แต่นี่เราใส่ไปทีเดียว 5 ตัวในแนวตั้ง มันก็เลยให้ผลลัพธ์ออกมา 5 ตัวในแนวตั้งด้วยเช่นกัน

=LEN(A2:A6)
Excel Array Formula ตอนที่ 1 : พื้นฐาน 13

สุดท้ายค่อยรวบด้วย MAX ครอบเข้าไป ทำให้ผลลัพธ์กลับมาเหลือตัวเดียวอีกครั้ง

=MAX(LEN(A2:A6))
Excel Array Formula ตอนที่ 1 : พื้นฐาน 14

และถ้าเราอยากได้ว่าชื่อที่ยาวที่สุดชื่ออะไรด้วย ก็ใช้พวก index match มาช่วยก็ได้ เช่น

=INDEX(A2:A6,MATCH(C2,LEN(A2:A6),0))
Excel Array Formula ตอนที่ 1 : พื้นฐาน 15

สังเกตุว่าใน MATCH เราก็ใส่ LEN ซ้อนลงไปได้เลยเหมือนกัน เพื่อให้มันให้ค่ามาเป็นจำนวนตัวอักษรแต่ละชื่อก่อน จะได้ MATCH หาเลข 17 ได้เจอ

Excel Array Formula

ตอนต่อไป

เดี๋ยวตอนต่อไปเราจะมาดูตัวอย่างการเขียนที่เจ๋งยิ่งขึ้นกว่านี้ นั่นก็คือการเขียน array formula แบบมีเงื่อนไขครับ

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 593
  •  
  •  
  •  
  •  
  • 593
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply