Нов-и Excel

Поделиться:

Функции Excel для экономиста

7 функций Excel для экономиста: простые примеры для сложных задач

2020-08-04 2418

Вычислительные функции — это сердцевина Excel и то, что, по-сути, отличает программу от обычного калькулятора. Функций в программе довольно много — более 400. Часть из них дублируется, но тем не менее, возможности для экономических расчетов в программе огромные.
С помощью формул можно безошибочно рассчитать себестоимость товара, подготовить аналитические отчеты, свести планы. Решить как простые экономические задачи учета, так и составить полный баланс предприятия. Подборку инструментов Excel для бухгалтера мы уже делали. Настал черед рассмотреть основные функции Excel для экономиста, которые упростят работу с данными и большими таблицами.

Что такое функции Excel и где они находятся

Функции в Excel — это команды, которые помогают в считанные минуты откорректировать данные в ячейках, рассчитать значения по определенным критериям, изъять нужную информацию из огромного массива. Функции часто используют в формулах, чтобы их упростить и сделать более гибкими.

Как мы уже говорили, в программе функций много — около 10 категорий: есть математические, логические, текстовые. И специальные функции — финансовые, статистические и пр. Все функции лежат во вкладке «Формулы». Перейдя в нее, нужно нажать на кнопку «Вставить функцию» на панели инструментов, после чего запустится «Мастер функций».

Вставка функции

Останется выбрать в «Категориях» нужную функцию для вычислений и заполнить данные по структуре в выпадающем окошке.

Если вы знаете, какая функция вам нужна, просто введите ее в строке формул, начиная со знака «=». Как только ввели, нажмите «Enter» для вычисления.

подсказки в Excel

Запоминать, что делает каждая функция не нужно. В Excel есть удобные подсказки, зачем нужна та или иная формула, и как ее использовать.

Ниже рассмотрим основные и часто используемые формулы в Excel для экономистов: ЕСЛИ, СУММЕСЛИ, ВПР, СУММПРОИЗВ, СЧЁТ, СРЗНАЧ и МАКС/МИН.

Функция ЕСЛИ для сравнения данных

Самая популярная логическая функция, которая полезна экономистам, бухгалтерам. С ее помощью можно сравнить числовые и текстовые значения по определенным критериям. Например, рассчитать амортизацию, скидки, наценку или убрать ошибки из расчетов. Иногда используется встроенная функция ЕСЛИ в статистических, текстовых и математических формулах, что наделяет их способностью «принимать решения».

Функция ЕСЛИ помогает точно сравнить значения и получить результат, в зависимости от того, истинно сравнение или нет.

Так выглядит формула:
=ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь])

  • Лог_выражение — это то, что нужно проверить или сравнить (числовые или текстовые данные в ячейках)
  • Значение_если_истина — это то, что появится в ячейке, если сравнение будет верным.
  • Значение_если_ложь — то, что появится в ячейке при неверном сравнении.

Например, магазин торгует аксессуарами для мужчин и женщин. В текущем месяце на все женские товары скидка 20%. Отсортировать акционные позиции можно с помощью функции ЕСЛИ для текстовых значений.

Пропишем формулу в столбце «Скидка» так:
=ЕСЛИ(B2="женский";20%;0)
И применим ко всем строкам. В ячейках, где равенство выполняется, увидим товары по скидке.

функция ЕСЛИ для текстовых значений

Так применяется функция ЕСЛИ для текстовых значений с одним условием

Функции СУММЕСЛИ и СУММЕСЛИМН

Еще одна полезная функция СУММЕСЛИ, которая позволяет просуммировать несколько числовых данных по определенному критерию. Состоит формула из 2-х частей:

  • СУММ — математическая функция сложения числовых значений. Записывается как =СУММ(ячейка/диапазон 1; ячейка/диапазон 2; …).
  • и функция ЕСЛИ, которую рассмотрели выше.

Например, вам нужно просчитать общую сумму начисленной заработной платы менеджерам, а в отчете указаны данные по всем сотрудникам предприятия.

В формуле нужно прописать такие аргументы:

  • Выделить диапазон всех должностей сотрудников — в нашем случае B2:B10.
  • Прописываем критерий выбора через точку с запятой — “менеджер”.
  • Диапазон суммирования — это заработные платы. Указываем C2:C10.

И получаем в один клик общую сумму заработной платы менеджеров:

функции excel для экономистов

С помощью СУММЕСЛИ можно просуммировать ячейки, которые соответствуют определенному критерию

Важно! Функция СУММЕСЛИ чувствительна к правильности и точности написания критериев. Малейшая опечатка может дать неправильный результат. Это также касается названий ячеек. Формула выдаст ошибку, если написать диапазон ячеек кириллицей, а не латиницей.

Более сложный вариант этой формулы — функция СУММЕСЛИМН. По-сути, это выборочное суммирование данных, отобранных по нескольким критериям. В отличие от СУММЕСЛИ, можно использовать до 127 критериев отбора данных. Например, с помощью этой формулы легко рассчитать суммарную прибыль от поставок разных товаров сразу в несколько стран.

В функции СУММЕСЛИМН можно работать с подстановочными символами, использовать операторы для вычислений типа «больше», «меньше» и «равно». Для удобства работы с функцией лучше применять абсолютные ссылки в Excel — они не меняются при копировании и позволяют автоматически пересчитать формулу, если данные в ячейке изменились.

Функции ВПР и ГПР — поиск данных в большом диапазоне

Экономистам часто приходится обрабатывать огромные таблицы, чтобы получить необходимые данные для анализа. Или сводить две таблицы в одну, что тоже не редкость. Функция ВПР или, как ее еще называют, вертикальный просмотр (англ. вариант VLOOKUP) позволяет быстро найти и извлечь нужные данные в столбцах. Либо перенести данные из одной таблицы в соответствующие ячейки другой.

Синтаксис самой простой функции ВПР выглядит так:
= ВПР(искомое_значение; таблица; номер_столбца; [интервальный просмотр]).

Например, вам нужно быстро извлечь наименование товара по номеру в списке. С помощью функции ВПР это сделать очень просто:

Функции ВПР и ГПР

Функция ВПР позволяет быстро найти нужные данные и перенести их в выделенную ячейку.

В ячейке С1 мы указали номер товара. Потом выделили диапазон ячеек, где его искать (A1:B10) и написали номер столбца «2», в котором нужно взять данные. Нажали Enter и получили нужный товар в выделенной ячейке.

Есть менее популярная функция ГПР. Она работает также, как ВПР, но ищет данные в горизонтальных списках.

Важно! Чтобы увеличить область применения этой функции, можно задать данные с помощью подстановочных знаков.

Одна из задач этой функции — упростить подготовку исходных данных для аналитических и сводных таблиц. Как подготовить такую таблицу можете почитать бесплатно в книге Сводные таблицы в Excel.

Функция СУММПРОИЗВ в Excel

Четвертая функция нашего списка — СУММПРОИЗВ или суммирование произведений. Поможет быстро справиться с любой экономической задачей, где есть массивы. Включает в себя возможности предыдущих формул ЕСЛИ, СУММЕСЛИ и СУММЕСЛИМН, а также позволяет провести расчеты в 255 массивах. Ее любят бухгалтеры и часто используют при расчетах заработной платы и других расходов.

Что в данном случае может быть произведениями? Как заданные диапазоны, так и целые массивы данных. Чтобы задать количество массивов, их нужно перечислить через точку с запятой, потом перемножить, а затем просуммировать. При этом, есть одно условие — массивы должны быть одинаковые по типу и длине.

Например, у вас есть месячный отчет по продажам в магазине, из которого нужно узнать, на какую сумму продали футболок размера «М».

Для этого используем функцию СУММПРОИЗВ и указываем 2 условия. Каждое из них берем в скобки, а между ними ставим «звездочку», которая в Excel читается как союз «и».

Запишем команду так: =СУММПРОИЗВ((A5:A11=A13)*(B5:B11=B13)*C5:C11), где

  • первое условие A5:A11=A13— диапазон поиска и наименование нужного товара
  • второе условие B5:B11=B13 — диапазон поиска и размер
  • C5:C11 — массив, из которого берется итоговая сумма
формулы excel для экономистов

С помощью функции СУММПРОИЗВ мы узнали за пару минут, что в магазине за месяц продали футболок М-размера на 100 у.е.

Чтобы узнать продажи по другому товару и размеру, нужно просто заменить данные в ячейках A13 и B13.

Данный пример слишком прост, по сравнению с тем, с какими объемами данных приходится работать экономистам. Но зная, что умеет данная формула, уже не так страшны таблицы в миллионы строк и сотни столбцов, а нужные данные находятся за считанные минуты.

Как применить МАКС, ВПР и ПОИСКПОЗ для решения задач

Функции МИН и МАКС помогают найти наименьшее или наибольшее значение данных. Функция ПОИСКПОЗ помогает найти номер указанного элемента в выделенном диапазоне. А формула ВПР, напомним, позволяет извлечь нужные данные из столбцов в указанные ячейки.

Рассмотрим комбинацию этих функций на примере решения задачи в Excel для экономистов. Составим комбинированную формулу, чтобы найти компанию с наибольшей суммой задолженности.

Для начала составим список должников, укажем сумму их задолженности и выставим дату окончания кредитного договора.

МАКС, ВПР и ПОИСКПОЗ для решения задач

Для решения задачи, можно применить функции последовательно:

  • Найти самый крупный долг поможет функция МАКС (=МАКС(B2:B10)), где B2:B10 — столбец с данными по задолженности.
макс впр
  • Чтобы найти номер компании-должника в списке, нужно в таблицу добавить столбец с нумерацией. Так как функция ПОИСКПОЗ ищет данные только в крайнем левом столбце выделенного диапазона.
функция ПОИСКПОЗ

Составляем функцию по формуле:
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

В нашем случае это будет =ПОИСКПОЗ(14569;C2:C10;0), где искомое — максимальная сумма долга. Тип сопоставления будет “0”, потому что к столбцу с долгами мы не применяли сортировку.

  • Чтобы узнать название компании-должника, применим знакомую функцию ВПР.

Выглядеть она будет так =ВПР(D14;A2:B10;2), где D4 — искомое, A2:B10 — таблица или выделенный диапазон с названиями компаний и нумерацией, а “2” — номер столбца с должниками.

решение финансовых задач в excel примеры

Этот же результат можно было получить, собрав одну формулу из 3-х:

=ВПР (ПОИСКПОЗ (МАКС (C2:C10); C2:C10;0); A2:B10;2).

В экономических расчетах функция ВПР помогает быстро извлечь нужное значение из огромного диапазона данных. Причем значение можно найти по разным критериям отбора. Например, цену товара можно извлечь по идентификатору, налоговую ставку — по уровню дохода и пр.
Кроме вышеупомянутых функций, экономисты часто используют формулу СРЗНАЧ, например, для расчета средней заработной платы. Функцию СЧЁТ, когда нужно рассчитать количество отгрузок в разрезе клиентов или стоимости товара за определенный период. Кстати, на примере отгрузок, формула МИН/МАКС поможет отследить диапазон, в котором изменялась стоимость товара.

Существует еще сотня математических и финансовых функций в Ехсеl, с помощью которых можно решить экономические задачи разной сложности. Например, обрабатывать заказы, планировать загрузку производства, рассчитывать налоги, вести кадровый учет и управлять сбытом. Но для решения подобных задач, подсказок в Excel будет уже недостаточно. Тут необходимо глубже погружаться в программу и учиться работать с разными ее инструментами: от функций и сводных таблиц, до диаграмм и макросов.

Хотите полностью изучить Excel и владеть ее инструментами на уровне «профи»? Запишитесь на мультикурс Excel, чтобы поэтапно освоить программу от базовых инструментов до ярких визуализаций данных, занимаясь по собственному графику.
Оцените материал

Поделиться:

Добро пожаловать!

Куда хотите войти:

В систему дистанционного
обучения

Войти

В личный кабинет пользователя
вебинаров

Войти

Рады приветствовать на нашем сайте!

У нас есть два полезных портала для повышения квалификации!
Скажите, что именно вас интересует?

Курсы повышения
квалификации

Зарегистрироваться

Вебинары об изменениях
в законодательстве

Зарегистрироваться