Dəyərləri əvəz etmək üçün VLOOKUP funksiyasından istifadə edin

Kim çox tənbəldir və ya oxumağa vaxtı yoxdur - videoya baxın. Təfərrüatlar və nüanslar aşağıdakı mətndədir.

Problemin formalaşdırılması

Beləliklə, iki masamız var - sifariş masası и qiymət cədvəli:

Vəzifə, məhsulun adına diqqət yetirərək qiymətləri qiymət siyahısından sifarişlər cədvəlinə avtomatik olaraq əvəz etməkdir ki, daha sonra dəyəri hesablaya biləsiniz.

Həll

Excel funksiya dəstində, kateqoriya altında İstinadlar və massivlər (Axtarış və istinad) funksiyası var VPR (BAXIN).Bu funksiya yuxarıdan aşağıya doğru hərəkət edən göstərilən cədvəlin (qiymət siyahısı) ən sol sütununda verilmiş dəyəri axtarır (bizim nümunəmizdə bu "Alma" sözüdür) və onu tapdıqdan sonra bitişik hüceyrənin məzmununu göstərir. (23 rubl) .Sxematik olaraq bu funksiyanın işləməsi belə göstərilə bilər:

Funksiyadan sonrakı istifadəni asanlaşdırmaq üçün bir anda bir şey edin - qiymət siyahısındakı xanaların diapazonuna öz adınızı verin. Bunu etmək üçün, "başlıq" (G3: H19) istisna olmaqla, qiymət siyahısının bütün xanalarını seçin, menyudan seçin Daxil et - Ad - Təyin et (Daxil et - Ad - Müəyyən et) və ya basın CTRL + F3 kimi hər hansı bir ad (boşluq yoxdur) daxil edin qiymət… İndi, gələcəkdə, siz qiymət siyahısına keçid üçün bu addan istifadə edə bilərsiniz.

İndi funksiyadan istifadə edirik VPR… Daxil ediləcək xananı seçin (D3) və nişanı açın Düsturlar - Funksiya daxil edilməsi (Düsturlar — Funksiya daxil edin)… Kateqoriyada İstinadlar və massivlər (Axtarış və İstinad) funksiyasını tapın VPR (BAXIN) və mətbuat OK… Funksiya üçün arqumentlərin daxil edilməsi üçün pəncərə görünəcək:

Dəyərləri əvəz etmək üçün VLOOKUP funksiyasından istifadə edin

Onları növbə ilə doldururuq:

  • İstənilən dəyər (Axtarış Dəyəri) – funksiyanın qiymət siyahısının ən sol sütununda tapmalı olduğu məhsulun adı. Bizim vəziyyətimizdə B3 xanasından "Alma" sözü.
  • Masa (Cədvəl Massivi) – istədiyiniz dəyərlərin uXNUMXbuXNUMX götürüldüyü cədvəl, yəni bizim qiymət siyahımız. İstinad üçün biz daha əvvəl verilmiş öz “Qiymət” adımızdan istifadə edirik. Əgər ad verməmisinizsə, sadəcə cədvəli seçə bilərsiniz, amma düyməsinə basmağı unutmayın F4linki dollar işarələri ilə bağlamaq, çünki əks halda düsturumuzu D3:D30 sütununda qalan xanalara köçürərkən aşağı sürüşəcək.
  • Sütun_nömrəsi (Sütun indeksinin nömrəsi) – qiymət cədvəlində qiymət dəyərlərini götürəcəyimiz sütunun seriya nömrəsi (hərf deyil!). Qiymət cədvəlinin adları olan birinci sütunu 1 nömrəlidir, ona görə də bizə 2 nömrəli sütundan qiymət lazımdır.
  • interval_axtar (Rəkanı Axtarış) – bu sahəyə yalnız iki dəyər daxil edilə bilər: FALSE və ya TRUE:
      • Dəyər daxil edilərsə 0 or YALAN (YANLIŞ), onda əslində bu o deməkdir ki, yalnız axtarışa icazə verilir tam uyğunluq, yəni funksiya qiymət siyahısında sifariş cədvəlində göstərilən qeyri-standart elementi tapmasa (məsələn, “Kokos” daxil edilərsə), #N/A (məlumat yoxdur) xətası yaradacaq.
      • Dəyər daxil edilərsə 1 or TRUE (DOĞRU), onda bu o deməkdir ki, siz dəqiq deyil, axtarışa icazə verirsiniz təxmini uyğunluq, yəni “kokos” vəziyyətində funksiya “kokos”a mümkün qədər yaxın olan ada malik məhsul tapmağa və bu adın qiymətini qaytarmağa çalışacaq. Əksər hallarda, belə təxmini əvəzetmə əslində orada olan yanlış məhsulun dəyərini əvəz etməklə istifadəçiyə oyun oynaya bilər! Beləliklə, əksər real biznes problemləri üçün təxmini axtarışa icazə verməmək yaxşıdır. İstisna mətni deyil, rəqəmləri axtardığımız zamandır – məsələn, Addım Endirimlərini hesablayarkən.

Hər şey! Qalır basmaq OK və daxil edilmiş funksiyanı bütün sütuna kopyalayın.

# N / A səhvləri və onların aradan qaldırılması

Function VPR (BAXIN) #N/A xətası qaytarır (#Yoxdur) əgər a:

  • Dəqiq axtarış aktivdir (arqument Interval görünüşü = 0) və istədiyiniz ad yoxdur Masa.
  • Kobud axtarış daxildir (Interval görünüşü = 1), Amma MasaAxtarışın aparıldığı , adların artan sırasına görə sıralanmır.
  • Adın tələb olunan dəyərinin gəldiyi xananın formatı (məsələn, bizim vəziyyətimizdə B3) və cədvəlin birinci sütununun xanalarının formatı (F3: F19) fərqlidir (məsələn, rəqəm və mətn). ). Bu hal xüsusilə mətn adları əvəzinə rəqəmli kodlardan (hesab nömrələri, identifikatorlar, tarixlər və s.) istifadə edərkən xarakterikdir. Bu vəziyyətdə funksiyalardan istifadə edə bilərsiniz Ч и TEXT məlumat formatlarını çevirmək üçün. Bu kimi bir şey görünəcək:

    =VLOOKUP(MƏTN(B3),qiymət,0)

    Bu barədə ətraflı burada oxuya bilərsiniz.

  • Funksiya tələb olunan dəyəri tapa bilmir, çünki kodda boşluqlar və ya görünməz çap olunmayan simvollar (sətir fasilələri və s.) var. Bu halda mətn funksiyalarından istifadə edə bilərsiniz TRIM (TRIM) и ÇAP(TƏMİZ) onları aradan qaldırmaq üçün:

    =VLOOKUP(TRIMSPACES(TƏMİZ(B3)),qiymət,0)

    =VLOOKUP(TRIM(TƏMİZ(B3));qiymət;0)

Səhv mesajını basdırmaq üçün # N / A (#Yoxdur) funksiyanın dəqiq uyğunluğu tapa bilmədiyi hallarda funksiyadan istifadə edə bilərsiniz XƏTA (İFRAR)… Beləliklə, məsələn, bu konstruksiya VLOOKUP tərəfindən yaradılan hər hansı səhvləri tutur və onları sıfırlarla əvəz edir:

= SƏHTA (VLOOKUP (B3, qiymət, 2, 0), 0)

= SƏHTA (VLOOKUP (B3; qiymət; 2; 0); 0)

PS

Bir dəyəri deyil, bütün dəsti bir anda çıxarmaq lazımdırsa (bir neçə fərqli varsa), o zaman massiv düsturu ilə şamanlaşmalı olacaqsınız. və ya Office 365-dən yeni XLOOKUP funksiyasından istifadə edin.

 

  • VLOOKUP funksiyasının təkmilləşdirilmiş versiyası (VLOOKUP 2).
  • VLOOKUP funksiyasından istifadə edərək addım (aralıq) endirimlərinin sürətli hesablanması.
  • INDEX və MATCH funksiyalarından istifadə edərək "sol VLOOKUP" necə etmək olar
  • Siyahıdakı məlumatlarla formaları doldurmaq üçün VLOOKUP funksiyasından necə istifadə etmək olar
  • Cədvəldən birincini deyil, bütün dəyərləri bir anda necə çıxarmaq olar
  • PLEX əlavəsindən VLOOKUP2 və VLOOKUP3 funksiyaları

 

Cavab yaz