Добијте више вредности тражења у једној ћелији (са и без понављања)

Можемо ли потражити и вратити више вредности у једној ћелији у Екцелу (одвојене зарезима или размаком)?

Многе колеге и читаоци су ми више пута постављали ово питање.

Екцел има неке невероватне формуле за претраживање, као што су ВЛООКУП, ИНДЕКС/МАТЦХ (и сада КСЛООКУП), али ниједна од ових не нуди начин за враћање више одговарајућих вредности. Све ово функционише тако што идентификује прво подударање и враћа то.

Зато сам направио мало ВБА кодирања да бих смислио прилагођену функцију (која се назива и кориснички дефинисана функција) у Екцелу.

ажурирање: Након што је Екцел објавио динамичке низове и сјајне функције, попут УНИКУЕ и ТЕКСТЈОИН, сада је могуће користити једноставну формулу и врати све одговарајуће вредности у једну ћелију (обрађено у овом водичу).

У овом водичу ћу вам показати како то учинити (ако користите најновију верзију програма Екцел - Мицрософт 365 са свим новим функцијама), као и начин на који то можете учинити у случају да користите старије верзије ( помоћу ВБА).

Па да почнемо!

Потражите и вратите више вредности у једну ћелију (помоћу формуле)

Ако користите Екцел 2016 или старије верзије, пређите на следећи одељак где ћу показати како то да урадите помоћу ВБА.

Уз претплату на Мицрософт 365, ваш Екцел сада има много моћније функције и функције којих нема у претходним верзијама (као што су КСЛООКУП, динамички низови, функције УНИКУЕ/ФИЛТЕР итд.)

Дакле, ако користите Мицрософт 365 (раније познат као Оффице 365), можете користити методе описане у овом одељку за тражење и враћање више вредности у једну ћелију у Екцелу.

И као што ћете видети, то је заиста једноставна формула.

Испод имам скуп података где имам имена људи у колони А и обуку коју су прошли у колони Б.

Кликните овде да преузмете датотеку примера и пратите је

За сваку особу желим да сазнам коју су обуку завршили. У колони Д имам листу јединствених имена (из колоне А) и желим брзо да потражим и издвојим сву обуку коју је свака особа прошла и да их добијем у једном скупу (одвојеном зарезом).

Испод је формула која ће то учинити:

= ТЕКСТЈОИН (",", ТРУЕ, ИФ (Д2 = $ А $ 2: $ А $ 20, $ Б $ 2: $ Б $ 20, ""))

Након што унесете формулу у ћелију Е2, копирајте је за све ћелије у које желите резултате.

Како функционише ова формула?

Дозволите ми да деконструишем ову формулу и објасним сваки део како се она спаја даје нам резултат.

Логички тест у формули ИФ (Д2 = $ А $ 2: $ А $ 20) проверава да ли је ћелија имена Д2 иста као она у опсегу А2: А20.

Пролази кроз сваку ћелију у опсегу А2: А20 и проверава да ли је име исто у ћелији Д2 или не. ако је исто име, враћа ТРУЕ, иначе враћа ФАЛСЕ.

Дакле, овај део формуле ће вам дати низ као што је приказано испод:

{ТРУЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ}

Пошто желимо само да прођемо обуку за Боба (вредност у ћелији Д2), морамо да прођемо сву одговарајућу обуку за ћелије које враћају ТРУЕ у горњи низ.

То се лако постиже навођењем [валуе_иф_труе] дела формуле ИФ као опсега који има обуку. Ово осигурава да ако се име у ћелији Д2 подудара са именом у опсегу А2: А20, формула ИФ ће вратити све обуке које је та особа прошла.

И где год низ врати ФАЛСЕ, навели смо вредност [валуе_иф_фалсе] као “” (празно), тако да враћа празно.

ИФ део формуле враћа низ као што је приказано испод:

{“Екцел”; ””; ””; ”ПоверПоинт”; ””; ””; ””; ””; ””; ””; ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ”” ; ””; ””; ””}

Тамо где има имена обуке коју је Боб прошао и празна места где се није звао Боб.

Сада, све што треба да урадимо је да комбинујемо ове називе обуке (одвојене зарезом) и вратимо их у једну ћелију.

А то се лако може учинити помоћу нове формуле ТЕКСТЈОИН (доступна у Екцел2021-2022 и Екцел у Мицрософт 365)

Формула ТЕКСТЈОИН узима три аргумента:

• граничник - који је „,“ у нашем примеру, јер желим да обука буде одвојена зарезом и размаком
• ТРУЕ - која говори формули ТЕКСТЈОИН да игнорише празне ћелије и комбинује само оне које нису празне
• Формула Иф која враћа текст који је потребно комбиновати

Ако користите Екцел у систему Мицрософт 365 који већ има динамичке низове, можете само унети горњу формулу и притиснути ентер. А ако користите Екцел2021-2022, морате унети формулу и држати тастер Цонтрол и Схифт, а затим притиснути Ентер

Кликните овде да преузмете датотеку примера и пратите је

Добијте више вредности тражења у једној ћелији (без понављања)

Будући да је формула УНИКУЕ доступна само за Екцел у Мицрософт 365, нећете моћи да користите ову методу у програму Екцел2021-2022

У случају да у вашем скупу података има понављања, као што је приказано испод, формулу морате мало променити тако да добијете само листу јединствених вредности у једној ћелији.

У горњем скупу података, неки људи су прошли обуку више пута. На пример, Боб и Стан су два пута похађали Екцел програм, а Бетти два пута МС Ворд. Али у нашем резултату не желимо да се понавља назив обуке.

За то можете користити доњу формулу:

= ТЕКСТЈОИН (",", ТРУЕ, УНИКУЕ (ИФ (Д2 = $ А $ 2: $ А $ 20, $ Б $ 2: $ Б $ 20, "")))

Горња формула функционише на исти начин, са мањом променом. користили смо формулу ИФ унутар функције УНИКУЕ тако да у случају понављања у резултату формуле иф, функција УНИКУЕ би је уклонила.

Кликните овде за преузимање датотеке примера

Потражите и вратите више вредности у једну ћелију (помоћу ВБА)

Ако користите Екцел 2016 или претходне верзије, нећете имати приступ формули ТЕКСТЈОИН. Дакле, најбољи начин да потражите и добијете више одговарајућих вредности у једној ћелији је коришћењем прилагођене формуле коју можете да креирате помоћу ВБА.

Да бисмо добили више вредности за претраживање у једној ћелији, морамо да направимо функцију у ВБА (слично функцији ВЛООКУП) која проверава сваку ћелију у колони и ако се пронађе вредност за претраживање, додаје је резултату.

Ево ВБА кода који то може учинити:

'Цоде би Сумит Бансал (хттпс://трумпекцел.цом) Функција СинглеЦеллЕктрацт (Лоокупвалуе Ас Стринг, ЛоокупРанге Ас Ранге, ЦолумнНумбер Ас Интегер) Дим и Ас Лонг Дим Ресулт Ас Стринг Фор и = 1 За ЛоокупРанге.Цолумнс (1). .Цоунт Иф ЛоокупРанге.Целлс (и, 1) = Лоокупвалуе Тхен Ресулт = Ресулт & "" & ЛоокупРанге.Целлс (и, ЦолумнНумбер) & "," Енд Иф Нект и СинглеЦеллЕктрацт = Лефт (Резултат, Лен (Резултат) - 1) Завршна функција

Где ставити овај код?

1. Отворите радну свеску и кликните Алт + Ф11 (ово отвара прозор ВБА Едитор).
2. У овом прозору ВБА Едитор, са леве стране, налази се истраживач пројеката (где су наведене све радне свеске и радни листови). Десним тастером миша кликните било који објекат у радној свесци на коме желите да овај код ради, па идите на Уметни -> Модул.
3. У прозору модула (који ће се појавити са десне стране) копирајте и залепите горњи код.
4. Сада сте спремни. Идите у било коју ћелију у радној свесци и откуцајте = СинглеЦеллЕктрацт и укључите потребне улазне аргументе (тј. ЛоокупВалуе, ЛоокупРанге, ЦолумнНумбер).

Како функционише ова формула?

Ова функција ради слично као функција ВЛООКУП.

За унос су потребна 3 аргумента:

1. Лоокупвалуе - Низ који морамо да потражимо у низу ћелија.
2. ЛоокупРанге - Низ ћелија одакле морамо да преузмемо податке ($ Б3: $ Ц18 у овом случају).
3. ЦолумнНумбер - То је број колоне табеле/низа из којег се враћа одговарајућа вредност (2 у овом случају).

Када користите ову формулу, она проверава сваку ћелију у крајњој левој колони у опсегу за претраживање а када пронађе подударност, додаје резултат у ћелију у којој сте користили формулу.

Запамтити: Сачувајте радну свеску као радну свеску са омогућеним макроима (.клсм или .клс) да бисте поново користили ову формулу. Такође, ова функција би била доступна само у овој радној свесци, а не у свим радним свескама.

Кликните овде за преузимање датотеке примера

Научите како да аутоматизујете досадне понављајуће задатке помоћу ВБА у Екцелу. Придружи Екцел ВБА курс

Добијте више вредности тражења у једној ћелији (без понављања)

Постоји могућност да се подаци понављају.

Ако користите горе коришћени код, то ће вам поновити и резултат.

Ако желите да добијете резултат тамо где нема понављања, потребно је да мало измените код.

Ево ВБА кода који ће вам дати више вредности тражења у једној ћелији без понављања.

'Цоде би Сумит Бансал (хттпс://трумпекцел.цом) Функција МултиплеЛоокупНоРепт (Лоокупвалуе Ас Стринг, ЛоокупРанге Ас Ранге, ЦолумнНумбер Ас Интегер) Дим и Као Лонг Дим Ресулт Ас Стринг Фор и = 1 То ЛоокупРанге.Цолумнс (1). .Цоунт Иф ЛоокупРанге.Целлс (и, 1) = Лоокупвалуе Затим за Ј = 1 То и - 1 Ако је ЛоокупРанге.Целлс (Ј, 1) = Лоокупвалуе Тада Иф ЛоокупРанге.Целлс (Ј, ЦолумнНумбер) = ЛоокупРанге.Целлс (и, ЦолумнНумбер) Тхен ГоТо Скип Енд Иф Енд Иф Нект Иф Ресулт Ј Ресулт = Ресулт & "" & ЛоокупРанге.Целлс (и, ЦолумнНумбер) & "," Скип: Енд Иф Нект и МултиплеЛоокупНоРепт = Лефт (Ресулт, Лен (Ресулт) - 1) Енд Функција

Након што поставите овај код у ВБ Едитор (као што је приказано горе у водичу), моћи ћете да користите МултиплеЛоокупНоРепт функција.

Ево кратког приказа резултата који ћете добити са овим МултиплеЛоокупНоРепт функција.

Кликните овде за преузимање датотеке примера

У овом водичу сам описао како се користе формуле и ВБА у Екцелу за проналажење и враћање више вредности претраживања у једну ћелију у Екцелу.

Иако се то лако може урадити помоћу једноставне формуле ако користите Екцел у претплати на Мицрософт 365, ако користите претходне верзије и немате приступ функцијама као што је ТЕКСТЈОИН, то и даље можете учинити помоћу ВБА тако што ћете креирати свој сопствена прилагођена функција.