Функции эксель для экономистов

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

2020-11-27 7370

Вычислительные функции — это сердцевина 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, чтобы поэтапно освоить программу от базовых инструментов до ярких визуализаций данных, занимаясь по собственному графику.

Насколько уверенно вы владеете Excel?

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

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

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

С опорой на математику и программу

Стандартные вычисления и сравнения числовых данных в Excel можно осуществлять двумя способами.

Во-первых, с помощью арифметических операторов, известных всем из школьного курса:

– сложение (+) / вычитание (-);

– умножение (*) / деление (/);

– возведение в степень (^).

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

Зная математику всего лишь на базовом уровне, в Excel уже можно выполнять различные экономические вычисления.

В качестве примера возьмем расчеты, с которыми сталкивается, пожалуй, большинство экономистов – калькуляция себестоимости и определение отпускной цены товара.

Предположим, у нас есть ряд данных для расчетов – естественно, в каждой организации они будут своими, поэтому ограничимся условными (см. табл. 1).

Таблица 1

Статьи

Данные

Сырье и материалы (СиМ), руб.

Возвратные отходы, %

5% от СиМ

Топливо и энергия, руб.

Основная заработная плата (ЗП)

производственных рабочих, руб.

Дополнительная ЗП производственных рабочих, руб.

10% от основной ЗП рабочих

Отчисления из ЗП производственных рабочих, руб.

35%

Общепроизводственные расходы, руб.

15% от ЗП рабочих

Общехозяйственные расходы, руб.

20% от ЗП рабочих

Прибыль, %

6,5% от полной себестоимости

НДС

20% от оптовой цены

На основе данных таблицы произведем калькуляцию себестоимости и рассчитаем отпускную цену, пользуясь обычными математическими фор­мулами:

1) возвратные отходы = 100 x 0,05 = 5 BYN;

2) дополнительная ЗП = 55 x 0,1 = 5,5 BYN;

3) отчисления из ЗП = (55+5,5) x 0,35 = 21,2 BYN;

4) общепроизводственные расходы = (55+5,5) x 0,15 = 9,1 BYN;

5) общехозяйственные расходы = (55+5,5) x 0,2 = 12,1 BYN;

6) полная себестоимость = 100+5+23+55+5,5+21,2+­ 9,1+12,1= 230,9 BYN;

7) прибыль = 230,9 x 0,065 = 15 BYN;

8) оптовая цена товара = 230,9 + 15 = 245,9 BYN;

9) НДС = 245,9 x 0,2 = 49,2 BYN;

10) отпускная цена товара = 245,9 + 49,2 = 295,1 BYN.

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

Вначале обратим внимание на некоторые особенности работы с программой.

1. Формула может вноситься сразу в ячейку, в которой осуществляется расчет, либо, выбрав необходимую ячейку, в строку формул (см. табл. 2).

Таблица 2

2. Ввод функции начинается со знака «=».

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

4. После внесения формулы необходимо нажать «Enter» для ее вычисления.

Продемонстрируем, как выглядят калькуляция себестоимости и расчет отпускной цены в Excel (см. табл. 3).

Таблица 3

Таким образом, в табл. 3 мы видим итоговые значения, а также формулы для их расчетов с использованием как цифр, так и ссылок на ячейки.

Минимум формализма и формул

Подобным способом c помощью обычных арифметических операторов в Excel можно осуществлять абсолютно любые экономические расчеты. Но чем же отличаются возможности расчетов в программе от обычного калькулятора?

Одним из основных отличий является еще один способ вычислений, упомянутый ранее, а именно использование формул, которые разработчики заботливо зафиксировали в программе, существенно упростив тем самым работу пользователя Excel.

В программе представлен широкий выбор функций, разбитый по блокам области их применения: начиная от общих (текстовых, математических, логических) и заканчивая специальными формулами (статические, инженерные, финансовые). Увидеть полную Библиотеку функций можно, перейдя на вкладку Формулы.

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

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

Для начала рассмотрим самые простые, но при этом наиболее часто используемые формулы для экономических расчетов и анализа данных: СУММ, СЧЁТ, СРЗНАЧ, МАКС, МИН.

1. Функция СУММ. Для подведения экономических итогов, пожалуй, эта функция используется наиболее часто. Она может помочь рассчитать как сумму отдельных чисел, так и сумму значений в одном или нескольких диапазонах данных.

Структура записи: СУММ (ячейка/диапазон 1; ячейка/диапазон 2; …).

Пример использования: вернемся к нашему примеру калькуляции себестоимости. Так, при вычислении полной себестоимости можно было не вносить сумму множества слагаемых, а применить формулу СУММ.

Функция в данном случае выглядела бы так: =СУММ (С3:С10).

2. Функция СЧЁТ. Данная формула посчитывает количество ячеек, содержащих числовое значение. Можно производить подсчет в одном или нескольких диапазонах, а также в массивах данных.

Структура записи: СЧЁТ (ячейка/диапазон 1; ячейка/диапазон 2; …).

Пример использования: предположим, у нас есть данные по отгрузкам за месяц в разрезе клиентов с указанной стоимостью. Для расчета количества отгрузок, совершенных в данном периоде, может применяться функция СЧЁТ, где диапазоном анализа выступит столбец с суммами товаров.

3. Функция СРЗНАЧ. Является очередным примером помощи разработчиков Excel, заменяющей комбинацию формул СУММ и СЧЁТ для расчета среднего арифметического значения аргументов. Для вычислений могут использоваться как отдельные числа, так и диапазоны значений.

Структура записи: СРЗНАЧ (ячейка/диапазон 1; ячейка/диапазон 2; …).

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

4. Функции МИН / МАКС. Данные формулы помогают анализировать данные, упрощая поиск минимального и максимального значения из отдельных чисел или диапазонов значений.

Структура записи: МИН (ячейка/диапазон 1; ячейка/диапазон 2; …); МАКС (ячейка/диапазон 1; ячейка/диапазон 2; …).

Пример использования: предположим, используя наш пример, что мы также хотим проанализировать диапазон, в котором изменялась стоимость отгрузок в течение месяца. Таким образом для поиска минимального значения нам пригодится формула МИН, а для максимального – МАКС. В обоих функциях данными для анализа будут выступать суммы отгрузок.

* * *

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

В дальнейшем мы рассмотрим более сложные, но при этом очень полезные для вашей работы формулы, а также некоторые другие возможности про­граммы.

Автор публикации: Валерия СТОЯНОВА, бизнес-аналитик консультационной компании «Ключевые решения» www.krconsult.org

Добавить комментарий