Vispārēja formula
=COUNTIF(list,A1)=0
Kopsavilkums
Lai salīdzinātu sarakstus un izceltu vērtības, kas pastāv vienā, bet ne otrā, varat piemērot nosacītu formatēšanu ar formulu, kuras pamatā ir funkcija COUNTIF. Piemēram, lai izceltu vērtības A1: A10, kas nepastāv C1: C10, atlasiet A1: A10 un izveidojiet nosacījuma formatēšanas kārtulu, pamatojoties uz šo formulu:
=COUNTIF($C$1:$C$10,A1)=0
Piezīme: izmantojot nosacītu formatēšanu, ir svarīgi atlasē ievadīt formulu attiecībā pret "aktīvo šūnu", kas šajā gadījumā tiek pieņemts kā A1.
Paskaidrojums
Šo formulu novērtē katrai no 10 šūnām A1: D10. A1 mainīsies uz vērtējamās šūnas adresi, savukārt C1: C10 tiek ievadīts kā absolūta adrese, tāpēc tā nemaz nemainīsies.
Šīs formulas atslēga ir = 0 beigās, kas "apgāž" formulas loģiku. Katrai vērtībai A1: A10 COUNTIF atgriež reižu skaitu, cik liela vērtība tiek parādīta C1: C10. Kamēr vērtība C1: C10 tiek parādīta vismaz vienu reizi, COUNTIF atgriezīs skaitli, kas nav nulle, un formula atgriezīs FALSE.
Bet, ja vērtība C1: C10 nav atrasta, COUNTIF atgriež nulli un, tā kā 0 = 0, formula atgriezīs vērtību TRUE un tiks izmantots nosacītais formatējums.
Nosauktie diapazoni vienkāršai sintaksei
Ja meklējamo sarakstu (šajā gadījumā C1: C10) nosaucat ar nosauktu diapazonu, formulu ir vienkāršāk lasīt un saprast:
=COUNTIF(list,A1)=0
Tas darbojas, jo nosauktie diapazoni tiek automātiski absolūti.
Lielo un mazo burtu versija
Ja jums ir nepieciešams reģistrjutīgo reģistru skaits, varat izmantot šādu formulu:
=SUMPRODUCT((--EXACT(A1,list)))=0
EXACT funkcija veic reģistrjutīgu novērtēšanu, un SUMPRODUCT saskaita rezultātu. Tāpat kā ar COUNTIF, šī formula atgriezīsies, kad rezultāts būs nulle. Tā kā tests ir reģistrjutīgs, “ābols” tiks rādīts kā trūkstošs, pat ja otrajā sarakstā būs “Apple” vai “APPLE”. Sīkāku skaidrojumu skatiet šajā lapā.
