Использование логических функций
ФЕДЕРАЛЬНОЕ АГЕНСТВО ПО ОБРАЗОВАНИЮ РФ
САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ
Электронные таблицы EXCEL
Методические указания
для выполнению лабораторных работ
Часть 1
Санкт-Петербург
2006
Общие указания к лабораторным работам
Запуск программы
Запуск программы Microsoft Excel осуществляется ПускèПрограммыè Microsoft Excel или с помощью соответствующего ярлыка на рабочем столе.
Отчетность по работе
Студент по результатам каждой лабораторной работы должен представить отчет и файл; отчет выполняется в рукописном виде и должен содержать исходные данные и результаты работы.
Файл следует сохранить папке V:\N_группы\фамилия_студента.Имя файла: ЛабРаб#_задание# (где символ # обозначает номер лабораторной работы и задания).
Примечание. Папка Мои документы при завершении сеанса пользователя очищается, поэтому студент, сохранивший файл в папке Мои документы, при следующем входе в систему не обнаружит свой файл и работа не будет засчитана. Будьте внимательны!
Лабораторная работа 1
Создание типовых шаблонов для расчетов
Цель работы: вычисление по формулам, использование абсолютной адресацией (2 способа), оформление документа, создание шаблонов, защита ячеек листа
Задание 1. Вычисления и построение диаграмм
Заполнить таблицу, выполнить вычисления. Вычисляемые значения – жирнымкурсивным шрифтом.
|
|
|
При выполнении задания необходимо:
- при вводе табельного номера должен обеспечиваться ввод нулей в первой позиции;
- для вычисления стоимости затрат рублях использовать абсолютную адресацию, использовать 2 способа задания абсолютного адреса (с помощью символа $ в адресе ячейки и присвоении ячейке имени Курс).
Построить диаграмму, иллюстрирующую числовые данные.
Закрыть для ввода все ячейки, за исключением числовых данных, используя функции защиты листа.
| Таб. номер | Фамилия, инициалы | Месяцы |
| ||||
| Январь | Февраль | Март | Всего | Всего, р | Курс | ||
| 0987 | Николаев А.Л. | € 1 236 | € 456 | € 852 | € 2 544 | 85 376,64р. | 33,56р. |
| 00123 | Петров П.Д. | € 145 | € 896 | € 987 | € 2 028 | 68 059,68р. |
|
| 7890 | Семенова Е.Г. | € 789 | € 742 | € 456 | € 1 987 | 66 683,72р. |
|
| 3456 | Устинов К.Ф. | € 785 | € 258 | € 852 | € 1 895 | 63 596,20р. |
|
| 8765 | Петров В.Д. | € 963 | € 896 | € 789 | € 2 648 | 88 866,88р. |
|
|
| Итого | € 3 918 | € 3 248 | € 3 936 | € 11 102 |
|
|
|
| Итого, р | 131 488,08р. | 109 002,88р. | 132 092,16р. | 372 583,12р. |
|
|
Задание 2. Использование абсолютной адресации и констант
Заполнить таблицу, выполнить вычисления. Вычисляемые значения – жирнымкурсивным шрифтом.
|
|
|
При выполнении задания необходимо:
- для вычисления расхода и стоимости топлива использовать абсолютную адресацию;
- константы (цену и расход топлива) разместить на отдельном листе, присвоить данному листу имя Константы;
- ячейкам с константами (цена и расход топлива) присвоить имена Цена и Расход соответственно.
Закрыть для ввода все ячейки, за исключением числовых данных (пробег на начало дня при поступлении автомобиля в эксплуатацию – 01.09.03 и на конец каждого дня), используя функции защиты листа.
| Расходы на эксплуатацию автомобиля | |||||||
|
|
|
|
|
|
| ||
| Дата | Пробег, км | Суточный пробег | Расход топлива, л | Стоимость топлива | |||
| На начало дня | На конец дня | ||||||
| 01.09.03 | 15 800 | 16 250 | 450 | 38,3 | 478,13р. | ||
| 02.09.03 | 16 250 | 16 500 | 250 | 21,3 | 265,63р. | ||
| 03.09.03 | 16 500 | 16 800 | 300 | 25,5 | 318,75р. | ||
| 04.09.03 | 16 800 | 17 325 | 525 | 44,6 | 557,81р. | ||
| 05.09.03 | 17 325 | 18 000 | 675 | 57,4 | 717,19р. | ||
| 06.09.03 | 18 000 | 18 256 | 256 | 21,8 | 272,00р. | ||
| 07.09.03 | 18 256 | 18 380 | 124 | 10,5 | 131,75р. | ||
| 08.09.03 | 18 380 | 19 000 | 620 | 52,7 | 658,75р. | ||
| 09.09.03 | 19 000 | 19 257 | 257 | 21,8 | 273,06р. | ||
| 10.09.03 | 19 257 | 19 500 | 243 | 20,7 | 258,19р. | ||
| 11.09.03 | 19 500 | 19 800 | 300 | 25,5 | 318,75р. | ||
| 12.09.03 | 19 800 | 20 500 | 700 | 59,5 | 743,75р.
| ||
| 13.09.03 | 20 500 | 21 000 | 500 | 42,5 | 531,25р. | ||
| 14.09.03 | 21 000 | 21 300 | 300 | 25,5 | 318,75р. | ||
| Константы |
|
|
| Наименование | Значение | Примечание |
| Цена | 12,50р. | Бензин А92 на ПТК |
| Расход | 8,5 | литров на 100 км в летний период |
Лабораторная работа 2
Сводные таблицы
Ввести данные, выполнить вычисления и сформировать сводные таблицы (распределение поступления оборудования в отделы). При вычислениях использовать абсолютную адресацию.
| Учет поступления оборудования | ||||||||
|
|
|
|
|
|
|
| ||
| Дата | Отдел | Наименование | Цена, $ | Кол | Стоимость, $ | Стоимость, р | ||
| 10.01.03 | Администрация | Монитор | $125 | 1 | $125 | 3 820р. | ||
| 10.01.03 | Отдел кадров | Сканер | $89 | 2 | $178 | 5 440р. | ||
| 10.01.03 | Служба безопасности | Принтер | $210 | 4 | $840 | 25 670р. | ||
| 15.01.03 | Технический отдел | Системный блок | $450 | 3 | $1 350 | 41 256р. | ||
| 15.01.03 | Администрация | Монитор | $125 | 4 | $500 | 15 280р. | ||
| 20.01.03 | Администрация | Сканер | $89 | 1 | $89 | 2 720р. | ||
| 21.01.03 | Отдел кадров | Принтер | $210 | 2 | $420 | 12 835р. | ||
| 01.02.03 | Технический отдел | Системный блок | $450 | 5 | $2 250 | 68 760р. | ||
| 01.02.03 | Технический отдел | Монитор | $125 | 4 | $500 | 15 280р. | ||
| 15.02.03 | Отдел кадров | Принтер
| $210 | 3 | $630 | 19 253р. | ||
| 15.02.03 | Технический отдел | Сканер | $89 | 4 | $356 | 10 879р. | ||
| 02.03.03 | Администрация | Принтер | $210 | 2 | $420 | 12 835р. | ||
| 02.03.03 | Служба безопасности | Монитор | $125 | 4 | $500 | 15 280р. | ||
| 25.03.03 | Технический отдел | Системный блок | $450 | 2 | $900 | 27 504р. | ||
| 25.03.03 | Служба безопасности | Сканер | $89 | 1 | $89 | 2 720р. | ||
|
|
|
|
|
|
|
| ||
| Курс $ |
|
|
|
|
|
| ||
| 30,56р. |
|
|
|
|
|
| ||
Сформировать сводные таблицы, показывающие:
- распределение поступления оборудования в отделы;
- поступление оборудования по датам
Указание.
- Сводные таблицы формируются: Данные->Сводные_таблицы.
- Начинает работать мастер сводных таблиц.
- Шаги 1 и 2 проходим «по умолчанию».
- На шаге 3 нажимаем кнопку МАКЕТ. В макете сводной таблицы буксируем поля исходной таблицы на макет сводной.
- На шаге 4 выбираем место расположения сводной таблицы. По умолчанию – на новом листе.
| Отдел | Администрация |
|
|
|
|
|
|
|
|
|
|
|
| Сумма по полю Стоимость, р | Дата |
|
|
|
|
| Наименование | 10.01.2003 | 15.01.2003 | 20.01.2003 | 02.03.2003 | Общий итог |
| Монитор | 3820 | 15280 |
|
| 19100 |
| Принтер |
|
|
| 12835 | 12835 |
| Сканер |
|
| 2720 |
| 2720 |
| Общий итог | 3820 | 15280 | 2720 | 12835 | 34655 |
| Наименование | Монитор |
|
|
|
|
|
|
|
|
|
|
|
| Сумма по полю Стоимость, р | Дата |
|
|
|
|
| Отдел | 10.01.2003 | 15.01.2003 | 01.02.2003 | 02.03.2003 | Общий итог |
| Администрация | 3 820р. | 15 280р. |
|
| 19 100р. |
| Служба безопасности |
|
|
| 15 280р. | 15 280р. |
| Технический отдел |
|
| 15 280р. |
| 15 280р. |
| Общий итог | 3 820р. | 15 280р. | 15 280р. | 15 280р. | 49 660р. |
| Наименование | Принтер |
|
|
|
|
|
|
|
|
|
|
|
| Сумма по полю Стоимость, р | Дата |
|
|
|
|
| Отдел | 10.01.2003 | 21.01.2003 | 15.02.2003 | 02.03.2003 | Общий итог |
| Администрация |
|
|
| 12 835р. | 12 835р. |
| Отдел кадров |
| 12 835р. | 19 253р. |
| 32 088р. |
| Служба безопасности | 25 670р. |
|
|
| 25 670р. |
| Общий итог | 25 670р. | 12 835р. | 19 253р. | 12 835р. | 70 593р. |
Лабораторная работа 3
Использование логических функций
- Произвести расчет заработной платы (Начислено) сотрудникам компании в соответствии с отработанными рабочими днями и установленным дневным тарифом.
- Рассчитать премию, используя логическую функцию ЕСЛИ., исходя из условия - премия выплачивается, если сотрудник отработал 15 и более дней.
- Произвести расчет налога (13% от общей суммы) и рассчитать сумму К выдаче.
- Закрыть для ввода все ячейки, за исключением числовых данных (отработано дней), используя функции защиты листа.
| Начисление заработной платы в августе 2003 г. | |||||||
|
|
|
|
|
|
|
| |
| Таб. номер | Фамилия, инициалы | Отработано, дней | Начислено | Премия | Налог 13% | К выдаче | |
| 0987 | Николаев А.Л. | 15 |
|
|
|
| |
| 00123 | Петров П.Д. | 25 |
|
|
|
| |
| 7890 | Семенова Е.Г. | 12 |
|
|
|
| |
| 3456 | Устинов К.Ф. | 14 |
|
|
|
| |
| 8765 | Петров В.Д. | 16 |
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| |
| Тариф | Премия |
|
|
|
|
| |
| 350р. | 1 000р. |
|
|
|
|
| |
|
|
|
|
|
|
|
| |
|
|
| ||||||
Лабораторная работа 4
Подбор параметра
Постановка задачи.
- У студента имеется некоторая сумма свободная денег (S), которую он хочет положить на вклад в банк, допустим S=1000р.
- Банк выплачивает ежемесячно доход (p в %) от суммы вклада на последний день месяца, допустим p=0.8% в месяц.
- Студент положил деньги в банк 1 января[1] текущего года, а 31 декабря того же года получил доход по вкладу. Рассчитать сумму дохода D в программе Excel
- На полученный доход студент хочет к новогоднему столу купить коробку конфет, допустим, коробка конфет стоит K=65р.
Определить
- Сколько коробок конфет можно купить на полученный доход (количество может быть только целым), рассчитать сдачу.
- В результате того, что при покупке осталась сдача, очевидно, что часть денег на вкладе «не работали», т.е. можно было положить либо меньшую сумму, либо большую и, тем самым, купить на 1 коробку конфет больше. Поэтому необходимо произвести подбор параметра – определить сумму, при вложении которой дохода хватит на покупку 1 или 2 коробок конфет.
- Произвести подбор параметра – дохода банка.
Подбор параметра в Microsoft Excel производится следующим образом:
- активизируем ячейку, в которой должен быть подобран параметр;
- в меню выбираем Сервис - > Подбор параметра;
- в появившемся диалоговом окне указываем требуемое значение (в данном случае 1 или 2);
- указываем адрес ячейки, в которой должен быть изменен параметр (в данном случае – начальный вклад или процент банка).
Лабораторная работа 5
Дата добавления: 2018-04-05; просмотров: 117; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!