Microsoft Excel Avanzado

Funciones

Función BucarV

Nos permite encontrar un valor dentro de un rango de datos, es decir, podemos buscar un valor dentro de una tabla y saber si dicho valor existe o no.

Tenemos nuestra siguiente tabla:

Deseamos buscar por medio del código: el nombre de la persona y la cantidad a devolver, usaremos la Función BuscarV

Haremos una pequeña tabla donde al digitar el código automáticamente aparezca  el nombre del contribuyente y la cantidad a devolver:

Nos ubicamos en la celda al lado de contribuyente, en la barra de menú nos iremos a "Insertar Función", digitamos el nombre de nuestra función, que seria "buscarv":

Al encontrar nuestra función buscarv y al darle aceptar se abre otro cuadro de diálogo llamado, argumentos de función:

Digitamos los argumentos en base a los valores buscados y serian los siguientes:

Y el resultado seria el siguiente:

Al digitar cualquier código, nos aparece automáticamente el contribuyente y el dinero a devolver

FUNCIONES FINANCIERAS

Calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante.

Las funciones financiera de Excel permiten organizar mas fácilmente el cálculo, administración y análisis delas finanzas tanto personales como para negocios 

Las principales funciones financieras comúnmente utilizadas en excel son : vf, pago, va, tasa, nper.

Tenemos los siguientes datos:

Calcularemos el pago mensual, el valor actual, periodo pago, interés y valor a futuro.

Pago mensual:

Digitamos los argumentos o datos proporcionados para calcular el pago mensual por 6 meses

Al obtener el resultado observamos que la cantidad a pagar mensual aparece en un color rojo, lo que significa que es como un egreso mensual el que tendremos, por eso su color siempre será rojo:

Calcularemos el Valor Actual

Digitamos los argumentos o datos para obtener el Valor Actual

Calcularemos el periodo de pago (NPER)

Ingresamos los datos 

y obtenemos el periodo de pago 

Ahora corroboramos si los intereses

Ingresamos los datos o argumentos

Resultado

Y por ultimo buscamos el Valor a Futuro

Ingresamos los argumentos para encontrar el Valor Futuro

Y así completamos este grupo de funciones financieras.

FORMATO CONDICIONAL

Es una útil herramienta que se encarga de aplicar automáticamente  un formato determinado a aquellas celdas que cumplan una serie de criterios concretos. Por ejemplo puedes ser si el valor es mayor a 5,000 que la celda sea amarilla, así podrás ver de un vistazo las celdas cuyo valor es mayor a 5,000. 

Observen las siguientes imágenes:

• Formato condicional lo encontramos en el grupo de "Estilos"; Formato Condicional.

Emplearemos un formato condicional en forma de Barra de datos, para cada celda:

Marcamos la columna "C" , que es juegos perdidos y seleccionamos un tipo de relleno:

Lo mismo hacemos con la siguiente columna "D" , que seria juegos ganados:

Repetimos los pasos de marcar la columna "E", empatados:

Y así tenemos nuestra tabla con un formato condicional, opción Barra de Datos.

Observe el siguiente ejemplo de formato condicional, el que es partiendo o cumpliendo una condición:

Marcar las celdas de la columna "D", si las perdidas son mayores a 2950, marcar de color relleno verde, con texto verde oscuro:

1 Marcar la columna pérdidas, clic en formato condicional, resaltar regla de celda, es mayor que

Y verificamos que la condición se cumple, texto color verde, con fondo verde oscuro para las cantidades mayores a 2950.

Hacemos los mismos pasos para la siguiente columna que es la de Ganancia, marcamos las celdas de color amarillo fuerte fondo y letra las que sean mayores a 3800

Verificamos que la función se haya cumplido y vemos que así es, los datos marcados de color amarillo corresponden a la condición establecida que era mayor a 3800.

Función Lógica si

Es una de las funciones mas populares de Excel y nos permite realizar comparaciones lógicas entre un valor y lo esperado.

Por esto una instrucción SI, puede tener dos resultados. El primer resultado es si la comparación es verdadera y el segundo si la comparación es falsa.

Tenemos la siguiente tabla, donde pondremos en práctica la función lógica SI:

Necesitamos que la función lógica SI, nos de como resultado si el alumno "Aprobó" o "Reprobó" con una nota mayor o igual a 7.

1 Abrimos el cuadro de diálogo Insertar Función, y buscamos la función SI Observe la imagen: 

Damos un clic en SI y se abre otro cuadro de dialogo llamado, Argumentos de Función:

Ingresamos los datos para obtener los resultados:

En el campo Prueba Lógica, ingresamos la condición que si el promedio es >=7

En el valor _si_verdadero, si cumple la prueba lógica seria "Aprobado"

y en el Valor_si _falso, seria al no cumplir la prueba lógica seria "Reprobado"

Le damos clic en aceptar y este es el resultado:

Vemos que si cumple la Función Lógica Si

FUNCIÓN SUMAR.SI

Esta función sirve para sumar los valores de un intervalo que cumplen los criterios especificados y de esta manera excluir aquellas celdas que nos nos interesa incluir en la operación.

En el siguiente listado de números queremos la cantidad que suma todos los números "5"

Abrimos el cuadro de diálogo Insertar Función y buscamos la función "Sumar. Si" 

En "Rango", marcamos los datos y en "Criterio", colocamos el numero que queremos que sume las veces que se repita en ese listado:

Le damos aceptar y verificamos el resultado:

TABLAS DINÁMICAS

Es una de las herramientas mas poderosas de Excel, pero también es una de las características que mas usuarios de Excel se sienten intimidados a utilizar  a utilizar. Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan solo arrastrar y soltar las diferentes columnas que formarán el reporte. 

Tenemos nuestra siguiente tabla:

En la barra de herramientas del menú "INSERTAR", encontramos Tablas Dinámicas:

Al ubicarnos dentro de la tabla, le damos un clic a "Tabla Dinámica", y se abre un cuadro de dialogo, llamado "Crear tabla dinámica" , donde automáticamente nos marca de forma punteada toda la tabla, y donde nos pregunta donde ubicar el informe de tabla dinámica:

En la opción hoja de calculo existente, tenemos que indicar en que celda queremos el informe:

 Cuando ya tenemos definida la ubicación para nuestro reporte, le damos clic en Aceptar y nos aparece de la siguiente manera:

Observamos que se abre un cuadro de diálogo con una serie de campos, con los que esta conformada nuestra tabla, y son esos campos los que arrastraremos según la información que necesitemos, como por ejemplo que servicios son los que mas solicitan los clientes, que es lo que mas se factura al mes, en que localidades solicitan mas los servicios de telefonía, etc:

Como podemos observar en la imagen anterior ya arrastramos ciertos campos para así formar nuestra Tabla Dinámica, y nos damos cuenta que de nuestra primera tabla, a una Tabla Dinámica, se ha reducido a la mitad, utilizando únicamente la información que mas nos interese, ya sea para un informe o para un dato estadístico, siendo así que de igual manera podemos hacer un "Gráfico Dinámico", con estos misma información:

Marcamos nuestra Tabla Dinámica y en la barra de herramientas del menú "Insertar", grupo "Gráficos", encontramos Gráfico Dinámico: 

   Le damos un clic y se abre la opción de Gráfico dinámico:

Al darle clic al gráfico dinámico se abre un cuadro de dialogo con opciones de diferentes gráficos observe la siguiente imagen:

Escogemos el de columna y le damos aceptar:

Y así tenemos nuestra tabla y gráfico dinámico; tenemos que saber que esta es una de muchas formas para hacer nuestra tabla dinámica, podemos escoger el resto de campos para crear diferentes tablas y gráficos dinámicos.