1 UNIVERSIDAD COLEGIO MAYOR DE CUNDINAMARCA INFORMATICA EXCEL AVANZADO PRESENTADO POR: PAOLA ANDREA PALACIO PRESENTADO A: CRISTIAN ANDRES SARMIENTO Bogota, Mayo 20 de 2014
2 EXCEL Microsoft Excel es una aplicación distribuida por Microsoft Office para hojas de calculo. Este programa es desarrollado y distribuido por Microsoft, y es utilizado normalmente en tareas financieras y contables.
3 Recursividad: La Recursividad es el proceso por el cual un valor de salida de una función se convierte en un parámetro de entrada de otra función, generando funciones anidadas. Niveles de Profundidad de una Función: Cada nivel de profundidad representa una función dentro de otra función y así sucesivamente. Rangos: Los Rangos son parámetros de entrada de una función que representan un conjunto de Celdas tanto columnas como renglones. RECORDEMOS CONCEPTOS
4 Las fórmulas en Excel son expresiones que se utilizan para realizar cálculos o procesamiento de valores, produciendo un nuevo valor que será asignado a la celda en la cual se introduce dicha fórmula. En una fórmula, por lo general, intervienen valores que se encuentran en una o más celdas de un libro de trabajo. Las fórmulas están conformadas por operadores de cálculo, y, con frecuencia, por funciones. Para introducir una fórmula en una celda, se debe entrar como primer carácter el signo igual ( El signo igual = le indica a Excel que los caracteres que le siguen constituyen una fórmula ). Cuando se escribe la fórmula no se deben dejar espacios en blanco dentro de la misma. FORMULAS
5 Componentes de una fórmula Una fórmula en Excel puede contener cualesquiera de los siguientes elementos: referencias a celdas, constantes, operadores y funciones. =PI()*B1^2 En esta fórmula podemos identificar los elementos nombrados anteriormente: - Referencias a celdas: B1. Se hace referencia al valor que contiene la celda B1. - Constantes: el valor 2. Números o valores escritos directamente en una fórmula. - Operadores: ^ y *. El operador ^ (acento circunflejo) eleva un número a una potencia, y el operador * (asterisco) multiplica. FORMULAS
6 - Funciones: la función PI(). Esta función al ser invocada devuelve el valor de pi: 3,141592... EJEMPLO: =SI(PROMEDIO(A6:A13)
7 FUNCIONES FUNCION SI Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO. Utilice SI para realizar pruebas condicionales en valores y fórmulas. Sintaxis SI(prueba_lógica;valor_si_verdadero;valor_si_falso) Prueba_lógica es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO. Valor_si_verdadero es el valor que se devuelve si el argumento prueba_lógica es VERDADERO. Valor_si_falso es el valor que se devuelve si el argumento prueba_lógica es FALSO.
8 SUMAR.SI.CONJUNTO Rango_suma (obligatorio): El rango de celdas que contiene los valores a sumar. Rango_criterios1 (obligatorio): El rango de celdas que será evaluado por el Criterio1. Criterio1 (obligatorio): El criterio que deben cumplir las celdas del Rango_criterios1. Rango_criterios2 (opcional): El segundo rango de celdas que será evaluado por el Criterio2. Criterio2 (opcional): El criterio que deben cumplir las celdas del Rango_criterios2.
9 EquipoCantidadInventarioCiudadALARMA LMU800209BOGOTÁ LMU1275151BOGOTÁCOMPRAR LMU11703014BOGOTÁ LMU8002518CALI LMU1275252CALICOMPRAR LMU11703021CALI LMU800255ARMENIACOMPRAR LMU12753022ARMENIA LMU1170404ARMENIACOMPRAR INVENTARIO NACIONAL EquipoInicialActual Usado Actual en Inventario LMU800703832 LMU1275704525 LMU11701006139 ClienteEquiposUSADOSCIUDADFecha Venta 10001LMU800 2 BOGOTÁ20-ene 10001LMU1275 1 BOGOTÁ10-feb 10001LMU1170 4 BOGOTÁ30-ene 10002LMU800 2 CALI15-ene 10002LMU1275 20 CALI20-ene 10003LMU1170 3 CALI15-ene 10003LMU800 15 ARMENIA30-ene 10003LMU1275 1 ARMENIA20-ene 10004LMU1170 3 ARMENIA10-feb 10004LMU800 1 BOGOTÁ30-ene 10004LMU1275 10 BOGOTÁ15-ene 10005LMU1170 3 BOGOTÁ20-ene 10005LMU800 4 CALI15-ene 10006LMU1275 2 CALI30-ene 10006LMU1170 4 CALI15-ene 10006LMU800 2 ARMENIA20-ene 10007LMU1275 5 ARMENIA15-ene 10008LMU1170 33 ARMENIA15-ene 10009LMU800 8 BOGOTÁ20-ene 10010LMU1275 3 BOGOTÁ15-ene 10011LMU1170 9 BOGOTÁ30-ene 10012LMU800 1 CALI15-ene 10013LMU1275 1 CALI20-ene 10014LMU1170 2 CALI30-ene 10015LMU800 3 ARMENIA15-ene 10016LMU1275 2 ARMENIA20-ene =Inventario[[#Esta fila],[Cantidad]]- SUMAR.SI.CONJUNTO(EquiposUsados[USADOS],EquiposUsados[Equipos],Inventario[ [#Esta fila],[Equipo]],EquiposUsados[CIUDAD],[Ciudad]) EJEMPLO SUMAR.SI.CONJUNTO
10 Un grafico es la representación de datos, generalmente numéricos, mediante líneas, superficies o símbolos, para ver la relación que esos datos guardan entre si y facilitar su interpretación. GRAFICO Fecha Venta(Todas) Suma de USADOSRótulos de columna Rótulos de filaARMENIABOGOTÁCALITotal general LMU11703616961 LMU12758142345 LMU8002011738 Total general644139144 Conclusión el total de equipos vendidos fue de 144, en Armenia las ventas fueron de 64 equipos, en Bogota de 41 y Cali de 39, por lo tanto las mejores ventas fueron reportadas en la ciudad de Armenia.
11 La función BUSCARV de Excel tiene gran utilidad para hacer búsquedas en tablas o bases de datos. Se comprende de 4 argumentos: Valor_buscado: Regularmente se trata de una clave, código, valor, indicador, con el que realizaremos la búsqueda. Matriz_buscar_en: Es la tabla, rango, base de datos, donde se hará la búsqueda. Indicador_de_columnas: Es el número de columna de nuestra matriz donde se encuentra el resultado que deseamos obtener. Ordenado: En este argumento debemos poner 0 (cero) ó FALSO si estamos buscando el valor exacto, 1 ó VERDADERO si queremos el aproximado. FUNCION BUSCARV
12 Operador AvantrackTIPO ACACIASCiudades Tipo 1 ARAUCACiudades Tipo 2 BOSCONIA/CESARCiudades Tipo 1 BARRANCA DE UPIACiudades Tipo 2 BARRANCABERMEJACiudades Tipo 3 BARRANQUILLACiudades Tipo 4 BOGOTACiudades Tipo 1 BUCARAMANGACiudades Tipo 2 BUENAVENTURACiudades Tipo 3 CALICiudades Tipo 4 CARMEN DE APICALACiudades Tipo 1 CARTAGENACiudades Tipo 2 CASTILLACiudades Tipo 1 COTACiudades Tipo 2 CUCUTACiudades Tipo 3 DAGUA / VALLECiudades Tipo 4 FACATATIVACiudades Tipo 1 FUSAGASUGACiudades Tipo 2 GIRARDOTCiudades Tipo 3 ITAGUICiudades Tipo 4 LA MESACiudades Tipo 1 EJEMPLO BUSCARV CLIENTECIUDADSERVICIOSOPORTE TIPO CIUDAD CLIENTE 1BARRANQUILLATRASLADO SI #¡VALOR! CLIENTE 1BARRANQUILLATRASLADO SI Ciudades Tipo 4 CLIENTE 1BUCARAMANGAINSTALACIÓN SI Ciudades Tipo 4 CLIENTE 1BUCARAMANGAREVISIÓN NO Ciudades Tipo 4 CLIENTE 1BOGOTATRASLADO NO Ciudades Tipo 2 CLIENTE 1BOGOTATRASLADO SI Ciudades Tipo 2 CLIENTE 1RUBIALES DEMO INSTALACION SI Ciudades Tipo 1 CLIENTE 1RUBIALES DEMO DESINSTALACIÓ N SI Ciudades Tipo 1 CLIENTE 1RUBIALES DESINSTALACIÓ N SI Ciudades Tipo 3 CLIENTE 2RUBIALES DESINSTALACIÓ N SI Ciudades Tipo 3 CLIENTE 2RUBIALESTRASLADO SI Ciudades Tipo 3 CLIENTE 2RUBIALESTRASLADO SI Ciudades Tipo 3 CLIENTE 2RUBIALESINSTALACIÓN SI Ciudades Tipo 3 CLIENTE 2RUBIALESREVISIÓN NO Ciudades Tipo 3 CLIENTE 2RUBIALESTRASLADO NO Ciudades Tipo 3 =BUSCARV([CIUDAD],Tabla2,2,FALSO)
13 TIPO CIUDAD(Todas) Cuenta de VALORRótulos de columna Rótulos de filaDEMO DESINSTALACIÓNDEMO INSTALACIONDESINSTALACIÓN INSTALACI ÓNREVISIÓN TRASL ADOTotal general ARAUCA1157 BARRANCABERMEJA11114 BARRANQUILLA123 BOGOTA211610 BUCARAMANGA6612762158 CALI11211511 FACATATIVA112 FUSAGASUGA11 LA PALMA112 MEDELLIN448331436 NEIVA11 PEREIRA123 RUBIALES12211411 SOLEDAD / ATL112 Total general15 3115 60151 Conclusión en todas las ciudades el servicio que mas se presto fue el de traslado, esto equivale al 39% de la facturación, siendo Bucaramanga la ciudad que mas traslado realizo, correspondiente al 35% de todas las ciudades y Soledad Atlántico la que menos se le facturo con el 1.6% por este servicio
14 TABLAS DINAMICAS Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc. Para crear una tabla dinámica Excel nos proporciona tablas y gráficos dinámicos, en la opción Insertar tabla dinámica
15 Aparece el cuadro de diálogo de creación de tablas dinámicas. Desde aquí podremos indicar el lugar donde se encuentran los datos que queremos analizar y el lugar donde queremos ubicarla.
16 PARTES DE UNA TABLA DINÁMICA EN EXCEL Justo cuando se ha creado una tabla dinámica se muestra en la parte derecha de la hoja la lista de campos disponibles y por debajo las áreas donde podemos arrastrar dichos campos. Estas áreas denotan cada una de las partes de una tabla dinámica.
17 Filtro de informe. Los campos que coloques en esta área crearán filtros para la tabla dinámica a través de los cuales podrás restringir la información que ves en pantalla. Estos filtros son adicionales a los que se pueden hacer entre las columnas y filas especificadas. Etiquetas de columna. Esta área contiene los campos que se mostrarán como columnas de la tabla dinámica. Etiquetas de fila. Contiene los campos que determinan las filas de la tabla dinámica. Valores. Son los campos que se colocarán como las “celdas” de la tabla dinámica y que serán totalizados para cada columna y fila.
18 Una vez especificados los campos para cada una de las áreas, la tabla dinámica cobra vida. Puedes tener una tabla dinámica funcional con tan solo especificar las columnas, filas y valores. Los filtros son solamente una herramienta para mejorar el análisis sobre los datos de la tabla dinámica. Una vez especificados los campos para cada una de las áreas, la tabla dinámica cobra vida. Puedes tener una tabla dinámica funcional con tan solo especificar las columnas, filas y valores. Los filtros son solamente una herramienta para mejorar el análisis sobre los datos de la tabla dinámica.
19 EJEMPLO DE TABLA DINAMICA
20
21 CANTIDAD DE SERVICIO POR ESTADO Cuenta de SERVICIOSRótulos de columna Rótulos de filaAGENDADO PENDIENT E REALIZAD O Total general PROGRAMACIÓN415470165 CAMBIO POR GARANTÍA90119146355 DESINSTALACIÓN425472168 INSTALACIÓN243543102 MANTENIMIENTO7095117282 TRASLADO273543105 Total general2943924911,177 Cuenta de MODELORótulos de columna Rótulos de filaCLIENTE 1 CLIENTE 22 CLIENTE 30Total general CA11711261028 CA272I12161139 CA272P1124 CA800118151346 CA800P1225 Total general444038122 CANTIDAD DE SERVICIOS REALIZADOS POR DEPARTAMENTO, PLATAFORMA Y POR MES ESTADOREALIZADO Cuenta de SERVICIOSRótulos de columna Rótulos de filaPLATAFORMA 1 PLATAFOR MA 2Total general ANTIOQUIA105666 enero12 febrero21315 marzo189 abril5914 mayo11314 junio112 ATLÁNTICO42933 BOGOTÁ, D.C.42933 BOLÍVAR23133 BOYACÁ52833 CAQUETÁ62733 CESAR32932 CÓRDOBA23032 CUNDINAMARCA23032 HUILA72532 META62733 SANTANDER33033 Sucre23133 VALLE DEL CAUCA42933 Total general60431491
22 TABLA TIPO OBJETO Tabla en las bases de datos, se refiere al tipo de modelado de datos, donde se guardan los datos recogidos por un programa Una tabla es utilizada para organizar y presentar información. Las tablas se componen de filas y columnas de celdas que se pueden rellenar con textos y gráficos Las tablas se componen de dos estructuras: Registro: es cada una de las filas en que se divide la tabla. Cada registro contiene datos de los mismos tipos que los demás registros. Ejemplo: en una tabla de nombres y direcciones, cada fila contendrá un nombre y una dirección. Campo: es cada una de las columnas que forman la tabla. Contienen datos de tipo diferente a los de otros campos. En el ejemplo anterior, un campo contendrá un tipo de datos único, como una dirección, o un número de teléfono, un nombre, etc.
23 EJEMPLO
24 BIBLIOGRAFIA http://exceltotal.com/partes-de-una-tabla-dinamica/ http://office.microsoft.com/es-es/excel-help/funcion- si-HP010069829.aspx http://office.microsoft.com/es-es/excel-help/buscarv- funcion-buscarv-HA102752820.aspx http://exceltotal.com/la-funcion-sumar-si-conjunto-en- excel/ http://exceltotal.com/formulas-y-funciones-de-excel/ http://es.wikipedia.org/wiki/Tabla_(base_de_datos)