Excel macros avanzados para gente que trabaja, Diapositivas de Competencias en MS Microsoft Excel

¡Descarga Excel macros avanzados para gente que trabaja y más Diapositivas en PDF de Competencias en MS Microsoft Excel solo en Docsity! IMECAF MÉXICO SC NINO INCOADO CONTABILIDAD, ADMINISTRACIÓN Y FINANZAS EXCEL AVANZADO —— NN a IMECAF, SC Excel 2016 avanzado E . Tabla de contenido za a TEMA 1. MANEJO AVANZADO DE FUNCIONES Es po 1,1. Funciones anidadaS ccoo... 1 LJ y 1.1.1. — Funciones Si (If) Anidadas A » 1.1.2. Función Coincidir (Match) con Buscarv O 2 > 1.1.3, — Función Elegir (Choose) con Diasem(WeekdaY) coccion 4 1.2. Función Indirecto (Indirect) E 1.3. Función Indice (Index) » 1.4. Aplicación de funciones Indirecto (Indirect) con BuscarY (Vlookup 1.5. Aplicación de funciones Función Indice (Index) con Coincidir (Match) . TEMA 2. FUNCIONES DE BASES DE DA P 2.1. Sintaxis de una función de bases de datos 2.2. Función BDSuma (DSUM) o... 2.3. Función BDPromedio (DAverage) 2.4. Función BDContar (OU) cccccconicconiriocooocoooornocooionocono 2 2.5. Función BDContara (DCounta) a 2.6. Función BDMax (DMax) 2.7. Función BDMin (DMin).. > 2.8. Función BDExtraer (DGeb e TEMA 3, HERRAMIENTAS PARA EL MANEJO DE LISTAS DE DATOS a 3.1. Texto en columnas 3.2, Listas de datos dependientes. y 3.3. Quitar datos duplicados en una columna ] r 3.4. Manejo de esquemas ] 35 RM occoccocacococooocoorarococonocoooonocnrorccconnnnsss , 3.6. y Sl, ) TEMA 4. CONSOLIDACION DE INFORMACION 4.1. Consolidación por referencias 3D .. 4.2. Consolidación por posición ....... 4.3. Consolidación por categoría TEMA 5. MANEJO DE ESCENARIOS 5.1. a!  IMECAF, SC Excel 2016 Avanzado TEMA 1. MANEJO AVANZADO DE FUNCIONES A A A A A A A A A A A A AA A A A A Objetivos de la unidad En esta primera unidad se revisará la aplicación avanzada de funciones que le permita identificar la manera correcta para preparar fórmulas que incluyan funciones anidadas. Objetivos particulares. + Identificar el concepto de funciones anidadas. + Integrar fórmulas que incluyan el uso de varias funciones al mismo tiempo 1.1. Funciones anidadas En el trabajo diario con Excel, tendremos la necesidad de usar dos o más funciones para resolver una determinada operación, además, el análisis de los cálculos que vamos a efectuar nos lleva a que debemos manejarlas de forma conjunta, a esta forma de uso se le denomina funciones anidadas, de tal manera que debemos colocar una función dentro de otra, respetando su sintaxis de trabajo. 1.1.1. Funciones Si (If) Anidadas Como habrá revisado en tus cursos anteriores, la tunción Si tiene la siguiente sintaxis: Sl(prueba_lógica, [valor_si_verdadero], [valor_si_falso]) Para usar de manera correcta la función Sl anidada podemos utilizar la función anidada SI como el segundo argumento de la función principal. Por ejemplo, para evaluar si una celda tiene alguno de tres valores posibles (10, 20, 30) podemos utilizar la siguiente fórmula: =SI(A1=10”diez” SI(A1=20,"veinte”,"treinta”)) La fórmula anterior se lee, si A1 es igual a 10, devuelve ei texto diez, en caso contrario si la celda A1 es igual a 20, devuelve el texto 20, en caso contrario devuelve el texto treinta. Ofro uso de la función Sl es con el anidamiento de e E funciones Y y O, para realizar dos o más D evaluaciones, con la función Y se deben cumplir 5 CANT DESCRIPCIÓN Ue IMPORTE todas las evaluaciones para que devuelva * MO ] verdadero, con la función O al menos se debe - cumplir una de las evaluaciones para que devuelva verdadero, observe el siguiente ejemplo: El objetivo de la preparación de la fórmula para el cálculo del importe es que se haga la multiplicación cuando las celdas que contienen ta cantidad (CANT) 1 y el precio unitario (P.U) contengan números, por lo que la fórmula queda de la guiente manera: 1  IMECAF, SC Excel 2016 Avanzado =SI(V(ESNUMERO(AS), ESNUMERO(CS))-A6*Ú5.”") Observe que en la fórmula también es usada la función ESNUMERO (ISNUMBER) para evaluar si las celdas contienen un valor, con la función Y las dos condiciones se deben cumplir, las celdas AG y D6 deben tener un número para que se efectúe la multiplicación. Adicionalmente, hay que considerar que, si tenemos una fórmula con varias funciones Si anidadas y está causando problemas para darle mantenimiento, probablemente debemos usar la función BUSCARV, 1.1.2. Función Coincidir (Match) con BuscarV (Vlookup) Recordemos que las funciones Coincidir y Buscarv pertenecen a la categoría de funciones de búsqueda y referencia. La función Coincidir busca un elemento especificado en un rango de celdas y, a continuación, devuelve la posición relativa de ese elemento en el rango. La función Buscarv, nos permite buscar un valor en una primera columna de una matriz, una vez localizado nos muestra dentro de la misma fila el valor que contiene la columna que deseamos obtener, Ambas funciones manejadas de manera conjunta ofrecen una optimización en el manejo de búsquedas de información, para ejemplificarlo, empezaremos por definir una tabla de datos como es mostrada en la siguiente figura. lA ARTUALZACIÓN CONSUTORÍA — TLAXCALA NSTALACIÓN — MORELOS $5 CONAGUA CONSULTORIA A continuación, realizaremos la búsqueda para conocer los datos Correspondientes de las claves que se muestran en la siguiente figura, observe que hay una clave que no se encuentra en el listado y que el orden de los fítulos es diferente a la lista original.  IMECAF, SO Excel 2016 Avanzado de w a o iS ESTADO ——— CUENTE —— SERVICIOS. IMPORTE — Comenzaremos colocando en la celda B41 la siguiente fórmula, para obtener la fecha correspondiente a las claves. Al aplicar un autorelleno en la fórmula obtendremos las fechas de cada clave, a excepción de la clave que no se encuentra en la lista. Se aplicó un formato de fecha corta a los datos obtenidos. =Buscarví$A41,$A$2:$F$26,2,0) 07/2016 25 TOS0 ENJA Para optimizar la función Buscarv, recurriremos a la función Coincidir, de tal manera que la fórmula queda de la siguiente manera: =Buscarv($A41,$A$2:5F$26,Coincidir(8$1,$A$1:$F$1,0),0) Aplicamos un autorelleno a todo el cuadro de información y observe que la función COINCIDIR permite localizar el número de columna correspondiente al título a pesar de que están en un orden diferente a la lista originai. Se aplicó un formato de número a los datos de importes. 16.000.00 25/01/2016 D6/04/2016 SEMARNAT INSTALACIÓN — TABASCO 34,000.00 19/06/2016 SECOFI CONSULTORÍA — SLP 50,C0C.cC ¡ 11/07/2015 CFE INSTALACIÓN — CAMPECHE 53,060.00 45 ¿TO30 ENÍA ENÍA BNÍA ENÍA aNA Por último, en la celda B41, usaremos la función SI.ERROR (IFERROR) para manejar el error de la clave que no se encuentra en la lista original, la fórmula queda de la siguiente manera: =Si.Error(Buscarv($A41,$A$2:$F$26,Coincidir(B$1,$A51:$F$1,0),0),”Clave no existe”) Aplicamos un autorelleno para todo el cuadro de información y obtendremos el resultado mostrado en la siguiente figura. Se aplicó un formato de fecha corta a los datos de fecha y se aplicó un formato de número a los datos de importes.  -] Y w IMECAF, SC Excel 2016 Avanzado Observe que en la columna A aparecen fechas de vencimiento de créditos otorgados a nuestra empresa, en la columna B se muestra el día de la semana de esas fechas. El ejercicio consiste en generar el pago un viernes antes de la fecha de vencimiento, ya que es el día de pago en tesoreria, o bien, si cae en viernes ese mismo día se realiza el pago. Lo anterior se resuelve con la siguiente fórmula tecleada en la ceida C2: =ELEGIR(DIASEM(A2), A2-2, A2-3, A2-4, A2-5, A2-6, A2, A2--1) Lo que estamos haciendo, es restar días con base al día de la semana obtenido con la función DiaSem, si la función DiaSem devuelve 1 equivale a domingo, por lo que restamos dos días a la fecha de ta celda A2 para que sea un viernes antes del vencimiento, esto lo controlamos con la función Elegir porque al recibir el índice (1 al 7), debemos colocar todos los elementos de ocurrencia del día 1 al 7, a partir del segundo argumento de la mencionada función. Aplique un autorrelleno a la fórmula y aplique un formato de fecha corta. Observe el resultado. A B € b 1. VENCIMIENTO | DIASEMANA | DIAPAGO |DIASEMANA 2 31/10/2016|lunes 28/10/2016 |viernes 3 17/11/2026 |jueves 11/11/2016 [viernes 4 06/12/2016|martes 02/12/2016 [viernes 5 16/12/2016 |viernes 16/12/2016 [viernes 6 21/12/2036|miércoles 16/12/2016 [viernes 1.2. Función Indirecto (Indirect) Descripción Devuelve la referencia especificada por una cadena de texto. Las referencias se evalúan de inmediato para presentar su contenido. Use INDIRECTO para cambiar la referencia a una celda en una fórmula sin cambiar la propia fórmula. Sintaxis INDIRECTO(ref, [a1]) La sintaxis de la función INDIRECTO tiene los siguientes argumentos: +. Ref Obligatorio. Una referencia a una celda que contiene una referencia de tipo A1 o F1C1, un nombre definido como referencia o una referencia a una celda como cadena de texto. Si ref ho es una referencia de celda válida, INDIRECTO devuelve el valor de error H¡REFI. Si ref hace referencia a otro libro (una referencia externa), el otro libro debe estar abierto. Si el libro de origen no está abierto, INDIRECTO devuelve el valor de error R¡REFI. Si ref hace referencia a un rango de celdas fuera del límite de filas de 1.048.576 o del límite de cotumnas de 16,384 (XFD), INDIRECTO devuelve el error 4¡REFI.  IMECAF, SC Nota Excel 2016 Avanzado Este comportamiento es diferente en las versiones de Excel anteriores a Microsoft Office Excel 2013, que ignoran si se excede el límite y devuelven un vator. » Ai Opcional. Es un valor lógico que especifica el tipo de referencia que contiene la celda ref. Si omite aí o es VERDADERO, ref se interpreta como una referencia de estilo A1. Si a1 es FALSO, ref se interpreta como una referencia estilo F1C1. Ejemplo Copie los datos de ejemplo en la tabla siguiente y cópielos en la celda A1 de una nueva hoja de cáículo de Excel. Para que las fórmulas muestren resultados, selecciónelas, presione F2 y después presione Entrar. Si lo necesita, puede ajustar los anchos de columna para ver todos los datos. Datos B2 B3 Marina 5 Fórmula '=INDIRECTO(A2) '"=INDIRECTO(A3) '=INDIRECTO(A4) '“=INDIRECTO('B"S.A5) 1,333 45 10 62 Descripción Resultado Valor de la referencia en la celda A2. La referencia es a la celda B2, que contiene 1,333 el valor 1,333. Valor de la referencia en la celda A3. La referencia es a la celda B3, que contiene 45 el valor 45 Puesto que la celda B4 tiene el nombre definido "George," ta referencia al nombre 10 definido es para la celda 84, que contiene el valor 10. Combina "B" con el valor de AS, que es 5. Esto, a su vez, se refiere a la celda B5, 62 que contiene el valor 62. 1.3. Función Indice (Index) La función Indice nos devuelve el valor de una celda con tan solo especificar el número de fila y de columna. Sintaxis INDICE(matriz; núm_fila; [núm_columna)) La sintaxis de la función INDICE tiene los siguientes argumentos:  IMECAF, SC Excel 2016 Avanzado a Matriz Obligatorio. Es un rango de celdas o una constante de matriz. + Si matriz contiene solo una fila o columna, el argumento núm_fila o núm_columna Je correspondiente es opcional. Ñ + Si matriz tiene varias filas y columnas, y solo usa núm_fila o núm_columna, INDICE devuelve a una matriz de dicha fila o columna completa. » Núm_fila — Obligatorio. Selecciona la fita de la matriz desde la cual devolverá un valor. Si omite núm_fila, se necesita el argumento núm_columna. Núm_columna Opcional. Selecciona la columna de la matriz desde la cual devolverá un valor. Si > omite núm_columna, se necesita el argumento núm_fila. Ejemplo > Capture los datos de ejemplo en la tabla siguiente y péguelos en la celda Aí de una hoja de cálculo - nueva de Excel. Para que las fórmulas muestren los resultados, selecciónelas, presione F2 y luego > Enter. Si lo necesita, puede ajustar el ancho de las columnas para ver todos los datos. Datos Datos _ Manzanas Limones LS A Plátanos Peras - Fórmula Descripción Resultado =|NDICE(A2:B3,2,2) Valor en la intersección de la segunda fila y Peras la segunda columna del rango A2:B3. =[NDICE(A2:B3,2,1) Valor en la intersección de la segunda fila y Plátanos la primera columna del rango A2:B3. 1.4. Aplicación de funciones Indirecto (Indirect) con BuscarV (Vlookup) Para revisar este punto considere la siguiente información, observe que son hojas diferentes. El objetivo del ejercicio es localizar información a través de varias hojas de cáiculo, a 00/01/2019 CAPUFE — ASESCRÍA EDO. DE 03/01/2018 NSTALACIÓN — MORELOS 03/01/2619 SCT MPLEMENTACIÓN PUEBLA TLAXCALA SONORA SINALOA CHIHUARUA ASESORÍA 55 VERACRUZ A A A ii a PI A  kosa IMECAF, SC Excel 2016 Avanzado TEMA 2. FUNCIONES DE BASES DE DATOS y aa Objetivos de la unidad En esta primera unidad se revisará la aplicación avanzada de funciones que le permita identificar la manera correcta para preparar fórmulas que incluyan funciones anidadas. a Objetivos particulares. « + Identificar el contexto a preparar de una función de Base de Datos » Crear áreas de criterio para peticiones de información + Aplicar funciones de bases de datos para la obtención de información « 2.1. Sintaxis de una función de bases de datos . Las funciones de base de datos en Excel permiten agilizar nuestro trabajo con información que está organizada como una tabla de datos, es decir, de forma tabular. Estas funciones nos permiten contar, . sumar, multiplicar los valores de una columna que cumplen con condiciones específicas llamadas criterios, e Sintaxis general de una función de bases de datos. « =Nombre_de_la_función(Rango_de_BD, "Campo”, Rango de área de criterios) e Observe que, para el uso de funciones de bases de datos, la sintaxis pide una área de criterios que € prepararemos en los ejemplos, € Para el desarrollo de funciones de bases de datos considere lo siguiente $ + Cualquier rango se puede usar como argumento criterios, siempre que incluya al menos un a rótulo de columna y una celda debajo del mismo para especificar la condición. y e : a A a Por ejemplo, si el rango G1:G2 contiene el rótulo de columna Ingresos en la celda G1 y la »7 cantidad 10.000 en la celda G2, el rango podría definirse como Coincidirlngresos y ese nombre 6 podría usarse como argumento criterios en las funciones de base de datos. Ñ + Aunque el rango de criterios puede ubicarse en cualquier parte de la hoja de cálculo, no lo e coloque debajo de la lista. Si agrega más información a la lista, la nueva información se incluirá Z en la primera fila debajo de la misma. Si la fila de debajo no está vacía, Microsoft Excel no puede e agregar la nueva información. 6 + Asegúrese de que el rango de criterios no se superpone a la lista. ul e +» Pararealizar una operación en una columna completa de la base de datos, inserte una línea en blanco debajo de los rótulos de columna en el rango de criterios. Para el desarrollo del tema considere la siguiente información. 7 ..ASA  mm IMECAF, SC Excel 2016 Avanzado BDSUMA(base_de_datos, nombre_de campo, criterios) La sintaxis de la función BDSUMA tiene los siguientes argumentos: > + Base_de_datos Obligatorio. Es el rango de celdas que compone la lista o base de datos. Una > base de datos es una lista de datos relacionados en la que las filas de información son registros Ñ y las columnas de datos, campos. La primera fila de la lista contiene los rótulos de cada == columna. > + Nombre_de campo Obligatorio. Indica qué columna se usa en la función. Escriba el rótulo = de la columna entre comillas, como por ejemplo "Edad" o "RENDTO", o un número (sin las comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente. Í a pS 8 8 e E PP a ca 3 I a 2 CENTRO o] 3 2 ASESORÍA CENTRO SUR I 3 E PEMEX MISTALAC.ON. CENTSO SUR . a 4 28/02/20.8 SCT PAPLEMENTAC ÓN CENTRO NORTE I E 3 19/03/2 E CENTRO NORTE TLAXCALA » 7 6 DISEÑO WES NOROESTE SONORA E al 7 BASE DE DATOS NOROE! ¡ > 2 S CAPACITACIÓN MORCESTE > so ñ ASESORÍA NTRO NORTE 131,000 | “ 10 NSTALACIÓN. CRIENTE 143,000 i e] oz ó ORIENTE 155,090 l 3 2 SUFGCESTE 262,000 É 43 SURDESTE 369.906 | MW BASE DE DATOS CENTRO SUR 176,008 1] 15 CAPACITACIÓN CENTRO SUR 183,000 | a 15 ASESORÍA CENTRO SUR 5 7 INSTALACIÓN: CENTRO NORTE E CENTRO NORTE | E 3 SONORA. | >. 2 29/22/2018 CFE SHALDA E) E 2.2. Función BDSuma (DSum) t | Descripción E | Suma los números de un campo (columna) de registros de una lista o base de datos que cumplen las 1=—) condiciones especificadas. | Sintaxis 1 t t t r 1 | l + Criterios (obligatorio). Es el rango de celdas que contiene las condiciones especificadas. Puede usar cualquier rango en el argumento Criterios mientras este incluya por lo menos un rótulo de columna y a! menos una celda debajo del rótulo de columna en la que se pueda especificar una condición de columna.  IMECAF, SC Excel 2016 Avanzado Ejemplo: Tome en cuenta el listado de información indicada en el punto 2.1. Es necesario obtener la sumatoria de importes del cliente DECSIS para el primer trimestre del 2018. Prepare el área de criterios como se indica en ta siguiente figura. A B 28 ¡ÁREA DE CRITERIOS En alguna celda desocupada teclee la fórmula =BDSUMA(A1:G21,"IMPORTE",A30:031) Resultado 2.3. Función BDPromedio (DAverage) Descripción Devuelve el promedio de tos valores de un campo (columna) de registros en una lista o base de datos que cumple tas condiciones especificadas. Sintaxis BDPROMEDIO(base_de_datos, nombre_de_campo, criterios) La sintaxis de la función BDPROMEDIO tiene los siguientes argumentos: + Base_de datos es elrango de celdas que compone la lista o base de datos. Una base de datos es una lista de datos relacionados en la que las filas de información son registros y las columnas de datos, campos. La primera fila de la lista contiene los rótulos de cada columna. + Nombre_de_campo indica qué columna se usa en la función. Escriba el rótulo de la columna entre comillas, como por ejemplo "Edad" o "Rendimiento", o un número (sin las comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente, * Criterios es el rango de celdas que contiene tas condiciones especificadas. Puede usar cualquier rango en el argumento Criterios mientras este incluya por lo menos un rótulo de columna y al menos una celda debajo del rótulo de columna en la que se pueda especificar una condición de columna. JT O O O NS O O E ce  IMECAF, SC Excel 2016 Avanzado de columna y una celda debajo del mismo en la que se pueda especificar una condición para la columna. Ejemplo: Tome en cuenta el listado de información indicada en el punto 2.1. Es necesario contar las celdas ocupadas por cualquier tipo de dato del cliente DECSIS para el primer semestre del 2018. Prepare el área de criterios como se indica en la siguiente figura. 31 (2018 <=30/6/2018 DECSS En atguna celda desocupada teclee la fórmula =BDCONTARA(A1:G21,"CLIENTE",A30:C31) Resultado » o o 2.6. Función BDMax (DMax) Descripción Devuelve el valor máximo de un campo (columna) de registros en una lista o base de datos que cumple las condiciones especificadas. Sintaxis BDMAX(base_de_datos, nombre_de_campo, criterios) La sintaxis de la función BDMAX tiene los siguientes argumentos: » Base_de_datos Obligatorio. Es el rango de celdas que compone la lista o base de datos. Una base de datos es una lista de datos relacionados en la que las filas de información son registros y las columnas de datos, campos. La primera fila de la lista contiene los rótulos de cada columna. » Nombre_de_campo Obligatorio. Indica qué columna se usa en la función. Escriba el rótulo de la columna entre comillas, como por ejemplo "Edad" o "RENDTO", o un número (sin las comillas) que represente la posición de la columna en Ja lista: 1 para la primera columna, 2 para la segunda y así sucesivamente. + Criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas. Puede usar cualquier rango en el argumento criterios mientras este incluya al menos un rótulo de columna y una celda debajo del mismo en la que se pueda especificar una condición para la columna.  IMECAF, SC Excel 2016 Avanzado Ejemplo: Tome en cuenta el listado de información indicada en el punto 2.1, Es necesario obtener el importe mayor para el servicio de CAPACITACIÓN para el primer semestre del 2018. Prepare el área de criterios como se indica en la siguiente figura x 5 ¿ ÍS 28 ÁREA DE CRITERIOS 29 20 Ed En alguna celda desocupada teclee la fórmula =BDMAX(A1:G21,"IMPORTE",A30:031) Resultado 2.7. Función BDMin (DMin) Descripción Devuelve el valor mínimo de un campo (columna) de registros en una lista o base de datos que cumple las condiciones especificadas. Sintaxis BDMiN(base_de_datos, nombre_de_campo, criterios) La sintaxis de la función BDMIN tiene los siguientes argumentos: . Base_de_datos Obligatorio. Es el rango de celdas que compone la lista o base de datos. Una base de datos es una lista de datos relacionados en la que las filas de información son registros y las columnas de datos, campos. La primera fila de la lista contiene los rótulos de cada columna. Nombre_de_campo Obligatorio. Indica qué columna se usa en la función. Escriba el rótulo de la columna entre comillas, como por ejemplo "Edad" o "RENDTO", o un número (sin las comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente. Criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas. Puede usar cualquier rango en el argumento criterios mientras este incluya al menos un rótulo de columna y una celda debajo del mismo en la que se pueda especificar una condición para la columna. W is le la (e e Y IMECAF, SC Excel 2016 Avanzado Ejemplo: Tome en cuenta el listado de información indicada en el punto 2.1. Es necesario obtener el importe menor para el servicio de CAPACITACIÓN para el primer semestre del 2018. Prepare el área de criterios como se indica en ta siguiente figura E n 28 AREA DE CRITERIOS 29 2048 En alguna celda desocupada teclee la fórmula =BDMIN(A1:G21,"IMPORTE”,A30:C31) Resultado 436 s ell mal 2.8. Función BDExtraer (DGet) Descripción Extrae un único valor de una columna de una lista o una base de datos que cumple las condiciones especificadas. Sintaxis BDEXTRAER(base_de_datos, nombre_de_campo, criterios) La sintaxis de la función BDEXTRAER tiene los siguientes argumentos: » Base_de_datos Obligatorio. El rango de celdas que compone la lista o base de datos, Una base de datos es una lista de datos relacionados en la que las filas de información son registros y las columnas de datos, campos. La primera fila de la lista contiene los rótulos de cada columna. + Nombre_de_campo Obligatorio. Indica qué columna se usa en la función. Escriba el rótulo de la columna entre comillas, como por ejemplo "Edad" o “Rendimiento”, o un número (sin las comillas) que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente. » Criterios Obligatorio. Es el rango de celdas que contiene fas condiciones especificadas. Puede usar cualquier rango en el argumento criterios mientras este incluya al menos un rótulo de columna y una celda debajo del mísmo en la que se pueda especificar una condición para la columna.  IMECAF, SC. Excel 2016 Avanzado Procedimiento para convertir texto en columnas: a) Seleccione el rango de celdas con la información a dividir. b) Active la ficha Datos y en el grupo Herramientas de datos, haga clic en el comando Texto en Columnas. Teto en Henamientas de dato c) Enel Asistente seleccione la opción Delimitados y haga clic en el botón Siguiente. ETS lba los datos con mayar preciién: omo camas o tabulaca! pos estan alineadas en columnas <an espacios cancion . Siguientes 1 Elnalizze d) En el segundo paso del asistente, seleccione un separador y haga clic en el botón Siguiente. 2de3 y | prstacia per concen tex en colummes los separadores contenidas an los datos, Se puede ve como. vesuticos somo un: Yista presa se los dates Cansatar «ota ete> dl Eimalizar => 21 y PP AA A ai Sa ¡Fl IMECAF, SC Excel 2016 Avanzado e) En el tercer paso puede elegir el tipo de dato para las columnas resultantes, en este caso no seleéccionaremos algún tipo. En la caja Destino, seleccione la celda donde se colocará las columnas y haga clic en el botón Finalizar. Asicrente paa consertetedo sn coumnes- peso 2 de ox Ea parara gemie a Formato de los dates “e Depas 5380 ua de ios datos. otunno y establecer xl formato ae as datos, sacados 1 f) Observe la división del texto columnas como resultado del uso del Asistente. 1 2 TmBerers-Les 3 Matt Groening £ Nelson Mandela 5 Steve Hawkings A Gary Kasparov 7. Bobby Fisher 3 Bl Gates 9 ibert Hoffman 10, Oscar Niemeyer 3.2. Listas de datos dependientes Tm Mar Nefson Steve Gary Bobby Bill Albert Oscar B Berners-Lee Groening Mandela Hawkings Kasparov Fisher Gates Hoffman Kiemeyer Las listas de datos dependientes es una variación de la validación de datos de tipo fista y consiste en desplegar información relacionada con un tema en específico, para el desarrollo del ejemplo usaremos un listado de Hardware, Software y Redes 1. Preparar el listado de dependencias en una hoja nombrada Elementos, observe la figura. Conertor RJa5  IMECAF, SO Excel 2016 Avanzado 2. Nombre el rango de cada columna, por ejemplo, seleccione el rango A1:A7, presione Shift + + Ctrl + F3. Seleccionar Fila superior y haga clic en Aceptar 3 AAA [cpu lautocaD [Mentor (reus [saninete Icivi30 [*uente de poder [Navisuorks limaresora [Plotter Carcel 3. Repita esta operación para los rangos con información de las columnas B y C de tal manera que genere en total tres nombres (HARDWARE, SOFTWARE y REDES) 4. Enla celda E2 prepare una Validación de datos de tipo Lista, en el origen seleccione el rango A1:C1. Observe la siguiente figura E 3 sisters] 5. A continuación, en la celda F2 prepare una validación de tipo lista, en el Origen use la función =Indirecto($E$2) como es mostrado en la siguiente figura.  IMECAF, SC Excel 2016 Avanzado Se puedén mostrar u ocuitar los datos detallados presionando en el signo más, en el signo menos y en los números 1, 2, 3 o 4 que indican el nivel de esquema.), muestra datos de detalle (datos de detalle: para subtotales automáticos y esquemas de hoja de cálculo, filas o columnas de subtotal que totalizan los datos de resumen. Los datos de detalle suelen ser adyacentes, por encima o a la izquierda, a los datos de resumen.) del nivel externo anterior, representado por un número inferior de simbolos de esquema. Use un esquema para mostrar rápidamente filas o columnas de resumen, o bien para mostrar los datos de detalle de cada grupo. Puede crear un esquema de filas (como se ilustra en el ejemplo siguiente), un esquema de columnas o un esquema de filas y columnas. Considere la siguiente información para ta revisión de este tema 23,900 35,000 47,500 105,000 27,000 29,000 51,000 — 117,000 22,000 FCO 220 00 55,900 — 129,000 25000 E O ALO 0.000 141,000 39,000 21,000 51000 153,000 70,000 85000 155.000 115,009 255,000 ers.oo0 ' 65,000 200% Le 13000 ; 55,500 51,000 165.000 > 47,900 3 59000 71900 1PTOUO 51,000 63100 35000 189,000 mn 55.000 2,00 79900 201.000 0 36.000 S7000 71400 83.000 213,000 100000 195,000 255,000 130.000 265,000 315,000 180,000 195,000 175.000 395600 190,000 220,000 340,008 250,000 280,000 530,000 310,000 390,000 630.000 1.590.000 1 > 3 AUNADO RT a SUN AO El ET sun] UNA SUMAS 0 E E una) cars a > ASS) UNatRsaSS una leer onalicsel Traje 06 2 sua rara umaleace, uva sumaseara a Una UNAS 08,491 ua pc camot) Uana2l SU 16.625.011) mapa es SUMA AE LaS SU 01204:) Loca ea, SI MASIAER IMAN SUMA UI AA suyas MAIS MANS MAGIA AIUIz UVA MORIA Generación del autoesquema. Para crear un esquema siga el procedimiento indicado a continuación. 1. Ubique el indicador dentro dei rango de información. 2. Active la ficha Datos en el grupo Esquema y haga clic en el comando Agrupa y seleccione Autoesquema  IMECAF, SC Excel 2016 Avanzado dB Esquema 3. Observe los indicadores de niveles que se generan en los extremos izquierdo y superior de la hoja de cálculo. j | [ 3 ES a (3 b E 4 a 2 + . 200 22000 +10 35,000 39,090 32000 37.020 Y zop0o 2 22500 51.000 INAMESOAN 155/00 200.000 115.00 50 asuru $5 uso 17000 50.000 510c0 63190 suo 57000 79000 291000 5eoco JLOOO 10000 E 83000 222,000 E 120,900 135/0C0 255000 150.000 165000 315.070 130.090 195000 375000 045000 190,003 P3ODCO 4LODCO 250.000 200000 550.000 310000 S4c/gC e5ej0Na 1,530,000 62000 152,000 195106 "275.000 645.000 67006 — 165,900 73008 177000 7SD0O 185000 As BIZ es SEMI 155,000 ' 215,000 275,000 645,000 255,000 315,000 375,000 945,000 310,080 530,000 650,009 1,590,000 Eliminación del esquema. Para eliminar un esquema siga el procedimiento indicado a continuación. 1. Ubique el indicador dentro del rango de información convertido en esquema. 34 Acstrar «Jetalio. q 1 Bu Desagrarar Subtotal 3 Ocultas detalle 2. Active la ficha Datos en el grupo Esquema y haga clic en el comando Desagrupar y seleccione Borrar esquema. Observe que la información regresa a su estado original.  IMECAF, SC Excel 2016 Avanzado 3.5. XML Microsoft Excel facilita la importación de datos XML (Lenguaje de marcado extensible) creados a partir de otras bases de datos y aplicaciones para asignar elementos XML de un esquema XML a celdas de una hoja de cálculo y para exportar datos XML revisados con vistas a la interacción con otras bases de datos y aplicaciones. Estas características XML convierten a Office Excel en un generador de archivos de datos XML con una interfaz de usuario familiar. ¿Por qué utilizar XML en Excel? XML es una tecnología diseñada para administrar y compartir datos estructurados en un archivo de texto legible para el usuario. XML sigue directrices estándar del sector y puede ser procesado por una amplia gama de bases de datos y aplicaciones. El uso de XML permite a los diseñadores de aplicaciones crear sus propias etiquetas, estructuras de datos y esquemas personalizados. En resumen, XML facilita considerablemente la definición, la transmisión, la validación y la interpretación de datos entre bases de datos, aplicaciones y organizaciones. Procedimiento para importar un archivo XML 1. Haga clic en la ficha Datos > Obtener datos > Desde un archivo > Desde un archivo XML 2. Vayaala unidad, carpeta o ubicación de Internet que tiene el archivo de datos XML (.xml) que desea importar. W  IMECAF, SC Excel 2016 Avanzado Observe las filas insertadas con los subtotales de cada grupo y el total general de la lista. Además, se agrega un esquema en la parte izquierda de la lista. Crear informes de resumen Al agregar subtotales automáticos a una lista, ésta aparecerá esquematizada. Puede crear un informe de resumen haciendo clic en los simbolos del esquema para ocultar los detalles y mostrar sólo los subtotales. Jl a 8 c D E E G | 77 m7 E SEDES BSO UE a | | Total BMV 261,000.000 l Total ERICSSON 261,400,000 | Total GYMPASS 262.200.000 l Total HERMES 201,800,c00 | Total PMA 202,609,000 | Total general 1.009,.£250,000 3.7. Filtro avanzado Para filtrar con criterios complejos, se usa la herramienta de filtro avanzado, que permite combinar criterios de varias columnas. 4 Filtrado Avanzado ofrece dos opciones: i e - Filtrar sin mover la lista. 4 e Filtrar copiando a otro lugar Filtro avanzado permite filtrar un rango en contexto, como el comando Autofiltro, pero no muestra listas desplegables para las columnas. En lugar de ello, tiene que escribir los criterios (criterios: condiciones que se especifican para limitar los registros que se incluyen en el conjunto de resultados de una 4 consuíta o un filtro.) según los cuales desea filtrar los datos en un rango de criterios independiente situado sobre el rango. Un rango de criterios permite filtrar criterios más complejos. 4 Para el uso del filtro avanzado, es necesario tener preparado una fista de datos, un área de criterios y 1 opcionalmente, un área de extracción. Preparación del filtro avanzado. ista de datos. Es el listado de información con títulos específicos en cada una de las columnas (campos). wm uo Z Ól Y Y al a IMECAF, SC Excel 2016 Avanzado 2018 $T EFE SEMER SEMASMAT FELOFS 15 PEMEX Gs/03/201€ TONAGUE m1 13 13/03/2015 21/09/2016 CFE 25/03/2016 SENER 06/02/2016 SEMARNAT 12/04/2015 SECOFt 22/74/2015 PEMEX oo IS SENER SEMARINAS PERIEX 2on CFE Área de Criterios INGENTERO 2 ¿NGEN:ERO 1, HERO GENERO 31 MSEN.ERO 02 MGENIERC 3 MSEN ERO MEN ERC MGEN EROS 35 INGENIERO 37 ¿NGENCERO 99 JERO 69 GENERO 1 NGENIERO 82 INGENIERO 92 HGENTERO Es HGENIERO 05 MGENERO 05 GEN CONSULTORÍA NSTALACIÓN YIATUADZACIÓN CONSULTORÍA NSTALACIÓN VIRTUALIZACIÓN CONSULTORÍA NSTALACIÓN VIRTUALIZACIÓN INSULTO e IMSTALAD¡ÓN: Y 2TUAL ZACiÓN CONSULTORÍA NSTALACIÓN VIRTUALIZACIÓN MTORÍA, ALACIÓN ALZACIÓN: CONSULTORÍA. in ón VIRTUALIZACIÓN CONSULTORÍA MSTALACIÓN 20, DE MEX. TLAXCALA MORELOS HIDALSO SHRLAHUA NUEVO LESH =p JALISCO GUERRERO VERACRUZ ZAMPECHE TABASEO LO EDO! DE MEX. TLAXTALA MORELOS H UALGO TH HUJARUA NUEVO LEON SLP JAaL SiO SHERRERO 35,000.09 38,009.00 42.000.090 Rango de celdas que contiene un conjunto de condiciones de búsqueda. Un rango de criterios está formado por una fila de rótulos de criterio y, como mínimo, una fila que defina las condiciones de búsqueda. Rango de extracción. VIRTUALIZACIÓN Un rango de extracción, aunque es opcional, se sugiere usar para no trabajar directamente con la información original. El rango de extracción está formado por una fila de rótulos que deben ser los mismos de la lista de datos, aunque pueden estar en diferente orden.  IMECAF, SC Excel 20168 Avanzado Aplicación del filtro avanzado. á Una vez que ya hemos generado los elementos del filtro avanzado, el procedimiento a seguir para á aplicarlo es el siguiente. 1.- Ubique el indicador de la celda en la hoja de extracción. 2. Active la ficha Datos en el grupo Ordenar y Filtrar haga clic en el comando Avanzadas. a 4 á á 3. Enla ventana Filtro avanzado, haga clic en la opción Copiar a otra ubicación. t 4. Enla caja Rango de lista: seleccione el rango de la lista de datos a filtrar. á 5. Enla caja Rango de criterios: el rango de títulos y renglones de condición. á 6. En la caja Copiar a: seleccione sólo los títulos. i a | fte asancaco me x Ear | . e : € 7. Haga clic en Aceptar. e Observe el resultado, solo se extrae la información solicitada en el área de criterio e a 99/01/2016 CONAGUA EDO, DE MEX 12,000.00 5 y 5 92/02/2015 SENER E HIDALGO 15,900.40 4 3 26/02/2016 PEMEX E SLP 24,980.00 > 5 11 21/c3/2015 CFE Ny VERACRUZ 30,000.00 $ Lé 14/04/2DL5 SECOFI E CUA 36,000.00. ú É 17 08/05/2015 SCT IN MORELOS. 382,000.00 A 8 20 O1/CS/Z016 SEMARNAT ¿NGEMIERO 02 VIRTUALIZACIÓN NUEVO LEÓR 38,000.00 23 25/06/2016 CONAGUA —¡MGENEROOS VIRTUALIZACIÓN GUERRERO — 59,000.00 a Dada la importancia de los criterios o condiciones para el filtro avanzado, a continuación, se enlistan e varios ejemplos que le pueden ayudar en filtros personalizados a través del filtro avanzado. z a Condición para filtrar o extraer solo el tipo de proyecto Autopista. Ñ a " AUTOPISTA . A A a v a IMECAF, SC Excel 2016 Avanzado Sugerencia. Para especificar una referencia de celda como Ventas3!B4 en una fórmuta sin escribir, escriba la fórmula hasta el punto en el que necesite la referencia, haga clic en la etiqueta de la hoja de cáiculo y, a continuación, haga clic en la celda. Si los datos que se van a consolidar están en las mismas celdas de otras hojas de cálculo + Escriba una fórmula con una referencia 3D que use una referencia a un rango de nombres de hojas de cálculo. Por ejemplo, para consolidar los datos de las celdas A2 desde Ventas hasta Marketing (ambos incluidos), tendría que escribirio siguiente en la celda A2 de la hoja de cálculo maestra: a m entas EH la Nota. Si el libro está configurado para calcular fórmulas automáticamente, una consolidación por fórmula siempre se actualiza automáticamente cuando cambian los datos de las hojas de cálculo independientes. 4.2. Consolidación por posición Use este método cuando los datos de varias áreas de origen se organizan en el mismo orden y usan las mismas etiquetas de fila y columna (por ejemplo, cuando tiene una serie de hojas de cálculo de gastos creadas a partir de la misma plantilla). US En cada hoja de cálculo que contiene los datos que desea consolidar, prepare los datos del siguiente modo: += Asegúrese de que cada rango de datos está en formato de lista: cada columna tiene una etiqueta en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco. + Coloque cada rango en una hoja de cálculo diferente, pero no ponga ningún rango en la hoja de cálculo donde vaya a colocar la consolidación. + Asegúrese de que cada rango tiene el mismo diseño. Sugerencia. Si suele consolidar datos frecuentemente, puede que sea conveniente basar las hojas de cálculo en una plantilla de hoja de cálculo que usa un diseño coherente. 2. Enla hoja de cálculo maestra, haga clic en la celda superior izquierda del área donde desea que aparezcan los datos consolidados. Nota. Para no sobrescribir los datos existentes en la hoja de cálculo de destino que contiene los datos que está consolidando, asegúrese de dejar celdas suficientes a la derecha y debajo de esta celda para los datos consolidados. 3. Enla ficha Datos del grupo Herramientas de Datos, haga clic en Consolidar.  IMECAF, 9. 10. , SC Excel 2016 Avanzado Haraamentes de datos En el cuadro Función, haga clic en la función de resumen que desea que use Microsoft Excel para consolidar los datos. Si la hoja de cálculo que contiene los datos que desea consolidar está en otro libro, haga clic en Examinar. Concoliriar ETE Eunción: £l bonne. ,] ¿regar Usar rótulos en [Fila zupertor | EL Colamra izquierda rear gnculos con tos datos de origen Cerrar La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamación. Si ta hoja de cálculo que contiene los datos que desea consolidar está en el libro actual, haga lo siguiente: * En el cuadro Referencia, haga clic en el botán Contraer diálogo —- para seleccionar los datos de la hoja de cálculo, Haga clic en la hoja de cálculo que contiene los datos que desea consolidar, seleccione los datos y, a continuación, haga clic en el botón Expandir diálogo p En el cuadro de diálogo Consolidar, haga clic en Agregar y repita los pasos 7 y 8 para agregar todos los rangos que desea. Indique cómo desea actualizar la consolidación mediante una de las siguientes acciones: Para configurar la consolidación de manera que se actualice automáticamente cuando cambien los datos de origen en otro libro, active la casilla de verificación Crear vínculos con los datos de origen. Importante. Active esta casilla de verificación únicamente si la hoja de cálculo que contiene los datos se encuentra en otro libro. Cuando active esta casilla de verificación, ya no podrá cambiar las celdas y los rangos que se van a incluir en la consolidación. LH sa IMECAF, SC Excel 2016 Avanzado «é Para configurar la consolidación de modo que pueda actualizarla manualmente cambiando las celdas y los rangos incluidos, desactive la casilla Crear vínculos con los datos de origen 11. Haga clic en Aceptar 4.3. Consolidación por categoría Use este método cuando los datos de varias áreas de origen se organizan de forma diferente, pero se usan las mismas etiquetas de fila y columna. Por ejemplo, puede usar este método cuando tiene una serie de hojas de inventario de cada mes en las que se usa el mismo diseño, pero cada hoja de cálculo contiene distintos elementos o un número diferente de elementos. 1. En cada hoja de cálcuto que contiene los datos que desea consolidar, prepare los datos del siguiente modo: + Asegúrese de que cada rango de datos está en formato de lista: cada columna tiene una etiqueta en la primera fila, contiene hechos similares y no tiene filas o columnas en blanco. «* Coloque cada rango en una hoja de cálculo diferente, pero no ponga ningún rango en la hoja de cálculo donde vaya a colocar ta consolidación. + Asegúrese de que cada rango tiene el mismo diseño. Sugerencia. Si suele consolidar datos frecuentemente, puede que sea conveniente basar las hojas de cálculo en una plantilla de hoja de cálculo que usa un diseño coherente. 2. En la hoja de cáículo maestra, haga clic en la celda superior izquierda del área donde desea que aparezcan los datos consolidados. Nota. Para evitar sobrescribir datos existentes en la hoja de cálculo de destino que contiene tos datos que está consolidando, asegúrese de dejar celdas suficientes a la derecha y debajo de esta celda para los datos consolidados. 3. Enla ficha Datos del grupo Herramientas de Datos, haga clic en Consolidar. 4. En el cuadro Función, haga clic en la función de resumen que desea que use Microsoft Excel para consolidar los datos. 5. Sila hoja de cálculo que contiene los datos que desea consolidar está en otro libro, haga clic en Examinar. o 38]  IMECAF, SC z Excel 2016 Avanzado ar escenario Ci ombre del escenario. peststal + cambiantes > si eli en las celdas para seteccanar las cells cambiantes na adyacentes, Comentario: da par tmando e 05/01/2018 E ocultar aceptar Conca 6. Haga clic en Aceptar. 7. En la caja de diálogo Valores del escenario escriba los valores del escenario para cada celda cambiante. valores del escenario Pi Intraduzca un vatar aca cada celda cambiante, le 5852 | 150000 A la $833 > | a 35S3 | £ ES e | $E38 e Agregar E Cancelar | 8. Haga clic en Aceptar. 9. Repita los pasos 2 al 8 las veces necesarias hasta tener todos los escenarios posibles para el manejo de su información, esto le permitirá mostrar varios contextos de información- Administrador de escenarios ? x “Tggregan. Eliminar Hogitecar. Comtiman | Besumen.. | Cetdas cambiantes: [589258510 Comentario: Creado par LENOVO el 2441 Tdodificado par LENOYO el 52,19; | Utsstrar Car  “W W W IMECAF, SC Modificar un escenario Seleccione el escenario del cuadro de diálogo Escenarios. Haga clic en Modificar. Haga clic en Aceptar. Modifique los valores de cada celda cambiante que desee. Haga clic en Aceptar. one pn 5.3. Combinar y eliminar escenarios Combinar un escenario 1. Entre a la hoja en donde desea importar los escenarios. Si lo desea modifique el nombre, las celdas cambiantes y los comentarios, Excel 2016 Avanzado 2. Haga clic en la ficha Datos / grupo Previsión / / botón Análisis de hipótesis / comando Administrador de Escenarios 3. Clic en el botón Combinar. Agregar. Enminar Mogificar. Goimbsinar. Resumen. 4. Seleccione el libro y la hoja que contiene los escenarios. Cembinar escenarios sárlos dasds 6 MANEJO DE ESCE POS isx Resumen de escenario Tabia dinámica del es ESCENARIOS ED Hoja Hojalí Existe 1 escenado en lá hoja de origen 1 aceptar? 5. Haga clic en Aceptar. Eliminar Escenarios. 1. Seleccione el escenario del cuadro de diálogo Escenarios. 2. Haga clic en Eliminar para eliminarlo. Cancelar  IMECAF, SC Excel 2016 Avanzado 5.4. ds 2. 3. 4. 5.5. don = Crear un informe de resumen de escenarios En el cuadro de diálogo Escenarios seleccione Resumen Seleccione el tipo de resumen, en la figura está marcada la opción Resumen O ¡rtecne de tabla dsnámica de ascenacio ettas ce resultados lead 2 En Celdas de resultado seleccione la celda que contiene la fórmula. Haga clic en Aceptar. Excel agregará una hoja antes de la hoja actual con el resumen solicitado. 22000 1o sra Evitar cambios en un escenario Entre al Administrador de escenarios Haga clic en el nombre del escenario que desea proteger u ocultar. Haga clic en Modificar Para evitar modificaciones accidentales, active la casilla de verificación Evitar cambios Iladificar escenario ox Nombre del escenarios carnal ldas combates: Use CTRL<clic en las celdas paca sezeccionar las celdas camblantes no ssgacentes. Comentario: Creado por LENGC a Etadifizado par LENO: Madífitado por ánmar Protección rocas] Coal Y cancelar LA y me IMECAF, SC. Excel 2016 Avanzado 4. Ehla lista Administrar, haga clic en Complementos de Excel y clic en Ir. DY au w - VU 0. Se v  IMECAF, 50 Excel 2016 Avanzado 6.5. Definición, edición y eliminación de restricciones Para ejemplificar el manejo de restricciones suponga que nos reportan un ajuste del presupuesto mostrado en la imagen con las siguientes características: El presupuesto total ahora debe ser de 3,900,000 Las partidas de RRHH y Otros queda igual Subcontratos debe ser con un mento máximo de 1,950,000 A E , 350,000 200,000 2,200,000 2,200.00€ 820,000 4,550,000 e RAHH MATERIALES AQUIVARIA SUBCONTRATOS OTROS TOFAL mor be am Una vez habilitada la herramienta utilícela de la siguiente forma: 1. 2. Clic en la ficha Datos / grupo Análisis/ botón Solver. Haga clic en el cuadro de referencia Establecer objetivo y seleccione la celda que contiene la fórmula que utilizará Solver para caícular. En la opción Para, seleccione la opción que desea calcular (Max, Min o Valor de, esta última requerirá un valor específico). Para nuestro ejemplo seleccione Valor de: Usando el cuadro de referencia Cambiando las celdas de variables, seleccione las celdas que contienen los argumentos que desea que calcule Solver. En la sección Sujeto a las restricciones agregue, cambie o elimine los criterios a seguir en las celdas que contienen los argumentos a calcularse. Puede hacerlo con el botón Agregar Agregar restricción x Petesencia de caida sesz Li- Zceptar Agregar Lanceias La configuración de la ventana Solver debe verse como la siguiente figura 47  Sa de d A 1 b IMECAF, SC Excel 2016 Avanzado 14 tin 2 Susto all (sos. Agrega Cambiar Eliminar Bestantecertado i EargariCuardar EA Consta variables sin restricciones 40 0 negativas Hetodo de GRG Monlinear pl Opciones resolución Hiétado de rescia amas qe Soler ox Ineniss suavizados. Seleccione Bneates, ¡seleccione e motar Evoluticnary para. Seleccione sí motor GRS Honlinear gas al mator 1? Simples para problemas de $2 problemas de Solves no sumizados. Cerrar 6. Clic en el botón Resolver, observe en resultado en la hoja de cálculo, si es adecuado haga clic en Aceptar QUINARIA SUBCONTRATOS Resultados de Seiver x 130,009 encontró sa ss uc ón Se curspien todas as 197,488 «iones y cand e. ones oprimas 1,108,861 1,643,672 | $00,008 [2.300.099 'cemes de esquema | ler escenar Solver encontró una solución. Se cumplen todas las restricciones y condiciones óptimas. proa fecal. 61 user E repisa, 6.6. Guardar desde Solver los valores de celdas ajustables como un escenario 1. Una vez que ha optimizado un resultado basado en varias restricciones, puede almacenar el resultado como un escenario dando un clic en el botón Guardar escenario.  IMECAF, SC Excel 2016 Avanzado TEMA 7. MANEJO AVANZADO DE TABLAS DINÁMICAS RA AAA A A A AA A e A A A A a A A A o id Objetivos de la unidad Es esta unidad revisaremos los aspectos avanzados para el manejo de las tablas dinámicas con la finalidad de que pueda obtener más funcionalidad de esta herramienta tan popular. Objetivos particulares. + Aplicar funciones integradas en Excel para crear Tablas Dinámicas de mayor complejidad + Hacer uso de la parte análisis de las Tablas dinámicas + Crear gráficos dinámicos con funcionalidad avanzada Para aprovechar el uso de las tablas dinámicas, en ocasiones debemos recurrir a funciones integradas en Excel, campos calculados, mostrar valores en porcentajes, etc., todo ello para obtener los reportes necesarios para un manejó óptimo y flexible de la información. 7.1. Funciones integradas en listas de datos Para ejemplificar el uso de funciones integradas, considere la siguiente lista de datos. 1 ai PISE 3 2 HIGEN ERO Na PIGEN ERC TLaxDaLe E MGEMIERO EORELOS EnraE WIGEMIERO 05 SNAIDALGO 2d 8 NIGENERO 06 HUSA. g 7 MGENERO HUEVO LEÓN 0 NGENERO 08 E MGEN ERO e GENERO C1 ap (MGEN ERC 1412 IMGENIERO 03 14. 13 INGENIERO 06 15) 12 16) 15 1 18 e 1 18) 18 16/05/2018 0% 20) 18 24/05/2018 8 INSTALACION] EN GUERSERO HIGEMIERO 03 MSENERO Consideremos que estos servicios se encuentran en Cartera Vencida y es necesario catalogarlos por categorías de: + de 200 días, + de 100 días, - de 100 DÍAS. Para ello es necesario crear una columna adicional para colocar una fórmula basada en el uso de funciones integradas que permita generar las categorías solicitadas.  “Wu W E GS, a IMECAF, SC Excel 2016 Avanzado GeneraJa etiqueta CARTERA VENCIDA en la celda H1 y en H2 coloque la siguiente fórmula: =SI(B2<FECHA(2016,3,29),"+ DE 200 DÍAS", SIB2<FECHA(2016,6,30), "+ DE 100 DÍAS' DIAS") (inglés) =IF(B2<DATE(2016,3,29),"+ DE 200 DÍAS” IF(B2<DATE(2016,6,30), "+ DE 100 DÍAS”, "- DE 100 DÍAS") - DE 100 Una vez generada la fórmula, aplique un autorelleno y el resultado debe ser igual al de la siguiente figura. GUERRES WE RAPECRE  IMECAF, SC. Excel 2016 Avanzado Observe el resultado. 2 Toral general 7.2. Campos calculados 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. Para generar un campo calculado el procedimiento es el siguiente: 1. Seleccione la tabla dinámica donde desea agregar un campo calculado 2. Clic en la ficha contextual Analizar / grupo Cálculos / botón Campos, Elementos y Conjuntos / opción Campo calculado 3. Escriba un nombre para el campo en Nombre, 4. En el cuadro de entrada Fórmula construya su fórmula (por 2 1 ejemplo: =IMPORTE * .16), para lo cual haga doble clic en el campo a utilizar dentro del cuadro Campos o selecciónelo y p haga clic en el botón Insertar campo 5. Clic en el botón Suma, esto generará el campo calculado y lo agregará al listado del cuadro Campos. Insertar campo calexiado 0 IMPORTE 0,16. Esc 4 IMEORTE Aceptar Cenar HE 1 6. Haga clic en Aceptar. 7.3. Configuración de campo de valor (Mostrar valores como) Puede cambiar el tipo de cálculo usado en campos de valor de tabla dinámica de modo que, en lugar 0 de su número, verá un porcentaje del total, un total acumulativo, la diferencia de otro valor, o su rango. [ | | | | | | Sy “W » ss .y IMECAF, SC Excel 2016 Avanzado al modifiear la estructura de la tabla dinámica, los cambios se verán reflejados automáticamente en el gráfico. Creación de una tabla dinámica con un análisis de Pareto A continuación, vamos a revisar cómo podemos realizar un análisis de Pareto con los datos de la Tabla dinámica el cual es muy ufilizado en los análisis comparativos. Tenemos la siguiente información. 3 E E CONSECUTIVO FECHA EAT ANA 2 GUSTAVO. , 1029 INSTALACIÓN CARLOS ss 05/01/2039 1 TACIÓN ANA 5 Defo1/2013 aiseño wes GUSTAVO e 07/01/2049 ZUESO CARLOS 7 ORÍA. ARA 8 D9/01/2019 INSTALACÓN GUSTAVO 3 10/01/202: CARLOS He ANA Ea! GUSTAVO E CARLOS 4 ANA sa GJISTAVO 15 CARLOS 16 ANA 7 GUSTAvO 15 19/01/2019 INSTALACIÓN CARLOS 9 2OIO1/2019 ¡ADLEMENTACIÓN ANA El JEJ01/2019 DISEÑO WES GUSTAVO El análisis de Pareto nos permite observar la contribución que realiza cada Vendedor (en este ejemplo) para el resultado tota! general. Para lograr esto debemos realizar algunas operaciones desde la Base de datos de origen de la Tabla dinámica. Lo primero es adicionar una columna donde debemos calcular el porcentaje que representan las ventas de cada registro. a) Enla celda F1 coloque la etiqueta % DE VENTA y en la celda F2 coloque ta fórmula: =E2/SUMA(SE$2:$E$26) b) Enseguida, aplique un autorelleno para todos los registros, el resultado debe ser el siguiente: 6 D + RES EA E a. GUSTAVO CEsLOS ANA Tavo,  IMECAF, SC ? Excel 2016 Avanzado C) Genere una tabla dinámica con la siguiente configuración . d) Configure la tabla dinámica resultante para que se vea similar a la de la siguiente figura PRODUCTO / IMPORTES % DE VENTAS [ASESORÍA z 715 CURSO 17.42% DISEÑO 128 22.58% E NTACIÓN 21.29% INSTALACIÓN: 26,005 Total general 100.00% e) Finalmente vamos a configurar la forma como se muestran estos valores, haga clic secundario en una celda de la columna de % VENTAS y seleccione la opción Total en  WI l 6 Www e e. a . la W aoaosb w IMECAF, SC Excel 2016 Avanzado f) Seleccione como campo base el campo PRODUCTO, haga clic en Aceptar. La tabla dinámica resultante ordénela por el campo % DE VENTAS de menor a mayor y observe el resultado final. 2 Ms PRODUCTO — > IMPORTES %DEVENTAS festrar veleres como í ? Xx ¡ ASESORÍA 116.000 ES | cueso 108,£00 Calcutas Totai en DISEÑO YES 240.000 IMPLEMENTACIÓN — 132.000 Lo INSTALACIÓN 124,000 Total general $20,000 g) Cerciórese de que esté en una celda de la tabla dinámica y haga otic en el comando Gráfico dinámico. En la ventana Insertar gráfico, seleccione el tipo de gráfico Combinado, marque la casilla Eje secundario de % DE VENTAS y haga clic en Aceptar. Es ano: so ombre de harorTe h) El resuitado en la gráfica de Pareto. IHPORTES de DEAEITAS. PREDUCTa > ES  IMECAF, SC Excel 2016 Avanzado Excel mostrará las fórmulas que contiene la hoja de cálculo en vez de los resultados. 1 201 AGOSTO. 15 | M N o | =CONTAR.SI.COMIUNTO(SH=IM5/N5 SUMARSI.CONIUNTO(SGS2. =SUMAR SLCONJUNTO($0$2: =SUMAR.51.CONIUNTO($6S2:5(=CONTAR.SLCONJUNTO(S! =SUMAR51.CONJENTO(S6: 9 =SUMARSI.CONJUNTOISG: 10 (SUMAR SLCONJUNTO(S6: 'ONTAR.S!.CONJUNTO(S: 11 =SUMAR.SI.CONIUNTO(SG$2:5[=CONTARSI.CONJUNTO(S [12 ESUMA(M5:M12) ESUMA(N3-N1) =PROMEDIOIOS:D12) NOTA: Para ocultar las fórmulas haga clic de nuevo en el botón Mostrar fórmulas para desactivarlo. 8.4. Comprobación de errores Comprobación de errores contiene varios comandos que le permitirán identificar y corregir errores, los cuales le explicamos a continuación, NOTA: El cuadro de diálogo Comprobación de errores se abre solo si la hoja de cálculo contiene un error marcado por Excel. Para abrir el cuadro de diálogo Comprobación de errores realice lo siguiente: ¿li Haga clic en la ficha Fórmulas / grupo Auditoría de fórmulas / botón Comprobación de errores. EX, Rastresr preced ser dependient Quita, flechas Auditoria de formule 2. Enel ejemplo existe un error en el nombre de la función SUMA, observe como en la ventana de Comprobación de errores se indica un nombre no válido O vatones Evade noma La fórmula contierateeto na seconocida, Omiirarroy Hodilical enta bara de rormutas Econ. arterias O Siguiente 51 WD a a d a . e IMECAF, SC Excel 2016 Avanzado 3. Puede editar la fórmula con el botón Modificar en la barra de fórmulas 8.5. Evaluar fórmula Para mostrar los pasos de un cálculo realice lo siguiente: 1. Seleccione la celda que contiene el cálculo que desea analizar paso a paso. 2. Haga clic en la ficha Fórmulas / grupo Auditoría de fórmulas / botón Evaluar fórmula. Ban 4 orecsdentes VÁ Audios det 3. Haga clic en el botón Evaluar que se encuentra dentro del cuadro de diálogo Evaluar fórmula. Excel muestra un cuadro similar al siguiente: alias Formula ES Para mostrar más reciente Maga 1213 expresion cubrayasa, haya dicen Evatuar. El cesultado vete en curia, 4. Haga clic en cualquiera de las siguientes opciones: a. Evaluar Para descubrir el orden de ejecución de los cálculos de una celda paso a paso. b. Paso a paso para entrar. Le sirve para visualizar en paneles inferiores los cálculos que se encuentran en otras celdas que están incluidas como argumentos precedentes de la celda evaluada, mostrando en colores las referencias que se detallaron en los paneles superiores. c. Paso a paso para salir. Le sirve para resolver los cálculos de los paneles generados por el botón Paso a paso para entrar, va obteniendo los resultados precedentes y al mismo tiempo cerrando los paneles que los contienen. d. Cerrar Cierra el cuadro de diálogo Evaluar fórmula. 8.6. Ventana de Inspección La ventana de inspección de Excel nos ayuda a supervisar los valores de determinadas celdas de manera que siempre tengamos a la vista su resultado aun cuando estemos trabajando en múltiples hojas. En la siguiente imagen podrá observar un reporte de ventas. Supongamos que desea poner especial atención a las ventas de diciembre de 2018.  IMECAF, SC : Excel 2016 Avanzado 1 K L E , 2016 2017 57 1,240.00 | 23,932,006 | 46,562,000 4 Pa 3 Febrero 2,301 23,268,000 | 4 Nano 27.590,000 [5 ao 28,530,000 (6 aya 3,742,000 | 31,372,000 7 ¿ano 10,290,000 | 32,190,000 8 sulo 12,524,600 | 35,154,000 9 Agosto 14,445,000 | 37,076.00 10 ¡Seotiemare 37,710,000 Octubre 3 41,370,000 ¡Diciembre 22.610.000 | 44,640,000 Si desea monitorear el valor de la celda D13 debe hacer lo siguiente. En la ficha Fórmulas, dentro del grupo Auditoría de fórmulas, debe hacer clic en el botón Ventana de inspección. Se mostrará la ventana de inspección y debo hacer clic en el botón Agregar inspección el cual le permitirá seleccionar las celdas que deseo inspeccionar: Hoja Homar Cea a sema * a Al pulsar el botón Agregar se insertará un nuevo elemento en la lista de celdas inspeccionadas, vea ejemplo. Mover la ventana de inspección La ventana de inspección estará visible todo el tiempo sobre las hojas de Excel, pero en ocasiones eso puede impedir que tengamos una visibilidad completa de todas las celdas. En este caso podemos mover la ventana de inspección por debajo de las hojas o a la derecha. Solamente arrastre la barra de título de la ventana de inspección hacia la barra de estado y Excel la ajustará a ta nueva posición. De igual manera puedes colocar la ventana de inspección por arriba de la barra de fórmulas. 63 |  - a a as nn Wa asa IMECAF, SC Excel 2016 Avanzado O $ A o ida iS Ad e e lala Libro compartida para que pueda cargar el. - archivo a OneDiive y trabajar con oltás usuarios EAN 3 7. En la barra de título del archivo aparecerá Se ha compartido. El archivo ahora puede ser modificado por varias personas al mismo tiempo. 9.2. Proteger y compartir un Hibro Otra de las opciones para compartir un libro es colocar una contraseña además de permitir que varios usuarios lo modifiquen al mismo tiempo, el procedimiento es el siguiente: 1. Creeunlibro o abra un libro existente. Después, colóquelo en una ubicación de red, Porejemplo, colóquelo en una ubicación como inombre_servidorinombre_carpeta. No coloque el archivo en OneDrive o SharePoint. 2. Haga clic en el botón Proteger y compartir Rápido. 3. En la ventana Proteger libro compartido marque la casilla Compartir con control de cambios. Se recomienda establecer una contraseña de la Barra de herramientas de Acceso Confirmar contisceña bl Juelea a estrdrda contraseña paro peace seña fopcionall 4. Excel mostrará una notificación para guardar el archivo, haga clic en Aceptar. 5. En la barra de título del archivo aparecerá Se ha compartido. El archivo ahora puede ser modificado por varias personas al mismo tiempo 9.3. Permitir a usuarios modificar rangos De forma predeterminada, proteger una hoja de cálculo bloqueatodas las celdas de modo que ninguna de ellas es editable. Para habilitar algunas celdas edición, mientras que otras celdas bloqueadas, es posibie desbloquear todas las celdas. Puede bloquear solo celdas y rangos especificos antes de proteger la hoja de cálculo y, opcionalmente, habilite específicos a los usuarios editar solo en intervalos específicos de una hoja protegida.  IMECAF, SC Excel 2016 Avanzado Proteger rangos 1. Haga clic en la ficha Revisar / grupo Proteger / botón Permitir editar rangos. 2. Haga clic en el botón Nuevo. Demi a acuarios rredificarcangos 1 Rango: decplogueadas avcrsrte contraseña cuarta sa hoj esta prategs Tio Tuiraseondaree a, Ll osgar ia intamnarón sobre eme: nus teo Pateger ncja Breatar O Cncea Zola Escriba un título para el rango en la opción Título. Seleccione un rango de celdas en la opción Correspondiente a las celdas. Escriba una contraseña en Contraseña del rango. Haga clic en Aceptar (Excel le pedirá volver a escribir la contraseña). Haga clic en Aceptar. Nom Tuto: Confirmar certcaseño Rangel “guta a esca cONMrAseÑa para prose koi Contraseña de jango recae sa 3 sel Peris, Cancelar data Camcaar ] | | | 1 | | | 1 | | | 8. Repita los pasos 2 al 7 las veces necesarias. 9. Haga clic en el botón Proteger hoja. NA Bangos desbloquezdos med ida. curada a hoja satá apura ada. | y aa Rangol PACA, Enmnar Espesiicar quien puede modificar sl rango sin cenaseóa: Permisos, [O Segaria información sobre permisos an un muero Séno. Proteger hoja. Acaptar cancelar Aplica  yy sn ..'./ - a = IMECAF, SC Excel 2016 Avanzado 10. Proteja la hoja de preferencia con una contraseña Cenfismar centraceta PAN anna arrisica contraseña para proceden. [captar | cancelar Desproteger rangos 1. Desproteja la hoja con un clic en ta ficha Revisar / grupo Proteger / botón Desproteger hoja 2. Haga clic en la ficha Revisar / grupo Proteger / botón Permitir editar rangos 3. Seleccione un título del cuadro principal y haga clic en Eliminar (las veces necesarias). Permitir aa ies medificer renges ÉS Pangos desbloqueados mediante contraseña cuando la protegida. Tula Hueso. O A 1 Eliminac | | ecifizar quier: puede modificar el sango sin contraseña: |El pagara información sebes misns an un nuevo bro Frateges naaa. ] | acemtar [Conc 4. Clic en Aceptar. 9.4. Establecer permisos de acceso a un libro En Excel, puede usar una contraseña para evitar que otras personas abran o modifiquen sus documentos, libros. Después podrá quitar las contraseñas fácilmente para desproteger un libro si ya no necesita que sea de solo lectura. Además, puede usar la protección de documentos para restringir los tipos de cambios que los revisores pueden aplicar. Establecer permisos. En una hoja de cálculo abierta, haga clic en Archivo / Información / Proteger libro. Verá las opciones siguientes. Jj 68  IMECAF, SC Excel 2016 Avanzado 9.6. Control de cambios Puede usar el control de cambios para registrar los detalles acerca de los cambios en un libro cada vez que lo guarda. Este historial de cambios puede ayudarle a identificar los cambios realizados en los datos de un libro y puede aceptar o rechazar estos cambios. El control de cambios es especialmente útil cuando varios usuarios editan un libro. También resulta útil cuando envía un libro a los revisores para que lo comenten y luego desea combinar los comentarios que recibió con una copia deese libro, incorporando los cambios y comentarios que desea conservar. 9.6.1. Resaltar cambios a medida que trabaja Cuando resalta los cambios a medida que trabaja, Excel marca las revisiones (como los cambios, las inserciones y las eliminaciones) con un color de resaltado. 1. Haga clic en el botón Control de cambios (heredado) 1 en la Barra de Herramientas de Acceso Rápido y seleccione Resaltar cambios 2. En el cuadro de diálogo Resaltar cambio, active la casilla Efectuar contro] de cambios al modificar. Esto también comparte el libro | Resaltar cambies, FT og Rezaitar cambias bro] [El cuándo: Todos [auier: ados [326nde: > [A Resaltar car! Al activar esta casilla, compartirá el libro y se resaltarán los cambios que haga o que otros usuarios realicen. 3. En Resaltar cambios, active la casilla Cuándo y, a continuación, en la lista haga clic en la opción que desea. 4. Para especificar los usuarios para los que desea resaltar cambios, active la casilla Quién y, a continuación, en la lista Dónde, haga clic en la opción que desee. 5. Para especificar el área de la hoja de cálculo donde desea resaltar los cambios, active la casilla Dónde y, a continuación, en el cuadro Dónde, escriba la referencia de celda del intervalo de la hoja de cálculo. 7 Wa > le e aaa IMECAF, SC Excel 2016 Avanzado amics EA ol de cambios al opdíficar. Eo tambien comparte el libro. | ar pios (A cuándo: Tadod Dgsien: Tas EJ bones: snsesos5 » ar cambios en pantalla cancer | 6. Asegúrese de que la casilla Resaltar cambios en pantalla se encuentre activada. 7. Haga clic en Aceptar. Si se solicita, guarde el libro. 8. Enla hoja de cálculo, realice los cambios que desea. Nota Algunos cambios, como el formato, no se controlan y, por lo tanto, no se marcan con un color de resaltado. Detener el resaltado de cambios Cuando ya no desee resaltar los cambios, puede desactivar el resaltado de cambios. 1. Haga clic en el botón Control de cambios (heredado) a en la Barra de Herramientas de Acceso Rápido y seleccione Resaltar cambios. 2. En el cuadro de diálogo Resaltar cambios, desactive la casilla Efectuar control de cambios al modificar, Esto también comparte el libro. 9.6.2. Aceptar y rechazar cambios 1. Haga clic en el botón Control de cambios (heredado) a en la Barra de Herramientas de Acceso Rápido y seleccione Aceptar o rechazar cambios. 2. Si se le consulta si desea guardar el libro, haga clic en Aceptar 3. En el cuadro de diálogo Seleccionar cambios para aceptar o rechazar, haga lo siguiente ? x Seleccionar cambios para aceptar ¿Du cambios? b Cansetar  IMECAF, SC Excel 2016 Avanzado + Para aceptar o rechazar los cambios que se realizan después de una fecha determinada, active la casilla Cuándo, haga clic en Desde la fecha en la lista Cuándo y luego escriba la primera fecha para la que desea ver los cambios. + Para aceptar o rechazar los cambios realizados por otro usuario, active la casilla Quén y, en la lista haga clic en el usuario cuyos cambios desea revisar. + Para aceptar o rechazar los cambios realizados por todos los usuarios, desactive la casilla Quén. + Para aceptar y rechazar los cambios realizados en un área específica, active la casilia Cuándo y escriba la referencia de celda del intervalo de la hoja de cálculo. + Para aceptar o rechazar los cambios en todo el libro, desactive la casilla Dónde. 4. Haga clic en Aceptar y revise ta información sobre los cambios en el cuadro de diálogo Aceptar o Rechazar cambios. Sugerencia La información incluye otros cambios afectados por la acción que desee llevar a cabo para un cambio. Puede desplazarse para ver toda la información según sea necesario. 5. Para aceptar o rechazar cada cambio, haga clic en Aceptar o Rechazar Sugerencia La hoja de cálculo de historial registra un rechazo mostrando "Deshacer" o “Resultado de la acción rechazada" en la columna Tipo de Acción 6. Si se le pide que seleccione un valor para una celda, haga clic en el valor que desea y luego en Aceptar. Notas + Debe aceptar o rechazar un cambio antes de avanzar al siguiente. + Puede aceptar o rechazar todos los cambios restantes simultáneamente al hacer clic en Aceptar todo o Rechazar todo. Aceptar o Cambio 1 de 12 aplicado a este documento: armando, 06:01/2019 Cambiada Celda M2 de «espacio vacios a | = SUMAR, Si. CONJUNTO:SFS2:5F51001, $692:3631001,M51, SHS2:GH$1001,13) Rechazár — : Aceptartodos | Pachazartados Cerrar A E a aaa ss ala IMECAF, SC Excel 2016 Avanzado 10.2. Desde Texto Cree una conexión a un archivo de texto para trabajar siempre con sus últimos datos. El Asistente para importar texto le ayuda a garantizar que todos los datos acaban en las columnas correctas. 1. Enlaficha Datos / grupo Obtener y transformar datos, haga clic en Obtener datos 2. Seleccione Desde un archivo / Desde texto/CSV 3. Seleccione el archivo de texto y haga clic en Importar. 4. A continuación, haga clic en Cargar 5. Observe que la información importada aparece como una tabla que puede actualizar en cualquier momento con el comando Actualizar (Alt + FS). 3 8 3 D E E á INICACIONES. STRUCTURA. COARROS E FINANCIAMIENTO 2AUSCO so ACOWDICIONAMIENTO FÍSICO PUEBLA 2 Po o  IMECAF, SC Excel 2016 Avanzado 10.3. Desde Web Cree una conexión a una página web para usar datos de sus tablas en el libro. 1. Enla ficha Datos / grupo Obtener y transformar datos, haga clic en Obtener datos 2. Seleccione Desde otras fuentes / Desde la web Me A Dese ateos fuentes orar ge Destelawet 3. En la ventana De web en Dirección URL teclee el acceso a la página web (ej. https://es.finance.yahoo.com/quote/%5EIBEX/components?!tr=1) y haga clic en Aceptar | | | | il | 3 l 4. Seleccione la tabla que necesite vincular y haga clic en importar. 5. A continuación, haga clic en Cargar IMECAF, SC Excel 2016 Avanzado : 6. Observe que la información importada aparece como una tabla que puede actualizar en - cualquier momento con el comando Actualizar (Alt + F5). y E e D E z a j e ea . AT > 021 722,962 . 2538 Res rica Corcoración, S.A 1953 o sa md -0.23 807.903 A Endesa, Sociedad Anonima 2020 e 0.35 992,239 => Acciona, $4, 7662 23 u 706 5 0.74 15.186.132 L. 2253 E 0.31 851,230 E 2309 25 Ñ 3 ¿MALL LN Iroperties SOCIE, S.A. 3080 2 o 10.4. Otras conexiones o Una de las conexiones más recurrentes además de las revisadas en los puntos anteriores es a una A base de datos SQL server por lo que es el tipo de conexión que revisaremos en este punto o 1. Enla ficha Datos / grupo Obtener y transformar datos, haga clic en Obtener datos " 2. Seleccione Desde una base de datos / Desde una base de datos SQL Server. o o o [O tasa , o | os pe de dates de SAN Server o , á 5 ve eS o o u E o 3. Al hacerlo, se iniciará el Asistente para la conexión de datos. En el cuadro Nombre del E servidor, escriba el nombre del equipo SQL Server al que desee conectarse. y > - — - | l | z | a | = | . 1 | . . a  IMECAF, SC Excet 2016 Avanzado 2. Haga clic en el botón Aceptar y en la ventana que aparece seleccione el archivo de Access Seleccionar base dle datos x Hambre de base de datos — Ejrectosios: Basecco a . a Cancetar Mm. es S l 15 Users id | (E Ermanda sa | | pa Pen Solo fetura | vrs - l AE Exclusiva Unidades: Ecos El Pad 3. Haga clic en Aceptar y en la ventana Elegir columnas seleccione la tabla o consulta para trabajar. sistema pers comu - Elegir selamnes x Atar en rutaman slecconada ls 4. Avance a través del asistente y en la última ventana seleccione Ver datos o modificar consulta en Microsoft Query y haga clic en Finatizar Zsrtente car Guardar ccmsulta, 5. Ya en Microsoft Query, puede establecer criterios de búsqueda mediante el botón Ocultar o mostrar los criterios Observe la figura siguiente.  a Do. mn As mn) DN 2 Bm nas sa » IMECAF, SC Excel 2016 Avanzado Ear ceo Acce atra 3 | Ti Ec Cuente sE ioCrAL, Campos docto: TUENTE Í t Van MEAN AYICIO 1 SEDE me ET CECICADO “ERACAL Sl HUEVO LE Eto ÓN A Cua naa, JN ECO DEM ERmeno, cea 2000! % 01500000 =% sa, Amatozz00 000 3% usa 20131 50ngeso daria FS pus ir m120080d0 EF DEDICADO Eto caco odo Pza Escolta dovo o os o ofacón cnsaada sun ] En la ficha Archivo en la opción Devolver datos a Microsoft Excel puede enviar el resultado de la búsqueda a la hoja de cálculo de Excel. 10.6. Actualización de información de datos externos Si su libro de Excel tiene una conexión a un orígen de datos externo, puede asegurarse de que muestra la información más actualizada. Para ello presione la secuencia de teclas Alt + F5 10.7. Vínculos entre aplicaciones Puede insertar un objeto, como una diapositiva de PowerPoint o un documento de Word directamente en una hoja de cálculo, lo que posibilita mostrar o compartir. Puede mostrar el contenido del objeto o solo mostrarlo como un icono. En ambos casos, el objeto se almacena en el archivo Excel. Una vez incrustado, puede abrirlo directamente desde Excel mediante el programa con el que se creó el objeto. 10.7.1. Incrustar un objeto en una hoja de cálculo Un objeto es un archivo de una aplicación externa con respecto a Excel. El procedimiento para incrustar un objeto es el siguiente: 1. Haga clic en la celda de la hoja de cálculo donde desee insertar el objeto. 2. Seleccione en la ficha Insertar / grupo Texto / botón Objeto  IMECAF, SC Excel 2016 Avanzado 3. Haga clic en la ficha Crear de un archivo. 4. Haga clic en Examinar y seleccione el archivo que desea insertar. 5. Si desea insertar un icono en la hoja de cálculo en lugar de mostrar el contenido del archivo, active la casilla de verificación Mostrar como icono. Si no selecciona las casillas de verificación, Excel muestra la primera página del archivo. En ambos casos, se abre el archivo completo hacer doble clic. Haga clic en Aceptar. Nota: Después de agregar el icono o el archivo, puede arrastrar y colóquela en cualquier lugar de la hoja de cálculo. También puede cambiar el icono o el archivo mediante el uso de los controladores de tamaño. Cambios icono, 10.7.2. Vincular un objeto en Excel Si desea agregar un vínculo al objeto, puede hacerlo si el libro y el objeto que desea agregar se almacenan en un sitio de SharePoint, una unidad de red compartida o una ubicación similar y la ubicación de los archivos permanecerán igual. Esto es útil si en el objeto vinculado se realizan cambios, porque el vínculo siempre abre el documento más actualizado. Nota: Si mueve el archivo vinculado a otra ubicación, el vínculo ya no funciona. Haga clic dentro de la celda de la hoja de cálculo donde desee insertar el objeto. 1. Haga clic en la celda de la hoja de cálculo donde desee insertar el objeto. 2. Seleccione en la ficha Insertar / grupo Texto / botón Objeto = _ 33 )-Y mn Ba Bm) 2) MAA AAA y hb IMECAF, SC Excel 2016 Avanzado TEMA. 11. INTRODUCCIÓN A LAS MACROS CON VISUAL BASIC A A AAA A AA Objetivos de la unidad Una vez que maneja y ha practicado los temas avanzados de Excel, es momento de comenzar a automatizar procedimientos mediante el uso de macros. Objetivos particulares. » Identificar los tipos de macros que se pueden crear en Excel e Crear macros grabadas + Crear una 0 grupo de instrucciones programadas bajo el entorno VBA (Visual Basic para Aplicaciones), cuya tarea principal es la automatización de tareas repetitivas y la resolución de cálculos complejos. El lenguaje VBA, es un lenguaje de programación basado en el visual Basic, enfocado a la realización de programas sobre las herramientas Excel, Access, Word, etc., mediante macros en VBA podemos crear nuevas funciones para nuestras hojas Excel, personalizar estilos y formatos, crear programas para la resolución de cáiculos complejos, automatizar tareas, etc. 6.1. Macros grabadas vs macros codificadas Macros grabadas Puedes crear una macro utilizando el lenguaje de programación VBA, pero el método más sencillo es utilizar la grabadora de macros que guardará todos los pasos realizados para ejecutarlos posteriormente. La grabadora de macros almacena cada acción que se realiza en Excel, por eso es conveniente planear con antelación los pasos a seguir de manera que no se realicen acciones innecesarias mientras se realiza la grabación. Para utilizar la grabadora de macros debes ira la ficha Programador y seleccionar el comando Grabar macro. Macros codificadas Las macros codificadas, se les llama así porque se generan directamente en un módulo tecleando directamente los comandos en un lenguaje de programación ltamado Visual Basic para Aplicaciones VBA, o bien, copiando el código a partir de una macro grabada.  IMECAF, 50 Excel 2016 Avanzado La programación en VBA puede ser un tanto complicada para la mayoría de los usuarios de Excel, sin embargo, una vez que se comprenden los principios básicos de programación en VBA se comenzarán a crear soluciones robustas y efectivas. El primer concepto importante para entender es que cada elemento de Excel es representado en VBA como un objeto. Por ejemplo, existe el objeto Workbook que representa a un libro de Excel. También existe el objeto Sheet que representa una hoja y el objeto Chart para un gráfico. El segundo concepto importante para entender es que cada uno de estos objetos tiene propiedades y métodos. Una propiedad es una característica de un objeto y un método es un servicio que se proporciona para el objeto. Un tipo de macro especifico no es más importante que otro, más bien, se complementan para crear macros eficientes y robustas. 6.2. Introducción al Editor de Visual Basic Cuando comenzamos a trabajar con macros, es imprescindible conocer el entorno de trabajo del Editor de Visual Basic, para ello puede teclear la secuencia de teclas Alt + F11. Project Explorer enc Al entrar al Editor observará dos ventanas al lado izquierdo de la pantalla. CC IMECAF, SC Excel 2016 Avanzado Project Explorer La ventana de Explorador de proyectos permitirá controlar todos aquellos elementos y archivos que componen nuestra aplicación. Los archivos que forman el proyecto pueden ser formularios, módulos, módulos de clase, controles de usuario, etc. Properties Window Cuando creamos un objeto, un circulo, por ejemplo, indicamos determinadas coordenadas para su centro, luego, según el método seleccionado, damos un valor para su radio o su diámetro. Finalmente podemos cambiar su grosor de línea y su color, entre otras propiedades. A su vez, un grupo de objetos como los mencionados en el capítulo anterior, pueden tener en común determinadas características, como el grosor de línea, aunque se trate de objetos de distinto tipo. Todo este conjunto de propiedades de objetos individuales o en grupo puede verse en la paleta “Propiedades”, que muestra, precisamente, todas las características inherentes al objeto u objetos seleccionados. Aunque no sólo nos limitamos a consultar las propiedades del objeto, también podemos modificarlas. Estos cambios se reflejarán de inmediato en pantalla, con to que esta ventana se convertiría entonces en un método alternativo para editar los objetos. Para activar la ventana Properties Window presione la tecla de función F4 o a través del menú View / Properties Window 6.3. Proyectos VBA Un proyecto de Visual Basic para Aplicaciones (VBA), es el libro de trabajo de Excel almacenado como Libro de Excel habilitado para macros. En el momento que almacenamos el archivo se convierte en un proyecto VBA que contiene elementos como son las hojas de cálculo, módulos, Userforms y módulos de clase. Sin duda alguna el módulo es el elemento más importante en el trabajo con macros ya que en él reside el código de una macro el cual “puede ser editado de manera directa Un módulo es un contenedor del código de una macro que puede ser generado de manera automática por Excel al usar la grabadora de macros o puede ser insertado de manera manual en el Editor de Visual Basic. Un módulo es dónde se teclea el código en VBA. Un proyecto de VBA es un conjunto de módulos (o uno sólo) dónde se escriben las diferentes macros y funciones de VBA. Los módulos nos permiten organizar el código de VBA en diferentes archivos dentro de VBA de manera rápida, fácil e intuitiva. Para acceder a un módulo de VBA primero habrá que acceder al proyecto y después elegir el módulo que se quiere editar. 88