В тази статия ще научим как да търсим стойност между две числа в Excel.
Сценарий:
Лесно е да потърсите стойност с един критерий в таблица. Можем просто да използваме VLOOKUP. Но какво бихме могли да направим, ако критериите за няколко колони съвпадат във вашите данни и трябва да търсите в няколко колони, за да съответстват на стойност. Нека научим как този проблем може да бъде решен с помощта на различни версии за търсене на Excel
LOOKUP стойност между две числа
Използвайки формулата VLOOKUP
При приблизително съвпадение на VLOOKUP, ако не е намерена стойност, последната стойност, която е по -малка от стойността за търсене, се съпоставя и се връща стойността от дадения индекс на колоната.
Обща формула за стойност LOOKUP между две числа:
| = VLOOKUP (стойност, таблица, lookup_col, 1) |
И още нещо за Vlookup е, че търси стойността в колоната и ако не намери стойността в масива от колони, тя съвпада и връща стойността, която е по -малка от тази стойност в масива на таблицата.
Забележка: VLOOKUP по подразбиране прави приблизително съвпадение, ако пропуснем променливата за търсене на диапазон. Приблизителното съвпадение е полезно, когато искате да направите приблизително съвпадение и когато масивът на таблицата ви е сортиран във възходящ ред.
Използвайки формулата INDEX и MATCH
Формулата INDEX и MATCH върши същата работа както по -горе, но с различен синтаксис. Но ако трябва само да изберете този, с кои функции ви е удобно
Обща формула за LOOKUP стойност между две числа
| = ИНДЕКС (lookup_range, MATCH (1, INDEX ((критерии1 = диапазон1)*(критерии2 = диапазон2),0,1),0)) |
lookup_range: диапазонът, от който искате да извлечете стойност.
Критерии1, Критерии2, Критерии N: Това са критериите, които искате да съответствате в диапазон1, диапазон2 и Обхват N. Можете да имате до 270 критерия - двойки диапазони.
Диапазон1, диапазон2, диапазонN: Това са диапазоните, в които ще отговаряте на съответните критерии.
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем как да използваме функцията, използвайки пример. Тук имаме студентски идентификатор и съответните им оценки в тест. Сега трябваше да получим оценките за всеки ученик, като потърсим в таблицата със системата за оценки.
За да направим това, ще използваме атрибут на функцията VLOOKUP, който е, ако функцията VLOOKUP не намери точното съвпадение, тя търси приблизителното съвпадение само в таблицата и само ако последният аргумент на функцията е или ВЯРНО или 1.
Таблицата с точки / точки трябва да е във възходящ ред
Използвайте формулата:
| = VLOOKUP (B2, таблица, 2, 1) |
Как работи?
Функцията Vlookup търси стойността 40 в колоната с маркировки и връща съответната стойност, ако съвпада. Ако не съвпада, функцията търси по -малката стойност от стойността за търсене (т.е. 40) и връща резултата си.
Тук table_array е кръстен range като маса във формула и В2 е даден като еталон на клетка.
Както можете да видите, имаме оценка за първия ученик. Сега, за да получим всички останали степени, ще използваме пряк път Ctrl + D или използвайте опция за плъзгане надолу клетка в Excel.
Ето всички степени на класа, използващи функцията VLOOKUP.
LOOKUP стойност между две числа в таблицата Employee
Имаме таблица, която съдържа подробности за всички служители в една организация на отделен лист. Първата колона съдържа идентификационния номер на тези служители. Нарекох тази таблица като emp_data.
Сега моят лист за търсене трябва да извлече информацията за служителя, чийто идентификационен номер е записан в клетка В3. Назовах B3 като ID.
За по -лесно разбиране всички заглавия на колоните са в абсолютно същия ред като таблицата emp_data.
Сега напишете формулата по -долу в клетка C3, за да извлечете зоната на идентификационния номер на служителя, написана на B3.
| = VLOOKUP (ID, Emp_Data, 2,0) |
Това ще върне зоната на служителя Id 1-1830456593, тъй като колона номер 2 в базата данни съдържа зоната на служителите.
Копирайте тази формула в останалите клетки и променете номера на колоната във формулата, за да получите цялата информация за служителите.
Можете да видите цялата информация, свързана със споменатия идентификатор в клетка В3. Какъвто и идентификатор да напишете в клетка В3, цялата информация ще бъде извлечена без да се правят промени във формулата.
Как работи това?
Няма нищо сложно. Ние просто използваме функцията VLOOKUP за търсене на ID и след това извличаме споменатата колона. Практикувайте VLOOKUP, като използвате такива данни, за да разберете повече VLOOKUP.
Извличане на данни за служители с помощта на заглавия
В горния пример имахме всички колони, организирани в същия ред, но ще има моменти, когато ще имате база данни, която ще има стотици колони. В такива случаи този метод за извличане на информация за служителите няма да е добър. Ще бъде по -добре, ако формулата може да погледне заглавието на колоната и да извлече данни от тази колона от таблицата на служителите.
Така че, за да извлечем стойност от таблицата, използвайки заглавието на колона, ще използваме двупосочен метод за търсене или да кажем динамична колона VLOOKUP.
Използвайте тази формула в клетка C3 и копирайте в останалите клетки. Не е нужно да променяте нищо във формулата, всичко ще бъде извлечено от thd emp_data.
| = VLOOKUP (ID, Emp_Data, MATCH (C2, Emp_Data_Headers, 0), 0) |
Тази формула просто извлича цялата информация от съответстващи колони. Можете да бъркате заглавките в доклада, това няма да промени. Каквото и заглавие да е записано в клетката по -горе, съответните данни съдържат.
Как работи това?
Това е просто динамичен VLOOKUP. Можете да прочетете за това тук. Ако го обясня тук, ще стане твърде голяма статия.
Извличане на идентификационен номер на служител с частично съвпадение
Може да се случи, че не помните целия идентификационен номер на служител, но все пак искате да извлечете информацията за някакъв документ за самоличност. В такива случаи частичното съвпадение VLOOKUP е най -доброто решение.
Например, ако знам, че някакъв идентификатор съдържа 2345, но не знам целия идентификатор. Ако въведа този номер в клетка C3, изходът ще бъде като.
Не получаваме нищо. Тъй като нищо не съответства на 2345 в таблицата. Променете горната формула така.
| =VLOOKUP("*"&документ за самоличност&"*", Emp_Data,СЪВПАДА(C2, Emp_Data_Headers, 0), 0) |
Копирайте това в целия ред. И сега имате информацията за първия служител, която съдържа този номер.
Моля, обърнете внимание, че ще получим първия идентификационен номер, който съдържа съответстващия номер в колоната Emp Id. Ако друг идентификационен номер съдържа същия номер, тази формула няма да извлече информацията за този служител.
Ако искате да получите всички идентификатори на служители, които съдържат един и същ номер, използвайте формулата, която търси всички съответстващи стойности.
Използвайки формулата INDEX и MATCH
Тук имаме таблица с данни. Искам да изтегля името на клиента, използвайки Дата на резервация, Строител и Площ. Тук имам три критерия и един диапазон за търсене.
Напишете тази формула в клетка I4 натиснете Enter.
| = INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)) |
Как работи:
Вече знаем как работят функциите INDEX и MATCH в EXCEL, така че няма да обяснявам това тук. Ще говорим за трика, който използвахме тук.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Основната част е това. Всяка част от това изявление връща масив от true false.
Когато булевите стойности се умножат, те връщат масив от 0 и 1. Умножението работи като оператор AND. Следователно, когато всички стойности са верни, тогава той връща 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Това като цяло ще се върне
| {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}*
{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE} |
Което ще се преведе в
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Функцията INDEX ще върне същия масив ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) към MATCH функция като масив за търсене.
MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): Функцията MATCH ще търси 1 в масив {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. И ще върне индексния номер на първото 1, намерено в масива. Което е 8 тук.
INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Накрая INDEX ще се върне стойността от дадения диапазон (E2: E16) при намерен индекс (8).
Ето всички бележки за наблюдение, използващи формулата в Excel
Бележки:
- Колоната в таблицата с маркировки / резултати трябва да е във възходящ ред, в противен случай функцията може да даде грешен резултат.
- Функцията VLOOKUP търси стойността в първия ред на Table_array и извлича съответните стойности само вдясно от lookup_range.
- Последният аргумент на функцията VLOOKUP на функцията трябва да бъде зададен на TRUE или 1, за да се получи приблизителното съвпадение.
- Функцията VLOOKUP връща грешка, ако адресът на работната книга е невалиден или грешен.
- Функцията VLOOKUP връща грешка, ако стойността не съвпада.
Надявам се, че тази статия за това как да търсите стойност между две числа в Excel е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако вашите блогове са ви харесали, споделете ги с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Начин за използване на функцията Vlookup при проверка на данни : Ограничете потребителите да позволяват стойности от таблицата за търсене, като използвате полето за формула за валидиране на данни в Excel. Кутията с формули при валидиране на данни ви позволява да изберете типа на необходимото ограничение.
Как да извлечете най -новата цена в Excel : Обичайно е да се актуализират цените във всеки бизнес и използването на най -новите цени за всяка покупка или продажба е задължително. За да извлечем най -новата цена от списък в Excel, използваме функцията LOOKUP. Функцията LOOKUP достига най -новата цена.
Функция VLOOKUP за изчисляване на оценка в Excel : За изчисляване на оценки IF и IFS не са единствените функции, които можете да използвате. VLOOKUP е по -ефективен и динамичен за такива условни изчисления. За да изчислим оценки с помощта на VLOOKUP можем да използваме тази формула.
17 неща за Excel VLOOKUP : VLOOKUP се използва най -често за извличане на съответстващи стойности, но VLOOKUP може да направи много повече от това. Ето 17 неща за VLOOKUP, които трябва да знаете, за да използвате ефективно.
Потърсете първия текст от списък в Excel : Функцията VLOOKUP работи добре с заместващи знаци. Можем да използваме това, за да извлечем първата текстова стойност от даден списък в Excel. Ето общата формула.
LOOKUP дата с последна стойност в списъка : За да извлечем датата, която съдържа последната стойност, използваме функцията LOOKUP. Тази функция проверява за клетката, която съдържа последната стойност във вектор и след това използва тази препратка, за да върне датата.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнявайте задачите си в Excel. Тези преки пътища ще ви помогнат да увеличите ефективността на работата си в Excel.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на Excel, която се използва за търсене на стойности от различни диапазони и листове.
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.