Финансы в 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.
Например, если нам дано 7% от 70, то подставив эти значения в формулу, получим числовое значение. Таким образом, ячейке, в которой появится итоговый результат не нужно присваивать формат «Процентный». В этой строке должно быть написано «Общий» или «Числовой».
Как в Эксель посчитать проценты: 3 практичных способа
- Дополнительное преобразования исходных данных, что зачастую сложно выполнимо на практике (представьте, что в исходном файле покупателей не 3, а 3000).
- Сложность добавления информации – нового покупателя требуется добавлять не в одну, а в три таблицы.
где n – это число, квадратную степень которого требуется вычислить. Значение этого аргумента можно указать разными способами: в виде конкретного числа, либо указав адрес ячейки, которая содержит требуемое числовое значение.
| 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 |
