5 инструментов Excel, которые должны знать все, кто работает с электронными таблицами

Раньше Татьяна работала с данными в Excel вручную. Самостоятельно просматривала тысячи строк, искала и удаляла лишние пробелы, меняла регистр букв со строчных на прописные и переносила данные из одной таблицы в другую методом «Копировать» → «Вставить». Это занимало у неё целые рабочие дни и недели.

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

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

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

Функция ОБЪЕДИНИТЬ — соберёт данные из нескольких ячеек в одной

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

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

Как сделать. Выделить ячейку, в которую функция будет собирать значения. Открыть окно для построения функций и найти функцию ОБЪЕДИНИТЬ. Ввести аргументы функции: разделитель и значения, которые нужно объединить.

Например, если нужно объединить ячейки из диапазона B4:F4 и разделить данные из них запятой с пробелом, функция в строке ссылок будет такого вида: fx=ОБЪЕДИНИТЬ(", ";1;B4:F4).

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

Выпадающий список — ускорит заполнение однообразных таблиц

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

Когда нужен. Когда требуется много раз ввести повторяющиеся параметры — например, фамилии сотрудников в графике смен или наименования товаров в каталоге. С выпадающим списком не нужно вводить одни и те же значения несколько раз. Ещё уменьшается вероятность опечаток, и данные в таблице становятся единообразными.

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

В появившемся окне нажать «Параметры» → «Разрешить» и выбрать пункт «Список». Поставить курсор в поле «Источник» и выбрать диапазон со значениями для выпадающего списка — их вводили на втором листе. Дальше нужно нажать на кнопку «ОК» — выпадающий список готов.

Что будет, если не пользоваться. Вы будете вводить одни и те же данные несколько раз. А когда потеряете концентрацию, можете начать ошибаться и делать опечатки.

Сводная таблица — обработает тысячи строк и соберёт их в наглядный отчёт

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

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

Как сделать. Перейти во вкладку «Вставка» и нажать на кнопку «Сводная таблица». В появившееся окно внести два значения:

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

Excel создаст новый лист для сводной таблицы. Слева на листе будет область, где появится сводная таблица после настроек. Справа — панель «Поля сводной таблицы», на которой настраивается таблица.

Что будет, если не пользоваться. Придётся обрабатывать тысячи строк, проводить расчёты, анализ, группировать данные и оформлять отчёты вручную. Это долго. А в случае, когда данных слишком много, это становится практически нереальным.

Функция ВПР — переставляет данные из одной таблицы в другую, даже если они идут не по порядку

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

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

Как сделать. Выделить ячейку, куда функция ВПР должна перенести найденное значение. Нажать «Формулы» → «Вставить функцию» и в появившемся построителе формул найти функцию ВПР. Дальше нужно внести аргументы функции:

• Искомое значение — название ячейки с одинаковыми данными для обеих таблиц, в которых функция будет искать данные для переноса.
• Таблица — диапазон ячеек, из которого функция будет брать данные для искомого значения. В этот диапазон должны войти столбцы с искомым значением и со значением, которое нужно перенести в первую таблицу. Для правильной работы ВПР искомое значение всегда должно находиться в первом столбце диапазона.
• Номер столбца — порядковый номер столбца в первой таблице, в котором находится переносимое значение. Считается по принципу: номер 1 — самый левый столбец, 2 — столбец правее и так далее.
• Интервальный просмотр — условное значение, которое определяет, насколько точно будет работать функция. Если при поиске ВПР нужно точное совпадение, вводите 0. Если нужно приблизительное совпадение — 1.

Нажмите кнопку «Готово» — в выбранной ячейке появится найденное значение. Дальше можно протянуть это значение вниз до конца таблицы — функция найдёт и перенесёт оставшиеся значения.

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

Логические функции — проверят значения и объединят несколько формул в одной ячейке

Что делают. Проверяют, выполняются ли условия пользователя в выбранном диапазоне. Пользователь указывает критерии, которые нужно проверить, — функции проверяют и выдают результат: выполняется условие или нет.

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

Ещё логические функции используют при работе с другими функциями Excel, чтобы расширить их возможности. Например, если использовать ВПР и логическую функцию ЕСЛИ, можно настроить поиск сразу по двум критериям. Или объединить несколько формул в одном действии.

Что будет, если не пользоваться. Все условия придётся проверять вручную. При работе с большими базами данных это будет трудозатратно.

Что ещё важно изучить в Excel

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

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

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

Power Query и Power Pivot. Это надстройки Excel для обработки и анализа больших объёмов информации. С помощью них можно загружать в Excel данные из внешних источников — например, с интернет-страниц, из ERP-систем, программ «1С», PDF-файлов и так далее, — преобразовывать их в удобный вид, моделировать на их основе базу данных и работать с ней дальше.

Главный плюс этих надстроек в том, что в них нет ограничений по количеству строк. Excel позволяет работать только с 1 048 000 строк, а в Power Query и Power Pivot их может быть гораздо больше. При этом производительность программы не уменьшается.

Приходите изучать Excel в Skillbox

В Skillbox есть программа «Excel с нуля до PRO». Она подходит как новичкам в электронных таблицах, так и тем, кто уже работает в Excel, но хочет делать это на продвинутом уровне.

Курс ведут практики — сертифицированный тренер по MS Office Ренат Шагабутдинов и соавтор книги «Google Таблицы: это просто» Евгений Намоконов.

На курсе учат:

• быстро делать сложные расчёты — работать с формулами, использовать все важные функции;
• визуализировать данные — сортировать и фильтровать информацию, строить диаграммы, создавать сводные таблицы;
• строить прогнозы — обрабатывать массивы данных, рассчитывать результаты при разных условиях;
• работать с внешними источниками данных — импортировать, экспортировать и преобразовывать данные из других файлов, пользоваться Power Query и Power Pivot;
• настраивать макросы и скрипты — создавать собственные функции для решения нестандартных задач.

Во время учёбы студенты выполняют домашние задания и получают их разбор от экспертов. А ещё забирают готовые шаблоны и памятки для быстрой работы в Excel.