Selectarea valorilor unice (elimina duplicatele dintr-o listă) în ms Excel - compatibil cu Microsoft Excel

Având o listă cu valori duplicate, creați o listă care constă numai din valorile unice. Când adăugați valori noi la lista inițială a listei unice valori care urmează să fie actualizate automat.

Să presupunem că coloana A are o listă cu valori duplicate, de exemplu, o listă cu numele companiilor.

Selectarea valorilor unice (elimina duplicatele dintr-o listă) în ms Excel - compatibil cu Microsoft Excel

În unele celule sunt duplicate ale listei originale - noua listă de valori unice nu ar trebui să le conțină.

Selectarea valorilor unice (elimina duplicatele dintr-o listă) în ms Excel - compatibil cu Microsoft Excel

Pentru claritate, valorile unice din lista de surse sunt evidențiate folosind formatarea condițională.

Selectarea valorilor unice (elimina duplicatele dintr-o listă) în ms Excel - compatibil cu Microsoft Excel

În primul rând, creați un interval dinamic. reprezentând lista inițială. În cazul în care lista de surse va fi adăugată la noua valoare, atunci acesta va fi inclus automat în intervalul dinamic al următoarei formule și nu trebuie să fie modificate.

Pentru a crea un domeniu dinamic:

  • Fila Formulele din grupul numele definite, faceți clic pe numele Alocați;
  • în caseta Nume, tastați: Iskhodnyy_spisok;
  • în câmpul Range, introduceți formula = OFFSET ($ A $ UnikalnyeZnacheniya 5 ;;; COUNTA (UnikalnyeZnacheniya $ A $ 5: !! $ A $ 30))
  • faceți clic pe OK.

Crearea unei liste de valori unice în coloana B, folosind o matrice cu formula (vezi. Exemplu de fișier). Pentru a face acest lucru, introduceți următoarea formulă în celula B5:

Selectarea valorilor unice (elimina duplicatele dintr-o listă) în ms Excel - compatibil cu Microsoft Excel

După introducerea unei formule în loc de ENTER trebuie să apăsați CTRL + SHIFT + ENTER. Apoi, pentru a copia formula în jos, de exemplu, folosind markeri necesari. Pentru toate valorile listei originale au fost garantate valori unice afișate în listă, aveți nevoie pentru a face dimensiunea listei unice de valori egale cu dimensiunea listei inițiale (în cazul în care toate valorile nu se repetă lista inițială). În cazul unei liste sursă a unui număr mare de valori care se repetă, o listă de valori unice pot fi făcute mai mici prin eliminarea formulei inutile pentru a evita calcule inutile, inhibând foaie de conversie.

Să lucrăm mai mult cu formula:

  • Aici utilizarea funcțiilor COUNTIF () este neobișnuit: drept criteriu (al doilea argument) nu este specificată o valoare unică, ci o întreagă gamă de Iskhodnyy_spisok. Prin urmare, funcția nu returnează o singură valoare, ci o întreagă gamă de zerouri și cele. Se returnează 0 dacă valoarea lista de start nu se găsește într-un interval B4: B4 (B4: B5, etc.), și 1 dacă este găsit. De exemplu, în formula de celule B5 COUNTIF (B $ 4: B5; Iskhodnyy_spisok) returnează un tablou. Ie Lista inițială S-au găsit 4 valori «OOO Horn și copite» (B5). Array vezi ușurință apăsând F9 (în COUNTIF expresie evidențiați formula bar (B $ cu 4 :. B5; Iskhodnyy_spisok) Apăsați F9 formula afișată în locul rezultatului său.);
  • MECI () - returnează poziția primul zero în matrice din etapa anterioară. Primul zero corespunde unei valori nu a fost încă găsită în lista sursă (adică, valoarea „de companie de încredere“, formula in celula B5);
  • INDEX () - restabilește valoarea poziției sale în gama Iskhodnyy_spisok;
  • IFERROR () suprimă o problemă care apare atunci când funcția MECI () încearcă în matrice de zerouri și cele returnate COUNTIF (). Găsiți 0, care nu este (o situație apare în celula B12. Atunci când toate valorile unice au fost derivate din lista inițială.)

Formula va funcționa, iar în cazul în care lista sursă conține valorile numerice.

Dacă tabela sursă conține lacune, trebuie să utilizați o formulă matrice diferită (a se vedea fișier mostră foaie trece.)
= IFERROR (INDEX ($ A $ 5: $ A $ 19;
MECI (0; IF (ISBLANK ($ A $ 5: A19); ""; COUNTIF ($ B $ 4: B4; $ A $ 5: $ A $ 19)), 0)
); "")

Selectarea valorilor unice (elimina duplicatele dintr-o listă) în ms Excel - compatibil cu Microsoft Excel

În principiu, este posibil să se facă fără utilizarea unor formule matrice. plasarea unei aeriene coloană suplimentară pentru calculele intermediare (vezi. foaia „Fara CSE“ din fisierul exemplu).

SFAT:
Lista de valori unice pot fi create în diferite moduri, de exemplu, cu ajutorul filtrului îmbunătățită (a se vedea. Articolul Selectarea siruri de caractere unice cu ajutorul filtrului avansat), tabelele pivot sau meniul Date / Funcționare Date / Eliminare duplicate. Fiecare metodă are avantajele și dezavantajele sale. Dar, în acest articol dorim să atunci când adăugați valori noi la lista inițială de valori unice liste ar trebui să fie actualizate în mod automat, astfel încât există o listă a construi folosind formule.

SOVET2:
Pentru cei care creează o listă de valori unice, în scopul de a forma o listă derulantă ar trebui să ia în considerare faptul că formula de mai sus returnează textul gol „“. care necesită o manipulare atentă, mai ales în calculul valorilor (în loc de COUNTA funcția obișnuită () necesitatea de a utiliza COUNTIF () cu argumente speciale). De exemplu, a se vedea. Articolul dinamic lista drop-down în MS exce L.

articole conexe

Citește alte articole care rezolva probleme similare, în MS Excel. Acest lucru vă va permite să rezolve o clasă largă de probleme similare.