36877

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

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

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

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

Русский

2013-09-23

527 KB

31 чел.

Лабораторная работа № 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.  Сдайте отчет о проделанной работе преподавателю.


 

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

67124. Мифологизм советской тоталитарной культуры 38 KB
  Все эти меры нужны были чтобы в сознании каждого человека и массы укрепить новую мифологию советскую социально-историческую мифологию. Исходя из всего этого в новом мире все должно быть новым поэтому все силы советской идеологии бросили на создание нового человека за счет образования и воспитания.
67126. Мертвые души, Ревизор, Петербургские повести, 3-4 рассказа из духовной прозы, Тарас Бульба 42.5 KB
  Замысел произведения возник в 1835 году. В плане замысла ему помогает А. С. Пушкин, он подсказывает Гоголю сюжет. Тогда же Гоголем были сделаны первые наброски авантюрно-сатирического романа, в котором автор пытался показать хотя бы с одного бока, но всю Русь.
67127. Воздействие опасности на человека и техносферу 18.58 KB
  Повышение привычных уровней потоков сопровождается негативными воздействиями на человека и или природную среду. Результат взаимодействия человека со средой обитания может изменяться в широких пределах от позитивного до катастрофического сопровождающегося гибелью людей и разрушением компонентов среды обитания.
67128. Информационное общество и информационная культура 38 KB
  Информационное общество – термин введен американским экономистом Машлуп. Работа вышла в 1962 году – «Производство и распространение знаний в США». Начинает рассматривать информационную концепцию. Он делал акцент на степени развития информационных технологий...
67129. ФОРМЫ ГОСУДАРСТВА 147 KB
  Категория формы государства показывает особенности внутренней организации государства порядок образования и структуру органов государственной власти специфику их территориальной обособленности характер взаимоотношения друг с другом и населением а также те методы которые используются...
67130. ОСТАНОВКА КРОВОТЕЧЕНИЯ. ОСТРАЯ КРОВОПОТЕРЯ 308.5 KB
  Кровотечением называется вытекание крови из кровеносных сосудов при нарушении целостности их стенки в ткани полости организма во внешнюю среду. В силу большого давления крови кровотечение самостоятельно не останавливается.
67131. Розвиток культури у другій половині XVII - ХVІІІ ст. Доба бароко та її особливості в Україні. Козацтво як явище соціально-політичної історії та культури українського народу 281.5 KB
  Барокова архітектура. Таке розуміння філософії акцентуація уваги на проблемі людина і Всесвіт що як уже згадувалося притаманне філософській думці доби Бароко визначає вагоме місце яке посідає в системі поглядів києвомогилянців натурфілософська проблематика.
67132. Товар и товарная политика в маркетинге. Классификация товаров промышленного назначения 74.5 KB
  Концепция жизненного цикла товара Смысл концепции жизненного цикла товара ЖЦТ заключается в том что каждый товар имеет определенный период рыночной устойчивости т. Графически жизненный цикл товара можно описать в виде кривой рис. Кривая жизненного цикла товара Как правило жизненный цикл товара...