Array คืออะไร

บทความนี้ยกความหมายของ Array มาไว้ตอนหลัง เพื่อให้เห็นตัวอย่างการใช้สูตร Array ไว้ก่อนบ้าง ทำให้ได้เห็นว่า เรานำสูตร Array ไปใช้งานกันอย่างไร แล้วมันคำนวณหาอะไร พอได้ผ่านการลองทำกับมือมาแล้ว คงพอเดากันได้บ้างว่า Array คืออะไร

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

ค่าตั้งแต่สองค่าขึ้นไปจะเรียงตามแนวนอน หรือแนวตั้ง หรือเรียงต่อกันไปเป็นพื้นที่ตารางที่มีความสูงความกว้างหลาย row หลาย column ก็ได้ โดย Excel กำหนดให้ใช้เครื่องหมายในการแบ่งลำดับของค่า ดังนี้

• ถ้าเป็น Array ในแนวนอน ให้ใช้เครื่องหมาย comma , คั่นระหว่างค่าแต่ละค่า เช่น 11,22,33
• ถ้าเป็น Array ในแนวตั้ง ให้ใช้เครื่องหมาย semi-colon ; คั่นระหว่างค่าแต่ละค่า เช่น 11;22;33
• ถ้าเป็น Array ที่มีขนาดความสูง 2 row และมีความกว้าง 3 column จะแสดงค่าที่มีเครื่องหมาย comma และ semi-colon ผสมกัน เช่น 1,2,3;11,22,33 โดย Array จะไล่ลำดับจากแนวนอน 1,2,3 ก่อนแล้วจึงใช้ ; ขึ้นแนวนอนถัดไปเป็น 11,22,33

การนำค่าแบบ Array ไปใช้ในเซลล์ ต่างจากการใช้วิธีพิมพ์ 1,2,3 ลงไปในเซลล์ตามปกติ โดยต้องใช้ Array แบบสูตรที่มีเครื่องหมายเท่ากับนำหน้าสุดเท่านั้น ดังนี้

1. การใช้แบบสูตร Array Constant เช่น พิมพ์ ={1,2,3} ลงไปในเซลล์ใดๆ จะพบว่าในเซลล์แสดงค่าแรกคือเลข 1 เพียงค่าเดียว ซึ่งถ้าต้องการแสดงให้ครบทุกค่า ต้องเลือกเซลล์ 3 เซลล์ติดกันตามแนวนอนแล้วพิมพ์ ={1,2,3} แล้วกดปุ่ม Ctrl+Shift+Enter เพื่อบันทึก จะพบว่าสูตร ={1,2,3} ซึ่งมีวงเล็บปีกกาอยู่ด้านหลังเครื่องหมายเท่ากับ จะมีวงเล็บปีกกาเพิ่มด้านหน้าเครื่องหมายเท่ากับอีกชั้นหนึ่งเป็น {={1,2,3}}
2. การใช้แบบสูตร Array ซึ่งรับค่ามาจากการอ้างอิงกับพื้นที่ในตาราง เช่น รับค่า Array มาจากเซลล์ตามแนวนอน {=A1:C1} หรือรับค่า Array มาจากเซลล์ตามแนวตั้ง {=A1:A3} หรือรับค่า Array มาจากตารางขนาดความสูง 2 row และมีความกว้าง 3 column {=A1:C2} ทั้งนี้โปรดสังเกตว่าวิธีนี้จะมีเครื่องหมายวงเล็บปีกกาแสดงไว้หน้าเครื่องหมายเท่ากับ แสดงว่าเป็นวงเล็บปีกกาที่เกิดจากการกดปุ่ม Ctrl+Shift+Enter
3. การใช้แบบสูตร Array ซึ่งรับค่ามาจากสูตรที่คืนค่าแบบ Array เช่น {=Offset(A1,0,0,2,3)} ซึ่งเทียบเท่ากับการอ้างอิงมาจากเซลล์ A1:C2 เป็นต้น

การนำค่าแบบ Array ทั้ง 3 แบบไปใช้ ขอให้เลือกพื้นที่ตารางให้มีขนาดพอดิบพอดีกับจำนวนของค่าที่มีอยู่ใน Array หรือให้มีขนาดตารางเท่ากับจำนวนผลลัพธ์ที่ต้องการไว้ก่อนที่จะสร้างสูตรลงไป กล่าวคือ ถ้าในสูตร Array คืนค่าสุดท้ายเพียงค่าเดียวก็ให้สร้างสูตรลงไปในเซลล์เพียงเซลล์เดียว แต่ถ้าสูตร Array คืนค่าผลลัพธ์ที่มีจำนวนค่ามากกว่าค่าเดียว ก็ต้องเลือกพื้นที่ตารางให้มีขนาดความสูงและความกว้างเท่ากันกับจำนวนและลำดับค่าที่มีอยู่ จากนั้นให้กดปุ่ม Ctrl+Shift+Enter ในการรับสูตร Array ลงไป จะพบว่า Excel กระจายค่าที่มีอยู่ลงไปในพื้นที่ตารางที่เตรียมไว้ให้เห็นได้ครบทุกค่า

ตัวอย่างการสร้างตารางสูตรคูณแบบ Array

เมื่อตั้งชื่อ Range Name ให้กับตัวเลขบนหัวตารางและข้างตารางว่า Top และ Left เสร็จแล้ว ให้ทดลองเลือกพื้นที่เซลล์ C3:H7 ซึ่งเกินกว่าแนวขอบเขตตารางตัวเลขที่มี แล้วสร้างสูตร =Top*Left แล้วกดปุ่ม Ctrl+Shift+Enter จะได้สูตร Array {=Top*Left}

สังเกตว่าเฉพาะพื้นที่ตาราง C3:F5 ซึ่งอยู่ภายในขอบเขตของ Top กับ Left เท่านั้นสามารถคำนวณหาผลคูณเป็นตารางสูตรคูณที่ต้องการ ส่วนพื้นที่นอกเหนือจาก C3:F5 ที่มีสูตร Array จะคืนค่าเป็น Error #N/A

จากนั้นให้คลิกเซลล์ใดก็ได้ที่มีสูตร {=Top*Left} แล้วกดปุ่ม F2 ตามด้วยปุ่ม F9 จะแสดงผลลัพธ์เป็น Array Constant {20,30,40,50;40,60,80,100;60,90,120,150} โดยผลลัพธ์นี้มีเครื่องหมาย ; คั่นอยู่ 2 ตัว แสดงว่ามี 3 row เพราะมีการขึ้น row ใหม่ 2 ครั้ง และในช่วงที่คั่นด้วย ; นั้น มีเครื่องหมาย , คั่นอยู่ช่วงละ 3 ตัว แสดงว่าในแต่ละ row มีค่าอยู่ 4 column หรืออีกนัยหนึ่งแสดงว่า พื้นที่ตารางที่เหมาะจะสร้างสูตร {=Top*Left} ลงไปนั้น ต้องมีความสูง 3 row และกว้าง 4 column ดังนั้นหากเลือกพื้นที่เกินกว่าที่จำเป็นก็จะได้คำตอบเป็น Error #N/A

แต่ถ้าต้องการหายอดรวมของผลคูณของ =Top*Left ในเซลล์ C9 ให้สร้างสูตร =SUM(Top*Left) แล้วกดปุ่ม Ctrl+Shift+Enter จะได้สูตร Array {=SUM(Top*Left)} ซึ่งสาเหตุที่คราวนี้สร้างสูตรลงไปในเซลล์ C9 เพียงเซลล์เดียว เพราะคำตอบของการหายอดรวมมีเพียงค่าเดียวเท่านั้น

เรื่องอื่นๆเกี่ยวกับ Array ที่ควรทราบ

1. ใน Excel Help เรียกพื้นที่ใดๆที่มีจำนวนตั้งแต่ 2 เซลล์ขึ้นไปว่า Array
2. การแกะดูค่าที่คำนวณได้จากสูตร Array ให้เริ่มจากคลิกลงไปในเซลล์สูตรแล้วกดปุ่ม F2 แล้วตามด้วยปุ่ม F9 เมื่อเห็นค่าที่คำนวณได้แล้วให้กดปุ่ม Esc เพื่อกลับเป็นสูตรตามเดิม ทั้งนี้ถ้าค่าที่แกะได้จากสูตร Array คืนค่าหลายค่าจนเกินกว่าที่เซลล์หนึ่งจะรับได้ จะพบว่า Excel ไม่ยอมแสดงค่าให้เห็น
3. วิธีค้นหาพื้นที่ของสูตร Array ที่เกิดจากการสร้างขึ้นหลายเซลล์พร้อมกัน ให้เริ่มจากคลิกเลือกเซลล์ใดเซลล์หนึ่งซึ่งใช้สูตร Array แล้วกดปุ่ม F5 > Special > กาช่อง Current array
   
   
   
4. บางคนกดปุ่ม Ctrl+Shift+Enter แต่ไม่ได้เครื่องหมายวงเล็บปีกกา เพราะไม่ได้กดทั้งสามปุ่มพร้อมกันจริง ถ้าอยากให้ได้ { } อย่างแน่นอน ให้ใช้มือซ้ายกดปุ่ม Ctrl+Shift แช่ค้างไว้ก่อน จากนั้นใช้มือขวากดปุ่ม Enter ลงไป
5. สูตร Array ที่เกิดจากการสร้างขึ้นหลายเซลล์พร้อมกันโดยการกดปุ่ม Ctrl+Shift+Enter จะไม่สามารถ Insert หรือ Delete เฉพาะบางเซลล์ หากต้องการแก้ไขหรือลบทิ้ง ต้องเลือกพื้นที่ทั้งหมดที่ใช้สูตร Array นั้นก่อนแล้วจึงแก้ไขสูตรหรือลบสูตรทิ้ง (ดังนั้นหากสามารถลบสูตร Array เพียงเซลล์เดียวได้ ย่อมแสดงว่าเป็นสูตร Array ที่สร้างทีละเซลล์)
6. สูตร Array ที่เราสร้างขึ้นโดยการกดปุ่ม Ctrl+Shift+Enter จะไม่สามารถสร้างลงไปในเซลล์ที่ถูก Merge ไว้ก่อนแล้ว จะถูกเตือนว่า Array formulas are not valid in merged cells (แต่เราสามารถ Merge เซลล์ที่สร้างสูตร Array ไว้ก่อน)
   
   
   
   
7. สูตร Array ที่เกิดจากการสร้างขึ้นหลายเซลล์พร้อมกันโดยการกดปุ่ม Ctrl+Shift+Enter จะใช้เวลาคำนวณเร็วกว่าการสร้างสูตรคำนวณเพื่อหาคำตอบทีละเซลล์แยกจากกัน
8. สูตร Array ที่เราสร้างขึ้นโดยการกดปุ่ม Ctrl+Shift+Enter ทั้งที่สร้างในเซลล์เดียวหรือหลายเซลล์พร้อมกัน จะใช้เวลาคำนวณช้ากว่าสูตรสำเร็จรูปที่มีอยู่ใน Excel ดังนั้นจึงแนะนำให้ใช้สูตร Array ต่อเมื่อไม่มีสูตรสำเร็จรูปอื่นซึ่งสามารถคำนวณหาคำตอบที่ต้องการได้แล้วเท่านั้น
9. ใน Excel รุ่น 2003 และรุ่นเก่าก่อนนั้น สูตร Array ที่เราสร้างขึ้นโดยการกดปุ่ม Ctrl+Shift+Enter จะไม่ยอมรับการอ้างอิงพื้นที่ตารางที่มีขนาดใหญ่เต็มความสูงทั้ง 65,536 row ของตาราง เช่น
   {=MIN(IF(A:A<>0,ROW(A:A)))} หรือ
   {=MIN(IF(A1:A65536<>0,ROW(A1:A65536)))}
   แต่ถ้าแก้เป็น {=MIN(IF(A1:A65535<>0,ROW(A1:A65535)))} จะใช้ได้
10. นอกจากสูตร Array ที่เราสร้างขึ้นโดยการกดปุ่ม Ctrl+Shift+Enter แล้ว ใน Excel ยังมีสูตรสำเร็จรูปอื่นอีกที่ทำงานแบบ Array โดยไม่ต้องกดปุ่ม Ctrl+Shift+Enter เช่น สูตร SumIF, CountIF, SumIFs, CountIFs, AverageIfs, SumProduct ซึ่งถ้าพิจารณาตามหลักการแล้วสูตรใดก็ตามซึ่งรับค่าจากเซลล์ตั้งแต่ 2 เซลล์ขึ้นไปได้ ย่อมเป็นสูตรที่ทำงานแบบ Array จึงยังมีสูตรอีกมากที่สามารถทำงานแบบ Array ได้ เช่น สูตร Sum, Max, Min, And, Or, Index, หรือ Offset เป็นต้น
11. SumIFs, CountIFs, AverageIfs เป็นสูตรที่เกิดขึ้นใน Excel 2007 เป็นต้นมา ดังนั้นหากยังจำเป็นต้องใช้ Excel 2003 หรือรุ่นเก่ากว่านี้อยู่อีก แนะนำให้หลีกเลี่ยงสูตร SumIFs, CountIFs, AverageIfs ไปก่อน โดยหันไปใช้สูตร Sum-IF-Array, Count-IF-Array, Average-If-Array ซึ่งสามารถใช้งานใน Excel ได้ทุกรุ่น แต่จำเป็นต้องสร้างโดยการกดปุ่ม Ctrl+Shift+Enter
12. Max, Min, And, Or เมื่อนำมาใช้แบบ Array จะไม่สามารถคืนค่าหลายค่าแบบ Array เช่น ถ้าสร้าง {=Max(RangeA,RangeB)} จะไม่ได้คำตอบเป็นค่าสูงสุดของแต่ละตำแหน่งใน RangeA เมื่อนำมาเทียบกับแต่ละตำแหน่งใน RangeB แต่จะได้คำตอบเป็นค่าสูงสุดเพียงค่าเดียวจากค่าทั้งหมดใน RangeA และ RangeB
13. สูตร Array บางสูตรไม่สามารถหาคำตอบมาแสดงให้เห็นในเซลล์ แต่ถ้านำสูตรนั้นไปซ้อนในสูตรอื่นจะสามารถทำงานร่วมกับสูตรอื่นได้
14. สูตร Array ที่คืนค่าคำตอบหลายค่า ไม่ควรสร้างสูตรนั้นลงไปในเซลล์เดียวเพราะจะได้คำตอบไม่ครบทั้งหมดหรือได้คำตอบ error
15. ถ้าสร้างสูตร Array ที่คืนค่าคำตอบหลายค่า ลงไปในเซลล์เดียวในแนวเดียวกับคำตอบจากตารางฐานข้อมูล จะได้คำตอบจากเซลล์ในแนวคู่ขนานกับตำแหน่งเซลล์ที่สร้างสูตรลงไป ซึ่งถือเป็นความบังเอิญเท่านั้น ถ้าย้ายสูตรไปนอกแนวคู่ขนานจะได้คำตอบ error
    
    
    
    
16. ถ้าสร้างสูตร Array ลงไปในตารางหลายเซลล์พร้อมกัน จะพบว่าในการสร้างสูตรตอนที่คลิกเลือกพื้นที่เซลล์ที่เคยตั้งชื่อ Range Name ไว้ก่อนนั้น Excel จะไม่ยอมนำชื่อ Range Name มาใส่ให้ในสูตร เช่น เดิมตั้งชื่อเซลล์ B4:B8 ว่า DataRange จากนั้นเมื่อเลือกเซลล์อื่นใดเพื่อจะสร้างสูตรลงไปพร้อมกัน พอพิมพ์ = แล้วคลิกเลือกเซลล์ B4:B8 จะไม่ได้ชื่อ DataRange มาใส่ในสูตร แต่จะได้ตำแหน่งเซลล์ B4:B8 แสดงในสูตรเหมือนว่าไม่เคยมีชื่อ DataRange ตั้งไว้ก่อนแต่อย่างใด หากต้องการนำชื่อ Range Name มาใส่ในสูตร ให้กดปุ่ม F3 เพื่อเลือกชื่อที่ต้องการมาใช้แทนการคลิกเลือกเซลล์
17. ถ้าอยากจะเก่ง Excel ให้ทดลองสร้างสูตรกับตารางขนาดเล็กให้ผ่านก่อน แล้วให้ลองกดปุ่ม Enter ตามธรรมดาบ้าง หรือกดปุ่ม Ctrl+Shift+Enter บ้าง เพราะบางครั้งจะได้คำตอบต่างไปจากเดิม
18. ไม่ควรปล่อยให้คนที่ไม่รู้จักวิธีสร้างสูตร Array ที่สร้างขึ้นโดยการกดปุ่ม Ctrl+Shift+Enter มาแก้ไขแฟ้มที่มีสูตร Array เพราะเขาจะกดปุ่ม Enter แทนแล้วอาจทำให้ได้คำตอบผิดเพี้ยนไปหรือได้คำตอบ error ขึ้นมาแทน

หากคุณอ่านบทความนี้แล้วยังไม่เข้าใจ แล้วพยายามย้อนกลับไปอ่านซ้ำแล้วซ้ำอีก ผมเชื่อว่าถึงอย่างไรก็จะยังไม่เข้าใจอยู่นั่นเอง ถ้าอยากจะเข้าใจสูตร Array ต้องเรียนรู้จากการทดลอง ลองผิดลองถูกหลายๆครั้ง

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

ถ้ามัวแต่รีรอ คิดในใจไปเรื่อยๆว่าจะเป็นไปได้ไหมเนี่ย ก็ไม่มีวันเก่งสูตร Array