Bu məqalə həsr edilmişdir VLOOKUP funksiyaları. Nəzərə alacaq VLOOKUP funksiyası üçün addım-addım təlimatlar, "" başlığı ilə. Bu yazıda təsviri, sintaksisi və ətraflı nəzərdən keçirəcəyik Excel-də VLOOKUP funksiyasının nümunələri. Biz onu necə istifadə edəcəyimizi də nəzərdən keçirəcəyik və əsas səhvlərə və nəyə görə olduğuna baxacağıq. Vlookup funksiyası işləmir.
Excel-də VLOOKUP funksiyasının sintaksisi və təsviri
Beləliklə, bu məqalənin ikinci adı “ Butaforlar üçün Excel-də VLOOKUP funksiyası", gəlin VLOOKUP funksiyasının nə olduğunu və nə etdiyini öyrənməklə başlayaq? İngilis dilində VLOOKUP funksiyası VLOOKUP, göstərilən dəyəri axtarır və başqa sütundan müvafiq dəyəri qaytarır.
VLOOKUP funksiyası necə işləyir? Excel-də VLOOKUP funksiyası unikal identifikator əsasında məlumat siyahılarınızı axtarır və sizə həmin unikal identifikatorla əlaqəli məlumat verir.
VPR-də "V" hərfi "şaquli" deməkdir. Fərqləndirmək üçün istifadə olunur VLOOKUP və GLOOKUP funksiyaları, massivin yuxarı cərgəsindəki dəyəri axtarır ("H" "üfüqi" deməkdir).
VLOOKUP funksiyası Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003-ün bütün versiyalarında mövcuddur.
Sintaksis VLOOKUP funksiyaları göstərildiyi kimi:
VLOOKUP(axtarma_dəyəri, cədvəl, sütun_nömrə, [interval_axtar])
Gördüyünüz kimi, VLOOKUP funksiyası 4 parametr və ya arqumentə malikdir. İlk üç parametr tələb olunur, sonuncu isteğe bağlıdır.
- axtarış_dəyəri axtarış dəyəridir.
Bu ya dəyər (rəqəm, tarix və ya mətn) və ya ola bilər hüceyrə istinadı(axtarış dəyərini ehtiva edən xanaya istinad) və ya digər Excel funksiyası tərəfindən qaytarılan dəyər. Misal üçün:
- Nömrəni axtarın: =VLOOKUP(40; A2:B15; 2) - düstur 40 rəqəmini axtaracaq.
- Mətn axtarışı: =VLOOKUP("almalar", A2:B15, 2) - düstur "alma" mətnini axtaracaq. Qeyd edək ki, siz həmişə mətn dəyərlərini "ikiqat dırnaq" içərisinə daxil edirsiniz.
- Başqa xanadan dəyər axtarılır: =VLOOKUP(C2, A2:B15, 2) - düstur C2 xanasındakı dəyəri axtaracaq.
- cədvəl iki və ya daha çox verilənlər sütunudur.
yadda saxla ki VLOOKUP funksiyası həmişə cədvəlin birinci sütununda istədiyiniz dəyəri axtarır. Cədvəlinizdə mətn, tarix, nömrələr və ya boolean dəyərlər kimi müxtəlif dəyərlər ola bilər. Dəyərlər hərflərə həssas deyil, bu o deməkdir ki, böyük və kiçik hərflər eyni sayılır.
Beləliklə, =VLOOKUP(40, A2:B15, 2) düsturumuz A2-dən A15-ə qədər xanalarda "40" axtaracaq, çünki A A2:B15 cədvəlinin birinci sütunudur.
- sütun_nömrəsi - müvafiq sətirdəki dəyərin qaytarılmalı olduğu cədvəldəki sütunun nömrəsi.
Göstərilən cədvəldə ən sol sütun 1, ikinci sütun 2, üçüncüsü 3 və s.
4. interval_görünüş dəqiq uyğunluq (YANLIŞ) və ya təxmini uyğunluq (DOĞRU və ya buraxılmış) axtardığınızı müəyyən edir. Bu son parametr isteğe bağlıdır, lakin çox vacibdir.
Excel nümunələrində VLOOKUP funksiyası
İndi bəzi istifadə hallarına baxaq VLOOKUP funksiyaları real məlumatlar üçün.
Müxtəlif vərəqlərdə VLOOKUP funksiyası
Təcrübədə VLOOKUP düsturları bir vərəqdə məlumat axtarmaq üçün nadir hallarda istifadə olunur. Daha tez-tez başqa bir vərəqdən müvafiq məlumatları axtarmalı və götürməli olacaqsınız.
Kimə VLOOKUP funksiyasından istifadə edin başqa Excel vərəqindən siz iş vərəqinin adını və xanalar diapazonundan əvvəl cədvəl arqumentinə nida işarəsini daxil etməlisiniz, məsələn, =VLOOKUP(40;Sheet2!A2:B15,2). Düstur A2:B15 axtarış diapazonunun Cədvəl2-də olduğunu göstərir.
Əlbəttə ki, vərəq adını əl ilə daxil etmək lazım deyil. Sadəcə düsturu yazmağa başlayın və söhbət masa arqumentinə gəldikdə, axtarış iş vərəqinə keçin və siçanla diapazonu seçin.
Aşağıdakı şəkildə gördüyünüz düstur "Qiymətlər" iş vərəqində A sütununda (A2:B9 axtarış aralığının 1-ci sütunu) A2 ("Məhsul 3") xanasındakı mətni axtarır:
VLOOKUP(A2,Qiymətlər!$A$2:$B$8,2,YANLIŞ)
Excel-də VLOOKUP funksiyası - müxtəlif vərəqlərdə VLOOKUP funksiyası
VLOOKUP düsturlarında adlandırılmış diapazondan və ya cədvəldən necə istifadə etmək olar
Birdən çox VLOOKUP düsturlarında eyni axtarış diapazonundan istifadə etmək niyyətindəsinizsə, onun adını birbaşa VLOOKUP düsturunuzun cədvəl arqumentində yarada və daxil edə bilərsiniz.
Adlandırılmış diapazon yaratmaq üçün sadəcə xanaları seçin və Formula panelinin solunda yerləşən Ad xanasına istənilən adı daxil edin.
Excel-də VLOOKUP funksiyası - Aralığın adlandırılması
İndi Məhsul 1-in qiymətini əldə etmək üçün aşağıdakı VLOOKUP düsturunu yaza bilərsiniz:
VLOOKUP("Məhsul 1";Məhsullar;2)
Excel-də VLOOKUP funksiyası - Aralıq adı ilə VLOOKUP funksiyasının nümunəsi
Excel-dəki əksər diapazon adları bütün iş kitabına tətbiq edilir, beləliklə, axtarış diapazonunuz başqa iş vərəqində olsa belə, iş vərəqi adını göstərməyə ehtiyac yoxdur. Belə formullar daha başa düşüləndir. Həmçinin, adlandırılmış diapazonlardan istifadə hüceyrələrə yaxşı alternativ ola bilər. Düstur digər xanalara kopyalandıqda adlandırılan diapazon dəyişmədiyi üçün axtarış diapazonunuzun həmişə düzgün qalacağına əmin ola bilərsiniz.
Əgər siz bir sıra xanaları tam funksiyalı Excel cədvəlinə çevirmisinizsə (Daxil et nişanı -> Cədvəl), siçanla axtarış diapazonunu seçə bilərsiniz və Microsoft Excel avtomatik olaraq düstura sütun adlarını və ya cədvəl adını əlavə edəcək. :
Excel-də VLOOKUP funksiyası - Cədvəl adı ilə VLOOKUP funksiyasının nümunəsi
Tam formula bu kimi görünə bilər:
VLOOKUP("Məhsul 1";Cədvəl 6[[Məhsul]:[Qiymət]];2)
və ya hətta =VLOOKUP("Məhsul 1";Cədvəl 6;2).
Adlandırılmış diapazonlar kimi, sütun adları da sabitdir və VLOOKUP düsturunun harada kopyalanmasından asılı olmayaraq, xana istinadları dəyişməyəcək.
Çoxlu şərtlərlə VLOOKUP funksiyası
Gəlin nəzərdən keçirək bir neçə şərtlə VLOOKUP funksiyasının nümunəsi. Aşağıdakı giriş məlumatlarımız var:
Excel-də VLOOKUP funksiyası - Mənbə məlumat cədvəli
Tutaq ki, istifadə etməliyik Bir neçə şərtlə VLOOKUP funksiyası. Məsələn, iki meyardan istifadə edərək məhsulun qiymətini axtarmaq üçün: məhsulun adı və onun növü.
- İstifadə etmək üçün Bir neçə şərtlə VLOOKUP funksiyası əvvəlində məhsulun adı və növü ilə məlumatı saxlayacaq əlavə sütun daxil etmək lazımdır.
Beləliklə, "Qiymətlər" vərəqində bir sütun daxil edin və A2 xanasına aşağıdakı düsturu daxil edin:
Bu düsturdan istifadə edərək "Məhsul" və "Növ" sütunlarının dəyərini alırıq. Bütün xanaları doldurun.
İndi axtarış cədvəli belə görünür:
Excel-də VLOOKUP funksiyası - Köməkçi sütunun əlavə edilməsi
- İndi “Satış” vərəqindəki C2 xanasına aşağıdakı VLOOKUP düsturunu yazacağıq:
VLOOKUP(A2Qiymətlər!$A$1:$D$8,4,YANLIŞ)
Qalan xanaları doldururuq və nəticədə növə uyğun olaraq hər bir məhsulun qiymətlərini alırıq:
Excel-də VLOOKUP funksiyası - Çoxlu Şərtlərlə VLOOKUP Nümunəsi
İndi gəlin buna baxaq VLOOKUP funksiyası xətaları.
VLOOKUP funksiyası niyə işləmir?
Məqalənin bu hissəsində bunun səbəbini nəzərdən keçirəcəyik Vlookup funksiyası işləmir və mümkündür VLOOKUP funksiyası xətaları.
|
Səhv növü |
Səbəb |
Həll |
|
Axtarılan sütunun yeri yanlışdır |
Axtarışın aparıldığı cədvəl sütunu ən solda olmalıdır.
|
|
|
Cədvəl diapazonu sabit deyil |
Əgər ilk dəyər düzgün göstərilibsə və VLOOKUP düsturunu uzatdıqdan sonra bəzi xanalarda #N/A xətası ilə rast gəlinirsə, cədvəl diapazonu sabit deyil.
|
|
|
Dəqiq uyğunluq tapmaq mümkün olmadı (əgər varsa time-lapse baxış seçilmiş dəqiq dəyəri axtarın (0) |
||
|
IN time-lapse baxışən yaxın dəyərin axtarışı (1) aparılır və axtarışın aparıldığı cədvəl çeşidlənmir. |
Cədvəlin birinci sütununu adların artan sırası ilə çeşidləyin. |
|
|
TYPE və ya SPACE funksiyalarından istifadə edin. |
||
|
Sütun nömrəsinin dəyəri cədvəldəki sütunların sayını keçir |
Qaytarılan dəyəri ehtiva edən sütun nömrəsini yoxlayın. |
|
|
Düsturda sitatlar yoxdur |
kimi istifadə edirsinizsə istədiyiniz dəyər xana linki deyil, mətndir, onda dırnaq içərisində olmalıdır. Misal üçün: VLOOKUP("Məhsul 1"; Qiymətlər!$A$2:$B$8,2,0) |
Ümid edirəm ki, indi belə dummies üçün Excel-də VLOOKUP funksiyası aydın olacaq.
Batyanov Denis Qonaq müəllif kimi o, bu yazıda bir Excel cədvəlində verilənləri necə tapıb digərinə çıxarmaq barədə danışır, həmçinin şaquli görünüş funksiyasının bütün sirlərini açır.
Çıxarma sütununu təyin etmək üçün COLUMN funksiyasından istifadə edin
VLOOKUP istifadə etmək üçün məlumat əldə etdiyiniz cədvəl axtarış cədvəli ilə eyni quruluşa malikdirsə, lakin sadəcə daha az sətirdən ibarətdirsə, VLOOKUP əldə ediləcək sütun nömrələrini avtomatik hesablamaq üçün COLUMN() funksiyasından istifadə edə bilər. Bu halda, bütün VLOOKUP düsturları eyni olacaq (avtomatik olaraq dəyişən birinci parametr üçün düzəliş edilir)! Nəzərə alın ki, birinci parametrin mütləq sütun koordinatı var.
&»|»& istifadə edərək kompozit açarın yaradılması
Eyni anda bir neçə sütunda axtarış aparmaq zərurəti yaranarsa, o zaman axtarış üçün kompozit açar yaratmaq lazımdı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ə, onda sizi veb saytıma baş çəkməyə dəvət edirəm və mən də VLOOKUP funksiyasından istifadə sirləriniz və buna bənzər şeylər haqqında şərhlərdə oxumaqdan məmnun olaram. Çox sağ ol. :)
Xülasə cədvəli ilə işləmək digər cədvəllərdən dəyərlərin daxil edilməsini əhatə edir. Cədvəllər çox olarsa, əl ilə ötürmə çox vaxt aparacaq və məlumatlar daim yenilənirsə, bu Sisyphean tapşırığı olacaq. Xoşbəxtlikdən, məlumatları avtomatik əldə etmək imkanı təklif edən VLOOKUP funksiyası var. Bu funksiyanın necə işlədiyinə dair konkret nümunələrə baxaq.
VLOOKUP funksiyasının adı “şaquli baxış funksiyası” deməkdir. İngilis dilində onun adı VLOOKUP-dur. Bu funksiya araşdırılan diapazonun sol sütununda verilənləri axtarır və sonra müəyyən edilmiş xanada əldə edilən dəyəri qaytarır. Sadəcə olaraq, VLOOKUP bir cədvəldəki xanadan digər cədvələ dəyərləri yenidən təşkil etməyə imkan verir. Excel-də VLOOKUP funksiyasından necə istifadə edəcəyimizi öyrənək.
VLOOKUP-dan istifadə nümunəsi
Gəlin xüsusi bir nümunədən istifadə edərək VLOOKUP funksiyasının necə işlədiyinə nəzər salaq.
İki masamız var. Bunlardan birincisi ərzaq məhsullarının adlarının yerləşdiyi alış cədvəlidir. Addan sonra növbəti sütunda alınması lazım olan məhsulun miqdarının dəyəri göstərilir. Sonrakı qiymət gəlir. Və son sütunda - kəmiyyəti qiymətə vurmaq üçün hüceyrəyə daxil edilmiş düsturdan istifadə edərək hesablanan müəyyən bir məhsulun alınmasının ümumi dəyəri. Ancaq biz sadəcə qiymət siyahısı olan bitişik cədvəldən VLOOKUP funksiyasından istifadə edərək qiyməti qaldırmalı olacağıq.
Göründüyü kimi, kartofun qiyməti qiymət cədvəlindən cədvələ əlavə edilib. Digər məhsul adları ilə belə mürəkkəb prosedurdan keçməmək üçün sadəcə olaraq doldurulmuş xananın sağ alt küncündə dayanırıq ki, xaç görünsün. Bu xaçı masanın ən altına çəkirik.
Beləliklə, VLOOKUP funksiyasından istifadə edərək bütün lazımi məlumatları bir cədvəldən digərinə çəkdik.
Gördüyünüz kimi VLOOKUP funksiyası ilk baxışdan göründüyü qədər mürəkkəb deyil. Onun istifadəsini başa düşmək çox çətin deyil, lakin bu aləti mənimsəmək cədvəllərlə işləyərkən çox vaxtınıza qənaət edəcəkdir.
Microsoft Excel-də istifadəçinin işini xeyli asanlaşdıran çoxlu müxtəlif funksiyalar var və bu yazıda onlardan biri haqqında danışacağıq. Bu VLOOKUP adlanır və əgər ingilis dilindədirsə, VLOOKUP.
VLOOKUP funksiyası dəyərləri bir cədvəldən digərindəki xüsusi hüceyrələrə köçürür. Daha ətraflı izah edim - birinci cədvəldə ikincinin sol sütununda tapmaq üçün lazım olan dəyəri seçirsiniz. Uyğunluqlar varsa, VLOOKUP dəyəri bu sətirdə göstərdiyiniz sütundan birinci cədvələ köçürür. Tərif bir az qarışıq olsa da, funksiyadan istifadə o qədər də çətin deyil. Gəlin bir neçə nümunəyə baxaq.
Funksiya ən çox fərdi qiymət siyahılarında göstərilən qiymət sütununu doldurmaq üçün istifadə edildiyi üçün aşağıdakı nümunəni götürək. Məsələn, meyvələr (portağal) olan bir masa var, burada hər biri üçün neçə kiloqram sifariş vermək istədiyimiz göstərilir. Müvafiq olaraq, hər bir meyvə üçün tədarükçünün verdiyi qiymət siyahısından (yaşıl) qiyməti yazmalısınız və sonra alışın nə qədər başa gələcəyini hesablamalısınız. Hər bir elementə baxmaq və məlumatları ötürmək çətindir, xüsusən də mallarla minlərlə sətir varsa, biz VLOOKUP-dan istifadə edirik.
Funksiya Sihirbazı pəncərəsini açmaq üçün Qiymət sütununda birinci cədvəlin yuxarıdakı xanasına, sonra isə düsturlar çubuğundakı “fx” düyməsinə klikləyin.
Növbəti işimiz təklif olunan sahələrə arqumentləri yazmaqdır.
Sahəyə kursiv qoyun "Axtarış_dəyəri" və birinci cədvəldə axtaracağımız dəyəri vurğulayın. Məndə bu alma var.
"Cədvəl" sətrində məlumatların alınacağı birini seçməlisiniz - başlığı seçməyin. Qeyd edək ki, ən sol sütun axtardığımız dəyərlərdən ibarət olmalıdır. Yəni mənə alma və digər meyvələr lazımdır, yəni onların siyahısı seçilmiş sahənin sol sütununda olmalıdır.
Düsturu yazdıqdan və onu bütün sütuna uzatdıqdan sonra seçilmiş diapazon aşağıya doğru hərəkət etməməsi üçün bağlantıları mütləq etməliyik: sahədəki məlumatları seçin və "F4" düyməsini basın. İndi xanaların ünvanı mütləq oldu, onlara “$” işarəsi əlavə edildi və diapazon dəyişməyəcək.
Sütun nömrəsi olduğu yerdə, ikinci cədvəldə müvafiq rəqəmi məlumat ötürmək istədiyiniz sütuna qoyun. Qiymət siyahım meyvə və qiymətdən ibarətdir, ikinciyə ehtiyacım var, ona görə də “2” rəqəmini qoyuram.
IN "Zaman Keçiri" Biz "YANLIŞ" yazırıq - dəqiq uyğunluqları axtarmaq lazımdırsa və ya "DOĞRU" - dəyərlər təxmini ola bilərsə. Nümunəmiz üçün birincini seçirik. Bu sahədə heç nə göstərməsəniz, ikincisi standart olaraq seçiləcək. Sonra "OK" düyməsini basın.
Burada aşağıdakılara diqqət yetirin: əgər siz rəqəmlərlə işləyirsinizsə və “Doğru” deyirsinizsə, ikinci cədvəl (bu bizim qiymət siyahımızdır) artan qaydada sıralanmalıdır. Məsələn, 5.25-i axtararkən 5.27 tapılacaq və bu sətirdən məlumatlar alınacaq, baxmayaraq ki, 5.2599-dan aşağı rəqəm də ola bilər - lakin düstur bundan sonra baxmayacaq, çünki aşağıdakı rəqəmlərin yalnız daha böyük olduğunu düşünür. .
VLOOKUP necə işləyir? İstədiyiniz dəyəri (alma) götürür və onu göstərilən diapazonun ən sol sütununda axtarır (meyvələrin siyahısı). Uyğunluq varsa, dəyər eyni cərgədən götürülür, yalnız arqumentlərdə göstərilən sütun (2) və bizə lazım olan xanaya köçürülür (C2). Formula belə görünür:
VLOOKUP(A2,$G$2:$H$12,2,YANLIŞ)
İndi aşağı sağ küncü çəkərək onu lazımi sayda sətirə qədər uzada bilərsiniz.
Bütün qiymətlər meyvələrin adlarına uyğun olaraq qiymət cədvəlindən satınalma cədvəlinə köçürülüb.
Əgər birinci cədvəldə qiymət siyahısında olmayan məhsulların adları varsa, mənim üçün bunlar tərəvəzdir, onda bu nöqtələrin əksinə VLOOKUP düsturu #N/A xətası yaradacaq.
İş vərəqinə sütunlar əlavə etdikdə funksiyanın Cədvəl arqumenti üçün məlumatlar avtomatik olaraq dəyişəcək. Nümunədə qiymət 2 sütun sağa sürüşdürülüb. Gəlin düsturlu istənilən xananı seçək və görək ki, $G$2:$H$12 əvəzinə indi $I$2:$J$14-dür.
İndi Excel-də VLOOKUP funksiyası və açılan siyahılarla necə işləməyi anlayaq. Əvvəlcə açılan siyahı hazırlamalısınız. Hüceyrəni seçin, yuxarıdakı "Məlumat" ı açın və düyməni basın "Məlumatların yoxlanılması".
Açılan "Məlumat növü" pəncərəsində "Siyahı" olacaq, aşağıda mənbə sahəsini göstəririk - bunlar meyvələrin adlarıdır, yəni həm birinci, həm də ikinci cədvəllərdə olan sütundur. "OK" düyməsini basın.
F2 seçirəm və VLOOKUP funksiyasını daxil edirəm. Birinci arqument hazırlanmış siyahıdır (F1).
İkincisi, göstərilən qiymətlərlə təchizatçı cədvəlidir. Unutmayın ki, burada sol sütun açılan siyahını təşkil edən məlumatlara uyğun olmalıdır.
Axtarış kimi bir şey çıxdı: bir meyvə seçirik və VPR onun qiymətini qiymət siyahısında tapır.
Eyni qiymətlərə sahibsinizsə və sonra təchizatçılar sizə yeni bir siyahı verdilərsə, onda nəyin dəyişdiyini birtəhər görmək lazımdır. Bunu əl ilə etmək çox vaxt aparır, lakin sözügedən funksiyanın köməyi ilə hər şeyi çox tez etmək olar. Bir sütun əlavə etməli və ona yeni dəyərlər köçürməli və sonra sadəcə məlumatları müqayisə etməlisiniz.
D sütununda istənilən xananı vurun və yenisini daxil edin.
Mən onu Yeni Qiymət adlandırdım - yeni qiymətlər burada olacaq, köhnə qiymətlər isə sol sütunda göstəriləcək. Yeni cədvəlim başqa vərəqdə yerləşir, belə ki, məlumat müxtəlif vərəqlərdə yerləşirsə VLOOKUP-dan necə istifadə edəcəyinizi başa düşə bilərsiniz. Əlavə edilmiş sütunda ilk boş xananı seçin.
Funksiyanı daxil edirik və arqumentləri təyin edirik. Birincisi, nə axtaracağıq, nümunədə bir alma (A2). Yeni qiymət siyahısından diapazon seçmək üçün kursoru “Cədvəl” sahəsinə qoyun və istədiyiniz vərəqə keçin, məndə “Vədvəl1” var.
Başlıqlar olmadan tələb olunan sütun və sətirləri seçmək üçün siçandan istifadə edin.
Sonra xanalara mütləq istinad edirik: “Sheet1!$A$2:$B$12” . Xətti seçin və hüceyrə ünvanlarına dollar işarəsi əlavə etmək üçün "F4" düyməsini basın. Sütun (2) göstəririk və “YANLIŞ” yazırıq.
Sonda "OK" düyməsini basın.
İndi yeni və köhnə qiymətləri olan iki sütun yan-yana yerləşir və siz ya vizual müqayisə, ya da müəyyən düsturlar tətbiq etməklə və ya şərti formatlaşdırma edə bilərsiniz.
Ümid edirəm ki, Excel-də VLOOKUP funksiyasından necə istifadə etmək və tətbiq etmək barədə addım-addım təlimat verdim və indi hər şey sizə aydındır.
Bu məqaləni qiymətləndirin: (13
reytinqlər, orta: 5,00
5-dən)
Veb ustası. İnformasiya Təhlükəsizliyi üzrə ali təhsil. Əksər məqalələrin və kompüter savadlılığı dərslərinin müəllifi
- SUMPRODUCT funksiyasından istifadə etməklə
- Sütun dəyərlərini birləşdirir ay Və Şəhər bir massivdə: JanMoscow, FebMoscow...
- İki massivi iki sütunlu cədvəldə birləşdirir.
Bunun nə qədər faydalı olduğunu hamımız bilirik. Yəqin ki, Excel-də bütün hərəkətlərin yarısı ondan istifadə etməklə həyata keçirilir. Bununla belə, bu funksiyanın bir sıra məhdudiyyətləri var. Məsələn, VLOOKUP cədvəldə yalnız ən sol sütuna baxır və ya yalnız bir şərt axtarır. Bəs iki şərtə uyğun olan dəyəri qaytarmaq lazımdırsa nə olacaq? Belə olan halda bəzi hiylələrə əl atmalı olacağıq. Bu fəndlər haqqında bugünkü məqalədə danışacağıq.
Beləliklə, iki şərtlə əvəzetmə funksiyası yaratmaq üçün dörd varianta baxacağıq:
Yaxşı, ən sadə şeydən başlayacağıq.
Əlavə sütundan istifadə
Əksər hallarda mürəkkəb problemlər onları kiçik hissələrə böldükdə daha sadə və idarə oluna bilir. Eyni şey Excel-də düsturlar qurarkən də tətbiq olunur.
Klassik bir nümunəyə baxaq. Ay və şəhər üzrə satış cədvəlimiz var. Və iki şərtə uyğun satış dəyərini təyin etməliyik: ay - fevral və şəhər - Samara.
VLOOKUP funksiyasının klassik formada istifadəsi bizə kömək etməyəcək, çünki o, yalnız bir şərtə uyğun gələn dəyəri qaytara bilər. Ay və Şəhər sütunlarının dəyərlərini birləşdirəcəyimiz bu vəziyyətdən çıxmağımıza əlavə bir sütun kömək edəcək. Bunun üçün A2 xanasına =B2&C2 düsturunu yazın və bu düsturu A13 xanasına genişləndirin. İndi tələb olunan dəyəri qaytarmaq üçün A sütununun dəyərlərindən istifadə edə bilərik. Düsturu G3 xanasına yazırıq:
VLOOKUP(G1A2:D13;4;0)
Bu düstur G1 və G2 xanalarının iki şərtini bir cərgədə birləşdirir və ona A sütununda baxır. İstənilən şərt tapıldıqdan sonra düstur A1:D13 cədvəlinin dördüncü sütunundan dəyəri qaytarır, yəni. sütun Satış.
Yeni axtarış cədvəli yaratmaq üçün SELECT funksiyasından istifadə edin
Nədənsə əlavə sütundan istifadə bizim üçün seçim deyilsə, istifadə edə bilərik.
SEÇİM funksiyasından istifadə sütun dəyərlərinin hansı cədvəldə olduğunu görmək üçün yeni cədvəl yaratmağı nəzərdə tutur ay Və Şəhər artıq birləşdirilib. Formulamız belə görünəcək:
VLOOKUP(G1SEÇ((1,2),B2:B13&C2:C13,D2:D13),2,0)
Bu düsturun əsas məqamı iki şeyi yerinə yetirən CHOICE((1;2);B2:B13&C2:C13;D2:D13) hissəsidir:
Bu funksiyanın nəticəsi belə görünən bir cədvəl olacaq:
İndi formula daha aydın oldu.
ƏHƏMİYYƏTLİ: Biz massiv düsturundan istifadə etdiyimiz üçün düsturu daxil etməyi bitirdikdən sonra proqrama niyyətlərimizi bildirmək üçün Ctrl+Shift+Enter düymələrini basın. Bu düymələr kombinasiyasını basdıqdan sonra proqram avtomatik olaraq formulun əvvəlində və sonunda əyri mötərizələri quraşdıracaq.
INDEX və MATCH funksiyalarından istifadə
Nəzərdən keçirəcəyimiz üçüncü üsul da massiv düsturundan istifadəni nəzərdə tutur və INDEX və SEARCH funksiyalarından istifadə edir.
Formula bu kimi görünəcək.
İNDEKS(D2:D13,MATCH(1,(B2:B13=G1)*(C2:C13=G2),0))
Bu düsturun hər bir hissəsinin nə etdiyinə baxaq.
Əvvəlcə MATCH(1;(B2:B13=G1)*(C2:C13=G2);0 funksiyasını nəzərdən keçirək. Bu halda, G1 xanasının dəyəri ardıcıl olaraq B2:B13 diapazonunda olan xanaların hər bir dəyəri ilə müqayisə edilir və dəyərlər uyğun gələrsə TRUE, uyğun gəlmirsə FALSE qaytarır. Eyni müqayisə G2 xanasının dəyəri və C2:C13 diapazonu ilə aparılır. Sonra TRUE və FALSE-dən ibarət bu massivlərin hər ikisini müqayisə edirik. TRUE * TRUE kombinasiyası bizə 1 (TRUE) nəticəsini verir. Əməliyyat prinsipini daha aydın izah etməyə kömək edəcək aşağıdakı şəkilə baxaq.
İndi hər iki şərti ödəyən sətrin harada yerləşdiyini deyə bilərik. MATCH funksiyası nəticə massivində 1-in mövqeyini tapır və 6-nı qaytarır, çünki 1 altıncı cərgədə baş verir. Sonra, INDEX funksiyası D2:D13 diapazonunun altıncı sırasının dəyərini qaytarır.
SUMPRODUCT istifadə
Ən güclü Excel düsturlarından biridir. Hətta bu düstura həsr olunmuş ayrıca məqaləm də var. Çoxlu şərtlərdən istifadə etmək üçün dördüncü üsulumuz SUMPRODUCT funksiyası ilə düstur yazmaqdır. Və belə görünəcək:
XÜSUSİYYƏT((B2:B13=G1)*(C2:C13=G2);D2:D13)
Bu formulun işləmə prinsipi əvvəlki yanaşmanın işləmə prinsipinə bənzəyir. G1 və G2 xanalarının dəyərlərinin müvafiq olaraq B2:B13 və C2:C13 diapazonları ilə müqayisə edildiyi virtual cədvəl yaradılır. Sonra bu massivlərin hər ikisi müqayisə edilir və birlər və sıfırlar massivi alınır, burada hər iki şərtin uyğun gəldiyi cərgəyə biri təyin edilir. Sonra bu virtual massiv D2:D13 diapazonuna vurulur. Virtual massivimizdə altıncı cərgədə yalnız bir 1 olacağı üçün düstur 189 nəticəsini qaytaracaq.
D2:D13 diapazonunda mətn dəyərləri varsa, bu funksiya işləməyəcək.
Bu formulun necə işlədiyini başa düşmək üçün SUMPROIZ funksiyası haqqında məqaləni oxumağı məsləhət görürəm.
NƏTİCƏ
Beləliklə, hansı üsuldan istifadə etməlisiniz? Onların hamısı etibarlı işləsə də, mən birinci üsula üstünlük verirəm. Gündəlik işimdə asan başa düşülən və dəyişən fayllarla işləməyə üstünlük verirəm. Bu tələblərin hər ikisi birinci yanaşmanın şərtlərinə cavab verir.
Düsturları daha yaxşı başa düşmək üçün bugünkü məqalədə onlara baxa bilərsiniz.
