Excel-ის ჩანაცვლების ფორმულები: vlookup, index და searchpos. Excel: დანამატი საპირისპირო VLOOKUP ფუნქციისთვის, ან როგორც VLOOKUP "გადადით მარცხნივ"

29.08.2019

აუცილებელი წინასიტყვაობა

თუ აქამდე არ გიმუშავიათ ფუნქციასთან VLOOKUP, მაშინ ბევრი დაკარგე, გირჩევთ ჯერ წაიკითხოთ.

პრობლემა

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

პრაქტიკაში, ხშირად წარმოიქმნება სიტუაციები, როდესაც თქვენ უნდა მოძებნოთ მონაცემები სვეტში, რომელიც მდებარეობს შედეგების სვეტის მარჯვნივ და არა მარცხნივ, მაგალითად:

მარტივია ღირებულების პოვნა შეკვეთის კოდით - ჩვეულებრივი VLOOKUP დაგეხმარებათ აქ ერთხელ ან ორჯერ. მაგრამ როგორ მოვძებნოთ პროდუქტის სახელი კოდით? ტრენინგებზე ყველაზე ხშირად მესმის ეს კითხვა ფორმულირებაში "როგორ გავაკეთოთ მარცხენა VLOOKUP"?

მოდით შევხედოთ რამდენიმე გზას.

მეთოდი 1. ფრონტალური შეტევა

თუ მიჰყვებით Occam-ის პრინციპს და არ გაართულებთ მას ზედმეტად, შეგიძლიათ უბრალოდ დააკოპიროთ სასურველი სვეტი მარჯვნივ (ან დააკავშიროთ) და გამოიყენოთ ჩვეულებრივი VLOOKUP:

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

მეთოდი 2. სვეტების ვირტუალურად გადაწყობა SELECT ფუნქციის გამოყენებით

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

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

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

SELECT(2; A1:A10; D1:D10; B1:B10)

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

არჩევანი ( {1;2} ; A1:A10 ; D1:D10 ; B1:B10)

შემდეგ გამოსავალზე მივიღებთ პირველ ორ დიაპაზონს (A1:A10 და D1:D10), რომლებიც გაერთიანებულია ერთ მთლიანობაში.

ახლა კი ეს ყველაფერი შეიძლება ჩავდოთ ჩვენს შიგნით VLOOKUP"მარცხენა ძიების" განსახორციელებლად:

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

ამ მეთოდის უარყოფითი მხარეა სიჩქარე (დაახლოებით 5-7-ჯერ უფრო ნელი, ვიდრე ჩვეულებრივი VLOOKUP) და კოლეგებისთვის უცნობია (და შესაძლოა ეს პლიუსიც კი იყოს!)

მეთოდი 3. INDEX და SEARCH ფუნქციების დაკავშირება

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

ფუნქცია ძიებაეძებს მოცემულ მნიშვნელობას (C2, ანუ ჩვენთვის საჭირო რიგის კოდს) ერთგანზომილებიან დიაპაზონში (კოდების სვეტი ცხრილში C10:C25) და შედეგად იძლევა იმ უჯრედის სერიულ ნომერს, სადაც მან იპოვა ის, რაც ჩვენ გვაქვს. ეძებდნენ - ჩვენს შემთხვევაში ეს იქნება ნომერი 4, ე.ი. შეკვეთის კოდი, რომელიც ჩვენ გვჭირდება, მეოთხეა ცხრილში.

და შემდეგ ფუნქცია შედის თამაშში ინდექსი, რომელსაც შეუძლია მონაცემების ამოღება ვერტიკალური სვეტის მასივიდან (პროდუქტის სახელები B10:B25-ში) სერიული ნომრის მიხედვით (რომელიც მანამდე იპოვა ძიება). ამრიგად, ინდექსიმოგვცემს მეოთხე უჯრედის შიგთავსს სვეტიდან პროდუქტი, რაც საჭირო იყო.

წინა მეთოდთან შედარებით, ეს ვარიანტი გადაითვლება ბევრად უფრო სწრაფად (თითქმის ისეთივე სწრაფად, როგორც ჩვეულებრივი VLOOKUP), რაც მნიშვნელოვანია დიდი მაგიდებისთვის.

მსგავსი მაგალითი (ვიდეო) ადრე გავაანალიზე. და ფუნქციის შესახებ ინდექსიშეგიძლია ისაუბრო :)

მეთოდი 4. SUMIF(MN) ფუნქცია

თუ ცხრილიდან ზუსტად რიცხვის ამოღება გჭირდებათ (მაგალითად, მოცულობა ლიტრებში), მაშინ ზოგჯერ უფრო ადვილია გამოიყენოთ შერჩევითი შეჯამების ფუნქცია „მარცხენა VLOOKUP“-ის განსახორციელებლად. სუმიფიან მისი უფროსი და - ფუნქცია SUMIFS:

ამ მიდგომის უარყოფითი მხარეები აშკარაა - ის მუშაობს მხოლოდ ნომრებზე და იმ პირობით, რომ სვეტში არ არის დუბლიკატი მნიშვნელობები. თუ არის დუბლიკატები (რამდენიმე შეკვეთა იგივე კოდით), მაშინ ეს ფუნქცია დაამატებს ყველა ტომს და არ დააბრუნებს პირველს, როგორც ამას VLOOKUP გააკეთებდა. ამ მეთოდის სიჩქარე ასევე არ არის ძალიან კარგი - დაახლოებით 3-4-ჯერ ნელი, ვიდრე ჩვეულებრივი VLOOKUP.

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

მეთოდი 5: მზა მაკრო ფუნქცია PLEX-ისგან

თუ მაკროსების გამოყენების არ გეშინიათ, მაშინ შეგიძლიათ გამოიყენოთ მზა მომხმარებლის მიერ განსაზღვრული ფუნქცია Visual Basic-ში, რომელიც შედის Microsoft Excel-ში. ჩვეულებრივ VLOOKUP-თან შედარებით, მას შეუძლია:

მოძებნეთ ერთდროულად რამდენიმე სვეტში (3-მდე)
შედეგის მიღება ნებისმიერი სვეტიდან (მარცხნივ ან მარჯვნივ - არ აქვს მნიშვნელობა)
გამოაქვს არა მხოლოდ პირველი შეხვედრილი მნიშვნელობა, არამედ საჭირო თანმიმდევრობით
შეგიძლიათ მიუთითოთ რა უნდა იყოს ნაჩვენები, თუ არაფერი არ არის ნაპოვნი #N/A შეცდომის ნაცვლად

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

მაგრამ როგორც ერთ-ერთი ვარიანტი იმუშავებს :)

დაკავშირებული ბმულები

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

მარშრუტი	ეიდი	პრეფიქსი	გადახდა
დასავლეთი2	251458	BR7652I	16.01.2017
კაშირა1	252412	BR7652I	17.01.2017
S5-1	253016	BR7272I	18.01.2017
S3-1	254392	BR7652I	19.01.2017
კაშირა1	255031	BR7249I	20.01.2017
კაშირა1	257321	BR7759I	21.01.2017
SV1-1	257569	BR7761I	22.01.2017
პუშკინო 1	259373	BR7647I	23.01.2017
კოლომნა1	259591	BR7315I	24.01.2017
კაშირა1	260300	BR7544I	25.01.2017

სწორედ ამას ვეძებ

პრეფიქსი	ეიდი
BR7652I	INDEX(A2:D11,MATCH(D20,C2:C11,0),MATCH(E19,A1:D1,0))

მაგრამ მჭირდება შედეგი მაქსიმალური გადახდის თარიღით და არა პირველი პირობითი ფორმატირებით (5)
სიები და დიაპაზონები (5)
მაკროები (VBA პროცედურები) (63)
სხვადასხვა (39)
Excel-ის შეცდომები და ხარვეზები (3)

როგორ მოვძებნოთ მნიშვნელობა სხვა ცხრილში ან VLOOKUP სიძლიერეში

სინამდვილეში, ამ სტატიაში მინდა ვისაუბრო არა მხოლოდ შესაძლებლობებზე VLOOKUP ფუნქციები, მაგრამ ასევე მინდა შევეხო ძიება, როგორც ფუნქცია ძალიან დაკავშირებული VLOOKUP-თან. თითოეულ ამ ფუნქციას აქვს თავისი დადებითი და უარყოფითი მხარეები. მოკლედ, VLOOKUP ეძებს გარკვეულ მნიშვნელობას, რომელსაც ჩვენ ვაზუსტებთ ერთ სვეტში მდებარე მრავალ მნიშვნელობას შორის. შესაძლოა, ყველაზე ხშირად VLOOKUP-ის საჭიროება წარმოიქმნება მაშინ, როდესაც გჭირდებათ მონაცემების შედარება, სხვა ცხრილში მონაცემების პოვნა, მონაცემების დამატება ერთი ცხრილიდან მეორეზე, გარკვეული კრიტერიუმების საფუძველზე და ა.შ.
იმისათვის, რომ უკეთ გავიგოთ VLOOKUP-ის პრინციპი, უმჯობესია დავიწყოთ რამდენიმე პრაქტიკული მაგალითით. არსებობს ასეთი ცხრილი:
ნახ.1

ხოლო პირველი ცხრილიდან უნდა შეიცვალოს მეორე თარიღით თითოეული გვარისთვის. სამი ჩანაწერისთვის ეს არ არის პრობლემა და ხელით ამის გაკეთება აშკარაა. მაგრამ რეალურ ცხოვრებაში, ეს არის ცხრილები ათასობით ჩანაწერით და მონაცემთა ხელით ჩანაცვლებით ძიებას შეიძლება ერთ საათზე მეტი დასჭირდეს. გარდა ამისა, კიდევ რამდენიმე დაფრინავს მალამო: არა მხოლოდ სახელები განლაგებულია სრულიად განსხვავებული თანმიმდევრობით ორივე ცხრილში და ცხრილებში ჩანაწერების რაოდენობა განსხვავებულია, არამედ ცხრილები ასევე განლაგებულია სხვადასხვა ფურცლებზე/წიგნებზე. ვფიქრობ, დავრწმუნდი, რომ ხელით ჩანაცვლება საერთოდ არ არის ვარიანტი. მაგრამ VLOOKUPაქ შეუცვლელი იქნება. ამ შემთხვევაში, პრაქტიკულად არაფრის გაკეთება დაგჭირდებათ - უბრალოდ ჩაწერეთ მეორე ცხრილის C სვეტის პირველ უჯრედში (სადაც უნდა ჩაანაცვლოთ თარიღები პირველი ცხრილიდან)ეს ფორმულა:
=VLOOKUP($A2 ;Sheet1!$A$2:$C$4 ;3,0)
თქვენ შეგიძლიათ დაწეროთ ფორმულა ან პირდაპირ უჯრედში, ან ფუნქციის მენეჯერის გამოყენებით, არჩევით კატეგორიაში ბმულები და მასივები VPR და ცალკე მიუთითეთ საჭირო კრიტერიუმები. ახლა ჩვენ ვაკოპირებთ ( Ctrl+C) უჯრედი ფორმულით, აირჩიეთ C სვეტის ყველა უჯრედი მონაცემების ბოლომდე და ჩადეთ ( Ctrl+ვ).

პირველი, მუშაობის ძირითადი პრინციპი: VLOOKUP ეძებს ცხრილის არგუმენტის პირველ სვეტში არგუმენტით მითითებულ მნიშვნელობას. Search_value . როდესაც სასურველი მნიშვნელობა მოიძებნება, ფუნქცია აბრუნებს მნიშვნელობას ნაპოვნი მნიშვნელობის საპირისპიროდ, მაგრამ არგუმენტით მითითებული სვეტიდან სვეტის_ნომერი . დროებით ნახვას ცოტა მოგვიანებით შევეხებით. VLOOKUP-ს შეუძლია დააბრუნოს მხოლოდ ერთი მნიშვნელობა - პირველი, რომელიც შეესაბამება კრიტერიუმს. თუ მნიშვნელობა, რომელსაც ეძებთ, ვერ მოიძებნა (ცხრილში არ არის), მაშინ ფუნქციის შედეგი იქნება #არ/ა . ამის შიში არ არის საჭირო - ის სასარგებლოც კია. თქვენ ზუსტად გეცოდინებათ რომელი ჩანაწერები აკლია და ამით შეგიძლიათ შეადაროთ ორი ცხრილი ერთმანეთს. ზოგჯერ აღმოჩნდება, რომ ხედავთ: არის მონაცემები ორივე ცხრილში, მაგრამ VLOOKUP აწარმოებს #N/A. ეს ნიშნავს, რომ თქვენს ცხრილებში მონაცემები არ არის იდენტური. ზოგიერთ მათგანს აქვს დამატებითი შეუმჩნეველი სივრცეები (ჩვეულებრივ, მნიშვნელობის წინ ან მის შემდეგ), ან კირილიცას სიმბოლოები შერეულია ლათინური სიმბოლოებით. ასევე #არ/ა იქნება თუ კრიტერიუმები რიცხვებია და სასურველ ცხრილში ისინი ტექსტის სახით ჩაიწერება (როგორც წესი, მწვანე სამკუთხედი ჩნდება ასეთი უჯრედის ზედა მარცხენა კუთხეში), ხოლო საბოლოო შედეგში - როგორც რიცხვები. ან პირიქით.

VLOOKUP არგუმენტების აღწერა
$A2 - არგუმენტი Search_value(მოდით დავარქვათ კრიტერიუმიმოკლედ). ეს არის ის, რასაც ჩვენ ვეძებთ. იმათ. მეორე ცხრილის პირველი რეკორდისთვის ეს იქნება Petrov S.A. აქ შეგიძლიათ პირდაპირ მიუთითოთ კრიტერიუმის ტექსტი (ამ შემთხვევაში ის უნდა იყოს ბრჭყალებში - =VLOOKUP("Petrov S.A" ;Sheet1!$A$2:$C$4;3;0) ან ბმული უჯრედზე ამ ტექსტს (როგორც ფუნქციის მაგალითში). არის პატარა ნიუანსი: ასევე შეგიძლიათ გამოიყენოთ wildcard სიმბოლოები: "*" და "?". ეს ძალიან მოსახერხებელია, თუ თქვენ გჭირდებათ მნიშვნელობების პოვნა მხოლოდ სტრიქონის ნაწილისთვის. მაგალითად, თქვენ შეიძლება არ შეიყვანოთ "Petrov S.A" სრულად, მაგრამ შეიყვანოთ მხოლოდ გვარი და ვარსკვლავი - "Petrov*". შემდეგ გამოჩნდება ნებისმიერი ჩანაწერი, რომელიც იწყება "Petrov"-ით. თუ თქვენ გჭირდებათ ჩანაწერის პოვნა, რომელშიც გვარი "პეტროვი" გამოჩნდება ხაზის ნებისმიერ წერტილში, მაშინ შეგიძლიათ მიუთითოთ ის ასე: "*Petrov*". თუ გსურთ იპოვოთ გვარი პეტროვი და არ აქვს მნიშვნელობა რა ინიციალები ექნება სახელსა და პატრონიმიკას (თუ სრული სახელი იწერება ფორმით Ivanov I.I.), მაშინ ეს სახე სწორია: „ივანოვი?.?. ” . ხშირად საჭიროა თითოეული სტრიქონისთვის მისი მნიშვნელობის მითითება (სვეტში A გვარები და თქვენ უნდა იპოვოთ ისინი ყველა). ამ შემთხვევაში, A სვეტის უჯრედებზე მითითებები ყოველთვის არის მითითებული, მაგალითად, უჯრედში A1 წერია: Ivanov. ასევე ცნობილია, რომ ივანოვი სხვა ცხრილშია, მაგრამ გვარის შემდეგ შეიძლება ჩაიწეროს როგორც სახელი, ასევე შუა სახელი (ან სხვა). მაგრამ ჩვენ მხოლოდ უნდა ვიპოვოთ სტრიქონი, რომელიც იწყება გვარით. შემდეგ თქვენ უნდა დაწეროთ იგი შემდეგნაირად: A1 &"*" . ეს ჩანაწერი იქნება „ივანოვის*“ ექვივალენტი. A1 იწერება Ivanov, ampersand(&) გამოიყენება ორი ტექსტური მნიშვნელობის ერთ სტრიქონში გაერთიანებისთვის. ვარსკვლავი არის ბრჭყალებში (როგორც ტექსტი უნდა იყოს ფორმულის შიგნით). ამრიგად, ჩვენ ვიღებთ:
A1&"*" =>
"ივანოვი"&"*" =>
"ივანოვი *"
ძალიან მოსახერხებელია, თუ საძიებელი ბევრი მნიშვნელობაა.
თუ თქვენ უნდა დაადგინოთ არის თუ არა სიტყვა სადმე ხაზში, მაშინ ორივე მხარეს დადეთ ვარსკვლავი: "*"& A1 &"*"

Sheet1!$A$2:$C$4 - არგუმენტი მაგიდა. განსაზღვრავს უჯრედების დიაპაზონს. მხოლოდ დიაპაზონი უნდა შეიცავდეს მონაცემებს მონაცემთა პირველი უჯრედიდან ბოლომდე. ეს არ უნდა იყოს მაგალითში ნაჩვენები დიაპაზონი. თუ არის 100 მწკრივი, მაშინ Sheet1!$A$2:$C$100. მნიშვნელოვანია გვახსოვდეს სამი რამ: პირველი, ეს მაგიდა ყოველთვის უნდა დაიწყოს იმ სვეტით, რომელშიც ჩვენ ვეძებთ კრიტერიუმი . და სხვა არაფერი. წინააღმდეგ შემთხვევაში, ვერაფერი მოიძებნება ან შედეგი არ იქნება ის, რასაც ელოდებით. მეორე: არგუმენტი მაგიდა უნდა "გამოსწორდეს" . რას ნიშნავს. ხედავთ დოლარის ნიშნებს? ეს არის კონსოლიდაცია (უფრო ზუსტად, ამას ჰქვია აბსოლუტური დიაპაზონის მითითება). როგორ კეთდება. მონიშნეთ ბმულის ტექსტი (მხოლოდ ერთი დიაპაზონი - ერთი კრიტერიუმი)და დააჭირეთ F4 სანამ დაინახავთ, რომ დოლარი გამოჩნდება როგორც სვეტის სახელზე, ასევე მწკრივის ნომრის წინ. თუ ეს არ გაკეთებულა, მაშინ ფორმულის კოპირებისას, ცხრილის არგუმენტი "გადაადგილდება" და შედეგი კვლავ არასწორი იქნება. და ბოლოს, ცხრილი უნდა შეიცავდეს სვეტებს პირველიდან (რომელშიც ვეძებთ) ბოლომდე (საიდანაც უნდა დავაბრუნოთ მნიშვნელობები). მაგალითში Sheet1!$A$2:$C$4- ეს ნიშნავს, რომ შეუძლებელი იქნება მნიშვნელობის დაბრუნება სვეტიდან D(4), რადგან ცხრილს აქვს მხოლოდ სამი სვეტი.

3 - სვეტის_ნომერი. აქ ჩვენ უბრალოდ მიუთითებთ არგუმენტში სვეტის ნომერს მაგიდა, მნიშვნელობები, საიდანაც უნდა ჩავანაცვლოთ შედეგად. მაგალითში ეს არის მიღების თარიღი - ე.ი. სვეტის ნომერი 3. თუ ჩვენ გვჭირდებოდა განყოფილება, მაშინ მივუთითებდით 2-ს, და თუ უბრალოდ დაგვჭირდებოდა შედარება, არის თუ არა სახელები ერთი ცხრილიდან მეორეში, მაშინ შეგვიძლია მივუთითოთ 1. მნიშვნელოვანია: არგუმენტი სვეტის_ნომერი არ უნდა აღემატებოდეს არგუმენტის სვეტების რაოდენობას მაგიდა . წინააღმდეგ შემთხვევაში, ფორმულა გამოიწვევს შეცდომას #ლინკი!. მაგალითად, თუ მითითებულია $B$2:$C$4 დიაპაზონი და უნდა დააბრუნოთ მონაცემები C სვეტიდან, მაშინ სწორია 2-ის მითითება. არგუმენტი მაგიდა($B$2:$C$4) შეიცავს მხოლოდ ორ სვეტს - B და C. თუ ცდილობთ მიუთითოთ მე-3 სვეტის ნომერი (როგორც ჩანს ფურცელზე), მიიღებთ შეცდომას. #ლინკი!, იმიტომ უბრალოდ არ არის მესამე სვეტი მითითებულ დიაპაზონში.

პრაქტიკული რჩევა: თუ ცხრილის არგუმენტს აქვს ძალიან ბევრი სვეტი და თქვენ უნდა დააბრუნოთ შედეგი ბოლო სვეტიდან, მაშინ მათი რიცხვის გამოთვლა სულაც არ არის საჭირო. შეგიძლიათ მიუთითოთ ეს ასე: =VLOOKUP($A2 ;Sheet1! $A$2:$C$4 ;COLUMN NUM(Sheet1! $A$2:$C$4);0) . სხვათა შორის, ამ შემთხვევაში Sheet1! ასევე შეიძლება წაიშალოს როგორც არასაჭირო: =VLOOKUP($A2 ;Sheet1! $A$2:$C$4 ;COLUMN NUM($A$2:$C$4);0) .

0 - Time-lapse_view- ძალიან საინტერესო არგუმენტია. შეიძლება იყოს TRUE ან FALSE. მაშინვე ჩნდება კითხვა: რატომ არის 0 ჩემს ფორმულაში? ეს ძალიან მარტივია - ფორმულებში Excel-ს შეუძლია 0 განიხილოს როგორც FALSE და 1 როგორც TRUE. თუ თქვენ მიუთითებთ ამ პარამეტრს VLOOKUP-ში 0-ის ან FALSE-ის ტოლი, მაშინ მოძებნილი იქნება ზუსტი შესატყვისი მითითებულ კრიტერიუმთან. ამას არანაირი კავშირი არ აქვს ველურ ბარათებთან ("*" და "?"). თუ იყენებთ 1-ს ან TRUE-ს (ან საერთოდ არ მიუთითებთ ბოლო არგუმენტს, რადგან ნაგულისხმევად არის TRUE), მაშინ... ეს ძალიან გრძელი ამბავია. მოკლედ - VLOOKUP მოძებნის ყველაზე მსგავს მნიშვნელობას, რომელიც ემთხვევა კრიტერიუმი . ზოგჯერ ძალიან სასარგებლოა. თუმცა, თუ თქვენ იყენებთ ამ პარამეტრს, მაშინ აუცილებელია, რომ სია არგუმენტში Table დალაგებული იყოს ზრდადი თანმიმდევრობით. გთხოვთ გაითვალისწინოთ, რომ დახარისხება საჭიროა მხოლოდ იმ შემთხვევაში, თუ არგუმენტი Interval_lookup არის TRUE ან 1. თუ ის არის 0 ან FALSE, დახარისხება საჭირო არ არის.

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

როგორ ავიცილოთ თავიდან #N/A (#N/A) შეცდომა VLOOKUP-ში?
კიდევ ერთი გავრცელებული პრობლემა ის არის, რომ ბევრს არ სურს დაინახოს #N/A შედეგი, თუ შესატყვისი ვერ მოიძებნა. ამის გადაადგილება ადვილია:
=IF(END(VLOOKUP($A2,Sheet1! $A$2:$C$4,3,0));"";VLOOKUP($A2,Sheet1! $A$2:$C$4,3,0)))
ახლა თუ VLOOKUP ვერ იპოვის შესატყვისს, უჯრედი ცარიელი იქნება.
და Excel 2007 და უფრო მაღალი ვერსიების მომხმარებლებს შეუძლიათ გამოიყენონ IFERROR:
=IFERROR(VLOOKUP($A2,Sheet1! $A$2:$C$4,3,0);"")

დაპირებული მატჩი

ეს ფუნქცია ეძებს პარამეტრით მითითებულ მნიშვნელობას Search_valueკამათში ხედი_მასივი. და ფუნქციის შედეგი არის ნაპოვნი მნიშვნელობის პოზიციის ნომერი ხედი_მასივი. ეს არის პოზიციის ნომერი და არა თავად მნიშვნელობა. პრინციპში, მე არ აღვწერ იგივე დეტალებს, რადგან ძირითადი პუნქტები ზუსტად იგივეა. თუ გვინდოდა მისი გამოყენება ზემოთ მოცემულ ცხრილში, ეს ასე იქნებოდა:
=MATCH($A2,ფურცელი1! $A$2:$A$4,0)
$A2 - Search_value. აქ ყველაფერი ზუსტად იგივეა, რაც VLOOKUP-ში. ასევე დაშვებულია ველური სიმბოლოები და ზუსტად იგივე დიზაინით.

ფურცელი 1! $A$2:$A$4 - სანახავი მასივი. მთავარი განსხვავება VLOOKUP-ისგან არის ის, რომ თქვენ შეგიძლიათ მიუთითოთ მასივი მხოლოდ ერთი სვეტით. ეს უნდა იყოს სვეტი, რომელშიც ვაპირებთ ძიებას Search_value . თუ ცდილობთ მიუთითოთ ერთზე მეტი სვეტი, ფუნქცია დააბრუნებს შეცდომას.

მატჩის_ტიპი (0) - იგივე, რაც VPR-ში Time-lapse_view . იგივე თვისებებით. იგი განსხვავდება მხოლოდ სასურველიდან ყველაზე პატარას ძებნის უნარით. მაგრამ ამ სტატიაში ამაზე არ შევჩერდები.

ჩვენ დავალაგეთ საფუძვლები. მაგრამ ჩვენ უნდა დავაბრუნოთ არა პოზიციის ნომერი, არამედ თავად მნიშვნელობა. ეს ნიშნავს, რომ SEARCH მისი სუფთა სახით არ არის შესაფერისი ჩვენთვის. ერთი მაინც, თავისთავად. მაგრამ თუ ის გამოიყენება INDEX ფუნქციასთან ერთად, ეს არის ის, რაც გვჭირდება და კიდევ უფრო მეტი.
=INDEX(Sheet1! $A$2:$C$4 ;MATCH($A2 ;Sheet1! $A$2:$A$4 ;0);2)
ეს ფორმულა დააბრუნებს იგივე შედეგს, როგორც VLOOKUP.

INDEX ფუნქციის არგუმენტები
ფურცელი 1! $A$2:$C$4 - მასივი. როგორც ეს არგუმენტი, ჩვენ ვაზუსტებთ დიაპაზონს, საიდანაც გვინდა მივიღოთ მნიშვნელობები. შეიძლება იყოს ერთი ან რამდენიმე სვეტი. თუ არსებობს მხოლოდ ერთი სვეტი, მაშინ ბოლო ფუნქციის არგუმენტი არ არის საჭირო. სხვათა შორის, ეს არგუმენტი შეიძლება საერთოდ არ ემთხვეოდეს იმას, რასაც ჩვენ ვაზუსტებთ MATCH ფუნქციის Lookup_array არგუმენტში.

შემდეგ მოდის Row_Number და Column_Number. სწორედ Row_Number-ის სახით ვცვლით MATCH, რომელიც გვიბრუნებს პოზიციის ნომერს მასივში. ეს არის ის, რაზეც ყველაფერი აგებულია. INDEX აბრუნებს მნიშვნელობას მასივიდან, რომელიც არის მასივის მითითებულ მწკრივში (RowNumber) და მითითებულ სვეტში (ColumnNumber), თუ არის ერთზე მეტი სვეტი. მნიშვნელოვანია ვიცოდეთ, რომ ამ კომბინაციაში, INDEX ფუნქციის Array არგუმენტში რიგების რაოდენობა და MATCH ფუნქციის Lookup_array არგუმენტში რიგების რაოდენობა უნდა შეესაბამებოდეს. და დაიწყე იგივე ხაზიდან. ეს ჩვეულებრივ შემთხვევებშია, თუ სხვა მიზნებს არ მისდევთ.
როგორც VLOOKUP-ის შემთხვევაში, INDEX აბრუნებს #N/A თუ სასურველი მნიშვნელობა ვერ მოიძებნა. და თქვენ შეგიძლიათ მსგავსი შეცდომების თავიდან აცილება იმავე გზით:
Excel-ის ყველა ვერსიისთვის (მათ შორის 2003 და უფრო ადრე):
=IF(END(MATCH($A2,Sheet1! $A$2:$A$4,0));"";INDEX(Sheet1! $A$2:$C$4;MATCH($A2,Sheet1! $A$2: $A$4;0);2))
2007 და უფრო მაღალი ვერსიებისთვის:
=IFERROR(INDEX(Sheet1! $A$2:$C$4,MATCH($A2,Sheet1!$A$2:$A$4,0),2);"")

255 სიმბოლოზე მეტი კრიტერიუმებით მუშაობა
INDEX-SEARCH-ს ასევე აქვს კიდევ ერთი უპირატესობა VLOOKUP-თან შედარებით. საქმე იმაშია, რომ VLOOKUP ვერ ეძებს მნიშვნელობებს რომლის ხაზის სიგრძე შეიცავს 255-ზე მეტ სიმბოლოს. ეს იშვიათად ხდება, მაგრამ ხდება. თქვენ, რა თქმა უნდა, შეგიძლიათ მოატყუოთ VLOOKUP და შეამციროთ კრიტერიუმი:
=VLOOKUP(PSTR($A2,1,255), PSTR(ფურცელი1!$A$2:$C$4,1,255),3,0)
მაგრამ ეს არის მასივის ფორმულა. გარდა ამისა, ასეთი ფორმულა ყოველთვის არ დააბრუნებს სასურველ შედეგს. თუ პირველი 255 სიმბოლო ცხრილის პირველი 255 სიმბოლოს იდენტურია, შემდეგ კი სიმბოლოები განსხვავებულია, ფორმულა ამას ვეღარ დაინახავს. და ფორმულა აბრუნებს ექსკლუზიურად ტექსტურ მნიშვნელობებს, რაც არ არის ძალიან მოსახერხებელი იმ შემთხვევებში, როდესაც რიცხვები უნდა დაბრუნდეს.

ამიტომ, უმჯობესია გამოიყენოთ ეს რთული ფორმულა:
=INDEX(Sheet1!$A$2:$C$4,SUMPRODUCT(MATCH(TRUE,Sheet1!$A$2:$A$4 =$A2,0));2)
აქ მე გამოვიყენე იგივე დიაპაზონები წაკითხვის ფორმულებში, მაგრამ ჩამოტვირთვის მაგალითში ისინი განსხვავდებიან აქ მითითებულისგან.
თავად ფორმულა აგებულია SUMPRODUCT ფუნქციის უნარზე, გარდაიქმნას მასში არსებული ზოგიერთი ფუნქციის მასიურ გამოთვლებად. ამ შემთხვევაში, MATCH ეძებს მწკრივის პოზიციას, სადაც კრიტერიუმი უდრის მწკრივის მნიშვნელობას. Wildcard სიმბოლოების გამოყენება აქ აღარ შეიძლება.

სტატიას თანდართულ მაგალითში ნახავთ ყველა აღწერილი შემთხვევის გამოყენების მაგალითებს და იმის მაგალითს, თუ რატომ არის INDEX და MATCH ზოგჯერ სასურველი VLOOKUP-ზე.

ჩამოტვირთეთ მაგალითი

(26.0 KiB, 13,776 ჩამოტვირთვა)

დაეხმარა თუ არა სტატიას? გაუზიარე ბმული მეგობრებს! ვიდეო გაკვეთილები

("ქვედა ზოლი":("ტექსტის სტილი":"სტატიკური","ტექსტის სტატიკური":"ქვედა","ტექსტოჰიდე":true,"textpositionmarginstatic":0,"textpositiondynamic":"ქვედა მარცხენა","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"მარცხნივ","ტექსტის ეფექტი" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"მარჯვნივ","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic,"text. "texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"მარჯვნივ","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,""texteffectslidedirection2":600"text textcss":"display:block:left;","textbgcs":"display:absolute-color:#333333; ","titlecss":"display:block; პოზიცია:ნათესავი; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; ფერი:#fff;","descriptioncss":"display:block; პოზიცია:ნათესავი; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; ფერი:#fff; margin-top:8px;","buttoncss":"display:block; პოზიცია:ნათესავი; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !მნიშვნელოვანი;","buttoncssresponsive" "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

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

მოძებნეთ Excel-ში მრავალი კრიტერიუმის გამოყენებით

ფუნქცია VLOOKUP Excel არის მართლაც ძლიერი ინსტრუმენტი მონაცემთა ბაზაში კონკრეტული მნიშვნელობის მოსაძებნად. თუმცა, არსებობს მნიშვნელოვანი შეზღუდვა - მისი სინტაქსი საშუალებას გაძლევთ მოძებნოთ მხოლოდ ერთი მნიშვნელობა. რა მოხდება, თუ რამდენიმე პირობის გამოყენებით გჭირდებათ ძებნა? გამოსავალს იპოვით ქვემოთ.

მაგალითი 1: ძიება 2 განსხვავებული კრიტერიუმის გამოყენებით

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

რეგულარული ფუნქცია VLOOKUPარ იმუშავებს ამ სცენარში, რადგან ის დააბრუნებს პირველ ნაპოვნი მნიშვნელობას, რომელიც ემთხვევა მოცემულ საძიებო მნიშვნელობას. მაგალითად, თუ გსურთ იცოდეთ ნივთის რაოდენობა ტკბილეულიმყიდველის მიერ შეკვეთილი ჯერემი ჰილიჩაწერეთ შემდეგი ფორმულა:

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

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

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

ასე რომ, თქვენ ამატებთ ცხრილს დამხმარე სვეტს და დააკოპირებთ მსგავს ფორმულას მის ყველა უჯრედში: =B2&C2. თუ გსურთ, რომ სტრიქონი უფრო იკითხება იყოს, შეგიძლიათ გამოყოთ მიბმული მნიშვნელობები ინტერვალით: =B2&" "&C2. ამის შემდეგ შეგიძლიათ გამოიყენოთ შემდეგი ფორმულა:

VLOOKUP ("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=VLOOKUP("ჯერემი ჰილი ტკბილეული";$A$7:$D$18,4;FALSE)

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

სად არის საკანი B1შეიცავს არგუმენტის თანმიმდევრულ მნიშვნელობას საძიებელი_მნიშვნელობა(ძებნის_მნიშვნელობა) და 4 - არგუმენტი col_index_num(სვეტის_ნომერი), ე.ი. ამოსაღები მონაცემების შემცველი სვეტის ნომერი.

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

თუ თქვენ გჭირდებათ Main ცხრილის განახლება მეორე ცხრილის მონაცემების დამატებით (Lookup table), რომელიც მდებარეობს სხვა ფურცელზე ან სხვა Excel სამუშაო წიგნში, მაშინ შეგიძლიათ შეაგროვოთ საჭირო მნიშვნელობა პირდაპირ ფორმულაში, რომელიც ჩასვით მთავარ ცხრილში. .

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

ასე რომ, ფორმულა ერთად VLOOKUPშეიძლება იყოს ასეთი:

VLOOKUP(B2&" "&C2,შეკვეთები!$A&$2:$D$2,4,FALSE)
=VLOOKUP(B2&" "შეკვეთები!$A&$2:$D$2;4;FALSE)

აქ სვეტები B და C შეიცავს შესაბამისად მომხმარებელთა სახელებს და პროდუქტის სახელებს და ბმულს შეკვეთები!$A&$2:$D$2განსაზღვრავს ცხრილს სხვა სამუშაო ფურცელში მოსაძებნად.

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

VLOOKUP(B2&" "&C2,ბრძანებები,4,FALSE)
=VLOOKUP(B2&" "ბრძანებებს;4;FALSE)

იმისთვის, რომ ფორმულამ იმუშაოს, ცხრილის მარცხენა სვეტის მნიშვნელობები, რომელსაც ათვალიერებთ, უნდა გაერთიანდეს ისევე, როგორც საძიებო კრიტერიუმებში. ზემოთ მოცემულ ფიგურაში ჩვენ გავაერთიანეთ მნიშვნელობები და დავაყენეთ სივრცე მათ შორის, იგივე უნდა გაკეთდეს ფუნქციის პირველ არგუმენტში (B2&""&C2").

გახსოვდეს!ფუნქცია VLOOKUPშემოიფარგლება 255 სიმბოლოთი, მას არ შეუძლია 255 სიმბოლოზე მეტი სიგრძის მნიშვნელობის მოძიება. გაითვალისწინეთ ეს და დარწმუნდით, რომ თქვენთვის სასურველი მნიშვნელობის სიგრძე არ აღემატება ამ ზღვარს.

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

გამოვყავით მე-2, მე-3 და ა.შ. მნიშვნელობები VLOOKUP-ის გამოყენებით

თქვენ უკვე იცით ეს VLOOKUPშეუძლია დააბრუნოს მხოლოდ ერთი შესატყვისი მნიშვნელობა, უფრო სწორად, პირველი ნაპოვნი. მაგრამ რა მოხდება, თუ ეს მნიშვნელობა რამდენჯერმე განმეორდება მასივში, რომელსაც თქვენ ათვალიერებთ და გსურთ მათი მე-2 ან მე-3 ამონაწერი? რა მოხდება, თუ ყველა ღირებულება? პრობლემა რთული ჩანს, მაგრამ გამოსავალი არსებობს!

დავუშვათ, ცხრილის ერთი სვეტი შეიცავს მომხმარებელთა სახელებს (მომხმარებლის სახელი), ხოლო მეორე შეიცავს მათ მიერ შეძენილ პროდუქტებს (პროდუქტს). შევეცადოთ ვიპოვოთ მოცემული მომხმარებლის მიერ შეძენილი მე-2, მე-3 და მე-4 პროდუქტები.

უმარტივესი გზაა სვეტის წინ დამხმარე სვეტის დამატება მომხმარებლის სახელიდა შეავსეთ იგი მომხმარებლის სახელებით თითოეული სახელის განმეორებითი ნომრით, მაგ. ჯონ დო 1, ჯონ დო 2და ა.შ. მოდით გავაკეთოთ ხრიკი ნუმერაციის ფუნქციის გამოყენებით COUNTIF(COUNTIF), იმის გათვალისწინებით, რომ კლიენტების სახელები მოცემულია სვეტში B:

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

ამის შემდეგ შეგიძლიათ გამოიყენოთ ნორმალური ფუნქცია VLOOKUPთქვენთვის საჭირო შეკვეთის მოსაძებნად. მაგალითად:

ჩვენ ვპოულობთ მე-2 დენ ბრაუნი:
VLOOKUP ("დენ ბრაუნი2",$A$2:$C$16,3,FALSE)
=VLOOKUP("დენ ბრაუნი2",$A$2:$C$16,3,FALSE)
ჩვენ ვპოულობთ მე-3მყიდველის მიერ შეკვეთილი საქონელი დენ ბრაუნი:
VLOOKUP ("დენ ბრაუნი3",$A$2:$C$16,3,FALSE)
=VLOOKUP("დენ ბრაუნი3",$A$2:$C$16,3,FALSE)

თუ თქვენ ეძებთ მხოლოდ მე-2განმეორებით, ამის გაკეთება შეგიძლიათ დამხმარე სვეტის გარეშე, უფრო რთული ფორმულის შექმნით:

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

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

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

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

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

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

როგორც ზემოთ აღინიშნა, VLOOKUPარ შეუძლია ყველა დუბლიკატი მნიშვნელობის ამოღება სანახავი დიაპაზონიდან. ამისათვის დაგჭირდებათ ოდნავ უფრო რთული ფორმულა, რომელიც შედგება რამდენიმე 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)),"") )
(=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;"");ROW()-3));"") )

შეიყვანეთ ეს მასივის ფორმულა რამდენიმე მიმდებარე უჯრედში, მაგ. F4:F8, როგორც ნაჩვენებია ქვემოთ მოცემულ ფიგურაში. უჯრედების რაოდენობა უნდა იყოს ტოლი ან მეტი, ვიდრე საძიებო მნიშვნელობის გამეორებების მაქსიმალური შესაძლო რაოდენობა. არ დაგავიწყდეთ დააწკაპუნოთ Ctrl+Shift+Enterრომ სწორად შეიყვანოთ მასივის ფორმულა.

თუ გაინტერესებთ იმის გაგება, თუ როგორ მუშაობს იგი, მოდით ჩავუღრმავდეთ ფორმულის დეტალებს:

ნაწილი 1:

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

$F$2=B2:B16- შეადარეთ მნიშვნელობა F2 უჯრედში თითოეულ მნიშვნელობას B2:B16 დიაპაზონში. თუ შესატყვისი ნაპოვნია, მაშინ გამოთქმა ROW(C2:C16)-1აბრუნებს შესაბამისი მწკრივის ნომერს (მნიშვნელობა -1 საშუალებას გაძლევთ გამოტოვოთ სათაურის ხაზი). თუ მატჩები არ არის, ფუნქცია თუ(IF) აბრუნებს ცარიელ სტრიქონს.

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

ნაწილი 2:

ROW() -3
ROW() -3

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

ნაწილი 3:

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

ფუნქცია პატარა(SMALL) ბრუნდება nthყველაზე მცირე მნიშვნელობა მონაცემთა მასივში. ჩვენს შემთხვევაში, რომელი პოზიცია (ყველაზე პატარადან) უნდა დაბრუნდეს, განისაზღვრება ფუნქციით 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))
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1;""),ROW()-3))

ფუნქცია ინდექსი(INDEX) უბრალოდ აბრუნებს მასივის კონკრეტული უჯრედის მნიშვნელობას C2:C16. უჯრედისთვის F4ფუნქცია INDEX ($C$2:$C$16,1)დაბრუნდება ვაშლი, ამისთვის F5ფუნქცია INDEX ($C$2:$C$16,3)დაბრუნდება ტკბილეულიდა ასე შემდეგ.

ნაწილი 5:

IFERROR()
IFERROR()

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

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

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

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

ორგანზომილებიანი ძიების განხორციელების რამდენიმე გზა არსებობს. გაეცანით ხელმისაწვდომ ვარიანტებს და აირჩიე ის, რომელიც საუკეთესოდ ჯდება.

VLOOKUP და MATCH ფუნქციები

შეგიძლიათ გამოიყენოთ მთელი რიგი ფუნქციები VLOOKUP(VLOOKUP) და ძიება(MATCH) ველების გადაკვეთაზე მნიშვნელობის საპოვნელად პროდუქტის დასახელება(სტრიქონი) და თვეგანსახილველი მასივის (სვეტი):

VLOOKUP("ლიმონები",$A$2:$I$9,MATCH("მარტი",$A$1:$I$1,0),FALSE)
=VLOOKUP("ლიმონები",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

ზემოთ მოყვანილი ფორმულა ჩვეულებრივი ფუნქციაა VLOOKUP, რომელიც ეძებს "Lemons" მნიშვნელობის ზუსტ შესაბამისობას A2-დან A9-მდე უჯრედებში. მაგრამ რადგან არ იცით რომელ სვეტშია მარტის გაყიდვები, თქვენ ვერ შეძლებთ მიუთითოთ სვეტის ნომერი ფუნქციის მესამე არგუმენტისთვის VLOOKUP. ამის ნაცვლად, ფუნქცია გამოიყენება ძიებაამ სვეტის დასადგენად.

MATCH ("მარტი", $A$1:$I$1,0)
MATCH ("მარტი", $A$1:$I$1,0)

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

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

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

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

SUMPRODUCT ფუნქცია

ფუნქცია SUMPRODUCT(SUMPRODUCT) აბრუნებს არჩეული მასივების პროდუქტთა ჯამს:

SUMPRODUCT(($A$2:$A$9="ლიმონები")*($A$1:$I$1="მარტ"),$A$2:$I$9)
=SUMPRODUCT(($A$2:$A$9="ლიმონები")*($A$1:$I$1="მარტ");$A$2:$I$9)

INDEX და MATCH ფუნქციები

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

INDEX($A$2:$I$9,MATCH("ლიმონები",$A$2:$A$9,0),MATCH("მარტი",$A$1:$I$1,0))
=INDEX($A$2:$I$9,MATCH("ლიმონები",$A$2:$A$9,0),MATCH("მარტი",$A$1:$I$1,0))

სახელად Ranges და Intersection Operator

თუ არ გაგიჟდებით Excel-ის ყველა ამ რთულ ფორმულებზე, შეიძლება მოგეწონოთ ეს ვიზუალური და დასამახსოვრებელი გზა:

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

დააწკაპუნეთ შედიდა შეამოწმეთ შედეგი

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

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

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

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

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

დაწერეთ ფუნქცია VLOOKUP, რომელიც პოულობს პროდუქტის სახელს ცხრილში საძიებო ცხრილი 1გამოყენებით SKU, როგორც სასურველი მნიშვნელობა:
VLOOKUP (A2, New_SKU, 2, FALSE)
=VLOOKUP(A2,New_SKU,2,FALSE)
აქ New_SKU- დასახელებული დიაპაზონი $A:$Bმაგიდაში საძიებო ცხრილი 1, ა 2 – ეს არის სვეტი B, რომელიც შეიცავს პროდუქციის სახელებს (იხ. სურათი ზემოთ)
ჩამოწერეთ ფორმულა ცხრილიდან ფასების ჩასასმელად საძიებო ცხრილი 2პროდუქტის ცნობილი სახელების საფუძველზე. ამისათვის ჩადეთ ადრე შექმნილი ფორმულა, როგორც ახალი ფუნქციის საძიებო მნიშვნელობა VLOOKUP:
VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),ფასი,3,FALSE)
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),ფასი,3,FALSE)

აქ ფასი- დასახელებული დიაპაზონი $A:$Cმაგიდაში საძიებო ცხრილი 2, ა 3 არის C სვეტი, რომელიც შეიცავს ფასებს.

ქვემოთ მოყვანილი სურათი გვიჩვენებს ჩვენს მიერ შექმნილ ფორმულით დაბრუნებულ შედეგს:

მონაცემთა დინამიური ჩანაცვლება სხვადასხვა ცხრილიდან VLOOKUP და INDIRECT გამოყენებით

პირველ რიგში, მოდით განვმარტოთ, რას ვგულისხმობთ გამოთქმაში „სხვადასხვა ცხრილებიდან მონაცემების დინამიური ჩანაცვლება“, რათა დავრწმუნდეთ, რომ სწორად გვესმის ერთმანეთი.

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

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

თუ თქვენ გაქვთ მხოლოდ ორი ასეთი ანგარიში, მაშინ შეგიძლიათ გამოიყენოთ აღმაშფოთებელი მარტივი ფორმულა ფუნქციებით VLOOKUPდა თუ(IF) საძიებლად სასურველი ანგარიშის ასარჩევად:

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

$D$2– ეს არის უჯრედი, რომელიც შეიცავს პროდუქტის სახელს. გთხოვთ გაითვალისწინოთ, რომ ჩვენ ვიყენებთ აბსოლუტურ მითითებებს, რათა თავიდან ავიცილოთ საძიებო მნიშვნელობის შეცვლა ფორმულის სხვა უჯრედებში კოპირებისას.
$D3– ეს არის უჯრედი რეგიონის სახელწოდებით. ჩვენ ვიყენებთ აბსოლუტურ მითითებას სვეტისთვის და შედარებით მითითებას მწკრივისთვის, რადგან ვგეგმავთ ფორმულის კოპირებას იმავე სვეტის სხვა უჯრედებში.
FL_Salესდა CA_Sales– ცხრილების (ან დასახელებული დიაპაზონების) სახელები, რომლებიც შეიცავს გაყიდვების შესაბამის ანგარიშებს. რა თქმა უნდა, შეგიძლიათ გამოიყენოთ ჩვეულებრივი ფურცლების სახელები და უჯრედების დიაპაზონის მითითებები, მაგ. „FL Sheet“!$A$3:$B$10, მაგრამ დასახელებული დიაპაზონები ბევრად უფრო მოსახერხებელია.

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

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

VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"_გაყიდვები"),2,FALSE)

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

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

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

A1თუ არგუმენტი თანაბარია მართალი(TRUE) ან არ არის მითითებული;
R1C1, თუ ფALSE(ტყუილი).

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

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

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

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

VLOOKUP($D$2,INDIRECT($D3&"სამუშაო წიგნი1!_გაყიდვები"),2,FALSE)
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

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

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

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

SUMPRODUCT ფუნქციის გამოყენებით

კარგი, ჩვენ დავიწყებთ უმარტივესით.

დამატებითი სვეტის გამოყენება

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

მოდით შევხედოთ კლასიკურ მაგალითს. გვაქვს ცხრილი გაყიდვებით თვეებისა და ქალაქების მიხედვით. და ჩვენ უნდა განვსაზღვროთ გაყიდვების ღირებულება, რომელიც შეესაბამება ორ პირობას: თვე - თებერვალი და ქალაქი - სამარა.

VLOOKUP ფუნქციის კლასიკური ფორმით გამოყენება არ დაგვეხმარება, რადგან მას შეუძლია დააბრუნოს მხოლოდ ერთი პირობა. დამატებითი სვეტი დაგვეხმარება ამ სიტუაციიდან გამოსვლაში, რომელშიც გავაერთიანებთ თვის და ქალაქის სვეტების მნიშვნელობებს. ამისათვის ჩაწერეთ ფორმულა =B2&C2 A2 უჯრედში და გააფართოვეთ ეს ფორმულა A13 უჯრედამდე. ახლა ჩვენ შეგვიძლია გამოვიყენოთ A სვეტის მნიშვნელობები საჭირო მნიშვნელობის დასაბრუნებლად. ჩვენ ვწერთ ფორმულას G3 უჯრედში:

VLOOKUP(G1A2:D13;4;0)

ეს ფორმულა აერთიანებს G1 და G2 უჯრედების ორ მდგომარეობას ერთ მწკრივად და უყურებს მას A სვეტში. სასურველი პირობის პოვნის შემდეგ ფორმულა აბრუნებს მნიშვნელობას A1:D13 ცხრილის მეოთხე სვეტიდან, ე.ი. სვეტი გაყიდვები.

SELECT ფუნქციის გამოყენება ახალი საძიებო ცხრილის შესაქმნელად

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

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

VLOOKUP(G1SELECT((1,2),B2:B13&C2:C13,D2:D13),2,0)

ამ ფორმულის მთავარი პუნქტია CHOICE((1;2);B2:B13&C2:C13;D2:D13) ნაწილი, რომელიც აკეთებს ორ რამეს:

აერთიანებს სვეტის მნიშვნელობებს თვედა ქალაქიერთ მასივში: JanMoscow, FebMoscow...
აერთიანებს ორ მასივს ორსვეტიან ცხრილში.

ამ ფუნქციის შედეგი იქნება ცხრილი, რომელიც ასე გამოიყურება:

ახლა ფორმულა უფრო ნათელი გახდა.

მნიშვნელოვანია: ვინაიდან ჩვენ ვიყენებდით მასივის ფორმულას, როდესაც დაასრულებთ ფორმულის შეყვანას, დააჭირეთ Ctrl+Shift+Enter, რათა პროგრამამ იცოდეს ჩვენი განზრახვები. ამ კლავიშთა კომბინაციის დაჭერის შემდეგ, პროგრამა ავტომატურად დააინსტალირებს ხვეულ ბრეკეტებს ფორმულის დასაწყისში და ბოლოს.

INDEX და MATCH ფუნქციების გამოყენება

მესამე მეთოდი, რომელსაც განვიხილავთ, ასევე მოიცავს მასივის ფორმულის გამოყენებას და იყენებს INDEX და SEARCH ფუნქციებს.

ფორმულა ასე გამოიყურება.

INDEX(D2:D13,MATCH(1,(B2:B13=G1)*(C2:C13=G2),0))

მოდით შევხედოთ რას აკეთებს ამ ფორმულის თითოეული ნაწილი.

პირველ რიგში განიხილეთ ფუნქცია MATCH(1;(B2:B13=G1)*(C2:C13=G2);0). ამ შემთხვევაში, G1 უჯრედის მნიშვნელობა თანმიმდევრულად შედარებულია B2:B13 დიაპაზონის უჯრედების თითოეულ მნიშვნელობასთან და აბრუნებს TRUE-ს, თუ მნიშვნელობები ემთხვევა და FALSE-ს, თუ ისინი არ შეესაბამება. იგივე შედარება ხდება G2 უჯრედის მნიშვნელობასთან და C2:C13 დიაპაზონთან. შემდეგ ჩვენ შევადარებთ ორივე მასივს, რომელიც შედგება TRUE და FALSE-ისგან. კომბინაცია TRUE * TRUE გვაძლევს შედეგს 1 (TRUE). მოდით შევხედოთ ქვემოთ მოცემულ სურათს, რომელიც დაგეხმარებათ უფრო ნათლად აგიხსნათ მოქმედების პრინციპი.

ახლა შეგვიძლია ვთქვათ, სად მდებარეობს სტრიქონი, რომელიც აკმაყოფილებს ორივე პირობას. MATCH ფუნქცია პოულობს 1-ის პოზიციას შედეგების მასივში და აბრუნებს 6-ს, რადგან 1 ხდება მეექვსე რიგში. შემდეგი, INDEX ფუნქცია აბრუნებს D2:D13 დიაპაზონის მეექვსე მწკრივის მნიშვნელობას.

SUMPRODUCT-ის გამოყენება

Excel-ის ერთ-ერთი ყველაზე ძლიერი ფორმულა. ამ ფორმულას ცალკე სტატიაც კი მაქვს მიძღვნილი. მრავალი პირობის გამოყენების ჩვენი მეოთხე გზა არის ფორმულის დაწერა SUMPRODUCT ფუნქციით. და ეს ასე გამოიყურება:

SUMPRODUCT((B2:B13=G1)*(C2:C13=G2);D2:D13)

ამ ფორმულის მოქმედების პრინციპი მსგავსია წინა მიდგომის მოქმედების პრინციპის. იქმნება ვირტუალური ცხრილი, რომელშიც G1 და G2 უჯრედების მნიშვნელობები შედარებულია B2:B13 და C2:C13 დიაპაზონებთან, შესაბამისად. შემდეგ, ორივე ეს მასივი შედარებულია და მიიღება ერთებისა და ნულების მასივი, სადაც ერთი ენიჭება იმ მწკრივს, რომელშიც ორივე პირობა ემთხვევა. შემდეგი, ეს ვირტუალური მასივი მრავლდება D2:D13 დიაპაზონზე. ვინაიდან ჩვენს ვირტუალურ მასივს ექნება მხოლოდ ერთი 1 მეექვსე რიგში, ფორმულა დააბრუნებს შედეგს 189.

ეს ფუნქცია არ იმუშავებს, თუ არის ტექსტის მნიშვნელობები D2:D13 დიაპაზონში.

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

შედეგი

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

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

ნებისმიერი მომხმარებელი, რომელიც მუშაობს LOOKUP ფუნქციით, გარკვეული პერიოდის შემდეგ ხვდება, რომ არ არის ძალიან მოსახერხებელი მუშაობა იმ ფუნქციასთან, რომელიც ეძებს მხოლოდ იმ სვეტის მარჯვნივ, რომელშიც მდებარეობს მოძიებული მნიშვნელობა. MATCH და INDEX ფუნქციებს შეუძლიათ ამ პრობლემის გადაჭრა, მაგრამ საერთო ფორმულა გაცილებით გრძელი და რთული იქნება, ვიდრე მარტივი VLOOKUP.

მორგებული დანამატი rLOOKUPმუშაობს როგორც VLOOKUP, მაგრამ საშუალებას გაძლევთ შეასრულოთ საპირისპირო ძებნა. ფუნქცია უბრალოდ იყენებს ინდექსისა და მატჩის მეთოდებს VBA-ში. იგი მოწოდებულია როგორც Excel-ის დანამატი, მარტივად გამოყენებისთვის.

ჩამოტვირთეთ დანამატი

შეინახეთ ფაილი თქვენს კომპიუტერში და დააინსტალირეთ Excel-ში არჩევით ფაილი -> ოფციები -> დამატებები. დააჭირეთ ღილაკს წადი..., ფანჯარაში, რომელიც იხსნება, აირჩიეთ მიმოხილვა. აირჩიეთ შენახული ფაილი და დააწკაპუნეთ OK ინსტალაციის დასასრულებლად. ინსტალაციის შემდეგ, შეტყობინება "Reverse_Lookup" გამოჩნდება ხელმისაწვდომი დანამატების სიაში და rLOOKUP ფუნქცია ხელმისაწვდომი გახდება ყველა წიგნში.

ფუნქციის აღწერა

rLOOKUP ფუნქციის არგუმენტები იგივეა, რაც VLOOKUP ფუნქციისთვის, იხილეთ სკრინშოტი ქვემოთ. საპირისპირო ძიების შესასრულებლად, უბრალოდ შეიყვანეთ უარყოფითი რიცხვი არგუმენტში "Col_Index_Num" (იხილეთ მაგალითები ქვემოთ). ფორმულა მოძებნის მარცხნივ.

ამ ორ ფუნქციას შორის ერთ-ერთი განსხვავება ისაა, რომ თუ Time-lapse_view VLOOKUP-ში გამოტოვებული, შედეგი ნამდვილად არ იქნება. თუმცა, საპირისპიროა rLOOKUP, რომელიც ნაგულისხმევად აბრუნებს ზუსტ მნიშვნელობას. კიდევ ერთხელ, ეს იმიტომ ხდება, რომ უფრო ხშირად, ვიდრე არა, მომხმარებლებს სურთ მიიღონ მნიშვნელობა ძიების შედეგიდან. rLOOKUP ფუნქციის მიერ დაბრუნებული შეცდომის მნიშვნელობები მსგავსია VLOOKUP-ის მიერ დაბრუნებული მნიშვნელობების.

ფუნქციის მაგალითი

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

ამ კითხვაზე პასუხის გასაცემად "ვინ მუშაობს ოფისში B43" შეგვიძლია გამოვიყენოთ VLOOKUP ფუნქცია:

VLOOKUP("B43"; A1:C6;2; FALSE) აბრუნებს "Dilbert".

თუმცა, თუ გვინდა ვიცოდეთ, "სად არის დილბერტის ოფისი, მაშინ VLOOKUP არ იმუშავებს?" აქ rLOOKUP ფუნქცია გამოდგება:

rLOOKUP ("დილბერტი"; A1:B6, -2).

გთხოვთ გაითვალისწინოთ, რომ არგუმენტებში სვეტის ნომერი მითითებულია, როგორც უარყოფითი რიცხვი -2, რაც საშუალებას გაძლევთ მოძებნოთ მარცხენა სვეტში.

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

rLOOKUP("B43";A1:C6;2) აბრუნებს "Dilbert" და

rLOOKUP("Dilbert"; A1:B6; -2) აბრუნებს "B43".

სხვა მაგალითები მოცემულია ქვემოთ:

არ დაგავიწყდეთ, რომ ჯერ დააინსტალიროთ დანამატი, წინააღმდეგ შემთხვევაში დაბრუნდება შეცდომა #NAME?.

როგორ მუშაობს ფუნქცია

ფუნქცია იყენებს ფუნქციებს MATCH და INDEXსაპირისპირო ძიების გასაშვებად.

იხილეთ ფუნქციის საწყისი კოდი ქვემოთ.

ფუნქცია rLOOKUP(Lookup_value, Table_Array როგორც დიაპაზონი, Col_index_num როგორც მთელი რიცხვი, არჩევითი Range_lookup როგორც ლოგიკური)
"
"მორგებული ფუნქცია, რომელიც საშუალებას იძლევა წინ და უკან ვერტიკალური ძიება
"მუშაობს როგორც VLookup დადებითი Col_index_num-ისთვის და აბრუნებს ძიებებს უარყოფითი Col_index_num-ისთვის
"VLOOKUP-ისგან განსხვავებით, ის ნაგულისხმევად ეძებს ზუსტ მნიშვნელობას
"
"ნიტინ მეჰტა, www.Engineers-Excel.com
"
"
Dim Source_Col როგორც დიაპაზონი, Dest_Col_num როგორც მთელი რიცხვი

წყაროს სვეტი არის სვეტი, სადაც Lookup_value მდებარეობს. ეს არის ყველაზე მარცხენა სვეტი, თუ
"Col_index_number დადებითია. თუ Col_index_number უარყოფითია, ეს არის ყველაზე მარჯვენა სვეტი
დააყენეთ Source_Col = Table_array.Columns(IIf(Col_index_num > 0, 1, Table_array.Columns.Count))

"Dest_col_num არის სვეტის ნომერი Table_array დიაპაზონში, საიდანაც ჩვენ უნდა ავიღოთ ჩანაწერი Lookup_value-ის წინააღმდეგ
Dest_Col_num = IIf(Col_index_num > 0, Col_index_num, Table_Array.Columns.Count + Col_index_num + 1)

"გამოიყენეთ MATCH და INDEX ფუნქციები საძიებლად.
ბრაიან კეინსის მიერ შემოთავაზებული CVERr(xlErrRef) გამოყენება
rLOOKUP = IIf(დამკვეთი_სვე_რაოდენობა< 1, CVErr(xlErrRef), Application.Index(Table_array, Application.Match(Lookup_value, Source_Col, Range_lookup), Dest_Col_num))

დასრულების ფუნქცია

დატოვე შენი კომენტარი!