Wie kopiere ich Formeln, wenn dazwischen leere Zeilen sind, ohne die Reihenfolge der verknüpften Blätter zu verlieren?

Question

Ich habe eine Excel-Datei mit zwei Blättern.

• Zusammenfassung
• Rohdaten

Mein Zusammenfassungsblatt enthält Daten wie diese, wobei für jede nächste Datenzeile eine Lücke von einigen Zeilen besteht

Das Problem besteht nun darin, dass ich, wenn ich ein Blatt mit mehr als 1.000 Zeilen habe und darin etwas ändern möchte, jeden Zellverweis manuell bearbeiten muss, da dadurch nicht der Verweis des RawData-Blattes fortgesetzt wird, sondern die Zellnummer als Verweis des Zusammenfassungsblatts abgerufen wird.

Wenn ich im angehängten Beispielbild die Formel kopiere und einfüge, erhalte ich die Zellennummern 2, 5, 8, 11 usw., abhängig vom Zeilenabstand. Ich muss die Formeln jedoch wie in der manuellen Formelspalte angezeigt kopieren, also in der Reihenfolge B2, B3, B4, B5, unabhängig von der aktuellen Blattzeilennummer. Habe vieles probiert, aber keine Lösung gefunden. Kann mir jemand helfen?

Um Verwirrung zu vermeiden, erstelle ich aus meinem Blatt ein realistischeres Beispiel mit einem Screenshot der Registerkarte „RawData“.

Rohdatenblatt: - RawData-Blatt

Hier ist das verknüpfte Blatt, in dem ich Daten vom RawData-Blatt verknüpfe Zusammenfassung des Produktblatts

Wie Sie sehen, werden beim Kopieren und Einfügen der Formel Testprodukt 1, Testprodukt 6 und Testprodukt 11 angezeigt, obwohl es eigentlich Testprodukt 1, 2 und 3 sein sollten.

Hier ist der Link zum Beispiel einer Excel-Dateihttps://we.tl/t-3F3mJra7pe

Answer 1

Lassen Sie mich zunächst den Grund für das Problem beschreiben.

• AnZusammenfassungIn der Zelle F3gibt die Formel =Rawdata!A2den Wert zurück Test Product 1.
• Sobald Sie kopierenZusammenfassungsblätterDaten von B3:F7at B8, Excel liest6 Reihenzwischen F3:F8und kehrt Test Product 7von Cell B8ofRohdatenBlatt.

Das hat die ganze Übung verdorben.

Die Lösung:

• Du brauchstHilfsspalteInRohdatenBlatt.

• Zelle neu schreiben B3inZusammenfassungBlatt mit Einfaches Produkt 1.
• Geben Sie diese Formel in die Zelle ein F3inZusammenfassungBlatt.

=VLOOKUP(B3,RawData!A$2:E$12,2,FALSE)

Du bekommst das.

• Jetzt,KopierenReichweite B3:F7undPasteauf dem Handy B8.

Du bekommst das.

• Zelle neu schreiben B8mitEinfaches Produkt 2, jetzt erhalten Sie die richtige Reihenfolge.

• Wiederholen Sie dieKopieren Einfügenmit KorrekturEinfache ProdukteSequenz erhalten Sie die gewünschten Ergebnisse.

Sie können die Zellbezüge in der Formel nach Bedarf anpassen.

Answer 2

In solchen Szenarien wäre meiner Meinung nach die Verwendung der Funktion OFFSET() eine einfachere, aber dennoch robuste Lösung. Offset gibt einen Verweis auf eine einzelne Zelle oder einen Zellbereich zurück. In Anbetracht Ihres Szenarios würde ich diesen OFFSET wie folgt implementieren, wenn sich das erste Kopieren und Einfügen der Formel auf die Rohdaten von Produkt1 und das zweite Kopieren und Einfügen auf Produkt2 beziehen sollte, unabhängig davon, wie viele Zeilen bei diesen Kopieren und Einfügen übersprungen würden:

A B               C      D        E       F
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B6);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B11);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B16);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large

Beachten Sie, dass sich beim Kopieren der Formel "=OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)" der Verweis auf "Summary!$B$1:B1" automatisch mit demselben Verhalten ändert, das Sie NICHT WOLLEN; aber sobald dieser Verweis in einer COUNTA()-Formel verwendet wird, funktioniert er und Sie müssen nicht Tausende von Verweisen selbst ändern. Dabei zählt COUNTA(), wie oft ein "einfaches Produkt" seit dem Beginn der Spalte vorkommt, und verwendet diesen internen Offset, um auf das jeweilige Produkt in der Ordinalposition zu verweisen. Und das alles, ohne dass zusätzliche Spalten erstellt werden müssen. Das Ergebnis ist wie folgt:

A B               C      D        E       F
  Simple Product  FALSE  TRUE             TestProduct1
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct2
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct3
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct4
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large