Горячие клавиши Excel: топ-15 комбинаций, которые экономят часы работы

Каждый, кто хоть раз выгружал базу клиентов или отчет по продажам из 1С, Битрикс24 или AmoCRM, знает эту боль. Программа выдает файл, на который больно смотреть: ФИО клиента, телефон и адрес слеплены в одну ячейку, перед названиями компаний стоят какие-то невидимые пробелы, а артикулы склеились с датами.

Анализировать такую базу невозможно. Сводные таблицы сойдут с ума, а ВПР откажется искать совпадения.

Первый порыв — сесть и начать вручную перебивать данные в соседние колонки. Остановитесь. На приведение в чувство базы из пары тысяч строк уйдет весь день. В Excel есть пара блестящих инструментов, которые сделают эту грязную работу за пару минут.

Волшебная кнопка «Текст по столбцам»

Самая частая проблема: в выгрузке есть столбец «Клиент», где написано «Иванов Иван Иванович». А для загрузки данных в сервис рассылок вам нужно, чтобы Имя, Фамилия и Отчество лежали в трех разных колонках.

Инструмент «Текст по столбцам» решает это в три клика:

1. Создайте пару пустых столбцов справа от ваших данных (чтобы разделенному тексту было куда падать, иначе он затрет соседние данные).

2. Выделите колонку с ФИО.

3. В верхнем меню перейдите на вкладку Данные и нажмите кнопку Текст по столбцам.

4. Появится Мастер текстов. На первом шаге выбираем С разделителями и жмем «Далее».

5. На втором шаге Excel спросит: «Что именно разделяет ваши слова?». Ставим галочку напротив пункта Пробел (или «Запятая», если у вас в ячейке данные через запятую). В окошке предпросмотра вы сразу увидите, как программа аккуратно разрежет ФИО вертикальными линиями.

6. Жмем «Готово».

Бинго! Текст разлетелся по нужным столбцам. Вы прекрасны.

Убираем мусор: функция, которая спасет ваши формулы

Иногда выгрузки грешат фантомными пробелами. Они могут стоять в самом начале слова или в конце. Глазом их не видно, но для Excel слово «Договор» и « Договор » — это абсолютно разные сущности. Из-за этого перестают работать любые формулы поиска.

Чтобы быстро "сбрить" все лишние пробелы, используйте простейшую функцию =СЖПРОБЕЛЫ().

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

Хирургическая точность: вытаскиваем часть текста формулами

Бывает, что данные склеены намертво без пробелов или запятых, но в них есть четкая логика. Например, система выплюнула номер заказа в таком виде: ЗАК-987654-МСК. Вам нужны только шесть цифр из середины.

Здесь в игру вступают текстовые функции. Они работают как ножницы:

• ЛЕВСИМВ: отрезает кусок текста слева. Если мы напишем =ЛЕВСИМВ(A1; 3), Excel отщипнет ровно три первых символа из ячейки. В нашем примере он вернет ЗАК.

• ПРАВСИМВ: делает то же самое, но с конца строки. =ПРАВСИМВ(A1; 3) вернет МСК.

• ПСТР: самый точный инструмент. Вы говорите ему: с какого символа начать резать и сколько символов взять. Чтобы достать номер нашего заказа, мы пишем =ПСТР(A1; 5; 6). Это значит: «начни с пятого по счету знака (это девятка) и отрежь ровно шесть символов». Результат — чистый номер 987654.

Освоив комбинацию «Текст по столбцам» и этих простеньких формул, вы перестанете бояться сырых данных. Любая самая страшная выгрузка приводится в идеальный порядок быстрее, чем остывает ваш кофе.

Знакомая боль: вы собрали красивую таблицу, прописали сложные формулы, настроили сводные отчеты и отправили файл менеджерам, чтобы они просто внесли свои данные.

На следующий день файл возвращается, и всё сломано. Там, где в колонке «Город» должно было быть написано «Москва», один менеджер написал «Мск», другой — «г. Москва», а третий вообще промазал по клавиатуре и ввел «Москав». Из-за этого ваши формулы ВПР не находят совпадений, а сводные таблицы плодят дубликаты.

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

Шаг 1: Готовим "справочник" вариантов

Чтобы Excel понимал, какие варианты предлагать пользователю, их нужно где-то написать.

1. Создайте в вашей книге новый, пустой лист (можете назвать его "Справочник").

2. В столбик напишите все правильные варианты ответов. Например, если это статусы оплат, напишите друг под другом: Оплачено, Ожидает оплаты, Отмена.

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

Шаг 2: Привязываем список к рабочей таблице

Теперь идем в наш основной рабочий лист, где люди должны заполнять данные.

1. Выделите ячейки (или весь столбец целиком), в которых должен появиться выпадающий список.

2. В верхнем меню перейдите на вкладку Данные.

3. Найдите кнопку Проверка данных (в некоторых версиях Excel это просто маленькая иконка с зеленой галочкой и красным кружком). Нажмите на нее.

4. Откроется окно. На первой вкладке «Параметры» в поле «Тип данных» выберите Список.

5. Ниже появится строка «Источник». Кликните в нее, а затем перейдите на ваш лист "Справочник" и выделите мышкой столбик с заранее написанными вариантами.

6. Нажмите ОК.

Готово! Теперь при клике на ячейку рядом с ней появляется маленькая стрелочка. Нажимаешь на нее — выпадает аккуратный список.

Как жестко пресечь "отсебятину"

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

Чтобы таблица била по рукам за такие вольности, нам нужно немного докрутить настройки:

1. Снова выделите столбец со списком и нажмите Проверка данных.

2. Перейдите на третью вкладку — Сообщение об ошибке.

3. Убедитесь, что стоит галочка «Выводить сообщение об ошибке».

4. В поле «Вид» выберите Останов.

5. В поле «Заголовок» можно написать: Ошибка ввода!

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

Теперь, если кто-то попытается вписать в ячейку «Мск» вместо «Москва», Excel выдаст строгую табличку с вашим текстом и просто не позволит сохранить неправильное значение.

Прячем следы

Чтобы никто случайно не удалил ваши эталонные варианты, лист "Справочник" лучше скрыть. Кликните правой кнопкой мыши по ярлыку листа в самом низу экрана и выберите Скрыть. Лист исчезнет из виду, но выпадающие списки продолжат исправно работать.

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

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

Каждый день одно и то же. На эту механическую возню уходит 15-20 минут, которые за месяц складываются в полноценный рабочий день, потраченный впустую.

А теперь представьте, что всю эту последовательность действий можно сделать за одну секунду, просто нажав на кнопку. Это и есть макросы. И нет, вам не придется учить сложные языки программирования.

Что такое рекордер макросов (и почему это не страшно)

Слово "макрос" у многих ассоциируется с суровыми программистами, черными экранами и непонятным кодом на VBA. Но в Excel есть встроенный инструмент для обычных людей — макрорекордер.

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

Шаг 1: Достаем скрытую вкладку «Разработчик»

По умолчанию Microsoft прячет инструменты для макросов от новичков, чтобы они случайно ничего не сломали. Нам нужно их достать.

1. Откройте Excel и нажмите на вкладку Файл в левом верхнем углу.

2. Спуститесь в самый низ и выберите Параметры.

3. В появившемся окне слева нажмите Настроить ленту.

4. В правом столбике найдите пункт Разработчик (Developer), поставьте напротив него галочку и нажмите ОК.

Теперь в верхнем меню у вас появилась новая вкладка. Вы готовы к магии.

Шаг 2: Включаем запись и делаем магию

Допустим, наша цель — быстро отформатировать сырую выгрузку: удалить лишний столбец "А" и покрасить шапку.

1. Перейдите на новую вкладку Разработчик и нажмите кнопку Запись макроса.

2. В появившемся окне придумайте название вашему скрипту. Важно: название должно быть одним словом, без пробелов (например, "ФорматированиеОтчета"). Нажмите ОК.

3. С этого момента Excel записывает всё! Не делайте лишних кликов.

4. Выполните нужные действия: кликните правой кнопкой по столбцу А -> Удалить. Затем выделите первую строку, перейдите на Главную вкладку, сделайте текст жирным и залейте ячейки цветом.

5. Когда закончите, вернитесь на вкладку Разработчик и нажмите Остановить запись.

Поздравляем, вы только что написали свой первый программный код, даже не открывая редактор!

Шаг 3: Сохраняем макрос в удобную кнопку

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

1. Зайдите во вкладку Вставка -> Иллюстрации -> Фигуры и выберите любой прямоугольник.

2. Нарисуйте его прямо поверх таблицы, напишите на нем "Сделать красиво".

3. Кликните по этой кнопке правой кнопкой мыши и выберите Назначить макрос...

4. В списке выберите ваш "ФорматированиеОтчета" и нажмите ОК.

Готово! Теперь можете открыть новую, нетронутую выгрузку, скопировать туда эту кнопку (или просто запустить макрос через меню Разработчик) и нажать на неё. Excel мгновенно удалит столбец и покрасит шапку.

Важный нюанс: Файлы с макросами нужно сохранять в специальном формате. При сохранении выберите тип файла «Книга Excel с поддержкой макросов (.xlsm)»*, иначе вся ваша автоматизация сотрется при закрытии документа.