Лайфхаки в Ексель

Найдите 10 самых крупных отклонений — даже если часть ушла в минус В отчёте по закупкам отклонения от бюджета разнонаправленные: где-то потратили на 18 000 ₽ больше, а где-то — на 25 000 ₽ меньше. Если применить правило «Первые 10» прямо к столбцу отклонений, большие положительные значения окажутся выше отрицательных, и крупные отклонения в минус могут остаться незамеченными. Сравнивать нужно размер отклонения, а не его знак. Пусть в столбце A указана статья, а в B — разница «факт минус бюджет». Данные занимают строки 2–31. В C2 введите =ABS(B2) и протяните формулу до C31. ABS возвращает аб…

Один срез переключит сразу две сводные диаграммы На встрече просят показать продажи по месяцам, а потом — по категориям. Если для каждого графика выбирать регион отдельно, легко получить два разных отчёта. В Excel 2021 один срез может фильтровать несколько сводных таблиц и связанных с ними диаграмм. Главное — обе сводные таблицы должны использовать общий источник данных и совместимый кэш. Надёжный способ: сначала создайте одну сводную таблицу, затем скопируйте её и в копии измените расположение полей. Так у таблиц будет общий кэш. Постройте по диаграмме для каждой. Теперь добавьте срез «Ре…

Спрячьте подробности отчёта, оставив итог на виду На встрече руководителю нужен итог по отделу, а бухгалтеру — каждая операция. Делать две версии отчёта неудобно: подробности должны быть рядом, но не обязательно постоянно отображаться на экране. В Excel можно сгруппировать строки, чтобы временно убирать детали с экрана. Допустим, в строках 2–4 перечислены операции, а в строке 5 стоит итог. Выделите номера строк 2–4 слева, затем откройте «Данные» → «Группировать» в блоке «Структура». Если появится выбор, укажите «Строки». Слева от группы появится кнопка «−». Нажмите её: строки 2–4 скроются,…

Alt+= посчитает расходы — если проверить границы В конце встречи координатор собирает чеки: 1 250 ₽ за зал, 860 ₽ за воду, 430 ₽ за бейджи и 720 ₽ за доставку. Итог нужен сразу, а искать кнопку «Автосумма» в ленте не хочется. Разместите суммы в B2:B5, оставив B6 для итога. Поставьте курсор в B6 и нажмите Alt+=. Excel вставит формулу суммы и предложит диапазон — в этом примере =СУММ(B2:B5). Для перечисленных сумм результат — 3 260 ₽. Перед Enter взгляните на выделенные ячейки: именно этот диапазон попадёт в расчёт. Вот маленькая страховка: если Excel захватил лишнюю ячейку или пропустил част…

Поднять весь прайс на 10% — без формулы в каждой строке Поставщик прислал новые условия: цены нужно увеличить на 10%. В прайсе сотни позиций — менять каждую вручную долго и легко ошибиться. В Excel можно применить увеличение сразу ко всему диапазону цен. Сначала сделайте рабочую копию листа: этот приём заменит исходные значения. Допустим, цены стоят в B2:B50 и содержат обычные числа. В свободной ячейке введите 1,1 и скопируйте её. Выделите B2:B50, откройте «Главная» → «Вставить» → «Специальная вставка». В блоке «Операция» выберите «Умножить» и нажмите «ОК». Цена 250 станет 275, а 480 — 528…

Сумма расходов подозрительно мала? Возможно, числа стали текстом Выгрузили расходы, сложили столбец — а итог меньше ожидаемого. Суммы в ячейках выглядят нормально, но Excel может воспринимать их как текст и не включать в обычную сумму. Проверим на примере. Пусть в A2:A4 импортированы как текст значения «1200», «800» и «350». В свободной ячейке введите =СЧЁТ(A2:A4). Функция посчитает ячейки с числами. Если на экране видны все три суммы, а результат — 0, вероятно, значения хранятся как текст. Преобразуйте их в соседнем столбце, не затрагивая оригинал. В B2 введите =ЗНАЧЕН(A2) и протяните форм…

Статусы в нужном порядке: «Срочно» выше «Готово» В таблице задач десятки строк, и руководителю важно сразу замечать приоритетные дела. Алфавитная сортировка не знает, что «Срочно» должно стоять выше «В работе», а «Готово» — ниже. Зададим этот порядок один раз — без формул и ручной перетасовки строк. Сначала создайте пользовательский список в Excel 2021 для Windows: • Откройте Файл → Параметры → Дополнительно и в разделе «Общие» нажмите «Изменить списки…». • В поле «Элементы списка» введите статусы по одному в строке: Срочно, В работе, Готово. Нажмите «Добавить», затем «ОК». Теперь отсортиру…

Просрочка появится сама — без ежедневной проверки дат В списке задач всё спокойно. А завтра один срок уже окажется просроченным — и потеряется среди десятков строк. Зачем проверять даты глазами каждый день, если Excel умеет подсвечивать просрочку автоматически? ⚡ Допустим, в A — задача, в B — срок, в C — статус. Данные находятся в A2:C100. • Выделите A2:C100. • Откройте «Главная» → «Условное форматирование» → «Создать правило» → «Использовать формулу для определения форматируемых ячеек». • Введите формулу: =И($B2<СЕГОДНЯ();$C2<>"Готово";$B2<>"") • Выберите заметную заливку и сохраните прав…

ФИЛЬТР найдёт строки сразу по двум условиям Перед планёркой не хочется вручную просматривать сотню счетов, чтобы найти неоплаченные по нужному филиалу. В Excel 2021 для такой выборки доступна функция ФИЛЬТР. Например, нужно собрать неоплаченные счета только по филиалу «Север». В исходной таблице A — дата, B — филиал, C — статус, D — сумма. Заголовки в первой строке, данные — со второй по сотую. В свободную ячейку, например F2, введите: =ФИЛЬТР(A2:D100;(B2:B100="Север")*(C2:C100="Не оплачен");"Совпадений нет") Знак умножения здесь объединяет условия по логике «и»: в результат попадут только…

Отфильтровали заказы — почему сумма осталась прежней? В отчёте есть заказы с доставкой и самовывозом. Вы оставили фильтром только доставки, а итоговая сумма всё равно считает все заказы. 🤨 Ничего не сломалось: обычная СУММ учитывает и строки, которые фильтр временно скрыл. ⚡ Решение — ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Представьте таблицу A1:B6: Тип | Сумма Доставка | 120 Самовывоз | 80 Доставка | 150 Самовывоз | 60 Доставка | 90 Что делаем: • В ячейке B7 под таблицей вводим =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B2:B6). • Выделяем только A1:B6 и включаем «Данные» → «Фильтр». • В столбце «Тип» оставляем «Доставка».…