Самые полезные функции excel: 8 полезных функций и возможностей Excel, о которых не помнят

Полезные функции 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. Мы остановили внимание только на самых популярных и наиболее важных из них.

Журнал «Пуш»: Карьерные статьи и советы

Статьи

5 полезных функций Excel для финмоделирования

Excel уже давно доказал свою ценность в работе с любого рода математическими данными. Но помимо обычных инструментов, о которых знает любой обыватель, программа скрывает в себе и более широкие возможности. К слову, при сложном моделировании или анализе больших финансовых моделей, состоящих из множества вкладок либо требующих длительной прокрутки на сотни ячеек, чаще всего используются «Элементы управления формы». Освоить другие полезные функции программы на продвинутом уровне можно на онлайн-курсе «Excel для карьеры и поиска работы» от Changellenge >> ToolKit.

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

Если вы нечасто пользуетесь Excel, то вряд ли сразу найдете вкладку с нужными элементами. В стандартном виде программы ее нет. Чтобы включить, нажмите на кнопку Office или «Файл», зайдите в параметры Excel, в разделе «Основные» поставьте галочку у строки «Показывать вкладку “Разработчик”». Затем, зайдя во вкладку «Разработчик» и нажав на кнопку «Вставить», вы увидите все элементы управления формы.

Если у вас установлена версия Microsoft Office 2013, вкладку «Разработчик» вы найдете по следующему алгоритму. Нажмите на кнопку «Файл», откройте вкладку «Параметры» и выберите пункт «Настроить ленту». В списке «Основные вкладки» поставьте галочку у элемента «Разработчик».

Если вы пользуетесь версией программы для macOS, войти в меню «Разработчик» будет еще проще. В меню Excel выберите пункт «Параметры», затем «Представление» и поставьте в разделе «На ленте показывать» флажок напротив «Вкладка “Разработчик”».

Наиболее полезные для финансового моделирования функции — это «Поле со списком», «Список», «Переключатель», «Флажок» и «Счетчик».  О них и пойдет речь.

1. Поле со списком

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

2. Список

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

3. Переключатель

«Переключатель», так же как «Список» и «Поле со списком», используется для предоставления пользователям возможности выбора из нескольких вариантов. От одного переключателя на листе толку нет, ведь он, как следует из названия, должен что-то переключать. Этот инструмент может пригодиться, например, при составлении плана продаж. Можно, скажем, рассчитать его по трем сценариям: базовому, умеренному и оптимистичному.

4. Флажок

Этот инструмент дает возможность активировать или деактивировать некое условие. Каждый флажок необходимо связать с ячейкой, в которой, в зависимости от того, зажат флажок или нет, появятся значения «ИСТИНА» или «ЛОЖЬ». В дальнейшем регулировать значения флажка можно, например, через функцию =ЕСЛИ(). 

Более продвинутые пользователи, впрочем, знают, что диапазон применения «Флажка» не ограничивается только этим. С помощью различных формул можно отображать разные надписи. Также можно проводить различные вычисления, скажем выводить сумму одного из двух диапазонов (например, A1 : A5 и B1 : B5). А еще «Флажок» часто используют для того, чтобы включать и выключать заливку ячейки.

5. Счетчик

«Счетчик» предоставляет возможность производить тонкую настройку какого-либо параметра путем его изменения с некоторым настраиваемым шагом (1, 2, 3…). Этот виджет может пригодиться, если перед вами модель, зависящая от определенного параметра, а ваша задача в том, чтобы проверить, как ведут себя показатели в зависимости от него. Вместо того чтобы каждый раз вводить новое значение в ячейку, просто нажимайте на кнопку счетчика. Можно даже нажать на счетчик, удерживая левую кнопку мыши, чтобы быстро прокрутить цифры в ячейке. Значение показателя может использоваться в дальнейшем, например, с помощью функций =СМЕЩ(), =ВЫБОР(), =ЕСЛИ(). В английской версии программы эти функции называются =OFFSET(), =CHOOSE() и =IF().

Хотите больше узнать о работе в Excel?

Мы создали онлайн-курс, на котором учим использовать самые важные функции этой программы на реальных бизнес-задачах. Вебинары от экспертов с опытом работы в Big3 и Big4, много практики и возможность получить карьерные рекомендации — подробности тут.

Чтобы проверить, насколько хорошо вы знаете Excel, пройдите тестирование.

Пройти тест >>

Теги

Hard Skills
Финансы
Подборка

Получите карьерную поддержку

Если вы не знаете, с чего начать карьеру, зашли в тупик или считаете, что совершили какие-то ошибки, спросите совета у специалистов. Заполните заявку и консультанты Changellenge >> окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

Стажировки

• 
  

  TO GO Практика PUMP! от СИБУРа

  
  

• 
  

  Лидерская программа 100 лидеров

  
  

• 
  

  Менеджер по работе с партнёрами (стажер)

  
  

• Следите за новыми вакансиями в нашем Telegram-канале

  
  CL-Offers

Смотрите также

Что нужно уметь, чтобы стать CEO? Семь советов от руководителей ведущих компаний

«Райффайзенбанк»: пластиковые карты почти полностью исчезнут в ближайшие годы

Как стать топом: изучайте советы CFO Московской Биржи и выиграйте приглашение на деловой завтрак!

Как ставить цели и достигать результатов, или OKR

Как уйти в отпуск

Пять российских вузов вошли в топ-10 международного рейтинга QS

Почти треть россиян уже полностью отказались от наличных

От кейса до запуска продукта: призер чемпионата Changellenge >> рассказывает о том, как его решение обрело жизнь

Fake it till you make it: мифы о работе в продажах

Что такое фит-интервью и как его пройти

Полезные связи: найти и не потерять

Названы самые востребованные направления высшего образования 2021 года

11 неочевидных способов провести новогодние каникулы с пользой

Как стать CEO до 30: 10 карьерных советов от Андрея Алясова

Как пройти ассессмент и попасть на программу развития в FMCG-компанию

Лень, эгоизм и A/B-тесты: как создавать цифровые продукты

Российская удаленка в цифрах и фактах

Как все устроено: МИЭФ ВШЭ

Создать аккаунт

Адрес e-mail :

Телефон :

+7+380+375+49+996+1+66+44+86+371+373+998+374+994+992+995+90+48+31

Фамилия :

Пароль :

Подтверждение пароля :

Получать рассылку Changellenge

Согласен с условиями обработки персональных данных

Если данные введены неверно, то восстановить доступ к личному кабинету будет
невозможно

E-mail:

Номер телефона:

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. формулы. Вы можете более подробно изучить ВПР в этой статье или в нашем всеобъемлющем курсе 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.

=ФИЛЬТР(B2:C12,A2:A12=F2,»Без оценок»)

Подведение итогов

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

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

Еще два важных инструмента Excel, которые необходимо освоить, — это Power Query и Power Pivot.

Power Query упрощает импорт и преобразование данных для анализа. А Power Pivot — идеальный инструмент, если вы анализируете большие объемы данных. Он может хранить огромные объемы данных вне Excel и имеет собственный язык формул под названием DAX.

Пройдите курсы Power Query и Power Pivot на GoSkills до ускорьте свои навыки анализа данных в Excel уже сегодня.

Повысьте уровень своих навыков работы с Excel

Станьте сертифицированным мастером Excel с небольшими курсами GoSkills

Начать бесплатную пробную версию

25 лучших формул MS Excel, которые нужно знать

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

Одной из таких функций, которая выделяет Excel, являются формулы листа Excel. Здесь мы рассмотрим 25 лучших формул Excel, которые необходимо знать при работе в Excel. В этой статье мы рассмотрим следующие темы:

.

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

Давайте теперь рассмотрим 25 лучших формул Excel, которые вы должны знать. В этой статье мы классифицировали 25 формул Excel на основе их операций. Начнем с первой формулы Excel в нашем списке.

1. СУММА

Функция СУММ(), как следует из названия, дает общее значение для выбранного диапазона значений ячеек. Он выполняет математическую операцию сложения. Вот пример этого ниже:

Образец формулы: «=СУММ(C2:C4)»

Рис.: Функция суммирования в Excel

Как вы можете видеть выше, чтобы найти общую сумму продаж для каждой единицы, нам нужно было просто ввести функцию «=СУММ(C2:C4)». Это автоматически добавляет 300, 385 и 480. Результат сохраняется в C5.

2. СРЕДНЯЯ

Функция СРЗНАЧ() фокусируется на вычислении среднего значения выбранного диапазона значений ячеек. Как видно из приведенного ниже примера, чтобы найти среднее значение общего объема продаж, вам нужно просто ввести:

.

«=СРЕДНЕЕ(C2, C3, C4)»  

Рис. Функция усреднения в Excel

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

3.

СЧЕТ

Функция COUNT() подсчитывает общее количество ячеек в диапазоне, содержащем число. Он не включает пустую ячейку и те, которые содержат данные в любом другом формате, кроме числового.

Пример формулы: «=COUNT(C1:C4)»

Рис. Функция Microsoft Excel — подсчет

Как видно выше, здесь мы считаем от C1 до C4, в идеале четыре ячейки. Но поскольку функция COUNT принимает во внимание только ячейки с числовыми значениями, ответ равен 3, поскольку ячейка, содержащая «Общий объем продаж», здесь опущена.

Если вам необходимо подсчитать все ячейки с числовыми значениями, текстом и любым другим форматом данных, вы должны использовать функцию «СЧЕТЧИК()». Однако COUNTA() не считает пустые ячейки.

Для подсчета количества пустых ячеек, присутствующих в диапазоне ячеек, используется СЧИТАТЬПУСТОТЫ().

4. ПРОМЕЖУТОЧНЫЙ ИТОГ

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

Рис: Функция промежуточного итога в Excel

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

«=ПРОМЕЖУТОЧНЫЙ ИТОГ (1, A2: A4)»

В списке промежуточных итогов «1» относится к среднему значению. Следовательно, приведенная выше функция даст среднее значение A2: A4, а ответ на него — 11, который хранится в C5. Аналогично,

«=ПРОМЕЖУТОЧНЫЙ ИТОГ (4, A2: A4)»

Выбирает ячейку с максимальным значением от A2 до A4, то есть 12. Включение «4» в функцию обеспечивает максимальный результат.

Рис. Функция подсчета в Excel

5. МОДУЛЬ

Функция MOD() возвращает остаток при делении определенного числа на делитель. Давайте теперь посмотрим на примеры ниже для лучшего понимания.

• В первом примере мы поделили 10 на 3. Остаток вычисляем с помощью функции

        «=MOD(A2,3)»  

• Результат сохраняется в ячейке B2. Мы также можем напрямую ввести «=MOD(10,3)», так как это даст тот же ответ.

Рис. Функция модуля в Excel

• Точно так же здесь мы разделили 12 на 4. Остаток равен 0, который хранится в ячейке B3.

Рис. Функция модуля в Excel

6. ПИТАНИЕ

Функция «Степень()» возвращает результат возведения числа в определенную степень. Давайте посмотрим на примеры, показанные ниже:

Рис. Степенная функция в Excel

Как вы можете видеть выше, чтобы найти степень числа 10, хранящуюся в A2, увеличенную до 3, мы должны ввести:

«= МОЩНОСТЬ (A2,3)»

Так работает функция мощности в Excel.

7. ПОТОЛОК

Далее у нас есть функция потолка. Функция CEILING() округляет число до ближайшего кратного значения.

Рис: Функция потолка в Excel

Ближайшее максимальное число, кратное 5 для 35,316, равно 40.

8. ПОЛ

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

Рис. Функция этажа в Excel

Ближайшее наименьшее число, кратное 5 для 35,316, равно 35.

9. СЦЕПИТЬ

Эта функция объединяет или объединяет несколько текстовых строк в одну текстовую строку. Ниже приведены различные способы выполнения этой функции.

• В этом примере мы использовали синтаксис:

         «=СЦЕПИТЬ(A25, » «, B25)» 

Рис. Функция конкатенации в Excel

• В этом примере мы использовали синтаксис: 

        «= СЦЕПИТЬ (A27&» «&B27)»

Рис. Функция конкатенации в Excel

Это были два способа реализации операции конкатенации в Excel.

Читайте также: Как использовать конкатенацию в Excel?

10. ЛЕН

Функция ДЛСТР() возвращает общее количество символов в строке. Таким образом, он будет учитывать все символы, включая пробелы и специальные символы. Ниже приведен пример функции Len.

Рис. Функция Len в Excel

Давайте теперь перейдем к следующей функции Excel в нашем списке этой статьи.

11. ЗАМЕНИТЕ

Как следует из названия, функция REPLACE() заменяет часть текстовой строки другой текстовой строкой.

Синтаксис: «=REPLACE(старый_текст, начальный_номер, число_символов, новый_текст)». Здесь start_num относится к позиции индекса, с которой вы хотите начать замену символов. Затем num_chars указывает количество символов, которые вы хотите заменить.

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

• Здесь мы заменяем A101 на B101, набрав

        “=ЗАМЕНИТЬ(A15,1,1,»B»)” 

Рис. Функция замены в Excel

• Далее мы заменяем A102 на A2102, набрав:

        «=ЗАМЕНИТЬ(A16,1,1, «A2″)» 

Рис. Функция замены в Excel

• Наконец, мы заменяем Адама на Саама, набрав:

        «=ЗАМЕНИТЬ(A17,1,2, «Sa»)»  

Рис. Функция замены в Excel

Давайте теперь перейдем к нашей следующей функции.

12. ЗАМЕНА

Функция ПОДСТАВИТЬ() заменяет существующий текст новым текстом в текстовой строке.

Синтаксис: «=ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра])».

Здесь [instance_num] ссылается на индексную позицию настоящих текстов более одного раза.

Ниже приведены несколько примеров использования этой функции:

• Здесь мы заменяем «Мне нравится» на «Ему нравится», набрав:

        «=ПОДСТАВИТЬ(A20, «Мне нравится», «Ему нравится»)» 

Рис: Замещающая функция в Excel

• Затем мы заменяем второй 2010, который встречается в исходном тексте в ячейке A21, на 2016, введя «= ПОДСТАВИТЬ (A21,2010, 2016,2)».

Рис. Функция замены в Excel

• Теперь мы заменяем оба числа 2010 в исходном тексте на 2016, введя «= ПОДСТАВИТЬ (A22,2010,2016)».

Рис: Замещающая функция в Excel

Это все, что касается подстановочной функции, теперь давайте перейдем к нашей следующей функции.

13. ЛЕВАЯ, ПРАВАЯ, СРЕДНЯЯ

Функция ВЛЕВО() возвращает количество символов от начала текстовой строки. Между тем, функция MID() возвращает символы из середины текстовой строки, учитывая начальную позицию и длину. Наконец, функция right() возвращает количество символов с конца текстовой строки.

Давайте разберемся с этими функциями на нескольких примерах.

• В приведенном ниже примере мы используем функцию left для получения самого левого слова в предложении в ячейке A5.

Рис: Левая функция в Excel

Ниже показан пример использования средней функции.

Рис: Средняя функция в Excel

• Здесь у нас есть пример правильной функции.

Рис: Правая функция в Excel

14. ВЕРХНИЙ, НИЖНИЙ, ПРАВИЛЬНЫЙ

Функция ПРОПИСНЫЕ() преобразует любую текстовую строку в верхний регистр. Напротив, функция LOWER() преобразует любую текстовую строку в нижний регистр. Функция PROPER() преобразует любую текстовую строку в правильный регистр, т. е. первая буква в каждом слове будет прописной, а все остальные — строчной.

Давайте лучше поймем это на следующих примерах:

• Здесь мы преобразовали текст в формате A6 в полный верхний регистр в формате A7.

Рис. Верхняя функция в Excel

• Теперь мы преобразовали текст в формате A6 в полный нижний регистр, как показано в A7.

Рис: Нижняя функция в Excel

• Наконец, мы преобразовали неправильный текст в формате A6 в чистый и правильный формат в формате A7.

Рис. Правильная работа в Excel

Теперь давайте перейдем к изучению некоторых функций даты и времени в Excel.

15. СЕЙЧАС()

Функция СЕЙЧАС() в Excel дает текущую системную дату и время.

Рис. Теперь функция в Excel

Результат функции NOW() изменится в зависимости от даты и времени вашей системы.

16. СЕГОДНЯ()

Функция СЕГОДНЯ() в Excel предоставляет текущую системную дату.

Рис. Функция «Сегодня» в Excel

Функция ДЕНЬ() используется для возврата дня месяца. Это будет число от 1 до 31. 1 — первый день месяца, 31 — последний день месяца.

Рис: Функция дня в Excel

Функция МЕСЯЦ() возвращает месяц, число от 1 до 12, где 1 — январь, а 12 — декабрь.

 Рис. Функция месяца в Excel

Функция YEAR(), как следует из названия, возвращает год из значения даты.

Рис: Функция года в Excel

17. ВРЕМЯ()

Функция ВРЕМЯ() преобразует часы, минуты, секунды, заданные в виде чисел, в порядковый номер Excel, отформатированный в формате времени.

Рис. Функция времени в Excel

18. ЧАС, МИНУТА, СЕКУНД

Функция HOUR() генерирует час из значения времени в виде числа от 0 до 23. Здесь 0 означает 12:00, а 23 — 23:00.

Рис. Функция часов в Excel

Функция МИНУТЫ() возвращает минуты из значения времени в виде числа от 0 до 59.

Рис. Функция минут в Excel

Функция СЕКУНД() возвращает секунды из значения времени в виде числа от 0 до 59..

Рис: Вторая функция в Excel

19. РАЗНДАТ

Функция РАЗНДАТ() определяет разницу между двумя датами в годах, месяцах или днях.

Ниже приведен пример функции РАЗНДАТ, в которой мы вычисляем текущий возраст человека на основе двух заданных дат: даты рождения и сегодняшней даты.

Рис. Функция Datedif в Excel

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

20. ВПР

Далее в этой статье будет функция ВПР(). Это означает вертикальный поиск, который отвечает за поиск определенного значения в крайнем левом столбце таблицы. Затем он возвращает значение в той же строке из указанного столбца.

Ниже приведены аргументы функции ВПР:

lookup_value — это значение, которое нужно искать в первом столбце таблицы.

Таблица

— указывает таблицу, из которой извлекается значение.

col_index — Колонка в таблице из значения должна быть извлечена.

range_lookup — [необязательный] TRUE = приблизительное совпадение (по умолчанию). ЛОЖЬ = точное совпадение.

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

Если вы хотите найти отдел, к которому принадлежит Стюарт, вы можете использовать функцию ВПР, как показано ниже:

Рис. Функция ВПР в Excel

Здесь ячейка A11 имеет значение поиска, A2: E7 — массив таблиц, 3 — номер индекса столбца с информацией об отделах, а 0 — поиск диапазона.

Если вы нажмете Enter, появится сообщение «Маркетинг», что означает, что Стюарт из отдела маркетинга.

21. ГПР

Подобно ВПР, у нас есть еще одна функция, называемая ГПР() или горизонтальный поиск. Функция HLOOKUP ищет значение в верхней строке таблицы или массива преимуществ. Он дает значение в том же столбце из указанной вами строки.

Ниже приведены аргументы функции ГПР:

• lookup_value — указывает значение для поиска.

Таблица

• — это таблица, из которой вы должны получить данные.
• row_index — это номер строки, из которой извлекаются данные.
• range_lookup — [необязательный] Это логическое значение для указания точного или приблизительного совпадения. Значение по умолчанию — TRUE, что означает приблизительное совпадение.

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

                                                  Рис. Функция Hlookup в Excel

Здесь h33 имеет искомое значение, т. е. Jenson, G1:M5 — массив таблиц, 4 — номер индекса строки, 0 — приблизительное совпадение.

После того, как вы нажмете Enter, появится «Нью-Йорк».

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

22. Формула IF

Функция ЕСЛИ() проверяет заданное условие и возвращает конкретное значение, если оно истинно. Он вернет другое значение, если условие FALSE.

В приведенном ниже примере мы хотим проверить, больше ли значение в ячейке A2 5. Если оно больше 5, функция вернет «Да, 4 больше», в противном случае она вернет «Нет».

                    Рис. Функция If в Excel

В этом случае будет возвращено «Нет», так как 4 не больше 5.

«ЕСЛИ ОШИБКА» — еще одна широко используемая функция. Эта функция возвращает значение, если выражение оценивается как ошибка, в противном случае она возвращает значение выражения.

Предположим, вы хотите разделить 10 на 0. Это неверное выражение, так как вы не можете разделить число на ноль. Это приведет к ошибке.

Приведенная выше функция вернет «Невозможно разделить».

23. ИНДЕКС-МАЧ

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

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

                                                    Рис. Функция Index-Match в Excel

Теперь давайте найдем департамент Зампа.

24. СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ() используется для подсчёта общего количества ячеек в диапазоне, удовлетворяющих заданному условию.

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

Найдем, сколько раз Афганистан присутствует в таблице.

Рис. Функция Countif в Excel

Функция СЧЁТЕСЛИМН подсчитывает количество ячеек, заданное заданным набором условий.

Если вы хотите подсчитать количество дней, в течение которых число случаев в Индии превышало 100. Вот как вы можете использовать функцию СЧЁТЕСЛИМН.

25. СУММЕСЛИФ

Функция СУММЕСЛИ() складывает ячейки, заданные заданным условием или критериями.

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