Topicos de Excel de Manera sencilla, Apuntes de Informática

¡Descarga Topicos de Excel de Manera sencilla y más Apuntes en PDF de Informática solo en Docsity!

(Material teórico gratuito 2019)

• 1. Introducción..................................................................................................................................................... Contenido
  • 1.1. Tipos de datos en las planillas de cálculo ................................................................................................
  • 1.2. Celdas, Rangos y Tablas ...........................................................................................................................
  • 1.3. Los formatos y sus propiedades ..............................................................................................................
  • 1.4. Formatos condicionales ...........................................................................................................................
  • 1.5. Filtros
    • 1.5.1. Filtros de texto
    • 1.5.2. Filtros de Números
    • 1.5.3. Filtros por color.............................................................................................................................
  • 1.6. Filtros Avanzados
  • 1.7. Subtotales
• 2. ¿Qué son las funciones en Excel?
  • 2.1. ¿Cómo invocar a una función?
  • 2.2. Tipos de funciones
    • 2.2.1. Funciones de Búsqueda y Referencia
    • 2.2.2. Funciones de Fecha y Hora
    • 2.2.3. Funciones de Texto
    • 2.2.4. Funciones Estadísticas
    • 2.2.5. Funciones Financieras
    • 2.2.6. Funciones Lógicas
    • 2.2.7. Funciones Matemáticas y Trigonométricas
  • 2.3. Diferencia entre funciones y fórmulas
  • 2.4. Caracteres de TEXTO dentro de las funciones y fórmulas
  • 2.5. Errores en las Funciones y Fórmulas en Excel
  • 2.6. Funciones recursivas y dinámicas (Material teórico gratuito 2019)
    • 2.6.1. Funciones recursivas.....................................................................................................................
    • 2.6.2. Funciones dinámicas
    • 2.6.3. Ejemplo del uso de la función AGREGAR
• 3. Tablas dinámicas y Herramientas Business Intelligence en Excel
  • 3.1. ¿Qué son las tablas dinámicas en Excel?
  • 3.2. ¿Cómo activar la Herramienta Tablas Dinámicas?
    • 3.2.1. Ejemplo de aplicación
  • 3.3. Tipos de Informes Dinámicos
  • 3.4. Personalizando los cálculos
  • 3.5. Tipo de funciones de resumen de datos en tablas dinámicas
    • 3.5.1. Mostrando valores relativos
  • 3.6. Personalizando la apariencia de los informes
  • 3.7. Gráficos dinámicos y Datos segmentados
    • 3.7.1. Gráficos dinámicos........................................................................................................................
    • 3.7.2. Segmentación de datos
  • 3.8. Modelos de Datos
    • 3.8.1. Modelos de datos relacionales
  • 3.9. Herramientas “Power” en Excel
    • 3.9.1. Power Pivot
  • 3.10. Power Query (Obtener y Transformar desde Excel 2016)
    • 3.10.1. Utilizando Power Query................................................................................................................
  • 3.11. Power Maps (Mapas 3D desde Excel 2016)......................................................................................
    • 3.11.1. Generar un Power Map
  • 3.12. Power BI. Más allá de Excel
    • 3.12.1. Utilizando Power BI Desktop
• 4. Grabación de Macros y Programación VBA
  • 4.1. Introducción..........................................................................................................................................
  • 4.2. Grabación de macros en Excel con VBA
    • 4.2.1. Traduciendo nuestras acciones en código VBA: la grabadora de macros
    • 4.2.2. Grabando nuestra primera macro
    • 4.2.3. Ejemplo (Referencias Absolutas) (Material teórico gratuito 2019)
    • 4.2.4. Examinando el Código de una macro
    • 4.2.5. Ejemplo (Referencias Relativas)
    • 4.2.6. Optimizando el código generado por la macro
    • 4.2.7. Ejemplo: Pegar Valores con atajo de teclado
  • 4.3. Personalizando los mensajes de error
    • 4.3.1. La función MSGBOX
  • 4.4. On Error Resume Next
  • 4.5. La instrucción Select CASE
  • 4.6. ¿Qué es la programación orientada a objetos?
    • 4.6.1. Jerarquía
    • 4.6.2. Colecciones
    • 4.6.3. Propiedades
    • 4.6.4. Métodos........................................................................................................................................
    • 4.6.5. Eventos
  • 4.7. Explorando los métodos y propiedades de un objeto
    • 4.7.1. Variables y Funciones en VBA
  • 4.8. El bucle For – Next
  • 4.9. Uso de la función Offset en VBA
  • 4.10. Funciones personalizadas en Excel – VBA
    • 4.10.1. Creando nuestras primeras funciones en VBA
    • 4.10.2. El bucle For Each – Next
    • 4.10.3. Funciones personalizadas que recalculen automáticamente
• 5. Conclusiones
• 6. Bibliografìa

(Material teórico gratuito 2019)

1. Introducción

Microsoft Excel © (en adelante Excel) es el software para la creación, gestión y edición de planillas de cálculo más conocido y utilizado a nivel mundial. Los orígenes de este programa datan de los primeros años de la década del 80.

Todas las personas que realizan tareas relacionadas con las ciencias económicas deberían dominar Excel; pero la realidad es que, aun siendo tan utilizado y tener tantos años de vigencia, existe mucho desconocimiento acerca de las características de este poderoso software. Esperamos poder contribuir con este material teórico (basado en las versiones 2016 y 2019 de Excel) a generar la inquietud en los lectores sobre el estudio de Excel en particular y de la teoría de las planillas de cálculo en general.

1.1. Tipos de datos en las planillas de cálculo

Excel puede gestionar tres tipos de datos, los cuales se definen como datos NUMÉRICOS (estos son los que pueden operarse matemáticamente), datos ASCII (datos que representan cadenas de texto) y datos FÓRMULA (instrucciones que contienen procedimientos operacionales integrados a Excel).

Más adelante, veremos que los datos pueden representarse visualmente al usuario con distintos formatos. Lo más importante en este punto es que el usuario comprenda que, sin importar cómo se muestren en pantalla los datos, internamente Excel procesa tres tipos de datos: NUMÉRICOS, ASCII o FÓRMULA.

Es importante destacar que los datos se ordenarán de distintas formas dependiendo los tipos de datos que contenga un determinado conjunto de celdas.

Observemos la siguiente representación gráfica de una pantalla de Excel en donde tenemos, por un lado, una serie de datos NUMÉRICOS y, por el otro, una serie de datos ASCII. A ambas series de datos se les aplico un orden con la opción:

Observemos ahora como, internamente, Excel diferencia un carácter numérico ASCII de un dato NUMÉRICO. Lo hace anteponiendo un apostrofe (‘) al dato.

(Material teórico gratuito 2019)

1.2. Celdas, Rangos y Tablas

En este punto abordaremos la forma en los archivos de Excel estructura los datos que estos contienen.

Las planillas de Excel se componen por LIBROS (cada archivo de Excel se denomina Libro) con una determinada cantidad de HOJAS (desde la versión 2007 de Excel, el número de hojas posibles en un libro está limitado solamente por la memoria de la computadora) Estas HOJAS pueden tener un total de 1.048.576 filas por 16384 columnas.

La unidad mínima de información en Excel se denomina CELDA y, en principio, se identifica por su posición vertical y su posición horizontal, por ejemplo, la celda A1 es la que corresponde a la intersección de la primera fila con la primera columna, de un total de 17.179.869.184 posibles intersecciones distintas, es decir, unos 17 mil millones de celdas posibles.

Excel permite identificar las columnas por letras o por su posición numérica. Para ello se debe realizar los

siguientes pasos ARCHIVO  OPCIONES  FORMULAS  ESTILO DE REFERENCIA F1C1.

En principio puede resultar algo confuso ya que la celda A1 se denominará F1C1 (Fila 1, Columna 1), pero en planillas grandes (con más de 30 columnas) resultará muy fácil identificar la posición vertical de cada celda. Observe estos casos…

El segundo tipo de agrupamiento de datos se da con los llamados RANGOS. Un rango en Excel es un conjunto de celdas contiguas en forma vertical, como en forma horizontal. Dos o más celdas contiguas forman un rango. Es importante aclarar que Excel no considera a los rangos como una estructura de información a menos que se les dé un nombre a los mismos.

(Material teórico gratuito 2019)

El tercer agrupamiento de datos se da con las llamadas TABLAS. Una tabla en Excel es una estructura ampliada de información que permite una integración adecuada de fórmulas y formatos, además de filtros y ordenamientos entre los mismos.

Con la combinación de teclado CTRL + t se puede convertir un rango en tabla de una manera rápida y fácil. Al trabajar con tablas se consiguen una serie de ventajas en lo que respecta a velocidad y precisión en la carga de datos, así como también en lo referente a la integridad de la lista contenida en la tabla.

(Material teórico gratuito 2019)

1.4. Formatos condicionales

La herramienta FORMATO CONDICIONAL permite aplicar distintos tipos de formatos en las celdas según cumplan éstas ciertas condiciones específicas, como valores duplicados, valores que cumplen criterios específicos (como mayor que 100 o es igual a “Ingresos” con Resaltar reglas de celdas y Reglas superiores e inferiores).

El formato condicional es dinámico y, por lo tanto, a medida que cambien los valores, el formato se ajustará automáticamente.

En el ejemplo tenemos una lista de sucursales y a cuanto ascendieron sus ventas anuales. Queremos resaltar aquellas sucursales cuya venta fue mayor a $150.000. Para ello utilizaremos la opción de formato condicional.

(Material teórico gratuito 2019)

Podemos, además, dar formato a celdas que respondan a una determinada característica no numérica, por ejemplo, las sucursales que empiecen con la letra C. Para ello vamos a la opción más reglas.

1.5. Filtros

Cuando se tiene una gran cantidad de información en una planilla, es importante el disponer de herramientas que permitan filtrar determinados datos. Excel permite aplicar distintos tipos de filtros en función al tipo de datos con los que se estén trabajando.

1.5.1. Filtros de texto

Los filtros de texto permiten trabajar con las cadenas de caracteres ASCII que componen los datos en las celdas. Podremos, por ejemplo, filtrar todas las palabras que NO contengan el carácter “J”, así como también las que comiencen o terminen con una determinada cadena de caracteres, entre otras opciones.

(Material teórico gratuito 2019)

Al combinar las herramientas de formato condicional con la de filtro por color, podremos reducir la cantidad de datos mostrados por la planilla, enfocando nuestra atención en la información que realmente nos interesa.

1.6. Filtros Avanzados

Entre las herramientas de Excel, dentro de la pestaña datos, se encuentra la opción filtros avanzados. Con filtros avanzados se pueden realizar interesantes manipulaciones de información en las planillas de cálculo.

Entre las aplicaciones de Filtros avanzados es posible, por ejemplo, copiar una serie de datos sin valores duplicados a otro lugar de la planilla (utilizando la opción Sólo registros únicos)

El funcionamiento de la opción Rangos de criterios es bastante sencillo, si se entiende su lógica.

(Material teórico gratuito 2019)

En este caso se pide a Excel que realice un filtro a la lista utilizando la siguiente instrucción lógica; a saber: Filtrar la lista Mostrando los valores (dentro de la columna Sucursal) que sean iguales a “Buenos Aires” o los que (dentro de la columna Local) sean igual a “Mediano” o los que (dentro de la columna Ventas) sean inferior a 95000.

De una forma similar se aplica otro criterio de filtro. En este caso se precisa la información de las sucursales en donde se hayan vendidos montos de más de $100.000 y menos de $200.000. El rango de criterios quedaría de la siguiente manera.

Se observa que se mantiene la lógica de que las condiciones “Y” se agregan en forma horizontal, en este caso se agregó una nueva columna Ventas.

(Material teórico gratuito 2019)

2. ¿Qué son las funciones en Excel?

Las funciones de Excel son procedimientos (algoritmos) predefinidos, que permiten realizar diferentes tipos de cálculo.

Las funciones se alimentan de valores específicos, denominados parámetros o argumentos, que el usuario debe introducir en un orden particular.

2.1. ¿Cómo invocar a una función?

Antes de responder a dicha pregunta, es necesario aclarar que las últimas versiones de Excel traen incorporadas más de 400 funciones, con lo cual resulta bastante dificultoso para la mayoría de los usuarios el memorizar los nombres y la ubicación de los distintos argumentos de éstas en su construcción.

Excel, en su interfaz, posee diversos métodos para invocar a las funciones; algunos de éstos son:

1. Escribir el nombre de la función (luego de los simbolos “=” , “+” y “@”). Esta metodología es considerada la más avanzada, ya que supone que el usuario conoce el orden de los argumentos de la función utilizada.

Figura 2.1. Al escribir, luego del símbolo “=”, las primeras letras del nombre de una función, Excel desplegará un cuadro para seleccionar la función requerida. Al recorrer dichos nombres se mostrará un pequeño resumen de la aplicación de la función correspondiente.

Figura 2.2. El usuario debe introducir los argumentos correspondientes a la función elegida. El separador de argumentos utilizado se mostrará en la ayuda que aparece debajo de la función. (En este caso, el separador de argumentos es el punto y coma “;”)

(Material teórico gratuito 2019)

2. Utilizando el ícono “Insertar función” junto a la barra de fórmulas.

Figura 2.3. El icono “Insertar Función” facilita el trabajo con funciones en Excel

Figura 2.4. La ventana “Insertar Función” nos permitirá buscarla por nombre o categoría, también recordará las que utilicemos frecuentemente. En esta ventana, además podemos acceder al panel de ayuda de cada función.

Figura 2.5. La ventana “Argumentos de función” nos brindará una interfaz clara para el ingreso de los argumentos de la función elegida. También nos mostrará el resultado que arrojará la función. Esta ventana resulta de mucha utilidad cuando trabajamos con funciones con gran cantidad de parámetros.

(Material teórico gratuito 2019)

FUNCIÓN INGLÉS DESCRIPCIÓN

AREAS AREAS^

Devuelve el número de áreas de una referencia. Un área es un rango de celdas contiguas o una única celda. BUSCAR LOOKUP^ Busca valores de un rango de una columna o una fila o desde una matriz. BUSCARH HLOOKUP^

Busca en la primera fila de una tabla o matriz de valores y devuelve el valor en la misma columna desde una fila especificada.

BUSCARV VLOOKUP

Busca un valor en la primera columna de la izquierda de una tabla y luego devuelve un valor en la misma fila desde una column especificada. De forma predeterminada, la tabla se ordena de forma ascendente.

COINCIDIR MATCH^

Devuelve la posición relativa de un elemento en una matriz, que coincide con un valor dado en un orden especificado. COLUMNA COLUMN^ Devuelve el número de columna de una referencia. COLUMNAS COLUMNS^ Devuelve el número de columnas en una matriz o referencia. DESREF OFFSET^

Devuelve una referencia a un rango que es un número especificado de filas y columnas de una referencia dada.

DIRECCION ADDRESS^

Crea una referencia de celda en forma de texto una vez especificados los números de fila y columna.

ELEGIR CHOOSE^

Elige un valor o una acción de una lista de valores a partir de un número de índice. FILA ROW^ Devuelve el número de fila de una referencia. FILAS ROWS^ Devuelve el número de filas de una referencia o matriz. HIPERVINCULO HYPERLINK^

Crea un acceso directo o salto que abre un documento guardado en el disco duro, en un servidor de red o en Internet. IMPORTARDATOSDINAMICOS GETPIVOTDATA^ Extrae datos almacenados en una tabla dinámica. INDICE INDEX^

Devuelve un valor o referencia de la celda en la intersección de una fila y columna en particular, en un rango especificado. INDIRECTO INDIRECT^ Devuelve una referencia especificada por un valor de texto. RDTR RTD^

Recupera datos en tiempo real de un programa compatible con automatizaciones COM.

TRANSPONER TRANSPOSE^

Devuelve un rango vertical de celdas como un rango horizontal, o viceversa.

2.2.2. Funciones de Fecha y Hora

Las funciones de fecha y hora de Microsoft Excel son de gran utilidad para los siguientes casos: buscar fechas específicas, conocer la hora actual, encontrar la diferencia en días laborales entre dos fechas además de facilitar una gran cantidad de acciones que involucren el trabajo con este tipo de datos.

Las funciones de fecha y hora deberían de las más conocidas por los profesionales en Ciencias Económicas, debido a su potencial en el trabajo con datos que involucren el paso del tiempo.

(Material teórico gratuito 2019)

FUNCIÓN INGLÉS DESCRIPCIÓN

AHORA NOW^ Devuelve la fecha y hora actuales con formato de fecha y hora. AÑO YEAR^ Devuelve el año, un número entero en el rango 1900-9999. DIA DAY^ Devuelve el día del mes (un número de 1 a 31). DIA.LAB WORKDAY^

Devuelve el número de serie de la fecha antes o después de un número especificado de días laborables.

DIA.LAB.INTL WORKDAY.INTL^

Devuelve el número de serie de la fecha anterior o posterior a un número especificado de días laborables con parámetros de fin se semana personalizados. DIAS.LAB NETWORKDAYS^ Devuelve el número total de días laborables entre dos fechas. DIAS.LAB.INTL NETWORKDAYS.INTL^

Devuelve el número de días laborables completos entre dos fechas con parámetros de fin de semana personalizados.

DIAS360 DAYS^

Calcula el número de días entre dos fechas basándose en un año de 360 días (doce meses de 30 días). DIASEM WEEKDAY^ Devuelve un número de 1 a 7 que identifica el día de la semana. FECHA DATE^

Devuelve el número que representa la fecha en código de fecha y hora de Microsoft Excel.

FECHA.MES EDATE^

Devuelve el número de serie de la fecha que es el número indicado de meses antes o después de la fecha inicial.

FECHANUMERO DATEVALUE^

Convierte una fecha en forma de texto en un número que representa la fecha en código de fecha y hora de Microsoft Excel.

FIN.MES EOMONTH^

Devuelve el número de serie del último día del mes antes o después del número especificado de meses.

FRAC.AÑO YEARFRAC^

Devuelve la fracción del año que representa el número de días completos entre la fecha_inicial y la fecha_fin. HORA HOUR^ Devuelve la hora como un número de 0 (12:00 a.m.) a 23 (11:00 p.m.).

HORANUMERO TIMEVALUE

Convierte una hora de texto en un número de serie de Excel para una hora, un número de 0 (12:00:00 a.m.) a 0.999988426 (11:59:59 p.m.). Da formato al número con un formato de hora después de introducir la fórmula. HOY TODAY^ Devuelve la fecha actual con formato de fecha. MES MONTH^ Devuelve el mes, un número entero de 1 (enero) a 12 (diciembre). MINUTO MINUTE^ Devuelve el minuto, un número de 0 a 59. NSHORA TIME^

Convierte horas, minutos y segundos dados como números en un número de serie de Excel, con formato de hora.

NUM.DE.SEMANA WEEKNUM^ Devuelve el número de semanas en el año.

SEGUNDO SECOND^ Devuelve el segundo, un número de 0 a 59.