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

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

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

ในบทความนี้เราจะแนะนำขั้นตอนในการสร้างตัวกรองใน Excel และใช้ในตัว

สร้างตัวกรองแบบธรรมดาใน Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

เมื่อพิมพ์ค่าต่างๆ แถวหมายถึงหรือ ตอนนี้เราต้องการครอบครัวนิวออร์ลีนที่มีสมาชิกมากกว่าสองคนและครอบครัวของคลาร์กสวิลล์ที่มีสมาชิกมากกว่า 3 คน โดยพิมพ์ & gt; 2ใน C2 และ >3ใน C3

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

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

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

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

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

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

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

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

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

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

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

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

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

21

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

s>22

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

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

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

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

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

วิธีการกรอง ข้อมูล excel 2007 2010

กระทู้ที่เกี่ยวข้อง: