Мазмұны
Барлық классикалық іздеу және түр ауыстыру функциялары VPR (КӨРУ), GPR (ТОҚТАУ), КӨБІРЕК АШЫҚ (МАТЧ) және олар сияқтылардың бір маңызды ерекшелігі бар – олар басынан аяғына дейін, яғни бастапқы деректерде солдан оңға немесе жоғарыдан төменге қарай іздейді. Бірінші сәйкестік табылған бойда іздеу тоқтатылады және бізге қажет элементтің бірінші рет пайда болуы ғана табылады.
Бірінші емес, соңғы оқиғаны табу керек болса, не істеу керек? Мысалы, клиент үшін соңғы транзакция, соңғы төлем, ең соңғы тапсырыс және т.б.?
1-әдіс: Массив формуласымен соңғы жолды табу
Егер бастапқы кестеде күні немесе жолдың реттік нөмірі бар баған болмаса (тапсырыс, төлем ...), онда біздің міндетіміз, шын мәнінде, берілген шартты қанағаттандыратын соңғы жолды табу болып табылады. Мұны келесі массив формуласымен жасауға болады:
Мұнда:
- функция IF (Егер) бағандағы барлық ұяшықтарды бір-бірден тексереді сатып алушы және ол бізге қажет атауды қамтыса, жол нөмірін көрсетеді. Парақтағы жол нөмірі бізге функция арқылы беріледі ТҮЗУ (ҚАТАР), бірақ бізге кестеде жол нөмірі қажет болғандықтан, біз қосымша 1-ді алып тастауымыз керек, өйткені кестеде тақырып бар.
- Содан кейін функция MAX (МАКС) жол нөмірлерінің қалыптасқан жиынынан максималды мәнді, яғни клиенттің ең соңғы жолының нөмірін таңдайды.
- функция INDEX (ИНДЕКС) табылған соңғы саны бар ұяшықтың мазмұнын кез келген басқа қажетті кесте бағанынан қайтарады (Тапсырыс коды).
Мұның барлығы ретінде енгізілуі керек массив формуласы, яғни:
- Соңғы жаңартулары орнатылған және динамикалық массивтерге қолдау көрсететін Office 365 жүйесінде жай ғана басуға болады кіру.
- Барлық басқа нұсқаларда формуланы енгізгеннен кейін пернелер тіркесімін басу керек Ctrl+ауысым+кіру, ол формула жолағында оған бұйра жақшаларды автоматты түрде қосады.
2-әдіс: Жаңа ІЗДЕУ функциясымен кері іздеу
Мен жаңа мүмкіндік туралы бейнемен ұзақ мақала жаздым VIEW (ТҰРАҚТАУ), ескі VLOOKUP ауыстыру үшін Office бағдарламасының соңғы нұсқаларында пайда болды (КӨРУ). BROWSE көмегімен біздің міндетіміз қарапайым түрде шешіледі, өйткені. бұл функция үшін (VLOOKUP қарағанда) іздеу бағытын нақты орнатуға болады: жоғарыдан төменге немесе төменнен жоғарыға – оның соңғы аргументі (-1) бұған жауапты:
3-әдіс. Соңғы күні бар жолды іздеңіз
Егер бастапқы деректерде сериялық нөмірі немесе ұқсас рөл атқаратын күні бар баған болса, онда тапсырма өзгертілген - сәйкестік бар соңғы (ең төменгі) жолды емес, ең соңғысы бар жолды табу керек ( максималды) күні.
Мен мұны классикалық функциялардың көмегімен қалай жасау керектігін егжей-тегжейлі талқыладым, енді жаңа динамикалық массив функцияларының қуатын пайдалануға тырысайық. Үлкенірек әдемілік пен ыңғайлылық үшін біз түпнұсқа кестені пернелер тіркесімін пайдаланып «ақылды» кестеге түрлендіреміз. Ctrl+T немесе командалар Басты – кесте ретінде пішімдеу (Үй — Кесте ретінде пішімдеу).
Олардың көмегімен бұл «өлтіруші жұп» біздің мәселемізді өте әдемі шешеді:
Мұнда:
- Алдымен функция СҮЗГІ (СҮЗГІ) кестеден бағандағы жолдарды ғана таңдайды сатып алушы - бізге керек атау.
- Содан кейін функция СЫНЫП (СҰРУ) таңдалған жолдарды күні бойынша кему ретімен сұрыптайды, ең соңғы мәміле жоғарғы жағында болады.
- функция INDEX (ИНДЕКС) бірінші жолды шығарады, яғни бізге қажет соңғы сауданы қайтарады.
- Соңында, сыртқы FILTER функциясы нәтижелерден қосымша 1-ші және 3-ші бағандарды жояды (Тапсырыс коды и сатып алушы) және тек күн мен соманы қалдырады. Ол үшін тұрақтылар массиві қолданылады. {0;1;0;1}, қай бағандарды көрсеткіміз келетінін (1) немесе қаламайтынымызды (0) анықтау.
4-әдіс: Power Query ішіндегі соңғы сәйкестікті табу
Толық болу үшін Power Query қондырмасын пайдаланып кері іздеу мәселесінің шешімін қарастырайық. Оның көмегімен бәрі өте тез және әдемі шешіледі.
1. Түпнұсқа кестені пернелер тіркесімін пайдаланып «ақылды» кестеге түрлендірейік Ctrl+T немесе командалар Басты – кесте ретінде пішімдеу (Үй — Кесте ретінде пішімдеу).
2. Түймемен Power Query ішіне жүктеңіз Кестеден/Ауқымнан қойындысы мәліметтер (Деректер — кестеден/ауқымнан).
3. Ең соңғы транзакциялар жоғарғы жағында болуы үшін кестемізді күннің кему реті бойынша сұрыптаймыз (тақырыптағы сүзгінің ашылмалы тізімі арқылы).
4… Қойындыда трансформация команда таңдау Топ бойынша (Трансформация — Топ бойынша) және тұтынушылар бойынша топтауды орнатыңыз және жинақтау функциясы ретінде опцияны таңдаңыз Барлық сызықтар (Барлық жолдар). Жаңа бағанды өзіңізге ұнайтын кез келген атау беруге болады, мысалы Егжей.
Топтастырудан кейін біз клиенттеріміздің бірегей атауларының тізімін және бағанда аламыз Егжей – олардың әрқайсысының барлық транзакциялары бар кестелер, мұнда бірінші жол соңғы транзакция болады, бұл бізге қажет:
5. Түймемен жаңа есептелген бағанды қосыңыз Реттелетін баған қойындысы Баған қосу (Баған қосу — реттелетін баған қосу)және келесі формуланы енгізіңіз:
осында Егжей – бұл тұтынушылар кестелерін алатын баған, және 0 {} - біз шығарып алғымыз келетін жолдың нөмірі (Power Query жүйесіндегі жолды нөмірлеу нөлден басталады). Біз жазбалары бар бағанды аламыз (рекорд), мұнда әрбір жазба әрбір кестенің бірінші жолы болып табылады:
Барлық жазбалардың мазмұнын баған тақырыбындағы қос көрсеткілері бар түймешікті кеңейту қалады Соңғы мәміле қажетті бағандарды таңдау:
… содан кейін қажет емес бағанды жойыңыз Егжей оның тақырыбын тінтуірдің оң жақ түймешігімен басу арқылы - Бағандарды жою (Бағандарды жою).
Нәтижелерді параққа жүктегеннен кейін Негізгі бет — Жабу және жүкте — Жабу және жүкте (Басты бет — Жабу және жүктеу — Жабу және жүктеп салу...) Біз қалағанымыздай соңғы транзакциялар тізімі бар жақсы кестені аламыз:
Бастапқы деректерді өзгерткен кезде нәтижелерді тінтуірдің оң жақ түймешігімен басу арқылы жаңартуды ұмытпау керек - пәрмен Жаңарту және сақтау (Жаңарту) немесе пернелер тіркесімі Ctrl+Alt+F5.
- ІЗДЕУ функциясы VLOOKUP функциясының ұрпағы болып табылады
- SORT, FILTER және UNIC жаңа динамикалық массив функцияларын пайдалану жолы
- ІЗДЕУ функциясы бар жолдағы немесе бағандағы соңғы бос емес ұяшықты табу