Macros Excel (III) ¡Un pasito más!

Otra entrega más, la tercera ya, de esta pequeña introducción al mundo de las Macros en Excel®. Hoy quiero avanzar un poquito más, para ello pondré un ejemplo que quizás lo podáis adaptar a vuestro entorno, como ir a comprar pan, tabaco calcular si vuestros interfaces de red, Ethernet, WiFi, WAN, balancean el tráfico de forma correcta.

Ante todo entended que NO es más que una pequeña macro que va embebida en otras más complejas, ¿vale?

Os comento el escenario del ejemplo:

• Partimos de un fichero en formato CSV que, tras importarlo a un libro Excel (XLSX) separamos sus campos en varias columnas, un total de 36 columnas, aunque trabajaremos con tres de estas.
• Creamos una Tabla Dinámica (Pivot Table) para ordenar los campos deseados y, de paso, darle un formato a nuestro gusto.
• La "foto" de la PT es la siguiente:

  

 

En la  captura anterior podéis ver que existen espacios en blanco entre los campos principales, ahí tan sólo podemos aplicar un poco de lógica que explico ahora.

Copiemos en una nueva hoja (sheet) dentro del mismo libro los campos mostrados con sus valores y, para ir avanzando, agregamos nuevas cabeceras en las columnas D,E,F y G:

# Caminos    % Respecto Total    Total KB/sec    Balanceado?

Una vez finalizada nuestra tabla, vamos a calcular algunas cosas, como por ejemplo cuántos Devices Ethernet tiene cada máquina (en el ejemplo es fácil pero podemos tener múltiples campos)
[Para una mejor ejecución, he puesto una marca de "FIN" en la última celda de columna B]

Sub Gimme_N_Devices ()
    Dim Cont As Integer, Pos As Integer, Traffic As Double
    Dim RES As Variant
   
    Traffic = 0
    Cont = 1
   
    For Each x In Selection
        If x.Offset(1, 0) = "" Then
            Cont = Cont + 1
            Traffic = Traffic + x.Offset(0, 1)
        Else
            Pos = Cont * (-1) + 1
            Traffic = Traffic + x.Offset(0, 1)
            x.Offset(Pos, 2) = Cont
            x.Offset(Pos, 4) = Traffic          
            x.Offset(Pos, 5) = Gimme_Medias(x.Column, x.Row, Cont, Traffic)
            Cont = 1
            Traffic = 0
        End If
    Next x
End Sub

Nota: Antes de empezar a ejecutar la macro en si, seleccionar la columna B hasta la marca FIN que comentábamos anteriormente.
 
Comienzo a describir qué hace la función creada ( Gimme_N_Path ):

• Las variables las defino (DIM), por regla general, al principio de la misma.
• Inicializo las variables Traffic y Cont, la primera porque nos servirá como sumatorio de los KB/sec por device y la segunda es un simple contador. Ambas se irán reinicializando al final del bucle.
• Inicio del bucle, para cada elemento X que se encuentre dentro de la selección, los devices ethernet de la columna B.
• Las condiciones IF comprobarán si siguen dentro del mismo ETHx o del siguiente, para ello mirará debajo si la celda está vacía o no X.Offset(1, 0) OFFSET es un desplazamiento desde el punto indicado hacia una coordenada concreta. En nuestro caso, X.Offset (1, 0) le indica que, para la celda X de la selección, mirar hacia 1 celda positiva hacia el eje de ordenadas (y) 0 en el eje de abscisas (x) ¿Me seguís? Quizás a alguno se le haya olvidad cómo mirar los ejes de coordenadas en 2 dimensiones... mal, muy mal.
• SI la celda de abajo está vacía, entonces sumará a la variable Traffic lo que se encuentre a su derecha [para ello usamos Offset(0, 1) esta vez] y Cont sumará 1, para tener en cuenta de que ya hemos pasado por un ETHx.
• SI NO está vacía la celda de abajo,  entra en juego una nueva variable POS que almacena, gracias a CONT, la posición en negativo del ETHx tratado  Pos = Cont * (-1) + 1. Entiendo que os puede resultar algo raro esta utilidad, pero lo veréis para qué ahora. Jugamos de nuevo con Offset para, mediante la variable Pos, decirle dónde colocar los valores de Cont y Traffic, veáse el número de posibles valores o caminos de ETH y el tráfico total :

            x.Offset(Pos, 2) = Cont
            x.Offset(Pos, 4) = Traffic          
            x.Offset(Pos, 5) = Gimme_Medias(x.Column, x.Row, Cont, Traffic)

• Aquí haremos uso de una FUNCIÓN llamada Gimme_Medias en la que le pasaremos los valores de posición de X, es decir de la celda que estamos trabajando, para ello usamos X.ROW y X.COLUMN; además le facilitaremos CONT y TRAFFIC para el cálculo de las medias. [luego explico dicha función]
• Ahora si, reseteamos las variables CONT y TRAFFIC para que se comience de nuevo el Loop con la siguiente X (celda).

 Sigo ahora con la función Gimme_Medias, que nos devuelve SI, NO ó -- (dos guiones).
¿Por qué tiro de esta función? Porque tomando los valores de TRAFFIC y CONT  nos hace la media aritmética, además de compara dicha media con los valores de cada una de las celdas que la forman, véase, calcular el porcentaje % de cada valor respecto a la media, con un márgen de "error" de +/- 5%. ¿Lo pilláis? Es algo más matemático que algorítmico.

Function Gimme_Medias(Colu, Fila, Paths, Totales)
    Dim a As Integer
    Dim RES As Long
    a = 0
    fila_new = Fila - Paths + 1
    Colu_new = Colu + 3
       
    Do While a < Paths
        IO_Count = Cells(fila_new + a, Colu_new - 2)
        If Totales > 0 Then
            RES = (IO_Count * 100) / Totales
            Cells(fila_new + a, Colu_new) = (IO_Count * 100) / Totales
            If (RES <= ((100 / Paths) + 5)) And (RES >= ((100 / Paths) - 5)) Then
               Gimme_Medias = "SI"
            Else
               Gimme_Medias = "NO"
            End If
           
        Else
            Gimme_Medias = "--"
        End If
        a = a + 1
    Loop
  
End Function

  Además, una vez  ejecutada la función, nos devuelve para condición si el valor está balanceado "SI" o "NO" y la otrora condición "--" que viene a ser "Sin tráfico".

Buf, parecía cortito el código pero algunas cosas ejecuta, ¿cierto?

Hasta aquí la tercera entrega, las próximas más código y menos charla.

¡Ah! el resultado final en una imagen:

 

#HALT