Mein Idol auf ewig: Dornröschen. Kann nix, pennt 100 Jahre, wacht auf und wird Königin.

Hallo Herr Martin,

ich verfolge mit Begeisterung Ihre Excelkurse und -hilfen, die mir schon in der einen oder anderen Situation weitergeholfen haben. Nun scheitere ich aber dann doch und hoffe Sie können mir weiterhelfen.

Ich arbeite mit Excel 2019 und habe folgende Herausforderung. Ich möchte zu einem eingegebenen Wert alle Daten, die sich in der direkt danebenstehenden Spalte befinden anzeigen lassen. An den Wert, den ich eingebe, sind z. B. 10 Datensätze verknüpft. Ich habe es schon mit einer Matrixformel probiert, die index und vergleich beinhaltet, aber meist bekomm ich nur einen Wert, der dann aber z. B. 10 mal daneben angezeigt wird, je nachdem wie weit ich die Formel runterziehe.

Ich hoffe Sie können mir einen kleinen Tipp geben und weiterhelfen.

Liebe Grüße

Hallo Frau S.,
Sie verwenden wahrscheinlich den SVERWEIS, oder? Der SVERWEIS verlangt die Nummer der Spalte, in welcher der Wert gefunden werden soll. Man kann hier die Funktion SPALTE() verwenden – sie gibt die Nummer der aktuellen Spalte zurück (möglicherweise muss man + oder – eine Zahl rechnen.
Man kann mit WENN abfangen, ob überhaupt etwas gefunden wird und mit WENNFEHLER auf mögliche Fehler reagieren.
Hilft das?
Ich hänge mal eine Dummy-Datei an.
LIebe Grüße :: Rene Martin

Schlüsseldienst: zugezogen? – Ich: nein, ich komme von hier! – Ich, 27, manchmal dumm wie Brot!

Gestern habe ich über den interessanten Beitrag von Martin Weiß (tabellenexperte) geschrieben, der herausgefunden hat, dass SVERWEIS, wenn er auf eine andere Datei zugreift, die gesamte Datei – genauer: den gesamten ausgewählten Bereich – speichert:

Wird die Quelldatei geschlossen und umbenannt, funktioniert der SVERQWEIS noch immer – man kann sogar eine andere Spaltennummer wählen und ein anderes Suchkriterium.

Ich werde neugierig und benenne die Zieldatei mit .ZIP um und entzippe sie.

Tatsächlich: im Order \xl\externalLinks befindet sich die Datei externalLink1.xml in welcher sämtliche Daten gespeichert sind!

Fatal. Oder – wie Martin zu recht schreibt: Aufpassen!

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

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!

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

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!

Entweder finden Jogger Leichen oder sie werden selbst ermordet. – Was soll daran gesund sein?

Excelschulung. EIne junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen WENN und SVERWEIS:

Ich sehe folgende Formel:

=WENN(A17="";"";SVERWEIS(A17;Leistung;2))

Ist ja okay zu überprüfen, ob A17 leer ist, aber wäre es nicht besser ALLE Fehler mit einem WENNFEHLER abzufangen?

Sicherlich dient diese Formel WENN zum Üben …

Es ist total spannend älter zu werden. Man lernt jeden Tag neue Knochen kennen.

Hi René,

ich stehe mal wieder auf dem Schlauch:

Kannst Du mir sagen, wie ich, wenn in einem Tabellenblatt einen Wert aus einem anderen Reiter angegeben ist, in der daneben liegenden Spalte automatisch den Wert aus einer anderen Spalte der Tabelle in dem anderen Reiter ausgebe?

In A2 habe ich per Dropdown mit INDIREKT „<10%“ aus tab_AntwortKategorie[Kategorie].

In B2 möchte ich automatisch den dazugehörigen Wert aus tab_AntwortKategorie[Wert].

Was muss ich in B2 eingeben?

Ohne INDIREKT wüsste ich es, aber wie gesagt – ich stehe einfach auf dem Schlauch.

Momentan „fuhrwerke ich wild (und falsch) rum“, z.B.
=WVERWEIS($AE4;tab_AntwortKategorie[Kategorie];[Wert])

Bye

Michael

Hallo Michael,

wenn du den Wert DANEBEN haben möchtest, musst du den SVERWEIS verwenden, nicht den WVERWEIS. „S“ steht für „senkrecht“, heißt: die Liste ist von oben nach unten aufgebaut.

Schau mal in mein Beispiel:

Hilft das?

Liebe Grüße

Rene

Hi René

aaaaah (über eigene Dummheit)!

Ja, danke.

Bye

Michael

Oma: Homofiss – iss datt wedder son Schwulengrupp? – Enkel: Oma: datt heet Homeoffice. Net Homo-fiss!

Angelika ruft erneut an: „Hallo Rene: SVERWEIS kann DOCH zwischen Groß- und Kleinschreibung unterscheiden. Schau mal, ich hab’s gefunden!“

Prinzipiell glaube ich andere Menschen NICHT. Ich schaue die Datei an, die sie mir geschickt hat:

„Schau“, sagt sie, „der Text arnstein steht einmal groß- und einmal kleingeschrieben in der Liste. Wenn ich die Liste nun sortiere, und den Parameter WAHR beim SVERWEIS verwende, findet Excel den unteren, großgeschriebenen Eintrag.“

Ich prüfe das und trage den Text in Kleinbuchstaben ein: „arnstein“.

Auch hier wird der untere gefunden. Ich stutze: „Angelika: deine Liste ist nicht sortiert. Probier mal aus: =CODE(„A“) ergibt 65, =CODE(„a“) liefert 97. Arnstein ist kleiner als arnstein. Du musst anders herum sortieren.

Wir probieren es aus:

und auch:

Beide Male wird der untere Text gefunden. Die folgende Vermutung liegt nahe:

SVERWEIS „läuft“ bei der Verwendung des Parameters WAHR in [Bereich_Verweis] in einer Liste so lange nach unten bis ein größerer Wert als der gesuchte gefunden wird. Dann „stoppt“ SVERWEIS und gibt den Wert der zuletzt gefundenen Zeile zurück. Dabei wird weder bei WAHR noch bei FALSCH zwischen Groß- und Kleinschreibung unterschieden (wie fast an kleiner Stelle in Excel).

Fazit: Wenn man Texte in Listen mit SVERWEIS suchen muss (was durchaus sein kann – Artikelnummern, Personalnummer, Länderkennzeichen, etc. die alphanumerisch aufgebaut sind), sollte man NIEMALS die Liste sortieren und mit dem Parameter WAHR (oder ohne diesen Parameter) in SVERWEIS arbeiten. Schließlich möchte man ein eindeutiges Ergebnis. WAHR ist nur bei numerischen Reihen sinnvoll, beispielsweise: Umsatzzahlen, Erlöse, Gewinne, Kosten, Kilometer … von Betrag x bis Betrag y. Oder Datum von d1 bis d2.

Ich glaube, Angelika war mit meiner Erklärung einverstanden. Ganz sicher bin ich mir nicht. Ich werde sie noch einmal anrufen.

Habe meiner Pflanze vorgeschlagen, sie nur noch einmal im Monat zu gießen. Sie ist darauf eingegangen.

Angelika fragt weiter: „Kennst du das Problem? Ich habe eine Liste in der Begriffe mehrmals in unterschiedlicher Schreibweise vorliegen – manchmal in Großbuchstaben, manchmal nur in Kleinschreibweise. SVERWEIS findet leider nur den ersten Eintrag – egal wie er geschrieben ist.“

Bevor ich antworten kann, beantwortet Angelika ihre Frage selbst: „Ich habe vor Kurzem einen Artikel von Martin gelesen; dem Tabellenexperten – er beschreibt dort das Problem mit der Lösung IDENTISCH. Diese Funktion unterscheidet Groß- und Kleinschreibung.“

https://www.tabellenexperte.de/wie-sverweis-nur-richtig/

„Ja“, pflichte ihr bei: „den Artikel habe ich auch gelesen. Ich habe es ausprobiert – ja, er hat recht. Und: seine Lösung funktioniert so!“

In der Singlebörse wollte sich jemand mit mir treffen um spazieren zu gehen. Solche Sportsfreaks blockier ich doch gleich!

Lieber Herr Martin,

Ich muss zugeben, Excel nervt mich normalerweise nicht, aber ev beschäftige ich mich zuwenig

Damit..

Jetzt habe ich einen neuen Job und muss SVERWEISE nachvollziehen, prinzipiell fühle ich mich dazu

In der Lage, allerdings habe ich heute als Spaltenindex folgendes gefunden.

=SVERWEIS($A393;’specs quotation‘!$F$14:$DD$150;F$2;FALSCH)

Was bedeuted F$2

Vielleicht können sie mir ja helfen.

Vielen Dank

Judith

Ps: ich liebe ihre Seite :-J

Hallo Judith,

danke für das Lob.

Die Antwort ist (wahrscheinlich) einfach:

Schauen Sie mal in dem Tabellenblatt nach, welcher Wert in der Zelle F2 steht. Mit Sicherheit die Nummer der Spalte aus der der Wert des Bereichs !$F$14:$DD$150 des Blattes „specs quotation“ geholt wird.

Ich vermute, dass der SVERWEIS in mehreren Zellen der gleichen Spalte verwendet wird – deshalb wurde die Zeile 2 fixiert, also statt F2 wurde geschrieben F$2. Vielleicht wird er auch in mehreren Spalten verwendet. Was steht in G2? Was in H2? Wenn der SVERWEIS auch in mehreren Spalten benutzt wird, dann bedeutet F (ohne $-Zeichen) ein relativer Bezug – das heißt: in der Spalte daneben wird der Wert aus G$2 verwendet.

Wenn SVERWEIS nur in einer Spalte zu finden ist, hätte man auch $F$2 schreiben können, F$2 ist aber auch okay.

verstehen Sie die Ausführung?

schöne Grüße aus dem kalten und sonnigen München
Rene Martin

Lieber Herr Martin,

Vielen Dank, in Zelle F2 steht leider nix und in allen Formel Zeilen nach unten steht immer F$2 (eh klar),

die Info die in der Zelle aufscheint steht in Spalte :-J…

Es braucht definitiv Smiley in Outlook…

Ev nervt es ja doch, noch so kurz vor Weihnachten…

Danke

Lg Judith

Hallo Judith,

klicken Sie mal bitte auf die Zelle mit der Formel SVERWEIS.

klicken Sie mal auf den Funktionsassistenten fx neben der Eingabezelle.

Dort müsste der Werte von F$2 angezeigt werden:

Die Erleuchtung kam gerade, yup, in F2 steht 5, dass ist die 5te Spalte in der der Wert

Steht ausgehend von der Matrix.. Excel nervt doch nicht J

Vielen Dank!

Liebe Grüße ins wunderschöne München!

Frohe Feiertage und einen guten Rutsch ins nächste Jahr!

Judith

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

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

Mein Chef hat mich heute gefragt: „Wo warst du – ich habe dich überall gesucht.“ Ich habe ihm geantwortet: „Chef – gute Mitarbeiter sind schwer zu finden.“

Man hat mich wieder gezwungen LibreOffice Calc zu unterrichten.

Wenn man in Calc mit dem Funktionsassistenten nicht alle Parameter einträgt (beispielsweise bei SVERWEIS) erhält man einen Fehlerwert. Ruft man erneut die Funktion über den Assistenten auf, werden die urspünglich eingetragenen Parameter gelöscht.

Wenn Männer wüssten, wie schwierig wir uns manchmal selbst finden …

Man hat mich wieder gezwungen LibreOffice Calc zu unterrichten.

Immerhin – diese Unschönheit wurde in der neuesten Version von Calc entfernt.

Greift man mit einer Funktion, beispielsweise SVERWEIS auf ein anderes Tabellenblatt zu, hat Calc in älteren Version die absolute Referenz dargestellt als:

$Tabellenblatt.$A$1:$C$99

Trägt man die Formel per Hand ein, wählt den Bereich aus und fixiert ihn mit [F4] ist dis kein Problem:

Im Funktionsassistenten kann leider die Taste [F4] nicht verwenden. Dort musste man die fünf $-Zeichen per Hand eintragen. Und wie leicht hat man das erste (das auch sinnlos ist, weil die Tabelle ja nicht verändert wird) vergessen:

In der neuesten LibreOffice-Version werden beide Schreibweisen zugelassen – ältere Versionen verlangen allerdings das $-Zeichen vor Tabelle1. Und lieferten einen Fehler!

Ich lese „SPÜLMASCHINENFEST“. Aber nicht wann, wo, Kosten? Weiß jemand mehr darüber?

Guten Tag Herr Martin,

ich hatte schon lange kein Problem mehr mit Excel, sicherlich auch dank Ihrer Kurse, die ich mir in LINDEDIN Learning immer wieder einmal ansehe.

Heute nun habe ich ein Problem, bei dem Sie mir vielleicht helfen können. Wenn es allerdings nicht so nebenher geht, dann können Sie mir dies gerne mitteilen, dann muss ich weiter forschen.

Ich möchte mir hervorgehobene Zellen zählen lassen, genau genommen, möchte ich beim Wahlverfahren D’Hondt sofort sehen, wie viele Sitze hat Liste 1, 2…

Möglicherweise geht es auch nicht mit dem Zählen der hervorgehobenen Zellen sondern anders.

Ich habe es mit SVERWEIS versucht, was leider dann ab der 5. Zeile ein „NV“ brachte – mir leider unverständlich. Mit INDEX und VERGLEICH komme ich auch nicht weiter, weil ich ja keine genaue Zeilen oder Spalenzahl angeben kann. Diese kann ja – je nach Höchstwert – variieren.

Ich sende Ihnen die Datei einmal zu und freue mich auf Ihre Antwort, kann aber auch verstehen, wenn Sie schreiben: Kann ich Ihnen nun leider nicht mitteilen, dauert zu lange…

Trotzdem danke für Ihr offenes Ohr.

Ich wünsche Ihnen noch einen schönen Tag.

Hallo Frau P.,

das ist eine hübsche Fingerübung.

Ein paar Anmerkungen:

1. man kann Farben in Excel nicht zählen. Und wenn ich jetzt von Ihnen die Funktion ZELLE höre – mit Einschränkungen ja. Aber – diese Funktion reagiert nicht auf Formatänderungen. Und: zeigt auch nicht alle Formate an!

2. Ich würde es klassisch mit SUMMEWENN lösen. SVERWEIS, INDEX & co greifen auf mehrere Spalten zu. Mit der neuen Funktion XVERWEIS kann man es auch lösen – aber ich weiß nicht, ob sie diese schon haben. Werfen Sie einen Blick auf meine Lösung in Spalten M:O, bzw. zusammengefasst in Spalte Q.

=KGRÖSSTE($B$6:$E$10;ZEILE(A1))
=SUMMENPRODUKT((M6=$B$6:$E$10)*ZEILE($A$1:$A$5))
=INDEX($A$6:$A$10;N6)

3. Für welches Beispiel/Land verwenden Sie das? Ich lese bei wikipedia, dass noch die Schweiz, Spanien, Portugal, Belgien, Polen und Finnland dieses Verfahren verwenden. Sie wissen, dass dies in Deutschland 1985 durch das Hare-Niemeyer-Verfahren abgelöst wurde. Ich hätte es nämlich fälschlicherweise in meinem Excel-Formelbuch erläutert … und dann gemerkt, das wir so (in Deutschland) gar nicht mehr rechnen.

zusammengefasst

Hallo Herr Martin,

herzlichen Dank für Ihre schnelle Rückmeldung und Ihren Vorschlag der Berechnung.

Ich habe es jetzt noch einmal nachgebaut und verstanden, obwohl ich niemals auf die Formel gekommen wäre.

Zu Nr. 3:

Ich weiß nur, dass die Hochschule Furtwangen den Vorschlag unserer Justiziarin aus Stuttgart aufgegriffen hat und diesen in ihrer Wahlordnung nun festgelegt hat. Wir haben bisher mit Hare-Niemeyer gewählt. Aber leider soll es auch bei D’Hondt bleiben, obwohl wir eine Hochschule mit drei Standorten sind und unserer kleinster Standort mit diesem Wahlverfahren ernste Schwierigkeiten bekommen könnte. Ich hatte das (als Wahlleitung) angemerkt, es bleibt trotzdem bei D’Hondt.

Ich wünsche Ihnen noch einen schönen Tag und weiterhin viel Erfolg bei Ihrer Arbeit.

Im nächsten Leben mache ich etwas mit ohne aufstehen.

Einige Funktionen in Excel können nicht dateiübergreifend rechnen. Ist die Quelldatei geschlossen, stehen in der Zieldatei nach erneutem Öffnen Fehler in den Zellen.

Erstaunlicherweise kann Excel auch keine Bezüge auf intelligente/formatierte Tabellen in anderen Dateien zulassen. Hier ein Beispiel mit einem SVERWEIS:

Schließt man beide Dateien und öffnet die Zieldatei erneut, sieht das Ergebnis folgendermaßen aus:

Rechts stehen übrigens die Funktionen ZÄHLENWENN und SUMMEWENN.

SVERWEIS ist eine Erfindung des Teufels !!!!!!!!!!!!!!!!!!!!!

Hi Rene,

wahrscheinlich wirst du jetzt von mir das CALC Zeugnis zurück verlangen, aber mich macht der SVERWEIS noch wahnsinnig in EXCEL2016.

Es ist eine völlig simple Datei, aber trotzdem verweigert SVERWEIS den Dienst.

Ich hab dir die Datei im Original angehängt. Schau dir mal meine SVERWEIS Formal an und zeig mir bitte den Fehler. Die Boulesche Variable hab ich auch in allen Variationen ausprobiert à IMMER #NV.

Die Formel:

=SVERWEIS(A2;$I:$K;1)

Hi Jo,

SVERWEIS sucht immer in der ersten Spalte einer Liste.

Dein Pin steht aber in der dritten Spalte.

Du kann es lösen mit den Funktionen INDEX und VERGLEICH:

=INDEX(J:J;VERGLEICH(A2;K:K;0))

Hilft das?

LG aus Hamburg

Rene

Stirb nicht als Jungfrau, sonst erwarten dich im Himmel nur Terroristen.

Schon irgendwo doof. Zumindest ein bisschen.

Ich erstelle eine Pivottabelle. In der zweiten Spalte befindet sich eine ID. Diese ID soll mit INDEX- und VERGLEICHS-Funktionen in einer anderen Tabellen gefunden werden und weitere Informationen geliefert werden, beispielsweise die Gesellschafterin und die Geschäftsbezeichnung Spalte A und B):

Filtert man die Pivottabelle werden jedoch die Zeilen außerhalb der Pivottabelle nicht ausgeblendet. Das bedeutet: ich erhalte eine Reihe Fehlerwerte:

Und nein – ich kann und will die anderen Daten nicht zu den Daten der Pivottabelle hinzufügen. Und ja: natürlich habe ich anschließend diesen Fehler mit WENNFEHLER abgefangen.

Schon irgendwie seltsam: ein Filtern der Pivottabelle wirkt sich nicht auf die Zeilen außerhalb aus. Anders als beim „normalen“ Filtern mit Autofilter.

Dumm sein ist nicht leicht. Ich habe viel Konkurrenz.

Hallo René,

 

ich bin auf deine Webseite gestoßen und hoffe, dass es ok ist, eine E-Mail zu schreiben.

ich habe folgendes Problem: Mir steht eine Exceltabelle mit einer Liste von verschiedenen Kostenarten in Spalte A zur Verfügung, z.B. „MasterCard, Adobe“, „Klipfolio 30€“ und „Juni Klipfolio VisaCard“. Diese möchte ich in die Kategorien „Adobe“ und „Klipfolio“ einteilen (nur als Beispiel, in Wirklichkeit sind es natürlich wesentlich mehr Kategorien) und suche deshalb nach genau diesen Stichwörtern im Text. Wird eines dieser Stichwörter gefunden, soll es entsprechend in Spalte B geschrieben werden, so dass ich das ganze später bequem in einer Pivottabelle zusammenfassen kann.

 

                                                  B

Kostenart                                      Stichwort

MasterCard, Adobe                        Adobe

Klipfolio 30€                                  Klipfolio

Juni Klipfolio VisaCard                    Klipfolio

Insgesamt handelt es sich um ca. 1.000 Datensätze und rund 50 verschiedene Stichwörter. Das „Problem“ ist zur Zeit über eine lange verschachtelete Wenn-Funktion gelöst. Ich dachte aber, es gäbe über VBA vllt eine schnellere und unkompliziertere Lösung. Zur Zeit bin ich so weit, dass ich nach einem bestimmten Stichwort (egal wo im Text es steht) suchen lassen kann.

Sub a()

Dim Rng As Range
Dim wks As Worksheet

Set wks = Worksheets(„data“)

Set Rng = wks.Range(wks.Cells(1, 1), wks.Cells(1000, 1)).Find(what:=“Adobe“, lookat:=xlPart, LookIn:=xlValues, MatchCase:=True)

If Not Rng Is Nothing Then Rng.Select

End Sub

Ich komme allerdings jetzt nicht mehr weiter bzw. bin mir auch nicht sicher, ob das überhaupt ein guter Ansatz ist.

Kannst du mir weiterhelfen?

Vielen Dank im Voraus,

#####

Hallo J.,

ich würde das mit einem SVERWEIS lösen. Oder mit der Funktion INDEX und VERGLEICH. Kannst du?

Liebe Grüße

Rene

#####

Hallo René,
vielen Dank für deine super schnelle Antwort.
Ja, beide Funktionen sind mir bekannt. Da ich ja aber ca. 50 verschiedene Suchkriterien habe, würde ja auch da wieder eine extrem lange und verschachtelte Formel in der Spalte B stehen- oder übersehe ich etwas? Aus diesem Grund dachte ich an eine VBA Abfrage. Diese wäre vllt ebenfalls lang und verschachtelt- würde ja aber quasi „im Hintergrund“ laufen- andere Nutzer müssten dann ja nur noch monatlich in der Kostenübersicht das Makro starten. Siehst du bzgl VBA auch eine Möglichkeit?
Danke und LG J.
#####
Wer nervt mehr? Excel oder die Menschen, die Excel benutzen?

Selbst gemachte Geschenke sind beängstigend, denn sie zeigen, dass du zu viel Freizeit hast (douglas Coupland)

Servus Rene,

ich hoffe es geht dir soweit gut und du bist mit deiner 4ten Million nun endlich fertig

Sorry, dass ich dich wieder mal belästige, aber der SVERWEIS mag mich nicht mehr. Ich hatte jetzt damit jahrelang keinerlei Probleme mehr und nun sträubt sich das Ding wie ne keusche Jungfrau.

Ich hab dir mal die Datei angehängt. Wäre super wenn du da mal drüber schauen könntest.

Ich muss hier 2 Tabellen nach der PIN Nummer vergleichen.

Kriterium: PIN Nummer (Spalte F)

Matrix: Spalte K-M

Gesuchter Wert: Nachname (Spalte M)

Hallo J.,

du solltest richtig markieren. Dann klappt es auch.

Und: verwende besser den Parameter FALSCH – dann siehst du auch die PIN-Nummern, die nicht in der Liste vorhanden sind.

Liebe Grüße

Rene

Moing Rene,

zunächst einmal Danke für deine schnelle Hilfe.

Und was jetzt kommt ist die reine Wahrheit, ich hab Zeugen dafür. Bitte glaub mir, der SVERWEIS macht mir normalerweise keine Problem mehr, aber was hier abgelaufen ist, ist nicht erklärbar:

Ich hatte genau die gleiche Formel wie du auch, aber bei mir kamen völlig blödsinnige Werte, meistens ein #NV. Ich hab dann manuell eine Tabelle entworfen, nur um evtl. Zelltypenfehler zu vermeiden, aber auch da hat der SVERWEIS nicht funktioniert. Erst dann habe ich dir geschrieben. Als deine Antwort da war und in deiner Formel genau das gleiche stand wie in meiner, war ich völlig von den Socken . Darauf nahm ich meine Original Tabelle und hab einen SVERWEIS nochmal genauso eingegeben wie davor und …. JETZT LÄUFT DAS DING WIE GESCHMIERT.

Hast du sowas schon mal erlebt?

DA fällst doch vom Glauben ab oder hast du da eine Erklärung?

Prokrastination – ein Problem, dass ich gleich morgen angehe.

Es dauert zirka eine Minute, bis die 1,5 MByte große Datei geöffnet ist. Das Filtern dauert ebenso lange.

In einer Excelmappe befindet sich eine Verknüpfung auf eine externe Datei auf die mittels SVERWEIS zugegriffen wird:

Inquire ermittelt 65.000 Formeln in dieser Datei:

Die Aktivierung der Mulithreadingberechnung (In den Optionen / Erweitert) nutzt nichts:

Sämtliche Prozessoren sind ausgelastet:

Lösungsvorschläge bei „zu vielen Formeln“, die Excel verlangsamen:

* Die verknüpften Daten in die Arbeitsmappe hineinkopieren

* Die automatische Berechnung deaktivieren

* Formeln durch Werte ersetzen

* Statt mit einer Verknüpfung auf die Datei mit PowerQuery (Daten abrufen und transformieren) auf die Daten zugreifen.

* Die Datei als Excel-Binärarbeitsmappe (XLSB) speichern.

 

 

Ich bin sehr wohl spontan … wenn man mir früh genug Bescheid sagt

Eigentlich unterscheidet Excel zwischen Text und Zahl. Eigentlich. Sicherlich kennen Sie folgendes Phänomen:

Eine Spalte ist als Text formatiert:

In einer anderen Spalte stehen ZAHLEN. Diese werden mit der Funktion SVERWEIS als #NV (nicht vorhanden) quittiert:

Das habe ich schon mehrmals beschrieben – beispielsweise in: „Sverweis funktioniert nicht“
Ebenso werden sie bei der Überprüfung auf Gleichheit

{=ODER(K2=$G$2:$G$22)}

(als Matrixformel) korrekt als unterschiedliche Werte erkannt:

Verwendet man statt dem Gleichheitsoperator „=“ jedoch die Funktion IDENTISCH werden die Texte und Zahlen als gleich(wertig) eingestuft:

{=ODER(IDENTISCH(K2;$G$2:$G$22))}

Ebenso übergeht ZÄHLENWENN diesen Unterschied:

Auch hier gilt mal wieder – schade, dass Excel an so vielen unterschiedlich ist, beziehungsweise einfach nicht konsequent. Kein Verlass auf gar nichts!

SVERWEIS rechnet nicht

Seit einer Weile arbeite ich mit dem SVERWEIS. Ich habe ihn schon recht gut verstanden. Aber manchmal rechnet er nicht richtig. Warum?

SVERWEIS rechnet nicht richtig.

SVERWEIS rechnet nicht richtig.

Dazu muss man sich die Formel genau ansehen:

=SVERWEIS(K2;$A$1:$A$32;3;FALSCH)

Sie suchen den Wert, der in der Zelle K2 steht in der Spalte A – genauer in den Zellen A1 bis A32. Soweit so gut. Sie möchten den Wert der dritten Spalte (3), also den Last Name wissen. Sie müssen den Bereich ändern: Es ist richtig – Sie suchen zwar in A1:A32, aber in der Spalte A steht nicht der Wert den Sie haben möchten. Sie müssen in der Matrix (in der Informationstabelle, in der die Daten gesucht werden), auch den Bereich einschließen, in dem sich die Daten befinden, also Spalte C. Sie können dabei gerne übers Ziel schießen, beispielsweise:

=SVERWEIS(K2;$A$1:$H$32;3;FALSCH)

Dann funktioniert es.

SVERWEIS klappt nur manchmal

In den ersten Zeilen rechnet der SVERWEIS noch richtig, aber dann gibt es Fehler.

SVERWEIS rechnet manchmal richtig.

SVERWEIS rechnet manchmal richtig.

Eigentlich müssten Sie den Fehler selbst finden können, wenn Sie die Formel anschauen. Der Bereich (hier: A1:B14) wurde nicht fixiert. Das bedeutet: er „wandert“ beim Herunterziehen mit nach unten. Was passiert, können Sie leicht überprüfen, wen Sie auf eine Zelle doppelklicken, die sich weiter unten befindet.

Der Bereich wurde nicht fixiert.

Der Bereich wurde nicht fixiert.

Wandeln Sie also A1:B14 in $A$1:$B$14 um (oder verwenden einen Namen für diesen Bereich)

VLOOKUP und SVERWEIS

Die Erklärung des letzten Parameters „Range_Lookup“, der vielen Anwendern Schwierigkeiten bereitet, lautet im englischsprachigen Excel:

„[…]is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE“

Im Deutschen dagegen:

„[…] gibt an, ob eine genaue Übereinstimmung gefunden werden soll: WAHR = aus der aufsteigend sortierten Reihenfolge der Werte wird der Wert zurückgegeben, der am dichtesten am gesuchten Wert liegt; Falsch = es wird eine genaue Übereinstimmung angenommen“

Dabei stellen sich mir folgende Fragen:

1. Warum wird das nicht korrekt erklärt? Weder im Englischen noch im Deutschen? „the closest match“ ist genauso falsch wie „der am dichtesten am gesuchten Wert liegt“.

2. Warum schneidet Excel seit der Version 2007 den letzten Teil der Erklärung ab. Hat das noch keiner gemerkt?

3. Warum wird „to find the closest match“ übersetzt mit „gibt an, ob eine genaue Übereinstimmung gefunden werden soll“ – „closest“ ist nicht „genau“ sondern „ungefähr“. Somit ist die deutsche Übersetzung zur englischen spiegelverkehrt. Und das, seit ich Excel kenne (seit Excel 4.0)

Auf http://office.microsoft.com/de-de/excel-help/sverweis-HP005209335.aspx lese ich:

„[…]

Wenn dieser Parameter WAHR ist oder weggelassen wird, wird eine ungefähre Entsprechung zurückgegeben. Wenn keine genaue Entsprechung gefunden wird, wird der nächstgrößere Wert zurückgegeben, der kleiner als Suchkriterium ist.

Die Werte in der ersten Spalte von Matrix müssen aufsteigend sortiert sein. Andernfalls gibt SVERWEIS möglicherweise nicht den korrekten Wert zurück. Sie können die Werte aufsteigend sortieren, indem Sie im Menü Daten auf Sortieren klicken und Aufsteigend auswählen. Unter Standardsortierreihenfolgen finden Sie weitere Informationen.

[…]“

Auf https://support.office.com/de-at/article/SVERWEIS-0bbc8083-26fe-4963-8ab8-93a18ad188a1 lese ich:

„Wenn Bereich_Verweis entweder WAHR oder nicht belegt ist, wird eine genaue oder ungefähre Entsprechung zurückgegeben. Wird keine genaue Entsprechung gefunden, wird der nächstgrößere Wert zurückgegeben, der kleiner als Suchkriterium ist.“

Warum so umständlich? Warum nicht beispielsweise so:

„Ist dieser Wert WAHR und es wird kein dem Suchkriterium entsprechender Wert gefunden, wird der nächstkleinere Wert (das heißt der Wert aus der darüberliegenden Zeile) ausgegeben. Ist der Wert FALSCH und es wird kein dem Suchkriterium entsprechender Wert gefunden, wird eine Fehlermeldung erzeugt.“

VLOOKUP auf englisch

VLOOKUP auf englisch

SVERWEIS auf deutsch

SVERWEIS auf deutsch