Thema: Abfragen und Neuauflisten

SVERWEIS(A3;$Kontoeingang.$A$1:$B$65536;2;0)

Hallo,
ich habe vergessen wie man nach einer Abfrage angibt, dass beim mehrmaligen Auffinden der in A3 befindlichen Angabe die Ergebnisse untereinander aufgelistet werden sollen.

Also: in einer Spalte Liste verschiedener Obstarten gibt es mehrmals das Wort "Apfel", das ich in A3 als Referenz hinterlegt habe. Alle Angaben aus den daneben befindlichen Spalten (z.B. grün, Granny Smith, 0,50 EUR oder rot, Boskop, 1 EUR) sollen in einem anderen Tabellenblatt "Äpfel" untereinander aufgelistet werden.

Also aus :
Obst-Tabelle
Apfel, grün, Granny Smith, 0,50 EUR
Birnen, gelb, Williams, 0,80 EUR
Kirschen, rot, Sweets, 1,20 EUR
Apfel, grün, Granny Smith, 0,50 EUR oder rot, Boskop, 1 EUR

wird:
Äpfel-Tabelle
Apfel, grün, Granny Smith, 0,50 EUR
Apfel, grün, Granny Smith, 0,50 EUR oder rot, Boskop, 1 EUR

Ich meine, dass die obige Angabe (...;2;0) irgendwie solch eine "Ausfilterungs- und Neuauflistungsaktion" steuert...?

Vielen Dank.
anja

2

Re: Abfragen und Neuauflisten

Hallo Anja,

Die Funktion =Sverweis() ist für diesen Zweck nicht geeignet. Genaugenommen ist keine Funktion hierfür geeignet, da jede Funktion ein Ergebnis für eine bestimmte Zelle berechnet und nicht etwas auflisten kann. Durch kopieren der Formel ändert sich dieses Prinzip auch nicht...

Eigentlich wäre das ein Fall für eine kleine Access-Datenbank (wirklich einfacher und eleganter für sowas). Aber auch in Excel (wenn's denn sein muss...) geht's - nämlich mit dem FILTER.

Kopiere Deine Tabelle und füge in einem neuen Tabellenblatt die Verknüpfungen ein (Inhalte einfügen bzw. Pfeil bei Einfügen-Symbol). So werden Änderungen in der Original-Tabelle auch in den verknüpften Tabellen wirksam. In dieser neuen Tabelle setzt Du nun den Filter ein. Nun kannst Du z.B. nur die Zeilen anzeigen lassen, die Apfel in der ersten Spalte enthalten.

Noch ein allgemeiner Tipp: in der von Dir verwendeten Formel schreibst Du $A$1:$B$65536. Es reicht in so einem Fall die Angabe $A:$B.

LG, Oliver

[color=#555555]...und hier noch Olivers Kochtipp: wenn Du die Zahnstocher mitessen kannst, hast Du die Rouladen zu lange gekocht!!! :-)[/color]

3

Re: Abfragen und Neuauflisten

Vielen Lieben Dank,
d.h. ich muss Access wieder unter die Lupe nehmen ...    roll
Leider habe ich in einer Aufräumaktion meine EDCL-Unterlagen weggeschmissen, was sich nun als weniger klug herausgestellt hat. Wir werden uns hier wahrscheinlich noch öfters unterhalten... ;-)

Kannst du mir trotzdem nochmal sagen, für was die (...;2;0) in der Formel unten steht?
Ich habe die oben zitierte Formel aus einem eurer Downloads (Sverweis-Mahnung) rauskopiert:

=(WENN(ISTFEHLER(SVERWEIS(A2;$Kontoeingang.$A$1:$B$65536;2;0));"";SVERWEIS(A2;$Kontoeingang.$A$1:$B$65536;2;0)))

Übrigens, ich finde es ganz toll, dass ihr Übungsdateien zu Verfügung stellt und gleich noch die Lösungen in einem gesonderten Blatt mitgibt. Dann hat man auch 1 Jahr nach dem Kurs noch eine Chance sich wieder an das zu erinnern, was man während der Prüfung eignentlich gewußt hat...

LG
Anja

Zuletzt bearbeitet von anja (24-04-2009 10:56:37)

4

Re: Abfragen und Neuauflisten

Die 2 bedeutet, dass der Wert zurückgegeben wird, der in der 2ten Spalte steht.
Die 0 bedeutet, das die SVERWEIS-Funktion genau nach dem im ersten Argument angegebenem Wert sucht.
Das könnte auch anders sein: stell Dir folgende Tabelle vor:

       A           B            C
1    Ab      Provision
2         0       10%
3    1000       15%
4  10000       20%
5
6   Name   Umsatz   Provision
7   Oliver    890        =SVERWEIS(B7;$A$2:$B$4;2;1)
8    Anja    1475       =SVERWEIS(B8;$A$2:$B$4;2;1)

Wird 1 angegeben, sucht die SVERWEIS Funktion auch zwischen den in der ersten Tabelle angegebenen Werten. 1475 wird dem nächst kleineren Wert zugewiesen und Du würdest 15% Provision bekommen. Mit 0 im letzten Argument würde Dein Umsatz in der Tabelle nicht gefunden werden und die Fehlermeldung #NV ("Nicht Vorhanden") erscheinen.

anja schrieb:

...toll, dass ihr Übungsdateien zu Verfügung stellt...

Danke, aber "ihr" ist leider nicht ganz richtig - ich mache das alleine hier... :-)

LG, Oliver

[color=#555555]...und hier noch Olivers Kochtipp: wenn Du die Zahnstocher mitessen kannst, hast Du die Rouladen zu lange gekocht!!! :-)[/color]

5

Re: Abfragen und Neuauflisten

cool
Danke!

Uhh, da fallen mir doch noch weitere Frage ein:
Wenn die Werte in Spalte A nicht wie in deinem Beispiel aufsteigend sortiert sind, muss ich dann auch den Wert 0 eingeben?
Oder heißt die Angabe 0 immer: "Suche genau den angegebenen Suchwert" ?

Ich habe auch Probleme nach einem "Buchstabenwert" zu suchen:
=SVERWEIS("A";E7:V9;D7:D17)

"A" soll in Spalte D gesucht werden und dann sollen alle Angaben aus E7:V9 zurückgegeben werden.
Als Ergebnis erhalte ich immer Err: 502, selbst wenn ich noch eine 0 angebe:

=SVERWEIS("A";E7:V9;D7:D17;0)

sad

Zuletzt bearbeitet von anja (11-05-2009 12:41:22)

6

Re: Abfragen und Neuauflisten

Heureka! Ich muss auch die Spalte mit dem Suchwert berücksichtigen: also anstatt E7 muss in der Matrix-Angabe D7 eingepflegt werden:

=SVERWEIS("A";D7:V9;2;0)

Meine vorläufig "geschusterte" Lösung zum Problem

=SVERWEIS("A";$E7:$V9;2;0), dann in der nächsten Spalte
=SVERWEIS("A";$E7:$V9;3;0)
=SVERWEIS("A";$E7:$V9;4;0)
...
=SVERWEIS("A";$E7:$V9;10;0)

7

Re: Abfragen und Neuauflisten

Ich glaube, Du hast es jetzt eh richtig gemeint, aber wieder $E$7:$V$9 statt $D$7:$V$9 geschrieben (war wohl ein "copy-paste-error").
Also richtig:
=SVERWEIS("A";$D$7:$V$9;2;0), dann in der nächsten Spalte
=SVERWEIS("A";$D$7:$V$9;3;0)
=SVERWEIS("A";$D$7:$V$9;4;0)
...

Zu Deinem vorletzten Beitrag: Ja, 0 heißt "Suche genau den angegebenen Suchwert"! In diesem Fall muss die Tabelle nicht sortiert sein.
Bei 1 muss die Tabelle aufsteigend sortiert sein.

Also eh alles richtig selbst beantwortet! :-)

[color=#555555]...und hier noch Olivers Kochtipp: wenn Du die Zahnstocher mitessen kannst, hast Du die Rouladen zu lange gekocht!!! :-)[/color]

8

Re: Abfragen und Neuauflisten

Hallo,
hier nun eine weiter Knobelfrage zum Thema "Suche genau den angegebenen Suchwert!"

Wenn ich =VERWEIS(1;$E8:$E28;$D8:$D28) in einer Zelle eingebe,
weil ich im Bereich $E8:$E28 den Wert 1 suche
und beim Auffinden, dann die Angabe aus $D8:$D28 wiedergeben will,
funktioniert das, solange die Werte in Bereich $E8:$E28 sortiert sind,

gebe ich nun =VERWEIS(1;$E8:$E28;$D8:$D28;0) an,
damit genau nach 1 gesucht wird erhalte ich Err. 504 ...   mad

9

Re: Abfragen und Neuauflisten

Geht so leider nicht!
Die Spalte, aus der ein Wert zurückgegeben wird, muss hinter der Suchspalte (Funktions-Argument 2) liegen. Ist das nicht der Fall, musst Du hinten noch eine Spalte dranhängen (z.B. F) in der die Werte aus der Spalte D erscheinen (in F2 z.B. =D2, in F3 also =D3, etc.). Argument 3 der Funktion ist nie ein Zellbereich, sondern immer ein Spaltenindex (2 oder 3 oder 4...).

[color=#555555]...und hier noch Olivers Kochtipp: wenn Du die Zahnstocher mitessen kannst, hast Du die Rouladen zu lange gekocht!!! :-)[/color]

10

Re: Abfragen und Neuauflisten

Ach so!
Vielen Dank.

11

Re: Abfragen und Neuauflisten

Hat funktioniert!    smile
Vielen Dank.

12

Re: Abfragen und Neuauflisten

hallo,
ich stehe vor einem problem auf dessen lösung ich nicht so recht draufkomme.

ich habe eine tabelle mit verschiedenen produkten
Spalte A
und deren Einheitspreis
Spalte B

jetzt möchte ich in einer weiteren tabelle nur das produkt eingeben und den dazugehörigen einheitspreis erhalten.
es funktioniert aber leider nur wenn ich produkte mit den selben anfangsbuchstaben habe.
HEA
HEB usw.
sobald ich aber ein anderes produkt eingebe IP oder sowas, funktioniert der verweis nicht mehr.
kann mir wer erklären wieso???

lg claudia

13

Re: Abfragen und Neuauflisten

Hallo Claudia,

Deine Frage ist eigentlich ein neues Thema (Thread), weil keine Antwort auf Anjas Frage. So kriegt jetzt Anja eine Benachrichtigung, wenn ich antworte und nicht Du - außerdem findet das später niemand mit einem ähnlichen Problem. Egal.

Als viertes Argument solltest Du in Deinem Fall eine 0 setzen, also =SVERWEIS(Suchkriterium, Matrix, Spaltenindex, 0). Nicht vergessen, die Matrix = absoluter Bezug.

LG, Oliver

[color=#555555]...und hier noch Olivers Kochtipp: wenn Du die Zahnstocher mitessen kannst, hast Du die Rouladen zu lange gekocht!!! :-)[/color]