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
ผลลัพธ์คือรายชื่อเมืองที่ไม่ซ้ำกันห้าชื่อ ซึ่งปรากฏใน E5:E9
เช่นเดียวกับสูตรทั้งหมด UNIQUE จะอัปเดตโดยอัตโนมัติเมื่อข้อมูลเปลี่ยนแปลง ด้านล่าง แวนคูเวอร์ได้แทนที่พอร์ตแลนด์ในแถวที่ 11 ผลลัพธ์จาก 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 ดังนี้:
เมื่อข้อมูลต้นฉบับเปลี่ยนแปลงไป ผลลัพธ์ที่ไม่ซ้ำใหม่จะปรากฏขึ้นโดยอัตโนมัติและจัดเรียงอย่างสวยงามเหมือนเมื่อก่อน
พฤติกรรมพื้นเมือง
สิ่งสำคัญคือต้องเข้าใจว่าพฤติกรรมอาร์เรย์แบบไดนามิกคือ a พื้นเมืองและบูรณาการอย่างลึกซึ้ง . เมื่อไหร่ ใด ๆ สูตรส่งกลับผลลัพธ์หลายรายการ ผลลัพธ์เหล่านี้จะกระจายออกเป็นหลายเซลล์บนเวิร์กชีต ซึ่งรวมถึงฟังก์ชันที่เก่ากว่าซึ่งเดิมไม่ได้ออกแบบมาเพื่อทำงานกับอาร์เรย์ไดนามิก
ตัวอย่างเช่น ใน Excel ดั้งเดิม ถ้าเราให้ ฟังก์ชัน LEN ถึง พิสัย ของค่าข้อความ เราจะเห็น a เดี่ยว ผลลัพธ์. ใน Dynamic Excel ถ้าเราให้ช่วงค่าของฟังก์ชัน LEN เราจะเห็น หลายรายการ ผลลัพธ์. หน้าจอด้านล่างนี้แสดงพฤติกรรมเก่าทางด้านซ้ายและพฤติกรรมใหม่ทางด้านขวา:
นี่เป็นการเปลี่ยนแปลงครั้งใหญ่ที่สามารถส่งผลต่อสูตรทุกประเภท ตัวอย่างเช่น ฟังก์ชัน VLOOKUP ออกแบบมาเพื่อดึงค่าเดียวจากตาราง โดยใช้ดัชนีคอลัมน์ อย่างไรก็ตาม ใน Dynamic Excel ถ้าเราให้ VLOOKUP มากกว่าหนึ่งดัชนีคอลัมน์โดยใช้ an ค่าคงที่อาร์เรย์ แบบนี้:
= VLOOKUP ('jose',F7:H10,{1,2,3},0)
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'
เนื่องจากมี 5 เซลล์ใน B5:B9 ผลลัพธ์คือ 5 ค่า TRUE/FALSE ในอาร์เรย์:
{FALSETRUEFALSETRUETRUE}
การทำงานของอาร์เรย์ด้านล่างจะตรวจสอบจำนวนที่มากกว่า 100:
=C5:C9>100 // amounts > 100
การทำงานของอาร์เรย์สุดท้ายรวมการทดสอบ A และการทดสอบ B ในนิพจน์เดียว:
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100
หมายเหตุ: Excel จะบังคับให้ค่า TRUE และ FALSE เป็น 1 และ 0 โดยอัตโนมัติในระหว่างการดำเนินการทางคณิตศาสตร์
เพื่อนำสิ่งนี้กลับไปที่สูตรอาร์เรย์แบบไดนามิกใน Excel ตัวอย่างด้านล่างแสดงให้เห็นว่าเราสามารถใช้การดำเนินการอาร์เรย์เดียวกันภายในฟังก์ชัน FILTER ได้อย่างไร รวม การโต้แย้ง:
FILTER ส่งคืนสองระเบียนโดยที่ state = 'ca' และจำนวน > 100
สำหรับการสาธิต ดู: วิธีการกรองด้วยสองเกณฑ์ (วิดีโอ).
สูตรอาร์เรย์ใหม่และเก่า
ใน Dynamic Excel ไม่จำเป็นต้องป้อนสูตรอาร์เรย์ด้วย control + shift + enter เมื่อมีการสร้างสูตร Excel จะตรวจสอบว่าสูตรอาจส่งกลับค่าหลายค่าหรือไม่ หากเป็นเช่นนั้น ระบบจะบันทึกสูตรอาร์เรย์แบบไดนามิกโดยอัตโนมัติ แต่คุณจะไม่เห็นวงเล็บปีกกา ตัวอย่างด้านล่างแสดงสูตรอาร์เรย์ทั่วไปที่ป้อนใน Dynamic 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