შინაარსი
ეს პატარა სახელმძღვანელო განმარტავს, თუ როგორ უნდა შექმნათ ფუნქცია VPR (VLOOKUP) რეგისტრისადმი მგრძნობიარე, აჩვენებს რამდენიმე სხვა ფორმულას, რომლებშიც Excel-ს შეუძლია მოძებნოს რეგისტრის მგრძნობიარე წესით და მიუთითებს თითოეული ფუნქციის ძლიერ და სუსტ მხარეებზე.
ვფიქრობ, Excel-ის ყველა მომხმარებელმა იცის, რა ფუნქცია ასრულებს ვერტიკალურ ძიებას. მართალია, ეს ფუნქციაა VPR. თუმცა, ცოტამ თუ იცის ეს VPR არ არის რეგისტრირებული, ანუ მცირე და დიდი ასოები მისთვის იდენტურია.
აქ არის სწრაფი მაგალითი, რომელიც აჩვენებს უუნარობას VPR რეესტრის ამოცნობა. დავუშვათ, საკანში A1 შეიცავს მნიშვნელობას „ბილი“ და უჯრედი A2 - "ბილი", ფორმულა:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… შეაჩერებს ძიებას „ბილი“-ზე, რადგან ეს მნიშვნელობა პირველ ადგილზეა სიაში და ამოიღებს მნიშვნელობას უჯრედიდან B1.
მოგვიანებით ამ სტატიაში მე გაჩვენებთ როგორ გააკეთოთ VPR შემთხვევის მგრძნობიარე. გარდა ამისა, ჩვენ ვისწავლით კიდევ რამდენიმე ფუნქციას, რომელსაც შეუძლია შეასრულოს რეგისტრირებული ძიებები Excel-ში.
ჩვენ დავიწყებთ უმარტივესით - VIEW (ძებნა) და პროდუქტი (SUMPRODUCT), რომელსაც, სამწუხაროდ, აქვს რამდენიმე მნიშვნელოვანი შეზღუდვა. შემდეგი, ჩვენ უფრო დეტალურად განვიხილავთ ოდნავ უფრო რთულ ფორმულას INDEX+MATCH (INDEX+MATCH), რომელიც მუშაობს უნაკლოდ ნებისმიერ სიტუაციაში და ნებისმიერ მონაცემთა ბაზაში.
VLOOKUP ფუნქცია მგრძნობიარეა
როგორც უკვე იცით, ჩვეულებრივი ფუნქცია VPR არის რეგისტრის მგრძნობელობა. თუმცა, არსებობს გზა, რათა ის ქეისით მგრძნობიარე იყოს. ამისათვის თქვენ უნდა დაამატოთ ცხრილს დამხმარე სვეტი, როგორც ეს ნაჩვენებია შემდეგ მაგალითში.
დავუშვათ სვეტში B არის პროდუქტის იდენტიფიკატორები (საქონელი) და გსურთ ამოიღოთ პროდუქტის ფასი და შესაბამისი კომენტარი სვეტებიდან C и D. პრობლემა ის არის, რომ იდენტიფიკატორები შეიცავს როგორც მცირე, ასევე დიდ ასოებს. მაგალითად, უჯრედის მნიშვნელობები B4 (001Tvci3u) და B5 (001Tvci3U) განსხვავდება მხოლოდ ბოლო სიმბოლოს შემთხვევაში, u и U შესაბამისად.
როგორც თქვენ წარმოიდგინეთ, ჩვეულებრივი საძიებო ფორმულა
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
დაბრუნდება $ 90, ღირებულებიდან გამომდინარე 001Tvci3u არის საძიებო დიაპაზონში უფრო ადრე ვიდრე 001Tvci3U. მაგრამ ეს არ არის ის, რაც ჩვენ გვჭირდება, არა?
ფუნქციით მოსაძებნად VPR Excel-ის შემთხვევაში, თქვენ მოგიწევთ დაამატოთ დამხმარე სვეტი და შეავსოთ მისი უჯრედები შემდეგი ფორმულით (სადაც B არის საძიებო სვეტი):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
ეს ფორმულა არღვევს სასურველ მნიშვნელობას ცალკეულ სიმბოლოებად, ცვლის თითოეულ სიმბოლოს თავისი კოდით (მაგალითად, ნაცვლად A სამაგიეროდ 65-ზე a კოდი 97) და შემდეგ აერთიანებს ამ კოდებს რიცხვების უნიკალურ სტრიქონში.
ამის შემდეგ, ჩვენ ვიყენებთ მარტივ ფუნქციას VPR შემთხვევის მგრძნობიარე ძიებისთვის:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
ფუნქციის სწორად მუშაობა VPR შემთხვევის მიმართ მგრძნობიარე დამოკიდებულია ორ ფაქტორზე:
- დამხმარე სვეტი უნდა იყოს ყველაზე მარცხენა სვეტი ხილვადობის დიაპაზონში.
- მნიშვნელობა, რომელსაც ეძებთ, რეალური მნიშვნელობის ნაცვლად უნდა შეიცავდეს სიმბოლოს კოდს.
როგორ გამოვიყენოთ CODE ფუნქცია სწორად
დამხმარე სვეტის უჯრედებში ჩასმული ფორმულა ვარაუდობს, რომ თქვენი ძიების ყველა მნიშვნელობას აქვს იგივე რაოდენობის სიმბოლო. თუ არა, მაშინ თქვენ უნდა იცოდეთ ყველაზე პატარა და უდიდესი რიცხვები და დაამატოთ რაც შეიძლება მეტი ფუნქცია Iferror (IFERROR) რამდენი სიმბოლოა განსხვავება უმოკლეს და ხანგრძლივ მოძიებულ მნიშვნელობას შორის.
მაგალითად, თუ უმოკლეს საძიებო მნიშვნელობა არის 3 სიმბოლო, ხოლო ყველაზე გრძელი 5 სიმბოლო, გამოიყენეთ ეს ფორმულა:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
ფუნქციისთვის PSTR (MID) თქვენ აწვდით შემდეგ არგუმენტებს:
- 1 არგუმენტი - ტექსტი (ტექსტი) არის ტექსტი ან უჯრედის მითითება, რომელიც შეიცავს ამოსაღებ სიმბოლოებს (ჩვენს შემთხვევაში ეს არის B2)
- 2 არგუმენტი - start_num (დაწყების_პოზიცია) არის პირველი ამოსაღები სიმბოლოების პოზიცია. შედიხარ 1 პირველ ფუნქციაში PSTR, 2 – მეორე ფუნქციაში PSTR ა.შ.
- 3 არგუმენტი - num_chars (სიმბოლოების_რაოდენობა) – განსაზღვრავს ტექსტიდან ამოსაღები სიმბოლოების რაოდენობას. ვინაიდან სულ გვჭირდება მხოლოდ 1 სიმბოლო, ყველა ფუნქციაში ვწერთ 1.
შეზღუდვები: ფუნქცია VPR არ არის საუკეთესო გამოსავალი Excel-ში რეგისტრირებული ძიებებისთვის. პირველ რიგში, საჭიროა დამხმარე სვეტის დამატება. მეორეც, ფორმულა კარგად მუშაობს მხოლოდ იმ შემთხვევაში, თუ მონაცემები ერთგვაროვანია, ან ცნობილია საძიებო მნიშვნელობებში სიმბოლოების ზუსტი რაოდენობა. თუ ეს არ არის თქვენი შემთხვევა, უმჯობესია გამოიყენოთ ერთ-ერთი გამოსავალი, რომელსაც ქვემოთ ვაჩვენებთ.
LOOKUP ფუნქცია რეგისტრირებული ძებნისთვის
ფუნქცია VIEW (LOOKUP) დაკავშირებული VPR, თუმცა მისი სინტაქსი იძლევა რეგისტრის მგრძნობიარე ძიების საშუალებას დამხმარე სვეტის დამატების გარეშე. ამისათვის გამოიყენეთ VIEW ფუნქციასთან ერთად EXACT (ზუსტი).
თუ ავიღებთ მონაცემებს წინა მაგალითიდან (დამხმარე სვეტის გარეშე), მაშინ შემდეგი ფორმულა გაუმკლავდება დავალებას:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
ფორმულის ძიება დიაპაზონში A2: A7 ზუსტი ემთხვევა უჯრედის მნიშვნელობას F2 რეზონანსული და აბრუნებს მნიშვნელობას იმავე მწკრივის B სვეტიდან.
Like VPRფუნქცია VIEW თანაბრად მუშაობს ტექსტთან და ციფრულ მნიშვნელობებთან, როგორც ხედავთ ქვემოთ მოცემულ ეკრანის სურათზე:
მნიშვნელოვანია! ფუნქციის მიზნით VIEW სწორად მუშაობდა, საძიებელი სვეტის მნიშვნელობები უნდა იყოს დალაგებული ზრდადობის მიხედვით, ანუ უმცირესიდან ყველაზე დიდამდე.
ნება მომეცით მოკლედ აგიხსნათ, თუ როგორ მუშაობს ფუნქცია EXACT ზემოთ ნაჩვენები ფორმულაში, რადგან ეს არის მთავარი წერტილი.
ფუნქცია EXACT ადარებს ტექსტის ორ მნიშვნელობას პირველ და მე-1 არგუმენტებში და აბრუნებს TRUE-ს, თუ ისინი ზუსტად იგივეა, ან FALSE-ს, თუ ისინი არ არიან. ჩვენთვის მნიშვნელოვანია, რომ ფუნქცია EXACT შემთხვევის მგრძნობიარე.
ვნახოთ, როგორ მუშაობს ჩვენი ფორმულა VIEW+EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- ფუნქცია EXACT ადარებს უჯრედის მნიშვნელობას F2 ყველა ელემენტით სვეტში A (A2:A7). აბრუნებს TRUE-ს, თუ ზუსტი დამთხვევა არის ნაპოვნი, წინააღმდეგ შემთხვევაში FALSE.
- რადგან თქვენ აძლევთ პირველ ფუნქციის არგუმენტს VIEW მნიშვნელობა TRUE, ის ამოიღებს შესაბამის მნიშვნელობას მითითებული სვეტიდან (ჩვენს შემთხვევაში, სვეტი B) მხოლოდ იმ შემთხვევაში, თუ აღმოჩენილია ზუსტი შესატყვისი, რეგისტრის მგრძნობიარე.
იმედი მაქვს, რომ ეს ახსნა გასაგები იყო და ახლა გესმით მთავარი აზრი. თუ ასეა, მაშინ არ გექნებათ რაიმე სირთულე სხვა ფუნქციებთან დაკავშირებით, რომლებსაც შემდგომ გავაანალიზებთ, რადგან. ისინი ყველა მუშაობენ იმავე პრინციპით.
შეზღუდვები: საძიებო სვეტის მონაცემები უნდა იყოს დალაგებული ზრდადი თანმიმდევრობით.
SUMPRODUCT - პოულობს ტექსტის მნიშვნელობებს, რეგისტრის მგრძნობიარე, მაგრამ აბრუნებს მხოლოდ ციფრებს
როგორც სათაურიდან უკვე მიხვდით, პროდუქტი (SUMPRODUCT) არის Excel-ის კიდევ ერთი ფუნქცია, რომელიც დაგეხმარებათ ჩაატაროთ რეგისტრის მგრძნობიარე ძიება, მაგრამ დააბრუნებს მხოლოდ ციფრულ მნიშვნელობებს. თუ ეს ვარიანტი არ ჯდება, მაშინვე შეგიძლიათ გადახვიდეთ პაკეტზე INDEX+MATCH, რომელიც იძლევა გამოსავალს ნებისმიერი შემთხვევისთვის და მონაცემთა ნებისმიერი ტიპისთვის.
პირველ რიგში, ნება მომეცით მოკლედ აგიხსნათ ამ ფუნქციის სინტაქსი, ეს დაგეხმარებათ უკეთ გაიგოთ რეგისტრირებული ფორმულა, რომელიც შემდეგშია.
ფუნქცია პროდუქტი ამრავლებს მოცემული მასივების ელემენტებს და აბრუნებს შედეგების ჯამს. სინტაქსი ასე გამოიყურება:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
იმის გამო, რომ ჩვენ გვჭირდება ქეისისადმი მგრძნობიარე ძიება, ჩვენ ვიყენებთ ფუნქციას EXACT (EXACT) წინა მაგალითიდან, როგორც ერთ-ერთი მულტიპლიკატორი:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
როგორც გახსოვთ EXACT ადარებს უჯრედის მნიშვნელობას F2 ყველა ელემენტით სვეტში A. აბრუნებს TRUE-ს, თუ ზუსტი დამთხვევა არის ნაპოვნი, წინააღმდეგ შემთხვევაში FALSE. მათემატიკური ოპერაციებისას Excel იღებს TRUE როგორც 1, და FALSE for 0დამატებითი პროდუქტი ამრავლებს ამ რიცხვებს და აჯამებს შედეგებს.
ნულები არ ითვლება, რადგან გამრავლებისას ისინი ყოველთვის იძლევიან 0. მოდით უფრო დეტალურად შევხედოთ რა ხდება სვეტში ზუსტი თანხვედრის დროს A იპოვეს და დაბრუნდნენ 1… ფუნქცია პროდუქტი ამრავლებს რიცხვს სვეტში B on 1 და აბრუნებს შედეგს - ზუსტად იგივე რიცხვს! ეს იმიტომ ხდება, რომ სხვა პროდუქტების შედეგები ნულის ტოლია და ისინი არ იმოქმედებენ მიღებულ თანხაზე.
სამწუხაროდ ფუნქცია პროდუქტი ვერ იმუშავებს ტექსტურ მნიშვნელობებთან და თარიღებთან, რადგან მათი გამრავლება შეუძლებელია. ამ შემთხვევაში, თქვენ მიიღებთ შეცდომის შეტყობინებას #VALUE! (#VALUE!) როგორც უჯრედში F4 ქვემოთ მოცემულ სურათზე:
შეზღუდვები: აბრუნებს მხოლოდ ციფრულ მნიშვნელობებს.
INDEX + MATCH – ნებისმიერი ტიპის მონაცემებისთვის რეგისტრირებული ძიება
და ბოლოს, ჩვენ ახლოს ვართ შეუზღუდავი და რეგისტრირებული ძებნის ფორმულასთან, რომელიც მუშაობს ნებისმიერი მონაცემთა ნაკრებით.
ეს მაგალითი ბოლოა, არა იმიტომ, რომ საუკეთესო დესერტად რჩება, არამედ იმიტომ, რომ წინა მაგალითებიდან მიღებული ცოდნა დაგეხმარებათ უკეთ და სწრაფად გაიგოთ რეგისტრირებული ფორმულა. INDEX+MATCH (INDEX+MATCH).
როგორც თქვენ ალბათ მიხვდით, ფუნქციების კომბინაცია უფრო გამოვლენილი и ინდექსი გამოიყენება Excel-ში, როგორც უფრო მოქნილი და ძლიერი ალტერნატივა VPR. სტატია VLOOKUP-ის ნაცვლად INDEX-ისა და MATCH-ის გამოყენება შესანიშნავად აგიხსნით, თუ როგორ მუშაობს ეს ფუნქციები ერთად.
მე უბრალოდ შევაჯამებ ძირითად პუნქტებს:
- ფუნქცია უფრო გამოვლენილი (MATCH) ეძებს მნიშვნელობას მოცემულ დიაპაზონში და აბრუნებს მის ფარდობით პოზიციას, ანუ მწკრივის ან/და სვეტის ნომერს;
- შემდეგი, ფუნქცია ინდექსი (INDEX) აბრუნებს მნიშვნელობას მითითებული სვეტიდან და/ან მწკრივიდან.
ფორმულამდე INDEX+MATCH შეიძლება მოძებნოთ რეგისტრირებული ასოების მიხედვით, თქვენ მხოლოდ ერთი ფუნქციის დამატება გჭირდებათ. ძნელი მისახვედრი არ არის ისევ რა არის EXACT (ზუსტი):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
ამ ფორმულაში EXACT მუშაობს ისევე, როგორც ფუნქციასთან ერთად VIEWდა იძლევა იგივე შედეგს:
გაითვალისწინეთ, რომ ფორმულა INDEX+MATCH ხვეული ბრეკეტებში ჩასმული არის მასივის ფორმულა და თქვენ უნდა შეავსოთ იგი დაჭერით Ctrl + Shift + Enter.
რატომ არის INDEX+MATCH საუკეთესო გამოსავალი ასოებისადმი მგრძნობიარე ძიებისთვის?
შეფუთვის ძირითადი უპირატესობები ინდექსი и უფრო გამოვლენილი:
- არ საჭიროებს დამხმარე სვეტის დამატებას, განსხვავებით VPR.
- არ საჭიროებს საძიებო სვეტის დალაგებას, განსხვავებით VIEW.
- მუშაობს ყველა ტიპის მონაცემით - ნომრები, ტექსტი და თარიღები.
ეს ფორმულა იდეალურად გამოიყურება, არა? სინამდვილეში, ეს ასე არ არის. და ამიტომ.
დავუშვათ, რომ საძიებო მნიშვნელობასთან დაკავშირებული დაბრუნების მნიშვნელობის სვეტის უჯრედი ცარიელია. რა შედეგს დააბრუნებს ფორმულა? არა? ვნახოთ, რას აბრუნებს ფორმულა სინამდვილეში:
ოჰ, ფორმულა აბრუნებს ნულს! ეს შეიძლება არ იყოს დიდი პრობლემა, თუ თქვენ მუშაობთ სუფთა ტექსტის მნიშვნელობებთან. თუმცა, თუ ცხრილი შეიცავს ციფრებს, მათ შორის „რეალურ“ ნულებს, ეს პრობლემად იქცევა.
სინამდვილეში, ყველა სხვა საძიებო ფორმულა (VLOOKUP, LOOKUP და SUMPRODUCT), რომელიც ადრე განვიხილეთ, ერთნაირად იქცევა. მაგრამ გსურთ სრულყოფილი ფორმულა, არა?
ფორმულის გასაკეთებლად რეგისტრირებული INDEX+MATCH სრულყოფილი, ჩადეთ ის ფუნქციაში IF (IF), რომელიც შეამოწმებს უჯრედს დაბრუნების მნიშვნელობით და დააბრუნებს ცარიელ შედეგს, თუ ის ცარიელია:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
ამ ფორმულაში:
- B არის სვეტი დაბრუნებული მნიშვნელობებით
- 1+ არის რიცხვი, რომელიც აბრუნებს ფუნქციით დაბრუნებული უჯრედის ფარდობით პოზიციას უფრო გამოვლენილი, უჯრედის რეალურ მისამართზე. მაგალითად, ჩვენს ფუნქციაში უფრო გამოვლენილი მოცემული საძიებო მასივი A2: A7, ანუ უჯრედის ფარდობითი პოზიცია A2 იქნება 1, რადგან ის პირველია მასივში. მაგრამ უჯრედის რეალური პოზიცია A2 სვეტში არის 2, ასე რომ დავამატებთ 1რომ შეადგინოს განსხვავება და ჰქონდეს ფუნქცია არაპირდაპირი (INDIRECT) მოიძია მნიშვნელობა სასურველი უჯრედიდან.
ქვემოთ მოყვანილ სურათებში ნაჩვენებია შესწორებული ფორმულა INDEX+MATCH მოქმედებაში. ის აბრუნებს ცარიელ შედეგს, თუ დაბრუნებული უჯრედი ცარიელია.
მე გადავწერე ფორმულა სვეტებად ბ:დრათა მოერგოს ფორმულის ზოლს ეკრანის სურათზე.
ფორმულა ბრუნდება 0თუ დაბრუნებული უჯრედი შეიცავს ნულს.
თუ გინდა ლინკი ინდექსი и უფრო გამოვლენილი ნაჩვენებია გარკვეული შეტყობინება, როდესაც დაბრუნების მნიშვნელობა ცარიელია, შეგიძლიათ ჩაწეროთ იგი ფორმულის ბოლო ბრჭყალებში (“”), მაგალითად, ასე:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
