Nə üçün INDEX və MATCH Excel-də VLOOKUP-dan yaxşıdır

Biz əvvəllər yeni başlayanlara VLOOKUP (İngiliscə VLOOKUP, abbreviatura “şaquli axtarış funksiyası” deməkdir) əsas funksiyalarından necə istifadə edəcəyini izah etdik. Təcrübəli istifadəçilərə bir neçə daha mürəkkəb düsturlar göstərildi.

Və bu yazıda şaquli axtarışla işləməyin başqa bir üsulu haqqında məlumat verməyə çalışacağıq.

Sizi maraqlandıra bilər: “Bu, niyə lazımdır?”. Və bu, bütün mümkün axtarış üsullarını göstərmək üçün lazımdır. Bundan əlavə, çoxsaylı VLOOKUP məhdudiyyətləri çox vaxt istənilən nəticəni əldə etməyə mane olur. Bu baxımdan, INDEX( ) MATCH( ) daha funksional və müxtəlifdir və onlar da daha az məhdudiyyətlərə malikdir.

Əsaslar INDEX MATCH

Bu təlimatın məqsədi bu xüsusiyyətin nə qədər yaxşı olduğunu göstərmək olduğundan, biz Onun fəaliyyət prinsipləri ilə bağlı əsas məlumatlara baxaq. Və nümunələr göstərəcəyik və bunun VLOOKUP () dən daha yaxşı olduğunu düşünəcəyik.

INDEX Funksiyasının Sintaksisi və İstifadəsi

Bu funksiya sütun və ya sətir nömrəsi əsasında müəyyən edilmiş axtarış sahələri arasında istədiyiniz dəyəri tapmağa kömək edir. Sintaksis:

=INDEX(massiv, sıra nömrəsi, sütun nömrəsi):

• massiv – axtarışın aparılacağı sahə;
• sətir nömrəsi – müəyyən edilmiş massivdə axtarılacaq xəttin nömrəsi. Əgər sətir nömrəsi məlum deyilsə, sütun nömrəsi göstərilməlidir;
• sütun nömrəsi - müəyyən edilmiş massivdə tapılacaq sütunun nömrəsi. Dəyər məlum deyilsə, sətir nömrəsi tələb olunur.

Sadə bir formulun nümunəsi:

=İNDEKS(A1:S10,2,3)

Funksiya A1 ilə C10 diapazonunda axtarış aparacaq. Rəqəmlər istədiyiniz dəyərin hansı sətirdən (2) və sütundan (3) göstərildiyini göstərir. Nəticə C2 xanası olacaq.

Olduqca sadə, elə deyilmi? Ancaq real sənədlərlə işlədiyiniz zaman sütun nömrələri və ya xanalar haqqında məlumatınız olmayacaq. MATCH() funksiyası bunun üçündür.

MATCH Funksiyasının Sintaksisi və İstifadəsi

MATCH() funksiyası istədiyiniz dəyəri axtarır və müəyyən edilmiş axtarış sahəsində onun təxmini sayını göstərir.

searchpos() sintaksisi belə görünür:

=MATCH(axtarılacaq dəyər, axtarılacaq massiv, uyğunluq növü)

• axtarış dəyəri – tapılacaq nömrə və ya mətn;
• axtarılan massiv – axtarışın aparılacağı sahə;
• uyğunluq növü - dəqiq dəyəri və ya ona ən yaxın olan dəyərləri axtarmaq lazım olduğunu müəyyənləşdirir:
  • 1 (və ya dəyər göstərilməyib) – göstərilən dəyərə bərabər və ya ondan kiçik olan ən böyük dəyəri qaytarır;
  • 0 – axtarılan dəyərlə dəqiq uyğunluğu göstərir. INDEX() MATCH() kombinasiyasında sizə demək olar ki, həmişə dəqiq uyğunluq lazımdır, ona görə də biz 0 yazırıq;
  • -1 – Düsturda göstərilən dəyərdən böyük və ya ona bərabər olan ən kiçik dəyəri göstərir. Çeşidləmə azalan ardıcıllıqla aparılır.

Məsələn, B1:B3 diapazonunda Nyu-York, Paris, London qeydə alınıb. Aşağıdakı düstur 3 rəqəmini göstərəcək, çünki London siyahıda üçüncüdür:

=MƏHŞƏT (London,B1:B3,0)

INDEX MATCH funksiyası ilə necə işləmək olar 

Yəqin ki, siz artıq bu funksiyaların birgə işinin qurulduğu prinsipi anlamağa başlamısınız. Bir sözlə, onda INDEX() göstərilən sətir və sütunlar arasında istədiyiniz dəyəri axtarır. Və MATCH() bu dəyərlərin nömrələrini göstərir:

=INDEX(qiymətin qaytarıldığı sütun, MATCH(axtarılacaq dəyər, axtarış ediləcək sütun, 0))

Hələ də bunun necə işlədiyini anlamaqda çətinlik çəkirsiniz? Bəlkə bir nümunə daha yaxşı izah edəcək. Tutaq ki, sizdə dünya paytaxtlarının və onların əhalisinin siyahısı var:

Müəyyən bir paytaxtın, məsələn, Yaponiyanın paytaxtının əhalisinin sayını öyrənmək üçün aşağıdakı düsturdan istifadə edirik:

=İNDEKS(C2:C10, MATCH(Yaponiya, A2:A10,0))

Explanation:

• MATCH() funksiyası A2:A10 massivində “Yaponiya” dəyərini axtarır və 3 rəqəmini qaytarır, çünki Yaponiya siyahıda üçüncü dəyərdir. 
• Bu rəqəm gedirsətir nömrəsi” INDEX() düsturunda yazın və funksiyaya bu sətirdən dəyəri çap etməsini bildirir.

Beləliklə, yuxarıdakı düstur standart düstura çevrilir İNDEKS(C2:C10,3). Formula C2 xanalarından C10-a qədər axtarış aparır və bu diapazondakı üçüncü xanadan, yəni C4-dən məlumatları qaytarır, çünki geri sayım ikinci cərgədən başlayır.

Düsturda şəhərin adını yazmaq istəmirsiniz? Sonra onu istənilən xanaya yazın, F1 deyin və MATCH() düsturunda istinad kimi istifadə edin. Və dinamik axtarış formulunu əldə edirsiniz:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Əhəmiyyətli! Daxil olan sətirlərin sayı array INDEX() sətirlərin sayı ilə eyni olmalıdır massiv hesab olunur MATCH(), əks halda səhv nəticə əldə edəcəksiniz.

Bir dəqiqə gözləyin, niyə VLOOKUP() düsturundan istifadə etməyəsiniz?

=VLOOKUP(F1, A2:C10, 3, Yanlış)

 INDEX MATCH-in bütün bu mürəkkəbliklərini anlamağa çalışaraq vaxt itirməyin nə mənası var?

Bu halda hansı funksiyadan istifadə etməyin əhəmiyyəti yoxdur. Bu, INDEX() və MATCH() funksiyalarının birlikdə necə işlədiyini başa düşmək üçün sadəcə bir nümunədir. Digər nümunələr VLOOKUP-un gücsüz olduğu hallarda bu funksiyaların nəyə qadir olduğunu göstərəcək. 

INDEX MATCH və ya VLOOKUP

Hansı axtarış formulunun istifadə ediləcəyinə qərar verərkən, çoxları INDEX() və MATCH()-in VLOOKUP-dan xeyli üstün olduğu ilə razılaşır. Bununla belə, bir çox insan hələ də VLOOKUP() funksiyasından istifadə edir. Birincisi, VLOOKUP() daha sadədir, ikincisi, istifadəçilər INDEX() və MATCH() ilə işləməyin bütün üstünlüklərini tam başa düşmürlər. Bu bilik olmadan heç kim vaxtını mürəkkəb sistemi öyrənməyə sərf etməyə razı olmaz.

INDEX() və MATCH() funksiyalarının VLOOKUP() ilə müqayisədə əsas üstünlükləri bunlardır:

• Sağdan sola axtar. VLOOKUP() sağdan sola axtarış edə bilməz, ona görə də axtardığınız dəyərlər həmişə cədvəlin ən sol sütunlarında olmalıdır. Lakin INDEX() və MATCH() bunu problemsiz idarə edə bilər. Bu yazı sizə praktikada necə göründüyünü izah edəcək: sol tərəfdə istədiyiniz dəyəri necə tapmaq olar.

1. Sütunların təhlükəsiz əlavə edilməsi və ya çıxarılması. VLOOKUP() düsturu sütunları silərkən və ya əlavə edərkən səhv nəticələr göstərir, çünki VLOOKUP() uğurlu olması üçün dəqiq sütun nömrəsini tələb edir. Təbii ki, sütunlar əlavə edildikdə və ya çıxarıldıqda onların nömrələri də dəyişir. 

Və INDEX() və MATCH() düsturlarında ayrı-ayrı sütunlar deyil, bir sıra sütunlar göstərilir. Nəticə etibarilə, hər dəfə düsturu yeniləmədən sütunları təhlükəsiz əlavə edə və silə bilərsiniz.

1. Axtarış həcmində məhdudiyyət yoxdur. VLOOKUP() funksiyasından istifadə edərkən axtarış meyarlarının ümumi sayı 255 simvoldan çox olmamalıdır, əks halda siz #VALUE alacaqsınız! Beləliklə, əgər məlumatlarınız çox sayda simvoldan ibarətdirsə, INDEX() və MATCH() ən yaxşı seçimdir.
2. Yüksək emal sürəti. Cədvəlləriniz nisbətən kiçikdirsə, çətin ki, hər hansı bir fərq hiss edəsiniz. Ancaq cədvəldə yüzlərlə və ya minlərlə sətir varsa və buna görə yüzlərlə və minlərlə düstur varsa, INDEX () və MATCH () VLOOKUP () ilə müqayisədə daha sürətli öhdəsindən gələcəkdir. Fakt budur ki, Excel bütün cədvəli emal etmək əvəzinə, yalnız formulada göstərilən sütunları emal edəcəkdir. 

VLOOKUP() funksiyasının performansa təsiri, iş vərəqinizdə VLOOKUP() və SUM() kimi çoxlu sayda düsturlardan ibarət olduqda xüsusilə nəzərə çarpacaq. Massivdəki hər bir dəyəri təhlil etmək üçün VLOOKUP() funksiyalarının ayrıca yoxlamaları tələb olunur. Beləliklə, Excel böyük miqdarda məlumatı emal etməlidir və bu, işi əhəmiyyətli dərəcədə yavaşlatır.

Formula nümunələri 

Biz artıq bu funksiyaların faydalılığını anladıq, ona görə də ən maraqlı hissəyə keçə bilərik: biliklərin praktikada tətbiqi.

Sağdan sola axtarış üçün formula

Artıq qeyd edildiyi kimi, VLOOKUP bu axtarış formasını həyata keçirə bilməz. Beləliklə, istədiyiniz dəyərlər ən sol sütunda deyilsə, VLOOKUP() nəticə verməyəcək. INDEX() və MATCH() funksiyaları daha çox yönlüdür və dəyərlərin yeri onların işləməsi üçün böyük rol oynamır.

Məsələn, cədvəlimizin sol tərəfinə bir dərəcə sütunu əlavə edəcəyik və Ölkəmizin paytaxtının əhali baxımından hansı yeri tutduğunu anlamağa çalışacağıq.

G1 xanasına tapılacaq dəyəri yazırıq və sonra C1:C10 diapazonunda axtarış etmək üçün aşağıdakı düsturdan istifadə edirik və A2:A10-dan müvafiq dəyəri qaytarırıq:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Tələb. Bu düsturdan bir neçə xana üçün istifadə etməyi planlaşdırırsınızsa, mütləq ünvanlamadan istifadə edərək diapazonları düzəltdiyinizə əmin olun (məsələn, $A$2: $A$10 və $C$2: 4C$10).

İNDEKS DAHA ÇOX MARUZ OLUR  sütun və sətirlərdə axtarmaq üçün

Yuxarıdakı misallarda biz bu funksiyalardan VLOOKUP() funksiyasının əvəzedicisi kimi əvvəlcədən təyin edilmiş sətirlər diapazonundan dəyərləri qaytarmaq üçün istifadə etdik. Bəs matris və ya iki tərəfli axtarış etmək lazımdırsa?

Mürəkkəb səslənir, lakin bu cür hesablamalar üçün düstur yalnız bir fərqlə standart INDEX() MATCH() düsturuna bənzəyir: MATCH() düsturundan iki dəfə istifadə edilməlidir. Birinci dəfə sıra nömrəsini, ikinci dəfə isə sütun nömrəsini almaq üçün:

=INDEX(massiv, MATCH(şaquli axtarış dəyəri, axtarış sütunu, 0), MATCH(üfüqi axtarış dəyəri, axtarış sırası, 0))

Aşağıdakı cədvələ baxaq və düstur yaratmağa çalışaq INDEX() EXPRESS() EXPRESS() seçilmiş il üçün müəyyən bir ölkədə demoqrafik göstəriciləri göstərmək üçün.

Hədəf ölkə G1 xanasında (şaquli axtarış), hədəf il isə G2 xanasındadır (üfüqi axtarış). Formula belə görünəcək:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Bu formula necə işləyir

Hər hansı digər mürəkkəb düsturlarda olduğu kimi, onları ayrı-ayrı tənliklərə bölməklə başa düşmək daha asandır. Və sonra hər bir fərdi funksiyanın nə etdiyini başa düşə bilərsiniz:

• MATCH(G1,A2:A11,0) – A1:A2 diapazonunda dəyəri (G11) axtarır və bu dəyərin sayını göstərir, bizim vəziyyətimizdə 2-dir;
• AXTAR(G2,B1:D1,0) – B2:D1 diapazonunda dəyəri (G1) axtarır. Bu vəziyyətdə nəticə 3 oldu.

Tapılan sətir və sütun nömrələri INDEX() düsturunda müvafiq dəyərə göndərilir:

=İNDEKS(B2:D11,2,3)

Nəticədə, B2:D3 diapazonunda 2 sətir və 11 sütunun kəsişməsindəki xanada olan dəyərə sahibik. Və düstur D3 xanasında olan istənilən dəyəri göstərir.

INDEX və MATCH ilə çoxsaylı şərtlərlə axtarın

VLOOKUP() üzrə bələdçimizi oxumusunuzsa, yəqin ki, birdən çox axtarış formulunu sınamısınız. Ancaq bu axtarış metodunun bir əhəmiyyətli məhdudiyyəti var - köməkçi sütun əlavə etmək ehtiyacı.

Ancaq yaxşı xəbər budur INDEX() və MATCH() ilə siz iş vərəqinizi redaktə etmədən və ya dəyişmədən çoxlu şərtləri axtara bilərsiniz.

INDEX() MATCH() üçün ümumi çoxşərtli axtarış düsturu budur:

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Qeyd: bu formula klaviatura qısayolu ilə birlikdə istifadə edilməlidir CTRL+SHIFT+ENTER.

Tutaq ki, 2 şərt əsasında axtardığınız dəyəri tapmalısınız: Alıcı и Məhsul.

Bunun üçün aşağıdakı düstur tələb olunur:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Bu düsturda C2:C10 axtarışın aparılacağı diapazondur, F1 - bu şərt, A2:A10 — vəziyyəti müqayisə etmək üçün diapazondur, F2 - şərt 2, V2: V10 – 2-ci şərtin müqayisəsi üçün diapazon.

Düsturla işin sonunda birləşməni basmağı unutmayın CTRL + ÜSTKRKT + GİRİŞ – Excel nümunədə göstərildiyi kimi düsturu əyri mötərizələrlə avtomatik bağlayacaq:

İşiniz üçün massiv düsturundan istifadə etmək istəmirsinizsə, düstura başqa INDEX() əlavə edin və ENTER düyməsini basın, o, misaldakı kimi görünəcək:

Bu düsturlar necə işləyir

Bu düstur standart INDEX() MATCH() düsturu ilə eyni şəkildə işləyir. Çoxlu şərtləri axtarmaq üçün siz sadəcə olaraq düzgün və yanlış fərdi şərtləri təmsil edən çoxlu Yanlış və Doğru şərtlər yaradırsınız. Və sonra bu şərtlər massivin bütün uyğun elementlərinə aiddir. Düstur False və True arqumentlərini müvafiq olaraq 0 və 1-ə çevirir və 1-in sətirdə tapılan uyğun dəyərlər olduğu massiv çıxarır. MATCH() 1-ə uyğun gələn ilk dəyəri tapacaq və onu INDEX() düsturuna ötürür. Və o, öz növbəsində, istədiyiniz sütundan göstərilən sətirdə artıq istədiyiniz dəyəri qaytaracaqdır.

Massiv olmayan düstur INDEX() funksiyasının onları təkbaşına idarə etmək qabiliyyətindən asılıdır. Düsturdakı ikinci INDEX() yalana (0) uyğun gəlir, ona görə də bütün massivi həmin dəyərlərlə MATCH() düsturuna ötürür. 

Bu, bu formulun arxasında duran məntiqin kifayət qədər uzun izahıdır. Daha çox məlumat üçün məqaləni oxuyun "Çoxlu şərtlərlə INDEX MATCH.

INDEX və MATCH-də AVERAGE, MAX və MIN

Excel orta, maksimum və minimumları tapmaq üçün öz xüsusi funksiyalarına malikdir. Bəs siz həmin dəyərlərlə əlaqəli xanadan məlumat əldə etmək istəyirsinizsə nə etməli? Bu halda AVERAGE, MAX və MIN INDEX və MATCH ilə birlikdə istifadə edilməlidir.

INDEX MATCH və MAX

D sütununda ən böyük dəyəri tapmaq və onu C sütununda göstərmək üçün düsturdan istifadə edin: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH və MIN

D sütununda ən kiçik dəyəri tapmaq və onu C sütununda göstərmək üçün aşağıdakı düsturdan istifadə edin:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

AXTAR INDEX və SERPENT

D sütununda orta dəyəri tapmaq və bu dəyəri C-də göstərmək üçün:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Verilənlərinizin necə yazılmasından asılı olaraq, MATCH() üçün üçüncü arqument ya 1, 0 və ya -1-dir:

• sütunlar artan qaydada sıralanırsa, 1-i təyin edin (sonra düstur orta dəyərdən az və ya ona bərabər olan maksimum dəyəri hesablayacaq);
• sort azalandırsa, onda -1 (düstur ortadan böyük və ya ona bərabər olan minimum dəyəri çıxaracaq);
• əgər axtarış massivində orta qiymətə tam bərabər olan dəyər varsa, onu 0-a təyin edin. 

 Nümunəmizdə əhali azalan ardıcıllıqla sıralanır, ona görə də -1 qoyuruq. Və nəticə Tokiodur, çünki əhalinin dəyəri (13,189) orta dəyərə (000) ən yaxındır.

VLOOKUP() də belə hesablamaları yerinə yetirə bilər, lakin yalnız massiv düsturu kimi: AVERAGE, MIN və MAX ilə VLOOKUP.

INDEX MATCH və ESND/IFERROR

Yəqin ki, siz artıq qeyd etdiniz ki, əgər düstur istənilən dəyəri tapa bilmirsə, xəta verir # N / A. Standart səhv mesajını daha informativ bir şeylə əvəz edə bilərsiniz. Məsələn, düsturda arqument təyin edin XNUMX-cu ildə:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Bu formula ilə cədvəldə olmayan məlumatları daxil etsəniz, forma sizə göstərilən mesajı verəcəkdir.

Bütün səhvləri tutmaq istəyirsinizsə, istisna olmaqla XNUMX-cu ildə istifadə edilə bilər XƏTA:

=SƏHF(İNDEKS(C2:C10,MATCH(F1,A2:A10,0)), "Nəsə xəta baş verdi!”)

Ancaq unutmayın ki, səhvləri bu şəkildə maskalamaq yaxşı fikir deyil, çünki standart səhvlər düsturdakı pozuntuları bildirir.

Ümid edirik ki, INDEX MATCH() funksiyasından istifadə etmək üçün bələdçimizi faydalı tapdınız.