Размещено на http://www.allbest.ru/

4

4

Размещено на http://www.allbest.ru/

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ

РОССИЙСКАЯ МЕЖДУНАРОДНАЯ АКАДЕМИЯ ТУРИЗМА

ВОЛЖСКО-КАМСКИЙ ФИЛИАЛ

КУРСОВАЯ РАБОТА

по дисциплине «Информатика»

на тему:

«Применение MS Excel для практических расчетов

(информационно-аналитическая система магазина по продаже посуды)»

Выполнил:

Студентка очного отделения

Группа №23 Гиоргазде М. Д.

Проверил:

д.т.н., проф. Розенцвайг А.К..

г. Набережные Челны

2010 год

Введение

Исходная информация содержится в двух таблицах:

1. «Цены» (№ п/п, код товара, наименование фрукта, цена за 1 шт) - 7 строк.

2. «Продажи» (№ п/п, код товара, дата продажи, ФИО продавца (не более 5-ти фамилий), наименование посуды (функция ВПР), количество проданной посуды (шт), общая стоимость посуды (функция ВПР))- 40 строк. Одним продавцом могут быть проданы разные видов фруктов.

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

Лист 1. Титульный лист

Открываем Лист 1 и даем ему название «Титульный лист».

Включаем панель Рисование с помощью команды Вид/Панели инструментов/Рисование. С помощью кнопки Добавить объект WordArt создаем заголовок работы («Информационно-аналитическая система магазина по продаже фруктов») и размещаем его в верхней части листа.

Включаем режим привязки объектов к сетке с помощью команды Действие/Привязать/Привязать к сетке.

Для создания кнопок перехода к разным листам выбираем команду Вид/Панели инструментов/Формы и используем инструмент «кнопка». Создаются 13 кнопок с названиями: Выход, Таблица «Продажи», Таблица «Цены», Об авторе, Задание, Сортировка, Автофильтр, Расширенный фильтр, Итоги, Функции, Сводная таблица, Сводная диаграмма, Отчеты.

Добавляем фон титульного листа (Формат/Лист/Подложка) и выбираем нужный рисунок. Для скрытия сетки на листе используем команду Сервис/Параметры/Вид и снимаем флажок сетка.

Активизируем лист «Продажи» и копируем его 6 раз с помощью команды Правка/Переместить_Скопировать лист. Задаем параметры копирования: перед листом - (переместить в конец), устанавливаем галочку «Создавать копию» и нажимаем OK. Переименовываем созданные листы и даем им следующие названия: Сортировка, Автофильтр, Расширенный фильтр, Итоги, Функции, Отчеты.

Для добавления пустых строк перед таблицей выделяем 6 созданных листов, затем выделяем строки 1-9, выбираем команду Вставка/Строки, выделяем ячейку A1, переходим на лист «Продажи».

Добавляем в рабочую книгу еще 2 новых листа (Вставка - Лист) и даем им названия:

Сводная таблица и Сводная диаграмма.

Выбираем команду Вид/Панели инструментов/Visual Basic и щелкаем на кнопке «редактор Visual Basic» или вызываем редактор Visual Basic с помощью сочетания клавиш Alt+F11.

Создаем первую пользовательскую форму: Insert/UserForm. На экране появится форма с набором инструментов:

электронная таблица информационная система

В левой нижней части в окне свойств созданной формы изменяем свойство Caption, вместо UserForm1 пишем «Задание».

Выбираем инструмент Label (надпись), добавляем надпись и вносим все необходимые сведения.

Выбираем инструмент CommandButton (кнопка), задаем ей название «Закрыть», выделяем кнопку и в контекстном меню выбираем пункт «View code» вводим текст «UserForm1.Hide». В результате получим:

Private Sub CommandButton1_Click()

UserForm1.Hide

End Sub

Создаем вторую пользовательскую форму: Insert/UserForm. На экране появится форма с набором инструментов.

В левой нижней части в окне свойств созданной формы изменяем свойство Caption, вместо UserForm2 пишем «Об авторе».

Выбираем инструмент Label (надпись), добавляем надпись и вносим все необходимые сведения.

Выбираем инструмент CommandButton (кнопка), задаем ей название «Закрыть», выделяем кнопку и в контекстном меню выбираем пункт «View code» вводим текст «UserForm1.Hide». В результате получим:

Private Sub CommandButton1_Click()

UserForm2.Hide

End Sub

Добавляем новый модуль, в котором будут написаны все программы для работы с титульным листом (Insert/Module) и называем его «№0_титул».

Добавляем первую процедуру с помощью команды Insert/Procedure и задаем следующие параметры:

Вводим текст: «UserForm1.Show». В результате получим:

Sub задание()

UserForm1.Show

End Sub

Добавляем вторую процедуру с помощью команды Insert/Procedure и задаем следующие параметры:

Вводим текст: «UserForm2.Show». В результате получим:

Sub об_авторе()

UserForm2.Show

End Sub

Добавляем новую процедуру и задаем ее название «лист_титульный_лист», вводим текст:

Sheets("Титульный лист").Select

Range("A1").Select

MsgBox ("Вы перешли на титульный лист")

В результате получим:

Sub лист_титульный_лист()

Sheets("Титульный лист").Select

Range("A1").Select

MsgBox ("Вы перешли на титульный лист")

End Sub

Аналогичным образом добавляем процедуры с названиями: «лист_продажи», «лист_цены», «лист_автофильтр», «лист_расширенный_фильтр», «лист_итоги», «лист_функции», «лист_сводная_таблица», «лист_сводная_диаграмма», «лист_отчеты». В каждую из них добавляем такой же текст изменяя только названия листов.

В результате получим:

Sub лист_продажи()

Sheets("Цены").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Цены")

End Sub

Sub лист_цены()

Sheets("Продажи").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Продажи")

End Sub

Sub лист_сортировка()

Sheets("сортировка").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Сортировка")

End Sub

Sub лист_автофильтр()

Sheets("автофильтр").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Автофильтр")

End Sub

Sub лист_расширенный_фильтр()

Sheets("расширенный фильтр").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Расширенный фильтр")

End Sub

Sub лист_итоги()

Sheets("итоги").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Итоги")

End Sub

Sub лист_функции()

Sheets("функции").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Функции")

End Sub

Sub лист_сводная_таблица()

Sheets("сводная таблица").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Сводная таблица")

End Sub

Sub лист_сводная_диаграмма()

Sheets("сводная диаграмма").Select

MsgBox ("Вы перешли на лист Сводная диаграмма")

End Sub

Sub лист_отчеты()

Sheets("отчеты").Select

Range("A1").Select

MsgBox ("Вы перешли на лист Отчеты")

End Sub

Добавляем процедуру с названием: «выход», которая служит для завершения работы с системой. Код программы следующий:

Sub Выход()

Сообщение = "Вы действительно хотите выйти из Excel?"

Заголовок = "До свидания!"

Кнопки = vbYesNo + vbQuestion

Результат = MsgBox(Сообщение, Кнопки, Заголовок)

If Результат = vbYes Then

Application.Quit

Else: MsgBox ("Выход не состоится")

End If

End Sub

Закрываем редактор Visul Basic и возвращаемся в EXCEL на Титульный лист. Для привязки к кнопкам соответствующих процедур выделяется нужная кнопка, в контекстном меню выбирается пункт «назначить макрос», из списка имеющихся подпрограмм выбирается нужная и нажимается OK. Эти действия повторяются для всех созданных на листе кнопок.

Листы 2-3. Исходные данные

Создать в MS Excel две таблицы на отдельных листах в соответствии с заданием.

Лист 2 переименовываем в «Цены», на нем расположена таблица, состоящая из 5 строк (данные не повторяются).

Таблица «Цены»

Лист 3 переименовываем в «Продажи» на нем расположена таблица, состоящая из 30 строк (данные должны повторяться).

Таблица «Продажи»

На листе «Цены» ввести соответствующую информацию и отсортировать данные по столбцу «Наименование». Создаем списки на листе «Цены»: выделяем ячейки B2:B6, выполняем команду Вставка/Имя/Присвоить, вводим имя диапазона - «Код», выделяем ячейки C2:C6, выполняем команду Вставка/Имя/Присвоить, вводим имя диапазона - «Фрукты».

На листе «Продажи» заполняем столбец «№ п/п». Для задания параметров проверки вводимых значений используется команда Данные/Проверка. В ячейках L2:L4 создаем список фамилий продавцов фруктов (Антонова, Попова, Смирнова).

В столбце «Код товара» выделяем диапазон B2:B31 и задаем следующие параметры проверки значений:

Сообщение об ошибке: «Выбираются из списка». В столбце «Дата продажи» выделяем диапазон C2:C31 и задаем следующие параметры проверки значений:

Сообщение об ошибке: «Диапазон от 01.10.07 до 07.10.07»

В столбце «ФИО продавца» выделяем диапазон D2:D31 и задаем следующие параметры проверки значений:

Сообщение об ошибке: «Выбираются из списка». В столбце «Количество» выделяем диапазон E2:E31 и задаем следующие параметры проверки значений:

Сообщение об ошибке: «Диапазон от 10 до 50 кг»

Между таблицами установить связь с помощью функции ВПР. Функция ВПР ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Буква «В» в имени функции ВПР означает «вертикальный».

ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

Искомое_значение - это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой.

Таблица - таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например БазаДанных или Список.

Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если «интервальный_просмотр» имеет значение ЛОЖЬ, то «таблица» не обязана быть отсортированной.

Номер_столбца - это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «номер_столбца» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «номер_столбца» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее.

Интервальный_просмотр - это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Заполним наименование и цены товаров, используя таблицу Цены:

В ячейку E2 вводим формулу =ВПР(B2;Цены!$B$2:$D$6;2;ЛОЖЬ) и заполняем диапазон E2:E31.

В ячейку G2 вводим формулу =ВПР(B2;Цены!$B$2:$D$6;3;ЛОЖЬ)*F2 и заполняем диапазон G2:G31.

Для выполнения обработки исходных данных необходимо вставить в рабочую книгу еще 8 листов и скопировать таблицу с листа Продажи на листы 4, 5, 6, 7, 8, 11, поместив начало таблицы в ячейку А10. Первые строки оставить пустыми для размещения в них кнопок перехода на другие листы и прочую информацию!

Лист 4. «Сортировка»

На этом листе проводится сортировка данных в таблице по одному или нескольким столбцам, а также в особом порядке.

Создаем на листе 6 кнопок, задаем им следующие названия: Титульный лист, Сортировка по фамилии и наименованию по возрастанию, Сортировка по фамилии и наименованию по убыванию, Сортировка в особом порядке по возрастанию, Сортировка в особом порядке по убыванию, Отмена сортировки.

Задаем для ячеек J5:J7 проверку вводимых значений (Данные/Проверка) с параметрами

Сортировка по столбцам «ФИО продавца» и «Наименование»

Для проведения сортировки по столбцам «ФИО продавца» и «Наименование» по возрастанию необходимо выполнить следующие действия:

1. Установить курсор за переделами таблицы (J40)

2. Выбрать меню Сервис/Макрос/Начать запись. Ввести имя макроса «сорт_фио_наим_возр».

Нажать кнопку ОК. После этого появится панель «Остановить запись».

3. Выделить ячейку A10, выполнить команду Данные/Сортировка и задать сортировку по полю «ФИО продавца» и по полю «наименование» по возрастанию и нажать OK.

Выбрать команду Сервис/Макрос/Остановить запись.

Текст полученного макроса:

Sub сорт_фио_наим_возр()

Range("A10").Select

Range("A10:G40").Sort Key1:=Range("D11"), Order1:=xlAscending, Key2:= _

Range("E11"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _

MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _

DataOption2:=xlSortNormal

End Sub

Для кнопки «Сортировка по фамилии и наименованию по возрастанию» назначаем созданный макрос «сорт_фио_наим_возр».

Для проведения сортировки по столбцам «ФИО продавца» и «Наименование» по убыванию необходимо выполнить следующие действия:

1. Установить курсор за переделами таблицы (J40)

2. Выбрать меню Сервис/Макрос/Начать запись. Ввести имя макроса «сорт_фио_наим_убыв».

Нажать кнопку ОК. После этого появится панель «Остановить запись».

3. Выделить ячейку A10, выполнить команду Данные/Сортировка и задать сортировку по полю «ФИО продавца» и по полю «наименование» по убыванию и нажать OK.

4. Выбрать команду Сервис/Макрос/Остановить запись.

Текст полученного макроса:

Sub сорт_фио_наим_убыв()

Range("A10").Select

Range("A10:G40").Sort Key1:=Range("D11"), Order1:=xlDescending, Key2:= _

Range("E11"), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, _MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _

DataOption2:=xlSortNormal

End Sub

Для кнопки «Сортировка по фамилии и наименованию по убыванию» назначаем созданный макрос «сорт_фио_наим_убыв».

Сортировка в особом порядке по полю «Наименование»

Для сортировки в особом порядке предварительно создается новый список из данных любого имеющегося столбца. Выполним сортировку по столбцу «наименование», элементы пользовательского списка находятся в ячейках J5:J7.

Для добавления нового списка используем команду Сервис/Параметры/Списки, в поле импорт списка выделяем ячейки J5:J7 и нажимаем кнопку «Импорт».

В макросе эти действия пользователя записываются в виде строки:

Application.AddCustomList ListArray:=Range("J5:J7")

Для удаления созданного списка используем команду Сервис/Параметры/Списки, в поле списки выделяем созданный ранее список и нажимаем кнопку «Удалить».

В макросе эти действия пользователя записываются в виде строки:

Application.DeleteCustomList ListNum:=5

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

1. Устанавливаем курсор за переделами таблицы (J40)

2. Выбираем меню Сервис/Макрос/Начать запись. Вводим имя макроса «сорт_особая_возр».

Нажимаем кнопку ОК. После этого появится панель «Остановить запись».

3. Выделяем ячейку A10, выполняем команду Данные/Сортировка и задаем сортировку по полю «наименование» по возрастанию.

4. Нажимаем кнопку «Параметры», в списке выбираем созданный ранее список

Нажимаем кнопку ОК.

5. Выбираем команду Сервис/Макрос/Остановить запись.

Текст полученного макроса с добавлением 2х строк:

Sub сорт_особая_возр()

Application.DeleteCustomList ListNum:=5

Application.AddCustomList ListArray:=Range("J5:J7")

Range("A10").Select

Range("A10:G40").Sort Key1:=Range("E11"), Order1:=xlAscending, Header:= _

xlGuess, OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

End Sub

Для кнопки «Сортировка в особом порядке по возрастанию» назначаем созданный макрос «сорт_особая_возр».

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

1. Устанавливаем курсор за переделами таблицы (J40)

2. Выбираем меню Сервис/Макрос/Начать запись. Вводим имя макроса «сорт_особая_убыв».

Нажимаем кнопку ОК. После этого появится панель «Остановить запись».

3. Выделяем ячейку A10, выполняем команду Данные/Сортировка и задаем сортировку по полю «наименование» по убыванию.

4. Нажимаем кнопку «Параметры», в списке выбираем созданный ранее список

Нажимаем кнопку ОК.

5. Выбираем команду Сервис/Макрос/Остановить запись.

Текст полученного макроса с добавлением 2х строк:

Sub сорт_особая_убыв()

Application.DeleteCustomList ListNum:=5

Application.AddCustomList ListArray:=Range("J5:J7")

Range("A10").Select

Range("A10:G40").Sort Key1:=Range("E11"), Order1:=xlDescending, Header:= _

xlGuess, OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

End Sub

Для кнопки «Сортировка в особом порядке по убыванию» назначаем созданный макрос «сорт_особая_убыв».

Отмена сортировки

Для отмены сортировки необходимо выполнить следующие действия:

1. Устанавливаем курсор за переделами таблицы (J40)

2. Выбираем меню Сервис/Макрос/Начать запись. Вводим имя макроса «сорт_отмена».

Нажимаем кнопку ОК. После этого появится панель «Остановить запись».

3. Выделяем ячейку A10, выполняем команду Данные/Сортировка и задаем сортировку по полю «№ п/п» по возрастанию.

Нажимаем кнопку ОК.

4. Выбираем команду Сервис/Макрос/Остановить запись.

Текст полученного макроса:

Sub сорт_отмена()

Range("A10").Select

Range("A10:G40").Sort Key1:=Range("A11"), Order1:=xlAscending, Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

End Sub

Для кнопки «Отмена сортировки» назначаем созданный макрос «сорт_отмена».

Для кнопки «Титульный лист» назначаем ранее созданный макрос «лист_титульный_лист».

Модулю, в котором записаны все программы для работы с листом «сортировка» задаем имя «№1_сортировка».

Лист 5. «Автофильтр»

На этом листе проводится отбор данных в таблице с помощью автофильтрации.

Создаем на листе 2 кнопки, задаем им названия в соответствии с рисунком.



Для создания макроса необходимо выполнить следующие действия:

1. Установить курсор за пределы таблицы (A1) и выбрать меню Сервис/Макрос/Начать запись. Ввести имя макроса «Автофильтр». Нажать ОК.

2. Выделить ячейку A10 и выбрать команду Данные/Фильтр/Автофильтр. После этого в строке заголовка таблицы появятся кнопки раскрывающихся списков

3. Выбрать меню Сервис/Макрос/Остановить запись

Текст полученного макроса:

Sub автофильтр()

Range("A10").Select

Selection.AutoFilter

End Sub

Для кнопки «Включение/выключение автофильтра» назначаем созданный макрос «автофильтр».

Для кнопки «Титульный лист» назначаем ранее созданный макрос «лист_титульный_лист».

Модулю, в котором записаны все программы для работы с листом «автофильтр» задаем имя «№2_автофильтр».

Использование автофильтра

При работе с автофильтром можно выполнять следующие действия:

1. фильтрация простым выбором критерия из раскрывающегося списка. Например, если необходимо просмотреть записи только на определенную дату - откройте список в столбце «дата продажи» и выберете нужную дату.

В результате получим таблицу следующего вида

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

В результате получим таблицу следующего вида

3. фильтрация с помощью пользовательского фильтра по двум условиям. Например, если нужно получить сведения о товарах, сумма продаж для которых больше 1000 руб. и меньше 2000 руб. - откройте список в столбце «сумма» и выберите пункт «условие» в появившемся окне введите следующие параметры:

4. просмотр наибольших или наименьших элементов списка только для числовых данных. Для этого необходимо выбрать в соответствующем столбце пункт «первые 10» и ввести необходимые параметры.

5. отображение всех данных с помощью пункта «все» для соответствующих столбцов с заданными ранее условиями отбора.

Для кнопки «Включение/выключение автофильтра» назначаем созданный макрос «автофильтр».

Для кнопки «Титульный лист» назначаем ранее созданный макрос «лист_титульный_лист».

Модулю, в котором записаны все программы для работы с листом «автофильтр» задаем имя «№2_автофильтр».

Лист 6. «Расширенный фильтр»

Расширенный фильтр - сложный вид фильтрации по сравнению с автофильтром. Для выполнения расширенной фильтрации необходимо предварительно сформировать в свободном месте рабочего листа критерий фильтрации.

Для создания таблицы критериев выделяем ячейки A10:G10 и копируем их в ячейки A1:G1. Добавляем еще одну ячейку «Количество» (G1). Строка 2 будет содержать критерии отбора данных. Создадим на листе следующие 6 кнопок: Титульный лист, Фильтр по одному наименованию, Фильтр по ФИО двух продавцов, фильтр по диапазону количество, Фильтр по дате и ФИО продавца, Отмена фильтра.

Фильтрация по наименованию

Для фильтрации по наименованию выполняются следующие действия:

1. Установить курсор в произвольную ячейку рабочего листа, например I40. Выбрать команду Сервис/Макрос/Начать запись. Ввести имя макроса - фильтр_наименование:

2. Установить курсор в ячейку E2 и ввести с клавиатуры наименование любого фрукта, который есть в таблице, например Апельсины.

3. Установить курсор в ячейку A10 и выполнить команду Данные/Фильтр/Расширенный фильтр. В появившемся окне ввести два диапазона данных: исходный диапазон - ячейки A10:G40 (вся исходная таблица) и диапазон условий - ячейки А1:H2

4. Нажать ОК и выделить ячейку A1. В результате получится таблица:

5. Выбрать команду Сервис/Макрос/Остановить запись

6. Для внесения изменений в текст макроса нужно выбрать команду Сервис/Макрос/Макросы, в появившемся окне выделить макрос фильтр_наименование и нажать кнопку «Войти». В результате перехода мы окажемся в среде редактора Visual Basic в тексте нужного макроса.

В строке

ActiveCell.FormulaR1C1 = «тарелки»

Необходимо заменить слово «апельсины», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите наименование фрукта")

При нажатии на кнопку «фильтр_наименование» появится диалоговое окно

Фильтрация по ФИО двух продавцов

Для фильтрации по ФИО двух продавцов выполняются следующие действия:

1. Установить курсор в произвольную ячейку рабочего листа, например I40. Выбрать команду Сервис/Макрос/Начать запись. Ввести имя макроса - фильтр_фио:

2. Установить курсор в ячейку D2 и ввести с клавиатуры одну фамилию, в ячейку D3 ввести с клавиатуры другую фамилию из тех, которые есть в таблице, например Антонова и Попова.

3. Установить курсор в ячейку A10 и выполнить команду Данные/Фильтр/Расширенный фильтр. В появившемся окне ввести два диапазона данных: исходный диапазон - ячейки A10:G40 (вся исходная таблица) и диапазон условий - ячейки А1:H3

4. Нажать ОК и выделить ячейку A1.

5. Выбрать команду Сервис/Макрос/Остановить запись

6. Для внесения изменений в текст макроса нужно выбрать команду Сервис/Макрос/Макросы, в появившемся окне выделить макрос фильтр_фио и нажать кнопку «Войти». В результате перехода мы окажемся в среде редактора Visual Basic в тексте нужного макроса.

В строке

ActiveCell.FormulaR1C1 = «антонова»

Необходимо заменить слово «антонова», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите ФИО первого продавца")

В строке

ActiveCell.FormulaR1C1 = «попова»

Необходимо заменить слово «попова», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите ФИО второго продавца")

При нажатии на кнопку «фильтр_фио» последовательно появятся 2 диалоговых окна

Фильтрация по диапазону количество

Для фильтрации по диапазону количество выполняются следующие действия:

1. Установить курсор в произвольную ячейку рабочего листа, например I40. Выбрать команду Сервис/Макрос/Начать запись. Ввести имя макроса - фильтр_колво:

2. Установить курсор в ячейку F2 и ввести с клавиатуры одно условие для количества, в ячейку G2 ввести с клавиатуры другое условие для количества, например >=20 и <=40.

3. Установить курсор в ячейку A10 и выполнить команду Данные/Фильтр/Расширенный фильтр. В появившемся окне ввести два диапазона данных: исходный диапазон - ячейки A10:G40 (вся исходная таблица) и диапазон условий - ячейки А1:H2

4. Нажать ОК и выделить ячейку A1.

5. Выбрать команду Сервис/Макрос/Остановить запись

6. Для внесения изменений в текст макроса нужно выбрать команду Сервис/Макрос/Макросы, в появившемся окне выделить макрос фильтр_колво и нажать кнопку «Войти». В результате перехода мы окажемся в среде редактора Visual Basic в тексте нужного макроса.

В строке

ActiveCell.FormulaR1C1 = «>=20»

Необходимо заменить слово «>=20», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите знак >= и наименьшее значение количества")

В строке

ActiveCell.FormulaR1C1 = «<=40»

Необходимо заменить слово «<=40», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите знак <= и наибольшее значение количества")

При нажатии на кнопку «фильтр_колво» последовательно появятся 2 диалоговых окна

Фильтрация по дате и фамилии

Для фильтрации по диапазону количество выполняются следующие действия:

1. Установить курсор в произвольную ячейку рабочего листа, например I40. Выбрать команду Сервис/Макрос/Начать запись. Ввести имя макроса - фильтр_дата_фио:

2. Установить курсор в ячейку С2 и ввести с клавиатуры дату, в ячейку D2 ввести с клавиатуры ФИО продавца, например 01.10.2007 и антонова.

3. Установить курсор в ячейку A10 и выполнить команду Данные/Фильтр/Расширенный фильтр. В появившемся окне ввести два диапазона данных: исходный диапазон - ячейки A10:G40 (вся исходная таблица) и диапазон условий - ячейки А1:H2

4. Нажать ОК и выделить ячейку A1. В результате получится таблица:

5. Выбрать команду Сервис/Макрос/Остановить запись

6. Для внесения изменений в текст макроса нужно выбрать команду Сервис/Макрос/Макросы, в появившемся окне выделить макрос фильтр_дата_фио и нажать кнопку «Войти». В результате перехода мы окажемся в среде редактора Visual Basic в тексте нужного макроса.

В строке

ActiveCell.FormulaR1C1 = «01.10.2007»

Необходимо заменить слово «01.10.2007», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите дату продажи")

В строке

ActiveCell.FormulaR1C1 = «антонова»

Необходимо заменить слово «антонова», в результате получим

ActiveCell.FormulaR1C1 = InputBox("введите ФИО продавца")

При нажатии на кнопку «фильтр_дата_фио» последовательно появятся 2 диалоговых окна

Отмена фильтрации

Для отмены фильтрации выполняются следующие действия:

1. Установить курсор в произвольную ячейку рабочего листа, например I40. Выбрать команду Сервис/Макрос/Начать запись. Ввести имя макроса - фильтр_отмена:

2. Выделить ячейки A2:H3 и нажать клавишу Delete.

3. Установить курсор в ячейку A1, затем в ячейку A10 и выполнить команду Данные/Фильтр/Расширенный фильтр. В появившемся окне ввести два диапазона данных: исходный диапазон - ячейки A10:G40 (вся исходная таблица) и диапазон условий - ячейки А1:H2

4. Нажать ОК. В результате все скрытые данные отобразятся в таблице

5. Выбрать команду Сервис/Макрос/Остановить запись

Для кнопки «Титульный лист» назначаем ранее созданный макрос «лист_титульный_лист».

Для всех остальных кнопок назначаем соответствующие макросы.

Модулю, в котором записаны все программы для работы с листом «расширенный фильтр» задаем имя «№3_расширенный_фильтр».

Во все макросы, кроме макроса «фильтр_отмена», для отмены действия предыдущей фильтрации данных добавляем после названия строку

Call фильтр_отмена

Полный текст полученного модуля:

Sub фильтр_наименование()

Call фильтр_отмена

Range("E2").Select

ActiveCell.FormulaR1C1 = InputBox("введите наименование фрукта")

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("A1:H2"), Unique:=False

Range("A1").Select

End Sub

Sub фильтр_фио()

Call фильтр_отмена

Range("D2").Select

ActiveCell.FormulaR1C1 = InputBox("введите ФИО первого продавца")

Range("D3").Select

ActiveCell.FormulaR1C1 = InputBox("введите ФИО второго продавца")

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("A1:H3"), Unique:=False

Range("A1").Select

End Sub

Sub фильтр_колво()

Call фильтр_отмена

Range("F2").Select

ActiveCell.FormulaR1C1 = InputBox("введите знак >= и наименьшее значение количества")

Range("G2").Select

ActiveCell.FormulaR1C1 = InputBox("введите знак <= и наибольшее значение количества")

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("A1:H2"), Unique:=False

Range("A1").Select

End Sub

Sub фильтр_дата_фио()

Call фильтр_отмена

Range("C2").Select

ActiveCell.FormulaR1C1 = InputBox("введите дату продажи")

Range("D2").Select

ActiveCell.FormulaR1C1 = InputBox("введите ФИО продавца")

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("A1:H2"), Unique:=False

Range("A1").Select

End Sub

Sub фильтр_отмена()

Range("A2:H3").Select

Selection.ClearContents

Range("A1").Select

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("A1:H2"), Unique:=False

End Sub

Лист 7. «Итоги»

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

Создадим на листе 4 кнопки: Титульный лист, Итоги по ФИО продавца, Итоги по наименованию фруктов, Отмена итогов.

Итоги по ФИО продавца

Для подведения итогов по ФИО продавца выполняются следующие действия:

1. Установить курсор за пределами таблицы в ячейке A1, выбрать команду Сервис/Макрос/Начать запись. Задать макросу имя Итоги_фио, нажать ОК

2. Установить курсор в ячейку А10 и выполнить команду Данные/Сортировка для группировки данных в столбце ФИО продавца

3. Вызвать команду Данные/Итоги и задать следующие параметры:

Нажать ОК и выбрать команду Сервис/Макрос/Остановить запись

В результате получится таблица:

Итоги по наименованию фруктов

Для подведения итогов по наименованию фруктов выполняются следующие действия:

1. Установить курсор за пределами таблицы в ячейке A1, выбрать команду Сервис/Макрос/Начать запись. Задать макросу имя Итоги_наим, нажать ОК

2. Установить курсор в ячейку А10 и выполнить команду Данные/Сортировка для группировки данных в столбце Наименование

3. Вызвать команду Данные/Итоги и задать следующие параметры:

4. Нажать ОК и выбрать команду Сервис/Макрос/Остановить запись

В результате получится таблица:



Отмена итогов

Для отмены подведения итогов выполняются следующие действия:

1. Установить курсор за пределами таблицы в ячейке A1, выбрать команду Сервис/Макрос/Начать запись. Задать макросу имя Итоги_отмена, нажать ОК

2. Выделить ячейку А10, вызвать команду Данные/Итоги и нажать кнопку Убрать все

3. Выполнить команду Данные/Сортировка для сортировки данных по столбцу №п/п

4. Нажать ОК и выбрать команду Сервис/Макрос/Остановить запись

Для кнопки «Титульный лист» назначаем ранее созданный макрос «лист_титульный_лист».

Для всех остальных кнопок назначаем соответствующие макросы.

Модулю, в котором записаны все программы для работы с листом «итоги» задаем имя «№4_итоги».

Во все макросы, кроме макроса «итоги_отмена», для отмены действия подведения итогов добавляем после названия строку

Call итоги_отмена

Полный текст полученного модуля:

Sub итоги_фио()

Call итоги_отмена

Range("A10").Select

Range("A10:G40").Sort Key1:=Range("D11"), Order1:=xlAscending, Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(6, 7), _

Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End Sub

Sub итоги_наим()

Call итоги_отмена

Range("A10").Select

Range("A10:G40").Sort Key1:=Range("E11"), Order1:=xlAscending, Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6, 7), _

Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End Sub

Sub итоги_отмена()

Range("A10").Select

Selection.RemoveSubtotal

Range("A10:G40").Sort Key1:=Range("A11"), Order1:=xlAscending, Header:= _

xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

End Sub

Лист 8. «Функции»

На листе должны быть использованы пять функций рабочего листа из категории «Работа с базой данных»: ДМАКС, ДМИН, ДСРЗНАЧ, БДСУММ, БДСЧЕТ

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

Для создания таблицы критериев выделяем ячейки A10:G10 и копируем их в ячейки A1:G1. Строка 2 будет содержать критерии для фильтрации данных. Создадим на листе следующие 7 кнопок: Титульный лист, Отмена расчетов, ДМАКС, ДМИН, ДСРЗНАЧ, БДСУММ, БСЧЕТ. Заполним ячейки A4:A8 и F4:F8 в соответствии с образцом.

Функция ДМАКС

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

Действия пользователя следующие:

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_ДМАКС»

2. Установить курсор в ячейку D2 и ввести с клавиатуры любую фамилию, которая есть в таблице, например, Антонова

3. Установить курсор в ячейку G4 и выбрать команду Вставка/Функция. В появившемся окне из списка «Категория» выбрать «Работа с базой данных», а затем - функцию ДМАКС.

4. В появившемся окне ввести следующие параметры:

5. Нажать ОК и выбрать команду Сервис/Макрос/Остановить запись.

6. Чтобы изменить текст макроса нужно выбрать команду Сервис/Макрос/Макросы/ДМАКС и нажать кнопку «Войти»

В строке макроса:

ActiveCell.FormulaR1C1 = "Антонова"

Внести изменения:

ActiveCell.FormulaR1C1 = InputBox("Введите ФИО продавца")

Связать соответствующую кнопку с макросом ДМАКС. При нажатии на эту кнопку будет выводиться диалоговое окно:

Функция ДМИН

Запишем макрос для нахождения минимальной суммы продаж по наименованию фрукта, которое будем вводить через диалоговое окно InputBox.

Действия пользователя следующие:

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_ДМИН»

2. Установить курсор в ячейку E2 и ввести с клавиатуры любое наименование фрукта, которое есть в таблице, например, апельсины

3. Установить курсор в ячейку G5 и выбрать команду Вставка/Функция. В появившемся окне из списка «Категория» выбрать «Работа с базой данных», а затем - функцию ДМИН.

4. В появившемся окне ввести следующие параметры:

В строке макроса:

ActiveCell.FormulaR1C1 = "апельсины"

Внести изменения:

ActiveCell.FormulaR1C1 = InputBox("Введите наименование фрукта")

Связать соответствующую кнопку с макросом ДМИН. При нажатии на эту кнопку будет выводиться диалоговое окно:

Функция ДСРЗНАЧ

Запишем макрос для нахождения среднего количества проданных фруктов в течение одного дня. Дату будем вводить через диалоговое окно InputBox.

Действия пользователя следующие:

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_ДСРЗНАЧ»

2. Установить курсор в ячейку С2 и ввести с клавиатуры любое наименование фрукта, которое есть в таблице, например, апельсины

3. Установить курсор в ячейку G6 и выбрать команду Вставка/Функция. В появившемся окне из списка «Категория» выбрать «Работа с базой данных», а затем - функцию ДСРЗНАЧ.

4. В появившемся окне ввести следующие параметры:

В строке макроса:

ActiveCell.FormulaR1C1 = "01.10.07"

Внести изменения:

ActiveCell.FormulaR1C1 = InputBox("Введите дату")

Связать соответствующую кнопку с макросом ДСРЗНАЧ. При нажатии на эту кнопку будет выводиться диалоговое окно:

Функция БДСУММ

Запишем макрос для нахождения суммы продаж фруктов в течение одного дня. Дату будем вводить через диалоговое окно InputBox.

Действия пользователя следующие:

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_БДСУММ»

2. Установить курсор в ячейку С2 и ввести с клавиатуры любое наименование фрукта, которое есть в таблице, например, апельсины

3. Установить курсор в ячейку G7 и выбрать команду Вставка/Функция. В появившемся окне из списка «Категория» выбрать «Работа с базой данных», а затем - функцию БДСУММ.

4. В появившемся окне ввести следующие параметры:

В строке макроса:

ActiveCell.FormulaR1C1 = "01.10.07"

Внести изменения:

ActiveCell.FormulaR1C1 = InputBox("Введите дату")

Связать соответствующую кнопку с макросом БДСУММ. При нажатии на эту кнопку будет выводиться диалоговое окно:

Функция БСЧЕТ

Запишем макрос для нахождения количества строк на определенную сумму, задаваемую с помощью условия, которое будем вводить через диалоговое окно InputBox.

Действия пользователя следующие:

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_БСЧЕТ»

2. Установить курсор в ячейку G2 и ввести с клавиатуры любое наименование фрукта, которое есть в таблице, например, апельсины

3. Установить курсор в ячейку G8 и выбрать команду Вставка/Функция. В появившемся окне из списка «Категория» выбрать «Работа с базой данных», а затем - функцию БСЧЕТ.

4. В появившемся окне ввести следующие параметры:

В строке макроса:

ActiveCell.FormulaR1C1 = ">1000"

Внести изменения:

ActiveCell.FormulaR1C1 = InputBox("Введите условие для суммы")

Связать соответствующую кнопку с макросом БСЧЕТ. При нажатии на эту кнопку будет выводиться диалоговое окно:

Отмена функций

Запишем макрос для отмены расчетов. Действия пользователя следующие:

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «функции_отмена»

2. Выделить ячейки A2:G2 и нажать клавишу Delete

3. Выделить ячейки G4:G8 и нажать клавишу Delete

4. Установить курсор в ячейку A1

5. Выбрать команду Сервис/Макрос/Остановить запись

Для кнопки «Титульный лист» назначаем ранее созданный макрос «лист_титульный_лист».

Для всех остальных кнопок назначаем соответствующие макросы.

Модулю, в котором записаны все программы для работы с листом «функции» задаем имя «№5_функции».

Во все макросы, кроме макроса «функции_отмена», для очистки ячеек с результатами расчетов добавляем после названия строку

Call функции_отмена

Полный текст полученного модуля:

Sub функции_ДМАКС()

Call функции_отмена

Range("D2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите ФИО продавца")

Range("G4").Select

ActiveCell.FormulaR1C1 = "=DMAX(R[6]C[-6]:R[36]C,R[6]C,R[-3]C[-6]:R[-2]C)"

End Sub

Sub функции_ДМИН()

Call функции_отмена

Range("E2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите наименование фрукта")

Range("G5").Select

ActiveCell.FormulaR1C1 = "=DMIN(R[5]C[-6]:R[35]C,R[5]C,R[-4]C[-6]:R[-3]C)"

End Sub

Sub функции_ДСРЗНАЧ()

Call функции_отмена

Range("C2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите дату")

Range("G6").Select

ActiveCell.FormulaR1C1 = "=DAVERAGE(R[4]C[-6]:R[34]C,R[4]C[-1],R[-5]C[-6]:R[-4]C)"

End Sub

Sub функции_БДСУММ()

Call функции_отмена

Range("C2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите дату")

Range("G7").Select

ActiveCell.FormulaR1C1 = "=DSUM(R[3]C[-6]:R[33]C,R[3]C,R[-6]C[-6]:R[-5]C)"

End Sub

Sub функции_БСЧЕТ()

Call функции_отмена

Range("G2").Select

ActiveCell.FormulaR1C1 = InputBox("Введите условие для суммы")

Range("G8").Select

ActiveCell.FormulaR1C1 = "=DCOUNT(R[2]C[-6]:R[32]C,R[2]C,R[-7]C[-6]:R[-6]C)"

End Sub

Sub функции_отмена()

Range("A2:G2").Select

Selection.ClearContents

Range("G4:G8").Select

Selection.ClearContents

Range("A1").Select

End Sub

Лист 9. «Сводная таблица»

Рассмотрим порядок создания сводной таблицы один из вариантов создания.

Перейти на лист «Продажи», установить курсор внутри таблицы в ячейке A1 и выбрать команду Данные/Сводная таблица. Появится Мастер создания сводных таблиц.

Первый шаг:

Нажать кнопку «Далее»

Второй шаг - выделить всю таблицу вместе со строкой заголовков:

Третий шаг - выбрать место размещения таблицы

нажать кнопку «макет», появится макет сводной таблицы

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

В область «страница» нужно перетащить мышкой заголовок того столбца, в котором данные повторяются, например, «дата продажи».

В область «строка» поместим столбец «наименование», в область «столбец» поместим столбец «ФИО продавца».

В область «данные» нужно поместить тот столбец, в котором есть числа, так как для них будут выполняться расчеты, например, «сумма».

Один из примеров макета:

Нажать кнопку «ОК», а затем «Готово».

На новом листе появится таблица:

Переименуем лист и зададим ему название «Сводная таблица».

Создаем на листе кнопку «Титульный лист» и назначаем ранее созданный макрос «лист_титульный_лист».

Лист 10. «Сводная диаграмма»

Щелкнем правой кнопкой мыши внутри сводной таблицы и выберем пункт «сводная диаграмма». На отдельном листе появится диаграмма, которая связана с полями сводной таблицы.

Переименуем лист и зададим ему название «Сводная диаграмма».

Изменим тип диаграммы

Название диаграммы - «Результаты работы магазина по продаже фруктов», подписи данных - значения.

Создаем на листе кнопку «Титульный лист» и назначаем ранее созданный макрос «лист_титульный_лист».

В результате получим следующую диаграмму

Лист 11. «Отчеты»

Для получения отчетов будем использовать возможность отбора данных из таблицы с помощью расширенного фильтра и элементов управления.

Для создания таблицы критериев выделяем ячейки A10:G10 и копируем их в ячейки A1:G1. В столбцах I, J, K создаем списки возможных значений для полей таблицы: Дата продажи, ФИО продавца, Наименование.

На листе создаем 2 кнопки: Титульный лист, Отменить все. С помощью панели Формы создаем 3 поля со списком и добавляем соответствующие названия полей над ними.

Для поля «Дата продажи» задаем следующие параметры:

Для поля «ФИО продавца» задаем следующие параметры:



Для поля «Наименование» задаем следующие параметры:

Лист «Отчеты» должен выглядеть следующим образом

Выделяем ячейку A1 и записываем макрос для заполнения таблицы критериев с помощью полей со списками и выполнения фильтрации данных.

Действия пользователя следующие:

1. Выбрать команду Сервис/Макрос/Начать запись. Задать имя «отчеты»

2. Установить курсор в ячейку С2 и нажать клавишу Delete

3. Выбрать команду Вставка/Функция. В появившемся окне в категории «Ссылки и массивы» выбрать функцию ИНДЕКС.

4. В появившемся окне ввести следующие параметры:

5. Установить курсор в ячейку D2 и нажать клавишу Delete

6. Выбрать команду Вставка/Функция. В появившемся окне в категории «Ссылки и массивы» выбрать функцию ИНДЕКС.

7. В появившемся окне ввести следующие параметры:

8. Установить курсор в ячейку E2 и нажать клавишу Delete

9. Выбрать команду Вставка/Функция. В появившемся окне в категории «Ссылки и массивы» выбрать функцию ИНДЕКС.

10. В появившемся окне ввести следующие параметры:



11. Установить курсор в ячейку A10, выбрать команду Данные/Фильтр/Расширенный фильтр и в появившемся окне ввести следующие параметры:

12. Выбрать команду Сервис/Макрос/Остановить запись.

Для ячейки С2 в строке

ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[5]C[6],R[4]C)"

Необходимо добавить текст «If Range("C6") <> 1 Then», в результате получим

If Range("C6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[5]C[6],R[4]C)"

Для ячейки D2 в строке

ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[2]C[6],R[4]C)"

Необходимо добавить текст «If Range("D6") <> 1 Then », в результате получим

If Range("D6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[2]C[6],R[4]C)"

Для ячейки E2 в строке

ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[4]C[6],R[4]C)"

Необходимо добавить текст «If Range("E6") <> 1 Then », в результате получим

If Range("E6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[4]C[6],R[4]C)"

Полный текст полученной процедуры после внесения изменений:

Sub отчеты()

Range("C2").Select

Selection.ClearContents

If Range("C6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[5]C[6],R[4]C)"

Range("D2").Select

Selection.ClearContents

If Range("D6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[2]C[6],R[4]C)"

Range("E2").Select

Selection.ClearContents

If Range("E6") <> 1 Then ActiveCell.FormulaR1C1 = "=INDEX(R[-1]C[6]:R[4]C[6],R[4]C)"

Range("A10").Select

Range("A10:G40").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("A1:G2"), Unique:=False

End Sub

Для отмены фильтрации данных создается процедура «отменить_все», которая должна содержать следующий программный код:

Sub отменить_все()

Range("C6") = "1"

Range("D6") = "1"

Range("E6") = "1"

Application.Run "отчеты"

Range("A1").Select

End Sub

Для кнопки «Титульный лист» назначаем ранее созданный макрос «лист_титульный_лист», для кнопки «Отменить все» макрос «отменить_все». Для трех созданных полей со списками назначаем одинаковый макрос «отчеты».

Модулю, в котором записаны все программы для работы с листом «отчеты» задаем имя «№6_отчеты».

Для скрытия вспомогательных данных на листе выделяем диапазоны A1:G2 и I1:K7 и задаем белый цвет шрифта символов.

Размещено на Allbest.ru

...