Category: Office

Verschachtelte WENN-Funktion – Fall 6

Mit Hilfe der verschachtelten WENN-Funktion können auch Werte in unterschiedliche und sich nicht überschneidende Zahlenbereiche einsortiert werden.

Im nachfolgenden Fall gilt es, einen Einnahmenwert in Zelle C3 zu prüfen und den passenden Provisionswert zu identifizieren.

at32Groß

Wichtig ist, dass sich die Bereiche gegenseitig ausschließen und auch keine Lücken aufweisen.

at32Zahlen

Man kann sich vorstellen, dass die WENN-Funktion im Falle von noch mehr Provisionsbereichen sehr komplex und kompliziert zu lesen sein würde.

Von daher sollte als Alternative die Funktion VERWEIS überdacht werden.

Die Datei zum nachverfolgen der Übung:

at0032 Verschachtelte WENN-Funktion 6 herunterladen

Hier geht es zum YouTube-Video

Videolink: https://youtu.be/FEtmm5mjKv8

Viel Spaß beim Nachbasteln und sonnige Grüße
Andreas

Lösung 1 zu Übung 2 – at Excel Experts

Die folgende Liste hat leider in den ersten drei Spalten verbundene Zellen. Das Auge ist schlau genug, die Daten korrekt zuzuweisen. Tabellenauswertungen u.a. Pivot können allerdings nicht direkt auf diesen Bereich angewandt werden.

E793Ausgang

Also steht zunächst ein Umbau an.

Hier die Datei zum Herunterladen:

Gegliederte Aufstellung Lösung 1 Übung 2 – Ausgang

Zum YouTube-Video:

Videolink: https://youtu.be/0weIjlYQoNw

Die Schritte noch einmal zusammengefasst:

  1. Korrekte Spalten markieren
  2. STRG + G für Gehe Zu
  3. Inhalte
  4. Leerzellen markieren
  5. Nun in aktive Zelle per Formel auf Zelle darüber verweisen. In C3 z.B. die Formel =C2
  6. Nun mit STRG + ENTER den kompletten Bereich mit der Formel füllen.
  7. Bereich erneut komplett markieren und kopieren.
  8. Beim Einfügen nur die harten, formelfreien Werte einfügen

E793Lösung

Das sieht schon besser aus. Nicht schön, aber funktional.

Viel Spaß beim Nachbasteln.

Sonnige Grüße
Andreas

Liste der Outlook-Videos

Eine Liste meiner mehr als 100 Outlook-Videos. Die Laufzeit beträgt über 9 Stunden, da sollte doch für die meisten Outlook-Fragen etwas dabei sein. Die früheren Titel wurden unter Outlook 2010 und die neueren Titel unter Outlook 2013 aufgezeichnet. Als Server konnte ich auf einen Exchange zurückgreifen.

Die Titel sind auch im Umfang des aktuellen Excel700-Sticks auf dem Zusatzstick enthalten. https://thehos-public.sharepoint.com/tutorials-privat bzw. für Unternehmen https://thehos-public.sharepoint.com/tutorials

Viel Erfolg mit Outlook
Andreas

Schnellbausteine – Fertige Textbausteine in die E-Mail einfügen http://youtu.be/t3i_GO3FhKk
Kontakte für Serienbrief bzw. Serienmail vorbereiten http://youtu.be/z6mOrz-COR0
E-Mail-Vorlage – E-Mail als Vorlage Typ *.oft speichern http://youtu.be/XC7N3rauiEA
Kontakte exportieren – Adressen in csv-Datei exportieren http://youtu.be/f89KAefhJpU
Automatische Antworten – Abwesenheitsassistent http://youtu.be/qM4-SqFTA-g
Automatisch auf jede E-Mail antworten – Abwesenheitsassistent http://youtu.be/TxJOIU2I0sA
Postfach für andere Nutzer freigeben – Arbeiten im Team http://youtu.be/PlthFtHKO9c
Postfach anderer Person einbinden – Arbeiten im Team http://youtu.be/PC5SUqeR8vM
Kontakte vor Globaler Adressliste – Reihenfolge der Adressbücher ändern http://youtu.be/F9fff-mWGP4
Reihenfolge von Vor- und Nachnamen tauschen – Kontakte http://youtu.be/n-6c-UhnN5Q
Lesebestätigungen erstellen, verwalten und vermeiden http://youtu.be/3A7OPvMmDPo
mehrere Postfächer in Outlook einbinden – Exchange http://youtu.be/0p2hIg0WXjE
zweite Zeitzone einstellen http://youtu.be/ix5WbejwduI
Serienmail – individualisierte E-Mails versenden http://youtu.be/FF1u6ZOlIIM
Firmenevents, Messen und Projekttermine als Ereignisse importieren http://youtu.be/op4ufK500sI
Kategorien http://youtu.be/SJ08-paktS0
Kennzeichnung zur Nachverfolgung – Rote Fahne http://youtu.be/_FPzZRqBr_M
Datendatei umbenennen – pst-Datei http://youtu.be/vtcaKMWgzM0
Datendatei erstellen – pst-Datei http://youtu.be/7KvvVPWQLLI
Postfach in pst-Datei sichern – Archivieren http://youtu.be/MqmKAYnbGzU
Alte PST-Dateien identifizieren – 97-2002-Format http://youtu.be/OJL5wWtY-n0
Schriftfarbe bei Antworten ändern – schwarz statt blau http://youtu.be/zrhLLgBIkAg
Weiterleitung von E-Mails bei Abwesenheit per Regel http://youtu.be/fxLsoJGZfhk
E-Mail-Antwort an andere Personen senden lassen http://youtu.be/FQ1Jezl6aKk
E-Mails später versenden – Übermittlung verzögern http://youtu.be/10kCDI7TOIY
Erinnerungsfenster nicht im Vordergrund unter Windows 7 http://youtu.be/lR2wyyF64zw
Erinnerungen im Vordergrund anzeigen lassen – Benachrichtigungen http://youtu.be/7SAihH1tCvc
Ansicht für E-Mails organisieren http://youtu.be/SzE5U-fSWFU
Wetter im Kalender anzeigen http://youtu.be/uMaJwe9orkc
Automatische Antworten 1 – Abwesenheits-Assistent http://youtu.be/hY6Lt50W8YU
Automatische Antworten 2 – mit Regeln – Abwesenheits-Assistent http://youtu.be/gV1O-j0BMhQ
Als Unterhaltung anzeigen – mehr Überblick http://youtu.be/j3XBKHg-wH8
Ordnerbereich anpassen – Navigation http://youtu.be/aZHv6J_16O0
Personenbereich – Infos aus Facebook http://youtu.be/4W7CMhG4jp0
QuickSteps – Regeln manuell ausführen http://youtu.be/BFcmWuiI3xM
Touch – Bedienung über Tablet http://youtu.be/JTpZQsfLTQw
Kontaktgruppe erstellen – Verteilerlisten http://youtu.be/2tXrKYyC248
Favoriten nutzen – Suchordner für ungelesene E-Mails http://youtu.be/JmDjplwIJjA
Office-Design von weiß auf hellgrau ändern http://youtu.be/-6_DWYtDUB4
Gruppierung und Rasterlinien ändern – Striche zwischen E-Mails http://youtu.be/FIS_qe9Me58
Bedingte Formatierung für E-Mails – besser organisieren http://youtu.be/5q22BwA7XuU
Reihenfolge von Ordnern beliebig ändern http://youtu.be/JXTjeZvJ4V8
E-Mails löschen, endgültig löschen und permanent löschen http://youtu.be/P7YtnYzfLCI
E-Mails verändern – nachträglich bearbeiten http://youtu.be/4ei8NDPvRKk
Signaturen erstellen – Fehler vermeiden http://youtu.be/BpkgGL7iNHc
E-Mails im Rich-Text-Format – winmail.dat vermeiden http://youtu.be/z4XGdnACwNw
Schriftart für E-Mails im Nur-Text-Format ändern – Courier New http://youtu.be/O7O8nQzJFk8
Schnellbausteine – Textbausteine für E-Mails http://youtu.be/pxLajTfCbsU
Postfach für gemeinsame Nutzung freigeben – Arbeiten im Team http://youtu.be/6glU679M7vU
Freigegebenes Postfach einbinden – Arbeiten im Team http://youtu.be/5UahjkaMurw
Überblick über den Kalender – Teil 01 http://youtu.be/8mzu98viJzQ
Termin erstellen – Kalender – Teil 02 http://youtu.be/T5NfTm96YJ0
Freie Termine in Planungsansicht anzeigen – Kalender – Teil 03 http://youtu.be/OKmgx6qIZFI
Feiertage importieren und entfernen – Kalender – Teil 04 http://youtu.be/UBTzUqtn5mg
Eigene Feiertage importieren – Kalender – Teil 05 http://youtu.be/XqXtUZHQ8ZU
Ganztägige Ereignisse – Urlaub eintragen – Kalender – Teil 06 http://youtu.be/TSnqJ_KVuSE
Private Termine – Kalender – Teil 07 http://youtu.be/iiaEFverRgI
Einfache Besprechungsanfragen – Kalender – Teil 08 http://youtu.be/gTz0Py9KppY
Besprechungen über die Planungsansicht – Kalender – Teil 09 http://youtu.be/M0rdFVZSLV0
Besprechungsanfragen an Externe – Kalender – Teil 10 http://youtu.be/8RwGaHqzEmY
Besprechungen absagen – richtig und korrekt – Kalender – Teil 11 http://youtu.be/LyR8480ZJWE
Einfache Terminserien – Kalender – Teil 12 http://youtu.be/zaAaJoXNOHc
Terminserien nachträglich ändern – Kalender – Teil 13 http://youtu.be/isnvuIFVQAw
Besprechung mit Raumbuchung – Kalender – Teil 14 http://youtu.be/Lvx3hU3_E6k
Kalendergruppen – Kalender – Teil 15 http://youtu.be/zjfWkIc0Rpw
optionale Teilnehmer – Besprechungen weiterleiten – Kalender – Teil 16 http://youtu.be/d6YcQgng5ck
Besprechung mit vielen Teilnehmern – Kalender – Teil 17 http://youtu.be/K5rEj_xvJ1U
E-Mails im Nur-Text-Format lesen – E-Mail-Sicherheit  http://youtu.be/W4Ik13Xwkmw
Ändern einer digital signierten E-Mail http://youtu.be/ng9Ssux-bB8
Kennzeichnung als Aufgabenelement – Nachverfolgung http://youtu.be/WizWkaLtP2A
Outlook  – E-Mail verzögert übermitteln – Exchange-Postfach http://youtu.be/bmixISq4hH8
Antwort auf E-Mail an andere Personen senden http://youtu.be/8AHuqyLoaiE
Lesebestätigung bei E-Mails – Umgang mit Lesebestätigungen http://youtu.be/rRHNOYHp0oc
Das Journal – Teil 1 http://youtu.be/ymISFharwEA
Das Journal – Funktion mit Zukunft? – Teil 2 http://youtu.be/YDoh-FOHcC4
Outlook heute und Leselayout – Mehr Überblick http://youtu.be/b2QVyYtqKrU
Verknüpfungen – Organisation von Projekten http://youtu.be/vF3LqBRZCiU
Ansichtstyp Zeitskala – Termine im Zeitstrahl – Kalender – Teil 18 http://youtu.be/BzE2l4v26Bk
E-Mail an BCC – blind carbon copy – anonymer Empfängerkreis http://youtu.be/WAx7zKjuNZk
Neue Ansichten erstellen und verwalten – E-Mail-Ordner http://youtu.be/TG-UwUNClvA
Webseiten über Ordnerbereich, Favoriten oder Verknüpfungen anzeigen http://youtu.be/qTiHqRHgxVk
Kalender per E-Mail senden – Kalender – Teil 19 http://youtu.be/eDDwTenFtWA
Karte zum Kontakt öffnen – BING Karten http://youtu.be/AuSOycpG0pU
Hyperlinks auf Dateien und Ordner versenden – auf Anlagen verzichten http://youtu.be/qGOAzU-frzU
Kontakten ein Geschlecht zuordnen – Vorbereitung Serienbrief http://youtu.be/hVNbsy8xepI
Persönliche Informationen in Kontakten – Überlegungen zum Datenschutz http://youtu.be/PqDDHQCkk1U
Erinnerungsfenster unter Windows 8 im Vordergrund? – Kalender – Teil 20 http://youtu.be/Nx3XmXnxD6g
Kalender und Termine in der Aufgabenleiste – Kalender – Teil 21 http://youtu.be/IR1XJFBaEaw
Serien-E-Mail aus Kontakten versenden – Teil 1 http://youtu.be/cBx15ECvVFk
Serien-E-Mail an kategorisierte Kontakte – mit Anrede – Teil 2 http://youtu.be/Jg3M_MXGqzQ
Serien-E-Mail über Word starten – mit Regeln – Teil 3 http://youtu.be/UQTa7v-KS20
Serien-E-Mail über freigegebene Kontakte – Teil 4 http://youtu.be/T43FlxfMLwc
Einfache Aufgaben – Überblick – Teil 1 http://youtu.be/X-zg5T7C_VU
E-Mail-Vorlage erstellen – oft-Datei http://youtu.be/Z7cauF7bUWY
individuellen Newsletter im HTML-Format als Serien-E-Mail – Teil 6 http://youtu.be/gBvdnTsb-xE
Serienaufgaben – regelmäßig wiederkehrende Aufgaben – Teil 2 http://youtu.be/J3RJ3MEMtcE
Weiteres Postfach einbinden – mehrere Postfächer nutzen – Exchange http://youtu.be/ZXOWwQE2Yl0
Aufgaben zuweisen – Aufgabe an andere delegieren – Teil 3 http://youtu.be/y9osuQ-SIcM
RSS-Feeds abonnieren – RSS mit Internet Explorer abgleichen http://youtu.be/e6KsvGnKg1c
Outlook-Datendatei – pst-Datei erstellen – Teil 1 http://youtu.be/-kICBuMTFlU
Outlook-Datendatei gemeinsam nutzen – pst im Team – Teil 2 http://youtu.be/o9i8_mOF_0Y
alte pst-Datei importieren – Datendatei aus Outlook 97-2002 – Teil 3 http://youtu.be/gnWvS-yyj0Y
Kontakte über Skype anrufen http://youtu.be/v2XHXof9Dco
Registry ändern – Skype zum Standard für Instant Messaging erklären http://youtu.be/an3C9n8qC04

Verschachtelte WENN-Funktion in Excel

Bei der verschachtelten WENN-Funktion können mehrere Prüfungen nacheinander und in Abhängigkeit von der vorherigen Prüfung durchgeführt werden.

So erfolgt im nachfolgenden Fall zunächst eine Prüfung, ob eine Person Vegetarier ist und es wird für diesen Fall ein veganes Gericht zubereitet. Für alle anderen Personen folgt dann die Frage, ob diese auch Schweinefleisch ist – und die Prüfung erfolgt nur dort. Für den Nein-Fall gibt es dann halt Hühnchen.

at31

Hier ist die Excel-Datei:

at0031 Verschachtelte WENN-Funktion 5

Bis zu 64 Unterschiedliche Antworten könnten so in Reihe durch Verschachtelung geprüft werden. Spätestens dort werden die verschachtelten zu lang, unhandlich und sind kaum noch zu überblicken. Immerhin müssen dann die ganzen Klammern geschlossen werden.

at31WENN

Hier geht es zum Video:

Videolink: https://www.youtube.com/watch?v=O2XBXNTY5cc

In Fall 6 wird dann ebenfalls eine verschachtelte WENN-Funktion demonstriert. Dieses mal an Wertbereichen.

Beste Grüße
Andreas

Gegliederte Liste umwandeln – Übung 2

Bei den at Excel Experts gibt es fünf Seminartage binnen fünf Monaten und eine Menge unterschiedlichen Stoff zu lernen. Zwischen den Trainings gilt es, das erworbene Wissen in der Praxis zu erproben, Fragen zu sammeln und ein wenig zu üben.

Hier die zweite Übungsaufgabe für die at Excel Experts. Es gilt, die gelieferten Listen aus den ersten beiden Tabellen auswertbar zu machen, zum Beispiel für Pivot. Dort stört natürlich die vorgegebene gegliederte Struktur. Und dann auch noch verbundene Zellen 🙁

02 Gegliederte Aufstellung K herunterladen

Hier geht es zum Video:

Videolink: https://www.youtube.com/watch?v=BFR7DDuW5dk

Die Lösung(en) folg(t/en) in den nächsten Tagen.

Viel Spaß und eine erfolgreiche Woche
Andreas

at Excel Experts – Lösung 4 zu Übung 1

Abschließend gibt es hier in Lösungsrunde 4 zur ersten Übungsaufgabe vom 8.9.2017 noch einen dicken Patzer zu berichten. Zusätzlich einige Optimierungsvorschläge.

Das Video startet bei folgender Datei:

01 Ausgangsdatei K Lösung 4 Ausgang herunterladen

Ich bedanke mich schon nach wenigen Tagen für das starke Interesse. In vielen Nachrichten wurde mir auch mitgeteilt, dass die Qualität der eigenen Daten häufig nicht überdacht wird und Phänomene wie der Tabellenname vor dem Zellbezug oder leere Spalten nicht so selten vorkommen.

Ähnliche Dateien wie diese hier habe ich auch schon in Bewerbungstests gesehen. Schließlich müssen wir dort laut Stellenanzeige ja alle „sehr gute bis perfekte Office-Kenntnisse insb. Excel“ besitzen. (Was bedeutet das überhaupt? Sollte man mal eine eigene Reihe zu machen.)

Hier geht es zum YouTube-Video

Videolink: https://www.youtube.com/watch?v=p-ykPwVsrOw

Folgende Punkte wurden behoben bzw. angepasst:

  1. Die Spaltenwerte von tatsächliche Fertigstellung liegen zum Teil vor dem Startdatum. Vielleicht wurden die Daten aus anderen Systemen mit anderer Zeitzone übernommen und sind so um einen Tag verrutscht. Hier lässt sich nur außerhalb von Excel die Datenherkunft prüfen. Eventuell sind ja alle Daten um einen Tag verschoben.
  2. Das Tabellenformat wurde auf den Datenbereich angewandt. Das hätte ich schon von Anfang an machen können, wollte aber erst noch auf ein neues Dateiformat warten.
  3. Die Datei wurde im XLSX- und XLSB-Format gespeichert. Die Datei wird kleiner und kann die neuen Features von Excel verwenden. Das Dateiformat kann verändert werden, wenn es keine nachfolgenden (noch unbekannten) Dateiverknüpfungen gibt. Auch muss überdacht werden, ob die Datei von irgendwelchen Folgesystemen benötigt wird.
  4. Die Dateieigenschaften wurden überprüft. Ersteller, Manager und Firma zeigten eventuell noch Daten an, die man so nicht weitergeben möchte.
  5. Der Druckbereich wurde angepasst. Die Seitenzahl für den Druck konnte von 396 auf unter 150 Seiten reduziert werden.
  6. Für den Druck wurde die Wiederholungszeile eingesetzt, so dass die Spaltentitel auf jeder Seite oben wiederholt werden.
  7. Die Tabelle wurde etwas nach unten verschoben, um oberhalb ein wenig Platz für eine Überschrift und Infos zur Datenherkunft zu machen. Auch können oben gut die Summen der Spalten angezeigt werden. Somit hat man immer alles im Blick, ohne extra nach unten scrollen zu müssen.
  8. Ein Tabellenblatt mit Hinweisen / Impressum wurde angelegt. Dort können Infos zur Datenherkunft, Aktualisierungsintervall, letzte Aktualisierung, Ersteller, Zweck der Datei etc. untergebracht werden. Gebt den Tabellen diese Infos. Kostet zwar am Anfang Zeit, spart aber später eigene und fremde Nerven. Wie soll man das Meer an Excel-Dateien überblicken, wenn man frisch einen Bereich übernimmt und die Dateien nicht beschrieben sind?

Sollte etwas nicht erwähnt worden sein, folgen die Infos hier oder unterhalb der Videos.

DANKE fürs Mitmachen!
Wünsche euch eine erfolgreiche Woche und bis zur nächsten Übung.

Meine aktuelle at Excel Experts-Gruppe möchte ich bitten, mal die eigenen Daten anzuschauen 😉

Sonnige Grüße
Andreas

at Excel Experts – Lösung 3 zu Übung 1

Ich hoffe, die bislang gefundenen Pannen / Fehler regen euch an, mal in die eigenen Daten hinein zu schauen.

Hier im dritten Lösungsteil geht es um doppelte Datenbestände bzw. fehlerhafte Einträge. Wer das Video direkt am Beispiel nachvollziehen möchte, kann die modifizierte Übungsdatei herunterladen:

01 Ausgangsdatei K Lösung 3 Ausgang

Hier geht’s zum Video:

Videolink: https://youtu.be/YKJry8nmowc

Die folgenden Fehler wurden behoben:

  1. Doppelte ID in Spalte A entfernt und korrigiert. ID 5204 ist doppelt, 5217 fehlt. Zudem waren die IDs doch noch als Text formatiert.
  2. Die Standorte sind in mehreren Schreibweisen vorhanden. Einige werden bereits durch den Filter dargestellt. Andere bekomme ich nur über die Pivot-Auswertung ausgewiesen.
  3. In Spalte H (geplante Fertigstellung) gibt es den 29.02.2014. Wohl nur dort 😉
  4. In Spalte I fehlt bei der tatsächlichen Fertigstellung bei den IDs 10001 bis 10003 das Datum. Die Aufträge gelten als erledigt, also sollte es ein Datum geben.

Es folgt noch ein letzter Blogeintrag… und dann kann es auch schon zur zweiten Übung gehen.

Sonnige Grüße
Andreas

at Excel Experts – Lösung 2 zu Übung 1

Weiter geht es bei der Fehlerbehebung in Übungsdatei 1 vom 8. September 2017. Mag die Tabelle auf den ersten Blick stimmig aussehen. Es befinden sich massiv Fehler und Pannen in der Liste. Ein Teil davon wird im zweiten Lösungsvideo beschrieben.

Die bereits angepasste Übungsdatei:

01 Ausgangsdatei K Lösung 2 Ausgang herunterladen

Hier geht es zum Video

Videolink: https://youtu.be/2nkUNwfKDXM

Welche Fehler werden hier behoben?

  1. Die verbundenen Zellen werden identifiziert und aufgelöst.
  2. Mit Leertasten manipulierte Zellen werden gefunden und korrigiert.
  3. Leere Zellen sind durch den Kopiervorgang gar nicht so leer und werden nicht mit STRG + Pfeiltaste angesprungen. Diese werden über den Filter entfernt und wirklich geleert.
  4. Die ID ist nicht nummerisch, sondern wird als Text interpretiert. Die IDs werden ins Zahlen umgewandelt.
  5. Bei der Sortierung machen die IDs Stress, aber auch die Nettoberechnung, da hier der Tabellenblattname mit in der Formel steht und für eine mittelschwere Katastrophe sorgt.
  6. In der Spalte Nettowert gibt es abweichende Zahlenformate (mit Dollarzeichen).

Und? Noch mehr gefunden? Da stecken noch ein paar „Sauereien“ in den Daten.
Viel Spaß beim Suchen

Andreas