8994

3. Использовать Мастер функций для вычисления среднего балла функция СРЗНАЧ() и вычисления количества баллов функция Счетесли().

Примечание:

Функция СРЗНАЧ() вычисляет среднее арифметическое своих аргументов.

Функция Счетесли() подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию.

Задание: Создать и оформить таблицу расчета заработной платы работникам некоторого подразделения.

На листе 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