Denne opplæringen vil demonstrere hvordan du tester formler før du bruker dem på betinget formatering i Excel.
Testing av egendefinerte formler - Excel
Når du bruker en egendefinert formel til betinget formatering i Excel, er det viktig å sørge for at formelen faktisk returnerer riktig TRUE eller FALSE -verdi i regnearket for at den betingede formateringen skal fungere korrekt.
Før vi oppretter den betingede formateringsregelen, kan vi lage formelen i et "ledig mellomrom" i Excel -arbeidsboken og sørge for at formelen fungerer som den skal.
Testing av en formel for å markere en regnearkcelle
I eksemplet ovenfor ønsker vi å skrive en formel for å se om hver celle i regnearket er større enn 5, og hvis den er større enn 5, ønsker vi å farge bakgrunnen til cellen.
Vi kan starte med den første cellen i området - i dette tilfellet B3, og vi kan deretter teste hver rad og kolonne i området - ned til E11.
Fra en enkel formel kan vi se at B3 har en verdi på 5 og derfor IKKE er større enn 5.
Vi kan deretter bruke håndtaket til å dra den formelen ned til rad 11, og legge merke til at raddelen av celleadressen (dvs. 3) endres til neste tall når vi drar formelen ned gjennom radene slik at B3 blir B4, da B5 og så videre.
Vi kan deretter dra det markerte celleområdet over 4 kolonner for å teste om cellene i kolonne C til E har en verdi større enn 5. Når vi drar formelen over, vil kolonnedelen i celleadressen endres tilsvarende - fra kolonne C til D til E til F.
Som vi nå kan se, får vi riktig TRUE eller FALSE -verdi avhengig av verdien i den tilsvarende cellen.
Vi vet nå at formelen vår er riktig, og vi kan bruke den formelen til å lage den betingede formateringsregelen. Som med vår testformel bruker vi den første cellen i området, B3.
= B3> 5
Når vi har typer i formelen, kan vi angi formatet for bakgrunnsfargen og klikke OK.
Som du kan se gjelder formelen = B3> 5 for området B3: E11. Formelcellen må alltid svare til den første cellen i området som skal formateres.
Klikk Søke om for å bruke formateringen på regnearket.
Testing av en formel for å markere en regnearkrad
Å bruke en formel for å endre bakgrunnsfargen på en rad i stedet for en kolonne er litt annerledes og litt mer komplisert.
Vurder følgende regneark.
I dette regnearket ønsker vi å markere en hel rad hvis prosjektet er forsinket - så hvis kolonne E har en celle som returnerer verdien "Forfalt" i stedet for "På tide", Ønsker vi deretter å markere hele raden som cellen er i.
Formelen for å gjøre det er enkel:
= HVIS (E4 = ”Forfalt”, SANN, FALSK)
Imidlertid, hvis vi bruker dette på vår betingede formatering, er dette det som blir returnert.
Bare den første kolonnen i området blir formatert.
La oss prøve denne formelen i vårt Excel -regneark.
Vi får ønsket resultat, SANN for når prosjektet er forsinket i kolonne E. Men hvis vi skulle kopiere denne formelen på tvers for de 5 kolonnene i området (f.eks. Kolonne B til kolonne E), ville resultatet som ble returnert være FALSE .
Formelen ville endres - så kolonne E ville endres til kolonne F - og selvfølgelig er det ingenting i kolonne F, så formelen vil returnere en FALSK hver gang.
Vi må sørge for at formelen KUN ser på kolonne E - men det ser på riktig rad - så når vi kopierer formelen på tvers, forblir kolonne E den samme. For å gjøre det må vi bruke en blandet referanse som låser kolonnen på plass.
= $ E4 = "Forfalt"
Når vi nå kopierer den over 4 kolonner, forblir kolonnen i formelen den samme, men raden endres.
Med formelen vår som nå fungerer i regnearket, kan vi lage vår tilpassede formel i betinget formatering.
Når vi klikker OK, og Søke om, våre rader som har kolonne E som sier at det er forfalt, vil bli uthevet.
