Создание и ведение списков – ЧАСТЬ 6

Excel также предполагает, что вы хотите выполнить сортировку по столбцу, содержащему активную ячейку, и  включает в поле Сортировать по  (Sort  By) либо заголовок этого столбца  (если у  вас  есть  строка   заголовков),  либо букву,  обозначающую  столбец. Поскольку  пользователи, как правило, предпочитают сортировку по возрастанию,  Excel устанавливает по умолчанию кнопки По возрастанию  (Ascending).  Если у вас иные намерения,  измените параметры сортировки и щелкните на кнопке ОК.

СОВЕТ

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

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

Сортировка по одному столбцу хороша, но максимум удовольствия от  мощи Excel дает упорядочивание сразу по двум и  даже по трем столбцам.  Для этого введите значения в одно  или   оба  поля  Затем  по  (Then  By)  в  диалоговом окне  Сортировка диапазона. Например, чтобы отсортировать список персонала с того же рис. 28.1, сначала в порядке убывания по столбцу Оклад и одновременно («затем») в порядке возрастания по столбцу Фамилия, заполните диалоговое  окно  так,  как  показано  на   рис.  28.15.  Результат продемонстрирован на рис. 28.16.

Рис. 28.15. Чтобы выполнить сортировку по двум столбцам, введите заголовки столбцов в поля Сортировать по и Затем по

Рис.  28.16.  Строки  теперь  расположены  в  порядке  убывания   окладов;  строки  с одинаковыми окладами упорядочены в алфавитном порядке по фамилиям

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

Если перед выбором команды Сортировка из меню Данные вы выделите  одну ячейку, Excel просканирует всю область, окружающую эту ячейку,  выделит весь непрерывный диапазон  ячеек   и    предположит, что   вас   интересует  весь   диапазон.  Поэтому   при необходимости  отсортировать только часть списка выделите только те строки и столбцы, которые  действительно следует упорядочить. Затем выполните команду  Сортировка из меню Данные. Так, чтобы отсортировать в нашем примере строки с 10-й по 20-ю (см. рис. 28.1), выделите ячейки А10:G20.

Нельзя указать обрабатываемый диапазон в диалоговом окне Сортировка диапазона. Вы должны  выделить диапазон перед  тем,  как  открыть  это  окно.  В  самом  окне  адрес сортируемого диапазона не  отображается.  Подстрахуйтесь  лишний раз  —  проверьте результат сортировки: если он не совпадает с ожидаемым, выполните команду Отменить (Undo).

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

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

1.   Выберите команду Сортировка в меню Данные.

2.   В диалоговом окне Сортировка диапазона нажмите кнопку Параметры (Options) и установите переключатель в положение Столбцы диапазона (Sort Left To Right).

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

4.   Заполните поля, настройте переключатели и нажмите кнопку ОК.

На рис. 28.17 и 28.18 показан лист перед сортировкой столбцов и после нее.

Рис. 28.17. В этом отчете годы расположены по возрастанию

Рис. 28.18. Тот же отчет, где порядок летоисчисления изменен на обратный

Этот результат был достигнут при помощи следующей процедуры:

1.   Мы выделили ячейки B1:F4.

2.   Затем выбрали команду Сортировка в меню Данные.

3.   В диалоговом окне Сортировка диапазона нажали кнопку Параметры.

4.   Установили переключатель в положение Столбцы диапазона и нажали ОК.

5.   Определили направление сортировки и снова нажали ОК.

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

Сортировка ячеек с формулами

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

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

Пример листа до и после сортировки на рис. 28.19 демонстрирует указанные выше сложности. В строке  5  вычисляется изменение  прибыли за  год  с  использованием относительных ссылок. Например, в ячейке С5 находится формула =С4-В4 для вычисления разности в прибыли за 2000 и 1999   годы.   Другие   формулы   также   ссылаются   на   ячейки,  расположенные   в   столбцах непосредственно слева от формул.

Рис. 28.19. Сортировка столбцов в этом списке испортила формулы в строке 5

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

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

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

соблюдайте следующие правила:

? в  формулах,  которые  содержат  ссылки на  ячейки,   расположенные  вне  списка,

используйте сугубо абсолютные ссылки;

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

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

Последовательность сортировки данных в Excel

Чтобы результаты сортировки не были для вас неожиданными, ознакомьтесь с основными правилами, которыми руководствуется Excel:

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

? Числовые данные имеют больший приоритет  относительно текстовых  значений. То есть значение 98052 будет выше по списку значения 123РА, поскольку первое является числом, а второе — текстом.

? Приоритеты символов при сортировке алфавитных и алфавитно-цифровых следующие: 0123456789 (пробел) !0$%&()*,./:;?@[\]^-`{ | }~ + < = > a b с … х у z а б в … э ю я

? Апострофы и  дефисы игнорируются. Однако если два значения  идентичны  без учета дефиса, тогда то, которое без дефиса, считается меньше значения с дефисом.

? Логические  значения размещаются  после  текстовых  значений, и  ЛОЖЬ  (FALSE)

считается меньше ИСТИНА (TRUE).

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

? Пустые ячейки попадают в конец списка как при сортировке  по возрастанию, так и при сортировке  по убыванию.

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

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

…; Январь, Февраль, Март, …). Если в ячейках столбца присутствуют такие названия дней недели или  месяцев, вы можете упорядочить столбец  хронологически.  Еще вы вправе создать свои собственные списки  и сортировать текстовые поля с помощью этих списков.

СМОТРИ ТАКЖЕ

Информация о создании пользовательских списков и работе с ними  приведена в разделе

«Создание пользовательских списков» главы 7.

Чтобы   применить для   сортировки пользовательский  список,  в   диалоговом  окне Сортировка диапазона нажмите кнопку Параметры и затем раскройте список Сортировка по  первому  ключу  (Fisrt  Key  Sort  Order).  Вы  увидите  четыре  встроенных  пользовательских списка наряду с другими, сформированными  вами.

Сортировка с учетом регистра букв

Обычно Excel не учитывает регистр букв при сортировке текстовых значений.  Другими словами, программа рассматривает букву «А» как  полный эквивалент буквы «а». При желании изменить стандартное  поведение программы  нажмите кнопку  Параметры  в диалоговом окне Сортировка диапазона и  затем установите флажок Учитывать регистр (Case Sensitive).

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий