Отсортируем формулами таблицу, состоящую из 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.

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

Возрастающий порядок сортировки:

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

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

Текст будет отсортирован по алфавиту. При этом сначала будут расположены заданные в качестве текста числовые значения.

При сортировке в возрастающем порядке логических значений сначала будет отображено значение ЛОЖЬ, а затем – значение ИСТИНА.

Значения ошибки будут отсортированы в том порядке, в котором они были обнаружены (с точки зрения сортировки все они равны).

Пустые ячейки будут отображены в конце отсортированного списка.

Убывающий порядок сортировки:

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

Пользовательский порядок сортировки:

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

Сортировка списка

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

Excel автоматически выделит список и выведет на экран диалоговое окно “Сортировка диапазона” в котором нужно указать параметры сортировки.

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

Excel автоматически распознает имена полей, если формат ячеек, содержащих имена, отличается от формата ячеек с данными.

Диалоговое окно “Сортировка диапазона”.

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

Заданные в диалоговом окне “Сортировка” диапазона и “Параметры сортировки” параметры будут сохранены и отображены в диалоговом окне при следующем его открытии.

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

26. Фильтрация данных в Excel.

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

Автофильтр

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

Вставка автофильтра

1. Поместите указатель ячейки внутри списка.

2. В подменю Данные – Фильтр выберите команду “Автофильтр”. Рядом с именами полей будут отображены кнопки со стрелками, нажав которые, можно открыть список.

3. Откройте список для поля, значение которого хотите использовать в качестве фильтра (критерия отбора). В списке будут приведены значения ячеек выбранного поля.

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

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

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

Если перед выполнением команды “Автофильтр” Вы выделили один или несколько столбцов, то раскрывающиеся списки будут добавлены только соответствующим полям.

Чтобы снова отобразить на экране все записи списка, выполните команду “Отобразить все” из подменю Данные – Фильтр.

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

Чтобы деактивировать функцию автофильтра (удалить раскрывающиеся списки), выберите повторно команду Данные – Фильтр – Автофильтр.

Применение пользовательского автофильтра

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

Вставьте в список автофильтр, выбрав команду Данные – Фильтр – Автофильтр.

Откройте список автофильтра для нужного поля и выберите в нем элемент (Условие).

В открывшемся диалоговом окне “Пользовательский автофильтр” (Рис. 6.3.27.) укажите первый критерий.

Выберите логический оператор, объединяющий первый и второй критерии.

Диалоговое окно “Пользовательский автофильтр”.

Вы можете задать для отдельного поля в пользовательском автофильтре один или два критерия. В последнем случае их можно объединить логическим оператором “И” либо “ИЛИ”.

Задайте второй критерий.

Нажмите кнопку “OK”. Excel отфильтрует записи в соответствии с указанными критериями.

Расширенный фильтр

Для задания сложных условий фильтрации данных списка Excel предоставляет в помощь пользователю так называемый расширенный фильтр.

Диапазон критериев

Критерии можно задать в любом свободном месте рабочего листа. В диапазоне критериев Вы можете вводить и сочетать два типа критериев:

Простые критерии: программа сравнит содержимое полей с заданным критерием (аналогично применению автофильтра).

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

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

В строке (строках), расположенной непосредственно под строкой с именами полей, следует задать критерии. При задании критериев Excel не различает регистр. Вы можете использовать при задании критериев символы подстановки? и *.

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

Применение расширенного фильтра

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

1. Поместите указатель ячейки внутри списка. В этом случае Excel автоматически распознает диапазон списка и представит ссылку на диапазон в диалоговом окне.

2. Выполните команду Данные – Фильтр – Расширенный фильтр. Поместите курсор ввода в поле “Диапазон условий” и выделите соответствующий диапазон в рабочем листе.

3. Закройте диалоговое окно нажатием кнопки “ОК”. На экране теперь будут отображены записи, удовлетворяющие заданным критериям.

Вы можете применить в рабочем листе только один расширенный фильтр.

Если в результате применения расширенного фильтра не должны быть отображены повторяющиеся записи, в диалоговом окне “Расширенный фильтр” установите флажок параметра “Только уникальные записи”.

Чтобы после фильтрации задать отображение в рабочем листе всех записей списка, выполните команду Данные – Фильтр – Отобразить все.

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

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

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

Три главных алгоритма сортировки в Excel.

1. числовые значения, сортируются по принципу «от меньшего к большему» или наоборот.

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

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

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

Продолжим работу с базой данных БД2 «Выпуск металлоконструкций участком №2», созданной в статье « ».

Рассматриваемая учебная база данных состоит всего из 6-и полей (столбцов) и 10-и записей (строк). Реальные базы данных обычно содержат более десятка полей и тысячи записей! Найти необходимую информацию в такой таблице очень не просто! Именно через призму такого понимания необходимо смотреть на последующие наши действия.

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

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

В производстве при грамотном построении и тщательном ведении баз данных применение результатов анализа позволяет удвоить, а иногда и утроить объемы выпуска продукции на тех же площадях, оборудовании и с тем же составом ИТР.

Простейшая сортировка.

Простейшая сортировка в Excel осуществляется с помощью кнопок «Сортировка по возрастанию» и «Сортировка по убыванию», расположенных на панели инструментов «Стандартная». (На рисунке ниже эти кнопки обведены красным эллипсом.)

Задача №1:

Определить: какое из выпущенных изделий самое тяжелое и какова его масса? Когда это изделие было изготовлено?

1. Открываем в MS Excel файл .

2. Активируем щелчком мыши ячейку E7 с заголовком столбца «Масса 1шт, т» (можно активировать любую ячейку в интересующем нас столбце).

3. Нажимаем кнопку «Сортировка по убыванию» на панели инструментов «Стандартная».

4. Считываем ответ на поставленный вопрос в самой верхней строке базы данных (строка №8). Самое тяжелое изделие в базе – Балка 045 из заказа № 2. Изготавливалась Балка 045 с 23-его по 25-е апреля 2014-ого года (смотри записи в строках Excel №8-10).

5. Вернуть базе данных вид, предшествовавший сортировке в Excel можно (если нужно), нажав кнопку «Отменить» той же панели инструментов «Стандартная». Или можно применить «Сортировку по возрастанию» для столбца «Дата» базы данных.

Сортировка в Excel по нескольким столбцам.

Сортировка этим способом может быть осуществлена последовательно по двум или трем столбцам.

Задача №2:

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

1. Активируем щелчком мыши любую ячейку базы данных (например, ячейку C11).

2. Нажимаем кнопку главного меню «Данные» и выбираем «Сортировка…».

3. В выпавшем окне «Сортировка диапазона» выбираем из выпадающих списков значения так, как показано на снимке с экрана слева и нажимаем «ОК».

4. Задача №2 выполнена. Записи, во-первых, отсортированы по номерам заказов и, во-вторых, внутри каждого из заказов расположены по алфавиту имен изделий.

Итоги.

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

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

Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!

Уважаемые читатели, отзывы и замечания пишите в комментариях.

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

Рис. 1. Сортировка по полю Заказчик : (а) по умолчанию – от А до Я; (б) в порядке уменьшения дохода; (в) порядок сортировки по полю Заказчик не изменился при добавлении поля Сектор

Скачать заметку в формате или , примеры в формате

Сортировка заказчиков в порядке убывания дохода

Чтобы отсортировать строки сводной таблицы в порядке убывания дохода, выберите любую ячейку столбца Сумма по полю Доход , например, Е4 (но не заголовок), и щелкните на значке ЯА , находящемся на вкладке Данные (рис. 2). Подобная сортировка напоминает стандартную, но это лишь внешнее сходство. При выполнении сортировки сводной таблицы Excel создает правило, которое будет работать и после внесения дополнительных изменений в сводную таблицу.

На примере сводной таблицы, находящейся в столбцах G:I (рис. 1в), видно, что произойдет после добавления нового внешнего поля строки Сектор . Сводная таблица продолжает сортировать данные в порядке убывания дохода внутри каждого сектора. Например, в секторе Производство на первом месте находится компания General Motors с доходом 750 163 доллара. За ней следует компания Ford с доходом 622 794 доллара. Если даже удалить поле Заказчик из сводной таблицы, выполнить дополнительные настройки и вернуть это поле обратно, но уже в область столбцов, Excel запомнит сортировку заказчиков в порядке уменьшения дохода.

Чтобы в сводной таблице, находящейся в столбцах G:I (рис. 1в), секторы также были отсортированы в порядке убывания дохода, можно пойти одним из трех способов:

• Выделите ячейку G4, щелкните правой кнопкой мыши и выберите Свернуть всё поле , чтобы скрыть все элементы, которые относятся к заказчику. После того как на экране будут отображаться лишь одни секторы, выделите ячейку I4 и щелкните на значке ЯА на вкладке Данные для выполнения сортировки по убыванию. Таким образом, будет создано правило сортировки для поля Сектор . Повторно выделите ячейку G4, щелкните правой кнопкой мыши и выберите Развернуть всё поле.
• Временно удалите поле Заказчик из сводной таблицы, отсортируйте таблицу по убыванию дохода (методом, который был описан для рис. 2), а потом вновь верните поле Заказчик .
• Воспользуйтесь возможностями команды Дополнительные параметры сортировки (я пользуюсь именно этим методом). Чтобы вызвать команду: (а) выделите ячейку G4, щелкните правой кнопкой мыши и выберите Сортировка → Дополнительные параметры сортировки (рис. 3) или (б) кликните на значке треугольника в поле Сектор , а затем выберите пункт Дополнительные параметры сортировки (рис. 4). В обоих случаях откроется окно Сортировка (рис. 5). Установите переключатель в положение по убыванию и выберите строку Сумма по полю Доход .

Рис. 3. Вызов команды Дополнительные параметры сортировки правой кнопкой мыши

Рис. 4. Вызов команды Дополнительные параметры сортировки с помощью меню Сортировка и фильтры поля Сектор

Рис. 5. Настройка параметров в окне Сектор

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

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

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

Чтобы выполнить такую сортировку:

1. Раскройте список Заказчик, находящийся в ячейке А4.
2. Выберите параметр Дополнительные параметры сортировки.
3. В диалоговом окне Сортировка (Заказчик) щелкните на кнопке Дополнительно …
4. В диалоговом окне Дополнительные параметры сортировки (Заказчик) выберите раздел Порядок сортировки и установите переключатель Значения в выделенном столбце .
5. Щелкните в поле ссылки, а затем выберите ячейку С5. Обратите внимание на то, что нужно щелкнуть в одной из ячеек значений Устройство , поскольку на заголовке Устройство в ячейке С4 щелкнуть невозможно.
6. Чтобы завершить установку параметров дважды кликните ОK.

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

• А1, С1, D1, Е1, F1, F2, А30, F30 – не доступны
• А2:А29 – расположит по алфавиту имена заказчиков в столбце А
• В1, В2, С2, D2, E2 – расположит по алфавиту названия товаров в строке 2
• В30, С30, D30, E30 – расположит по убыванию (возрастанию) суммы дохода в строке 30
• по возрастанию (убыванию) продаж В3:В29 – модулей, С3:С29 – устройств, D3:D29 – деталей, Е3:Е29 – препаратов, F3:F29 – итого.

Сортировка вручную

Обратите внимание на то, что в диалоговом окне Сортировка (см. рис. 5) можно вручную определить правила сортировки данных. Но сортировка сводной таблицы вручную также выполняется другим, весьма необычным способом. В отчете сводной таблицы на рис. 8а показана последовательность категорий товаров, отсортированных в алфавитном порядке: Деталь, Модуль, Препарат и Устройство . Обратите внимание на то, что объем проданных товаров, относящихся к категории Деталь , не наибольший. И вряд ли стоит эту категорию отображать первой. Установите указатель мыши в ячейке Е4 и введите слово Деталь . Стоит лишь нажать клавишу Enter , как Excel определит, что вы решили переместить колонку Деталь в последний столбец таблицы. Все числовые значения, относящиеся к этой категории товаров, переместятся из столбца В в столбец Е. Значения, относящиеся к другим категориям товаров, сместятся влево. Подобное поведение выглядит нелогичным и присуще лишь сводным таблицам Excel. Обычный набор данных Excel переупорядочить таким образом не удастся. На рис. 8б показана сводная таблица после перемещения заголовка нового столбца в ячейку Е4.

Рис. 8. Сортировка вручную: (а) категории товаров отсортированы по алфавиту, (б) категория Деталь размещена последней

Любители мыши могут просто перетаскивать заголовки требуемых колонок (или отдельные строки). Щелкните в области заголовка столбца и удерживайте указатель мыши над границей диапазона выделенных ячеек до тех пор, пока он не приобретет вид четырехнаправленной стрелки. Начинайте перетаскивать ячейку в выбранное место; появится указатель в виде жирной линии и засечками. Как только вы отпустите кнопку мыши, числовые значения тут же переместятся в новую колонку. Учтите, что при использовании ручной сортировки товары, добавляемые в источник данных, добавляются в конец списка. Это связано с тем, что программа Excel не знает, куда именно нужно добавить новый регион.

Сортировка данных согласно пользовательским спискам

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

Чтобы создать собственный список сортировки, выполните следующие действия:

1. В свободной от данных области рабочего листа введите названия категорий товаров в последовательности, которая соответствует создаваемому пользовательскому списку. В каждой ячейке вводите по одному названию, а названия располагайте в одном столбце (рис. 9).
2. Выделите полученный список названий категорий товаров (ячейки А10:А13).
3. Выберите вкладку ленты Файл и в нижней части панели навигации, отображенной в окне слева, щелкните на кнопке Параметры для открытия диалогового окна Параметры Excel.
4. Выберите категорию Дополнительно , перейдите в раздел Общие и щелкните на кнопке Изменить списки .
5. В диалоговом окне Списки адрес диапазона, содержащего предварительно выделенный список названий, отображается в поле Импорт списка из ячеек (рис. 10). Щелкните на кнопке Импорт , чтобы сформировать новый список категорий товаров на основе указанных данных. Новый список добавляется в нижнюю часть области Списки .
6. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Списки . Щелкните еще раз на кнопке ОК для закрытия диалогового окна Параметры Excel .

Рис. 10. Окно Списки

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

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

1. Раскройте список поля Товар и выберите параметр Дополнительные параметры сортировки .
2. В диалоговом окне Сортировка (Товар) выберите кнопку по возрастанию (от А до Я) по полю , а в раскрывающемся списке выберите Товар .
3. Щелкните на кнопке Дополнительно …
4. В диалоговом окне Дополнительные параметры сортировки (Товар) отмените установку флажка Автосортировка .
5. Раскройте список Сортировка по первому ключу и выберите список, включающий названия категорий товара (рис. 12).
6. Дважды щелкните на кнопке ОК.

Заметка написана на основе книги Билл Джелен, Майкл Александер. . Глава 4.

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

Типы сортируемых данных и порядок сортировки

Сортировка числовых значений в Excel

Сортировка числовых значений по возрастанию - это такая расстановка значений, при которой значения располагаются от наименьшего к наибольшему (от минимального к максимальному).

Соответственно, сортировка числовых значений по убыванию - это расположение значений от наибольшего к наименьшему (от максимального к минимальному).

Сортировка текстовых значений в Excel

"Сортировка от А до Я" - сортировка данных по возрастанию;

"Сортировка от Я до А" - сортировка данных по убыванию.

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

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

Можно отсортировать текстовые данные с учетом регистра. Для этого необходимо в параметрах сортировки установить флажок в поле "Учитывать регистр".

Обычно буквы верхнего регистра имеют меньшие номера, чем буквы нижнего регистра.

Сортировка значений даты и времени

"Сортировка от старых к новым" - это сортировка значений даты и времени от самого раннего значения к самому позднему.

"Сортировка от новых к старым" - это сортировка значений даты и времени от самого позднего значения к самому раннему.

Сортировка форматов

В Microsoft Excel 2007 и выше предусмотрена сортировка по форматированию. Этот способ сортировки используется в том случае, если диапазон ячеек отформатирован с приминением цвета заливки ячеек, цвета шрифта или набора значков. Цвета заливок и шрифтов в Excel имеют свои коды, именно эти коды и используются при сортировке форматов.

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

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

Параметры сортировки

Сортировка по столбцу

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

Сортировка по строке

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

Многоуровневая сортировка

Итак, если производится сортировка по столбцу, то строки меняются местами, если данные сортируются по строке, то местами меняются столбцы.

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

Кроме всего прочего при сортировке можно учитывать, либо не учитывать регистр.

Надстройка для сортировки данных в Excel

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