Количество или сумма ячеек определенного цвета

Попросили меня в одной мегатаблице великого и ужасного 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, либо заново ввести формулу.

Программисты – они все больные на голову. Конечно молодцы что такое придумывают, но всё равно больные.

  • IGOR-R-RYAN
    27 ноября 2009 г. в 13:03 | #1

    Да уже, кстати самого на работе с такой ботвой озадачивали. Но сослался на невозможность таких манипуляций =). Так жить проще.
    Хотя сказал, что в последних версиях екселя это есть опционально, но руководителю не нравится офис 2007, и это помогло =).

    • 27 ноября 2009 г. в 15:10 | #2

      В 2007 я такого не нашёл.
      Мне тоже было бы лень, но с бухгалтерией стараюсь поддерживать хорошие отношения. Да и самому стало интересно, возможно ли такое.

  • Максим
    12 января 2010 г. в 07:21 | #3

    Не работает в условном форматировании

  • Дмитрий
    19 ноября 2012 г. в 10:29 | #4

    День добрый! всё получилось! спасибо!
    По дскажите только после сохранения и закрытия документа всё слетает….пишит #ИМЯ?
    как воосстановить чтоб работало снова?

Оставить комментарий

XHTML: Вы можете использовать эти теги: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>