Семинар ОИТ. Макросы. Работа с базой данных, фильтрами. Работа с файлами.
Содержание задания (постановка задачи)
Создать базу данных, содержащую некоторую информацию, минимум 7 столбцов и не менее 100 строк, пример на рисунке 1.
Ресурс, где можно найти много готовых баз данных по разным направлениям в формате *.xls: https://hub.opengovdata.ru/dataset
Рис.1. База данных сотрудников
На базе полученной таблицы провести двух-, трехуровневую сортировку данных, отфильтровать список по предложенным преподавателем критериям (создав таблицы условий) на 5 отдельных листах.
Этапы выполнения
1. Открываем файл с данными.
2. Создаем дополнительные 5 листов в этом файле, каждый называем фильтр 1, фильтр 2 и т.д.
3. Переносим туда данные из 1 листа, чуть ниже, на уровне 10 строки следующего листа.
4. Создаем 2 кнопки (Разработчик – вставка-кнопка). При нажатии на одну будет происходить фильтрация данных по заданным условиям. При нажатии на вторую фильтр будет удаляться (см. рис.2)
Рис. 2. Создание кнопок
5. Создать таблицу условий, которую будете использовать для фильтрации (см. рис.3).
Рис. 3. Таблица условий
6. Создаем макрос для удаления фильтра (разработчик – макросы – даем имя макросу – создать).
7. Вписываем 1 строку после Sub название_макроса():
ActiveSheet.ShowAllData
ActiveSheet – наш открытый лист
ShowAllData – делает видимыми все строки после фильтрации.
8. Закрываем окно Visual Basic.
9. Нажимаем ПК мыши – «Назначить макрос».
10. Выбираем наш макрос. Кнопка привязана к макросу.
11. Создаем макрос для фильтра данных (разработчик – макросы – даем имя макросу – создать).
12. Вписываем 1 строку после Sub название_макроса():
Range("C14:H413").AdvancedFilter Action:=xlFilterInPlace,CriteriaRange:=Range("A1:D2")
Range – Ячейка или диапазон ячеек
C14:H413 – диапазон с базой данных (вместе с заголовками)
A1:D2 – диапазон с таблицей условий для фильтра (вместе с заголовками)
AdvancedFilter – метод, который фильтрует или копирует данные из списка на основе диапазона условий.
Action:=xlFilterInPlace – означает оставить данные на месте.
CriteriaRange:=Range("A1:D2") – критерии отбора взять из таблицы условий
13. Закрываем окно Visual Basic.
14. Нажимаем ПК мыши – «Назначить макрос».
15. Выбираем наш макрос. Кнопка привязана к макросу.
16. Аналогично делаем на всех 5 листах. На каждом листе по 3 и более условия.
17. Для удобства просмотра результата работы макроса использовать функцию ПРОМЕЖУТОЧНЫЕ ИТОГИ, с номером функции 3.
18. Далее создать макрос для разделения листов на отдельные файлы.
19. Заходим в разработчик – Visual Basic.
20. Нажимаем на Insert – Module.
21. Создаем макрос, называем его разъединение:
Sub Разъединение ()
Dim s as Worksheet 'объявление переменной s
Dim wb as Workbook 'объявление переменной wb
Set wb = ActiveWorkbook 'переменная wb будет использоваться в активном окне
For Each s In wb.Worksheets 'цикл проходит во всем листам книги
s.Copy 'сохраняем лист как новый файл
ActiveWorkbook.SaveAs wb.Path & "\" & s.Name & ".xls"
Next
End Sub
22. Чтобы переместить файлы в какое-то свое место, вместо wb.Path прописываем директорию, куда положить файлы.
ПРИМЕР ВЫПОЛНЕНИЯ МАКРОСА
1. Вывевести сотрудника, Фамилия начинается на К, продаж более чем на 10 тыс., в феврале.
2. Вывести сотрудников, продаж более чем на 70 тыс., в мае.
3. Вывести продажи Кралева Петра в сентябре.
