Las consultas en Excel 365 E-Book

José Mauricio Flores Castillo

• Herausgeber: Marcombo
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Spanisch
• Veröffentlichungsjahr: 2023

Seitenzahl: 161

Beschreibung

Las consultas en Excel son un gran acierto en el desarrollo de herramientas para aumentar la utilidad del programa y para reducir tiempos a la hora de importar y transformar datos. Sin embargo, la naturaleza del trabajo de las consultas hace que las funciones trabajen con datos duros. Hacer que un sistema codifique las funciones en datos duros plantea ciertos retos ya que, si algo cambia, la consulta puede fallar en alguna de sus funciones y dejar de trabajar, lo que supondría modificar los datos duros de las funciones. Si desea adentrarse en el mundo de las consultas en Excel para resolver con su interfaz gráfica la mayoría de los casos que se indican a continuación y no quedarse atascado en el error, este libro es el indicado. •Su consulta ¿falla al modificar el nombre a una de las columnas de datos a importar? •Su consulta ¿es muy compleja porque los datos a importar tienen diferentes ubicaciones en los renglones? •Su consulta ¿requiere renombrar varias columnas y esto se vuelve complicado? •Su consulta ¿tarda mucho en ejecutarse? Solventar este tipo de situaciones requiere la creación de funciones dinámicas y consultas abiertas a los cambios de los datos que los usuarios puedan realizar. En este libro encontrará las técnicas para solucionar estos y muchos más casos, y así lograr hacer variaciones dentro de las consultas y que estas se vuelvan más sólidas.

Leseprobe

Las consultas en Excel 365

Soluciones dinámicasa problemas cotidianos

José Mauricio Flores Castillo

Las consultas en Excel 365

Soluciones dinámicasa problemas cotidianos

José Mauricio Flores Castillo

Las consultas en Excel 365Soluciones dinámicas a problemas cotidianos

José Mauricio Flores Castillo

Derechos reservados © Alfaomega Grupo Editor, S.A. de C.V., México

Primera edición: 2023

ISBN: 978-607-538-943-1

Primera edición: MARCOMBO, S.L. 2023

© 2023 MARCOMBO, S.L.

www.marcombo.com

Cualquier forma de reproducción, distribución, comunicación pública o transformación de esta obra solo puede ser realizada con la autorización de sus titulares, salvo excepción prevista por la ley. Diríjase a CEDRO (Centro Español de Derechos Reprográficos, www.cedro.org) si necesita fotocopiar o escanear algún fragmento de esta obra

ISBN del libro en papel: 978-84-267- 3690-1

ISBN del libro electrónico: 978-84-267-3707-6

Producción del ePub: booqlab

Al señor de los 1000 nombres, que me ha permitido vivir esta experiencia de vida rodeado de tantas bendiciones.

A Norma, gracias por encontrarnos y atreverte a ser mi maga con sonrisa dorada.

Contenido

Acerca del autor

Introducción

A quien va dirigido este libro

Queremos saber de ti

Capítulo 1Conceptos básicos

Capítulo 2Ejemplos útiles con un mínimo de lenguaje M

Crear ruta dinámica a un folder

Filtrar registros solo del mes en curso

Dinamizar con múltiples encabezados

Separar datos que vienen en una sola columna

Dinamizar varias hojas dinámicamente

Ordenación personalizada

Ordenación avanzada

Agregar renglón en blanco

Capítulo 3Ejemplos avanzados con programación

Ignorar mayúsculas y minúsculas

Renombrar columnas dinámicamente

Combinar hojas con diferente posición de tablas

Generar fechas entre día inicial y final

Datos en los títulos del informe

Acumulados

Simular la función sumar.si.conjunto

Separar datos que vienen en una sola columna con diferentes renglones

Obtener siempre la última columna

Agrupar por, avanzado

Dividir celdas con comas dinámicamente

Capítulo 4Trucos, tips y otras cosas muy útiles

Atajos dentro del Editor de las consultas

Obtener todas las funciones dentro de una consulta

Documentar los pasos aplicados

Desactivar el paso aplicado Tipo cambiado

Dependencias de las consultas

El paso aplicado Navegación

Proteger consultas

Copiar una consulta a un libro diferente

Opciones del Editor avanzado para mejorar la visualización

Cómo abrir una instancia de Excel con una consulta abierta

Herramienta Elegir columnas e Ir a columna

Acerca del autor

José Mauricio Flores Castillo

Estudió en la Escuela de Ingeniería de la Universidad La Salle, ha sido docente en el área de sistemas de cómputo en todos los niveles y ha dado cursos de capacitación (formación) en diversas empresas privadas y gubernamentales.

Desde 1993 empezó como capacitador externo de la Comisión Nacional Bancaria y de Valores en temas de Office, que en ese entonces eran programas por separado y generó gran prestigio por los cursos que se impartían en aquel entonces.

Desde ese entonces, su experiencia como instructor ha ido creciendo, y ya lleva más de 28 años dedicado a mejorar metodologías de enseñanza y a estar siempre actualizado en las últimas versiones de Microsoft Office para poder transmitir los conocimientos a sus alumnos. A la par con la capacitación desarrolla soluciones a medida con macros de Excel, las cuales han sido implantadas en empresas de muy diversas áreas, como la financiera, la textil y la bancaria, entre otras.

Con el auge del desarrollo y la colaboración en la nube y el cambio de Microsoft 365 a su ecosistema de aplicaciones, integra nuevos desarrollos colaborativos en diferentes empresas para que sus sistemas tengan la disponibilidad y movilidad necesarias, en tiempo real, para que la productividad de las empresas siempre siga adelante.

Trabaja no solo con empresas, sino también para personas que trabajan por su cuenta y que han recurrido a la optimización y automatización de hojas de cálculo para poder concentrar su energía y tiempo en generar negocios y no en pensar cómo trabajar las hojas de cálculo.

En la actualidad asesora a empresas, imparte cursos y desarrolla sistemas para personas que trabajan como asesores y que requieren el aspecto técnico a medida sin que tengan que invertir más dinero en aplicaciones extras. Por supuesto, sigue escribiendo libros con su casa, Alfaomega Grupo Editor, y coeditando con Marcombo.

Cuenta con un canal en YouTube (https://www.youtube.com/user/dudasoffice), donde sube vídeos de tips, preguntas y consejos acerca de Office donde cualquier persona que le escriba con una duda, él responde con un vídeo para aclarar cualquier inquietud.

Sin olvidar el lado humano, es Reiki Master y da terapias de Reiki desde hace muchos años para ayudar a las personas con esta técnica de relajación y los domingos hace voluntariado de terapias Mey en los Viveros de Coyoacán, puerta 3.

Entre otros, es autor de los libros:

•    Power Query para Excel 365 / 2021

•    Macros, el recurso máximo. Excel 2019 – 365

•    Tablas dinámicas. Optimice su trabajo. Excel

•    Las consultas en Excel 365

•    Tablas dinámicas y funciones

•    Macros con Excel 2013

•    Macros con Excel. Automatiza tu trabajo

•    Tablas dinámicas. Funciones, tablas y base de datos

Todos publicados por Alfaomega Grupo Editor y coeditados por Marcombo. Si usted quiere contactar con el autor, puede hacerlo en el correo: [email protected], y con gusto le responderá.

Introducción

Estimado lector/lectora, si bien es cierto que la interfaz gráfica de las Consultas resuelven el 90 % de nuestros casos para importar información de diferentes fuentes a Excel, existen ocasiones en que nuestras consultas quedan lejanas, ya que es difícil lograr nuestros objetivos con esta forma de trabajo.

Hay una gran variedad de ocasiones en que necesitamos hacer 3 o 4 pasos para lograr nuestro objetivo haciendo que la consulta se vuelva más lenta y difícil de revisar y controlar, debido a todo esto, es mejor aprender algo del poderoso Lenguaje M, que nos permite desarrollar consultas con un mayor control de estas, reduciendo en pasos y rendimiento las consultas.

El lenguaje M, a pesar de que su inteligente desarrollo nos permite unir pasos continuos que son repetitivos en algunas líneas de código, es muy recomendable que se entienda cómo usarlo para poder optimizar su uso, haciendo cosas que, por un lado, no se pueden lograr simplemente con la interfaz gráfica, y por el otro lado nos permite tener los pasos aplicados en un tamaño considerable y manejable para que estas se ejecuten de la manera más eficiente.

Existen tareas comunes que nos pueden obligar a elaborar muchos pasos aplicados para la resolución de estos, pero con un poco de pericia podemos hacer que estas tareas complicadas sean más sencillas con el manejo del lenguaje M.

A pesar de lo poderoso que puede llegar a ser el lenguaje M, también hay que tener en consideración que tiene ciertas limitaciones en cuanto a la codificación de los pasos aplicados se refiere, y esto debido a que, cuando se genera un paso aplicado, siempre usa los datos duros para poder referirse a los encabezados de las columnas a trabajar. Esto tiene su relevancia cuando necesitamos que las acciones usadas sean dinámicas, y para hacer esto es necesario recurrir al uso del editor avanzado y cambiar el código utilizando el lenguaje M.

En este libro encontrará ejemplos reales que se me han presentado en diferentes empresas y en otros casos se mostrarán ejercicios de ejemplos de preguntas que mis lectores me han hecho llegar a mi correo y que me han parecido interesantes presentarlas en este libro. En cada ejemplo se da una descripción del caso y la lógica que usé para resolverlo, en algunos casos se usará mucho el lenguaje M y en otros la pura interfaz gráfica, lo que hay que resaltar es la lógica usada, aunque siempre tenga en mente que esta es solo una forma de resolver el caso, usted puede hacerlo de otra manera.

¡¡¡Gracias!!!

A quien va dirigido este libro

Este libro está dirigido a usuarios de las consultas que tengan un conocimiento básico o avanzado, ya que veremos ciertas técnicas que les ayudarán a elevar su nivel de destreza con esta herramienta para importar y transformar datos y llevarlos a Excel de una forma estructurada para su análisis.

Si usted, lector/lectora, se encuentra con problemas cotidianos donde su consulta falla porque los datos de origen no son consistentes y tiene que realizar demasiados pasos para llegar a su objetivo, este libro le ayudará a usar el lenguaje M para resolver estos temas cotidianos y hacer que las consultas sean más eficientes y dinámicas.

Queremos saber de ti

Estimado/estimada lector/lectora, tenemos un gran compromiso contigo y sabemos en Alfaomega y Marcombo que tienes mucho que enseñarnos, así que para nosotros es de suma importancia saber de ti, qué piensas de este libro. Coméntanos para poder seguir ofreciéndote libros con valor, libros que reflejen la experiencia de los autores para transmitirte los conocimientos a ti, que los necesitas con la calidad que mereces.

Puedes compartir cualquier comentario escribiendo a Alfaomega Grupo Editor, a Marcombo o directamente al autor del libro a su correo [email protected].

Gracias.

Material web

En www.marcombo.info, con el código EXCEL23, encontrará los archivos para seguir los ejemplos que se desarrollan en la obra.

CAPÍTULO 1CONCEPTOS BÁSICOS

Aquí vamos a clasificar los conceptos que necesitan para entender los siguientes casos, ya que es importante tenerlos bien definidos y saber qué hacen dentro de una consulta.

DE LA CINTA DE OPCIONES

Origen de datos de una consulta: las consultas pueden importar datos de diversas fuentes y además una gran variedad de tipos de datos. Debemos tener en cuenta que estas opciones pueden variar dependiendo de la versión de Office que esté usando. Algunos de los formatos y fuentes más usados que se encuentran en la Cinta de opciones son los siguientes:

Datos> Obtener y transformar datos> Obtener datos

En la Figura 1.1 se muestra esta sección:

Figura 1.1. Sección Obtener y transformar junto con las opciones de las Consultas y conexiones.

De un archivo

De un libro de Excel: en esta opción podemos importar cualquier libro de Excel y además podemos seleccionar cualquier hora, tabla, nombre de rango o el libro entero para trabajar.

De texto/CSV: nos permite importar archivos de texto o con formato CSV

De XML: son las siglas de Extensible Markup Language, que podemos traducir como Lenguaje de Marcas Extensibles, aunque realmente es un metalenguaje. Normalmente, los archivos XML contienen información de cualquier tipo, ya sea información sensible o información de cualquier ámbito. Los archivos XML se componen de etiquetas que nos aportan datos e información que queremos procesar. Estas etiquetas pueden estar de forma individual o anidadas.

De JSON: que es la abreviatura de la notación de objetos de JavaScript (JavaScript Object Notation), y no es más que uno de tantos textos simples que se crearon como un subconjunto de JavaScript, pero dada su versatilidad, rápidamente fue considerado como un lenguaje independiente, situándose como una alternativa al clásico XML.

De una carpeta: esta opción nos permite importar todos los archivos que contenga una carpeta, ya sea dentro de nuestro equipo, de OneDrive o SharePoint.

De una base de datos

De una base de datos de Access: nos permite importar datos de Microsoft Access.

De otras fuentes

De una tabla o rango: importa los datos dentro de Excel que estén en formato tabla o los datos que tengan un nombre de rango definido.

De la web: nos permite importar de una página de internet las tablas que contenga dicha página, habrá algunas páginas que pidan una autentificación.

Consulta en blanco: siendo la forma más simple de una consulta, nos muestra una consulta totalmente en blanco para empezar a crearla desde cero.

DENTRO DEL EDITOR DE CONSULTAS, EN EL PANEL IZQUIERDO LLAMADO CONSULTAS

Panel de consultas: en este panel situado en el lado izquierdo de la pantalla se nos muestran todas las consultas que existen en este libro, además de poder realizar acciones para el manejo de estas. Estas acciones son ejecutadas con el botón derecho del ratón, si el clic derecho del ratón es sobre el nombre de una consulta, veremos las siguientes opciones:

Copiar: nos permite enviar la consulta al portapapeles.

Pegar: si hay alguna consulta en el portapapeles, la pegará en este archivo.

Eliminar: borra la consulta seleccionada.

Cambiar nombre: permite renombrar la consulta seleccionada.

Duplicar: hace una copia idéntica de la consulta seleccionada.

Referencia: hace una referencia a la consulta seleccionada, es decir, la consulta creada apuntará a la consulta seleccionada.

Mover a un grupo: puede mover la consulta o grupo seleccionado a otro grupo.

Subir: mueve la consulta arriba.

Bajar: mueve la consulta abajo.

Crear función: convierte la consulta seleccionada en una función.

Convertir en parámetro: convierte la consulta seleccionada en un parámetro. Para que esta opción trabaje correctamente active la opción Permitir siempre, dentro de la Cinta de opciones:

Vista> Parámetros> Permitir siempre

Editor avanzado: abre el Editor avanzado con los pasos de la consulta.

Propiedades: muestra el cuadro de propiedades, donde se puede cambiar el nombre de la consulta y agregar un comentario.

Si se hace clic derecho en un área en blanco del panel de la consulta, verá las siguientes opciones:

Pegar: si existe una consulta en el portapapeles, la puede pegar aquí.

Nueva consulta: nos permite crear una nueva consulta.

Nuevo grupo: permite crear una carpeta que servirá como grupo para organizar las consultas.

Expandir todo: abre todos los grupos para ver su contenido.

Contraer todo: oculta todos los grupos.

Configuración de la consulta: es el panel, situado a la derecha de la pantalla, donde se asigna el nombre de la consulta, además de registrar cualquier acción dentro de la consulta, aquí va agregando los pasos que se hagan a través de la interfaz gráfica. Dentro de los pasos aplicados podemos revisar cómo se va comportando la consulta haciendo clic a cualquier paso aplicado, y la consulta nos mostrará el resultado de ese paso inmediatamente. Vea la Figura 1.2.

Figura 1.2. Panel de Configuración de la consulta.

PANEL PASOS APLICADOS

Dentro de los pasos aplicados se pueden realizar diversas acciones con el botón derecho del ratón, estas opciones son:

Editar la configuración: en ciertos pasos podemos cambiar los parámetros que se establecieron para este paso aplicado, no todos los pasos aplicados se pueden cambiar, solo los pasos donde se nos muestra un engranaje en el lado derecho de dicho paso, lo que nos permitirá modificar esta opción.

Cambiar nombre: esto permite que nuestros pasos aplicados tengan más sentido dentro de nuestra consulta, también se puede seleccionar el paso a renombrar y oprimir la tecla F2 para editar el nombre del paso en cuestión.

Eliminar un paso: dentro de las consultas no existe el comando deshacer, así que todo se registra dentro de este panel y, cuando realizamos una acción que no necesitamos, podemos simplemente eliminar el paso que no queremos o en su caso, si nos aparece el engranaje en el lado derecho del paso, podemos modificarlo.

Eliminar hasta el final: existen consultas que son largas y sería monótono en algún momento o por alguna razón eliminar muchos pasos hasta llegar al que estamos buscando. Con esta opción, borrará desde el paso seleccionado hasta el final de todos los pasos, haciendo más fácil que se eliminen varios pasos al mismo tiempo.

Insertar paso después: en cualquier paso aplicado se pueden insertar pasos a lo largo de la consulta, esto lo podemos hacer con esta acción y en cualquier paso aplicado que se necesite.

Mover antes de: el paso aplicado que esté seleccionado se mueve un paso arriba. Se tiene que usar con precaución esta opción porque puede corromper toda la consulta.

Mover después de: el paso aplicado que esté seleccionado se mueve un paso abajo. Se tiene que usar con precaución esta opción porque puede corromper toda la consulta.

Extraer anteriores: crea una consulta nueva con los pasos que se encuentran antes del paso seleccionado y, entonces, la nueva consulta se vuelve el origen de la consulta actual.

Propiedades: nos muestra las propiedades del paso seleccionado, en este cuadro podremos cambiar el nombre y agregar comentarios a este paso.

LOS CONCEPTOS BÁSICOS DE NOMENCLATURA

Tabla: las tablas son un conjunto de datos organizadas de forma tabular que son el resultado de importarlos de diferentes fuentes. Las tablas pueden también ser el resultado del trabajo con otras tablas e incluso ser parte de una columna que en cada celda contenga una tabla, un concepto nuevo que veremos cómo se trabaja más adelante.

El manejo de las Consultas tiene una forma llamada columnar, es decir, que solo se podrán manejar datos de las columnas enteras, no se podrán modificar las renglones o celdas. Además, tenemos que pensar que un cambio a una columna siempre afectará a todos los datos de esta.

Las consultas funcionan de diferente manera a las de Excel, ya que estas pueden contener, en sus celdas, elementos como otra tabla completa, una lista o incluso un archivo de Excel. Dependiendo del tipo de objeto que contenga una celda, esta tendrá una palabra clave específica. Las palabras clave en las celdas son:

Binary: el contenido de la celda es un archivo, si hacemos clic en la celda que contiene esta nomenclatura, sin tocar la palabra, nos mostrará una vista previa del archivo que contiene. Vea la Figura 1.3.

Table: el contenido de la celda es otra tabla, si hacemos clic en la celda que contiene esta nomenclatura, sin tocar la palabra, nos mostrará una vista previa de la tabla que contiene. Vea la Figura 1.4.

Figura 1.3. El contenido de una celda Binary es un archivo.

Figura 1.4. La columna llamada Columna con tablas contiene tablas completas en cada celda.

List: el contenido de la celda es una lista, una lista es una serie de valores, en una sola columna, que nos permiten darles mucho poder a nuestras funciones, si hacemos clic en la celda que contiene esta nomenclatura, sin tocar la palabra, nos mostrará una vista previa de la lista que contiene. Vea la Figura 1.5.

Figura 1.5. Una columna con listas.

Record: el contenido de un renglón es un registro, es decir, los datos que son parte de un renglón entero. Si hacemos clic en la celda que contiene esta nomenclatura, sin tocar la palabra, nos mostrará una vista previa del registro que contiene. Vea la Figura 1.6.

Figura 1.6.

Error: el contenido de la celda es un error, si hacemos clic en la celda que contiene esta nomenclatura, sin tocar la palabra, nos mostrará una vista previa del error al que hace referencia.

Lista: la lista es un conjunto de datos definidos por una sola columna, las listas son usadas recurrentemente dentro de las funciones del lenguaje M, estas son increíblemente poderosas para poder anidar funciones y crear consultas que, a través de la interfaz gráfica, no tendríamos forma de lograr.

Una lista puede contener más listas, a esto se le llama lista de listas. Esta forma de trabajo es increíble, ya que podemos crear poderosas funciones personalizadas, anidando las funciones básicas para obtener resultados extraordinarios.

Las listas se definen entre llaves, por ejemplo:

{1..10}

Y una lista de listas se define entre llaves, y cada lista que va a pertenecer a esta lista deberá ir entre llaves también separadas por una coma, por ejemplo:

{{1..10},15..20},{30..50}}

Si quisiéramos convertir una columna de una tabla en una lista, puede usar la siguiente sintaxis:

NombreTabla[NombreColumna]

Y si queremos obtener el primer elemento de esa lista, use esta sintaxis:

NombreTabla[NombreColumna]{NumeroRegistro}

El NumeroRegistro es un número entero que le indica a las consultas qué registro visualizar. Las consultas tienen una base 0, es decir, que para hacer referencia al primer elemento usaremos siempre la referencia 0.

Registro: