Задание 3
Необходимо определить автомобили цвета «Цвет 1» (белый) и цвета «Цвет 2»(черный).
Технология выполнения:
1. Создать копию листа «Исходные данные». Переименовать копию листа в «IV.3».
2. Выбрать диапазон ячеек, содержащих нужные буквенно-цифровые данные: столбец «Цвет машины».
3. На вкладке «Данные» выбрать команду «Фильтр».
4. Выбрать элемент управления Поле со списком 
в поле «Цвет машины». В раскрывающемся списке в поле «Цвет машины» активизировать значения «белый» и «черный». Нажать клавишу ОК. Требуемый список получен.
Рис. 6. Задание критериев – точное соответствие
Задание критериев на основе сравнения
Задание 2
Необходимо определить автомобили марки «Марка1» (Hyundai).
Технология выполнения:
1. Создать копию листа «Исходные данные». Переименовать копию в лист «IV.2 (2)».
2. Выбрать диапазон ячеек, содержащих нужные буквенно-цифровые данные: столбец «Марка машины»
3. На вкладке «Данные» выбрать команду «Фильтр».
4. Щелкнуть стрелку рядом с заголовком столбца «марка машины». В раскрывающемся списке в поле «марка машины» выбрать следующую последовательность команд: «Текстовые фильтры» - «начинается с…» (Также можно использовать команды: «Текстовые фильтры» - «равно», но уже придется писать полное название автомобиля марки «Марка1» (Hyundai)).
5. В появившемся диалоговом окне ввести нужный критерий: «h» (Задать можно и больше первых букв название марки, но, так как на букву H начинается только одна марка машины, нам достаточно одной первой буквы)
6. Нажать клавишу ОК. Требуемый список получен.
Рис. 7. Задание критериев – на основе сравнения
Расширенный фильтр
Расширенный фильтр позволяет произвести фильтрацию при более сложных условиях отбора для одного или нескольких полей. Для использования расширенного фильтра всегда необходимо задать диапазон критериев.
Диапазон критериев это специально отведенная область рабочего листа.
Задание 6
Определить автомобили марки «Марка 1» (Hyundai), пробег которых не превышает 80 000 км, отсортировать полученные данные по возрастанию пробега автомобилей.
Задание также можно выполнять с помощью автофильтра, так как критерии отбора соединены операцией И.
Технология выполнения:
1. Создать копию листа «Исходные данные». Переименовать копию в лист «IV.6».
2. Создать диапазон критериев. Ниже таблицы данные в ячейку B42 скопировать название столбца «марка машины», в ячейку B43 ввести значение: Hyundai.
3. Аналогично, в ячейку В42 скопировать название «Пробег», в ячейку В43 ввести значение: <80000. Условия отбора находятся на одной строке, значит, они соединены логической операцией: И.
4. Выбрать столбец с алфавитно-цифровыми данными в диапазоне ячеек или убедиться, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные.
5. На вкладке «Данные» выбрать команду «Фильтр - Дополнительно».
6. В появившемся диалоговом окне ввести следующие значения:
· Обработка: скопировать результат в другое место (чтобы были видны исходный и полученный результаты).
· Исходный диапазон (если не установился автоматически): $A$1:$J$33.
· Диапазон условий: $B$42:$C$43.
· Поместить результат в диапазон (можно указать как диапазон ячеек, так и одну ячейку): $A$48:$J$48.
· ОК. Требуемый список получен.
Рис. 8. Расширенный фильтр
· Выбрать столбец с алфавитно-цифровыми данными в диапазоне ячеек или убедиться, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные. Столбец «Курс». На вкладке «Данные» выбрать команду «Сортировка от А до Я (по возрастанию)». Список отсортирован в соответствии с заданием.
7. См. приложение 3.
Задание 4
Необходимо определить автомобили марки «Марка2» (Toyota) или цвета «Цвет2» (черный).
Задание необходимо выполнять с помощью расширенного фильтра, так как критерии отбора соединены операцией ИЛИ.
Технология выполнения:
1. Создать копию листа «Исходные данные». Переименовать копию в лист «IV.4».
2. Создать диапазон критериев. Ниже таблицы данные в ячейку B40 скопировать название столбца «марка машины», в ячейку В41 ввести значение: Toyota.
3. В ячейку С40 скопировать название «цвет машины», в ячейку С42 ввести значение: черный. Условия отбора находятся на разных строках, значит, они соединены логической операцией: ИЛИ.
4. Выбрать столбец с алфавитно-цифровыми данными в диапазоне ячеек или убедиться, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные.
5. На вкладке «Данные» выбрать команду «Фильтр - Дополнительно».
6. В появившемся диалоговом окне ввести следующие значения:
· Обработка: скопировать результат в другое место (чтобы были видны исходный и полученный результаты).
· Исходный диапазон (если не установился автоматически): $A$1:$J$33.
· Диапазон условий: $B$40:$C$42.
· Поместить результат в диапазон (можно указать как диапазон ячеек, так и одну ячейку): $A$46:$J$46.
· ОК. Требуемый список получен.
7. См. приложение 4.
8.
Вычисляемый критерий
Вычисляемый критерий основывается на одном или нескольких вычислениях.
Задание 8
Необходимо определить машины, год выпуска и год приобретения которых совпадает, а также – вторая буква номера которых «С» или «Х».
Технология выполнения:
1. Создать копию листа «Исходные данные». Переименовать копию в лист «2».
2. Ниже таблицы данные в ячейку А38 ввести надпись: Критерий.
3. Далее воспользуемся функцией И и запишем два раза один и тот же критерий. В ячейку В38 копируем название поля «буквы номера». После вводим выражение для вычисляемого критерия в ячейки А39 и А40: =E2=F2. С целью фильтра машин по номеру в ячейку В39 вводим:?с* (? – один символ; * - множество символов), а в ячейку В40:?х*
4. В ячейках А39 и А40 должно появиться значение «ЛОЖЬ».
5. На вкладке «Данные» выбрать команду «Фильтр - Дополнительно».
6. В появившемся диалоговом окне ввести следующие значения:
· Обработка: скопировать результат в другое место.
· Исходный диапазон (если не установился автоматически): $A$1:$J$33.
· Диапазон условий: $A$38:$B$40.
· Поместить результат в диапазон: $A$45:$J$45.
· ОК. Требуемый список получен.
Рис. 9. Диалоговое окно. Команда Расширенный фильтр
7. См. приложение 5.
Задание 9
Определить машины, цена которых не превосходит средней цены для машин марки «Марка2» (Toyota).
Технология выполнения:
1. Создать копию листа «Исходные данные». Переименовать копию в лист «IV.8».
2. Ниже таблицы данные в ячейку A42 ввести текст: «средняя цена для машин марки "Марка 2":».
3. В ячейку E42 ввести формулу: =СРЗНАЧЕСЛИ(B2:B33;"Toyota";I2:I33). После вычисления данной функции в ячейке появится среднее значение цены для марки машин «Марка2» (оно равно 243166,7).
4. В ячейке А45 указываем критерий отбора: =I2<=$E$42. В ячейке должно появиться значение ЛОЖЬ.
5. Выбрать столбец с алфавитно-цифровыми данными в диапазоне ячеек или убедиться, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные.
6. На вкладке «Данные» выбрать команду «Фильтр - Дополнительно».
7. В появившемся диалоговом окне ввести следующие значения.
· Обработка: скопировать результат в другое место
· Исходный диапазон (если не установился автоматически): $A$1:$J$33
· Диапазон условий: $A$44:$A$45
· Поместить результат в диапазон: $A$48:$J$48
· ОК. Требуемый список получен.
Рис. 10. Вычисляемый критерий (для задания 9)
8. См. приложение 6.
Анализ данных
Сводная таблица
Сводная таблица - специальный тип таблицы, суммирующий информацию из конкретных полей списка или базы данных, допускающая после построения настройку на различные разрезы данных.
Сводная таблица используется для формирования отчетов по нескольким критериям исходных данных.
Сводная таблица обобщает сведения в полях базы данных в их связи друг с другом и позволяет получить быстрый ответ на многие вопросы.
Задание 10
Необходимо определить количество студентов каждого курса и факультета, которые сдавали спортивные нормативы.
Для каждой марки автомобиля определить среднюю цену, максимальный пробег, количество машин каждой марки.
Технология выполнения:
1. Сделать активной ячейку в диапазоне исходных данных.
2. На вкладке «Вставка» выбрать команду «Сводная таблица».
3. В появившемся диалоговом окне ввести следующие параметры:
· Таблица или диапазон (если не установлено автоматически): 'Исходные данные'!$A$1:$J$33.
· Поместить отчет сводной таблицы на новый лист (лист «IV.10»).
· ОК.
Рис. 11. Создание сводной таблицы
4. Выбрать функцию: «Параметры сводной таблицы».
5. В появившемся диалоговом окне на вкладке «Вывод» активизировать параметр: «Классический макет сводной таблицы».
Рис. 12. Параметры сводной таблицы
Рис. 13. Сводная таблица
6. Заполнить макет сводной таблицы:
· Поле строк: Марка машины.
· Поле столбцов: Значения
· Значения: Среднее по полю цена; Максимум по полю пробег; Количество по полю марка машины. Нужные по полю для каждой марки машины значения получены.
7. См. приложение 7.
Также возможны другие варианты выполнений данного задания с помощью сводной таблицы.
Задание 12
Определить суммарный и средний пробег определенной марки машины с учетом конкретного года выпуска.
1. Сделать активной ячейку в диапазоне исходных данных.
2. На вкладке «Вставка» выбрать команду «Сводная таблица».
3. В появившемся диалоговом окне ввести следующие параметры:
· Таблица или диапазон (если не установлено автоматически): 'Исходные данные'!$A$1:$J$33.
· Поместить отчет сводной таблицы на новый лист (лист «IV.12»).
· ОК
4. Выбрать функцию: «Параметры сводной таблицы».
5. В появившемся диалоговом окне на вкладке «Вывод» активизировать параметр: «Классический макет сводной таблицы».
6. Заполнить макет сводной таблицы:
· Фильтр отчета: Марка машины; Год выпуска.
· Поле столбцов: Значения.
· Значения: Сумма по полю Пробег; Среднее по полю пробег. Нужные по полю для каждой марки машины значения с учетом года выпуска получены.
7. См. приложение 8
Также возможны другие варианты выполнений данного задания с помощью Сводной таблицы.
Промежуточные итоги
Подведение промежуточных итогов – это удобная и наглядная возможность провести обработку записей списка для последующего анализа. При этом в список автоматически вставляются дополнительные строки, отражающие итоговые значения по выбранным ячейкам.
При создании промежуточных итогов необходимо, чтобы в списке первая строка содержала название столбцов, а остальные строки — однотипные данные.
Задание 10
Для каждой марки автомобиля определить среднюю цену, максимальный пробег, количество машин каждой марки.
Технология выполнения:
1.1 Создать копию листа «Исходные данные». Переименовать копию в лист «IV.10(пром_ит)».
1.2 Выбрать столбец с алфавитно-цифровыми данными в диапазоне ячеек или убедиться, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные. Столбец «Марка машины». На вкладке «Данные» выбрать команду «Сортировка от А до Я (по возрастанию)».
1.3 Сделать активной ячейку в диапазоне данных.
1.4 На вкладке «Данные» в разделе «Структура» выбрать команду «Промежуточные итоги».
1.5 Найдем среднюю цену для каждой марки автомобиля. В появившемся диалоговом окне ввести следующие параметры:
· При каждом изменении в: марка машины.
· Операция: Среднее.
· Добавить итоги по: цена.
· ОК. Средняя цена для каждой марки автомобиля получена.
Рис. 14. Диалоговое окно Промежуточные итоги
2.1 Теперь найдем максимальный пробег для каждой марки автомобиля. Копировать таблицу «Исходные данные» и вставить таблицу, начиная от ячейки А47.
2.2 Выбрать столбец с алфавитно-цифровыми данными в диапазоне ячеек или убедиться, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные. Столбец «Марка машины». На вкладке «Данные» выбрать команду «Сортировка от А до Я (по возрастанию)».
2.3 Сделать активной ячейку в диапазоне данных.
2.4 На вкладке «Данные» в разделе «Структура» выбрать команду «Промежуточные итоги».
2.5 В появившемся диалоговом окне ввести следующие параметры:
· При каждом изменении в: марка машины.
· Операция: Максимум.
· Добавить итоги по: пробег.
· ОК. Максимальный пробег для каждой марки автомобиля получен.
3.1. Теперь найдем количество машин каждой марки. Копировать таблицу «Исходные данные», начиная от ячейки А90.
3.2. Выбрать столбец с алфавитно-цифровыми данными в диапазоне ячеек или убедиться, что активная ячейка находится в столбце таблицы, который содержит алфавитно-цифровые данные. Столбец «Марка машины». На вкладке «Данные» выбрать команду «Сортировка от А до Я (по возрастанию)».
3.3. Сделать активной ячейку в диапазоне данных.
3.4. На вкладке «Данные» в разделе «Структура» выбрать команду «Промежуточные итоги».
3.5. В появившемся диалоговом окне ввести следующие параметры:
· При каждом изменении в: марка машины.
· Операция: Количество.
· Добавить итоги по: марка машины.
· ОК. Нашли количество машин каждой марки.
В итоге мы получим три таблицы, каждая из которых будет содержать ответ к заданию.
Макрос
Макрос — это макрокоманда, содержащая последовательность действий, записанных пользователем.
Макросы можно использовать как для автоматизации обычных задач, так и для создания пользовательских функций.
Задание V
Создать макрос, с помощью которого в поле «Цвет» определяются неповторяющиеся значения; эти значения выводятся в ячейке перечислением через запятую.
Порядок выполнения данного задания:
1. На листе «Исходные данные» выделить столбец «Цвет».
2. Поместить выделенный диапазон данных в буфер обмена.
3. С помощью команды «Специальная вставка» вставить выделенные данные начиная с ячейки С40
4. Выполнить сортировку данных по возрастанию.
5. В ячейку D41 ввести формулу: =С41.
6. В ячейку D42 ввести формулу: =ЕСЛИ(C41=C42;D41;D41&", "&C42)
7. Скопировать формулу на диапазоне D42:D72. При этом в последней строке (ячейка D72) должна получиться нужная последовательность неповторяющихся значений.
8. Поместить значением ячейки H64 в буфер обмена.
9. Вставить скопированное значение с помощью команды «Специальная вставка» в ячейку F41.
Технология создания макроса:
1. Перейти на лист «Исходные данные»
2. В ячейку F40 ввести: «Неповторяющиеся значения по полю "Цвет":»
3. На вкладке «Вид» в разделе «Макросы» выбрать команду «Запись макроса» (Также на вкладке «Разработчик»).
4. В появившемся диалоговом окне ввести название макроса: «Цвет». Нажать клавишу ОК.
5. Выполнить все действия по порядку выполнения задания.
6. Остановить запись макроса. Нужный макрос создан.
Заключение
Выполняя данную курсовую работы, мы освоили различные способы обработки структурированных данных в программах MS Excel и MS Access, такие как:
· поиск данных для заполнения таблиц заданной структуры;
· ввод, редактирование и форматирование данных;
· выборку данных на основе критериев;
· агрегирование данных, применение статистических функций;
· импорт данных из одного приложения в другое;
· создание макроса;
· создание выходных документов в виде отчета.
Библиография
1. Информатика: базовый курс. Под ред. С.В. Симоновича. Москва – Санкт-Петербург – Нижний Новгород: Питер, 2009 – 640 с.
2. Острейковский В.А. Информатика: учебник для студентов технических направлений и специальностей вузов. М.: Высшая школа, 2009. – 511 с.
3. Джонсон С. Microsoft Excel 2007 М.: NT-Press, 2008. – 528.c.
4. Тимошок Т.В. Microsoft Office Access 2007: самоучитель. М.: Вильямс, 2009.
5. Беляев М.А. Основы информатики: Учебник для студентов вузов/ Беляев М.А. [и др.]. – Ростов н/Д: Феникс, 2006. – 352с.: ил. – (Высшее образование).
6. Методический материал кафедры «Прикладная информатика» – \\Capri-srv\общие документы\_Лашманова\энергетический институт – 24.05.2012
Приложения
Приложение 1
Приложение 2
Приложение 3
Приложение 4
Приложение 5
Приложение 6
Приложение 7
Приложение 8
Приложение 9
[1]https://fictionbook.ru/author/vadim_vasilevich_liysenko/osnoviy_informatiki_uchebnik_dlya_vuzov/read_online.html?page=1