Cálculo manual de un percentil con Excel.

Hablábamos el otro día en un grupo de compañeros sobre el proceso de cálculo que realiza Excel para el cálculo de los percentiles y cómo devuelve un valor la función PERCENTIL o alguna de sus variantes, por ejemplo =PERCENTIL.INC(...)

Así tiramos de Internet y concluimos que no debía ser muy distinto al proceso manual de cálculo... que expongo a continuación.

Partiremos de un rango de diez valores:

Obviamente obtener el percentil 50% y 80% es muy sencillo empleando las funciones de la imagen (celdas E2 y E4):
=PERCENTIL.INC($B$2:$B$11;D2)
que retorna el valor 57,50
y también
=PERCENTIL.INC($B$2:$B$11;D4)
que retorna el valor 96,00.

Pero, y el cálculo manual, ¿cómo sería?.
El inicio es construir una tabla de frecuencias y frecuencias acumuladas a partir de nuestro rango origen en B2:B11:

Para el cálculo de la frecuencia bastaría emplear una función CONTAR.SI, y para la frecuencia acumulada una SUMA incremental.

Para llegar al valor del percentil emplearemos la siguiente función:

Veamos el proceso de cálculo para el percentil 50% de esos 10 valores.

haz clic en la imagen

Tenemos ciertas variables a calcular:
N (número de valores):= 10
k (porcentaje de casos del percentil):= 50
kN/100:= 50x10/100=5

este valor cinco es el que buscaremos en la columna de frecuencia acumulada, al comprobar que la frecuencia del dato es mayor que uno, aplicaremos un promedio entre ese dato y el siguiente (entre 50 y 65), esto es:
Li (Límite inferior de la puntuación donde se haya el percentil):= =PROMEDIO(A17:A18) = 57,50
fi (frecuencia de la puntuación donde se haya el percentil):= 1
fa (frecuencia acumulada hasta el límite inferior de la puntuación donde se encuentre el percentil):= 5
según aparece en la imagen anterior...

Finalmente el cálculo de percentil al 50% lo incluimos en la celda F19:
=F14+(1/F15*F18-F17)
= 57,50
que coincide, no puede ser de otra forma, con la función
=PERCENTIL.INC($B$2:$B$11;D2)

Si repetimos el cálculo para el percentil 80% sobre los mismos 10 valores:

haz clic en la imagen

Tenemos ciertas variables a calcular:
N (número de valores):= 10
k (porcentaje de casos del percentil):= 80
kN/100:= 80x10/100=8

este valor cinco es el que buscaremos en la columna de frecuencia acumulada, al comprobar que la frecuencia del dato es uno, nos quedaremos con ese valor, esto es:
Li (Límite inferior de la puntuación donde se haya el percentil):= 95
fi (frecuencia de la puntuación donde se haya el percentil):= 1
fa (frecuencia acumulada hasta el límite inferior de la puntuación donde se encuentre el percentil):= 7
según aparece en la imagen anterior...

Finalmente el cálculo de percentil al 80% lo incluimos en la celda F30:
=F25+(1/F26*F29-F28)
= 96,00
que coincide, no puede ser de otra forma, con la función
=PERCENTIL.INC($B$2:$B$11;D4)

Seguir leyendo...

Promedios con SUBTOTALES sin incluir ceros.

Un caso bastante frecuente es querer calcular el promedio de un rango de valores SIN incluir en el cálculo las celdas vacías o con valor cero (ver ejemplo).
En este ejemplo de hoy, además añadiremos un nuevo condicionante, queremos obtener ese promedio sin ceros al aplicar un filtro usando la función SUBTOTALES.

Comenzamos a trabajar desde nuestro rango de datos con un autofiltro incorporado:

Para el correcto cálculo insertaremos en la celda B26 la siguiente fórmula matricial:
=PROMEDIO(SI(SUBTOTALES(102;DESREF(B3;FILA(B3:B24)-FILA(B3);;1));SI(B3:B24;B3:B24)))
recordemos validarla presionando Ctrl+mayusc+Enter!!!

Pero ¿cuál es la explicación?.
La clave está en el uso de la función SUBTOTALES con su argumento 102 para forzar un cálculo de CONTAR valores numéricos:
SUBTOTALES(102;DESREF(B3;FILA(B3:B24)-FILA(B3);;1))
que nos devuelve, según los valores filtrado un rango virtual de 0 y 1, como podemos ver en la imagen siguiente:

La causa de estos ceros y unos es su visibilidad.. y es que la función SUBTOTALES devuelve únicamente valor (uno) cuando el dato es visible!!, y cero cuando está oculto.
Necesario para distinguir qué datos están a la vista para operar sobre ellos.

Al incluir esta sucesión de ceros y unos en el condicional SI estamos discriminando todos aquellos valores que han quedado fuera del filtro.
=SI(SUBTOTALES(102;DESREF(B3;FILA(B3:B24)-FILA(B3);;1));SI(B3:B24;B3:B24))
Además de esto, con el argumento de verdadero del condicional:
SI(B3:B24;B3:B24)
discriminamos todos aquellos importes que sean cero:

El resto es sencillo, ya que aplicamos la función PROMEDIO sobre los datos restantes... que no son otros que lo importes a la vista distintos de cero después de aplicar el filtro.

Seguir leyendo...

VBA: Eliminar columnas específicas con macros en Excel.

Veremos un caso interesante propuesto por un lector, y es que se plantea la posibilidad de eliminar fácilmente una serie de columnas concretas dentro de un rango especificado.

Por ejemplo, partamos del siguiente modelo de 11 columnas (A:F), donde únicamente queremos eliminar las columnas: A, D y F.

Sabemos lo complejo que suele ser trabajar e identificar columnas, ya que éstas aparecen nombradas con letras, y normalmente los bucles se realizan con valores numéricos.
En la explicación siguiente aprovecharemos las ventajas de las matrices (Array) para salvar esta dificultad, y homgeneizar los valores a comparar.

Insertamos el siguiente código dentro de la ventana de código de un módulo estándar desde el editor de VB:

Sub ElimnarColumnas()
'Columnas a eliminar
matriz = Array("A", "D", "F")
nUltCol = Range("A1:K1").Columns.Count

'recorremos de izquierda a derecha las columnas del rango
For i = (nUltCol - 1) To 0 Step -1
    'comparamos cada columna del rango
    'con la matrriz de columnas a eliminar
    For x = 0 To UBound(matriz)
        'identificamos el número de columna a eliminar
        NumCol = Cells(1, matriz(x)).Column
        'y comparamos con la posición de columna en que estamos..
        If i = NumCol Then
            'si coincide, estamos posicionados en una columna a eliminar
            ActiveSheet.Columns(i).Delete Shift:=xlShiftToLeft
        End If
    Next x
Next i

End Sub

El proceso es simple, identificamos qué columnas deseamos eliminar con la matriz de constantes:
matriz = Array("A", "D", "F")
y luego realizamos un proceso de loop de izquierda a derecha entre las columnas del rango a tratar, para evitar errores en la eliminación. en ese recorrido comparamos si la columna coincide con alguna de las indicadas a eliminar.

El proceso acaba con el resultado esperado, con las columnas A, D y F eliminadas:

Seguir leyendo...

Reparto aleatorio de dos días de descanso a la semana.

Un lector preguntaba si habría forma de conseguir un reparto aleatorio de dos días por semana mediante macros o fórmulas...
Obviamente la respuesta es afirmativa. En esta ocasión montaré un conjunto de fórmulas empleando la función ALEATORIO.ENTRE, SI y SUMA.

haz clic en la imagen

Tendremos tres grupos de fórmulas distintas.
Primer grupo para el rango B3:B6 y rango C3:C6 donde incluiremos la misma fórmula:
=ELEGIR(ALEATORIO.ENTRE(1;2);"descanso";"trabajo")
donde realizamos un lanzamiento aleatorio entre 1 y 2 para poder seleccionar entre 'descanso' o 'trabajo'

Segundo grupo para el rango D3:D6,rango E3:E6 y rango F3:F6 donde incluiremos la misma fórmula:
=SI(CONTAR.SI($B3:C3;"descanso")>=2;"trabajo";ELEGIR(ALEATORIO.ENTRE(1;2);"descanso";"trabajo"))
en este grupo incluimos un condicional
SI(CONTAR.SI($B3:C3;"descanso")>=2;"trabajo";....
que controla el caso en que ya hayan aparecido dos 'descansos', en tal caso, indicamos día de 'trabajo'.

Tercer grupo para el rango G3:G6 y rango H3:H6 donde incluiremos la misma fórmula:
=SI(CONTAR.SI($B3:F3;"descanso")<2;"descanso";SI(CONTAR.SI($B$3:F3;"descanso")≥2;"trabajo";ELEGIR(ALEATORIO.ENTRE(1;2);"descanso";"trabajo")))
donde incluimos un segundo condicional
=SI(CONTAR.SI($B3:F3;"descanso")<2;"descanso";
con el que controlamos que no aparezcan menos de dos días de descanso!!!.

Algo sencillo y práctico empleando funciones habituales...

Seguir leyendo...

VBA: Macro de un Filtro Avanzado condicionado según celda validada.

Recientemente me han preguntado varias veces sobre la misma cuestión:
¿Cómo realizar filtros avanzados tomando rangos de criterios variables, elegidos según una celda validada?

Y esto es precisamente, mediante una sencilla macro, lo que vamos a realizar hoy.
Partimos del siguiente planteamiento:

Importante el rango de trabajo, la 'base de datos' está en A1:D21.
Los rangos de criterios opcionales, a los que hemos asignado Nombres Definidos son:
Filtro_1 =Hoja1!$I$2:$J$4
Filtro_2 =Hoja1!$I$8:$I$11
Filtro_3 =Hoja1!$I$15:$K$16

Y en la celda F1 de la hoja hemos creado una Celda validada tipo Lista, la cual nos despliega tres opciones: Filtro_1;Filtro_2;Filtro_3, siendo éstos, precisamente, los Nombres definidos asociados a los rangos de criterios a aplicar a nuestra base de datos.

La idea, por tanto, es clara. Queremos que según seleccionemos algún valor en la celda F1 se aplique el filtro correspondiente.
Un dato importante para el tercero de los criterios, en el que hemos incluido un filtro de fechas, es que las he construido de la siguiente forma:
=">="&FECHA(2016;1;1)
="<="&FECHA(2016;3;31) el motivo es por que de otra forma la programación no las entendería.

Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de la ventana de código de la hoja de trabajo desde el editor de VB; empleando el evento _Change (que nos asegura la ejecución del procedimiento al variar el dato de la celda F1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
    'La celda activa SÍ cruza con las celdas del rango
    'eliminamos cualquier filtro existente en el rango filtrado
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
    'recuperamos lo escrito en la celda F1
    'para luego tratarlo como nombre definido
    NombreDefinido = "" & Range("F1").Value & ""
    'aplicamos el filtro avanzado... con el rango de criterios
    'el Nombre seleccionado en F1
    Range("A1:D21").AdvancedFilter Action:=xlFilterInPlace, _
            CriteriaRange:=Hoja1.Range(NombreDefinido), _
            Unique:=False
End If
End Sub

Podemos probar variando el dato de F1, y comprobaremos cómo se aplica el filtro correspondiente...

Seguir leyendo...

VBA: Un ejemplo del método Worksheets.EVALUATE.

En otro foro que atiendo se planteaba la siguiente cuestión:

Si A1 tiene el texto "2^3+1" o "16/2-(1/3)^-1" quiero que B1 efectúe las operaciones y dar como resultado 9 o 5

La respuesta más simple y directa es emplear el método Worksheets.EVALUATE, el cual convierte un nombre de Microsoft Excel en un objeto o un valor.
Qué debemos saber de este método:
-Podemos usar cualquier referencia a una sola celda en la notación de estilo A1. Todas las referencias se consideran absolutas.
-Con las referencias podremos usar los operadores de rango, intersección y unión (dos puntos, espacio y punto y coma, respectivamente).
-Es posible especificar cualquier nombre en el lenguaje de la macro.
-También podemos usar el operador ! para hacer referencia a una celda o un nombre definido en otro libro. Por ejemplo, Evaluate("[Libro1.xlsx]Hoja1!A1").

Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de la ventana de código de un módulo estándar desde el editor de VB:

Function CalculaTexto(celda As Range) As Variant
CalculaTexto = ActiveSheet.Evaluate(celda.Value)
End Function

Finalmente bastará escribir la función UDF 'CalculaTexto' en cualquier celda de la hoja, indicando la celda de referencia.
El resultado:

Seguir leyendo...

VBA: Elementos filtrados en un UserForm.

Veremos en esta ocasión un clásico, cómo obtener dentro de un ListBox, los elementos de una lista de nuestra hoja decálculo, que cumplan una condición.

Partiremos de nuestras lista en la hoja de cálculo:

Diseñamos un Formulario con los siguientes controles importantes:
1- Un TextBox: llamado 'txtFiltro1'
2- Un ListBox: llamado 'ListFiltrados'. Con una propiedad especial:.ListStyle= 1-fmListStyleOption (conseguiremos un efecto diferente, con un botón de selección en el ListBox para cada elemento).
3- Un botón de Cerrar (CommandButtom): llamado 'CmdCerrar'
4- unas etiquetas descriptivas...

El aspecto sería:

El objetivo consiste en rellenar el TextBox con un Año, y tras salir (evento _Exit) de ese control, completar el ListBox con los registros que coincidan con el año indicado...
Adicionalmente, al seleccionar uno de esos elementos filtrados en el ListBox se marcarán en la hoja de cálculo.

Así añadiremos nuestro código en la ventana del Formulario:

Private Sub UserForm_Initialize()
'Rellenamos las Etiquetas con las celdas A1:A4
For i = 1 To 4
    Me.Controls("Label" & i) = Cells(1, i).Value
Next i

'configuramos el ListBox para cuatro columnas
'y un mismo ancho por columna de 60 puntos
With ListFiltrados
    .ColumnCount = 4
    .ColumnWidths = "60 pt;60 pt;60 pt;60 pt"
End With
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub CmdCerrar_Click()
'Cerrar formulario
Unload Me
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub txtFiltro1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Ejecutamos el filtro al salir del TextBox

'en caso de no haber añadido ningún año salimos del procedimiento
If Me.txtFiltro1.Value = "" Then Exit Sub
'limpiamos el ListBox de filtros anteriores
Me.ListFiltrados.Clear

'recorremos cada fila de la hoja con datos
For i = 2 To 16
    'en caso de coincidencia con el Año
    If Cells(i, "C").Value = CInt(Me.txtFiltro1.Value) Then
        'añadimos el registro
        Me.ListFiltrados.AddItem Cells(i, "A")
        Me.ListFiltrados.List(Me.ListFiltrados.ListCount - 1, 1) = Cells(i, "B").Value
        Me.ListFiltrados.List(Me.ListFiltrados.ListCount - 1, 2) = Cells(i, "C").Value
        Me.ListFiltrados.List(Me.ListFiltrados.ListCount - 1, 3) = Cells(i, "D").Value
    End If
Next i
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub ListFiltrados_Click()
'Activar la celda del registro elegido

Range("A2").Activate
'contamos el número de elementos filtrados
Cuenta = Me.ListFiltrados.ListCount

'recorremos cada elemento del ListBox para idetificar cuál está seleccionado
For i = 0 To Cuenta - 1
    If Me.ListFiltrados.Selected(i) Then
        'una vez detectado el elemento seleccionado
        'según el NumId
        Valor = Me.ListFiltrados.List(i, 0)
        'realizamos la búsqueda y selección en la hoja de cálculo
        fila = Hoja1.Range("A2:A15").Find(What:=Valor, LookAt:=xlWhole, After:=ActiveCell).Row
        Hoja1.Range(Cells(fila, "A"), Cells(fila, "D")).Select
    End If
Next i
End Sub

El resultado final sería:

Seguir leyendo...

VBA: Lanzar macro cuando cambia el valor de una fórmula.

En un comentario de este post un lector preguntaba:

...Tengo 4 hojas, las tres primeras hojas en su celda A1 toman los valores de la cuarta hoja, de A1, A2 y A3, respectivamente, con la siguiente expresión: =SI(Hoja4!A1="","",Hoja4!A1) =SI(Hoja4!A2="","",Hoja4!A2) =SI(Hoja4!A3="","",Hoja4!A3) El inconveniente que tengo es que no cambia el color de la etiqueta a menos que lo haga de forma manual, sin estar formulado...

La idea es conseguir ejecutar una acción, mediante macros, cuando cambia el valor de una celda que contiene una fórmula.
La clave para resolver este asunto es emplear una variable general donde cargar el valor de la celda, para luego controlarla.
La carga la podemos realizar al entrar o activar la hoja de trabajo en cuestión (por ejemplo)... una vez cargada, la seguiremos controlando dentro del evento _Change (o _Calculate) de los ejemplos siguientes


Insertamos el siguiente código dentro de la ventana de código de la Hoja:

Option Explicit
Dim Monitor
 
Private Sub Worksheet_Activate()
'Cargamos la variable para controlar el cambio de valor en la celda A1
Monitor = Range("A1").Value
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)

'Prevenimos un posible bucle debido a cambios en la hoja
Application.EnableEvents = False
'Comparamos la celda A1 controlada con el valor anterior...
If Range("A1").Value <> Monitor Then
    'Lanzamos la acción deseada...
    MsgBox "La fórmula en A1 ha cambiado.."
    
    'cargamos de nuevo la variable de control de cambio
    Monitor = Range("A1").Value
End If
 
'Reseteamos los eventos
Application.EnableEvents = True
End Sub

Otra opción sería emplear el evento _Calculate (muy similar al anterior...):

Public ValorPrevio As Variant

Private Sub Worksheet_Activate()
'Cargamos la variable para controlar el cambio de valor en la celda A1
ValorPrevio = Hoja2.Range("A1").Value
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Worksheet_Calculate()
Set etiqueta = ThisWorkbook.Sheets(2).Tab
    
'Prevenimos un posible bucle debido a cambios en la hoja
Application.EnableEvents = False

'verificamos si el valor de A1 ha cambiado respecto al valor anterior
If Hoja2.Range("A1").Value <> ValorPrevio Then
    'lanzamos mensaje
    MsgBox "Valor celda A1 en Hoja2 ha cambiado el valor de su fórmula"
    'ejecutamos la acción deseada..
    etiqueta.ColorIndex = 3
    'cargamos de nuevo la variable de control de cambio
    ValorPrevio = Hoja2.Range("A1").Value
End If

'Reseteamos los eventos
Application.EnableEvents = True
End Sub

Seguir leyendo...