Быстрые ссылки
В Microsoft Excel есть сотни функций для создания эффективных таблиц. Но если хотите существенно прокачать свои навыки, познакомьтесь с этими шестью незаменимыми инструментами прямо сейчас.
Это краткий обзор ключевых возможностей Excel, которые должен знать каждый пользователь. Для более глубокого изучения каждой функции переходите по ссылкам "Похожие материалы" в отдельных разделах.
6 Таблиц Excel для Удобной Организации Данных
Лист Excel — это огромная таблица с ячейками, сгруппированными по столбцам и строкам, куда можно вводить данные или формулы. Но внутри неё можно создавать мини-таблицы, которые работают как отдельные организованные наборы данных.
Excel-таблицы предлагают встроенные функции сортировки и фильтрации, автоматически расширяются при добавлении новых строк, копируют формулы по столбцу и быстро подсчитывают итоги. Кроме того, имена столбцов можно использовать в формулах как структурированные ссылки, а также применять различные стили оформления.
Полный Гайд по Таблицам Excel — Почему Их Всегда Стоит Использовать
Этот навык преобразит вашу работу в Excel.
Чтобы создать таблицу с нуля, на вкладке "Вставка" нажмите кнопку "Таблица".
Затем подтвердите создание таблицы, нажав "ОК" в появившемся окне.
Дайте имя первой колонке и начинайте вводить данные.
Как только вы вводите данные в первую пустую ячейку под таблицей и нажимаете Enter, она автоматически включается в структуру таблицы. Аналогично, если добавить данные в новый столбец справа, оформление автоматически расширится.
Если данные уже есть, выделите любую ячейку в них, на вкладке "Главная" выберите "Форматировать как таблицу" и подберите стиль оформления.
Если в таблице есть заголовки, отметьте соответствующую опцию — иначе Excel создаст их автоматически.
После нажатия "ОК" ваши данные превратятся в аккуратно оформленную таблицу.
Если в таблице используются формулы с ссылками на другие столбцы, после преобразования их нужно заменить на структурированные ссылки. Например, формулу =SUM(A2+B2) стоит переписать так, чтобы в ней использовались имена столбцов вместо конкретных ячеек.
Важно также дать таблице понятное имя — это позволит ссылаться на неё в формулах и быстро находить через окно имён в левом верхнем углу экрана.
Почему Сейчас Самое Время Начать Переименовывать Таблицы в Excel
Имя — это не просто слово, а ключ к удобству.
Для этого кликните по любой ячейке таблицы, перейдите на вкладку "Конструктор таблиц", в блоке "Свойства" введите новое имя.
На вкладке "Конструктор таблиц" можно менять внешний вид и настройки таблицы: выбирать стиль оформления, добавлять итоговую строку или чередовать цвета, а также использовать слайсеры для удобной фильтрации.
Если вы раньше не пользовались Excel-таблицами, вы удивитесь, насколько они упрощают работу с данными.
5 Функций Поиска для Быстрого Доступа к Данных
Функции поиска — одни из самых популярных в моих таблицах. Они позволяют мгновенно находить значения в массивах данных и возвращать связанные с ними результаты.
Хотя в Excel подобных функций много, я чаще всего использую XLOOKUP, а также пару INDEX с MATCH — они просто незаменимы в повседневной работе. Вот несколько примеров.
Пример 1: Поиск Значения в Одномерной Таблице
В этом примере нам нужно найти итоговый балл игрока, имя которого указано в ячейке F2.
Для этого в ячейке G2 с помощью XLOOKUP напишите формулу:
=XLOOKUP(F2;A2:A7;B2:B7)
где
- F2 — имя игрока для поиска;
- A2:A7 — столбец с именами;
- B2:B7 — столбец с соответствующими баллами.
Нажмите Enter — и вы получите правильный итог — 9 очков.
Забудьте про VLOOKUP: Почему Я Предпочитаю XLOOKUP
Современный и удобный инструмент для поиска.
Та же задача с помощью INDEX и MATCH в ячейке G3 будет выглядеть так:
=INDEX(B2:B7;MATCH(F2;A2:A7;0))
где
- INDEX возвращает значение из указанного диапазона;
- MATCH ищет позицию имени в списке;
- аргумент 0 указывает на точное совпадение.
Как Работать с INDEX и MATCH в Excel
Альтернатива VLOOKUP с широкими возможностями поиска.
Обратите внимание: XLOOKUP проще и удобнее в написании. Она появилась позже, поэтому во многих случаях превосходит пару INDEX и MATCH для одномерных задач.
Пример 2: Поиск по Двум Критериям (Имя и Дата)
XLOOKUP и связка INDEX+MATCH позволяют искать данные по двум параметрам одновременно. В нашем примере нужно найти очки игрока за определённый день, учитывая имя в F6 (например, Майк) и день в G6 (день 2).
В ячейке H6 с помощью XLOOKUP используйте формулу:
=XLOOKUP(G6;B1:E1;XLOOKUP(F6;A2:A7;B2:E7))
Первая XLOOKUP ищет день среди заголовков столбцов, вторая — имя в столбце, затем функция возвращает нужное значение из массива.
Для INDEX+MATCH формула в ячейке H7 выглядит так:
=INDEX(B2:E7;MATCH(F6;A2:A7;0);MATCH(G6;B1:E1;0))
INDEX указывает на массив с результатами, первый MATCH ищет имя в столбце, второй — день в заголовках.
В таком случае формула с INDEX-MATCH получается короче, поэтому для двумерного поиска я предпочитаю её.
Пример 3: Поиск внутри Excel-таблиц
До этого примеры показывали поиск по диапазонам вне таблиц. Но эти функции отлично работают и в форматированных Excel-таблицах.
Задача — возвращать оценки студентов по их баллам.
Формула на XLOOKUP в ячейке C10 будет следующей:
=XLOOKUP(B10;Scores[Баллы];Scores[Оценки])
После нажатия Enter формула автоматически распространится на весь столбец.
Аналогично для INDEX и MATCH в ячейке C14:
=INDEX(Scores[Оценки];MATCH(B14;Scores[Баллы];0))
После ввода формула применяется к остальным строкам.
Обе функции принимают примерно одинаковое количество аргументов, а формулы похожи по длине. Однако XLOOKUP удобнее тем, что вы можете указать, что возвращать, если значение не найдено, поэтому я выбираю её чаще.
Сравнение INDEX+MATCH, VLOOKUP и XLOOKUP в Excel
Какой способ эффективнее искать нужные данные?
4 Условное Форматирование для Мгновенного Визуального Анализа
Можно знать все формулы, но если результаты сложно воспринимать — толку мало. Именно поэтому условное форматирование должно стать вашим незаменимым помощником. Эта функция меняет оформление ячеек в зависимости от заданных условий.
Почему Я Везде Использую Условное Форматирование
Простой способ сделать данные понятнее.
Представьте, что у вас продуктовый магазин, и нужно быстро понять, какая прибыль у каждого вида фруктов.
Хотя цифры видны в столбце E, они плохо воспринимаются. Выделите данные, на вкладке "Главная" выберите "Условное форматирование" — "Цветовые шкалы" и подберите градиент от зелёного к красному.
Теперь сразу видно, где значения выше, а где — ниже.
Как Быстро Применить Цветовую Шкалу В Excel
Сделайте восприятие данных мгновенно понятным.
Вы также можете создавать свои правила. Например, чтобы выделить целиком строки с прибылью выше 40 000 ₽, выделите весь диапазон и выберите "Условное форматирование" — "Создать правило".
В открывшемся окне выберите "Использовать формулу для определения форматируемых ячеек" и введите формулу:
=$E2>40000
Эта формула проверит значения в колонке E, начиная с ячейки E2, и выделит строки с прибылью больше 40 000 ₽.
Затем нажмите "Формат", выберите заливку, например, сиреневый цвет.
Подтвердите нажатием "ОК" — и строки с нужными значениями сразу выделятся.
Условное форматирование помогает акцентировать внимание на важных датах, значениях выше или ниже среднего и других параметрах.
Как Выделять Значения Выше или Ниже Среднего В Excel
Легко замечайте важные отклонения в данных.
3 Проверка Данных для Сохранения Точности и Предотвращения Ошибок
Собираетесь ли вы делиться таблицей или пользоваться ею самостоятельно, проверка данных помогает разрешать ввод в ячейки только корректных значений или типов. Это позволяет поддерживать порядок, снижать количество ошибок и экономить время.
Выделите нужные ячейки, перейдите на вкладку "Данные" и нажмите "Проверка данных". Если появится дополнительное меню, повторно выберите "Проверка данных".
По умолчанию можно вводить любые данные. В расширенном меню в поле "Разрешить" доступны ограничения: целые числа, десятичные, даты, время, длина текста и другие параметры с возможностью настройки.
Как Ограничить Ввод Данных В Excel с Помощью Проверки Данных
Держите данные в порядке и избегайте ошибок.
Если выбрать "Список", можно вручную указать разрешённые значения через запятую или сослаться на диапазон с ними.
После подтверждения в выделенных ячейках появится выпадающий список с вариантами.
Как Создать Выпадающий Список в Ячейке Excel
Удобный способ избежать ручного ввода повторяющихся значений.
В меню "Проверка данных" есть ещё две вкладки для тонкой настройки правил и сообщений об ошибках.
2 Power Query для Гибкой и Мощной Работы с Данными
Power Query — недооценённый, но очень полезный инструмент для всех — от новичков до опытных пользователей. Он позволяет импортировать и объединять данные из самых разных источников: веб-страниц, PDF, отдельных листов и многого другого, а затем трансформировать их под свои задачи.
Например, делить ячейки на части, исправлять ошибки, "распаковывать" скрытые таблицы, заполнять пустые строки данными из предыдущих и многое другое. Можно также легко объединять таблицы с одинаковой структурой из разных листов.
4 Команды Power Query для Освоения Обработки Данных
Станьте настоящим мастером управления данными.
Чтобы начать преобразование, выделите любую ячейку в диапазоне и на вкладке "Данные" нажмите "Из таблицы/диапазона".
Откроется редактор Power Query, где начинается подготовка данных. Вкладка "Преобразовать" содержит основные инструменты, а контекстное меню заголовков колонок даёт быстрый доступ к функциям.
Как Очистить и Импортировать Данные с Помощью Power Query
Не упустите возможности этого мощного инструмента Excel!
Когда закончите настройку, нажмите "Закрыть и загрузить" на вкладке "Главная", чтобы разместить результат на новом листе вашей книги.
Чтобы импортировать таблицу с сайта, перейдите на вкладку "Данные", выберите "Из Интернета" и вставьте URL в появившееся окно.
После нажатия "ОК" появится окно "Навигатор", где можно выбрать нужную таблицу для загрузки и перейти к её редактированию в Power Query.
Как Импортировать Таблицы с Веб-сайтов в Excel 365
Больше не нужно копировать вручную и рисковать ошибками.
1 Маски (Wildcards) для Поиска с Частичными Совпадениями
Маски — это специальные символы в Excel, которые заменяют неизвестные или любые символы при поиске, фильтрации и в формулах.
Есть два основных типа масок, которые помогают искать значения с "приблизительным" совпадением:
Как Использовать Маски Wildcards в Excel для Точного Поиска
Легко находите частичные совпадения.
Если нужно искать сами символы вопроса или звёздочки, поставьте перед ними тильду (~) — эта заставит Excel воспринимать их как обычные знаки.
Маски обычно применяют в окне поиска (Ctrl+F). Например, запрос *2??A найдёт два значения: UK255A и CAN267A.
Маски также пригодятся в формулах. Например, с помощью SUMIF можно сложить цены товаров из Великобритании по кодам с маской:
=SUMIF(A2:A100;"UK*";C2:C100)
Это сложит цены всех товаров, код которых начинается с "UK".
Как Работать с SUMIF в Microsoft Excel
Удобно подсчитывайте данные по условиям!
При использовании масок и текстовых значений в формулах всегда берите их в кавычки.
Наконец, после нажатия Ctrl+Shift+L для включения фильтров, в поисковом поле фильтра столбца А можно ввести:
*A
чтобы отобразить все коды, которые заканчиваются на букву "A".
Освоив эти простые приёмы, вы можете проверить свои знания, пройдя наши тесты по Excel для новичков, продолжающих и продвинутых пользователей.
На Дзен канале Герман Геншин доступна премиум подписка!Она дает доступ к уникальным материалам, которые недоступны в бесплатном доступе!✅ Отсутствие рекламы во всех статьях
✅ Доступ к эксклюзивным премиум материалам
✅ Возможность предлагать темы для статейПодробно о том, что входит в премиум подписку рассказал - здесь
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Вы также можете найти наши материалы в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru