В тази статия ще разгледаме възможностите на програмата Rufus за създаване на ...
Определено е по-лесно да търсите в една, макар и голяма, но цяла таблица или в съседни диапазони от клетки, отколкото в няколко таблици, разделени на части, разпръснати в различни несъседни диапазони или дори отделни листове. Дори ако извършите автоматично търсене на няколко маси едновременно, могат да възникнат значителни пречки. И поставянето на всички данни в една таблица е трудно, понякога почти невъзможно. Използвайки конкретен пример, ще демонстрираме правилното решение за едновременно търсене в множество таблици в Excel.
Едновременно търсене в множество диапазони
За визуален пример, нека създадем три прости отделни таблици, разположени в несъседни диапазони на един и същи лист:
Трябва да потърсите количеството, необходимо за производството на 20 броя продукти. За съжаление тези данни са в различни колони и редове. Следователно, на първо място, трябва да проверите колко време ще отнеме производството на тези продукти (първа таблица).
Въз основа на получените данни е необходимо незабавно да преминете към търсене в друга таблица и да намерите колко работници трябва да бъдат включени в даден производствен обем. Полученият резултат трябва да се сравни с данните от третата таблица. Така в една операция за търсене в три таблици веднага ще определим необходимите разходи (сума).
Средният потребител на Excel би търсил решение, използвайки формули, базирани на функции като VLOOKUP. Също така ще изпълни търсене в 3 етапа (отделно за всяка таблица). Оказва се, че можете незабавно да получите готовия резултат, като извършите търсене само на 1 етап по специална формула. За това:
- В клетка E6 въведете стойността 20, която е условието за заявката за търсене.
- В клетка E7 въведете следната формула:
Производствена себестойност за 20 бр. определен продукт.
Как една формула работи с VLOOKUP в множество таблици:
Принципът на действие на тази формула се основава на последователното търсене на всички аргументи за основната функция VLOOKUP (първата). Първо, третата функция VLOOKUP търси в първата таблица времето, необходимо за производството на 20 броя от продукта, зададен като стойност за клетка E6 (която след това може да бъде променена, ако е необходимо). След това втората функция VLOOKUP търси стойността за първия аргумент на основната функция.
В резултат на търсенето на третата функция получаваме стойността 125, която е първият аргумент за втората функция. След като получи всички параметри, втората функция търси във втората таблица броя на необходимите работници за производство. В резултат на това се връща стойността 5, която ще се използва от основната функция. Въз основа на всички получени данни формулата връща крайния резултат от изчислението. А именно сумата от $1750, необходима за производството на 20 броя от определен продукт.
Съгласно този принцип можете да използвате формули за функцията VLOOKUP от няколко листа.
Microsoft Excel често работи с големи количества информация. Той създава огромни таблици с хиляди редове, колони и позиции. Може да е трудно да се намерят конкретни данни в такъв масив. А понякога изобщо е невъзможно. Тази задача може да бъде опростена. Научете как да намерите точната дума в Excel. Това ще ви улесни при навигацията в документа. И можете бързо да преминете към информацията, която търсите.
За да покажете адресите на всички клетки, които имат това, което търсите, направете следното:
- Ако имате Office 2010, отидете на Меню - Редактиране - Намери.
- Ще се отвори прозорец с поле за въвеждане. Напишете фраза за търсене в него.
- В Excel 2007 този бутон е в началното меню в панела за редактиране. Тя е отдясно.
- Подобен резултат във всички версии може да се постигне чрез натискане на клавишите Ctrl + F.
- В полето въведете думата, фразата или числата, които искате да намерите.
- Щракнете върху „Намери всички“, за да търсите в целия документ. Ако щракнете върху „Напред“, програмата ще избере клетки една по една, които са под курсора на клетката на Excel.
- Изчакайте процеса да приключи. Колкото по-голям е документът, толкова по-дълго ще търси системата.
- Ще се появи списък с резултатите: имената и адресите на клетките, които отговарят на дадената фраза, и текстът, който е изписан в тях.
- Когато щракнете върху всеки ред, съответната клетка ще бъде маркирана.
- За удобство можете да "опънете" прозореца. Така че ще покаже повече линии.
- За да сортирате данните, щракнете върху имената на колоните над резултатите от търсенето. Ако щракнете върху "Лист", редовете ще се подредят по азбучен ред в зависимост от името на листа, ако изберете "Стойности" - ще бъдат поставени по стойност.
- Тези колони също са "опънати".
Можете да зададете свои собствени условия. Например започнете да търсите няколко знака. Ето как да намерите дума в Excel, която не си спомняте напълно:
- Въведете само част от етикета. Можете да имате поне една буква - ще бъдат избрани всички места, в които тя присъства.
- Използвайте символите * (звездичка) и? (въпросителен знак). Те заместват липсващите знаци.
- Въпросът показва един липсващ елемент. Ако напишете например "P???", ще се покажат клетки, които имат дума от четири знака, започваща с "P": "Ора", "Поле", "Чифт" и т.н.
- Звездата (*) замества произволен брой знаци. За да намерите всички стойности, които съдържат корена "rast", започнете да търсите ключа "*rast*".
Можете също да отидете в настройките:
- В прозореца Търсене щракнете върху Опции.
- В секциите "Преглед" и "Област за търсене" посочете къде и по какви критерии искате да търсите съвпадения. Можете да изберете формули, бележки или стойности.
- За да може системата да прави разлика между малки и главни букви, поставете отметка в квадратчето „чувствителни към малки и големи букви“.
- Ако поставите отметка в квадратчето „Цялата клетка“, резултатите ще покажат клетки, които съдържат само посочената фраза за търсене и нищо друго.
Опции за форматиране на клетки
За да намерите стойности с конкретно запълване или стил, използвайте настройките. Ето как да намерите дума в Excel, ако изглежда различно от останалата част от текста:
- В полето за търсене щракнете върху „Опции“ и щракнете върху бутона „Форматиране“. Ще се отвори меню с няколко раздела.
- Можете да посочите конкретен шрифт, тип рамка, цвят на фона, формат на данните. Системата ще търси места, които отговарят на зададените критерии.
- За да вземете информация от текущата клетка (избрана в този момент), щракнете върху „Използване на формата на тази клетка“. Тогава програмата ще намери всички стойности, които имат еднакъв размер и тип знаци, същия цвят, еднакви граници и други подобни.
Търсене на няколко думи
В Excel можете да намерите клетки по цели фрази. Но ако сте въвели клавиша "Синя топка", системата ще работи точно по тази заявка. Стойностите със „Синя кристална топка“ или „Синя блестяща топка“ няма да се показват в резултатите.
За да намерите повече от една дума в Excel, но няколко наведнъж, направете следното:
- Напишете ги в лентата за търсене.
- Поставете звезди между тях. Ще се получи "*Текст* *Текст2* *Текст3*". Това ще намери всички стойности, съдържащи посочените надписи. Независимо дали между тях има герои или не.
- По този начин можете да зададете ключа дори с отделни букви.
Филтър
Ето как да търсите в Excel с помощта на филтър:
- Изберете произволна попълнена клетка.
- Щракнете върху Начало - Сортиране - Филтриране.
- В горния ред на клетките ще се появят стрелки. Това е падащо меню. Отвори го.
- Въведете вашето запитване в текстовото поле и щракнете върху OK.
- Колоната ще показва само клетки, които съдържат фразата за търсене.
- За да нулирате резултатите, поставете отметка в „Избор на всички“ в падащия списък.
- За да изключите филтър, щракнете отново върху него при сортиране.
Този метод няма да работи, ако не знаете в кой ред е необходимата ви стойност.
За да намерите фраза или число в Excel, използвайте вградените функции на интерфейса. Можете да изберете допълнителни опции за търсене и да активирате филтър.
Този урок говори за основните предимства на функциите ИНДЕКСи СЪВПАДАв Excel, което ги прави по-привлекателни в сравнение с VLOOKUP. Ще видите няколко примера за формули, които ще ви помогнат лесно да се справите с много от сложните задачи, пред които е изправена функцията. VLOOKUPбезсилен.
В няколко скорошни статии положихме всички усилия да обясним основите на функцията на начинаещите потребители. VLOOKUPи показват примери за по-сложни формули за напреднали потребители. Сега ще се опитаме, ако не за да ви разубедим да използвате VLOOKUP, тогава поне покажете алтернативни начини за прилагане на вертикално търсене в Excel.
Защо имаме нужда от това? - ти питаш. Да защото VLOOKUPне е единствената функция за търсене в Excel и множеството й ограничения могат да ви попречат да получите желания резултат в много ситуации. От друга страна функциите ИНДЕКСи СЪВПАДА– по-гъвкави и имат редица функции, които ги правят по-привлекателни от VLOOKUP.
Основна информация за INDEX и MATCH
Тъй като целта на този урок е да покаже възможностите на функциите ИНДЕКСи СЪВПАДАза реализиране на вертикално търсене в Excel, няма да се спираме на техния синтаксис и приложение.
Тук даваме необходимия минимум за разбиране на същността и след това ще анализираме подробно примери за формули, които показват предимствата на използването ИНДЕКСи СЪВПАДАвместо VLOOKUP.
ИНДЕКС - синтаксис и приложение на функцията
функция ИНДЕКС(INDEX) в Excel връща стойност от масив при дадените номера на ред и колона. Функцията има следния синтаксис:
Всеки аргумент има много просто обяснение:
- масив(масив) е диапазонът от клетки, от които да се извлече стойността.
- номер_ред(номер на ред) е номерът на реда в масива, от който да се извлече стойността. Ако не е посочено, се изисква аргумент колона_номер(номер_на_колона).
- колона_номер(column_number) е номерът на колоната в масива, от която да се извлече стойността. Ако не е посочено, се изисква аргумент номер_ред(номер_на_ред)
Ако са дадени и двата аргумента, тогава функцията ИНДЕКСвръща стойността от клетката в пресечната точка на посочения ред и колона.
Ето един прост пример за функция ИНДЕКС(ИНДЕКС):
ИНДЕКС(A1:C10;2;3)
=ИНДЕКС(A1:C10;2;3)
Формулата търси в диапазон A1:C10и връща стойността на клетката в 2-ролиния и 3мколона, тоест от клетка C2.
Много просто, нали? На практика обаче не винаги знаете от кой ред и колона се нуждаете и затова се нуждаете от помощта на функцията СЪВПАДА.
MATCH - синтаксис и приложение на функцията
функция СЪВПАДА(MATCH) в Excel търси определена стойност в диапазон от клетки и връща относителната позиция на тази стойност в диапазона.
Например, ако в диапазона B1: B3съдържа стойностите New-York, Paris, London, тогава следната формула ще върне цифра 3 , защото "Лондон" е третият елемент в списъка.
MATCH("Лондон";B1:B3;0)
=MATCH("Лондон";B1:B3;0)
функция СЪВПАДА(MATCH) има следния синтаксис:
MATCH(търсена_стойност,търсен_масив,)
MATCH(търсеща_стойност, търсен_масив, [тип_съвпадение])
- търсена_стойност(търсена_стойност) е числото или текстът, който търсите. Аргументът може да бъде стойност, включително булево, или препратка към клетка.
- търсене_масив(масив_за_търсене) е диапазонът от клетки, в които да се търси.
- съвпадение_тип(match_type) – Този аргумент казва функцията СЪВПАДАнезависимо дали искате да намерите точно съвпадение или приблизително съвпадение:
- 1 или неопределено– намира максималната стойност, по-малка или равна на желаната. Масивът, който се търси, трябва да бъде сортиран във възходящ ред, тоест от най-малкия към най-големия.
- 0 - намира първата стойност, равна на желаната. За комбинация ИНДЕКС/СЪВПАДАвинаги искате точно съвпадение, така че третият аргумент на функцията СЪВПАДАтрябва да са равни 0 .
- -1 - намира най-малката стойност, по-голяма или равна на желаната стойност. Преглежданият масив трябва да бъде сортиран в низходящ ред, т.е. от най-големия към най-малкия.
На пръв поглед предимствата на функцията СЪВПАДАбуди съмнения. Кой трябва да знае позицията на елемент в диапазон? Искаме да знаем стойността на този елемент!
Нека ви напомня, че относителната позиция на стойността, която търсите (т.е. номера на реда и/или колоната) е точно това, което трябва да посочим за аргументите номер_ред(номер_на_ред) и/или колона_номер(колона_номер) функции ИНДЕКС(ИНДЕКС). Както си спомняте, функцията ИНДЕКСможе да върне стойността в пресечната точка на дадения ред и колона, но не може да определи кой ред и колона ни интересуват.
Как да използвате INDEX и MATCH в Excel
Сега, след като знаете основната информация за тези две функции, смятам, че вече става ясно как функционират СЪВПАДАи ИНДЕКСмогат да работят заедно. СЪВПАДАдефинира относителната позиция на търсената стойност в дадения диапазон от клетки и ИНДЕКСизползва това число (или числа) и връща резултата от съответната клетка.
Все още не е напълно ясно? Представяне на функции ИНДЕКСи СЪВПАДАв тази форма:
ИНДЕКС(,(МАЧ ( желаната стойност,колоната, която търсим,0))
=ИНДЕКС( колона, от която да извлечете;(СЪВПАДА( желаната стойност;колоната, която търсим;0))
Мисля, че ще бъде още по-лесно да се разбере с пример. Да предположим, че имате следния списък с държавни столици:
Нека намерим населението на една от столиците, да речем Япония, като използваме следната формула:
ИНДЕКС($D$2:$D$10,МАЧ("Япония",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;МАЧ("Япония";$B$2:$B$10;0))
Сега нека разбием какво прави всеки елемент от тази формула:
- функция СЪВПАДА(MATCH) търси стойността "Япония" в колона б, и конкретно в клетките B2:B10, и връща число 3 , тъй като „Япония“ е на трето място в списъка.
- функция ИНДЕКС(ИНДЕКС) използва 3
за аргумент номер_ред(номер_на_ред), който указва от кой ред трябва да се върне стойността. Тези. се получава проста формула:
ИНДЕКС($D$2:$D$10,3)
=ИНДЕКС($D$2:$D$10,3)Формулата казва нещо подобно: погледнете в клетки от D2преди D10и извлечете стойността от третия ред, т.е. от клетката D4, тъй като броенето започва от втория ред.
Ето резултата в Excel:
важно! Броят на редовете и колоните в масива, които функцията използва ИНДЕКС(ИНДЕКС), трябва да съответства на стойностите на аргумента номер_ред(номер_на_ред) и колона_номер(колона_номер) функции СЪВПАДА(СЪВПАДА). В противен случай резултатът от формулата ще бъде грешен.
Спрете, спрете... защо просто не можем да използваме функцията VLOOKUP(VLOOKUP)? Има ли смисъл да губя време в опити да разбера лабиринтите СЪВПАДАи ИНДЕКС?
VLOOKUP("Япония",$B$2:$D$2,3)
=VLOOKUP("Япония",$B$2:$D$2,3)
В този случай няма смисъл! Целта на този пример е чисто илюстративна, за да можете да разберете как функционира СЪВПАДАи ИНДЕКСработете по двойки. Следващите примери ще ви покажат истинската сила на пакета. ИНДЕКСи СЪВПАДА, която лесно се справя с много трудни ситуации, когато VLOOKUPзасяда.
Защо INDEX/MATCH е по-добър от VLOOKUP?
Когато решават коя формула да използват за вертикално търсене, повечето гурута на Excel вярват в това ИНДЕКС/СЪВПАДАмного по-добре от VLOOKUP. Въпреки това, много потребители на Excel все още прибягват до използването VLOOKUP, защото тази функция е много по-лесна. Това е така, защото много малко хора разбират напълно пълните ползи от преминаването от VLOOKUPна куп ИНДЕКСи СЪВПАДАи никой не иска да прекарва време в изучаване на по-сложна формула.
Топ 4 предимства от използването на MATCH/INDEX в Excel:
1. Търсете от дясно на ляво.Както всеки грамотен потребител на Excel знае, VLOOKUPне може да гледа наляво, което означава, че желаната стойност трябва задължително да бъде в най-лявата колона на изследвания диапазон. В случай на СЪВПАДА/ИНДЕКС, колоната за търсене може да бъде отляво или отдясно на диапазона за търсене. Пример: ще покаже тази функция в действие.
2. Безопасно добавяне или премахване на колони.Формули с функция VLOOKUPспрете да работите или върнете грешни стойности, ако премахнете или добавите колона към справочната таблица. За функция VLOOKUPвсяка вмъкната или премахната колона ще промени резултата от формулата, защото синтаксисът VLOOKUPизисква да посочите целия диапазон и конкретния номер на колона, от която искате да извлечете данни.
Например, ако имате маса A1:C10и искате да извлечете данни от колона б, тогава трябва да зададете стойността 2 за аргумент номер_индекс_колона(колона_номер) функции VLOOKUP, като този:
VLOOKUP("търсена стойност",A1:C10,2)
=VLOOKUP("търсена стойност";A1:C10;2)
Ако по-късно вмъкнете нова колона между колоните Аи б, тогава стойността на аргумента ще трябва да се промени от 2 на 3 , в противен случай формулата ще върне резултата от току-що вмъкнатата колона.
Използвайки СЪВПАДА/ИНДЕКС, Можете да премахвате или добавяте колони към изследвания диапазон, без да изкривявате резултата, тъй като колоната, съдържаща желаната стойност, е директно дефинирана. Наистина, това е голямо предимство, особено когато трябва да работите с големи количества данни. Можете да добавяте и премахвате колони, без да се притеснявате, че трябва да коригирате всяка функция, която използвате VLOOKUP.
3. Няма ограничение за размера на търсената стойност.Използвайки VLOOKUP, имайте предвид ограничението от 255 знака за дължината на търсената стойност, в противен случай рискувате да получите грешка #СТОЙНОСТ!(#СТОЙНОСТ!). Така че, ако таблицата съдържа дълги редове, единственото жизнеспособно решение е да се използва ИНДЕКС/СЪВПАДА.
Да приемем, че използвате следната формула: VLOOKUP, който търси в клетки от B5преди D10стойността, посочена в клетката A2:
VLOOKUP(A2,B5:D10,3,НЕВЯРНО)
=VLOOKUP(A2;B5:D10;3;НЕВЯРНО)
Формулата няма да работи, ако стойността е в клетката A2по-дълъг от 255 знака. Вместо това трябва да използвате подобна формула ИНДЕКС/СЪВПАДА:
ИНДЕКС(D5:D10;МАЧ(ВЯРНО;ИНДЕКС(B5:B10=A2;0);0))
=ИНДЕКС(D5:D10;МАЧ(ВЯРНО;ИНДЕКС(B5:B10=A2;0);0))
4. По-висока скорост на бягане.Ако работите с малки електронни таблици, тогава разликата в производителността на Excel най-вероятно няма да бъде забележима, особено в последните версии. Ако работите с големи таблици, които съдържат хиляди редове и стотици формули за търсене, Excel ще работи много по-бързо, когато използвате СЪВПАДАи ИНДЕКСвместо VLOOKUP. Като цяло подобна замяна увеличава скоростта на Excel с 13% .
Влияние VLOOKUPЕфективността на Excel е особено забележима, ако работната книга съдържа стотици сложни формули за масиви, като напр VLOOKUP+SUM. Факт е, че проверката на всяка стойност в масива изисква отделно извикване на функция VLOOKUP. Следователно, колкото повече стойности съдържа един масив и колкото повече формули за масив съдържа вашата таблица, толкова по-бавен е Excel.
От друга страна, формула с функции СЪВПАДАи ИНДЕКСпросто извършва търсене и връща резултата, като върши същата работа значително по-бързо.
INDEX и MATCH - примерни формули
Сега, когато разбирате причините, поради които трябва да научите функции СЪВПАДАи ИНДЕКС, нека да преминем към най-интересното и да видим как можете да приложите теоретичните знания на практика.
Как да търсите от лявата страна с помощта на MATCH и INDEX
Всеки учебник VLOOKUPказва, че тази функция не може да гледа наляво. Тези. ако колоната, която се търси, не е най-лявата колона в диапазона на търсене, тогава няма шанс да се стигне от VLOOKUPжелан резултат.
Функции СЪВПАДАи ИНДЕКС Excel е много по-гъвкав и не се интересува къде се намира колоната със стойността за извличане. Например, нека се върнем към таблицата със столиците на щатите и населението. Този път пишем формулата СЪВПАДА/ИНДЕКС, който ще покаже какво място заема столицата на Русия (Москва) по население.
Както можете да видите на фигурата по-долу, формулата се справя чудесно с тази задача:
INDEX($A$2:$A$10,MATCH("Русия",$B$2:$B$10,0))
Сега не би трябвало да имате проблем да разберете как работи тази формула:
- Първо, нека използваме функцията СЪВПАДА(MATCH), който намира позицията "Русия" в списъка:
MATCH("Русия",$B$2:$B$10,0))
=MATCH("Русия",$B$2:$B$10,0)) - След това задайте диапазона за функцията ИНДЕКС(ИНДЕКС), от който да извлечете стойността. В нашия случай това A2:A10.
- След това свързваме двете части и получаваме формулата:
ИНДЕКС($A$2:$A$10;МАЧ("Русия";$B$2:$B$10;0))
=ИНДЕКС($A$2:$A$10,MATCH("Русия",$B$2:$B$10,0))
улика:Правилното решение е винаги да използвате абсолютни препратки за ИНДЕКСи СЪВПАДАтака че диапазоните на търсене да не се губят при копиране на формулата в други клетки.
Изчисления с помощта на INDEX и MATCH в Excel (СРЕДНО, МАКС., МИН.)
Можете да вмъкнете други функции на Excel ИНДЕКСи СЪВПАДА, например, за да намерите минималната, максималната или най-близката до средната стойност. Ето няколко опции за формули, приложени към таблица от:
1. МАКС(МАКС.). Формулата намира максимума в колона д ° Ссъщия ред:
ИНДЕКС($C$2:$C$10,МАЧ(МАКС($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10,МАЧ(МАКС($D$2:I$10),$D$2:D$10,0))
Резултат: Пекин
2. МИН(МИН). Формула намира минимума в колона ди връща стойността от колоната ° Ссъщия ред:
ИНДЕКС($C$2:$C$10,МАЧ(МИН($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10,МАЧ(МИН($D$2:I$10),$D$2:D$10,0))
Резултат: Лима
3. СРЕДНО АРИТМЕТИЧНО(СРЕДНО АРИТМЕТИЧНО). Формулата изчислява средната стойност в диапазона D2:D10, след това намира най-близкия до него и връща стойността от колоната ° Ссъщия ред:
ИНДЕКС($C$2:$C$10,МАЧ(СРЕДНО($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10,МАЧ(СРЕДНО($D$2:D$10),$D$2:D$10,1))
Резултат: Москва
Точки, които трябва да запомните, когато използвате функцията AVERAGE с INDEX и MATCH
Използване на функцията СРЕДНО АРИТМЕТИЧНОв комбинация с ИНДЕКСи СЪВПАДА, като трети аргумент на функцията СЪВПАДАнай-често ще трябва да посочите 1 или -1 в случай, че не сте сигурни, че разглежданият диапазон съдържа стойност, равна на средната. Ако сте сигурни, че има такава стойност, поставете 0 за да намерите точно съвпадение.
- Ако посочите 1 , стойностите в колоната за търсене трябва да бъдат сортирани във възходящ ред и формулата ще върне максималната стойност, по-малка или равна на средната.
- Ако посочите -1 , стойностите в колоната за търсене трябва да бъдат сортирани в низходящ ред и ще бъде върната минималната стойност, по-голяма или равна на средната.
В нашия пример стойностите в колоната дса сортирани във възходящ ред, така че използваме типа съвпадение 1 . Формула ИНДЕКС/ТЪРСЕНЕ ПОУвръща „Москва“, тъй като населението на Москва е най-близката по-ниска стойност до средната (12 269 006).
Как да използвате INDEX и MATCH за търсене в известен ред и колона
Тази формула е еквивалентна на 2D търсене VLOOKUPи ви позволява да намерите стойността в пресечната точка на определен ред и колона.
В този пример формулата ИНДЕКС/СЪВПАДАще бъдат много подобни на формулите, които вече обсъдихме в този урок, само с една разлика. Познай какво?
Както си спомняте, синтаксисът на функцията ИНДЕКС(INDEX) позволява три аргумента:
ИНДЕКС(масив,номер_на_ред,)
ИНДЕКС(масив, номер_на_ред, [номер_на_колона])
И поздравления за тези от вас, които са го разбрали!
Нека започнем с написването на шаблона за формула. За да направим това, ние вземаме вече познатата формула ИНДЕКС/СЪВПАДАи добавете друга функция към него СЪВПАДА, което ще върне номера на колоната.
INDEX(Вашата маса ,(MATCH(, колона за търсене,0)),(МАЧ(, низ за търсене,0))
=ИНДЕКС(Вашата маса ,(МАЧ( стойност за вертикално търсене,колона за търсене,0)),(МАЧ( стойност за хоризонтално търсене,низ за търсене,0))
Моля, обърнете внимание, че за двуизмерно търсене трябва да посочите цялата таблица в аргумента масив(масив) функции ИНДЕКС(ИНДЕКС).
Сега нека подложим този модел на тест. По-долу виждате списък на най-населените държави в света. Да предположим, че нашата задача е да разберем населението на Съединените щати през 2015 г.
Добре, нека напишем формулата. Когато трябва да създам сложна формула в Excel с вложени функции, първо записвам всяка вложена поотделно.
Така че нека започнем с две функции СЪВПАДА, което ще върне номерата на редовете и колоните за функцията ИНДЕКС:
- МАЧ за колона- търсим в колона б, по-точно в диапазона B2:B11, стойността, която е посочена в клетката H2(САЩ). Функцията ще изглежда така:
MATCH($H$2,$B$1:$B$11,0)
=МАЧ($H$2,$B$1:$B$11,0)4 , тъй като "САЩ" е 4-тият елемент от списъка в колоната б(включително заглавие).
- MATCH за низ- търсим стойността на клетката H3(2015) в съответствие 1
, тоест в клетките A1:E1:
MATCH($H$3,$A$1:$E$1,0)
=МАЧ($H$3,$A$1:$E$1,0)Резултатът от тази формула ще бъде 5 защото „2015“ е в 5-та колона.
Сега вмъкваме тези формули във функцията ИНДЕКСи готово:
INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11.0),MATCH($H$3,$A$1:$E$1.0))
=ИНДЕКС($A$1:$E$11,МАЧ($H$2,$B$1:$B$11,0),МАЧ($H$3,$A$1:$E$1,0))
Ако заменим функциите СЪВПАДАвърху стойностите, които връщат, формулата ще стане лесна и разбираема:
ИНДЕКС($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11,4,5))
Тази формула връща стойността в пресечната точка 4-тилинии и 5-тиколони в диапазон A1:E11, тоест стойността на клетката E4. Просто? да
Търсете по множество критерии с INDEX и MATCH
В учебника VLOOKUPпоказахме пример за формула с функция VLOOKUPза търсене по множество критерии. Въпреки това, значително ограничение на това решение беше необходимостта от добавяне на спомагателна колона. Добрите новини: Формулата ИНДЕКС/СЪВПАДАможе да търси по стойности в две колони, без да е необходимо да създавате спомагателна колона!
Да предположим, че имаме списък с поръчки и искаме да намерим сумата по два критерия − име на купувача(Клиент) и продукт(Продукт). Въпросът се усложнява от факта, че един купувач може да закупи няколко различни продукта наведнъж, а имената на купувачите в таблицата на листа таблица за справкиподредени в произволен ред.
Ето формулата ИНДЕКС/СЪВПАДАрешава проблема:
(=INDEX("Таблица за справка"!$A$2:$C$13,MATCH(1,(A2="Таблица за справка"!$A$2:$A$13)*
(B2="Таблица за търсене"!$B$2:$B$13),0),3))
(=INDEX("Таблица за справка"!$A$2:$C$13;MATCH(1;(A2="Таблица за справка"!$A$2:$A$13)*
(B2="Таблица за търсене"!$B$2:$B$13);0);3))
Тази формула е по-сложна от останалите, които обсъдихме по-рано, но е въоръжена с познания за функциите ИНДЕКСи СЪВПАДАЩе я преодолееш. Най-трудната част е функцията СЪВПАДАМисля, че първо трябва да се обясни.
MATCH(1,(A2="Таблица за справка"!$A$2:$A$13),0)*(B2="Таблица за справка"!$B$2:$B$13)
MATCH(1;(A2="Таблица за справка"!$A$2:$A$13);0)*(B2="Таблица за справка"!$B$2:$B$13)
Във формулата, показана по-горе, стойността, която търсите, е 1 , а масивът за търсене е резултатът от умножението. Добре, какво трябва да умножим и защо? Нека вземем всичко по ред:
- Вземете първата стойност в колоната А(Клиент) на лист основна масаи го сравнете с всички имена на купувачи в таблицата на листа таблица за справки(A2:A13).
- Ако се намери съвпадение, уравнението се връща 1 (ВЯРНО), а ако не - 0 (НЕВЯРНО).
- След това правим същото за стойностите на колоните б(Продукт).
- След това умножаваме получените резултати (1 и 0). Само ако се намерят съвпадения и в двете колони (т.е. и двата критерия са верни), ще получите 1 . Ако и двата критерия са грешни или само един от тях е изпълнен, ще получите 0 .
Сега разбирате защо попитахме 1 като желаната стойност? Правилно е функцията СЪВПАДАвърна позицията само когато и двата критерия са изпълнени.
Забележка:В този случай трябва да използвате третия незадължителен аргумент на функцията ИНДЕКС. Необходимо е, тъй като в първия аргумент ние определяме цялата таблица и трябва да кажем на функцията от коя колона да извлече стойността. В нашия случай това е колоната ° С(Сума) и така въведохме 3 .
И накрая, защото трябва да проверим всяка клетка в масива, тази формула трябва да е формула за масив. Можете да видите това по фигурните скоби, в които е ограден. Така че, когато приключите с въвеждането на формулата, не забравяйте да натиснете Ctrl+Shift+Enter.
Ако всичко е направено правилно, ще получите резултата като на фигурата по-долу:
INDEX и MATCH комбинирани с IFERROR в Excel
Както вероятно вече сте забелязали (и то повече от веднъж), ако въведете грешна стойност, например, която не е в преглеждания масив, формулата ИНДЕКС/СЪВПАДАсъобщава за грешка #N/A(#N/A) или #СТОЙНОСТ!(#СТОЙНОСТ!). Ако искате да замените такова съобщение с нещо по-разбираемо, можете да вмъкнете формула с ИНДЕКСи СЪВПАДАвъв функция ЕСЛИОШИБКА.
Синтаксис на функцията ЕСЛИОШИБКАмного просто:
IFERROR(стойност,стойност_при_грешка)
IFERROR(стойност, стойност_при_грешка)
Къде е аргументът стойност(стойност) е стойността, която се проверява за грешка (в нашия случай резултатът от формулата ИНДЕКС/СЪВПАДА); и аргументът стойност_при_грешка(value_if_error) е стойността, която трябва да се върне, ако формулата изведе грешка.
Например, можете да поставите функция ЕСЛИОШИБКАкато този:
IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11.0),MATCH($G$3,$A$1:$E$1.0)),
„Не е намерено съответствие. Моля, опитайте отново!“) =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1) :$E$1;0));
„Няма намерени съвпадения. Моля, опитайте отново!“)
И сега, ако някой въведе грешна стойност, формулата ще даде следния резултат:
Ако предпочитате да оставите клетката празна в случай на грешка, можете да използвате кавички (“”) като стойност на втория аргумент на функцията ЕСЛИОШИБКА. Като този:
IFERROR(INDEX(масив,MATCH(търсена_стойност, търсен_масив,0),"")
IFERROR(INDEX(масив,MATCH(търсена_стойност,търсен_масив,0),"")
Надявам се, че поне една от формулите, описани в този урок, ви е била полезна. Ако сте срещнали други задачи за търсене, за които не можете да намерите подходящо решение сред информацията в този урок, не се колебайте да опишете проблема си в коментарите и ние всички ще се опитаме да го разрешим заедно.
Да приемем, че трябва да намерите вътрешния телефонен номер на служител, като използвате неговия номер, и да прецените правилно размера на комисионната за сумата на продажбите. Вие търсите данни, за да намерите бързо и ефективно конкретни данни в списък и автоматично проверявате дали данните се използват правилно. След като прегледате данните, можете да извършите изчисления и да покажете резултатите, като посочите върнатите стойности. Има няколко начина за търсене на стойности в списък с данни и показване на резултатите.
В тази статия
Намиране на стойности в списък вертикално чрез точно съвпадение
Можете да използвате функцията VLOOKUP или комбинация от функциите INDEX и MATCH, за да изпълните тази задача.
Примери за използване на функцията VLOOKUP
Функция VLOOKUP.
Примери за ИНДЕКСИ и МАЧОВЕ
Какво означава:
=INDEX(трябва да върне стойност от C2:C10, която ще съответства на MATCH(първа стойност „Зеле“ в масив B2:B10))
Формулата търси първата стойност в клетка C2:C10, съответстваща на зеле(в B7) и връща стойността в C7 ( 100 ) е първата стойност, съответстваща на зеле.
За повече информация вижте функцията Index и функцията MATCH.
Намиране на стойности в списък вертикално чрез приблизително съвпадение
За да направите това, използвайте функцията VLOOKUP.
Важно:Уверете се, че стойностите в първия ред са сортирани във възходящ ред.
В горния пример функцията VLOOKUP търси името на ученик, който има 6 закъснения в диапазона A2:B7. Няма запис в таблицата за 6 закъснения, така че VLOOKUP търси следващото най-високо съвпадение под 6 и намира стойността 5, свързана с първото име Дейв, и следователно се връща Дейв.
За повече информация вижте раздела за функцията VLOOKUP.
Намиране на вертикални стойности в списък с неизвестен размер с точно съвпадение
За да изпълните тази задача, използвайте функциите OFFSET и MATCH.
Забележка:Този подход се използва, ако данните са във външен диапазон от данни, който актуализирате всеки ден. Знаете, че колона B има цена, но не знаете колко реда данни връща сървърът и първата колона не е сортирана по азбучен ред.
C1е горната лява клетка на диапазона (наричана още начална клетка).
Съвпадение ("портокали"; C2: C7; 0)търси оранжев цвят в диапазона C2:C7. Не трябва да включвате началната клетка в диапазона.
1 - броя на колоните вдясно от началната клетка, за които трябва да се върне върнатата стойност. В нашия пример върнатата стойност е в колона D, Продажби.
Намиране на стойности в списък хоризонтално чрез точно съвпадение
За изпълнение на тази задача се използва функцията HLOOKUP. По-долу е даден пример.
Функцията HLOOKUP извършва търсене в колона Продажбии връща стойността от ред 5 в посочения диапазон.
За повече информация вижте раздела за функциите на HLOOKUP.
Създайте формула за справка с помощта на съветника за справка (само за Excel 2007)
Забележка:Добавката Lookup Wizard е отхвърлена в Excel 2010. Тази функционалност е заменена от Function Wizard и наличните функции за търсене и препратка (справка).
В Excel 2007 съветникът за справка създава формула за справка въз основа на данни от работен лист, който има заглавия на редове и колони. Съветникът за търсене ви помага да намерите други стойности в ред, когато знаете стойността в една колона и обратно. Помощникът за търсене използва индекс и MATCH във формулите, които създава.
Основната цел на програмата Excel office е извършването на изчисления. Документът на тази програма (книга) може да съдържа много листове с дълги таблици, пълни с числа, текст или формули. Автоматичното бързо търсене ви позволява да намерите необходимите клетки в тях.
Просто търсене
За да търсите стойност в електронна таблица на Excel, в раздела Начало отворете падащия списък на инструмента Намиране и замяна и щракнете върху елемента Намиране. Същият ефект може да се получи с помощта на клавишната комбинация Ctrl + F.
В най-простия случай, в прозореца Намиране и замяна, който се появява, въведете стойността, която търсите, и щракнете върху Намери всички.
Както можете да видите, резултатите от търсенето се появиха в долната част на диалоговия прозорец. Намерените стойности са подчертани в червено в таблицата. Ако вместо „Намиране на всички“ щракнете върху „Намиране на следващо“, първо ще се търси първата клетка с тази стойност, а при повторно щракване – втората.
Текстът се търси по същия начин. В този случай текстът за търсене се въвежда в лентата за търсене.
Ако данните или текстът не се търсят в цялата електронна таблица на Excel, първо трябва да се избере областта за търсене.
подробно търсене
Да предположим, че искате да намерите всички стойности между 3000 и 3999. В този случай ще въведете 3??? в низа за търсене. Заместващ знак "?" замества всяка друга.
Анализирайки резултатите от извършеното търсене, може да се отбележи, че наред с правилните 9 резултата, програмата изведе и неочаквани, подчертани в червено. Те са свързани с присъствието на числото 3 в клетка или формула.
Можете да бъдете доволни от повечето от получените резултати, пренебрегвайки грешните. Но функцията за търсене в Excel 2010 може да работи много по-точно. Това е инструментът Опции в диалоговия прозорец.
Щраквайки върху „Опции“, потребителят може да извърши разширено търсене. На първо място, нека обърнем внимание на елемента "Зона за търсене", в който стойността "Формули" е зададена по подразбиране.
Това означава, че търсенето е извършено, включително в тези клетки, където няма стойност, а формула. Наличието на числото 3 в тях даде три неверни резултата. Ако изберете „Стойности“ като обхват на търсене, тогава ще се търсят само данни и неправилните резултати, свързани с клетки с формули, ще изчезнат.
За да се отървете от единствения останал неправилен резултат на първия ред, в прозореца за разширено търсене изберете елемента „Цялата клетка“. След това резултатът от търсенето става 100% точен.
Такъв резултат може да се постигне чрез незабавно избиране на елемента „Цялата клетка“ (дори оставяйки стойността „Формули“ в „Зона за търсене“).
Сега нека се обърнем към елемента "Търсене".
Ако вместо стандартната "На листа" изберете стойността "В книгата", тогава няма нужда да сте на листа на клетките, които търсите. Екранната снимка показва, че потребителят е започнал търсенето, докато е на празен лист 2.
Следващият елемент в прозореца за разширено търсене е „Преглед“, който има две значения. По подразбиране е зададено "по редове", което означава последователността на сканиране на клетки по редове. Избирането на друга стойност - "по колони", ще промени само посоката на търсене и последователността на резултатите.
Когато търсите в документи на Microsoft Excel, можете да използвате и друг заместващ знак - "*". Ако се вземе предвид "?" означава произволен знак, тогава "*" замества не един, а произволен брой знаци. По-долу е екранна снимка на търсене на Луизиана.
Понякога търсенето трябва да е чувствително към главни и малки букви. Ако думата луизиана е с главна буква, тогава резултатите от търсенето няма да се променят. Но ако в прозореца за разширено търсене изберете „Регистри и малки букви“, търсенето ще бъде неуспешно. Програмата ще счита думите Луизиана и Луизиана за различни и, разбира се, няма да намери първата от тях.
Разновидности за търсене
Намиране на съвпадения
Понякога е необходимо да се открият дублиращи се стойности в таблица. За да търсите съвпадения, първо трябва да маркирате диапазона на търсене. След това в същия раздел Начало, в групата Стилове, отворете инструмента за условно форматиране. След това последователно изберете елементите „Правила за избор на клетки“ и „Дублирани стойности“.
Резултатът е показан на екранната снимка по-долу.
Ако е необходимо, потребителят може да промени цвета на визуалния дисплей на съответстващите клетки.
Филтриране
Друг вид търсене е филтрирането. Да предположим, че потребителят иска да намери числови стойности в колона B между 3000 и 4000.
Както можете да видите, показват се само редове, които отговарят на въведеното условие. Всички останали бяха временно скрити. За да се върнете в първоначалното състояние, повторете стъпка 2.
Бяха разгледани различни опции за търсене, използвайки като пример Excel 2010. Как да търсите в Excel на други версии? Има разлика в прехода към филтриране във версия 2003. В меню "Данни" изберете последователно командите "Филтър", "Автофилтър", "Условие" и "Потребителски автофилтър".
Видео: Търсене в електронна таблица на Excel
