Сортировка по нескольким столбцам в Excel. Как пользоваться функцией сортировки данных в Microsoft Excel Сортировка данных в excel функция

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

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

Чисел

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

Теперь у нас данные в С размещены в порядке убывания.

У меня столбец С расположен между двумя другими, которые заполнены данными. В этом случае, Excel считает, что выделенный столбец – это часть таблицы (и считает правильно). В результате появилось следующее сообщение. Поскольку мне нужно сделать сортировку конкретно для Класса, выделяю маркером пункт «… в пределах указанного выделения» и нажимаю «Сортировка» .

По алфавиту

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

Список имен в примере отсортирован по алфавиту.

По дате

Чтобы отсортировать даты в Эксель, сначала обратите внимание, какой формат установлен для тех ячеек, в которых они записаны. Выделите их и на вкладке «Главная» посмотрите на группу «Число» . Лучше всего подойдет или формат «Дата» , краткий или длинный, или «(все форматы)» – дата может быть записана различными способами: ДД.ММ.ГГГГ, ДД.МММ, МММ.ГГ.

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

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

По цвету ячейки или текста

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

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

В следующем окне, уберите галочку с поля , если Вы выделили их без верхней строки, которая является шапкой таблицы. Затем выбираем столбец, по которому будем сортировать, в примере это «I» . В разделе «Сортировка» из выпадающего списка выбираем «Цвет шрифта» . В разделе порядок выбираем «красный цвет» – «Сверху» . Это мы отсортировали числа красного цвета.

Теперь нужно, чтобы в столбце шли числа зеленого цвета. Нажмите на кнопочку «Добавить уровень» . Все настройки те же, только выберите «зеленый цвет» . Нажмите «ОК» .

Наш столбец отсортирован следующим образом.

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

Теперь наш столбец отсортирован и по цвету текста и в порядке возрастания данных.

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

Таблицы

Если у Вас есть таблица, в которой нужно выполнить сортировку сразу по нескольким столбцам, делаем следующее. Выделяем весь диапазон ячеек таблицы вместе с шапкой. Кликаем по кнопочке «Сортировка и фильтр» и выбираем «Настраиваемая …» .

Давайте отсортируем класс в порядке возрастания, и таким же образом средний бал.

В окне сортировки ставим галочку в поле «Мои данные содержат заголовки» . В разделе «Столбец» выбираем из списка «Класс» , сортировка по «Значению» , а порядок «По возрастанию» .

Чтобы сделать все тоже самое по среднему балу, нажмите на кнопочку «Добавить уровень» . В разделе «Столбец» выбираем «Средн.бал» . Нажмите «ОК» .

Данные в таблице отсортированы.

Теперь в столбце «Имя» закрасим ячейки с мальчиками в синий цвет, ячейки с девочками в розовый. Чтобы не делать это для каждой ячейки в отдельности, прочтите статью, как выделить ячейки в Excel – в ней написано, как выделить несмежные ячейки.

Выполним сортировку этого столбца по цвету ячейки: сначала будут девочки, потом мальчики. Снова выделяем всю таблицу, жмем «Сортировка» – «Настраиваемая …» .

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

Нажимаем на кнопку «Добавить уровень» . В разделе «Столбец» выбираем «Имя» , сортировка – «Цвет ячейки» , порядок – «розовый» , «Сверху» .

Теперь с помощью стрелочек перемещаем данную строку наверх списка. Нажмите «ОК» .

Таблица с отсортированными данными выглядит следующим образом.

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

Существуют три типа сортировки:

• в возрастающем порядке
• в убывающем порядке
• в пользовательском порядке

Сортировка списка по возрастанию означает упорядочение списка в порядке: от 0 до 9, пробелы, символы, буквы от А до Z или от А до Я, а по убыванию - в обратном порядке. Пользовательский порядок сортировки задается пользователем в окне диалога "Параметры" на вкладке "Списки", которое открывается командой "Параметры" в меню "Сервис", а отображается этот порядок сортировки в окне диалога "Параметры сортировки" (Рис. 1).

Рис. 1.

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

В окне "Параметры сортировки" (Рис. 2) из списка "Сортировка по первичному ключу" можно выбрать пользовательский порядок сортировки, а также установить переключатель "Сортировать" в положение: "строки диапазона" или "столбцы диапазона".

Рис. 2.

В окне "Сортировка диапазона" (Рис. 3) можно установить переключатель в положение: "по возрастанию" или "по убыванию", а также выбрать положение переключателя идентификации диапазона данных.

Рис. 3.

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

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

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

• Выделите ячейку в списке, который требуется отсортировать;
• Выполните команду "Данные" - "Сортировка", открывается окно диалога "Сортировка диапазона";
• В списке "Сортировать по" выберите заголовок того столбца, по которому будете осуществлять сортировку;
• Выберите тип сортировки "По возрастанию" или "По убыванию";

На рисунках 4 и 5 представлены фрагменты списка до сортировки, и после сортировки "по возрастанию" по одному столбцу "№ склада".

Рис. 4.

Рис. 5.

При сортировке списка по нескольким столбцам, строки с одинаковыми значениями в первом сортируемом столбце ("Сортировать по"), сортируются в порядке, который определяется заголовком, указанным в списке "Затем по", а строки с одинаковыми значениями в первых двух сортируемых столбцах сортируются по заголовку, указанному в списке "В последнюю очередь, по". Другими словами столбцы, указанные в списках "Затем по" и "В последнюю очередь по" не влияют на порядок сортировки в первом сортируемом столбце, а устраняют неоднозначность в отсортированном списке.

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

• Выделите ячейку в списке;
• Выберите заголовок для сортировки в списке "Сортировать по" и установите порядок сортировку "по возрастанию" или "по убыванию";
• Откройте список "Затем по", установите заголовок другого столбца для сортировки и задайте сортировку "по возрастанию" или "по убыванию";
• Раскройте список "В последнюю очередь по" и выберите заголовок третьего столбца для сортировки и укажите сортировку "по возрастанию" или "по убыванию";
• Нажмите кнопку ОК для выполнения сортировки.

Алгоритм сортировки данных по строкам

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

• Укажите ячейку в сортируемом списке;
• В меню "Данные" выберите команду "Сортировка";
• В окне "Сортировка диапазона" нажмите кнопку "Параметры";
• Установите переключатель "Сортировать" в положение "столбцы диапазона" и нажмите кнопку OK;
• В окне "Сортировка диапазона" выберите строки, по которым требуется отсортировать столбцы в списках "Сортировать по", "Затем по", "В последнюю очередь, по".
• Нажмите кнопку ОК для выполнения сортировки

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

Алгоритм сортировки в пользовательском порядке

• Укажите ячейку в списке;
• В меню "Данные" выберите команду "Сортировка";
• Выберите в списке "Сортировать по" заголовок столбца для сортировки;
• Установите переключатель в положение "по возрастанию" или "по убыванию";
• Нажмите кнопку "Параметры";
• В раскрывающем списке "Порядок сортировки по первому ключу" выберите необходимый порядок сортировки и нажмите кнопку OK;
• Нажмите кнопку ОК в окне "Сортировка диапазона" для завершения сортировки.

В большинстве случаев работы с данными они организованны вертикально, поэтому когда подразумевается обработка имеется ввиду именно вертикальная обработка данных, включая сортировку. Т.е. если нужно отсортировать числа по возрастанию, подразумевается, что вверху списка будут числа с минимальными значениями, а внизу списка с максимальными. Именно с такой “вертикальной” организацией данных и связана чрезвычайная популярность (относительно той же ).

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

Но, все-таки, как отсортировать ячейки Excel по горизонтали ? Для горизонтальной сортировки в Excel припасено несколько вариантов, которые дополняют друг друга:

1. С помощью стандартного механизма настраиваемой сортировки: Вкладка “Главная”/ группа “Редактирование”/ команда “Сортировка и фильтр”/ “Настраиваемая сортировка”. Далее, в параметрах выбираем сортировку по столбцам диапазона.

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

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

1. С помощью механизма . Мы уже неоднократно использовали данный инструмент при решении конкурсных заданий ExcelWorldChamp и . На первый взгляд решение может показаться громоздким, однако, по мере освоения Power Query, можно привыкнуть. А, если вы планируете заниматься обработкой данных в Excel, то без использования данного инструмента сейчас просто нельзя.

Используем горизонтальную сортировку с помощью Power Query

Для совершения каких либо манипуляций в Power Query данные необходимо предварительно загрузить. На вкладке данные, в зависимости от типа источника данных пользуемся соответствующей командой. В нашем, простом случае, – это “Из таблицы/диапазона”.

В самом окне редактора Power Query переходим на вкладку “Добавление столбца”, команда “Настраиваемый столбец”. В диалоговом окне настройки столбца вводим функцию List.Sort(), в качестве аргументов передаем столбцы. По умолчанию функция сортируем по возрастанию, поэтому этот опциональный параметр нужно обязательно указывать, если сортировка нужна в убывающем порядке.

List.Sort({[Столбец1],[Столбец2],[Столбец3],[Столбец4],[Столбец5],[Столбец6],[Столбец7]}, Order.Ascending)

что равноценно

List.Sort({[Столбец1],[Столбец2],[Столбец3],[Столбец4],[Столбец5],[Столбец6],[Столбец7]})

а вот формула для убывающего порядка

List.Sort({[Столбец1],[Столбец2],[Столбец3],[Столбец4],[Столбец5],[Столбец6],[Столбец7]}, Order.Descending)

Поскольку результатом получается элемент типа список, нужно определиться с дальнейшими действиями сортировки, если нужно получить столбец со значениями, выбираем опцию “Развернуть в новые строки”, однако, если идет сортировка для большого количества данных, лучше воспользоваться опцией “Извлечь значения…”. При извлечении значений все они извлекаются в одну ячейку с определенным разделителем (ну или без). Если потом возникнет необходимость разнести значения по ячейкам сделать это можно несколькими способами, мы их рассмотрели в данном , однако, лучшим вариантом будет воспользоваться командой “Разделить столбец” вкладки “Главная” в самом Power Query.

Отсортируем формулами таблицу, состоящую из 2-х столбцов. Сортировку будем производить по одному из столбцов таблицы (решим 2 задачи: сортировка таблицы по числовому и сортировка по текстовому столбцу). Формулы сортировки настроим так, чтобы при добавлении новых данных в исходную таблицу, сортированная таблица изменялась динамически. Это позволит всегда иметь отсортированную таблицу без вмешательства пользователя. Также сделаем двухуровневую сортировку: сначала по числовому, затем (для повторяющихся чисел) - по текстовому столбцу.

Пусть имеется таблица, состоящая из 2-х столбцов. Один столбец – текстовый: Список фруктов ; а второй - числовой Объем Продаж (см. файл примера ).

Задача1 (Сортировка таблицы по числовому столбцу)

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

Для наглядности величины значений в столбце Объем Продаж выделены с помощью (). Также желтым выделены повторяющиеся значения.

Примечание : Задача сортировки отдельного столбца (списка) решена в статьях и .

Решение1

Если числовой столбец гарантировано не содержит значений, то задача решается легко:

• Числовой столбец отсортировать функцией НАИБОЛЬШИЙ() (см. статью );
• Функцией ВПР() или связкой функций ИНДЕКС()+ПОИСКПОЗ() выбрать значения из текстового столбца по соответствующему ему числовому значению.

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

Поэтому механизм сортировки придется реализовывать по другому.

ИНДЕКС(Продажи;
ОКРУГЛ(ОСТАТ(НАИБОЛЬШИЙ(
--(СЧЁТЕСЛИ(Продажи;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
СТРОКА()-СТРОКА($E$6));1)*1000;0)
)

Данная формула сортирует столбец Объем продаж (динамический диапазон Продажи ) по убыванию. Пропуски в исходной таблице не допускаются. Количество строк в исходной таблице должно быть меньше 1000.

Разберем формулу подробнее:

• Формула СЧЁТЕСЛИ(Продажи;"<"&Продажи) возвращает массив {4:5:0:2:7:1:3:5}. Это означает, что число 64 (из ячейки B7 исходной таблицы, т.е. первое число из диапазона Продажи ) больше 4-х значений из того же диапазона; число 74 (из ячейки B8 исходной таблицы, т.е. второе число из диапазона Продажи ) больше 5-и значений из того же диапазона; следующее число 23 - самое маленькое (оно никого не больше) и т.д.
• Теперь вышеуказанный массив целых чисел превратим в массив чисел с дробной частью, где в качестве дробной части будет содержаться номер позиции числа в массиве: {4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008}. Это реализовано выражением &","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6)) Именно в этой части формулы заложено ограничение о не более 1000 строк в исходной таблице (см. выше). При желании его можно легко изменить, но это бессмысленно (см. ниже раздел о скорости вычислений).
• Функция НАИБОЛЬШИЙ() сортирует вышеуказанный массив.
• Функция ОСТАТ() возвращает дробную часть числа, представляющую собой номера позиций/1000, например 0,005.
• Функция ОКРУГЛ() , после умножения на 1000, округляет до целого и возвращает номер позиции. Теперь все номера позиций соответствуют числам столбца Объемы продаж, отсортированных по убыванию.
• Функция ИНДЕКС() по номеру позиции возвращает соответствующее ему число.

Аналогичную формулу можно написать для вывода значений в столбец Фрукты =ИНДЕКС(Фрукты;ОКРУГЛ(...))

В файле примера , из-за соображений скорости вычислений (см. ниже), однотипная часть формулы, т.е. все, что внутри функции ОКРУГЛ() , вынесена в отдельный столбец J . Поэтому итоговые формулы в сортированной таблице выглядят так: =ИНДЕКС(Фрукты;J7) и =ИНДЕКС(Продажи;J7)

Также, изменив в формуле массива функцию НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ() получим сортировку по возрастанию.

Для наглядности, величины значений в столбце Объем Продаж выделены с помощью (Главная/ Стили/ Условное форматирование/ Гистограммы ). Как видно, сортировка работает.

Тестируем

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

1. В ячейку А15 исходной таблицы введите слово Морковь ;
2. В ячейку В15 введите Объем продаж Моркови = 25;
3. После ввода значений, в столбцах D и Е автоматически будет отображена отсортированная по убыванию таблица;
4. В сортированной таблице новая строка будет отображена предпоследней.

Скорость вычислений формул

На "среднем" по производительности компьютере пересчет пары таких формул массива, расположенных в 100 строках, практически не заметен. Для таблиц с 300 строками время пересчета занимает 2-3 секунды, что вызывает неудобства. Либо необходимо отключить автоматический пересчет листа (Формулы/ Вычисления/ Параметры вычисления ) и периодически нажимать клавишу F9 , либо отказаться от использования формул массива, заменив их столбцами с соответствующими формулами, либо вообще отказаться от динамической сортировки в пользу использования стандартных подходов (см. следующий раздел).

Альтернативные подходы к сортировке таблиц

Отсортируем строки исходной таблицы с помощью стандартного фильтра (выделите заголовки исходной таблицы и нажмите CTRL+SHIFT+L ). В выпадающем списке выберите требуемую сортировку.

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

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

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

Как и в предыдущей задаче предположим, что в столбце, по которому ведется сортировка имеются повторы (названия Фруктов повторяются).

Для сортировки таблицы придется создать 2 служебных столбца (D и E).

=СЧЁТЕСЛИ($B$7:$B$14;"<"&$B$7:$B$14)+1

Эта формула является аналогом для текстовых значений (позиция значения относительно других значений списка). Текстовому значению, расположенному ниже по алфавиту, соответствует больший "ранг". Например, значению Яблоки соответствует максимальный "ранг" 7 (с учетом повторов).

В столбце E введем обычную формулу:

=СЧЁТЕСЛИ($D$6:D6;D7)+D7

Эта формула учитывает повторы текстовых значений и корректирует "ранг". Теперь разным значениям Яблоки соответствуют разные "ранги" - 7 и 8. Это позволяет вывести список сортированных значений. Для этого используйте формулу (столбец G):

=ИНДЕКС($B$7:$B$14;ПОИСКПОЗ(СТРОКА()-СТРОКА($G$6);$E$7:$E$14;0))

Аналогичная формула выведет соответствующий объем продаж (столбец Н).

Задача 2.1 (Двухуровневая сортировка)

Теперь снова отсортируем исходную таблицу по Объему продаж. Но теперь для повторяющихся значений (в столбце А три значения 74), соответствующие значения выведем в алфавитном порядке.

Для этого воспользуемся результатами Задачи 1.1 и Задачи 2.

Подробности в файле примера на листе Задача2.

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

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

Сортировку данных в программе Microsoft Excel можно выполнять, воспользовавшись кнопкой «Сортировка и фильтр», которая размещена во вкладке «Главная» на ленте в блоке инструментов «Редактирование». Но, прежде, нам нужно кликнуть по любой ячейке того столбца, по которому мы собираемся выполнить сортировку.

Например, в предложенной ниже таблице следует отсортировать сотрудников по алфавиту. Становимся в любую ячейку столбца «Имя», и жмем на кнопку «Сортировка и фильтр». Чтобы имена упорядочить по алфавиту, из появившегося списка выбираем пункт «Сортировка от А до Я».

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

Для того, чтобы выполнить сортировку в обратном порядке, в том же меню выбираем кнопку Сортировка от Я до А».

Список перестраивается в обратном порядке.

Нужно отметить, что подобный вид сортировки указывается только при текстовом формате данных. Например, при числовом формате указывается сортировка «От минимального к максимальному» (и, наоборот), а при формате даты – «От старых к новым» (и, наоборот).

Настраиваемая сортировка

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

А, что делать, если мы хотим отсортировать имена по алфавиту, но например, при совпадении имени сделать так, чтобы данные располагались по дате? Для этого, а также для использования некоторых других возможностей, все в том же меню «Сортировка и фильтр», нам нужно перейти в пункт «Настраиваемая сортировка…».

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

В поле «Столбец» указываем наименование столбца, по которому будет выполняться сортировка. В нашем случае, это столбец «Имя». В поле «Сортировка» указывается, по какому именно типу контента будет производиться сортировка. Существует четыре варианта:

• Значения;
• Цвет ячейки;
• Цвет шрифта;
• Значок ячейки.

Но, в подавляющем большинстве случаев, используется пункт «Значения». Он и выставлен по умолчанию. В нашем случае, мы тоже будем использовать именно этот пункт.

В графе «Порядок» нам нужно указать, в каком порядке будут располагаться данные: «От А до Я» или наоборот. Выбираем значение «От А до Я».

Итак, мы настроили сортировку по одному из столбцов. Для того, чтобы настроить сортировку по другому столбцу, жмем на кнопку «Добавить уровень».

Появляется ещё один набор полей, который следует заполнить уже для сортировки по другому столбцу. В нашем случае, по столбцу «Дата». Так как в данных ячеек установлен формат даты, то в поле «Порядок» мы устанавливаем значения не «От А до Я», а «От старых к новым», или «От новых к старым».

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

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

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

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

Теперь, по аналогии с предыдущим примером, можно вписывать данные для сортировки. Вводим данные, и жмем на кнопку «OK».

Как видим, после этого, столбцы поменялись местами, согласно введенным параметрам.

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

Фильтр

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

Чтобы воспользоваться данной функцией, становимся на любую ячейку в таблице (а желательно в шапке), опять жмем на кнопку «Сортировка и фильтр» в блоке инструментов «Редактирование». Но, на этот раз в появившемся меню выбираем пункт «Фильтр». Можно также вместо этих действий просто нажать сочетание клавиш Ctrl+Shift+L.

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

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

Когда процедура выполнена, жмем на кнопку «OK».

Как видим, в таблице остались только строки с именем работника Николаева.

Усложним задачу, и оставим в таблице только данные, которые относятся к Николаеву за III квартал 2016 года. Для этого, кликаем по значку в ячейке «Дата». В открывшемся списке, снимаем галочки с месяцев «Май», «Июнь» и «Октябрь», так как они не относятся к третьему кварталу, и жмем на кнопку «OK».

Как видим, остались только нужные нам данные.

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

Если же вы хотите сбросить фильтр в целом по таблице, тогда нужно нажать кнопку «Сортировка и фильтр» на ленте, и выбрать пункт «Очистить».

Если нужно полностью удалить фильтр, то, как и при его запуске, в этом же меню следует выбрать пункт «Фильтр», или набрать сочетание клавиш на клавиатуре Ctrl+Shift+L.

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

Умная таблица

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

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

После этого, открывается диалоговое окно, в котором можно изменить координаты таблицы. Но, если вы ранее выделили область правильно, то больше ничего делать не нужно. Главное, обратите внимание, чтобы около параметра «Таблица с заголовками» стояла галочка. Далее, просто нажать на кнопку «OK».

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

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

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

При нажатии на этот значок, будут доступны все те же функции, что и при запуске фильтра стандартным способом через кнопку «Сортировка и фильтр».

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