15 основных формул и функций Excel с запросами Copilot

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

Изучите 15 основных формул и функций Excel: от очистки импортированных данных до анализа и сводных наборов данных. Каждый раздел содержит реальные примеры и Copilot в Действия Microsoft Excel для начала работы без запоминания синтаксиса.

Очистка данных для точных формул

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

• TRIM: удаляет лишние пробелы в начале и конце текстовой строки и сокращает несколько пробелов между словами до одного пробела. Вставка имен или идентификаторов из другой системы, а функция TRIM очищает несоответствия между интервалами, которые нарушают формулы ВПР и счетчики COUNTIF.

• CONCAT и TEXTJOIN: объединение текста из отдельных столбцов в одно поле. TEXTJOIN добавляет выбранный разделитель между значениями, что ускоряет создание полных адресов, меток продуктов или отображаемых имен из разделенных исходных данных.

• LEFT и RIGHT: вытяните фиксированное количество символов с любого конца текстовой строки. Используйте left, чтобы извлечь код региона из начала идентификатора продукта, или RIGHT, чтобы изолировать расширение файла или компонент даты в конце поля.

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

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

• Удаление дубликатов: удаляет повторяющиеся строки из выбранного диапазона за один шаг. Запустите его в списке контактов перед отправкой кампании, при объединенном экспорте клиента, чтобы получить уникальные записи, или в списке ключевое слово, извлеченном из нескольких инструментов SEO.

Попробуйте эти примеры запросов Copilot

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

Итоги, средние значения и счетчики в данных электронной таблицы

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

• СУММ: добавляет каждое значение в выбранном диапазоне из персональный планировщик бюджета, отслеживающий ежемесячные исходящие данные в балансовая выверка активов и обязательств.

• AVERAGE: вычисляет среднее в диапазоне. Отслеживание средних значений заказов за четверть; отчет о прибыли и убытках, среднее время отклика в группе поддержки или средняя оценка из столбца ответа на опрос.

• MIN и MAX: возвращают наименьшие и самые высокие значения в диапазоне. Свяжите их, чтобы выявить выбросы в наборе данных о расходах, проверка, что запись данных остается в пределах ожидаемых границ, или выявить лучших и худших исполнителей в столбце sales без сортировки всего листа.

• COUNT и COUNTA: СЧЁТ подсчитывает ячейки, содержащие числа; COUNTA подсчитывает любую непустую ячейку. Используйте COUNTA в столбце регистрации, чтобы узнать, сколько ответов поступило, или COUNT в столбце дохода, чтобы проверка, сколько транзакций было введено на данный момент.

• СЧЁТЕСЛИ: подсчитывает ячейки, соответствующие одному условию. Сколько счетов имеет состояние "Оплачено"? Сколько ответов на опросы выбран вариант B? ФУНКЦИЯ СЧЁТЕСЛИ возвращает число без необходимости фильтрации или сортировки.

• СУММЕСЛИ: суммирует значения в одном столбце на основе условия в другом. Сложить часы, зарегистрированные одним участником команды в расписании, общие затраты от одного поставщика в электронная таблица бизнес-бюджета , сумма запросов в службу поддержки, закрытых одним агентом в неделю, или общие расходы по категориям из заявки на поездку.

• РАНГА. EQ: возвращает позицию числа в списке, от максимального до самого низкого или обратного. Ранжируете продавцов по доходам, результаты кампании заказа по коэффициенту конверсии или создайте список лидеров из столбца результатов без изменения базовых данных.

Попробуйте эти примеры запросов Copilot

Какую формулу следует использовать для подсчета количества задач на этом листе, помеченных как завершенные? Щелкните, чтобы скопировать этот запрос Copilot

Условная логика и обработка ошибок

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

• ЕСЛИ: возвращает один результат, если условие имеет значение true, и другой результат, если оно равно false. Пометка просроченных счетов в средство отслеживания управления проектами , пометка результатов опроса как "Выше целевого объекта" или "Ниже целевого объекта" или пометка задач в Список действий как полный на основе значения в столбце состояния.

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

• AND и OR: объединяют несколько условий в один логический тест. И возвращает значение true только при выполнении каждого условия; ИЛИ возвращает значение true, если хотя бы один из них имеет значение . Вложение в формулу IF для создания более точных правил, таких как пометка записи только в том случае, если регион и состояние соответствуют целевому объекту.

Попробуйте эти примеры запросов Copilot

Я хочу, чтобы в столбце C отображалось значение "По пути", если значение в столбце B превышает 80 %, а значение "Требуется проверка", если это не так. Какую формулу следует использовать? Щелкните, чтобы скопировать этот запрос Copilot

Поиск и сопоставление данных

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

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

• XLOOKUP: выполняет поиск в любом направлении и может вернуть указанное значение, если совпадение не найдено, что делает его более гибким, чем ВПР. Доступно в Excel для Интернета.

• MATCH: находит позицию значения в диапазоне. Свяжите его с ИНДЕКСом, чтобы получить данные из любого столбца в таблице, включая столбцы слева от столбца поиска, куда не удается достичь ВПР.

• INDEX: извлекает значение из определенной позиции в таблице. В сочетании с MATCH он обрабатывает запросы, которые ни XLOOKUP, ни VLOOKUP не могут разрешать напрямую, например возвращает значение на основе двух условий соответствия в отдельных столбцах.

Попробуйте эти примеры запросов Copilot

Напишите формулу ВПР, чтобы сопоставить код продукта в столбце A с прейскурантом в столбцах D и E. Щелкните, чтобы скопировать этот запрос Copilot

Анализ и сводные данные

СУММЕСЛИ И сводные таблицы обрабатывают анализ в большом масштабе в Excel. SUMIFS вычисляет итоги для нескольких условий, а сводные таблицы группируют и сводные данные в представление, которое легко читать и делиться ими.

• Сводная таблица: суммирует и изучает большие наборы данных, группируя строки, вычисляя итоги и позволяя упорядочить представление без касания исходных данных. Использование сводной таблицы для сравнения ежемесячных продаж между регионами в таблица бизнес-бюджетирования , анализ трафика по источникам и устройствам или разделение расходов по категориям в Файл проекта на основе диаграммы Ганта. Пообщайтесь с Copilot, чтобы настроить сводную таблицу.

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

Попробуйте эти примеры запросов Copilot

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

Примечание. Для copilot в Excel требуется подписка На Microsoft 365 с включенным Приложением Copilot. Files необходимо сохранить в OneDrive или SharePoint с включенной функцией автосохранение. Copilot работает с файлами .xlsx, XLSB и XLSM.

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

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