Funciones con condicion

CONTAR.SI: Cuenta la cantidad de celdas de un rango que coinciden con la condición.

Sintaxis:
=CONTAR.SI(RANGO, CRITERIO)

Ejemplo

La formulara que colocaríamos en la celda D15 seria =CONTAR.SI(C3:C13,"Sistemas")

SUMAR.SI: Suma las celdas que cumplen determinado criterio o condición.

Sintaxis:

=SUMAR.SI(RANGO,CRITERIO,RANGO_SUMA)

Rango: Celdas que contienen los datos donde actuara el criterio.

Criterio: Es la condición que se debe cumplir para que se proceda a sumar.

Rango_suma: Es el rango que contiene los daros que se sumaran.

La formula que se coloca en la celda D15 es =SUMAR.SI(C3:C13,"Sistemas",D3:D13)

Tablas Dinamicas

Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte.

Crear una tabla dinámica

Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la nueva tabla dinámica.

Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo Tablas de la ficha Insertar.

Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás ajustar el rango de datos que se considerará en la tabla dinámica. Veremos que Excel a seleccionado el cuadro de datos para la tabla dinámica.

En este mismo cuadro de diálogo se puede elegir si se desea colocar la tabla dinámica en una nueva hoja de Excel o en una ya existente.  Haz clic en el botón Aceptar y se creará la nueva tabla dinámica.

Excel agregará en la parte izquierda del libro la tabla dinámica y en la parte derecha la lista de campos. Esta lista de campos está dividida en dos secciones, primero la lista de todos los campos de los cuales podremos elegir y por debajo una zona a donde arrastraremos los campos que darán forma al reporte ya sea como columna, fila, valor o como un filtro.

De manera predeterminada Excel aplica la función SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la siguiente:

Utilizando una tabla dinámica fue posible crear un reporte de una manera fácil y sin la necesidad de utilizar fórmulas. Pronto veremos cómo se pueden elaborar tablas dinámicas más complejas que permitirán realizar un análisis profundo de la información.

Cambiar la Función
Se podrá cambiar la función predeterminada (Suma) por otra función de acuerdo a la información que se requiere. Debemos hace lo siguiente:
Hacer clic sobre el campo correspondiente dentro del área Valores. 

Del menú mostrado debes seleccionar la opción Configuración de campo de valor.

Se mostrará el cuadro de diálogo Configuración de campo de valor.

Seleccionar la función para realizar el calculo y luego Clic en Aceptar.

Funciones de Texto

Las funciones de texto en Excel permiten concatenar cadenas de caracteres, remover los espacios en blanco, reemplazar ciertos caracteres por otros y muchas cosas más que te permitirán manipular las cadenas de texto para obtener otros datos.

Función Izquierda: Devuelve caracteres a la izquierda de una cadena de texto de acuerdo a un numero de caracteres especificado.

Sintaxis
=IZQUIERDA(Texto,[Num Caracteres])

RES=  Exc

=IZQUIERDA(A1,2)    RES=   Ex

=IZQUIERDA(A1)       RES=   E

En este ultimo caso si se omite el argumento [Num Caracteres], Excel tomara por defecto el valor 1, y como resultado tendremos el primer carácter del texto.

Debemos recordar que los símbolos [ ], significa que es opcional, se puede o no utilizar.

Función Derecha: Devuelve caracteres a la derecha de una cadena de caracteres de texto de acuerdo a un numero de caracteres especificado.

Sintaxis
=DERECHA(Texto,[Num Caracteres])

RES=  cel

=DERECHA(A1,2)    RES=   el
=DERECHA(A1)       RES=   l

Al omitir el argumento [Num Caracteres], Excel tomara como valor 1 y el resultado sera el primer carácter del lado derecho del texto.

Función Encontrar: Devuelve la posición de un texto dentro de otro texto.

Sintaxis
=ENCONTRAR(Texto buscado,Dentro de texto,[Numero inicial])

• Texto buscado: Texto que se quiere buscar.
• Dentro de texto: Cadena de texto donde se va a buscar.
• Numero inicial. Es opcional, El numero de carácter donde se inicia la búsqueda, si se omite se iniciara desde la primera posición.

En el presente caso se desea saber la posición del espacio.

RES= 6

Debemos tener presente: Si se quiere la posición de la segunda "l", el tercer argumento [Numero inicial] debe ser la siguiente posición de la primera "l", osea 6 y obtendremos como resultado la posición de la segunda "l", Resultado 11.

Función Extrae: Extrae caracteres que pertenecen a una cadena de texto, se debe proporcionar el numero de caracteres a extraer y el punto de inicio.

Sintaxis
=EXTRAE(Texto, Posición Inicial, Numero Caracteres)

Texto: Cadena de texto donde se tomaran los caracteres.
Posición Inicial: Numero de posición que ocupa el primer carácter a extraer.
Numero Caracteres: Numero de caracteres que se va a extraer a partir de la Posición Inicial.

En el presente caso se desea extraer el 3er y 4to carácter de texto de la celda A1.

RES=  ce

Podemos remarcar que la función Extrae, toma caracteres intermedios de una cadena de texto.

Función Mayusc: Convierte una cadena de texto a mayúscula.

Sintaxis

=MAYUSC(Texto)

El presente caso convierte el texto de la celda A1 a Mayúscula.

RES= EXCEL TOTAL

Función Minusc: Convierte una cadena de texto a minúscula.

Sintaxis
=MINUSC(Texto)

El presente caso convierte la cadena de texto de la celda A1 a Minúsculas.

 RES= excel total

Función Largo: Devuelve el numero de caracteres de una cadena de texto o el  numero de caracteres que compone un numero.

Sintaxis

=LARGO(Texto)

El presente caso, cuenta la cantidad de caracteres de una cadena de texto.

RES= 11

Función Concatenar: Une varias cadenas de texto en uno solo.

Sintaxis

=CONCATENAR(Texto1,[Texto2],..)

El presente caso, une dos textos que se encuentran en celdas diferentes en una sola celda, hay que recalcar que en la formula se agrega el argumento " ", que permite el espacio entre un texto y otro, si no agregamos este argumento, los textos aparecerán juntos (ExcelTotal).

 RES= Excel Total

El siguiente caso genera el Código, tomando los dos primeros caracteres del nombre + los dos primeros caracteres del apellido + el tercer carácter del área + dos últimos caracteres del DNI.

La Formula que corresponde es la siguiente:

=MAYUSC(CONCATENAR(IZQUIERDA(C6,2),EXTRAE(C6,ENCONTRAR(" ",C6,1)+1,2),EXTRAE(D6,3,1),

DERECHA(H6,2)))

El resultado es el que vemos en el cuadro.

Funciones Matemáticas

• Función Cociente: Devuelve como resultado la parte entera de una división.

Sintaxis
Cociente(numerador,denominador)

=COCIENTE(SUMA(A1:A5),3)

• Función Fact: Devuelve como resultado el Factorial de un numero.

Sintaxis
Fact(numero)                                                  =FACT(4)       RES. 24

• Función Potencia: Devuelve el resultado de elevar un numero a una potencia.

Sintaxis
Potencia(numero, potencia)                          =POTENCIA(100,3)     RES. 1,000,000

• Función Producto: Multiplica todos los números, especificados como argumento.

Sintaxis
Producto(numero1,numero2,...)

 RES. 4800

• Función Raiz: Devuelve la raíz cuadrada de un numero.

Sintaxis
Raiz(numero)

RES. 6

• Función Residuo: Devuelve como resultado el residuo de una división.

Sintaxis
Residuo(numero, divisor)

RES. 1

Celdas Absolutas y Relativas

Debemos recordar que una celda es la intersección de una columna y una fila, en donde se ingresan datos, estos datos en ocasiones pueden ocupar 2 o mas celdas; es lo que se ve pero en realidad el dato esta en una sola celda.
Veamos el siguiente caso:

En este caso el titulo que vemos esta ocupando mas de una celda, si vemos el cuadro de nombre el titulo se encuentra en la celda A1.

Celdas Relativas
Las celdas por defecto son relativas, es decir Excel puede modificar las celdas para ajustarla dentro de una formula, conforme cambia la fila o la columna.

Por ejemplo. Si en la celda B1 la formula es:        =A1*2

De tal manera, lo que se quiere es que los datos de la columna A se multiplique por 2 y obtener los resultados en la columna B.

Para esto arrastramos en cuadro de relleno de la celda B1 hacia abajo, obteniendo como resultado la celda de la columna A multiplicado por 2.

Se observa que en las celdas de la columna B, donde se obtuvieron los resultados, Excel ha generado las demás formulas, es decir la formula siguiente es =A2*2, cambiando la celda de la formula.

En resumen hemos creado una formula y Excel a creado el resto.

Una celda relativa cambia cuando la formula se copia. Hacia abajo cambia la fila. Hacia la derecha cambia la columna.

Celdas Absolutas

Una celda es absoluta cuando Excel no la puede ajustar para adaptarse a la fórmula conforme cambia de fila o de columna.

Las celdas absolutas permanecen constantes sin importar a dónde se copie la fórmula y se definen utilizando el símbolo “$”.

En el siguiente caso se quiere calcular el Porcentaje (20%), donde el valor del porcentaje se encuentra en la celda B1,  

Como vemos en la formula se ha agregado el signo $ en la fila, de tal manera que al arrastrar el cuadro de relleno hacia abajo, la celda B$1 siempre sera la misma, haciendo referencia al valor del 20%.

Pero no así la celda del subtotal, por que debe cambiar para obtener su porcentaje.

El signo de valor absoluto ($), puede estar en la fila, en la columna o en ambos.

Ejemplo:

• Columna Absoluta   =A4*$B1 
• Fila Absoluta           =A4*B$1
• Columna y Fila Absoluta   =A4*$B$1

La tecla de función F4, coloca el signo de valor absoluto.

Formato en Excel - Mejora la presentacón

Formato

Las hojas de calculo que creamos, tienen que tener una correcta presentación con lo cual utilizamos los comandos de formato como: Combinar y Centrar, tipo y tamaño de fuente, subrayado, negrita, centrado, color de fuente, color de relleno, bordes, estilo de millares, etc.

Grupo fuente: Con este grupo se podrá cambiar el tipo y tamaño de fuente, Negrita, Cursiva, Subrayado y estilo de subrayado, color de relleno para la celda y color de fuente, borde a las celdas.

Grupo alineación:  Tiene el comando Combinar y Centrar, permite combinar celdas y centrar el texto en la celda combinada. Ajustar el texto al ancho de la columna, Alineación horizontal y alineación vertical, Orientar el texto (rotar el texto), Aumentar y disminuir sangría.

Grupo Numero: Se podrá cambiar el formato de numero como fechas, horas, etc., monedas, porcentaje, estilo de millares, aumentar y disminuir decimales.

Datos y Selección en Excel

Ingreso de Datos

En la celda activa se ingresaran los datos, estos datos ingresados serán aceptados por Excel una vez que se haya presionado alguna de las siguientes teclas: Enter, Flechas de dirección o Tab.

Si se escribe una formula en una celda, esta formula hará referencia a otras celdas, el resultado se vera una vez aceptado el ingreso.

Modificar Datos

Para modificar el contenido de una celda se utiliza la tecla de función F2. Es muy útil cuando los datos o formulas que tiene varios caracteres, ha faltado o se modifica algún carácter entonces no sera necesario escribir nuevamente la formula o el dato.

Tipos de Datos

Excel trabaja con 3 tipos de datos, con los cuales podemos utilizar diferentes formatos:

Números: Tipo de datos que esta conformado por los numero de 0 al 9, el punto decimal, la barra diagonal, dos puntos para las horas y el espacio para números con fracción. Los números se colocaran automáticamente a la derecha del celda.

Debemos recordar que al ingresar números No debemos escribir con coma ni con el signo monetario, ya que excel no lo reconocerá como dato numérico y los resultados tendrán errores.

Formulas: Las formulas empiezan a escribirse con el signo igual, encargándose de realizar operaciones con los datos de la hoja estas formulas utilizan operadores y funciones.

Texto: Este tipo de dato utiliza caracteres alfabéticos asi como otros caracteres del teclado. Los datos de tipo testo se colocaran automáticamente a la izquierda de la celda.

Selección de celdas: Una vez que hemos escrito datos en la celda se podrán seleccionar para realizar diferentes acciones como mover, copiar, imprimir, gráficos, ordenar, etc.

Seleccionar celdas las podemos realizar con el mouse o con el teclado.

Seleccionar con el Mouse: Ubicar el puntero dentro de la celda y cuando se activa el puntero en forma de cruz blanca arrastrar en al mouse para seleccionar varias celdas agrupadas en forma rectangular.

Con el Teclado.- Manteniendo presionada la tecla [Mayusc] desplazar el puntero de celda con las teclas de desplazamiento.

Teclado y Mouse.- Manteniendo presionada la tecla [Ctrl] seleccionar con el puntero del mouse varios rangos de celda.

• Para seleccionar filas o columnas completas, ubicar el puntero del mouse en el encabezado de fila o columna y cuando el puntero cambia a la forma de flecha hacer clic.
• Puede seleccionar varias columnas o filas consecutivas o utilizando la tecla Ctrl, podrá seleccionar columnas o filas distantes.

Manejo de los datos

Copiar series de datos

En una hoja de cálculo en blanco, en la celda A1 escribir el mes Abril y luego arrastrar hacia abajo el cuadro de relleno de esa celda.

Como resultado se obtiene la copia de los demás meses a partir de Abril.

Ahora, en una celda escriba la palabra Lun (Lunes en forma abreviada) y luego arrastre su cuadro de relleno hacia abajo.

Los meses del año como lo días de semana serán reconocidos como elementos de serie, tantos si se los escribe en forma completa como en forma abreviada.

Copia de datos que no son considerados series

Como se puede ver, los textos simplemente no son elementos de series, y se copian tan iguales como fueron escritos en la celda .

Efecto que produce la tecla de Control [Ctrl.] al momento de arrastrar el cuadro de relleno para copiar una serie de datos.

En esta ocasión lo que antes generaba serie ahora el resultado sera copia de los datos. Una vez escrito el dato (Abril o 10) arrastre el cuadro de relleno hacia abajo y se copia los datos.

Que sucede cuando se copia con el cuadro de relleno no una, sino varias celdas seleccionadas.

• En el primer caso si seleccionamos 2 celdas con valores distantes y arrastramos hacia abajo el cuadro de relleno, se generara una serie.
• En el segundo caso seleccionamos los mismos datos que el primer caso, manteniendo presionando la tecla Ctrl, arrastramos el cuadro de relleno, se repitan los datos.
• En el tercer caso al escribir tres textos, al arrastrar el cuadro de relleno se copiaran los dato.

Uso de los datos

Para utilizar los datos correctamente en la creación de la hoja de calculo, tenemos 4 tipos de operadores.

Operadores matemáticos: Este tipo de datos realiza operaciones con datos que son necesariamente con datos de tipo numérico. Permite crear formulas haciendo uso de los operadores matemáticos respetando sus reglas. Estos operadores matemáticos son:

1. Cambio de signo ( - )
2. Porcentaje ( % )
3. Potencia ( ^ )
4. Multiplicación ( * )
5. División (  /  )
6. Suma ( + )
7. Resta ( - )

Debemos tener presente, si en una formula se debe ejecutar primero un operador de menor nivel este debe estar entre paréntesis. 

Operadores de Relación: Las fórmulas que usan operadores de relación, arrojan respuestas lógicas del tipo Verdadero o Falso, según como sea evaluada la comparación. Los datos a comparar pueden ser numéricos o de texto.

1. Mayor que ( > )
2. Menor que ( < )
3. Mayor o igual ( >= )
4. Menor o igual que ( <= )
5. Igual que ( = )
6. Diferente que ( < > )

Operador de Texto: Este operador se encarga de unir textos de diferentes celdas para formar frases. Los datos que se concatenan pueden ser del tipo texto o también numérico, pero el resultado de estas formulas son datos del tipo texto.

1. Concatenación ( & )

Operadores de Referencia: Se utilizan en las formulas para indicar qué se debe hacer con las celdas o los rangos de celdas, se utilizan con las funciones.

• Los dos puntos escritos entre dos coordenadas de celdas permiten hacer referencia a un grupo de celdas dispuestas en forma rectangular.
• La coma (o punto y coma) permite separar las celdas o rangos de celdas que se desean unir para una operación determinada.
• El espacio en blanco escrito entre dos rangos de celdas indica que se desea realizar una operación solamente con la intersección de ambos rangos, y no con todas las celdas en conjunto.

Funciones mas comunes de Excel

Funciones Mas Comunes

Estas funciones son las mas utilizadas en la creación de hojas de calculo.

• Función Suma: Suma todos los números en un rango de celdas,

Sintaxis
=Suma(numero1, numero2, ...)

• Función Promedio: Devuelve el promedio (media aritmética) de todos los números de un  rango de celdas.

Sintaxis
=Promedio(numero1, numero2, ...)

• Función Max: Devuelve el numero máximo de un rango de celdas.

Sintaxis
=Max(numero1, numero2, ...)

• Función Min: Devuelve el numero mínimo de un rango de celdas.

Sintaxis
=Min(numero1, numero2, ...)

• Función Contar: Devuelve la cantidad de números de un  rango de celdas.

Sintaxis
=Contar(numero1, numero2, ...)

• Función Contara: Devuelve el numero de celdas no vacías, no importa el tipo de dato.

Sintaxis
=Contara(valo1, valor2, ...)

• Función Entero: Devuelve la parte de entera de un numero con decimal, truncando la parte decimal.

Sintaxis
=Entero(numero)

• Función Redondear: Redondea un numero a un numero de decimales especificado.

Sintaxis
=Redondear(numero, numero decimales)

• Suma de edades de los pacientes:  Formula es  =suma(b4:b11)  Res. 148   
• Promedio de edades de los pacientes:  Formula es =promedio(b4:b11)   Res. 29.6
• Edad máxima de un paciente:  Formula es  =max(b4:b11)    Res. 37
• Edad mínima de un paciente: Formula es  =min(b4:b11)  Res. 22
• Cantidad de pacientes con cita programada: Formula es   =contar(c4:c11)  Res. 5
• Cantidad total de pacientes: Formula es   =contara(c4:c11) Res. 6

En los 2 siguientes casos, las formulas podrán tener 2 o mas funciones, en ese caso la ejecución se realizara de adentro hacia afuera:
- Primero se ejecuta la función promedio y luego la función entero,
- Primero se ejecuta la función promedio y luego la función redondear.

• Edad promedio sin considerar decimales: Formula es   =entero(promedio(b4:b11)  Res. 29
• Edad promedio redondeado a 0 decimal: Formula =redondear(promedio(b4:b11)  Res. 30