Betingede formater for lige og ulige kolonner

Zar har et behov for at anvende et betinget format til værdierne i kolonne A i et regneark, men han kan ikke regne ud, hvad reglerne kunne være. Udover kolonne A, har han også data med i kolonne B, og han tilføjer periodisk nye kolonner af data. Hvis der er data i alle ulige kolonner begyndende med B, Zar ønsker et format anvendt i kolonne A. (B er data kolonne 1 for hans regneark, så han finder det mærkeligt.) Hvis der er data i alle de lige kolonner begyndende med C, Zar ønsker et andet format anvendt i kolonne A. Hvis der er data i alle datakolonner starter med B-dog mange, der kunne være-så han ønsker en tredje format anvendt.

Da Zar uden tvivl har regnet ud, kan du nemt oprette en formel til at afgøre, om der er oplysninger i kolonne B og C og anvende formatering i overensstemmelse hermed. Faktisk vil en simpel formel som disse gøre det trick:

= TÆLV (B1: C1) = 2
= TÆLV (B1) = 1
= COUNTA (C1) = 1

Den første formel giver True, hvis der er oplysninger i både B og C, den anden, hvis der er oplysninger i B, og den tredje, hvis der er oplysninger i C. Så længe du vælger "Stop Hvis True" for hver regel / formel, så vil din formatering vil fungere fint.

Oprettelse af en formel for flere kolonner ud B og C er kun marginalt mere vanskeligt. De samme tre typer af formler, i orden, ville være som følger:

= TÆLV (B1: G1) = 6)
= TÆLV (B1, D1, F1) = 3
= COUNTA (C1, E1, G1) = 3

Du kan nemt tilføje flere cellereferencer til formlerne, efter behov. En sådan fremgangsmåde giver True i kun tre betingelser: Hvis alle celler i området B1: G1 har noget i dem, hvis alle ulige celler (B1, D1, F1) har noget i dem, og hvis alle selv celler (C1, E1, G1) har noget i dem. Det vil ikke returnere True, hvis kun nogle af cellerne i området har værdier i dem. For eksempel er der værdier i cellerne B1, C1 og E1, så vil det ikke returnere sandt, og ingen af ​​kriterierne for formatering vil blive opfyldt.

Mens disse alle fungerer fint med den noterede begrænsning, de ikke præcis, hvad Zar er på udkig efter-han ønsker en formel, der vil afsløre, hvor mange kolonner bliver brugt uge efter uge, da han fortsætter med at tilføje data til kolonner, og juster formel derfor uden behov for manuelt at redigere formlen for at tage hensyn til de tilføjede data. Med andre ord, hvis han tilføjer data til kolonne H, ville han have formlerne automatisk justeres for at tage hensyn til den øgede kolonne:

= TÆLV (B1: H1) = 7)
= TÆLV (B1, D1, F1, H1) = 4
= COUNTA (C1, E1, G1) = 3

Det er naturligvis en mere kompleks behov. Måske er den bedste måde at nærme sig problemet er at skabe en brugerdefineret funktion (en makro), som kan se på en række celler og afgøre, om et af de tre kriterier er opfyldt. Overvej følgende makro:

Funktion CellChk (crng Da 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 rCell Som Range
Dim iLastCol As Integer
Dim Stemp As String

iOdds = 0
iEvens = 0
iTots = 0

»Finde ud den virkelige sidste kolonne i rækkevidde regnearket og sæt
iLastCol = ActiveSheet.Cells.Find (Hvad: = "*", _
SearchOrder: = xlByColumns, SearchDirection: = xlPrevious, _
Lookin: = xlFormulas) .Column
Set rWork = Range (Cells (crng.Row, 2), Cells (crng.Row, iLastCol))

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

For hver rCell I rWork
Hvis rCell <> "" Så
Hvis ((rCell.Column - 1) Mod 2) = 1 Så
iOdds = iOdds + 1
Andet
iEvens = iEvens + 1
End hvis
iTots = iTots + 1
End hvis
Næste rCell

Stemp = ""
Hvis iTots = iTotCells Then
Stemp = "t"
ElseIf iOdds = iNumOdds Then
Stemp = "o"
ElseIf iEvens = iNumEvens Så
Stemp = "e"
End hvis
CellChk = Stemp
End Function

Du bruger makroen ved at føre det en adresse i den række du vil kontrollere. Så, for eksempel, hvis du var at anvende den betingede formatering reglen til celle A3, du vil videregive makroen en adresse på B3 eller C3-noget undtagen A3, som vil medføre en cirkulær reference. Macroen ser for den sidste celle, der anvendes i den pågældende række og derefter bestemmer hvor mange ulige og lige celler har noget i dem. Makroen returnerer nogen af ​​fire værdier; hvis de første kriterier er opfyldt (alle celler i rækken starter med kolonne B har noget i dem), så et "t" returneres. Hvis alle de ulige kolonner (med B er den første ulige kolonne) har noget i dem, så "o" returneres. Hvis alle de lige søjler (med C er den første selv kolonne) har noget i dem, derefter "e" tilbage. Hvis ingen af ​​de tre kriterier er opfyldt, så returnerer funktionen ingenting.

Du vil stadig nødt til at oprette tre betingede formatering, der er afhængige af en vurdering af en formel. Her er tre, du kan bruge med denne makro:

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

Disse eksempler er til påføring af en betinget format til celle A1; justere celle referencer til den rigtige række, du vil makroen at analysere. Husk, at selvom du angive en enkelt celle (B1 i disse eksempler), makroen beregner, hvor mange celler i rækken til rent faktisk at se på.

ExcelTips er din kilde til omkostningseffektiv Microsoft Excel træning. Dette tip (5945) gælder for Microsoft Excel 2007, 2010 og 2013.


© 2019 Zajacperrone.com | Contact us: webmaster# zajacperrone.com