36877

РАБОТА С ФОРМУЛАМИ И ФУНКЦИЯМИ В MS EXCEL

Лабораторная работа

Информатика, кибернетика и программирование

Создайте таблицу Результаты тестирования рассчитайте средний показатель тестирования для каждого сотрудника. Создайте таблицу содержащую следующие поля: № п п Фамилия Тест 1 Тест 2 Тест 3 Тест 4 Средний показатель Заполните таблицу данными. Таблица результаты тестирования Рассчитайте Средний показатель тестирования каждого сотрудника. Для этого: Выделите пустую ячейку в поле Средний показатель напротив фамилии первого сотрудника.

Русский

2013-09-23

527 KB

30 чел.

Лабораторная работа № 2.
РАБОТА С ФОРМУЛАМИ И ФУНКЦИЯМИ В MS EXCEL

Цель работы: изучить правила создания формул, научиться обрабатывать данные в приложении MS Excel при помощи формул  и функций.

Организационная форма занятия: лабораторная работа.

Вопросы (компетенции, навыки) для освоения:

  1.  Научиться создавать формулы и функции в MS Excel.
  2.  Изучить правила создания формул и функций.
  3.  Изучить назначение окна Мастер функций и научиться использовать опции этого окна для создания функции.
  4.  Изучить возможности использования абсолютной и относительной адресации ячеек при создании формул и функций.

Задания для выполнения и методические рекомендации:

Выполнять работу вы можете на пустых листах книги, созданной в предыдущей работе (файл Работа 1) или открыть новый файл MS Excel.

! Все формулы в MS Excel должны начинать со знака равенства

Задание 1. Создайте таблицу Результаты тестирования, рассчитайте средний показатель  тестирования для каждого сотрудника.

  1.  Создайте таблицу, содержащую следующие поля:

№ п/п

Фамилия

Тест 1

Тест 2

Тест 3

Тест 4

Средний показатель

  1.  Заполните таблицу данными. Поля № п/п и Фамилия заполните, воспользовавшись функцией автозаполнения. Для заполнения фамилий MS Excel будет использовать список, созданный вами в 1 лабораторной работе. Проставьте результаты тестирования (рис. 1).

Рис. 1. Таблица результаты тестирования

  1.  Рассчитайте Средний показатель тестирования каждого сотрудника. Для этого:
  •  Выделите пустую ячейку в поле Средний показатель напротив фамилии первого сотрудника.
  •  Нажмите кнопку Вставить функцию  в строке ввода или на панели Формулы.
  •  В окне диалога Мастер функций выберите Статистические в списке Категория. Выберите СРЗНАЧ в списке Функций. Нажмите кнопку ОК (рис. 2).

Рис. 2. Диалоговое окно Мастер функций

  •  В появившемся диалоговом окне укажите диапазон ячеек (в нашем примере: диапазон – C2:F2), среди которых надо найти среднее значение и нажмите ОК. 
  1.  Рассчитайте средний показатель тестирования для всех сотрудников, протащив полученный результат за маркер автозаполнения вниз.

Задание 2. Обработка данных при помощи логических функций.

При помощи логической функции Если определите прошли или не прошли сотрудники тестирование. Если средний показатель за тестирование меньше 60 % сотрудник тестирование не прошел.

  1.  Добавьте к таблице столбец с заголовком Прошел/Не прошел тестирование.
  2.  Установите курсор в пустую ячейку напротив первого сотрудника. Вызовите Мастер функций: в списке Категория выберите Логические, в списке Функций функцию Если (или выберите категорию Логические непосредственно на панели Формул).
  3.  В диалоговом окне Аргументы функции (рис. 3) установите: Лог_ выражение: G2>60%; Значение_если_истина: тест прошел; Значение_если_ложь:  тест не прошел. Нажмите ОК.

Рис. 3. Диалоговое окно Аргументы функции

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

Задание 3. Рассчитайте размер премии для каждого сотрудника, исходя из коэффициента и расчетной суммы премии в 1 000 рублей.

Коэффициент проставляется в зависимости от среднего показателя за тестирование: если средний показатель до 60 % (т.е. сотрудник не сдал тест), то коэффициент 0, от 60% до 70% коэффициент 1, от 70% до 80% коэффициент 1,2, выше 80 % - 1,5.

  1.  Добавьте к ранее созданной таблице поле Коэффициент. Проставьте коэффициенты для каждого сотрудника, исходя из результатов тестирования.
  2.  Добавьте к таблице поле Премия. Отступив одну ячейку от таблицы запишите  Расчетная сумма для премии и в пустую ячейку впишите сумму, например 1000.
  3.  Для первого сотрудника в таблице рассчитайте премию по формуле расчетная сумма * коэффициент, используя абсолютные и относительные ссылки на ячейки. Относительная ссылка задается адресом ячейки, абсолютная ссылка содержит в записи адреса значок  $ перед буквой столбца и цифрой строки. Например: относительная ссылка – A2, D4; абсолютная ссылка - $A$2; $D$4. В формуле для расчета премии относительная ссылка задается для ячейки, содержащей коэффициент, а абсолютная – для адреса ячейки с расчетной суммой. Для нашего примера установите курсор в ячейку J2 и наберите: =$L$2*I2 (рис. 4).

Рис. 3. Таблица тестирования сотрудников

  1.  Воспользовавшись функцией автозаполнения,  скопируйте полученную формулу  для расчета премии остальных сотрудников. Обратите внимание, что ссылка на ячейку с абсолютной адресацией при копировании не изменяется.
  2.  Подсчитайте общую сумму, потраченную на премию для всех сотрудников, воспользовавшись функцией автосуммирование. Для этого:
  •  Выделите пустую ячейку под последним значением столбца Премия
  •  Нажмите на кнопку Автосуммирование  на панели Главная или Формулы.
  •  Проверьте, правильно ли выделен диапазон ячеек для суммирования или выделите нужный диапазон мышью
  •  Нажмите Enter

Задание 4. Составление  платежной ведомости.

  1.  Скопируйте, полученную таблицу на пустой лист следующим образом:
    •  Выделите таблицу, не захватывая строку с общей суммой премии, выберите на панели Главная команду Копировать;
    •  Прейдите на пустой лист;
    •  Установите курсор в ячейку А1 и выполните команду Вставить значения и исходное форматирование или в диалоговом окне Специальная вставка - Значения и форматы чисел;
  2.  Так как промежуточные данные нам не понадобятся, то скроем их. Для этого выделим эти столбцы (от столбца C до столбца I). и выполним команду Формат – Скрыть или отобразить – Скрыть столбцы.
  3.  Озаглавьте таблицу «Платежная ведомость». Для этого добавьте одну пустую строку перед таблицей следующим образом: Выделите мышью строку 1, выберите на панели Главная команду Вставить – Вставить строки на листе.
  4.  Добавьте к таблице еще два поля: Налог и К выдаче.
  5.  Рассчитайте налог для каждого сотрудника, если известно, что он составляет 13 % от начисленной суммы премии.
  6.  Рассчитайте сумму к выдаче: Премия – Налог (Рис. 4).

Рис. 4. Таблица Платежная ведомость

Сохраните результаты проделанной работы в своей папке под названием Работа 2

Рекомендуемая литература:

Основная:

  1.  Информатика: учебник / Под ред. проф. В.В.Трофимова. – М.: Издательство Юрйт; ИД Юрайт, 2011. – 911 c. (указать раздел и стр.)
  2.  Симонович С.В. Информатика. Базовый курс. Учебник для вузов. Изд-во: Питер, 2009. – 640 с. (указать раздел и стр.)

Дополнительная:

  1.  Программное обеспечение ЭВМ (практическое руководство по работе с приложениями OpenOffice.org): Учебно-методическое пособие. – Ставрополь: Изд-во СГУ, 2009. – 235 с. (Электронный процессор OpenOffice.org Calc – Лабораторная работа 2, 3, стр. 86-113).

Интернет-ресурсы:

http://www.intuit.ru/catalog/office/ - Офисные технологии: Microsoft Excel:  Основы вычислений, Математические и статистические функции, Финансовые функции, функции работы с датами и временем и другие функции

http://office.microsoft.com - официальный сайт Корпорации Майкрософт (Microsoft Corporation)

Задание для развития и контроля владения компетенциями:

  1.  Создайте таблицу по образцу (рис. 5):

Рис. 5. Образец «Таблица Студент»

  1.  Рассчитайте средний балл за сессию каждого студента и средний балл группы.
    1.  Для назначения стипендии с помощью Расширенного фильтра выберите в отдельную таблицу студентов сдавших сессию на 4 и 5. (Для выборки в таблице Условий используйте условие для каждого предмета >=4). Внимание! Технология работы с расширенным фильтром была изучена в 1 лабораторной работе.
    2.  Добавьте к полученной таблице поля Коэффициент и Стипендия и рассчитайте стипендию для студентов группы. Для этого:
  •  Отсортируйте таблицу в порядке возрастания Среднего балла за сессию
  •  Проставьте коэффициенты студентам группы:  

коэффициент 1 – средний балл = 4;

коэффициент 1,2 – средний балл > 4, но < 5;

коэффициент 1,5 – средний балл = 5;

  •  В любую ячейку, не входящую в таблицу занесите исходную сумму для расчета стипендии, например 1000
  •  Для первого студента в таблице рассчитайте стипендию по формуле расчетная сумма * коэффициент, используя абсолютные и относительные ссылки на ячейки.
  •  Воспользовавшись функцией автозаполнения, скопируйте полученную формулу  для расчета стипендии остальных студентов группы. Обратите внимание, что ссылка на ячейку с абсолютной адресацией при копировании не изменяется.
    1.  Скопируйте таблицу Студент на пустой лист (Столбцы: № п/п, Фамилия, Названия предметов). Воспользовавшись функцией Если с дополнительным параметром И выясните будут ли студенты получать стипендию (рис. 6)

Рис. 6. Таблица Студент

Внимание! Функцию и аргументы функции задайте в специальном диалоговом окне (рис. 7).

Рис. 7. Диалоговое окно Аргументы функции

  1.  Сохраните полученные в результате выполнения заданий таблицы в своей папке.
    1.  Сдайте отчет о проделанной работе преподавателю.


 

А также другие работы, которые могут Вас заинтересовать

15175. Жамбыл - айтыс шебері 44 KB
  ӘОЖ ЖАМБЫЛ АЙТЫС ШЕБЕРІ Ж.Момбекова Ә. Уралбекова Тараз мемлекеттік педагогикалық институты Тараз қ. Халық поэзиясы көбіне ауызша айтылады кейін назарға түсіріліп оқылады. Оның көркемдеу құралдары да басқаша көбіне байырғы тіл әдісімен баяндалады. Оның та...
15176. Жамбыл - әлем түркілері поэзиясының алыбы 55.5 KB
  ӘӨЖ 894.342.35 Ә 20 ЖАМБЫЛ ӘЛЕМ ТҮРКІЛЕРІ ПОЭЗИЯСЫНЫҢ АЛЫБЫ Меңдібай Әбілұлы А.Ясауи атындағы халықаралық қазақтүрік университеті Тараз институты Тараз қаласы. Түрік әдебиетінің мақтанышы Мехмет Юрдақұл: Мен түрікпін дінім ұлтым ұлы ғой Өне ...
15177. Жамбыл - жырау 50.5 KB
  ӘОЖ:82.09:82.091 ЖАМБЫЛ – ЖЫРАУ Т.Мұқашев Тараз мемлекеттік педагогикалық институты Тараз қ. Жыраулық дәстүрдің соңы ХІХ ғасырмен аяқталып оның орнына ендігі жерде ақындық дәстүр жалғасын тапты. ХІХ ғасырда өмір сүрген Базар жырау Қабан жырау сияқты жыр дүлділі ...
15178. Жамбылдың ұстазы еді Майкөт ақын 46.5 KB
  ЖАМБЫЛДЫҢ ҰСТАЗЫ ЕДІ МАЙКӨТ АҚЫН Сыманова Г.Қ 10 сынып оқушысы Жамбыл облыстық ерекше дарынды балаларға арналған Дарын мектепинтернаты. Ғасыр мен ғасыр беттесіп уақыт теңізінің алып толқындары жалын желбіретіп аспанға шапшығанда тереңде қалған жақұт ...
15179. Жүрсін Ерман 38 KB
  Сөз құны – сөз қадірі Бұған дейін Жанартау Арайлы күн армысың Ұлытауға бардың ба... Құдіретке жүгіну кітаптарымен танылған Жүрсін Ерманның жақында Атамұра баспасынан Құстың көлеңкесі деп аталатын таңдамалы жарық көрді. Кітаптың кіріспесі Ақы...
15180. Жүсіпбек Аймауытов - әдебиет сыншысы 71.5 KB
  Рақымжан Тұрысбек Л.Н.Гумилев атындағы Еуразия ұлттық университетінің профессоры филология ғылымдарының докторы Ж.АЙМАУЫТҰЛЫ – ӘДЕБИЕТ СЫНШЫСЫ Ұлттық сөз өнеріне оның өрісті өркендеуіне өлшеусіз үлес қосқан – Жүсіпбек Аймауытұлы әдеби мұра мен оны зерт...
15181. Жүсіпбек Аймауытов 107 KB
  ЖҮСІПБЕК АЙМАУЫТОВ 18891931 Аймауытов Жүсіпбек 1889 1931 қазақтың көрнекті жазушысы қазақ әдебиетін қалыптастырушылардың бірі.Туып өскен жері Павлодар облысының Баянауыл ауданына қарасты бұрынғы Қызыл ту қазіргі Жүсіпбек Аймауытов ауылы.Әкесі Аймауыт кед
15182. Жұбан Молдағалиев 64 KB
  Бір естелік 86ның ызғарлы желтоқсанындағы дүрбелең халық көкейінде әлі жаңғырып тұр. Уақыт алға жылжыған сайын кезінде айтылуға тыйым салынған шындықтар тамтұмдап болса да оқиғаны көзбен көргендердің аузымен айтыла бастады. Жастардың зиялы қауымға деген сенімсізд
15183. Зейнолла Шыраев 74 KB
  ЖЫР ОТЫМЕН БІРГЕ ЖАНЫП ӨМІРДЕН ӨТКЕН АҚЫН Г.Е.Махамбетжанова Н.Т.Қожанова Қызылорда облысы Қазалы қаласындағы Ғ.Мұратбаев атындағы № 17 орта мектебі Cыр өңірі қашаннан хас таланттардың отаны болып табылады. Бұл өңірден түлеп ұшқан жырау жыршы шайырлардың ақынд...