Применение автофильтра в MS EXCEL. Выставка собак.

Сортировка

Создайте лист Сортировка

Мы хотим отсортировать щенков по стоимости, чтобы узнать, щенки какой породы самые дорогие, а какой – самые дешевые.
Для этого надо выделить все данные (НЕ ЗАТРАГИВАЯ заголовки столбцов!) и в меню Данные выбрать пункт Сортировка.
В появившемся диалоговом окне вы указываете, по какому столбцу следует отсортировать значения. Можно также отсортировать по нескольким значениям, например сначала по породам, а потом (внутри каждой породы) – по дате рождения.

Задание 1: Отсортируйте щенков по стоимости.

Фильтр

Создайте лист Автофильтр

Более удобным инструментом для отбора и сортировки данных является Автофильтр. С помощью него вы можете не только сортировать данные, но и делать выборки.
Для этого нужно выделить все данные ВМЕСТЕ с заголовками столбцов и в меню Данные выбрать пункт Фильтр, а в нем подпункт Автофильтр.
Ячейки в строке заголовков у вас станут не обычными, а с кнопочками выпадающих списков. В этих выпадающих списках можно выбрать разные условия сортировки или отбора.

Задание 2а: выберите всех далматинов.

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

Можно задавать более сложные условия отбора, например, отобрать всех сеттеров. В выставке участвуют английские и ирландские сеттеры. Значит, нам нужно отобрать всех собак, в названии породы которых СОДЕРЖИТСЯ слово «сеттер».

Шаг 1:

Шаг 2:

Задание 2б: выберите всех собак, относящихся к группе сеттеров.

Итоги

Создайте лист Итоги

Теперь нам интересно узнать, сколько представителей разных пород приехало на выставку, и какова средняя стоимость щенка каждой породы. 
Для всех этих действий, при которых мы сначала объединяем щенков в группы (по породам), а потом в КАЖДОЙ из них находим либо количество, либо среднее значение, либо другой параметр, нам понадобится такая операция Excel как подведение итогов.

Подведение итогов выполняется в три шага.

1. ОБЯЗАТЕЛЬНО нужно отсортировать щенков ПО ТОМУ ПРИЗНАКУ, по которому мы хотим объединять их в группы (с помощью Сортировки). В данном случае их нужно отсортировать по породе.
2. Выделяете все данные ВМЕСТЕ с заголовками столбцов и в меню Данные выбираете пункт Итоги, у вас открывается диалоговое окно Промежуточные итоги.

3. В диалоговом окне вы указываете:

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

Тогда

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

Задание 3: Сосчитайте с помощью Итогов количество щенков каждой породы.

Диаграмма

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

Шаг 1. Подготовка данных

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

стало:

Итак, мы свернули всю таблицу. Теперь переходим к следующему шагу.

Шаг 2. Вставка диаграммы. 

Так же как и в Word, вставка диаграммы в Excel осуществляется через меню Вставка (пункт Диаграмма). В открывшемся диалоговом окне вам предложат выбрать тип диаграммы. Для разных задач используются разные диаграммы. В нашем случае лучше всего подойдет круговая: она отображает долю разных значений в общей сумме.

После того, как вы выберете тип диаграммы и нажмете ОК, запустится Мастер диаграмм, который поможет ввести данные и настроить параметры диаграммы.

Этап ввода данных самый ответственный! Небольшая неточность может привести к тому, что диаграмма будет отображаться некорректно.
1.В поле Диапазон на вкладке Диапазон данных вы указываете ячейки с данными (цифры + подписи) Их можно просто выделить мышью на рабочем поле, и они автоматически занесутся в ячейку.

2. Теперь будьте внимательны! На следующей вкладке Ряды надо заполнить три поля: 1) в поле Имя вы говорите, как будет называться диаграмма; 2) в поле Значения вы вставляете ячейки (выделяя их мышью на рабочем листе) с ЧИСЛОВЫМИ ЗНАЧЕНИЯМИ, по которым рисуется диаграмма; 3) наконец, в поле Подписи категорий вы указываете ячейки с подписями, которые пойдут в легенду диаграммы.

Завершите вставку диаграммы. Разместите ее на том же листе Итоги.

 

---

 

Задания. Здесь вы можете скачать Excel-файл и исходными данными. Для каждого задания, необходимо создать отдельный лист. 

 

1. Определить перечень Товаров с ценой >=2000 до <=9000 и которые проданы в марте и феврале 2012 года. Отсортировать полученные данные по Продавцу (от А до Z).

2. Определить партии Товаров, реализация которых превысила 600 единиц. Отсортировать полученные данные по возрастанию цены Товара.

3. Определить Покупателей, продающих Цифровые фото и видеокамеры. Отсортировать эти данные по Товару.

4. Определить Покупателей, продающих Товары, в названии которых содержится слово Камера. Отсортировать эти данные по Товару. Убедиться, что результат аналогичен результату в задании №3.

5. Определить 5 самых дорогих Товаров.

6. Определить самые дешевые Товары: отсортировать по возрастанию цены, затем отобрать самые дешевые (10% от общего количества Товаров).
 

Сохранить файл в Общую папку/Класс/Excel/Фамилия.