Monat: Mai 2016

Summe nach Zellfarbe bilden

Roman Allgemein, Formeln, VBA (Makros) , , , , ,

Eine SUMMEWENN-Funktion berücksichtigt zwar Kriterien und bildet bei Erfüllen dieser die Summe der jeweiligen Werte, beispielsweise die Zellfarbe kann allerdings nicht ohne weiteres als Kriterium erkannt und berücksichtigt werden. Dazu benötigt es einen zusätzlichen Schritt, welchen wir in diesem Beitrag betrachten:

Im Menüband gehst Du unter "Formeln" auf "Name definieren". Gib dort als Name "Farbe" ein - dies ist später der Name einer Formel, welche wir in eine Hilfsspalte eingeben werden (Du kannst daraufhin "=Farbe" in eine Zelle eingeben). Bei "Bezieht sich auf" gibst Du folgendes ein:

=ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))

Zusammengefasst sieht Dein definierter Name wie folgt aus:

ZellfarbeName

[Der Bezug ZS(-1) ist ein Referenzbezug: Dieser bedeutet, dass Du auf die selbe Zeile (Z), allerdings auf eine Spalte (S) nach links (-1) zugreifst.]

Wenn Du nun eine Spalte rechts Deiner farbigen Zelle "=Farbe" eingibst, wirst Du eine Zahl erhalten - diese bezieht sich auf die Zellfarbe. Wenn Du die Zellfarbe änderst, wird sich auch das Resultat in der Zelle nebenan verändern, sobald Du die "=Farbe"-Formel aktualisierst (oder F9 auf der Tastatur zur Kalkulation des gesamten Tabellenblatts drückst). Nun kannst Du die bekannte SUMMEWENN-Formel verwenden und alle Werte summieren, welche in der Spalte nebenan den entsprechenden Farbcode aufweisen. In diesem Beispiel erstelle ich die Summe aller orangen Zellen (mit dem Farbcode 44):

SummewennZellfarbe

(10 + 2 + 3 + 5 + 10 = 30)

Wichtig zu beachten ist, dass Du Deine Datei daraufhin als .xlsm (Makro-Datei) abspeichern musst, damit die "=Farbe"-Funktion auch zu einem späteren Zeitpunkt wieder funktionsfähig ist.

Makro als Add-In einbetten

Roman VBA (Makros) , , , , , ,

Wir haben uns in diesem früheren Beitrag (Suchmaschine programmieren) schon angeschaut, wie man bei einem Objekt (beispielsweise einem Textfeld) ein Makro hinterlegen kann – damit dieses quasi als Knopf dient, um das Programm zu starten. Ich erläutere hier, wie man ein sogenanntes Add-In erstellen kann und dieses Makro dann über Symbole in der Menüleiste aufrufen kann.

Als erstes musst Du Dein Makro aufzeichnen/programmieren. Du verwendest möglicherweise den Makro-Rekorder (wie in diesem Beitrag erklärt) und gelangst mittels "Alt+F11" in die Makro-Umgebung, wo Du auch Dein Makro findest. Sobald alles passt und die richtigen Befehle ausgeführt werden, speichere Deine Excel-Tabelle ab als "Add-In"-Datei (.xlam):

AddInDatei

Daraufhin schliesst Du sämtliche Excel-Mappen und öffnest Excel erneut – so werden im Hintergrund sämtliche Add-Ins geladen.

Du musst nun das Add-In lokalisieren und in Excel aktivieren:

Im Menüband auf Datei → Optionen → Add-Ins und unten auf "Gehe zu" klicken. Daraufhin durchsuchst Du Dein Laufwerk nach dem abgelegten Add-In:

AddInLokalisieren

Danach kannst Du mit einem Rechtsklick auf die Menüleiste oben "Menüband anpassen" selektieren. Als erstes kannst Du auf der rechten Seite eine neue, eigene Sektion erstellen. Klicke dazu auf "neue Registerkarte" und benenne diesen zum Beispiel mit "Makros" (Rechtsklick auf die erstellte Registerkarte → "Umbenennen"). Die darin enthaltene Gruppe musst Du ebenfalls benennen – ich nehme hier "Tätigkeit1". Falls Du auf mehrere Makros schnell zugreifen willst, würde ich weitere Gruppen ("Tätigkeit2", "Tätigkeit3", ...) anlegen. Das sieht dann so aus:

BenutzerdefinierteGruppe

Als nächstes suchst Du auf der linken Seite bei "Befehle auswählen" in der Rubrik "Makros". Dort solltest Du Dein aufgezeichnetes Makro finden, welches Du nun der Gruppe "Tätigkeit1" zuteilen kannst. Stelle sicher, dass Du die Gruppe im rechten Abschnitt grau markiert hast und klicke dann in der Mitte auf "Hinzufügen", um das Makro der Gruppe zuzuordnen. Nun hast Du Dein Add-In in Deinem eigenen Menüpunkt jederzeit schnell zur Hand! Innerhalb der Gruppe bei "Menüband anpassen" kannst Du via Rechtsklick → "Umbenennen" auch noch das Symbol des Makros anpassen:

ButtonUmbenennen

Das Add-In kann auch an andere Personen übertragen werden, diese müssen es wiederum unter Datei → Optionen --> Add-Ins lokalisieren und in ihre Menüleiste mitaufnehmen.

Als Text gespeicherte Zahlen umwandeln

Roman Allgemein , , , ,

Hattest Du das schon einmal? Eine Zahl ist in Excel als Text formatiert und deshalb kannst Du damit nicht weiterrechnen. Excel markiert Dir solche als Text formatierte Zahlen mit einem Hinweis (grünes Dreieck am linken oberen Zellenrand):

AlsTextGespeicherteZahl

Du könntest via diesen Hinweis auf "in eine Zahl umwandeln" klicken. Bei grösseren Tabellen rate ich Dir davon allerdings ab - dieser Befehl beansprucht nämlich enorme Rechenleistung. Schneller geht dieses Vorgehen, wenn Du wie folgt vorgehst:

Schreibe die Zahl "1" in eine Hilfszelle und kopiere diese Zelle ("CTRL + C", respektive Rechtsklick → "Kopieren"). Selektiere daraufhin Deine gesamten als Text formatierten Zahlen und gehe via Rechtsklick auf "Inhalte einfügen...". Dort selektierst Du dann "Werte" und weiter unten "Multiplizieren" und klickst auf "OK":

TextInZahlUmwandeln

Nun hast Du sämtliche als Text formatierten Zahlen mit 1 multipliziert. Dieser Vorgang hat im Nu allesamt in Zahlen umgewandelt, die Werte an sich bleiben durch die Multiplikation mit 1 logischerweise bestehen - jetzt funktioniert auch beispielsweise eine SUMME-Formel wieder einwandfrei.