8 интересных функции в Excel
Microsoft Excel, пожалуй, лучшая офисная программа из когда-либо созданных. На ней держатся целые отрасли экономики, так что, виртуозно овладев этим инструментом, Вы сразу заметите, что дела у Вашего бизнеса идут в гору.
Кроме множества базовых приёмов работы с этой программой, которые Вы наверняка уже знаете, полезно изучить некоторые хитрости «для продвинутых», которые сделают Вас на голову выше всех остальных. Так что, если хотите впечатлить своих боссов и разгромить конкурентов, Вам пригодятся эти 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 (Изменяя значение ячейки) выберите ячейку, где будет отображаться количество проданных в третьем месяце товаров. И – вуаля! – программа справилась. Параметрами и значениями в ячейках можно легко манипулировать, чтобы получить нужный Вам результат.
Полезные функции Excel
Содержание
- Работа с функциями в Excel
- Функция «ВПР»
- Сводные таблицы
- Создание диаграмм
- Формулы в Excel
- Функция «ЕСЛИ»
- Макросы
- Условное форматирование
- «Умная» таблица
- Подбор параметра
- Функция «ИНДЕКС»
- Вопросы и ответы
Microsoft Excel способен в значительной мере облегчить пользователю работу с таблицами и числовыми выражениями, автоматизировав ее.
Ввиду широкого функционального предназначения программы пользователь далеко не всегда знает о возможностях, которые упрощают взаимодействие со многими инструментами Экселя. Далее в статье мы расскажем о 10 лучших функциях, которые могут пригодиться в разных целях, а также предоставим ссылки на подробные уроки по работе с каждой из них.
Функция «ВПР»
Одной из самых востребованных функций в Microsoft Excel является «ВПР» («VLOOKUP)». Задействовав ее, можно перетягивать значения одной или нескольких таблиц в другую. При этом поиск производится только в первом столбце таблицы, тем самым при изменении данных в таблице-источнике автоматически формируются данные и в производной таблице, в которой могут выполняться отдельные расчеты. Например, сведения из таблицы, в которой находятся прейскуранты на товары, могут использоваться для расчета показателей в таблице об объеме закупок в денежном выражении.
ВПР запускается путем вставки оператора «ВПР» из «Мастера функций» в ту ячейку, где данные должны отображаться.
В появившемся окне после запуска этой функции нужно указать адрес ячейки или диапазона ячеек, откуда данные будут подтягиваться.
Урок: Применение функции «ВПР» в Microsoft Excel
Сводные таблицы
Еще одной важной функцией программы Excel является создание сводных таблиц, обеспечивающее группировку данных из других таблиц по различным критериям, выполнение различных расчетов с ними (суммирование, умножение, деление и т.д.), а результаты выводятся в отдельную таблицу. При этом существуют очень широкие возможности по настройке полей сводной таблицы.
Создается она на вкладке «Вставка» нажатием на кнопку, которая так и называется — «Сводная таблица».
Урок: Применение сводных таблиц в Microsoft Excel
Создание диаграмм
Для визуального отображения данных, размещенных в таблице, удобно использовать диаграммы.
Их часто применяют для создания презентаций, написания научных работ, в исследовательских целях и т.д. Excel предоставляет широкий набор инструментов по созданию различного типа диаграмм.
Чтобы создать диаграмму, нужно выделить набор ячеек с данными, которые вы хотите визуально отобразить. Затем, находясь на вкладке «Вставка», выбрать на ленте тот тип диаграммы, который считаете наиболее подходящим для достижения поставленных целей.
Более точная настройка диаграмм, включая установку ее наименования и наименования осей, производится в группе вкладок «Работа с диаграммами».
Одним из видов диаграмм являются графики. Принцип построения их тот же, что и у остальных типов диаграмм.
Урок:
Применение диаграмм в Microsoft Excel
Построение графика в Microsoft Excel
Формулы в Excel
Для работы с числовыми данными в программе позволяется использовать специальные формулы. С их помощью можно производить различные арифметические действия с данными в таблицах: сложение, вычитание, умножение, деление, возведение в степень извлечение корня и т.
д. Чтобы применить формулу, нужно в ячейке, куда планируется выводить результат, поставить знак «=». После этого вводится сама формула, которая может состоять из математических знаков, чисел и адресов ячеек. Для указания адреса ячейки, из которой берутся данные для расчета, достаточно кликнуть по ней мышкой, и ее координаты появится в ячейке для вывода результата.
Excel также удобно использовать и в качестве обычного калькулятора. Для этого в строке формул или в любой ячейке просто вводятся математические выражения после знака «=».
Урок: Применение формул в Microsoft Excel
Функция «ЕСЛИ»
Одной из самых популярных функций, которые используются в Excel, является «ЕСЛИ». Она дает возможность задать в ячейке вывод одного результата при выполнении конкретного условия и другого результата в случае его невыполнения. Ее синтаксис выглядит следующим образом: ЕСЛИ(логическое выражение; [результат если истина]; [результат если ложь]).
Операторами «И», «ИЛИ» и вложенной функцией «ЕСЛИ» задается соответствие нескольким условиям или одному из нескольких условий.
Урок: Применение функции «ЕСЛИ» в Microsoft Excel
Макросы
С помощью макросов в программе записывается выполнение определенных действий, а потом они воспроизводятся автоматически. Это существенно экономит время на выполнении большого количества однотипной работы. Макросы записываются путем включения захвата своих действий в программе через соответствующую кнопку на ленте.
Запись макросов также можно производить, используя язык разметки Visual Basic в специальном редакторе.
Урок: Применение макросов в Microsoft Excel
Условное форматирование
Чтобы выделить определенные данные, в таблице применяется функция условного форматирования, позволяющая настроить правила выделения ячеек. Само условное форматирование позволяется выполнить в виде гистограммы, цветовой шкалы или набора значков. Переход к ней осуществляется через вкладку «Главная» с выделением диапазона ячеек, который вы собираетесь отформатировать.
Далее в группе инструментов «Стили» нажмите кнопку, имеющая название «Условное форматирование». После этого останется выбрать тот вариант форматирования, который считаете наиболее подходящим.
Форматирование будет выполнено.
Урок: Применение условного форматирования в Microsoft Excel
«Умная» таблица
Не все пользователи знают, что таблицу, просто начерченную карандашом или границами, Excel воспринимает как простую область ячеек. Заставить программу видеть этот набор данных как таблицу можно через переформатирование. Делается это просто: для начала выделяем нужный диапазон с данными, а затем, находясь на вкладке «Главная», кликаем по кнопке «Форматировать как таблицу». Появится список с различными вариантами стилей оформления, где укажите подходящий.
Таблица также создается нажатием на кнопку «Таблица», которая расположена на вкладке «Вставка», предварительно выделив определенную область листа с данными.
Выделенный набор ячеек редактор будет воспринимать как таблицу. Вследствие этого, к примеру, если вы введете в расположенные у границ таблицы ячейки какие-то данные, они будут автоматически включены в нее. Вдобавок к этому при прокрутке вниз шапка будет постоянно в пределах области зрения.
Урок: Создание таблицы в Microsoft Excel
Подбор параметра
С помощью функции подбора параметров можно подобрать исходные данные, руководствуясь желаемым для вас результатом. Перейдите на вкладку «Данные» и нажмите кнопку «Анализ «что если»», расположенную в блоке инструментов «Работа с данными». В появившемся списке укажите пункт «Подбор параметра…».
Откроется окно подбора параметра. В поле «Установить в ячейке» вы должны указать ссылку на ячейку, которая содержит нужную формулу. В поле «Значение» должен быть указан конечный результат, который вы хотите получить. В поле «Изменяя значения ячейки» вставьте координаты ячейки с корректируемым значением.
Урок: Применение подбора параметров в Microsoft Excel
Функция «ИНДЕКС»
Возможности, которые предоставляет функция «ИНДЕКС», в чем-то близки к возможностям функции «ВПР». Она также позволяет искать данные в массиве значений и возвращать их в указанную ячейку. Синтаксис выглядит следующим образом: ИНДЕКС(диапазон_ячеек;номер_строки;номер_столбца).
Урок: Применение функции «ИНДЕКС» в Microsoft Excel
Это далеко не полный перечень всех функций, которые доступны в программе Microsoft Excel. Мы остановили внимание только на самых популярных и наиболее важных из них.
3 сумасшедшие формулы Microsoft Excel, которые чрезвычайно полезны
Формулы Microsoft Excel могут делать практически все. В этой статье вы узнаете, насколько мощными могут быть формулы Microsoft Excel и условное форматирование, на трех полезных примерах.
Знакомство с Microsoft Excel
Мы рассмотрели ряд различных способов более эффективного использования Excel, например, где найти отличные загружаемые шаблоны Excel и как использовать Excel в качестве инструмента управления проектами.
Большая часть возможностей Excel заключается в формулах и правилах Excel, которые помогают автоматически управлять данными и информацией, независимо от того, какие данные вы вставляете в электронную таблицу.
Давайте углубимся в то, как вы можете использовать формулы и другие инструменты, чтобы лучше использовать Microsoft Excel.
Условное форматирование с формулами Excel
Одним из инструментов, который люди используют недостаточно часто, является условное форматирование. С помощью формул, правил Excel или нескольких действительно простых настроек вы можете превратить электронную таблицу в автоматизированную панель мониторинга.
Чтобы перейти к условному форматированию, просто щелкните вкладку Главная и щелкните значок Условное форматирование на панели инструментов.
В условном форматировании есть много вариантов. Большинство из них выходит за рамки этой конкретной статьи, но большинство из них посвящено выделению, окрашиванию или затенению ячеек на основе данных в этой ячейке.
Это, вероятно, наиболее распространенное использование условного форматирования — такие вещи, как окрашивание ячейки в красный цвет с помощью формул меньше или больше. Узнайте больше о том, как использовать операторы ЕСЛИ в Excel.
Одним из редко используемых инструментов условного форматирования является параметр Icon Sets , который предлагает отличный набор значков, которые можно использовать для превращения ячейки данных Excel в значок на панели мониторинга.
Когда вы нажмете Управление правилами , вы перейдете к Диспетчеру правил условного форматирования .
В зависимости от данных, которые вы выбрали перед выбором набора значков, вы увидите ячейку, указанную в окне менеджера с только что выбранным набором значков.
Когда вы нажмете Edit Rule , вы увидите диалоговое окно, в котором происходит волшебство.
Здесь вы можете создать логическую формулу и уравнения, которые будут отображать нужный значок панели мониторинга.
Этот пример панели мониторинга покажет время, затраченное на выполнение различных задач, по сравнению с запланированным временем. Если вы превысите половину бюджета, загорится желтый индикатор. Если вы полностью превысите бюджет, он станет красным.
Как видите, эта информационная панель показывает, что составление бюджета времени не удалось.
Почти половина времени тратится намного больше, чем предусмотрено в бюджете.
Время переориентироваться и лучше управлять своим временем!
1. Использование функции ВПР
Если вы хотите использовать более продвинутые функции Microsoft Excel, вот несколько из них, которые вы можете попробовать.
Вы, вероятно, знакомы с функцией VLookup, которая позволяет искать в списке определенный элемент в одном столбце и возвращать данные из другого столбца в той же строке, что и этот элемент.
К сожалению, функция требует, чтобы элемент, который вы ищете в списке, находился в левом столбце, а данные, которые вы ищете, были в правом, но что, если они перепутаны?
Что делать, если в приведенном ниже примере я хочу найти Задание, которое я выполнил 25.
06.2018, по следующим данным?
В этом случае вы просматриваете значения справа и хотите вернуть соответствующее значение слева.
Если вы читаете форумы профессиональных пользователей Microsoft Excel, вы обнаружите, что многие говорят, что это невозможно с помощью VLookup. Для этого вы должны использовать комбинацию функций Index и Match. Это не совсем так.
Вы можете заставить VLookup работать таким образом, вложив в него функцию CHOOSE. В этом случае формула Excel будет выглядеть так:
. "=ВПР(ДАТА(2018,6,25),ВЫБОР({1,2},E2:E8,A2:A8),2,0)" Эта функция означает, что вы хотите найти дату 6/ 25/2013 в списке поиска, а затем вернуть соответствующее значение из индекса столбца.
В этом случае вы заметите, что индекс столбца равен «2», но, как видите, столбец в приведенной выше таблице на самом деле равен 1, верно?
Это правда, но то, что вы делаете с функцией » ВЫБЕРИТЕ «, манипулирует двумя полями.
Вы присваиваете ссылочные «индексные» номера диапазонам данных — присваиваете датам индексный номер 1, а задачи — индексному номеру 2.
Итак, когда вы вводите «2» в функции VLookup, вы ссылаясь на номер индекса 2 в функции ВЫБОР. Круто, правда?
VLookup теперь использует столбец Date и возвращает данные из Task , несмотря на то, что Задача находится слева.
Теперь, когда вы знаете эту маленькую хитрость, представьте, что еще вы можете сделать!
Если вы пытаетесь выполнить другие сложные задачи поиска данных, ознакомьтесь с этой статьей о поиске данных в Excel с помощью функций поиска.
2. Вложенная формула для разбора строк
Вот еще одна безумная формула Excel для вас! Могут быть случаи, когда вы либо импортируете данные в Microsoft Excel из внешнего источника, состоящего из строки данных с разделителями.
После того, как вы ввели данные, вы хотите разобрать их на отдельные компоненты. Вот пример информации об имени, адресе и номере телефона, разделенных знаком «;». персонаж.
Вот как вы можете проанализировать эту информацию, используя формулу Excel (посмотрите, сможете ли вы мысленно следовать этому безумию):
Для первого поля, чтобы извлечь крайний левый элемент (имя человека), вы должны просто использовать ЛЕВЫЙ функция в формуле.
"=ЛЕВЫЙ(A2,НАЙТИ(";",A2,1)-1)" Вот как работает эта логика:
- Ищет текстовую строку из A2
- Находит «;» символ-разделитель
- Вычитает единицу для правильного расположения конца этой секции строки
- Захватывает крайний левый текст в эту точку
В данном случае крайний левый текст — «Райан». Миссия выполнена.
3. Вложенная формула в Excel
А как насчет других разделов?
Возможно, есть более простые способы сделать это, но поскольку мы хотим попытаться создать самую безумную вложенную формулу Excel (которая действительно работает), мы собираемся использовать уникальный подход.
Чтобы извлечь части справа, вам нужно вложить несколько функций RIGHT, чтобы захватить часть текста до первого «;» символ и снова выполните для него функцию ВЛЕВО. Вот как это выглядит для извлечения части адреса с номером улицы.
"=ЛЕВЫЙ((ПРАВЫЙ(A2,ДЛИН(A2)-НАЙТИ(";",A2))),НАЙТИ(";",(ПРАВЫЙ(A2,ДЛИН(A2)-НАЙТИ(";",A2 ))),1)-1)" Выглядит безумно, но собрать несложно.
Все, что я сделал, это взял эту функцию:
ПРАВО(A2,LEN(A2)-НАЙТИ(";",A2)) И вставил его во все места в функции LEFT выше, где есть «A2».
Это правильно извлекает вторую часть строки.
Каждая последующая секция строки требует создания еще одного гнезда. Теперь все, что вам нужно сделать, это взять уравнение « ПРАВИЛЬНО », которое вы создали в предыдущем разделе, и вставить его в новую формулу ПРАВИЛЬНО с вставленной в нее предыдущей формулой ПРАВИЛЬНО там, где вы видите «A2». Вот как это выглядит.
(ПРАВИЛЬНО((ПРАВИЛЬНО(A2,ДЛСТР(A2)-НАЙТИ(";",A2))),ДЛСТР((ПРАВИЛЬНО(A2,ДЛСТР(A2)-НАЙТИ(";",A2))))-НАЙТИ (";",(ПРАВО(A2,ДЛСТР(A2)-НАЙТИ(";",A2))))) Затем вам нужно взять ЭТУ формулу и поместить ее в исходную ЛЕВУЮ формулу везде, где есть » А2″.
Окончательная умопомрачительная формула выглядит так:
"=ЛЕВО((ПРАВО((ПРАВО(A2,ДЛСТР(A2)-НАЙТИ(";",A2))),ДЛСТР((ПРАВО(A2,ДЛСТР) (A2)-НАЙТИ(";",A2))))-НАЙТИ(";",(ВПРАВО(A2,ДЛСТР(A2)-НАЙТИ(";",A2)))))),НАЙТИ("; ",(ПРАВИЛЬНО((ПРАВИЛЬНО(A2,ДЛСТР(A2)-НАЙТИ(";",A2))),ДЛСТР((ПРАВИЛЬНО(A2,ДЛСТР(A2)-НАЙТИ(";",A2))))- НАЙТИ(";",(ПРАВО(A2,ДЛСТР(A2)-НАЙТИ(";",A2)))))),1)-1)" Эта формула правильно извлекает «Портленд, Мэн 04076» из исходной строки.
Чтобы извлечь следующий раздел, повторите описанный выше процесс еще раз.
Ваши формулы Excel могут быть очень зацикленными, но все, что вы делаете, это вырезаете и вставляете длинные формулы друг в друга, создавая длинные гнезда, которые все еще работают.
Да, это соответствует критерию «сумасшедший». Но давайте будем честными, есть гораздо более простой способ сделать то же самое с помощью одной функции.
Просто выберите столбец с данными с разделителями, а затем в пункте меню Данные выберите Текст в столбцы .
Появится окно, в котором вы можете разделить строку по любому разделителю, который вы хотите. Просто введите ‘; ‘, и вы увидите, что предварительный просмотр выбранных вами данных изменится соответствующим образом.
В пару кликов вы можете сделать то же самое, что и эта безумная формула выше… но что в этом интересного?
Сходить с ума от формул Microsoft Excel
Вот и все.
Приведенные выше формулы доказывают, насколько чрезмерным может быть человек при создании формул Microsoft Excel для выполнения определенных задач.
Иногда эти формулы Excel не являются самым простым (или лучшим) способом выполнения задач. Большинство программистов скажут вам, что нужно быть проще, и это так же верно для формул Excel, как и для всего остального.
Если вы действительно хотите серьезно заняться использованием Excel, вам следует прочитать наше руководство для начинающих по использованию Microsoft Excel. В нем есть все, что вам нужно, чтобы начать повышать свою производительность с помощью Excel. После этого обязательно ознакомьтесь с нашей памяткой по основным функциям Excel для получения дополнительных рекомендаций.
Изображение предоставлено: kues/Depositphotos
12 самых полезных функций Excel для анализа данных
В Excel более 475 функций. Это может сделать вас ошеломляющим, когда вы начинаете анализировать данные.
С таким большим разнообразием функций может быть трудно понять, какую из них использовать для конкретных задач Excel.
Самые полезные функции Excel — это те, которые облегчают задачу. И хорошая новость заключается в том, что у большинства пользователей Excel есть набор инструментов, состоящий всего из нескольких функций, которые удовлетворяют большинство их потребностей.
Этот ресурс охватывает 12 наиболее полезных функций Excel для анализа данных . Эти функции предоставляют вам инструменты для решения большинства задач анализа данных Excel.
Загрузите бесплатное учебное пособие
Не забудьте загрузить файл упражнения , который поможет вам следовать этой статье и изучить лучшие функции для анализа данных.
Изучите самые полезные функции Excel
Загрузите БЕСПЛАТНЫЙ файл с упражнениями для выполнения
Введите адрес электронной почты
1.
ЕСЛИФункция ЕСЛИ чрезвычайно полезна. Эта функция означает, что мы можем автоматизировать принятие решений в наших электронных таблицах.
С помощью ЕСЛИ мы можем заставить Excel выполнять другие вычисления или отображать другое значение в зависимости от результата логического теста (решения).
Функция ЕСЛИ запрашивает выполнение логического теста, какое действие следует предпринять, если тест верен, и альтернативное действие, если результат теста неверен.
= ЕСЛИ (логический тест, значение, если оно истинно, значение, если оно ложно)
В этом примере мы отображаем слово «Да», если дата доставки в столбце C более чем на 7 дней позже даты заказа в столбец B. В противном случае отображается слово «Нет».
=ЕСЛИ(D2>7,»Да»,»нет»)
2. СУММЕСЛИМН
СУММЕСЛИМН — одна из самых полезных функций Excel. Он суммирует значения, соответствующие указанным критериям .
В Excel также есть функция СУММЕСЛИМН, которая выполняет ту же задачу, но может проверять только одно условие, тогда как СУММЕСЛИМН может проверять многие.
Таким образом, вы можете игнорировать СУММЕСЛИМН, поскольку СУММЕСЛИМН — превосходная функция.
Функция запрашивает диапазон значений для суммирования, а затем каждый диапазон для проверки и критерии для проверки.
=СУММЕСЛИМН(диапазон суммы, диапазон критериев 1, критерии 1, …)
В этом примере мы суммируем значения в столбце C для области, введенной в ячейку E3.
=СУММЕСЛИМН(C2:C9,B2:B9,E3)
Функция СУММЕСЛИМН определенно заслуживает более подробного изучения. Это чрезвычайно полезная функция Excel.
3. СЧЁТЕСЛИМН
Функция СЧЁТЕСЛИМН — ещё одна мегафункция для анализа данных Excel.
Очень похоже на функцию СУММЕСЛИМН. И хотя они не упоминаются как часть 12 наиболее полезных функций Excel для анализа данных, существуют также функции СРЗНАЧЕСЛИ, МАКСИФМ и МИНИМФС.
Функция СЧЁТЕСЛИМН подсчитает количество значений, соответствующих заданным критериям . Поэтому для него не требуется диапазон суммы, такой как СУММЕСЛИМН.
=СЧЁТЕСЛИМН(диапазон критериев 1, критерии 1, …)
В этом примере мы подсчитываем количество продаж из региона, введенных в ячейку E3, которые имеют значение 200 или более.
=СЧЁТЕСЛИМН(B2:B9,E3,C2:C9,»>=200″)
При использовании функций СУММЕСЛИМН и СЧЁТЕСЛИМН критерии необходимо вводить в виде текста или ссылки на ячейку. Этот пример использует оба метода в одной формуле
4. TRIM
Эта замечательная функция удалит все пробелы из ячейки , за исключением одиночных пробелов между словами.
Чаще всего эта функция используется для удаления завершающих пробелов. Это обычно происходит, когда содержимое вставляется откуда-то еще или когда пользователи случайно вводят пробелы в конце текста.
В этом примере функция СЧЁТЕСЛИМН не работает, так как в конце ячейки B6 был случайно использован пробел.
Пользователи не могут видеть это пространство, что означает, что оно не идентифицируется, пока что-то не перестанет работать.
Функция TRIM предложит вам ввести текст, из которого нужно удалить пробелы.
=TRIM(текст)
В этом примере функция TRIM используется в отдельном столбце для очистки данных в столбце региона, готовых к анализу.
=TRIM(B2)
Тогда функция СЧЁТЕСЛИМН имеет чистые данные и работает правильно.
5. СЦЕПИТЬ
Функция СЦЕПИТЬ объединяет значения из нескольких ячеек в одно.
Это полезно для объединения различных частей текста, таких как чье-то имя, адрес, номер ссылки, путь к файлу или URL-адрес.
Предлагает вам использовать различные значения.
=СЦЕПИТЬ(текст1, текст2, текст3, …)
В этом примере СЦЕПИТЬ используется для объединения имени и фамилии в полное имя. Пробел вводится для аргумента text2.
=СЦЕПИТЬ(A2,» «,B2)
6. ВЛЕВО/ВПРАВО
Функции ВЛЕВО и ВПРАВО будут выполнять действие, противоположное СЦЕПИТЬ.
Они будут извлекать указанное количество символов из начала и конца текста.
Может использоваться для извлечения частей адреса, URL-адреса или ссылки для дальнейшего анализа.
Функции ВЛЕВО и ВПРАВО запрашивают одинаковую информацию. Они хотят знать, где находится текст и сколько символов вы хотите извлечь.
=ЛЕВО(текст, число символов)
=ПРАВО(текст, число символов)
В этом примере столбец A содержит ссылку, состоящую из идентификатора клиента (первые два символа), идентификатора транзакции и кода региона (последний символ).
Следующая функция ЛЕВАЯ используется для извлечения идентификатора клиента.
=ЛЕВО(A2,2)
Функция ПРАВИЛЬНО может использоваться для извлечения последнего символа из ячеек в столбце A. Этот пример указывает, находится ли клиент на юге или севере.
=ПРАВО(A2,1)
7. ВПР
Функция ВПР является одной из наиболее часто используемых и узнаваемых функций в Excel.
Он будет искать значение в таблице и возвращать информацию из другого столбца, относящегося к этому значению.
Отлично подходит для объединения данных из разных списков в один или для сравнения двух списков на наличие совпадающих или отсутствующих элементов. Это важный инструмент в анализе данных Excel.
Запрашивает четыре элемента информации:
- Значение, которое вы хотите найти
- Какую таблицу искать в
- Какой столбец содержит информацию, которую вы хотите вернуть
- Какой тип поиска вы хотите выполнить.
=ВПР(значение поиска, массив таблиц, номер индекса столбца, поиск диапазона)
В этом примере у нас есть таблица, содержащая данные о продажах наших сотрудников. Существует еще одна таблица с дополнительной информацией об этих сотрудниках (для примера таблицы сделаны небольшими).
Мы хотели бы внести данные, показывающие, в каком регионе находится сотрудник, в таблицу продаж для анализа.
В столбце D используется следующая формула:
=VLOOKUP(B2,$G$2:$H$12,2,FALSE)
Эта функция может оказаться одной из самых сложных для начинающих. Формулы Эксель. Вы можете более подробно изучить ВПР в этой статье или в нашем всеобъемлющем курсе Excel.
8. ЕСЛИОШИБКА
Иногда случаются ошибки, которые могут быть невинными, а иногда эти ошибки можно предсказать. Функция VLOOKUP, описанная выше, является типичным примером этого.
Произошла ошибка из-за опечатки в имени в таблице продаж. Это означает, что ВПР не может найти это имя и выдает ошибку.
Используя ЕСЛИОШИБКА, мы могли бы отобразить более значимую ошибку , чем та, которую предоставляет Excel, или даже выполнить другой расчет.
Функция ЕСЛИОШИБКА требует двух вещей. Значение, которое необходимо проверить на наличие ошибки и какое действие следует выполнить вместо этого.
В этом примере мы используем функцию ЕСЛИОШИБКА для функции ВПР, чтобы отобразить более осмысленное сообщение.
=ЕСЛИОШИБКА(ВПР(B2,$G$2:$H$12,2,ЛОЖЬ);»Имя не найдено. Проверьте оба списка») для анализа был импортирован из другой системы или откуда-то скопирован и вставлен.
Это часто может привести к тому, что данные будут в неправильном формате, например, число будет сохранено как текст. Вы не можете выполнять задачи анализа данных, такие как СУММ, если Excel не распознает их как числа.
К счастью, на помощь приходит функция ЗНАЧЕНИЕ. Его работа до преобразовать числа, хранящиеся в виде текста, в числа .
Функция запрашивает текст для преобразования.
=ЗНАЧ(текст)
В этом примере следующая формула преобразует значения продаж, хранящиеся в виде текста в столбце B, в число.
=ЗНАЧ(B2)
10. УНИКАЛЬНАЯ
Функция УНИКАЛЬНАЯ — это новая функция , доступная только тем, кто использует версию Microsoft 365 .
Функция хочет знать три вещи:
- Диапазон для возврата уникального списка из
- Хотите ли вы проверять уникальные значения по столбцу или по строке
- Нужен ли вам уникальный список или отдельный список (элементы, которые встречаются только один раз).
=UNIQUE(массив, по столбцу, ровно один раз)
В этом примере у нас есть список продаж продуктов, и мы хотим извлечь уникальный список названий продуктов. Для этого нам нужно только указать диапазон.
=UNIQUE(B2:B15)
Это функция динамического массива, и поэтому результаты передаются. Синяя рамка указывает на разбросанный диапазон.
Затем мы можем использовать функцию СУММЕСЛИМН, упомянутую ранее в этой статье, для суммирования продаж каждого из этих продуктов.
Это должно выглядеть знакомым ранее. Однако на этот раз для ссылки на разлитый диапазон использовался символ #.
11. СОРТИРОВКА
Это еще одна функция, доступная только подписчикам Microsoft 365 . Как следует из названия, он будет сортировать список.
Функция СОРТИРОВКИ запрашивает четыре аргумента:
- Диапазон для сортировки
- По какому столбцу сортировать диапазон по
- В каком порядке сортировать диапазон (по возрастанию или по убыванию)
- Сортировать ли строки или столбцы.
=СОРТИРОВАТЬ(массив, индекс сортировки, порядок сортировки, по столбцу)
Это фантастика. И его можно использовать с предыдущим примером UNIQUE для сортировки названий продуктов по порядку.
Для этого нам нужно только указать диапазон для сортировки.
=СОРТИРОВКА(УНИКАЛЬНАЯ(B2:B15))
12. ФИЛЬТР
После функции СОРТИРОВКИ существует также функция фильтрации списка. Еще одна функция , доступная только пользователям Microsoft 365 .
Эта функция будет фильтровать диапазон . Это чрезвычайно мощная функция, которая идеально подходит для анализа данных и создания отчетов.
Функция ФИЛЬТР принимает три аргумента:
- Диапазон для фильтрации
- Критерий, указывающий, какие результаты возвращать
- Какие действия предпринять, если результаты не возвращаются.
=ФИЛЬТР(массив, включить, если пусто)
В этом примере возвращаются только результаты для субъекта, введенного в ячейку F2.