Как сделать сортировку в сводной таблице в excel
Обновлено: 02.07.2024
Зачем нужны сводные таблицы?
В Excel и Google Spreadsheets есть мощный инструмент анализа данных – сводные таблицы. Они помогают из большого количества сырых данных сделать наглядный отчет, который поможет быстро найти выводы для журналистских историй. Сводные таблицы агрегируют данные и выполняют за вас их анализ.
Например, у вас есть огромный список каждого преступления с его датой, местом происшествия и другими характеристиками. С помощью сводных таблиц можно быстро получить ответ на вопрос для вашей истории: например, где был зафиксирован самый высокий уровень преступности.
Кроме этого, когда набор данных слишком большой, и не все значения из него нужны для анализа, сводные таблицы позволяют производить вычисления с отдельными данными. При этом исходная таблица не изменится и полные данные не исчезнут.
- Для примера мы будем использовать данные Минпросвещения России о количестве усыновленных за рубеж российских детей в 2019 году. Эту статистику мы использовали в своем исследовании о том, как введенные российскими властями ограничения для иностранцев повлияли на российских сирот. Этот набор данных для тренировки мы собрали из отчетов каждого региона. В них указан год, регион, из которого дети были усыновлены за рубеж, страна, в которую они отправились, количество усыновленных детей, количество усыновленных детей с инвалидностью и количество усыновленных по разным возрастным группам.
- Это руководство подходит для работы в Google Spreadsheets.
Как создать сводную таблицу?
Появится новая таблица со списком стран и количеством усыновленных в них детей. Внизу таблицы автоматически подсчитывается сумма. Так мы узнаем, что всего за 2019 год иностранцами было усыновлено 240 детей.
Сортировка в сводной таблице
Фильтры в сводной таблице
Процентное отображение значений
Добавление нескольких значений
Перемена строк
Суммирование значений разными способами
Использование столбцов
Сохранение результатов
Для удобства работы с большим массивом данных в таблицах, их постоянно нужно упорядочивать по определенному критерию. Кроме того, для выполнения конкретных целей, иногда весь массив данных не нужен, а только отдельные строки. Поэтому, чтобы не путаться в огромном количестве информации, рациональным решением будет упорядочить данные, и отфильтровать от других результатов. Давайте выясним, как производится сортировка и фильтрация данных в программе Microsoft Excel.
Простая сортировка данных
Сортировка является одним из самых удобных инструментов при работе в программе Microsoft Excel. С помощью неё, можно расположить строчки таблицы в алфавитном порядке, согласно данным, которые находятся в ячейках столбцов.
Как видим, все данные в таблице разместились, согласно алфавитному списку фамилий.
Список перестраивается в обратном порядке.
Настраиваемая сортировка
Но, как видим, при указанных видах сортировки по одному значению, данные, содержащие имена одного и того же человека, выстраиваются внутри диапазона в произвольном порядке.
- Значения;
- Цвет ячейки;
- Цвет шрифта;
- Значок ячейки.
Как видим, теперь в нашей таблице все данные отсортированы, в первую очередь, по именам сотрудника, а затем, по датам выплат.
Как видим, после этого, столбцы поменялись местами, согласно введенным параметрам.
Конечно, для нашей таблицы, взятой для примера, применение сортировки с изменением места расположения столбцов не несет особенной пользы, но для некоторых других таблиц подобный вид сортировки может быть очень уместным.
Фильтр
Кроме того, в программе Microsoft Excel существует функция фильтра данных. Она позволяет оставить видимыми только те данные, которые вы посчитаете нужными, а остальные скрыть. При необходимости, скрытые данные всегда можно будет вернуть в видимый режим.
Как видим, в ячейках с наименованием всех столбцов появился значок в виде квадрата, в который вписан перевернутый вниз треугольник.
Кликаем по этому значку в том столбце, по данным которого мы собираемся проводить фильтрацию. В нашем случае, мы решили провести фильтрацию по имени. Например, нам нужно оставить данные только работника Николаева. Поэтому, снимаем галочки с имен всех остальных работников.
Как видим, в таблице остались только строки с именем работника Николаева.
Как видим, остались только нужные нам данные.
Умная таблица
Далее, выбираем один из понравившихся стилей, в открывшемся перечне. На функционал таблицы выбор влиять не будет.
Как видим, инструменты сортировки и фильтрации, при правильном их использовании, могут значительно облегчить пользователям работу с таблицами. Особенно актуальным вопрос их использования становится в том случае, если в таблице записан очень большой массив данных.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Элементы поля сводной таблицы автоматически сортируются в возрастающем порядке, согласно их именам. Сортировка элементов по именам позволяет восстановить первоначальный порядок, если элементы были перемещены или отсортированы иначе.
Сортировка элементов сводной таблицы по названиям
Для того, что бы произвести сортировка элементов сводной таблицы по названиям выделите сортируемое поле. После чего необходимо выбрать команду "Сортировка" на панели инструментов.
В появившемся окне установите переключатель "Диапазон" в положение "по возрастанию" или "по убыванию".
Сортировка элементов сводной таблицы по значениям ячеек области данных
Для сортировка элементов сводной таблицы по значениям ячеек области данных выделите поле, содержащее сортируемые элементы. Если указано поле столбца, элементы будут сортироваться слева направо. Если указано поле строки, элементы будут сортироваться сверху вниз.
После выделения нажмите кнопку "Поле сводной таблицы" на панели инструментов "Сводные таблицы". После чего нажмите кнопку "Далее".
В появившемся окне установите переключатель "Параметры сортировки" в положение "по возрастанию" или "по убыванию". Выделите поле данных, значения которого необходимо использовать для сортировки элементов, в раскрывающемся списке с помощью поля.
Сортировки, пожалуй, заключительная функция позволяющая работать с данными в сводных таблицах. Переходим к заключительному разделу, в котором изучается обработка результатов сводных таблиц.
Потребовалось мне в сводной таблице реализовать общую фильтрацию данных, отображаемых в сводной таблице, по дате, причем не по дням, а по месяцам и годам. Вроде бы, чего тут такого? Однако по умолчанию, если попробовать сделать просто вот так:
то есть просто доступное поле дата перетащить в раздел "Поля страниц", то в результате мы сможем фильтровать только так, как это указано в изначальных данных, на которых основана сводная таблица (у меня это были конкретные даты - дни):
Как видно на скриншоте выше, можно указать фильтровать информацию в сводной таблице по каждой конкретной дате, а выбрать период вроде месяца или года, нельзя. Просто нет таких пунктов. А хотелось иметь нечто вроде того, как показывает автофильтр, но прямо в сводной таблице:
Одним из вариантов является создание вспомогательных столбцов в исходных данных, в которых бы формулами типа МЕСЯЦ(А1) и ГОД(А1) высчитывались бы номер месяца и год. Затем добавить эти столбцы в сводную таблицу и делать сортировку по ним. В принципе - это то, что нужно, НО оказывается есть способ (отнюдь не очевидный) и без захламления дополнительными данными исходной таблицы. Это настройка фильтрации по дате по периодам в самой сводной таблице.
Установите курсор в любую ячейку сводной таблицы с датой и выбирете пункт меню Данные > Группа и структура > Группировать (или просто нажмите клавишу F12 на клавиатуре). Откроется вот такой диалог "Группировка":
в котором, внезапно, можно настроить интервалы времени, по которым возможна будет дополнительная фильтрация. Выбрать можно несколько значений одновременно. Я выбрал "Месяцы" и "Годы". В итоге мы получим в свое распоряжение дополнительные поля, которые нужно перенести в настройках сводной таблицы обратно в раздел "Поля страниц", нажать "ОК"
Как раз то, что и было нужно, теперь можно фильтровать данные по периодам, задавая отдельно месяц и год.
Отмечу, что такой не очень очевидный путь для настройки общего фильтра в LibreOffice Calc полностью копирует решение из MS Excel. Почему нельзя было сделать такую настройку доступной из контекстного меню поля прямо в настройках сводной таблицы, не прибегая к шаманству с перемещением полей из раздела в раздел - это великая тайна разработчиков офисов.
ps: в Excel название поля "Дата" можно заменить на свое, в Либре я что-то такого не нахожу, плохо ищу?
Читайте также: