ЛАБОРАТОРНАЯ РАБОТА

Тема: Информационная технология автоматизированного офиса»

ТАБЛИЧНЫЙ ПРОЦЕССОР 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.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 – КТУ

Разряд	Тарифная зарплата
-	60000
1	85000
2	95000
3	100000
4	120000
5	150000
6	200000

Отработанное время	КТУ
100-200	1,2
200-300	1,4
300-400	1,5

3 . Рассчитать значения Расчетной зарплаты:

Расчетная зарплата = Тарифная зарплата* КТУ.

4. Определить общий фонд зарплаты рабочих 5-го и 6-го разрядов.

5. Построить диаграмму по данным столбцов Тарифная Зарплата и Расчетная Зарплата.

Вариант 2. В книге MS Excel создать ведомость для начисления премии рабочим раскройного участка швейного цеха (не менее 10 - 12 записей). Ведомость должна содержать следующие поля: ФИО сотрудника, Стаж (лет), Семейное положение, Количество детей, вычисляемые поля: Коэффициент, Оклад, Премия. Вычисляемые поля не заполняются.

1. Рассчитать значения коэффициентов, руководствуясь таблицей 2.6

Таблица 2.6- Коэффициенты Таблица 2.7 – Премия

Коэффициент	Стаж
1	<1
1,1	1
1,2	2
1,3	3
1,4	4
1,7	5
2	>5

Количество детей	Премия
1	10%
2	20%
3	30%
>3	50%

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 записей). Список должен содержать следующие поля: ФИО, Форма обучения (бюджет, внебюджет), Средний балл, вычисляемые поля: Коэффициент, Стипендия. Вычисляемые поля не заполняются.

Стипендия начисляется только студентам-бюджетникам и рассчитывается следующим образом: Стипендия = Базовая величина * Коэффициент.

---

Смотрите также файлы

• Информатика Методичка Windows.docx

• Методические указания по КР ПМ 02 Разработка и администрирование БД.doc

• контр. раб. Методичка ВТ.doc

• ИДЗ-1 Кейс Внутренняя и Внешняя среда.docx

• Зотов Методичка Delphi.doc