Borrar minigráficos de una hoja de Excel

Problema

Queremos borrar todos los minigráficos de una hoja de Excel.

Solución

Manualmente

• Opción 1: Clic sobre uno de los minigráficos, clic en Herramientas para minigráficos y clic en borrar. En el desplegable elegimos la opción deseada.

• Opción 2: Clic sobre uno de los minigráficos, clic en Minigráficos y elegimos la opción de borrar deseada.

VBA

Podemos borrar todos los minigráficos de una hoja ejecutando el siguiente código. La acción es irreversible.

Sub Borrar_Minigráficos()

    ActiveSheet.Select
    Selection.SparklineGroups.ClearGroups

End Sub

Entradas relacionadas

• Gráficos dentro de las celdas en Excel
• Ley de Benford en Excel

Ejes con escalas proporcionales en gráficos de Excel

Problema

Cuando creamos un gráfico, Excel no respeta el mismo ratio para el eje de abscisas y de ordenadas. Por ello si dibujamos las líneas de división se genera una cuadrículas rectangular.

Solución

Para crear una cuadrícula y que conserve el mismo ratio empleamos el siguiente código escrito por Jon Peltier.

1. Copiamos el siguiente código en un módulo

Option Explicit

Sub MakePlotGridSquareOfActiveChart()
    MakePlotGridSquare ActiveChart
End Sub

Sub MakePlotGridSquareOfAllCharts()
    Dim myChartObject As ChartObject
    For Each myChartObject In ActiveSheet.ChartObjects
        MakePlotGridSquare myChartObject.Chart
    Next
End Sub

Sub MakePlotGridSquare(myChart As Chart, Optional bEquiTic As Boolean = False)
    
    Dim plotInHt As Integer, plotInWd As Integer
    Dim Ymax As Double, Ymin As Double, Ydel As Double
    Dim Xmax As Double, Xmin As Double, Xdel As Double
    Dim Ypix As Double, Xpix As Double

    With myChart
        ' get plot size
        With .PlotArea
            plotInHt = .InsideHeight
            plotInWd = .InsideWidth
        End With
        
        Do
            ' Get axis scale parameters and lock scales
            With .Axes(xlValue)
                Ymax = .MaximumScale
                Ymin = .MinimumScale
                Ydel = .MajorUnit
                .MaximumScaleIsAuto = False
                .MinimumScaleIsAuto = False
                .MajorUnitIsAuto = False
            End With
            With .Axes(xlCategory)
                Xmax = .MaximumScale
                Xmin = .MinimumScale
                Xdel = .MajorUnit
                .MaximumScaleIsAuto = False
                .MinimumScaleIsAuto = False
                .MajorUnitIsAuto = False
            End With
            If bEquiTic Then
                ' Set tick spacings to same value
                Xdel = WorksheetFunction.Max(Xdel, Ydel)
                Ydel = Xdel
                .Axes(xlCategory).MajorUnit = Xdel
                .Axes(xlValue).MajorUnit = Ydel
            End If
        
            ' Pixels per grid
            Ypix = plotInHt * Ydel / (Ymax - Ymin)
            Xpix = plotInWd * Xdel / (Xmax - Xmin)
            
            ' Keep plot size as is, adjust max scales
            If Xpix > Ypix Then
                .Axes(xlCategory).MaximumScale = plotInWd * Xdel / Ypix + Xmin
            Else
                .Axes(xlValue).MaximumScale = plotInHt * Ydel / Xpix + Ymin
            End If
            
            ' Repeat if "something" else changed to distort chart axes
            ' Don't repeat if we're within 1%
        Loop While Abs(Log(Xpix / Ypix)) > 0.01
        
    End With

End Sub

2. Presionamos ALt+F8 y nos aparecerán dos opciones:

MakePlotGridSquareOfActiveChart: Modificará el gráfico activo.
MakePlotGridSquareOfAllCharts: Modificará todos los gráficos de la hoja.

Referencias

• Make Gridlines Square

Gráfico de líneas verticales en Excel

Problema

En Excel, deseamos crear el siguiente gráfico de líneas verticales:

Solución

Como Excel solamente permite la orientación horizontal de este tipo de gráficos, lo giramos 90º con la Cámara.

1. Creamos el gráfico de línea horizontal
2. Alineamos el texto de los dos ejes 270º.


3. Seleccionamos el rango de celdas que comprende el gráfico

4. Clic en el icono de la Cámara

5. Clic en el área de la hoja donde deseamos insertar el gráfico y lo giramos 90º.

Medidas de tendencia central en histogramas en R: moda

En esta entrada añadiremos a los histogramas otra medida de tendencia central: la moda. Completamos así una entrada anterior en la que añadimos la media y la mediana.

Asimétrica negativa o a la izquierda

media < mediana < moda

rbeta(n, 5, 2)

Simétrica

media = mediana = moda

rbeta(n, 5, 5)

Asimétrica positiva o a la derecha

media > mediana > moda

rbeta(n, 2, 5)
parámetro x de legend = "topright"

Código

Instalamos y cargamos el paquete modeest, para calcular la moda de las distribuciones con las funciones betaMode y normMode.

install.packages("modeest")
library(modeest)

Empleamos la función de densidad de beta:

dbeta(x, shape1, shape2, ncp = 0, log = FALSE)

Modificaremos los parámetros de la distribución beta a (shape1) y b (shape2) para alterar la forma de la distribución y que sea asimétrica negativa, simétrica o asimétrica positiva.

Asimétrica negativa: shape1 = 5, shape2 =2
Simétrica: shape1 = 5, shape2 =5
Asimétrica positiva: shape1 = 2, shape2 =5

# Ejemplo: asimétrica negativa
set.seed(2014)
vble  <- rbeta(1000000, 5, 2) # Parametros a modificar

# Histograma
hist(vble, 
     prob = TRUE,
     xlim = c(0, 1),
     col = "slategray2",
     border = "white",
     main = "Asimetría negativa", 
     xlab = "", 
     las = 1) 

# Función de densidad
lines(density(vble), # density plot
      lwd = 2, # thickness of line
      col = "darkblue")

# Líneas
  # Media
mean  <- mean(vble)
segments(x0 = mean, y0 = 0, 
         x1 = mean, y1 = dbeta(mean, 5, 2), # Parametros a modificar
         col = "blue", lwd = 2) # lty = 3 dotted line
  # Mediana
median <-  median(vble)
segments(x0 = median, y0 = 0, 
         x1 = median, y1 = dbeta(median, 5, 2), # Parametros a modificar
         col = "red", lwd = 2)
  # Moda
mode <- betaMode(5, 2)
segments(x0 = mode, y0 = 0, x1 = mode, 
         y1 = dbeta(mode, 5, 2), 
         col = "orange", lwd = 2)

# Leyenda
legend(x = "topleft", # Ubicación de la leyenda
       c("Función de densidad", "Media", "Mediana"),
       col = c("darkblue", "blue", "red"),
       lwd = c(2, 2, 2),
       bty = "n")

Alternativa

Podemos añadir las líneas de la media y mediana mediante la función abline. La diferencia respecto al ejemplo anterior con segments es que la línea cortará a la función de densidad pues es infinita.

set.seed(2014)
vble  <- rnorm(n = 1000000) 
hist(vble, 
     prob = TRUE,
     xlim = c(-3,3), 
     ylim = c(0, .4),
     col = "slategray2",
     border = "white",
     main = "Simétrica", 
     xlab = "", 
     las = 1) 

# Función de densidad
lines(density(vble), # density plot
      lwd = 2, # thickness of line
      col = "darkblue")
# Media
abline(v = mean(vble),
       col = "blue",
       lwd = 2)
# Mediana
abline(v = median(vble),
       col = "red",
       lwd = 2)
# Mode
abline(v = normMode(),
       col = "orange",
       lwd = 2)

Entradas relacionadas

Calcular la moda en R usando el paquete modeest

Medidas de tendencia central en histogramas en R: media y mediana

Generar una distribución normal aleatoria en R

Operaciones básicas con la distribución normal en R

Calculadora y gráfica de la distribución normal no estándar en R

Con pequeñas modificaciones de la función creada anteriormente, podemos calcular y representar la probabilidad de un área bajo la curva de la función de densidad de una distribución normal no estándar.

Ejemplo: N(2, 9), media de 2 y varianza de 9.

En primer lugar nos pedirá el valor de la media y de la desviación típica. Si los dejamos en blanco asumirá que es una distribución normal estándar N(0,1). Luego nos solicitará dos valores, el límite inferior x1 y el superior x2. La función pnorm devuelve la probabilidad a la izquierda del valor especificado.

Intervalo: Pr(x1<X<x2) = Pr(X<x2) − Pr(X<x1)

• Para calcular la probabilidad comprendida dentro de un intervalo, restamos de la probabilidad del límite superior x2 la probabilidad del límite inferior.

Cola izquierda: Pr(X<x2)

• Para calcular la probabilidad por debajo de un valor, solamente introducimos el límite superior x2. Cuando nos solicite el límite inferior x1, lo dejamos en blanco y presionamos la tecla Entrar.

Cola derecha: Pr(X>x1)

• Para calcular la probabilidad por encima de un valor, solamente introducimos el límite inferior x1. Cuando no solicite el límite superior x2, lo dejamos en blanco y presionamos la tecla Entrar.

Función

fun <- function(){
  # Pregunta la media y desviación típica 
  media <- as.numeric(readline("¿Cuál es la media?"))
  sd <- as.numeric(readline("¿Cuál es la desviación típica?"))
  # Distribución normal estándar si se dejan en blanco
  if (is.na(media)) {
    media <- 0
  }
  if (is.na(sd)) {
    sd <- 1
  }    
  # Límites
  x1 <- as.numeric(readline("¿Cuál es el valor inferior x1?"))
  x2 <- as.numeric(readline("¿Cuál es el valot superior x2?"))
  # Cola izquierda y derecha
  if (is.na(x1)) {
    x1 <- -100
  }
  if (is.na(x2)) {
    x2 <- 100
  }  
  prob <- pnorm(x2, media, sd) - pnorm(x1, media, sd)
  curve(dnorm(x, mean = media, sd = sd), 
        xlim=c(-4*sd, 4*sd), 
        las = 1, 
        main = bquote("Probabilidad:" ~ .(round(prob, 4))),
        ylab = bquote("Media:" ~ .(media) ~ "Desv. típica:"~ .(sd)))
  cord.x <- seq(x1, x2, 0.1)
  cord.y <- dnorm(cord.x, media, sd)
  polygon(c(x1, cord.x, x2), c(0, cord.y, 0), col = "skyblue") 
  prob
}

Intervalos

El ejemplo del gráfico anterior. Con una N(2, 9), calcular la probabilidad entre -1 y 1: Pr(-1<X<1)

# Ejecutamos la función
fun()

En la consola nos preguntará la media, la desviación típica y los dos límites del intervalo. Tecleamos todos.

¿Cuál es la media?2
¿Cuál es la desviación típica?3
¿Cuál es el valor inferior x1?-1
¿Cuál es el valot superior x2?1

La consola arrojará el resultado:

[1] 0.2107861

Creará el siguiente gráfico con el área sombreada. He modificado el código de la entrada anterior para que muestre la probabilidad en la misma línea del título y el título del eje de ordenadas con la media y desviación típica introducida. He usado la función bquote.

Ejemplo práctico

Si los resultados de un test del inteligencia realizados a unos niños se distribuyen con una media de un coeficiente intelectual de 100 y una desviación típica de 15. ¿Qué proporción de niños se espera que tengan un coeficiente de inteligencia entre 80 y 120?

¿Cuál es la media?100
¿Cuál es la desviación típica?15
¿Cuál es el valor inferior x1?80
¿Cuál es el valot superior x2?120
[1] 0.8175776

Entradas relacionadas:

Calculadora y gráfica de la distribución normal estándar en R

Creamos una función muy sencilla para calcular y representar la probabilidad de un área bajo la curva de la función de densidad de la distribución normal estándar N(0, 1).

No solicitará dos valores, el límite inferior x1 y el superior x2. La función pnorm devuelve la probabilidad a la izquierda del valor especificado.

Intervalo: Pr(x1<X<x2) = Pr(X<x2) − Pr(X<x1)

• Para calcular la probabilidad comprendida dentro de un intervalo, restamos de la probabilidad del límite superior x2 la probabilidad del límite inferior.

Cola izquierda: Pr(X<x2)

• Para calcular la probabilidad por debajo de un valor, solamente introducimos el límite superior x2. Cuando nos solicite el límite inferior x1, lo dejamos en blanco y presionamos la tecla Entrar.

Cola derecha: Pr(X>x1)

• Para calcular la probabilidad por encima de un valor, solamente introducimos el límite inferior x1. Cuando no solicite el límite superior x2, lo dejamos en blanco y presionamos la tecla Entrar.

Función

fun <- function(){
# Convertimos vector de carácter a numérico
  x1 <- as.numeric(readline("¿Cuál es el límite inferior x1?"))
  x2 <- as.numeric(readline("¿Cuál es el límite superior x2?"))
# Gestión de errores
  if (is.na(x1))  {
    x1 <- -100
  }
  if (is.na(x2))  {
    x2 <- 100
  }
# Cálculo de probabilidad  
  prob <- pnorm(x2) - pnorm(x1)
# Gráfico
  curve(dnorm(x), xlim = c(-3, 3), las = 1, main = c("Probabilidad:", 
        round(prob, 4)))
# Sombreamos el área
  cord.x <- seq(x1, x2, 0.1)
  cord.y <- dnorm(cord.x)
  polygon(c(x1, cord.x, x2),c(0, cord.y ,0),col = "skyblue")
# Resultado en la consola  
  prob
}

Intervalos

La probabilidad en el intervalos entre -0.6745 y 0.6745.

# Ejecutamos la función
fun()

En la consola nos preguntará los dos límites del intervalo. Escribimos cada uno y presionamos la tecla entrar.

¿Cuál es el valor inferior x1?-0.6745
¿Cuál es el valot superior x2?0.6745

La consola arrojará el resultado:

[1] 0.5000065

Creará el siguiente gráfico con el área sombreada y la probabilidad redondeada en el título del mismo.

Cola de la izquierda

Dejamos el límite inferior en blanco

¿Cuál es el límite inferior x1?
¿Cuál es el límite superior x2?1
[1] 0.8413447

Cola de la derecha

Dejamos el límite superior en blanco

¿Cuál es el límite inferior x1?2.1
¿Cuál es el límite superior x2?
[1] 0.01786442

Entradas relacionadas:

Regresión lineal simple para principiantes en Excel

La regresión lineal simple sirve para evaluar la relación entre dos variables, una variable independiente X o predictora y una variable dependiente o explicada Y. Se trata de encontrar una ecuación lineal con fines predictivos.

Datos

Como ejemplo, usamos los datos del peso corporal y del corazón de una muestra gatos machos y hembras del paquete MASS en R. Importamos o copiamos los datos en Excel. Tendremos 4 columnas con 144 observaciones. Fichero cats.csv

Obs - número de observaciones
Sex - sexo
Bwt - Body Weight, peso corporal
Hwt - Heart Weight, peso del corazón

Diagrama de dispersión

1. Seleccionamos las columnas Bwt y Hwt.
2. En la ficha Insertar, en el grupo Gráficos, hacemos clic en Dispersión.

Recta de regresión y R²

3. Botón secundario sobre los puntos del gráfico y seleccionamos Agregar línea de tendencia.

4. En Opciones de línea de tendencia, por defecto aparece marcado el tipo lineal, y en la parte inferior marcamos Presentar ecuación en el gráfico y Presentar el valor R cuadrado en el gráfico.

5. Formateamos el gráfico apropiadamente

Alternativa

1. En la ficha Presentación, en el grupo Análisis, clic en Línea de tendencia.

2. Clic sobre la línea de tendencia y seleccionamos Formato de línea de tendencia y, en la parte inferior, marcamos Presentar ecuación en el gráfico y Presentar el valor R cuadrado en el gráfico.

Aplicación del modelo

Con la ecuación de la recta de regresión podemos calcular el valor de la variable dependiente Y introduciendo un valor de la variable independiente X en la misma: y = mx + b

Definimos los nombres:

Bwt= =Hoja1!$C$2:$C$145
Hwt = =Hoja1!$D$2:$D$145

Calculamos la pendiente y la intersección en Y:

m - pendiente: =PENDIENTE(Hwt;Bwt)
b - intersección Y: =INTERSECCION.EJE(Hwt;Bwt)

Ejemplos:

Si un gato pesa 5 Kg, ¿cuál es el peso estimado de su corazón?: = 5*4,03406-0,35666 = 19,81365
Si un gato pesa 3 Kg, ¿cuál es el peso estimado de su corazón?: = 3*4,03406-0,35666 = 11,74552

Bondad del modelo

R², el coeficiente de determinación, nos informa de la bondad del ajuste del modelo. Se calcula elevando al cuadrado el coeficiente de correlación de Pearson. Varía entre 0 y 1.

0 - las variables son independientes

1 - existe una relación perfecta entre las variables

En nuestro ejemplo es 0,6466. Indica que conociendo el peso de un gato mejoramos un 64,66% nuestra estimación del peso del corazón, si usamos nuestro modelo en lugar del peso medio del corazón de un gato.

Referencias

• Regresión lineal simple
• Regresión lineal simple para principiantes en R
• Regresión lineal simple en R con ggplot2