Полезные функции в Excel
И, ИЛИ, ЕСЛИ, НЕ, ИСТИНА, ЛОЖЬ — каждая функция полезна, если нам требуется сделать какой-то вывод об истинности или ложности. Реальный пример: считаем сумму в 30 столбцах и если мы везде в плюсе, то выводим ИСТИНА и идем праздновать, а если где-то оказались в минусе, то выводим эту ячейку и смотрим, где же у нас проблемы.
Самые простые функции уже есть на сайтах банков и финансовых организаций (кредитный калькулятор, калькулятор вкладов и т.д.).
Поэтому имеет смысл изучать что-то сложнее (особенно, если планируете работать финансистом или бухгалтером).
Самые полезные функции Excel
Среди многих функций Excel есть некоторые скрытые функции, которые просты в использовании, и многие из них не знают их всех. Без дальнейших задержек мы рассмотрим 5 таких функций Excel.
Sparklines
Sparklines были впервые представлены в Excel 2010 и используются для представления визуализации для тренда по данным в строке. Он помещается в одну ячейку Excel и сохраняет пробел на листе. Это классная функция и очень проста в использовании. Вычисление тенденции для данных о строках и размещение визуализации в единственном excel действительно отличная функция для использования.
Чтобы создать собственные Sparklines, выберите диапазон данных. Нажмите вставить на ленте и выберите тип Sparklines (Line, Column или Win / Loss). Затем введите диапазон цели, где вы хотите показать Sparklines. Для получения дополнительной информации о том, как создавать Sparklines, посетите Office Blogs.
Условное форматирование
Условное форматирование — хорошо известная функция Excel. Он используется для визуального представления данных на основе выполненных условий. Также полезно создавать карты тепла. Было бы полезно найти интересные шаблоны, эффективно изучив данные.
Чтобы создать карту тепла, выберите данные и перейдите к ленте. Под Главная, щелкните Условное форматирование и нажмите Цветные весы. Теперь выберите цветовую гамму. Вы можете даже установить цветовую шкалу, отредактировав правило форматирования. Дополнительные сведения об условном форматировании см. В разделе «Поддержка Office».
МАЛЫЕ и БОЛЬШИЕ функции
Мы все знаем о функциях MAX и MIN. Они дают вам максимальные и минимальные значения выбранных данных соответственно. Но, чтобы найти 1 улица , 2 й , 3 й или nth наибольшее или наименьшее значение выбранного диапазона, если данные, мы можем использовать функции LARGE и SMALL соответственно.
В этом примере, чтобы найти два лучших продукта за каждый месяц, мы использовали функции MATCH и INDEX вместе с функциями LARGE и SMALL. Для получения дополнительной информации посетите МАЛЫЕ и БОЛЬШИЕ функции.
Удалить дубликаты
Не обвиняйте меня в упоминании этой функции в этом списке. Очень важно избавиться от избыточных данных из имеющегося огромного количества данных. Это один из лучших способов очистки и организации данных, и поэтому он думал о том, что в этом списке мощных функций Excel. Функция «Удаление дубликатов» была введена из Excel 2007 и полезна для удаления дубликатов, что является самой важной проблемой, с которой мы сталкиваемся.
Чтобы удалить дубликаты, выберите данные и перейдите к ленте. Под Данные, нажмите Удалить дубликаты и что вы видите данные без дубликатов. Дополнительные сведения о поиске и удалении дубликатов см. В Office.com.
Срезы
Слайсеры действуют как визуальные фильтры. Это помогает вам визуализировать подмножество данных как подключенную диаграмму или как необработанные данные. Например, если вы хотите показать тенденцию продаж различных продуктов, вы можете создать интерактивную диаграмму трендов продаж, используя Slicers. На основе выбранного вами продукта отображается соответствующая диаграмма. Слайсеры были впервые представлены в Excel 2010 и значительно улучшились в Excel 2013.
В Excel 2013, если вы хотите добавить Slicer в свои диаграммы, выберите диапазон данных и нажмите insert> Slicer. Теперь выберите часть данных, которые вы хотите использовать в качестве фильтра. На изображении выше столбец Product используется как фильтр. Как здесь для получения дополнительной информации о том, как использовать Slicers.
Для получения дополнительной информации об этих функциях и загрузки примера рабочей книги посетите Office Blogs
Сколько из вас использовали эти мощные и полезные функции Excel? Если вы хотите добавить дополнительные функции в список, сообщите нам об этом через комментарии.
Ввиду широкого функционального предназначения программы пользователь далеко не всегда знает о возможностях, которые упрощают взаимодействие со многими инструментами Экселя. Далее в статье мы расскажем о 10 лучших функциях, которые могут пригодиться в разных целях, а также предоставим ссылки на подробные уроки по работе с каждой из них.
Функция «ВПР»
Одной из самых востребованных функций в Microsoft Excel является «ВПР» («VLOOKUP)». Задействовав ее, можно перетягивать значения одной или нескольких таблиц в другую. При этом поиск производится только в первом столбце таблицы, тем самым при изменении данных в таблице-источнике автоматически формируются данные и в производной таблице, в которой могут выполняться отдельные расчеты. Например, сведения из таблицы, в которой находятся прейскуранты на товары, могут использоваться для расчета показателей в таблице об объеме закупок в денежном выражении.
ВПР запускается путем вставки оператора «ВПР» из «Мастера функций» в ту ячейку, где данные должны отображаться.
В появившемся окне после запуска этой функции нужно указать адрес ячейки или диапазона ячеек, откуда данные будут подтягиваться.
Сводные таблицы
Еще одной важной функцией программы Excel является создание сводных таблиц, обеспечивающее группировку данных из других таблиц по различным критериям, выполнение различных расчетов с ними (суммирование, умножение, деление и т.д.), а результаты выводятся в отдельную таблицу. При этом существуют очень широкие возможности по настройке полей сводной таблицы.
Создается она на вкладке «Вставка» нажатием на кнопку, которая так и называется — «Сводная таблица».
Создание диаграмм
Для визуального отображения данных, размещенных в таблице, удобно использовать диаграммы. Их часто применяют для создания презентаций, написания научных работ, в исследовательских целях и т.д. Excel предоставляет широкий набор инструментов по созданию различного типа диаграмм.
Чтобы создать диаграмму, нужно выделить набор ячеек с данными, которые вы хотите визуально отобразить. Затем, находясь на вкладке «Вставка», выбрать на ленте тот тип диаграммы, который считаете наиболее подходящим для достижения поставленных целей.
Более точная настройка диаграмм, включая установку ее наименования и наименования осей, производится в группе вкладок «Работа с диаграммами».
Одним из видов диаграмм являются графики. Принцип построения их тот же, что и у остальных типов диаграмм.
Формулы в Excel
Для работы с числовыми данными в программе позволяется использовать специальные формулы. С их помощью можно производить различные арифметические действия с данными в таблицах: сложение, вычитание, умножение, деление, возведение в степень извлечение корня и т.д. Чтобы применить формулу, нужно в ячейке, куда планируется выводить результат, поставить знак «=». После этого вводится сама формула, которая может состоять из математических знаков, чисел и адресов ячеек. Для указания адреса ячейки, из которой берутся данные для расчета, достаточно кликнуть по ней мышкой, и ее координаты появится в ячейке для вывода результата.
Excel также удобно использовать и в качестве обычного калькулятора. Для этого в строке формул или в любой ячейке просто вводятся математические выражения после знака «=».
Функция «ЕСЛИ»
Одной из самых популярных функций, которые используются в Excel, является «ЕСЛИ». Она дает возможность задать в ячейке вывод одного результата при выполнении конкретного условия и другого результата в случае его невыполнения. Ее синтаксис выглядит следующим образом: ЕСЛИ(логическое выражение; [результат если истина]; [результат если ложь]) .
Операторами «И», «ИЛИ» и вложенной функцией «ЕСЛИ» задается соответствие нескольким условиям или одному из нескольких условий.
Макросы
С помощью макросов в программе записывается выполнение определенных действий, а потом они воспроизводятся автоматически. Это существенно экономит время на выполнении большого количества однотипной работы. Макросы записываются путем включения захвата своих действий в программе через соответствующую кнопку на ленте.
Запись макросов также можно производить, используя язык разметки Visual Basic в специальном редакторе.
Условное форматирование
Чтобы выделить определенные данные, в таблице применяется функция условного форматирования, позволяющая настроить правила выделения ячеек. Само условное форматирование позволяется выполнить в виде гистограммы, цветовой шкалы или набора значков. Переход к ней осуществляется через вкладку «Главная» с выделением диапазона ячеек, который вы собираетесь отформатировать. Далее в группе инструментов «Стили» нажмите кнопку, имеющая название «Условное форматирование». После этого останется выбрать тот вариант форматирования, который считаете наиболее подходящим.
Форматирование будет выполнено.
«Умная» таблица
Не все пользователи знают, что таблицу, просто начерченную карандашом или границами, Excel воспринимает как простую область ячеек. Заставить программу видеть этот набор данных как таблицу можно через переформатирование. Делается это просто: для начала выделяем нужный диапазон с данными, а затем, находясь на вкладке «Главная», кликаем по кнопке «Форматировать как таблицу». Появится список с различными вариантами стилей оформления, где укажите подходящий.
Таблица также создается нажатием на кнопку «Таблица», которая расположена на вкладке «Вставка», предварительно выделив определенную область листа с данными.
Выделенный набор ячеек редактор будет воспринимать как таблицу. Вследствие этого, к примеру, если вы введете в расположенные у границ таблицы ячейки какие-то данные, они будут автоматически включены в нее. Вдобавок к этому при прокрутке вниз шапка будет постоянно в пределах области зрения.
Подбор параметра
С помощью функции подбора параметров можно подобрать исходные данные, руководствуясь желаемым для вас результатом. Перейдите на вкладку «Данные» и нажмите кнопку «Анализ «что если»», расположенную в блоке инструментов «Работа с данными». В появившемся списке укажите пункт «Подбор параметра…».
Откроется окно подбора параметра. В поле «Установить в ячейке» вы должны указать ссылку на ячейку, которая содержит нужную формулу. В поле «Значение» должен быть указан конечный результат, который вы хотите получить. В поле «Изменяя значения ячейки» вставьте координаты ячейки с корректируемым значением.
Функция «ИНДЕКС»
Возможности, которые предоставляет функция «ИНДЕКС», в чем-то близки к возможностям функции «ВПР». Она также позволяет искать данные в массиве значений и возвращать их в указанную ячейку. Синтаксис выглядит следующим образом: ИНДЕКС(диапазон_ячеек;номер_строки;номер_столбца) .
Это далеко не полный перечень всех функций, которые доступны в программе Microsoft Excel. Мы остановили внимание только на самых популярных и наиболее важных из них.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 12369 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Рассмотрим несколько простых, но нетривиальных и изящных инструментов, с помощью которых можно работать с большими объемами данных в 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
Среди многих функций 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, поскольку подразумевает не добавление их в таблицу, а непосредственное составление таблицы по формуле, чтобы затем сформировать из нее диаграмму либо линейный график. Сейчас детально останавливаться на этой теме не будем, но если она вас интересует, перейдите по ссылке ниже для прочтения другой моей статьи по этой теме.
В этой статье вы узнали, какие есть функции в Excel, как сделать формулу и использовать полезные возможности программы, делающие процесс взаимодействия с электронными таблицами проще. Применяйте полученные знания для самостоятельной практики и поставленных задач, требующих проведения расчетов и их автоматизации.
ИНДЕКС
Эта функция возвращает значение конкретной ячейки в указанном диапазоне:
=ИНДЕКС(диапазон; номер_строки; [номер_столбца])
Если в диапазон ячеек входит только один столбец, достаточно будет указать только номер строки. Когда в выбранном диапазоне получается два столбца и больше, можно будет выбрать его номер, чтобы точно обозначить ячейку.
Попробуем в нашем примере с бюджетом найти того, кто сделал самую большую покупку. Мы уже знаем номер строки, в которой записана самая большая сумма, и на этой же строке записан тот, кто эту сумму потратил. Используем это:
Когда Excel встретит такую формулу, он возьмёт наш диапазон из второго столбца, затем возьмёт номер строки с самой большой покупкой (она у нас в ячейке Е10, нашли в прошлом примере), и определит, что самую крупную покупку сделал Миша.
