ПРАКТИЧЕСКАЯ РАБОТА. Практическая работа задание Формирование структуры таблицы и заполнение ее постоянными данными

• Название: Практическая работа задание Формирование структуры таблицы и заполнение ее постоянными данными
• Дата: 21.12.2022
• Размер: 286.5 Kb.
• Имя файла: ПРАКТИЧЕСКАЯ РАБОТА.doc
• Тип: Практическая работа; #856988
• страница: 2 из 2

ЛАБОРАТОРНЫЕ РАБОТЫ

Лабораторная работа 1. Создание и заполнение таблицы постоянными данными и формулами (2 часа)

Порядок выполнения:

1. Ознакомьтесь с теоретическими сведениями (п. 3.1).
2. Выполните практическое задание 1.
3. Выполните практическое задание 2.
4. Выполните практическое задание 3.
5. Оформите отчет, в котором должны быть отражены следующие технологии по созданию и заполнение таблицы постоянными данными и формулами: ввод текстовых и числовых данных в электронные таблицы; ввод и вычисление формул; копирование формул методом автозаполнения; использование относительных и абсолютных ссылок.

Лабораторная работа 2. Построение, редактирование и форматирование диаграмм (4 часа)

Порядок выполнения:

1. Ознакомьтесь с теоретическими сведениями (п. 3.2).
2. Выполните практическое задание 4.
3. Выполните практическое задание 5.
4. Выполните практическое задание 6.
5. Выполните практическое задание 7.
6. Оформите отчет, в котором должны быть отражены технологии по построению, редактированию и форматированию диаграмм, а также построению трендов.

Лабораторная работа 3. Сортировка данных (2 часа)

Порядок выполнения:

1. Ознакомьтесь с теоретическими сведениями (п. 3.3).
2. Выполните практическое задание 8.
3. Оформите отчет, в котором должны быть отражены основные технологии по сортировке данных.

Лабораторная работа 4. Фильтрация (выборка) данных (2 часа)

Порядок выполнения:

1. Ознакомьтесь с теоретическими сведениями (п. 3.4).
2. Выполните практическое задание 9.
3. Выполните практическое задание 10.
4. Выполните практическое задание 11.
5. Оформите отчет, в котором должны быть отражены технологии выборки данных с использованием Автофильтра, Расширенного фильтра, Критерия сравнения, Вычисляемого критерия, Формы.

Лабораторная работа 5. Структурирование таблиц (2 часа)

Порядок выполнения:

1. Ознакомьтесь с теоретическими сведениями (п. 3.5).
2. Выполните практическое задание 12.
3. Выполните практическое задание 13.
4. Выполните практическое задание 14.
5. Оформите отчет, в котором должны быть отражены следующие технологии по структурированию таблиц: структурирование таблицы ручным способом; автоструктурирование таблицы и введение дополнительного иерархического уровня структуры ручным способом; структурирование таблицы с автоматическим подведением итогов по группам таблицы.

Лабораторная работа 6. Создание сводных таблиц (2 часа)

Порядок выполнения:

1. Ознакомьтесь с теоретическими сведениями (п. 3.6).
2. Выполните практическое задание 15.
3. Оформите отчет, в котором должны быть отражены основные технологии по созданию сводных таблиц.

Лабораторная работа 7. Консолидация данных (2 часа)

Порядок выполнения:

1. Ознакомьтесь с теоретическими сведениями (п. 3.7).
2. Выполните практическое задание 16.
3. Оформите отчет, в котором должны быть отражены технологии консолидации данных по расположению и по категориям.

САМОСТОЯТЕЛЬНАЯ РАБОТА

Задание 1. Обработка данных

1. Запустите программу Excel (Пуск —› Программы —› Microsoft Excel).
2. Создайте новую рабочую книгу (кнопка Создать на стандартной панели инструментов).
3. Дважды щелкните на ярлычке текущего рабочего листа и дайте этому рабочему листу имя Данные.
4. Дайте команду Файл —› Сохранить как и сохраните рабочую книгу под именем book.xls.
5. Сделайте текущей ячейку A1 и введите в нее заголовок Результаты измерений.
6. Введите произвольные числа в последовательные ячейки столбца A, начиная с ячейки A2.
7. Введите в ячейку B1 строку Удвоенное значение.
8. Введите в ячейку C1 строку Квадрат значения.
9. Введите в ячейку D1 строку Квадрат следующего числа.
10. Введите в ячейку B2 формулу = 2*A2.
11. Введите в ячейку С2 формулу = A2*A2.
12. Введите в ячейку D2 формулу = B2 + C2 + 1.
13. Выделите протягиванием ячейки B2, C2 и D2.
14. Наведите указатель мыши на маркер заполнения в правом нижнем углу рамки, охватывающей выделенный диапазон. Нажмите левую кнопку мыши и перетащите этот маркер, чтобы рамка охватила столько строк в столбцах B, C и D, сколько имеется чисел в столбце A.
15. Убедитесь, что формулы автоматически модифицируются так, чтобы работать со значением ячейки в столбце A текущей строки.
16. Измените одно из значений в столбце A и убедитесь, что соответствующие значения в столбцах B, C и D в этой же строке были автоматически пересчитаны.
17. Введите в ячейку E1 строку Масштабный множитель.
18. Введите в ячейку E2 число 5.
19. Введите в ячейку F1 строку Масштабирование.
20. Введите в ячейку F2 формулу = A2*E2.
21. Используйте метод автозаполнения, чтобы скопировать эту формулу в ячейки столбца F, соответствующие заполненным ячейкам столбца A.
22. Убедитесь, что результат масштабирования оказался неверным. Это связано с тем, что адрес E2 в формуле задан относительной ссылкой.
23. Щелкните на ячейке F2, затем в строке формул. Установите текстовый курсор на ссылку E2 и нажмите клавишу F4. Убедитесь, что формула теперь выглядит как =A2*$E$2, и нажмите клавишу Enter.
24. Повторите заполнение столбца F формулой из ячейки F2.
25. Убедитесь, что благодаря использованию абсолютной адресации значения ячеек столбца F теперь вычисляются правильно. Сохраните рабочую книгу books.xls.

Задание 2. Применение итоговых функций

1. Запустите программу Excel и откройте рабочую книгу book.xls, созданную ранее.
2. Выберите рабочий лист Данные.
3. Сделайте текущей первую свободную ячейку в столбце A.
4. Щелкните на кнопке Автосумма на стандартной панели инструментов.
5. Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу Enter.
6. Сделайте текущей следующую свободную ячейку в столбце A.
7. Щелкните на кнопке Вставка функции на стандартной панели инструментов.
8. В списке Категория выберите пункт Статистические.
9. В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке ОК.
10. Переместите методом перетаскивания палитру формул, если она заслоняет нужные ячейки. Обратите внимание, что автоматически выбранный диапазон включает в себя все ячейки с числовым содержанием, в том числе и ту, которая содержит сумму. Выделите правильный диапазон методом протягивания и нажмите клавишу Enter.
11. Используя порядок действий, описанный в пп.6-10, вычислите минимальное число в выбранном наборе (функция МИН), максимальное число (МАКС), количество элементов в наборе (СЧЕТ).
12. Сохраните рабочую книгу book.xls.

Задание 3. Подготовка и форматирование прайс-листа

1. Запустите программу Excel и откройте рабочую книгу book.xls.
2. Выберите щелчком на ярлыке неиспользованный рабочий лист или создайте новый, переименовав его на Прейскурант.
3. В ячейку A1 введите текст Прейскурант и нажмите клавишу Enter.
4. В ячейку A2 введите текст Курс пересчета: и нажмите клавишу Enter. В ячейку B2 введите текст 1 у. е. = и нажмите клавишу Enter.
В ячейку C2 введите текущий курс пересчета и нажмите клавишу Enter.
5. В ячейку A3 введите текст Наименование товара и нажмите клавишу Enter. В ячейку B3 введите текст Цена (у. е.) и нажмите клавишу Enter. В ячейку С3 введите текст Цена (руб.) и нажмите клавишу Enter.
6. В последующие ячейки столбца A введите названия товаров, включенных в прейскурант.
7. В соответствующие ячейки столбца B введите цены товаров в условных единицах.
8. В ячейку C4 введите формулу: =B4*$C$2, которая используется для пересчета цены из условных единиц в рубли.
9. Методом автозаполнения скопируйте формулы во все ячейки столбца С, которым соответствуют заполненные ячейки столбцов A и B.
10. Измените курс пересчета в ячейке С2. Обратите внимание, что цены в рублях изменяются автоматически.
11. Выделите методом протягивания диапазон A1:C1 и дайте команду Формат —› Ячейки. На вкладке Выравнивание задайте выравнивание по горизонтали По центру и установите флажок Объединение ячеек.
12. На вкладке Шрифт задайте размер шрифта в 14 пунктов и в списке Начертание выберите вариант Полужирный. Щелкните на кнопке ОК.
13. Щелкните правой кнопкой мыши на ячейке B2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По правому краю и щелкните на кнопке ОК.
14. Щелкните правой кнопкой мыши на ячейке C2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По левому краю и щелкните на кнопке ОК.
15. Выделите методом протягивания диапазон B2:C2. С помощью кнопки Границы задайте для этих ячеек внешнюю рамку.
16. Дважды щелкните на границе между заголовками столбцов A и B, B и C, C и D. Обратите внимание, как при этом изменяется ширина столбцов A, B и C.
17. Щелкните на кнопке Предварительный просмотр на стандартной панели инструментов, чтобы увидеть, как документ будет выглядеть при печати.
18. Сохраните рабочую книгу book.xls.

Задание 4. Построение экспериментального графика

1. Запустите программу Excel и откройте рабочую книгу book.xls, созданную ранее.
2. Выберите щелчком на ярлычке неиспользуемый рабочий лист (или создайте новый) и переименуйте его на Обработка эксперимента.
3. В столбец A, начиная с ячейки A1, введите произвольный набор значений независимой переменной.
4. В столбец B, начиная с ячейки B1, введите произвольный набор функции.
5. Методом протягивания выделите все заполненные ячейки столбцов A и B.
6. Щелкните на значке Мастер диаграмм на стандартной панели инструментов.
7. В списке Тип выберите пункт Точечная (для отображения графика, заданного парами значений). В палитре Вид выберите средний пункт в первом столбце (маркеры, соединенные гладкими кривыми). Щелкните на кнопке Далее.
8. Так как диапазон ячеек был выбран заранее, Мастер диаграмм автоматически определяет расположение рядов данных. На вкладке Ряд в поле Имя укажите: Результаты измерений. Щелкните на кнопке Далее.
9. Выберите вкладку Заголовки. Убедитесь, что заданное название ряда данных автоматически использовано как заголовок диаграммы. Замените его, введя в поле Название диаграммы заголовок Экспериментальные точки. Щелкните на кнопке Далее.
10. Установите переключатель Отдельном. По желанию, задайте произвольное имя добавляемого рабочего листа. Щелкните на кнопке Готово.
11. Убедитесь, что диаграмма построена и внедрена в новый рабочий лист. Рассмотрите ее и щелкните на построенной кривой, чтобы выделить ряд данных.
12. Дайте команду Формат ® Выделенный ряд. Откройте вкладку Вид.
13. На панели Линия откройте палитру Цвет и выберите красный цвет. В списке Тип линии выберите пунктир.
14. На панели Маркер выберите в списке Тип маркера треугольный маркер. В палитрах Цвет и Фон выберите зеленый цвет.
15. Щелкните на кнопке ОК, снимите выделение с ряда данных и посмотрите, как изменился вид графика.
16. Сохраните рабочую книгу.

Задание 5. Анализ данных с использованием метода наименьших квадратов
Для заданного набора пар значений независимой переменной и функции определить наилучшее линейное приближение в виде прямой с уравнением y = aЧx + b и показательное приближение в виде линии с уравнением y = bЧax.

1. Запустите программу Excel и откройте рабочую книгу book.xls, созданную ранее.
2. Выберите рабочий лист Обработка эксперимента.
3. Сделайте ячейку C1 текущей и щелкните на кнопке Изменить формулу в строке формул. Раскройте список на левом краю строки формул и выберите пункт Другие функции.
4. В окне мастера функций выберите категорию Ссылки и массивы и функцию ИНДЕКС. В новом диалоговом окне выберите первый вариант набора параметров.
5. Установите текстовый курсор в первое поле для ввода параметров в палитре формул и снова выберите пункт Другие функции в раскрывающемся списке в строке формул.
6. С помощью мастера функций выберите функцию ЛИНЕЙН категории Статистические.
7. В качестве первого параметра функции ЛИНЕЙН выберите диапазон, содержащий значения функции (столбец B).
8. В качестве второго параметра функции ЛИНЕЙН выберите диапазон, содержащий значения независимой переменной.
9. Переместите текстовый курсор в строке формул, чтобы он стоял на имени функции ИНДЕКС. В качестве второго параметра функции ИНДЕКС задайте число 1. Щелкните на кнопке ОК на палитре формул.
Пояснение: функция ЛИНЕЙН возвращает коэффициенты уравнения прямой в виде массива из двух элементов, а функция ИНДЕКС выбирает нужный элемент.
10. Сделайте текущей ячейку D1. Повторите операции, описанные в пп. 3-9, чтобы в итоге в этой ячейке появилась формула: ИНДЕКС (ЛИНЕЙН(B1:B5;A1:A5);2). Ее можно ввести и вручную (посимвольно). Теперь в ячейках C1 и D1 вычислены соответственно коэффициенты a и b уравнения наилучшей прямой.
11. Сделайте текущей ячейку C2. Повторите операции, описанные в пп.
3-9, или введите вручную формулу: =ИНДЕКС(ЛГРФПРИБЛ(B1:B5;A1:A5);1).
12. Сделайте текущей ячейку D2. Повторите операции, описанные в пп.
3-9, или введите вручную формулу: =ИНДЕКС(ЛГРФПРИБЛ(B1:B5;A1:A5);2).
Теперь ячейки C2 и D2 содержат соответственно коэффициенты
a и b уравнения наилучшего показательного приближения.
13. Для построения наилучшей прямой другим способом дайте команду Сервис ® Анализ данных.
14. В списке Инструменты анализа выберите пункт Регрессия, после чего щелкните на кнопке ОК.
15. В поле Входной интервал Y укажите методом протягивания диапазон, содержащий значения функции (столбец B).
16. В поле Входной интервал X укажите методом протягивания диапазон, содержащий значения аргумента (столбец A).
17. Установите переключатель Новый рабочий лист, для которого задайте имя Результат расчета.
18. Щелкните на кнопке ОК и по окончании расчета откройте рабочий лист Результат расчета. Убедитесь, что вычисленные коэффициенты (см. ячейки B17 и B18) совпали с полученными первым способом.
19. Сохраните рабочую книгу book.xls.

Задание 6. Применение таблиц подстановки для построения графиков функций
Построить графики функций, коэффициенты которых определены в предыдущем задании.

1. Запустите программу Excel и откройте рабочую книгу book.xls.
2. Создайте рабочий лист Обработка эксперимента.
3. Для табулирования формулы сделайте текущей ячейку С3 и занесите в нее значение 0. Эта ячейка будет использоваться как ячейка ввода, на которую будут ссылаться формулы.
4. Скопируйте значения столбца А в столбец F, начиная со второй строки.
5. В ячейку G1 введите формулу =C3*$C$1+$D$1. Здесь C3 - ячейка ввода, а в качестве других ссылок используются вычисленные методом наименьших квадратов коэффициенты уравнения прямой.
6. В ячейку H1 введите формулу =$D$2*$C$2^C3 для вычисления значения показательной функции.
7. Выделите прямоугольный диапазон, включающий столбцы F, G
и H и строки от строки 1, содержащей формулы, до последней строки
с данными в столбце F.
8. Дайте команду Данные ® Таблица подстановки. Выберите поле Подставить значения по строкам и щелкните на ячейке ввода С3.
9. Щелкните на кнопке ОК, чтобы заполнить пустые ячейки в столбцах G и H выделенного диапазона значениями формул в ячейках первой строки для значений независимой переменной, выбранных из столбца F.
10. Щелкните на кнопке Мастер диаграмм на стандартной панели инструментов, выберите Тип График и щелкните на кнопке Далее.
11. В пункте Диапазон данных в строках укажите диапазон ячеек с данными в столбце F, а в пункте столбцы - диапазон ячеек в столбце G. Щелкните на кнопке Далее.
12. В поле Название диаграммы выберите Наилучшая прямая. В двух следующих полях можно дать названия осям координат. Щелкните на кнопке Далее.
13. Поместите диаграмму на отдельном листе, дав ему имя График 1. Щелкните на кнопке Готово.
14. Повторите пп. 10-13 для построения Показательной функции. Поместите диаграмму на отдельном листе, дав ему имя График 2.
15. Сохраните рабочую книгу book.xls.

Задание 7. Решение уравнений средствами программы Excel
Найти решение уравнения x3 - 3x2 + x = -1.

1. Запустите программу Excel и откройте рабочую книгу book.xls.
2. Создайте новый рабочий лист, дав ему имя Уравнение.
3. Занесите в ячейку А1 значение 0 (начальное значение).
4. Занесите в ячейку B1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку A1. Для заданного уравнения формула имеет вид: =A1^3-3*A1^2+A1.
5. Дайте команду Сервис —› Подбор параметра.
6. В поле Установить в ячейке укажите B1, в поле Значение задайте -1, в поле Изменяя значение ячейки укажите A1.
7. Щелкните на кнопке ОК и посмотрите на результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Щелкните на кнопке ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции.
8. Повторите расчет, задавая в ячейке A1 другие начальные значения, например 0,5 или 2. Совпали ли результаты вычислений? Чем можно объяснить различия?
9. Сохраните рабочую книгу book.xls.