Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
628to632
628to632
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

SVERWEIS

Forumthread: SVERWEIS

SVERWEIS
23.06.2005 16:08:35
Volker
Hallo Experten,
habe wieder mal ein neues Problem:
Ich möchte, dass SVERWEIS aufbauen, aber es müssen 2 Suchkriterien in einer Zeile der Matrix übereinstimmen.
Wer kann mir helfen oder kennt ein andere Möglichkeit.
Danke
Volker
Anzeige

22
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: SVERWEIS
23.06.2005 16:39:20
Volker
Danke, aber irgendetwas ist immer noch falsch. Habe Formel für meine Zwecke umgeschrieben und die sieht jetzt so aus:
=INDEX(AUSZUG.xls!$I:$I;VERGLEICH(I7&P9;AUSZUG.xls!$D:$D&AUSZUG.xls!$B:$B;0))
Ergebnis #NV
Ist die Formel so richtig? I7 ist Suchkriterium als Datum und P9 ist Artikelnummer
Anzeige
AW: SVERWEIS
23.06.2005 16:51:59
Volker
Bitte um schnellstmögliche Antwort auf mein Problem.
Danke
Volker
AW: SVERWEIS
23.06.2005 18:48:10
MartinFl
Hallo Volker,
in der Antwort von Mac4 wird auf ein schönes Matrix-Beispiel verwiesen. Die geschweifte Klammer nicht per Hand dazufügen, sondern die Formel am Ende einfach mit Strg + Shift + Enter drücken.
Klappt bei mir hervorragend.
Gruß vom Martin
Anzeige
AW: SVERWEIS
23.06.2005 23:30:31
MichaV
Hallo
hi Martin, ne feine Sache, diese Matrixformeln, was?
Aber warum hast Du die Frage offen gelassen?
Gruß- Micha
AW: SVERWEIS
24.06.2005 00:11:48
MartinFl
Hallo Micha,
ja, wenn man sich die Funktionen mit SVERWEIS, INDEX, INDIREKT und {Matrizen} mal richtig zu Gemüte führt, sind dies wunderbare Excel-Werkzeuge.
Aber nicht nur die Standard-Varianten, sondern mit Betonung auf die Möglichkeiten der inneren Verschachtelungen.
Die letzten Tage ging´s damit richtig rund mit diesen Funktionen - wie Sie sicherlich mitbekommen haben.
Hauptsache: Fortschritt!
Zum "Frage noch offen":
Ich dachte eigentlich, daß diese Option solange gedrückt werden soll, bis der Beitrags-Starter diesen als erledigt erklärt. Und die Beiträge so nicht aus dem Forum verschwinden - unbeantwortet.
Ist wohl falsch angenommen.
Gruß vom Martin
Anzeige
AW: SVERWEIS
24.06.2005 10:06:37
MichaV
Hi Martin,
leider ist es nicht die Regel, daß sich die Frager nach erfolgreicher Beantwortung den Beitrag als Erledigt erklären. :o(
Gruß- Micha
AW: SVERWEIS
24.06.2005 08:14:50
Volker
hallo Leute,
Hilfe, ich weiss immer noch nicht warum die Formel falsch ist. Trotz vieler Bemühungen meinerseits habe ich leider noch nicht die Lösung gefunden. Ist zwar nett, dass sich alle daran auslassen am Thema, aber ich benötige Lösungen :-(
Danke, für denjenigen der mir jetzt helfen kann.
Volker
Anzeige
AW: SVERWEIS
24.06.2005 09:56:46
MartinFl
Hallo Volker,
wenn Du aus anderen Dateien Daten benötigst, kommt es auf die genau Bezugsschreibweise an. Am besten mit der Maus direkt auf die Zelle in Auszug.xls klicken, oder die gewünschte Matrix markieren.
Mit folgender Formel klappt es problemlos, aber nur wenn Du die Zelle mit Strg+Shift+Enter gemeinsam abschließt, sonst erscheint #WERT.
=INDEX([AUSZUG.xls]Tabelle1!$A1:$A1000;VERGLEICH(I7&P9;[AUSZUG.xls]Tabelle1!$D1:$D1000&[AUSZUG.xls]Tabelle1!$E1:$E1000;0))
Gruß vom Martin
Anzeige
AW: SVERWEIS
24.06.2005 10:10:53
MartinFl
Hallo nochmal,
statt $E1:$E1000 soll es bei Dir heißen $B1:$B1000, ist halt eine Anordnungssache.
Gruß vom Martin
AW: SVERWEIS
24.06.2005 10:25:30
Volker
Hallo Martin,
Danke für deine Hilfe, entscheidender Tipp war Strg+Shift+Enter-Tasten,aber wieso muss man das machen? Eine andere Frage dazu, wenn I7 als Datum nicht gefunden wird( weil an dem Tag keine Veränderungen waren, werden leider in der anderen Datei dann nicht aufgeführt bzw weitergeführt), soll er mir den letzten Wert wiedergeben den er findet( kann unter Umständen Wochen vorher sein). Kann ich das noch irgendwie einbauen?
Das wäre klasse, wenn du mir dabei auch helfen könntest.
Volker
Anzeige
AW: SVERWEIS
24.06.2005 12:27:04
MartinFl
Hallo Volker,
in
Archivdatei (hier ist keine Antwort möglich):
https://www.herber.de/forum/archiv/624to628/t626754.htm
hatte ich vor kurzem genauso vor dieser Frage gestanden. Darin ist ein Link zu
Matrix Station 1 - Keine Angst , Grundlegene Technik
zu sehen.
MichaV, Martin Beck und Hajo_Zi wiesen mich in die Matrix-Welt ein, und schwups, Problem nicht nur gelöst, sondern viel wichtiger: auch verstanden.
Oder direkt nachschlagen unter (drin gibt es fünf Stationen, Matrix-Kurs):
http://www.online-excel.de/excel/singsel.php?f=26
Zur Datum-Abfrage:
Die Beitragsleser können sich wahrscheinlich nur schwer Deine Tabellenstruktur vorstellen – zumindest mir geht es so.
Bitte genauer erläutern, oder einfach hochladen (Pfad nicht zu tief, kleiner 300kB, und .xls-Datei sind Bedingungen für das Hochladen).
Gruß vom Martin
Gruß hier auch mal an MichaV - was macht die Kunst?
Excel hatte mir bereits vorher schon Spaß gemacht. Aber seit diesen Tagen geht richtig die Post ab!
Anzeige
AW: SVERWEIS
24.06.2005 15:21:31
Volker
hallo Martin und andere,
da sich nur Martin gemeldet hat, hat man meine Ausführungen wohl nicht nachvollziehen können deshalb hier noch ein Beispiel zum besseren Verständnis. Vielleicht könnt ihr mir jetzt helfen.
Danke vorab
https://www.herber.de/bbs/user/24168.xls
Anzeige
AW: SVERWEIS
24.06.2005 17:58:45
MartinFl
Hallo Volker,
kopiere doch mal bitte folgende Formel so wie sie ist (aber ohne ) in Deinem hochgeladenen Beispiel in die Zelle H9 der Tabelle 1. Dann mit der Maus nach unten bis H14 kopieren - keine Angst, relative und absolute Bezüge sind bereits berücksichtigt.
~f~=SUMMENPRODUKT((Tabelle2!$A$2:$A$11=Tabelle1!$C$7)*(Tabelle2!$B$2:$B$11=Tabelle1!B9)*(Tabelle2!$E$2:$E$11&gt0))
Aber diesesmal nur mit Enter, nicht Strg+Shift+Enter.
Diese Formel liest aus, wieviel vorhandene Datenübereinstimmungen existieren.
Man sieht, daß überall wo #NV auftauchte es also keine Eintragung zu diesem Datum existiert.
Nun ließe sich das Ganze mit WENN-Abfragen bis zu 7 Tage in die Vergangenheit des Suchkriterium-Datums nachschauen, aber nicht darüber hinaus, denn mit WENN kann man maximal 7 (ok, mit Sondervariante 14) verschachtelte Abfragen tätigen.
So, jetzt ist dies mal ein kleiner Entwicklungsschritt - aber vielleicht findet sich ja jemand, der damit wenigstens weiter kommt, und den passenden Schlüssel bis zur vollendeten Lösung findet.
Gruß vom Martin

Anzeige
AW: SVERWEIS
25.06.2005 00:48:18
MichaV
Hallo Volker,
in C9 diese MATRIXFormel:
{=WENN(ISTNV(VERGLEICH($C$7&B9;Tabelle2!A1:A1002&Tabelle2!B1:B1002;0));INDEX(Tabelle2!E1:E1002;MAX(N(Tabelle2!B1:B1002=B9)*(Tabelle2!A1:A1002&lt$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!E1:E1002;VERGLEICH($C$7&B9;Tabelle2!A1:A1002&Tabelle2!B1:B1002;0)))}
Die dann runterkopieren.
Gruß- Micha
PS: Rückmeldung wäre nett.
PPS: Formel ist aufgebaut auf Martins Formel aus Deiner Beispieltabelle.
Anzeige
AW: SVERWEIS
27.06.2005 15:29:12
Volker
Hallo Micha, hallo Martin,
vielen Dank für eure Bemühungen.
Micha, deine Lösungsweg habe ich versucht umzusetzen, aber leider wirft er mir nicht die richtigen Ergebnisse raus. Hast du die Formel in der Beispiel-Datei ausprobiert?
Gruss
Volker
AW: SVERWEIS
27.06.2005 16:18:50
MichaV
Hi Volker,
denkst Du, ich schreibe so eine Formel aus dem Hut ohne zu testen? So weit ist es noch nicht mit mir...
Hast Du an die Matrixformel gedacht? STRG+SHIFT+ENTER
Tabelle1
  
 ABCD
5  Artikelbestand 
6  per  
7  22.05.2005 
8 Artikel  
9 110,00 
10 27,00hier soll jetzt der Wert vom 20.05.05 mit 7 stehen
11 31,00 
12 42,00 
13 85,00 
14 97,00 
Formeln der Tabelle
C9 : {=WENN(ISTNV(VERGLEICH($C$7&B9;Tabelle2!A1:A1002&Tabelle2!B1:B1002;0));INDEX(Tabelle2!E1:E1002;MAX(N(Tabelle2!B1:B1002=B9)*(Tabelle2!A1:A1002<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!E1:E1002;VERGLEICH($C$7&B9;Tabelle2!A1:A1002&Tabelle2!B1:B1002;0)))}
C10 : {=WENN(ISTNV(VERGLEICH($C$7&B10;Tabelle2!A2:A1003&Tabelle2!B2:B1003;0));INDEX(Tabelle2!E2:E1003;MAX(N(Tabelle2!B2:B1003=B10)*(Tabelle2!A2:A1003<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!E2:E1003;VERGLEICH($C$7&B10;Tabelle2!A2:A1003&Tabelle2!B2:B1003;0)))}
C11 : {=WENN(ISTNV(VERGLEICH($C$7&B11;Tabelle2!A3:A1004&Tabelle2!B3:B1004;0));INDEX(Tabelle2!E3:E1004;MAX(N(Tabelle2!B3:B1004=B11)*(Tabelle2!A3:A1004<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!E3:E1004;VERGLEICH($C$7&B11;Tabelle2!A3:A1004&Tabelle2!B3:B1004;0)))}
C12 : {=WENN(ISTNV(VERGLEICH($C$7&B12;Tabelle2!A4:A1005&Tabelle2!B4:B1005;0));INDEX(Tabelle2!E4:E1005;MAX(N(Tabelle2!B4:B1005=B12)*(Tabelle2!A4:A1005<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!E4:E1005;VERGLEICH($C$7&B12;Tabelle2!A4:A1005&Tabelle2!B4:B1005;0)))}
C13 : {=WENN(ISTNV(VERGLEICH($C$7&B13;Tabelle2!A5:A1006&Tabelle2!B5:B1006;0));INDEX(Tabelle2!E5:E1006;MAX(N(Tabelle2!B5:B1006=B13)*(Tabelle2!A5:A1006<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!E5:E1006;VERGLEICH($C$7&B13;Tabelle2!A5:A1006&Tabelle2!B5:B1006;0)))}
C14 : {=WENN(ISTNV(VERGLEICH($C$7&B14;Tabelle2!A6:A1007&Tabelle2!B6:B1007;0));INDEX(Tabelle2!E6:E1007;MAX(N(Tabelle2!B6:B1007=B14)*(Tabelle2!A6:A1007<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!E6:E1007;VERGLEICH($C$7&B14;Tabelle2!A6:A1007&Tabelle2!B6:B1007;0)))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Tabelle2
  
 ABCDE
1DatumArtikelZugängeAbgängeBestand
219.05.20052325
319.05.20054231
420.05.20051205
520.05.20052507
620.05.20054112
721.05.20058326
821.05.20059542
922.05.200513010
1022.05.20058205
1122.05.20059407
 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: SVERWEIS
                                            

                    

                                                    27.06.2005 16:42:33
                                            

                                

                                            Volker
                                    

            

        

        

                            
Hallo Micha,

warum zeigt er bei Artikel 3 einen Bestand von 1 an?
Schön, dass du mir die Formel ins Netz gesetzt hast.Irgendwie habe ich diese Formel wohl falsch abgeschrieben, statt sie mir gleich zu kopieren.

Herzlichen Dank für deine Mithilfe, hätte ich wohl niemals alleine hingekriegt. Werde nun versuchen diese Formel zu verstehen  ;-)

Ihr Experten seid schon echt klasse.

Gruss Volker

                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: SVERWEIS
                                            

                    

                                                    27.06.2005 20:34:55
                                            

                                

                                            Hajo_Zi
                                    

            

        

        

                            
Hallo Volker,

Dein Beitrag liest so so als ob das Problem gelöst ist. Warum ist der Beitrag dann als offen gekennzeichnet.
Bitte keine Mail, Probleme sollten im Forum gelöst werden.
Microsoft MVP für Excel
Das Forum lebt auch von den Rückmeldungen.
Betriebssystem XP Home SP2 und Excel Version 2003 SP1.



                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: SVERWEIS
                                            

                    

                                                    27.06.2005 21:03:33
                                            

                                

                                            MichaV
                                    

            

        

        

                            
Hey Hajo,

weil da noch eine Frage drin versteckt war! 

"warum zeigt er bei Artikel 3 einen Bestand von 1 an?"

Ich krieg das heut noch raus (bisher keine Zeit) , dann ist die Sache erledigt ;o)

Gruß- Micha

                    

            



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: SVERWEIS
                                            

                    

                                                    28.06.2005 20:16:48
                                            

                                

                                            MichaV
                                    

            

        

        

                            
Hi Volker,

danke für den Hinweis. Die Formel war Murks, denn da fehlten einige $. Die hier passt besser:


Tabelle1
  
 ABCD
5  Artikelbestand 
6  per  
7  22.05.2005 
8 Artikel  
9 110,00 
10 27,00hier soll jetzt der Wert vom 20.05.05 mit 7 stehen
11 3kein Eintrag 
12 42,00 
13 85,00 
14 97,00 
Formeln der Tabelle
C9 : {=WENN(ISTNV(VERGLEICH(B9;Tabelle2!$B$1:$B$1002;0));"kein Eintrag";WENN(ISTNV(VERGLEICH($C$7&B9;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0));INDEX(Tabelle2!$E$1:$E$1002;MAX(N(Tabelle2!$B$1:$B$1002=B9)*(Tabelle2!$A$1:$A$1002<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!$E$1:$E$1002;VERGLEICH($C$7&B9;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0))))}
C10 : {=WENN(ISTNV(VERGLEICH(B10;Tabelle2!$B$1:$B$1002;0));"kein Eintrag";WENN(ISTNV(VERGLEICH($C$7&B10;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0));INDEX(Tabelle2!$E$1:$E$1002;MAX(N(Tabelle2!$B$1:$B$1002=B10)*(Tabelle2!$A$1:$A$1002<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!$E$1:$E$1002;VERGLEICH($C$7&B10;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0))))}
C11 : {=WENN(ISTNV(VERGLEICH(B11;Tabelle2!$B$1:$B$1002;0));"kein Eintrag";WENN(ISTNV(VERGLEICH($C$7&B11;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0));INDEX(Tabelle2!$E$1:$E$1002;MAX(N(Tabelle2!$B$1:$B$1002=B11)*(Tabelle2!$A$1:$A$1002<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!$E$1:$E$1002;VERGLEICH($C$7&B11;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0))))}
C12 : {=WENN(ISTNV(VERGLEICH(B12;Tabelle2!$B$1:$B$1002;0));"kein Eintrag";WENN(ISTNV(VERGLEICH($C$7&B12;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0));INDEX(Tabelle2!$E$1:$E$1002;MAX(N(Tabelle2!$B$1:$B$1002=B12)*(Tabelle2!$A$1:$A$1002<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!$E$1:$E$1002;VERGLEICH($C$7&B12;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0))))}
C13 : {=WENN(ISTNV(VERGLEICH(B13;Tabelle2!$B$1:$B$1002;0));"kein Eintrag";WENN(ISTNV(VERGLEICH($C$7&B13;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0));INDEX(Tabelle2!$E$1:$E$1002;MAX(N(Tabelle2!$B$1:$B$1002=B13)*(Tabelle2!$A$1:$A$1002<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!$E$1:$E$1002;VERGLEICH($C$7&B13;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0))))}
C14 : {=WENN(ISTNV(VERGLEICH(B14;Tabelle2!$B$1:$B$1002;0));"kein Eintrag";WENN(ISTNV(VERGLEICH($C$7&B14;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0));INDEX(Tabelle2!$E$1:$E$1002;MAX(N(Tabelle2!$B$1:$B$1002=B14)*(Tabelle2!$A$1:$A$1002<$C$7)*ZEILE($B$1:$B$1002)));INDEX(Tabelle2!$E$1:$E$1002;VERGLEICH($C$7&B14;Tabelle2!$A$1:$A$1002&Tabelle2!$B$1:$B$1002;0))))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Gruß- Micha!

                    

                                    


    
Anzeige

    

        
                    
            
                
    



                        



                    
                                

    
    

        

            
            

                                    

                                                    
                            AW: SVERWEIS
                                            

                    

                                                    25.06.2005 00:15:25
                                            

                                

                                            MichaV
                                    

            

        

        

                            
Hallo Martin,

Gruß zurück. So wie Dir gings mir im Frühjahr ;o)

Micha (nun wieder weniger Zeit für Excel :o)    )

                    

            



                
    ;

    
    
    
                    


    
    
    

    
    
    
            


    

        
        Beliebteste Forumthreads (12 Monate)
    

    
    

 

    
    
    

    
    

    

        

            

                
                    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
                
            

            

                

                
            

        

    




    
    

    
    
            
 

            
            

                
                
                
                


    
Anzeige

    

        
                    
            
                
    



                


    
Anzeige

    

        
                    
            
                
    



                

                
                
                
                


    
Anzeige