formulas de excel

Sistemas de Información I Silvia S. Storni

1

Ficha Fórmulas

Grupo Biblioteca de funciones

− Insertar funciones Insertar funciones es un cuadro de diálogo que simplifica la introducción de funciones en una fórmula. Para iniciar Insertar funciones, se debe: 1) hacer clic en el botón Insertar funciones de la barra de de fórmulas o 2) seleccionar el botón Insertar Función del grupo Biblioteca de funciones de la ficha Fórmulas o 3) seleccionar la Opción Más funciones del botón Autosuma de la grupo Modificar de la ficha Inicio o 4) seleccionar la opción Insertar función de cualquiera de las listas de las categorías de funciones del grupo Biblioteca de funciones de la ficha Fórmulas o 5) seleccionar la opción Más funciones del cuadro de Funciones de la barra de Fórmulas. Este cuadro de diálogo permite en Buscar una función escribir una descripción breve de lo que se desea que haga una función que se busca y en el cuadro Seleccionar una función se mostrará una lista de funciones posibles que se ajustan a los requisitos especificados en la descripción proporcionada. Las funciones están agrupadas por categoría, tales como "Financieras", "Matemáticas y trigo-nométricas", "Estadísticas", "Búsqueda y referencia", "Fecha y hora", etc. Seleccionar una categoría Es una lista desplegable, que permite seleccionar entre las categor-ías: Usadas recientemente: para las funciones que se hayan insertado últimamente, Una ca-tegoría específica: para las funciones de dicha categoría y Todas: que mostrará todas las funciones, tanto las integradas y como las definidas por el usuario. En todos los casos las fun-ciones de la categoría seleccionada se mostrarán en orden alfabético en el cuadro Seleccionar una función. Seleccionar una función enumera todas las funciones incorporadas y personalizadas que están disponibles en la categoría seleccionada, mientras, en el área de presentación en la parte infe-rior se describe brevemente que realiza la función seleccionada y muestra la sintaxis (la sin-taxis determina la posición correcta de los argumentos, los separadores -puntos y comas- y paréntesis).

Sintaxis de las funciones Cada descripción de una función incluye una línea de sintaxis. Por ejemplo, la línea de sin-taxis de la función DIASEM es la siguiente: DIASEM(núm_de_serie;tipo) En la línea de sintaxis, los argumentos requeridos aparecen en negrita y los argumentos op-cionales aparecen en texto normal. Si no se indican los argumentos requeridos para una fun-ción, Excel no permitirá introducirla en una celda. En el ejemplo anterior, el argumento núm_de_serie está en negrita y por lo tanto es requerido; el argumento tipo no está en negrita


2

lo que significa que es opcional. Cuando un argumento es opcional no significa que si el usua-rio no lo indica la función se resuelva sin él, sino que, Excel asume un valor predeterminado para dicho argumento. Cualquiera de los ejemplos siguientes son válidos: DIASE-M("2/14/90"); DIASEM("15-4-93";2); pero DIASEM() no es válido. Cuando un argumento va seguido de puntos suspensivos (...), puede introducir varios argu-mentos con el mismo tipo de datos. Algunas funciones aceptan hasta 255 argumentos, siem-pre y cuando el número total de caracteres en la fórmula no exceda de 1.024. Por ejemplo, la sintaxis para la función MAX es la siguiente: MAX(número1;número2;...) Cualquiera de las siguientes fórmulas es válida: MAX(C7:C11;A7:A11;D7:D11;A7;C7) MAX(A6:A13;26;31); MAX(26;31;29). En las funciones que no requieren argumentos introducidos por el usuario deben incluirse de todas maneras los paréntesis de apertura y cierre, ya que es la condición para que Excel las re-conozca como tales.

Nombres de argumentos Muchos nombres de argumentos indican el tipo de información que se debe usar como tal. Si la abreviatura núm, ref o valor_lógico aparece en el nombre del argumento, entonces ese ar-gumento será un número, una referencia de celda o un valor lógico respectivamente. Por ejemplo, en la función IZQUIERDA(texto;núm_de_caracteres), el primer argumento debe ser una cadena de caracteres y el segundo un número. Del mismo modo, las palabras número, referencia, lógico, texto y matriz en el nombre de un argumento especifican que el argumento debe corresponder a ese tipo de datos ingresado di-rectamente u obtenido del contenido de una celda o del resultado de otra función. El argumento Valor significa que el dato puede corresponder a cualquier un valor que sea úni-co. Dicho dato puede ser un número, un texto, un valor lógico o uno de error.

Argumentos

Los argumentos son la información que una función utiliza para producir un valor nuevo o pa-ra realizar una acción. Los argumentos siempre se colocan a la derecha del nombre de la fun-ción y deben ir entre paréntesis. La mayoría de los argumentos pertenecen a un determinado tipo de datos. El argumento que introduzca debe corresponder al tipo adecuado o a algún otro tipo que Excel pueda convertir en el tipo adecuado. Se puede usar cualquier tipo de argumento siempre que produzca el tipo de datos deseado. Por ejemplo, la función SUMA, que suma los argumentos, puede aceptar entre 1 y 255 argumen-tos. La función SUMA acepta los siguientes argumentos ya que devuelven uno o varios números: − Un valor que sea un número, por ejemplo: SUMA(1;10;100) o una función o fórmula cuyo

resultado sea un número, por ejemplo: SUMA(0,5+0,5; PROMEDIO(5;5); 10^2;A3:A20) El uso de una función como argumento de otra función, como en el ejemplo anterior, se llama anidamiento de funciones. En este ejemplo, la función PROMEDIO es un argumento de la función SUMA. En una fórmula es posible tener hasta sesenta y cuatro niveles de funciones anidadas.

− Una referencia a una celda o rango de celdas que contiene un número o una fórmula que da como resultado un número, por ejemplo: SUMA(A1;A2); SUMA(A1:A5) El segundo ejemplo equivale a la fórmula SUMA(A1;A2;A3;A4;A5). La ventaja de utili-zar un rango como argumento es que el argumento A1:A5 cuenta con un solo argumento mientras que la otra forma cuenta con cinco argumentos. Si desea sumar más de 255 números, se tendrán que agrupar en rangos de celdas, porque las funciones sólo aceptan hasta 255 argumentos.

− Un nombre que haga referencia a un valor, una fórmula, una celda o un rango de celdas


3

que contiene un valor o una fórmula, por ejemplo: SUMA(ValorDeAjuste;Inflación).

Tipos de argumentos Los argumentos de una función pueden ser los siguientes:

• Números – Fechas • Referencias – Nombre

• Texto • Valores de error

• Valores lógicos • Funciones

• Números Por ejemplo: 5,003; 0; 150,286 y -30,05. Los números pueden ser enteros o con decimales y pueden alcanzar una precisión de 15 posiciones (0,123456789012345 o 11,1234567890123 o 165257,123456789).

• Texto Por ejemplo: "a", "Palabra", "c/punt." o "". Los valores de texto utilizados en fórmulas de-ben ir entre comillas dobles. Si el texto contiene comillas, usar dos comillas dobles para cada comilla doble que aparezca en el texto. Por ejemplo, para obtener los 7 caracteres del texto "Buenas Noches", se debe introducir la fórmula: =DERECHA("""Buenas No-ches""";7) y devolverá Noches” Las cadenas de texto para Excel pueden tener hasta 32000 caracteres pero cuando se re-quieren en una fórmulas es conveniente ingresarlas como contenido de una celda y refe-renciar la misma ya que las fórmula tiene la limitación de los 1.024 caracteres como máxima longitud. Una constante de texto que no contiene caracteres, se escribe como "" y se llama "texto vacío". Si no pone entre comillas el texto que se usa como argumento, Excel supondrá que es un nombre de rango e intentará reemplazarlo con el valor al que hace referencia ese nombre. Si dicho texto sin comillas no es un nombre de rango, y por lo tanto no tiene ningún valor asociado, Excel devuelve el valor de error #¿NOMBRE?

• Valores lógicos Los valores lógicos son VERDADERO y FALSO. Los argumentos lógicos también pue-den ser enunciados, por ejemplo B10>20 cuyo resultado será VERDADERO o FALSO.

• Valores de error Ejemplos de valores de error son #¡DIV/0!, #N/A, #¿NOMBRE?, #¡NULO!, #¡NUM!, #¡REF! y #¡VALOR!

• Referencias Ejemplos de referencias son $A$10; A10; $A10; A$10; L1C1 o L[10])C[(-10]). Pueden ser referencias a celdas individuales, a rangos o a selecciones múltiples, y pueden ser relativas, absolutas o mixtas. Cuando se usa una referencia como argumento se supone es un núme-ro, texto, valor de error o valor lógico, el contenido de las celdas especificadas por la refe-rencia. Las funciones que devuelven referencias muestran el valor contenido en la celda de la refe-rencia, en lugar de la dirección misma. Para indicar una selección múltiple como argumento único de la referencia, encierre la re-ferencia en un segundo par de paréntesis, por ejemplo: SUMA((E5:E8;E10:E18); PRO-MEDIO(A1:A5))


4

1º Para anidar una función hacer clic en cuadro de texto del argumento en el cual se colocará la 2ª función.

2º Hacer clic en la flecha del cuadro de Funciones de la

barra de fórmulas y seleccio-nar la función a ser usada

como argumento

3º Para volver a la 1ª fun-ción, hacer clic sobre el nombre del misma en la barra de fórmulas

• Funciones

Cuando una función se utiliza como argumento de otra, se denominan funciones Anidadas permitiendo Excel un máximo de 64 niveles de anidamientos. Cuando se anidada una fun-ción, ésta deberá devolver el mismo tipo de dato que el que utilice el argumento. Se puede utilizar el cuadro de diálogo Argumentos de función para facilitar el anidar funciones. Por ejemplo, se puede anidar la función PROMEDIO como argumento de la función SI haciendo clic en el cuadro de texto Valor_si_verdadero, luego haciendo clic en Más fun-ciones.... del cuadro Funciones de la barra de fórmulas y finalmente, seleccionando la fun-ción PROMEDIO en el cuadro de diálogo Insertar funciones. Al igual que cuando se selec-ciona una función sin anidar en Insertar funciones, se abrirá el cuadro de diálogo Argu-mentos de función de la función anidada. Para cambiar el cuadro de diálogo Argumentos de función de una función anidada a otra, se debe hacer clic en la barra de fórmulas en nombre de la función de la que se desea visualizar los argumentos y su resultado.

Uso del punto y coma en una lista de argumentos Si se introduce una función sin hacer uso del cuadro de diálogo Insertar Función los argumen-tos se deben separar con puntos y comas. Se debe evitar escribir puntos y comas innecesarios ya que indican el lugar de un argumento que no se introduce, y Excel lo sustituirá por el valor predeterminado para ese argumento, si se trata de un argumento opcional o dará un error si es


5

obligatorio. Por ejemplo, si se introduce (arg1;;), Excel sustituye arg2 y arg3 por valores ade-cuados. En las funciones para hojas de cálculo y en particular en las funciones que cuentan el número de argumentos antes de calcular, cada punto y coma adicional afecta al cálculo de argumentos y, por lo tanto, a la forma en que se calcula la función. Por ejemplo, PROMEDIO(1;2;3;4;5) devuelve 3, pero PROMEDIO(;;1;2;3;4;5) devuelve 2,14. Para la mayoría de los argumentos el valor utilizado para sustituir un argumento omitido es 0, FALSO o "" (texto vacío), dependiendo del tipo de datos que deba tener el argumento. Cuan-do los argumentos tipo referencia se omiten, el valor predeterminado usado es generalmente la celda o la selección activa.

− Autosuma

Coloca automáticamente un signo = y la función SUMA, quedan-do a la espera de él o los argumentos a sumar. Despliega una lista con las funciones suma, promedio, contar número, máximo y mínimo, con las cuales coloca lo mismo que con la suma. En todos lo caso por debajo de donde se encuentra la fórmula muestra la sintaxis de la función seleccionada. La opción Más funciones abre el cuadro de diálogo Insertar fun-ción que permite acceder a la totalidad de las funciones disponi-bles.

− Listados con las distintas categorías de funciones

Grupo Nombres definidos

Un nombre es una forma de referirse a una referencia de celda, rango, constante, fórmula o tabla, cuyo propósito es hacer más clara la lectura de una fórmula. Tipos de nombres Se pueden crear y utilizar dos tipos de nombres. a) Nombre definido Es un nombre que representa una celda, un rango de celdas, una fórmula o

un valor de constante. Como usuario se puede crear sus propios nombres definidos aunque, en ocasiones, Excel los crea automáticamente; por ejemplo, cuando se establece un área de impresión.

b) Nombre de tabla Nombre de una tabla de Excel, que es un conjunto de datos relacionados que se guardan en registros (filas) y campos (columnas). Excel crea nombres de tabla prede-terminados, como Tabla1, Tabla2, etc. que el usuario luego puede cambiar.

Nombre Pautas para la asignación de nombres:


6

a) Puede contener letras, números, subrayados ( _ ), barras inversas (\), puntos (.) y signos de interrogación (?). Sin embargo, el primer carácter debe ser una letra, un subrayado ( _ ) o una barra inversa (\).

b) No se pueden usar espacios en blanco. c) El nombre puede tener hasta 255 caracteres de largo. d) No se permiten los nombres que son semejantes a números o referencias de celdas.

Crear nombres Se puede crear un nombre con cualquiera de estos métodos: a) Cuadro Nombre de la barra de fórmulas Es la forma más rápida de crear un nombre en el

nivel de libro para un rango seleccionado. b) Crear un nombre a partir de una selección se puede crear nombres fácilmente partiendo

de los rótulos de fila y de columna existentes mediante una selección de celdas de la hoja de cálculo.

c) Cuadro de diálogo Nombre nuevo Es el método más adecuado si desea tener más flexibili-dad al crear nombres; por ejemplo, especificar un ámbito de nivel de hoja de cálculo local o crear un comentario de nombre.

− Administrador de Nombre Abre el cuadro de diálogo Administrador de nombres para trabajar con todos los nombres de-finidos y de tabla del libro. Permite ordenar y filtrar la lista de nombres y, agregar, cambiar o eliminar nombres de una ubicación. En el cuadro de diálogo Administrador de nombres se muestra la siguiente información so-bre cada nombre de un cuadro de lista: Esta columna: Muestra:

Icono y nombre Nombre definido que se indica mediante un icono de nombre definido. Nombre de tabla que se indica mediante un icono de nombre de ta-

bla.

Valor Valor actual del nombre, como los resultados de una fórmula, una cons-tante de cadena, un rango de celdas, un error, una matriz de valores o un marcador si la fórmula no se puede calcular. Por ejemplo: "ésta es mi constante de cadena"


7

3,1459 {2003;12,2002;23,;2001,18} #REF! {...}

Hace referencia a: La referencia actual del nombre. Por ejemplo =Hoja1!$A$3 =8,3 =HR!$A$1:$Z$345 =SUMA(Hoja1!A1,Hoja2!B2)

Ámbito Nombre de una hoja de cálculo, si el ámbito está en el nivel de la hoja de cálculo local. Libro", si el ámbito está en el nivel de hoja de cálculo global.

Comentario Información adicional sobre el nombre cuya longitud tiene un máximo de 255 caracteres. Por ejemplo: Este valor caducará el 2 de mayo de 2007. No eliminar! Nombre crítico! Basado en los números de exámenes de certificación ISO.

− Nuevo…

Abre el cuadro de diálogo Nombres nuevo para crear un nombre definido. − Editar…

Abre el cuadro de diálogo Nombres nuevo para modificar lo establecido para un nombre definido excepto el Ámbito.

− Eliminar Elimina el nombre seleccionado en el cuadro Administrador de nombres

− Filtro Muestra un subconjunto de nombres que cumple con la condición seleccionada. Al selec-cionar cada comando, se activa o desactiva la operación de filtrado, lo que facilita combi-nar o quitar distintas operaciones de filtrado con el fin de obtener los resultados deseados. Para filtrar una lista de nombres, se debe puede utilizar: Nombres en el ámbito de la hoja de cálculo

Muestra sólo los nombres que son locales respecto a una hoja de cálculo.

Nombres en el ámbito del libro

Muestra los nombres que son globales respecto a del libro.

Nombres con errores Muestra sólo los nombres con valores que contengan errores (como #REF, #VALOR, #NOMBRE, etc.)

Nombres sin errores Muestra sólo los nombres con valores que no contengan errores.

Nombres definidos Muestra sólo los nombres definidos por el usuario o por Excel, como un área de impresión.

Nombres de tabla Muestra sólo los nombres de las tablas.

− Hace referencia a

Muestra la referencia actual del nombre permitiendo modificarla mediante Contraer diálo-go (lo que oculta temporalmente el cuadro de diálogo), seleccionando las celdas de la hoja de cálculo y, a continuación, presione Expandir diálogo .


8

− Asignar nombre a un rango − Nombre nuevo

Crea un nombre para una celda o un rango. Puede utilizar este nombre para referirse a la celda o al rango. Los nombres hacen que las fórmulas sean más fáciles de leer, entender y mantener. Es posible cambiar o eliminar los nombres definidos anteriormente y defi-nir las constantes o fórmulas que piensa utilizar más adelante. Cuando se selecciona una celda con nombre o un rango completo con nombre, los nombres aparecen en el área de referencia de la barra de fórmulas. − Nombres

Enumera todos los nombres actualmente definidos en el libro de trabajo. Cuando se selecciona un nombre de la lista, el nombre aparece en el cuadro de edi-ción encima del cuadro de lista y su referencia aparece en el cuadro “Se refiere a”. Si la celda activa contiene texto, dicho texto aparece en el cuadro de edición como el nombre propuesto. De otra manera, cualquier texto en la celda que está a la izquier-da o encima de la celda activa aparece en el cuadro de edición. Se puede aceptar o editar el nombre propuesto, escribir un nombre nuevo o seleccio-nar uno en el cuadro “Nombres en el libro”. Si se utiliza un nombre que ya ha sido definido, la definición nueva reemplazará a la definición anterior.

− Ámbito Todos los nombres tienen un ámbito: a) Para una hoja de cálculo específica (denominado nivel de hoja de cálculo local) b) Para todo el libro (denominado nivel de libro global).

Si ha definido un nombre, y su ámbito es el libro, ese nombre se reconoce en todas las hojas de cálculo de ese libro, pero en ningún otro libro. Un nombre debe ser único en su ámbito. Excel impide que el usuario defina un nom-bre que no sea único en su ámbito. Sin embargo, puede usar el mismo nombre en ámbitos distintos. Por ejemplo, puede definir un nombre, cuyo ámbito sean Hoja1, Hoja2 y Hoja3 en el mismo libro. Aunque cada nombre sea el mismo, cada uno de ellos es único en su ámbito. Debería hacerlo así para asegurarse de que una fórmula que use el nombre, siempre haga referencia a las mismas celdas en el nivel de hoja de cálculo local. Incluso puede definir el mismo nombre, para el nivel de libro global, pero nuevamen-te el ámbito es único. En este caso, no obstante, se puede producir un conflicto de nombres y para resolverlo, de forma predeterminada, Excel usa el nombre definido para la hoja de cálculo, ya que el nivel de hoja de cálculo local tiene prioridad res-pecto al libro global. Si desea reemplazar la prioridad y usar el nombre del libro,


9

puede eliminar la ambigüedad del nombre si coloca un prefijo en el nombre del libro. − Hace referencia a

Presenta la referencia para la celda o el rango seleccionado. Puede ser un rango de celdas o una sola celda. Si se selecciona un nombre definido anteriormente, este cuadro propondrá el rango, el valor o la fórmula a la que ese nombre se refiere actualmente. Se puede editar una referencia. Mientras se hace, también es posible realizar selec-ciones en la hoja de cálculo, elegir comandos para la edición de fórmulas, cambiar y mover ventanas.

− Comentario Observación descriptiva con una longitud máxima de 255 caracteres

− Aceptar Agrega la entrada actual de los cuadros Nombres, Se refiere y Ámbito a en la lista de nombres definidos y luego cierra el cuadro de diálogo.

− Cerrar Cierra el cuadro de diálogo Definir nombre sin agregar la entrada actual en la lista de los nombres definidos.

− Aplicar nombres Busca en las fórmulas y reemplaza las referencias de celdas con nombres definidos para las mismas, siempre que estén seleccionados en la lista Aplicar nombre. − Aplicar nombres

Enumera todos los nombres definidos en un libro de trabajo y permite seleccionar el nombre o los nombres que se desea se coloquen en lugar de las referencias de celdas.

− Ignorar relativa/absoluta Permite reemplazar las referencias de celdas con nombres sin considerar los tipos de direcciones (absolutas o relativas) referencias de los nombres o de las referencias de las fórmulas. Si se desactiva esta casilla de verificación, se reemplazan las referencias absolutas en las fórmulas por nombres cuando estos tengan rangos de direcciones absolutos, las referencias relativas en las fórmulas por nombres cuando estos tengan rangos de di-recciones relativos y las referencias mixtas por nombres cuando estos tengan rangos de direcciones mixtas.


10

− Usar nombres de filas y columnas Posibilita usar los nombres de rangos de filas y de columnas que contengan las cel-das a las que se hace referencia, si no se encuentran nombres precisos para las celdas.

− Opciones Presenta las opciones adicionales siguientes: − Omitir nombre de columna

Si la celda a la que hace referencia está en la misma columna que la fórmula y en un rango con nombre a partir de encabezados de fila, reemplaza la referencia con un nombre a partir de encabezados de fila sin incluir un nombre a partir de encabezados de columna.

− Omitir nombre de fila Si la celda a la que hace referencia está en la misma fila que la fórmula y en un rango con nombre a partir de encabezados de columna, reemplaza la referencia con un nombre a partir de encabezados de columna sin incluir un nombre a partir de enca-bezados de fila.

− Orden de nombres Determina qué nombre de rango se enumera primero cuando una referencia de celda es reemplazada por un nombre de rango a partir de encabezados de fila al igual que encabezados de columna.

− Utilizar en la fórmula Muestra el listado de nombres definidos que pueden ser utilizados en la hoja activa. − Pegar nombres…

Muestra el cuadro de diálogo Pegar nombre que permite inserta un nombre de defi-nido seleccionado en la barra de fórmulas. Si la barra de fórmulas está activa y el usuario comienza una fórmula escribiendo un signo igual (=), al elegir el comando Pegar pegará el nombre seleccionado en el pun-to de inserción. Si la barra de fórmulas no está activa, elegir Pegar nombre la acti-vará y pegará un signo igual (=) seguido por el nombre seleccionado. − Pegar nombre

Enumera todos los nombres definidos en el ámbito de la hoja de cálculo activa y en el ámbito global. Para pegar en la barra de fórmulas uno de los nombres que aparecen en la lista de nombres definidos, se debe resaltar el nombre y luego se-leccionar Aceptar o hacer doble clic sobre el nombre.

− Pegar lista Pega todos los nombres definidos que pueden ser usados en el ámbito donde se pega lista, comenzando en la celda activa y en orden alfabético. En una hoja de


11

cálculo, la lista de nombres es un área de dos columnas de ancho. La columna iz-quierda enumera los nombres; la derecha las referencias de celda a las que se re-fieren los nombres. En una hoja de macros, la lista de nombres tiene un ancho de cinco columnas e incluye una columna de nombre, una columna de referencia, una columna para el tipo de macro (0=ninguna, 1=función, 2=comando), una co-lumna para las teclas asignadas para el método abreviado y una columna para la categoría.

− Crear desde la selección Crea nombres usando rótulos del rango seleccionado.

De un rango seleccionado utiliza el texto que se encuentra en la fila superior o inferior, la columna izquierda o derecha, o cualquier combinación para nombrar las otras celdas en el rango. Dicho rango debe posee filas y/o columnas en las posiciones antes mencionadas en las que se encuentren los textos que se desean utilizar como nombres de rango y que además cumplan con los requisitos para ser utilizados como tal.

En el rango seleccionado

Crear nombres con la Nombra a las celdas de Fila superior Las filas por debajo Columna izquierda Las columnas de la derecha Fila inferior Las filas por encima Columna derecha Las columnas de la izquierda

Si la celda en el borde de la selección contiene un número al que ha dado el formato de fe-cha, Excel convierte la fecha a texto para crear un nombre. Si un nombre ya ha sido defi-nido en la hoja de cálculo, Excel pregunta si desea reemplazar la definición existente.

formulas de excel

Documents