Villkor Format som skiljer Tomma och nollor

May 27

Låt oss säga att du rutinmässigt importera information från ett annat program i Excel. Informationen innehåller numeriska värden, men det kan också innehålla blanksteg. Du kanske vill använda ett villkorsformat på den importerade informationen att uppmärksamma eventuella nollvärden. Problemet är, om du bara lägga ett villkorsformat som belyser cellerna för att se om de är noll, då villkoret kommer också att belysa eventuella celler som är tomma, eftersom de innehåller ett värde "noll", liksom.

Det finns flera olika lösningar på detta predikament. En lösning är att tillämpa en villkorsformat som använder två villkor. Det första villkoret kontrollerar tomrummen och den andra kontrollerar nollvärden. Villkoret som kontrollerar om ämnena inte behöver justera någon formatering, men den som söker efter nollvärden kan. Detta fungerar eftersom om det första villkoret är uppfyllt (cellen är tom), är det andra villkoret aldrig testat. Gör så här:

  1. Markera det område som du vill villkor formaterat. (I det här exemplet ska jag antar att du har valt området A2:. A99)
  2. Med fliken Start i menyfliks visas klickar alternativet Villkorsstyrd formatering i gruppen Format. Excel visar en palett av alternativ rörande villkorsstyrd formatering.
  3. Klicka på Hantera regler. Excel visar Villkorsstyrd formatering Regler dialogchef ruta.
  4. Klicka på Ny regel. Excel visar nya Formatera dialogrutan Regel ruta.
  5. I Välj en regeltyp område längst upp i dialogrutan, välj Format Endast celler som innehåller. (Se figur 1.)

    Villkor Format som skiljer Tomma och nollor

    Figur 1. Nya Formatera Dialogrutan regel ruta.

  6. Använda den första listrutan för regeln, välj Blanks.
  7. Klicka på OK. Excel stänger New Formatera dialogrutan Regel ruta och igen visar Villkorsstyrd formatering Regler dialogchef ruta, denna gång med den nya regeln synlig. (Observera att du inte angav någon formatering för denna regel, det är bra.)
  8. Se till att stopp Om Sann kryssrutan är markerad för regeln.
  9. Klicka på Ny regel. Excel visar återigen den nya Formatera dialogrutan Regel ruta.
  10. I Välj en regeltyp område längst upp i dialogrutan, välj Format Endast celler som innehåller.
  11. Använda den första listrutan för regeln, välj Cell Värde.
  12. Använda den andra listrutan för regeln, välj Lika med.
  13. I värderutan för Condition 2, anger 0.
  14. Klicka på knappen Format. Excel visar dialogrutan Formatera celler.
  15. Använd kontrollerna i dialogrutan för att ändra formateringen, enligt önskemål.
  16. Klicka på OK för att stänga dialogrutan Formatera celler.
  17. Klicka OK för att stänga New Formatera dialogrutan Regel ruta. Excel visas igen Villkorsstyrd formatering Regler Manager och rätts du just definierat är den första i listan. (Det bör också väljas.)
  18. Klicka på nedåtpilen för att flytta regeln du just har skapat till den andra positionen i listan över reglerna.
  19. Klicka OK för att stänga Villkorsstyrd formatering Regler dialogchef ruta. Formateringen appliceras på cellområde du markerade i steg 1.

En annan lösning är att kombinera dina två villkor till ett enda tillstånd. Gör så här:

  1. Markera det område som du vill villkor formaterat. (I det här exemplet ska jag antar att du har valt området A2:. A99)
  2. Med fliken Start i menyfliks visas klickar alternativet Villkorsstyrd formatering i gruppen Format. Excel visar en palett av alternativ rörande villkorsstyrd formatering.
  3. Klicka på Ny regel. Excel visar nya Formatera dialogrutan Regel ruta.
  4. I Välj en regeltyp område längst upp i dialogrutan, välj Använd en formel för att bestämma vilka celler att Formatera.
  5. I formeln rutan in formeln = AND (A2 = 0, A2 <> "").
  6. Klicka på knappen Format. Excel visar dialogrutan Formatera celler. (Se figur 2.)

    Villkor Format som skiljer Tomma och nollor

    Figur 2. Formatera celler dialogrutan.

  7. Använd kontrollerna i dialogrutan för att ändra formateringen, enligt önskemål.
  8. Klicka på OK för att stänga dialogrutan Formatera celler.
  9. Klicka OK för att stänga New Formatera dialogrutan Regel ruta. Formateringen appliceras på cellområde du markerade i steg 1.

Den formel som används i steg 5 kontroller för att se till att värdet är 0 och att cellen inte är tom. AND-funktionen ser till att endast när båda kriterier är uppfyllda kommer formeln tillbaka Sann och formatet ska tillämpas.

Det finns ett antal andra formler som också skulle kunna användas. Exempelvis kan var och en av följande formler vara substituerade i steg 5:

  • = OCH (COUNT (A2) = 1, A2 = 0)
  • = OCH (A2 = 0, EJ (ISBLANK (A2)))
  • = OCH (A2 = 0, LEN (A2)> 0)
  • = EJ (ISBLANK (A2)) * (A2 = 0)

Om du ville ha en ännu snabbare sätt att markera nollvärden och bortse ämnen, kan du överväga att använda ett makro. Makro skulle vara snabbare eftersom du bara kunde importera och köra det; du behöver inte välja ett cell och ange formeln (eller formler) för villkorsstyrd formatering. Följande makro är ett exempel på en du kan använda:

Sub FormatRed ()
TotalRows = 5000
ColNum = 1

För i = 1 till celler (TotalRows, ColNum) .End (xlUp) .Row
Celler (i, ColNum) .Interior.ColorIndex = xlAutomatic
Om IsNumeric (Cells (i, ColNum) .Value) Därefter
Om Cells (i, ColNum) .Value = 0 Då
Celler (i, ColNum) .Interior.ColorIndex = 3
End If
End If
Nästa
End Sub

De makrokontroller cellerna i kolumn A. (Den kontrollerar cellerna i raderna 1 till 5.000, du kan ändra det här, om så önskas.) Om cellen innehåller ett numeriskt värde och att värdet är noll, då cellen fylls med rött. Om cellen innehåller något annat, då cellen ställs tillbaka till sin normala färg.

ExcelTips är din källa för kostnadseffektiv Microsoft Excel utbildning. Detta tips (7131) gäller för Microsoft Excel 2007 och 2010. Du kan hitta en version av detta tips för den äldre menygränssnittet i Excel här: Villkorsformat som skiljer Tomma och nollor.