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:
Vysvetlenie= COUNTIFS ($B:$B,$B4,$C:$C,$C4,$D:$D,$D4)>1
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:
Rýchly štart | Viac príkladov | Riešenie problémov | Školenia Autor Dave Bruns= TEXTJOIN (',',TRUE,A1:Z1)