Inhalte
Manchmal dauert es sehr lange, bis man sich etwas einfallen lässt. Aber wenn sie SCHON erfunden sind, wirken sie im Nachhinein selbstverständlich und sogar banal. Aus der Serie „Was, es war möglich?“.
Von den allerersten Versionen an zeigte die Statusleiste am unteren Rand des Microsoft Excel-Fensters traditionell Summen für ausgewählte Zellen an:
Auf Wunsch war es sogar möglich, mit der rechten Maustaste auf diese Ergebnisse zu klicken und aus dem Kontextmenü genau auszuwählen, welche Funktionen wir sehen möchten:
Und erst kürzlich haben die Microsoft-Entwickler in den neuesten Excel-Updates eine einfache, aber geniale Funktion hinzugefügt – wenn Sie jetzt auf diese Ergebnisse klicken, werden sie in die Zwischenablage kopiert!
Schönheit.
Aber was ist mit denen, die noch nicht (oder schon?) über eine solche Version von Excel verfügen? Hier können einfache Makros helfen.
Kopieren der Summe ausgewählter Zellen in die Zwischenablage mithilfe eines Makros
Im Tab öffnen Entwickler (Entwickler) Herausgeber Visual Basic oder verwenden Sie diese Tastenkombination Andere+F11. Neues leeres Modul über Menü einfügen Einfügen – Modul und kopiere dort folgenden Code:
Sub SumSelected() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection) .PutInClipboard End With End Sub Seine Logik ist einfach:
- Zuerst kommt der „Schutz vor dem Narren“ – wir prüfen, was genau hervorgehoben wird. Wenn keine Zellen ausgewählt sind (aber beispielsweise ein Diagramm), beenden Sie das Makro.
- Dann mit dem Befehl Objekt abrufen Wir erstellen ein neues Datenobjekt, in dem später unsere Summe der ausgewählten Zellen gespeichert wird. Ein langer und unverständlicher alphanumerischer Code ist in Wirklichkeit ein Link zum Zweig der Windows-Registrierung, in dem sich die Bibliothek befindet Microsoft Forms 2.0-Objektbibliothek, die solche Objekte erstellen kann. Manchmal wird dieser Trick auch genannt implizite späte Bindung. Wenn Sie es nicht verwenden, müssen Sie in der Datei über das Menü einen Link zu dieser Bibliothek erstellen Werkzeuge – Referenzen.
- Die Summe der ausgewählten Zellen wird als Befehl betrachtet ArbeitsblattFunktion.Summe(Auswahl), und dann wird der resultierende Betrag mit dem Befehl in die Zwischenablage gelegt PutInClipboard
Zur einfacheren Handhabung können Sie dieses Makro natürlich über die Schaltfläche einem Tastenkürzel zuweisen Makros Tab Entwickler (Entwickler – Makros).
Und wenn Sie nach dem Ausführen des Makros sehen möchten, was genau kopiert wurde, können Sie das Clipboard-Panel mit dem kleinen Pfeil in der unteren rechten Ecke der entsprechenden Gruppe einschalten Zu den wichtigsten (Home) Tab:
Nicht nur die Menge
Wenn Sie neben dem banalen Betrag noch etwas anderes wollen, können Sie alle Funktionen nutzen, die uns das Objekt bietet ArbeitsblattFunktion:
Zum Beispiel gibt es:
- Summe – Summe
- Durchschnitt – arithmetisches Mittel
- Count – Anzahl der Zellen mit Zahlen
- CountA – Anzahl der gefüllten Zellen
- CountBlank – Anzahl der leeren Zellen
- Min – Mindestwert
- Max – Maximalwert
- Median – Median (zentraler Wert)
- … Etc.
Was passiert, wenn Zeilen oder Spalten im ausgewählten Bereich ausgeblendet sind (manuell oder durch einen Filter)? Um sie in den Summen nicht zu berücksichtigen, müssen wir unseren Code leicht ändern, indem wir das Objekt hinzufügen Auswahl Resorts SpecialCells(xlCellTypeVisible):
Sub SumVisible() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.SpecialCells(xlCellTypeVisible)) . PutInClipboard Ende mit End Sub In diesem Fall wird die Berechnung einer Gesamtfunktion nur auf sichtbare Zellen angewendet.
Wenn Sie eine lebende Formel brauchen
Wenn Sie sich etwas ausdenken, können Sie sich Szenarien ausdenken, in denen es besser ist, keine Zahl (Konstante), sondern eine lebendige Formel in den Puffer zu kopieren, die die Summen berechnet, die wir für die ausgewählten Zellen benötigen. In diesem Fall müssen Sie die Formel aus Fragmenten kleben, das Entfernen von Dollarzeichen hinzufügen und das Komma (das in VBA als Trennzeichen zwischen Adressen mehrerer ausgewählter Bereiche verwendet wird) durch ein Semikolon ersetzen:
Sub SumFormula() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Replace(Replace(Selection. Adresse, ",", ";"), "$", "") & ")" .PutInClipboard End With End Sub Summation mit zusätzlichen Bedingungen
Und schließlich können Sie für völlig Verrückte ein Makro schreiben, das nicht alle ausgewählten Zellen zusammenfasst, sondern nur diejenigen, die die angegebenen Bedingungen erfüllen. So sieht zum Beispiel ein Makro so aus, dass die Summe ausgewählter Zellen in den Puffer legt, wenn deren Werte größer als 5 sind und sie gleichzeitig mit einer beliebigen Farbe gefüllt sind:
Sub CustomCalc() Dim myRange As Range If TypeName(Selection) <> "Range" Then Exit Sub For Each cell In Selection If cell.Value > 5 And cell.Interior.ColorIndex <> xlNone Then If myRange Is Nothing Then Set myRange = cell Else Set myRange = Union(myRange, cell) End If End If Next cell With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .PutInClipboard End With End Sub Wie Sie sich leicht vorstellen können, können Bedingungen absolut beliebig – bis hin zu Zellformaten – und in beliebiger Menge (auch durch Verknüpfung mit logischen Operatoren oder oder und) gesetzt werden. Es gibt viel Raum für Fantasie.
- Formeln in Werte umwandeln (6 Wege)
- Was sind Makros, wie werden sie verwendet, wo wird Visual Basic-Code eingefügt?
- Nützliche Informationen in der Statusleiste von Microsoft Excel