การรวมข้อมูลที่มีการเว้นค่าเป็นช่วง ๆ
ปกติรายงานที่ได้มาจากสรุปข้อมูลแล้วมีการเว้นช่วงเอาไว้เช่นรายงานที่ได้จาก PivotTable แล้วมีการ Copy ค่าตามรูปแบบเดิม ๆ มาใช้ จากภาพด้านล่างจะเห็นว่าคอลัมน์ B มีการเว้นข้อมูลไว้เป็นช่วง ๆ การจะให้ง่ายต่อการใช้งานต้องเติม Field ด้านล่างให้เหมือนด้านบน แล้วใช้สูตร Sumif เข้ามาช่วย
แต่หากต้องการจะใช้ความสามารถของสูตรต่าง ๆ เพื่อสรุปข้อมูลโดยไม่ต้องปรับแต่งฐานข้อมูลก็สามารถทำได้แต่สูตรค่อนข้างซับซ้อนและยากต่อการทำความเข้าใจ แต่ก็เป็นประโยชน์ในการนำเสนอแนวความคิด เพื่อนำไปต่อยอดในการใช้งานด้านอื่น ๆ ครับ
ภาพประกอบ
การที่เราจะรวมข้อมูลในอลัมน์ D จึงต้องประยุกต์สูตรต่าง ๆ มาใช้ โดยคีย์สูตรที่ D5 ดังนี้
=IF(AND(LOOKUP(CHAR(255),$B$2:B5)=LOOKUP(CHAR(255),$B$2:B6),C6=""),SUM(OFFSET($C$1,MATCH(9.99999999999999E+307,C:C),0):OFFSET($C$1,MATCH(9.99999999999999E+307,$D$2:D4)+1,0)),IF(LOOKUP(CHAR(255),$B$2:B5)<>LOOKUP(CHAR(255),$B$2:B6),SUM(OFFSET($C$1,MATCH(CHAR(255),$B$2:B5),0):OFFSET($C$1,MATCH(CHAR(255),$B$2:B6)-1,0)),""))
โดยมีสูตรสำคัญ ๆ ดังนี้
1. LOOKUP(CHAR(255),$B$2:B5) เป็นการหาค่าอักขระสุดท้ายในช่วง B2:B5
2. OFFSET(
):OFFSET(
) เป็นการนำผลลัพธ์ของสูตร OFFSET มาเป็นช่วงข้อมูลใหม่
3. MATCH(CHAR(255),$B$2:B5),0) เป็นการหาตำแหน่งของอักขระสุดท้ายในช่วง B2:B5
4. MATCH(9.99999999999999E+307,C:C) เป็นการหาตำแหน่งสุดท้ายที่พบตัวเลขในช่วง C:C
| Create Date : 20 มีนาคม 2553 |
| Last Update : 1 มีนาคม 2556 21:59:46 น. |
|
2 comments
|
| Counter : 1468 Pageviews. |
 |
|
|
ฝากข้อความหลังไมค์
Rss Feed
ผู้ติดตามบล็อก : 27 คน [
