გაფართოებული 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პროდუქტის შესაბამისი Apples, რადგან ეს არის პირველი მნიშვნელობა, რომელიც ემთხვევა.

არსებობს მარტივი გამოსავალი - შექმენით დამატებითი სვეტი, რომელშიც გაერთიანდება ყველა სასურველი კრიტერიუმი. ჩვენს მაგალითში ეს არის სვეტები Კლიენტის სახელი (დამკვეთი) и პროდუქტის სახელი (პროდუქტი). არ დაგავიწყდეთ, რომ გაერთიანებული სვეტი ყოველთვის უნდა იყოს ყველაზე მარცხენა სვეტი საძიებო დიაპაზონში, რადგან ეს არის მარცხენა სვეტი, რომელიც ფუნქციონირებს 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 (ძიების_მნიშვნელობა) და 4 - არგუმენტი col_index_num (სვეტის_ნომერი), ანუ ამოსაღები მონაცემების შემცველი სვეტის ნომერი.

მაგალითი 2: VLOOKUP ორი კრიტერიუმით ცხრილის ნახვა სხვა ფურცელზე

თუ გჭირდებათ ძირითადი ცხრილის განახლება (მთავარი ცხრილი) მონაცემების დამატებით მეორე ცხრილიდან (Lookup table), რომელიც მდებარეობს სხვა ფურცელზე ან 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 სიმბოლოზე მეტი სიგრძის მნიშვნელობის მოძიება. გაითვალისწინეთ ეს და დარწმუნდით, რომ სასურველი მნიშვნელობის სიგრძე არ აღემატება ამ ზღვარს.

ვეთანხმები, რომ დამხმარე სვეტის დამატება არ არის ყველაზე ელეგანტური და არა ყოველთვის მისაღები გამოსავალი. თქვენ შეგიძლიათ იგივე გააკეთოთ დამხმარე სვეტის გარეშე, მაგრამ ამას დასჭირდება ბევრად უფრო რთული ფორმულა ფუნქციების კომბინაციით ინდექსი (INDEX) და მატჩი (უფრო გამოვლენილი).

ჩვენ ვიღებთ მე-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 მომხმარებლის მიერ შეკვეთილი ნივთი დენ ბრაუნი:

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

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

• მოვძებნით მე -3 მომხმარებლის მიერ შეკვეთილი ნივთი დენ ბრაუნი:

  =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;ИСТИНА);"")

ამ ფორმულაში:

• $2 F$ – უჯრედი, რომელიც შეიცავს მყიდველის სახელს (ის უცვლელია, გთხოვთ გაითვალისწინოთ – ბმული აბსოლუტურია);
• $ B $ - სვეტი მომხმარებლის სახელი;
• ცხრილი 4 – თქვენი მაგიდა (ეს ადგილი ასევე შეიძლება იყოს ჩვეულებრივი დიაპაზონი);
• C16 დოლარი – თქვენი ცხრილის ან დიაპაზონის ბოლო უჯრედი.

ეს ფორმულა პოულობს მხოლოდ მეორე შესატყვის მნიშვნელობას. თუ დარჩენილი გამეორებების ამოღება გჭირდებათ, გამოიყენეთ წინა ხსნარი.

თუ თქვენ გჭირდებათ ყველა მატჩის სია - ფუნქცია VPR ეს არ არის დამხმარე, რადგან ის აბრუნებს მხოლოდ ერთ მნიშვნელობას - პერიოდს. მაგრამ Excel-ს აქვს ფუნქცია ინდექსი (INDEX), რომელიც ადვილად უმკლავდება ამ ამოცანას. როგორ გამოიყურება ასეთი ფორმულა, შეიტყობთ შემდეგ მაგალითში.

მიიღეთ სასურველი მნიშვნელობის ყველა გამეორება

როგორც ზემოთ აღინიშნა VPR არ შეუძლია ყველა დუბლიკატი მნიშვნელობის ამოღება სკანირებული დიაპაზონიდან. ამისათვის საჭიროა ოდნავ უფრო რთული ფორმულა, რომელიც შედგება რამდენიმე Excel ფუნქციისგან, მაგალითად ინდექსი (INDEX), მცირე (პატარა) და ROW (ხაზი)

მაგალითად, ქვემოთ მოყვანილი ფორმულა პოულობს მნიშვნელობის ყველა გამეორებას 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:

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 აბრუნებს შესაბამისი ხაზის ნომერს (მნიშვნელობა -1 საშუალებას გაძლევთ არ შეიტანოთ სათაურის ხაზი). თუ შესატყვისები არ არის, ფუნქცია IF (IF) აბრუნებს ცარიელ სტრიქონს.

ფუნქციის შედეგი IF (IF) იქნება ასეთი ჰორიზონტალური მასივი: {1,"",3,"",5,"","","","","","",12,"","",""}

ნაწილი 2:

ROW()-3

СТРОКА()-3

აქ არის ფუნქცია ROW (LINE) მოქმედებს როგორც დამატებითი მრიცხველი. ვინაიდან ფორმულა დაკოპირებულია უჯრედებში F4:F9, ჩვენ ვაკლებთ რიცხვს 3 ფუნქციის შედეგიდან მნიშვნელობის მისაღებად 1 საკანში F4 (სტრიქონი 4, გამოაკელი 3) მისაღებად 2 საკანში F5 (სტრიქონი 5, გამოვაკლოთ 3) და ა.შ.

ნაწილი 3:

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

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

ფუნქცია მცირე (SMALL) ბრუნდება არა უმცირესი მნიშვნელობა მონაცემთა მასივში. ჩვენს შემთხვევაში, რომელი პოზიცია (ყველაზე პატარადან) უნდა დაბრუნდეს, განისაზღვრება ფუნქციით ROW (LINE) (იხ. ნაწილი 2). ასე რომ, უჯრედისთვის F4 ფუნქცია SMALL ({მასივი},1) ანაზღაურება მე -1 (ყველაზე პატარა) მასივის ელემენტი, ე.ი 1. უჯრედისთვის F5 ანაზღაურება მე -2 მასივის ყველაზე პატარა ელემენტი, ანუ 3და ა.შ.

ნაწილი 4:

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 ფუნქცია INDEX ($C$2:$C$16) დაბრუნდება Applesიყიდება F5 ფუნქცია INDEX ($C$2:$C$16) დაბრუნდება ტკბილეული და ასე შემდეგ.

ნაწილი 5:

IFERROR()

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

ბოლოს ფორმულა ფუნქციის შიგნით ჩავსვით Iferror (IFERROR), რადგან ნაკლებად სავარაუდოა, რომ კმაყოფილი დარჩებით შეცდომის შესახებ #ატ (# N/A) თუ უჯრედების რაოდენობა, რომლებშიც ფორმულა დაკოპირებულია, ნაკლებია, ვიდრე ნახულ დიაპაზონში დუბლიკატი მნიშვნელობების რაოდენობა.

XNUMXD ძიება ცნობილი მწკრივისა და სვეტის მიხედვით

Excel-ში XNUMXD ძიების განხორციელება გულისხმობს მნიშვნელობის ძიებას ცნობილი მწკრივისა და სვეტის ნომრით. სხვა სიტყვებით რომ ვთქვათ, თქვენ ამოიღებთ უჯრედის მნიშვნელობას კონკრეტული მწკრივისა და სვეტის კვეთაზე.

მოდით მივმართოთ ჩვენს ცხრილს და დავწეროთ ფორმულა ფუნქციით 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)

ადამიანურ ენაზე თარგმნილი ეს ფორმულა ნიშნავს:

• ჩვენ ვეძებთ პერსონაჟებს "მარ" - არგუმენტი lookup_value (ძიების_მნიშვნელობა);
• ეძებს უჯრედებში A1-დან I1-მდე - არგუმენტი ძიება_მასივი (ძიების_მასივი);
• ზუსტი შესატყვისის დაბრუნება - არგუმენტი მატჩის_ტიპი (შემთხვევა_ტიპი).

გამოყენება 0 მესამე არგუმენტში თქვენ ამბობთ ფუნქციებს უფრო გამოვლენილი მოძებნეთ პირველი მნიშვნელობა, რომელიც ზუსტად ემთხვევა იმ მნიშვნელობას, რომელსაც ეძებთ. ეს ღირებულების ტოლფასია FALSE (FALSE) მეოთხე არგუმენტისთვის VPR.

ასე შეგიძლიათ შექმნათ ორმხრივი ძიების ფორმულა Excel-ში, რომელიც ასევე ცნობილია როგორც ორგანზომილებიანი ძიება ან ორმხრივი ძიება.

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. ნებისმიერ ცარიელ უჯრედში ჩაწერეთ = რიგის_სახელი სვეტის_სახელი, მაგალითად ასე:

   =ლიმონები მარ

   … ან პირიქით:

   =მარ ლიმონები

   გახსოვდეთ, რომ მწკრივების და სვეტების სახელები უნდა იყოს გამოყოფილი ინტერვალით, რომელიც ამ შემთხვევაში მუშაობს კვეთის ოპერატორის მსგავსად.

სახელის შეყვანისას, Microsoft Excel აჩვენებს ინსტრუმენტს შესაბამისი სახელების სიით, ისევე როგორც ფორმულის შეყვანისას.

4. პრეს შეიყვანეთ და შეამოწმეთ შედეგი

ზოგადად, რომელი მეთოდიც არ უნდა აირჩიოთ, ორგანზომილებიანი ძიების შედეგი იგივე იქნება:

რამდენიმე VLOOKUP-ის გამოყენება ერთ ფორმულაში

ხდება ისე, რომ მთავარ ცხრილსა და საძიებო ცხრილს არ აქვთ ერთი საერთო სვეტი და ეს ხელს გიშლით ჩვეულებრივი ფუნქციის გამოყენებაში. VPR. თუმცა, არის კიდევ ერთი ცხრილი, რომელიც არ შეიცავს ჩვენთვის საინტერესო ინფორმაციას, მაგრამ აქვს საერთო სვეტი მთავარ ცხრილთან და საძიებო ცხრილთან.

მოდით შევხედოთ შემდეგ მაგალითს. ჩვენ გვაქვს მთავარი ცხრილი სვეტით SKU (ახალი), სადაც გსურთ დაამატოთ სვეტი სხვა ცხრილიდან შესაბამისი ფასებით. გარდა ამისა, ჩვენ გვაქვს 2 საძიებო ცხრილი. პირველი (საძიებო ცხრილი 1) შეიცავს განახლებულ ნომრებს SKU (ახალი) და პროდუქტის სახელები, და მეორე (საძიებო ცხრილი 2) – პროდუქტის სახელები და ძველი ნომრები SKU (ძველი).

მეორე საძიებო ცხრილიდან მთავარ ცხრილში ფასების დასამატებლად, თქვენ უნდა შეასრულოთ მოქმედება, რომელიც ცნობილია როგორც ორმაგი VPR ან დაბუდებული VPR.

1. დაწერეთ ფუნქცია VPR, რომელიც პოულობს პროდუქტის სახელს ცხრილში საძიებო ცხრილი 1გამოყენების SKU, როგორც სასურველი მნიშვნელობა:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   აქ New_SKU - დასახელებული დიაპაზონი $A:$B მაგიდაზე საძიებო ცხრილი 1, 2 – ეს არის სვეტი B, რომელიც შეიცავს საქონლის სახელებს (იხ. სურათი ზემოთ)

2. დაწერეთ ფორმულა ცხრილიდან ფასების ჩასასმელად საძიებო ცხრილი 2 პროდუქტის ცნობილი სახელების საფუძველზე. ამისათვის ჩასვით ადრე შექმნილი ფორმულა, როგორც საძიებო მნიშვნელობა ახალი ფუნქციისთვის VPR:

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

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

   აქ ფასი - დასახელებული დიაპაზონი $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_Sales и CA_Sales – ცხრილების (ან დასახელებული დიაპაზონების) სახელები, რომლებიც შეიცავს გაყიდვების შესაბამის ანგარიშებს. თქვენ, რა თქმა უნდა, შეგიძლიათ გამოიყენოთ ფურცლების ჩვეულებრივი სახელები და უჯრედების დიაპაზონის მითითებები, მაგალითად 'FL Sheet'!$A$3:$B$10, მაგრამ დასახელებული დიაპაზონები ბევრად უფრო მოსახერხებელია.

თუმცა, როდესაც ბევრი ასეთი ცხრილია, ფუნქცია IF არ არის საუკეთესო გამოსავალი. ამის ნაცვლად, შეგიძლიათ გამოიყენოთ ფუნქცია არაპირდაპირი (INDIRECT) სასურველი საძიებო დიაპაზონის დასაბრუნებლად.

როგორც მოგეხსენებათ, ფუნქცია არაპირდაპირი გამოიყენება ტექსტის სტრიქონით მოცემული ბმულის დასაბრუნებლად, რაც ახლა ჩვენ გვჭირდება. ასე რომ, თამამად შეცვალეთ ზემოთ მოცემულ ფორმულაში გამონათქვამი ფუნქციით IF ფუნქციასთან დაკავშირება არაპირდაპირი. აქ არის კომბინაცია VPR и არაპირდაპირი მშვენივრად მუშაობს:

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

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

სად:

• $ D 2 $ – ეს არის უჯრედი პროდუქტის სახელწოდებით, ის უცვლელია აბსოლუტური კავშირის გამო.
• $ D3 არის უჯრედი, რომელიც შეიცავს რეგიონის სახელის პირველ ნაწილს. ჩვენს მაგალითში ეს FL.
• _Გაყიდვების – ყველა დასახელებული დიაპაზონის ან ცხრილის სახელის საერთო ნაწილი. D3 უჯრედის მნიშვნელობასთან შერწყმისას ის ქმნის საჭირო დიაპაზონის სრულად კვალიფიციურ სახელს. ქვემოთ მოცემულია რამდენიმე დეტალი მათთვის, ვინც ახალია ამ ფუნქციისთვის არაპირდაპირი.

როგორ მუშაობს INDIRECT და VLOOKUP

პირველ რიგში, შეგახსენებთ ფუნქციის სინტაქსს არაპირდაპირი (არაპირდაპირი):

INDIRECT(ref_text,[a1])

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

პირველი არგუმენტი შეიძლება იყოს უჯრედის მითითება (A1 ან R1C1 სტილი), დიაპაზონის სახელი ან ტექსტის სტრიქონი. მეორე არგუმენტი განსაზღვრავს, თუ რა სტილის ბმულს შეიცავს პირველი არგუმენტი:

• A1თუ არგუმენტი არის ჭეშმარიტი კოდი (TRUE) ან არ არის მითითებული;
• R1C1იმ შემთხვევაში, თუ FAS E (ცრუ).

ჩვენს შემთხვევაში, ბმულს აქვს სტილი A1, ასე რომ თქვენ შეგიძლიათ გამოტოვოთ მეორე არგუმენტი და ყურადღება გაამახვილოთ პირველზე.

მოდით დავუბრუნდეთ ჩვენს გაყიდვების ანგარიშებს. თუ გახსოვთ, მაშინ თითოეული ანგარიში ცალკე ცხრილია, რომელიც ცალკე ფურცელზეა განთავსებული. იმისათვის, რომ ფორმულმა სწორად იმუშაოს, თქვენ უნდა დაასახელოთ თქვენი ცხრილები (ან დიაპაზონები) და ყველა სახელს უნდა ჰქონდეს საერთო ნაწილი. მაგალითად, ასე: CA_Sales, FL_Sales, TX_Sales და ასე შემდეგ. როგორც ხედავთ, "_Sales" არის ყველა სახელში.

ფუნქცია არაპირდაპირი აკავშირებს D სვეტის მნიშვნელობას და ტექსტურ სტრიქონს „_Sales“, რითაც ამბობს VPR რომელ ცხრილში უნდა მოძებნოთ. თუ უჯრედი D3 შეიცავს მნიშვნელობას „FL“, ფორმულა მოძებნის ცხრილს FL_Sales, თუ „CA“ – ცხრილში CA_Sales და ასე შემდეგ.

ფუნქციების შედეგი VPR и არაპირდაპირი იქნება შემდეგი:

თუ მონაცემები განლაგებულია Excel-ის სხვადასხვა წიგნში, მაშინ თქვენ უნდა დაამატოთ წიგნის სახელი დასახელებული დიაპაზონის წინ, მაგალითად:

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

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

თუ ფუნქცია არაპირდაპირი ეხება სხვა სამუშაო წიგნს, ეს სამუშაო წიგნი ღია უნდა იყოს. თუ ის დახურულია, ფუნქცია შეატყობინებს შეცდომას. # REF! (#SSYL!).