Excel на первый взгляд прозрачен: набрал цифры, нажал плюс, получил сумму. Но стоит файлу подрасти до пары сотен строк, как начинаются фокусы — формулы выдают ерунду, столбец с числами отказывается складываться, фильтр «не видит» очевидного. Глюки тут почти ни при чём: в девяти случаях из десяти виноваты типовые промахи старта. Разберём главные ошибки в Excel и способы их обойти — тем более что в пособиях вроде «Excel для начинающих» о них пишут мельком, а на практике спотыкаются все.
Калькулятор рядом с клавиатурой: ручной счёт вместо формул
Классика жанра: человек складывает числа на калькуляторе и вбивает итог в ячейку руками. Данные обновились — результат устарел, и замечают это случайно, когда цифры в отчёте уже разошлись с реальностью. Базовое правило работы с таблицами: всё, что можно вычислить, должно считаться формулой. Для большинства задач хватает СУММ, СРЗНАЧ и ЕСЛИ (функция условия — «если значение больше N, то одно, иначе другое»), а мастер функций — значок fx слева от строки формул — подскажет синтаксис и назначение аргументов.
Родственная привычка — перепечатывать формулы в Excel вручную для каждой строки. Протяните ячейку за маленький квадратик в правом нижнем углу — автозаполнение размножит формулу вниз за секунду, без опечаток и пропущенных скобок.
Формула «съезжает»: относительные и абсолютные ссылки
Знакомая ситуация: в первой строке всё считается верно, а после копирования вниз — бред. Причина в том, что ссылки по умолчанию относительные: при копировании они сдвигаются вместе с формулой. Это удобно, когда каждая строка должна ссылаться на соседнюю. Но если в расчёте участвует, скажем, курс валюты из отдельной ячейки, адрес надо зафиксировать — сделать абсолютную ссылку, то есть адрес, который не меняется при протягивании. Ставится знак доллара: $A$1. Быстрее — выделить адрес в формуле и нажать F4, доллары подставятся сами.
Забавно, что про F4 многие узнают спустя годы и потом расстраиваются: сколько минут ушло на ручную расстановку долларов.
ВПР возвращает #Н/Д, хотя значение «вон оно»
ВПР — функция поиска: она находит заданное значение в одном столбце и подтягивает данные из соседнего. Незаменима, когда надо подставить цену по артикулу или ФИО по табельному номеру. И именно с ней новички бьются чаще всего — формула упорно выдаёт #Н/Д («не найдено»), хотя совпадение видно глазами.
Три виновника почти всегда одни и те же:
- число сохранено как текст в одной из таблиц — внешне одинаково, а для Excel это разные типы данных;
- лишний пробел в конце значения — лечится функцией СЖПРОБЕЛЫ, которая убирает лишние пробелы;
- диапазон поиска не закреплён знаком доллара и «уезжает» при копировании формулы вниз.
Проверить тип данных просто: у настоящего числа содержимое прижимается к правому краю ячейки, у текста — к левому. Заодно стоит запомнить соседей #Н/Д: ошибка #ЗНАЧ! означает, что формула пытается считать текст как число, а #ССЫЛКА! — что удалили строки или столбцы, на которые она ссылалась.
Зелёные треугольники и капризы формата ячеек
Маленький зелёный уголок в левом верхнем углу ячейки — не украшение, а предупреждение: программа считает содержимое текстом, хотя выглядит оно как число. Такие «псевдочисла» не попадают в сумму, ломают сортировку и фильтры. Появляются они по-разному: выгрузка из учётной системы, пробел после цифры, апостроф в начале. Быстрое лечение — выделить столбец и выбрать «Преобразовать в число» из меню предупреждения.
Формат ячеек подкидывает и другие сюрпризы. Даты внутри Excel хранятся как числа, поэтому «01.02» может превратиться то ли в 1 февраля, то ли остаться текстом — зависит от настроек. А денежный формат с округлением отображения часто путают с реальным округлением: на экране 10,00, внутри — 9,98, и итоги «не сходятся на копейки».
Объединённые ячейки: красиво ровно до первого фильтра
Шапка с объединением ячеек выглядит солидно, пока не понадобятся сортировка, фильтр или сводная таблица — инструмент, который сворачивает большие массивы данных в наглядные отчёты за пару кликов. Объединённые ячейки ломают все три операции, а заодно мешают автозаполнению. Тот же эффект дают пустые строки «для воздуха» внутри таблицы: Excel перестаёт понимать, где заканчивается диапазон.
Рабочее правило: одна строка — одна запись, шапка сверху, никаких разрывов. Оформлять лучше границами, заливкой и условным форматированием, а не разрушением структуры.
Сортировка «выбором»
Ещё один способ разнести данные по разным углам файла — выделить один столбец и отсортировать только его. Если согласиться на «сортировать в пределах выделенного диапазона», фамилии останутся на своих строках, а суммы уедут к чужим людям. Выделение при сортировке связной таблицы нужно расширять всегда.
Формулы молчат: циклы и ручной пересчёт
Отдельная история — циклическая ссылка: формула ссылается сама на себя, напрямую или через цепочку других ячеек. Excel предупреждает об этом при вводе, но окно легко закрыть не глядя — и файл начинает считать непонятно что. Полный список проблемных адресов виден в разделе «Формулы → Проверка наличия ошибок».
Вторая причина «застывших» расчётов — включённый ручной режим пересчёта. Его иногда активируют случайно через настройки или наследуют из чужого файла. Симптом один: меняешь исходные данные, а итоги не двигаются, пока не нажать F9.
Привычки, которые экономят часы
Помимо грубых промахов есть набор мелочей, отличающих уверенного пользователя от новичка:
- закрепление областей — шапка остаётся на виду при прокрутке длинной таблицы;
- Ctrl + стрелка — прыжок к краю заполненного диапазона вместо бесконечной прокрутки;
- Ctrl + T — превращение диапазона в «умную таблицу», то есть фрагмент, который Excel воспринимает как единый объект: формулы и фильтры там настраиваются сами;
- проверка данных — настройка, не пускающая в столбец заведомо неверные значения, например текст туда, где ждут дату;
- двойной клик по маркеру автозаполнения — формула протягивается до конца данных без всякого мышечного труда.