Die heutige Frage aus dem Atlanta Power Excel-Seminar.
Ich habe Daten mit jeder zweiten Zeile in Grau formatiert. Wenn ich zwei neue Zeilen in die Mitte der Daten einfüge, werden beide neuen Zeilen grau, anstatt dem Muster zu folgen.
Schauen Sie sich die Arbeitsmappe im Bild unten an. Die geradzahligen Zeilen sind grau gefüllt.
Wenn Sie A5: A6 und Zeilen einfügen auswählen, erhalten Sie zwei neue leere Zeilen. Aber beide Zeilen sind grau.
Ich habe eine Lösung dafür, aber es erfordert eine Reise zurück in die 3. Klasse Mathematik.
In der 2. oder 3. Klasse, als Sie die erste Lernabteilung waren, können Sie sich daran erinnern:
- 4 geht auf 9: 2 mit einem Rest von 1 oder 2R1
- 5 Geht zu 17: 3 mit einem Rest von 2 oder 3R2
In der Grundschule nannten Sie dies einen Rest. Ich finde, viele Leute erinnern sich daran.
Aber später, in der High School, als die meisten von uns aufgehört hatten, sich mit Mathematik zu beschäftigen, führten sie das Konzept namens Modulo ein.
17 Modulo 5 ist 2.
Modulo leitet sich vom lateinischen Wort Modulus ab.
Auf Englisch heißt es: "Teilen Sie 5 in 17 und der Rest ist 2"
In Excel können Sie ein Modulo mit berechnen =MOD(17,5). Hier sind einige MOD-Ergebnisse:
In diesem Artikel geht es darum, Zeilen grau zu schattieren. Warum die ganze Mathe-Theorie? Weil MOD dieses Problem löst!
Schauen Sie sich Spalte A unten an. Die =ROW()Funktion sagt Ihnen, in welcher Zeile Sie sich befinden.
Und Spalte C teilt die Nummer 2 in die Zeilennummer und gibt den Rest an. Bei geraden Zeilen beträgt der Rest 0. Bei ungeraden Zeilen beträgt der Rest 1.
Richten Sie die bedingte Formatierung ein, um zu überprüfen, ob MOD(ROW(),2)=0:
- Wählen Sie alle Zellen in Ihren Daten aus.
- Home, Bedingte Formatierung, Neue Regel.
- Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen.
- Die Formel lautet
=MOD(ROW(),2)=0 - Klicken Sie auf Format… und wenden Sie eine graue Füllung an.
- OK klicken.
Jede zweite Zeile ist grau formatiert. Aber - der schöne Vorteil - wenn Sie neue Zeilen einfügen, behalten sie die abwechselnde grau / weiße Farbe bei. (Schauen Sie sich das Video unten an, um dies in Aktion zu sehen.)
Sie können dieses Konzept erweitern. Angenommen, Sie möchten zwei Reihen Grün, gefolgt von zwei Reihen Weiß. Berechnen =MOD(ROW(),4). Die vier möglichen Antworten sind 0, 1, 2 und 3. Richten Sie eine Regel ein, um zu testen, ob das Ergebnis <2 ist, und formatieren Sie diese Zeilen in Grün.
Schau Video
Video-Transkript
Lernen Sie Excel aus Podcast Episode 2209: Einfügen alternierender Zeilen.
Hey, willkommen zurück im MrExcel Netcast, ich bin Bill Jelen. Dies ist ein Klassiker - ich war unten in Atlanta und jemand beschrieb, dass er eine solche Tabelle hatte. Nun, hey, ich möchte nicht hören, dass Sie Ihre Tabelle nicht so aufbewahren sollten. Sie hatten eine Tabelle wie diese und sagten: "Hier ist unser Problem. Wir haben 2 physische Zeilen für alle Daten, und ob wir das tun sollen oder nicht, wenn ich 2 Zeilen einfüge, Alt + I + R und 2 Zeilen einfügen, Alle 3 Reihen werden grau! " Ich sagte: "Ah, ja, ich habe eine Lösung dafür."
Bevor wir jedoch zu dieser Lösung gelangen, müssen wir zur dritten Klasse / zweiten Klasse zurückkehren, wenn Sie zum ersten Mal lernen, wie man teilt. Merk dir das? 4 geht in 9: 9/4 und die Antwort wäre 2 mit einem Rest von 1. 4 geht 2 mal in 9; 4 × 2 = 8; Es ist noch 1 übrig. Also würden wir 2R1 schreiben. 5 geht 3 mal in 17 mit einem Rest von 2; 3R2. Du erinnerst dich an Reste, oder? Das liegt daran, dass wir alle in der dritten Klasse aufgepasst haben. Dieses Zeug war in der dritten Klasse hart. In der High School hat niemand mehr auf Mathematik geachtet - wir haben auf das hübsche Mädchen geachtet, das vor uns sitzt - und Sie erinnern sich nicht, wann Ihr Mathematiklehrer Sie über etwas unterrichtet hat, das Modulo heißt.
Modulo ist wie Reste. 17 Modulo 5 ist 2, weil 2 der Rest ist, wenn Sie 5 in 17 teilen. Sie erhalten 3 mit einem Rest von 2. Wenn wir also nach = MOD (17,5) gefragt haben, lautet die Antwort 2. MOD steht für Modulo, was eigentlich nur ein ausgefallener Name für den Rest ist. Das ist der Rest. Okay, warum all das Gerede über Modulo?
Also werde ich hier eine Funktion verwenden = ROW, und = ROW sagt mir nur, in welcher Zeile es sich befindet (3, 4, 5, 6, 7, 8, 9, 10, 11, 12…) und dann ich ' Ich nehme das = MOD dieser Zeile, geteilt durch 2. Okay, also geht 2 mit einem Rest von 1 zu 3 1 Mal. 2 geht zweimal zu 4 mit einem Rest von 0. Diese Formel hier wird geben mir nur 2 mögliche Antworten: entweder 0 oder 1, 0 oder 1. Und das Schöne ist, wenn ich eine neue Zeile einfüge, diese Nullen oder Einsen - sie werden sich anpassen. Im Grunde genommen gibt mir eine 1 jede ungerade Zeile und eine 0 jede gerade Zeile. Es sieht so aus, als ob ich hier die geraden Zeilen hervorheben möchte. Also werde ich nur all diese Daten auswählen und mich mit der bedingten Formatierung befassen. Zuhause; Bedingte Formatierung; Neue Regel; Zelle formatieren; Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen. und ich'Ich werde fragen = MOD (ROW (), 2) = 0 und wenn ja, dann möchte ich das mit einem grauen Hintergrund formatieren - Füllung aus Grau; OK klicken; OK klicken; und bam - jede zweite Reihe ist grau, genau wie wir in Atlanta angefangen haben, bevor ich diese beiden Reihen eingefügt und alles vermasselt habe. Aber das Schöne daran ist, wenn ich mich entscheide, mehr Daten zu benötigen, und 2 Zeilen einfüge - Alt + I + R -, die Zeilen sind wie gewünscht in Weiß und Grau formatiert. Und ich könnte hier sogar 2 Zeilen einfügen - Alt + I + R - und es wird immer noch in Weiß und Grau formatiert, wie ich es wollte. Modulo - es ist ein Rest.Genau wie wir in Atlanta angefangen haben, bevor ich diese beiden Reihen eingefügt und alles vermasselt habe. Aber das Schöne daran ist, wenn ich mich entscheide, mehr Daten zu benötigen, und 2 Zeilen einfüge - Alt + I + R -, die Zeilen sind wie gewünscht in Weiß und Grau formatiert. Und ich könnte hier sogar 2 Zeilen einfügen - Alt + I + R - und es wird immer noch in Weiß und Grau formatiert, wie ich es wollte. Modulo - es ist ein Rest.Genau wie wir in Atlanta angefangen haben, bevor ich diese beiden Reihen eingefügt und alles vermasselt habe. Aber das Schöne daran ist, wenn ich mich entscheide, mehr Daten zu benötigen, und 2 Zeilen einfüge - Alt + I + R -, die Zeilen sind wie gewünscht in Weiß und Grau formatiert. Und ich könnte hier sogar 2 Zeilen einfügen - Alt + I + R - und es wird immer noch in Weiß und Grau formatiert, wie ich es wollte. Modulo - es ist ein Rest.
Okay, jetzt, wo wir das hier haben, machen wir zwei Reihen hoch. Früher, als wir unsere Green Bar-Berichte druckten, waren unsere Green Bar-Berichte nicht 1 Zeile, 1 Zeile, 1 Zeile, sondern 2 Zeilen, 2 Zeilen oder vielleicht sogar 4 Zeilen, 4 Zeilen. In Ordung? Wenn ich also 2 Zeilen hoch machen möchte - hier ist unsere = ROW-Funktion, und dann nehme ich das = MOD dieser Zeilennummer und dividiere es durch 4, und wir ziehen beide nach unten und sehen, was wir bekommen. Wir werden jetzt 4 mögliche Antworten erhalten - 0, 1, 2 oder 3. Die ganze Frage ist also: Ist = MOD (ROW, 4) = 0 oder 1, 2 oder 3? Deine Entscheidung. Wählen Sie alle diese Daten hier unten aus. Alt + O + D für bedingte Formatierung; Neue Regel; "Verwenden Sie eine Formel"; dann sagen wir = MOD (ROW (), 4) = <2 (also sind die Auswahlmöglichkeiten 0, 1, 2, 3. Ich möchte jeden anderen hervorheben, also = <2; das heißt, ist es 0 oder 1? ); und wenn ja, ich 'Ich werde das gleiche Grün wählen, das wir in den 1980er Jahren in den IBM Druckern hatten (ja, ich bin so alt). OK klicken; OK klicken; und BAM! Wir haben jetzt jede Zeile 2 hoch, dann 2 groß, dann 2 groß, 2 groß, und wieder funktioniert es wunderbar, wenn ich eine ganze Reihe weiterer Reihen einfüge. Ich halte das grüne Balkenformat ganz unten.
Ich liebe es, wenn ich eine großartige Excel-Frage bekomme, deren Antwort ich kenne, und die Antwort beinhaltet eine Art seltsame, geekige Mathematik, denn jetzt ist es geekiges Excel und geekige Mathematik. Das Wort Modulo - Ich weiß nicht, ob es lateinisch oder griechisch ist oder wer weiß, was es ist, aber es löst dieses Problem.
MrExcel LIVe: Die 54 besten Excel-Tipps aller Zeiten. Klicken Sie dort oben rechts auf das "Ich", um mehr über dieses Buch zu erfahren.
Zusammenfassung dieser Episode vom Gulf South Council beim IMA-Seminar in Atlanta - hey, ich habe Daten, die mit jeder zweiten grauen Reihe formatiert sind; Wenn ich zwei Zeilen einfüge, sind beide Zeilen grau. Ich habe eine großartige Lösung mit bedingter Formatierung, benötige aber Mathematik der dritten Klasse. Teilen Sie die Zeilennummer durch 2. Was ist der Rest? Es wird entweder 0 oder 1 sein. Richten Sie also die bedingte Formatierung ein, prüfen Sie, ob = MOD (ROW (), 2) = 1, und füllen Sie diese Zeile mit Grau, falls dies der Fall ist. Es funktioniert großartig.
Laden Sie die Arbeitsmappe herunter - die URL befindet sich dort unten in der Youtube-Beschreibung - und richten Sie eine = MOD-Funktion ein, um 3 Zeilen in Orange und 1 Zeile in Blaugrün hervorzuheben, und das ist richtig. Mit der Funktion = MOD können Sie auch schrecklich aussehende Arbeitsmappen erstellen. Ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Laden Sie die Excel-Datei herunter
So laden Sie die Excel-Datei herunter: inserting-alternating-rows.xlsx
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Zwei Tabellenkalkulationen sind besser als keine."
Jordan Goldmeier
