Merwyn aus England hat dieses Problem eingeschickt.
Gibt es in Zelle B2 eine einzige Formel, die kopiert werden kann, um eine Multiplikationstabelle zu erstellen?
Merwyns Ziel ist es, eine einzige Formel in B2 einzugeben, die leicht in alle 144 Zellen kopiert werden kann, um eine Multiplikationsreferenztabelle zu erstellen.
Lassen Sie uns dies als Excel-Neuling angreifen und sehen, auf welche Fallstricke wir stoßen. Die erste Reaktion könnte darin bestehen, die Formel in B2 zu haben =A2*B1. Diese Formel liefert das richtige Ergebnis, ist jedoch nicht für die Zuweisung von Merwyn geeignet.
Wenn Sie diese Formel aus Zelle B2 in Zelle C2 kopieren, werden die in der Formel angegebenen Zellen auch über eine Zelle verschoben. Die Formel, die =A2*B1jetzt war, wird =C1*B2. Dies ist eine in Microsoft Excel entwickelte Funktion, die als relative Formelreferenz bezeichnet wird. Während Sie eine Formel kopieren, verschieben die Zellreferenzen in der Formel auch eine entsprechende Anzahl von Zellen hin und her.
Es gibt Zeiten, in denen Excel dieses Verhalten nicht aufweisen soll, und der aktuelle Fall ist einer davon. Fügen Sie vor dem Teil der Referenz, den Sie einfrieren möchten, ein "$" ein, um zu verhindern, dass Excel die Referenz beim Kopieren der Zellen ändert. Beispiele:
- $ A1 teilt Excel mit, dass Sie immer auf Spalte A verweisen möchten.
- B $ 1 teilt Excel mit, dass Sie immer auf Zeile 1 verweisen möchten.
- $ B $ 1 teilt Excel mit, dass Sie immer auf Zelle B1 verweisen möchten.
Durch das Erlernen dieses Codes wird die zum Erstellen von Arbeitsblättern erforderliche Zeit erheblich verkürzt. Anstatt 144 Formeln eingeben zu müssen, kann Merwyn diese Kurzform verwenden, um eine einzelne Formel einzugeben und in alle Zellen zu kopieren.
Wenn =B1*A2wir uns Merwyns Formel ansehen , erkennen wir, dass der Teil "B1" des Ausdrucks immer auf eine Zahl in Zeile 1 zeigen sollte. Dies sollte als "B $ 1" umgeschrieben werden. Der Abschnitt "A2" der Formel sollte immer auf eine Zahl in Spalte A verweisen. Dies sollte als "$ A2" umgeschrieben werden. Die neue Formel in Zelle B2 lautet also =B$1*$A2.
Nachdem ich die Formel in B2 eingegeben habe, kann ich sie kopieren und in den entsprechenden Bereich einfügen. Excel folgt meinen Anweisungen und nach dem Kopieren finde ich die folgenden Formeln:
- Zelle M2 enthält
=M$1*$A2 - Zelle B13 enthält
=B$1*$A13 - Zelle M13 enthält
=M$1*$A13
Jede dieser Formeltypen hat einen Namen. Formeln ohne Dollarzeichen werden als relative Formeln bezeichnet. Formeln, bei denen sowohl die Zeile als auch die Spalte mit einem Dollarzeichen gesperrt sind, werden als absolute Formeln bezeichnet. Formeln, bei denen entweder die Zeile oder die Spalte mit einem Dollarzeichen gesperrt ist, werden als gemischte Formeln bezeichnet.
Es gibt eine Kurzmethode zur Eingabe der Dollarzeichen. Während Sie eine Formel eingeben und eine Zellreferenz beenden, können Sie die Taste drücken, um zwischen den 4 Referenztypen umzuschalten. Angenommen, Sie haben mit der Eingabe einer Formel begonnen und Sie haben eingegeben =100*G87.
- Drücken Sie F4 und Ihre Formel ändert sich zu
=100*$G$87 - Drücken Sie erneut F4 und Ihre Formel ändert sich zu
=100*G$87 - Drücken Sie erneut F4 und Ihre Formel ändert sich zu
=100*$G87 - Drücken Sie erneut F4 und Ihre Formel kehrt zum Original zurück
=100*G87
Sie können während der Formeleingabe an jeder Zellreferenz eine Pause einlegen, um F4 zu drücken, bis Sie den richtigen Referenztyp erhalten. Die Tastenanschläge zur Eingabe der obigen Merwyn-Formel lauten =B1*A1.
Eine meiner bevorzugten Verwendungen von gemischten Formelreferenzen tritt auf, wenn ich eine lange Liste von Einträgen in Spalte A habe. Wenn ich eine schnelle und schmutzige Methode zum Auffinden von Duplikaten haben möchte, gebe ich diese Formel manchmal in Zelle B4 ein und kopiere sie dann nach unten:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Beachten Sie, dass der zweite Term der VLOOKUP-Formel sowohl eine absolute ($ A $ 2) als auch eine gemischte ($ A3) Referenz verwendet, um den Suchbereich zu beschreiben. Auf Englisch fordere ich Excel auf, immer von Zelle $ A $ 2 bis zur Zelle in Spalte A direkt über der aktuellen Zelle zu suchen. Sobald Excel auf einen doppelten Wert stößt, ändert sich das Ergebnis dieser Formel von # N / A! zu einem Wert.
Durch die kreative Verwendung der $ in-Zellreferenzen können Sie sicherstellen, dass eine einzelne Formel mit korrekten Ergebnissen in viele Zellen kopiert werden kann.
