Модуль 6 · Урок 6.1
Таблица, оформление и сводные
Оформление как таблицы, условное форматирование, сводная таблица и диаграммы.
В таблице сто покупок по городам и товарам. Какой город принёс больше всего выручки? В каких строках сумма больше 50? Формулами курса ты ответишь на оба вопроса, а Excel умеет ещё и показать ответ: подсветить строки, собрать итоги перетаскиванием полей, нарисовать диаграмму. Оформление помогает увидеть данные, а вычисляет по-прежнему формула.
Урок дополнительный: экзамену он не нужен, задачи основных уроков решаются без него. Опирается он на знак $ (урок 2.2) и сумму по условию (урок 3.3).
«Таблица» Excel
«Таблица» Excel — диапазон, который знает свои заголовки: у него фильтры, полосы строк и формулы по именам столбцов. Выдели таблицу покупок вместе с заголовками и нажми Ctrl
Формула стоимости внутри такой таблицы выглядит иначе: =[@Цена]*[@Количество] вместо =B2*C2. Знак @ значит «в этой строке»: цена и количество берутся из строки, где стоит формула. Excel сам копирует её на весь столбец.
![Окно Excel, вкладка «Конструктор таблиц»: таблица покупок A1:D5 оформлена как «Таблица» с синими заголовками и кнопками фильтра, выбрана D3, в строке формул =[@Цена]*[@Количество]; метки 1 и 2](/images/lessons/excel-sheet/excel-smart-table.webp)
Тонкости:когда обычные адреса удобнее
В экзаменационных файлах обычно простой диапазон, и формулы с адресами вроде B2 там понятнее: видно, какие ячейки участвуют и как сдвинется копия. Имена столбцов удобны в рабочих таблицах, которые растут.
Внутри списка записей не объединяй ячейки и не вставляй строки итогов: они мешают сортировке, фильтру и формулам. Итоги — под таблицей или на другом листе.
Условное форматирование
Условное форматирование красит ячейки по правилу, а правило пишется формулой для верхней левой ячейки выделения. Выдели диапазон и выбери «Главная → Условное форматирование → Создать правило → Использовать формулу». Чтобы подсветить строки A2:F4, где сумма больше 50, правило — =СУММ($A2:$F2)>50.
Пролистай, как Excel применяет правило к каждой ячейке:
- Выдели A2:F4 и задай правило формулой. Её пишут так, будто она стоит в верхней левой ячейке A2: =СУММ($A2:$F2)>50.
- Каждая ячейка получает правило как копию формулы. В B2 оно то же самое: столбцы закреплены знаком $, поэтому вся строка проверяет одну сумму.
- Номер строки без $ сдвигается: строка 3 проверяет свою сумму 33. Строка 2 с суммой 51 уже закрашена.
- Закрашены строки, где сумма больше 50: 2 (51), 4 (54). Поменяй число — правило пересчитается, а заливка, поставленная вручную, осталась бы на месте.
Правило работает как формула, скопированная на весь диапазон: без $ перед буквами ячейка B2 проверяла бы СУММ(B2:G2), и строка покрасилась бы пятнами. Цвет при этом не меняет значения: посчитать подсвеченные строки можно только формулой — признаком и суммой признаков.
Сводная таблица
Сводная таблица группирует записи и считает итог каждой группы — то же, что СУММЕСЛИ для каждого значения. Нужна выручка по каждому городу. Выдели таблицу с заголовками, выбери «Вставка → Сводная таблица» и размести её на новом листе. Перетащи поле «Город» в область «Строки», поле «Стоимость» — в «Значения». Проверь, что для стоимости выбрана «Сумма», а не «Количество»: если числа записаны текстом, Excel предложит считать записи.
Поле «Товар» в области «Столбцы» превращает сводную в разрез: каждая ячейка — сумма для одного города и одного товара. Формулы дают те же числа и годятся для сверки:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Город | Товар | Стоимость | Город | Выручка | ||
| 2 | Москва | Чай | 300 | Москва | 700 | ||
| 3 | Казань | Кофе | 450 | Казань | 650 | ||
| 4 | Москва | Кофе | 250 | Омск | 120 | ||
| 5 | Омск | Чай | 120 | Всего | 1470 | ||
| 6 | Казань | Чай | 200 | ||||
| 7 | Москва | Какао | 150 |
G2скопирована до G4=СУММЕСЛИ($A$2:$A$7;F2; $C$2:$C$7) G5=СУММ(G2:G4)
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Город | Товар | Стоимость | Город | Чай | Кофе | Какао | |
| 2 | Москва | Чай | 300 | Москва | 300 | 250 | 150 | |
| 3 | Казань | Кофе | 450 | Казань | 200 | 450 | 0 | |
| 4 | Москва | Кофе | 250 | Омск | 120 | 0 | 0 | |
| 5 | Омск | Чай | 120 | |||||
| 6 | Казань | Чай | 200 | |||||
| 7 | Москва | Какао | 150 |
F2скопирована до H4=СУММЕСЛИМН($C$2:$C$7;$A$2:$A$7; $E2; $B$2:$B$7; F$1)
Одна формула на всю таблицу: город берётся из столбца E ($E2 — закреплён столбец), товар — из строки 1 (F$1 — закреплена строка).
Сводная не пересчитывается сама: после изменения данных её обновляют («Анализ сводной таблицы → Обновить») и проверяют, что новые строки вошли в её источник.
Диаграммы
Вид диаграммы выбирают по вопросу: сравнить группы, проследить изменение во времени или увидеть, как одно число зависит от другого. Выдели подписи и значения, выбери вид в группе «Вставка → Диаграммы» и подпиши показатель с единицами. Заголовок столбца не должен попасть в числа, а строка итога — стать ещё одной группой.
Для чисел по горизонтали нужна точечная диаграмма: график ставит подписи через равные промежутки, и цены 20, 40, 80 и 120 легли бы на одинаковом расстоянии друг от друга.
Диаграмма показывает картину, но точное число с неё не считать: ответ задачи берут из вычислений.
Итог
Excel умеет показать данные — подсветкой, сводной и диаграммой, — но ответ по-прежнему даёт формула.
- «Таблица»Ctrl
T: фильтры, полосы, формулы [@Цена]по именам столбцов. - ПравилоФормула для верхней левой ячейки; $ закрепляет столбцы. Цвет — не признак.
- СводнаяИтоги по группам, как
СУММЕСЛИ; после изменения данных — обновить. - ДиаграммаГруппы — гистограмма, время — график, зависимость — точки.
Проверьте себя
Ответь сначала сам, потом открой ответ.
Правило подсветки для B2:B50 — «значение больше среднего по столбцу». Какую формулу записать в правило?
ОтветСкрыть ответ
=B2>СРЗНАЧ($B$2:$B$50): ячейка строки сдвигается, диапазон среднего закреплён.
Правило =СУММ(A2:F2)>50 без знаков $ применили к A2:F100. Что проверяет ячейка B2?
ОтветСкрыть ответ
=СУММ(B2:G2)>50: столбцы сдвинулись, и ячейки одной строки проверяют разные суммы. Нужно =СУММ($A2:$F2)>50.
В сводной выручка Москвы — 3, хотя покупок там на тысячи рублей. Что выбрано в области «Значения»?
ОтветСкрыть ответ
«Количество» вместо «Суммы»: 3 — число записей Москвы.
Какую диаграмму взять, чтобы показать выручку по дням месяца? А чтобы сравнить выручку трёх городов?
ОтветСкрыть ответ
График — для изменения по дням, гистограмму — для сравнения городов.
В данные добавили строку, а сводная показывает старые числа. Что сделать?
ОтветСкрыть ответ
Обновить сводную и проверить, что новая строка входит в её источник.
Практика
Для проверки ответов нужен JavaScript. Условия, подсказки и решения доступны ниже.
Формула правила
Нужно подсветить строки A2:F100, где сумма чисел строки больше 50. Какую формулу записать в правило условного форматирования?
Введите формулу со знаком =, адреса латиницей.
Подсказка
Правило пишут для верхней левой ячейки A2; столбцы закрепляют, номер строки оставляют свободным.
Разбор
Для A2 сумма строки — СУММ($A2:$F2). Знак $ перед буквами держит столбцы A:F в каждой ячейке строки, номер строки сдвигается вниз.
Ответ: =СУММ($A2:$F2)>50
Сколько строк подсветится
Правило =СУММ($A2:$F2)>50 применено к A2:F4. В строке 2 числа 9, 9, 9, 9, 9, 9; в строке 3 — 10, 10, 10, 10, 10, 0; в строке 4 — 20, 20, 5, 1, 1, 4. Сколько строк подсветится?
Введите число.
Подсказка
Каждая строка проверяет свою сумму; «больше 50» не включает 50.
Разбор
Суммы: 54, 50 и 51. Больше 50 — строки 2 и 4.
Ответ: 2
Правило без $
Правило =СУММ(A2:F2)>50 без знаков $ применили к A2:F100. Как читает это правило ячейка D2?
Введите формулу со знаком =, адреса латиницей.
Подсказка
Правило копируется на каждую ячейку, как формула: из A2 в D2 — на три столбца вправо.
Разбор
Без $ оба адреса сдвигаются на три столбца: A2:F2 становится D2:I2. Ячейки одной строки проверяют разные суммы.
Ответ: =СУММ(D2:I2)>50
Сводная: сумма по городу
Покупки: Москва — чай 300, Казань — кофе 450, Москва — кофе 250, Омск — чай 120, Казань — чай 200, Москва — какао 150. В сводной поле «Город» — в строках, «Стоимость» — в значениях с операцией «Сумма». Что сводная покажет для Москвы?
Введите число.
Подсказка
Сводная складывает стоимости всех строк одного города — как СУММЕСЛИ.
Разбор
Москва: 300 + 250 + 150 = 700.
Ответ: 700
Сводная: Количество вместо Суммы
Покупки: Москва — чай 300, Казань — кофе 450, Москва — кофе 250, Омск — чай 120, Казань — чай 200, Москва — какао 150. В сводной для «Стоимости» по ошибке выбрана операция «Количество». Что сводная покажет для Москвы?
Введите число.
Подсказка
«Количество» считает записи, а не складывает их значения.
Разбор
У Москвы три покупки — сводная покажет 3 вместо 700.
Ответ: 3
Москва без какао
Покупки: Москва — чай 300, Казань — кофе 450, Москва — кофе 250, Омск — чай 120, Казань — чай 200, Москва — какао 150. Найди выручку Москвы без какао.
Введите число.
Подсказка
Два условия сразу: город — Москва, товар — не какао. Это СУММЕСЛИМН с критерием "<>Какао".
Разбор
300 + 250 = 550: =СУММЕСЛИМН(C2:C7;.
Ответ: 550
Вид диаграммы
Нужно показать, как количество проданных штук зависит от цены товара: цены 20, 40, 80 и 120 рублей. Какую диаграмму выбрать?
Введите текст без кавычек.
Подсказка
По горизонтали — числа с неравными промежутками, а не группы и не дни.
Разбор
Точечная: она ставит цены на свои места по оси. График разложил бы 20, 40, 80 и 120 через равные промежутки.
Ответ: точечная