martes, 19 de septiembre de 2017

Fórmula Matricial para replicar el operador O.

Un usuario solicitaba ayuda para replicar unos cálculos donde se necesitaba emplear el operador lógico O, i.e., adicionar importes según condiciones:
[...]Tengo que hallar la edad promedio de personas que nacieron en tumbes o puno.
La fórmula que estoy utilizando es :
=+PROMEDIO.SI.CONJUNTO(G21:G91,E21:E91,"TUMBES")
pero esta solo me obtiene la edad promedio de un lugar, lo que necesito es saber que formula tendría que usar para que me salga la edad promedio de las personas que nacieron en tumbes o en puno.[...]

Fórmula Matricial para replicar el operador O.



La fórmula matricial concreta que busca el usuario sería (en la celda H12):
=PROMEDIO(SI((F17:F87="TUMBES")+(F17:F87="PUNO");H17:H87;""))
(no olvides ejecutarla presionando Ctrl+Mayusc+Enter!!!).


Para mostrar la forma de trabajar con el operador O simplificaremos el ejercicio:

Fórmula Matricial para replicar el operador O.



En este ejemplo disponemos de un campo dividido por categoría (a, b,c y d) y unos importes sobre los que operar.
La primera fórmula matricial, en la celda F2 es:
=PROMEDIO(SI((B2:B10="a")+(B2:B10="b");C2:C10;""))
que nos devolverá el promedio de los importes que correspondan exclusivamente a las categoría a y b (celda amarillas).


Otro cálculo matricial frecuente lo vemos en la celda F3:
=SUMA(SI((B2:B10="a")+(B2:B10="b");1;0))
que nos devolverá un conteo de registros, en concreto de aquellos registros que sean a o b.


Por finalizar, un tercer cálculo matricial en al celda F4:
=SUMA(SI((B2:B10="a")+(B2:B10="b");C2:C10;""))
con el que obtenemos el sumatoria de los importes correspondientes a las categorías a o b.


En respuesta a la cuestión planteada por el usuario, no es posible usar directamente PROMEDIO.SI.CONJUNTO, ya que estas funciones emplean internamente el operador lógico Y...

jueves, 14 de septiembre de 2017

VBA. GetDetailsOf o como conseguir propiedades de un fichero

Me preguntaba una lectora por la forma de conseguir la propiedad de 'Dimensiones' de un fichero o imagen..

El asunto a priori no es fácil, pero construyendo una macro podremos recuperar de alguna forma, junto a otras propiedades informativas, dicho dato.


En un módulo estándar incluimos el siguiente procedimiento:

Sub DetallesFichero()
Dim objShell  As Object, objFolder As Object, objFolderItem As Object

Dim strRutaCompleta As String       'para la ruta completa, incluido el nombre del fichero
Dim NombreFichero As String         'solo el nombre del fichero
Dim RutaCarpeta As Variant

Dim strCab As String    'la cabecera del MsgBox
Dim strTxt As String    'para la composicón del texto a devolver en el MsgBox


'Definimos la variable del fichero elegido
'mediante GetOpenFilename
'debe ser una imagen para poder recuperar las Dimenensiones
strRutaCompleta = Application.GetOpenFilename
'si no seleccionamos nada salimos del procedimiento
If strRutaCompleta = "False" Then Exit Sub

On Error GoTo ControlError
'asignamos la Ruta del fichero elegido
RutaCarpeta = Left(strRutaCompleta, Len(strRutaCompleta) - Len(Dir(strRutaCompleta, vbHidden + vbSystem)) - 1)
'y el nombre del fichero
NombreFichero = StrReverse(Left(StrReverse(strRutaCompleta), InStr(StrReverse(strRutaCompleta), "\") - 1))
On Error GoTo 0

'abrimos objeto Shell y definimos la Carpeta
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(RutaCarpeta)

'controlamos la ruta
If (Not objFolder Is Nothing) Then
    'creamos y definimos un objeto como parte de la Carpeta
    '.ParseName convierte en objeto un texto
    Set objFolderItem = objFolder.ParseName(NombreFichero)
    'En caso de que exista...
    If (Not objFolderItem Is Nothing) Then
        '...obtenemos los detalles/propiedades del fichero elegido
        strCab = "Detalles de:= " & NombreFichero
        strTxt = "Carpeta:=" & RutaCarpeta & vbCrLf
        
        'con .GetDetailsOf obtenemos los detalles del fichero
        MsgBox strTxt & objFolder.GetDetailsOf(objFolderItem, -1), vbExclamation, strCab
        'el segundo parametro de .GetDetailsOf devuelve:
        '-1 detalles informativos del elemento.
        '0 nombre del elemento.
        '1 tamaño.
        '2 tipo.
        '3 Fecha y hora última modificación.
        '4 los atributos del elemento.
        
    End If
    
End If

'liberamos memoria
Set objFolderItem = Nothing
Set objFolder = Nothing
Set objShell = Nothing

Exit Sub

ControlError:
'lanzamos mensaje de error en su caso
MsgBox Err.Number & ":" & Err.Description, vbInformation
End Sub



Si probamos la macro y elegimos una imagen cualquiera obtendremos:

VBA. GetDetailsOf o como conseguir propiedades de un fichero



Obteniendo una lista de propiedades asociadas al fichero seleccionado.

martes, 12 de septiembre de 2017

VBA: Ordenar una Array Multidimensional

Veremos en el post de hoy cómo conseguir ordenar, empleando una array en VBA para Excel, un rango de celdas de varias columnas.
Como punto especial veremos que sirve para cualquier dimensión de rango (número de filas y columnas) y que es configurable cuál es el campo o columna prioritaria a la hora de la ordenación.

Veamso el punto de partida y el resultado tras la ejecución de la macro:

VBA: Ordenar una Array Multidimensional



La función definida personalizada que veremos a continuación: 'OrdenarMatrices' emplea el método de burbuja de ordenación, que ya vimos en esta entrada hace algún tiempo; si bien en esta ocasión en modo función.

Y ahora lo importante.. en un módulo estándar incluimos los siguiente procedimientos:

Function OrdenarMatrices(TempArray() As Variant, NumCol As Long, TotalCol As Long)
Dim situacion As Boolean
Dim elto As Long, col As Long
'definimos la dimensión de una matriz temporal
Dim Temp()
x = Val(TotalCol)
'la redimensionamos
ReDim Temp(1 To x)

'añadimos un bucle para pasar por todos los elementos de la matriz
Do
    situacion = True
    'recorremos cada elemento de la matriz de trabajo cargada
    For elto = LBound(TempArray) To UBound(TempArray) - 1
        'con el < ordenamos de menor a mayor o ascendente (con > en sentido descendente)
        If TempArray(elto, NumCol) < TempArray(elto + 1, NumCol) Then
            situacion = False
            'recorremos cada columna de cada elemento
            For col = 1 To TotalCol
                'hacemos un trasvase de datos de la matriz temporal a la cargada
                Temp(col) = TempArray(elto, col)
                TempArray(elto, col) = TempArray(elto + 1, col)
                TempArray(elto + 1, col) = Temp(col)
            Next
        End If
    Next
'salimos del bucle...
Loop While Not situacion

End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Ordenamos()
Dim MiMatriz() As Variant
'cargamos la matriz con datos
MiMatriz = Range("A1:D10")
'ordenamos de menor a mayor según la tercera columna el rango anterior
Call OrdenarMatrices(MiMatriz, 3, UBound(MiMatriz, 2))

'devolvemos la matriz oredenada a la hoja de cálculo
Range("F1:I10") = MiMatriz
    
End Sub

Tras la ejecución de 'Ordenamos' obtenemos el resultado esperado, tal como se veía en la imagen primera....