Microsoft Excel Tips and Tricks: การสรุปข้อมูลจากฐานข้อมูลที่แบ่งเป็นช่วง ๆ

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

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

จากภาพด้านล่างจะเป็นการสรุปข้อมูลตามที่ต้องการโดยการเลือก Region และ Product โปรแกรมจะแสดงยอดรวม ทั้งแจกแจงรายการทั้งหมดที่ตรงตาม Region และ Product

ภาพตัวอย่างการสรุปข้อมูลจากฐานข้อมูลที่แบ่งเป็นช่วง ๆ


โดยมีวิธีการและขั้นตอนดังนี้
A. List รายการของ Product ทั้งหมด

1. นับ Product ที่มีทั้งหมดโดยไม่นับค่าที่ซ้ำ H1 คีย์
   =SUM(IF(FREQUENCY(IF(ISNUMBER($B$1:$B$39),MATCH("~"&$A$1:$A$39,$A$1:$A$39&"",0)),ROW($B$1:$B$39)-ROW($B$1)),1))Ctrl+Shift+Enter


2. List รายการ Product ทั้งหมดโดยไม่เอาค่าซ้ำที่ H2 คีย์
   =IF(ROWS($H$2:H2)<=$H$1,INDEX($A$1:$A$39,SMALL(IF(FREQUENCY(IF(ISNUMBER($B$1:$B$39),MATCH("~"&$A$1:$A$39,$A$1:$A$39&"",0)),ROW($A$1:$A$39)-ROW($A$1)+1),ROW($A$1:$A$39)-ROW($A$1)+1),ROWS($H$2:H2))),"")Ctrl+Shift+Ener > Copy ลงด้านล่าง

B. List รายการ Region ทั้งหมด

1. นับจำนวน Region โดยไม่เอาค่าซ้ำที่ G1 คีย์
   =SUMPRODUCT(--(B1:B39=""),--(A1:A39<>""))
   Enter


2. List Region ทั้งหมด G2 คีย์
   =IF(ROWS($G$2:G2)<=$G$1,INDEX($A$1:$A$39,SMALL(IF($B$1:$B$39="",IF($A$1:$A$39<>"",ROW($B$1:$B$39)-ROW($B$1)+1)),ROWS($G$2:G2))),"")
   Ctrl+Shift+Ener > Copy ลงด้านล่าง

C. หาตำแหน่งเซลล์ว่างสุดท้ายในบรรทัดด้านล่างถัดจาก Region ที่เลือกใน E2 ที่ E1 คีย์

=SMALL(IF(A1:A39="",ROW(A1:A39)-ROW(A1)+1),MATCH(E2,G2:G7,0))Ctrl+Shift+Ener

D. List รายการที่ตรงตามเงื่อนไขทั้ง Region และ Prod ที่เลือกใน E2 และ E3 ตามลำดับ

1. นับว่าตรงตามเงื่อนไขทั้งหมดมีจำนวนเท่าไรที่ D6 คีย์
   =COUNTIF(OFFSET(A2,MATCH(E2,$A$1:$A$39,0),0,E1-MATCH(E2,$A$1:$A$39,0)),E3)
   Enter


2. List Product ทั้งหมดที่ตรงตามเงื่อนไข ที่ D8 คีย์
   =IF(ROWS($D$8:D8)<=$D$6,INDEX(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)),SMALL(IF(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0))=$E$3,ROW(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)))-MATCH($E$2,$A$1:$A$39,0)-1),ROWS($D$8:D8))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง


3. List Value ทั้งหมดที่ตรงตามเงื่อนไข ที่ E8 คีย์
   =IF(ROWS($D$8:D8)<=$D$6,INDEX(OFFSET($B$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)),SMALL(IF(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0))=$E$3,ROW(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)))-MATCH($E$2,$A$1:$A$39,0)-1),ROWS($D$8:D8))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

E. สูตรหายอดรวมรายการที่ตรงตามเงื่อนไขโดยไม่ต้องแจกแจงรายการออกมาก่อน ที่ E4 คีย์

=SUMIF(OFFSET(A2,MATCH(E2,$A$1:$A$39,0),0,E1-MATCH(E2,$A$1:$A$39,0)),E3,OFFSET(B2,MATCH(E2,$A$1:$A$39,0),0,E1-MATCH(E2,$A$1:$A$39,0)))Enter