manual excel avanzado
TRANSCRIPT
INFORMÁTICAMODULO 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
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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
= SUMA (A2:A10)
12
3
INFORMÁTICAMODULO 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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
A B C1 VESTIDO LARGO LARGO2 PANTALÓN >70 <1303 CAMISA >50 <70
4 5 VESTIDO LARGO COSTO6 PANTALÓN 120 1200007 CAMISA 70 300008 CHAQUETA 80 450009 PANTALÓN 110 2800010 CAMISA 50 2000011 PANTALÓN 130 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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
FUNCIÓN FECHA
=FECHA(A2,B2,C2)
FUNCIÓN DIA
A B1 Fecha2 24/12/2006
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
A B C1 Año Mes Día2 2008 1 13 Fórmula 4 01/01/2008
INFORMÁTICAMODULO DE EXCEL II
PÁG. 7
3Fórmula Descripción (Resultado)
4 24 Día de la fecha (24)
=DIA(A2)
FUNCIÓN DIAS360
A B1 Fechas2 01/01/2006 Fecha inicial3 06/11/2006 Fecha Final4
Fórmula Descripción (Resultado)
5 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 B1 03:30:30 a.m.
2 Fórmula Descripción (Resultado)
3 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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
Devuelve la función Bessel modificada In(x)
BESSELJ
Devuelve la función Bessel Jn(x)
BESSELK BESSELY CENTRO SEVICIOS EMPRESARIALES Y TURISTICOS
REGIONAL SANTANDERING. KELY ALEJANDRA QUIROS DUARTE
INFORMÁTICAMODULO DE EXCEL II
PÁG. 9
Devuelve la función Bessel modificada Kn(x)
Devuelve la función Bessel Yn(x)
BIN.A.DEC
Convierte un número binario en decimal
BIN.A.HEX
Convierte un número binario en hexadecimal
BIN.A.OCT
Convierte un número binario en octal
COMPLEJO
Convierte coeficientes reales e imaginarios en un número complejo
CONVERTIR
Convierte un número de un sistema de medida a otro
DEC.A.BIN
Convierte un número decimal en binario
DEC.A.HEX
Convierte un número decimal en hexadecimal
DEC.A.OCT
Convierte un número decimal en octal
DELTA
Comprueba si dos valores son iguales.
FUN.ERROR
Devuelve la función de error
FUN.ERROR.COMPL
Devuelve la función de error complementaria
MAYOR.O.IGUAL
Comprueba si un número es mayor que el valor de referencia
HEX.A.BIN
Convierte un número hexadecimal en binario
HEX.A.DEC
Convierte un número hexadecimal en decimal
HEX.A.OCT
Convierte un número hexadecimal en octal
IM.ABS
Devuelve el valor absoluto (módulo) de un número complejo
IMAGINARIO
Devuelve el coeficiente de la parte imaginaria de un número complejo
IM.ANGULO
Devuelve el argumento theta, un ángulo expresado en radianes
IM.CONJUGADA IM.COS
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 10
Devuelve el conjugado complejo de un número complejo
Devuelve el coseno de un número complejo
IM.DIV
Devuelve el cociente de dos números complejos
IM.EXP
Devuelve el resultado de la función exponencial de un número complejo
IM.LN
Devuelve el logaritmo neperiano de un número complejo
IM.LOG10
Devuelve el logaritmo en base 10 de un número complejo
IM.LOG2
Devuelve el logaritmo en base 2 de un número complejo
IM.POT
Devuelve el resultado de un número complejo elevado a una potencia entera
IM.PRODUCT
Devuelve el producto de dos números complejos
IM.REAL
Devuelve el coeficiente real de un número complejo
IM.SENO
Devuelve el seno de un número complejo
IM.RAIZ2
Devuelve la raíz cuadrada de un número complejo
IM.SUSTR
Devuelve la diferencia entre dos números complejos
IM.SUM
Devuelve la suma de dos números complejos
OCT.A.BIN
Convierte un número octal en binario
OCT.A.DEC
Convierte un número octal en decimal
OCT.A.HEX
Convierte un número octal en hexadecimal
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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
A B1 INTERÉS 6%2 TIEMPO 243 CAPITAL 500004 5 CUOTA $ -2.216,03
=PAGO(B1/12,B3,0,B4)
CALCULAR EL INTERÉS PARA UN PERIODO DE TIEMPO
10% Interés anual1 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)
$ -22.41 Interés que se pagará el primer mes para un préstamo
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 12
=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 VALOR CUOTAMARIANA SUÁREZ 56000STELLA SALAMANCA 45000CARLOS CASTRO 45500ANDREA PÉREZ CAROLINA PÁEZ 45670
=CONTAR.BLANCO(A9:B13)
RTA = 1
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 13
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
PRESUPUESTO PLANEADO
1500 900500 900500 925
FórmulaDescripción (Resultado)
Presupuesto excedido Comprueba si la primera fila sobrepasa el presupuesto (Presupuesto excedido)
=SI(A2>B2,"Presupuesto excedido","Aceptar")
FUNCIONES BÚSQUEDA Y REFERENCIA
Devuelve una referencia como texto a una única celda en la hoja de cálculo
AREAS
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 14
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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 A52 DESCRIPCION CHILE3 TOTAL 4545 CODIGO DESCRIPCION TOTAL6 A1 COLOMBIA 1207 A2 ECUADOR 208 A3 PERU 3409 A4 URUGUAY 400
10 A5 CHILE 4511 A6 MEXICO 6712 A7 VENEZUELA 8913 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 NOMBRE4 CARLOS5 ANDRES6 FELIPE7 MARIO8 SERGIO
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 16
ANDRES 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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
NOTAS4.55.04.23.83.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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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.
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 25
3.En el cuadro del medio, escriba un número.
4.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.
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 26
UTILICE PARA BUSCAR
? (signo de interrogación)
Un único carácterPor ejemplo, Gr?cia buscará "Gracia" y "Grecia"
* (asterisco) Reemplace el texto a buscar por *.
~ (tilde) seguida de ?, *, o ~
Un signo de interrogación, un asterisco o una tilde.Por ejemplo, fer? buscará palabras que empiecen con fer y terminen con cualquier tipo de letra.
FILTRAR POR CELDAS VACÍAS O CELDAS NO VACÍAS
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 27
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.
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.
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 28
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.
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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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.
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 30
POWER NOTA>4.0
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.
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 31
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 Datos y haga clic en Autofiltro. Se da clic sobre la opción seleccionada.
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 32
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.
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.
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 33
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.
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
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 34
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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 35
El Resultado será el Siguiente
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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 36
El Resultado será el siguiente
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 37
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 38
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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 39
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 B C D
1 Mes Semana Vehículo Cantidad
2 Enero 1Auto 105.000
3 Enero 1Camión 1.050
4 Enero 1Ómnibus 1.575
5 Enero 1Camioneta 2.100
6 Enero 1Moto 583
7 Enero 2Auto 120.750
8 Enero 2Camión 1.208
9 Enero 2Ómnibus 1.411
10 Enero 2Camioneta 2.015
11 Enero 2Moto 485
12 Enero 3Auto 122.350
13 Enero 3Camión 1.124
14 Enero 3Ómnibus 1.685
15 Enero 3Camioneta 2.247
16 Enero 3Moto 630
17 Enero 4Auto 99.000
18 Enero 4Camión 990
19 Enero 4Ómnibus 1.485
20 Enero 4Camioneta 1.980
21 Enero 4Moto 544
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 40
Crear una Tabla DinámicaLa 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 Tabla2- Tomar la opción Menú-Datos- Informe de tablas y gráficos
dinámicosAparece el cuadro
1° Pasoa) ¿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ámicac) clic en Siguiente
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 41
2° Paso Rango de Datosa) Seleccionar el rango de la tabla, incluyendo la fila de titulob) Siguiente
3° Paso Ubicación de la Tabla
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 43
a) Arrastrar el cuadrito de Semana a Columna b) Arrastrar el cuadrito de Vehículo a Fila c) Arrastrar el cuadrito de Cantidad a Datosd) 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 Semana Vehículo 1 2 3 4 Total general Auto 105.000 120.750 122.350 99.000 447.100 Camión 1.050 1.208 1.124 990 4.371 Camioneta 2.100 2.015 2.247 1.980 8.342 Moto 583 485 630 544 2.242 Ómnibus 1.575 1.411 1.685 1.485 6.157 Total general 110.308 125.869 128.036 103.999 468.212
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 44
Aplicar Filtros a la Tabla
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO DE EXCEL II
PÁG. 45
Suma de Cantidad Semana Vehículo 3 4 Total general Auto 122.350 99.000 221.350 Ómnibus 1.685 1.485 3.170 Total general 124.035 100.485 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 TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]
INFORMÁTICAMODULO 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 Datosh) Aceptar.Obteniéndose el siguiente el siguiente resultado.
Suma de Cantidad Vehículo Semana Auto Camión Camioneta Moto Ómnibus Total general
1 105000 1050 2100 583 1575 1103082 120750 1207,5 2015 485 1411,25 125868,753 122350 1123,5 2247 630 1685,25 128035,754 99000 990 1980 544 1485 103999
Total general 447100 4371 8342 2242 6156,5 468211,5
Aplicar Filtros a la Tabla
Siguiendo con el mismo criterio, aplicando en Vehículos se dejan activados –Auto-CamiónEn 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 1060503 122350 1123,5 123473,5
Total general 227350 2173,5 229523,5
CENTRO SEVICIOS EMPRESARIALES Y TURISTICOSREGIONAL SANTANDER
ING. KELY ALEJANDRA QUIROS [email protected] [email protected]