ต้องการคัดแยกข้อมูล

[gentoo]

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

[snasui]

ลองดูตัวอย่างการทำรายงานด้วย PivotTable ตามด้านล่างครับ

[gentoo]

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

[snasui]

อาจารย์ครับเนื่องจากข้อมูลดิบที่ใช้มีจำนวนมากและเราต้องการเลือกข้อมูลบางช่วงเวลาเพื่อนำมาทำรายงานครับ ซึ่งข้อมูลตาม 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 ลงด้านล่าง

[gentoo]

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