Лабораторная работа №8
Цель. Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.
Задание 1. Создать и заполнить таблицу расчёта доходов, показанную на рисунке.
Распределение доходов в зависимости от КТУ
Общий доход
Фамилия
Время, ч
Квалификационнй разряд
Сумма к выдаче
Итого
Алгоритм выполнения задания.
Записать исходные значения таблицы, указанные на рисунке.
Заполнить графу Фамилия значениями Сотрудник 1÷10, используя операцию Автозаполнение.
Рассчитать графу КТУ как произведение времени, затраченного сотрудником, на его квалификационный разряд (формула =В4*С4).
Подсчитать значение Итого с помощью операции Автосумма.
Графа Сумма к выдаче рассчитывается как произведение общего дохода на отношение КТУ данного сотрудника к итоговому КТУ (формула =В2*D4/D14).
При выполнении операции Автозаполнение в графе Сумма к выдаче появляются ошибки #ЗНАЧ! и #ДЕЛ/0!. Это происходит из-зи того, что при применении формулы происходит изменение адресов в ней, например, в ячейке Е5 формула содержит адреса = В3*D5/D15.
Для правильного расчёта необходимо зафиксировать адреса В2 и D14, для этого:
Выделить ячейку Е4.
В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу F4, у обозначения адреса появятся значки $B$4, щёлкнуть по обозначению адреса D14, нажать клавишу F4, у обозначения адреса появятся значки $D$14.
Выполнить заново операцию Автозаполнение для графы Сумма к выдаче (вместе с ячейкой Итого).
В ячейке Итого должна получиться сумма, равная Общему доходу.
Присвоить денежным величинам обозначение в рублях, для этого выделить ячейку В2, щёлкнуть кнопку Денежный формат на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
Денежный, установить в поле Обозначение тип р.
Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификацилннлгл разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например на 25000 р.
Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, щёлкнуть на кнопке Увеличить разрядность на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
/Денежный, установить в поле Число десятичных знаков число 2.
Задание 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.
Тема: Основы работы с электронной таблицей Excel
Цель:Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.
Задание № 1:Создать и заполнить таблицу расчёта доходов, показанную на рисунке.
Алгоритм выполнения задания:
1. Записать исходные значения таблицы, указанные на рисунке.
2. Заполнить графу Фамилиязначениями Сотрудник 1-10, используя операцию Автозаполнение.
3. Рассчитать графу КТУ как произведение времени, затраченного сотрудником, на его квалификационный разряд (формула =В4*С4).
4. Подсчитать значение Итогос помощью операции Автосумма.
5. Графа Сумма к выдачерассчитывается как произведение общего дохода на отношение КТУ данного сотрудника к итоговому КТУ (формула =В2*D4/D14).
6. При выполнении операции Автозаполнение в графе Сумма к выдачепоявляются ошибки #ЗНАЧ! и #ДЕЛ/0!. Это происходит из-за того, что при применении формулы происходит изменение адресов в ней, например, в ячейке Е5 формула содержит адреса = В3*D5/D15.
7. Для правильного расчёта необходимо зафиксировать адреса В2 и D14, для этого:
a. Выделить ячейку Е4.
b. В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу F4, у обозначения адреса появятся значки $B$2, щёлкнуть по обозначению адреса D14, нажать клавишу F4, у обозначения адреса появятся значки $D$14.
c. Выполнить заново операцию Автозаполнение для графы Суммак выдаче (вместе с ячейкой Итого).
d. В ячейке Итого должна получиться сумма, равная Общему доходу.
8. Присвоить значению графы Общий доход обозначение в рублях, для этого выделить ячейку В2, выполнить команду Формат Ячеек…/Число. Выбрать значение Денежный, установить в поле Обозначение тип р.
9. Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, выполнить команду Формат Ячеек…/Число.Выбрать значение Денежный, установить в поле Обозначение тип р, установить в поле Число десятичных знаков значение 2.
10. Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификационного разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например, на 25000 р.
Примерный вид таблицы, после выполнения задания 9:
Задание № 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.
Алгоритм выполнения задания.
1. Записать исходные текстовые и числовые данные.
2. Рассчитать графу Стоимость, р. по формуле =B3*B10, используя курс доллара (ячейка В10) как абсолютный адрес. Смотри инструкцию к пункту 7 Задания № 1.
3. Рассчитать графу Стоимость, Европо формуле =С3/D10,используя курс Евро (ячейка D10) как абсолютный адрес.
4. Подсчитать значение Итогос помощью операции Автосумма.
5. Установить для графы Стоимость, Евроотображение без десятичных разрядов, для этого выделить диапазон ячеек D3:D9, выполнить команду Формат Ячеек…/Число.Выбрать значение Числовой, установить в поле Число десятичных знаковзначение0.
6. Рассчитать графу Доля в общей стоимостидля диапазона ячеек E3:E9 по формуле =С3/С9, используя итоговую Стоимость, р. как абсолютный адрес.В ячейке Итого должна получиться сумма, равная 100.
7. Преобразовать числовые значения в графе Доля в общей стоимостив процентные значения:
a) Выделить числовые значения этой графы.
b) В окне Формат ячеек выбрать значение Процентный формат.
Практическая работа «Абсолютный адрес в MS Excel»
Цель. Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.
Задание 1. Создать и заполнить таблицу расчёта доходов, показанную на рисунке.
Алгоритм выполнения задания.
Записать исходные значения таблицы, указанные на рисунке.
Заполнить графу Фамилия значениями Сотрудник 1÷10, используя операцию Автозаполнение.
Рассчитать графу КТУ как произведение времени, затраченного сотрудником, на его квалификационный разряд (формула =В4*С4).
Подсчитать значение Итого с помощью операции Автосумма.
Графа Сумма к выдаче рассчитывается как произведение общего дохода на отношение КТУ данного сотрудника к итоговому КТУ (формула =В2* D 4/ D 14).
При выполнении операции Автозаполнение в графе Сумма к выдаче появляются ошибки #ЗНАЧ! и #ДЕЛ/0!. Это происходит из-зи того, что при применении формулы происходит изменение адресов в ней, например, в ячейке Е5 формула содержит адреса = В3* D 5/ D 15.
Для правильного расчёта необходимо зафиксировать адреса В2 и D 14, для этого:
Выделить ячейку Е4.
В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу F 4, у обозначения адреса появятся значки $ B $4, щёлкнуть по обозначению адреса D 14, нажать клавишу F 4, у обозначения адреса появятся значки $ D $14.
Выполнить заново операцию Автозаполнение для графы Сумма к выдаче (вместе с ячейкой Итого).
В ячейке Итого должна получиться сумма, равная Общему доходу.
Присвоить денежным величинам обозначение в рублях, для этого выделить ячейку В2, щёлкнуть кнопку Денежный формат на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
Денежный, установить в поле Обозначение тип р.
Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификацилннлгл разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например на 25000 р.
Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, щёлкнуть на кнопке Увеличить разрядность на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
/Денежный, установить в поле Число десятичных знаков число 2.
Задание 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.
Алгоритм выполнения задания.
Записать исходные текстовые и числовые данные.
Рассчитать графу Стоимость, р., используя курс доллара как абсолютный адрес.
Рассчитать графу Стоимость, Евро, используя курс доллара и курс Евро как абсолютные адреса.
Рассчитать графу Доля в общей стоимости, используя итоговую Стоимость, р. как абсолютный адрес.
Преобразовать числовые значения в графе Доля в общей стоимости в процентные значения:
Выделить числовые значения этой графы.
Щёлкнуть по кнопке Процентный формат.
Установить отображение процентов с одним десятичным знаком, используя кнопки Увеличить или Уменьшить разрядность.
Контрольные вопросы
Для чего используются абсолютные и относительные адреса ячеек?
В чём смысл правил автоматической настройки формул при выполнении операций копирования и перемещения?
Как рассчитать графу стоимость р используя курс доллара как абсолютный адрес
Тема. Абсолютный адрес в MS Excel.
Цель. Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.
Задание 1. Создать и заполнить таблицу расчёта доходов, показанную на рисунке.
| A | B | C | D | E |
| Распределение доходов в зависимости от КТУ | ||||
| Общий доход | ||||
| Фамилия | Время, ч | Квалификационнй разряд | КТУ | Сумма к выдаче |
| Сотрудник 1 | ||||
| Итого |
Алгоритм выполнения задания.
- Записать исходные значения таблицы, указанные на рисунке.
- Заполнить графу Фамилиязначениями Сотрудник 1÷10, используя операцию Автозаполнение.
- Рассчитать графу КТУ как произведение времени, затраченного сотрудником, на его квалификационный разряд (формула =В4*С4).
- Подсчитать значение Итого с помощью операции Автосумма.
- Графа Сумма к выдачерассчитывается как произведение общего дохода на отношение КТУ данного сотрудника к итоговому КТУ (формула =В2*D4/D14).
- При выполнении операции Автозаполнение в графе Сумма к выдачепоявляются ошибки #ЗНАЧ! и #ДЕЛ/0!. Это происходит из-зи того, что при применении формулы происходит изменение адресов в ней, например, в ячейке Е5 формула содержит адреса = В3*D5/D15.
- Для правильного расчёта необходимо зафиксировать адреса В2 и D14, для этого:
Выделить ячейку Е4.
В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу F4, у обозначения адреса появятся значки $B$4, щёлкнуть по обозначению адреса D14, нажать клавишу F4, у обозначения адреса появятся значки $D$14.
Выполнить заново операцию Автозаполнение для графы Сумма к выдаче (вместе с ячейкой Итого).
В ячейке Итого должна получиться сумма, равная Общему доходу.
Присвоить денежным величинам обозначение в рублях, для этого выделить ячейку В2, щёлкнуть кнопку Денежный формат на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
- Денежный, установить в поле Обозначение тип р.
- Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификацилннлгл разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например на 25000 р.
- Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, щёлкнуть на кнопке Увеличить разрядность на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
/Денежный, установить в поле Число десятичных знаковчисло 2.
Задание 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.
| A | B | C | D | E |
| Стоимость программного обеспечения | ||||
| Наименование | Стоимость, $ | Стоимость, р. | Стоимость, Евро | Доля в общей стоимости, % |
| OC Windows | ||||
| Пакет MS Office | ||||
| Редактор Corel Draw | ||||
| Графический ускоритель 3D | ||||
| Бухгалтерия 1С | ||||
| Антивирус DR Web | ||||
| Итого | ||||
| Курс валюты (к рублю) |
Алгоритм выполнения задания.
- Записать исходные текстовые и числовые данные.
- Рассчитать графу Стоимость, р., используя курс доллара как абсолютный адрес.
- Рассчитать графу Стоимость, Евро,используя курс доллара и курс Евро как абсолютные адреса.
- Рассчитать графу Доля в общей стоимости, используя итоговую Стоимость, р. как абсолютный адрес.
- Преобразовать числовые значения в графе Доля в общей стоимостив процентные значения:
Выделить числовые значения этой графы.
Щёлкнуть по кнопке Процентный формат.
Установить отображение процентов с одним десятичным знаком, используя кнопки Увеличить или Уменьшить разрядность.
Практическая работа «Абсолютный адрес в MS Excel»
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Лабораторная работа №8
Тема. Абсолютный адрес в MS Excel .
Цель. Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.
Задание 1. Создать и заполнить таблицу расчёта доходов, показанную на рисунке.
Алгоритм выполнения задания.
Записать исходные значения таблицы, указанные на рисунке.
Заполнить графу Фамилия значениями Сотрудник 1÷10, используя операцию Автозаполнение.
Рассчитать графу КТУ как произведение времени, затраченного сотрудником, на его квалификационный разряд (формула =В4*С4).
Подсчитать значение Итого с помощью операции Автосумма.
Графа Сумма к выдаче рассчитывается как произведение общего дохода на отношение КТУ данного сотрудника к итоговому КТУ (формула =В2* D 4/ D 14).
При выполнении операции Автозаполнение в графе Сумма к выдаче появляются ошибки #ЗНАЧ! и #ДЕЛ/0!. Это происходит из-зи того, что при применении формулы происходит изменение адресов в ней, например, в ячейке Е5 формула содержит адреса = В3* D 5/ D 15.
Для правильного расчёта необходимо зафиксировать адреса В2 и D 14, для этого:
Выделить ячейку Е4.
В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу F 4, у обозначения адреса появятся значки $ B $4, щёлкнуть по обозначению адреса D 14, нажать клавишу F 4, у обозначения адреса появятся значки $ D $14.
Выполнить заново операцию Автозаполнение для графы Сумма к выдаче (вместе с ячейкой Итого).
В ячейке Итого должна получиться сумма, равная Общему доходу.
Присвоить денежным величинам обозначение в рублях, для этого выделить ячейку В2, щёлкнуть кнопку Денежный формат на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
Денежный, установить в поле Обозначение тип р.
Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификацилннлгл разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например на 25000 р.
Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, щёлкнуть на кнопке Увеличить разрядность на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
/Денежный, установить в поле Число десятичных знаков число 2.
Задание 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.
Алгоритм выполнения задания.
Записать исходные текстовые и числовые данные.
Рассчитать графу Стоимость, р., используя курс доллара как абсолютный адрес.
Рассчитать графу Стоимость, Евро, используя курс доллара и курс Евро как абсолютные адреса.
Рассчитать графу Доля в общей стоимости, используя итоговую Стоимость, р. как абсолютный адрес.
Преобразовать числовые значения в графе Доля в общей стоимости в процентные значения:
Выделить числовые значения этой графы.
Щёлкнуть по кнопке Процентный формат.
Установить отображение процентов с одним десятичным знаком, используя кнопки Увеличить или Уменьшить разрядность.
Контрольные вопросы
Для чего используются абсолютные и относительные адреса ячеек?
В чём смысл правил автоматической настройки формул при выполнении операций копирования и перемещения?
Практические работы по MS Excel
Создайте книгу Практическая работа в Excel .
Стоимость программного обеспечения
наименование
стоимость, руб.
пакет MS Office
Антивирус DR Web
Курс валюты (к рублю)
1. Записать исходные текстовые и числовые данные, оформить таблицу согласно образцу, приведенному выше.
2. Рассчитать «Стоимость, руб.», используя курс доллара как абсолютный адрес.
3. Рассчитать графу «Стоимость, евро», используя стоимость в рублях и используя курс доллара как абсолютный адрес.
4. Рассчитать графу «Итого», используя функцию =СУММ (выделить диапазон).
Задание 1.2
В книге Практическая работа в Excel .
- Создайте таблицу учета товаров, на втором Листе книги, пустые столбцы сосчитайте по формулам.
курс доллара
6 3 ,5
Таблица учета проданного товаров
цена в рублях за 1 товар
цена в долларах за 1 товар
всего в рублях
2. Отформатируйте таблицу по образцу. Курс доллара- абсолютный адрес.
3. Переименуйте лист Учет товара.
4. Оформите таблицу (цвет шрифта, заливка, рамка таблицы)
5. Сохраните работу в собственной папке.
Задание 1.3
В книге Практическая работа в Excel .
1. Составьте таблицу для выплаты заработной платы для работников предприятия на третьем Листе книги.
Расчет заработной платы.
Полученный доход
Налоговые вычеты
Налогооблагаемый доход
Сумма налога,
Богданов К.М.
- Сосчитайте по формулам пустые столбцы.
- Налогооблагаемый доход = Полученный доход – Налоговые вычеты.
- Сумма налога = Налогооблагаемый доход*0,13.
- К выплате = Полученный доход-Сумма налога НДФЛ.
- Отсортируйте таблицу в алфавитном порядке.
- Переименуйте лист Расчет заработной платы.
- Оформите таблицу (цвет шрифта, заливка, рамка таблицы)
- Сохраните работу в собственной папке.
Практические работы по MS Excel
Практическая работа №2. Использование функций СУММ, СРЗНАЧ, МИН, МАКС, ЕСЛИ.
Задание 1.1
В книге Практическая работа в Excel №2.
Заданы стоимость 1 кВт/ч электроэнергии и показания счетчика за предыдущий и текущий месяцы. Необходимо вычислить расход электроэнергии за прошедший период и стоимость израсходованной электроэнергии.
Технология работы:
1. Выровняйте текст в ячейках. Выделите ячейки А3:Е3. Главная — Формат –Формат ячейки – Выравнивание: по горизонтали – по центру, по вертикали – по центру, отображение – переносить по словам.
2. В ячейку А4 введите: Кв. 1, в ячейку А5 введите: Кв. 2. Выделите ячейки А4:А5 и с помощью маркера автозаполнения заполните нумерацию квартир по 7 включительно.
5. Заполните ячейки B4:C10 по рисунку.
6. В ячейку D4 введите формулу для нахождения расхода эл/энергии. И заполните строки ниже с помощью маркера автозаполнения.
7. В ячейку E4 введите формулу для нахождения стоимости эл/энергии. И заполните строки ниже с помощью маркера автозаполнения.
Обратите внимание!
При автозаполнении адрес ячейки B1 не меняется,
т.к. установлена абсолютная ссылка.
8. В ячейке А11 введите текст «Статистические расчеты» выделите ячейки A11:B11 и щелкните на панели инструментов кнопку «Объединить и поместить в центре».
9. В ячейках A12:A15 введите текст, указанный на рисунке.
10. В ячейке B12 с помощью функции СУММ, рассчитать общую сумму стоимости эл/энергии.
11. Аналогично функции задаются и в ячейках B13:B15.
В13-СРЗНАЧ расхода эл/энергии,
В14-МАКС расход эл/энергии,
В15-МИН расход эл/энергии.
12. Расчеты выполняются на Листе 1, переименуйте его в Электроэнергию.
Логические функции предназначены для проверки выполнения условия или проверки нескольких условий.
Функция ЕСЛИ позволяет определить выполняется ли указанное условие. Если условие истинно, то значением ячейки будет выражение1, в противном случае – выражение2.
=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)
Пример: Вывести в ячейку сообщение «тепло», если значение ячейки B 2>20, иначе вывести «холодно» =ЕСЛИ( B 2>20;”тепло”;”холодно”)
Пример: вывести сообщение «выиграет» если значение ячеек Е4<3 и Н98>=13 (т.е. одновременно выполняются условия), иначе вывести «проиграет»
Часто на практике одного условия для логической функции мало. Когда нужно учесть несколько вариантов принятия решений, выкладываем операторы ЕСЛИ друг в друга. Таким образом, у нас получиться несколько функций ЕСЛИ в Excel.
Синтаксис будет выглядеть следующим образом:
=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))
Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.
Задание 1.2
1. Заполнить таблицу и отформатировать по образцу (Лист 2 «Экзамены»)
2. Заполните формулой =СУММ диапазон ячеек F 4: F 10
3. В ячейках диапазона G 4: G 10 должно быть выведено сообщение о зачислении абитуриента.
4. Абитуриент зачислен в институт, если сумма баллов больше или равна проходному баллу и оценка по математике 4 или 5, в противном случае – не зачислен.
Задание 1.3 (Самостоятельная работа)
1. Создайте таблицу оклада работников предприятия на Листе 3 («Оклад») книги.