Гледайте видео - Потърсете втората, третата или N -та съвпадаща стойност
Когато става въпрос за търсене на данни в Excel, има две невероятни функции, които често използвам - VLOOKUP и INDEX (най -вече във връзка с функцията MATCH).
Тези формули обаче са предназначени да намерят само първия екземпляр от стойността за търсене.
Но какво ще стане, ако искате да потърсите втората, третата, четвъртата или N-та стойност.
Е, това е възможно с малко допълнителна работа.
В този урок ще ви покажа различни начини (с примери) как да търсите втората или N -тата стойност в Excel.
Потърсете втората, третата или N -тата стойност в Excel
В този урок ще разгледам два начина за търсене на втората или N-тата стойност в Excel:
- Използване на помощна колона.
- Използване на формули за масиви.
Да започнем и да се потопим направо.
Използване на помощната колона
Да предположим, че сте координатор на обучение в организация и имате набор от данни, както е показано по -долу. Искате да изброите цялото обучение пред името на служител.
В горния набор от данни служителите са преминали обучение по различни инструменти на Microsoft Office (Excel, PowerPoint и Word).
Сега можете да използвате функцията VLOOKUP или комбинацията INDEX/MATCH, за да намерите обучението, което служителят е завършил. Той обаче ще върне само първия съвпадащ екземпляр.
Например, в случая с Джон, той е преминал и трите обучения, но когато потърся името му с VLOOKUP или INDEX/MATCH, то винаги ще връща „Excel“, което е първото обучение за неговото име в списъка .
За да направим това, можем да използваме помощна колона и да създадем уникални стойности за търсене в нея.
Ето стъпките:
- Вмъкнете колона преди колоната, която изброява обучението.
- В клетка В2 въведете следната формула:
= A2 & COUNTIF ($ A $ 2: $ A2, A2)
- В клетка F2 въведете следната формула и копирайте-поставете за всички останали клетки:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Горната формула би върнала обучението за всеки служител в реда, в който се появява в списъка. В случай, че няма посочени обучения за служител, той връща празно място.
Как действа тази формула?
Формулата COUNTIF в помощната колона прави името на всеки служител уникално, като добавя номер към него. Например, първият екземпляр на John става John1, вторият екземпляр става John2 и т.н.
Формулата VLOOKUP вече използва тези уникални имена на служители, за да намери подходящото обучение.
Обърнете внимание, че $ E2 & COLUMNS ($ F $ 1: F1) е стойността за търсене във формулата. Това ще добави номер към името на служителя въз основа на номера на колоната. Например, когато тази формула се използва в клетка F2, стойността за търсене става „John1“. В клетка G2 става „John2“ и т.н.
Използване на формула за масив
Ако не искате да променяте оригиналния набор от данни, като добавяте помощни колони, можете също да използвате формула за масив, за да търсите втората, третата или n -тата стойност.
Да предположим, че имате същия набор от данни, както е показано по -долу:
Ето формулата, която ще върне правилната стойност за търсене:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14)) -1, ""), COLUMNS ($ E $ 1 : E1))), "")
Копирайте тази формула и я поставете в клетка E2.
Обърнете внимание, че това е формула за масив и трябва да използвате Control + Shift + Enter (задръжте клавишите Control и Shift и натиснете клавиша Enter), вместо да натискате само клавиша Enter.
Щракнете тук, за да изтеглите примерния файл.
Как действа тази формула?
Нека разбием тази формула на части и да видим как работи.
$ A $ 2: $ A $ 14 = $ D2
Горната част на формулата сравнява всяка клетка в A2: A14 със стойността в D2. В този набор от данни той проверява дали клетката съдържа името „Джон“ или не.
Връща масив от TRUE на FALSE. Ако клетката има името „Джон“, това би било вярно, в противен случай би било невярно.
По -долу е масивът, който бихте получили в този пример:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}
Имайте предвид, че тя има TRUE на 1 -ва, 7 -а и 111 -та позиция, тъй като там името на Джон се появява в набора от данни.
АКО ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Горната формула IF използва масива от TRUE и FALSE и заменя TRUE с позицията на появата му в списъка (дадена от ROW ($ A $ 2: $ A $ 14) -1) и FALSE с “” (празни). По -долу е полученият масив, който получавате с тази формула IF:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Забележете, че 1, 7 и 11 са позицията на Йоан в списъка.
МАЛКИ (АКО ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””), КОЛОНИ ($ E $ 1: E1))
Функцията SMALL сега избира първото най -малко, второто най -малко, третото най -малко число от този масив. Обърнете внимание, че тя използва функцията COLUMNS за генериране на номера на колоната. В клетка E2 функцията COLUMNS връща 1, а функцията SMALL връща 1. В клетка F2 функцията COLUMNS връща 2, а функцията SMALL връща 7.
ИНДЕКС ($ B $ 2: $ B $ 14, МАЛКИ (АКО ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ”)), КОЛОНИ ($ E $ 1: E1) ))
Функцията INDEX сега връща стойността от списъка в колона B въз основа на позицията, върната от функцията SMALL. Следователно в клетка E2 връща „Excel“, който е първият елемент в B2: B14. В клетка F2 връща PowerPoint, който е 7 -мият елемент в списъка.
Тъй като има случаи, когато има само едно или две обучения за някои служители, функцията INDEX би върнала грешка. Функцията IFERROR се използва за връщане на празно място вместо грешката.
Обърнете внимание, че в тези примери съм използвал препратки към диапазони. В практическите примери обаче е полезно да конвертирате данните в таблица на Excel. Чрез преобразуване в таблица на Excel можете да използвате структурирани препратки, което улеснява създаването на формули. Също така, таблица в Excel може автоматично да отчита всички нови елементи на обучение, които са добавени към списъка (така че не е нужно да коригирате формулите всеки път).
Какво правите, когато трябва да потърсите втората, третата или N-тата стойност? Сигурен съм, че има повече начини да направите това. Ако използвате нещо по -лесно от изброеното тук, споделете с всички нас в секцията за коментари.
Щракнете тук, за да изтеглите примерния файл.
