Как сделать сортировку в excel 2003?

7. Работа со списками данных

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

В Microsoft Excel понятия список и база данных взаимозаменяемы. При создании списка на рабочем листе необходимо выполнять следующие условия:

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

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

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

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

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

При форматировании списка рекомендуется следовать следующим правилам:

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

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

3. Следите за отсутствием пустых строк и столбцов. В самом списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

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

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

1. Выберите команду Сервис =>Параметры, а затем — вкладку Правка.

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

7.1 Сортировка данных

Сортировка – это расположение записей списка по возрастанию или убыванию значений какого-либо столбца.

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

Сортировку можно выполнить:

а) кнопками панели инструментов (этот способ удобно использовать для сортировки списка по данным одного столбца);

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

Сортировка списка по данным одного столбца

  1. Активизировать любую ячейку в столбце, значения которого требуется отсортировать.
  2. В панели инструментов щелкнуть нужную кнопку:
  • — сортировать по возрастанию ;
  • – сортировать по убыванию;

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

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

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

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

Пример такого списка показан в таблице на рисунке 7.2.

Cортировка строк и столбцов в списках Excel

Курс дистанционного обучения:
«Экономическая информатика»
Модуль 2 (2,5 кредита): Прикладное программное обеспечение офисного назначения

Работа с таблицей Excel 2003 как с базой данных

2.2.5.3. Сортировка строк и столбцов списка Excel

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

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

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

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

Читать еще:  Описательная статистика в excel 2010 как сделать

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сортировка данных в Excel по строкам и столбцам с помощью формул

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

Числовые значения можно отсортировать по возрастанию и убыванию, текстовые – по алфавиту и в обратном порядке. Доступны варианты – по цвету и шрифту, в произвольном порядке, по нескольким условиям. Сортируются столбцы и строки.

Порядок сортировки в Excel

Существует два способа открыть меню сортировки:

  1. Щелкнуть правой кнопкой мыши по таблице. Выбрать «Сортировку» и способ.
  2. Открыть вкладку «Данные» — диалоговое окно «Сортировка».

Часто используемые методы сортировки представлены одной кнопкой на панели задач:

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

  1. Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
  2. Далее действуем в зависимости от поставленной задачи. Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач. Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида: Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.

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

Сортировка по цвету ячейки и по шрифту

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

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

  1. Выделяем столбец – правая кнопка мыши – «Сортировка».
  2. Из предложенного списка выбираем «Сначала ячейки с выделенным цветом».
  3. Соглашаемся «автоматически расширить диапазон».

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

В открывшемся окне вводим необходимые параметры:

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

Читать еще:  Как сделать dll для excel?

По такому же принципу сортируются данные по шрифту.

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

Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.

  1. Открываем меню «Настраиваемая сортировка». Назначаем первый критерий.
  2. Нажимаем кнопку «Добавить уровень».
  3. Появляются окошки для введения данных следующего условия сортировки. Заполняем их.

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

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

По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:

  1. В диалоговом окне «Настраиваемой сортировки» нажать кнопку «Параметры».
  2. В открывшемся меню выбрать «Столбцы диапазона».
  3. Нажать ОК. В окне «Сортировки» появятся поля для заполнения условий по строкам.

Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.

Случайная сортировка в Excel

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

Например, нужно расположить в случайном порядке набор неких чисел.

Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.

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

Динамическая сортировка таблицы в MS Excel

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

  1. Есть набор простых чисел, которые нужно отсортировать по возрастанию.
  2. Ставим курсор в соседнюю ячейку и вводим формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно так. В качестве диапазона указываем весь столбец. А в качестве коэффициента – функцию СТРОКА со ссылкой на первую ячейку.
  3. Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.

Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.

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

  1. Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.
  2. Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.
  3. В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.
  4. Если в исходный столбец будут добавляться строки, то вводим чуть модифицированную формулу: Добавим в диапазон «фрукты» еще одно значение «помело» и проверим:

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

Сортировка по цвету в MS Excel 2003

Забегая вперед скажу, что среди стандартных возможностей MS Excel 2003 сортировка по цвету не предусмотрена. Тем не менее, задача эта выполнима. И мы сейчас в этом убедимся. Но вначале пару слов о том, что имеется в виду и для чего это нужно.

С желанием отсортировать данные по цветам я столкнулся приблизительно в 2000 — 2001 году, работая заместителем главного бухгалтера одной крупной компании. Характер задач, которые мне приходилось решать практически ежедневно, был связан с достаточно нетривиальной обработкой баз данных. Причем базы эти были немаленькие… Понятное дело, что в процессе работы с данными я делал пометки. Проблемные моменты выделял одним цветом, внесенные изменения — другим и т. д. В какой-то момент передо мной неизбежно вставала одна и та же задача: как в отформатированной таблице выделить записи синего цвета? Или собрать вместе все изменения, которые я пометил желтым? Более того. Подобная задача возникала так часто, что мы с главбухом умудрились состряпать письмо в группу разработки Microsoft с предложением дополнить Excel такой удобной возможностью! Понятное дело, что реакции на это телодвижение не последовало. Но в один прекрасный момент все стало на свои места. Оказалось, что для решения проблемы нужна самая малость — создать пользовательскую функцию размером буквально в три строки. И сейчас я предлагаю посмотреть, как это сделать.

Для примера воспользуемся базой данных, фрагмент которой показан на рис. 1. В этой базе собраны сведения о кассовых операциях за сентябрь 2012 года. В исходной базе шесть полей: « Дата » — дата регистрации хозяйственной операции; « СчД », « СчК » — счет дебета и кредита поводки; « Д », « К » — сумма по дебету и кредиту; « Контрагент » — название контрагента. Отдельные записи в базе выделены цветом. Например, группа операций, где фигурирует сотрудник « Ильченко И.Е. », отмечена желтым фоном. Записи о сотруднике « Рудь Н.И. » выделены зеленым и т. д. Теперь наша задача — упорядочить таблицу, используя в качестве признака сортировки цвет заливки. В результате получится, что записи о каждом сотруднике будут собраны в один блок, анализировать их будет намного проще.

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

1. Открываем рабочую книгу MS Excel. Вызываем меню « Сервис → Макрос → Редактор Visual Basic » (в некоторых версиях MS Office можно воспользоваться комбинацией « Alt+F11 »). Откроется окно редактора « Visual Basic for application », изображенное на рис. 2.

2. В этом окне вызываем меню « Insert → Module » (вставить модуль). Откроется область для ввода текста программы.

Читать еще:  Как сделать открывающийся список в excel?

3. Печатаем текст модуля, который выглядит так:

Public Function ColorCeil ( Cell As Range)

4. Закрываем окно « Visual Basic », возвращаемся в рабочую книгу Excel с базой данных (рис. 1).

Функция готова, называется она « ColorCeil ». У функции единственный параметр — адрес ячейки в рабочей книге. Результат работы функции — это число, которое представляет собой код цвета заливки для указанной ячейки. Теперь можно приступить к редактированию таблицы, чтобы подготовить ее для сортировки. Делаем так:

1. Становимся в свободную колонку на рабочем листе. В базе на рис. 1 я выбрал столбец « G ».

2. В ячейку « G1 » печатаем заголовок колонки (на рис. 1 это текст « Пр »).

3. Переходим на ячейку « G2 ».

4. Вызываем меню « Вставка → Функция… ». Откроется окно Мастера функций, изображенное на рис. 3.

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

6. Из этого списка выбираем « ColorCeil ». Откроется окно для ввода параметров функции (рис. 4).

7. Оставаясь в области для ввода параметров, щелкаем на ячейке « A2 », — мы будем сортировать строки, используя цвет заливки ячеек в первой колонке таблицы.

8. В окне настройки параметров нажимаем « ОК ».

9. Копируем формулу из ячейки « G2 » вниз на всю высоту таблицы. В результате колонка « G » будет заполнена числовыми значениями, каждое из которых характеризует цвет фона в первой колонке соответствующей строки таблицы.

10. Выделяем базу данных.

11. Вызываем меню « Данные → Сортировка… ». Откроется окно настройки параметров, как на рис. 5.

12. Щелкаем на значке выпадающего списка « Сортировать по ». Из предложенных вариантов выбираем « Пр ».

13. Устанавливаем переключатель направления сортировки (на рис. 5 он имеет значение « по возрастанию »).

14. В окне настройки параметров сортировки нажимаем « ОК ». Excel отсортирует базу данных по значениям в колонке « Пр », как показано на рис. 6. Иными словами, он отсортирует записи с учетом цвета заливки, который указан для ячеек в первой колонке исходной базы данных.

Важно! Excel не считает изменение цвета редактированием ячейки и поэтому не обновляет значения на рабочем листе. Как следствие, после изменения цвета заливки результат функции « ColorCeil » автоматически обновляться не будет. Это можно проделать вручную, воспользовавшись комбинацией « Ctrl+Alt+F9 ». Однако на результат сортировки такая ситуация не влияет — в данном случае обновление функции Excel делаем своевременно.

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

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

1. Открываем таблицу, изображенную на рис. 1.

2. Становимся на свободную колонку. Пусть это будет столбец « H » (напомню, что в колонке « G » у нас находится функция для определения цвета заливки).

3. В ячейку « H1 » вводим название колонки, например, « Раб ».

4. В ячейку « H2 » вводим число « 1 ». В ячейку « H3 » вводим значение « 2 ».

5. Выделяем на рабочем листе блок « H2:H3 ».

6. Ставим указатель мышки на прямоугольный маркер в правом нижнем углу выделенного блока.

7. Когда курсор изменит свою форму, удерживаем левую кнопку мышки и растягиваем блок на всю высоту таблицы. Excel проанализирует выделенные ячейки и заполнит колонку « H » элементами арифметической прогрессии с шагом « 1 ». В результате этих действий в ячейки колонки « H » будут записаны значения « 1 », « 2 », « 3 » и т. д. Это и есть номера записей в исходной базе данных.

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

И последнее. На первый взгляд, заполнить колонку « Раб » можно при помощи формул. Например, ввести в « H2 » значение « 1 », в « H3 » написать формулу « =H2+1 » и скопировать ее вниз до конца таблицы. На самом деле это не так. При сортировке базы данных будет нарушена адресация ячеек. А в результате вместо значений формулы вернут сообщение об ошибке. Поэтому заполнение рабочей колонки копированием (в режиме прогрессии) в данном случае принципиально.

На сегодня все. Удачной работы! Жду ваших вопросов, замечаний и предложений на bk@id.factor.ua , nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .

Наш сайт корисний для вас?

Підписатися на найактуальнішу розсилку для бухгалтера бюджетної установи

Ссылка на основную публикацию
Adblock
detector