Home-theater-designers
Els conjunts de dades a Excel solen contenir el mateix valor diverses vegades en una columna. De vegades, pot ser útil saber quants valors únics hi ha en una columna. Per exemple, si teniu una botiga i teniu un full de càlcul de totes les vostres transaccions, és possible que vulgueu determinar quants clients únics teniu, en lloc de comptar cada transacció.
És possible fer-ho comptant valors únics a Excel mitjançant els mètodes que us parlarem a continuació.
Elimineu les dades duplicades d'una columna
Una manera ràpida i bruta de comptar els valors únics a Excel és eliminar els duplicats i veure quantes entrades queden. Aquesta és una bona opció si necessiteu una resposta ràpidament i no necessiteu fer un seguiment del resultat.
Copieu les dades en un full nou (per no suprimir accidentalment les dades que necessiteu). Seleccioneu els valors o la columna de la qual vulgueu eliminar els valors duplicats. A la Eines de dades secció del Dades selecció de pestanya Elimina els duplicats . Això elimina totes les dades duplicades i només deixa els valors únics.
El mateix procés funciona si la informació es divideix en dues columnes. La diferència és que heu de seleccionar les dues columnes. En el nostre exemple, tenim una columna per al nom i una segona per al cognom.
Si voleu fer un seguiment del nombre de valors únics, és millor que escriviu una fórmula. A continuació us mostrarem com fer-ho.
Relacionat: Com filtrar a Excel per mostrar les dades que desitgeu
Compteu valors únics amb una fórmula Excel
Per comptar només valors únics hem de combinar diverses funcions d'Excel. Primer, hem de comprovar si cada valor és un duplicat i, a continuació, hem de comptar les entrades restants. També hem d’utilitzar una funció de matriu.
Si només busqueu la resposta, utilitzeu aquesta fórmula substituint cada instància de A2: A13 per les cel·les que vulgueu utilitzar:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}Com hi arribem és una mica complicat. Per tant, si voleu entendre per què funciona aquesta fórmula, la descompondrem d’una peça a la vegada a continuació.
Explicació d'una funció de matriu
Primer comencem explicant què és una matriu. Una matriu és una única variable que conté diversos valors. És com referir-se a un munt de cel·les d'Excel alhora en lloc de referir-se a cada cel·la individualment.
Aquesta és una estranya distinció des del nostre punt de vista. Si diem a una fórmula que miri les cel·les A2: A13 normalment o com una matriu, les dades ens semblen iguals. La diferència radica en la forma en què Excel tracta les dades entre bastidors. És una diferència tan subtil que les versions més recents d'Excel ja no en distingeixen ni les versions anteriors.
Per als nostres propòsits, és més important saber com podem utilitzar matrius. Si teniu la versió més nova d'Excel, emmagatzema automàticament les dades com a matriu quan és més eficient fer-ho. Si teniu una versió anterior, quan hàgiu acabat d’escriure la fórmula, premeu Ctrl + Maj + Retorn . Un cop ho feu, la fórmula estarà envoltada de claudàtors per mostrar que està en mode matriu.
Presentació de la funció de freqüència
La funció de freqüència ens indica quantes vegades apareix un número en una llista. Això és fantàstic si esteu treballant amb números, però la nostra llista és de text. Per utilitzar aquesta funció, primer hem de trobar una manera de convertir el nostre text en números.
Si intenteu comptar els valors únics en una llista de números, podeu ometre el pas següent.
Utilització de la funció MATCH
La funció MATCH retorna la posició de la primera aparició d'un valor. Podem utilitzar-lo per convertir la nostra llista de noms en valors numèrics. Necessita conèixer tres dades:
- Quin valor busqueu?
- Quin conjunt de dades comproveu?
- Cerqueu valors superiors, inferiors o iguals al valor objectiu?
En el nostre exemple, volem buscar cada nom dels nostres clients al nostre full de càlcul Exel per veure si el seu nom exacte torna a aparèixer en altres llocs.
com emetre a Xbox One
A l'exemple anterior, cerquem Tiah Gallagher (A2) a la nostra llista (A2: A13) i volem que coincideixi exactament. El 0 de l'últim camp especifica que ha de coincidir exactament. El nostre resultat ens indica on va aparèixer el nom a la llista. En aquest cas, era el primer nom, de manera que el resultat és 1.
El problema és que estem interessats en tots els nostres clients, no només en Tiah. Però, si intentem cercar A2: A13 en lloc de només A2, obtindrem un error. Aquí és on les funcions de matriu són útils. El primer paràmetre només pot prendre una variable o, en cas contrari, retorna un error. Però, les matrius es tracten com si fossin una única variable.
Ara la nostra funció indica a Excel que comprovi si hi ha coincidències per a tota la nostra matriu. Però espereu, el nostre resultat no ha canviat. Encara diu 1. Què passa aquí?
La nostra funció és retornar una matriu. Revisa cada element de la nostra matriu i comprova si hi ha coincidències. Els resultats de tots els noms es guarden en una matriu, que es retorna com a resultat. Com que una cel·la només mostra una variable a la vegada, mostra el primer valor de la matriu.
Ho podeu comprovar vosaltres mateixos. Si canvieu el primer rang a A3: A13, el resultat canviarà a 2. Això es deu al fet que el nom d'Eiliyah és el segon de la llista i aquest valor es desa primer a la matriu ara. Si canvieu el primer interval a A7: A13, tornareu a obtenir 1 perquè el nom de Tiah apareix per primera vegada a la primera posició del conjunt de dades que comprovem.
Relacionat: Fórmules Excel que us ajudaran a resoldre problemes de la vida real
Utilització de la funció de freqüència
Ara que hem canviat els noms a valors numèrics, podem utilitzar la funció FREQÜÈNCIA. De manera similar a MATCH, requereix un objectiu a buscar i un conjunt de dades per comprovar. De manera similar a MATCH, no volem cercar només un valor, sinó que volem que la funció comprovi cada element de la nostra llista.
L'objectiu que volem que comprovi la funció FREQÜÈNCIA és cada element de la matriu que ha retornat la nostra funció MATCH. I volem comprovar el conjunt de dades retornat per la funció MATCH. Per tant, enviem la funció MATCH que hem creat anteriorment per a tots dos paràmetres.
Si cerqueu números únics i ometeu el pas anterior, envieu l’interval de números com a tots dos paràmetres. Per cercar tots els números de la llista, també haureu d’utilitzar una funció de matriu, així que recordeu de prémer Ctrl + Maj + Retorn després d'haver introduït la fórmula si utilitzeu una versió anterior d'Excel.
Ara el nostre resultat és 2. De nou, la nostra funció és retornar una matriu. Torna una matriu del nombre de vegades que ha aparegut cada valor únic. La cel·la mostra el primer valor de la matriu. En aquest cas, el nom de Tiah apareix dues vegades, de manera que la freqüència retornada és 2.
Utilització de la funció IF
Ara la nostra matriu té el mateix nombre de valors que els valors únics. Però no acabem d’acabar. Necessitem una manera de sumar-ho. Si convertim tots els valors de la matriu a 1 i els sumem, finalment sabrem quants valors únics tenim.
Podem crear una funció IF que canviï tots els valors superiors a zero a 1. Llavors tots els valors seran iguals a 1.
Per fer-ho, volem que la nostra funció IF comprovi si els valors de la nostra matriu FREQÜÈNCIA són superiors a zero. Si és cert, hauria de retornar el valor 1. Notareu que ara el primer valor de la matriu torna com un.
Utilització de la funció SUM
Estem a la recta final! L'últim pas és sumar la matriu.
Emboliqueu la funció anterior en una funció SUM. Acabat! Per tant, la nostra fórmula final és:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}Comptar entrades úniques a Excel
Aquesta és una funció avançada que requereix molts coneixements sobre Excel. Provar-ho pot intimidar. Però, un cop configurat, pot ser molt útil, de manera que pot valer la pena revisar la nostra explicació per assegurar-vos que l’enteneu.
Si no necessiteu comptar entrades úniques que sovint, la punta ràpida i bruta d’eliminar els valors duplicats funcionarà en una mica.
Compartir Compartir Tweet Correu electrònic Com copiar fórmules a Microsoft ExcelAprendre tots els millors mètodes per copiar i enganxar fórmules al full de càlcul d'Excel és una bona manera de començar a estalviar temps.
Llegiu a continuació Temes relacionats- Productivitat
- Full de càlcul
- Microsoft Excel
- Anàlisi de dades
J. Seaton és un escriptor científic especialitzat en la descomposició de temes complexos. És doctora per la Universitat de Saskatchewan; la seva investigació es va centrar a utilitzar l'aprenentatge basat en jocs per augmentar la implicació dels estudiants en línia. Quan no treballa, la trobareu amb ella llegint, jugant a videojocs o fent jardineria.
Més de Jennifer SeatonSubscriu-te al nostre butlletí
Uniu-vos al nostre butlletí per obtenir consells tècnics, ressenyes, llibres electrònics gratuïts i ofertes exclusives.
Feu clic aquí per subscriure-us