ความหมายของการกระจายผลลัพธ์

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

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

สมมติว่า A1:A5 มีตัวเลข 1 ถึง 5 อยู่ ในเซลล์ C1 ให้พิมพ์ =A1:A5*10 แล้วกด Enter

แทนที่จะได้คำตอบเดียว คุณจะได้ห้าคำตอบ ได้แก่ 10, 20, 30, 40, 50 เรียงลงมาใน C1:C5 คุณพิมพ์สูตรใน C1 เพียงเซลล์เดียว แต่ผลลัพธ์จะกระจายไปยังเซลล์ด้านล่างโดยอัตโนมัติ

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

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

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

หากสูตรใน C1 กระจายลงไปถึง C5 C1# จะหมายถึงช่วงผลลัพธ์ที่กระจายทั้งหมด คุณสามารถนำไปใช้ที่อื่นได้ เช่น =SUM(C1#) และผลรวมจะรวมค่าที่กระจายอยู่ทั้งหมดเสมอ แม้ว่าช่วงดังกล่าวจะขยายในภายหลังก็ตาม

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

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

ฟังก์ชันสมัยใหม่หลายรายการได้รับการออกแบบให้ส่งคืนอาร์เรย์ จึงสามารถกระจายผลลัพธ์ได้:

• FILTER ส่งคืนแถวที่ตรงตามเงื่อนไข
• UNIQUE ส่งคืนรายการที่ไม่ซ้ำกัน
• SORT และ SORTBY จัดลำดับช่วงข้อมูลใหม่
• SEQUENCE สร้างรายการตัวเลข

คุณจะได้เรียนรู้ FILTER และ UNIQUE ในหลักสูตรนี้ ทั้งสองฟังก์ชันส่งคืนผลลัพธ์หลายรายการจากสูตรเดียว

สมมติว่าคอลัมน์ A แสดงภูมิภาคดังนี้: East, West, East, North, West หากต้องการรายการภูมิภาคที่ไม่ซ้ำกันและเป็นระเบียบ ให้พิมพ์สูตรเดียว:

=UNIQUE(A2:A6)

สูตรนี้จะกระจายผลลัพธ์สามรายการ ได้แก่ East, West, North เพิ่มภูมิภาคอื่นลงในข้อมูลต้นทาง แล้วรายการที่กระจายจะขยายเอง สูตรเดียวกับรายการที่เป็นปัจจุบันอยู่เสมอ

ผลลัพธ์ที่กระจายไม่ได้จำกัดแค่การเรียงลงด้านล่าง สูตรสามารถกระจายไปตามคอลัมน์ หรือเติมข้อมูลเป็นกริดสองมิติได้

ตัวอย่างเช่น =SEQUENCE(3,4) จะกระจายเป็นกลุ่มข้อมูลสูง 3 แถวและกว้าง 4 คอลัมน์ เติมตัวเลข 1 ถึง 12 ลงใน 12 เซลล์ ทิศทางและรูปร่างจะขึ้นอยู่กับอาร์เรย์ที่สูตรสร้างขึ้นทั้งหมด

อาร์เรย์แบบไดนามิกและการกระจายผลลัพธ์ใช้งานได้ใน Excel for Microsoft 365 และ Excel 2021 และรุ่นที่ใหม่กว่า ส่วน Google Sheets ก็รองรับการกระจายอาร์เรย์มาหลายปีแล้ว

รุ่นเก่าอย่าง Excel 2016 และ 2019 ไม่รองรับการกระจายผลลัพธ์ ในรุ่นเหล่านั้น สูตรอาร์เรย์ต้องยืนยันด้วย Ctrl+Shift+Enter และไม่สามารถขยายโดยอัตโนมัติได้ หากสูตรของคุณไม่ยอมกระจายผลลัพธ์ ให้ตรวจสอบรุ่นที่ใช้งานก่อน

ก่อนมีอาร์เรย์แบบไดนามิก คุณจะเขียน =B2*C2 ในเซลล์หนึ่ง แล้วลากสูตรลงมาหลายร้อยแถว แต่เมื่อมีการกระจายผลลัพธ์ คุณเขียนสูตรเพียงครั้งเดียว

สูตร =B2:B100*C2:C100 จะกระจายผลลัพธ์หนึ่งรายการต่อแถวตลอดทั้งช่วง การดูแลรักษาจึงง่ายขึ้นมาก เพราะมีสูตรเดียวให้อ่านและแก้ไข แทนที่จะมีสำเนาหลายร้อยรายการที่อาจไม่สอดคล้องกัน

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

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

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

• มีเพียงเซลล์ยึดเท่านั้นที่มีสูตร
• อ้างอิงช่วงการกระจายทั้งหมดด้วยตัวดำเนินการ # เช่น C1#
• ช่วงการกระจายจะขยายและหดตามข้อมูลต้นทางโดยอัตโนมัติ
• ฟังก์ชันอย่าง FILTER, UNIQUE, SORT และ SEQUENCE ล้วนกระจายผลลัพธ์ได้

ถัดไป คุณจะใช้ FILTER เพื่อส่งคืนเฉพาะแถวที่ต้องการ