Excel имеет значительную популярность среди бухгалтеров, экономистов и финансистов не в последнюю очередь благодаря обширному инструментарию по выполнению различных финансовых расчетов. Главным образом выполнение задач данной направленности возложено на группу финансовых функций. Многие из них могут пригодиться не только специалистам, но и работникам смежных отраслей, а также обычным пользователям в их бытовых нуждах. Рассмотрим подробнее данные возможности приложения, а также обратим особое внимание на самые популярные операторы данной группы.
В группу данных операторов входит более 50 формул. Мы отдельно остановимся на десяти самых востребованных из них. Но прежде давайте рассмотрим, как открыть перечень финансового инструментария для перехода к выполнению решения конкретной задачи.
Переход к данному набору инструментов легче всего совершить через Мастер функций.
В Мастер функций также можно перейти через вкладку «Формулы» . Сделав переход в неё, нужно нажать на кнопку на ленте «Вставить функцию» , размещенную в блоке инструментов «Библиотека функций» . Сразу вслед за этим запустится Мастер функций.
Имеется в наличии также способ перехода к нужному финансовому оператору без запуска начального окна Мастера. Для этих целей в той же вкладке «Формулы» в группе настроек «Библиотека функций» на ленте кликаем по кнопке «Финансовые» . После этого откроется выпадающий список всех доступных инструментов данного блока. Выбираем нужный элемент и кликаем по нему. Сразу после этого откроется окно его аргументов.
ДОХОД
Одним из наиболее востребованных операторов у финансистов является функция ДОХОД . Она позволяет рассчитать доходность ценных бумаг по дате соглашения, дате вступления в силу (погашения), цене за 100 рублей выкупной стоимости, годовой процентной ставке, сумме погашения за 100 рублей выкупной стоимости и количеству выплат (частота). Именно эти параметры являются аргументами данной формулы. Кроме того, имеется необязательный аргумент «Базис» . Все эти данные могут быть введены с клавиатуры прямо в соответствующие поля окна или храниться в ячейках листах Excel. В последнем случае вместо чисел и дат нужно вводить ссылки на эти ячейки. Также функцию можно ввести в строку формул или область на листе вручную без вызова окна аргументов. При этом нужно придерживаться следующего синтаксиса:
ДОХОД(Дата_сог;Дата_вступ_в_силу;Ставка;Цена;Погашение»Частота;[Базис])
БС
Главной задачей функции БС является определение будущей стоимости инвестиций. Её аргументами является процентная ставка за период («Ставка» ), общее количество периодов («Кол_пер» ) и постоянная выплата за каждый период («Плт» ). К необязательным аргументам относится приведенная стоимость («Пс» ) и установка срока выплаты в начале или в конце периода («Тип» ). Оператор имеет следующий синтаксис:
БС(Ставка;Кол_пер;Плт;[Пс];[Тип])
ВСД
Оператор ВСД вычисляет внутреннюю ставку доходности для потоков денежных средств. Единственный обязательный аргумент этой функции – это величины денежных потоков, которые на листе Excel можно представить диапазоном данных в ячейках («Значения» ). Причем в первой ячейке диапазона должна быть указана сумма вложения со знаком «-», а в остальных суммы поступлений. Кроме того, есть необязательный аргумент «Предположение» . В нем указывается предполагаемая сумма доходности. Если его не указывать, то по умолчанию данная величина принимается за 10%. Синтаксис формулы следующий:
ВСД(Значения;[Предположения])
МВСД
Оператор МВСД выполняет расчет модифицированной внутренней ставки доходности, учитывая процент от реинвестирования средств. В данной функции кроме диапазона денежных потоков («Значения» ) аргументами выступают ставка финансирования и ставка реинвестирования. Соответственно, синтаксис имеет такой вид:
МВСД(Значения;Ставка_финансир;Ставка_реинвестир)
ПРПЛТ
Оператор ПРПЛТ рассчитывает сумму процентных платежей за указанный период. Аргументами функции выступает процентная ставка за период («Ставка» ); номер периода («Период» ), величина которого не может превышать общее число периодов; количество периодов («Кол_пер» ); приведенная стоимость («Пс» ). Кроме того, есть необязательный аргумент – будущая стоимость («Бс» ). Данную формулу можно применять только в том случае, если платежи в каждом периоде осуществляются равными частями. Синтаксис её имеет следующую форму:
ПРПЛТ(Ставка;Период;Кол_пер;Пс;[Бс])
ПЛТ
Оператор ПЛТ рассчитывает сумму периодического платежа с постоянным процентом. В отличие от предыдущей функции, у этой нет аргумента «Период» . Зато добавлен необязательный аргумент «Тип» , в котором указывается в начале или в конце периода должна производиться выплата. Остальные параметры полностью совпадают с предыдущей формулой. Синтаксис выглядит следующим образом:
ПЛТ(Ставка;Кол_пер;Пс;[Бс];[Тип])
ПС
Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ . У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС» ), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт» ). Синтаксис соответственно такой:
ПС(Ставка;Кол_пер;Плт;[Бс];[Тип])
ЧПС
Следующий оператор применяется для вычисления чистой приведенной или дисконтированной стоимости. У данной функции два аргумента: ставка дисконтирования и значение выплат или поступлений. Правда, второй из них может иметь до 254 вариантов, представляющих денежные потоки. Синтаксис этой формулы такой:
ЧПС(Ставка;Значение1;Значение2;…)
СТАВКА
Функция СТАВКА рассчитывает ставку процентов по аннуитету. Аргументами этого оператора является количество периодов («Кол_пер» ), величина регулярной выплаты («Плт» ) и сумма платежа («Пс» ). Кроме того, есть дополнительные необязательные аргументы: будущая стоимость («Бс» ) и указание в начале или в конце периода будет производиться платеж («Тип» ). Синтаксис принимает такой вид:
СТАВКА(Кол_пер;Плт;Пс[Бс];[Тип])
ЭФФЕКТ
Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка. Синтаксис её выглядит так:
ЭФФЕКТ(Ном_ставка;Кол_пер)
Нами были рассмотрены только самые востребованные финансовые функции. В общем, количество операторов из данной группы в несколько раз больше. Но и на данных примерах хорошо видна эффективность и простота применения этих инструментов, значительно облегчающих расчеты для пользователей.
Обширный функционал MS Excel позволяет решать множество задач, в том числе и финансового характера.
В программе есть большое количество инструментов, предназначенных для анализа данных, математических расчетов, сведения планов, отчетов и т. д.
Используя этот программный продукт, можно значительно сэкономить время при подготовке аналитических таблиц, исключить ошибки при расчетах или переносе данных, обусловленные человеческим фактором.
В статье изучим часто используемые функции и возможности MS Excel, которые помогают специалистам экономических и финансовых отделов предприятий найти решения многих практических задач с минимальными затратами сил и времени.
ПРОСТЫЕ ФУНКЦИИ И ФОРМУЛЫ MS EXCEL
Начнем изучение с наиболее простых функций из раздела «Мастер функций» MS Excel.
Функция «СУММ»
Данная функция помогает суммировать значения нескольких ячеек. Рассмотрим пример использования этой функции (рис. 1).
|
№ п/п |
Наименование |
Ед. изм. |
Стоимость ед. изм., руб. |
Расход |
Сумма, руб. |
|||
|
Материал № 1 |
||||||||
|
Материал № 2 |
||||||||
|
Материал № 3 |
||||||||
|
Материал № 4 |
||||||||
|
Материал № 5 |
||||||||
|
Итого |
||||||||
Рис. 1. Пример использования функции «СУММ»
Необходимо посчитать стоимость материальных расходов, затраченных на единицу выпущенной продукции, если известна стоимость закупки единицы измерения и фактический расход каждого вида материала на изготовление единицы продукции (графы 4 и 5 таблицы, представленной на рис. 1). Итог по каждой позиции материала выведен в графе 6 путем перемножения фактического расхода на стоимость закупки.
«Итого» рассчитывают сложением всех подытогов по каждой позиции материала. Для этого используют функцию «СУММ » и выделяют диапазон ячеек с необходимыми значениями данных (в нашем случае — графа 6, которой в MS Excel соответствует столбец «Н»). Тогда формула приобретет следующий вид:
СУММ(H5:H9), где H5:H9 — диапазон данных по графе 6 от материала № 1 до материала № 5.
Когда пользователю нужно рассчитать сумму значений ячеек или применить иную функцию, но при этом получить результат расчетов с округлением (например, без копеек), применяют функции «ОКРУГЛ », «ОКРУГЛВВЕРХ » и «ОКРУГЛВНИЗ ». Как правило, эти функции не используют как самостоятельные, чаще их применяют в комплексе с другими функциями (например, с «СУММ»). В нашем случае по материалу № 2 сумма составляет 50,77 руб . (графа 6). Составим формулу для расчета итоговой суммы с учетом округления:
ОКРУГЛ(СУММ(H5:H9);0), где «0» — число разрядов для округления.
Справочная информация о форматировании ячеек:
1. Чтобы установить количество знаков после запятой, нужно кликнуть правой кнопкой мыши по необходимой ячейке и выбрать «Формат ячеек », где определяется категория формата: числовой, текстовый, процентный, дата и др. (в нашем случае для граф 4-6 нужен числовой формат), а затем устанавливается количество десятичных знаков (для рассматриваемого примера — 2).
Дополнительно можно установить флажок на «Разделитель групп разрядов ». Это обеспечит представление чисел, превышающих тысячу, с соответствующими пробелами для лучшей визуализации информации.
2. Чтобы применить конкретный формат одной ячейки к другим ячейкам, используют функцию «Формат по образцу », представленную на вкладке «Главная» основного меню.
3. Для выравнивания информации в ячейке можно обратиться к «Формату ячеек» и во всплывающем диалоговом окне выбрать «Выравнивание » или воспользоваться одноименной функцией во вкладке «Главная» основного меню (рис. 2). Данная функция позволяет определить направление (ориентацию) текста, его расположение в ячейке. При выборе «перенос по словам » текст ячейки не будет выходить за ее пределы.
Функция «СУММЕСЛИ»
Функция также предназначена для суммирования значений ячеек. Отличительная особенность — назначение конкретного условия (критерия) отбора. Для определения условия используются символы («˃», «<» и др.) и непосредственно текстовые значения ячеек. Рассмотрим пример применения функции «СУММЕСЛИ» (рис. 3).
В таблице с исходными данными, приведенной на рис. 3, отображены расходы предприятия по двум обособленным подразделениям (ОП) — г. Москва и г. Липецк. Суммарные расходы по этим подразделениям составляют 4924 руб. Необходимо рассчитать расходы каждого подразделения. Для этого воспользуемся функцией «СУММЕСЛИ», формула которой имеет следующий вид:
СУММЕСЛИ(диапазон;критерий;[диапазон суммирования]).
В формуле в квадратных скобках указан дополнительный аргумент, который не является обязательным.
Для рассматриваемого примера (см. рис. 3) формулы приобретут следующий вид:
- г . Москва :
СУММЕСЛИ(F17:F24;"Москва";E17:E24) = 2750 руб .;
- г . Липецк :
СУММЕСЛИ(F17:F24;"Липецк";E17:E24) = 2174 руб .
Рассмотрим работу формулы на примере обособленного подразделения в г. Москва:
- первый диапазон ячеек (F17:F24) — это столбец для отбора, где представлены наименования подразделений; критерий отбора в данном случае — Москва;
- второй диапазон (E17:E24) — столбец с суммами расходов, из которых программа выберет те, которые имеют отношение только к критерию отбора, и просуммирует их.
Как отмечено ранее, диапазон суммирования (в формуле указан в квадратных скобках) не является обязательным к заполнению. Например, на основании исходных данных, представленных в таблице на рис. 3, необходимо посчитать сумму расходов в размере 1000 руб. Тогда формула приобретет следующий вид:
СУММЕСЛИ(E17:E24;1000) = 3000 руб .
В данном случае второй диапазон не используется. Достаточно выделить диапазон отбора, который и будет диапазоном для дальнейшего суммирования.
Функции «ЕСЛИ» и «СЧЕТЕСЛИ»
Данные функции используют при установлении определенных условий или критериев.
Функция «СЧЕТЕСЛИ » предназначена для расчета количества ячеек по заданному критерию в формуле и имеет следующий вид:
СЧЕТЕСЛИ(диапазон;критерий).
Функция «ЕСЛИ » позволяет сравнивать значения и в зависимости от результата выводить итог при верном или неверном сравнении. Формула выглядит следующим образом:
ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь]).
Рассмотрим пример применения данных функций (рис. 4).
Для рассматриваемого примера необходимо определить, опаздывал ли сотрудник Иванов И. И. на работу, при условии, что рабочий день согласно трудовому распорядку предприятия начинается в 9 утра. Для этого в графе «Примечание» нужно установить факт наличия опозданий. С этой целью применяем формулу:
ЕСЛИ(G40>F40;"опоздание";"-"), где необходимым условием к выполнению является превышение значения ячеек «G» (время фактического зафиксированного прибытия работника) над значением ячеек «F» (нормативное время прибытия).
Если неравенство выполняется, функция «ЕСЛИ» установит в ячейках «Н» — «опоздание »; если неравенство не выполняется, будет установлен прочерк, который показывает, что факт нарушения трудовой дисциплины не выявлен.
Для определения количества опозданий воспользуемся функцией «СЧЕТЕСЛИ»:
СЧЕТЕСЛИ(H40:H47;"опоздание") = 2 , где функция отбирает ячейки в диапазоне H40:H47 со значением «опоздание» и выводит их количество. В нашем случае Иванов И. И. опоздал на работу дважды, что и посчитала указанная функция.
Дополнительно отметим еще несколько функций с критериями: «ЕСЛИОШИБКА», «СЧЕТЕСЛИМН» и «СЧЕТЗ».
«ЕСЛИОШИБКА » возвращает значение, если вычисление по формуле выдает ошибку, в противном случае — возвращает результат формулы:
ЕСЛИОШИБКА(значение;значение_если_ошибка).
«СЧЕТЕСЛИМН » — функция, похожая на «СЧЕТЕСЛИ», единственное отличие заключается в возможности применения нескольких критериев. Если бы в рассматриваемом примере (рис. 4) не провели предварительный отбор по конкретному сотруднику и по графе 2 встречалось бы несколько сотрудников, то для определения количества опозданий для каждого сотрудника в отдельности нужно было применять функцию «СЧЕТЕСЛИМН».
«СЧЕТЗ » — наиболее простая функция среди рассмотренных, которая рассчитывает количество непустых ячеек в заданном для анализа диапазоне.
Функции «МИН» и «МАКС»
Из названий функций следует, что основная их задача заключается в определении минимальных и максимальных значений в анализируемом диапазоне данных.
На основании исходных данных таблицы, представленной на рис. 4, определим максимальное и минимальное время прибытия на работу сотрудника Иванова И. И.:
МИН(G40:G47) = 8:24:11;
МАКС(G40:G47) = 9:02:14.
Функция «ЧИСТРАБДНИ»
Функция предназначена для расчета количества рабочих дней между двумя датами (начальной и конечной). По умолчанию она считает, что в неделе два выходных дня — суббота и воскресенье. Формула представлена следующим образом:
ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники]), где начальная и конечная дата являются обязательными условиями для заполнения, а праздники заполняются при необходимости.
Рассмотрим пример определения количества рабочих дней за период на основании таблицы, представленной на рис. 5.
- Определим количество рабочих дней за период с 01.07.2018 по 31.07.2018. Известно, что в указанном месяце не было нерабочих праздничных дней. Тогда формула расчета будет иметь следующий вид:
ЧИСТРАБДНИ(B63;C63) = 22 рабочих дня.
- Определим количество рабочих дней в июне 2018 г ., если известно, что 12.06 — государственный праздник. При написании формулы нужно уточнить информацию о празднике:
ЧИСТРАБДНИ(B64;C64;C66) = 20 рабочих дней .
Функция «СРЗНАЧ»
С помощью этой функции определяют среднеарифметическое значение для выбранного диапазона данных. Она работает как с числовыми форматами, так и со временем.
Рассчитаем на основании исходных данных таблицы, отраженной на рис. 4, среднее время прибытия сотрудника на работу. Формула будет иметь следующий вид:
СРЗНАЧ(G40:G47) = 8:50:13.
Часто функцию «СРЗНАЧ» используют для расчета среднего уровня заработной платы. Рассмотрим соответствующий пример с числовыми данными (рис. 6).
Таблица на рис. 6 содержит сведения о зарплате каждого сотрудника. Нужно рассчитать средний уровень зарплаты среди представленных сотрудников:
СРЗНАЧ(D77:D82) = 55 222,39 руб .
Данная формула рассчитала среднеарифметическое по диапазону ячеек с суммами заработных плат. Аналогичный результат получим, разделив итоговую сумму (331 334,34 руб.) на количество сотрудников (6 чел.).
АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ ИНСТРУМЕНТА «СВОДНЫЕ ТАБЛИЦЫ»
В Microsoft Excel можно найти разные инструменты для анализа данных, однако широкое распространение получил инструмент формирования сводных таблиц, который необходим для обобщения и консолидации баз данных. Под базой данных понимают как таблицу из любого файла MS Excel, так и базу данных из внешнего носителя информации (например, 1С).
Сводная таблица представляет собой графическую таблицу, которая динамически изменяется в зависимости от внесенных изменений в исходную базу данных. Она обобщает информацию по заданному критерию или критериям. Дополнительно сводная таблица может выводить промежуточные итоги, раскрывать или скрывать информацию до нужного уровня детализации. С помощью такой таблицы легко строить сводную диаграмму для визуализации полученного результата.
Для построения сводной таблицы при помощи MS Excel нужно определить исходную таблицу или базу данных. Далеко не каждая таблица может подойти для построения сводной таблицы, поэтому настоятельно рекомендуем учитывать основные требования, предъявляемые к исходной базе данных :
- в заголовках столбцов (шапке) исходной таблицы не должно быть объединенных ячеек и столбцов без наименования или с одинаковыми наименованиями;
- в таблице исходной базы данных не должно быть пустых строк (пустые ячейки допустимы). В противном случае MS Excel по умолчанию воспримет это концом таблицы, и все данные, находящиеся после пустой строки, не попадут в сформированную сводную таблицу;
- должны отсутствовать объединенные ячейки внутри таблицы, при их наличии консолидация данных невозможна.
Пример использования инструмента «Сводные таблицы»
Рассмотрим пример использования инструмента MS Excel «Сводные таблицы» на основании исходных данных, приведенных в табл. 1 .
|
Таблица 1. Исходные данные для применения инструмента MS Excel «Сводные таблицы» |
|||
|
Наименование подразделения |
Квартал |
Месяц |
Выручка, тыс. руб. |
|
ОП Москва |
|||
|
ОП С.-Петербург |
|||
|
ОП Саратов |
|||
|
ОП Москва |
|||
Таблица 1 содержит исходные данные по выручке разных обособленных подразделений (ОП ) предприятия с детализацией по месяцам и кварталам. В данном виде анализировать результативность деятельности предприятия проблематично, поэтому рационально использовать сводную таблицу, которая позволит осуществить дальнейшей анализ показателей.
Сформируем сводную таблицу, чтобы проанализировать уровень получаемой выручки по каждому обособленному подразделению в отдельности. Для этого рассмотрим этапы формирования сводной таблицы.
Этап 1. Форматирование исходного диапазона данных для придания анализируемому объему данных значения таблицы (необязательный пункт). Это возможно с помощью функции «Форматировать как таблицу» командной строки «Главная». В данном разделе MS Excel предлагает разные стили оформления, из которых можно выбрать любой понравившийся, так как для построения сводных таблиц стиль не имеет никакого значения.
Указанная функция облегчает добавление новых строк в таблицу. Когда появится необходимость продолжить таблицу исходных данных, достаточно в следующей строке после крайней записи начать вносить новые данные, и MS Excel автоматически распознает их как часть таблицы. В дальнейшем это облегчит обновление сводных таблиц с учетом новой информации.
Данное форматирование устанавливает на каждый столбец автофильтр, что упрощает пользователю отбор по выбранному критерию. Дополнительные преимущества:
- автоматическое протягивание формул, если в каком-либо столбце и/или строке исходной таблицы они есть;
- полное копирование формата по образцу (выравнивание в ячейке, шрифт и его размер, формат ячеек и т. д.).
А. Н. Дубоносова, заместитель управляющего директора по экономике и финансам
Материал публикуется частично. Полностью его можно прочитать в журнале
Доброго времени суток уважаемый читатель!
Эту статью я хочу посвятить экономистам и их работе, точнее говоря тем инструментам, которые им очень нужны в работе и позволят значительно сократить свои усилия, сэкономят ваше время и улучшат ваши результаты.
Несмотря на всё то многообразие функций, которые существует в Excel, тем не менее, есть основная когорта функций, знать которые необходимо каждому уважающему себя и свой труд экономисту. Да именно экономисту, человеку с которого реально начинается работа предприятия, ведь исходя из его планирования и расчётов действует предприятие, они решают, как и что надо делать, что бы получить позитивные экономический эффект. Конечно, многие могут, не согласится с моими словами, но я говорю исходя из собственного опыта и взглядов, а они у каждого разные.
Я сомневаюсь что многие будут со мной спорить в том вопросе что знать экономический эффект от любого действия на предприятии или рассчитать прибыль для любого продукта это «архиважно» товарищи. А вот для этого и важны те функции, которые мы будем рассматривать. Рассмотрим 5 ТОП функций для экономиста, это:
Функция ЕСЛИ в
Excel
Начну, пожалуй, с самой главной функции в жизни любого экономиста, это функция ЕСЛИ. Это самая мощная и классная логическая функция в арсенале, да именно логическая, так как расчёты экономиста, это и есть большой объём логических вариантов разнообразных вычислений.
Функция ЕСЛИ в Excel используется как в простом исполнению, где происходит простая проверка условий так и в сложном варианте, когда формула проверяет много критериев и логических вариантов и подбирает необходимый, исходя из первоначальных данных.
С помощью функции ЕСЛИ можно вычислять не только числовые значения, но и текстовые, условия применения настолько широки что их даже все и не перечислишь (я просто уверен что всех и не знаю), это может быть любые вычисления по установленным критериям (амортизация, расчёт штатных единиц, штатное расписание, наценка и прочее), также убирать с вычислений , возникающие в и многое другое. Также, данная функция используется как встроенная функция для получения логического аргумента в , в , математических и прочих.
У данной логической функции есть разнообразные вариации функций адаптированные для других категорий, это , , но их специфика иная и о них будем говорить отдельно.
Детально о том как работает эта функция вы можете .
Функция ВПР в
Excel
Следующей функцией, которая заслуживает наше внимание, является функция ВПР с категории «Массивы и ссылки»
. Я думаю, что нового не скажу и откровением не станет тот факт, что в работе каждого экономиста встречаются большие объемы информации, громадные таблицы, которые нужно перелопатить, анализировать для получения нужных данных, вот такие данные и называются .
В этой статье я обращаю ваше внимание как работает функция ВПР в Excel, так как она производит поиск в вертикальных списках данных, которые наиболее распространенные в нашей работе. Есть еще , которая работает аналогично, но поиск производит в горизонтальных списках, а это намного реже нужно, нежели в вертикальных. Можно также использовать и для расширения ваших возможностей, но о них вы почитаете в других статьях на моем сайте.
Это функция является своеобразным культовым символом при работе с массивами и не знать о ее возможностях это грех. Она производит поиск любых значений по заданным критериям в большом массиве данных, задать критерии можно даже с помощью символов подстановки, что позволит расширить горизонты применения на небывалую величину.
Детально о том как работает описываемая функция в Excel вы можете .
Функция СУММЕСЛИ в
Excel
Представляю вашему вниманию третью очень нужную функцию, функция СУММЕСЛИ, как видите, состоит из 2 частей и то есть логически вы видите что формула будет суммировать определенное значение по определенному критерию. Это особенно актуально, когда нужно выбрать и просуммировать из большого диапазона только определенное значение, например, сколько было списано сырья в производство всего, если вам дали общее списание по предприятию по дням. Вам нужно просто указать, что именно вас интересует и где это взять, а формула сделает всё за вас, ну не все, конечно, саму формулу вы уже сами будете писать.
Функция СУММЕСЛИ в Excel хороша еще тем что, спокойно работает с значений, что значительно упрощает рутинные вычисления. Но стоит помнить, что функция чувствительна к точности написания критериев и даже в один знак не даст вам правильный результат.
Детальнее о том, как работает СУММЕСЛИ в Excel вы можете .
Функция СУММЕСЛИМН в
Excel
Ну вот теперь перейдем к еще более сложному, шучу, варианту, функция СУММЕСЛИМН. Вы уже ознакомились и знаете о функциях СУММ, ЕСЛИ, СУММЕСЛИ
, а вот теперь соединим всё это во множестве, как вы поняли с последних двух буковок функции, и получим нужную нам функцию. И теперь вы сможете делать выборку по 127 критериям
, обалдеть, я даже не могу придумать, зачем мне, сколько критериев, хотя для вас это может стать панацеей.
Большим плюсом того как работает функция СУММЕСЛИМН в Excel, это работа с символами подстановки, а также с операторами отношений, типа «больше», «меньше», «равно». Также не стоить забывать, что для удобства работы с функцией стоить использовать , что позволит вам более удобно использовать столь полезную функцию.
В целом при работе с большими массивами данных функция СУММЕСЛИМНбудет являться для вас неоценимым помощником.
Детально о том, как работает функция нашего топ списка вы можете .
Функция СУММПРОИЗВ в
Excel
Пятой функцией нашего топ-списка станет функция СУММПРОИЗВ, которая является, наверное, даже наиглавнейшей функцией для экономиста. Она позволяет воплотить в себе практически все предыдущее возможности, которые имеют , ,
, а также производить свои вычисление в 255 массивах
, а это, я вам скажу, ох как много.
Функция СУММПРОИЗВ в Excel позволит вам справится практически с любой экономической задачей, где фигурируют массивы. Подобрав правильные критерии или условия, с помощью формул или иным способом, любые задачи смогут капитулировать перед легкостью, с которой эта функция будет их решать.
Не стоит заблуждаться ее кажущейся простотой или запутанным описанием, это отличный инструмент о котором вы должны знать и уметь им пользоваться, это сократить ваше время и сбережёт километры нервных клеток.
Детально о том, как работает функция СУММПРОИЗВ в Excel вы можете .
А на этом у меня всё! Я очень надеюсь, что список самых важных ТОП-5 функций для экономиста или бухгалтера мы рассмотрели. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
То не беда, если за рубль дают полрубля; а то будет беда, когда за рубль станут давать в морду.
Михаил Салтыков-Щедрин
" № 28/2011
Александр
Зеляев,
заместитель начальника отдела
проектного планирования управления инвестиционных программ ОАО «АВТОВАЗ»
Павел Зырянов,
начальник управления инвестиционных программ
ОАО «АВТОВАЗ»
При выборе оптимального банковского вклада для размещения средств компании финансовой службе надо одновременно оценить несколько показателей: срок вклада, сумму, процент и, наконец, банк. Выполнять подсчеты вручную сложно и трудоемко. Упростить работу можно при помощи функции программы Excel «Поиск решения».
Установка ограничений
Суть в том, что программа перебирает все возможные варианты и выбирает самый лучший по заданным параметрам. Поэтому сначала надо задать условия, по которым Excel будет выбирать вклад. Для депозита главное условие – максимальный доход, но есть и другие.
Всю информацию заносят в одну таблицу, например, как показано на скриншоте (см. рисунок). В примере условий несколько. Во-первых, это свободные суммы, которые есть у компании на счетах, и время, в течение которого эти деньги не нужны в обороте. На скриншоте это диапазоны ячеек H12:K12 и H11:K11.
Во-вторых, надо внести ставки по депозитам, которые предлагают банки (D17:G25). Наконец, в-третьих, предстоит ввести сведения об объемах неиспользованных лимитов по каждому из банков (С17:С25).
Настройка файла для расчетов
После надо создать табличные блоки для вывода промежуточных и итоговых данных. В примере это разделы «Суммы к размещению на депозитах по срокам в днях, руб.», «Итого размещено, руб.», «Доход от средств, размещенных на депозитах, по срокам в днях, руб.» и «Доходность от размещения на депозитах, итого».
В блоке «Суммы к размещению на депозитах по срокам в днях, руб.» (H17:K25) отразятся результаты. Excel здесь заполнит все сам.
В разделы «Итого размещено, руб.» (С17:С25)) и «Доход от средств, размещенных на депозитах, по срокам в днях, руб.» (M17:P25) потребуется вручную ввести формулы. Они нужны, чтобы суммировать депозиты и вычислить доход от вклада. Итоговый результат будет в ячейке «Доходность от размещения на депозитах итого, в рублях».
Поиск решения
Запускаем процедуру «Поиск решения». Для этого надо кликнуть по одноименной кнопке на вкладке панели инструментов «Данные». В нашем примере это будет выглядеть так. В появившемся диалоговом окне в графе «Установить целевую ячейку» надо поставить ячейку С30 «Доходность от размещения на депозитах итого, в рублях». Затем выбрать «максимальное значение». Диапазон, в котором будут отражаться результаты решения, то есть суммы вкладов в различных банках (H17:K25).
И в этом же диалоговом окне необходимо задать ограничения. Они у нас следующие
– все свободные деньги должны быть размещены на депозитах (Н27:К27 = Н12:К12);
– сумма вкладов в одном банке с учетом лимита (L17:L25 <= C17:C25).
Затем надо нажать кнопку «Выполнить». Программа найдет самое выгодное решение.
КСТАТИ. В Excel настройка «Поиск решения» по умолчанию отключена. Включают ее так: кнопка «Office», затем «Параметры Excel» > «Надстройки» > «Перейти», в появившемся окне поставить галочку «Поиск решения» и нажать «Ок».
Скачать файл с готовыми формулами и инструкцию пользователя .
Подготовлено в сотрудничестве с редакцией журнала
Или . Чтобы воспользоваться автоматической установкой Вам потребуются права администратора. Если прав администратора нет - ничего страшного, ручная установка не вызовет проблем. Инструкция по установке ниже:
Для тех, у кого есть права администратора
- Скачайте надстройку
- Запустите разархивированный файл (Установка и удаление надстройки) из папки «Автоматическая установка»
- Готово
Для тех, кто не хочет лишний раз беспокоить сисадмина:)
- Скачайте надстройку
- Разархивируйте сохраненный файл
- Закройте все приложения MS Office (Word, Excel, Outlook и др.), предварительно сохранив открытые файлы
- Запустите разархивированный файл Excel «Установка и удаление надстройки» из папки «Ручная установка»
- Разрешите запуск макросов при запуске файла и кликните по кнопке «Установить надстройку Финансист»
- Перезагрузите Excel (закройте и заново откройте)
- Готово
Если не получилось, можно действовать так:
Как обновить надстройку
Автоматическое обновление. Для тех, у кого есть права администратора на компьютере
- Скачайте надстройку
- Разархивируйте сохраненный файл в новую папку
- Закройте все приложения MS Office (Word, Excel, Outlook и др.), предварительно сохранив открытые файлы
- Запустите разархивированный файл «Установка и удаление надстройки» (из папки «Автоматическая установка»)
- в открывшемся окне выбираем опцию «Удалить надстройку» -> кнопка «Выполнить»
- запускаем этот же файл («Установка и удаление надстройки») повторно, опция «Добавить надстройку» -> кнопка «Выполнить»
- Перезагрузите Excel (закройте и заново откройте)
- Готово
Ручное обновление. Для тех, кто не хочет лишний раз беспокоить сисадмина:)
- Скачайте надстройку
- Разархивируйте сохраненный файл
- Закройте все приложения MS Office (Word, Excel, Outlook и др.), предварительно сохранив открытые файлы
- Из папки «Ручная установка» запустите разархивированный файл Excel «Установка и удаление надстройки»
- Разрешите запуск макросов при запуске файла и кликните по кнопке «Удалить надстройку Финансист» (файл удалит старую версию)
- Кликните по кнопке «Установить надстройку Финансист»
- Перезагрузите Excel (закройте и заново откройте)
- Готово
Ответы на вопросы
Что такое надстройка «Финансист»? Надстройка «Финансист» - это программа, добавляющая в Excel дополнительные команды и функции, которые будут полезны в работе финансовому директору и другим сотрудникам финансовой службы. В стандартной версии Excel такого функционала нет, либо он запрятан так далеко, что использовать его не слишком удобно.
Кому надстройка будет полезна. В-первую очередь надстройка будет полезная финансовым директорам и другим специалистам финансовой службы. Ведь именно с учетом их специфики работы формировался список команд и функций, вошедших в состав надстройки. Тем не менее надстройка будет полезна и другим специалистам, которым приходится много работать с Excel, формируя всевозможные отчеты. Например, генеральным директорам и главным бухгалтерам.
