Ехель возможности: Расширенные возможности Microsoft Excel с примерами и описанием функций

Новые возможности Excel 2021 для Windows

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

Примечание: Некоторые перечисленные ниже функции не включены в Excel LTSC 2021 для коммерческих клиентов. Каждая из этих функций будет иметь отметку, если она не применима к Excel LTSC 2021 для коммерческих клиентов.

Совместное редактирование

Вы и ваши коллеги можете открывать и работать над одной и той же книгой Excel. Это называется совместное редактирование. После совместного редактирования вы можете быстро увидеть изменения друг друга — за считанные секунды.

ПОДРОБНЕЕ

Примечание. Совместное редактирование недоступно в Excel LTSC 2021.

Улучшение совместной работы с помощью современных комментариев

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

ПОДРОБНЕЕ

Примечание. Современные комментарии недоступны в Excel LTSC 2021.

Узнайте, кто в вашей книге

Узнайте, кто еще работает вместе с вами и где они находятся в книге.

Примечание. Эта функция недоступна в Excel LTSC 2021.

Визуальное обновление

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

Примечание. Визуальное обновление недоступно в Excel LTSC 2021.

Посмотрите налево, посмотрите направо… XLOOKUP уже здесь!

Найдите все, что вам нужно, по строкам в таблице или диапазоне с помощью XLOOKUP​.

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

ПОДРОБНЕЕ

Функция ПУСТЬ

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

Как и переменные в программировании, функция LET выполняется с помощью синтаксиса формул Excel.

ПОДРОБНЕЕ

Динамические массивы

Напишите одну формулу и возвращайте массив значений.  Ускоряют вычисления и анализ с помощью шести
​
новых функций: FILTER, SORT, SORTBY, UNIQUE, SEQUENCEи RANDARRAY.

ПОДРОБНЕЕ

Функция ПОИСКПОЗX

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

Функцию XMATCH также можно использовать для возврата значения в массиве.

ПОДРОБНЕЕ

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

Создавайте настраиваемые представления на листе Excel, не нарушая работу других.

ПОДРОБНЕЕ

Примечание. Представления листа недоступны в Excel LTSC 2021.

Новый способ работы со средствами специальных возможностей.

Все средства, необходимые для создания содержимого, поддерживающего специальные возможности, объединены в одном месте, на ленте «Специальные возможности».

ПОДРОБНЕЕ

Примечание. Лента «Специальные возможности» недоступна в Excel LTSC 2021.

Увеличьте охваты вашего контента

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

Повышение производительности

Улучшена производительность, стабильность и скорость работы Excel.

Ускоряйте вычисления с помощью основных функций Excel, таких как СУММЕСЛИ, СЧЁТЕСЛИ и СРЗНАЧЕСЛИ.

Одновременное отображение нескольких листов

Больше не нужно отображать листы по одному. Отображайте несколько скрытых листов одновременно.

ПОДРОБНЕЕ

Узнайте, что нового в стоковых мультимедиа

Мы постоянно добавляем разнообразное мультимедиа-содержимое в коллекцию Office Premium Creative Content, чтобы вы могли выразить себя, например библиотеки стоковых изображений, значков и многое другое.

ПОДРОБНЕЕ

Находите то, что вам нужно с помощью Поиска (Майкрософт)

В верхней части приложений Microsoft Office в Windows вы найдете новое поле Поиска (Майкрософт). Это мощное средство помогает быстро находить то, что вам нужно, например текст, команды, справку и т.  д.

ПОДРОБНЕЕ

Сохранение изменений по мере внесения

Загрузите файлы в OneDrive, OneDrive для бизнеса или SharePoint Online, чтобы убедиться, что все обновления сохраняются автоматически.

ПОДРОБНЕЕ

Примечание. Автосохранение недоступно в Excel LTSC 2021.

Поддержка формата OpenDocument (ODF) 1.3

Теперь поддерживается формат OpenDocument (ODF) 1.3. Спецификация ODF 1.3 добавляет поддержку многих новых функций.

ПОДРОБНЕЕ

Обновленная вкладка «Рисование»

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

Упростите работу с рукописным вводом с помощью новых дополнений вкладки «Рисование»: Точечный ластик, Линейка и Лассо.

ПОДРОБНЕЕ

Краткие сведения о том, что есть в книге

Хотите узнать, насколько большими становятся ваш лист или таблица? Excel предоставляет статистику по книге, которая поможет вам.

ПОДРОБНЕЕ

Выберите идеальный цвет

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

Для любого свойства, в котором можно определить цвет, теперь можно ввести в поле Hex шестнадцатеричное значение цвета, например #0F4C81 или 444.

Попробуйте структуру стиля «Набросок»

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

Параметры «Кривая», «Рисованная» и «Рисованная кривая» можно найти в меню Формат фигуры > Линия > Набросок.

ПОДРОБНЕЕ

Развивайте навыки работы с Office

Обучение работе с Excel

Краткое руководство по началу работу с Excel

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

14.12.2020 admin Интересное 0

Главная  »  Интересное   »   Возможности Excel, которые будут полезны в работе

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

Что такое Excel

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

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

Прикладная программа облегчит жизнь очень многим специалистам. Научившись работать с ней, можно сократить время на расчёты и учёт, это сделает Excel автоматически. Чтобы правильно вносить данные и вводить формулы, желательно пройти курсы Excel онлайн https://online.videoforme.ru/business_school/excel-courses-web, и работать с таблицами.

Для чего применяют Excel в работе

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

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

Существует много вариантов применения таблиц:

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

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

Полезные формулы Excel

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

• Формула СУММ автоматически суммирует числовые значения заданной области, но игнорирует текстовый формат. Функция сложение (+) для диапазонов требует ручного ввода слагаемых, но её преимущество – преобразование слагаемого в число.
• ПОИСК служит для определения в строке с текстом позиции введённой комбинации и записывает их в числовом значении.
• ВРЕМЯ – опция, нужная для проведения временных расчётов, например, час прибытия транспорта в точку назначения.
• Обработка дат. Для этого используется целый комплекс – ГОД, МЕСЯЦ, ДЕНЬ, ДЕНЬНЕД.
• Текстовая категория ПРАВСИМВ И ЛЕВСИМВ – вычисляют количество символов в ячейке.
• Производная МУМНОЖ применима для решения задач алгебры.
• Среднее арифметическое вычисляется с помощью формулы СРЗНАЧ.
• Несколько показателей для расчёта амортизации – АПЛ, ПУО, ФУО, ДДОБ, АМОРУВ.
• Финансовый анализ проводит ЧПС, вычисляя, например, прибыль инвестора с вычетом выплат.
• СРЗНАЧЕСЛИ используется для выведения среднего арифметического по заданным критериям: продажа по группам товаров или сумма закупок.
• Для сортировки и упорядочивания остатков на складах применима практика ОСТАТ.
• Для работы с огромными массивами информации и выборки данных существует ИНДЕКС.
• Чтобы смоделировать процессы используют графическую опцию ОТРЕЗОК.
• Составление простых прогнозов обеспечит функция ТЕНДЕНЦИЯ.
• Экономические задачи решит формула ВПР – найдёт заданное значение в большом диапазоне, причём по другому значению, например, цену по идентификатору продукта.

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

Итог

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

Новые возможности Excel 2021 для Windows

Excel 2021 для Windows позволяет вам совместно работать с другими и легко анализировать данные благодаря новым возможностям Excel, включая совместное редактирование, динамические массивы, функции XLOOKUP и LET.

Примечание. Некоторые функции, перечисленные ниже, не включены в Excel LTSC 2021 для коммерческих клиентов. Каждая из этих функций отмечена, если она не применима к Excel LTSC 2021 для коммерческих клиентов.

Соавторство

Вы и ваши коллеги можете открывать и работать с одной и той же книгой Excel. Это называется соавторством. Когда вы работаете в соавторстве, вы можете быстро увидеть изменения друг друга — за считанные секунды.

УЗНАТЬ БОЛЬШЕ

Примечание : совместное редактирование недоступно в Excel LTSC 2021.

Лучшее сотрудничество с современными комментариями

Управляйте тем, когда вы отправляете комментарии своим соавторам, и работайте продуктивно благодаря единообразному комментированию в своих книгах и других приложениях Office.

УЗНАТЬ БОЛЬШЕ

Примечание : современные комментарии недоступны в Excel LTSC 2021.

Знайте, кто в вашей рабочей тетради

Посмотрите, кто еще работает вместе с вами и где они находятся в книге.

Примечание : эта функция недоступна в Excel LTSC 2021.

Визуальное обновление

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

Примечание : визуальное обновление недоступно в Excel LTSC 2021.

Посмотрите налево, посмотрите направо… XLOOKUP уже здесь!

Строка за строкой, найдите все, что вам нужно в таблице или диапазоне с помощью XLOOKUP​.

По умолчанию возвращать точные совпадения — указывать не нужно.

УЗНАТЬ БОЛЬШЕ

ПУСТЬ функция

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

Подобно переменным в программировании, оператор LET реализуется с помощью собственного синтаксиса формул Excel.

УЗНАТЬ БОЛЬШЕ

Динамические массивы

Напишите одну формулу и верните массив значений. Ускорьте расчеты и аналитику с помощью шести ​
больше новых функций: ФИЛЬТР, СОРТИРОВКА, СОРТИРОВКА, УНИКАЛЬНОСТЬ, ПОСЛЕДОВАТЕЛЬНОСТЬ и СЛУЧАЙ.

УЗНАТЬ БОЛЬШЕ

Функция XMATCH

Функция XMATCH ищет указанный элемент в массиве или диапазоне ячеек, а затем возвращает относительное положение элемента.

Вы также можете использовать XMATCH для возврата значения в массиве.

УЗНАТЬ БОЛЬШЕ

просмотров листа

Создавайте настраиваемые представления на листе Excel, не нарушая работу других.

УЗНАТЬ БОЛЬШЕ

Примечание : представления листа недоступны в Excel LTSC 2021.

Новый способ доступа к инструментам специальных возможностей

Лента «Специальные возможности» объединяет все инструменты, необходимые для создания доступного содержимого, в одном месте.

УЗНАТЬ БОЛЬШЕ

Примечание . Лента специальных возможностей недоступна в Excel LTSC 2021.

Увеличьте охват вашего контента

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

Повышение производительности

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

Ускорьте вычисления с помощью стандартных функций Excel, таких как СУММЕСЛИ, СЧЁТЕСЛИ и СРЗНАЧСЛИ​.

Показать много листов одновременно

Больше не нужно отображать по одному листу за раз — отображать сразу несколько скрытых листов.

УЗНАТЬ БОЛЬШЕ

Узнайте, что нового на стоковых носителях

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

УЗНАТЬ БОЛЬШЕ

Найдите то, что вам нужно, с помощью Microsoft Search

В верхней части приложений Microsoft Office в Windows вы найдете новое поле поиска Microsoft. Этот мощный инструмент поможет вам быстро найти то, что вы ищете, например текст, команды, справку и многое другое.

УЗНАТЬ БОЛЬШЕ

Сохраняйте изменения по мере их появления

Загрузите свои файлы в OneDrive, OneDrive для бизнеса или SharePoint Online, чтобы убедиться, что все ваши обновления сохраняются автоматически.

УЗНАТЬ БОЛЬШЕ

Примечание : Автосохранение недоступно в Excel LTSC 2021.

Поддержка формата OpenDocument (ODF) 1.3

Теперь мы включили поддержку формата OpenDocument (ODF) 1. 3. Спецификация ODF 1.3 добавляет поддержку многих новых функций.

УЗНАТЬ БОЛЬШЕ

Обновлена ​​вкладка Draw

Быстрый доступ и изменение цвета всех ваших инструментов для рисования в одном месте.

Упростите работу с чернилами с помощью новых дополнений на вкладке «Рисование»: Точечный ластик , Линейка и Лассо .

УЗНАТЬ БОЛЬШЕ

Получите краткую сводку того, что есть в вашей книге

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

УЗНАТЬ БОЛЬШЕ

Выберите идеальный цвет

Основываясь на ваших отзывах, мы добавили новое поле ввода в диалоговое окно Colors для шестнадцатеричных значений цвета. Нет необходимости преобразовывать шестнадцатеричные значения цвета в значения RGB.

Для любого свойства, для которого можно определить цвет, теперь можно ввести шестнадцатеричное значение цвета в поле Hex , например, #0F4C81 или 444.

Попробуйте эскизный стиль

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

Попробуйте параметры Изогнутая, От руки или Каракули в разделе Формат Форма > Линия > Эскизный стиль .

УЗНАТЬ БОЛЬШЕ

Развивайте свои офисные навыки

Обучение Excel

Краткое руководство по Excel

10 лучших функций для анализа данных в Excel

Электронные таблицы по-прежнему в моде! Использование электронных таблиц восходит к 1979 и по-прежнему являются одним из самых популярных способов просмотра и обработки данных.

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

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

1. Сводные таблицы и сводные диаграммы
2. Условное форматирование
3. Удаление дубликатов
4. XLOOKUP
5. IFERROR
6. MATCH
7. COUNTBLANK 9 0235
8. ДНИ и ЧИСТРАБДНИ
9. РАНГ
10. СУММПРОИЗВ

Вы можете щелкнуть любую функцию, чтобы перейти к ней.

Прежде чем мы начнем…

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

… и, если вы хотите посмотреть дальше…

Вот наш собственный старший научный сотрудник Том, объясняющий эти функции в видео:

1. Сводные таблицы и сводные диаграммы

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

Допустим, мы хотели подсчитать записи, сгруппированные по Семейному_статусу . Использование сводной таблицы делает это очень простым, поскольку она преобразует данные и объединяет их для нас.

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

Выбранный диапазон появится в поле Table/Range по умолчанию. Нажмите OK , и сводная таблица будет сгенерирована.

В полях сводной диаграммы перетащите Marital_Status из списка полей в поле Axis (Categories) . Затем перетащите Marital_Status из списка полей в поле Values ​​. Поскольку данные Marital_Status представляют собой строку, агрегация значений по умолчанию должна иметь значение Count 9.0006 . Если тип данных был числовым, агрегирование по умолчанию равно Sum .

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

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

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

Допустим, мы хотим выделить в наборе данных все значения Year_Birth , превышающие 1987 год.

Выберите столбец Year_Birth и нажмите Условное форматирование > Правила выделения ячеек > Больше . Появится редактор:

Введите 1987 и нажмите OK . Ячейки в столбце со значением больше 1987 станут светло-красными.

Если мы решим, что хотим изменить только что созданное правило условного форматирования, следуйте пути Условное форматирование > Диспетчер правил условного форматирования .

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

3. Удалить дубликаты

Данные часто беспорядочны, поэтому важно знать, как удалять дубликаты. Используя правила условного форматирования, вы можете выделить повторяющиеся данные, чтобы просмотреть их перед удалением. Удаление дубликатов 9Функция 0006 доступна в разделе Data > Data Tools > Remove Duplicates .

Выделите набор данных в Excel и нажмите Удалить дубликаты . Появится редактор удаления дубликатов:

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

Нажмите OK . Дубликаты будут удалены из набора данных. Он расскажет нам, сколько уникальных значений осталось.

4. XLOOKUP

XLOOKUP похож на комбинацию функций VLOOKUP и HLOOKUP, поскольку он может выполнять поиск значения в диапазоне как по вертикали, так и по горизонтали. По сути, это позволяет вам использовать выбранный диапазон в качестве таблицы поиска и возвращать «просмотренный» результат в ячейку. Синтаксис следующий:

=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])

Допустим, мы хотим найти Year_Birth на основе введенного Значение ID . В ячейке AD2 введите значение идентификатора, например 8755. В ячейке AE2 введите формулу XLOOKUP:

=XLOOKUP(

мы вводим AD2 .

lookup_array — это столбец или строка, содержащая искомое значение, поэтому мы вводим A2:A2241 , так как это даст нам массив идентификаторов. массив — это столбец или строка, содержащая значение, которое мы хотим вернуть, поэтому мы выбираем B2:B2241 , так как это даст нам значений Year_Birth .

Завершенная формула будет выглядеть следующим образом: =XLOOKUP(AD2, A2:A2241, B2:B2241)

Введите разные идентификаторы, и будет возвращен соответствующий Year_Birth .

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

5. ЕСЛИОШИБКА

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

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

Возвращаясь к функции XLOOKUP, если мы введем идентификатор в AD2 , который не существует в массиве поиска, AE2 выводит #Н/Д . Вместо этого давайте вернем «ID не найден». Для значения в функции ЕСЛИОШИБКА используйте функцию XLOOKUP. Это должно выглядеть так:

=ЕСЛИОШИБКА(XLOOKUP(AD2, A2:A2241, B2:B2241), «ID не найден»)

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

6. МАТЧ

Подобно функциям поиска, ПОИСКПОЗ можно использовать, когда вам нужна позиция значения в диапазоне вместо самого значения.

Это синтаксис для ПОИСКПОЗ:

=ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_сопоставления])

При написании функции важно знать, какой тип соответствия использовать. Хотя он не является обязательным и по умолчанию равен 1, доступны следующие варианты: -1, 0 или 1.

• -1: находит наименьшее значение, большее или равное 9.0005 искомое_значение .
• 0: Находит первое значение, точно равное искомое_значение .
• 1: Находит наибольшее значение, которое меньше или равно искомое_значение .

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

=ПОИСКПОЗ(AE2,B:B, 0)

7. СЧИТАТЬ ПУСТО

Функция СЧИТАТЬПУСТОТЫ довольно интуитивно понятна, но она важна для обработки данных в аналитике, поскольку многие алгоритмы машинного обучения чувствительны к нулям. Зная, сколько значений являются нулевыми, вы лучше понимаете, как к ним подходить. Например, если многие значения равны нулю, вы должны удалить столбец. Если несколько значений равны нулю, вы должны ввести значение, чтобы заполнить нулевое значение. СЧИТАТЬПУСТОТЫ подсчитывает количество пустых ячеек в диапазоне. Синтаксис прост:

=СЧЕТПУСТЫ(диапазон)

Мы хотим подсчитать количество нулей в столбце Доход , поэтому добавьте эту формулу в ячейку AG2 :

=СЧЕТПУСТОТЫ(E2: E2241)

8.

ДНИ и ЧИСТРАБДНИ

ДНИ и ЧИСТРАБДНИ — это отдельные функции, но они достаточно похожи, чтобы не требовать отдельных записей в моем списке. Функция ДНИ просто возвращает количество дней между двумя датами, тогда как функция ЧИСТРАБДНИ немного отличается тем, что исключает выходные и указанные праздники. Он возвращает только количество рабочих дней между двумя датами. Синтаксис обоих легко запомнить:

=ДНИ(дата_начала, дата_окончания)

=ЧИСТРАБДНИ(дата_начала, дата_окончания, [праздники])

На моей работе я анализирую данные об использовании. Я использую эти функции для таких вещей, как подсчет количества дней с тех пор, как пользователь вошел в систему или использовал инструмент. Поскольку наше программное обеспечение используется в школах, иногда имеет смысл исключить выходные и праздничные дни из наших данных об использовании, поэтому я благодарен за функцию ЧИСТРАБДНИ.

9. РАНГ

Функция РАНГ упорядочивает число по его размеру относительно других значений в списке и возвращает желаемый ранг. Это означает, что ранг числа будет его позицией, если список будет отсортирован по возрастанию или по убыванию. Например, отсортируйте доход в порядке убывания, чтобы наибольшее значение находилось вверху и могло иметь ранг 1. РАНГ присваивает повторяющимся числам один и тот же ранг, но учитывается кумулятивно. Это означает, что если два значения имеют ранг четыре, следующий ранг будет шести, а не пяти (1,2,3,4,4,6). Синтаксис RANK принимает 3 аргумента.

=РАНГ(число, ссылка, [порядок])

Важно отметить, что [порядок] может быть установлен на 0 для убывания и 1 (или выше) для возрастания.

Мы хотим ранжировать доход (столбец E) наших клиентов, поэтому мы будем использовать следующую формулу в ячейке Ah3 :

=РАНГ(E2, E2:E2241, 0)

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

10. СУММПРОИЗВ

Последняя функция в моем списке 10 лучших — СУММПРОИЗВ . Это здорово, когда вам нужно выполнить арифметические действия для определенных групп значений. Его можно использовать для сложения, вычитания, умножения или деления выбранных числовых значений для соответствующих диапазонов. Хотя это звучит сложно, логика довольно интуитивно понятна, как только вы попробуете. Синтаксис, однако, не очень интуитивно понятен.

=СУММПРОИЗВ(массив1, [массив2], [массив3], …)

Мы хотим просуммировать KidHome для всех строк, где Education равно Graduation (C2) и Marital_Status равно Single (D2) 90 006 . Мы добавим эту формулу в ячейку AI:

=СУММПРОИЗВ((C2:C2241=C2)*(D2:D2241=D2)*(F2:F2241))

Если мы хотим увидеть другое значение образования, например, кандидат наук , мы можем изменить C2 на C6 в формуле.

Резюме и следующие шаги

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