สูตร Excel ดึงข้อมูลที่ เหมือนกัน

วิธีการหาข้อมูลที่ไม่ซ้ำกัน

สารบัญ Show

การใช้ INDEX และ MATCH แทน VLOOKUP
คุณควรลองทำดู
ตัวอย่าง VLOOKUP ในที่งาน
ตัวอย่าง HLOOKUP
ตัวอย่าง INDEX และ MATCH

ขั้นตอนที่ 1 ลากคลุมข้อมูลทั้งหมด

ขั้นตอนที่ 2 คลิกที่ conditional formatting เลือก duplicate value เหมือนกับที่เราจะหาข้อมูลซ้ำ

ขั้นตอนที่ 3 จากนั้นเปลี่ยนจาก duplicate เป็น unique value เราสามารถเปลี่ยนสีที่ต้องการ highlight ได้ ก็จะเจอ highlight ข้อมูลที่ไม่ซ้ำ ถ้าเราอยากจะดึงข้อมูลที่ไม่ซ้ำออกมา ให้ filter และเลือกเฉพาะสีที่เรา highlight ได้ เพียงเท่านี้เราก็จะสามารถเลือกข้อมูลที่ไม่ซ้ำออกมาได้แล้ว

ขอบคุณข้อมูลดีๆ จากหมีขี้เกียจ

เคล็ดลับ: ลองใช้ฟังก์ชัน XLOOKUP และ XMATCH ใหม่ ฟังก์ชันเวอร์ชันที่ปรับปรุงซึ่งอธิบายไว้ในบทความนี้ ฟังก์ชันใหม่เหล่านี้สามารถใช้งานในทิศทางใดก็ได้ และส่งกลับรายการที่ตรงกันทุกรายการตามค่าเริ่มต้น ซึ่งช่วยให้การใช้งานง่ายและสะดวกกว่าการใช้งานก่อน

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

ฟังก์ชัน VLOOKUPและ HLOOKUP ร่วมกับ INDEX และ MATCHเป็นฟังก์ชันที่มีประโยชน์ที่สุดใน Excel

หมายเหตุ: ฟีเจอร์ตัวช่วยสร้างการค้นหาจะไม่พร้อมใช้งานใน Excel อีกต่อไป

ต่อไปนี้เป็นตัวอย่างของวิธีใช้ VLOOKUP

=VLOOKUP(B2,C2:E7,3,TRUE)

ในตัวอย่างนี้ B2 เป็นอาร์กิวเมนต์ แรกซึ่งเป็นองค์ประกอบของข้อมูลที่ฟังก์ชันต้องการจะใช้งาน For VLOOKUP, this first argument is the value that you want to find. อาร์กิวเมนต์นี้อาจเป็นการอ้างอิงเซลล์ หรือค่าคงที่ เช่น "smith" หรือ 21,000 อาร์กิวเมนต์ที่สองคือช่วงของเซลล์ C2-:E7 ที่จะค้นหาค่าที่คุณต้องการค้นหา อาร์กิวเมนต์ที่สามคือคอลัมน์ในช่วงของเซลล์นั้นที่มีค่าที่คุณต้องการ

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

ตัวอย่างนี้จะแสดงวิธีการใช้ฟังก์ชัน เมื่อคุณใส่ค่าในเซลล์ B2 (อาร์กิวเมนต์แรก) VLOOKUP จะค้นหาเซลล์ในช่วง C2:E7 (อาร์กิวเมนต์ 2nd) และส่งกลับค่าที่ใกล้เคียงที่สุดจากคอลัมน์ที่สามในช่วง คอลัมน์ E (อาร์กิวเมนต์ที่ 3)

อาร์กิวเมนต์ที่สี่ว่างเปล่า ฟังก์ชันจะส่งกลับค่าที่ตรงกันโดยประมาณ ถ้าไม่ คุณจะต้องใส่ค่าใดค่าหนึ่งในคอลัมน์ C หรือ D เพื่อให้ได้ผลลัพธ์

เมื่อคุณสะดวกกับ VLOOKUP ฟังก์ชัน HLOOKUP จะใช้งานได้ง่ายเท่าๆ กัน คุณใส่อาร์กิวเมนต์เดียวกัน แต่จะค้นหาในแถวแทนที่จะเป็นคอลัมน์

การใช้ INDEX และ MATCH แทน VLOOKUP

มีข้อจํากัดบางอย่างในการใช้ VLOOKUP ฟังก์ชัน VLOOKUP สามารถค้นหาค่าจากซ้ายไปขวาเท่านั้น ซึ่งหมายความว่าคอลัมน์ที่ประกอบด้วยค่าที่คุณค้นหาควรอยู่ทางด้านซ้ายของคอลัมน์ที่ประกอบด้วยค่าที่ส่งกลับเสมอ ในตอนนี้ ถ้าสเปรดชีตของคุณไม่ได้ถูกสร้างขึ้นด้วยวิธีนี้ อย่าใช้ VLOOKUP ใช้การผสมของฟังก์ชัน INDEX และ MATCH แทน

ตัวอย่างนี้แสดงรายการขนาดเล็กที่ค่าที่เราต้องการค้นหา Chicago ไม่อยู่ในคอลัมน์ซ้ายสุด ดังนั้น เราจึงใช้ VLOOKUP ไม่ได้ แต่เราจะใช้ฟังก์ชัน MATCH เพื่อค้นหา Chicago ในช่วง B1:B11 แทน พบในแถวที่ 4 จากนั้น INDEX จะใช้ค่านั้นเป็นอาร์กิวเมนต์การค้นหา และค้นหาประชากรของ Chicago ในคอลัมน์ที่ 4 (คอลัมน์ D) สูตรที่ใช้จะแสดงในเซลล์ A14

For more examples of using INDEX and MATCH instead of VLOOKUP, see the article https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ by Bill Jelen, Microsoft MVP.

คุณควรลองทำดู

ถ้าคุณต้องการทดลองใช้ฟังก์ชันการค้นหาก่อนที่คุณจะลองใช้กับข้อมูลของคุณเอง ต่อไปนี้เป็นข้อมูลตัวอย่างบางส่วน

ตัวอย่าง VLOOKUP ในที่งาน

คัดลอกข้อมูลต่อไปนี้ลงในสเปรดชีตเปล่า

เคล็ดลับ: ก่อนที่คุณจะวางข้อมูลลงใน Excel ให้ตั้งค่าความกว้างคอลัมน์ของคอลัมน์ A ถึง C เป็น 250 พิกเซล แล้วคลิกตัดข้อความ(แท็บหน้าแรกกลุ่มการจัดแนว)

ความหนาแน่น	ความหนืด	อุณหภูมิ
0.457	3.55	500
0.525	3.25	400
0.606	2.93	300
0.675	2.75	250
0.746	2.57	200
0.835	2.38	150
0.946	2.17	100
1.09	1.95	50
1.29	1.71	0
สูตร	คำอธิบาย	ผลลัพธ์
=VLOOKUP(1,A2:C10,2)	ใช้การค้นหาค่าที่ตรงกันโดยประมาณ ค้นหาค่า 1 ในคอลัมน์ A พบค่าที่สูงสุดซึ่งน้อยกว่าหรือเท่ากับ 1 ในคอลัมน์ A คือ 0.946 แล้วส่งกลับค่าจากคอลัมน์ B ในแถวเดียวกัน	2.17
=VLOOKUP(1,A2:C10,3,TRUE)	ใช้การค้นหาค่าที่ตรงกันโดยประมาณ ค้นหาค่า 1 ในคอลัมน์ A พบค่าที่สูงสุดซึ่งน้อยกว่าหรือเท่ากับ 1 ในคอลัมน์ A คือ 0.946 แล้วส่งกลับค่าจากคอลัมน์ C ในแถวเดียวกัน	100
=VLOOKUP(0.7,A2:C10,3,FALSE)	ใช้การค้นหาค่าที่ตรงกันพอดี ค้นหาค่า 0.7 ในคอลัมน์ A เนื่องจากไม่มีค่าที่ตรงกันพอดีในคอลัมน์ A ข้อผิดพลาดจะถูกส่งกลับ	#N/A
=VLOOKUP(0.1,A2:C10,2,TRUE)	ใช้การค้นหาค่าที่ตรงกันโดยประมาณ ค้นหาค่า 0.1 ในคอลัมน์ A เนื่องจาก 0.1 น้อยกว่าค่าที่น้อยที่สุดในคอลัมน์ A ข้อผิดพลาดจะถูกส่งกลับ	#N/A
=VLOOKUP(2,A2:C10,2,TRUE)	ใช้การค้นหาค่าที่ตรงกันโดยประมาณ ค้นหาค่า 2 ในคอลัมน์ A พบค่าที่สูงที่สุดที่น้อยกว่าหรือเท่ากับ 2 ในคอลัมน์ A คือ 1.29 แล้วส่งกลับค่าจากคอลัมน์ B ในแถวเดียวกัน	1.71

ตัวอย่าง HLOOKUP

คัดลอกเซลล์ทั้งหมดในตารางนี้ แล้ววางข้อมูลลงในเซลล์ A1 บนเวิร์กชีตเปล่าใน Excel

เพลา	ตลับลูกปืน	สลักเกลียว
4	4	9
5	7	10
6	8	11
สูตร	คำอธิบาย	ผลลัพธ์
=HLOOKUP("เพลา", A1:C4, 2, TRUE)	ค้นหา "เพลา" ในแถวที่ 1 และส่งกลับค่าจากแถวที่ 2 ซึ่งอยู่ในคอลัมน์เดียวกัน (คอลัมน์ A)	4
=HLOOKUP("ตลับลูกปืน", A1:C4, 3, FALSE)	ค้นหา "ตลับลูกปืน" ในแถวที่ 1 และส่งกลับค่าจากแถวที่ 3 ซึ่งอยู่ในคอลัมน์เดียวกัน (คอลัมน์ B)	7
=HLOOKUP("B", A1:C4, 3, TRUE)	ค้นหา "B" ในแถวที่ 1 และส่งกลับค่าจากแถวที่ 3 ซึ่งอยู่ในคอลัมน์เดียวกัน เนื่องจากไม่พบค่าที่ตรงกันกับ "B" ดังนั้นจะใช้ค่ามากที่สุดในแถวที่ 1 ที่น้อยกว่า "B" ซึ่งก็คือ "เพลา" ใน คอลัมน์ A	5
=HLOOKUP("สลักเกลียว", A1:C4, 4)	ค้นหา "สลักเกลียว" ในแถวที่ 1 และส่งกลับค่าจากแถวที่ 4 ซึ่งอยู่ในคอลัมน์เดียวกัน (คอลัมน์ C)	11
=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)	ค้นหาเลข 3 ในค่าคงที่อาร์เรย์ที่มีสามแถว และส่งกลับค่าจากแถวที่ 2 ในคอลัมน์เดียวกัน (ในกรณีนี้ คือคอลัมน์ที่สาม) มีแถวของค่าอยู่สามแถวในค่าคงที่อาร์เรย์ โดยแต่ละแถวคั่นด้วยเครื่องหมายอัฒภาค (;) เนื่องจากพบ "c" ในแถวที่ 2 และอยู่ในคอลัมน์เดียวกันกับ 3 ดังนั้นจะส่งกลับ "c"	c

ตัวอย่าง INDEX และ MATCH

ตัวอย่างสุดท้ายนี้ใช้ฟังก์ชัน INDEX และ MATCH ร่วมกันเพื่อส่งกลับหมายเลขใบแจ้งหนี้แรกสุดและวันที่ที่สอดคล้องกันของแต่ละห้าเมือง เนื่องจากวันที่จะถูกส่งกลับเป็นตัวเลข เราจึงใช้ฟังก์ชัน TEXT เพื่อจัดรูปแบบเป็นวันที่ ฟังก์ชัน INDEX จะใช้ผลลัพธ์ของฟังก์ชัน MATCH เป็นอาร์กิวเมนต์ การรวมกันของฟังก์ชัน INDEX และ MATCH จะใช้สองครั้งในแต่ละสูตร อันดับแรก เพื่อส่งกลับหมายเลขใบแจ้งหนี้ แล้วส่งกลับวันที่

เคล็ดลับ: ก่อนที่คุณจะวางข้อมูลลงใน Excel ให้ตั้งค่าความกว้างคอลัมน์ของคอลัมน์ A ถึง D เป็น 250 พิกเซล แล้วคลิกตัดข้อความ(แท็บหน้าแรกกลุ่มการจัดแนว)

ใบแจ้งหนี้	เมือง	วันที่ใบแจ้งหนี้	ใบแจ้งหนี้ที่เก่าที่สุดตามชื่อเมือง พร้อมกับวันที่
3115	แอตแลนตา	4/7/12	="แอตแลนตา = "&INDEX($A$2:$C$33,MATCH("แอตแลนตา",$B$2:$B$33,0),1)& ", วันที่ใบแจ้งหนี้: " & TEXT(INDEX($A$2:$C$33,MATCH("แอตแลนตา",$B$2:$B$33,0),3),"m/d/yy")
3137	แอตแลนตา	4/9/12	="ออสติน = "&INDEX($A$2:$C$33,MATCH("ออสติน",$B$2:$B$33,0),1)& ", วันที่ใบแจ้งหนี้: " & TEXT(INDEX($A$2:$C$33,MATCH("ออสติน",$B$2:$B$33,0),3),"m/d/yy")
3154	แอตแลนตา	4/11/12	="ดัลลัส = "&INDEX($A$2:$C$33,MATCH("ดัลลัส",$B$2:$B$33,0),1)& ", วันที่ใบแจ้งหนี้: " & TEXT(INDEX($A$2:$C$33,MATCH("ดัลลัส",$B$2:$B$33,0),3),"m/d/yy")
3191	แอตแลนตา	4/21/12	="นิวออร์ลีนส์ = "&INDEX($A$2:$C$33,MATCH("นิวออร์ลีนส์",$B$2:$B$33,0),1)& ", วันที่ใบแจ้งหนี้: " & TEXT(INDEX($A$2:$C$33,MATCH("นิวออร์ลีนส์",$B$2:$B$33,0),3),"m/d/yy")
3293	แอตแลนตา	4/25/12	="แทมปา = "&INDEX($A$2:$C$33,MATCH("แทมปา",$B$2:$B$33,0),1)& ", วันที่ใบแจ้งหนี้: " & TEXT(INDEX($A$2:$C$33,MATCH("แทมปา",$B$2:$B$33,0),3),"m/d/yy")
3331	แอตแลนตา	4/27/12
3350	แอตแลนตา	4/28/12
3390	แอตแลนตา	5/1/12
3441	แอตแลนตา	5/2/12
3517	แอตแลนตา	5/8/12
3124	ออสติน	4/9/12
3155	ออสติน	4/11/12
3177	ออสติน	4/19/12
3357	ออสติน	4/28/12
3492	ออสติน	5/6/12
3316	ดัลลัส	4/25/12
3346	ดัลลัส	4/28/12
3372	ดัลลัส	5/1/12
3414	ดัลลัส	5/1/12
3451	ดัลลัส	5/2/12
3467	ดัลลัส	5/2/12
3474	ดัลลัส	5/4/12
3490	ดัลลัส	5/5/12
3503	ดัลลัส	5/8/12
3151	นิวออร์ลีนส์	4/9/12
3438	นิวออร์ลีนส์	5/2/12
3471	นิวออร์ลีนส์	5/4/12
3160	แทมปา	4/18/12
3328	แทมปา	4/26/12
3368	แทมปา	4/29/12
3420	แทมปา	5/1/12
3501	แทมปา	5/6/12