ฟังก์ชัน Excel ง่าย ๆ ที่ตัดสินใจว่าฟอร์มูลาของคุณจะล้นหรือคืนค่าเพียงค่าเดียว

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

การมาถึงของอาร์เรย์แบบไดนามิกใน Excel สำหรับ Office 2019 และ Microsoft 365 ทำให้เราสามารถเลิกกด Ctrl+Shift+Enter (CSE) เพื่อบังคับให้ใช้ตรรกะแบบอาร์เรย์ได้แล้ว แต่ด้วยพลังที่มากขึ้นก็มาพร้อมกับความจำเป็นในการควบคุมที่มากขึ้น! แค่เพียงตัดสินใจว่าจะแทรกรูปแบบสูตรของฉันไว้ใน SUM หรือไม่ ฉันก็สามารถบังคับให้ผลลัพธ์แสดงออกมาหรือย่อให้เป็นผลลัพธ์เดียวได้

เส้นทาง 1: ละเว้น SUM เพื่อบังคับให้เกิดการกระจาย

พฤติกรรมเริ่มต้นของสูตรอาร์เรย์แบบไดนามิกใดๆ คือการสร้างช่วงที่ล้นออกมา ตัวอย่างเช่น หากคุณป้อน:

=SORT(A:.A)

Excel จะแสดงรายการที่สมบูรณ์และเรียงลำดับแล้ว โดยกระจายลงไปจนถึงระดับที่จำเป็น

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

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

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

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

เส้นทางที่ 2: ใช้ SUM เพื่อคืนค่าหนึ่งค่า

เมื่อเป้าหมายของคุณไม่ใช่รายการตัวแปรแต่เป็นตัวชี้วัดเดียว คุณต้องบอก Excel ให้ ประมวลผล อาร์เรย์นั้น ก่อน ที่มันจะมีโอกาสล้นออกมา นี่คือจุดที่ฟังก์ชัน SUM เข้ามามีบทบาท

ที่นี่ พิมพ์:

=SUM(FILTER(A:A,A:A>50))

ยังคงสร้างอาเรย์ที่กรองแล้วในหน่วยความจำอยู่ แต่ฟังก์ชัน SUM จะรวมค่าทั้งหมดทันทีและส่งกลับเพียงผลรวมสุดท้าย ดังนั้นฉันจึงสามารถเปลี่ยนผลลัพธ์จากอาเรย์ที่กระจายเป็นผลลัพธ์แบบสเกลาร์ (ค่าหนึ่งค่า) ได้สำเร็จ

วิธีนี้ยังทำให้ฉันสามารถใช้ฟังก์ชันอาเรย์แบบไดนามิกในตาราง Excel ได้ด้วย ที่นี่ ฉันต้องการให้คอลัมน์ Total แสดงยอดขายรวมทั้งหมดของแต่ละคนเพื่อที่ฉันจะได้เปรียบเทียบข้อมูลตามภูมิภาคในแต่ละแถวกับตัวเลขรวมทั้งหมดของพวกเขา

ในการทำสิ่งนี้ ฉันห่อฟังก์ชัน FILTER ไว้ภายในตัวรวมอาเรย์ SUM:

=SUM(FILTER([Sales],[Name]=[@Name]))

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

ตัวรวมอาร์เรย์อื่น ๆ

แม้ว่าฉันจะเน้นไปที่ SUM แต่แนวคิดนี้ก็ยังใช้ได้กับกลุ่มฟังก์ชันรวมตัวของ array ทั้งหมด ฟังก์ชันเช่น AVERAGE, MAX, MIN และ COUNT ก็จะใช้ array ป้อนเข้ามาและส่งค่ากลับมาเป็นค่าเดียว ทำให้เกิดการรวมค่าแทนที่จะกระจายค่าออกไป

ที่นี่ ผมได้เปลี่ยนหัวข้อของคอลัมน์ D จาก Total เป็น Average และแทนที่ SUM ด้วย AVERAGE ในสูตร:

=AVERAGE(FILTER([Sales],[Name]=[@Name]))

การใช้ SUM โดยไม่ใช้ฟังก์ชันอาเรย์แบบไดนามิก

ความแตกต่างระหว่างอาร์เรย์ที่กระจายออกกับผลลัพธ์ที่รวมกันจะชัดเจนยิ่งขึ้นเมื่อจัดการกับการดำเนินการอาร์เรย์ที่ไม่เกี่ยวข้องกับฟังก์ชันอาร์เรย์ไดนามิกเฉพาะ

นี่คือวิธีสมัยใหม่ในการทำสิ่งที่ก่อนหน้านี้รู้จักกันในชื่อการคูณเมทริกซ์หรือดอตโปรดักต์—งานที่คุณต้องกด CSE เพื่อคำนวณ

รายงาน How-To Geek

รายงาน How-To Geek

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

สมมติว่าคุณต้องการคูณปริมาณในคอลัมน์ B กับราคาที่อยู่ในคอลัมน์ C

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

=(B2:B8*C2:C8)

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

=SUM((B2:B8*C2:C8))

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

• หากคุณต้องการเลขตัวเดียว: ให้นำสูตรมาห่อด้วย SUM หรือฟังก์ชันตัวรวมอื่นๆ เพื่อประมวลผลอาร์เรย์ ช่วยให้มั่นคงและใช้งานร่วมกับตาราง Excel ได้
• หากคุณต้องการรายการแบบไดนามิก: ปล่อยสูตรว่าง ให้มันเลื่อนไหลออกมาได้ แต่อย่าลืมตรวจสอบว่ามีพื้นที่เพียงพอเพื่อหลีกเลี่ยงข้อผิดพลาด #SPILL!.

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