Evidențiem celulele în excel, astfel încât să putem identifica cu ușurință zonele de concentrare pe date. Formatarea celulelor în excel se face și pentru vizualizarea valorilor datelor pe baza culorii și stilului. De exemplu, vă recomandăm să vă concentrați pe cele mai mici 3 valori din datele dvs. Este greu de făcut într-un volum mare de date, dar dacă i-ați putea spune Excel să evidențieze cele mai mici valori, atunci ar fi destul de ușor. Din fericire, putem face asta folosind opțiunea de formatare condiționată în Excel.
Este ușor să evidențiați 3 valori mici în Excel, dar devine puțin dificil atunci când adăugăm criterii. În acest articol, vom învăța cum să evidențiem cele mai mici 3 valori cu o condiție.
Formula generică în formarea condiționată
= ȘI (verifica celula = criterii, valoare celula<= SMALL (IF (interval de criterii = criterii, interval de valori),3))
Verificați celula: Este celula pe care doriți să o verificați dacă include primele criterii (categoria).
Criterii: Criteriile pe care doriți să le filtrați.
Celula valoare: Este celula care conține o valoare numerică. Îl vom compara cu cea de-a treia cea mai mică valoare din categoria sa, în intervalul de valori.
Gama de criterii: Intervalul absolut conține criteriile.
Interval de valori: Intervalul care conține toate valorile.
Să vedem un exemplu pentru a clarifica lucrurile.
Exemplu: Evidențiați cele mai mici trei vânzări realizate de Departamentul 1
Aici am un exemplu de fișier de lucru care conține vânzări realizate de diferite departamente în diferite luni.
Vreau să evidențiez cele mai mici trei vânzări realizate de Departamentul 1.
Să identificăm variabilele pe care le avem aici.
Verificați celula: B2 $ (vrem să verificăm dacă B2 conține „dealer 1”. Deoarece nu vrem să se schimbe coloana, folosim semnul $. Aceasta face ca întregul rând să fie evidențiat.)
Criterii: „Departamentul 1”
Celula valoare: $ C2
Gama de criterii: $ B $ 2: $ B $ 12
Interval de valori: $ C $ 2: $ C $ 12
Acum, că știm toate argumentele de care avem nevoie pentru a trece la formulă, este timpul să implementăm formula generică de mai sus pentru setul de date.
- Selectați datele. Du-te acasă ? formatarea condițională ? Noua regulă
- Selectați opțiunea „Utilizați o formulă pentru a determina ce celulă să formatați”.
- În caseta de text de mai jos, scrieți această formulă
= ȘI ($ B2 = "Dealer 1", $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = "Dealer 1", $ C $ 2: $ C $ 12), 3))
Apăsați butonul OK. Și s-a făcut. Se evidențiază partea de jos trei sau să spunem cele mai mici 3 vânzări din departamentul 1.
Cum functioneazã?
Să descompunem formula din interior.
Această formulă este o formulă matrice, dar CTRL + SHIFT + ENTER nu este necesar în formatarea condiționată.
Deoarece avem două condiții, prima este Dealerul 1 și a doua este valori mai mici sau egale cu cea de-a treia cea mai mică valoare din Dealerul 1. Ambele trebuie să fie ADEVĂRATE. Pentru asta, folosim funcția ȘI.
$ B2 = "Dealer 1"
Primul criteriu este ușor de verificat. Pur și simplu punem această afirmație $ B2 = "Dealer 1". Va fi ADEVĂRAT dacă celula curentă B2 conține Dealer 1.
$ C2 <=MIC(DACĂ($ B $ 2: $ B $ 12 = "Dealer 1", $ C $ 2: $ C $ 12), 3)
Pentru al doilea criteriu, trebuie să filtrăm Dealerul 1 și să găsim cea de-a treia cea mai mică valoare din acesta. Apoi comparați celula criteriilor cu cea de-a treia cea mai mică valoare. Acest lucru se face folosind declarația $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = "Dealer 1", $ C $ 2: $ C $ 12), 3)
Când o evaluăm, această formulă se va rezolva în acest fel:
Verificăm intervalul $ B $ 2: $ B $ 12 și obținem celule care conțin „Dealerul 1” folosind declarația $ B $ 2: $ B $ 12 = „Dealerul 1”. Vom avea o serie de ADEVĂRAT și FALS.
$ C2 <= SMALL (IF ({TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}, $ C $ 2: $ C $ 12), 3)
Pentru fiecare ADEVĂRAT, valoarea va fi returnată din intervalul de valori $ C $ 2: $ C $ 12 și vom avea o altă matrice.
$ C2 <= SMALL ({12; FALSE; 25; FALSE; FALSE; FALSE; FALSE; 18; FALSE; FALSE; 14}, 3)
Acum SMALL Function va returna cea de-a treia cea mai mică valoare din această matrice. (FLASE va fi ignorat).
Acum avem:
$ C2 <= 18
În cele din urmă, formula este
= ȘI ($ B2 = "Dealer 1", $ C2 <= 18)
Acum verifică dacă B2 conține Dealer 1. Da. Prin urmare, este ADEVĂRAT
= ȘI (ADEVĂRAT, $ C2 <= 18)
Apoi, verifică dacă C2 este mai mic decât sau egal cu 18 (a treia cea mai mică vânzare din reprezentanța 1). Este ADEVĂRAT (12).
= ȘI (ADEVĂRAT, ADEVĂRAT)
Deoarece ambele afirmații sunt ADEVĂRATE, formula returnează ADEVĂRAT și primim ROW-ul nostru evidențiat cu culoare. La fel se întâmplă în fiecare rând.
Puțini !!! A fost greu de explicat. Sper că am fost suficient de clar încât să te fac să înțelegi metoda. În practică, este ușor.
Aici am folosit criterii codificate, dar putem oferi și referințe celulare. Acest lucru va face formula mai flexibilă.
Aici pentru Criterii Am folosit referința celulei E1. Indiferent de reprezentantul pe care îl aveți în E1, va fi evidențiată cea mai mică valoare din trei reprezentanți.
Formula în formatarea condiționată este:
= ȘI ($ B2 = $ E $ 1, $ C2 <= SMALL (IF ($ B $ 2: $ B $ 12 = $ E $ 1, $ C $ 2: $ C $ 12), 3))
Deci, da, așa puteți evidenția valoarea de jos a trei cu o condiție în Excel. Sper că articolul a fost de ajutor. Dacă aveți orice întrebare, vă rugăm să folosiți secțiunea de comentarii de mai jos. Puteți pune întrebări legate de acest articol sau orice alt articol legat de Excel. Vom fi bucuroși să vă ajutăm.
Formatare condiționată bazată pe o altă valoare a celulei
IF și formatarea condiționată
Formatare condiționată cu formula 2016
Formatare condiționată folosind VBA în Microsoft Excel
Evidențiați celulele care conțin text specific
Cum să însumați mai multe coloane cu condiție
Articole populare:
50 Comenzi rapide Excel pentru a vă crește productivitatea
Funcția VLOOKUP în Excel
COUNTIF în Excel 2016
Cum se utilizează funcția SUMIF în Excel