Работа в excel с функциями: Функции Excel (по категориям) — Служба поддержки Майкрософт

Содержание

Формулы и функции в Excel

Главная » Уроки MS Excel







Автор Антон Андронов На чтение 4 мин Опубликовано



  • Ввод формулы
  • Редактирование формул
  • Приоритет операций
  • Копировать/Вставить формулу
  • Вставка функции

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

Например, на рисунке ниже ячейка А3 содержит формулу, которая складывает значения ячеек А2 и A1.

Ещё один пример. Ячейка A3 содержит функцию SUM (СУММ), которая вычисляет сумму диапазона A1:A2.

=SUM(A1:A2)
=СУММ(A1:A2)

Содержание

  1. Ввод формулы
  2. Редактирование формул
  3. Приоритет операций
  4. Копировать/вставить формулу
  5. Вставка функции

Ввод формулы

Чтобы ввести формулу, следуйте инструкции ниже:

  1. Выделите ячейку.
  2. Чтобы Excel знал, что вы хотите ввести формулу, используйте знак равенства (=).
  3. К примеру, на рисунке ниже введена формула, суммирующая ячейки А1 и А2.

Совет: Вместо того, чтобы вручную набирать А1 и А2, просто кликните по ячейкам A1 и A2.

  1. Измените значение ячейки A1 на 3.

    Excel автоматически пересчитывает значение ячейки A3. Это одна из наиболее мощных возможностей Excel.

Редактирование формул

Когда вы выделяете ячейку, Excel показывает значение или формулу, находящиеся в ячейке, в строке формул.

    1. Чтобы отредактировать формулу, кликните по строке формул и измените формулу.
  1. Нажмите Enter.

Приоритет операций

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

Сперва Excel умножает (A1*A2), затем добавляет значение ячейки A3 к этому результату.

Другой пример:

Сначала Excel вычисляет значение в круглых скобках (A2+A3), потом умножает полученный результат на величину ячейки A1.

Копировать/вставить формулу

Когда вы копируете формулу, Excel автоматически подстраивает ссылки для каждой новой ячейки, в которую копируется формула. Чтобы понять это, выполните следующие действия:

  1. Введите формулу, показанную ниже, в ячейку A4.

  2. Выделите ячейку А4, кликните по ней правой кнопкой мыши и выберите команду Copy (Копировать) или нажмите сочетание клавиш Ctrl+C.

  3. Далее выделите ячейку B4, кликните по ней правой кнопкой мыши и выберите команду Insert (Вставить) в разделе Paste Options (Параметры вставки) или нажмите сочетание клавиш Ctrl+V.

  4. Ещё вы можете скопировать формулу из ячейки A4 в B4 протягиванием. Выделите ячейку А4, зажмите её нижний правый угол и протяните до ячейки В4. Это намного проще и дает тот же результат!

    Результат: Формула в ячейке B4 ссылается на значения в столбце B.

Вставка функции

Все функции имеют одинаковую структуру. Например:

SUM(A1:A4)
СУММ(A1:A4)

Название этой функции — SUM (СУММ). Выражение между скобками (аргументы) означает, что мы задали диапазон A1:A4 в качестве входных данных. Эта функция складывает значения в ячейках A1, A2, A3 и A4. Запомнить, какие функции и аргументы использовать для каждой конкретной задачи не просто. К счастью, в Excel есть команда Insert Function (Вставить функцию).

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

  1. Выделите ячейку.
  2. Нажмите кнопку Insert Function (Вставить функцию).

    Появится одноименное диалоговое окно.

  3. Отыщите нужную функцию или выберите её из категории. Например, вы можете выбрать функцию COUNTIF (СЧЕТЕСЛИ) из категории Statistical (Статистические).

  4. Нажмите ОК. Появится диалоговое окно Function Arguments (Аргументы функции).
  5. Кликните по кнопке справа от поля Range (Диапазон) и выберите диапазон A1:C2.
  6. Кликните в поле Criteria (Критерий) и введите «>5».
  7. Нажмите OK.

    Результат: Excel подсчитывает число ячеек, значение которых больше 5.

    =COUNTIF(A1:C2;">5")
    =СЧЁТЕСЛИ(A1:C2;">5")

Примечание: Вместо того, чтобы использовать инструмент «Вставить функцию», просто наберите =СЧЕТЕСЛИ(A1:C2,»>5″). Когда напечатаете » =СЧЁТЕСЛИ( «, вместо ввода «A1:C2» вручную выделите мышью этот диапазон.

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excel-easy.com/introduction/formulas-functions.html
Перевела: Ольга Гелих
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:




Полезные функции 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 для Интернета Дополнительно… Меньше

Вы можете создать формулу для вычисления значений на листе с помощью функции. Например, формулы =СУММ(A1:A2) и СУММ(A1,A2) используют функцию СУММ для сложения значений в ячейках A1 и A2. Формулы всегда начинаются со знака равенства ( = ).

  1. Щелкните ячейку, в которую вы хотите ввести формулу.

  2. Чтобы начать формулу с функцией, щелкните в строке формул или начните вводить формулу в ячейке.

    Совет: Список доступных функций см. в разделе Функции (в алфавитном порядке) или Функции (по категориям).

  3. После ввода аргументов формулы нажмите Enter, чтобы увидеть результат формулы в ячейке.

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

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

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

Данные

5

4

2

6

3

8

7

1

Формула

Описание

Результат

‘=СУММ(А:А)

Добавляет все числа в столбец A

=СУММ(А:А)

‘=СРЕДНЕЕ(A1:B4)

Усредняет все числа в диапазоне A1:B4

=СРЕДНЕЕ(A1:B4)

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 . Как следует из названия, он будет сортировать список.

Функция SORT запрашивает четыре аргумента:

  • Диапазон для сортировки
  • По какому столбцу сортировать диапазон по
  • В каком порядке сортировать диапазон (по возрастанию или по убыванию)
  • Сортировать ли строки или столбцы.

=СОРТИРОВКА(массив, индекс сортировки, порядок сортировки, по столбцу)

Это фантастика. И его можно использовать с предыдущим примером UNIQUE для сортировки названий продуктов по порядку.

Для этого нам нужно только указать диапазон для сортировки.

=СОРТИРОВКА(УНИКАЛЬНАЯ(B2:B15))

12. ФИЛЬТР

После функции СОРТИРОВКИ существует также функция фильтрации списка. Еще одна функция доступна только пользователям Microsoft 365 .

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

Функция ФИЛЬТР принимает три аргумента:

  • Диапазон для фильтрации
  • Критерий, указывающий, какие результаты возвращать
  • Что делать, если результаты не возвращаются.

=ФИЛЬТР(массив, включить, если пусто)

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

Читайте также: