Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1076to1080
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
1076to1080
1076to1080
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Auslesen in Sammelliste mit Offset

Auslesen in Sammelliste mit Offset
06.06.2009 11:49:16
Jubro
Hallo zusammen,
ich hoffe mal wieder auf eure Hilfe :o) bei meinem Problem.
Ich hab eine Kundendatenbank (in der Tabelle Namens Kundendaten) in der ab Zelle D6 Kundennamen stehen und die dazugehörige Kundennr. in E6. Jedoch folgt der nächste Kundenname erst in Zelle D16 und die dazugehörige Nr. E16 und dann wieder in D26 und E26 usw. Also es ist immer ein Abstand von 10 Zellen zwischen den Kunden (das kann auch nicht geändert werden da dazwischen noch andere Daten verankert sind).
Jetzt möchte ich in der selben Mappe in einer neuen Tabelle (die sich Sammelliste nennt) diese Daten auslesen und untereinander ein einer Liste haben um diese auszudrucken usw.
Am liebsten mit SVERWEIS und/oder WVWERWEIS aber ich bekomms nicht hin.
Gruß Jürgen

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Auslesen in Sammelliste mit Offset
06.06.2009 12:07:29
Jan
Hallo Jürgen,
die Lösung, die mir spontan einfällt wäre die Benutzung von INDIREKT in Zusammenspiel mit ZEILE.
Wenn z.B. die Kundennamen auf dem ersten Blatt ab Zeile 5 aufgelistet werden sollen, lautet die Formel
=INDIREKT("Kundendaten!D"&(ZEILE()-5)*10+6)
Und für die Kundennummern
=INDIREKT("Kundendaten!E"&(ZEILE()-5)*10+6)
... dann einfach nach unten kopieren.
Viele Grüße aus Hamburg,
Jan
Index statt Indirekt
06.06.2009 14:17:30
Beate
Hallo,
INDIREKT hat den Nachteil, dass es volatil ist.
Siehe hierzu: !Langsames Excel (1) - Volatile Funktionen
Deshalb besser INDEX benutzen:
Hier ein Ausschnitt der Ausgangstabelle:
Kundendaten

 DE
5NameKd-Nr.
6Name11000
7  
8  
9  
10  
11  
12  
13  
14  
15  
16Name21010
17  
18  
19  
20  
21  
22  
23  
24  
25  
26Name31020

Und hier die Auswertung:
Sammelliste

 AB
1NameKd-Nr.
2Name11000
3Name21010
4Name31020
5Name41030
6Name51040
7Name61050
8Name71060
9Name81070
10Name91080
11Name101090

Formeln der Tabelle
ZelleFormel
A2=INDEX(Kundendaten!D:D;(ZEILE())*10-14)
B2=INDEX(Kundendaten!E:E;(ZEILE())*10-14)

Die Formeln aus Zeile 2 kannst du runterkopieren.
Gruß,
Beate
Anzeige
Index statt Indirekt
06.06.2009 14:17:55
Beate
Hallo,
INDIREKT hat den Nachteil, dass es volatil ist.
Siehe hierzu: !Langsames Excel (1) - Volatile Funktionen
Deshalb besser INDEX benutzen:
Hier ein Ausschnitt der Ausgangstabelle:
Kundendaten

 DE
5NameKd-Nr.
6Name11000
7  
8  
9  
10  
11  
12  
13  
14  
15  
16Name21010
17  
18  
19  
20  
21  
22  
23  
24  
25  
26Name31020

Und hier die Auswertung:
Sammelliste

 AB
1NameKd-Nr.
2Name11000
3Name21010
4Name31020
5Name41030
6Name51040
7Name61050
8Name71060
9Name81070
10Name91080
11Name101090

Formeln der Tabelle
ZelleFormel
A2=INDEX(Kundendaten!D:D;(ZEILE())*10-14)
B2=INDEX(Kundendaten!E:E;(ZEILE())*10-14)

Die Formeln aus Zeile 2 kannst du runterkopieren.
Gruß,
Beate
Anzeige
AW: Index statt Indirekt
06.06.2009 16:21:49
Luschi
Hallo Beate,
die Klammersetzung sollte sicher so sein:
=INDEX(Kundendaten!D:D;(ZEILE()*10)-14)
es ginge aber auch: =INDEX(Kundendaten!((ZEILE()-1)*10)-4)
aber bei Deiner Lösung brauch er einen Rechenschritt weniger.
Gruß von Luschi
aus klein-Paris
@ Luschi - Index kürzer
06.06.2009 23:50:48
Beate
Hallo Luschi,
bei mir ist sogar ein ganzes Klammerpaar überflüssig. So reicht es auch:
Sammelliste

 AB
1NameKd-Nr.
2Name11000
3Name21010
4Name31020
5Name41030
6Name51040
7Name61050
8Name71060
9Name81070
10Name91080
11Name101090

Formeln der Tabelle
ZelleFormel
A2=INDEX(Kundendaten!D:D;ZEILE()*10-14)
B2=INDEX(Kundendaten!E:E;ZEILE()*10-14)

Der Rest wird ja durch Punkt- vor Strichrechnung geregelt.
Grüße,
Beate
Anzeige
AW: @ Luschi - Index kürzer
08.06.2009 07:41:52
Luschi
Hallo Beate,
sicher hast Du recht, aber bei solchen Sachen bin ich etwas vorsichtiger und schreibe die Klammern trotzdem.
Gruß von Luschi
aus klein-Paris
AW: Index statt Indirekt
08.06.2009 08:53:06
Jubro
Hallo zusammen,
hierzu hab ich jetzt doch nochmal eine Frage!
Ich wollte nun die generierte Sammelliste A-Z sortieren und das funzt nicht. Hat das was mit den Formeln zu tun?
Wie kann ich die Sortierung hinbringen
Gruß Jürgen
Liste sortieren
08.06.2009 15:27:54
Beate
Hallo Jürgen,
ja, die Nichtsortierbarkeit hat mit den Formeln zu tun. Da die Formel ja in allen Zeilen gleich ist, hängt das gezeigte Ergebnis ja nur von der Zeilenzahl im Blatt Sammelliste ab.
Ausweg:
  • Bennene dein bisheriges Blatt Sammelliste in Hilfsblatt um

  • Füge ein neues Blatt ein mit dem Namen Sammelliste

  • Dort folgende Formel einfügen (Formel aus A2 kann nach rechts und unten kopiert werden)

  • Anschließend kannst du im neuen Blatt Sammelliste wie gewohnt nach Namen sortieren

    • Sammelliste

     AB
    1NameKd-Nr.
    2Name11000
    3Name21010
    4Name31020
    5Name41030
    6Name51040
    7Name61050
    8Name71060
    9Name81070
    10Name91080
    11Name101090

    Formeln der Tabelle
    ZelleFormel
    A2=Hilfsblatt!A2

    Gruß,
    Beate
    Anzeige
    AW: Liste sortieren
    08.06.2009 16:45:38
    Jubro
    Hallo Beate,
    THX :o)
    Gruß Jürgen
    AW: Auslesen in Sammelliste mit Offset
    06.06.2009 14:05:34
    Luschi
    Hallo Jürgen,
    so etwas läßt sich mit der Funktion Bereich.Verschieben() bewerkstelligen.
    Hier ein Beispiel dazu: https://www.herber.de/bbs/user/62251.xls
    Gruß von Luschi
    aus klein-Paris
    ..warum fehlt Krüger? Zeile16 ? oT
    06.06.2009 14:49:01
    bernd
    AW: ..warum fehlt Krüger? Zeile16 ? oT
    06.06.2009 16:12:52
    Luschi
    Hallo Jürgen,
    hast Recht: es reichen aber auch diese Formeln in Tabelle2:
    A2: =BEREICH.VERSCHIEBEN(Tabelle1!$D$1;((ZEILE()-1)*10)-5;0)
    B2: =BEREICH.VERSCHIEBEN(Tabelle1!$E$1;((ZEILE()-1)*10)-5;0)
    und dann die Formeln nach unten ziehen!
    Gruß von Luschi
    aus klein-Paris
    Anzeige
    Auch Bereich.Verschieben ist volatil
    07.06.2009 00:25:21
    Beate
    Hallo Luschi,
    da Bereich.Verschieben ebenso volatil ist wie INDIREKT, führt es in der Masse benutzt auch zur Ausbremsung der Datei. Daher hat sich der Fragesteller mit der INDEX-Lösung für die schnellste Formellösung entschieden.
    Schönes Restwochenende für dich.
    Grüße,
    Beate
    AW: Auslesen in Sammelliste mit Offset
    06.06.2009 19:28:48
    Jubro
    Hallo,
    vielen Dank für eure Hilfe, bin gerade nach Hause gekommen und habs gleich ausprobiert, ich habs mit Index realisiert.
    Hab noch nicht so viel Ahnung von Formeln und VBA in Excel und währ ohne euch alle recht aufgeschmissen.
    Danke nochmals an alle

    Beliebteste Forumthreads (12 Monate)

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

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige