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


 

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

53128. Збірник граматичних вправ з німецької мови для 6 класу 440.5 KB
  Sie waren jung und hatten Spaß! Präsens Heute bin ich alt und habe graue Haare. Perfekt Ich bin auch mal jung gewesen und habe keine grauen Haare gehabt. Präteritum Ich war auch mal jung und hatte keine grauen Haare.
53129. Внеклассное мероприятие по немецкому языку «Рождество в Германии» 1.31 MB
  Ведущий 1: Guten Tag, liebe Freunde! Heute feiern wir der hellste Tag - Weihnachten! Heute ist der schönste Tag im ganzen Jahr. Das ist der Weihnachtstag- Wie glänzt der Christbaum hell und klar. Wie freut sich die Kinderschar.
53130. Частини тіла (5 клас) 192.5 KB
  Heute beginnen wir ein neues Thema „Der Mensch“. Wir lernen, wie alle Körperteile heißen. Zuerst hört ihr ein Gedicht und sprecht nach.
53131. Федеративна Республіка Німеччина 70.5 KB
  Частина світу сторона світу країни карлики 25 держав колонізатори країни Західної Європи. Число Андрій Шевченко чудеса світу країни великий Велика сімка. Мотивація навчальної діяльності При знайомстві з регіоном Західної Європи ми зясували що країни цього регіону входять до числа розвинених країн світу а чотири з них Німеччина Італія Франція Великобританія члени Великої сімки. Інструктивна картка №1 Політологи фізико географи Загальні відомості про країну: назва; площа; форма правління; ...
53132. Свята в Німеччині та в Украіні 220.5 KB
  Guten Tag, liebe Kinder und liebe G?ste! Ich freue mich sehr, euch alle zu sehen und ich hoffe, dass ihr gute Zeit haben werdet. Feste und Br?uche spielen eine wichtige Rolle im Leben des Volkes. Und Deutsche haben Feiertage auch sehr gern. Der deutsche Dichter Joseph von Eichendorff hat?ber Feiertage ein Gedicht geschrieben...
53133. Leseverstehen. Das Märchen „Das Salz“ 52 KB
  Die dritte Tochter weinte und verließ ihren Vater. Aber bald sah man keine fröhlichen Menschen mehr. Brot ohne Salz, Fleisch ohne Salz, Gemüse ohne Salz -- das alles schmeckte den Menschen nicht. Sie hatten keinen Appetit mehr, sie wollten nicht mehr essen. Auch die Arbeit bereitete ihnen keine Freude mehr. Der König sah das alles. Das Essen schmeckte ihm auch nicht. Es gab kein Salz im Land mehr.
53135. Йоганн Вольфганг фон Гете – виразник ідей Просвітництва. Творчий і життєвий шлях письменника. Образ Фауста у літературі, музиці, малярстві 82.5 KB
  Liebe Freunde! Ich wünsche euch einen wunderschönen Guten Morgen und freue mich euch wiederzusehen. Wir werden heute über die Gestalt des Faust und den Unterstied dieser Gestalt im Werken von Goethe und Puschkin sprechen und wie diese Gestalt des Faust sich in der Musik und Malerei widergespiegelt hat.
53136. Гетьманщина наприкінці ХVІІ - у першій половині ХVІІІ ст 124.5 KB
  Підручники таблиця Іван Мазепа роздатковий матеріал. Мазепа біографія Дискусійний клуб Обери позицію: дати оцінку діяльності І. Мазепа історична постать неоднозначна таємничо загадкова і до цього часу. В літературних творах оспівано його образ...