ПРАКТ Excel. Практическая работа 5. Создание электронной книги. Относительная и абсолютная адресация в Microsoft Excel

• Название: Практическая работа 5. Создание электронной книги. Относительная и абсолютная адресация в Microsoft Excel
• Дата: 01.03.2023
• Размер: 1.57 Mb.
• Имя файла: ПРАКТ Excel.doc
• Тип: Практическая работа; #962247
• страница: 1 из 3

Практическая работа №5. Создание электронной книги. Относительная и абсолютная адресация в Microsoft Excel.

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

Оборудование и ПО: ПК, ОС Windows, Microsoft Excel 2010.

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

Исходные данные представлены на рис. 5.1., результаты работы-на рис. 5.6.

Порядок выполнения работы

1.Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

2.Создайте таблицу расчет заработной платы по образцу (см. рис. 5.1.). Введите исходные данные – Табельный номер, ФИО и оклад, % Премии=27%, % Удержания=13%.

Примечание. Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).


Рисунок 5.1 - Исходные данные для Задания 5.1.
Произведите расчеты во всех столбцах таблицы.

При расчете Премии используется формула Премии = Оклад х % Премии, в ячейке D5 наберите формулу = $D$4* C5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символов $ c клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено» = Оклад+ Премия.

Всего начислено= Оклад+ Премия.

При расчете Удержания используется формула

Удержание= Всего начислено х % Удержания,

для этого в ячейке F5 наберите формулу = $F$4 * E5.

Формула для расчета столбца « К выдаче»:

К выдаче = Всего начислено – Удержания.

3.Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/категория- Статические функции).

4.Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать

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

Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.


Рисунок 5.2 - Итоговый вид таблицы расчета заработной платы за октябрь

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/ Переместить/ Скопировать лист). Можно воспользоваться командой Переместить/ Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создавать копию (рис. 5.3.).

Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).



Рисунок 5.3 - Копирование листа электронной книги
6.Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32%. Убедитесь, что программа произвела перерасчет формул.

7. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Вставка/Столбец) и рассчитайте значение доплаты по формуле Доплата = Оклад х % Доплаты. Значение доплаты примите равным 5%.

8. Измените формулу для расчета значений колонки «Всего начислено»:

Всего начислено= Оклад+ Премия + Доплата.

9. Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10 000 – зеленым цветом шрифта; меньше 7000 – красным; больше или равно 10000 0 синим цветом шрифта (Формат/Условное форматирование) (рис 5.4.).







Рисунок 5.4 - Условное форматирование данных
10. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент с 5 по 18 строки таблицы – без итогов, выберите меню Данные/Сортировка, сортировать по- Столбец В) (рис. 5.5.)



Рисунок 5.5 - Сортировка данных
11. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен на рис. 5.6.



Рисунок 5.6 - Конечный вид зарплаты за ноябрь

12. Защитите лист «Зарплата ноябрь» от изменений (Сервис/Защита/Защитить лист). Задайте пароль лист (рис. 5.7.), сделайте подтверждение пароля (рис. 5.8.)



Рисунок 5.7 - Защита листа электронной книги
Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа (Сервис/Защита/Снять защиту листа).



Рисунок 5.8 - Подтверждение пароля
13. Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.
Дополнительные задания

Задание 1. Сделать примечания к двум-трем ячейкам.

Здание 2. Выполнить условное форматирование склада и премии за ноябрь месяц:

до 2000 р. – жёлтым цветом заливки;

от 2000 до 10000 р. – зеленым цветом шрифта;

свыше 10000 р. – малиновым цветом заливки, белым цветом шрифта.

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

Проверьте защиту. Убедитесь в неизменяемости данных. Снимите защиту со всех листов электронной книги «Зарплата»

Задание 4. Построить круговую диаграмму начисленной суммы к выдаче со всех сотрудников за ноябрь месяц.

Практическая работа №6. Связанные таблицы. Расчёт промежуточных итогов в таблицах MS Excel.

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

Оборудование и ПО: ПК,ОС Windows, MS Excel 2010.

Продолжительность: 1 час 30 минут.
Порядок выполнения работы

1. 
   Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в практической работе 5 файл «Зарплата».
   
2. 
   Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги (Правка/ Переместить/ Скопировать лист).
   

Не забудьте для копирования поставить галочку в окошке Создать копию.

3. 
   Присвойте скопированному листу название «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь.
   

Рисунок 6.1 - Ведомость зарплаты за декабрь.

4. 
   Измените значение Премии на 46%, Доплаты – на 8%. Убедитесь, что программа произвела пересчёт формул (рис. 6.1).
   
5. 
   По данным таблицы «Зарплата декабрь» постройте гистограмму доходов сотрудников. Проведите форматирование диаграммы. Конечный вид гистограммы приведен на рис. 6.2.
   

Рисунок 6.2 - Гистограмма зарплаты за декабрь

6. 
   Перед расчётом итоговых данных за квартал проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь – декабрь.
   
7. 
   Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/ Переместить/ Скопировать лист). Не забудьте для копирования поставить галочку в окошке Создавать копию.
   
8. 
   Присвойте скопированному листу название « Итоги за квартал». Измените название таблицы на «Ведомость начисления заработной платы за 4 квартал».
   
9. 
   Отредактируйте лист «Итоги за квартал» согласно образцу на рис. 6.3. Для этого удалите в основной таблице (см. рис. 6.1) колонки Оклада и Премии, а также строку 4 с численным значением % Премии и % Удержания и строку 19 «Всего». Удалите также строки с расчётом максимального, минимального и среднего доходов под основной таблицей. Вставьте пустую третью строку.
   
10. 
    Вставьте новый столбец «Подразделение» ( Вставка/ Столбец) между столбцами «Фамилия» и «Всего начислено». Заполните столбец «Подразделение» данным по образцу (см. рис. 6.3).
    
11. 
    Произведите расчёт квартальных начислений, удержаний и суммы к выдаче как сумму начислений за каждый месяц (данные по месяцам располагаются на разных листах электронной книги, поэтому к адресу ячейки добавится адрес листа).
    

Краткая справка. Чтобы вставить в формулу адрес или диапазон ячеек с другого листа, следует во время ввода формулы щелкнуть по закладке этого листа и выделить на нем нужные ячейки. Вставляемый адрес будет содержать название этого листа. В ячейке D5 для расчета квартальных начислений «Всего начислено» формула имеет вид




Рисунок 6.3 – Таблица для расчёта итоговой квартальной заработной платы.
=

’Зарплата декабрь’F5 + ‘Зарплата ноябрь’Е5 + ’Зарплата октябрь’Е5.

Аналогично произведите квартальный расчёт «Удержания» и «К выдаче».

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

12. 
    В силу однородности расчётных таблиц зарплаты по месяцам для расчёта квартальных значений столбцов «Удержание» и «К выдаче» достаточно скопировать формулу из ячейки D5 в ячейке Е5 и F5 (рис. 6.4).
    

Для расчёта квартального начисления заработной платы для всех сотрудников скопируйте формулы в столбцах D,EиF. Ваша электронная таблица примет вид, как на рис. 6.4.

13. 
    Для расчёта промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений – по фамилиям. Таблица примет вид, как на рис. 6.5.
    
14. 
    Подведите промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Итоги (рис. 5.6). Задайте параметры подсчёта промежуточных итогов:
    

при каждом изменении в – Подразделение;

операция – Сумма;

добавить итоги по: Всего начислено, Удержания, К выдаче.



Рисунок 6.4 - Расчёт квартального начисления заработной платы, связыванием листов электронной книги.



Рисунок 6.5 - Вид таблицы начисления квартальной заработной платы после сортировки по подразделениям.

Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».

Примерный вид итоговой таблицы представлен на рис. 6.7.

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

Краткая справка. Под структурирование понимается многоуровневая группировка строк и столбцов таблицы и создание таблицы и создание элементов управления, с помощью которых легко можно скрывать и раскрывать эти группы.



Рисунок 6.6 - Окно задания параметров расчёта промежуточных итогов.


Рисунок 6.7 - Итоговый вид таблицы расчёта квартальных итогов по зарплате.

16. 
    Сохраните файл «Зарплата» с произведенными изменениями (Файл/Сохранить).
    

Дополнительные задания.

Задние 1. Исследовать графическое отображение зависимостей ячеек друг от друга.

Порядок выполнения работы

Скопируйте содержимое листа «Зарплата октябрь» на новый лист. Копии Присвойте имя «Зависимость». Откройте панель «Зависимости» (Сервис/Зависимости/Панель зависимостей) (рис. 6.8). Изучите назначение инструментов панели, задерживая на них указатель мыши.

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

Рисунок 6.9 - Зависимости в таблице расчёта зарплаты
Практическая работа №7. Подбор параметра. Организация обратного расчёта.

Цель занятия. Изучение технологии подбора параметра при обратных расчётах.

Оборудование и ПО: ПК,ОС Windows, MS Excel 2010.

Продолжительность: 1 час 30 минут.

Задание 1. Используя режим подбора параметра, определить, при каком значении %Премии общая сумма заработной платы за октябрь будет равна 25 000 р. (на основании файла «Зарплата», созданного в практических работах 5…6).

Краткая справка.К исходным данным этой таблицы относятся значения Оклада и %Премии, одинакового для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчётов. Использование операции «Подбор параметра» в MS Excel позволяет производить обратный расчёт, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчёта.

Порядок выполнения работы

1. 
   Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в Практической 5…6 файл «Зарплата».
   
2. 
   Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги (Правка/Переместить/Скопируйте лист).
   

Не забудьте для копирования поставить галочку в окошке Создавать копию. Присвойте скопированному листу имя «Подбор параметра».

3. 
   Осуществите подбор параметра командой Сервис/ Подбор параметра (рис. 7.1).
   
4. 
   В диалогов окне Подбор параметра на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G19), на второй строке наберите заданное значение 250 000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку ОК. В окне Результат подбора параметра дайте подтверждение подобранному параметру нажатием кнопки ОК (рис. 7.2).
   

Произойдет обратный пересчёт %Премии. Результаты подбора (рис. 7.3): если сумма к выдаче равна 250 000 р., то % Премии должен быть 203 %.



Рисунок 7.1 - Задание параметров подбора параметра.



Рисунок 7.2 - Подтверждение результатов подбора параметра.



Рисунок 7.3 - Подбор значения % Премии для заданной общей суммы заработной платы, равной 250 000р.

Задание 2. Используя режим подбора параметра, определить штатное расписание фирмы. Исходные данные приведены на рис. 7.4.