Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 12:41:11
Vielleicht Onur ? Leider werden nicht alle Monate
16.04.2024 12:08:33
Zelle/Spalte bestimmten Wert in Kopfzeile zuordnen
16.04.2024 12:01:17
Dynamische Liste generiert aus mehreren Vorgagen
16.04.2024 11:48:22
Blatt in HTML exportieren wenn Checkbox aktiv ist
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
984to988
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
984to988
984to988
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

sverweise mit suchstring?

sverweise mit suchstring?
Aron
MY favorit Forum ;)
Ich habe zwei excel Dateien (eine Liste und Rechnungen).
Aus der Rechnungsdatei sollen folgende Daten importiert werden:
Das Datum(Eingangsdatum der Rechnung)
Die Rechnungsnummer
Der Betrag
Datum dargestellt als z.B. Feb 08
Nochmal den Netto Betrag
Und ein X für Bezahlt
Ich dachte da an sverweise.
Das größte Problem liegt im ersten Schritt, dass die Projektnummern in der Rechnungsdatei gefunden werden muss(um weitere Daten der Zeile zu entnehmen), leider wird die Projektnummer manchmal als pxxx-xx eingetragen und manchmal als xxx-xx eingetragen.
Also kann ich nicht nach dem exakten Wert suchen.
Also muss ich nach string suchen.

Suchkriterium mit * und zusammengebastelt
18.06.2008 12:02:31
heikoS
Hallo Aron,
das sollte so funktionieren:
Tabelle1

 ABCDEFG
1p4711-4222hi P4711-4222hi
24711-3333du P4711-3333du
3P99-844frager P99-844frager

Formeln der Tabelle
ZelleFormel
F1=SVERWEIS("*"&RECHTS($E1;LÄNGE($E1)-1); $A$1:$C$3;2;)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Klappt´s?
Gruß Heiko

Anzeige
kommt drauf an, auf welcher Seite ...
18.06.2008 12:07:16
neopa
Hallo Aron,
... die Fehleingabe gemacht wurden . Habe für beides eine Lösungsvorschlag, auch wenn Heiko mit seinem schneller war.
 ABCDEFG
1Pr-NrDatumWert1 Pr-NrDatumWert1
2P001-00101.03.20082560 001-00215.04.20083680
3P001-00215.04.20083680 p001-00522.05.20089564
4001-00318.04.200878560 123-004??
5P001-00401.05.20088560    
6001-00522.05.20089564    
7P001-00618.06.200834567    

Formeln der Tabelle
ZelleFormel
F2=WENN(ISTZAHL(VERGLEICH("*"&WECHSELN($E2;"p";""); $A$2:$A$9;)); SVERWEIS("*"&WECHSELN($E2;"p";""); $A$2:$C$9;SPALTE(B1); ); "?")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: kommt drauf an, auf welcher Seite ...
Aron
Dankeschön.
Ich hab erstmal zum verständnis deine Tabelle gemacht.
Dann hab ich versucht den sverweis auf eine andere Excel Datei-Tabelle zuverlinken.Ging bisher noch nicht.
Problem List und Rechnung stimmen nicht überein.
Rechnung fängt mit Rechnungsnummer an.
List fängt mit Namen des Projekts an.

Wer fängt womit an?
18.06.2008 14:54:24
heikoS
Hallo Aron,
nichts für ungut, aber vielleicht machst Du ´mal eine Beispieldatei fertig und stellst die hier ein, sonst wird das nichts - glaube ich.
Gruß Heiko
PS: Und wenn das hier Dein favorisiertes Forum ist, dann kennst Du bestimmt auch die hier herrschende Netiquette, oder?

Anzeige
sorry..mach ich gern-Hier zwei Dateien
Aron
Moin Du hast recht sorry.
Bitte könntet ihr mir nochmal helfen. Ich habe hier Projekt- und Rechnungsmuster fertig gemacht.
Aufgabenstellung wie gehabt.
Die Formel hatte ich von neopa verwendet .
=WENN(ISTZAHL(VERGLEICH("*"&WECHSELN($D2;"p";""); [test Rech.xlsx]Tabelle1!$C$2:$C$8;)); SVERWEIS("*"&WECHSELN($D2;"p";""); [test Rech.xlsx]Tabelle1!$A$2:$D$8;SPALTE($B$2); ); "")
Mir wurde dann #NV angezeigt.
Würde mich echt über eure Hilfe freuen.
Danke
Schönen Tag
Gruß
Aron S.
Rechnungs datei https://www.herber.de/bbs/user/53195.xlsx
Rechnungsnummer Datum Pr-Nr Nettobetrag
2190_010_2008 01.03.2008 P001-002 1000
2190_010_2009 15.04.2008 001-005 1200
2190_010_2010 01.05.2008 P001-001 2430
2190_010_2011 22.05.2008 P001-004 23414
2190_010_2012 18.06.2008 001-007 13123
2190_010_2013 19.06.2008 P001-010 312345
2190_010_2014 20.06.2008 P001-013 5477
Projekt datei. https://www.herber.de/bbs/user/53194.xlsx
Kunde Projektbez. Leiter Pr-Nr Datum Rechnungsn. Netto Datum Netto Bezahlt . (xx.xx.xxxx) (als Feb.08) (X-Eintragen)
xy spiel wes P001-001
xy spiel wes P001-002
xy spiel wes 001-003
xy Auto wes P001-004
xy lego wes 001-005
xy sti wes P001-006
xy lok wes 001-007
xy bahn wes P001-008
Die Spalten von Datum bis Bezahlt sollen ausgefüllt werden anhand der Rechnungsdatei.

Anzeige
AW: sorry..mach ich gern-Hier zwei Dateien
Aron
Moin Du hast recht sorry.
Bitte könntet ihr mir nochmal helfen. Ich habe hier Projekt- und Rechnungsmuster fertig gemacht.
Aufgabenstellung wie gehabt.
Die Formel hatte ich von neopa verwendet .
=WENN(ISTZAHL(VERGLEICH("*"&WECHSELN($D2;"p";""); [test Rech.xlsx]Tabelle1!$C$2:$C$8;)); SVERWEIS("*"&WECHSELN($D2;"p";""); [test Rech.xlsx]Tabelle1!$A$2:$D$8;SPALTE($B$2); ); "")
Mir wurde dann #NV angezeigt.
Würde mich echt über eure Hilfe freuen.
Danke
Schönen Tag
Gruß
Aron S.
Rechnungs datei https://www.herber.de/bbs/user/53195.xlsx
Rechnungsnummer Datum Pr-Nr Nettobetrag
2190_010_2008 01.03.2008 P001-002 1000
2190_010_2009 15.04.2008 001-005 1200
2190_010_2010 01.05.2008 P001-001 2430
2190_010_2011 22.05.2008 P001-004 23414
2190_010_2012 18.06.2008 001-007 13123
2190_010_2013 19.06.2008 P001-010 312345
2190_010_2014 20.06.2008 P001-013 5477
Projekt datei. https://www.herber.de/bbs/user/53194.xlsx
Kunde Projektbez. Leiter Pr-Nr Datum Rechnungsn. Netto Datum Netto Bezahlt . (xx.xx.xxxx) (als Feb.08) (X-Eintragen)
xy spiel wes P001-001
xy spiel wes P001-002
xy spiel wes 001-003
xy Auto wes P001-004
xy lego wes 001-005
xy sti wes P001-006
xy lok wes 001-007
xy bahn wes P001-008
Die Spalten von Datum bis Bezahlt sollen ausgefüllt werden anhand der Rechnungsdatei.

Anzeige
dann besser mit INDEX() und VERGLEICH() ...
20.06.2008 16:26:00
neopa
Hallo Aron,
... meine Antwort hat auf sich warten lassen, weil ich zur Zeit Stress mit der Zeit hatte & habe. Habe auch verzichtet mir Deine Tabellen runterzuladen..
Nun aber ein angepasster Lösungsvorschlag, den ich auf Basis Deiner Textangaben vorgenommen habe, die ich allerdings wieder der einfacherenn Darstellung in einem Tabellenblatt zusammengestellt habe.
Bei Deiner Datenstruktur hättest Du mit SVERWEIS() immer Probleme. Für derartige Aufgaben ist die Kombination INDEX() und VERGLEICH() besser geeignet, weil SVERWEIS() nur "einseitig" nach rechts vergleichen und suchen kann.
Die Formel in J1 einfach nach rechts und unten durch Ziehen kopieren. Übrigens: Der Formelnteil mit WECHSELN(J$1;".";"")&"*" wäre nicht notwendig, wenn wenigstens die Überschriften identisch mit den der Rechnungstabelle wären ;-) .
Um die angepriesene "Wechselseitig" der Pr-Nr. der Formel nachzuweisen, habe ich in C2 und I6 (gelb markiert) eine kleine Manupulation der Bezeichnung vorgenommen. Also ob mit oder ohne "P" sollte das richtige Ergebnis kommen. Allerdings Achtung, das gilt jetzt nur für ein großes "P". Wenn en kleines "p" vorangestellt ist/wird, muss die Formel noch etwas erweitert werden.
 ABCDEFGHIJKL
1RechnungsnummerDatumPr-NrNettobetrag KundeProjektbez.LeiterPr-NrDatumRechnungsn.Netto
22190_010_200801.03.2008001-0021000 xyspielwesP001-00101.05.20082190_010_20102430
32190_010_200915.04.2008001-0051200 xyspielwesP001-00201.03.20082190_010_20081000
42190_010_201001.05.2008P001-0012430 xyspielwes001-003???
52190_010_201122.05.2008P001-00423414 xyAutowesP001-00422.05.20082190_010_201123414
62190_010_201218.06.2008001-00713123 xylegowesP001-00515.04.20082190_010_20091200
72190_010_201319.06.2008P001-010312345 xystiwesP001-006???
82190_010_201420.06.2008P001-0135477 xylokwes001-00718.06.20082190_010_201213123
9     xybahnwesP001-008???

Formeln der Tabelle
ZelleFormel
J2=WENN(ISTZAHL(VERGLEICH("*"&WECHSELN($I2;"P";""); $C$2:$C$9;)); INDEX($A$2:$D$99;VERGLEICH("*"&WECHSELN($I2;"P";""); $C$2:$C$99;); VERGLEICH(WECHSELN(J$1;".";"")&"*";$A$1:$D$1;)); "?")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: dann besser mit INDEX() und VERGLEICH() ...
23.06.2008 16:47:27
Aron
Hey großen Danke für deine Rückmeldung, trotz das du so wenig zeit hast!
Echt cool!
Leider stoße ich immer wieder auf neue Probleme...
1.In der Rechnungsdatei werden die Projektnummern Pxxx_JJJJ, xxx.F1_JJJJ und auch xxx_JJJJ geschrieben.
Leider wurden die Projektnummern in der Rechnungsdatei als xxx-JJ, xxx.F1-JJ geschrieben.
Mein Problem ist wie veranlasse ich Excel dazu die Datei zu suchen und die Daten wie Datum etc. zu der richtigen Projektnummer in der Projektdatei hinzuzufügen?
Für die Zukunft könnte ich versuchen eine Einheitliche Schreibweise zu veranlassen, aber für die restlichen bräuchte ich noch eine Lösung.
2.Wenn "nichts" in der Zelle steht oder ein "x" sollte die Zelle mit der Formel leer stehen.
Wie geht das?
Bisher verwendete Formeln(von neopa)
=WENN(ISTZAHL(VERGLEICH("*"&WECHSELN($C36;"p";""); [Rechnungspiegel_extra_datei.xlsm]Tabelle1!$A$2:$A$13;));SVERWEIS("*"&WECHSELN($C36;"p";""); [Rechnungspiegel_extra_datei.xlsm]Tabelle1!$A$2:$D$13; SPALTE([Rechnungspiegel_extra_datei.xlsm]Tabelle1!$B$2););"")
=WENN(ISTZAHL(VERGLEICH("*"&WECHSELN($C37;"P";""); [Rechnungspiegel_extra_datei.xlsm]Tabelle1!$A$2:$A$13;)); INDEX([Rechnungspiegel_extra_datei.xlsm]Tabelle1!$A$2:$D$13;VERGLEICH("*"&WECHSELN($C37;"P";""); [Rechnungspiegel_extra_datei.xlsm]Tabelle1!$A$2:$A$13;); VERGLEICH(WECHSELN(J2;".";"")&"*"; [Rechnungspiegel_extra_datei.xlsm]Tabelle1!$A$1:$D$1;)); "?")
Bitte könntest du(oder jemand anders?) mir helfen, wenn du noch Zeit hättest.(im laufe der Woche.)
Danke dir für alles.
Aron
Projektdatei:
https://www.herber.de/bbs/user/53283.xlsx
Rechnungsdatei:
https://www.herber.de/bbs/user/53284.xlsx

Anzeige
da musst DU mehr WECHSELN() ...
24.06.2008 15:59:15
neopa
Hallo Aron,
... als bisher angegeben. In Deiner Beipieldatei sollte in E1 dann z.B.:
=WENN(ISTZAHL(VERGLEICH("*"&WECHSELN(WECHSELN($D2;"-";"_20");"p";""); [Rechnungspiegel_extra_datei.xls]Tabelle1!$A$2:$A$99;))*(D2"")*(D2"x"); SVERWEIS("*"&WECHSELN(WECHSELN($D2;"-";"_20");"p";""); [Rechnungspiegel_extra_datei.xls]Tabelle1!$A$2:$G$99;VERGLEICH(E$1; [Rechnungspiegel_extra_datei.xls]Tabelle1!$1:$1;);0);"")
stehen. Formel ist nach rechts und unten kopierbar.
Gruß Werner
.. , - ...

AW: da musst DU mehr WECHSELN() ...
24.06.2008 19:10:29
Aron
Hey besten Dank.
Ich hatte mir das zwar schon gedacht das ich da mehr wechseln muss, aber es leider nicht hin bekommen.
Danke dir.
Geb mir bescheid wenn ich was für dich tun kann.
*close*
cya As
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige