วิธีสร้าง Running Number ข้อมูลที่ถูก Filter ใน Excel

หลายครั้งที่เรา ใช้ Excel แล้วใส่ Filter  เพื่อดูกลุ่มข้อมูลที่สนใจ และพบปัญหาว่าลำดับข้อมูลที่เคยเรียงอยู่ ไม่เรียงเหมือนเดิม เช่น  1,3,7,8 แล้วจะทำยังไงดีล่ะ

ซึ่งใน Excel จะมี Function SUBTOTAL ซึ่งช่วยทำการคำนวณเฉพาะข้อมูลที่ยังคงมองเห็นอยู่หลังถูก Filter ไปแล้วโดย

รูปแบบคำสั่งของ SUBTOTAL คือ SUBTOTAL (function_num, ref1, [ref2], …)

โดยจะมี function_code ฟังก์ชันที่จะใช้ในการรวมผลย่อย

1 คือ AVERAGE

2 คือ COUNT

3 คือ COUNTA

4 คือ MAX

5 คือ MIN

6 คือ PRODUCT

7 คือ STDEV

8 คือ STDEVP

9 คือ SUM

10 คือ VAR

11 คือ VARP

ในที่นี้เราใช้ code 3 (COUNTA) ในการนับจำนวนข้อมูลจากแถวแรกถึงแถวที่จะใส่เลขลำดับนั้นๆ เพื่อให้รู้ว่ามีข้อมูลกี่รายการหลังถูก Filter ไปแล้ว

ตัวอย่างวันนี้
เป็นข้อมูลจากการสำรวจ size เสื้อขององค์กร เมื่อเรา Filter หน่วยงานที่สังกัด เพื่อที่จะดูว่าใครสั่ง size เสื้ออะไรบ้าง สามารถเขียนสูตรในแถวที่ 2 ได้ ดังนี้ =SUBTOTAL(3,B2:B2) โดยคอลัมน์ที่เราใช้ในสูตร ต้องไม่มีค่าว่าง

ในตัวอย่างนี้จะใช้ คอลัมน์ B (ชื่อ-สกุล) หรือ C (หน่วยงานที่สังกัด) ก็ได้

การ copy สูตร ควรจะ fix cell Reference เพื่อไม่ให้สูตรเลื่อน จะได้เป็น

=SUBTOTAL(3,B$2:B2)

ซึ่งสูตรนี้จะไม่นับแถวสุดท้ายของการ filter จึงต้อง คูณด้วย *1 เพื่อให้เอาแถวสุดท้ายของการ filter มานับด้วย สุดท้ายจึงผลลัพธ์ของสูตรออกมา

=SUBTOTAL(3,B$2:B2)*1

จากนั้นเราสามารถ copy สูตรมาใช้ได้ทั้งคอลัมน์

เมื่อทำการ filter หลังจากใช้สูตร จากรูปสังเกตในช่องสูตรลำดับที่ 1 เดิมจะเป็นข้อมูล แถวที่ 2 คอลัมน์ที่ 3 นั้นเอง

ขอขอบคุณ :https://www.inwexcel.com

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.