Постановка задачи
Имеем таблицу по продажам, например, следующего вида:
Задача : просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ (SUMIF)
Если бы в нашей задаче было только одно условие (все заказы Григорьева или все заказы в "Копейку"), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ из категории Математические. О том, как ее использовать можно почитать . Но в нашем случае имеются два условия, а не одно, поэтому этот способ не подходит...
Третье окно представляет собой графический инструмент редактирования. Он содержит несколько закладок, которые мы обсудим. На этой вкладке мы можем включать и выключать отдельные оси. Сетки представляют собой вспомогательные вертикальные и горизонтальные линии на графике, которые помогают легче читать отображаемые значения графиков.
Заполнив эту вкладку, вы увидите легенду на графике. Легенда объясняет, что означает серия графиков. Позволяет добавлять данные диаграммы непосредственно к отдельным точкам. Название категории: точки графика показывают отдельные категории, соответствующие данному значению. Значение: отображает числовые значения в графике. . Позволяет включить таблицу как часть графика.
Способ 2. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1 иначе 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать полученные суммы:
К сожалению, у нас нет таблицы с четырьмя рядами и пятью столбцами в офисе. Поверьте, что электронные таблицы выглядят как 10 бумаг формата А4, и о них ничего не остается. По правде говоря, эта глава начинается с реальной работы со столами. Мы не только узнаем эту главу в больших таблицах, но это даже сэкономит нам много времени и работы. Таблицы будут работать для нас. Самый простой способ сортировки - сортировка по алфавиту. Просто выберите одну ячейку в столбце, который вы хотите отсортировать, и нажмите следующую кнопку.
Вам не нужно беспокоиться о потере данных или подключении ячеек в таблице. Поэтому он тщательно сортирует все содержимое строк, избегая любой связи между ячейками. Однако могут быть ситуации, когда нам нужно сортировать таблицу по цене или другому параметру, который содержится в таблице.
Способ 3. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего . Ну а в нашем случае задача решается одной формулой:
СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
Вы не можете сортировать таблицу только по столбцам. В этом случае строки остаются на месте, а столбцы сдвинуты. Фильтр является незаменимым помощником для очень больших таблиц. Используя фильтры, мы можем отображать только данные в соответствии с инструкциями, которые мы приводим в таблице. Фильтр отобразит только нужные нам данные, а другие данные будут скрыты для большей ясности.
Например, фильтр - отличный инструмент для отслеживания эффективности бизнеса. Мы предпочитаем описывать фильтрацию на примере. Мы будем отвечать за четырех страховых агентов, которые продают страхование транспортных средств, страхование имущества и жизни.
После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Теперь представьте, что они будут заключать 50 контрактов каждый месяц. Пятьдесят строк в таблице много для ориентации. И теперь мы хотим знать, сколько новых контрактов являются новыми клиентами. Сколько новых клиентов было приобретено отдельными страховщиками.
Мы создадим таблицу для этого экзамена. И на этой таблице давайте посмотрим, как создавать фильтры. Сначала вставьте курсор в заголовок таблицы. В заголовке таблицы отображаются кнопки фильтра, что означает, что фильтр готов к использованию. На этом этапе мы можем начать анализировать эффективность наших страховщиков. Нажмите кнопку «Фильтр клиента» и выберите новый вариант.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных можно найти функцию БДСУММ (DSUM) , которая также может помочь нам решить нашу задачу:
Нюанс состоит в том, что для работы этой функции необходимо создать на листе диапазон ячеек, содержащих условия отбора, и указать затем этот диапазон функции как аргумент:
Теперь мы видим, что таблица меньше. Отобразились только данные о контрактах, заключенных новыми клиентами. Но этого недостаточно для нас, поэтому давайте посмотрим, как индивидуальным страховщикам удалось получить новых клиентов. Теперь, благодаря фильтру, мы можем отображать данные в больших таблицах, которые мы действительно хотим отобразить, и нам не нужно беспокоиться о том, чтобы их найти в большой таблице.
В офисе мы обязательно увидим таблицу, которая будет обширной и содержит несколько уровней иерархии. Без функции отчетности у нас нет возможности попасть в эту таблицу. Итак, давайте посмотрим, как решить эту задачу на конкретном примере. В страховой компании мы обновились, и мы не управляем группой страховщиков, но мы управляем работой ведущих групп, ведущих группы страховщиков. В качестве административного менеджера нас будут интересовать продажи на всех подчиненных уровнях бизнеса. Доходы отдельных страховщиков, а также доходы менеджеров, ведущих группы страховщиков.
БДСУММ(A1:D26;D1;F1:G2)
Способ 5. Мастер Частичной Суммы
Так называется надстройка Excel, которая помогает создавать сложные формулы для многокритериального суммирования. Подключить эту бесплатную надстройку можно через меню Сервис - Надстройки - Мастер суммирования (Tools - Add-Ins - Conditional Sum Wizard) . После этого в меню Сервис должна появится команда Частичная сумма , запускающая Мастер суммирования:
Чтобы помочь в этом примере, мы также можем использовать фильтры, но давайте посмотрим на решение с другой точки зрения. Может случиться так, что мы будем управлять 20 руководителями, которые будут управлять работой 400 страховщиков. В этом случае фильтры станут неясными, потому что мы не просто контролируем продажи. Как мы покажем через некоторое время, обзор заставит таблицу выглядеть лучше с уровнем, который мы вводим.
Мы создадим следующую таблицу экспериментов. При написании электронной таблицы вы должны соблюдать следующие правила.
- В таблице может быть не более 8 иерархий.
- Таблица должна содержать непрерывные итоги каждой части иерархии.
- Суммы должны быть сделаны в одном направлении, сверху вниз.
На первом шаге Мастера суммирования необходимо указать диапазон с данными, т.е. выделить всю нашу таблицу. На втором шаге нужно выбрать столбец для суммирования и сформировать условия для отбора значений, добавляя каждое условие в список кнопкой Добавить условие :
Это таблица с полным обзором. Мы можем сказать кнопками на левом краю книги. Нажатие на эти кнопки отображает или скрывает отдельные иерархии. В этом отчете показаны все иерархии в полном объеме. Вот почему мы показываем заклинание обзора на простом примере. Мы покажем вам обзор только для лидера и нижнего лидера.
Мы дадим ему команду, написав формулу. Чтобы функция функционировала, она должна соответствовать нескольким шаблонам. Вот почему эта глава начинается с темы Законов Формул.
- Формулы вводятся в ячейку, а также число или текст.
- Формула отображается в поле формы, где ее также можно легко восстановить.
- Формула всегда должна начинаться с знака равенства.
- Формула формируется на основе математических моделей.
И, наконец, на 3-м и 4-м шагах указываем ячейку, куда необходимо вывести результат. И получаем в итоге следующее:
Легко заметить, что нечто похожее на эту формулу массива мы использовали в Способе 3. Только здесь можно к клавиатуре вообще не прикасаться - да здравствует лень - двигатель прогресса!
Вот как мы будем отображать формулу в поле формы, нажмите кнопку мыши и отредактируйте ее знакомым способом, потому что появится знакомый курсор. Например, мы хотим добавить в Формулу 10. Работа упрощает нас, подсчитывая большое количество чисел для нас, которые нам больше не нужно вычислять вручную на калькуляторе. Если мы отложим калькулятор и посчитаем все по формулам в таблице, мы сможем приложить огромные усилия в будущем.
Когда мы рассчитываем на калькулятор, мы будем считать все с самого начала. Мы играем на автодилере и создаем следующую таблицу, которую мы будем использовать в следующих примерах. У нас есть готовая таблица, поэтому мы можем перейти к первой задаче. Он приближается к кварталу, и босс хочет, чтобы мы продавали марки автомобилей. Вот сколько автомобилей было продано за квартал.
Знаете ли Вы,
что такое мысленный эксперимент, gedanken experiment?
Это несуществующая практика, потусторонний опыт, воображение того, чего нет на самом деле. Мысленные эксперименты подобны снам наяву. Они рождают чудовищ. В отличие от физического эксперимента, который является опытной проверкой гипотез, "мысленный эксперимент" фокуснически подменяет экспериментальную проверку желаемыми, не проверенными на практике выводами, манипулируя логикообразными построениями, реально нарушающими саму логику путем использования недоказанных посылок в качестве доказанных, то есть путем подмены. Таким образом, основной задачей заявителей "мысленных экспериментов" является обман слушателя или читателя путем замены настоящего физического эксперимента его "куклой" - фиктивными рассуждениями под честное слово без самой физической проверки.
Заполнение физики воображаемыми, "мысленными экспериментами" привело к возникновению абсурдной сюрреалистической, спутанно-запутанной картины мира. Настоящий исследователь должен отличать такие "фантики" от настоящих ценностей.
Копировать формулы внутри таблицы. Нам больше не нужно продавать другие транспортные средства. Нам не нужно указывать формулу для каждого автомобиля отдельно, но мы копируем только формулу для спойлеров на другие транспортные средства. Ячейка сильно обрамлена, а квадрат отображается в нижнем правом углу. . Изменение содержимого ячейки также изменяет результат уравнения.
У нас есть готовые результаты продаж, и мы хотим отправить их боссу. Но продавец вышел с фактом, что продал один лишний багаж. Решение прост. Нам не нужно пересчитывать что-либо и только фиксировать цифру в правильной ячейке. Поэтому просто исправьте входные данные, и результат автоматически пересчитается.
Релятивисты и позитивисты утверждают, что "мысленный эксперимент" весьма полезный интрумент для проверки теорий (также возникающих в нашем уме) на непротиворечивость. В этом они обманывают людей, так как любая проверка может осуществляться только независимым от объекта проверки источником. Сам заявитель гипотезы не может быть проверкой своего же заявления, так как причина самого этого заявления есть отсутствие видимых для заявителя противоречий в заявлении.
При использовании функции формула упрощается. Мы покажем рецепт для управления функциями на одном из них. Работа с другими функциями практически одинакова. Функция имеет следующую форму написания. Мы уже знаем, как сделать сумму, используя формулу, и теперь мы узнаем сумму, используя эту функцию. Используя функцию, мы рассчитываем количество автомобилей, проданных в январе. Итак, сколько автомобилей было продано в январе?
Мы уже знаем, как добавлять строки, столбцы и даже использовать его для добавления функции. Но таблицы всегда хороши в работе, и такой подсчет принесет нам много драгоценного времени. Босс придет к вам и сразу захочет узнать, сколько автомобилей было продано за квартал. Процедура точно такая же, как добавление столбца, просто измените формулу.
Это мы видим на примере СТО и ОТО, превратившихся в своеобразный вид религии, управляющей наукой и общественным мнением. Никакое количество фактов, противоречащих им, не может преодолеть формулу Эйнштейна: "Если факт не соответствует теории - измените факт" (В другом варианте " - Факт не соответствует теории? - Тем хуже для факта").
После центрифугирования общее количество автомобилей, проданных за квартал, отображается в ячейке, на которую мы нажали. Благодаря этой адресации ячеек, при расчете, мы можем действительно подсчитать то, что хотим в таблицах. В этой главе мы обсудим важность пяти других простых функций, которые могут понадобиться постоянному пользователю на работе.
Вычисляет среднее количество выбранных ячеек. Ненумерованное содержимое ячейки опускает эту функцию. Это означает, сколько ячеек находится в области, которую мы выбираем. То есть, ячейки, которые содержат что-то, и неважно, является ли это текстом, числом. Эта функция не учитывает полностью пустые ячейки.
Максимально, на что может претендовать "мысленный эксперимент" - это только на внутреннюю непротиворечивость гипотезы в рамках собственной, часто отнюдь не истинной логики заявителя. Соответсвие практике это не проверяет. Настоящая проверка может состояться только в действительном физическом эксперименте.
Эксперимент на то и эксперимент, что он есть не изощрение мысли, а проверка мысли. Непротиворечивая внутри себя мысль не может сама себя проверить. Это доказано Куртом Гёделем.
Это означает количество ячеек, в которых у нас есть число в выбранной нами области. Другие ячейки, кроме числового, не учитывают эту функцию. Это самая высокая ценность в выборе. Даже при самых простых вычислениях в офисе нам иногда приходится выполнять вычисления, которые состоят из нескольких операций. Мы покажем его на примере, который мы уже знаем. Босс придет к вам и захочет узнать, сколько автомобилей предполагается продать даже во втором квартале.
При построении формулы мы опишем общие основы математики. Сначала мы должны сделать сумму чисел в области таблицы, которую мы хотим добавить. Затем умножим эту сумму на 2 и получим приблизительное представление о количестве автомобилей, проданных за два квартала.
НОВОСТИ ФОРУМА Рыцари теории эфира | 30.10.2017 - 06:17: -> - Карим_Хайдаров. 19.10.2017 - 04:24: -> - Карим_Хайдаров. 11.10.2017 - 05:10: -> - Карим_Хайдаров. 05.10.2017 - 11:03: |
Добрый день!
Продолжу свое стремление описать разнообразие функций в Excel и следующей на рассмотрении у нас . Это еще один из представителей функций суммирования, но со своими специфическими условиями. Функция БДСУММ в Excel ищет и числа в вашей таблице по определенным вами критериям, это ее основное свойство.
На мой взгляд, самое важное, что делать вычисления, - это их ясность. Поэтому легче разделить вычисление промежуточного итога на отдельные расчеты. Это определенно не так уж плохо, что расчет так разделен и подробно описан в примечании, прикрепленном к вспомогательным ячейкам. Это потому, что мы помним год за тем, почему мы использовали такой расчет.
Таблицы и вычисления делают наши собственные имена ячеек очень ясными. Наконец, еще одно предупреждение. Именованные ячейки используют абсолютную адресацию в шаблонах. Это означает, что ссылочная ячейка не изменяется при копировании формулы. Использование Абсолютных адресов описано в разделе Абсолютный адрес формулы.
Положа руку на сердце могу сказать, что много расчётов и вычислений можно сделать и без нее используя разнообразные , или СУММПРОИЗВ, но если же вам нужно сделать сложную выборку используя «подстановочные» знаки, то тогда вам точно нужно использовать героя нашей статьи.
Для начала рассмотрим синтаксис, который использует :
=БДСУММ(диапазон вашей базы данных; поле поиска; условие поиска) , где
При работе с функцией БДСУММ стоить отметить несколько условий, на которые стоит обращать внимание при работе:
- При выполнении работы над целым столбиком заполненным данными, обязательно стоит вставить пустую строку под заголовками столбиков в указанном диапазоне критериев;
- Диапазон критериев не должен иметь пересечения со списком;
- Хотя данные для формирования диапазона условий вы можете помещать в любое место на листе, но тем не менее, помещать его после списка не следует, так как данные которые мы добавляем в список, будут вставляться в первую строку после списка и если в строке есть данные, то добавить новые не получится;
- Можно использовать для условий любой диапазон, который содержит, как минимум один заголовок и хотя бы одну из ячеек, которая расположена под заголовком и содержит в себе условие.
Итак, теоретическую часть, я считаю выполненной, приступим теперь к практическому применению функции БДСУММ в своей работе, для этого рассмотрим несколько примеров для выполнения, я примеры сделал по принципу , но вместо сбора значений по критерию будет суммирование:
Функция БДСУММ с
одним числовым критерием
Итак, для начала рассмотрим простой пример с одним числовым критерием, для этого выберем столбик «Урожай» и укажем что нам надо деревья с урожайность «>=10». Что бы получить результат нам нужна формула такого вида (советую использовать абсолютные ссылки):
=БДСУММ($B$6:$G$12;E6;E2:E3) ,
где, $B$6:$G$12 диапазон в котором мы будем суммировать, E6 – столбик в котором мы будем суммировать и E2:E3 диапазон в которые мы ввели критерии для суммирования. В итоге формула нашла 3 позиции на общую сумму 34.
Для получения аналогично результата вы можете воспользоваться также такими формулами:
=СУММЕСЛИ(E7:E12;">=10")
=СУММЕСЛИ(E7:E12;E3)
Функция БДСУММ с одним текстовым критерием
Теперь рассмотрим, как ведет себя функция БДСУММ с текстовыми критериями, в общем всё остается так и в предыдущем примере за исключением того как указывается текстовый критерий, а он указывается только в таком виде: ="=с.Серово" и тогда результат у вас получится, иначе формула не сможет распознать ваш критерий. Теперь подставляем этот критерий в формулу и получаем:
=БДСУММ($B$6:$G$12;E6;C2:C3) , как видим произошло только изменения диапазона критерия.
Для получения схожего результата вам пригодится :
=СУММЕСЛИ(C7:C12;"с.Серово";E7:E12)
Суммирование по двум критериям по разным столбцам
Пример усложняется применением двух критериев, но ничего принципиально нового мы применять не будем, укажем текстовый критерий «с.Серово» и числовой критерий «>=10», оставив поле суммирование «Урожай», мы получим изменение формулы только по последнему аргументу, как результат. Теперь наша формула будет выглядеть так:
=БДСУММ($B$6:$G$12;E6;C2:E3) , снова видите изменения только адреса диапазона критерия.
Альтернативный вариант можно получить с помощью и так:
=СУММЕСЛИМН(E7:E12;C7:C12;C3;E7:E12;E3)
=СУММЕСЛИМН(E7:E12;C7:C12;"с.Серово";E7:E12;">=10")
Суммирование по одному из двух условий в одном столбике
Рассмотрим еще один вариант как применяется , но сейчас будем использовать не единичный критерий, а двойной, но для одного поля. Обязательно два критерия должны быть в разных строках. Суть формулы сводится к тому что, она проходит один и тот же диапазон два раза считая каждый из критериев отдельно. Для этого примера формула будет выглядеть так:
=БДСУММ($B$6:$G$12;E6;C2:C4) , здесь снова изменяем диапазон критерия, но не в ширину, а в высоту.
Также заменителем, вы можете использовать сумму функции СУММЕСЛИ:
=СУММЕСЛИ(C7:C12;C3;E7:E12)+СУММЕСЛИ(C7:C12;C4;E7:E12)
. 
Суммирование по одному из двух условий в двух разных столбиках
Пятым примером применения станет суммирование по одному из двух условий в двух разных столбиках и обязательно аргументы нужно размещать все в новых строках, это позволит их суммировать по очереди. Для этих целей нам понадобится формула:
=БДСУММ($B$6:$G$12;D6;C2:D4)
, принцип формирования формулы сохраняется, окромя диапазона критерия, который включает в себя три строки: заголовок и два критерия. 
Суммирование по двум текстовым критериям по двум столбикам
В данном примере работы рассмотрим практически полный аналог ранее рассматриваемого примера, когда были два критерия в двух столбика, но там были числовой и текстовый критерий, а здесь рассмотрим суммирование по двум текстовым критериям и по двум столбикам. Используем критерии «="=с.Иваново"» и «="=Вишня"», которые мы и укажем в диапазоне критериев. Значит наша формула будет иметь следующий вид:
=БДСУММ($B$6:$G$12;D6;B2:C3).
Использование результата формулы для получения критерия отбора и суммирования
В данном примере функции БДСУММ я использую формулу для определения критерия отбора и суммирования, в данном случае найдем какие же деревья нам приносят доход и для определения условного аргумента возьмем среднее значение по продажам плодов с деревьев и всё что выше среднего показателя нас интересует. Для определения среднего значение которое станет нашим критерием «ИСТИНА» мы создаем формулу :
=G7>СРЗНАЧ($G$7:$G$12)
, не забываем об для закрепления диапазона, что бы при переборе формулой значений они не сползли вниз, а вот значение G7 должно скользить по всему диапазону для определения «ЛОЖЬ» это или «ИСТИНА». 
Очень важно еще то чтобы названия заголовков не дублировались, имели отличие, поэтому поле критериев я назову «Среднее». И тогда формула начнёт работать, она переберет весь диапазон $G$7:$G$12 на наличие среднего значения и при получении положительного результата «ИСТИНА» она будет суммировать. С этой работой справится формула следующего вида:
=БДСУММ($B$6:$G$12;G6;$G$2:$G$3)
А если вам очень интересно альтернативное решение вопроса, то тогда испробуйте вариант с функцией СУММЕСЛИ в таком виде:
=СУММЕСЛИ($G$7:$G$12;">"&СРЗНАЧ($G$7:$G$12))
Функция БДСУММ по трём критериям
В этом примере посчитаем среднее по продажам с выращенных плодов в двоих сёлах: «с.Иваново» и «с.Уютное». Основную идею отбора по критериям я уже описывал, поэтому повторятся не буду, просто скажу, что это будет соединение ранее рассмотренных критериев. Для получения результата нам нужна в таком виде:
=БДСУММ($B$6:$G$12;G6;$C$2:$G$4)
Суммирование по текстовому критерию с учётом регистра
Как я упоминал ранее, может производить поиск не только с подстановочными символами, но и с учётом регистра букв, вот сейчас именно такой вариант. Для начала определим условие по отбору критерия, если встречается название «с.ИВАНОВО», заглавными, то мы производим суммирование, для определения этого критерия нам нужна формула:
=СОВПАД(«с.ИВАНОВО»;C7)
А вот теперь мы можем прописать функцию , которая проверит диапазон на наличие указанного критерия и при получении значения «ИСТИНА» произведет суммирование. В примере я указал специально один раз по условию, и как видим, формула успешно отобрала все населенные пункты и нашла нужный и получили результат «9». Для этого была использована формула:
=БДСУММ($B$6:$G$12;D6;$C$2:$C$3)
Ну что же, я думаю, что , мною была описана деталь и во многих подробностях, поэтому вопросов будет мало, а пользы много. Если у вас возникли вопросы пишите комментарии, жду ваши лайки и отзывы. О других функциях вы можете ознакомиться в на моем сайте.
Успехов вам в ваших делах!
"За деньги можно, конечно, купить очаровательного пса, но никакие деньги не смогут заставить его радостно вилять хвостом.
"
Д. Биллингс