การกรองคอลัมน์แนวนอนใน Excel

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

อย่างไรก็ตาม ไม่มีกฎใดที่ไม่มีข้อยกเว้น และด้วยความถี่ที่ค่อนข้างสม่ำเสมอ ฉันถูกถามว่าต้องทำอย่างไรหากตารางที่มีการวางแนวความหมายในแนวนอน หรือตารางที่แถวและคอลัมน์มีความหมายเท่ากัน มาเจอกันในงาน:

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

วิธีที่ 1 ฟังก์ชัน FILTER ใหม่

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

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

• สมมติว่าเราต้องการแสดงคอลัมน์แรกและคอลัมน์สุดท้ายเสมอ (ส่วนหัวและผลรวม) ดังนั้นในเซลล์แรกและเซลล์สุดท้ายของอาร์เรย์ เราจึงตั้งค่า = TRUE
• สำหรับคอลัมน์ที่เหลือ เนื้อหาของเซลล์ที่เกี่ยวข้องจะเป็นสูตรที่ตรวจสอบเงื่อนไขที่เราต้องการโดยใช้ฟังก์ชัน И (และ) or OR (หรือ). ตัวอย่างเช่น ยอดรวมอยู่ในช่วง 300 ถึง 500

หลังจากนั้นเหลือเพียงการใช้ฟังก์ชั่น FILTER เพื่อเลือกคอลัมน์ด้านบนซึ่งอาร์เรย์เสริมของเรามีค่า TRUE:

ในทำนองเดียวกัน คุณสามารถกรองคอลัมน์ตามรายการที่กำหนด ในกรณีนี้ ฟังก์ชันจะช่วย COUNTIF (เคาน์ตีฟ)ซึ่งตรวจสอบจำนวนครั้งของชื่อคอลัมน์ถัดไปจากส่วนหัวของตารางในรายการที่อนุญาต:

วิธีที่ 2. ตาราง Pivot แทนตารางปกติ

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

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

หากตรงตามเงื่อนไขเหล่านี้ทั้งหมด ในการสร้างตารางเดือยที่ดูเหมือนตารางเดิมของเรา จะต้องขยาย (ตารางเดิม) จากครอสแท็บเป็นตารางแบบเรียบ (ทำให้เป็นมาตรฐาน) และวิธีที่ง่ายที่สุดในการทำเช่นนี้คือด้วย Add-in ของ Power Query ซึ่งเป็นเครื่องมือการแปลงข้อมูลที่มีประสิทธิภาพซึ่งมีอยู่ใน Excel ตั้งแต่ปี 2016 

เหล่านี้คือ:

1. มาแปลงตารางเป็นคำสั่งไดนามิก "อัจฉริยะ" กันเถอะ หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง).
2. กำลังโหลดลงใน Power Query ด้วยคำสั่ง ข้อมูล – จากตาราง / ช่วง (ข้อมูล – จากตาราง / ช่วง).
3. เรากรองบรรทัดด้วยผลรวม (สรุปจะมียอดรวมของตัวเอง)
4. คลิกขวาที่ส่วนหัวของคอลัมน์แรกแล้วเลือก คลายคอลัมน์อื่นๆ (Unpivot คอลัมน์อื่นๆ). คอลัมน์ที่ไม่ได้เลือกทั้งหมดจะถูกแปลงเป็นสองคอลัมน์ – ชื่อพนักงานและค่าของตัวบ่งชี้ของเขา
5. การกรองคอลัมน์ด้วยผลรวมที่เข้าสู่คอลัมน์ คุณลักษณะ.
6. เราสร้างตารางเดือยตามตารางแบน (ปกติ) ที่ได้ผลลัพธ์ด้วยคำสั่ง หน้าแรก — ปิดและโหลด — ปิดและโหลดใน… (หน้าแรก — ปิด & โหลด — ปิด & โหลดไปที่…).

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

และแน่นอนว่าเมื่อเปลี่ยนข้อมูล คุณจะต้องอัปเดตการสืบค้นและข้อมูลสรุปของเราด้วยแป้นพิมพ์ลัด Ctrl+อื่น ๆ+F5 หรือทีม ข้อมูล – รีเฟรชทั้งหมด (ข้อมูล — รีเฟรชทั้งหมด).

วิธีที่ 3 มาโครใน VBA

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

สมมติว่าเราต้องการกรองคอลัมน์ในทันทีโดยที่ชื่อของผู้จัดการในส่วนหัวของตารางตรงกับรูปแบบที่ระบุในเซลล์สีเหลือง A4 เช่น ขึ้นต้นด้วยตัวอักษร "A" (นั่นคือ ได้ "Anna" และ "Arthur" " ผลที่ตามมา). 

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

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

Private Sub Worksheet_Change (ByVal Target As Range) ถ้า Target.Address = "$A$4" จากนั้นสำหรับแต่ละเซลล์ในช่วง ("D2:O2") ถ้าเซลล์ = True แล้ว cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End ถ้าเซลล์ถัดไป End ถ้า End Sub  

ตรรกะของมันมีดังนี้:

• โดยทั่วไป นี่คือตัวจัดการเหตุการณ์ ใบงาน_เปลี่ยนกล่าวคือ มาโครนี้จะทำงานโดยอัตโนมัติเมื่อมีการเปลี่ยนแปลงในเซลล์ใดๆ ในชีตปัจจุบัน
• การอ้างอิงไปยังเซลล์ที่เปลี่ยนแปลงจะอยู่ในตัวแปรเสมอ เป้า.
• ขั้นแรก เราตรวจสอบว่าผู้ใช้ได้เปลี่ยนเซลล์ด้วยเกณฑ์ (A4) อย่างแน่นอน – ซึ่งทำโดยโอเปอเรเตอร์ if.
• จากนั้นวงจรก็เริ่มขึ้น แต่ละ… เพื่อวนซ้ำในเซลล์สีเทา (D2:O2) ด้วยค่าตัวบ่งชี้ TRUE / FALSE สำหรับแต่ละคอลัมน์
• หากค่าของเซลล์สีเทาถัดไปเป็น TRUE (จริง) คอลัมน์จะไม่ถูกซ่อน มิฉะนั้นเราจะซ่อนไว้ (คุณสมบัติ ซ่อนเร้น).

•  ฟังก์ชันอาร์เรย์แบบไดนามิกจาก Office 365: FILTER, SORT และ UNIC
• ตาราง Pivot ที่มีส่วนหัวแบบหลายบรรทัดโดยใช้ Power Query
• มาโครคืออะไร สร้างและใช้งานอย่างไร