Excel array formula เป็นสูตรที่ทำการคำนวณค่าในอาร์เรย์อย่างน้อยหนึ่งอาร์เรย์แทนที่จะเป็นค่าข้อมูลเดียว ในโปรแกรมสเปรดชีต อาร์เรย์คือช่วงหรือชุดของค่าข้อมูลที่เกี่ยวข้องซึ่งมักจะอยู่ในเซลล์ที่อยู่ติดกันในเวิร์กชีต
คำแนะนำเหล่านี้ใช้กับ Excel 2019, 2016, 2013, 2010 และ Excel สำหรับ Microsoft 365
สูตรอาร์เรย์คืออะไร
สูตรอาร์เรย์คล้ายกับสูตรปกติ พวกเขา:
- เริ่มต้นด้วยเครื่องหมายเท่ากับ (=)
- ใช้รูปแบบเดียวกับสูตรปกติ
- ใช้ตัวดำเนินการทางคณิตศาสตร์เดียวกัน
- ปฏิบัติตามคำสั่งเดิม
อย่างไรก็ตาม สูตรอาร์เรย์ถูกล้อมรอบด้วยวงเล็บปีกกา { }. และคุณไม่สามารถพิมพ์ได้ คุณต้องเพิ่มโดยกดปุ่ม Ctrl, Shift และ Enter หลังจากป้อนสูตรลงใน a เซลล์หรือเซลล์ ด้วยเหตุนี้ บางครั้งสูตรอาร์เรย์จึงเรียกว่า CSE formula ใน Excel
ทุกครั้งที่คุณแก้ไขสูตรอาร์เรย์ วงเล็บปีกกาจะหายไป หากต้องการส่งคืน ให้กดปุ่ม Ctrl, Shift และ Enter อีกครั้ง
สูตรอาร์เรย์มีสองประเภทหลัก:
- สูตรอาร์เรย์เซลล์เดียวที่ทำการคำนวณหลายรายการในเซลล์แผ่นงานเดียว
- สูตรอาร์เรย์หลายเซลล์ที่อยู่ในเซลล์แผ่นงานมากกว่าหนึ่งเซลล์
วิธีสร้างสูตรอาร์เรย์
-
ป้อนสูตรในเซลล์
- กด Ctrl และ Shift บนแป้นพิมพ์ค้างไว้
- กดและปล่อยปุ่ม Enter เพื่อสร้างสูตรอาร์เรย์
- ปล่อยปุ่ม Ctrl และ Shift
- ถ้าทำถูกวิธีจัดฟันแบบดัดลอนจะล้อมรอบสูตร
สูตรอาร์เรย์เซลล์เดียว
สูตรอาร์เรย์เซลล์เดียวใช้ฟังก์ชัน เช่น SUM, AVERAGE หรือ COUNTเพื่อรวมผลลัพธ์ของสูตรอาร์เรย์หลายเซลล์ให้เป็นค่าเดียวในเซลล์เดียว ด้านล่างเป็นตัวอย่าง:
{=SUM(A1:A2B1:B2)}
สูตรข้างต้นรวมผลคูณของ A1B1 และ A2B2 เข้าด้วยกัน แล้วส่งกลับผลลัพธ์เดียวใน เซลล์เดียวในเวิร์กชีต อีกวิธีในการนำเสนอสูตรนั้นคือ:
=(A1B1)+(A2B2)
สูตรอาร์เรย์หลายเซลล์
ตามชื่อสูตร สูตรอาร์เรย์หลายเซลล์อยู่ในเซลล์แผ่นงานหลายเซลล์ และคืนค่าอาร์เรย์เป็นคำตอบ กล่าวอีกนัยหนึ่ง สูตรเดียวกันอยู่ในเซลล์ตั้งแต่สองเซลล์ขึ้นไป และจะส่งกลับคำตอบที่แตกต่างกันในแต่ละเซลล์
สูตรอาร์เรย์แต่ละสูตรหรือแต่ละสำเนาจะทำการคำนวณแบบเดียวกันในแต่ละเซลล์ที่มันอาศัยอยู่ แต่แต่ละเซลล์ใช้ข้อมูลต่างกัน ดังนั้นแต่ละคนจึงให้ผลลัพธ์ที่แตกต่างกัน ตัวอย่างของสูตรอาร์เรย์หลายเซลล์คือ:
{=A1:A2B1:B2}
หากสูตรอาร์เรย์ด้านบนอยู่ใน เซลล์ C1 และ C2 ในเวิร์กชีต ผลลัพธ์จะเป็นดังนี้:
- ข้อมูลใน A1 ถูกคูณด้วยข้อมูลใน B1 และผลลัพธ์จะปรากฏใน เซลล์ C1.
- ข้อมูลใน A2 ถูกคูณด้วยข้อมูลใน B2 และผลลัพธ์จะปรากฏใน เซลล์ C2.
สูตรอาร์เรย์และฟังก์ชัน Excel
คุณสามารถใช้ฟังก์ชันในตัวของ Excel ได้มากมาย เช่น SUM, AVERAGE และ COUNT ในสูตรอาร์เรย์ นอกจากนี้ยังมีฟังก์ชันบางอย่าง เช่น TRANSPOSE ซึ่งต้องเป็นสูตรอาร์เรย์เสมอเพื่อให้ทำงานได้อย่างถูกต้อง (ฟังก์ชัน TRANSPOSE คัดลอกข้อมูลจากแถวหนึ่งไปยังคอลัมน์หรือกลับกัน)
คุณยังสามารถขยายประโยชน์ใช้สอยของฟังก์ชันได้มากมาย เช่น INDEX และ MATCH หรือ MAX และ IF โดยใช้สูตรอาร์เรย์ร่วมกัน
สร้างสูตรอาร์เรย์เซลล์เดียวอย่างง่าย
สูตรอาร์เรย์เซลล์เดียวมักจะทำการคำนวณหลายเซลล์ก่อน จากนั้นจึงใช้ฟังก์ชันเช่น AVERAGE หรือ SUM เพื่อรวม ผลลัพธ์ของอาร์เรย์เป็นผลลัพธ์เดียว
ละเว้นค่าความผิดพลาดเมื่อค้นหาข้อมูล
สูตรอาร์เรย์นี้ใช้ฟังก์ชัน AVERAGE, IF และ ISNUMBER เพื่อค้นหา ค่าเฉลี่ยสำหรับข้อมูลที่มีอยู่โดยไม่สนใจค่าความผิดพลาด เช่น DIV/0! และ NAME?
นับเซลล์ข้อมูล
ใช้ฟังก์ชัน SUM และ IF ในสูตรอาร์เรย์เพื่อนับเซลล์ของข้อมูลที่ตรงตามเงื่อนไขข้อใดข้อหนึ่ง เทคนิคนี้แตกต่างจากการใช้ฟังก์ชัน COUNTIFS ของ Excel ซึ่งกำหนดให้ตรงตามเงื่อนไขที่ตั้งไว้ทั้งหมดก่อนที่จะนับเซลล์
ค้นหาจำนวนบวกหรือลบที่ใหญ่ที่สุด
ตัวอย่างนี้รวมฟังก์ชัน MAX และฟังก์ชัน IF ในสูตรอาร์เรย์ที่จะหาค่าที่มากที่สุดหรือสูงสุดสำหรับช่วงของ ข้อมูลเมื่อตรงตามเกณฑ์เฉพาะ ค่าที่มากที่สุดคือเวลาที่ช้าที่สุด
ค้นหาจำนวนบวกหรือลบที่น้อยที่สุด
คล้ายกับตัวอย่างข้างต้น คุณสามารถรวมฟังก์ชัน MIN และ IF ในสูตรอาร์เรย์เพื่อค้นหาค่าที่น้อยที่สุดหรือต่ำสุด สำหรับช่วงของข้อมูลเมื่อตรงตามเกณฑ์เฉพาะ
หาค่ากลางหรือค่ามัธยฐาน
ฟังก์ชัน MEDIAN ใน Excel จะค้นหาค่ากลางสำหรับรายการข้อมูล เมื่อรวมกับฟังก์ชัน IF ในสูตรอาร์เรย์ คุณจะพบค่ากลางสำหรับกลุ่มข้อมูลที่เกี่ยวข้องกันต่างๆ
สร้างสูตรการค้นหาด้วยหลายเกณฑ์
สูตรอาร์เรย์นี้เกี่ยวข้องกับการซ้อนฟังก์ชัน MATCH และ INDEX เพื่อค้นหาข้อมูลเฉพาะในฐานข้อมูล
สร้างสูตรการค้นหาทางซ้าย
ฟังก์ชัน VLOOKUP มักจะค้นหาเฉพาะข้อมูลที่อยู่ในคอลัมน์ทางขวา แต่การรวมเข้ากับฟังก์ชัน CHOOSE คุณ สามารถสร้างสูตรการค้นหาด้านซ้ายที่จะค้นหาคอลัมน์ของข้อมูลทางด้านซ้ายของอาร์กิวเมนต์ Lookup_value
เมื่อป้อนอาร์เรย์เป็นอาร์กิวเมนต์สำหรับฟังก์ชันที่โดยปกติแล้วจะมีเพียงค่าเดียวหรือการอ้างอิงเซลล์ คุณสามารถพิมพ์ในวงเล็บปีกกาได้โดยตรง แทนที่จะใช้ Ctrl+Shift+Enterการกดแป้นพิมพ์พร้อมกัน ดังตัวอย่างด้านบน
