domingo, 14 de noviembre de 2010

Contar registros únicos sobre dos columnas de datos.

Aplicaremos en esta ocasión una fórmulas matriciales para determinar el número de veces que se repiten (sin duplicados) unos registros en una tabla de datos.
El motivo de este post ha sido la pregunta de un lector:

...Acudo a usted porque no he podido resolver una inquietud y respecto a contar los días pero sin repetirlos para unos torneos efectuados en un mes determinado. Ejemplo:

torneo1 01/07/2010 julio 2010
torneo1 01/07/2010 julio 2010
torneo1 01/07/2010 julio 2010
torneo1 01/07/2010 julio 2010
torneo1 01/07/2010 julio 2010
torneo1 02/07/2010 julio 2010
torneo2 01/07/2010 julio 2010

Según la tabla anterior, para el mes de julio, del año 2010, debo contar los días (sin duplicados) en los que se efectuó el torneo1, que equivale a dos días...


Para resolver esta difícil cuestión emplearemos las funciones matriciales, y al no haber encontrado una forma mejor, usaremos dos columnas auxiliares de cálculo, para llegar a nuestra solución.
Lo primero es plantear nuestra tabla origen:


Nuestra primera columna auxiliar determinará la correcta condición sobre el primer campo 'Torneo', especificado en la celda E10; con la fórmula:
=A2=$E$10 arrastrando hacia abajo para todos los registros de la tabla:


convirtiendo en VERDADERO o FALSO cada uno de los valores de la tabla.
Al construir nuestra segunda columna auxiliar conseguimos dejar sólo los datos del segundo campo 'Fecha', para una vez conseguido, realizar un conteo sobre ésta, con el fin de conocer el número de veces que se repite (sin duplicados) cada uno de los valores. Mediante la fórmula condicional:
=SI(E2*B2=0;"";E2*B2)
aprovechándonos que VERDADERO equivale a 1 y FALSO a 0.


Llegamos a la fase final, vamos a contar cuantos elementos diferentes existen en esta última 'Auxiliar2'; aplicando la siguiente fórmula matricial (ejecutándola con Ctrl+Mayus+Enter) en la celda F10:
{=SUMAPRODUCTO(($F$2:$F$8<>"")/CONTAR.SI($F$2:$F$8;$F$2:$F$8&""))}


¿Pero que estamos haciendo realmente con esta matricial?, analicemósla por partes.
  • En primer lugar veamos el resultado individual para cada registro del numerador de nuestro cociente:
    {=$F$2:$F$8<>""}


    obtenemos dos VERDADEROS, ya que son los únicos elementos de nuestra 'Auxiliar 2' que forzamos anteriormente con valor.

  • en segundo lugar, para nuestro denominador, hemos empleado una función de conteo sobre la columna 'Auxliar2':
    =CONTAR.SI($F$2:$F$8;$F2)


    resultando que cada elemento de la columna 'Auxiliar2' se repite un número de veces.

  • Por último, vemos que ocurriría al realizar la división entre ambos valores, esto es, al dividir numerador entre denominador:
    =G2/H2


    sumando estos valores obtenemos el conteo buscado inicialmente; en nuestro ejemplo, el número de veces que se repite el 'Torneo 2' en días diferentes es dos.

11 comentarios:

  1. David Moralesnoviembre 16, 2010

    Muchísimas gracias, de verdad que agregas un componente importantísimo que le da un gran valor a tu exposición: DIDÁCTICA EN EL APRENDIZAJE.

    Das la impresión de ser una persona organizada y minuciosa.

    Gracias por el detalle de dedicarle tiempo a pensar en esta solución.

    ResponderEliminar
  2. Hola ! Estoy intentado hacer un seguimiento comercial de las visitas comerciales a los clientes.
    A parte de no saber por donde agarrarlo, necesito organizarlo de manera que pueda incluir una columna para porcentajes de la actividad de esas visitas de cada comercial. Pero falló una y otra vez en las dinamicas.
    Me pude alguien arientar en el tema.
    muchas gracias.

    ResponderEliminar
  3. Hola Gorvi,
    sin ver como tienes planteado tu base de datos es dificil dar una solución, pero suponiendo una tabla plana de registros donde cada comercial tiene un importe de ventas, y que tu objetivo es determinar la distribución proporcional de cada comercial sobre el total, entonces deberás aplicar y configurar una tabla dinámica, de manera similar a como se explica en:
    http://excelforo.blogspot.com/2009/07/opciones-de-un-campo-de-tabla-dinamica_28.html
    o en
    http://excelforo.blogspot.com/2009/07/opciones-de-un-campo-de-tabla-dinamica.html
    En tu caso al configurar el campo de 'Suma de Importe' deberás Mostrar valores como % de la columna
    Slds

    ResponderEliminar
  4. Hola, esta buena la opción y sirvió de mucho. Tengo una duda adicional:
    ¿Existe alguna forma de que no solo cuente por celdas con la ayuda de las celdas sino creando function alguno? Gracias :

    ResponderEliminar
    Respuestas
    1. Hola Alexander,
      bien, con un procedimiento function en VBA (creo que es lo que preguntas) también, inicialmente se podría, sería suficente aplicar un proceso parecido a
      http://excelforo.blogspot.com.es/2012/09/aleatorios-sin-repeticion-con-macros-en.html
      es decir, repasar cada registro previamente concatenado para disponer el número de registros únicos; para luego devolver ese conteo de elementos de la Collection con la function creada.
      Slds cordiales

      Eliminar
    2. Exacto :) en VBA. Es muy similar el link que adjuntas. Intento realizar un conteo de registros únicos, considerando solo 3 campos por cada registro. Si solo llegase a ser igual los 3 campos al de algún otro registro o mas de 1, que este solo sea considerado como 1.
      Con el ejemplo me ayudas a obtener un ejemplo casi aproximado. Lo máximo Ismael, Saludos :)

      Eliminar
  5. Seria interesante ver que cuente el Torneo1 con fecha 01/07/2010

    ResponderEliminar
    Respuestas
    1. Hola Ickurd,
      un gusto saludarte, igualmente.
      Para contar el 'torneo1' con fecha '1/7/2010' bastaría aplicar la función CONTAR.SI.CONJUNTO con esas dos condiciones/criterios...
      =CONTAR.SI.CONJUNTO(A:A;"torneo1";B:B;01/07/2010)

      cordiales saludos

      Eliminar
  6. Estimado, te agradezco la publicación que realizaste. Me sirvió de mucho.

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.