Вы читаете вторую статью моего цикла "В помощь аналитику". С первой статьёй можно ознакомиться здесь. Напомню, что в первой части мы:

1. Поклялись использовать один и только один источник данных для нашего предельно сложного отчёта P&L
2. Определились с устройством данного источника (столбцы данных, структура)
3. Нашли способ, при помощи которого мы будем извлекать информацию из нашего источника данных
4. Немного поговорили про терминологию данного отчёта (что такое VOLUME, GROSS REVENUE и т.д.)

Цели этого этапа

1. Полностью сформировать и закончить отчёт P&L
2. Структурировать все секции данных и дать пользователю полную возможность самостоятельно управлять тем, что он хочет видеть в отчёте
3. Реализовать произвольный выбор регионов и типа бизнеса, то есть не только показывать цифры одной территории или всех сразу, но и дать возможность выбирать по принципу "регион 1" И "регион 3" И "регион 5", то есть произвольно (за эту возможность мне пришлось кое-чем поплатиться)
4. Быстрая смена языка (русский, английский), используемого в отчёте, что очень удобно для международных компаний
5. Продемонстрировать способы, при помощи которых сложные отчёты могут быть полностью динамическими, настраиваемыми, что избавляет нас от необходимости делать множество вариантов одного отчёта (для разных территорий, разных уровней руководства). И кстати, всё это чрезвычайно компактно - размер XLSM файла менее 1 мегабайта!

Файл для скачивания

Обзор рабочих листов

Лист DATA

Тут хранится ВСЯ информация, которая потом в агрегированном виде появляется в отчёте. Предполагается, что в реальном мире данный отчёт будет залинкован на таблицу SQL или Access.

В сравнении с файлом из первой части цикла у нас произошло одно изменение - добавился столбец MTDRE. Изучая отчёт, я вдруг осознал, что для секции MTD (Month to date) мне надо сравнивать факт за месяц с роликом (RE) за месяц, а ролика в таблице нет, так как на его месте лежит уже факт. Поэтому пришлось этот самый RE для конкретного месяца добавить и ввести дополнительное измерение (колонка MTDRE), чтобы иметь возможность разделять эти данные. Таким образом, если в строке лежит RE за текущий месяц, то MTDRE=1. Кстати говоря, мой отчёт за АПРЕЛЬ, так как PnL готовится ежемесячно и надо было выбрать какой-то месяц.

На листе созданы именованные диапазоны для каждой колонки с данными. Эти диапазоны удобно создавать через функцию ФОРМУЛЫ -> Создать из выделенного. Если вы в будущем будете линковать лист DATA на источник внешних данных (Access, SQL), то позаботьтесь об автоматическом переопределении данных диапазонов через VBA макрос.

Для информации: объём наших данных - около 9400 строк, 10 столбцов, в отдельном файле XLSX занимает 450 Кб.

Лист REF

Вспомогательный лист, на котором хранится таблица-справочник для замены языковых элементов отчёта на нужный вариант, а также объявлено 2 параметра через именованные диапазоны: selLang (текущий язык отчёта) и selCurMonth (текущий месяц отчёта - выше мы говорили про Апрель).

Лист SELECT

Данный лист играет важную роль - на нём производятся ВСЕ расчёты и хранятся ВСЕ данные, которые могут потребоваться на листе PnL. Он поделен на несколько сегментов:

Лист PnL

Это сам отчёт P&L. На этом листе НЕ производится никаких расчётов, связанных с данными. Тут сосредоточено всё, что касается визуализации данных. Это очень правильное решение - разделять расчёты и визуализацию этих расчётов. Для сложных отчётов она сильно облегчает вам задачу, позволяя сосредоточиться либо на расчётах, либо на том, как должны выглядеть ваши данные. На листе есть кнопка вызова формы, при помощи которой пользователи настраивают внешний вид отчёта.

Изменения в способе извлечения данных

К сожалению, мне пришлось отказаться от разрекламированной мною в первой части функции СУММЕСЛИМН (SUMIFS), так как данная функция не может обеспечить удобный отбор значений критериев по условию ИЛИ.

Критерий в виде именованного диапазона valGEO принимает значения "G1", "G2", "G3", "G4", "G5", либо "*", то есть либо одна территория, либо все вместе. Я же хотел дать пользователям возможность выбирать набор территорий по своему вкусу. Я видал в интернете варианты решения вместо valGEO писать {"G1";"G3";"G5"}, но у меня не получилось это заставить работать в Excel 2013. Если кто-то знает как это должно работать, то непременно мне сообщите!

В виду вышеописанной причины я был вынужден отказаться от СУММЕСЛИМН (SUMIFS) в пользу СУММПРОИЗВ (SUMPRODUCT). Проблема в том, что формула это хоть и более гибкая, но она ужасно медленная, так как обрабатывает гораздо больше данных. К тому же список параметров расширился в простейшем случае с 5 до 11. Мне даже пришлось параметры формулы разнести по отдельным строкам, чтобы не запутаться в них. Кстати я рекомендую брать этот способ на вооружение - получается очень наглядно, разбираться стало куда проще.

Конструкция
"((rngGEO=valGEO1)+(rngGEO=valGEO2)+(rngGEO=valGEO3)+(rngGEO=valGEO4)+(rngGEO=valGEO5))" реализует то, чего я добивался - возможность для пользователя указывать произвольные наборы географических регионов для анализа. То же самое и с бизнесами
"((rngBUS=valBUS1)+(rngBUS=valBUS2)+(rngBUS=valBUS3))". Тут диапазоны valGEO1-5 и valBUS1-3 ссылаются на специальный блок ячеек на листе SELECT (смотри адрес ячейки C170 и следующий экран). Если пользователь выберет к показу регионы G1+G3+G5, то в невыбранные ячейки регионов я запишу просто константу, которой заведомо нет на листе DATA в столбце GEO (я использую "FF"). Безусловно, функция СУММПРОИЗВ достойна, чтобы на ней задержаться подробнее, но я думаю, что вы, если читаете эту статью, сможете сделать это самостоятельно, иначе статья станет неудобоваримым монстром по объёму.

В некоторых ячейках (смотрите, например, С12) формула ещё сложнее из-за того, что TYPE (в данном случае DEDUC) имеет свои типы ("D1"), да ещё должен и учесть типы клиентов ("S1"), поэтому используется сложный SUBTYPE "D1S1", а формула приобретает вид:

С сожалением приходится признать, что пересчёт листа SELECT занимает на моём ноутбуке (Intel Core i3-4000M 2.4GHz) около 9 секунд (спасибо СУММПРОИЗВ, которых на листе 1764 штуки !), поэтому, если вам не критично, чтобы пользователь мог выбирать произвольные наборы регионов, то я рекомендую вернуться к более шустрой формуле СУММЕСЛИМН. Тем не менее при работе с отчётом я ревностно слежу, чтобы лист SELECT лишний раз ни дай Бог не пересчитался, поэтому он пересчитывается ТОЛЬКО тогда, когда пользователь действительно меняет набор регионов/бизнесов.

Остальные формулы, встречающиеся на этом листе, я надеюсь, не будут сложны для понимания, а если нет, то, пожалуйста, регистрируйтесь на сайте и пишите комментарии. Кстати, я в любом случае хотел бы увидеть ваши комментарии :)

Детали визуализации

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

В первую очередь хочу обратить ваше внимание, что отчёт очень тщательно структурирован, стандартизирован и формализован. Он чётко поделён на большие блоки, сегменты и отдельные типовые колонки. Опишем их:

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

За отображение блоков отвечает раздел формы Block, отображение секций - Section, отображение столбцов - Columns. Все эти элементы управления типа checkbox (галочка) привязаны через именованные диапазоны к следующим ячейкам (лист SELECT, ячейка I170):

В свою очередь эти именованные диапазоны участвуют на листе PnL в ячейках-индикаторах видимости столбцов. Это строки 1:4. Строка 2 отвечает за индикаторы показа блоков, строка 3 - секций, строка 4 - отдельных типов колонок. Строка 1 вычисляет логическое И по строкам 2:4 и именно на это значение макрос смотрит при принятии решения по скрытию или отображению данного столбца. Если столбец по факту скрыт, а значение индикатора 1, то макрос его сделает видимым, если столбец является видимым, а индикатор равен 0, то такой столбец макрос скроет. Всё просто.

Назначение остальных элементов управления пользовательской формы предельно ясно. Можно выбирать язык отчёта, территории, типы бизнеса и режим показа типов клиентов. При изменении регионов и типов бизнеса после нажатия кнопки Config & Hide Excel будет думать около 10 секунд, предварительно выведя на экран уведомление, что надо слегка подождать.

Исходный код пользовательской формы приведен в листинге и снабжен комментариями:

' Обработчик нажатия кнопки Config & Hide на форме
Private Sub btnHide_Click()
 
 ' Меняем стиль курсора
 Application.Cursor = xlWait
 
 ' Выгружаем текущую форму, чтобы отобразить форму ожидания
 Unload Me
 ' Выводим и отрисовываем форму ожидания
 frmWaiting.Show vbModeless
 ' Если не отрисовать, то из-за высокой загрузки CPU она не успеет отрисоваться в фоне
 frmWaiting.Repaint
 ' Возвращаем автоматический пересчёт формул
 ' После этого формулы начнут пересчитываться
 Application.Calculation = xlCalculationAutomatic
 
 ' Выделяем текущий регион (равносильно нажатию Ctrl+A)
 [A1].CurrentRegion.Select
 
 With Selection
 ' Перебираем в выделении столбцы
 For i = 1 To .Columns.Count
 ' Если столбец скрыт, но должен быть виден, то отменяем скрытие
 If .Columns(i).Hidden And .Cells(1, i) = 1 Then
 .Columns(i).Hidden = False
 End If
 ' Если столбец виден, но должен быть скрыт, то скрываем
 If Not .Columns(i).Hidden And .Cells(1, i) = 0 Then
 .Columns(i).Hidden = True
 End If
 Next
 ' Аналогично перебираем в выделении строки
 For i = 1 To .Rows.Count
 If .Rows(i).Hidden And .Cells(i, 1) = 1 Then
 .Rows(i).Hidden = False
 End If
 
 If Not .Rows(i).Hidden And .Cells(i, 1) = 0 Then
 .Rows(i).Hidden = True
 End If
 Next
 
 End With
 
 ' Разрешаем Excel разморозить экран
 Application.ScreenUpdating = True
 ' Возвращаем нормальный вид курсора
 Application.Cursor = xlDefault
 ' Убираем форму ожидания
 Unload frmWaiting
 ' Позиционируем текущую ячейку
 [A1].Select
End Sub

Private Sub chbB1_Change()
 If Me.chbB1 Then
 ' Ячейки valBUS1-3 и valGEO1-5 лучше не беспокоить без особой надобности,
 ' так как даже, если вы запишите в них значение, которое в них уже есть,
 ' Excel всё равно захочет пересчитать всё, что от них зависит, поэтому
 ' прежде чем их изменять, я проверяю, а есть ли смысл их менять.
 If Range("valBUS1").Item(1) <> "B1" Then Range("valBUS1").Item(1) = "B1"
 Else
 If Range("valBUS1").Item(1) <> "FF" Then Range("valBUS1").Item(1) = "FF"
 End If
End Sub

Private Sub chbB2_Change()
 If Me.chbB2 Then
 If Range("valBUS2").Item(1) <> "B2" Then Range("valBUS2").Item(1) = "B2"
 Else
 If Range("valBUS2").Item(1) <> "FF" Then Range("valBUS2").Item(1) = "FF"
 End If
End Sub

Private Sub chbB3_Change()
 If Me.chbB3 Then
 If Range("valBUS3").Item(1) <> "B3" Then Range("valBUS3").Item(1) = "B3"
 Else
 If Range("valBUS3").Item(1) <> "FF" Then Range("valBUS3").Item(1) = "FF"
 End If
End Sub

Private Sub chbG1_Change()
 If Me.chbG1 Then
 If Range("valGEO1").Item(1) <> "G1" Then Range("valGEO1").Item(1) = "G1"
 Else
 If Range("valGEO1").Item(1) <> "FF" Then Range("valGEO1").Item(1) = "FF"
 End If
End Sub

Private Sub chbG2_Change()
 If Me.chbG2 Then
 If Range("valGEO2").Item(1) <> "G2" Then Range("valGEO2").Item(1) = "G2"
 Else
 If Range("valGEO2").Item(1) <> "FF" Then Range("valGEO2").Item(1) = "FF"
 End If
End Sub

Private Sub chbG3_Change()
 If Me.chbG3 Then
 If Range("valGEO3").Item(1) <> "G3" Then Range("valGEO3").Item(1) = "G3"
 Else
 If Range("valGEO3").Item(1) <> "FF" Then Range("valGEO3").Item(1) = "FF"
 End If
End Sub

Private Sub chbG4_Change()
 If Me.chbG4 Then
 If Range("valGEO4").Item(1) <> "G4" Then Range("valGEO4").Item(1) = "G4"
 Else
 If Range("valGEO4").Item(1) <> "FF" Then Range("valGEO4").Item(1) = "FF"
 End If
End Sub

Private Sub chbG5_Change()
 If Me.chbG5 Then
 If Range("valGEO5").Item(1) <> "G5" Then Range("valGEO5").Item(1) = "G5"
 Else
 If Range("valGEO5").Item(1) <> "FF" Then Range("valGEO5").Item(1) = "FF"
 End If
End Sub

Private Sub chbS1_Change()
 If Me.chbS1 Then
 Range("valSEG1").Item(1) = "S1"
 Else
 Range("valSEG1").Item(1) = "FF"
 End If
End Sub

Private Sub chbS2_Change()
 If Me.chbS2 Then
 Range("valSEG2").Item(1) = "S2"
 Else
 Range("valSEG2").Item(1) = "FF"
 End If
End Sub

Private Sub frColumns_Click()

End Sub

Private Sub opBusAll_Click()
 Me.chbB1.Value = True
 Me.chbB2.Value = True
 Me.chbB3.Value = True

 If Range("valBUS1").Item(1) <> "B1" Then Range("valBUS1").Item(1) = "B1"
 If Range("valBUS2").Item(1) <> "B2" Then Range("valBUS2").Item(1) = "B2"
 If Range("valBUS3").Item(1) <> "B3" Then Range("valBUS3").Item(1) = "B3"
 
 Me.chbB1.Enabled = False
 Me.chbB2.Enabled = False
 Me.chbB3.Enabled = False
End Sub

Private Sub opBusManual_Click()
 Me.chbB1.Value = Range("valBUS1").Item(1) = "B1"
 Me.chbB2.Value = Range("valBUS2").Item(1) = "B2"
 Me.chbB3.Value = Range("valBUS3").Item(1) = "B3"
 
 Me.chbB1.Enabled = True
 Me.chbB2.Enabled = True
 Me.chbB3.Enabled = True
End Sub

Private Sub opEnglish_Click()
 Range("selLang").Item(1) = "ENG"
End Sub

Private Sub opGeoAll_Click()
 Me.chbG1.Value = True
 Me.chbG2.Value = True
 Me.chbG3.Value = True
 Me.chbG4.Value = True
 Me.chbG5.Value = True
 
 Me.chbG1.Enabled = False
 Me.chbG2.Enabled = False
 Me.chbG3.Enabled = False
 Me.chbG4.Enabled = False
 Me.chbG5.Enabled = False
 
 If Range("valGEO1").Item(1) <> "G1" Then Range("valGEO1").Item(1) = "G1"
 If Range("valGEO2").Item(1) <> "G1" Then Range("valGEO2").Item(1) = "G2"
 If Range("valGEO3").Item(1) <> "G1" Then Range("valGEO3").Item(1) = "G3"
 If Range("valGEO4").Item(1) <> "G1" Then Range("valGEO4").Item(1) = "G4"
 If Range("valGEO5").Item(1) <> "G1" Then Range("valGEO5").Item(1) = "G5"
End Sub

Private Sub opGeoManual_Click()
 ' Не для всех будет понятна эта конструкция
 ' Me.chbG1.Value примет значение True, если range равен указанной константе
 ' и False, если range не равен
 Me.chbG1.Value = Range("valGEO1").Item(1) = "G1"
 Me.chbG2.Value = Range("valGEO2").Item(1) = "G2"
 Me.chbG3.Value = Range("valGEO3").Item(1) = "G3"
 Me.chbG4.Value = Range("valGEO4").Item(1) = "G4"
 Me.chbG5.Value = Range("valGEO5").Item(1) = "G5"
 
 Me.chbG1.Enabled = True
 Me.chbG2.Enabled = True
 Me.chbG3.Enabled = True
 Me.chbG4.Enabled = True
 Me.chbG5.Enabled = True
End Sub

Private Sub opRussian_Click()
 Range("selLang").Item(1) = "RUS"
End Sub

Private Sub opSegAll_Click()
 Me.chbS1.Value = False
 Me.chbS2.Value = False
 
 Me.chbS1.Enabled = False
 Me.chbS2.Enabled = False
End Sub

Private Sub opSegManual_Click()
 Me.chbS1.Value = Range("valSEG1").Item(1) = "S1"
 Me.chbS2.Value = Range("valSEG2").Item(1) = "S2"
 
 Me.chbS1.Enabled = True
 Me.chbS2.Enabled = True
End Sub

Private Sub UserForm_Activate()
 
 ' Переводим Excel в режим ручного пересчета
 Application.Calculation = xlCalculationManual
 ' Замораживаем экран
 Application.ScreenUpdating = False
 
 ' Если не все регионы выбраны, то включаем Manual переключатель
 If Not (Range("valGEO1").Item(1) = "G1" And _
 Range("valGEO2").Item(1) = "G2" And _
 Range("valGEO3").Item(1) = "G3" And _
 Range("valGEO4").Item(1) = "G4" And _
 Range("valGEO5").Item(1) = "G5") Then
 Me.opGeoManual.Value = True
 End If
 
 ' Если не все бизнесы выбраны, то включаем Manual переключатель
 If Not (Range("valBUS1").Item(1) = "B1" And _
 Range("valBUS2").Item(1) = "B2" And _
 Range("valBUS3").Item(1) = "B3") Then
 Me.opBusManual.Value = True
 End If
 
 If Not (Range("valSEG1").Item(1) = "FF" And _
 Range("valSEG2").Item(1) = "FF") Then
 Me.opSegManual.Value = True
 End If
 
 If Range("selLang").Item(1) = "RUS" Then
 Me.opRussian.Value = True
 Else
 Me.opEnglish.Value = True
 End If
 
 ' Привязываем элементы управления к соответствующим ячейкам через свойство ControlSource
 Me.chbACT.ControlSource = Range("showACT").Worksheet.Name & "!" & Range("showACT").Address
 Me.chbAmount.ControlSource = Range("showAMT").Worksheet.Name & "!" & Range("showAMT").Address
 Me.chbAmtU.ControlSource = Range("showAMTU").Worksheet.Name & "!" & Range("showAMTU").Address
 Me.chbBP.ControlSource = Range("showBP").Worksheet.Name & "!" & Range("showBP").Address
 Me.chbDIFF.ControlSource = Range("showDIFF").Worksheet.Name & "!" & Range("showDIFF").Address
 Me.chbDIFFpercent.ControlSource = Range("showDIFFp").Worksheet.Name & "!" & Range("showDIFFp").Address
 Me.chbDIFFpercentU.ControlSource = Range("showDIFFpU").Worksheet.Name & "!" & Range("showDIFFpU").Address
 Me.chbDIFFU.ControlSource = Range("showDIFFU").Worksheet.Name & "!" & Range("showDIFFU").Address
 Me.chbFY.ControlSource = Range("showFY").Worksheet.Name & "!" & Range("showFY").Address
 Me.chbMTD.ControlSource = Range("showMTD").Worksheet.Name & "!" & Range("showMTD").Address
 Me.chbPY.ControlSource = Range("showPY").Worksheet.Name & "!" & Range("showPY").Address
 Me.chbRE.ControlSource = Range("showRE").Worksheet.Name & "!" & Range("showRE").Address
 Me.chbYTD.ControlSource = Range("showYTD").Worksheet.Name & "!" & Range("showYTD").Address
 Me.chbYTG.ControlSource = Range("showYTG").Worksheet.Name & "!" & Range("showYTG").Address
End Sub

Выводы

• В первую очередь, я ставил перед собой цель продемонстрировать, как можно проектировать сложный корпоративный отчёт и при этом не потерять контроль по трудозатратам. Если вы упустите свой отчёт и он начнёт обрастать множеством источников данных, множеством экземпляров таблиц для визуализации, то любое самое мелкое изменение этого отчёта для вас превратится в большую проблему и будет требовать много сил и времени. В нашем же случае всё находится в единственном экземпляре и меняется очень просто. Эту цель мы достигли.
• Во вторую очередь, я хотел показать, что задача, которую мы решили не так уж и сложна, как могло показаться на первый взгляд, что лишний раз доказывает, что аналитикам, прежде чем браться за серьёзный проект, необходимо провести серьёзные исследовательские изыскания и изначально заложить здоровую базу в свой проект, а не то, что первое пришло в голову, потому что потом с этим придётся жить.
• В третьих, мы успешно продемонстрировали ключевые технологии Excel, которые, будучи примененные совместно, дают очень неплохие результаты. Умение применять знания в комплексе, умение комбинировать простые инструменты - ключевой навык для профессионала в области обработки информации.

Читайте также:

• В помощь аналитику (часть 1)

• В помощь аналитику (часть 3)

• Ценные советы начинающим аналитикам