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

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

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

· Скопируйте содержимое листа Исходный список на новый лист. Назовите лист Сортировка1.

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

· На вкладке Данныенажмите кнопку Сортировка

· В окне сортировка в группе Столбец в поле Сортировать повыберите Наименование поставщика.

· В группе Сортировкавыберите Значения.

· В группе Порядоквыберите От А до Я.

· Нажмите кнопку Добавить Уровень. Появится строка Затем по.

· В группе Столбец в поле Сортировать повыберите Дата поставки.

· В группе Сортировкав поле выберите Значения.

· В группе Порядоквыберите От старых к новым.

· Нажмите кнопку Добавить Уровень. Появится строка Затем по.

· В группе Столбец в поле Сортировать повыберите Сумма заказа.

· В группе Сортировкав поле выберите Значения.

· В группе Порядоквыберите По возрастанию.

· Нажмите ОК.

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

Для сортировки в определенном пользовательском порядке можно использовать списки, которые созданы только на основе значений (текстовых, числовых или значений дат и времени). Создать пользовательский список на основе данных форматирования (цвета ячейки, шрифта или значков) невозможно.

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

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

Упражнение:выполните сортировку данных таблицы по столбцу Наименование товарав соответствии со следующим списком товаров – шкаф, стол, тумба, стул. Для этого:

· Скопируйте содержимое листа Исходный список на новый лист. Назовите лист Сортировка2.

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

· На вкладке Главнаяв группе Редактированиераскройте кнопку Сортировка и фильтр.

· В открывшемся окне выберите команду Настраиваемая сортировка.

· В окне сортировка в группе Столбец в поле Сортировать повыберите Наименование товара.

· В группе Сортировкавыберите Значения.

· В группе Порядок выберите Настраиваемый список.

· В окне Списки в группе Списки выберите Новый список.

· В группе Элементы списка введите список шкаф, стол, тумба, стул.

· Нажмите ОК в окне Списки.

· Нажмите ОК в окне Сортировка.

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

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

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

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

Упражнение: в качестве промежуточных итогов выведите общую сумму заказа по каждому наименованию поставщика. Для этого:

· Скопируйте содержимое листа Исходный список на новый лист. Назовите лист Итоги.

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

· На вкладке Данные нажмите кнопку Промежуточные итоги.

· В открывшемся окне в группе При каждом изменении в, нажав стрелку вниз, выберите Наименование поставщика.

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

· В группе Добавит итоги по: установите флажок в опцию Сумма заказа, а другие флажки удалите.

· Нажмите ОК.

Упражнение: в дополнение к существующим промежуточным итогам выведите среднюю сумму поставки по каждой дате. Для этого:

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

· На вкладке Данные нажмите кнопку Промежуточные итоги.

· В открывшемся окне в группе При каждом изменении в:, нажав стрелку вниз, выберите Дата поставки.

· В группе Операция выберите Среднее.

· В группе Добавит итоги по: установите флажок в опцию Сумма поставки, не удаляя другие флажки.

· Удалите флажок в опции Заменить текущие итоги.

· Нажмите ОК.

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

Структура данных рабочего листа

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

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

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

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

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

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

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

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

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

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

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

Упражнение: с помощью автофильтра выберите из списка данные о поставленных товарах поставщиком АО «Макс» в марте месяце на сумму менее 100000 руб. Для этого:

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

· Нажмите кнопку Фильтр на вкладке Данные.

· Нажмите кнопку со стрелкой в правом углу ячейки «Наименование поставщика».

· В раскрывшемся окне снимите флажок Выделить все,а затем установите флажок рядом с наименованием АО «Макс», нажмите ОК.

· Нажмите кнопку со стрелкой в ячейке Дата поставки и в раскрывшемся окне оставьте флажок рядом с наименованием месяца Март, нажмите ОК.

· Нажмите кнопку со стрелкой в ячейке Сумма поставки.

· В раскрывшемся окне выберите команду Числовые фильтры и в следующем окне выберите Меньше.

· В раскрывшемся окне Пользовательский автофильтр в области Сумма поставки установите меньше 100000, нажмите ОК.

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

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