Unidad 3 Disefo de modelos con hoja de célculo Introduccion iver problemas de dives epeciiades yg coms fsone op cron me son on since ene Fe ee Sohn a tls problemas, de shite notin dee i Sre7as que permitan un mejor dominio de esta aplicacion, 3.1 Operaciones con Tangos ConcePTo DE MODELO 4 mines sencillos,entendemos por modelo td Tel El melo ne ee sera aden dun fendmeno ost e ele iene qu ser de natraleramatemice, pero en erent ds ec cee sti, acon sna La velocidad de un objeto est en funcién ccucin puede ser: \elosiad = distancia tempo ltotal de las ventas de un producto esté en funcié ds vend. La ecuacién puede ser ecio * unidades ‘dela distancia recortida y del tiempo empleado. La Jn del precio de venta y del nimero de unida- Yeats E salaro de un empleado depende del nimero de das trabajados y del pago por dia. La cua cin puede sr: Saario = das * pago Crear un modelo en la hoja de célculo requere: © Comprender el problema para identiicar los datos de entrada y qué sada se require ‘¢Enconrar la relacién ene a entrada y la sla para estblecer una func, ‘© Deteminar una ecuaci6o 0 regla mater ‘© Trasladar la ecuacino rep la hoja de eeu, Ranco Lamanipulacin de una celda es relativamente simple y sin problemas, pero en acasiones es neee= sario utilizar una regiGn de Ia hoja de cdlculo. En tal caso es preciso conocer y apicar el concepto de ang para fuclitar cierto tipo de operaciones. ‘Rango es una regi6n rectangular de celdes contiguas en una hoja de elculo. En si forma mas simple un rango puede contener una sola cea. Para hacer referencia aun rango «es necesarioespecificar los dos puntos anclas (direcciones de las celdas esquina superior izquiea y csquna inferior derecha del rango). Por ejemplo el rango AI:B3, comprende a las celdas Al, A2, A3, BI, BZ, BS, La expresién AL:A\L es vilida como expresin de un rango y equivale a lacelda Al. Aslgnar nombre a un rango Es esta operaci6n la de mayor interés para la generaci6n de modelos con Ia hoja de cdleulo, debido que su uso falta el entender 0 mantener las expresiones o la formulas, 133Aplicacin de | A ceotimacta se reentan bs m6 el rango se encuentra prevament se ot «efones fica Formals, rp er ei Cn CP, de io Nam mone el Ace reser OPEC ating, fe Sa Método 1 ides oi. en Nombre: 0 SS celda. Taam crc de sed ()01 Ct ive 2 Bin anes eo ce de baa 0g, bac sew hare 5. No se permite espa par SPH G om Wao! ‘recs ce eros a etl 7 gy? 5 El mimo cartes 25. oe | liminar et nombre de wn F206? “ at } ean a anager nse Cae SAS is oat Pc Aan ete i i del | eles nana arene eee tte en Cem oy bes dees range 2 gotten arctan coe rete isa ee sD etme La edd ans A ae aa ig wore | a bapa ae ee 2 Se Fe tl | negli rr cance ee oe a ner ee Ei ro as cies a el J ree lence cin cn enn eg Cine oni piety ames dr eens nee eA i Ft | eh dle fn am ens cn ‘nen a anc a de ams ee conn ieee i forte se riontolet | = SUMAGVe de st) oor \ em Tal dela dean {pd termi enn sep et | sm ee ene eee pp adel?) yan!) = SUMA‘Sucuna1) 8 nr com) (20 esas cro) (y= de vena) ic hte pies cel ce /=SUMA(Soeu I)Unidad 3 Diselio de modelos con hoja de céteulo Ejemplos emplos 3.1 y 32 se anal gn os ee an las aplicaciones del concepto no satura Practica claborande el modelo ea ia hoje deedieata, om? & rng lengusie Evento 3.1 Dasa sie na clo ue epee as vend conto o deuna empresa. tas decontado y a exédito de tes sucursal Ventasa cio Sera [th a Suwa? 7a = canal Suewsal3 | 245 360 =| [s Determinar el “Total de ventas” en cada sucursaly la 00 suma de las ventas de contado y a eréito elas tres, us para ello nombres defnidos de rango, 5 Procedimiento 1. Crear un libro nev. Introduce model plameado lo planteado y gurdar el documento como Ejem- 103_O1 xlsx en la trayectoria, Evidencias\Bjemplos. Realizar el procedimiento para estable- cer en Propiedades del Archivo: Aur: [Eset nombre), Tit: Ejemplo 3.1, Asi ‘so de nombres definios de rang, Hacer lic en Cerrar el panel de infomacda del docu- rent. 2. Mareare ango A2:C2en la Cinta de opciones, cha Férmuls, grupo Nomis defides, psn Crear dese la selec, ena ventana de dlogo Crear nombres part del seloe. chén ace ec en la open Cora iguierday Aceon Repetrel procedimiento para el rang A3:C3 y después para ACA, 3, Marer ef rango BI:B4 en la Cinta de opciones, cha Fé, grupo Nombes defnidos, opin Crear dese la selec, ena ventana dedslogo Crear nombres a par del selee: cidn hace elie en la opin Fla superior y Acptar Repetirel procedimiento paral rang C1:CAy después para DDS 4, Hacer clic en el control Cuadro de nombres obsrar os nombres els rangos denis en tos pasos 2 3. 5 Escibir ls siguientsexpresines en as celdas conespndientes Celia D2 =SUMA(svcual_1) Celis DS =SUMA(sucunal 2) Celda Dé =SUMA(sucursal 3) Celda B6 =SUMA\venas de coma) Celda C6 =SUMA(venta_a_crio) Celda D6 =SUMA\toade_vents) 6, Bdiarel contenido dea siguientes ects yobservarelcompontamiento de model BI Ventas Conta CI Venus Créito {Se moifcaron ls resultados de ls fouls de as columns conespondints? 135‘ informacion i tecnologias de | 6 Aplicacién de las 21 Cambie tos valores murda obser comporaminto de model 8, Guardar el documento y salir de Excel. Evempto 3.2 : 7 ! Dada le sgucnehoja decleuo que representa cl comportamsento de proguccion de una emer, | 1 Mes, Inventario inicial | ‘Demanda Slice | 2 | Bree 1500 300 4 3 Febrero > corane| | aa one ass 3 5 tsi jes 800 a a = Determinar el “Inventario Final” de cada mes, usando nombres definidos de rango. 1. Crear un libro nuevo, ntroducire! modelo planteado y guardarlo con el_ nombre -Ejemplo3_02.xlsx en la trayectoria Evidencias\Ejemplos. Realizar el procedimiento para establecer en Propiedades del Archivo: Autor: (Escribir tu nombre), Titulo: Ejemplo 3.2, Asunto: Uso de nombres defnidos de rango. Hacer clic en Cerrar el panel de informacién del documento, 2. Asignar los nombres definidos a los rangos correspondientes: Marcar el rango BI cjecutar la secuencia Cinta de Opciones, fcha Férmula, grupo Nombres definidos, opin Crear desde la seleceién, en Ia ventana de didlogo seleccionar Fila superior y Aceptar, Repetir el procedimiento para el rango Produecién C1:C5, Demanda DIDS. 3, Escrbir las siguientes expresiones en las celdas correspondientes: EX Inventario final‘Unidad 3 Diseno de modelos con hoje de clculo AT Totales E2 =Loventario_inicial + Produecién-Demanda 7. Barbas siguientes expresiones ens CT =SUMA(Produccién) ee D7 =SUMA(emands) 8, Modifica el contenido de as celdas que contin i Shree conporanics ae cen ovals de Paacin y Demand y H 9. Guardar el libro de trabajo y salir de Excel. ActivipADES Desarrola cada uno de los siguientes ejercicios y guard los archivos como evidencia de aprendizaje. Activipap 3.1 Objetivo Usar nombres definidos de rango para erear un modelo que caleule y como funcién de x, Datos La funcidn a utilizar en este ejercicio es y = x42 + 26x +4.slogias de informacion Aplicacién de las tecnol Procedimiento Te coer i a ta Sele meal pleted esto son Cr ena Actas (ohn ncaa de At nes ort oxen rere odin yrs esablces en Propleaades del Aecives Anton: Ha eco ia: jel 1, Amuno: Uso de nombres dchnime de rng Fae erie on feen 6. Hacer clic en Cerrar el panel de iaformaciGa el documento. 2, Marca el rango AI:AIO, eecuta la [Nombres definidos, opcién Crear d nar Fila superior y Aeeptar. Se crea el rango + 3, Escribe en lacelda B2 la formula = x°2 + 2¢x + 4 4, Copia el contenido dela celda B2 en el rango B3:B10. to del modelo caleulando manualmente el resultado para secuencia Cinta de Opciones fcha Férmula, grupo rade ta selecein, en fa ventana de didlogo seleccio- SS. Verifiew el correctofuncionamien ‘alguno de ls valores de x Seer ene Rue Ba SiS a8) | ea aS lll: IiActivipan 3,2 Objetivo Usar nombres definid : de distancia recorri ida Por un objeto coy eMBUaje Datos 2 imiento ua Par : former Un LaexpresiGn de la distancia es mene Procedimiento 1. Crea un libro nuevo, guarda el documento como Di 1 , umento como Distancia xsx en a _ GietActividades, Esiablec la propiedades del achive Autor: [Eserir nombre Te fulo: Actividad 3.2, Asunto: Uso de nombres definido de rango, Comentarios! Modelo para calcul a distancia, clic en Cerrar el panel de informactin del documento : nombres Vo y Aceleracién alas celdas BI y B2,respectivamente, Marea el rango fecuta la secuencia Cinta de Opciones, ficha Férmula, grupo Nombres defin- dos, opcién Crear desde la seleccién, en Ia ventana de didlogo seleciona Columna jaquierda y Aceptar, crea el nombre Vo, Marca el rango A2:B2, repite procedimiento nerior para asignar el nombre definido Aceleraci6n. Marea el rango D':D/2, repie al procedimiento, considera fila arriba para crear el nombre definido Tiempo. scribe en la celda EZ = Vo * Tiempo + 1/2 ¢ Aeceracién * Tiempo"? Escribe en las celdas correspondientes los siguientes datos: Vo = 30, Accleracién = 5. contenido de la celda B2 en el rango E3:E12 ‘signa AL:BI, ej Copia etJoidad y acelerac6n y observ el funcionamieno del nog, fica los datos de vel 6. Modifica los datos aes 7. Guarda el libro de trabajo y st Activipap 3.3 Objetivo Crear un modelo relacion: bres definidos de rango en lengu ado con algn tema de tu especialidad en donde e utilicen nop, aje natural. Procedimiento 1. Presentael documento que expligue el modelo y Tos pasos para elaboraro, 2. Presents el ibro de trabajo correspondiente, 3.2 Funciones de busqueda y de referencia Las funciones de bisqueda permiten, como su nombre indica, la bisqueda de vallores dentro det, rango de la hoja de célculo. Fanciones BUSCARV y BUSCARH La biisqueda puede ser realizada vertical u horizontalmente mediante las funciones BUSCARV y BUSCAR. Sintaxis BUSCARV(valor_buscado; matriz_buscar_en; idicador_columnas; [ordenado]) BUSCARH(valor_buscado; matriz_buscar_en; indicador _ fila; [ordenado]) En donde:mw Valor ex SS yee rsisn he se desea — |, RUSCARY) cna pie fi BUSCAR : Eleangoen donde te desea scar] so nimero de columna ode la donde aly tO column ode fila donde la ania va a ees un = A = asi Teaeto opciones VERDADERO excoenta la colnclencia seat a oma oa sein ann, 5c PALSO, serexata snes ices print opal “ereassmyinpat Sire ma sands etn temo de wer AS $i se require loalizacién aproximads, el parmetto debe de ser VERDAI nitirse : 0 ede er V ee y eae dato. En xe Simo cats repeal valor pande peeve to ean ‘En los primeros eercicios emplearemos una erase ‘bisqueda exacts por lo que el parimetro“ordena- Ejemplos Los jemplos 3.3 y 3.4 muestran la aplicacin dels funcones de ferencia. Analiza ‘Sisano de ellos y produce el modelo enlahoja de ealon hess oe etre. Evewrto 3.3 1, Crearun ito nuevo Invodueircada no dls modelos planteado gurdro cone bre Bumped xe la tayecia Eider emperor pects pt establecer en Propiedades del Archivo: Autor: (Escribir tu nombre, Titulo: Ejemplo 3.3, | Asunto: Uso de la funcién de bisqueda, hacer clic en Cerrar el panel de informacién del documento C1002 C1003 3 C1004 6 ‘C1005 7 1006 8 C1007 + C1008 10 1009 “Oscar 0 C1010 a 4 Selecionar el rangoAL:CI y hacer eicenel Cuadro de nombres escribir Clients, para asignarel nombre al rango. 4. Cambiar el nombre ala Hojal por Clientes. Para ello, hacer doble cic en el nombre de Ia Hojal y escribir Clientes. Guardar et libro de trabajo ax az 5. Crear modelo Productos pra almacenar wna isa de productos. En Hoj2sinrovygy, siguiente: Precio Regulador 150 250 | | | Extractor i Mesa 400) i] Protector 280 | ‘Limpara 25 Plums 200 Deseripeisn | hacer clic en el Cuadro de nombres para asignar el nome 6, Seleccionar el rango Al: Products. +7. Cambiar de nombre ala Hoja2 por Productos. Para ello, hacer doble clic en el nombre del, | Hoja2 y escribir Productos. Guarda libro de trabajo.modelo BUSA que permita real 7 crear et de 10s MOdelOS anteriores a ro introduc 10 siguiente: a Hoja * ot Fang0 B1:B3. Para eto, py, so, 880% Necuencia Cinta de opciones, ello, marca ala Meade In seleccin, en la ventana at T™ crea y Aceptar. Verificar el ing ro de nombTES Ta come gs Desoento- iar de nombre a 1a Hoje3 por Buscar, ir las siguentesf6rmulas en las celdas corre Pondienes: 52 =BUSCARV(ClaveCliente Clientes, 2FALS0) oa 53 =BUSCARV(ClaveCliene Clientes3FAL $0) ‘nerar que aparecen el nombre de cliente cua #8 Sento correspondiente "8 clave C1009 y el porcentaje de dese gala mis hoja BUSCA S8regar el siguiente maxlo an STN pa Nome ABD ron et Seer ede do alate cadre monn Bl ela Cina een ‘ y com 2 6 Cantidad | Clave Producto facies? P22 ta pe} 9 2 p201 10 a 204 u 4 7206 2 Eltotal a ‘Asgnar el nombre a 10s rangos: Cantidad a C6:C11, Clave Product 18 GGEILy Total a POF, con el siguiente prcedinienseecions emo Cinta de opciones, cha Formulas, grupo Nombresdefnides, opisn Crews dewk ac Iecin, en la ventana de dilogo s6lo marca Fla superior y Aceptar. Verificarelnomee ‘tno para cada rango. 1K Bserbir las siguientes formulas en las celdas correspondientes: £7 =BUSCARV(Clave_Producto Productos 3,FALSO)*(100-Descuentoy100) F =Cantidad*Precio 12 =Summa(Total) 17, Copa el rango E7:F7 al rango E8:FL1 18 Observar el comportamiento del modelo, cambiando la clave del cliente en In hoa Buscar ¥ nolare6mo se modifican os precios de los productos para ese cient. De igual manera, ‘aria las cantidades y claves de los productos para ver el comportamiento del modelo com- pleto, 3tecnologia de informactén “Aplicacién de tas 19. Guardar el libro de trabajo y salir dela aplicacin. Exempto 3.4 (Crear un modelo para caeu Jarel impuesto sobre larenta de un determinado salario, con base en, tabla de impuestos correspondiente siguiente: {© De0.a3 salarios minimos (SM) no se paga impuesto De 3 SM + 1 Peso hasta 5 SM el impuesto es del 15% De 5 SM + 1 Peso hasta 7 SM el impuesto es del 25% De7 SM + 1 Peso hasta 10 SM cl impuesto es del 35% De 10SM + 1 Peso en adelante el impuesto es del 45% Crear un libro nuevo en la hoja de cé[Link] cada uno de 10s modelos plantas ‘y guardarlo oon el nombre Busqueda2xIsx en la trayectoria Evidencias\Bjemplos,Realiar 2 procedimiento para estableceren Propiedades del Archivo: Autor: [Escribir tu nombre, ‘Titulo: Ejemplo 3.4, Asunto: Uso de Ia funcién de busqueda, hace clic en Cerrar el pane 4e informacién del documento, Introducir el siguiente modelo parcial. Coniajenar nombres 2 128 celdas del ra nna eo eh siguientes formulas en las 4, Bsr as siguiente |as celdascorrespondienes: 10 =U/Salario_Minimo ~> Un peso come faci de 313 —Salario-Impuesto salar mimo 53 =SM+Un Peso +5, copa el comer dela elda 3 al ango BAB, seb on la celda B12 ta f6rmula pra calcula el impuesto “=BUSCARV Galario/SalarioMinimo B2:C5,2)*Salario/00 ee eee Ee teens aes eee ve ase ed dd ltt 8, Guardar el libro de trabajo y salir de Ia aplicacion.Aplicacién de las secnologtas de informacion Actividades Acnvwap 3.4 Objetivo Cede pce caliber ms Una de alums ese oad AUDA Pee basado en caificacion por letras Datos Usar nombres definidos de ang. 1:F6 para a bisqueda. ‘Asignar nombre “TablaCalif” al rango EI ignar ri ‘Usarlafuncidn BUSCARVO pata determina Ia EI modelo sugerido se indica a cominuaci6a: 1 [ean 3 [Juan | 8 4 Rail 50 70 fe taeda | ett end ae 30 6 | Sail at 90 [Enrique — [100 | Salvador [74 1 9 | Las 5 fe 10| Laura fo 1 [Mario 8 12 [Benito | 99 Procedimiento 1. Crea un libro nuevo, introduce ef modelo planteado y guarda e! documento cong Calificacion xis. 2, Seleccionar el rango E1:F6, hacer licen el Cuadro de nombre y escribir TablaCalif 3. Eseribir en la celda C2 la siguiente formula: =BUSCARV(B2:TablaCalif.2) 4, Copiar el contenido dela celda C2en el rango C3:C12.Uy an aaa “7 yo de table comesponne, isis de datos 3 Analii bla estdcompuesta de renglonesy columns. El pier Fl encabezado de cada columna reibe el nombre de ese MP SU engin ‘campo, y cada renglén es un {52> ao comee todos 1s dts reocionalsconun een ea ub ps otto ep canoe at S 8 egmitalo ences micas tne ne Crees aa os os gists comparten Tos mismos nombres de campos + Las valores 6e cada campo son consstentes (por ejemplo, nimeros con igual cana de decimales). os valores de cada campo son del mismo tipo de dato, ‘na ista por cada hoja de edleuo (el ordenamientoyfilrado es ms ecient), Ls nombres de los campos deben de ser descriptive, Los campos deben de separase en partes bésicas (en el caso de una direccién, en calle, colo- ns, o6digo postal, etcéera). 4 Nodebe de haber renglones en blanco, Tha1As DINAMICAS Concepto Lab dingmica (pivot table) es una herramienta de la hoja de eéteulo que permite crear una ‘isminenetiva de un conjunto de datos. Se dice que la vista es un report o informe de la tabla Aiea igo de report es una forma répida y fécil de: + Resumir grandes cantidades de datos en informacién con significado,