Быстрая навигация
Хотите получить работу, где требуется экспертное владение Excel? Или расширить свои аналитические возможности? А может, просто научиться работать с данными более эффективно? Освойте эти пять инструментов Excel — и вы выйдете на совершенно новый уровень мастерства.
1 Таблицы Excel: приводим данные в порядок
Данные в Microsoft Excel могут храниться в обычном диапазоне или в таблице. Обычный диапазон — это просто набор ячеек без особой структуры. Таблица же — это продуманный объект с заголовками столбцов, полями, записями и множеством полезных функций.
Когда данные оформлены как таблица Excel, они сразу становятся понятнее. Вы можете легко фильтровать и сортировать информацию, а в формулах ссылаться на конкретные заголовки столбцов — это экономит время и повышает точность.
Чтобы превратить данные в таблицу, выделите весь диапазон (включая заголовки). Если данные расположены непрерывно (без пустых строк и столбцов), достаточно выбрать одну ячейку внутри диапазона. Затем на вкладке «Вставка» нажмите «Таблица» или используйте горячие клавиши Ctrl+T.
Обязательно проверьте, что у вас есть заголовки в верхней строке — это существенно упростит дальнейшую работу.
В диалоговом окне «Создание таблицы» убедитесь, что выделен нужный диапазон, поставьте галочку «Таблица с заголовками» и нажмите «ОК».
Готово! Перед вами красиво оформленная таблица с кнопками фильтрации в заголовках.
По умолчанию таблицы называются Таблица1, Таблица2 и так далее. Но если переименовать таблицу в группе «Свойства» на вкладке «Конструктор таблиц», это сделает файл удобнее для навигации, поможет пользователям программ чтения с экрана и упростит создание формул со ссылками на столбцы.
При именовании таблиц помните: имя должно начинаться с буквы, подчеркивания или обратной косой черты. Остальные символы могут быть буквами, цифрами, точками или подчеркиваниями. Имена должны быть уникальными, без пробелов и не совпадать с адресами ячеек вроде A1.
На той же вкладке можно изменить дизайн таблицы или настроить, какие элементы в неё включить.
Одно из главных преимуществ таблиц Excel — лёгкость добавления столбцов и строк. Посмотрите: как только я ввёл заголовок в ячейку F1 и номер игры в A12, Excel автоматически расширил таблицу для новых данных.
Чтобы вставить столбец или строку в середину таблицы, кликните правой кнопкой на ячейке, наведите на «Вставить» и выберите нужный вариант.
Взгляните на плюсы и минусы использования таблиц в Excel:
Преимущества таблиц Excel
Недостатки таблиц Excel
Формулы автоматически копируются на новые строки.
Не работают с формулами динамических массивов.
Упрощают сортировку и фильтрацию данных.
Слишком много таблиц в книге может замедлить работу.
Можно быстро отформатировать для лучшего восприятия.
Не подходят для нестандартных структур данных (впрочем, это скорее плюс — заставляют правильно структурировать информацию).
Имеют полезные дополнения: строка итогов, чередующиеся цвета.
Лучше совместимы с другими инструментами Excel: Power Query, диаграммы.
2 Power Query: импортируем и преобразовываем данные
Когда я открыл для себя Power Query в Excel, это полностью изменило мой подход к работе с данными. Этот инструмент подключается к одному или нескольким источникам данных и извлекает нужную информацию. Затем можно преобразовать и очистить полученные данные под свои потребности и загрузить результат на лист.
Начните с вкладки «Данные» на ленте и укажите Excel, где находится источник данных — выберите из видимых вариантов или через выпадающее меню «Получить данные».
Power Query может импортировать один набор данных (книгу Excel, таблицу с веб-сайта, базу данных) или несколько наборов — из разных листов одного файла или из нескольких книг Excel.
После выбора источника данных вы попадаете в редактор Power Query, где выполняется большая часть преобразований.
Например, на вкладке «Главная» можно добавлять новые источники, управлять параметрами, разделять столбцы, объединять запросы и выполнять множество других операций. На вкладке «Преобразование» — изменять существующие данные: разделять столбцы, транспонировать структуру, заменять значения, извлекать текст и многое другое.
После всех преобразований нажмите «Закрыть и загрузить» в верхнем левом углу. Нажатие верхней части кнопки автоматически загрузит данные на новый лист, нижней — откроет дополнительные варианты загрузки.
Чтобы таблица всегда отражала изменения в источнике данных, регулярно нажимайте «Обновить» на вкладке «Конструктор таблиц».
Изучите сильные и слабые стороны этого инструмента:
Преимущества Power Query
Недостатки Power Query
Удобен в использовании благодаря интуитивному интерфейсу.
Как и многие продвинутые инструменты, может потреблять много ресурсов. Применяйте фильтры на раннем этапе и сокращайте количество шагов.
Настоящая экономия времени — без него преобразование и очистка данных были бы мучительными и чреватыми ошибками.
Для максимального использования возможностей придётся освоить язык M — язык программирования Power Query.
Поскольку Power Query извлекает данные из источника, оригинал остаётся неизменным.
Гибко работает с данными разного размера из внешних источников.
3 Проверка данных: контролируем ввод информации
Проверка данных — это то, что должен знать каждый опытный пользователь Excel, независимо от того, где вы его применяете. Более того, помимо базового ограничения ввода данных, этот инструмент помогает создавать динамические диаграммы и каскадные выпадающие списки.
Выделите нужные ячейки и на вкладке «Данные» нажмите «Проверка данных». Если видите выпадающее меню, нажмите «Проверка данных» ещё раз.
В поле «Разрешить» выберите тип данных.
Например, выбрав «Целое число», вы увидите дополнительные поля для указания параметров.
А если выбрать «Список», можно ссылаться на ячейки или именованный диапазон, чтобы создать выпадающий список.
Кроме ограничения ввода, проверка данных позволяет добавить подсказку, которая появляется при выборе ячейки. Перейдите на вкладку «Сообщение для ввода» и заполните поля.
Нажмите «Обвести неверные данные» в меню проверки данных, чтобы выделить значения, не соответствующие правилам.
На вкладке «Сообщение об ошибке» можно настроить предупреждение, которое появляется при вводе неправильных данных.
Вот плюсы и минусы проверки данных в Excel:
Преимущества проверки данных
Недостатки проверки данных
Повышает точность, согласованность и эффективность работы.
Правила не абсолютно надёжны — ячейки можно переопределить, условия изменить.
Помогает другим понять вашу книгу, если планируете её передавать.
Нельзя использовать заголовки столбцов таблицы как источник списка. Но это можно обойти через именованный диапазон.
Создание правил, сообщений и предупреждений довольно простое.
При грамотном использовании становится незаменимой для принятия решений и анализа.
Помимо ограничения ввода в ячейки, правила проверки критически важны для динамических листов — панелей управления и форм, поскольку управляют интерактивными диаграммами и зависимыми выпадающими списками.
4 Сводные таблицы: обобщаем и анализируем большие массивы данных
В простейшем виде сводные таблицы сжимают и реорганизуют огромные наборы данных для мгновенного анализа. Ведь листы с сотнями строк и столбцов могут быть просто подавляющими.
Но когда вы изучите все возможности сводных таблиц, поймёте, что это невероятно мощный инструмент, способный на гораздо большее, чем кажется на первый взгляд.
Чтобы превратить таблицу Excel в сводную, выберите любую ячейку в диапазоне и на вкладке «Вставка» нажмите верхнюю часть кнопки «Сводная таблица».
Для создания сводной таблицы из внешнего источника нажмите нижнюю часть значка «Сводная таблица» и выберите «Из внешнего источника данных».
В диалоговом окне проверьте правильность диапазона или имени таблицы и решите, добавить сводную таблицу на текущий лист или создать новый.
Теперь в панели «Поля сводной таблицы» перетащите нужные поля в одну из областей:
Чтобы убрать поле из области, просто перетащите его из панели «Поля сводной таблицы».
После выбора полей можно изменить макет на вкладке «Конструктор» или получить доступ к продвинутым инструментам (временные шкалы, срезы) на вкладке «Анализ сводной таблицы».
С настроенной сводной таблицей можно быстро выполнять сложные задачи, на которые вручную ушло бы гораздо больше времени: отображать значения в процентах от общей суммы, фильтровать наибольшие значения, создавать индивидуальные отчёты на основе фильтров.
Плюсы и минусы сводных таблиц в Excel:
Преимущества сводных таблиц
Недостатки сводных таблиц
Позволяют обобщать, агрегировать, фильтровать и сортировать огромные наборы данных за секунды.
В старых версиях Excel требуют ручного обновления при изменении исходных данных.
Можно быстро менять строки, столбцы, фильтры и итоги для оптимального представления данных.
Сводные таблицы на основе больших массивов данных потребляют много памяти и могут заметно замедлить компьютер.
Имеют продвинутые опции фильтрации: срезы и временные шкалы, что ещё больше упрощает анализ.
Можно использовать для управления сводными диаграммами, дополнительно помогающими в визуализации данных.
5 XLOOKUP: ищем и извлекаем данные
Выбрать подходящие функции для изучения в Excel непросто — их очень много. Но если бы мне пришлось выбирать одну, это была бы XLOOKUP.
XLOOKUP ищет значение в диапазоне и возвращает соответствующее значение из другого столбца или строки, превращая ваш Excel в динамическую книгу с взаимосвязанными наборами данных. Вот синтаксис:
где
В этом примере я хочу, чтобы Excel нашёл ID сотрудника по имени в ячейке H1 и вернул результат в H2.
Для этого нужна следующая формула:
где
XLOOKUP — более мощная и гибкая альтернатива VLOOKUP (работает только с вертикальными данными) и HLOOKUP (только с горизонтальными). Более того, XLOOKUP можно использовать для двумерного поиска, возвращая значение на пересечении указанного столбца и строки.
Сильные и слабые стороны этой полезной функции:
Преимущества XLOOKUP
Недостатки XLOOKUP
Лучший способ вернуть значение на основе точного совпадения.
Доступна только в версиях Excel 2021 года и новее.
В отличие от других функций поиска, можно ввести аргумент если не найдено для обработки ошибок.
Другие функции поиска (особенно INDEX и MATCH) считаются более гибкими при работе с несмежными столбцами.
Работает с вертикальными и горизонтальными наборами данных.
Можно вкладывать XLOOKUP для двустороннего поиска.
Не требует сортировки данных.
Может возвращать несколько значений из одного поиска.
Чтобы проверить свою экспертность в Excel, попробуйте пройти наш продвинутый тест по Excel!
Microsoft 365 Personal
Microsoft 365 включает доступ к приложениям Office (Word, Excel, PowerPoint) на до пяти устройствах, 1 ТБ хранилища OneDrive и многое другое.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru