Dagens fråga från Atlanta Power Excel-seminariet.
Jag har data formaterade med varannan rad i grått. När jag infogar två nya rader i mitten av data, blir båda de nya raderna gråa istället för att följa mönstret.
Ta en titt på arbetsboken i bilden nedan. De jämna numrerade raderna har en grå fyllning.
När du väljer A5: A6 och Infoga rader får du två nya tomma rader. Men båda raderna är gråa.
Jag har en lösning på detta, men det kräver en resa tillbaka till matematik i tredje klass.
I 2: a eller 3: e klass, när du först lärde dig division, kanske du kommer ihåg detta:
- 4 går in i 9: 2 med en rest på 1 eller 2R1
- 5 Går in till 17: 3 med en rest på 2 eller 3R2
I grundskolan kallade du detta för en återstod. Jag tycker att många kommer ihåg detta.
Men senare, på gymnasiet, när de flesta av oss slutade uppmärksamma matematik, introducerade de konceptet som heter Modulo.
17 Modulo 5 är 2.
Modulo härrör från det latinska ordet Modulus.
På engelska står det: "Dela 5 in till 17 och resten är 2"
I Excel kan du beräkna en Modulo med =MOD(17,5). Här är några MOD-resultat:
Den här artikeln ska handla om att skugga rader i grått. Varför all matteori? Eftersom MOD löser detta problem!
Kolla in kolumn A nedan. Den =ROW()funktion talar om vad rad du är i.
Och kolumn C delar upp numret 2 till radnumret och ger resten. För jämna rader är resten 0. För udda rader är resten 1.
Ställ in villkorlig formatering för att kontrollera om MOD(ROW(),2)=0:
- Markera alla celler i dina data.
- Hem, villkorlig formatering, ny regel.
- Använd en formel för att bestämma vilka celler som ska formateras.
- Formeln är
=MOD(ROW(),2)=0 - Klicka på Format… och applicera en grå fyllning.
- Klicka på OK.
Varannan rad är formaterad i grått. Men - den vackra fördelen - när du infogar nya rader behåller de den växlande grå / vita färgen. (Kolla in videon nedan för att se detta i aktion.)
Du kan utöka detta koncept. Anta att du vill ha två rader med grönt följt av två rader med vitt. Beräkna =MOD(ROW(),4). De fyra möjliga svaren är 0, 1, 2 och 3. Ställ in en regel för att testa om resultatet är <2 och formatera dessa rader i grönt.
Kolla på video
Videoutskrift
Lär dig Excel från Podcast avsnitt 2209: Infoga alternerande rader.
Hej, välkommen tillbaka till MrExcel netcast, jag heter Bill Jelen. Den här är en klassiker-- Jag var nere i Atlanta och någon beskrev att de hade ett kalkylblad som det här. Nu, hej, jag vill inte höra att du inte ska behålla kalkylarket så här; de hade ett kalkylark som detta och de sa, "Här är vårt problem. Vi har två fysiska rader för varje data, och om vi ska göra det eller inte, när jag infogar 2 rader, Alt + I + R och infogar 2 rader, alla 3 rader som är freaking blir grå! " Jag sa, "Ah, ja, jag har en lösning på det här."
Men innan vi kommer till den lösningen måste vi gå tillbaka, som tredje klass / andra klass, när du först lär dig att dela. Kom ihåg det här? 4 går in i 9: 9/4, och svaret skulle vara 2 med en återstod på 1. 4 går till 9 2 gånger; 4x2 = 8; det finns 1 kvar. Så vi skulle skriva 2R1. 5 går in i 17 tre gånger med en återstod på 2; 3R2. Du kommer ihåg rester, eller hur? Det beror på att vi alla uppmärksammade i tredje klass. Det här var svårt tillbaka i tredje klass. På gymnasiet uppmärksammade ingen längre matematik - vi uppmärksammade den vackra flickan som satt framför oss - och så kommer du inte ihåg när din matematiklärare lärde dig om något som heter Modulo.
Modulo är precis som resten. 17 Modulo 5 är 2 eftersom 2 är resten när du delar 5 in till 17. Du får 3 med en återstod på 2. Så om vi bad om = MOD (17,5) kommer svaret att vara 2. MOD står för Modulo, som egentligen bara är något fint namn för resten. Det är resten. Okej, varför allt detta pratar om Modulo?
Så jag ska använda en funktion här = ROW, och = ROW berättar bara för vilken rad den är i (3, 4, 5, 6, 7, 8, 9, 10, 11, 12 …) och sedan jag ' m kommer att ta = MOD för den raden, dividerat med 2. Okej, så 2 går in till 3 1 gång med en återstod på 1. 2 går i 4 två gånger med en återstod på 0. Denna formel här kommer att ge mig bara 2 möjliga svar: antingen 0 eller 1, 0 eller 1. Och det vackra är att när jag infogar en ny rad, kommer dessa nollor eller enor - de kommer att justera. Så i grund och botten kommer en 1 att ge mig varje udda rad, och en 0 kommer att ge mig varje jämn rad. Det ser ut som att jag här vill markera de jämna raderna. Så jag ska bara välja all denna information och jag går in i villkorlig formatering. Hem; Villkorlig formatering; Ny regel; Formatcell; Använd en formel för att bestämma vilka celler som ska formateras; och jag'm kommer att fråga = MOD (ROW (), 2) = 0 och, om det är det, så vill jag formatera det med en grå bakgrund - fyll med grå; klicka OK; klicka OK; och bam - varannan rad är grå, precis som vi började med i Atlanta innan jag satte in de två raderna och skruvade upp allt. Men det vackra med det här är när jag bestämmer att jag behöver mer data, och jag infogar 2 rader - Alt + I + R - raderna är formaterade i vitt och grått, som jag ville. Och jag kan till och med infoga 2 rader här - Alt + I + R-- och det fortsätter fortfarande att formateras i vitt och grått som jag ville. Modulo-- det är en rest.precis som vi började med i Atlanta innan jag satte in de två raderna och skruvade upp allt. Men det vackra med det här är när jag bestämmer att jag behöver mer data, och jag infogar 2 rader - Alt + I + R - raderna är formaterade i vitt och grått, som jag ville. Och jag kan till och med infoga 2 rader här - Alt + I + R-- och det fortsätter fortfarande att formateras i vitt och grått som jag ville. Modulo-- det är en rest.precis som vi började med i Atlanta innan jag satte in de två raderna och skruvade upp allt. Men det vackra med det här är när jag bestämmer att jag behöver mer data, och jag infogar 2 rader - Alt + I + R-- raderna är formaterade i vitt och grått, som jag ville. Och jag kan till och med infoga 2 rader här - Alt + I + R-- och det fortsätter fortfarande att formateras i vitt och grått som jag ville. Modulo-- det är en rest.
Okej, nu när vi har den där nere, låt oss göra två rader långa. Tillbaka på dagen, när vi skrev ut våra Green Bar-rapporter, våra Green Bar-rapporter - de var inte 1 rad, 1 rad, 1 rad, det var 2 rader, 2 rader eller kanske till och med 4 rader, 4 rader. OK? Så, om jag vill göra två rader långa - här är vår = ROW-funktion, och sedan tar jag = MOD för det radnumret och delar det med 4, så drar vi båda dessa ner och ser vad vi får. Vi kommer att få 4 möjliga svar nu - 0, 1, 2 eller 3. Så, hela frågan är, är = MOD (RAD, 4) = 0 eller 1, 2 eller 3? Ditt val. Välj all denna information här nere; Alt + O + D för villkorlig formatering; Ny regel; "Använd en formel"; då säger vi = MOD (RAD (), 4) = <2 (så, valen är 0, 1, 2, 3. Jag vill markera varannan, så = <2; det betyder att det är 0 eller 1? ); och om det är, jag 'Jag ska välja samma green som vi hade tillbaka i IBM-skrivarna på 1980-talet (ja, jag är så gammal); klicka OK; klicka OK; och BAM! vi har nu att varje rad är 2 lång, sedan 2 lång, sedan 2 lång, 2 lång, och igen, det fungerar vackert om jag sätter in en hel massa fler rader, jag håller det gröna stapelformatet hela vägen ner.
Jag älskar när jag får en bra Excel-fråga som jag vet svaret, och svaret involverar någon sorts konstig, nördig matte för nu är det nördigt Excel och nördigt matte. Ordet Modulo-- Jag vet inte om det är latin eller grekiskt, eller vem vet vad det är, men det löser det problemet.
MrExcel LIVe: De 54 bästa Excel-tipsen genom tiderna. Klicka på det "jag" uppe i det övre högra hörnet för att läsa mer om den boken.
Avslutning av detta avsnitt från Gulf South Council vid IMA-seminariet i Atlanta - hej, jag har data formaterade med varannan rad grå. När jag infogar två rader är båda raderna grå. Jag har en bra lösning med villkorlig formatering men kräver matematik i tredje klass. Dela radnumret med 2. Vad är resten? Det kommer antingen att vara 0 eller 1. Så ställ in villkorlig formatering, kontrollera om = MOD (RAD (), 2) = 1 och om det är, fyll sedan den raden med grått. Det fungerar fantastiskt.
Ladda ner arbetsboken - webbadressen där nere i Youtube-beskrivningen - och ställ in en = MOD-funktion för att markera 3 rader i orange och 1 rad i kricka, och det stämmer. Du kan också skapa hemskt utseende arbetsböcker, bara med funktionen = MOD. Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Ladda ner Excel-fil
För att ladda ner Excel-filen: inserting-alternating-rows.xlsx
Excel-tanke på dagen
Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på:
"Två kalkylark är bättre än inga."
Jordan Goldmeier
