Excel - Formulas CONTARA y CONTAR.SI que se modifican internamente

 
Vista:
sin imagen de perfil
Val: 179
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Quico (106 intervenciones) el 09/10/2019 22:57:21
Buenas a todos.
Tengo un problema con unas celdas que tienen un CONTADOR.
Se trata de 2 contadores que están situados en la FILA 7

Una celda tiene la fórmula:=SUMA(1/CONTAR.SI(B8:B100;B8:B100&""))-1
Otra celda tiene la fórmula: =CONTARA(D8:D100)

Lo importante es que debe empezar a realizar su función desde la FILA 8.

¿Y cual es el problema?
Muchas veces tengo que DUPLICAR los datos de la fila 8, para lo que utilizo una macro:

Sub DuplicarFila()

' DuplicarFila Macro
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub
Primeramente selecciono toda la fila 8 y después ejecuto la MACRO

¿Y que me ocurre?
Pues que las fórmulas de contadores se modifican a la fila 9

Ej: =SUMA(1/CONTAR.SI(B9:B100;B9:B100&""))-1
=CONTARA(D9:D100)

Si duplico la fila 9 o la 10, no sucede y mantiene las formulas desde la fila 8.

¿Existe alguna manera de corregir esto?
No se si a la fórmula hay alguna manera de decirle algo del tipo 'Cuenta desde la celda de abajo de esta celda (B7 por ejemplo) para que ocurra lo que ocurra, se dupliquen filas o se eliminen, siempre cuente desde la fila 8.
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Ignacio
Val: 639
Bronce
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Ignacio (205 intervenciones) el 09/10/2019 23:08:12
Hola:

Prueba con la función Indirecto..

Algo así:

1
=CONTARA(INDIRECTO("D8:D100"))


Comenta si te funciona.

Saludos.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
sin imagen de perfil
Val: 179
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Quico (106 intervenciones) el 09/10/2019 23:25:43
Hola Ignacio.

...pues no estoy muy seguro.
Diría que este: =CONTARA(INDIRECTO("D8:D100")) si que hace su función y aunque duplique la fila 8, no modifica el contador a la fila 9.

Sin embargo, si utilizo ese argumento 'INDIRECTO' en las siguientes fórmulas, no las admite o la fórmula se transforma en ###########

=SUMA(1/CONTAR.SI(INDIRECTO(B8:B100;B8:B100&"")))-1 '<--Esta por ejemplo deja de funcionar.

Lo mismo me ocurre con esta otra que cuenta solo si aparece un valor ubicado en la celda AE5.
=CONTAR.SI(A8:A100;AE5)

Si le indico =CONTAR.SI(INDIRECTO(A8:A100;AE5)) tampoco me funciona :-(
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Ignacio
Val: 639
Bronce
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Ignacio (205 intervenciones) el 09/10/2019 23:43:41
En contar.si le estas diciendo que en el rango de A8:A100 cuente cuantas veces aparece el valor que tienes en AE5. Por supuesto que si en AE5 no hay ningún valor no va a contar nada.

Si usas indirecto el rango debe ir entre comillas.

1
=CONTAR.SI(INDIRECTO("A8:A100");AE5)

La otra debería quedar así:

1
=SUMA(1/CONTAR.SI(INDIRECTO("B8:B100");INDIRECTO("B8:B100"&" ")))-1

Saludos.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
sin imagen de perfil
Val: 179
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Quico (106 intervenciones) el 10/10/2019 07:25:24
¡¡¡Pues parece que sí funciona, Ignacio!!! GRACIASSSS

Pero si que tengo un problema en la celda que utiliza:
=SUMA(1/CONTAR.SI(INDIRECTO("B8:B100");INDIRECTO("B8:B100"&" ")))-1


Aquí me muestra el clásico error: #¡DIV/0!
Y aunque he entrado al formato de celda y he modificado el 'tipo', no consigo quitar el error.

He probado con tipo ;;; y con 0;;;@ y 0;-0;;@ pero no lo consigo :-(

¿Que debería hacer?
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Dirk
Val: 455
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Dirk (166 intervenciones) el 10/10/2019 15:14:23
Hola Quico, el error #¡DIV/0! significa que estás dividiendo por 0, no se soluciona cambiando el formato de la celda.... revisa la fórmula CONTAR.SI(INDIRECTO("B8:B100");INDIRECTO("B8:B100"&" ")), si puede dar 0 entonces mete toda la fórmula en un SI.ERROR(), si no puede dar 0 entonces revisa los datos

Salu2
Dirk
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
sin imagen de perfil
Val: 179
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Quico (106 intervenciones) el 10/10/2019 20:57:22
Hola Dirk,
pues la verdad es que no consigo arreglar el error #¡DIV/0!

La función que trato de utilizar es para contar el número de valores numéricos únicos que tenemos en un rango. Osea, que si por ejemplo tengo 100,101,101,102 ; el resultado no es 4 números, sino 3 números (se repite 101).


Si utilizo esta fórmula =SUMA(1/CONTAR.SI(B8:B100;B8:B100&""))-1 y le hago Crtl+Mayuscula+Enter y me queda {=SUMA(1/CONTAR.SI(B8:B100;B8:B100&""))-1} me funciona perfectamente.

El problema está en que a veces he de duplicar la fila 8 por completo y entonces la formula modifica el B8 por B9. Eso hace que el contador no cuente correctamente al obviar la celda de la fila 8.

En este post me indicaron que se podía arreglar añadiendo INDIRECTO, para otro tipo de fórmulas. ej: =CONTAR.SI(INDIRECTO("A8:A150");AE5)

¡¡Y la verdad es que sí funciona para la fórmula =CONTAR.SI.

Sin embargo no consigo hacerlo con la que comento.

He probado variantes como:

{=SUMA(1/CONTAR.SI(INDIRECTO("B8:B150");INDIRECTO("B8:B150"&" ")))-1}

{=SI.ERROR(SUMA(1/CONTAR.SI(INDIRECTO("B8:B150");INDIRECTO("B8:B150"&" "))))-1}

{=SUMAPRODUCTO(1/CONTAR.SI(INDIRECTO("B8:B150");INDIRECTO("B8:B150"&" ")))}


...y en todos los casos sigo con el error #¡DIV/0! y sin contar nada!

¿Alguna idea?
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Ignacio
Val: 639
Bronce
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Ignacio (205 intervenciones) el 10/10/2019 21:07:23
Eso es porque en el rango que le estas dando hay alguna o algunas celdas vacias
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
Imágen de perfil de Ignacio
Val: 639
Bronce
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Ignacio (205 intervenciones) el 10/10/2019 22:19:22
Estás utilizando mal el SI.ERROR...

Inténtalo con así:

1
=SUMA(SI.ERROR(1/CONTAR.SI(INDIRECTO("B8:B150");INDIRECTO("B8:B150"));0))

con CTRL+SHIFT+ENTER

Saludos.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
sin imagen de perfil
Val: 179
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Quico (106 intervenciones) el 10/10/2019 22:55:40
Pues has dado en el clavo!!!
Tal cual con:
=SUMA(SI.ERROR(1/CONTAR.SI(INDIRECTO("B8:B150");INDIRECTO("B8:B150"));0))

Desaparece el error y contabiliza el TOTAL de valores únicos en el rango!!
Muchas gracias. :-)
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Ignacio
Val: 639
Bronce
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Ignacio (205 intervenciones) el 10/10/2019 23:03:21
De nada, hasta la próxima...
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
Imágen de perfil de Dirk
Val: 455
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Dirk (166 intervenciones) el 11/10/2019 17:04:07
Hola Quico, que bueno que pudiste solucionar, sin embargo debo reconocer que me "molesta" que el rango de la fórmula esté fijo, quizás tus datos nunca lleguen a la fila 100 o a la 150 pero el hecho que pueda salirse del rango me incomoda así que hice pruebas convirtiendo el rango en una tabla y efectivamente puedes agregar todas las filas que quieras y la fórmula seguirá funcionando.

La famosa fórmula:
1
{=SUMA(SI.ERROR(1/CONTAR.SI(Tabla13[Columna1];Tabla13[Columna1]);0))}

pero.... siempre hay un pero.... tu macro DuplicarFila no funcionaría ya que Excel no permite copiar una fila e insertar las celdas copiadas en la tabla, lo hice de forma manual y me dio error, la solución a esto pudiera ser copiar la celda de la fila 8 o de la última fila y pegarla a continuación para que la tabla tome esa celda como parte de tabla pero OJO que es la celda, si copias/pegas la fila como insertar celda, la tabla no ajusta su tamaño, lo que me incomoda sería que los datos quedarían en la última fila.... si aun así quieres que los datos queden en la primera fila entonces mi sugerencia sería insertar una fila a la tabla a nivel de la fila 8 y copiar los datos de la fila 9 y pegar en la fila 8 (puedes ser con tu macro y si, lo puedes hacer con las filas completas), esto sí lo permite y los datos quedarían al principio de la tabla para que sean manipulados de forma mas fácil; este método serviría como solución general para contar valores únicos sin considerar el tamaño del rango incluso si insertas columnas a la izquierda o filas arriba de la tabla.

Nota: mi interés surgió por la falta de conocimiento en implementaciones de las formulas matriciales y por supuesto por una buena dosis de obsesión en soluciones a prueba de usuarios con menos conocimientos de excel..... La verdad que este post me ha servido para reforzas mis conocimientos en fórmulas matriciales.

Salu2 a todos,
Dirk
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
sin imagen de perfil
Val: 179
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Formulas CONTARA y CONTAR.SI que se modifican internamente

Publicado por Quico (106 intervenciones) el 14/10/2019 07:32:45
Hola Dirk,
me alegro que este post haya servido a alguien más.

Muchas gracias por vuestra ayuda.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar