ตัวกรองขั้นสูงและเวทย์มนตร์

สำหรับผู้ใช้ Excel ส่วนใหญ่ เมื่อคำว่า "การกรองข้อมูล" ปรากฏขึ้นในหัว จะมีเพียงตัวกรองคลาสสิกปกติจากแท็บ ข้อมูล – ตัวกรอง (ข้อมูล — ตัวกรอง):

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

ฐาน

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

ต้องมีอย่างน้อยหนึ่งบรรทัดว่างระหว่างเซลล์สีเหลืองกับตารางต้นฉบับ

อยู่ในเซลล์สีเหลืองที่คุณต้องป้อนเกณฑ์ (เงื่อนไข) ตามการกรองที่จะดำเนินการ ตัวอย่างเช่น หากคุณต้องการเลือกกล้วยในมอสโก "Auchan" ในไตรมาสที่ XNUMX เงื่อนไขจะมีลักษณะดังนี้:

ในการกรอง ให้เลือกเซลล์ใดก็ได้ในช่วงที่มีข้อมูลต้นทาง เปิดแท็บ ข้อมูล และคลิก นอกจากนี้ (ข้อมูล — ขั้นสูง). ในหน้าต่างที่เปิดขึ้น ควรป้อนช่วงที่มีข้อมูลโดยอัตโนมัติและเราจะต้องระบุช่วงของเงื่อนไขเท่านั้น เช่น A1:I2:

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

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

การเพิ่มมาโคร

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

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

Private Sub Worksheet_Change (ByVal Target As Range) หากไม่ตัดกัน (Target, Range("A2:I5")) ไม่มีอะไรเกิดขึ้นจากนั้นเมื่อเกิดข้อผิดพลาด Resume ถัดไป ActiveSheet.ShowAllData ช่วง ("A7") CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Range("A1")).CurrentRegion End ถ้า End Sub  

ขั้นตอนนี้จะทำงานโดยอัตโนมัติเมื่อมีการเปลี่ยนแปลงเซลล์ใดๆ บนเวิร์กชีตปัจจุบัน หากที่อยู่ของเซลล์ที่เปลี่ยนแปลงอยู่ในช่วงสีเหลือง (A2:I5) มาโครนี้จะลบตัวกรองทั้งหมด (ถ้ามี) และใช้ตัวกรองแบบขยายอีกครั้งกับตารางข้อมูลต้นทางที่ขึ้นต้นด้วย A7 กล่าวคือ ทุกอย่างจะถูกกรองทันทีในทันที หลังจากเข้าสู่เงื่อนไขถัดไป:

ทุกอย่างดีขึ้นมากใช่มั้ย? 🙂

การใช้คำสั่งที่ซับซ้อน

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

ประเด็นที่ละเอียดอ่อน:

• เครื่องหมาย * หมายถึงจำนวนอักขระใดๆ และ ? - ตัวละครตัวใดตัวหนึ่ง
• ตรรกะในการประมวลผลข้อความค้นหาและตัวเลขจะแตกต่างกันเล็กน้อย ตัวอย่างเช่น เซลล์เงื่อนไขที่มีตัวเลข 5 ไม่ได้หมายถึงการค้นหาตัวเลขทั้งหมดที่ขึ้นต้นด้วยห้า แต่เซลล์เงื่อนไขที่มีตัวอักษร B เท่ากับ B* กล่าวคือ จะค้นหาข้อความใดๆ ที่ขึ้นต้นด้วยตัวอักษร B
• หากข้อความค้นหาไม่ได้ขึ้นต้นด้วยเครื่องหมาย = คุณสามารถใส่ * ต่อท้ายได้
• ต้องป้อนวันที่ในรูปแบบสหรัฐอเมริกา เดือน วัน ปี และเศษส่วน (แม้ว่าคุณจะมีการตั้งค่า Excel และภูมิภาคก็ตาม)

การเชื่อมต่อแบบลอจิคัล AND-OR

เงื่อนไขที่เขียนในเซลล์ต่าง ๆ แต่ในบรรทัดเดียวกัน ถือว่าเชื่อมต่อกันโดยตัวดำเนินการเชิงตรรกะ И (และ):

เหล่านั้น. กรองกล้วยให้ฉันในไตรมาสที่สามอย่างแม่นยำในมอสโกและในเวลาเดียวกันจาก Auchan

หากคุณต้องการเชื่อมโยงเงื่อนไขกับตัวดำเนินการเชิงตรรกะ OR (หรือ)จากนั้นก็ต้องป้อนในบรรทัดต่างๆ ตัวอย่างเช่น หากเราจำเป็นต้องค้นหาคำสั่งซื้อทั้งหมดของผู้จัดการ Volina สำหรับลูกพีชมอสโกและคำสั่งซื้อหัวหอมทั้งหมดในไตรมาสที่สามใน Samara สิ่งนี้สามารถระบุได้ในช่วงของเงื่อนไขดังต่อไปนี้:

หากคุณต้องการกำหนดเงื่อนไขตั้งแต่สองเงื่อนไขขึ้นไปในหนึ่งคอลัมน์ คุณก็สามารถทำสำเนาส่วนหัวของคอลัมน์ในช่วงเกณฑ์และป้อนเงื่อนไขที่สอง สาม ฯลฯ ได้ภายใต้เงื่อนไขนั้น เงื่อนไข ตัวอย่างเช่น คุณสามารถเลือกธุรกรรมทั้งหมดตั้งแต่เดือนมีนาคมถึงพฤษภาคม:

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

• Superfilter บนมาโคร
• มาโครคืออะไร ที่ไหนและอย่างไรที่จะแทรกโค้ดแมโครใน Visual Basic
• ตารางอัจฉริยะใน Microsoft Excel