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

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

Цель работы: Освоение технологии удаления и переименова­ния листов, создания формул, имеющих ссылки на ячейки друго­го листа Рабочей книги. Закрепление навыков работы с Мастером диаграмм.

Общие сведения

Листы книги Excel можно переименовывать, добавлять, уда­лять, вставлять и т.д. Для переименования Рабочего листа необхо­димо нажать правую кнопку мыши на его ярлычке и в появив­шемся контекстном меню выбрать команду Переименовать. Затем удалить старое имя, ввести новое и нажать клавишу [Enter].

С помощью контекстного меню можно также удалять, встав­лять, копировать листы.

Установка   связей   между   Рабочими листами

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

•  создайте на Рабочих листах требуемые таблицы;

•  выделите ячейку, в которую будет копироваться значение, наберите знак «=»;

•  перейдите на лист, в котором находится нужное значение, выберите нужную ячейку и нажмите клавишу [Enter];

•  в строке формул должна появиться формула, в которой за именем листа следует восклицательный знак, а перед буквой стол­бца и номером строки стоит знак «$», например: =Лист2!$Б$12.

Построение   диаграмм

Диаграмма — это удобное средство графического представле­ния данных. Создать диаграмму легче всего с помощью Мастера диаграмм.

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

действия:

• подготовьте лист со столбцами и строками, снабдите их над­писями, которые впоследствии появятся на диаграмме;

•  выделите диапазон ячеек с данными:

•  нажмите кнопку Мастер диаграмм

•  откроется окно Мастер диаграмм, в котором предлагается выбрать тип диаграммы (рис. 3.23). После выбора нажмите кнопку Далее;

Рис. 3.23. Первое окно Мастер диаграмм

•  в следующем окне предлагается выделить ячейки листа, вклю­чаемые в диаграмму. Нажмите кнопку Далее, так как диапазон ячеек уже определен;

•  откроется окно, в котором определяются внешний вид диа­граммы, названия легенды и подписей. После ввода этой инфор­мации нажмите кнопку Далее;

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

•  завершение построения диаграммы выполните нажатием кноп­ки Готово.

Созданную диаграмму можно масштабировать, форматировать, перемещать и удалять:

•  для масштабирования следует установить указатель мыши на край диаграммы и щелкнуть левой кнопкой мыши — диаграмма будет выделена. Теперь для изменения ее размеров можно исполь­зовать один из маркеров.

•  для форматирования следует сделать по диаграмме двойной щелчок. Откроется окно диалога «Формат области диаграммы», куда можно ввести необходимые изменения.

•  для удаления необходимо выделить диаграмму и нажать кла­вишу [Delete].

Порядок работы

1. Подготовьте ведомость на выдачу заработной платы (приве­денные данные условные). Поскольку в дальнейшем будем рабо

                        Рис. 3.24. Примерный вид таблицы на листе «Начисления»

тать сразу с несколькими Рабочими листами, имеет смысл пере­именовать их ярлычки в соответствии с содержимым.

1  лист — «Начисления» (Сведения о начислениях);

2  лист — «Диаграмма»;

3  лист — «Детские» (Ведомость на выдачу компенсации на де­тей);

     4 лист — «К выдаче» (Ведомость на выдачу заработной платы);
2. Создайте таблицу на листе «Начисления» (рис. 3.24). Для это­
го выполните следующие действия:

•  запустите Excel;

•  сформируйте строки заголовка;

•  измените ширину столбца (в зависимости от объема вводи­мой информации);

•  выполните обрамление таблицы;

•  определите формат числа «Денежный» для ячеек, содержа­щих суммы. Данное действие можно выполнить до ввода данных в таблицу;

•  заполните ячейки столбца последовательностью чисел 1, 2...;

•  введите формулы в верхнюю ячейку столбца;

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

•  заполните таблицу текстовой и фиксированной числовой ин­формацией;

•  выполните сортировку строк;

•  рассчитайте величину профсоюзных и пенсионных взносов, исходя из того, что каждый из них равен 1 % от оклада;

•  рассчитайте подоходный налог по формуле: 13 % от оклада за вычетом минимальной заработной платы и пенсионного налога.

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

• заполните столбцы «ФИО», «Оклад» и «Число детей», после
того как введены все формулы. Результат будет вычисляться сразу же после ввода данных в ячейки. При желании воспользуйтесь режимом Формы для заполнения таблицы.

3. Постройте диаграмму на основе готовой таблицы:

• выделите заполненные данными ячейки таблицы, относящиеся к столбцам «ФИО» и «Сумма к выдаче»;

Рис. 3.25. Примерный вид диаграммы                                                Рис. 3.26. Примерный вид таблицы начислений

• запустите Мастер диаграмм и, передвигаясь по шагам, со­здайте диаграмму в соответствии с рис. 3.25.

3. Создайте ведомость на получение компенсации на детей на основе таблицы начислений (рис. 3.26):

•  перейдите к листу «Детские»;

•  сформируйте заголовки таблицы;

•  в столбец «ФИО» поместите список сотрудников, который имеется на листе «Начисления», для этого установите связь меж­ду листами. Сначала перейдите на лист «Детские» и в первую ячейку столбца «ФИО» введите знак «=», символизирующий ввод фор­мулы. Затем перейдите на лист «Начисления», выделите первую ячейку в столбце «ФИО» и нажмите клавишу [Enter]. Активным станет лист «Детские», где отобразится первая фамилия в списке. Для переноса всех остальных фамилий выполните копирование формулы по столбцу «ФИО» на листе «Детские».

Список фамилий теперь есть и на листе «Детские». Если внести новые данные в таблицу начислений, они отразятся и на листе «Детские». Нужно будет только распространить формулу далее:

• в графе «Сумма» аналогичным образом разместите формулу

=Начисления!НЗ*57,

где НЗ — адрес первой ячейки на листе «Начисления», содержа­щей число детей; 57 — пособие на одного ребенка;

• скопируйте эту формулу вниз и примените формат числа «Денежный»;

•выполните обрамление таблицы.

4. Создайте ведомость на вы­дачу заработной платы. Оформите лист «К выдаче» в соответствии с рис. 3.27, в котором будут отображаться «ФИО» сотрудников, а также «Сумма к выдаче» и «Подпись».                                       

5. Сохраните результат работы в файле с именем «Work3.xls».

Рис. 3.27. Примерный вид ведомости на выдачу заработной платы    

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

1. Как осуществляется переход между Рабочими листами книги?

2.     Как удалить лист из Рабочей книги?

3.     Какие способы переименования Рабочего листа вы знаете?

4.    Как устанавливаются связи между Рабочими листами?

5.     Как происходит копирование формул?

6.    Что нужно сделать, чтобы при операции копирования не происхо­дила автоматическая смена адреса?

7.     Для чего нужны диаграммы?

8.     Как можно изменить размер диаграммы?

9.     Как удалить диаграмму?