Ich habe mehr als 10.000 Datenzeilen, die ich für eine Datenspalte berechnen muss. Ich habe die Berechnung in einer Zelle vorgenommen und muss nun die Formel für die Berechnung und alle Zellen ausfüllen.
Normalerweise wähle ich einfach die Zellen mit der Tastatur aus und drücke dann das Ctrl+ D, um die Zellen zu füllen. Jetzt muss ich dies jedoch wiederholt tun, und es ist schwierig, 5000 Zeilen pro Datei auszuwählen. Gibt es eine einfachere Möglichkeit, den Bereich über die Tastatur festzulegen? Vielleicht sogar ein Makro, da alle Dateien eine feste Länge haben
Da ich aus medizinischen Gründen Schwierigkeiten mit der Maus habe, habe ich diese spezielle Lösung nicht getestet, bei der auf einen sehr kleinen Füllpunkt geklickt wird. Möglicherweise gibt es dazu eine parallele Lösung über die Tastatur, oder ich kann ein Makro erstellen, das automatisch auf einen Füllpunkt einer ausgewählten Zelle klickt. Bitte geben Sie an, ob es einen Weg gibt.
Angenommen, die Daten befinden sich in Spalte Aund die Formel in Spalte B:
B1B1AB).Duplikate:
Sie müssen die Formel jedoch nicht für alle Zellen ausfüllen. Die Verwendung einer Array-Formel mit mehreren Ergebnissen wäre besser. Geben Sie einfach die Formel wie gewohnt ein, aber ersetzen Sie die Zellen durch den Bereich (z . B. B1mit B1:B10000. Das ist ein bisschen zu stark vereinfacht, aber das ist die Idee, lesen Sie unten für weitere Details) und drücken SieCtrlShiftEnter dann + + . Die Formel wird dann sofort auf alle Zellen in der Tabelle angewendet. Es sind nur wenige Tastenanschläge erforderlich
Warum Arrayformeln verwenden?
Wenn Sie Erfahrung mit der Verwendung von Formeln in Excel haben, wissen Sie, dass Sie einige recht komplizierte Vorgänge ausführen können. Sie können beispielsweise die Gesamtkosten eines Darlehens über eine beliebige Anzahl von Jahren berechnen. Sie können Array-Formeln verwenden, um komplexe Aufgaben auszuführen, z.
- Zählen Sie die Anzahl der Zeichen, die in einem Zellbereich enthalten sind.
- Summe nur Zahlen, die bestimmte Bedingungen erfüllen, z. B. die niedrigsten Werte in einem Bereich oder Zahlen, die zwischen einer oberen und einer unteren Grenze liegen.
- Addiere jeden n-ten Wert in einem Bereich von Werten.
Arrayformeln bieten auch diese Vorteile:
- Konsistenz: Wenn Sie auf eine der Zellen aus E2 nach unten klicken, wird dieselbe Formel angezeigt. Diese Konsistenz kann zu größerer Genauigkeit beitragen.
- Sicherheit: Sie können eine Komponente einer Matrixformel mit mehreren Zellen nicht überschreiben. Klicken Sie beispielsweise auf Zelle E3 und drücken Sie die Entf-Taste. Sie müssen entweder den gesamten Zellbereich auswählen (E2 bis E11) und die Formel für das gesamte Array ändern oder das Array unverändert lassen. Als zusätzliche Sicherheitsmaßnahme müssen Sie Ctrl+ Shift+ drücken Enter, um die Änderung der Formel zu bestätigen.
- Kleinere Dateigrößen: Sie können oft eine einzelne Arrayformel anstelle von mehreren Zwischenformeln verwenden. Die Arbeitsmappe verwendet beispielsweise eine Matrixformel, um die Ergebnisse in Spalte E zu berechnen. Wenn Sie Standardformeln verwendet hätten (z. B. = C2 * D2, C3 * D3, C4 * D4…), hätten Sie zur Berechnung 11 verschiedene Formeln verwendet die gleichen Ergebnisse.
Die Array-Formel ist auch schneller, da das Zugriffsmuster bereits bekannt ist. Anstatt elf verschiedene Berechnungen separat durchzuführen, können sie vektorisiert und parallel ausgeführt werden, wobei mehrere Kerne und eine SIMD-Einheit in der CPU verwendet werden
Wenn Sie beispielsweise möchten, dass Zellen in Spalte D das Produkt von Zellen in B und C enthalten, und Spalte E die Summe von B und C enthält, verwenden Sie anstelle von D1 = B1*C1und E1 = B1 + C1und ziehen Sie die unten stehenden Formeln in D1 bzw. E1 und drücken Sie Ctrl+ Shift+Enter
=B:B*C:C =B:B + C:C In Google Blatt müssen Sie verwenden ARRAYFORMULAFunktion anstelle der Verknüpfung wie in Excel
=ARRAYFORMULA(B:B*C:C) =ARRAYFORMULA(B:B + C:C) Danach wird die Formel automatisch auf die gesamte Spalte angewendet, ohne etwas zu ziehen. X:Xbezieht sich auf Spalte X. Wenn Sie nur von Zeile 3 bis zur Endanwendung einschließen möchten X3:X. Oder wenn Sie das Ergebnis nur für Zellen von X3 bis X10003 berechnen möchten, verwenden Sie X3:X10003. Dieselbe Syntax kann für Zeilen verwendet werden, dh 1: 1 für Zeile 1. Hier ein weiteres Beispiel aus der Excel-Dokumentation, in der das Produkt in jeder Spalte berechnet wird
Array-Formel ist ein sehr leistungsfähiges Werkzeug. Verwenden Sie es jedoch mit Vorsicht. Jedes Mal, wenn Sie es bearbeiten müssen, dürfen Sie nicht vergessen, Ctrl+ Shift+ zu drückenEnter
Weitere Informationen finden Sie unter Erstellen einer Arrayformel
Drücken Sie ALT+ F11, um den VBA-Bildschirm zu öffnen. Dieser Code sollte die Formel abschreiben, ändern Sie einfach den Bereich in die richtigen Zellen
Range("B1:B10000").FillDown Bearbeiten
Oder fügen Sie einfach die Formel mit VBA hinzu
ActiveSheet.Range("B1:B10000").formula = "=A2+2"