Kategorie: Excel

XVERWEIS statt SVERWEIS, INDEX und VERGLEICH !!!

Das ist der HAMMER!

Endlich ist ein würdiger Nachfolger für die Funktionen SVERWEIS bzw. die Funktionkombination INDEX und VERGLEICH veröffentlicht worden.

Mit dem Funktionsupdate der Office 365 Insider Fast-Version stehen die neuen Funktionen XVERWEIS und XVERGLEICH erst einmal einer sehr kleinen Gruppe von Office-Testnutzern zur Verfügung. In welchem Zeitraum die Funktionen allgemein zugänglich gemacht werden, ist noch nicht bekannt.

Die Funktion XVERWEIS benötigt explizit keine Suchspalte, die sich vorne links in der Datenmatrix befindet. Dies war immer eine der Schwachstellen des SVERWEISES, so dass die Datenmatrix immer erst umgebaut werden musste.

Syntax:

=XVERWEIS(Suchkriterium;Suchmatrix;Rückgabematrix;[Vergleichsmodus];[Rückgabemodus])

Suchkriterium: Ist derjenige Wert oder ein nah beim Suchkriterium gelegener Wert, der in der Suchmatrix aufgespürt bzw. identifiziert werden soll. An dieser Stelle kann auch auf einen Datenbereich mit Suchwerten verwiesen werden – es wird dann statt eines Ergebnisses pro Suchkriterium ein Wert in einer Matrix erzeugt (Stichwort dynamische Arrays).

Im Beispiel soll die Rechnungsnummer in Zelle H4 gefunden werden.

XVERWEIS_01

Suchmatrix: Ist die Spalte oder Zeile von Werten, in der das Suchkriterium gefunden werden soll. In der Suchmatrix kann sich der gesuchte Wert auch mehrfach befinden. Die Suchmatrix kann unsortiert, auf- und absteigend sortiert sein. Die Suchmatrix kann sich irgendwo im Datenbereich befinden.

Gesucht wird hier in der Tabelle tabRechnungen in Spalte RECHNUNG.

XVERWEIS_02

Rückgabematrix: Ist ein Bereich aus den Daten, den man bei gefundenem Suchkriterium ausgegeben haben möchte. Dank der dynamischen Arrays kann die Rückgabematrix auch aus mehreren Spalten oder Zeilen bestehen.

Wenn die passende Rechnung gefunden wurde, soll daneben das Zielland aus der Spalte ZIEL wiedergegben werden.

XVERWEIS_03

Vergleichsmodus: Soll der Wert des Suchkriteriums exakt gefunden werden? Oder eventuell ein Wert der etwas größer oder kleiner ist? Oder soll auch mit Platzhaltern wie Sternchen *, Tilde ~ und Fragezeichen ? gesucht werden? Dieses Feld ist optional zu füllen. Standardmäßig wird die exakte Suche gewählt, sollte der Vergleichsmodus frei bleiben.  Als Eingabe wird einer der Werte 0, -1, 1 oder 2 gewählt.

Ich wähle die Zahl 0 für den exakten Treffer nach der Rechnungsnummer und die Zahl eins beim Rückgabemodus für eine komplette Suche in den Daten, die nicht sortiert sein müssen.

 

 

Rückgabemodus: Soll die Liste von Oben nach unten oder unten nach oben durchsucht werden? Liegt eine sortierte Liste vor? Oder ist die Liste unsortiert. Mit 1;-1;2 oder -2 kann man vorab seine Wahl treffen. Wobei ein Sortieren der Liste die Suche schneller machen würde, ein versehentliches Umsortieren aber die Suche eventuell fehlerbehaftet macht. Auch dieser Wert ist optional, per Standard wird oben nach unten durchsucht.

Am besten schaust du ins Video, um dir die Funktionsweise von XVERWEIS anzuschauen.

Ich werde auch am nächsten Excel-Stammtisch in Essen am 18. September 2019 mehr zu dieser neuen Funktion berichten.
Es bleibt aber leider erst einmal abzuwarten, wie groß der Kreis der Personen sein wird, die diese Funktion nutzen können. Bitte also immer noch die alten Lösungen in der Hinterhalt haben 😦 Zumindest stehen mir XVERWEIS und XVERGLEICH auch schon auf meinem Mac zur Verfügung.

Mehr zum Funktionsumfang und den Anwendungsmöglichkeiten erfährst Du hier:

 

Videolink: https://youtu.be/FyDB55xzBUQ

Ich freue mich über Deine Kommentare unterhalb des Videos. Was hälst Du von dieser Funktion? Wäre Dir diese Funktion so wichtig, um auf Office 365 umzusteigen?

Beste Grüße

Andreas

P.S.: Merci an die Kollegen, die meine Amazon-Listen entdeckt haben 😉

https://www.amazon.de/hz/wishlist/ls/VT98VIVR6CX8?&sort=default

https://www.amazon.de/hz/wishlist/ls/2KKWCBR7ZZ96W/ref=nav_wishlist_lists_1?_encoding=UTF8&type=wishlist

Das Ende des 128 GB-Sticks naht & mehr

 

Ahoi zusammen!

Der Excel-Stick

Gute Nachrichten: Mittlerweile sind so viele Daten auf meinem Excel-Stick, dass ich in Kürze keine 128 GB-Sticks mehr nehmen kann, sondern auf 256 GB-Sticks ausweichen muss.

VollesLaufwerk

Der Stick umfasst ja nicht nur die meisten der 1000 Excel-Videos und davon die meisten mit den passenden Excel-Dateien, welche ich bislang bei YouTube auf meinen Kanälen veröffentlicht habe. Es sind auch viele weitere Titel zu Outlook, Project, Power BI, Windows usw.

Statt jetzt also die Videos in ein kleineres Format zu packen, konzentriere ich mich weiter auf die Videoerstellung.

Der Stickpreis wird entsprechend der größeren Kapazität und des erweiterten Inhalts angepasst. Die letzten neun 128-GB-Sticks und fünf 2×64 GB-Sticks können noch zu EUR 129 inkl. 19% Mehrwertsteuer erworben werden. Nach Abverkauf der alten Version bleibt der Preis dauerhaft auf EUR 144 inkl. 19% Umsatzsteuer für die 256 GB-Variante.

Excel-Video 1017 mit bedingter Formatierung – Ampelfarben

By the way… es ist ein neues Video online. Der erste Teil ist ja sehr gut angekommen 🙂

Videolink: https://youtu.be/EN5Q-K6kpKM 

Hier gibt es für Dich die Datei zum Video mit den farbig formatierten Fortschrittsanzeigen zum Download: E1017 Progress Chart – Ringdiagramm farbig

Excel-Stammtisch Essen

Der nächste Excel-Stammtisch in Essen findet am 18. September 2019 ab 19 Uhr im Unperfekthaus statt. Danke nochmal an Lothar und Johannes für die Beiträge. Das Feedback war grandios!

Wir haben nun auch eine Meetup-Gruppe zum Stammtisch gegründet und kündigen dort unsere Termine an.

https://www.meetup.com/de-DE/Excel-Stammtisch-NRW/

Es wäre großartig, wenn Du Lust und Zeit hast – wir sind immer knapp 12-20 Personen. In Münster auch schon mal 40! Gerne kannst Du auch ohne Verpflichtungen der Meetup-Gruppe beitreten und wirst somit informiert, wenn wir anderswo einen Stammtisch durchführen.

Dir einen schönen Rest-August
Sonnige Grüße und tausend Dank für den netten Support (siehe Blog unterstützen oben)

Andreas

Chemtrail Chart statt Häufigkeitsverteilung

Hey!

Erst einmal vielen Dank für den Support durch Artur, Fabian und Peter. Merci!

Häufigkeitsverteilungen können ja soooo langweilig sein. Natürlich stellen sie zum Beispiel bei einem Histogramm über die Klassenbreiten und Höhen der Säulen (also eigentlich über die Fläche) korrekt die Häufigkeit dar, in der die Werte einer Klasse auftreten.

 

So wäre das korrekt – so habe ich es in meinem Studium der Angewandten Sozialforschung gelernt.

Ich mag aber Alternativen – und die Bastelei in Excel. Darum ein kurzer Ausflug in eine Alternative Darstellung, in der jeder Einzelwert in einer „Punktwolke“ dargestellt wird.

Die Punkte sind transparent gestaltet, so dass die Farben bei Überlagerung kräftiger werden. Bitte nicht ungeprüft in wissenschaftlichen Arbeiten verwenden. Aber vielleicht fallen euch ja noch Anwendungsmöglichkeiten ein.

Hier die XLSX-Datei zum Download: E1005 Verteilung alternativ

Link zum Video: https://youtu.be/jiGgz6xLn-0

Freue mich über Kommentare unterhalb des Videos und einen Besuch bei https://twitter.com/athehos

Beste Grüße

Andreas

Dankeschön! Mauspads für Unterstützter

Danke an euch alle!

Danke für das großartige Feedback zu meinem YouTube-Kanal nach jetzt über 1.000 Excel-Videos und fast 400 anderer Office-Tutorials. Danke für die vielen Anregungen und Danke auf für einen Blick auf https://thehosblog.com/unterstutzung/

Ich investiere sehr viel Zeit, Know How und Geld in meine Arbeit und freue mich über jeden Form eines Dankeschöns per E-Mail, per Kommentar unterhalb eines Videos und natürlich auch über einen finanzielle Würdigung. Am meisten würde ich mich über eine Weiterempfehlung des Kanals an weitere Personen freuen – davon bekomme ich ja aber direkt nichts mit 😉

Ich werde immer wieder gefragt, ob ich nicht meine Excel-Mauspads einzeln verkaufen mag. Ein Mauspad liegt aktuell als Beigabe meinen Excel-Sticks bei. Ich möchte mich ab jetzt mit einem Mauspad bei denjenigen in Deutschland ansässigen Spendern bedanken, die meine Arbeit mit einem Betrag ab 10 EUR aufwärts per „Spende“ unterstützen möchten. Vergesst also bitte nicht eine kurze E-Mail mit euren Kontaktdaten – ausschließlich Versand nach Deutschland, da allein der Versand in Länder außerhalb der EU schon knapp 9 Euro und in andere EU-Länder knapp 5 Euro kostet – zusätzlich noch der Gang zur Post bzw. zur Packstation… Die seit 1. Januar 2019 gültigen Versandregeln sind echt übel 😦

Excel-Stammtisch in Essen am 21. August 2019

Der nächste Termin für einen Excel-Stammtisch im Unperfekthaus in Essen steht.
Wieder einmal ab 19 Uhr im UPH. Schaut doch mal für weitere Informationen oben links auf den Schalter für weitere Informationen zum Excel-Stammtisch.

Ich bin wieder gespannt auf unsere treuen Stammgäste und viele neue Gesichter.

Ansonsten freue ich mich jetzt erst einmal auf ein paar Tage Wien und Frankfurt am Main.

Sonnige Grüße
Andreas

 

 

 

 

547 Zustimmungswerte optisch auswerten

Eine fiktive Menge von 547 Personen wurde nach einem Zustimmungswert von 0% (Ablehnung) bis 100% (volle Zustimmung) gebeten.

Die Daten können natürlich nun in einem klassischen Säulendiagramm nach Gruppen ausgewertet werden. Wobei die Säulen des unteren Diagramms so zu lesen wären: „Anzahl der Zustimmungswerte größer der vorherigen Klasse bis zur  aktuell angezeigten Klasse“.

VerteilungProzent

Die vorletzte Säule wäre also der Wert von größer 93,33 Prozent Zustimmung bis einschließlich 96,67 Prozent Zustimmung.

Soweit so gut… das ist schnell umgesetzt.
Doch dann werde ich auf einen Diagrammtyp angesprochen, der in ähnlicher Form bei Online-Umfragen genutzt wird. Dort ist auf einer Art Tachoscheibe die Zustimmung im Halbkreis angeordnet.

Im unteren Diagramm wurden Werte unterhalb von 33,33 Prozent Zustimmung als Ablehnung interpretiert und Werte größer 66,66 Prozent Zustimmung als Zustimmung.

Die Punkte zeigen an, in welchem Bereich der Zustimmungswerte sich die Antworten „knubbeln“ (sehr wissenschaftlich – ich weiß).  Es ist vielleicht nicht die optimale wissenschaftliche Darstellung, gibt aber in meinen Augen viel mehr her. Und das liegt nicht nur an dem schicken Foto 😉

 

VerteilungChart

Als Basis hat mir Video Excel 948 https://www.youtube.com/watch?v=LS_3IcwBovU gedient. Dazu noch einen Halbkreis formatieren und einmal zum Fotografen… fertig ist das kleine Kunstwerk.

Wer sich die XLSX-Datei selbst einmal anschauen möchte, kann sich diese hier E1004 Zustimmung herunterladen.

Ach… ist das alles spannend! Und das bei DEM Wetter!
Sonnige Grüße

Andreas

P.S.: Merci für kleinen Aufmerksamkeiten zur Unterstützung des Blogs und des Bloggers 😉
Danke an Jürgen.
Danke an Martin.

P.P.S.: Nächster Excel-Stammtisch in Essen am 21. August 2019 ab 19 Uhr im Unperfekthaus

 

Juli 2019 – Excel-Stammtisch & mehr

1989

habe ich noch Unter den Linden den Vorbeimarsch der Arbeiter und Bauern „bewundern“ können. Fahnen schwenkend probten FDJ, FDGB und Volkspolizei den 40. Jahrestag der DDR. Das ich noch einmal Honecker und Krenz sehen konnte, wie sie am alten Zeughaus einen Kranz niederlegten und zurück in ihre Staatskarosse stiegen, war für mich als Westbesucher mit 25 Mark Zwangsumtausch natürlich der Hammer. Vorher stand ich noch auf West-Seite am Brandenburger Tor und las die vielen Schilder mit der Aufschrift „Achtung! Sie verlassen jetzt West-Berlin!“ (gedruckt) und Zusatz „Wie denn?“ (geschmiert) Das man heute an gleicher Stelle mir Euro bezahlt, war für mich damals nicht vorstellbar.

Nun, knapp 30 Jahre später wandele ich wieder Unter den Linden und kehre zum x-ten Male in der Microsoft digital eatery ein. Und HEY! Wir könnten rein theoretisch nach Ladenschluss die Räumlichkeiten für einen Excel-/Power BI-Austausch nutzen – nennen wir es Meetup und nicht Excel-Stammtisch. Kostet allerdings nichts… äh… da war jetzt kein Haken. Der kommt jetzt… Wir sollten schon eine Gruppe von 25+ Personen sein. Das wäre doch etwas, oder?

Interesse? Dann achtet bitte auf den Diskussionsbeitrag vom 17. Juli 2019 bei YouTube https://www.youtube.com/user/AThehos/community Ich bleibe dran!

Es wird sicher auch jemanden geben, der das federführend vor Ort mit organisieren möchte. Ich bin halt nur 4-5x pro Jahr in Berlin und bestimmt auch nicht immer dann, wenn gerade ein Stammtisch geplant ist. Interesse?

 

Excel-Stammtisch in Essen am 24.07.2019 !

Den Stammtisch am 10. Juli musste ich leider absagen. Nun habe ich aber noch einen Termin für den 24. Juli für Essen bekommen! Wieder ab 19 Uhr im Unperfekthaus. https://www.unperfekthaus.de/projekte/excel-stammtisch Ansonsten wäre die Sommerpause auch einfach zu groß… ich muss euch einfach wiedersehen…. echte Menschen, die Excel im Alltag nutzen! Einige haben auch schon angekündigt, dass sie einen kleinen Beitrag vorbereitet haben. 🙂

Ich und andere werden vermutlich schon wieder etwas eher da sein. Wegen der Ferien wird es vielleicht nicht so voll. Aber vielleicht finden sich ja doch eine unerschrockene Personen, die sich ein wenig zu Excel austauschen möchten. Gerne auch zu Basic-Themen. Wir müssen da nicht nur High-End-Kram machen.

Wie immer zahlt ihr nur ein wenig für die Tee-/Kaffee-/Wasser-/Softdrink-Flat.

Wenn Du Interesse hast, vielleicht eine kurze E-Mail an mich, damit wir ein bisschen besser planen können. Merci!

 

Der Stick bekommt eine Box

Mein USB-Stick bekommt nun endlich eine Aufbewahrungsbox. Ab sofort werden alle Sticks in einer kleinen Schachtel geliefert… frisch aus meinem neuen Spielzeug selbst in 3D gedruckt.

Mehr Infos gibt es hier https://thehosblog.com/700-excel-tutorials-fuer-privatpersonen%e2%80%8b%e2%80%8b/

Link zu YouTube: https://youtu.be/0lHl4VfmIeU

Und dann war da noch…

mein 1000. Excel-Video bei YouTube! Wow! Was für ein Feuerwerk und was für ein Feedback von Euch! Ich bin so dankbar für so viel tolles Feedback und dafür, dass so großartige Kollegen wie Bill Jelen, Mike Girvin, Oz du Soleil, Leila Gharani, Alan Murray, René Martin, Tanja Kuhn, André Naß, Christian Neuberger, Martin Weiß & weitere Lust darauf hatten, einen kleinen Beitrag für Nr. 1000 zu erstellen. Weitere? Oh… da kommt noch eine Compilation 🙂 Bin schon gespannt!

1000 Excel-Titel bedeuten auch, dass ich im Laufe der letzten neun Jahre selbst viel gelernt habe. Bestimmte Themen hatte ich online, dann konnte man ja nicht noch einmal dasselbe erzählen. Zudem kam auch ständig Feedback von euerer Seite, welches ich dann eingebaut habe und so wie beim erklimmen einer Wendeltreppe immer weiter auch mit dem eigenen Wissen nach oben kletterte.

In der langen Zeit konnte ich mich aber auch weltweit mit vielen Excel-Profis vernetzen. Persönlicher Austausch Face-to-Face mit den meisten Excel-Gurus. Das macht Spaß…. Hey… da sind bald wieder tolle Events in München (Oktober), Sofia und Ljubljana (beide im November). Nach München schaffe ich es 2019 leider nicht.

Und wie es mit den Videos weitergeht? Ja! 😉 Es geht irgendwie weiter voran. Ich werde aber auch in Kürze meine ersten Videokurse anbieten. Ein bisschen darf man ja auch an die Finanzierung der nächsten Fortbildung bzw. des nächsten Urlaubs denken.

Das 1000er-Jubiläum werde ich auch ein wenig auf dem nächsten Excel-Stammtisch feiern. Hmm… Tüte Erdnussflips oder so…  😉

Support durch euch

Und ein riesiges Dankeschön an alle bisherigen Unterstützer dieses Kanals! Der Kanal wächst langsam aber stetig um neue Abonnenten – jetzt gerade zu den Ferien stagniert er immer ein wenig…

Für die Weiterentwicklung sind vor allen Dingen wichtig: Weiterempfehlen, Abonnieren, Bewerten und Kommentieren. Die Reichweite des Kanals mag ganz gut sein, wenn ich aber immer wieder höre, dass jemand schon ewig mit Excel arbeitet, aber den Kanal nicht wahrgenommen hat, dann würde ich gerne dieses Potential noch ein wenig ausreizen. Von der magischen 100k-Grenze bei den Abonnenten bei YouTube bin ich noch Meilen entfernt. Vielleicht klappt das nicht mit deutschen Videos und ich muss mal wenig mein Englisch in Form bringen.

Einige haben auch schon die Videos und begleitenden Übungsdateien auf einem Stick erworben. Bei den Einzelsticks bleiben dann auch ein paar Euro für den Aufwand bei mir haften. Aber wie wäre es denn, wenn die Videos und Dateien bei euch im Unternehmen verfügbar wären? Habt Ihr überhaupt YouTube-Zugriff in der Firma? Ich kenne etliche Unternehmen, die bei Ihren internen Schulungen auf mein Material bzw. YouTube-Kanal zurückgreifen. Ich freue mich über jede Anfrage und komme da auch besonders Schulen und Universitäten entgegen.

Hey… und ein ganz herzliches Dankeschön an alle, die auf meine Amazon-Liste gestoßen sind. Ja! Bei uns schlagen ein paar große Herzen für gezeichnete, colorierte und verfilmte Werke 😉

 

Hey!

Habt einen großartigen Sommer
Sonnige Grüße aus Berlin

Andreas

Mehrere CSV-Dateien importieren – Power Query Miniserie 1.1

In einer kleinen Videoserie beschreibe ich den Import von vier CSV-Dateien, welche in Excel zu einer Liste verdichtet werden sollen. In den nachfolgenden Videos dieser kleinen Miniserie werden die Daten noch um Einträge aus einer anderen Datei per Verknüpfung angereichert und dann in Excel mit Pivot ausgewertet.

Übungsdateien zu Video E996

CSV-Dateien   4 CSV-Dateien in einer ZIP-Datei

E996 Kunden  Beispiel für das zweite Video

Hier geht es zum Video:

Videolink: https://youtu.be/FNouVfBG90E

Kleine Information zum Excel-Stammtisch in Essen…  der für den 10. Juli 2019 geplante Termin im Unperfekthaus kann von meiner Seite leider nicht stattfinden. Die Raumbuchung ist mit meiner Anwesenheit gekoppelt. Ihr könnt euch natürlich immer auch ohne mich treffen und über die Microsoft Teams-Gruppe abstimmen. Ich versuche einen anderen Termin zu finden.
Da ich an dem Abend in Berlin bin, würde sich ja dort etwas anbieten…

Sonnige Grüße und viel Spaß beim Nachbasteln

Andreas