Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
1 Piensa… ¿Habrá necesidad de conocer y saber aplicar TODAS las Funciones de Excel 2016? ¿Cuántas son? ¿Están divididas por Categorías? ¿Cuáles serán de aplicación en tu trabajo? Asignatura: INFORMÁTICA Profesor: Cadoni, Jorge Pieza Nro. 11 del “Puzzle”. Excel. Clase 1 11.1 Estudio y aplicación pormenorizados de Funciones: ¿Cuáles funciones se debe conocer? De Fecha y Hora. De Texto. De Búsqueda y Referencia. Aplicación de las funciones: BUSCAR, CONSULTAV, CONSULTAH, INDICE, COINCIDIR. Financieras. De Base de Datos. Matemáticas y Trigonométricas. Estadísticas. Lógicas. De Información. Algunas aplicaciones de Funciones Matemáticas Condicionales y Relacionadas a Tendencias Centrales, Funciones Financieras Relacionadas a Depreciación, Funciones Estadísticas. . Estudio y aplicación pormenorizados de Funciones: ¿Cuáles funciones se debe conocer? Veamos los diversos tipos de Funciones que existen: Funciones de fecha y hora De entre todo el conjunto de funciones, en este apartado estudiaremos las funciones dedicadas al tratamiento de fechas y horas. En varias funciones veremos que el argumento que se le pasa o el valor que nos devuelve es un "número de serie". Pues bien, Excel llama número de serie al número de días transcurridos desde el 0 de enero de 1900 hasta la fecha introducida, es decir toma la fecha inicial del sistema como el día 0/1/1900 y a partir de ahí empieza a contar, en las funciones que tengan “número de serie” como argumento, podremos poner un número o bien la referencia de una celda que contenga una fecha. CUIDADO: PUEDES ABURRIRTE EN LOS APARTADOS SIGUIENTES. Lee este mensaje en rojo CUIDADOSAMENTE: Solamente se necesita que sepas que en Excel existen muchos TIPOS de FUNCIONES. Preocúpate por conocer los Tipos y para qué sirve cada Tipo. ¿Cuáles funciones debes conocer y “dominar”? Aquellas que vayas necesitando en tu trabajo. Solamente la experiencia te llevará a un conocimiento acabado de las funciones, pero nunca aplicarás todas. Por eso te sugiero que leas con curiosidad cada lista de funciones pero sin intentar aprender, en profundidad, para qué sirve cada una. Los listados te servirán de referencia a la hora de tener que resolver situaciones- problema usando funciones. 2 Estas son las funciones de fecha y hora ofrecidas por Excel. Función Descripción AHORA Devuelve el número de serie correspondiente a la fecha y hora actuales AÑO Convierte un número de serie en un valor de año DIA Convierte un número de serie en un valor de día del mes DIA.LAB Devuelve el número de serie de la fecha que tiene lugar antes o después de un número determinado de días laborables DIA.LAB.INTL Devuelve el número de serie de la fecha anterior o posterior a un número especificado de días laborables mediante parámetros para indicar cuáles y cuántos días son días de fin de semana DIAS.LAB Devuelve el número de todos los días laborables existentes entre dos fechas DIAS360 Calcula el número de días entre dos fechas a partir de un año de 360 días DIASEM Convierte un número de serie en un valor de día de la semana FECHA Devuelve el número de serie correspondiente a una fecha determinada FECHA.MES Devuelve el número de serie de la fecha equivalente al número indicado de meses FECHANUMERO Convierte una fecha con formato de texto en un valor de número de serie FIN.MES Devuelve el número de serie correspondiente al último día del mes anterior o posterior a un número de meses especificado FRAC.AÑO Devuelve la fracción de año que representa el número total de días existentes entre el valor de fecha inicial y el de fecha final HORA Convierte un número de serie en un valor de hora HOY Devuelve el número de serie correspondiente al día actual MES Convierte un número de serie en un valor de mes MINUTO Convierte un número de serie en un valor de minuto NSHORA Devuelve el número de serie correspondiente a una hora determinada NUM.DE.SEMANA Convierte un número de serie en un número que representa el lugar numérico correspondiente a una semana de un año. SEGUNDO Convierte un número de serie en un valor de segundo HORANUMERO Convierte una hora con formato de texto en un valor de número de serie Funciones de texto Una hoja de cálculo está pensada para manejarse dentro del mundo de los números, pero Excel también tiene un conjunto de funciones específicas para la manipulación de texto. Estas son las funciones de texto ofrecidas por Excel. Función Descripción CARACTER Devuelve el carácter especificado por el número de código CODIGO Devuelve un código numérico del primer carácter de una cadena de texto CONCATENAR Concatena varios elementos de texto en uno solo DECIMAL Da formato a un número como texto con un número fijo de decimales DERECHA, DERECHAB Devuelve los caracteres del lado derecho de un valor de texto ENCONTRAR, ENCONTRARB Busca un valor de texto dentro de otro (distingue mayúsculas de minúsculas) 3 Función Descripción EXTRAE, EXTRAEB Devuelve un número específico de caracteres de una cadena de texto que comienza en la posición que se especifique HALLAR, HALLARB Busca un valor de texto dentro de otro (no distingue mayúsculas de minúsculas) IGUAL Comprueba si dos valores de texto son idénticos IZQUIERDA, IZQUIERDAB Devuelve los caracteres del lado izquierdo de un valor de texto LARGO, LARGOB Devuelve el número de caracteres de una cadena de texto LIMPIAR Quita del texto todos los caracteres no imprimibles MAYUSC / MINUSC Convierte el texto en mayúsculas o en minúsculas respectivamente MONEDA Convierte un número en texto, con el formato de moneda $ (dólar) NOMPROPIO Pone en mayúscula la primera letra de cada palabra de un valor de texto REEMPLAZAR Reemplaza caracteres de texto REPETIR Repite el texto un número determinado de veces SUSTITUIR Sustituye texto nuevo por texto antiguo en una cadena de texto T Si el valor es un texto lo devuelve, y si no devuelve una cadena vacía TEXTO Convierte un valor en texto, con un formato de número específico. Funciones de búsqueda En una hoja de Excel es muy importante escoger los datos correctos para trabajar con las fórmulas diseñadas. Por eso existe una agrupación de funciones específicas para realizar búsquedas de datos. Comprendamos qué es en sí una búsqueda, cuando queremos encontrar alguna información de algo no buscamos directamente por lo que buscamos pues lo desconocemos, realizamos una búsqueda de una propiedad o algo similar que conocemos que puede tener lo que buscamos. Por ejemplo, si buscamos a una persona, describimos su aspecto físico, si buscamos el nº de teléfono de un restaurante, buscamos en la guía de teléfonos por el nombre del restaurante. Normalmente el dato que queremos encontrar no lo conocemos por eso buscamos por otros datos que sí conocemos. Estas son las funciones disponibles por Excel para realizar búsquedas: Función Descripción AREAS Devuelve el número de áreas de una referencia BUSCAR Busca valores de un vector o una matriz CONSULTAH Busca en la fila superior de una matriz y devuelve el valor de la celda indicada CONSULTAV Busca en la primera columna de una matriz y se mueve en horizontal por la fila para devolver el valor de una celda COINCIDIR Busca valores de una referencia o matriz COLUMNA Devuelve el número de columna de una referencia COLUMNAS Devuelve el número de columnas de una referencia DESREF Devuelve un desplazamiento de referencia respecto a una referencia dada 4 Función Descripción DIRECCION Devuelve una referencia comotexto a una sola celda de una hoja de cálculo ELEGIR Elige un valor de una lista de valores FILA Devuelve el número de fila de una referencia FILAS Devuelve el número de filas de una referencia HIPERVINCULO Crea un acceso directo o un salto que abre un documento almacenado en un servidor de red, en una intranet o en Internet IMPORTARDATOSDINAMICOS Devuelve los datos almacenados en un informe de tabla dinámica INDICE Usa un índice para elegir un valor de una referencia o matriz INDIRECTO Devuelve una referencia indicada por un valor de texto TRANSPONER Devuelve la transposición de una matriz Algunas funciones de búsqueda son tan importantes que merecen un tratamiento especial en este curso: CONSULTAV, CONSULTAH, INDICE, COINCIDIR Dentro de las múltiples tareas que podemos realizar en Excel una de las más llamativas, interesantes y de gran ayuda es, sin duda alguna, utilizar CONSULTAV y CONSULTAH. Estas funciones nos brindan la posibilidad de buscar valores ya sea en forma horizontal o vertical para obtener un resultado específico. Con CONSULTAV podemos encontrar valores en una lista vertical mientras que con CONSULTAH los podemos buscar en una lista horizontal. 1. Usar CONSULTAV En primer lugar, debemos seleccionar el rango de celdas donde se realizará la búsqueda: Posteriormente nos ubicaremos en la celda donde deseamos que el resultado esté desplegado, en este caso usaremos la celda H1. La sintaxis para usar CONSULTAV es la siguiente: CONSULTAV(Valor_buscado;rango donde buscaremos el valor;indicador de columnas;orden) En este caso queremos que con el número de código nos despliegue que sistema operativo es. Para ello usaremos la siguiente sintaxis: =CONSULTAV(H2;A1:E7;3;FALSO) Para resumir esta función necesitamos lo siguiente: CONSULTAV: Hace referencia al tipo de búsqueda, en este caso vertical. H2: Indica que buscaremos por el valor que ingresemos en la celda H2 en el rango indicado A1:E7: Hace referencia al rango de celdas donde se realizará la búsqueda de la información 3: Hace referencia al número de columna de la cual se obtendrá el resultado FALSO: Hace referencia a la exactitud de la búsqueda donde Verdadero hace referencia a una búsqueda aproximada y Falso hace referencia a una búsqueda exacta. 5 El resultado obtenido será el siguiente en proporción al código que ingresemos en la celda H2. Hemos visto que si ingresamos el código F en la celda H2 veremos en la celda H1 el resultado CentOS el cual está relacionado a la tercera columna que fue la indicada en la búsqueda. 2. Cómo usar CONSULTAH Ahora CONSULTAH nos va a brindar la posibilidad de realizar la búsqueda de forma horizontal. Para ello usaremos la siguiente sintaxis: CONSULTAH(Valor_buscado;rango donde buscaremos el valor;indicador de columnas;orden) Para este ejemplo usaremos el mismo rango de celdas usado en CONSULTAV. En este caso queremos buscar el desarrollador al ingresar el nombre del producto, la sintaxis a usar será la siguiente: =CONSULTAH("Desarrollador";B1:E7;2;FALSO) Como vemos la función más usada es CONSULTAV ya que realiza una búsqueda más completa ya que con CONSULTAH debemos indicar la fila exacta para la obtención del resultado. Como vemos estas grandes funciones son una valiosa ayuda cuando necesitamos obtener información de grandes cantidades de datos sin necesidad de mucho esfuerzo. PERO DICEN QUE NO TODO LO QUE RELUCE ES ORO ¡!!! CONSULTAV tiene algunas serias limitaciones. Y, por eso, conviene aprender a usar INDICE y COINCIDIR. Muchos se preguntan como se puede resolver algún problema con las funciones de Excel de búsqueda y la respuesta siempre es la misma: “Ten cuidado, CONSULTAV tiene varias limitaciones“. La peor limitación de CONSULTAV() posiblemente sea que sólo devuelve valores de columnas que estén a la derecha del valor buscado. Una vez que tengamos tablas complejas definidas lo más probable es que el campo (columna) que queramos usar para realizar las búsquedas no sea el primero de la tabla. Vamos a ver cómo resolver este problema con las funciones INDICE y COINCIDIR y verás que podrás prescincir de usar CONSULTAV. Vamos a ver un ejemplo, que así se entienden mejor las cosas. Sobre la siguiente tabla, vamos a realizar tres búsquedas (consultas) distintas: 1. ¿Cómo se llama el usuario de la Delegación de Bilbao? 2. ¿Cuál es la Delegación de Amaia Salamanca? 3. ¿Quién de los usuarios hizo el mayor gasto en 2015? 6 Vamos a ver la explicación con el primer ejemplo de búsqueda. Estamos buscando el usuario de la delegación de Bilbao. Los parámetros son simples. Se usa la función INDICE (función que devuelve los datos de una matriz, si le pasamos la fila y la columna) y la función COINCIDIR (función que devuelve una posición dentro de un rango de valores). INDICE(): Requiere 3 parámetros: 1) El primer parámetro de la función INDICE() del ejemplo es $A$2:$E$5 que no es otra cosa que el rango que ocupan todos los datos de nuestra tabla. En realidad, no es necesario referenciar todo el rango, si no que será suficiente con especificar el rango de la columna del valor que queremos que nos devuelva nuestra función (en este caso $C$2:$C$5), pero cuando trabajamos con tablas complejas es lo habitual, luego explicaré por qué. 2) El segundo parámetro es la fila que queremos que nos devuelva, este valor lo obtenemos mediante la función COINCIDIR(), que requiere otros 3 parámetros: El primero es el valor buscado, en nuestro caso “Bilbao”. El segundo es el rango de datos (matriz) donde lo buscamos, en nuestro ejemplo $A$2:$A$5. El tercero es el tipo de coincidencia. Si como en nuestro caso estamos buscando valores únicos, indicaremos 0, que significa “Coincidencia exacta”. 3) El tercer parámetro de INDICE es la columna que queremos que nos devuelva dentro del rango especificado en el primer parámetro. En nuestro ejemplo queremos que nos devuelva un valor de la columna 3, que es donde están los nombres de usuario. Esta primera búsqueda la podríamos haber realizado con CONSULTAV(), ya que el valor buscado está en la columna C y el texto que usamos para buscar está en la columna A. Es decir, buscamos que nos devuelva valores situados a la derecha. El segundo ejemplo es exactamente igual, con la salvedad que en este caso no podríamos usar la función CONSULTAV, porque el valor que queremos está a la izquierda del texto que estamos buscando. CONSULTAV presenta aquí su clara limitación. En el tercer ejemplo se aprovechó para introducir otra función como valor de búsqueda, en vez de un texto simple. En este caso la función MAX() nos devuelve el número mayor dentro de un rango de datos. En los tres primeros ejemplos he usado referencias de fila y columna fijas, pero lo verdaderamente útil es usar el Administrador de Nombres de Excel (se lo verá más adelante en este curso), que nos permite dar nombres a rangos, tablas, etc. El texto de las funciones queda mucho más legible y es mucho más sencillo mover posteriormente los datos de una celda a otra. En este último ejemplo se ha creado una tabla con el rango de datos $A$1:$E$5 y se le ha dado el nombre de tUsuariosGastos. Como puede verse, el texto usado en nuestra función de búsqueda es mucho más claro. https://support.office.com/es-es/article/Definir-y-usar-nombres-en-f%C3%B3rmulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64 7 Ahora coloca este archivo en Vista al 150%. Copia los datos de la última imagen en un nuevo Libro de Excel. Aplica las funciones vistas. Practica. Obtendrás un gran rédito: APRENDER. Funciones financieras Excel es una de las herramientas más potentes para trabajar con información y cálculos financieros, ofrece una amplia gama de funciones prediseñadas para crearte tu propia "caja de ahorros en casa". Todas estas funciones están agrupadas en la categoría deFinancieras. Vamos a enumerar la amplia gama de funciones financieras que nos ofrece Excel: Función Descripción AMORTIZ.LIN Devuelve la amortización de cada uno de los períodos contables AMORTIZ.PROGRE Devuelve la amortización de cada período contable mediante el uso de un coeficiente de amortización. CUPON.DIAS Devuelve el número de días del período (entre dos cupones) donde se encuentra la fecha de liquidación CUPON.DIAS.L1 Devuelve el número de días desde el principio del período de un cupón hasta la fecha de liquidación CUPON.DIAS.L2 Devuelve el número de días desde la fecha de liquidación hasta la fecha del próximo cupón CUPON.FECHA.L1 Devuelve la fecha de cupón anterior a la fecha de liquidación CUPON.FECHA.L2 Devuelve la fecha del próximo cupón después de la fecha de liquidación CUPON.NUM Devuelve el número de pagos de cupón entre la fecha de liquidación y la fecha de vencimiento DB Devuelve la amortización de un bien durante un período específico a través del método de amortización de saldo fijo DDB Devuelve la amortización de un bien durante un período específico a través del método de amortización por doble disminución de saldo u otro método que se especifique DVS Devuelve la amortización de un bien durante un período especificado usando el método de amortización acelerada con una tasa doble y según el coeficiente que se especifique. DURACION Devuelve la duración anual de un valor bursátil con pagos de interés periódico INT.ACUM Devuelve el interés acumulado de un valor bursátil con pagos de interés periódicos INT.ACUM.V Devuelve el interés acumulado de un valor bursátil con pagos de interés al vencimiento INT.EFECTIVO Devuelve la tasa de interés anual efectiva INT.PAGO.DIR Calcula el interés pagado durante un período específico de una inversión. Esta función se incluye para proporcionar compatibilidad con Lotus 1-2-3. MONEDA.DEC Convierte una cotización de un valor bursátil expresada en forma fraccionaria en una cotización de un valor bursátil expresada en forma decimal MONEDA.FRAC Convierte una cotización de un valor bursátil expresada en forma decimal en una cotización de un valor bursátil expresada en forma fraccionaria NPER Devuelve el número de pagos de una inversión, basada en pagos constantes y periódicos y una tasa de interés constante. PAGO.INT.ENTRE Devuelve el interés acumulado pagado entre dos períodos PAGO.PRINC.ENTRE Devuelve el capital acumulado pagado de un préstamo entre dos períodos 8 PAGOINT Devuelve el pago de intereses de una inversión durante un período determinado Función Descripción PAGOPRIN Devuelve el pago de un capital de una inversión determinada, basado en pagos constantes y periódicos y una tasa de interés constante. SYD Devuelve la depreciación por método de anualidades de un bien durante un período específico. TASA Devuelve la tasa de interés por periodo de un préstamo o una inversión. TASA.DESC Devuelve la tasa de descuento de un valor bursátil TASA.INT Devuelve la tasa de interés para la inversión total de un valor bursátil TIR Devuelve la tasa interna de retorno de una inversión para una serie de valores en efectivo. TIRM Devuelve la tasa interna de retorno modificada, para una serie de flujos periódicos, considerando costo de la inversión e interés al volver a invertir el efectivo. VA Devuelve el valor actual de una inversión. El valor actual es el valor que tiene actualmente la suma de una serie de pagos que se efectúan en el futuro. VF Devuelve el valor futuro de una inversión VF.PLAN Devuelve el valor futuro de un capital inicial después de aplicar una serie de tasas de interés compuesto VNA Devuelve el valor neto actual de una inversión a partir de una tasa de descuentos y una serie de pagos futuros. Funciones Matemáticas y Trigonométricas, Estadísticas, Lógicas y de Información. Además de las funciones anteriormente mencionadas, existe un gran abanico de funciones de diferentes categorías que nos pueden ser de gran utilidad. Veremos algunas de ellas clasificándolas por categorías. Función Descripción Matemáticas y trigonométricas ABS Devuelve el valor absoluto de un número ALEATORIO Devuelve un número entre 0 y 1 COMBINAT Devuelve el número de combinaciones para un número determinado de elementos COS Devuelve el coseno de un ángulo ENTERO Redondea un número hasta el entero inferior más próximo EXP Realiza el cálculo de elevar "e" a la potencia de un número determinado FACT Devuelve el factorial de un número NUMERO.ROMANO Devuelve el número pasado en formato decimal a número Romano PI Devuelve el valor de la constante pi POTENCIA Realiza el cálculo de elevar un número a la potencia indicada PRODUCTO Devuelve el resultado de realizar el producto de todos los números pasados como argumentos RAIZ Devuelve la raiz cuadrada del número indicado 9 RESIDUO Devuelve el resto de la división Función Descripción Estadísticas MEDIA.ARMO Devuelve la media armónica de un conjunto de números positivos MAX Devuelve el valor máximo de la lista de valores MIN Devuelve el valor mínimo de la lista de valores MEDIANA Devuelve la mediana de la lista de valores MODA Devuelve el valor que más se repite en la lista de valores PROMEDIO Devuelve la media aritmética de la lista de valores VAR Devuelve la varianza de una lista de valores K.ESIMO.MAYOR Devuelve el valor k-ésimo mayor de un conjunto de datos K.ESIMO.MENOR Devuelve el valor k-ésimo menor de un conjunto de datos Función Descripción Lógicas FALSO Devuelve el valor lógico Falso VERDADERO Devuelve el valor lógico Verdadero SI Devuelve un valor u otro, según se cumpla o no una condición NO Invierte el valor lógico proporcionado Y Comprueba si todos los valores son verdaderos O Comprueba si algún valor lógico es verdadero y devuelve VERDADERO Función Descripción De Información ESBLANCO Comprueba si se refiere a una celda vacía ESERR Comprueba si un valor es un error ESLOGICO Comprueba si un valor es lógico ESNOTEXTO Comprueba si un valor no es de tipo texto ESTEXTO Comprueba si un valor es de tipo texto ESNUMERO Comprueba si un valor es de tipo numérico TIPO Devuelve un número que representa el tipo de datos del valor Funciones de Bases de Datos: Una pequeña aproximación al empleo de funciones con una tabla (o Lista o Base de Datos) Un aspecto muy útil de la relación entre programas de hojas de cálculo y bases de datos es que es posible crear muchas funciones de resumen de datos de bases de datos en programas de hoja de cálculo y viceversa. Existen funciones disponibles para su uso con las listas de datos de Excel, BDSUMA, BDPROMEDIO, BDCONTAR, BDMAX y BDMIN. Cada función opera del mismo modo que su versión más sencilla que ya conoces. 10 Pero a diferencia de las sencillas, con las funciones de bases de datos, se puede definir criterios para restringir las celdas que la función considera cuando genera su resultado. Cada función de bases de datos sigue este formato. Usaremos como ejemplo BDSUMA. =BDSUMA(Base de datos;"Nombre del campo";criterios) "Base de datos" es el rango de celdas que comprende la lista de datos, "Nombre de campo" es el nombre del campo (no la letra de la columna), y "criterios" es el rango de celdas que contiene los nombres de campo y criterios a tener en cuenta. Por ejemplo: =BDSUMA(A10:F687;"Compras";A1:F2)Las funciones de bases de datos se utilizan cuando queremos realizar cálculos sobre alguna columna pero añadiendo una condición de selección de las filas que entrarán en el cálculo, es decir, aplicando previamente un filtro. Por ejemplo, si tenemos una columna con el beneficio obtenido por nuestros automóviles (ver figura más abajo) y queremos saber cuánto ha sido el beneficio de los Ford, no podemos utilizar la función suma porque sumaría todos los automóviles, en este caso lo podríamos conseguir con la función de base de datos BDSUMA incluyendo la condición de filtrado automóvil="Ford" Para explicar las funciones de Base de datos que nos ofrece Excel, utilizaremos la hoja que se muestra a la derecha: En esta hoja, tenemos una lista con los automóviles de la empresa, con los datos de plazas, años, rentabilidad y beneficio obtenido. Nota: Las filas 1 a 4 se utilizan para definir los criterios. Estas son las funciones de base de datos ofrecidas por Excel. Todas ellas guardan la misma estructura: FUNCION (datos; campo; criterios) y fueron vistas junto al Tema Tablas en la Unidad anterior, por lo cual aquí no se extenderá la explicación de las mismas. Función Descripción BDCONTAR Cuenta las celdas que contienen un número BDCONTARA Cuenta las celdas que contienen un valor BDMAX Obtiene el valor máximo BDMIN Obtiene el valor mínimo BDPRODUCTO Obtiene el producto de los valores indicados BDPROMEDIO Obtiene el promedio de los valores indicados BDSUMA Obtiene la suma de los valores indicados BDEXTRAER Obtiene un valor de un campo en una fila que cumpla un criterio de selección BDVAR Calcula la varianza sobre una muestra de valores BDVARP Calcula la varianza sobre todos los valores de un campo BDDESVEST Calcula la desviación estándar sobre una muestra de valores BDDESVESTP Calcula la desviación estándar sobre todos los valores de un campo 11 12 Algunas aplicaciones de Funciones: Las Funciones cuya aplicación veremos son simplemente parte de la gran cantidad de funciones que el producto trae programadas y listas para ser utilizadas. Se las emplea en actividades que hacen uso intensivo de las capacidades de Excel; aplicaciones reservadas para personas con un dominio respetable del producto de hoja de cálculo de Microsoft. Más allá de las funciones "básicas" SUMA, PROMEDIO, MÁXIMO, MÍNIMO y CONTAR, entre algunas otras, cada tipo de actividad requiere el uso de funciones específicas. No sería posible practicar en este curso TODAS las funciones de Excel y, por lo tanto, resulta razonable describir sólo algunas, con ejemplos. En general, saber utilizar funciones implica conocer que para cada "cálculo complejo" debe pensarse que "es probable que exista una función que lo resuelva" por lo cual el usuario DEBE SABER BUSCAR LA FUNCIÓN NECESARIA. Si la encuentra, debe analizar los argumentos que requiere, comprender ACABADAMENTE su significado (para lo cual GOOGLE y el Asistente de Funciones prestan gran ayuda) y, finalmente, pasar adecuadamente estos argumentos para que la función opere correctamente y devuelva los resultados necesarios. Por lo expuesto, se verán algunas funciones de utilidad y se dejará para el estudiante encontrar aquella o aquellas funciones que resuelvan su problema particular en un momento dado. 1. Aplicaciones de Funciones Matemáticas Condicionales. Una Función de Microsoft Excel realiza cálculos con valores de la hoja de cálculo. Normalmente, las fórmulas realizan cálculos con todos los valores de un rango determinado. Sin embargo, ¿qué sucede si desea que Excel cambie la fórmula si una determinada condición es cierta o si desea incluir en el cálculo sólo los valores que cumplan determinadas condiciones? Por ejemplo, es posible que desee hacer un seguimiento de los pedidos hechos por los vendedores y después resumir las ventas de cada vendedor sin volver a organizar los datos. O tal vez desee determinar la cantidad de bonificación por cada venta, basada en la cantidad total facturada. Cuando desee que las Funciones realicen pruebas condicionales, puede utilizar Funciones condicionales en Excel. Utiliza fórmulas condicionales para calcular los porcentajes de bonificación y para resumir el número de pedidos hecho por cada vendedor y la cantidad total facturada durante un período determinado. Excel incluye tres funciones de hoja de cálculo que calculan resultados basados en condiciones. Para contar el número de veces que un valor específico aparece en un rango de celdas, utiliza la función de hoja de cálculo CONTAR.SI. Para calcular una cantidad total basada en una sola condición, utiliza la función de hoja de cálculo SUMAR.SI. Para devolver un valor de dos, por ejemplo el porcentaje de bonificación, utiliza la función de hoja de cálculo SI. Imagina que deseas crear un resumen que muestre, para cada vendedor, el número total de pedidos hechos y la cantidad total facturada durante un período determinado. Para contar el número de pedidos hecho, utiliza la función CONTAR.SI. Para calcular la cantidad total facturada, utiliza la función SUMAR.SI. La función CONTAR.SI cuenta el número de pedidos hechos por cada vendedor. 13 CONTAR.SI tiene dos argumentos: el rango que debe comprobarse y el valor que debe buscarse dentro del rango (el criterio). =CONTAR.SI(rango;criterio) Para Buchanan, la función (de la celda B32) tiene esta forma: =CONTAR.SI(A2:A26;A32) La función cuenta el número de veces que el nombre de la celda A32 (el argumento criterio) aparece en la lista Vendedores (A2:A26, el argumento rango). La función SUMAR.SI calcula la cantidad total facturada por cada vendedor. La función SUMAR.SI busca un valor dentro de un rango y después suma todos los valores correspondientes en otro rango. SUMAR.SI tiene tres argumentos: el rango que debe comprobarse, el valor que debe buscarse dentro del rango (el criterio) y el rango que contiene los valores que deben sumarse. =SUMAR.SI(rango_de_búsqueda;criterio;rango_suma) Para Buchanan, la función (de la celda C32) tiene esta forma: =SUMAR.SI(A2:A26;A32;B2:B26) La fórmula busca el texto de la celda A32 (el argumento criterio) en la lista Vendedores (A2:A26, el argumento rango_de_búsqueda) y después suma las cantidades correspondientes de la columna Total factura (B2:B26 el argumento rango_suma). Empleo de la función SI Imagina que la organización determina las bonificaciones por venta en una escala variable que otorga un 10 o un 15 por ciento según la cantidad facturada. Para determinar cuál de los dos valores usar, basándose en una condición que puede ser verdadera o falsa, utiliza la función SI. La función de hoja de cálculo SI devuelve una bonificación del 10% o del 15% según la cantidad facturada. La función SI comprueba una condición que ha de ser verdadera o falsa. Si la condición es verdadera, la función devuelve un valor y, si es falsa, 14 devuelve otro valor. Esta función tiene tres argumentos: la condición que se desea comprobar, el valor que se devolverá si la condición es verdadera y el valor que se devolverá si la condición es falsa. =SI(prueba_lógica;valor_si_verdadero;valor_si_falso) Para la facturación de 8.000 de Suyama, la función (de la celda C4) tiene esta forma: =SI(B4<10000;10%;15%) Si la cantidad facturada es inferior a 10.000 $ (el argumento prueba_lógica), la bonificación será del 10 por ciento (el argumento valor_si_verdadero). Si la cantidad facturada es 10.000 $ o más, la bonificación será del 15 por ciento (el argumento valor_si_falso). 2. Aplicaciones de Funciones Matemáticas relacionadas a Tendencias Centrales Función Mediana Problema Ejemplo: MEDIANA(1; 2; 3; 4; 5) es igual a 3 Observaciones Devuelve la medianade los números. La mediana es el número que se encuentra en medio de un conjunto de números, es decir, la mitad de los números es mayor que la mediana y la otra mitad es menor. MEDIANA(número1;número2; ...) Número1;número2; ... son entre 1 y 30 números cuya mediana desea obtener. Los argumentos deben ser números o nombres, matrices o referencias que contengan números. Microsoft Excel examina todos los números en cada argumento matricial o de referencia. Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, esos valores se pasan por alto; sin embargo, se incluirán las celdas cuyo valor sea 0. Si la cantidad de números en el conjunto es par, MEDIANA calcula el promedio de los números centrales. Por ejemplo: MEDIANA(1; 2; 3; 4; 5; 6) es igual a 3,5 (es decir que es el promedio de 3 y 4). 15 3. Aplicación de Funciones Financieras relacionadas a Depreciación Función SLN (Depreciación por Método Directo) Problema Ejemplo Supongamos que en la oficina de una planta tú estés comprando algunas máquinas nuevas. El costo de las máquinas es $100,000 y tienen una vida útil de 10 años. El valor de rescate (valor residual) de las máquinas es de $12,000. Utilizando la depreciación por método directo, podrás conocer cuál es la depreciación permitida para cada año. Observaciones: La función de SLN devuelve la depreciación de un activo por un período específico. La función pide lo siguiente: SLN(Cost, salvage, life) [SLN(costo, valor_residual, vida)]: Cost es el costo inicial del activo, el cual en este ejemplo es 100,000. Salvage es el valor de rescate (valor residual) del activo al final de la depreciación, el cual en este ejemplo es 12,000. Life es el número de períodos por el cual el activo va a ser depreciado, el cual en este ejemplo es 10. Como resultado, la función y la depreciación permitida para cada año es la siguiente: SLN(100000, 12000, 10) = $8,800 ¿Entendiste el concepto de Depreciación? Confírmalo con algún profesor en cuya materia pueda aplicarse este concepto. 4. Aplicación de otras Funciones Financieras Función FV (Valor Futuro) Problema Ejemplo: Supongamos que deseas crear un fondo de ahorro para Navidad. Entonces realizas un depósito de $500.00 dentro de una cuenta de ahorros que gana el 6% de interés anual compuesto mensualmente. Planeas depositar $100.00 al principio de cada mes durante los próximos 10 meses. ¿Qué cantidad obtendrás al final de los 10 meses? Observaciones: La función de FV le pide los siguientes datos, FV(rate, nper, pmt, PV, type) [VF(tasa, nper, pago, va, tipo)]: rate es la tasa de interés por período, el cual en este ejemplo es el 6% anual, el 6% / 12. nper es el número total de períodos de pago, el cual en este ejemplo es 10. pmt es el pago hecho para cada período, el cual en este ejemplo es $100.00. pv es el valor actual, o sea, la cantidad que vale ahora, en este ejemplo es $500.00 del depósito inicial. type es 0 ó 1, dependiendo de cuándo se realicen los pagos, al final (0) o al inicio (1) de cada período. Si es omitido, se asume que será 0. En este ejemplo es 1. 16 Como resultado, nuestra función es la siguiente: FV(5%, 10, -100, -500,1), la cual es igual a $1,553.49. 5. Aplicación de Funciones Estadísticas Función PEARSON (Coeficiente de Correlación producto o momento r) Problema ejemplo: Supongamos que tus empleados tomen una prueba para medir la agilidad manual. Tienes la impresión de que la edad de los empleados está relacionada con las calificaciones de las pruebas. Diez empleados reportaron sus calificaciones de la prueba de agilidad manual. ¿Existe alguna correlación entre la edad de los empleados y las calificaciones de la prueba? Tabla para la aplicación de la Función PEARSON. Observaciones: La función de PEARSON devuelve el coeficiente de correlación producto o momento r de Pearson. En este caso ‘r’ es un índice que está entre –1.0 y 1.0 que refleja el grado de dependencia lineal entre dos conjuntos de datos. La función le pide lo siguiente: PEARSON(Array1, Array2) [PEARSON(matriz1, matriz2)]: Array 1 es un conjunto de valores independientes. Array 2 es un de conjunto valores dependientes. Como resultado, la función para el problema ejemplo es la siguiente: PEARSON(B2:B11, C2:C11). El coeficiente de correlación producto o momento r en este caso es r = 0.971591. En otras palabras, efectivamente existe una alta correlación entre la edad de los empleados y la calificación de la prueba de agilidad manual. Empleado Edad Prueba 1 35 93 2 25 96 3 52 87 4 40 90 5 26 94 6 55 86 7 61 84 8 30 93 9 47 91 10 66 84
Compartir