Сортировка списков и диапазонов

 

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

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

Стандартные средства Excel по умолчанию позволяют сортировать данные по трем признакам (графам таблицы).

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

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

 

Для сортировки списка наименований товаров в алфавитном порядке необходимо:

· Выделить блок ячеек В3:I18. Обратить внимание, что первая графа (№ п/п) таблицы не принимает участия в процессе сортировки, чтобы нумерация строк оставалась неизменной.

· Активизировать пункт меню Данные.

· Выбрать команду Сортировка.

· В окне Сортировать по из выпадающего списка выбрать «Наименование товаров».

· Установить переключатель По возрастанию.

· Установить переключатель Идентифицировать поля по в положение Подписям (первая строка диапазона).

· Щелкнуть по кнопке Параметры.

· Установить переключатель в положение Строки диапазона.

· Нажать ОК.

· В окне Сортировка диапазона нажать ОК.

· Снять выделение с диапазона ячеек в таблице.

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

 

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

 

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

· На листе «Отчет» выделить блок ячеек В3:I18.

· Выполнить команду меню Данные|Сортировка.

· В поле Сортировать по выбрать «Наименование магазина» (это поле называется первым ключом сортировки) и установить флажок По возрастанию. В поле Затем по (второй ключсортировки) выбрать «Вид продукции» и установить флажок По убыванию. В поле В последнюю очередь по (третий ключ сортировки) выбрать «Вид оплаты (нал./безнал.)» и установить флажок По возрастанию. Второй ключ используется, если обнаруживаются повторения в первом, а третий – если повторяется значение и в первом, и во втором ключе.

· Нажать ОК.

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

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

 

Промежуточные итоги

 

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

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

 

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

· Установить курсор в любую ячейку таблицы с данными.

· Выбрать пункт меню Данные|Итоги.

· В окне При каждом изменении в выбрать «Наименование магазина».

· В окне Операция выбрать Сумма.

· В окне Добавить итоги по выбрать Сумма (проверить, чтобыв остальных полях флажки отсутствовали).

· Включить флажок Итоги под данными. Если флажок этого параметра установлен, то итоги размещаются под данными, если сброшен – над ними.

· Нажать ОК, снять выделение с таблицы и просмотреть результат на экране.

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

· Установить курсор в любую ячейку таблицы с данными.

· Выбрать пункт меню Данные|Итоги.

· В поле При каждом изменении в выбрать «Вид продукции».

· В поле Операция выбрать Сумма.

· В поле Добавить итоги по выбрать Сумма (проверить, чтобы ничего другого выбрано не было).

· Обратить внимание, что в поле Заменить текущие итоги флажок должен отсутствовать. Нажать ОК.

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

· Убрать полученные итоги, предварительно установив курсор в таблицу и выполнив команду Данные|Итоги|Убрать все.

 

Обеспечение поиска и фильтрации данных

 

Наиболее часто используемыми операциями над списками (базами данных) в Excel являются поиск и фильтрация данных.

Отфильтровать список - значит скрыть все строки, за исключением тех, которые удовлетворяют заданным условиям отбора. Excel предоставляет две команды: Автофильтр -для простых условий отбора и Расширенный фильтр - для более сложных критериев.

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

 

Применение Автофильтра

 

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

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

 

Необходимо определить, какие товары были проданы ООО «Техносервис» магазину «Техносила» по безналичному расчету, для этого:

· Установить курсор в любую ячейку таблицы.

· Выбрать пункт меню Данные.

· Выбрать команду Фильтр, а затем Автофильтр.

· Выбрать в раскрывающемся списке рядом с заголовком «Наименование магазина» - Техносила.

· Выбрать в раскрывающемся списке рядом с заголовком «Вид оплаты» - Безнал.

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

 

Удаление Автофильтра

 

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

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

 

Применение Автофильтра к нескольким столбцам с заданием условий

 

Необходимо выбрать товары, реализованные за наличный расчет на сумму от 100000 у.е. и выше, для этого:

· Удалить результаты предыдущего автофильтра.

· Установить курсор в таблицу.

· Выбрать Данные|Фильтр|Автофильтр.

· Выбрать в раскрывающемся списке рядом с заголовком «Вид оплаты…» - Нал.

· Выбрать в раскрывающемся списке рядом с заголовком «Сумма» - Условие.

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

· В соседнем поле ввести с клавиатуры 100000.

· Щелкнуть кнопкой ОК.

· Просмотреть результат на экране и убрать автофильтр.

Примечание: с помощью пользовательского автофильтра, выбрав пункт Условие, можно создать специальный автофильтр с более гибкими возможностями. Например, выбрать товары, проданные за наличный расчет по цене менее 100 у.е. или более 500 у.е., для чего задать два условия отбора, соединенные логическим оператором ИЛИ.

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