Bis heute erinnere ich mich manchmal mit einem Schmunzeln an eine meiner ersten Field Corporate Trainings vor 10 Jahren.
Imagine: the open space office of the representative office of an international FMCG company, huge as a football field. Chic design, expensive office equipment, dress code, expats cooing in the corners – that’s all 🙂 In one of the meeting rooms, I start a two-day advanced training on the then current version of Excel 2003 for 15 key employees of the economic department, along with their leader. We get acquainted, I ask them about business tasks, problems, I ask them to show several typical work files. They show the kilometer length of unloading from SAP, the sheets of reports that they make on this, etc. Well, it’s a familiar thing – I mentally figure out topics and timing, adjust to the audience. Out of the corner of my eye, I notice how one of the participants, demonstrating a piece of his report, patiently pulls the cell with the formula down by the black cross in the lower right corner for several thousand lines, then skips the end of the table on the fly, pulls it back, etc. Unable to stand it, I interrupt him curling the mouse around the screen and show a double-click on the black cross, explaining about auto-completion down to the stop.
Plötzlich bemerke ich, dass das Publikum verdächtig still ist und mich alle seltsam angucken. Ich schaue mich unmerklich um, wo ich kann – alles ist in Ordnung, meine Arme und Beine sind an Ort und Stelle, mein Hosenschlitz ist zugeknöpft. Ich spule meine letzten Worte auf der Suche nach einer schrecklichen Klausel zurück – es scheint nichts Kriminelles zu geben. Danach steht der Gruppenleiter schweigend auf, schüttelt mir die Hand und sagt mit versteinertem Gesicht: „Danke, Nikolai. Diese Ausbildung kann abgeschlossen werden.
Nun, kurz gesagt, es stellte sich heraus, dass keiner von ihnen eine Ahnung hatte, wie man auf ein schwarzes Kreuz doppelklickt und automatisch vervollständigt. Es ist historisch irgendwie passiert, dass es niemanden gab, der ihnen so eine einfache, aber notwendige Sache zeigte. Die gesamte Abteilung hat Formeln für Tausende von Zeilen manuell gezogen, die armen Kerle. Und hier bin ich. Ölszene. Der Abteilungsleiter bat daraufhin sehr darum, den Namen ihrer Firma niemandem preiszugeben 🙂
Einige Male später gab es ähnliche Situationen, aber nur bei einzelnen Zuhörern – die meisten kennen diese Funktion natürlich jetzt.
Die Frage ist anders. Nach der ersten Freude, ein so wunderbares Feature zu beherrschen, verstehen die meisten Benutzer, dass das automatische Kopieren von Formeln durch einen Doppelklick auf das schwarze Kreuz (Autocomplete-Marker) alle positiven und negativen Aspekte hat:
- Das Kopieren erfolgt nicht immer bis zum Ende der Tabelle. Wenn die Tabelle nicht monolithisch ist, dh es gibt leere Zellen in benachbarten Spalten, dann ist es nicht so, dass die Autovervollständigung bis zum Ende der Tabelle funktioniert. Höchstwahrscheinlich wird der Prozess an der nächsten leeren Zelle anhalten, bevor er das Ende erreicht. Wenn es Zellen gibt, die von etwas unterhalb der Spalte belegt sind, dann hält die Autovervollständigung genau dort an.
- Beim Kopieren Zelldesign verdirbt, denn Standardmäßig wird nicht nur die Formel kopiert, sondern auch das Format. Klicken Sie zum Korrigieren auf die Schaltfläche Kopieroptionen und wählen Sie aus Nur Werte (Ohne Format ausfüllen).
- Es gibt keine schnelle Möglichkeit, die Formel auch bequem zu dehnen nicht nach unten, sondern nach rechtsaußer mit der Hand zu ziehen. Ein Doppelklick auf das schwarze Kreuz ist nur unten.
Versuchen wir, diese Mängel mit einem einfachen Makro zu beheben.
Drücken Sie die Tastenkombination links Alt + F11 oder Taste Visual Basic Tab Entwickler (Entwickler). Neues leeres Modul über Menü einfügen Einfügen – Modul und kopiere den Text dieser Makros dorthin:
Sub SmartFillDown() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(0, -1).CurrentRegion If rng.Cells.Count > 1 Then n = rng.Cells(1).Row + rng.Rows. Count - ActiveCell.Row ActiveCell.AutoFill Destination:=ActiveCell.Resize(n, 1), Type:=xlFillValues End If End Sub Sub SmartFillRight() Dim rng As Range, n As Long Set rng = ActiveCell.Offset(-1, 0).CurrentRegion If rng.Cells.Count > 1 Then n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Type: =xlFillValues End If End Sub
Solche Makros:
- kann nicht nur nach unten füllen (SmartFillDown), sondern auch nach rechts (SmartFillRight)
- Verderben Sie nicht das Format der Zellen unten oder rechts – nur die Formel (der Wert) wird kopiert
- leere benachbarte Zellen werden ignoriert und das Kopieren erfolgt genau bis zum Ende der Tabelle und nicht bis zur nächsten Lücke in den Daten oder der ersten belegten Zelle.
Zur Vereinfachung können Sie diesen Makros über die Schaltfläche Tastaturkürzel zuweisen Makros – Optionen (Makros – Optionen) genau dort auf der Registerkarte. Entwickler (Entwickler). Jetzt reicht es aus, die gewünschte Formel oder den gewünschten Wert in die erste Zelle der Spalte einzugeben und die angegebene Tastenkombination zu drücken, damit das Makro automatisch die gesamte Spalte (oder Zeile) füllt:
Schönheit.
PS Ein Teil des Problems beim Kopieren von Formeln an das Ende der Tabelle wurde in Excel 2007 mit dem Aufkommen von „intelligenten Tabellen“ gelöst. Sie sind zwar nicht immer und nicht überall angemessen. Und rechts hat Excel das Kopieren nie gelernt.
- Was sind Makros, wie verwendet man sie, wo erhält man Visual Basic-Code und wo fügt man ihn ein?
- Intelligente Tabellen in Excel 2007-2013
- Formeln ohne Link-Shift kopieren