Yleinen kaava
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Yhteenveto
Excel sisältää sisäänrakennetun esiasetuksen päällekkäisten arvojen korostamiseksi ehdollisella muotoilulla, mutta se toimii vain solutasolla. Jos haluat korostaa kokonaisia kaksoiskappaleita, sinun on käytettävä omaa kaavaasi alla kuvatulla tavalla.
Jos haluat korostaa päällekkäisiä rivejä lajittelemattomassa tietojoukossa etkä halua lisätä auttaja-saraketta, voit käyttää kaavaa, joka käyttää COUNTIFS-funktiota laskemaan päällekkäiset arvot jokaisessa sarakkeessa.
Esimerkiksi, jos soluissa B4: D11 on arvoja ja haluat korostaa kokonaisia kaksoisrivejä, voit käyttää melko rumaa kaavaa:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Nimetyt alueet puhtaampaa syntaksia varten
Syy yllä olevaan kaavaan on niin ruma, että meidän on lukittava jokainen sarakealue kokonaan, ja sitten käytettävä sekoitettua viittausta jokaisen sarakkeen jokaisen solun testaamiseen. Jos luot nimettyjä alueita jokaiselle datan sarakkeelle: col_a, col_b ja col_c, kaava voidaan kirjoittaa paljon puhtaammalla syntaksilla:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Selitys
Kaavassa COUNTIFS laskee, kuinka monta kertaa kukin solun arvo näkyy sen "vanhempi" -sarakkeessa. Määritelmän mukaan jokaisen arvon on oltava vähintään kerran, joten kun luku> 1, arvon on oltava kaksoiskappale. Viitteet on lukittu huolellisesti, joten kaava palaa tosi vain, kun kaikki kolme riviä solussa näkyvät useammin kuin kerran vastaavissa sarakkeissaan.
Apusarakkeen vaihtoehto "huijaa" yhdistämällä kaikki rivin arvot yhteen soluun ketjutuksen avulla. Sitten COUNTIF yksinkertaisesti laskee, kuinka monta kertaa tämä ketjutettu arvo näkyy sarakkeessa D.
Helper-sarake + ketjutus
Jos et halua lisätä auttajasaraketta tietoihisi, voit yksinkertaistaa ehdollisen muotoilun kaavaa melko vähän. Kiinnitä apusarakkeessa arvot kaikista sarakkeista. Lisää esimerkiksi sarakkeeseen E kaava, joka näyttää tältä:
=B4&C4&D4
Käytä sitten ehdollisen muotoilun säännössä seuraavaa kaavaa:
=COUNTIF($E$4:$E$11,$E4)>1
Tämä on paljon yksinkertaisempi sääntö, ja voit piilottaa auttajasarakkeen, jos haluat.
Jos sarakkeita on todella paljon, voit käyttää ketjutusta TEXTJOIN-toiminnolla (Excel 2016 365) alueen avulla:
=TEXTJOIN(",",TRUE,A1:Z1)
Voit sitten käyttää COUNTIF: ää kuten yllä.
YHTEENVETO
Jos käytät Excel-versiota ennen vuotta 2007, voit käyttää SUMPRODUCTia näin:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
