3.5. Условное форматирование

Информатика, 7 класс. Учебник: Кадыркулов Р. А., Рыскулбекова А. Д., Нурмуханбетова Г. К. (Алматыкітап баспасы, 2023). Раздел 3: Решение задач с помощью электронных таблиц. Страницы 72–78.

Чему научишься

• Объяснять, что такое условное форматирование и зачем оно нужно.
• Выделять в таблице ячейки по условию: больше, меньше, между, равно.
• Выделять самые большие и самые маленькие значения.
• Использовать гистограммы, цветовые шкалы и наборы значков.
• Управлять правилами: создавать, менять и удалять.

Разминка

• Как ты отбираешь важное из большого потока информации? Обычно так: ищу заголовки, ключевые слова, цифры, выделенное цветом или жирным. Мозг сам цепляется за то, что выделяется. Условное форматирование делает то же самое с таблицей.
• Как на глаз отличить самое большое и самое маленькое число в таблице? Раскрасить. Например, большие числа залить зелёным, а маленькие красным. Тогда хватит одного взгляда, а не чтения всех цифр подряд.

Разбираемся

Таблица, которая красится сама

Представь таблицу твоего времени в играх за неделю. Нужно быстро увидеть «тяжёлые» дни, когда ты сидел дольше двух часов. Можно искать глазами, а можно поручить это Excel.

Условное форматирование — это оформление ячейки (заливка, цвет текста, значок), которое включается само, когда значение в ячейке подходит под правило. Изменилось число, и цвет пересчитался сам. Руками ничего перекрашивать не надо.

💡 Обычное форматирование красит ячейку навсегда. Условное смотрит на число и решает, красить или нет. Поменял 90 на 300, и ячейка сама стала «тяжёлой».

Где искать: вкладка Главная → панель Стили → кнопка Условное форматирование. Там пять групп правил и три пункта управления.

Правила выделения ячеек

Это самая простая группа: «покрась, если ячейка…».

• Больше, Меньше — число больше или меньше того, что ты задал.
• Между — число попадает в промежуток.
• Равно — число точно такое же.
• Текст содержит — в ячейке есть нужное слово или буквы.
• Дата — дата попадает в выбранный период.
• Повторяющиеся значения — найдёт одинаковые ячейки.

⚠️ Частая ошибка. Ждать, что «Больше 120» покрасит и само число 120. Не покрасит. Чтобы покрасить и 120 тоже, возьми правило «Между» или поставь границу на единицу меньше (119).

Первые и последние значения

Иногда не важна граница, а нужен рейтинг: «покажи трёх лидеров». Для этого есть группа Правила отбора первых и последних значений:

• 10 первых элементов — самые большие. Цифру 10 можно поменять на любую, например на 3.
• 10 последних элементов — самые маленькие.
• Первые 10 % и Последние 10 % — верхняя и нижняя доля списка.
• Выше среднего и Ниже среднего — сравнение со средним значением столбца.

В игровой таблице «3 первых элемента» покрасит Сб (240), Вс (200) и Пт (180). «3 последних» покрасит Пн (90) и оба дня по 120: Вт и Чт, потому что значения равны и делят одно место.

📌 Сначала выделяешь диапазон, потом выбираешь правило. Без выделения программа не поймёт, где искать лидеров.

Гистограммы, цветовые шкалы, наборы значков

Эти три инструмента показывают не «да или нет», а «насколько много».

• Гистограмма рисует внутри ячейки горизонтальную полоску. Чем больше число, тем длиннее полоска. Это как мини-диаграмма прямо в таблице.
• Цветовая шкала плавно меняет цвет заливки от одного цвета к другому (бывает из двух или из трёх цветов). Например, от белого к зелёному: чем темнее, тем больше.
• Набор значков ставит рядом с числом картинку: стрелки, флажки, светофор, «сигнал связи». Excel делит диапазон значений на равные части по числу значков в наборе, и каждой части достаётся свой значок.

Для набора из трёх стрелок диапазон 90–240 делится примерно на три части: до 140 минут, от 140 до 190, и больше 190. Тогда значки расставятся так.

📕 В учебнике сказано, что средняя цена книги в таблице равна 1700 тенге. Это неверно: сумма цен 17 650, цен десять, поэтому среднее 1765 тенге. Кроме того, на рисунке со стрелками у самой дорогой книги (2100) стоит красная стрелка вниз, а у самой дешёвой (1400) зелёная вверх, хотя в тексте сказано наоборот.

🌟 Excel считает проценты не от нуля, а от наименьшего до наибольшего значения в выделенном диапазоне. Поэтому границы между значками зависят от самих данных: добавил в таблицу очень большое число, и стрелки пересчитались.

Управление правилами

В том же меню есть ещё три пункта.

1. Создать правило — открывает окно, где ты сам задаёшь условие и оформление.
2. Удалить правила — убирает правила у выделенных ячеек или со всего листа.
3. Управление правилами — открывает «Диспетчер правил»: там список всех правил, их можно изменить или удалить.

💡 Бесплатная замена. В Google Таблицах есть похожая возможность: Формат → Условное форматирование. Там есть правила «Больше чем», «Текст содержит», градиент для цвета, а вот гистограмм и значков в ячейках нет. Для «трёх лидеров» нужна своя формула, например =B2>=НАИБОЛЬШИЙ(B2:B8;3).

👨‍👩‍👧 Родителю. Попросите ребёнка сделать таблицу расходов семьи или карманных денег за месяц и покрасить траты больше определённой суммы. Так навык решает реальную задачу, а не остаётся упражнением. Если Excel нет, подойдут Google Таблицы.

Проверь себя

• Чем условное форматирование отличается от обычной заливки ячейки? Заливка красит навсегда. Условное форматирование включается только тогда, когда число подходит под правило, и меняется вместе с данными.
• Можно ли покрасить ячейки с числами строго больше 50? Можно: выделить диапазон и выбрать «Правила выделения ячеек → Больше…», вписать 50.
• Чем «10 первых элементов» отличается от «Больше»? «Больше» сравнивает с числом, которое ты задал сам. «10 первых элементов» ищет лидеров по рейтингу, число-границу программа находит сама.
• Зачем нужны цветовые шкалы? Чтобы по плавному изменению цвета сразу видеть, где числа большие, а где маленькие.

Решаем упражнения

Таблица для практической работы: названия учебников, цена в тенге и количество в библиотеке. Данные берём с рисунка в учебнике.

Практическая работа

№ 1 · «Практическая работа», задание 1

В столбце «Цена» выдели книги, цена которых выше 1800 тенге.

Как думать. «Выше» значит строго больше, значит нужно правило «Больше…».

Как сделать.

1. Выдели ячейки C2:C11.
2. Главная → Условное форматирование → Правила выделения ячеек → Больше…
3. Впиши 1800, выбери оформление (например, светло-красная заливка) и нажми OK.

Ответ: закрасятся Алгебра (2000), Биология (2100), География (2000) и История Казахстана (1900). Физика (1800) остаётся без цвета.

№ 2 · «Практическая работа», задание 2

В столбце «Количество» выдели первые 5 книг с наибольшим количеством.

Как думать. Нужен рейтинг, а не граница, значит группа «первых и последних значений». Число 10 заменим на 5.

Как сделать.

1. Выдели D2:D11.
2. Главная → Условное форматирование → Правила отбора первых и последних значений → 10 первых элементов…
3. В окне замени 10 на 5, выбери зелёную заливку, нажми OK.

Ответ: выделятся 110 (Казахский язык), 100 (Информатика), 90 (Алгебра), 80 (Геометрия) и 78 (Биология).

№ 3 · «Практическая работа», задание 3

Раскрась столбец «Количество» от большего значения к меньшему.

Как думать. Плавная раскраска «от большего к меньшему» — это либо гистограмма, либо цветовая шкала. Раньше выделение было по правилу, здесь нужно показать все значения.

Как сделать.

1. Выдели D2:D11. Если есть старое правило, можно сначала убрать его через Условное форматирование → Удалить правила.
2. Условное форматирование → Цветовые шкалы и выбери любую шкалу, например зелёно-жёлто-красную. Или Гистограммы и выбери синюю.
3. Больше не нужно ничего вводить.

Ответ: все числа столбца получат цвет по размеру: у 110 самый «большой» цвет (или самая длинная полоска), у 25 самый «малый».

№ 4 · «Практическая работа», задание 4

В столбце «Цена» выдели дорогие и дешёвые книги.

Как думать. Нужны два правила на одном столбце: самые дорогие и самые дешёвые. Лучше взять «первых и последних», например по три.

Как сделать.

1. Выдели C2:C11.
2. Правила отбора первых и последних значений → 10 первых элементов…, замени 10 на 3, цвет — зелёная заливка.
3. Снова выдели столбец и выбери 10 последних элементов…, замени 10 на 3, цвет — светло-красная заливка.
4. Можно вместо этого выбрать Набор значков со стрелками, чтобы видеть и «средних».

Ответ: зелёным будут 2100, 2000, 2000, красным 1400, 1500, 1600. Книги по 1650, 1700, 1800 и 1900 остаются без цвета.

Синтез и оценка

№ 5 · «Синтез»

Приведи два конкретных примера использования видов условного форматирования.

Как думать. Вспомни свою жизнь: оценки, игры, деньги, спорт.

Пример ответа. 1) В таблице времени в играх правило «Больше 120» красит дни, когда я засиделся. 2) В таблице оценок класса набор значков «стрелки» показывает, у кого рейтинг растёт, а у кого падает.

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

№ 6 · «Оценка»

Назови преимущества стиля «условное форматирование».

Как думать. Подумай, что даёт цвет вместо чтения чисел.

Пример ответа. Таблицу видно с первого взгляда, проблемные места находятся мгновенно. Цвет пересчитывается сам, когда меняются данные. Иногда полоски гистограммы заменяют отдельную диаграмму.

Ответ: наглядность, скорость анализа, автоматическое обновление при изменении данных, иногда замена диаграммы.

Вопросы для размышления

№ 7 · «Вопросы для размышления», вопрос 1

Что такое условное форматирование?

Как думать. Скажи, что оно делает и когда включается.

Пример ответа. Это оформление ячеек (заливка, цвет текста, значок, полоска), которое включается автоматически, когда значение ячейки подходит под заданное правило.

Ответ: автоматическое оформление ячеек по правилам, зависящим от их значений.

№ 8 · «Вопросы для размышления», вопрос 2

Из каких частей состоят инструменты условного форматирования?

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

Пример ответа. Это пять групп: правила выделения ячеек; правила отбора первых и последних значений; гистограммы; цветовые шкалы; наборы значков. Плюс пункты управления: создать, удалить и управлять правилами.

Ответ: правила выделения ячеек, правила отбора первых и последних значений, гистограммы, цветовые шкалы, наборы значков, а также создание, удаление и управление правилами.

№ 9 · «Вопросы для размышления», вопрос 3

Какие виды опций имеет стиль «условное форматирование» и как описываются их функции?

Как думать. Перечисли группы и скажи по одной фразе о каждой.

Решение.

• Правила выделения ячеек — красят ячейки, где значение больше, меньше, между, равно, содержит текст, дату или повторяется.
• Правила отбора первых и последних значений — находят самые большие и самые маленькие значения, проценты, а также значения выше или ниже среднего.
• Гистограммы — рисуют в ячейке полоску, длина которой зависит от числа.
• Цветовые шкалы — плавно меняют цвет заливки в зависимости от значения (два или три цвета).
• Наборы значков — вставляют рядом с числом значок: стрелку, флажок, светофор.

Ответ: пять видов (выделение ячеек; первые и последние значения; гистограммы; цветовые шкалы; наборы значков), их функции описаны выше.

№ 10 · «Вопросы для размышления», вопрос 4

Объясни методы вставки значков в опции «Набор значков».

Как думать. Что делает Excel с диапазоном чисел, когда ты выбираешь набор?

Решение. Выделяешь ячейки и выбираешь набор значков. Excel делит весь диапазон значений на равные части, по числу значков в наборе. Каждому значению достаётся значок той части, куда оно попало. Для трёх значков граница делит диапазон примерно на «нижнюю, среднюю и верхнюю» трети.

Ответ: диапазон делится на столько частей, сколько значков в наборе, и каждое число получает значок своей части.

№ 11 · «Вопросы для размышления», вопрос 5

Как осуществляется выбор цветов ячеек в опции «Цветовые шкалы»?

Как думать. Что изменяется с ростом числа?

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

Ответ: цвет ячейки зависит от значения: от цвета минимума к цвету максимума через плавные оттенки.

№ 12 · «Вопросы для размышления», вопрос 6

Чем условное форматирование удобно для пользователя?

Как думать. Вспомни пользу из «Оценки».

Пример ответа. Можно сразу увидеть важное, не читая все числа. Цвет сам меняется при изменении данных. Иногда не нужно строить отдельную диаграмму.

Ответ: наглядно, быстро, автоматически обновляется, иногда заменяет диаграмму.

Задание

№ 13 · «Задание»

Выполни проект «Успеваемость класса»: по образцу создай таблицу с оценками учеников за первую четверть и качеством знаний, оформи её правилами условного форматирования и представь как проект.

Как думать. Образец в учебнике — таблица с 7 предметами, рейтингом (сумма оценок) и «качеством» в процентах. У образца рейтинг и процент можно получить формулами: рейтинг = сумма оценок по строке, качество = рейтинг / 35, потому что 7 предметов по 5 баллов дают 35. Покажу на трёх учениках с придуманными оценками.

Решение. Следуем шагам выше. Формулы для строки 3 с образцом: J3: =СУММ(C3:I3) даёт 33, K3: =J3/35 даёт 0,942857…, что в процентном формате с одним знаком выглядит как 94,3%. Для строки 4: 27 и 77,1%. Для строки 5: 24 и 68,6%. Оформление проекта — на твой вкус: главное, чтобы использовалось несколько видов условного форматирования.

Ответ: таблица с формулами рейтинга и качества и тремя-четырьмя видами условного форматирования (гистограммы, цветовые шкалы, значки). Образец показывает: 33 → 94,3%, 27 → 77,1%, 24 → 68,6%.

Домашнее задание

№ 14 · «Домашнее задание», задание 1

В Туркестане на Дне здоровья школьники бегали 100 м и прыгали в длину. Нужно найти лучшие результаты и выделить победителя забега красным цветом, а победителя прыжков синим.

Как думать. Тут ловушка. В беге побеждает тот, у кого времени меньше, значит нужно «последнее» значение. В прыжках побеждает тот, у кого результат больше, значит «первое». И в обоих случаях элемент всего один.

Как сделать.

1. Выдели B2:B10. Условное форматирование → Правила отбора первых и последних значений → 10 последних элементов…, замени 10 на 1. В списке оформления выбери «Светло-красная заливка» (или «Красная заливка», если есть).
2. Выдели C2:C10. 10 первых элементов…, замени 10 на 1. Для синего цвета в списке оформления выбери Пользовательский формат… и на вкладке «Заливка» задай синий цвет.
3. Проверь: красной станет одна ячейка в столбце бега, синей одна ячейка в столбце прыжков.

Ответ: в забеге побеждает Данабек С. (6,9 с), красная ячейка B7. В прыжках побеждает тоже Данабек С. (2,9 м), синяя ячейка C7.

Шпаргалка

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

Мини-тест