SUBTOTALES 103, IGNORA HIDDEN ROWS, pero yo necesito IGNORAR COLUMNAS OCULTAS

[patriciomates]

Hola, compañeros, buenas tardes desde España.

Osa traigo una consulta, según la captura que se ve a continuación:

 

Cuando ocultamos una fila del rango A1:A6 nos cuenta solo 5 valores, pero esto no sucede con columnas, como podréis apreciar en la siguiente captura

 

Una solución posible habría sido usar =SI(CELDA("ancho";C:C)<>0;1;0) y una fila (row) de apoyatura (helper) pero en esta versión de office 365 me produce desbordamiento, ¿Cómo puedo evitar ese desbordamiento? La finalidad es CONTAR con SUMAPRODUCTO(C1:H1;$C$2:$H$2), PERO EL DESBORDAMIENTO NO ME PERMITE APOYARME CON LOS 1.

 

No sé si alguien conoce alguna manera os lo agradecería mucho. Gracias

Subtotales de izquierda a derecha.xlsx

[njnsp]

Buenas,

La ayuda de excel indica:

Cita

• La función SUBTOTALES está diseñada para columnas de datos o rangos verticales. No está diseñada para filas de datos ni rangos horizontales. Por ejemplo, cuando desea calcular el subtotal de un rango horizontal mediante un núm_función de 101 o superior, como SUBTOTALES(109,B2:G2), si oculta una columna no afecta al subtotal. Sin embargo, si oculta una fila en un subtotal de un rango vertical, sí afectará al subtotal.

Respecto al desbordamiento, has probado con:

Cita

=SI(@CELDA("ancho";C:C)<>0;1;0)

Saludos

[patriciomates]

Hace 1 hora, njnsp dijo:

Buenas,

La ayuda de excel indica:

Respecto al desbordamiento, has probado con:

Saludos

Hola, si conozco que subtotales funciona en vertical. Solamente quería saber si habría una forma de hacer lo mismo en horizontal con formulación sin tener que recurrir a VBA. Ahora que la @ me quita el desbordamiento puedo apoyarme en una fila helper auxiliar y contar los 1. Aun así, creo que si no encuentro un contar.si ,  si al menos match un valor de un array de valores. voy a tener que hacerlo con VBA, que es de la única manera que se.  Ya que el ejemplo que he puesto es mucho más simple que el archivo que llevará esa función.

 

Te muestro:

Quiero totalizar una serie de valores, pero si hidden false las columnas y muestro todo el año pues que se cuente toda la fila de valores.

Voy a tener que tirar de MACRO:

Yo tenía entendido que todas estas cosas se podían hacer con la nueva función LAMBDA y las nuevas funciones matrices de EXCEL sin tener que recurrir a MACROS, hasta hoy en varias ayudas que he pedido  no he encontrado sugerencia de uso, yo con fórmulas me llevo mal jejeje igualmente muchas gracias.

[Sergio]

Hola, @patriciomates,

Esta es la fórmula que se me ha ocurrido:

=SUMA(BYCOL(C1:H1;LAMBDA(c;ELEGIRCOLS(SI(CELDA("ancho";c)>0;1;0);1))))

Como bien dices, LAMBDA es la ideal para este tipo de cálculo. Al usar la función CELDA, que solo permite evaluar una celda cada vez, hay que usar la función BYCOL para que compruebe todas las celdas de forma individual.

Espero que te sirva.

[patriciomates]

hace 7 horas, Sergio dijo:

Hola, @patriciomates,

Esta es la fórmula que se me ha ocurrido:

=SUMA(BYCOL(C1:H1;LAMBDA(c;ELEGIRCOLS(SI(CELDA("ancho";c)>0;1;0);1))))

Como bien dices, LAMBDA es la ideal para este tipo de cálculo. Al usar la función CELDA, que solo permite evaluar una celda cada vez, hay que usar la función BYCOL para que compruebe todas las celdas de forma individual.

Espero que te sirva.

Me gusta pq elimino la fila helper, voy a investigar en esa línea ... a ver si consigo resultado similar con contar.si (rango; "coincide un array de valores")

[Sergio]

CONTAR.SI es una opción que estuve barajando pero al final no la pude implementar porque el resto de la fórmula devuelve una matriz de números y CONTAR.SI se alimenta de un rango.

¿Qué necesitas exactamente? 

[patriciomates]

Hasta ahora consigo este resultado de la siguiente manera:

Macro:

Private Function CONTAR_LUN_SAB(Rg As Range) As Double

    Dim xCell         As Range
    Dim xRg           As Range
    Dim xOutRg        As Range
    Dim ResultArray() As Variant
    Dim wr            As Worksheet

    Set wr = Sheets("Configuración")
    ResultArray = getAR(wr.ListObjects("R_1_R_6").ListColumns(1).Range)

    On Error Resume Next
    Application.Volatile
    Set xRg = Application.Intersect(Rg, Rg.Worksheet.UsedRange)
    If Not (xRg Is Nothing) Then
        For Each xCell In xRg
            If (xCell.EntireRow.Hidden = False) And _
               (xCell.EntireColumn.Hidden = False) Then
                If xOutRg Is Nothing Then
                    Set xOutRg = xCell
                Else
                    Set xOutRg = Application.Union(xCell, xOutRg)
                End If
            End If
        Next
    End If

With ThisWorkbook

    Dim Keywords()  As Variant
    Dim iVal        As Long
    Dim myRange     As Range
    Dim myCell      As Range
    Dim bCount      As Boolean
    Dim myVal       As Variant

    Keywords = ResultArray

    Set myRange = xOutRg

    For Each myCell In myRange
        bCount = False

        For Each myVal In Keywords
            If InStr(1, myCell, myVal, vbBinaryCompare) And Len(myCell) = Len(myVal) Then bCount = True
        Next myVal

        If bCount Then iVal = iVal + 1
    Next myCell

        Debug.Print iVal
End With

    If Not xOutRg Is Nothing Then
        CONTAR_LUN_SAB = iVal
    Else
        CONTAR_LUN_SAB = 0
    End If

End Function

Public Function getAR(c1 As Range) As Variant
    Dim s As String, arrTemp() As Variant, arr() As Variant, j As Integer, someRange As Range
    Set someRange = c1
    With someRange
        If .Cells.Count = 1 Then
            ReDim arrTemp(1 To 1)
            arrTemp(1) = someRange.value
        ElseIf .Rows.Count = 1 Then
            arrTemp = Application.Transpose(Application.Transpose(someRange.value))
        ElseIf .Columns.Count = 1 Then
            arrTemp = Application.Transpose(someRange.value)
        Else
            MsgBox "someRange is mutil-dimensional"
        End If
        For Each X In arrTemp
            If Not X = "" Then
                ReDim Preserve arr(j)
                arr(j) = X
                j = j + 1
            End If
        Next
    End With
    getAR = arr
End Function

PERO TENGO PROBLEMAS CON EL CALULATION AUTOMATICO

[patriciomates]

hace 1 minuto , Sergio dijo:

CONTAR.SI es una opción que estuve barajando pero al final no la pude implementar porque el resto de la fórmula devuelve una matriz de números y CONTAR.SI se alimenta de un rango.

¿Qué necesitas exactamente? 

A veces algunas functions que hago corren cuando por sí solas cuando no deberían, he gestionado esto desactivando el cálculo automático del libro y calculando cuando yo lo necesito de manera focalizada en una hoja determinada ... me falta experiencia en todo esto debe haber maneras más limpias.

[Sergio]

Patricio, es normal que hayas decidido calcular la hoja manualmente. Si tienes una celda por día del año y cada una tiene su propia fórmula, no me quiero imaginar qué infierno debe ser la espera...

Cuando me solicitan un desarrollo de este tipo, suelo generar un calendario dinámico que solo muestra los días del mes que se seleccionan mediante una barra de desplazamiento. De esa forma se evitan calcular miles de fórmulas. Tienes un sencillo ejemplo muy similar en este enlace:

https://www.contextures.com/excelscrollbarselectdate.html

 

[patriciomates]

hace 40 minutos , Sergio dijo:

Patricio, es normal que hayas decidido calcular la hoja manualmente. Si tienes una celda por día del año y cada una tiene su propia fórmula, no me quiero imaginar qué infierno debe ser la espera...

Cuando me solicitan un desarrollo de este tipo, suelo generar un calendario dinámico que solo muestra los días del mes que se seleccionan mediante una barra de desplazamiento. De esa forma se evitan calcular miles de fórmulas. Tienes un sencillo ejemplo muy similar en este enlace:

https://www.contextures.com/excelscrollbarselectdate.html

 

Me da muy buena vibra la página que me has recomendado, no conocía a Debra, estoy seguro de que voy a pasar buenos ratos estudiando el trabajo de ella. 

Por otra parte, las celdas están sin fórmulas ni formatos condicionales, si escribes una letra en pronóstico se lanza un event que formatea la celda, este pronóstico de traslada a la base de datos una vez ha pasado el día y se confirma la asistencia del trabajador.

Cada celda es independiente el código solo corre en la celda que ha sufrido un cambio. Y está segmentado en trocitos por fila, para tenerlo más separado y controlado.

además, no te permite pronosticar más días de los permitidos por la normativa de tráfico ... tacógrafo etc...

En otros posts de consultas se ven otras partes del archivo.  La gestión de personal, horarios, vacaciones, ausencias, bajas, festivos .... era algo que no había trabajado anteriormente, pero a pesar de que no es cosa fácil he disfrutado mucho haciéndolo, he estudiado mucho esto durante este año, te estoy muy agradecido por el archivo que me facilitaste con el cual inicie esta andadura he aprendido muchísimo de él. Ahora me siento preparado para afrontar la gestion de personal para el año 2023 con mucha seguridad y firmeza.