Условия фильтрации по Критерию 3
| Тип | Оперативная память | Видеопамять |
| Athlon | >1 | >512 |
Методические указания. Для расчета средней цены компьютеров воспользуйтесь функцией ДСРЗНАЧ. Для использования этой функции надо предварительно построить область критериев с заголовком Видеопамять и значением 512. Если область критериев размещается по адресу I3:I4 и ей присвоено имя блока Критерий, а фрагмент исходного списка занимает ячейки блока A1:G15, которому присвоено имя База данных, то аргументы функции ДСРЗНАЧ должны быть заданы следующим образом: ДСРЗНАЧ(База данных; G1; Критерий). При задании аргументов через координаты ячеек обращение к функции получает менее удобный вид: ДСРЗНАЧ(A1:G15; G1; I3:I4).
Для расчета максимальной цены компьютера используйте функцию ДМАКС, предварительно разместив в ячейке I8 заголовок области критериев Тип процессора, а в ячейке I9 – значение Core 2 Duo. Если блоку ячеек I8:I9 присвоить имя Критерий 2, а блоку ячеек исходного списка A1:G15 присвоить имя БД, то обращение к функции ДМАКС получит следующий вид: ДМАКС(БД; G1; Критерий).
Для определения цены компьютера с характеристиками, указанными в Критерии 3, используйте функцию БИЗВЛЕЧЬ, которая примет вид БИЗВЛЕЧЬ (БД; G1; Критерий 3).
Решение заданий в режиме формул представлены на рис. 2.1 и 2.2.
Рис. 2.1. Использование функции СЧЕТЕСЛИ
Рис. 2.2. Использование функций ДСРЗНАЧ, ДМАКС, БИЗВЛЕЧЬ
Фильтрация списков. Структурирование и группировка данных для формирования итогов
Цель: приобрести навыки работы с фильтрами.
Для выполнения заданий используйте таблицу с исходными данными из п. 2.1.
Задание 1. Применить автофильтр к приведенному списку. Для этого использовать команды Данные→Фильтр→Автофильтр.Из раскрывающегося списка для поля Видеопамять выбрать значение 512 для сравнения.
Задание 2. Для создания сложного условия для значений элементов одного из столбцов в команде Автофильтрвыбирается:
· строка(Условие…) по которой открывается диалоговое окно Пользовательский автофильтр. С использованием указанного средства Excel выбрать из списка компьютеры, цена которых находится в диапазоне (520–700$);
· строка (Первые 10…), вызывающая окно Наложение условия по списку.
Выбрать из списка 40 %наиболее дорогих компьютеров.
Задание 3. Применить к вышеприведенному списку средство Excel Расширенный фильтр.Для этого сформировать диапазон условий фильтрации (табл. 2.3), размещенных на одном листе с исходным списком.
Таблица 2.3
Условия фильтрации
| Тип процессора | Частота процессора | Оперативная память | Жесткий диск | Видеопамять | Примечание | Цена, USD |
| >250 | <700 |
После ввода условий в область критериев маркер выделения следует поместить в одну из ячеек списка и вызвать окно Расширенный фильтр. В поле Исходный диапазон автоматически отобразится диапазон ячеек исходного списка. В поле Диапазон условий надо ввести ссылку на ячейки области критериев. Это выделение можно сделать с помощью мыши. Щелчок на кнопке ОК скроет в исходном списке строки, не удовлетворяющие условиям критерия. Скопируйте результат фильтрации списка в другую область рабочего листа.
Вынесите результат фильтрации фрагмента прайс-листа компьютерных фирм с использованием команды Расширенный фильтр на отдельный рабочий лист Excel, на котором расположен диапазон условий, приведенный в табл. 2.4.
Выполните фильтрацию, результат сохраните.
Таблица 2.4
Условия фильтрации
| Тип процессора | Частота процессора | Оперативная память | Жесткий диск | Видео память | Примечание | Цена (USD) |
| <512 | <500 |
Задание 4. Вычислить среднее значение цены компьютеров, затем отфильтровать компьютеры с ценой выше средней.
Вычислите сначала среднее значение цены в отдельной ячейке. Для этого введите в нее формулу вычисления среднего значения цены по столбцу, в котором она находится, (G): =СРЗНАЧ(G2:G15). Если столбцу цен (G2:G15) присвоить имя блока Цена, то формула вычисляемого критерия примет вид =СРЗНАЧ(Цена). Использование в формулах имен блоков вместо координат ячеек облегчает их применение (Сервис→Параметры→Вычисления→Флажок «Допускать название диапазонов»).
Теперь задайте вычисляемый критерий. Для этого в ячейку введите заголовок Цена выше средней, а в ячейку ниже введите формулу =G1>$H$2, где G1 – это первая ячейка столбца G, содержащего цены компьютеров, а H2–вычисленная средняя цена. Формула позволяет отобрать только те строки списка, в которых значение цены исходного списка больше средней цены в прайс-листе. В диалоговом окне Расширенный фильтр в поле Диапазон условий введите ссылку на ячейки критерия средней цены, содержащие критерий отбора записей списка. После выполнения команды Расширенный фильтр список будет содержать строки с ценой выше средней.
Результаты и порядок выполнения заданий отражены на рис. 2.3–2.6.
Рис. 2.3. Результат фильтрации по критерию Видеопамять 512
Рис. 2.4. Использование условия в Автофильтре
Рис. 2.5. Использование условий для фильтрации
Рис. 2.6. Результат фильтрации по дополнительному критерию
Задание для самостоятельной работы. Определить для прайс-листа компьютерных фирм средние цены компьютеров в группах с одинаковыми объемами Видеопамяти с помощью команды Итоги. После выделения списка и запуска команды Итоги следует заполнить диалоговое окно Промежуточные итоги следующим образом: в поле При каждом изменении в – Видеопамяти;в поле Операция – Среднее; в поле Добавить итоги по – установить флажок напротив Цена (USD). Установить флажки напротив Заменить текущие итоги и Итоги под данными. Щелчок по кнопке ОК вставляет итоговые строки и структурирует список.
Рекомендуемая литература: [1, 3, 5, 10, 15, 17, 19].
Лабораторная работа № 3
Методы обработки и анализа экономической
информации в Excel. Бизнес-планирование
Сортировка списков
Цель: приобрести навыки сортировки данных по нескольким ключам и направлениям.
Задание 1. выполнить сортировку списка с помощью команды Сортировка, которая входит в меню Данные по двум ключам: сначала по первому ключу (по возрастанию Числа дней, на которые выдан кредит); затем по второму ключу (по возрастанию Годовой ставки процента). Исходные данные приведены в табл. 3.1.
Таблица 3.1
Исходные данные
| Кредитор | Месяц выдачи кредита | Сумма кредита, тыс. руб. | Годовая ставка процента, % | Число дней, на которые выдан кредит |
| Ветров А.Р. | Январь | 400 000 | ||
| Захаров Л.Д. | Март | 78 000 | ||
| Иванов В.А. | Февраль | 10 000 | ||
| Каримов Р.А. | Сентябрь | 80 000 | ||
| Морозов К.Н. | Май | 65 000 | 29,50 | |
| Петров А.А. | Январь | 50 000 | ||
| Сидоров П.Р. | Июль | 350 000 | 28,50 | |
| Черкасов К.Г. | Май | 99 000 | ||
| Мельников С.Р. | Январь | 54 000 | ||
| Алехина В.П. | Апрель | 43 000 | 24,50 | |
| Зубова Р.Л | Октябрь | 75 000 | ||
| Громов А.Н. | Январь | 61 000 | ||
| Уткин В.С. | Декабрь | 70 000 | 29,50 | |
| Конев А.В. | Июнь | 25 000 | 30,50 | |
| Пронин Т.А. | Октябрь | |||
| Миронов К.А. | Январь | 78 000 | 26,50 | |
| Самойлов Р.Д. | Май | 75 000 | ||
| Забелина С.А. | Август | 99 000 | 24,5 | |
| Быков А.М. | Июнь | 65 000 | ||
| Бобылев Е.А. | Октябрь | 47 000 | 28,50 | |
| Невров А.М. | Апрель | 54 000 | 26,50 | |
| Сотников В.Д. | Декабрь | 12 000 | ||
| ИТОГО: | – | =SUM(ABOVE) 1 899 000 | – | – |
Задание 2. Выполнить сортировку списка, задав пользовательский порядок сортировки по месяцам выдачи кредита и по сумме кредита по возрастанию. Для этого использовать кнопку Параметры… в диалоговом окне Сортировка.
Задание 3. Произвести сортировку по убыванию суммы кредита, используя кнопку на панели инструментов Стандартная.
Каждое задание выполнить на отдельном листе одной рабочей книги. Присвоить листам имена: ИСХ. ДАННЫЕ; Решение1; Решение2; Решение3 соответственно.
Решение задач бизнес-планирования средствами аппарата сводных таблиц
Цель: приобрести навыки в использовании аппарата сводных таблиц в задачах бизнес-анализа.
Задание 1. создать на основе исходных данных ведомость зарплаты работников за полугодие (табл. 3.2).
Таблица 3.2
Исходные данные
| Ф.И.О. | Всего начислено | Всего удержано | Сумма к выдаче | Отдел | Дата |
| Ведомость зарплаты сотрудников за 1-й квартал | |||||
| Иванов И.А. | 487,80 | 4012,20 | 1-й кв. 1998 | ||
| Малаев В.П. | 441,12 | 3458,88 | 1-й кв. 1998 | ||
| Федоров Е.Н. | 505,52 | 3894,48 | 1-й кв. 1998 | ||
| Климов Ф.Ф. | 484,60 | 3515,40 | 1-й кв. 1998 | ||
| Осипов Ю.Я. | 537,72 | 4112,28 | 1-й кв. 1998 | ||
| Ведомость зарплаты сотрудников за 2-й квартал | |||||
| Иванов И.А. | 899,96 | 6800,04 | 2-й кв. 1998 | ||
| Малаев В.П. | 724,48 | 5375,52 | 2-й кв. 1998 | ||
| Федоров Е.Н. | 685,84 | 5114,16 | 2-й кв. 1998 | ||
| Климов Ф.Ф. | 587,64 | 4212,36 | 2-й кв. 1998 | ||
| Осипов Ю.Я. | 660,08 | 4939,92 | 2-й кв. 1998 |
В колонке Сумма к выдаче в исходной таблице необходимо ввести формулу для расчета заработной платы.
Методика выполнения
1. Необходимо разместить исходные ведомости на одном рабочем листе Excel (табл. 3.3).
Таблица 3.3