В тази статия ще научим как да търсим точни съвпадения с помощта на функцията SUMPRODUCT в Excel.
Сценарий:
С прости думи, докато работим с таблици с данни, понякога трябва да търсим стойности с букви, чувствителни към малки и големи букви в Excel. Функциите за търсене като VLOOKUP или MATCH не намират точно съвпадение, тъй като това не са чувствителни към малки и големи букви. Да приемем, че работим върху данни, при които 2 имена се диференцират въз основа на чувствителността към регистъра, т.е. Jerry & JERRY са две различни собствени имена. Можете да изпълнявате задачи като операции в множество диапазони, като използвате формулата, обяснена по -долу.
Как да решим проблема?
За този проблем ще трябва да използваме функцията SUMPRODUCT & EXACT. Сега ще направим формула от функцията. Тук ни е дадена таблица и трябва да намерим стойности, съответстващи на точното съвпадение в таблицата в Excel. Функцията SUMPRODUCT връща SUM на съответните TRUE стойности (като 1) и игнорира стойности, съответстващи на FALSE стойности (като 0) във върнатия масив
Обща формула:
| = SUMPRODUCT ( - (ТОЧНО (lookup_value, lookup_array)), (return_array)) |
lookup_value: стойност за търсене.
lookup_array: масив за търсене за стойност за търсене.
return_array: масив, върната стойност, съответстваща на масив за търсене.
-: Отрицанието (-) char променя стойностите, TRUEs или 1s на FALSEs или 0s и FALSEs или 0s в TRUEs или 1s.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу. Тук имаме данни за количеството и цената на продуктите, получени на датите. Ако някой продукт е закупен два пъти, той има 2 имена. Тук трябва да намерим броя на елементите за всички предмети от първа необходимост. Така че за това сме назначили 2 списъка като получен списък и основни неща, необходими в колона за формулата. Сега ще използваме формулата по -долу, за да получим количеството на "млякото" от таблицата.
Използвайте формулата:
| = СУМПРОДУКТ ( - (ТОЧНО (F5, B3: B11)), (C3: C11)) |
F6: потърсете стойност в клетка F6
B3: B11: масивът за търсене е Items
C3: C11: масивът за връщане е Количество
-: Отрицанието (-) char променя стойностите, TRUEs или 1s на FALSEs или 0s и FALSEs или 0s в TRUEs или 1s.
Тук диапазонът е даден като референтна клетка. Натиснете Enter, за да получите количеството.
Както можете да видите, 58 е количеството, съответстващо на стойността "мляко" в клетка В5, а не "мляко" в клетка В9. Ще трябва да потърсите стойност "Мляко", ако имате нужда от количеството "54" в клетка С9.
Можете да търсите цена, съответстваща на стойността „мляко“, само като използвате формулата, показана по -долу.
Използвайте формулата:
| = СУМПРОДУКТ ( - (ТОЧНО (F5, B3: B11)), (D3: D11)) |
F6: потърсете стойност в клетка F6
B3: B11: масивът за търсене е Items
D3: D11: масивът за връщане е Price
Тук имаме 58 е цената, съответстваща на стойността "мляко" в клетка В5, а не "мляко" в клетка В9. Ще трябва да потърсите стойност „Мляко“, ако имате нужда от Цена „15,58“ в клетка D9.
Уникални стойности в масива за търсене
По същия начин можете да намерите уникалните стойности, като използвате формулата. Тук стойността за търсене "EGGS", използвана като пример.
На изображението, показано по -горе, имаме стойностите, коригиращи същата формула. Но проблемът възниква, ако той има уникална стойност и не търсите правилната стойност за търсене. Както тук, използвайки стойността "Хляб" във формулата за търсене в таблица.
Формулата връща 0 като количество и цена, но това не означава, че количеството и цената на хляба са 0. Просто формулата връща 0 като стойност, когато търсената стойност не е намерена. Затова използвайте тази формула само за да търсите точното съвпадение.
Можете също така да извършите търсене на точни съвпадения, като използвате функциите INDEX и MATCH в Excel. Научете повече за това как да извършите търсене с чувствителност към регистъра, като използвате функцията INDEX & MATCH в Excel. Можете също да потърсите частичните съвпадения, като използвате заместващи знаци в Excel.
Ето някои наблюдателни бележки, показани по -долу.
Бележки:
- Формулата работи само за търсене на точното съвпадение.
- Функцията SUMPRODUCT разглежда нечисловите стойности като 0.
- Функцията SUMPRODUCT разглежда логическата стойност TRUE като 1 и False като 0.
- Масивът на аргументите трябва да бъде със същия размер, в противен случай функцията връща грешка.
- Функцията SUMPRODUCT връща стойността, съответстваща на TRUE стойностите във върнатия масив, след като вземете отделни продукти в съответния масив.
- Оператори като равен на ( = ), по -малко от равно на ( <= ), по-голяма от ( > ) или не е равно на () може да се извърши в рамките на приложена формула, само с числа.
Надявам се, че тази статия за Как да търсите точно съвпадение с помощта на функцията SUMPRODUCT в Excel е обяснителна. Намерете още статии за формулите за броене тук. Ако нашите блогове са ви харесали, споделете го с вашите първенци във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
Използвайте INDEX и MATCH за търсене на стойност : Формулата INDEX-MATCH се използва за динамично и точно търсене на стойност в дадената таблица. Това е алтернатива на функцията VLOOKUP и тя преодолява недостатъците на функцията VLOOKUP.
Сума по OFFSET групи в редове и колони : Функцията OFFSET може да се използва за динамично сумиране на група клетки. Тези групи могат да бъдат навсякъде в листа.
Как да извлечете всяка N -та стойност в диапазон в Excel: Използвайки функцията OFFSET на Excel, можем да извлечем стойности от редуващи се редове или колони. Тази формула използва функцията ROW за редуване през редове и функцията COLUMN за редуване в колони.
Как да използвате функцията OFFSET в Excel : Функцията OFFSET е мощна функция на Excel, която се подценява от много потребители. Но експертите знаят силата на функцията OFFSET и как можем да използваме тази функция за извършване на някои магически задачи във формулата на Excel. Ето основите на функцията OFFSET.
Как да използвате заместващи символи в Excel : Пребройте клетки, съвпадащи с фрази, използвайки заместващи знаци в Excel
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате tФункцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на Excel, която се използва за търсене на стойност от различни диапазони и листове в Excel.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности в Excel. Функцията COUNTIF е от съществено значение за подготовката на вашето табло за управление.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите с конкретни условия.