Mündəricat
- Excel-də bir neçə meyarla axtarın
- VLOOKUP istifadə edərək 2-ci, 3-cü və s. dəyərləri çıxarırıq
- İstədiyiniz dəyərin bütün təkrarlarını əldə edin
- Məlum sətir və sütun üzrə XNUMXD axtarış
- Bir düsturda çoxlu VLOOKUP-dan istifadə
- VLOOKUP və DOLAYI istifadə edərək müxtəlif cədvəllərdən verilənlərin dinamik dəyişdirilməsi
Funksiyaya dair təlimatımızın ikinci hissəsində VPR Excel-də (VLOOKUP) bütün gücü yönləndirməyə kömək edəcək bir neçə nümunəni təhlil edəcəyik VPR ən iddialı Excel tapşırıqlarını həll etmək. Nümunələr güman edir ki, siz artıq bu funksiyanın necə işlədiyinə dair əsas biliklərə maliksiniz. Yoxdursa, bu dərsliyin sintaksisi və əsas istifadəni izah edən birinci hissəsindən başlamaq sizə maraqlı ola bilər. VPR. Yaxşı, başlayaq.
Excel-də bir neçə meyarla axtarın
Function VPR Excel-də verilənlər bazasında müəyyən bir dəyər üçün axtarış aparmaq üçün həqiqətən güclü bir vasitədir. Bununla belə, əhəmiyyətli bir məhdudiyyət var - onun sintaksisi yalnız bir dəyəri axtarmağa imkan verir. Bir neçə şərtlə axtarış etmək istəsəniz nə olacaq? Aşağıda həllini tapacaqsınız.
Nümunə 1: 2 müxtəlif meyarla axtarın
Tutaq ki, bizdə sifarişlərin siyahısı var və biz tapmaq istəyirik Malların miqdarı (qty.), iki meyar əsasında - Müştəri adı (Müştəri) и Название продукта (Məhsul). Aşağıdakı cədvəldən göründüyü kimi, alıcıların hər birinin bir neçə növ mal sifariş etməsi məsələni çətinləşdirir:
müntəzəm funksiya VPR bu ssenaridə işləməyəcək, çünki verilmiş axtarış dəyərinə uyğun tapdığı ilk dəyəri qaytaracaq. Məsələn, bir maddənin miqdarını bilmək istəyirsinizsə şirniyyatalıcı tərəfindən sifariş edilir Jeremy Hill, aşağıdakı düsturu yazın:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– bu formula nəticəni qaytaracaq 15məhsula uyğundur Apples, çünki uyğun gələn ilk dəyərdir.
Sadə bir həll yolu var - bütün istədiyiniz meyarları birləşdirəcək əlavə bir sütun yaradın. Bizim nümunəmizdə bunlar sütunlardır Müştəri adı (Müştəri) и Название продукта (Məhsul). Unutmayın ki, birləşdirilən sütun həmişə axtarış diapazonunda ən sol sütun olmalıdır, çünki funksiyanın işlədiyi sol sütundur. VPR dəyər axtararkən yuxarı baxır.
Beləliklə, cədvələ köməkçi sütun əlavə edirsiniz və aşağıdakı düsturu onun bütün hüceyrələrinə köçürürsünüz: =B2&C2. Sətin daha oxunaqlı olmasını istəyirsinizsə, birləşdirilmiş dəyərləri boşluqla ayıra bilərsiniz: =B2&» «&C2. Bundan sonra aşağıdakı düsturdan istifadə edə bilərsiniz:
=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;ЛОЖЬ)
Hüceyrə haradadır B1 arqumentin birləşdirilən dəyərini ehtiva edir lookup_value (axtarış_dəyəri) və 4 – Arqument col_index_num (sütun_nömrəsi), yəni əldə ediləcək məlumatları ehtiva edən sütunun nömrəsi.
Nümunə 2: Cədvəl başqa vərəqdə görünməklə iki meyar üzrə VLOOKUP
Başqa vərəqdə və ya başqa bir Excel iş kitabında yerləşən ikinci cədvəldən (Axtarış cədvəli) məlumat əlavə etməklə əsas cədvəli (Əsas cədvəl) yeniləməlisinizsə, istədiyiniz dəyəri birbaşa daxil etdiyiniz düsturda toplaya bilərsiniz. əsas masaya.
Əvvəlki nümunədə olduğu kimi, Axtarış cədvəlində birləşdirilmiş dəyərləri olan köməkçi sütuna ehtiyacınız olacaq. Bu sütun axtarış aralığında ən sol sütun olmalıdır.
Beləliklə, formula ilə VPR belə ola bilər:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Burada B və C sütunlarında müvafiq olaraq müştəri adları və məhsul adları və keçid var Sifarişlər!$A&$2:$D$2 başqa vərəqdə axtarmaq üçün cədvəli müəyyənləşdirir.
Düsturu daha oxunaqlı etmək üçün siz baxış diapazonuna ad verə bilərsiniz və sonra formula daha sadə görünəcək:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Düsturun işləməsi üçün baxdığınız cədvəlin ən sol sütunundakı dəyərlər axtarış meyarlarında olduğu kimi eyni şəkildə birləşdirilməlidir. Yuxarıdakı şəkildə, biz u2bu2band dəyərlərini birləşdirdik, aralarında boşluq qoyduq, eyni şəkildə funksiyanın ilk arqumentində də etməlisiniz (BXNUMX& “” & CXNUMX).
Unutma! Function VPR 255 simvol ilə məhdudlaşır, o, 255 simvoldan çox olan dəyəri axtara bilməz. Bunu nəzərə alın və istədiyiniz dəyərin uzunluğunun bu həddi aşmadığından əmin olun.
Razıyam ki, köməkçi sütun əlavə etmək ən zərif və həmişə məqbul bir həll deyil. Siz köməkçi sütun olmadan da eyni şeyi edə bilərsiniz, lakin bunun üçün funksiyaların birləşməsi ilə daha mürəkkəb düstur tələb olunur İNDEKSİ (İNDEKS) və MAÇ (DAHA FAZLA).
VLOOKUP istifadə edərək 2-ci, 3-cü və s. dəyərləri çıxarırıq
Bunu artıq bilirsiniz VPR yalnız bir uyğun dəyəri qaytara bilər, daha dəqiq desək, tapılan birinci. Bəs bu dəyər baxılan massivdə bir neçə dəfə təkrarlansa və siz onlardan 2-ci və ya 3-cü hissəsini çıxarmaq istəsəniz necə olar? Bəs bütün dəyərlər olarsa? Problem mürəkkəb görünür, amma həll yolu var!
Tutaq ki, cədvəlin bir sütununda müştərilərin adları (Müştəri adı), digər sütununda isə onların satın aldığı məhsullar (Məhsul) var. Verilmiş müştərinin aldığı 2-ci, 3-cü və 4-cü əşyaları tapmağa çalışaq.
Ən asan yol sütundan əvvəl köməkçi sütun əlavə etməkdir Müştəri adı və hər adın təkrar nömrəsi ilə müştəri adları ilə doldurun, məsələn, John Doe1, John Doe2 və s. Biz funksiyadan istifadə edərək nömrələmə ilə hiylə edəcəyik COUNTIF (COUNTIF), müştəri adlarının B sütununda olduğunu nəzərə alaraq:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Bundan sonra normal funksiyadan istifadə edə bilərsiniz VPRtələb olunan sifarişi tapmaq üçün. Misal üçün:
- SİZİN ÜÇÜN MÜKEMMEL YERİ TAPIN 2-ci müştərinin sifariş etdiyi məhsul Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - SİZİN ÜÇÜN MÜKEMMEL YERİ TAPIN 3-ci müştərinin sifariş etdiyi məhsul Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Əslində, aşağıdakı şəkildə göstərildiyi kimi mətn əvəzinə axtarış dəyəri kimi xana arayışı daxil edə bilərsiniz:
Yalnız axtarırsınızsa 2-e təkrarlama, daha mürəkkəb bir düstur yaradaraq bunu köməkçi sütun olmadan edə bilərsiniz:
=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;ИСТИНА);"")
Bu formulada:
- $F$2 – alıcının adının olduğu xana (dəyişməmişdir, lütfən qeyd edin – keçid mütləqdir);
- $ B $ – sütun Müştəri adı;
- Table4 – Masanız (bu yer adi bir sıra ola bilər);
- 16 dollar – cədvəlinizin və ya diapazonunuzun son xanası.
Bu düstur yalnız ikinci uyğun dəyəri tapır. Qalan təkrarları çıxarmaq lazımdırsa, əvvəlki həlli istifadə edin.
Bütün uyğunluqların siyahısına ehtiyacınız varsa - funksiya VPR bu köməkçi deyil, çünki o, bir anda yalnız bir dəyər qaytarır - dövr. Ancaq Excel-in bir funksiyası var İNDEKSİ (INDEX), bu vəzifənin öhdəsindən asanlıqla gələ bilər. Belə bir formulun necə görünəcəyini aşağıdakı nümunədə öyrənəcəksiniz.
İstədiyiniz dəyərin bütün təkrarlarını əldə edin
Yuxarıda qeyd edildiyi kimi VPR skan edilmiş diapazondan bütün dublikat dəyərləri çıxara bilməz. Bunu etmək üçün bir neçə Excel funksiyasından ibarət bir az daha mürəkkəb düstur lazımdır, məsələn İNDEKSİ (İNDEKS), SMALL (Kiçik) və SIRA (Xətt)
Məsələn, aşağıdakı düstur B2:B2 diapazonunda F16 xanasındakı dəyərin bütün təkrarlarını tapır və C sütununda eyni sətirlərin nəticəsini qaytarır.
{=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));"")}
Bu massiv düsturunu xanalar kimi çoxlu bitişik xanalara daxil edin F4: F8aşağıdakı şəkildə göstərildiyi kimi. Hüceyrələrin sayı axtarılan dəyərin maksimum mümkün təkrar sayına bərabər və ya ondan çox olmalıdır. Klik etməyi unutmayın Ctrl + Shift + Entermassiv düsturunu düzgün daxil etmək üçün.
Bunun necə işlədiyini başa düşmək istəyirsinizsə, düsturun təfərrüatlarına bir az dalaşaq:
Part 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – F2 xanasındakı dəyəri B2:B16 diapazonundakı dəyərlərin hər biri ilə müqayisə edin. Uyğunluq tapılarsa, ifadə STRING(C2:C16)-1 müvafiq sətrin nömrəsini qaytarır (dəyər -1 başlıq xəttini daxil etməməyə imkan verir). Uyğunluqlar yoxdursa, funksiya IF (IF) boş sətir qaytarır.
Funksiya nəticəsi IF (Əgər) belə bir üfüqi massiv olacaq: {1,"",3,"",5,"","","","","","",12,"","",""}
Part 2:
ROW()-3
СТРОКА()-3
Burada funksiya SIRA (LINE) əlavə sayğac kimi çıxış edir. Düstur F4:F9 xanalarına köçürüldüyü üçün rəqəmi çıxarırıq 3 dəyər əldə etmək üçün funksiya nəticəsində 1 hüceyrədə F4 (sətir 4, çıxın 3) almaq 2 hüceyrədə F5 (sətir 5, çıxın 3) və s.
Part 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Function SMALL (KÜÇÜK) qaytarır yox verilənlər massivindəki ən kiçik dəyər. Bizim vəziyyətimizdə hansı mövqenin (ən kiçikdən) qayıtması funksiya ilə müəyyən edilir SIRA (SƏT) (2-ci hissəyə baxın). Beləliklə, bir hüceyrə üçün F4 funksiyası KİÇİ({massiv},1) Yekunları 1-ci (ən kiçik) massiv elementi, yəni 1. Hüceyrə üçün F5 Yekunları 2-ci massivdəki ən kiçik element, yəni 3Və sairə
Part 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))
Function İNDEKSİ (INDEX) sadəcə massivdəki xüsusi xananın dəyərini qaytarır C2:C16. Hüceyrə üçün F4 funksiyası İNDEKS($C$2:$C$16) geri dönəcək ApplesÜçün F5 funksiyası İNDEKS($C$2:$C$16) geri dönəcək şirniyyat və s.
Part 5:
IFERROR()
ЕСЛИОШИБКА()
Nəhayət, düsturu funksiyanın içərisinə qoyuruq XƏTA (IFERROR), çünki səhv mesajından məmnun olma ehtimalınız yoxdur #AT (#N/A) formulun kopyalandığı xanaların sayı baxılan diapazondakı dublikat dəyərlərin sayından azdırsa.
Məlum sətir və sütun üzrə XNUMXD axtarış
Excel-də XNUMXD axtarışının yerinə yetirilməsi məlum sətir və sütun nömrəsi ilə dəyərin axtarışını əhatə edir. Başqa sözlə, siz müəyyən bir sıra və sütunun kəsişməsində xana dəyərini çıxarırsınız.
Beləliklə, cədvəlimizə müraciət edək və funksiyası olan bir düstur yazaq VPR, mart ayında satılan limonların qiyməti ilə bağlı məlumat tapacaq.
XNUMXD axtarışını yerinə yetirməyin bir neçə yolu var. Seçimləri yoxlayın və sizə ən uyğun olanı seçin.
VLOOKUP və MATCH funksiyaları
Bir sıra funksiyalardan istifadə edə bilərsiniz VPR (VLOOKUP) və DAHA FAZLA (MATCH) sahələrin kəsişməsindəki dəyəri tapmaq üçün Название продукта (simli) və ay sözügedən massivin (sütun):
=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);ЛОЖЬ)
Yuxarıdakı düstur müntəzəm funksiyadır VPR, A2-dən A9-a qədər xanalardakı “Limonlar” dəyərinin dəqiq uyğunluğunu axtarır. Lakin siz mart satışlarının hansı sütunda olduğunu bilmədiyiniz üçün üçüncü funksiya arqumenti üçün sütun nömrəsini təyin edə bilməyəcəksiniz. VPR. Bunun əvəzinə funksiya istifadə olunur DAHA FAZLAbu sütunu müəyyən etmək üçün.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
İnsan dilinə tərcümə edilən bu düstur:
- Biz “Mar” – mübahisə personajlarını axtarırıq lookup_value (axtarış_dəyəri);
- A1-dən I1-ə qədər xanalara baxmaq – arqument axtarış_massivi (axtarış_massivi);
- Dəqiq uyğunluğun qaytarılması – arqument uyğunluq_növü (uyğun_növ).
Istifadə 0 üçüncü arqumentdə funksiyaları deyirsiniz DAHA FAZLA axtardığınız dəyərə tam uyğun gələn ilk dəyəri axtarın. Bu dəyərə bərabərdir SAXTA (YANLIŞ) dördüncü arqument üçün VPR.
Excel-də ikiölçülü axtarış və ya iki yönlü axtarış kimi də tanınan ikitərəfli axtarış formulunu belə yarada bilərsiniz.
SUMPRODUCT funksiyası
Function SUMPRODUCT (SUMPRODUCT) seçilmiş massivlərin məhsullarının cəmini qaytarır:
=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 və MATCH funksiyaları
Növbəti məqalədə mən bu funksiyaları ətraflı izah edəcəyəm, buna görə də indi bu düsturu kopyalaya bilərsiniz:
=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))
Adlandırılmış diapazonlar və kəsişmə operatoru
Bütün bu mürəkkəb Excel düsturları ilə tanış deyilsinizsə, bu vizual və yaddaqalan üsulu bəyənə bilərsiniz:
- Cədvəl seçin, nişanı açın Formüller (Formullar) və klikləyin Seçimdən yaradın (Seçimdən yaradın).
- Qutuları yoxlayın Üst sıra (yuxarıdakı sətirdə) və Sol sütun (soldakı sütunda). Microsoft Excel cədvəlinizin yuxarı cərgəsində və sol sütunundakı dəyərlərdən aralıqlara adlar təyin edəcək. İndi düsturlar yaratmadan birbaşa bu adlardan istifadə edərək axtarış edə bilərsiniz.
- İstənilən boş hücrəyə yazın =sətir_adı sütun_adıməsələn, bu kimi:
=Limonlar Mart
... və ya əksinə:
=Mar Limonlar
Unutmayın ki, sətir və sütun adları boşluqla ayrılmalıdır, bu halda kəsişmə operatoru kimi işləyir.
Siz ad daxil etdiyiniz zaman, Microsoft Excel, düstur daxil etdiyiniz zaman olduğu kimi, uyğun adların siyahısı ilə alət ipucunu göstərəcək.
- mətbuat Daxil edin və nəticəni yoxlayın
Ümumiyyətlə, yuxarıda göstərilən üsullardan hansını seçsəniz, iki ölçülü axtarışın nəticəsi eyni olacaq:
Bir düsturda çoxlu VLOOKUP-dan istifadə
Belə olur ki, əsas cədvəllə axtarış cədvəlinin ortaq bir sütunu yoxdur və bu, adi funksiyadan istifadə etməyinizə mane olur. VPR. Bununla belə, bizi maraqlandıran məlumatları ehtiva etməyən, lakin əsas cədvəl və axtarış cədvəli ilə ümumi sütunu olan başqa bir cədvəl var.
Gəlin aşağıdakı nümunəyə nəzər salaq. Sütunlu Əsas cədvəlimiz var SKU (yeni), başqa cədvəldən müvafiq qiymətləri olan bir sütun əlavə etmək istədiyiniz yerə. Bundan əlavə, bizdə 2 axtarış cədvəli var. Birincisi (Cədvəl 1) yenilənmiş nömrələri ehtiva edir SKU (yeni) və məhsul adları, ikincisi (Cədvəl 2 axtarışı) – məhsul adları və köhnə nömrələr SKU (köhnə).
İkinci axtarış cədvəlindən qiymətləri əsas cədvələ əlavə etmək üçün siz double kimi tanınan hərəkəti yerinə yetirməlisiniz VPR və ya yuvalanmış VPR.
- Funksiya yazın VPR, cədvəldə məhsulun adını tapan Axtarış cədvəli 1istifadə SKU, istədiyiniz dəyər kimi:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Burada Yeni_SKU - adlı diapazon $A:$B cədvəldə Axtarış cədvəli 1, 2 – bu, malların adlarını ehtiva edən B sütunudur (yuxarıdakı şəklə baxın)
- Cədvəldən qiymətləri daxil etmək üçün düstur yazın Axtarış cədvəli 2 tanınmış məhsul adlarına əsaslanır. Bunu etmək üçün əvvəllər yaratdığınız düsturu yeni funksiya üçün axtarış dəyəri kimi yapışdırın VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Burada qiymət - adlı diapazon $A:$C cədvəldə Axtarış cədvəli 2, 3 qiymətləri ehtiva edən C sütunudur.
Aşağıdakı şəkildə yaratdığımız düsturla qaytarılan nəticə göstərilir:
VLOOKUP və DOLAYI istifadə edərək müxtəlif cədvəllərdən verilənlərin dinamik dəyişdirilməsi
Əvvəlcə gəlin bir-birimizi düzgün başa düşməyimizə əmin olmaq üçün “Müxtəlif cədvəllərdən verilənlərin dinamik əvəzlənməsi” ifadəsi ilə nə demək istədiyimizi aydınlaşdıraq.
Eyni formatda verilənləri olan bir neçə vərəq olduqda vəziyyətlər var və müəyyən bir hüceyrəyə daxil edilən dəyərdən asılı olaraq müəyyən bir vərəqdən lazımi məlumatları çıxarmaq lazımdır. Məncə bunu bir misalla izah etmək daha asandır.
Təsəvvür edin ki, eyni məhsullara və eyni formata malik bir neçə region üzrə satış hesabatlarınız var. Müəyyən bir bölgə üçün satış rəqəmlərini tapmaq istəyirsiniz:
Yalnız iki belə hesabatınız varsa, o zaman funksiyaları olan biabırçı sadə düsturdan istifadə edə bilərsiniz VPR и IF (IF) axtarış etmək istədiyiniz hesabatı seçmək üçün:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Harada:
- $D$2 məhsulun adını ehtiva edən xanadır. Nəzərə alın ki, formulanı digər xanalara köçürərkən axtarış dəyərini dəyişməmək üçün burada mütləq istinadlardan istifadə edirik.
- $ D3 bölgənin adını daşıyan xanadır. Biz mütləq sütun istinadından və nisbi sıra istinadından istifadə edirik, çünki düsturu eyni sütunun digər xanalarına köçürməyi planlaşdırırıq.
- FL_Sales и CA_Satış – müvafiq satış hesabatlarını ehtiva edən cədvəllərin (və ya adlandırılmış diapazonların) adları. Siz, əlbəttə ki, məsələn, adi vərəq adlarından və hüceyrə diapazonu istinadlarından istifadə edə bilərsiniz 'FL Vərəqi'!$A$3:$B$10, lakin adlandırılmış diapazonlar daha rahatdır.
Bununla belə, belə cədvəllər çox olduqda, funksiya IF ən yaxşı həll yolu deyil. Bunun əvəzinə funksiyadan istifadə edə bilərsiniz DOLAYI (DOLAYI) istədiyiniz axtarış diapazonunu qaytarmaq üçün.
Yəqin ki, bildiyiniz kimi, funksiya DOLAYI mətn sətri ilə verilən keçidi qaytarmaq üçün istifadə olunur ki, bu da indi bizə lazım olan şeydir. Beləliklə, yuxarıdakı düsturdakı ifadəni cəsarətlə funksiya ilə əvəz edin IF funksiyası ilə əlaqələndirmək DOLAYI. Budur bir birləşmə VPR и DOLAYI ilə əla işləyir:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Harada:
- $D$2 – bu məhsulun adı olan xanadır, mütləq əlaqəyə görə dəyişməzdir.
- $ D3 bölgə adının birinci hissəsini ehtiva edən xanadır. Bizim nümunəmizdə bu FL.
- _Satış – bütün adlandırılmış diapazonların və ya cədvəllərin adının ümumi hissəsi. D3 xanasındakı dəyərlə birləşdirildikdə, tələb olunan diapazonun tam təyin olunmuş adını təşkil edir. Aşağıda funksiyaya yeni başlayanlar üçün bəzi təfərrüatlar verilmişdir DOLAYI.
DOLAYI və VLOOKUP necə işləyir
Əvvəlcə funksiyanın sintaksisini xatırlatmaq istərdim DOLAYI (DOLAYI):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Birinci arqument xana arayışı (A1 və ya R1C1 üslubu), diapazon adı və ya mətn sətri ola bilər. İkinci arqument birinci arqumentdə hansı keçid üslubunun olduğunu müəyyənləşdirir:
- A1arqument olarsa GERÇEK KOD (DOĞRU) və ya göstərilməyib;
- R1C1Əgər FAS E (YANLIŞ).
Bizim vəziyyətimizdə linkin üslubu var A1, belə ki, ikinci arqumenti buraxıb birinciyə diqqət yetirə bilərsiniz.
Beləliklə, satış hesabatlarımıza qayıdaq. Xatırlayırsınızsa, onda hər bir hesabat ayrı bir vərəqdə yerləşən ayrı bir cədvəldir. Düsturun düzgün işləməsi üçün cədvəllərinizi (və ya diapazonlarınızı) adlandırmalısınız və bütün adların ümumi hissəsi olmalıdır. Məsələn, bu kimi: CA_Satış, FL_Satış, TX_Satış və sair. Gördüyünüz kimi “_Satış” bütün adlarda mövcuddur.
Function DOLAYI D sütunundakı dəyəri və “_Sales” mətn sətirini birləşdirir, bununla da bildirir VPR hansı cədvəldə axtarmaq lazımdır. D3 xanasında “FL” dəyəri varsa, düstur cədvəldə axtarış aparacaq FL_Satış, əgər “CA” – cədvəldə CA_Satış və s.
Funksiyaların nəticəsi VPR и DOLAYI aşağıdakılar olacaq:
Məlumatlar müxtəlif Excel kitablarında yerləşirsə, kitabın adını qeyd olunan diapazondan əvvəl əlavə etməlisiniz, məsələn:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Əgər funksiyası DOLAYI başqa iş kitabına istinad edir, həmin iş dəftəri açıq olmalıdır. Bağlıdırsa, funksiya xəta haqqında məlumat verəcəkdir. #REF! (#SSYL!).