Contidos
Moitos procesos comerciais (e incluso negocios enteiros) nesta vida implican o cumprimento de pedidos por un número limitado de artistas nun prazo determinado. A planificación nestes casos prodúcese, como din, "desde o calendario" e moitas veces hai que transferir os eventos previstos nel (pedidos, reunións, entregas) a Microsoft Excel - para unha análise posterior mediante fórmulas, táboas dinámicas, gráficos, etc. etc.
Por suposto, gustaríame implementar tal transferencia non mediante unha copia estúpida (o que non é difícil), senón cunha actualización automática de datos para que no futuro todos os cambios realizados no calendario e os novos pedidos se mostren en directo. Excel. Podes implementar tal importación en cuestión de minutos usando o complemento Power Query integrado en Microsoft Excel, a partir da versión 2016 (para Excel 2010-2013, pódese descargar desde o sitio web de Microsoft e instalarse por separado desde a ligazón) .
Supoñamos que usamos o Google Calendar gratuíto para a planificación, no que eu, por comodidade, creei un calendario separado (o botón cun signo máis na esquina inferior dereita xunto a Outros calendarios) co título Traballar. Aquí introducimos todos os pedidos que teñan que ser completados e entregados aos clientes nos seus enderezos:
Facendo dobre clic en calquera pedido, podes ver ou editar os seus detalles:
Teña en conta que:
- O nome do evento é xerentequen cumpre esta orde (Elena) e orderNumber
- Indicado enderezo entrega
- A nota contén (en liñas separadas, pero en calquera orde) os parámetros do pedido: tipo de pago, importe, nome do cliente, etc. no formato Parámetro=Valor.
Para máis claridade, as ordes de cada xestor destacan na súa propia cor, aínda que non é necesario.
Paso 1. Obtén unha ligazón a Google Calendar
Primeiro necesitamos obter unha ligazón web ao noso calendario de pedidos. Para iso, fai clic no botón con tres puntos Opcións do calendario traballo xunto ao nome do calendario e selecciona o comando Configuración e compartición:
Na xanela que se abre, pode, se o desexa, facer público o calendario ou abrir o acceso a el para usuarios individuais. Tamén necesitamos unha ligazón para o acceso privado ao calendario en formato iCal:
Paso 2. Cargue os datos do calendario en Power Query
Agora abra Excel e na pestana Data (Se tes Excel 2010-2013, na pestana Consulta de enerxía) escolla un comando Desde Internet (Datos: de Internet). A continuación, pega o camiño copiado no calendario e fai clic en Aceptar.
O iCal Power Query non recoñece o formato, pero é fácil axudar. Esencialmente, iCal é un ficheiro de texto simple con dous puntos como delimitador, e no seu interior parece algo así:
Polo tanto, podes facer clic co botón dereito sobre a icona do ficheiro descargado e seleccionar o formato que teña o significado máis próximo CSV – e os nosos datos sobre todos os pedidos cargaranse no editor de consultas de Power Query e dividiranse en dúas columnas por dous puntos:
Se miras detidamente, podes ver claramente que:
- A información sobre cada evento (orde) agrúpase nun bloque que comeza coa palabra BEGIN e remata por FIN.
- As datas de inicio e de finalización almacénanse en cadeas etiquetadas como DTSTART e DTEND.
- O enderezo de envío é LOCATION.
- Nota de pedido – Campo DESCRICIÓN.
- Nome do evento (nome do xestor e número de pedido) — Campo RESUMO.
Queda por extraer esta información útil e transformala nunha táboa conveniente.
Paso 3. Converte á vista normal
Para iso, realice a seguinte cadea de accións:
- Imos eliminar as 7 liñas principais que non necesitamos antes do primeiro comando BEGIN Inicio — Eliminar filas — Eliminar filas principais (Inicio — Eliminar filas — Eliminar filas superiores).
- Filtrar por columna Column1 liñas que conteñen os campos que precisamos: DTSTART, DTEND, DESCRIPCIÓN, LOCALIZACIÓN e RESUMO.
- Na pestana Avanzado Engadindo unha columna escoller Columna de índice (Engadir columna — Columna de índice)para engadir unha columna de número de fila aos nosos datos.
- Alí mesmo na pestana. Engadindo unha columna escolle un equipo Columna condicional (Engadir columna — Columna condicional) e ao comezo de cada bloque (orde) mostramos o valor do índice:
- Encha as celas baleiras na columna resultante Bloquearfacendo clic co botón dereito sobre o seu título e seleccionando o comando Encher - Abaixo (Encher - Abaixo).
- Elimina a columna innecesaria Índice.
- Seleccione unha columna Column1 e realiza unha convolución dos datos da columna Column2 usando o comando Transformar: columna pivote (Transformar — Columna pivote). Asegúrese de seleccionar nas opcións Non agregar (Non agregar)para que non se aplique ningunha función matemática aos datos:
- Na táboa bidimensional (cruz) resultante, borre as barras inclinadas invertidas na columna de enderezos (faga clic co botón dereito na cabeceira da columna - Substitución de valores) e elimine a columna innecesaria Bloquear.
- Para xirar o contido das columnas DTSTART и DTEND nunha data-hora completa, destacándoos, seleccione na pestana Transformación - Data - Análise de execución (Transformar — Data — Analizar). Despois corriximos o código na barra de fórmulas substituíndo a función Data.Desde on DataHora.Desdepara non perder valores de tempo:
- Despois, facendo clic co botón dereito sobre a cabeceira, dividimos a columna DESCRICIÓN con parámetros de orde por separador – símbolo n, pero ao mesmo tempo, nos parámetros, seleccionaremos a división en filas, e non en columnas:
- Unha vez máis, dividimos a columna resultante en dúas separadas: o parámetro e o valor, pero polo signo de igual.
- Selección dunha columna DESCRICIÓN.1 realizar a circunvolución, como fixemos anteriormente, co comando Transformar: columna pivote (Transformar — Columna pivote). A columna de valor neste caso será a columna cos valores dos parámetros − DESCRICIÓN.2 Asegúrese de seleccionar unha función nos parámetros Non agregar (Non agregar):
- Queda por establecer os formatos para todas as columnas e renomealas como desexe. E pode cargar os resultados de novo a Excel co comando Inicio — Pechar e cargar — Pechar e cargar en… (Inicio — Pechar e cargar — Pechar e cargar en…)
E aquí está a nosa lista de pedidos cargados en Excel desde Google Calendar:
No futuro, ao cambiar ou engadir novos pedidos ao calendario, só será suficiente actualizar a nosa solicitude co comando Datos - Actualizar todo (Datos — Actualizar todo).
- Calendario de fábrica en Excel actualizado desde Internet a través de Power Query
- Transformar unha columna nunha táboa
- Crear unha base de datos en Excel