Сортировка данных
Сортировка = для числового и алфавитного порядка





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

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Сортировка = для числового и алфавитного порядка

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

Упорядочивание записей при помощи инструмента Сортировка

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Сортировка строк, в том числе по двум и более столбцам

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

Итак, чтобы отсортировать таблицу, например, в порядке убывания суммы по столбцу Всего, руб., нужно выбрать любую заполненную ячейку этого столбца и выбрать команду Данные – Сортировка и фильтр ЯА [DataSort & Filter - ZA] – строки таблицы будут упорядочены в соответствии с этой настройкой. Причем, сортировка случилась не в пределах одного столбца, а в пределах всей таблицы – об этом очень хорошо говорит сбившийся № п/п. А еще хотелось бы обратить внимание на пару следующих моментов: заголовок таблицы в примере состоит всего из одной строки, а сама таблица не имеет пустых строк. Как отсортировать таблицу, заголовки которой состоят из двух и более строк, рассмотрим чуть ниже

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

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

Для этого воспользуемся основным окном настроек сортировки: находясь в любой заполненной ячейке этой таблицу выполните команду Данные – Сортировка и фильтр – Сортировка [Data Sort & FilterSort] и на экран будет выведено одноименное диалоговое окно, в котором буду зафиксированы настройки для уже выполненной сортировки по филиалу, нам же остается добавить еще один уровень. Нажмите в верхней левой части окна кнопку Добавить уровень [Add_Level] и в центральной части окна появится следующий ключ сортировки Затем по [Then_by], в котором в первом же списке выбираю поле Вид договора, а в правой части окна определяю Порядок [Order] как От Я до А [Z_to_A]. Жмем ОК и получаем требуемый результат:

 

Сортировка по заголовкам

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

Прежде чем нажимать кнопку Сортировка [Sort], нужно выделить диапазон сортировки, так как первые пять столбцов не нужно менять местами (помним, что в обычном режиме, то есть когда выбрана всего одна ячейка таблицы, программа автоматически определяет диапазон таблицы полностью). Чтобы быстро выделить нужный набор ячеек, выберите ячейку, с которой следует начать (у меня это ячейка F2), далее нажмите две клавиши Ctrl + Shift и удерживайте их, а теперь нажмите на клавиатуре еще две клавиши – стрелки вверх и вправо (то есть в том направлении, в котором необходимо выполнить выделение от стартовой ячейки). Клавиши можно отпустить, а вот команду выполнить уже пора Данные – Сортировка и фильтр – Сортировка [Data Sort & FilterSort] и в открывшемся окне настроек нажмите в верхнем правом углу кнопку Параметры [Options] – осталось выбрать переключатель столбцы диапазона и нажать ОК

И еще одно финальное действие – в качестве ключа указываем строку 2, ведь именно в ней находятся заголовки с названиями городов. Готово:

При желании можно выполнить двухуровневую сортировку столбцов – сначала вывести все города региона Зап., а затем все города филиала Вост. Такая отметка содержится в строке 3 – добавьте уровень сортировки через кнопку Добавить уровень [Add_Level] и выведите на первое место при помощи кнопок управления положением, что в верхней центральной части окна:

 

Сортировка по цвету ячейки или текста

Продолжаем работу в той же книге на листе Сортировка Цвет. В больших таблицах цвет может выступать в качестве ключа – например, особо важные позиции могут быть обозначены красным цветом, а второстепенные – серым или синим. И когда в определенный момент потребуется расположить все красные строки в верхней части таблицы, а серые в нижней – начинается паника и рутинная работа по вырезке-вставке строк. Хорошо еще, если приходит мысль в дополнительном столбце проставить, скажем, цифры 1 для всех красных строк и т.д. и выполнить сортировку по ним, но это тоже не самое оптимальное решение. Оказывается, при помощи окна настроек сортировки можно быстро и эффектно решить и такой вопрос. В моем примере буду выводить на первое место красные строки (Япония), а в самый низ таблицы – зеленые (Италия), синие и черные трогать не буду

Прежде чем делать настройки, нужно выделить диапазон сортировки, так как заголовок этой таблицы состоит из двух строк, а программа умеет автоматически определять строкой заголовка всего одну. Выделяем ячейку, с которой следует начать – у меня это B4 (я просто не хочу восстанавливать потом порядковый номер и в целом он не привязан к содержимому строк, поэтому можно не захватывать этот столбец) – и удерживая нажатыми клавиши Ctrl + Shift жмем стрелки вниз и вправо, и только после этого выполняем команду Данные – Сортировка и фильтр – Сортировка [Data Sort & FilterSort]. Обратите внимание, как программа определяет диапазон сортировки – отступает одну верхнюю строку, хотя к заголовку она не имеет никакого отношения. Чтобы восстановить справедливость – сбросьте флажок в верхней правой части окна Мои данные содержат заголовки [My_data_has_headers] и убедитесь, что первоначальное выделение диапазона восстановлено

Теперь настраиваем ключ сортировки, для этого в левом разделе Столбец [Column] выбираем под кнопкой выпадающего списка тот столбец, в котором содержатся цветные записи – у меня это один из столбцов B или C, - далее в центральной части ключа Сортировка [Sort_On] выбираем Цвет шрифта [Font_Color] и в правой части определяем, какой цвет должен быть Сверху [On_Top]:

Нажмите ОК и убедитесь, что начинается таблица с записей красного цвета, а остальные цвета остались расположены в первоначальном порядке, так как мы не его не определили. Поэтому нажмите еще раз кнопку Сортировка [Sort], а в нем – кнопку Копировать уровень [Copy_Level] и укажите в ключе Затем по [Then_by] какой цвет расположить далее Сверху [On_Top] таблицу, но уже под красным, либо Снизу [On_Bottom]:

 

Сортировка по месяцам и собственным спискам

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

Так как заголовок таблицы по-прежнему занимает две строки, сначала выделяем диапазон сортировки, а для этого выделяем ячейку, с которой следует начать – B4 – и удерживая нажатыми клавиши Ctrl + Shift жмем стрелки вниз и вправо, и только после этого выполняем команду Данные – Сортировка и фильтр – Сортировка [DataSort & FilterSort] и в открывшемся окне скидываем флажок Мои данные содержат заголовки [My_data_has_Headers], если программа изменила диапазон выделения. Выставляем настройки ключа сортировки, при этом Порядок [Order] нужно определить как Настраиваемый список [Custom_List]:

В открывшемся окне Списки [Custom_Lists] выберите список с полными названиями месяцев и нажмите ОК – записи будут выстроены в указанном порядке:

Аналогичным образом можно отсортировать таблицу в соответствии с внутренними списками компании. В Excel их конечно же нет, но есть возможность создать такие самостоятельно. Предлагаю соорудить список регионов, чтобы упорядочить заголовки таблицы не по алфавиту, а в соответствии с утвержденным перечнем компании. Для этого я выделила диапазон сортировки F2:Q41, нажала кнопку Сортировка [Sort], не забыла под кнопкой Параметры [Options] выбрать переключатель столбцы диапазона [Sort_left_to_right] и при настройке Порядка [Order] для команды Настраиваемый список [Custom_List] в левом списке выбрала строку НОВЫЙ СПИСОК [NEW_LIST], а затем в правой части перечислила элементы этого списка – каждый с новой строки, не указывая никаких разделителей в виде запятых или пр.:

Далее нажав на ОК получаем готовую таблицу:

Кстати, этот список будет доступен в любом файле на Вашем компьютере и возможности его использования не ограничиваются сортировкой. В любой пустой ячейке напишите Москва и потяните эту ячейку за нижний правый уголок (маркер заполнения) вниз либо вправо – будет построен список подразделений – по-моему здорово :) Столь полезный и часто используемый список всегда под рукой и не нужно за ним далеко идти. Ну а чтобы внести в него изменения или добавить еще один список, не нужно вызывать окно сортировки – можно добраться до этих списков другим путем: Файл – Параметры – Дополнительно – Общие – Изменить списки [FileOptionsAdvanced General Edit_Custom_Lists]. Для 2007-ой версии программы – цветная Кнопка Office – Параметры Excel – Общие – Изменить списки [Office Options_ExcelGeneral Edit_Custom_Lists]

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

Похожие материалы

Настройка искомого значения в числовом и текстовом форматах, с помощью символа звёздочки. Вычисление номера столбца.
Функция ВПР - профессиональные хитрости
Функция ВПР может научиться игнорировать формат ячеек и даже высчитывать номер столбца в таблице с помощью ПОИСКПОЗ.

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

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

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

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



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