Финансы в 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: примеры и пояснения — MS Office Excel — Работа на компьютере: инструкции и советы — Образование, воспитание и обучение — Сообщество взаимопомощи учителей
#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 |
Простое сложение в Excel
Складывать значения ячеек можно с помощью простой функции «СУММ», которая суммирует выбранные значения без определенного условия. Она используется, когда есть ячейки, массив или несколько массивов для сложения. В этом случае достаточно выделить нужные элементы электронной таблицы и применить функцию. Она играет роль обычного калькулятора со знаком «+».
Как сравнить два столбца в Excel на совпадения — ЭКСЕЛЬ ХАК
- Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
- Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как «>5» . Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие «Иванов» ;
- Диапазон суммирования – массив значений, которые нужно сложить.
#1. Умная таблица. Выделяем диапазон с источником, переходим во вкладку на панели инструментов «Главная», раздел «Стили», раскрываем меню «Форматировать как таблицу» и выбираем понравившийся стиль умной таблицы Excel.
Небольшой совет
Подробная информация о фильтрах приведена ниже в пунктах Операторы, Заголовки и Множественные условия.
В дополнение к вышеупомянутым подстановочным знакам приведём некоторые операторы, необходимые при работе с фильтром:
- = равенство, т.е. левая и правая часть должна совпасть: =билет [включит все ячейки, содержащие билет]
- не равно, левая и правая часть не должны совпасть: https://www.aviasales.ru/mag* [исключаем данный адрес (блог)]
- ‘ апостроф преобразует формулу в текст, если поставить его первым в ячейке: ‘=купить авиабилет
- > больше чем: >500
- >= больше или равно: B4-C4>=3
- < меньше чем: C6
Суммирование несвязанных диапазонов
- Формула должна возвращать логическое значение ЛОЖЬ или ИСТИНА;
- Заголовки над формулами должны отличаться от заголовков базы данных, можно вообще использовать пустую строку;
- Формула должна ссылаться на ячейки первой строки под заголовками базы данных;
- Ссылки на проверяемые ячейки базы должны быть относительными: вида С4, при абсолютных, таких как $C$4 — значение формул будет статичным, т.е. всегда ЛОЖЬ или всегда ИСТИНА.
Помните, что функция ВПР всегда ищет искомое значение в первом столбце таблицы. Ваш таблица может содержать различные значения, такие как текст, дата, числа или логические значения. Значения нечувствительны к регистру , что означает, что прописные и строчные буквы считаются идентичными.
