Summieren basierend auf der Formatierung in benachbarten Zellen (Microsoft Excel)
Srinivas hat Daten in beiden Spalten A und B. Er muss die Werte in Spalte B summieren, für die das Zellenformat der entsprechenden Zellen in Spalte A gelb ist. Wenn beispielsweise das Format in Zelle A17 gelb ist, sollte der Wert in Zelle B17 in der Summe enthalten sein.
Im Internet stehen zahlreiche Makros zur Verfügung (einschließlich ExcelTips), mit denen Sie bedingte Summierungen basierend auf der Farbe oder einem anderen Format einer Zelle durchführen können. Dieser Bedarf unterscheidet sich jedoch darin, dass es sich nicht um die Farbe der betreffenden Zelle handelt, sondern um die Farbe der Zelle eine Spalte links. Dies kann weiterhin mit einem Makro erfolgen, wie hier gezeigt:
Function SumNextYellow(ByVal r As Range) Dim c As Range Dim a As Double For Each c In r If c.Offset(0, -1).Interior.ColorIndex = 6 Then 'Yellow a = a + c.Value End If Next c SumNextYellow = a End Function
Die Funktion kann in einer Arbeitsblattformel verwendet werden und akzeptiert eine Bereichsreferenz als Argument. Anschließend wird jede Zelle im Bereich durchlaufen. Wenn die Zelle links gelb ist, ist der Wert in der Summe enthalten. (Sie sollten beachten, dass der im Makro verwendete ColorIndex mit Ihrer Excel-Version getestet werden sollte, um sicherzustellen, dass er anwendbar ist. Er kann in verschiedenen Versionen unterschiedlich sein.)
Ein viel robusteres Beispiel zeigt die folgende Auflistung. Diese Funktion akzeptiert einen oder mehrere Zellbereiche sowie ein Argument, das ein Beispiel für die Formatierung darstellt, die Sie verwenden möchten.
Function ColorConditionSum(cSample As Excel.Range, rng As Excel.Range) ' This Function returns sum of values in rng.Columns(2) if ' corresponding cell in rng.Columns(1) is colored with sample ' color (cSample cell) ' Arguments: cSample = cell colored by sample color ' rng = cell range to be processed (2 columns) Dim rngCol2 As Excel.Range Dim rngConstants As Excel.Range Dim rngFormulas As Excel.Range Dim lColorIndex As Long Dim MySum As Double Dim area As Excel.Range Dim c As Excel.Range ColorConditionSum = False If Not TypeOf cSample Is Excel.Range Then Exit Function '>>> lColorIndex = cSample.Interior.ColorIndex MySum = 0 Set rngCol2 = Nothing If TypeOf rng Is Excel.Range Then If rng.Columns.Count < 2 Then Exit Function '>>> On Error Resume Next For Each area In rng.Areas If rngCol2 Is Nothing Then Set rngCol2 = area.Columns(2).SpecialCells(xlCellTypeConstants, 1) If rngCol2 Is Nothing Then Set rngCol2 = area.Columns(2).SpecialCells(xlCellTypeFormulas, 1) Else Set rngCol2 = Application.Union( _ rngCol2, area.Columns(2).SpecialCells(xlCellTypeFormulas, 1)) End If Else Set rngCol2 = Application.Union( _ rngCol2, area.Columns(2).SpecialCells(xlCellTypeConstants, 1)) Set rngCol2 = Application.Union( _ rngCol2, area.Columns(2).SpecialCells(xlCellTypeFormulas, 1)) End If Next area For Each area In rngCol2.Areas For Each c In area.Cells With c.Offset(0, -1) If .Interior.ColorIndex = lColorIndex Then MySum = MySum + c.Value End If End With Next c Next area End If ColorConditionSum = MySum End Function
Sie verwenden diese Funktion in einem Arbeitsblatt folgendermaßen:
=ColorConditionSum(A10, A12:B22)
In diesem Fall handelt es sich um eine Zelle mit der Innenfarbe, die Sie anpassen möchten, und A12: B22 ist der Bereich der zu bewertenden Zellen. Die Werte werden aus der zweiten Spalte im Bereich abgerufen und die Formatierung für die Zellen in der ersten Spalte überprüft.
_Hinweis: _
Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (3298) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier: