624
.pdfSheets(“Фильтр2”).Select |
‘Активизация листа Фильтр2 |
Range(“A1”).Select |
‘Активизация клетки A1 |
ActiveSheet.Paste |
‘Вставка из буфера обмена |
Range(“E2:E” & n).Select |
|
Selection.AutoFilter |
|
Selection.AutoFilter Field:=1, Criteria1:=”>=10", Operator:=xlAnd, _ |
|
Criteria2:=”<=20" |
‘Условие отбора |
Range(“A” & n + 5).Select |
|
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = _
“Число дефектных рельсов попадает в диапазон от 10 до 20”
Range(“A21”).Select
End Sub
11. Поместите отредактированный макрос в модуль «Отчеты», гденаходятся всеранееполученныемакросы, ипроверьте его работу. Для этого на листе Отчеты создайте командную кнопку «Фильтр на диапазон», свяжите с макросом «ФильтрДиапазон» и нажмите ее для выполнения макроса.
Задание 3. Получите таблицу «Сведения о состоянии пути ПЧ» на листе Фильтр3 и отфильтруйте записи по условию отбора «Тип скрепления» — ДО и «Процент негодных скреплений попадает в диапазон от 8 до 18».
Данныебудемфильтровать,используярасширенныйфильтр. По ря д ок р або т ы
1.Создайте новый лист Фильтр3.
2.Нажмитекомандную кнопку «Расчетная таблица», расположенную на листе Отчеты. В результате на листе Расчетная таблица появится таблица «Сведения о состоянии пути ПЧ».
3.Включите автозапись (Сервис Макрос Начать запись), задайте макросу имя «ФильтрПоДвумСтолбцам».
4.Выделите таблицу и ее название (блок A1 : N15), скопируйте выделенный блок в буфер обмена любым известным вам способом.
5.Перейдите на лист Фильтр3, активизируйте ячейу A1, вставьте из буфера обмена выделенный блок. Появится такая же таблица, как и на листе Расчетная таблица.
6.Создайте диапазон условий в блоке A18 : С19:
6.1. В ячейку A18 скопируйте содержимое ячейки F2 (“Тип скрепления”).
6 1
6.2.В ячейку B18 скопируйте содержимое ячейки G2 (“Негодные скрепления, %”).
6.3.В ячейку С18 также скопируйте текст “Негодные скрепления, %»”.
6.4.В ячейку А19 запишите тип скрепления — ДО.
6.5.В ячейку B19 запишите условие отбора: >=8.
6.6.В ячейку С19 запишите условие отбора: <=18.
7. Выберите команду Данные Фильтр Расширенный фильтр, введите информацию в окне диалога и нажмите кнопку ОК (рис. 28).
Рис. 28. Окно диалога «Расширенный фильтр» с данными о местонахождении списка, диапазона условий и отфильтрованного списка
Результат представлен на рис. 29. 8. Остановите автозапись макроса. Вы получите следующий текст:
Sub ФильтрПоДвумСтолбцам() Range(“A1:N15”) Selection.Copy Sheets(“Фильтр3”).Select Range(“A1”).Select ActiveSheet.Paste Range(“F2:G2”).Select Application.CutCopyMode = False Selection.Copy Range(“A18”).Select ActiveSheet.Paste
6 2
Range(“B18”).Select Application.CutCopyMode = False
Selection.AutoFill Destination:=Range(“B18:C18”), Type:=xlFillDefault Range(“A19”).Select
ActiveCell.FormulaR1C1 = “ДО” Range(“B19”).Select ActiveCell.FormulaR1C1 = “>=8” Range(“C19”).Select ActiveCell.FormulaR1C1 = “<=18” Range(“D12”).Select
Range(“A2:N13”).AdvancedFilter Action:=xlFilterCopy, CritriaRange:=_ Range(“A18:C19”), CopyToRange:=Range(“A21:N21”), Unique:=False
End Sub
Рис. 29. Результат фильтрации по двум столбцам
9. Внесите изменения в текст полученного макроса, позволяющие фильтровать таблицу с произвольным количеством строк. Макрос будет иметь вид:
Sub ДиапазонПоДвумСтолбцам()
Расчет ‘Обращение к макросу, формирующему расчетную таблицу
Sheets(“Фильтр3”).Cells.Clear |
‘Очистка клеток листа Фильтр3 |
Range(“A1:N” & n + 2).Select |
‘Выделение блока для копирования |
6 3
Selection.Copy |
‘Копирование в буфер обмена |
Sheets(“Фильтр3”).Select |
‘Активизация листа Фильтр3 |
Range(“A1”).Select |
‘Активизация клетки A1 |
ActiveSheet.Paste |
‘Вставка из буфера обмена |
Range(“F2:G2”).Select |
‘Выделение блока для копирования в ди- |
апазон условий
Application.CutCopyMode = False
Selection.Copy |
‘Копирование в буфер обмена |
Range(“A” & n+5).Select |
‘Выделение угловой клетки диапазона |
условий, который всегда будет расположен на 2 строки ниже исходной
таблицы |
|
ActiveSheet.Paste |
‘Вставка из буфера обмена |
Range(“B” & n+5).Select |
|
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range(“B” & n + 5 & “:C” & n + 5),
Type:=xlFillDefault |
|
Range(“A” & n+6).Select |
‘Формирование условий отбора |
ActiveCell.FormulaR1C1 = “ДО” |
|
Range(“B” & n+6).Select |
|
ActiveCell.FormulaR1C1 = “>=8” |
|
Range(“C” & n+6).Select ActiveCell.FormulaR1C1 = “<=18”
Range(“A2:N” & n).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= Range(“A” & n + 5 & “:C” & n + 6), CopyToRange:=Range(“A” & n + 8 & “:N” & n + 8), Unique:=False
Range(“D19”).Select
End Sub
10. Поместите отредактированный макрос в тот же модуль, где находятся все ранее полученные макросы, и проверьте его работу.Для этогоналистеОтчетысоздайтекоманднуюкнопку «Фильтр по двум столбцам», свяжите с макросом «ФильтрПоДвумСтолбцам» и нажмите ее для выполнения макроса.
Контрольныевопросы
1.Что такое фильтрация данных? Виды фильтров.
2.Порядок отбора данных с помощью автофильтра.
3.Пользовательский автофильтр, возможные варианты задаваемых условий.
4.Использование метода AutoFilter в программе, его синтаксис.
5.Расширенный фильтр, его возможности, порядок использования.
6 4
3. ОБЩИЕ ТРЕБОВАНИЯ К ВЫПОЛНЕНИЮ РАБОТЫ И ВАРИАНТЫ ЗАДАНИЙ
1.Работа выполняется в табличном процессоре Microsoft Excel с элементами программирования в VBA. Управление проектом должно осуществляться через кнопочные меню на рабочих листах.
2.Исходные данные для справочника и базы данных (не менее десяти строк) придумать самостоятельно.
3.Разработать форму для заполнения и редактирования базы данных.
4.Расчетныеграфы заполнять спомощью ручногомакроса.
5.Данные из справочника брать функцией ВПР или ИНДЕКС.
6.Макросы для поиска данных в справочнике, сортировки, построения диаграмм и фильтрации таблицы получить автозаписью.
7.Все макросы отредактировать для работы с таблицей, имеющей произвольное количество строк.
8.Макросы запускать управляющими кнопками.
9.Разработать титульный лист, с которого предусмотреть выход из Excel и переход в Главное меню.
10.Разработать форму Главного меню, позволяющую выбрать необходимое действие.
11.Листы книги назвать:
титульный лист, задание, главное меню, справочник, база данных, отчеты, расчетная таблица, таблицаотсортированная, гистограмма,круговая,кольцевая,фильтр1,фильтр2,фильтр3.
12. Полученные макросы поместить в три модуля:
—переходы,
—форма,
—отчеты.
Модуль “переходы»долженсодержать всемакросы, осуществляющие активизацию нужных листов. “Форма» — макросы, обеспечивающие работу с базой данных. “Отчеты» — макросы, формирующие различного вида документы.
Для защиты представить пояснительную записку с дискетой. Пояснительная записка должна содержать:
— текст задания,
6 5
—распечатки полученных по варианту результатов,
—форму кнопочного Главного меню,
—форму для корректировки справочника,
—форму для заполнения и корректировки базы данных,
—вид листа «отчеты»,
—все виды отчетов по варианту (сортировка, диаграммы, фильтры),
—тексты процедур, созданных в VBA для автоматизации проекта.
6 6
Вариант 1
1.Создайте справочник. Корректировка справочника должна выполняться с использованием стандартного диалогового окна (команда Фор-
ма… из меню Данные).
2.Создайте на рабочем листе форму для заполнения базы данных, в которой должны находиться только исходные данные для расчетной таблицы «Расчет стоимости перевозки грузов».
На форме расположить кнопки для добавления, удаления, корректиров-
ки записей таблицы.
Расчет стоимости перевозки грузов
|
|
|
Масса |
Расстояние |
|
Тариф на |
Плата за |
|
|
Наименование |
Станция |
Станция |
Простой |
1 ч |
Стоимость |
||||
груза, |
перевозки, |
перевозку |
|||||||
груза |
отправления |
назначения |
под п/в, ч |
простоя |
перевозки груза, р. |
||||
|
|
|
т |
км |
|
1 т–км, р. |
под п/в, р. |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
… |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Итого |
|
|
|
|
|
|
|
|
В среднем |
|
|
|
|
|
|
|
|
Исходные данные — гр. 1–6. Данные из справочников — гр. 7–8. Результаты — гр. 9.
гр. 9 = гр. 4•гр. 5•гр. 7 + гр. 6•гр. 8. 3. Создайте отчеты:
а) таблицу с рассчитанными данными, средними значениями и итогами; б) таблицу с данными, отсортированными по возрастанию значений гр. 9; в) максимальное и минимальное значения гр. 4; г) три вида диаграмм:
—гистограмму,
—круговую,
—кольцевую;
д) таблицы с данными, отфильтрованными по условиям:
—масса превышает среднее значение;
—расстояние перевозки попадает в указанный диапазон (границы диапазона задать самостоятельно);
—отбор по двум столбцам (условие сформулировать самостоятельно).
6 7
Вариант 2
1.Создайте справочник. Корректировка справочника должна выполняться с использованием стандартного диалогового окна (команда Фор-
ма… из меню Данные).
2.Создайте на рабочем листе форму для заполнения базы данных, в которой должны находиться только исходные данные для расчетной
таблицы «Расчет скорости перевозки грузов».
На форме расположить кнопки для добавления, удаления, корректировки записей таблицы.
Расчет средней скорости перевозки грузов
Наименование |
Станция |
Станция |
Масса |
Расстояние |
Время в |
Тариф на |
Стоимость |
Средняя скорость |
|
перевозки, |
перевозку |
перевозки |
|||||||
груза |
отправления |
назначения |
груза, т |
пути, ч |
перевозки, км/ч |
||||
км |
1 т–км, р. |
груза, р. |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
… |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Итого |
|
|
|
|
|
|
|
|
В среднем |
|
|
|
|
|
|
|
|
Исходные данные — гр. 1–6. Данные из справочников — гр. 7. Результаты — гр. 8–9.
гр. 8 = гр. 4•гр. 5•гр. 7. гр. 9 = гр. 5/гр. 6.
3. Создайте отчеты:
а) таблицу с рассчитанными данными, средними значениями и итогами; б) таблицу с данными, отсортированными по возрастанию значений гр. 8; в) максимальное и минимальное значения гр. 4; г) три вида диаграмм:
—гистограмму,
—круговую,
—кольцевую;
д) таблицы с данными, отфильтрованными по условиям:
—стоимость перевозки превышает среднее значение;
—средняя скорость перевозки попадает в указанный диапазон (границы диапазона задать самостоятельно);
—отбор по двум столбцам (условие сформулировать самостоятельно).
6 8
Вариант 3
1.Создайте справочник. Корректировка справочника должна выполняться с использованием стандартного диалогового окна (команда Фор-
ма… из меню Данные).
2.Создайте на рабочем листе форму для заполнения базы данных, в которой должны находиться только исходные данные для расчетной таблицы «Расчет простоя вагонов».
На форме расположить кнопки для добавления, удаления, корректиров-
ки записей таблицы.
Расчет простоя вагонов
|
Число вагонов |
Норма простоя |
Простой вагонов |
||||
|
|
|
|
|
|
|
|
Наименование |
|
под |
со |
под |
|
|
|
со сдвоенными |
погрузкой |
погрузкой |
со сдвоенными |
под погрузкой или |
|||
дороги |
|||||||
сдвоенными |
|||||||
операциями |
или |
или |
операциями |
выгрузкой |
|||
|
|||||||
|
операциями |
||||||
|
|
выгрузкой |
выгрузкой |
|
|
||
|
|
|
|
|
|
|
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
|
… |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Итого |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
В среднем |
|
|
|
|
|
|
|
Исходные данные — гр. 1–3.
Данные из справочников — гр. 4–5. Результаты — гр. 6–7.
гр. 6 = гр. 2•гр. 4. гр. 7 = гр. 3•гр. 5.
3. Создайте отчеты:
а) таблицу с рассчитанными данными, средними значениями и итогами; б) таблицу с данными, отсортированными по возрастанию значений гр. 7; в) максимальное и минимальное значения гр. 2; г) три вида диаграмм:
—гистограмму,
—круговую,
—кольцевую;
д) таблицы с данными, отфильтрованными по условиям:
—простой под погрузкой или выгрузкой превышает среднее значение;
—число вагонов со сдвоенными операциями попадает в указанный диапазон (границы диапазона задать самостоятельно);
—отбор по двум столбцам (условие сформулировать самостоятельно).
6 9
Вариант 4
1.Создайте справочник. Корректировка справочника должна выполняться с использованием стандартного диалогового окна (команда Фор-
ма… из меню Данные).
2.Создайте на рабочем листе форму для заполнения базы данных, в которой должны находиться только исходные данные для расчетной таблицы «Расчет наличия свободных мест в поезде».
На форме расположить кнопки для добавления, удаления, корректиров-
ки записей таблицы.
Расчет наличия свободных мест в поезде
Номер |
|
Число вагонов |
|
|
Число мест в поезде |
|
Число проданныхбилетов |
Наличие свободныхмест |
||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
поезда |
куп. |
плацк. |
общ. |
Всего |
куп. |
|
плацк. |
общ. |
Всего |
куп. |
плацк. |
общ. |
Всего |
куп. плацк. |
общ. |
Всего |
||
|
|
|||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
2 |
3 |
4 |
5 |
6 |
|
7 |
8 |
|
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
17 |
… |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Итого |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
В среднем |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Исходные данные — гр. 1–4, гр. 10–12.
Данные из справочников — для расчета гр. 6–8 (число мест в вагоне соотв.
типа, nкуп, nплацк, nобщ). |
гр. 13 = гр. 10 + гр. 11 + гр. 12. |
||
Результаты — гр. 5–9, 13–17. |
|||
гр. 5 = гр. 2 + гр. 3 + гр. 4. |
гр. 14 |
= гр. 6 – гр. 10. |
|
гр. 6 |
= гр. 2•nкуп. |
гр. 15 |
= гр. 7 – гр. 11. |
гр. 7 |
= гр. 3•nплацк. |
гр. 16 |
= гр. 8 – гр. 12. |
гр. 8 |
= гр. 4•nобщ. |
гр. 17 |
= гр. 14 + гр. 15 + гр. 16. |
гр. 9 |
= гр. 6 + гр. 7 + гр. 8. |
|
|
3. Создайте отчеты:
а) таблицу с рассчитанными данными, средними значениями и итогами; б) таблицу с данными, отсортированными по возрастанию значений гр. 17; в) максимальное и минимальное значения гр. 10; г) три вида диаграмм:
—гистограмму,
—круговую,
—кольцевую;
д) таблицы с данными, отфильтрованными по условиям:
—число вагонов «всего» превышает среднее значение;
—число проданных купейных билетов попадает в указанный диапазон (границы диапазона задать самостоятельно);
—отбор по двум столбцам (условие сформулировать самостоятельно).
7 0