Мундариҷа
Ҳама функсияҳои ҷустуҷӯ ва ивазкунии навъи классикӣ VPR (ИНТИЗОР), GPR (НАМОИШ), БЕШТАР ФОШ (МАТЧ) ва монанди онҳо як хусусияти муҳим доранд - онҳо аз аввал то ба охир, яъне дар маълумоти сарчашма аз чап ба рост ё боло ба поён ҷустуҷӯ мекунанд. Ҳамин ки мувофиқати аввалини мувофиқ пайдо шуд, ҷустуҷӯ қатъ мешавад ва танҳо пайдоиши аввалин унсури ба мо лозим аст.
Чӣ бояд кард, агар мо на аввал, балки охирин ҳодисаро пайдо кунем? Масалан, амалиёти охирин барои муштарӣ, пардохти охирин, фармоиши охирин ва ғайра?
Усули 1: Ҷустуҷӯи сатри охирин бо формулаи массив
Агар дар ҷадвали аслӣ сутун бо сана ё рақами силсилавии сатр (фармоиш, пардохт ...) надошта бошад, пас вазифаи мо воқеан пайдо кардани сатри охиринест, ки шарти додашударо қонеъ мекунад. Инро бо формулаи массиви зерин анҷом додан мумкин аст:
Ин ҷо:
- функсия IF (АГАР) ҳамаи чашмакҳои сутунро як ба як тафтиш мекунад муштариён ва рақами сатрро нишон медиҳад, агар он номи ба мо лозимиро дар бар гирад. Рақами сатр дар варақ аз ҷониби функсия ба мо дода мешавад LINE (ҚАТР), аммо азбаски ба мо рақами сатри ҷадвал лозим аст, мо ба таври илова бояд 1-ро кам кунем, зеро мо дар ҷадвал сарлавҳа дорем.
- Сипас, функсия MAX (Макс) арзиши максималиро аз маҷмӯи ташаккулёфтаи рақамҳои сатр, яъне рақами сатри охирини муштарӣ интихоб мекунад.
- функсия INDEX (ИНДЕКС) мундариҷаи чашмакро бо рақами охирини ёфтшуда аз ҳар як сутуни дигари ҷадвали зарурӣ бармегардонад (Рамзи фармоиш).
Ҳамаи ин бояд ҳамчун ворид карда шавад формулаи массив, яъне:
- Дар Office 365 бо навсозиҳои охирин насбшуда ва дастгирии массивҳои динамикӣ, шумо метавонед танҳо пахш кунед даромадан.
- Дар ҳама версияҳои дигар, пас аз ворид кардани формула, шумо бояд миёнабури клавиатураро пахш кунед Ctrl+Shift+даромадан, ки ба таври худкор қавсҳои ҷингиларо дар сатри формула илова мекунад.
Усули 2: Ҷустуҷӯи баръакс бо функсияи нави LOOKUP
Ман аллакай мақолаи дароз бо видео дар бораи хусусияти нав навишта будам VIEW (НАЗАР), ки дар версияҳои охирини Office барои иваз кардани VLOOKUP-и кӯҳна пайдо шудааст (ИНТИЗОР). Бо ёрии BROWSE, вазифаи мо ба таври оддӣ ҳал карда мешавад, зеро. барои ин функсия (баръакси VLOOKUP), шумо метавонед самти ҷустуҷӯро ба таври возеҳ таъин кунед: аз боло ба поён ё аз поён ба боло - далели охирини он (-1) барои ин масъул аст:
Усули 3. Ҷустуҷӯи сатр бо таърихи охирин
Агар дар маълумоти сарчашма мо сутуни дорои рақами силсилавӣ ё санае дошта бошем, ки нақши шабеҳро иҷро мекунад, пас вазифа тағир дода мешавад - мо бояд на сатри охирин (пасттарин) бо мувофиқат, балки сатри охиринро ( максимум) сана.
Ман аллакай ба таври муфассал дар бораи чӣ гуна ин корро бо истифода аз функсияҳои классикӣ муҳокима кардам ва ҳоло биёед кӯшиш кунем, ки қудрати функсияҳои нави массиви динамикиро истифода барем. Барои зебоӣ ва роҳати бештар, мо инчунин бо истифода аз миёнабурҳои клавиатура ҷадвали аслиро ба ҷадвали "ақл" табдил медиҳем. Ctrl+T ё фармонҳо Хона - Формат ҳамчун ҷадвал (Хона — Формат ҳамчун ҷадвал).
Бо кӯмаки онҳо ин "ҷуфти қотил" мушкилоти моро хеле зебо ҳал мекунад:
Ин ҷо:
- Аввалан функсия филтр (ФИЛТР) танҳо он сатрҳоро аз ҷадвали мо, ки дар сутун ҷойгир аст, интихоб мекунад муштариён — номе, ки ба мо лозим аст.
- Сипас, функсия синфи (ЧУНИН) сатрҳои интихобшударо аз рӯи сана бо тартиби камшавӣ бо аҳдномаи охирин дар боло ҷудо мекунад.
- функсия INDEX (ИНДЕКС) сатри аввалро мебарорад, яъне савдои охирини ба мо лозимиро бармегардонад.
- Ва ниҳоят, функсияи FILTER берунии сутунҳои иловагии 1 ва 3-ро аз натиҷаҳо хориҷ мекунад (Рамзи фармоиш и муштариён) ва танҳо сана ва маблағро мегузорад. Барои ин массиви доимӣ истифода мешавад. {0;1;0;1}, муайян кардани кадом сутунҳои мо (1) ё намехоҳем (0) намоиш дода шаванд.
Усули 4: Ҷустуҷӯи мувофиқати охирин дар Query Power
Хуб, барои пуррагӣ, биёед ҳалли мушкилоти ҷустуҷӯи баръакси худро бо истифода аз замимаи Power Query бубинем. Бо ёрии вай, ҳама чиз хеле зуд ва зебо ҳал карда мешавад.
1. Биёед бо истифода аз миёнабурҳои клавиатура ҷадвали аслии худро ба ҷадвали "ақл" табдил диҳем Ctrl+T ё фармонҳо Хона - Формат ҳамчун ҷадвал (Хона — Формат ҳамчун ҷадвал).
2. Онро ба Power Query бо тугма бор кунед Аз ҷадвал/диапазон ҷадвали Маълумот (Маълумот - Аз ҷадвал/диапазон).
3. Мо (тавассути рӯйхати афтанда филтр дар сарлавҳа) ҷадвали худро бо тартиби камшавии сана ҷудо мекунем, то амалиёти охирин дар боло бошанд.
4… Дар ҷадвал дигаргунсозии даста интихоб кунед Аз ҷониби гурӯҳ (Тағйир додан — Гурӯҳ аз ҷониби) ва гурӯҳбандиро аз рӯи муштариён муқаррар кунед ва ҳамчун вазифаи ҷамъкунӣ, интихобро интихоб кунед Ҳама сатрҳо (Ҳама сатрҳо). Шумо метавонед сутуни навро ҳар чизе, ки ба шумо маъқул аст, номгузорӣ кунед - масалан Cохта шуд.
Пас аз гурӯҳбандӣ, мо рӯйхати номҳои беназири мизоҷони худро ва дар сутун мегирем Cохта шуд - ҷадвалҳо бо тамоми транзаксияҳои ҳар яки онҳо, ки дар он сатри аввал транзаксияи охирин хоҳад буд, ки ба мо лозим аст:
5. Бо тугма сутуни нави ҳисобшударо илова кунед Сутуни фармоишӣ ҷадвали Сутун илова кунед (Иловаи сутун - Иловаи сутуни фармоишӣ)ва формулаи зеринро ворид кунед:
Дар ин ҷо Cохта шуд – ин сутунест, ки мо аз он мизҳои муштариёнро мегирем ва 0 {} рақами сатрест, ки мо мехоҳем истихроҷ кунем (рақамгузории сатр дар Power Query аз сифр оғоз мешавад). Мо сутунеро бо сабтҳо мегирем (рекорд), ки ҳар як вуруд сатри аввал аз ҳар як ҷадвал аст:
Барои васеъ кардани мундариҷаи ҳамаи сабтҳо бо тугмаи тирчаҳои дукарата дар сарлавҳаи сутун боқӣ мемонад Созиши охирин интихоби сутунҳои дилхоҳ:
... ва сипас сутунеро, ки дигар лозим нест, нест кунед Cохта шуд бо клики рости муш ба унвони он - Сутунҳоро нест кунед (Нест кардани сутунҳо).
Пас аз бор кардани натиҷаҳо ба варақ тавассути Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед (Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед…) мо чунин ҷадвали хуберо бо рӯйхати муомилоти охирин мегирем, тавре ки мо мехостем:
Ҳангоми тағир додани маълумоти манбаъ, шумо набояд фаромӯш накунед, ки натиҷаҳоро бо пахш кардани тугмаи рости онҳо - фармон навсозӣ кунед Навсозӣ ва Захира (навсозӣ) ё миёнабури клавиатура Ctrl+Alt+F5.
- Функсияи LOOKUP насли VLOOKUP мебошад
- Чӣ тавр истифода бурдани функсияҳои массиви динамикии SORT, FILTER ва UNIC
- Ҷустуҷӯи чашмаки холии охирин дар як сатр ё сутун бо функсияи LOOKUP