Manual Excel Avanzado

INFORMÁTICA MODULO DE EXCEL II

PÁG. 1

FUNCIONES DE EXCEL

Las funciones son fórmulas predefinidas que ejecutan cálculos utilizando valores específicos, denominados argumentos, en un orden determinado o estructura. Existen funciones simples o complejas ESTRUCTURA DE UNA FUNCIÓN

= SUMA (A2:A10) 1

2

3

1. Estructura. La estructura de una función comienza por el signo igual (=), seguido por el nombre de la función, un paréntesis de apertura, los argumentos de la función separados por comas y un paréntesis de cierre. 2. Nombre de función. Para obtener una lista de funciones disponibles, haga clic en una celda y presione MAYÚSC+F3. 3. Argumentos. Los argumentos pueden ser números, texto, valores lógicos como VERDADERO o FALSO,

o referencias de celda.. Los argumentos

pueden ser también constantes, fórmulas u otras funciones. ACTIVAR ASISTENTE FUNCIONES 1. Para trabajar una función se da clic en el icono de Fx de la barra de formulas. 2. Clic en el menú INSERTAR, clic en FUNCIÓN y se despliega el asistente para funciones

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 2

CATEGORÍAS DE FUNCIONES El asistente para funciones despliega una clasificación de funciones

para

aplicar, a continuación se enumeran 1. BASE DE DATOS Microsoft Excel incluye funciones de hoja de cálculo que analizan los datos almacenados en listas o bases de datos. Cada una de estas funciones, denominadas colectivamente funciones BD, usa tres argumentos: 1.

Base de datos.

2.

Nombre de campo

3.

Criterios. CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 3

Base de datos Es el rango de celdas que compone la lista o base de datos. Nombre de campo Indica qué columna se utiliza en la función. Nombre de campo puede ser texto con el rótulo encerrado entre dobles comillas, como por ejemplo "Edad" o "Campo", o como un número 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 las condiciones especificadas. Puede utilizar cualquier rango en el argumento Criterios mientras éste incluya por lo menos un rótulo de columna y por lo menos una celda debajo del rótulo de columna que especifique una condición de columna.

SINTAXIS Las funciones de base de datos tienen la siguiente sintaxis

BDPROMEDIO (base de datos; nombre de campo; criterios)

FUNCIÓN

DESCRIPCIÓN

BDPROMEDIO

Devuelve el promedio de los valores de una columna de una lista o base de datos que coinciden con las condiciones especificadas.

BDCONTAR

Cuenta las celdas que contienen números en una base de datos

BDMAX

Devuelve el valor máximo de las entradas seleccionadas de la base de datos.

BDMIN

Devuelve el valor mínimo de las entradas seleccionadas de la base de datos

BDPRODUCTO Multiplica los valores de un campo determinado de registros de la base de datos que coinciden con los criterios CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 4

especificados BDSUMA

Suma los números de la columna del campo de los registros de la base de datos que coincidan con los criterios especificados

EJEMPLOS 1 2 3 4 5 6 7 8 9 10 11

A VESTIDO PANTALÓN CAMISA VESTIDO PANTALÓN CAMISA CHAQUETA PANTALÓN CAMISA PANTALÓN

B LARGO >70 >50 LARGO 120 70 80 110 50 130

C LARGO <130 <70 COSTO 120000 30000 45000 28000 20000 24000

1. CALCULAR CUANTOS PANTALONES TIENEN UN LARGO ENTRE 70 Y 130 MTS

=BDCONTAR(A5:C11,"LARGO",A1:C2) RTA: 2 2. CALCULAR EL COSTO DE LOS PANTALONES

=BDSUMA(A5:C11,"Beneficio",A1:A2) RTA =172.000

3. CALCULAR EL COSTO DE LOS PANTALONES CON LARGO ENTRE 70 Y 130 MTS

=BDSUMA(A5:C11,"COSTO",A1:C2) CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 5

RTA= 148.000 4. CALCULAR EL COSTO MAX DE PANTALONES Y CAMISAS

=BDMAX(A5:C11,"COSTO",A1:A3) RTA= 120.000 5. CALCULAR EL COSTO MÍNIMO ENTRE PANTALONES Y CAMISAS

=BDMIN(A5:E11,"COSTO",A1:A3) RTA = 20 6. CALCULAR EL COSTO TOTAL DE CAMISAS Y PANTALONES

=BDSUMA(A5:E11,"COSTO",A1:A3) RTA= 222.000

FECHA Y HORA FUNCIÓN

DESCRIPCIÓN

FECHA

Devuelve una fecha determinada

DIA

Convierte un número que representa una fecha en el día del mes correspondiente.

DIAS360

Calcula el número de días entre dos fechas basándose en un año de 360 días.

FECHA.MES

Devuelve el número que representa una fecha que es un número determinado de meses anterior o posterior a la fecha inicial.

FIN.MES

Devuelve el número correspondiente al último día del mes, que es un número determinado de meses anterior o posterior a la fecha inicial.

HORA

Convierte un número en la hora correspondiente.

MINUTO

Convierte un número en el minuto correspondiente.

MES

Convierte un número en el mes correspondiente.

DIAS.LAB

Devuelve el número de días laborables completos entre dos fechas.

AHORA

Devuelve el número de serie de la fecha y hora actuales.

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 6

SEGUNDO

Convierte un número en el segundo correspondiente.

NSHORA

Devuelve el numero de una hora determinada

HORANUMERO

Convierte una hora representada por texto en un número de serie.

HOY

Devuelve el número que representa la fecha actual.

DIASEM

Convierte un número en el día de la semana correspondiente.

NUM.DE.SEMANA

Convierte un número en un número que indica dónde cae la semana numéricamente dentro de un año.

DIA.LAB

Devuelve el número que representa una fecha que es determinado número de días laborables anterior o posterior a la fecha especificada.

AÑO

Convierte un número en el año correspondiente.

EJEMPLOS

1 2 3 4

A Año

B Mes

2008 Fórmula 01/01/2008

C Día 1

1

FUNCIÓN FECHA

=FECHA(A2,B2,C2) FUNCIÓN DIA A 1 2

B Fecha

24/12/2006

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 7

3 Fórmula 4

Descripción (Resultado) 24 Día de la fecha (24)

=DIA(A2) FUNCIÓN DIAS360 A 1 2 3 4

B

Fechas 01/01/2006 Fecha inicial 06/11/2006 Fecha Final Fórmula

5

Descripción (Resultado) 305 Número de días entre las dos fechas anteriores, basado en un año de 360 días

=DIAS360(A2,A3) FUNCIÓN HORA

A 1 2

B

03:30:30 a.m. Fórmula

3

Descripción (Resultado) 3 Hora de la primera hora (3)

- AHORA(): Síntaxis: =AHORA() Devuelve el número de serie de la fecha y hora actuales. - MES(): Síntaxis: =MES(núm_de_serie) Devuelve el mes, un número entero de 1 (enero) a 12 (diciembre), correspondiente a un número de serie. - DIA(): CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 8

Síntaxis: =DIA(núm_de_serie) Devuelve el día del mes, (un número de 1 a 31), correspondiente al número que representa una fecha.

FUNCIONES DE INGENIERIA BESSELI

BESSELJ

Devuelve la función Bessel modificada Devuelve la función Bessel Jn(x) In(x) BESSELK

BESSELY CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 9

Devuelve la función Bessel modificada Devuelve la función Bessel Yn(x) Kn(x) BIN.A.DEC Convierte decimal

BIN.A.HEX un

número

binario

en Convierte un hexadecimal

número

binario

en

BIN.A.OCT

COMPLEJO

Convierte un número binario en octal

Convierte coeficientes reales e imaginarios en un número complejo

CONVERTIR

DEC.A.BIN

Convierte un número de un sistema de Convierte un número decimal en medida a otro binario DEC.A.HEX Convierte un hexadecimal

DEC.A.OCT número

decimal

DELTA

en Convierte un número decimal en octal FUN.ERROR

Comprueba si dos valores son iguales. Devuelve la función de error FUN.ERROR.COMPL Devuelve la función complementaria HEX.A.BIN

MAYOR.O.IGUAL de

error Comprueba si un número es mayor que el valor de referencia HEX.A.DEC

Convierte un número hexadecimal en Convierte un número hexadecimal en binario decimal HEX.A.OCT

IM.ABS

Convierte un número hexadecimal en Devuelve el valor absoluto (módulo) de octal un número complejo IMAGINARIO

IM.ANGULO

Devuelve el coeficiente de la parte Devuelve el argumento theta, imaginaria de un número complejo ángulo expresado en radianes IM.CONJUGADA

IM.COS

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

un

INFORMÁTICA MODULO DE EXCEL II

PÁG. 10

Devuelve el conjugado complejo de un Devuelve el coseno de un número número complejo complejo IM.DIV

IM.EXP

Devuelve el cociente de dos números Devuelve el resultado de la función complejos exponencial de un número complejo IM.LN

IM.LOG10

Devuelve el logaritmo neperiano de un Devuelve el logaritmo en base 10 de número complejo un número complejo IM.LOG2

IM.POT

Devuelve el logaritmo en base 2 de un Devuelve el resultado de un número número complejo complejo elevado a una potencia entera IM.PRODUCT

IM.REAL

Devuelve el producto de dos números Devuelve el coeficiente real de un complejos número complejo IM.SENO

IM.RAIZ2

Devuelve el seno de un número Devuelve la raíz cuadrada de un complejo número complejo IM.SUSTR

IM.SUM

Devuelve la diferencia números complejos

entre

dos Devuelve la suma de dos números complejos

OCT.A.BIN

OCT.A.DEC

Convierte un número octal en binario

Convierte un número octal en decimal

OCT.A.HEX Convierte un hexadecimal

número

octal

en

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 11

FUNCIONES FINANCIERAS

PAGOINT Devuelve el pago de intereses de una inversión durante un período determinado PAGO Devuelve el pago periódico de una anualidad

EJEMPLOS CALCULAR CUOTAS FIJAS DE UN CRÉDITO

1 2 3 4 5

A INTERÉS TIEMPO CAPITAL CUOTA

B 6% 24 50000 $ -2.216,03

=PAGO(B1/12,B3,0,B4) CALCULAR EL INTERÉS PARA UN PERIODO DE TIEMPO 10% Interés anual 1 Período para el cual desea calcular el interés 3 Años del préstamo 8000 Valor actual del préstamo

Fórmula

Descripción (Resultado)

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 12

$ -22.41 Interés que se pagará el primer mes para un préstamo

=PAGOINT(A2/12,A3*3,A4,A5)

FUNCIÓN DE INFORMACIÓN

CELDA Devuelve información acerca del formato, la ubicación o el contenido de una celda CONTAR.BLANCO Cuenta el número de celdas en blanco dentro de un rango TIPO.DE.ERROR Devuelve un número correspondiente a un tipo de error INFO Devuelve información acerca del entorno operativo en uso ESBLANCO Devuelve el valor VERDADERO si el valor está en blanco ESERR Devuelve VERDADERO si el valor es cualquier valor de error excepto #N/A ESERROR Devuelve VERDADERO si el valor es cualquier valor de error

EJEMPLO CALCULAR LOS ESPACIOS VACÍOS DE LA BASE DE DATOS

NOMBRE MARIANA SUÁREZ STELLA SALAMANCA CARLOS CASTRO ANDREA PÉREZ CAROLINA PÁEZ

VALOR CUOTA 56000 45000 45500 45670

=CONTAR.BLANCO(A9:B13) CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 13

RTA = 1

FUNCIÓN LÓGICAS

Y

Devuelve VERDADERO si todos sus argumentos son verdaderos

FALSO

Devuelve el valor lógico FALSO

SI

Especifica un texto lógico para ejecutar

NO

Invierte la lógica de sus argumentos

O

Devuelve VERDADERO si algún argumento es VERDADERO

VERDADERO

Devuelve el valor lógico VERDADERO

EJEMPLO PRESUPUESTO EJECUTADO 1500 500 500 Fórmula Presupuesto excedido

PRESUPUESTO PLANEADO 900 900 925 Descripción (Resultado) Comprueba si la primera fila sobrepasa el presupuesto (Presupuesto excedido)

=SI(A2>B2,"Presupuesto excedido","Aceptar")

FUNCIONES BÚSQUEDA Y REFERENCIA CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 14

Devuelve una referencia como texto a una única celda en la hoja de cálculo AREAS Devuelve el número de áreas de una referencia ELEGIR Elige un valor en una lista de valores COLUMNA Devuelve el número de columna de una referencia COLUMNAS Devuelve el número de columnas de una referencia BUSCARH Busca en la fila superior de una matriz y devuelve el valor de la celda indicada HIPERVINCULO Crea un acceso directo o un salto que abre un documento almacenado en un servidor de red, en una intranet o en Internet INDICE Utiliza un índice para elegir un valor a partir de una referencia o matriz INDIRECTO Devuelve una referencia indicada por un valor de texto BUSCAR Busca los valores en un vector o matriz COINCIDIR Busca los valores en una referencia o matriz FILA Devuelve el número de fila de una referencia FILAS Devuelve el número de filas de una referencia CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 15

EJEMPLO - BUSCARV(): Síntaxis: =BUSCARV(Celda;Rango;Columna) Busca el valor de una celda en un rango de celdas y retornará el contenido de n columnas a su derecha. Hay que observar detenidamente los tres argumentos que nos pide la función =BUSCARV(), primero la celda donde estará lo que intentamos buscar, luego el rango donde hemos de buscarlo y por último el número de columna que queremos mostrar Ejemplo: Supongamos que tenemos una lista: Observa que en la parte superior se busca el resultado de CODIGO, DESCRIPCIÓN Y TOTAL . La fórmula es la siguiente =BUSCARV(B1;A5:C13;2) A B 1 CODIGO A5 2 DESCRIPCION CHILE 3 TOTAL 45 4 5 CODIGO DESCRIPCION TOTAL 6 A1 COLOMBIA 120 7 A2 ECUADOR 20 8 A3 PERU 340 9 A4 URUGUAY 400 10 A5 CHILE 45 11 A6 MEXICO 67 12 A7 VENEZUELA 89 13 A8 ARGENTINA 90

Nota: Si la lista está escrita en otra hoja de cálculo, llamada por ejemplo Hoja1, entonces la fórmula sería la siguiente: =BUSCARV(C1;Hoja1!A7:C15;2)

EJEMPLO 2 CODIGO 4 5 6 7 8

NOMBRE CARLOS ANDRES FELIPE MARIO SERGIO

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

ANDRES

PÁG. 16

Busca 5,00 en la columna A y devuelve el valor de la columna B que está en la misma fila (ANDRES)

FUNCIONES MATEMÁTICAS Y TRIGONOMETRICAS

CONTAR.SI Cuenta el número de celdas que no están en blanco dentro de un rango que coincida con los criterios especificados GRADOS Convierte radianes en grados EXP Devuelve e elevado a la potencia de un número determinado FACT Devuelve el factorial de un número M.C.D Devuelve el máximo común divisor ENTERO Redondea un número hasta el entero inferior más próximo M.C.M Devuelve el mínimo común múltiplo LN Devuelve el logaritmo neperiano de un número LOG Devuelve el logaritmo de un número en una base especificada LOG10 Devuelve el logaritmo en base 10 de un número RESIDUO Devuelve el resto de la división POTENCIA Devuelve el resultado de elevar el argumento número a una potencia PRODUCTO Multiplica sus argumentos COCIENTE Devuelve la parte entera de una división RADIANES Convierte grados en radianes ALEATORIO Devuelve un número aleatorio entre 0 y 1 NUMERO.ROMANO Convierte un número arábigo en número romano con formato de texto CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 17

REDONDEAR Redondea un número a un número especificado de dígitos REDONDEAR.MENOS Redondea un número hacia abajo, hacia cero REDONDEAR.MAS Redondea un número hacia arriba, en dirección contraria a cero RAIZ Devuelve la raíz cuadrada de un número SUBTOTALES Devuelve un subtotal en una lista o base de datos SUMA Suma sus argumentos SUMAR.SI Suma las celdas en el rango que coinciden con el argumento criterio SUMAPRODUCTO Devuelve la suma de los productos de los componentes de la matriz correspondiente TAN Devuelve la tangente de un número TANH Devuelve la tangente hiperbólica de un número TRUNCAR Trunca un número y lo convierte en entero

EJEMPLO = NUMERO.ROMANO(20,1) RTA = XX

=REDONDEAR(4.257,2) RTA= 4.26 - RAIZ(): Síntaxis: =RAIZ(número) Devuelve la raíz cuadrada de un número. Ejemplo: La raíz cuadrada de 64 sería 8 CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 18

FUNCIONES ESTADÍSTICAS

PROMEDIO Devuelve el promedio de los argumentos CONTAR Cuenta cuántos números hay en la lista de argumentos CONTARA Cuenta cuántos valores hay en la lista de argumentos MAX Devuelve el valor máximo de una lista de argumentos MAXA Devuelve el valor máximo de una lista de argumentos, incluidos números, texto y valores lógicos MEDIANA Devuelve la mediana de los números dados MIN Devuelve el valor mínimo de una lista de argumentos MODA: Valor que más se repite en un rango.

EJEMPLO

- MODA(): Síntaxis: =MODA(Números) Ejemplo:

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 19

- CONTAR() y CONTARA(): Síntaxis: =CONTAR(Rango) =CONTARA(Rango) El primero cuenta las veces que aparece un elemento numérico en una lista y el segundo elementos de texto. - MEDIANA(): Síntaxis: =MEDIANA(Números) Ejemplo: CALCULAR EL PROMEDIO DE LAS SIGUIENTES NOTAS NOTAS 4.5 5.0 4.2 3.8 3.7 4.24

=PROMEDIO(D2:D6)

FUNCIONES DE TEXTO Y DATOS

CONCATENAR Une varios elementos de texto en uno solo MINUSC

Convierte texto en minúsculas

MAYUSC

Convierte el texto en mayúsculas

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 20

sena

=MAYUSC(A15) RTA= SENA FUNCIONES LOGICAS - Y(): Síntaxis: =Y(valor_lógico1;valor_lógico2;...) Devuelve VERDADERO si todos los argumentos son VERDADEROS; devuelve FALSO si algún argumento es FALSO. - SI(): Síntaxis: =SI(Condición;Verdadero;Falso) Es una de las funciones más potentes que tiene Excel. Esta función comprueba si se cumple una condición. Si ésta se cumple, da como resultado VERDADERO. Si la condición no se cumple, da como resultado FALSO. Esta es la forma más simple de representar esta función, porque se puede complicar más. Ejemplo: Vamos a hacer que la siguiente factura, nos haga un descuento del 10% sólo en el caso de cobrar al contado. La fórmula se colocará en la celda D5 y será la siguiente: =SI(A7=”Contado”;D4*10%;0;)

Esta fórmula mirará si en la casilla A7 (celda gris) existe la palabra Contado. En tal caso, ejecutará una fórmula (10% de descuento), en caso contrario, colocará simplemente un cero en la celda D5, es decir, no realizará ningún cálculo. Observa el resultado:

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 21

En este ejemplo se observa como la función =SI, debía cumplir una condición, que era la de controlar si en una celda determinada había un texto. Pero, ¿qué pasaría si se tuviesen que cumplir más de una condición? Supongamos que la función =SI debe tener en cuenta dos condiciones. Estas dos condiciones podrían ser: - Que la función =SI hiciese algo sólo si se tuvieran que cumplir las dos - Que la función =SI hiciese algo si se cumpliese una de las dos Controlaremos una u otra forma con dos operadores lógicos: el Y y el O La sintaxis de la orden sería la siguiente: =SI(Y(Condición1;Condición2.............. Caso en el que se deban cumplir todas las condiciones. =SI(O(Condición1;Condición2............. Caso que se deba cumplir sólo una En el siguiente ejemplo se ha diseñado una hoja de control de flujo de caja, en el caso, de que debamos controlar entradas y salidas además del saldo.

En las columnas C y D introducimos las cantidades según sea un gasto (extracción) o un ingreso (depósito). Sería muy fácil colocar en la celda E5 (saldo) la siguiente fórmula: =E4+C5-D4, que calcularía el saldo anterior, más la cantidad de la celda del depósito, menos la cantidad de la celda de la extracción. El problema viene cuando copiamos la fórmula varias celdas hacia abajo. A partir de la celda del último saldo, siempre nos mostraría el saldo CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 22

anterior, hubiéramos o no, introducido cantidades en las celdas de depósito o extracción. Observa el resultado que se obtendría:

En la ilustración superior utilizamos la función =SI, la cual ha de controlar que se cumplan dos condiciones: que introduzcamos una cantidad en la celda del depósito O de la extracción. Sólo en uno de los dos casos se ejecutará la función. De esta forma, si todavía no hemos introducido nada en las celdas de la izquierda, la función no se ejecutará. Observa a continuación las partes de la fórmula: =SI(O La letra O controla que se cumpla una de las dos condiciones. (C5>0; Primera condición: que en C5 haya algo mayor de cero, es decir, un número positivo. D5>0) Separada por punto y coma, la segunda condición controla lo mismo: que en D5 haya algún número. ;E4+C5-D5 caso de cumplirse una de las dos condiciones, se ejecutará esta fórmula. ;””) caso de no cumplirse ninguna condición, no saldrá nada. Las dos comillas quieren decir caracter nulo.

FILTROS Una lista filtrada muestra sólo las filas que cumplen el criterio que se especifique para una columna. Microsoft Excel proporciona dos comandos para aplicar filtros a las listas: •

Autofiltro, que incluye filtrar por selección, para criterios simples CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II •

PÁG. 23

Filtro avanzado, para criterios más complejos

A diferencia de ordenar, el filtrado no reorganiza las listas. El filtrado oculta temporalmente las filas que no desee mostrar. Cuando Excel filtra filas, puede modificar, aplicar formato, representar en gráficos e imprimir el subconjunto de la lista sin necesidad de reorganizarlo u ordenarlo. AUTOFILTRO DE UNA LISTA 1. Haga clic en la celda de la lista que desea filtrar. 2.

En el menú Datos, seleccione Filtro y haga clic en Autofiltro.

3. Se despliegan los filtros en cada columna representados por una flecha.

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 24

4. Luego en las opciones desplegadas se selecciona el tipo de filtro a realizar.

FILTRAR POR EL NÚMERO MENOR O MAYOR 1.

Haga clic en la flecha de la columna que contiene los números y haga clic en (Las 10 más).

2.

En el cuadro de la izquierda, haga clic en superiores o inferiores. 3.En el cuadro del medio, escriba un número. CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II 4.

PÁG. 25

En el cuadro de la derecha, haga clic en elementos.

FILTRAR UNA LISTA EN BUSCA DE FILAS QUE CONTENGAN UN TEXTO ESPECÍFICO 1.

Haga clic en la flecha de la columna que contiene los números y haga clic en (Personalizar).

2.

En el cuadro de la izquierda, haga clic en igual o en no igual, contiene o no contiene

3. Introduzca el texto que desee en el cuadro de la derecha. Puede utilizar los siguientes caracteres comodín pueden usarse como criterios de comparación para filtros, así como para buscar y reemplazar contenido.

UTILICE ? (signo interrogación) * (asterisco)

PARA BUSCAR de Un único Por ejemplo, Gr?cia buscará "Gracia" y "Grecia"

carácter

Reemplace el texto a buscar por *.

~ (tilde) seguida de Un signo de interrogación, un asterisco o una tilde. ?, *, o ~ Por ejemplo, fer? buscará palabras que empiecen con fer CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 26

y terminen con cualquier tipo de letra.

FILTRAR POR CELDAS VACÍAS O CELDAS NO VACÍAS

Haga clic en la flecha de la columna que contiene los datos y, a continuación, elija (Vacías) o (No vacías). Nota Las opciones Vacías y No vacías sólo están disponibles si la columna que desea filtrar contiene una celda vacía.

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 27

FILTRAR POR NÚMEROS MAYORES O MENORES QUE OTRO NÚMERO 1.

Haga clic en la flecha de la columna que contiene los números y haga clic en (Personalizar).

2.

En el cuadro de la izquierda, haga clic en es mayor que, es menor que, es mayor o igual que o es menor o igual que.

3. En el cuadro de la derecha, escriba un número.

4. Haga clic en Aceptar. FILTRAR POR EL COMIENZO O EL FINAL DE UNA CADENA DE TEXTO 1.

Haga clic en la flecha de la columna que contiene los datos y haga clic en (Personalizar).

2.

En el cuadro de la izquierda, haga clic en comienza por o no comienza por, o en termina con o no termina con.

3. Introduzca el texto que desee en el cuadro de la derecha. 4. Si necesita buscar valores de texto que comparten algunos caracteres pero no otros, utilice un carácter comodín.

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 28

FILTRO AVANZADO

El comando Filtro avanzado permite filtrar una lista en su lugar, como el comando Autofiltro, pero no muestra listas desplegables para las columnas. En lugar de ello, tiene que escribir los criterios según los cuales desea filtrar los datos en un rango de criterios independiente situado sobre la lista. Un rango de criterios permite filtrar criterios más complejos.

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 29

VARIAS CONDICIONES EN UNA SOLA COLUMNA Si incluye dos o más condiciones en una sola columna, escriba los criterios en filas independientes, una directamente bajo otra. Por ejemplo, el siguiente rango de criterios presenta las filas que contienen "MARIA NELCY" "FRANCY" NOMBRE MARIA NELCY FRANCY UNA CONDICIÓN EN DOS O MÁS COLUMNAS Para buscar datos que cumplan una condición en dos o más columnas, introduzca todos los criterios en la misma fila del rango de criterios. Por ejemplo, el siguiente rango de criterios muestra todas las filas que contienen "POWER" y NOTA > 4.0. POWER

NOTA >4.0

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 30

QUITAR FILTROS •

Para quitar un filtro aplicado a una columna de una lista, haga clic en la flecha azul que aparece junto a la columna y, a continuación, haga clic en Todas.

•

Para quitar filtros aplicados a todas las columnas de una lista, seleccione Filtro en el menú Datos y haga clic en Mostrar todo.

•

Para quitar las flechas de filtro de una lista, seleccione Filtro en el menú Datos y haga clic en Autofiltro. Se da clic sobre la opción seleccionada.

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 31

GRÁFICOS Los gráficos son visualmente llamativos y facilitan a los usuarios las visualización de comparaciones, tramas y tendencias de los datos. Por ejemplo, en lugar de analizar varias columnas de números de la hoja de cálculo, puede ver en seguida si las ventas están disminuyendo o aumentando en trimestres sucesivos, o cómo se están comportando las ventas con respecto a las previsiones.

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 32

Un gráfico está vinculado a los datos de la hoja de cálculo a partir de la que se generan y se actualiza automáticamente al cambiar estos datos.

Marcador de datos

Cada marcador de datos representa un número de la

hoja de cálculo. Los marcadores de datos que tengan la misma trama representan una serie de datos. En el ejemplo anterior, el marcador de datos situado en el extremo derecho representa el valor real del segundo trimestre de 99.

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 33

Línea de cuadrícula principal Microsoft Excel crea los valores de los ejes a partir de los datos de la hoja de cálculo. Observe que los valores de los ejes en el ejemplo anterior estaban comprendidos en el rango que va de 0 a 120, que abarca todos los valores de la hoja de cálculo. Las líneas de división principales marcan los intervalos principales del eje. También es posible ver las líneas de división secundarias del gráfico, que marcan los intervalos existentes entre los intervalos principales. Nombres de categoría Excel utiliza los títulos de las columnas o de las filas de datos de la hoja de cálculo para asignar nombres a los ejes de las categorías. En el ejemplo anterior, los títulos de las filas TRIM1 y TRIM2 aparecen como nombres de ejes de categorías. Nombres de series de datos del gráfico Excel también utiliza los títulos de las columnas o de las filas de datos de la hoja de cálculo para los nombres de las series. Estos nombres aparecen en la leyenda del gráfico. En el ejemplo anterior, los títulos de las filas Proyectado y Real aparecen como nombres de series.

PERSONALIZAR GRÁFICOS Para crear un grafico con 3d y modificar las series de datos se utiliza el paso dos del asistente para graficos, a continuación se muestra un ejemplo Al seleccionar el paso dos seleccionar la opción de serie, se ubican las casillas de NOMBRES,VALORES Y ROTULO DE EJES DE X.

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

El Resultado será el Siguiente

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

PÁG. 34

INFORMÁTICA MODULO DE EXCEL II

PÁG. 35

GRÁFICOS COMBINADOS El asistente para gráficos tiene una opción que permite combinar los diferentes tipos de gráficos, en el paso 1 del asistente en LA OPCIÓN TIPOS PERSONALIZADOS seleccionar los tipos de gráficos combinados.

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

El Resultado será el siguiente

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

PÁG. 36

INFORMÁTICA MODULO DE EXCEL II

PÁG. 37

TABLAS DINÁMICAS Comprende una serie de Datos agrupados en forma de resumen que agrupan aspectos concretos de una información global. Es decir con una Tabla Dinámica se pueden hacer resúmenes de una Base de Datos, utilizándose para, promediar, o totalizar datos. Debe ser muy importante la cantidad de información a manejar para que el uso de la tabla dinámica se justifique. Para su utilización, se debe recurrir a Menú- Datos- Informe de Tablas y gráficos dinámicos

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 38

Partiendo de una Planilla confeccionada, tomando como ejemplo la circulación de vehículos a través de una estación de peaje, se trata de aplicar sobre la misma un principio de ordenamiento a través de una herramienta de Excel, Tablas Dinámicas A 1 2 3 4 5 6 7 8

Mes Enero Enero Enero Enero Enero Enero Enero

B Semana

C D Vehículo Cantidad 1Auto 105.000 1Camión 1.050 1Ómnibus 1.575 1Camioneta 2.100 1Moto 583 2Auto 120.750 2Camión 1.208

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II 9 10 11 12 13 14 15 16 17 18 19 20 21

Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero Enero

2Ómnibus 2Camioneta 2Moto 3Auto 3Camión 3Ómnibus 3Camioneta 3Moto 4Auto 4Camión 4Ómnibus 4Camioneta 4Moto

PÁG. 39

1.411 2.015 485 122.350 1.124 1.685 2.247 630 99.000 990 1.485 1.980 544

Crear una Tabla Dinámica La creación de una tabla dinámica se realiza a través del asistente y en varios pasos. 1- Poner el cursor en cualquier celda de la Tabla 2- Tomar la opción Menú-Datos- Informe de tablas y gráficos dinámicos Aparece el cuadro

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

1° Paso a) ¿Dónde están los datos que desea analizar? Marcar opción : Lista o base de datos de Microsoft Excel b) ¿ Que tipo de informe desea crear? Marcar la opción: Tabla dinámica c) clic en Siguiente

2° Paso Rango de Datos a) Seleccionar el rango de la tabla, incluyendo la fila de titulo b) Siguiente

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

PÁG. 40

INFORMÁTICA MODULO DE EXCEL II

3° Paso

Ubicación de la Tabla

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

PÁG. 41

INFORMÁTICA MODULO DE EXCEL II

PÁG. 42

a) Se adopto ubicar la tabla en la misma hoja de calculo , determinando la celda de comienzo de ejecución de la misma b) Se indica , clic en Diseño , comienza el momento de diseñar la tabla

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

a) b) c) d)

PÁG. 43

Arrastrar el cuadrito de Semana a Columna Arrastrar el cuadrito de Vehículo a Fila Arrastrar el cuadrito de Cantidad a Datos Aceptar.

Aparece la tabla dinámica diseñada, acompañada por una barra de herramientas especial que permite filtrar datos por despliegue de las mismas. Suma de Cantidad Vehículo Auto Camión Camioneta Moto Ómnibus Total general

Semana 1 105.000 1.050 2.100 583 1.575 110.308

2 120.750 1.208 2.015 485 1.411 125.869

3 122.350 1.124 2.247 630 1.685 128.036

4 99.000 990 1.980 544 1.485 103.999

Total general 447.100 4.371 8.342 2.242 6.157 468.212

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

Aplicar Filtros a la Tabla

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

PÁG. 44

INFORMÁTICA MODULO DE EXCEL II

Suma de Cantidad Vehículo Auto Ómnibus Total general

PÁG. 45

Semana 3 122.350 1.685 124.035

4 99.000 1.485 100.485

Total general 221.350 3.170 224.520

Resultado de la tabla al aplicarle los filtros correspondientes: En primer lugar se despliega la lista en semana y se desactiva la 1° y 2° En segundo lugar se despliega la lista en vehículo y se desactivan las opciones –Camión- Camioneta- Moto Cambiar el diseño de la tabla Siguiendo el mismo procedimiento se puede cambiar el diseño de la tabla, por ejemplo CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]

INFORMÁTICA MODULO DE EXCEL II

PÁG. 46

e) Arrastrar el cuadrito de Vehículo a Columna f) Arrastrar el cuadrito de Semana a Fila g) Arrastrar el cuadrito de Cantidad a Datos h) Aceptar. Obteniéndose el siguiente el siguiente resultado.

Suma de Cantidad Semana

Total general

Vehículo Auto Camión Camioneta Moto 1 105000 1050 2100 2 120750 1207,5 2015 3 122350 1123,5 2247 4 99000 990 1980 447100 4371 8342

Ómnibus 583 485 630 544 2242

1575 1411,25 1685,25 1485 6156,5

Total general 110308 125868,75 128035,75 103999 468211,5

Aplicar Filtros a la Tabla Siguiendo con el mismo criterio, aplicando en Vehículos se dejan activados –Auto-Camión En Semana se deja activada solamente la 1° y 3° Semana. De lo ejecutado surge el siguiente resultado. Suma de Cantidad Vehículo Semana Auto Camión Total general 1 105000 1050 106050 3 122350 1123,5 123473,5 Total general 227350 2173,5 229523,5

CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS REGIONAL SANTANDER ING. KELY ALEJANDRA QUIROS DUARTE [email protected] [email protected]