16557

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

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

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

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

Русский

2013-06-22

152.5 KB

32 чел.

Лабораторная работа № 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


 

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

20305. Машинерия классической сцены 182.5 KB
  Машинерия классической сцены. Базанова УСТРОЙСТВО СЦЕНЫ Основные части сцены Сценическая коробка по вертикальному сечению распадается на три основные части: трюм планшет и колосники рис. Нижняя сцена используется для устройства люковспусков со сцены и для осуществления различных эффектов. Площадь трюма обычно равна площади основной сцены за вычетом места отведенного для склада мягких декораций сейфа.
20306. Золотой век русского искусства. 180.5 KB
  €œЗОЛОТОЙ ВЕК€РУССКОЙ КУЛЬТУРЫ В одном из своих произведений А. не зря называют €œзолотым веком€русской культуры. В русской литературе век классицизма был сравнительно короток и неярок в русской музыке почти не было классицизма зато в живописи и особенно в архитектуре он оставил подлинные шедевры. Константин Андреевич Тон 1794 в своем творчестве попытался возродить традиции древнерусской архитектуры.
20307. Комедия дель-арте 658.5 KB
  Комедия дельарте. Комедия дель арте [править] Материал из Википедии свободной энциклопедии Эта версия страницы ожидает проверки и может отличаться от последней подтверждённой проверенной 3 июня 2011. Сцена из представления комедии дель арте. Комедия дель арте итал.
20308. Планировка современной сцены 139.5 KB
  Планировка современной сцены. Работа над макетом и планировкой Воплощение замысла художника в пространстве начинается с компоновки и проверки расположения декораций на плане сцены. Немалую роль в этой работе играет точность имеющегося плана сцены. Реальные размеры сцены габариты установленного оборудования всегда отличаются от чертежей рабочего проекта по которым велось строительство.
20309. Серебряный век в русском искусстве 103.5 KB
  Новая концепция искусства 2. Литература музыка театр соединение видов искусства Заключение Литература Введение В России первой трети прошлого века произошел мощный духовный всплеск вбросивший в сокровищницу мировой культуры немало значительных идей и произведений в сферах религиозной и философской мысли всех видов искусства. На взлет творческой активности Серебряного века повлияло постоянно укрепляющееся ощущение наиболее чуткими мыслителями и художниками нарастающего глобального никогда не случавшегося еще в истории человечества...
20310. Театр эпохи Просвещения 920 KB
  Театр эпохи Просвещения. Западноевропейский театр в эпоху Просвещения Театр от греч. Родовое понятие театра подразделяется на виды театрального искусства: драматический театр оперный балетный театр пантомимы и т. Происхождение термина связано с древнегреческим античным театром где именно так назывались места в зрительном зале от греческого глагола теаомай – смотрю .
20311. Сценические эффекты в театре XIX века 55.5 KB
  Кроме этого появление электрических двигателей послужило мощным толчком для развития механического оборудования сцены. Классические люкипровалы превратились в подъемноопускные площадки и лифтовые сцены греческие выкатные площадки эккиклемы в платформы вывозящие в пределы игровой зоны целые декорационные комплексы небольшие круглые вращающиеся площадки времен театра Возрождения и барокко в различные системы поворотных кругов примитивные веревочные ручные подъемы в механизированные и немеханизированные штанкетные подъемы. Ответы на эти...
20312. Древнерусское искусство: архитектура, иконопись, литература, театр 242 KB
  С образованием государственности и принятием христианства из Византии на Русь пришли новые для нее виды монументальной живописи мозаика и фреска а также станковая живопись иконопись. Византия познакомила русских художников с новой для них техникой живописи дала им иконографический канон неизменность которого строго оберегалась церковью. Это в известной степени сковывало художественное творчество и предопределяло более длительное и устойчивое византийское влияние в живописи нежели в архитектуре. Самые ранние из сохранившихся...
20313. Театр эпохи Романтизма 174 KB
  Театр в эпоху романтизма 27 марта Международный день театра. Во Франции где были сильны традиции классицизма формирование сценического романтизма длилось дольше протекало в острой борьбе с классицизмом. В 1827 было опубликовано предисловие Гюго к его драме Кромвель теоретический манифест романтизма.