gentoo wrote:อาจารย์ครับเนื่องจากข้อมูลดิบที่ใช้มีจำนวนมากและเราต้องการเลือกข้อมูลบางช่วงเวลาเพื่อนำมาทำรายงานครับ ซึ่งข้อมูลตาม File แนบดังกล่าวเป็นส่วนหนึ่งในหน้ารายงานครับจึงไม่สามารถใช้ pivot table ได้ครับ อาจารย์พอจะมีวิธีอื่นไหมครับ ถ้าได้แบบใช้สูตรจะดีมากเลยครับ รบกวนอาจารย์อีกครับ
ข้อมูลยิ่งมากยิ่งจำเป็นต้องใช้ PivotTable ครับ เพราะทำงานได้รวดเร็วและสามารถดูได้หลายมิติ เรื่องช่วงเวลาและการเพิ่มลดข้อมูลไม่ได้เป็นปัญหากับการใช้ PivotTable ครับ
การเขียนสูตรนั้นสามารถทำได้ แต่สูตรจะซับซ้อนจนยากแก่การทำความเข้าใจ เพราะต้องพึ่งสูตรแบบ Array ครับ อีกทั้งข้อมูลมีจำนวนมาก การใช้สูตรจะช้าลงตามปริมาณข้อมูล คือ ข้อมูลยิ่งมากก็ยิ่งช้าคำนวณช้ามาก
ผมเขียนสูตรมาให้เพื่อให้เห็นความซับซ้อนของสูตรที่จะใช้ครับ
จากไฟล์ที่แนบลองตามนี้ และสามารถดูสูตรต่าง ๆ ในไฟล์แนบครับ
1. H1 คีย์เพื่อนับว่ามี kVA ที่ไม่ซ้ำและระบบเป็น 22 มีกี่ตัว
=SUM(IF(FREQUENCY(IF(C3:C16=22,MATCH(B3:B16&C3:C16,B3:B16&C3:C16,0)),ROW(B3:B16)-ROW(B3)+1),1))
Ctrl+Shift+Enter
2. G3 คีย์เพื่อ List kVA ทั้งหมด
=IF(ROWS($G$3:G3)>$H$1,"",SMALL(IF(FREQUENCY(IF($C$3:$C$16=22,MATCH($B$3:$B$16&$C$3:$C$16,$B$3:$B$16&$C$3:$C$16,0)),ROW($B$3:$B$16)-ROW($B$3)+1),$B$3:$B$16),ROWS($G$3:G3)))
Ctrl+Shift+Enter > Copy ลงด้านล่าง
3. H3 คีย์เพื่อ List ระบบตาม kVA ที่พบ
=IF(N(G3),22,"")
Enter > Copy ลงด้านล่าง
4. I3 คีย์เพื่อนับ kVA และระบบตามที่ List มาได้
=SUMPRODUCT(--($B$3:$B$16=G3),--($C$3:$C$16=H3))
Enter > Copy ลงด้านล่าง
5. L1 คีย์สูตรเพื่อนับระบบอื่น
=SUM(IF(FREQUENCY(IF(C3:C16<>22,MATCH(B3:B16&C3:C16,B3:B16&C3:C16,0)),ROW(B3:B16)-ROW(B3)+1),1))
Ctrl+Shift+Enter
6. K3 คีย์สูตรเพื่อ List ระบบอื่น
=IF(ROWS($K$3:K3)>$L$1,"",SMALL(IF(FREQUENCY(IF($C$3:$C$16<>22,MATCH($B$3:$B$16&$C$3:$C$16,$B$3:$B$16&$C$3:$C$16,0)),ROW($B$3:$B$16)-ROW($B$3)+1),$B$3:$B$16),ROWS($K$3:K3)))
Ctrl+Shift+Enter > Copy ลงด้านล่าง
7. L3 คีย์สูตรเพื่อ List ระบบอื่น
=IF(ROWS($K$3:K3)>$L$1,"",INDEX($C$3:$C$16,SMALL(IF($B$3:$B$16=K3,IF($C$3:$C$16<>22,ROW($B$3:$B$16)-ROW($B$3)+1)),COUNTIF(K$3:K3,K3))))
Ctrl+Shift+Enter > Copy ลงด้านล่าง
8. M3 คีย์สูตรเพื่อนับระบบอื่นตาม kVA และระบบที่ List มาได้
=SUMPRODUCT(--($B$3:$B$16=K3),--($C$3:$C$16=L3))
Enter > Copy ลงด้านล่าง