บทความนี้จะอธิบายไวยากรณ์ของสูตรและการใช้ฟังก์ชัน ค้นหาและ ค้นหาในไมโครซอฟต์เอ็กเซล

คำอธิบาย

ฟังก์ชั่น ค้นหาและ ค้นหาค้นหาสตริงข้อความหนึ่งภายในอีกสตริงหนึ่งและส่งกลับตำแหน่งเริ่มต้นของสตริงข้อความแรก (นับจากอักขระตัวแรกของสตริงข้อความที่สอง) ตัวอย่างเช่น หากต้องการค้นหาตำแหน่งของตัวอักษร "n" ในคำว่า "printer" คุณสามารถใช้ฟังก์ชันต่อไปนี้:

ค้นหา("n", "เครื่องพิมพ์")

ฟังก์ชันนี้ส่งคืน 4 เนื่องจาก "n" เป็นอักขระตัวที่สี่ในคำว่า "printer"

คุณยังสามารถค้นหาคำในคำอื่นได้ ตัวอย่างเช่นฟังก์ชัน

ค้นหา("ฐาน", "ฐานข้อมูล")

ผลตอบแทน 5 เนื่องจากคำว่า "base" ขึ้นต้นด้วยอักขระตัวที่ห้าของคำว่า "database" สามารถใช้ฟังก์ชั่นได้ ค้นหาและ ค้นหาเพื่อกำหนดตำแหน่งของอักขระหรือสตริงข้อความในสตริงข้อความอื่น จากนั้นส่งคืนข้อความโดยใช้ฟังก์ชัน พีเอสทีอาร์และ PSTRBหรือแทนที่ด้วยฟังก์ชัน แทนที่และ แทนที่. ฟังก์ชันเหล่านี้แสดงอยู่ในบทความนี้

สำคัญ:

คุณสมบัติเหล่านี้อาจไม่สามารถใช้ได้ในทุกภาษา

ฟังก์ชัน SEARCH นับสองไบต์ต่ออักขระเฉพาะในกรณีที่ภาษาเริ่มต้นเป็นภาษาที่เปิดใช้งาน BDC มิฉะนั้น ฟังก์ชัน SEARCH จะทำงานเหมือนกับฟังก์ชัน SEARCH และนับหนึ่งไบต์ต่ออักขระ

ภาษาที่รองรับ BDCS ได้แก่ ญี่ปุ่น จีนตัวย่อ จีนตัวเต็ม และเกาหลี

ไวยากรณ์

ค้นหา(search_text, viewed_text, [start_position])

SEARCHB(search_text, ดู_ข้อความ, [start_position])

อาร์กิวเมนต์ของฟังก์ชัน SEARCH และ SEARCHB มีอธิบายไว้ด้านล่างนี้

ค้นหา_ข้อความที่จำเป็น. ข้อความที่คุณต้องการค้นหา

ดู_ข้อความแล้วที่จำเป็น. ข้อความที่ใช้ค้นหาค่าของอาร์กิวเมนต์ ค้นหา_ข้อความ .

เริ่มต้น_ตำแหน่งไม่จำเป็น. หมายเลขอักขระในการโต้แย้ง ดู_ข้อความแล้ว ที่ที่คุณควรเริ่มค้นหา

ความคิดเห็น

ฟังก์ชั่น ค้นหาและ ค้นหาไม่คำนึงถึงขนาดตัวพิมพ์ หากคุณต้องการคำนึงถึงขนาดตัวพิมพ์ ให้ใช้ฟังก์ชันต่างๆ หาและ ไนท์ติบ.

ในการโต้แย้ง ค้นหา_ข้อความ คุณสามารถใช้ไวด์การ์ด: เครื่องหมายคำถาม ( ? ) และเครื่องหมายดอกจัน ( * ). เครื่องหมายคำถามตรงกับอักขระใดๆ เครื่องหมายดอกจันจะจับคู่ลำดับอักขระใดๆ หากคุณต้องการค้นหาเครื่องหมายคำถามหรือเครื่องหมายดอกจัน ให้พิมพ์เครื่องหมายตัวหนอน ( ~ ).

ถ้าค่าอาร์กิวเมนต์ ค้นหา_ข้อความ ไม่พบ #VALUE! ค่าความผิดพลาดที่ส่งคืน

ถ้าจะโต้แย้ง. start_position ละไว้ จากนั้นจะถูกตั้งค่าเป็น 1

ถ้า เริ่มต้น_ตำแหน่ง ไม่เกิน 0 หรือมากกว่าความยาวของอาร์กิวเมนต์ ดู_ข้อความแล้ว , #ค่า! ค่าความผิดพลาดที่ส่งคืน

การโต้แย้ง start_position สามารถใช้เพื่อข้ามอักขระจำนวนหนึ่งได้ ให้เราสมมุติว่าฟังก์ชัน ค้นหาควรใช้ในการทำงานกับสตริงข้อความ "MDS0093.Men's Clothing" หากต้องการค้นหาการเกิดขึ้นครั้งแรกของ "M" ในส่วนที่เป็นคำอธิบายของสตริงข้อความ ให้ตั้งค่าอาร์กิวเมนต์เป็น start_position ค่า 8 เพื่อไม่ให้การค้นหาในส่วนนั้นของข้อความที่เป็นอยู่ หมายเลขซีเรียล(ในกรณีนี้ - "MDS0093") การทำงาน ค้นหาเริ่มการค้นหาจากอักขระตัวที่แปด ค้นหาอักขระที่ระบุในอาร์กิวเมนต์ ค้นหา_ข้อความ ในตำแหน่งถัดไป และส่งกลับตัวเลข 9 ฟังก์ชัน ค้นหาส่งคืนหมายเลขอักขระโดยนับจากจุดเริ่มต้นเสมอ ของข้อความที่กำลังดูอยู่ รวมถึงอักขระที่ถูกข้ามหากค่าอาร์กิวเมนต์เป็น start_position มากกว่า 1

ตัวอย่าง

คัดลอกข้อมูลตัวอย่างจากตารางต่อไปนี้และวางลงในเซลล์ A1 ของแผ่นงาน Excel ใหม่ หากต้องการแสดงผลลัพธ์ของสูตร ให้เลือกสูตรแล้วกด F2 จากนั้นกด Enter หากจำเป็น ให้เปลี่ยนความกว้างของคอลัมน์เพื่อดูข้อมูลทั้งหมด

ข้อมูล
รายได้: ส่วนต่าง
“บอส” นี่..
สูตร	คำอธิบาย	ผลลัพธ์
ค้นหา("และ";A2;6)	ตำแหน่งของ "และ" ตัวแรกในแถวของเซลล์ A2 โดยเริ่มต้นที่อักขระที่หก
ค้นหา(A4;A3)	ตำแหน่งเริ่มต้นของแถว "ระยะขอบ" (แถวที่ค้นหาอยู่ในเซลล์ A4) ในแถว "รายได้: ระยะขอบ" (เซลล์ที่ค้นหาคือ A3)
แทนที่(A3,ค้นหา(A4,A3),6,"ระดับเสียง")	แทนที่คำว่า "margin" ด้วยคำว่า "volume" โดยค้นหาคำว่า "margin" ในเซลล์ A3 และแทนที่อักขระนั้นและอักขระห้าตัวต่อไปนี้ด้วยสตริงข้อความ "volume"	รายได้: ปริมาณ
PSTR(A3;ค้นหา(" ";A3)+1,4)	ส่งกลับอักขระสี่ตัวแรกที่อยู่ต่อจากช่องว่างแรกในแถว Revenue: Margin (เซลล์ A3)
ค้นหา("""";A5)	ตำแหน่งก่อน เครื่องหมายคำพูดคู่(") ในเซลล์ A5
PSTR(A5;ค้นหา("""";A5)+1;ค้นหา("""";A5;ค้นหา("""";A5)+1)-ค้นหา("""";A5)-1)	ส่งกลับเฉพาะข้อความที่อยู่ในเครื่องหมายคำพูดคู่จากเซลล์ A5

การทำงานกับโครงการ VB (12)
การจัดรูปแบบตามเงื่อนไข (5)
รายการและช่วง (5)
มาโคร (ขั้นตอน VBA) (63)
เบ็ดเตล็ด (39)
ข้อบกพร่องและข้อบกพร่องของ Excel (3)

ค้นหาคำใดๆ จากรายการในเซลล์

สมมติว่าคุณได้รับตารางที่เสร็จสมบูรณ์พร้อมรายการผลิตภัณฑ์จากซัพพลายเออร์/ลูกค้า/ลูกค้า:

และต้องเปรียบเทียบตารางนี้กับหมายเลขบทความ/รหัสผลิตภัณฑ์ในแค็ตตาล็อกผลิตภัณฑ์ที่มีอยู่:

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


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

(49.5 KiB, ดาวน์โหลด 13,249 ครั้ง)

ในชีต "คำสั่งซื้อ" ในไฟล์นี้มีตารางที่ได้รับจากลูกค้า และในชีต "แคตตาล็อก" มีบทความของเรา
ตัวสูตรเองโดยใช้ไฟล์ตัวอย่างจะมีลักษณะดังนี้:

BROWSE(2,1/SEARCH(ไดเรกทอรี!$A$2:$A$11, A2);ไดเรกทอรี!$A$2:$A$11)
=LOOKUP(2,1/SEARCH(ไดเรกทอรี!$A$2:$A$11,A2),ไดเรกทอรี!$A$2:$A$11)
สูตรนี้จะส่งคืนชื่อของบทความหากข้อความมีอย่างน้อยหนึ่งบทความจากแค็ตตาล็อกและ #ไม่มี (#ไม่มี)หากไม่พบบทความในแค็ตตาล็อก
ก่อนที่เราจะปรับแต่งสูตรนี้ด้วยการเพิ่มเติมทุกประเภท (เช่น การลบ #N/A ที่ไม่จำเป็นออก) เรามาดูกันว่ามันทำงานอย่างไร
ฟังก์ชัน LOOKUP ค้นหาค่าที่ระบุ (2) ในช่วงที่ระบุ (อาร์เรย์ - อาร์กิวเมนต์ที่สอง) โดยทั่วไปช่วงจะเป็นอาร์เรย์ของเซลล์ แต่ฟังก์ชัน VIEW มีคุณลักษณะแรกที่เราต้องการ โดยจะพยายามแปลงนิพจน์ใดๆ ที่เขียนเป็นอาร์กิวเมนต์ที่สองเป็นอาร์เรย์โดยตรง กล่าวอีกนัยหนึ่ง จะประเมินนิพจน์ในอาร์กิวเมนต์นี้ ซึ่งเราใช้โดยการแทนที่นิพจน์เป็นอาร์กิวเมนต์ที่สอง: 1/SEARCH(Directory!$A$2:$A$11;A2) ส่วน SEARCH (Catalog!$A$2:$A$11;A2) จะค้นหาแต่ละค่าจากรายการแค็ตตาล็อกในเซลล์ A2 (ชื่อจากตารางลูกค้า) ตามลำดับ หากพบค่า ระบบจะส่งกลับหมายเลขตำแหน่งของอักขระตัวแรกของค่าที่พบ ถ้าไม่พบค่า ค่าความผิดพลาด #VALUE! (#VALUE!) จะถูกส่งกลับ มาถึงฟีเจอร์ที่สอง: ฟังก์ชันต้องการให้ข้อมูลถูกจัดเรียงในอาร์เรย์จากน้อยไปหามาก หากข้อมูลอยู่ในตำแหน่งที่แตกต่างกันฟังก์ชันจะสแกนอาร์เรย์จนกว่าจะพบค่าที่มากกว่าค่าที่ค้นหา แต่ให้ใกล้เคียงที่สุด (แม้ว่าข้อมูลจะอนุญาต แต่สำหรับการค้นหาที่แม่นยำยิ่งขึ้นก็ยังดีกว่า เพื่อเรียงลำดับรายการจากน้อยไปหามาก) ดังนั้น อันดับแรกเราหาร 1 ด้วยนิพจน์ SEARCH(Directory!$A$2:$A$11,A2) เพื่อให้ได้อาร์เรย์ของแบบฟอร์ม: (0.0181818181818182:#VALUE!:#VALUE!:#VALUE!:#VALUE!: #VALUE !:#VALUE!:#VALUE!:#VALUE!:#VALUE !}
ตามค่าที่ต้องการเราให้ฟังก์ชันเป็นตัวเลข 2 - จำนวนที่มากกว่าที่พบในอาร์เรย์อย่างเห็นได้ชัด (เนื่องจากจำนวนหนึ่งหารด้วยจำนวนใดๆ จะน้อยกว่าสอง) และด้วยเหตุนี้เราจึงได้ตำแหน่งในอาร์เรย์ที่เกิดการจับคู่ครั้งล่าสุดจากไดเร็กทอรี หลังจากนั้นฟังก์ชัน VIEW จะจดจำตำแหน่งนี้และส่งกลับค่าจาก array Directory!$A$2:$A$11 (อาร์กิวเมนต์ที่สาม) ที่เขียนในอาร์เรย์นี้สำหรับตำแหน่งนี้
คุณสามารถดูขั้นตอนการคำนวณฟังก์ชันสำหรับแต่ละเซลล์ได้ด้วยตัวเอง ฉันจะนำเสนอขั้นตอนต่างๆ ในรูปแบบที่ขยายออกไปเล็กน้อยเพื่อความเข้าใจ:

1. =BROWSE(2,1/SEARCH(ไดเรกทอรี!$A$2:$A$11, A2);ไดเรกทอรี!$A$2:$A$11)
2. =ดู(2;
   1/(55:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE;!}
   แคตตาล็อก!$A$2:$A$11)
3. =VIEW(2;(0.0181818181818182:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE;Каталог!$A$2:$A$11)!}
4. =ดู(2;
   1;
   ("FM2-3320": "CV455689" " Q5949X " " CE321A " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ") "
5. ="FM2-3320"

ตอนนี้เรามาปรับแต่งฟังก์ชันกันเล็กน้อยและทำการใช้งานเพิ่มเติมอีกสองสามอย่าง
การนำไปปฏิบัติ 1:
แทนที่จะแสดงหมายเลขบทความและ #N/A เราจะแสดง "ใช่" สำหรับตำแหน่งที่พบ และ "ไม่พบในแค็ตตาล็อก" สำหรับตำแหน่งที่หายไป:
=IF(END(VIEW(2,1/SEARCH(Catalog!$A$2:$A$11,A2)));"ไม่พบในแค็ตตาล็อก","ใช่")
=IF(ISNA(LOOKUP(2,1/SEARCH(Directory!$A$2:$A$11,A2))),,"ไม่พบในไดเรกทอรี","ใช่")
การทำงานของฟังก์ชันนั้นง่าย - เราได้แยกการค้นหาออกแล้ว ดังนั้นสิ่งที่เหลืออยู่คือ UND และ IF
UNM (ISNA) กลับมาแล้ว จริงหากนิพจน์ภายในจะส่งกลับค่าความผิดพลาด #ไม่มี (#ไม่มี)และ เท็จหากนิพจน์ภายในไม่ส่งคืนค่าความผิดพลาดนี้
IF (IF) ส่งคืนสิ่งที่ระบุโดยอาร์กิวเมนต์ที่สอง หากนิพจน์ในอาร์กิวเมนต์แรกเท่ากับ จริงและสิ่งที่ระบุโดยอาร์กิวเมนต์ที่สามถ้าเป็นการแสดงออกของอาร์กิวเมนต์แรก เท็จ.

การนำไปปฏิบัติ 2:
แทนที่จะแสดง #N/A เราจะแสดง "ไม่พบในแค็ตตาล็อก" แต่หากพบบทความ เราจะแสดงชื่อของบทความเหล่านี้:
=IFERROR(VIEW(2,1/SEARCH(Catalog!$A$2:$A$11,A2),Catalog!$A$2:$A$11);"ไม่อยู่ในแค็ตตาล็อก")
=IFERROR(LOOKUP(2,1/SEARCH(Catalog!$A$2:$A$11,A2),Catalog!$A$2:$A$11),"ไม่อยู่ในแค็ตตาล็อก")
ฉันได้พูดคุยโดยละเอียดเกี่ยวกับฟังก์ชัน IFERROR ในบทความนี้: วิธีแสดง 0 แทนข้อผิดพลาดในเซลล์ด้วยสูตร
กล่าวโดยสรุป หากนิพจน์ที่ระบุโดยอาร์กิวเมนต์แรกของฟังก์ชันส่งคืนค่าของข้อผิดพลาดใด ๆ ฟังก์ชันก็จะส่งคืนสิ่งที่เขียนโดยอาร์กิวเมนต์ที่สอง (ในกรณีของเราคือข้อความ "ไม่พบในไดเร็กทอรี") หากนิพจน์ไม่ส่งคืนข้อผิดพลาด ฟังก์ชัน IFERROR จะเขียนค่าที่ได้รับจากนิพจน์ในอาร์กิวเมนต์แรก (ในกรณีของเรา นี่จะเป็นชื่อของบทความ)

การนำไปปฏิบัติ 3
จำเป็นไม่เพียงแต่เพื่อกำหนดว่าบทความใดสอดคล้องกับบทความเท่านั้น แต่ยังต้องแสดงราคาสำหรับรายการสำหรับบทความนี้ด้วย (ราคาควรอยู่ในคอลัมน์ B ของแผ่นแคตตาล็อก):
=IFERROR(VIEW(2,1/SEARCH(Directory!$A$2:$A$11,A2),Directory!$B$2:$B$11);"")
=IFERROR(LOOKUP(2,1/SEARCH(Directory!$A$2:$A$11,A2),Directory!$B$2:$B$11),"")

หมายเหตุสำคัญบางประการ:

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

ดังนั้นจึงขอแนะนำ เรียงลำดับรายการก่อนใช้สูตรจากน้อยไปมาก (จากน้อยไปมาก จาก A ถึง Z)

ในตัวอย่างที่แนบมาตอนต้นบทความ คุณจะพบตัวเลือกทั้งหมดที่วิเคราะห์

หากคุณต้องการแสดงชื่อทั้งหมด คุณสามารถใช้ฟังก์ชัน CONTAIN_ONE_OF จากโปรแกรมเสริม MulTEx ของฉันได้

บทความนี้ช่วยได้หรือไม่? แชร์ลิงก์กับเพื่อนของคุณ! บทเรียนวิดีโอ

("แถบด้านล่าง":("textstyle": "static", "textpositionstatic": "bottom", "textautohide":true, "textpositionmarginstatic":0,"textpositiondynamic:"bottomleft", "textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect：slide","texteffecteasingบนหน้าจอ:easeOutCubic","texteffectduration":600,"texteffectslidedirection：left,”texteffectslidedistance” :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":slide", "text effectslidedirection1" "right", "text effectslidedistance1":120, "text effecteasing1":easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2"slide"", "text effectslidedirection2" "right", "text effectslidedistance2":120, "texteffecteasing2":easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss″display:block; padding:12px; text-align:left;″textbgcss″display:block; ตำแหน่ง:สัมบูรณ์; ด้านบน:0px; ซ้าย:0px; ความกว้าง:100%; ความสูง:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);", "titlecss":display:block; ตำแหน่ง:ญาติ; แบบอักษร:ตัวหนา 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; สี:#fff;","descriptioncss://display:block; ตำแหน่ง:ญาติ; แบบอักษร:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; สี:#fff; ขอบบน:8px;"buttoncss":display:block; ตำแหน่ง:ญาติ; Margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;", "descriptioncssresponsive": "display: none !important;", "buttoncssresponsive": "", "addgooglefonts":false,"googlefonts:", "textleftrightpercentforstatic":40))

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

รันคำสั่ง การแก้ไขหน้าแรก ค้นหาและไฮไลต์ ค้นหา(หรือคลิก Ctrl+F) เพื่อเปิดกล่องโต้ตอบ ค้นหาและแทนที่. หากคุณต้องการแทนที่ข้อมูล ให้เลือกคำสั่ง การแก้ไขหน้าแรก ค้นหาและไฮไลต์ แทนที่(หรือคลิก Ctrl+H). คำสั่งที่แน่นอนที่คุณดำเนินการจะกำหนดว่าแท็บใดในสองแท็บที่กล่องโต้ตอบจะเปิดขึ้น

หากอยู่ในกล่องโต้ตอบที่เปิดขึ้น หาและกดปุ่มเพื่อเปลี่ยน ตัวเลือกจากนั้นตัวเลือกการค้นหาข้อมูลเพิ่มเติมจะปรากฏขึ้น (รูปที่ 21.1)

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

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

เมื่อค้นหาโดยใช้กล่องโต้ตอบ ค้นหาและแทนที่สามารถใช้ไวด์การ์ดได้สองตัว:

• ? - จับคู่อักขระใด ๆ
• * - จับคู่อักขระจำนวนเท่าใดก็ได้

นอกจากนี้ อักขระตัวแทนเหล่านี้ยังสามารถใช้เพื่อค้นหาค่าตัวเลขอีกด้วย เช่น หากคุณระบุในแถบค้นหา 3* ผลลัพธ์จะแสดงเซลล์ทั้งหมดที่มีค่าขึ้นต้นด้วย 3 และถ้าคุณป้อน 1?9 คุณจะได้รับรายการสามหลักทั้งหมดที่ขึ้นต้นด้วย 1 และลงท้ายด้วย 9

หากต้องการค้นหาเครื่องหมายคำถามหรือเครื่องหมายดอกจัน ให้นำหน้าด้วยเครื่องหมายตัวหนอน (~)
ตัวอย่างเช่น สตริงการค้นหาต่อไปนี้ค้นหาข้อความ *NONE*: -*N0NE~*
หากต้องการค้นหาอักขระตัวหนอน ให้ใส่ตัวหนอนสองตัวในแถบค้นหา

หากคุณคิดว่าการค้นหาของคุณทำงานไม่ถูกต้อง ให้ตรวจสอบการตั้งค่าสามรายการต่อไปนี้ (ซึ่งบางครั้งอาจเปลี่ยนแปลงได้เอง)

• ช่องทำเครื่องหมาย กรณีการแข่งขัน- ตั้งค่าให้ตัวพิมพ์ของข้อความที่ค้นหาตรงกับตัวพิมพ์ ข้อความที่กำหนด. ตัวอย่างเช่น หากคุณป้อนคำว่า Ivan ในการค้นหาและทำเครื่องหมายในช่องที่ระบุ คำว่า Ivan จะไม่ปรากฏในผลการค้นหา
• ช่องทำเครื่องหมาย ทั้งเซลล์- ตั้งค่าให้ค้นหาเซลล์ที่มีข้อความตรงกับที่ระบุไว้ในแถบค้นหา เช่น หากคุณพิมพ์ Excel ในแถบค้นหาและทำเครื่องหมายในช่อง คุณจะไม่พบเซลล์ที่มีวลี Microsoft Excel
• รายการแบบเลื่อนลง พื้นที่ค้นหา- รายการประกอบด้วยสามรายการ: ค่า สูตร และบันทึกย่อ ตัวอย่างเช่น หากคุณป้อนตัวเลข 900 ในแถบค้นหาและในรายการแบบเลื่อนลง พื้นที่ค้นหาถ้าคุณเลือกรายการค่า คุณจะไม่เห็นเซลล์ที่มีค่า 900 ในผลลัพธ์การค้นหา หากได้รับมาโดยใช้สูตร

โปรดจำไว้ว่าการค้นหาจะดำเนินการภายในช่วงของเซลล์ที่เลือก หากคุณต้องการค้นหาทั้งแผ่นงาน ให้เลือกเซลล์เดียวก่อนเริ่มค้นหา

นอกจากนี้โปรดทราบด้วยว่าการใช้หน้าต่าง ค้นหาและแทนที่ไม่พบการจัดรูปแบบ ค่าตัวเลข. เช่น หากคุณเข้าไปในแถบค้นหา $5* ซึ่งจะไม่พบค่าที่มีการใช้การจัดรูปแบบสกุลเงินและดูเหมือนว่าจะไม่พบ $54.00

การทำงานกับวันที่อาจเป็นเรื่องท้าทายเนื่องจาก Excel รองรับรูปแบบวันที่มากมาย หากคุณกำลังมองหาวันที่ที่ใช้การจัดรูปแบบเริ่มต้น Excel จะค้นหาวันที่แม้ว่าจะจัดรูปแบบแล้วก็ตาม วิธีทางที่แตกต่าง. ตัวอย่างเช่น หากระบบของคุณใช้รูปแบบวันที่ m/d/y สตริงการค้นหา 10/*/2010 จะค้นหาวันที่ทั้งหมดในเดือนตุลาคม 2010 ไม่ว่าจะจัดรูปแบบอย่างไร

ใช้ช่องว่าง แทนที่ด้วยเพื่อลบข้อมูลบางอย่างออกจากเวิร์กชีตอย่างรวดเร็ว เช่น กรอก - * ในช่อง หาและออกจากสนาม แทนที่เมื่อว่างเปล่า จากนั้นคลิกที่ปุ่ม แทนที่ทั้งหมดเพื่อให้ Excel ค้นหาและลบดาวทั้งหมดในเวิร์กชีต

สวัสดีเพื่อน. คุณต้องค้นหาค่าที่ตรงกันในสเปรดชีต Excel บ่อยแค่ไหน? ตัวอย่างเช่น คุณต้องค้นหาที่อยู่ของบุคคลในไดเรกทอรี หรือราคาของผลิตภัณฑ์ในรายการราคา หากงานดังกล่าวเกิดขึ้น โพสต์นี้เหมาะสำหรับคุณเท่านั้น!

ฉันทำตามขั้นตอนที่คล้ายกันทุกวัน และหากไม่มีฟังก์ชันที่อธิบายไว้ด้านล่าง ฉันคงลำบากมาก จดบันทึกและนำไปใช้ในงานของคุณ!

ค้นหาในตาราง Excel, ฟังก์ชัน VLOOKUP และ GLOOKUP

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

ไวยากรณ์ของฟังก์ชัน VLOOKUP คือ: =VLOOKUP( lookup_value; ค้นหา_ตาราง; เอาท์พุท_คอลัมน์_หมายเลข; [ประเภทการทำแผนที่]). ลองดูข้อโต้แย้ง:

• ค้นหาค่า– คุณค่าที่เราจะมองหา นี่เป็นอาร์กิวเมนต์ที่จำเป็น
• ค้นหาตาราง– อาร์เรย์ของเซลล์ที่จะทำการค้นหา คอลัมน์ที่มีค่าที่ค้นหาจะต้องเป็นคอลัมน์แรกในอาร์เรย์นี้ นี่เป็นข้อโต้แย้งที่จำเป็นเช่นกัน
• หมายเลขคอลัมน์ที่จะแสดง– หมายเลขซีเรียลของคอลัมน์ (เริ่มจากหมายเลขแรกในอาร์เรย์) ซึ่งฟังก์ชันจะแสดงข้อมูลหากค่าที่ต้องการตรงกัน อาร์กิวเมนต์ที่จำเป็น
• ประเภทการจับคู่– เลือก “1” (หรือ “TRUE”) สำหรับการจับคู่แบบอ่อน “0” (“FALSE”) สำหรับการจับคู่แบบสมบูรณ์ อาร์กิวเมนต์เป็นทางเลือก หากละเว้น การค้นหาจะดำเนินการ การแข่งขันที่ไม่เข้มงวด.

การค้นหาค่าที่ตรงกันโดยใช้ VLOOKUP

ลองดูตัวอย่างวิธีการทำงานของฟังก์ชัน VLOOKUP เมื่อประเภทการจับคู่เป็น FALSE โดยค้นหาการจับคู่แบบตรงทั้งหมด อาร์เรย์ B5:E10 ระบุสินทรัพย์ถาวรของบริษัทหนึ่ง มูลค่าตามบัญชี หมายเลขสินค้าคงคลัง และสถานที่ตั้ง เซลล์ B2 มีชื่อที่คุณต้องการค้นหาหมายเลขสินค้าคงคลังในตารางและวางไว้ในเซลล์ C2

ฟังก์ชัน VLOOKUP ใน Excel

มาเขียนสูตรกัน: =VLOOKUP(B2,B5:E10,3,FALSE)

อาร์กิวเมนต์แรกระบุว่าในตารางคุณต้องค้นหาค่าจากเซลล์ B2 เช่น คำว่า "แฟกซ์" อาร์กิวเมนต์ที่สองบอกว่าตารางที่จะค้นหาอยู่ในช่วง B5:E10 และคุณต้องค้นหาคำว่า "แฟกซ์" ในคอลัมน์แรก เช่น ในอาร์เรย์ B5:B10 อาร์กิวเมนต์ที่สามบอกโปรแกรมว่าผลลัพธ์ของการคำนวณอยู่ในคอลัมน์ที่สามของอาร์เรย์ กล่าวคือ D5:D10. อาร์กิวเมนต์ที่สี่คือ FALSE เช่น จำเป็นต้องมีการจับคู่ที่สมบูรณ์

ดังนั้น ฟังก์ชันจะรับสตริง "แฟกซ์" จากเซลล์ B2 และจะค้นหาในอาร์เรย์ B5:B10 จากบนลงล่าง เมื่อพบรายการที่ตรงกัน (บรรทัดที่ 8) ฟังก์ชันจะส่งกลับค่าที่สอดคล้องกันจากคอลัมน์ D เช่น เนื้อหา D8 นี่คือสิ่งที่เราต้องการจริงๆ ปัญหาได้รับการแก้ไขแล้ว

หากไม่พบค่าที่ค้นหา ฟังก์ชันจะส่งกลับ

ค้นหาค่าที่ตรงกันโดยใช้ VLOOKUP

ด้วยตัวเลือกนี้ใน VLOOKUP เราจึงสามารถหลีกเลี่ยงสูตรที่ซับซ้อนเพื่อค้นหาผลลัพธ์ที่ต้องการได้

อาร์เรย์ B5:C12 แสดงอัตราดอกเบี้ยของสินเชื่อโดยขึ้นอยู่กับจำนวนเงินกู้ ในเซลล์ B2 เราระบุจำนวนเงินกู้และต้องการรับอัตราสำหรับธุรกรรมดังกล่าวใน C2 งานเป็นเรื่องยากเนื่องจากจำนวนเงินสามารถเป็นอะไรก็ได้และไม่น่าจะตรงกับที่ระบุไว้ในอาร์เรย์ การค้นหาการจับคู่แบบตรงทั้งหมดไม่เหมาะสม:

จากนั้นเราเขียนสูตรสำหรับการค้นหาแบบไม่เข้มงวด: =VLOOKUP(B2,B5:C12,2,จริง). ตอนนี้จากข้อมูลทั้งหมดที่นำเสนอในคอลัมน์ B โปรแกรมจะค้นหาข้อมูลที่เล็กกว่าที่ใกล้ที่สุด นั่นคือสำหรับจำนวน 8,000 ค่า 5,000 จะถูกเลือกและเปอร์เซ็นต์ที่เกี่ยวข้องจะปรากฏขึ้น

การค้นหา VLOOKUP แบบไม่เข้มงวดใน Excel

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

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

การค้นหาข้อมูลโดยใช้ฟังก์ชัน VIEW

ฟังก์ชัน LOOKUP ทำงานคล้ายกับ VLOOKUP แต่มีไวยากรณ์ต่างกัน ฉันใช้มันเมื่อตารางข้อมูลมีหลายสิบคอลัมน์ และในการใช้ VLOOKUP คุณต้องคำนวณจำนวนคอลัมน์ผลลัพธ์เพิ่มเติม ในกรณีเช่นนี้ ฟังก์ชัน VIEW จะทำให้งานง่ายขึ้น ดังนั้นไวยากรณ์: =ดู( lookup_value; array_to_search; Array_to_display) :

• ค้นหาค่า– ข้อมูลหรือลิงค์ไปยังข้อมูลที่จะค้นหา
• อาร์เรย์เพื่อค้นหา– หนึ่งแถวหรือคอลัมน์ที่เรากำลังมองหาค่าที่คล้ายกัน เราต้องเรียงลำดับอาร์เรย์นี้จากน้อยไปหามาก
• อาร์เรย์ที่จะแสดง– ช่วงที่มีข้อมูลสำหรับแสดงผล โดยปกติแล้วจะต้องมีขนาดเท่ากับอาร์เรย์การค้นหา

เมื่อเขียนในลักษณะนี้ คุณกำลังให้การอ้างอิงที่ไม่สัมพันธ์กับอาร์เรย์ผลลัพธ์ และคุณชี้ตรงไปที่มันนั่นคือ ไม่จำเป็นต้องคำนวณจำนวนคอลัมน์เอาต์พุตก่อน เราใช้ฟังก์ชัน VIEW ในตัวอย่างแรกสำหรับฟังก์ชัน VLOOKUP (สินทรัพย์ถาวร หมายเลขสินค้าคงคลัง): =มุมมอง(B2,B5:B10,D5:D10). ปัญหาได้รับการแก้ไขเรียบร้อยแล้ว!

ฟังก์ชัน VIEW ใน Microsoft Excel

ค้นหาตามพิกัดสัมพัทธ์ ฟังก์ชัน MATCH และ INDEX

อีกวิธีหนึ่งในการค้นหาข้อมูลคือการรวมฟังก์ชัน MATCH และ INDEX

รายการแรกใช้เพื่อค้นหาค่าในอาร์เรย์และรับหมายเลขซีเรียล: SEARCH( lookup_value; ดู_อาร์เรย์; [ประเภทการจับคู่] ). อาร์กิวเมนต์ของฟังก์ชัน:

• ค้นหาค่า– อาร์กิวเมนต์ที่จำเป็น
• อาร์เรย์ที่จะดู– หนึ่งแถวหรือคอลัมน์ที่เรากำลังมองหาการจับคู่ อาร์กิวเมนต์ที่จำเป็น
• ประเภทการจับคู่– ระบุ “0” เพื่อค้นหาคู่ที่ตรงกันทุกประการ “1” สำหรับคู่ที่เล็กที่สุดที่ใกล้ที่สุด “-1” สำหรับคู่ที่ใหญ่กว่าที่ใกล้ที่สุด เนื่องจากฟังก์ชันจะค้นหาจากจุดเริ่มต้นของรายการไปยังจุดสิ้นสุด เมื่อค้นหาคอลัมน์ที่เล็กกว่าที่ใกล้ที่สุด ให้เรียงลำดับคอลัมน์การค้นหาจากมากไปน้อย และเมื่อค้นหาเพิ่มเติมให้เรียงลำดับจากน้อยไปหามาก

พบตำแหน่งของค่าที่ต้องการแล้ว ตอนนี้คุณสามารถแสดงบนหน้าจอโดยใช้ฟังก์ชันได้ ดัชนี( อาร์เรย์; line_number; [คอลัมน์_หมายเลข]) :

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

ตอนนี้เรามารวมฟังก์ชันเหล่านี้เข้าด้วยกันเพื่อให้ได้ผลลัพธ์:

ฟังก์ชัน MATCH และ INDEX ใน Excel

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

คุณรู้สึกว่าความรู้และทักษะของคุณเติบโตและแข็งแกร่งขึ้นอย่างไร? ถ้าอย่างนั้นอย่าหยุดอ่านต่อ! ในโพสต์ถัดไปเราจะพิจารณาว่ามันจะยากและน่าสนใจ!

คำถามมักจะเกิดขึ้น « วิธีค้นหาใน Excel»? ใน Excel คุณสามารถค้นหาข้อมูลต่างๆ ได้ เช่น ข้อความ ส่วนหนึ่งของข้อความ หมายเลข หมายเลขโทรศัพท์ อีเมล ที่อยู่, นามสกุล สูตร บันทึก รูปแบบเซลล์ ฯลฯ
ค้นหาเซลล์ที่จุดตัดของแถวและคอลัมน์ใน Excel – « วิธีค้นหาเซลล์ใน Excel ที่จุดตัดของแถวและคอลัมน์» ( ฟังก์ชัน INDEX ใน Excel).
ค้นหาและย้ายไปยังตำแหน่งอื่นใน Excel(เช่น ในแบบฟอร์ม)ข้อมูลหลายรายการพร้อมกัน - ดูบทความ "ค้นหาข้อมูลหลายรายการพร้อมกันใน Excel" (ฟังก์ชัน VLOOKUP ใน Excel).
หรือ ค้นหาเซลล์ที่มีลิงก์อยู่ สูตรเอ็กเซล, หากต้องการแทนที่ลิงก์ โปรดดู « เปลี่ยนลิงก์ไปยังแผ่นงานอื่นในสูตร Excel».
ค้นหาใน เซลล์ Excelพร้อมโน้ต - บทความ "แทรกบันทึกย่อใน Excel" .
สำหรับ ค้นหาอย่างรวดเร็วมีแป้นพิมพ์ลัด - Ctrl+F. คลิก ปุ่ม Ctrlและกดปุ่ม F ค้างไว้ หน้าต่างค้นหาจะปรากฏขึ้น
คุณยังสามารถเปิดหน้าต่างค้นหาได้ดังนี้: บนแท็บ "หน้าแรก" คลิกปุ่ม "ค้นหาและเลือก"
บนแท็บ "ค้นหา" ในเซลล์ "ค้นหา" ให้เขียนคำที่คุณกำลังมองหา (ส่วนหนึ่งของคำเป็นไปได้) แล้วคลิก "ค้นหาถัดไป" จะพบคำดังกล่าวคำแรก จากนั้นคลิก "ค้นหาถัดไป" และการค้นหาจะย้ายไปยังคำที่สองดังกล่าว

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

หากการค้นหาไม่พบสิ่งใดเลย และคุณทราบว่ามีข้อมูลนี้แน่นอน ให้ลองลบการเยื้องออกจากเซลล์ตาราง วิธีลบการเยื้องในเซลล์ดูบทความ " ข้อความ Excel. รูปแบบ".
ค้นหาตัวเลขใน Excelต้องมีการปรับเงื่อนไขการค้นหาเล็กน้อย - ใช้ได้ การค้นหาขั้นสูงใน Excel.
คำแนะนำ.
หากคุณทำงานกับโต๊ะเป็นเวลานานและคุณมักจะต้องค้นหาจากคำหนึ่งไปอีกคำหนึ่งจะสะดวกกว่าที่จะไม่ปิดหน้าต่างค้นหาทุกครั้ง แต่ให้ย้ายไปยังส่วนของตารางที่จะไม่รบกวน คุณสามารถย้ายไปไว้ใต้หน้าจอ โดยเหลือเพียงเซลล์ป้อนข้อมูลสำหรับคำค้นหา (“ค้นหา”) แล้วกด Enter
กล่องโต้ตอบการค้นหานี้จะยังคงอยู่บนหน้าจอเสมอ แม้ว่าคุณจะนำทางไปยังหน้าอื่นก็ตาม การใช้หน้าต่างนี้คุณสามารถค้นหาในหน้าใดก็ได้ คุณเพียงแค่ต้องเปิดใช้งานมัน เปิดหน้า. โดยคลิกเคอร์เซอร์บนบรรทัด "ค้นหา"
สำหรับการค้นหาขั้นสูงเพิ่มเติม ให้คลิกปุ่มตัวเลือก และเลือกตัวเลือกการค้นหาที่ต้องการ
ตัวอย่างเช่น เลือก "มูลค่า" จากนั้นมันจะค้นหาเบอร์ เบอร์โทรศัพท์ ฯลฯ
หากคุณต้องการค้นหาคำทั้งหมดที่มีน้ำหนักเท่ากัน แต่ในกรณี (นม นม นม ฯลฯ) เราจะเขียนสูตรโดยใช้ไวด์การ์ด ดู "สัญลักษณ์ตัวแทนใน Excel" เกี่ยวกับเรื่องนี้
ฟังก์ชัน Excel "ค้นหาและเน้น"จะช่วยไม่เพียงแต่ค้นหาข้อมูล แต่ยังแทนที่ด้วย ดูบทความ "วิธีคัดลอกสูตรใน Excel โดยไม่ต้องเปลี่ยนลิงก์"
วิธีลบช่องว่างเพิ่มเติมที่รบกวนการประมวลผลข้อมูลในตารางอ่านบทความ "วิธีลบช่องว่างเพิ่มเติมใน Excel"
ใน Excel คุณสามารถค้นหาข้อมูลต่างๆ ไม่เพียงแต่ด้วยฟังก์ชันหรือสูตรค้นหาเท่านั้น แต่ยังรวมถึงฟังก์ชันการจัดรูปแบบตามเงื่อนไขด้วย อ่านบทความ “การจัดรูปแบบตามเงื่อนไขใน Excel” เกี่ยวกับเรื่องนี้
คุณสามารถอ่านเกี่ยวกับฟังก์ชัน "ค้นหาและเลือก" ได้ในบทความ "กรองใน Excel".