Ən yaxın nömrəni tapmaq

Mündəricat

Təcrübədə çox tez-tez belə hallar olur ki, siz və mən verilmiş ədədə münasibətdə çoxluqda (cədvəldə) ən yaxın dəyəri tapmaq lazımdır. Bu, məsələn, ola bilər:

Həcmdən asılı olaraq endirimin hesablanması.
Planın icrasından asılı olaraq mükafatların məbləğinin hesablanması.
Məsafədən asılı olaraq göndərmə tariflərinin hesablanması.
Mallar üçün uyğun qabların seçilməsi və s.

Üstəlik, vəziyyətdən asılı olaraq yuvarlaqlaşdırma həm yuxarı, həm də aşağı tələb oluna bilər.

Belə bir problemi həll etməyin bir neçə yolu var - aşkar və o qədər də açıq deyil. Gəlin onlara ardıcıl olaraq baxaq.

Başlamaq üçün, topdansatışda endirimlər verən bir təchizatçı təsəvvür edək və endirimin faizi alınan malların miqdarından asılıdır. Məsələn, 5 ədəddən çox alanda 2% endirim verilir, 20 ədəddən alanda isə artıq 6% və s.

Alınan malların miqdarını daxil edərkən endirim faizini necə tez və gözəl hesablamaq olar?

Metod 1: İç-içə IF-lər

“Düşünmək üçün nə var – tullanmaq lazımdır!” seriyasından bir üsul. Yuvalanmış funksiyalardan istifadə IF (Əgər) xana dəyərinin intervalların hər birinə düşdüyünü ardıcıl olaraq yoxlamaq və müvafiq diapazon üçün endirim göstərmək. Ancaq bu vəziyyətdə düstur çox çətin ola bilər:

Ən yaxın nömrəni tapmaq

Düşünürəm ki, belə bir "canavar kuklası" nı aradan qaldırmaq və ya bir müddət sonra ona bir neçə yeni şərt əlavə etməyə çalışmağın əyləncəli olduğu aydındır.

Bundan əlavə, Microsoft Excel-də IF funksiyası üçün yuva limiti var - köhnə versiyalarda 7 dəfə və yeni versiyalarda 64 dəfə. Daha çox ehtiyacınız olsa nə olar?

Metod 2. İnterval görünüşü ilə VLOOKUP

Bu üsul daha yığcamdır. Endirim faizini hesablamaq üçün əfsanəvi funksiyadan istifadə edin VPR (BAXIN) təxmini axtarış rejimində:

Ən yaxın nömrəni tapmaq

hara

B4 – endirim axtardığımız ilk əməliyyatda malların miqdarının dəyəri
$G$4:$H$8 – endirim cədvəlinə keçid – “başlıq” olmadan və $ işarəsi ilə qeyd olunan ünvanlarla.
2 — endirim cədvəlində endirim dəyərini almaq istədiyimiz sütunun sıra nömrəsi
TRUE – bu, “it”in dəfn olunduğu yerdir. Əgər sonuncu funksiya arqumenti kimi VPR müəyyənləşdirin YALAN (YANLIŞ) və ya 0, onda funksiya axtaracaq ciddi uyğunluq kəmiyyət sütununda (və bizim vəziyyətimizdə bu, #N/A xətası verəcək, çünki endirim cədvəlində 49 dəyəri yoxdur). Amma əvəzinə YALAN yazmaq TRUE (DOĞRU) və ya 1, onda funksiya dəqiq deyil, ancaq axtaracaq ən yaxın ən kiçik dəyər və bizə lazım olan endirim faizini verəcək.

Bu metodun mənfi tərəfi endirim cədvəlini birinci sütuna görə artan qaydada çeşidləmək ehtiyacıdır. Əgər belə bir çeşidləmə yoxdursa (və ya tərs qaydada aparılırsa), onda düsturumuz işləməyəcək:

Ən yaxın nömrəni tapmaq

Müvafiq olaraq, bu yanaşma yalnız ən yaxın ən kiçik dəyəri tapmaq üçün istifadə edilə bilər. Əgər sizə ən yaxın ən böyüyünü tapmaq lazımdırsa, onda fərqli bir yanaşma istifadə etməlisiniz.

Metod 3. INDEX və MATCH funksiyalarından istifadə edərək ən yaxın böyüyün tapılması

İndi problemimizə digər tərəfdən baxaq. Tutaq ki, biz müxtəlif tutumlu sənaye nasoslarının bir neçə modelini satırıq. Soldakı satış cədvəli müştərinin tələb etdiyi gücü göstərir. Ən yaxın maksimum və ya bərabər gücə malik, lakin layihənin tələb etdiyindən az olmayan bir nasos seçməliyik.

VLOOKUP funksiyası burada kömək etməyəcək, ona görə də onun analoqundan - bir dəstə INDEX funksiyasından istifadə etməli olacaqsınız. (İNDEKS) və DAHA FAZLA (MAÇ):

Ən yaxın nömrəni tapmaq

Burada son arqumenti -1 olan MATCH funksiyası ən yaxın ən böyük dəyəri tapmaq rejimində işləyir və INDEX funksiyası daha sonra qonşu sütundan bizə lazım olan model adını çıxarır.

Metod 4. Yeni funksiya VIEW (XLOOKUP)

Bütün yeniləmələri quraşdırılmış Office 365 versiyası varsa, VLOOKUP əvəzinə (BAXIN) onun analoqu olan VIEW funksiyasından istifadə edə bilərsiniz (XALQIMLAMA), mən artıq ətraflı təhlil etdim:

Ən yaxın nömrəni tapmaq

Burada:

B4 – endirim axtardığımız məhsulun miqdarının ilkin dəyəri
$G$4:$G$8 – uyğunluq axtardığımız diapazon
$H$4:$H$8 – endirimi geri qaytarmaq istədiyiniz nəticələr diapazonu
dördüncü arqument (-1) dəqiq uyğunluq əvəzinə istədiyimiz ən yaxın ən kiçik ədədin axtarışını ehtiva edir.

Bu metodun üstünlükləri ondan ibarətdir ki, endirim cədvəlini çeşidləməyə ehtiyac yoxdur və lazım olduqda yalnız ən yaxın ən kiçik deyil, həm də ən yaxın ən böyük dəyəri axtarmaq imkanı var. Bu vəziyyətdə son arqument 1 olacaq.

Təəssüf ki, hələ hər kəs bu xüsusiyyətə malik deyil - yalnız Office 365-in xoşbəxt sahibləri.

Metod 5. Power Query

Əgər Excel üçün güclü və tamamilə pulsuz Power Query əlavəsi ilə hələ tanış deyilsinizsə, onda siz buradasınız. Əgər siz artıq tanışsınızsa, o zaman problemimizi həll etmək üçün ondan istifadə etməyə çalışaq.

Əvvəlcə bəzi hazırlıq işləri aparaq:

Klaviatura qısa yolundan istifadə edərək mənbə cədvəllərimizi dinamika (ağıllı) çevirək Ctrl+T və ya komanda Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format).
Aydınlıq üçün onlara ad verək. Satış и Endirimlər nişanı konstruktor (Dizayn).
Düyməni istifadə edərək cədvəllərin hər birini növbə ilə Power Query-ə yükləyin Cədvəldən/Aralıqdan nişanı Tarix (Məlumat - Cədvəldən/aralıqdan). Excelin son versiyalarında bu düymənin adı dəyişdirilib Yarpaqları ilə (vərəqdən).
Cədvəllərdə nümunəmizdə olduğu kimi kəmiyyətlərlə fərqli sütun adları varsa (“Malların miqdarı” və “Malların miqdarı…”), o zaman Power Query-də onların adı dəyişdirilməli və eyni adlandırılmalıdır.
Bundan sonra Power Query redaktoru pəncərəsində əmri seçməklə Excel-ə qayıda bilərsiniz Əsas səhifə — Bağlayın və Yükləyin — Bağlayın və Yükləyin… (Ev — Bağla&Yüklə — Bağla&Yüklə...) və sonra seçim Sadəcə əlaqə yaradın (Yalnız əlaqə yaradın).
Sonra ən maraqlısı başlayır. Əgər Power Query-də təcrübəniz varsa, güman edirəm ki, sonrakı düşüncə xətti əvvəlki üsulda olduğu kimi bu iki cədvəlin birləşmə sorğusu (birləşmə) və VLOOKUP ilə birləşdirilməsi istiqamətində olmalıdır. Əslində, biz ilk baxışdan heç də aydın olmayan əlavə rejimində birləşməli olacağıq. Excel sekmesinde seçin Məlumat - Məlumat əldə edin - Sorğuları birləşdirin - Əlavə edin (Məlumat - Məlumat əldə et - Sorğuları birləşdir - Əlavə et) və sonra masalarımız Satış и Endirimlər görünən pəncərədə:
Klikdən sonra OK masalarımız bir-birinin altına yapışdırılacaq. Nəzərə alın ki, bu cədvəllərdə malların miqdarı olan sütunlar bir-birinin altına düşür, çünki. eyni ada malikdirlər:
Satış cədvəlindəki sətirlərin orijinal ardıcıllığı sizin üçün vacibdirsə, bütün sonrakı dəyişikliklərdən sonra onu bərpa edə bilsəniz, əmrdən istifadə edərək cədvəlimizə nömrələnmiş sütun əlavə edin. Sütun əlavə etmək - İndeks Sütunu (Sütun əlavə et - İndeks sütunu). Sətirlərin ardıcıllığı sizin üçün əhəmiyyət kəsb etmirsə, bu addımı atlaya bilərsiniz.
İndi cədvəlin başlığında açılan siyahıdan istifadə edərək onu sütuna görə çeşidləyin kəmiyyət Artan:
Və əsas hiylə: sütun başlığına sağ vurun Endirim komanda seçin Doldur - Aşağı (Doldur - Aşağı). ilə boş xanalar null avtomatik olaraq əvvəlki endirim dəyərləri ilə doldurulur:
Sütunlara görə sıralamaqla sətirlərin orijinal ardıcıllığını bərpa etmək qalır indeks (sonra təhlükəsiz şəkildə silə bilərsiniz) və filtrlə lazımsız xətlərdən xilas olun null sütun üzrə Əməliyyat kodu: