ใน Microsoft Excel มีหลายวิธีสร้างรายการเฉพาะเจาะจง (unique list) ภายใต้เกณฑ์หลายอย่าง รายการเฉพาะเจาะจงโดยทั่วไปเตรียมเพื่อลบรายการซ้ำจากตาราง ในบทความนี้ คุณจะเรียนรู้วิธีใช้วิธีต่างๆ สร้างรายการเฉพาะเจาะจงตามเงื่อนไขหลายอย่าง
คุณสามารถดาวน์โหลดสมุดงาน Excel ที่เราใช้เตรียมบทความนี้
9 วิธี สร้างรายการเฉพาะเจาะจงใน Excel ตามเกณฑ์
1. สร้างรายการแถวเฉพาะเจาะจงด้วยเกณฑ์หลายคอลัมน์
ในภาพต่อไป ตารางด้านซ้ายวางอยู่กับชื่อกีฬาสุ่มๆ ผู้เข้าร่วมและอายุ ตารางด้านขวาคือตารางผลลัพธ์ที่เราจะดึงข้อมูลตามค่าที่ไม่ซ้ำเท่านั้น ตัวอย่าง เราต้องการทราบชื่อผู้เข้าร่วมที่ไม่ซ้ำกัน พร้อมอายุของพวกเขา
เราจะใช้ฟังก์ชัน UNIQUE ที่นี่ ซึ่งคืนค่าที่ไม่ซ้ำจากช่วงหรืออาร์เรย์ สูตรทั่วไปของฟังก์ชันนี้คือ:
=UNIQUE(array, [by_col], [exactly_once])
แม้ฟังก์ชัน UNIQUE จะมีเฉพาะใน Excel 365 เราจะแสดงวิธีทางเลือกด้วยการรวมฟังก์ชันพื้นฐานอื่นๆ ใน 3 ส่วนสุดท้าย ก่อนสรุป
ดังนั้น ฟังก์ชันที่จำเป็นใน เซลล์ F5 จะเป็น:
=UNIQUE(B5:C13,FALSE,FALSE)หลังกด Enter ฟังก์ชันจะคืนอาร์เรย์พร้อมชื่อเฉพาะเจาะจง พร้อมอายุที่สอดคล้อง
อ่านเพิ่มเติม: วิธีสร้างรายการตามเกณฑ์ใน Excel (4 วิธี)
2. ได้รายการค่าที่ไม่ซ้ำ จัดเรียงตามตัวอักษร
เรายังสามารถได้ค่าที่ไม่ซ้ำ จัดเรียงตามตัวอักษร โดยใช้ฟังก์ชัน SORT โดยระบุลำดับการจัดเรียงแบบขึ้น (A to Z) หรือลง (Z to A) เราสามารถจัดเรียงชื่อผู้เข้าร่วมตามตัวอักษรต้นได้
ใน เซลล์ E5 สูตรที่จำเป็นกับฟังก์ชัน SORT และ UNIQUE จะเป็น:
=SORT(UNIQUE(B5:C13,FALSE,FALSE),,1)หลังกด Enter ฟังก์ชันจะคืนอาร์เรย์จัดเรียงตามตัวอักษร A to Z
อ่านเพิ่มเติม: วิธีสร้างรายการตามตัวอักษรใน Excel (3 วิธี)
3. สร้างรายการค่าที่ไม่ซ้ำ รวมเป็นเซลล์เดียว
สมมติว่า เราต้องการดึงข้อมูลเฉพาะเจาะจงจากสองคอลัมน์ แล้วรวมค่าเรียงต่อกัน เพื่อคืนค่าที่ต่อกันในคอลัมน์เดียว ในภาพต่อไป ชื่อเฉพาะเจาะจงและอายุที่สอดคล้อง จะแสดงร่วมกันในคอลัมน์ โดยใช้ตัวคั่นเฉพาะ - จุลภาค (,) และเราจะใช้ Ampersand (&) ที่นี่ เพื่อต่อค่าเรียงติดกันตามสองคอลัมน์ที่ต่างกัน
สูตรที่จำเป็นใน เซลล์ F5 จะเป็น:
=UNIQUE(B5:B13&", "&C5:C13)สูตรจะคืนอาร์เรย์แถวที่ไม่ซ้ำในคอลัมน์เดียว
อ่านเพิ่มเติม: วิธีสร้างรายการคั่นจุลภาคใน Excel (5 วิธี)
4. สร้างรายการค่าที่ไม่ซ้ำ พร้อมเกณฑ์ (สูตร UNIQUE-FILTER)
i. ระบุค่าที่ไม่ซ้ำ ตามเกณฑ์ AND หลายอย่างใน Excel
ในส่วนนี้ เราจะเพิ่มเกณฑ์บ้าง และดึงข้อมูลเฉพาะเจาะจงตามเงื่อนไขเหล่านั้น ตัวอย่าง เราต้องการทราบชื่อผู้เข้าร่วมที่ไม่ซ้ำ ที่เข้าร่วมว่ายน้ำอย่างเดียว และอายุต่ำกว่า 25 ปี ดังนั้น เราต้องรวมฟังก์ชัน UNIQUE และ FILTER เพื่อกรองข้อมูลด้วยเกณฑ์ที่กำหนด
สูตรที่จำเป็นใน เซลล์ F5 จะเป็น:
=UNIQUE(FILTER(B5:C13,(D5:D13=G9)*(C5:C13<G10)))
หลังกด Enter สูตรจะคืนอาร์เรย์พร้อมชื่อเฉพาะเจาะจงและอายุที่สอดคล้อง ตามเกณฑ์ที่เลือก
ii. ค้นหาค่าที่ไม่ซ้ำ ตามเกณฑ์ OR หลายอย่าง
สมมติว่า ตอนนี้เราต้องการทราบชื่อผู้เข้าร่วมที่ไม่ซ้ำ ที่เข้าร่วมกีฬากลางแจ้งทั้งสองชนิด คือ ว่ายน้ำและปั่นจักรยาน เนื่องจากเรากำหนดเกณฑ์จากคอลัมน์เดียว เราต้องใช้การบวกตัวเลข เพื่อรวมเงื่อนไขสองอย่างในฟังก์ชัน FILTER
สูตรที่จำเป็นใน เซลล์ F5 จะเป็น:
=UNIQUE(FILTER(B5:C13,(D5:D13=F11)+(D5:D13=F12)))
กด Enter และคุณจะได้ข้อมูลเฉพาะเจาะจงที่ดึงมา ทันที ตามภาพด้านล่าง
iii. ได้รายการค่าที่ไม่ซ้ำ โดยข้ามเซลล์ว่าง
ชุดข้อมูลของเราอาจมีเซลล์ว่างหรือแถวว่างหลายช่อง ดังนั้น หากเราต้องการข้ามแถวว่างเหล่านั้น และดึงข้อมูลเฉพาะเจาะจงจากตาราง เราต้องใช้ตัวดำเนินการเปรียบเทียบ: Not Equal To (<>) ในสูตร
สูตรที่จำเป็นใน เซลล์ F5 จะเป็น:
=UNIQUE(FILTER(B5:C13,D5:D13<>""))
หลังกด Enter สูตรจะคืนอาร์เรย์ต่อไปนี้ พร้อมแถวที่ไม่ซ้ำและไม่ว่าง ในตารางผลลัพธ์
การอ่านที่คล้ายกัน
- วิธีสร้าง To Do List ใน Excel (3 วิธีง่าย)
- Creating a Mailing List in Excel (2 Methods)
5. หารายการค่าที่ไม่ซ้ำ ในคอลัมน์ที่ระบุใน Excel
สมมติว่า เราต้องการดึงข้อมูลเฉพาะเจาะจงจากคอลัมน์เฉพาะบางคอลัมน์เท่านั้น ไม่สามารถเลือกคอลัมน์ที่แตกต่างด้วยเมาส์ และใส่เป็นอาร์กิวเมนต์สำหรับฟังก์ชัน UNIQUE ได้ ดังนั้น เราต้องรวมฟังก์ชัน CHOOSE ที่นี่ กับฟังก์ชัน UNIQUE เนื่องจาก CHOOSE จะให้คุณเลือกคอลัมน์ที่ระบุหรือช่วงเซลล์จำนวนใดก็ได้ ตามหมายเลขดัชนีจากรายการค่า
จากตารางข้อมูลของเรา เราจะดึงชื่อผู้เข้าร่วมที่ไม่ซ้ำ และแสดงผลลัพธ์จากคอลัมน์ Name (Column B) และ Sports Name (Column D) เท่านั้น
สูตรที่จำเป็นใน เซลล์ F5 จะเป็น:
=UNIQUE(CHOOSE({1,3},B5:B13,C5:C13,D5:D13))
หลังกด Enter สูตรจะคืนชื่อเฉพาะเจาะจงและชื่อกีฬาที่สอดคล้อง จากสองคอลัมน์ที่แตกต่างกัน ตามภาพด้านล่าง
ภายในฟังก์ชัน CHOOSE หมายเลขดัชนีคือ 1 and 3 ซึ่งหมายความว่า ช่วงเซลล์ที่ 1 และ 3 ต้องเลือกจากรายการค่า ฟังก์ชัน UNIQUE จึงพิจารณาคอลัมน์ที่ระบุเท่านั้น และคืนข้อมูลเฉพาะเจาะจงจากคอลัมน์เหล่านั้นในอาร์เรย์
อ่านเพิ่มเติม: วิธีสร้างรายการหมายเลขใน Excel (8 วิธี)
6. การใช้ฟังก์ชัน IFERROR ขณะสร้างรายการเฉพาะเจาะจง
เราสามารถใช้ฟังก์ชัน IFERROR หลังฟังก์ชัน UNIQUE เพื่อแสดงข้อความที่กำหนดเอง หากพบข้อผิดพลาดในค่าที่คืน สมมติว่า เราต้องการทราบชื่อผู้เข้าร่วมที่ไม่ซ้ำ ที่อายุต่ำกว่า 21 เนื่องจากไม่มีผู้เข้าร่วมคนไหนอายุต่ำกว่า 21 สูตรควรคืนข้อผิดพลาด #N/A แต่แทนที่จะแสดงข้อผิดพลาดนี้ เราจะใช้ฟังก์ชัน IFERROR แสดงข้อความกำหนดเอง - “Not Found”
ดังนั้น สูตรที่จำเป็นใน เซลล์ F5 จะเป็น:
=IFERROR(UNIQUE(FILTER(B5:C13,C5:C13<=G10)), "Not Found")
หลังกด Enter สูตรจะคืนข้อความที่ระบุ ตามภาพด้านล่าง
7. ดึงรายการเฉพาะเจาะจงตามเกณฑ์ (สูตร INDEX-MATCH)
ตอนนี้เราจะไม่ใช้ฟังก์ชัน UNIQUE และใช้การรวมของฟังก์ชัน INDEX และ MATCH ฟังก์ชัน INDEX คืนค่าหรืออ้างอิงเซลล์ที่ตัดกันของแถวและคอลัมน์เฉพาะ และฟังก์ชัน MATCH คืนตำแหน่งของรายการที่ตรงกับค่าที่ระบุในลำดับที่ระบุ สมมติว่า เราต้องการทราบชื่อผู้เข้าร่วมที่ไม่ซ้ำ ที่เข้าร่วมว่ายน้ำอย่างเดียว
📌 ขั้นตอนที่ 1:
➤ เลือก เซลล์ F5 และพิมพ์:
=INDEX(B5:B13, MATCH(0, IF($F$12=$D$5:$D$13,COUNTIF($F$4:$F4, $B$5:$B$13), ""), 0))➤ กด Enter
คุณจะได้ชื่อเฉพาะเจาะจงแรกเป็นค่าที่คืน
📌 ขั้นตอนที่ 2:
➤ ใช้ Fill Handle ลากเซลล์ไปทางขวา
และคุณจะพบอายุของผู้เข้าร่วมด้วย
📌 ขั้นตอนที่ 3:
➤ จาก เซลล์ G5 ลากลงคอลัมน์ จนกว่าจะเจอค่า #N/A
ดังนั้น นี่คือวิธีดึงข้อมูลเฉพาะเจาะจงตามเงื่อนไขที่กำหนด ด้วยสูตร INDEX-MATCH
🔎 สูตรทำงานอย่างไร?
- COUNTIF($F$4:$F4, $B$5:$B$13): ฟังก์ชัน COUNTIF ที่นี่ใช้เก็บและนับเซลล์ทั้งหมดในช่วง B5:B13 และฟังก์ชันคืนค่าเป็น:
{0;0;0;0;0;0;0;0;0}
- IF($F$12=$D$5:$D$13, COUNTIF($F$4:$F4, $B$5:$B$13), “”): ฟังก์ชัน IF ค้นหาเกณฑ์ที่กำหนดในเซลล์ และคืนค่าเป็น:
{0;””;0;””;””;0;””;””;””}
- ฟังก์ชัน MATCH คืนหมายเลขแถวของเซลล์ที่พบในขั้นตอนก่อน
- สุดท้าย ฟังก์ชัน INDEX ดึงข้อมูลตามหมายเลขแถวนั้น
8. เตรียมรายการเฉพาะเจาะจงใน Excel ตามเกณฑ์หลายอย่าง
ในส่วนนี้ เราจะดูว่า สูตร INDEX-MATCH ทำงานอย่างไร เมื่อใช้เงื่อนไขหลายอย่าง ตัวอย่าง เราต้องการทราบชื่อผู้เข้าร่วมที่ไม่ซ้ำ ที่เข้าร่วมว่ายน้ำ และอายุน้อยกว่า 25 ปี
สูตรที่จำเป็นใน เซลล์ F5 จะเป็น:
=IFERROR(INDEX($B$5:$B$13,MATCH(0,COUNTIF(F4:$F$4,$B$5:$B$13)+IF(D5:D13=$G$9,1,0)+IF(C5:C13<$G$10,1,0),0)),"")
หลังกด Enter และลากเติมเซลล์ใหม่ในคอลัมน์ผลลัพธ์ เราจะได้ชื่อเฉพาะเจาะจง ตามภาพด้านล่าง
ในสูตรนี้ เรากำหนดเกณฑ์ด้วยสองฟังก์ชัน IF และฟังก์ชัน COUNTIF จะพิจารณาเกณฑ์เหล่านั้นขณะดึงอาร์เรย์เซลล์ผลลัพธ์ ตามที่อธิบายในวิธีก่อนหน้า สูตร INDEX-MATCH จะคืนผลลัพธ์ตามอาร์เรย์นั้น และฟังก์ชัน IFERROR ที่นี่ใช้เพื่อคืนข้อความกำหนดเอง หากพบข้อผิดพลาด
9. สร้างรายการเฉพาะเจาะจงหลายรายการ ตามแถวและคอลัมน์ พร้อมเกณฑ์
ในวิธีสุดท้าย เราจะใช้ตาราง Excel เพื่อดึงรายการเฉพาะเจาะจงหลายรายการ ในภาพต่อไป ตารางด้านล่าง จะแสดงชื่อผู้เข้าร่วมที่ไม่ซ้ำ ตามประเภทกีฬา
เราตั้งชื่อตารางหรือช่วงเซลล์ (B5:C13) ว่า Sports คอลัมน์มีหัวข้อชื่อ SportsName และ Name คุณต้องจำว่า หัวข้อในตารางไม่สามารถมีช่องว่างได้
📌 ขั้นตอนที่ 1:
➤ สูตรที่จำเป็นใน เซลล์ B16 แรก จะเป็น:
=IFERROR(INDEX(Sports,SMALL(IF(Sports[SportsName]=B$15,ROW(Sports)-4),ROW(1:1)),2),"")
หลังกด Enter ค่าผลลัพธ์แรกจะคืนทันที
📌 ขั้นตอนที่ 2:
➤ ใช้ Fill Handle ลากลงคอลัมน์ จนกว่าจะเจอเซลล์ว่าง
📌 ขั้นตอนที่ 3:
➤ คัดลอกช่วงเซลล์จากผลลัพธ์ (B16:B20) ในคอลัมน์แรก
➤ ใน เซลล์ C16 วางค่าด้วยตัวเลือก Formulas (F)
คุณจะได้คอลัมน์ที่สอง พร้อมชื่อผู้เข้าร่วมที่ไม่ซ้ำ ที่เข้าร่วมปั่นจักรยานอย่างเดียว
📌 ขั้นตอนที่ 4:
➤ ทำนองเดียวกัน วางค่าที่คัดลอกเป็นสูตรอีกครั้ง ใน เซลล์ D16 และ E16
และคุณจะได้ชื่อผู้เข้าร่วมที่ไม่ซ้ำอื่นๆ ตามประเภทกีฬา ทันที แต่ที่นี่คุณต้องจำว่า คุณไม่สามารถใช้ Fill Handle ลากอัตโนมัติไปทางขวาจากค่าผลลัพธ์แรกใน คอลัมน์ B ได้ การใช้ตัวเลือก Autofill จะทำให้ข้อมูลบิดเบือน และคุณจะไม่สามารถดึงผลลัพธ์ต้นฉบับได้
🔎 สูตรทำงานอย่างไร?
- IF(Sports[SportsName]=B$15, ROW(Sports)-4): ส่วนนี้ของฟังก์ชัน ค้นหาเงื่อนไขที่ระบุโดยหัวข้อใน เซลล์ B15 และฟังก์ชันคืนค่าเป็น:
{1;FALSE;FALSE;4;FALSE;FALSE;7;8;FALSE}
- SMALL(IF(Sports[SportsName]=B$15, ROW(Sports)-4), ROW(1:1)): ฟังก์ชัน SMALL ดึงตัวเลขเล็กที่สุดจากผลลัพธ์ก่อนหน้า และสำหรับ เซลล์ C16 คือ ‘1’
- ฟังก์ชัน INDEX ดึงชื่อออกมา ตามหมายเลขแถวที่ระบุโดย SMALL
- ฟังก์ชัน IFERROR ใช้แสดงเซลล์ว่าง หากพบผลลัพธ์ข้อผิดพลาด
- ในสูตรรวมนี้ ตัวเลข ‘4’ ในส่วน- “ROW(Sports)-4” คือหมายเลขแถวของหัวข้อในตารางข้อมูลหลัก
คำสรุป
หวังว่าวิธีทั้งหมดที่แสดงด้านบน จะช่วยให้คุณนำไปใช้ในสเปรดชีต Excel ของคุณได้อย่างมีประสิทธิภาพมากขึ้น ขณะสร้างรายการค่าที่ไม่ซ้ำจากตารางข้อมูล หากมีคำถามหรือข้อเสนอแนะ โปรดแจ้งในส่วนความคิดเห็น หรือคุณสามารถดูบทความอื่นๆ ที่เกี่ยวกับฟังก์ชัน Excel บนเว็บไซต์นี้
คุณอาจสนใจสำรวจเพิ่มเติม
- วิธีสร้างรายการจุดใน Excel (9 วิธี)
- Make a List within a Cell in Excel (3 Quick Methods)
- วิธีสร้างราคารายการใน Excel (แนวทางทีละขั้นตอน)
