Excel/PowerQuery/VBA, Анализ данных

Насколько лучше в цвете

Время прочтения: 2 мин.

В своей работе мы практически ежедневно сталкиваемся с таблицами Excel. Для их анализа, зачастую, используем разноцветные заливки ячеек. Визуализация данных, безусловно, помогает пользователю легче усваивать содержимое таблицы. Но, что делать, когда наступает момент подсчета результата? Для этого мы предлагаем не использовать стандартный подход применения фильтров по цвету, а воспользоваться редактором Microsoft Visual Basic (далее VBA), который есть в любом стандартном пакете от Microsoft Office.

Запускать его будем в Microsoft Excel по следующему алгоритму:

  • запускаем MS Excel;
  • входим во вкладку «Вид»;
  • нажимаем «Макросы»;
  • в отрывшемся окне вводим название нашей будущей программы (разрешены английские буквы и символы);
  • нажимаем «Создать».

Теперь мы в редакторе Visual Basic, и чтобы осуществить расчет по количеству залитых ячеек используем следующий код:

Public Function SumColour(DataRange As Range, ColorSample As Range) As Double 
Dim cell As Range
Dim SumAll As Long
Application.Volatile True
SumAll = 0
For Each cell In DataRange
If cell.Interior.Color = ColorSample.Interior.Color Then
SumAll = SumAll+ 1
End If
Next cell
SumColour = SumAll
End Function

Так, с помощью функции SumColour, можно пересчитать количество ячеек.

Для пересчета суммы окрашенных ячеек берем вышеописанный код, меняем название функции (например, SumByColour) и строку кода «SumAll = SumAll+ 1» на «SumAll = SumAll+ cell.Value».

На практике использование редактора VBA помогает пересчитывать большие объемы данных не опасаясь ошибок, связанных с сортировкой и фильтрацией в Excel.

А какой способ используете вы? Делитесь в комментариях!

Советуем почитать