Ошибки Н/Д, ЗНАЧ!, ССЫЛКА!: переводим с экселевского на русский и чиним таблицу

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

Каждый раз, когда вы отрываете руку от клавиатуры, берете мышку, целитесь курсором в нужную ячейку, а потом тянетесь в верхнее меню за нужной кнопкой — вы теряете 3-5 секунд. За день набегают минуты, за месяц — часы чистой рутины.

В Excel сотни горячих клавиш, но зубрить их все нет смысла. Мы собрали топ-15 самых полезных комбинаций (или «шорткатов»), которые закрывают 90% ежедневных болей при работе с рабочими базами и отчетами.

Навигация: перестаем крутить колесико мыши

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

• Ctrl + Стрелка (вниз/вверх/вправо/влево) — телепорт к краю таблицы. Курсор мгновенно перепрыгнет к последней заполненной ячейке в выбранном направлении.

• Ctrl + Home — мгновенный возврат в самое начало листа (в ячейку А1), как бы далеко вниз вы ни ушли.

• Ctrl + End — прыжок в самую последнюю ячейку, где есть хоть какие-то данные или форматирование.

• Ctrl + PageUp / PageDown — быстрое переключение между листами в книге. Забудьте про прицеливание мышкой в эти мелкие ярлычки внизу экрана.

Мгновенное выделение данных

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

• Ctrl + Shift + Стрелка (вниз/вверх/вправо/влево) — выделяет всё от вашей ячейки до конца таблицы (до первой пустой ячейки). Идеально для выделения целого столбца с данными под формулу.

• Ctrl + A — если вы находитесь внутри таблицы, комбинация выделит весь массив данных целиком. Нажмете второй раз — выделится весь лист.

• Shift + Пробел — выделить всю строку целиком.

• Ctrl + Пробел — выделить весь столбец целиком.

Магия редактирования и формул

Здесь собраны комбинации, которые превращают механическую работу в удовольствие.

• Alt + = (Знак равно) — автосумма. Встаньте в пустую ячейку под столбцом с цифрами и нажмите эту комбинацию. Excel сам напишет функцию СУММ и выделит весь столбец над вами. Останется только нажать Enter.

• Ctrl + D — скопировать вниз (от слова Down). Работает быстрее обычного Копировать/Вставить. Встаньте в пустую ячейку, нажмите комбинацию, и Excel скопирует в нее всё, что находится ровно на одну ячейку выше (включая формулы и цвет заливки).

• Ctrl + 1 — открывает меню «Формат ячеек». Самое важное меню для настройки границ, числовых форматов и выравнивания, теперь доступно в одно нажатие.

• F4 — повторяет последнее действие. Покрасили ячейку в желтый? Выделите другую и просто нажмите F4 — она тоже станет желтой. Добавили строку? F4 добавит еще одну. Это гениальная кнопка для однотипной работы.

Работа со структурой (строки, столбцы, фильтры)

Быстро прячем лишнее и наводим порядок на листе.

• Ctrl + Shift + L — мгновенно включает или выключает автофильтры в шапке вашей таблицы. Больше не нужно искать иконку воронки в меню.

• Ctrl + 0 (ноль) — скрыть выделенный столбец (чтобы не мешался перед глазами).

• Ctrl + 9 — скрыть выделенную строку.

Как это внедрить и не сойти с ума?

Не пытайтесь выучить весь список за один день. Выберите 3-4 комбинации, которые решают вашу самую частую задачу (например, Ctrl + Стрелки для навигации), напишите их на стикере и приклейте на монитор. Через неделю ваши пальцы будут нажимать их на автомате, и вы почувствуете себя Excel-ниндзя.

Каждый, кто хоть раз выгружал базу клиентов или отчет по продажам из 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 выдаст строгую табличку с вашим текстом и просто не позволит сохранить неправильное значение.

Прячем следы

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

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