8 de abril de 2018

Funcion COINCIDIR + CONTAR.SI para saber si mi dato se repite.



En ocasiones, nececsitaremos 1. Saber si mi dato se repite. 2. Donde está e dato que se repite.
Dada esta necesidad, emplearemos las funciones contar.si y coincidir.

La funcion contar.si, como ya sabemos nos ayuda a contar dependiendo de una condicion dada.
La funcion coincidir, me devolverá una posicion dependiendo de una condicion dada.
COINCIDIR devuelve la posición del valor coincidente dentro de matriz_buscada, no el valor en sí. Por ejemplo, COINCIDIR("b",{"a","b","c"},0) devuelve 2, la posición relativa de "b" dentro de la matriz {"a","b","c"}.



Sintaxis:


COINCIDIR(valor_buscado,matriz_buscada, [tipo_de_coincidencia])

   Valor_buscado    Obligatorio. Es el valor que desea buscar en matriz_buscada.
   Matriz_buscada    Obligatorio. Es el rango de celdas en que se realiza la búsqueda.

CONTAR.SI(matriz_buscada, valor_buscado)

  Valor_buscado    Obligatorio. Es el valor que desea buscar en matriz_buscada.
  Matriz_buscada    Obligatorio. Es el rango de celdas en que se realiza la búsqueda.


Ahora para nuestro ejemplo, tenemos este listado de nombres.

 


Deseamos si se repite, cuantas veces y donde.

Primero usaremos la funcion contar.si, para saber cuantas veces. En la celda B1 escribiremos la formula: =CONTAR.SI($A$1:$A$26;A1) . Copiaremos la formula en toda la columna B


Ya con esta sabemos cuantas veces se repite mi dato.

Ahora para saber donde usaremos la funcion coincidir en la celda C1 escribiremos =COINCIDIR(A1;$A$1:$A$26;0)



 Ahora debemos escribir estas dos funciones en una sola celda, además tendremos que usar la funcion SI, para que cuando se repite me diga donde, y en caso que no me coloque cero.


La funcion completa quedaria asi. =SI(CONTAR.SI($A$1:$A$26;A1);COINCIDIR(A1;$A$1:$A$26;0);0)



Esta fórmula arrojará la fila donde está el valor repetido. Es importante recordar que el valor arrojado es el número de la fila dentro del rango fijado, de modo que si el rango es A2:A26 y el valor se repite en la fila 3, en realidad estará en la fila 4, puesto que el rango inicia en la fila 2.





Si te gustó, síguenos en Twitter  para recibir nuestras actualizaciones y trucos

16 comentarios:

  1. buenas
    alguien me puede ayudar necesito una formula que me ayude a contabilizar cuantas auditorias se realizaron dentro de una rango de horas es decir cuantas auditorias se hicieron de las 08:00 a las 09:00 pero tengo que separar por entidades.
    Ejemplo:
    celda B CeldaE
    anden 1 08:15
    anden 2 08:30
    anden 3 09:02
    anden 4 23:48
    anden 2 08:43

    ResponderBorrar
    Respuestas
    1. Y estas horas que nos describen es la hora de inicio?
      En este ejemplo el resultado seria Anden 2=3 y todas las demas =1 ?¿?

      Borrar
  2. mmmm si mira el resultaro segun el ejemplo seria el siguiente si lo hacemos manual
    en almacen mty se rezalizaron 2 auditorias a las 8 que corresponden al anden 2
    y bien si fuera por fecha seria el siguiente resultado
    el 13/06/2019
    3 auditorias a las 8
    1 auditoria a las 9
    1 auditoria a las 23
    dado que no me interesa mucho los minutos por que son variables mas sin embargo que los pueda contar


    se realizaron

    ResponderBorrar
  3. Hola, pudieran por favor apoyarme: tengo dos hojas de libro de excel, en la primera hoja : categoria y todos los dias de la semana simulando una asistencia; en la otra hoja tengo descripcion(categoria) y este hago llamado de la sumatoria de un día de asistencia y si coincide la categoria que me realice una suma. Pero esto no me es posible con esta formula me manda "0"
    =SI(CONTAR.SI(ListASIS!$D7:$D21;B7);COINCIDIR(B7;ListASIS!$B$7:$B$22;0);0).
    Gracias

    ResponderBorrar
    Respuestas
    1. Debes usar la funcion Sumar.Si.Conjunto = https://ideasdeexcel.blogspot.com/2011/06/funcion-sumarsiconjunto.html

      Borrar
    2. Muchas gracias, en lugar de sumar.si.conjunto; maneje la siguiente funcion =CONTAR.SI.CONJUNTO(List[Lun];"1";List[CATEGORIA];HH.HH!B14).
      Visualmente se adquiere el objetivo.

      Borrar
    3. Me surgió una duda; esta función realiza la coincidencia y la suma entonces... para confirmar.
      Gracias

      Borrar
  4. QUE TAL, QUISERA SABER COMO PUEDO SUMAR UNA COLUMNA EN DONDE TENGO DIFERENTES NOMBRES EL CUAL VARIOS SON REPETITIVOS Y SOLO QUIERO QUE ME SUME UNA VEZ EL NOMBRE, TOMANDO EN CUENTA OTRO CRITERIO DE OTRA COLUMNA. EJEMPLO:
    CELDA A CELDA B
    PATO SUR
    PATO NORTE
    LEON ESTE
    ELEFANT OESTE
    CABALLO SUR
    CABALLO SUR

    EL RESULTADO SERIA QUE EN ZONA SUR TENGO 2 NADA MAS (CABALLO Y PATO)
    GRACIAS

    ResponderBorrar
  5. Tengo una lista con nombres en las filas y cinco nombres de capacitación en las columnas. En cada fila están la participacion de cada uno en las diferentes capacitaciones. Pero necesito dilucidar como cómo hago para saber cuáles personas hacen más de una capacitación

    ResponderBorrar
    Respuestas
    1. Gracias por escribir Felipe, aqui resolvimos tu inquietud. https://ideasdeexcel.blogspot.com/2020/08/funcion-contarsi-ejercicio-practico.html

      Borrar
  6. Yo tengo otra duda: Tengo una lista de personas con nombres y fechas de nacimiento. Lo que quiero hacer es en primer lugar una fórmula para que dado el rango de la columna de fechas más lejana (la persona mayor de la lista) pero que también se fije que nombre tiene esa persona, se fije en el contenido de la celda que está aledaña a la izquierda y despliegue en otra celda ese valor, ese nombre.

    ResponderBorrar
    Respuestas
    1. Hola Alejandro, si quisieras resaltar la celda se podria con formato condicional.
      Para traer el contenido en una celda, se me ocurre con validacion de datos.
      Para traer el mayor en edad, usaria =MIN(B:B)
      Para saber la posicion de esa edad anterior usaria =COINCIDIR(C1;B2:B53;0) en esta C1 es el contenido de la funcion MIN
      y para traer la celda adyacente usamos indice.
      Completa, la formula es =INDICE($A:$A;COINCIDIR(MIN($B:$B);$B:$B;0);)
      Esta funcion, la podrias usar en formato condicional, para desplegar el nombre de la persona con mayor edad.

      Borrar
  7. Me podrían apoyar con una formula que me ayude a buscar un valor en un rango, sin importar que no sea exactamente igual, mientras coincida ese valor dentro del texto. Por ejemplo.
    Si busco el valor BE020070119 y en una celda aparece ese valor escondido como BE02007011956DFGVF, entonces que la formula me indique que ha encontrado ese valor buscado.

    ResponderBorrar
    Respuestas
    1. Buenas tardes, le informo que para esta inquietud la mejor solución es la función Hallar que le permite buscar dentro de un texto una coincidencia de lo buscado y como resultado en cada celda muestra la posición donde se encuentra posicionado el valor buscado.

      Borrar