Egy igazán kemény Pivot-dió

2018. április 30. - Office Guru

Van a Pivot-tábláknak több, roppant módon idegesítő tulajdonsága, amelyek többségét azért beállításokkal lehet kezelni (például, hogy az adatok frissítésével ne állítsa mindig át az oszlopok szélességét), viszont van olyan, amelyet VBA nélkül már csak igen bajosan lehet megoldani. Ennek ellenére azért én most megpróbálkozom egy igazán kemény dióval. Adott egy kis Pivot-tábla, amelyben az értékek nagyság szerint vannak sorba rendezve:

pivot1_1.JPGEbben a táblában a harmadik helyezett érték (a 3. legnagyobb) alatt mindig szeretnénk egy vonalat látni:

pivot2_1.JPGAlapesetben ha változtatunk az adatforrásunkon, majd utána az Analyze ribbonfül Data szekciójából nyomunk egy Refresht, akkor máris bukott a mutatványunk:

pivot3_1.JPG

pivot4_1.JPGA jobb egérgombbal való kattintás után előhívható menüből megnyitható PivotTable Options alatt is hiába keresgélünk, bizonyos formátumoknál a "Preserve cell formatting on update" működik, itt viszont nem (akár ki, akár be van kapcsolva):

pivot5_1.JPGHa pedig még plusz sorokat is hozzáadunk, akkor meg végképp szétesett a tábla:

pivot6_1.JPGEgy lehetséges megoldás a kívánt cél elérésére a Conditional Formatting használata - álljunk a Pivot-táblánkra, majd a Home ribbonfül Styles szekciójában hívjuk le a Conditional Formatting legördülő menüjét és hozzunk létre egy új Rulet:

pivot9_1.JPGFigyeljünk rá, hogy az Apply Rule az összes értéket tartalmazó cellánkra vonatkozzon! A Rule típusa legyen "Format only top or bottom ranked values". A Top 3 értékre tegyünk először alsó bordert, majd ugyanezeket a lépéseket ismételjük meg, de akkor már úgy, hogy a Top 2 értékről távolítsuk el az alsó bordert, tehát ezzel elértük, hogy a 3. értékünk mindig aláhúzott lesz:

pivot10_1.JPGMost már lehet frissítgetni:

pivot8_1.JPGAz aláhúzás ott lesz szépen a harmadik helyezett érték alatt:

pivot11_1.JPGA megoldás hiányossága, hogy a Row Label oszlopban már nincs aláhúzás, másrészt a borderek formázási lehetőségei elég korlátozottak Conditional Formattingban. Ennek tükrében pedig a lecke fel van adva minden kedves Olvasónak - ha valakinek van nem VBA-s ötlete a fenti problémára, ne fogja vissza magát!

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr3313881992

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

alfredaurel 2018.05.01. 09:18:18

Eloszor erre gondoltam, conditional formatting-nal, formulahoz:=row(A4)=6

Viszont ez refresh-nel eltuntet ia B oszlop alahuzasat. Viszont ha kombinalod a te rule-jaiddal, bar nem tul elegans de megoldja a problemat

Office Guru 2018.05.01. 21:09:59

@alfredaurel: Hm...sajnos nekem így is elcsesződik a B oszlop. :( De majd még próbálkozom. Köszi