Excel VLOOKUP funksiyasının bütün sirləri cədvəldə məlumatları tapmaq və digərinə çıxarmaq üçün

2024 Müəllif: Malcolm Clapton | [email protected]. Son dəyişdirildi: 2023-12-17 03:47

Məqaləni oxuduqdan sonra siz nəinki Excel cədvəlində verilənləri tapıb digərinə çıxarmağı öyrənməyəcəksiniz, həm də VLOOKUP funksiyası ilə birlikdə istifadə edilə bilən üsulları öyrənəcəksiniz.

Excel-də işləyərkən çox vaxt məlumatları bir cədvəldə tapmaq və digərinə çıxarmaq ehtiyacı var. Bunu necə edəcəyinizi hələ də bilmirsinizsə, məqaləni oxuduqdan sonra yalnız bunu necə edəcəyinizi öyrənməyəcəksiniz, həm də hansı şərtlərdə sistemdən maksimum performansı sıxışdıra biləcəyinizi öyrənəcəksiniz. VLOOKUP funksiyası ilə birlikdə istifadə edilməli olan çox təsirli üsulların əksəriyyəti nəzərdən keçirilir.

İllərdir VLOOKUP funksiyasından istifadə etsəniz belə, bu məqalə sizin üçün faydalı olacaq və sizi laqeyd qoymayacaqdır. Məsələn, İT mütəxəssisi, sonra isə İT üzrə rəhbər olduğum üçün 15 ildir ki, VLOOKUP-dan istifadə edirəm, ancaq indi, insanlara Excel proqramını peşəkar şəkildə öyrətməyə başlayanda bütün nüansların öhdəsindən gələ bildim.

VLOOKUP üçün abreviaturadır v şaquli NSmüayinə. Eynilə VLOOKUP - Şaquli AXTAR. Funksiyanın adı bizə onun sütunlarda deyil (üfüqi - sütunlar üzərində iterasiya və cərgəni düzəltmək) cədvəlin sətirlərində axtarış etdiyini (şaquli - sətirlər üzərində təkrarlamaq və sütunu düzəltmək) olduğunu göstərir. Qeyd etmək lazımdır ki, VLOOKUP-un bir bacısı var - heç vaxt qu quşu olmayacaq çirkin ördək balası - bu HLOOKUP funksiyasıdır. HLOOKUP, VLOOKUP-dan fərqli olaraq, üfüqi axtarışları yerinə yetirir, lakin Excel konsepsiyası (və əslində məlumatların təşkili konsepsiyası) cədvəllərinizdə daha az sütun və daha çox sətir olduğunu nəzərdə tutur. Odur ki, biz sütunlara deyil, sətirlərə görə dəfələrlə axtarış aparmalıyıq. Excel-də HLO funksiyasından çox istifadə edirsinizsə, çox güman ki, bu həyatda nəyisə başa düşməmisiniz.

Sintaksis

VLOOKUP funksiyası dörd parametrə malikdir:

= VLOOKUP (;; [;]), burada:

- istədiyiniz dəyər (nadir hallarda) və ya istədiyiniz dəyəri ehtiva edən hüceyrəyə istinad (halların böyük əksəriyyəti);

- İLK (!) sütununda parametr dəyərinin axtarılacağı xanalar diapazonuna (iki ölçülü massiv) istinad;

- dəyərin qaytarılacağı diapazonda sütun nömrəsi;

- bu, diapazonun birinci sütununun artan qaydada çeşidləndiyi sualına cavab verən çox vacib parametrdir. Massiv çeşidlənibsə, biz TRUE və ya 1 dəyərini təyin edirik, əks halda - FALSE və ya 0. Bu parametr buraxılıbsa, o, standart olaraq 1-dir.

Əminəm ki, VLOOKUP funksiyasını ləpə kimi bilənlərin çoxu dördüncü parametrin təsvirini oxuduqdan sonra özlərini narahat hiss edə bilərlər, çünki onlar bunu bir az fərqli formada görməyə öyrəşiblər: adətən onlar nə vaxt dəqiq uyğunluqdan danışırlar. axtarış (YANLIŞ və ya 0) və ya diapazon skanı (DOĞRU və ya 1).

İndi siz sona qədər deyilənlərin mənasını hiss edənə qədər növbəti abzası bir neçə dəfə gərginləşdirməli və oxumalısınız. Orada hər bir söz önəmlidir. Nümunələr bunu anlamağa kömək edəcək.

VLOOKUP düsturu tam olaraq necə işləyir?

• Formula növü I. Əgər sonuncu parametr buraxılıbsa və ya 1-ə bərabər göstərilibsə, VLOOKUP birinci sütunun artan qaydada çeşidləndiyini güman edir, ona görə də axtarış bu sətirdə dayanır. dərhal istədiyinizdən böyük dəyəri olan sətirdən əvvəl gəlir … Əgər belə sətir tapılmazsa, diapazonun sonuncu sətri qaytarılır.

  

• Formula II. Əgər sonuncu parametr 0 kimi göstərilibsə, VLOOKUP ardıcıl olaraq massivin ilk sütununu skan edir və parametrlə ilk dəqiq uyğunluq tapıldıqda axtarışı dərhal dayandırır, əks halda # N / A (# N / A) səhv kodu qaytarılır.

  

Formulaların iş axını

VPR növü I

II tip VPR

Forma I formulları üçün nəticələr

1. Düsturlar dəyərləri diapazonlar arasında yaymaq üçün istifadə edilə bilər.
2. Birinci sütunda dublikat dəyərlər varsa və düzgün çeşidlənibsə, dublikat dəyərləri olan sətirlərin sonuncusu qaytarılacaq.
3. Əgər siz ilk sütunun ehtiva edə biləcəyindən açıq-aydın böyük olan bir dəyər axtarırsınızsa, o zaman cədvəlin son sətirini asanlıqla tapa bilərsiniz, bu da olduqca qiymətli ola bilər.
4. Bu görünüş # N / A səhvini yalnız istədiyinizdən az və ya ona bərabər bir dəyər tapmadıqda qaytaracaq.
5. Massiviniz sıralanmadıqda düsturun səhv dəyərləri qaytardığını başa düşmək olduqca çətindir.

II növ düsturlar üçün nəticələr

İstədiyiniz dəyər massivin birinci sütununda bir neçə dəfə baş verirsə, o zaman düstur məlumatların sonrakı axtarışı üçün birinci sıranı seçəcəkdir.

VLOOKUP performansı

Məqalənin kulminasiya nöqtəsinə çatdınız. Deyəsən, axırıncı parametr kimi sıfır və ya birini qeyd etsəm, nə fərqi var? Əsasən, hər kəs, əlbəttə ki, sıfırı göstərir, çünki bu olduqca praktikdir: massivin birinci sütununu çeşidləməkdən narahat olmaq lazım deyil, dəyərin tapılıb-tapılmadığını dərhal görə bilərsiniz. Lakin vərəqinizdə bir neçə min VLOOKUP düsturunuz varsa, VLOOKUP II-nin yavaş olduğunu görəcəksiniz. Eyni zamanda, adətən hamı düşünməyə başlayır:

• Mənə daha güclü kompüter lazımdır;
• Mənə daha sürətli bir düstur lazımdır, məsələn, bir çox insan INDEX + MATCH haqqında bilir ki, bu, cüzi 5-10% daha sürətlidir.

Və az adam düşünür ki, I tipli VLOOKUP-dan istifadə etməyə başlayan kimi və birinci sütunun hər hansı bir vasitə ilə çeşidlənməsini təmin edən kimi VLOOKUP sürəti 57 dəfə artacaq. Sözlə yazıram - ELLİ YEDDİ DƏFƏ! 57% yox, 5700%. Mən bu faktı kifayət qədər etibarlı şəkildə yoxladım.

Bu cür sürətli işin sirri ondadır ki, ikili axtarış adlanan çeşidlənmiş massivdə son dərəcə səmərəli axtarış alqoritmi tətbiq oluna bilər (halving metodu, dixotomiya üsulu). Beləliklə, I tipli VLOOKUP onu tətbiq edir və II növ VLOOKUP heç bir optimallaşdırma olmadan axtarış edir. Eyni şey, oxşar parametri ehtiva edən MATCH funksiyası və yalnız çeşidlənmiş massivlərdə işləyən və Lotus 1-2-3 ilə uyğunluq üçün Excel-ə daxil olan LOOKUP funksiyası üçün də keçərlidir.

Formulun çatışmazlıqları

VLOOKUP-un çatışmazlıqları göz qabağındadır: birincisi, o, yalnız göstərilən massivin birinci sütununda, ikincisi, yalnız bu sütunun sağında axtarış aparır. Və başa düşdüyünüz kimi, lazımi məlumatları ehtiva edən sütunun axtaracağımız sütunun solunda olacağı da baş verə bilər. INDEX + MATCH düsturlarının artıq qeyd olunmuş birləşməsi bu çatışmazlıqdan məhrumdur, bu onu VLOOKUP (VLOOKUP) ilə müqayisədə cədvəllərdən məlumat çıxarmaq üçün ən çevik həll edir.

Düsturun real həyatda tətbiqinin bəzi aspektləri

Aralıq axtarışı

Bir sıra axtarışının klassik təsviri sifariş ölçüsünə görə endirimin müəyyən edilməsi vəzifəsidir.

Mətn sətirlərini axtarın

Əlbəttə ki, VLOOKUP yalnız rəqəmlərə deyil, mətnə də baxır. Nəzərə almaq lazımdır ki, düstur simvol halları arasında fərq qoymur. Joker işarələrdən istifadə etsəniz, qeyri-səlis axtarış təşkil edə bilərsiniz. İki joker işarə var: "?" - mətn sətirində hər hansı bir simvolu əvəz edir, "*" - istənilən sayda simvolu əvəz edir.

Döyüş yerləri

Axtarış zamanı əlavə boşluq problemini necə həll etmək olar sualı tez-tez qaldırılır. Axtarış cədvəli hələ də onlardan təmizlənə bilirsə, VLOOKUP düsturunun ilk parametri həmişə sizdən asılı deyil. Buna görə də, hüceyrələrin əlavə boşluqlarla tıxanma riski varsa, onu təmizləmək üçün TRIM funksiyasından istifadə edə bilərsiniz.

Fərqli məlumat formatı

VLOOKUP funksiyasının birinci parametri nömrəni ehtiva edən, lakin xanada mətn kimi saxlanılan xanaya aiddirsə və massivin birinci sütununda düzgün formatda rəqəmlər varsa, onda axtarış uğursuz olacaq. Əks vəziyyət də mümkündür. Problem 1 parametrini tələb olunan formata çevirməklə asanlıqla həll edilə bilər:

= VLOOKUP (−− D7; Məhsullar! $ A $ 2: $ C $ 5; 3; 0) - əgər D7 mətn, cədvəldə isə rəqəmlər varsa;

= VLOOKUP (D7 & ""); Məhsullar $ A $ 2: $ C $ 5; 3; 0) - və əksinə.

Yeri gəlmişkən, mətni eyni anda bir neçə yolla bir sıraya çevirə bilərsiniz, seçin:

• İkiqat inkar -D7.
• Bir D7 * 1-ə vurma.
• Sıfır əlavə D7 + 0.
• Birinci gücə yüksəldilməsi D7 ^ 1.

Nömrəni mətnə çevirmək Excel-i məlumat növünü çevirməyə məcbur edən boş sətirlə birləşmə yolu ilə həyata keçirilir.

# N / A-nı necə basdırmaq olar

Bunu IFERROR funksiyası ilə etmək çox rahatdır.

Məsələn: = IFERROR (VLOOKUP (D7; Məhsullar! $ A $ 2: $ C $ 5; 3; 0); "").

VLOOKUP # N / A səhv kodunu qaytararsa, IFERROR onu tutacaq və 2-ci parametri (bu halda boş sətir) əvəz edəcək və heç bir səhv baş verməsə, bu funksiya ümumiyyətlə mövcud olmadığını iddia edəcək, lakin normal nəticə verən yalnız VLOOKUP var.

Massiv

Çox vaxt onlar massivin istinadını mütləq etməyi unudurlar və massiv uzanan zaman “üzər”. A2: C5 əvəzinə $ A $ 2: $ C $ 5 istifadə etməyi unutmayın.

İstinad massivini iş kitabının ayrıca vərəqinə yerləşdirmək yaxşı olar. Ayağın altına düşmür və daha təhlükəsiz olacaq.

Bu massivi adlandırılmış diapazon kimi elan etmək daha yaxşı bir fikir olardı.

Bir çox istifadəçi massivi təyin edərkən bütün sütunları göstərərək A: C kimi konstruksiyadan istifadə edir. Bu yanaşmanın mövcud olmaq hüququ var, çünki siz massivinizin bütün tələb olunan sətirləri ehtiva etməsi faktını izləmək məcburiyyətindən xilas olursunuz. Vərəqə orijinal massivlə sətirlər əlavə etsəniz, A: C kimi göstərilən aralığın tənzimlənməsinə ehtiyac yoxdur. Əlbəttə ki, bu sintaktik konstruksiya Excel-i diapazonu dəqiq müəyyən etməkdən bir az daha çox iş görməyə məcbur edir, lakin bu əlavə məsrəflərə məhəl qoymamaq olar. Biz saniyənin yüzdə biri haqqında danışırıq.

Yaxşı, dahi ərəfəsində - massivi formada təşkil etmək.

Çıxarılacaq sütunu təyin etmək üçün COLUMN funksiyasından istifadə edin

VLOOKUP-dan istifadə edərək məlumat əldə etdiyiniz cədvəl axtarış cədvəli ilə eyni quruluşa malikdirsə, lakin sadəcə olaraq daha az sətirdən ibarətdirsə, onda siz əldə ediləcək sütunların sayını avtomatik hesablamaq üçün VLOOKUP-da SÜTUN () funksiyasından istifadə edə bilərsiniz. Bu halda, bütün VLOOKUP düsturları eyni olacaq (avtomatik olaraq dəyişən birinci parametrə uyğunlaşdırılmışdır)! Qeyd edək ki, birinci parametrin mütləq sütun koordinatı var.

& "|" & ilə kompozit açar yaratmaq

Eyni anda bir neçə sütun üzrə axtarış etmək zərurəti yaranarsa, o zaman axtarış üçün kompozit açar hazırlamaq lazımdır. Əgər qaytarılan dəyər mətn deyilsə ("Kod" sahəsində olduğu kimi), rəqəmsal olsaydı, daha rahat SUMIFS düsturu bunun üçün uyğun olardı və kompozit sütun açarı ümumiyyətlə tələb olunmazdı.

Bu mənim Lifehacker üçün ilk məqaləmdir. Bəyəndinizsə, sizi ziyarət etməyə dəvət edirəm, həmçinin VLOOKUP funksiyasından istifadə sirləriniz və buna bənzər şeylər haqqında şərhlərdə məmnuniyyətlə oxuyaram. təşəkkürlər.:)