Excel VLOOKUP ilə birdən çox məlumat sahəsi tapın

Excel-in VLOOKUP funksiyasını COLUMN funksiyası ilə birləşdirərək bir verilənlər bazasının və ya məlumatların cədvəlinin bir sıra birdən çox dəyərləri qaytarmağa imkan verən bir axtarış formulu yarada bilərsiniz.

Yuxarıdakı şəkildə göstərilən nümunədə, axtarış formulası, qiymət, parça nömrəsi və təchizatçı kimi müxtəlif dəyərlərə aid bütün dəyərləri geri qaytarmağa imkan verir.

01 ilə 10

Excel VLOOKUP ilə Birden çox Dəyərlər Dönüş

Aşağıda göstərilən addımlardan sonra, yuxarıda göstərilən görünüşlü bir formada, vahid məlumatdan birdən çox dəyər qayıdacaqlar.

Axtarış formulu COLUMN funksiyasının VLOOKUP içərisində içəri yerləşdirilməsini tələb edir.

Bir funksiyanı yerləşdirmək, birinci funksiyaya aid arqumentlərdən biri kimi ikinci funksiyaya daxildir.

Bu təlimatda, COLUMN funksiyası VLOOKUP üçün sütun indeksinin arqumenti kimi daxil edilir.

Tutorialdakı son addım seçilən hissəyə əlavə dəyərlər almaq üçün axtarış formulunun əlavə sütunlara köçürülməsini nəzərdə tutur.

Tutorial məzmunu

• Tutorial məlumatlarını daxil edin
• Verilənlər cədvəli üçün adlandırılmış bir sıra yaratmaq
• VLOOKUP funksiyasının başlanması
• Mütləq Hüceyrə Referanslarını istifadə edərək Axtarış Qiyməti Dəyərini Girin
• Cədvəl Array Arqumentini Girmək
• Daxili SOLUM funksiyasına daxil olmaq
• VLOOKUP funksiyasının tamamlanması
• Axtarış Formülünü Doldurma Tutumu ilə Kopyalayın
• Axtarış Formulası ilə məlumatın alınması

02/10

Tutorial məlumatlarını daxil edin

Tutorialdakı ilk addım məlumatları bir Excel səhifəsinə daxil etməkdir.

Tutorialdakı addımları izləmək üçün yuxarıda göstərilən məlumatları aşağıdakı hüceyrələrə daxil edin.

• D1-dən G1 hüceyrələrinə ən yaxşı məlumat daxil edin
• D4-dən G10 hüceyrələrinə ikinci sıra daxil edin

Axtarış meyarları və bu dərslik zamanı yaradılan axtarış formulu iş səhifəsinin 2-ci hissəsinə daxil edilir.

Tutorial şəkildəki görünüşü daxil etmir, lakin bu, axtarış formulunun necə işlədiyini təsir etmir.

Yuxarıda göstərilənlərə bənzər formatlaşdırma variantları barədə məlumatlar bu Əsas Excel Formatlaşdırma Təlimatında mövcuddur.

Tutorial addımlar

1. Veriyi yuxarıda göstərildiyi kimi D10-dən G10 hüceyrələrinə daxil edin

03/10

Verilənlər cədvəli üçün adlandırılmış bir sıra yaratmaq

Adlandırılmış bir sıra formulada bir sıra məlumatlara müraciət etmək üçün asan bir yoldur. Verilən məlumatların hüceyrə istinadlarına yazmaq əvəzinə, aralığın adını yaza bilərsiniz.

Adlandırılmış bir aralığın istifadə üçün ikinci bir üstünlük, formulun iş səhifəsində digər hüceyrələrə kopyalansa belə bu aralığa aid olan hüceyrə dəyişməyəcəyi.

Range adları, beləliklə, formülləri kopyalayarkən səhvlərin qarşısını almaq üçün mütləq hüceyrə istinadlarını istifadə etmək üçün alternativdir.

Qeyd: Aralığın adı, məlumatların başlıqlarını və ya sahə adlarını ehtiva etməz (satır 4), yalnız məlumatın özüdür.

Tutorial addımlar

1. Seçmək üçün iş səhifəsində D5-dən G10 hüceyrələrinə vurun
2. Sütun A-nin yuxarı hissəsində yerləşən Ad qutusuna basın
3. Ad qutusuna "Cədvəl" (heç bir tırnak) yazın
4. Klaviaturada ENTER düyməsini basın
5. D5-dən G10 hüceyrələri artıq "Cədvəl" adını daşıyırlar. VLOOKUP masa array argümanının adı dərslikdə daha sonra istifadə edəcəyik

04/10

VLOOKUP Dialoq qutusunu açmaq

Yalnız bir iş formundaki bir hüceyrəyə doğrudan axtarış formülümüzü daxil etmək mümkün olsa da, bir çox insanlar sintaksisini düz saxlamaq üçün çətin olur - xüsusilə də bu təlimatda istifadə etdiyimiz kompleks bir formul üçün.

Alternativ, bu halda VLOOKUP informasiya qutusunu istifadə etməkdir. Excel-in funksiyalarının demək olar ki, hamısı funksiyanın hər bir arqumentini ayrı bir xəttə daxil etməyə imkan verən bir əlaqə qutusuna malikdir.

Tutorial addımlar

1. İş səhifəsinin E2 hücresinə basın - iki ölçülü axtarış formulunun nəticələrinin nümayiş ediləcəyi yer
2. Şeridin Formüller sekmesini basın
3. Fonksiyanı açılan siyahı açmaq üçün lentdə Axtar & Reference seçimini basın
4. Funksiyanın dialoq qutusunu açmaq üçün siyahıda VLOOKUP düyməsinə vurun

05/10

Mütləq Hüceyrə Referanslarını istifadə edərək Axtarış Qiyməti Dəyərini Girin

Normalda, axtarış dəyəri data masasının birinci sütununda bir məlumat sahəsinə uyğun gəlir .

Bizim nümunəmizdə axtarış dəyəri informasiya tapmaq istədiyimiz hardware hissəsinin adını ifadə edir.

Axtarış dəyərinin icazə verilən növləri aşağıdakılardır:

• mətn məlumatları
• məntiqi dəyər (yalnız TRUE və ya FALSE)
• nömrə
• iş səhifəsində bir dəyərə hücum istinad edir

Bu nümunədə, partiyanın adı yerləşdiriləcəyi yerə hücum istinadına - D2 hücresinə daxil edəcəyik.

Mütləq Cell References

Tutorialdakı sonrakı bir addımda, E2 hücresində axtarış formulunu F2 və G2 hüceyrələrinə köçürəcəyik.

Normalda, formulalar Excel-də kopyalandığında, mobil istinadlar yeni yerlərini əks etdirmək üçün dəyişir.

Bu olarsa, D2 - axtarış dəyərinin hüceyrə istinadı - formulun F2 və G2 hüceyrələrində səhvlər yaradaraq kopyalanacağı kimi dəyişəcəkdir.

Səhvlərin qarşısını almaq üçün, D2-nin hüceyrə istinadını mütləq bir hüceyrə istinadına çevirəcəyik.

Düsturlar kopyalandıqda mütləq mobil istinadlar dəyişmir.

Mütləq mobil arayışlar klaviaturada F4 düyməsini basaraq yaradılar. Bunu etmək, $ D $ 2 kimi hüceyrə istinadına dolğunluq əlamətləri əlavə edir

Tutorial addımlar

1. Dialoq qutusunda lookup_value satırına basın
2. Lookup_value xəttinə bu hüceyrə istinad əlavə etmək üçün D2 hücresinə basın. Bu, biz istədiyimiz məlumatın adını yazacağımız hüceyrədir
3. Girmə nöqtəsini daşımadan, D2'yi $ D $ 2 mütləq hüceyrə istinadına çevirmək üçün klaviaturada F4 düyməsini basın
4. Tutorialdakı növbəti addım üçün VLOOKUP funksiyası informasiya qutusunu açıq buraxın

06-dan 10-a

Cədvəl Array Arqumentini Girmək

Cədvəl array, axtarış formulunun istədiyimiz məlumatı tapmaq üçün axtarış etdiyimiz məlumatlar cədvəlidir.

Cədvəl dizisində ən azı iki sütun məlumatı olmalıdır .

• ilk sütun , axtarış dəyəri arqumentini ehtiva edir (təlimatdakı əvvəlki addım)
• ikincisi və hər hansı əlavə sütunlar, biz göstərdiyimiz məlumatı tapmaq üçün axtarış formulası ilə aranacaq.

Cədvəl array argümanı verilənlər cədvəli üçün və ya bir sıra adı kimi hüceyrə istinadları olan bir sıra kimi daxil edilməlidir.

Bu nümunə üçün təlimatın 3-cü addımında yaradılan sıra adını istifadə edəcəyik.

Tutorial addımlar

1. Əlaqə qutusuna table_array satırına basın
2. Bu arqument üçün sıra adını daxil etmək üçün "Cədvəl" (tırnak) yazın
3. Tutorialdakı növbəti addım üçün VLOOKUP funksiyası informasiya qutusunu açıq buraxın

07/10

Sütun funksiyasını yerləşdirmək

Normal olaraq VLOOKUP məlumatların bir sütunundan məlumatları qaytarır və bu sütun sütun indeksinin arqumenti ilə təyin olunur.

Bununla birlikdə, bu nümunədə məlumatların geri qaytarılmasını istədiyimiz üç sütunumuz var, belə ki, sütun indeksinin nömrəsini bizim axtarış formulunu düzəltmədən asanlıqla dəyişdirmək üçün bir yola ehtiyacımız var.

Sütun işarəsi funksiyası daxil olduğu yerdir. Sütun indeksinin arqumenti olaraq daxil edilərək, axtarış formunu D2 hücresindən E2 və F2 hüceyrələrinə dərslikdə daha sonra kopyalanır.

Nesting funksiyaları

Buna görə COLUMN funksiyası VLOOKUP'un sütun indeks sayı argümanı olaraq çıxış edir .

Bu, VLOOKUP içərisində olan COLUMN funksiyasını, informasiya qutusunun Col_index_num xəttində yerləşdirməklə həyata keçirilir.

COLUMN funksiyasını Əl ilə daxil edin

Funksiyaları yuva edərkən, Excel bizə öz arqumentlərini daxil etmək üçün ikinci funksiyanın informasiya qutusunu açmağa icazə vermir.

Buna görə COLUMN funksiyası əl ilə Col_index_num xəttinə daxil edilməlidir.

Sütun işarəsi yalnız bir dəlil var - bir hüceyrə istinadı olan istinad arqumenti.

Sütun funksiyasının referans parametrini seçmək

COLUMN funksiyasının işi, verilənlər sütununu Referans argümanı olaraq qaytarmaqdır.

Başqa sözlə, sütun məktubunu A sütunu olan birinci sütun, ikinci sütun B və s kimi bir nöqtəyə çevirir.

Geri dönmək istədiyimiz ilk məlumat sahəsindəki elementin qiyməti - data sütununun iki sütununda olanı olduğundan, S sütununda hər hansı bir hüceyrənin hüceyrə istinadını referans arqumenti olaraq seçə bilərik. Col_index_num arqumenti.

Tutorial addımlar

1. VLOOKUP funksiyası informasiya qutusunda, Col_index_num satırına basın
2. İşlev adı sütunu yazın və sonra açıq dəyirmi bracket " ( "
3. Referans arqumenti olaraq bu hüceyrə istinadına daxil olmaq üçün iş səhifəsində B1 hücresinə basın
4. Sütun funksiyasını başa çatdırmaq üçün bir yekun dəyirmi bracket yazın " )
5. Tutorialdakı növbəti addım üçün VLOOKUP funksiyası informasiya qutusunu açıq buraxın

08/10

VLOOKUP Aralığı Axtarış Arqumentini daxil edin

VLOOKUP'un Range_lookup arqumenti, VLOOKUP-un Lookup_value-a tam və ya təxminən bir uyğunluğu tapmaq istəmədiyini göstərən məntiqi bir dəyərdir (TRUE və ya yalnız FALSE).

• TRUE və ya bu arqument çəkildikdə, VLOOKUP ya Lookup_value-ya dəqiq bir nəticə qaytarır və ya dəqiq bir uyğunlaşma yoxdursa, VLOOKUP növbəti ən böyük dəyəri qaytarır. Bunu etmək üçün formula üçün, Table_array'ın ilk sütunundaki məlumatlar artan sıraya görə sıralanmalıdır .
• FALSE, VLOOKUP yalnız Lookup_value-a tam uyğun istifadə edir. Axtarış dəyərinə uyğun olan Table_array'ın ilk sütununda iki və ya daha çox dəyər varsa, tapılan ilk dəyər istifadə olunur. Tam bir uyğunlaşma yoxdursa, # N / A səhv qaytarılır.

Bu təlimatda, müəyyən bir hardware maddələri haqqında dəqiq məlumatlar axtarır olduğumuzdan, Range_lookup'u False'a bərabərləşdirəcəyik.

Tutorial addımlar

1. Əlaqə qutusunda Range_lookup satırına basın
2. VLOOKUP-un istədiyimiz məlumatlar üçün tam uyğunluğu qaytarmasını istəyirik. Bu xəttdə saxta sözü yazın
3. Axtarış formulunu tamamlamaq və informasiya qutusunu bağlamaq üçün OK düyməsini basın
4. Hələ də D2 hücresinə daxil olan axtarış meyarlarına daxil edilməmiş olduğundan E2 hücresində # N / A səhv var
5. Öğreticinin son mərhələsində axtarış meyarlarını əlavə edəndə bu səhv düzəldiləcək

09/10

Axtarış Formülünü Doldurma Tutumu ilə Kopyalayın

Axtarış formulu bir dəfə data masasının birdən çox sütunundan verilən məlumatları almaq üçün nəzərdə tutulub.

Bunu etmək üçün, axtarış formulası məlumat istədiyimiz bütün sahələrdə olmalıdır.

Bu təlimatda data masasının 2, 3 və 4-cü sütunlarından məlumat almaq istəyirik. Bu, Lookup_value kimi bir hissə adı daxil edildikdə qiymət, parça nömrəsi və təchizatçı adıdır.

Verilər iş yerindəki müntəzəm bir nümunədə göstərildiyi üçün E2 hücresində axtarış formulunu F2 və G2 hüceyrələrinə köçürə bilərik.

Formulun kopyalandığı üçün, Excel funksiyasının (B1) funksiyasında nisbi hüceyrə istinadını formulanın yeni yerini əks etdirmək üçün yeniləyir.

Həm də Excel, mütləq hüceyrə istinadını dəyişmir $ D $ 2 və formulun kopyalandığında adlandırılmış aralık Cədvəl .

Excel-də məlumatların kopyalanmasının bir çox yolu var, amma yəqin ki, ən asan yol Doldurma Tutumu istifadə edir.

Tutorial addımlar

1. E2 hücresini - axtarış formulunun yerləşdiyi yerdə - aktiv hüceyrə etmək üçün basın
2. Siçan pointerini sağ alt köşedeki siyah kvadrat üzərinə qoyun. Göstərici " + " bir plus əlamətinə dəyişəcək - bu doldurma qabığıdır
3. Sol siçan düyməsini klikləyin və doldurma tutacaqını G2 hücresinə sürükləyin
4. Siçan düyməsini buraxın və F3 hücresi iki ölçülü axtarış formulunu ehtiva etməlidir
5. Düzgün həyata keçirildikdə, F2 və G2 hüceyrələri artıq E2 hücresində mövcud olan # N / A hatasını da ehtiva etməlidirlər

10-dan 10-a

Axtarış meyarlarına daxil olmaq

Axtarış formulu tələb olunan hüceyrələrə kopyalandıqdan sonra, məlumatların cədvəlindən məlumat almaq üçün istifadə edilə bilər.

Bunun üçün, Lookup_value hüceyrəsinə (D2) daxil etmək istədiyiniz elementin adını yazın və Klaviaturadan ENTER düyməsini basın.

Yapıldıktan sonra, axtarış formunu ehtiva edən hər bir hüceyrə, aradığınız hardware maddə haqqında fərqli bir məlumat ehtiva etməlidir.

Tutorial addımlar

1. Çalışma kitabında D2 hücresinə basın
2. Dizi D2 daxil Widget yazın və klaviatura ENTER düyməsini basın
3. E2-dən G2 hüceyrələrinə aşağıdakı məlumatlar göstərilməlidir:
   • E2 - $ 14.76 - bir widgetın qiyməti
   • F2 - PN-98769 - bir widget üçün parça nömrəsi
   • G2 - Widgets Inc - widget üçün təchizatçı adı
4. VLOOKUP array formulunu digər hissələrin adını D2 hücresinə yazaraq və E2-dən G2 hüceyrələrinə baxaraq test edin.

#REF kimi bir səhv mesajı varsa ! E2, F2 və ya G2 hüceyrələrində görünür, bu VLOOKUP səhv mesajlarının siyahısı problemin yalan olduğunu müəyyən etməyə kömək edə bilər.