Contenido
Muchos procesos comerciales (e incluso negocios completos) en esta vida implican el cumplimiento de pedidos por parte de un número limitado de ejecutantes en un plazo determinado. La planificación en tales casos ocurre, como dicen, "desde el calendario" y, a menudo, es necesario transferir los eventos planificados en él (pedidos, reuniones, entregas) a Microsoft Excel, para un análisis posterior mediante fórmulas, tablas dinámicas, gráficos, etc.
Por supuesto, me gustaría implementar dicha transferencia no mediante una copia estúpida (que no es difícil), sino con la actualización automática de datos para que en el futuro todos los cambios realizados en el calendario y los nuevos pedidos se muestren sobre la marcha en Sobresalir. Puede implementar dicha importación en cuestión de minutos utilizando el complemento Power Query integrado en Microsoft Excel, a partir de la versión 2016 (para Excel 2010-2013, puede descargarse del sitio web de Microsoft e instalarse por separado desde el enlace) .
Supongamos que usamos el Google Calendar gratuito para la planificación, en el que, por conveniencia, creé un calendario separado (el botón con un signo más en la esquina inferior derecha al lado de Otros calendarios) con el título Actividades:. Aquí ingresamos todos los pedidos que deben completarse y entregarse a los clientes en sus direcciones:
Al hacer doble clic en cualquier pedido, puede ver o editar sus detalles:
Tenga en cuenta que:
- El nombre del evento es gerentequien cumple este pedido (Elena) y Número de orden
- Indicado dirección entrega
- La nota contiene (en líneas separadas, pero en cualquier orden) los parámetros del pedido: tipo de pago, monto, nombre del cliente, etc. en el formato Parámetro=Valor.
Para mayor claridad, las órdenes de cada gerente están resaltadas en su propio color, aunque esto no es necesario.
Paso 1. Obtén un enlace a Google Calendar
Primero necesitamos obtener un enlace web a nuestro calendario de pedidos. Para hacer esto, haga clic en el botón con tres puntos Calendario Opciones Trabajo junto al nombre del calendario y seleccione el comando Configuración y uso compartido:
En la ventana que se abre, puede, si lo desea, hacer público el calendario o abrir el acceso a él para usuarios individuales. También necesitamos un enlace para el acceso privado al calendario en formato iCal:
Paso 2. Cargue datos del calendario en Power Query
Ahora abre Excel y en la pestaña Datos (si tiene Excel 2010-2013, luego en la pestaña Power Query) elige un comando Desde Internet (Datos — De Internet). Luego pegue la ruta copiada en el calendario y haga clic en Aceptar.
iCal Power Query no reconoce el formato, pero es fácil de ayudar. Esencialmente, iCal es un archivo de texto sin formato con dos puntos como delimitador, y por dentro se parece a esto:
Entonces puede simplemente hacer clic con el botón derecho en el icono del archivo descargado y seleccionar el formato que tenga el significado más cercano CSV – y nuestros datos sobre todos los pedidos se cargarán en el editor de consultas de Power Query y se dividirán en dos columnas por dos puntos:
Si miras de cerca, puedes ver claramente que:
- La información sobre cada evento (pedido) se agrupa en un bloque que comienza con la palabra BEGIN y termina con END.
- Las fechas y horas de inicio y finalización se almacenan en cadenas etiquetadas como DTSTART y DTEND.
- La dirección de envío es UBICACIÓN.
- Nota de pedido – campo DESCRIPCIÓN.
- Nombre del evento (nombre del administrador y número de pedido): campo RESUMEN.
Queda por extraer esta información útil y transformarla en una tabla conveniente.
Paso 3. Convertir a vista normal
Para hacer esto, realice la siguiente cadena de acciones:
- Eliminemos las 7 líneas principales que no necesitamos antes del primer comando BEGIN Inicio — Eliminar filas — Eliminar filas superiores (Inicio — Eliminar filas — Eliminar filas superiores).
- Filtrar por columna Column1 líneas que contienen los campos que necesitamos: DTSTART, DTEND, DESCRIPTION, LOCATION y SUMMARY.
- En la pestaña Avanzado Agregar una columna escoger columna de índice (Agregar columna — Columna de índice)para agregar una columna de número de fila a nuestros datos.
- Justo ahí en la ficha. Agregar una columna elige un equipo columna condicional (Agregar columna — Columna condicional) y al principio de cada bloque (orden) mostramos el valor del índice:
- Rellene las celdas vacías en la columna resultante Bloquearhaciendo clic derecho en su título y seleccionando el comando Llenar hacia abajo (Llenar hacia abajo).
- Eliminar columna innecesaria Home.
- Seleccionar una columna Column1 y realizar una convolución de los datos de la columna Column2 usando el comando Transformar – Columna pivote (Transformar — Columna dinámica). Asegúrate de seleccionar en las opciones no agregar (No agregue)para que no se aplique ninguna función matemática a los datos:
- En la tabla bidimensional (cruz) resultante, borre las barras invertidas en la columna de dirección (haga clic con el botón derecho en el encabezado de la columna – Sustitución de valores) y elimine la columna innecesaria Bloquear.
- Para convertir el contenido de las columnas DTINICIO и DTEND en una fecha-hora completa, resaltándolos, seleccione en la pestaña Transformar – Fecha – Ejecutar análisis (Transformar — Fecha — Analizar). Luego corregimos el código en la barra de fórmulas reemplazando la función Fecha de on FechaHora.Desdepara no perder valores de tiempo:
- Luego, haciendo clic derecho en el encabezado, dividimos la columna DESCRIPCIÓN con parámetros de orden por separador – símbolo n, pero al mismo tiempo, en los parámetros, seleccionaremos la división en filas, y no en columnas:
- Una vez más, dividimos la columna resultante en dos separadas: el parámetro y el valor, pero por el signo igual.
- Seleccionar una columna DESCRIPCIÓN.1 realice la convolución, como hicimos antes, con el comando Transformar – Columna pivote (Transformar — Columna dinámica). La columna de valor en este caso será la columna con valores de parámetros: DESCRIPCIÓN.2 Asegúrese de seleccionar una función en los parámetros no agregar (No agregue):
- Queda por configurar los formatos para todas las columnas y cambiarles el nombre como desee. Y puede volver a cargar los resultados en Excel con el comando Inicio — Cerrar y cargar — Cerrar y cargar en… (Inicio — Cerrar y cargar — Cerrar y cargar a…)
Y aquí está nuestra lista de pedidos cargados en Excel desde Google Calendar:
En el futuro, a la hora de cambiar o añadir nuevos pedidos al calendario, solo bastará con actualizar nuestra petición con el comando Datos – Actualizar todo (Datos — Actualizar todo).
- Calendario de fábrica en Excel actualizado desde internet a través de Power Query
- Transformar una columna en una tabla
- Crear una base de datos en Excel