5 инструментов Excel, которые должны знать все, кто работает с электронными таблицами
2026-02-21 14:39 Diff
https://skillbox.ru/media/management/5-instrumentov-excel-kotorye-dolzhny-znat-vse-kto-rabotaet-s-elektronnymi-tablitsami/

#статьи

  • 17 окт 2023
  • 0

Потратьте 15 минут на чтение статьи и сэкономьте сотни часов на рутинных задачах в Excel.

Иллюстрация: Катя Павловская для Skillbox Media

Рассказываем обо всём, что нужно для руководства бизнесом, командой или проектом, — от SMM и SEO до корпоративных финансов.

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

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

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

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

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

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

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

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

Работа с функцией ОБЪЕДИНИТЬ
Скриншот: Excel / Skillbox MediaРабота с функцией ОБЪЕДИНИТЬ
Скриншот: Excel / Skillbox Media

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

Если вам нужна подробная инструкция по работе с функцией ОБЪЕДИНИТЬ, прочитайте эту статью Skillbox Media.

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

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

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

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

Создаём выпадающий список
Скриншот: Excel / Skillbox MediaСоздаём выпадающий список
Скриншот: Excel / Skillbox MediaСоздаём выпадающий список
Скриншот: Excel / Skillbox Media

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

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

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

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

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

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

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

Создаём сводную таблицу
Скриншот: Excel / Skillbox MediaСоздаём сводную таблицу
Скриншот: Excel / Skillbox MediaСоздаём сводную таблицу
Скриншот: Excel / Skillbox Media

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

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

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

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

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

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

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

Настройка функции ВПР
Скриншот: Excel / Skillbox MediaНастройка функции ВПР
Скриншот: Excel / Skillbox Media

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

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

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

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

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

Результат работы логической функции ЕСЛИ
Скриншот: Excel / Skillbox Media

В этой статье Skillbox Media подробнее рассказали о том, как работают и для чего можно применять логические функции Excel. На примерах показали, как запустить функции ИСТИНА, ЛОЖЬ, И, ИЛИ, НЕ, ЕСЛИ, ЕСЛИОШИБКА, ЕОШИБКА, ЕПУСТО.

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

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

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

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

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

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

Выгрузка данных из внешних источников с помощью Power Query
Скриншот: Excel / Skillbox Media

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

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

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

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

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

Курс-тренажёр: «Excel + «Google Таблицы» с нуля до PRO» Узнать о курсе