Excel
Bedingte Formatierung
- Eine sehr nützliche Funktion in einer Tabellenkalkulation ist die bedingte Formatierung, dass in Abhängigkeit von Werten einzelne Zellen oder Zeilen farbig formatiert werden und so der Inhalt leichter interpretierbar ist.
- Etwas tricky ist die Anforderung eine Zelle farbig zu formatieren in Bezug auf eine Spalte. Die Spalte muss hier mit "$" angegeben werden, damit immer die gleiche Spalte als Referenz verwendet wird. Die Zeile jedoch nicht mit "$", da in jeder Zeile der entsprechende Wert abgefragt werden soll.
- Ein Beispiel für eine persönliche kleine Ticketsammlung
- Hier soll die Spalte F mit einem Status eines Tickets die Farbe der Zeile bestimmen, entweder Q oder P oder nichts. Dazu werden nun die Werte Q und P abgefragt.
Formel
=$F2="Q"
bzw.
=$F2="P"
und Anwendung auf den Bereich. 441 war hier nur ein Wert für eine Zeile, die nie erreicht werden wird. Als einzige Ausnahme soll die Überschriftenzeile 1 nicht formatiert werden in Abhängigkeit von der Spalte F.
=$2:$441
VBA/Makros
Entwicklungsumgebung
- alt+F11
Makrosicherheit
Makrorecorder
- Makros lassen sich in Word und Excel sehr komfortabel aufzeichnen, um so das Coding für bestimmte Funktionen zu verstehen oder komplette Anforderungen aufzeichnen zu lassen.
- Makro - Makro aufzeichnen
- Das Makro lässt sich wahlweise in der aktuellen Arbeitsmappe/Excel-Datei oder in der persönlichen Makroarbeitsmappe speichern
Vorgehensweise
- Makro anlegen mit sprechendem Namen
- Makro starten
- Excel-Funktionen ausführen
- Makro stoppen
Persönliche Makroarbeitsmappe
- Alle Makros, die in der "Persönlichen Makroarbeitsmappe" gespeichert werden, stehen in jeder Excel-Datei zur Verfügung, die auf dem eigenen PC geöffnet wird.
Siehe hierzu
- http://www.office2010-blog.de/de/start/excel/excel-2010-die-eigene-makroarbeitsmappe
- https://support.office.com/de-de/article/Erstellen-und-Speichern-s%C3%A4mtlicher-Makros-in-einer-einzelnen-Arbeitsmappe-aa439b90-f836-4381-97f0-6e4c3f5ee566
Dateneingabeformular
Formeln
IF (WENN)
=IF(<bedingung>;"True-Text";"False-Text")
=IF(A2>B2;"Budget überschritten";"OK")
bzw.
=WENN(A2>B2;"Budget überschritten";"OK")
IFS (WENNS)
=IFS(<bedingung1>; "TRUE1-Text"; <bedingung2>; "TRUE2-Text";...)
=IFS(A2 > 90; "A"; A2 > 80; "B")
NOW
Die Formel NOW zeigt das aktuelle Datum mit aktueller Uhrzeit an.
=NOW()
Zum Beispiel
29.11.2018 10:23
WECHSELN
Eine sehr praktische Formel kann WECHSELN sein. Die Formel sucht in einem Text nach vorgegebenen Zeichenfolge und ersetzt diese durch eine andere vorgegebene Zeichenfolge.
Man kann diese Formel auch nutzen, wenn man folgende Aufgabenstellung hat:
text1|text2|text3
soll beim Trennzeichen durch einen Zeilenumbruch ersetzt werden.
text1 text2 text3
Beispielanwendung
In Zelle C66 steht der Beispieltext
Anton|Berta|Chilli
Die Formel lautet nun
=WECHSELN(ERP_neu!$Q$2;"|";O67)
Argumente:
- ERP_neu!$Q$2 = Verweis auf das Registerblatt ERP_neu mit der absoluten Referenz auf die Zelle Q2 (hier steht der Text "Anton|Berta|Chilli")
- | = das gesuchte Trennzeichen zwischen den Texten
- O67 = Referenz auf eine Zelle (hier gelb markiert), wo ein Zeilenumbruch eingefügt wurde (da es kein Zeichen für einen Zeilenumbruch gibt, der direkt in die Formel eingefügt werden kann)
Screenshot mit Beispiel
Referenzen/Zellbezügen
Bei absoluten Bezügen wird ein "$"-Zeichen vorangestellt, also z. B. "$A$1" verweist auf die Zelle A1. Auch wenn z. B. eine erste Zeile eingefügt wird, bleibt die Referenz immer noch auf "A1". Bei relativen Bezügen werden die Dollarzeichen weggelassen.
Referenz auf Zellen auf gleichem Arbeitsblatt
Absolute Referenz
Auch bei der Verschiebung der referenzierenden Zelle bleibt die Referenz konstant.
=$A$2
Relative Referenz
=A2
Würde hier eine erste Zeile hinzugefügt, würde die Referenz sich ändern auf
=B2
oder beim Hinzufügen einer ersten Spalte zu
=A3
Referenz Inhalte auf anderem Arbeitsblatt
Excel kann eine Referenz auf Zellen anderer Arbeitsblätter erstellen.
Absolute Referenz
Hier referenziert die Zelle "C7" vom Arbeitsblatt "Vergabenotiz" auf das Register-/Arbeitsblatt "ERP16" mit einem absoluten Bezug zur Zelle "A2". Die Trennung zwischen Arbeitsblatt und Zelle erfolgt über das "!". Das '-Zeichen ist hier nur nötig, da in "ERP16" eine schließende Zahl ist.
='ERP16'!$A$2
Man sieht, dass in der Zelle "A2" von der Registerkarte "ERP16 der Wert "Eberstein" steht.
Relative Referenz
='ERP16'!A2
Excel zu MediaWiki
Excel und CSV
- Öffnet man eine CSV-Datei direkt in Excel, dann passiert mit den Inhalten der CSV-Datei oft Dinge, die nicht erwünscht sind. Z. B. wird eine Postleitzahl "01234" zu "1234", weil Excel es als Zahl interpretiert und daher die 0 weglässt.
- https://danielpeters.eu/csv-dateien-mit-microsoft-exel-richtig-oeffnen.html hat eine Anleitung, wie man Excel dazu bringt die Inhalte der CSV-Datei mit korrekten Inhalten darzustellen, aber in Spalten.
- Ein Verbesserungsvorschlag an Microsoft: Wenn in Excel eine CSV-Datei geöffnet wird, erscheint eine Abfrage "Sollen die Inhalte der CSV-Datei interpretiert werden oder möchten Sie sie unformatiert darstellen?" Ich bin sicher 99,9% aller Anwender werden dann das unformatierte Öffnen wählen. Leider ist Microsoft bei solchen Problemen, die seit Jahrzehnten existieren, häufig nicht bereit mitzudenken.
Exel aus SAP mit OLE2
- Man kann aus SAP heraus komplexe Excel-Dateien ergzeugen
- Im OLE2-Objektmodell sind alle Funktionalitäten vom MS-Office gekapselt
- Es ist jedoch nicht trivial aus SAP heraus eine Excel-Datei zu erzeugen
- Siehe https://answers.sap.com/questions/5609371/create-excel-sheet-using-ole.html
Web-Links
- www.tippscout.de: Absolute und relative Bezüge in Excel
- www.excel-helfer.de: Excel-Zellbezügen aus einem anderen Tabellenblatt
- http://excelbibel.de/datenformular-excel/
- https://danielpeters.eu/csv-dateien-mit-microsoft-exel-richtig-oeffnen.html
- https://answers.sap.com/questions/5609371/create-excel-sheet-using-ole.html