Wie kann ich mit Hilfe einer Excel-Formel beim nach rechts kopieren mehrere Spalten überspringen?

Computer: Software & Programmierung Office Software
#1

Hallo zusammen,
ich hoffe, mir kann hier jemand weiterhelfen :smile:

Ich bin auf der Suche nach einer Excel-Formel, die beim nach rechts kopieren 5 Spalten überspringt.
Ich habe im ersten Tabellenblatt eine große Gesamtübersicht, die sich aus mehreren einzelnen Tabellenblättern zusammensetzt. In einem dieser weiteren Tabellenblätter sind zum Beispiel etliche Produkte aufgelistet, deren Bestand, Preis etc. monatlich ausgewertet wird. Daher trage ich zum Beispiel den Bestand immer manuell pro Filiale ein und summiere jede Spalte.

Eine der weiteren Tabellen ist dann so aufgebaut, dass ich in Spalte A die Filialen habe, in den Spalten B bis F die Bestände, Preise etc. für den Monat Januar. In den Spalten G bis K dann alles für den Monat Februar usw. (also immer 5 Spalten pro Monat)

Jetzt habe ich versucht, in der Gesamtübersicht mit Hilfe der Formel =wverweis nach dem Text der ersten Spalte des Monats Januar, also: =wverweis(Suchkriterium;B2:F3;2;falsch)
So, nun will ich diese Formel in der Gesamtübersicht um eine Zelle nach rechts kopieren, aber gleichzeitig soll Excel mir im dazugehörigen Tabellenblatt die Matrix um 5 Spalten verschieben, so dass dann als =wverweis(Suchkriterium;G2:K3;2;falsch) rauskommen muss.

Ich habe es auch schon mit der Zusatzfunktion BEREICH.VERSCHIEBEN versucht, komme aber nicht auf meinen Fehler … =WVERWEIS(B42;BEREICH.VERSCHIEBEN(‚Tabelle1‘!B2:F3;ZEILE();(SPALTE(I3)*5));2;FALSCH)

Kann mir hier vielleicht jemand weiterhelfen? Ich hoffe, ich habe es nicht zu kompliziert erklärt :smile:
Vielen Dank schon mal! :wink:
LG aus Passau, Anna

#2

hi,

es ist Warm.
Mach doch bitte ein Beispiel. Irgendwo bei den Filialen hing mein Blick wieder am eisgekühltem Getränk.

Generell zieht man Formeln mit Zwischenräumen in dem Man einfach Formel und Leerzellen markiert und diese füllt.
Aber glaub das wird hier nix, bin mir aber unsicher, da ich mich da echt nicht reindenken will.

Wenn ichs richtig verstanden habe, würde ich die Position der Zelle errechnen und dann mit Adresse() aufrufen.

grüße
lipi

#3

Hallo,
danke für deine Antwort.

Kurz gesagt suche ich eine Funktion/Formel, mit der beim nach rechts kopieren meiner sverweis/wverweis Formel die Matrix um 5 Spalten übersprungen wird.

Ich will nicht einfach mit einem Zellenbezug arbeiten, wie =Tabellenblatt2A1 … also nicht so:
In Tabellenblatt 1 in Zelle A1 =Tabellenblatt2!A1
In Tabellenblatt 1 in Zelle A2 =Tabellenblatt2!G1

Gibt es hier so eine Möglichkeit? Ich habe schon von Index, BEREICH.VERSCHIEBEN etc. gelesen, aber hier bin ich leider auch nicht weiter gekommen …

Schon mal vielen lieben Dank für deine Hilfe! :smile:
LG, Anna

#4

Es geht also nicht darum wie man die Zellen kopiert, sonst hätte man Bill fragen müssen. Wenn ich es recht verstehe willst du nach rechts ausfüllen und dabei einen Sprung der „gesuchten Spalte“ um 5 erreichen.

Mal unterstellt ich hätte es verstanden. In solchen Fällen nehme ich eine Hilfszeile, in der ich die Spalte berechne. Also K1 (K ist geraten und ich nehme am liebsten die Zeile 1) enthält die erste Spalte: z.B. 10 (Der Sverweis holt die 10 Spalte aus der Matrix). In L1 steht dann =K1+5; Dann diese Formel nach rechts kopieren. Der Sverweis lautet dann: Sverweis(„name“, matrix, K$1, ) Den auch nach unten und rechts ausfüllen.

Danach die Zeile ausblenden, voila!

Vorteil am Rande: Verschiebt man mal was in der Matrix weil man ein paar Spalten einfügt oder braucht dann doch einen 6er Rhytmus ist das dann schnell anzupassen.

fg

Dirk_P

#5

Hallo Anna

Ich weiss nicht, ob ich Dich richtig verstanden habe. - Ich habe versucht, nach Deinen Angaben etwas zu machen und habe hier zwei Lösungen:

A) eine „primitive“: Ich formuliere die Spaltenüberschriften für jeden Monat einzeln.
http://www.file-upload.net/download-11886131/AnnaA.htm.html

B) eine Lösung mit BEREICH.VERSCHIEBEN, wie Du es angetönt hast.
http://www.file-upload.net/download-11886149/AnnaB.htm.html

Viele Grüsse Niclaus

#6

Hallo,
leider kann ich beide Dateien nicht herunterladen, da sie mein Virus Programm als „schädliche Dateien“ kennzeichnet. Kannst du mir vielleicht einen Screenshot der Formeln hochladen?

Vielen lieben Dank für die Hilfe.
LG, Anna

#7

Hallo Anna
Das ist nicht gut, dass ich zum Viren-Verbreiter wurde :innocent:
Hier meine Vorschläge. Ich habe mir eine Tabelle zusammengebastelt, die etwas Deinen Schilderungen entsprechen kann. Die Printscreens sind leider kaum lesbar. Hast Du eine Up/Download-Adresse, die Dein Antivirenprogramm verdauen kann?

AnnaA: Die primitive Variante mit individuellen Spaltenbeschriftungen.

AnnaA
AnnaA1600×865 80.3 KB

AnnaB: Mit BEREICH.VERSCHIEBEN - Der Zeilenindex muss jeweils von Hand eingegeben werden.

AnnaB
AnnaB1600×865 90.5 KB

AnnaC: Der Zeilen-Index wird mit VERGLEICH() berechnet.

AnnaC
AnnaC1600×865 99.7 KB

Viele Grüsse Niclaus

#8

Vergessen habe ich die Formel für die Monate bei AnnaC in B9 für die Monate:

=BEREICH.VERSCHIEBEN($B$1;0;(SPALTE()-2)*5)

Diese Formel kann man nach rechts verschieben.
Grüsse Niclaus

#9

Wow, vielen lieben Dank für deine Mühe! :sunflower: :sunflower: :sunflower:

Ich hab das gleich mal in meiner Excel Mappe ausprobiert. Zwar kommt jetzt keine Fehlermeldung mehr, aber Excel zeigt mir nun nicht das Ergebnis an, dass ich möchte (Vertrauen ist gut, Kontrolle ist besser :smiley:)
Vom Aufbau der Tabelle her ist es ja im Prinzip egal, ab wann die Matrix beginnt?

Test1

Wenn ich nun also mit Hilfe deiner Formel nach der ersten Zahl suchen möchte, also hier die 160 (Ist die Summe aus allen Filialen), dann komme ich zwar auf ein Ergebnis, aber nicht auf diese 160, sondern auf 146 (Ich hab alles abgesucht, nirgends ist etwas mit 146 aufgeführt). Von den Zeilen her bleibt es sich gleich, da „Feb 16“ in der 1. Zeile steht, „Einkauf“ also dann in T2. In den darunter liegenden Zeilen dann immer die Werte je Filiale.

Also entweder ich kann deine Formel nicht anwenden, oder habe hier irgendwo einen Denkfehler drin :disappointed_relieved:

Meine Formel hätte ich übrigens so aufgebaut:
=WVERWEIS($B42;BEREICH.VERSCHIEBEN(‚Tabelle2‘!$T$2;0;(SPALTE()-2)*5;5;5);2;FALSCH)

Nochmals vielen lieben Dank für all die Mühe :wink:

#10

Hallo Anna
So kanns mit BEREICH.VERSCHIEBEN nicht gehen! T:X ist ein ganz anderes „Matrix-Paket“. Meine Pakete sind: B:F / G:K / L:M und Q:T usw.
Um das zu ändern, müsste ich genau wissen, wie Deine Tabellen aufgebaut sind! Es war schon mühsam genug, aufgrund Deiner früheren Angaben meine Tabelle zu erstellen! Falls Du ein PrintScreen machst, achte bitte darauf, dass auch die Zeilen-Nummern erscheinen.
Im Prinzip ist es schon egal, wie die Tabelle aufgebaut ist - aber eben nur im Prinzip! Für die Details ist Kontrolle (und Anpassung) nicht nur besser sondern sogar notwendig!
Funktioniert es denn wenigstens, wenn Du meine Tabelle genau so nachkonstruierst, wie ich sie erstellt habe?
Viele Grüsse Niclaus