Указания к выполнению задания по применению табличного процессора Excel c использованием программирования

Первая исходная таблица должна быть создана на рабочем листе «Справочник сотрудников» и иметь следующий вид:

Табельный номер	Фамилия, имя, отчество	Должность	Отдел

Вторая исходная таблица создается на рабочем листе «Начисления-удержания» и имеет вид:

Табельный номер	Месяц	Начислено	Удержано

При создании второй таблицы необходимо учесть, что она должна содержать данные  нескольких месяцев.

На основе данных исходных таблиц получить на рабочем листе «Платежная ведомость» результатную таблицу, содержащую данные по конкретному месяцу следующего вида:

Отдел	Фамилия, имя, отчество	Сумма к выдаче

Номер месяца должен быть введен в диалоговое окно.

Программа создания таблицы  «Справочник сотрудников»

Sub Entertab1()

Sheets(1).Activate

If Sheets(1).Cells(1, 1) = "" Then

Range("A:E").Clear

Range("A:E").HorizontalAlignment = xlCenter

ActiveSheet.Cells(1, 1) = "Табельный номер"

ActiveSheet.Cells(1, 2) = "Фамилия Имя Отчество"

ActiveSheet.Cells(1, 3) = "Должность"

ActiveSheet.Cells(1, 4) = "Отдел"

Columns("A:A").ColumnWidth = 16

Columns("B:B").ColumnWidth = 10

Columns("C:C").ColumnWidth = 25

Columns("D:D").ColumnWidth = 15

Columns("E:E").ColumnWidth = 15

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = True

.Orientation = 0

.ShrinkToFit = False

.MergeCells = False

End With

i = 2

Else

i = 2

Do While Sheets(1).Cells(i, 1) <> ""

i = i + 1

Loop

End If

Cells(i, 1).Activate

s = InputBox("Табельный номер")

ActiveSheet.Cells(i, 1) = s

Do While Sheets(1).Cells(i, 1) <> "" And s <> ""

ActiveSheet.Cells(i, 2) = InputBox("ФИО")

ActiveSheet.Cells(i, 3) = InputBox("Должность")

ActiveSheet.Cells(i, 4) = InputBox("Отдел")

i = i + 1

s = InputBox("Табельный номер")

Cells(i, 1).Activate

ActiveSheet.Cells(i, 1) = s

Loop

End Sub

В результате создается таблица

Табельный номер	Фамилия Имя Отчество	Должность	Отдел
50	Иванов И.А.	Директор	Управление
55	Сидоров К.П.	Комерческий директор	Маркетинг
60	Уварова И.М.	Гл. бухгалтер	Бухгалтерия
65	Смирнова О.Б.	бухгалтер	Бухгалтерия
70	Леонова Д.Ю.	бухгалтер	Бухгалтерия
75	Антонов Т.О.	Водитель	Управление
80	Вавилова С.А.	Менеджер	Управление
85	Лавров В.В.	Топ-менеджер	Управление

Программа создания таблицы  «Начисления -удержание»

Sub Entertab2()

Sheets(2).Activate

If Sheets(2).Cells(1, 1) = "" Then

Range("A:D").Clear

Range("A:D").HorizontalAlignment = xlCenter

ActiveSheet.Cells(1, 1) = "Табельный номер"

ActiveSheet.Cells(1, 2) = "Месяц"

ActiveSheet.Cells(1, 3) = "Начислено"

ActiveSheet.Cells(1, 4) = "Удержано"

Columns("A:A").ColumnWidth = 16

Columns("B:B").ColumnWidth = 17

Columns("C:C").ColumnWidth = 15

Columns("D:D").ColumnWidth = 24

Range("A1:D1").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = True

.Orientation = 0

.ShrinkToFit = False

.MergeCells = False

End With

Else

i = 2

Do While Sheets(2).Cells(i, 1) <> ""

i = i + 1

Loop

End If

Cells(i, 1).Activate

s = InputBox("Табельный номер")

ActiveSheet.Cells(i, 1) = s

Do While Sheets(2).Cells(i, 1) <> "" And s <> ""

ActiveSheet.Cells(i, 2) = InputBox("Месяц")

ActiveSheet.Cells(i, 3) = InputBox("Начислено")

ActiveSheet.Cells(i, 4) = InputBox("Удержано")

i = i + 1

s = InputBox("Табельный номер")

Cells(i, 1).Activate

ActiveSheet.Cells(i, 1) = s

Loop

End Sub

В результате создается таблица

Табельный номер	Месяц	Начислено	Удержано
50	январь	12000	950
55	январь	11200	1200
60	январь	5630	450
65	январь	3500	250
70	январь	6825	256
75	январь	4500	258
80	январь	2569	458
85	январь	6523	247
50	февраль	11250	850
55	февраль	8600	600
60	февраль	6500	450
65	февраль	3600	256
70	февраль	5689	560
75	февраль	6987	655
80	февраль	3689	147
85	февраль	2658	1698
50	март	12300	970
55	март	8900	850
60	март	6930	620
65	март	3900	300
70	март	6894	258
75	март	5689	258
80	март	2589	215
85	март	3597	258

Программа формирования «Платежной ведомости» за заданный месяц

Sub Calculation()

Sheets(3).Activate

Range("A:D").Clear

Range("A:D").HorizontalAlignment = xlCenter

Columns("A:A").ColumnWidth = 10.14

Columns("B:B").ColumnWidth = 20

Columns("C:C").ColumnWidth = 17

Columns("D:D").ColumnWidth = 19

Rows("1:1").Select

With Selection

.VerticalAlignment = xlCenter

.Orientation = 0

.ShrinkToFit = False

.MergeCells = False

End With

With Selection

.VerticalAlignment = xlCenter

.WrapText = True

.Orientation = 0

.ShrinkToFit = False

.MergeCells = False

End With

ActiveSheet.Cells(1, 1) = "Отдел"

ActiveSheet.Cells(1, 2) = "Фамилия имя отчество"

ActiveSheet.Cells(1, 3) = "Сумма к выдаче"

i = 2

i1 = 2

a = InputBox("Месяц")

Sheets(2).Activate

m = True

Do While Sheets(2).Cells(i, 1) <> "" And m

If a <> Sheets(2).Cells(i, 2) Then

i = i + 1

Else: m = False

End If

Loop

Do While Sheets(2).Cells(i, 1) <> "" And Sheets(2).Cells(i, 2) = a

b = Sheets(2).Cells(i, 1)

c = Sheets(2).Cells(i, 3)

d = Sheets(2).Cells(i, 4)

e = c - d

i = i + 1

ii = 2

Sheets(1).Activate

l = True

Do While Sheets(1).Cells(ii, 1) <> "" And l

If b = Sheets(1).Cells(ii, 1) Then

b1 = Sheets(1).Cells(ii, 2)

d1 = Sheets(1).Cells(ii, 4)

l = False

End If

ii = ii + 1

Loop

Sheets(3).Activate

ActiveSheet.Cells(i1, 1) = d1

ActiveSheet.Cells(i1, 2) = b1

ActiveSheet.Cells(i1, 3) = e

i1 = i1 + 1

Sheets(2).Activate

Loop

Sheets(3).Activate

Range("A1").Select

End Sub

В результате создается ведомость за январь

Отдел	Фамилия имя отчество	Сумма к выдаче
Управление	Иванов И.А.	11050
Маркетинг	Сидоров К.П.	10000
Бухгалтерия	Уварова И.М.	5180
Бухгалтерия	Смирнова О.Б.	3250
Бухгалтерия	Леонова Д.Ю.	6569
Управление	Антонов Т.О.	4242
Управление	Вавилова С.А.	2111
Управление	Лавров В.В.	6276

В результате создается ведомость за февраль

Отдел	Фамилия имя отчество	Сумма к выдаче
Управление	Иванов И.А.	10400
Маркетинг	Сидоров К.П.	8000
Бухгалтерия	Уварова И.М.	6050
Бухгалтерия	Смирнова О.Б.	3344
Бухгалтерия	Леонова Д.Ю.	5129
Управление	Антонов Т.О.	6332
Управление	Вавилова С.А.	3542
Управление	Лавров В.В.	960

В результате создается ведомость за март

Отдел	Фамилия имя отчество	Сумма к выдаче
Управление	Иванов И.А.	11330
Маркетинг	Сидоров К.П.	8050
Бухгалтерия	Уварова И.М.	6310
Бухгалтерия	Смирнова О.Б.	3600
Бухгалтерия	Леонова Д.Ю.	6636
Управление	Антонов Т.О.	5431
Управление	Вавилова С.А.	2374
Управление	Лавров В.В.	3339

Варианты заданий

Вариант 1.

Создать две исходные таблицы:

Первая исходная таблица должна быть создана на рабочем листе «Наличие на начало» и иметь следующий вид:

Код детали	Наименование детали	Остаток текущий	Дата последнего движения	Единицы измерения

Вторая исходная таблица создается на рабочем листе «Приход в течение декады» и имеет вид:

Код детали	Количество прихода	Дата прихода	Номер приходного документа

При создании этой таблицы необходимо учесть, что по каждому коду детали возможен многократный приход в течение декады

На основе данных исходных таблиц получить результатную на рабочем листе «Наличие после прихода» следующего вида:

Код детали	Наименование детали	Остаток  после прихода	Дата последнего движения	Единицы измерения

Вариант 2.

Создать две исходные таблицы:

Первая исходная таблица должна быть создана на рабочем листе «Наличие на начало» и иметь следующий вид:

Код детали	Наименование детали	Остаток текущий	Дата последнего движения	Единицы измерения

Вторая исходная таблица создается на рабочем листе «Расход в течение декады» и имеет вид:

Код детали	Количество расхода	Дата расхода	Номер приходного документа

При создании этой таблицы необходимо учесть, что по каждому коду детали возможен многократный расход в течение декады

На основе данных исходных таблиц получить результатную на рабочем листе «Наличие после расхода» следующего вида:

Код детали	Наименование детали	Остаток  после расхода	Дата последнего движения	Единицы измерения

Вариант 3.

Создать три исходные таблицы:

Первая исходная таблица должна быть создана на рабочем листе «Справочник деталей» и иметь следующий вид:

Код детали	Наименование детали	Единицы измерения

Вторая исходная таблица должна быть создана на рабочем листе «Приход в течение декады» и иметь следующий вид:

Код детали	Количество поступило	Дата поступления	Номер приходного документа

Третья исходная таблица создается на рабочем листе «Расход в течение декады» и имеет вид:

Код детали	Количество выдано	Дата выдачи	Номер расходного документа

При заполнении второй и третьей исходных таблиц учесть то, что как приход, так и расход может быть многократным в течение декады.

На основе данных исходных таблиц получить результатную на рабочем листе «Приход- расход за декаду» следующего вида:

Код детали	Наименование детали	Приход за декаду	Расход  за декаду	Единицы измерения

Вариант 4.

Создать три исходные таблицы:

Первая исходная таблица должна быть создана на рабочем листе «Справочник деталей» и иметь следующий вид:

Код детали	Наименование детали	Единицы измерения

Вторая исходная таблица должна быть создана на рабочем листе «Приход в течение месяца» и иметь следующий вид:

Код детали	Количество поступило	Дата поступления	Номер приходного документа

При заполнении второй и третьей исходных таблиц учесть то, что как приход, так и расход может быть многократным в течение месяца.

На основе данных исходных таблиц получить на рабочем листе «Приход- расход за сутки» результатную таблицу следующего вида:

Код детали	Наименование детали	Приход за сутки	Расход  за сутки	Единицы измерения

Дата, на которую предполагается получение результата, должна быть введена в диалоговое окно.

Вариант 5.

Создать три исходные таблицы:

Первая исходная таблица должна быть создана на рабочем листе «Наличие товаров в фирме» и иметь следующий вид:

Код товара	Наименование товара	Остаток на начало недели	Дата	Единицы измерения

Вторая исходная таблица создается на рабочем листе «Поступление  в течение недели» и имеет вид:

Код товара	Дата поступления	Количество поступило	Номер накладной

Третья исходная таблица создается на рабочем листе «Продажи в течение недели» и имеет вид:

Код товара	Дата продажи	Количество продано

При создании второй и третьей таблиц необходимо учесть, что по каждому товару в течение недели возможны многократные поступления и продажи.

На основе данных исходных таблиц получить результатную таблицу на рабочем листе «Наличие товара на конец недели» следующего вида:

Код товара	Наименование детали	Остаток на конец недели	Дата	Единицы измерения

Вариант 6.

Создать три исходные таблицы:

Первая исходная таблица должна быть создана на рабочем листе «Справочник товаров» и иметь следующий вид:

Код товара	Наименование товара	Единицы измерения

Вторая исходная таблица создается на рабочем листе «Поступление