Excel 2007 avanzado

Elaborado por: Antonio Manuel Campos Navas

Edición: 5.1

EDITORIAL ELEARNING

ISBN: 978-84-17232-48-1

No está permitida la reproducción total o parcial de esta obra bajo cualquiera de sus formas gráficaso audiovisuales sin la autorización previa y por escrito de los titulares del depósito legal.

Excel 2007 avanzado

ínDiceIntroducción ............................................................................................... 7

UD1Funciones para trabajar con números

1.1. Funciones y fórmulas. Sintaxis. Utilizar funciones en Excel ............. 11 1.1.1. Funciones y Fórmulas. Sintaxis .............................................. 11 1.1.2. Utilizar funciones en Excel ..................................................... 111.2. Funciones Matemáticas y trigonométricas ..................................... 141.3. Funciones Estadísticas .................................................................... 181.4. Funciones Financieras .................................................................... 221.5. Funciones de Bases de Datos .......................................................... 251.6. La euroconversión ..........................................................................26Lo más importante ....................................................................................29Autoevaluación UD1 ..................................................................................31Ejercicios ...................................................................................................35

UD2Las funciones lógicas

2.1. La función =Sumar.Si ...................................................................... 412.2. La función =Contar.Si .....................................................................422.3. La función =Promedio.Si ................................................................ 432.4. La función disyunción =O................................................................442.5. La función conjunción =Y ................................................................442.6. La función condicional =SI ..............................................................45 2.6.1. Uso de la función =SI .............................................................45 2.6.2. Uso de la función =SI con la función =O .................................46 2.6.3. Uso de la función =SI con la función =Y .................................. 472.7. El Formato Condicional ..................................................................48

nuevAs tecnologíAsExcel 2007 avanzado

Lo más importante ....................................................................................55Autoevaluación UD2 ..................................................................................57Ejercicios ...................................................................................................61

UD3Funciones sin cálculos y para trabajar con textos

3.1. Funciones de Texto .........................................................................653.2. Funciones de Información ............................................................... 673.3. Funciones de Fecha y Hora .............................................................693.4. Funciones de búsqueda y referencia ............................................... 70Lo más importante .................................................................................... 73Autoevaluación UD3 ..................................................................................75Ejercicios ...................................................................................................79

UD4Los objetos en Excel

4.1. Concepto de Objeto. Utilidad ......................................................... 834.2. Insertar Imágenes: prediseñadas y desde archivo ........................... 83 4.2.1. Imágenes Prediseñadas ......................................................... 83 4.2.2. Imágenes desde Archivo ....................................................... 874.3. Textos especiales con WordArt .......................................................884.4. Los SmartArt en Excel ....................................................................904.5. Creación de Ecuaciones .................................................................. 934.6. Insertar Formas en la Hoja de cálculo ..............................................954.7. Insertar Cuadros de texto ................................................................984.8. Insertar símbolos y comentarios .....................................................98 4.8.1. Insertar símbolos ...................................................................98 4.8.2. Insertar Comentarios ............................................................99Lo más importante .................................................................................. 101Autoevaluación UD4 ................................................................................ 103Ejercicios ................................................................................................. 107

UD5Gráficos en Excel

5.1. Tiposdegráficos ...........................................................................1115.2. CrearungráficoapartirdelosdatosdeunaHoja ......................... 1145.3. Operacionesbásicasconlosgráficos ............................................ 1165.4. Herramientasdegráficos:cintadeopcionesDiseño ......................1175.5. Herramientasdegráficos:cintadeopcionesPresentación............ 1195.6. Herramientasdegráficos:cintadeopcionesFormato .................. 122Lo más importante ..................................................................................125Autoevaluación UD5 ................................................................................ 127Ejercicios ................................................................................................. 131

UD6Bases de datos en Excel (Tablas)

6.1. Tablas en Excel ............................................................................. 135 13.1.1. Creación de una tabla. Imprimir tabla ..................................1376.2. Ordenación de los datos ............................................................... 1396.3. Gestión de los datos en formato Formulario ................................. 1416.4. Utilizacióndefiltros:Autofiltros ................................................... 1426.5. Utilizacióndefiltros:FiltroAvanzado ............................................ 1456.6. Cálculos con Bases de datos: Subtotales ....................................... 1476.7. Control de datos: Validación ......................................................... 1486.8. TablasdinámicasenExcel.Gráficosdinámicos ............................. 151Lo más importante .................................................................................. 157Autoevaluación UD6 ................................................................................159Ejercicios .................................................................................................163

UD7Plantillas y Macros en Excel

7.1. Concepto de Plantilla. Utilidad ..................................................... 1677.2. Uso de Plantillas predeterminadas en Excel. Crear Plantillas de Libro ................................................................ 167 7.2.1. Plantillas predeterminadas en Excel .................................... 167 7.2.2. Crear Plantillas de Libro ....................................................... 168

nuevAs tecnologíAsExcel 2007 avanzado

7.3. Crear Plantillas personalizadas con Excel ...................................... 1707.4. UsoymodificacióndePlantillaspersonalizadas............................ 1747.5. Concepto y Creación de Macros .................................................... 176 7.5.1. Creación de una Macro ........................................................ 176

7.5.2. Ejemplo de creación de una Macro .......................................1777.6. Ejecutar Macros ............................................................................ 1787.7. Nivel de seguridad de Macros ....................................................... 180

Lo más importante .................................................................................. 183Autoevaluación UD7 ................................................................................185Ejercicios ................................................................................................. 187

UD8Formularios y análisis de datos en Excel

8.1. Concepto de Formulario. Utilidad ................................................. 1938.2. Análisis de los Botones de Formularios ......................................... 1938.3. Creación de Formularios ............................................................... 1968.4. Análisis Hipotético Manual ........................................................... 1998.5. Análisis Hipotético con Tablas de Datos ........................................200 8.5.1. Tablas de datos de una variable ...........................................200 8.5.2. Tablas de datos de dos variables .......................................... 2028.6. Buscar Objetivo ............................................................................2048.7. Solver ........................................................................................... 205Lo más importante .................................................................................. 211Autoevaluación UD8 ................................................................................ 213Ejercicios .................................................................................................215

UD9Redes e Internet con Excel

9.1. Correo Electrónico y Excel ............................................................ 2199.2. Insertar Hipervínculos en Hojas de cálculo .................................... 2209.3. Guardar Hojas de cálculo como páginas Web ................................ 2229.4. Compartir datos y protección en red ............................................. 223Lo más importante .................................................................................. 227Autoevaluación UD9 ................................................................................229Ejercicios ................................................................................................. 231

Excel 2007 avanzado

Presentación

Vamos a estudiar y practicar con funciones que permiten realizar multitud decálculosavanzados(funcionesfinancieras,matemáticas,estadísticas…),funciones que permiten establecer condicionantes lógicos antes de realizar un cálculo u operación determinada (funciones lógicas) y funciones quepermiten trabajar con textos y/o que no realizan ningún tipo de cálculo (fun­cionesdetexto,deinformación,defechayhora,debúsquedayreferencia…).

Avanzando con Excel llegaremos a un elemento que permite mejorar las pre­sentaciones e introducir información de una manera especial: se trata de los Objetos.Éstosnospermitencrearrótulos(WordArtySmarArt),Ecuaciones(Editordeecuaciones), formasgeométricas (Formas),etc.Peroel tipodeobjeto más avanzado y útil para una Hoja de cálculo son losGráficos. Gra­cias a los mismos la información de la tabla podrá presentarse de una maneraclaray,almismotiempo,avanzada(gráficosde líneas,debarras,desectores…).

Porúltimo,Excelnospermiteotrasoperacionesavanzadastalescomotraba­jar conBasesdedatos (filtros, subtotales, validaciones…), crearPlantillas(libros de trabajo predeterminados) y Macros (pequeños programas quepermitenautomatizarunconjuntodeoperaciones).Finalmente,elúltimotema permite estudiar la relación entre Excel y las redes: compartir datos en red,usodehipervínculos,relaciónconelCorreoelectrónico…

Endefinitiva,todoelcontenidodeestemanualloconvertiráenunusuarioavanzado de Microsoft Excel 2007. Esperamos que disfrute con este manual y con el aprendizaje de Excel. Gracias por su interés.

El autor.

nuevAs tecnologíAsExcel 2007 avanzado

UD1Funciones para trabajarcon números

1.1. Funciones y fórmulas. Sintaxis. Utilizar funciones en Excel 1.1.1. Funciones y Fórmulas. Sintaxis 1.1.2. Utilizar funciones en Excel1.2. Funciones Matemáticas y trigonométricas1.3. Funciones Estadísticas1.4. Funciones Financieras1.5. Funciones de Bases de Datos1.6. La euroconversión

nuevAs tecnologíAs

UD1

11Excel 2007 avanzado

1.1. Funciones y Fórmulas. Sintaxis. Utilizar funciones en Excel

1.1.1. Funciones y Fórmulas. Sintaxis

Hay muchos tipos de cálculos que bien no se pueden realizar con una fórmula creadaconelusuario (funcionesdebúsqueday referencia, funciones lógicas…),bien se requieren unos conocimientos técnicos o matemáticos específicos(financieros,matemáticos,deingeniería…)quenotenemosporquédominarentodos sus campos.

Veamos un ejemplo sencillo. Supongamos que sabemos calcular la media aritmética de un grupo de celdas o hay que introducir una fórmula demasiado larga (con mayoresprobabilidadesdeerror).SiqueremoscalcularlamediaaritméticadelosvaloresquehaysituadosentrelasceldasC3yC17(15celdasacalcular)podemosescribir la siguiente fórmula manualmente: =(C3+C4+C5+C6+C7+C8+C9+C10+C11+C12+C13+C14+C15+C16+C17)/15,obienpodemosescribirlafunción de Excel que permite calcular la media aritmética de las celdas indicadas: =PROMEDIO(C3:C17). Como puede deducirse claramente la primera fórmula requiere más tiempo,esfuerzo y tiene más probabilidades de error que la segunda.

Comohapodido comprobar las funciones comienzan, al igual que las fórmulascreadasporelusuario,porelsigno“=”y no pueden llevar espacios en blanco (dos normasbásicasparaquenoproduzcanerrores).Porotrolado,aunquesusintaxispuedesermuyvariada,sesueleutilizarmuyhabitualmentelareferenciaaungrupodeceldas(comohavistoenelejemploanterior,laexpresión“C3:C17”serefierealgrupodeceldasquevandesdeC3hastaC17,ambasinclusive)olareferenciaaunrango(porejemplo,podríamoshaberescrito=PROMEDIO(Febrero)paracalcularla media del mismo grupo de celdas siempre y cuando hayamos asignado dicho nombrealrangodeceldasC3:C17–esteconceptoseestudióeneltema6,punto6.2­).Tambiénpuedeutilizarse la referenciaaceldasdeterminadas:paraelloseusaelsigno“;”ylasintaxisdeunafunciónpodríaserentonces=SUMA(B4;B7;C9),loquesumaríalastresceldas(B4,B7yC9).

1.1.2. Utilizar funciones en Excel

No siempre es fácil recordar la sintaxis exacta de una función en Excel (nombres complejos, signosdepuntuación, valores a indicar). Por ello, Excel le ofreceunsistemadeusoyayudadelasFuncionesmuyeficaz.

Con la práctica cotidiana, para las funciones quemás utilice, no será necesariorepetirelmismoprocesoquevaaexplicarseacontinuaciónunayotravez,sinoquela función en cuestión se introducirá directamente por teclado en la celda deseada.

EDición 5.1 12

PeroenmuchasocasionesnecesitaráqueExcelleayudeenla“construcción”delafórmula necesaria.

Así,porunlado,disponemosdelacombinacióndeteclasMayúsculas+F3 que nos muestra el Cuadro de Diálogo “Insertar función”. A través del mismo podemos acceder a todas las funciones de Excel directamente (con la barra de desplazamiento yseleccionandolafuncióndeseadaExcelnosmuestralasintaxisydefinicióndelamisma),agruparlasfuncionespor“categorías”(seleccionandolacategoríadeseadadelalistadesplegable)ointroduciruntextorelacionadoconlafunciónabuscary,posteriormente,pulsarelBotón“Ir”.

Porotrolado,disponemosdelGrupodecomandos“Biblioteca de funciones” de la Pestaña“Fórmulas”que,ademásdepermitiraccederalCuadrodediálogoanteriormedianteelBotón“Insertar Función”,permiteaccederalasdistintasfuncionesdeExcel a través de las distintas listas desplegables/categorías que se muestran en dicho grupo.

UD1

13Excel 2007 avanzado

UnavezquehemosvistoelmétododeaccederalasdistintasfuncionesdeExcel,vamos a estudiar ahora el proceso a seguir para utilizarlas en nuestras Hojas de Cálculo, al mismo tiempo que comprobamos su resultado con un ejemplosencillo:

4 Sitúese en la celda donde vamos a intro­ducir la función (en nuestro ejemplo nos situaremos en B7).Vamosautilizarcomoejemplo las notas de las áreas básicas de Educación Primaria.

4 Seleccione en primer lugar la categoría de funciónnecesaria (Todas,Financieras,Fecha y hora, Matemáticas, Estadísticas,Búsqueda,Basededatos,Texto,Lógicas,Información,Definidasporelusuario)porcualquiera de los dos métodos estudiados (“Mayúsculas+F3” o “Bibliotecade Funciones”). Seleccione, por ejemplo, la categoría “Estadísticas” (en la “Biblioteca”deberáseleccionarprimerolalista“Másfunciones”).

4 En segundo lugar seleccione la función deseada (comprobará como al se­leccionarlaExcelmostrarálasintaxisyladefinicióndelafunciónseleccionada).Si desea que Excel le muestre ayuda más extensa sobre las funciones haga “clic”sobreelBotón“Ayuda”(?).Ennuestroejemploseleccione,dentrodelacategoría“Estadísticas”,lafunción“Promedio”.Finalmente,haga“clic”sobreel botón Aceptar.

4 Nosencontramosconunnuevocuadrodediálogotitulado“Argumentos de la Función” donde introduciremos las celdas o valores que van a permitir realizar el cálculo. Con el botón de selección puede regresar a Excel para seleccionar las celdas afectadas por la función (seleccione en nuestro ejemplode“B2”a“B5”otecleedirectamenteenlaopción“Número1”lare­ferencia B2:B5).Además,vuelveadisponerde laopción“Ayudasobreestafunción”comoenelcuadroanterior.Unavezqueapareceel“Resultadodelafórmula”yestádeacuerdoconélhaga“clic”sobreelbotónAceptar.

EDición 5.1 14

Podrá comprobar cómo en la posición B7 aparece el resultado 7,5 (nota media de lascuatronotas).Vamosaestudiarapartirdeestemomentounaparteimportantede las más de 300 funciones de las que dispone la Hoja de Cálculo Excel.

1.2. Funciones Matemáticas y trigonométricas

Las Funciones Matemáticas permiten resolver multitud de fórmulas matemáticas detipoaritmético,trigonométrico…Lasmásimportantessonlassiguientes:

4 =ABS(número): devuelve el valor absoluto de un número (si el número es positivo,ésteseguirásiendopositivo;sielnúmeroesnegativo, loconvierteenpositivo).Porejemplo,sienA7seencuentraelvalor­5yenA8aplico lafunción=ABS(A7),elresultadodelamismaserá5.Enrealidad,estafunciónsueleaplicarseencombinaciónconotras,comolasdetipoFinanciero,paraqueadquieraunautilidadreal.Porejemplo,=ABS(PAGO(B5%/12;C5*12;D5)).Deestaforma,lafunciónpagonosdevolveríalacantidadmensualapagarporunpréstamocomounnúmeronegativopero,alutilizar la función=ABS, lacantidad aparecerá como positiva.

4 =ALEATORIO(): devuelve un número aleatorio entre 0 y 1. Simplemente hemosdeteclear=ALEATORIO().Estafórmulapuedeadaptarseparaobtenernúmeros aleatorios entre dos límites. Por ejemplo, para obtener númerosaleatorios entre 1 y 49 (juego de la Primitiva) tendremos que introducir=ALEATORIO()*49yeliminar todos losdecimalesde lacelda (conelBotón“Disminuirdecimales”delGrupodecomandos“Número”,Pestaña“Inicio”).Cada vez que pulsemos F9 (“calcular ahora”) cambiará el resultado de la fórmula.

4 =ALEATORIO.ENTRE(nº inferior; nº superior): devuelve un número alea­torio entre los dos números especificados.Así,continuandoconelejemploanterior, la función =ALEATORIO.ENTRE(1;49), nos devolverá un númeroaleatorioentre1y49,debiendopulsarF9cadavezquesequieracambiarderesultado.

4 =COCIENTE(numerador;denominador): devuelve la parte entera del resul­tadodeunadivisión.Porejemplo, si utilizamos la función=COCIENTE(9;2)elresultadoserá4(sólodevuelvelaparteenteradelcociente).Siencambioescribimoslafórmula=9/2elresultadoserá4,5(cocientecompleto).

4 =COMBINAT(número;tamaño): indica el número de combinaciones sin repetición que pueden realizarse de un número de elementos combinados según el tamaño indicado. Por ejemplo, continuando con el juego de laprimitiva, al introducir =COMBINAT(49;6), Excel nos devuelve el resultado13.983.816 (número de combinaciones posibles de 49 números agrupados de 6en6).

UD1

15Excel 2007 avanzado

4 =ENTERO(número): redondea un número hasta el entero inferior más próximo.Porejemplo,sienD5tenemoselvalor7,6543yenD6aplicamoslafunción=ENTERO(D5),elresultadoserá7.

4 =FACT(número): devuelve el factorial de un número. Así, por ejemplo=FACT(C4)yC4=5,devuelveelvalor120(Factorialde5=5*4*3*2*1=120).

4 =M.C.D(número1;número2;…): devuelve el máximo común divisor de los númerosespecificados(esdecirdevuelveelnúmeromayorquees,a lavez,divisordetodoslosnúmerosindicados).Veaelejemplosiguiente:

4 =M.C.M(número1;número2;…): devuelve el mínimo común múltiplo de los númerosespecificados(esdecirdevuelveelnúmeromenorquees,alavez,múltiplodetodoslosnúmerosindicados).Veaelejemplosiguiente:

4 =NUMERO.ROMANO(número): convierte un número natural a su equiva­lente en número romano. Por ejemplo: =NUMERO.ROMANO(19), nos de­volverá XIX. Esta función se utiliza a menudo para expresar el año actual en

(silostrozosmiden36,54y27m,ysequierencortar todas las telas entrozosiguales,cadatrozo nuevo deberá medir9m)

(si los vehículos entran hoy en almacén todos a la vez, y entran cada15,5y9díasrespectiva­mente,volveránacoin­cidir los tres vehí culos en almacén dentro de 45 días)

EDición 5.1 16

númerosromanos.Porejemplo:=NUMERO.ROMANO(2009),nosdevolveráMMIX(comosiemprepuedeindicarlaceldaynoelvalor).

4 =PRODUCTO(número1;número2): multiplica los argumentos expresados en la función.Así,susintaxiseslasiguiente:=PRODUCTO(número1;número2;número3;…).Porejemplo,siqueremosmultiplicarlosvaloresdeB3,C3,D3yF3loexpresaremosdelasiguienteforma:=PRODUCTO(B3;C3;D3;F3).

4 =PI(): devuelve el valor del número pi (π=3,141592…)con15dígitosdepre­cisión. Este valor tiene un uso fundamental en el cálculo de la longitud de la circunferencia (L= π*d),enelcálculodeláreadelcírculo(a= π*r2),volumendeloscuerposredondos(esfera,cilindroycono)…

4 =POTENCIA(número;potencia): devuelve la potencia de un número (eleva “número” a la potencia indicada).Así por ejemplo =POTENCIA(C5;C6), sa­biendoqueC5=4yC6=3,devolveráelvalor64(43=4*4*4=64).

4 =RAIZ(número): calcula la raíz cuadrada del número especificado. Así, lafunción=RAIZ(121)devolveráelresultado11.

4 =REDONDEA.IMPAR(número): redondea un número hasta el siguiente número entero impar.Porejemplo,alaplicarestafunciónen=REDONDEA.IMPAR(7,75),elresultadoobtenidoes9(siguientenúmeroenteroimpar).Comosiempre,lohabitualserálareferenciaalaceldaynoelvalordelamisma.

4 =REDONDEA.PAR(número): redondea un número hasta el siguiente núme­ro entero par.Lasintaxisessimilaralaanterior:=REDONDEA.PAR(número).Al aplicar esta función sobre elmismo ejemplo anterior (7,75), el resultadoobtenidoserá8(siguientenúmeroenteropar).

4 =REDONDEAR(número; nº decimales): redondea un número al número de decimales especificado.Sintaxis:=REDONDEAR(número;númerodedecimales).Así,porejemplo,sielcontenidodeC5es4,75386yenD5introducimoslafunción=REDONDEAR(C5;4), el resultado que se obtiene es 4,7539 (4 decimales yredondeofinala9porque6esmayorque5).

4 =REDONDEAR.MAS(número;nº decimales): redondea un número hacia arri­ba, según el número de decimales especificado.Porejemplo,=REDONDEAR.MAS(4,75386;3)nosdarácomoresultado4,754(redondeodetresdecimaleshaciaarriba).

4 =REDONDEAR.MENOS(número;nº decimales): redondea un número hacia abajo,segúnelnúmerodedecimalesespecificado.Tomandoelmismoejemploanterior, =REDONDEAR.MENOS(4,75386;3), el resultado en este caso será4,753(redondeodetresdecimaleshaciaabajo).

UD1

17Excel 2007 avanzado

4 =RESIDUO(dividendo;divisor): devuelve el resto de una división. La sintaxis delafuncióneslasiguiente:=RESIDUO(dividendo;divisor).Porejemplo,sienD3tenemoselvalor8,573yenE3elvalor4,elresultadodeaplicarlafunción=RESIDUO(D3;E3)seráelresto0,573.

Veamos a continuación un ejemplo de uso de las funciones de redondeo:

4 =SUMA(celda inicio:celda fin): suma el rango de datos especificado. Podemosutilizarladirectamenteconlasintaxis:=SUMA(rango).Porejemplo:

VENTAS 1er Semestre 2006

Enero 1.200.000 €

Febrero 1.400.000 €

Marzo 1.300.000 €

Total 1er Trimestre 3.900.000 €

Abril 1.600.000 €

Mayo 1.700.000 €

Junio 1.500.000 €

Total 2º Trimestre 4.800.000 €

Total 1er Semestre 8.700.000 €

4 =TRUNCAR(número): truncaunnúmero,quedandosólosuvalorentero.Porejemplo,sienlasceldasB2,B3yB4tenemoslosvalores5,67,4,321y12,7yaplicamosenB5lafunción=TRUNCAR(B2:B4),obtenemoselresultado5,4y 12.

=SUMA(B2:B4)

=SUMA(B6:B8)

=SUMA(B5;B9)

EDición 5.1 18

1.3. Funciones Estadísticas

En este grupo vamos a realizar un estudio de las principales Funciones Estadísticas,funciones que son de gran utilidad dentro del mundo empresarial,por loqueprobablemente éste será uno de los grupos de los que más uso haga:

4 =CONTAR(celda inicio:celda final): cuenta las celdas numéricas de un ran­go.Lasintaxiseslasiguiente:=CONTAR(rango).Porejemplo:

33 X 44 55 77 665

4 =CONTARA(celda inicio:celda final): cuenta el número de celdas no vacías (tanto números como celdas de texto). Sintaxis: =CONTARA(rango). Con elmismoejemplodelafunciónanterior,=CONTARA(A1:G1)devolveráelvalor6.

Ambas funciones (=contar y =contara) tienen una función importante decontrol de errores. Por ejemplo, controlar quenohaya celdas vacías o quetodas las celdas sean numéricas.

4 =CONTAR.BLANCO(celda inicio:celda fin): cuenta el número de celdas en blanco dentro de un rango. Se trata de una función interesante a la hora de controlarposibleserrores.Así,porejemplo,enundeterminadorangodedatosque contengan las ventas de los doce meses del año el resultado de esta función nodeberíaser0,yaqueenelcasodeserlosetrataríaprobablementedeunerror(yaquehadetectadoalgunaceldaenblanco).Porejemplo:=CONTAR.BLANCO(C3:N3)o=CONTAR.BLANCO(RESULTADOS).

4 =CUARTIL(rango;cuartil): devuelve el cuartil de un conjunto de datos. El valor del cuartil puede ser: 0 (valormínimo), 1 (1er cuartil –percentil 25­), 2(2ºcuartil–mediana­),3(3ercuartil–percentil75­),4(valormáximo).Existenrealmente 3 cuartiles que dividen al conjunto de datos en cuatro partes.

Veamos un ejemplo de renta per cápita en Andalucía haciendo uso de cuartiles:

=CONTAR(A1:G1)

=CUARTIL(B3:B10;2)rentamediana(valorcentral)=CUARTIL(B3:B10;1)25%derenta(percentil25)

=CUARTIL(B3:B10;4) renta máxima

=CUARTIL(B3:B10;0) renta mínima

=CUARTIL(B3:B10;3)75%derenta(percentil75)

Interpretación de los resultados: el valor 0 nos proporciona la renta mínima; el 1er cuartil nos informa sobre el 25%(cuarta parte) menor de renta; el 2º cuartil nos informasobrelamediana(elvalorcentraldelasrentas);el3er cuartil nos informa sobre el 75%menor de renta; el valor 4 nosproporciona la renta máxima.

UD1

19Excel 2007 avanzado

4 =CURTOSIS(rango): permite estudiar si los datos son parecidos (resultado negativo)oestánmuyalejados(resultadopositivo).Así,continuandoconelejemploanterior,sienA18tecleamoseltexto“Curtosis:”yenB18introducimosla función=CURTOSIS(B3:B10)Exceldevolveráel valor ­1,06, loquequieredecir que las rentas per cápita están bastante igualadas (un resultado positivo hubieseindicadoquelasrentasestándemasiadoalejadasunasdeotras).

4 =DESVEST(rango): permite calcular la desviación estándar de un rango de datos(dispersióndelosvaloresconrespectoalamediaaritmética).Siguiendoconelmismoejemplo,siintroducimoslafunción=DESVEST(B3:B10)éstanosdevolveráelvalor1.407,89.

4 =K.ESIMO.MAYOR(celda inicio:celda final;posición): devuelve el K.ésimo (elnºdeordenindicado) valor mayor de un rango de números.Porejemplo,si deseamos conocer la segunda renta más alta de Andalucía (tomando el ejemplo anterior sobre los cuartiles) utilizaremos la siguiente sintaxis:=K.ESIMO.MAYOR(B3:B10;2),loquenosdevolveráelvalor19.500€.

4 =K.ESIMO.MENOR(celda inicio:celda final;posición): devuelve el K.ésimo (elnºdeordenindicado) valor menor de un rango numérico (función similar a laanterior,perocomenzandoporelvalormásbajo).Así,siguiendoelejemploanterior,siaplicamos=K.ESIMO.MENOR(B3:B10;2)lafunciónnosdevolveráelvalor16.500€(2ªrentamásbajadelaAndalucía).

4 =MAX(celda inicio;celda final): devuelve el valor máximo de un rango nu­mérico.Porejemplo,paracalcularelvalormáximodeunaseriededatos:

7 4 9 11 34 25 17

Utilizaremoslasiguientesintaxis:=MAX(A1:G1),loquenosdevolveráelvalor34.Siguiendoel ejemplode las rentas andaluzas, la función=MAX(B3:B10)devolveráelvalor23.000€(rentamásalta)

4 =MEDIA.ARMO(celda inicio:celda final): calcula la media armónica de una serie de datos. La sintaxis de la función es la siguiente: =MEDIA.ARMO(número1;número2;…).Ejemplo:

VELOCIDADES MEDIAS OBTENIDASRecorrido Km/h (ida) Km/h (vuelta) Media Km/hMálaga­Granada 93 84 88,27Málaga­Sevilla 99 94 96,44Málaga­Madrid 92 97 94,43Málaga­Valencia 87 89 87,99Málaga­Barcelona 82 79 80,47

1.3. Funciones Estadísticas

En este grupo vamos a realizar un estudio de las principales Funciones Estadísticas,funciones que son de gran utilidad dentro del mundo empresarial,por loqueprobablemente éste será uno de los grupos de los que más uso haga:

4 =CONTAR(celda inicio:celda final): cuenta las celdas numéricas de un ran­go.Lasintaxiseslasiguiente:=CONTAR(rango).Porejemplo:

33 X 44 55 77 665

4 =CONTARA(celda inicio:celda final): cuenta el número de celdas no vacías (tanto números como celdas de texto). Sintaxis: =CONTARA(rango). Con elmismoejemplodelafunciónanterior,=CONTARA(A1:G1)devolveráelvalor6.

Ambas funciones (=contar y =contara) tienen una función importante decontrol de errores. Por ejemplo, controlar quenohaya celdas vacías o quetodas las celdas sean numéricas.

4 =CONTAR.BLANCO(celda inicio:celda fin): cuenta el número de celdas en blanco dentro de un rango. Se trata de una función interesante a la hora de controlarposibleserrores.Así,porejemplo,enundeterminadorangodedatosque contengan las ventas de los doce meses del año el resultado de esta función nodeberíaser0,yaqueenelcasodeserlosetrataríaprobablementedeunerror(yaquehadetectadoalgunaceldaenblanco).Porejemplo:=CONTAR.BLANCO(C3:N3)o=CONTAR.BLANCO(RESULTADOS).

4 =CUARTIL(rango;cuartil): devuelve el cuartil de un conjunto de datos. El valor del cuartil puede ser: 0 (valormínimo), 1 (1er cuartil –percentil 25­), 2(2ºcuartil–mediana­),3(3ercuartil–percentil75­),4(valormáximo).Existenrealmente 3 cuartiles que dividen al conjunto de datos en cuatro partes.

Veamos un ejemplo de renta per cápita en Andalucía haciendo uso de cuartiles:

=CONTAR(A1:G1)

=CUARTIL(B3:B10;2)rentamediana(valorcentral)=CUARTIL(B3:B10;1)25%derenta(percentil25)

=CUARTIL(B3:B10;4) renta máxima

=CUARTIL(B3:B10;0) renta mínima

=CUARTIL(B3:B10;3)75%derenta(percentil75)=MEDIA.ARMO(B7;C7)

EDición 5.1 20

Deestaformaobtenemoslamediarealizadaencadarecorrido(idayvuelta),ya que la utilización de la media aritmética en este tipo de casos no es la correcta(kilómetrosiguales,tiemposdistintos).

4 =MEDIA.GEOM(celda inicio:celda final): calcula la media geométrica de una serie de datos. La sintaxis de la función es la siguiente: =MEDIA.GEOM(número1;número2;…).Porejemplo:

Elcálculodelamediageométricatieneencuenta,porejemplo,queen2007subeel13,6%deeseañomásel9,3%delanterioryasísucesivamente.

4 =MEDIANA(rango): devuelve la mediana de un rango de datos (valor central delosdatos).Así,enelejemplode loscuartiles, lamedianade losdatossecalcularácomo=MEDIANA(B3:10),loquedevolveráelvalor17750€(aligualqueelcuartil2).

4 =MIN(celda inicio;celda final): devuelve el valor mínimo de un rango nu­mérico.Porejemplo,paracalcularelvalormínimodeunaseriededatos:

7 4 9 11 34 25 17

Utilizaremoslasiguientesintaxis:=MIN(A1:G1),loquenosdevolveráelvalor4.Siguiendoelejemplode las rentas, la función=MIN(B3:B10)devolveráelvalor 16.000 €.

4 =MODA(celda inicio:celda final): permite calcular la moda (el valor que más se repite)enunaseriededatos.Porejemplo,paracalcular lamodadeunaserie de datos:

5 4 5 11 34 5 4

Utilizaremos la siguiente sintaxis: =MODA(A1:G1), lo que nos devolverá elvalor 5.

=C3+(C3*B4/100)

=MEDIA.GEOM(B4:B7)