Excel

สูตรอาร์เรย์แบบไดนามิกใน Excel

Dynamic Array Formulas Excel

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





นี่คือการอัปเกรดครั้งใหญ่และการเปลี่ยนแปลงที่น่ายินดี Dynamic Arrays จะแก้ปัญหาที่ยากมากใน Excel และเปลี่ยนวิธีการออกแบบและสร้างเวิร์กชีตโดยพื้นฐาน

ความพร้อมใช้งาน

อาร์เรย์ไดนามิกและฟังก์ชันใหม่ด้านล่างมีให้ใช้งานเท่านั้น Excel 365 . Excel 2016 และ Excel 2019 ไม่มีการสนับสนุนสูตรอาร์เรย์แบบไดนามิก เพื่อความสะดวก ฉันจะใช้ 'Dynamic Excel' (Excel 365) และ 'Traditional Excel' (2019 หรือก่อนหน้า) เพื่อแยกความแตกต่างของเวอร์ชันด้านล่าง





ใหม่: วิดีโอการฝึกอบรมสูตร Dynamic Array Formula

ฟังก์ชั่นใหม่

ในฐานะที่เป็นส่วนหนึ่งของการอัปเดตอาร์เรย์แบบไดนามิก ตอนนี้ Excel มีฟังก์ชันใหม่ 8 ฟังก์ชันซึ่งใช้ประโยชน์จากอาร์เรย์แบบไดนามิกโดยตรงเพื่อแก้ปัญหาที่ยากต่อการแก้ไขด้วยสูตรทั่วไป คลิกลิงก์ด้านล่างเพื่อดูรายละเอียดและตัวอย่างสำหรับแต่ละฟังก์ชัน:

การทำงาน วัตถุประสงค์
กรอง กรองข้อมูลและส่งคืนระเบียนที่ตรงกัน
รันดาร์เรย์ สร้างอาร์เรย์ของตัวเลขสุ่ม
ลำดับ สร้างอาร์เรย์ของตัวเลขตามลำดับ
เรียงลำดับ จัดเรียงช่วงตามคอลัมน์
เรียงลำดับ จัดเรียงช่วงตามช่วงหรืออาร์เรย์อื่น
มีเอกลักษณ์ แยกค่าที่ไม่ซ้ำจากรายการหรือช่วง
XLOOKUP การแทนที่ที่ทันสมัยสำหรับ VLOOKUP
XMATCH การแทนที่ที่ทันสมัยสำหรับฟังก์ชัน MATCH

วิดีโอ: ฟังก์ชันไดนามิกอาร์เรย์ใหม่ใน Excel (ประมาณ 3 นาที)



วิธีคัดลอกแถวอื่น ๆ ใน excel

บันทึก: XLOOKUP และ XMATCH ไม่ได้อยู่ในกลุ่มเดิมของฟังก์ชันอาร์เรย์ไดนามิกใหม่ แต่ทำงานได้ดีบนเอ็นจิ้นอาร์เรย์ไดนามิกใหม่ XLOOKUP เข้ามาแทนที่ VLOOKUP และนำเสนอแนวทางที่ทันสมัยและยืดหยุ่นซึ่งใช้ประโยชน์จากอาร์เรย์ XMATCH เป็นการอัปเกรดเป็นฟังก์ชัน MATCH ซึ่งให้ความสามารถใหม่ๆ แก่ INDEX และ MATCH สูตร

ตัวอย่าง

ก่อนที่เราจะลงรายละเอียด เรามาดูตัวอย่างง่ายๆ กันก่อน ด้านล่างเราใช้ new ฟังก์ชัน UNIQUE เพื่อดึงค่าที่ไม่ซ้ำจากช่วง B5:B15 ด้วย a เดี่ยว สูตรที่ป้อนใน E5:

 
= UNIQUE (B5:B15) // return unique values in B5:B15

ตัวอย่างฟังก์ชัน UNIQUE

ผลลัพธ์คือรายชื่อเมืองที่ไม่ซ้ำกันห้าชื่อ ซึ่งปรากฏใน E5:E9

เช่นเดียวกับสูตรทั้งหมด UNIQUE จะอัปเดตโดยอัตโนมัติเมื่อข้อมูลเปลี่ยนแปลง ด้านล่าง แวนคูเวอร์ได้แทนที่พอร์ตแลนด์ในแถวที่ 11 ผลลัพธ์จาก UNIQUE ในตอนนี้รวมถึงแวนคูเวอร์:

ตัวอย่างฟังก์ชัน UNIQUE หลังการเปลี่ยนแปลง

หกล้ม - สูตรเดียว หลายค่า

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

สี่เหลี่ยมที่ล้อมรอบค่าเรียกว่า ' ช่วงเกม '. คุณจะสังเกตเห็นว่าช่วงการรั่วไหลมีการเน้นเป็นพิเศษ ในตัวอย่าง UNIQUE ด้านบน ช่วงการรั่วไหลคือ E5:E10

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

หมายเหตุ: เมื่อข้อมูลอื่นบล็อกการรั่วไหล คุณจะเห็นข้อผิดพลาด #SPILL เมื่อคุณสร้างที่ว่างสำหรับช่วงการรั่วไหล สูตรจะหกโดยอัตโนมัติ

วิดีโอ: การรั่วไหลและระยะการรั่วไหล

การอ้างอิงช่วงการรั่วไหล

หากต้องการอ้างอิงถึงช่วงการรั่วไหล ให้ใช้สัญลักษณ์แฮช (#) หลังเซลล์แรกในช่วง ตัวอย่างเช่น ในการอ้างอิงผลลัพธ์จากฟังก์ชัน UNIQUE ด้านบน ให้ใช้:

 
=E5# // reference UNIQUE results

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

คุณสามารถป้อนการอ้างอิงช่วงการรั่วไหลลงในสูตรอื่นๆ ได้โดยตรง ตัวอย่างเช่น ในการนับจำนวนเมืองที่ส่งคืนโดย UNIQUE คุณสามารถใช้:

 
= COUNTA (E5#) // count unique cities

ตัวอย่างการอ้างอิงช่วงการรั่วไหลของอาร์เรย์ไดนามิก

เมื่อช่วงการรั่วไหลเปลี่ยนแปลง สูตรจะแสดงข้อมูลล่าสุด

การลดความซับซ้อนอย่างมาก

การเพิ่มสูตรอาร์เรย์ไดนามิกใหม่หมายความว่าสูตรบางสูตรสามารถลดความซับซ้อนได้อย่างมาก นี่คือตัวอย่างบางส่วน:

  • แยกและแสดงรายการค่าที่ไม่ซ้ำ ( ก่อน | หลังจาก )
  • นับค่าที่ไม่ซ้ำ ( ก่อน | หลังจาก )
  • กรองและแยกบันทึก ( ก่อน | หลังจาก )
  • แยกการจับคู่บางส่วน ( ก่อน | หลังจาก )

พลังของหนึ่ง

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

ตัวอย่างเช่น ด้านล่างเราใช้ฟังก์ชัน FILTER เพื่อแยกระเบียนในกลุ่ม 'A' ในเซลล์ F5 มีการป้อนสูตรเดียว:

 
= FILTER (B5:D11,B5:B11='a') // references are relative

อาร์เรย์ไดนามิก ตัวอย่างสูตรเดียวเท่านั้น

สังเกตว่าช่วงทั้งสองถูกปลดล็อกการอ้างอิงแบบสัมพัทธ์ แต่สูตรทำงานได้อย่างสมบูรณ์

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

ฟังก์ชั่นการผูกมัด

สิ่งที่น่าสนใจมากเมื่อคุณเชื่อมโยงฟังก์ชันอาร์เรย์ไดนามิกมากกว่าหนึ่งฟังก์ชันเข้าด้วยกัน บางทีคุณอาจต้องการเรียงลำดับผลลัพธ์ที่ส่งคืนโดย UNIQUE? ง่าย. เพียงแค่ห่อ ฟังก์ชัน SORT รอบฟังก์ชัน UNIQUE ดังนี้:

ตัวอย่าง UNIQUE และ SORT ร่วมกัน

เมื่อข้อมูลต้นฉบับเปลี่ยนแปลงไป ผลลัพธ์ที่ไม่ซ้ำใหม่จะปรากฏขึ้นโดยอัตโนมัติและจัดเรียงอย่างสวยงามเหมือนเมื่อก่อน

พฤติกรรมพื้นเมือง

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

ตัวอย่างเช่น ใน Excel ดั้งเดิม ถ้าเราให้ ฟังก์ชัน LEN ถึง พิสัย ของค่าข้อความ เราจะเห็น a เดี่ยว ผลลัพธ์. ใน Dynamic Excel ถ้าเราให้ช่วงค่าของฟังก์ชัน LEN เราจะเห็น หลายรายการ ผลลัพธ์. หน้าจอด้านล่างนี้แสดงพฤติกรรมเก่าทางด้านซ้ายและพฤติกรรมใหม่ทางด้านขวา:

ฟังก์ชัน LEN พร้อมอาร์เรย์ - เก่าและใหม่

นี่เป็นการเปลี่ยนแปลงครั้งใหญ่ที่สามารถส่งผลต่อสูตรทุกประเภท ตัวอย่างเช่น ฟังก์ชัน VLOOKUP ออกแบบมาเพื่อดึงค่าเดียวจากตาราง โดยใช้ดัชนีคอลัมน์ อย่างไรก็ตาม ใน Dynamic Excel ถ้าเราให้ VLOOKUP มากกว่าหนึ่งดัชนีคอลัมน์โดยใช้ an ค่าคงที่อาร์เรย์ แบบนี้:

 
= VLOOKUP ('jose',F7:H10,{1,2,3},0)

VLOOKUP จะส่งคืนหลายคอลัมน์:

ผลลัพธ์ที่หลากหลายด้วย VLOOKUP และไดนามิกอาร์เรย์

กล่าวอีกนัยหนึ่ง แม้ว่า VLOOKUP ไม่เคยได้รับการออกแบบให้ส่งกลับค่าหลายค่า แต่ตอนนี้สามารถทำได้แล้ว ต้องขอบคุณกลไกจัดการสูตรใหม่ใน Dynamic Excel

ทุกสูตร

สุดท้าย โปรดทราบว่าอาร์เรย์ไดนามิกทำงานร่วมกับ ทุกสูตร ไม่ใช่แค่ ฟังก์ชั่น . ในตัวอย่างด้านล่างเซลล์ C5 มีสูตรเดียว:

วิธีพิมพ์วันที่ใน excel
 
=B5:B14*C4:L4

ผลลัพธ์จะกระจายออกเป็นช่วง 10 คูณ 10 ที่มี 100 เซลล์:

ตารางสูตรคูณไดนามิก

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

อาร์เรย์ไปสู่กระแสหลัก

ด้วยการเปิดตัวไดนามิกอาร์เรย์ คำว่า ' อาร์เรย์ ' จะปรากฎขึ้นบ่อยขึ้น อันที่จริง คุณอาจเห็น 'อาร์เรย์' และ 'ช่วง' ใช้สลับกันได้เกือบ คุณจะเห็นอาร์เรย์ใน Excel อยู่ในวงเล็บปีกกาดังนี้:

 
{1,2,3} // horizontal array {123} // vertical array

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

วิดีโอ: อาร์เรย์คืออะไร?

การทำงานของอาร์เรย์กลายเป็นสิ่งสำคัญ

เนื่องจากสูตร Dynamic Excel สามารถทำงานกับค่าต่างๆ ได้หลายค่า การทำงานของอาร์เรย์จึงมีความสำคัญมากขึ้น คำว่า 'การดำเนินการอาร์เรย์' หมายถึงนิพจน์ที่รันการทดสอบตรรกะหรือการดำเนินการทางคณิตศาสตร์ในอาร์เรย์ ตัวอย่างเช่น นิพจน์ด้านล่างทดสอบว่าค่าใน B5:B9 เท่ากับ 'ca' หรือไม่

 
=B5:B9='ca' // state = 'ca'

การทดสอบตัวอย่างการทำงานของอาร์เรย์ a

เนื่องจากมี 5 เซลล์ใน B5:B9 ผลลัพธ์คือ 5 ค่า TRUE/FALSE ในอาร์เรย์:

 
{FALSETRUEFALSETRUETRUE}

การทำงานของอาร์เรย์ด้านล่างจะตรวจสอบจำนวนที่มากกว่า 100:

 
=C5:C9>100 // amounts > 100

ตัวอย่างการทำงานของอาร์เรย์ การทดสอบ b

การทำงานของอาร์เรย์สุดท้ายรวมการทดสอบ A และการทดสอบ B ในนิพจน์เดียว:

 
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100

ตัวอย่างการทำงานของอาร์เรย์ ทดสอบ a และ b

หมายเหตุ: Excel จะบังคับให้ค่า TRUE และ FALSE เป็น 1 และ 0 โดยอัตโนมัติในระหว่างการดำเนินการทางคณิตศาสตร์

เพื่อนำสิ่งนี้กลับไปที่สูตรอาร์เรย์แบบไดนามิกใน Excel ตัวอย่างด้านล่างแสดงให้เห็นว่าเราสามารถใช้การดำเนินการอาร์เรย์เดียวกันภายในฟังก์ชัน FILTER ได้อย่างไร รวม การโต้แย้ง:

การทำงานของอาร์เรย์ด้วยฟังก์ชัน FILTER

FILTER ส่งคืนสองระเบียนโดยที่ state = 'ca' และจำนวน > 100

สำหรับการสาธิต ดู: วิธีการกรองด้วยสองเกณฑ์ (วิดีโอ).

สูตรอาร์เรย์ใหม่และเก่า

ใน Dynamic Excel ไม่จำเป็นต้องป้อนสูตรอาร์เรย์ด้วย control + shift + enter เมื่อมีการสร้างสูตร Excel จะตรวจสอบว่าสูตรอาจส่งกลับค่าหลายค่าหรือไม่ หากเป็นเช่นนั้น ระบบจะบันทึกสูตรอาร์เรย์แบบไดนามิกโดยอัตโนมัติ แต่คุณจะไม่เห็นวงเล็บปีกกา ตัวอย่างด้านล่างแสดงสูตรอาร์เรย์ทั่วไปที่ป้อนใน Dynamic Excel:

สูตรอาร์เรย์พื้นฐานใน Excel . แบบดั้งเดิม

หากคุณเปิดสูตรเดียวกันใน Excel ดั้งเดิม คุณจะเห็นวงเล็บปีกกา:

สูตรอาร์เรย์พื้นฐานใน Excel แบบไดนามิก

ในอีกทางหนึ่ง เมื่อเปิดสูตรอาร์เรย์ 'ดั้งเดิม' ใน Dynamic Excel คุณจะเห็นวงเล็บปีกกาในแถบสูตร ตัวอย่างเช่น หน้าจอด้านล่างแสดงสูตรอาร์เรย์อย่างง่ายใน Excel แบบดั้งเดิม:

สูตรอาร์เรย์อย่างง่ายพร้อมวงเล็บปีกกาที่มองเห็นได้

อย่างไรก็ตาม หากคุณป้อนสูตรอีกครั้งโดยไม่มีการเปลี่ยนแปลง วงเล็บปีกกาจะถูกลบออก และสูตรจะส่งคืนผลลัพธ์เดียวกัน:

สูตรอาร์เรย์อย่างง่ายที่มองไม่เห็นวงเล็บปีกกา

บรรทัดล่างคือสูตรอาร์เรย์ที่ป้อนด้วย control + shift + enter (CSE) ยังคงทำงานเพื่อรักษาความเข้ากันได้ แต่คุณไม่จำเป็นต้องป้อนสูตรอาร์เรย์ด้วย CSE ใน Dynamic Excel

บทบาท

ด้วยการแนะนำอาร์เรย์แบบไดนามิก คุณจะเห็นอักขระ @ ปรากฏขึ้นบ่อยขึ้นในสูตร อักขระ @ ทำให้เกิดพฤติกรรมที่เรียกว่า ' ทางแยกโดยปริยาย '. การแยกโดยนัยเป็นกระบวนการทางตรรกะที่ค่าจำนวนมากถูกลดเหลือค่าเดียว

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

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

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

สรุป

  • Dynamic Arrays จะทำให้บางสูตรง่ายต่อการเขียน
  • ขณะนี้ คุณสามารถกรองข้อมูลที่ตรงกัน จัดเรียง และแยกค่าที่ไม่ซ้ำได้อย่างง่ายดายด้วยสูตร
  • สูตร Dynamic Array สามารถเชื่อมโยง (ซ้อนกัน) เพื่อทำสิ่งต่างๆ เช่น ตัวกรองและการเรียงลำดับ
  • สูตรที่คืนค่ามากกว่าหนึ่งค่าจะหกโดยอัตโนมัติ
  • ไม่จำเป็นต้องใช้ Ctrl+Shift+Enter เพื่อป้อนสูตรอาร์เรย์
  • สูตรอาร์เรย์แบบไดนามิกมีเฉพาะใน Excel 365
ผู้เขียน Dave Bruns


^