мазмуну
Бардык классикалык издөө жана түр алмаштыруу функциялары VPR (КӨРҮҮ), GPR (ТАШУУ), КӨБҮРӨГҮ АЧЫРЫЛГАН (МАТЧ) жана аларга окшогондордун бир маанилүү өзгөчөлүгү бар – алар башынан аягына чейин издешет, б.а. солдон оңго же жогорудан ылдыйга. Биринчи дал келген дал келүү табылганда, издөө токтойт жана бизге керектүү элементтин биринчи жолу гана табылат.
Биринчи эмес, акыркы көрүнүштү табышыбыз керек болсо, эмне кылышыбыз керек? Мисалы, кардар үчүн акыркы транзакция, акыркы төлөм, эң акыркы заказ ж.б.у.с.?
Метод 1: Массив формуласы менен акыркы сапты табуу
Эгерде баштапкы таблицада датасы же катардын катар номери (буйрутма, төлөм...) бар тилке жок болсо, анда биздин милдет, чынында, берилген шартты канааттандырган акыркы сапты табуу. Муну төмөнкү массив формуласы менен жасоого болот:
Бул жерде:
- милдети IF (ЭГЕР) тилкедеги бардык уячаларды бирден текшерет кардар жана ал бизге керектүү аталышты камтыса, саптын номерин көрсөтөт. Барактагы саптын номери бизге функция тарабынан берилет LINE (КАП), бирок бизге таблицада саптын номери керек болгондуктан, кошумча 1ди алып салышыбыз керек, анткени бизде таблицада баш маалымат бар.
- Андан кийин функция MAX (МАКС) сап номерлеринин калыптанган топтомунан максималдуу маанини, башкача айтканда кардардын эң акыркы сабынын санын тандайт.
- милдети INDEX (ИНДЕКС) башка керектүү таблица тилкесиндеги акыркы табылган уячанын мазмунун кайтарат (Заказдын коду).
Мунун баары катары киргизилиши керек массив формуласы, б.а.:
- Акыркы жаңыртуулар орнотулган жана динамикалык массивдерди колдогон Office 365те сиз жөн гана бассаңыз болот кирүү.
- Бардык башка версияларда, формуланы киргизгенден кийин, баскычтоптун жарлыгын басышыңыз керек болот Ctrl+өзгөрүү+кирүү, бул формула тилкесинде ага тармал кашааларды автоматтык түрдө кошот.
Метод 2: Жаңы LOOKUP функциясы менен кайра издөө
Мен буга чейин жаңы функция жөнүндө видео менен узун макала жазганмын КӨРҮҮ (ТҮЗӨТҮҮ), эски VLOOKUP ордуна Officeтин акыркы версияларында пайда болгон (КӨРҮҮ). BROWSE жардамы менен, биздин милдет өтө эле жөнөкөй чечилет, анткени. бул функция үчүн (VLOOKUP айырмаланып), сиз издөө багытын ачык орното аласыз: жогорудан ылдыйга же ылдыйдан өйдөгө – анын акыркы аргументи (-1) бул үчүн жооптуу:
Метод 3. Акыркы датасы бар сапты издеңиз
Эгерде баштапкы маалыматтарда бизде сериялык номери же окшош ролду ойногон датасы бар тилке болсо, анда тапшырма өзгөртүлгөн - дал келген акыркы (эң төмөнкү) сапты эмес, эң акыркы ( максималдуу) датасы.
Мен буга чейин классикалык функцияларды колдонуу менен муну кантип жасоону майда-чүйдөсүнө чейин талкууладым, эми жаңы динамикалык массив функцияларынын күчүн колдонууга аракет кылалы. Көбүрөөк кооздук жана ыңгайлуулук үчүн, биз ошондой эле баскычтоптун жарлыгын колдонуп, баштапкы таблицаны "акылдуу" таблицага айландырабыз Ctrl+T же буйруктар Башкы бет – Таблица катары форматтоо (Башкы бет — Таблица катары формат).
Алардын жардамы менен бул "өлтүрүүчү жубайлар" биздин көйгөйдү абдан кооз чечет:
Бул жерде:
- Биринчи функция FILTER (ФИЛЬТР) биздин таблицадан тилкедеги ошол саптарды гана тандайт кардар - Бизге керектүү ысым.
- Андан кийин функция КЛАСС (СОРТ) тандалган саптарды датасы боюнча азаюу тартибинде иреттейт, эң акыркы келишим жогору жагында.
- милдети 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.
- LOOKUP функциясы VLOOKUPтун тукуму болуп саналат
- SORT, FILTER жана UNIC динамикалык массивинин жаңы функцияларын кантип колдонуу керек
- LOOKUP функциясы менен катардагы же тилкедеги акыркы бош эмес уячаны табуу