Kategorie: Office

Excel-Pivot – Folgen 15 bis 17

Insgesamt wurden meine Excel Pivot-Tutorials (ohne PowerPivot) über 920.000 mal aufgerufen. Aus meiner Tätigkeit als Berater und Trainer weiß ich, dass gerade die Pivot-Auswertungen von größtem Nutzen für sämtliche Listenauswertungen im betrieblichen Controlling, Projekt- und Personalcontrolling und vielen anderen Aufgabengebieten sind.

In den Ergebnissen meiner im Dezember 2015 gestarteten Umfrage https://youtu.be/NQYhO9BOyQY  wurde auch verstärkt der Wunsch geäußert, noch mehr Themen aus den Bereichen Excel für das Controlling aufzunehmen, um Controllership und Rechnungswesen im Unternehmen zu unterstützen. Oftmals waren es nur kleine technische Tipps  und Denkanstöße, die bei den Zusehern zu neuen Auswertungen und Berichten geführt haben.

Noch kurz zwei Antworten auf die Frage, ob die Videos schon weiterhelfen konnten:

Ich habe sehr viel gelernt und konnte die Lohnberechnung in unserem kleinen Familienbetrieb besser organisieren.

 

Die Videos helfen oft bei knackigen Problemen der Auswertung und geben Anregungen zu weiteren Möglichkeiten

Größter Beliebtheit mit mehr als 550.000 Videoaufrufen erfreut sich immer noch eine Pivot-Reihe aus dem Jahr 2012. Die Videos basieren auf zwei Übungsdateien, an denen man die demonstrierten Inhalte nachvollziehen kann.

Die ersten Tutorials „Pivot 1-14“ sind inklusive Übungsdatei unter dem Link http://thehosblog.com/2013/10/12/pivot_tutorial/ zu finden.

Die weiteren Titel nutzen die Datei: Pivot_Tutorial_15-

15 – Berechnetes Element: http://youtu.be/1SFSjpwSOQc
15a – Berechnetes Element Ergänzung: http://youtu.be/5yaYak5J8Bo
16 – Berechnete Felder: http://youtu.be/KXGdA0I6bKc
16a – Problem mit berechneten Feldern: http://youtu.be/MpU3up0LqJ4
16b – Berechnete Felder und Gesamtergebnis: http://youtu.be/totLHKwOeTw
16c – Problem mit berechneten Feldern3: http://youtu.be/Sedr8xZijJY
17 – PIVOTDATENZUORDNEN: http://youtu.be/21wrdnVyLxM

Danke nochmals für die vielen Unterstützer, die den YouTube-Kanal und diesen Blog im vergangen Jahr finanziell und mit Tipps unterstützt haben.

Beste Grüße und ein spannendes Jahr 2016

Andreas Thehos

 

 

 

 

Excel Online – Vorlagen nutzen

Über Excel Online und die Webseite http://templates.office.com stehen Vorlagen zur Verwendung bereit. Diese Vorlagen können direkt genutzt oder modifiziert werden. Auch haben diese Vorlagen so manch verstecktes Gimmick, bei dem man noch etwas lernen kann. Diese neue Reihe beschäftigt sich gezielt mit den Möglichkeiten der von Microsoft bereitgestellten Vorlagen.

Eine Möglichkeit über OneDrive ist der Klick auf die Online-Dienste, die Schaltfläche oben links bietet Excel Online an. Entweder startet man mit einer leeren Arbeitsmappe, oder wählt eine der Vorlagen aus.

729_thehos_1

Sollte Excel Online bereits im Browser geöffnet sein, einfach auf Datei und dann auf Neu klicken. Es steht eine reduzierte Auswahl an Vorlagen zur Verfügung.

Ein kostenfreies OneDrive-Konto könnt ihr auch über diesen Link erhalten: https://onedrive.live.com/?invref=26b8bc27fd8ccbb4&invsrc=90

Hier geht es zum Video:

Link zum Video: https://youtu.be/YqgztL-d2Qw

Mit XODER Binärzahlen addieren

Mit der Hilfe der Funktionen XODER, SUMMENPRODUKT, TEIL und DEZINBIN kann ich 2 Binärzahlen (hier zweimal 8 Bit) addieren.

728_Thehos_01

Die folgenden Formeln habe ich dafür eingesetzt. Die Funktionen mit geschweiften Klammern (Matrixformeln) müssen mit STRG + SHIFT + ENTER bestätigt werden, also die Klammern nicht manuell eingeben.

728_thehos_02

Hier ist die Übungsdatei: Excel 728 XODER 2

Käufer des USB-Sticks finden die Datei im Ordner E728.

Hier geht es zum YouTube-Video:

Link zum Video: https://youtu.be/lQQHrApYbTo

XODER – Excel-Funktion

Seit Excel 2013 steht die Logik-Funktion XODER (engl. XOR) zur Prüfung von bis zu 254 Prüfungen zur Verfügung. Prüfungen mit den logischen Operatoren =, >, <, >=, <= und <> können pro Prüfung den Wert FALSCH oder WAHR liefern. Die Funktion XODER prüft, ob bei einer oder mehreren Prüfungen die Anzahl der positiven Prüfungsergebnisse ungerade oder gerade ist. Bei gerader Anzahl an WAHRen Einzelprüfungen, resultiert der Wert FALSCH, Bei einer ungeraden Anzahl an WAHRen Einzelprüfungen resultiert das Ergebnis WAHR.

Syntax: =XODER(Prüfung1;Prüfung2;.... ;Prüfung254)

Mindestens eine Prüfung der Form A1=2 ist erforderlich.

Es können jedoch auch Arrays geprüft werden. Anstelle von 3 Einzelprüfungen der Form =XODER(A1=2;A2=2;A3=2) kann auch die Funktion =XODER(A1:A3=2) genutzt werden und als Matrixfunktion mit der Tastenkombination STRG + SHIFT + ENTER bestätigt werden.

Die Funktion XODER kann gut genutzt werden, um zu prüfen, ob ausschließlich eine von zwei Bedingungen zutrifft.

Eine Ergänzung von Sebastian in den Kommentaren zum Video:

Hier noch eine kleine Lösung die eine XOR-Funktionalität hinsichtlich von der Anzahl von Suchtreffern erstellt gilt auch für ältere Versionen (erstellt in 2010):
=NICHT(REST(SUMMENPRODUKT(–(Matrix=Suchkriterium));2)=0)
Wenn man ein normales XOR haben möchte gilt Suchkriterium = WAHR und eine 2 Zellen große Matrix

Und noch eine etwas nicht so elegante Lösung:
=NICHT(ODER(UND(Wert1;Wert2);UND(NICHT(Wert1);NICHT(Wert2))))
Computer-Prozessorstruktur (Nur mit NICHT und UND, max 2 input Werte zulässig (glaub ich, nicht getestet)):
=NICHT(UND(NICHT(UND(Wert1;NICHT(UND(Wert1;Wert2))));NICHT(UND(NICHT(UND(Wert1;Wert2));Wert2))))

Die Datei aus dem Video: Excel 727 XODER

Hier ist Excel-Video # 727

Link zum Video: https://youtu.be/ov_qGZdqsBw

Differenzen im Diagramm ausweisen

In einer kleinen dreiteiligen Reihe habe ich ein gestapeltes Säulendiagramm dafür genutzt, die Differenzen im Diagramm zwischen vorletzter und letzter Säule anzuzeigen.

Dabei sind die Werte entweder positiv angestiegen und werden auch mit einem Pluszeichen ausgewiesen, oder die negative Abweichung wird mit einem Minuszeichen angezeigt. Da die Säulenhöhe meines gelben Hilfsbereichs positiv ist, ist die negative Ausweisung auch nicht selbstverständlich.

(Hinweis: Aktuelle Ankündigen nun wieder über http://twitter.com/athehos )

Die gelben horizontalen Hilfsstriche wurden mit Hilfe von Säulensegmenten gelöst – beschrieben mit einer manuellen Lösung in Video 1., die Berechnung der Segmente per Formel in Video 2 und in Video 3 wurden die Labels ausgerechnet.

Zudem wird in Video 3 eine Lösung gezeigt, wie man die Differenz eleganter ausweisen kann. Ab Excel 2013 gibt es die Möglichkeit, gezielt eine weitere Datenreihe für Beschriftungen zu nutzen, für vorherige Excel-Versionen gibt es auch das kostenlose Excel-Add-In XY-Chart-Labeler.

Hier ist die Übungsdatei: Excel 722 Diagramm letzte Änderung

Video 1 (Excel # 722)

Link zum Video: https://youtu.be/qvsqcYvtqXc

Video 2 (Excel # 724)

Link zum Video: https://youtu.be/yGkVi_uQ6kA

Video 3 (Excel # 726)

Link zum Video: https://youtu.be/MqSMIw-39Hw

Und als Ergänzung der XY-Chart-Labeler

Link zum Video: https://youtu.be/rNKzwWTkN-c

Äpfel, Zirkelbezug, Iteration & Co.

Anbei eine kleine Bildstrecke zum Video Excel – Äpfel, Zirkelbezug, Iteration & Co.  Unten ist auch die Datei als Downloadlink verfügbar.

Man beachte den Zirkelbezug in Zelle O2! Die Formel ist unten im Bild aufgeführt.

Die kleine Formel aus D3 zeigt, dass nur Zufallswerte gezogen werden, wenn die Zelle J14 auf 0 steht.

a4.jpg

Das ist die Formel in den Körben, stellvertretend für Zelle N14 mit der entsprechenden Formatierung für grüne Äpfel.

Damit der Zirkelbezug vernünftig berechnet werden kann, habe ich unter Datei – Optionen – Formeln die iterativen Berechnungen aktiviert und die Iterationszahl auf 1 gesenkt.

a7.jpg

Hier gibt es die Excel-Datei zum Download:
Excel 723 Apfelernte

Hier geht es zum Video:

Videolink: https://youtu.be/PK_1E3aX6iM

Erste Umfrageergebnisse

Hallo und Willkommen in 2016!

Ich hoffe, Ihr seid alle gut ins neue Jahr gerutscht. Da anzunehmen ist, dass es auch in 2016 keine ernsthafte Alternative zu Excel geben wird, hoffe ich, dass die YouTube-Videos weiterhin eine Unterstützung in Beruf und Alltag werden.

Vor zwei Wochen habe ich eine kleine OneDrive-Umfrage gestartet. Unter dem Link http://1drv.ms/1T4lZQd ist sie weiterhin erreichbar. Doch nach 2 Wochen haben sich schon 136 Personen damit befasst und Wünsche, Erfahrungen und Meinungen geäußert. Danke!

In meiner 2. Frage wollte ich gerne wissen, welche Anwendung am meisten interessiert. Knapp 70 Prozent meiner Videos (724 Titel) behandeln Excel, knapp 96 Prozent der antwortenden Personen sind hauptsächlich an Excel interessiert.  Danach folgt PowerPoint… Irgendwie ein klares Signal…

39 Prozent nutzen die Videos für berufliche Zwecke, 35 Prozent für alles Mögliche und knapp 19 Prozent für private Zwecke. 7 Prozent für Schule, Ausbildung und Uni.
In 2015 lag der Anteil der Zuschauer bis einschließlich 24 Jahren in Deutschland bei 15 Prozent, in der Schweiz bei 20 Prozent und in Österreich bei 24 Prozent. Meine Hauptzielgruppe scheinen die 25 bis 34 jährigen YouTuber zu sein, in Deutschland allein mit 42 Prozent Anteil (laut YouTube Analytics). Verbunden mit dem Feedback aus der Umfrage sind hier vor allem die Herausforderungen zu Beginn eines Jobs, neue Aufgabenstellungen im Job und das spielerische Interesse an Excel Grund, häufiger in die Titel bei YouTube zu schauen. Die Altersgruppe über 45 macht nur knapp 20 Prozent aus – aus dieser Gruppe kommen die meisten Unterstützer des Kanals.
Der Unterschied zwischen Männlein und Weiblein ist (nicht nur anatomisch) interessant. Knapp 18 Prozent der deutschen Besucher der Videos sind weiblich. Davon knapp 73 Prozent jünger als 35. Bei den Männern sind knapp 55 Prozent jünger als 35.
Ich mag daraus schließen, dass a) Excel für private und berufliche Zwecke ab 35 Jahren eher von Männern genutzt wird oder diese eventuell beruflich damit häufiger zu tun haben – da mag auch der mögliche Ausstieg aus dem Beruf wegen der Familie mit zu tun haben. b) dass meine Themen und die Art der Präsentation für Zuschauerinnen weniger spannend sind.

Da ich bei einigen Unternehmen auch die neuen Auszubildenden in Empfang nehmen und ein wenig mit Excel quälen darf, weiß ich, dass Excel bis zum Einstieg in den Beruf häufig zu selten benutzt wurde. Mein ältestes Kind ist an einer Microsoft Associate Showcase Schule, in der bereits ab der fünften Klasse an Tablets Excel zusammen im Matheunterricht gelehrt wird. Ich bin von der Begeisterung und schnellen Umsetzung der jungen Generation fasziniert und könnte mir gut vorstellen, hier ein paar mehr weniger abstrakte Themen für den Nachwuchs anzubieten.

71 Prozent der Zuschauer wünschen sich aufeinander aufbauende Videos, die mit der Zeit schwieriger werden, 26 Prozent Titel mit komplexen Themen, bei denen man auch mal länger grübeln muss. Da bleiben nur ein paar Prozent für die Personen, die grundlegende Themen sehen möchten. Ich habe aber auch den Eindruck, dass die Umfrage eher die Personen erreicht hat, die schon länger den Kanal begleiten und entsprechend weniger Grundlagen benötigen.

Das positivste Feedback hatte ich auch immer zu den mehrteiligen Reihen erhalten, welche eine große Spannweite über alle möglichen Excel-Themen abdeckten. Mal schauen, welche Themen mir hier in den Sinn kommen. Hier wurden vor allen Dingen mehr VBA und mehr PowerPivot gewünscht.

49 Prozent der Antwortenden ist es egal, ob vor oder in den Videos Werbung gezeigt wird. 35 Prozent ist es wichtig, dass der Kanal frei von Werbung ist, 16 Prozent ist es unwichtig.
Interessant ist hier ein kleiner Zusammenhang, dass dort Werbung am meisten stören würde, wo die Videos aus beruflichen Gründen genutzt werden.

Und als Durchschnittsnote hat der Kanal eine 1,37 erhalten.

Die großen Textantworten muss ich noch inhaltlich auswerten. Es besteht aber ein großes Interesse an Treffen an Orten, die ein wenig weiter weg von Bochum sind. Wien wäre da mein Favorit und auch eine gute Kandidatin für einen Excel-Stammtisch, in Berlin und Hamburg bin ich gelegentlich.

Merci für Euer Interesse
und bis zum nächsten Blog-Beitrag

Andreas

 

Umfrage zur Verbesserung des YouTube-Kanals

Hallo,

unter dem Link http://1drv.ms/1T4lZQd ist aktuell eine kleine und anonyme Umfrage zu erreichen.

Ich würde den YouTube-Kanal gerne etwas mehr an den Bedürfnissen der Besucher ausrichten und schauen, welche Erfahrungen Ihr mit dem Kanal gewonnen habt. Ich würde mich freuen, wenn Ihr Zeit, Lust und Interesse habt, Eure Erfahrungen und Wünsche beizusteuern.

Sonnige Grüße und DANKE

Andreas