В този урок ще намерите някои интересни примери, които показват как да използвате функцията CDF (VLOOKUP), заедно със сумата (SUM) или SUMIF (SUMIF), за да Excel, за да търсите и сумиране на стойностите в една или повече от критериите.

Задачите могат да бъдат различни, но тяхното значение е същото - трябва да се намери и да се сумират стойностите на един или повече критерии в Excel. Какви са тези стойности? Всеки числен. Какви са тези критерии? Всяка ... Тъй като броят или препратка към клетка, съдържаща стойност, и завършва с логически оператори, както и резултатите от формули в Excel.

CDF и размера на Excel - изчисляване на размера намери съвпадение ценности

Ако работите с числови данни в Excel, вие доста често ще трябва не само да се извлекат сродно данни от друга маса, но също така обобщи няколко колони или редове. За да направите това, можете да комбинирате функцията SUM и CDF. както е показано по-долу.

Да предположим, че имаме списък на стоки с данните за продажбите в продължение на няколко месеца, с отделна графа за всеки месец. Източник на данни - списък Месечните продажби:

Сега ние трябва да се направи таблица с резултатите от сумите от продажбите за всеки продукт.

Решението на този проблем - използва набор от константи в аргумент col_index_num (колона_номер) VLOOKUP. Ето един пример от формулата:

= SUM (VLOOKUP (за справка стойност, справка гама ,, невярно))
= SUM (CDF (lookup_value маса ;; LIE))

Както можете да видите, ние използваме масив на третия аргумент да търсите по няколко пъти в една и съща VLOOKUP. и за получаване на сумата на стойностите в колони 2 и 4 3.

Сега нека да прилага тази комбинация от PPS и количеството данни в нашата маса, за да се намери общата сума на продажбите в колони В чрез M:

Важно! Ако въведете формула за масив, не забравяйте да натиснете клавишната комбинация Ctrl + Shift + Enter, вместо обичайната влиза натискане. Microsoft Excel ще влезе във вашата формула в фигурни скоби:

Ако се ограничим само с натискане на Enter. изчисляването ще бъде направено само в първата стойност на масива, което ще доведе до неправилни резултати.

Може би сте се чудили защо формулата на фигурата по-горе показва [@Product]. като желаната стойност. Това е така, защото моята информация е превърната в една маса с командния таблица (таблица) раздел Insert (Insert). Аз предпочитам да работя с напълно функционален Excel електронна таблица от обикновен диапазон. Например, когато въведете формула в една от клетките, Excel автоматично го копира в цялата колона, което спестява няколко ценни секунди.

Както можете да видите, за да използвате функцията VLOOKUP в Excel и сумата е съвсем проста. Все пак, това не е идеалното решение, особено ако работите с големи таблици. Фактът, че използването на формули за масиви може да забави работата на приложението, като всяка стойност в масива прави една покана за функцията на CDF. Оказва се, че по-високите стойности в масива, толкова по формулите за масиви в работната книга и по-бавно течаща Excel.

Този проблем може да се преодолее чрез използване на комбинация от функции INDEX (ИНДЕКС) и MATCH (MATCH) вместо VLOOKUP (CDF) и SUM (сума). По-късно в тази статия ще видите някои примери за такива формули.

Извършване на други изчисления, използвайки функцията CDF в Excel

Ние току-що демонтирани примера за това как да извлечете стойностите на няколко колони в таблицата и се изчислява тяхната сума. По същия начин, можете да извършвате други математически операции с резултатите, които се връщат от CDF. Ето някои примери за формули:

Ние изчисляваме средната:

Формула търси стойност А2 на клетките в таблицата на лист търсене и изчислява аритметични средни стойности, които се намират в пресечната точка на редове и колони В, С и D.

Ние считаме, максимално:

Формула търси стойност А2 на клетките в таблицата на лист търсене и връща максимума на стойностите, които се намират в пресечната точка на редове и колони В, С и D.

Ние намираме най-малко:

Формула търси стойност А2 на клетките в таблицата на лист търсене и връща минимум на стойностите, които се намират в пресечната точка на редове и колони В, С и D.

Изчисляваме% от сумата:

Формулата изглежда на стойността на клетка A2 в таблицата с лист търсене. след това обобщава стойностите, които се намират в пресечната точка на редове и колони В, С и D, и след това се изчислява само 30% от сумата.

Ако добавим и по-горе формула в таблицата на предишния пример. резултатът ще изглежда така:

Нека разгледаме един пример, че ви стане по-ясно за това, което има разговор. Да предположим, че имаме таблица, която описва имената на клиенти, закупените продукти, както и на броя (таблица Главна таблицата). В допълнение, има и втора таблица, съдържаща цените на стоките (Таблица Таблица за търсене). Нашата задача - да напише формула, която намира сумата на всички посочени клиентски поръчки.

Тъй като това е формула за масив, не забравяйте да натиснете клавишната комбинация Ctrl + Shift + Enter в края на входа.

Търсене на маса - е името на листа, където границите се гледа.

Нека да анализираме компоненти на формулата, че сте разбрали как работи, и да я персонализирате, за да отговаря на нуждите ви. SUM функция, докато настрана, тъй като целта му е очевидна.

Тъй като нашата формула - това е формула за масив, тя се повтаря по-горе стъпки за всяка стойност в масива за търсене. В крайна сметка, сумата на функцията изчислява сумата от стойностите, получени чрез умножаване. Не е трудно, съгласни ли сте?

CDF и SUMIF - намерете и обобщава стойности, които отговарят на определени критерии

SUMIF функция (SUMIF) в Excel, подобен на сумата (SUM), която току-що демонтирани, защото тя също добавя стойност. Единствената разлика е, че SUMIF обобщава само стойностите, които отговарят на определени критерии за търсене. Например, най-простата формула с SUMIF:

- обобщава всички стойности на клетки в A2 диапазон: A10. което е повече от 10.

Много просто, нали? Сега нека да погледнем малко по-сложен пример. Да предположим, че имаме таблица, която описва имената на продавачите и броят им ID (търсене таблицата). В допълнение, има и друга таблица, в която един и същ идентификатор, свързан с данните за продажбите (Main таблицата). Нашата задача - да се намери сумата от продажби за даден търговец на дребно. Има два утежняващи обстоятелства:

• Обобщена таблица (Main таблицата) съдържа набор от записи за едно ID на случаен принцип.
• Не можете да добавите колона с имената на продавачите на главната таблица.

Да запиша формулата, която ще намерите всичко от продажбите, реализирани да поиска от продавача, и ще обобщи намерени стойности.

Преди да започнем, нека ви припомня SUMIF функция синтаксис (SUMIF):

• диапазон (обхват) - аргументът говори за себе си. Това е просто диапазона от клетки, които искате да се направи оценка на даден критерий.
• Критерии (критерии) - условие, че споменатата формула, която се сумират стойности. Тя може да бъде число, позоваване на функция клетка или друг експресионен Excel.
• общ_сбор_диапазон (общ_сбор_диапазон) - по избор, но много важен аргумент за нас. Той определя интервала от свързани клетки, които ще бъдат обобщени. Ако не е посочено, Excel събира стойностите клетъчни, в първия аргумент на функцията.

След събиране на всички заедно, нека да се определи една трета аргумент за нашата SUMIF функция. Както си спомняте, ние искаме да се обобщят всички продажби, извършени определен продавач, чието име е посочено в F2 (виж фигура, наподобяваща по-горе).

1. обхват (диапазон) - тъй като ние търсим ID Продавач, този аргумент ще бъде стойностите в колона Б на главната таблица (Main таблицата). Може да определят диапазон на В: В (цялата колона) или чрез превръщане на данните в таблицата, се използва името на колона Main_table [ID].

критерии (тест) - продавачи, тъй като имена са написани на таблица за търсене (търсене на маса), използвайте функцията за CDF ID за търсене. съответстваща на даден продавач. Името е написано в клетка F2, за да потърсите помощта на формулата:

Разбира се, можете да въведете името както на желаната стойност директно в функцията CDF. но е по-добре да се използва абсолютно препратка към клетка, защото начинът, по който се създаде универсална формула, която ще работи за всяка стойност влезе в клетката.

Всичко, което трябва да направите е да се постави парчета заедно в едно, а формула SUMIF + ВПР е готов: