16557

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

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

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

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

Русский

2013-06-22

152.5 KB

28 чел.

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


 

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

67814. Минеральная часть агропочв 70.59 KB
  Ассимиляция смектитового компонента это изменение состава и свойств пахотных горизонтов почвы при эрозии а также в ряде регионов при орошении мутными водами. Для дерново-среднеподзолистой супесчаной почвы характерно резкое увеличение количества каолинита по сравнению с содержанием гидрослюд...
67815. ПРОМИСЛОВІ РОБОТИ (ПР) 544 KB
  По типу систем керування сучасні перспективні промислові роботи поділяються на три: види так звані покоління: програмні адаптивні і інтелектуальні з елементами штучного інтелекту. Усі вони мають властивість швидкого перепрограмування на різні операції причому в першому...
67816. ОСНОВИ ОРГАНІЗАЦІЇ САНІТАРНО-ГІГІЄНІЧНОГО ТА ПРОТИЕПІДЕМІЧНОГО ЗАБЕЗПЕЧЕННЯ ВІЙСЬК 134.5 KB
  Висвітлити найбільш складні питання навчального матеріалу, актуальні питання теорії та практики, сучасні досягнення науки та техніки, сприяти розвитку творчого мислення студентів і формування у них сучасного світогляду, а також бути основою для організації та проведення...
67817. ПРАВО КОРИСТУВАННЯ ПРИРОДНИМИ РЕСУРСАМИ 215.5 KB
  Об’єктивне право природокористування – сукупність екологічних норм, які визначають підстави виникнення, зміни та припинення права природокористування, встановлюють комплекс прав та обов’язків природокористувачів, формують юридичні засоби захисту прав та інтересів суб’єктів природокористування.
67818. КІНЕМАТИКА ПРОМИСЛОВИХ РОБОТІВ 154.5 KB
  Кожне тіло щовільно рухається в просторі має ортогональну систему координат і 6 степенів вільності свободи можливість руху вздовж кожної з осей і обертання навколо них. Проте як видно з рисунку вся сукупність переміщень кінематичних ланок руки людини зводиться до транспортних переносних рухів...
67819. ОСНОВИ ОРГАНІЗАЦІЇ ЗАБЕЗПЕЧЕННЯ МЕДИЧНИМ МАЙНОМ 135 KB
  Забезпечення медичним майном і медичною технікою організується і здійснюється з метою безперервного і повного задоволення потреб частин, підрозділів в них для надання медичної допомоги пораненим та хворим і їх лікування, проведення санітарно-гігієнічних...
67820. ПРАВОВЕ РЕГУЛЮВАННЯ ВИКОРИСТАННЯ НАДР 82.5 KB
  Приблизна вартість промислових запасів основних видів корисних копалин дорівнює більше 15 трлн. Всі мінеральносировинні ресурси України сконцентровані в: родовищах корисних копалин нагромадженнях мінеральних речовин в надрах на поверхні землі в джерелах вод та газів на дні водоймищ які за кількістю якістю...
67821. ПРИВОДИ ПРОМИСЛОВИХ РОБОТІВ 142 KB
  Крім того тип приводу визначає і можливості системи керування або ступінь інтелектуальності робота. Для виконання загальної конкретної технологічної операції необхідне групове керування виконавчими двигунами приводу тобото з погляду керування привід робота розглядається як система.
67822. ПРАВОВЕ РЕГУЛЮВАННЯ ВИКОРИСТАННЯ ВОД 91 KB
  В Україні розроблено законодавство, яке регулює правову охорону та режим використання водних об’єктів України та покликане сприяти формуванню водно-екологічного правопорядку і забезпечення екологічної безпеки населення України, а також більш ефективному, науково обґрунтованому використанню...