Mündəricat
Bu kiçik dərslik funksiyanı necə düzəltməyi izah edir VPR (VLOOKUP) hərflərə həssasdır, Excel-in hərflərə həssas şəkildə axtara biləcəyi bir neçə digər düsturları göstərir və hər bir funksiyanın güclü və zəif tərəflərini göstərir.
Güman edirəm ki, hər bir Excel istifadəçisi şaquli axtarışın hansı funksiyanı yerinə yetirdiyini bilir. Düzdür, bu bir funksiyadır VPR. Ancaq bunu az adam bilir VPR böyük hərflərə həssas deyil, yəni kiçik və böyük hərf simvolları onun üçün eynidir.
Budur, bacarıqsızlığı göstərən sürətli bir nümunə VPR reyestri tanımaq. Tutaq ki, bir hüceyrədə A1 "hesab" dəyərini və xananı ehtiva edir A2 – “Bill”, düstur:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… “hesab” üzrə axtarışını dayandıracaq, çünki bu dəyər siyahıda birinci yerdədir və xanadan dəyəri çıxarır. B1.
Daha sonra bu məqalədə sizə necə edəcəyinizi göstərəcəyəm VPR hərflərə həssasdır. Bundan əlavə, Excel-də hərflərə həssas axtarışları yerinə yetirə bilən daha bir neçə funksiyanı öyrənəcəyik.
Ən sadədən başlayacağıq - VIEW (LOOKUP) və SUMPRODUCT (SUMPRODUCT), təəssüf ki, bir sıra əhəmiyyətli məhdudiyyətlərə malikdir. Sonra, bir az daha mürəkkəb formulla daha yaxından tanış olacağıq INDEX+MATCH (INDEX+MATCH), istənilən vəziyyətdə və istənilən verilənlər bazası ilə qüsursuz işləyir.
VLOOKUP funksiyası hərflərə həssasdır
Artıq bildiyiniz kimi, adi funksiya VPR böyük hərflərə həssasdır. Bununla belə, hərflərə həssas etmək üçün bir yol var. Bunun üçün aşağıdakı nümunədə göstərildiyi kimi cədvələ köməkçi sütun əlavə etmək lazımdır.
Tutaq ki, bir sütunda B məhsul identifikatorları (Element) var və siz məhsulun qiymətini və müvafiq şərhi sütunlardan çıxarmaq istəyirsiniz C и D. Problem ondadır ki, identifikatorlar həm kiçik, həm də böyük hərflərdən ibarətdir. Məsələn, hüceyrə dəyərləri B4 (001Tvci3u) və B5 (001Tvci3U) yalnız sonuncu simvol halında fərqlənir, u и U müvafiq.
Təsəvvür edə bildiyiniz kimi, adi axtarış düsturu
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
geri dönəcək $ 90, dəyərindən bəri 001Tvci3u daha əvvəl axtarış diapazonundadır 001Tvci3U. Amma bizə lazım olan bu deyil, elə deyilmi?
Funksiya ilə axtarış etmək VPR Excel hərfinə həssas olaraq, köməkçi sütun əlavə etməli və onun xanalarını aşağıdakı düsturla doldurmalı olacaqsınız (burada B axtarış sütunudur):
=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));"")
Bu düstur istənilən dəyəri ayrı simvollara bölür, hər simvolu öz kodu ilə əvəz edir (məsələn, əvəzinə A əvəzinə 65-də a kodu 97) və sonra bu kodları unikal nömrələr sətirində birləşdirir.
Bundan sonra sadə bir funksiyadan istifadə edirik VPR hərflərə həssas axtarış üçün:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Funksiyanın düzgün işləməsi VPR hərflərə həssaslıq iki amildən asılıdır:
- Köməkçi sütun görünən diapazonda ən sol sütun olmalıdır.
- Axtardığınız dəyər real dəyər əvəzinə simvol kodunu ehtiva etməlidir.
KOD funksiyasından necə düzgün istifadə etmək olar
Köməkçi sütunun xanalarına daxil edilmiş düstur bütün axtarış dəyərlərinizin eyni sayda simvola malik olduğunu nəzərdə tutur. Əgər belə deyilsə, onda siz ən kiçik və ən böyük rəqəmləri bilməli və bir o qədər çox funksiya əlavə etməlisiniz XƏTA (IFERROR) ən qısa və ən uzun axtarılan dəyər arasındakı fərq neçə simvoldur.
Məsələn, ən qısa axtarış dəyəri 3 simvol və ən uzunu 5 simvoldursa, bu düsturdan istifadə edin:
=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));"")
Funksiya üçün PSTR (MID) Siz aşağıdakı arqumentləri təqdim edirsiniz:
- 1-ci arqument - mətn (mətn) çıxarılacaq simvolları ehtiva edən mətn və ya xana istinadıdır (bizim vəziyyətimizdə B2-dir)
- 2-ci arqument - başlanğıc_num (başlanğıc_mövqeyi) çıxarılacaq simvollardan birincisinin mövqeyidir. girirsən 1 birinci funksiyada PSTR, 2 – ikinci funksiyada PSTR s.
- 3-ci arqument - simvol_sayı (simvolların_sayı) – Mətndən çıxarılacaq simvolların sayını təyin edir. Bizə hər zaman yalnız 1 simvol lazım olduğundan, bütün funksiyalarda yazırıq 1.
Məhdudiyyətlər: Function VPR Excel-də hərflərə həssas axtarışlar üçün ən yaxşı həll yolu deyil. Birincisi, köməkçi sütunun əlavə edilməsi tələb olunur. İkincisi, düstur yalnız məlumatlar homojen olduqda və ya axtarılan dəyərlərdəki simvolların dəqiq sayı məlum olduqda yaxşı bir iş görür. Bu sizin halınız deyilsə, aşağıda göstərdiyimiz həllərdən birini istifadə etmək daha yaxşıdır.
Hərflərə həssas axtarış üçün LOOKUP funksiyası
Function VIEW (LOOKUP) ilə əlaqədardır VPR, lakin onun sintaksisi köməkçi sütun əlavə etmədən hərf-həssas axtarışlara imkan verir. Bunu etmək üçün istifadə edin VIEW funksiyası ilə birləşdirilir EXACT (DƏqiq).
Əvvəlki nümunədən məlumatları götürsək (köməkçi sütun olmadan), onda aşağıdakı düstur tapşırığın öhdəsindən gələcək:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Aralıqda formula axtarışları A2: A7 hüceyrə dəyəri ilə dəqiq uyğunluq F2 böyük hərflərə həssasdır və eyni sətrin B sütunundan dəyəri qaytarır.
Kimi VPRfunksiyası VIEW Aşağıdakı ekran görüntüsündə gördüyünüz kimi mətn və rəqəmli dəyərlərlə bərabər işləyir:
Əhəmiyyətli! Funksiya üçün VIEW düzgün işləndikdə, axtarış sütunundakı dəyərlər artan qaydada, yəni kiçikdən böyüyə doğru sıralanmalıdır.
Funksiyanın necə işlədiyini qısaca izah edim EXACT yuxarıda göstərilən düsturda, çünki bu əsas məqamdır.
Function EXACT 1-ci və 2-ci arqumentlərdəki iki mətn dəyərini müqayisə edir və onlar tam eynidirsə TRUE, deyilsə FALSE qaytarır. Funksiya bizim üçün vacibdir EXACT hərflərə həssasdır.
Düsturumuzun necə işlədiyini görək GÖRÜN + DƏqiq:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Function EXACT hüceyrə dəyərini müqayisə edir F2 sütundakı bütün elementlərlə A (A2:A7). Dəqiq uyğunluq aşkar edilərsə TRUE, əks halda YANLIŞ qaytarır.
- Birinci funksiya arqumentini verdiyiniz üçün VIEW dəyər TRUE, o, göstərilən sütundan müvafiq dəyəri çıxarır (bizim vəziyyətimizdə B sütunu) yalnız dəqiq uyğunluq aşkar edildikdə, hərflərə həssasdır.
Ümid edirəm bu izahat aydın oldu və indi siz əsas fikri başa düşürsünüz. Əgər belədirsə, onda siz daha sonra təhlil edəcəyimiz digər funksiyalarla heç bir çətinlik çəkməyəcəksiniz, çünki. hamısı eyni prinsip üzərində işləyir.
Məhdudiyyətlər: Axtarış sütunundakı məlumatlar artan qaydada çeşidlənməlidir.
XÜSUSİYYƏT – mətn dəyərlərini tapır, hərflərə həssasdır, lakin yalnız rəqəmləri qaytarır
Artıq başlıqdan başa düşdüyünüz kimi, SUMPRODUCT (SUMPRODUCT) başqa bir Excel funksiyasıdır ki, sizə hərf-həssas axtarış aparmağa kömək edəcək, lakin yalnız rəqəmli dəyərləri qaytaracaq. Bu seçim sizə uyğun gəlmirsə, dərhal paketə keçə bilərsiniz INDEX+MATCH, hər hansı bir hal və hər hansı bir məlumat növü üçün bir həll verir.
Əvvəla, icazə verin, bu funksiyanın sintaksisini qısaca izah edim, bu, aşağıdakı böyük hərflərə həssas düsturun daha yaxşı başa düşülməsinə kömək edəcək.
Function SUMPRODUCT verilmiş massivlərin elementlərini çoxaldır və nəticələrin cəmini qaytarır. Sintaksis belə görünür:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Hərfi-həssas axtarışa ehtiyacımız olduğundan, funksiyadan istifadə edirik EXACT (EXACT) çarpanlardan biri kimi əvvəlki nümunədən:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Xatırladığınız kimi EXACT hüceyrə dəyərini müqayisə edir F2 sütundakı bütün elementlərlə A. Dəqiq uyğunluq aşkar edilərsə TRUE, əks halda YANLIŞ qaytarır. Riyazi əməliyyatlarda Excel TRUE kimi qəbul edir 1, və FALSE üçün 0Əlavə SUMPRODUCT bu ədədləri çoxaldır və nəticələri cəmləyir.
Sıfırlar sayılmır, çünki çoxaldıqda həmişə verirlər 0. Bir sütunda dəqiq uyğunluq olduqda nə baş verdiyinə daha yaxından nəzər salaq A tapıb geri qaytardı 1… Funksiya SUMPRODUCT sütundakı rəqəmi çoxaldır B on 1 və nəticəni qaytarır - tam olaraq eyni nömrə! Bunun səbəbi, digər məhsulların nəticələrinin sıfır olması və nəticədə əldə edilən məbləğə təsir göstərməməsidir.
Təəssüf ki, funksiya SUMPRODUCT mətn dəyərləri və tarixləri ilə işləyə bilməz, çünki onları çoxaltmaq mümkün deyil. Bu halda, bir səhv mesajı alacaqsınız #VALUE! (#VALUE!) xanadakı kimi F4 aşağıdakı şəkildə:
Məhdudiyyətlər: Yalnız ədədi dəyərləri qaytarır.
INDEX + MATCH – hər hansı bir məlumat növü üçün hərflərə həssas axtarış
Nəhayət, hər hansı bir məlumat dəsti ilə işləyən limitsiz və hərflərə həssas axtarış formuluna yaxınıq.
Bu misal ən yaxşısının desert üçün qaldığı üçün deyil, əvvəlki nümunələrdən əldə edilən biliklərin böyük hərflərə həssas düsturu daha yaxşı və daha tez başa düşməyinizə kömək edəcəyi üçün sonuncu olur. INDEX+MATCH (INDEX+MATCH).
Yəqin ki, təxmin etdiyiniz kimi, funksiyaların birləşməsi DAHA FAZLA и İNDEKSİ üçün daha çevik və güclü alternativ kimi Excel-də istifadə olunur VPR. VLOOKUP əvəzinə INDEX və MATCH istifadə məqaləsi bu funksiyaların birlikdə necə işlədiyini mükəmməl şəkildə izah edəcək.
Mən yalnız əsas məqamları təkrarlayacağam:
- Function DAHA FAZLA (MATCH) verilmiş diapazonda dəyəri axtarır və onun nisbi mövqeyini, yəni sətir və/yaxud sütun nömrəsini qaytarır;
- Sonrakı, funksiya İNDEKSİ (INDEX) müəyyən edilmiş sütun və/və ya sətirdən dəyəri qaytarır.
Formula üçün INDEX+MATCH böyük hərflərlə axtarış edə bilər, ona yalnız bir funksiya əlavə etməlisiniz. Yenə nə olduğunu təxmin etmək çətin deyil EXACT (DƏqiq):
=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))
Bu formulada EXACT funksiyası ilə birlikdə olduğu kimi işləyir VIEW, və eyni nəticəni verir:
Qeyd edək ki, formula INDEX+MATCH buruq mötərizələrə daxil edilmiş massiv düsturudur və siz onu basaraq tamamlamalısınız Ctrl + Shift + Enter.
Nə üçün INDEX+MATCH hərf-həssas axtarış üçün ən yaxşı həll yoludur?
Paketin əsas üstünlükləri İNDEKSİ и DAHA FAZLA:
- Fərqli olaraq köməkçi sütunun əlavə edilməsini tələb etmir VPR.
- Fərqli olaraq axtarış sütununun çeşidlənməsini tələb etmir VIEW.
- Bütün növ məlumatlarla işləyir - nömrələr, mətn və tarixlər.
Bu formula mükəmməl görünür, elə deyilmi? Əslində elə deyil. Və buna görə.
Tutaq ki, axtarış dəyəri ilə əlaqəli qaytarılan dəyər sütununda xana boşdur. Formula hansı nəticəni qaytaracaq? Yox? Düsturun əslində nə qaytardığını görək:
Ups, düstur sıfır qaytarır! Təmiz mətn dəyərləri ilə işləyirsinizsə, bu böyük problem olmaya bilər. Bununla belə, cədvəldə “real” sıfırlar da daxil olmaqla nömrələr varsa, bu problemə çevrilir.
Əslində, əvvəllər müzakirə etdiyimiz bütün digər axtarış düsturları (VLOOKUP, LOOKUP və SUMPRODUCT) eyni şəkildə davranır. Amma mükəmməl formul istəyirsən, elə deyilmi?
Düsturun hərfi həssas etmək üçün INDEX+MATCH mükəmməl, onu funksiyaya qoyun IF (IF) qaytarılan dəyəri olan bir xananı sınaqdan keçirəcək və boş olduqda boş nəticə verəcəkdir:
=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));"")
Bu formulada:
- B qaytarılan dəyərləri olan sütundur
- 1+ funksiyanın qaytardığı xananın nisbi mövqeyini çevirən ədəddir DAHA FAZLA, hüceyrənin həqiqi ünvanına. Məsələn, bizim funksiyamızda DAHA FAZLA axtarış massivi verilmişdir A2: A7, yəni hüceyrənin nisbi mövqeyi A2 iradə 1, çünki massivdə birincidir. Ancaq hüceyrənin həqiqi mövqeyi A2 sütundadır 2, buna görə də əlavə edirik 1fərqi aradan qaldırmaq və funksiyaya sahib olmaq DOLAYI (DOLAYI) istədiyiniz xanadan dəyəri əldə etdi.
Aşağıdakı şəkillər düzəldilmiş böyük hərflərə həssas düsturu göstərir INDEX+MATCH Fəaliyyətdə. Qaytarılan xana boş olarsa, boş nəticə qaytarır.
Formulu yenidən sütunlara yazdım B:Ddüstur çubuğunu ekran görüntüsünə uyğunlaşdırmaq üçün.
Formula qayıdır 0qaytarılmış xanada sıfır varsa.
İstəsəniz link İNDEKSİ и DAHA FAZLA Qayıdış dəyəri boş olduqda bəzi mesaj göstərilir, onu düsturun son dırnaqlarına (“”) yaza bilərsiniz, məsələn:
=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.")
