Автоматизация процессов обработки информации в MS Excel при помощи макросов и VBA

[pic 1]

Цель работы

Получение навыков программирования на языке Visual Basic for Application (VBA) и его применение для управления приложениями Excel; использования макроса для автоматической обработки информации; разработка компьютерных моделей с использованием электронных таблиц; проведение компьютерного эксперимента - исследование компьютерных моделей.

Постановка задачи

Для заданного описания предметной области обработать запросы №1 - №3 из общего перечня обрабатываемых запросов при помощи макросов и VBA в электронных таблицах Microsoft Excel. Программа должна предоставлять пользователю интерфейс для ввода и просмотра данных. В качестве хранилища данных использовать листы электронной таблицы.

1) Выполнить импорт текстовых файлов в рабочий файл электронной таблицы Microsoft Excel так, чтобы каждая новая таблица оказалась на отдельных листах рабочей книги.

2) На основании полученных таблиц средствами языка VBA получить единую составную таблицу.

[pic 2]

Рис. 1. Составная таблица.

После получения такой единой таблицы выполнить запросы 1а) и 1б) используя встроенные средства Excel для анализа и выборки данных (сводные таблицы, сортировку, фильтрацию).

3) Выполнить запросы 2) - 3) используя среду VBA.

ПРЕДМЕТНАЯ ОБЛАСТЬ

[pic 3]

Рис. 2. Таблица «Промышленные предприятия».

[pic 4]

Рис. 3. Таблица «Уплата налогов».

[pic 5]

Рис. 4. Таблица «Уплата налогов». Продолжение.

[pic 6]

Рис. 5. Таблица «Прибыль».

[pic 7]

Рис. 6. Таблица «Виды налогов».

СОСТАВНАЯ ТАБЛИЦА

Постановка задачи: На основании полученных таблиц средствами языка VBA получить единую составную таблицу.

Алгоритм решения поставленной задачи:

1. Зная структуры входных файлов, заполним сначала строку заголовка.
2. Заполнение начнём с таблицы уплаты налогов, так как эта таблица в разы превышает остальные таблицы по количеству строк.
3. По коду предприятия мы можем узнать информацию о предприятии и его прибыли, а по коду налога узнаем название самого налога.

ЗАДАЧА 1

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

Алгоритм решения поставленной задачи:

1. Был записан макрос, который по составной таблице формировал сводную таблицу.
2. В строчках сводной таблицы помещаются названия предприятий, а столбцы – названия налогов, а в значения помещаются суммы налогов.
3. Передавая в макрос количество налогов и предприятий, с помощью цикла составляется уникальная формула, определяющая названия налога, затраты на которые были максимальны. Зная количество налогов и предприятий, определяется столбец, в который будет помещена формула и до какой строки протягивается ячейка с формулой.

ЗАДАЧА 2

Постановка задачи: Уменьшить сумму налога на прибыль на 10% для всех предприятий государственной собственности.

Алгоритм решения поставленной задачи:

1. Проходя по составной таблице, ищутся строки, в которых код налога равен 3 и у предприятия государственная форма собственности.
2. Сумма налога в таких строках умножается на 0,9.

ЗАДАЧА 3

Постановка задачи: Найти предприятие, уплатившее наименьшую сумму налогов.

Алгоритм решения поставленной задачи:

1. Подсчитаем количество предприятий, чтобы установить длину динамического массива.
2. Проходя по составной таблице, добавляем сумму налога предприятий в массив в элемент под номером равный коду предприятия.
3. После прохождения всей таблицы найдём минимальную сумму налога и по коду предприятий узнаем его название.
4. Выведем результат.

Текст формы главного меню программы:

'задать нумерацию индексов массивов с 1

Option Base 1

'составная таблица

Private Sub AllTablButton_Click()

    'отключаем "мерцание" окна

    Application.ScreenUpdating = False