Файл: Методичка MS Excel(2011).doc

Заполнить ячейки таблицы произвольными числовыми данными.

3. Используя Мастер диаграмм, построить плоскостной график изменения выручки разных отделов по месяцам полугодия.

Перед вызовом Мастера диаграмм выделить всю таблицу, включая названия месяцев и отделов. На Шаге1 выбрать тип: График и вид графика, описывающий развитие процесса во времени и по категориям (см. подсказку внизу диалогового окна). На Шаге2 задать группировку рядов по строкам. На Шаге3 задать название диаграммы: Изменение выручки отделов по месяцам полугодия, для оси Х: Названия месяцев, для оси У: Выручка от реализации товаров, размещение Легенды справа. На Шаге 4 задать вывод диаграммы на Лист1.

4. Построить объемный график изменения выручки каждого месяца полугодия по разным отделам.

Выполняется аналогично предыдущему пункту, только на Шаге1 выбирается вид: объемный вариант графика; на Шаге2 задается группировка рядов по столбцам; на Шаге3 задается название диаграммы: Изменение выручки за разные месяцы по отделам, для оси Х: Номера отделов, для оси У: Месяцы, для оси Z: Выручка от реализации товаров, размещение Легенды справа.

5. Построить гистограмму изменения выручки по отделам для первых трех месяцев полугодия.

Выполняется аналогично предыдущим пунктам, но предварительно выделяются только первых три месяца (с названиями); на Шаге1 выбирается тип: Гистограмма и вид: обычная гистограмма (отображает значения различных категорий); на Шаге2 задается группировка рядов по строкам; на Шаге3 задается название гистограммы: Гистограмма изменения выручки за три месяца, для оси Х: Месяцы, для оси У: Выручка от реализации товаров, размещение Легенды справа.

6. Построить круговую плоскостную диаграмму распределения выручки за январь по отделам.

Выполняется аналогично предыдущим пунктам, но предварительно выделяются данные первого столбца (с названием месяца и названиями отделов); на Шаге1 выбирается тип: Круговая и вид: круговая диаграмма (отображает вклад каждого значения в общую сумму); на Шаге2 задается группировка рядов по столбцам; на Шаге3: размещение Легенды справа.

7. Построить круговую объемную диаграмму распределения выручки Отдела1 по месяцам полугодия. Сделать выделение данных первой строки, включая Отдел1 и названия месяцев.

Выполняется аналогично предыдущему пункту, но предварительно выделяются только данные первой строки (включая Отдел1 и названия месяцев); на Шаге1 выбирается тип: Круговая и вид: объемный вариант круговой диаграммы ; на Шаге2 задается группировка рядов по строкам; на Шаге3: размещение Легенды справа.

8. На Листе2 построить график функции y = 5 x² +2 для точек на отрезке [0,10] с шагом 0,5.

Для этого следует предварительно сформировать на Листе2 таблицу значений переменных х и у:

Значения х	Значения у

---

Для заполнения столбца значений х необходимо в его первую ячейку (где должно быть записано первое значение х) ввести начальное значение 0, сделать эту ячейку текущей, а затем выполнить команду Правка→ Заполнить→ Прогрессия. В окне диалога включить следующие режимы: по столбцам; арифметическая; шаг 0,5; предельное значение 10.

Столбец значений у заполняется как обычный расчетный столбец ЭТ. В первую ячейку столбца записывает формулу пользователь, в остальные ячейки эта формула копируется. При записи формулы следует помнить, что роль переменной х играют ячейки ЭТ, в которых содержатся значения этой переменной.

Перед вызовом Мастера диаграмм необходимо выделить оба столбца (х и у) вмести названиями. При построении графика на Шаге1 выбрать тип: Точечная и вид: «Точечная диаграмма со значениями, соединенными сглаживающими линиями без маркеров» (см. подсказку внизу окна диалога); на Шаге 2: ряды в столбцах; на Шаге3: задать название графика График функции У, для оси Х: Значения аргумента Х, для оси У: Значения функции У, расположить легенду справа.

9. Добавить к таблице на Листе2 столбец для значений функции z = 3 x² + 1 и назвать его Значения Z. Заполнить этот столбец, задав вычисление значений функции z в тех же точках х, что и для функции у.

Построить на одной системе координат графики сразу двух функций у и z. Выполняется аналогично пункту 8, только перед вызовом Мастера Диаграмм нужно выделить все три столбца (х, у и z) вместе с названиями. На Шаге2 задать название: Графики функций Х и У, для оси Х: Значения аргумента Х, для оси У: Значения функции У и Z, расположить легенду справа.

10. Сохранить полученные результаты в файл Таблица4-фамилия в своей папке Фамилия на диске D:.

Лабораторная работа N5

Работа с таблицей как с базой данных

Выполнение статистических расчетов по данным таблицы

5.1. Понятие базы данных (БД)

Базами данных называют большие объемы информации, имеющей определенную структуру и хранящиеся в специальных файлах на диске (файлах баз данных).

База данных реляционной модели (relation – отношение) представляет собой двумерную таблицу (т.е. на плоскости два измерения: столбец и строка), строки которой принято называть записями, а столбцы – полями.

Каждое поле БД (столбец) должно иметь свое уникальное имя и может содержать только однотипные данные. Запись БД (строка), наоборот, может состоять из разнотипных значений, т.к. в одной БД (таблице) могут содержаться разнотипные поля (столбцы). Наиболее часто используются поля числового и символьного (текстового) типа.

Значения внутри записи являются неразрывно связанными данными, т.к. они представляют собой сведения о некотором объекте (человеке, товаре, детали машины и т.п.). Поэтому, например, при сортировке значений по некоторому полю таблицы перестановке должны подлежать записи (строки) целиком, а не только значения сортируемого поля.

---

Для обработки таблицы ЭТ как базы данных в программе MS Excel используется специальная команда Данные. При этом перед выполнением команды необходимо выделить всю БД, т.е. таблицу целиком вместе со строкой с именами полей (столбцов). В качестве имен полей можно использовать уже заданные названия столбцов в том случае, если «шапка» таблицы занимает всего одну строку ЭТ. В противном случае необходимо добавить строку с именами полей над строками с данными.

Пример БД реляционной модели:

Фамилия	Год рождения	Адрес	Телефон
Иванов А.В.	1965	Тверь, ул. Горького, 22-5	55-32-10
Петров С.И.	1973	Тверь, ул. Советская, 20-15	31-25-77
Сидоров К.П.	1969	Тверь, Тверской пр-т, 18-10	33-38-45
Иванов И.К.	1975	Тверь, ул. Благоева, 34-2	56-43-87
Смирнов Д.А.	1980	Тверь, ул. Спартака, 15-24	44-65-38
Сипейкин А.А.	1982	Тверь, ул. Бебеля, 5-17	23-34-65
Мурашов Д.В.	1974	Тверь, ул. Левитана, 2-14	45-33-51

5.2. Сортировка записей БД

Для выполнения сортировки записей БД (строк таблицы) используется команда Данные→Сортировка. Предварительно должна быть выделена вся БД, т.е. вся таблица вместе с именами полей (а не один столбец!). Тем самым мы показываем программе MS Excel, что является нашей БД (вся таблица, а не один столбец).

Далее в окне диалога команды Данные→Сортировка нужно задать порядок сортировки (по возрастанию или убыванию), а также поле (столбец), по которому будет происходить сортировка записей (строк), т.е. ключ сортировки. Для символьных полей сортировка по возрастанию соответствует сортировке по алфавиту, а по убыванию – в порядке обратном алфавитному.

В программе MS Excel может задаваться последовательная сортировка записей БД по трем ключам (первичному, вторичному и третичному). Кроме того, пользователь может сам выбрать способ идентификации полей сортировки (ключей) либо по названиям полей, либо по обозначениям столбцов ЭТ (например, если у БД нет строки с именами полей).

5.3. Поиск, изменение, добавление и удаление записей в БД

Для выполнения различных действий над записями БД: их редактирования, добавления новых записей в БД, удаления записей из БД, поиска нужной записи в большой БД и т.п. в MS Excel используется команда Данные→Форма.

Данная команда выдает окно диалога, в котором в столбик перечислены все поля БД и их значения. Вначале выдаются сведения по первой записи БД. В этом окне диалога можно изменять (редактировать) значения полей (все изменения будут внесены в таблицу на листе ЭТ). Для листания записей БД в окне диалога можно использовать вертикальные линии прокрутки, либо кнопки Назад и Далее. Для добавления новых записей в БД или удаления записей из БД также используются соответствующие кнопки: Добавить и Удалить.

---

С помощью кнопки Критерии можно осуществлять поиск нужных записей в большой БД по заданному критерию (условию поиска). После щелчка по этой кнопке выдается диалоговое окно со всеми именами полей, но без значений полей. Пользователь должен задать значения тех полей, по которым будет производиться поиск подходящих записей. Когда критерий поиска задан, остается щелкнуть по кнопке Далее.

5.4. Статистические расчеты

Для выполнения статистических расчетов по данным таблицы в MS Excel используется команда Сервис→Анализ данных…. Если данная команда отсутствует в меню, то ее можно включить с помощью команды Сервис→Надстройки (флажок Пакет анализа).

Команда Сервис→Анализ данных… выдает окно диалога со списком различных видов анализа: Однофакторный дисперсионный анализ, Двухфакторный дисперсионный анализ с повторениями, Двухфакторный дисперсионный анализ без повторений, Ковариация, Корреляция, Описательная статистика и т.д.

После выбора нужного вида анализа появится окно диалога, в котором требуется задать: входной интервал – адрес блока с данными (можно ввести, можно выделить в ЭТ), порядок группирования данных – по столбцам или по строкам, адрес выходного интервала – адрес блока, куда будет выдан результат анализа (можно задать адрес только верхней левой ячейки этого блока). При желании можно задать вывод результата на новый Лист ЭТ или записать в новую Книгу. Некоторые виды анализа требуют задания каких-либо специфических параметров. Например, при выполнении однофакторного дисперсионного анализа необходимо задать значение коэффициента α (альфа).

При выполнении статистических расчетов нет необходимости делать предварительное выделение таблицы, т.к. в любом случае потребуется задать адрес входного интервала в окне диалога. Если первая строка входного интервала содержит названия столбцов, то в окне диалога следует включить флажок Метки в первой строке. Тогда результат анализа будет иметь те же заголовки столбцов (строк), что и исходная таблица с данными.

5.5. Практическое задание для выполнения на ПК

1. Запустить MS Excel.

2. Открыть свою таблицу из файла Таблица3-фамилия и подготовить таблицу Листа1 для работы с ней как с БД.

Для этого необходимо вставить пустую строку между «шапкой» таблицы и данными. Записать в эту строку имена полей (названия столбцов). После этого строки старой «шапки» можно удалить.

Подумать, для чего были сделаны предыдущие действия (почему старая шапка не подходила).

3. Добавить в таблицу еще 10 строк с данными (по исходным данным ввести произвольные значения, по расчетным – скопировать формулы).

4. Отсортировать записи БД в алфавитном порядке фамилий (первичный ключ сортировки). При задании сортировки не забывать, что является БД (предварительно выделять всю таблицу, а не один столбец).

---

5. Отсортировать записи БД по убыванию начислений (первичный ключ сортировки).

6. Просмотреть данные своей таблицы и изменить их так, чтобы некоторая фамилия (например, Иванов) встречалась несколько раз (2-3 раза).

С помощью команды Данные→Форма задать автоматический поиск записи с заданной фамилией (например, Иванов) в вашей БД. Для этого использовать кнопку Критерии в окне диалога команды Данные→Форма. Убедиться, что поиск работает для всех вхождений искомого значения в вашей БД (кнопка Далее или Назад).

7. Удалить одну из найденных записей из БД (кнопка Удалить в окне диалога команды Данные→Форма). Убедиться, что после удаления записи БД пустая строка опустилась в конец БД.

8. Выполнить однофакторный дисперсионный анализ. В качестве входного интервала взять числовые данные таблицы (начиная, со столбца «Тариф»). Коэффициент α: 0,05. Группирование: по столбцам. Результат выдать либо ниже, либо справа от таблицы (на пустом месте). Результат должен содержать те же заголовки, что и названия столбцов в исходной таблице.

9. Вычислить корреляцию. В качестве входного интервала взять числовые данные таблицы (начиная, со столбца «Тариф»). Группирование: по столбцам. Результат выдать либо ниже, либо справа от таблицы (на пустом месте). Результат должен содержать те же заголовки, что и названия столбцов в исходной таблице.

10 . Вычислить описательную статистику (включить флажок Итоговая статистика). В качестве входного интервала взять числовые данные таблицы (начиная, со столбца «Тариф»). Группирование: по столбцам. Результат выдать либо ниже, либо справа от таблицы (на пустом месте). Результат должен содержать те же заголовки, что и названия столбцов в исходной таблице.

Контрольные вопросы

Лабораторная работа N1

1. В чем основное назначение ЭТ?

2. Какие дополнительные возможности предоставляют ЭТ?

3. Способы запуска MS Excel.

4. Содержание окна MS Excel.

5. Какую структуру имеет рабочая область ЭТ? Обозначение строк и столбцов ЭТ.

6. Ячейка и ее адрес.

7. Блок (диапазон) ячеек и его адрес.

8. Как завершить ввод информации в ячейку?

9. Способы редактирования информации в ЭТ.

10. Как очистить содержание ячейки (блока)?

11. Виды информации.

12. Как MS Excel определяет тип вводимой пользователем информации?

13. Правила записи формулы.

14. Как узнать, какая формула была записана в ячейку?

15. Способы изменения ширины столбцов, высоты строк.

16. Отличия команд Файл→Сохранить и Файл→Сохранить как…?

17. Назначение команд Файл→Создать и Файл→Открыть.

Лабораторная работа N2

1. Способы выделения блока.

2. Как выполняется центрирование заголовка таблицы?

3. Способы обрамления контуров таблицы.

4. Способы цветового оформления таблицы.

5. Способы копирования ячеек.

6. Способы перемещения ячеек.

7. Способы вставки строк и столбцов.

8. Способы удаления строк и столбцов.

---