Mündəricat
Bütün klassik axtarış və növ əvəzetmə funksiyaları VPR (BAXIN), GPR (HLOOKUP), DAHA FAZLA (MAÇ) və onlar kimilərin bir mühüm xüsusiyyəti var – onlar əvvəldən axıra qədər, yəni mənbə məlumatında soldan sağa və ya yuxarıdan aşağıya doğru axtarış aparırlar. İlk uyğunluq tapılan kimi axtarış dayandırılır və yalnız bizə lazım olan elementin ilk dəfə rast gəlinməsi tapılır.
İlk deyil, son hadisəni tapmaq lazımdırsa nə etməli? Məsələn, müştəri üçün son əməliyyat, son ödəniş, ən son sifariş və s.?
Metod 1: Massiv düsturu ilə axırıncı cərgənin tapılması
Orijinal cədvəldə bir sıra tarixi və ya seriya nömrəsi olan bir sütun yoxdursa (sifariş, ödəniş ...), onda bizim vəzifəmiz, əslində, verilmiş şərti ödəyən sonuncu sıranı tapmaqdır. Bunu aşağıdakı massiv düsturu ilə etmək olar:
Burada:
- Function IF (Əgər) sütundakı bütün xanaları bir-bir yoxlayır Müştəri və bizə lazım olan adı ehtiva edərsə, sətir nömrəsini göstərir. Vərəqdəki sətir nömrəsi funksiya tərəfindən bizə verilir LINE (SIRA), lakin cədvəldə sətir nömrəsinə ehtiyacımız olduğundan əlavə olaraq 1-i çıxmalıyıq, çünki cədvəldə başlığımız var.
- Sonra funksiya MAX (MAX) formalaşmış sıra nömrələri toplusundan maksimum dəyəri, yəni müştərinin ən son sətirinin nömrəsini seçir.
- Function İNDEKSİ (İNDEKS) hər hansı digər tələb olunan cədvəl sütunundan tapılan son nömrə ilə xananın məzmununu qaytarır (Sifariş kodu).
Bütün bunlar kimi daxil edilməlidir massiv formulu, yəni:
- Ən son yeniləmələr quraşdırılmış və dinamik massivlərə dəstək olan Office 365-də sadəcə basa bilərsiniz Daxil edin.
- Bütün digər versiyalarda düsturu daxil etdikdən sonra klaviatura qısa yolunu basmalı olacaqsınız Ctrl+ÜstKrkt+Daxil edin, bu avtomatik olaraq düstur çubuğunda ona əyri mötərizələr əlavə edəcək.
Metod 2: Yeni LOOKUP funksiyası ilə əks axtarış
Artıq yeni bir xüsusiyyət haqqında video ilə uzun bir məqalə yazdım VIEW (XALQIMLAMA), köhnə VLOOKUP-u əvəz etmək üçün Office-in ən son versiyalarında göründü (BAXIN). BROWSE köməyi ilə vəzifəmiz olduqca elementar şəkildə həll olunur, çünki. bu funksiya üçün (VLOOKUP-dan fərqli olaraq) axtarış istiqamətini açıq şəkildə təyin edə bilərsiniz: yuxarıdan aşağıya və ya aşağıdan yuxarı – onun son arqumenti (-1) buna cavabdehdir:
Metod 3. Ən son tarixlə sətir axtarın
Mənbə məlumatında seriya nömrəsi və ya oxşar rol oynayan bir tarix olan bir sütunumuz varsa, tapşırıq dəyişdirilir - uyğunluğu olan sonuncu (ən aşağı) xətti deyil, ən son ( maksimum) tarix.
Klassik funksiyalardan istifadə etməklə bunu necə edəcəyimi artıq ətraflı müzakirə etmişəm və indi yeni dinamik massiv funksiyalarının gücündən istifadə etməyə çalışaq. Daha gözəllik və rahatlıq üçün biz həmçinin klaviatura qısa yolundan istifadə edərək orijinal cədvəli “ağıllı” cədvələ çeviririk. Ctrl+T və ya əmrlər Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format).
Onların köməyi ilə bu “qatil cütlük” problemimizi çox zərif şəkildə həll edir:
Burada:
- Əvvəlcə funksiya FILTER (FİLTRE) cədvəlimizdən yalnız sütunda olan sətirləri seçir Müştəri - bizə lazım olan ad.
- Sonra funksiya GRADE (NÖV) seçilmiş cərgələri tarixə görə azalan qaydada, ən son sövdələşmə yuxarıda olmaqla çeşidləyir.
- Function İNDEKSİ (İNDEKS) birinci sıranı çıxarır, yəni bizə lazım olan son ticarəti qaytarır.
- Və nəhayət, xarici FILTER funksiyası əlavə 1-ci və 3-cü sütunları nəticələrdən çıxarır (Sifariş kodu и Müştəri) və yalnız tarix və məbləği buraxır. Bunun üçün sabitlər massivi istifadə olunur. {0;1;0;1}, hansı sütunların göstərilməsini istədiyimiz (1) və ya istəmədiyimiz (0) müəyyən edilir.
Metod 4: Power Query-də Son Uyğunluğun Tapılması
Yaxşı, tamlıq naminə Power Query əlavəsindən istifadə edərək əks axtarış problemimizin həllinə baxaq. Onun köməyi ilə hər şey çox tez və gözəl şəkildə həll olunur.
1. Klaviatura qısa yolundan istifadə edərək orijinal cədvəlimizi “ağıllı” cədvələ çevirək Ctrl+T və ya əmrlər Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format).
2. Düymə ilə Power Query-ə yükləyin Cədvəldən/Aralıqdan nişanı Tarix (Məlumat - Cədvəldən/Aralıqdan).
3. Cədvəlimizi tarixə görə azalan ardıcıllıqla (başlıqdakı filtrin açılan siyahısı vasitəsilə) çeşidləyirik ki, ən son əməliyyatlar yuxarıda olsun.
4… Nişanda Transformasiya komanda seçin Qrup tərəfindən (Transform - Qrup üzrə) və qruplaşdırmanı müştərilərə görə təyin edin və toplama funksiyası olaraq seçimi seçin Bütün xətlər (Bütün sıralar). Yeni sütunu istədiyiniz hər hansı bir ad verə bilərsiniz - məsələn Ətraflı.
Qruplaşdırdıqdan sonra biz müştərilərimizin unikal adlarının siyahısını və sütunda alacağıq Ətraflı – onların hər birinin bütün əməliyyatları olan cədvəllər, burada birinci sətir ən son əməliyyat olacaq, bizə lazım olan budur:
5. Düymə ilə yeni hesablanmış sütun əlavə edin Fərdi sütun nişanı Sütun əlavə edin (Sütun əlavə et - Fərdi sütun əlavə et)və aşağıdakı formula daxil edin:
Burada Ətraflı – bu, müştərilər tərəfindən cədvəllər aldığımız sütundur və 0 {} çıxarmaq istədiyimiz cərgənin nömrəsidir (Power Query-də sıra nömrələmə sıfırdan başlayır). Qeydləri olan bir sütun alırıq (rekord), burada hər giriş hər cədvəlin birinci cərgəsidir:
Sütun başlığında ikiqat oxları olan düymə ilə bütün qeydlərin məzmununu genişləndirmək qalır Son razılaşma istədiyiniz sütunların seçilməsi:
… və sonra artıq lazım olmayan sütunu silin Ətraflı başlığına sağ klikləməklə - Sütunları silin (Sütunları silin).
Nəticələri vərəqə yüklədikdən sonra Əsas səhifə — Bağlayın və yükləyin — Bağlayın və daxil edin (Ev - Bağla və Yüklə - Bağla və Yüklə...) İstədiyimiz kimi son əməliyyatların siyahısı ilə belə gözəl bir cədvəl alacağıq:
Mənbə məlumatlarını dəyişdirərkən, onların üzərinə sağ tıklayarak nəticələri yeniləməyi unutmamalısınız - əmr Yeniləyin və Saxlayın (Təzələmək) və ya klaviatura qısa yolu Ctrl+Dayandırmaq+F5.
- LOOKUP funksiyası VLOOKUP-un nəslindəndir
- SORT, FILTER və UNIC yeni dinamik massiv funksiyalarından necə istifadə etmək olar
- LOOKUP funksiyası ilə sətir və ya sütunda sonuncu boş olmayan xananın tapılması