ตัวอย่าง VLOOKUP ขั้นสูง: การค้นหาแบบหลายเกณฑ์

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

• ค้นหาใน Excel ตามเกณฑ์หลายเกณฑ์
• เราแยกค่าที่ 2, 3 และอื่น ๆ โดยใช้ VLOOKUP
• ดึงค่าที่ซ้ำกันทั้งหมดที่ต้องการ
• XNUMXD ค้นหาตามแถวและคอลัมน์ที่รู้จัก
• ใช้ VLOOKUP หลายรายการในสูตรเดียว
• การแทนที่ข้อมูลแบบไดนามิกจากตารางต่างๆ

ค้นหาใน Excel ตามเกณฑ์หลายเกณฑ์

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

ตัวอย่างที่ 1: ค้นหาตาม 2 เกณฑ์ที่แตกต่างกัน

สมมติว่าเรามีรายการคำสั่งซื้อและเราต้องการที่จะค้นหา ปริมาณสินค้า (จำนวน) ตามเกณฑ์สองข้อ – ชื่อลูกค้า (ลูกค้า) หรือ Названиепродукта (ผลิตภัณฑ์). เรื่องนี้ซับซ้อนเนื่องจากผู้ซื้อแต่ละรายสั่งซื้อสินค้าหลายประเภทดังตารางด้านล่าง

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

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

– สูตรนี้จะส่งคืนผลลัพธ์ 15ตรงตามตัวสินค้า กระเช้าแอปเปิ้ลเพราะเป็นค่าแรกที่ตรงกัน

มีวิธีแก้ปัญหาง่ายๆ คือ สร้างคอลัมน์เพิ่มเติมเพื่อรวมเกณฑ์ที่ต้องการทั้งหมด ในตัวอย่างของเรา นี่คือคอลัมน์ ชื่อลูกค้า (ลูกค้า) หรือ Названиепродукта (ผลิตภัณฑ์). อย่าลืมว่าคอลัมน์ที่ผสานจะต้องเป็นคอลัมน์ซ้ายสุดในช่วงการค้นหาเสมอ เนื่องจากเป็นคอลัมน์ทางซ้ายที่ฟังก์ชัน VPR เงยหน้าขึ้นเมื่อมองหาค่า

ดังนั้น คุณจึงเพิ่มคอลัมน์เสริมลงในตารางและคัดลอกสูตรต่อไปนี้ไปไว้บนเซลล์ทั้งหมด =B2&C2. หากคุณต้องการให้สตริงอ่านง่ายขึ้น คุณสามารถแยกค่าที่รวมกันด้วยการเว้นวรรค: =B2&» «&C2. หลังจากนั้นคุณสามารถใช้สูตรต่อไปนี้:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

เซลล์อยู่ที่ไหน B1 มีค่าที่ต่อกันของอาร์กิวเมนต์ lookup_value (lookup_value) และ 4 - การโต้แย้ง col_index_num (column_number) คือหมายเลขของคอลัมน์ที่มีข้อมูลที่จะดึงข้อมูล

ตัวอย่างที่ 2: VLOOKUP ตามเกณฑ์สองข้อโดยมีการดูตารางบนชีตอื่น

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

ดังในตัวอย่างก่อนหน้านี้ คุณจะต้องมีคอลัมน์เสริมในตารางค้นหาด้วยค่าที่รวมกัน คอลัมน์นี้ต้องเป็นคอลัมน์ซ้ายสุดในช่วงการค้นหา

ดังนั้นสูตรด้วย VPR อาจเป็นเช่นนี้:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

ในที่นี้ คอลัมน์ B และ C ประกอบด้วยชื่อลูกค้าและชื่อผลิตภัณฑ์ ตามลำดับ และลิงก์ คำสั่งซื้อ!$A&$2:$D$2 กำหนดตารางเพื่อค้นหาในชีตอื่น

ในการทำให้สูตรอ่านง่ายขึ้น คุณสามารถตั้งชื่อช่วงมุมมอง จากนั้นสูตรจะดูง่ายขึ้นมาก:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

เพื่อให้สูตรทำงานได้ ค่าในคอลัมน์ซ้ายสุดของตารางที่คุณกำลังดูจะต้องรวมกันในลักษณะเดียวกับเกณฑ์การค้นหา ในรูปด้านบน เราได้รวมค่า u2bu2band ใส่ช่องว่างระหว่างกัน เช่นเดียวกับที่คุณต้องทำในอาร์กิวเมนต์แรกของฟังก์ชัน (BXNUMX& “” & CXNUMX)

จำไว้! ฟังก์ชัน VPR จำกัดไว้ที่ 255 อักขระ ไม่สามารถค้นหาค่าที่มีความยาวเกิน 255 อักขระได้ โปรดระลึกไว้เสมอว่าความยาวของค่าที่ต้องการไม่เกินขีดจำกัดนี้

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

เราแยกค่าที่ 2, 3 และอื่น ๆ โดยใช้ VLOOKUP

คุณรู้อยู่แล้วว่า VPR สามารถคืนค่าที่ตรงกันได้เพียงค่าเดียว แม่นยำยิ่งขึ้น คือค่าแรกที่พบ แต่ถ้าค่านี้ซ้ำหลายครั้งในอาร์เรย์ที่ดู และคุณต้องการแยกค่าที่ 2 หรือ 3 ออกมา เกิดอะไรขึ้นถ้าค่าทั้งหมด? ปัญหาดูซับซ้อน แต่มีทางแก้ไข!

สมมติว่าหนึ่งคอลัมน์ของตารางมีชื่อของลูกค้า (ชื่อลูกค้า) และอีกคอลัมน์หนึ่งมีผลิตภัณฑ์ (ผลิตภัณฑ์) ที่พวกเขาซื้อ เรามาลองค้นหารายการที่ 2, 3 และ 4 ที่ซื้อโดยลูกค้ารายหนึ่งกัน

วิธีที่ง่ายที่สุดคือการเพิ่มคอลัมน์เสริมก่อนคอลัมน์ ชื่อลูกค้า และกรอกชื่อลูกค้าด้วยหมายเลขซ้ำของแต่ละชื่อ เช่น จอห์น โด1, จอห์น โด2 ฯลฯ เราจะทำเคล็ดลับด้วยการนับโดยใช้ฟังก์ชัน COUNTIF (COUNTIF) เนื่องจากชื่อลูกค้าอยู่ในคอลัมน์ B:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

หลังจากนั้นคุณสามารถใช้ฟังก์ชันปกติได้ VPRเพื่อค้นหาคำสั่งซื้อที่ต้องการ ตัวอย่างเช่น:

• หา 2-TH รายการที่ลูกค้าสั่ง แดนบราวน์:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• หา 3-TH รายการที่ลูกค้าสั่ง แดนบราวน์:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

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

หากคุณกำลังมองหา 2-E ทำซ้ำได้โดยไม่ต้องใช้คอลัมน์เสริมโดยการสร้างสูตรที่ซับซ้อนมากขึ้น:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

ในสูตรนี้:

• $ F $ 2 – เซลล์ที่มีชื่อของผู้ซื้อ (ไม่มีการเปลี่ยนแปลง โปรดทราบ – ลิงก์เป็นแบบสัมบูรณ์)
• $ข$ – คอลัมน์ ชื่อลูกค้า;
• Table4 – ตารางของคุณ (ที่นี่อาจเป็นช่วงปกติก็ได้)
• $ C16 – เซลล์สุดท้ายของตารางหรือช่วงของคุณ

สูตรนี้ค้นหาเฉพาะค่าที่ตรงกันที่สองเท่านั้น หากคุณต้องการแยกการทำซ้ำที่เหลือ ให้ใช้วิธีแก้ไขปัญหาก่อนหน้า

หากคุณต้องการรายการการแข่งขันทั้งหมด – ฟังก์ชั่น VPR นี่ไม่ใช่ตัวช่วย เนื่องจากจะส่งกลับค่าครั้งละหนึ่งค่าเท่านั้น – ระยะเวลา แต่ Excel มีฟังก์ชัน ดัชนี (INDEX) ซึ่งสามารถรับมือกับงานนี้ได้อย่างง่ายดาย สูตรดังกล่าวจะมีลักษณะอย่างไรคุณจะได้เรียนรู้ในตัวอย่างต่อไปนี้

ดึงค่าที่ซ้ำกันทั้งหมดที่ต้องการ

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

ตัวอย่างเช่น สูตรด้านล่างจะค้นหาการทำซ้ำของค่าทั้งหมดจากเซลล์ F2 ในช่วง B2:B16 และส่งกลับผลลัพธ์จากแถวเดียวกันในคอลัมน์ C

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

ป้อนสูตรอาร์เรย์นี้ลงในเซลล์ที่อยู่ติดกันหลายเซลล์ เช่น เซลล์ F4: F8ดังแสดงในรูปด้านล่าง จำนวนเซลล์ต้องเท่ากับหรือมากกว่าจำนวนการทำซ้ำสูงสุดของค่าที่ค้นหา อย่าลืมคลิก Ctrl + Shift + Enterเพื่อป้อนสูตรอาร์เรย์ให้ถูกต้อง

หากคุณสนใจที่จะเข้าใจว่ามันทำงานอย่างไร มาดูรายละเอียดของสูตรกันสักหน่อย:

1 Part:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 – เปรียบเทียบค่าในเซลล์ F2 กับแต่ละค่าในช่วง B2:B16 หากพบการจับคู่แสดงว่านิพจน์ STRING(C2:C16)-1 ส่งกลับจำนวนบรรทัดที่สอดคล้องกัน (value -1 อนุญาตให้คุณไม่รวมบรรทัดส่วนหัว) หากไม่มีรายการที่ตรงกัน ฟังก์ชัน IF (IF) ส่งคืนสตริงว่าง

ฟังก์ชันผลลัพธ์ IF (IF) จะมีอาร์เรย์แนวนอนดังกล่าว: {1,"",3,"",5,"","","","","","",12,"","",""}

2 Part:

ROW()-3

СТРОКА()-3

ที่นี่ฟังก์ชั่น แถว (LINE) ทำหน้าที่เป็นเคาน์เตอร์เสริม เนื่องจากสูตรถูกคัดลอกลงในเซลล์ F4:F9 เราจึงลบตัวเลข 3 จากฟังก์ชันผลลัพธ์เพื่อรับค่า 1 ในเซลล์ F4 (บรรทัดที่ 4 ลบ 3) เพื่อรับ 2 ในเซลล์ F5 (บรรทัดที่ 5 ลบ 3) เป็นต้น

3 Part:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

ฟังก์ชัน ขนาดเล็ก (เล็ก) ส่งคืน น-โอ้ ค่าที่น้อยที่สุดในอาร์เรย์ข้อมูล ในกรณีของเราตำแหน่งใด (จากที่เล็กที่สุด) ที่จะส่งคืนจะถูกกำหนดโดยฟังก์ชัน แถว (LINE) (ดูตอนที่ 2). ดังนั้นสำหรับเซลล์ F4 ฟังก์ชัน เล็ก({array},1) รับคืน 1-TH (เล็กที่สุด) องค์ประกอบอาร์เรย์เช่น 1. สำหรับเซลล์ F5 รับคืน 2-TH องค์ประกอบที่เล็กที่สุดในอาร์เรย์ นั่นคือ 3ฯลฯ

4 Part:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

ฟังก์ชัน ดัชนี (INDEX) จะส่งกลับค่าของเซลล์เฉพาะในอาร์เรย์ C2:C16. สำหรับเซลล์ F4 ฟังก์ชัน ดัชนี($C$2:$C$16) จะกลับมา กระเช้าแอปเปิ้ลสำหรับ F5 ฟังก์ชัน ดัชนี($C$2:$C$16) จะกลับมา ขนม' เป็นต้น

5 Part:

IFERROR()

ЕСЛИОШИБКА()

สุดท้ายเราใส่สูตรในฟังก์ชัน การอ้างอิง (IFERROR) เพราะคุณไม่น่าจะพอใจกับข้อความแสดงข้อผิดพลาด #ที่ (#N/A) หากจำนวนเซลล์ที่คัดลอกสูตรไปนั้นน้อยกว่าจำนวนค่าที่ซ้ำกันในช่วงที่ดูอยู่

XNUMXD ค้นหาตามแถวและคอลัมน์ที่รู้จัก

การค้นหา XNUMXD ใน Excel เกี่ยวข้องกับการค้นหาค่าด้วยหมายเลขแถวและคอลัมน์ที่ทราบ กล่าวคือ คุณกำลังแยกค่าเซลล์ที่จุดตัดของแถวและคอลัมน์หนึ่งๆ

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

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

ฟังก์ชัน VLOOKUP และ MATCH

คุณสามารถใช้ฟังก์ชั่นมากมาย VPR (VLOOKUP) และ เปิดเผยมากขึ้น (MATCH) เพื่อหาค่าที่จุดตัดของทุ่งนา Названиепродукта (สตริง) และ เดือน (คอลัมน์) ของอาร์เรย์ที่เป็นปัญหา:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

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

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

แปลเป็นภาษามนุษย์ สูตรนี้หมายถึง:

• เรากำลังมองหาตัวละคร "Mar" – อาร์กิวเมนต์ lookup_value (lookup_value);
• มองในเซลล์จาก A1 ถึง I1 – ข้อโต้แย้ง lookup_array (lookup_array);
• ส่งคืนการจับคู่แบบตรงทั้งหมด – อาร์กิวเมนต์ match_type (match_type)

การใช้ 0 ในอาร์กิวเมนต์ที่สาม คุณพูดว่า functions เปิดเผยมากขึ้น มองหาค่าแรกที่ตรงกับค่าที่คุณต้องการ ซึ่งเทียบเท่ากับมูลค่า FALSE (FALSE) สำหรับอาร์กิวเมนต์ที่สี่ VPR.

นี่คือวิธีที่คุณสามารถสร้างสูตรการค้นหาแบบสองทางใน Excel หรือที่เรียกว่าการค้นหาแบบสองมิติหรือการค้นหาแบบสองทิศทาง

ฟังก์ชัน SUMPRODUCT

ฟังก์ชัน SUMPRODUCT (SUMPRODUCT) ส่งกลับผลรวมของผลิตภัณฑ์ของอาร์เรย์ที่เลือก:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

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

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

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

ช่วงที่มีชื่อและตัวดำเนินการทางแยก

หากคุณไม่คุ้นเคยกับสูตร Excel ที่ซับซ้อนเหล่านั้น คุณอาจชอบวิธีที่เห็นภาพและน่าจดจำนี้:

1. เลือกตาราง เปิดแท็บ สูตร (สูตร) ​​และคลิก สร้างจากการเลือก (สร้างจากการเลือก).
2. ทำเครื่องหมายในช่อง แถวบนสุด (ในบรรทัดด้านบน) และ คอลัมน์ซ้าย (ในคอลัมน์ด้านซ้าย) Microsoft Excel จะกำหนดชื่อให้กับช่วงต่างๆ จากค่าในแถวบนสุดและคอลัมน์ด้านซ้ายของสเปรดชีตของคุณ ตอนนี้คุณสามารถค้นหาโดยใช้ชื่อเหล่านี้ได้โดยตรงโดยไม่ต้องสร้างสูตร
3. ในเซลล์ว่างใดๆ ให้เขียน =row_name คอลัมน์_ชื่อตัวอย่างเช่นเช่นนี้:

   =เลมอนมาร์

   … หรือในทางกลับกัน:

   = มาร์มะนาว

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

เมื่อคุณป้อนชื่อ Microsoft Excel จะแสดงรายการคำแนะนำเครื่องมือพร้อมรายชื่อที่ตรงกัน เช่นเดียวกับเมื่อคุณป้อนสูตร

4. ข่าวประชา เข้าสู่ และตรวจสอบผลลัพธ์

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

ใช้ VLOOKUP หลายรายการในสูตรเดียว

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

ลองมาดูตัวอย่างต่อไปนี้ เรามีตารางหลักพร้อมคอลัมน์ SKU (ใหม่)ซึ่งคุณต้องการเพิ่มคอลัมน์ที่มีราคาที่สอดคล้องกันจากตารางอื่น นอกจากนี้ เรามีตารางค้นหา 2 ตาราง รายการแรก (ตารางค้นหา 1) มีตัวเลขที่อัปเดต SKU (ใหม่) และชื่อผลิตภัณฑ์และที่สอง (ตารางค้นหา 2) – ชื่อผลิตภัณฑ์และตัวเลขเก่า SKU (เก่า).

ในการเพิ่มราคาจากตารางค้นหาที่สองไปยังตารางหลัก คุณต้องดำเนินการที่เรียกว่า double VPR หรือซ้อนกัน VPR.

1. เขียนฟังก์ชัน VPRซึ่งพบชื่อผลิตภัณฑ์ในตาราง ตารางค้นหา 1การใช้ SKUเป็นค่าที่ต้องการ:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   Here ใหม่_SKU – ชื่อ range $ก:$ข ในตาราง ตารางค้นหา 1, 2 – นี่คือคอลัมน์ B ซึ่งมีชื่อสินค้า (ดูภาพด้านบน)

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

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   Here ราคา – ชื่อ range $ A: $ C ในตาราง ตารางค้นหา 2, 3 คือคอลัมน์ C ที่มีราคา

รูปด้านล่างแสดงผลที่ส่งคืนโดยสูตรที่เราสร้างขึ้น:

การแทนที่ข้อมูลแบบไดนามิกจากตารางต่างๆ โดยใช้ VLOOKUP และ INDIRECT

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

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

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

หากคุณมีรายงานดังกล่าวเพียงสองฉบับ คุณสามารถใช้สูตรง่ายๆ ที่มีฟังก์ชันอย่างน่าขายหน้าได้ VPR и IF (IF) เพื่อเลือกรายงานที่ต้องการค้นหา:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

ที่ไหน:

• $D$2 เป็นเซลล์ที่มีชื่อผลิตภัณฑ์ โปรดทราบว่าเราใช้การอ้างอิงแบบสัมบูรณ์ที่นี่เพื่อหลีกเลี่ยงการเปลี่ยนแปลงค่าการค้นหาเมื่อคัดลอกสูตรไปยังเซลล์อื่น
• $ D3 เป็นเซลล์ที่มีชื่อภูมิภาค เรากำลังใช้การอ้างอิงคอลัมน์แบบสัมบูรณ์และการอ้างอิงแถวแบบสัมพัทธ์ เนื่องจากเราวางแผนที่จะคัดลอกสูตรไปยังเซลล์อื่นๆ ในคอลัมน์เดียวกัน
• FL_ซัลes и CA_ฝ่ายขาย – ชื่อของตาราง (หรือช่วงที่มีชื่อ) ที่มีรายงานการขายที่เกี่ยวข้อง คุณสามารถใช้ชื่อแผ่นงานปกติและการอ้างอิงช่วงเซลล์ได้ ตัวอย่างเช่น 'แผ่น FL'!$A$3:$B$10แต่ช่วงที่ตั้งชื่อสะดวกกว่ามาก

อย่างไรก็ตาม เมื่อมีตารางดังกล่าวจำนวนมาก ฟังก์ชัน IF ไม่ใช่ทางออกที่ดีที่สุด คุณสามารถใช้ฟังก์ชันแทนได้ ทางอ้อม (INDIRECT) เพื่อส่งคืนช่วงการค้นหาที่ต้องการ

อย่างที่ทราบกันดีอยู่แล้วว่าฟังก์ชัน ทางอ้อม ใช้เพื่อส่งคืนลิงก์ที่กำหนดโดยสตริงข้อความ ซึ่งเป็นสิ่งที่เราต้องการในตอนนี้ ดังนั้นจงแทนที่นิพจน์ในสูตรข้างต้นอย่างกล้าหาญด้วยฟังก์ชัน IF เพื่อเชื่อมโยงกับฟังก์ชัน ทางอ้อม. นี่คือการรวมกัน VPR и ทางอ้อม ใช้งานได้ดีกับ:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

ที่ไหน:

• $D$2 – นี่คือเซลล์ที่มีชื่อของผลิตภัณฑ์ ไม่มีการเปลี่ยนแปลงเนื่องจากลิงก์แบบสัมบูรณ์
• $ D3 คือเซลล์ที่มีส่วนแรกของชื่อภูมิภาค ในตัวอย่างของเรา this FL.
• _ฝ่ายขาย – ส่วนร่วมของชื่อช่วงหรือตารางที่มีชื่อทั้งหมด เมื่อรวมกับค่าในเซลล์ D3 จะสร้างชื่อแบบเต็มของช่วงที่ต้องการ ด้านล่างนี้คือรายละเอียดบางส่วนสำหรับผู้ที่ยังใหม่ต่อฟังก์ชันนี้ ทางอ้อม.

ทางอ้อมและ VLOOKUP ทำงานอย่างไร

ก่อนอื่น ให้ฉันเตือนคุณเกี่ยวกับไวยากรณ์ของฟังก์ชัน ทางอ้อม (ทางอ้อม):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

อาร์กิวเมนต์แรกอาจเป็นการอ้างอิงเซลล์ (สไตล์ A1 หรือ R1C1) ชื่อช่วง หรือสตริงข้อความ อาร์กิวเมนต์ที่สองกำหนดลักษณะของลิงค์ที่มีอยู่ในอาร์กิวเมนต์แรก:

• A1ถ้าอาร์กิวเมนต์คือ ทรูโค้ด (TRUE) หรือไม่ระบุ
• R1C1ถ้า FAS E (เท็จ).

ในกรณีของเราลิงค์มีสไตล์ A1เพื่อให้คุณสามารถละทิ้งอาร์กิวเมนต์ที่สองและโฟกัสไปที่อาร์กิวเมนต์แรกได้

กลับมาที่รายงานการขายของเรากัน หากคุณจำได้ รายงานแต่ละฉบับจะเป็นตารางที่แยกจากกันซึ่งอยู่บนแผ่นงานแยกต่างหาก เพื่อให้สูตรทำงานได้อย่างถูกต้อง คุณต้องตั้งชื่อตารางของคุณ (หรือช่วง) และชื่อทั้งหมดต้องมีส่วนร่วม ตัวอย่างเช่นเช่นนี้: CA_ฝ่ายขาย, FL_การขาย, TX_ยอดขาย และอื่นๆ อย่างที่คุณเห็น “_Sales” มีอยู่ในชื่อทั้งหมด

ฟังก์ชัน ทางอ้อม เชื่อมต่อค่าในคอลัมน์ D และสตริงข้อความ “_Sales” จึงบอก VPR ในตารางที่จะค้นหา ถ้าเซลล์ D3 มีค่า “FL” สูตรจะค้นหาในตาราง FL_การขาย, ถ้า “CA” – ในตาราง CA_ฝ่ายขาย เป็นต้น

ผลลัพธ์ของฟังก์ชัน VPR и ทางอ้อม จะเป็นดังต่อไปนี้:

หากข้อมูลอยู่ในหนังสือ Excel หลายเล่ม คุณจะต้องเพิ่มชื่อหนังสือก่อนช่วงที่ตั้งชื่อ เช่น

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

ถ้าฟังก์ชัน ทางอ้อม หมายถึงสมุดงานอื่น สมุดงานนั้นต้องเปิดอยู่ หากปิดอยู่ ฟังก์ชันจะรายงานข้อผิดพลาด #REF! (#เอสซิล!).