Условное форматирование для авто, которые НЕ подходят (зачёркивание)
Хочу себе такие же кнопки
Что вы получите от этого урока
Вы научитесь быстро визуально отделять из списка автотранспорта те единицы, которые не подходят под заданные параметры (грузоподъёмность, тип двигателя, возраст и т.п.). Для этого мы разберём условное форматирование в Excel/Google Sheets и покажем, как заставить программу зачёркивать строки, которые не удовлетворяют условию. После урока вы сможете:
- построить формулу‑критерий, проверяющую несколько условий одновременно;
- применить к ней стиль зачёркивания (strikethrough);
- настроить диапазон так, чтобы форматирование обновлялось автоматически при добавлении новых записей.
1. Основы условного форматирования
| Понятие | Описание | Пример |
|---|---|---|
| Диапазон | Ячейки, к которым будет применяться правило. | A2:E100 |
| Формула‑критерий | Выражение, которое возвращает TRUE/FALSE. |
=AND($C2<5,$D2>2015) |
| Стиль | Внешний вид ячейки при выполнении условия (цвет, шрифт, зачёркивание). | жирный, курсив, зачёркивание |
Условное форматирование – это «правило», которое проверяет каждую строку (или ячейку) и, если условие истинно, меняет её внешний вид. В логистике это удобно, когда нужно мгновенно увидеть, какие машины не подходят под текущий заказ.
2. Как построить правильный критерий
2.1. Обычные условия
- Грузоподъёмность (
Грузоподъёмность ≥ 3 т) →=$C2>=3 - Год выпуска (
Год ≤ 2020) →=$D2<=2020
2.2. Сочетание нескольких условий
Для «не подходит» нам нужны отрицательные проверки. Пример: машина не подходит, если одна из следующих проблем:
- Грузоподъёмность меньше 3 т;
- Год выпуска старше 2020;
- Тип двигателя не «дизель».
Формула в Excel/Google Sheets будет выглядеть так:
=OR($C2<3, $D2>2020, $E2<="Бензин")OR→ истинно, если любое из условий истинно.$фиксирует столбец, позволяя формуле «прокатываться» по строкам.
2.3. Пример полного диапазона
| № | Марка | Грузоподъёмность (т) | Год | Двигатель |
|---|---|---|---|---|
| 1 | Volvo | 3.5 | 2019 | Дизель |
| 2 | Scania | 2.8 | 2021 | Дизель |
| 3 | Mercedes | 4.0 | 2015 | Бензин |
| 4 | MAN | 3.2 | 2022 | Дизель |
Для диапазона A2:E5 формула =OR($C2<3, $D2>2020, $E2<="Бензин") отметит строки 2 и 3 как «не подходит».
3. Применение стиля зачёркивания
- Выделите диапазон – к примеру
A2:E5. - В Excel: Главная → Условное форматирование → Новое правило → Использовать формулу.
В Google Sheets: Формат → Условное форматирование → Формула. - Вставьте формулу из пункта 2.2.
- Нажмите Формат, перейдите во вкладку Шрифт и поставьте галочку Зачёркивание (Strikethrough).
- Сохраните правило.
Теперь любые строки, попавшие под условие, будут отображаться с перечёркнутым текстом, и вы сразу увидите, какие машины исключить из расчётов.
4. Динамический диапазон – «таблица»
Если вы постоянно добавляете новые записи, вручную менять диапазон неудобно. Есть два способа:
| Способ | Как реализовать |
|---|---|
| Таблица Excel | Выделите диапазон → Вставка → Таблица. Таблица автоматически расширяется, а условное форматирование привязывается к её столбцам (Таблица1[Грузоподъёмность]). |
| Диапазон с динамической формулой | В Google Sheets используйте =INDIRECT("A2:E"&COUNTA(A:A)). В Excel – =OFFSET($A$2,0,0,COUNTA($A:$A)-1,5). |
Оба метода гарантируют, что новые строки сразу получат нужный стиль.
5. Практические нюансы
| Проблема | Решение |
|---|---|
| Слишком много «зачёркиваний» | Уточните критерий: замените OR на AND или добавьте дополнительные условия. |
| Форматирование не применяется к новым строкам | Проверьте, что диапазон действительно динамический, либо скопируйте правило на новые ячейки. |
| Текст в ячейке слишком мелкий после зачёркивания | В настройках стиля можно одновременно увеличить размер шрифта или изменить цвет. |
| Нужен цвет в дополнение к зачёркиванию | В диалоговом окне формата укажите заполнение (например, светло‑серый) и зачёркивание одновременно. |
6. Пример реального кейса из логистики
Задача: У вас есть список 150 грузовиков. Нужно быстро отобрать те, которые не могут выполнить заказ на перевозку 4‑т тонного груза в 2023 году, если у них дизельный двигатель и год выпуска не старше 2022.
Шаги:
- Добавьте столбец «Подходит» с формулой:
=AND($C2>=4, $D2<=2022, $E2="Дизель") - Создайте условное форматирование с формулой
=$F2=FALSE(гдеF– столбец «Подходит»). - В стиле выберите зачёркивание и, например, светло‑красный фон.
Результат – все неподходящие машины сразу же становятся перечёркнутыми и визуально выделяются.
7. Часто задаваемые вопросы
-
Можно ли применить несколько разных стилей к одной строке?
Да. В Excel/Sheets можно создать несколько правил с разными приоритетами. Например, один стиль – зачёркивание, другой – красный фон. -
Как убрать условное форматирование?
Выделите диапазон → Условное форматирование → Очистить правила. -
Работает ли зачёркивание в мобильных приложениях?
В большинстве современных приложений (Excel Mobile, Google Sheets) стиль отображается корректно, но иногда требуется обновить страницу.
Практика для закрепления
-
Создайте таблицу из 10 машин с колонками:
Марка,Грузоподъёмность,Год,Двигатель. Примените условное форматирование, чтобы зачёркивать машины с грузоподъёмностью меньше 3 т. -
Модифицируйте правило: теперь нужно зачёркивать машины, если одновременно грузоподъёмность < 3 т и год > 2020. Как изменится формула?
-
Добавьте третий критерий – тип двигателя не «дизель». Составьте окончательную формулу, которая будет зачёркивать все машины, не удовлетворяющие любому из трёх условий.
-
Тестируйте динамический диапазон: добавьте в таблицу 5 новых строк. Убедитесь, что форматирование автоматически применилось к ним.
-
Сочетание стилей: сделайте так, чтобы неподходящие машины были зачёркнуты и имели серый фон. Какие шаги нужны в Excel/Sheets?
Подытожим
- Условное форматирование – ваш визуальный помощник в логистике.
- Формула
OR(илиAND) позволяет задать любой набор критериев. - Стиль зачёркивания делает «не подходящие» записи мгновенно заметными.
- Динамический диапазон гарантирует, что новые данные не останутся без визуального контроля.
Применяйте полученные навыки в ежедневных отчётах, и вы будете экономить часы ручной проверки, сосредотачиваясь на действительно важных решениях. Удачной работы!
Почему Excel без VBA быстрее внедрить в малой логистике
Структура исходных данных: что должно быть в таблице «Остатки на складах»
Структура исходных данных: таблица «Поступления товара»
Структура исходных данных: таблица «Отгрузки и продажи»
Создание умной таблицы (Ctrl+T) для автоматического расширения диапазонов
Правила именования складов и ячеек хранения
Формула СУММЕСЛИ для подсчёта остатков по конкретному товару
Формула СУММЕСЛИМН для расчёта остатков по товару + складу
Расчёт свободного объёма склада в паллето-местах
Создание справочника «Товары» с габаритами и весом единицы
Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Автоматический расчёт веса партии по ВПР + умножение
Автоматический расчёт объёма партии (длинаширинавысотаколичество)
Условное форматирование для подсветки отрицательных остатков (красным)
Условное форматирование для подсветки нулевых остатков (серым)
Условное форматирование для подсветки страхового запаса (жёлтым)
Создание выпадающего списка складов через «Проверку данных»
Создание выпадающего списка товаров через «Проверку данных»
Динамическая таблица остатков с выбором склада через выпадающий список
Формула ЕСЛИ для сигнала «Срочный заказ» при остатке ниже нормы
Создание простого дашборда остатков на одном листе
Таблица «Заявки на перевозку»: номер, дата, маршрут, вес, объём
Справочник автомобилей: тип, грузоподъёмность, объём кузова, расход топлива
Формула ВПР для подбора авто по грузоподъёмности (ближайшая сверху)
Дополнительная проверка: объём груза < объёма кузова
Формула И / ИЛИ для одновременной проверки веса и объёма
Условное форматирование для авто, которые НЕ подходят (зачёркивание)
Автоматическая маркировка «Газель» / «Фура» по весу
Расчёт минимального количества автомобилей для сборного груза
Расчёт коэффициента загрузки фуры (факт/максимум) в процентах
Сводная таблица для анализа частоты заказов по каждому маршруту
Гистограмма в сводной таблице для визуализации загрузки авто
Расчёт удельной стоимости перевозки на 1 кг или 1 куб. м
Расчёт остатков на завтра: остаток сегодня минус плановая отгрузка
Формула СЕГОДНЯ для контроля сроков годности (если скоропорт)
Условное форматирование для товаров с истекающим сроком годности
Создание журнала перемещений между складами
Расчёт транзитных остатков (товар в пути) формулой СУММЕСЛИМН
Общий доступный остаток = складской + транзитный
Расчёт точки заказа (мин. остаток под заказ новой партии)
Автоматический текст: «Заказать N единиц» через ЕСЛИ и округление
Проверка дубликатов в накладных через СЧЁТЕСЛИ
Создание понятной шапки с фильтрами (Автофильтр)
Защита листов от случайного изменения формул (без пароля на старте)
Настройка вывода процентов загрузки склада в условных значках (зелёный/жёлтый/красный)
Расчёт оборачиваемости товара (отношение продаж к среднему остатку)
Создание шаблона «Заявка водителю» с автоподстановкой данных из подобранного авто
Сохранение файла как шаблона (.xltx) для ежедневного копирования
Чек-лист из 5 шагов на каждый день: проверить отрицательные остатки, загрузку авто, подсветку страхового
Бонусный пункт: как не сломать формулы при вставке новых строк
АПТЕЧКА ДЛЯ СОБАКИ С ПОМОЩЬЮ МЕДИЦИНЫ
Часы на весь экран в высоком разрешении
Чат рулетка 2026: чаты с элементом неопределенности
Чат рулетка бесплатно
Чатрулетка: чат с новой личностью
Чай и кофе: два мира вкуса
Диагностика шин: Проверка давления и износа
Excel для логистики: бесплатный курс учёта остатков и подбора авто
Фототехника в цифровую эпоху
Игрушки для развития навыков самостоятельности
Инновационные подходы в управлении грузовыми перевозками на линиях Валберис
Немецкие авто: легковые и внедорожники
Общение с Аней в чате
Онлайн генератор паролей с символами
Оптимизация динамического контента GEO
Политика конфиденциальности и пользовательское соглашение
Сервер для affiliate-маркетинга: Безопасность, Скорость, Изоляция
Смешные телевизоры
Современные тенденции в российском автопроме
Средства IP видеонаблюдения
