Consolidación y uso compartido de datos
Consolidación de datos diversos: descripción del ejemplo
Presentación del ejemplo
En informática, la consolidación permite agrupar datos procedentes de diferentes fuentes para obtener un informe estructurado.
En el ejemplo siguiente se consolidarán primero varios orígenes de datos en uno y, a continuación, se trabajará en la tabla consolidada para extraer información clave.
Se trata de un grupo inmobiliario compuesto por dos agencias separadas que se encuentran en Madrid y Sevilla. Aunque similar en su organización, la introducción de las transacciones actuales no se realiza de la misma manera para las dos agencias. Sin embargo, ambas usan un archivo de Excel que rastrea su actividad.
Por lo tanto, el objetivo será consolidar estas dos fuentes de datos en un único archivo que contenga toda la información. La agencia inmobiliaria también desea ofrecer una tabla resumen de su actividad.
En este ejemplo no se proporciona una interfaz de usuario. El resultado se presenta en forma de una fuente de datos consolidada.

Descripción de los libros
Este ejemplo se presenta con tres libros independientes:
-
El libro Immo-Madrid.xlsx contiene una hoja para la agencia de Madrid que incluye los siguientes datos:
Columna Excel |
Etiqueta |
Valor de ejemplo |
Columna A |
Fecha |
Fecha de realización de la operación. Se almacenará en el formato Timestamp, que está muy extendido en informática: es un contador numérico correspondiente al número de segundos transcurridos desde el 1 de enero de 1970. Por ejemplo: 1 de enero de 2022: 1641016966. Una de las ventajas de este formato es la facilidad de realizar la comparación de fechas, ya que basta con obtener la diferencia entre dos números. |
Columna B |
Agente |
Nombre del agente inmobiliario que gestiona la venta. |
Columna C |
Distrito |
Distritos (en caso de que el municipio los tenga). |
Columna D |
Población |
Madrid, Getafe... |
Columna E |
Código postal |
28002, 28030... |
Columna F |
Tipo de bien |
Casa, Apartamento, Loft, Villa. |
Columna G |
Precio de venta inicial |
100 000 (sin unidad y sin decimales), vacío si se alquila. |
Columna H |
Precio de venta real |
120 000 (sin unidad y sin decimales), vacío si se alquila. |
Columna I |
Importe honorarios |
3000 (sin unidades y sin decimales). |
Columna J |
Número de visitas |
4 (sin unidades y sin decimales). |
Columna K |
Número de ofertas |
2 (sin unidades y sin decimales). |
Columna L |
Precio inicial del alquiler |
3120 (sin unidades y sin decimales), vacío si se vende. |
Columna M |
Precio real del alquiler |
3000 (sin unidades y sin decimales), vacío si se vende. |
Columna N |
Precio por m² |
5123,2 por m²: precio por metro cuadrado con decimales potenciales y una unidad de medida que es siempre el precio por metro cuadrado. |
Columna O |
Operación realizada |
VERDADERO / FALSO. |
Columna P |
Duración de la operación |
25: duración en días entre la fecha de publicación de la oferta y la firma. |
-
El libro Immo_Sevilla.xlsx contiene una hoja para la agencia de Sevilla que incluye los siguientes datos:
Columna Excel |
Etiqueta |
Valor de ejemplo |
Columna A |
Agente inmobiliario |
Nombre del agente inmobiliario. |
Columna B |
Venta/Alquiler |
V en venta o A en alquiler. |
Columna C |
Honorarios en % |
4,17 %: cantidad porcentual con 2 decimales posibles. |
Columna D |
Precio... |
Consolidación de varios datos: conceptos del curso
Operación con hojas y libros
La operación con hojas y libros consiste en operar con variables de objetos.
Administración de la aplicación Excel
La importación consistirá en abrir libros de Excel (Sevilla y Madrid) y luego seleccionar las hojas necesarias. Toda esta información se almacenará en variables de tipo objeto.
Dim ExcApp As Excel.Application 'Variable de administración de la aplicación
Dim WB As Excel.Workbook 'Variable libro
Dim WS As Excel.Worksheet 'Variable hoja de cálculo
La administración de aplicaciones no es necesaria si la aplicación ya está abierta. Por otro lado, el mismo mecanismo se aplica para trabajar con otras aplicaciones, como PowerPoint.
Seleccionar y abrir un libro de Excel
Método GetOpenFileName
Para seleccionar el archivo de Excel, puede utilizar el método GetOpenFileName de la clase Application, que abre una ventana de selección de archivos. Este método devuelve la ruta de acceso de la aplicación seleccionada y se puede utilizar para abrir el archivo seleccionado.
Nombre_Archivo = Application.GetOpenFilename("Archivos de Excel (*.xlsm),
*.xlsm") 'filtro en los archivos de Excel
If Nombre_Archivo <> False Then
'Abrir archivo
Else
Msgbox ("Archivo no seleccionado")
End if
Método FileDialog
Sin embargo, el ejemplo propuesto utilizará otro método más completo: Application.FileDialog, que permite cualquier tipo de intercambio con directorios y archivos:
Dependiendo del argumento asociado con el método FileDialog, el cuadro de diálogo tendrá una forma diferente:
-
Argumento msoFileDialogFilePicker: seleccionar archivo.
-
Argumento msoFileDialogFolderPicker: seleccionar carpeta.
-
Argumento msoFileDialogOpen: abrir archivo.
-
Argumento msoFileDialogSaveAs: guardar archivo.
Por ejemplo:
With Application.FileDialog(msoFileDialogFolderPicker)
'Establecer el cuadro de diálogo para seleccionar un directorio.
.Title = "Seleccionar el directorio" 'Agregar un título al cuadro
de diálogo
.Show 'Visualizar la ventana
If .SelectedItems.Count > 0 Then
Msgbox .SelectedItems(1) 'Visualizar el primer elemento seleccionado.
End If
Este método ofrece más posibilidades;...
Consolidación de varios datos: realización del ejemplo
Abra el archivo Enunciado_6-ABC.xlsm que contendrá los datos consolidados. Los archivos Inmo_Madrid.xlsx e Inmo_Sevilla.xlsx se utilizarán en la importación, pero no se abrirán en el ejemplo.
Estructura del código
El código se realizará dentro de un único procedimiento que se utilizará una sola vez para recuperar los datos de ambas hojas.
Por lo tanto, es necesario crear un procedimiento que almacenará todo el procesamiento y se llamará InsercionDatos.
Inserte un módulo e introduzca las siguientes líneas de código:
Option Explicit
Sub InsercionDatos
'El código se insertará aquí
End sub
Declaración de variables de hoja y libro
Para realizar este ejemplo, primero tendrá que crear variables de objeto para almacenar y trabajar con hojas y libros.
Será necesario tener seis variables:
-
Tres variables de tipo Libro:
-
Archivo Consolidado.
-
Archivo Sevilla.
-
Archivo Madrid.
-
Tres variables de tipo Hoja:
-
Hoja Consolidada: dentro del archivo consolidado Enunciado_6-ABC.xlsx, hoja que contiene los datos consolidados.
-
Hoja Sevilla: dentro del archivo Inmo_Sevilla.xlsx, hoja que contiene los datos del archivo Sevilla.
-
Hoja Madrid: dentro del archivo Immo_Madrid.xlsx, hoja que contiene los datos del archivo Madrid.
Declare las variables de la siguiente manera:
Definir las variables
Dim WBSevilla As Excel.Workbook
Dim WBMadrid As Excel.Workbook
Dim WBFinal As Excel.Workbook
Dim WSSevilla As Excel.Worksheet
Dim WSMadrid As Excel.Worksheet
Dim WSFinal As Excel.Worksheet
La declaración de variables se puede agrupar en una sola fila por tipo de variable. Los nombres de las variables deben estar separados por una coma:
Dim WBFinal, WBSevilla, WBMadrid As Excel.Workbook
Dim WSFinal, WSSevilla, WSMadrid As Excel.Worksheet
Para asignar valores a las variables, es necesario utilizar la palabra clave Set, que se utiliza para asignar una referencia al objeto. En este caso, WBFinal hará referencia al libro de trabajo actual: ThisWorkbook.
Finalmente, la hoja WSFinal será la hoja Datos de WBFinal.
Introduzca el siguiente código:
Set WBFinal = ThisWorkbook
Set WSFinal = WBFinal.Sheets("Datos")
Configuración del cuadro de diálogo para abrir el archivo
El primer paso es abrir un cuadro de diálogo para seleccionar los dos archivos que desea importar.
Para definir las características del cuadro de diálogo, llame al método FileDialog de la clase Application con el argumento msoFileDialogFilePicker para especificar que se trata de una selección de archivo.
En primer lugar, debe crear el objeto correspondiente a este cuadro de diálogo y, a continuación, definir sus características.
Almacenaremos el archivo de Sevilla como una variable o, más concretamente, asignaremos a la variable WBSevilla una referencia a la instancia de Excel.Workbook.
Para simplificar el código, utilice la estructura With ... End with para no reescribir sistemáticamente el código del cuadro de diálogo:
With Application.FileDialog(msoFileDialogFilePicker)
End with
A continuación, defina las características del cuadro de diálogo como su título...
Compartir datos: descripción del ejemplo
Presentación del ejemplo
El objetivo de este ejemplo es proponer una solución que permita a ambas agencias inmobiliarias introducir datos. El problema es que este archivo de Excel no está destinado a ser mantenido por una sola agencia, sino que debe ser accesible y modificable por ambas agencias; quizás, al final, por una multitud de ellas.
Presentación de los libros y herramientas utilizadas
A efectos de este ejemplo, se utilizará el archivo generado en la primera parte de este capítulo, ya que contiene los datos introducidos en las agencias, aunque se le han aplicado algunas mejoras, incluida una pestaña Configuración. El archivo en el que nos vamos a basar es Enunciado_6-DEF.xlsm.
Este ejemplo requerirá una cuenta en Microsoft OneDrive (https://onedrive.live.com/) y, para la última parte, es necesario tener Outlook 2021 u Outlook Microsoft 365 instalado en su ordenador. Si no dispone de Outlook, el código se puede adaptar fácilmente para otras soluciones.
La cuenta de OneDrive le permitirá crear un formulario de Excel en línea cuyos valores se almacenarán dentro de un archivo.
La aplicación Outlook le permitirá enviar un correo electrónico a partir de los datos contenidos en Excel.
Funcionalidades
Las funcionalidades que se abordarán son:
-
Crear un formulario de introducción automática...
Intercambio de datos: conceptos del curso
Formulario de tabla
La opción Formulario es una característica de Excel para agregar/editar/eliminar datos en una serie de datos. Normalmente, se usa con tablas, pero esta funcionalidad también se puede usar con una simple serie de datos.
Ventajas
La ventaja de esta funcionalidad es que genera un formulario de entrada y edición de una manera sencilla, simplemente con un clic. La edición, la adición y la eliminación son de fácil acceso e incluso es posible buscar un elemento.
Desventajas
La principal desventaja es la falta de posibilidades de ayuda a la hora de introducir datos.
Es imposible calificar los datos que se han de insertar. Además, si ha aplicado restricciones a los datos (pestaña Datos - Validación de datos), es posible que no pueda insertar los datos con el formulario. De hecho, si el valor introducido no se corresponde con el valor esperado, no se insertará toda la fila.
¿Cómo insertar el formulario?
Abra el archivo EjemploCurso_Capítulo_6.xlsx.
Encontrará en la hoja dos tablas idénticas en A1:C9 y H1:J9. Cada tabla tiene tres columnas donde aparecen los nombres, el sexo y el nombre del equipo.
La primera tabla (rango A1:C9) representa un rango de datos, no está declarada como tabla de Excel. Ninguna casilla conlleva ninguna restricción. La segunda tabla (rango H1: J9) es una tabla de Excel. Las columnas relativas al sexo y al nombre del equipo son obligatorias: el usuario debe elegir uno de los valores.
En la pestaña Archivo, elija Opciones.
Haga clic en Personalizar cinta de opciones; luego, en Comandos disponibles en, escoja Todos los comandos.
En la lista de la izquierda, seleccione Formulario.
En la lista de la derecha, seleccione Datos y haga clic en Nuevo grupo.

Haga clic en Agregar>> para insertar el botón Formulario en este grupo nuevo de la pestaña Datos.
Haga clic en Cambiar nombre para llamar al grupo Formulario de datos, como se muestra a continuación.

Haga clic en Aceptar dos veces para cerrar las ventanas Cambiar nombre y Opciones de Excel.
Obtendrá la siguiente vista:

Seleccione la celda A1 en la tabla de la izquierda y, a continuación, en la pestaña Datos, haga clic en Formulario.
Aparecerá la siguiente ventana; haga clic en Aceptar para mostrar el formulario.

El formulario se muestra de la siguiente manera:

1. El botón Nuevo crea un registro nuevo vacío.
2. El botón Eliminar elimina el registro actual.
3. Los botones Buscar anterio/Buscar siguien permiten navegar a través de los registros.
4. El botón Criterios permite introducir criterios para mostrar solo los registros que cumplen con estos criterios.
Como prueba, puede agregar/editar/eliminar registros. A continuación, haga clic en Cerrar.
A continuación, haga clic en la celda H1 y después, en la pestaña Datos haga clic en Formulario.
Aquí también se muestra el formulario para la tabla nueva.
Haga clic en Nuevo y, a continuación, escriba los siguientes valores:
-
Nombre: Juan
-
Sexo: V
-
Equipo: Beta
Vuelva a hacer clic en Nuevo.
Se ha aplicado una restricción a la tabla: solo se aceptan los valores "Hombre" y "Mujer" para...
Uso compartido de datos: realización del ejemplo
Primero, abra el archivo Enunciado_6-DEF.xlsm.
Crear un formulario de tipo autocompletar para facilitar la introducción de datos
El formulario de entrada permitirá la introducción de datos en una tabla sin tener que usar un formulario VBA.
Mostrar el formulario
Si no ha agregado el botón Formulario, agréguelo en la pestaña Datos (consulte la sección Formulario de tabla en la sección Intercambio de datos: conceptos del curso, en este capítulo).
Seleccione la celda A1.
En la pestaña Datos, haga clic en el botón Formulario.

El formulario aparece de la siguiente manera:

Modificar un dato
El agente Benito le informa de que finalmente no se realizó una venta después de un problema de última hora en la concesión del préstamo. Sabe que la oferta apareció el 15/12/2021.
Haga clic en Criterios e introduzca 15/12/2021 en el cuadro Fecha de publicación de la oferta.

Pulse la tecla Intro y luego haga clic en los botones Buscar anterio y Buscar siguien para acceder a la operación realizada por el agente Benito el 15/12/2021.

Cambie el valor del campo Operación realizada de VERDADERO a FALSO y haga clic en Cerrar.

Compruebe los datos con el valor de la celda P1130: ha cambiado a FALSO.
Búsqueda de datos
El agente Cruz está buscando una de sus ventas en la que se ha colado un error: el cliente disponía de parking. Recuerda que fue la venta de una villa de más de 350 000 €. Utilice la herramienta para realizar la búsqueda.
Vuelva a mostrar el formulario haciendo clic en el botón Formulario de la pestaña Datos. Aparece el formulario.
Haga clic en el botón Criterios.
Introduzca la información para encontrar estas dos líneas:

Después de verificar que ha encontrado el registro, cambie el valor del campo Parking de FALSO a VERDADERO y haga clic en el botón Cerrar.

Crear una encuesta compartida a través de OneDrive y entregarla
Como el formulario permite introducir datos en local, la encuesta parece satisfacer en todos los puntos la necesidad de compartir el mismo formulario de datos con las agencias. La encuesta permitirá a las agencias tener un enlace web para introducir sus datos, que se consolidarán en el mismo archivo.
Crear una cuenta de OneDrive
Si no lo ha hecho para este curso, cree una cuenta de OneDrive en el sitio: https://onedrive.live.com/

La interfaz de OneDrive aparece así:

Crear la encuesta
Haga clic en el menú Nuevo y elija Encuesta sobre formularios:

Aparece un formulario sin título que le ofrece crear su encuesta de inmediato.

En la pestaña Preguntas, comience introduciendo un título para su encuesta. Aquí elegiremos Operaciones de las agencias.
A continuación, introduzca la descripción para la encuesta; por ejemplo, Por favor, introduzca en el formulario siguiente las operaciones realizadas durante su actividad.
El resultado es el siguiente:

Haga clic en el botón Agregar nuevo y, a continuación, en Opción para elegir el tipo de pregunta que desea hacer.

Los detalles de la pregunta de tipo Opción se muestran de la siguiente manera:...