Numărul de articole disponibile pentru filtrare este limitat. Excel nu poate filtra coloanele în care numărul de articole depășește 999 (nu numărul de rânduri).
Pentru a filtra când există mai mult de 999 de articole, utilizați filtrul avansat.
Pentru a crea un filtru avansat, vom utiliza funcțiile „OFFSET” și „COUNTA” în Microsoft Excel.
COUNTA: Returnează numărul numărului de celule care conțin valori.
Sintaxa funcției „COUNTA”: = COUNTA (valoare1, valoare2, valoare3 ….)
Exemplu: În intervalul A1: A5, celulele A2, A3 și A5 conțin valorile, iar celulele A1 și A4 sunt goale. Selectați celula A6 și scrieți formula-
= COUNTA (A1: A5) funcția va reveni 3
DECALAJ: Returnează o referință la un interval care este compensat de un număr de rânduri și coloane dintr-un alt interval sau celulă.
Sintaxa funcției OFFSET: = OFFSET (referință, rânduri, cols, înălțime, lățime)
Referinţă:- Aceasta este celula sau intervalul din care doriți să compensați.
Rânduri și coloane de mutat: - Numărul de rânduri pe care doriți să le mutați de la punctul de plecare și ambele pot fi pozitive, negative sau zero.
Înălțime și lățime: - Aceasta este dimensiunea intervalului pe care doriți să îl returnați. Acesta este un câmp opțional.
Să luăm un exemplu pentru a înțelege funcția Offset în Excel.
Avem date în intervalul A1: D10. Coloana A conține codul produsului, coloana B conține cantitatea, coloana C conține pe costul produsului și coloana D conține costul total. Trebuie să returnăm valoarea celulei C5 în celula E2.
Pentru a obține rezultatul dorit, trebuie să urmăm pașii de mai jos.
- Selectați celula E2 și scrieți formula.
- = OFFSET (A1,4,2,1,1)și apăsați Enter pe tastatură.
- Funcția va returna valoarea celulei C5.
În acest exemplu, trebuie să obținem valoarea din celula C5 la E2. Celula noastră de referință este prima celulă din intervalul care este A1 și C5 este 4 rânduri dedesubt și 2 coloane la dreapta din A1. Prin urmare, formula este = OFFSET (A1,4,2,1,1) sau = OFFSET (A1,4,2) (deoarece 1,1 este opțional).
Acum, să luăm un exemplu pentru a recupera ultima valoare dintr-o listă dinamică.
Avem nume de țări într-o gamă. Acum, dacă adăugăm mai multe țări la această listă, aceasta ar trebui să fie disponibilă automat în lista derulantă.
Pentru a pregăti un filtru avansat, urmați pașii de mai jos: -
- Selectați celula B2.
- Accesați fila Date, selectați Data Validation din grupul Data tools.
- Va apărea caseta de dialog „Validarea datelor”. În fila „Setări”, selectați „Personalizat” din lista derulantă Permiteți.
- Se va activa caseta de formule.
- Scrieți formula în această casetă.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Faceți clic pe OK.
- În acest stadiu, ultima celulă actualizată este A11.
- Pentru a verifica dacă validarea datelor funcționează corect, adăugați un nume de oraș în celula A12.
De îndată ce adăugați o intrare în A12, aceasta va fi adăugată la lista derulantă.
Acesta este modul în care putem adăuga mai multe intrări decât 999 articole în Microsoft Excel.