Excel

Zvýraznite duplicitné riadky

Highlight Duplicate Rows

Vzorec programu Excel: Zvýraznite duplicitné riadkyGenerický vzorec | _+_ | Zhrnutie

Excel obsahuje vstavanú predvoľbu na zvýraznenie duplicitných hodnôt s podmieneným formátovaním, funguje však iba na úrovni buniek. Ak chcete zvýrazniť celé riadky, ktoré sú duplikáty, budete musieť použiť svoj vlastný vzorec, ako je to popísané nižšie.





Ak chcete v netriedenej množine údajov zvýrazniť duplicitné riadky a nechcete pridať pomocný stĺpec, môžete použiť vzorec, ktorý pomocou funkcie COUNTIFS počíta duplicitné hodnoty v každom stĺpci údajov.

Ak máte napríklad hodnoty v bunkách B4: D11 a chcete zvýrazniť celé duplicitné riadky, môžete použiť dosť škaredý vzorec:





= COUNTIFS (A:A,$A1,B:B,$B1,C:C,$C1)

Pomenované rozsahy pre čistejšiu syntax

Dôvod, prečo je vyššie uvedený vzorec taký škaredý, je ten, že musíme úplne uzamknúť každý rozsah stĺpcov a potom použiť zmiešanú referenciu na testovanie každej bunky v každom stĺpci. Ak vytvoríte pomenované rozsahy pre každý stĺpec v údajoch: col_a, col_b a col_c, vzorec je možné zapísať s oveľa čistejšou syntaxou:

 
= COUNTIFS ($B:$B,$B4,$C:$C,$C4,$D:$D,$D4)>1
Vysvetlenie

Vo vzorci COUNTIFS počíta, koľkokrát sa každá hodnota v bunke zobrazí v jej nadradenom stĺpci. Podľa definície sa každá hodnota musí objaviť aspoň raz, takže keď je počet> 1, musí ísť o duplikát. Odkazy sú starostlivo uzamknuté, takže vzorec sa vráti ako pravdivý, iba ak sa všetky 3 bunky v rade zobrazia v príslušných stĺpcoch viac ako raz.



ako vypočítať percento rastu v programe Excel

Pomocný stĺpec „podvádza“ kombináciou všetkých hodnôt v rade dohromady v jednej bunke pomocou zreťazenia. Potom COUNTIF jednoducho spočíta, koľkokrát sa táto zreťazená hodnota objaví v stĺpci D.

Pomocný stĺpček + zreťazenie

Ak vám nevadí pridať a pomocný stĺp k svojim údajom môžete vzorec podmieneného formátovania dosť zjednodušiť. V pomocnom stĺpci zreťazte hodnoty zo všetkých stĺpcov. Pridajte napríklad vzorec do stĺpca E, ktorý vyzerá takto:

 
= COUNTIFS (col_b,$B4,col_c,$C4,col_d,$D4)>1

Potom v pravidle podmieneného formátovania použite nasledujúci vzorec:

 
=B4&C4&D4

Toto je oveľa jednoduchšie pravidlo a pomocný stĺpec môžete skryť, ak chcete.

použiť definované názvy na existujúce vzorce

Ak máte skutočne veľký počet stĺpcov, môžete pomocou funkcie TEXTJOIN (Excel 2016 365) vykonať zreťazenie pomocou rozsahu:

 
= COUNTIF ($E:$E,$E4)>1

Potom môžete použiť COUNTIF, ako je uvedené vyššie.

SUMPRODUCT

Ak používate verziu programu Excel pred rokom 2007, môžete použiť SUMPRODUCT takto:

 
= TEXTJOIN (',',TRUE,A1:Z1)
Rýchly štart | Viac príkladov | Riešenie problémov | Školenia Autor Dave Bruns


^