Читайте также:
|
ЛАБОРАТОРНАЯ РАБОТА 18 - 19
|
По дисциплине __________________________________________________________
________________________________________________________________________
(наименование учебной дисциплины согласно учебному плану)
ПОЯСНИТЕЛЬНАЯ ЗАПИСКА
|
________________________________________________________________________
________________________________________________________________________
________________________________________________________________________
| |||
| |||
Автор: студент гр. _______ ____________________ /_______________/
(подпись) (Ф.И.О.)
ОЦЕНКА: _____________
Дата: ___________________
ПРОВЕРИЛ
|
|
(должность) (подпись) (Ф.И.О.)
Санкт-Петербург
1999 год
Аннотация
Пояснительная записка представляет собой отчет о выполнении лабораторной работы № 18-19. В ней рассматриваются вопросы профессионального оформления документов с элементами простых расчетов средствами пакета Microsoft Excel 97 на примере трех задач.
Страниц 12, таблиц 1, рисунков 9, приложений 2.
The summary
The given report contains the description of the three tasks’ decisions, which conditions and results are written down as spreadsheets of Excel 97.
Pages 11, tables 1, figures 9, appendices 2.
Оглавление
Введение............................................................................................................................... 3
1. Платежная ведомость................................................................................................... 3
1.1. Постановка задачи.................................................................................................. 3
1.2. Требования к решению.......................................................................................... 3
1.3. Анализ задачи......................................................................................................... 4
1.4. Описание решения задачи..................................................................................... 4
1.5. Выводы.................................................................................................................... 5
2. Выбор оптимального бизнеса........................................................................................ 6
2.1. Постановка задачи..................................................................................................... 6
2.2. Требования к решению............................................................................................. 6
2.3. Анализ задачи............................................................................................................ 7
2.4. Описание решения задачи........................................................................................ 7
2.5. Выводы....................................................................................................................... 8
3. Фигура с минимальным периметром............................................................................ 9
3.1. Постановка задачи..................................................................................................... 9
3.2. Требования к решению............................................................................................. 9
3.3. Анализ задачи............................................................................................................ 9
3.4. Описание решения задачи...................................................................................... 10
3.5. Выводы..................................................................................................................... 11
Заключение......................................................................................................................... 11
Библиографический список............................................................................................. 11
Приложение 1.................................................................................................................... 12
Приложение 2.................................................................................................................... 13
Введение
В настоящее время большое значение придается умению использовать компьютерные технологии для выполнения расчетов различной сложности, для организации безошибочного документооборота как внутри одной фирмы, так и между различными юридическими лицами.
При решении подобных задач широко применяются офисные приложения Word 97 и Excel 97, входящие в состав весьма популярного пакета MS Office 97.
В данной лабораторной работе на примере задач, содержащих небольшой объем исходных данных, рассматриваются основные приемы оформления документов и выполнения простых расчетов с помощью Excel 97. Большое внимание уделяется обеспечению автоматического изменения результатов расчета при изменении исходных данных.
В первой задаче иллюстрируется составление платежной ведомости на примере бригады из четырех человек. В следующей задаче осуществляется выбор оптимального бизнеса по критерию «максимум часового заработка». В третьей задаче выбирается огород с самым коротким забором из четырех, имеющих одинаковую площадь и различную форму.
При выполнении работы были использованы материалы лекций, рекомендованные источники [1-3]. Результаты решения задач, приведены в приложениях 1, 2.
1. Платежная ведомость
В этом разделе пояснительной записки приводится решение задачи составления платежной ведомости для бригады из четырех человек
1.1. Постановка задачи
В составе бригады трое рабочих: Иванов Б.И., Петров О.Д., Сидоров П.Г. и бригадир Яковлев Д.К. (всего 4 человека). Заработок всей бригады – 5600 руб. Доплата бригадиру составляет 1000 руб. Требуется распределить заработанные бригадой деньги между ее членами и составить платежную ведомость.
1.2. Требования к решению
Для решения задачи необходимо использовать Excel 97 с соблюдением следующих требований:
q решение разместить на одном рабочем листе;
q все исходные данные представить в верхней части рабочего листа;
q результат решения – платежную ведомость –разместить под исходными данными;
q для задачи и результата ее решения предусмотреть заголовки, которые следует подходящим образом отформатировать;
q в последней строке подвести суммарный итог выплачиваемых денег, который должен совпадать с заработком всей бригады;
q для удобства работы переименовать рабочий лист в соответствии с размещенной на нем информацией;
q после окончания форматирования платежной ведомости отменить показ сетки на рабочем листе;
q обеспечить автоматическое изменение результата решения при изменении исходных данных.
Последнее требование является наиболее важным. Оно касается не только числовых исходных данных, но и текстовых (например, фамилий членов бригады и их профессий).
1.3. Анализ задачи
Для выполнения расчетов, связанных с распределением денег между членами бригады, нужно составить соответствующие формулы.
Введем следующие обозначения: S – заработок всей бригады, D – доплата бригадиру, n – число членов бригады, Pбр, Pраб – заработки бригадира и одного рабочего соответственно.
Предполагая, что все рабочие выполнили один и тот же объем работы, можно вычислить заработок одного рабочего по формуле
Зная величину доплаты бригадиру и заработок одного рабочего можно для вычисления заработка бригадира составить формулу
Учитывая тот факт, что заработок должен быть положительным, приходим к выводу, что числитель в формуле (1.1) больше нуля и, следовательно, для разрешимости задачи должно выполняться условие S>D. При постановке задачи (см. раздел 1.1) заданы значения S=5600, D=1000. Поэтому заключаем, что сформулированная задача имеет решение.
1.4. Описание решения задачи
Фрагмент рабочего листа, на котором представлено решение задачи о бригаде, составляют ячейки интервала A1:E16 (см. рис. 1.1).
В ячейку A1 введен заголовок всей задачи. После ввода данных и формул в остальные ячейки указанного интервала было произведено форматирование заголовка, затем объединение ячеек A1:D1 и центрирование заголовка.
Исходные данные введены в соответствующие ячейки интервала A3:D7, причем фамилии бригадира и рабочих занимают отдельные ячейки. Например, фамилия рабочего Петрова О.Д. помещена в ячейку C7. Это сделано для того, чтобы обеспечить возможность автоматического изменения фамилий в платежной ведомости при их изменении в исходных данных.
Заголовок платежной ведомости первоначально введен в ячейку A9. Затем он был отформатирован аналогично заголовку задачи.
Все ячейки интервала A11:C14, а также ячейка C15 являются зависимыми – в них введены соответствующие формулы Excel. Рассмотрим их подробнее.
Формулы в «текстовых» ячейках интервала A11:B14 очень просты: они состоят из символа «равно» (=) и ссылки на соответствующую ячейку. Например, в ячейку A11 помещена формула =B7. Отметим, что формула в ячейке B11 содержит абсолютную ссылку на ячейку A7 и имеет вид =$A$7. Это необходимо для того, чтобы при копировании этой формулы в ячейки B12 и B13 не произошло автоматического изменения ссылок на ячейки.
Формула Excel, введенная в ячейку C11, имеет вид: =($C$3-$C$4)/ $C$5 и основана на формуле (1.1), полученной при анализе задачи (см. подраздел 1.3). Необходимость абсолютных ссылок на ячейки в формуле объясняется тем, что при ее копировании в ячейки C12 и C13 данные должны браться из тех же самых ячеек с исходной информацией. При вводе формулы Excel в ячейку C14 была использована математическая формула (1.2), полученная при анализе задачи.
 |
В соответствии с требованиями к решению задачи рабочий лист был переименован (см. ярлык листа Бригада), отменен показ сетки и проведено несколько горизонтальных линий в таблице при помощи вкладки Граница команды Ячейки… из меню Формат (см. рис. 1.1).
1.5. Выводы
Несмотря на малый объем исходной информации и простоту условий задачи, она весьма полезна для получения первоначальных навыков, позволяющих создавать профессионально оформленные документы Excel типа платежной ведомости. Нужно особо подчеркнуть, что поскольку такие документы должны составляться регулярно перед каждой выплатой заработной платы, имеет большое значение возможность автоматического изменения зависимых величин при обновлении исходных данных.
В данной задаче не затрагиваются вопросы налогообложения и других вычетов из заработка. Ясно, что эти вопросы являются предметом отдельного рассмотрения и могут быть предложены в больших по объему работах (например, в курсовых работах) студентам экономических специальностей. Другое направление усложнения условий задачи и приближения их к реальным потребностям – это переход от масштабов отдельной бригады к более крупным подразделениям и к предприятию в целом.
2. Выбор оптимального бизнеса
В этом разделе пояснительной записки представлено решение задачи выбора оптимального бизнеса по критерию «максимум часового заработка».
2.1. Постановка задачи
Для каждого из рассматриваемых видов бизнеса в области торговли и сервиса заданы:
q цена одной единицы товара или услуги,
q количество имеющихся единиц товаров или услуг,
q время, необходимое для продажи товаров или оказания услуг,
q выручка от продажи товаров или оказания услуг.
Требуется определить тот вид бизнеса, для которого часовой заработок максимален.
Необходимые для решения числовые данные и названия видов бизнеса указаны в таблице (см. табл. 2.1).
Таблица 2.1
Исходные данные для выбора оптимального бизнеса
| Вид бизнеса | Цена за единицу, руб. | Время, час. | Число единиц | Выручка, % |
| Продажа газет | 0,9 | |||
| Продажа бананов | 5,4 | |||
| Мойка автомашин |
2.2. Требования к решению
Для решения задачи необходимо использовать Excel 97 с соблюдением следующих требований:
q все решение разместить на одном рабочем листе;
q таблицу исходных данных представить в верхней части рабочего листа;
q таблицу результатов решения разместить под исходными данными;
q задачу, исходные данные и результат решения снабдить заголовками, которые следует подходящим образом отформатировать;
q в последнем столбце «Лучший бизнес» таблицы результатов обеспечить автоматическое появление символа «*» в строке, соответствующей оптимальному виду бизнеса;
q для удобства работы переименовать рабочий лист в соответствии с размещенной на нем информацией;
q после окончания форматирования отменить показ сетки на рабочем листе;
q обеспечить автоматическое изменение результата решения при изменении исходных данных.
Последнее требование является наиболее важным. Оно касается не только числовых исходных данных, но и текстовых (например, названий видов бизнеса).
Подчеркнем также, что символ «*» должен появляться только в строках самого выгодного бизнеса с точки зрения выбранного критерия. В остальных ячейках столбца «Лучший бизнес» не должно быть видимых символов.
2.3. Анализ задачи
Составим формулы, необходимые для решения задачи выбора наилучшего бизнеса с учетом часового заработка.
Введем обозначения: n – общее количество видов бизнеса, i – порядковый номер бизнеса в исходной таблице, bi – название i - го бизнеса, ci – цена за единицу товара или услуг для i - го бизнеса, ki – число единиц товаров или услуг для i - го бизнеса, ti – время продажи всех товаров или оказания всех услуг для i - го бизнеса, pi – доля выручки для i - го бизнеса, si – искомый символ в столбце «Лучший бизнес» для i – го бизнеса, ri часовой заработок для i го бизнеса.
Исходя из понятия часового заработка, для его вычисления можно применить формулу:
Искомый символ si – это «*» для оптимального бизнеса или пробел для остальных видов бизнеса. Поскольку критерием для определения лучшего бизнеса служит часовой заработок, формулу для определения указанного выше символа можно записать так
Из формулы (2.1) следует, что время ti продажи товаров или оказания услуг не должно обращаться в нуль для любого вида бизнеса. Поскольку исходные данные удовлетворяют этому условию (см. табл. 2.1), поставленная задача имеет решение и его можно найти по формулам (2.1) – (2.2).
2.4. Описание решения задачи
Фрагмент рабочего листа, на котором представлено решение задачи о бригаде, составляют ячейки интервала A1:E14 (см. рис. 2.1).
В ячейку A1 введен заголовок задачи. После ввода данных и формул в остальные ячейки указанного интервала было произведено форматирование заголовка, затем объединение ячеек A1:E1 и центрирование заголовка.
Таблица 2.1 с исходными данными на рисунке 2.1 размещена в интервале ячеек A3:E7, причем заголовок таблицы первоначально введен в ячейку A3, а затем отформатирован аналогично заголовку задачи. Отметим, что для чисел в ячейках интервала E5:E7 применен формат Процентный, поэтому символ процента появляется автоматически. Преобразование таких чисел в доли приложение Excel делает автоматически при выполнении соответствующих расчетов.
Результаты решения в табличной форме представлены в ячейках интервала A9:С13. Заголовок создан и отформатирован аналогично остальным заголовкам. Зависимыми ячейками являются все ячейки интервала A11:C13 и ячейка A10. Формулы, введенные в ячейки с названиями бизнеса, содержат символ «=» и ссылку на соответствующую ячейку в таблице с исходными данными. Например, формула Excel в ячейке A12 выглядит так =$A6. Здесь использована смешанная ссылка на ячейку, так как при копировании формулы должен изменяться только номер строки, а столбец должен оставаться постоянным.
Формула в ячейке B11 имеет вид: =$B5*$D5/$C5*$E5 и соответствует формуле (2.1) подраздела 2.3. Здесь тоже используются смешанные ссылки на ячейки с исходными данными, что позволяет скопировать эту формулу в ячейки B12 и B13.
 |
=ЕСЛИ(B11=МАКС($B$11:$B$13);"*";" ")
Внешняя функция ЕСЛИ имеет три аргумента, которые друг от друга отделены точкой с запятой (;). Первый аргумент задает логическое условие, при выполнении которого функция возвращает значение, указанное во втором аргументе. Третий аргумент задает значение, возвращаемое функцией ЕСЛИ при невыполнении условия, указанного первым аргументом. В состав логического условия входит функция МАКС, аргумент которой содержит абсолютную ссылку на интервал ячеек, среди содержимого которых нужно выбрать максимальное значение.
Формулы в ячейках C12 и C13 аналогичны и получены при помощи копирования рассмотренной формулы.
2.5. Выводы
Беглое знакомство с исходными данными (см. табл. 2.1) может привести к неверному выводу о том, что самым выгодным видом бизнеса является мойка автомашин. К такому заключению может привести наибольшая выручка и самая высокая цена единицы этой услуги.
Проведенные с помощью Excel расчеты показали, что этот вывод ошибочен и что наиболее выгодным видом бизнеса является продажа бананов. Причиной ошибки является игнорирование фактора времени на оказание торговых или сервисных услуг, который для мойки автомашин является самым неблагоприятным.
3. Фигура с минимальным периметром
В этом разделе пояснительной записки на примере выбора формы огорода приводится решение задачи определения плоской фигуры заданной площади, имеющей наименьший периметр
3.1. Постановка задачи
Четыре огорода в форме круга, квадрата и двух прямоугольников с отношением сторон 2:1 и 3:1 имеют одинаковую заданную площадь 2000 м2. Определить огород, вокруг которого забор будет самым коротким.
3.2. Требования к решению
Для решения задачи необходимо использовать Excel 97 с соблюдением следующих требований:
q полное решение разместить на одном рабочем листе;
q заданную площадь огородов представить в верхней части рабочего листа;
q таблицу результатов решения разместить под исходными данными;
q задачу снабдить заголовком, который следует подходящим образом отформатировать;
q в качестве итога расчетов предусмотреть ячейку с длиной самого короткого забора и ячейку с автоматическим указанием формы соответствующего огорода;
q для удобства работы переименовать рабочий лист в соответствии с размещенной на нем информацией;
q после окончания форматирования отменить показ сетки на рабочем листе;
q обеспечить автоматическое изменение результата решения при изменении исходных данных.
Последнее требование является наиболее важным. Оно касается не только числовых исходных данных, но и текстовых (например, форм огородов).
3.3. Анализ задачи
Обозначим заданную площадь огородов буквой S. Составим формулы для вычисления длины заборов вокруг огородов, которая совпадает с периметром соответствующей фигуры.
Рассмотрим сначала огород в форме круга. Поскольку для круга с диаметром D периметр равен длине L окружности, можем написать следующие расчетные формулы
Для квадрата со стороной a периметр P можно вычислить с помощью следующих формул
Переходим к формулам для двух огородов в форме прямоугольников. Обозначим через b меньшую сторону прямоугольника, а через q отношение его сторон (для первого прямоугольника q=2, для второго – q=3). Тогда формулы для вычисления периметра в случае прямоугольника можно записать так
Рассмотрим теперь формулы для выбора самого короткого забора вокруг четырех огородов. Пусть i – порядковый номер огорода, Pi – периметр i - го огорода, Pmin – искомый минимальный периметр. Тогда можно написать формулу
Обозначим буквой T – форму огорода с самым коротким забором и напишем формулу для ее автоматического вычисления
Легко проверить, что условия задачи позволяют выполнить расчеты по формулам (3.1)-(3.5) и найти решение поставленной задачи.
3.4. Описание решения задачи
Фрагмент рабочего листа Excel с решением задачи об огородах (см. рис. 3.1) дает пример возможного оформления результатов вычислений.
 |
Ввод и форматирование заголовка произведено так, как описано в задачах о платежной ведомости и об оптимальном бизнесе. Заданная площадь огородов введена в ячейку B3. Расчеты, связанные с определением длины заборов вокруг огородов, представлены в табличной форме в ячейках интервала A4:D8. Первая колонка таблицы представляет варианты формы огородов. Длина заборов (периметры), вычисленная по формулам (3.1)-(3.3), видна во втором столбце таблицы. Вспомогательные величины вычислены в ячейках интервала D5:D8 и вместе с кратким пояснением в ячейках C5:C8 образуют третий столбец таблицы (см. рис. 3.1). Например, для квадрата в ячейку D6 введена формула =КОРЕНЬ(B3), обеспечивающая вычисление стороны, а в ячейку B6 формула =4*D6 для вычисления периметра. Копирование этих формул в другие ячейки не производилось, поэтому в них можно использовать ссылки на ячейки любого типа. Основу указанных двух формул составляют формулы (3.2) из подраздела 3.3.
Наибольший интерес представляют формулы в ячейках B10 и C10. В ячейку B10 введена формула =МИН($B$5:$B$8), реализующая вычисление минимального периметра Pmin по формуле (3.4). Более сложна формула в ячейке C10
=ЕСЛИ(B5=B10;A5;ЕСЛИ(B6=B10;A6;ЕСЛИ(B7=B10;A7;A8)))
Она реализует выбор варианта формы огорода с минимальным периметром в точном соответствии с формулой (3.5). Пояснения по использованию логической функции ЕСЛИ имеются в подразделе 2.4.
3.5. Выводы
Результаты решения задачи соответствуют известному математическому факту: чем более «правильную» форму имеет плоская фигура заданной площади, тем меньше ее периметр. Периметры огородов в форме круга, квадрата и двух прямоугольников (см. рис. 3.1) увеличиваются по мере уклонения их формы от «правильной». Наименьший периметр 158,53 м имеет огород в форме круга. Наибольший периметр 206,56 м у прямоугольника с отношением сторон 3:1. Следовательно, по сравнению с круглым огородом забор для второго прямоугольного огорода на 48,03 м длиннее. Соответственно увеличиваются затраты на его строительство.
На практике круглые огороды не очень распространены. Поэтому из трех других вариантов формы следует выбрать квадрат, у которого периметр меньше, чем у остальных.
Заключение
Организация правильного документооборота имеет большое значение в деятельности современной фирмы с любой формой собственности. Главное значение, конечно, имеет содержание документов, т.е. представленная в них информация. Но если документ плохо оформлен и не имеет четкой логической структуры, использовать его для нахождения в нем необходимой информации сложно.
Многие документы (платежные ведомости, справки для налоговой инспекции, типовые договоры и др.) должны составляться регулярно и некоторая часть содержащейся в них информации каждый раз изменяется. Форма же этих документов остается неизменной достаточно долго.
Рассмотренные в данной работе задачи помогают понять, как создаются современные документы и получить начальные навыки их практического создания.
Библиографический список
1. Винтер П. Microsoft Word 97: справочник – СПб: Питер, 1998. – 320 с.
2. Гончаров А. Excel 97 в примерах – СПб: Питер, 1997. – 336 с.
3. Йозеф Штайнер, Роберт Валентин Windows 95: справочник / Пер. с нем. – М: Восточная Книжная Компания, 1997. – 352 с.
4. Кокс Дж., Дадлей К., Урбан П. Microsoft Excel 97: краткий курс – СПб: Питер, 1998. – 256 с.
5. Основы современных компьютерных технологий: Учебное пособие /Под ред. проф. Хомоненко А. Д. Авторы: Артамонов Б. Н., Брякалов Г. А., Гофман В. Э. и др.–СПб.: КОРОНА принт, 1998.
Приложение 1.
Образцы решения задач 1 - 3.
Задача 1.
Рис.П.1.1 Расчетная таблица Excel задачи 1 в режиме отображения данных
Задача 2
Рис.П.1.2 Расчетная таблица Excel задачи 2 в режиме отображения данных
Задача 3.
Рис.П.1.3 Расчетная таблица Excel задачи 3 в режиме отображения данных
Приложение 2.
Образцы решения задач 1 - 3. Формулы
Задача 1.
Рис.П.2.1 Расчетная таблица Excel задачи 1 в режиме отображения формул
Задача 2.
Рис.П.2.2 Расчетная таблица Excel задачи 2 в режиме отображении формул
Задача 3.
Рис.П.2.3 Расчетная таблица Excel задачи 3 в режиме отображении формул
Дата добавления: 2015-12-01; просмотров: 31 | Нарушение авторских прав