Как выполнить сортировку в сводной таблице excel

Слайсеры Vs. Фильтры отчетов

В чем разница между срезами и фильтрами отчетов?

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

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

Вот некоторые ключевые различия между срезами и фильтрами отчетов:

• Срезы не занимают фиксированную ячейку на листе. Вы можете перемещать их, как любой другой объект или форму. Фильтры отчета привязаны к ячейке.
• Фильтры отчетов связаны с определенной сводной таблицей. С другой стороны, срезы можно связать с несколькими сводными таблицами (как мы увидим позже в этом руководстве).
• Поскольку фильтр отчета занимает фиксированную ячейку, его проще автоматизировать с помощью VBA. С другой стороны, слайсер — это объект, и ему потребуется более сложный код.

Как создать сводную таблицу в Excel

Microsoft Excel имеет отличную встроенную опцию сводной таблицы

Обратите внимание, что Excel — далеко не единственная программа для работы с электронными таблицами, которая использует сводные таблицы. Альтернативы Microsoft Office, такие как Libre Office и G-Suite Google, поддерживают все сводные таблицы в своих программах для работы с электронными таблицами

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

Примеры файлов данных сводных таблиц через Media Fire

Чтобы создать сводную таблицу, перейдите на вкладку « Вставка » и выберите « Сводная таблица». Выберите таблицу или диапазон данных, который вы хотите включить в сводную таблицу. В случае примера весь диапазон данных составляет выбор.

Как видите, есть варианты подключения внешнего источника данных (например, MS Access) или размещения сводной таблицы в существующей рабочей таблице. Для последнего варианта обязательно убедитесь, что ваша новая сводная таблица не затеняет и не разрушает ваши существующие данные (хотя вы всегда можете нажать CTRL + Z, чтобы отменить, если случится что-то ужасное!).

Сортировка данных сводной таблицы

Как только вы нажмете ОК, сводная таблица откроется на новом листе. Рабочий лист начинается пустым. Справа вы увидите панель полей сводной таблицы . Эта панель содержит диапазоны данных образца набора данных, такие как имена, адреса, продажи и т. Д.

Отсюда у вас есть два варианта добавления данных в сводную таблицу:

1. Перетаскивание: поля сводной таблицы, перечисленные справа, можно перетащить в четыре области ниже (Фильтры, Столбцы, Строки и Значения). Вы можете выбрать конкретные элементы данных, которые вы хотите пересечь с любым количеством различий.
2. Добавить в отчет: при нажатии на отдельные поля в наборе данных они будут добавлены непосредственно в таблицу ожидающих отчетов. Используйте этот метод для быстрого создания, анализа, сопоставления и редактирования доступных данных.

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

Выбор диапазонов данных сводной таблицы

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

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

Теперь вы можете взглянуть на данные в сводной таблице и проанализировать данные на наличие трендов. Если вы хотите просмотреть обзор каждого раздела, щелкните правой кнопкой мыши и выберите « Развернуть / Свернуть»> «Свернуть все поле» .

Фильтрация данных сводной таблицы

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

Наведите указатель мыши на нужное поле данных и обратите внимание на небольшую черную стрелку справа от текста.
Выбор черной стрелки показывает раскрывающееся меню «Фильтр», которое чрезвычайно полезно для изоляции данных в одном поле или для сравнения аналогичных диапазонов данных в нескольких источниках данных.
Выберите меню Фильтр округа .
По умолчанию выбраны все диапазоны данных. Отмените выбор всех (нажав « Выбрать все» ), после чего выберите новый регион

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

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

Срезы

Программа Microsoft Excel позволяет прикрепить к таблицам интерактивные элементы для сортировки и фильтрации – срезы. После выхода версии 2013-о года появилась возможность подключать срезы к обычным таблицам, а не только к сводным отчетам. Разберемся, как создать и настроить эти опции.

Создание срезов

1. Кликаем по одной из ячеек таблицы – на панели инструментов появится вкладка «Конструктор», которую нужно открыть.

2. На вкладке следует найти кнопку «Вставить срез» – кликаем по ней и переходим к новому открывшемуся окну.
3. Отмечаем галочками все поля, которые следует добавить в срез, и кликаем по кнопке «ОК».

Срезы выглядят, как диалоговые окна со списками кнопок. Названия пунктов зависят от того, какие элементы таблицы были выбраны при создании среза. Чтобы отфильтровать данные, нужно кликнуть по кнопке в одном из списков. Фильтрация по нескольким диапазонам данных возможна, если нажать кнопки в нескольких срезах.

Форматирование срезов

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

1. Открываем вкладку «Параметры» и находим раздел «Стили срезов». В нем находятся темы для срезов разных цветов. Выбираем любую из них – цвет не повлияет на эффективность работы элемента. Сразу после клика по стилю срез приобретет указанные цвета.
2. Также возможно изменить положение срезов на экране. Воспользуемся кнопками «Переместить вперед» и «Переместить назад» в разделе «Упорядочить». Необходимо выбрать один из срезов и нажать кнопку на панели инструментов. Теперь при перемещении по экрану срез будет оказываться поверх всех срезов или попадет под них.
3. Удаление срезов – несложная операция. Выберите лишнее окно и нажмите клавишу «Delete» на клавиатуре. Срез исчезнет с экрана и перестанет влиять на фильтрацию данных в таблице.

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

Как настроить сортировку в Excel

Исходная таблица для следующего примера:

Необходимо распределить данные по столбцу F – «Категория сотрудника» в следующей последовательности:

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

Сортировка по списку в Excel:

1. Перейдите на любую ячейку таблицы и снова воспользуйтесь инструментом: «ДАННЫЕ»-«Сортировка».
2. В первом выпадающем списке укажите «Категории сотрудника». Во втором все тоже «Значение». А в третьем выпадающем списке в секции «Порядок» выберите последнюю опцию «Настраиваемый список».
3. В появившемся окне «Списки» в левой группе укажите на первый «НОВЫЙ СПИСОК», а в правом текстовом поле введите все 4 категории разбив их на 4 строки. Для этого после ввода каждой нового названия категории сотрудника нажимайте клавишу Enter на клавиатуре:
4. Нажмите на кнопку «Добавить», которая расположена справой стороны. В результате чего в левой группе будет создан новый список для порядка пользовательской сортировки.
5. Нажмите на кнопку ОК в диалоговом окне «Списки» и в третьем выпадающем списке в секции «Порядок» автоматически добавились 2 новых опции. С прямым и обратным порядком для пользовательской настраиваемой сортировки. И снова нажмите на кнопку ОК в главном диалоговом окне инструмента.

В результате таблица отсортирована с учетом всех привередливых пожеланий пользователя.

Полезный совет! Чтобы каждый раз не создавать новые списки для порядка сортировки воспользуйтесь редактором списков в настройках Excel. Для этого:

1. Откройте редактор списков в настройках Excel: «ФАЙЛ»-«Параметры»-«Дополнительно»-«Общие»-«Изменить списки».
2. Введите свои списке и нажмите на кнопку «Добавить» как описано выше в примере. Если же у вас большой список и нет желания его заполнять вручную, тогда воспользуйтесь встроенным импортом. Для этого в данном диалоговом окне присутствует дополнительное поле «Импорт списка из ячеек:» в котором можно ввести ссылку на диапазон ячеек и нажать на кнопку «Импорт». И нажмите ОК.

В результате при работе с Excel у вас будет всегда под рукой свой пользовательский список для порядка сортировки данных.

Изменение набора полей сводной таблицы

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

1. Щелкните на произвольно выбранной ячейке сводной таблицы.

Excel добавит на ленту набор контекстных вкладок Работа со сводными таблицами с собственными контекстными вкладками Анализ и Конструктор.

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

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

После открытия панели списка полей можно выполнить следующие изменения.

Чтобы удалить поле, перетащите его имя из области, в которой оно находится в текущий момент (ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ или ЗНАЧЕНИЯ), в любое другое место. Как только указатель мыши примет вид крестика, отпустите кнопку мыши или просто снимите флажок около этого поля в списке полей.

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

Группировка по датам в сводной таблице

Построим по исходной таблице сводную (подробно про это рассказывал в отдельной статье), в строки таблицы добавим даты, в значения поместим сумму:

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

Встаем в любой ячейку с датой, нажимаем правой кнопкой мыши и выбираем в контекстном меню команду Группировать:

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

Мы можем выбрать любую метрику по времени от секунд до годов (в том числе и сразу показателей несколько), выберем подходящие (к примеру, дни и месяца слева на картинке, кварты и месяца — справа) и группировка дат в сводной таблице будет сделана от более крупного к более мелкому:

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

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

Форматирования сводной таблицы

В первую очередь поговорим о заголовках. Именно их хочется сразу изменить, но тут есть один нюанс, который стоит учитывать.

Заголовок изменяется самым обычным образом — щелкаем по ячейке с ним и затем меняем текст.

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

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

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

Я же просто переименую поля в «Сумма заказов» и «Количество заказов». Первый заголовок также можно изменить на «Город

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

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

Затем в появившемся окне указываем нужный формат и задаем его параметры.

Форматирование будет применено сразу ко всем столбцу.

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

Как сортировать числа от меньшего к большему в Google Таблицах?

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

Как получить последние 7 цифр в Excel? Если вы хотите извлечь последние n символов, например последние 3 символа, введите эту формулу = ВПРАВО (E1; 3).

Как извлечь последние 4 цифры в Excel?

Показывать только последние 4 цифры ssn с формулой

1. Выберите пустую ячейку, введите формула =ПРАВО(A2,4) в строку формул, а затем нажмите клавишу Enter. Смотрите скриншот: Примечание. В формуле A2 ячейка содержит номер социального страхования.

Как сортировать по первой цифре в Excel?

Сводные таблицы в Excel: Анализ данных с помощью сводных таблиц

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

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

Группировка строк в таблице (Power Query)

​ экране все данные​​Отображение и скрытие структурированных​ что попроще.​ загрузке, редактирование или​В​Средства OLAP​ всех остальных несгруппированных​Excel автоматически добавляет вычисляемые​ функция — группировка по​Важно:​.​.​511​124​Данные​нажмите кнопку​ для группировки строк​ во избежание ошибок​ данных​antal10​ создание запроса с​Group By​

​) невозможно. Перед ее​​ элементов в поле.​​ столбцы в сводную​​ времени. Приложение автоматически​​ Если во время удаления​​Нажмите кнопку​Выполните одно или несколько​410​750​в группе​Разгруппировать​ со 2-й по​

​ группировки строк.​​Настройка многоуровневой структуры со​: Предусмотрели. Кнопку можно​ помощью​всплывающего окна имя​ вызовом необходимо сначала​

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

​ 5-ю, у которых​Структурируйте внешнюю группу.​

• ​ стилями​ найти в настройках​

• ​Power Query​ столбца, который вы​

Группировка по одному или нескольким столбцам

​ разгруппировать сгруппированные элементы.​ уровням, можно группировать​ ​ группировки значений даты​​ значениями соответствующих полей​​ данные скрыты, то​​.​​ действий.​​124​

​1 074​щелкните стрелку под​ ​Важно:​​ имеется итоговая строка​Структурирование внешней группы​Копирование структурированных данных​ панели быстрого доступа,​. Видео показано, что​​ использовали на шаге​​Создавать срезы для иерархий​

Группировка по одному или нескольким столбцам

​ только элементы, имеющие​ и времени

Excel​ и группирует их,​ строки или столбцы​​Для форматирования структурированных данных​​Отображение или скрытие подробных​​750​​2 276​

​ пунктом​​ Если во время разгруппировки​​ 6, выберите строки​Выделите все подчиненные итоговые​Скрытие или удаление многоуровневой​ туда ее можно​ окно​ 1 (по умолчанию)​​ OLAP со сгруппированными​​ одинаковые следующие уровни.​ также автоматически свернет​ когда пользователь добавляет​ с подробными данными​ также можно применять​ данных для группы​​200​​Важно:​Группировать​​ структуры подробные данные​​ со 2-й по​ строки и соответствующие​ структуры​ и вынести.​Редактора запросов​​ выбран в раскрывающемся​​ полями нельзя.​

​ Например, если в​​ данные таким образом,​​ в сводные таблицы​

​ могут также не​​ автоформаты.​​    ​​1 074​​ При выборе не включайте​​, а затем выберите​​ скрыты, строки с​

​ 5-ю. Для группировки​ им строки с​​Создание итогового отчета с​​paradoxs​​отображаются после редактирования​​ списке​​ТолькоExcel 2016 : чтобы отключить​​ поле есть два​ чтобы они отображались​ строки полей времени.​​ отображаться

Для просмотра​К началу страницы​​Чтобы показать подробные данные​​2 276​ итоговую строку J​​Создать структуру​​ подробными данными могут​ строк с 7-й​ данными.​ диаграммой​: Честно сказать не​​ запроса из книги​​Группировать по​

​ группировку по времени​​ уровня «Страна» и​​ в периодах даты​

Агрегирование столбца с помощью агрегатной функции

​ Созданную группу можно​​ данных выделите номера​​Если символы структуры документа​​ группы, нажмите соответствующую​​Важно:​​ (общий итог).​​.​​ также не отображаться.​​ по 9-ю, у​​В приведенном ниже примере​​Убедитесь в том, что​​ нашёл такой кнопки….​​ Excel. Для просмотра​

Выполнение операции со строками

​. При необходимости можно​​ в сводных таблицах​​ «Город», нельзя группировать​​ и времени максимального​​ перетащить в сводную​ ​ или буквы видимых​ ​ (​​ этой группе кнопку​

​  При выделении столбцов​​На вкладке​​Структурирование данных вручную​ Для отображения данных​ которых имеется итоговая​ строка 6 содержит​ в первой строке​

​Serge​​Редактора запросов​​ выбрать другое имя​ (в том числе​ города из разных​ уровня.​ таблицу для анализа.​

​ соседних строк или​​,​​.​​ не включайте в​Данные​Важно:​ перетащите указатель через​​ строка 10, выделите​​ промежуточные итоговые данные​ каждого столбца данных,​​: Эта кнопка нужна?​​без загрузки или​ столбца из раскрывающегося​ в моделях данных)​​ стран.​​Например, если в списке​В сводной таблице щелкните​ столбцов соответственно. На​​и​​Чтобы скрыть подробные данные​​ диапазон данных итоговый​​в группе​​ Группируя уровни структуры вручную,​ номера видимых строк​​ строки с 7-й​

support.office.com>

Что такое сводная таблица?

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

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

Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:

И каждый день сюда добавляются все новые сведения. Одним из возможных способов суммирования этого длинного списка чисел по одному или нескольким условиям является использование формул, как было продемонстрировано в руководствах по функциям СУММЕСЛИ и СУММЕСЛИМН. 

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

Вот посмотрите сами.

Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж. И далее мы рассмотрим примеры построения сводных таблиц в Excel 2016, 2013, 2010 и 2007.

Как сделать сводную таблицу из нескольких файлов

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

Дальнейшая работа по созданию сводной таблицы из нескольких файлов будет зависеть от типа данных. Если информация однотипная (табличек несколько, но заголовки одинаковые), то Мастер сводных таблиц – в помощь.

Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

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

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

1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.

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

Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

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

Покажем, к примеру, количество проданного товара.

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

Автофильтр

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

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

Рассмотрим опцию «Настраиваемый фильтр». С ее помощью пользователи могут самостоятельно установить нужные настройки фильтрации.

1. Снова открываем текстовые или числовые фильтры. В конце списка находится нужный пункт, по которому следует кликнуть.
2. Заполняем поля – можно выбрать любой тип фильтра, походящий по формату ячеек, из списка и значения, существующие в диапазоне ячеек. После заполнения нажимаем кнопку «ОК». Если заполнение было непротиворечивым, настройки будут применены автоматически.

Стоит обратить внимание на пункты И/ИЛИ в окне настройки автофильтра. От них зависит то, как будут применены настройки – вместе или частично

Сортировка данных подписей строк или столбцов в pivotTable

В этой статье вы можете щелкнуть любое поле в столбце с элементами, которые вы хотите отсортировать.

На вкладке Данные нажмите кнопку Сортировкаи выберите нужный порядок сортировки. Чтобы отсортировать дополнительные параметры, нажмите кнопку Параметры.

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

Примечание: Вы также можете быстро отсортировать данные по возрастанию или убытию, щелкнув от А до Я или От Я до А. При этом текстовые элементы сортируется от А до Я или от Я до А, числа — от наименьших к наибольшим или от наибольших к наименьшим, а даты и время — от старых к новым или от новых к старым.

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

• каждый столбец должен иметь заголовок;
• в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата >;>
• в таблице должны отсутствовать полностью незаполненные строки и столбцы;
• в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
• избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

Вместо того, чтобы плодить повторяющиеся столбцы ( регион 1, регион 2, … ), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).

Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц .

Несколько облегчит процесс построения Сводной таблицы , тот факт, если исходная таблица будет преобразована в формат EXCEL 2007 ( Вставка/ Таблицы/ Таблица ). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица . Все поля окна будут автоматически заполнены, нажмите ОК.

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

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

В таблице имеются столбцы:

• Товар – наименование партии товара, например, « Апельсины >»;
• Группа – группа товара, например, « Апельсины » входят в группу « Фрукты >»;
• Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
• Дата поставки – Дата поставки Товара Поставщиком;
• Регион продажи – Регион, в котором была реализована партия Товара;
• Продажи – Стоимость, по которой удалось реализовать партию Товара;
• Сбыт – срок фактической реализации Товара в Регионе (в днях);
• Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».

Вариант 1: Быстрая сортировка в Excel

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

1. Выделите зажатой левой кнопкой мыши столбец с данными и вызовите меню «Редактирование».

2. Откройте список «Сортировка и фильтр» и выберите подходящий для вас вариант упорядочения. С числами это по возрастанию или убыванию, а для текста – по алфавиту и в обратную сторону.

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

4. Изменения вступают в силу сразу же. Нажмите Ctrl + Z для отмены действия, если что-то выполнено неправильно.

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Изменение функции итогов

При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы. Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .

Изменение порядка сортировки

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

Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).

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

После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.