Ja, du kan dölja rader i Excel med hjälp av en formel eller villkorat uttalande, men inte * direkt * inom en cellformel. Istället måste du använda
villkorad formatering med en anpassad formel i samband med ett VBA -makro . Så här gör man det:
1. Villkorlig formatering:
* Syfte: För att flagga rader som uppfyller dina kriterier (tillståndet). Istället för att direkt gömma oss, ändrar vi radens teckensnitt och fyller färg för att vara osynlig.
* steg:
1. Välj dataintervall: Markera hela intervallet av rader och kolumner som du vill tillämpa gömningstillståndet på (t.ex. `A1:Z100` eller hur stora dina data är). Av avgörande betydelse kommer den * första * raden i ditt val att användas som referens för din formel.
2. Gå till villkorad formatering: Klicka på "villkorad formatering"> "Ny regel på fliken" Hem ".
3. Välj "Använd en formel för att bestämma vilka celler som ska formateras": Välj den här regeltypen.
4. Ange din formel: I rutan Formel anger du en formel som returnerar "True" när du vill dölja raden och "falsk" annars. * VIKTIGT:* Använd relativa referenser (`$ ColumnNamerOwnumber ') eller blandade referenser (` $ ColumnNamerOwnumber') på lämpligt sätt, beroende på vad du vill testa.
* Exempel 1 (dölj rader där värdet i kolumn A är "dölj"):
`` `Excel
=$ A1 ="dölj"
`` `
Denna formel kontrollerar om värdet i kolumn A i * första raden * i ditt valda intervall är lika med "dölj". Den kommer sedan att tillämpa formateringen på den raden. Eftersom du valde hela sortimentet justeras formeln automatiskt för varje rad.
* Exempel 2 (dölj rader där värdet i kolumn B är mindre än 0):
`` `Excel
=$ B1 <0
`` `
Detta döljer rader där värdet i kolumn B är negativt.
* Exempel 3 (dölj rader där värdet i kolumn C är tomt):
`` `Excel
=Isblank ($ C1)
`` `
5. Ställ in formatet (för att "dölja"): Klicka på knappen "Format ...".
* font: Ändra teckensnittsfärgen så att du matchar bakgrundsfärgen på dina celler. Vanligtvis är detta vitt (eller ingen fyllning).
* Fill: Ändra fyllningsfärgen så att du matchar bakgrundsfärgen på dina celler. Återigen, vanligtvis vit (eller ingen fyllning).
* Klicka på "OK" för att stänga formatdialogen och sedan "OK" igen för att skapa villkorad formateringsregel.
2. VBA -makro (för att justera radhöjden):
* Syfte: För att ställa in radhöjden till 0 för raderna som matchar formateringen. Detta är det sista steget i visuellt döljer raderna.
* steg:
1. Öppna VBA -redaktören: Tryck på `Alt + F11` för att öppna Visual Basic Editor.
2. Sätt in en modul: I VBA -redigeraren, gå till "Infoga"> "Modul".
3. klistra in följande kod: Klistra in denna VBA -kod i modulen:
`` VBA
SUB HIDEROWSBASEDONFORMAT ()
Dim rng som intervall, cell som räckvidd
Dim WS som kalkylblad
Dim kondantat som formatCondition
'Ändra "Sheet1" till namnet på ditt ark
Ställ in ws =thisworkbook.sheets ("Sheet1")
'Ändra A1:Z100 till det faktiska intervallet där den villkorade formateringen tillämpas
SET RNG =WS.RANGE ("A1:Z100")
'Slinga genom varje cell i sortimentet
För varje cell i RNG.ROWS
'Antag initialt inte göm sig inte
cell.hidd =falsk
'Slinga genom alla villkorade format
För varje kondition i cell.FormatConditions
Om condformat.type =xlexpression då
'Utvärdera formeln, om det är sant, göm dig
Om ws.valuate (condformat.formula1) då
cell.Hidden =true
Gå ut för
Slut om
Slut om
Nästa kondition
Nästa cell
Slutsub
Sub UnhideAllrows ()
Dim WS som kalkylblad
'Ändra "Sheet1" till namnet på ditt ark
Ställ in ws =thisworkbook.sheets ("Sheet1")
ws.rows.hidden =falsk
Slutsub
`` `
4. Anpassa koden:
* `ws =thisworkbook.blad (" Sheet1 ")` :Ändra `" Sheet1 "` till det faktiska namnet på kalkylbladet där dina data är.
* `set rng =ws.range (" a1:z100 ")` :Ändra `" A1:Z100 "` till samma dataintervall som du använde när du skapade villkorad formateringsregel.
5. Kör makro: Gå tillbaka till VBA -redigeraren (om du stängde den) och tryck på `F5" för att köra Makroen "HiderowSBasedonFormat". Alternativt, från Excel, gå till fliken "Utvecklare" (om du inte ser den måste du aktivera det i Excels alternativ)> "Macro", välj `HiderowSBasedonFormat 'och klicka på" Kör ".
6. (Valfritt) Lägg till en knapp: Du kan infoga en knapp på ditt kalkylblad (infoga -> Former -> -knappen) och tilldela makroet "HiderowSBasedonFormat" till den. Detta ger ett användarvänligt sätt att återuppstå raderna när data ändras. Du kan också lägga till en knapp för "ohideallrows".
Förklaring:
* Villkorlig formatering: Detta markerar de rader vi vill dölja.
* vba makro: Denna kod itererar genom raderna och kontrollerar om den villkorade formateringen gäller för varje rad. Om det gör det, ställer det in "rowheight" till 0, vilket gör raden effektivt osynlig. Vi använder den villkorade formateringsformeln som en del av makroen så att den inte bara är baserad på format eftersom det kan ändras.
Viktiga överväganden:
* Prestanda: Om du har ett mycket stort datasätt (tiotusentals rader) kan detta tillvägagångssätt * vara långsamt eftersom det itereras genom varje rad. Det kan finnas mer komplexa men snabbare VBA -lösningar om prestanda är kritiska.
* Omberäkning: När dina data ändras (och de villkorade formateringsreglerna beräknar) måste du återköra makroen för att återuppta raderna om förhållandena har ändrats. Du kan automatisera detta med en händelseshanterare i VBA (t.ex. `Worksheet_Change` för att utlösa makroen när en cell på arket ändras). Var dock försiktig, eftersom det också kan sakta ner ett makro på varje förändring.
Exempel med evenemanget `Worksheet_Change` (Automatisk gömning):
För att göra gömningen automatisk när data ändras kan du ändra VBA -koden enligt följande. Istället för att placera koden i en modul, placera den i * Kalkylens * kodfönster:
1. Högerklicka på plåtfliken (t.ex. "Sheet1") i Excel och välj "Visa kod". Detta öppnar VBA -redigeraren direkt för kalkylbladets kodmodul.
2. klistra in följande kod i kalkylbladets kodfönster:
`` VBA
Privat Sub Worksheet_Change (ByVal Target As Range)
"Ring HiderowSBasedonFormat -makroen när arket ändras.
Hiderowbasedonformat
Slutsub
SUB HIDEROWSBASEDONFORMAT ()
Dim rng som intervall, cell som räckvidd
Dim WS som kalkylblad
Dim kondantat som formatCondition
'Ändra "Sheet1" till namnet på ditt ark
Ställ in ws =thisworkbook.sheets ("Sheet1")
'Ändra A1:Z100 till det faktiska intervallet där den villkorade formateringen tillämpas
SET RNG =WS.RANGE ("A1:Z100")
'Slinga genom varje cell i sortimentet
För varje cell i RNG.ROWS
'Antag initialt inte göm sig inte
cell.hidd =falsk
'Slinga genom alla villkorade format
För varje kondition i cell.FormatConditions
Om condformat.type =xlexpression då
'Utvärdera formeln, om det är sant, göm dig
Om ws.valuate (condformat.formula1) då
cell.Hidden =true
Gå ut för
Slut om
Slut om
Nästa kondition
Nästa cell
Slutsub
Sub UnhideAllrows ()
Dim WS som kalkylblad
'Ändra "Sheet1" till namnet på ditt ark
Ställ in ws =thisworkbook.sheets ("Sheet1")
ws.rows.hidden =falsk
Slutsub
`` `
* Viktigt: Byt ut `" Sheet1 "` och `" A1:Z100 "` med ditt faktiska arknamn och dataintervall, som tidigare.
Nu, när du ändrar ett värde på arket, kommer makroen "HiderowSBasedonFormat" automatiskt att köras och gömmer alla rader som uppfyller de villkorade formateringskriterierna.
Kom ihåg att spara din Excel-fil som en makroaktiverad arbetsbok (.XLSM) för att bevara VBA-koden.
Detta tillvägagångssätt ger ett flexibelt sätt att dynamiskt "dölja" rader i Excel baserat på en formel, även om den kräver användning av både villkorad formatering och VBA. Välj den metod som bäst passar dina behov och komfortnivå med VBA. Det enklare, knappaktiverade makro är ofta en bra utgångspunkt.
