Вычисляемый критерий
Вычисляемый критерий основывается на одном или нескольких вычислениях.
Задание 8
Необходимо определить машины, год выпуска и год приобретения которых совпадает, а также – вторая буква номера которых «С» или «Х».
Технология выполнения:
Создать копию листа « Исходные данные». Переименовать копию в лист «2».
Ниже таблицы данные в ячейку А38 ввести надпись: Критерий.
Далее воспользуемся функцией И и запишем два раза один и тот же критерий. В ячейку В38 копируем название поля «буквы номера». После вводим выражение для вычисляемого критерия в ячейки А39 и А40: =E2=F2. С целью фильтра машин по номеру в ячейку В39 вводим: ?с* (? – один символ; * - множество символов), а в ячейку В40: ?х*
В ячейках А39 и А40 должно появиться значение «ЛОЖЬ».
На вкладке «Данные» выбрать команду «Фильтр - Дополнительно».
В появившемся диалоговом окне ввести следующие значения:
Обработка: скопировать результат в другое место.
Исходный диапазон (если не установился автоматически): $A$1:$J$33.
Диапазон условий: $A$38:$B$40.
Поместить результат в диапазон: $A$45:$J$45.
ОК. Требуемый список получен.
Рис. 9. Диалоговое окно. Команда Расширенный фильтр
См. приложение 5.
Задание 9
Определить машины, цена которых не превосходит средней цены для машин марки «Марка2» (Toyota).
Технология выполнения:
Создать копию листа « Исходные данные». Переименовать копию в лист «IV.8».
Ниже таблицы данные в ячейку A42 ввести текст: «средняя цена для машин марки "Марка 2":».
В ячейку E42 ввести формулу: =СРЗНАЧЕСЛИ(B2:B33;"Toyota";I2:I33). После вычисления данной функции в ячейке появится среднее значение цены для марки машин «Марка2» (оно равно 243166,7).
В ячейке А45 указываем критерий отбора: =I2<=$E$42. В ячейке должно появиться значение ЛОЖЬ.
Выбрать столбец с алфавитно-цифровыми данными в диапазоне ячеек или убедиться, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные.
На вкладке «Данные» выбрать команду «Фильтр - Дополнительно».
В появившемся диалоговом окне ввести следующие значения.
Обработка: скопировать результат в другое место
Исходный диапазон (если не установился автоматически): $A$1:$J$33
Диапазон условий: $A$44:$A$45
Поместить результат в диапазон: $A$48:$J$48
ОК. Требуемый список получен.
Рис. 10. Вычисляемый критерий (для задания 9)
См. приложение 6.
Анализ данных Сводная таблица
Сводная таблица - специальный тип таблицы, суммирующий информацию из конкретных полей списка или базы данных, допускающая после построения настройку на различные разрезы данных.
Сводная таблица используется для формирования отчетов по нескольким критериям исходных данных.
Сводная таблица обобщает сведения в полях базы данных в их связи друг с другом и позволяет получить быстрый ответ на многие вопросы.
Задание 10
Необходимо определить количество студентов каждого курса и факультета, которые сдавали спортивные нормативы.
Для каждой марки автомобиля определить среднюю цену, максимальный пробег, количество машин каждой марки.
Технология выполнения:
Сделать активной ячейку в диапазоне исходных данных.
На вкладке «Вставка» выбрать команду «Сводная таблица».
В появившемся диалоговом окне ввести следующие параметры:
Таблица или диапазон (если не установлено автоматически): 'Исходные данные'!$A$1:$J$33.
Поместить отчет сводной таблицы на новый лист (лист «IV.10»).
ОК.
Рис. 11. Создание сводной таблицы
Выбрать функцию: «Параметры сводной таблицы».
В появившемся диалоговом окне на вкладке «Вывод» активизировать параметр: «Классический макет сводной таблицы».
Рис. 12. Параметры сводной таблицы
Рис. 13. Сводная таблица
Заполнить макет сводной таблицы:
Поле строк: Марка машины.
Поле столбцов: Значения
Значения: Среднее по полю цена; Максимум по полю пробег; Количество по полю марка машины. Нужные по полю для каждой марки машины значения получены.
См. приложение 7.
Также возможны другие варианты выполнений данного задания с помощью сводной таблицы.
Задание 12
Определить суммарный и средний пробег определенной марки машины с учетом конкретного года выпуска.
Сделать активной ячейку в диапазоне исходных данных.
На вкладке «Вставка» выбрать команду «Сводная таблица».
В появившемся диалоговом окне ввести следующие параметры:
Таблица или диапазон (если не установлено автоматически): 'Исходные данные'!$A$1:$J$33.
Поместить отчет сводной таблицы на новый лист (лист «IV.12»).
ОК
Выбрать функцию: «Параметры сводной таблицы».
В появившемся диалоговом окне на вкладке «Вывод» активизировать параметр: «Классический макет сводной таблицы».
Заполнить макет сводной таблицы:
Фильтр отчета: Марка машины; Год выпуска.
Поле столбцов: Значения.
Значения: Сумма по полю Пробег; Среднее по полю пробег. Нужные по полю для каждой марки машины значения с учетом года выпуска получены.