16557

Простые и сложные вычисления в табличном процессоре MS Excel’2000/2003

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

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

Лабораторная работа № 11 Тема: Простые и сложные вычисления в табличном процессоре MS Excel’2000/2003. Цели работы: Закрепить прежние знания и умения по оформлению таблиц и построению диаграмм в MS Excel’2000/2003. Освоить применение относительной абсолютной и смешанной ссылок а т...

Русский

2013-06-22

152.5 KB

31 чел.

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

Тема: Простые и сложные вычисления в табличном процессоре MS Excel’2000/2003.

Цели работы: Закрепить прежние знания и умения по оформлению таблиц и построению диаграмм в MS Excel’2000/2003. Освоить применение относительной, абсолютной и смешанной ссылок, а также логических функций при вычислениях в MS Excel’2000/2003.

Содержание работы:

Применение относительной и абсолютной ссылки в простых и сложных вычислениях MS Excel’2000/2003.

Применение логических функций вычислениях  и условного форматирования в MS Excel’2000/2003.

Технология выполнение работы:

Задание 1.

  1.  Откройте новую книгу и скопируйте в нее лист «Продажи» из книги «Работа1_Excel».
  2.  Переименуйте лист – «Сумма_продаж».
  3.  Добавьте новые столбцы: «Продано на сумму, в руб, в у.е.», «курс у.е.». И новую строку: «Всего продано на сумму:».
  4.  Сделайте оформление новых элементов таблицы. Например, как показано на рис 1.
  5.  В ячейку G4 введите формулу для вычисления суммы проданного товара в рублях (количество проданного товара * цену 1 ед. товара): =D4*F4 (так как при копировании ссылки должны измениться, то применяем относительную ссылку).
  6.  Скопируйте формулу в ячейки G5:G7, используя Маркер Автозаполнения. Проанализируйте изменение формул в этих ячейках.
  7.  Для ячеек G4:G7 задайте формат данных – денежный, обозначение р.
  8.  В ячейку H4 введите формулу для вычисления суммы проданного товара в у.е. (продано на сумму в руб./курс у.е.). Учитывая, что курс у.е. периодически изменяется, поэтому необходимо ввести такую формулу, чтобы при изменении только одной ячейки - J4 (величина курса), происходил автоматический пересчет данных «продано на сумму в у.е.». Таким образом получается формула: =G4/$J$4 или =D4*F4/$J$4 (так как при копировании первая ссылка должны измениться, то применяем относительную ссылку, а вторая не должна измениться – абсолютную ссылку).
  9.  Скопируйте формулу в ячейки H4:H7, используя Маркер Автозаполнения. Проанализируйте изменение формул в этих ячейках.
  10.  Для ячеек H4: H7 задайте формат данных – денежный, обозначение евро - €.
  11.  В ячейках G8, H8 подсчитать на какую сумму всего продано товара в руб. и в у.е.. Для этого используйте автосуммирование: поставьте курсор в ячейку G8 и нажмите кнопку на панели инструментов: , проверить правильность формулы и нажать Enter.
  12.  Установите соответствующий формат для ячеек G8, H8. Измените цвет для текста в этих ячейках.
  13.  В результате должна получиться примерно таблица, показанная на рисунке 1:

Рисунок 1. Таблица «Продажи» после всех вычислений.

  1.  Построить диаграмму, отражающую долю суммы продажи каждого товара в руб. от общей суммы.
  2.  Сохраните книгу в личной папке по именем «Работа3_Excel».

Задание 2. Рассчитать размер премии работникам обувной фабрики «Юничел» за ноябрь 2005года, учитывая, что в этом месяце 26 рабочих дней.

На фабрике три цеха: раскройный, заготовочный, пошивочный. В каждом цехе работает по 10 человек, из них: 1 мастер, 1 помощник мастера, 1 грущик, остальные сдельщики. Оклад мастера равен 3500 рублей, помощника мастера – 3000 рублей, грущика – 2000 рублей, сдельщиков – 2500 рублей. Кроме того задана норма выработки пар в день для каждого сдельщика = 80 пар в день. Мастер каждого цеха подает данные о норме выработке пар в день каждого сдельщика по факту.

Рассчитать для каждого работника размер премии, учитывая, что премия начисляется всем в зависимости от плана производства, а сдельщикам еще дополнительно в зависимости от выполнения нормы выработки в день.

Этапы решение задачи:

1 этап. Анализ задачи.

Исходные данные: количество рабочих дней, наименование цеха, фамилия, имя, отчество, должность, оклад, норма выработки пар в день, норма выработки пар по факту в месяц.

Требуется рассчитать:

  1.  План производства за месяц по плану =общей норме выработки пар в день по плану всех сдельщиков каждого цеха * на количество рабочих дней в месяце.
  2.  План производства за месяц по факту =общей норме выработки пар за месяц по факту всех сдельщиков каждого цеха.
  3.  Размер премии = размер премии по плану + размер премии по норме выработки, где
    1.  размер премии по плану рассчитывается следующим образом:

если план производства за месяц по плану = по факту, то премия = 15% от оклада,

если план производства за месяц по плану < по факту <=150% от плана, то премия = 30% от оклада, 

если план производства за месяц по плану > 150% от плана, то премия = 35% от оклада.

  1.  размер премии по норме выработки рассчитывается следующим образом:

если норма выработки по плану = по факту, то премия = 10% от оклада,

если норма выработки по плану > по факту, то премия = 15% от оклада.

2 этап. Моделирование таблицы.

Оформим таблицу средствами MS Excel’2000/2003. Для этого:

  1.  Перейдите на новый лист и назовите его «Расчет премии».
  2.  Установите параметры страницы: ориентация - страницы Альбомная, верхнее и нижнее поле – 1,5 см, левое и правое – 2 см.
  3.  Оформите заголовок и строку заголовков таблицы (рисунок 2). Для всего текста установить тип шрифта Times New Roman, для текста 1 строки установить размер шрифта – 14, для строки заголовков размер шрифта – 12, начертание – полужирный, выравнивание по центру, для остального текста размер шрифта – 11, выравнивание по левому краю. А так же задать границу ячейкам.
  4.  Для ячейки D1 установить формат – дата, тип –только название месяца. Ввести в ячейку номер месяца – 11.
  5.  В ячейку J1 ввести количество рабочих дней в ноябре -26.
  6.  Заполнить таблицу исходными данными.
  7.  Выполнить Автоподбор ширины столбцов и Автоподбор высоты строк.
  8.  Установите вид документа – Разметка страницы (Вид/ Разметка страницы).
  9.  Сделайте просмотр таблицы, нажав кнопку на панели инструментов «Стандартная» . Внесите изменения в таблицу такие, чтобы вся таблица умещалась на одной странице.
  10.  Сохранить изменения.

Рисунок 2. Таблица «Расчет премии за ноябрь 2005 г»

3 этап. Выполнение расчетов.

Для того чтобы рассчитать премию для каждого работника необходимо выполнить промежуточные расчеты:

  1.  На второй странице рабочего листа «Расчет премии» внести условия расчетов премии (рисунок 3). Для ячеек С35, С36, С37, F35, F36 установить процентный формат.

Рисунок 3. Вид страницы 2 рабочего листа «Расчет премии».

  1.  В ячейке G5 рассчитаем план производства за месяц по плану, используя функцию СУММ: =СУММ(ячейки нормы в день раскройного цеха)*$J$1. Скопируйте формулу в соответствующие ячейки остальных цехов.
  2.  В ячейке H5 рассчитаем план производства за месяц по факту, используя функцию СУММ: =СУММ(ячейки нормы выработки по факту за месяц). Скопируйте формулу в соответствующие ячейки остальных цехов.
  3.  В ячейке I5 рассчитаем размер премии по плану, используя логические функции ЕСЛИ, И, по алгоритму показанному на рисунке 4. Где

H$5 –размер плана производства за месяц,

G$5 - размер плана производства за месяц по факту,

D5 – размер оклада,

$C$35, $C$36, $C$37 – процент премии по плану от оклада.

  1.  Для ввода формулы необходимо:
    •  вызвать Мастер функции, нажав в Строке формул кнопку или выполнить команду Вставка/Функция,
    •  выбрать категорию – Логические, функцию – ЕСЛИ,
    •  в диалоговом окне Аргументы функции заполнить поля, в поле Лог_выражение ввести лог_выражение1, в поле Значение_если_истина ввести значение_если_истина1, в поле Значение_если_ложь ввести слово - если и нажать Ок.
    •  в строке формул выделить слово - если и нажать кнопку , в диалоговом окне Аргументы функции заполнить поля в соответствии с алгоритмом для вложенной функции ЕСЛИ 1 уровня,
    •  аналогично ввести аргументы вложенной функции ЕСЛИ 2 уровня, для которой в поле Значение_если_ложь ввести значение_если_ложь3 и нажать Ок.

В результате в строке формул должна получиться следующая формула: =ЕСЛИ(H$5<G$5;0;ЕСЛИ(H$5=G$5;D5*$C$35;ЕСЛИ(И(H$5>G$5;H$5<=G$5*1,5);D5*$C$36;D5*$C$37))). Скопируйте формулу в остальные ячейки по раскройному цеху, по заготовочному и пошивочному, изменив адреса ячеек в логических выражениях.

Рисунок 4. Алгоритм расчета премии по плану.

  1.  В ячейке J5 рассчитаем размер премии по норме выработки:

=ЕСЛИ(ИЛИ(F5<E5*$J$1;F5=0);0;ЕСЛИ(F5=E5*$J$1;D5*$F$35;D5*$F$36)). Где 

F5 – норма выработки пар по факту в месяц,

E5*$J$1 - норма выработки пар в день*количество рабочих дней,

$F$35, $F$36 - процент премии по выработке от оклада.

Скопируйте формулу в остальные ячейки.

  1.  В ячейке K5 рассчитаем общий размер премии: =СУММ(I5:J5).
  2.  Сохранить изменения.
  3.  Установим для ячеек, в которых рассчитан размер плана производства за месяц по факту, условное форматирование. То есть если размер плана производства за месяц по факту>= размера плана производства за месяц, то ячейку залить, например, желтым цветам и установить начертание шрифта – полужирное, а если размер плана производства за месяц по факту< размера плана производства за месяц, то установить начертание шрифта – полужирное и цвет шрифта, например, красный. Для этого:
    •  выделить диапазон ячеек H5:H23,
    •  выполнить команду меню Формат/Условное форматирование, в открывшемся диалоговом окне установить параметры для Условия 1 указанные на рисунке 5, нажать кнопку А также,
    •  в дополнительном окне установить параметры для Условия 2указанные на рисунке 5, нажать кнопку А также, установить параметры для Условия 3, нажать ОК.
  4.  Оцените результат. Измените размеры нормы выработки пар по факту в месяц. Какие произошли изменения?
  5.  Сохранить изменения.

Рисунок 5. Установка параметров условного форматирования.

PAGE 1


 

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

52503. Диференціація – умова успішного навчання 95.5 KB
  Виконувати диференційовані завдання систематично майже на кожному уроці уникаючи стандарту. Добирати завдання з поступовим ускладненням для сильніших і зменшувати міру допомоги для слабших учнів. Диференціацію бажано застосовувати під час фронтальної роботи коли учні розв’язують загальні навчальні завдання. Добирати варіативні завдання що полегшують роботу вчителя і учнів в перевірці цих завдань.
52504. Практический материал для развития речевого дыхания при открытой ринолалии 75 KB
  Речевое дыхание характеризуется коротким вдохом и удлиненным выдохом. При проведении работы по нормолизации дыхания необходимо соблюдать ряд правил: заниматься только в проветренном помещении до еды; строго дозировать количество и темп упражнений при недомогании лучше отложить занятия не делать слишком большой вдох; следить чтобы ребенок не напрягал плечи шею; ребенок должен ощущать движения диафрагмы межреберные мышцы мышцы нижней части живота; движения необходимо производить плавно под счет тихо. Сделать полный...
52505. Значение дыхания. Строение и функции органов дыхания. Голосовой аппарат 378 KB
  Строение и функции органов дыхания. Разработка содержит: конспект урока с поэтапным описанием методов и форм работы учителя и учащихся продолжительностью этапов урока в виде таблицы и ожидаемыми результатами презентации: Строение органов дыхания Эрудицион созданы в PowerPoint видеофайл Строение органов дыхания формат МР4 аудиозапись отрывков из оперы Джузеппе Верди. Тема: Значение дыхания.
52506. Болезни органов дыхания. Профилактика. Вредное влияние курения, загрязнения окружающей среды 37.5 KB
  Взрослым и детям для предупреждения этой болезни делают прививки. Возбудитель попадает в организм воздушнокапельным путем может длительное время находиться в организме человека н вызывая болезни. Ее положительная реакция говорит о присутствии возбудители этой болезни в организме.
52507. Дихальні здоров’єзберігаючі технології на уроках читання 48 KB
  Технологія Стоніжка. Технологія Свічка. Технологія Проколотий м’яч. Технологія Передай звуками для очищення легенів.
52508. ДИХАННЯ 262 KB
  9 КЛАС Цикл уроків з розділу Біологія людини у 9 класі на тему Дихання розроблено згідно з Програмою з біології для 7 – 11 класів загальноосвітніх навчальних закладів що затверджена Міністерством освіти і науки України 2005 року. Запропонована методична розробка уроків теми Дихання відповідає підручнику С. Планування навчального матеріалу з теми Дихання 6 годин № уроку Тема уроку 1.
52509. ТАЄМНИЦЯ РІЗДВА. УРОК-ЗУСТРІЧ З ГЕРОЯМИ ТВОРУ Ч.ДІККЕНСА «РІЗДВЯНА ПІСНЯ В ПРОЗІ» 80 KB
  The teacher: Charles Dickens, a famous British writer, also wrote about Christmas in his book Christmas Carol. He was born in 1812. He lived in the south of England when he was a little boy. His father was a clever man, but he was very poor. Charles had brothers and sisters, but he did not often played with them.
52510. Знания по философии и логике. Шуранов Б.М. 1.84 MB
  В трактате представлены шесть основных направлений исследований, создание каковых стало итогом научной деятельности автора: запредельная метафизика, 3-членная онтология, метафизика Вселенной, наука о бесконечности, Онтологический определитель, нематериальное естествознание. Из статей по логике обратим внимание читателя на Заполнение промежутка между финитными и трансфинитными числами: решение проблемы бесконечности, Логика без отношения следования и скрытые выводы...
52511. ПРОФЕССИОНАЛЬНАЯ ДЕЯТЕЛЬНОСТЬ УЧИТЕЛЯ ПО ВОСПИТАНИЮ ЭМОЦИОНАЛЬНО-ПОЛОЖИТЕЛЬНОГО ОТНОШЕНИЯ ПОДРОСТКОВ К ОБУЧЕНИЮ 876.5 KB
  профессиональная деятельность учителя по воспитанию у подростков эмоционально-положительного отношения к обучению будет эффективной, если системообразующим фактором этого процесса будет выступать экологически ориентированная педагогика...