Полезные функции excel: 21 полезная функция Excel и «Google Таблиц» для интернет-маркетологов

Полезные функции Microsoft Excel – руководство для новичков

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

Office 365 заменён новой платформой Microsoft 365

На смену Office 365 пришел Microsoft 365. Изменилось не только название, вместе с ним появилось много улучшений и совершенно новых решений.

Среди них приложение «Семейная безопасность», инновационные решения для Microsoft Word, Excel или PowerPoint, расширенные функции хранения файлов в облаке OneDrive, а также службы Microsoft Teams и Outlook.

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

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

Что такое Excel и как он выглядит?

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

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

Как устроен Excel

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

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

1. Вверху есть строка заголовка, которая, среди прочего, включает возможность сохранить файл, настроить панель инструментов, название приложения (Excel), стандартное название книги (Книга1) и имя пользователя;
2. Ниже находится меню Файл и панель вкладок с функциями. Вы можете просмотреть параметры Excel в меню Файл. Здесь вы также можете сохранить текущий файл и изменить наиболее важные настройки приложения.

   Панель закладок – самый важный элемент приложения. Наиболее важные вкладки включают:

   • Главная;
   • Вставка;
   • Разметка страницы;
   • Формулы;
   • Данные;
   • Рецензирование;
   • Вид;
   • Справка;
   • Дополнительные расширения.
3. Ниже вы найдёте поле имени ячейки, функцию вставки и панель формул;
4. В левом нижнем углу есть выбор листов;
5. Справа, в самом низу программы, есть опция увеличения / уменьшения масштаба.

Структура листа Excel

Самым важным элементом Excel, над которым выполняется работа, является лист.

Лист состоит из нескольких элементов:

• Строки;
• Столбцы;
• Клетки;
• Диапазоны.

Лист представляет собой очень большую поверхность, состоящую из 1 048 576 строк и 16 384 столбцов. Строки в Excel помечены цифрами, а столбцы – буквами

от A до XFD. На пересечении строк и столбцов находятся ячейки, называемые в соответствии с их положением, то есть начиная с A1 и заканчивая XFD1048576 (вы можете менять названия ячеек на собственные значения). Последний элемент листа – это диапазон, представляющий собой набор ячеек.

Списки и диапазоны данных в Excel

Заполнить рабочий лист данными можно двумя способами. Первый – вручную перетащить курсор мыши. Операция в этом случае очень проста:

• просто введите данные в любую ячейку;
• щелкните по ней и, удерживая курсор в правом нижнем углу, перетащите в любом направлении.

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

Такой подход также работает для создания рядов данных, состоящих из чисел и дат. Однако, что касается чисел, если вы вводите значение только в одной строке, оно будет просто скопировано в следующую. Чтобы создать числовую последовательность, вам необходимо установить шаблон, который будет заполнен серией как минимум в двух ячейках, например, введя значения 5 и 10, следующие ячейки будут заполнены значениями, увеличенными на 5. Если вы вводите одну дату, после перетаскивания появятся следующие дни, а когда заполнены две или более ячеек, Excel автоматически распознает строку даты.

Нумерация созданного списка

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

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

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

Выпадающий список в Excel

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

Создать раскрывающийся список

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

• Создайте список в одном столбце или одной строке с данными, которые вы хотите отобразить в раскрывающемся списке;
• Выберите любую другую ячейку, в которой должен появиться список;
• В верхнем меню выберите вкладку Данные, а затем щелкните Проверка данных;
• Откроется окно с названием Проверка вводимых значений, в котором нужно развернуть список в элементе «Тип данных:» и выбрать Список, а в поле «Источник:» выбрать мышью ячейки, значение которых следует добавить в список;

• Щелкните ОК и закройте окно.

Редактирование списка

Вы можете редактировать список в любое время несколькими способами, в зависимости от ваших потребностей:

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

Удаление раскрывающегося списка

Чтобы удалить список в Excel, недостаточно нажать кнопку «Удалить». Для этого вам необходимо выполнить следующие действия:

• Выделите всю область из выпадающего списка;
• Перейдите на вкладку Данные и нажмите Проверка данных;
• В диалоговом окне выберите опцию Очистить все;
• Щелкните ОК.

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

Как удалить раскрывающийся список, не зная его местоположения? Для этого вам необходимо:

• Перейдите на вкладку Главная, нажмите «Найти и выделить» и после раскрытия меню нажмите Перейти → Специальные. Вы также можете сделать это с помощью сочетания клавиш Ctrl + G;
• Выберите параметр «Проверка данных», и будут выбраны все раскрывающиеся списки, доступные на листе.

Сортировка в Excel

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

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

Чтобы использовать функцию сортировки:

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

Чтобы отсортировать данные по нескольким критериям, нажмите настраиваемая сортировка и кнопку Сортировка и выберите, какие критерии следует учитывать в Excel.

Фильтрация в документах Excel

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

настроить фильтры в Excel.

Фильтрация скрывает строки с временно нерелевантными данными, оставляя видимыми только те, которые содержат важную информацию:

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

• Параметр Выделить всё установлен по умолчанию, снимите его и выберите только те данные, которые хотите отобразить;
• Нажмите ОК, и всё готово – будут отображаться только данные, выбранные в фильтре, остальные будут скрыты. Теперь вы можете, например, удалить их все, отредактировать или суммировать данные только из выбранных ячеек.

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

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

Чтобы использовать функцию условного форматирования:

• Выберите область, в которой находятся данные, которые вы хотите выделить. Если вы хотите выделить все данные в данном столбце, вам пригодится сочетание клавиш Ctrl + Shift + ↓, благодаря которому вы выберете все заполненные ячейки в столбце, вплоть до первой пустой;
• На вкладке Главная выберите Условное форматирование;
• Установите курсор в поле Правила выделения ячеек и выберите нужный вариант в появившемся меню.

Разделение текстов

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

К счастью, Excel имеет решение на такой случай – функция «Текст по столбцам» на вкладке Данные:

• Выделите ячейку или столбец, содержащий текст, который нужно разбить на части;
• Выберите функцию Текст по столбцам;
• Появится окно Мастер распределения текста по столбцам, выберите там пункт с разделителями;
• В следующем окне нужно выбрать разделители данных. Это означает, что, например, если данные, которые вы хотите разделить на отдельные ячейки, разделены пробелом, вы должны выбрать поле «Пробел». В предварительном просмотре данных вы увидите, правильно ли выполнено разделение;
• Затем вы выбираете формат данных столбца; здесь по умолчанию выбран общий формат, который распознает большинство типов данных. Кроме того, вы можете выбрать: текст, дату и возможность опустить столбец. В том же окне вы должны выбрать место назначения, куда должны быть помещены разделенные данные;
• Нажмите Готово, и всё готово.

Связь текстов в Excel

Если текст можно разделить, можно ли его объединить? Конечно, да. Вы можете объединить данные из нескольких ячеек в одну с помощью символа &.

Знак & позволяет комбинировать текстовые элементы без использования дополнительных функций. В этом случае в выбранной ячейке введите = → добавьте координаты ячейки → введите знак & → добавьте пробел в формате ” ” (если он нужен) → введите знак & → затем добавьте координаты другой ячейки, например, =A1&” “&B1 и нажмите Enter. Вы можете объединить сразу несколько ячеек.

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

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

Вопреки внешнему виду, их создание довольно просто:

• Чтобы подготовить сводную таблицу, вам нужно собрать данные в строках и столбцах;

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

• Затем выберите ячейки, из которых вы хотите создать сводную таблицу;
• Появится окно «Создать сводную таблицу», где появится диапазон таблицы (стоит проверить его ещё раз) и поле, в котором вы должны указать, где вы хотите разместить сводную таблицу. Вы можете поместить его на тот же рабочий лист, за пределы диапазона данных или на совершенно новый;

• Справа появится поле с полями сводной таблицы. В верхней части – окно со всеми названиями полей вашей базы данных (заголовки столбцов исходной таблицы), ниже – 4 окна для создания элементов сводной таблицы: фильтры, столбцы, строки, значения. Чтобы создать базовую сводную таблицу, переместите имена полей из верхней панели в одно из следующих четырех. Вы также можете сделать это, перейдя к параметрам сводной таблицы и на вкладке Отображение выберите «Классический макет сводной таблицы», который позволит вам перетаскивать данные прямо в представление таблицы.

Диаграммы в документах Excel

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

Создать диаграмму в Microsoft Excel очень просто:

• Отметьте данные для графика;
• Выберите вкладку Вставка и нажмите «Рекомендуемые диаграммы»;
• Откроется диалоговое окно, в котором вы можете выбрать тип диаграммы, которая вам подходит, а затем отобразится её предварительный вид;

• Выберите подходящий тип диаграммы, чтобы наилучшим образом визуализировать нужные вам данные;
• Щелкните кнопку ОК.

Полезные сочетания клавиш в Excel

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

Ниже приведены несколько примеров ярлыков:

• F2 – редактировать активную ячейку;
• Shift + 2 – редактирует комментарий в активной ячейке или вставляет комментарий, если в ячейке его нет;
• Ctrl + D – заполняет активную ячейку содержимым ячейки выше;
• Ctrl + R – заполняет активную ячейку содержимым левой ячейки;
• Alt + = – автосумма;
• Ctrl + Shift + ; – вставляет текущую дату;
• Ctrl + 1 – форматирование ячейки;
• Ctrl + Shift + 1 – включить числовое форматирование;
• Ctrl + Shift + 4 – установка денежного формата;
• Ctrl + Shift + ↓ – выделить диапазон данных в столбце до первой пустой ячейки;
• Ctrl + Z – отменить;
• F12 – Сохранить как.

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

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

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

Расширенные функции Excel [Единственное руководство по Excel на 2023 год]

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

Поиск цели

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

Давайте рассмотрим пример, чтобы лучше понять его.

Пример

В этом примере мы стремимся найти процентную ставку, если человек захочет заплатить 

5000 долларов в месяц для погашения суммы кредита.

Функция

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

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

• Формула PMT теперь должна быть введена в ячейку, которая находится рядом с платежной ячейкой. В настоящее время в ячейке процентной ставки нет значения, Excel дает нам платеж в размере 3 333,33 доллара США, поскольку предполагается, что процентная ставка равна 0%. Игнорируй это.

• Перейдите в раздел Данные > Анализ «что, если» > Поиск цели

• Нажмите OK. Вы увидите, что функция поиска цели автоматически дает процентную ставку, необходимую для выплаты суммы кредита.

Перейдите в раздел «Главная» > «Число» и измените значение на «Процент».

Ваш результат будет выглядеть следующим образом:

Анализ “что если” с помощью Solver

Анализ «что, если» — это метод изменения значений для опробования различных сценариев для формул в Advanced Excel.

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

Решатель идеально подходит для анализа “что, если”. Это надстройка в Microsoft Excel, полезная на многих уровнях. Эту функцию можно использовать для определения оптимального значения формулы в ячейке, известной как целевая ячейка. Однако некоторые ограничения или пределы применимы к другим значениям ячеек формул на листе.

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

Читайте также: Лучшее руководство по созданию информационной панели Excel

Активация надстройки Solver

• На вкладке “Файл” щелкните “Параметры”.
• Перейдите в «Надстройки», выберите «Надстройка Solver» и нажмите кнопку «Перейти».

• Проверьте надстройку Solver и нажмите OK.

•  На вкладке «Данные» в группе «Анализ» вы можете увидеть добавление параметра «Решатель».

Как использовать решатель в Excel

В этом примере мы попытаемся найти решение простой задачи оптимизации.

Проблема: Предположим, вы владелец бизнеса и хотите, чтобы ваш доход составлял 8000 долларов.

Цель: Рассчитать количество продаваемых единиц и цену за единицу для достижения цели.

Например, мы создали следующую модель:

• На вкладке Данные в группе Анализ нажмите кнопку Решатель.
• В заданной цели выберите ячейку дохода и установите для нее значение 8000 долларов.
• Чтобы изменить ячейку переменной, выберите ячейки C5, C6 и C10.

• Нажмите «Решить».

Ваша модель данных изменится в соответствии с условиями.

Если-иначе

Функция ЕСЛИ используется для проверки условия и возврата значения, если условие действительно истинно, и заранее определенного другого значения, если оно оказывается ложным.

Синтаксис: =ЕСЛИ(тест, истинный результат, ложный результат)

Если-Ошибка

Функция Excel ЕСЛИОШИБКА возвращает альтернативный результат, если формула выдает ошибку, и ожидаемый результат, если ошибки не обнаружено.

Синтаксис: =ЕСЛИОШИБКА (значение, значение_если_ошибка)

Например, Excel возвращает ошибку деления на ноль, когда формула пытается разделить число на 0.

С помощью функции ЕСЛИОШИБКА можно добавить сообщение, если формула дает ошибку.

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

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

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

Пример данных

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

Вставка сводных таблиц

Чтобы вставить сводную таблицу на лист, выполните следующие действия:

• Щелкните любую ячейку в наборе данных.
• На вкладке Вставка в группе Таблицы щелкните Сводная таблица.

Появится диалоговое окно. Excel автоматически выберет ваш набор данных. Он также создаст новый рабочий лист для вашей сводной таблицы.

• Нажмите “ОК”. Затем он создаст рабочий лист сводной таблицы.

Поле для перетаскивания

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

• Поле покупателя в область строк.
• Поле элементов в область значений.

Эксель VBA

VBA расшифровывается как Visual Basic Analysis. Excel VBA — это управляемый событиями язык программирования Microsoft для приложений Office. Макросы — это то, что использует большинство людей, пишущих код VBA.

Включить параметр разработчика в Excel

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

• Щелкните правой кнопкой мыши в любом месте на ленте, а затем выберите параметр «Настроить ленту».

• Перейдите в раздел «Настройка ленты» и установите флажок «Разработчик».

Интерфейс редактора VBA

Вы можете открыть интерфейс VBA с помощью сочетания клавиш ALT + F11 или перейти на вкладку «Разработчик» и щелкнуть Visual Basic.

Создание командной кнопки и назначение макроса командной кнопке

После включения вкладки разработчика и ознакомления с редактором VBA приступим к созданию макроса с помощью командной кнопки.

Чтобы разместить командную кнопку на рабочем листе, с которым вы работаете, вам необходимо выполнить шаги, указанные ниже:

• Перейдите на вкладку «Разработчик» > «Вставка» > «Элементы управления ActiveX» > «Командная кнопка».

• Перетащите командную кнопку на рабочий лист.

Чтобы назначить макрос командной кнопке, выполните следующие действия:

• Щелкните правой кнопкой мыши командные кнопки и выберите «Просмотреть код».

• Добавьте следующие строки кода, показанные ниже.

• Закройте редактор VBA и нажмите кнопку команды на рабочем листе. Не забудьте отменить выбор режима дизайна.

Индекс

и соответствие

Это расширенная функция Excel. Функция ПОИСКПОЗ предназначена для возврата позиции значения в указанном диапазоне, а функция ИНДЕКС возвращает конкретное значение, присутствующее в одномерном диапазоне.

Функция ПОИСКПОЗ возвращает позицию искомого идентификатора. Функция ИНДЕКС вернет значение зарплаты, соответствующее должности.

Функция смещения

Функция СМЕЩ возвращает ссылку на диапазон ячеек, который представляет собой заданное количество строк и столбцов из ячейки или диапазона ячеек.

Синтаксис: OFFSET(ссылка, строки, столбцы, [высота], [ширина])

Пример:

Рассмотрим следующие данные: 

 Для ссылки на C4, начинающейся с A1, ссылка – это A1, строки – 3, а столбцы – 2:

.

Функция СУММ со смещением

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

Функция OFFSET возвращает диапазон 1×2, 8 строк ниже ячейки A2 и 1 столбец справа от ячейки A2. Затем функция СУММ вычисляет сумму этого диапазона.

Получите опыт работы с новейшими инструментами и методами бизнес-аналитики с помощью магистерской программы для бизнес-аналитиков.