Гледајте видео - Потражите другу, трећу или Н -ту одговарајућу вредност
Што се тиче претраживања података у Екцелу, постоје две невероватне функције које често користим - ВЛООКУП и ИНДЕКС (углавном заједно са функцијом МАТЦХ).
Међутим, ове формуле су осмишљене тако да пронађу само прву инстанцу вредности тражења.
Али шта ако желите да потражите другу, трећу, четврту или Н-ту вредност.
Па, то је изводљиво са мало додатног рада.
У овом водичу ћу вам показати различите начине (са примерима) како да потражите другу или Н -ту вредност у Екцелу.
Потражите другу, трећу или Нту вредност у Екцелу
У овом водичу ћу покрити два начина тражења друге или Н-те вредности у Екцелу:
- Коришћење помоћне колоне.
- Коришћење формула за низ.
Почнимо и одмах уронимо.
Коришћење колоне помоћника
Претпоставимо да сте координатор обуке у организацији и имате скуп података као што је приказано у наставку. Желите да наведете сву обуку испред имена запосленог.
У горњем скупу података запослени су прошли обуку о различитим алаткама Мицрософт Оффице (Екцел, ПоверПоинт и Ворд).
Сада можете користити функцију ВЛООКУП или комбинацију ИНДЕКС/МАТЦХ да пронађете обуку коју је запослени завршио. Међутим, вратиће само прву одговарајућу инстанцу.
На пример, у случају Јохна, он је прошао све три обуке, али када погледам његово име помоћу ВЛООКУП или ИНДЕКС/МАТЦХ, увек ће се вратити „Екцел“, што је прва обука за његово име на листи .
Да бисмо то урадили, можемо користити помоћну колону и у њој створити јединствене вредности за претраживање.
Ево корака:
- Уметните колону испред колоне која наводи обуку.
- У ћелију Б2 унесите следећу формулу:
= А2 & ЦОУНТИФ ($ А $ 2: $ А2, А2)
- У ћелију Ф2 унесите следећу формулу и копирајте и налепите за све остале ћелије:
= ИФНА (ВЛООКУП ($ Е2 & ЦОЛУМНС ($ Ф $ 1: Ф1), $ Б $ 2: $ Ц $ 14,2,0), "")
Горња формула би вратила обуку за сваког запосленог по редоследу који се појављује на листи. У случају да није наведена обука за запосленог, враћа се празно.
Како функционише ова формула?
Формула ЦОУНТИФ у помоћној колони чини име сваког запосленика јединственим додавањем броја. На пример, прва инстанца Јована постаје Јован 1, друга инстанца постаје Јован 2 и тако даље.
Формула ВЛООКУП сада користи ова јединствена имена запослених за проналажење одговарајуће обуке.
Имајте на уму да су $ Е2 & ЦОЛУМНС ($ Ф $ 1: Ф1) вредност претраживања у формули. Ово би додало број имену запосленог на основу броја колоне. На пример, када се ова формула користи у ћелији Ф2, вредност за претраживање постаје „Јохн1“. У ћелији Г2 постаје „Јохн2“ итд.
Користећи формулу низа
Ако не желите да промените изворни скуп података додавањем помоћних колона, можете користити и формулу низа да бисте потражили другу, трећу или нту вредност.
Претпоставимо да имате исти скуп података као што је приказано испод:
Ево формуле која ће вратити исправну вредност претраживања:
= ИФЕРРОР (ИНДЕКС ($ Б $ 2: $ Б $ 14, СМАЛЛ (ИФ ($ А $ 2: $ А $ 14 = $ Д2, РОВ ($ А $ 2: $ А $ 14) -1), ""), ЦОЛУМНС ($ Е $ 1 : Е1))), "")
Копирајте ову формулу и залепите је у ћелију Е2.
Имајте на уму да је ово формула низа и да морате користити Цонтрол + Схифт + Ентер (држите тастере Цонтрол и Схифт и притисните тастер Ентер), уместо да притиснете само тастер Ентер.
Кликните овде за преузимање датотеке примера.
Како функционише ова формула?
Поделимо ову формулу на делове и видимо како функционише.
$ А $ 2: $ А $ 14 = $ Д2
Горњи део формуле упоређује сваку ћелију у А2: А14 са вредношћу у Д2. У овом скупу података проверава да ли ћелија садржи име „Јован“ или не.
Враћа низ ТРУЕ од ФАЛСЕ. Ако ћелија има назив „Јован“, то би било тачно, у супротном би било нетачно.
Испод је низ који бисте добили у овом примеру:
{ТРУЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ}
Имајте на уму да има ТРУЕ на 1., 7. и 111. позицији, јер се тамо у скупу података појављује име Јохн.
АКО ($ А $ 2: $ А $ 14 = $ Д2, РОВ ($ А $ 2: $ А $ 14) -1, ””)
Горња ИФ формула користи низ ТРУЕ и ФАЛСЕ и замењује ТРУЕ позицијом свог појављивања на листи (дато РОВ ($ А $ 2: $ А $ 14) -1) и ФАЛСЕ са “” (празна поља). Следи резултујући низ који добијате помоћу ове ИФ формуле:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Имајте на уму да су 1, 7 и 11 положај Јована на листи.
МАЛО (АКО ($ А $ 2: $ А $ 14 = $ Д2, РОВ ($ А $ 2: $ А $ 14) -1, ””), ЦОЛУМНС ($ Е $ 1: Е1))
Функција СМАЛЛ сада бира први најмањи, други најмањи, трећи најмањи број из овог низа. Имајте на уму да користи функцију ЦОЛУМНС за генерисање броја колоне. У ћелији Е2 функција ЦОЛУМНС враћа 1, а функција СМАЛЛ враћа 1. У ћелији Ф2 функција ЦОЛУМНС враћа 2, а функција СМАЛЛ враћа 7.
ИНДЕКС ($ Б $ 2: $ Б $ 14, СМАЛЛ (ИФ ($ А $ 2: $ А $ 14 = $ Д2, РОВ ($ А $ 2: $ А $ 14) -1, ””), ЦОЛУМНС ($ Е $ 1: Е1) ))
ИНДЕКС функција сада враћа вредност са листе у колони Б на основу позиције коју враћа функција СМАЛЛ. Дакле, у ћелији Е2 враћа „Екцел“, која је прва ставка у Б2: Б14. У ћелији Ф2 враћа ПоверПоинт, која је седма ставка на листи.
Пошто постоје случајеви у којима постоји само једна или две обуке за неке запослене, функција ИНДЕКС би вратила грешку. Функција ИФЕРРОР се користи за враћање празног места уместо грешке.
Имајте на уму да сам у овим примерима користио референце опсега. Међутим, у практичним примерима, корисно је претворити податке у Екцел табелу. Претварањем у Екцел табелу можете користити структуриране референце, што олакшава креирање формула. Такође, Екцел таблица може аутоматски узети у обзир све нове ставке обуке које се додају на листу (тако да не морате сваки пут прилагођавати формуле).
Шта радите када морате да потражите другу, трећу или Н-ту вредност? Сигуран сам да постоји више начина за то. Ако користите нешто лакше од оног што је овде наведено, поделите са свима нама у одељку за коментаре.
Кликните овде за преузимање датотеке примера.
