Презентация. Лабораторная работа № 5 ТЕМА: Создание электронной книги. Относительная и абсолютная адресация в MS Excel.

Кара-Балтинский технико-экономический колледж имени М.Т. Ибрагимова

Дисциплина: Информационные технологии в профессиональной деятельности. Преподаватель: Долженко Р.П.

Кара-Балтинский технико-экономический колледж имени М.Т. Ибрагимова

Лабораторная работа № 5 ТЕМА: Создание электронной книги. Относительная и абсолютная

адресация в MS Excel.

Тема: «Создание электронной книги. Относительная и абсолютная адресация в MS Excel»

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

Задание 1. Повторить правила техники безопасности:

• Следуйте порядку включения и выключения компьютера, чтобы не привести его в нерабочее состояние.
• Во время работы не ударяйте по клавиатуре, не кладите книги и тетради на клавиатуру.
• Во время работы не присоединяйте и не отсоединяйте кабели, не трогайте разъёмы, провода и розетки.
• Если компьютер не реагирует на ваши команды, обратитесь к преподавателю. Не старайтесь самостоятельно устранить неполадки.
• Прекращайте работу при появлении необычного звука, запаха или самовольного выключения ПК и немедленно сообщите об этом преподавателю.

" — больше, "=" — больше или равно, "" — не равно. " width="640"

Задание 2. Ознакомиться с теоретической частью лабораторной работы.

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

В формулах различают арифметические операции и операции отношений. Excel допускает арифметические операции "+" — сложение, "-" — вычитание, "*" — умножение,"/" — деление, "^" — возведение в степень; операции отношений: "" — больше, "=" — больше или равно, "" — не равно.

Адрес ячейки включает имя колонки и номер строки.

Адреса ячеек (ссылки на ячейки) можно использовать в формулах.

Возможны относительные, абсолютные и смешанные ссылки.

Ссылка, которая включает имя колонки и номер строки, является относительной.

При копировании формулы, а также редактировании листа такая ссылка будет модифицироваться.

В абсолютных ссылках перед именем колонки и номером строки стоит символ $.

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

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

В строке состояния выводится краткое описание функции.

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

ƒ Х

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

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

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

Задание 3

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

Исходные данные представлены на рис. 9.1, результаты работы – на рис. 9.6

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

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

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

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

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

ВЕДОМОСТЬ НАЧИСЛЕНИЯ ЗАРАБОТНОЙ ПЛАТЫ

ЗА ОКТЯБРЬ 2019 Г

Табель

Фамилия И О

ный номер

200

Оклад (сом)

201

Аббас кызы Ж.

Премия (сом)

202

4500,00

Ануфриева И.

27%

всего начислено (сом)

203

4850,00

Ачакеева М.

?

Удержано (сом)

204

5200,00

Бохан В.

?

?

К выдаче (сом)

13%

5550,00

Дженгызыкызы М.

205

?

?

?

206

5900,00

Джумакадыр к. А.

?

?

?

?

207

Кадыркулов Н.

6250,00

?

?

?

?

208

Куцева Н.

6600,00

?

?

?

?

209

Николенко А.

6950,00

?

?

?

?

7300,00

Сатыбалдиев К.

?

?

210

?

?

?

7650,00

?

211

Сайидов О.

?

?

?

?

8000,00

?

?

212

ТажибаеваА.

?

?

?

?

8350,00

213

Шамуза Р.

?

?

?

8700,00

?

Щербакова М.

?

?

?

9050,00

?

?

?

?

?

Максимальный доход

?

?

?

Минимальный доход

?

?

?

Средний доход

?

?

?

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

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

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

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

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

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

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

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

ВЕДОМОСТЬ НАЧИСЛЕНИЯ ЗАРАБОТНОЙ ПЛАТЫ

ЗА ОКТЯБРЬ 2013 Г

Табельный номер

Фамилия И О

200

Оклад (сом)

201

Аббас кызы Ж.

Премия (сом)

202

4500,00

Ануфриева И.

всего начислено (сом)

27%

203

4850,00

Ачакеева М.

1215,00

Удержано (сом)

204

5200,00

Бохан В.

5715,00

1309,50

К выдаче (сом)

13%

Дженгызыкызы М.

205

5550,00

6159,50

1404,00

742,95

206

Джумакадыр к. А.

5900,00

6604,00

1498,50

4972,05

800,74

207

6250,00

Кадыркулов Н.

7048,50

1593,00

5358,77

858,52

208

Куцева Н.

6600,00

7493,00

1687,50

916,31

5745,48

6950,00

Николенко А.

209

1782,00

7937,50

974,09

6132,20

7300,00

Сатыбалдиев К.

210

1876,50

8382,00

6518,91

1031,88

8826,50

1971,00

7650,00

1089,66

211

6905,63

Сайидов О.

9271,00

1147,45

8000,00

7292,34

2065,50

212

ТажибаеваА.

9715,50

7679,06

1205,23

8350,00

2160,00

213

Шамуза Р.

8065,77

1263,02

10160,00

2254,50

8700,00

Щербакова М.

8452,49

10604,50

1320,80

2349,00

9050,00

Всего

8839,20

1378,59

11049,00

2443,50

94850,00

Максимальный доход

9225,92

1436,37

11493,50

25609,50

Минимальный доход

9612,63

120459,50

1494,16

9999,35

Средний доход

9999,35

15659,74

4972,05

104799,7

7485,70

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

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

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

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

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

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

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

Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000 – зеленым цветом шрифта, меньше 7000 – красным, больше или равно 10000 - желтым цветом шрифта.

Поставьте в ячейке D3 комментарии «Премия пропорциональна окладу», при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. ( панель Рецензирование – Создать примечание) (рис. 9.5)

Защитите лист «Зарплата ноябрь» от изменений ( панель Рецензирование – Защитить лист). Задайте пароль на лист Рис. 9.6. Сделайте подтверждение пароля.

Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа. ( панель Рецензирование – Снять защиту листа)

Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.

Дополнительные задания к заданию №3

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

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

До 5000 с – желтым цветом

От 5000 до 7000 с- зеленым цветом шрифта

Свыше 7000 с – красным цветом шрифта.

Задание 3.3 Построить круговую диаграмму начислений

суммы к выдаче всех сотрудников за ноябрь месяц.

Задание 4. Создать таблицу расчета годового фонда заработной платы бригады рабочих хлебозавода, произвести расчеты и форматирование. Система оплаты повременно-премиальная. По балансу рабочего времени один среднесписочный рабочий должен отработать 1824 часа.

Квалификация рабочих

Количество рабочих в смену

Пекарь-мастер

1

Дрожжевод

Часовая тарифная ставка, сом

Доплата за профессиональное мастерство, %

1

21,32

Тестовод

Доплата за вредные условия труда, %

20

Пекарь

18,43

1

17

2

Машинист

18,43

20

17

Укладчик

21,32

2

20

17

1

Годовой фонд заработной платы, тыс. сом

20

16,50

18,43

20

8

20

20

20

Примечание. В т аблицу необходимо добавить следующие столбцы: «Доплата за профессиональное мастерство, сом», «Доплата за вредные условия труда, Сом», «Количество отработанных часов», «Тарифная зарплата, сом», «Общая заработная плата, сом».

Формулы расчета Тарифная зарплата = Количество рабочих в смену× Количество отработанных часов × Часовая тарифная ставка, сом

Доплата за профессиональное мастерство, сом = Тарифная зарплата, сом × Доплата за профессиональное мастерство, %

Доплата за вредные условия труда, сом = Тарифная зарплата, сом × Доплата за вредные условия труда, %

Годовой фонд заработной платы, тыс. сом = Сумма столбца «Общая заработная плата, сом»

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

После окончания работы закройте все открытые файлы, закройте окно текстового редактора MS Excel, после чего завершите работу компьютера (Пуск – выключить ПК)

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

• Основное назначение и возможности процессора электронных таблиц MS Excel?
• Как произвести запуск программы MS Excel?
• Что такое «ячейка абсолютной адресации»?
• Какие типы функций используются в MS Excel?
• Каким образом производится копирование электронных таблиц?
• Как можно произвести условное форматирование в отдельном столбце ЭТ?
• Каким образом можно вставить примечание в отдельную ячейку ЭТ?
• Как устанавливается пароль доступа к ЭТ?