อาร์เรย์ไดนามิกใน Excel

ไดนามิกอาร์เรย์คืออะไร

ในเดือนกันยายน 2018 Microsoft ได้เปิดตัวการอัปเดตที่เพิ่มเครื่องมือใหม่อย่างสมบูรณ์ให้กับ Microsoft Excel: Dynamic Arrays และ 7 ฟังก์ชันใหม่สำหรับการทำงานร่วมกับพวกเขา สิ่งเหล่านี้เปลี่ยนเทคนิคปกติของการทำงานกับสูตรและฟังก์ชันและข้อกังวลโดยสิ้นเชิงโดยปราศจากการพูดเกินจริงผู้ใช้ทุกคนอย่างแท้จริง

ลองพิจารณาตัวอย่างง่ายๆ เพื่ออธิบายสาระสำคัญ

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

ใน Excel เวอร์ชันก่อนหน้าทั้งหมด หลังจากคลิกที่ เข้าสู่ เราจะได้เนื้อหาของเซลล์ B2 แรกเพียงเซลล์เดียว ยังไงอีก?

หรือไม่ก็เป็นไปได้ที่จะรวมช่วงนี้ไว้ในฟังก์ชันการรวมบางประเภท เช่น =SUM(B2:C4) และรับผลรวมทั้งหมด

หากเราต้องการการดำเนินการที่ซับซ้อนกว่าผลรวมดั้งเดิม เช่น การแยกค่าที่ไม่ซ้ำกันหรือ 3 อันดับแรก เราจะต้องป้อนสูตรของเราเป็นสูตรอาร์เรย์โดยใช้แป้นพิมพ์ลัด Ctrl+เปลี่ยน+เข้าสู่.

ตอนนี้ทุกอย่างแตกต่างกัน

ตอนนี้หลังจากใส่สูตรดังกล่าวแล้ว เราสามารถคลิกที่ เข้าสู่ – และรับผลลัพธ์ทันที ค่าทั้งหมด uXNUMXbuXNUMXbto ที่เราอ้างอิง:

นี่ไม่ใช่เวทย์มนตร์ แต่เป็นไดนามิกอาร์เรย์ใหม่ที่ Microsoft Excel มีในขณะนี้ ยินดีต้อนรับสู่โลกใบใหม่ 🙂

คุณสมบัติของการทำงานกับไดนามิกอาร์เรย์

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

ความพยายามที่จะลบเซลล์ "ลูก" อย่างน้อยหนึ่งเซลล์จะไม่นำไปสู่สิ่งใด - Excel จะคำนวณใหม่และกรอกข้อมูลในเซลล์ทันที

ในเวลาเดียวกัน เราสามารถอ้างถึงเซลล์ "ลูก" เหล่านี้ได้อย่างปลอดภัยในสูตรอื่นๆ:

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

หากเราจำเป็นต้องย้ายอาร์เรย์ก็เพียงพอที่จะย้าย (ด้วยเมาส์หรือการรวมกันของ Ctrl+X, Ctrl+V) อีกครั้ง เฉพาะเซลล์หลักแรก G4 – หลังจากนั้น เซลล์จะถูกย้ายไปยังตำแหน่งใหม่และอาร์เรย์ทั้งหมดของเราจะถูกขยายอีกครั้ง

หากคุณต้องการอ้างอิงที่อื่นในชีตไปยังไดนามิกอาร์เรย์ที่สร้างขึ้น คุณสามารถใช้อักขระพิเศษ # (“pound”) หลังที่อยู่ของเซลล์ชั้นนำได้:

ตัวอย่างเช่น ตอนนี้คุณสามารถสร้างรายการดรอปดาวน์ในเซลล์ที่อ้างอิงถึงอาร์เรย์ไดนามิกที่สร้างขึ้นได้อย่างง่ายดาย:

ข้อผิดพลาดอาร์เรย์แบบไดนามิก

แต่จะเกิดอะไรขึ้นถ้าไม่มีพื้นที่ว่างเพียงพอที่จะขยายอาร์เรย์ หรือมีเซลล์ที่ถูกครอบครองโดยข้อมูลอื่นในเส้นทางของมันอยู่แล้ว พบข้อผิดพลาดประเภทใหม่โดยพื้นฐานใน Excel – #โอนย้าย! (#หก!):

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

ข้อผิดพลาดที่คล้ายกันจะเกิดขึ้นหากอาร์เรย์หลุดออกจากแผ่นงานหรือพบเซลล์ที่ผสาน หากคุณนำสิ่งกีดขวางออก ทุกอย่างจะได้รับการแก้ไขทันที

ไดนามิกอาร์เรย์และตารางอัจฉริยะ

หากไดนามิกอาร์เรย์ชี้ไปที่ตาราง "อัจฉริยะ" ที่สร้างโดยแป้นพิมพ์ลัด Ctrl+T หรือโดยการ หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง)จากนั้นจะสืบทอดคุณภาพหลัก - การปรับขนาดอัตโนมัติ

เมื่อเพิ่มข้อมูลใหม่ที่ด้านล่างหรือด้านขวา ตารางอัจฉริยะและช่วงไดนามิกจะขยายโดยอัตโนมัติเช่นกัน:

อย่างไรก็ตาม มีข้อจำกัดอยู่อย่างหนึ่ง: เราไม่สามารถใช้การอ้างอิงช่วงไดนามิกในฟอรัมภายในตารางอัจฉริยะ:

ไดนามิกอาร์เรย์และคุณสมบัติอื่นๆ ของ Excel

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

ไม่เชิง

อาร์เรย์แบบไดนามิกไม่ได้เป็นเพียงเครื่องมืออื่นใน Excel ตอนนี้พวกเขาฝังอยู่ในหัวใจ (หรือสมอง) ของ Microsoft Excel ซึ่งเป็นเครื่องมือคำนวณ ซึ่งหมายความว่าสูตรและฟังก์ชัน Excel อื่นๆ ที่เราคุ้นเคยในขณะนี้ยังสนับสนุนการทำงานกับอาร์เรย์ไดนามิกอีกด้วย ลองมาดูตัวอย่างบางส่วนเพื่อให้คุณได้ทราบถึงความลึกของการเปลี่ยนแปลงที่เกิดขึ้น

ไขว้

ในการเปลี่ยนช่วง (สลับแถวและคอลัมน์) Microsoft Excel มีฟังก์ชันในตัวเสมอ ทรานส์ (โอน). อย่างไรก็ตาม ในการใช้งาน คุณต้องเลือกช่วงผลลัพธ์ให้ถูกต้องก่อน (เช่น หากอินพุตเป็นช่วง 5×3 คุณต้องเลือก 3×5) จากนั้นเข้าสู่ฟังก์ชันแล้วกดปุ่ม การผสมผสาน Ctrl+เปลี่ยน+เข้าสู่เนื่องจากสามารถทำงานได้ในโหมดสูตรอาร์เรย์เท่านั้น

ตอนนี้คุณสามารถเลือกเซลล์เดียว ป้อนสูตรเดียวกันลงในเซลล์แล้วคลิกที่เซลล์ปกติ เข้าสู่ – ไดนามิกอาร์เรย์จะทำทุกอย่างด้วยตัวเอง:

ตารางสูตรคูณ

นี่คือตัวอย่างที่ฉันใช้เมื่อถูกขอให้เห็นภาพประโยชน์ของสูตรอาร์เรย์ใน Excel ทีนี้ ในการคำนวณตารางพีทาโกรัสทั้งหมด ก็เพียงพอที่จะยืนอยู่ในเซลล์แรก B2 ป้อนสูตรที่คูณสองอาร์เรย์ (ชุดตัวเลข 1..10 ในแนวตั้งและแนวนอน) แล้วคลิก เข้าสู่:

การติดกาวและการแปลงเคส

อาร์เรย์ไม่เพียงแต่สามารถคูณได้เท่านั้น แต่ยังติดกาวร่วมกับตัวดำเนินการมาตรฐาน & (เครื่องหมายและ) สมมติว่าเราต้องแยกชื่อและนามสกุลออกจากสองคอลัมน์และแก้ไขตัวพิมพ์ใหญ่ในข้อมูลเดิม เราทำสิ่งนี้ด้วยสูตรสั้นๆ สูตรเดียวที่สร้างอาร์เรย์ทั้งหมด จากนั้นเราจึงนำฟังก์ชันไปใช้กับมัน พรนัช (เหมาะสม)เพื่อจัดระเบียบการลงทะเบียน:

สรุปอันดับ3

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

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

VLOOKUP แยกหลายคอลัมน์พร้อมกัน

ฟังก์ชั่น VPR (วีลุคอัพ) ตอนนี้คุณสามารถดึงค่าไม่ได้จากหนึ่ง แต่จากหลายคอลัมน์ในครั้งเดียว - เพียงแค่ระบุตัวเลข (ตามลำดับที่ต้องการ) เป็นอาร์เรย์ในอาร์กิวเมนต์ที่สามของฟังก์ชัน:

ฟังก์ชัน OFFSET ส่งกลับอาร์เรย์ไดนามิก

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

ตอนนี้ปัญหานี้อยู่ในอดีต ดูว่าตอนนี้โดยใช้สูตรเดียวและอาร์เรย์ไดนามิกที่ส่งกลับโดย OFFSET คุณสามารถแยกแถวทั้งหมดสำหรับผลิตภัณฑ์ที่กำหนดจากตารางที่จัดเรียงแล้ว:

ลองดูข้อโต้แย้งของเธอ:

• A1 – เซลล์เริ่มต้น (จุดอ้างอิง)
• นโยบายความเป็นส่วนตัว(F2;A2:A30;0) – การคำนวณการเปลี่ยนแปลงจากเซลล์เริ่มต้น – เป็นกะหล่ำปลีที่พบครั้งแรก
• 0 – เลื่อน “หน้าต่าง” ไปทางขวาที่สัมพันธ์กับเซลล์เริ่มต้น
  
• СЧЁТЕСлИ(A2:A30;F2) – การคำนวณความสูงของ "หน้าต่าง" ที่ส่งคืน – จำนวนบรรทัดที่มีกะหล่ำปลี
• 4 — ขนาดของ "หน้าต่าง" ในแนวนอน เช่น เอาต์พุต 4 คอลัมน์

ฟังก์ชันใหม่สำหรับ Dynamic Arrays

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

• ระดับการศึกษา (เรียงลำดับ) – จัดเรียงช่วงอินพุตและสร้างอาร์เรย์ไดนามิกบนเอาต์พุต
  
• สพป (เรียงลำดับ) – สามารถจัดเรียงช่วงหนึ่งตามค่าจากอีกช่วงหนึ่งได้
  
• FILTER (กรอง) – ดึงแถวจากช่วงต้นทางที่ตรงตามเงื่อนไขที่กำหนด
• ยูนิค (มีเอกลักษณ์) – แยกค่าที่ไม่ซ้ำจากช่วงหรือลบค่าที่ซ้ำกัน
  
• บางเฉียบ (รันดาร์เรย์) – สร้างอาร์เรย์ของตัวเลขสุ่มของขนาดที่กำหนด
• รก (ลำดับ) — สร้างอาร์เรย์จากลำดับของตัวเลขด้วยขั้นตอนที่กำหนด
  

ข้อมูลเพิ่มเติมเกี่ยวกับพวกเขา – ในภายหลัง มีค่าเป็นบทความแยกต่างหาก (และไม่ใช่หนึ่งบทความ) เพื่อการศึกษาอย่างรอบคอบ 🙂

สรุป

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

สรุปผลคือ ข้อดี ไดนามิกอาร์เรย์ คุณสามารถเขียนสิ่งต่อไปนี้:

• คุณสามารถลืมเกี่ยวกับการรวมกัน Ctrl+เปลี่ยน+เข้าสู่. ตอนนี้ Excel ไม่เห็นความแตกต่างระหว่าง "สูตรปกติ" และ "สูตรอาร์เรย์" และปฏิบัติต่อพวกเขาในลักษณะเดียวกัน
• เกี่ยวกับฟังก์ชั่น SUMPRODUCT (ซัมโปรดักส์)ซึ่งก่อนหน้านี้ใช้ในการป้อนสูตรอาร์เรย์โดยไม่ต้อง Ctrl+เปลี่ยน+เข้าสู่ คุณยังสามารถลืม – ตอนนี้มันง่ายพอ SUM и เข้าสู่.
• ตารางอัจฉริยะและฟังก์ชันที่คุ้นเคย (SUM, IF, VLOOKUP, SUMIFS เป็นต้น) รองรับอาร์เรย์ไดนามิกทั้งหมดหรือบางส่วนแล้ว
• มีความเข้ากันได้แบบย้อนหลัง: หากคุณเปิดเวิร์กบุ๊กที่มีอาร์เรย์ไดนามิกใน Excel เวอร์ชันเก่า เวิร์กบุ๊กเหล่านั้นจะเปลี่ยนเป็นสูตรอาร์เรย์ (ในวงเล็บปีกกา) และทำงานใน "รูปแบบเก่า" ต่อไป

พบเลขเด็ด ข้อเสีย:

• คุณไม่สามารถลบแต่ละแถว คอลัมน์ หรือเซลล์ออกจากอาร์เรย์ไดนามิก นั่นคือ ใช้งานเป็นเอนทิตีเดียว
• คุณไม่สามารถจัดเรียงอาร์เรย์ไดนามิกด้วยวิธีปกติผ่าน ข้อมูล – การเรียงลำดับ (ข้อมูล — เรียงลำดับ). ขณะนี้มีฟังก์ชั่นพิเศษสำหรับสิ่งนี้ ระดับการศึกษา (เรียงลำดับ).
• ไม่สามารถเปลี่ยนช่วงไดนามิกเป็นตารางอัจฉริยะได้ (แต่คุณสามารถสร้างช่วงไดนามิกตามตารางอัจฉริยะได้)
  

แน่นอนว่านี่ไม่ใช่จุดจบ และฉันมั่นใจว่า Microsoft จะทำการปรับปรุงกลไกนี้ต่อไปในอนาคต

ฉันจะดาวน์โหลดได้ที่ไหน

และสุดท้ายคำถามหลัก 🙂

Microsoft ประกาศครั้งแรกและแสดงตัวอย่างอาร์เรย์แบบไดนามิกใน Excel ย้อนกลับไปในเดือนกันยายน 2018 ในการประชุม จุดชนวน. ในอีกไม่กี่เดือนข้างหน้า มีการทดสอบอย่างละเอียดและเรียกใช้คุณสมบัติใหม่ เริ่มแรกใน แมว พนักงานของ Microsoft เอง และอาสาสมัครทดสอบจากวง Office Insiders ในปีนี้ การอัปเดตที่เพิ่มอาร์เรย์ไดนามิกเริ่มทยอยเปิดตัวให้กับสมาชิก Office 365 ปกติ ตัวอย่างเช่น ฉันได้รับในเดือนสิงหาคมด้วยการสมัครใช้งาน Office 365 Pro Plus (กำหนดเป้าหมายรายเดือน) เท่านั้น

หาก Excel ของคุณยังไม่มีอาร์เรย์แบบไดนามิก แต่คุณต้องการทำงานกับอาร์เรย์เหล่านี้จริงๆ มีตัวเลือกต่อไปนี้:

• หากคุณมีการสมัครใช้งาน Office 365 คุณสามารถรอจนกว่าการอัปเดตนี้จะมาถึงคุณ เหตุการณ์นี้เกิดขึ้นได้เร็วเพียงใดขึ้นอยู่กับความถี่ในการส่งการอัปเดตไปยัง Office ของคุณ (ปีละครั้ง ทุกๆ หกเดือน และเดือนละครั้ง) หากคุณมีพีซีขององค์กร คุณสามารถขอให้ผู้ดูแลระบบตั้งค่าการอัปเดตให้ดาวน์โหลดบ่อยขึ้นได้
  
• คุณสามารถเข้าร่วมกับอาสาสมัครทดสอบ Office Insiders เหล่านั้นได้ แล้วคุณจะเป็นคนแรกที่ได้รับฟีเจอร์และฟังก์ชันใหม่ทั้งหมด (แต่แน่นอนว่ามีโอกาสเกิดข้อผิดพลาดใน Excel มากขึ้น)
• หากคุณไม่มีการสมัครใช้งาน แต่เป็น Excel เวอร์ชันสแตนด์อโลนชนิดบรรจุกล่อง คุณจะต้องรอจนกว่า Office และ Excel เวอร์ชันถัดไปจะออกในปี 2022 เป็นอย่างน้อย ผู้ใช้เวอร์ชันดังกล่าวจะได้รับเฉพาะการอัปเดตความปลอดภัยและการแก้ไขจุดบกพร่อง และตอนนี้ "สินค้า" ใหม่ทั้งหมดจะให้บริการเฉพาะกับสมาชิก Office 365 เท่านั้น เศร้าแต่จริง🙂
  

ไม่ว่าในกรณีใด เมื่ออาร์เรย์ไดนามิกปรากฏใน Excel ของคุณ – หลังจากบทความนี้ คุณจะพร้อมสำหรับมัน 🙂

• สูตรอาร์เรย์คืออะไรและใช้งานอย่างไรใน Excel
• ผลรวมของหน้าต่าง (ช่วง) โดยใช้ฟังก์ชัน OFFSET
• 3 วิธีในการเปลี่ยนตารางใน Excel