16557

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

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

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

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

Русский

2013-06-22

152.5 KB

33 чел.

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


 

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

83143. Учебное пособие по дисциплине: Металлические конструкции 9.04 MB
  Строительство является одной из самых металлоемких отраслей народного хозяйства. Значительная часть стали расходуется на изготовление металлоконструкций, из которых возводятся автодорожные и железнодорожные мосты, каркасы промышленных и гражданских зданий, башни и мачты антенных устройств, опоры линий электропередачи...
83145. ВИКОРИСТАННЯ ДИДАКТИЧНИХ ІГОР НА УРОКАХ ОБРАЗОТВОРЧОГО МИСТЕЦТВА В СЕРЕДНІХ КЛАСАХ 218 KB
  Проблеми перебудови нашого суспільства нерозривно пов’язані з вирішенням завдань формування творчої особистості. Адже саме така особистість є справжнім творцем історії, оскільки весь шлях, який пройдено людством, – це безперервний процес творіння.
83146. ШЛЯХИ ВДОСКОНАЛЕННЯ БУХГАЛТЕРСЬКОГО ОБЛІКУ ВЛАСНОГО КАПІТАЛУ 88.05 KB
  Актуальність теми дослідження полягає в тому, що запровадження в економічне життя України ринкових відносин, провокує до розвитку окремих фірм не державного характеру, що в свою чергу вимагає грамотної політики в галузях управління цими фірмами, це стосується і обліку результатів діяльності підприємств.
83147. Роль интеллектуальной собственности в реформируемой экономике РФ 211 KB
  Интеллектуальная собственность и уровень инновационного развития экономики Российской Федерации, являются объектом исследования в данной работе. В качестве предмета исследования выступают отношения интеллектуальной собственности. Целью работы является изучение понятия интеллектуальной собственности...
83148. Диагностика и лечение хронического гастрита 299.34 KB
  При изучении влияния профессиональных факторов на течение хронического гастрита у подростков установлено, что в группе больных с тяжелыми видами труда, ухудшение в состоянии здоровья наблюдается значительно чаще, чем при благоприятных условиях работы.
83149. Конструпрование супергетеродинного радиоприемника 165.69 KB
  Осуществляет первичную частотную селекцию полезного сигнала ослабляет внеполосные сильные помехи обеспечивая линейное усиление последующих каскадов приёмника совместно с УРЧ осуществляет избирательность побочных каналов приёмника. Усилитель радио частоты УРЧ и далее по тексту предназначен для повышения чувствительности приёмника. Входная цепь и УРЧ осуществляет основную избирательность по зеркальному каналу образованным гетеродином их расчет будем производить исходя из заданной избирательности при полосе задерживания . Полосу...
83151. Контроль и учет в компании 85.24 KB
  Планы не всегда выполняются так, как было задумано. Люди не всегда принимают делегированные им права и обязанности. Руководству не всегда удается должным образом мотивировать людей на достижение поставленных целей. Меняются условия окружающей среды, и организация должна соответственно к ним адаптироваться.