Отбор данных при помощи фильтров
Лабораторная работа №4
Анализ табличных данных средствами MS Excel
Цель работы: Научиться приемам работы, связанным с решением задач экономического анализа данных; самостоятельной постановке (формулировке) таких задач.
Изучаемые понятия: список, поля и записи списка; сортировка и отбор (фильтрация) данных, подведение итогов.
Используемые средства Excel: Команды и мастерá меню Данные: Сортировка, Фильтр, Итоги, Сводная таблица.
Внимание! Отчет по данной лабораторной работе оформить в Word, поместив в него результат по каждому заданию с кратким описанием его выполнения.
Исходные данные для решения поставленных задач находятся на листе ВсеМодели книги ЛР_13(Анализ_Данных).xls.
Определение экстремальных (минимальных и максимальных) значений в списках при помощи сортировки и автофильтра
Подсказка: Для решения поставленных задач целесообразно совместно использовать автофильтр и сортировку.
Пример содержания и оформления отчета
Например, необходимо решить такую задачу: найти, у кого из продавцов модель Sony Ericsson K850i продается по самой низкой цене.
Решение: Сначала при помощи автофильтра отобразить в таблице данные только этой модели (Sony Ericsson K850i), а затем отсортировать отфильтрованные данные по возрастанию по полю Цена, грн. (или Цена, $)
Ответ: Дешевле всего, по цене 565 $ (или 2881,50 грн.) телефон модели Sony Ericsson K850i можно купить в магазине «Topmobila» (поле Продавец).
Задания
Найти ответы на поставленные задачи в соответствии со своим вариантом в таблице 1.
Во всех задачах также указывать цену телефона. Задачу 3 каждого варианта решить двумя способами: 1) комбинируя фильтрацию и сортировку; 2) используя только многоуровневую сортировку.
Таблица 1
| № Варианта | № Задачи | Задание |
| у кого из продавцов Nokia N73 Music Edition продается по самой высокой цене | ||
| самый дорогой телефон (модель, продавец) производимый Францией | ||
| продавца самого дорогого телефона модели Nokia N73 Music Edition, производимого Венгрией | ||
| у кого из продавцов медель AppleiPhone8Gb продается по самой высокой цене | ||
| самый дешевый телефон (модель, продавец) производимый Германией | ||
| продавца самого дорогого телефона модели AppleiPhone8Gb, производимого Францией | ||
| у кого из продавцов Nokia N95 продается по самой высокой цене | ||
| самый дорогой телефон (модель, продавец) производимый Финляндией | ||
| продавца самого дешевого телефона модели Nokia N95, производимого Францией | ||
| у кого из продавцов Nokia-6300 продается по самой высокой цене | ||
| самый дорогой телефон (модель, продавец) производимый Венгрией | ||
| продавца самого дорогого телефона модели Nokia-6300, производимого Францией | ||
| у кого из продавцов Sony Ericsson K850i продается по самой высокой цене | ||
| самый дешевый телефон (модель, продавец) производимый Францией | ||
| продавца самого дорогого телефона модели Sony Ericsson K850i, производимого Францией | ||
| у кого из продавцов Nokia N73 Music Edition продается по самой низкой цене | ||
| самый дешевый телефон (модель, продавец) производимый Германией | ||
| продавца самого дорогого телефона модели Nokia N73 Music Edition, производимого Германией | ||
| у кого из продавцов модель AppleiPhone8Gb продается по самой низкой цене | ||
| самый дешевый телефон (модель, продавец), производимый Финляндией | ||
| продавца самого дешевого телефона модели Nokia N95, производимого Германией | ||
| у кого из продавцов Nokia N95 продается по самой низкой цене | ||
| самый дешевый телефон (модель, продавец) производимый Венгрией | ||
| срок гарантии и продавца самого дорогого телефона модели Nokia-6300, производимого Финляндией | ||
| у кого из продавцов Nokia-6300 продается по самой низкой цене | ||
| минимальный срок гарантии на телефоны модели Sony Ericsson K850i, производства Финляндии | ||
| продавца самого дорогого телефона модели Nokia-6300, производимого Германией | ||
| у кого из продавцов Sony Ericsson K850i продается по самой низкой цене | ||
| страну-производителя телефона модели Nokia-6300, для которогоустановлен наименьший срок гарантии | ||
| продавца самого дорогого телефона модели Sony Ericsson K850i, производимого Германией |
Задание 4 (общее). Создать собственный параметр сортировки, например, по стране-производителю в таком порядке: Франция, Германия, Финляндия, Венгрия. Для этого сначала создать список с указанной последовательностью значений, в окне Сортировка – Порядок – Настраиваемый список(рис. 1) Элементы списка можно добавить вручную с клавиатуры (в поле Элементы списка). После этого (ввода или импорта элементов списка) нажать кнопку <Добавить> для добавления нового списка к уже имеющимся.
Рис. 1 Создание пользовательского списка
Отсортировать список по своему параметру. Для этого:
® вернуться в таблицу с исходными данными;
® выполнить команду Данные®Сортировка…;
® в списке Сортировать по выбрать Производитель и нажать кнопку <Порядок> (рис.2);
® в списке Настраиваемый список выбрать собственный список и <ОК> (рис.2);
® вернувшись в окно Сортировка диапазона нажать <ОК>.
 |
Рис. 2 Сортировка по параметру пользователя
Отбор данных при помощи фильтров
Задачи 1 и 2 решить двумя способами: при помощи автофильтра и расширенного фильтра (данные расширенным фильтром фильтровать на месте). Задачу 3 решить при помощи расширенного фильтра (предварительно создать диапазон условий, затем Фильтр – Дополнительно).
Найти ответы на поставленные задачи в соответствии со своим вариантом в таблице 2.
Таблица 2
| № Варианта | № Задачи | Задание |
| Название продавца содержит сочетание символов «com» | ||
| Название модели содержит «5», а цена не более 3500 грн. | ||
| Гарантия на телефон не менее полугода а также все телефоныпроизводства во Франции | ||
| Название продавца заканчивается на «ua» | ||
| Название модели содержит «Nokia», а цена лежит в пределах от 1000 до 2000 грн. | ||
| Гарантия на телефон менее полугода и все такие, которыепроизведены в Венгрии | ||
| Название продавца заканчивается на «market» | ||
| Название модели не содержит «Nokia», а цена лежит в пределах от 2000 до 4000 грн | ||
| Любые финские телефоны и все такие, у которых цена не менее 900 $ | ||
| Название продавца не заканчивается на «ua» | ||
| Цена модели лежит в пределах от 3000 до 5000 грн, а производитель начинается на «Ф» | ||
| Все телефоны, предлагаемые продавцом koala.com.ua и все телефоны Nokia | ||
| Название продавца содержит «mobil» | ||
| Цена модели больше 4000 грн, а производитель начинается на «Ф» | ||
| Все телефоны, в названии продавца которых содержится «market» и все телефоны с гарантией 6 или 7 месяцев | ||
| Название продавца начинается с «mobil» | ||
| Цена модели не превышает 2000 грн, а название продавца содержит «ua» | ||
| Все телефоны, цена которых в грн. не менее 4500, и все телефоны, цена которых в $ не более 250 | ||
| Название продавца не заканчивается на «a» (англ.) | ||
| Цена модели лежит в пределах от 600 до 1000 $, а название продавца содержит «mobil» | ||
| Название продавца содержит «tele» и все телефоны с гарантией 3 месяца | ||
| Название продавца содержит «net» | ||
| Цена модели лежит в пределах от 500 до 700 $, а страна производитель Финляндия или Франция | ||
| Все телефоны с гарантией не более полугода и все телефоны, цена которых в $ не более 250 | ||
| Название продавца начинается со «sk» | ||
| Цена модели не ниже 600$, а страна производитель Германия или Венгрия | ||
| Все телефоны, цена которых в $ не менее 1000, и все телефоны, цена которых в грн. от 1300 до 1400 | ||
| Название продавца содержит «market» | ||
| Гарантия на телефон не более полугода, а цена модели от 1000 $ | ||
| Название продавца заканчивается на «net» и все телефоны с гарантией не более 1 месяца |
Задание 4 (общее).. * При помощи расширенного фильтра найти все телефоны с ценой в полтора раза выше средней с использованием функции СРЗНАЧ.