ฟังก์ชัน UNIQUE ของ Excel ไม่สามารถข้ามคอลัมน์ได้—เว้นแต่ว่าคุณจะใช้เคล็ดลับนี้

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

พื้นฐาน: วิธีที่ UNIQUE มักทำงาน

ฟังก์ชัน UNIQUE ของ Excel ซึ่งสามารถใช้งานได้สำหรับผู้ที่ใช้เวอร์ชันแยกของ Excel ที่ออกในปี 2021 หรือหลังจากนั้น, Excel สำหรับ Microsoft 365, Excel สำหรับเว็บ และแอปมือถือและแท็บเล็ตที่อัปเดตล่าสุด ใช้ไวยากรณ์ดังนี้:

=UNIQUE(array,[by_col],[exactly_once])

ที่ไหน :

• array (จำเป็น): คือช่วงหรือ ตารางที่คุณต้องการดึงค่าที่ไม่ซ้ำออกมา
• by_col (ไม่จำเป็น): บอก Excel ให้เปรียบเทียบคอลัมน์ (หากตั้งค่าเป็น TRUE) แทนการเปรียบเทียบแถว (ค่าเริ่มต้นเป็น FALSE)
• exactly_once (ไม่จำเป็น): ส่งคืนรายการที่ปรากฏเพียงครั้งเดียวในแหล่งข้อมูล (หากตั้งค่าเป็น TRUE) แทนที่จะเป็นรายการของค่าที่แตกต่างกันทั้งหมด (ค่าปริยายคือ FALSE)

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

การดึงคอลัมน์ที่อยู่ติดกัน

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

เนื่องจากนี่คือตัวคอลัมน์ที่อยู่ติดกันในตารางของคุณ คุณสามารถเชื่อมโยงพวกมันเข้าด้วยกันโดยใช้เครื่องหมายจุดคู่ในอาร์กิวเมนต์ array:

=UNIQUE(T_Expenses[[Category]:[Store]])

ปัญหากับการดึงคอลัมน์ที่ไม่ติดกัน

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

หากคุณพยายามเลือกเฉพาะคอลัมน์เฉพาะเหล่านั้นเป็นอาร์กิวเมนต์แยกต่างหากในสูตร UNIQUE ของคุณ คุณจะเห็นข้อผิดพลาด #VALUE! เกิดขึ้น เหตุผลคือ UNIQUE ต้องการอาร์เรย์ที่ต่อเนื่องกันเพียงชุดเดียว เมื่อคุณเลือกคอลัมน์สองคอลัมน์แยกกันโดยมีเครื่องหมายจุลภาค Excel จะพยายามใช้คอลัมน์ที่สองเป็นอาร์กิวเมนต์ by_col แต่เนื่องจากอาร์กิวเมนต์นี้ต้องเป็น TRUE หรือ FALSE สูตรจึงล้มเหลว

นี่คือสองวิธีในการแก้ปัญหานี้

วิธีแก้ไขอย่างรวดเร็ว: การซ้อน CHOOSECOLS ภายใน UNIQUE

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

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

=UNIQUE(CHOOSECOLS(T_Expenses,2,4))

CHOOSECOLS จะดูทั้งตาราง T_Expenses และละเว้นทุกอย่างยกเว้นคอลัมน์ที่ 2 และ 4 จากนั้นจะส่งตารางเสมือนสองคอลัมน์ใหม่นี้ไปยังฟังก์ชัน UNIQUE ซึ่งจะทำการตรวจสอบและแสดงผลลัพธ์ออกมา

การแก้ไขที่มั่นคง: ใช้ MATCH สำหรับหัวข้อแบบไดนามิก

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

เพื่อทำให้สูตรของคุณไม่สามารถถูกทำลายได้ คุณสามารถใช้ฟังก์ชัน MATCH เพื่อค้นหาคอลัมน์ตามชื่อของพวกมันแทน:

=UNIQUE(
CHOOSECOLS(
T_Expenses,
MATCH(G1,T_Expenses[#Headers],0),
MATCH(H1,T_Expenses[#Headers],0)
))

ที่นี่ แทนที่จะพิมพ์ 2 และ 4 ด้วยตัวเอง ฟังก์ชัน MATCH จะทำงานให้คุณ MATCH แรกดูข้อความในเซลล์ G1 ("Category") และค้นหาในแถวหัวของตารางของคุณ ตัวเลข 0 ที่ท้ายฟังก์ชันบอกให้ Excel หาค่าที่ตรงกันเป๊ะ จากนั้นมันจะส่งกลับตัวเลขที่สัมพันธ์กับช่วงที่เลือกในหน่วยความจำ—ถ้า Category เป็นคอลัมน์ที่สอง ผลลัพธ์จะเป็น 2 MATCH ตัวที่สองก็ทำแบบเดียวกันกับข้อความในเซลล์ H1 ("Method") ส่งกลับค่าเป็น 4

นี่คือวิธีแก้ไขที่แน่นอนด้วยเหตุผลดังต่อไปนี้:

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

เคล็ดลับโปร: สร้างเมนูแบบเลื่อนลงในหัวตารางโดยใช้ฟังก์ชัน INDIRECT

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

อย่างไรก็ตาม มีอุปสรรคทางเทคนิค: เครื่องมือการตรวจสอบข้อมูลของ Excel ไม่รองรับการอ้างอิงแบบมีโครงสร้างโดยตรง (เช่น T_Expenses[#Headers]) คุณสามารถหลีกเลี่ยงปัญหานี้ได้โดยใช้ฟังก์ชัน INDIRECT ซึ่งจะเปลี่ยนข้อความเป็นการอ้างอิงที่ถูกต้องซึ่งการตรวจสอบข้อมูลสามารถเข้าใจได้

อันดับแรก เลือกเซลล์ที่คุณต้องการใส่หัวข้อแบบไดนามิก (ในกรณีนี้คือ G1 และ H1) จากนั้นบนแท็บข้อมูล คลิกที่ไอคอน "การตรวจสอบความถูกต้องของข้อมูล"

ตอนนี้ ในเมนูแบบเลื่อนลง Allow ให้เลือก "List" และป้อนสูตร INDIRECT ดังต่อไปนี้ลงในช่อง Source:

=INDIRECT("T_Expenses[#Headers]")

เมื่อคุณคลิก "ตกลง" กล่องโต้ตอบจะปิด และเซลล์ G1 และ H1 จะมีลูกศรที่คลิกได้ แสดงทุกหัวข้อในตาราง T_Expenses ของคุณ

ทันทีที่คุณเลือกชื่อคอลัมน์ใหม่จากรายการ ฟังก์ชัน MATCH จะหาตำแหน่งใหม่ของมัน, CHOOSECOLS จะดึงข้อมูล และ UNIQUE จะอัปเดตรายการของคุณทันที นอกจากนี้ เนื่องจากฟังก์ชัน INDIRECT กำลังมองไปที่หัวตารางโดยเฉพาะ คอลัมน์ใหม่ใดๆ ที่คุณเพิ่มในตาราง T_Expenses จะปรากฏในเมนูดรอปดาวน์โดยอัตโนมัติโดยที่คุณไม่ต้องอัปเดตการตั้งค่า Data Validation

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