Лабораторная работа №2. создание таблиц и построение диаграмм

      Комментарии к записи Лабораторная работа №2. создание таблиц и построение диаграмм отключены

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

В Excel существует два способа адресации ячеек: относительный и абсолютный.

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

Абсолютная адресация — это дополнительный способ адресации, позволяющий при любом изменении структуры рабочего листа, а также при копировании или перемещении ячеек с формулами и исходными данными полностью или частично зафиксировать адреса ячеек или блоков, на которые выполняется ссылка. Абсолютный адрес формируется из относительного добавлением символа доллара $ к нужной компоненте адреса ячейки или блока, например: $A9, B$7, $C$5, $D$3:$F$10.

ЗАДАНИЕ 1. Абсолютная и относительная адресация ячеек

1. Запустите программу Microsoft Excel.

2. Переименуйте Лист1 рабочей книги в Итоги продаж.

3. Наберите таблицу по образцу, начав с ячейки А1:

Наименование Цена Количество Стоимость Процент
Марс 6,5
Сникерс 7,5
Баунти 6,8
Пикник
Твикс 8,5
Шок
ИТОГО:

Важно!

После того, как вы введете число, убедитесь, что оно соответствует образцу. Если это не так, значит у вас либо ошибочный десятичный знак (вместо запятой — точка), либо другой формат ячейки. Для изменения формата ячейки выберите в меню Формат/Ячейки, вкладку Число и измените формат на Числовой.

4. Установите курсор в ячейку D2 и наберите формулу = B2*C2 (стоимость=цена*количество). В ячейке D2 получился числовой результат (975).

5. Для вычисления стоимости в остальных ячейках таблицы можно продолжать набирать формулы, но проще скопировать формулу из ячейки D2 на нижележащие ячейки. Для этого можно воспользоваться маркером автозаполнения или выделить ячейки D2-D7 и воспользоваться командой меню Правка/Заполнить/Вниз.

Важно!

При копировании формул происходит автоматическое изменение адресов ячеек. При этом копирование на нижележащую строку приводит к автоматическому увеличению номера строки в адресах ячеек, а копирование вправо – к автоматическому увеличению номера столбца.

6. Установите курсор в ячейку D8 и найдите на панели инструментов кнопку Автосумма (?). Щелкните по ней. В строке формул появится формула =СУММ(D2:D7), а указанные ячейки будут обведены пунктиром. Нажмите Enter. В ячейке появится итоговая стоимость шоколадных батончиков.

7. Подсчитаем, какой процент общей стоимости будет составлять стоимость отдельных товаров. Ссылка на ячейку D8 должна быть абсолютной, поэтому ее адрес будем записывать $D$8. В ячейке E2 запишите формулу =D2/$D$8. Нажмите Enter.

Важно!

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

8. Скопируйте формулу в ячейки E3-E8.

9. Выделите ячейки E2-E8 и в меню Формат/Ячейки выберете строку Процентили нажмите кнопку (%) на кнопочной панели Форматирование. Нажмите ОК.В ячейках отобразятся проценты (в Е8 должно получиться 100%).

ЗАДАНИЕ 2. Форматирование таблицы

1. Озаглавьте составленную таблицу. Для этого вставьте пустую строку выше таблицы (при выделенной первой строке выполните команду Вставка/Строки).

2. В ячейку А1 введите текст Итоги продаж шоколадных батончиков за ноябрь 2005 г.

3. Отцентрируйте введенный текст по ширине составленной таблицы. Для этого выделите блок ячеек А1-Е1 и нажмите кнопку Объединить и поместить в центре кнопочной панели Форматирование .

4. Скопируйте таблицу вместе с заголовком на Лист 2 рабочей книги.

5. Переименуйте Лист 2 в Копию итогов продаж.

6. Перейдите на лист Итоги продаж. Выделите таблицу без заголовка. Выполните команду меню Формат/Автоформат. Выберите понравившийся вам стиль оформления таблицы.

7. Выделите заголовок. Обратите внимание на то, что группа ячеек А1-Е1 выделяется как единая ячейка, поэтому все приемы форматирования будут относиться ко всему выделению. Установите размер шрифта 14 пт, жирный, курсив, цвет — темно-синий, заливка — светло-желтая. Контур — двойная линия темно-синего цвета. (Используйте команду меню Формат / Ячейки,либо кнопки форматирования на кнопочной панели.)

8. Если текст заголовка не помещается по ширине в ячейку, выполните команду Формат/Ячейки, перейдите на закладку Выравнивание, установите флажок Переносить по словам. Выберите варианты размещения текста по горизонтали и вертикали — По центру. Если часть текста заголовка по-прежнему не отображается в ячейке, увеличьте высоту первой строки таблицы протяжкой за разделительную линию между ярлычками первой и второй строки.

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

ЗАДАНИЕ 3. Построение диаграмм

Отображение данных таблиц в виде диаграмм является важным аспектом использования электронных таблиц, так как диаграммы позволяют сделать результаты расчетов более наглядными. Существует большое количество типов диаграмм, которые можно отобразить средствами MS Excel. Тип выбранной диаграммы зависит от того, что вы хотите отобразить. Наиболее популярными являются столбиковые диаграммы (гистограммы), которые используются при сравнении показателей, и круговые диаграммы, которые отображают итоги.

Построим диаграмму, на которой отобразим количество проданных шоколадных батончиков каждого вида.

1. Выделите ячейки А3-С8 таблицы. Щелкните на кнопке Мастер диаграмм или выполните команду Вставка/Диаграмма. Откроется окно Мастер диаграмм (шаг 1 из 4).

2. Выберите тип диаграммы — Гистограмма. Выберите вид диаграммы: Объемный вариант обычной гистограммы и нажмите кнопку Далее.

3. На втором шаге уточняется диапазон исходных данных. Т.к. в диапазон попал неинформативный столбец Цена за единицу продукции, то на этом шаге его можно удалить из диаграммы. Для этого перейдите на закладку Ряд. В поле Ряд выделите Ряд1 и нажмите кнопку Удалить. Обратите внимание на то, как изменился внешний вид диаграммы. Перейдите на следующий шаг Мастера диаграмм.

4. На шаге 3 уточняется оформление диаграммы. На закладке Заголовки в поле Название диаграммы введите Итоги продаж за ноябрь 2005 г.

5. На закладке Легенда снимите флажок Добавить легенду.

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

7. Ознакомьтесь с остальными закладками. Нажмите кнопку Далее.

8. На последнем шаге выберите место размещения диаграммы: Отдельный лист. Нажмите кнопку Готово. Диаграмма появится на отдельном листе.

9. Переименуйте лист Диаграмма1 в Диаграмма продаж.

10. Можно отформатировать элементы диаграммы. Для этого по выбранному элементу диаграммы щелкните правой кнопкой мыши и в контекстном меню выберите команду Формат… Обратите внимание на стиль выделения столбиков диаграммы: они выделяются все вместе! Чтобы выделить отдельный столбик, щелкните по нему сначала левой кнопкой мыши, а затем правой. Измените цвет столбиков диаграммы по своему усмотрению.

11. Измените цвет стенок диаграммы.

12. Измените подписи столбиков данных (цифры сверху): жирный, 16 пт, выравнивание по вертикали, рамка с заливкой белого цвета.

13. Измените оформление заголовка диаграммы: рамка с тенью, заливка, шрифт.

14. Выполните форматирование других элементов диаграммы.

15. Выполните команду меню Диаграмма/Объемный вид. Поэкспериментируйте с параметрами объема диаграммы.

16. Перейдите на лист Итоги продаж. Для построения круговых диаграмм необходимо выделить два отдельных блока ячеек А3-А8 и D3-D8. Это можно сделать следующим образом: выделить блок А3-А8, затем, нажав клавишу Ctrl и удерживая её, выделить блок D3-D8.

17. Запустите Мастер диаграмм. На первом шаге выберите вариант Круговая диаграмма. Самостоятельно постройте круговую диаграмму на отдельном листе и отформатируйте её. Лист переименуйте в Круговая диаграмма.

18. Самостоятельно постройте еще две диаграммы: С областями и График. Разместите их на листе Итоги продаж.

Важно!

Если диаграмма не подошла по внешнему виду, можно выделить ее и нажать клавишу Delete, а затем снова запустить Мастер диаграмм. Можно изменить размер диаграммы, для этого нужно выполнить протяжку с нажатой левой кнопкой мыши за чёрные квадратные маркеры рамки диаграммы.

19. Сохраните рабочую книгу в своей папке на диске.

ЗАДАНИЕ 4. Создание таблицы с расчетами в Excel

1. В рабочей книге сделайте активным чистый лист или вставьте новый лист. Переименуйте его в Самолеты.

Заполните все клетки таблицы по образцу.

Страны Процент Количество Реактивные Турбо- винтовые Поршневые
13% 70%
Мексика 20%
Аргентина 7%
Венесуэла 6%
Чили 5%
Колумбия 3%
Перу 2%
Остальные страны
Общее число самолетов

Важно!

При создании сложного заголовка используйте приемы объединения ячеек, например для создания заголовка Страны нужно выделить ячейки А1 и А2, выполнить команду Формат / Ячейки / Выравнивание, а затем установить флажок Объединить ячейки.

Заполните формулами все свободные клетки таблицы.

2. Если количество самолетов получится дробным, то в меню Формат/Ячейки необходимо выбрать формат числа без десятичных знаков, тогда числа округлятся до целых.

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

4. Постройте на отдельном рабочем листе диаграмму, отображающую для каждой страны количество самолетов каждого типа.

5. Сохраните работу.

Статьи к прочтению:

Excel: как построить график функции или диаграмму в Эксель


Похожие статьи: