Финансы в Excel
При обработке больших таблиц иногда возникает потребность получить итоговые значения на основе данных, расположенных в диапазонах ячеек, не позволяющих применить функции консолидации с прямой адресацией, либо требующих дополнительных вычислений. В статье описывается несколько вариантов построения моделей на примере экономической задачи расчета валовой выручки.
Для простоты понимания все примеры формул будем рассматривать на основе простейшей экономической задачи: вычисление итоговой выручки при известных значениях объема и цен продажи по нескольким покупателям.
На практике решение подобных задач зачастую сводится к написанию «некопируемых» формул, либо требует преобразование итоговых формул после добавления данных (см. лист Пример1, ячейки C10,C11). Этот метод допустим только на небольших объемах данных и нарушает основные принципы оптимизации работы в электронных таблицах.
Второй стандартный метод решения проблемы – это создание дополнительных таблиц с промежуточными вычислениями.
Такой способ позволяет применить одинаковые (копируемые) формулы в итоговых ячейках, а также не требует изменения итоговых формул при добавлении строк или столбцов с данными. Эти два условия обеспечивают максимальную достоверность вычислений при развитии и эксплуатации экономической модели.
К недостаткам способа решения задачи через добавление промежуточных вычислений следует отнести:
- Дополнительное преобразования исходных данных, что зачастую сложно выполнимо на практике (представьте, что в исходном файле покупателей не 3, а 3000).
- Сложность добавления информации – нового покупателя требуется добавлять не в одну, а в три таблицы.
Попробуем все-таки решить задачу без использования промежуточных таблиц, но сохранив копируемость и корректность итоговых формул при добавлении покупателей и периодов.
Как уже отмечалось выше, формулы в ячейках C10:C11 решают задачу верно, но усложняют работу с моделью. Хочется только обратить внимание на применение функции СУММ с дополнительными вычислениями в ячейке C11.
Остальные формулы в диапазоне D10:E11 также верны и при этом полностью соответствуют требованиям задачи. Рассмотрим их поподробнее.
Формула очень простая и очевидная для понимания. Суммирование происходит по условию, закрепленному в ячейке $B$3. Вместо $B$3 можно было написать «объем, кг» непосредственно в функции. Это с одной стороны делает формулу более читаемой, с другой стороны, менее безопасно, так как при замене значения в ячейках заголовков, например с «объем, кг» на «объем, л» функция будет работать неверно.
Также необходимо заметить, что возможность применения СУММЕСЛИ часто бывает ограничена из-за допустимости проверки только по одному условию (только в Excel 2007, появилась функция СУММЕСЛИМН). В сложных случаях без формул с обработкой массивов не обойтись.
Формулы обработки массивов применяются через нажатие Ctrl+Shift+Enter
Одно из нестандартных применений функции СУММПРОИЗВ. Идея – перемножить три массива чисел. Первый массив – объемы, второй – цены, а третий является триггером, умножающим ненужные данные на ноль.
Преобразуем формулу через выделение текста в скобках и нажатие 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.
Почему эксель неправильно считает сумму столбца
Чем мне особенно нравится excel, так это тем, что можно менять параметры формулы на лету. Например, если нам надо одно из участвующих числе уменьшить на две единицы, то соответственно уменьшится на два и вся сумма. Такую штуку частенько использую в повседневной жизни.
| 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 |
Сумма, если меньше чем
Подводя итоги если меньше чем, вы можете использовать функцию СУММЕСЛИ.
В показанном примере ячейка Н4 содержит следующую формулу:
Эта формула суммирует суммы в колонке D, когда они меньше, чем 1000.
Функция СУММЕСЛИ поддерживает логические операторы в Excel (то есть «=», «>», «> =» и т.д.), так что вы можете использовать их, как вам нравится в ваших критериях.
В этом случае, мы хотим, чтобы соответствовующие суммы были меньше, чем 1000, и «критерии варьировались» так же, как «диапазон суммы», поэтому нет необходимости вводить диапазон суммы в качестве последнего аргумента.
Функция СУММЕСЛИ просто суммирует все суммы менее 1000.
Если вы хотите включить пороговое число в сумму, используйте меньше или равно (<=), например, так:
Если вы хотите выставить пороговое значение на рабочем листе, так чтобы его можно было легко изменить, используйте следующую формулу:
Там, где А1 представляет собой ссылку на ячейку, которая содержит пороговое число.
Вы можете также использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, но порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН является:
Обратите внимание на то, что диапазон суммы всегда стоит первым в функции СУММЕСЛИМН. Также обратите внимание, что критерии СУММЕСЛИМН должны быть введены в парах (диапазон/критерий), что означает имя диапазона «количество» должен быть введен дважды: один раз в пределах суммы, и один раз как ряд критериев.
Как сделать функцию суммы в excel?
Если одного условия Вам не достаточно, то Вы всегда можете воспользоваться функцией СУММЕСЛИМН, которая позволяет осуществлять условное суммирование в Excel на основе нескольких критериев.
Как посчитать сумму определенных ячеек в Excel
Если формулу нужно отредактировать, например, вам нужно изменить адреса ячеек, то для этого нужно выделить ячейку с суммой и изменить формулу в строке для формул.
Как посчитать сумму в Экселе в столбце и в определенных ячейках
Чтобы значение в ячейке G7 равнялось сумме отображаемых значений в ячейках G4:G6, можно применить формулу массива, проводящую округление значений до двух десятичных знаков перед суммированием: (рис. 3). [1]
