Dagens spørsmål fra Atlanta Power Excel-seminaret.
Jeg har data formatert med annenhver rad i grått. Når jeg setter inn to nye rader midt i dataene, ender begge de nye radene grå i stedet for å følge mønsteret.
Ta en titt på arbeidsboken i bildet nedenfor. De jevn nummererte radene har en grå fylling.
Når du velger A5: A6 og Sett inn rader, får du to nye tomme rader. Men begge radene er grå.
Jeg har en løsning på dette, men det krever en tur tilbake til matematikk i 3. klasse.
I 2. eller 3. klasse, da du først lærte divisjon, husker du kanskje dette:
- 4 Går inn til 9: 2 med en rest på 1 eller 2R1
- 5 Går inn til 17: 3 med en rest på 2 eller 3R2
På barneskolen kalte du dette en rest. Jeg opplever at mange husker dette.
Men senere, på videregående skole, da de fleste av oss hadde sluttet å ta hensyn til matematikk, introduserte de konseptet kalt Modulo.
17 Modulo 5 er 2.
Modulo er avledet fra det latinske ordet Modulus.
På engelsk står dette: "Divide 5 in to 17 and the resta is 2"
I Excel kan du beregne en Modulo ved hjelp av =MOD(17,5). Her er noen MOD-resultater:
Denne artikkelen skal visstnok handle om skyggelegging av rader i grått. Hvorfor all matteorien? Fordi MOD løser dette problemet!
Sjekk ut kolonne A nedenfor. Den =ROW()funksjonen forteller deg hva raden du er i.
Og kolonne C deler tallet 2 inn på radnummeret og gir resten. For jevne rader er resten 0. For odde rader er resten 1.
Sett opp betinget formatering for å sjekke om MOD(ROW(),2)=0:
- Velg alle cellene i dataene dine.
- Hjem, Betinget formatering, Ny regel.
- Bruk en formel for å bestemme hvilke celler som skal formateres.
- Formelen er
=MOD(ROW(),2)=0 - Klikk på Format … og bruk en grå fylling.
- Klikk OK.
Annenhver rad er formatert i grått. Men - den vakre fordelen - når du setter inn nye rader, beholder de den vekslende grå / hvite fargen. (Sjekk ut videoen nedenfor for å se dette i aksjon.)
Du kan utvide dette konseptet. Si at du vil ha to rader med grønt etterfulgt av to rader med hvitt. Beregn =MOD(ROW(),4). De fire mulige svarene er 0, 1, 2 og 3. Sett opp en regel for å teste om resultatet er <2 og formater disse radene i grønt.
Se på video
Videoutskrift
Lær Excel fra Podcast Episode 2209: Inserting Alternating Rows.
Hei, velkommen tilbake til MrExcel netcast, jeg heter Bill Jelen. Dette er en klassiker - Jeg var nede i Atlanta, og noen beskrev at de hadde et regneark som dette. Nå, hei, jeg vil ikke høre at du ikke skal beholde regnearket ditt slik; de hadde et regneark som dette, og de sa: "Her er problemet vårt. Vi har to fysiske rader for hver data, og om vi skal gjøre det eller ikke, når jeg setter inn 2 rader, Alt + I + R og setter inn 2 rader, alle 3 radene som freaking blir grå! " Jeg sa: "Ah, ja, jeg har en løsning på dette."
Men før vi kommer til den løsningen, må vi gå tilbake til, som tredje klasse / andre klasse, når du først lærer å dele deg. Husk dette? 4 går inn i 9: 9/4, og svaret vil være 2 med en rest på 1. 4 går inn i 9 2 ganger; 4x2 = 8; det er 1 rest. Så vi ville skrevet 2R1. 5 går inn i 17 3 ganger med en rest på 2; 3R2. Husker du rester, ikke sant? Det er fordi vi alle var oppmerksomme i tredje klasse. Dette var vanskelig i tredje klasse. På videregående var det ingen som la mer vekt på matematikk - vi tok hensyn til den pene jenta som satt foran oss - og så husker du ikke når matematikklæreren din lærte deg om noe som heter Modulo.
Modulo er akkurat som resten. 17 Modulo 5 er 2 fordi 2 er resten når du deler 5 i til 17. Du får 3 med en rest på 2. Så hvis vi ba om = MOD (17,5), blir svaret 2. MOD står for Modulo, som egentlig bare er noe fancy navn for resten. Det er resten. Ok, hvorfor snakker alt dette om Modulo?
Så jeg skal bruke en funksjon her = ROW, og = ROW forteller meg bare hvilken rad den er i (3, 4, 5, 6, 7, 8, 9, 10, 11, 12 …) og så jeg ' m kommer til å ta = MOD av den raden, delt på 2. OK, så 2 går inn til 3 1 gang med en rest på 1. 2 går inn i 4 to ganger med en rest på 0. Denne formelen her kommer til å gi meg bare to mulige svar: enten 0 eller 1, 0 eller 1. Og det vakre er at når jeg setter inn en ny rad, vil de nullene eller de ene - de vil justere seg. Så i utgangspunktet vil en 1 gi meg hver odde rad, og en 0 vil gi meg hver eneste rad. Det ser ut som om jeg her vil fremheve de jevne radene. Så jeg skal bare velge alle disse dataene, og jeg skal gå inn i Betinget formatering. Hjem; Betinget formatering; Ny regel; Format celle; Bruk en formel for å bestemme hvilke celler som skal formateres; og jeg'm kommer til å spørre = MOD (ROW (), 2) = 0, og hvis det er det, så det jeg vil gjøre er at jeg vil formatere det med en grå bakgrunn - fylle grå; klikk OK; klikk OK; og bam - annenhver rad er grå, akkurat som vi startet med i Atlanta før jeg satte inn de to radene og skrudde opp alt. Men det vakre med dette er når jeg bestemmer meg for at jeg trenger mer data, og jeg setter inn 2 rader - Alt + I + R - radene er formatert i hvitt og grått, som jeg ønsket. Og jeg kan til og med sette inn 2 rader akkurat her - Alt + I + R-- og den fortsetter å formateres i hvitt og grått slik jeg ønsket. Modulo-- det er en rest.akkurat som vi startet med i Atlanta før jeg satte inn de to radene og skrudde opp alt. Men det vakre med dette er når jeg bestemmer meg for at jeg trenger mer data, og jeg setter inn 2 rader - Alt + I + R - radene er formatert i hvitt og grått, som jeg ønsket. Og jeg kan til og med sette inn 2 rader akkurat her - Alt + I + R-- og den fortsetter å formateres i hvitt og grått slik jeg ønsket. Modulo-- det er en rest.akkurat som vi startet med i Atlanta før jeg satte inn de to radene og skrudde opp alt. Men det vakre med dette er når jeg bestemmer meg for at jeg trenger mer data, og jeg setter inn 2 rader - Alt + I + R - radene er formatert i hvitt og grått, som jeg ønsket. Og jeg kan til og med sette inn 2 rader akkurat her - Alt + I + R-- og den fortsetter å formateres i hvitt og grått slik jeg ønsket. Modulo-- det er en rest.
OK, nå som vi har fått den nede, la oss gjøre to rader høye. Tilbake på dagen, da vi skrev ut våre Green Bar-rapporter, var våre Green Bar-rapporter - de var ikke 1 rad, 1 rad, 1 rad, det var 2 rader, 2 rader eller kanskje til og med 4 rader, 4 rader. Ok? Så hvis jeg vil gjøre to rader høye - her er vår = ROW-funksjon, og så tar jeg = MOD for det radenummeret og deler det med 4, så drar vi begge disse ned og ser hva vi får. Vi kommer til å få 4 mulige svar nå - 0, 1, 2 eller 3. Så, hele spørsmålet er, er = MOD (RAD, 4) = 0 eller 1, 2 eller 3? Ditt valg. Velg alle disse dataene her nede; Alt + O + D for betinget formatering; Ny regel; "Bruk en formel"; så sier vi = MOD (ROW (), 4) = <2 (altså, valgene er 0, 1, 2, 3. Jeg vil markere annenhver, så = <2; det betyr at det er 0 eller 1? ); og hvis det er, jeg 'Jeg skal velge den samme greenen som vi hadde tilbake på IBM-skriverne på 1980-tallet (ja, jeg er så gammel); klikk OK; klikk OK; og BAM! Vi har nå at hver rad er 2 høy, så 2 høy, deretter 2 høy, 2 høy, og igjen, det fungerer vakkert hvis jeg setter inn en hel haug med flere rader, jeg holder det grønne bjelkeformatet helt nede.
Jeg elsker når jeg får et flott Excel-spørsmål som jeg vet svaret, og svaret involverer en slags merkelig, geeky matematikk, for nå er det geeky Excel og geeky matematikk. Ordet Modulo-- Jeg vet ikke om det er latin eller gresk, eller hvem vet hva det er, men det løser det problemet.
MrExcel LIVe: De 54 største Excel-tips gjennom tidene. Klikk på det "jeg" der oppe i høyre hjørne for å lese mer om den boka.
Avslutning på denne episoden fra Gulf South Council på IMA-seminaret i Atlanta - hei, jeg har data formatert med annenhver rad grå; Når jeg setter inn to rader, er begge radene grå. Jeg har en flott løsning med betinget formatering, men krever matematikk i tredje klasse. Del radnummeret med 2. Hva er resten? Det blir enten 0 eller 1. Så sett opp Betinget formatering, sjekk om = MOD (ROW (), 2) = 1 og hvis det er, så fyll den raden med grått. Det fungerer fantastisk.
Last gjerne ned arbeidsboken - URL-en der nede i Youtube-beskrivelsen - og sett opp en = MOD-funksjon for å markere 3 rader i oransje og 1 rad i blågrønn, og det er riktig. Du kan også lage hederlige arbeidsbøker bare ved å bruke = MOD-funksjonen. Jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
Slik laster du ned excel-filen: inserting-alternating-rows.xlsx
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"To regneark er bedre enn ingen."
Jordan Goldmeier
