Mundarija
Barcha klassik qidirish va turlarni almashtirish funktsiyalari VPR (KO'RISH), Ushbu o'lchovlari (QO'SHISH), KO'PROQ (MATCH) va ularga o'xshashlar bitta muhim xususiyatga ega - ular boshidan oxirigacha, ya'ni manba ma'lumotlarida chapdan o'ngga yoki yuqoridan pastgacha qidirishadi. Birinchi mos keladigan moslik topilgandan so'ng, qidiruv to'xtaydi va faqat bizga kerak bo'lgan elementning birinchi paydo bo'lishi topiladi.
Agar birinchi emas, balki oxirgi hodisani topishimiz kerak bo'lsa, nima qilish kerak? Masalan, mijoz uchun oxirgi tranzaksiya, oxirgi to'lov, eng so'nggi buyurtma va boshqalar?
1-usul: Massiv formulasi yordamida oxirgi qatorni topish
Agar asl jadvalda sana yoki qatorning seriya raqami bo'lgan ustun bo'lmasa (buyurtma, to'lov ...), unda bizning vazifamiz, aslida, berilgan shartni qondiradigan oxirgi qatorni topishdir. Buni quyidagi massiv formulasi yordamida amalga oshirish mumkin:
Bu yerga:
- vazifa IF (Agar) ustundagi barcha kataklarni birma-bir tekshiradi Mijozlar va agar u bizga kerak bo'lgan nomni o'z ichiga olgan bo'lsa, qator raqamini ko'rsatadi. Varaqdagi satr raqami bizga funksiya tomonidan beriladi LINE (QATR), lekin jadvaldagi qator raqami kerak bo'lganligi sababli, biz qo'shimcha ravishda 1 ni ayirishimiz kerak, chunki bizda jadvalda sarlavha mavjud.
- Keyin funksiya MAX (MAX) shakllangan qator raqamlari to'plamidan maksimal qiymatni, ya'ni mijozning eng so'nggi qatori sonini tanlaydi.
- vazifa INDEKS (INDEKS) Topilgan oxirgi raqam bilan yacheyka mazmunini boshqa istalgan jadval ustunidan qaytaradi (Buyurtma kodi).
Bularning barchasi sifatida kiritilishi kerak massiv formulasi, ya'ni:
- Eng so'nggi yangilanishlar o'rnatilgan va dinamik massivlarni qo'llab-quvvatlaydigan Office 365-da siz shunchaki bosishingiz mumkin kiriting.
- Boshqa barcha versiyalarda, formulani kiritgandan so'ng, klaviatura yorlig'ini bosishingiz kerak bo'ladi Ctrl+smena+kiriting, bu formulalar qatorida unga avtomatik ravishda jingalak qavslar qo'shadi.
2-usul: Yangi QIDIRISh funksiyasi bilan teskari qidirish
Men allaqachon yangi xususiyat haqida video bilan uzoq maqola yozdim VIEW (XIZASH), eski VLOOKUP o'rnini bosuvchi Office-ning so'nggi versiyalarida paydo bo'ldi (KO'RISH). BROWSE yordamida bizning vazifamiz juda oddiy hal qilinadi, chunki. ushbu funktsiya uchun (VLOOKUP-dan farqli o'laroq) siz qidiruv yo'nalishini aniq belgilashingiz mumkin: yuqoridan pastga yoki pastdan yuqoriga - uning oxirgi argumenti (-1) bunga javob beradi:
3-usul. Oxirgi sana bilan qatorni qidiring
Agar manba ma'lumotlarida bizda seriya raqami yoki shunga o'xshash rol o'ynaydigan sana bo'lgan ustun bo'lsa, unda vazifa o'zgartiriladi - biz mos keladigan oxirgi (eng past) qatorni emas, balki oxirgi ( maksimal) sana.
Klassik funksiyalar yordamida buni qanday qilishni men allaqachon batafsil muhokama qildim va endi yangi dinamik massiv funksiyalarining kuchidan foydalanishga harakat qilaylik. Ko'proq go'zallik va qulaylik uchun biz klaviatura yorlig'i yordamida asl jadvalni "aqlli" jadvalga aylantiramiz. Ctrl+T yoki buyruqlar Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash).
Ularning yordami bilan bu "qotil juftlik" bizning muammomizni juda nozik tarzda hal qiladi:
Bu yerga:
- Avval funksiya Filtrni (FILTR) jadvalimizdan faqat ustundagi satrlarni tanlaydi Mijozlar - bizga kerak bo'lgan ism.
- Keyin funksiya sinf (SARALASH) tanlangan qatorlarni sana bo‘yicha kamayish tartibida tartiblaydi, eng so‘nggi bitim tepada joylashgan.
- vazifa INDEKS (INDEKS) birinchi qatorni chiqaradi, ya'ni bizga kerak bo'lgan oxirgi savdoni qaytaradi.
- Va nihoyat, tashqi FILTER funktsiyasi natijalardan qo'shimcha 1 va 3-ustunlarni olib tashlaydi (Buyurtma kodi и Mijozlar) va faqat sana va miqdorni qoldiradi. Buning uchun doimiylar massivi ishlatiladi. {0;1;0;1}, qaysi ustunlarni ko'rsatishni xohlayotganimizni (1) yoki xohlamasligimizni (0) aniqlash.
4-usul: Power Query-da oxirgi moslikni topish
To'liqlik uchun keling, Power Query plaginidan foydalangan holda teskari qidiruv muammomizning yechimini ko'rib chiqaylik. Uning yordami bilan hamma narsa juda tez va chiroyli tarzda hal qilinadi.
1. Keling, klaviatura yorlig'i yordamida asl jadvalimizni "aqlli" jadvalga aylantiraylik Ctrl+T yoki buyruqlar Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash).
2. Uni tugma yordamida Power Query-ga yuklang Jadval/diapazondan tab ma'lumotlar (Ma'lumotlar - Jadvaldan/diapazondan).
3. Biz jadvalimizni (sarlavhadagi filtrning ochiladigan ro'yxati orqali) sana bo'yicha kamayish tartibida saralaymiz, shunda eng so'nggi tranzaksiyalar tepada bo'ladi.
4… Yorliqda O'tkazish jamoani tanlang Guruh tomonidan (O'zgartirish - Guruh bo'yicha) va mijozlar bo'yicha guruhlashni o'rnating va yig'ish funktsiyasi sifatida variantni tanlang Barcha qatorlar (Barcha qatorlar). Yangi ustunga o'zingiz yoqtirgan har qanday nom berishingiz mumkin - masalan Tafsilotlar.
Guruhlashtirgandan so'ng, biz mijozlarimizning noyob nomlari ro'yxatini va ustunni olamiz Tafsilotlar - ularning har birining barcha tranzaksiyalari ko'rsatilgan jadvallar, bu erda birinchi qatorda so'nggi tranzaksiya bo'ladi, bu bizga kerak:
5. Tugma yordamida yangi hisoblangan ustun qo'shing Maxsus ustun tab Ustun qo'shish (Ustun qo'shish - Maxsus ustun qo'shish)va quyidagi formulani kiriting:
shu yerda Tafsilotlar – bu biz mijozlar tomonidan jadvallarni oladigan ustun va 0 {} - biz chiqarmoqchi bo'lgan qatorning raqami (Power Query-da qator raqamlash noldan boshlanadi). Biz yozuvlar bilan ustunni olamiz (rekord), bu erda har bir yozuv har bir jadvalning birinchi qatoridir:
Ustun sarlavhasidagi ikkita strelkali tugma bilan barcha yozuvlarning mazmunini kengaytirish qoladi Oxirgi bitim kerakli ustunlarni tanlash:
... va keyin kerak bo'lmagan ustunni o'chiring Tafsilotlar uning sarlavhasini o'ng tugmasini bosib - Ustunlarni olib tashlang (ustunlarni olib tashlash).
Natijalarni sahifaga yuklaganingizdan so'ng Bosh sahifa — Yopish va yuklash — Yopish va yuklash (Uy — Yopish va yuklash — Yopish va yuklash…) Biz xohlaganimizdek, so'nggi operatsiyalar ro'yxati bilan shunday chiroyli jadvalga ega bo'lamiz:
Manba ma'lumotlarini o'zgartirganda, natijalarni sichqonchaning o'ng tugmasi bilan yangilashni unutmasligingiz kerak - buyruq Yangilash va saqlash (yangilash) yoki klaviatura yorlig'i Ctrl+Alt+F5.
- LOOKUP funksiyasi VLOOKUP ning avlodidir
- SORT, FILTER va UNIC dinamik massivning yangi funksiyalaridan qanday foydalanish kerak
- QIDIRISh funksiyasi bilan qator yoki ustundagi oxirgi bo'sh bo'lmagan katakchani topish