10 полезных функций программы Microsoft Excel. Полезные функции эксель для экономистов


Полезные функции Excel

Поолезные функции в Microsoft Excel

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

Функция ВПР

Одной из самых востребованных функций в программе Microsoft Excel является ВПР (VLOOKUP). С помощью данной функции, можно значения одной или нескольких таблиц, перетягивать в другую. При этом, поиск производится только в первом столбце таблицы. Тем самым, при изменении данных в таблице-источнике, автоматически формируются данные и в производной таблице, в которой могут выполняться отдельные расчеты. Например, данные из таблицы, в которой находятся прейскуранты цен на товары, могут использоваться для расчета показателей в таблице, об объёме закупок в денежном выражении.

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

Выбор функции ВПР в Microsoft Excel

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

Окончание введение аргументов в Microsoft Excel

Урок: Применение функции ВПР в Microsoft Excel

Сводные таблицы

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

Обмен областями в Microsoft Excel

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

Переход к созданию сводной таблицы в Microsoft Excel

Урок: Применение сводных таблиц в Microsoft Excel

Создание диаграмм

Для визуального отображения данных, размещенных в таблице, можно использовать диаграммы. Их можно применять в целях создания презентаций, написания научных работ, в исследовательских целях, и т.д. Программа Microsoft Excel предоставляет широкий набор инструментов для создания различного типа диаграмм.

Круговая диаграмма в Microsoft Excel постороена

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

Подвиды гистограмм в Microsoft Excel

Более точная настройка диаграмм, включая установку её наименования и наименования осей, производится в группе вкладок «Работа с диаграммами».

Изменение стиля диаграммы в Microsoft Excel

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

График функции создан в Microsoft Excel

Урок: Применение диаграмм в Microsoft Excel

Формулы в EXCEL

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

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

Арифметическое действие в Microsoft Excel

Также, программу Microsoft Excel можно использовать и в качестве обычного калькулятора. Для этого, в строке формул или в любой ячейки просто вводятся математические выражения после знака «=».

Использование Microsoft Excel в качестве калькулятора

Урок: Применение формул в Microsoft Excel

Функция «ЕСЛИ»

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

Синтаксис данной функции выглядит следующим образом «ЕСЛИ(логическое выражение; [результат если истина]; [результат если ложь])».

Результат функции ЕСЛИ в программе Microsoft Excel

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

Копирование функции ЕСЛИ с оператором ИЛИ в программе Microsoft Excel

Урок: Применение функции «ЕСЛИ» в Microsoft Excel

Макросы

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

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

Включение записи макроса в Microsoft Excel

Также, запись макросов можно производить, используя язык разметки Visual Basic, в специальном редакторе.

Microsoft Visual Basic в Microsoft Excel

Урок: Применение макросов в Microsoft Excel

Условное форматирование

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

Для того, чтобы перейти к условному форматированию, нужно, находясь во вкладке «Главная», выделить диапазон ячеек, который вы собираетесь отформатировать. Далее, в группе инструментов «Стили» нажать на кнопку, которая так и называется «Условное форматирование». После этого, нужно выбрать тот вариант форматирования, который считаете наиболее подходящим.

Создание условного форматирования в Microsoft Excel

Форматирование будет выполнено.

Условное форматирование в Microsoft Excel

Урок: Применение условного форматирования в Microsoft Excel

«Умная» таблица

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

Делается это просто. Для начала, выделяем нужный диапазон с данными, а затем, находясь во вкладке «Главная», кликаем по кнопке «Форматировать как таблицу». После этого, появляется список с различными вариантами стилей оформления таблицы. Выбираем наиболее подходящий из них.

Форматирование в Microsoft Excel

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

Переход к созданию таблицы в Microsoft Excel

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

Закрепление шапки в приложении Microsoft Excel

Урок: Создание таблицы в Microsoft Excel

Подбор параметра

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

Для того, чтобы использовать эту функцию, нужно находиться во вкладке «Данные». Затем, требуется нажать на кнопку «Анализ «что если»», которая располагается в блоке инструментов «Работа с данными». Потом, выбрать в появившемся списке пункт «Подбор параметра…».

Переход к подбору параметра в Microsoft Excel

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

Подбор параметра в Microsoft Excel

Урок: Применение подбора параметров в Microsoft Excel

Функция «ИНДЕКС»

Возможности, которые предоставляет функция «ИНДЕКС», в чем-то близки к возможностям функции ВПР. Она также позволяет искать данные в массиве значений, и возвращать их в указанную ячейку.

Синтаксис данной функции выглядит следующим образом: «ИНДЕКС(диапазон_ячеек;номер_строки;номер_столбца)».

Индекс в Microsoft Excel

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

Мы рады, что смогли помочь Вам в решении проблемы. Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

lumpics.ru

Excel для продвинутых: 9 полезных трюков

Кроме множества базовых приёмов работы с этой программой, которые Вы наверняка уже знаете, полезно изучить некоторые хитрости «для продвинутых», которые сделают Вас на голову выше всех остальных. Так что, если хотите впечатлить своих боссов и разгромить конкурентов, Вам пригодятся эти 8 хитрых функций Excel. 

1. Функция ВПР

Эта функция позволяет быстро найти нужное Вам значение в таблице. Например, нам нужно узнать финальный балл Бетт, мы пишем: =ВПР(“Beth",A2:E6,5,0), где Beth – имя ученика, A2:E6 – диапазон таблицы, 5 – номер столбца, а 0 означает, что мы не ищем точного соответствия значению. 

Функция очень удобна, однако нужно знать некоторые особенности её использования. Во-первых, ВПР ищет только слева направо, так что, если Вам понадобится искать в другом порядке, придется менять параметры сортировки целого листа. Также если Вы выберете слишком большую таблицу, поиск может занять много времени. 

2. Функция ИНДЕКС

Отражает значение или ссылку на ячейку на пересечении конкретных строки и столбца в выбранном диапазоне ячеек. Например, чтобы посмотреть, кто стал четвёртым в списке самых высокооплачиваемых топ-менеджеров Уолл-стрит, набираем: =ИНДЕКС(А3:А11, 4). 

3. Функция ПОИСКПОЗ

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне (Диапазон - две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными) ячеек и отражает относительную позицию этого элемента в диапазоне. 

По отдельности ИНДЕКС и ПОИСКПОЗ не особо полезны. Но вместе они могут заменить функцию ВПР. 

Например, чтобы в большой таблице найти, кто является главой Wells Fargo, пишем =ИНДЕКС(А3:А11,ПОИСКПОЗ(«Wells Fargo»,B3:B11,0). С помощью функции ВПР этого не сделать, потому что она ищет только слева направо. А сочетание двух последних позволяет сделать это с легкостью. 

4. 3D-сумма

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

Теперь на вкладке TOTAL (ИТОГО) нам нужно увидеть, сколько и в какой день Вы потратили за этот период. Набираем =СУММ('Week1:Week7'!B2), и формула суммирует все значения в ячейке B2 на всех вкладках. Теперь, заполнив все ячейки, мы выяснили, в какой день недели тратили больше всего, а также в итоге подбили все свои расходы за эти 7 недель. 

5. $

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

При этом знак $ перед «А» не даёт программе изменять формулу по горизонтали, а перед «1» – по вертикали. Если же написать «$A$1», то значение скопированной ячейки будет одинаковым в любом направлении. Очень удобный приём, когда приходится работать с большими базами данных. 

6. &

Если Вы хотите собрать все значения из разных ячеек в одну, Вы можете использовать функцию СЦЕПИТЬ. Но зачем набирать столько букв, если можно заменить их знаком «&». 

7. Массивы

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

Например, давайте перемножим две матрицы. Для этого используем функцию МУМНОЖ (Массив 1, Массив 2). Главное, не забудьте закрыть формулу круглой скобкой. Теперь нажмите сочетание клавиш Ctrl+Shift+Enter, и Excel покажет результат умножения в виде матрицы. То же самое касается и других функций, работающих с массивами, – вместо простого нажатия Enter для получения результата используйте Ctrl+Shift+Enter. 

8. Подбор параметра

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

Например, Вы занимаетесь продажами новой видеоигры, и Вам нужно узнать, сколько экземпляров Ваши менеджеры должны продать в третьем месяце, чтобы заработать 100 миллионов долларов. Для этого в меню «Инструменты» выберите функцию «Подбор параметра». Нам нужно, чтобы в ячейке Total revenue (Общая выручка) оказалось значение 100 миллионов долларов. В поле set cell (Установить в ячейке) указываем ячейку, в которой будет итоговая сумма, в поле to value (Значение) – желаемую сумму, а в by Changing cell (Изменяя значение ячейки) выберите ячейку, где будет отображаться количество проданных в третьем месяце товаров. И – вуаля! – программа справилась. Параметрами и значениями в ячейках можно легко манипулировать, чтобы получить нужный Вам результат.

9. Сводные таблицы

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

Сводная таблица – прекрасный способ превратить огромный бесполезный объём информации в удобные для работы данные в рекордно короткие сроки.

hr-portal.ru

10 самых часто используемых функций в Экселе: max_makhrov

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

Предлагаю 10 наиболее часто используемых мной функций в Экселе:

Кратко

English

Формула

Для чего

Ограничения (2003 Эксель)

1

сумм

SUM

сумм(число1;число2;…)

Суммирует аргументы.

от 1 до 30 аргументов.

2

суммесли

SUMIF

суммесли(диапазон; критерий; диапазон_суммирования)

Суммирует ячейки, удовлетворяющие заданному условию.

Длина сопоставляемых строк — не более 255 символов.

3

впр

VLOOKUP

впр(искомое_значение; таблица; номер столбца; интервальный_просмотр)

Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце.

4

правсимв

RIGHT

правсимв(текст; число_знаков)

Выдает самые правые знаки текстовой строки.

5

сцепить

CONCATENATE

сцепить(текст1; текст2;…)

Объединяет несколько текстовых элементов в один.

от 1 до 30 аргументов

6

левсимв

LEFT

левсимв(текст; количество_знаков)

Выдает нужное количество самых левых знаков в строке.

7

округл

ROUND

округл(число; число_разрядов)

Округляет число до указанного количества десятичных разрядов.

8

текст

TEXT

текст(значение; формат)

Форматирует число и преобразует его в текст.

9

счётесли

COUNTIF

счётесли(диапазон; критерий)

Подсчитывает количество непустых ячеек, удовлетворяющих заданному условию внутри диапазона.

10

если

IF

если(лог_выражение; значение_если_истина; значение_если_ложь)

Выполняет проверку условия.

До 7 функций ЕСЛИ могут быть вложены друг в друга.

max-makhrov.livejournal.com

Excel для продвинутых: 9 полезных трюков

1Microsoft Excel, пожалуй, лучшая офисная программа из когда-либо созданных. На ней держатся целые отрасли экономики, так что, виртуозно овладев этим инструментом, вы сразу заметите, что дела у вашего бизнеса идут в гору.

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

1. Функция  ВПР

2

3 4 Эта функция позволяет быстро найти нужное вам значение в таблице. Например, нам нужно узнать финальный балл Бетт, мы пишем: =ВПР(“Beth”,A2:E6,5,0), где Beth – имя ученика, A2:E6 – диапазон таблицы, 5 – номер столбца, а 0 означает, что мы не ищем точного соответствия значению.

Функция очень удобна, однако нужно знать некоторые особенности её использования. Во-первых, ВПР ищет только слева направо, так что, если вам понадобится искать в другом порядке, придется менять параметры сортировки целого листа. Также если вы выберете слишком большую таблицу, поиск может занять много времени.

2. Функция ИНДЕКС

5Отражает значение или ссылку на ячейку на пересечении конкретных строки и столбца в выбранном диапазоне ячеек.

Например, чтобы посмотреть, кто стал четвёртым в списке самых высокооплачиваемых топ-менеджеров Уолл-стрит, набираем: =ИНДЕКС(А3:А11, 4).

3. Функция ПОИСКПОЗ

6Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне(Диапазон. Две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными.) ячеек и отражает относительную позицию этого элемента в диапазоне.

Например, вот как можно найти позицию Bank of America в выбранном диапазоне ячеек.

Чем это полезно?

7 11 10 9 8По отдельности ИНДЕКС и ПОИСКПОЗ не особо полезны. Но вместе они могут заменить функцию ВПР.

Например, чтобы в большой таблице найти, кто является главой Wells Fargo, пишем =ИНДЕКС(А3:А11,ПОИСКПОЗ(«Wells Fargo»,B3:B11,0).С помощью функции ВПР этого не сделать, потому что она ищет только слева направо. А сочетание двух последних позволяет сделать это с легкостью.

4. 3D-сумма

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

13Теперь на вкладке TOTAL (ИТОГО) нам нужно увидеть, сколько и в какой день вы потратили за этот период. Набираем =СУММ(‘Week1:Week7’!B2), и формула суммирует все значения в ячейке B2 на всех вкладках.

14Теперь, заполнив все ячейки, мы выяснили, в какой день недели тратили больше всего, а также в итоге подбили все свои расходы за эти 7 недель.

155. $

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

16 20 19 18 17На рисунках выше четыре разных способа указать программе, что верхняя левая ячейка должна быть идентична ячейке А1. Теперь, если вы будете копировать эту формулу со знаком $ в ячейки ниже, её содержимое останется неизменным.

21

При этом знак $ перед «А» не даёт программе изменять формулу по горизонтали, а перед «1» – по вертикали. Если же написать «$A$1», то значение скопированной ячейки будет одинаковым в любом направлении.

22Очень удобный приём, когда приходится работать с большими базами данных.

6. &

24Если вы хотите собрать все значения из разных ячеек в одну, вы можете использовать функцию СЦЕПИТЬ. Но зачем набирать столько букв, если можно заменить их знаком «&», как в примере выше?

7. Массивы

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

26 27Например, давайте перемножим две эти матрицы. Для этого используем функцию МУМНОЖ(Массив 1, Массив 2). Главное, не забудьте закрыть формулу круглой скобкой. Теперь нажмите сочетание клавиш Ctrl+Shift+Enter, и Excel покажет результат умножения в виде матрицы. То же самое касается и других функций, работающих с массивами – вместо простого нажатия Enter для получения результата используйте Ctrl+Shift+Enter.

8. Подбор параметра

28

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

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

29 31 30Для этого в меню «Инструменты» выберите функцию «Подбор параметра». Нам нужно, чтобы в ячейке Total revenue (Общая выручка) оказалось значение 100 миллионов долларов. В поле set cell (Установить в ячейке) указываем ячейку, в которой будет итоговая сумма, в поле to value (Значение) – желаемую сумму, а в by Changing cell (Изменяя значение ячейки) выберите ячейку, где будет отображаться количество проданных в третьем месяце товаров. И – вуаля! – программа справилась. Параметрами и значениями в ячейках можно легко манипулировать, чтобы получить нужный вам результат.

9. Сводные таблицы

32Если вам нужно провести макроанализ и увидеть общую картину, а на руках у вас только бесконечные колонки цифр, то сводная таблица станет отличным способом оптимизировать и ускорить вашу работу.

33 36 35 34Для этого выберете в меню «Вставка» пункт «Сводная таблица». Введя нужные диапазоны, вы получите данные в виде удобной для восприятия таблицы. Вы легко можете изменять параметры отображения данных с помощью конструктора сводных таблиц, сравнивать и анализировать полученные числа и прочее, прочее.

Сводная таблица – прекрасный способ превратить огромный бесполезный объём информации в удобные для работы данные в рекордно короткие сроки.

Примечание: название функций и операций на русском языке указано в соответствии с русифицированной версией Microsoft Excel 2010.

ПОХОЖИЕ ЗАПИСИ

© Gearmix 2013 Права на опубликованный перевод принадлежат владельцам вебсайта gearmix.ru Все графические изображения, использованные при оформлении статьи принадлежат их владельцам. Знак охраны авторского права распространяется только на текст статьи. Использование материалов сайта без активной индексируемой ссылки на источник запрещено.

gearmix.ru