Практическая работа 2 по Excel

(Смешанные ссылки в таблицах. Использование функций в формулах, Автосуммирование,создание диаграмм)

Ссылка, относительная по одной из координат и абсолютная – по другой, называется смешанной. Смешанные ссылки используются, когда при копировании формулы может изменяться какая-то одна часть ссылки – либо буквы столбца, либо номер строки. Символ доллара ставится перед той частью ссылки, которая должна остаться неизменной. Например, $C8 или A$5).

Построение таблицы умножения

1. Открыть новый лист и назвать его Таблица умножения.

2. Составить таблицу умножения. Для этого:

« левую верхнюю ячейку таблицы оставить пустой;

« первую строку и первый столбец этой таблицы (начиная со вторых ячеек) заполнить числами от 1 до 9;

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

Создание таблицы КомПлат (расчет стоимости коммунальных платежей)

1. Открыть в книге новый лист и составить таблицу, содержащую расчет стоимости коммунальных услуг.

Столбцы таблицы: Фамилия квартиросъемщика, Количество жильцов, Жилая площадь, Наем жилья, Оплата жилья, Отопление, Холодная вода, Горячая вода, ТБО, Сумма.

Ввод значений и формул в столбцы начать с третьей строки.

2. Значения столбца Фамилия квартиросъемщикаскопировать из таблицы на листе Сотрудники.

3. Столбцы Количество жильцов и Жилая площадь заполнить произвольными значениями.

4. Ниже приведены тарифы по каждому виду платежей.

Тарифы разместить во второй строке таблицы (под «шапкой»).

Тарифы: наем жилья – 0,1 руб. за 1 кв. м; оплата жилья – 1,8 руб. за 1 кв. м; отопление – 4,2 руб. за 1 кв. м; холодная вода – 68,4 руб. за 1 чел.; горячая вода – 77,3 руб. за 1 чел.; ТБО – 7,7 руб. за 1 чел.).

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

5. Ввести формулы для расчета платежей:

- Наем жилья = тариф ´ Жилая площадь;

- Оплата жилья = тариф ´ Жилая площадь;

- Отопление = тариф ´ Жилая площадь;

- Холодная вода = тариф ´ Количество жильцов;

- Горячая вода = тариф ´ Количество жильцов;

- ТБО = тариф ´ Количество жильцов.

6. Колонка Суммарассчитывается как сумма всех видов платежей. Использовать кнопку Автосумма (вкл. Формулы – группа Библиотека функций).

7. Добавить итоговую строку, содержащую суммы по каждому виду платежа для всех квартиросъемщиков (щелкнуть по кнопке Вставить функцию, которая находится левее строки формул, выбрать категорию Математическиеи функцию Сумм).

8. Отформатировать таблицу (вкл. Главная – гр. Выравнивание – кнопка в нижнем углу Формат ячеек: выравнивание):

- для ячеек «шапки» таблицы задать вертикальную ориентацию текста; текст по вертикали и горизонтали расположить по центру; выполнить перенос текста на новую строку при достижении правого края ячеек (флажок «переносить по словам»); задать жирный шрифт, светло-серый фон;

- ячейкам, содержащим денежные величины, назначить денежный формат;

- оформить таблицу красной рамкой, столбцы разделить вертикальными линиями, «шапку» отделить горизонтальной линией, строки таблицы не разделять линиями.

9. Отменить вывод на экран сетки таблицы (ком-да Вид –кн. Показать или скрыть).

10. Отобразить в ячейках таблицы формулы. Вернуться к отображению в ячейках значений формул (ком-да Формулы – Зависимости формул).

Создание диаграмм

Диаграмма – это графическое представление данных рабочего листа.

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

Чтобы диаграмма выводилась на экран вместе со связанными с ней данными, надо создать внедренную диаграмму.

Чтобы диаграмма занимала отдельный лист рабочей книги, надо создать лист диаграмм.

Диаграммы строятся с помощью Мастера диаграмм, представляющего собой ряд диалоговых окон, в которые вводятся данные, необходимые для построения диаграмм.

Элементы диаграммы

В диаграммах различают следующие элементы.

Ряд данных – строка или столбец, содержащие данные для построения диаграммы. Большинство типов диаграмм могут изображать несколько рядов.

Точка данных – отдельное значение ряда данных (данные, содержащиеся в одной ячейке).

Категории – надписи по оси x (числа или текст).

Маркер данных – графическое изображение значения ряда данных (столбик, точка и т. д.).

Подписи данных – численное отображение на диаграмме значения ряда данных.

Легенда – название ряда данных (необходимо при изображении нескольких рядов на одной диаграмме).

Ось x, Ось y.

Названия осей.

Название диаграммы.

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

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

Выделение блока ячеек для построения диаграммы

Для построения диаграммы необходимо выделить данные на рабочем листе: ряды, категории, легенды.

Ориентация данных может быть горизонтальной или вертикальной.

Выделенный блок ячеек должен быть прямоугольным (левая верхняя ячейка тоже должна быть выделена).

Способы создания диаграмм

I способ

1. Выделить диапазон данных для построения диаграммы.

2. На ленточной вкладке Вставка в группе Диаграммы щелкнуть по кнопке с нужным типом диаграмм.

3. В открывшемся списке выбрать диаграмму нужного вида.

II способ

1. На ленточной вкладке Вставка в группе Диаграммы щелкнуть по кнопке с нужным типом диаграмм.

2. На ленточной вкладке Работа с диаграммами ‑ Конструктор в группе Данные щелкнуть по кнопке Выбрать данные.

3. В открывшемся диалоговом окне в поле Диапазон данных для диаграммы указать диапазон ячеек с данными для диаграммы.

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

5. Проверить и при необходимости изменить категории, используя кнопку Изменить в поле Подписи горизонтальной оси (категории).

6. Проверить и при необходимости изменить легенды, используя кнопки Добавить, Изменить, Удалить в поле Элементы легенды (ряды).

7. Щелкнуть по кнопке ОК.

11. Построить внедренную гистограмму: в качестве рядов использовать данные из столбцов Оплата жилья, Отопление, Холодная вода, Горячая вода, ТБО, в качестве категорий – данные из столбца Фамилия квартиросъемщика. Для этого:

Ø выделить все данные из указанных столбцов, начиная с третьей строки. Итоговую строку не выделять;

Ø вкл. Вставка –гр. Диаграммы – кнопка Гистограмма – Гистограмма с группировкой;

Ø добавить названия рядов (вызвать контекстное меню диаграммы – Выбрать данные – в окне Элементы диаграммы (ряды)для каждого из рядовнажимать кнопкуИзменитьи в поле Имя ряда вводить адрес ячейки, содержащей название ряда);

Ø задать заголовки диаграммы и осей Х и У – соответственно «Коммунальные платежи», Фамилии жильцов», «Платежи» (Работа с диаграммами – Макет).

12. Присвоить листу имя КомПлат.

13. Сохранить книгу (файл).

14. Ответить на контрольные вопросы.