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...