8997
.pdf3. Использовать Мастер функций для вычисления среднего балла функция СРЗНАЧ() и вычисления количества баллов функция Счетесли().
Примечание:
Функция СРЗНАЧ() вычисляет среднее арифметическое своих аргументов.
Функция Счетесли() подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию.
Задание: Создать и оформить таблицу расчета заработной платы работникам некоторого подразделения.
На листе 2 рабочей книги создать таблицу расчета заработной платы сотрудников некоторого подразделения (рисунок 19).
Рисунок 19. Расчет заработной платы
Для этого:
1.Создать заголовок таблицы: «Расчетный лист за январь» (ячейка В1) . 2.Объединить ячейки области (В1:N1) и разместить текст по центру как
по горизонтали и вертикали.
3.Оформить «шапку» таблицы ячейки (В4:N4) (рисунок 19). Для форматирования текста использовать диалоговое окно Форматирование ячеек
(Главная – Ячейки – Формат – Формат ячеек).
4.В ячейки В6, В7 ввести цифры 1,2 соответственно. Продолжить нумерацию область (В6:В15), используя маркер Автозаполнение
(Главная – Редактирование – Заполнить – Прогрессия).
31
5.Столбцы таблицы, содержащие Фамилия, Имя, Отчество и Оклад
заполнить по своему усмотрению.
6.В ячейку G6 ввести формулу расчета коэффициента, который составляет 50% от оклада (число процента находится в ячейке D17). Ячейки
(G7:G15) заполнить, копируя формулу в ячейке G6.
Примечание:
Ссылки на ячейки в формуле должны быть корректными. Для этого необходимо использовать абсолютную, относительную или смешанную адресацию.
7.Аналогично пункту 6 заполнить столбец Полярная надбавка – это 80%
от оклада (использовать информацию о числе процента из ячейки D18). 8.Используя функцию СУММ(), в ячейку L6 ввести формулу,
суммирующую содержимое ячеек, из которых состоит «чистая» заработная плата, т.е. Оклад, Коэффициент, Полярная надбавка. Ячейки (L7:L15)
заполнить копируя формулу в ячейке L6.
9.В ячейку I6 ввести формулу, вычисляющую подоходный налог – 13%
от вычисляемой суммы, т.е. =L6*13%. В область (I7:I15) скопировать формулу из ячейки I6.
10. Столбцы Пенсионный фонд и Медицинская страховка заполнить аналогично пункту 9, используя следующие ставки:
Пенсионный фонд – 3% от общей суммы;
Медицинская страховка – 2% общей суммы.
11. В ячейку М6 ввести формулу, суммирующую все отчисления из зарплаты (область I6:K6). В область (М7:М15) скопировать формулу из ячейки
М6.
12. Ячейка N6 – итоговая формула расчета заработной платы: из общей начисленной суммы L6 вычитается сумма всех отчислений М6. В область
(N7:N15) скопировать формулу из ячейки N6.
13. Используя функции СУММ(), МАКС(), МИН(), СРЗНАЧ() в ячейках
(N16:N19) найти соответственно:
32
Общую сумму, необходимую для выплаты заработной платы сотрудникам;
Максимальную заработную плату;
Минимальную заработную плату;
Среднюю заработную плату.
14.Установить для ячеек (N6:N19) денежный формат и два знака после запятой. Оформить рамку таблицы, отформатировать по образцу внешний вид таблицы (рисунок 2), установив необходимые размеры и стиль шрифта.
15.Переименовать Лист2 в Расчет, используя контекстно-зависимое
меню.
16.Сохранить полученный документ в файле.
Задание: Создать ведомость выдачи заработной платы.
1.Переименовать Лист3 в Ведомость.
2.На Листе3 рабочей книги создать ведомость выдачи заработной платы сотрудникам некоторого подразделения (рисунок 20).
3.На листе Ведомость в ячейку В3 ввести название таблицы Ведомость выдачи заработной платы. Объединить область ячеек (B3:F3).
4.Создать и отформатировать «шапку» таблицы по образцу (рисунок
20).
5.Заполнить область (В5:В14), используя автозаполнение.
6.Используя функции СЦЕПИТЬ() и ЛЕВСИМВ() заполнить область
(С5:С14), отображая фамилии и инициалы сотрудников. Для этого нужно воспользоваться соответствующей информацией с Листа2 область (С6:Е15).
Примечание:
Функция СЦЕПИТЬ() объединяет несколько текстовых строк в одну.
Функция ЛЕВСИМВ() возвращает указанное количество символов с начала строки текста.
Формула связи между листами должна иметь вид: Имя листа !Адрес
ячейки
7. В ячейку D5 ввести формулу связи с листом Расчет:
33
активизировать ячейку D5 и поставить символ формулы – знак «=»;
перейти на лист Расчет и выделить ячейку L6;
нажать клавишу Enter.
8.Аналогично создать формулы связи для столбцов Всего удержано и
Сумма к выдаче.
9. Отформатировать полученную таблицу. Для этого установить:
денежный формат для области ячеек (D5:F15), нарисовать рамку для таблицы,
выровнять содержимое таблицы.
10. Сохранить результат.
Рисунок 20. Ведомость выдачи заработной платы
Задание: Создать диаграмму «Доля налогов и доходов»
1. На листе Расчет создать внедренную диаграмму, на которой отобразить данные из области (K6:N15) (Диаграмма 1).
Диаграмма 1. Внедренная диаграмма «Доля налогов и доходов»
Примечание:
Технология создания диаграммы состоит из последовательности шагов:
Выделить область данных в таблице, которые нужно отобразить на
34
диаграмме.
Выполнить команду создания диаграммы Вставка – Диаграммы –
Гистограмма – Гистограмма с группировкой. В ленте меню появятся
пункты: Конструктор, Макет, Формат с их помощью можно выполнять
различные операции над диаграммой.
2.Добавить для диаграммы заголовок «Доля налогов и доходов».
3.Внести название оси х – «Фамилия» и оси у – «Сумма».
4.Добавить легенду:
«Всего начислено»;
«Всего удержано»;
«Сумма к выдаче».
5.Добавить к диаграмме сетку.
6.Изменить шрифт легенды и максимум оси значений.
7.Сохранить файл.
Задание: Создать диаграмму «Ведомость выдачи заработной платы»
1.Создать на отдельном листе круговую объемную диаграмму, на которой отобразить данные с листа Ведомость область данных (F5:F14) (Диаграмма 2).
2.Добавить к диаграмме значения меток данных.
3.Добавить название диаграммы «Ведомость выдачи заработной
платы».
4.Добавить легенду.
5.Сохранить файл.
Диаграмма 2. Круговая диаграмма «Ведомость выдачи заработной платы»
35
Запуск редактора Visual Basic for Application в табличном процессоре
Excel
Открытие окна редактора VBA
Для удобной работы с редактором VBA необходимо вывести на панели управления вкладку «Разработчик». Для этого на панели управления
необходимо выполнить команду: «Кнопка Office» , в появившемся диалоговом окне выбрать кнопку «Параметры Excel», «Настроить ленту». В
правой части, где указано «Настроить ленту» поставить галочку около пункта
«Разработчик» (Рисунок 1).
Рисунок 1. Диалоговое окно «Параметры Excel»
В результате выполненных действий появится вкладка «Разработчик» в
строке команд меню. Для запуска редактора Visual Basic необходимо выполнить команду меню «Разработчик Visual Basic». В результате выполненных действий откроется окно редактора VBA (Рисунок 2).
36
Рисунок 2. Окно редактора VBA
Лабораторная работа 3 Основы синтаксиса VBA
Цель: Изучить типы данных, правила именования и объявления переменных, базовые операторы, реализующие линейный алгоритм.
Приобрести навыки написания пользовательских функций и процедур.
Теоретические сведения
Создание макроса
Программный код, написанный на VBA, представляет собой последовательность команд, находящихся в модуле VBA. Для написания модуля необходимо выполнить команду меню: Insert Module. В результате выполнения этой команды откроется окно документа, в котором необходимо вводить текст кода.
Например: Создать код, выводящий в диалоговом окне текст «Введите ваши данные: Ф.И.О.», в ответном диалоговом окне вывести текст: «Здравствуйте Ф.И.О.».
Создайте модуль и введите текст кода:
Sub Приветствие()
Dim b As String
b = InputBox("Ф.И.О.", "Введите ваши данные") MsgBox ("Здравствуйте " & b)
End Sub
Результат работы программного кода представлен на рисунке 4.
37
Выполнить программный код можно двумя способами: с помощью команды меню Run Run Sub (или кнопка на панели инструментов ) или из окна приложения Excel. Для перехода из редактора VBA в окно приложения достаточно выбрать кнопку «View Microsoft Excel»( ). Далее в приложении
Excel для запуска кода нужно выполнить команду «Разработчик Макросы».
В результате выполнения команды появиться диалоговое окно «Макросы»
(Рисунок 3). В диалоговом окне Макрос выбрать имя созданного макроса и нажать кнопку «Выполнить».
Рисунок 3. Диалоговое окно «Макрос» Командные кнопки, расположенные в правой части окна позволяют:
выполнить код, войти в редактор VBA с текстом кода, изменить, удалить код, создать и настроить параметры.
Рисунок 4. Результат работы программного кода Запишем более сложный текст программы:
Sub Program_1( )
Dim A, B, C As String
A = InputBox(« », «Как вас зовут?», «Петя»)
B= InputBox(« », «Ваша фамилия?», «Петров»)
C= InputBox(« », «Где учитесь?», «ННГАСУ»)
38
MsgBox «Знакомьтесь:» + vbCrLf+ _
«это „+A+“ „+B+“ ,»+ vbCrLf+ _
«он (она) учится в „+C
End Sub
В тексте программы три раза вызывается окно InputBox: для ввода с клавиатуры имени, для ввода фамилии, для ввода названия ВУЗа. При этом вводимые переменные записываются в разные переменные: имя в
переменную «А», фамилия – в переменную «В», название ВУЗа – в переменную
«С». Затем все три слова соединяются в единую строку знаками плюс (+), а
полученная строка выводится на экран командой MsgBox. Предположим, что пользователь, запустив программу, указал имя – «Иван», фамилию – «Иванов»,
а название ВУЗа взял как стандартное значение – «ННГАСУ», нажав в третьем окне InputBox кнопку «ОК» сразу, не меняя предлагаемого программой слова.
Результат работы программного кода представлен на рисунке 5.
Рисунок 5 Результат работы программного кода
Создание подпрограмм
Текст модуля VBA состоит из подпрограмм. Существуют два вида подпрограмм: процедура и функция.
Структура процедуры включает в себя: заголовок, описание переменных,
операторы (тело процедуры) и оператор завершения процедуры.
39
Список параметров, расположенный в заголовке после имени подпрограммы, в круглых скобках указываются в виде списка имена переменных и их тип. Переменные используется для передачи значений между программой и подпрограммой.
Вотличие от процедуры посредством имени функции будет возвращено некоторое значение. Для этого для функции нужно указать тип возвращаемого значения в конце заголовка, а в области же операторов подпрограммы необходимо присвоить имени функции требуемое значение.
Впроцедурах и функциях переменные имеют тип, который задается ключевым словом As. В том случае, когда тип функции не задается, то будет присвоен тип Variant. Тип функции определяет тип возвращаемого ею значения. Возвращаемое значение присваивается имени функции «имя функции» и может быть использовано в выражениях программного кода аналогично стандартным функциям VBA.
Основы создания процедур
Каждой процедуре соответствует программный код. Его синтаксис выглядит следующим образом:
Sub «имя процедуры» («параметры»)
операторы
End Sub
Основы создания функций
Синтаксис функции выглядит следующим образом:
Function «имя функции» («параметры») [As «тип»]
операторы
End Function
Сохранение программного кода
После написания программного кода функции необходимо сохранить документ с программным кодам («Кнопка office Сохранить как») и при этом указать в диалоговом окне сохранения в строке Тип файла Документ
Word с поддержкой макросов (Рисунок 6).
40