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

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

จะสร้างตัวกรองขั้นสูงใน Excel ได้อย่างไร

ตัวกรองขั้นสูงช่วยให้คุณสามารถกรองข้อมูลตามเงื่อนไขได้ไม่จำกัด ด้วยเครื่องมือนี้ ผู้ใช้สามารถ:

1. กำหนดเกณฑ์การคัดเลือกมากกว่าสองข้อ
2. คัดลอกผลการกรองไปยังแผ่นงานอื่น
3. กำหนดเงื่อนไขของความซับซ้อนโดยใช้สูตร
4. แยกค่าที่ไม่ซ้ำกัน

อัลกอริทึมสำหรับการใช้ตัวกรองขั้นสูงนั้นง่ายมาก:

ตารางด้านบนคือผลลัพธ์ของการกรอง แผ่นด้านล่างพร้อมเงื่อนไขระบุไว้เพื่อความชัดเจนถัดจากนั้น



วิธีการใช้ตัวกรองขั้นสูงใน Excel?

หากต้องการยกเลิกการทำงานของตัวกรองขั้นสูง ให้วางเคอร์เซอร์ที่ใดก็ได้ในตารางแล้วกดคีย์ผสม Ctrl + Shift + L หรือ "Data" - "Sort and Filter" - "Clear"

ใช้เครื่องมือ "ตัวกรองขั้นสูง" ค้นหาข้อมูลเกี่ยวกับค่าที่มีคำว่า "ตั้งค่า"

มาเพิ่มเกณฑ์ในตารางเงื่อนไขกันเถอะ ตัวอย่างเช่น:

ในกรณีนี้ โปรแกรมจะค้นหาข้อมูลทั้งหมดของผลิตภัณฑ์ที่มีคำว่า "Set" อยู่ในชื่อ

คุณสามารถใช้เครื่องหมาย "=" เพื่อค้นหาค่าที่แน่นอน เพิ่มเกณฑ์ต่อไปนี้ในตารางเงื่อนไข:

Excel ใช้เครื่องหมาย "=" เป็นสัญญาณว่าผู้ใช้กำลังป้อนสูตร เพื่อให้โปรแกรมทำงานได้อย่างถูกต้อง แถบสูตรต้องมีรายการของแบบฟอร์ม: ="=ชุดของภูมิภาค 6 เซลล์"

หลังจากใช้ "ตัวกรองขั้นสูง":

ตอนนี้มากรองตารางต้นฉบับตามเงื่อนไข "OR" สำหรับคอลัมน์ต่างๆ ตัวดำเนินการ "OR" อยู่ในเครื่องมือ "ตัวกรองอัตโนมัติ" ด้วย แต่สามารถใช้ได้ภายในคอลัมน์เดียว

ในตารางเงื่อนไข เราจะแนะนำเกณฑ์การเลือก: ="=ชุดของภูมิภาค 6 เซลล์" (ในคอลัมน์ "ชื่อ") และ ="

โปรดทราบ: เกณฑ์จะต้องเขียนภายใต้หัวข้อที่เหมาะสมในบรรทัดที่แตกต่างกัน

ผลการคัดเลือก:

ตัวกรองขั้นสูงช่วยให้คุณใช้สูตรเป็นเกณฑ์ได้ พิจารณาตัวอย่าง

การเลือกแถวที่มีหนี้สูงสุด: =MAX(Table1[Debt])

ดังนั้นเราจึงได้ผลลัพธ์หลังจากเรียกใช้ตัวกรองหลายตัวในแผ่นงาน Excel เดียวกัน

จะสร้างตัวกรองหลายตัวใน Excel ได้อย่างไร?

มาสร้างตัวกรองตามค่าต่างๆ กัน ในการทำเช่นนี้ เราแนะนำเกณฑ์การเลือกข้อมูลหลายข้อในตารางเงื่อนไขพร้อมกัน:

ใช้เครื่องมือตัวกรองขั้นสูง:

ตอนนี้จากตารางที่มีข้อมูลที่เลือก เราจะดึงข้อมูลใหม่ซึ่งเลือกตามเกณฑ์อื่นๆ ตัวอย่างเช่น เฉพาะการจัดส่งสำหรับปี 2014

เราป้อนเกณฑ์ใหม่ในตารางเงื่อนไขและใช้เครื่องมือกรอง ช่วงเริ่มต้นคือตารางที่มีข้อมูลที่เลือกตามเกณฑ์ก่อนหน้า นี่คือวิธีที่คุณกรองหลายคอลัมน์

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

วิธีการกรองใน Excel ตามแถว?

ไม่เป็นไปตามมาตรฐาน Microsoft Excel เลือกข้อมูลในคอลัมน์เท่านั้น ดังนั้นจึงต้องหาทางออกอื่นๆ

ต่อไปนี้เป็นตัวอย่างของเกณฑ์สตริงตัวกรองขั้นสูงใน Excel:

เพื่อให้ตัวอย่างการทำงานของตัวกรองตามแถวใน Excel เรามาสร้างตารางกัน

25 ม.ค

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

ตัวกรองจำเป็นสำหรับข้อมูลจำนวนมากหรือเมื่อคุณต้องการจัดกลุ่มข้อมูลบนพื้นฐานเดียวกัน

วิธีแรก

เพื่อเปิดใช้งานตัวกรองเราต้องการ:

• เลือกช่วงข้อมูลทั้งหมด (ทั้งตาราง);
• บนแท็บ "หน้าแรก" ในบล็อกปุ่ม "แก้ไข" ให้คลิก "เรียงลำดับและกรอง"
• เลือกบรรทัด "ตัวกรอง" (หรือแป้นพิมพ์ลัด Ctrl + Shift + L)

มาดูกันว่าตารางมีลักษณะอย่างไรหากไม่มีตัวกรอง

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

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

นี่เป็นกรณีที่มีข้อความ (ชื่อ วันในสัปดาห์) หากจำเป็นต้องกรองตัวเลข - กรองตามจำนวนยอดขายสูงสุด - คุณต้องกำหนดค่ารายการที่เกี่ยวข้อง

ตั้งค่ามากกว่าค่าที่โปรแกรมควรแสดงบรรทัดข้อมูล

คลิก "ตกลง" และดูผลลัพธ์ แถวที่มียอดขายมากกว่าหรือเท่ากับ 1,000 จะแสดงขึ้น

วิธีที่สอง

คุณยังสามารถเปิดหรือปิดตัวกรองโดยใช้แมโครธรรมดา

เราใส่โมดูลใหม่ลงในหนังสือของเรา:

• แท็บ "นักพัฒนา" บล็อกปุ่ม "รหัส" ปุ่ม "Visual Basic"
• เพิ่มเติม "แทรก" -\u003e "โมดูล";
• วางข้อความของมาโคร

Sub เปิดใช้งานตัวกรองอัตโนมัติ()

เมื่อเกิดข้อผิดพลาด ให้ดำเนินการต่อ

Selection.ตัวกรองอัตโนมัติ

จบย่อย

แท็บ "นักพัฒนา" บล็อกปุ่ม "รหัส" ปุ่ม "มาโคร" (คุณสามารถใช้ Alt+F8) เราจะแสดงบรรทัดเดียว

คลิก "การตั้งค่า" และในหน้าต่างที่ปรากฏขึ้น ให้ตั้งค่าแป้นพิมพ์ลัดสำหรับเรียกมาโคร Ctrl+W ยืนยัน "ตกลง"

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

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

ขั้นตอนที่ 1: สร้างตารางพร้อมเกณฑ์การเลือก

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

ตอนนี้เราป้อนข้อมูลในตารางเพิ่มเติมที่จะต้องกรองจากตารางหลัก ในกรณีพิเศษของเรา จากรายการค่าจ้างที่ออกให้กับพนักงาน เราตัดสินใจเลือกข้อมูลเกี่ยวกับพนักงานชายหลักสำหรับวันที่ 25/07/2016

ขั้นตอนที่ 2: เรียกใช้ตัวกรองขั้นสูง

หลังจากสร้างตารางเพิ่มเติมแล้ว คุณสามารถดำเนินการเปิดใช้ตัวกรองขั้นสูงได้

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

Microsoft Excel เป็นเครื่องมือสเปรดชีตที่แพร่หลายและสะดวก

การทำงานที่หลากหลายทำให้โปรแกรมนี้ได้รับความนิยมเป็นอันดับสองรองจาก MS Word ในบรรดาโปรแกรมสำนักงานทั้งหมด

มันถูกใช้โดยนักเศรษฐศาสตร์ นักบัญชี นักวิทยาศาสตร์ นักเรียน และตัวแทนของวิชาชีพอื่นๆ ที่ต้องการประมวลผลข้อมูลทางคณิตศาสตร์

หนึ่งในคุณสมบัติที่สะดวกที่สุดในโปรแกรมนี้คือการกรองข้อมูล พิจารณาวิธีตั้งค่าและใช้ตัวกรอง MS excel

ตัวกรองใน Excel อยู่ที่ไหน - ประเภทของตัวกรอง

การค้นหาตัวกรองในโปรแกรมนี้เป็นเรื่องง่าย - คุณต้องเปิดเมนูหลักหรือเพียงแค่กดปุ่ม Ctrl + Shift + L ค้างไว้

ฟังก์ชันการกรองพื้นฐานใน Excel:

• กรองตามสี: ให้คุณจัดเรียงข้อมูลตามแบบอักษรหรือเติมสี
• ตัวกรองข้อความใน excel: อนุญาตให้คุณกำหนดเงื่อนไขบางอย่างสำหรับแถว เช่น น้อยกว่า มากกว่า เท่ากับ ไม่เท่ากับ และอื่นๆ รวมทั้งกำหนดเงื่อนไขเชิงตรรกะ - และ หรือ
• ตัวกรองตัวเลข: จัดเรียงตามเงื่อนไขตัวเลข เช่น ต่ำกว่าค่าเฉลี่ย 10 อันดับแรก และอื่นๆ
• คู่มือ: สามารถเลือกได้ตามเกณฑ์ที่เลือกเอง

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

วิธีใช้ตัวกรองขั้นสูงใน Excel - วิธีปรับแต่ง

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

คำแนะนำวิดีโอ

ลำดับการตั้งค่า:

1. สร้างตารางพร้อมข้อมูลเพื่อใช้งานต่อไป ไม่ควรมีบรรทัดว่าง
2. สร้างตารางที่มีเงื่อนไขการเลือก
3. เรียกใช้ตัวกรองขั้นสูง

ลองดูตัวอย่างการตั้งค่า
เรามีตารางที่มีคอลัมน์สินค้า ปริมาณ และราคา

ตัวอย่างเช่น คุณต้องจัดเรียงแถวที่ชื่อผลิตภัณฑ์ขึ้นต้นด้วยคำว่า "Nails" หลายแถวอยู่ภายใต้เงื่อนไขนี้

ตารางที่มีเงื่อนไขจะอยู่ในเซลล์ A1:A2 สิ่งสำคัญคือต้องระบุชื่อของคอลัมน์ที่จะทำการเลือก (เซลล์ A1) และคำสำหรับการเลือก - เล็บ (เซลล์ A2)

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

จากนั้นคุณต้อง:

1. เลือกเซลล์ใดก็ได้
2. เปิด "ตัวกรองขั้นสูง" ตามเส้นทาง: ข้อมูล - เรียงลำดับและกรอง - ขั้นสูง
3. ตรวจสอบสิ่งที่ระบุไว้ในฟิลด์ "ช่วงเริ่มต้น" - ควรไปที่นั่นทั้งตารางพร้อมข้อมูล
4. ใน "ช่วงของเงื่อนไข" จำเป็นต้องตั้งค่าของเซลล์ที่มีเงื่อนไขการเลือกในตัวอย่างนี้คือช่วง A1:A2

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

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

• เลือกเซลล์ตารางใดก็ได้
• เปิด "ตัวกรองขั้นสูง"
• ตรวจสอบว่าตารางทั้งหมดที่มีข้อมูลรวมอยู่ใน "ช่วงเริ่มต้น"
• ใน "ช่วงของเงื่อนไข" ระบุ B1:B2

หลังจากคลิก "ตกลง" ข้อมูลจะถูกกรองออก

นี่เป็นตัวอย่างที่ง่ายที่สุด ทำงานกับตัวกรองใน excel. ในเวอร์ชันขยาย จะสะดวกในการตั้งค่าเงื่อนไขอื่นๆ สำหรับการเลือก เช่น การออกกลางคันด้วยพารามิเตอร์ “OR” การออกกลางคันด้วยพารามิเตอร์ “Nails” และค่าในคอลัมน์ “Quantity” > 40

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

ตัวอย่างที่ 1

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

ในการรับข้อมูลเกี่ยวกับหลักการย้อนกลับ คุณต้องใช้พารามิเตอร์ "YA"

ตัวอย่างที่ 2

จำเป็นต้องคัดกรองข้อมูลเป็นเดือน และเมืองที่มีปริมาณการขายมากควรอยู่ในตารางที่สูงกว่าเมืองที่มีปริมาณการขายน้อยกว่า ในการแก้ปัญหา คุณต้องเลือกตัวเลือก "เรียงลำดับ" ใน "เรียงลำดับและกรอง" ในหน้าต่างที่ปรากฏขึ้นพร้อมกับการตั้งค่า ให้ระบุ "เรียงตาม" - "เดือน"

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

คำแนะนำวิดีโอ

เหตุใดตัวกรองจึงไม่ทำงานใน Excel

ในการทำงานกับเครื่องมือเช่นตัวกรอง ผู้ใช้มักจะประสบปัญหา โดยปกติแล้วจะเกี่ยวข้องกับการละเมิดกฎสำหรับการใช้การตั้งค่าบางอย่าง

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

วิธีแก้ไขปัญหา:

1. เลือกคอลัมน์ที่มีวันที่
2. เปิดแท็บ Excel ในเมนูหลัก
3. เลือกปุ่ม "เซลล์" ในรายการแบบเลื่อนลง เลือกตัวเลือก "แปลงข้อความเป็นวันที่"

ข้อผิดพลาดทั่วไปของผู้ใช้เมื่อทำงานกับโปรแกรมนี้ควรรวมถึง:

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

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

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