• Author / Uploaded
• Carolina Patiño

• Categories
• Microsoft Excel
• Tabla (base de datos)
• Hoja de cálculo
• Función (Matemáticas)
• Fórmula

Citation preview

NIVEL AVANZADO Microsoft Excel es una aplicación para manejar grandes informaciones en hojas de cálculo. A través de este Curso el usuario será capaz de efectuar Funciones Financiera, de Auditoria, Herramientas de Análisis, Base de Datos, Tareas Avanzadas, Macros Automáticas y otras Herramientas de Excel.

Representaciones ISRAMCA, C.A. Adiestramiento Gerencial, Administrativo, Secretarial

Diseño y Diagramación: Especialista Informática Milady Arévalo

Actualización: Enero 2017

Prohibida la reproducción parcial o total de este Libro Derechos Reservados

Curso Nivel Avanzado

Requisitos para realizar este curso: Tener conocimientos básicos del sistema operativo Windows XP, Windows 7 u 8, o Usuarios con experiencia previa en Excel Intermedio. Dirigido a: Usuarios con necesidades de dominio avanzado de Excel, para aplicar y desarrollar a través de técnicas avanzadas, criterios para proporcionar información y reportes de manera rápida y efectiva, explorar las funcionalidades avanzadas para el diseño de fórmulas complejas, financieras y tablas dinámicas. Para ejecutivas y ejecutivos, es una herramienta de gran poder para realizar cálculos financieros y estadísticos, ya que en cualquier organización es un prerequisito que se exige a sus empleados. A todas las personas que necesiten utilizar esta hoja de cálculo como herramienta habitual diaria tanto para su trabajo como él hogar. Para estudiantes de todo tipo, ya que podrán presentar sus trabajos finales de una forma más profesional. Objetivos del curso: Este Curso provee un nivel avanzado en el uso de la herramienta, los usuarios serán capaces de efectuar: Bases de Datos, funciones Financieras, Auditoria, Tareas Avanzadas, Herramientas de Análisis y propiedades avanzadas de Excel, Macros automáticas.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 1 de 154

Curso Nivel Avanzado

INDICE DE CONTENIDO Pág HERRAMIENTAS DE DATOS ..................................................................................................... 5 1.

VALIDACIÓN DE DATOS EN EXCEL .................................................................................... 5 VALIDACIÓN DE UN NÚMERO ENTERO:................................................................................................................................. 8 VALIDACIÓN DE UNA LISTA: ............................................................................................................................................... 9 VALIDACIÓN DE FECHA: .................................................................................................................................................... 9 VALIDACIÓN PERSONALIZADA POR MEDIO DE UNA FÓRMULA.................................................................................................. 10

2.

FUNCION DESREF ........................................................................................................... 12 EJEMPLOS DE LA FUNCIÓN DESREF .................................................................................................................................. 14 CREAR LA LISTA DESPLEGABLE EN EXCEL ............................................................................................................................. 15

3.

FUNCIÓN SI.ERROR ........................................................................................................ 18

4.

FUNCIONES DE BUSQUEDA Y REFERENCIA...................................................................... 19

5.

FUNCIÓN EXCEL BUSCARV: BUSCA DE MANERA VERTICAL .............................................. 19 SINTAXIS DE LA FUNCIÓN DE EXCEL BUSCARV ................................................................................................................... 19 ESTRUCTURA DE LA FUNCIÓN DE EXCEL BUSCARV ............................................................................................................. 19

6.

FUNCIÓN EXCEL BUSCARH: BUSCA DE MANERA HORIZONTAL ........................................ 25

7.

BUSCARV CON FUNCION SI, SOBRE 2 TABLAS DE DATOS................................................ 27

8.

FUNCIÓN INDICE ............................................................................................................ 29

9.

FUNCIÓN COINCIDIR ...................................................................................................... 30

10. ¿CÓMO BUSCAR A PARTIR DE DOS VARIABLES EN EXCEL? .............................................. 32 11. CONSOLIDACIÓN ........................................................................................................... 36 12. SUBTOTALES Y ESQUEMATIZACION................................................................................ 47 ELABORACIÓN DE SUBTOTALES ......................................................................................................................................... 47 ELABORACIÓN DE SUBTOTALES ANIDADOS .......................................................................................................................... 49 USO DE VARIAS FUNCIONES DE RESUMEN ........................................................................................................................... 49

13. HERRAMIENTAS DE ANÁLISIS ......................................................................................... 51 14. ESCENARIOS .................................................................................................................. 51 CREAR UN ESCENARIO: ................................................................................................................................................... 51 CREACIÓN DE UN INFORME TIPO RESUMEN......................................................................................................................... 56 EVALUANDO ESCENARIOS CON EXCEL ................................................................................................................................ 56 EL ADMINISTRADOR DE ESCENARIOS EN EXCEL .................................................................................................................... 57 PROBAR LOS ESCENARIOS CREADOS .................................................................................................................................. 59 Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 2 de 154

Curso Nivel Avanzado

CREAR BOTÓN DE ESCENARIO EN LA BARRA DE TAREAS .......................................................................................................... 61

15. BUSCAR OBJETIVO ......................................................................................................... 61 EL COMANDO BUSCAR OBJETIVO ...................................................................................................................................... 62

16. SOLVER, MUCHO MÁS QUE UN COMPLEMENTO DE ANÁLISIS DE HIPÓTESIS. .................. 67 UTILIZANDO EXCEL SOLVER .............................................................................................................................................. 67 EJEMPLO DE USO DE SOLVER............................................................................................................................................ 67

17. TABLA DE DATOS FINANCIEROS ..................................................................................... 76 TABLA DE DATOS DE UNA VARIABLE................................................................................................................................... 76 TABLA DE DATOS DE DOS VARIABLES.................................................................................................................................. 79

18. TABLAS DINAMICAS EXCEL ............................................................................................. 82 CÓMO CREAR UNA TABLA DINÁMICA ................................................................................... 83 CREAR UNA TABLA DINÁMICA EN EXCEL ............................................................................................................................. 83 PARTES DE UNA TABLA DINÁMICA EN EXCEL ........................................................................................................................ 86 DAR FORMATO A UNA TABLA DINÁMICA............................................................................................................................. 87 CREACIÓN DE TABLAS DINÁMICAS .................................................................................................................................... 88

CAMPOS CALCULADOS EN TABLA DINÁMICA ........................................................................ 93 CAMPO CALCULADO OTRO EJEMPLO ................................................................................................................................. 96 CREACIÓN DEL CAMPO CALCULADO ................................................................................................................................... 98 AGRUPAR ELEMENTOS NUMÉRICOS DE UNA TABLA DINÁMICA............................................................................................. 100 AGRUPAR ELEMENTOS SELECCIONADOS DE UNA TABLA DINÁMICA ......................................................................................... 101 AGRUPAR POR FECHAS EN UNA TABLA DINÁMICA ............................................................................................................. 103 DESAGRUPAR ELEMENTOS AGRUPADOS DE UNA TABLA DINÁMICA ......................................................................................... 106 SEGMENTACIÓN DE DATOS EN TABLAS DINÁMICAS ............................................................................................................. 106

19. GRAFICOS DINAMICOS................................................................................................. 108 20. SPARKLINES, EL PODER DE UN GRÁFICO EN TAN SOLO UNA CELDA............................... 110 21. FUNCIONES DE BASE DE DATOS ................................................................................... 112 FUNCIÓN EXCEL BDSUMA........................................................................................................................................... 114 SINTAXIS ................................................................................................................................................................... 114 BDCONTAR EN EXCEL ................................................................................................................................................ 115 SINTAXIS DE LA FUNCIÓN BDCONTAR ........................................................................................................................... 115 EJEMPLO DE LA FUNCIÓN BDCONTAR ........................................................................................................................... 116 MÚLTIPLES CRITERIOS PARA UNA MISMA COLUMNA ........................................................................................................... 118

22. BASE DE DATOS ........................................................................................................... 120 BASE CONCEPTUAL ...................................................................................................................................................... 120 CREACIÓN DE UNA BASE DE DATOS ................................................................................................................................. 121 AGREGAR, ELIMINAR O RESTAURAR REGISTROS.................................................................................................................. 123 CREACIÓN DE COPIA DE LA BASE ..................................................................................................................................... 123 Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 3 de 154

Curso Nivel Avanzado

23. FORMULARIO CON BASE DE DATOS EXISTENTE ............................................................ 124 24. COMO HACER UN RANKING EN EXCEL .......................................................................... 127 RANKING EN ORDEN ASCENDENTE .................................................................................................................................. 131

25. MACROS AUTOMATICAS.............................................................................................. 134 GRABAR UNA MACRO................................................................................................................................................... 134 EJECUTAR LA MACRO ................................................................................................................................................... 135 EJECUTAR DESDE LA BARRA DE ACCESO RÁPIDO ................................................................................................................ 136 ASIGNAR UNA MACRO A UN BOTÓN ................................................................................................................................ 137 ELIMINAR UNA MACRO ................................................................................................................................................. 139 SEGURIDAD DE MACROS ............................................................................................................................................... 140

26. RESOLUCIÓN DE ERRORES............................................................................................ 141 ERROR #¡NULO! ....................................................................................................................................................... 141 ERROR #¡NUM! ........................................................................................................................................................ 141 ERROR #¡REF! ........................................................................................................................................................... 142 ERROR #¡VALOR! ...................................................................................................................................................... 142

27. TECLAS DE ACCESO RÁPIDO ......................................................................................... 143 TECLAS PARA MOVERSE Y DESPLAZARSE POR UNA HOJA DE CÁLCULO O UN LIBRO.................................................................... 143 TECLAS PARA MOVERSE POR UNA HOJA DE CÁLCULO CON EL MODO FIN ACTIVADO ................................................................. 143 TECLAS PARA VER O IMPRIMIR UN DOCUMENTO ............................................................................................................... 144 TECLAS DE DIRECCIÓN DESPLAZARSE POR LA PÁGINA CUANDO ESTÁ AMPLIADA ...................................................................... 144 TECLAS PARA TRABAJAR CON HOJAS DE CÁLCULO, GRÁFICOS Y MACROS ................................................................................ 145 TECLAS PARA INTRODUCIR DATOS EN UNA HOJA DE CÁLCULO .............................................................................................. 145 TECLAS DE DIRECCIÓN DESPLAZARSE UN CARÁCTER HACIA ARRIBA, ABAJO, IZQUIERDA O DERECHA ............................................... 145 TECLAS PARA TRABAJAR EN LAS CELDAS O LA BARRA DE FÓRMULAS...................................................................................... 146 TECLAS PARA APLICAR FORMATO A LOS DATOS ................................................................................................................. 146 TECLAS PARA MODIFICAR DATOS ................................................................................................................................... 147 TECLAS PARA INSERTAR, ELIMINAR Y COPIAR UNA SELECCIÓN.............................................................................................. 148 TECLAS PARA MOVERSE DENTRO DE UNA SELECCIÓN ........................................................................................................... 148 TECLAS PARA SELECCIONAR CELDAS, COLUMNAS O FILAS ..................................................................................................... 148 TECLAS PARA AMPLIAR LA SELECCIÓN CON EL MODO FIN ACTIVADO ..................................................................................... 149 TECLAS PARA SELECCIONAR CELDAS CON CARACTERÍSTICAS ESPECIALES ................................................................................. 150 TECLAS PARA SELECCIONAR UNA HOJA DE GRÁFICOS.......................................................................................................... 150 TECLAS PARA SELECCIONAR UN GRÁFICO INCRUSTADO ....................................................................................................... 151 TECLAS PARA SELECCIONAR ELEMENTOS DE GRÁFICOS ....................................................................................................... 151

28. TECLAS PARA UTILIZAR CON BASES DE DATOS Y LISTAS ................................................ 151 TECLAS PARA TRABAJAR CON UN FORMULARIO ................................................................................................................ 151 TECLAS PARA UTILIZAR AUTOFILTRO ................................................................................................................................ 152 TECLAS PARA ESQUEMATIZAR DATOS ............................................................................................................................... 152

29. TECLAS PARA UTILIZAR CON INFORMES DE TABLA DINÁMICA O GRAFICO DINÁMICO... 152 Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 4 de 154

Curso Nivel Avanzado

TECLAS PARA LOS CAMPOS DE PÁGINA QUE SE MUESTRAN EN UN INFORME DE TABLA DINÁMICA O DE GRÁFICO DINÁMICO ............... 153 TECLAS PARA DISEÑAR UN INFORME DE TABLA O DE GRÁFICO DINÁMICO................................................................................. 153

HERRAMIENTAS DE DATOS 1. VALIDACIÓN DE DATOS EN EXCEL Para prevenir y minimizar que los datos sean introducidos correctamente en una hoja de cálculo, puede especificar qué tipo de datos serán válidos para determinadas celdas o para rangos de celdas. Podemos especificar que sean sólo texto, números, fechas, listas y otros. Si tenemos un grupo de datos de entrada y queremos que los usuarios de un archivo en particular carguen los datos bajo un formato o un rango establecido los podemos “forzar” a través del comando Validación de datos a que registren solo los datos de acuerdo a lo que predefinas, a continuación te explicamos cómo emplear este útil comando: ¿Cómo aplicarlo? Por ejemplo si queremos que los datos de entrada estén limitados a un rango de fechas, “En una base de datos de un grupo de profesionales se requiere que el campo donde se carga la “Fecha de ingreso a la compañía” no sea anterior al año 1995 y no sea superior al día en el cual se estén cargando los datos”; lo primero que debemos hacer es seleccionar el rango de celdas donde vamos a limitar los datos de entrada, para este ejemplo de F3 a F8.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 5 de 154

Curso Nivel Avanzado

Luego vas a la Cinta de opciones escoges la ficha Datos y en la sección Herramienta de datos seleccionas el comando Validación de datos (tal como se ve en la imagen anexa):

Una vez seleccionado el comando se va a mostrar un cuadro de dialogo en el que por defecto se encuentra en la pestaña Configuración, dentro de Criterio de validación en “Permitir:” de la lista desplegable en vas a escoger Fecha, tal como se ve en la siguiente imagen:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 6 de 154

Curso Nivel Avanzado

En “Datos:” vas a dejar el campo por defecto que tiene la lista: “entre” que en efecto es el que se ajusta a las limitantes que vamos a definir, para la Fecha inicial vas a colocar la siguiente fecha: 01/01/1995 y para Fecha Final vas a colocar la función HOY ya que la fecha de ingreso no debe ser superior a la fecha en el momento en que se carguen los datos, por ultimo presionas el botón Aceptar, veamos lo anteriormente explicado mediante la imagen anexa:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 7 de 154

Curso Nivel Avanzado

Listo! Una vez hayas configurado todo de esta manera ya delimitaste los datos de entrada de ese rango de datos, si hacemos una prueba colocando una fecha o un valor distinto al preconfigurado automáticamente Excel nos arrojara un aviso indicándonos que los datos que estamos tratando de ingresar no son válidos:

Esta forma de emplear el comando de Validación de datos es muy útil para los casos en los cuales delimitamos los valores de entrada de una base de datos por lo cual aprovechar este recurso te facilita en gran manera el manejo de bases de datos. Ejemplos:

Validación de un número entero: Entre 10 a 100

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 8 de 154

Curso Nivel Avanzado

Validación de una lista: Primero escribir la lista, después por la opción de lista, seleccionar en Origen donde se encuentra la lista y aceptar

En la celda donde se colocó la validación aparece una lista desplegable con la lista indicada.

Validación de fecha: Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 9 de 154

Curso Nivel Avanzado

Validación personalizada por medio de una fórmula En el cuadro Permitir, haga clic en Personalizada. En el cuadro Fórmula, escriba una fórmula que calcule un valor lógico (VERDADERO para las entradas válidas o FALSO para las no válidas).

Mensaje de Entrada

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 10 de 154

Curso Nivel Avanzado

Mensaje de Salida

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 11 de 154

Curso Nivel Avanzado

2. FUNCION DESREF La función DESREF en Excel nos devuelve una referencia a un rango de celdas que ha sido desplazado respecto a otra referencia que hemos especificado ¿Suena complicado? Vaya que es complicado explicar, pero la función DESREF es uno de esos tesoros escondidos de Excel. La función DESREF devuelve una referencia a un rango que es un número especificado de filas y columnas de una referencia dada, es la que permite buscar la información con base a una posición. También se utiliza esta función en combinación con otras funciones.

SINTAXIS:

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

ref (obligatorio): Referencia en la que se basa la desviación o rango donde se iniciará el desplazamiento. filas (obligatorio): El número de filas a moverse. Si el valor es positivo se moverá hacia abajo y si es negativo se moverá hacia arriba de la celda superior izquierda del resultado. columnas (obligatorio): El número de columnas a moverse. Si el valor es positivo se moverá hacia la derecha y si es negativo se moverá a la izquierda de la celda superior izquierda del resultado. alto (opcional): El número de filas que deseamos que nos devuelva como resultado o el número de filas (alto) que se desea que tenga la referencia devuelta.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 12 de 154

Curso Nivel Avanzado

ancho (opcional): El número de columnas que deseamos que nos devuelva como resultado o el número de columnas (ancho) que se desea que tenga la referencia devuelta. Sintaxis de la función DESREF

De esta manera, para indicar a la función DESREF que necesitamos desplazarnos 5 filas hacia abajo y 3 columnas a la derecha a partir de la celda A1 utilizaremos la siguiente fórmula: =DESREF(A1, 5, 3) Algo muy importante que debo decir sobre la función DESREF en Excel es que si el primer argumento de la función es una referencia a una sola celda entonces la función nos devolverá de manera la referencia a una sola celda. Observa la siguiente imagen:

Ya que la función DESREF nos devuelve la referencia a la celda C4 es posible mostrar su valor en la celda C7. Ahora considera lo siguiente, si en lugar de especificar una sola celda como el primer argumento de la función proporcionamos un rango, entonces nos daremos cuenta que la función DESREF devuelve una referencia a un rango y no podremos mostrar su resultado sino que obtendremos un error:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 13 de 154

Curso Nivel Avanzado

El error no está en la función DESREF sino al intentar desplegar su resultado en la celda C7 cuando en realidad nos está devolviendo una referencia a un rango. Para este ejemplo específico la función DESREF devuelve la referencia C3:C4 y lo puedo demostrar utilizando la función SUMA sobre el resultado de la función DESREF:

Observa que no he cambiado la función DESREF sino que solamente apliqué la función SUMA al rango de celdas devuelto por la función. En este ejemplo la función SUMA hace la operación SUMA(C3:C4) que es precisamente el resultado mostrado en la celda C7. Por esta razón es que frecuentemente observarás que la función DESREF es utilizada en junto con otras funciones.

Ejemplos de la función DESREF Ya hemos visto varios ejemplos de la función DESREF en Excel, solo mostraré algunos ejemplos adicionales para dejar más claro el uso de la función. En el Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 14 de 154

Curso Nivel Avanzado

siguiente ejemplo coloco en el segundo argumento de la función un número negativo que hará un desplazamiento de filas hacia arriba:

Veamos otro ejemplo con validación:

Crear la lista desplegable en Excel El beneficio de actualizar una lista desplegable en Excel de forma automática garantiza que los datos o valores que siga ingresando o modificando en las celdas de los datos, se visualizará inmediatamente en la celda donde se encuentre la lista, esto evita que no se tenga que actualizar de forma manual la información. Ahora que ya tenemos la fórmula que nos devolverá el rango de celdas adecuado podemos iniciar con la creación de la lista desplegable. Selecciona el comando Datos > Herramientas de datos > Validación de datos. Se mostrará el cuadro de diálogo Validación de datos y en la pestaña Configuración selecciona la opción Lista y en el cuadro de texto origen pega la fórmula que acabamos de construir en los pasos anteriores:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 15 de 154

Curso Nivel Avanzado

Para actualizar los valores de la lista desplegable será suficiente con agregar un nuevo elemento en la columna A para que sea incluido automáticamente dentro de la lista desplegable.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 16 de 154

Curso Nivel Avanzado

Lo mismo sucederá si eliminamos algún elemento de la columna A ya que será excluido de la lista desplegable. La única condición es que todos los valores de la columna A sean contiguos, es decir, que no existan celdas en blanco entre ellos. Si el valor tiene que moverse una posición a la izquierda sería un número negativo, y si se mueve a la derecha sería un número positivo.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 17 de 154

Curso Nivel Avanzado

En nuestro ejemplo práctico la fórmula aplicada fue: =COINCIDIR(L10;$E$7:$E$24;0) Para buscar la posición de la venta más alta =K.ESIMO.MAYOR($E$7:$E$24;G10) Para encontrar la compra más alta =DESREF($E$6;H10;-3) Para mostrar la información en base a la posición

3. Función SI.ERROR La Función Si.Error que permite personalizar los errores de Excel que aparecen en las diferentes fórmulas, se pueden tratar con la función Si.Error, ya que esta función permite parametrizar el comportamiento en Excel en el caso que una formula presente un error.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 18 de 154

Curso Nivel Avanzado

4. FUNCIONES DE BUSQUEDA Y REFERENCIA A veces uno necesita buscar un tipo de información en una tabla o lista, si esta posee demasiado contenido se hace necesario tener una herramienta para tal fin. EXCEL posee varias formas de búsqueda: Tenemos las funciones: BUSCARV, BUSCARH, INDICE y COINCIDIR.

5. Función Excel BUSCARV: Busca de manera Vertical Busca un valor en una lista con rótulos de fila. Utilice BUSCARV cuando la lista tenga rótulos de fila en la columna del extremo izquierdo y desee buscar un valor de otra columna, basándose en el rótulo de la fila. Debe ordenar la lista antes de usar BUSCARV.

Sintaxis de la función de Excel BUSCARV

Viéndola desde un punto de vista más simple la función BUSCARV se puede traducir en: =BUSCARV(¿Qué criterio referencial deseas buscar?;¿Dónde buscas ese criterio referencial?;¿El valor de qué columna quieres que te muestre?;¿Quieres que el valor sea exacto o aproximado?).

Estructura de la función de Excel BUSCARV La Función BUSCARV tiene 4 argumentos: Primero el dato que queremos buscar (que debe estar en la primera columna donde deseamos buscar); Segundo es el Rango de los datos donde vamos a buscar el dato; Tercero es el número de la columna de la variable que queremos que Excel nos devuelva, y el Cuarto toma el

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 19 de 154

Curso Nivel Avanzado

valor verdadero o falso dependiendo si estamos haciendo una coincidencia exacta o aproximada. Valor_buscado: se entiende como el criterio que se va a buscar en la primera columna de la matriz de tabla. Valor buscado puede ser un valor o una referencia. Si valor_buscado es inferior al menor de los valores de la primera columna de matriz_buscar_en, BUSCARV devuelve al valor de error #N/A.

Debemos recordar que el valor del primer argumento de la función será buscado siempre en la primera columna de la tabla de datos. No es posible buscar en una columna diferente que no sea la primera columna. Matriz_buscar_en: Dos o más columnas de datos. Usa una referencia a un rango o un nombre de rango. Los valores de la primera columna de matriz_buscar_en son los valores que busca valor buscado. •

Es importante resaltar que al momento de seleccionar la matriz donde buscaremos la información, el valor referencial debe estar en la primera columna.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 20 de 154

Curso Nivel Avanzado

Estos valores pueden ser texto, números o valores lógicos. Las mayúsculas y minúsculas del texto son equivalentes. El segundo argumento de la función indica la totalidad del rango que contiene los datos. En este rango es importante asegurase de incluir la columna que vamos a necesitar como resultado.

Si

la tabla tiene encabezados, lo más recomendable es excluirlos del rango para evitar que la función considere a los títulos dentro de la búsqueda. El tercer argumento indica la columna que deseamos obtener como resultado: Indicador_columnas: Es el valor del número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_buscar_en y así sucesivamente. Si indicador_columnas es: •

Si es inferior a 1, la función Excel BUSCARV devuelve al valor de error #VALUE!

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 21 de 154

Curso Nivel Avanzado •

Si es superior al número de columnas de matriz_buscar_en, función Excel BUSCARV devuelve el error #REF!

Ordenado: Es el valor lógico que especifica si la función Excel BUSCARV va a buscar una coincidencia exacta o aproximada: •

Si se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor buscado.

•

Los valores de la primera columna de matriz_buscar_en deben estar clasificados según un criterio de ordenación ascendente; en caso contrario, es posible que la función Excel BUSCARV no devuelva el valor correcto. Para obtener más información.

•

Si es FALSO, la función Excel BUSCARV sólo buscará una coincidencia exacta. En este caso, no es necesario ordenar los valores de la primera columna de matriz_buscar_en. Si hay dos o más valores en la primera columna de matriz_buscar_en, se utilizará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 22 de 154

Curso Nivel Avanzado

En esta imagen puedes ver que la función BUSCARV encuentra el valor “BR” en la primera columna pero nos devolverá el valor de la tercera columna tal como lo indicamos en los argumentos de la función. Si la función BUSCARV no encuentra el valor en la columna uno, devolverá el error #N/A. Para mejor comprensión de la función Excel BUSCARV veamos el siguiente ejemplo: ¡En Práctica! Función Excel BUSCARV Se quieren obtener los valores que están asociados a cada fruta, para ello utilizaremos la función Excel BUSCARV. En la imagen anexa se muestra la tabla de donde se desean obtener los datos. Para obtener los datos deseados insertamos en una celda de nuestra preferencia la función Excel BUSCARV: =BUSCARV(E3;$B$3:$C$12;2;FALSO)

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 23 de 154

Curso Nivel Avanzado

E3: Representa el valor buscado que en este caso es el nombre de la fruta. $B$3:$C$12: Representa la matriz donde se van a buscar los datos. 2: Representa el indicador de columnas de donde se va a extraer la información FALSO: Representa de qué forma se quieren los datos y en efecto se desean de manera exacta. De forma visual la función Excel BUSCARV aplicada se representa de la siguiente manera:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 24 de 154

Curso Nivel Avanzado

6. Función Excel BUSCARH: Busca de manera Horizontal

La función BUSCARH: busca un valor en la fila superior de una tabla o una matriz de valores y, a continuación, devuelve un valor en la misma columna de una fila especificada en la tabla o en la matriz. Use BUSCARH cuando los valores de comparación se encuentren en una fila en la parte superior de una tabla de datos y desee encontrar información que se encuentre dentro de un número especificado de filas. Previamente se debe ordenar la lista antes de usar BUSCARH. Sintaxis =BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas; ordenado) Valor_buscado: es el valor que se busca en la primera fila de matriz_buscar_en. Puede ser un valor, una referencia o una cadena de texto. Matriz_buscar_en: es una tabla de información en la que se buscan los datos. Utilice una referencia a un rango o el nombre de un rango. Nota: •

•

•

Los valores de la primera fila del argumento matriz_buscar_en pueden ser texto, números o valores lógicos. Si el argumento ordenado es VERDADERO, los valores de la primera fila del argumento matriz_buscar_en deberán colocarse en orden ascendente: ...-2; -1; 0; 1; 2;..., A-Z, FALSO, VERDADERO; de lo contrario, es posible que BUSCARH no devuelva el valor correcto. El texto en mayúsculas y minúsculas es equivalente.

Indicador_filas: es el número de fila en matriz_buscar_en desde el cual se deberá devolver el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la segunda fila en matriz_buscar_en y así sucesivamente. Si indicador_filas es menor Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 25 de 154

Curso Nivel Avanzado

que 1, BUSCARH devuelve el valor de error #¡VALOR!; si indicador_filas es mayor que el número de filas en matriz_buscar_en, BUSCARH devuelve el valor de error #¡REF! Ordenado: es un valor lógico que especifica si desea que el elemento buscado por la función BUSCARH coincida exacta o aproximadamente. Si ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no se encuentra un valor exacto, se devuelve el mayor valor que sea menor que el argumento valor_buscado. Si ordenado es FALSO, la función BUSCARH encontrará el valor exacto. Si no se encuentra dicho valor, devuelve el valor de error #N/A. ¡En Práctica! Función Excel BUSCARV =BUSCARH($D6;$D$16:$G$18;2;FALSO)

$D6 Represena el valor buscado que seria los años de antiguedad del empleado $D$6:$D$16 Representa el rango de datos donde se buscaria la información de manera Horizontal. Numero 2: representa el numero de Filas donde se encuentra la información. Falso: Para encontrar la coincidencia exacta. Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 26 de 154

Curso Nivel Avanzado

7. BUSCARV CON FUNCION SI, SOBRE 2 TABLAS DE DATOS

Analizaremos el caso de una institución financiera que ha establecido una nueva regla para otorgar créditos a sus clientes. La empresa otorgará dos tipos de créditos de acuerdo al plazo ya sea de 24 meses o de 36 meses. Dependiendo del plazo acordado y el monto del mismo se cobrará una comisión al cliente.

El plazo del crédito es la variable que determina la tabla de comisiones que debemos utilizar. En base a dicho valor es que tomaremos la decisión sobre cuál tabla utilizar y para ello utilizaremos la función SI.

El plazo del crédito es la variable que determina la tabla de comisiones que debemos utilizar. En base a dicho valor es que tomaremos la decisión sobre cuál tabla utilizar y para ello utilizaremos la función SI. Para obtener el valor de la comisión debemos utilizar la tabla ubicada en el rango A5:A9: UtIlizaremos funcion Logica SI.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 27 de 154

Curso Nivel Avanzado

Esta fórmula evaluará el valor de la celda B13 y en caso de ser igual a 24 regresará el rango de la tabla de 24 meses, de lo contrario regresará el rango de la tabla de 36 meses. Ahora incluyamos esta fórmula como el segundo argumento de la función BUSCARV:

La función SI proveerá el rango de la tabla adecuada donde se deberá buscar el valor de la celda C13 por la función BUSCARV. Observa que esta función obtiene el valor adecuado.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 28 de 154

Curso Nivel Avanzado

Reemplazar BuscarV por Indice y Coincidir La correcta combinación de las funciones Indice y la función Coincidir nos arroja como resultado la no implementación de la función BuscarV cuando se trata de consultar registros, ya que el potencial de estas dos funciones mencionadas, en algunos casos supera a la función BuscarV de Excel. Con la función BuscarV no se puede consultar o extraer información que se encuentren en columnas a la izquierda del campo que se esté consultando como referencia, con las funciones Indice y Coincidir de Excel si se puede lograr esto INDICE y COINCIDIR, Unidas estas funciones devuelven un valor basado en el rótulo de una fila o de una columna. El Asistente para búsquedas utiliza INDICE y COINCIDIR en las fórmulas que crea.

8. Función INDICE INDICE: devuelve una referencia a una celda en la intersección entre una fila y una columna determinadas dentro de un rango. La función INDICE(): Sintaxis 2 =INDICE(ref;núm_fila;núm_columna;núm_área) Ref: selecciona un rango en el argumento ref desde el cual se devolverá la intersección de núm_fila y núm_columna. Núm_fila: es el número de la fila en el argumento ref desde la cual se devolverá una referencia. Núm_columna: es el número de la columna en el argumento ref desde la cual se devolverá una referencia. Núm_área: selecciona un rango en el argumento ref desde el cual se devolverá la intersección de núm_fila y núm_columna. La primera área seleccionada o

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 29 de 154

Curso Nivel Avanzado

introducida se numera con 1, la segunda con 2 y así sucesivamente. Si se omite núm_área, INDICE usa área 1. La referencia devuelta por INDICE es la intersección entre núm_fila y núm_columna. Si se define núm_fila o núm_columna como 0 (cero), INDICE devuelve la referencia de toda la fila o columna, según corresponda. La flexibilidad que nos ofrece la función INDICE nos permite utilizarla en otros escenarios para buscar un valor y regresar múltiples resultados. Por otro lado, la función COINCIDIR combinada con la función BUSCARV es una excelente alternativa cuando deseamos hacer una búsqueda sobre una tabla y elegir la columna que necesitamos como resultado a partir de una lista desplegable.

9. Función COINCIDIR Busca un Valor y devuelve su posición relativa dentro de un rango de Búsqueda basándose en el valor que desea localizar, es decir de un elemento en una matriz que coincida con un orden especificado, y no el valor en sí. Utilice COINCIDIR en lugar de las funciones BUSCAR cuando necesite conocer la posición de un elemento en un rango en lugar del elemento en sí. Esta función tiene tres argumentos y solo dos de ellos son obligatorios. Sintaxis =COINCIDIR(valor_buscado;matriz_buscada;tipo_de_coincidencia) Valor_buscado: es el valor que se usa para encontrar el valor deseado en la tabla. • Puede ser un valor (número, texto o valor lógico) o una referencia de celda a un número, a un texto o a un valor lógico. Matriz_buscada: es un rango múltiple de celdas que contienen posibles valores a buscar, puede ser una matriz o una referencia matricial

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 30 de 154

Curso Nivel Avanzado

Tipo_de_coincidencia: es el número -1, 0 ó 1 y especifica cómo hace coincidir Microsoft Excel el valor_buscado con los valores de matriz_buscada. •

•

•

•

Si tipo_de_coincidencia es 1, COINCIDIR encuentra el mayor valor que es inferior o igual al valor_buscado. Los valores en el argumento matriz_buscada deben colocarse en orden ascendente: ...-2; -1; 0; 1; 2;...AZ; FALSO; VERDADERO. Si tipo_de_coincidencia es 0, COINCIDIR encuentra el primer valor que es exactamente igual al valor_buscado. Los valores en matriz_buscada pueden estar en cualquier orden. Si tipo_de_coincidencia es -1, COINCIDIR encuentra el menor valor que es mayor o igual al valor_buscado. Los valores en matriz_buscada deben colocarse en orden descendente: VERDADERO; FALSO; Z-A;...2; 1; 0; -1; -2;... y así sucesivamente. Si se omite tipo_de_coincidencia, se supondrá que es 1.

Las funciones INDICE y COINCIDIR pueden usarse combinadas para encontrar un valor en una lista basándose en el rótulo de una fila o de una columna, o en ambos. El INDICE trae la información, COINCIDIR trae la posición del criterio buscado. Se está buscando el número telefónico que se encuentra en la columna A y el criterio de búsqueda es José Úbeda que se encuentra en la columna C

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 31 de 154

Curso Nivel Avanzado

Se obtiene el siguiente resultado:

10.

¿CÓMO BUSCAR A PARTIR DE DOS VARIABLES EN EXCEL?

Supongamos que tenemos como punto de inicio una tabla de datos organizada como la de la figura adjunta, es decir una tabla no organizada como una base de datos sino cómo un sumario de datos por categorías. La primera fila y la primera columna describen el contenido de cada fila y columna respectivamente. Nuestro objetivo será conocer cuál es el valor intersección entre dos variables, por ejemplo, cuál es el valor de alcanzado en Julio en América, para esta forma buscar a partir de dos variables en Excel.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 32 de 154

Curso Nivel Avanzado

Para conocer el valor intersección de dos variables emplearemos la función Excel ÍNDICE para localizar entre el área de datos la fila y columna de nuestro interés. Para conocer cuál es la fila y columna de nuestro interés emplearemos la función Excel COINCIDIR. En la celda donde deseemos realizar nuestra búsqueda aplicaremos la siguiente fórmula: Fórmula para Buscar a partir de dos Variables =INDICE(Rango_donde_se_hará_la_búsqueda; COINCIDIR(Valor_Columna_a_Buscar;Rango_Primera_Columna;0); COINCIDIR(Valor_Fila_a_Buscar;Rango_Primera_Fila;0) En esta fórmula: •

•

• •

•

Rango_donde_se hará_la_búsqueda, es igual al rango donde deseamos realizar nuestra búsqueda a partir de dos variables, excluyendo la primera fila y la primera columna que empleamos para identificar nuestros datos. Valor_Columna_a_Buscar, es la variable que deseamos buscar en una columna específica. Puede ser una referencia a una celda, un valor numérico o de texto. Rango_Primera_Columna, es el rango donde se encuentra la columna que etiqueta nuestros datos. Valor_Fila_a_Buscar, es la variable que deseamos buscar en una fila específica. Puede ser una referencia a una celda, un valor numérico o de texto. Rango_Primera_Fila, es el rango donde se encuentra la fila que etiqueta nuestros datos.

En nuestro ejemplo práctico la fórmula aplicada fue: =INDICE($B$2:$F$13;COINCIDIR(“Julio”;$A$2:$A$13;0);COINCIDIR(“América”;$B$ 1:$F$1;0)) Donde: El Rango_donde_se hará_la_búsqueda, es igual al rango $B$2:$F$13, tal como la zona sombreada en verde en la imagen anexa. Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 33 de 154

Curso Nivel Avanzado

El Valor_Columna_a_buscar, es el valor de texto “Julio”, conforme la Primera_Columna, en nuestro caso es igual al rango $A$2:$A$13, tal como la zona sombreada en verde en la imagen anexa. El Valor_Fila_a_buscar, es el valor de texto “América”, conforme el Rango_Primera_Fila, en nuestro caso es igual al rango $B$1:$F$1, tal como la zona sombreada en verde en la imagen anexa.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 34 de 154

Curso Nivel Avanzado

Una vez aplicada esta fórmula podremos realizar búsquedas de dos variables en Excel con tan solo cambiar los argumentos de los valores de fila y columna a buscar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 35 de 154

Curso Nivel Avanzado

11. CONSOLIDACIÓN

Consolidación de Datos, Unir Tablas Eficientemente Consolidar es un comando que da como resultado un resumen de datos. La Consolidación NO puede hacerse en la misma Hoja. Debe usar otra Hoja u otro Libro para copiar los datos que va a Consolidar. Análisis de Ventas Supongamos que nos encontramos en una fábrica de Bicicletas, la cual tiene 3 líneas diferentes de productos denominados así: Playera, Doméstica y Carrera, cada línea de bicicletas maneja la misma variedad de colores. Para el mes de Julio y a fin de mejorar el desempeño de la compañía en el segundo semestre, queremos analizar el total de unidades vendidas mes a mes en la primera mitad del año, dependiendo del color de los productos, el problema radica en que la información solicitada llega en diferentes archivos que debemos unificar en uno solo. Solución Lo primero es abrir el archivo donde se encuentra los datos a consolidar, a continuación se muestran las tablas para cada línea de producto:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 36 de 154

Curso Nivel Avanzado

Nuestro objetivo: unir o consolidar los datos de las tres tablas presentadas arriba en una única tabla Consolidación de Datos En una hoja nueva nos ubicamos en la celda activa donde queremos que aparezca la tabla de consolidados, nos dirigimos a la pestaña Datos y en el grupo Herramientas de Datos presionamos el botón Consolidar.

Automáticamente aparece el siguiente cuadro de dialogo:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 37 de 154

Curso Nivel Avanzado

Y ahora agregamos uno a uno los rangos donde se encuentran las tablas, para ello damos clic izquierdo en la caja de texto de Referencia y nos dirigimos a la primera hoja, allí seleccionamos toda la tabla, incluyendo las etiquetas de columna.

En la caja de texto Referencia, se muestra el nombre de la hoja y el rango donde se encuentra la tabla, para finalizar presionamos el botón agregar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 38 de 154

Curso Nivel Avanzado

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 39 de 154

Curso Nivel Avanzado

Podemos apreciar que las tres tablas han sido agregadas exitosamente a la caja de texto Todas las Referencias, ahora vamos a elegir con que función vamos a consolidar los datos. Funciones Existen 11 funciones que podemos implementar en la consolidación de datos: SUMA: Suma los productos de las tres líneas por cada color. CUENTA: Calcula el número de entradas para cada mes y por color PROMEDIO: Calcula el promedio entre las tres líneas por cada color. MAX: Arroja el valor máximo para cada color en cada mes. MIN: Arroja el valor mínimo para cada color en cada mes. PRODUCTO: Multiplica los valores entre sí, dependiendo el color y el mes. CONTAR NÚMEROS: Cuenta las celdas con Números. DESVEST: Desviación Estandar de una Muestra. DESVESTP: Desviación Estandar Poblacional. Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 40 de 154

Curso Nivel Avanzado

VAR: Varianza de una muestra. VARP: Varianza Poblacional.

La función que debemos elegir, depende completamente de nuestras necesidades, sin embargo, para este caso la más adecuada es la función Suma. Nota: ¡IMPORTANTE! Si dejamos las opciones Fila Superior y Columna Izquierda habilitadas, se copiará en la tabla de consolidado estas etiquetas, Además, la opción Crear Vínculos con los Datos de Origen, permite que las tablas queden vinculadas es decir, si hay algún cambio en alguna de las tablas esto se verá reflejado en la tabla de consolidados automáticamente, por lo anterior, es recomendable habilitar las tres opciones. Por último, presionamos el botón aceptar. Y posterior a ello se crea la siguiente tabla:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 41 de 154

Curso Nivel Avanzado

Como podemos ver en las etiquetas de fila y columna aparecen botones para revisar en detalle la consolidación de los datos, para mostrarnos como se calculó la tabla.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 42 de 154

Curso Nivel Avanzado

Si se quiere hacer un análisis más profundo podemos implementar gráficos y filtros todo depende del escenario en el que nos encontremos, además, con esta tabla existe la posibilidad de encontrar conclusiones significativas, como el color más vendido, el color menos vendido, el mes con menores ventas, etc. La herramienta Consolidar es altamente recomendada, imaginemos el tiempo que podemos llegar a ahorrar si en lugar de 3 archivos son 10 o más.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 43 de 154

Curso Nivel Avanzado

Para el siguiente ejemplo: Suponga que tiene en la Hoja 1, cuatro tablas representando las ventas por vendedores del mes de enero y quiere resumir: El Total, Máximo y Mínimo por vendedor. Seleccione los nombres de la primera tabla que se encuentra en la Hoja 1 y que va a Consolidar y cópiela 3 veces en la Hoja 2, de arriba hacia abajo.

Se copia 3 veces para calcular el Total, Máximo y Mínimo. En la primera tabla de la Hoja 2, seleccione las celdas que contendrán las cantidades y haga clic en Menú Datos, Herramientas de Datos - opción Consolidar

En el cuadro de diálogo, seleccione la Función Suma. Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 44 de 154

Curso Nivel Avanzado

Coloque el cursor en la sección Referencia y luego seleccione en la primera tabla de la Hoja 1, el primer rango de datos para consolidar, Agregue y continúe el mismo procedimiento con las tablas restantes:

En la Hoja 2 aparecerá la nueva tabla con la información consolidada, según los parámetros predeterminados. Al activar la casilla de verificación Crear vínculos con los datos de origen, en el cuadro de diálogo Consolidar, todos los rangos de datos, hojas o libros seleccionados en la consolidación, quedarán vinculados y se actualizará automáticamente la tabla de destino.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 45 de 154

Curso Nivel Avanzado

Realice el mismo procedimiento para calcular el Máximo y el Mínimo. Cambié en la Función Suma por Máx o Mín. Las tablas de la Hoja 2 mostrarán los resultados de la Consolidación: Totales, Mínimo y Máximo.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 46 de 154

Curso Nivel Avanzado

12.

SUBTOTALES Y ESQUEMATIZACION

Elaboración de subtotales Los subtotales constituyen una manera fácil y rápida para resumir datos en una lista de Excel. Ejemplo: si tiene una lista de información de ventas que incluye fecha, número de cuenta, producto, unidad, precio y ganancia. Puede especificar si desea ver subtotales por cuenta, o subtotales por producto, etc. con el comando Subtotales de Excel, no necesita crear las fórmulas. Excel crea la fórmula, inserta la fila (o filas) con el subtotal y esquematiza los datos de manera automática. Usted puede formatear los datos resultantes, graficarlos e imprimirlos con facilidad. Pasos: 1.

Ordene la lista con relación al resumen que desea en los subtotales.

2.

Sitúe el cursor dentro de la base de datos en cualquier celda que posea datos.

3.

En la cinta de opciones, seleccione la ficha Datos, en la herramienta Esquema, seleccione el ícono Subtotal.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 47 de 154

Curso Nivel Avanzado

4.

En el cuadro de diálogo Subtotal seleccionar: a. Para cambio en: El campo por donde ordenó la base de datos. b. Usar función: Elija entre las funciones que nos brinda Excel. c. Agregar subtotal a: marcar la casilla de verificación por el campo donde se desea totalizar. Para crear subtotales para más de una columna, por ejemplo: seleccione columnas adicionales en la lista Agregar subtotal a.

5.

Haga clic en Aceptar. Excel crea los subtotales; un total principal aparece en la parte inferior de la lista.

NOTA: Para eliminar rápidamente los subtotales de su lista, seleccione una celda dentro de la tabla. Después elija Datos, opción Subtotales, clic en el botón Quitar todos.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 48 de 154

Curso Nivel Avanzado

Elaboración de subtotales anidados Si desea subtotales adicionales dentro de cada grupo (un subtotal anidado), usted puede crear varios grupos de subtotales. Por ejemplo, puede totalizar todas las cuentas e incluir también subtotales para cada producto dentro de una cuenta. Pasos: 1. 2.

3. 4. 5.

6.

Ordene la lista por las dos o más columnas que desea que contengan subtotales. Proceda a entrar en Subtotal de la forma que se indicó en el paso anterior, en la caja de diálogo en la lista desplegable Para cada cambio en: seleccione el campo. Clic en aceptar, para aceptar subtotales para su primera columna ordenada. Proceda a entrar de nuevo. En la lista desplegable Para cada cambio en, seleccione la columna para el segundo grupo de subtotales. Después desmarque la casilla de verificación Reemplazar subtotales actuales. Clic en aceptar.

Uso de varias funciones de resumen Además de la función SUMA (utilizada para crear subtotales en las listas de Excel), otros tipos de funciones de Excel resultan útiles también. Por ejemplo: quizá desee utilizar la función CONTAR para resumir el número de los elementos de la lista, PROMEDIO para obtener un promedio de los valores de la lista y MAX para conocer el valor más grande de una lista. Pasos: 1. 2.

Proceda a entrar en Subtotal de la forma que se indicó en el paso anterior. Seleccione otra función en la lista en Usar función: Suma, Cuenta, Promedio, Máximo, Mínimo, entre otras.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 49 de 154

Curso Nivel Avanzado

Si está utilizando un subtotal anidado acuérdese de desmarcar la casilla de verificación Reemplaza subtotales actuales y después haga clic en Aceptar. Excel inserta una fila de subtotal adicional con el nuevo cálculo.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 50 de 154

Curso Nivel Avanzado

13.

HERRAMIENTAS DE ANÁLISIS

Herramientas utilizadas para la búsqueda óptima y análisis de resultados.

14.

ESCENARIOS

Los Escenarios son parte de una serie de comandos a veces denominados herramientas de análisis. Un Escenario es un conjunto de valores que Microsoft Excel guarda y puede sustituir automáticamente en la hoja de cálculo. Puede utilizar los Escenarios para prever el resultado de un modelo de hoja de cálculo. Puede crear y guardar diferentes grupos de valores en una hoja de cálculo y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos resultados. Seleccione Escenarios desde el Menú Herramientas, opción Escenarios.

Crear un escenario: • Haga clic en la ficha Datos, en el grupo Herramientas de datos, haga clic en la flecha descendente del botón Análisis Y si. • En el menú desplegable, haga clic sobre la opción Administración de escenarios. • Se activa el cuadro de diálogo Administrador de escenarios.

En la ventana Administrador de escenarios se indicará si existen o no escenarios definidos, para crear un escenario presione el botón Agregar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 51 de 154

Curso Nivel Avanzado

Crear escenarios: Supongamos, por ejemplo, que desea preparar un presupuesto, pero no sabe con exactitud sus ingresos. En este caso, podrá definir valores diferentes para dichos ingresos y, seguidamente, pasar de un escenario a otro para realizar un análisis. En la siguiente tabla guarde un Escenario original seleccionando las Cantidades, que posteriormente va a cambiar.

Seleccione el rango de celdas B2:B5, haga clic en Menú Herramientas, opción Escenarios. En el Administrador de Escenarios haga clic en Agrega.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 52 de 154

Curso Nivel Avanzado

En Nombre del escenario: coloque el nombre que le va a asignar a su escenario, en este caso Cantidad Original. Y acepte.

La siguiente ventana mostrará los valores de las celdas previamente seleccionadas. Acepte.

Se regresará a la ventana Administrador de escenarios. Mostrando su nuevo Escenario.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 53 de 154

Curso Nivel Avanzado

Cambie los datos de la Columna Cantidad y guarde los nuevos datos en un Nuevo Escenario. En el Administrador de escenarios haga clic en Agregar.

Coloque el nombre a su nuevo Escenario, en este caso Cantidad Nuevo. Acepte.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 54 de 154

Curso Nivel Avanzado

En la siguiente ventana podrá observar sus dos Escenarios.

Seleccione Cantidad Original y haga clic en Mostrar, verá como las cantidades de la columna Cantidad regresan a sus valores originales. Agregar Crea nuevos escenarios. Eliminar Elimina los escenarios seleccionados. Modificar Permite modificar algunas características del escenario. Combinar Permite combinar los escenarios de otras hojas o incluso de otros libros. • Resumen Crea un informe tipo resumen o tipo tabla dinámica de los escenarios creados.

• • • •

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 55 de 154

Curso Nivel Avanzado

Creación de un Informe tipo resumen 1. Cree todos los escenarios que necesitará para el informe, repitiendo los pasos anteriores 3, 4 y 5. 2. En la ventana Administrador de escenarios, presione el botón Resumen. En seguida se mostrará el resumen del escenario a manera de autoesquema

Evaluando Escenarios con Excel Los escenarios nos permiten analizar un resultado generado en base a un conjunto de celdas variables. Los escenarios en Excel permiten un máximo de 32 variables, pero podemos crear tantos escenarios como sea necesario. Para este ejemplo utilizaré el caso de un préstamo personal en donde me interesa conocer la cantidad que debo pagar dependiendo el plazo elegido. Para ello utilizo la función PAGO y en las celdas superiores he colocado los argumentos de dicha función de la siguiente manera:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 56 de 154

Curso Nivel Avanzado

El Administrador de Escenarios en Excel Ahora me interesa saber cómo cambia la mensualidad si cambio el plazo de pago. Para iniciar con la creación de escenarios debo pulsar el botón Análisis Y si que se encuentra en la ficha Datos y dentro de las opciones mostradas seleccionar Administrador de escenarios.

Al seleccionar esta opción se mostrará el cuadro de diálogo Administrador de escenarios y lo primero que debemos hacer es pulsar el botón Agregar para mostrar el cuadro de diálogo Agregar escenario.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 57 de 154

Curso Nivel Avanzado

En este cuadro de diálogo comenzaré por asignar un nombre a mi escenario, que en este caso será 12 Meses. En el cuadro de texto Celdas cambiantes debo seleccionar aquellas celdas que afectan el resultado de la fórmula PAGO, que es el rango $B$1:$B:$3. Al oprimir el botón Aceptar se mostrará un nuevo cuadro de diálogo llamado Valores del escenario que me permitirá ingresar los valores específicos del escenario recién creado. Es posible cambiar todos los valores, pero para este ejemplo solamente modificaré el plazo que tendrá el valor 12:

Ya que voy a agregar escenarios adicionales oprimiré el botón Agregar y Excel mostrará de nueva cuenta el cuadro de diálogo Agregar escenario y volveré a repetir estos mismos pasos para crear nuevos escenarios para los plazos de 18, 36 y 48 meses. Una vez que he terminado de crear el último escenario debes pulsar el Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 58 de 154

Curso Nivel Avanzado

botón Aceptar para regresar al Administrador de escenarios donde podrás ver una lista de todos los escenarios creados:

Probar los Escenarios creados Ya con los escenarios creados será suficiente seleccionar alguno de ellos y oprimir el botón Mostrar para aplicar los valores del escenario al resultado de la fórmula PAGO. Observa cómo va cambiando el resultado de la celda B4 de acuerdo al escenario elegido:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 59 de 154

Curso Nivel Avanzado

Para este ejemplo solamente he modificado los valores del plazo en cada escenario, sin embargo se podrían crear escenarios donde cambie tanto el plazo como la tasa de interés. Las combinaciones posibles son muchas y solamente estarán restringidas a tus necesidades de análisis. Una desventaja de los escenarios es que tienes que crear manualmente cada uno de ellos y podría llegar a ser un trabajo muy laborioso. En caso de que tuvieras que realizar un análisis de dos variables te recomiendo considerar la opción de Tablas de datos ya que evitará la creación manual de escenarios.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 60 de 154

Curso Nivel Avanzado

Crear botón de escenario en la barra de tareas Haga clic en personalizar barra de tareas, mas comandos y comandos que no están en la cinta de opciones. Seleccionar escenario y pulsar el botón agregar.

15.

BUSCAR OBJETIVO

Excel 2010 incluye varias herramientas de análisis de datos y Buscar objetivo es una técnica utilizada para encontrar fácilmente el número que cumple las condiciones necesarias para alcanzar un objetivo. Esta herramienta te ayudará a hacer muchas pruebas de valores en una fórmula hasta encontrar el valor exacto que cumpla con las condiciones establecidas. Es decir, si sabemos el resultado de una fórmula, pero no estamos seguros del valor de uno de sus argumentos, entonces Buscar objetivo nos ayudará a probar Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 61 de 154

Curso Nivel Avanzado

diversos escenarios hasta encontrar el valor exacto que necesitamos. Por ejemplo, supongamos que tenemos el siguiente problema.

Tengo un número que multiplicado por otro número X me deberá dar el resultado mostrado. Aunque este es un problema muy fácil de resolver matemáticamente será de utilidad para mostrar la funcionalidad de Buscar objetivo. Empezaré por definir una fórmula importante. En la celda C3 colocaré la fórmula que se deberá cumplir para obtener el resultado deseado, es decir, =B1*B2 deberá dar como resultado 439482.

Por ahora el resultado en C3 será cero, porque no hay valor en la celda B2 sin embargo, al momento de ir buscando el valor adecuado para B2, el valor de la celda C3 llegará a ser el mismo que B3 cuando se haya encontrado el valor correcto.

El comando Buscar objetivo Ahora deberás hacer clic en la ficha Datos y seleccionar Análisis Y si donde se desplegará un menú de opciones y deberás elegir Buscar objetivo.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 62 de 154

Curso Nivel Avanzado

De inmediato se mostrará el cuadro de diálogo Buscar objetivo donde deberás llenar los siguientes cuadros de texto.

Definir la celda: Es la celda que contiene la fórmula Con el valor: Este es el número que deseamos como resultado de la fórmula. Para cambiar la celda: La celda que será modificada hasta tener el resultado deseado. Una vez que se han establecido los parámetros debes hacer clic en el botón Aceptar y Excel comenzará a realzar los cálculos necesarios. Cuando haya encontrado un resultado adecuado se mostrará el cuadro de dialogo Estado de la búsqueda de objetivo.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 63 de 154

Curso Nivel Avanzado

Este cuadro de diálogo simplemente notifica que se ha llegado al objetivo modificando la celda especificada. Al cerrar el cuadro de diálogo podrás observar el resultado. Para nuestro ejemplo Excel fue haciendo diversas pruebas hasta llegar al resultado de 520.0970 que es el número que multiplicado por 845 nos dará el resultado esperado.

Veamos otro ejemplo de Buscar Objetivo: Una Empresa tiene como meta en el 1er trimestre de llegar en ventas a Bs. 6.000.000, pero la meta no se cumplió. Vamos a definir para el 2do. Trimestre que debemos llegar a la meta de 6.500.000 en ventas.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 64 de 154

Curso Nivel Avanzado

Copiar la misma tabla en desde la celda G2 para realizar el ejercicio Vamos a sumir que las ventas del producto 1, 3, 4 y 5 están dentro del rango de ventas y se mantienen, las que deben variar son las del producto 2, que son las mínimas ventas del trimestre 1. Vamos a utilizar la Herramienta de Buscar Objetivo: Datos + Análisis y Si + Buscar Objetivo 1) La celda que va a ser objetivo tiene que tener una formula, en este caso sería la celda J11, debemos posicionarnos allí. =SUMA(J5:J9) 2) La celda con el valor, es el valor al que queremos llegar en la meta, debemos escribirlo de forma manual: 6.500.000 3) Para cambiar la celda, es la celda que debe variar en este caso producto 2 seleccionamos la celda H6 Pulsamos Aceptar y veamos cómo nos calcula la cantidad de unidades que deben ser vendidas para llegar a la meta.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 65 de 154

Curso Nivel Avanzado

El resultado sería el siguiente:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 66 de 154

Curso Nivel Avanzado

16. SOLVER, MUCHO MÁS QUE UN COMPLEMENTO DE ANÁLISIS DE HIPÓTESIS. Aquellos usuarios que tradicionalmente han empleado versiones anteriores de Excel con seguridad conocen el gran complemento Solver, un excelente aliado para el modelado analítico de ciertos problemas de negocio. Básicamente esta herramienta de análisis nos permite puede buscar cual es el valor óptimo para una fórmula en una celda, denominada celda objetivo, en una hoja de cálculo. Microsoft Excel 2010 incluye una nueva y mejorada versión de Solver, que nos permitirá llevar a cabo análisis de hipótesis con funcionalidades de consulta de soluciones de prueba reutilización de modelos de restricción. La nueva versión de Solver también incorporó nuevas opciones de optimización global, nuevos informes de linealidad y viabilidad.

Utilizando excel solver Si tienes la necesidad de realizar un pronóstico que involucra más de una variable, puedes utilizar Solver en Excel. Este complemento ayudará a analizar escenarios de negocio multivariable y de optimización.

Ejemplo de uso de solver Se tiene un presupuesto asignado para comprar equipos informáticos de Bs. 45.000,00, el proveedor no venden sino piezas completas, y para lo cual tengo una cantidad mínima para la compra de los siguientes artículos: Monitores: 7 Mouse: 15 Regletas: 25

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 67 de 154

Curso Nivel Avanzado

Se requiere buscar la compra óptima de dichos artículos, sin pasarse del presupuesto asignado y con las restricciones de los artículos Debemos tomar en cuenta lo siguiente: • El total del presupuesto de Bs. 45.000,00 • No venden sino piezas completas • Mínimo de compras de artículo como restricciones: 7 Monitores 15 Mouse 25 Regletas Realizaremos la siguiente tabla colocando los artículos, precio de cada uno cantidad de unidades y el total a invertir.

Las unidades serían las que colocaría solver al encontrar una respuesta óptima al planteamiento. El total sería una formula del precio por la cantidad de unidades Total del Presupuesto debe darnos igual o cercano al presupuesto asignado, con la suma total de cada artículo.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 68 de 154

Curso Nivel Avanzado

• En Establecer Objetivo especificaremos la celda D7 que es nuestra celda Objetivo allí estará el valor que queremos alcanzar del presupuesto asignado. • Con un Valor de prepuesto asignado 45.000 • Las celdas cambiantes serían las unidades que puedo comprar • Y las restricciones que son las mínimas cantidades

En este ejemplo queremos saber cuántas unidades de cada artículo podemos comprar sin alterar el presupuesto, así como cumplir con las restricciones del mínimo de producto.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 69 de 154

Curso Nivel Avanzado

Los datos ya están listos para utilizar Solver, así que debes ir a la ficha Datos y hacer clic en el comando Solver donde se mostrará el cuadro de diálogo Parámetros de Solver.

Todo está listo para continuar. Solamente debes hacer clic en el botón Resolver y Excel comenzará a calcular diferentes valores para las celdas variables hasta encontrar el valor máximo para la compra de unidades y el presupuesto asignado. Al término del cálculo se mostrará el cuadro de diálogo Resultados de Solver.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 70 de 154

Curso Nivel Avanzado

solver ya consiguió el mejor resultado al planteamiento, ahora fácilmente podrías cambiar los valores de las restricciones y volver a efectuar el cálculo con Solver para observar el comportamiento de la compra.

Otra de las opciones que tenemos con solver es guardar el escenario del resultado obtenido y tildar la opción de restaurar valores originales, para ver diferentes escenarios que podemos plantear.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 71 de 154

Curso Nivel Avanzado

Otro Ejercicio y Ejemplo muy práctico para entender esta herramienta tan poderosa como lo es Solver. Tengo un establecimiento de venta de pizzas que ofrece dos tipos de pizza tradicionales, Pepperoni ($30) y Vegetariana ($35) además de la pizza especial Suprema ($45). No sabemos cuál es el potencial de ingresos del establecimiento y tampoco el énfasis que se debería de dar a cada tipo de pizza para maximizar las ventas. Antes de realizar el análisis debemos considerar las siguientes condiciones. Dada nuestra capacidad de producción solamente podemos elaborar 150 pizzas al día. Otra condición es que no podemos exceder de 90 pizzas tradicionales (Pepperoni y Vegetariana) y además, al no haber muchos vegetarianos en el área, estimamos vender un máximo de 25 pizzas vegetarianas al día. Otra condición a considerar es que solamente podemos comprar los ingredientes necesarios para producir 60 pizzas Suprema por día. Con esta información elaboraré la siguiente hoja de Excel:

Observa que en los datos están representadas todas las reglas de negocio del establecimiento. Para cada tipo de pizza he colocado el total de pizzas a vender (por ahora en cero), el subtotal de cada una, así como el total de ventas que está Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 72 de 154

Curso Nivel Avanzado

formado por la suma de los subtotales. Además bajo el título Restricciones he colocado las condiciones previamente mencionadas. Algo muy importante es establecer las equivalencias para las restricciones. Por ejemplo, una restricción es que el total de pizzas no puede exceder de 150, pero Excel no necesariamente sabe lo que significa “Total de pizzas”, así que he destinado una celda para especificar que el total de pizzas es la suma de las celdas B2+B6+B10. Lo mismo sucede para explicar lo que significa Pizzas Tradicionales. Los datos ya están listos para utilizar Solver, así que debes ir a la ficha Datos y hacer clic en el comando Solver donde se mostrará el cuadro de diálogo Parámetros de Solver.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 73 de 154

Curso Nivel Avanzado

En nuestro ejemplo lo que queremos maximizar son las ventas totales por lo que en el cuadro de texto Establecer objetivo está especificada la celda $E$1 y por supuesto seleccioné la opción Máx. El otro parámetro importante son las celdas de variables que en nuestro ejemplo son las pizzas a vender para cada uno de los diferentes tipos. Finalmente observa cómo en el cuadro de restricciones están reflejadas las condiciones de venta del establecimiento. Pon especial atención a la manera en que se han utilizado las equivalencias que son las celdas $E$10 y $E$11. Todo está listo para continuar. Solamente debes hacer clic en el botón Resolver y Excel comenzará a calcular diferentes valores para las celdas variables hasta encontrar el valor máximo para las ventas totales. Al término del cálculo se mostrará el cuadro de diálogo Resultados de Solver.

Solamente haz clic en Aceptar para ver los resultados en la hoja de Excel.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 74 de 154

Curso Nivel Avanzado

Excel ha hecho los cálculos para saber que, con las restricciones establecidas, tendremos un valor máximo de venta total de $5,525. Ahora fácilmente podrías cambiar los valores de las restricciones y volver a efectuar el cálculo con Solver para observar el comportamiento en las ventas.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 75 de 154

Curso Nivel Avanzado

17.

TABLA DE DATOS FINANCIEROS

Las tablas de datos forman parte de las herramientas de Análisis Y si que nos permiten cambiar el valor de algunas celdas para ver cómo es afectado el resultado original. Una tabla de datos analiza un conjunto de valores y determina posibles resultados.

Tabla de datos de una variable El ejemplo más sencillo de una tabla de datos es aquél que utiliza una variable para realizar los cálculos. Supongamos el siguiente escenario:

En este ejemplo se está realizando una proyección de ventas para el año 2011 basándose en las ventas del año 2010 y esperando una tasa de crecimiento del 2.5%. Lo que desea saber es cuál sería la proyección de ventas para el 2011 si la tasa de crecimiento fuera diferente. Para hacer este análisis colocaremos las tasas de crecimiento que deseo utilizar de la siguiente manera:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 76 de 154

Curso Nivel Avanzado

Para crear la tabla de datos debo seleccionar el rango de celdas tal como se muestra en la imagen anterior y entonces ir a la ficha Datos, y dentro del grupo Herramientas de datos pulsar el botón Análisis Y si para posteriormente seleccionar la opción Tabla de datos.

Se mostrará el cuadro de diálogo Tabla de datos y en la caja de texto Celda de entrada (columna) se debe seleccionar la celda B2 que es la celda que contiene el porcentaje de crecimiento.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 77 de 154

Curso Nivel Avanzado

Al hacer clic en el botón Aceptar se llenarán las celdas contiguas a las tasas de crecimiento con el valor de la proyección de ventas correspondiente a cada una de las tasas.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 78 de 154

Curso Nivel Avanzado

Excel ha creado la tabla de datos en el rango B5:B13 y de esta manera puedo analizar las diferentes proyecciones de ventas para una tasa de crecimiento diferente. Una vez que he terminado de analizar la información, si intento eliminar alguna de las celdas pertenecientes al rango de la tabla de datos, Excel desplegará un mensaje advirtiendo que no se puede cambiar parte de una tabla de datos. Si deseas eliminar la tabla de datos deberás primero seleccionar el rango completo antes de oprimir la tecla suprimir.

Tabla de datos de dos variables En el artículo anterior se mostró los beneficios de las tablas de datos como una opción de Análisis Y si. En esta ocasión revisaremos un ejemplo clásico sobre el uso de una tabla de datos para realizar un análisis de dos variables. El escenario es el siguiente. Supongamos que quiero solicitar un préstamo personal a un banco, estoy haciendo una investigación con varios bancos y cada uno de ellos me ha dado una tasa de interés diferente así como diversos plazos de pago. El primero de los casos es el siguiente:

En lugar de hacer el cálculo para cada opción de tasa de interés y plazo, acomodaremos los datos adecuadamente para formar una tabla de datos. En las filas especificaremos las diferentes tasas de interés y en las columnas los posibles plazos. Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 79 de 154

Curso Nivel Avanzado

Para crear la tabla de datos de dos variables seleccionaremos el rango B4:G11 y debo ir a la ficha Datos y pulsar el botón Análisis Y si para seleccionar la opción Tabla de datos. Dentro del cuadro de diálogo deberemos elegir cada una de las celdas de entrada para la tabla de datos de la siguiente manera:

Al hacer clic en el botón Aceptar se generará la tabla de datos correspondiente:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 80 de 154

Curso Nivel Avanzado

En esta tabla de datos podemos observar lo que esperábamos, entre mayor es el plazo los pagos mensuales se reducen y entre mayor es la tasa de interés el pago mensual se incrementa. Con esta información puedo analizar y elegir adecuadamente la opción que más se acomoda a mis posibilidades de pago mensual. Las tablas de datos son una herramienta que nos ayuda en el análisis de nuestra información, la única desventaja es que solamente se puede hacer el análisis de dos variables como máximo. En caso de que te encuentres en una situación que requiera de un análisis de tres o más variables recomiendamos utilizar los Escenarios o también utilizar Excel Solver.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 81 de 154

Curso Nivel Avanzado

18.

TABLAS DINAMICAS EXCEL

Las tablas dinámicas son un elemento de análisis de información de Excel muy poderoso y esa fortaleza proviene de la facilidad que provee para manipular la información de acuerdo a tus necesidades. Utiliza Tablas dinámicas de Excel y veras fácilmente las tendencias, modelos y comparaciones. Las tablas dinámicas permiten tomar decisiones fundamentadas sobre datos críticos de una organización. Una tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Utiliza una tabla dinámica para analizar detenidamente datos numéricos y responder a preguntas no anticipadas sobre los datos. Una tabla dinámica está especialmente diseñada para los siguientes casos: • Consultar grandes cantidades de datos de muchas maneras diferentes y cómodas para el usuario. • Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y subcategorías, y crear cálculos y fórmulas personalizados. • Expandir y contraer los niveles de datos para destacar los resultados y ver los detalles de los datos de resumen de las áreas de interés. • Mover filas a columnas y columnas a filas para ver diferentes resúmenes de los datos de origen. • Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos más útiles e interesantes para poder centrarse en la información que le interesa. • Presentar informes electrónicos o impresos concisos, atractivos y con comentarios.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 82 de 154

Curso Nivel Avanzado

Es recomendable utilizar una tabla dinámica cuando desees comparar totales relacionados, sobre todo si tienes una lista larga de números para sumar y deseas realizar comparaciones distintas con cada número.

Cómo crear una tabla dinámica Las tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un ejemplo claro de cómo crearlas.

Crear una tabla dinámica en Excel Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la n ueva tabla dinámica

Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo Tablas de la ficha Insertar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 83 de 154

Curso Nivel Avanzado

Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás ajustar el rango de datos que se considerará en la tabla dinámica.

En este mismo cuadro de diálogo se puede elegir si se desea colocar la tabla dinámica en una nueva hoja de Excel o en una ya existente. Haz clic en el botón Aceptar y se creará la nueva tabla dinámica.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 84 de 154

Curso Nivel Avanzado

Excel agregará en la parte izquierda del libro la tabla dinámica y en la parte derecha la lista de campos. Esta lista de campos está dividida en dos secciones, primero la lista de todos los campos de los cuales podremos elegir y por debajo una zona a donde arrastraremos los campos que darán forma al reporte ya sea como columna, fila, valor o como un filtro. Para completar la tabla dinámica debemos arrastrar los campos al área correspondiente. Colocaré como columna el campo Producto y como fila al campo Ciudad. Finalmente como valores colocaré el campo Ventas.

De manera predeterminada Excel aplica la función SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la siguiente:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 85 de 154

Curso Nivel Avanzado

Utilizando una tabla dinámica fue posible crear un reporte de una manera fácil y sin la necesidad de utilizar fórmulas. Pronto veremos cómo se pueden elaborar tablas dinámicas más complejas que permitirán realizar un análisis profundo de la información.

Partes de una tabla dinámica en Excel Justo cuando se ha creado una tabla dinámica se muestra en la parte derecha de la hoja la lista de campos disponibles y por debajo las áreas donde podemos arrastrar dichos campos. Estas áreas denotan cada una de las partes de una tabla dinámica. Filtro de informe: Los campos que coloquemos en esta área crearán filtros para la tabla dinámica a través de los cuales podrás restringir la información que ves en pantalla. Estos filtros son adicionales a los que se pueden hacer entre las columnas y filas especificadas. Etiquetas de columna: Esta área contiene los campos que se mostrarán como columnas de la tabla dinámica.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 86 de 154

Curso Nivel Avanzado

Etiquetas de fila: Contiene los campos que determinan las filas de la tabla dinámica. Valores: Son los campos que se colocarán como las “celdas” de la tabla dinámica y que serán totalizados para cada columna y fila.

Dar formato a una tabla dinámica Una vez que hemos creado una tabla dinámica, Excel permite aplicarle formato fácilmente como si fuera una tabla de datos. La ficha Diseño incluye comandos especiales para aplicar formato a una tabla dinámica y es una ficha contextual, por lo que deberemos seleccionar la tabla dinámica para que se muestre. Diseño: Este grupo permite agregar subtotales y totales generales a la tabla dinámica así como modificar aspectos básicos de diseño.

Opciones de estilo de tabla dinámica: Las opciones de este grupo permiten restringir la selección de estilos que se muestran en el grupo que se encuentra justo a su derecha. Es decir, si seleccionas la opción “Filas con bandas”, entonces se mostrarán a la derecha los estilos que tienen filas con bandas. Estilos de tabla dinámica: Muestra la galería de estilos que se pueden aplicar a la tabla dinámica. Con tan sólo hacer clic sobre el estilo deseado se aplicará sobre la tabla. En el siguiente ejemplo te indicaremos paso a paso como desarrollar una tabla dinámica:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 87 de 154

Curso Nivel Avanzado

Creación de Tablas Dinámicas Las tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un ejemplo claro de cómo crearlas. Partiremos de la siguiente tabla con los datos de una serie de ponentes, con las distintas ciudades, sueldo percibido y horas trabajadas. En esta tabla están los datos que necesitamos para responder a las siguientes preguntas: ¿Cuantas conferencias se han dado en Mérida?, ¿Cuánto ha ganado Ramona Torres y Luis López? ¿Cuál es el número de horas que Frank Ortiz ha dado en PZO? ¿Cuál es el sueldo promedio de cada ponente? ¿Y el sueldo medio por ciudad? Y así, con este tipo de preguntas podíamos seguir y seguir. Puede hallarse la solución sin emplear tablas dinámicas que, como ya hemos dicho, es la solución que emplean en muchas empresas, pero es muy complicada, laboriosa y, sobre todo, de un sólo uso, esto es, podemos diseñar complicadas fórmulas empleando funciones lógicas, por ejemplo, pero sólo valdrán para el caso concreto que estoy usando. Por contra, la solución mediante tablas dinámicas, como su propio nombre indica, es eso dinámica, con apenas unos cambios rápidos podremos responder a varias preguntas distintas. Vamos a responder una a una a cada una de las preguntas que hemos hecho utilizando tablas dinámicas. Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 88 de 154

Curso Nivel Avanzado

¿Cuantas conferencias se han dado en Mérida? Lo primero que haremos será (ahora y siempre) seleccionar el rango de datos que queremos usar para la tabla dinámica. En este caso sería el rango A1:D16. Es buena práctica seleccionar las columnas completas A, B, C y D, pulsando [CTRL] + el botoncito de su rótulo, para que, en el caso que la tabla se agrande, tenga más valores, las tablas dinámicas sigan valiéndonos. Una vez seleccionado el rango, en nuestro caso, las columnas A, B, C y D. Nos vamos al menú Insertar, opción Tabla dinámica.

En principio, dejaremos las opciones que hay seleccionadas: dos para el rango de datos, que ya hemos seleccionado previamente, y otras dos para la ubicación de la tabla dinámica, que por defecto es en una nueva hoja. Le damos a aceptar, y nos aparece, ya en la hoja nueva, una serie de opciones y menús para generar nuestra tabla dinámica.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 89 de 154

Curso Nivel Avanzado

Elegiremos el campo región (la columna de las ciudades) y, para saber cuántas conferencias ha habido, cualquier otro campo, ya que dependerá de la función que le asignemos, nosotros hemos elegido el campo ponente. Pare ello Arrastramos el campo Delegación al área Etiqueta de la Fila y el campo Ponente al área Valores Vemos que en el área Valores, pone Cuenta de Ponentes, en un desplegable. En este caso lo dejaremos como está porque lo que queremos es que cuente el número de ponentes, pero ahí podríamos elegir otras funciones como la función SUMA, PROMEDIO, etc. Llegados a este punto se nos ha generado la siguiente tabla dinámica: En la que se ve el número de ponencias por cada ciudad y el total.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 90 de 154

Curso Nivel Avanzado

Como quiera que en el ejercicio nos piden sólo las de Mérida, elegiríamos esa ciudad del desplegable rotulado como Etiquetas de Fila. El resultado es una tabla similar a la anterior, pero en la que sólo aparece el dato de Mérida: De este modo, en un instante podemos obtener la información por las ciudades que queramos.

En este ejemplo, hemos visto claramente porque se llaman tablas dinámicas, porque son tablas que se modifican muy fácilmente en función de lo que nos interese.

¿Cuánto han ganado Ramona Torres y Luis López?

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 91 de 154

Curso Nivel Avanzado

En el ejemplo a continuación se busca totalizar los productos vendidos (Columna Producto Vs Columna Cantidad) en un periodo determinado, para realizar esta operación debes seguir los siguientes pasos: En la ficha Insertar, en el grupo Tablas, haces clic en Tabla dinámica y, a continuación, en Tabla dinámica. Luego de esto Excel mostrara un cuadro de dialogo en donde vas a seleccionar el rango de los datos que deseas comparar, sin embargo con anterioridad puedes seleccionar los datos, en cualquiera de los casos de es correcto, en la imagen anexa se detalla este procedimiento.

Una vez realizado este paso Excel abre una nueva hoja en el libro y en esta se presenta un submenú del lado derecho de la hoja (Lista de campos de tabla dinámica) en donde vas hacer clic en los campos que representan las columnas (Seleccionar campos para agregar al informe) después de realizar este procedimiento en el lado izquierdo de la hoja en efecto se va a reflejar la Tabla Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 92 de 154

Curso Nivel Avanzado

Dinámica con el total que representa cada producto y un total general de todos los productos. En la imagen anexa se muestra en detalle el procedimiento.

Campos calculados en tabla dinámica Las tablas dinámicas nos permiten hacer uso de campos calculados los cuales son columnas que obtienen su valor de la operación realizada entre algunas de las otras columnas existentes en la tabla dinámica. Supongamos la siguiente tabla dinámica en donde tenemos el resumen de las ventas de nuestros productos para el año pasado.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 93 de 154

Curso Nivel Avanzado

Ahora queremos insertar una nueva columna que contendrá nuestro pronóstico de ventas para el próximo año y el cual hemos estimado que tendrá un crecimiento del 4%. Esta nueva columna la podemos agregar como un campo calculado a la tabla dinámica de la siguiente manera. Dentro del título Herramientas de tabla dinámica selecciona la ficha Opciones y posteriormente el botón Cálculos el cual desplegará varias opciones y deberás seleccionar Campos, elementos y conjuntos para finalmente seleccionar la opción Campo calculado.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 94 de 154

Curso Nivel Avanzado

Se mostrará el cuadro de diálogo Insertar campo calculado en donde pod remos especificar un Nombre para nuestro campo.

Además del nombre del campo debemos especificar la Fórmula que Excel utilizará para realizar el cálculo de la nueva columna. Puede observar un recuadro donde aparecen enlistados todos los Campos de la tabla dinámica de manera que al seleccionar alguno de ellos y presionar el botón Insertar campo se insertará su nombre en la fórmula. Una vez que hayas terminado de configurar el nuevo campo calculado pulsa el botón Aceptar y Excel insertará el campo.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 95 de 154

Curso Nivel Avanzado

Al actualizar los datos de la tabla dinámica los campos calculados reflejarán automáticamente los nuevos cálculos. Recuerda que un campo calculado es una nueva columna que ha sido creada realizando operaciones con los campos existentes de una tabla dinámica.

Campo Calculado otro ejemplo En este artículo vamos a crear una Tabla Dinámica partiendo de una base de datos. En la tabla dispondremos de los costos de diferentes departamentos de la empresa para el año 2010 y la previsión para 2011. Crearemos un campo calculado que nos permita observar el incremento de cada departamento en estos años.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 96 de 154

Curso Nivel Avanzado

En Excel 2010 vamos al menú Insertar y luego Tabla Dinámica. Siguiendo unos sencillos pasos llegamos a crear una tabla dinámica como la que se muestra en la siguiente imagen:

Disponemos de los costos del año 2010 y la previsión para 2011 por cada uno de los departamentos. Los cuatro proyectos se han establecido como filtro de página en la parte superior de la tabla dinámica. Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 97 de 154

Curso Nivel Avanzado

Ahora deseamos disponer de una columna más que nos indique la variación porcentual experimentada por los costos entre los años 2010 y 2011. Este objetivo se podría lograr por varios métodos: 1. Escribiendo en la celda D5 la fórmula: =C5/B5-1. Esta fórmula nos da el incremento en tanto por uno. Para verlo en porcentaje basta pulsar sobre el icono de porcentaje (%). 2. Establecer la fórmula anterior pero vinculando sobre las celdas C5 y B5. En este caso veremos que la fórmula utiliza la función IMPORTARDATOSDINAMICOS. Esta forma de trabajar tiene la ventaja de que esta función apunta a la tabla dinámica y por tanto no perdemos el vínculo dinámico con la base de datos. 3. Crear un campo calculado.

Creación del campo calculado En Excel 2007 con el cursor sobre la tabla dinámica veremos arriba una nueva opción denominada: Herramientas de tabla dinámica Al pulsar sobre ella se abren un nuevo menú sobre el que pulsaremos sobre Formulas.

Aparece una ventana denominada Insertar campo calculado en el que crearemos la fórmula: =’2011’/’2010′-1

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 98 de 154

Curso Nivel Avanzado

La fórmula se crea introduciendo los campos (columnas) de la tabla dinámica. En este caso calculamos el porcentaje de variación por la clásica fórmula: Valor Final / Valor Inicial -1 Expresión que es igual a la siguiente: (Valor Final – Valor Inicial) / Valor Inicial En nuestro caso los costos del año 2010 son los valores iniciales y las previsiones para 2011 son los valores finales.

Esto genera una nueva columna que denominamos Var. % que recoge la variación porcentual de los costos entre los años 2010 y 2011. Inicialmente los valores que nos dan están en tanto por uno y hemos de ser nosotros los que debemos dar formato a esos valores como Porcentaje de dos decimales.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 99 de 154

Curso Nivel Avanzado

Los campos calculados son muy útiles al trabajar con tablas dinámicas y tienen la ventaja de que no perdemos el vínculo dinámico con la base de datos.

Agrupar elementos numéricos de una tabla dinámica En el siguiente ejemplo verás una tabla dinámica que tiene la información de varios pedidos de compra que se han hecho a una empresa.

Lo que deseamos hacer es analizar la información en grupos de 10. Para hacer este tipo de agrupación debo hacer clic derecho sobre cualquiera de los elementos de la columna que contiene los números de pedido y seleccionar la opción Agrupar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 100 de 154

Curso Nivel Avanzado

Se mostrará el cuadro de diálogo Agrupar el cual me permitirá indicar el número inicial y final así como el tamaño de la agrupación que deseo. El pulsar el botón Aceptar podremos observar la información agrupada de acuerdo a la e specificación hecha:

Agrupar elementos seleccionados de una tabla dinámica Es posible agrupar solamente algunos elementos de nuestra tabla dinámica de acuerdo a alguna selección que realicemos. Para ello debemos seleccionar los elementos mientras mantenemos pulsada la tecla CTRL.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 101 de 154

Curso Nivel Avanzado

Posteriormente en la ficha Opciones dentro del grupo Agrupar pulsamos el botón Agrupar selección y obtendremos el siguiente resultado:

En esta paso sucedieron dos cosas. La primera es que todos los elementos seleccionados se agruparon dentro de un solo grupo llamado Grupo1. La segunda cosa que sucedió es que se creó un nuevo campo llamado Vendedor2 que puedes Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 102 de 154

Curso Nivel Avanzado

observar en la lista de campos. En la siguiente imagen puedes observar que he hecho otra selección y agrupación para tener un grupo de mujeres y otro de hombres en la tabla dinámica.

Agrupar por fechas en una tabla dinámica Cuando tienes un dato de tipo fecha en una tabla dinámica es posible agrupar la información por cualquiera de las siguientes unidades de tiempo: Años, trimestres, meses, días, horas, minutos y segundos. Supongamos la siguiente tabla Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 103 de 154

Curso Nivel Avanzado

dinámica donde tenemos la fecha y monto de las facturas que hemos cobrado durante un año.

Necesito agrupar la información por mes, así que debo hacer clic derecho sobre cualquier celda con una fecha y selección la opción Agrupar. Se mostrará el cuadro de diálogo Agrupar permitiéndome seleccionar la opción deseada:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 104 de 154

Curso Nivel Avanzado

Al pulsar el botón Aceptar obtendremos el resultado deseado:

Es importante que consideres que para este ejemplo he supuesto que todos los datos son del mismo año. En caso de que tu tabla dinámica tuviera información de diversos años, sería necesario incluir también el año para que Excel haga la diferencia los meses de los diferentes años. Para agrupar por fechas una tabla dinámica debemos hacer clic derecho en cualquier celda de la columna de fechas y seleccionar la opción de menú Agrupar. Se mostrará el cuadro de diálogo Agrupar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 105 de 154

Curso Nivel Avanzado

En este cuadro de diálogo debes seleccionar las opciones Meses y Años de manera que la tabla dinámica haga una clara distinción entre los meses de 2011 y 2012. Al pulsar el botón Aceptar tendremos los datos agrupados por fecha.

Desagrupar elementos agrupados de una tabla dinámica Para desagrupar los elementos que tengamos agrupados en nuestra tabla dinámica debemos seleccionar dicho grupo e ir a la ficha Opciones y dentro del grupo Agrupar pulsar el botón Desagrupar.

Segmentación de datos en tablas dinámicas La segmentación de datos en tablas dinámicas es una nueva característica de Excel 2010 que permite hacer un filtrado de los datos dentro de ellas. De esta manera podemos filtrar fácilmente la información por más de una columna. A) En primer lugar debemos hacer clic sobre cualquier celda de la tabla dinámica y posteriormente en la ficha Opciones y dentro del grupo Ordenar y filtrar hacer clic sobre el comando Insertar Segmentación de datos

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 106 de 154

Curso Nivel Avanzado

b) Excel mostrará el cuadro de diálogo Insertar segmentación de datos

c) En este cuadro deberemos seleccionar los campos que deseamos utilizar como filtros en la tabla dinámica y Excel colocará un filtro para cada campo seleccionado.

d) Para filtrar la información de la tabla dinámica es suficiente con hacer clic sobre cualquiera de las opciones del filtro. Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 107 de 154

Curso Nivel Avanzado

e) Excel ajustará la información de la tabla dinámica de acuerdo a las opciones seleccionadas. Para mostrar de nuevo toda la información hacer clic en el botón Borrar filtro que se encuentra en la esquina superior derecha de cada panel.

Podremos agregar tantos filtros como campos disponibles tengas en la tabla dinámica, lo cual permitirá hacer un buen análisis de la información.

19.

GRAFICOS DINAMICOS

Un gráfico dinámico es un gráfico que está basado en los datos de una tabla dinámica y que se ajusta automáticamente al aplicar cualquier filtro sobre la información. Con este tipo de gráfico se visualiza mejor los resultados de los datos de una tabla dinámica. Pasos: Para crear un gráfico dinámico hacer clic sobre cualquier celda de la tabla dinámica que servirá como base del gráfico y posteriormente hacer clic sobre el comando Gráfico dinámico que se encuentra dentro del

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 108 de 154

Curso Nivel Avanzado

grupo Herramientas de la ficha Opciones.

Se mostrará el cuadro de diálogo Insertar gráfico de donde podrás seleccionar el tipo de gráfico que deseas utilizar. Una vez seleccionado el gráfico adecuado Excel lo insertará en la hoja de trabajo.

Después se trabaja como un gráfico normal, identificando las etiquetas. Al aplicarle un filtro a la tabla dinámica inmediatamente se ve reflejado en el gráfico.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 109 de 154

Curso Nivel Avanzado

20. SPARKLINES, EL PODER DE UN GRÁFICO EN TAN SOLO UNA CELDA Los sparklines, también conocidos como minigráficos, son pequeños gráficos de alta resolución integrados en un contexto de palabras, números o imágenes. El término fue acuñado por primera vez por Edward Tufte, quién los definía como pequeñas, intensas y simples gráficas del tamaño de una palabra con resolución tipográfica. Mientras que el gráfico típico esta hecho para mostrar muchos más datos los sparklines están diseñados para ser breves, fáciles de recordar y analizados en función del contexto donde están siendo insertados, mostrando tan solo la forma general de la variación, típicamente en el tiempo, de alguna medición. Microsoft Excel 2010 introdujo como funcionalidad la posibilidad de crear sparklines en una celda, lo que resulta una poderosa herramienta que podemos emplear en la creación de dashboards, tableros de control, reportes de gestión, etc. permitiéndonos mostrar tendencias en una serie de valores, como aumentos o reducciones periódicos, o simplemente para resaltar valores mínimos y máximos.

Para crearlos simplemente seleccionamos la celda donde queramos ubicar nuestro sparkline o minigráfico, vamos a la pestaña Insertar, vamos hasta el grupo Minigráficos y seleccionamos el tipo de minigráfico que deseamos crear, contando luego con una interesante variedad de opciones para personalizar. Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 110 de 154

Curso Nivel Avanzado

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 111 de 154

Curso Nivel Avanzado

21.

FUNCIONES DE BASE DE DATOS

Una base de datos es una colección de datos organizados en registros (filas) y en campos (columnas). Un registro contiene toda la información perteneciente a un elemento (ítem), por ejemplo: los datos de un alumno. Un campo contiene un dato perteneciente al elemento, por ejemplo: su nombre, edad, domicilio, cursos, etc. Una base de datos es toda la colección de registros, por ejemplo: cuadro de presupuestos de Excel. CONDICIONES Excel, usa una lista como una base de datos relacional, si se cumplen las siguientes condiciones: 1. El rango de una lista deberá estar separado al menos por una fila y una columna de otra información 2. La primera fila, está destinada a los títulos o nombres de los campos 3. Un título o un nombre de un campo puede contener hasta 255 caracteres. 4. No es válido introducir espacios en blanco al empezar un dato en una celda 5. Cada columna debe usar el mismo formato 6. Excel diferencia los caracteres en minúsculas o mayúsculas, cuando efectúa ordenamientos 7. Se puede usar fórmulas para calcular valores de campo. 8. No puede haber columna o filas en blanco entre los datos

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 112 de 154

Curso Nivel Avanzado

Por ejemplo tenemos el mismo presupuesto de gastos, esta vez administraremos mediante funciones de base de datos: Las funciones de base de datos en Excel facilitan nuestro trabajo con información que está organizada como una base de datos, es decir, que se encuentra en un rango de celdas donde la primera fila tiene los títulos de cada columna. Estas funciones nos permiten contar, sumar, multiplicar los valores de una columna que cumplen con los criterios especificados e inclusive podremos extraer un registro que cumpla con ciertas condiciones. Estas son las funciones de base de datos ofrecidas por Excel. Todas ellas guardan la misma estructura: FUNCION(datos; campo; criterios).

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 113 de 154

Curso Nivel Avanzado

Función Excel BDSUMA Suma los valores de una columna de la base de datos que cumplen con las condiciones especificadas. Sintaxis BDSUMA(base_de_datos, nombre_de_campo, criterios) base_de_datos (obligatorio): Rango de celdas que compone la base de datos. La primera fila del rango contiene los títulos de cada columna • nombre_de_campo (obligatorio): La columna con los valores a sumar. • criterios (obligatorio): Rango de celdas que contiene los criterios a cumplir. Ejemplos •

BDSUMA(I2:L24, “Salario”, B3:D4) = Obtiene la suma de la columna “Salario” que cumplen con los criterios establecidas en B3:D4

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 114 de 154

Curso Nivel Avanzado

BDCONTAR en Excel La función BDCONTAR en Excel cuenta los registros dentro de una columna numérica que permanecen después de aplicar un grupo de criterios sobre los datos. La función se puede utilizar sobre cualquier rango de celdas siempre y cuando se incluya un nombre para cada columna. Sintaxis de la función BDCONTAR La función BDCONTAR tiene tres argumentos obligatorios:

•

•

Base_de_Datos (obligatorio): El rango de celdas que compone la base de datos en donde haremos la consulta. La primera fila del rango contiene los nombres de cada columna. Nombre_de_campo (obligatorio): La columna, que contiene datos numéricos y sobre la cual se hará la cuenta de registros. Es posible escribir el nombre de la columna o el número que represente la posición de la columna dentro del rango.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 115 de 154

Curso Nivel Avanzado •

Criterios (obligatorio): El rango de celdas donde están especificadas las condiciones que deben cumplirse. El rango de criterios debe tener en la primera fila el nombre de la columna a la cual se aplicará el criterio.

Ejemplo de la función BDCONTAR En la siguiente imagen podrás observar una base de datos en el rango A4:D9 que contiene información de productos. Deseo saber la cantidad de registros de la tabla que tienen un IDProducto mayor a 25032.

La condición a cumplir está indicada en el rango A1:A2 el cual tiene un título de columna idéntico al título de la tabla donde están los datos. La función que he colocado en la celda G2 es la siguiente: =BDCONTAR(A4:D9, "IDProducto", A1:A2) El primer argumento es precisamente el rango de celdas que contiene los datos. El segundo argumento es el nombre de la columna que tiene los datos numéricos que serán contados. El tercer argumento especifica las celdas donde se encuentra la condición que se debe cumplir. La función DBCONTAR de la celda G1 devuelve

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 116 de 154

Curso Nivel Avanzado

el resultado correcto porque solamente existen dos registros que tienen un IDProducto mayor a 25032 que son las filas 8 y 9. Criterios para varias columnas En el ejemplo anterior solamente usé un criterio para la columna IDProducto, sin embargo es posible indicar a la función BDCONTAR un criterio para más de una columna. En el siguiente ejemplo obtendré la cuenta de los registros que tienen un IDPRODUCTO diferente a 25032 y que además tengan una cantidad de 5:

La diferencia más importante con el primer ejemplo es que he ampliado el rango de criterios para incluir la celda B2. =BDCONTAR(A4:D9, "IDProducto", A1:B2) Solamente la fila 6 cumple con la condición de tener un IDProducto diferente a 25032 y además tener una cantidad igual a 5. Como te puedes dar cuenta, es posible indicar un criterio para cada columna y la función BDCONTAR contará los registros que cumplan con todas la condiciones. En el siguiente ejemplo puedes observar que he indicado un criterio adicional para la columna Costo Total:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 117 de 154

Curso Nivel Avanzado

El único cambio es de nuevo el tercer argumento de la función BDCONTAR para indicar el rango correcto donde se encuentran todos los criterios a cumplir. Bajo estas condiciones, son las filas 8 y 9 que cumplen con todos los criterios.

Múltiples criterios para una misma columna También es posible indicar a la función BDCONTAR varios criterios para una misma columna y lo único que debemos hacer es colocar el nuevo criterio en una nueva fila dentro del rango de criterios. Observa el siguiente ejemplo:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 118 de 154

Curso Nivel Avanzado

La función BDCONTAR contará los registros que tengan una cantidad igual a 2 e igual a 5 que incluye a los registros en las filas 6, 7, y 8. Ahora observa el siguiente ejemplo:

En este caso la función BDCONTAR contará los registros que tienen una cantidad igual a 2 y además los registros que tienen una cantidad igual a 5 que además tienen un costo unitario mayor a 5. En este ejemplo solo las filas 6 y 7 cumplen con los criterios especificados. Así que ahora ya sabes que al trabajar con bases de datos, la función BDCONTAR nos ayudará a contar aquellos registros que cumplan con los criterios que especifiquemos. Nota importante: Si tus datos no tienen un título de columna como lo requiere la función BDCONTAR, entonces puedes utilizar la función CONTAR.SI.CONJUNTO y podrás obtener resultados similares.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 119 de 154

Curso Nivel Avanzado

22.

BASE DE DATOS

Base conceptual Una base de datos es un conjunto de información que está relacionada con un tema y que tiene una finalidad particular, que están lógicamente enlazados y ordenados por campos claves. Siendo una herramienta útil. Por ejemplo: una lista de teléfonos de todos los proveedores de una empresa, la cual va a contener el número de teléfono, nombre, dirección, etc. Otro ejemplo: mantener un registro del inventario de insumos de una empresa. Teniendo una base de datos es muy fácil recuperar cualquier información que se necesite en la forma que se desee. Se puede imprimir una lista de todos los vendedores, cuyas ventas sean mayores de un monto definido en Bolívares en el mes de diciembre, o imprimir listas separadas de números telefónicos privados y de negocios, entre otros. La base de datos está compuesta por bloques de datos conocidos como Registros, que están relacionados entre sí y éste a su vez está compuesto por campos. Cada registro va estar en una fila y no puede contener más de una fila. En cada fila, los elementos individuales son almacenados en campos, cada uno de ellos constituye una columna en la hoja de cálculo. Cuando la información está organizada en una lista, se puede ordenar, filtrar y totalizar los datos.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 120 de 154

Curso Nivel Avanzado

Registro

Un registro es un objeto que está compuesto por un conjunto de campos que están relacionados entre sí para formar una unidad. Estos campos que forman un registro pueden contener información del mismo tipo o de diferente naturaleza. Campo Es la parte más pequeña de una tabla de datos, donde va estar la información de los diferentes ítems. Ejemplo: CODIGO

COSTO

FECHA

NOMBRE

Creación de una base de Datos 1. Escriba los nombres de campo en una sola fila (Nombre, Dirección, Teléfono, etc.) aparece como encabezados de columna en su base de datos. 2. Seleccione todos los nombres de campo y aplíqueles un estilo (negrita, cursiva, etc). Con ello se destaca los nombres de campo entre los registros de la base de datos. 3. Cuando se utiliza por primera vez el formulario se tiene que incorporar el ícono en la barra de acceso rápido, para ello se procede de la siguiente forma: a) Clic en el menú archivo, seleccionar el comando de opciones. b) Seleccione Barra de herramientas de acceso rápido, en el panel izquierdo.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 121 de 154

Curso Nivel Avanzado

c) En la opción Comandos disponible en: abra la lista desplegable y seleccione Comandos que no están en la cinta de opciones. Busque el comando Formulario y clic en el botón agregar y aceptar.

d) Ya se encuentra en la barra de acceso rápido de Office.

4. Clic en el ícono de formulario.

5. Ya se puede comenzar a escribir los registros de la base de datos, una vez finalizado se procede a cerrar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 122 de 154

Curso Nivel Avanzado

Agregar, eliminar o restaurar registros Una vez finalizada la base de datos, para agregar más registros o eliminarlos o restaurarlos se tiene que utilizar la ficha de Datos. 1. 2.

Se procede de igual forma que cuando se creó la base de datos. Se presenta en pantalla el formulario con toda la data correspondiente de un registro y las opciones de agregar un nuevo registro, eliminar o restaurar. NOTA: Cuando se quiere buscar registros según una determinada condición, se dice que se establece un criterio. 1. 2.

Haga clic en el botón Criterios y escriba en el campo el criterio a buscar tal cual como se encuentre escrito en la base de datos. Clic en el botón buscar siguiente.

Creación de copia de la base Es recomendable trabajar con una copia de la base de datos original. Pasos: 1.

2. 3. 4. 5.

Se ubica el puntero del mouse sobre la etiqueta a copiar, botón derecho del mouse (menú contextual), se elige la opción de mover o copiar… En la lista desplegable Al libro: (dejarlo en el mismo). En el cuadro de lista Antes de la hoja: (seleccione hoja 2) Clic en la casilla de verificación Crear una copia. Clic en Aceptar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 123 de 154

Curso Nivel Avanzado

23. FORMULARIO CON BASE DE DATOS EXISTENTE

Podemos realizar un formulario también con una Base de Datos existente, para ello debemos colocarnos dentro de la tabla de la base de datos en cualquier sitio y hacemos clic en el botón de formulario.

SEMANA 4 4 2 2 2 1 1 1 1 1 1 1 1 2 2 2 1 4 4 2 4 4 1 4 1 2 4 2 1

PRODUCTO Whisky Ron Ron Vinos Whisky Vinos Whisky Vinos Whisky Whisky Ron Ron Vinos Ron Whisky Whisky Whisky Ron Whisky Ron Ron Vinos Ron Vinos Ron Ron Whisky Whisky Ron

ZONA Este Centro Este Sur Centro Este Este Centro Este Centro Sur Este Sur Centro Este Sur Sur Sur Centro Sur Centro Este Centro Este Centro Centro Centro Sur Este

VENDEDOR Luis Castillo Luis Castillo Juan Duarte Daniela Sosa Julio Perez Luis Castillo Daniela Sosa Daniela Sosa Luis Castillo Daniela Sosa Juan Duarte Juan Duarte Luis Castillo Julio Perez Luis Castillo Daniela Sosa Daniela Sosa Luis Castillo Luis Castillo Luis Castillo Julio Perez Julio Perez Juan Duarte Julio Perez Julio Perez Juan Duarte Juan Duarte Juan Duarte Juan Duarte

VENTAS 1.617,00 909,00 2.248,00 3.571,00 1.729,00 3.584,00 3.682,00 3.453,00 1.157,00 1.354,00 1.862,00 1.013,00 1.415,00 713,00 753,00 888,00 308,00 3.842,00 3.311,00 3.896,00 108,00 1.865,00 2.942,00 544,00 3.505,00 458,00 3.432,00 3.716,00 2.137,00

Automáticamente nos trae la tabla de formulario, primero aparece la hoja donde estamos y luego aparece el primer dato o registro de la tabla de base de datos, en el ejemplo vemos los encabezados o títulos de la tabla por ejemplo semana, producto, zona, vendedor y ventas.

Botón Nuevo o Eliminar: Podemos agregar más registros haciendo clic en botón nuevo o eliminar el que estamos editando.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 124 de 154

Curso Nivel Avanzado

Botón Buscar Anterior o Siguiente: A medida que pulsemos la opción buscar siguiente nos trae cada registro, en el formulario podemos visualizar la cantidad de registros y el número de registro donde estamos. Botón Criterios: Nos permite hacer una búsqueda en la base de datos cumpliendo con una condición, pudiéramos buscar por producto y por zona.

Nos traería todos los registros que cumplen con esta condición, pulsaríamos buscar siguiente o anterior para ver los registros. En el caso de las ventas se puede buscar el criterio que sea >=3000 Para hacer la búsqueda exacta debemos escribir el texto como esta en la base de datos de lo contrario no arrojara ningún resultado, sin embargo podemos utilizar el comodín del símbolo de interrogación ? para buscar un nombre sino sabemos si tiene mayúscula o si tiene tilde.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 125 de 154

Curso Nivel Avanzado

De esta manera nos traerá el registro del nombre de Luis con tilde y sin tilde, el otro comodín que podemos utilizar es el del asterisco * delante o detrás de la palabra para buscar un nombre que no sabemos si es Daniel o Daniela, el traerá todo los registros que comience con Daniel. La Interrogación se utiliza para un carácter y el asterisco para una serie de caracteres cuando hay más de una palabra que existe. Para una base de datos grande resulta muy útil utilizar este formulario para hacer búsquedas específicas o agregar nuevos elementos a la base de datos.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 126 de 154

Curso Nivel Avanzado

24. COMO HACER UN RANKING EN EXCEL

MS Excel ofrece varias alternativas para jerarquizar valores de una lista de datos, una de ellas es mediante la función JERARQUIA no obstante esta función es limitada cuando requieres un Ranking en Excel un poco más complejo. Mediante el uso de fórmulas podemos lograr organizar los datos para proporcionar un análisis mucho más profundo y significativo de los datos. A continuación te explicamos en detalle cómo hacer un Ranking en Excel de valores únicos en orden descendente y orden ascendente. ¡En Practica! En una lista tenemos los puntos de un grupo de 10 nadadores, dichos puntos van a ser necesarios para definir la clasificación a una segunda ronda de competición, es necesario entonces emplear una forma que nos permita jerarquizar los resultados, la lista con la que vamos a trabajar es la mostrada en la siguiente imagen:

El resultado requerido es que se devuelva el nombre del nadador que tenga más puntos así como también hay que tomar en cuenta que los valores deben estar en orden descendente y deben ser resultados únicos.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 127 de 154

Curso Nivel Avanzado

En tal sentido vas a seguir los siguientes pasos para lograr realizar el ranking solicitado: Paso 1 Vas a necesitar crear una columna adicional donde vas a insertar la función JERARQUIA y vas a agregarle el factor de corrección para evitar que los valores repetidos afecten el ranking y esto conlleve a que solo haya valores únicos, el resultado de aplicar la formula con los criterios antes mencionados es tal como lo mostrado en la siguiente imagen:

En la columna “Jerarquía” se empleó la siguiente fórmula para obtener valores únicos de acuerdo a los datos de la columna “Puntos”: =JERARQUIA(C3;$C$3:$C$12;0)+CONTAR.SI($C$3:C3;C3)-1 La fórmula arroja como resultado un ranking de acuerdo al nadador que tenga más puntos; por ejemplo “Fernando” que tiene 13,63 puntos se le asignó la primera posición y David que tiene 7,06 puntos se le asignó el séptimo puesto.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 128 de 154

Curso Nivel Avanzado

Paso 2

Insertas una columna adicional que vas a denominar “Nombre” y es donde vas a insertar la fórmula que va a traer el nombre del nadador que tenga la mejor posición de acuerdo a los puntos que posea. La fórmula que vamos a emplear para lograr el resultado consta de las siguientes funciones: DESREF, COINCIDIR, K.ESIMO.MENOR y FILA. Cada una de las funciones va a cumplir una labor en específico de acuerdo a lo solicitado. Mediante DESREF y empleando como argumento “fila” de la función utilizamos COINCIDIR en conjunto con K.ESIMO.MENOR y FILA obtenemos el nombre del nadador con mayor puntaje y mejor jerarquía con respecto al resto de los nadadores. La fórmula resultante que empleamos es la mostrada a continuación: =DESREF(B$3;COINCIDIR(K.ESIMO.MENOR(E$3:E$12;FILA()FILA(E$3)+1);E$3:E$12;0)-1;0) Aplicando esta fórmula a todo el conjunto de datos obtenemos los siguientes resultados:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 129 de 154

Curso Nivel Avanzado

Tal como se ve en la imagen anexa en la columna “Nombre” aparecen los nombres de los nadadores ordenados de acuerdo a su ranking, como por ejemplo el caso de “Fernando” quien posee ranking 1 y aparece en la primera posición de la lista, de igual forma el caso de “David” que tiene ranking 7 y aparece en la séptima ubicación. Paso 3 Por ultimo para tener la tabla completa vamos a traer los puntos que tuvo cada nadador y los vamos a cargar del lado de los nombres, para ello vamos a emplear la función BUSCARV con los argumentos que habitualmente empleamos quedando para este caso de la siguiente manera: =BUSCARV(H3;$B$3:$C$12;2;FALSO) Resultados La tabla con los resultados finales queda conforme a lo mostrado en la siguiente imagen:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 130 de 154

Curso Nivel Avanzado

Ranking en orden ascendente Se preguntaran ¿Es posible crear también una formula con un Ranking en Excel a la inversa es decir, en orden ascendente? En efecto si, ingresando a la función JERARQUIA y combinándola con la función CONTAR.SI podemos lograr que el ranking sea en orden ascendente nuevamente mediante el uso de algunas fórmulas. Vamos a continuar trabajando sobre la tabla que veníamos utilizando:

Para lograr que el ranking sea en orden ascendente primero insertamos una columna adicional donde vamos a obtener los valores de la jerarquía, para luego insertar la fórmula que vemos a continuación: =CONTAR($C$16:$C$25)(JERARQUIA(C16;$C$16:$C$25;0)+CONTAR.SI($C$16:C16;C16))+2 El resultado que vamos a alcanzar con la formula anterior es que de acuerdo a los puntos que tenga cada nadador se va a arrojar un valor en orden descendente, los resultados de aplicar la formula los podemos apreciar mediante la siguiente imagen:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 131 de 154

Curso Nivel Avanzado

Los valores del ranking tal como se ve en la imagen ahora han cambiado por ejemplo para el caso del nadador “Fernando” la jerarquía da como valor 10 (Diez) y para el nadador “David” la jerarquía da como resultado 4 (Cuatro). Solo resta crear una columna donde vamos a tener los nombres de los nadadores de acuerdo a su posición en el ranking, para ello vamos a emplear la fórmula que utilizamos para el primer ejemplo: =DESREF(B$16;COINCIDIR(K.ESIMO.MENOR(E$16:E$25;FILA()FILA(E$16)+1);E$16:E$25;0)-1;0) Dicha fórmula permite ordenar Mediante la función DESREF y en combinación con COINCIDIR, K.ESIMO.MENOR y FILA el nombre del nadador con el valor jerarquía más bajo con respecto al resto de los nadadores. Los resultados de emplear la formula lo observamos mediante la siguiente imagen:

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 132 de 154

Curso Nivel Avanzado

Tal como se ve el orden de los nombres de los nadadores cambio ya que el valor del ranking para este caso es en orden ascendente y los nombres son ordenados de acuerdo al que tenga menor puntos en adelante. Existen opciones adicionales para hacer un Ranking en Excel no obstante las formulas presentadas en este artículo son una muy buena opción a la hora de elaborar una jerarquía dentro de un grupo de datos.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 133 de 154

Curso Nivel Avanzado

25. MACROS AUTOMATICAS

Las macros en Excel se utilizan principalmente para automatizar tareas repetitivas, grabando o programando en lenguaje VBA (Visual Basic para Aplicaciones) cada uno de los pasos que realizará al momento de su ejecución. Una vez creada una macro, puede asignarla a un objeto (como un botón de barra de herramientas, un gráfico o un control) para que pueda ejecutarla haciendo clic en ese objeto. Una macro es un conjunto de instrucciones que sirve para automatizar procesos. Por ejemplo: si realizamos frecuentemente la acción de aplicarle negrita, cambio de fuentes y centrado. En lugar de hacer estas acciones manualmente se puede elaborar una macro e invocarla para que se ejecute estos tres procesos en forma automática.

Grabar una macro Cuando grabe una macro, es recomendable que primero anote y ensaye cada uno de los pasos antes de grabar la macro, esto le permitirá tener una macro más limpia, es decir, con los menos errores posibles. Para grabar una macro, realice lo siguiente: Pasos: Clic ficha Programador, grupo Código, comando Grabar macro. En el cuadro de texto Grabar macro, en Nombre de la macro, escriba el nombre que le desea dar a la macro. Si desea asignarle una tecla en Método abreviado: escriba una letra (que no esté asignada a un CTRL), en la lista desplegable Guardar macro en: Este libro y en Descripción: escriba lo que hace la macro. Clic en Aceptar. En la barra de estado aparece un Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 134 de 154

Curso Nivel Avanzado

mensaje que dice grabando. 1. Inicie la creación de la macro, ejecutando todos los pasos que se haría en forma normal. 2.

Al finalizar los pasos haga clic en el botón Detener grabación. En este momento ya está creada la macro. 4

G b

d

Ejecutar la macro Las macros se pueden ejecutar por varios métodos, antes que nada ubicar el cursor donde se desea aplicar la macro: Ejecutar por el Método abreviado: el asignado al ser creada la macro, activándola con la tecla CTRL simultáneamente con la tecla asignada. Ejecutar por el Cuadro de diálogo Nombre de la macro Por ficha Programador, grupo Código, comando Macros. En el cuadro de Nombre de la macro: seleccione la macro que va a ejecutar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 135 de 154

Curso Nivel Avanzado

Oprima el botón Ejecutar y automáticamente se ejecutará la macro.

Ejecutar desde la Barra de Acceso Rápido Después de grabar una macro, se puede asignar un botón en la barra de herramientas de acceso rápido. Por ficha Archivo, comando Opciones, elegir en el panel izquierdo la opción Personalizar. En Comandos disponible en: abrir la lista desplegable y seleccionar Macros, buscar el nombre de la macro y seleccionarla, después presionar el comando Agregar >> Seleccione la macro y se puede asignar al ícono un dibujo, presionando el comando Modificar…, seleccionar el ícono de su agrado, presionar aceptar dos veces. Listo ya aparece el ícono de la macro en la barra de acceso directo.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 136 de 154

Curso Nivel Avanzado

Asignar una macro a un botón Vaya al botón Office y, haga clic en Opciones de Excel.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 137 de 154

Curso Nivel Avanzado

1. En la ventana mostrada seleccione la opción Personalizar y¸ Comandos disponibles en seleccione la opción Macros y en macros mostradas seleccione la que desee agregar un botón herramientas de acceso rápido, por ultimo presione Agregar Aceptar.

en el cuadro el listado de a la barra de y en seguida

NOTA: Puede cambiar la imagen del botón asignado a la macro y el nombre del mismo, presionando el botón Modificar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 138 de 154

Curso Nivel Avanzado

2. Tras presionar Aceptar en el paso 2, el botón se mostrará en la barra de herramientas de acceso rápido y solo necesitará presionarlo para ejecutar las acciones grabadas o programadas en la macro asignada.

Eliminar una macro 1. Abra el libro que contiene la macro que desee eliminar. 2. En la ficha Programador, en el grupo Código, haga clic en Macros. 3. En el cuadro Nombre de la macro, haga clic en el nombre de la macro que desee eliminar y, presione el botón Eliminar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 139 de 154

Curso Nivel Avanzado

Seguridad de macros En Microsoft Office Excel, puede cambiar la configuración de seguridad de macros para controlar qué macros se ejecutan y en qué circunstancias al abrir un libro. Por ejemplo, puede permitir la ejecución de macros en función de si están firmadas digitalmente por un programador de confianza. Por ficha Programador, grupo Código, seleccionar Seguridad de macros Seleccionar la opción aplicar y aceptar.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 140 de 154

Curso Nivel Avanzado

26. RESOLUCIÓN DE ERRORES Los errores son los mensajes que aparecen dentro de una celda antecedidos del símbolo #. A continuación analizaremos cada uno de los errores en Excel y la manera de solucionar el problema.

Error #¡NULO! Este error es poco común, pero generalmente es consecuencia de haber especificado incorrectamente un rango. Una fórmula que regresará este tipo de error es la siguiente: =SUMA(A1:A5 B1:B5) El error se produce porque olvidamos colocar el separador (,) entre los rangos especificados. Solución: Asegúrate de que has especificado correctamente los rangos dentro de la fórmula.

Error #¡NUM! Hay ocasiones en que los cálculos en Excel son muy exhaustivos y las fórmulas llegan a calcular valores mucho más grandes que las capacidades de Excel y es cuando obtienes un error de este tipo. Solución: Verifica que no estás llamando repetitivamente a las funciones y que generen un número demasiado grande.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 141 de 154

Curso Nivel Avanzado

Error #¡REF!

Este es uno de los errores más comunes de Excel y sucede cuando una celda trata de referenciar otra celda que no puede ser encontrada porque se ha eliminado. Por ejemplo, escribe la siguiente fórmula: =SUMA(D3:D5, E3:E5,F3:F5) Posteriormente elimina la columna E. Inmediatamente se mostrará este tipo de error. Solución: Verifica que los rangos a los que hace referencia la fórmula aún existan o de lo contrario realiza los ajustes necesarios.

Error #¡VALOR! Es generado por utilizar argumentos de tipo texto cuando en realidad la función espera valores numéricos. Considera la siguiente función: =SUMA("a", "b") La función SUMA regresará este tipo de error porque los argumentos no nos numéricos. Solución: Verifica que los tipos de datos de los argumentos son los adecuados, en especial los argumentos que esperan un valor numérico.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 142 de 154

Curso Nivel Avanzado

27. TECLAS DE ACCESO RÁPIDO

Teclas para moverse y desplazarse por una hoja de cálculo o un libro Teclas de dirección Moverse una celda hacia arriba, hacia abajo, hacia la izquierda o hacia la derecha •

CTRL+ tecla de dirección Ir hasta el extremo de la región de datos actual

•

INICIO Ir hasta el comienzo de una fila

•

CTRL+INICIO Ir hasta el comienzo de una hoja de cálculo

•

CTRL+FIN Ir a la última celda de la hoja de cálculo, que es la celda ubicada en la intersección de la columna situada más a la derecha y la fila ubicada más abajo (en la esquina inferior derecha) o la celda opuesta a la celda inicial, que es normalmente la celda A1

•

AV PÁG Desplazarse una pantalla hacia abajo

•

RE PÁG Desplazarse una pantalla hacia arriba

•

ALT+AV PÁG Desplazarse una pantalla hacia la derecha ALT+RE PÁG Desplazarse una pantalla hacia la izquierda CTRL+AV PÁG Ir a la siguiente hoja del libro

•

CTRL+RE PÁG Ir a la hoja anterior del libro

•

CTRL+F6 o CTRL+TAB Ir al siguiente libro o a la siguiente ventana CTRL+MAYÚS+F6 o CTRL+MAYÚS+TAB Ir al libro o a la ventana anterior F6 Mover al siguiente panel de un libro que se ha dividido

•

MAYÚS+F6 Mover al anterior panel de un libro que se ha dividido

•

CTRL+RETROCESO Desplazarse para ver la celda activa

•

F5 Mostrar el cuadro de diálogo Ir a

•

MAYÚS+F5 Mostrar el cuadro de diálogo Buscar

•

MAYÚS+F4 Repetir la última acción de Buscar (igual a Buscar siguiente)

•

TAB Desplazarse entre celdas desbloqueadas en una hoja de cálculo protegida

Teclas para moverse por una hoja de cálculo con el modo Fin activado Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 143 de 154

Curso Nivel Avanzado

•

FIN Activar o desactivar el modo Fin

•

FIN, tecla de dirección Desplazarse un bloque de datos dentro de una fila o columna

•

FIN, INICIO Ir a la última celda de la hoja de cálculo, que es la celda ubicada en la intersección de la columna situada más a la derecha y la fila ubicada más abajo (en la esquina inferior derecha) o la celda opuesta a la celda inicial, que es normalmente la celda A1

•

FIN, ENTRAR Ir a la última celda situada a la derecha de la fila actual que no esté en blanco; esta tecla no estará disponible si se ha activado la casilla de verificación Teclas de desplazamiento para transición en la ficha Transición (menú Herramientas, comando Opciones)

Teclas para moverse por una hoja de cálculo con la tecla BLOQ DESPL activada

•

BLOQ DESPL Activar o desactivar la tecla BLOQ DESPL

•

INICIO Ir a la celda de la esquina superior izquierda de la ventana

•

FIN Ir a la celda de la esquina inferior derecha de la ventana

•

FLECHA ARRIBA o FLECHA ABAJO Desplazarse una fila hacia arriba o hacia abajo

•

FLECHA IZQUIERDA o FLECHA DERECHA Desplazarse una columna hacia la izquierda o hacia la derecha

Teclas para ver o imprimir un documento •

CTRL+P o CTRL+MAYÚS+F12 Presentar el cuadro de diálogo Imprimir

Teclas de dirección Desplazarse por la página cuando está ampliada •

RE PÁG o AV PÁG Avanzar o retroceder una página cuando está reducida

•

CTRL+FLECHA ARRIBA o CTRL+FLECHA IZQUIERDA Ir a la primera página cuando está reducida

•

CTRL+FLECHA ABAJO o CTRL+FLECHA DERECHA Ir a la última página cuando está reducida

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 144 de 154

Curso Nivel Avanzado

Teclas para trabajar con hojas de cálculo, gráficos y macros •

MAYÚS+F11 o ALT+MAYÚS+F1 Insertar una nueva hoja de cálculo

•

F11 o ALT+F1 Crear un gráfico que utilice el rango actual

•

ALT+F8 Mostrar el cuadro de diálogo Macro

•

ALT+F11 Mostrar/ocultar el Editor de Visual Basic

•

CTRL+F11 Insertar una hoja de macros de Microsoft Excel 4.0

•

CTRL+AV PÁG Ir a la siguiente hoja del libro

•

CTRL+RE PÁG Ir a la hoja anterior del libro

•

MAYÚS+CTRL+AV PÁG Seleccionar la hoja actual y la hoja siguiente del libro

•

MAYÚS+CTRL+RE PÁG Seleccionar la hoja actual y la hoja anterior del libro

Teclas para introducir datos en una hoja de cálculo •

ENTRAR Completar una entrada de celda e ir hacia abajo en la selección

•

ALT+ENTRAR Comenzar una nueva línea en la misma celda

•

CTRL+ENTRAR Rellenar el rango de celdas seleccionado con la entrada actual MAYÚS+ENTRAR Completar una entrada de celda e ir hacia abajo en la selección TAB Completar una entrada de celda e ir hacia la derecha en la selección MAYÚS+TAB Completar una entrada de celda e ir hacia la izquierda en la selección ESC Cancelar una entrada de celda RETROCESO Eliminar el carácter situado a la izquierda del punto de inserción o eliminar la selección SUPR Eliminar el carácter situado a la derecha del punto de inserción o eliminar la selección CTRL+SUPR Eliminar texto hasta el final de la línea

•

Teclas de dirección Desplazarse un carácter hacia arriba, abajo, izquierda o derecha

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 145 de 154

Curso Nivel Avanzado

• • • • •

INICIO Ir al comienzo de la línea F4 o CTRL+Y Repetir la última acción MAYÚS+F2 Modificar un comentario de celda CTRL+MAYÚS+F3 Crear nombres a partir de rótulos de fila y columna CTRL+J Rellenar hacia abajo CTRL+D Rellenar hacia la derecha CTRL+F3 Definir un nombre

Teclas para trabajar en las celdas o la barra de fórmulas • • • • •

• • • • • • • • • • • •

RETROCESO Modificar la celda activa y, a continuación, borrarla, o eliminar el carácter anterior en la celda activa al modificar el contenido de las celdas ENTRAR Completar una entrada de celda CTRL+MAYÚS+ENTRAR Introducir una fórmula como fórmula matricial ESC Cancelar una entrada en la celda o en la barra de fórmulas CTRL+A Presentar la Paleta de fórmulas, después de escribir un nombre de función en una fórmula CTRL+MAYÚS+A Insertar los nombres de argumentos y los paréntesis de una función, después de escribir un nombre de función en una fórmula CTRL+ALT+K Insertar un hipervínculo ENTRAR (en una celda con hipervínculo) Activar un hipervínculo F2 Modificar la celda activa y colocar el punto de inserción al final de la línea F3 Pegar un nombre definido en una fórmula MAYÚS+F3 Pegar una función en una fórmula F9 Calcular todas las hojas de todos los libros abiertos CTRL+ALT+F9 Calcular todas las hojas del libro activo MAYÚS+F9 Calcular la hoja activa = (signo igual) Iniciar una fórmula ALT+= (signo igual) Insertar una fórmula de Autosuma CTRL+; (punto y coma) Introducir la fecha CTRL+MAYÚS+: (dos puntos) Introducir la hora CTRL+MAYÚS+” (comillas) Copiar el valor de la celda situada sobre la celda activa en la celda o en la barra de fórmulas CTRL+` (comilla simple izquierda) Alternar entre mostrar valores o fórmulas en las celdas CTRL+’ (apóstrofo) Copiar una fórmula de la celda situada sobre la celda activa en la celda o en la barra de fórmulas ALT+FLECHA ABAJO Presentar la lista de Autocompletar

Teclas para aplicar formato a los datos

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 146 de 154

Curso Nivel Avanzado

Presione Para • ALT+’ (apóstrofo) Mostrar el cuadro de diálogo Estilo CTRL+1 Mostrar el cuadro de diálogo Formato de celdas CTRL+E Aplicar el formato de número General •

CTRL+MAYÚS+$ Aplicar el formato Moneda con dos decimales (los números negativos aparecen en rojo)

•

CTRL+MAYÚS+% Aplicar el formato Porcentaje sin decimales CTRL+MAYÚS+^ Aplicar el formato numérico Exponencial con dos decimales CTRL+MAYÚS+# Aplicar el formato Fecha con el día, mes y año

•

CTRL+MAYÚS+@ Aplicar el formato Hora con la hora y minutos e indicar a.m. o p.m.

•

CTRL+MAYÚS+! Aplicar el formato Número con dos decimales, separador de millares y signo menos (–)

•

para los valores negativos CTRL+MAYÚS+& Aplicar un borde CTRL+MAYÚS+_ Quitar los contornos

•

CTRL+N Aplicar o quitar el formato de negrita CTRL+K Aplicar o quitar el formato de cursiva CTRL+S Aplicar o quitar el formato de subrayado CTRL+5 Aplicar o quitar el formato de tachado CTRL+9 Ocultar filas

•

CTRL+MAYÚS+( (paréntesis de apertura) Mostrar filas

•

CTRL+0 (cero) Ocultar columnas

•

CTRL+MAYÚS+) (paréntesis de cierre) Mostrar columnas

Teclas para modificar datos •

F2 Modifique la celda activa y coloque el punto de inserción al final de la línea

•

ESC Cancelar una entrada en la celda o en la barra de fórmulas

•

RETROCESO Modificar la celda activa y luego eliminarla; o bien eliminar el carácter precedente en la celda activa mientras se modifica el contenido de la misma

•

F3 Pegar un nombre definido en una fórmula

•

ENTRAR Completar una entrada de celda

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 147 de 154

Curso Nivel Avanzado

•

CTRL+MAYÚS+ Introducir una fórmula como fórmula matricial

•

ENTRAR Completar una entrada de celda

•

CTRL+A Presentar la Paleta de fórmulas, después de escribir un nombre de función en una fórmula CTRL+MAYÚS+A Insertar los nombres de argumentos y los paréntesis de una función, después de escribir un nombre de función en una fórmula

•

F7 Mostrar el cuadro de diálogo Ortografía

Teclas para insertar, eliminar y copiar una selección • • •

CTRL+C Copiar la selección CTRL+X Cortar la selección CTRL+V Pegar la selección SUPR Borrar el contenido de la selección CTRL+GUIÓN Eliminar celdas CTRL+Z Deshacer la última acción CTRL+MAYÚS+SIGNO MÁS Insertar celdas vacías

Teclas para moverse dentro de una selección •

•

• • • • •

ENTRAR Moverse de arriba a abajo dentro de la selección (abajo), o bien moverse en la dirección que se haya seleccionado en la ficha Edición (menú Herramientas, comando Opciones) MAYÚS+ENTRAR Moverse de abajo a arriba dentro de la selección (arriba), o bien moverse en la dirección que se haya seleccionado en la ficha Edición (menú Herramientas, comando Opciones) TAB Ir de izquierda a derecha dentro de la selección o desplazarse una celda hacia abajo si sólo se ha seleccionado una columna MAYÚS+TAB Ir de derecha a izquierda dentro de la selección o desplazarse una celda hacia arriba si sólo se ha seleccionado una columna CTRL+PUNTO Desplazarse en el sentido de las agujas del reloj hasta la siguiente esquina de la selección CTRL+ALT+FLECHA DERECHA Desplazarse hacia la derecha entre selecciones no adyacentes CTRL+ALT+FLECHA IZQUIERDA Desplazarse hacia la izquierda entre selecciones no adyacentes

Teclas para seleccionar celdas, columnas o filas •

CTRL+MAYÚS+* (asterisco) Seleccionar la región actual alrededor de la celda activa (la región actual es un área de datos ubicada entre filas y columnas en blanco)

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 148 de 154

Curso Nivel Avanzado

• • • • • •

• • • • • • • •

• •

MAYÚS+tecla de dirección Ampliar la selección una fila o columna CTRL+MAYÚS+tecla de dirección Ampliar la selección a la última celda que no esté en blanco de la misma columna que la celda activa MAYÚS+INICIO Ampliar la selección hasta el comienzo de la fila CTRL+MAYÚS+INICIO Ampliar la selección hasta el comienzo de la hoja de cálculo CTRL+MAYÚS+FIN Ampliar la selección hasta la última celda utilizada en la hoja de datos (esquina inferior derecha) CTRL+BARRA ESPACIADORA Seleccionar toda la columna MAYÚS+BARRA ESPACIADORA Seleccionar toda la fila CTRL+MAYÚS+BARRA ESPACIADORA Seleccionar toda la hoja de cálculo MAYÚS+RETROCESO Seleccionar sólo la celda activa cuando están seleccionadas varias celdas MAYÚS+AV PÁG Ampliar la selección una pantalla hacia abajo MAYÚS+RE PÁG Ampliar la selección una pantalla hacia arriba CTRL+MAYÚS+BARRA ESPACIADORA Con un objeto seleccionado, seleccionar todas las celdas de una hoja CTRL+6 Alternar entre ocultar objetos, mostrarlos o mostrar marcadores de los objetos CTRL+7 Mostrar u ocultar la barra de herramientas Estándar F8 Activar la ampliación de una selección utilizando las teclas de dirección MAYÚS+F8 Agregar otro rango de celdas a la selección; o utilice las teclas de dirección para moverse al principio del rango que desee agregar y, a continuación, presione F8 y las teclas de dirección para seleccionar el siguiente rango BLOQ DESP, MAYÚS+INICIO Ampliar la selección hasta la celda de la esquina superior izquierda de la ventana BLOQ DESP, MAYÚS+FIN Ampliar la selección hasta la celda superior derecha de la ventana

Teclas para ampliar la selección con el modo Fin activado •

FIN Activar o desactivar el modo Fin

•

FIN, MAYÚS+tecla de dirección Ampliar la selección hasta la última celda que no esté en blanco de la misma columna que la celda activa

•

FIN, MAYÚS+INICIO Ampliar la selección hasta la última celda utilizada en la hoja de datos (esquina inferior derecha)

•

FIN, MAYÚS+ENTRAR Ampliar la selección hasta la última celda en la fila actual. Esta tecla no estará disponible si se activó la casilla de verificación Teclas de desplazamiento para transición en la ficha Transición (menú Herramientas, comando Opciones)

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 149 de 154

Curso Nivel Avanzado

Teclas para seleccionar celdas con características especiales •

CTRL+MAYÚS+* (asterisco) Seleccionar la región actual alrededor de la celda activa (la región actual es un área de datos ubicada entre filas y columnas en blanco)

•

CTRL+/ Seleccionar la matriz actual, que es la matriz a la que pertenece la celda activa

•

CTRL+MAYÚS+O (letra O) Seleccionar todas las celdas con comentarios

•

CTRL+ Seleccionar las celdas de una fila que no coincidan con el valor en la celda activa de esa fila. Se debe seleccionar la fila que comience con la celda activa.

•

CTRL+MAYÚS+| Seleccionar las celdas de una columna que no coincidan con el valor en la celda activa de esa columna. Se debe seleccionar la columna que comience con la celda activa.

•

CTRL+[ (corchete de apertura) Seleccionar sólo las celdas a las que se hace referencia directa en fórmulas de la selección

•

CTRL+MAYÚS+{ (llave de apertura) Seleccionar sólo las celdas a las que se hace referencia directa o indirecta en fórmulas de la selección

•

CTRL+] (corchete de cierre) Seleccionar sólo las celdas con fórmulas que hagan referencia directa a la celda activa

•

CTRL+MAYÚS+} (llave de cierre) Seleccionar todas las celdas con fórmulas que hagan referencia directa o indirecta a la celda activa

•

ALT+; (punto y coma) Seleccionar sólo celdas visibles de la selección actual

Teclas para seleccionar una hoja de gráficos • •

CTRL+AV PÁG Seleccionar la siguiente hoja de un libro, hasta que se seleccione la hoja de gráficos que desea CTRL+RE PÁG Seleccionar la hoja anterior de un libro, hasta que se seleccione la hoja de gráficos que desea

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 150 de 154

Curso Nivel Avanzado

Teclas para seleccionar un gráfico incrustado •

Nota La barra de herramientas Dibujo debe estar activada.

•

Presione la tecla F10 para activar la barra de menús.

•

Presione CTRL+TAB o CTRL+MAYÚS+TAB para seleccionar la barra de herramientas Dibujo.

•

Presione la tecla FLECHA DERECHA para seleccionar el botón Seleccionar objetos de la barra de herramientas Dibujo.

•

Presione CTRL+ENTRAR para seleccionar el primer objeto.

•

Presione la tecla TAB para avanzar (o MAYÚS+TAB para retroceder) en la pila de objetos hasta que aparezca el gráfico incrustado que desee seleccionar.

•

Presione CTRL+ENTRAR para activar el gráfico.

Teclas para seleccionar elementos de gráficos •

28.

FLECHA ABAJO Seleccionar el grupo de elementos anterior FLECHA ARRIBA Seleccionar el grupo de elementos siguiente FLECHA DERECHA Seleccionar el siguiente elemento del grupo FLECHA IZQUIERDA Seleccionar el elemento anterior del grupo

TECLAS PARA UTILIZAR CON BASES DE DATOS Y LISTAS

Teclas para trabajar con un formulario • •

• •

ALT+tecla, donde tecla es la letra subrayada en el nombre del campo o del comando Seleccionar un campo o un botón de comando FLECHA ABAJO Ir al mismo campo del siguiente registro FLECHA ARRIBA Ir al mismo campo del anterior registro TAB Ir al siguiente campo modificable del registro MAYÚS+TAB Ir al anterior campo modificable del registro ENTRAR Ir al primer campo del siguiente registro MAYÚS+ENTRAR Ir al primer campo del registro anterior AV PÁG Ir al mismo campo 10 registros más adelante CTRL+AV PÁG Moverse a un nuevo registro RE PÁG Ir al mismo campo 10 registros más atrás CTRL+RE PÁG Ir al primer registro

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 151 de 154

Curso Nivel Avanzado

• • • •

INICIO o FIN Ir al comienzo o al final de un campo MAYÚS+FIN Ampliar una selección hasta el final de un campo MAYÚS+INICIO Ampliar una selección hasta el comienzo de un campo FLECHA IZQUIERDA o FLECHA DERECHA Desplazarse un carácter hacia la izquierda o hacia la derecha dentro de un campo MAYÚS+FLECHA IZQUIERDA Seleccionar el carácter de la izquierda MAYÚS+FLECHA DERECHA Seleccionar el carácter de la derecha

Teclas para utilizar Autofiltro •

•

•

Teclas de dirección para seleccionar la celda que contenga el rótulo de columna y, a continuación, presione ALT+FLECHA ABAJO para mostrar la lista de Autofiltro de la columna actual FLECHA ABAJO Seleccionar el siguiente elemento de la lista de Autofiltro FLECHA ARRIBA Seleccionar el elemento anterior de la lista de Autofiltro ALT+FLECHA ARRIBA Cerrar la lista de Autofiltro de la columna actual INICIO Seleccionar el primer elemento (Todo) en la lista de Autofiltro FIN Seleccionar el último elemento de la lista de Autofiltro ENTRAR Filtrar la lista mediante el elemento seleccionado en la lista de Autofiltro

Teclas para esquematizar datos • • • • •

ALT+MAYÚS+FLECHA DERECHA Agrupar filas o columnas ALT+MAYÚS+FLECHA IZQUIERDA Desagrupar filas o columnas CTRL+8 Mostrar u ocultar símbolos de esquema CTRL+9 Ocultar filas seleccionadas CTRL+MAYÚS+( (paréntesis de apertura) Mostrar filas seleccionadas CTRL+0 (cero) Ocultar columnas seleccionadas CTRL+MAYÚS+) (paréntesis de cierre) Mostrar columnas seleccionadas

29. TECLAS PARA UTILIZAR CON INFORMES DE TABLA DINÁMICA O GRAFICO DINÁMICO • • • •

Teclas para el Asistente para tablas y gráficos dinámicos FLECHA ARRIBA o FLECHA ABAJO Seleccionar el botón de campo anterior o siguiente de la lista FLECHA IZQUIERDA o FLECHA DERECHA Seleccionar el botón de campo situado a la izquierda o a la derecha en una lista de botones de campo con varias columnas ALT+C Desplazar el campo seleccionado al área de columna

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 152 de 154

Curso Nivel Avanzado

• •

ALT+D Desplazar el campo seleccionado al área de datos ALT+L Mostrar el cuadro de diálogo Campo de la tabla dinámica ALT+P Desplazar el campo seleccionado al área de página ALT+R Desplazar el campo seleccionado al área de fila

Teclas para los campos de página que se muestran en un informe de tabla dinámica o de gráfico dinámico • • • • • • • •

CTRL+MAYÚS+* (asterisco) Seleccionar el informe de tabla dinámica completo Teclas de dirección para seleccionar la celda que contiene el campo y, después, ALT+FLECHA ABAJO para mostrar la lista del campo actual en un informe de tabla dinámica Teclas de dirección para seleccionar el campo de página de un informe de gráfico dinámico y, después, ALT+FLECHA ABAJO para mostrar la lista del campo de página actual en un informe de gráfico dinámico FLECHA ARRIBA Seleccionar el elemento anterior de la lista FLECHA ABAJO Seleccionar el siguiente elemento de la lista INICIO Seleccionar el primer elemento visible de la lista FIN Seleccionar el último elemento visible de la lista ENTRAR Mostrar el elemento seleccionado BARRA ESPACIADORA Activar o desactivar una casilla de verificación de la lista

Teclas para diseñar un informe de tabla o de gráfico dinámico • • •

• • • •

Presione la tecla F10 para activar la barra de menús. Presione CTRL+TAB o CTRL+MAYÚS+TAB para seleccionar la barra de herramientas Tabla dinámica. Presione las teclas FLECHA IZQUIERDA o FLECHA DERECHA para seleccionar el menú situado a la izquierda o a la derecha o, cuando el submenú está visible, para pasar del menú principal al submenú. Presione ENTRAR (en un botón de campo) y las teclas FLECHA ABAJO y FLECHA ARRIBA para seleccionar el área a la que desee mover el campo seleccionado. Teclas para agrupar y desagrupar elementos de tablas dinámicas ALT+MAYÚS+FLECHA DERECHA Agrupar los elementos de la tabla dinámica seleccionados ALT+MAYÚS+FLECHA IZQUIERDA Desagrupar los elementos de la tabla dinámica seleccionados.

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 153 de 154

Curso Nivel Avanzado

Representaciones ISRAMCA, C.A. [email protected]/[email protected] Twitter: @isramca

Pág. 154 de 154