Mehrere Ergebnisse bei SVERWEIS möglich?

Computer: Software & Programmierung Office Software
#1

Hallo,

wieder einmal erhoffe ich mir Hilfe aus diesem großartigen Forum.
Ich habe nun schon stundenlang gegoogelt und nichts passendes gefunden, bzw. die Hälfte auch offen gesagt nicht verstanden…

Also, die Fragestellung:
Es gibt ein Tabellenblatt mit Unterrichtsformen und den dazugehörigen Preisen, z.B. A1 = E45 und B1 = 85,00. Es gibt 20 entsprechende Bezeichnungen und Werte.
Dann gibt es ein weiteres Tabellenblatt, in dem die Zahlungspflichtigen aufgelistet sind. Dahinter habe ich nun Zellen angelegt, in denen eingetragen werden soll, welche Unterrichtsform(en) der jeweilige Zahlungspflichtige belegt hat. Derer gibt es z.Zt. 6 Stück, da ein Zahlungspflichtiger ja u.U. mehrere Unterrichtsformen belegt hat.
Rechts daneben in die Zelle möchte ich gerne eine Formel schreiben, die sich die Informationen automatisch aus den vorigen 6 Zellen und dem entsprechenden 2. Tabellenblatt holt.
Als Beispiel: bei Max Mustermann steht in der ersten Zelle E45 und in der 2. E30, die weiteren 4 sind leer, da er nur diese 2 Unterrichtsformen belegt hat.
Meine schlaue Formel weiß nun: Aha, E45=85 und E30=63 UND addiert diese Werte und wirft als Ergebnis 145 aus.
Ich habs mit SVERWEIS probiert, aber ich glaube das ist nicht der richtige Ansatz; wenn ich das richtig verstehe, kann SVERWEIS nur das jeweils erste Ergebnis ausgeben.
Ich hatte dann überlegt, für jede der 6 Zellen einen eigenen SVERWEIS zu schreiben und diese zu addieren; aus einem mir unerfindlichen Grund funktioniert das aber nicht. Als Ergebnis kommt immer #NV oder #WERT heraus.
Ist es problematisch, dass einige der Zellen freibleiben? Wie kann ich das umgehen?
Es funktioniert, wenn ich die SVERWEISE addiere und die Felder ausgefüllt sind, sobald aber eins der Felder leer ist, kommt #NV als Ergebnis heraus…

Vielen Dank im Voraus,
Leila

#2

Grüezi Leila

Meine schlaue Formel weiß nun: Aha, E45=85 und E30=63 UND
addiert diese Werte und wirft als Ergebnis 145 aus.

Ich habs mit SVERWEIS probiert, aber ich glaube das ist nicht
der richtige Ansatz; wenn ich das richtig verstehe, kann
SVERWEIS nur das jeweils erste Ergebnis ausgeben.

Ich hatte dann überlegt, für jede der 6 Zellen einen eigenen
SVERWEIS zu schreiben und diese zu addieren; aus einem mir
unerfindlichen Grund funktioniert das aber nicht. Als Ergebnis
kommt immer #NV oder #WERT heraus.

Es funktioniert, wenn ich die SVERWEISE addiere und die
Felder ausgefüllt sind, sobald aber eins der Felder leer ist,
kommt #NV als Ergebnis heraus…

Lange Beschreibung - kurze Nachfrage:

Wie lautet denn dein SVERWEIS()im Moment?

Vermutlich musst Du nur den #NV abfangen und stattdessen „“ oder 0 in die Zellen ausgeben lassen.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#3

Das mit dem „#NV abfangen“ habe ich nun schon öfter gelesen, habe aber keine Ahnung, wie das gemeint ist…

Meine Formel sieht z.Zt. so aus (für 2 Zellen, da ich zu faul war, das für alle 6 zu basteln wenn es eh nicht funktioniert :wink:):

=(SVERWEIS(K129;Tabelle2.A1:B21;2;0))+(SVERWEIS(L129;Tabelle2.A1:B21;2;0))

Kann man da noch eine Wenn-Formel reinbasteln? Wenn Zellinhalt gleich „“ dann Ergebnis für die Zelle = 0? Oder so ähnlich?
Ich habe das versucht, aber bekomme es von der Syntax her nicht hin…

Dankeschön schonmal soweit!

#4

Grüezi Leila

Das mit dem „#NV abfangen“ habe ich nun schon öfter gelesen,
habe aber keine Ahnung, wie das gemeint ist…

Wie ich sehe, ist das hier auch garn nicht notwendig

Meine Formel sieht z.Zt. so aus (für 2 Zellen, da ich zu faul
war, das für alle 6 zu basteln wenn es eh nicht funktioniert
:wink:):

=(SVERWEIS(K129;Tabelle2.A1:B21;2;0))+(SVERWEIS(L129;Tabelle2.
A1:B21;2;0))

Kann man da noch eine Wenn-Formel reinbasteln? Wenn
Zellinhalt gleich „“ dann Ergebnis für die Zelle = 0? Oder so
ähnlich?

Genau so ist das:

=WENN(K129="";0;(SVERWEIS(K129;Tabelle2.$A$1:blush:B$21;2;0)))
+WENN(L129="";0;(SVERWEIS(L129;Tabelle2.$A$1:blush:B$21;2;0)))

BTW:
Warum hast Du zwischen dem Namen des Tabellenblattes und der Adresse einen Punkt und kein Ausrufezeichen?

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#5

Halle Leila,

Das mit dem „#NV abfangen“ habe ich nun schon öfter gelesen,
habe aber keine Ahnung, wie das gemeint ist…

Auswertung von ISTNV(,) oder ISTFEHLER(…)

=(SVERWEIS(K129;Tabelle2.A1:B21;2;0))+(SVERWEIS(L129;Tabelle2.
A1:B21;2;0))

Kann man da noch eine Wenn-Formel reinbasteln? Wenn
Zellinhalt gleich „“ dann Ergebnis für die Zelle = 0? Oder so
ähnlich?

Formeln manuell für die Anzeige umgebrochen, in Excel wieder zusammenfügen. Ab XL2007 wird die obere Formel kürzer.

=WENN(ISTNV(SVERWEIS(K129;Tabelle2!$A$1:blush:B$21;2;0));0;SVERWEIS(K129;
Tabelle2!$A$1:blush:B$21;2;0))+WENN(ISTNV(SVERWEIS(L129;Tabelle2!$A$1:blush:B$21
;2;0));0;SVERWEIS(L129;Tabelle2!$A$1:blush:B$21;2;0))

=WENN(K130="";0;SVERWEIS(K130;Tabelle2!$A$1:blush:B$21;2;0))+WENN(K130="";
0;SVERWEIS(L130;Tabelle2!$A$1:blush:B$21;2;0))

Gruß
Reinhard

#6

Hallo,

dann frag doch ohne SVERWEIS() gleich das Ergebnis ab!
=SUMMENPRODUKT((Tabelle2!$B$1:blush:B$21)*((Tabelle2!$A$1:blush:A$21=K129)+(Tabelle2!$A$1:blush:A$21=L129)+(Tabelle2!$A$1:blush:A$21=M129)))

War auch zu faul und hab nur 3 Zellen eingebaut! :smile:

VG René

#7

… das liegt wohl daran, dass ich OpenOffice benutze? Weiß nicht, ehrlich gesagt.

Ich probiere die hier genannten Lösungsmöglichkeiten mal aus, melde mich dann! :wink:
Klingt zumindest sehr vielversprechend…

LG

#8

Grüezi Leila

… das liegt wohl daran, dass ich OpenOffice benutze?

Das ist durchaus denkbar und daher eine wichtige Information, die Du bei Anfragen unbedingt mitliefern solltest.

Siehe dazu auch die Brettbeschreibung:

http://www.wer-weiss-was.de/app/service/board_navi?T…

Ich probiere die hier genannten Lösungsmöglichkeiten mal aus,
melde mich dann! :wink:

Wir sind gespannt :smile:

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#9

Also, habe jetzt die Wenn-Dann-Sonst-SVERWEIS-Formel genommen, funktioniert gut:

=WENN(H4="";0;(SVERWEIS(H4;$‚Liste Schulgeldbeiträge‘.$C$2:blush:D$40;2;0)))+WENN(I4="";0;(SVERWEIS(I4;$‚Liste Schulgeldbeiträge‘.$C$2:blush:D$40;2;0)))+WENN(J4="";0;(SVERWEIS(J4;$‚Liste Schulgeldbeiträge‘.$C$2:blush:D$40;2;0)))+WENN(K4="";0;(SVERWEIS(K4;$‚Liste Schulgeldbeiträge‘.$C$2:blush:D$40;2;0)))+WENN(L4="";0;(SVERWEIS(L4;$‚Liste Schulgeldbeiträge‘.$C$2:blush:D$40;2;0)))+WENN(M4="";0;(SVERWEIS(M4;$‚Liste Schulgeldbeiträge‘.$C$2:blush:D$40;2;0)))+N4

Zu dem Vorschlag von Silex
(„dann frag doch ohne SVERWEIS() gleich das Ergebnis ab!
=SUMMENPRODUKT((Tabelle2!$B$1:blush:B$21)*((Tabelle2!$A$1:blush:A$21=K129)+(Tabelle2!$A$1:blush:A$21=L129)+(Tabelle2!$A$1:blush:A$21=M129)))“)
habe ich noch eine Frage:
Es geht ja darum, dass ich in maximal 6 Zellen (plus eine zur freien Eingabe) Informationen stehen habe, welches Unterrichtsverhältnis ein Schüler belegt hat. Diese Formel weiß doch gar nicht, dass es um diese 6 Felder geht, oder? Sorry, wenn ich mich so blöd ausdrücke, es ist für mich noch recht kompliziert, dahinterzublicken…

Deine Lösung, Reinhard, ist für mich daher auch logisch noch nicht nachvollziehbar, aber das liegt daran, dass ich mit den genannten Formeln noch nie gearbeitet habe. Kommt bestimmt noch…

Aber jetzt funktioniert es ja. Das hat mich gestern Abend zu Freudensprüngen animiert :wink:.

Jetzt stehe ich vor dem nächsten Schritt, wo ich absolut leider noch keinen Lösungsansatz für weiß:
Bislang gebe ich in die 6 Zellen die Informationen manuell ein. Schön wäre es natürlich, wenn ich da eine Formel hinterlegen könnte, so dass die Informationen automatisch aus einer anderen Tabelle geholt werden können. Dafür würde sich ja unsere Gesamtliste anbieten, wo einfach alle Daten zu jedem Schüler erfasst sind. Das Problem dabei ist, dass in der Schülergesamtliste jedes Unterrichtsverhältnis als eigener Datensatz erfasst ist, d.h. dass ein Schüler z.B. 3x erfasst ist, weil er 3 Fächer belegt hat. In der Liste mit Zahlungspflichtigen allerdings sind es weit weniger, da jeder nur einmal erfasst ist.

Gibt es eine Möglichkeit, wenn ich beispielsweise den Zahlungspflichtigen laufende Nummern vergebe, und somit dann jeden Schüler eindeutig einem Zahlungspflichtigen zuordne, dass ich dann die Informationen automatisch holen lasse?
Eine weitere Schwierigkeit, könnte ich mir denken, ist, dass in der Schülergesamtliste die verschiedenen Unterrichtsverhältnisse untereinander aufgeführt sind, während in der Liste mit Zahlungspflichtigen wie gesagt diese 6 Zellen nebeneinander angeordnet sind.
Das kommt mir z.Zt. unmöglich vor… aber es MUSS ja irgendwie gehen, oder?
Hat da zufällig einer eine Idee?

Liebe Grüße,
Leila

#10

Also meine erste Idee zu dem „neuen“ Problem war jetzt folgende:
ich habe von der Schülergesamtliste eine Kopie als weiteres Tabellenblatt (mit Verknüpfung) zu meiner Liste mit Zahlungspflichtigen zugefügt. Ob das nötig war, weiß ich nicht, aber kam mir irgendwie vernünftig vor :wink:.

Dann habe ich versucht, dort per SVERWEIS nach dem Namen des/der Zahlungspflichtigen suchen zu lassen und entsprechend das Unterrichtsverhältnis als Ergebnis ausgeben zu lassen. Aber das funktioniert nicht!

Die Formel sieht so aus:
=SVERWEIS(B4;Tabelle3.$Y$2:Y501;19;0)
Als Ergebnis kommt „Error 502“ heraus. Müsste das nicht so funktionieren?? Die Formel bedeutet doch eigentlich: nimm den Namen aus Zelle B4 und suche den entsprechenden in Tabelle 3 Spalte Y; als Ergebnis gibst du bitte den entsprechenden Wert aus Spalte 19 (also S) aus. Oder habe ich da nen Denkfehler drin?

#11

=SVERWEIS(B4;Tabelle3.$Y$2:Y501;19;0)

Als Ergebnis kommt „Error 502“ heraus. Müsste das nicht so
funktionieren?? Die Formel bedeutet doch eigentlich: nimm den
Namen aus Zelle B4 und suche den entsprechenden in Tabelle 3
Spalte Y; als Ergebnis gibst du bitte den entsprechenden Wert
aus Spalte 19 (also S) aus. Oder habe ich da nen Denkfehler
drin?

Hallo Leila,

du hast da schon einen Denkfehler drin.

Für Sverweis gibst du als Suchbereich $Y$2:Y501 ein, also eine Spalte, bei Treffer willst du dann aus dem einspaltigen Bereich die 19te Spalte, kann nicht klappen.

Also müßte der Suchbereich so lauten:
$Y$2:AZ501

Allerdings ist da dann die 19te Spalte nicht S.

Gruß
Reinhard

#12

Ok, verstehe… zumindest teilweise :wink:.

Also muss sich die Ergebnisspalte (die durch den Index angegeben wird) innerhalb der Suchmatrix befinden? Logisch eigentlich, na sicher…

Ich habe allerdings grad Schwierigkeiten damit, den Index festzulegen. Und zwar ist das so: in der Liste mit Zahlungspflichtigen steht in B4 der Nachname. In der Gesamtliste taucht dieser Nachname öfters auf, abgesehen von Ausnahmefällen mindestens 2x: als Nachname des Schülers und als Nachnahme des Zahlungspflichtigen.
Zur Zeit sind das Spalte D und Spalte Z (ändert sich grad ab und an, weil ich noch mit Nummerierung rumprobiere :wink:).
Ich könnte also doch als Suchmatrix die ganze Tabelle angeben, oder? Die Daten, die ausgegeben werden sollen, stehen in Spalte T. Welchen Index hat die Spalte dann? Wieso ist es nicht möglich, einfach die entsprechende Spalte anzugeben?

#13

Ich könnte also doch als Suchmatrix die ganze Tabelle
angeben, oder? Die Daten, die ausgegeben werden sollen, stehen
in Spalte T. Welchen Index hat die Spalte dann? Wieso ist es
nicht möglich, einfach die entsprechende Spalte anzugeben?

Hallo Leila,

die Suchmatrix kannste nicht frei wählen, die erste Spalte der Suchmatrix muß die zu durchsuchende Liste sein. Die Spaltennummer der Spalte wo der Svwerweis Rückgabewertes muß relativ zur ersten Spalte der Suchmatrix erfolgen.

Bei größeren Suchmatrixen und/oder wenn sie rechts von A-Z sind, schreib in eine Zelle der ersten Spalte der Suchmatrix
=Spalte()
Genauso in eine Zelle der anderen Spalte.
Dann kannste ausrechnen was da die Differenz ist, dazu addierst du eine 1.

Aternativ, schreib in eine Zelle der ersten Spalte der Suchmatrix eine 1
Das füllst du nach rechts aus.

Gruß
Reinhard

#14

Grüezi Leila

Also muss sich die Ergebnisspalte (die durch den Index
angegeben wird) innerhalb der Suchmatrix befinden? Logisch
eigentlich, na sicher…

Ja, logisch - SVERWEIS() erwartet laut Online-Hilfe einen festen Bereich, sucht in dessen erster Spalte und liefert dann einen Wert aus der Rückgabespalte. Dieser Index wird innerhalb des Bereiches gezählt, also ab der ersten Spalte.

Ich habe allerdings grad Schwierigkeiten damit, den Index
festzulegen.

Ich könnte also doch als Suchmatrix die ganze Tabelle
angeben, oder? Die Daten, die ausgegeben werden sollen, stehen
in Spalte T. Welchen Index hat die Spalte dann?

Wenn die erste Spalte der im SVERWEIS() angegebenen Matrix Spalte D ist einfach von da an weiterzählen - Spalte T wäre dann Index 16

Wieso ist es :nicht möglich, einfach die entsprechende Spalte
anzugeben?

Weil SVERWEIS() nicht darauf ausgelegt ist, ganz einfach.

Aber Du kannst eine andere Formel-Kombination verwenden - lass uns folgende Annahmen treffen:

B4: Dein gesuchten Name
Spalte D: Liste mit den Namen
Spalte Z: Werte die Du haben möchtest

Die Formel lautet dann:

=INDEX($T:blush:T;VERGLEICH(B4;$D:blush:D;0))

Gesucht wird in Spalte D der Wert aus B4, geliefert wird aus Spalte T der Wert in derselben Zeile wir der Name in Spalte D gefunden wird.
Allgemein ausgedrückt also wie folgt:

=INDEX( Spalte der Werte ;VERGLEICH( Gesuchter Begriff ; Spalte in der gesucht wird ;0))

Damit bist Du dann sehr flexibel und die Angaben in der Formel sagen auch immer direkt wo gesucht wird.

#15

Vielen Dank, so langsam ergibt das alles einen Sinn :wink:.

Ich habe am Wochenende weiter getüftelt. Mein Ansatz bislang ist der, dass sich die erste der 6 Spalten per SVERWEIS den je ersten Datensatz eines Zahlungspflichtigen holt

(Formel:
=SVERWEIS(B4;$Tabelle3.A2:AA551;20;0))

Das klappt auch wunderbar.
Jetzt allerdings komme und komme ich einfach nicht weiter. Jeder Zahlungspflichtige hat jetzt eine eindeutige ID, und jeder Schülerdatensatz, der zu diesem dazugehört, hat die gleiche Nummer. Dadurch funktioniert dieser SVERWEIS in der ersten Zelle ja auch gut. Nun möchte ich allerdings für die zweite, rechts daneben liegende Zelle eine Formel finden, die ungefähr besagt:
Wenn der WVERWEIS zu B4 = B4 ist (ist das logisch so richtig?? Also in B4 steht die zugeordnete ID-Nummer; wenn ich für den WVERWEIS die Zeile darunter angebe, müsste das doch rauskommen?), dann SVERWEIS zum Unterrichtsverhältnis der Zeile.
Aber das bekomme ich einfach nicht hin, weil ich nicht weiß, wie ich diesen Befehl „die Zeile darunter“ angeben kann.
Im Grunde ist der WVERWEIS schon Quatsch, weil ich da ja einen Zeilenindex angeben muss, der sich dann aber für jeden weiteren Datensatz ändert…
Ich habe schon alles mögliche durchprobiert, wo auch teilweise sogar das richtige Ergebnis herauskommt; wenn ich das dann aber auf die anderen Datensätze übertrage, kommt wieder irgendein Blödsinn heraus.
Ich hoffe mein Problem ist einigermaßen nachvollziehbar, ich selbst finde es recht kompliziert zu erklären…

LG, Leila

#16

Wenn der WVERWEIS zu B4 = B4 ist (ist das logisch so
richtig?? Also in B4 steht die zugeordnete ID-Nummer; wenn ich
für den WVERWEIS die Zeile darunter angebe, müsste das doch
rauskommen?), dann SVERWEIS zum Unterrichtsverhältnis der
Zeile.

Aber das bekomme ich einfach nicht hin, weil ich nicht weiß,
wie ich diesen Befehl „die Zeile darunter“ angeben kann.

Im Grunde ist der WVERWEIS schon Quatsch, weil ich da ja
einen Zeilenindex angeben muss, der sich dann aber für jeden
weiteren Datensatz ändert…

Hallo Leila,

lade mal mit file-upload , s. FAQ:2606 eine Beispielmappe hoch.
Kannste ja fix anonymisieren. Schreibst als obersten Namen Name1 in die Namensspalte, das Gleiche für Vorname1 Ort11 usw.
Dann gehe wieder auf die Zelle zurück und gehe in die rechte untere Ecke bis da ein kleines Kreuz erscheint, das ziehst du nach unten.

Doppelte namen mußte dann selbst eintragen, aber da langen ja einige zweifach dreifach namen, mal hintereinander, mal in den Zeilen verteilt.

Telefonnummern, da knallste überall 4711 rein.

Und je nachdem, reichen da 20-50 Datensätze.

Mir am liebsten, du bastelst zwei Blätter.
Das erste Blatt ist dann der IST-Zustand, im zweiten Blatt machste eine Kopie des ersten Blattes und trägst manuell ein, wie es denn nachher aussehen soll mit entsprechenden Formeln in Blatt1.

Gruß
Reinhard

#17

Grad gelesen - werd mich mal dransetzen die Tage!