36877

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

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

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

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

Русский

2013-09-23

527 KB

29 чел.

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


 

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

33224. Электромагнитные измерительные приборы 13.15 KB
  Магнитоэлектрический прибор измерительный прибор непосредственной оценки для измерения силы электрического тока напряжения или количества электричества в цепях постоянного тока Электри́ческая мо́щность физическая величина характеризующая скорость передачи или преобразования электрической энергии. Ток изменяющийся во времени по значению и направлению называется переменным. В практике применяют периодически изменяющийся по синусоидальному закону переменный ток.
33225. Внешние магнитные поля 13.91 KB
  Изменение направления тока в обмотке прибора приводит к перемагничиванию сердечника или сердечников и сила взаимодействия не меняет своего направления. К недостаткам прибора нужно отнести малую точность неравномерность шкалы зависимость показаний прибора от внешних магнитных полей и от частоты.
33226. Химические источники э. д. с. (аккумуляторы, элементы) 13.53 KB
  Если внести проводник с током в магнитном поле то в результате сложения магнитных полей магнита и проводника произойдет усиление результирующего магнитного поля с одной стороны проводника на чертеже сверху и ослабление магнитного поля с другой стороны проводника Правило левой рукидля определения направления силы действующей на проводник с током в магнитном поле. поле проводник с током: если расположить левую ладонь так чтобы вытянутые пальцы совпадали с направлением тока а силовые линии магн. поля входили в ладонь то отставленный...
33227. Электродинамический прибор, измерительный прибор 12.88 KB
  Состоит из измерительного преобразователя преобразующего измеряемую величину в переменный или постоянный ток и измерительного механизма электродинамической системы Мощность в цепи трехфазного тока может быть измерена с помощью одного двух и трех ваттметров.
33228. Измерительный трансформатор 13.46 KB
  Трансформаторы тока служат для преобразования тока большой величины в ток малой величины. Простейшим аппаратом ручного управления в электрических сетях постоянного и переменного тока являются рубильники. Они применяются в сетях до 500 в для замыкания и размыкания цепей при токах от 100 до 5000 а.
33229. Измерительный трансформатор напряжения 13.31 KB
  ИЗМЕРИТЕЛЬНЫЕ ТРАНСФОРМАТОРЫ НАПРЯЖЕНИЯ В сетях переменного тока для отделения измерительных приборов в целях безопасности от проводов высокого напряжения а также для расширения пределов измерения приборов применяются измерительные трансформаторы напряжения и тока. Измерительные трансформаторы напряжения по своему устройству принципиально не отличаются от устройства силовых трансформаторов служащих для питания ламп накаливания электродвигателей и т.
33230. Химический источник постоянного тока (гальванический элемент или аккумулятор) 13.69 KB
  Для получения постоянного тока используют также электрические машины генераторы постоянного тока. Источник тока это устройство в котором происходит преобразование какоголибо вида энергии в электрическую энергию. В любом источнике тока совершается работа по разделению положительно и отрицательно заряженных частиц которые накапливаются на полюсах источника.
33231. Энергосбережение 13.85 KB
  В четырехпроводной системе при несимметричной нагрузке необходимо включение трех ваттметров обмотки напряжений которых включаются между нулевым и соответствующим линейным проводом. Каждый ваттметр измеряет мощность одной фазы и суммарная мощность трехфазной системы равна сумме показаний трех ваттметров т. В трехпроводной системе при несимметричной нагрузке наиболее часто используют схему двух ваттметров которая не может быть использована в четырехпроводной системе. В схеме двух ваттметров обмотки напряжений каждого ваттметра соединены с...
33232. СОЕДИНЕНИЕ ЗВЕЗДОЙ И ТЕУГОЛЬНИК 14.55 KB
  ТРЕУГОЛЬНИК Треугольник такое соединение когда конец первой фазы соединяется с началом второй фазы конец второй фазы с началом третьей а конец третьей фазы соединяется с началом первой Обмотки трехфазного генератора могут быть соединены и другим способом: если конец первой обмотки соединить с началом второй конец второй обмотки с началом третьей и конец третьей с началом первой получим соединение треугольником. Соединение треугольником выполняется таким образом чтобы конец фазы А был соединен с началом фазы В конец фазы В...