Задача № 1. Предприятие реализует три номенклатурные группы продукции: лимонад, минеральная вода и пиво. С 01.09.2020 запланировано установить скидку в размере 15 % на пиво.
Чтобы сформировать новый прайс на продукцию, сохраняем ее перечень в виде таблицы Excel. В первом столбце таблицы отражена номенклатура всей продукции в алфавитном порядке, во втором — признак группы продукции.
Для решения задачи создаем в таблице третий столбец и прописываем в первой ячейке номенклатуры формулу: =ЕСЛИ(C4="пиво";15%;0).
Эту формулу продлеваем до конца перечня номенклатуры продукции. В итоге получаем сведения о продукции, на которую с сентября снизится цена (табл. 1).
В данном примере показано использование формулы ЕСЛИ для обработки текстовых значений в исходных данных.
Решение экономической задачи с помощью формулы СУММЕСЛИ
Формулы СУММЕСЛИ и СУММЕСЛИМН также используют для экономических расчетов, но они обладают более широкими возможностями для выборки и обработки данных. Можно задать не одно, а несколько условий отборов и диапазонов.
Задача № 2. На основе ведомости начисления заработной платы сотрудникам магазина нужно определить общую сумму зарплаты продавцов.
Чтобы решить эту задачу, сохраняем ведомость из учетной базы данных в виде таблицы Excel. В данном случае нам нужно не просто произвести выборку значений, но и суммировать их результат. Поэтому будем использовать более сложную разновидность формулы ЕСЛИ — СУММЕСЛИ.
Результат решения задачи — в табл. 2.
Решение экономической задачи с помощью формул ВПР и ГПР
Формулы ВПР и ГПР используют для решения более сложных экономических задач. Они популярны среди экономистов, так как существенно облегчают поиск необходимых значений в больших массивах данных. Разница между формулами:
ВПР предназначена для поиска значений в вертикальных списках (по строкам) исходных данных;
ГПР используют для поиска значений в горизонтальных списках (по столбцам) исходных данных.
Формулы прописывают в общем виде следующим образом:
=ВПР(искомое значение, которое требуется найти; таблица и диапазон ячеек для выборки данных; номер столбца, из которого будут подставлены данные; [интервал просмотра данных]);
=ГПР(искомое значение, которое требуется найти; таблица и диапазон ячеек для выборки данных; номер строки, из которой будут подставлены данные; [интервал просмотра данных]).
Указанные формулы имеют ценность при решении задач, связанных с консолидацией данных, которые разбросаны на разных листах одной книги Excel, находятся в различных рабочих книгах Excel, и размещении их в одном месте для создания экономических отчетов и подсчета итогов.
Задача № 3. У экономиста есть данные в виде таблицы Excel о реализации продукции за сентябрь в натуральном измерении (декалитрах) и данные о реализации продукции в сумме (рублях) в другой таблице Excel. Экономисту нужно предоставить руководству отчет о реализации продукции с тремя параметрами:
продажи в натуральном измерении;
продажи в суммовом измерении;
средняя цена реализации единицы продукции в рублях.
Для решения этой задачи с помощью формулы ВПР нужно последовательно выполнить следующие действия.
Шаг 1. Добавляем к таблице с данными о продажах в натуральном измерении два новых столбца. Первый — для показателя продаж в рублях, второй — для показателя цены реализации единицы продукции.
Шаг 2. В первой ячейке столбца с данными о продажах в рублях прописываем расчетную формулу: =ВПР(B4:B13;Табл.4!B4:D13;3;ЛОЖЬ).
Пояснения к формуле:
В4:В13 — диапазон поиска значений по номенклатуре продукции в создаваемом отчете;
Табл.4!B4:D13 — диапазон ячеек, где будет производиться поиск, с наименованием таблицы, в которой будет организован поиск;
3 — номер столбца, по которому нужно выбрать данные;
ЛОЖЬ — значение критерия поиска, которое означает необходимость строгого соответствия отбора наименований номенклатуры таблицы с суммовыми данными наименованиям номенклатуры в таблице с натуральными показателями.
Шаг 3. Продлеваем формулу первой ячейки до конца списка номенклатуры в создаваемом нами отчете.
Шаг 4. В первой ячейке столбца с данными о цене реализации единицы продукции прописываем простую формулу деления значения ячейки столбца с суммой продаж на значение ячейки столбца с объемом продаж (=E4/D4).
Шаг 5. Продлим формулу с расчетом цены реализации до конца списка номенклатуры в создаваемом нами отчете.
В результате выполненных действий появился искомый отчет о продажах (табл. 3).
На небольшом количестве условных данных эффективность формулы ВПР выглядит не столь внушительно. Однако представьте, что такой отчет нужно сделать не из заранее сгруппированных данных по номенклатуре продукции, а на основе реестра ежедневных продаж с общим количеством записей в несколько тысяч.
Тогда эта формула обеспечит такую скорость и точность выборки нужных данных, которой трудно добиться другими функциями Excel.
Решение экономической задачи с помощью формулы СУММПРОИЗВ
Задача № 4. Есть реестр продаж различной номенклатуры продукции за сентябрь 2020 г. Нужно рассчитать из общего реестра данные о суммах реализации по основным номенклатурным группам продукции.
Выполнив указанные действия, получим искомое решение задачи в табл. 4.
Решение экономической задачи с помощью формулы СЧЕТЕСЛИ
Формула СЧЕТЕСЛИ используется не так широко, как предыдущие, но она выручает экономистов, если нужно минимизировать ошибки при работе с таблицами Excel. Эта формула удобна для проверки корректности вводимых данных и установке различного рода запретов, что особенно важно, если с данными работает несколько пользователей.
Задача № 5. Экономисту поручили провести корректировку справочника номенклатуры ТМЦ в учетной базе данных компании. Справочник долгое время не проверяли, данные в него вносили порядка 10 человек, поэтому появилось много некорректных и дублирующих наименований.
Экономист может решить эту проблему с помощью формулы СЧЕТЕСЛИ. Нужно выполнить следующие действия:
Такими экономическими задачами могут быть:
расчет оптимального объема выпуска продукции при ограниченности сырья;
минимизация транспортных расходов на доставку продукции покупателям;
решение по оптимизации фонда оплаты труда.