Информатика

Практическая работа № 11

Тема: «Использование различных возможностей динамических (электронных) таблиц. Средства графического представления статистических данных»

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

Теория.

Возможности динамических (электронных) таблиц

Класс программного обеспечения, используемый для этой цели, называется электронными (динамическими) таблицами. Их назначение — обработка экономической, бухгалтерской, инженерной, статистической информации, проведение расчетов и обеспечение визуального представления хранимых данных и результатов их обработки в виде графиков, диаграмм.

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

- в проведении вычислений по заданным формулам, причем при изменении данных происходит автоматический перерасчет всей таблицы;

- в использовании различных шаблонов, стилей, форматировании ячеек;

- в автоматизации процессов ввода исходных данных;

- в применении различных видов адресации при работе с формулами;

- в визуализации данных с помощью диаграмм.

Для запуска Microsoft Excel следует выполнить команду Пуск/Все программы/Microsoft Office/Microsoft Excel (год выпуска). Главное окно программы Microsoft Excel включает в себя основные элементы, такие как лента, текущая ячейка, строка формул, номер строки, имена столбцов.

Документ, созданный в Microsoft Excel, называется рабочей книгой, поэтому по умолчанию носит название Книга 1. Рабочая книга состоит из набора рабочих листов, каждый из которых имеет имя Лист1, Лист2, ЛистЗ. Чтобы переименовать рабочий лист, надо дважды щелкнуть на его ярлыке либо нажать правой кнопкой мыши на ярлыке и выбрать пункт Переименовать.

Пространство рабочего листа состоит из строк и столбцов. На пересечении строк и столбцов образуются ячейки таблицы. 

Ячейка — это минимально адресуемый элемент рабочего листа. Адрес ячейки состоит из имени столбца и номера строки, например А1 или F14.

Группа ячеек называется диапазоном. Для того чтобы указать диапазон ячеек, достаточно выделить их мышью. При записи формул диапазон ячеек обозначается с использованием знака «двоеточие», например, А1:В5.

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

- нажатием клавиши F2;

- двойным щелчком на ячейке;

- щелчком на строке формул;

- вводом новых данных.

Выделение ячеек с помощью мыши

Удаление содержимого ячейки или диапазона ячеек происходит нажатием клавиши Delete на клавиатуре.

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

Построение любой таблицы всегда начинается с ячейки А1, после ввода значения принято нажимать клавишу Tab (курсор смещается по горизонтали) либо клавишу Enter (курсор смещается на следующую строку). При заполнении таблицы данными желательно придерживаться следующего правила: «сначала осуществить ввод данных, потом приступать к форматированию».

При вводе формул в ячейки электронной таблицы следует учитывать следующие особенности:

- формула в Excel всегда начинается со знака равенства (=);

- имена ячеек вводятся на английском языке (следует переключить раскладку клавиатуры с русского на английский регистр);

- не следует включать клавишу CapsLock;

- имена ячеек можно не набирать с клавиатуры, а выбрать нужную ячейку мышью.

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

Теперь рассмотрим такую возможность электронных таблиц, как автоматизация процесса ввода. Можно выделить несколько способов завершения операций ввода.

1. Метод автозавершения. Используется при вводе в ячейки повторяющихся данных. При вводе текста в очередную ячейку программа проверяет соответствие введенных символов данным, имеющимся в этом столбце выше. Если обнаружено совпадение, введенный текст автоматически дополняется. Нажатие клавиши Enter подтверждает операцию завершения ввода данных, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант.

2. Метод автозаполнения. Его можно продемонстрировать на простом примере. В ячейку А1 введите число 1, в ячейку А2 введите число 2. Выделите обе ячейки и поставьте указатель мыши на правый нижний угол выделенного блока. Указатель мыши превратится в черный крестик — это маркер заполнения.

Нажав левую кнопку, мы можем продолжить список далее.

3. Использование прогрессии.

Способ может использоваться для заполнения ячеек методом арифметической, геометрической прогрессии с использованием шага. Выполним следующие действия: в ячейку А1 введем число 1, в ячейку А2 введем число 2.

Выделив обе ячейки, выберем меню Главная, кнопка Запол- нить/Прогрессия.

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

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

Абсолютная адресация. Используется тогда, когда адресация ячеек в формуле не должна меняться при ее копировании.

Под термином деловая графика понимаются графики и диаграммы, наглядно представляющие динамику развития того или иного производства, отрасли и любые другие числовые данные

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

Возможности деловой графики преследуют единую цель: улучшить восприятие информации человеком, сделать ее более наглядной и выразительной.

Основной целью деловой графики, является оперативная выдача информации в графической форме в процессе анализа задачи при ее решении средствами электронной таблицы. Главным критерием является быстрота подготовки и отображения графических образов, соответствующих оперативно изменяющейся числовой информации.

В современном мире существует большое количество информации, которую трудно сразу усвоить обычному человеку. А если нужно обработать, проанализировать какие-то данные, то задача становится сложной вдвойне. В настоящее время, с помощью компьютерных технологий, можно хранить и обрабатывать любую информацию.

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

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

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

Правильно составленные схемы или графики превращают скучные цифры в наглядные изображения и помогают пользователям сориентироваться в «море» информации и принять нужное решение. Деловая графика позволяет в удобной форме сравнивать различные данные, выявлять закономерности и тенденции развития. Можно также с помощью различных графических программ решать задачи в области информационных технологий, архитектуры и инженерии. В настоящее время деловая графика прочно вошла в нашу жизнь, сейчас невозможно представить никаких сводных документов или презентаций без таблиц, схем, диаграмм, различных изображений.

Компьютерной графикой пользуются административные и технические сотрудники, студенты, а также обычные пользователи персональных компьютеров.

С помощью богатой библиотеки диаграмм Excel можно составлять диаграммы и графики разных видов: гистограммы, круговые диаграммы, столбчатые, графики и др., их можно снабжать заголовками и пояснениями, можно задавать цвет и вид штриховки в диаграммах, печатать их на бумаге, изменяя размеры и расположение на листе, и вставлять диаграммы в нужное место листа.

Ход работы.

 Задание № 1. Создайте таблицу по образцу (рис. 1)

Рис. 1

Алгоритм выполнения задания.

1. В ячейке А1 записать название таблицы.
2. В ячейках А2:Е2 записать шапки таблицы с предварительным форматированием ячеек, для этого:

1. Выделить диапазон ячеек А2:Е2.
2. Выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
3. Установить переключатель «переносить по словам».
4. В поле «по горизонтали» выбрать «по центру», ОК.
5. В поле «по вертикали» выбрать «по центру», ОК.
6. Набрать тексты шапочек, подбирая по необходимости ширину столбцов вручную.

3. Заполнить столбец А названиями предприятий, предварительно отформатировав диапазон ячеек А3:А13 по образцу ячейки В2, для этого:

1. Выделить ячейку В2.
2. Выполнить команду Формат по образцу на панели инструментов Стандартная (кнопка в виде кисточки), к указателю мыши добавится значок кисточки.
3. С нажатой левой кнопкой мыши обвести диапазон А3:А13.
4. Набрать текст с названиями предприятий, подбирая при необходимости ширину столбцов вручную.

4. Набрать цифровые данные таблицы.
5. Подсчитать итоговые данные по столбцам, используя команду Автосумма.
6. Рассортировать предприятия по разным видам показателей, для этого:

1. Выделить шапочку заголовка «Выполнение плана (в процентах)» (ячейка Е2), выполнить команду Сортировка по возрастанию (значок А/Я↓ на панели инструментов Стандартная), проверить изменение таблицы.
2. Выполнить команду Сортировка по убыванию значок (Я/А↓), проверить изменение таблицы.
3. Повторить сортировки для столбцов D, C, В, выделяя соответственно ячейки D2, С2, В2.

7. Рассортировать предприятия по алфавиту:

1. Выделить шапочку «Наименование предприятия», выполнить команду Сортировка по возрастанию (значок А/Я↓)
2. Отметить, что в середину списка предприятий попала графа ИТОГО:
3. Отменить результаты последней сортировки, щёлкнув по кнопке Отменить на панели инструментов Стандартная (закруглённая синяя стрелка).
4.  Для выполнения нормальной сортировки необходимо отделить пустой строкой итоговые данные таблицы, для этого:

1. Выделить строку 13, щёлкнув по заголовку строки.
2. Выполнить команду Вставка/Строки.

5. Провести сортировку по п. 7.1, отметить изменение таблицы.

8. Провести сортировку с помощью команды Данные/Сортировка, для этого:

1. Выполнить эту команду.
2. В диалоговом окне Сортировка диапазона установить переключатель Идентифицировать поля «по подписям».
3. В поле Сортировать по выбрать из списка нужный заголовок.
4. Установить переключатель «по возрастанию» (или «по убыванию»), ОК.

Задание № 2. Создайте таблицу с использованием Мастера функций, согласно рис. 2

Рис. 2

Алгоритм выполнения задания.

1. Записать заголовок и шапочки таблицы (ячейки А1:Н2).
2. Заполнить боковик таблицы, используя функцию Список.

1. В ячейку А3 записать Январь.
2. Выделить ячейку А3, подвести указатель мыши к правому нижнему углу ячейки, указатель примет вид тонкого чёрного креста ┼, протащить с нажатой левой кнопкой до ячейки А14 (операция Автозаполнения).

3. Заполнить четыре столбца цифровыми данными:

1. Заполнить две строки указанными на рисунке цифрами.
2. Выделить диапазон ячеек В3:Е4, выполнить операцию Автозаполнение до строки Итого.

4. Заполнить графу Итого, используя операции Автосумма и Автозаполнение.
5. Рассчитать Среднее в ячейке F3, используя команду Вставка функции.

1. Выделить ячейку F3, щёлкнуть значок fx на панели инструментов Стандартная или выполнить команду Вставка/Функция.
2. В диалоговом окне Мастер функций в левом поле Категория выбрать Статистические, в правом поле Функция найти и выбрать СРЗНАЧ, нажать ОК.
3. Появится диалоговое окно функции СРЗНАЧ с автоматически подставленным диапазоном В3:F3 в поле Число1 и подсказками, нажать ОК.

6. Заполнить столбец Среднее по Декабрь, используя операцию Автозаполнение.
7. Рассчитать Максимум в ячейке G3, используя команду Вставка функции.

1. Выделить ячейку G3, щёлкнуть значок fx на панели инструментов Стандартная или выполнить команду Вставка/Функция.
2. В диалоговом окне Мастер функций в левом поле Категория выбрать Статистические, в правом поле Функция найти и выбрать МАКС, нажать ОК.
3. Появится диалоговое окно функции МАКС с автоматически подставленным диапазоном B3:F3в поле Число1, этот диапазон неверен, для его исправления:

1. Отодвинуть диалоговое окно, захватив его левой кнопкой мыши за любое место на сером поле так, чтобы была видна строка Январь.
2. Обвести диапазон В3:Е3 с нажатой левой кнопкой мыши, при этом в поле Число1 появятся нужные адреса (можно также ввести нужные адреса с клавиатуры), нажать ОК.

8. Заполнить столбец Максимум по Декабрь, используя операцию Автозаполнение.
9. Рассчитать Минимум в ячейке Н3, используя команду Вставка функции.

1. Выделить ячейку Н3, щёлкнуть значок fx на панели инструментов Стандартная или выполнить команду Вставка/Функция. 
2. В диалоговом окне Мастер функций в левом поле Категория выбрать Статистические, в правом поле Функция найти и выбрать МИН, нажать ОК.
3. Появится диалоговое окно функции МИН с автоматически подставленным диапазоном B3:G3 в поле Число1, этот диапазон неверен, для его исправления:

1. Отодвинуть диалоговое окно, захватив его левой кнопкой мыши за любое место на сером поле так, чтобы была видна строка Январь.
2. Обвести диапазон В3:Е3 с нажатой левой кнопкой мыши, при этом в поле Число1 появятся нужные адреса (можно также ввести нужные адреса с клавиатуры), нажать ОК.

10. Заполнить столбец Минимум по Декабрь, используя операцию Автозаполнение.
11. Рассчитать строку 16 Максимум с помощью мастера функций, исправляя диапазон адресов на В3:В14 и применяя операцию Автозаполнение.
12. Рассчитать строку 17 Минимум с помощью мастера функций, исправляя диапазон адресов на В3:В14 и применяя операцию Автозаполнение.

Задание № 3. Создать и заполнить таблицу продаж, показанную на рисунке 3. Построить графики функций.

Рис. 3

Алгоритм выполнения задания.

1. Записать исходные значения таблицы, указанные на рисунке.
2. Заполнить графу Модель значениями ВАЗ2101÷2111, используя операцию Автозаполнение.
3. Построить диаграмму по всем продажам всех автомобилей, для этого:

1. Выделить всю таблицу (диапазоеА1:Е13).
2. Щёлкнуть Кнопку Мастер диаграмм на панели инструментов Стандартная или выполнить команду Вставка/Диаграмма.
3. В диалоговом окне Тип диаграммы выбрать Тип Гистограммы и Вид 1, щёлкнуть кнопку Далее.
4. В диалоговом окне Мастер Диаграмм: Источник данных диаграммы посмотреть на образец диаграммы, щёлкнуть  кнопку Далее.
5. В диалоговом окне Мастер Диаграмм: Параметры диаграммы ввести в поле Название диаграммы текст Продажа автомобилей, щёлкнуть  кнопку Далее.
6. В диалоговом окне Мастер Диаграмм: Размещение диаграммы установить переключатель «отдельном», чтобы получить диаграмму большего размера на отдельном листе, щёлкнуть  кнопку Готово.

4. Изменить фон диаграммы:

1. 4.1. Щёлкнуть правой кнопкой мыши по серому фону диаграммы (не попадая на сетку линий и на другие объекты диаграммы).
2. 4.2. В появившемся контекстном меню выбрать пункт Формат области построения.
3. 4.3. В диалоговом окне Формат области построения выбрать цвет фона, например, бледно-голубой, щёлкнув по соответствующему образцу цвета.
4. 4.4. Щёлкнуть на кнопке Способы заливки.
5. 4.5. В диалоговом окне Заливка установить переключатель «два цвета», выбрать из списка Цвет2 бледно-жёлтый цвет, проверить установку Типа штриховки «горизонтальная», щёлкнуть ОК, ОК.
6. Повторить пункты 4.1-4.5, выбирая другие сочетания цветов и способов заливки.

5. Отформатировать Легенду диаграммы (надписи с пояснениями).

1. 5.1. Щёлкнуть левой кнопкой мыши по области Легенды (внутри прямоугольника с надписями), на её рамке появятся маркеры выделения.
2. 5.2. С нажатой левой кнопкой передвинуть область Легенды на свободное место на фоне диаграммы.
3. 5.3. Увеличить размер шрифта Легенды, для этого:

1. 5.4. Щёлкнуть правой кнопкой мыши внутри области Легенды.
2. 5.5. Выбрать в контекстном меню пункт Формат легенды.
3. 5.6. На вкладке Шрифт выбрать размер шрифта 16, на вкладке Вид выбрать желаемый цвет фона Легенды, ОК.

4. 5.7. Увеличить размер области Легенды, для этого подвести указатель мыши к маркерам выделения области Легенды, указатель примет вид ↔ двунаправленной стрелки, с нажатой левой кнопкой раздвинуть область.
5. Увеличить размер шрифта и фон заголовка Продажа автомобилей  аналогично п.5.3.

6. Добавить подписи осей диаграммы.

1. 6.1. Щёлкнуть правой кнопкой мыши по фону диаграммы, выбрать пункт Параметры диаграммы, вкладку Заголовки.
2. 6.2. Щёлкнуть левой кнопкой мыши в поле Ось Х (категорий), набрать Тип автомобилей.
3. 6.3. Щёлкнуть левой кнопкой мыши в поле Ось Y (значений), набрать Количество, шт.
4. Увеличить размер шрифта подписей аналогично п.5.3.

Задание № 4. Создать таблицу продажи акций брокерской фирмы. Произвести расчеты по заданию.

Задание № 5. Создание и обработка БД в Excel. Связывание листов рабочей книги.

1.Создать в новом файле таблицу по образцу (см. рис.1). Знаки ?, указанные в некоторых ячейках, означают, что данные показатели необходимо вычислить.

2.Переименовать Лист1 на Январь.

3.Отформатировать таблицу.

4.Выполнить указанные ниже действия:

3.На листе 4 создать таблицу, изображенную на рис4.

3.1.Для добавления Листа необходимо щёлкнуть правой кнопкой мыши на ярлыке Листа и из контекстного меню выбрать команду Вставить. Во вкладке Общие выбрать Лист и нажать Ok.

3.2.В ячейку B2 ввести формулу для суммарного подсчёта проданной продукции за январь, февраль и март месяцы, а именно

=ЛИСТ1!G4+ЛИСТ2!G4+ЛИСТ3!G4, где знак ! означает связь между листами.

Для оптимизации ввода формулы необходимо проделать следующее:

1.Поместить курсор в ячейку B2 и ввести знак =.

2.Щёлкнуть на ярлыке Лист1, щёлкнуть по ячейке G4.

3.Нажать клавишу +

4.Щёлкнуть на ярлыке Лист 2, ячейке G4, нажать +.

5.Щёлкнуть на ярлыке Лист 3, ячейке G4 и нажать Enter

6.Скопировать введённую формулу на остальные ячейки.

3.3.В ячейку С2 ввести формулу для подсчёта суммарной выручки за 1 квартал (т.е. месяцы январь, февраль, март)

=ЛИСТ1!H4 + ЛИСТ2!H4+ЛИСТ3!H4

3.4.Лист 4 переименовать в Итоги продаж (товары).

4.На Листе5 создать таблицу, изображенную на рис5.

. 

4.1.Самостоятельно ввести формулы для подсчёта итоговой продажи и итоговой выручки за январь, февраль и март.

4.2.Переименовать Лист5 в Итоги продаж (месяцы).

4.3. Построить диаграммы:

-Наименование продукции и Продано

-Наименование продукции и выручка

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

1. Назначение и возможности табличного процессора Excel.
2. Перечислите и поясните основные типы входных данных, которые могут быть введены в ячейки электронной таблицы?
3. Как выполняются операции автозаполнения и копирования в электронной таблице?
4. Что такое формула в электронной таблице и её типы. Приведите примеры.
5. Что понимается под термином деловая графика?
6. Для чего предназначена деловая графика?
7. Кто пользуется деловой графикой?

Требования к оформлению практической работы.

1. Отчёт № 11.
2. Тема практической работы.
3. Ход работы, содержащий скриншоты с результатом заданий и описание тех функций, которые были использованы.
4. Ответы на контрольные вопросы.
5. Вывод о проделанной работе.