ตารางสรุปด้วยอาร์เรย์แบบไดนามิก

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

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

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

ลองนึกภาพแผ่นงานชื่อ Sales ที่มีสามคอลัมน์ ได้แก่ ภูมิภาคในคอลัมน์ A สินค้าในคอลัมน์ B และจำนวนเงินในคอลัมน์ C โดยมีข้อมูลตั้งแต่แถวที่ 2 ถึง 200

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

• A2:A200 เก็บชื่อภูมิภาคที่ซ้ำกันจำนวนมาก เช่น ตะวันออก ตะวันตก ตะวันออก เหนือ
• เราต้องการเพียง ตะวันออก ตะวันตก เหนือ โดยให้แต่ละรายการปรากฏเพียงครั้งเดียว

รายการที่ไม่ซ้ำกันนี้เป็นแกนหลักของตารางสรุปทั้งหมด

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

พิมพ์สูตรนี้ในเซลล์ E2 แล้วรายการภูมิภาคจะปรากฏขึ้นโดยอัตโนมัติด้านล่าง

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

ตอนนี้เราต้องหาผลรวมของจำนวนเงินสำหรับแต่ละภูมิภาคในคอลัมน์ E SUMIFS จะบวกค่าจากช่วงข้อมูลหนึ่ง เฉพาะเมื่ออีกช่วงข้อมูลหนึ่งตรงกับเงื่อนไข

โครงสร้างคือ SUMIFS(sum_range, criteria_range, criteria) ให้วางสูตรนี้ใน F2 ถัดจากภูมิภาคแรก

การอ้างอิง E2# คือเคล็ดลับสำคัญ # หมายถึงช่วงผลลัพธ์ที่กระจายทั้งหมดจาก E2 ดังนั้นสูตรเดียวนี้จึงหาผลรวมของทุกภูมิภาคที่ UNIQUE สร้างขึ้น

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

เมื่อ UNIQUE พบ 3 ภูมิภาค E2# จะมีความสูง 3 เซลล์ และ SUMIFS จะคืนผลรวม 3 ค่า เมื่อข้อมูลขยายเป็น 5 ภูมิภาค ช่วงข้อมูลทั้งสองก็จะขยายไปพร้อมกันโดยไม่ต้องแก้ไขเลย

• E2 = เฉพาะเซลล์ด้านบนสุดเพียงเซลล์เดียว
• E2# = อาร์เรย์ผลลัพธ์ทั้งหมดที่กระจายโดยเริ่มจาก E2

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

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

หากต้องการแสดงรายการภูมิภาคตามลำดับตัวอักษรใน E2

เนื่องจากผลรวมใน F ยังคงอ้างอิง E2# การเรียงลำดับภูมิภาคจึงจัดตำแหน่งผลรวมให้ตรงกันโดยอัตโนมัติ ทั้งสองคอลัมน์จะสอดคล้องกันเสมอ

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

หากต้องการแสดงแถวการขายทั้งหมดที่ภูมิภาคตรงกับค่าในเซลล์ H1

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

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

อาร์กิวเมนต์ที่สามจะแสดงขึ้นเมื่อไม่พบรายการตรงกันเลย

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

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

วางสูตรนี้ในคอลัมน์ G ถัดจากผลรวม

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

นี่คือสูตรทั้งหมดที่วางเคียงกัน

• E2: =SORT(UNIQUE(Sales!A2:A200)) แสดงรายการภูมิภาค
• F2: =SUMIFS(Sales!C2:C200, Sales!A2:A200, E2#) หาผลรวมของแต่ละภูมิภาค
• G2: =COUNTIFS(Sales!A2:A200, E2#) นับจำนวนของแต่ละภูมิภาค

มีเพียงสูตรใน E2 ที่ต้องพิมพ์ ส่วน F และ G จะกระจายผลลัพธ์จากการอ้างอิง # เพิ่มรายการขายใหม่ที่ใดก็ได้ใน Sales แล้วทั้งสามคอลัมน์จะอัปเดตโดยไม่ต้องคลิกใด ๆ

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

• อัปเดตแบบสด: คำนวณใหม่ทันทีที่ข้อมูลเปลี่ยนแปลง
• ปรับขนาดเอง: หมวดหมู่ใหม่จะปรากฏโดยอัตโนมัติผ่าน UNIQUE และการอ้างอิง #
• โปร่งใส: ทุกคนสามารถอ่านตรรกะในเซลล์ได้

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

คุณได้สร้างตารางสรุปที่ดูแลและอัปเดตตัวเอง

• UNIQUE แสดงแต่ละหมวดหมู่เพียงครั้งเดียวและกระจายผลลัพธ์
• SORT เรียงลำดับรายการนั้นเพื่อให้อ่านง่าย
• SUMIFS และ COUNTIFS หาผลรวมและนับจำนวนของแต่ละหมวดหมู่โดยใช้การอ้างอิงผลลัพธ์ที่กระจาย E2#
• FILTER ดึงแถวที่ตรงกันสำหรับการเจาะลึก พร้อมข้อความสำรองเมื่อไม่พบรายการตรงกัน

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