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

ค้นหาง่าย

เพื่อค้นหาค่าใน สเปรดชีต Excelคุณต้องเปิดรายการแบบเลื่อนลงของเครื่องมือ "ค้นหาและแทนที่" บนแท็บ "หน้าแรก" และคลิกรายการ "ค้นหา" เอฟเฟกต์เดียวกันนี้สามารถทำได้โดยใช้แป้นพิมพ์ลัด Ctrl + F

ในกรณีที่ง่ายที่สุดในหน้าต่าง "ค้นหาและแทนที่" ที่ปรากฏขึ้น คุณจะต้องป้อนค่าที่ต้องการแล้วคลิก "ค้นหาทั้งหมด"

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

การค้นหาข้อความดำเนินการในลักษณะเดียวกัน ในกรณีนี้ ข้อความที่ค้นหาจะถูกพิมพ์ลงในแถบค้นหา

หากไม่ได้ค้นหาข้อมูลหรือข้อความในตาราง Excel ทั้งหมด จะต้องเลือกพื้นที่การค้นหาก่อน

การค้นหาขั้นสูง

สมมติว่าคุณต้องการค้นหาค่าทั้งหมดในช่วงตั้งแต่ 3000 ถึง 3999 ในกรณีนี้ ให้พิมพ์ 3??? ในแถบค้นหา ไวด์การ์ด "?" แทนที่สิ่งอื่นใด

จากการวิเคราะห์ผลลัพธ์การค้นหา สังเกตได้ว่านอกจากผลลัพธ์ที่ถูกต้อง 9 รายการแล้ว โปรแกรมยังสร้างสิ่งที่ไม่คาดคิดโดยเน้นด้วยสีแดง มีความเกี่ยวข้องกับการมีเลข 3 ในเซลล์หรือสูตร

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

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

ซึ่งหมายความว่าทำการค้นหารวมถึงในเซลล์ที่ไม่มีค่า แต่เป็นสูตร การมีหมายเลข 3 อยู่ในนั้นให้ผลลัพธ์ที่ไม่ถูกต้องสามประการ หากคุณเลือก "ค่า" เป็นขอบเขตการค้นหา คุณจะค้นหาเฉพาะข้อมูลและผลลัพธ์ที่ไม่ถูกต้องที่เกี่ยวข้องกับเซลล์สูตรจะหายไป

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

ผลลัพธ์นี้สามารถทำได้โดยการเลือกรายการ "ทั้งเซลล์" ทันที (แม้จะปล่อยค่า "สูตร" ไว้ใน "พื้นที่ค้นหา")

ตอนนี้เรามาดูรายการ "ค้นหา"

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

รายการถัดไปในหน้าต่างค้นหาขั้นสูงคือ "มุมมอง" ซึ่งมีความหมายสองประการ ค่าเริ่มต้นคือ “ตามแถว” ซึ่งหมายความว่าเซลล์จะถูกสแกนทีละแถว การเลือกค่าอื่น “ตามคอลัมน์” จะเปลี่ยนเฉพาะทิศทางการค้นหาและลำดับของผลลัพธ์เท่านั้น

เมื่อค้นหาในเอกสาร ไมโครซอฟต์ เอ็กเซลคุณสามารถใช้ไวด์การ์ดอื่น – “*” ได้ หากถือว่า "?" หมายถึงอักขระใดๆ จากนั้น "*" จะแทนที่ไม่ใช่ตัวเดียว แต่จะแทนที่อักขระจำนวนเท่าใดก็ได้ ด้านล่างนี้คือภาพหน้าจอของการค้นหาลุยเซียนา

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

ประเภทของการค้นหา

ค้นหาการแข่งขัน

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

ผลลัพธ์จะแสดงในภาพหน้าจอด้านล่าง

หากจำเป็น ผู้ใช้สามารถเปลี่ยนสีของการแสดงภาพของเซลล์ที่ตรงกันได้

การกรอง

การค้นหาอีกประเภทหนึ่งคือการกรอง สมมติว่าผู้ใช้ต้องการค้นหาค่าตัวเลขในช่วงตั้งแต่ 3000 ถึง 4000 ในคอลัมน์ B

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

มีการกล่าวถึงตัวเลือกการค้นหาต่างๆ โดยใช้ Excel 2010 เป็นตัวอย่าง จะค้นหาเวอร์ชันอื่นใน Excel ได้อย่างไร การเปลี่ยนไปใช้การกรองมีความแตกต่างกันในเวอร์ชัน 2003 ในเมนู "ข้อมูล" คุณควรเลือกคำสั่ง "ตัวกรอง", "ตัวกรองอัตโนมัติ", "เงื่อนไข" และ "ตัวกรองอัตโนมัติแบบกำหนดเอง" ตามลำดับ

วิดีโอ: ค้นหาในตาราง Excel

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

ค้นหาพร้อมกันในหลายช่วง

สำหรับตัวอย่างที่เป็นภาพ เรามาสร้างตารางง่ายๆ 3 ตารางแยกกันโดยอยู่ในช่วงที่ไม่อยู่ติดกันในชีตเดียว:

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

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

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

1. ในเซลล์ E6 ให้ป้อนค่า 20 ซึ่งเป็นเงื่อนไขสำหรับคำค้นหา
2. ในเซลล์ E7 ให้ป้อนสูตรต่อไปนี้:

ต้นทุนการผลิตต่อ 20 ชิ้น ผลิตภัณฑ์บางอย่าง

สูตรที่มี VLOOKUP ทำงานอย่างไรในหลายตาราง:

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

จากผลลัพธ์ของการค้นหาฟังก์ชันที่สาม เราได้ค่า 125 ซึ่งเป็นอาร์กิวเมนต์แรกสำหรับฟังก์ชันที่สอง เมื่อได้รับพารามิเตอร์ทั้งหมดแล้ว ฟังก์ชันที่สองจะค้นหาจำนวนคนงานที่จำเป็นสำหรับการผลิตในตารางที่สอง เป็นผลให้คืนค่า 5 ซึ่งจะถูกใช้โดยฟังก์ชันหลัก ตามข้อมูลทั้งหมดที่ได้รับ สูตรจะส่งกลับผลลัพธ์สุดท้ายของการคำนวณ กล่าวคือ ต้องใช้จำนวนเงิน 1,750 ดอลลาร์เพื่อผลิตผลิตภัณฑ์บางอย่าง 20 ชิ้น

เมื่อใช้หลักการนี้ คุณสามารถใช้สูตรสำหรับฟังก์ชัน VLOOKUP จากหลายแผ่นงานได้

สวัสดีตอนบ่ายชาว Habro ที่รัก!

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

การค้นหาทำให้ฉันมีบทความเกี่ยวกับHabréในหัวข้อที่คล้ายกันเพียงบทความเดียว - “Talmud โดยใช้สูตรใน Google SpreadSheet” ให้คำอธิบายที่ดีเกี่ยวกับสิ่งพื้นฐานสำหรับการทำงานใน Excel (แม้ว่าจะไม่ได้เกี่ยวกับ Excel 100% ก็ตาม)

ดังนั้น เมื่อรวบรวมคำขอ/งานจำนวนหนึ่งแล้ว จึงมีแนวคิดที่จะพิมพ์และเสนอ แนวทางแก้ไขที่เป็นไปได้(แม้ว่าจะไม่ใช่ทั้งหมดที่เป็นไปได้ แต่ให้ผลลัพธ์อย่างรวดเร็ว)

เราจะพูดถึงการแก้ปัญหาที่พบบ่อยที่สุดที่ผู้ใช้พบ

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

กรณีที่_1: ฟังก์ชันลอจิกและฟังก์ชั่นการจับคู่
“ฉันมีชุดของค่าในตารางและจำเป็นที่เมื่อตรงตามเงื่อนไข/ชุดเงื่อนไขบางอย่าง ค่าบางอย่างจะปรากฏขึ้น” (c) ผู้ใช้

โดยปกติข้อมูลจะแสดงในรูปแบบตาราง:

เงื่อนไข:

• หากค่าในคอลัมน์ "ปริมาณ" มากกว่า 5
• จากนั้นคุณจะต้องแสดงค่า “ไม่จำเป็นต้องมีคำสั่งซื้อ” ในคอลัมน์ “ผลลัพธ์”

ไวยากรณ์ของสูตรมีดังนี้:
ถ้า(logic_expression, [value_if_true], [value_if_false])

• นิพจน์เชิงตรรกะคือนิพจน์ที่ประเมินค่าเป็น TRUE หรือ FALSE
• Value_if_true - ค่าที่พิมพ์หากนิพจน์เชิงตรรกะเป็นจริง
• Value_if_false - ค่าที่พิมพ์หากนิพจน์เชิงตรรกะเป็นเท็จ

=IF(C5>5, “ไม่ต้องสั่ง”, “ต้องสั่ง”)

มันเกิดขึ้นที่เงื่อนไขมีความซับซ้อนมากขึ้น เช่น การปฏิบัติตามเงื่อนไข 2 ข้อขึ้นไป:

• หากค่าในคอลัมน์ “ปริมาณ” มากกว่า 5 และค่าในคอลัมน์ “ประเภท” คือ “A”

• Boolean_value1-2 ฯลฯ - เงื่อนไขที่จะทดสอบ ซึ่งการประเมินจะให้ผลลัพธ์เป็นค่า TRUE หรือ FALSE

=IF(และ(C2>5,B2=“A”),1,0)

มาลองทำให้งานซับซ้อนขึ้น - เงื่อนไขใหม่:

• หากค่าในคอลัมน์ "ปริมาณ" คือ 10 และค่าในคอลัมน์ "ประเภท" คือ "A"
• หรือค่าในคอลัมน์ปริมาณมากกว่าหรือเท่ากับ 5 และค่าประเภทคือ B
• จากนั้นคุณจะต้องแสดงค่า "1" ในคอลัมน์ "ผลลัพธ์" ไม่เช่นนั้นจะเป็น "0"

=IF(หรือ(และ(C2=10,B2=“A”); และ(C2>=5,B2=“B”)),1,0)

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

• 1 = อ
• 2 = บ
• 3 = บ
• 4 = ช

=IF(A2=1,“เอ”, IF(A2=2,“B”, IF(A2=3,“C”, IF(A2=4,“D”,0))))

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

โซลูชันทางเลือก_1:
การใช้สูตร CHOOSE
ไวยากรณ์ของฟังก์ชัน:
SELECT(index_number, value1, [value2], ...)

• Index_number - จำนวนของอาร์กิวเมนต์ค่าที่เลือก หมายเลขดัชนีต้องเป็นตัวเลขระหว่าง 1 ถึง 254 เป็นสูตร หรือการอ้างอิงไปยังเซลล์ที่มีตัวเลขระหว่าง 1 ถึง 254
• Value1, value2,... - อาร์กิวเมนต์ค่าตั้งแต่ 1 ถึง 254 ซึ่งฟังก์ชัน "SELECT" จะใช้หมายเลขดัชนีเลือกค่าหรือการดำเนินการที่จะดำเนินการ อาร์กิวเมนต์อาจเป็นตัวเลข การอ้างอิงเซลล์ ชื่อเฉพาะ สูตร ฟังก์ชัน หรือข้อความ

• 1 = อ
• 2 = บ
• 3 = บ
• 4 = ช

=ตัวเลือก(A2, “A”, “B”, “C”, “D”)

• 1 = อ
• 2 = บ
• 3 = บ
• 5 = ช

=ตัวเลือก(A31, “A”, “B”, “C”, “D”)

โซลูชันทางเลือก_2:
เรามาถึงหนึ่งในความนิยมมากที่สุด ฟังก์ชัน Excelความเชี่ยวชาญในการเปลี่ยนพนักงานออฟฟิศให้เป็น “ผู้ใช้ Excel ที่มีประสบการณ์” /sarcasm/ โดยอัตโนมัติ
ไวยากรณ์ของสูตร:
VLOOKUP(lookup_value, ตาราง, column_number, [interval_lookup])

• Search_value – ค่าที่ฟังก์ชันค้นหา
• ตารางคือช่วงของเซลล์ที่มีข้อมูล อยู่ในเซลล์เหล่านี้ที่การค้นหาจะเกิดขึ้น ค่าอาจเป็นข้อความ ตัวเลข หรือบูลีน
• Column_number - จำนวนคอลัมน์ในอาร์กิวเมนต์ "ตาราง" ซึ่งจะได้รับค่าหากมีค่าที่ตรงกัน สิ่งสำคัญคือต้องเข้าใจว่าคอลัมน์ไม่ได้นับตามตารางชีตทั่วไป (A.B,C,D ฯลฯ) แต่นับอยู่ภายในอาร์เรย์ที่ระบุในอาร์กิวเมนต์ "ตาราง"
• Interval_lookup - กำหนดว่าฟังก์ชันควรค้นหาค่าที่ตรงกันทุกประการหรือค่าที่ตรงกันโดยประมาณ

จากนั้นในส่วนการทำงานของตารางจะมีการเขียนสูตรพร้อมลิงก์ไปยังหนังสืออ้างอิงที่กรอกไว้ก่อนหน้านี้ เหล่านั้น. ในไดเร็กทอรีในคอลัมน์ “D” ค่าจากคอลัมน์ “A” จะถูกค้นหา และเมื่อพบค่าที่ตรงกัน ค่าจากคอลัมน์ “E” จะแสดงในคอลัมน์ “B”
ไวยากรณ์ของสูตร:
แสดงผลผลลัพธ์ในเซลล์ B2:

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

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

• การอ้างอิงคือเซลล์หรือช่วงของเซลล์ที่คุณต้องการส่งกลับหมายเลขคอลัมน์

=VLOOKUP($A3,$H$3:$M$6, คอลัมน์(),0)

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

• หากระบุหมายเลข 1 ในคอลัมน์ "Directory" ควรดึงข้อมูลจากตาราง "Directory_1" หากตัวเลขคือ 2 จากนั้นจากตาราง "Directory_2" ตามเดือนที่ระบุ

วิธีแก้ปัญหาที่นึกถึงได้ทันทีมีดังต่อไปนี้:

=IF($B3=1; VLOOKUP($A3,$G$3:$I$6; COLUMN()-1,0); VLOOKUP($A3,$K$3:$M$6; COLUMN()-1;0 ))

=VLOOKUP($A3, SELECT($B3,$G$3:$I$6,$K$3:$M$6), คอลัมน์()-1,0)

โบนัส: VLOOKUP ตามคุณลักษณะตั้งแต่ 2 รายการขึ้นไปในอาร์กิวเมนต์ "search_value"
เงื่อนไข:

• ลองจินตนาการว่าเช่นเคยเรามีอาร์เรย์ของข้อมูลในรูปแบบตาราง (ถ้าไม่ใช่เราจะนำข้อมูลไปไว้) เราต้องรับค่าจากอาร์เรย์ตามคุณลักษณะบางอย่างและวางไว้ในรูปแบบตารางอื่น .

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

=เชื่อมต่อ(H3;"_";I3;"_";J3)

=IFERROR(VLOOKUP(A2&"_"&B2&"_"$G$2:$K$6,5,0),0)

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

Case_3 ค้นหาค่าในอาร์เรย์หรือเมื่อ VLOOKUP ไม่สามารถช่วยเราได้

ลองพิจารณาสถานการณ์ที่เราต้องเข้าใจว่าอาร์เรย์เซลล์มีค่าที่เราต้องการหรือไม่
งาน:

• คอลัมน์ "เงื่อนไขการค้นหา" มีค่าและคุณต้องพิจารณาว่ามีอยู่ในคอลัมน์ "อาร์เรย์การค้นหา" หรือไม่

ดังที่เราเห็นแล้วว่าฟังก์ชัน "VLOOKUP" ไม่มีพลังที่นี่เพราะว่า เราไม่ได้มองหาค่าที่ตรงกันทุกประการ แต่เป็นการมีค่าที่เราต้องการในเซลล์
เพื่อแก้ปัญหาจำเป็นต้องใช้ฟังก์ชันหลายอย่างรวมกัน ได้แก่:
"ถ้า"
"หากผิดพลาด"
"ต่ำกว่า"
"หา"

เพื่อที่จะเกี่ยวกับทุกคน เราได้พูดถึง "IF" ไปแล้วก่อนหน้านี้ ดังนั้นเรามาดูฟังก์ชัน "IFERROR" กันดีกว่า

IFERROR(ค่า, error_value)

• ค่าคืออาร์กิวเมนต์ที่ถูกตรวจสอบข้อผิดพลาด
• Value_on_error - ค่าที่ส่งคืนหากมีข้อผิดพลาดเมื่อคำนวณสูตร เป็นไปได้ ประเภทต่อไปนี้ข้อผิดพลาด: #N/A, #VALUE!, #REF!, #DIV/0!, #NUMBER!, #NAME? และ #ว่างเปล่า!.

"ต่ำกว่า"

• ข้อความ - ข้อความที่แปลงเป็นตัวพิมพ์เล็ก

ตอนนี้เรามาดูไวยากรณ์ของฟังก์ชัน FIND กันดีกว่า

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

• Search_text - ข้อความที่ต้องการค้นหา
• Search_text - ข้อความที่คุณต้องการค้นหาข้อความที่ค้นหา
• Start_position - สัญลักษณ์ที่จะเริ่มการค้นหา อักขระตัวแรกในข้อความ "view_text" มีหมายเลข 1 หากไม่ได้ระบุหมายเลข จะมีค่าเริ่มต้นเป็น 1

=IF(IFERROR(ค้นหา(LINE(A2), LINE(E2),1),0)=0,“ล้มเหลว”,“บิงโก!”)

1. LOWER(A2) – แปลงอาร์กิวเมนต์ Search_Text ในเซลล์ A2 เป็นข้อความตัวพิมพ์เล็ก
2. ฟังก์ชัน FIND เริ่มค้นหาอาร์กิวเมนต์ที่แปลงแล้ว Search_Text ในอาร์เรย์ Search_Text ซึ่งถูกแปลงโดยฟังก์ชัน LOWER (E2) ให้เป็นข้อความตัวพิมพ์เล็กด้วย
3. หากฟังก์ชันค้นหารายการที่ตรงกัน เช่น ส่งกลับเลขลำดับของอักขระตัวแรกของคำ/ค่าที่ตรงกัน เงื่อนไข TRUE ในสูตร "IF" จะถูกทริกเกอร์ เนื่องจาก ค่าผลลัพธ์ไม่ใช่ศูนย์ ด้วยเหตุนี้ คอลัมน์ “ผลลัพธ์” จะแสดงค่า “บิงโก!”
4. อย่างไรก็ตาม หากฟังก์ชันไม่พบรายการที่ตรงกัน เช่น ไม่ได้ระบุหมายเลขลำดับของอักขระตัวแรกของคำ/ค่าที่ตรงกัน และส่งคืนข้อผิดพลาดแทนค่า เงื่อนไขที่รวมอยู่ในสูตร "IFERROR" จะถูกทริกเกอร์และส่งกลับค่าเท่ากับ "0" ซึ่งสอดคล้องกับ ให้เป็นเงื่อนไข FALSE ในสูตร “IF” เพราะ ค่าผลลัพธ์คือ “0” ด้วยเหตุนี้ ค่า "ล้มเหลว" จะแสดงอยู่ในคอลัมน์ "ผลลัพธ์"

ดังที่เห็นได้จากรูปด้านบนด้วยฟังก์ชัน "LOW" และ "FIND" เราค้นหาค่าที่ต้องการโดยไม่คำนึงถึงกรณีของอักขระและตำแหน่งในเซลล์ แต่เราต้องใส่ใจกับบรรทัดที่ 5 .
ข้อความค้นหาถูกตั้งค่าเป็น "111" แต่อาร์เรย์การค้นหามีค่า "คุกกี้ 1111111" แต่สูตรส่งคืนผลลัพธ์ "Bingo!" สิ่งนี้เกิดขึ้นเนื่องจากค่า "111" รวมอยู่ในชุดค่า "1111111" ดังนั้นจึงพบรายการที่ตรงกัน มิฉะนั้น เงื่อนไขนี้จะไม่ทำงาน

Case_4 การค้นหาค่าในอาเรย์ตามเงื่อนไขหลายประการ หรือเมื่อ VLOOKUP ยิ่งไม่สามารถช่วยเราได้

ลองจินตนาการถึงสถานการณ์ที่คุณต้องค้นหาค่าจาก "ตารางพร้อมผลลัพธ์" อาร์เรย์สองมิติ“Directory” สำหรับหลายเงื่อนไข ได้แก่ ค่า “ชื่อ” และ “เดือน”
รูปแบบตารางของงานจะมีลักษณะดังนี้:

เงื่อนไข:

• มีความจำเป็นต้องดึงข้อมูลลงในตารางโดยให้ผลลัพธ์เป็นไปตามความบังเอิญของเงื่อนไข "ชื่อ" และ "เดือน"

ไวยากรณ์ของฟังก์ชัน INDEX

INDEX(อาร์เรย์, row_number, [column_number])

• อาร์เรย์ - ช่วงของเซลล์ที่ค่าจะแสดงหากเงื่อนไขการค้นหาตรงกัน
• ถ้าอาร์เรย์มีเพียงแถวเดียวหรือหนึ่งคอลัมน์ อาร์กิวเมนต์ row_number หรือ column_number ตามลำดับจะเป็นทางเลือก
• ถ้าอาร์เรย์ครอบครองมากกว่าหนึ่งแถวและหนึ่งคอลัมน์ และมีเพียงอาร์กิวเมนต์ row_number และ column_number เพียงตัวเดียวเท่านั้น ฟังก์ชัน INDEX จะส่งกลับอาร์เรย์ที่ประกอบด้วยทั้งแถวหรือทั้งคอลัมน์ของอาร์กิวเมนต์อาร์เรย์
• Line_number - จำนวนบรรทัดในอาร์เรย์ที่คุณต้องการส่งคืนค่า
• column_number - จำนวนคอลัมน์ในอาร์เรย์ที่คุณต้องการส่งคืนค่า

ไวยากรณ์ของฟังก์ชัน MATCH

MATCH(lookup_value, lookup_array, [ประเภทการจับคู่])

• Lookup_value คือค่าที่ตรงกับค่าในอาร์กิวเมนต์ lookup_array อาร์กิวเมนต์ lookup_value อาจเป็นค่า (ตัวเลข ข้อความ หรือบูลีน) หรือการอ้างอิงไปยังเซลล์ที่มีค่าดังกล่าว
• Search_array - ช่วงของเซลล์ที่ทำการค้นหา
• match_type เป็นอาร์กิวเมนต์ที่เป็นทางเลือก ตัวเลขคือ -1, 0 หรือ 1

ส่วนหนึ่งของสูตร:

ตรงกัน($A4,$I$4:$I$7,0)

ตรงกัน(B$3,$J$3:$L$3,0)

ดังนั้นเราจึงได้รับพิกัดของจุด (1; #N/A) ซึ่งฟังก์ชัน “INDEX” ใช้เพื่อค้นหาในอาร์กิวเมนต์ “Array”
ฟังก์ชันที่เขียนอย่างสมบูรณ์สำหรับเซลล์ B4 จะมีลักษณะดังนี้:

=INDEX($J$4:$L$7, ตรงกัน($A4,$I$4:$I$7,0), ตรงกัน(B$3,$J$3:$L$3,0))

=INDEX($J$4:$L$7,1,#N/A))

เพื่อต่อต้านผลกระทบด้านลบนี้ เราใช้ฟังก์ชัน "IFERROR" ซึ่งเราอ่านมาก่อนหน้านี้ และแทนที่ค่าที่ส่งคืนจากข้อผิดพลาดด้วย "0" จากนั้นสูตรจะมีลักษณะดังนี้:

=IFERROR(INDEX($J$4:$L$7, MATCH($A4,$I$4:$I$7,0), MATCH(B$3,$J$3:$L$3,0)),0)

ดังที่คุณเห็นในภาพ ค่า “#N/A” จะไม่รบกวนการคำนวณครั้งต่อไปของเราอีกต่อไปโดยใช้ค่าในตารางผลลัพธ์

Case_5 การค้นหาค่าในช่วงตัวเลข

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

• ตั้งแต่ 0 ถึง 1,000 = A
• จาก 1,001 ถึง 1,500 = B
• ตั้งแต่ ค.ศ. 1501 ถึง 2000 = บี
• ตั้งแต่ปี 2544 ถึง 2500 = G
• มากกว่า 2501 = ด

ฟังก์ชัน LOOKUP ส่งกลับค่าจากแถว คอลัมน์ หรืออาร์เรย์ ฟังก์ชันมีรูปแบบวากยสัมพันธ์สองรูปแบบ: รูปแบบเวกเตอร์และอาร์เรย์

ค้นหา(lookup_value, lookup_vector, [result_vector])

• lookup_value คือค่าที่ฟังก์ชัน LOOKUP ค้นหาในเวกเตอร์แรก Lookup_value อาจเป็นตัวเลข ข้อความ บูลีน ชื่อ หรือการอ้างอิงค่า
• Watch_vector คือช่วงที่ประกอบด้วยหนึ่งแถวหรือหนึ่งคอลัมน์ ค่าในอาร์กิวเมนต์ lookup_vector อาจเป็นข้อความ ตัวเลข หรือบูลีน
• ค่าในอาร์กิวเมนต์ view_vector ต้องเรียงลำดับจากน้อยไปหามาก: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; มิฉะนั้น ฟังก์ชัน LOOKUP อาจส่งคืนผลลัพธ์ที่ไม่ถูกต้อง ข้อความตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ถือว่าเทียบเท่ากัน
• result_vector คือช่วงที่ประกอบด้วยหนึ่งแถวหรือคอลัมน์ result_vector ต้องมีขนาดเท่ากันกับ lookup_vector

=VIEW(E3,$A$3:$A$7,$B$3:$B$7)

อาร์กิวเมนต์ “View_vector” และ “Result_vector” สามารถเขียนได้ในรูปแบบอาร์เรย์ ในกรณีนี้ คุณไม่จำเป็นต้องแสดงในตารางแยกต่างหากบนแผ่นงาน Excel
ในกรณีนี้ ฟังก์ชันจะมีลักษณะดังนี้:
แสดงผลผลลัพธ์ในเซลล์ B3:
=VIEW(E3;(0;1001;1501;2001;2501);("A", "B", "C", "D", "D"))

Case_6 ผลรวมของตัวเลขตามคุณลักษณะ

หากต้องการรวมตัวเลขตามคุณลักษณะบางอย่าง คุณสามารถใช้ฟังก์ชันที่แตกต่างกัน 3 รายการได้:
SUMIF – ผลรวมด้วยแอตทริบิวต์เดียวเท่านั้น
SUMIFS – ผลรวมของคุณลักษณะหลายรายการ
SUMPRODUCT – ผลรวมของคุณลักษณะหลายรายการ
นอกจากนี้ยังมีตัวเลือกที่ใช้ "SUM" และฟังก์ชันสูตรอาร์เรย์ เมื่อสูตร "SUM" ถูกยกขึ้นเป็นอาร์เรย์:
((=SUM(()*()))
แต่วิธีนี้ค่อนข้างไม่สะดวกและครอบคลุมฟังก์ชันการทำงานทั้งหมดด้วยสูตร "SUMPRODUCT"
สำหรับรายละเอียดเพิ่มเติมเกี่ยวกับไวยากรณ์ "SUMPRODUCT":

SUMPRODUCT(อาร์เรย์1, [อาร์เรย์2], [อาร์เรย์3],...)

• Array1 เป็นอาร์เรย์แรกที่ต้องคูณคอมโพเนนต์แล้วจึงบวกผลลัพธ์
• Array2, array3… - ตั้งแต่ 2 ถึง 255 อาร์เรย์ ซึ่งจะต้องคูณส่วนประกอบแล้วจึงบวกผลลัพธ์

• ค้นหาต้นทุนรวมของการจัดส่งสำหรับผลิตภัณฑ์แต่ละรายการในช่วงเวลาหนึ่ง:

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

=SUMPRODUCT(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
ลองดูสูตรในส่วนต่างๆ:
– กำหนดเงื่อนไขการเลือกในคอลัมน์ “ชื่อ” ของตารางด้วยข้อมูลในคอลัมน์ “ชื่อ” ในตารางพร้อมผลลัพธ์

($K$3:$K$11>=B$3)*($K$3:$K$11 – เรากำหนดเงื่อนไขตามกรอบเวลา วันที่มากกว่าหรือเท่ากับวันแรกของเดือนปัจจุบัน แต่น้อยกว่าวันแรกของเดือนถัดไป ในทำนองเดียวกัน เงื่อนไขอยู่ในตารางพร้อมกับผลลัพธ์ ส่วนอาร์เรย์อยู่ในตารางที่มีข้อมูล
– คูณคอลัมน์ “ปริมาณ” และ “ราคา” ในตารางด้วยข้อมูล
ข้อได้เปรียบที่ไม่ต้องสงสัยของฟังก์ชั่นนี้คือลำดับเงื่อนไขการบันทึกอิสระสามารถเขียนในลำดับใดก็ได้ซึ่งจะไม่ส่งผลกระทบต่อผลลัพธ์
ผลลัพธ์:

ตอนนี้เรามาทำให้เงื่อนไขซับซ้อนขึ้นและเพิ่มข้อกำหนดที่การเลือกชื่อ "คุกกี้" จะเกิดขึ้นเฉพาะในคลาส "เล็ก" และ "ใหญ่" และสำหรับชื่อ "ม้วน" ทุกอย่างยกเว้นคลาส "พร้อมแยม":

แสดงผลผลลัพธ์ในเซลล์ B4:

=SUMPRODUCT(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
มีการเพิ่มเงื่อนไขใหม่ในสูตรสำหรับการเลือกคุกกี้:

(($I$3:$I$11=“เล็ก”)+($I$3:$I$11=“ใหญ่”))

อย่างที่คุณเห็น เงื่อนไขตั้งแต่ 2 เงื่อนไขขึ้นไปในคอลัมน์เดียวจะถูกแยกออกเป็นกลุ่มแยกต่างหากโดยใช้สัญลักษณ์ "+" และใส่เงื่อนไขไว้ในวงเล็บเพิ่มเติม
มีการเพิ่มเงื่อนไขใหม่ลงในสูตรสำหรับการเลือกแบบม้วน:

=SUMPRODUCT(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11 “กับแยม”);($L$3:$L$11)*($K$3:$K$11))

นี้:

($I$3:$I$11<>"กับแยม")

– อันที่จริง ในสูตรนี้เป็นไปได้ที่จะเขียนเงื่อนไขการเลือกในลักษณะเดียวกับเมื่อเลือกด้วยคุกกี้ แต่คุณจะต้องระบุเงื่อนไขสามข้อในสูตร ในกรณีนี้ จะง่ายกว่าในการเขียนข้อยกเว้น - ไม่เท่ากับ “กับแยม” สำหรับสิ่งนี้เราใช้ค่า "<>».
โดยทั่วไป หากทราบกลุ่มของคุณลักษณะ/คลาสล่วงหน้า จะเป็นการดีกว่าที่จะรวมเข้าเป็นกลุ่มเหล่านี้โดยสร้างหนังสืออ้างอิง แทนที่จะเขียนเงื่อนไขทั้งหมดลงในฟังก์ชันโดยขยายให้พอง
ผลลัพธ์:

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

ขอบคุณสำหรับเวลาของคุณ!

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

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

วิธีที่ 1: การค้นหาแบบง่าย

การค้นหาข้อมูลอย่างง่ายใน Excel ช่วยให้คุณค้นหาเซลล์ทั้งหมดที่มีชุดอักขระที่ป้อนลงในหน้าต่างค้นหา (ตัวอักษร ตัวเลข คำ ฯลฯ) โดยไม่คำนึงถึงขนาดตัวพิมพ์

วิธีที่ 2: ค้นหาตามช่วงเซลล์ที่ระบุ

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

วิธีที่ 3: การค้นหาขั้นสูง

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

นอกจากนี้ ไม่เพียงแต่เนื้อหาของเซลล์ใดเซลล์หนึ่งเท่านั้น แต่ยังรวมถึงที่อยู่ขององค์ประกอบที่อ้างถึงในผลลัพธ์ด้วย ตัวอย่างเช่น เซลล์ E2 มีสูตรที่เป็นผลรวมของเซลล์ A4 และ C3 ผลรวมนี้คือ 10 และเป็นตัวเลขนี้ที่ปรากฏในเซลล์ E2 แต่หากเราป้อนตัวเลข "4" ในการค้นหา เซลล์ E2 เดียวกันก็จะอยู่ในผลลัพธ์ สิ่งนี้เกิดขึ้นได้อย่างไร? เพียงเซลล์ E2 มีที่อยู่สำหรับเซลล์ A4 เป็นสูตร ซึ่งรวมเฉพาะหมายเลข 4 ที่ต้องการเท่านั้น

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

1. หลังจากเปิดหน้าต่างแล้ว "ค้นหาและแทนที่"โดยใช้วิธีใดๆ ที่อธิบายไว้ข้างต้น คลิกที่ปุ่ม "ตัวเลือก".



2. เครื่องมือเพิ่มเติมจำนวนหนึ่งสำหรับจัดการการค้นหาจะปรากฏในหน้าต่าง ตามค่าเริ่มต้น เครื่องมือเหล่านี้ทั้งหมดจะอยู่ในสถานะเดียวกับการค้นหาปกติ แต่สามารถปรับเปลี่ยนได้หากจำเป็น

   

   ตามค่าเริ่มต้น ฟังก์ชัน “คดีตรงกัน”และ "ทั้งเซลล์"ถูกปิดใช้งาน แต่หากเราทำเครื่องหมายในช่องถัดจากรายการที่เกี่ยวข้อง ในกรณีนี้ เมื่อสร้างผลลัพธ์ กรณีที่ป้อนและการจับคู่แบบตรงทั้งหมดจะถูกนำมาพิจารณาด้วย หากคุณป้อนคำด้วยตัวอักษรตัวเล็ก เซลล์ที่มีการสะกดของคำนี้ด้วยอักษรตัวใหญ่จะไม่ปรากฏในผลการค้นหาอีกต่อไป ดังที่เคยเป็นโดยค่าเริ่มต้น นอกจากนี้หากเปิดใช้งานฟังก์ชันแล้ว "ทั้งเซลล์"จากนั้นจะมีการเพิ่มเฉพาะองค์ประกอบที่มีชื่อตรงกันทุกประการในผลลัพธ์ ตัวอย่างเช่น หากคุณตั้งค่าคำค้นหา "Nikolaev" เซลล์ที่มีข้อความ "Nikolaev A.D." จะไม่ถูกเพิ่มลงในผลการค้นหา

   

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

   

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

   

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

   

   คุณสามารถระบุการค้นหาที่แม่นยำยิ่งขึ้นได้โดยคลิกที่ปุ่ม "รูปแบบ".

   

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

   

   หากคุณต้องการใช้รูปแบบของเซลล์ใดเซลล์หนึ่งให้คลิกที่ปุ่มที่ด้านล่างของหน้าต่าง "ใช้รูปแบบของเซลล์นี้...".

   

   หลังจากนั้นเครื่องมือจะปรากฏเป็นรูปปิเปต เมื่อใช้มัน คุณสามารถเลือกเซลล์ที่มีรูปแบบที่คุณจะใช้

   

   หลังจากกำหนดรูปแบบการค้นหาแล้วให้คลิกที่ปุ่ม "ตกลง".

   

   มีหลายครั้งที่คุณไม่จำเป็นต้องค้นหาด้วยวลีที่เฉพาะเจาะจง แต่ต้องค้นหาเซลล์ที่มีคำค้นหาอยู่ในลำดับใดๆ แม้ว่าจะถูกคั่นด้วยคำและสัญลักษณ์อื่นๆ ก็ตาม จากนั้นจะต้องเน้นคำเหล่านี้ด้วยเครื่องหมาย “*” ทั้งสองด้าน ตอนนี้ผลการค้นหาจะแสดงเซลล์ทั้งหมดที่มีคำเหล่านี้ในลำดับใดก็ได้



3. เมื่อตั้งค่าการค้นหาเรียบร้อยแล้ว ให้คลิกที่ปุ่ม "ค้นหาทุกสิ่ง"หรือ "ค้นหาต่อไป"เพื่อไปที่ผลการค้นหา

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

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

ในบทความนี้

ค้นหาค่าในรายการแนวตั้งโดยการจับคู่แบบตรงทั้งหมด

คุณสามารถใช้ฟังก์ชัน VLOOKUP หรือฟังก์ชัน INDEX และ MATCH ร่วมกันเพื่อดำเนินการนี้ได้

ตัวอย่างการใช้ฟังก์ชัน VLOOKUP

ฟังก์ชัน VLOOKUP

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

มันหมายความว่าอะไร:

=INDEX(คุณต้องส่งคืนค่าจาก C2:C10 ซึ่งจะตรงกับ MATCH(ค่าแรก "กะหล่ำปลี" ในอาร์เรย์ B2:B10))

สูตรค้นหาค่าแรกในเซลล์ C2:C10 ที่สอดคล้องกับ กะหล่ำปลี(ใน B7) และส่งกลับค่าใน C7 ( 100 ) - ค่าแรกที่สอดคล้องกัน กะหล่ำปลี.

สำหรับข้อมูลเพิ่มเติม โปรดดูที่ ฟังก์ชัน INDEX และ ฟังก์ชัน MATCH

ค้นหาค่าในรายการแนวตั้งโดยการจับคู่โดยประมาณ

เมื่อต้องการทำเช่นนี้ ให้ใช้ฟังก์ชัน VLOOKUP

สำคัญ:ตรวจสอบให้แน่ใจว่าค่าในแถวแรกเรียงลำดับจากน้อยไปหามาก

ในตัวอย่างข้างต้น ฟังก์ชัน VLOOKUP จะค้นหาชื่อของนักเรียนที่มีการมาสาย 6 ครั้งในช่วง A2:B7 ไม่มีรายการในตารางสำหรับ 6 ล่าช้า ดังนั้นฟังก์ชัน VLOOKUP จะค้นหาคู่ที่ตรงกันสูงสุดถัดไปที่ต่ำกว่า 6 และค้นหาค่า 5 ที่เกี่ยวข้องกับชื่อ เดฟและจึงกลับมา เดฟ.

สำหรับข้อมูลเพิ่มเติม โปรดดูที่ฟังก์ชัน VLOOKUP

การค้นหาค่าแนวตั้งในรายการขนาดที่ไม่รู้จักด้วยการจับคู่แบบตรงทั้งหมด

ในการดำเนินการนี้ ให้ใช้ฟังก์ชัน OFFSET และ MATCH

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

ค1คือเซลล์ด้านซ้ายบนสุดของช่วง (หรือที่เรียกว่าเซลล์เริ่มต้น)

จับคู่("ส้ม"; C2: C7; 0)มองหาสีส้มในช่วง C2:C7 คุณไม่ควรรวมเซลล์เริ่มต้นในช่วง

1 - จำนวนคอลัมน์ทางด้านขวาของเซลล์เริ่มต้นที่ควรส่งคืนค่าที่ส่งคืน ในตัวอย่างของเรา ค่าที่ส่งคืนอยู่ในคอลัมน์ D ฝ่ายขาย.

ค้นหาค่าในรายการในแนวนอนโดยการจับคู่แบบตรงทั้งหมด

ในการดำเนินการนี้ จะใช้ฟังก์ชัน GLOOKUP ด้านล่างนี้เป็นตัวอย่าง

ฟังก์ชัน LOOKUP ค้นหาคอลัมน์ ฝ่ายขายและส่งกลับค่าจากบรรทัดที่ 5 ในช่วงที่ระบุ

สำหรับข้อมูลเพิ่มเติม โปรดดูที่ฟังก์ชัน GLOOKUP

สร้างสูตรการค้นหาโดยใช้ตัวช่วยสร้างการค้นหา (Excel 2007 เท่านั้น)

บันทึก: Add-in ตัวช่วยสร้างการค้นหาถูกยกเลิกใน Excel 2010 ฟังก์ชันนี้ถูกแทนที่ด้วยตัวช่วยสร้างฟังก์ชันและฟังก์ชันการค้นหาและการอ้างอิง (อ้างอิง) ที่พร้อมใช้งาน

ใน Excel 2007 ตัวช่วยสร้างการค้นหาจะสร้างสูตรการค้นหาโดยยึดตามข้อมูลในเวิร์กชีตที่มีส่วนหัวของแถวและคอลัมน์ ตัวช่วยสร้างการค้นหาช่วยให้คุณค้นหาค่าอื่นๆ ในแถวเมื่อคุณทราบค่าในคอลัมน์เดียว และในทางกลับกัน ตัวช่วยสร้างการค้นหาใช้ดัชนีและ MATCH ในสูตรที่สร้างขึ้น