Комбинирано използване на sumif (vlookup)
Sumif с VLOOKUP е комбинация от две различни условни функции, SUMIF се използва за сумиране на клетките въз основа на някакво условие, което взема аргументи от диапазон, в който има данните и след това критериите или условието и клетките за сумиране, вместо критериите, които използваме VLOOKUP като критерии, когато има голямо количество данни в множество колони.
SUMIF е функция, представена в Excel от версия 2007, за да сумира различните стойности, съответстващи на критериите. VLOOKUP е една от най-добрите формули за събиране на данни от други таблици. Когато има множество условия и колони, sumif (vlookup) се използва за извършване на множество изчисления на Excel листа. Недостатъкът на функцията SUMIF, връщаща единствения номер, се преодолява с помощта на VLOOKUP. VLOOKUP помага да се върнат всякакви данни от таблица въз основа на съответстващите критерии.
Обяснение
Функция SUMIF: Функция тригонометрия и математика е да сумира стойностите, когато установеното условие е вярно. Сумираната стойност се получава въз основа само на един критерий.
Когато се справяме с функцията SUMIF в Excel, се използва следната формула
- Обхват: Това е обхватът на клетките, използвани за оценка на установените критерии
- Критерии: Условието е да се сумират стойностите. Това може да е препратка към клетка, номер и друга функция на Excel. Когато искаме да комбинираме SUMIF и VLOOKUP, функцията vlookup ще бъде въведена на мястото на критериите
- Обхват на сумата: Това е диапазонът от клетки, посочени за сумиране на числовите стойности.
Сега формулата е променена на
Формула = SUMIF (Обхват, Vlookup (справочна стойност, масив на таблица, номер на индекс на колона, [търсене на обхват]), [обхват на сумата])
- Справочна стойност: Указва стойността, която трябва да се търси в таблица. Това може да е референция или стойност.
- Масив на таблица: Това е обхватът на таблицата, съдържащ две или повече от две колони.
- Номер на индекс на колона : Това е относителният индекс на колона, който трябва да се посочи за връщане на необходимите данни от конкретна колона.
- [Търсене на диапазон]: Или 0 или 1, за да се определи дали трябва да се върне точната стойност или да се върне приблизителната стойност. Но това не е задължително за потребителя. 0 показва точно съвпадение, а 1 показва приблизително съвпадение.
Как да използвам SUMIF с VLOOKUP функция?
Комбинираното използване на sumif (vlookup) е полезно при търсене на данни въз основа на отделните критерии. Те се използват по много начини в Excel за извършване на изчисленията чрез търсене на данни. Те се използват колективно в бизнес средата за изпълнение на различни задачи за вземане на добри решения. За да използвате ефективно тези функции заедно,
Първо, функцията SUMIF трябва да се въведе, използвайки двата метода.
Първи метод: Формулата трябва да пише от клавиатурата, както е показано на изображението по-долу.
Втори метод: Функцията SUMIF може да се вмъкне от раздела „Формула“, както е показано на изображението.
След въвеждане на функцията SUMIF, формулата за VLOOKUP се въвежда във функцията SUMIF чрез заместване на елемента „Критерии“. Всички параметри на VLOOKUP, включително стойността за търсене, масив на таблица, номер на индекс на колоната и търсене на диапазон. Те трябва да бъдат затворени в скоби, за да се избегнат грешки с формулата. Обхватът на стойностите, които трябва да се сумират, са включени в елементите на обхвата на сумата на функцията SUMIF. Накрая клавишите CTRL, SHIFT и ENTER са натиснати заедно, за да улеснят стойностите като масив.
Примери
Можете да изтеглите този SUMIF с VLOOKUP шаблон на Excel тук - SUMIF с VLOOKUP шаблон на ExcelПример # 1 - Използване на sumif (vlookup) заедно за определяне на някаква стойност
Този пример показва как да използваме sumif (vlookup) заедно, за да намерим сумата от продажбите през един и същ месец в различни години. Следните данни се разглеждат за този пример, както е показано на екранната снимка.
Разглежданата справочна таблица е показана, както е споменато по-долу. Той включваше референтни стойности за месеците от януари до декември.
След въвеждане на данни в основната таблица и справочната таблица, функцията SUMIF се използва за определяне на общите продажби, генерирани през различни месеци от годината. Тук разглежданата стойност за справка е месецът. Формулата, комбинираща sumif (vlookup), е показана като,
Общите продажби през месец януари се определят като 17263,3 . Когато променихме стойността на външния вид на друг месец, се генерират съответните общи продажби.
Пример # 2 - Определяне на сумата въз основа на критерии за съвпадение в различни работни листове
В този пример справочната таблица и основната таблица се вземат на различни листове, а не на един лист. Данните за справочната таблица са показани на екрана по-долу.
Данните на основната таблица са показани на екрана по-долу.
За да се определят общите продажби, името на продавача се приема като справочна стойност и идентификационните номера на служителите се използват за справка. Формулата се въвежда, както е представено по-долу, и се наблюдават малко промени във формулата в сравнение с първия пример.
В този пример, вместо да се избере масив за справка, се споменава само Lookup_table. Чрез натискане на трите клавиша, включително CTRL, SHIFT и ENTER, се получават точни резултати.
Когато името на продавача се промени на имена, споменати в справочника, сумата на продажбите варира и дава нов резултат.
Ползи
По-долу са предимствата от използването на тези функции.
- Можем лесно да извлечем стойностите от друга таблица, за да извършим изчисления.
- Определяне на сумата от стойности, представени в диапазон, който отговаря на критериите, споменати в различни аспекти на бизнеса.
Неща за запомняне
- Докато използвате функцията VLOOKUP, индексният номер на колоната не трябва да бъде по-нисък от 1, за да се избегнат грешки.
- Индексите трябва да се дават на колоните за справочна таблица, като се посочват с номера 1, 2, 3 и т.н.
- CTRL + SHIFT + ENTER в Excel трябва да се използват вместо въвеждане на ключ, тъй като VLOOKUP се въвежда като формула на масив.
- За извличане на стойности и определяне на сумата от стойностите на масива се изисква дефиниране на две таблици, включително Main и Lookup.
- SUMIF предоставя точни резултати само за цифрови данни, няма да работи за друг вид данни.