Betingede formater for Odd og Even kolonner

December 17  by Eliza

Zar har et behov for å bruke en betinget format til verdiene i kolonne A i et regneark, men han kan ikke finne ut hva reglene kan være. Foruten kolonne A, har han også data starter i kolonne B og han jevnlig legger til nye kolonner med data. Hvis det er data i alle de merkelige søyler som begynner med B, ønsker Zar ett format brukt i kolonne A. (B er data kolonne 1 for sitt regneark, slik han ser det som rart.) Hvis det er data i alle selv kolonner som begynner med C, ønsker Zar et annet format brukes i kolonne A. Hvis det er data i alle datakolonner starter med B-imidlertid mange som kan være-så han ønsker et tredje format brukt.

Som Zar har uten tvil funnet ut, kan du enkelt lage en formel for å avgjøre om det er informasjon i kolonnene B og C og bruke formatering tilsvarende. Faktisk vil en enkel formel som disse gjør susen:

= ANTALLA (B1: C1) = 2
= COUNTA (B1) = 1
= COUNTA (C1) = 1

Den første formelen returnerer True hvis det finnes informasjon i både B og C, den andre hvis det finnes informasjon i B, og den tredje hvis det finnes informasjon i C. Så lenge du velger "Stopp hvis sann" for hver regel / formel, deretter din formatering vil fungere fint.

Opprette en formel for flere kolonner utover B og C er bare marginalt mer vanskelig. De samme tre typer av formler, i rekkefølge, ville være som følger:

= ANTALLA (B1: G1) = 6)
= ANTALLA (B1, D1, F1) = 3
= ANTALLA (C1, E1, G1) = 3

Du kan enkelt legge til flere cellereferanser til formlene, etter behov. En slik tilnærming returnerer Sann i bare tre forhold: Dersom alle cellene i området B1: G1 har noe i dem, hvis ALLE Odd celler (B1, D1, F1) har noe i dem, og hvis alt selv celler (C1, E1, G1) har noe i dem. Det vil ikke returnere True hvis bare noen av cellene i området har verdier i dem. For eksempel, det er verdier i cellene B1, C1, og E1, da det ikke vil returnere sann og ingen av kriteriene for formatering vil bli oppfylt.

Mens alle disse fungerer fint med den kjente begrensning, de er ikke akkurat hva Zar er på jakt etter, han ønsker en formel som vil oppdage hvor mange kolonner blir brukt uke etter uke, som han fortsetter å legge til data i kolonner, og juster formel tilsvar uten å måtte manuelt redigere formelen for å ta hensyn til den ekstra data. Med andre ord, hvis han legger data i kolonne H, ville han ønsker formlene automatisk justeres for å ta hensyn til den ekstra kolonne:

= ANTALLA (B1: H1) = 7)
= ANTALLA (B1, D1, F1, H1) = 4
= ANTALLA (C1, E1, G1) = 3

Det er åpenbart en mer kompleks behov. Kanskje den beste måten å nærme seg problemet er å lage en brukerdefinert funksjon (en makro) som kan se på et utvalg av celler og finne ut om en av de tre kriteriene er oppfylt. Vurdere følgende makro:

Funksjon CellChk (crng Som Range) As String
Dim iNumOdds As Integer
Dim iNumEvens As Integer
Dim iOdds As Integer
Dim iEvens As Integer
Dim iTots As Integer
Dim iTotCells As Integer
Dim rWork Som Range
Dim Rceii Som Range
Dim iLastCol As Integer
Dim STEMP As String

iOdds = 0
iEvens = 0
iTots = 0

"Finne ut den virke siste kolonnen i regnearket og innstilte området
iLastCol = ActiveSheet.Cells.Find (Hva: = "*", _
Search: = xlByColumns, SearchDirection: = xlPrevious, _
Lookin: = xlFormulas) .Column
Satt rWork = Range (Cells (crng.Row, 2), Celler (crng.Row, iLastCol))

iTotCells = rWork.Count
iNumOdds = (iTotCells + 1) \ 2 'Antall Odd kolonner
iNumEvens = iTotCells - iNumOdds 'Antall selv kolonner

For Hver Rceii I rWork
Hvis Rceii <> "" Then
If ((rCell.Column - 1) Mod 2) = 1 Deretter
iOdds = iOdds + 1
Else
iEvens = iEvens + 1
End If
iTots = iTots + 1
End If
Neste Rceii

STEMP = ""
Hvis iTots = iTotCells Then
STEMP = "t"
AnnetHvis- iOdds = iNumOdds Then
STEMP = "o"
AnnetHvis- iEvens = iNumEvens Deretter
STEMP = "e"
End If
CellChk = STEMP
End Function

Du bruker makroen ved å sende det til en adresse i raden du ønsker å sjekke. Så, for eksempel, hvis du var å bruke den betingede formater regel til celle A3, du ville passere makro en adresse av B3 eller C3-noe annet enn A3, som det vil føre til en sirkelreferanse. Makroen ser for den siste cellen som brukes i denne raden og deretter bestemmer hvor mange like og ulike celler har noe i dem. Makroen returnerer en av fire verdier; hvis de første kriteriene er oppfylt (alle cellene i raden starter med kolonne B har noe i dem) og deretter en "t" er returnert. Dersom alle de odde kolonner (med B være den første odde kolonne) har noe i dem, da "o" er returnert. Hvis alle med søyler (med C være den første til og med kolonne) har noe i dem, deretter "e" returnert. Hvis ingen av de tre kriteriene er oppfylt, returnerer deretter funksjonen ingenting.

Du må likevel å sette opp tre betingede formatreglene som er avhengige av evalueringen av en formel. Her er tre du kan bruke med denne makroen:

= CellChk (B1) = "t")
= CellChk (B1) = "o")
= CellChk (B1) = "e")

Disse eksempler er for påføring av et betinget format til celle A1; justere celle referanser til den riktige raden du ønsker makro å analysere. Husk at selv om du angir en enkelt celle (B1 i disse eksemplene), beregner makro hvor mange celler i raden til å faktisk se på.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (5945) gjelder for Microsoft Excel 2007, 2010, og 2013.