|
Содержание работы или список заданий
|
ЛАБОРАТОРНАЯ РАБОТА
Тема: Информационная технология автоматизированного офиса»
ТАБЛИЧНЫЙ ПРОЦЕССОР MS EXCEL. ТЕХНОЛОГИЯ OLE ПРИ СОВМЕСТНОЙ РАБОТЕ ПРИЛОЖЕНИЙ
Цель работы: приобрести практические навыки экономических расчетов в электронных таблицах с использованием встроенных функций категорий «Логические» и «Ссылки и массивы». Освоить технологию OLE - связь и внедрение объектов.
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
Функций категории «Логические» всего шесть: ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ. Они достаточно понятны и просты в использовании.
•
ЕСЛИ(логическое выражение; значение если истина; значение если ложь) – используется для проверки значений выражений или формул и организации переходов в зависимости от результатов этой проверки.
Возвращает аргумент значение если истина, если логическое выражение при вычислении дает значение ИСТИНА, и аргумент значение если ложь, если аргумент логическое выражение при вычислении дает значение ЛОЖЬ. В качестве возвращаемых значений могут использоваться числовые значения, ссылки на ячейки, формулы или текст. Предположим, что специалисту присваивается категория только в том случае, если он набрал более 50-ти баллов при сдаче квалификационного экзамена, в противном случае категория не присваивается. Реализуем это условие функцией ЕСЛИ: =ЕСЛИ (B4<50; "--";"I категория" ) Если в ячейке В4 содержится например, число 35, что меньше 50, результатом функции ЕСЛИ будет знак "--" . Если в ячейке В4 содержится например, число 75, что больше 50, результатом функции ЕСЛИ будет текст "I категория".
•
И(логическое значение1; логическое значение2;..; логическое значение N)
•
ИЛИ (логическое значение1; логическое значение2;…; логическое значение N)
Функции И и ИЛИ могут содержать до 30 проверяемых условий. Чаще всего эти функции используются в других условных функциях, например, в функции ЕСЛИ, для организации сложных условий. Аргументы должны быть логическими значениями, массивами или ссылками, которые содержат логические значения.
Расширим шкалу присвоения категории специалистам:
=ЕСЛИ(B4<50;"--";ЕСЛИ(И(B4>=50;B4<=100) ;"I категория";"Высшая категория")).
Если ячейка В4 содержит число 75, то в качестве результата будет выведен текст «I категория», если в ячейке В4 содержится число 150 то результатом является текст «Высшая категория», если же в ячейке В4 содержится число меньше 50, то результатом будет знак "--" .
•
НЕ (логическое значение). Эта функция меняет на противоположное логическое значение своего аргумента и используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине.
НЕ (8>2) возвратит значение ЛОЖЬ.
НЕ (8<2) возвратит значение ИСТИНА.
•
ИСТИНА возвращает логическое значение ИСТИНА.
•
ЛОЖЬ - возвращает логическое значение ЛОЖЬ.
Функции категории «Ссылки и массивы» рассмотрим на примере функций
•
ПРОСМОТР(искомое значение; просматриваемый вектор; вектор результатов)
Искомое значение - это значение, которое ПРОСМОТР ищет в первом векторе. Искомое значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение. Просматриваемый вектор - это интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый вектор могут быть текстами, числами или логическими значениями. Значения в аргументе просматриваемый вектор должны быть расположены в порядке возрастания: ..., - 2, -1, 0, 1, 2, . . . , A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными. Вектор результатов - это интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый вектор.
Если ПРОСМОТР не может найти искомое значение, то подходящим считается наибольшее значение в аргументе просматриваемый вектор, которое меньше, чем искомое значение.
Если искомое значение меньше, чем наименьшее значение в аргументе просматриваемый вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.
•
ВПР(искомое значение; инфо_таблица; номер столбца инфо_таблицы; интервальный просмотр)
Искомое значение - это значение, которое должно быть найдено в первом столбце массива. Искомое значение может быть значением, ссылкой или текстовой строкой.
Инфо_таблица - это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например, База Данных или Список.
Если интервальный просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента инфо_таблица должны быть расположены в возрастающем порядке: ..., -2, -1,0,1,2,..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если интервальный просмотр имеет значение ЛОЖЬ, то инфо_таблица не обязана быть сортированной.
Если ВПР не может найти искомое значение и интервальный просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое значение.
Если искомое значение меньше, чем наименьшее значение в первом столбце аргумента инфо_таблица, то функция ВПР возвращает значение ошибки # Н /Д.
Если ВПР не может найти искомое значение и интервальный просмотр имеет значение ЛОЖЬ, то ВПР возвращает значение ошибки #Н/Д.
Номер столбца - это номер столбца в массиве инфо_таблица, в котором должно быть найдено соответствующее значение. Если номер столбца равен 1, то возвращается значение из первого столбца аргумента инфо_таблица; если номер столбца равен 2, то возвращается значение из второго столбца.
Пример использования функции ВПР
Предположим, имеется список участников благотворительного мероприятия, в котором зафиксирован размер спонсорской помощи каждого из них (см. ячейки D2:F9 на рисунке ниже). Задача заключается в том, чтобы пользователь по номеру участника мог получить необходимую информацию о нем (имя, размер взноса).
Таким образом, в ячейках D2:F9 расположена таблица для выполнения поиска, в ячейках A2:B6 можно расположить рабочую таблицу. Если пользователь введет номер участника в ячейку B2, то в ячейках B5 и B6 он получит необходимую информацию из исходной таблицы.
Формулы, реализующие поиск, имеют следующий вид:
В ячейке В5: =ВПР ($B$2; $D$2:$F$9;2)
В ячейке В6: =ВПР($B$2;$D$2:$F$9;3)
Формула в ячейке В5 просматривает первый столбец таблицы D2:F9 в поисках величины введенной в ячейку В2 (это значение 205). Она выбирает соответствующее ему значение из столбца, номер которого задан в формуле (это столбец 2), и возвращает полученное значение (имя Федор) в ячейку В5. Аналогично работает формула в ячейке В6.
Если введенное значение не найдено в таблице, формула возвратит #Н/Д. С помощью функции ЕНД можно изменить формулу таким образом, чтобы сообщение было более понятным:
=ЕСЛИ(ЕНД(ВПР($B$2;$D$2:$F$9;2;ЛОЖЬ));"Ненайдено";ВПР($B$2;$D$2:$F$9;2;ЛОЖЬ)).
•
Функция ГПР работает точно так же, как и ВПР, с той лишь разницей, что просматривает значения ячеек первой строки таблицы (выполняет поиск по горизонтали).
ПРАКТИЧЕСКАЯ ЧАСТЬ
Рассмотрим использование описанных выше функций.
Пример. В таблице 2.1 размещены результаты квалификационного экзамена сотрудников подразделения (Баллы) и стаж работы.
Категория специалиста зависит от суммы набранных на квалификационном экзамене баллов.
Тарифный коэффициент зависит от категории (см. таблицу 2.2).
Надбавка за стаж зависит от стажа работы (см. табл.2.3).
Таблица 2.1— Результаты квалификационного экзамена
ФИО Баллы Категория Тарифный коэффициент Ставка Стаж, лет Надбавка за стаж Оклад
1 2 3 4 5 6 7 8
Абрамов П.Р. 85 4
Боровик Е.П. 100 4
Варданян Г.С. 65 5
Веревщеков Б.Р. 110 7
Глейх В.В. 90 3
Гордеева М.С. 180 15
Жовнер Л.П. 220 20
Дубровин П.А. 140 8
Таблица 2.2 Таблица 2.3
Баллы Категория Тарифный коэффициент
151-200 I 8
101-150 II 7,5
50-100 III 5
>200 Высшая 10
Надбавка Стаж, лет
10% <5
20% 5-10
40% >10
Ставка = Тарифный коэффициент * Ставка 1 разряда;
Оклад = Ставка + Надбавка за стаж(%) * Ставка.
Разместим таблицы с исходными данными в ячейках A3 : H11 рабочего листа Excel (рис.2.1). В таблицу 2.3 добавим столбец «Стаж» (ячейки J10: J12), в котором представим шкалу граничных значений стажа для начисления надбавки за стаж в форме, пригодной для использования функции
ВПР.
Рассчитаем столбец «Категория». В ячейку С4 с помощью мастера функций (рис. 2.2) введем формулу =ЕСЛИ(B4<50;"-";ЕСЛИ(И(B4>=50;B4<=100);"III";ЕСЛИ(И(B4>100;B4<150);"II"; ЕСЛИ(И(B4>150; B4<200);"I"; "Высшая")))).
Скопируем эту формулу в ячейки С5:С11. Рассчитаем столбец «Тарифный коэффициент»: В ячейку D4 введем формулу =ПРОСМОТР(C4;$K$4:$K$7;$L$4:$L$7).
Скопируем эту формулу в ячейки D5:D11.
Рис.2.1 -Исходные данные для расчета
Рис.2.2 - Окно «Мастера функций»
Рассчитаем столбец «Ставка»:
В ячейку Е4 введем формулу =$B$14*D4. (В ячейку В14 внесено значение ставки 1 разряда) (см. рис.2.3).
Скопируем эту формулу в ячейки Е 5 : Е11.
Рассчитаем столбец «Надбавка за стаж»: В ячейку G4 введем формулу: =ВПР(F4;$J$9:$K$12;2). Скопируем эту формулу в ячейки G5 : G11.
Рассчитаем столбец «Оклад»: В ячейку H4 введем формулу: =E4+G4*E4. Скопируем эту формулу в ячейки H5 : Н11.
В ячейке Н13 рассчитаем общий фонд заработной платы по отделу: =СУММ(H4 :H11).
В ячейках Н14 : Н16 рассчитаем фонд заработной платы по каждой категории:
По I категории: =СУММЕСЛИ ($C$4 : $C$11; G14 ; $H$4 : $H$11)
По II категории: =СУММЕСЛИ ($C$4 : $C$11; G15; $H$4 : $H$11)
По III категории: =СУММЕСЛИ ( $C$4 : $C$ 11; G1 6; $H$4 : $H$ 11)
По Высшей категории: =СУММЕСЛИ ($C$4 : $C$11;G17; $H$4 : $H$11)
Заполненная таблица с результатами расчетов представлена на рис. 2.3.
Рис. 2.3 - Результаты расчетов
Отразим графически соотношение размера ставки и оклада каждого сотрудника. Для этого воспользуемся мастером диаграмм, выбрав тип диаграммы - «График» (см. рис.2.4).
Рис. 2.4 – Сведения о заработной плате
Составим отчетную ведомость по подразделению. Для этого создадим документ Word, в который перенесем данные из таблицы Excel, например, ФИО сотрудника, Ставка, Оклад, обеспечив динамическую связь. Вначале в ячейках A20 : D27 сформируем таблицу с этими данными (рис.2.5).
Рис.2.5 - Таблица с данными для отчетной ведомости (в режиме формул и в режиме значений)
Далее выделим эту таблицу и скопируем ее в буфер обмена (Меню Главная - Копировать). Перейдем в документ Word. В меню Главная выберем пункт Вставить - Специальная вставка - Связать - Лист MS Excel (Объект) - ОК(см. рис. 2.6).
Рис.2.6 – Окно «Специальная вставка»
В результате таблица с данными будет вставлена с динамической связью в документ Word. Это значит, что все изменения, вносимые в данные, размещенные в MS Excel, будут автоматически отражаться в созданной отчетной ведомости.
Аналогичным образом можно вставить в отчетную ведомость и построенный график. Фрагмент Отчетной ведомости представлен на рис. 2.7.
Рис.2.7- Фрагмент отчетной ведомости
Задания для самостоятельной работы
Выполнить задание в соответствии с номером варианта. Создать связанный электронный документ: используя технологию OLE, таблицу с результатами расчетов и построенные в ТП MS Excel графические объекты экспортировать в документ Word.
Вариант 1. В книге MS Excel создать ведомость для начисления заработной платы рабочим раскройного участка швейного цеха (не менее 10 - 12 записей). Ведомость должна содержать следующие поля: ФИО сотрудника, Разряд, Отработанное время, вычисляемые поля: Тарифная зарплата, КТУ (коэффициент трудового участия), Расчетная зарплата. Вычисляемые поля не заполняются.
1.
Заполнить столбец Тарифная З/плата в соответствии с таблицей 2.4
2.
Заполнить столбец КТУ в соответствии с таблицей 2.5.
Таблица 2.4 - Тарифная зарплата Таблица 2.5 – КТУ
3 . Рассчитать значения Расчетной зарплаты:
Расчетная зарплата = Тарифная зарплата* КТУ.
4.
Определить общий фонд зарплаты рабочих 5-го и 6-го разрядов.
5.
Построить диаграмму по данным столбцов Тарифная Зарплата и Расчетная Зарплата.
Вариант 2. В книге MS Excel создать ведомость для начисления премии рабочим раскройного участка швейного цеха (не менее 10 - 12 записей). Ведомость должна содержать следующие поля: ФИО сотрудника, Стаж (лет), Семейное положение, Количество детей, вычисляемые поля: Коэффициент, Оклад, Премия. Вычисляемые поля не заполняются.
1.
Рассчитать значения коэффициентов, руководствуясь таблицей 2.6
Таблица 2.6- Коэффициенты Таблица 2.7 – Премия
2. Рассчитать Оклад по формуле
Оклад = 10 * Базовая величина * Коэффициент.
Базовую величину принять равной 70000 руб.
3.
Рассчитать Премию, руководствуясь таблицей 2.7. Учесть, что премия не положена холостым сотрудникам, и в этом случае должен выводиться прочерк или соответствующий комментарий.
4.
Рассчитать общую и среднюю сумму премии бригады.
5.
Определить премиальный фонд рабочих, отработавших на предприятии менее пяти лет.
6.
Построить диаграмму по данным столбцов Оклад и Премия.
Вариант 3. В книге MS Excel создать список сотрудников отдела маркетинга (не менее 12-15 записей). Список должен содержать следующие поля: ФИО, Оклад, Количество продаж, вычисляемые поля: Премия, Сумма к выдаче . Вычисляемые поля не заполняются.
Премия зависит от количества продаж и равна проценту от оклада в соответствии с таблицей 2.8.
Таблица 2.8 - Процент премии
Продажи От 15 до 25 От 25 до 30 Более 30
Процент 50 75 100
1.
Определить общую и среднюю сумму премии по отделу.
2.
Определить среднее значение суммы к выдаче и общую сумму премии сотрудников, совершивших от 15 до 20 продаж.
3 . Построить диаграмму по данным столбцов Премия и Оклад.
Вариант 4. В книге MS Excel создать список студентов (не менее 12-15 записей). Список должен содержать следующие поля: ФИО, Форма обучения (бюджет, внебюджет), Средний балл, вычисляемые поля: Коэффициент, Стипендия. Вычисляемые поля не заполняются.
Стипендия начисляется только студентам-бюджетникам и рассчитывается следующим образом: Стипендия = Базовая величина * Коэффициент.
Коэффициент зависит от среднего балла и устанавливается в соответствии с таблицей 2.9.
Таблица 2.9 – Коэффициенты
Средний балл От 5 до 7 От 7 до 9 От 9 до 10 10
Коэффициент 1,7 2,2 2,6 3
1.
Определить общий фонд стипендии группы.
2.
Подсчитать количество студентов, получающих и не получающих стипендию.
3.
Определить фонд стипендии студентов, у которых средний балл выше заданной величины.
4.
Построить диаграмму по данным столбца Средний балл.
Вариант 5. В книге MS Excel создать ведомость готовой продукции на складе швейного предприятия (не менее 12-15 записей). Ведомость должна содержать следующие поля: Наименование изделия, Количество, Год выпуска, Цена, вычисляемые поля - Переоценка и Стоимость.
Переоценка = Цена - Уценка;
Уценка = Процент * Цена;
Процент зависит от Года выпуска и устанавливается в соответствии с таблицей 2.10.
Стоимость = Количество * Переоценка.
Таблица 2.10 - Процент уценки продукции
Год выпуска 2010 и ранее 2011 2012 2013
Процент 50 20 10 -
1.
Определить общую стоимость переоцененной продукции, выпущенной за каждый год.
2.
Определить общую стоимость продукции, уцененной более, чем на 20%.
3.
Построить диаграмму по данным столбцов Цена и Переоценка.
Вариант 6. В книге MS Excel составить таблицу цен товаров в магазине. Таблица содержит поля: Наименование товара, Дата поступления товара в магазин, Цена товара при поступлении, Текущая цена товара. В магазине текущая цена товара уменьшается в зависимости от дня поступления товара в соответствии со шкалой, размещенной в таблице 2.11.
Таблица 2.11 - Шкала уценки товаров
Количество дней нахождения товара в магазине Уценка товара, от текущей цены
До 15 3
От 15 до 30 5
Более 30 10
Указание: использовать функции категории «Дата и время». По результатам расчетов построить диаграммы.
Вариант 7. В книге MS Excel составить таблицу заказа в магазине. Поля таблицы: Наименование товара, Цена товара, Количество. Количество наименований товаров - не менее 12-15. Определить, сколько должен заплатить покупатель, если в магазине действует 5%-ная скидка на стоимость товара, превышающую 20 000 рублей, и ночная 3%-ная скидка, действующая от 1 до 6 часов утра.
По результатам расчетов построить график спроса на товары.
Вариант 8. В книге MS Excel составить таблицу, в которой в зависимости от числа часов, отработанных за неделю, рассчитать заработную плату без вычетов, сумму выплачиваемых налогов и зарплату после вычетов. Построить диаграмму, на которой представить в сравнении заработную плату без вычетов и зарплату после вычетов.
Принять следующие условия:
■
Базовая часовая ставка = 10,00 ден. ед./час;
■
Переработка (при превышении 40 часов) = в полтора раза;
■
Налоговая ставка 15% с первых 300 ден. ед.
20% со следующих 150 ден. ед.
25% с остальной суммы.
Результаты расчетов отразить графически.
Вариант 9. В книге MS Excel составить ведомость реализации продукции. Ведомость должна содержать следующие поля: Название предприятия торговли, Реализация кондитерский отдел (тыс.руб.), Реализация бакалейный отдел (тыс. руб.), Реализация молочный отдел (тыс. руб.), План реализации (тыс. руб.) и вычисляемые поля: Реализация всего (тыс. руб.), % выполнения плана, Размер премии (тыс. руб.). Вычисляемые поля не заполняются.
1. Поле Реализация Всего рассчитывается как сумма реализации по каждому отделу.
2.
% выполнения плана = Реализация Всего/План реализации* 100-100.
3.
Размер премии рассчитывается в соответствии с таблицей 2.12.
Таблица 2.12 - Шкала для начисления премии
% выполнения плана Размер премии
(% от плана реализации)
Недовыполнение 0
1 – 15 % 10 %
15 – 30 % 20 %
31 – 50 % 30 %
Выше 50 % 50
4.
Определить премиальный фонд предприятий, перевыполнивших план более чем на 30%.
5.
Построить графики выполнения плана реализации (по данным Реализация Всего, План реализации).
Вариант 10. Федеральная шкала налоговых ставок США (таблица 3.14) — самая простая за последнее время.
Таблица 3.14. - Шкала налоговых ставок США
Одинокий 15% с первых $17,8 плюс 28% при превышении этой суммы
Глава семьи 15% с первых $23,9 плюс 28% при превышении этой суммы
Женат,
совместное ведение хозяйства 15% с первых $29,7 плюс 28% при превышении этой суммы
Женат,
раздельное ведение хозяйства 15% с первых $14,85 плюс 28% при превышении этой суммы
Составить таблицу, в которой в зависимости от семейного положения и заработной платы рассчитать сумму налога и сумму к выдаче. Построить диаграмму, на которой представить в сравнении заработную плату без вычетов и зарплату после уплаты налогов.
|