В тази статия ще научим как да извлечем цена от списък, който отговаря на критериите за категория и елемент в Excel.
Сценарий:
Лесно е да се намери стойност с един критерий в таблица, за това можем просто да използваме VLOOKUP. Но когато нямате критерии за една колона във вашите данни и трябва да намерите критерии за множество колони, за да съответстват на стойност, VLOOKUP не помага.
Обща формула както за категория, така и за артикул
| = INDEX (lookup_range, MATCH (1, INDEX ((item1 = item_range)) * (category2 = range_ range), 0,1), 0)) |
lookup_range: Това е диапазонът, от който искате да извлечете стойност.
Критерии1, Критерии2, Критерии N: Това са критериите, които искате да съответствате в диапазон1, диапазон2 и Обхват N. Можете да имате до 270 критерия - двойки диапазони.
Range1, range2, rangeN: Това са диапазоните, в които ще отговаряте на съответните критерии
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем как да използваме функцията, използвайки пример. Тук имаме таблица с данни. Искам да изтегля името на клиента, използвайки Дата на резервация, Строител и Площ. Тук имам три критерия и един диапазон за търсене.
Напишете тази формула в клетка 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) Това като цяло ще се върне
Което ще се преведе в
| {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).
Ако можете да натиснете CTRL + SHIFT + ENTER последователно, тогава можете да премахнете вътрешната функция INDEX. Просто напишете тази формула и натиснете CTRL + SHIFT + ENTER.
Формула
| = ИНДЕКС (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0)) |
Формула за общ масив за търсене на множество критерии
| = INDEX (диапазон на търсене, MATCH (1, (критерии1 = диапазон1)*(критерии2 = диапазон2)*(критерииN = диапазонN), 0)) |
Ето всички бележки за наблюдение, използващи формулата в Excel
Бележки:
- Използвайте Vlookup, ако отговаряте на един критерий, това е обичайна практика.
- Можете да добавите още редове и колони в масива за търсене.
- Текстовите аргументи трябва да бъдат дадени в кавички ("").
- Таблицата VLOOKUP трябва да има lookup_array в най -лявата или първата колона.
- Индексът на col не може да бъде 1, тъй като е масив за търсене
- Аргумент 0 се използва за стойността на точното съвпадение. Използвайте 1 за приблизителната стойност на съвпадението.
- Функцията връща грешка #N/A, ако търсената стойност не е намерена в масива за търсене. Така че хванете грешката, ако е необходимо.
Надявам се, че тази статия за това как да извлечете цена от списък, която отговаря на критериите за категория и елемент в Excel, е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако вашите блогове са ви харесали, споделете ги с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Как да извлечете най -новата цена в 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 е от съществено значение за подготовката на вашето табло.