[Excel 2013] Verwijderen blank van een pivot table...

[YellowOnline]

... maar niet (Blank)

Ingewikkelde Excelfile die eindigt met een pivot table waar een SUM in voorkomt voor rijen met waarde "0" op rijen met waarde "" - twee waarden die mij niet interesseren maar helaas niet eenvoudig uit de brondata te verwijderen zijn.

De "0" wegkrijgen was eenvoudig met een filter: NOT EQUALS "0". Maar hoe krijg ik het blanco label weg? Internet staat vol met oplossingen voor (Blank), maar dat is bij mij niet het geval. Screenshotje waarop de lijn in kwestie te zien is zowel in PivotTable als in PivotChart:



Ik heb nog een uur of twee om dit in te leveren aangezien ze dit vanaf morgen in gebruik nemen (het is iets voor personeelsbeheer). In het slechtste geval moeten ze hiermee leren leven (ik doe dit vrijwillig), maar ik zou het toch netjes vinden van die blank en zodoende de "0%" in de grafiek vanaf te komen.

[Speedmaster]

NOT EQUALS " " dus een spatie of meerdere tussen de quotes

Edit: Mooiste is natuurlijk om je formules aan te passen naar een Left(Trim( , ben je alle spaties meteen kwijt

[ Voor 49% gewijzigd door Speedmaster op 31-03-2015 16:09 ]

[YellowOnline]

Speedmaster schreef op dinsdag 31 maart 2015 @ 16:07:
NOT EQUALS " " dus een spatie of meerdere tussen de quotes

Edit: Mooiste is natuurlijk om je formules aan te passen naar een Left(Trim( , ben je alle spaties meteen kwijt

Maar het zijn lege velden - er zijn helemaal geen spaties Veel valt er dus niet te trimmen en variaties op "" en " " werken niet. Het is een aggregaat van blanks die een paar keer door een dynamische array gevlogen zijn (met leuke formules zoals "=IFERROR(INDEX($B$5:$B$205,SMALL((IF(LEN($B$5:$B$205),ROW(INDIRECT("1:"&ROWS($B$5:$B$205))))),ROW(A4)),1),"")") maar wordt niet als blank herkend door Excel. Als wat dan wel is mij onduidelijk.

[Speedmaster]

Zet een Trim aan het begin van de formule en kijk dan nog eens

[YellowOnline]

Speedmaster schreef op dinsdag 31 maart 2015 @ 16:16:
Zet een Trim aan het begin van de formule en kijk dan nog eens

Euh, hoe zie je dat precies? Ik snap vooral die LEFT niet. Ik probeerde

code:

{=TRIM(IFERROR(INDEX($K$5:$K$205,SMALL((IF(LEN($K$5:$K$205),ROW(INDIRECT("1:"&ROWS($K$5:$K$205))))),ROW(A1)),1),""))}

... maar die TRIM maakt geen verschil. LEFT zorgt er enkel voor dat de TRIM aan de linkerzijde gebeurt en niet rechts, terwijl een gewone TRIM sowieso beide zijden doet. Of heb ik het verkeerd?

[Speedmaster]

Kan nog zijn dat door afronding van getallen er een percentage overblijft wat minder is als 1 of 0 % 0,9 bijvoorbeeld. dan zou je moeten zoeken of er ergens een berekening is die niet helemaal lekker afrond

[YellowOnline]

Speedmaster schreef op dinsdag 31 maart 2015 @ 16:38:
Kan nog zijn dat door afronding van getallen er een percentage overblijft wat minder is als 1 of 0 % 0,9 bijvoorbeeld. dan zou je moeten zoeken of er ergens een berekening is die niet helemaal lekker afrond

Nee, het is een lijst met labels (tekst). De lijst is voorzien op ingave van maximum 2400 labels en de meeste zijn natuurlijk leeg (momenteel zelfs 2395 leeg of zo). Eigenlijk is het misschien gemakkelijker te zien in het bestand zelf: http://www.filedropper.com/sample_10.

Wat rood is, is normaal hidden. Op de tabbladen per maand heb ik enkel April (waar dummy data voor is) unhidden. All worksheets zijn unprotected nu.

[Icephase]

Volgens mij kun je in de opties van de draaitabel aanzetten hoe 'ie met nulwaarden om moet gaan. Misschien dat je daar wat mee kunt?

Anders kun je deze via het dataveldfilter eruit laten filteren misschien?

[YellowOnline]

Icephase schreef op dinsdag 31 maart 2015 @ 16:50:
Volgens mij kun je in de opties van de draaitabel aanzetten hoe 'ie met nulwaarden om moet gaan. Misschien dat je daar wat mee kunt?

Anders kun je deze via het dataveldfilter eruit laten filteren misschien?

Er is een optie "For empty cells show..." maar deze detecteert de cellen blijkbaar niet als empty. Anders zou ik ze laten vervangen door 0 zodat ze er uitgefilterd kunnen worden samen met de andere 0en.

Met filters kan ik wel degelijk de 0 en de "" er uit halen, maar dan is het probleem dan nieuwe waarden niet te zien zijn wanneer er een update is (verschillende keren per dag). "Show all" moet aangevinkt zijn, dus ik kan enkel een labelfilter toepassen zoals ik dat doe voor "0". Alleen vind ik geen string die overeenkomt met die blanks.

[Icephase]

YellowOnline schreef op dinsdag 31 maart 2015 @ 16:56:
[...]


Er is een optie "For empty cells show..." maar deze detecteert de cellen blijkbaar niet als empty. Anders zou ik ze laten vervangen door 0 zodat ze er uitgefilterd kunnen worden samen met de andere 0en.

Met filters kan ik wel degelijk de 0 en de "" er uit halen, maar dan is het probleem dan nieuwe waarden niet te zien zijn wanneer er een update is (verschillende keren per dag). "Show all" moet aangevinkt zijn, dus ik kan enkel een labelfilter toepassen zoals ik dat doe voor "0". Alleen vind ik geen string die overeenkomt met die blanks.

Tja, dat klinkt inderdaad herkenbaar. Ben ook ooit tegen zoiets aangelopen. Heb het maar zo gelaten destijds, ze moesten er maar mee leren leven. Ik denk dan ook dat de oplossing eerder in je input zit dan in de draaitabel zelf.

[naitsoezn]

Een label-filter op Greater Than "" (dus: quote-openen quote-sluiten) filtert wel die lege cell eruit, maar dan staat de 0 er nog in

[YellowOnline]

naitsoezn schreef op dinsdag 31 maart 2015 @ 17:02:
Een label-filter op Greater Than "" (dus: quote-openen quote-sluiten) filtert wel die lege cell eruit, maar dan staat de 0 er nog in

Goed gezien. Nu inderdaad nog vinden hoe ik zowel GREATER THAN "" als NOT EQUALS 0 doe

Edit:
Gevonden: GREATER THAN 0 krijgt ze allebei weg. De achterliggende logica is mij niet meteen duidelijk (waarom is "" groter dan 0?), maar het werkt.

Edit 2:
#@$% met wat voor filter dan ook actief is er na een refresh refresh on open niets meer zichtbaar tenzij de filter uitgezet wordt en dan weer aangezet

[ Voor 34% gewijzigd door YellowOnline op 31-03-2015 17:09 ]

[Bastien]

Misschien een vage oplossing, maar kun je een test inbouwen dat ie de waarde controleert? Want als je 0/inputwaarde doet en je krijgt een foutmelding (eender welke) dan moet ie het niet meenemen (dus tekst, blanks, 0) en de rest pakt ie wel want het antwoord is dan 0. Dus dan zou je daarop kunnen filteren met ifferror ofzo.

Geen idee of het mogelijk is, heb helaas geen tijd om in je spreadsheet te kijken.

[Belindo]

Wellicht met een stukje VBA?

Visual Basic .NET:

Private Sub Worksheet_Activate()
    
    'Haal filter weg om eventuele nieuwe values te tonen
    ActiveSheet.PivotTables("Übersicht Jahr").ClearAllFilters
    
    'Zet "blank" en "0" uit
    With ActiveSheet.PivotTables("Übersicht Jahr").PivotFields("Kategorie")
        .PivotItems("").Visible = False
        .PivotItems("0").Visible = False
    End With
    
End Sub

[YellowOnline]

Belindo schreef op dinsdag 31 maart 2015 @ 17:24:
Wellicht met een stukje VBA?

Visual Basic .NET:

Private Sub Worksheet_Activate()
    
    'Haal filter weg om eventuele nieuwe values te tonen
    ActiveSheet.PivotTables("Übersicht Jahr").ClearAllFilters
    
    'Zet "blank" en "0" uit
    With ActiveSheet.PivotTables("Übersicht Jahr").PivotFields("Kategorie")
        .PivotItems("").Visible = False
        .PivotItems("0").Visible = False
    End With
    
End Sub

Helaas kan ik geen VBA gebruiken - dat had het hele document eenvoudiger gemaakt - omdat de security policies van die organisatie xlsm blokkeren.

[Lustucru]

YellowOnline schreef op dinsdag 31 maart 2015 @ 16:47:
[...]


Nee, het is een lijst met labels (tekst). De lijst is voorzien op ingave van maximum 2400 labels en de meeste zijn natuurlijk leeg (momenteel zelfs 2395 leeg of zo). Eigenlijk is het misschien gemakkelijker te zien in het bestand zelf: http://www.filedropper.com/sample_10.

Wat rood is, is normaal hidden. Op de tabbladen per maand heb ik enkel April (waar dummy data voor is) unhidden. All worksheets zijn unprotected nu.

Als je voorbeeld representatief is, dan zou imho de oplossing erin liggen dat je minder moeilijk moet doen? Wat ik zie is dat je eerst een tabel opbouwt met unieke namen of "", die reeks weer inkort met een arrayformule en daar dan een draaigrafiek opbouwt. Die grafiek kun je ook direct uit de eerste kolommen trekken en dan heb je het hele probleem niet?

YellowOnline schreef op dinsdag 31 maart 2015 @ 16:56:
[...]
Met filters kan ik wel degelijk de 0 en de "" er uit halen, maar dan is het probleem dan nieuwe waarden niet te zien zijn wanneer er een update is (verschillende keren per dag).

Nieuwe items toevoegen aan handmatig filter aanvinken bij veldinstellingen categorie.

En anders: een waardenfilter >0 heeft het probleem met refresh on open niet maar filter effectief de lege rijen zonder waarde er ook uit.

---

maar a lmet al sowieso te laat...

[ Voor 23% gewijzigd door Lustucru op 01-04-2015 00:50 ]

[YellowOnline]

Lustucru schreef op woensdag 01 april 2015 @ 00:30:
[...]

Als je voorbeeld representatief is, dan zou imho de oplossing erin liggen dat je minder moeilijk moet doen? Wat ik zie is dat je eerst een tabel opbouwt met unieke namen of "", die reeks weer inkort met een arrayformule en daar dan een draaigrafiek opbouwt. Die grafiek kun je ook direct uit de eerste kolommen trekken en dan heb je het hele probleem niet?


[...]


Nieuwe items toevoegen aan handmatig filter aanvinken bij veldinstellingen categorie.

En anders: een waardenfilter >0 heeft het probleem met refresh on open niet maar filter effectief de lege rijen zonder waarde er ook uit.

---

maar a lmet al sowieso te laat...

Ja, inderdaad te laat nu Ik heb ze de versie zonder filter gestuurd en tenslotte ook een versie met een macro die refresht, filters cleart en opnieuw toepast (zie Belindo), voor het geval hun security policies het alsnog toe zouden laten.

Mijn originele idee was overigens zonder pivot table, maar ik vond geen goeie manier om al die data dmv. arrays bij elkaar te zetten zonder de CPU al te zeer te laten lijden.