Excel формула несвязный диапазон
Обновлено: 03.07.2024
Предположим, что вам нужно свести значения с более чем одним условием, например суммой продаж продуктов в определенном регионе. Это хороший случай для использования функции СУММЕСС в формуле.
Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж "Мясо" (из столбца C) в регионе "Южный" (из столбца A).
Вот формула, с помощью которая можно сопровождать эту формулу:
=СУММЕСС(D2:D11,A2:A11,"Южный",C2:C11,"Мясо")
Результат — значение 14 719.
Рассмотрим каждую часть формулы более подробно.
=СУММЕСЛИМН — это арифметическая формула. Она вычисляет числа, которые в этом случае находятся в столбце D. Прежде всего нужно указать расположение чисел.
=СУММЕСЛИМН(D2:D11,
Другими словами, вы хотите, чтобы формула суммировала числа в этом столбце, если они соответствуют определенным условиям. Это диапазон ячеок является первым аргументом в этой формуле — первым элементом данных, который требуется функции в качестве входных данных.
Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие ("Южный"). Обратите внимание на запятую между аргументами:
=СУММЕСЛИМН(D2:D11;A2:A11;"Южный";
Кавычка вокруг текста "Южный" указывает на то, что это текстовые данные.
Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово "Мясо", а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.
=СУММЕСЛИМН(D2:D11;A2:A11,"Южный";C2:C11,"Мясо")
Если вы ввели в Excel функцию СУММЕСС, если вы не помните аргументов, справка готова. После того как вы введете =СУММЕСС(, под формулой появится автозавершенная формула со списком аргументов в правильном порядке.
На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится "Южный" (критерий1).
По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.
Если вы нажмете кнопку СУММЕСС в автозавершении формул, откроется статья с дополнительной справкой.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функцией СУММЕСС, вот примеры данных и формула, в которую она используется.
Вы можете работать с образцами данных и формулами прямо в этой Excel в Интернете книге. Изменяйте значения и формулы или добавляйте свои собственные, чтобы увидеть, как мгновенно изменятся результаты.
Скопируйте все ячейки из приведенной ниже таблицы и вставьте их в ячейку A1 нового листа Excel. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
- Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
При обработке больших таблиц иногда возникает потребность получить итоговые значения на основе данных, расположенных в диапазонах ячеек, не позволяющих применить функции консолидации с прямой адресацией, либо требующих дополнительных вычислений. В статье описывается несколько вариантов построения моделей на примере экономической задачи расчета валовой выручки.
Для простоты понимания все примеры формул будем рассматривать на основе простейшей экономической задачи: вычисление итоговой выручки при известных значениях объема и цен продажи по нескольким покупателям.
В задаче подразумевается, что количество покупателей и периодов не постоянно. Требуется получить итоговые значения по объемам продаж и выручке. Очевидно, что применить простую функцию суммы (СУММ) не получится даже для получения итогов по объемам. Аналогично и для вычисления выручки не годится применение функции СУММПРОИЗВ «в лоб», так как диапазоны ячеек-множителей не связаны между собой.
На практике решение подобных задач зачастую сводится к написанию «некопируемых» формул, либо требует преобразование итоговых формул после добавления данных (см. лист Пример1, ячейки C10,C11). Этот метод допустим только на небольших объемах данных и нарушает основные принципы оптимизации работы в электронных таблицах.
Второй стандартный метод решения проблемы – это создание дополнительных таблиц с промежуточными вычислениями.
Такой способ позволяет применить одинаковые (копируемые) формулы в итоговых ячейках, а также не требует изменения итоговых формул при добавлении строк или столбцов с данными. Эти два условия обеспечивают максимальную достоверность вычислений при развитии и эксплуатации экономической модели.
Обратите внимание, задача корректности формул итогов при добавлении строк решена за счет добавления пустой служебной строки перед строкой итогов (лист Пример2, строки 6 и 22). Без пустой строки при добавлении нового покупателя вниз массива функция СУММ не подхватывает новые данные автоматически. Служебные строки – это стандартный «безопасный» прием при работе с переменным количеством строк с данными. При желании их можно скрыть стандартными методами интерфейса Excel.
К недостаткам способа решения задачи через добавление промежуточных вычислений следует отнести:
- Дополнительное преобразования исходных данных, что зачастую сложно выполнимо на практике (представьте, что в исходном файле покупателей не 3, а 3000).
- Сложность добавления информации – нового покупателя требуется добавлять не в одну, а в три таблицы.
Попробуем все-таки решить задачу без использования промежуточных таблиц, но сохранив копируемость и корректность итоговых формул при добавлении покупателей и периодов.
Как уже отмечалось выше, формулы в ячейках C10:C11 решают задачу верно, но усложняют работу с моделью. Хочется только обратить внимание на применение функции СУММ с дополнительными вычислениями в ячейке C11.
Остальные формулы в диапазоне D10:E11 также верны и при этом полностью соответствуют требованиям задачи. Рассмотрим их поподробнее.
Итого объем, кг: D10
Формула очень простая и очевидная для понимания. Суммирование происходит по условию, закрепленному в ячейке $B$3. Вместо $B$3 можно было написать «объем, кг» непосредственно в функции. Это с одной стороны делает формулу более читаемой, с другой стороны, менее безопасно, так как при замене значения в ячейках заголовков, например с «объем, кг» на «объем, л» функция будет работать неверно.
Также необходимо заметить, что возможность применения СУММЕСЛИ часто бывает ограничена из-за допустимости проверки только по одному условию (только в Excel 2007, появилась функция СУММЕСЛИМН). В сложных случаях без формул с обработкой массивов не обойтись.
Итого объем, кг: E10
Идея формулы – посчитать сумму только в нечетных строках данных. Формула работает с массивом ячеек E3:E9 (признак – фигурные скобки). Нечетные строки здесь определяются как остаток равный 1 при делении порядкового номера строки диапазона на 2. Дополнительно для поддержания целостности номера отсчитываются от заголовка таблицы СТРОКА(E2). Это позволяет переносить данную таблицу целиком на другое место на текущем или другом листе модели.
Формулы обработки массивов применяются через нажатие Ctrl+Shift+Enter
Итого выручка, руб: D11
Одно из нестандартных применений функции СУММПРОИЗВ. Идея – перемножить три массива чисел. Первый массив – объемы, второй – цены, а третий является триггером, умножающим ненужные данные на ноль.
Преобразуем формулу через выделение текста в скобках и нажатие F9, получим:
Представим в более понятном виде:
| 400 | 0,06 | ИСТИНА | =24 |
| 0,06 | 500 | ЛОЖЬ | =0 |
| 500 | 0,15 | ИСТИНА | 75 |
| 0,15 | 600 | ЛОЖЬ | =0 |
| 600 | 0,14 | ИСТИНА | 84 |
| 0,14 | 0 | ЛОЖЬ | =0 |
| 0 | 1500 | ЛОЖЬ | =0 |
При умножении массивов логическое значение ЛОЖЬ преобразуется в 0, а ИСТИНА в 1.
Итого выручка, руб: E11
Формула очень похожа на формулу вычисления итоговых объемов, находящуюся в ячейке E10 (см.выше), только происходит дополнительное умножение на диапазон со сдвигом вниз (E4:E10) – назовем его условно «диапазон цен», хотя это и не совсем верно по смыслу. При обработке массива, в тот момент когда условная функция определяет что строка содержит объемы, происходит дополнительное умножение на ячейку с тем же порядковым номером в своем массиве, но из диапазона цен. Следует заметить, что при применении в формуле нескольких массивов, они должны быть одинаковой размерности. Из-за этого диапазоны обработки в формулах «заезжают» на строку 10, что вообще-то не очень корректно. В нашем примере это не приводит к ошибкам, но в практической работе рекомендуется избегать таких ситуаций. Простейшим решением здесь может быть добавление еще одной пустой строки перед итогами.
Приведенные примеры формул показывают многовариантность решения одной и той же задачи. Наверняка найдется еще несколько способов, не менее эффективных, и возможно даже более простых. Универсальной рекомендации на все случаи жизни, какой способ выбрать при решении подобных задач, дать, пожалуй, не получится. Главное, стремиться не только изучать новые возможности, но и применять их на практике, следуя основным принципам оптимизации работы в электронных таблицах.
Суммирование несвязанных диапазонов
| sum_areas.xls | [Суммирование несвязанных диапазонов] | 37 kB |
При обработке больших таблиц иногда возникает потребность получить итоговые значения на основе данных, расположенных в диапазонах ячеек, не позволяющих применить функции консолидации с прямой адресацией, либо требующих дополнительных вычислений. В статье описывается несколько вариантов построения моделей на примере экономической задачи расчета валовой выручки.
Для простоты понимания все примеры формул будем рассматривать на основе простейшей экономической задачи: вычисление итоговой выручки при известных значениях объема и цен продажи по нескольким покупателям.
В задаче подразумевается, что количество покупателей и периодов не постоянно. Требуется получить итоговые значения по объемам продаж и выручке. Очевидно, что применить простую функцию суммы (СУММ) не получится даже для получения итогов по объемам. Аналогично и для вычисления выручки не годится применение функции СУММПРОИЗВ «в лоб», так как диапазоны ячеек-множителей не связаны между собой.
На практике решение подобных задач зачастую сводится к написанию «некопируемых» формул, либо требует преобразование итоговых формул после добавления данных (см. лист Пример1, ячейки C10,C11). Этот метод допустим только на небольших объемах данных и нарушает основные принципы оптимизации работы в электронных таблицах.
Второй стандартный метод решения проблемы – это создание дополнительных таблиц с промежуточными вычислениями.
Такой способ позволяет применить одинаковые (копируемые) формулы в итоговых ячейках, а также не требует изменения итоговых формул при добавлении строк или столбцов с данными. Эти два условия обеспечивают максимальную достоверность вычислений при развитии и эксплуатации экономической модели.
Обратите внимание, задача корректности формул итогов при добавлении строк решена за счет добавления пустой служебной строки перед строкой итогов (лист Пример2, строки 6 и 22). Без пустой строки при добавлении нового покупателя вниз массива функция СУММ не подхватывает новые данные автоматически. Служебные строки – это стандартный «безопасный» прием при работе с переменным количеством строк с данными. При желании их можно скрыть стандартными методами интерфейса Excel.
К недостаткам способа решения задачи через добавление промежуточных вычислений следует отнести:
- Дополнительное преобразования исходных данных, что зачастую сложно выполнимо на практике (представьте, что в исходном файле покупателей не 3, а 3000).
- Сложность добавления информации – нового покупателя требуется добавлять не в одну, а в три таблицы.
Попробуем все-таки решить задачу без использования промежуточных таблиц, но сохранив копируемость и корректность итоговых формул при добавлении покупателей и периодов.
Как уже отмечалось выше, формулы в ячейках C10:C11 решают задачу верно, но усложняют работу с моделью. Хочется только обратить внимание на применение функции СУММ с дополнительными вычислениями в ячейке C11.
Остальные формулы в диапазоне D10:E11 также верны и при этом полностью соответствуют требованиям задачи. Рассмотрим их поподробнее.
Итого объем, кг: D10
Формула очень простая и очевидная для понимания. Суммирование происходит по условию, закрепленному в ячейке $B$3. Вместо $B$3 можно было написать «объем, кг» непосредственно в функции. Это с одной стороны делает формулу более читаемой, с другой стороны, менее безопасно, так как при замене значения в ячейках заголовков, например с «объем, кг» на «объем, л» функция будет работать неверно.
Также необходимо заметить, что возможность применения СУММЕСЛИ часто бывает ограничена из-за допустимости проверки только по одному условию (только в Excel 2007, появилась функция СУММЕСЛИМН). В сложных случаях без формул с обработкой массивов не обойтись.
Итого объем, кг: E10
Идея формулы – посчитать сумму только в нечетных строках данных. Формула работает с массивом ячеек E3:E9 (признак – фигурные скобки). Нечетные строки здесь определяются как остаток равный 1 при делении порядкового номера строки диапазона на 2. Дополнительно для поддержания целостности номера отсчитываются от заголовка таблицы СТРОКА(E2). Это позволяет переносить данную таблицу целиком на другое место на текущем или другом листе модели.
Формулы обработки массивов применяются через нажатие Ctrl+Shift+Enter
Итого выручка, руб: D11
Одно из нестандартных применений функции СУММПРОИЗВ. Идея – перемножить три массива чисел. Первый массив – объемы, второй – цены, а третий является триггером, умножающим ненужные данные на ноль.
Преобразуем формулу через выделение текста в скобках и нажатие F9, получим:
Представим в более понятном виде:
| 400 | 0,06 | ИСТИНА | =24 |
| 0,06 | 500 | ЛОЖЬ | =0 |
| 500 | 0,15 | ИСТИНА | 75 |
| 0,15 | 600 | ЛОЖЬ | =0 |
| 600 | 0,14 | ИСТИНА | 84 |
| 0,14 | 0 | ЛОЖЬ | =0 |
| 0 | 1500 | ЛОЖЬ | =0 |
При умножении массивов логическое значение ЛОЖЬ преобразуется в 0, а ИСТИНА в 1.
Итого выручка, руб: E11
Формула очень похожа на формулу вычисления итоговых объемов, находящуюся в ячейке E10 (см.выше), только происходит дополнительное умножение на диапазон со сдвигом вниз (E4:E10) – назовем его условно «диапазон цен», хотя это и не совсем верно по смыслу. При обработке массива, в тот момент когда условная функция определяет что строка содержит объемы, происходит дополнительное умножение на ячейку с тем же порядковым номером в своем массиве, но из диапазона цен. Следует заметить, что при применении в формуле нескольких массивов, они должны быть одинаковой размерности. Из-за этого диапазоны обработки в формулах «заезжают» на строку 10, что вообще-то не очень корректно. В нашем примере это не приводит к ошибкам, но в практической работе рекомендуется избегать таких ситуаций. Простейшим решением здесь может быть добавление еще одной пустой строки перед итогами.
Приведенные примеры формул показывают многовариантность решения одной и той же задачи. Наверняка найдется еще несколько способов, не менее эффективных, и возможно даже более простых. Универсальной рекомендации на все случаи жизни, какой способ выбрать при решении подобных задач, дать, пожалуй, не получится. Главное, стремиться не только изучать новые возможности, но и применять их на практике, следуя основным принципам оптимизации работы в электронных таблицах.
Читайте также: