Приложение (О.Б. Калугина, В.С. Люцарев - Работа с электронными таблицами. Microsoft Office Excel 2003), страница 2

завершение Задания 3.1.с именем Диаграммы.xls, откройте ее и последовательновыполните задания, приведенные на рабочих листах Объемнаягистограмма и Поверхность. Результаты сохраните в том же файле Диаграммы.xls.214ПриложениеОт теории к практикеСписки в Excel215 В окне приложения Microsoft Office Excel 2003 подайте командуФайл Открыть…В диалоговом окне Открыть укажите текстовый файл Данные дляМНК.txt из своей папки. Далее, шаг за шагом с Мастером текстов1, импортируйте данныеиз текстового файла в ячейки рабочего листа.Задание 6.1 Подготовьте в своей папке копию учебного файла для_Спис-ков.xls с именем СПИСКИ.xls и откройте ее.

Скопируйте данные диапазона Список студентов!$А$1:$G$180на два других листа этой книги, а именно в ячейки: Сортировка!$А$1:$G$180 и Фильтры!$А$1:$G$180. Подберите налистах Сортировка и Фильтры ширину столбцов и высотустрок так, чтобы расположение данных соответствовало исходному списку на листе Список студентов. Выполните задание, приведенное на листе Сортировка. Сохраните текущие результаты.

Выполните задание, приведенное на листе Фильтры. Результатысохраните в том же файле СПИСКИ.xls. Сохраните данные в своей папке в рабочей книге МНК.xls. Присохранении текущих результатов следует правильно задать нетолько папку и имя, но и тип2 файла. На листе Данные для МНК подготовьте и отформатируйте текстовые и числовые значения по образцу рисунка.Задание 6.2 Подготовьте в своей папке копию учебного файла Данные дляМНК.txt с тем же именем.В этом текстовом файле находятся некоторые числовые значения –множества точек, которые нужно аппроксимироватьпрямой линией вида y = ax + b.Выполнение задания включает в себя несколько этапов: импорт данных из текстового файла в рабочую книгуMicrosoft Excel; подготовка расчетной таблицы для вычисления коэффициентов прямой по формулам: нахождение массива значений, описывающих полученнуюпрямую, с помощью функции ЛИНЕЙН.

Построение диаграммы, иллюстрирующей результаты математической обработки данных.Приводимые ниже указания помогут выполнению поставленнойзадачи.12Подробнее см. Глава 6, Импорт из текстового файла.Файл текстового типа (с расширением имени.txt) после того, как был открыт в MicrosoftExcel, не преобразовывается «автоматически» в тип рабочей книги Excel.216Приложение В ячейке В15 вычислите среднее значение по x, а в ячейке С15217От теории к практикедимо указать соответствующее количество ячеек для размещения результатов1. Выделите две ячейки для вычисления коэффициентов прямой – B23:C23. Вызовите Мастер функций и выберите на первом его шаге функцию ЛИНЕЙН из категории Статистические.

На втором шаге Мастера функций задайте аргументы и подтвердите ввод формулы массива нажатием клавиш Ctrl +Shift +Enter.– среднее значение по y, в обоих случаях воспользовавшисьфункцией СРЗНАЧ.В ячейке D5 подготовьте формулу для вычисления разности значений (x1 – xср) так, чтобы при ее копировании по столбцу таблицы ссылки на ячейки со значениями xi автоматически преобразовывались, а ссылка на ячейку со средним значением по x оставалась неизменной.Скопируйте формулу из ячейки D5 в ячейки D6:D14.Аналогично подготовьте формулу в ячейке E5 для разности(y1 – yср) и скопируйте ее в ячейки E6:E14.Заполните столбцы таблицы F5:F14 и G5:G14 формулами,соответствующими заголовкам – произведениями разностей(xi – xср)*(yi – yср) и квадратами разностей значений (xi – xср)2соответственно.

Проверьте себя, сравнив результаты вычислений с рисунком1.Ctrl +Shift + Enter Сравните значения коэффициентов пря-мой, полученные разными способами. В ячейке F15 вычислите сумму по этому столбцу расчетной таб-лицы (F5:F14), а в ячейке G15 – сумму значений ячеек G5:G14. Сверившись с математическими фор-мулами для коэффициентов прямой,приведенными в начале этого задания,подготовьте на листе расчетные формулы в ячейках C19 и C20. Функция ЛИНЕЙН из категории Статистические даст возможность проверить произведенный расчет коэффициентов. Обычно результатом выполнения этой функции являются несколькозначений, поэтому формула с ее использованием должна бытьподготовлена как формула массива. Это, прежде всего, означает,что перед вызовом функции ЛИНЕЙН на рабочем листе необхо1Форматы числовых значений также должны соответствовать примеру рисунка.

Заполнитепоследний столбец таблицы с заголовкомyтеор.= axi + b. В ячейке H5 подготовьте формулу для расчета теоретического значения y так, чтобы при копировании этой формулы преобразовывалась только ссылка на ячейку со значениемx, ссылки же на ячейки со значениями коэффициентов прямойоставались неизменными. Скопируйте формулу из ячейки H5 по столбцу вниз, в ячейкиH6:H14.1Если нужно получить только два коэффициента прямой — то две ячейки; если, кромеэтого, необходимо вернуть дополнительную регрессионную статистику, то в случае рассматриваемого уравнения прямой y = ax + b нужно будет выделить 10 ячеек.218ПриложениеОт теории к практике Выделив диапазоны с необходимыми данными (B4:C14;219 Сохраните результаты в файле МНК.xls.H4:H14), постройте на отдельном листе этой рабочей книги диаграмму точечного типа.Задание 6.3 Отредактируйте элементы диаграммы, предложенной Масте-ром, так, чтобы исходные значения y (С5:С14) отображались награфике отдельными маркерами, а теоретические (Н5:Н14) –прямой линией без маркеров.Напомним, что в параграфе Запросы к базам данных Главы 6 создание запроса и получение данных по нему разбирается на конкретномпримере для БД1, которая хранит списки студентов и различные сведения, связанные с обучением этих студентов.

Требовалось импортироватьна рабочий лист Excel единый список, включающий сведения о фамилиях (F2), именах (I), отчествах (O) студентов 101 и 102 учебных групп(Grp), названиях выполненных ими заданий (TDescr) и полученныхоценках (Mark).Сейчас же предлагается, следуя поставленной задаче, указаниям ииллюстрирующим их рисункам на стр.171-178, самостоятельно пройти всешаги, необходимые для создания аналогичного запроса, и получить на рабочем листе конкретные данные (см. рис. полученного списка на стр.178). Для выполнения этого задания в своей папке создайте новуюкнигу Microsoft Excel с именем ЗАПРОСЫ.xls. Подготовьте запрос и получите по нему данные из БД AccessStudents.mdb, которая находится в общей папке с учебными файлами.

Переименуйте лист с импортированными данными в Список.Сохраните текущий результат в той же книге ЗАПРОСЫ.xls. По данным полученного списка постройте на отдельном листекниги сводную таблицу3. Согласно макету, приведенному нарисунке,123База данных.В скобках указаны имена соответствующих полей.О построении и редактировании сводных таблиц читайте в параграфе Понятие отчетасводной таблицы в Главе 6 (стр.147-158).220Приложениесводная таблица должна выглядеть примерно так:От теории к практике221 Перейдите к листу Сводная таблица и подайте для имеющейся нанем сводки данных о средних оценках команду Обновить. Результат ее выполнения должен быть аналогичен приведенномуниже рисунку. Переименуйте Лист4 в Сводная таблица. Выведите отдельными листами книги сводки по группам.

Дляэтого в раскрывающемся списке Сводная таблица1 подайте команду Отобразить страницы для поля Grp – в книге автоматически должны появится еще два листа с именами 101 и 102 соответственно. Вернитесь к работе с листом Список и измените для него запроск базе данных Students.mdb так, чтобы импортировались аналогичные сведения (Grp, F, I, O, TDescr, Mark), но теперь длястудентов 105, 106 и 107 групп. Полученный текущий результат сохраните в книге ЗАПРОСЫ.xls.1В панели инструментов Сводные таблицы. Выведите отдельными листами книги сводки по группам 105,106 и 107.

Удалите Лист2 и Лист3, назначьте ярлычку Сводная таблицасветло-зеленый цвет, а ярлычку Список – голубой. Для проверки правильности выполнения этого задания сверьтеярлычки листов своей рабочей книги ЗАПРОСЫ.xls с рисунком Сохраните результаты работы в той же книге ЗАПРОСЫ.xls.222ПриложениеПодготовка к печатиЗадание 7.1 Подготовьте к печати данные листа Список студентов из книгиСПИСКИ.xls1 по следующим указаниям.1. Задайте область печати на листе так, чтобы она включала всебя значения всех строк списка, но только для столбцов:2.

В Параметрах страницы для установок, заданных по умолчанию, внесите следующие изменения: Поскольку печать всех строк списка займет несколькостраниц формата А4, укажите как сквозную строку названия столбцов списка (№ п/п Фамилия Имя …). Задайте колонтитулы по образцам рисунков ниже.Верхний колонтитулНижний колонтитул Установите масштаб печати 120%, центрирование дан-ных на странице по горизонтали, проверьте диапазондля вывода на печать (А2:Е180).1Результат выполнения Задания 6.1..