Formel waagerecht kopieren, wenn Quelldaten senkrecht stehen

Hallo liebe Excelexperten, ich haben zwei Arbeitsblätter mit Daten für Jan. bis Dez. Die Quelldaten in AB 1 stehen senkrecht (Jan bis Dez. untereinander). Die Daten im Ziel-Arbeitsblatt stehen aber waagerecht (Jan. bis Dez. nebeneinander). Nun möchte ich im AB 2 die Daten über die simple Formel =Tabelle1!B2 reinholen. Die Daten sollen als Formel stehen bleiben, da die Quelldaten sich ändern können. Wenn ich nun die Formel im AB 2 nach rechts kopieren (oder ziehen) möchte, kann ich weder über die Dollar-Zeichen noch sonst irgendwie bewerkstelligen, dass Excel die Spaltenzahlen fortsetzt (also =Tabelle1!B2, =Tabelle1!B3, =Tabelle1!B4 usw). Vor diesem Problem stehe ich sehr, sehr häufig und habe bisher immer über die Dollar-Zeichen zumindest in diesem Beispiel das B hinüber retten können. Die Ziffern musste ich dann manuell anpassen. Da gibt es doch sicher eine Lösung, oder? Ich muss noch erwähnen, dass die Quelldatei aus ganz vielen kleinen Tabellen Jan bis Dez besteht, so dass ich hier leider keine Datenbanksituation vorliegen habe, also kein Indirektbezug zu den Monaten möglich ist. Vielen Dank im Voraus und herzliche Grüße, Galathea

2 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

augsburgchris

Nutzer, der sehr aktiv auf gutefrage ist

im Thema Microsoft Excel

17.01.2011, 15:24

wenn Tabelle1 (die untereinander steht) in A1 losgeht geht es so:

=INDIREKT("Tabelle1!"&ADRESSE(SPALTE(A1);1))

das dann nach rechts rüberziehen und es werden die Daten die in Tabelle1 untereinander stehen gezogen.

wenn Tabelle1 in E9 losgeht lautet die Formel:

=INDIREKT("Tabelle1!"&ADRESSE(SPALTE(I1);5))

geht sie in B30 los:

=INDIREKT("Tabelle1!"&ADRESSE(SPALTE(AD1);2))

augsburgchris

17.01.2011, 15:37

Kurz vielleicht noch zur Erklärung:

Indirekt ist klar: gibt den Wert der Zelle des als Text angegebenen Bezugs.

("Tabelle1!": sagt das die Verknüpfung auf einen anderen Tabelle ist.

&: Verknüpft Blattname mit der Adresse

ADRESSE(: Erzeugt eine aus Spalten und Zeilennummer eine Adresse der Form $A$1

die Syntax lautet: ADRESSE(Zeile; Spalte)

du möchtest nach rechts ziehen und dabei soll sich die Zeilennummer erhöhen ergo üssen wir das Argument Zeile flexibel gestalten: diesgeht über Spalte weil sich Spalte beim nach rechts zeihen erhöht. Nun noch die Startspalte finden die im Ausgangsblatt der Zeile enspricht: bei B30 muss die Startspalte also die 30. Spalte sein, also AD. Als Spalten der ADRESSE-Formel noch 2 für Spalte B angeben und dann nach rechts rüberziehen.

Achtung dies funtkioniert nur wenn der letzte Wert in der Ausgagnstabelle vor Zeile 256 steht Also maximal für den Bereich A1:IV256

Galathea

Fragesteller

17.01.2011, 16:03

@augsburgchris

Perfekt! Wie bisher immer! Ich bin so begeistert von Deinem und Oubyis Excelwissen. Ihr helft mir echt weiter und spart mir eine Menge Zeit. Das macht so richtig Spaß! DH und Stern :-) (Sorry, dass ich bei meinem Kommentar Oubyi gleich einbezogen habe.)

augsburgchris

17.01.2011, 17:28

@Galathea

Oubyi ist perfekt keine Frage.

Galathea

Fragesteller

17.01.2011, 15:53

Vielen Dank, ausburgchris. Es hat etwas gedauert, bis ich die Formel auf meine Gegebenheiten anpassen konnte. Habe das Prinzip noch nicht verstanden. Warum z. B. heißt es in der Formel in Deinem 3. Beispiel (Beginn B30) ...(AD1);2...? Das Ergebnis stimmt, aber ich möchte es auch verstehen.

augsburgchris

17.01.2011, 17:26

@Galathea

Syntax ist =ADRESSE(Zeile,Spalte) du willst pro Zug nach rechts eine Zelle nach unten. ich habe in diesem (dummen) Beispiel in AD angefangen weil das Spalte 30 ist. die 2 steht für Spalte 2 also B.

Einfacher und übersichtlicher wird es wenn du:

=INDIREKT("Tabelle1!"&ADRESSE(SPALTE(A1)+29;2)) nimmst, da hab ich trotteligerweise zu Beginn gar nicht dran gedacht.

Hier brauchst du nur die 2 austauschen gegen deine Spalte und 29=30-1 für die Startzeile.

Sorry ist mir auch gerade erst eingefallen.

Galathea

Fragesteller

18.01.2011, 11:03

@augsburgchris

Nee, alles bestens. Bei meinem Kommentar, dass es noch Unklarheiten gibt, hatte ich Deine zweite Erklärung noch nicht erhalten. Diese klärte dann alles auf. Hast Du gut und verständlich erläutert. Die Einrückungen meiner Kommentare stimmen nicht ganz.

Anfaenger1973

21.01.2013, 18:13

@augsburgchris

Hallo augsburgchris, erstmal ein Riesenkompliment von mir. Hatte auch ein solches Problem und konnte es mit dieser tollen Formel lösen. Nur leider ist beim Bereich "IV" ja Schluss. Gibt es da eine Alternative wie man das erweitern könnte, da ich noch einiges mehr an Platz brauche bzw. eine Riesendatenmenge auf diese Art unterbringen bzw. darstellen möchte.

BPA03

06.03.2017, 22:37

Hallo,

Ich habe bei Excel 2016 das gleiche Problem, komme mit dieser Formel aber auf kein Ergebnis. Liegt es an der neuen Version?

Zu meinem Problem.

Habe im Tabellenblatt "TR" eine Liste. Und Möchte im Tabellenblatt "AT" eine Matrix erstellen. Die Liste wird aber erst befüllt.
Die Angaben aus TR möchte ich quer anordnen. Dies will ich eben Automatisieren, weil die Liste bist zu 100 Angaben erhalten wird. Ich will dann mit einfachem nach rechts ziehen, die Angaben aus der Liste (Vertikal) in der Matrix (Horizontal ) spiegeln.

Kann mir da jemand weiterhelfen? Vielen Dank!

romar1581

17.01.2011, 13:54

versuch mal Inhalte Einfügen.

dann "Formel" auswählen und unten das Häkchen bei "transponieren" setzen.

macht aus senkrechter, eine waagerechte anordnung

Galathea

Fragesteller

17.01.2011, 13:57

Nein, das funktioniert leider nicht. Auch das hatte ich bereits probiert. Aber trotzdem danke.

romar1581

17.01.2011, 14:05

Im 2. AB in Feld (Z.B. c13) =Tabelle1!B2, dann nach unten Kopieren so weit wie nötig

einzeln die zellen anwählen und F4 drücken

in AB2 den Bereich markieren und kopieren

Jetzt genau das Feld (c13) markieren und

bearbeiten

Inhalte einfügen

Formeln

transponieren anwählen.

Dann wird der Inhalt nach rechts einkopiert (c13 wird mit Inhalt von C13 überschrieben, macht nix) und du kannst C14 bis Csonstwohin löschen.

romar1581

17.01.2011, 14:13

@romar1581

Du hast recht, geht nicht! ich bitte um Entschuldigung. Ich werde versuchen mich sachkundig zu machen und dir im "Kompliment" zu antworten, wenn ich eine Lösung erfahre.

Galathea

Fragesteller

17.01.2011, 16:20

@romar1581

Ich danke Dir für Deine Mühe.

Hallo, ich habe in Excel eine Tabelle mit Werten in jedem Monat von Jan - Dez. Ich möchte nun eine Summe mit den jeweiligen Monaten bilden. D.h. im Januar soll nur der Wert aus Januar stehen, im Februar soll er Jan + Feb addieren, im März soll er Jan + Feb + Mrz addieren. Ich möchte aber nicht jeden Monat einen neue Formel eingeben müssen. Gibt es eine andere Lösung?

...zur Frage

Wie kopiere ich in Excel eine horizontale Spaltenreihe vertikal in Zeilen?

Ich versuche mal meine Frage genauer auszudrücken. Ich habe in Excel zwei Arbeitsblätter (Tabelle1 und Tabelle2). In Tabelle1 habe ich in i34folgende Formel =zählenwenn(i2:i31;"x"). Diese Formel habe ich horizontal bis z34 erweitert. Das Ergebniss aus den einzelnen Zählungen möchte ich gern in Tabelle2 vertikal e5:e35 kopieren.

Aber wenn ich in Tabelle1 die Spalte von i34:z34 kopiere und dann in Tabelle2 den Bereich e5:e35 markiere --> einfüge... Fügt er zwar die Formel ein aber der Bezug fehlt.

Habe es auch schon so ausprobiert dass ich in Tabelle2 in e5 =Tabellle1!i34 eingegeben habe und dann vertikal erweitert habe. Aber dann nimmt er die Daten aus i35 und nicht j34.....

Hoffe jemand erkennt mein Problem und hat eine Lösung für mich.

Bei Unverständlichkeit, einfach schreiben.

...zur Frage

Excle: Ausfüllformel von vertikal zu horizontal?

Aus einer Spalte mit Informationen (1), sollte in einer neuen Tabellenblatt (2) eine Auswertung stehen. Vertikarl (3) konnte ich die Daten über Kopieren hinzufügen. Wie kann ich die Daten über eine Formel (damit ich nicht 2x ausfüllen muss) automatisch horizontal hinzufügen, obwohl die Informationen (1) in der Spalte vertikal sind? Danke!

...zur Frage

Multiplikationstabelle in Calc erstellen?

Hey,

Wir lernen in der Schule aktuell die Calc grundlagen und sollen eine Multiplikationstabelle in Calc erstellen. Mit folgender aufgabenstellung. "Erstellen Sie in der Zelle B2 eine Formel, die sowohl senkrecht als auch waagerecht kopiert werden kann." Ich habe jedoch kein plan, wie ich das machen soll, ich weiß nur wie es schaffen würde die z.B Senkrecht zu kopieren. Aber keine ahnung mit welcher Formel ich jetzt beides kopieren kann.

Wäre echt lieb wenn mir irgendwer helfen kann wenn sich jemand damit auskennt.

LG :)

...zur Frage

Wie kann ich im Excel eine Formel "runterziehen", wenn die Reihenfolge in den Tabellen nicht übereinstimmt?

Hallo!

Ich versuche seit Tagen folgendes Problem zu lösen:

Ich habe eine Tabelle mit den Artikelnummern und den Bruttobeträgen, z.B:

A1 Art.Nr. B1 Brutto
A2 101 B2 844,20
A3 2456 B3 548,03
A4 756 B4 429,16

In der anderen Tabelle habe ich die Artikelnummer und die Steuersätze:

A1 Art.Nr. B1 Steuersätze
A2 101 B2 10%
A3 23 B3 20%
A4 2456 B4 13 %

Ich soll Nettobeträge berechnen. Dafür habe ich folgende Formel verwendet: =WENNS(Tabelle1!A2=Tabelle2!B2;Tabelle1!B2/(1+Tabelle2!A2))
=WENNS(101=101;844,20/(1+10%))

Hier funktioniert die Formel, da bei beiden Tabellen in A2 die gleiche Art. Nr. steht (101). Ich kann die Formel allerdings nicht einfach runterziehen, da die nächsten Artikel nicht in der gleichen Reihenfolge sind, nämlich in der ersten Tabelle Art. Nr. 2456 und bei der zweiten Tabelle Art. Nr. 23.

Ich benötige daher eine Formel, die erkennen soll, wenn in den beiden Tabellen die gleiche Art. Nr. steht, dann derjenige Steuersatz abgezogen werden soll.

Ich bedanke mich jetzt schon für eure Antworten!

...zur Frage

Excel Datum ohne Zeit kopieren?

Hi, ich bekomme es einfach nicht hin das linke Datum ohne die Zeitangabe in das rechte Feld zu kopieren (A2 ohne Zeit zu B2) und dann nochmal die Zeit von A2 in C2. Die Formatierung von A2 ist Benutzerdefiniert, ich habe sich schon probiert sie auf Datum zu stellen. In B2 steht die Formel =DATWERT(A2), aber ich bekomme immer nur WERT als Ausgabe. Wie kann ich das Datum ohne Zeit und die Zeit ohne Datum aus A2 in B2 und C2 eintragen?

...zur Frage

Muß die Waschmaschine waagerecht UND senkrecht gerade stehen?

Wir hatten vor kurzem einen Wohnungsumzug bei dem wir auch die Waschmaschine mitnahmen in der neuen Wohnung.

Seitdem funktioniert diese beim waschen nicht so richtig, insbesondere beim Anfang der Schleuderprogramme da ruckelt es lautvoll vor sich hin.

Ein Versuch meinerseits mit der Wasserwaage senkrecht und waagerecht die vier Standfüße zu vermessen, ergab ein einseitiges senkrechtes Defizit.

Kann es überhaupt das die Ursache sein für die Störungen im Waschprogramm?

Ich bin kein Handwerker daher keine Fachkenntnisse.

...zur Frage

Excel: Möglichkeit dieselbe Formel anzuwenden nur mit anderem Tabellenblattbezug?

Ich möchte in Excel ein Tabellenblatt komplett kopieren. Jedoch sollen die Formeln, die in dem neuen Tabellenblatt sind jeweils um eine Sache geändert werden. z.B.:

in Tabellenblatt1 steht in A1: =Tabellenblatt0!B2

in Tabellenblatt2 soll in A1 stehen: =Tabellenblatt3!B2

Also es ändert sich nur die Tabellenbezeichnung. Ist das so möglich, ohne selber alles manuell einzugeben?

...zur Frage

Wie kann ich in Excel Formelen kopieren, damit Excel die Bezüge auch beibehält?

Hallo zusammen,

ich habe in Excel eine Tabelle aufgebaut, in welcher ich viele Daten von einzelnen Tagen habe. Nun erweitere ich diese Tabelle immer um eine Woche. Weiter hinten in der Tabelle habe ich eine Zusammenfassung von der einzelnen Woche. Jetzt möchte ich allerdings die Wochenzusammenfassung einfach nur kopieren damit ich die Formel nicht jedes mal wieder neu eingeben muss. Excel verschiebt mir die Summen aber immer nur um eine Spalte anstatt um 7. Gibt es dafür eine Lösung?

Als Beispiel:

In Zelle MQ2 habe ich die Formel "=Summe(B2:H2)" nun möchte ich in MR2 die Formel "=Summe(I2:O2)" haben.

Excel macht mir aber leider nur daraus "=Summe(C2:I2)". Auch wenn ich mehrere Zellen markiere welche immer die gleiche Logik besitzen, dass die Summe aus 7 Zellen gebildet werden soll.

Vielen Dank

...zur Frage

Excel/VBA Kalenderwoche?

Ich habe eine Exceltabelle in der B2:B92 Daten (Datum) stehen. In Spalte A hätte ich jetzt gerne die passende KW dazu stehen was auch kein Problem ist mit =KÜRZEN((B2-DATUM(JAHR(B2-REST(B2-2;7)+3);1;REST(B2-2;7)-9))/7)

Jetzt kommt das ABER:

da in Spalte B die Daten mehrfach vorkommen (sind auch nach Datum sortiert) hätte ich gerne das die KW immer nur beim ersten Wochentag (Montag) einmal angezeigt wird und auch erst wieder beim Nächsten Montag. Und sollte das Datum in der Vergangenheit liegen Soll nur einmal "Rückstand" statt KW angezeigt werden. Gerne auch VBA lösungen falls es nicht mit einer Excel Formel funktioniert.

...zur Frage

Wie kann man eine Formel in Excel WIRKLICH unveränderlich machen?

Hallo, die absoluten Zellbezüge mit den $-Zeichen sind bekannt, aber die beziehen sich mit der Festlegung auf z.B. $B2 darauf, dass das gleiche Feld auch dann noch herangezogen wird, wenn die Formel selbst an eine andere Position verschoben/kopiert wird - soweit ist alles klar.

Meine Frage geht aber dahin, dass sich in dem Fall, wenn ich den "Wert" der Zelle B2 z.B. nach C3 verschiebe, automatisch auch der Bezug in der Formel auf $C3 verändert wird. Genau genommen bezieht sich also die Festlegung auf $B2 nicht auf die Zelle mit dieser Adresse, sondern auf den darin enthaltenen Wert - und wenn ich den verschiebe, verschiebt sich auch der Bezug in der Formel, trotz $ ! Gibt es eine Möglichkeit, den Bezug in der Formel auch dann auf B2 zu belassen, wenn die Daten aus der Zelle verschoben weden? Wenn die Zelle nach dem Verschieben des Inhalts leer ist, müßte jetzt der korrekte Inhalt "0" aus B2 geliefert werden, statt dass der Bezug in der Formel den Daten "hinterherläuft" - einmal B2, immer B2!

Weiß jemand, wie man diesen Automatismus abschalten kann? Vielen Dank für eine wirklich wertvolle Hilfe!

...zur Frage

Zellbezug bei Excel funktioniert nicht?

Ich möchte im Tabellenblatt 1 in Zelle A1 den Wert B2 aus Tabellenblatt 2 stehen haben.

Wenn ich jetzt in A1 die Formel "=Tabellenblatt2!A2" eingebe erscheint nicht der Wert aus A2 sondern die Formel "=Tabellenblatt2!A2".

Warum bekomme ich nicht den Wert aus A2, sondern nur die Formel??

...zur Frage

WENN- Funktion für ganze Spalte

Hallo,

ich möchte, dass eine WENN Funktion automatisch auf eine ganze Spalte angewendet wird und die Summe gebildet wird.

Bsp: C1=WENN(UND(ODER(Tabelle1!A1=56;Tabelle1!A1=89;Tabelle1!A1=78);Tabelle1!B1=375735);Tabelle1!J1;0)

Ich möchte nun, dass, das nun auf die ganze Spalte angwand wird (also aus A1 wird A2 aus B1 B2 und aus J1 J2) ohne die Formel ewig runterziehen zu müssen (es geht ungefähr bis A1500) und in C1(in der anderen Tabelle die Summe erscheint (also von allen J werten wo DANN eintrit)

Vielen Dank schon jetzt für die Hilfe

...zur Frage

Excel vba formel falsch,Was?

Sub test()
  Worksheets("Tabelle1").Range("A1") = worksheetfunction.iif(or(isempty(Range(A1)),isempty(Range(B2)),""(Range(A1-B2+1))
End Sub

...zur Frage

Was möchtest Du wissen?

Deine Frage stellen