UNIVERSIDAD NACIONAL COLOMBIA FACULTAD DE CIENCIAS ECONMICAS UNIDAD DE INFORMTICA Director: Coordinadoras: Asesores y conferencistas: Cesar Augusto Mendoza Martnez Yasmn Luca Durn Bobadilla Ana Milena Granados Rodrguez Henry Martnez Sarmiento Carlos Alberto Rodrguez Nestor Orlando Rojas Castillo Edison Jair Duque Oliva Este trabajo es resultado del esfuerzo de todo el equipo perteneciente a la Unidad de Informtica Se prohibe la reproduccin parcial o total de este documento, por cualquier tipo de mtodo fotomecnico y/o electrnico, sin previa autorizacin de la Universidad Nacional de Colombia.. Las aplicaciones WINDOWS, WORD, EXCEL, POWER POINT, ACCESS, PROJECT y EXPLORER son marcas registradas de Microsoft Corporation. NETSCAPE es una marca registrada de Netscape Corporation AUXILIARES DE INVESTIGACIN Encargados de la recopilacin y diagramacin del paquete integrado de manuales Carolina Rivera Torres Claudia Marcela Galvis Claudia Patricia Rincn Diego Fernando TamayoEduardo Ernesto Arias Rodas Francisco Ortiz Santos Gonzalo Andrs Jimnez Henry Cubaque Hortua Olga Cecilia Crdenas RojasYamile Sierra Navarro En otras labores de investigacin Adriana Vanegas Narvaez Alejandra Aristizbal C. Carolina Duarte Diana Marcela Baquero Liliana Snchez Luis Carlos Villabona Luisa Fernanda Roa S. Maritza Melgarejo Miguel Angel Santos Rodriguez Mauricio Vergara Bravo Nancy Patricia Corredor Roa Oscar Javier Silva Pedro Alejandro Leguizamo Pedro Rene Jimenez Hernndez Robinson Moscoso Perez Rocio Leal Cuellar Sandra Patricia Celis Vargas Yakelin Tocarruncho Manual desarrollado por Edison Jair Duque Oliva EXCEL AVANZADO 1 11 1NTRODUCCON NTRODUCCON NTRODUCCON NTRODUCCON Desdelaaparicindelasprimerashojasdeclculo,las necesidades que estas pretenden cubrir han ido aumentando con granrapidez,estoesocasionadoporelgranaugequehan tenidoloscomputadoresduranteelltimosiglo.Microsoft Excel proporciona una solucin sencilla y eficaz a las nuevas exigenciasquesepresentanenelmundoactual.Excela travsdeunambientegrficopermiterealizaroperaciones matemticasbsicasycomplejas,desarrollardiferentes funciones(financieras,lgicas,estadsticas,matemticas, etc.) creacin de grficos y manipulacin de bases de datos, adems puede ser utilizado para el anlisis e interpretacin de datos financieros, estadsticos, etc. MicrosoftExcelesunapoderosaherramientaquefacilitala realizacin de operaciones matemticas a travs de frmulas, permiteunaexcelentepresentacinparalosdatos,ysu representacingrfica,laimpresindelashojas,el intercambiodeinformacinconotrasaplicacionesyadems proporcionaalternativasdegrancalidadparamejorarel manejo de los registros de una base de datos simple. 2 22 2METODOLOCAMETODOLOCAMETODOLOCAMETODOLOCA PARA CONSULTAR EL MA PARA CONSULTAR EL MA PARA CONSULTAR EL MA PARA CONSULTAR EL MANUAL NUAL NUAL NUAL 2.1 ESTRUCTURA TEMTICA DEL MANUAL Elobjetivodeestemanualesmostraralgunasdelas caractersticasavanzadasdeExcel.Aunquesepartedela premisadequesetieneconocimientosbsicos,seiniciara por recordar aspectos generales del paquete. Despus de tratar los aspectos bsicos se abordarn temas ms especficos,comooperacionesentrerangos,funciones, formatos de celda y las opciones especiales de los mens de HERRAMIENTASyDATOS,loscualestraenconsigolas principales opciones avanzadas de la aplicacin.2.2 CONVENCIONES Parafacilitarelusodeestemanual,esimportanteque conozca las convenciones utilizadas dentro del mismo. Iconos:Cuando se hace mencin de conos o botones el nombre del icono aparecer entre comillas, por ejemplo Copiar MENS:Parahacerrelacinamensseutiliza mayscula sostenida. Por ejemplo ARCHIVO. Opcionesdelmen:Parahablaracercadeunaopcin delmenseutilizanegrillaycursiva,conlaprimera letra en mayscula. Por ejemplo Autoformato. NombredeVentanaActiva:Alreferenciarunaventana dealgncomandoespecficodentrodelpaquetese utilizacursiva y subrayado. Por ejemplo Guardar Teclado: Cuando se menciona una combinacin de teclas se utiliza negrita solamente. Por ejemplo Shift. Cuando aparece un signo ms (+) entre una combinacin deteclas,esteindicaquestasdebenpresionarse simultneamente. Por ejemplo Ctrl + v

2.3 COMO UTILIZAR EL MANUAL A continuacin se presenta una lista decaractersticas incluidas para proporcionar al usuario mayor facilidad y eficiencia en la utilizacin del manual. ALTERNATIVAS:Estasnotaspresentanlosmltiplescaminos porloscualessepuedeejecutarunaoperacin.Tambinpueden remitiralusuariodelmanualaotrocaptuloquelepermita complementar o entender mejor el tema tratado TRUCOS O NOTAS: Corresponden a pequeos apuntes que se debentenerencuentaparaelmanejodelsoftwarey/o describirn trucos que agilizarn el trabajo en la aplicacin. 3 33 3TA8LA DE CONTEND TA8LA DE CONTEND TA8LA DE CONTEND TA8LA DE CONTENDO OO O 1INTRODUCCIN2 2METODOLOGA PARA CONSULTAR EL MANUAL3 2.1 ESTRUCTURA TEMTICA DEL MANUAL3 2.2 CONVENCIONES3 2.3 COMO UTILIZAR EL MANUAL4 3TABLA DE CONTENIDO5 4FRMULASS 4.1 PUNTEROS DEL MOUSE EN EXCELS 4.2 OPERADORES ARITMTICOS O NUMRICOS9 4.3 OPERADORES DE COMPARACIN O RELACIONALES9 4.4 OPERADORES LGICOS10 4.5 FORMATO CONDICIONAL10 5EDICIN DE LA INFORMACIN11 5.1 RANGOS11 5.2 OPERACIONES CONRANGOS12 5.3 RELLENAR Y AUTOLLENAR15 5.3.1SER!ES15 5.3.2AUTORRELLENAR18 5.3.3LN!TE18 5.3.+TENDENC!A18 5.3.5LLENAR Y AUTOLLENAR CON ARRASTRE18 6GRFICOS20 6.1.1COLUNNAS25 6.1.2BARRA2/ 6.1.3LNEA2/ 6.1.+C!RCULAR2/ 6.1.5AREA28 6.1.6AN!LLOS29 6.1./RAD!AL29 6.1.8SUPERF!C!E30 6.1.9CONO, C!L!NDRO Y P!RAN!DE31 6.1.10COT!ZAC!ONES31 6.1.11GRAF!CO DE PARETTO31 6.2 MODO GRFICO35 6.3 CAMBIAR TIPO DE GRAFICO35 6.4 AGREGAR DATOS A UN GRFICO35 6.5 DAR FORMATO A UN GRAFICO37 6.5.1ENFOOUE PARA UN GRAF!CO3D39 7FUNCIONES EN EXCEL42 7.1 FUNCIONES FINANCIERAS43 /.1.1FUNC!ON PAGO++ /.1.2TASA+6 /.1.3NPER+6 /.1.+PAGO!NT+6 /.1.5PAGOPR!N+6 /.1.6PAGO.!NT.ENTRE+6 /.1./PAGO.PR!NC.ENTRE+6 /.1.8FUNC!ON vALOR ACTUAL (vA)+/ /.1.9vF+/ /.1.10vF.PLAN+/ 7.2 FUNCIONES FECHA Y HORA4S /.2.1AHORA()+9 /.2.2ANO ()+9 /.2.3D!A ()+9 /.2.+FUNC!ON HOY+9 7.3 FUNCIONES MATEMTICAS Y TRIGONOMTRICAS49 /.3.1FUNC!ON ALEATOR!O+9 /.3.2CONTAR.S!50 /.3.3ENTERO50 /.3.+NDETERN50 /.3.5N!NvERSA51 /.3.6NNULT52 7.4 FUNCIONES LGICAS52 /.+.1FUNC!ON O52 /.+.2FUNC!ON Y52 /.+.3FUNC!ON S! (COND!C!ONALES)52 7.5 FUNCIONES ESTADSTICAS54 /.5.1CONTAR Y CONTARA5+ /.5.2TENDENC!A55 /.5.3FRECUENC!A55 7.6 FUNCIONES DE TEXTO56 /.6.1OPERADOR DE TEXTO56 /.6.2CONCATENAR5/ /.6.3ESPAC!OS5/ /.6.+EXTRAE58 7.7 FUNCIONES ANIDADAS5S SOPCIONES ESPECIALES EN EL MANEJO DE BASES DE DATOS61 S.1 FILTROS AVANZADOS62 S.2 OPCION TABLA DINMICA63 8.2.1PASOS PARA LA GENERAC!ON DE UNA TABLA D!NAN!CA63 S.3 IMPORTAR DATOS67 9COMPLEMENTOS71 10ANALISIS DE SENSIBILIDAD74 10.1 TABLAS DE DOBLE ENTRADA74 10.2 ESCENARIOS75 10.2.1BUSCAR OBJET!vO/5 10.2.2ESCENAR!OS/6 10.2.3SOLvER/8 11MACROSS5 11.1 CREACION DE UNA MACROS5 11.1.1GRABAR UNA NACRO85 11.2 EJECUTAR UNA MACROS7 11.2.1EJECUTAR UNA NACRO DESDE UN BOTON DE LA BARRA DE HERRAN!ENTAS88 11.2.2EJECUTAR UNA NACRO DESDE UN BOTON O UN CONTROL GRAF!CO88 11.2.3EJECUTAR UNA NACRO DESDE UN AREA, ZONA !NTERACT!vA O UN OBJETO GRAF!CO89 11.3 COPIAR UNA MACROS9 11.4 MACROS Y VIRUS90 11.5 MODIFICAR UNA MACRO91 12PROBLEMAS FRECUENTES92 12.1 MENSAJES DE ERROR92 13CONSEJOS PRCTICOS94 4 44 4FORMULASFORMULASFORMULASFORMULASLa creacin de formulas esta planteada en el manual bsico de Excel,sinembargoseharunarpidadescripcindeesta parte. 4.1 PUNTEROS DEL MOUSE EN EXCELPUNTERODESCRIPCIN Aparecealsealarmens,barrasde desplazamiento,barrasdeherramientas.Se utilizaenExcelparamoverocopiarel rea seleccionada Indicaunlugardereadetextodeuna celda o recuadro. Indicaelcontroladorderellenodeun rangoseleccionado.Permiterealizarel copiadoautomticoogenerarlistas. Es llamado indicador de Arrastre Indica el lmite de cabecera de una columna yseutilizaparamodificarelanchode sta. Indica el lmite de cabecera de una fila y se utiliza para modificar el alto de sta. Indica el cuadro de divisin de la barra de desplazamiento horizontal. Indica el cuadro de divisin de la barra de desplazamiento vertical. IndicaelreadetrabajodeMicrosoft Excel. Se utiliza para realizar marcaciones dentro de ella. Existen tres tipos de operadores: aritmticos, matemticos o numricos; relacionales y lgicos. Adems para las funciones seutilizarnotrosoperadoresquesetratarneneltipo especfico de funciones. 4.2 OPERADORES ARITMTICOS O NUMRICOSRealizan operaciones matemticas bsicas, combinan y producen resultados numricos. Estos operadores son: SIGNODESCRIPCIN +Suma -Resta o negacin, resta dos valores o niega uno *Multiplicacin %Porcentaje ^Funcin exponencial /Dividido 4.3 OPERADORES DE COMPARACIN O RELACIONALES Estosoperadoressonutilizadosparaproducirvalores lgicos, es decir, FALSO o VERDADERO. Tambin debe colocarse antes de la frmula el signo (=) SIGNODESCRIPCIN =Igual >Mayor que =Mayoroigual que C2 EnlacasillaValor_si_verdaderoescribaelvalorquese devolversilacondicinaevaluar(prueba_lgica)es VERDADERA.Siprueba_lgicaesVERDADERAyestacasilla aparece vacala funcin devuelve VERDADERO.Esta casilla puede ser texto opuede ser una frmula. Para este ejemplo escriba Presupuesto Excedido EnlacasillaValor_si_falsoescribaelvalorquese devolver si la condicin a evaluar (prueba_lgica) es FALSA, si se omite el argumento de esta casilla, la funcin devuelve FALSO. Esta casilla puede ser texto opuede ser una frmula. Para este ejemplo escriba Presupuesto Exacto. HagaclicenelbotnAceptarycopielafrmulahacia abajo. Al final obtendr un resultado como este Observe que al ubicarse en la celda D2 la barra de frmulas indica la siguiente operacin: SI(B2>C2;"Presupuesto excedido";"Presupuesto exacto")7.5 FUNCIONES ESTADSTICAS 7.5.1CONTAR Y CONTARA A travs de CONTAR se puede determinar la cantidad de celdas quecontienenunnmeroylosnmerosenlalistade argumentos.Conestafuncinpuedeobtenerelnmerode entradas en un campo numrico de un rango o de una matriz de nmeros. ConCONTARAsepuedeobtenerlacantidaddeceldasque contienen informacin sin importar el tipo. Con esta funcin puede obtener el nmero de entradas un rango o de una matriz de nmeros. Por ejemplo tomemos la siguiente matriz: En el mismo rango A1:Q1, la funcin CONTAR da como resultado 15, y la funcin CONTARA 17, debido al tipo de datos. 7.5.2TENDENCIALafuncindetendenciapermiteobtenerlosvaloresque resultandeunatendencialinealconelmtododemnimos cuadrados.Setomacomobasevaloresdeobservacinque denomina conocido_Y y conocido_X, tomados como matrices de la hoja de clculo. Por medio de estos valores establece nuevos valores de tendencia acordes a la recta que se forma con los valoresconocidos.Paraestopidenuevosvaloresdeuna variablequelepermitanestablecerlatendencia correspondiente.En el caso de que no se coloque la matriz de nuevos valores de X se toma la misma de valores conocidos, y si se omiten ambassetomalasseriedenmerosiniciandoen1hastael numero de valores de Y. 7.5.3FRECUENCIASerefiereaestadsticadescriptivadondesedeterminala frecuenciadeocurrenciadeunrangodedatosenunagran seleccin,esdecir,tomalosvaloresdeungrupoylos busca dentro de una base de informacin. Arroja la frecuencia deaparicindeesosvaloresenlabase.Trabajados argumentos,datosreferidosalabaseenlacualharla bsqueda y Grupos referidos a los lmites superiores de los intervalos establecidos. Por ejemplo, supongamos que tenemos ungrupodeestudiantesquetienensuresultadofinaldel semestre.Son50estudiantesperoquieroagruparlosen intervalosparasabercuantosseencuentranendeterminado rango de datos. Veamos la imagen: Es importante aclarar que los datos pueden estar o no en la misma columna o fila. Al aplicar la funcin tambin se debe aplicar el principio de matricesparamostrarlosresultados,comosemuestraa continuacin: La funcin FRECUENCIA pasa por alto celdas en blanco y texto. 7.6 FUNCIONES DE TEXTO 7.6.1OPERADOR DE TEXTO Existe un operador de texto, representado por el signo & que une dos o ms valores de texto en uno slo Porejemplo,silaceldaB2contieneeltextoComprayla celda D5 contiene el texto de Frutas Para concatenar esta palabras vaya a la celda C2 la siguiente frmula =B2&D5 y el resultado ser: comprade Frutas Sideseaagregarunespacioentrelaspalabrascomprayde simplemente digite esta frmula: =B2& &D5, es decir agregue un espacio en blanco entre comillas y luego si concatene con la celda D5. Sialconcatenarunodelosvaloresesunareferenciade celda y la celda a la que hace referncia contiene frmulas , este operador une el valor producido por la frmula con los valores de texto. Cuandoincluyadentrodeunafrmulalasreferenciasdecelda, puede escribir directamente en la celda. Por ejemplo =S1+B2+C3 o conlaayudadelratnpuededarclicsobrelaceldadeseada.La celdaescogidatendrunalneapunteada;sihuboalgnerrory escogiunaceldaincorrecta,simplementehagaclicalacelda correctae inmediatamente la referencia cambiara. 7.6.2CONCATENAR Lafuncindeconcatenarpermiterealizarlaoperacin anterior, a travs del asistente para funciones. Es igual a la utilizacin del operador tipo texto. 7.6.3ESPACIOS Estafuncinsirveparaeliminarlosespaciosquese encuentran al interior de un texto, a excepcin del espacio normal que se deja entre palabras. Por ejemplo, si se tiene un texto como: Estadodeperdidas ygananciasalusarlafuncindeespacios quedara as: Estado de perdidas y ganancias. 7.6.4EXTRAE Comosunombrelodiceestafuncinextraedeunacelda determinadadenominadatexto,unnumerodeterminadode caracteresdenominadosnm_de_caracteres,apartirdeun carcter inicial de ese texto llamado posicin_inicial.7.7 FUNCIONES ANIDADAS Puedenutilizarsefuncionescomoargumentosdeotras funciones.Unafrmulapuedecontenercomomximosiete nivelesdefuncionesanidadasPuedeutilizarlaPaletade frmulas para anidar funciones como argumentos.Con los mismos datos del ejemplo anterior, se desea evaluar las siguientes condiciones: Silosgastosactualessonmenoresque3000son bajos sisonmayoresque3000peromenoresque4000son justos"si son iguales o mayores que 4000 son altos Para evaluar estas condiciones, realice lo siguiente: Cree una nueva columna con el nombre de observaciones Ubiquese en la celda E2y pulse el botn Pegar Funcin de la Barra de Herramientas Estndar. Elija funciones Lgicas y luego seleccione la funcin Si EnlacasillaPrueba_lgica,evaluesilosgastosactuales son menores que 3000 (B2