Voorwaardelijke opmaak dat Blanks en Zeroes Onderscheid

Laten we zeggen dat u regelmatig importeren uit een ander programma in Excel. Het bevat numerieke waarden, maar ook spaties bevatten. Wilt u misschien een voorwaardelijke opmaak te gebruiken op de geïmporteerde gegevens voor de nul- waarden te benadrukken. Het probleem is, als je gewoon voeg een voorwaardelijke opmaak dat de cellen hoogtepunten te zien of ze nul zijn, dan is de voorwaarde zal ook wijzen op eventuele cellen die leeg zijn, omdat ze bevatten een "nul" waarde, als goed.

Er zijn verschillende oplossingen voor dit dilemma. Een oplossing is om een ​​voorwaardelijke opmaak die twee voorwaarden gebruikt gelden. De eerste voorwaarde controleert de blanks, en de tweede controleert nulwaarden. De voorwaarde dat de lege cellen wordt gecontroleerd hoeft niet te enige opmaak aan te passen, maar degene die controleert op nul waarden kan. Dit werkt, want als de eerste voorwaarde is voldaan (de cel leeg is), wordt de tweede voorwaarde nooit getest. Doe het volgende:

  1. Selecteer het bereik dat u wilt voorwaardelijk geformatteerd. (Voor dit voorbeeld, ik neem aan dat u het bereik A2 heeft geselecteerd:. A99)
  2. Met het tabblad Start van het lint wordt weergegeven, klikt u op de optie Voorwaardelijke opmaak in de groep Stijlen. Excel toont een palet van opties met betrekking tot voorwaardelijke opmaak.
  3. Klik op Regels beheren. Excel toont het dialoogvenster Manager regels voor voorwaardelijke opmaak doos.
  4. Klik op Nieuwe regel. Excel toont het dialoogvenster Regel Nieuwe opmaak doos.
  5. In het Selecteer een regel Zetspiegel aan de bovenkant van het dialoogvenster, kies Opmaak Alleen cellen die bevatten. (Zie figuur 1.)

    Voorwaardelijke opmaak dat Blanks en Zeroes Onderscheid

    Figuur 1. Het dialoogvenster Regel Nieuwe opmaak doos.

  6. Met behulp van de eerste drop-down lijst voor de regel, kiest Blanks.
  7. Klik op OK. Excel sluit het dialoogvenster Regel Nieuwe opmaak doos en weer geeft het dialoogvenster Manager regels voor voorwaardelijke opmaak doos, dit keer met uw nieuwe regel zichtbaar. (Merk op dat u geen opmaak niet opgeven voor deze regel; dat is prima.)
  8. Zorg ervoor dat de Stop Als True selectievakje is geselecteerd voor de regel.
  9. Klik op Nieuwe regel. Excel toont opnieuw het dialoogvenster Regel Nieuwe opmaak doos.
  10. In het Selecteer een regel Zetspiegel aan de bovenkant van het dialoogvenster, kies Opmaak Alleen cellen die bevatten.
  11. Met behulp van de eerste drop-down lijst voor de regel, kiest Cell Value.
  12. Met behulp van de tweede keuzelijst voor de regel, kiest Gelijk aan.
  13. In het vak waarde voor Voorwaarde 2, voert u 0.
  14. Klik op de knop Opmaak. Excel toont het dialoogvenster Cellen opmaken.
  15. Gebruik de regelaars in het dialoogvenster om de opmaak te wijzigen, zoals gewenst.
  16. Klik op OK om het dialoogvenster Celeigenschappen te sluiten.
  17. Klik op OK om het dialoogvenster Regel Nieuwe opmaak sluiten. Excel toont nogmaals de regels voor voorwaardelijke opmaak Manager, en de regel die u zojuist gedefinieerd is de eerste in de lijst. (Ook moet worden geselecteerd.)
  18. Klik op de pijl omlaag om de regel die u zojuist hebt gemaakt naar de tweede positie in de lijst met regels te verplaatsen.
  19. Klik op OK om het dialoogvenster Manager regels voor voorwaardelijke opmaak te sluiten. De opmaak wordt toegepast op het bereik van de cellen die u in stap 1 hebt geselecteerd.

Een andere oplossing is om je aan twee voorwaarden te combineren tot één voorwaarde. Volg deze stappen:

  1. Selecteer het bereik dat u wilt voorwaardelijk geformatteerd. (Voor dit voorbeeld, ik neem aan dat u het bereik A2 heeft geselecteerd:. A99)
  2. Met het tabblad Start van het lint wordt weergegeven, klikt u op de optie Voorwaardelijke opmaak in de groep Stijlen. Excel toont een palet van opties met betrekking tot voorwaardelijke opmaak.
  3. Klik op Nieuwe regel. Excel toont het dialoogvenster Regel Nieuwe opmaak doos.
  4. In het Selecteer een regel Zetspiegel aan de bovenkant van het dialoogvenster, kiest u een formule gebruiken om te bepalen welke cellen te formatteren.
  5. In de doos formule voer de formule = EN (A2 = 0, A2 <> "").
  6. Klik op de knop Opmaak. Excel toont het dialoogvenster Cellen opmaken. (Zie figuur 2.)

    Voorwaardelijke opmaak dat Blanks en Zeroes Onderscheid

    Figuur 2. Het dialoogvenster Cellen opmaken.

  7. Gebruik de regelaars in het dialoogvenster om de opmaak te wijzigen, zoals gewenst.
  8. Klik op OK om het dialoogvenster Celeigenschappen te sluiten.
  9. Klik op OK om het dialoogvenster Regel Nieuwe opmaak sluiten. De opmaak wordt toegepast op het bereik van de cellen die u in stap 1 hebt geselecteerd.

De formule die wordt gebruikt in stap 5 controleert of de waarde 0 en de cel niet leeg. De EN-functie zorgt ervoor dat alleen wanneer beide criteria is voldaan, zal de formule terug Ware en het formaat worden toegepast.

Er zijn een aantal andere formules die ook kan worden gebruikt. Zo kan elk van de volgende formule worden gesubstitueerd in stap 5:

  • = EN (COUNT (A2) = 1, A2 = 0)
  • = EN (A2 = 0, NOT (ISBLANK (A2)))
  • = EN (A2 = 0, LEN (A2)> 0)
  • = NOT (ISBLANK (A2)) * (A2 = 0)

Als u een nog snellere manier om nulwaarden markeren terwijl het negeren van blanks wilde, kunt u overwegen het gebruik van een macro. De macro zou sneller zijn, want je kon gewoon importeren en voer het uit; je hoeft niet naar een bereik van cellen te selecteren en voer de formule (of formules) voor de voorwaardelijke opmaak. De volgende macro is een voorbeeld van degene die je zou kunnen gebruiken:

Sub FormatRed ()
TotalRows = 5000
ColNum = 1

Voor i = 1 To Cells (TotalRows, ColNum) .End (xlUp) .Row
Cells (i, ColNum) .Interior.ColorIndex = xlAutomatic
Als IsNumeric (Cells (i, ColNum) .Value) Dan
Als Cells (i, ColNum) .Value = 0 Then
Cellen (i, ColNum) .Interior.ColorIndex = 3
End If
End If
Volgende
End Sub

De macro controleert de cellen in kolom A (Het controleert de cellen in de rijen 1 tot 5000, u kunt deze wijzigen, indien gewenst.) Als de cel bevat een numerieke waarde en die waarde nul is, dan is de cel is gevuld met rode. Als de cel iets anders dan de cel is terug naar zijn normale kleur.

ExcelTips is uw bron voor kosteneffectieve Microsoft Excel training. Deze tip (7131) is van toepassing op Microsoft Excel 2007 en 2010. U kunt een versie van deze tip voor de oudere menu-interface van Excel hier vinden: Voorwaardelijke opmaak dat Blanks en Zeroes onderscheiden.