รายงานแบบตารางสรุปด้วยสูตร

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

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

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

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

รายงานที่เราต้องการมีลักษณะดังนี้

• ป้ายกำกับแถว: ภูมิภาคที่ไม่ซ้ำกันแต่ละรายการเรียงลงในคอลัมน์ E
• ป้ายกำกับคอลัมน์: Q1, Q2, Q3, Q4 เรียงตามแถวที่ 1 ตั้งแต่ F ถึง I
• ส่วนข้อมูล: จำนวนเงินรวมของแต่ละคู่ภูมิภาคและไตรมาส

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

หัวแถวคือภูมิภาคที่ไม่ซ้ำกัน ใช้ UNIQUE ร่วมกับ SORT เพื่อกระจายรายการลงในคอลัมน์ E และรักษาลำดับให้เรียบร้อย

ใส่สูตรนี้ใน E2

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

หัวคอลัมน์คือไตรมาสที่เรียงไปตามแถว คุณสามารถพิมพ์ Q1, Q2, Q3, Q4 ด้วยตนเอง หรือกระจายรายการในแนวนอนโดยครอบ UNIQUE ด้วย TRANSPOSE

ใน F1 สูตรนี้จะวางไตรมาสที่ไม่ซ้ำกันเรียงตามแนวนอนด้านบน

TRANSPOSE จะพลิกรายการแนวตั้งให้เป็นแนวนอน ดังนั้นคอลัมน์ไตรมาสจึงกลายเป็นแถวหัวคอลัมน์ ตอนนี้ทั้งสองแกนของตารางก็พร้อมแล้ว

ตอนนี้ให้เติมส่วนข้อมูล แต่ละเซลล์ต้องมีผลรวมของภูมิภาคในแถวนั้นและไตรมาสในคอลัมน์นั้น SUMIFS จัดการเงื่อนไขสองข้อได้อย่างง่ายดาย

ในเซลล์แรกของส่วนข้อมูล F2 ให้เขียนสูตรนี้

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

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

• $E2 ตรึงคอลัมน์ไว้ที่ E แต่อนุญาตให้แถวเปลี่ยน ดังนั้นแต่ละแถวจึงอ่านภูมิภาคของตัวเอง
• F$1 ตรึงแถวไว้ที่ 1 แต่อนุญาตให้คอลัมน์เปลี่ยน ดังนั้นแต่ละคอลัมน์จึงอ่านไตรมาสของตัวเอง
• $C$2:$C$500 ถูกตรึงไว้อย่างสมบูรณ์ เพราะช่วงข้อมูลจะไม่เลื่อนตำแหน่ง

คัดลอก F2 ไปยังคอลัมน์ไตรมาสทั้งหมดและลงไปยังแถวภูมิภาคทั้งหมด แล้วแต่ละเซลล์จะปรับการอ้างอิงให้ถูกต้องเอง

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

• เซลล์ G2 จะกลายเป็นภูมิภาค $E2 และไตรมาส G$1
• เซลล์ F3 จะกลายเป็นภูมิภาค $E3 และไตรมาส F$1

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

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

สำหรับผลรวมแถวของภูมิภาคแรก ให้วางสูตรนี้ในคอลัมน์ถัดจากไตรมาสสุดท้าย

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

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

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

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

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

หากผลรวมแถวของภูมิภาคอยู่ใน J2 และผลรวมทั้งหมดอยู่ใน J10 ให้เขียนสูตรนี้

การตรึงผลรวมทั้งหมดด้วย $J$10 ช่วยให้คุณเติมสูตรลงไปตามภูมิภาคทั้งหมดได้ โดยสูตรจะหารด้วยตัวหารเดิมเสมอ จัดรูปแบบคอลัมน์เป็นเปอร์เซ็นต์ แล้วผู้อ่านจะเห็นได้ทันทีว่าภูมิภาคใดมีสัดส่วนสูงสุด

แนวปฏิบัติบางอย่างช่วยให้ตารางสรุปข้อมูลด้วยสูตรทำงานได้อย่างน่าเชื่อถือ

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

หากทำอย่างถูกต้อง รายงานนี้แทบไม่ต้องดูแลรักษาเลย เพียงป้อนข้อมูลการขายใหม่ แล้วตาราง ผลรวม และป้ายกำกับทั้งหมดจะอัปเดตตัวเอง

ตรวจสอบความเข้าใจของคุณเกี่ยวกับการอ้างอิงแบบผสมที่ขับเคลื่อนตารางสรุปข้อมูลด้วยสูตร

คุณได้สร้างตารางสรุปข้อมูลขึ้นใหม่โดยใช้สูตรเท่านั้น

• UNIQUE ร่วมกับ SORT สร้างหัวแถวในคอลัมน์ที่กระจายผลลัพธ์
• TRANSPOSE กระจายหัวคอลัมน์ไปตามแถว
• SUMIFS ที่ใช้การอ้างอิงแบบผสม $E2 และ F$1 เติมข้อมูลลงในทุกจุดตัดได้ ไม่ว่าจะใช้วิธีลากสูตรหรือการอ้างอิงผลลัพธ์ที่กระจาย เช่น E2# และ F1#
• SUM เพิ่มผลรวมทั้งหมดที่ขอบตาราง

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