Contar elementos únicos con una tabla dinámica en Excel

Problema

Queremos contar los elementos únicos de un campo mediante una tabla dinámica en Excel.

Solución

Antes del Excel 2013 esto no era posible. Con Excel 2013, 2016 y Office 365 es posible emplear la función de resumen Recuento distinto, siempre que usemos el modelo de datos en Excel.

1. Al crear la tabla dinámica, seleccionamos: Agregar estos datos al modelo de datos y hacemos clic en Aceptar.

2. Añadimos el campo que queremos contar en el área valores

3. Clic en la flecha situada a la derecha del nombre del campo y luego selecciona la opción de Configuración de campo de valor.

4. En Resumir campo de valor por seleccionamos Recuento distinto y clic en Aceptar.

Resultado

Se puede apreciar como el campo Recuento distinto de Campo 2 cuenta los elementos únicos. Mientras que Recuento de Campo 2 el número de valores no vacíos, el equivalente a CONTARA.

Referencias

• Crea un modelo de datos en Excel

Entradas relacionadas

• Desactivar IMPORTARDATOSDINAMICOS como referencia a tablas dinámicas
• Desactivar autoajustar anchos de columnas al actualizar tablas dinámicas
• Opciones de cálculo de fórmulas, rendimiento y tratamiento de errores
• Reducir el tamaño de ficheros con tablas dinámicas en Excel
• Tablas dinámicas con origen de datos dinámico

Las entradas más visitadas en 2016

Las 10 entradas más visitadas en 2016 en las categorías de R, Excel, Access y VBA han sido:

R

1. Test de normalidad Shapiro–Wilk en R
2. Calcular la moda en R usando el paquete modeest
3. Eliminar duplicados de un data frame en R
4. Superponer gráficos en R: distribución normal
5. Introducción al diagrama de caja (box plot) en R
6. Una introducción a la minería de textos en R con El Quijote
7. Medir el tiempo de ejecución de procesos en R
8. Introducción al paquete igraph para R
9. Usando RStudio por primera vez
10. Gráfico Q-Q en R

Excel

1. Convertir horas en un número decimal en Excel
2. Invertir lista en Excel
3. Generar números aleatorios entre dos valores con decimales
4. Omitir todos los errores de una hoja
5. Formato condicional que diferencia ceros y celdas vacías
6. Números aleatorios de una lista o rango de opciones
7. Calcular la duración del día y el horario de verano en Excel
8. Vínculos externos rotos al mover el libro de destino en Excel
9. Numerar las celdas visibles con el autofiltro en Excel
10. Reducir el tamaño de ficheros con tablas dinámicas en Excel

Access

1. Cuadros combinados en cascada en Ms Access
2. Porcentaje del total de la columna en una consulta de Access
3. Convertir texto en número conservando los decimales en Access
4. Generar números aleatorios entre dos valores en Ms Access
5. Exportar tabla o consulta de Access como CSV
6. Consulta para generar una muestra aleatoria en Ms Access
7. Cambiar programáticamente la ruta de tablas vinculadas en Access
8. Crear una consulta de UNION y de UNION ALL en Ms Access
9. Consulta de actualización con los datos de otra tabla
10. Usar un cuadro combinado para buscar al teclear en Access

VBA

1. Importar ficheros CSV en Excel mediante VBA
2. Cambiar origen de datos de múltiples tablas dinámicas con VBA
3. Insertar un reloj en Excel usando VBA
4. Sucesión de Fibonacci en Excel y VBA
5. Medir el tiempo de ejecución de subrutinas en VBA
6. Mostrar u ocultar formas en Excel mediante VBA
7. Ocultar o mostrar todas las hojas de un libro de Excel (VBA)
8. Exportar un rango de Excel al bloc de notas mediante VBA
9. Buscar y reemplazar cadenas de texto con VBA
10. VBA: cambiar el texto a mayúsculas, minúsculas o nombre propio

Entradas relacionadas

• Las 10 entradas de R más visitadas en 2015
• Las 10 entradas de Excel más visitadas en 2015
• Las 10 entradas de Access más visitadas en 2015
• Las 10 entradas de R más visitadas en 2014
• Las 10 entradas de Excel más visitadas en 2014
• Las 10 entradas de Access más visitadas en 2014

Muestra aleatoria de un data frame basada en una condición en R

Problema

Deseamos extraer una muestra aleatoria de filas de un data frame basada en una condición en R. En nuestro ejemplo una muestra aleatoria del data frame iris que no contenga filas de la especie Setosa.

Solución

Paquete base

set.seed(12)
iris[sample(which(iris$Species != "setosa"), 2), ]

   Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
57           6.3         3.3          4.7         1.6 versicolor
131          7.4         2.8          6.1         1.9  virginica

Paquete dplyr

library(dplyr)
set.seed(12)
filter(iris, Species != "setosa") %>% sample_n(., 2) 

   Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
7           6.3         3.3          4.7         1.6 versicolor
81          7.4         2.8          6.1         1.9  virginica

Entradas relacionadas

• Muestra aleatoria de filas de un data frame en R
• Consulta para generar una muestra aleatoria en Ms Access
• Muestra aleatoria en Excel
• Muestra aleatoria de filas por grupos en R

Referencias

• stackoverflow

Reordenar el resto columnas de un data frame con dplyr

Problema

Cuando seleccionamos columnas usando el paquete dplyr, queremos organizar el resto de columnas al principio o el final del data frame, sin tener que escribir sus nombres manualmente.

En el siguiente ejemplo, el data frame flights tiene 19 columnas y queremos ordenar 5 de ellas al comienzo o al final del mismo.

library(nycflights13)
library(dplyr)
head(flights)

# A tibble: 6 × 19
   year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
                                     
1  2013     1     1      517            515         2      830            819
2  2013     1     1      533            529         4      850            830
3  2013     1     1      542            540         2      923            850
4  2013     1     1      544            545        -1     1004           1022
5  2013     1     1      554            600        -6      812            837
6  2013     1     1      554            558        -4      740            728
# ... with 11 more variables: arr_delay , carrier , flight ,
#   tailnum , origin , dest , air_time , distance ,
#   hour , minute , time_hour 

Solución

Las 5 columnas al inicio del data frame

col <- c("carrier", "tailnum", "year", "month", "day")
select(flights, one_of(col), everything())

# A tibble: 336,776 × 19
   carrier tailnum  year month   day dep_time sched_dep_time dep_delay arr_time
                                  
1       UA  N14228  2013     1     1      517            515         2      830
2       UA  N24211  2013     1     1      533            529         4      850
3       AA  N619AA  2013     1     1      542            540         2      923
4       B6  N804JB  2013     1     1      544            545        -1     1004
5       DL  N668DN  2013     1     1      554            600        -6      812
6       UA  N39463  2013     1     1      554            558        -4      740
7       B6  N516JB  2013     1     1      555            600        -5      913
8       EV  N829AS  2013     1     1      557            600        -3      709
9       B6  N593JB  2013     1     1      557            600        -3      838
10      AA  N3ALAA  2013     1     1      558            600        -2      753
# ... with 336,766 more rows, and 10 more variables: sched_arr_time ,
#   arr_delay , flight , origin , dest , air_time ,
#   distance , hour , minute , time_hour 

Las 5 columnas al final del data frame

select(flights, -one_of(col), one_of(col))

# A tibble: 336,776 × 19
   dep_time sched_dep_time dep_delay arr_time sched_arr_time arr_delay flight
                                          
1       517            515         2      830            819        11   1545
2       533            529         4      850            830        20   1714
3       542            540         2      923            850        33   1141
4       544            545        -1     1004           1022       -18    725
5       554            600        -6      812            837       -25    461
6       554            558        -4      740            728        12   1696
7       555            600        -5      913            854        19    507
8       557            600        -3      709            723       -14   5708
9       557            600        -3      838            846        -8     79
10      558            600        -2      753            745         8    301
# ... with 336,766 more rows, and 12 more variables: origin , dest ,
#   air_time , distance , hour , minute , time_hour ,
#   carrier , tailnum , year , month , day 

Para añadir las columnas a todo el data frame

Si quisiéramos añadir las 5 columnas, duplicándolas, a la totalidad del data frame embp.

# 5 columnas al principio
bind_cols(select(flights, one_of(col)), flights)

  # Con dplyr::relocate()
  flights %>%  
    relocate(carrier, tailnum, year, month, day) 

# 5 columnas al final
bind_cols(flights, select(flights, one_of(col)))

  # Con dplyr::relocate()
  flights %>%  
    relocate(carrier, tailnum, year, month, day, .after = last_col()) 

Entradas relacionadas

• Cómo omitir variables de un data frame en R basadas en un vector de caracteres usando dplyr
• Descartar variables de un data frame por su nombre

Referencias

• stackoveflow

Desactivar autoajustar anchos de columnas al actualizar tablas dinámicas con VBA

Problema

Por defecto al crear una tabla dinámica, Excel actualizará automáticamente el ancho las columnas de la tabla dinámica al tamaño del texto o valor de número más extenso. En lugar de desactivar esta opción manualmente como en la entrada anterior para cada una de las tablas dinámicas, queremos hacerlo automáticamente mediante VBA

Solución

Para desactivar esta opción en todas las tablas dinámicas de la hoja activa.

1. Abrimos el Editor de Microsoft Visual Basic: Alt+F11.
2. Copiamos una de las subrutinas de más abajo en un módulo.
• Tablas en la hoja activa

Sub Desactivar_Autoajustar()
For Each pt In ActiveSheet.PivotTables
    pt.HasAutoFormat = False
Next
End Sub

• Todas las tablas del libro de Excel

Sub Desactivar_Autoajustar2()
Dim n As Integer
Dim i As Integer
Dim pt As PivotTable
n = ActiveWorkbook.Worksheets.Count
    For i = 1 To n
        For Each pt In ActiveWorkbook.Worksheets(i).PivotTables
            pt.HasAutoFormat = False
        Next pt
    Next i    
End Sub

3. Ejecutamos la subrutina, F5.
4. Habremos deseactivado las casillas de Autoajustar anchos de columnas al actualizar en todas las tablas dinámicas de la hoja activa o del libro, dependiendo de la opción elegida.

Referencias

• Opciones de tabla dinámica

Desactivar autoajustar anchos de columnas al actualizar tablas dinámicas

Problema

Por defecto al crear una tabla dinámica Excel actualizará automáticamente el ancho las columnas de la tabla dinámica automáticamente al tamaño del texto o valor de número más extenso.

Solución

Para desactivar esta opción

1. Abrimos el cuadro de diálogo Opciones de tabla dinámica, botón secundario del ratón y clic en Opciones de tabla dinámica.

2. Desactivamos la casilla Autoajustar anchos de columnas al actualizar.

Referencias

• Opciones de tabla dinámica

Reemplazar saltos de línea por comas con Notepad++

Problema

Queremos reemplazar los saltos de línea con comas con Notepad++.

Solución

1. Hacemos clic en Ctrl+H, o mediante la barra de menú Search > Replace.
2. Escribimos en Find what:[\r\n]+
3. Y en Replace with, una coma seguida de un espacio: ,
4. Marcamos la botón Regular expression y presionamos Replace All.

Resultado final

Entradas relacionadas

• Reemplazar los saltos de línea por comas con Notepad++
• Entrecomillar palabras de una lista y separarlas con comas en Notepad++
• Comparar dos ficheros en Notepad++
• Editar un bloque de texto vertical en Notepad++
• Eliminar líneas en blanco con Notepad++

Obtener abreviatura y nombre de meses a partir de un vector numérico en R

Problema

En R deseamos obtener los nombre de los mesos, o las 3 iniciales los mismos, en inglés a partir de un vector numérico.

Solución

Empleamos las constantes month.abb y month.name que contienen respectivamente la abreviatura de 3 letras y los nombres de los nombres de los meses en inglés.

mes_num <- 1:12
mes_abr <- month.abb[mes_num]
mes_nombre <- month.name[mes_num]
data.frame(mes_num, mes_letra, mes_nombre)

Resultado

   mes_num mes_abr mes_nombre
1        1       Jan    January
2        2       Feb   February
3        3       Mar      March
4        4       Apr      April
5        5       May        May
6        6       Jun       June
7        7       Jul       July
8        8       Aug     August
9        9       Sep  September
10      10       Oct    October
11      11       Nov   November
12      12       Dec   December 

Referencias

• Built-in Constants