Betingede formater som skiller Blanks og Zeroes

April 8  by Eliza

La oss si at du rutinemessig importere informasjon fra et annet program i Excel. Informasjonen inneholder tallverdier, men det kan også inneholde mellomrom. Du ønsker kanskje å bruke en betinget format på den importerte informasjonen for å markere eventuelle nullverdier. Problemet er, hvis du bare legge et betinget format som fremhever de cellene for å se om de er null, da tilstanden vil også fremheve noen celler som er blank, siden de inneholder en "null" verdi, så vel.

Det finnes flere forskjellige løsninger på dette knipe. En løsning er å bruke en betinget format som bruker to forhold. Det første vilkåret sjekker for de blanke feltene, og de andre sjekker for nullverdier. Betingelsen som sjekker for blanks ikke trenger å justere noen formatering, men den som sjekker for nullverdier kan. Dette fungerer fordi hvis den første betingelsen er tilfredsstilt (cellen er tom), den andre betingelse ikke testet. Gjør følgende:

  1. Merk området du ønsker betinget formatert. (For dette eksempelet, vil jeg anta at du har valgt området A2:. A99)
  2. Med kategorien Hjem på båndet vises, klikker du på Betinget formatering alternativet i Stiler-gruppen. Excel viser en palett av opsjoner knyttet til betinget formatering.
  3. Klikk på Administrer reglene. Excel viser Betinget formatregler dialog Behandling.
  4. Klikk på Ny regel. Excel viser den nye dialogboksen for formatering regel-boksen.
  5. I boksen Velg en regel Type område på toppen av dialogboksen, velger du Format Bare celler som inneholder. (Se figur 1)

    Betingede formater som skiller Blanks og Zeroes

    Figur 1. New Formatering dialog regel-boksen.

  6. Ved hjelp av den første rullegardinlisten for regelen, velger Blanks.
  7. Klikk på OK. Excel lukker Ny formatering dialogboksen regel-boksen og igjen viser betinget formatregler dialog Behandling, denne gangen med den nye regelen synlig. (Legg merke til at du ikke har angitt noen formatering for denne regelen, det er greit.)
  8. Kontroller at Stop Hvis Sann er merket for regelen.
  9. Klikk på Ny regel. Excel viser den nye dialogboksen for formatering regel boksen igjen.
  10. I boksen Velg en regel Type område på toppen av dialogboksen, velger du Format Bare celler som inneholder.
  11. Ved hjelp av den første rullegardinlisten for regelen, velger Cell verdi.
  12. Ved hjelp av den andre rullegardinlisten for regelen, velger Lik.
  13. I verdiboksen for Tilstand 2, skriv 0.
  14. Klikk på knappen Format. Excel viser dialogboksen Formater celler.
  15. Bruk kontrollene i dialogboksen for å endre formateringen, som ønsket.
  16. Klikk OK for å lukke dialogboksen Formater celler.
  17. Klikk OK for å lukke New Formatering dialogboksen regel-boksen. Excel viser igjen den betingede formatreglene sjef, og regelen du nettopp definert er den første i listen. (Det bør også velges.)
  18. Klikk på pil ned for å flytte regelen du nettopp opprettet til den andre posisjonen i listen over regler.
  19. Klikk OK for å lukke betingede formatreglene dialog Behandling. Formateringen brukes til celleområdet du valgte i trinn 1.

En annen løsning er å kombinere de to tilstander i en enkelt tilstand. Følg denne fremgangsmåten:

  1. Merk området du ønsker betinget formatert. (For dette eksempelet, vil jeg anta at du har valgt området A2:. A99)
  2. Med kategorien Hjem på båndet vises, klikker du på Betinget formatering alternativet i Stiler-gruppen. Excel viser en palett av opsjoner knyttet til betinget formatering.
  3. Klikk på Ny regel. Excel viser den nye dialogboksen for formatering regel-boksen.
  4. I Velg en regel Type område på toppen av dialogboksen, velger Bruk en formel for å finne hvilke celler for å formatere.
  5. I formelen boksen inn formelen = AND (A2 = 0, A2 <> "").
  6. Klikk på knappen Format. Excel viser dialogboksen Formater celler. (Se figur 2)

    Betingede formater som skiller Blanks og Zeroes

    Figur 2. Formater celler dialogboksen.

  7. Bruk kontrollene i dialogboksen for å endre formateringen, som ønsket.
  8. Klikk OK for å lukke dialogboksen Formater celler.
  9. Klikk OK for å lukke New Formatering dialogboksen regel-boksen. Formateringen brukes til celleområdet du valgte i trinn 1.

Formelen som brukes i trinn 5 kontroller for å sikre at verdien er 0, og at cellen ikke er tom. OG-funksjonen sørger for at bare når begge kriteriene er oppfylt vil formelen return true og formatet brukes.

Det finnes en rekke andre formler som også kan benyttes. For eksempel, kan hver av de følgende formler kan være substituert i trinn 5:

  • = OG (ANTALL (A2) = 1, A2 = 0)
  • = OG (A2 = 0, IKKE (ISBLANK (A2)))
  • = OG (A2 = 0, LEN (A2)> 0)
  • = IKKE (ISBLANK (A2)) * (A2 = 0)

Hvis du ønsker en enda raskere måte å markere nullverdier, mens de ignorerer blanks, kan du vurdere å bruke en makro. Makroen ville være raskere fordi du bare kunne importere og kjøre det; du trenger ikke å velge et område med celler og inn formelen (eller formler) for betinget formatering. Følgende makro er et eksempel på en du kan bruke:

Sub FormatRed ()
TotalRows = 5000
ColNum = 1

For i = 1 til celler (TotalRows, ColNum) END (xlUp) .Row
Celler (i, ColNum) .Interior.ColorIndex = xlAutomatic
Hvis IsNumeric (Cells (i, ColNum) .Value) Then
Hvis Cells (i, ColNum) .Value = 0 Then
Celler (i, ColNum) .Interior.ColorIndex = 3
End If
End If
Neste
End Sub

Makro sjekker cellene i kolonne A. (Den sjekker cellene i rad 1 til 5000; man kan modifisere dette, om ønskelig.) Hvis cellen inneholder en numerisk verdi og at verdien er null, da cellen er fylt med rødt. Hvis cellen inneholder noe annet, da cellen er satt tilbake til sin normale farge.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (7131) gjelder for Microsoft Excel 2007 og 2010. Du kan finne en versjon av dette tipset for den eldre menygrensesnittet av Excel her: Betingede formater som skiller Blanks og Zeroes.