เรียนรู้ว่าอาร์เรย์สามารถลดความซับซ้อนของงานใน Excel ได้อย่างไร
อาร์เรย์เป็น ช่วง หรือกลุ่มของ ค่า ข้อมูลที่เกี่ยวข้อง ในโปรแกรมสเปรดชีตเช่น Excel และ Google Spreadsheets ค่าในอาร์เรย์จะถูกเก็บไว้ในเซลล์ที่อยู่ติดกันโดยปกติ
ใช้สำหรับอาร์เรย์
อาร์เรย์สามารถใช้ในสูตรทั้งสอง (สูตรอาร์เรย์) และเป็นอาร์กิวเมนต์สำหรับฟังก์ชันต่างๆเช่นอาร์เรย์ฟอร์มของฟังก์ชันค้นหาและ INDEX
ประเภทของอาร์เรย์
มีอาร์เรย์สองแบบใน Excel:
- อาร์เรย์แบบหนึ่งมิติหรือที่เรียกว่าเวกเตอร์หรือเวกเตอร์อาร์เรย์ ข้อมูลจะอยู่:
- ในแถวเดียว (อาร์เรย์แนวนอนแบบหนึ่งมิติ)
- ใน คอลัมน์ เดียว (อาร์เรย์แนวตั้ง 1 มิติ)
- อาร์เรย์สองมิติหรือที่เรียกว่าเมตริกซ์
- ข้อมูลจะอยู่ในหลายคอลัมน์หรือแถว
- ตารางอาร์เรย์ - อาร์เรย์ของตารางเป็นอาร์กิวเมนต์ที่ใช้ในฟังก์ชันการค้นหาของ Excel เช่น VLOOKUP และ HLOOKUP
- ฟังก์ชัน LOOKUP ค้นหาอาร์เรย์ของตารางเพื่อหาข้อมูลเฉพาะ
- สำหรับ VLOOKUP (การค้นหาตามแนวตั้ง) table_array ต้องมีข้อมูลอย่างน้อยสองคอลัมน์
- สำหรับ HLOOKUP (การค้นหาในแนวนอน) table_array ต้องมีข้อมูลอย่างน้อยสองแถว
ข้อมูลรวมสูตรอาร์เรย์
สูตรอาร์เรย์เป็น สูตร ที่ดำเนินการคำนวณเช่นการบวกหรือการคูณบนค่าในอาร์เรย์หนึ่งหรือมากกว่าหนึ่งค่ามากกว่าค่าข้อมูลเดียว
สูตรอาร์เรย์:
- ใช้ไวยากรณ์เดียวกันเป็นสูตรปกติ (พวกเขาทั้งหมดเริ่มต้นด้วยเครื่องหมายเท่ากับ (=) เช่น)
- ใช้ตัว ดำเนินการทางคณิตศาสตร์ เดียวกัน
- และปฏิบัติตามลำดับการปฏิบัติงานเดียวกัน
สูตรอาร์เรย์และฟังก์ชัน Excel
ฟังก์ชันในตัวของ Excel จำนวนมากเช่น SUM, AVERAGE หรือ COUNT สามารถใช้ในสูตรอาร์เรย์
นอกจากนี้ยังมีฟังก์ชันบางอย่างเช่นฟังก์ชัน TRANSPOSE ซึ่งต้องถูกป้อนเป็นแถวเพื่อให้ทำงานได้อย่างถูกต้อง
ประโยชน์ของฟังก์ชันหลายอย่างเช่น INDEX และ MATCH หรือ MAX และ IF สามารถขยายได้โดยการใช้สูตรเหล่านี้ร่วมกันในสูตรอาร์เรย์
สูตร CSE
ใน Excel สูตรอาร์เรย์ล้อมรอบด้วยวงเล็บปีกกา " {} " เครื่องหมายวงเล็บเหล่านี้ไม่สามารถพิมพ์ได้ แต่ต้องเพิ่มลงในสูตรโดยการกด Ctrl, Shift และ Enter หลังจากพิมพ์สูตรลงใน เซลล์ หรือเซลล์
ด้วยเหตุนี้สูตรอาร์เรย์บางครั้งจึงเรียกว่าสูตร CSE ใน Excel
ข้อยกเว้นของกฎนี้คือเมื่อใช้เครื่องหมายวงเล็บปีกกาเพื่อป้อนอาร์เรย์เป็น อาร์กิวเมนต์ สำหรับฟังก์ชันที่ปกติประกอบด้วยเพียงค่าเดียวหรือ การอ้างอิงเซลล์
ตัวอย่างเช่นในการกวดวิชาด้านล่างที่ใช้ฟังก์ชัน VLOOKUP และ CHOOSE เพื่อสร้างสูตรการค้นหาทางซ้ายอาร์เรย์ถูกสร้างขึ้นสำหรับอาร์กิวเมนต์ Index_num ของฟังก์ชัน CHOOSE โดยพิมพ์เครื่องหมายวงเล็บรอบ ๆ อาร์เรย์ที่ป้อน
ขั้นตอนในการสร้างสูตรอาร์เรย์
- ป้อนสูตร
- กด แป้น Ctrl และ Shift ค้าง ไว้บนแป้นพิมพ์
- กดและปล่อยคีย์ Enter เพื่อสร้างสูตรอาร์เรย์
- ปล่อย ปุ่ม Ctrl และ Shift
หากทำอย่างถูกต้องสูตรจะล้อมรอบด้วยวงเล็บปีกกาและแต่ละเซลล์ที่ถือสูตรจะมีผลลัพธ์ที่แตกต่างกัน
การแก้ไขสูตรอาร์เรย์
เมื่อใดที่สูตรอาร์เรย์ถูกแก้ไขวงเล็บปีกกาจะหายไปจากสูตรอาร์เรย์
เพื่อให้ได้สูตรดังกล่าวต้องป้อนอาร์เรย์สูตรโดยกดปุ่ม Ctrl, Shift และ Enter อีกครั้งเช่นเดียวกับเมื่อสร้างสูตรอาร์เรย์ขึ้นมา
ประเภทของสูตรอาร์เรย์
สูตรอาร์เรย์มีสองประเภทดังนี้
- สูตรอาร์เรย์หลายเซลล์ที่สูตรเดียวกันตั้งอยู่ในเซลล์มากกว่าหนึ่งเซลล์ใน แผ่นงาน
- สูตรอาร์เรย์เซลล์เดี่ยวที่สูตรหนึ่งดำเนินการคำนวณหลายแบบในเซลล์แผ่นเดียว
สูตรอาร์เรย์หลายเซลล์
เช่นชื่อของพวกเขาแสดงให้เห็นสูตรอาร์เรย์เหล่านี้จะอยู่ในเซลล์แผ่นงานหลายแผ่นและพวกเขายังส่งคืนอาร์เรย์เป็นคำตอบ
กล่าวอีกนัยหนึ่งสูตรเดียวกันนี้จะอยู่ในเซลล์สองเซลล์หรือมากกว่าและจะให้ผลลัพธ์ที่แตกต่างกันในแต่ละเซลล์
วิธีการทำเช่นนี้ก็คือแต่ละสำเนาหรือตัวอย่างของสูตรอาร์เรย์ทำการคำนวณเดียวกันในแต่ละเซลล์ที่อยู่ใน แต่แต่ละตัวอย่างของสูตรใช้ข้อมูลที่แตกต่างกันในการคำนวณและดังนั้นแต่ละอินสแตนซ์จะให้ผลลัพธ์ที่แตกต่างกัน
ตัวอย่างของสูตรอาร์เรย์หลายเซลล์จะเป็น:
{= A1: A2 * B1: B2}
สูตรอาร์เรย์เซลล์เดี่ยว
สูตรอาร์เรย์ที่สองนี้ใช้ฟังก์ชันเช่น SUM, AVERAGE หรือ COUNT เพื่อรวมผลลัพธ์ของสูตรอาร์เรย์หลายเซลล์เป็นค่าเดียวในเซลล์เดียว
ตัวอย่างของสูตรอาร์เรย์เซลล์เดียวคือ:
{= SUM (A1: A2 * B1: B2)}