Условия фильтрации по Критерию 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.

Условия фильтрации по Критерию 3 - №1 - открытая онлайн библиотека

Рис. 2.1. Использование функции СЧЕТЕСЛИ

Условия фильтрации по Критерию 3 - №2 - открытая онлайн библиотека

Рис. 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.

Условия фильтрации по Критерию 3 - №3 - открытая онлайн библиотека

Рис. 2.3. Результат фильтрации по критерию Видеопамять 512

Условия фильтрации по Критерию 3 - №4 - открытая онлайн библиотека

Условия фильтрации по Критерию 3 - №5 - открытая онлайн библиотека

Рис. 2.4. Использование условия в Автофильтре

Условия фильтрации по Критерию 3 - №6 - открытая онлайн библиотека

Условия фильтрации по Критерию 3 - №7 - открытая онлайн библиотека

Рис. 2.5. Использование условий для фильтрации

Условия фильтрации по Критерию 3 - №8 - открытая онлайн библиотека

Рис. 2.6. Результат фильтрации по дополнительному критерию

Задание для самостоятельной работы. Определить для прайс-листа компьютерных фирм средние цены компьютеров в группах с одинаковыми объемами Видеопамяти с помощью команды Итоги. После выделения списка и запуска команды Итоги следует заполнить диалоговое окно Промежуточные итоги следующим образом: в поле При каждом изменении вВидеопамяти;в поле ОперацияСреднее; в поле Добавить итоги по – установить флажок напротив Цена (USD). Установить флажки напротив Заменить текущие итоги и Итоги под данными. Щелчок по кнопке ОК вставляет итоговые строки и структурирует список.

Условия фильтрации по Критерию 3 - №9 - открытая онлайн библиотека Рекомендуемая литература: [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