Mündəricat
Bu yaxınlarda biz ən faydalı Excel funksiyalarından birinə məqalə həsr etdik VPR və onun verilənlər bazasından tələb olunan məlumatı iş vərəqi xanasına çıxarmaq üçün necə istifadə oluna biləcəyini göstərdi. Funksiya üçün iki istifadə halının olduğunu da qeyd etdik VPR və onlardan yalnız biri verilənlər bazası sorğuları ilə məşğul olur. Bu yazıda siz funksiyadan istifadə etməyin daha az tanınan başqa bir yolunu öyrənəcəksiniz VPR Excel-də.
Əgər bunu hələ etməmisinizsə, o zaman funksiya haqqında son məqaləni oxumağınızdan əmin olun VPR, çünki aşağıdakı bütün məlumatlar birinci məqalədə təsvir olunan prinsiplərlə artıq tanış olduğunuzu güman edir.
Verilənlər bazası, funksiyaları ilə işləyərkən VPR tapmaq istədiyimiz məlumatı (məsələn, məhsul kodu və ya müştəri identifikasiya nömrəsi) müəyyən etmək üçün istifadə edilən unikal identifikator ötürülür. Bu unikal kod verilənlər bazasında olmalıdır, əks halda VPR xəta barədə məlumat verəcək. Bu yazıda funksiyadan istifadənin bu üsuluna baxacağıq VPRid verilənlər bazasında ümumiyyətlə mövcud olmadıqda. Sanki funksiya VPR təxmini rejimə keçir və nəyisə tapmaq istədikdə bizə hansı məlumatları təqdim edəcəyimizi seçir. Müəyyən şəraitdə məhz bu lazımdır.
Həyatdan bir nümunə. Tapşırığı təyin etdik
Gəlin bu məqaləni real həyat nümunəsi ilə təsvir edək – geniş satış göstəriciləri əsasında komissiyaların hesablanması. Biz çox sadə bir seçimlə başlayacağıq və sonra problemin yeganə rasional həlli funksiyadan istifadə edənə qədər onu tədricən çətinləşdirəcəyik. VPR. Bizim uydurma tapşırığımızın ilkin ssenarisi belədir: əgər satıcı bir il ərzində 30000 dollardan çox satış qazanırsa, onun komissiyası 30% təşkil edir. Əks halda komissiya cəmi 20% təşkil edir. Onu cədvəl şəklində qoyaq:
Satıcı satış məlumatlarını B1 xanasına daxil edir və B2 xanasındakı düstur satıcının gözləyə biləcəyi düzgün komissiya dərəcəsini müəyyən edir. Öz növbəsində, əldə edilən dərəcə B3 xanasında satıcının almalı olduğu ümumi komissiyanı hesablamaq üçün istifadə olunur (sadəcə B1 və B2 xanalarını çoxaldır).
Cədvəlin ən maraqlı hissəsi B2 xanasındadır - bu, komissiya dərəcəsini təyin etmək üçün düsturdur. Bu formula adlı Excel funksiyası var IF (Əgər). Bu funksiya ilə tanış olmayan oxucular üçün onun necə işlədiyini izah edəcəyəm:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Vəziyyət hər ikisinin qiymətini alan funksiya arqumentidir GERÇEK KOD (DOĞRU) və ya SAXTA (YANLIŞ). Yuxarıdakı nümunədə B1 ifadəsi
B1-in B5-dən az olması doğrudurmu?
Və ya başqa cür deyə bilərsiniz:
İl ərzində satışların ümumi məbləğinin həddən az olması doğrudurmu?
Bu suala cavab versək YES (DOĞRU), sonra funksiya qayıdır doğrudursa dəyər (DOĞRU olarsa dəyər). Bizim vəziyyətimizdə bu, B6 xanasının dəyəri olacaq, yəni ümumi satışlar həddən aşağı olduqda komissiya dərəcəsi. sualına cavab versək YOX (YANLIŞ) sonra qayıdır yalan olarsa dəyər (YANLIŞ olduqda dəyər). Bizim vəziyyətimizdə bu, B7 xanasının dəyəridir, yəni ümumi satışlar həddən yuxarı olduqda komissiya dərəcəsidir.
Gördüyünüz kimi, 20000 dollarlıq ümumi satışları götürsək, B2 xanasında 20% komissiya dərəcəsi alırıq. $40000 dəyəri daxil etsək, komissiya dərəcəsi 30% dəyişəcək:
Bizim masamız belə işləyir.
Tapşırığı çətinləşdiririk
Gəlin işləri bir az da çətinləşdirək. Başqa bir həddi təyin edək: əgər satıcı 40000 dollardan çox qazanırsa, komissiya dərəcəsi 40%-ə qədər artır:
Hər şey sadə və aydın görünür, lakin B2 xanasındakı düsturumuz nəzərəçarpacaq dərəcədə mürəkkəbləşir. Formula diqqətlə baxsanız, funksiyanın üçüncü arqumentinin olduğunu görəcəksiniz IF (IF) başqa bir tam hüquqlu funksiyaya çevrildi IF (Əgər). Bu quruluşa funksiyaların bir-birinə yuvalanması deyilir. Excel məmnuniyyətlə bu konstruksiyalara icazə verir və hətta işləyirlər, lakin onları oxumaq və başa düşmək daha çətindir.
Biz texniki təfərrüatları - niyə və necə işlədiyini araşdırmayacağıq və iç-içə funksiyaların yazılmasının nüanslarına girməyəcəyik. Axı bu, funksiyaya həsr olunmuş məqalədir VPR, Excel üçün tam bələdçi deyil.
Nə olursa olsun, formula daha mürəkkəbləşir! Satışda 50 dollardan çox qazanan satıcılar üçün 50000% komissiya dərəcəsi üçün başqa bir variant təqdim etsək nə olacaq? Və kimsə 60000 dollardan çox satıbsa, 60% komissiya ödəyəcəkmi?
İndi B2 xanasındakı düstur, hətta səhvsiz yazılsa belə, tamamilə oxunmaz hala gəldi. Düşünürəm ki, layihələrində 4 səviyyəli yuvalama formullarından istifadə etmək istəyənlər azdır. Daha asan bir yol olmalıdır?!
Və belə bir yol var! Funksiya bizə kömək edəcək VPR.
Problemi həll etmək üçün VLOOKUP funksiyasını tətbiq edirik
Gəlin masamızın dizaynını bir qədər dəyişək. Biz bütün eyni sahələri və məlumatları saxlayacağıq, lakin onları yeni, daha yığcam şəkildə təşkil edəcəyik:
Bir az vaxt ayırın və yeni masaya əmin olun Qiymətləndirmə Cədvəli əvvəlki hədd cədvəli ilə eyni məlumatları ehtiva edir.
Əsas ideya funksiyadan istifadə etməkdir VPR cədvələ uyğun olaraq istədiyiniz tarif dərəcəsini müəyyən etmək Qiymətləndirmə Cədvəli satış həcmindən asılı olaraq. Nəzərə alın ki, satıcı cədvəldəki beş hədddən birinə bərabər olmayan məbləğə mal sata bilər. Məsələn, 34988 dollara sata bilərdi, amma belə bir məbləğ yoxdur. Gəlin görək funksiya necədir VPR belə bir vəziyyətin öhdəsindən gələ bilər.
VLOOKUP funksiyasının daxil edilməsi
B2 xanasını seçin (düsturumuzu daxil etmək istədiyimiz yerə) və tapın BAXIN Excel Funksiyaları Kitabxanasında (VLOOKUP): Formüller (formulalar) > Function Kitabxana (Funksiya Kitabxanası) > Axtarış və İstinad (İstinadlar və massivlər).
Dialoq qutusu görünür İş arqumentləri (Funksiya arqumentləri). Arqumentlərin dəyərlərini bir-bir doldururuq Axtarış_dəyəri (Axtarma_dəyəri). Bu nümunədə bu, B1 xanasından satışın ümumi məbləğidir. Kursoru sahəyə qoyun Axtarış_dəyəri (Axtarma_dəyəri) və B1 xanasını seçin.
Sonra, funksiyaları təyin etməlisiniz VPRməlumatları harada axtarmaq lazımdır. Bizim nümunəmizdə bu bir cədvəldir Qiymətləndirmə Cədvəli. Kursoru sahəyə qoyun Cədvəl_massivi (Cədvəl) və bütün cədvəli seçin Qiymətləndirmə Cədvəlibaşlıqlar istisna olmaqla.
Sonra, düsturumuzdan istifadə edərək hansı sütundan məlumat çıxaracağımızı müəyyən etməliyik. Cədvəlin ikinci sütununda olan komissiya dərəcəsi ilə maraqlanırıq. Buna görə də arqument üçün Col_index_num (Sütun_nömrəsi) 2 dəyərini daxil edin.
Və nəhayət, sonuncu arqumenti təqdim edirik - Aralığın_axtarması (Interval_axtar).
Mühüm: funksiyanın tətbiqinin iki yolu arasında fərq yaradan bu arqumentin istifadəsidir VPR. Verilənlər bazası ilə işləyərkən arqument Aralığın_axtarması (aralıq_axtarış) həmişə dəyərə malik olmalıdır SAXTA (YANLIŞ) dəqiq uyğunluğu axtarmaq üçün. Funksiyadan istifadəmizdə VPR, biz bu sahəni boş qoymalıyıq və ya dəyər daxil etməliyik GERÇEK KOD (DOĞRU). Bu seçimi düzgün seçmək son dərəcə vacibdir.
Daha aydın olması üçün təqdim edəcəyik GERÇEK KOD (DOĞRU) sahəsində Aralığın_axtarması (Interval_axtar). Baxmayaraq ki, sahəni boş qoysanız, bu səhv olmayacaq, çünki GERÇEK KOD onun standart dəyəridir:
Bütün parametrləri doldurduq. İndi basırıq OK, və Excel funksiya ilə bizim üçün düstur yaradır VPR.
Ümumi satış məbləği üçün bir neçə fərqli dəyərlə sınaqdan keçirsək, formulanın düzgün işlədiyinə əmin olacağıq.
Nəticə
Zaman funksiyası VPR verilənlər bazası ilə işləyir, arqument Aralığın_axtarması (aralıq_axtarış) qəbul etməlidir SAXTA (YANLIŞ). Və dəyər olaraq daxil edildi Axtarış_dəyəri (Axtarma_dəyəri) verilənlər bazasında mövcud olmalıdır. Başqa sözlə, tam uyğunluq axtarır.
Bu məqalədə baxdığımız nümunədə dəqiq uyğunluq əldə etməyə ehtiyac yoxdur. Bu funksiya nə zaman belədir VPR istədiyiniz nəticəni qaytarmaq üçün təxmini rejimə keçməlidir.
Misal üçün: Satış həcmi 34988 dollar olan bir satıcı üçün komissiya hesablamasında hansı tarifdən istifadə edəcəyimizi müəyyən etmək istəyirik. Funksiya VPR bizə 30% dəyər qaytarır, bu tamamilə doğrudur. Bəs niyə düstur 30% və ya 20% deyil, tam olaraq 40% olan cərgəni seçdi? Təxmini axtarış dedikdə nə nəzərdə tutulur? Gəlin aydın olaq.
Mübahisə olanda Aralığın_axtarması (interval_lookup) dəyəri var GERÇEK KOD (DOĞRU) və ya buraxılmış funksiya VPR birinci sütunda təkrarlanır və axtarış dəyərindən çox olmayan ən böyük dəyəri seçir.
Mühüm məqam: Bu sxemin işləməsi üçün cədvəlin birinci sütunu artan qaydada sıralanmalıdır.