únicos valores unicos una textos sumar repetidos registros lista fórmula extraer entre duplicados contar condiciones con como excel excel-formula

unicos - Obtener valores únicos en Excel utilizando solo fórmulas



extraer valores únicos con una fórmula (20)

Solución

Creé una función en VBA para usted, por lo que puede hacer esto ahora de una manera fácil.
Cree un módulo de código VBA (macro) como puede ver en este tutorial .

  1. Presiona Alt + F11
  2. Haga clic en Module en Insert .
  3. Pegar código
  4. Si Excel dice que su formato de archivo no es compatible con macros, guárdelo como Excel Macro-Enabled en Save As .

Código fuente

Function listUnique(rng As Range) As Variant Dim row As Range Dim elements() As String Dim elementSize As Integer Dim newElement As Boolean Dim i As Integer Dim distance As Integer Dim result As String elementSize = 0 newElement = True For Each row In rng.Rows If row.Value <> "" Then newElement = True For i = 1 To elementSize Step 1 If elements(i - 1) = row.Value Then newElement = False End If Next i If newElement Then elementSize = elementSize + 1 ReDim Preserve elements(elementSize - 1) elements(elementSize - 1) = row.Value End If End If Next distance = Range(Application.Caller.Address).row - rng.row If distance < elementSize Then result = elements(distance) listUnique = result Else listUnique = "" End If End Function

Uso

Simplemente ingrese =listUnique(range) en una celda. El único parámetro es el range que es un rango ordinario de Excel. Por ejemplo: A$1:A$28 o H$8:H$30 .

Condiciones

  • El range debe ser una columna.
  • La primera celda donde llamas a la función debe estar en la misma fila donde comienza el range .

Ejemplo

Caso regular

  1. Ingrese datos y llame a la función.
  2. Hazlo crecer.
  3. Voilà.

Caja de celda vacía

Funciona en columnas que tienen celdas vacías. Además, la función no genera nada (no errores) si sobrevasa las celdas (llamando a la función) en lugares donde no debería haber salida, como lo hice en la parte "2. Grow it" del ejemplo anterior.

¿Conoces una forma en Excel de "calcular" por fórmula una lista de valores únicos?

Por ejemplo: un rango contiene valores "red" , "blue" , "red" , "green" , "blue" , "black"
y quiero tener como resultado "red , "blue" , "green" , "black" + eventualmente otras 2 celdas en blanco.

Ya encontré una forma de obtener una lista ordenada calculada usando PEQUEÑO o GRANDE combinado con ÍNDICE, pero me gustaría tener este tipo calculado también, SIN UTILIZAR VBA.

Solución optimizada de VBScript

Utilicé el código de totymedli, pero lo encontré atascado cuando usé rangos grandes (como señalaron otros), así que optimicé un poco su código. Si alguien está interesado en obtener valores únicos usando VBScript pero encuentra que el código de totymedli es lento al actualizar, intente esto:

Function listUnique(rng As Range) As Variant Dim val As String Dim elements() As String Dim elementSize As Integer Dim newElement As Boolean Dim i As Integer Dim distance As Integer Dim allocationChunk As Integer Dim uniqueSize As Integer Dim r As Long Dim lLastRow As Long lLastRow = rng.End(xlDown).row elementSize = 1 unqueSize = 0 distance = Range(Application.Caller.Address).row - rng.row If distance <> 0 Then If Cells(Range(Application.Caller.Address).row - 1, Range(Application.Caller.Address).Column).Value = "" Then listUnique = "" Exit Function End If End If For r = 1 To lLastRow val = rng.Cells(r) If val <> "" Then newElement = True For i = 1 To elementSize - 1 Step 1 If elements(i - 1) = val Then newElement = False Exit For End If Next i If newElement Then uniqueSize = uniqueSize + 1 If uniqueSize >= elementSize Then elementSize = elementSize * 2 ReDim Preserve elements(elementSize - 1) End If elements(uniqueSize - 1) = val End If End If Next If distance < uniqueSize Then listUnique = elements(distance) Else listUnique = "" End If End Function

Este es un anticipo, y hay algunas soluciones, pero se me ocurrió una fórmula más corta y simple que cualquier otra que encontré, y podría ser útil para cualquiera que pase.

He nombrado los colores de la lista de Colors (A2: A7), y la fórmula de matriz puesta en la celda C2 es esto ( fijo ):

=IFERROR(INDEX(Colors,MATCH(SUM(COUNTIF(C$1:C1,Colors)),COUNTIF(Colors,"<"&Colors),0)),"")

Use Ctrl+Shift+Enter para ingresar la fórmula en C2 y copie C2 en C3: C7 .

Explicación con datos de muestra {"rojo"; "azul"; "rojo"; "verde"; "azul"; "negro"}:

  1. COUNTIF(Colors,"<"&Colors) devuelve una matriz (n. ° 1) con el recuento de valores que son más pequeños que cada elemento de los datos {4; 1; 4; 3; 1; 0} (negro = 0 elementos más pequeños, azul = 1 elemento, rojo = 4 elementos). Esto se puede traducir a un valor de clasificación para cada elemento.
  2. COUNTIF(C$1:C...,Colors) devuelve una matriz (n. ° 2) con 1 para cada elemento de datos que ya está en el resultado ordenado. En C2, devuelve {0; 0; 0; 0; 0; 0} y en C3 {0; 0; 0; 0; 0; 1} porque "negro" es el primero en el orden y el último en los datos. En C4 {0; 1; 0; 0; 1; 1} indica "negro" y todas las ocurrencias de "azul" ya están presentes.
  3. La SUM devuelve el valor de clasificación k-ésimo , contando todas las ocurrencias de valores más pequeños que ya están presentes (suma de la matriz n. ° 2).
  4. MATCH encuentra el primer índice del valor de clasificación k-ésimo (índice en la matriz n. ° 1).
  5. El IFERROR es solo para ocultar el error #N/A en las celdas inferiores, cuando se completa la lista única ordenada.

Para saber cuántos elementos únicos tiene, puede usar esta fórmula regular :

=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))

Esto solo funciona si los valores están en orden, es decir, todos los "rojos" están juntos y todos los "azules" están juntos, etc. suponga que sus datos están en la columna A comenzando en A2 - (No comience desde la fila 1) En el Tipo B2 en 1 En b3 escriba = if (A2 = A3, B2, B2 + 1) Arrastre hacia abajo la fórmula hasta el final de sus datos Todo "Rojo" será 1, todo "azul" será 2 todo "verde" lo hará ser 3 etc.

En C2 escriba 1, 2, 3 etc. bajando por la columna En D2 = DESPLAZAMIENTO ($ A $ 1, COINCIDIR (c2, $ B $ 2: $ B $ x, 0), 0) - donde x es la última celda Arrastrar hacia abajo , solo aparecerán los valores únicos. - poner en algún error de comprobación

He pegado lo que uso en mi archivo de Excel a continuación. Esto toma valores únicos del rango L11:L300 y los rellena en la columna V, V11 en adelante. En este caso, tengo esta fórmula en v11 y la arrastro hacia abajo para obtener todos los valores únicos.

=INDEX(L$11:L$300,MATCH(0,COUNTIF(V$10:V10,L$11:L$300),0))

o

=INDEX(L$11:L$300,MATCH(,COUNTIF(V$10:V10,L$11:L$300),))

esta es una fórmula de matriz

Incluso para obtener un valor único ordenado, se puede hacer usando la fórmula. Esta es una opción que puede usar:

=INDEX($A$2:$A$18,MATCH(SUM(COUNTIF($A$2:$A$18,C$1:C1)),COUNTIF($A$2:$A$18,"<" &$A$2:$A$18),0))

datos de rango: A2:A18

fórmula en la celda C2

Esta es una FORMULA DE ARRAY

La solución de Drew Sherman es muy buena, pero la lista debe ser contigua (sugiere ordenar manualmente, y eso no es aceptable para mí). La solución de Guitarthrower es un poco lenta si el número de elementos es grande y no respeta el orden de la lista original: genera una lista ordenada independientemente.

Quería el orden original de los artículos (que fueron ordenados por la fecha en otra columna), y adicionalmente quería excluir un ítem de la lista final no solo si estaba duplicado, sino también por una variedad de otras razones.

Mi solución es una mejora en la solución de Drew Sherman. Del mismo modo, esta solución usa 2 columnas para cálculos intermedios:

Columna A:

La lista con duplicados y quizás espacios en blanco que desea filtrar. Lo ubicaré en el intervalo A11: A1100 como ejemplo, porque tuve problemas para mover la solución de Drew Sherman a situaciones en las que no comenzó en la primera línea.

Columna B:

Esta fórmula dará como resultado 0 si el valor en esta línea es válido (contiene un valor no duplicado). Tenga en cuenta que puede agregar cualquier otra condición de exclusión que desee en el primer IF, o como otro IF externo.

=IF(ISBLANK(A11);1;IF(COUNTIF($A$11:A11;A11)=1;0;COUNTIF($A11:A$1100;A11)))

Use una copia inteligente para llenar la columna.

Columna C:

En la primera línea encontraremos la primera línea válida:

=MATCH(0;B11:B1100;0)

Desde esa posición, buscamos el siguiente valor válido con la siguiente fórmula:

=C11+MATCH(0;OFFSET($B$11:$B$1100;C11;0);0)

Póngalo en la segunda línea y use la copia inteligente para llenar el resto de la columna. Esta fórmula generará un error # N / D cuando no haya más elementos únicos para apuntar. Aprovecharemos esto en la próxima columna.

Columna D:

Ahora solo tenemos que obtener los valores apuntados por la columna C:

=IFERROR(INDEX($A$11:$A$1100; C11); "")

Use una copia inteligente para llenar la columna. Esta es la lista única de salida.

Me encontré con el mismo problema recientemente y finalmente lo descubrí.

Usando su lista, aquí hay una pegada de mi Excel con la fórmula.

Recomiendo escribir la fórmula en algún lugar en el medio de la lista, como, por ejemplo, en la celda C6 de mi ejemplo y luego copiarla y pegarla arriba y abajo de la columna, la fórmula debe ajustarse automáticamente sin que sea necesario volver a escribirla.

La única celda que tiene una fórmula única diferente está en la primera fila.

Usando su lista ("rojo", "azul", "rojo", "verde", "azul", "negro"); aquí está el resultado: ( No tengo un nivel lo suficientemente alto como para publicar una imagen, así que espero que esta versión de texto tenga sentido )

  • [Columna A: lista original]
  • [Columna B: Resultado de la lista única]

  • [Columna C: fórmula de lista única]

    1. rojo, rojo, =A3
    2. azul, azul, =IF(ISERROR(MATCH(A4,A$3:A3,0)),A4,"")
    3. rojo =IF(ISERROR(MATCH(A5,A$3:A4,0)),A5,"")
    4. verde, verde, =IF(ISERROR(MATCH(A6,A$3:A5,0)),A6,"")
    5. blue =IF(ISERROR(MATCH(A7,A$3:A6,0)),A7,"")
    6. negro, negro, =IF(ISERROR(MATCH(A8,A$3:A7,0)),A8,"")

Me sorprende que esta solución aún no haya surgido. Creo que es uno de los más fáciles

Proporcione un encabezado a sus datos y colóquelo en un rango con nombre dinámico (es decir, si sus datos están en la columna A )

=OFFSET($A$2,0,0,COUNTA($A:$A),1)

Y luego crea una tabla dinámica, haciendo que la fuente sea tu rango con nombre.

Simplemente colocando el encabezado en la sección de filas y obtendrás los valores únicos, ordena de la forma que quieras con la característica incorporada.

Ok, tengo dos ideas para ti. Con suerte, uno de ellos lo llevará a donde necesita ir. Tenga en cuenta que el primero ignora la solicitud de hacer esto como una fórmula, ya que esa solución no es bonita. Pensé que me aseguraría de que la manera fácil realmente no funcionara para ti; ^).

Use el comando de filtro avanzado

  1. Seleccione la lista (o coloque su selección en cualquier lugar dentro de la lista y haga clic en Aceptar si aparece el cuadro de diálogo quejándose de que Excel no sabe si su lista contiene encabezados o no)
  2. Elegir datos / filtro avanzado
  3. Elija "Filtrar la lista, en contexto" o "Copiar a otra ubicación"
  4. Haga clic en "Registros únicos solamente"
  5. Haga clic en Aceptar
  6. Estás listo. Se crea una lista única en su lugar o en una nueva ubicación. Tenga en cuenta que puede grabar esta acción para crear un script de VBA de una línea para hacer esto, que luego podría ser generalizado para que funcione en otras situaciones (por ejemplo, sin los pasos manuales enumerados anteriormente).

Uso de fórmulas (tenga en cuenta que estoy desarrollando una solución Locksfree para terminar con una lista sin agujeros)

Esta solución funcionará con las siguientes advertencias:

  • La lista debe ordenarse (ascendente o descendente no importa). De hecho, es bastante preciso ya que el requisito es que todos los elementos similares deben estar contiguos, pero la clasificación es la forma más fácil de alcanzar ese estado.
  • Se requieren tres nuevas columnas (dos nuevas columnas para los cálculos y una nueva columna para la nueva lista). La segunda y la tercera columna podrían combinarse, pero lo dejaré como ejercicio para el lector.

    Aquí está el resumen de la solución:

    1. Para cada elemento en la lista, calcule el número de duplicados sobre él.
    2. Para cada lugar en la lista única, calcule el índice del siguiente artículo único.
    3. Finalmente, use los índices para crear una nueva lista con solo artículos únicos.

    Y aquí hay un ejemplo paso a paso:

    1. Abra una nueva hoja de cálculo
    2. En a1: a6 ingrese el ejemplo dado en la pregunta original ("rojo", "azul", "rojo", "verde", "azul", "negro")
    3. Ordene la lista: ponga la selección en la lista y elija el comando ordenar.
    4. En la columna B, calcule los duplicados:
      1. En B1, ingrese "= IF (COUNTIF ($ A $ 1: A1, A1) = 1,0, COUNTIF (A1: $ A $ 6, A1))". Tenga en cuenta que los "$" en las referencias de celda son muy importantes ya que harán que el siguiente paso (llenar el resto de la columna) sea mucho más fácil. El "$" indica una referencia absoluta para que cuando el contenido de la celda sea copiado / pegado, la referencia no se actualice (a diferencia de una referencia relativa que se actualizará).
      2. Use la copia inteligente para completar el resto de la columna B: seleccione B1. Mueva su mouse sobre el cuadrado negro en la esquina inferior derecha de la selección. Haga clic y arrastre hacia abajo hasta la parte inferior de la lista (B6). Cuando suelte, la fórmula se copiará en B2: B6 con las referencias relativas actualizadas.
      3. El valor de B1: B6 ahora debería ser "0,0,1,0,0,1". Tenga en cuenta que las entradas "1" indican duplicados.
    5. En la Columna C, crea un índice de elementos únicos:
      1. En C1, ingrese "= Fila ()". Realmente solo quiere C1 = 1 pero usar Fila () significa que esta solución funcionará incluso si la lista no se inicia en la fila 1.
      2. En C2, ingrese "= IF (C1 + 1 <= ROW ($ B $ 6), C1 + 1 + INDICE ($ B $ 1: $ B $ 6, C1 + 1), C1 + 1)". El "si" se usa para evitar que se produzca un #REF cuando el índice llega al final de la lista.
      3. Use la copia inteligente para poblar C3: C6.
      4. El valor de C1: C6 debe ser "1,2,4,5,7,8"
    6. En la columna D, crea la nueva lista única:
      1. En D1, ingrese "= IF (C1 <= ROW ($ A $ 6), ÍNDICE ($ A $ 1: $ A $ 6, C1)," ")". Y, el "si" se usa para detener el caso #REF cuando el índice va más allá del final de la lista.
      2. Use la copia inteligente para poblar D2: D6.
      3. Los valores de D1: D6 ahora deberían ser "negro", "azul", "verde", "rojo", "", "".

    Espero que esto ayude....

    • Para una solución que funciona para valores en múltiples filas y columnas, encontré la siguiente fórmula muy útil, de http://www.get-digital-help.com/2009/03/16/unique-values-from-multiple-columns-using-array-formulas/ Oscar at get-digital.help.com incluso lo analiza paso a paso y con un ejemplo visualizado.

    1) Proporcione el rango de valores de la etiqueta tbl_text

    2) Aplique la siguiente fórmula de matriz con CTRL + MAYÚS + ENTRAR, a la celda B13 en este caso. Cambie $ B $ 12: B12 para hacer referencia a la celda que está encima de la celda donde ingresa esta fórmula.

    =INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)

    3) Copia / arrastra hacia abajo hasta que obtengas N / A.

    Pruebe esta fórmula en la celda B2

    =IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF(B$1:$B1,$A$2:$A$7),0),1),"")

    Después, F2 clic en F2 y presiona Ctrl + Shift + Enter

    Puede usar CONTAR para obtener el número de ocurrencia del valor en el rango. Entonces, si el valor está en A3, el rango es A1: A6, luego en la siguiente columna use un IF (EXACT (COUNTIF (A3: $ A $ 6, A3), 1), A3, ""). Para el A4, sería IF (EXACTO (CONTADOR (A4: $ A $ 6, A3), 1), A4, "")

    Esto le daría una columna donde todos los valores únicos están sin ningún duplicado

    Recurrir a una tabla dinámica puede no contar como usar fórmulas solamente, pero parece más práctico que la mayoría de las otras sugerencias hasta el momento:

    Seleccione la columna con valores duplicados luego vaya a la pestaña Datos, luego herramientas de datos seleccione eliminar selección duplicada 1) "Continuar con la selección actual" 2) Haga clic en el botón Eliminar duplicado .... 3) Haga clic en el botón "Seleccionar todo" 4) Haga clic DE ACUERDO

    ahora obtienes la lista de valores únicos.

    Si uno pone todos los datos en las mismas columnas y utiliza la siguiente fórmula Fórmula de ejemplo: =IF(C105=C104,"Duplicate","Not a Duplicate")

    Pasos

    1. Ordenar los datos
    2. Agregar columna para la fórmula
    3. Comprueba si la celda es igual a la celda de arriba
    4. Luego filtra Not a Duplicate
    5. Opcional: copie los datos calculados por la columna de fórmula y pegue solo como valores (de esa manera, si comienza a eliminar datos, no comienza a obtener errores)
    6. NOTA / ADVERTENCIA: Esto solo funciona si ordena los datos primero

    Fórmula de ejemplo: =IF(C105=C104,"Duplicate","Not a Duplicate")

    Suponiendo que la Columna A contiene los valores de los que desea encontrar una sola instancia única, y tiene una fila de Encabezado, utilicé la siguiente fórmula. Si desea que se escale con un número impredecible de filas, puede reemplazar A772 (donde terminaron mis datos) con = DIRECCIÓN (COUNTA (A: A), 1) .

    = IF (CONTINGENTE (A5: $ A $ 772, A5) = 1, A5, "")

    Esto mostrará el valor único en la ÚLTIMA instancia de cada valor en la columna y no asume ninguna clasificación. Aprovecha la falta de valores absolutos para contar esencialmente con una "ventana deslizante" decreciente de datos. Cuando el conteo en la ventana reducida es igual a 1, esa fila es la última instancia de ese valor en la columna.

    También puedes hacerlo de esta manera.

    Crea los siguientes rangos nombrados:

    nList = the list of original values nRow = ROW(nList)-ROW(OFFSET(nList,0,0,1,1))+1 nUnique = IF(COUNTIF(OFFSET(nList,nRow,0),nList)=0,COUNTIF(nList, "<"&nList),"")

    Con estos 3 rangos nombrados, puede generar la lista ordenada de valores únicos con la siguiente fórmula. Se ordenará en orden ascendente.

    IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-?),nUnique,0)),"")

    Tendrá que sustituir el número de fila de la celda justo encima del primer elemento de su lista ordenada única para el ''?'' personaje.

    p.ej. Si su lista ordenada única comienza en la celda B5, la fórmula será:

    IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-4),nUnique,0)),"")

    Una forma indirecta es cargar su hoja de cálculo de Excel en una hoja de cálculo de Google, usar la función UNIQUE (rango) de Google, que hace exactamente lo que usted desea, y luego guardar la hoja de cálculo de Google nuevamente en formato Excel.

    Admito que esta no es una solución viable para los usuarios de Excel, pero este enfoque es útil para cualquiera que quiera la funcionalidad y pueda usar una hoja de cálculo de Google.

    notamos que es una pregunta muy antigua, pero las personas todavía parecen tener problemas para usar una fórmula para extraer elementos únicos. aquí hay una solución que les devuelve los valores ellos mismos.

    Digamos que tiene "rojo", "azul", "rojo", "verde", "azul", "negro" en la columna A2: A7

    luego ponga esto en B2 como una fórmula de matriz y copie abajo =IFERROR(INDEX(A$2:A$7;SMALL(IF(FREQUENCY(MATCH(A$2:A$7;A$2:A$7;0);ROW(INDIRECT("1:"&COUNTA(A$2:A$7))));ROW(INDIRECT("1:"&COUNTA(A$2:A$7)));"");ROW(A1)));"")

    entonces debería verse algo como esto;