** NORMAS DEL FORO **
Inicio del foro Inicio del foro > Otros de Microsoft: Windows y Office > Excel
  Mensajes nuevos Mensajes nuevos RSS - Alternativa a SUMAPRODUCTO condicional
  Preguntas frecuentes Preguntas frecuentes  Buscar en el foro   Eventos   Registro Registro  Iniciar sesion Iniciar sesion

Tema cerradoAlternativa a SUMAPRODUCTO condicional

 Responder Responder
Autor
Mensaje
abcdx Ver desplegable
Habitual
Habitual
Avatar

Unido: 21/Junio/2007
Localización: España
Estado: Sin conexión
Puntos: 130
Enlace directo a este mensaje Tema: Alternativa a SUMAPRODUCTO condicional
    Enviado: 29/Octubre/2018 a las 09:30
Hola, buenos días,

tengo una tabla en la que se registran los informes codificados por persona (cada informe un nuevo registro).

Lo que intento obtener es el promedio de informes codificados por persona cada mes, y me gustaría tenerlo en una tabla dinámica.

No todas las personas trabajan los mismos días cada mes, con lo que se me ocurrió hacer una fórmula condicional con sumaproducto para poner 1 en el primer registro por usuario de cada mes que cambie de día y así poder sumar para poder calcular promedios en la tabla dinámica con un campo calculado (si una persona codifica 7 informes el día 29/10/2018, únicamente el primer registro de esos 7 tendría un 1). Problema: son 33000 registros (y aumentando) y cada vez que quiero actualizar la tabla Excel muere.

La fórmula era: 

=SI(SUMAPRODUCTO((U$2:U2=U2)*(X$2:X2=X2)*(Y$2:Y2=Y2))>1;0;1)

en donde en U estan los usuarios, en X los meses y en Y los días.

¿Hay alguna manera de hacer algo similar pero más eficiente?

Tal vez mi aproximación al problema no sea correcta y haya alguna alternativa más rápida (durante un tiempo utilicé subtotales, pero el jefe quiere poder ir filtrando usuarios y meses, con lo que debería ser en una tabla dinámica que se mostrase usuario, mes y día. En los subtotales de los meses debería poder tener el total de informes codificados y el promedio (en función de los días trabajados)).

Adjunto ejemplo de la tabla y de la tabla dinámica (con el campo calculado) tal y como la tendría ahora:

Usuario
FechaCodificación
MES
DIA
Count
sumaproducto
Etiquetas de fila
Suma de Count
Suma de sumaproducto
Suma de Count/sumaprod
Usuario1
17/10/2018 10:40
10
17
1
1
Usuario1
4
2
2
Usuario1
17/10/2018 11:00
10
17
1
0
oct
4
2
2
Usuario1
19/10/2018 10:40
10
19
1
1
17-oct
2
1
2
Usuario1
19/10/2018 11:00
10
19
1
0
19-oct
2
1
2
Usuario2
17/10/2018 10:40
10
17
1
1
Usuario2
5
3
1,666666667
Usuario2
17/10/2018 11:30
10
17
1
0
oct
5
3
1,666666667
Usuario2
19/10/2018 10:00
10
19
1
1
17-oct
2
1
2
Usuario2
19/10/2018 10:45
10
19
1
0
19-oct
2
1
2
Usuario2
20/10/2018 10:00
10
20
1
1
20-oct
1
1
1
Total general
9
5
1,8



Gracias por la ayuda. 



Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5454
Enlace directo a este mensaje Enviado: 29/Octubre/2018 a las 21:16
 

¿Promedio? Algo me estoy perdiendo.


Entiendo que puedes hallar el promedio de importes, p.e.  ingresos o/y  gastos,  y hallar el promedio de dichos importes, pero en los datos de tu consulta solo veo nombres y fechas. De 'eso' se puede obtener un conteo de registros por usuario, y eso lo hace la TD sin necesidad de fórmulas añadidas.


Por ejemplo con estos datos...




… puedo (o tu jefe) filtrar solo un mes (o varios), que entiendo es el informe que pretendes.


Me temo que debe ser algo diferente  a lo que yo entiendo y tendrás que aclarar los detalles faltantes.




Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
abcdx Ver desplegable
Habitual
Habitual
Avatar

Unido: 21/Junio/2007
Localización: España
Estado: Sin conexión
Puntos: 130
Enlace directo a este mensaje Enviado: 30/Octubre/2018 a las 07:24
Hola, gracias por responder y disculpa, cometí un error al poner la tabla de ejemplo, y es que los usuarios van en columnas y en las filas los meses y días.

El objetivo es obtener, en una sola tabla dinámica, cuántos informes ha codificado un usuario por mes y día (un cuenta, que como tengo el campo Count que es todo unos yo hice un suma) y, además, el promedio diario para cada mes.

La tabla final, para cada mes, quedaría algo así (hay hasta 10 usuarios, de ahí que ahora se vean filas (días) sin datos):

Usuario1
 
Usuario2
 
Usuario3
 
 
N
Media
N
Media
N
Media
ene
 
 
101
17
43
14
22-ene
23-ene
22
22
24-ene
3
3
25-ene
27
27
26-ene
19
19
4
4
28-ene
29-ene
29
29
17
17
30-ene
15
15
31-ene
8
8


El problema es el campo Media, que como en la tabla hay registros únicos no tengo un campo que pueda hacer el promedio y de ahí que utilice el sumaproducto que hace que mi Excel no tire y no pueda actualizar los datos... 
 
He probado también agregando la tabla al modelo de datos para contar distintos, pero no me deja agrupar fechas ni hacer campos o elementos calculados.

Espero haberme explicado algo mejor.

Gracias por la ayuda.
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5454
Enlace directo a este mensaje Enviado: 30/Octubre/2018 a las 11:35

Lo que has hecho ahora es complicarlo más.


Con esa distribución de datos ni siquiera puedes obtener una TD (incluso aunque no resuelva).


Una tabla solamente puede tener una fila de títulos y por tanto (en tu caso) no serían reconocidos los usuarios (p.e.).



Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
abcdx Ver desplegable
Habitual
Habitual
Avatar

Unido: 21/Junio/2007
Localización: España
Estado: Sin conexión
Puntos: 130
Enlace directo a este mensaje Enviado: 30/Octubre/2018 a las 13:02
Disculpa que no lo dejé claro (de nuevo), pero lo que he puesto en el segundo correo es la tabla dinámica final (con un elemento calculado), la distribución de datos es la de la tabla izquierda del primer correo.

Editado por abcdx - 30/Octubre/2018 a las 13:04
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5454
Enlace directo a este mensaje Enviado: 30/Octubre/2018 a las 18:03

Ah vale, eso me resulta más razonable, como leí «La tabla final...» interpreté la tabla con los datos de origen.


Yo hice pruebas para obtener el conteo y la media en la tabla de origen...

Usuario Fecha Dias Media UnionF UnionM
Isidro 17/10/18 10:40 2 2.00 Isidro 17oct Isidro10
Isidro 17/10/18 11:00 Isidro 17oct Isidro10
Isabel 17/10/18 10:40 2 1.67 Isabel 17oct Isabel10
Isabel 17/10/18 11:30
Isabel 17oct Isabel10
Isidro 19/10/18 10:40 2 Isidro 19oct Isidro10
Isidro 19/10/18 11:00 Isidro 19oct Isidro10
Isabel 19/10/18 10:00 2 Isabel 19oct Isabel10
Isabel 19/10/18 10:45 Isabel 19oct Isabel10
Isabel 20/10/18 10:00 1 Isabel 20oct Isabel10
Isabel 7/11/18 10:40 1 1.33 Isabel 7nov Isabel11
Pedro 12/11/18 10:00 1 1.00 Pedro 12nov Pedro11
Isidro 15/11/18 10:40 1 1.00 Isidro 15nov Isidro11
Isidro 17/11/18 11:00 1 Isidro 17nov Isidro11
Isabel 19/11/18 10:40 2 Isabel 19nov Isabel11
Isabel 19/11/18 11:30 Isabel 19nov Isabel11
Pedro 19/11/18 10:45 1 Pedro 19nov Pedro11
Isabel 20/11/18 10:00 1 Isabel 20nov Isabel11
Isidro 22/11/18 11:00 1 Isidro 22nov Isidro11



…  con dos columnas auxiliares: nombre&fecha y nombre&mes (que se pueden ocultar (para que no incordien) y  la tabla ordenada  cronológicamente.


Las fórmulas que utilicé son (ajusté a no mostrar ceros):

Días:         =SI([@UnionF]<>E1;CONTAR.SI([UnionF];[@UnionF]);0)

Media:     =SI(CONTAR.SI($F$1:F1;[@UnionM])=0;PROMEDIO.SI.CONJUNTO([Dias];[Dias];">0";[UnionM];[@UnionM]);"")

Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
abcdx Ver desplegable
Habitual
Habitual
Avatar

Unido: 21/Junio/2007
Localización: España
Estado: Sin conexión
Puntos: 130
Enlace directo a este mensaje Enviado: 31/Octubre/2018 a las 09:58
Gracias, AnSanVal, funciona correctamente si añado la función Entero para el cálculo de días, ya que la fecha la tenía con hora. 

Si bien es lento igual (manejo 33.360 registros y subiendo), con algo de paciencia y sin tocar nada mientras procesa, no se cuelga al actualizar, así que ya he ganado algo. 
Desactivaré cálculo automático de tablas y con eso creo ya estaría.

Se puede cerrar el hilo.
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5454
Enlace directo a este mensaje Enviado: 31/Octubre/2018 a las 15:46
«... funciona correctamente si añado la función Entero para el cálculo de días, ya que la fecha la tenía con hora...»

- ¡Correcto! esa es una opción, yo utilicé:

UnionF:    =[@Usuario]&TEXTO([@Fecha];" dmmm")   <<< cuando los datos contienen varios años, esta no funciona.

UnionM:   =[@Usuario]&MES([@Fecha])


«... es lento igual (manejo 33.360 registros y subiendo)…»

Entiendo, yo no hago las pruebas con tantos registros, pero ten en cuenta que ADEMAS no son 33.360 registros, ya que la tabla dinámica inserta otra copia completa de todos ellos para sus cálculos, y (en algún sitio) he leído que hace otra copia con cada actualización, esto no lo he confirmado, aunque esto último (si es cierto) se podría evitar  (supongo) borrando la TD y creando una nueva.


EDITADO para comentar la primera fórmula. Cierro el hilo.



Editado por AnSanVal - 31/Octubre/2018 a las 15:51
Aprendemos viendo respuestas de otros, también intentando resolver dudas (intenta ayudar cuando puedas/sepas).

Mi sitio_web con ejemplos Excel.
Arriba
 Responder Responder
  Compartir tema   

Ir al foro Permisos de foro Ver desplegable