36877

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

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

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

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

Русский

2013-09-23

527 KB

32 чел.

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


 

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

21260. Основи побудови обліку у будівельних підприємствах 191.5 KB
  З економічної точки зору для галузі будівництва можна виділити ряд особливостей які вливають на організацію аналітичного й синтетичного обліку: термін операційного циклу період будівництва може продовжуватися декілька звітних періодів; індивідуальний характер кожного об'єкта будівництва; мають місце великі залишки незавершеного виробництва зза довгого робочого періоду й одночасного ведення робіт на великій кількості об'єктів; обєкт будівництва знаходиться не за місцем розташування виконавця будівельномонтажних робіт БМР а на окремій...
21261. Облік у підрядчика. Облік у забудовника 597 KB
  Відображено витрати на транспортування у складі первісної вартості запасів 1000 20 22 631 685 4. Відображено витрати на транспортування у складі первісної вартості запасів 1000 20 22 631 685 8. Списано на загальновиробничі витрати нестачу запасів у межах норм природного збитку 7230 23 201 5. Списано на фінансові результати витрати 3259751195210 959 793 947 13.
21262. Особливості обліку на сільськогосподарських підприємствах 213 KB
  У цих умовах особливого значення набуває облік за допомогою якого забезпечується збереження сільськогосподарської власності і продукції правильне використання насінь кормів пального машин дотримання госпрозрахунку і підвищення рентабельності господарства. Виробництво продукції сільського господарства має тільки свої йому властиві особливості обліку. Це дозволяє точно обчислити собівартість продукції зерна кормів та іншої продукції сільського господарства. Тому фактичну собівартість продукції рослинництва обчислюють не щомісяця а лише...
21263. АУДИТ АКТИВІВ, ПАСИВІВ ТА ФІНАНСОВОЇ ЗВІТНОСТІ 213 KB
  АУДИТ СТАНУ БУХГАЛТЕРСЬКОГО ОБЛІКУ Й ФІНАНСОВОЇ ЗВІТНОСТІ 5. Таки комплекси виділяють відповідно до Плану рахунків бухгалтерського обліку тобто девять розділів та позабалансові рахунки; окремо бажано виділити Розрахунки з оплати праці. Запаси Аналітичний облік руху матеріальних цінностей на складах підприємства Документи по списанню матеріальних цінностей на витрати виробництва Документи по списанню нестач втрат та розкрадань матеріальних цінностей Облік МБП у запасі та експлуатації Зведений облік матеріальних цінностей Матеріали...
21264. АУДИТОРСЬКИЙ ВИСНОВОК ТА ІНШІ ПІДСУМКОВІ ДОКУМЕНТИ 55.5 KB
  Аудиторський висновок про бухгалтерську звітність економічного субєкта містить думку аудиторської фірми про достовірність цієї звітності яке має висловлювати оцінку аудиторської фірми відповідності у всіх суттєвих аспектах бухгалтерської звітності Закону України Про бухгалтерський облік та фінансову звітність в Україні Аудиторський висновок це лаконічний опис виявлених порушень помилок відхилень з оцінкою стану бухгалтерського обліку достовірності звітності та законності господарських операцій. Згідно з Законом України Про...
21265. Транспортная задача. Этапы построения решения транспортной задачи 474.5 KB
  Транспортная задача Т3возникает при планировании рациональных перевозок грузов загрузки оборудования и других организационноэкономических процессов. Требуется составить такой план перевозок откуда куда и сколько единиц груза везти чтобы все заявки были выполнены а общая стоимость всех перевозок минимальна. Матрицу X будем называть матрицей перевозок или планом грузоперевозок. Суммарное количество груза доставляемого в каждый ПН из всех ПО должно быть равно заявке поданной данным пунктом: 3...
21266. Понятие математической модели 342.5 KB
  И если ранее математический аппарат преимущественно использовался как инструмент расчета то сейчас экономика выдвигает другие задачи: рационального использования уже имеющегося сырья оборудования кадровых энергетических и прочих ресурсов; выбора наиболее выгодного варианта организации производственного процесса оптимальным образом. Эти задачи привели к появлению новых математических методов и направлений прикладной математики: теории игр теории массового обслуживания теории линейного и нелинейного программирования и др. Поэтому в...
21267. Аналіз рентабельності діяльності підприємства з метою її підвищення 574.5 KB
  Збільшення обсягу реалізації і поліпшення якості продукції 3. Резерви зниження собівартості продукції 3. Уособлення частини вартості продукції у вигляді витрат виступає в грошовому виразі як собівартість продукції. На формування прибутку як фінансового показника роботи підприємства впливає встановлений державою порядок формування витрат на виробництво продукції робіт послуг; обчислення й калькулювання собівартості продукції робіт послуг; визначення позареалізаційних прибутків і витрат; визначення балансового валового прибутку.
21268. Захист населення і територій від надзвичайних ситуацій 302 KB
  Актуальність проблеми забезпечення природнотехногенної безпеки населення і територій зумовлена тенденціями зростання втрат людей і шкоди територіям що спричиняються небезпечними природними явищами промисловими аваріями і катастрофами. Захист населення і територій від надзвичайних ситуацій техногенного та природного характеру це система організаційних технічних медикобіологічних фінансовоекономічних та інших заходів для запобігання та реагування на надзвичайні ситуації техногенного та природного характеру і ліквідації їх наслідків що...