Bendroji formulė
=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)
Santrauka
„Excel“ yra įmontuotas išankstinis nustatymas, skirtas paryškinti pasikartojančias reikšmes su sąlyginiu formatavimu, tačiau jis veikia tik langelių lygiu. Jei norite paryškinti visas eilutes, kurios yra dublikatai, turėsite naudoti savo formulę, kaip paaiškinta toliau.
Jei norite išryškinti pasikartojančias eilutes nerūšiuotame duomenų rinkinyje ir nenorite pridėti pagalbinio stulpelio, galite naudoti formulę, kuri naudoja funkciją COUNTIFS, kad kiekviename duomenų stulpelyje būtų skaičiuojamos pasikartojančios vertės.
Pavyzdžiui, jei langeliuose B4: D11 yra reikšmės ir norite pažymėti visas pasikartojančias eilutes, galite naudoti gana negražią formulę:
=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1
Pavadinti švaresnės sintaksės diapazonai
Priežastis, kodėl aukščiau pateikta formulė yra tokia negraži, yra ta, kad turime visiškai užrakinti kiekvieną stulpelių diapazoną, tada naudojome mišrią nuorodą, kad išbandytume kiekvieną langelį kiekvienoje stulpelyje. Jei sukursite pavadintus intervalus kiekvienam duomenų stulpeliui: col_a, col_b ir col_c, formulę galima parašyti naudojant daug švaresnę sintaksę:
=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1
Paaiškinimas
Formulėje COUNTIFS suskaičiuoja, kiek kartų kiekviena langelio vertė rodoma stulpelyje „pagrindinis“. Pagal apibrėžimą kiekviena reikšmė turi būti rodoma bent kartą, taigi, kai skaičius> 1, vertė turi būti kopija. Nuorodos yra kruopščiai užrakintos, todėl formulė bus teisinga tik tada, kai visi 3 eilutės langeliai bus rodomi daugiau nei vieną kartą atitinkamuose stulpeliuose.
Pagalbinio stulpelio parinktis „apgauna“, sujungiant visas eilutės reikšmes į vieną langelį, naudojant sujungimą. Tada COUNTIF paprasčiausiai suskaičiuoja, kiek kartų ši sujungta reikšmė rodoma D stulpelyje.
Pagalbinis stulpelis + sujungimas
Jei neprieštaraujate prie duomenų pridėti pagalbinio stulpelio, galite gana supaprastinti sąlyginio formatavimo formulę. Pagalbiniame stulpelyje sujungkite visų stulpelių vertes. Pavyzdžiui, E stulpelyje pridėkite formulę, kuri atrodo taip:
=B4&C4&D4
Tada sąlyginio formatavimo taisyklėje naudokite šią formulę:
=COUNTIF($E$4:$E$11,$E4)>1
Tai yra daug paprastesnė taisyklė, ir, jei norite, galite paslėpti pagalbininko stulpelį.
Jei turite tikrai daug stulpelių, galite naudoti funkciją TEXTJOIN („Excel 2016 365“), kad atliktumėte sujungimą naudojant diapazoną:
=TEXTJOIN(",",TRUE,A1:Z1)
Tada galite naudoti COUNTIF, kaip nurodyta aukščiau.
SUMPRODUKTAS
Jei naudojate „Excel“ versiją iki 2007 m., „SUMPRODUCT“ galite naudoti taip:
=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1
