§ 4. Инструменты анализа данных

Определения и обозначения

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

Фильтрация данных — отбор строк, которые соответствуют заданному условию, без удаления остальных строк из таблицы. Например, можно показать только записи о конкретном товаре или результаты выше выбранного балла. Фильтр нужен, когда требуется сосредоточиться на части данных, но сохранить возможность вернуться к полному набору.

Сводная таблица — средство группировки и обобщения исходных данных, которое позволяет получать итоги по выбранным категориям. Вместо просмотра каждой продажи можно увидеть общую выручку по товарам или месяцам. Это удобно для краткого отчёта и сравнения групп записей.

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

Как это устроено

Перед анализом данные организуют в таблицу: одна строка описывает одну запись, а столбец содержит один признак, например дату или цену. Такое устройство нужно программе, чтобы одинаково понимать назначение каждого значения и корректно выполнять операции над столбцами. Заголовки сообщают, какие признаки находятся в таблице, а единый формат значений помогает распознавать числа, даты и текст. Если в одном столбце даты записаны по-разному или среди чисел попались слова, анализ может дать неожиданный результат.

Сортировка должна переставлять записи целиком, потому что значения разных столбцов одной строки связаны между собой. Если отсортировать только столбец с баллами, фамилии останутся на местах и перестанут соответствовать результатам. При нескольких критериях сначала применяют главный, а затем уточняющий: например, сначала сортируют по классу, потом внутри каждого класса — по баллу. Поэтому порядок критериев определяет, какие различия будут главными в итоговом порядке.

Фильтр задаёт условие для одного или нескольких полей: текст можно отобрать по совпадению, числа — по сравнению с порогом, даты — по периоду. Несколько условий позволяют сузить выборку, например оставить продажи определённого товара за конкретный месяц. Это полезно, потому что нужные записи можно изучать отдельно, не изменяя исходный набор. Неподходящие строки лишь скрываются, поэтому снятие фильтра возвращает полный вид таблицы.

Сводная таблица создаёт краткое представление исходных записей: поля размещают в строках, столбцах и области значений. Категории в строках и столбцах задают группы, а поле значений показывает, что нужно обобщить для каждой группы. Для выручки выбирают сумму, а для подсчёта числа заказов — количество записей. Выбор важен, потому что одна и та же таблица при разных настройках отвечает на разные вопросы.

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

Инструменты анализа табличных данных

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

Разбор задач

Расположить учеников по баллам от наибольшего к наименьшему

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

1. Проверьте, что у таблицы есть заголовки, а фамилии и баллы записаны в одних и тех же строках. Выделите таблицу целиком или запустите сортировку для всего диапазона с распознаванием заголовков. Это позволит программе перемещать запись как единое целое.

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

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

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

Выбрать продажи за месяц и для одного товара

В таблице продаж нужно оставить записи, относящиеся одновременно к заданному месяцу и выбранному товару. Два условия применяют к разным полям, поэтому одного фильтра недостаточно. Проверка одновременного действия условий покажет, что в списке не осталось продаж других месяцев или товаров.

1. Убедитесь, что в таблице есть поля с датой продажи и названием товара. Включите фильтрацию для всего диапазона, чтобы заголовки стали доступными для выбора. Если даты представлены не как даты, а как текст, сначала приведите их к единому формату.

2. В поле даты выберите нужный месяц или задайте границы периода, если фильтр предлагает такой способ. В поле товара отметьте только нужное название. Программа покажет строки, удовлетворяющие обоим условиям одновременно.

3. Просмотрите результат и проверьте даты и названия в нескольких строках. Если видны продажи за другой месяц или другого товара, проверьте настройки фильтров и формат исходных значений. После снятия фильтров все записи должны снова появиться.

Ответ: примените условия к полям даты и товара одновременно; в таблице останутся только подходящие продажи, а остальные записи будут скрыты.

Найти выручку по товарам и месяцам и выделить большие значения

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

1. Создайте сводную таблицу на основе таблицы продаж и поместите поле товара в строки, а поле месяца — в столбцы. Такое расположение даст отдельную строку для каждого товара и отдельный столбец для каждого месяца. Если исходные данные содержат полную дату, используйте группировку по месяцам или подготовленное поле месяца.

2. Перенесите поле выручки в область значений и проверьте, что выбрано суммирование, а не количество записей. Сумма отвечает на вопрос об общей выручке, тогда как количество покажет число продаж. Сверьте один из итогов с исходными строками, чтобы убедиться в правильности группировки.

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

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

Типичные ошибки

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

• Принимают скрытые фильтром строки за удалённые. Это происходит, когда в таблице видна только часть записей и кажется, что остальные исчезли. Фильтрация лишь временно скрывает строки, не меняя исходные данные. Снимите фильтр и убедитесь, что полный набор снова отображается.

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

• Считают, что условное форматирование вычисляет или исправляет данные. Цветное выделение может выглядеть как отдельный результат, поэтому оформление принимают за изменение числа. На самом деле меняется только отображение ячейки. Чтобы проверить содержимое, посмотрите на значение или формулу в ячейке и используйте отдельное вычисление, если оно нужно.

Связь с другими темами

Для работы с инструментами анализа нужно уметь представлять сведения в электронной таблице, различать записи и поля, задавать простые условия и пользоваться формулами. Эти умения помогают подготовить данные и правильно истолковать полученные итоги. Сортировка, фильтры, сводные таблицы и форматирование особенно полезны при обработке больших таблиц, анализе результатов опросов и экспериментов. Они также помогают готовить отчёты и визуально представлять данные.

Что запомнить

• Перед анализом проверьте заголовки, полноту записей и единообразие данных.
• Сортировка меняет порядок записей, но не должна разрывать связь значений внутри строки.
• Фильтр показывает записи, подходящие под заданные условия.
• Сводная таблица группирует данные и вычисляет обобщённые показатели.
• Для сводных итогов важно правильно выбрать поле и способ вычисления.
• Условное форматирование помогает увидеть значимые значения, но не меняет сами данные.