Программист VBA
Разработка аналитической базы данных (образование)
Добавлено
24 окт 2022 в 13:01
1.Систематизация и типизация массивов исходных данных.
Значения показателей в таблицах исходных данных(около 2000 записей ) были представлены в избыточной, не систематизированной форме.
Кроме того, ячейки ввода данных были представлены общим типом "любое значение", а не типом данных "список", что затрудняет анализ исходных данных.
В связи с этим, исходные данные в ячейках экспертных оценок были систематизированы, и далее по каждому листу составлены списки значений.
Далее специальным макросом (напр.: Insert_DropDowns())ячейкам таблиц был назначен тип данных "список", что позволяет при вводе новых данных выбирать в ячейке необходимое значение из выпадающего списка.
Sub Insert_DropDownsP2_01() Dim criteria_1_P2 As Range Dim rCell As Range Dim LastRow As Long Dim DropDownStringP2_01 As String Dim i As Long With Worksheets("справочники")' извлекаем из листа "справочники" Set criteria_1_P2 = .Range("F8:F12")' диапазон по критерию меняем диапазон DropDownStringP2_01 = ""' начальное значение строки For Each rCell In criteria_1_P2' для каждой ячейки диапазона критерия DropDownStringP2_01 = DropDownStringP2_01 & rCell.Value & ","' заполняем строковую переменную Next rCell ' далее End With LastRow = Worksheets("П2").Cells(Rows.Count, 1).End(xlUp).Row' номер последней заполненной строки For i = 2 To LastRow' цикл создания выпадающего списка в столбце With Worksheets("П2").Cells(i, 10).Validation ' устанавливаем выпадающий список на активную ячейку .Delete' удалить предыдущее содержимое .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, _ Formula1:=DropDownStringP2_01' добавить характеристики и формулу ссылки на строковую переменную .InCellDropdown = True' добавить знак выпадающего списка End With Next i' конец цикла End Sub
Иллюстрация 1: Рис. 1 Услуга- аналитическая база данных
2. Анализ экспертных оценок таблиц.
Сводные таблицы. Разработаны 3 сводных таблицы в разрезе различных критериев соответствия (по столбцам). В каждой сводной таблице добавлены управленческие показатели: столбцы сортировки организаций(=наибольший(…))по убыванию критериев долей соответствия.
Сводная таблицаиз исходных данных (таблица "1") выводит детальную информациюпо критериям соответствия
Управленческие показатели:
1. Доля организаций со 100% соответствием, формула массива {=СЧЁТ(ЕСЛИ(I5:I1145=1;I5:I1145))/СЧЁТЗ(I5:I1145)}
2. Доля организаций с 50% соответствием, формула массива{=СЧЁТ(ЕСЛИ(I5:I1145=0,5;I5:I1145))/СЧЁТЗ(I5:I1145)}
3. Доля организаций с полным несоответствием, формула массива{=СЧЁТ(ЕСЛИ(I5:I1145=0;I5:I1145))/СЧЁТЗ(I5:I1145)}
4. Перечень образовательных организаций со 100% долей соответствия, формула массива:
{=ЕСЛИОШИБКА(ИНДЕКС($A$5:$G$1145;НАИМЕНЬШИЙ(ЕСЛИ($I$5:$I$1145=1;$A$5:$A$1145;"");СТРОКА()-4);2);0)} -
создает в столбце "J" перечень организаций, у которых имеется 100% доля соответствия.
Аналитические таблицы. Анализ материального обеспечения
Сводные таблицы из исходных данных выводят детальную информацию по соответствиям показателей в разрезе статусов (выделены желтым цветом).
Расчеты сводных показателей: "показатель 1", "показатель 2" и "показатель 3"выполнены с помощью макросов VBA ( P2CountIf(C6;6)иP2CountNonZero(6))для учета переменной величины числа записей в исходных данных.
Для сравнительного анализа представлены диаграммы количества / доли статусов приведенных показателей.
Option Explicit Public Function P2CountIf(criteria1 As String, ColNum As Integer) As Long Dim LastRow As Long' последняя строка Dim rng As Range' диапазон расчета с условием LastRow = Worksheets("П2").Cells(Rows.Count, ColNum).End(xlUp).Row' последняя строка With Worksheets("П2") Set rng = .Range(.Cells(2, ColNum), .Cells(LastRow, ColNum)) ' определяем диапазон End With P2CountIf = Application.WorksheetFunction.CountIf(rng, criteria1)' считаем число значений с условием End Function Public Function P2CountNonZero(ColNum As Integer) As Long Dim LastRow As Long' последняя строка Dim rng As Range' диапазон расчета с условием LastRow = Worksheets("П2").Cells(Rows.Count, ColNum).End(xlUp).Row' последняя строка With Worksheets("П2") Set rng = .Range(.Cells(2, ColNum), .Cells(LastRow, ColNum)) ' определяем диапазон End With P2CountNonZero = Application.WorksheetFunction.CountIf(rng, "<>""")' считаем число значений с условием End Function Иллюстрация 2: Рис. 2 Услуга- аналитическая база данных
Значения показателей в таблицах исходных данных(около 2000 записей ) были представлены в избыточной, не систематизированной форме.
Кроме того, ячейки ввода данных были представлены общим типом "любое значение", а не типом данных "список", что затрудняет анализ исходных данных.
В связи с этим, исходные данные в ячейках экспертных оценок были систематизированы, и далее по каждому листу составлены списки значений.
Далее специальным макросом (напр.: Insert_DropDowns())ячейкам таблиц был назначен тип данных "список", что позволяет при вводе новых данных выбирать в ячейке необходимое значение из выпадающего списка.
Sub Insert_DropDownsP2_01() Dim criteria_1_P2 As Range Dim rCell As Range Dim LastRow As Long Dim DropDownStringP2_01 As String Dim i As Long With Worksheets("справочники")' извлекаем из листа "справочники" Set criteria_1_P2 = .Range("F8:F12")' диапазон по критерию меняем диапазон DropDownStringP2_01 = ""' начальное значение строки For Each rCell In criteria_1_P2' для каждой ячейки диапазона критерия DropDownStringP2_01 = DropDownStringP2_01 & rCell.Value & ","' заполняем строковую переменную Next rCell ' далее End With LastRow = Worksheets("П2").Cells(Rows.Count, 1).End(xlUp).Row' номер последней заполненной строки For i = 2 To LastRow' цикл создания выпадающего списка в столбце With Worksheets("П2").Cells(i, 10).Validation ' устанавливаем выпадающий список на активную ячейку .Delete' удалить предыдущее содержимое .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlEqual, _ Formula1:=DropDownStringP2_01' добавить характеристики и формулу ссылки на строковую переменную .InCellDropdown = True' добавить знак выпадающего списка End With Next i' конец цикла End Sub
Иллюстрация 1: Рис. 1 Услуга- аналитическая база данных
2. Анализ экспертных оценок таблиц.
Сводные таблицы. Разработаны 3 сводных таблицы в разрезе различных критериев соответствия (по столбцам). В каждой сводной таблице добавлены управленческие показатели: столбцы сортировки организаций(=наибольший(…))по убыванию критериев долей соответствия.
Сводная таблицаиз исходных данных (таблица "1") выводит детальную информациюпо критериям соответствия
Управленческие показатели:
1. Доля организаций со 100% соответствием, формула массива {=СЧЁТ(ЕСЛИ(I5:I1145=1;I5:I1145))/СЧЁТЗ(I5:I1145)}
2. Доля организаций с 50% соответствием, формула массива{=СЧЁТ(ЕСЛИ(I5:I1145=0,5;I5:I1145))/СЧЁТЗ(I5:I1145)}
3. Доля организаций с полным несоответствием, формула массива{=СЧЁТ(ЕСЛИ(I5:I1145=0;I5:I1145))/СЧЁТЗ(I5:I1145)}
4. Перечень образовательных организаций со 100% долей соответствия, формула массива:
{=ЕСЛИОШИБКА(ИНДЕКС($A$5:$G$1145;НАИМЕНЬШИЙ(ЕСЛИ($I$5:$I$1145=1;$A$5:$A$1145;"");СТРОКА()-4);2);0)} -
создает в столбце "J" перечень организаций, у которых имеется 100% доля соответствия.
Аналитические таблицы. Анализ материального обеспечения
Сводные таблицы из исходных данных выводят детальную информацию по соответствиям показателей в разрезе статусов (выделены желтым цветом).
Расчеты сводных показателей: "показатель 1", "показатель 2" и "показатель 3"выполнены с помощью макросов VBA ( P2CountIf(C6;6)иP2CountNonZero(6))для учета переменной величины числа записей в исходных данных.
Для сравнительного анализа представлены диаграммы количества / доли статусов приведенных показателей.
Option Explicit Public Function P2CountIf(criteria1 As String, ColNum As Integer) As Long Dim LastRow As Long' последняя строка Dim rng As Range' диапазон расчета с условием LastRow = Worksheets("П2").Cells(Rows.Count, ColNum).End(xlUp).Row' последняя строка With Worksheets("П2") Set rng = .Range(.Cells(2, ColNum), .Cells(LastRow, ColNum)) ' определяем диапазон End With P2CountIf = Application.WorksheetFunction.CountIf(rng, criteria1)' считаем число значений с условием End Function Public Function P2CountNonZero(ColNum As Integer) As Long Dim LastRow As Long' последняя строка Dim rng As Range' диапазон расчета с условием LastRow = Worksheets("П2").Cells(Rows.Count, ColNum).End(xlUp).Row' последняя строка With Worksheets("П2") Set rng = .Range(.Cells(2, ColNum), .Cells(LastRow, ColNum)) ' определяем диапазон End With P2CountNonZero = Application.WorksheetFunction.CountIf(rng, "<>""")' считаем число значений с условием End Function Иллюстрация 2: Рис. 2 Услуга- аналитическая база данных
