Excel แถว สูตร เป็นสูตรที่ดำเนินการคำนวณค่าในอาร์เรย์หนึ่งหรือมากกว่าหนึ่งค่าแทนที่จะเป็นค่าข้อมูลเดียว ในโปรแกรมสเปรดชีตอาร์เรย์คือช่วงหรือชุดของค่าข้อมูลที่เกี่ยวข้องซึ่งปกติจะเก็บไว้ในเซลล์ที่อยู่ติดกันในแผ่นงาน คู่มือนี้จะอธิบายวิธีการใช้สูตรอาร์เรย์ใน Excel เวอร์ชันใหม่ทั้งหมด
บทแนะนำนี้สามารถใช้งานร่วมกับ Excel รุ่นล่าสุดได้รวมถึง 2007, 2010, 2013, 2016 และ Office 365
สูตร Array คืออะไร?
สูตรอาร์เรย์มีลักษณะคล้ายกับสูตรปกติ:
- พวกเขาเริ่มต้นด้วยเครื่องหมายเท่ากับ ( = )
- ใช้ไวยากรณ์เดียวกับสูตรปกติ
- ใช้โอเปอเรเตอร์ทางคณิตศาสตร์เดียวกัน
- ปฏิบัติตามลำดับขั้นตอนเดียวกัน
ใน Excel สูตรอาร์เรย์ล้อมรอบด้วยวงเล็บปีกกา { } - เครื่องหมายวงเล็บเหล่านี้ไม่สามารถพิมพ์ได้ ต้องเพิ่มสูตรโดยการกดปุ่ม Ctrl , เปลี่ยน , และ เข้าสู่ คีย์หลังจากพิมพ์สูตรลงในเซลล์หรือเซลล์ ด้วยเหตุนี้สูตรอาร์เรย์จึงเรียกว่า a CSE สูตร ใน Excel
ข้อยกเว้นของกฎนี้คือเมื่อใช้เครื่องหมายวงเล็บปีกกาเพื่อป้อนอาร์เรย์เป็นอาร์กิวเมนต์สำหรับฟังก์ชันที่ปกติประกอบด้วยเพียงค่าเดียวหรือการอ้างอิงเซลล์ ตัวอย่างเช่นในบทแนะนำด้านล่างที่ใช้ VLOOKUP และ เลือก เพื่อสร้างสูตรการค้นหาทางซ้ายอาร์เรย์ถูกสร้างขึ้นสำหรับ เลือก ฟังก์ชั่น index_num อาร์กิวเมนต์โดยการพิมพ์วงเล็บรอบอาร์เรย์ที่ป้อน
ขั้นตอนในการสร้างสูตรอาร์เรย์
- ป้อนสูตร
- กดค้างไว้ Ctrl และ เปลี่ยน บนแป้นพิมพ์
- กดและปล่อยปุ่ม เข้าสู่ คีย์เพื่อสร้างสูตรอาร์เรย์
- ปล่อยCtrl และ เปลี่ยน กุญแจ
ถ้าทำอย่างถูกต้องสูตรจะล้อมรอบด้วยวงเล็บปีกกาและแต่ละเซลล์ที่ถือสูตรจะมีผลลัพธ์ที่แตกต่างกัน
การแก้ไขสูตรอาร์เรย์
เมื่อมีการแก้ไขสูตรอาร์เรย์เครื่องหมายวงเล็บปีกกาจะหายไปจากสูตรอาร์เรย์ เพื่อให้ได้สูตรดังกล่าวให้ป้อนสูตรอาร์เรย์โดยการกดปุ่ม Ctrl, เปลี่ยน, และ เข้าสู่ คีย์อีกครั้งเช่นเดียวกับเมื่อสูตรอาร์เรย์ถูกสร้างขึ้นครั้งแรก
ประเภทของสูตรอาร์เรย์
มีสองประเภทของสูตรอาร์เรย์:
- สูตรอาร์เรย์เซลล์เดียวที่ดำเนินการคำนวณหลายแบบในเซลล์แผ่นเดียว
- สูตรอาร์เรย์หลายเซลล์ที่อยู่ในเซลล์แผ่นงานมากกว่าหนึ่งแผ่น
สูตรอาร์เรย์เซลล์เดี่ยว
สูตรอาร์เรย์ชนิดนี้ใช้ฟังก์ชันเช่น SUM, เฉลี่ย, หรือ นับเพื่อรวมผลลัพธ์ของสูตรอาร์เรย์หลายเซลล์เป็นค่าเดียวในเซลล์เดียว ตัวอย่างของสูตรอาร์เรย์เซลล์เดียวคือ:
{= SUM (A1: A2 * B1: B2)}
สูตรนี้รวมผลิตภัณฑ์ของ A1 * B1 และ A2 * B2, และจากนั้นจะแสดงผลลัพธ์เดียวในเซลล์เดียวในแผ่นงาน อีกวิธีหนึ่งในการเขียนสูตรข้างต้นจะเป็น:
สูตรอาร์เรย์หลายเซลล์
ชื่อของพวกเขาแสดงให้เห็นว่าสูตรอาร์เรย์เหล่านี้อยู่ในเซลล์แผ่นงานหลายแผ่นและจะส่งคืนอาร์เรย์เป็นคำตอบ กล่าวอีกนัยหนึ่งสูตรเดียวกันนี้มีอยู่ในเซลล์สองเซลล์ขึ้นไปและจะให้ผลลัพธ์ที่แตกต่างกันในแต่ละเซลล์
สำเนาแต่ละฉบับหรือ ตัวอย่าง, ของสูตรอาร์เรย์ดำเนินการคำนวณเดียวกันในแต่ละเซลล์ที่อาศัย แต่แต่ละตัวอย่างของสูตรจะใช้ข้อมูลที่แตกต่างกันในการคำนวณ ดังนั้นแต่ละอินสแตนซ์จะให้ผลลัพธ์ที่แตกต่างกัน ตัวอย่างของสูตรอาร์เรย์ของเซลล์หลายเซลล์จะมีลักษณะดังนี้:
{= A1: A2 * B1: B2}
ถ้าตัวอย่างข้างต้นอยู่ใน เซลล์ C1 และ C2 ในแผ่นงานผลลัพธ์จะเป็นดังนี้:
- ข้อมูลใน A1 คูณด้วยข้อมูลเข้า B1และผลลัพธ์จะถูกจัดเก็บไว้ เซลล์ C1.
- ข้อมูลใน A2 คูณด้วยข้อมูลเข้า B2และผลลัพธ์จะถูกจัดเก็บไว้ เซลล์ C2.
ใช้อาร์เรย์เพื่อย้ายแถวและคอลัมน์
ไขว้ ถูกใช้เพื่อคัดลอกข้อมูลจากแถวลงในคอลัมน์หรือในทางกลับกัน ฟังก์ชันนี้เป็นหนึ่งในไม่กี่แห่งใน Excel ที่ต้องใช้เป็นสูตรอาร์เรย์เสมอ
สูตรอาร์เรย์และฟังก์ชัน Excel
หลายฟังก์ชันในตัวของ Excel เช่น SUM, เฉลี่ย, และ นับ, นอกจากนี้ยังสามารถใช้ในสูตรอาร์เรย์ นอกจากนี้ยังมีฟังก์ชั่นบางอย่างเช่น ไขว้ ฟังก์ชันที่ต้องถูกป้อนเป็นอาร์เรย์เพื่อให้ทำงานได้อย่างถูกต้อง ประโยชน์ของหลายฟังก์ชันเช่น ดัชนี และ การจับคู่ หรือ MAX และ ถ้า สามารถขยายได้โดยการใช้ร่วมกันในสูตรอาร์เรย์
ตัวอย่างสูตรอาร์เรย์ใน Excel:
01 จาก 08สร้างสูตรอาร์เรย์แบบเดี่ยวที่เรียบง่าย
สูตรอาร์เรย์เซลล์เดียวปกติก่อนดำเนินการคำนวณหลายเซลล์แล้วใช้ฟังก์ชันเช่น เฉลี่ย หรือ SUM เพื่อรวมผลลัพธ์ของอาร์เรย์เป็นผลลัพธ์เดียว
02 จาก 08ละเว้นค่าผิดพลาดเมื่อค้นหาข้อมูล
สูตรอาร์เรย์นี้ใช้ เฉลี่ย, ถ้า, และ ISNUMBER เพื่อหาค่าเฉลี่ยสำหรับข้อมูลที่มีอยู่ในขณะที่ละเลยค่าผิดพลาดเช่น # DIV / 0! และ #ชื่อ?
03 จาก 08นับเซลล์ของข้อมูล
ใช้ SUM และถ้า ฟังก์ชันในสูตรอาร์เรย์เพื่อนับเซลล์ข้อมูลที่ตรงตามเงื่อนไขข้อใดข้อหนึ่ง เทคนิคนี้แตกต่างจากการใช้ Excel COUNTIFS ซึ่งจะต้องมีเงื่อนไขทั้งหมดที่จะต้องได้รับก่อนที่เซลล์จะถูกนับ
04 จาก 08ค้นหาหมายเลขบวกหรือเชิงลบที่ใหญ่ที่สุด
ตัวอย่างนี้ประกอบด้วย MAX ฟังก์ชันและถ้า ในสูตรอาร์เรย์ที่จะหาค่าที่มากที่สุดหรือสูงสุดสำหรับช่วงของข้อมูลเมื่อมีการระบุเกณฑ์เฉพาะ
05 จาก 08ค้นหาหมายเลขบวกหรือลบเล็กที่สุด
คล้ายกับตัวอย่างข้างต้น นาที ฟังก์ชันและ IF ในสูตรอาร์เรย์เพื่อหาค่าที่เล็กที่สุดหรือต่ำสุดสำหรับช่วงของข้อมูลเมื่อมีการระบุเกณฑ์เฉพาะ
06 จาก 08หาค่ากลางหรือค่ามัธยฐาน
MEDIAN ฟังก์ชันใน Excel พบค่ากลางสำหรับรายการข้อมูล โดยผสมผสานกับ ถ้า ในสูตรอาร์เรย์สามารถหาค่ากลางสำหรับกลุ่มข้อมูลที่เกี่ยวข้องกันได้
07 จาก 08สร้างสูตรการค้นหาด้วยเกณฑ์มากกว่าหนึ่งรายการ
สูตรอาร์เรย์นี้เกี่ยวข้องกับการทำรัง การจับคู่ และ ดัชนี ฟังก์ชันเพื่อค้นหาข้อมูลเฉพาะในฐานข้อมูล
08 ใน 08ทำสูตรค้นหาแบบค้นหาด้านซ้าย
VLOOKUP ฟังก์ชันจะค้นหาเฉพาะข้อมูลที่อยู่ในคอลัมน์ทางด้านขวาเท่านั้น แต่รวมกับ เลือก คุณสามารถสร้างสูตรการค้นหาด้านซ้ายที่จะค้นหาคอลัมน์ข้อมูลทางด้านซ้ายของ lookup_value ข้อโต้แย้ง.
