Grabación de un Formulario

Archivo de Excel utilizado: excelavanzado_grabacion_formulario.xlsm

Veamos cómo crear un formulario y como incorporar los valores a una base de datos de forma automática con una macro que grabe los registros simplemente pulsando un botón. La macro que haremos no requiere programación de código y se realiza con la grabadora de macros.

Primer Vídeo

En este vídeo veremos cómo se crea el formulario usando una de estas dos herramientas.

• Validación de datos
• Controles de formulario

Segundo Vídeo

Realizaremos de forma manual la incorporación de los datos del formulario a una base de datos y luego crearemos una macro con grabadora que permita automatizar el proceso. De esta forma para alimentar la tabla que se crea lo único que hemos de hacer es rellenar y seleccionar los distintos valores del formulario y cuando ya los tengamos pulsaremos un botón que lanza la macro y se añade el nuevo registro a la base de datos.

Tercer Vídeo

Vamos a retocar un poco el código que ha generado la grabadora de macros. El objetivo es conseguir que los nuevos registros de la base de datos no se queden arriba sino que se incorporen en la parte baja de la tabla.

Graficar por fechas

Archivo de Excel utilizado: excelavanzado_graficar_fechas.xlsm

Vamos a realizar una macro que nos permite representar gráficamente una serie de valores de una tabla pudiendo elegir el intervalo de fechas. El usuario selecciona una fecha inicial y una fecha final que van en el eje horizontal, y automáticamente el gráfico se adapta a ese intervalo de fechas.

Sub Eje_Personal()
ActiveSheet.ChartObjects("Temporal").Activate
ActiveChart.Axes(xlCategory).MinimumScale = [F4]
ActiveChart.Axes(xlCategory).MaximumScale = [F8]
End Sub

Graficar según el diccionario de la Real Academia Española

Funciones FILA, INDICE y RESIDUO

Archivo de Excel utilizado: excelavanzado_filas_alternas.xlsx

Veamos algunas funciones de Excel con las que luego realizaremos un caso práctico para seleccionar filas alternas en una tabla comenzando en una dada y con cierta frecuencia. Por ejemplo, comenzando en la fila doce y luego tomando una fila cada tres. Marcamos las filas seleccionadas con un cierto color usando Formato condicional y luego las copiamos y pegamos en una hoja nueva.

Primer Vídeo

La función FILA se puede utilizar de dos formas. Haciendo referencia a una celda nos da la fila en la que se encuentra esa celda. Y también funciona sin poner nada como argumento, simplemente poniendo =FILA(), en este caso nos da la fila en la se se encuentra la fórmula.

La función INDICE nos permite extraer un valor de una tabla o matriz dada la fila y columna de la celda que deseamos extraer.

Segundo Vídeo

La función RESIDUO calcula el resto o módulo que se obtiene al dividir dos números. Es muy útil para estudiar fenómenos repetitivos. Veremos dos casos, uno para determinar la letra del DNI (Documento Nacional de Identidad) y otro caso donde calcularemos el día de la semana que corresponde a cierta fecha.

Página de la Wikipedia que contiene las letras del DNI (Documento Nacional de Identidad) o NIF (Número de Identificación Fiscal) . Esto se usa en España y es una letra de control que se añade al número del documento de identidad que tiene asignado cada ciudadano.

• Número de identificación fiscal

Tercer Vídeo

Caso práctico donde se utilizan las funciones siguientes.

• FILA
• RESIDUO
• INDICE
• BUSCARV
• SI

Lo que hacemos es seleccionar una serie de filas de una tabla comenzando por una fila inicial y con una cierta frecuencia que nos indica el usuario. Usamos Formato condicional para marcarlas con un cierto color y luego las copiamos y pegamos en otra hoja.

Macros con Grabadora

Archivo de Excel utilizado: excelavanzado_grabadora.xlsm

Una macro es un programa que realizamos para automatizar procesos repetitivos. No solo se pueden crear macros en Excel, muchos otros programas pueden crear sus propias macros, por ejemplo Photoshop.

Vamos a crear una macro con grabadora y vamos a lanzarla de varias formas:

• Con un atajo de teclado: Control + Letra
• Con el menú de Macros
• Desde el Editor de Visual Basic
• Con botones
• Con imágenes

Primero aprenderemos a obtener la ficha PROGRAMADOR para poder trabajar con macros.

Realizaremos macros de posición absoluta y de posición relativa, que son las que se ejecutan a partir de donde tengamos situado el cursor cuando la lanzamos. El icono se denomina "Usar referencias relativas".

Grabaremos el archivo con extensión XLSM que son los habilitados para macros.

Veremos el Editor de Visual Basic al que podemos acceder con Alt+F11.

Las macros se pueden detener presionando la tecla Esc y si esto no funciona el método general para detenerlas es presionando la tecla: Control+Pausa. Es una tecla que suele estar el los teclados arriba a la derecha. Junto a la palabra pausa en ocasiones pone la palabra Interrumpir o una abreviatura (Inter) o en inglés Break.

Primer Vídeo

Veamos el vídeo para crear nuestra macro.

Segundo Vídeo

Veamos diferentes formas de lanzar la macro. Para que la macro no se ejecute siempre en la misma posición dentro de la hoja veremos cómo crear macros de posición relativa.

Veces que se repiten ciertos números

Archivo de Excel utilizado: matricial_veces_repetidos.xlsx

Nos han planteado un caso práctico donde se necesitaba conocer cuántas veces se repite cada uno de los números entre el 1 y el 33 dentro de una tabla donde se encuentra esa información. Además se requería en un formato concreto. Para resolver este caso hemos tenido que utilizar funciones matriciales de las de tamaño grande.

Para entender este caso se tendría que revisar previamente el siguiente post.

• Último valor de la fila

Tabla 1

En la zona azul se generan números aleatorios entre 1 y 33.

Tabla 2

En la zona naranja contamos cuántas veces a parece cada uno de los 33 números.

En la zona verde se representa cada número según las veces que aparece usando diferentes columnas según el número de veces que aparezca.

Tenemos columnas que van entre 0 y 15. Estamos suponiendo que el número máximo de veces que puede aparecer un número son 15 veces. Esto se tendría que verificar en cada caso.

Tabla 3

La zona amarilla es el transpuesto de la zona verde. Se podría haber obtenido con la función matricial TRANSPONER.

Tabla 4

La zona rosa es la que queríamos obtener. En ella se ve cuantas veces aparecen cada uno de los números generados aleatoriamente.

Gráfico incremental

El ejemplo utilizado en el vídeo se puede encontrar en la Hoja4 del siguiente archivo.

• excelavanzado_desref.xlsx

Utilizando la capacidad de la función DESREF para crear Rangos dinámicos vamos a crear el denominado Gráfico incremental. Se trata de un gráfico en el que podemos elegir de forma sencilla, presionando sobre un control de formulario, cuántos valores se verán representados en la serie de datos del gráfico.

Conviene entender previamente lo que es un Rango dinámico consultado el siguiente post.

• Rango dinámico

La sintaxis de la función DESREF es la siguiente.

=DESREF(ref, filas, columnas, [alto], [ancho])

Registros únicos

Archivo utilizado para realizar el caso práctico: excelavanzado_registros_unicos.xlsx

Vamos a extraer de una base de datos los registros únicos. Utilizaremos dos métodos:

1. Datos, Eliminar duplicados
2. Usando CONTAR.SI y con Filtro avanzado

Media móvil y línea de tendencia en un gráfico de dispersión

Archivo utilizado para realizar el caso práctico: excelavanzado_boletin_electrico.xlsx

Vamos a trabajar con datos reales de demanda eléctrica mensual en España. Vamos a construir un gráfico de dispersión y sobre él vamos a calcular la media móvil de 12 meses. También crearemos la Línea de tendencia o Recta de regresión. Aprenderemos a establecer su ecuación mediante el término independiente de la recta y la pendiente. Finalmente veremos la función PRONOSTICO dentro de la categoría de funciones estadísticas.

Primer vídeo

Segundo vídeo

• Recta de regresión
• Término independiente
• Pendiente
• Coeficiente R2 (Coeficiente de determinación)
• Coeficiente R (Coeficiente de correlación)
• PRONOSTICO

BUSCARV para valores repetidos

Archivo utilizado en el vídeo: excelavanzado_buscarv_repetidos.xlsx

La función BUSCARV siempre busca el primer valor que encuentra en el caso de que existan varios repetidos. Vamos a proponer un procedimiento que nos permitirá extraer de una base de datos aquellos registros que indiquemos aunque estén repetidos.

Nota

En el rango W2:Z2 se encuentran las fórmulas aleatorias con las que se han generado los valores de la base de datos.

Hoja1

Hoja2

Usamos la siguiente función matricial.

=SI.ERROR(INDICE($B$6:$B$24;K.ESIMO.MENOR(SI(C6:C24=0;FILA());FILA()-5)-5);"")

Hoja3

En la Hoja3 se mustran los pasos que permite obtener la fórmula de la Hoja3 de la columan amarilla.

Hoja4

Introducimos una fórmula matricial que extrae los registros que cumplen el criterio. En la celda Q9 vemos la siguiene fórmula matricial.

=SI.ERROR(INDICE(factura;K.ESIMO.MENOR(SI($I$5=Comercial;FILA()-8);FILA()-8);1);"")

Esta fórmula matricial no pertenece únicamente a esa celda, sino que abarca el rango Q9:Q58.

Las fórmulas matriciales requieren tres pasos para establecerlas correctamente.

1. Paso 1. Seleccionar el rango donde la fórmula matricial actua. En este caso es el rango Q9;Q58
2. Paso 2. Escribir la fórmula matricial
3. Paso 3. Validar la fórmula pulsando simultáneamente las tres siguiente teclas: Contro+Shift+Enter

Módulo 2: Manejo de datos y gráficos

Menús, Plantillas, Formato condicional, validación de datos, formularios, Agrupar y Subtotales

1. Rellenar series
2. Gráficos
3. Minigráficos
4. Suma en 3D e Hipervínculos
5. Vincular entre Excel y Word o Power Point
6. Importación de texto
7. Insertar Tabla en Excel 2010
8. Control de duplicados

Gráficos

Todos los ejemplos de estos vídeos están en el siguiente archivo de Excel. También se incluyen los archivos descargados de Yahoo Finanzas con las cotizaciones de Starbucks y de Apple.

• excelavanzado_graficos.xlsx
• descargas_csv.zip

Veremos tanto aspectos básicos como avanzados para mejorar la calidad de nuestros gráficos. Insertaremos una imagen prediseñada dentro de las barras de un gráfico de columnas.

Daremos especial importancia a la diferencia que existe entre un gráfico de Líneas y un gráfico de Dispersión (XY).

Crearemos líneas de tendencia con los gráficos de Dispersión. Veremos qué es una nube de puntos y cómo calcular la recta de regresión. Realizaremos alguna predicción o pronóstico sobre el comportamiento de los datos futuros siguiendo la curva de tendencia.

También tratamos la creación de medias móviles y el tema de la interpolación.

Veremos la incorporación de dos series en un mismo gráfico con dos escalas. Son los denominados gráficos combinados.

Primer vídeo

Gráfico de columnas. Introducción de una imagen representando la altura de las columnas.

Segundo vídeo

Gráfico circular.

Tercer vídeo

Gráfico de Líneas.

Cuarto vídeo

Gráfico de dispersión.

Quinto vídeo

Diferencia entre un gráfico de Líneas y un gráfico de Dispersión.

Sexto vídeo

Escala logarítmica en un gráfico de dispersión para representar variaciones porcentuales. El caso de los gráficos bursátiles.

Séptimo vídeo

Trabajar con dos escalas. El eje primario y el eje secundario.

Octavo vídeo

Comparación de la cotización histórica de Starbucks y de Apple. Ambas acciones cotizan en el Nasdaq. Es un caso práctico para explicar los gráficos con la misma base, en este caso, usamos base 100.

Importación de texto

Estos son los archivos utilizados como ejemplo.

• excelavanzado_logistica.xlsx
• logistica1.txt
• logistica2.txt
• logistica.zip  (contiene comprimidos los dos archivos anteriores)

En ocasiones importamos datos provenientes de un ordenador central o de un paquete de contabilidad, personal, almacén o cualquier otro que nos proporciona los datos en texto puro. Lo que en ocasiones llamamos texto plano o código ASCII.

En Datos, Obtener datos externos, desde texto disponemos de una herramienta que nos permite importar texto plano y transformarlo en columnas que luego Excel puede manejar.

Insertar Tabla en Excel 2010

Descargar el archivo excelavanzado_Tablas.xlsx


Veamos cómo trabajar con una Tabla en Excel 2010 y que actúe con Rango dinámico al crear un gráfico, una tabla dinámica o al hacer una validación de datos de tipo lista.

Primer vídeo

Vamos a crear una tabla y un gráfico sobre ella. Luego introduciremos nuevas filas y columnas en la tabla y veremos cómo se actualiza automáticamente el gráfico incorporándose a él las nuevas filas y columnas. Esto es lo que denominamos Rango dinámico.

Si hubiéramos creado la tabla sin haber pedido a Excel que la de carácter de Tabla al introducirse nuevas filas y columnas estas no se hubieran incorporado al gráfico ya que el gráfico se realiza sobre un área concreta marcada con un cierto rango, pero este rango no se amplia o reduce de forma dinámica.

Segundo vídeo

Lo mismo sucede si tenemos una Tabla dinámica. Si hemos pedido a Excel que dote de carácter de Tabla a nuestros datos al modificar las filas, aumentándolas o disminuyéndolas, la Tabla dinámica se actualiza sin más que pulsar sobre ella y pedir como es habitual que se actualice. Si no hubiéramos dotado a nuestra base de datos del carácter de Tabla de Excel la Tabla dinámica estaría apuntando siempre a un mismo rango de datos y no se actualizaría su tamaño de forma dinámica.

Tercer vídeo

• Veamos cómo se puede volver a dejar una Tabla de Excel en un rango normal: Convertir en rango
• Totalizar al final de la tabla: Fila de totales
• Resaltar Primera columna y Última columna
• Insertar columna en una Tabla de Excel
• Añadir columna de totales
• Validación de Datos tipo Lista
• Rango dinámico en Validación de Datos tipo Lista
• Función BUSCARV aplicada a una Tabla de Excel

Rango Dinámico

Ya hemos visto que se pude trabajar con Rangos dinámicos al Insertar Tabla de Excel. Antes de disponer de esta opción existía un truco que conseguía estos resultados para que al hacer un gráfico o una tabla dinámica al variar los registros de una base de datos no tuviéramos que modificar el rango del gráfico o de la tabla dinámica. Esto se puede ver en el siguiente post.

• Rango dinámico

Se trabajaba con la función DESREF y con las funciones CONTAR o CONTARA creando nombres de rango de tipo fórmula. Aún podemos seguir trabajando de esta forma, con fórmula, pero el sistema actual que tenemos al introducir Tablas de Excel ha mejorado mucho la forma de trabajar.

Si lo desea puede practicar partiendo de la información en blanco e introduciendo las tablas que hemos visto en el video usando el siguiente archivo.

• blanco_excelavanzado_Tablas.xlsx

Suma en 3D e Hipervínculos

Descargar el archivo excelavanzado_consolida.xlsx

Vamos a trabajar con varias hojas de cálculo y las vamos a consolidar mediante la suma en 3D, en tres dimensiones. Esto lo que quiere decir es que vamos a trabajar con hojas que tienen la misma estructura y sobre las que vamos a sumar cierta celda "en profundidad", esto es, a lo largo de todas las hojas.

En el ejemplo disponemos de 10 filiales con una cuenta de resultados que tiene la misma estructura en todas ellas y deseamos consolidar la información contable en la matriz. Excel lo que nos permite es sumar una misma celda de todas esas hojas.

También aprenderemos a trabajar en Grupo, esto es, seleccionando hojas y aplicando todo lo que hagamos a la selección.

Primer vídeo

Suma 3D.

Segundo vídeo

Aprenderemos a crear Hipervínculos en Excel. Los crearemos de varias formas:

• sobre texto dirigidos a "lugar de este documento"
• sobre Formas
• sobre imágenes prediseñadas
• sobre imágenes dirigidos a página web

Minigráficos

Descargar el archivo excelavanzado_minigraficos.xlsx

Los minigráficos se introdujeron en la versión Excel 2010. Son una gran ayuda para representar rápidamente la evolución de una serie de datos.

Los hay de tres tipos:

• Líneas
• Columnas
• Ganancia o pérdida

Veamos el siguiente vídeo donde se explica su creación y opciones.

Control de duplicados

Descargar el archivo duplicados.xlsx

En Excel existen varias formas de detectar duplicados y de eliminarlos. Utilizaremos una opción propia de Excel que se llama Quitar duplicados. También los detectaremos marcado con un color mediante Formato condicional y el uso de la función CONTAR.SI.

Primer vídeo

Segundo vídeo

Tercer vídeo

Otros post en los que se trata este tema.

• Comparación de tablas
• Elementos distintos y repetidos en una lista
• Impedir Repetidos

Función BUSCARV

Descargar el archivo excelavanzado_buscarv.xlsx

Una de las funciones más utilizadas en Excel, después de la de suma, es la función BUSCARV.

• BUSCARV nos permite hacer una búsqueda vertical en una tabla
• BUSCARH nos permite hacer una búsqueda horizontal en una tabla

Primer Vídeo

Segundo Vídeo

Tercer Vídeo

Módulo 1: Conceptos previos

Introducción de la materia y repaso de los elementos básicos de Excel y sus versiones.

1. Descripción básica de Excel 2010
2. Aspectos básicos de Excel 2010
3. Fórmulas básicas
4. Referencias relativas y absolutas
5. Métodos abreviados de teclado
6. Trabajar con Nombres de Rango
7. Función lógica SI
8. Función BUSCARV

Función lógica SI

Descargar el archivo excelavanzado_funcion_si.xlsx

La función SI nos permite escribir una de dos cosas en una celda según que una condición se cumpla o no.

Vamos a ver la función SI y cómo se trabaja con operadores lógicos.

Primer vídeo

Segundo vídeo

Tercer vídeo

Cuarto vídeo