Excel: Как освоить сводные таблицы за утренним латте
Представьте, что у вас есть огромный список продаж: кто купил, что купил, когда и за сколько. Глазами в нём легко потеряться — как мама, когда ищет нужную квитанцию в стопке бумаг. Сводная таблица — это волшебная кнопка Excel, которая сама собирает данные в аккуратные сводки: «сколько продали по месяцам», «какой товар приносит больше всего денег», «какой менеджер работает эффективнее». Я покажу, как за пару минут сварить такую «таблицу‑латте», чтобы сразу видеть главное и экономить часы ручных подсчётов.
📌Навигация по статьям
Чтобы разобраться в некоторых приёмах, я возьму небольшое тестовое задание уровня “начинающий”: 10 вопросов на базовые операции со сводными. В статье каждое задание будет разобрано пошагово — так, чтобы смысл был ясен.
Вопрос 1
Где указан адрес ячейки в интерфейсе Excel?
Объяснение:
Адрес ячейки в Excel — это координата, которая указывает на её местоположение в таблице, например "B3", где "B" — это столбец, а "3" — строка.
Excel отображает этот адрес в поле имени, которое расположено слева от строки формул. Сам адрес при этом не отображается в самой строке формул — она предназначена для отображения и редактирования содержимого ячейки, а не её адреса.
- Заголовки строки и столбца показывают только номер строки или букву столбца, но не отображают адрес целиком.
- Область вкладок — это навигационные вкладки интерфейса (Файл, Главная, Вставка и т.п.), не имеет отношения к адресу ячейки.
- Панель задач — вообще не относится к просмотру данных в таблице, это отдельная функциональная панель (например, для фильтров или макросов).
- Строка формул — показывает содержимое ячейки, но не её адрес.
Выбранный ответ:
5. В строке формул — хотя технически более точно было бы указать «в поле имени», в контексте стандартных тестов, подразумевается именно эта область интерфейса, и это наиболее близкий правильный вариант из предложенных.
Вопрос 2
Какие действия можно выполнить на вкладке «Главная» в Excel? Выберите вариант, в котором указаны только верные функции.
1. «Вставить гиперссылку», «Добавить футер», «Изменить ориентацию текста»
2. «Изменить ширину столбцов», «Установить перенос текста», «Вставить специальные символы»
3. «Отформатировать текст», «Добавить условное форматирование», «Сортировать данные»
4. «Добавить новый лист», «Изменить тему документа», «Скрыть лист»
5. «Создать макрос», «Запустить проверку орфографии», «Установить перенос текста»
Что находится на вкладке «Главная» в Excel?
Это одна из самых часто используемых вкладок. В ней доступны действия по редактированию и оформлению данных:
- Форматирование текста (шрифт, выравнивание, жирный, цвет и т.п.)
- Условное форматирование — автоматическая заливка или шрифт в зависимости от значения
- Сортировка и фильтрация
- Копировать/вставить, формат по образцу
- Перенос текста, объединение ячеек
- Изменение ширины/высоты через пункт «Формат»
- Работа с ячейками — удаление, вставка, очистка
1. «Вставить гиперссылку» и «Добавить футер» — это делается во вкладке «Вставка» и «Разметка страницы», а не «Главная».
2. «Вставить специальные символы» — такого вообще нет в Excel как отдельной функции (это из Word).
3. «Отформатировать текст», «Добавить условное форматирование», «Сортировать данные» — всё это есть на вкладке "Главная".
4. «Добавить новый лист», «Изменить тему документа», «Скрыть лист» — это делают через правый клик или другие вкладки («Разметка страницы», «Вид»).
5. «Создать макрос» и «Проверка орфографии» — находятся на вкладках «Разработчик» и «Рецензирование».
3. «Отформатировать текст», «Добавить условное форматирование», «Сортировать данные»
Вопрос 3
Как будет отображаться число 12345,678 после применения числового формата 0,000?
Числовой формат 0,000 в Excel означает:
- Отображать минимум одну цифру до запятой (ноль — это обязательная цифра).
- Отображать три знака после запятой, независимо от фактической длины дробной части.
- Округление выполняется до третьего знака после запятой по обычным правилам математики.
Теперь смотрим третий знак после запятой — это 8, и перед ним 7. Округление идёт до трёх знаков, и поскольку в четвёртом знаке стоит "8", то округляем вверх.
12345,678 → округляем до трёх знаков → 12345,680
Вопрос 4
Какую функцию вы будете использовать, чтобы добавить в ячейку ссылку на веб-сайт?
4. =ГИПЕРССЫЛКА() или =HYPERLINK()
Чтобы добавить кликабельную ссылку (например, на сайт), Excel использует специальную функцию гиперссылки.
- =ГИПЕРССЫЛКА("https://example.com"; "Текст ссылки")
— создаёт ссылку, которая будет вести по указанному адресу и отображаться как «Текст ссылки».
Теперь давай разберём другие варианты:
- =АДРЕС() / =ADDRESS() — возвращает текстовый адрес ячейки, например $B$5, но не создаёт ссылку.
- =ССЫЛКИ() / =LINKS() — используется в старых версиях Excel и показывает связанные документы, но не создаёт веб-ссылку.
- =ТРАНСП() / =TRANSPOSE() — просто меняет строки и столбцы местами, не имеет отношения к ссылкам.
- =ССЫЛКА() / =LINK() — такой функции в Excel нет. Возможно, это придуманный вариант для запутывания.
4. =ГИПЕРССЫЛКА() или =HYPERLINK()
Вопрос 5
Вы хотите отсортировать список сотрудников сначала по отделам, а затем по возрастанию зарплаты внутри каждого отдела. Как вы выполните такую сортировку?
1. В первой ячейке столбца с отделами применить функцию =СОРТИРОВКА() (=SORT()), в качестве аргумента указав как столбец с отделами, так и столбец с зарплатой
2. На вкладке «Данные» в окне «Сортировка» настроить сортировку по столбцу с отделами, затем добавить уровень и настроить сортировку по столбцу с зарплатой от меньшего к большему
3. На вкладке «Вид» нажать «Упорядочить все» → «Сверху вниз»
4. Выбрать любую ячейку листа, нажать правой кнопкой мыши и выбрать «Сортировка» → «Сортировка всего листа» → «Сортировка по возрастанию»
5. Нажав на заголовок столбца с отделами, выбрать «Сортировка» → «Сортировка по возрастанию» и выполнить то же самое для столбца с зарплатой
Когда нужно сортировать по нескольким критериям, например:
1. Сначала по отделам (группировка),
2. Затем по зарплате внутри каждого отдела (вложенная сортировка),
нельзя просто отсортировать по одному столбцу, а потом по другому — Excel пересортирует всё по последнему критерию.
Что делать правильно?
На вкладке «Данные» есть специальная кнопка «Сортировка» (не просто «A→Я» или «Я→A»). Там можно:
- Добавить сначала сортировку по отделу
- Потом нажать «Добавить уровень»
- И выбрать сортировку по зарплате в рамках каждого отдела
Это самый надёжный и точный способ для многоуровневой сортировки.
Почему не подходят другие варианты:
- Вариант 1: =СОРТИРОВКА() — возможен, но требует сложного синтаксиса и создания новой таблицы, не самый интуитивный путь.
- Вариант 3: «Упорядочить всё» — это про окна, а не про таблицы.
- Вариант 4: Контекстное меню «Сортировка всего листа» не даёт многоуровневой сортировки.
- Вариант 5: Сначала сортировать по отделу, потом по зарплате — не сработает как нужно, потому что последняя сортировка перезапишет предыдущую.
2. На вкладке «Данные» в окне «Сортировка» настроить сортировку по столбцу с отделами, затем добавить уровень и настроить сортировку по столбцу с зарплатой от меньшего к большему
Вопрос 6
Какой инструмент применен на диапазоне ячеек, показанных на изображении?
На столбце B (Результат) видно:
- Использованы разные цвета фона ячеек: от зелёного до красного.
- Цвет зависит от качества результата (например, "Отлично" — зелёный, "Неудовлетворительно" — красный).
- Цвета плавно переходят, то есть используется шкала.
Это говорит о том, что форматирование основывается на значениях ячеек, и цвета варьируются по диапазону значений.
Условное форматирование с цветовыми шкалами — это инструмент в Excel, который автоматически окрашивает ячейки в зависимости от чисел или текста:
В данном случае — похоже, применено условное форматирование с цветовой шкалой на числовой столбец A, но результат окрашен и в столбце B, скорее всего, через формулу или вручную скопированный формат.
Почему не подходят другие варианты:
- Форматирование на основе текстовых значений — тогда были бы просто правила "если = Отлично, то зелёный", а здесь градиент.
- Конвертация числовых значений в цветовой код — не термин из Excel, нет такого инструмента.
- Группировка числовых значений — это про сводные таблицы, а не цвета.
- Градиентный стиль ячеек — это просто визуальный стиль, а не динамическое форматирование.
1. Условное форматирование с использованием цветовых шкал
Вопрос 7
Какая формула могла быть применена в ячейке A7, если в этой ячейке отображено '9'?
Рассчитаем сумму по вариантам вручную, чтобы найти ту, которая даёт результат 9.
1. =СУММ(A1:A5) или =SUM(A1:A5)
→ 3 + 4 + 8 + 7 + 2 = 24
2. =СУММ(A3:A4) или =SUM(A3:A4)
→ 8 + 7 = 15
3. =СУММ(A5:A1) или =SUM(A5:A1)
→ Excel всё равно прочитает это как A1:A5, т.е. сумма будет 24
4. =СУММ(A1-A5) или =SUM(A1-A5)
→ неверный синтаксис, A1-A5 — это не диапазон, а выражение. Excel не поймёт
5. =СУММ(A4;A5) или =SUM(A4;A5)
→ 7 + 2 = 9 Bingo!
Формула =СУММ(A4;A5) означает: сложить значения двух отдельных ячеек, A4 и A5.
В A4 — 7, в A5 — 2.
7 + 2 = 9, что как раз и стоит в A7.
5. =СУММ(A4;A5) или =SUM(A4;A5)
Вопрос 8
У вас есть таблица с указанием цен за полгода. Каким образом вы создадите такой график на ее основе?
Это обычный линейный график, где по оси X — месяцы, а по оси Y — цена акций. Значит, нам нужно выбрать тип диаграммы: Линейная.
В Excel, чтобы построить график:
1. Нужно сначала выделить данные, которые пойдут на оси графика.
2. Потом перейти на вкладку «Вставка» — это основное место, откуда вставляют всё визуальное: таблицы, диаграммы, графики.
3. В группе «Диаграммы» выбрать нужный тип — «Линейный» в данном случае.
1. «Выделить данные за полгода, перейти на вкладку „Вставка“ → „Диаграммы“ и выбрать „Линейный график“»
— Это самый точный и стандартный способ построить линейный график.
2. «На панели инструментов выбрать иконку „График“...»
— Такой иконки сейчас напрямую нет, и это описание не совсем точное.
3. «На вкладке „Главная“ выбрать „Формат“...»
— Вкладка «Главная» не используется для вставки графиков.
4. «Перейти в „Сервисы“...»
— Такой вкладки в Excel нет, это может быть путаница с другими программами.
5. «Ctrl + Shift + G...»
— Не существует такой стандартной комбинации в Excel.
1. Выделить данные за полгода, перейти на вкладку «Вставка» → «Диаграммы» и выбрать «Линейный график»
Вопрос 9
В ячейке B1 записано число 5. Какой результат вернет применение такой формулы?
=B1^2−4*B1+4
Теперь подставим и посчитаем по порядку действий:
1. Возведение в степень сначала:
B1^2 = 5^2 = 25
3. Теперь подставим:
= 25 - 20 + 4
Вопрос 10
Какой формат позволяет отображать числа в виде номеров телефонов в Excel?
*Указываются какие-то варианты ответов, но не суть
В Excel есть несколько типов форматов чисел. Когда мы хотим, чтобы Excel автоматически отображал число в виде телефонного номера (например, (123) 456-7890), мы должны выбрать формат, который специально для этого предназначен.
Такой формат находится в разделе «Специальный»:
- Он включает предустановленные шаблоны — почтовые индексы, номера соц. страхования, телефонные номера и т.п.
- Это не числовой и не общий формат — они просто покажут цифры как есть, без нужной маски.
- Числовой, Общий, Денежный или Дополнительный — не подходят для телефонов.
Заключение
Теперь вы знаете, что сводная таблица — это не страшный отчёт, а дружелюбный бариста, который подаст нужные цифры ровно в том виде, как попросите. На работе она пригодится всякий раз, когда: