Мощные и полезные функции Excel, которые вам нужно знать

Мощные и полезные функции Excel, которые вам нужно знать

Майкрософт Эксель является самым полезным и легким инструментом для бизнес-аналитиков. Он имеет большое количество полезных формул, функций и пакетов интерактивных диаграмм. Но большинство из нас не знакомы со всеми из них, и есть еще несколько функций, которые являются мощными и простыми в использовании, чтобы упростить нашу работу. Возможно, вы не заметили некоторые полезные функции Excel 2013, такие как Sparklines, Slicers, Условное форматирование и другие формулы, которые повышают ценность вашей работы. В этой статье я проведу вас через них и дам вам представление о том, что это такое и как их использовать.

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

Excel обладает массой функций

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

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

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

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

ПОИСКПОЗ

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

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

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

С помощью функции ВПР этого не сделать, потому что она ищет только слева направо. А сочетание двух последних позволяет сделать это очент просто.

3D-сумма

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

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

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

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

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

Массивы

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

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

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

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

Знание этих приёмом поможет вам сэкономить кучу времени при работе с электронными таблицами.

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

Функция «ВПР»

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

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

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

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

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

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

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

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

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

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

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

Формулы в Excel

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

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

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

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

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

Макросы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12369 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Для чего применяют Excel в работе

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

Рабочая область листа Excel включает множество ячеек. Нумерация строк и буквенное обозначение столбцов определяют координаты каждой ячейки. При её выделении координаты будут подсвечены цветом.

Существует много вариантов применения таблиц:

• Администрирование. Работа со списками и базами данных в приложении позволить быстро вносить и сортировать сведения в таблицах, делать выборки и отчёты.
• Бухгалтерия. Excel может работать в комплексе с другими специализированными разработками. Приложение даёт возможность делать различные формы документов, производить расчёты с коэффициентами, создавать шаблоны для повторяемых операций.
• Экономика и финансовая аналитика. Функционал приложения включает формулы для расчётов пени, неустойки, ссуды, цен с учётом инфляции. Реализует анализ данных любой сложности, выводит прогнозы и отчёты.
• Банковское дело. Excel имеет целый комплексный подход к банковским подсчётам – выводит кредитные процентные ставки, считает доход банка для кредита с заданной суммой и ежемесячные выплаты и сроки погашения.
• Торговля. Софт визуально формирует клиентскую базу, группирует ассортимент товаров, осуществляет создание прайс-листов, накладных, ведёт учёт и рассчитывает программы лояльности.
• Статистика. Сравнение и обработка данных. Глубокий и специализированный анализ огромных объёмов информации.

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

Самые полезные функции Excel

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

Sparklines

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

Чтобы создать свои собственные Sparklines, выберите диапазон данных. Нажмите вставить на ленте и выберите тип Спарклайны (линия, столбец или выигрыш/проигрыш). Затем введите диапазон цели, где вы хотите показать Sparklines. Для получения дополнительной информации о том, как создать Sparklines, посетите Офисные блоги.

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

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

Чтобы создать тепловую карту, выберите данные и перейдите на ленту. В разделе Главная нажмите Условное форматирование , а затем нажмите Цветовые шкалы. Теперь выберите цветовую гамму. Вы даже можете установить цветовую шкалу, отредактировав правило форматирования. Для получения дополнительной информации об условном форматировании посетите веб-сайт поддержки Office.

МАЛЫЕ и БОЛЬШИЕ функции

Мы все знаем о функциях MAX и MIN. Они дают вам максимальные и минимальные значения выбранных данных соответственно. Но, чтобы найти 1-е, 2-е, 3-е или n-е наибольшее или наименьшее значение выбранного диапазона данных, мы можем использовать функции LARGE и SMALL соответственно.

В этом примере, чтобы найти два лучших продукта для каждого месяца, мы использовали функции MATCH и INDEX вместе с функциями LARGE и SMALL. Для получения дополнительной информации посетите МАЛЕНЬКИЕ и БОЛЬШИЕ функции.

Удалить дубликаты

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

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

Срезы

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

В Excel 2013, если вы хотите добавить Slicer в свои диаграммы, выберите диапазон данных и нажмите insert> Slicer. Теперь выберите часть данных, которую вы хотите использовать в качестве фильтра. На изображении выше столбец Product используется в качестве фильтра. Как здесь для получения дополнительной информации о том, как использовать слайсеры.

Для получения более подробной информации об этих функциях и для загрузки учебного пособия в качестве примера посетите раздел «Блоги Office»

Кто из вас использовал эти мощные и полезные функции Excel? Если вы хотите добавить больше функций в список, сообщите нам об этом в комментариях.

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

CHOOSE (ВЫБОР)

На основании индекса возвращает значение из списка аргументов. Используют функцию «CHOOSE» для выбора одного максимум из 254 значений на основании номера индекса. Сначала может показаться, что это обычная функция, ведь все, что она делает — выбирает элемент в своем списке на основе номера индекса. К тому же есть еще и функция «INDEX», которая делает то же, но если комбинировать «ВЫБОР» с другими формулами, она может быть довольно мощной.

Пример

Тривиальным примером использования данной функции является возвращение разных значений, основанных на условиях. Например, есть список фамилий и их оценки (1); нужно разделить их на основе определенных значений (2) и вывести полученные данные в поле «Результат».

Чтобы решить эту задачу, достаточно использовать функцию «CHOOSE» следующим образом:

Функции группы LOOKUP (ПРОСМОТР)

Функции группы «LOOKUP» (VLOOKUP, HLOOKUP) по праву можно назвать одними из самых важных и полезных. Они помогают найти данные в определенном столбце, строке или таблице. «VLOOKUP (ВПР)» позволяет искать значение в столбце («V» — по вертикали) и возвращать другое значение из той же строки. «HLOOKUP (ГПР)» позволяет искать значение в строке («H» — по горизонтали) и возвращать другое значение из того же столбца. Довольно удобные и часто используемые, так как сопоставить вручную обширные диапазоны с десятками тысяч наименований проблематично.

Пример

В самом простом виде использование «ВПР» можно представить следующим образом. Пусть в некой таблице имеется список сотрудников (фамилия и идентификатор) и необходимо вытянуть информацию об имени сотрудника с фамилией Сидоров.

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

IFERROR (ЕСЛИОШИБКА)

«ЕСЛИОШИБКА» в Microsoft Excel используется для обработки ситуаций, когда в результате работы формулы возникла некая ошибка. Работая с этой функцией, можно указать, какое именно значение должно возвращаться с помощью «IFERROR» вместо полученной ошибки. Если формула в ячейке не возвращает ошибку, вместо этого возвращается результат.

Пример

Простым примером использования «IFERROR» является замена ошибки в ячейке табличного процессора на значения, которые сопоставляются ошибке.

В данном случае стандартная ошибка, вызванная делением на 0 (числа первого столбца делятся на второй) заменяется на сообщение «Деление на 0» с помощью функции «ЕСЛИОШИБКА».
=IFERROR(A2/B2; «Деление на 0»)

SUMPRODUCT (СУММПРОИЗВ)

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

Пример

Хорошим примером использования функции «СУММПРОИЗВ» является расчет объемов продаж определенной категории товара по заданным параметрам (категория, размер).

XIRR (ЧИСТВНДОХ)

«XIRR» — финансовая функция MS Excel, используемая для вычисления внутренней ставки доходности по определенному ряду нерегулярных потоков денег (для постоянных потоков используется функция «ВСД»).

Пример

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

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

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

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

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

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

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

• Найти самый крупный долг поможет функция МАКС (=МАКС(B2:B10)), где B2:B10 — столбец с данными по задолженности.

• Чтобы найти номер компании-должника в списке, нужно в таблицу добавить столбец с нумерацией. Так как функция ПОИСКПОЗ ищет данные только в крайнем левом столбце выделенного диапазона.

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

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

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

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

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

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

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

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