Limpieza de datos en Excel

La limpieza de datos es el proceso de identificar y corregir los errores e incoherencias en los conjuntos de datos sin procesar para mejorar la calidad de la información

Ventajas de la limpieza de datos

Disponer de datos limpios aumentará la productividad general y permitirá obtener información de la más alta calidad para la toma de decisiones. Algunas de las ventajas son las siguientes.

• Eliminación de errores cuando se usan múltiples fuentes de datos. 
• La disminución de errores da como resultado clientes más felices y empleados menos frustrados.
• Monitoreo de errores y mejores informes que permiten ver de dónde provienen los errores, lo que facilita la reparación de datos incorrectos o corruptos para futuras aplicaciones.

A continuación se describen algunas herramientas comunes de Excel que nos van a permitir limpiar un conjunto de datos.

Corrección ortográfica

Con esta herramienta podemos encontrar errores ortográficos, además, podemos buscar valores que no se usan de forma coherente, como los nombres de productos o servicios de una columna en una base de datos. Esta herramienta se puede encontrar en la pestaña Revisar y en el grupo con el mismo nombre. A continuación se muestra un ejemplo de uso.

Eliminación de espacios y caracteres

Es posible que en nuestro conjunto de datos haya caracteres y espacios en blanco adicionales a los que deberían de estar, para resolver esto podemos usar las funciones LIMPIAR y ESPACIOS, es específico, LIMPIAR quita los caracteres que no se pueden imprimir y ESPACIOS borra todos los espacios con excepción de aquellos que ya se dejan entre palabras. En seguida se muestra un ejemplo de uso de la función que elimina espacios.

Formato condicional

Con la herramienta de formato condicional podemos visualizar tablas con registros y elementos duplicados, una vez que estos hayan sido identificados podemos usar la opción de Quitar duplicados que se encuentra en la pestaña de Datos. Como ejemplo, si tenemos una columna que debería tener valores de código únicos para cada elemento, entonces podemos usar estas herramientas para quitar tales registros repetidos, véase la siguiente imagen.

Uso adecuado de mayúsculas y minúsculas

Es posible que nuestros datos no estén bien escritos, por ejemplo, los nombres y apellidos no hacen uso de iniciales mayúsculas o bien, es necesario que haya datos con todas las letras en mayúscula o minúscula, para abordar esto podemos usar las fórmulas de MAYUSC, MINUSC y NOMPROPIO que nos ofrece Excel. A continuación se muestra un ejemplo de uso de la formula para nombres propios.

Separar texto en varias columnas

Otra herramienta que puede ser de utilidad es la separación del texto en un celda entre varias celdas, para esto, Excel tiene la herramienta llamada texto en columnas, como ejemplo de uso, podemos separar el nombre y apellido de las personas que estén en una sola columna, o bien, separar ciudad y país. En seguida se muestra un ejemplo de texto a columnas.

Formato de fechas

En distintas ocasiones los datos de fechas no van a estar en el formato adecuado, más bien estarán en un formato diferente al propio para el dato, entonces se puede hacer uso de la herramienta de convertir texto a columnas para realizar la corrección, en este caso no se va elegir ningún delimitador para realizar la separación, además, solo vamos a elegir el formato que tendrá la fecha resultante. En la siguiente imagen se muestra como se puede configurar el formato de la fecha de salida para lograr nuestro objetivo.

Celdas vacías

En el caso de registros con celdas o campos vacíos primero se tiene que buscar la manera de obtener los datos por diversos medios, ya sea con el dueño de la base de datos, la página web desde donde se descargo la información, etc. sin embargo, cuando esto no es posible entonces podemos proceder a eliminar los registros con datos faltantes para que el análisis devuelva mejores resultados, para esto podemos filtrar las columnas y una vez hecho esto podemos borrar los registros. En la siguiente imagen se muestra una tabla que contiene nombres de producto faltantes, podemos filtrar por medio de ese campo y luego elegir que hacer con tales elementos de acuerdo a las posibilidades.

Uso de macros
La limpieza de datos en Excel puede ser repetitiva, tardada y propensa a errores, para optimizar este tipo de flujos de trabajo podemos usar las herramientas de Visual Basic y así agilizar diversos procesos. En el siguiente ejemplo se presenta una subrutina que permite limpiar datos y quitar espacios innecesarios en columnas con datos erróneos.

Sub limpiar_quitar_espacios()

    Dim valorRango As String

    valorRango = "A2:A100"

    Dim celdaIndice As Range

    Dim valorCelda As String

    Sheets("Productos").Select

       

    For Each celdaIndice In Range(valorRango)

        valorCelda = celdaIndice.Value

        celdaIndice.Value = WorksheetFunction.Clean(valorCelda) 

        valorCelda = celdaIndice.Value

        celdaIndice.Value = WorksheetFunction.Trim(valorCelda)

    Next

    

    valorRango = "B2:B100"

    For Each celdaIndice In Range(valorRango)

        valorCelda = celdaIndice.Value

        celdaIndice.Value = WorksheetFunction.Clean(valorCelda) 

        valorCelda = celdaIndice.Value

        celdaIndice.Value = WorksheetFunction.Trim(valorCelda)

    Next

End Sub

Esta subrutina elimina los espacios y caracteres indeseables en dos rangos diferentes, en las columnas A y B, para esto es necesario reiniciar la variable valorRango con las celdas a recorrer, además se tienen que llamar a las funciones Clean y Trim que son las mismas que LIMPIAR Y ESPACIOS que se usan en el entorno gráfico de las hojas de un libro de Excel.

Referencias

A continuación se muestran los enlaces que describen los detalles técnicos de los métodos y objetos de Visual Basic utilizados en este artículo.

• Objeto WorksheetFunction
• Método Trim
  
• Método Clean
  

El modelo de objetos de Excel y su importancia para las macros

Microsoft Excel proporciona un modelo de objetos que es útil para obtener acceso a hojas de cálculo, rangos, tablas, gráficos, etc., esto es de relevancia porque podemos manipular tales objetos por medio de código de programación, en específico por medio del lenguaje VBA (Visual Basic for Applications), esto tiene el beneficio de poder personalizar la funcionalidad de las aplicaciones y proyectos creados con esta hoja de cálculo. Este modelo de objetos se describe a continuación.

• Un Libro contiene una o varias Hojas de cálculo.
• Una Hoja de cálculo contiene colecciones de aquellos objetos de datos presentes en la hoja individual y da acceso a las celdas en los objetos de Rango.
• Un Rango representa un grupo de celdas adyacentes.
• Los Rangos se usan para crear y colocar Tablas, Gráficos, Formas y otros objetos de visualización u organización de datos.

Objetos y colección de VBA

Un objeto representa un elemento de una aplicación, como una hoja de cálculo, una celda, un gráfico, un formulario o un informe, además, en Visual Basic, es necesario identificar un objeto para poder llamar a uno de sus métodos o cambiar el valor de sus propiedades .

Una colección es un objeto que contiene varios objetos y generalmente son objetos del mismo tipo. En Microsoft Excel, el objeto Workbooks contiene todos los objetos Workbook abiertos. En Visual Basic, la colección Forms contiene todos los objetos Form de una aplicación

Ahora, para poder acceder a estos objetos por medio de código VBA se van a mostrar diversos ejemplos.

Libros

El objeto Workbook representa un libro de Microsoft Excel, sin embargo puede ser de utilidad usar el objeto ActiveWorkbok que representa un objeto Workbook presente en la ventana activa o en uso. 

Los libros tienen varios métodos para operar con ellos, por ejemplo, podemos usar la propiedad Author para establecer un nombre de propiedad para un archivo, o bien, usamos el método Save para guardar los cambios, esto se muestra en los siguientes segmentos de código,

ActiveWorkbook.Author = "Samuel Ramirez"

ActiveWorkbook.Save

Hojas

En las macros de Excel podemos acceder a todas las hojas de un libro por medio del objeto Worksheets, como ejemplo, podemos asignar la primera hoja del libro a una variable Worksheet, cambiarle el nombre a la misma y también definir su orientación en vertical, esto se puede realizar con el siguiente código.

Dim hoja As Worksheet

Set hoja = Worksheets(1)

hoja.Name = "HojaProductos"

hoja.PageSetup.Orientation = xlPortrait

Rangos

Un rango es un grupo de celdas adyacentes en el libro. Los rangos tienen tres propiedades básicas: values, formulas y format. Estas propiedades obtienen o establecen los valores de celda, las fórmulas que se deben evaluar y el formato visual de las celdas. 

El objeto Range es el que representa una celda, una fila, una columna o un grupo de celdas adyacente en un libro.

Para ejemplificar lo descrito anteriormente podemos hacer uso de la siguiente tabla de Excel.

Para acceder a los valores contenidos en una celda o rango tenemos que usar la propiedad Value. En el siguiente código se accede al valor del rango A1, que en este caso también es una celda. Este ejemplo tiene una serie de nombres en la columna A, es por eso que se declara una variable de tipo String para almacenar el resultado obtenido de la llamada a la propiedad Value.

Dim valorCadena As String

valorCadena = Worksheets("HojaProductos").Range("A1").Value

MsgBox valorCadena

Para establecer una fórmula en, por ejemplo, la celda C1, podemos usar el siguiente código. Usamos la propiedad Formula para definir el método CONCATENAR en esa celda, en esta caso se unirá un nombre y un apellido.

Worksheets("HojaProductos").Range("C1").Formula = "=CONCATENAR(A1, "" "", B2)"

Además, para establecer el formato de un rango, podemos cambiar el tipo de letra, a saber, podemos establecer letra negritas en el rango de columnas de apellidos por medio de la propiedad Font.

Worksheets("HojaProductos").Range("B1:B7").Font.Bold = True

Celdas

En Visual Basic la propiedad Cells representa un objeto Range que contiene todas las celdas de la hoja de cálculo (no solo las celdas que están actualmente en uso). Las celdas usan la sintáxis Cells(Fila, columna) para acceder a elementos específicos. Como ejemplo, se puede acceder a la celda E2 y F2 para asignarles una valor por medio de la propiedad Value, además, podemos cambiar el color de cada una de estas celdas con la propiedad Interior.Color.

Worksheets("HojaProductos").Cells(2, 5).Value = 260

Worksheets("HojaProductos").Cells(2, 6).Value = "Camisas"

    

Worksheets("HojaProductos").Cells(2, 5).Interior.Color = _

        RGB(16, 40, 87)

Worksheets("HojaProductos").Cells(2, 6).Interior.Color = _

        RGB(56, 99, 33)

Tablas

En VBA se usa el objeto ListObject para representar una tabla en la hoja de cálculo. Además, tenemos disponible el objeto ListObjects que representa una colección de las tablas pertenecientes a una hoja. 

Como ejemplo, podemos acceder a todas las filas de la segunda tabla de la hoja de productos y luego podemos agregar una fila a esa tabla, para esto podemos usar el siguiente código.

Dim listaFilas As ListRows 

Set listaFilas = Worksheets("HojaProductos").ListObjects(2).ListRows

listaFilas.Add

En este caso ListObjects(2) representa la segunda tabla de la hoja, ListObjects(1) la primer tabla y así sucesivamente, además, ListRows es una entidad u objeto que representa todas las filas de la tabla y el método Add es el que nos permite agregar una nueva fila.

Gráficas

En Visual Basic el objeto ChartObject representa un gráfico incrustado en una hoja de cálculo, las propiedades y métodos de ChartObject controlan la apariencia y el tamaño del gráfico incrustado. El siguiente código muestra un ejemplo de configuración de una gráfica en una hoja de cálculo.

Dim grafica As ChartObject

Set grafica = _            Worksheets("HojaProductos").ChartObjects.Add(Left:=350, _

 Top:=100, Width:=280, Height:=200)

grafica.Chart.SetSourceData _                       Source:=Sheets("HojaProductos").Range("A10:C13"), _

 PlotBy:=xlColumns

grafica.Chart.SetElement (msoElementChartTitleAboveChart)

grafica.Chart.ChartTitle.Text = "Existencias"

grafica.Chart.HasLegend = False

En este código se declara una variable de tipo ChartObject para almacenar la gráfica que se desea incrustar en la hoja de cálculo, después se llama al método Add para establecer la esquina superior izquierda de la gráfica así como el ancho y alto de la misma, esta llamada al método devuelve un objeto que se guarda en la variable gráfica. También se llama al método SetSourceData que recibe los argumentos Source y PlotBy que definen la fuente de datos para dibujar la gráfica y el modo de representación de los datos, adicionalmente se ejecuta el método SetElement y se le provee el argumento msoElementChartTitleAboveChart para definir que se va mostrar el título que se encuentra en la parte superior del gráfico. Finalmente se usan las propiedades ChartTitle.Text para establecer el texto que se va colocar en el título previamente descrito y HasLegend con un valor de False para desactivar las etiquetas (estas etiquetas pueden ser las propiedades o características de los datos) que pueda tener el gráfico. La gráfica resultante se muestra en la siguiente imagen.

Instrucción Set

La instrucción Set que se uso previamente nos sirve para asignar un objeto (devuelto por una llamada a un método) a una variable de objeto (Declarada previamente por medio de la instrucción Dim). Es posible asignar una expresión de objeto o Nothing por medio de Set. En el siguiente código se realizan algunas asignaciones de objetos a variables de objetos.

Dim hoja As Worksheet

Set hoja = Worksheets(1)

Set objetoTest = Nothing

En este caso la variable de objeto llamada hoja es asignada con la primer hoja de calculo del libro y la variable objeto llamada objetoTest se asigna con Nothing. La palabra clave Nothing nos sirve para desasociar una variable de objeto de un objeto real y de esta manera usar esa variable de objeto para asignarle otro diferente.

Referencias

Para obtener más detalles técnicos sobre las propiedades y métodos del modelo de objetos de Excel se recomienda visitar los siguiente enlaces oficiales de Microsoft.

• El objeto Workbook
• El objeto Worksheet
• El objeto Range
• El objeto ListObject
• El objeto ListRow
• La propiedad Cells
• El objeto CharObject
  
• Creación de variables de objetos

Estructuras de repetición en macros de Excel

Las macros de Excel tienen a disposición del usuario un conjunto de estructuras de control de código que permiten ejecutar acciones repetitivas y con ello automatizar tareas y flujos de trabajo que así lo requieren, estas estructuras son las siguientes:

• Do While... Loop
• For... Next
• For Each... Next

El ciclo Do While... Loop se usa para ejecutar un bloque de sentencias un número indefinido de veces. Las sentencias se repiten mientras una condición sea verdadera o hasta que se convierta en verdadera.

La estructura For...Next se utiliza para repetir un bloque de sentencias un número específico de veces. Este bucle utilizan una variable de contador cuyo valor aumenta o disminuye con cada repetición.

El ciclo For Each... Next repite un bloque de instrucciones para cada objeto de una colección o cada elemento de una matriz. Visual Basic hace uso de una variable cada vez que se ejecuta el bucle.

Ejemplos de aplicación

A continuación se describen una serie de ejemplos en los que estos bucles pueden ser de utilidad en flujos de trabajo de Excel.

Suponiendo un caso en el que se desea realizar la limpieza de un conjunto de datos, podemos usar el ciclo For Each... Next para establecer un color de celda en aquellas que se encuentren vacías, de acuerdo a esto vamos a poder identificarlas de manera más sencilla y en consecuencia tomar una serie acciones como borrarlas o tenerlas pendientes para un posterior procesamiento de acuerdo a las necesidades del análisis. El código que describe lo descrito previamente de muestra a continuación.

Public Sub establecerColorCeldasVacias()

    Sheets("Hoja1").Select

    Dim celda As Range

    

    For Each celda In Range("A1:A38")

        If IsEmpty(celda) Then

            celda.Interior.Color = RGB(255, 164, 32) 'Se coloca un color anaranjado

        End If

    Next

End Sub

En este código definimos una variable rango para que el ciclo lo recorra y establecemos una variable llamada celda que va representar cada elemento sobre el que vamos iterando, además, usamos la propiedad Interior.Color para establecer el color de las celdas que cumplan la condición de estar vacías.

Otro caso en que los ciclos pueden ser de mucha utilidad es cuando queremos enviar datos desde una columna hacia otra, ya sea en la misma hoja o en una diferente, en este caso podemos usar el ciclo For... Next porque ya sabemos el número específico de datos que queremos enviar, en el siguiente ejemplo se copian datos en la misma hoja y desde una columna hacia otra.

Public Sub copiarDatosEntreColumnas()

    Dim i As Integer

    

    For i = 1 To 38

        Sheets("Hoja1").Cells(i, 2).Value =                        Sheets("Hoja1").Cells(i, 1).Value

    Next i

End Sub

En esta subrutina se copian los valores de celda desde la columna 1 (A) hacia la columna 2 (B), este código puede ser muy útil para conjuntos de datos cuya cantidad de registros sea muy grande. En este caso el ciclo recorre desde la primera fila hasta las número 38.

Por último, podemos hacer uso del ciclo Do While... Loop para generar un listado de fechas en un rango de celdas, esto puede se de utilidad para aplicaciones que necesiten llevar un control del día en que ocurrió un evento determinado, el ejemplo se muestra a continuación.

Public Sub generarFechas()

    Sheets("Hoja1").Select

    Dim i As Integer

    

    i = 1    

    Do While i <= 60

        Cells(i, 1).Value = Date + i - 1

        i = i + 1

    Loop

End Sub

En este caso se va iterar sobre la celda A, es por eso que se utiliza el 1 en el segundo argumento de la propiedad Cells, además, en la asignación de la fecha se va aumentar el valor del índice i al objeto Date y se le va restar un valor de uno para que la lista de fechas comience en el día actual.

Referencias

Los siguientes enlaces describen los detalles técnicos de las estructuras de Visual Basic utilizadas en este artículo.

• Iterando a través del código
  
• Ciclo Do While
  
• Ciclo For Next
  
• Ciclo For Each Next

El método ExportAsFixedFormat de Excel

En las macros de Excel está disponible una función para exportar las hojas y libros de Excel a formato PDF, esto puede ser muy útil para crear un reporte en este formato, o bien, para imprimir archivos con esa misma extensión, este método se llama ExportAsFixedFormat.

Convertir todo un libro de Excel a PDF

Para convertir todas las hojas de un libro de Excel en un solo archivo PDF se tiene que abrir un libro que se haya realizado y después en la pestaña Programador accedemos a la opción de Visual Basic para poder programar la macro que necesitamos, en esta macro abrimos y cerramos un procedimiento por medio de la instrucción Sub y le colocamos el nombre que deseemos, por ejemplo, le colocamos el nombre guardarComoUnSoloPDF, el código se muestra a continuación.

Public Sub guardarComoUnSoloPDF()

 'Código de programación

End Sub

Ahora, podemos declarar una variable de tipo String para almacenar la ruta en la que se va guardar el PDF, seguidamente se tiene que colocar la ruta elegida y asignarla a la variable declarada. Además, será necesario invocar al método ExportAsFixedFormat por medio del objeto ActiveWorkbook; los principales argumentos que se envían a este método son Type que indica el formato en que se va guardar el libro, FileName que es la cadena de la ruta donde se guardará el archivo, Quality que representa la calidad con la que serán guardadas las hojas de libro y OpenAfterPublish que es una bandera que se establece en True o False, este último permite mostrar o no, por pantalla, el PDF generado por medio del método ExportAsFixedFormat; todos estos argumentos se deben separar por comas. El código que realiza lo descrito previamente se muestra a continuación.

Public Sub guardarComoUnSoloPDF()

    Dim RutaArchivo As String

    

    RutaArchivo = "C:\Users\nombreUsuario\nombreCarpeta"

    

    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF,        Filename:=RutaArchivo & "\" & ActiveWorkbook.Name,         Quality:=xlQualityStandard, openafterpublish:=False

    

    MsgBox "Las hojas del libro " & ActiveWorkbook.Name & " se han convertido a PDF correctamente."

End Sub

En este caso, en nombreUsuario y nombre Carpeta se deben colocar los nombres de nuestra preferencia, además, el valor xlTypePDF del argumento indica que salida será en formato PDF, xlQualityStandard indica que la calidad será de tipo estándar y False de openafterpublish indica que no se va visualizar el archivo al terminar la ejecución del procedimiento o método. Por último se coloca un mensaje por medio de MsgBox para indicar que el archivo se ha creado correctamente.

Otro aspecto de interés es la propiedad ActiveWorkBook que representa el libro en la ventana activa, es decir, el libro de Excel que se abrió previamente. La propiedad ActiveWorkBook en si misma es un objeto WorkBook que tiene acceso al atributo Name, este atributo contiene el nombre del libro que se está trabajando.

Convertir una hoja de un libro de Excel a PDF

Para convertir una sola hoja de Excel a PDF el procedimiento será similar pero en este caso se va utilizar la propiedad ActiveSheet, esta propiedad representa la hoja que esté seleccionada y en visualización en el archivo de Excel que se ha abierto previamente. El código de programación que nos va permitir lograr este objetivo es el siguiente.

Public Sub guardarHojaEnPDF()

    Dim RutaArchivo As String

    

    RutaArchivo = "C:\Users\nombreUsuario\nombreCarpeta"

    

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=RutaArchivo & "\" & ActiveSheet.Name, Quality:=xlQualityStandard, openafterpublish:=False

    

    MsgBox "La hoja de nombre " & ActiveSheet.Name & " se ha convertido a PDF correctamente."

End Sub

A continuación se muestran algunos enlaces que describen los detalles técnicos de los métodos y propiedades de Visual Basic utilizados en este artículo.

• Método ExportAsFixedFormat
  
• Propiedad ActiveWorkBook
  
• Propiedad ActiveSheet
  

Análisis de una base de datos por medio de tablas dinámicas

Análisis de información de un conjunto de datos con productos de electrónica, este conjunto de datos simula una tienda de componentes electrónicos con algunos campos relevantes como las categorías, precio, país de origen, disponibilidad del producto, cantidad en existencia etc, además se colocan diversos productos que son comunes en el área de la electrónica. En este análisis se hace uso de diferentes configuraciones de tablas dinámicas de Excel para observar la utilidad que estas nos pueden proveer al momento de gestionar los productos que se tienen en el stock.

Enlace para acceder a este proyecto de Excel: https://1drv.ms/x/c/26601cb78d61cc7d/ProyectoComponentesElectronicos

La siguiente imagen muestra un extracto del conjunto de datos (Clic en la imagen para ampliarla)

Una primer configuración de tabla dinámica para este conjunto de datos es colocar en la zona de filas la categoría y justo después el producto, además en la zona de valores el precio unitario, esto con el fin de crear un filtro que nos permita analizar de manera individual cada categoría junto con los productos que pertenecen a ella, además, con esto en mente es posible agregar a la zona de filtros el país de origen y la existencia de los productos, esto sería de utilidad para situaciones como las siguientes:

• Visualizar aquellas categorías con productos sin existencia para un posterior resurtido de componentes.
• Considerar colocar nuevos productos a cada categoría así como agregar nuevas categorías para mejorar la variedad del producto.
• Evaluar la posibilidad de contactar nuevos países a los que se les solicite importar determinados productos de cada categoría, etc.

En la siguiente imagen se muestra la tabla dinámica estructurada como se describió previamente. (Clic en la imagen para ampliarla)

Otra configuración para analizar los datos que puede resultar útil en una tabla dinámica es la siguiente, en la zona de filtros la categoría y la existencia del producto, en la zona de columnas el país de origen, en la zona de filas los productos y en la zona de valores los precios de cada producto, con esto, podemos evaluar los siguientes casos:

• Considerar todos los productos de una categoría que provienen de un país determinado para ampliar o disminuir la cantidad de países de los cuales traer esos productos requeridos.
• Evaluar la necesidad de ampliar los productos que pertenecen a cada categoría y al mismo tiempo tomar en cuenta los países de los que provienen tales productos, etc.

La tabla dinámica con esta configuración se puede observar a continuación. (Clic en la imagen para ampliarla)

La ventaja de las tablas dinámicas es que podemos colocarlas de diferentes maneras para facilitar la visualización, resumen y análisis de datos, incluso con configuraciones simples como la siguiente, en la zona de filtros la categoría, en la de filas el producto y en los valores los precios, esto podría ser valioso para analizar cada categoría junto con los precios unitarios de los productos para casos como gestionar los precios de acuerdo a las necesidades económicas de la empresa o negocio, además esto tiene la ventaja de que la visualización se muestra en un solo lugar sin tener que recorrer todo el conjunto de datos cuando este se vuelva más extenso. La siguiente imagen muestra esta tabla dinámica que es más sencilla de estudiar. (Clic en la imagen para ampliarla)

Adicionalmente, en la tabla que se configuró previamente se podría agregar un campo calculado que nos muestre el precio total para cada producto de acuerdo a la existencia de los mismos, esto nos va permitir personalizar los cálculos que no estén en existencia en la serie de operaciones que nos ofrece Excel, con esto podemos resumir y administrar de manera más extensa la información del conjunto de datos. La siguiente imagen muestran el campo calculado que se colocó en la tabla dinámica.

Como conclusión de este análisis, se puede observar el numeroso grupo de posibilidades que ofrecen las tablas dinámicas para facilitar la toma de decisiones que se necesitan tomar en una empresa, negocio o conjunto de información, en este caso, en el área de la electrónica.

Análisis estadístico con Excel - Ejercicio 4

Las puntuaciones obtenidas por 100 aspirantes a la universidad en el último ejercicio se presentan en la siguiente figura  (Clic en la imagen para ampliarla).

Se desea construir la distribución de frecuencias adecuada para las puntuaciones, hallar el porcentaje de alumnos que aprobó el examen con 6 y encontrar el porcentaje de alumnos que sacaron notas superiores a 6.  Además, si sólo hay 20 plazas, ¿en qué nota hay que situar a los obtuvieron una plaza? 

También se requiere realizar  las  representaciones  gráficas  de  la  distribución  adecuadas  para  este problema.

La tabla de frecuencias se muestran en la siguiente imagen  (Clic en la imagen para ampliarla).

Se puede observar que el porcentaje de aspirantes que aprobó el examen con 6 fue del 11%, esto se observa en la frecuencia relativa de Xi=6, además, los aspirantes que obtuvieron una nota mayor a 6 son del 20%, esto se puede observar en la frecuencia relativa acumulada de Xi=6, hasta este valor hay un 80% que obtuvieron calificación de 6 o menos, por tanto el porcentaje restante obtuvieron una calificación superior a 6.

Respecto al número de plazas, se sabe que solo hay 20 disponibles, entonces en los datos se puede ver en la frecuencia absoluta acumulada (Ni) que hay 80 personas con calificación menor o igual a 6, las demás personas son 20, ellos obtuvieron una calificación de mayor a 6, entonces se puede concluir que la calificación para obtener una plaza debe ser mayor a 6.

Debido a que se está trabajando con una variable cuantitativa con datos sin agrupar entonces esta se puede representar con un diagrama de barras o mediante un polígono de frecuencias. Estas gráficas se muestran a continuación (Clic en la imagen para ampliarla).

Análisis estadístico con Excel - Ejercicio 3

Se han medido los diámetros de 50 tornillos y se han obtenido los resultados siguientes en milímetros.

Se desea obtener la tabla de frecuencias para la variable diámetro, construir el histograma de frecuencias absolutas, estudiar la simetría de la distribución, además se tiene la pregunta ¿se puede intuir si los datos provienen de una distribución normal?

Para formar la tabla de frecuencias adecuadamente, podemos tomar el número de clases dado por la fórmula de Sturgess: k=1+Ent(3*3logN); o bien: k=Ent(√N), siendo Ent la función parte entera.

En este caso k=Ent(√50)=Ent(7.071)=7, esto es, se van a tomar 7 clases para representar al histograma.

Para calcular los límites para estas clases se van a usar dos fórmulas:

• Límite inferior de la clase = Límite inferior de la clase anterior + Tamaño de intervalo
• Límite superior de la clase = Límite inferior de la clase anterior + Tamaño de intervalo - Unidad de variación

En este caso, al observar los datos es posible ver que la mínima diferencia entre los diámetros es de 0.1 mm, por tanto este valor se puede tomar como la unidad de variación. También hay que ajustar el tamaño exacto del intervalo, este debe ser por lo menos igual a la siguiente unidad de variación después del valor del ancho de clase (0.65), entonces 0.65+0.1=0.75 será el tamaño ajustado del ancho de clase.

Los cálculos de los límites se realizan en Excel y se muestran en la siguiente imagen.

Con estos cálculos se puede introducir una columna de clases para la opción del Rango de salida de la función Histograma de Excel, este rango de salida corresponde a los límites superiores de las clases.

El histograma que se va generar se muestra en la siguiente imagen junto con la tabla de frecuencias que ha creado Excel a partir de los límites que se han proporcionado.

Respecto a la normalidad de los datos, se puede observar una ligera simetría hacia la izquierda en la gráfica del histograma.

Análisis estadístico con Excel - Ejercicio 2

Los valores de los pesos en miligramos de 80 engranes pequeños producidas por una máquina son los siguientes:

Sobre estos datos, se desea construir la distribución de frecuencias adecuada a los datos, el histograma de frecuencias absolutas, el polígono de frecuencias relativas acumuladas y comprobar la normalidad de los datos.

Para lograr el objetivo se rellena la ventana de Histograma como se indica en la figura siguiente. En el campo Rango de entrada se introduce el rango en el que se sitúan los datos de la variable. En el campo Rango de clases se sitúa el rango que ocupa la columna de los extremos superiores de los intervalos de clase, pero en este caso se deja vacío para que Excel divida los datos automáticamente en un número adecuado de clases de la misma anchura. En el campo Rango de salida se coloca el rango que ocupará la tabla de frecuencias, pero en este caso se va a colocar sólo el extremo superior izquierdo de dicho rango. Es importante seleccionar la opción Gráfico para obtener el histograma de frecuencias absolutas, y la opción Porcentaje acumulado para obtener el polígono de frecuencias relativas acumuladas.

El histograma generado a partir de lo explicado previamente se ajusta a una campana de Gauss lo que indica la normalidad de los datos, el histograma se muestra a continuación.

Análisis estadístico con Excel - Ejercicio 1

En una clínica se han registrado durante un mes las longitudes en metros que los niños recorren el primer día que comienzan a caminar, obteniéndose los siguientes resultados.

Se desea construir la distribución de frecuencias adecuada para la variable longitud y realizar los gráficos pertinentes que la representen.

En estos datos, llamaremos X al conjunto de datos que contienes las distancias recorridas por los niños, esta es una variable cuantitativa con valores sin agrupar, se puede graficar un diagrama de barras situando sobre el eje de las abscisas los valores de la variable X. Sobre el eje de las ordenadas también se van a graficar las frecuencias absolutas acumuladas Ni, con esto obtendremos el diagrama de barras acumuladas. La tabla de frecuencias de la variable X se puede realizar en Excel partiendo de las variables Xi y ni para después realizar los cálculos de las frecuencias absolutas acumuladas, frecuencias relativas y frecuencias relativas acumuladas.

El diagrama de barras se va a crear usando el asistente de gráficos. Comenzamos seleccionando las celdas que contienen los datos que deseamos presentar en el gráfico de barras (en este caso la columna de frecuencias absolutas) y luego elegimos el gráfico Columnas agrupadas de Excel.

También se puede construir el diagrama de frecuencias absolutas acumuladas de manera similar a como se realizo en el proceso anterior.

El polígono de frecuencias se puede construir uniendo los puntos (Xi, ni), para esto, primero se eligen los pares de datos de estas columnas y luego elegimos el tipo de gráfico Dispersión con líneas rectas y marcadores. El gráfico resultante se muestra en la siguiente figura.

También puede construirse el polígono de frecuencias acumuladas uniendo los puntos (Xi, Ni), previa selección de los mismos en la hoja de cálculo y utilizando el asistente para gráficos como en el ejemplo anterior.

Interpretación de los resultados de las frecuencias

• Las frecuencias absolutas muestran que las distancias más comunes recorridas por los niños de la clínica fueron 4 m y 6 m, estas frecuencias tienen un valor de 10.
• El 82.5% de los niños y niñas ha recorrido 6 m o menos, el otro 17.5% restante han recorrido 8 o más metros. Esto nos dice que son muy pocas las personas que han recorrido grandes distancias como 8 o más metros, esto se debe a la edad de estas personas. Entonces se puede decir que los datos se concentran en un distancia recorrida de 6 m o menos.
• En las frecuencias relativas (fi) se puede observar que las distancias 1, 8 y 10 son las menos comunes, cada una de estas solo aportan un 5% (0.05) al total de los datos.

Funciones de bases de datos en Excel

En Excel existen funciones para procesar datos en hojas con grandes cantidades de datos, estas nos facilitan el procesamiento porque piden pocos argumentos para poder trabajar. A continuación se describe la función BDSUMA en Excel.

BDSUMA proporciona la suma de los números de los campos (columnas) de registros que cumplen las condiciones especificadas.

Sintaxis: BDSUMA(base_de_datos, nombre_de_campo, criterios)

La función BDSUMA requiere los siguientes argumentos:

• base_de_datos: Este es el rango de celdas que compone la lista o base de datos. Una base de datos es una lista de datos relacionados en la que las filas de información son registros y las columnas de datos son campos. La primera fila de una lista contiene etiquetas para cada columna en ella. Este argumento es obligatorio
• nombre_de_campo: Esto especifica qué columna se usa en la función. Especifique el rótulo de columna entre comillas, como "Edad" o "Rendimiento", por ejemplo. Como alternativa, puede especificar un número (sin comillas) que represente la posición de la columna dentro de la lista: por ejemplo, 1 para la primera columna, 2 para la segunda, etc. También podemos colocar la celda que contiene el campo sobre el cual deseamos calcular la suma
• criterios: Este es el rango de celdas que contiene las condiciones especificadas. Puede usar cualquier rango en el argumento criterios mientras este incluya al menos un rótulo de columna y una celda debajo del mismo en la que se pueda especificar una condición para la columna.

Nota: Los 3 argumentos de la función son requeridos para la misma, no es posible omitir ninguno de ellos.

Como ejemplo, podemos considerar la siguiente base de datos de una tienda de artículos.

 

En esta base de datos podemos calcular, por ejemplo, el precio de aquellos artículos cuyo país de origen es España  y su sección es Ferretería, para esto, en la función BDSUMA pasamos como primer argumento la base de datos que está entre las celdas A1 a G41, después le indicamos la celda que contiene a campo en donde se va a realizar la suma y por último se le provee a la función los criterios, estos últimos están fueron colocados entre las celdas A44 a G45.

 

 

 El uso de la función BDSUMA nos facilita los cálculos porque solo es necesario indicar una matriz que contenga los criterios que queremos aplicar a la operación de suma, esa matriz se puede construir por medio de una pequeña tabla que tiene los datos con los que deseamos trabajar.

 

En caso de que se quisiera usar otra fórmula para hacer los cálculos con criterios determinados entonces sería un poco más complicado realizar esta operación, por ejemplo si se usa la fórmula SUMAR.SI.CONJUNTO se tendrían que mandar más argumentos a la misma pero en el caso de la fórmula BDSUMA es posible usar pocos argumentos, uno de ellos es la matriz o tabla que contiene todos los datos con los criterios requeridos. Un uso práctico de la fórmula BDSUMA sería cuando el número de criterios sea muy extenso, en ese caso tal fórmula reduciría drásticamente el número de argumentos que se necesitan si en su lugar se usará otra fórmula como SUMAR.SI.CONUNTO.

 

 

 

 

Creación de tablas dinámicas en Excel 2019 y Microsoft Excel 365 (Web)

Una tabla dinámica es una herramienta que nos permite analizar, explorar y presentar datos en forma de resumen. Una tabla dinámica también nos permite realizar una gran cantidad de cálculos con nuestros datos sin la necesidad de escribir fórmulas, además, estas tablas nos permiten ver la información de forma ordenada, esquematizada y amigable.

Estructura de una tabla dinámica

Una tabla dinámica tiene la estructura que se muestra en la siguiente imagen.

Como se muestra, una tabla dinámica tiene 4 zonas o secciones, en ellas debemos de colocar los campos de alguna de nuestras tablas o base de datos que tengamos previamente en Excel. Es muy importante determinar en qué zona deseamos colocar cada campo para que de esta manera Excel pueda mostrarnos el diseño de tabla dinámica que necesitamos.

Cabe destacar que Excel nos permite dejar vacías una o varias zonas de una tabla dinámica, con esto, podemos personalizar el resumen de tabla dinámica que deseamos presentar o analizar, lo que no tiene mucho sentido en dejar vacío es la zona de valores porque en ella se van a realizar cálculos numéricos en base a los datos que tengamos.

Otro aspecto importante de las tablas dinámicas es que podemos agregar varios campos a una zona de una tabla, es por esto que también se denominan dinámicas porque podemos realizar en ellas muchos movimientos que nos van a permitir probar cual es la mejor manera en que podemos resumir y analizar nuestros datos.

Recomendaciones para la creación de una tabla dinámica

Para crear una tabla dinámica se propone utilizar las siguientes recomendaciones.

• Utilizar cabeceras o títulos en las columnas de nuestros datos
• Evitar usar espacios en blanco en campos numéricos, por ejemplo, se puede rellenar con ceros en espacios en blanco.
• No utilizar filas o columnas completamente en blanco.
• Eliminar los cálculos de una tabla o de una base de datos que ya los contenga.

El uso de estas recomendaciones se propone con el fin de que podamos crear una tabla dinámica que sea fácil de leer e interpretar.

Creación de una tabla dinámica en Excel 2019

A continuación se puede observar los pasos que se pueden seguir para crear una tabla dinámica en Excel 2019 versión de escritorio.

Consideremos la siguiente base de datos de una tienda de artículos.

Para poder crear una tabla dinámica a partir de estos datos, vamos a seleccionar cualquier celda de nuestra tabla para que Excel tenga una referencia desde la cual debe crear la tabla dinámica y después vamos a dirigirnos a la pestaña Insertar y luego damos clic en Tablas dinámicas recomendadas.

Con esto, va a aparecer una ventana que nos va a proponer algunos diseños de tabla y también nos va a dar la opción de crear una en blanco, vamos a elegir esta opción.

Al seleccionar esta opción, vamos a poder crear una tabla dinámica a nuestro gusto desde la ventana de creación que nos provee Excel.

En la ventana de creación de tabla dinámica podemos ver las zonas que se han descrito anteriormente.

Un primer diseño de tabla dinámica que podemos definir es aplicando lo siguiente: el campo Sección en la zona de columnas, el nombre de los artículos en las filas y la suma de precios de cada producto en la zona de valores.

Con esta configuración, la tabla dinámica resultante es la siguiente.

La tabla dinámica resultante se puede interpretar como un sistema de coordenadas, por ejemplo, el abrigo de caballero tiene una intersección con la sección de Confección, el balón de baloncesto con la sección de deportes, el carro de control con la sección de Juguetería y así sucesivamente. Otro aspecto importante que se puede observar en esta configuración de tabla dinámica es el total general, este total se genera para todas las secciones pero también para todos los productos, sin embargo la mayoría de los productos aparecen una sola vez en la tabla, por tanto los totales de cada producto son del mismo valor de cada producto, en algunas excepciones existen totales de productos que aparecen más de una vez en la tabla de datos, el destornillador es un caso de este tipo, como aparece 2 veces la tabla dinámica va sumar el total de ambos precios y lo va mostrar en la intersección del producto con la categoría a la que pertenece (ferretería), podemos dar un doble clic en ese producto (en la tabla dinámica) y se va abrir una subtabla que muestra el precio individual de cada destornillador así como todas los datos relacionados a este producto.

En esta tabla dinámica que acabamos de crear solo hemos colocado un campo en cada zona de la tabla pero esta configuración puede cambiar como veremos a continuación.

Otro tipo de tabla dinámica que podemos crear es utilizando la siguiente configuración: la sección y el nombre del artículo en la zona de filas y la suma de precios en la zona de valores, cabe destacar que es importante el orden en que se coloca un campo en alguna zona de la tabla dinámica, en este caso primero se coloca la sección y después el nombre del artículo en la zona de filas.

La tabla dinámica resultante se muestra en la siguiente figura.

La tabla resultante nos muestra la importancia del orden en el que se colocan los campos en cada zona de una tabla dinámica porque esto nos permite analizar de manera más sencilla el resumen de nuestros datos.

Creación de una tabla dinámica en Excel 365 (Web)

La creación de una tabla dinámica en Excel 365 versión web es similar a la versión de escritorio, para acceder a esta versión solo es necesario tener una cuenta de correo de Microsoft y los archivos se guardan en la nube en lugar de guardarse en nuestra computadora local. 

A partir de nuestra bases de datos, vamos a seleccionar cualquier celda de nuestra tabla para que Excel tenga una referencia desde la cual debe crear la tabla dinámica y después vamos a dirigirnos a la pestaña Insertar y luego damos clic en Tabla dinámica y después elegimos Desde una tabla o rango.

Con esto, va a aparecer una ventana que nos va a pedir que seleccionemos la tabla o rango de celdas en el que queremos trabajar y también si deseamos colocar la nueva tabla dinámica en una nueva hoja de cálculo o en la hoja donde se encuentran los datos, en nuestro caso vamos a crearla en una nueva hoja, además, los datos de referencia se seleccionan automáticamente por Excel dado que ya habíamos dado clic en alguna celda de nuestros datos.

Con esto en mente, la creación de la tabla dinámica desde la ventana de creación, es similar a la versión de escritorio, solo necesitamos elegir los campos y su orden para poder colocarlos en las zonas de la tabla dinámica.