Excel / Apple Numbers: Wie suche ich nach Wert in anderem Dokument und gebe die Zeile daneben im ursprünglichen Dokument aus?
Einen schönen guten Morgen!
Wie kann ich folgendes in Excel / Apple Numbers umsetzen?
- In Dokument A habe ich eine Spalte 1 mit dem Wert einer Artikelnummer
- Diese Artikelnummer soll in Dokument B gesucht werden
- Der Wert neben der gefundenen Zeile in Dokument B soll in die Formel-Zeile in Dokument A kopiert werden
Das würde mir wahrscheinlich 10 Stunden Arbeit sparen, dementsprechend wäre ich sehr dankbar für einen netten Rat :).
Habe auch schon gegoogelt, aber ich weiß nicht, was das Zauberwort für das ist, was ich möchte.
Einen schönen Tag!
2 Antworten
Das Schlüsselwort heißt sverweis, ich versuche mal eine Formel mit allgemeinen Bezeichnungen, allerdings waren diene Informationen ein wenig ... knapp.
ich gehe mal davon aus dass die Artikelnummer in Dokument B auch in Spalte 1 steht und du Spalte 2 ausgegeben haben willst....
Schreib in Dokument A in die Spalte 2 (in meinem Beispiel Zelle A2, in A1 steht eine Artikelnummer) folgendes:
=sverweis(A1;'[Dokument A.xlsx]Tabelle1'!A:B;2;falsch)
sag mir mal ob das klappt....
Die Bezeichnungen Dokument A und Tabelle1 musst du noch an dein Beispiel anpassen...
Ach ja und wenn du deine zweite Datei nicht immer geöffnet hast kannst du auch einen allgemeinen Bezug herstellen dann sieht das so aus
=sverweis(A1;'C:\Dateipfad\[Dokument A.xlsx]Tabelle1'!A:B;2;falsch)
Da kannst du das noch ein wenig nachlesen ;-)
Hey :). Funktionieren tut es so noch nicht. Aber ich versuche die Formel mal etwas aufzuschlüsseln. Ich möchte es ja auch verstehen :). Ich wollte mit den Infos nicht zu knapp sein :P.
Also:
A1 = Wert, welcher gesucht werden soll
Dokument.xlsx = Name von Dokument B
Tabelle 1 = Name der Arbeitsmappe in Dokument B
Den A:B;2;falsch Teil verstehe ich leider (noch) nicht.
Konkret in meinem Fall ist der Wert Artikelnummer in Dokument A in der Spalte K und in Dokument B in Spalte A. Wenn Wert von K in B vorkommt (die Werte haben nicht die gleiche Reihenfolge, Wert von K kann also in Zeile 500 sein und bei Dokument B in 3), soll er den Wert aus Spalte C (Dokument B) in Dokument A Spalte N kopieren.
Mit diesem Wert muss ich dann sowieso noch weiter rechnen, aber das auch noch in die Formel einzubauen wäre zu kompliziert, denke ich. Das eine würde mir schon sehr weiterhelfen.
Falls das verwirrend oder nicht ausreichend ist, lass es mich wissen. Ich bin selbst sehr interessiert an der Lösung :D
Danke für Deine nette Hilfe.
Das kriegen wir hin... ich muss zwar zweimal lesen aber das passt schon.
Gehen wir das mal anders an:
Du hast ein Dokument B. Das nennen wir mal Archiv. in deinem Archiv sind Artikelnummern mit zugehörigen Daten versehen, Spalte A sind die Artikelnummern und, ich vermute mal, Spalte C ist der Preis.
Dann hast du ein Dokument A. Das nennen wir mal, ich vermute wieder, Rechnung (das gleiche geht auch für Bestellung wenn du das meinst). Du gehst also in Spalte N der Rechnung (oder was auch immer Dokument A ist), denn da soll das Ergebnis ausgegeben werden. da kommt die Formel hin (ich hab sie jetzt deinen Angaben entsprechend angepasst und umbenannt:
=sverweis(K1;'C:\Dateipfad\[Archiv.xlsx]Tabelle1'!A:C;3;falsch)
Der sverweis such nach einem Kriterium in der ersten Spalte einer Matrix (eines Bereiches). Wenn er das Kriterium gefunden hat, gibt er den zugehörigen Wert einer geforderten Spalte aus. Die Angabe Wahr ist dafür da, den ersten ähnlichen Wert zu finden, Falsch findet die Zeile mit einer exakten Übereinstimmung des Kriteriums (was bei Artikelnummern ja vonnöten ist)
Die Formel heißt also übersetzt folgendes:
Liebes Excel, suche per sverweis nach dem Wert von K1 in der Datei C:\Dateipfad\Archiv.xlsx im Tabellenblatt Tabelle1 in dem Bereich A:C (Spalte A bis C). Wenn du ihn gefunden hast, gib mir den Wert der in Spalte 3 der Bereiches steht (denn C ist die dritte Spalte). Zusätzlich möchte ich, dass du exakt nur die Artikelnummer findest die ich dir sage (FALSCH).
du kannst auch einfach mal bei Excel
=sverweis(
eingeben. Dann siehst du unter dem Feld die Hilfe von Excel, vielleicht hilft dir das zu lesen, warum da jetzt ein ; oder was auch immer steht
Geholfen?
Übrigens, falls deine Reise in die Richtung geht, wie ich sie vermute, dann könnte dein erwähntes Rechnen innerhalb der Formel auch mit eingebaut werden, wenn du zum Beispiel in einer anderen Spalte noch Mengen hast und dann zum einen den Einzelpreis und den Gesamtpreis ermitteln willst. Nur so als Hinweis ;-)
Wow. Es gibt nicht viele Leute, denen ich Respekt zolle. Aber den hast Du Dir gerade mehr als verdient. Du arbeitest heute zwar im Büro, aber warst Du mal als Lehrer tätig? Wenn nicht, wird mit Dir viel Potenzial in dem Sektor verschenkt. So macht es Sinn. Gerade das mit dem Matrix/Bereich-Part lässt eine vorher komplizierte und unverständliche Aneinanderreihung von Befehlen zu einem klar verständlichen Konstrukt transformieren. Ich denke das ist wie mit Mathematik im Allgemeinen. Wenn man es "durchschaut" kann es "sehr" einfach sein. Für den, der "es" nicht sieht, ist es das Komplizierteste auf Erden.
DANKE! DANKE! DANKE! Ich probiere und berichte :)
es tut gut so eine Rückmeldung zu hören :-). ich war noch nicht als Lehrer tätig, aber ich bin ja auch erst 25 :-D. vielleicht gehe ich nach etlichen Jahren Wirtschaft ans Berufskolleg aber das ist noch ein weiter Weg. solltest du wieder fragen haben kannst du mir auch persönlich schreiben
@puggy: ich will Dir nicht die Hilfreichste klauen, Du hast Dich ja riesig reingehängt!
@beide: Aber flexibler als der SVerweis ist die Kombi indirekt(Adresse(Vergleich(ArtNr;Suchspalte;0);Spalte)), hier wegen der Zelle 2 daneben etwas komplizierter:Spalte+2)
Die externe SpalteA:A ist: C:\Dateipfad\[Archiv.xlsx]Tabelle1!A:A
Vergleich(SuchargumentzB.K5;externeSpalteA:A;0)
liefert die Zeilennummer des in A:A gefundenen Sucharguments. AUSLESEN willst Du aber aus Spalte C=3, die Adresse (also der Ort dieses Wertes ist =Adresse(Vergleich(ArtNr;Suchspalte;0);3)
damit hat Indirekt(Adresse(...)) alles zum Auslesen auch des Wertes.
Anstelle von 3 kannst Du auch relativ schreiben Spalte(A1)+2 oder Spalte(A:A)+2 oder eben gleich 3
Oder =index(...C:C;Vergleich(...)) Diese einfachste Variante von Index reicht völlig aus und man kann mit beiden Formeln auch links von der Suchspalte(macht der SVerweis nicht!), eins unter/über der Findezelle(oder 2 oder 3): mit Vergleich(...)±1 (macht SVerweis auch nicht) oder eben in einer beliebigen Spalte was auslesen (man braucht den Bereich nicht vorher zu definieren wie beim SVerweis)
die Kombi werde ich mir merken, wenn es mal um Ausgabe spalten links der auch Spalte geht, danke. ich denke aber, wenn man ganz einfach rechts sucht dann ist der sverweis simpler als eine verschachtelte Formel (hier sogar von drei Formeln, da leidet für Laien die Übersichtlichkeit. trotzdem, danke für die Kombi, ich werde sie zu 100% irgendwann benutzen können ;-)
danke für die Rückmeldung, zeigt doch, dass man nicht in den Wind gesprochen hat.
übrigens noch ein Vorteil der Paarung index(..;Vergleich(Sucharg;Suchspalte;0=exakt))
Bei entsprechender $-Setzung kann man zB nach rechts kopieren, ohne die Versatzspalte neu definieren zu müssen: Aus
=index(C:C;Vergleich($K5;$A:$A;0)) wird durch nach-rechts-kopieren
=index(D:D;Vergleich($K5;$A:$A;0))
Und man sieht auch der Formel sofort an, aus welcher Spalte sie den Wert holt (wird bei Versatzspalte >10 beim SVerweis recht unübersichtlich oder auch, wenn der Bereich nicht in A beginnt)
kompliziert: ja, aber hat mans einmal kapiert, ergibt sich alles wie von selbst, man muss nur eben wissen, dass immer die innerste Klammer zuerst ausgewertet wird. Mein Problem war lange Zeit, das 0 bzw 1 zuzuordnen, aber das muss man beim SV ja auch:
Eselsbrücke: 0=Falsch meldet, wenn's falsch ist und nimmt mit falsch=un-geordneten Listen vorlieb, WAHR bringt IMMER einen Wert (auch wenn er falsch ist) und verlangt WAHR=richtig geordnete Listen
ganz davon abgesehen, dass sich puggi ja schon gut um dich kümmert hört es sich im ansatz danach an als solltest du oder ihr mal über den einsatz von Datenbanken nachdenken.
Gerade beim Stichwort artikelerfassung und spätere bereitstellung/auslesen von daten wäre das mit excel nur gemurkse und mit einer datenbank weit sinnvoller organisiert
access (win) oder filemaker (mac) bieten gute möglichkeiten und automatisierungs- bzw. interaktionsschnittstellen
Daran hab ich auch schon gedacht aber wenn man in beiden Gebieten eher... schwierig an Lösungen kommt, ist Access dann noch mal ne Spur umständlicher, Excel kennt man wenigstens in Ansätzen. Direkt ein neues Programm nehmen wird heftig, fürchte ich.
Gut kümmern ist wohl untertrieben ;). Ich habe - wieso auch immer - lustigerweise nie einen Berührungspunkt zu Access bzw. Datenbanksoftwares gehabt, außer im Web-Bereich. Sonst würde ich so etwas von Haus aus vielleicht ganz anders angehen. Man denkt halt oft nur im Rahmen seiner Möglichkeiten.
Ich denke ich sollte mich allgemein mal mit dem Thema beschäftigen. Vielleicht ist dies auch noch für andere Bereiche nützlich. Und man lernt nie aus ;). Für den Moment suche ich eine "Quick 'n Dirty"-Lösung, weil wir die Verwaltung bald über eine neue WaWi laufen lassen und dafür wäre die Einrichtung einer eigenen Datenbank für 2x Benutzen wahrscheinlich etwas zu viel des Guten.
Bei den heutigen Speicherkapazitäten und dem Formelumfang von xl ist eine Datenbank nur noch für riesige Datenmengen sinnvoll. Für normale Datenmengen reicht xl völlig aus.
Ich hab 13 Jahre lang Artikelnummernlisten mit >50.000 Items und Spaltenzahlen von 40-50 bearbeitet, Be3standteilelisten und Rezeptberechnungen erstellt, Produktionsdaten kontrolliert etc. und das mit dem damaligen xl5, xl95 und xl97. "Gemurxe" war das sicher nicht.
Dateien mit über 10.000 zt komplizierteren, zT sogar sehr komplizierten Formeln waren kein Problem. Lieber gut in xl als mittelmäßig in zweierlei Programmen (wobei man Word, ppt und Outlook ja auch noch halbwegs können muss).
xl hat den Vorteil, dass Du relativ leicht Hilfe findest, viel leichter als bei zB Access.
Werde es probieren und berichten. Danke schonmal! :)