Contar registros unicos y repetidos según fecha

Solo consultas sobre Funciones y Fórmulas Excel.
Reglas del Foro
1. Antes de hacer tu pregunta intenta con el buscador de este foro (muchas preguntas ya fueron respondidas antes!)
2. Si haces una nueva pregunta, es muy recomendable que adjuntes el ejemplo Excel para poder comprenderla mejor!
3. Realiza tu pregunta de forma clara, explicando bien cada paso de lo que haces y tendrás más probabilidad de respuesta!
Responder
ivan26
Miembro Frecuente
Miembro Frecuente
Mensajes: 105
Registrado: 15 Oct 2016 21:15

Contar registros unicos y repetidos según fecha

Mensaje por ivan26 » 01 Nov 2019 16:41

Hola, estoy intentando contar los clientes nuevos y recurrentes por mes.

Estoy usando la formula SUMAPRODUCTO y CONTAR.SI. Logro contar el número total de clientes nuevo y recurrentes de toda mi base de datos; pero no puedo hacerlo por mes.

Así mismo y en otra tabla además de contar por mes, intento agregar una condición que tenga en cuenta el número de código asignado a ese registro.

Adjunto mi archivo

Espero puedas ayudarme.

Muchas gracias.

Iván
No tienes los permisos requeridos para ver los archivos adjuntos a este mensaje.



Avatar de Usuario
Víctor
Miembro Frecuente
Miembro Frecuente
Mensajes: 1374
Registrado: 25 Dic 2014 07:12
Ubicación: Barcelona (España)

Re: Contar registros unicos y repetidos según fecha

Mensaje por Víctor » 01 Nov 2019 21:08

Hola, ivan26.

Antes de seguir, tengo una pregunta:

Supongamos que sólo tenemos 1 cliente que, por ejemplo, aparece una sola vez en los meses de enero, febrero y marzo.

Entiendo que el cuadro quedaría así:

Mes..........Recurrentes.........Nuevos.......Total
Enero........... 0 .................... 1 ........... 1
Febrero......... 0 .................... 1 ........... 1
Marzo........... 0 .................... 1 ........... 1
.........
..........
.....
TOTAL AÑO.... 2 .................... 1 ............ 3

En Total año Nuevos tienes una fórmula que calcula los registros únicos (que en este caso, lógicamente es 1, ya que se trata del mismo y único cliente). En Total año Recurrentes tu fórmula calcula la diferencia entre el total de registros y el total nuevos, o sea (3 - 1 = 2).

¿Te das cuenta de que el total año, en algunos casos, puede no corresponder a la suma de los meses?

¿Quieres modificar el planteamiento de tu pregunta?

Saludos,



ivan26
Miembro Frecuente
Miembro Frecuente
Mensajes: 105
Registrado: 15 Oct 2016 21:15

Re: Contar registros unicos y repetidos según fecha

Mensaje por ivan26 » 01 Nov 2019 21:23

Muchas gracias por responder Victor

Siguiendo con el ejemplo, quisiera que el cliente solo se contabilice como "nuevo" en el mes de enero; en el mes de febrero y marzo aparecería como cliente recurrente.



Avatar de Usuario
Víctor
Miembro Frecuente
Miembro Frecuente
Mensajes: 1374
Registrado: 25 Dic 2014 07:12
Ubicación: Barcelona (España)

Re: Contar registros unicos y repetidos según fecha

Mensaje por Víctor » 02 Nov 2019 08:07

Hola de nuevo,

Aclarado el tema, ya que pensaba que creías evaluar cada mes por separado, sin tener en cuenta las repeticiones en otros meses. En este sentido, quizá sea más adecuado modificar los textos "Enero, Febrero, Marzo,........" por "Enero, Acumulado enero-febrero, Acumulado enero-marzo,..." etc. etc.

Bien, yendo al tema, prueba con esta fórmula MATRICIAL en I5 (clientes nuevos):

{=SUMA(SI(FRECUENCIA(SI(MES(E$5:E$30)<=FILA(W1);B$5:B$30);B$5:B$30);1))}

Esta otra en J5 (totales):

=SUMAPRODUCTO(--(MES(E$5:E$30)<=FILA(W1)))

Esta en N5, también MATRICIAL (nuevos cta 23):

{=CONTAR(1/FRECUENCIA(SI(C$5:C$30=23;SI(MES(E$5:E$30)<=FILA(W1);COINCIDIR(B$5:B$30;B$5:B$30;)));FILA(W$5:W$30)-FILA(DESREF(W$5:W$30;;;1;))+1))}

Y esta última en O5 (total cta 23):

=SUMAPRODUCTO((C$5:C$30=23)*(1*(MES(E$5:E$30)<=FILA(W1))))

Ambas 4 fórmulas puedes copiarlas verticalmente hasta diciembre. La fórmulas para obtener los 'Recurrentes' puedes obtenerlas por simple diferencia entre 'Totales' y 'Nuevos'.

Y ya puestos, también podrías cambiar la fórmula que tienes en I17 por esta más corta:

=SUMAPRODUCTO(--(FRECUENCIA(B5:B30;B5:B30)>0))

aunque, de hecho, la fila de 'TOTALES' la puedes suprimir, ya que según este esquema de trabajo, será el mismo dato que en Diciembre (basta con replicarlo, si es que quieres que aparezca).

Nota: Mi separador de argumentos es ";"
Si no te funcionan las fórmulas, cambia los ; por ,

Saludos,



ivan26
Miembro Frecuente
Miembro Frecuente
Mensajes: 105
Registrado: 15 Oct 2016 21:15

Re: Contar registros unicos y repetidos según fecha

Mensaje por ivan26 » 03 Nov 2019 12:34

Victor muchas gracias.

Creo que no me he explicado bien discúlpame.

Pongo el caso nuevamente:

Supongamos que el único cliente ha sido atendido 1 vez al mes y fueron en los meses de ene, feb y mar. La primera vez que se atendió fue en enero. Entonces va a aparecer como cliente nuevo solo en enero y en los meses de febrero y marzo como cliente recurrente; en la fila de cada mes el total será siempre 1.

Meses | Recurrente | Nuevo = Total
Enero | 0 | 1 = 1
Febero | 1 | 0 = 1
Marzo | 1 | 0 = 1
Total |2 | 1 = 3

Es decir no acumula; sino que cuenta los clientes reales de cada mes

Iván



Avatar de Usuario
Víctor
Miembro Frecuente
Miembro Frecuente
Mensajes: 1374
Registrado: 25 Dic 2014 07:12
Ubicación: Barcelona (España)

Re: Contar registros unicos y repetidos según fecha

Mensaje por Víctor » 03 Nov 2019 20:28

Para ese nuevo caso, lo que puedes hacer en la columna I (nuevos) es modificar la fórmula a partir de febrero, de forma que reste el dato del mes anterior. Así:

{=SUMA(SI(FRECUENCIA(SI(MES(E$5:E$30)<=FILA(W2);B$5:B$30);B$5:B$30);1))-SUMA(SI(FRECUENCIA(SI(MES(E$5:E$30)<=FILA(W1);B$5:B$30);B$5:B$30);1))}

Las columnas 'Recurrentes' y 'Total' no necesitan modificación.

Saludos,



ivan26
Miembro Frecuente
Miembro Frecuente
Mensajes: 105
Registrado: 15 Oct 2016 21:15

Re: Contar registros unicos y repetidos según fecha

Mensaje por ivan26 » 06 Nov 2019 01:01

Muchisimas gracias Victor, es lo que necesitaba.

No quisiera molestar mucho, pero en el caso del segundo cuadro donde toma en cuenta la "cta 23" quise hacer la restar el dato del mes anterior pero no funciona.

¿Podrías por favor hacerme un breve comentario del por qué usas la referencia W1?



Avatar de Usuario
Víctor
Miembro Frecuente
Miembro Frecuente
Mensajes: 1374
Registrado: 25 Dic 2014 07:12
Ubicación: Barcelona (España)

Re: Contar registros unicos y repetidos según fecha

Mensaje por Víctor » 06 Nov 2019 07:36

ivan26 escribió:
06 Nov 2019 01:01
.......quise hacer la restar el dato del mes anterior pero no funciona........
Pues no sé por qué, a mí me funciona sin problemas... en fin, para no alargar más el tema, te paso el archivo.

ivan26 escribió:
06 Nov 2019 01:01
breve comentario del por qué usas la referencia W1
Es una convención para, al copiar en vertical hacia abajo, aumentar automáticamente 1 unidad. Es decir, si en cualquier celda colocas =W1 obtendrás como respuesta 1, y si copias la fórmula hacia abajo, irá aumentando 1 unidad cada fila: de esta forma asociamos 1 a enero, 2 a febrero,... etc. etc. para conseguir el objetivo (mensual) que buscamos obtener.

El hecho de utilizar la W es indiferente; es lo mismo utilizar =W1 que =A1, =H1, =AZ1, o cualquier otro caracter alfanumérico, siempre que no excedamos =XFD1, que es la última columna que tiene Excel en la última versión.

Saludos,
No tienes los permisos requeridos para ver los archivos adjuntos a este mensaje.



ivan26
Miembro Frecuente
Miembro Frecuente
Mensajes: 105
Registrado: 15 Oct 2016 21:15

Re: Contar registros unicos y repetidos según fecha

Mensaje por ivan26 » 15 Nov 2019 02:41

Agradezco mucho su ayuda.

Iván



Dakapami
Miembro Nuevo
Miembro Nuevo
Mensajes: 1
Registrado: 20 Ene 2020 11:02

Re: Contar registros unicos y repetidos según fecha

Mensaje por Dakapami » 20 Ene 2020 12:22

Muy buenas Victor, en primer lugar, agradecerte la ayuda hacia la comunidad. He visto la respuesta que le das a ivan26 ya que en mi caso necesito algo parecido, pero al descargar el archivo no entiendo muy bien las referencias a la columna W ya que está vacía. En cualquier caso, creo que lo mejor es que suba yo un archivo de muestra ya que te será mucho más fácil entender mi caso.
Antes de entrar a explicarlo decir que he tratado de buscar previamente por el foro por si encontrara algo similar pero lo más parecido que he visto, ha sido el caso de ivan26. Si existiera ya mi respuesta, pido disculpas de antemano.
Al lio:
Dispongo de 3 columnas. CLIENTE, FECHA ACTUACIÓN y PROVINCIA.
Necesito extraer cuantas actuaciones se han realizado por cliente y provincia. Para ello tengo que extraer el número de fechas únicas (ésta sería la condición implícita) por cliente y provincia. (es imprescindible que existan las 2 condiciones explicitas simultáneamente, ya que puede darse el caso que se produzcan actuaciones para el mismo cliente el mismo día en distintas provincias).
Adjunto archivo de muestra para una mejor comprensión.
Como puedes ver en la celda D3 he conseguido extraer el número de fechas únicas usando una sola condicional explicita, la provincia, con una formula matricial (CTRL+SHIFT para ejecutarla). En la celda D6 lo mismo, pero por cliente. Pero no he sido capaz de sacarlo teniendo en cuenta las 2 condiciones explicitas simultáneamente, en este ejemplo seria en la celda D9.
Ruego me aporte algo de luz en este reto.
Muchas gracias por adelantado y saludos,
Miguel
No tienes los permisos requeridos para ver los archivos adjuntos a este mensaje.



Avatar de Usuario
Víctor
Miembro Frecuente
Miembro Frecuente
Mensajes: 1374
Registrado: 25 Dic 2014 07:12
Ubicación: Barcelona (España)

Re: Contar registros unicos y repetidos según fecha

Mensaje por Víctor » 20 Ene 2020 13:46

Hola, dakapami.

Prueba con esta fórmula en D9:

=SUMAPRODUCTO(((F2:F518=B9)*(I2:I518=C9)*(H2:H518<>""))/CONTAR.SI(H2:H518;H2:H518&""))

Nota: Mi separador de argumentos es ";"
Si no te funciona la fórmula, cambia los ; por ,

Saludos,



Responder