วิธีกรองข้อมูลใน Excel

ฉันเพิ่งเขียนบทความเกี่ยวกับวิธีใช้ฟังก์ชันสรุปใน Excelเพื่อสรุปข้อมูลจำนวนมากอย่างง่ายดาย แต่บทความนั้นคำนึงถึงข้อมูลทั้งหมดในเวิร์กชีต จะทำอย่างไรถ้าคุณต้องการดูข้อมูลชุดย่อยและสรุปข้อมูลชุดย่อย

ใน Excel คุณสามารถสร้างตัวกรองในคอลัมน์ที่จะซ่อนแถวที่ไม่ตรงกับตัวกรองของคุณ นอกจากนี้ คุณยังสามารถใช้ฟังก์ชันพิเศษใน Excel เพื่อสรุปข้อมูลโดยใช้เฉพาะข้อมูลที่กรองเท่านั้น

สารบัญ

• สร้างตัวกรองอย่างง่ายใน Excel
• สร้างตัวกรองขั้นสูงใน Excel
• การสรุปข้อมูลที่กรองแล้ว

ในบทความนี้ ฉันจะแนะนำคุณเกี่ยวกับขั้นตอนต่างๆ สำหรับการสร้างตัวกรองใน Excel และการใช้ฟังก์ชันที่มีอยู่แล้วภายในเพื่อสรุปข้อมูลที่กรองแล้ว

สร้างตัวกรองอย่างง่ายใน Excel

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

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

เมื่อคุณคลิกที่ตัวกรอง แต่ละคอลัมน์ในแถวแรกจะมีปุ่มแบบเลื่อนลงขนาดเล็กเพิ่มที่ด้านขวาสุดโดยอัตโนมัติ

ตอนนี้ไปข้างหน้าและคลิกที่ลูกศรแบบเลื่อนลงในคอลัมน์เมือง คุณจะเห็นตัวเลือกต่างๆ สองสามตัวเลือก ซึ่งฉันจะอธิบายด้านล่าง

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

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

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

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

สมมติว่าฉันต้องการกรองคอลัมน์ที่สองเพื่อลดจำนวนผลลัพธ์เพิ่มเติม ในคอลัมน์ C ฉันมีจำนวนสมาชิกทั้งหมดในแต่ละครอบครัว และฉันต้องการดูผลลัพธ์สำหรับครอบครัวที่มีสมาชิกมากกว่าสองคนเท่านั้น

ไปข้างหน้าและคลิกที่ลูกศรแบบเลื่อนลงในคอลัมน์ C แล้วคุณจะเห็นช่องทำเครื่องหมายเดียวกันสำหรับแต่ละค่าที่ไม่ซ้ำกันในคอลัมน์ อย่างไรก็ตาม ในกรณีนี้ เราต้องการคลิกที่Number Filtersจากนั้นคลิกที่Greater Than อย่างที่คุณเห็นมีตัวเลือกอื่น ๆ มากมายเช่นกัน

กล่องโต้ตอบใหม่จะปรากฏขึ้นและคุณสามารถพิมพ์ค่าสำหรับตัวกรองได้ที่นี่ คุณยังสามารถเพิ่มเกณฑ์มากกว่าหนึ่งเกณฑ์ด้วยฟังก์ชัน AND หรือ OR คุณอาจบอกว่าคุณต้องการแถวที่มีค่ามากกว่า 2 และไม่เท่ากับ 5 เป็นต้น

ตอนนี้ฉันเหลือข้อมูลเพียง 5 แถว: ครอบครัวเฉพาะจากนิวออร์ลีนส์และมีสมาชิก 3 คนขึ้นไป ง่ายพอไหม? โปรดทราบว่าคุณสามารถล้างตัวกรองในคอลัมน์ได้ง่ายๆ โดยคลิกที่รายการดรอปดาวน์ แล้วคลิกลิงก์ล้างตัวกรองจาก "ชื่อคอลัมน์"

ก็แค่นั้นแหละสำหรับตัวกรองอย่างง่ายใน Excel ใช้งานง่ายมากและผลลัพธ์ค่อนข้างตรงไปตรงมา ตอนนี้ มาดูตัวกรองที่ซับซ้อนโดยใช้กล่องโต้ตอบตัวกรองขั้นสูง

สร้างตัวกรองขั้นสูงใน Excel

หากคุณต้องการสร้างตัวกรองขั้นสูง คุณต้องใช้กล่องโต้ตอบตัวกรองขั้นสูง ตัวอย่างเช่น สมมติว่าฉันต้องการเห็นทุกครอบครัวที่อาศัยอยู่ในนิวออร์ลีนส์ที่มีสมาชิกในครอบครัวมากกว่า 2 คนหรือทุกครอบครัวในคลาร์กสวิลล์ที่มีสมาชิกในครอบครัวมากกว่า 3 คนและเฉพาะครอบครัวที่มีที่อยู่อีเมลลงท้าย ด้วย .EDU ตอนนี้คุณไม่สามารถทำได้ด้วยตัวกรองธรรมดา

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

นี่คือวิธีการทำงานของตัวกรองขั้นสูง คุณต้องพิมพ์เกณฑ์ของคุณลงในคอลัมน์ด้านบนก่อน จากนั้นคลิก ปุ่ม ขั้นสูงภายใต้การเรียงลำดับและกรองบนแท็บข้อมูล

แล้วเราจะพิมพ์อะไรลงไปในเซลล์เหล่านั้นได้ล่ะ? ตกลง เรามาเริ่มกันที่ตัวอย่างของเรา เราต้องการดูข้อมูลจาก New Orleans หรือ Clarksville เท่านั้น ดังนั้นลองพิมพ์ข้อมูลเหล่านั้นลงในเซลล์ E2 และ E3

เมื่อคุณพิมพ์ค่าในแถวต่างๆ หมายความว่า OR ตอนนี้เราต้องการครอบครัวนิวออร์ลีนส์ที่มีสมาชิกมากกว่าสองคนและครอบครัวคลาร์กสวิลล์ที่มีสมาชิกมากกว่า 3 คน เมื่อต้องการทำเช่นนี้ พิมพ์>2ใน C2 และ>3ใน C3

เนื่องจาก >2 และนิวออร์ลีนส์อยู่ในแถวเดียวกัน จึงเป็นตัวดำเนินการ AND เช่นเดียวกับแถวที่ 3 ด้านบน สุดท้าย เราต้องการเฉพาะครอบครัวที่มีที่อยู่อีเมลลงท้ายด้วย .EDU ในการทำเช่นนี้ เพียงพิมพ์*.eduลงในทั้ง D2 และ D3 สัญลักษณ์ * หมายถึงอักขระจำนวนเท่าใดก็ได้

เมื่อคุณทำเช่นนั้นแล้ว ให้คลิกที่ใดก็ได้ในชุดข้อมูลของคุณ จากนั้นคลิกที่ปุ่มขั้นสูง ฟิลด์รายการช่วง e จะคำนวณชุดข้อมูลของคุณโดยอัตโนมัติเมื่อคุณคลิกเข้าไปก่อนที่จะคลิกปุ่มขั้นสูง ตอนนี้คลิกที่ปุ่มเล็ก ๆ ที่ด้านขวาของปุ่มช่วงเกณฑ์

เลือกทุกอย่างตั้งแต่ A1 ถึง E3 จากนั้นคลิกที่ปุ่มเดิมอีกครั้งเพื่อกลับไปที่กล่องโต้ตอบตัวกรองขั้นสูง คลิก ตกลง และข้อมูลของคุณควรถูกกรองแล้ว!

อย่างที่คุณเห็น ตอนนี้ฉันมีเพียง 3 ผลลัพธ์ที่ตรงกับเกณฑ์ทั้งหมด โปรดทราบว่าป้ายกำกับสำหรับช่วงเกณฑ์ต้องตรงกับป้ายกำกับสำหรับชุดข้อมูลทั้งหมดจึงจะทำงานได้

เห็นได้ชัดว่าคุณสามารถสร้างข้อความค้นหาที่ซับซ้อนขึ้นได้อย่างมากโดยใช้วิธีนี้ ดังนั้นลองทำดูเพื่อให้ได้ผลลัพธ์ที่คุณต้องการ สุดท้าย เรามาพูดถึงการใช้ฟังก์ชันการรวมกับข้อมูลที่กรอง

การสรุปข้อมูลที่กรองแล้ว

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

ที่ด้านล่างของชุดข้อมูล ให้เพิ่มเซลล์ชื่อTotalแล้วเพิ่มฟังก์ชัน sum เพื่อสรุปจำนวนสมาชิกครอบครัวทั้งหมด ในตัวอย่างของฉัน ฉันเพิ่งพิมพ์ =SUM(C7: C31 )

ถ้าฉันดูครอบครัวทั้งหมด ฉันมีสมาชิกทั้งหมด 78 คน ตอนนี้ ไปข้างหน้าและใช้ตัวกรองขั้นสูงของเราอีกครั้งและดูว่าเกิดอะไรขึ้น

อ๊ะ! แทนที่จะโชว์เลขถูก 11 กลับเห็นรวมเป็น 78! ทำไมถึงเป็นเช่นนั้น? ฟังก์ชัน SUM ไม่ได้ละเว้นแถวที่ซ่อนอยู่ ดังนั้นจึงยังคงทำการคำนวณโดยใช้แถวทั้งหมด โชคดีที่มีฟังก์ชันสองสามอย่างที่คุณสามารถใช้เพื่อละเว้นแถวที่ซ่อนอยู่ได้

อันแรกคือSUBTOTAL ก่อนที่เราจะใช้ฟังก์ชันพิเศษเหล่านี้ คุณจะต้องล้างตัวกรองของคุณแล้วพิมพ์ฟังก์ชัน

เมื่อล้างตัวกรองแล้ว ให้พิมพ์=SUBTOTAL(แล้วคุณจะเห็นกล่องแบบเลื่อนลงปรากฏขึ้นพร้อมกับตัวเลือกมากมาย เมื่อใช้ฟังก์ชันนี้ คุณจะต้องเลือกประเภทของฟังก์ชันการบวกรวมที่คุณต้องการใช้โดยใช้ตัวเลขก่อน

ในตัวอย่างของเรา ฉันต้องการใช้SUMดังนั้นฉันจะพิมพ์เลข 9 หรือคลิกจากดร็อปดาวน์ จากนั้นพิมพ์เครื่องหมายจุลภาคและเลือกช่วงของเซลล์

เมื่อคุณกด Enter คุณควรเห็นค่า 78 เหมือนเดิม อย่างไรก็ตาม หากคุณใช้ตัวกรองอีกครั้ง เราจะเห็น 11!

ยอดเยี่ยม! นั่นคือสิ่งที่เราต้องการ ตอนนี้ คุณสามารถปรับตัวกรองของคุณ และค่าจะแสดงเฉพาะแถวที่แสดงอยู่เท่านั้น

ฟังก์ชันที่สองที่ทำงานเกือบจะเหมือนกับฟังก์ชัน SUBTOTAL คือAGGREGATE ข้อแตกต่างเพียงอย่างเดียวคือมีพารามิเตอร์อื่นในฟังก์ชัน AGGREGATE ซึ่งคุณต้องระบุว่าคุณต้องการละเว้นแถวที่ซ่อนอยู่

พารามิเตอร์แรกคือฟังก์ชันการรวมที่คุณต้องการใช้ และเช่นเดียวกับ SUBTOTAL นั้น 9 หมายถึงฟังก์ชัน SUM ตัวเลือกที่สองคือตำแหน่งที่คุณต้องพิมพ์ 5 เพื่อละเว้นแถวที่ซ่อนอยู่ พารามิเตอร์สุดท้ายเหมือนกันและเป็นช่วงของเซลล์

คุณยังสามารถอ่านบทความของฉันเกี่ยวกับฟังก์ชันสรุปเพื่อเรียนรู้วิธีใช้ฟังก์ชัน AGGREGATE  และฟังก์ชันอื่นๆ เช่น MODE, MEDIAN, AVERAGE และอื่นๆ ได้อย่างละเอียด

หวังว่าบทความนี้จะเป็นจุดเริ่มต้นที่ดีสำหรับการสร้างและใช้ตัวกรองใน Excel หากคุณมีคำถามใด ๆ โปรดอย่าลังเลที่จะแสดงความคิดเห็น สนุก!