Количество или сумма ячеек определенного цвета
Попросили меня в одной мегатаблице великого и ужасного MS Excel сделать чтобы считались суммы в ячейках залитых только зеленым цветом, или желтым, или вообще любым. Ибо тупо ячейка+ячейка подходит при небольших объёмах, а когда количество строк за тысячу перевалило (очередной акт сверки стоимости материалов, хорошо что не мне его делать выпало) – это уже рябь в глазах и помутнение в мозгах.
Ко мне обратились как будто это знаю и умею. А я не знаю и не умею. Но гугль, как Вергилий в адском интернете, помог и в этот раз. Набрёл на макрос на Visual Basic который и позволяет провернуть нужную операцию. Причем на выбор или сумму значений или просто количество ячеек определенного цвета.
Ну с бейсиком я еще в институте поссорился, поэтому тупо скопировал пример а он взял и заработал.
Итак, для таких же гениев в областях далёких от программирования, как и я:
В нужной книге excel нажать (Alt+F11), вылезет редактор этого Visual Basic. Там выбрать Insert > Module, и в окно ввести следующий код:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell,vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
Сохранить, закрыть, и в результате в списке формул появится ColorFunction, которая и будет всё считать.Сама формула будет выглядеть так:
=ColorFunction(D1;D1:D1295;"true")
или
=ColorFunction(D1;D1:D1295;"false")
где
D1 – ячейка-образец по цвету которой будет выбираться, что считать.
D1:D1295 – диапазон, где считается
“true” – сумма значений в ячейках заданного цвета
“false” – количество ячеек
Важное примечание: при простой замене цвета ячейки сумма сама по себе пересчитываться не будет. Нужно либо нажать Ctrl+Alt+F9, либо заново ввести формулу.
Программисты – они все больные на голову. Конечно молодцы что такое придумывают, но всё равно больные.
Да уже, кстати самого на работе с такой ботвой озадачивали. Но сослался на невозможность таких манипуляций =). Так жить проще.
Хотя сказал, что в последних версиях екселя это есть опционально, но руководителю не нравится офис 2007, и это помогло =).
В 2007 я такого не нашёл.
Мне тоже было бы лень, но с бухгалтерией стараюсь поддерживать хорошие отношения. Да и самому стало интересно, возможно ли такое.
Не работает в условном форматировании
День добрый! всё получилось! спасибо!
По дскажите только после сохранения и закрытия документа всё слетает….пишит #ИМЯ?
как воосстановить чтоб работало снова?