ฟังก์ชัน Excel 3 ตัว ใน 1 เซลล์: วิธีสร้างเครื่องมือแดชบอร์ดระดับมืออาชีพ

หยุดเสียเวลาเป็นชั่วโมงในการจัดเรียงข้อมูล ลบข้อมูลซ้ำ และกรองข้อมูลใน Excel ด้วยตัวเอง แทนที่จะทำเช่นนั้น ให้รวมฟังก์ชัน FILTER, UNIQUE และ SORTBY เพื่อสร้างเครื่องมือจัดการข้อมูลที่ทำความสะอาดได้เอง ซึ่งทำงานทั้งหมดจากเซลล์เดียวและไม่ต้องอัปเดตใด ๆ

การรวมฟังก์ชันนี้ขึ้นอยู่กับอาร์เรย์แบบไดนามิก ซึ่งสามารถใช้ได้เฉพาะใน Excel สำหรับ Microsoft 365, Excel 2021 หรือเวอร์ชันที่ใหม่กว่า และ Excel สำหรับเว็บเท่านั้น

วิธีการทำงานของชุด FILTER-UNIQUE-SORTBY

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

=SORTBY(
UNIQUE(FILTER(array,include,[if_empty])),
UNIQUE(FILTER(array,include,[if_empty])),
[sort_order]
)

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

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

ขั้นตอนที่ 1 แกนหลัก: กรอง

ทุกอย่างเริ่มต้นด้วย FILTER ห้องเครื่องที่ช่วยระบุข้อมูลดิบที่คุณต้องการดึงออกมา:

FILTER(array,include,[if_empty])

array คือช่วงของเซลล์หรือโต๊ะที่คุณต้องการกรอง
include คือเกณฑ์ที่บอก Excel ว่าจะเก็บอะไรไว้ในตัวกรอง
[if_empty] (ไม่จำเป็น) คือส่วนที่คุณระบุว่า Excel ควรจะแสดงอะไรหากไม่พบข้อมูลที่ตรงกัน

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

ขั้นตอนที่ 2 ชั้นกลาง: เอกลักษณ์

ต่อมา UNIQUE ทำหน้าที่เหมือน "ยาม" กำจัดค่าซ้ำทั้งหมดออกจากอาร์เรย์ที่ถูกกรองที่เพิ่งได้รับมา:

UNIQUE(FILTER(...))

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

ขั้นตอนที่ 3 เปลือกด้านนอก: SORTBY

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

=SORTBY(array,sort_array,[sort_order])

อาเรย์ คือผลลัพธ์ของสองขั้นตอนก่อนหน้านี้
sort_array คือตรรกะที่ Excel ใช้ในการจัดเรียงรายการ (ดูขั้นตอนที่ 4 ด้านล่าง)
[sort_order] (ไม่บังคับ) บอก Excel ทิศทางของการเรียงลำดับ ใช้ 1 สำหรับเรียงลำดับจากน้อยไปมาก (A-Z) หรือ -1 สำหรับเรียงลำดับจากมากไปน้อย (Z-A) ในทางกลับกัน ปล่อยว่างไว้เพื่อใช้ค่าเริ่มต้น (เรียงลำดับจากน้อยไปมาก)

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

ขั้นตอนที่ 4 ตรรกะของกระจก

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

นั่นคือเหตุผลที่คุณต้องสะท้อนตรรกะโดยการทำซ้ำโซ่ UNIQUE(FILTER(...)) เป็นอาร์กิวเมนต์ที่สอง—เพื่อให้แน่ใจว่ามิติตรงกันอย่างสมบูรณ์ทุกครั้ง

=SORTBY(
UNIQUE(FILTER(array,include,[if_empty])), <-- the data to sort 
UNIQUE(FILTER(array,include,[if_empty])), <-- the instructions to sort by 
[sort_order]
)

สามอัจฉริยะในการทำงาน: รายชื่อลูกค้า

ตอนนี้ที่ฉันได้แสดงแผนผังให้คุณดูแล้ว มาดูเครื่องยนต์ทำงานกัน ลองนึกภาพว่าคุณมีตาราง Excel ชื่อ T_Sales และต้องการรายการลูกค้าเฉพาะที่เรียงตามตัวอักษรแบบสด ๆ สำหรับภูมิภาคที่เลือกในเซลล์ G2

เมื่อคุณพิมพ์สูตรนี้ลงในเซลล์ G4 สูตรจะกระจายผลลัพธ์ลงไปยังเซลล์ด้านล่าง:

=SORTBY(
UNIQUE(FILTER(T_Sales[Company],T_Sales[Region]=G2,"No Matches")),
UNIQUE(FILTER(T_Sales[Company],T_Sales[Region]=G2,"No Matches")),
1
)

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

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

G4#

ข้อควรระวังขั้นสุดท้ายที่ต้องหลีกเลี่ยง

เพื่อให้เครื่องยนต์ข้อมูลใหม่ของคุณทำงานได้อย่างราบรื่น ให้จดจำกฎสามข้อเหล่านี้ไว้:

ล้างทาง: สูตรอาร์เรย์แบบไดนามิกจำเป็นต้องมีพื้นที่ว่างเพื่อแสดงผลลัพธ์ลงด้านล่าง หากมีข้อความหรือโต๊ะอื่นขวางอยู่ Excel จะแสดงข้อผิดพลาด #SPILL!
ห้ามใช้สูตรในตาราง: แม้ว่าคุณควรใช้ตารางเป็นข้อมูลต้นทาง แต่สูตรของคุณต้องอยู่ในเซลล์ปกติ เนื่องจากตารางใน Excel ไม่รองรับการ spill แบบไดนามิก
เว้นช่องคอลัมน์เดียว: เมื่อวิเคราะห์ข้อมูลนอกตาราง Excel ให้เว้นช่องคอลัมน์หนึ่งระหว่างตารางกับตำแหน่งที่คุณพิมพ์ มิฉะนั้น Excel จะคิดว่าคุณกำลังเพิ่มข้อมูลลงในตาราง จึงอาจ "จับ" การวิเคราะห์ของคุณไปด้วย

โดยการเลิกใช้เครื่องมือ Excel แบบแมนนวลและหันมาใช้ dynamic arrays คุณจะสร้างระบบที่เติบโตไปพร้อมกับข้อมูลของคุณ กลุ่มฟังก์ชันนี้เป็นจุดเริ่มต้นที่สมบูรณ์แบบสำหรับการสำรวจการรวมฟังก์ชัน Excel อื่น ๆ เช่น INDEX กับ XMATCH, IF กับ AND และ OR, และ EOMONTH กับ SEQUENCE ซึ่งสามารถช่วยทำให้งานประจำวันของคุณเป็นอัตโนมัติได้มากขึ้น