Preprocesamiento de datos para Excel con Python

Para procesar datos de Excel con Python es necesario entender el uso de la librería Pandas del lenguaje Python, esta herramienta nos permite analizar y manipular datos de una forma rápida y flexible. Esta librería usa como elemento principal una estructura de datos llamada dataframe que contiene filas y columnas tal como una tabla de Excel, además, también se usan las series que pueden representar las columnas de Excel.

En este artículo se va a ejemplificar el uso de los dataframes con el objetivo de manipularlos correctamente y así poder aplicar sus funciones en posteriores procesos y tareas de automatización y procesamiento de datos de Excel.

Requisitos previos

Para poder usar la librería de pandas de Python es necesario instalarla por medio de la línea de comandos. Además, en caso de que ya se tenga instalada un versión de Python en la máquina junto con algunos proyectos del mismo lenguaje entonces es posible que se necesite crear un entorno virtual de Python que nos permita aislar el ambiente de trabajo y con ello evitar la incompatibilidad de las líberías del proyecto de pandas con las librerías de Python que requieren los otros proyectos.

En seguida se detallan algunos pasos recomendados para usar pandas de la mejor manera en nuestra computadora.

• Creación de un entorno virtual para evitar incompatibilidad entre librerías de Python
• Instalación de librería pandas con el entorno virtual previamente activado, esto nos va permitir que la instalación de pandas se realice únicamente al interior del entorno y no de forma global con las librerías de Python que posiblemente ya se tengan instaladas.
• Instalación de la librería openpyxl, algunos métodos de pandas dependen directamente de la librería openpyxl, por ejemplo, el guardado de dataframes en archivos excel requiere de esta librería, por tanto también será importante instalarla aunque en un primer momento solo se trabaje con el procesamiento de datos con pandas.
• Elección del interprete correcto de Python, la correcta elección de del interprete nos va ayudar a importar a nuestro programa las librerías que se instalaron para el entorno virtual que fue creado, esto es, si se elige otro interprete que fue instalado de manera global en la computadora y para ese entorno global no se instalo pandas entonces se van a crear errores de importación en el script de trabajo, por tanto se recomienda hacer uso de un editor de código que nos permita elegir un interprete de Python de entre los diversos que existan en nuestra computadora. Un editor de mucha utilidad es Visual Studio Code.

A continuación se muestran algunos comandos usados para este artículo, el interprete de comandos usado es el de Powershell de Windows.

> python - m venv dataframes # creación del entorno virtual

> .\dataframes\Scripts\activate # activación del entorno virtual

> pip install pandas # instalación de pandas

> pip install openpyxl # instalación de openpyxl

Creación de dataframes

Los dataframes se pueden crear a partir de estructuras de datos de Python, por ejemplo, listas, diccionarios y combinaciones de estas estructuras.

A continuación se crea un diccionario a partir de una lista.

lista = ['Guayaba', 'Manzana', 'Jitomate', 'Pera', 'Kiwi', 'Rabano']

dataframe = pd.DataFrame(lista)

Así mismo, podemos crear un dataframe  a partir de un diccionario de arreglos numpy.

datos = {

    'Producto': np.array(['Mayonesa', 'Cafe', 'Galletas']),

    'Precio' : np.array([100, 50, 29]),

    'Cantidad': np.array([40, 25, 20]),

    'Unidad': np.array(['Caja', 'Caja', 'Tarima'])

}

dataframeProductos = pd.DataFrame(datos)

Índice de una dataframe

El índice de un DataFrame es una serie de etiquetas que identifican cada fila, estos índices nos permiten acceder a los datos de manera eficiente y puede ser valores numéricos (valores predeterminado) o bien, etiquetas definidas por el usuario. Cuando imprimimos en pantalla un dataframe, los índices se pueden ver como una primer columna a la izquierda de los datos. Esto se pueden ver en la siguiente imagen.

Para establecer índices definidos por el usuario podemos hacer uso del método set_index() de pandas, esto se muestra en el siguiente código.

galletas = {

    'Marca': ['SaltyCookies', 'ChocoCookies', 'Polvorin', 'Canelocas', 'BakedCookies'],

    'Categoria': ['Saladas', 'Rellenas', 'Mantequilla', 'Azucaradas', 'Saludables'],

    'UPC': ['09897241', '05892231', '19497872', '89897228', '49807382'],

    'Lote': ['MNX09', 'ZXV87', 'RTY91', 'ZXP92', 'NNQ62'],

    'Precio': [55, 30, 65, 35, 70]

}

dataframeGall = pd.DataFrame(galletas)

dataframeNuevoIndice = dataframeGall.set_index('UPC')

Así mismo, podemos acceder a una fila específica por medio del método loc, para ello tenemos que especificar el índice, en este caso el índice fue establecido por el usuario. En este caso se accede a la fila cuyo índice es el UPC 05892231, la fila se imprime por pantalla.

print(dataframeNuevoIndice.loc['05892231'])

En caso de que se desee acceder a una columna de un dataframe tenemos que especificar la etiqueta de la columna. En el siguiente código se obtiene la columna o serie y se guarda en una variable.

columnaCodigos = dataframeGall['UPC']

También podemos acceder a filas que cumplan un determinado criterio, por ejemplo, usando el dataframe que contiene datos de galletas, podemos acceder a las filas cuyo precio sea menor a 40 pesos.

datosFiltrados =  dataframeGall[dataframeGall['Precio'] < 40]

En caso de querer guardar esas filas con precio menor a 40 en un archivo de excel, podemos hacer lo siguiente.

archivo = "preciosBajos.xlsx"

datosFiltrados.to_excel(archivo)

Con las herramientas descritas anteriormente podemos comenzar a procesar datos desde Python y de esta manera usar toda su infraestructura con el propósito combinarla con las capacidades de Excel. Por ejemplo, podemos recibir datos desde una API, procesarlos con pandas y posteriormente enviarlos a Excel para llevar a cabo algún análisis específico tal como crear reportes con los datos, tomar decisiones basadas en datos, gestión de logística, etc.

Referencias

• Entornos virtuales de Python
• Guía de usuario de pandas
• El interprete de Python
• Intérprete de Python en VSCode
  

Consultas entre dos o más tablas en SQL Server

SQL Server utiliza uniones para recuperar datos de varias tablas basándose en las relaciones lógicas entre ellas. Las combinaciones son fundamentales para las operaciones de bases de datos relacionales y permiten combinar datos de dos o más tablas en un único conjunto de resultados.

SQL Server implementa tanto operaciones de unión lógica (definidas por la sintaxis de Transact-SQL) como operaciones de unión física (los algoritmos que se utilizan para ejecutar las uniones). Comprender ambos aspectos ayuda a escribir consultas eficientes y a optimizar el rendimiento de la base de datos.

Las uniones indican la forma en la que SQL Server debe usar los datos de una tabla para seleccionar las filas de otra. 

Una condición de unión define cómo se relacionan dos tablas en una consulta mediante:

• La especificación de la columna de cada tabla que se utilizará para la unión. Una condición de unión típica especifica una clave foránea de una tabla y su clave asociada en la otra.
• La especificación de un operador lógico (por ejemplo, = o <>,) que se utilizará para comparar los valores de las columnas.

Las uniones se expresan lógicamente mediante la siguiente sintaxis de Transact-SQL:

• INNER JOIN 
• LEFT JOIN 
• RIGHT JOIN 
• FULL  JOIN 

En la unión de tipo Inner Join se obtienen los datos que son comunes a 2 o más tablas en las que se realiza la consulta. Left Join devuelve todos los registros de la tabla izquierda y también los que coinciden con los registros de la tabla de la derecha (La tabla de la izquierda es la primer tabla que se define en la consulta de SQL, la de la derecha es la segunda tabla que se define en la consulta), además, si no hay coincidencia entre ambas tablas no se devuelven resultados de registros. Right join es el caso contrario, devuelve todos los registros de la tabla derecha y también los que coinciden con los registros de la tabla de la izquierda. Full Join devuelve todos los registros de las tablas, los de la tabla izquierda, los de la derecha y los que coinciden en ambas tablas.

De manera visual la uniones se muestran en la siguiente imagen.

Ejemplos

Se puede usar el siguiente código para ejemplificar el uso de INNER JOIN.

CREATE TABLE clientes ( 

idcliente int NOT NULL primary key,

nombre varchar(20) NOT NULL,

apellido varchar(30) NOT NULL,

direccion varchar(100) NOT NULL,

ciudad varchar(50) NOT NULL,

telefono numeric(10) NULL,

);

CREATE TABLE ordenes(

id_orden int NOT NULL primary key,

idcliente int foreign key references clientes(idcliente),

fecha_orden date default getdate(),

id_vendedor int NOT NULL

);

SELECT ordenes.id_orden, Clientes.nombre, Clientes.apellido, ordenes.fecha_orden -- De

la tabla ordenes elige el id de la orden, de la tabla de clientes el nombre y el

apellido y de la tabla de ordenes la fecha

FROM ordenes -- Para esto, usa la tabla de ordenes

INNER JOIN Clientes on ordenes.idcliente = Clientes.idCliente; -- y por medio un inner

JOIN con la tabla Clientes trae esos campos CUANDO el id del cliente de la tabla de

ordenes sea el mismo id del cliente en la tabla de Clientes

SELECT ordenes.id_orden, Clientes.nombre, Clientes.apellido, ordenes.fecha_orden,

ordenes.id_vendedor -- También podemos traer el id del vendedor para saber quien le ha

vendido productos al cliente Juan

FROM ordenes

INNER JOIN Clientes ON ordenes.idcliente = Clientes.idCliente

WHERE nombre = 'Juan' ORDER BY fecha_orden; -- Realizamos la misma consulta anterior

pero filtrando para el cliente con nombre Juan y ordenamos por fecha

Este código se puede ver de otra manera: Se toma cada registro de la tabla de ordenes y se checa con qué cliente está asociado esa orden por medio del id del cliente, después de esta verificación se van a devolver el nombre y el apellido en la tabla de clientes pero también el id de la orden y la fecha de la orden en la tabla de ordenes, estos campos se devuelven para cada uno de los registros de la tabla de ordenes.

Si se desea saber cuáles clientes tienen ordenes de servicio y cuáles no las tienen, se puede hacer uso de left join porque de esta manera podemos obtener aquellos registros que están relacionados con la tabla de ordenes pero también aquellos que no están relacionados, esto cumple con la definición de left join que devuelve los propios registros de la tabla izquierda y también sus coincidencias con respecto a otra tabla con la que está relacionada.

El siguiente ejemplo de código muestra cómo se puede usar left join para solucionar el problema previamente explicado.

SELECT Clientes.nombre, Clientes.apellido, ordenes.id_orden, ordenes.fecha_orden -- Devuelve el nombre y el apellido de la tabla Clientes y el id de orden y la fecha de

la tabla de ordenes

FROM Clientes --(Clientes es la tabla de la izquierda o primer tabla) Para esto, usa la tabla de ordenes

LEFT JOIN ordenes on Clientes.idcliente = ordenes.idcliente -- y por medio un left join con la tabla ordenes trae esos campos CUANDO el id del cliente de la tabla de ordenes sea el mismo id del cliente en la tabla de Clientes

order by id_orden; -- ordena la consulta por el id de la orden

SELECT cli.nombre, cli.apellido, ord.id_orden, ord.fecha_orden

FROM Clientes cli -- La tabla Clientes usa 'cli' como alias

LEFT JOIN ordenes ord on cli.idcliente = ord.idcliente -- La tabla ordenes usa 'ord' como alias

order by id_orden;

Referencias

Para obtener más información sobre las uniones, consultar el siguiente enlace.

• Uniones en SQL Server
  

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
  

Modularización de consultas por medio de procedimientos almacenados en SQL Server

En SQL Server un procedimiento almacenado es un grupo de una o más instrucciones de Transact-SQL (Lenguaje de programación). Estos procedimientos aceptan parámetros de entrada y devuelven múltiples valores en forma de parámetros de salida al programa que llama al procedimiento, además contienen instrucciones de programación que realizan operaciones en la base de datos. También, estos procedimientos devuelven un valor de estado al programa que llamo al procedimiento para indicar éxito o fracaso (y el motivo del fracaso).

Gracias al control de flujo que ofrece SQL Server es que podemos usar los procedimientos almacenados. Como se describió, Transact-SQL tiene diversas instrucciones que nos permiten emplear estructuras de control tal como en los lenguajes de programación, es decir, podemos usar construcciones del lenguaje para modificar el flujo de ejecución de las instrucciones de un programa.

Los procedimientos almacenados tienen la ventaja de que permiten modularizar soluciones de software al encapsular el código en unidades manejables y con ello facilitar su reutilización. Además, con el uso de procedimientos almacenados los comandos de estos se ejecutan como un único lote de código, esto puede reducir significativamente el tráfico de red entre el servidor y el cliente, ya que solo se envía la llamada a través de la red para ejecutar un procedimiento determinado. Sin la encapsulación de código que proporciona un procedimiento, cada línea de código tendría que atravesar la red.

Creación de procedimientos almacenados

La sentencia CREATE PROCEDURE es la que nos permite la creación y persistencia de bloques de código, además, cuando se usa esta sentencia, el procedimiento se guarda en la carpeta Databases > NombreBaseDeDatos > Programmability > Stored procedures, aquí es donde podemos encontrar todos los bloques de código que se han guardado en la propia base de datos. La carpeta de procedimientos almacenados se muestra en la siguiente imagen.

Como ejemplo de código, vamos a suponer que tenemos una tabla de productos que tiene un campo cantidad y que deseamos actualizar los valores de este campo, para esto, podemos crear un procedimiento almacenado que nos va permitir llamarlo cada vez que lo necesitemos sin tener que volver a escribir el código en su interior. El código es el que se muestra en seguida.

CREATE PROCEDURE p_actualiza_stock

AS

BEGIN

    IF EXISTS(SELECT * FROM productos

            WHERE cantidad = 0)

        UPDATE 

            productos SET cantidad = 20

        WHERE 

            cantidad <= 5;

END

Con el código anterior se guarda el procedimiento y ahora solo falta ejecutarlo por medio de la sentencia EXEC, esto se muestra a continuación.

EXECUTE p_actualiza_stock;

Es importante mencionar que cada vez que un procedimiento se guarda y modifica en la base de datos, será necesario ejecutarlo o mandarlo a llamar por medio de la sentencia EXEC.

Actualización de procedimientos almacenados

Para actualizar o modificar un procedimiento que ya guardamos previamente, tenemos que usar la sentencia ALTER PROCEDURE. Como ejemplo, podemos modificar el procedimiento que describimos previamente, en este caso podemos agregar 10 unidades más a cada artículo que tenga 20 elementos, entonces el código es el siguiente.

ALTER PROCEDURE p_actualiza_stock

AS

BEGIN

    IF EXISTS(SELECT * FROM productos

            WHERE cantidad = 10)

        UPDATE 

            productos SET cantidad = 30

        WHERE 

            cantidad = 20;

END

De igual manera, la instrucción ALTER modifica y guarda el procedimiento en la base de datos en cuestión.

Borrar procedimientos almacenados

Para borrar procedimientos almacenados tenemos que usar la instrucción DROP PROCEDURE, entonces para borrar el procedimiento con el que hemos estado trabajando tenemos que usar el código que muestra a continuación.

DROP PROCEDURE p_actualiza_stock;

Con esto, el módulo de actualización de stock ya no estará disponible en la carpeta Stored Procedures de la base de datos.

Parámetros de entrada en procedimientos almacenados

Para usar parámetros o variables al interior de una procedimiento almacenado tenemos que declarar las variables antes del propio código de ejecución, es decir, antes de la clausula AS, recordemos que la declaración de variables tiene la sintaxis @nombreVar TipoDeDato = valor, además, si se usan varias variables tenemos que separarlas por comas. En el siguiente ejemplo se muestra un procedimiento almacenado que nos permite buscar un producto con determinados valores en cada campo. En este caso la tabla de productos tiene los campos nombre, precio y cantidad (existencia) en su estructura.

CREATE OR ALTER PROCEDURE p_buscar_producto

@producto VARCHAR(40) = 'Microcontrolador',

@precioProducto SMALLMONEY = 29.99,

@existencia INT = 35

AS

SELECT * FROM productos

WHERE

    nombre = @producto AND

    precio = @precioProducto AND

    cantidad = @existencia;

En esta ocasión se uso la palabra reservada ALTER en el encabezado del procedimiento almacenado, con esto podemos crear y/o modificar un procedimiento en el mismo lugar sin la necesidad de realizar estas operaciones por separado.

Referencias

En los siguientes enlaces se puede obtener más detalles técnicos sobre las sentencias de código usadas en este artículo.

• Control de flujo en SQL Server
• Sentencia CREATE PROCEDURE
  
• Sentencia EXECUTE
• Sentencia ALTER PROCEDURE
• Declaración de variables T-SQL

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
  

Creación de tablas dinámicas en Google Sheets

El espacio de trabajo de Google en sus hojas de cálculo (Google Sheets) ofrece en sus herramientas la posibilidad de trabajar con tablas dinámicas de manera similar a como lo hace Excel, con ellas también podemos resumir y analizar datos, a continuación se muestra un ejemplo paso a paso de cómo crear tablas dinámicas en esta herramienta.

Se puede considerar el siguiente ejemplo de una tabla de empleados de una empresa hipotética en Sheets (Clic en la imagen para ampliarla).

Ahora, para agregar la tabla dinámica nos dirigimos a la pestaña Insertar y en ella elegimos la opción de tabla dinámica que se muestra en la siguiente figura (Clic en la imagen para ampliarla).

Ya que se ha elegido la opción previa se va mostrar una ventana con el origen de datos que necesita la tabla dinámica así como un par de opciones para decidir donde colocar la nueva tabla, esto se puede observar en la siguiente figura.

Después, se va mostrar una sección con las mismas zonas que tiene Excel para trabajar las tablas dinámicas, es decir, filas, columnas, valores y filtros, en cada zona vamos a poder agregar la columna o campo que deseemos para poder realizar el análisis requerido. Véase la siguiente imagen.

Una configuración de tabla dinámica que se puede diseñar es el siguiente, colocar en la zona de valores los salarios de los empleados, en las filas sus nombres y en las columnas los respectivos departamentos, la tabla dinámica generada se muestra en la siguiente figura.

En cuanto a la zona de filtros se puede usar el estado activo o inactivo de los empleados para poder visualizar solo aquellos con un estado determinado.

Además, Sheets ofrece algunas sugerencias de tablas dinámicas que pueden ser de utilidad, estas se pueden observar en el editor de tablas, de esta manera podemos encontrar diversas opciones que nos podrían ayudar a analizar la información y que no habíamos tenido en cuenta, véase la siguiente imagen.

Otra de las ventajas de Google Sheets es la compatibilidad de los archivos con los de Microsoft Excel, de esta manera se pueden trabajar los proyectos en cualquiera de las plataformas de acuerdo a las necesidades que se tengan, así mismo Sheets se encuentra en una plataforma en línea o en la nube lo cual permite tener acceso a los archivos desde cualquier dispositivo con acceso a internet. También es posible colaborar con otros usuarios o miembros de un equipo en un mismo archivo. En resumen, esta herramienta nos da una alternativa a Excel que puede ser muy poderosa para manejar las bases de datos o proyectos que requieren el uso de una hoja de cálculo.

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