Schlagwort: XVERWEIS

Achtung: Am Samstag veranstalte ich wieder den Kurs „Saubere Fenster leicht gemacht!“ bei mir zu Hause. Die schmutzigen Fenster werden KOSTENLOS zur Verfügung gestellt. Bei Interesse bitte bei mir melden!

Merkwürdige Formeln

Hallo Herr Martin,

ich würde ihr Angebot gerne wahrnehmen, und eine Praxisfrage zu einer Funktion stellen, falls das ok ist.

Problem: Summewenns verschiedene Testungen; eine Testung davon ist, ob der Wert in einer weiteren Liste zu einer bestimmten Kategorie gehört.

=summewenns($c:$c;$b:$b;“>=80000“;$b:$b;“<90000“;[DEM GERADE GETESTETEN WERT IST IN Tabellenblatt2! IN SPALTE 4 DER WERT „Zwerg“ ZUGEORDNET ])

Also in Worten: Summiere die Umsätze, wenn die Person eine 80000er-PLZ hat und ein Zwerg ist

[Blatt 1 hat 150.000 Zeilen; Blatt 2 hat 100 Zeilen. Das Tabellenblatt1 ist ein automatisiert erstellter Bericht (unveränderlich), Blatt 2 sind meine eigenen Daten, könnte verändert werden; Datenausgabe in drittem Tabellenblatt]

Ich habe verschiedene Formelkombinationen ausprobiert, es scheitert immer, dass die Testung einer bestimmten Zelle bedarf….

Haben Sie eine Idee? Falls nein, oder zu komplex, auch ok.

Freundliche Grüße und Vielen Dank vorab

####

Hallo Herr B.,

schwierig!

Erste Lösung: Sie verwenden eine Hilfsspalte:

Die Formel lautet dann:

=SUMMEWENNS(C2:C12;B2:B12;">=80000";B2:B12;"<80000";C2:C12;"Zwerg")

Zweite Lösung: das Problem ist, dass die Funktionen ZÄHLENWENN, SUMMEWENN & co nicht mit den Matrixfunktionen zusammenarbeiten. Aus irgendeinem Grund geht:

=ZÄHLENWENN(EINDEUTIG(C2:C4);1)

NICHT!

Schade – man hätte ZÄHLENWENN so schön verwenden können: „bist du in der Liste?“ Oder umgekehrt: SUMMEWENNS und HSTAPELN: baue die zwei Listen zusammen.

Also anders:

XVERWEIS ist matrixfähig. Also berechnet XVERWEIS(A2:A12;G2:G6;TYP!H2:H6) zu allen Zellen „Zwerg“ oder „Mensch“

Haben Sie die Funktion HSTAPELN? Sie baut zwei Bereiche nebeneinander zusammen, beispielsweise

=HSTAPELN(A2:A7;C2:C7)

Damit kann man die beiden Bereiche zu einem Bereich zusammenfassen:

=HSTAPELN(A2:C12; XVERWEIS(A2:A12;G2:G12;H2:H12))

Und davon kann man die heißen filtern:

= FILTER(HSTAPELN(A2:C12;XVERWEIS(A2:A12;G2:G12;H2:H12));(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg"))

Das Multiplikationszeichen dient als UND-Operator:

= FILTER(HSTAPELN(A2:C12;XVERWEIS(A2:A12;G2:G12;H2:H12));(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg")*(B2:B12>=80000)*(B2:B12<90000))

Eigentlich genügt es, wenn man nur die zweite und dritte Spalte „stapelt“:

= FILTER(HSTAPELN(C2:C12;XVERWEIS(A2:A12;G2:G12;H2:H12));(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg")(B2:B12>=80000)(B2:B12<90000))

Das Ergebnis:

Und die Werte – normalerweise stehen mehrere untereinander – kann man addieren/summieren:

= SUMME(FILTER(HSTAPELN(C2:C12;XVERWEIS(A2:A12;G2:G12;H2:H12));(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg")*(B2:B12>=80000)*(B2:B12<90000)))

Ich hänge Ihnen die Datei an.

Nachtrag: Kennen Sie LET? Damit kann man Teile einer Funktion an eine Variable auslagern:

= LET(X;XVERWEIS(A2:A12;G2:G12;H2:H12);SUMME(FILTER(HSTAPELN(C2:C12;X);(X="Zwerg")*(B2:B12>=80000)*(B2:B12<90000))))

Fragen? Einfach fragen!

Liebe Grüße und: viel Spaß mit Excel wünscht

Rene Martin

Nachtrag: nur wenige Augenblicke, nachdem ich diesen Beitrag gepostet hatte, kam eine kürzere Lösung – von Ernst:

=SUMME(((B2:B12>80000)*(B2:B12<90000)*(XVERWEIS(A2:A12;G2:G12;H2:H12)=&quot;Zwerg&quot;))*C2:C12)

Es funktioniert sogar mit SVERWEIS

=SUMME(((B2:B12>80000)*(B2:B12<90000)*(SVERWEIS(A2:A12;G2:H12;2;FALSCH)="Zwerg"))*C2:C12)

Danke, Ernst – wirklich sehr clever – ohne LET  FILTER  oder HSTABELN.

Und schließlich:

=SUMMENPRODUKT((B2:B12>80000)*(B2:B12<90000)*(XVERWEIS(A2:A12;G2:G12;H2:H12)="Zwerg")*C2:C12)
=SUMMENPRODUKT((B2:B12>80000)*(B2:B12<90000)*(SVERWEIS(A2:A12;G2:H12;2;FALSCH)="Zwerg")*C2:C12)

funktionieren auch.

3 Kommentare ,

Egal, ob müde, hässlich oder reich: vor der Kaffeemaschine sind wir alle gleich.

Fehlermeldungen

Warum denn der XVERWEIS, den wir in der Excelschulung geübt haben, nicht funktioniere, will eine Teilnehmerin wissen:

Die Ursache ist schnell gefunden: sie hatte ein Komma statt eines Semikolons getippt.

Eine andere Teilnehmerin gibt ihr den Tipp den Funktionsassistenten zu verwenden, statt die Formel per Hand zu schreiben. Ich pflichte ihr bei.

Kommentar hinterlassen , ,

Mein Mann und ich mögen genau die gleichen Dinge. Aber er hat zehn Jahre gebraucht, bis er das kapiert hat.

Merkwürdige Formeln

Eine Teilnehmerin einer Excelschulung stellt mir eine Frage. Ob ich ihr helfen könne.

Klar.

In einer Tabelle hat sie in einer Spalte Namen, in einer anderen einen Rechnungsbetrag:

Sie möchte auf einem zweiten Tabellenblatt zu dem Namen den entsprechenden Rechnungsbetrag sehen:

Nichts leichter als das: XVERWEIS muss her:

=XVERWEIS(A2;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51)

Sieht doof aus, weil in beiden Tabellen Leerzeilen stehen und Überschriftsinformationen, welche nun mit 0 Euro quittiert werden.

Also wird die Formel angepasst:

=WENN(XVERWEIS(A2;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51)=0;"";XVERWEIS(A2;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51))

Sieht doof aus, weil Nina 0 Euro hat und nun eine leere Zelle:

Also: Formel ändern und 0 durch „“ ersetzen:

=WENN(XVERWEIS(A8;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51)="";"";XVERWEIS(A8;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51))

Immer noch doof – weiter unten entdecken wir einen Namen, der noch nicht auf dem ersten Tabellenblatt auftauchte. Also ändern wir den Parameter wenn_nicht_gefunden in „“ – und schon verschwindet das #NV

Die Formel:

=WENN(XVERWEIS(A27;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51;"")="";"";XVERWEIS(A27;Tabelle1!$E$2:$E$51;Tabelle1!$H$2:$H$51;""))

Die Teilnehmerin ist glücklich!

Kommentar hinterlassen

Guck mal – dein Mann spricht gerade mit einer Anderen! – Lass ihn doch. Mal schauen, wie lange er den Bauch einziehen kann.

Fehlermeldungen, Power Query

Amüsant.

PowerQuery-Schulung. Wir greifen auf eine (Währungskurs-)Tabelle im Internet zu:

Wir müssen die Daten transformieren, das heißt: an mehreren Stellen trennen:

Das Ergebnis

wird nach Excel als Tabelle geladen und dort weiterverarbeitet:

IN diesem Beispiel mit einem XVERWEIS (oder SVERWEIS würde auch funktionieren).

Eine Teilnehmerin meldet sich und sagt, dass ihr XVERWEIS Fehler liefern würde:

Die Ursache war schnell gefunden: sie hatte nicht Leerzeichen:Bindestrich als Trennteichen verwendet, sondern nur den Bindestrich:

Somit war noch ein Leerzeichen im Text, Und deshalb lief der XVERWEIS ins Leere …

Kommentar hinterlassen , ,

Mein Rechner hat sich aufgehängt. Meine Wäsche macht das nie.

Merkwürdige Formeln

Volker Pagel ist sehr irritiert. Er hat lange gesucht, um die Ursache für folgende Inkonsequenz zu finden.

In einer Liste befinden sich Kostenstellennummer, beispielsweise 010, 0010 oder 000010.

Die Funktionen der Klasse ZÄHLENWENN, SUMMEWENN & co unterscheiden nicht zwischen 010, 0010 oder 00001, obwohl diese als Text vorliegen. Wahrscheinlich werden diese Texte intern als Zahlen behandelt.

Anders dagegen die Funktionen WENN, SVERWEIS, XVERWEIS, SUMMENPRODUKT & co. HIER wird klar zwischen 010 und 00010 unterschieden!

2 Kommentare , , , , , ,

Mama, ich bin noch nicht in Hausaufgabenstimmung. Könntest du mich bitte böse anschauen und mir sagen, dass ich nicht so faul sein soll!

Fehlermeldungen

„Ein in der Formel verwendeter Wert ist vom falschen Datentyp“, behauptet Excel und liefert den Fehler #WERT beim XVERWEIS:

Nein, lieber XVERWEIS – DAS ist nicht die Ursache des Fehlers, sondern weil die beiden Bereiche unterschiedlich groß sind:

$A$1:$A$297872 und $F$1:$F$297782

Kommentar hinterlassen , ,

Gerade meinen verheirateten Chef bei tinder gefunden. Ich bin schon so gut wie befördert.

Fehlermeldungen

Warum rechnet der XVERWEIS bei mir nicht, möchte ein Teilnehmer in meiner Excelschulung wissen:

Ich lasse die korrekt berechnete Zelle editieren:

Ich sehe den Fehler nicht. Er als ich den Teilnehmer bitte, eine andere, fehlerhafte Zelle mit einem Doppelklick zu editieren, sehe ich, dass von den acht Dollarzeichen eines fehlt. Und somit sind die beiden Spalten nicht gleich groß.

Kommentar hinterlassen , ,

Wann willst du dich deinem Alter entsprechend verhalten? – Sag ich nicht!

Merkwürdige Formeln

Ich wohne in der Albert-Roßhaupter-Straße. Eigentlich müsste sie – nach neuer deutscher Rechtschreibung – Albert-Rosshaupter-Straße heißen. Aber der Name des SPD-Politikers wurde nicht geändert. Sei’s drum. Was passiert allerdings bei der Suche in Excel von Wörtern mit „ß“ und „ss“?

Die Funktion SVERWEIS unterscheidet; XVERWEIS allerdings nicht:

Erstaunlich! Umso erstaunlicher ist es, dass im Spanischen weder zwischen „n“ und „ñ“ unterschieden wird:

Im spanischen Excel werden auch nicht die Vokale mit und ohne Akzent unterschieden:

nicht bei BUSCARV (SVERWEIS) und nicht bei BUSCARX (XVERWEIS). Sehr erstauntlich.

Danke an Mourad Louha für diesen wertvollen Tipp!

Kommentar hinterlassen ,

Liebe Nachbarn – am Mittwoch ist Vollmond. Es kann etwas lauter bei uns werden. Die Werwolfs.

Excel rechnet gar nicht, Fehlermeldungen

Sehr ärgerlich!

Auf einem Formular soll – unter anderem – der Preis für eine gelieferte Menge berechnet werden – in Abhängigkeit vom Gewicht. Dafür gibt es eine Tabelle:

In der Originaltabelle befand sich der Text „kg“ hinter den Zahlen – den habe ich schnell gelöscht. Eine schöne Aufgabe für XVERWEIS denke ich – eine Formel – alles drin, alles dran …

Mich beschleicht ein Gedanke …

Ich rufe den Kunden an und bitte ihn in einer leeren Excelmappe die drei Zeichen =XV zu tippen. „Ich sehe nichts“ lautet die Antwort. Das heißt: sie haben noch eine ältere Excel-Version, in der die Funktion XVERWEIS und XVERGLEICH noch nicht vorhanden sind. Damit natürlich auch noch nicht die neuen und ach so praktischen Matrixfunktionen. *gggrrrrr*

Kommentar hinterlassen ,

Warum sind Blondinenwitze immer so kurz? – Damit auch Männer sie verstehen!

Merkwürdige Formeln

Lieber Herr Martin,

Excel nervt nicht, ich weiß nur noch nicht ob es kann was ich gerne hätte.

Ich müsste Zellen in Spalten verknüpfen, wenn diese einer Bedingung in einer anderen Zelle

Entsprechen. So wie SummeWENN ich diese Zellen zusammenaddieren kann ist die Frage ob

Ich den Inhalt der Zellen auch verkettet ausgeben kann.

Vielen Dank

Bevor ich eine Antwort gebe, Frau L.,

zwei Gegenfragen: sind die Daten sortiert (nach den Kategorien, nach denen sie gruppiert werden sollen) und: haben Sie die Funktionen FILTER und EINDEUTIG? Haben Sie XVERWEIS? (damit geht es „recht einfach“)

Liebe Grüße

Rene Martin

So ist es mir recht,

leider kein XVERWEIS und kein WENNS… hoffe auf ein besseres 2021…

Eindeutig? Index? Geht nicht mit Liste sondern nur Matrix oder Bezug, damit kenn ich mich nicht aus…

Gruppiert nach der Nummer insofern dass sie untereinander geschrieben wurden (händisch…) allerdings Filter vorhanden

Die erste Spalte soll als Referenz herangezogen werden die letzte Spalte beinhaltet die Informationen die ich gerne zusammen verkettet hätte, also

Für 20-44 sollte dort MFM, MFM; CPS, PIB stehen, fantastisch wäre, wenn jeder Wert nur 1x vorkommen würde, aber soweit wage ich nicht zu träumen…

Beim Trennzeichen wäre ich emotional flexibel

Die erste Spalte wird in einem Übersichtssheet sozusagen als Einzeiler ausgegeben.

Vielen Dank

Hallo Frau L.,

ich hoffe, Sie haben die Funktion TEXTVERKETTEN. DAMIT klappt es.

Entweder Sie setzen eine Pivottabelle auf die Liste auf, gruppieren die Daten und verketten dann die Infos der letzten Spalte. Dann haben Sie allerdings Duplikate.

Oder Sie erstellen zwei Pivottabellen: eine für die gruppierten Infos der ersten Spalte und eine mit den Werten der ersten und letzten Spalte. Und verketten so die Texte.

Die erste Formel lautet:

=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($I$1;
VERGLEICH(L2;$A$2:$A$27;0);0;
VERGLEICH(L2;$A$2:$A$27;1)-
VERGLEICH(L2;$A$2:$A$27;0)+1))

Die zweite:

=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($M$31;
VERGLEICH(O32;$L$32:$L$51;0);0;
VERGLEICH(O32;$L$32:$L$51;1)-
VERGLEICH(O32;$L$32:$L$51;0)+1))

Klappt das? Ist es das, was Sie möchten?

Kommentar hinterlassen , , , , , ,

Glück ist das einzige, was sich verdoppelt, wenn man es teilt. Und Bakterien!

Datenaustausch, Fehlermeldungen

Gestern war ich sehr verblüfft und erstaunt. Ich habe eine Excelmappe mit einer intelligenten (strukturierten, dynamischen, formatierten) Tabelle. In einer ANDEREN Datei greife ich darauf zu – per SVERWEIS, INDEX und VERGLEICH oder XVERWEIS:

Also beispielsweise über:

=SVERWEIS([@Name];Datenquelle.xlsx!tbl_Schlumpf[#Alle];2;FALSCH)

oder:

=INDEX(Datenquelle.xlsx!tbl_Schlumpf[Schlumpf];VERGLEICH([@Name];Datenquelle.xlsx!tbl_Schlumpf[Name];0))

oder:

=XVERWEIS([@Name];Datenquelle.xlsx!tbl_Schlumpf[Name];Datenquelle.xlsx!tbl_Schlumpf[Schlumpf])

Beide Dateien, die sich im gleichen Ordner befinden, werden geschlossen. Nur die Zieldatei wird geöffnet. Das Ergebnis: Fehlermeldungen bei allen drei Formeln:

Ich ersetze die Bezüge, also

Datenquelle.xlsx!tbl_Schlumpf[Name]

durch

[Datenquelle.xlsx]Quelle!$A:$A

Das ist nicht schön! Aber – es funktioniert! Hat Microsoft vergessen DAS bei intelligenten Tabellen zu implementieren? DAS ist ja ein Schritt zurück! DAS will ich eigentlich nicht (mehr). Immerhin: es funktioniert. So kann ich die Zieldatei öffnen, die Werte werden aktuell angezeigt, ohne dass die Quelldatei offen sein muss.

Danke an Christa für den Hinweis, dass man die Verknüpfung über PowerQuery organisieren kann.

Und: vielen Dank an den Hinweis von Mourad Louha: DAS IST GEWOLLT!

https://support.microsoft.com/en-sg/help/2299192/links-to-data-tables-residing-in-external-files-display-ref?fbclid=IwAR32YcJ4HKAB_B_lMBHB17OtX8y8rIV0dVoFCFrmeYTjH4zxnPADLGigbM8

Kommentar hinterlassen , , , , , ,

Manche Faultiere fahren den Kreislauf so weit runter, dass sie aus Versehen sterben. – Als ich das gelesen habe, habe ich mich erstmal aufrecht hingesetzt.

Merkwürdige Formeln

Doof! In einer intelligenten Tabelle habe ich drei Spalten: ID, Vorname und Nachname. XVERWEIS kann einen Vornamen finden:

=XVERWEIS(G2;tbl_Kunden[Nr];tbl_Kunden[Vorname])

XVERWEIS kann ALLE Vornamen (als Matrixfunktion) zurückgeben:

=XVERWEIS(G2:G9;tbl_Kunden[Nr];tbl_Kunden[Vorname])

XVERWEIS kann Vor- und Nachnamen (hätten wir noch mehr Informationen – so auch diese) zurückgeben:

=XVERWEIS(G2;tbl_Kunden[Nr];tbl_Kunden[Vorname]:tbl_Kunden[Nachname])

Aber leider: beides geht nicht: mehrere Spalten zurückgeben von mehreren IDs:

Schade!

Kommentar hinterlassen , ,

Wie die Silvesterfeier war? – Weiß nicht – ich habe noch keine Fotos gesehen.

Merkwürdige Diagramme

Mit den drei Funktionen BEREICH.VERSCHIEBEN, INDIREKT und XVERWEIS kann man einen dynamischen bereich aufspannen. Diese drei Funktionen kann man als Namen speichern (ich habe sie mal Jahr1, Jahr2 und Jahr3 genannt).

Die Namen mit den Funktionen BEREICH.VERSCHIEBEN und XVERWEIS kann man wunderbar in einem Diagramm verwenden:

INDIREKT aber nicht!

Kommentar hinterlassen , , , ,