Что нужно уметь экономисту в эксель
ТОП 5 лучших функций Excel для экономиста
Доброго времени суток уважаемый читатель!
Эту статью я хочу посвятить экономистам и их работе, точнее говоря тем инструментам, которые им очень нужны в работе и позволят значительно сократить свои усилия, сэкономят ваше время и улучшат ваши результаты.
Несмотря на всё то многообразие функций, которые существует в Excel, тем не менее, есть основная когорта функций, знать которые необходимо каждому уважающему себя и свой труд экономисту. Да именно экономисту, человеку с которого реально начинается работа предприятия, ведь исходя из его планирования и расчётов действует предприятие, они решают, как и что надо делать, что бы получить позитивные экономический эффект. Конечно, многие могут, не согласится с моими словами, но я говорю исходя из собственного опыта и взглядов, а они у каждого разные.
Я сомневаюсь что многие будут со мной спорить в том вопросе что знать экономический эффект от любого действия на предприятии или рассчитать прибыль для любого продукта это «архиважно» товарищи. А вот для этого и важны те функции, которые мы будем рассматривать. Рассмотрим 5 ТОП функций для экономиста, это:
Функция ЕСЛИ в Excel
Начну, пожалуй, с самой главной функции в жизни любого экономиста, это функция ЕСЛИ. Это самая мощная и классная логическая функция в арсенале, да именно логическая, так как расчёты экономиста, это и есть большой объём логических вариантов разнообразных вычислений.
Функция ЕСЛИ в Excel используется как в простом исполнению, где происходит простая проверка условий так и в сложном варианте, когда формула проверяет много критериев и логических вариантов и подбирает необходимый, исходя из первоначальных данных.
С помощью функции ЕСЛИ можно вычислять не только числовые значения, но и текстовые, условия применения настолько широки что их даже все и не перечислишь (я просто уверен что всех и не знаю), это может быть любые вычисления по установленным критериям (амортизация, расчёт штатных единиц, штатное расписание, наценка и прочее), также убирать с вычислений разнообразнейшие ошибки, возникающие в промежуточных итогах и многое другое. Также, данная функция используется как встроенная функция для получения логического аргумента в статистических формулах, формулах массива, в текстовых, математических и прочих.
У данной логической функции есть разнообразные вариации функций адаптированные для других категорий, это СУММЕСЛИ, СЧЁТЕСЛИ, СУММЕСЛИМН, но их специфика иная и о них будем говорить отдельно.
Детально о том как работает эта функция вы можете ознакомиться и посмотреть видео здесь.
Функция ВПР в Excel
Следующей функцией, которая заслуживает наше внимание, является функция ВПР с категории «Массивы и ссылки». Я думаю, что нового не скажу и откровением не станет тот факт, что в работе каждого экономиста встречаются большие объемы информации, громадные таблицы, которые нужно перелопатить, анализировать для получения нужных данных, вот такие данные и называются «массивы».
В этой статье я обращаю ваше внимание как работает функция ВПР в Excel, так как она производит поиск в вертикальных списках данных, которые наиболее распространенные в нашей работе. Есть еще функция ГПР, которая работает аналогично, но поиск производит в горизонтальных списках, а это намного реже нужно, нежели в вертикальных. Можно также использовать функции ПОИСКПОЗ и ИНДЕКС для расширения ваших возможностей, но о них вы почитаете в других статьях на моем сайте.
Это функция является своеобразным культовым символом при работе с массивами и не знать о ее возможностях это грех. Она производит поиск любых значений по заданным критериям в большом массиве данных, задать критерии можно даже с помощью символов подстановки, что позволит расширить горизонты применения на небывалую величину.
Детально о том как работает описываемая функция в Excel вы можете ознакомиться и посмотреть видео здесь.
Функция СУММЕСЛИ в Excel
Представляю вашему вниманию третью очень нужную функцию, функция СУММЕСЛИ, как видите, состоит из 2 частей функция СУММ и функция ЕСЛИ то есть логически вы видите что формула будет суммировать определенное значение по определенному критерию. Это особенно актуально, когда нужно выбрать и просуммировать из большого диапазона только определенное значение, например, сколько было списано сырья в производство всего, если вам дали общее списание по предприятию по дням. Вам нужно просто указать, что именно вас интересует и где это взять, а формула сделает всё за вас, ну не все, конечно, саму формулу вы уже сами будете писать.
Функция СУММЕСЛИ в Excel хороша еще тем что, спокойно работает с поименованными диапазонами значений, что значительно упрощает рутинные вычисления. Но стоит помнить, что функция чувствительна к точности написания критериев и даже ошибка в один знак не даст вам правильный результат.
Детальнее о том, как работает СУММЕСЛИ в Excel вы можете ознакомиться и посмотреть видео здесь.
Функция СУММЕСЛИМН в Excel
Ну вот теперь перейдем к еще более сложному, шучу, варианту, функция СУММЕСЛИМН. Вы уже ознакомились и знаете о функциях СУММ, ЕСЛИ, СУММЕСЛИ, а вот теперь соединим всё это во множестве, как вы поняли с последних двух буковок функции, и получим нужную нам функцию. И теперь вы сможете делать выборку по 127 критериям, обалдеть, я даже не могу придумать, зачем мне, сколько критериев, хотя для вас это может стать панацеей.
Большим плюсом того как работает функция СУММЕСЛИМН в Excel, это работа с символами подстановки, а также с операторами отношений, типа «больше», «меньше», «равно». Также не стоить забывать, что для удобства работы с функцией стоить использовать абсолютные ссылки, что позволит вам более удобно использовать столь полезную функцию.
В целом при работе с большими массивами данных функция СУММЕСЛИМН будет являться для вас неоценимым помощником.
Детально о том, как работает функция нашего топ списка вы можете ознакомиться здесь.
Функция СУММПРОИЗВ в Excel
Пятой функцией нашего топ-списка станет функция СУММПРОИЗВ, которая является, наверное, даже наиглавнейшей функцией для экономиста. Она позволяет воплотить в себе практически все предыдущее возможности, которые имеют функции ЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, а также производить свои вычисление в 255 массивах, а это, я вам скажу, ох как много.
Функция СУММПРОИЗВ в Excel позволит вам справится практически с любой экономической задачей, где фигурируют массивы. Подобрав правильные критерии или условия, с помощью формул или иным способом, любые задачи смогут капитулировать перед легкостью, с которой эта функция будет их решать.
Не стоит заблуждаться ее кажущейся простотой или запутанным описанием, это отличный инструмент о котором вы должны знать и уметь им пользоваться, это сократить ваше время и сбережёт километры нервных клеток.
Детально о том, как работает функция СУММПРОИЗВ в Excel вы можете ознакомиться здесь.
Не забудьте поблагодарить автора!
То не беда, если за рубль дают полрубля; а то будет беда, когда за рубль станут давать в морду.
Михаил Салтыков-Щедрин
7 функций Excel для экономиста: простые примеры для сложных задач
Вычислительные функции — это сердцевина Excel и то, что, по-сути, отличает программу от обычного калькулятора. Функций в программе довольно много — более 400. Часть из них дублируется, но тем не менее, возможности для экономических расчетов в программе огромные.
С помощью формул можно безошибочно рассчитать себестоимость товара, подготовить аналитические отчеты, свести планы. Решить как простые экономические задачи учета, так и составить полный баланс предприятия. Подборку инструментов Excel для бухгалтера мы уже делали. Настал черед рассмотреть основные функции Excel для экономиста, которые упростят работу с данными и большими таблицами.
Что такое функции Excel и где они находятся
Функции в Excel — это команды, которые помогают в считанные минуты откорректировать данные в ячейках, рассчитать значения по определенным критериям, изъять нужную информацию из огромного массива. Функции часто используют в формулах, чтобы их упростить и сделать более гибкими.
Как мы уже говорили, в программе функций много — около 10 категорий: есть математические, логические, текстовые. И специальные функции — финансовые, статистические и пр. Все функции лежат во вкладке «Формулы». Перейдя в нее, нужно нажать на кнопку «Вставить функцию» на панели инструментов, после чего запустится «Мастер функций».
Останется выбрать в «Категориях» нужную функцию для вычислений и заполнить данные по структуре в выпадающем окошке.
Если вы знаете, какая функция вам нужна, просто введите ее в строке формул, начиная со знака «=». Как только ввели, нажмите «Enter» для вычисления.
Запоминать, что делает каждая функция не нужно. В Excel есть удобные подсказки, зачем нужна та или иная формула, и как ее использовать.
Ниже рассмотрим основные и часто используемые формулы в Excel для экономистов: ЕСЛИ, СУММЕСЛИ, ВПР, СУММПРОИЗВ, СЧЁТ, СРЗНАЧ и МАКС/МИН.
Функция ЕСЛИ для сравнения данных
Самая популярная логическая функция, которая полезна экономистам, бухгалтерам. С ее помощью можно сравнить числовые и текстовые значения по определенным критериям. Например, рассчитать амортизацию, скидки, наценку или убрать ошибки из расчетов. Иногда используется встроенная функция ЕСЛИ в статистических, текстовых и математических формулах, что наделяет их способностью «принимать решения».
Функция ЕСЛИ помогает точно сравнить значения и получить результат, в зависимости от того, истинно сравнение или нет.
Так выглядит формула:
=ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь])
Например, магазин торгует аксессуарами для мужчин и женщин. В текущем месяце на все женские товары скидка 20%. Отсортировать акционные позиции можно с помощью функции ЕСЛИ для текстовых значений.
Пропишем формулу в столбце «Скидка» так:
=ЕСЛИ(B2=»женский»;20%;0)
И применим ко всем строкам. В ячейках, где равенство выполняется, увидим товары по скидке.
Так применяется функция ЕСЛИ для текстовых значений с одним условием
Функции СУММЕСЛИ и СУММЕСЛИМН
Еще одна полезная функция СУММЕСЛИ, которая позволяет просуммировать несколько числовых данных по определенному критерию. Состоит формула из 2-х частей:
Например, вам нужно просчитать общую сумму начисленной заработной платы менеджерам, а в отчете указаны данные по всем сотрудникам предприятия.
В формуле нужно прописать такие аргументы:
И получаем в один клик общую сумму заработной платы менеджеров:
С помощью СУММЕСЛИ можно просуммировать ячейки, которые соответствуют определенному критерию
Важно! Функция СУММЕСЛИ чувствительна к правильности и точности написания критериев. Малейшая опечатка может дать неправильный результат. Это также касается названий ячеек. Формула выдаст ошибку, если написать диапазон ячеек кириллицей, а не латиницей.
Более сложный вариант этой формулы — функция СУММЕСЛИМН. По-сути, это выборочное суммирование данных, отобранных по нескольким критериям. В отличие от СУММЕСЛИ, можно использовать до 127 критериев отбора данных. Например, с помощью этой формулы легко рассчитать суммарную прибыль от поставок разных товаров сразу в несколько стран.
В функции СУММЕСЛИМН можно работать с подстановочными символами, использовать операторы для вычислений типа «больше», «меньше» и «равно». Для удобства работы с функцией лучше применять абсолютные ссылки в Excel — они не меняются при копировании и позволяют автоматически пересчитать формулу, если данные в ячейке изменились.
Функции ВПР и ГПР — поиск данных в большом диапазоне
Экономистам часто приходится обрабатывать огромные таблицы, чтобы получить необходимые данные для анализа. Или сводить две таблицы в одну, что тоже не редкость. Функция ВПР или, как ее еще называют, вертикальный просмотр (англ. вариант VLOOKUP) позволяет быстро найти и извлечь нужные данные в столбцах. Либо перенести данные из одной таблицы в соответствующие ячейки другой.
Синтаксис самой простой функции ВПР выглядит так:
= ВПР(искомое_значение; таблица; номер_столбца; [интервальный просмотр]).
Например, вам нужно быстро извлечь наименование товара по номеру в списке. С помощью функции ВПР это сделать очень просто:
Функция ВПР позволяет быстро найти нужные данные и перенести их в выделенную ячейку.
В ячейке С1 мы указали номер товара. Потом выделили диапазон ячеек, где его искать (A1:B10) и написали номер столбца «2», в котором нужно взять данные. Нажали Enter и получили нужный товар в выделенной ячейке.
Есть менее популярная функция ГПР. Она работает также, как ВПР, но ищет данные в горизонтальных списках.
Важно! Чтобы увеличить область применения этой функции, можно задать данные с помощью подстановочных знаков.
Одна из задач этой функции — упростить подготовку исходных данных для аналитических и сводных таблиц. Как подготовить такую таблицу можете почитать бесплатно в книге Сводные таблицы в Excel.
Функция СУММПРОИЗВ в Excel
Четвертая функция нашего списка — СУММПРОИЗВ или суммирование произведений. Поможет быстро справиться с любой экономической задачей, где есть массивы. Включает в себя возможности предыдущих формул ЕСЛИ, СУММЕСЛИ и СУММЕСЛИМН, а также позволяет провести расчеты в 255 массивах. Ее любят бухгалтеры и часто используют при расчетах заработной платы и других расходов.
Что в данном случае может быть произведениями? Как заданные диапазоны, так и целые массивы данных. Чтобы задать количество массивов, их нужно перечислить через точку с запятой, потом перемножить, а затем просуммировать. При этом, есть одно условие — массивы должны быть одинаковые по типу и длине.
Например, у вас есть месячный отчет по продажам в магазине, из которого нужно узнать, на какую сумму продали футболок размера «М».
Для этого используем функцию СУММПРОИЗВ и указываем 2 условия. Каждое из них берем в скобки, а между ними ставим «звездочку», которая в Excel читается как союз «и».
Запишем команду так: =СУММПРОИЗВ((A5:A11=A13)*(B5:B11=B13)*C5:C11), где
С помощью функции СУММПРОИЗВ мы узнали за пару минут, что в магазине за месяц продали футболок М-размера на 100 у.е.
Чтобы узнать продажи по другому товару и размеру, нужно просто заменить данные в ячейках A13 и B13.
Данный пример слишком прост, по сравнению с тем, с какими объемами данных приходится работать экономистам. Но зная, что умеет данная формула, уже не так страшны таблицы в миллионы строк и сотни столбцов, а нужные данные находятся за считанные минуты.
Как применить МАКС, ВПР и ПОИСКПОЗ для решения задач
Функции МИН и МАКС помогают найти наименьшее или наибольшее значение данных. Функция ПОИСКПОЗ помогает найти номер указанного элемента в выделенном диапазоне. А формула ВПР, напомним, позволяет извлечь нужные данные из столбцов в указанные ячейки.
Рассмотрим комбинацию этих функций на примере решения задачи в Excel для экономистов. Составим комбинированную формулу, чтобы найти компанию с наибольшей суммой задолженности.
Для начала составим список должников, укажем сумму их задолженности и выставим дату окончания кредитного договора.
Для решения задачи, можно применить функции последовательно:
Составляем функцию по формуле:
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
В нашем случае это будет =ПОИСКПОЗ(14569;C2:C10;0), где искомое — максимальная сумма долга. Тип сопоставления будет “0”, потому что к столбцу с долгами мы не применяли сортировку.
Выглядеть она будет так =ВПР(D14;A2:B10;2), где D4 — искомое, A2:B10 — таблица или выделенный диапазон с названиями компаний и нумерацией, а “2” — номер столбца с должниками.
Этот же результат можно было получить, собрав одну формулу из 3-х:
=ВПР (ПОИСКПОЗ (МАКС (C2:C10); C2:C10;0); A2:B10;2).
В экономических расчетах функция ВПР помогает быстро извлечь нужное значение из огромного диапазона данных. Причем значение можно найти по разным критериям отбора. Например, цену товара можно извлечь по идентификатору, налоговую ставку — по уровню дохода и пр.
Кроме вышеупомянутых функций, экономисты часто используют формулу СРЗНАЧ, например, для расчета средней заработной платы. Функцию СЧЁТ, когда нужно рассчитать количество отгрузок в разрезе клиентов или стоимости товара за определенный период. Кстати, на примере отгрузок, формула МИН/МАКС поможет отследить диапазон, в котором изменялась стоимость товара.
Существует еще сотня математических и финансовых функций в Ехсеl, с помощью которых можно решить экономические задачи разной сложности. Например, обрабатывать заказы, планировать загрузку производства, рассчитывать налоги, вести кадровый учет и управлять сбытом. Но для решения подобных задач, подсказок в Excel будет уже недостаточно. Тут необходимо глубже погружаться в программу и учиться работать с разными ее инструментами: от функций и сводных таблиц, до диаграмм и макросов.
Что нужно уметь экономисту в эксель
Таблицы Excel — очень мощный инструмент. В них больше 470 скрытых функций. Поначалу это пугает: кажется, на то, чтобы разобраться со всем, уйдут годы. На самом деле это не так. Всего десятка функций и горячих клавиш уже хватит для того, чтобы сильно упростить себе жизнь. Расскажем о некоторых из них (скоро стартует второй поток курса «Магия Excel»).
Интерфейс
Настраиваем панель быстрого доступа
Начнем с самого простого — добавления самых часто используемых опций на панель быстрого доступа. Чтобы сделать это, заходите в параметры Excel — «Настроить ленту» — и ищите в параметрах «Панель быстрого доступа».
Опции, перенесенные на панель быстрого доступа, будут доступны при работе со всеми вашими книгами Excel (хотя можно ее настроить и отдельно для любой книги). Так что если пользуетесь какими-то командами и инструментами постоянно — добавляйте их туда.
Другой вариант — просто щелкнуть по инструменту на ленте правой кнопкой мыши и нажать «Добавить…»:
Перемещаемся по ленте без мышки
Нажмите на Alt. На ленте инструментов появились цифры и буквы — у каждого инструмента на панели быстрого доступа и у каждой вкладки на ленте соответственно:
Нажмите на клавиатуре любую из букв — попадете на соответствующую вкладку на ленте, а там каждый инструмент в свою очередь тоже будет подписан. Так можно быстро вызвать нужные опции, не трогая мышку.
Ввод данных
Теперь давайте рассмотрим несколько инструментов для быстрого ввода данных.
Автозамена
Если вам часто нужно вводить какое-то словосочетание, адрес, емейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах:
Прогрессия
Если нужно заполнить столбец или строку последовательностью чисел или дат, введите в ячейку первое значение и затем воспользуйтесь этим инструментом:
Протягивание
Представьте, что вам нужно извлечь какие-то данные из целого столбца или переписать их в другом виде (например, фамилию с инициалами вместо полных ФИО). Задайте Excel одну ячейку с образцом — что хотите получить:
Выделите все ячейки, которые хотите заполнить по образцу, — и нажмите Ctrl+E. И магия случится (ну, в большинстве случаев).
Проверка ошибок
Проверка данных позволяет избежать ошибок при вводе информации в ячейки.
Какие бывают типовые ошибки в Excel?
Инструмент проверки данных
Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:
Если в графе «Сообщение об ошибке» вы выбрали вариант «Остановка», то после проверки в ячейки нельзя будет ввести значения, не соответствующие заданному правилу.
Если же вы выбрали «Предупреждение» или «Сообщение», то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.
Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:
Удаление пробелов
Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:
Дата и время
За любой датой в Excel скрывается целое число. Датой его делает формат.
Аналогично со временем: одна единица — это день, а часть единицы (число от 0 до 1) — время, то есть часть дня.
Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:
Вычесть из одной даты другую, чтобы получить разницу в днях (результатом вычитания будет число — количество дней.
Прибавить к дате число — и получить дату, которая наступит через соответствующее количество дней.
Поиск и подстановка значений
Функция ВПР / VLOOKUP
Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).
=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])
У нее есть два режима работы: интервальный просмотр и точный поиск.
Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).
В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.
Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX
У ВПР есть существенный недостаток: ключ (искомое значение) обязан быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР «подтянуть» невозможно.
Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:
=ПОИСКПОЗ (что ищем; где ищем ; 0)
На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).
ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.
=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)
Соответственно, мы можем определить номер строки, в котором находится искомое значение, с помощью ПОИСКПОЗ. А затем подставить этот номер в ИНДЕКС на место второго аргумента, чтобы получить данные из любого нужного нам столбца.
Получается следующая конструкция:
=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))
Оформление
Нужно оформить ячейки в книге Excel в едином стиле? Для этого есть одноименный инструмент — «Стили».
На ленте инструментов нажмите на «Стили ячеек» и выберите подходящий. Он будет применен к выделенным ячейкам:
А самое главное — если вы применили стиль ко многим ячейкам (например, ко всем заголовкам на 20 листах книги Excel) и захотели что-то переделать, щелкните правой кнопкой мыши и нажмите «Изменить». Изменения будут применены ко всем нужным ячейкам в документе.
На курсе «Магия Excel» будет два модуля — для новичков и продвинутых. Записывайтесь →