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

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

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

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

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

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

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

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

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

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

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