16557

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

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

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

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

Русский

2013-06-22

152.5 KB

35 чел.

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


 

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

1890. Задача синтеза 14.33 KB
  Задана система булевых функций. Задан или выбран по заданным критериям (быстродействие, надёжность, стоимость, условия эксплуатации) элементный базис проектирования – система логических элементов.
1891. Синтез комбинационных схем на ПЛМ 16.61 KB
  Процесс синтеза сводится к минимизации системы. Выбранные конъюнкции реализуем на очередной ПЛМ. Проектирование систем ПЛМ с учётом ограничений.
1892. Синтез комбинационных схем на мультиплексорах 23.54 KB
  Набор значений на адресных входах z1…zn определяет подключение к выходу одного из информационных входов, двоичный код номера которого совпадает с этим набором(z1-младшая переменная).
1893. Особенности синтеза многоуровневых схем. Методы вынесения за скобки и допустимых конфигураций 26.87 KB
  Многоуровневая реализация на основе скобочных форм. Особенности синтеза многоуровневых схем методом допустимых конфигураций (д.к.).
1894. Общая постановка задачи анализа схем. Универсальные и специальные методы анализа. Анализ схем в базисе И-НЕ 16.23 KB
  Проверка правильности схемы. Функциональный анализ. Алгоритм анализа схем на элементах И-НЕ методом построения эквивалентных схем.
1895. Логическое и временное моделирование схем. Методы параллельного и событийного моделирования. Понятие риска, классификация и методы его обнаружения 21.62 KB
  Моделирование КС заключается в том, что по функциональной схеме и входному набору (заданным значениям сигналов на всех входных полюсах схемы) вычисляются значения сигналов на выходах всех элементов схемы (внутренних и выходных).
1896. Неисправности в комбинационных схемах. Модель константных неисправностей. Особенности проявления неисправностей в схемах из элементов И-НЕ 24.47 KB
  Проверяющий и диагностирующий тесты. Процедура построения диагностирующего теста. Особенности проявления неисправностей в схемах из элементов И-НЕ. Процедура построения двухуровневого диагностирующего теста.
1897. МЭО и ВТО 748.34 KB
  Мировая торговля товарами и услугами. Международное движение капитала. Международные валютные расчеты. Система учета международных операций в форме платежного баланса. Национальная политика в области внешней торговли. ВТО в мировой экономической системе.
1898. Lessons In Electric Circuits, Volume V Reference 611.21 KB
  DC circuit equations and laws. Capacitor sizing equation. Series and parallel component equivalent values. Value of time constant in series RC and RL circuits. Calculating time at specied voltage or current.