ใน Microsoft Excel มีหลายวิธีในการสร้างรายการที่ไม่ซ้ำภายใต้เกณฑ์หลายเกณฑ์ โดยทั่วไปแล้ว รายการที่ไม่ซ้ำจะถูกจัดเตรียมเพื่อลบรายการที่ซ้ำกันออกจากตาราง ในบทความนี้ คุณจะได้เรียนรู้วิธีใช้วิธีการต่างๆ เพื่อสร้างรายการที่ไม่ซ้ำตามเงื่อนไขต่างๆ
คุณสามารถดาวน์โหลดเวิร์กบุ๊ก Excel ที่เราใช้ในการเตรียมบทความนี้ได้
9 วิธีในการสร้างรายการที่ไม่ซ้ำใน Excel ตามเกณฑ์
1. สร้างรายการแถวที่ไม่ซ้ำที่มีเกณฑ์หลายคอลัมน์
ในภาพต่อไปนี้ ตารางทางด้านซ้ายกำลังโกหกโดยสุ่มชื่อกีฬา ผู้เข้าร่วมและอายุของพวกเขา ตารางอื่นทางด้านขวาคือตารางผลลัพธ์ที่เราจะดึงข้อมูลตามค่าที่ไม่ซ้ำกันเท่านั้น ตัวอย่างเช่น เราต้องการทราบชื่อของผู้เข้าร่วมที่ไม่ซ้ำกันพร้อมกับอายุเท่านั้น
เราจะใช้ UNIQUE ฟังก์ชันที่นี่ซึ่งคืนค่าที่ไม่ซ้ำจากช่วงหรืออาร์เรย์ สูตรทั่วไปของฟังก์ชันนี้คือ:
=UNIQUE(array, [by_col], [exactly_once])
แม้ว่า ไม่ซ้ำ ฟังก์ชันใช้ได้เฉพาะใน Excel 365 นอกจากนี้ เรายังจะแสดงวิธีการทางเลือกร่วมกับฟังก์ชันทั่วไปอื่นๆ ใน 3 ส่วนสุดท้ายก่อนที่จะข้ามไปที่คำสรุป
ดังนั้น ฟังก์ชันที่จำเป็นใน เซลล์ F5 จะเป็น:
=UNIQUE(B5:C13,FALSE,FALSE) หลังจากกด Enter ฟังก์ชันจะส่งคืนอาร์เรย์ที่มีชื่อเฉพาะพร้อมกับอายุที่ตรงกันของผู้เข้าร่วม
อ่านเพิ่มเติม:วิธีสร้างรายการตามเกณฑ์ใน Excel (4 วิธี)
2. รับรายการค่าที่ไม่ซ้ำเรียงตามลำดับตัวอักษร
นอกจากนี้เรายังสามารถรับค่าที่ไม่ซ้ำกันซึ่งจัดเรียงตามลำดับตัวอักษรโดยใช้ SORT การทำงาน. โดยแสดงลำดับการจัดเรียงด้วย (A ถึง Z) หรือจากมากไปน้อย (Z ถึง A) เกณฑ์ เราสามารถจัดเรียงชื่อผู้เข้าร่วมใหม่ตามชื่อย่อของพวกเขาได้
ในผลลัพธ์ เซลล์ E5 , สูตรที่ต้องการด้วย SORT และ ไม่ซ้ำใคร ฟังก์ชันจะเป็น:
=SORT(UNIQUE(B5:C13,FALSE,FALSE),,1) หลังจากกด Enter ฟังก์ชันจะส่งคืนอาร์เรย์ที่จัดเรียงตามลำดับตัวอักษรโดย A ถึง Z .
อ่านเพิ่มเติม: วิธีการสร้างรายการตามตัวอักษรใน Excel (3 วิธี)
3. สร้างรายการค่าที่ไม่ซ้ำที่เชื่อมต่อกันเป็นเซลล์เดียว
สมมติว่า เราต้องการดึงข้อมูลที่ไม่ซ้ำจากสองคอลัมน์ แล้วรวมค่าข้างๆ เข้าด้วยกันเพื่อคืนค่าที่ต่อกันในคอลัมน์เดียว ในภาพต่อไปนี้ ชื่อที่ไม่ซ้ำกันและอายุที่สอดคล้องกันจะแสดงร่วมกันในคอลัมน์โดยใช้ตัวคั่นเฉพาะ จุลภาค (,) และเราจะใช้ เครื่องหมาย (&) ที่นี่เพื่อเชื่อมค่าควบคู่ไปกับสองคอลัมน์ที่แตกต่างกัน
สูตรที่ต้องการในผลลัพธ์ เซลล์ F5 จะเป็น:
=UNIQUE(B5:B13&", "&C5:C13) สูตรจะส่งคืนอาร์เรย์ของแถวที่ไม่ซ้ำในคอลัมน์เดียว
อ่านเพิ่มเติม: วิธีสร้างรายการที่คั่นด้วยจุลภาคใน Excel (5 วิธี)
4. สร้างรายการค่าที่ไม่ซ้ำพร้อมเกณฑ์ (สูตร UNIQUE-FILTER)
ก. ระบุค่าที่ไม่ซ้ำตามหลายเกณฑ์และใน Excel
ในส่วนนี้ เราจะเพิ่มเกณฑ์สองสามข้อและดึงข้อมูลที่ไม่ซ้ำตามเงื่อนไขเหล่านั้น ตัวอย่างเช่น เราต้องการทราบชื่อเฉพาะของผู้เข้าร่วมที่เข้าร่วมว่ายน้ำเท่านั้นและมีอายุต่ำกว่า 25 ปี ดังนั้น เราต้องผสมผสาน UNIQUE และ ตัวกรอง ทำหน้าที่กรองข้อมูลตามเกณฑ์ที่กำหนด
สูตรที่จำเป็นในผลลัพธ์ เซลล์ F5 จะเป็น:
=UNIQUE(FILTER(B5:C13,(D5:D13=G9)*(C5:C13<G10)))
หลังจากกด Enter สูตรจะส่งคืนอาร์เรย์ที่มีชื่อไม่ซ้ำกันและอายุที่สอดคล้องกันตามเกณฑ์ที่เลือก
ii. ค้นหาค่าที่ไม่ซ้ำตามเกณฑ์หรือหลายเกณฑ์
สมมติว่าตอนนี้เราต้องการทราบชื่อของผู้เข้าร่วมที่ไม่ซ้ำกันซึ่งมีส่วนร่วมในกีฬากลางแจ้ง - ว่ายน้ำและขี่จักรยาน เนื่องจากเรากำหนดเกณฑ์จากคอลัมน์เดียว เราต้องใช้การบวกตัวเลขเพื่อรวมสองเงื่อนไขใน ตัวกรอง ฟังก์ชัน
สูตรที่ต้องการใน เซลล์ F5 จะเป็นตอนนี้:
=UNIQUE(FILTER(B5:C13,(D5:D13=F11)+(D5:D13=F12)))
กด เข้าสู่ และคุณจะได้ข้อมูลเฉพาะที่แยกออกมาทันทีดังที่แสดงในภาพหน้าจอต่อไปนี้
iii. รับรายการค่าที่ไม่ซ้ำโดยไม่สนใจเซลล์ว่าง
ชุดข้อมูลของเราอาจมีเซลล์หรือแถวว่างหลายเซลล์ ดังนั้น หากเราต้องการข้ามแถวว่างเหล่านั้นและดึงข้อมูลที่ไม่ซ้ำออกจากตาราง เราต้องใช้ตัวดำเนินการเปรียบเทียบ:ไม่เท่ากับ (<>) ในสูตร
สูตรที่ต้องการในผลลัพธ์ เซลล์ F5 จะเป็น:
=UNIQUE(FILTER(B5:C13,D5:D13<>""))
หลังจากกด Enter สูตรจะส่งคืนอาร์เรย์ต่อไปนี้โดยมีเฉพาะแถวที่ไม่ซ้ำกันในตารางผลลัพธ์
การอ่านที่คล้ายกัน
- วิธีการสร้างรายการสิ่งที่ต้องทำใน Excel (วิธีง่ายๆ 3 วิธี)
- การสร้างรายชื่อผู้รับจดหมายใน Excel (2 วิธี)
5. ค้นหารายการค่าที่ไม่ซ้ำในคอลัมน์ที่ระบุใน Excel
สมมติว่าเราต้องการดึงข้อมูลเฉพาะจากคอลัมน์เฉพาะจำนวนหนึ่งเท่านั้น เป็นไปไม่ได้ที่จะเลือกคอลัมน์ที่แตกต่างกันเหล่านั้นด้วยเคอร์เซอร์ของเมาส์และป้อนเป็นอาร์กิวเมนต์สำหรับ UNIQUE การทำงาน. ดังนั้น เราต้องรวม CHOOSE . เข้าด้วยกัน ทำงานที่นี่ด้วย UNIQUE ทำหน้าที่เป็น เลือก ฟังก์ชันจะให้คุณเลือกจำนวนคอลัมน์ที่ระบุหรือช่วงของเซลล์ตามหมายเลขดัชนีจากรายการค่า
จากตารางข้อมูล เราจะแยกชื่อผู้เข้าร่วมที่ไม่ซ้ำและแสดงผลลัพธ์จากชื่อ (คอลัมน์ B) และ ชื่อกีฬา (คอลัมน์ D) เฉพาะคอลัมน์เท่านั้น
สูตรที่ต้องการใน เซลล์ F5 จะเป็น:
=UNIQUE(CHOOSE({1,3},B5:B13,C5:C13,D5:D13))
หลังจากกด Enter สูตรจะส่งคืนชื่อที่ไม่ซ้ำกันและชื่อของกีฬาที่เกี่ยวข้องจากสองคอลัมน์ที่แตกต่างกันดังแสดงในภาพด้านล่าง
ภายใน เลือก ฟังก์ชันตัวเลขดัชนีคือ 1 และ 3 ซึ่งหมายความว่าต้องเลือกช่วงเซลล์ที่ 1 และ 3 จากรายการค่า ฟังก์ชัน UNIQUE จะพิจารณาเฉพาะคอลัมน์ที่ระบุเท่านั้น และส่งคืนข้อมูลที่ไม่ซ้ำจากคอลัมน์เหล่านั้นในอาร์เรย์
อ่านเพิ่มเติม:วิธีสร้างรายการลำดับเลขใน Excel (8 วิธี)
6. การใช้ฟังก์ชัน IFERROR ขณะสร้างรายการที่ไม่ซ้ำ
นอกจากนี้เรายังสามารถใช้ IFERROR ฟังก์ชันเบื้องหลัง UNIQUE ฟังก์ชั่นเพื่อแสดงข้อความที่กำหนดเองหากพบข้อผิดพลาดใด ๆ ในค่าตอบแทน สมมติว่า เราต้องการทราบชื่อของผู้เข้าร่วมที่ไม่ซ้ำกันซึ่งมีอายุต่ำกว่า 21 ปี เนื่องจากไม่มีผู้เข้าร่วมที่อายุต่ำกว่า 21 ปี สูตรควรส่งคืน #N/A ข้อผิดพลาด. แต่แทนที่จะแสดงข้อผิดพลาดนี้ เราควรใช้ IFERROR ฟังก์ชั่นเพื่อแสดงข้อความที่กำหนดเอง - “ไม่พบ”.
ดังนั้น สูตรที่ต้องการใน เซลล์ F5 จะเป็น:
=IFERROR(UNIQUE(FILTER(B5:C13,C5:C13<=G10)), "Not Found")
หลังจากกด Enter สูตรจะส่งคืนข้อความที่ระบุดังแสดงในภาพหน้าจอต่อไปนี้
7. แยกรายการที่ไม่ซ้ำตามเกณฑ์ (สูตร INDEX-MATCH)
ตอนนี้เราจะกำจัด UNIQUE และใช้การรวมกันของ INDEX และ MATCH ฟังก์ชั่น. ดัชนี ฟังก์ชันส่งกลับค่าหรือการอ้างอิงของเซลล์ที่จุดตัดของแถวและคอลัมน์เฉพาะ และ 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:
➤ ใช้ เติมแฮนเดิล เพื่อลากเซลล์ไปทางขวา
และคุณจะพบกับอายุของผู้เข้าร่วมด้วย
📌 ขั้นตอนที่ 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;””;”;””}
- The MATCH ฟังก์ชั่นส่งคืนหมายเลขแถวของเซลล์ที่พบในขั้นตอนก่อนหน้า
- สุดท้าย ดัชนี ฟังก์ชันดึงข้อมูลตามหมายเลขแถวเหล่านั้น
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) กับกีฬา. คอลัมน์มีส่วนหัวที่มีชื่อ- SportsName และ Name คุณต้องจำไว้ว่าส่วนหัวในตารางไม่สามารถใช้พื้นที่ใดๆ ได้
📌 ขั้นตอนที่ 1:
➤ สูตรที่ต้องการในผลลัพธ์แรก เซลล์ B16 จะเป็น:
=IFERROR(INDEX(Sports,SMALL(IF(Sports[SportsName]=B$15,ROW(Sports)-4),ROW(1:1)),2),"")
หลังจากกด Enter ค่าเอาต์พุตแรกจะส่งคืนพร้อมกัน
📌 ขั้นตอนที่ 2:
➤ ใช้ เติมที่จับ เพื่อเติมลงในคอลัมน์จนเซลล์ว่างปรากฏขึ้น
📌 ขั้นตอนที่ 3:
➤ ตอนนี้คัดลอกช่วงของเซลล์จากเอาต์พุต (B16:B20) ในคอลัมน์แรก
➤ ใน เซลล์ C16 วางค่าด้วย สูตร (F) ตัวเลือก
คุณจะได้รับคอลัมน์ที่ 2 พร้อมชื่อเฉพาะของผู้เข้าร่วมที่มีส่วนร่วมในการปั่นจักรยานเท่านั้น
📌 ขั้นตอนที่ 4:
➤ ในทำนองเดียวกัน ให้วางค่าที่คัดลอกมาเป็นสูตรอีกครั้งใน เซลล์ D16 และ E16 .
และคุณจะได้รับชื่อเฉพาะอื่นๆ ของผู้เข้าร่วมตามประเภทกีฬาทันที แต่ที่นี่คุณต้องจำไว้ว่าคุณไม่สามารถเพียงแค่ใช้ Fill Handle เพื่อเติมเซลล์โดยอัตโนมัติจากค่าที่ส่งกลับครั้งแรกใน คอลัมน์ B . การใช้ ป้อนอัตโนมัติ ตัวเลือกจะส่งผลให้มีการจัดการข้อมูล และคุณจะไม่สามารถดึงผลลัพธ์ดั้งเดิมได้
🔎 สูตรทำงานอย่างไร
- IF(Sports[SportsName]=B$15, แถว (กีฬา)-4): ฟังก์ชันส่วนนี้ค้นหาเงื่อนไขที่ระบุโดยส่วนหัวใน เซลล์ B15 และฟังก์ชันจะคืนค่าเป็น:
{1;FALSE;FALSE;4;FALSE;FALSE;7;8;FALSE}
- SMALL(IF(Sports[SportsName]=B$15, ROW(Sports)-4), ROW(1:1)): เล็ก ฟังก์ชันดึงตัวเลขที่น้อยที่สุดจากเอาต์พุตก่อนหน้าและสำหรับ เซลล์ C16 มันคือ '1' .
- ดัชนี ฟังก์ชั่นดึงชื่อตามหมายเลขแถวที่ระบุโดย เล็ก ฟังก์ชัน.
- The IFERROR มีการใช้ฟังก์ชันเพื่อแสดงเซลล์ว่างหากพบเอาต์พุตข้อผิดพลาด
- ในสูตรรวมนี้ ตัวเลข '4' ในส่วน- “ROW(Sports)-4” คือหมายเลขแถวของส่วนหัวในตารางข้อมูลหลัก
สรุปคำศัพท์
ฉันหวังว่าวิธีการทั้งหมดที่แสดงไว้ข้างต้นจะช่วยให้คุณนำไปใช้กับสเปรดชีต Excel ของคุณได้อย่างมีประสิทธิภาพมากขึ้นในขณะที่สร้างรายการของค่าที่ไม่ซ้ำจากตารางข้อมูล หากคุณมีคำถามหรือข้อเสนอแนะโปรดแจ้งให้เราทราบในส่วนความคิดเห็น หรือคุณสามารถอ่านบทความอื่นๆ ของเราเกี่ยวกับฟังก์ชัน Excel ได้ในเว็บไซต์นี้
คุณอาจต้องการสำรวจด้วย
- วิธีการสร้างรายการสัญลักษณ์แสดงหัวข้อย่อยใน Excel (9 วิธี)
- สร้างรายการภายในเซลล์ใน Excel (วิธีด่วน 3 วิธี)
- วิธีการสร้างรายการราคาใน Excel (คำแนะนำทีละขั้นตอน)
