Excel - Función contar.si

 
Vista:

Función contar.si

Publicado por Jose (5 intervenciones) el 08/11/2019 20:25:33
Hola a todos,

Tengo un libro con diferentes hojas. Una principal donde cargo los datos del nuevo cliente y mediante macro me traslada los datos a una nueva hoja que automáticamente se renombra con el NIF del cliente.

Lo que quiero hacer es en una nueva hoja resumen que tengo preparada, contar la misma celda (M5) de todas las hojas de clientes que tengo creadas (300 hojas) que cumpla determinado criterio, por ejemplo "SI" o "NO" de la celda en cuestión.

He probado a utilizar :

=contar.si(hoja5:hoja300!M5;"SI") pero me da error ya que estoy utilizando los nombres internos de las hojas y no los que se han ido renombrando con el nif de cada cliente.

Hay alguna forma de meter en la fórmula para utilizar el nombre interno de la hoja?
También quisiera introducir en la formula que en lugar de hoja300 utilice hasta la última del libro para que así cuando se creen más hojas no tenga que ir modificando el número de hoja final.

No sé si tendré que hacerlo mediante VBA o hay algún formato en fórmulas que desconozco para hacer mención al nombre interno de las hojas.

Gracias
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 Antoni Masana
Val: 3.874
Plata
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Función contar.si

Publicado por Antoni Masana (1286 intervenciones) el 08/11/2019 20:42:41
Desconozco la razón por la que es necesario crear una hoja por cliente entre otras cosas porque no lo explicas.
Como has podido comprobar es la peor de las estructuras de datos para sacar información.
Para poder este tipo de cálculos debería haber una hoja con todos los datos.

La fórmula seria una cosa así:

1
=CONTAR.SI(Hoja1!C5;"=SI") + CONTAR.SI(Hoja2!C5;"=SI") + CONTAR.SI(Hoja3!C5;"=SI")

o esta:

1
=SI(Hoja1!C5="SI";1;0) + SI(Hoja2!C5="SI";1;0) + SI(Hoja3!C5="SI";1;0)

Pero NO funciona, son 300 hojas y Excel no soporta tantas fórmulas concatenadas.

Soluciones:

Opción 1: (la más correcta) Crear una hoja con los datos de todos los clientes.

Opción 2: Crear una macro

Saludos.
\\//_
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

Función contar.si

Publicado por Jose (5 intervenciones) el 08/11/2019 20:52:22
Y mediante Macro como sería?

Tengo 1 hoja por cada cliente porque cada cliente necesita tener muchas anotaciones que son homologas pero hay otras muy diferentes, cuadros de texto con comentarios, etc. es decir una hoja muy completa por cada cliente, mis anotaciones personales, etc.

Tengo una hoja principal, desde como he comentado introduzco nuevos clientes y ya me crea la nueva hoja del cliente y también una macro para buscar la hoja de un cliente.

Tienen unos encabezados homólogos para todos, que se ubican en las mismas celdas eso sí (materiales que compran, etc) y ahí es lo que quiero tener un resumen de todos los clientes , es decir por ejemplo : contar los clientes que cumplan que compran un determinado material >1500 o "SI" o "NO"

He intentado poner algo así mediante VBA, pero en lugar de Hoja5.Range("M5") no sé como poner para que tome un rango de hojas desde la hoja5 hasta la última del libro.

Sub RefCodenameHoja()
Hoja3.Range("I3").Formula = _
Application.WorksheetFunction.Countif(Hoja5.Range("M5"), "S")
End Sub
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 Antoni Masana
Val: 3.874
Plata
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Función contar.si

Publicado por Antoni Masana (1286 intervenciones) el 08/11/2019 21:20:29
La fórmula para contar seria así:

1
2
3
4
5
6
7
8
9
10
11
12
Sub Contar_Material()
    Dim Total As Integer, a As Integer
    Total = 0
    For a = 1 To Sheets.Count
        If UCase(Sheets(a).Name) <> UCase("Principal") Then
            If Sheets(a).Range("M5") = "SI" Then
                Total = Total + 1
            End If
        End If
    Next
    MsgBox Total
End Sub

Se salta la hoja en la que introduces los datos a la que he llamado Principal. El resultado lo muestra en un MsgBox pero lo puede poner en una celda.

También puedes hacer una función personal para usarla como si fuese del sistema:

1
2
3
4
5
6
7
8
9
10
11
12
Function Fun_Contar_Material(Celda, Valor)
    Dim Total As Integer, a As Integer
    Total = 0
    For a = 1 To Sheets.Count
        If UCase(Sheets(a).Name) <> UCase("Principal") Then
            If Sheets(a).Range(Celda) = Valor Then
                Total = Total + 1
            End If
        End If
    Next
    Fun_Contar_Material = Total
End Function

La llamada en la celda seria asi:

1
=Fun_Contar_Material("M5";"SI")

Saludos.
\\//_
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

Función contar.si

Publicado por Jose (5 intervenciones) el 08/11/2019 22:03:13
Cuando se utiliza Sheets(a)

con (a) se hace referencia a todas las hojas del libro? porque me da error al ejecutar en ese punto, indica subdince fuera.....

Como puedo hacer referencia a todas las hojas existentes del libro ? para así cuando se añadan más hojas no tenga que estar modificando la macro?

con sheets(array( )) entiendo que puedo hacerlo pero no sé como dar la instrucción para indicar todas las hojas del libro sin identificarlas, así me olvido.

No tengo problema ya que la celda M5 únicamente la tengo en las hojas que quiero contar por lo que en las 4 hojas iniciales en esa celda no hay nada por lo que no me importa que la tome también en cuenta.

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

Función contar.si

Publicado por Jose (5 intervenciones) el 08/11/2019 22:26:07
Ya está, ya lo tengo solucionado .

Mil gracias por tu 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

Función contar.si

Publicado por jose (5 intervenciones) el 09/11/2019 10:17:10
Gracias por lo de ayer.

Otra cosa si en la misma macro, quisiera ponerle que hiciera lo mismo con otras celdas de todas las hojas como lo puedo poner?

Es decir tengo que llevar el contaje de 13 celdas de todas las hojas a 13 celdas de la hoja resumen.

Tengo otra celda que me tiene que sumar , como puedo ponerlo ?

Gracias

He probado la que es función en lugar de ir con macros, funciona pero si cambio el valor de alguna celda no lo cambia automáticamente, tengo que ir a la celda de la formula y volver a darle intro.
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 Antoni Masana
Val: 3.874
Plata
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Función contar.si

Publicado por Antoni Masana (1286 intervenciones) el 09/11/2019 13:33:23
Cuando pones la función en la celda donde quieres el resultado indicas la referencia de la celda y el valor a buscar, en este caso un string.

Es decir esta función cuenta el número de veces que hay un determinado texto en una determinada celda en todo el libro (todas las hojas)
Para cambiar de celda y de texto solo hace falta cambiar los parámetros:

1
=Fun_Contar_Material("A9";"PATATA")

Con estos parámetros cuenta en cuantas hojas sale la palabra PATATA en la celda A9, no distingue entre mayúsculas y minúsculas.
Para contar celdas con números y para sumar hay que hacer algunos cambios.

Saludos.
\\//_
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 wordexperto.com
Val: 5.779
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Función contar.si

Publicado por wordexperto.com (1977 intervenciones) el 09/11/2019 16:21:07
Hola a todos:
Ya te lo ha dicho Antoni. Esto que haces es un disparate:
Tengo 1 hoja por cada cliente porque cada cliente necesita tener muchas anotaciones que son homologas pero hay otras muy diferentes, cuadros de texto con comentarios, etc. es decir una hoja muy completa por cada cliente, mis anotaciones personales, etc.
Usa una estructura de base de datos. Si son muchos los detalles en varias tablas relacionadas con muchas columnas, las que necesites, para cada detalle, a ser posible agrupadas. Como lo haces es un disparate, difícil de mantener y cada vez te dará más problemas y más complejos.
En Excel, como en la vida, lo primero y más importante es la estructura.
Insisto, no sigas con esto. Si quieres ir de Madrid a Cartagena, antes de llegar a Aranda de Duero, lo que debes hacer es dar la vuelta inmediatamente y cambiar de dirección.
https://www.youtube.com/channel/UCxgRcbeR2q4PpE1Pgft74Iw
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