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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ที่ด้านล่างของชุดข้อมูล ให้เพิ่มเซลล์ชื่อ ทั้งหมด แล้วเพิ่มฟังก์ชันผลรวมเพื่อสรุปสมาชิกครอบครัวทั้งหมด ในตัวอย่างของฉัน ฉันเพิ่งพิมพ์ =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 หากคุณมีคำถามใด ๆ โปรดโพสต์ความคิดเห็น สนุก!