1.¨COPIADO DE FORMULAS RELATIVAS Y ABSOLUTAS¨

Una referencia le indica a Excel donde buscar los valores que se desean utilizar en una fórmula.

Existen 2 tipos de referencias: Referencias relativas y Referencias absolutas.

REFERENCIAS RELATIVAS: Por lo general cuando se crea una fórmula, las referencias a las celdas se basan en su posición relativa a la celda que contiene la fórmula. Esta característica es muy útil cuando se copia una fórmula a otra celda, ya que las direcciones de las celdas se actualizan de acuerdo a su nueva posición,

Utilizaremos el siguiente ejemplo: En la celda D2 (Total) , está escrita la fórmula (=B2*C2) que multiplica el valor de la celda B2 (precio), por el valor de la celda C2 (cantidad).

Cuando se copia una fórmula a otras celdas se copian con su posición relativa. Veamos cómo se vería copiada la fórmula de la celda D2 en las celdas D3, D4 y D5:

 

Observe que al copiar la fórmula original a la celda D3 las direcciones de las celdas se actualizan automáticamente, ya que Excel interpreta que la fórmula original ha cambiado de posición una fila abajo; por eso la fila 2 cambia a ser la fila 3. Pero habrá ocasiones en que no desee que las referencias cambien cuando se copian las fórmulas a otras celdas distintas; entonces lo que necesitará son celdas con referencias absolutas.

REFERENCIAS ABSOLUTAS: Una referencia absoluta es una referencia en la fórmula, que no cambia cuando se copia a otra celda. Al utilizar referencias absolutas la dirección de la celda se mantiene cuando es copiada a otras celdas. Esta es una herramienta poderosa cuando se está trabajando en una hoja de cálculo con muchos datos. Para crear una referencia absoluta coloque el signo de pesos ($) antes de la columna (letra) o el renglón (número) que no desee que cambie. Por ejemplo: la referencia de celda $C$4 es una referencia absoluta; sin embargo la referencia de celda C4 es una referencia relativa. Si copia una fórmula que contiene la referencia absoluta $C$4 a otra celda, la referencia de celda $C$4 no cambia, pero si copia una fórmula que contiene la referencia relativa C4 a otra celda, la referencia C4 si cambia. Utilizaremos la siguiente imagen como ejemplo: Para obtener el costo de los artículos en pesos, es necesario multiplicar el precio en dólares de los productos, por el tipo de cambio que es la celda (B2).

En en la celda C6 se ha escrito una fórmula sin referencia absoluta (=B6*B2); pero como el tipo de cambio únicamente se encuentra en la celda B2, necesitamos hacer que esa celda sea de referencia absoluta en la fórmula. Entonces

colocaremos para la referencia de celda B2, un signo de pesos ($) antes de la letra y antes del número.

Referencias relativas

Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas. Por ejemplo, si copia una referencia relativa de la celda B2 a la celda B3, se ajusta automáticamente de =A1 a =A2.

Fórmula copiada con referencia relativa

 Referencias absolutas

Una referencia de celda absoluta en una fórmula, como $A$1, siempre hace referencia a una celda en una ubicación específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias absolutas. Por ejemplo, si copia una referencia absoluta de la celda B2 a la celda B3, permanece invariable en ambas celdas =$A$1.

Fórmula copiada con referencia absoluta

 Referencias mixtas

Una referencia mixta tiene una columna absoluta y una fila relativa, o una fila absoluta y una columna relativa. Una referencia de columna absoluta adopta la forma $A1, $B1, etc. Una referencia de fila absoluta adopta la forma A$1, B$1, etc. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia relativa y la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia relativa se ajusta automáticamente y la referencia absoluta no se ajusta. Por ejemplo, si se copia una referencia mixta de la celda A2 a B3, se ajusta de =A$1 a =B$1.

Fórmula copiada con referencia mixta

Utilizando referencias absolutas y relativas

En Excel puedes especificar tres tipos de referencias: relativas, absolutas y mixtas. Al utilizar el tipo de referencia adecuado nos aseguramos de que al copiar la fórmula a una nueva ubicación seguirá funcionando adecuadamente.

Si tienes una fórmula que hace referencia a otras celdas y copiamos esa fórmula a otra ubicación, Excel actualizará automáticamente las referencias de la fórmula para adecuarlas a la nueva ubicación.  Considera el siguiente ejemplo.

La celda C1 hace la suma de A1 y B1. Observa lo que sucede si copiamos el contenido de la celda C1 y lo pegamos en la celda C2.

Excel ha sido tan inteligente que ha supuesto que la fórmula de la celda C2 debe sumar los valores de A2 y B2. Si volvemos a copiar la fórmula y pegarla en la celda C3 obtendremos la fórmula: =A3 + B3.

Referencias relativas

Cuando copiamos fórmulas Excel utiliza el tipo de referencia relativa que es el comportamiento predeterminado. Este tipo de referencia se adapta al contexto de la nueva celda cambiando las referencias de acuerdo a la nueva ubicación. Sin embargo, en algunas ocasiones no queremos tener este comportamiento sino que queremos hacer referencia siempre a una celda específica sin importar que copiemos la fórmula a otra ubicación y para ello necesitamos utilizar las referencias absolutas.

Referencias absolutas

Para fijar la columna o fila de una referencia utilizamos el signo $ y lo colocamos antes de la letra de la columna o del número  de fila de la referencia. Así por ejemplo podemos tener una referencia absoluta que fija tanto la columna como la fila, por ejemplo: $A$1

Referencias mixtas

Las referencias mixtas son aquellas donde solamente se fija la columna ($A1) o solamente se fija la fila(A$1).

Atajo para crear referencias absolutas y mixtas

Una manera muy sencilla de crear una referencia absoluta es editar la fórmula y posicionar el cursor sobre la referencia relativa que deseamos convertir en referencia absoluta. Posteriormente debemos presionar la tecla F4 lo cual insertará el signo $ tanto para la columna como para el número de fila.

Si pulsas dos veces la tecla F4 observarás que Excel  solamente inserta el signo $ para la fila y si pulsas la tecla F4 tres veces se creará una referencia mixta con la columna fija. Al pulsar una cuarta vez la tecla F4 se removerán todos los signos $ y tendremos de nuevo una referencia relativa.

Una referencia en Excel identifica a una celda (o rango de celdas) dentro de una hoja de trabajo y le hace saber a Excel en dónde buscar los valores que se quieren utilizar en una fórmula. Pero ¿Cuál es la diferencia entre las referencias absolutas y las referencias relativas?

Referencias relativas en Excel

Una referencia relativa es cuando Excel puede modificar libremente dicha referencia para ajustarla al utilizarla dentro de una fórmula. Por ejemplo, si la fórmula de la celda D1 es la siguiente:

=C1*2

Si arrastramos el controlador de relleno hacia abajo, Excel copiará la fórmula y la ajustará de manera que la referencia se modifique automáticamente conforme va avanzando la fila.

En este ejemplo la referencia C1 de la fórmula se fue incrementando automáticamente a C2, C3, C4 y C5 conforme fue cambiando de fila.

Referencias absolutas en Excel

Ahora analicemos el caso de las referencias absolutas. Una referencia es absoluta cuando Excel no la puede ajustar para adaptarse a la fórmula conforme cambia de fila o de columna. Las referencias absolutas permanecen constantes sin importar a dónde se copie la fórmula y se definen utilizando el símbolo “$”. Por ejemplo, la referencia $A1 significa que en esta referencia la columna A será siempre fija mientras que la  fila podría ajustarse automáticamente. Por otro lado, la referencia A$1 significa que la fila 1 permanecerá siempre fija. Si quieres que tanto la columna como la fila permanezcan siempre fijas la referencia debe ser $A$1.

Con un ejemplo similar al anterior veamos lo que sucede cuando hacemos que la referencia sea absoluta. Nota que ahora la fórmula de la celda D1 está escrita de la siguiente manera:

=$C$1*2

Observa que sin importar a qué fila se copió la fórmula, la referencia siempre se mantiene hacia $C$1. Es decir, aún cuando se halla copiado la fórmula a filas diferentes la referencia dentro de la fórmula permaneció constante. Es importante que entiendas la diferencia entre estos dos tipos de referencias porque te permitirá escribir fórmulas efectivas en Excel.

Diferencias entre celdas absolutas y relativas en Excel

En Excel, una dirección de celda absoluta es aquella que permanece fija sin importar dónde se mueva. Las direcciones relativas se basan en la distancia entre la fuente y el destino que contienen las celdas de esa dirección. Entender la diferencia entre estos tipos ayuda a garantizar la integridad de los datos de las hojas de cálculo. El tipo de celda que utilizas tiene impacto en la hoja de cálculo al realizar cualquiera de estas operaciones comunes: usar la funcionalidad de autocompletado de Excel, copiar y pegar fórmulas que contengan direcciones de celdas, y definir nombres de rango.

Ejemplo para mostrar tipos de direcciones

Un ejemplo práctico ilustra las diferencias entre las direcciones de celdas absolutas y relativas Excel. Escribe los siguientes datos en una hoja de cálculo de Excel de manera que la entrada "Ítem" entre en la celda A1. Pulsa la tecla "Tab" en lugar de las comas. Ítem, Unit cost, Quantity, Total Price Paintbrush, 1.98, 2, =B2*C2 Paint, 2.34, 1 Haz clic en la celda de la fórmula "=B2*C2" y presiona "Control" y "C" al mismo tiempo para copiar la fórmula en el portapapeles. Pulsa la tecla de flecha hacia abajo para desplazarte a la celda directamente debajo y presiona "Control" y "V" al mismo tiempo para pegar la fórmula en la nueva celda. Ten en cuenta que el resultado de la fórmula es exacta: se muestra el precio total del elemento en la segunda fila. Esto significa que Excel cambia automáticamente la dirección de la celda de la fórmula original que copiaste. Las direcciones de celdas relativas hacen posible esta automatización.

Direccionamiento relativo es predeterminado

Las direcciones de celdas que introduzcas manualmente son relativas de manera predeterminada, lo que hace que copiar fórmulas sea una tarea sin problemas. Puedes verificar una dirección de celda relativa por su falta del carácter "$", lo que indica una dirección absoluta. Ten en cuenta que una dirección de celda puede ser parcialmente relativa y parcialmente absoluta. Por ejemplo, la dirección "$A1" tiene una columna de absoluta, pero una fila relativa.

Cambiar de relativa a absoluta

Puedes reemplazar el comportamiento predeterminado de introducir manualmente direcciones de celdas relativas de Excel con la tecla "F4" cuando el cursor del ratón está dentro de una dirección de celda. Por ejemplo, escribe la fórmula "=A1" en cualquier celda de una hoja de cálculo, además de A1, pero no presiones "Enter" todavía. En su lugar, pulsa la tecla "F4". Excel colocará el carácter "$" antes de ambos, el carácter "A" y el carácter "1", lo que indica que la dirección de la celda completa es ahora absoluta. Verifica este cambio con la tecla "Enter", copiando la celda en el portapapeles, al hacer clic en otra celda para seleccionarla y, a continuación, pega desde el portapapeles. Observa que la celda que se pega muestra "$A$1". Si no hubiera tomado la dirección de celda absoluta, verías otra dirección de la celda en su lugar, una dependiente de la ubicación en la que has pegado la celda correspondiente a la celda que copiaste.

Cuándo utilizar celdas absolutas

Usa direcciones de celda absolutas cuando tengas una celda a la que deseas hacer referencia a varias otras celdas. Por ejemplo, escribe lo siguiente en dos celdas adyacentes en una hoja de cálculo: "Price:" "$1,98". Tu objetivo es asegurar que "Price" diga "$1,98" en todas partes de la hoja de cálculo. Para ello, haz clic en la celda en la que escribiste "$1,98", haz clic en la casilla de texto "Rango de nombre" en la parte superior izquierda de la cuadrícula de la hoja de cálculo, a continuación, escribe "Price". Al crear un nombre de rango, Excel asigna automáticamente una dirección de celda absoluta. Para comprobar esto, haz clic en cualquier celda vacía de la hoja de cálculo y escribe "=Price", luego, pega la fórmula a otra celda en blanco. La celda en la que has pegado mostrará "=Price". Si el "Price" se refería a una dirección relativa, se verá un valor diferente a "=precio" en esta celda.

Cambiar entre referencias relativas y absolutas

Cuando crea una fórmula que hace referencia a otra celda o intervalo de celdas, esa referencia de celda puede ser absoluta o relativa. Una referencia de celda relativa se ajusta a su nueva ubicación cuando se copia o se mueve la fórmula. Una referencia de celda absoluta no cambia cuando se mueve la fórmula.

1. Seleccione la celda que contiene la referencia de celda que desea cambiar.

2. En la barra de fórmulas, haga clic en la referencia de celda que desea cambiar.

3. Presione + T para desplazarse por las combinaciones.

En la tabla siguiente, la columna Cambia a refleja cómo un tipo de referencia se actualiza si una fórmula que contiene la referencia se copia dos celdas hacia abajo y dos celdas a la derecha.

DIFERENCIAS ABSOLUTAS Y RELATIVAS A LAS CELDAS Referencias relativas      Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas. Por ejemplo, si copia una referencia relativa de la celda B2 a la celda B3, se ajusta automáticamente de =A1 a =A2.

Fórmula copiada con referencia relativa

Referencias absolutas

    Una referencia de celda absoluta en una fórmula, como $A$1, siempre hace referencia a una celda en una ubicación específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias absolutas. Por ejemplo, si copia una referencia absoluta de la celda B2 a la celda B3, permanece invariable en ambas celdas =$A$1.

1 -Referencias absolutas, relativas y mixtas

Excel u otras hojas de cálculos usan siempre referencias relativas para las direcciones de celdas introducidas en las fórmulas. Esto significa que las referencias usadas cambiarán de modo acorde tras copiar la fórmula de una celda a otra. Con mucha frecuencia éste es el comportamiento deseado.

El método más seguro para definir referencias a celdas consiste en utilizar el ratón para apuntar a la celda cuya referencia se desea insertar. Cuando se desea introducir la referencia a una celda en una fórmula, basta con hacer clic en dicha celda. De esta forma, se minimizan también los errores en la inserción de referencias a celdas.

Algoritmo para introducir una fórmula en una celda mediante referencias relativas  o con una referencia a otra celdas:1. Seleccionar la celda donde se desea introducir la fórmula.2. Teclear un signo igual ( = ).3. Señalar con el ratón la celda cuya referencia desea introducir y hacer clic. La dirección de la celda apuntada aparece en la posición del punto de inserción, en la barra de fórmulas.4. Introducir un operador, por ejemplo el operador suma (+).5. Si se desea introducir más referencias a celdas en la fórmula, basta repetir los pasos anteriores cuantas veces sea necesario. Para dar la fórmula por terminada se pulsa Intro o el signo correcto de la barra de fórmulas.6. Si la fórmula no es correcta, se anulará o borrará haciendo clic  en el signo error (   ) o se confirmará con.

En ciertos casos hay que evitar que las referencias a celdas cambien cuando se copia la fórmula a una nueva posición. Para ello hay que utilizar referencias absolutas. Es posible utilizar referencias absolutas para filas y relativas para columnas, o viceversa. Las referencias relativas se convierten en absolutas introduciendo el carácter dólar ($) antes de la letra de la columna o el número de fila, que se quieren mantener invariables. Si se desea que no cambie ni el índice de filas ni de columnas, hay que poner el carácter dólar ($) delante de cada uno de los dos índices.

Una referencia absoluta se puede insertar de dos formas diferentes:

1. Según se introduce la fórmula, se teclea el carácter $ delante del índice de fila y/o de columna que se quiere mantener constante.

2. Colocando el punto de inserción en la barra de referencias de manera que esté dentro de la referencia a la celda, pulsando la tecla F4 se pasa cíclicamente por referencias relativas, absolutas y por los dos casos mixtos. En los casos en los que  se pretende que sólo una de las dos dimensiones, fila o columna, permanezca constante se utiliza una referencia mixta, es decir, una referencia que contenga a la vez referencias absolutas y relativas. Por ejemplo, la referencia $B5 evita que cambie la columna, mientras que la fila se adapta cada vez que se copia la fórmula. Con B$5 ocurre lo contrario: la columna cambia, mientras que la fila 5 siempre permanece constante. Referencias absolutas, relativas y mixtas. Fórmulas  con referencias relativas  y  fórmulas  con referencias absolutas

Referencias relativas de celda. Son referencias a celdas relacionadas con la posición de la fórmula. Al copiar una fórmula con referencias relativas, dentro de la fórmula  se ajustan a la nueva posición. Se denotan con la letra de columna y el número de fila, por ejemplo, las referencias relativas A1, B5, H9, D5:D9, etc.

Referencias absolutas de celda. Son referencias que siempre se hacen a las celdas ubicadas en una posición específica. Al copiar una fórmula con referencias absolutas, dentro de la fórmula  no se ajustan a la nueva posición. Se denotan anteponiendo a la letra de la columna y al número de fila el signo de peso ($), por ejemplo, las referencias absolutas $A$1, $B$5, $H$9, $D$5:$D$9, etc.  

Existen casos en que es necesario especificar referencias múltiples como  $B1 o B$1, a estos casos se ls llama Referencias mixtas: Las fórmulas pueden contener todos los tipos de referencia, según sean las necesidades del problema.

EJEMPLOS SIMPLES DE COPIADO DE FORMULAS RELATIVAS Y ABSOLUTAS.

Tu puedes responder de dos maneras:

Una, de manera relativa: (dirección relativa) ================== Vivo a dos cuadras (o sea relativa al lugar donde estas parado, porque si estuvieras a tres cuadras, entonces dirías vivo a tres cuadras).

Dos, de manera absoluta: (dirección absoluta) =================== Vivo en Bolognesi 345 (estés a dos cuadras o a tres cuadras, la respuesta es

absoluta, es siempre la misma).

En resumen, dirección relativa se refiere "en relación a" la celda en que está ubicado el cursor, dirección absoluta, en una dirección específica e independiente de donde esté ubicado el cursor.

Las direcciones absolutas se indican anteponiendo un signo $ a las filas o columnas de una celda. Ejemplo $A$4 (columna A y fila 4 absoluta)

Las direcciones relativas se indican por defecto, es decir que si no pones un signo $ se entiende que es relativa. Ejemplo A4 (en relación a la columna A y fila 4).Referencias a Celdas.

Tipos de referencias a celdas.Los diferentes tipos de referencias a celdas son:

RELATIVAS: Si copiamos una fórmula con referencias relativas, la fórmula cambiará dependiendo de donde vamos a copiarla.

Supongamos el ejemplo:

A B1 15 202 =A1+2 303

Si ahora copiamos la celda A2 en B3, como la copiamos una columna hacia la derecha y en una fila hacia abajo, la fórmula cambiará por: =B2+2. Lo que variará es la referencia a la celda A1, al copiarla una columna hacia la derecha se incrementará el nombre de la columna en uno, es decir, en vez de A pondrá B y al copiarla una fila hacia abajo en vez de fila 1 pondrá 2, resultado =B2+2. Para mantener en la fórmula sumar 2 al contenido de la celda superior.

ABSOLUTAS: Si copiamos una fórmula con referencias absolutas, la fórmula NO variará.

Supongamos el ejemplo:

A B1 15 202 =$A$1+2 303

Si ahora copiamos la celda A2 en B3, aunque la copiemos una columna hacia la derecha y en una fila hacia abajo, como delante de la columna y delante de la fila

encuentra en signo $ no variará la fórmula y en B3 pondrá =$A$1+2.

MIXTAS: Si copiamos una fórmula con referencias mixtas, la fórmula cambiará la columna o la fila que no tiene el signo $ delante dependiendo de donde vamos a copiarla y no variará la que sí tiene el signo $ delante.

Supongamos el ejemplo:

A B1 15 202 =$A1+2 303

Si ahora copiamos la celda A2 en B3, como hay un signo $ delante de la columna aunque se copie una columna más a la derecha ésta no variará, pero al no tener el signo $ delante de la fila, al copiarla una fila hacia abajo la fila cambiará por 2 en vez de 1 y el resultado será  =$A2+2.

Supongamos el ejemplo:

A B1 15 202 =A$1+2 303

Si ahora copiamos la celda A2 en B3, como hay un signo $ delante de la fila aunque se copie una fila hacia abajo ésta no variará, pero al no tener el signo $ delante de la columna, al copiarla una columna más a la derecha la columna cambiará por B en vez de A y el resultado será =B$1+2.

¿Cuáles son las referencias absolutas y las referencias relativas?

Una referencia le indica a StarOffice Calc donde buscar los valores que se desean utilizar en una fórmula.

Existen 2 tipos de referencias: Referencias relativas y Referencias absolutas.

REFERENCIAS RELATIVAS: Por lo general cuando se crea una fórmula, las referencias a las celdas se basan en su posición relativa a la celda que contiene la fórmula. Esta característica es muy útil cuando se copia una fórmula a otra celda, ya que las direcciones de las celdas se actualizan de acuerdo a su nueva posición

Utilizaremos el siguiente ejemplo: En la celda D3 (Total), está escrita la fórmula (=B3*C3) que multiplica el valor de la celda B3 (Precio), por el valor de la celda C3 (Cantidad).

Cuando se copia una fórmula a otras celdas se copian con su posición relativa. Veamos cómo se vería copiada la fórmula de la celda D3 en las celdas D4, D5, D6 y D7:

Observe que al copiar la fórmula original a la celda D4 las direcciones de las celdas se actualizan automáticamente, ya que StarOffice Calc interpreta que la fórmula original ha cambiado de posición una fila abajo; por eso la fila 3 cambia a ser la fila 4.

Pero habrá ocasiones en que no desee que las referencias cambien cuando se copian las fórmulas a otras celdas distintas; entonces lo que necesitará son celdas con referencias absolutas.

REFERENCIAS ABSOLUTAS: Una referencia absoluta es una referencia en la fórmula, que no cambia cuando se copia a otra celda. Al utilizar referencias absolutas la dirección de la celda se mantiene cuando es copiada a otras celdas. Esta es una herramienta poderosa cuando se está trabajando en una hoja de cálculo con muchos datos.

Para crear una referencia absoluta coloque el signo de pesos ($) antes de la columna (letra) o el renglón (número) que no desee que cambie. Por ejemplo: la

referencia de celda $C$4 es una referencia absoluta; sin embargo la referencia de celda C4 es una referencia relativa. Si copia una fórmula que contiene la referencia absoluta $C$4 a otra celda, la referencia de celda $C$4 no cambia, pero si copia una fórmula que contiene la referencia relativa C4 a otra celda, la referencia C4 si cambia.

Utilizaremos la siguiente imagen como ejemplo: Para obtener el costo de los artículos en pesos, es necesario multiplicar el precio en dólares de los productos, por el tipo de cambio que es la celda (B2).

En en la celda C3 se ha escrito una fórmula sin referencia absoluta (=A3*B3); pero como el tipo de cambio únicamente se encuentra en la celda B2, necesitamos hacer que esa celda sea de referencia absoluta en la fórmula. Entonces colocaremos para la referencia de celda B2, un signo de pesos ($) antes de la letra y antes del número.

De esta manera cuando se copia la fórmula a otras celdas la dirección de celda $B$2 no cambia porque es una referencia absoluta.

Alternar entre referencias relativas, absolutas y mixtas/mixtas con autorización

Mostrar todoDe forma predeterminada, una referencia de celda es relativa. Por ejemplo, cuando hace referencia a la celda A2 de la celda C2, en realidad está haciendo referencia a una celda que está dos columnas a la izquierda (C menos A), y en la misma fila (2). Una fórmula que contiene una referencia de celda relativa cambia a

medida que la copia de una celda a otra. Como ejemplo, si copia la fórmula =A2+B2 de la celda C2 a D2, la fórmula en D2 se ajusta hacia abajo una fila y se convierte en =A3+B3. Si desea mantener la referencia de celda original en este ejemplo cuando la copia, puede hacer que la referencia de celda sea absoluta al colocar el signo dólar ($) delante de las columnas (A y B) y de la fila (2). A continuación, cuando copia la fórmula (=$A$2+$B$2) de C2 a D2, la fórmula permanece exactamente igual.

En casos menos frecuentes, es posible que deba hacer una referencia de celda “combinada” al colocar el signo dólar delante de la columna o del valor de fila para “bloquear” la columna o la fila (por ejemplo, $A2 o B$3). Para cambiar el tipo de referencia de celda:

1. Seleccione la celda que contenga la fórmula.2. En la barra de fórmulas , seleccione la referencia que desea

cambiar.3. Presione F4 para alternar los tipos de referencia.

En la tabla siguiente se indica cómo se actualiza un tipo de referencia si la fórmula que la contiene se copia dos celdas hacia abajo y dos hacia la derecha.

Para una fórmula que se va a copiar: Si la referencia es Cambia a:

$A$1 (columna absoluta y fila absoluta)

$A$1 (la referencia es absoluta)

A$1 (columna relativa y fila absoluta)

C$1 (la referencia es combinada)

$A1 (columna absoluta y fila relativa)

$A3 (la referencia es combinada)

A1 (columna relativa y fila relativa)

C3 (la referencia es relativa)

¿Qué significan las referencias de celdas mixtas en Microsoft Excel?

Microsoft Excel es un programa de hoja de cálculo que te permite introducir información en celdas y luego usarla para crear fórmulas en otras celdas usando proceso llamado referencias de celdas. De manera predeterminada, cuando copias una fórmula de una celda a otra, las referencias de la celda cambiarán de

manera relativa al cambio en la ubicación de la fórmula. Las referencias de celdas mixtas funcionan ligeramente diferentes, ya que cambiarán ya sea la columna o la fila, pero no ambas.

Referencias de celdas relativas

Las referencias de celdas relativas son simplemente una referencia dentro de una fórmula que apunta a otra celda en la hoja de cálculo. Las referencias de celdas relativas no tienen caracteres especiales y son la forma predeterminada en que Excel maneja las referencias. Si tienes una fórmula con "A1" en ésta, lo que significa que la fórmula requiere el valor en la celda "A1", y mueves esa fórmula dos celdas abajo y una celda a la derecha de su posición original, la referencia automáticamente cambiará a "B3", ya que "B3" está dos celdas hacia abajo y una a la derecha de "A1".

Referencias de celdas absolutas

Las referencias de celdas absolutas son exactamente lo opuesto a las referencias de celdas relativas, ya que las absolutas nunca cambiarán. Las referencias de celdas absolutas están señaladas por un "$" colocado frente a la letra de la columna y el número de la fila de la referencia. Por ejemplo, si quieres hacer una referencia absoluta a la celda "A1", lo escribirías "$A$1". Si copias y pegas la fórmula con una referencia absoluta a una nueva celda, las referencias en la fórmula permanecerán sin cambiar.

Referencias de celdas mixtas

Es posible, y a menudo útil, tener una mezcla de referencias absolutas y relativas dentro de la misma referencia. Hacer esto crea una referencia de celda mixta. Cuando crees una, coloca el signo de dólar frente a la referencia de columna o la referencia de fila, pero no de ambas. Por ejemplo, "$A1" cambiaría las filas si copias tu fórmula en la hoja de cálculo, pero siempre se referirá a la columna "A".

Moverse entre tipos de referencia

Microsoft Excel 2010 te da un método fácil para cambiar una referencia de celda a una referencia mixta. Sólo haz clic en la celda que contiene tu fórmula, luego haz clic en la referencia en tu barra de fórmula en la parte superior de la hoja de cálculo. Presiona "F4" para cambiar la referencia a través de una referencia relativa y una referencia absoluta y ambos tipos de referencias mixtas (una con las columnas fijas y otra con las filas fijas). Si seleccionas la fórmula completa, presionar "F4" cambiará entre los tipos de referencia para todas las referencias en la fórmula.

Referencias relativas, absolutas y mixtas en Excel o similar Para poder trabajar sin problemas con las planillas de cálculo es necesario tener muy claro cómo funcionan las referencias a celdas y/o rangos, aquí se plantea un artículo que pretende explicar su funcionamiento.

Parte de la información que se presenta fue extraída de la ayuda de Microsoft Excel, si bien fue escrita para los productos de Microsoft es válida para todas las plantillas de cálculo como ser: OpenOffice.org Calc, Google Docs, Numbers, etc.

Según la gente de Microsoft: “Una referencia identifica una celda o un rango de celdas en una hoja de cálculo e indica a Microsoft Excel en qué celdas debe buscar los valores o los datos que desea utilizar en una fórmula. En las referencias se puede utilizar datos de distintas partes de una hoja de cálculo en una fórmula, o bien utilizar el valor de una celda en varias fórmulas. También puede hacerse referencia a las celdas de otras hojas en el mismo libro y a otros libros. Las referencias a celdas de otros libros se denominan vínculos.".

Existen tres tipos de referencias: relativas, absolutas y mixtas.

Referencias relativas Una referencia relativa en una fórmula, como A1, se basa

en la posición relativa de la celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas. Por ejemplo, si copia una referencia relativa de la celda B2 a la celda B3, se ajusta automáticamente de =A1 a =A2."La figura muestra el copiado de la celda B2 a B3, como se puede observar se ha desplazado una fila, y de haberse copiado a la celda C3, el contenido de la misma estaría apuntando a B2.

Referencias absolutas Una referencia de celda absoluta en una fórmula, como $A$1, siempre hace referencia a una celda en una ubicación específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece

invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias absolutas. Por ejemplo, si copia una referencia absoluta de la celda B2 a la celda B3, permanece invariable en ambas celdas =$A$1." La figura muestra el copiado de la celda B2 a B3, como se puede observar que ahora no se ha desplazado ninguna fila, y de haberse copiado a la celda C3, el contenido de la misma estaría también apuntando a B1.

Referencias mixtas Una referencia mixta tiene una columna absoluta y una fila relativa, o una fila absoluta y una columna relativa. Una referencia de columna absoluta adopta la forma $A1, $B1, etc. Una referencia de fila absoluta adopta la

forma A$1, B$1, etc. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia relativa y la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia relativa se ajusta automáticamente y la referencia absoluta no se ajusta. Por ejemplo, si se copia una referencia mixta de la celda A2 a B3, se ajusta de =A$1 a =B$1"La figura muestra el copiado de la celda B2 a C3, como se puede observar no se ha desplazado la fila, pero si la columna. Si el contenido de B2 hubiese sido =$A1 al copiarla a C3 tendría =$A2.

Si se edita la formula, por ejemplo presionado F2 sobre la celda, y se sitúa el cursor sobre la referencia, al presionar F4 se van alternando las combinaciones mencionadas. Esto es para evitar digitar los símbolos de $.

A continuación se mostrarán un par de ejercicios o ejemplos:

Ejemplo 1:

En una hoja de cálculos se realizaron 5 cuadros, en el cuadro número 1 se ingresaron números del 1 al 9 ordenados. En el centro del cuadro 2, se ingresó una referencia relativa al centro del cuadro 1: =C5.En el centro del cuadro 3, se ingresó una referencia absoluta al centro del cuadro 1: =$C$5.En el centro del cuadro 4, se ingresó una referencia mixta al centro del cuadro 1 fijando la fila: =C$5.En el centro del cuadro 5, se ingresó una referencia mixta al centro del cuadro 1 fijando la columna: =$C5.En los cuadros 2 al 5 se copia la celda del centro a las 8 celdas vecinas de los bordes.

Los resultados muestran que:

Al utilizar referencias relativas en el cuadro 2 se logró reproducir exactamente la muestra.

Al utilizar referencias absolutas en el cuadro 3 se llenó con el valor del centro de la muestra.

Al utilizar referencias mixtas fijando la fila en el cuadro 4 solo varía las celdas de las columnas.

Al utilizar referencias mixtas fijando la columna en el cuadro 5 se ve lo contrario al cuadro 4.

Ejemplo 2:

En la siguiente figura las formulas introducidas en las celdas con colores se visualizan a la izquierda de cada una.

Aquí se ingresó información desde la celda C2 hasta G6 alterando con celdas sin datos.

En las celdas E14 a E17 se definieron referencias a la celda E4 que contiene la palabra Centro con los tres tipos de referencia relativas, absolutas, y mixtas (una para fila y otra para columna).

Por último se copió el rango E14 hacia las celdas C12, G12, C16 y G16 (desfasaje de 2 celdas en diagonal para todas las direcciones).

Luego de realizar el copiado se puede ver los diferentes resultados según las referencias introducidas.

Errores frecuentes con referencias:

Uno de los errores más frecuentes cuando se trabaja con diversas funciones se debe a no utilizar referencias absolutas.

Por ejemplo cuando se utiliza la famosa función BUSCARV o VLOOKUP, si a la matriz a buscar se la define con referencias relativas en lugar de absolutas, y cuando la función final se copia a las filas de abajo, la matriz también se desplazará hacia abajo provocando que apunte a un área donde no se encuentre el dato a buscar.

Uso erróneo de la función:

=BUSCARV (A1; F1:H100; 2; FALSO)

NOTA: No siempre está mal utilizar referencias relativas en esta función, hay veces -generalmente muy pocas- en que se necesita este desplazamiento.

Uso correcto de la función:

=BUSCARV ($A1; $F$1:$H$100; 2; FALSO)

Normalmente se fija la columna done se encuentra el dato a buscar ($A1), que es utilizado cuando se quiera copiar la formula a la derecha para buscar una segunda columna de datos.

La matriz a buscar generalmente ($F$1:$H$100) es fija.

Referencias relativas y absolutasUna referencia de celda es el conjunto de coordenadas que ocupa una celda en una hoja de cálculo. Por ejemplo, la referencia de la celda que aparece en la intersección de la columna B y la fila 3 es B3. Se refiere aúna celda o un rango de celdas en una hoja de cálculo y se puede usar en una fórmula de manera que Microsoft Office Excel pueda encontrar los valores o datos que desea que calcule la fórmula.Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la celda que contiene la fórmula, de modo tal que si cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente.

Una referencia absoluta de celda en una fórmula, como $A$1, siempre hace referencia a una celda en una ubicación específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma Predeterminada, las nuevas fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias absolutas.

Referencias Absolutas y Relativas – Microsoft Office ExcelReferencias Relativas y Referencias Absolutas

Cuando trabajamos con fórmulas, por lo general indicamos el lugar donde se encuentra la celda. Esas son las referencias. Como por ejemplo: Si queremos hacer una suma entre dos celdas: =A5+B5Estamos haciendo referencia a las celdas A5 y B5.Como ya vimos en el tema Funciones, cuando arrastramos una fórmula vertical u horizontalmente, las referencias a las celdas implicadas en la función, se autoincrementarán siguiendo el valor de la fila o columna, según como realicemos el arrastre.Pero, ¿qué pasa si arrastramos una fórmula que contiene el valor de una celda que no queremos que se incremente? La mejor forma de entenderlo es viéndolo en un ejemplo.Ejemplo: Cálculo de GananciaEn la siguiente planilla, deseamos calcular la ganancia que se obtendrá en cada producto, teniendo enCuenta el porcentaje de Ganancia indicado en la celda B1.

Conceptos:Existen 3 tipos de referencias:Referencia Relativa:Las referencias de filas y columnas cambian si se copia la formula en otra celda, es decir se adapta a su entorno porque las referencias las hace con respecto a la distancia entre la formula y las celdas que forman parte de la formula. Esta es la opción que ofrece Excel por defecto.Referencia Absoluta:Las referencias de filas y columnas no cambian si se copia la formula a otra celda, las referencias a las celdas de la formula son fijas. Podemos convertir una referencia a celda a absoluta,Presionando la tecla F4 una vez que ingresamos el valor de la celda o ingresando manualmente los signos $ delante del indicador de columna y de fila.Referencia Mixta:Podemos hacer una combinación de ambas referencias, podemos hacer que las filas sean relativas y las columnas absolutas o viceversa.Ejemplos:Si copiamos la celda A2 en B3 , como hay un signo $ delante de la columna aunque se copie una columna más a la derecha ésta no variará, pero al no tener el signo $ delante de la fila, al copiarla una fila hacia abajo la fila cambiará por 2 en vez de 1 y el resultado será =$A2+2 .Si copiamos la celda A2 en B3, como hay un signo $ delante de la fila aunque se copie una fila hacia abajo ésta no variará, pero al no tener el signo $ delante de la columna, al copiarla una columna más a la derecha la columna cambiará por B en vez de A y el resultado será =B$1+2.

UTILIZANDO REFERENCIAS ABSOLUTAS Y RELATIVASEn Excel puedes especificar tres tipos de referencias: relativas, absolutas y mixtas. Al utilizar el tipo de referencia adecuado nos aseguramos de que al copiar

la fórmula a una nueva ubicación seguirá funcionando adecuadamente.Si tienes una fórmula que hace referencia a otras celdas y copiamos esa fórmula a otra ubicación, Excel actualizará automáticamente las referencias de la fórmula para adecuarlas a la nueva ubicación.  Considera el siguiente ejemplo.

La celda C1 hace la suma de A1 y B1. Observa lo que sucede si copiamos el contenido de la celda C1 y lo pegamos en la celda C2.

Excel ha sido tan inteligente que ha supuesto que la fórmula de la celda C2 debe sumar los valores de A2 y B2. Si volvemos a copiar la fórmula y pegarla en la celda C3 obtendremos la fórmula: =A3 + B3.Referencias relativasCuando copiamos fórmulas Excel utiliza el tipo de referencia relativa que es el comportamiento predeterminado. Este tipo de referencia se adapta al contexto de la nueva celda cambiando las referencias de acuerdo a la nueva ubicación. Sin embargo, en algunas ocasiones no queremos tener este comportamiento sino que queremos hacer referencia siempre a una celda específica sin importar que copiemos la fórmula a otra ubicación y para ello necesitamos utilizar las referencias absolutas.Referencias absolutasPara fijar la columna o fila de una referencia utilizamos el signo $ y lo colocamos antes de la letra de la columna o del número  de fila de la referencia. Así por ejemplo podemos tener una referencia absoluta que fija tanto la columna como la fila, por ejemplo: $A$1

Ligas:

www.duiops.net

www.cca.org.mx

www.ehowenespanol.com

www.aulafacil.com

2.¨Hoja de cálculo. Concepto.¨

REALIZAR CÁLCULOS CON FUNCIONES

 Las funciones son fórmulas predefinidas, que utilizan ciertos valores denominados argumentos. 

Por ejemplo la función que nos permite realizar la suma de los números introducidos desde la casilla A1 a la C3 queda como =SUMA(A1:C3) donde:

Comenzamos la función con el signo igual "=" para indicar al programa que se trata de una función.

El nombre de la función que queremos realizar. La operación que deseamos realizar en este caso SUMA

El argumento que en este caso hace referencia al rango de celdas donde se encuentran los números a sumar.

En este curso en los primeros ejemplos estamos utilizando como argumentos de las fórmulas referencias de celdas. Pero en general los argumentos pueden ser números, texto, valores lógicos, matrices o referencias de celdas.

Las funciones adoptan la forma de  =FUNCION(argumento1;argumento2;argumento3...), donde los argumentos se encuentran separados por punto y coma.

Por tanto para sumar los números introducidos en las celdas A1, A2, A3 y A4 tenemos varias posibilidades

Utilizar la función SUMA y escribir todos los argumentos uno detrás de otro

=SUMA(A1;A2;A3;A4)

Utilizamos la función SUMA pero ahora resumimos las celdas escribiendo la primera, dos puntos y la última

=SUMA(A1:A4)

Sumamos mediante el operador matemático suma u escribiendo todas las celdas

=A1+A2+A3+A4

EJEMPLO

Vamos a partir de los datos que aparecen en la siguiente imagen.  Vamos a utilizar dos funciones:

Calcular las sumas y Una función para calcular  que cantidad corresponde a las ventas más

bajas de un mes y zona.

El texto "Ventas mensuales" lo tecleamos en la celda A1 y luego hemos marcado desde la celda A1 a la E1 y pulsado el botón Combinar y centrar.

Para adaptar el formato de los números hemos utilizado el botón Euro de la barra de formato.

 

Vamos a utilizar primero la función Autosuma que conocemos para calcular los totales. Para sumar la primera columna la función quedaría =SUMA(B4:B7)

Independientemente del cálculo anterior vamos a calcular el mes y la zona al que corresponden las ventas más bajas utilizamos la función Mínimo que se designa por MIN. 

Vamos a calcular el mínimo de los datos mensuales sin incluir los totales Por tanto el  argumento es  la zona de datos sobre la que deseamos calcular el mínimo. Es decir desde B4 a C7 por lo que el argumento se escribe (B4:C7)

La función queda =MIN(B4:C7)

Escribimos la función en la celda B11

Y el resultado se muestra en la siguiente imagen.

 

Una de las cuestiones más interesantes de las Hojas de Cálculo es que nos permite cambiar los datos y las funciones recalculan los resultados teniendo en cuenta los nuevos datos. Por ejemplo vamos a sustituir el número 122,00 que aparece en la celda C7 por el número 350 y comprobaremos como afecta a la suma de su columna y al resultado mínimo de ventas.

Y el resultado es que la suma de la columna de febrero refleja el cambio que hemos efectuado y la función mínimo también.

Dentro de las categorías de funciones que dispone Excel, y en general todas las hojas de cálculo como Google Docs., Numbers,…, una de las más usadas es la de las funciones matemáticas.Puedes realizar un montón de cálculos, desde los más sencillos como suma, resta, producto, hasta los más complicados como logaritmos, trigonometría,

matrices, además de un montón de utilidades como redondear, escribir en números romanos, usar números aleatorios, y muchas más.

Empiezas con la función Suma:

Haz clic en el icono fx, aparece en la barra de fórmulas, la encuentras en la parte superior de una ventana de Excel, es un espacio rectangular alargado en donde van a aparecer los valores y las formulas introducidas en la celda en la que esté situado el cursor.

Se abre un cuadro de dialogo llamado Insertar función, para buscar una función por ejemplos una, escribe en el recuadro debajo de Buscar una función

Eras que aparecen todas las funciones que tienen la palabra suma en su definición. La función suma aparece la primera seleccionada, pulsa Aceptar. (También puedes realizar la búsqueda por categorías, para ello despliega el triángulo negro que aparece a la derecha de O seleccionar una categoría: Usadas recientemente, sitúate en la categoría que necesitas, en este caso y te aparecerán en la parte central de la ventana)

Otro cuadro se abre, llamado Argumentos de función, en donde encuentras una pequeña explicación de los pasos a seguir para realizar la operación suma.

En el primer rectángulo escribe A1 o pulsa con el ratón en dicha celda, en el segundo rectángulo pon o pulsa en A2. Haz clic en el botón Aceptar, el cuadro de dialogo se cierra y en la celda C1 aparece el resultado de la suma que es 12.

Si quieres sumar más cifras basta con ir indicando las celdas a sumar en cada rectángulo.

Otra forma de realizar sumas es la Autosuma.

Restar en Excel:

No busques la formula Restar porque no está, si quieres realizar una resta en Excel has de escribir el signo menos delante de la celda a restar.

Para restar dos números en Excel u otra hoja de cálculo es necesario introducir una fórmula.

Tienes que seguir estos sencillos pasos:

1.- Abre un libro de cálculo: Excel, Works, Calc,...

2.- Sitúate en la celda A1 y escribe el número 100.

3.- Ahora coloca el ratón en la celda A2, haz clic y escribe 50.

4.- Pon el cursor, o como también se dice activa, en la celda A3.

5.- A continuación coloca el cursor en la barra de fórmulas.

6.- Escribe el signo =.

7.- Haz clic en A1.

8.- Pulsa la tecla - del teclado numérico.

9.- Pulsa A2.

10.-Por último clic en Intro.

En la celda A3 obtendrás la cantidad 50 que es el resultado de la resta entre 100 y 50. Y si te fijas en la barra de fórmulas aparece: =A1-A2.

Función Potencia:

En vez de escribir el nombre, elige la categoría Matemáticas y Trigonométricas y escógela de la lista (está en orden alfabético), desplázate con la barra de desplazamiento a la derecha para acceder a ella.

Si quieres por ejemplo calcular el resultado de la potencia de 2 al cubo, es decir a la tres, en el primer recuadro llamado Número pulsa en la celda A2, en el segundo escribe 3, Aceptar, obtienes el resultado que es 8.

Función Raíz cuadrada:

Escoge dentro de la categoría Matemáticas y Trigonométricas la función Raíz, en el cuadro de dialogo indica de que numero quieres saber la raíz cuadrada, bien escribiéndolo o bien pulsando una celda con datos numéricos, por ejemplo escribe 144 en el recuadro Número: al pulsar Aceptar obtendrás 12.

Funciones Básicas:

• Suma: Con esta función lo que calculamos es la suma de todos los números de un conjunto de datos (también denominado rango) Se representa en la hoja de Excel: =SUMA (celda(s): celda(s)).

• Resta: Análogamente a la suma, pero con el signo - en vez del signo:. Y será: =RESTA (celda(s)-celda(s)).

• Producto: Multiplica todos los números obteniendo el producto de dichos números. Aparecerá: =PRODUCTO (celda(s); celda(s)).

• Dividir: De forma similar al producto. La fórmula aparecerá =DIVIDIR ((celda(s)/celda(s)).

• Porcentaje, %: Excel nos calcula el tanto por ciento de una serie de datos, dividiendo la cantidad entre el total. La expresión de esta función es = PRODUCTO (%; celda(s)).

• Máximo y Mínimo: Con esta función Excel nos va a indicar el valor máximo y mínimo de un conjunto de valores. Así:=MAX(rango) y=MIN(rango). 

• Promedio: Devuelve la media aritmética de una serie de valores. Con la expresión: =PROMEDIO (rango).

 Ejemplo:

Supongamos que tienes los siguientes datos:

En la celda A1: 3 y en la celda A2:2. 

Este situado en la celda A3, quieres realizar la suma de estos números, para ello vas a:

Insertar, Función, elige Suma y pulsa o escribe en el primer rectángulo A1 y en el segundo A2 damos a aceptar, obtenemos 5. 

Introducir Fórmulas y Funciones

Una función es una fórmula predefinida por Excel 2007 (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene.

La sintaxis de cualquier función es:

Nombre _función (argumento1; argumento2;...; argumento N)

Siguen las siguientes reglas:

- Si la función va al comienzo de una fórmula debe empezar por el signo =.

- Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis.

- Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones.

- Los argumentos deben de separarse por un punto y coma ;.

Ejemplo: =SUMA (A1:C8)

Tenemos la función SUMA () que devuelve como resultado la suma de sus argumentos. El operador ":" nos identifica un rango de celdas, así A1:C8 indica todas las celdas incluidas entre la celda A1 y la C8, así la función anterior sería equivalente a:

=A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C6+C7+C8

En este ejemplo se puede apreciar la ventaja de utilizar la función.

Las fórmulas pueden contener más de una función, y pueden aparecer funciones anidadas dentro de la fórmula.

Ejemplo: =SUMA(A1:B4)/SUMA(C1:D4)

Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que realizan. Así hay funciones matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información.

Para introducir una fórmula debe escribirse en una celda cualquiera tal cual introducimos cualquier texto, precedida siempre del signo =.

INSERTAR FUNCIÓN CON EL ASISTENTE

Una función como cualquier dato se puede escribir directamente en la celda si conocemos su sintaxis, pero Excel 2007 dispone de una ayuda o asistente para utilizarlas, así nos resultará más fácil trabajar con ellas.

Si queremos introducir una función en una celda:

 Situarse en la celda donde queremos introducir la función.

Hacer clic en la pestaña Fórmulas.

Elegir la opción Insertar función.

 O bien, hacer clic sobre el botón   de la barra de fórmulas.

Aparecerá el siguiente cuadro de diálogo Insertar función:

Excel 2007 nos permite buscar la función que necesitamos escribiendo una breve descripción de la función necesitada en el recuadro Buscar una

función: y a continuación hacer clic sobre el botón  , de esta forma no es necesario conocer cada una de las funciones que incorpora Excel ya que el nos mostrará en el cuadro de lista Seleccionar una función: las funciones que tienen que ver con la descripción escrita. 

Para que la lista de funciones no sea tan extensa podemos seleccionar previamente una categoría del cuadro combinado O seleccionar una categoría:, esto hará que en el cuadro de lista sólo aparezcan las funciones de la categoría elegida y reduzca por lo tanto la lista. Si no estamos muy seguros de la categoría podemos elegir Todas.

En el cuadro de lista Seleccionar una función: hay que elegir la función que deseamos haciendo clic sobre ésta.

Observa como conforme seleccionamos una función, en la parte inferior nos aparecen los distintos argumentos y una breve descripción de ésta. También disponemos de un enlace Ayuda sobre esta función para obtener una descripción más completa de dicha función.

A final, hacer clic sobre el botón Aceptar.

Fórmulas y Funciones (II)

Justo por debajo de la barra de fórmulas aparecerá el cuadro de diálogo Argumentos de función, donde nos pide introducir los argumentos de la función: Este cuadro variará según la función que hayamos elegido, en nuestro caso se eligió la función SUMA ().

En el recuadro Número1 hay que indicar el primer argumento que generalmente será una celda o rango de celdas tipo A1:B4. Para ello, hacer clic sobre le botón   para que el cuadro se haga más pequeño y podamos ver toda la hoja de cálculo, a continuación seleccionar el rango de celdas o la celda deseadas como primer argumento (para seleccionar un rango de celdas haz clic con el botón izquierdo del ratón sobre la primera celda del rango y sin soltar el botón arrástralo hasta la última celda del rango) y pulsar la tecla INTRO para volver al cuadro de diálogo.

En el recuadro Número2 habrá que indicar cuál será el segundo argumento. Sólo en caso de que existiera.

Si introducimos segundo argumento, aparecerá otro recuadro para el tercero, y así sucesivamente.

Cuando tengamos introducidos todos los argumentos, hacer clic sobre el botón Aceptar.

Si por algún motivo insertáramos una fila en medio del rango de una función, Excel expande automáticamente el rango incluyendo así el valor de la celda en el rango. Por ejemplo: Si tenemos en la celda A5 la función =SUMA(A1:A4) e insertamos un fila en la posición 3 la fórmula se expandirá automáticamente cambiando a=SUMA(A1:A5).

En la pestaña Inicio o en la de Fórmulas encontrarás el

botón Autosuma   que nos permite realizar la función SUMA de forma más rápida.

Con este botón tenemos acceso también a otras funciones utilizando la flecha de la derecha del botón.

Al hacer clic sobre ésta aparecerá la lista desplegable de la derecha:

Y podremos utilizar otra función que no sea la Suma, como puede ser Promedio (calcula la media aritmética), Cuenta (cuenta valores), Máx.(obtiene el valor máximo) o Mín. (obtiene el valor mínimo). Además de poder accesar al diálogo de funciones a través de Más Funciones...

 

Si quieres conocer más sobre las referencias y nombres visita nuestro básico.

 

 Utilizar Expresiones como argumentos de las Funciones

Excel permite que en una función tengamos como argumentos expresiones, por ejemplo la suma de dos celdas (A1+A3). El orden de ejecución de la función será primero resolver las expresiones y después ejecutar la función sobre el resultado de las expresiones.

Por ejemplo, si tenemos la siguiente función =Suma((A1+A3);(A2-A4)) donde:

A1 vale 1

A2 vale 5

A3 vale 2

A4 vale 3

Excel resolverá primero las expresiones (A1+A3) y (A2-A4) por lo que obtendremos los valores 3 y 2 respectivamente, después realizará la suma obteniendo así 5 como resultado.

 

 Utilizar Funciones como argumentos de las Funciones

Excel también permite que una función se convierta en argumento de otra función, de esta forma podemos realizar operaciones realmente complejas en una simple celda. Por ejemplo =MAX(SUMA(A1:A4);B3) , esta fórmula consta de la combinación de dos funciones, la suma y el valor máximo. Excel realizará primero la suma SUMA(A1:A4) y después calculará el valor máximo entre el resultado de la suma y la celda B3.

Estructura de una función

La sintaxis de cualquier función es:

=nombre_funcion(argumento1;argumento2;…;argumentoN)

Esto es:

1. Signo igual (=).

2. Nombre de la función.

3. Paréntesis de apertura.

4. Argumentos de la función separados por puntos y comas.

5. Paréntesis de cierre.

Insertar una función

Cuando cree una fórmula que contenga una función, el cuadro de diálogo Insertar

Función le ayudará a introducir las funciones de la hoja de cálculo. A medida que

Se introduzca una función en la fórmula, el cuadro de diálogo Insertar función irá

Mostrando el nombre de la función, cada uno de sus argumentos, una descripción

De la función y de cada argumento, el resultado actual de la función y el resultado

Actual de toda la fórmula.

Para introducir una fórmula, cuenta con dos opciones:

1. Puede escribir la formula directamente en la celda que mostrará el

Resultado. Si el nombre de la fórmula es correcto Excel indicará los

Argumentos de la misma.

2. Puede utilizar la ayuda de Excel (cuadro de diálogo Insertar función), este

Asistente mostrará el nombre de la función, los argumentos, además una

Descripción de la función y de cada argumento.

Funciones Anidadas

Excel permite el utilizar funciones como uno de los argumentos de otra función,

Esto se conoce como funciones anidadas. Por ejemplo:

=SI (PROMEDIO (A1:A10)>50; SUMA(B1:B10);0)

La siguiente función SI compara con 50 el resultado producido por la función

PROMEDIO (función anidada), si es mayor a 50, invoca a la función SUMA

(Función anidada).

Resultados válidos

Cuando se utiliza una función anidada como argumento, ésta deberá devolver el

Mismo tipo de valor que el que utilice el argumento.

Límites del nivel de anidamiento

Una fórmula puede contener como máximo siete niveles de funciones anidadas.

FUNCIONES CONTENIDAS EN EXCEL

OPERACIONES MATEMÁTICAS SIMPLES

SUMAS Y CUENTAS

Sumar. Si (rango; criterio; rango suma)

Suma los componentes del rango suma que cumplan el criterio indicado en

La columna rango.

Contar (rango)

Cuenta cuántos números hay en el rango.

Contará (rango)

Cuenta cuántos valores no vacíos hay en el rango.

Contar. Blanco (rango)

Cuenta cuántos blancos hay en el rango.

Contar. Si (rango; condición)

Cuenta el número de celdas no vacías que cumplen la condición dada.

MÁXIMOS, MÍNIMOS Y PROMEDIOS

Maxi (número1; número2;...)

Devuelve el mayor valor de la lista de valores indicados.

maxa(número1; número2;...)

Idéntica a la anterior sólo que tiene en cuenta los valores lógicos, de modo

Que verdadero sería 1 y falso 0.

Min (número1; número2;...)

Devuelve el menor valor de la lista de valores indicados.

Mina (número1; número2;...)

Idéntica a la anterior sólo que tiene en cuenta los valores lógicos, de modo

Que verdadero sería 1 y falso 0.

Promedio (número1; número2;...)

Calcula el promedio o media aritmética de los valores que recibe como

Parámetros.

Promedio a(número1; número2;...)

Una hoja de cálculo es una herramienta muy útil para las personas que trabajan con números y que necesitan realizar cálculos con ellos. Ella permite trabajar con una gran cantidad de números y lleva incorporada una serie de funciones capaces de realizar cálculos matemáticos, estadísticos, técnicos y financieros.

La hoja de cálculo es una herramienta muy importante para la toma de decisiones, sobre todo, porque nos permite ver el efecto de los cambios en las variables ocasionan en el resultado buscado.

Una hoja de cálculo es un tipo de documento, que permite manipular datos numéricos y alfanuméricos dispuestos en forma de tablas compuestas por celdas (las cuales se suelen organizar en una matriz bidimensional de filas y columnas).

La celda es la unidad básica de información en la hoja de cálculo, donde se insertan los valores y las fórmulas que realizan los cálculos. Habitualmente es posible realizar cálculos complejos con fórmulas funciones y dibujar distintos tipos de gráficas.

Hoja de cálculo

La estructura principal que utiliza este software para almacenar y organizar la información es un área de trabajo en forma de matriz, como si fuera una hoja de papel cuadriculado , formada por un número determinado de filas y columnas , es lo que se llama hoja de cálculo.

La celda de una hoja de cálculo es el lugar donde se pueden introducir datos o realizar cálculos, visualmente es un espacio rectangular que se forma en la

intersección de una fila y una columna y se les identifica con un nombre, como por ejemplo C4 (C es el nombre de la columna y 4 el de la fila).

Las filas son horizontales y están identificadas por los números en secuencia ascendente. Las columnas en cambio están identificadas con las letras del alfabeto y van de forma vertical en la Hoja de Cálculo.

En las celdas se introduce cualquier tipo de información como texto o números, y también fórmulas o instrucciones para realizar un determinado cálculo o tarea.

Operaciones aritméticas básicas en platillas de cálculo

Cada vez que se insertan datos en una celda, es posible observar que, por ejemplo, los datos literales o de texto se alinean a la izquierda de la celda mientras que un dato tipo numérico (entero o con decimales) se alinea a la derecha de la celda de forma automática.

Sin embargo, puede decirse que cada vez que se necesita hacer uno o más cálculos en una celda, es necesario escribir el cálculo de un modo diferente.

Existen operadores aritméticos básicos como la suma, la diferencia, el producto y el cociente que permiten realizar dichos cálculos, existen además funciones predeterminadas para dicho fin. En todos los casos, debe anteponerse el signo igual (=) a todos estos tipos de cálculos para que la plantilla “reconozca” a ese dato como una operación aritmética o función sobre determinado dato o grupo de datos.

Las cuatro operaciones básicas en plantillas: Suma, resta, producto y cociente.

La multiplicación se realiza por medio del operador * (que se visualiza como un asterisco). Por ejemplo =b1*c3, multiplica los valores que hay en las celdas b1 y c3. Se pueden multiplicar más de dos celdas.

La división se realiza por medio del operador /. Por ejemplo =b1/c3, divide el valor que hay en la celda b1 por el de la celda c3.

Si se desea elevar el valor de una celda al exponente n, debe utilizarse el símbolo circunflejo (^). Por ejemplo, para elevar el contenido de la celda c4 al cubo se escribe la fórmula =c4^3.

Si la suma es de pocas celdas, conviene sumarlas directamente: =a1+a2+a3. Lo mismo puede hacerse si necesita restarse: =a1-b1-c1.

EXCEL es una plantilla de cálculo, por lo cual su función principal es realizar cálculos.

¿QUE SON LAS FUNCIONES EXCEL?

Las funciones Excel se definen como el conjunto de fórmulas que incorpora la aplicación Excel y que tienen por objetivo realizar cálculos predefinidos aportando únicamente los valores que se quieren calcular.

Gracias a las funciones de Excel podemos realizar cálculos complejos sin necesidad de introducir formulas complejas y que nos pueden inducir a errores.

Las funciones que llevan incorporadas la aplicación Excel se clasifican en las siguientes categorías:

Matemáticas y trigonométricas - Son funciones que hacen referencia cálculos matemáticos como el cálculo de cosenos, tangentes, logaritmos, raíces, etc...

Financieras - Son funciones que facilitan el cálculo y la generación de modelos financieros, hacen referencia a fórmulas para el cálculo de intereses, depreciaciones, pagos, etc...

Fecha y hora - Son funciones que se utilizan para el manejo y cálculo de fechas en las hojas de cálculo de Excel

Estadísticas - Son funciones que incorpora la aplicación Excel para facilitar el cálculo de estudios estadísticos, son fórmulas que nos permite calcular la media, varianza, distribuciones, variaciones ...

Búsqueda y referencia - Son funciones que nos ayuda a manipular y encontrar información dentro de las hojas Excel.

Base de datos - Son funciones que nos permiten manipular, gestionar y calcular lista de datos que se encuentren en las hojas Excel.

Ingeniería - Son funciones que ayuda a la realización de cálculos referentes a ingeniería así como modelos basados en fórmulas utilizadas en ingeniería.

Texto - Son funciones que nos permite manipular, convertir y calcular cadenas de texto, con las funciones de texto por ejemplo podemos suprimir o añadir palabras en una cadena de texto.

Lógicas - Son funciones que nos permite establecer condiciones para realizar cálculos.

Información - Son funciones que nos aportan información sobre los datos que contiene nuestra hoja de cálculo de Excel.

Definidas por el usuario - Son funciones creadas mediante macros Excel que se pueden incorporar en nuestras hojas de cálculo mediante la adición de complementos que contengan dichas funciones.

OPERACIONES BÁSICAS Y FUNCIONES

En Excel hay varias formas de escribir las operaciones básicas siempre deben comenzar escribiendo el signo =. Luego escribes la operación por ejemplo 2+2 en Excel seria =2+2

*SUMA = +

*RESTA = -

*MULTIPLICACIÓN *

*DIVISIÓN = /

FUNCIONES PREDETERMINADAS DE EXCEL

Excel cuenta con una amplia gama de funciones matemáticas, estadísticas, financieras, etc.,.las cuales se utilizan en la definición de fórmulas. La forma de definir una función en general, es a través de parámetros que correspondan a datos necesarios para que ellas funcionen.

FUNCIÓN (parámetro 1, parámetro 2,…)

Ejemplo:

°SUMA (2, 20,78)

°SUMA (A1:A5)

°PROMEDIO (A1:A5)

3.¨FORMATO DE NÚMEROS¨

El formato de números en Excel es utilizado para cambiar la apariencia de los números que se muestran como valores de las celdas. Al cambiar o modificar el formato no cambiamos el valor del número sino solamente su apariencia.

Una manera rápida de cambiar el formato es utilizar la lista de Formatos que se muestra en el grupo Número. Solamente pulsa la lista desplegable para observar las opciones disponibles y seleccionar la que mejor se adapte a tus necesidades.

El formato puede ser aplicado a una sola celda o a varias celdas a la vez, solamente debes hacer la selección previa de las celdas antes de aplicar el formato.

Formato de números desde la Cinta de opciones

Hay ocasiones en las que importamos datos a Excel o simplemente queremos modificar la manera en que se despliegan los números de una celda después de haberlos ingresado y para ello podemos utilizar los comandos de la Cinta de opciones que se encuentran dentro del grupo Número en la ficha Inicio. Cualquiera de estos comandos aplicará el formato seleccionado a la celda activa o a una selección de celdas.

1. Podemos elegir rápidamente un formato de la lista de formatos  más utilizados entre los cuales encontramos los formatos Número, Moneda, Contabilidad, Fecha corta, Fecha larga, Hora, Porcentaje, Fracción, Científica y Texto.

FORMATO DESCRIPCIÓNGeneral Éste es el formato de número predeterminado que aplica Excel cuando

se escribe un número. En la mayoría de los casos, los números que tienen el formato General se muestran tal y como se escriben. Sin embargo, si la celda no es lo bastante ancha para mostrar todo el número, el formato General redondea los números con decimales. El formato de número General también utiliza la notación científica (exponencial) para números grandes (de 12 o más dígitos).

Número Este formato se utiliza para la presentación general de números. Puede especificar el número de posiciones decimales que desea usar, si desea utilizar separadores de miles y cómo desea mostrar los números negativos.

Moneda Este formato se utiliza para los valores monetarios generales y muestra el símbolo de moneda predeterminado junto a los números. Puede especificar el número de posiciones decimales que desea usar, si desea utilizar separadores de miles y cómo desea mostrar los números negativos.

Contabilidad Este formato también se emplea para valores monetarios, pero alinea los símbolos de moneda y los separadores decimales de los números de una columna.

Fecha Este formato muestra los números de serie de fecha y hora como valores de fecha, en función del tipo y configuración regional (ubicación) que especifique. Excepto en el caso de los elementos que tienen un asterisco (*) en la lista Tipo (ficha Número, cuadro de diálogo Formato de celdas), los formatos de fecha que aplique no cambian el orden de las fechas con el sistema operativo.

Hora Este formato muestra los números de serie de fecha y hora como valores de hora, en función del tipo y configuración regional (ubicación) que especifique. Excepto en el caso de los elementos que tienen un asterisco (*) en la lista Tipo (ficha Número, cuadro de diálogo Formato de celdas), los formatos de hora que aplique no cambian el orden de las horas con el sistema operativo.

Porcentaje Este formato multiplica por 100 el valor de la celda y muestra el resultado con un símbolo de porcentaje. Puede especificar el número de cifras decimales que desea utilizar.

Fracción Este formato presenta los números en forma de fracción, dependiendo del tipo de fracción especificado.

Científico Este formato muestra los números siguiendo la notación exponencial, reemplazando parte del número con E+n, donde E (que significa Exponente) multiplica el número precedente por 10 elevado a la n-ésima potencia. Por ejemplo, un formato Científico con dos decimales muestra 12345678901 como 1,23E+10, que es 1,23 por 10 elevado a la décima potencia. Se puede especificar el número de posiciones decimales que se desea usar.

Texto Este formato trata el contenido de las celdas como texto y muestra el contenido exactamente tal y como se escribe, incluso cuando se escriban números.

Especial Este formato muestra los números como un código postal, número de teléfono o número de la seguridad social.

Personalizado Este formato permite modificar una copia de un código de formato de número existente. Crea un formato de número personalizado que se agrega a la lista de códigos de formato de número. Se pueden agregar entre 200 y 250 números de formato personalizados, dependiendo de la versión de idioma de Excel que se haya instalado.

2. El menú desplegable del botón Formatos de contabilidad nos permite elegir entre varias opciones de formatos específicos de contabilidad y especificar si deseamos agregar el símbolo de dólares americanos o el símbolo de euros.

3. El botón Estilo porcentual aplicará un estilo de porcentaje al valor de la celda.

4. El comando Estilo millares agregará el separador de miles al valor numérico de la celda.

5. Podemos Aumentar decimales mostrados dentro de la celda siempre y cuando el valor numérico contenga dicha cantidad de decimales.

6. También podemos mostrar menos decimales utilizando el botón Disminuir decimales.

7. Si queremos tener más opciones de formato podemos pulsar el indicador de cuadro de diálogo del grupo Número el cual abrirá una nueva ventana que nos dará más opciones de formato.

El cuadro de diálogo Formato de celdas

Otro método de elegir el formato adecuado para los números es a través del cuadro de diálogo Formato de celdas el cual se muestra con tan solo pulsar el indicador de cuadro de diálogo del grupo Número.

Formatos personalizados de números en Excel

Los formatos personalizados de números en Excel nos permiten crear diferentes tipos de formato que se adapten a nuestras necesidades. Cuando ninguno de los formatos prestablecidos es adecuado la mejor opción es crear un formato personalizado.

Crear formatos personalizados en Excel

La manera más fácil de crear un formato personalizado es pulsando la combinación de teclas Ctrl + 1 para desplegar el cuadro de diálogo Formato de celdas y asegurarnos de que estamos en la sección Número. Es entonces cuando seleccionamos la categoría Personalizada.

- Partes de un formato personalizado

Un formato personalizado nos permite especificar 4 códigos dentro de la misma cadena de texto: valores positivos, valores negativos, valores cero y texto. Cada

código debe ser separado por un punto y coma. Considera el siguiente ejemplo de un formato personalizado:

[Verde]Estándar;[Rojo]Estándar;[Negro]Estándar;[Azul]Estándar

En primer lugar observa que los 4 códigos están separados por un punto y coma. El primero código indica que los valores positivos serán de color verde. El segundo código nos dice que los valores negativos serán de color rojo. El tercer código hará que los valores que sean cero tengan un color negro y el último código hace que todas las celdas que sean de tipo texto deberán ser de color azul. Observa cómo se comporta este formato personalizado al aplicarlo a celdas con diferentes valores:

La

palabra Estándar que aparece dentro del formato personalizado que acabamos de crear significa que el número se mostrará en un formato estándar y solamente le aplicaremos un color. Es importante notar que el ejemplo mostrado hace uso de los nombres de colores los cuales podemos utilizar en nuestros formatos personalizados.

- Omitir un código en el formato personalizado

Aunque en el ejemplo mostrado especifiqué los 4 códigos, no siempre es necesario hacerlo. Podemos omitir algunos de los códigos del formato personalizado tomando en consideración lo siguiente:

Si solamente especificamos un solo código entonces se aplicará para todos los valores (positivo, negativo, cero y texto)

Si especificamos dos códigos, el primero aplicará para los valores positivos, los ceros y el texto, mientras que el segundo código será para los valores negativos.

Si especificamos tres códigos, el primero será para los valores positivos y el texto, el segundo código para los valores negativos y el tercer código para los ceros.

Observa el resultado de aplicar un formato personalizado con tres secciones solamente:

[Verde]Estándar;[Rojo]Estándar;[Negro]Estándar

Por último debo decirte que cuando creas un formato personalizado frecuentemente hacemos varios intentos hasta llegar al formato que deseamos. Cada vez que editamos la cadena de texto del formato personalizado, Excel la agrega a la lista como un nuevo formato disponible. Así que cuando hayas logrado el formato personalizado que necesitas  asegúrate de eliminar aquellos formatos que solamente fueron pruebas y que no necesitarás de nuevo.

Alineación de textos

Para que los datos se vean de forma óptima en la hoja de cálculo, puede ajustar la posición de ellos dentro de la celda. Puede cambiar la alineación del contenido de la celda, usar sangría para proporcionar mejor espaciado o mostrar los datos en diferentes ángulos mediante la rotación.

Los comandos de Alineación que observas en la ficha Inicio nos ayudan a darle la orientación adecuada al texto dentro de cada celda así como a combinar celdas independientes para formar una sola celda.

1. En la pestaña Inicio, en el grupo Alineación, realice uno o más de los procedimientos siguientes:

Para cambiar la alineación vertical del contenido de las celdas, haga clic en Alinear en la parte superior , Alinear en el medio   o Alinear en la parte inferior .

Para cambiar la alineación horizontal del contenido de las celdas, haga clic en Alinear texto a la izquierda , Centrar   o Alinear texto a la derecha .

Para cambiar la sangría del contenido de las celdas, haga clic en Disminuir sangría   o Aumentar sangría .

Para girar el contenido de las celdas, haga clic en Orientación   y luego seleccione la opción de rotación que desee.

Para ajustar el texto en una celda, haga clic en Ajustar texto.

Para usar opciones de alineación de texto adicionales, haga clic en el Selector de cuadro de diálogo   junto a Alineación y, a continuación, en la pestaña Alineación del cuadro de diálogo Formato de celdas seleccione las opciones que desee. Por ejemplo, para justificar el texto de una celda, en la pestaña Alineación, haga clic en el cuadro desplegable bajo Horizontal y luego haga clic en Justificar.

Ajustar el texto de una celda

El comando Ajustar texto nos ayuda a que el contenido de una celda se ajuste de manera que sea visibles sin necesidad de ajustar el ancho de la columna.

Excel ajustará el texto de la celda insertando saltos de línea de manera que sea visible con el ancho especificado de la columna. Esto por supuesto afectará el alto de la fila como puedes observar en la imagen superior.

Puedes ajustar la alineación vertical de las celdas que no se ajustaron con los comandos Alinear en la parte superior, Alinear en el medio y con el comando Alinear en la parte inferior.

Combinar  y centrar

El comando Combinar y centrar nos permite seleccionar varias celdas y hacer una sola de todas ellas. Una vez que se han combinado las celdas se centrará el texto. Este botón tiene algunas opciones adicionales. Si pulsas la pequeña flecha que se muestra a la derecha del botón se mostrarán dichas opciones.

La primera es la opción ya comentada Combinar y centrar. La segunda opción es Combinar horizontalmente la cual combinará cada una de las filas de las celdas seleccionadas. La tercera opción es Combinar celdas la cual nos permite unir celdas tanto horizontal como verticalmente. La última opción nos ayuda a Separar celdas combinadas.

Información obtenida de:

http://office.microsoft.com/es-mx/excel-help/ajustar-la-posicion-de-los-datos-en-una-celda-HP010342845.aspx

http://exceltotal.com/formato-de-numeros-en-excel-2013/

http://office.microsoft.com/es-hn/excel-help/formatos-de-numero-disponibles-HP001173902.aspx

http://exceltotal.com/formatos-personalizados-de-numeros-en-excel/

http://exceltotal.com/alineacion-de-celdas/

4.¨Microsoft Excel¨ 

Microsoft Excel es una planilla de cálculos de Microsoft Office y sirve para hacer cálculos por medio de fórmulas o funciones, a través de sus celdas, formadas por columnas y filas.

Su principal función es realizar operaciones matemáticas –de la misma manera que trabaja la más potente calculadora-, pero también la de computar complejas interrelaciones y ordenar y presentar en forma de gráfico los resultados obtenidos. Excel permite colocar, ordenar y buscar datos, así como insertar bloques de texto e imágenes. Los principales elementos de trabajo son:

FILA: Conjunto de varias celdas dispuestas en sentido horizontal.Título de fila: Está siempre a la izquierda y nombra a las filas mediante números, que en el caso de Excel Xp van desde el 1 hasta el 65,536.Columna: Conjunto de varias celdas dispuestas en sentido vertical.Título de columna: Está siempre arriba y nombra a las columnas mediante letras, van desde la A hasta la IV. Luego de la columna Z viene la AA, AB, AC, etc.; luego de la AZ viene la BA, la BB, la BC, etc.; y así sucesivamente.

CELDA: Es la intersección de una fila y una columna y en ella introducimos los gráficos, trátese de texto, números, fecha u otros datos. Una celda lleva

el nombre de la columna, seguido del nombre de la fila. Por ejemplo, la celda que es la intersección de la fila 29 con la columna F, es F29.

RANGO: Los rangos son una referencia a un conjunto de celdas de una planilla de cálculos. Son definidas mediante letras y números. Denominada mediante la celda de una esquina del rango (generalmente la superior izquierda), luego dos puntos y la esquina opuesta. Por ejemplo, al rango que comprende las celdas C4, C5, C6, C7, D4, D5, D6, D7, E4, E5, E6 y E7 es C4:E7.

FUNCIONES

Las funciones son fórmulas predefinidas que ejecutan cálculos utilizando valores específicos, denominados argumentos, en un orden determinado o estructura. Las funciones pueden utilizarse para ejecutar operaciones simples o complejas.

¿CÓMO?:

En el menú Herramientas, elija Complementos.En la lista Complementos disponibles, seleccione el cuadro Herramientas para análisis y, a continuación, haga clic en Aceptar.Si es necesario, siga las instrucciones del programa de instalación.Crear una fórmulaLas fórmulas son ecuaciones que efectúan cálculos con los valores de la hoja de cálculo. Una fórmula comienza por un signo igual (=). Por ejemplo, la siguiente fórmula multiplica 2 por 3 y, a continuación, suma 5 al resultado. = 2*3+5Buscar objetivoEs parte de una serie de comandos a veces denominados herramientas de análisis Y si. En el caso de que conozca el resultado deseado de una fórmula sencilla, pero no la variable que determina el resultado, podrá utilizar la función Buscar objetivo haciendo clic en Buscar objetivo en el menú Herramientas. Al realizar una búsqueda de objetivo, Microsoft Excel varía el valor de celda específica hasta que una fórmula dependiente de dicha celda devuelve el resultado deseado.

Ajustar el valor de una celda para obtener un resultado específico para otra.

1.- En el menú Herramientas, haga clic en Buscar objetivo.

2. En el cuadro Definir celda, escriba la referencia de la celda que contenga la fórmula (fórmula: secuencia de valores, referencias de celda, nombres, funciones u operadores de una celda que producen juntos un valor nuevo. Una fórmula comienza siempre con el signo (=).) Que desee resolver.

3. En el cuadro Con el valor, introduzca el resultado que desee.

4. En el cuadro Para cambiar la celda, introduzca la referencia de la celda que contenga el valor que desee ajustar. A esta celda debe hacer referencia la fórmula en la celda especificada del cuadro Definir celda.

5. Haga clic en Aceptar.

Ejercicio 1 (Aplicando la función Buscar Objetivo de Excel)Sí un préstamo de UM 5,000 al 3.8% mensual para su pago en 6 meses, se triplica cada dos meses, calcular las cuotas a pagar.

Solución:VA= 5,000; i = 0.038; n = 6; C1...6 = ?

La primera cuota puede ser cualquier valor; lo importante es que las demás cuotas (de la segunda en adelante) dependan de la primera; de modo que cuando cambie la primera, las demás cuotas y el resto de la tabla cambien también. Habrá que cambiar el valor de la primera cuota hasta cuando el saldo final sea cero. Es posible hacer esto a mano, pero el computador lo hace más rápido con la opción Buscar objetivo ya mencionada. Definimos la celda donde está el saldo final del último período con el valor cero y pedimos que cambie la celda donde está la primera cuota.Operando con Buscar Objetivo de Excel.1º Elaboramos la tabla de amortización, como ilustramos en el extracto de la hoja de Excel.En la columna E3 (Pago), ingresamos 10 un valor arbitrario, de la siguiente forma:Celda E3 10 [Ingresamos a la celda sin poner el signo (=)]Celda E4 =E3Celda E5 =E4*2 (de acuerdo a la condición del problema).Celda E6 =E5Celda E7 =E6*2Celda E8 =E9

INTERES = SALDO INICIAL x 0.038PAGO = BUSCAR OBJETIVOAMORTIZACION = PAGO - INTERES( =E3 - C3 ) ... ( =E8 - C8)

Cuando la tabla es de muchos períodos (filas) y no exista la condición doble o UM X más cada 2, 3, etc. cuotas; la forma más rápida de operar, es ingresar a la primera celda (PAGO) cualquier número, luego ingresamos a la segunda celda (PAGO) el signo (=) y hacemos clic con el mouse en la primera celda PAGO. Finalmente, colocamos el puntero en la 2º celda PAGO y del ángulo inferior arrastramos el puntero en forma de cruz hasta la celda PAGO final de la tabla.La opción Buscar Objetivo es de aplicación cuando calculemos el valor de las cuotas de cualquier préstamo o inversión con flujos uniformes.

2. FUNCIONES FINANCIERAS

Aún con la rapidez que brinda la hoja de cálculo Excel, la solución de problemas complejos requiere de tiempo y esfuerzo. El tema de las funciones financieras en el presente libro lo dividimos en dos grandes grupos: 9.5.1. Funciones para conversión de tasas de interés y 9.5.2. Funciones para el manejo de series uniformes.

2.1. Funciones para conversión de tasas de interés

Dentro de este grupo clasificamos dos funciones que sirven para convertir tasas de interés efectivas en nominales y viceversa.Los argumentos que utilizan las funciones financieras para conversión de tasas son los siguientes:Núm_per: Es el número de períodos de interés compuesto por año. (Cuando operamos con TASA.NOMINAL).

Núm_per_año:  Es el número de períodos de interés compuesto por año. (Cuando operamos con INT.EFECTIVO).

Int_nominal: Es la tasa de interés nominal anual expresada en términos decimales.

Tasa efectiva: Es la tasa de interés efectiva anual, es decir, la rentabilidad efectiva recibida cuando los intereses son reinvertidos en las mismas condiciones por el tiempo que resta del año.Período de interés compuesto: El tiempo que transcurre entre dos fechas de pago de interés; en el caso de estas funciones suponemos que el interés pagado no es retirado ni consumido, si no reinvertido por el tiempo que resta del año.

2.1.1. INT.EFECTIVODevuelve la tasa efectiva del interés anual si conocemos la tasa de interés anual nominal y el número de períodos de interés compuesto por año. De aplicación cuando los períodos de pago son exactos.

SINTAXIS

INT.EFECTIVO(INT_NOMINAL;NÚM_PER_AÑO)

Si alguno de los argumentos   es menor o igual a cero o si el argumento núm_per_año es menor a uno, la función devuelve el valor de error #¡NUM!La respuesta obtenida viene enunciada en términos decimales y debe expresarse en formato de porcentaje. Nunca divida ni multiplique por cien el resultado de estas funciones. Esta función proporciona la tasa efectiva de interés del pago de intereses vencidos. Para intereses anticipados debe calcularse la tasa efectiva aplicando la fórmula.El argumento núm_per_año se trunca a entero cuando los períodos son irregulares, hay que tener especial cuidado con esta función, sólo produce resultados confiables cuando la cantidad de períodos de pago en el año (núm_per_año) tiene valores exactos; por ejemplo: mensual(12), trimestral(4), semestral(2) o anual (1).

El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula:

EJERCICIO 2 (Aplicación de la función INT.EFECTIVO)(A) Cuando los períodos de pago son exactos y el resultado es confiable:FECHA INICIAL : 15-03-2004FECHA FINAL : 15-06-2004TASA NOMINAL : 68% anual, compuesto trimestralmente

Solución:n = (15/03/2004 - 15/06/2004) = 90/30 = 3, m = (12/3) = 4Aplicando ambos métodos:

(B) Cuando los períodos de pago son inexactos y por lo tanto el resultado es irreal.

FECHA INICIAL : 15-03-2004FECHA FINAL : 15-06-2004TASA NOMINAL : 68% anual, compuesto cada 2.20 mesesSolución:n = (15/03/2004 - 21/05/2004) = 66/30 = 2.2, m = (12/2.2) = 5.2174Aplicando ambos métodos:

Inicio

Observando ambos resultados, constatamos que son diferentes. En estos casos es recomendable el uso de las fórmulas, sus resultados son más reales.2.1.2. Función Financiera TASA.NOMINALDevuelve la tasa de interés nominal anual si conocemos la tasa efectiva y el número de períodos de interés compuesto por año.

Sintaxis

TASA.NOMINAL(tasa_efectiva; núm_per)

El argumento núm_per trunca a entero, hay que tener especial cuidado con esta función, sólo produce resultados confiables cuando la cantidad de períodos de pago en el año (núm_per) tiene valores exactos; por ejemplo: mensual (12), trimestral (4), semestral (2) o anual (1).Si alguno de los argumentos es menor o igual a cero o si el argumento núm_per es menor a uno, la función devuelve el valor de error #¡NUM!La respuesta obtenida viene enunciada en términos decimales y debe expresarse en formato de porcentaje. Nunca divida ni multiplique por cien el resultado de estas funciones.Esta función proporciona la tasa nominal del pago de intereses vencidos. Para el interés anticipado debe calcularse la tasa nominal aplicando la fórmula:

Ejemploi = 0.3449; n = 12; j = ?

2.2. Funciones para el manejo de series uniformes

Presenta las funciones que sirven para resolver problemas en los cuales entre el valor inicial y el valor final de un negocio existen pagos de cuotas o valores recibidos.

En todas las funciones de series uniformes suponemos que los valores recibidos o pagados durante el tiempo del negocio son reinvertidos por el tiempo restante del plazo total, en las mismas condiciones existentes para la inversión original. Un problema es de series uniformes cuando reúne las siguientes condiciones en su totalidad:

a) El monto de los pagos efectuados dentro del tiempo de la inversión es constanteb) La periodicidad de los pagos efectuados dentro del tiempo de la inversión es constante

c) La tasa de interés (de liquidación de los pagos efectuados dentro del tiempo de la inversión) es constante.Los argumentos que utilizan las funciones financieras de series uniformes son los siguientes:

Va, P en los términos financieros. Es el valor actual de una serie de pagos futuros iguales. Si este argumento es omitido, se considerará 0.Pago, C en los términos financieros. Es el pago efectuado en cada período y que no cambia durante la vida de la anualidad. El Pago incluye el capital y el interés pero no incluye ningún otro cargo o impuesto. Este argumento debe tener signo contrario al de Va, para conservar las condiciones del flujo de caja: los ingresos van con signo positivo y los egresos con signo negativo.Nper (n en términos financieros). Es la cantidad total de períodos en una anualidad, es decir el plazo total del negocio.Tasa (i en los términos financieros). Es la tasa de interés por período. Tener en cuenta que no es la tasa anual, si no la tasa nominal del período de pago expresada en términos decimales. Es importante la uniformidad en el uso de las unidades cuando especifiquemos Tasa y Nper.Vf (F en los términos financieros). Es el valor futuro o el saldo en efectivo que desea lograr después de efectuar el último pago. Si el argumento Vf es omitido, asumimos que el valor es 0.Tipo Es el número 0 ó 1 e indica la forma de pago de la cuota entre vencido y anticipado.

Defina tipo0 ó se omite al final del período1 Al inicio del período

Período. Especifica el número ordinal de la cuota en estudio, que debe encontrarse en el intervalo comprendido entre 1 y Nper.Per_inicial y Per_final Especifican el número ordinal de la primera y la última cuota de un período para el análisis respectivo de las cuotas pagadas.Estimar Es una tasa de interés estimada para que el Excel empiece las iteraciones en el cálculo de la tasa de interés de una serie uniforme. Si el argumento Estimar es omitido, suponemos que es 10%.2.2.1. Función Financiera VFPermite calcular VF a partir de C o de VA. También sirve para calcular el valor de VF indicando si es cuota anticipada (tipo=1) o vencida (tipo=0). Si lo que queremos calcular es VF a partir de VA omitimos el valor de C; si la cuota es vencida, omitimos el valor tipo.Devuelve el valor futuro de la inversión, equivalente a los pagos periódicos uniformes a una tasa de interés constante.

Sintaxis: VF(tasa;nper;pago;va;tipo)El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula:

 EJERCICIO 3 (Aplicación de la función VF)Si ahorramos UM 350 mensuales durante 3 años en un banco que paga el 18% nominal anual y deseamos saber cuánto dinero tendremos ahorrado al final de los 3 años:Solución:C = 350; n = (3*12) = 36; i = 0.015 (0.18/12); VF = ?Aplicando ambos métodos, tenemos:

Ingresamos los datos en los argumentos de función en el orden indicado en el cuadro de la sintaxis:

En la solución de los ejemplos y ejercicios en el presente libro, utilizaremos el formato simplificado indicado en el cuadro de la Sintaxis, cuando operemos con la herramienta Funciones Financieras de Excel. Esta metodología de ingresar los datos es aplicable a todas las funciones de Excel, utilizadas en la obra, desde luego, cada con su propia persiana de argumentos de función.Hay tres aspectos a considerar en este ejemplo:a) El interés incluido en el argumento Tasa debe estar en la misma unidad de tiempo utilizada para el argumento Nper. En este caso, como son cuotas mensuales, la tasa de interés debe ser mensual, es necesario dividir por doce la tasa anual nominal.b) VA puede omitirse como apreciamos en el asistente para funciones y en la barra de fórmulas automáticamente deja el espacio en la función, asumiéndolo como cero.

c) Si deseamos que las cifras en la hoja de cálculo sean positivas, introducimos el argumento Pago con signo negativo, como apre- ciamos en el asistente para funciones (-350, en C2).2.2.2. Función Financiera VAPermite calcular VA a partir de C o de VF. También sirve para calcular el valor de VF indicando si es cuota anticipada (tipo=1) o vencida (tipo=0). Para calcular VA a partir de VF, omitir el valor de C; y cuando operemos con cuotas vencidas, omitir el valor tipo. Devuelve el valor actual de la inversión. El valor actual es la suma de una serie de pagos a futuro. Por ejemplo, cuando pedimos dinero prestado, la cantidad del préstamo es el valor actual para el prestamista.La versión XP de Excel, recomienda el empleo de fx insertar función de la barra de fórmulas. Al oprimir fx aparece el menú de funciones y escogemos la función buscada. Esta función conserva las mismas observaciones efectuadas para VF.Sintaxis: VA(tasa;nper;pago;vf;tipo)El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula:

Por ejemplo:

Si ahorramos UM 350 mensuales durante 3 años en un banco que paga el 18% nominal anual y deseamos saber cuánto representan estas mensualidades al día de hoy.

Solución:

C = 350; n = (3*12) = 36; i = 0.015 (0.18/12); VA = ?Aplicando ambos métodos, tenemos:

2.2.3. PAGO

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

SintaxisPAGO(tasa;nper;va;vf;tipo)

Sugerencia: Para encontrar la cantidad total pagada durante el período del préstamo, multiplique el valor devuelto por PAGO por el argumento nper.El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula:

EJERCICIO 4 (Aplicación de la función PAGO)Obtenemos un crédito de UM 10,000 para su pago en 24 cuotas trimestrales iguales, a la tasa nominal anual de 36% por trimestre vencido:

Solución:VA = 10,000; n = 24; i = (0.36/12) = 0.03; C = ?Aplicando ambos métodos, tenemos:

En algunos casos puede darse la necesidad de requerir tanto el VA como el VF; como en el caso del leasing, en el cual, además del valor inicial de un equipo tenemos cuotas mensuales iguales y al final del pago existe la opción de compra para que el usuario adquiera el bien.

Por ejemplo:

En un leasing de UM 50,000 a 24 meses con la tasa de interés del 2.87% mensual y la opción de compra del 12%, la función Pago para calcular la cuota mensual a pagar operaría de la siguiente forma:

Solución:VA = 50,000; i = 0.0287; n = 24; VF = 12%; C = ?

2.2.4. Función Financiera TASA

Devuelve la tasa de interés por período de la anualidad. La TASA es calculada por iteración y puede tener cero o más soluciones. Si los resultados sucesivos de TASA no convergen dentro de 0,0000001 después de 20 iteraciones, TASA devuelve el valor de error #¡NUM!.Con esta función es posible calcular la tasa de interés, combinando no sólo VA y VF, sino también VA y C, C y VF y VA, C y VF.Por ser la tasa del período tiene la característica de ser simultáneamente nominal y efectiva, para convertir ésta tasa en tasa anual debe tenerse cuidado con la fórmula utilizada, dependiendo de qué tasa queremos calcular: la tasa nominal o la tasa efectiva anual (TEA).

SintaxisTASA(nper;pago;va;vf;tipo;estimar)

Por ejemplo:

VA = 5,000; n = 5; C = 1,250; i = ?Función utilizada para calcular la tasa periódica de las anualidades. No existen fórmulas para obtener la tasa de las anualidades.

2.2.5. Función Financiera NPERDevuelve la cantidad de períodos que debe tener la inversión para que sea equivalente a la serie de pagos periódicos iguales.

SintaxisNPER(tasa, pago, va, vf, tipo)La unidad de tiempo consignada en la función Nper debe ser la misma que la utilizada en la tasa de interés.El resultado proporcionado por esta función lo obtenemos también con las siguientes fórmulas, según los casos:

Por ejemplo:i = 0.06; C = 14,000; VA = 93,345.50; n = ?

2.2.6. Función Financiera VNA o VAN

Calcula el valor actual neto de la inversión a partir de la tasa de descuento y pagos futuros (valores negativos) e ingresos (valores positivos).

SintaxisVNA(tasa;valor1;valor2; ...)

Los valores incluidos en el flujo de caja no tienen que ser constantes. Esta es la principal diferencia frente a la función VA, conserva la condición de que tanto la tasa de interés como la periodicidad son constantes; es decir, todo el flujo de caja descuenta a la misma tasa y los valores incluidos en él ocurren a intervalos iguales.Dentro del rango del flujo de caja excluimos el valor presente ubicado en el período cero (0), dicho valor está en UM de hoy. La inversión inicial de la celda con período 0 no ingresa en el argumento valores, posteriormente restamos del resultado que arroje la función.El valor actual neto es un indicador sobre la conveniencia económica de la inversión, involucra la subjetividad del inversionista, que debe seleccionar la tasa de interés para descontar el flujo de caja. Al calcular con dos tasas diferentes obtenemos dos resultados, para evaluar estos casos debe tenerse en cuenta que la respuesta esta expresada en UM del período cero y su significado puede interpretarse de la siguiente manera:

a. VNA > 0, un resultado positivo indica que el negocio estudiado arroja rentabilidad superior a la exigida por el inversionista, deducida la inversión, luego es conveniente llevar a cabo el negocio.

b. VNA = 0, en caso de presentarse, un resultado igual a cero indica que el negocio arroja rentabilidad igual a la exigida por el inversionista, la ejecución del proyecto es opcional.

c. VNA < 0, valor presente neto negativo no significa que el negocio estudiado arroje pérdidas, únicamente la rentabilidad es inferior a la exigida por el inversionista y para él, particularmente, no es conveniente el negocio.

De lo anterior concluimos cuando anunciemos el VNA de un proyecto debe aclararse cuál fue la tasa de descuento utilizada para calcularlo, es decir, cuál fue el valor ingresado en el argumento Tasa.

2.2.7. Función Financiera TIR

Devuelve la tasa interna de retorno (tasa de rentabilidad) de los flujos de caja representados por los números del argumento valores. Estos flujos de caja no son constantes, como en las anualidades. Sin embargo, los flujos de caja deben ocurrir en intervalos regulares, como meses o años. La tasa interna de retorno equivale a

la tasa de interés producida por un proyecto de inversión con pagos (valores negativos) e ingresos (valores positivos) que ocurren en períodos regulares.

SintaxisTIR(valores; estimar)

Para el cálculo de la función TIR incluimos en el rango de valores todo el flujo de caja y es necesario que existan valores positivos y negativos. El argumento Estimar es opcional. En caso de omitirse, el Excel asume la tasa inicial del 10%.La TIR sólo involucra las condiciones particulares de un proyecto y no está afecta por la subjetividad del inversionista. Sin embargo, dificultades de orden matemático llevan a desconfiar de los resultados que arroja.

Finalizamos esta parte con el siguiente ejemplo ilustrativo

César ahorra UM 350 mensuales durante 3 años en un banco que paga el 18% nominal anual y desea saber cuánto dinero tendrá ahorrado al final de los 3 años:

Solución:C = 350; n = 36 (3*12); i = 0.015 (0.18/12); VF = ?

Hay tres aspectos a considerar en este ejemplo:

a) El interés incluido en el argumento tasa debe estar en la misma unidad de tiempo que el utilizado en el argumento Nper, en este caso, como son cuotas mensuales, la tasa de interés debe ser mensual, por lo tanto hay que dividir por doce la tasa anual nominal.

b) VA puede omitirse como apreciamos en el asistente para funciones y en la barra de fórmulas automáticamente deja el espacio en la función, asumiéndolo como cero.

c) Para que las cifras en la hoja de cálculo sean positivas, el argumento Pago va con signo negativo, como apreciamos en el asistente para funciones (-350).

En la solución de los ejemplos y ejercicios en el presente libro, utilizamos el formato simplificado (del ejemplo de aplicación) cuando operemos con la herramienta Funciones Financieras de Excel.

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. Las funciones pueden utilizarse para ejecutar operaciones simples o complejas. Por ejemplo, la función REDONDEAR redondea un número en la celda A10.

Estructura de una función

   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.

   Nombre de función. Para obtener una lista de funciones disponibles, haga clic en una celda y presione MAYÚSC+F3.

   Argumentos. Los argumentos pueden ser números, texto, valores lógicos como VERDADERO o FALSO, matrices, valores de error como #N/A o referencias de celda. El argumento que se designe deberá generar un valor válido para el mismo. Los argumentos pueden ser también constantes, fórmulas u otras funciones.

   Información sobre herramientas de argumentos. Cuando se escribe la función, aparece una información sobre herramientas con su sintaxis y sus argumentos. Por ejemplo, escriba =REDONDEAR( y aparecerá la información. La información sobre herramientas sólo aparece para las funciones integradas.

Escribir fórmulas       Cuando cree una fórmula que contenga una función, el cuadro de diálogo Insertar función le ayudará a introducir las funciones de la hoja de cálculo. A medida que se introduzca una función en la fórmula, el cuadro de diálogo Insertar función irá mostrando el nombre de la función, cada uno de sus argumentos, una descripción de la función y de cada argumento, el resultado actual de la función y el resultado actual de toda la fórmula.

QUÉ SON LAS FUNCIONES LÓGICAS:

SI: La función SI nos permite realizar una pregunta lógica, la cual pueda tener dos posibles resultados Verdadero o Falso y actuar de una u otra forma según la respuesta obtenida.

SI(Pregunta lógica; Acción en caso verdadero; Acción en caso falso). Lo que escribamos dentro del segundo y tercer argumento serán las acciones que se realizarán en caso de que la respuesta a la pregunta lógica sea verdadera o sea falsa. Los dos primeros argumentos son los únicos obligatorios para esta función.

Para realizar la pregunta lógica podremos utilizar los siguientes operadores de comparación: = para preguntar si dos valores son iguales, > para saber si un valor es mayor que otro, < para preguntar por menor, >= con este podremos conocer si es mayor o igual, <= preguntamos por menor o igual, o si deseamos mirar sin son diferente utilizaremos <>

Ejemplo de la función

Imagina que en la celda A1 escribimos la edad de una persona y en la celda A2 queremos que aparezca el texto "Mayor de edad" en el caso que la edad sea igual o superior a 18, mientras que nos interesará aparezca "Menor de edad" en caso que la edad sea menor de 18. 

La función que deberíamos escribir sería =SI(A1>=18;"Mayor de edad”; “Menor de edad") Observa que en el primer argumento preguntamos por mayor o igual que 18, si la respuesta a la pregunta es Verdadera se realizará el segundo argumento: "Mayor de edad", en cambio si la respuesta es falsa, realizamos el tercer argumento: "Menor de edad".

USO DE LAS FUNCIONES LÓGICAS Y CONDICIONALES:

 El uso de funciones condicionales en modelos de hojas de cálculo permite dotar a éstos de una gran flexibilidad, ya que van a posibilitar que una celda o rango adopten distintos valores en función de cuáles sean los valores de otras celdas (sea de la misma hoja, de otras hojas del mismo libro o, incluso, de libros diferentes). En su formato más simple, la sintaxis de la función lcondicional es:

=SI (condición; acción l; acción 2)

Así, el valor que se le asignará a la celda que contenga esta fórmula será el que se derive de la acción 1 si la condición es cierta y el de la acción 2 en caso de que la condición sea falsa. La condición puede ser más o menos simple; algo similar ocurre con las acciones. Analicémoslo más detenidamente.

CONDICIONES 

Para construir las condiciones se usan los operadores lógicos: mayor     mayor o igual     igual     menor o igual     menor     distinto     >                 >=               =                 <=                    <            <>

Excel evalúa la condición y devuelve VERDADERO si la condición se cumple o FALSO en caso contrario (puede comprobarlo escribiendo en cualquier celda la expresión =5>4, por ejemplo). Podemos usar números, rótulos o fórmulas para construir condiciones; todo dependerá del problema concreto que nos estemos planteando. A continuación mostramos algunos ejemplos:

A3<7         (1000/B6)*SUMA(A1:B4)<>F5+F8               G6="Contado"

Además, podemos diseñar una condición que sea la unión de varias condiciones simultáneamente; para ello, usamos las funciones lógicas Y y O

función   lógica Y  =SI(Y(subcondición l; subcondición 2; ... ; subcondición 30); acción l; acción 2)

Cada subcondición puede ser verdadera o falsa; para que el resultado conjunto de la condición Y sea verdadero, todas las subcondiciones han de ser verdaderas; dicho de otro modo, basta con que una de las subcondiciones sea falsa para que el resultado global sea falso, tal como se muestra en la tabla de verdad.

cond1 cond2     Y

F         F          F

F         V          F

V         F          F

V         V          V tabla de verdad de la función lógica Y

Si el resultado global es falso, se ejecuta la acción 2 y la acción 1 en caso contrario.  

Función   lógica O  =SI(O(subcondición l; subcondición 2; ... ; subcondición 30); acción l; acción 2)

En esta ocasión, la evaluación de la condición difiere: para que el resultado conjunto sea verdadero, al menos una de las subcondiciones ha de serlo, ejecutándose la acción l. Sólo en el caso de que todas las subcondiciones sean falsas, el resultado conjunto lo será y se ejecutará la acción 2.

cond1 cond2     O

F         F          F

F         V          V

V         F          V

V         V          V tabla de verdad de la función lógica O  

Existe una tercera función lógica denominada NO cuya misión es alterar el sentido de la condición o subcondición: NO(condición) devuelve verdadero si la condición es falsa y viceversa.

cond         NO F               V

V               F tabla de verdad de la función lógica NO

Como puede observarse, esta función es  perfectamente eludible, ya que podemos hacer lo mismo mediante el uso correcto de los operadores lógicos, como vemos en el ejemplo siguiente:

NO(A 1 >B 1) es igual que A 1 <=B1         NO(A 1 =B 1) es igual que A 1 <>B 1  

ACCIONES 

Las acciones son las que van a asignar los valores a las celdas en donde se encuentren escritas las funciones lógicas. Esta asignación puede ser muy simple (haz que la celda tome el valor 100), o muy compleja, mediante el uso de funciones que pueden, a su vez, incluir funciones condicionales: de esta manera, podemos anidar (es decir, usar una función condicional como argumento de otra función condicional) hasta siete funciones condicionales, de forma que la flexibilidad a la hora de resolver los modelos de hoja de cálculo es muy amplia.

Estructura condicional simple

=SI (condición; acción 1; acción2)                                 ^            ^             V                  |             |                ¯¯¯¯¯¯¯¯               |             F                                |                 ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ En  caso de que la condición sea verdadera se ejecutará la acción1 y la acción2 en caso contrario.

Estructura condicional compleja =SI (condición1; SI(condición2;acción1;acción2); acción 3 )                                                        ^            ^                  ^              V                    |V                 |             |                   |                ¯¯¯¯¯¯¯¯¯¯ |    ¯¯¯¯¯¯¯¯             |                   |                                     |F                               |                   |                                         ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯                   |              F                                                                          |                 ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯ Imaginemos que la acción1 de una condicional es, a su vez, otra condicional. Se trata, en otras palabras, de una anidación de dos condicionales: en caso de que la condición1 sea cierta tendremos que ver  qué ocurre con la condición2; si ésta es verdadera se ejecuta la acción1 y la acción2 si fuera falsa. La acción3 se ejecutará sólo cuando la condición1 haya resultado ser falsa; cada una de estas acciones podrían, a su vez, ser otras funciones condicionales, de manera que podemos anidar, como anteriormente comentamos, hasta siete de estas funciones.

Para construir funciones lógicas correctas es preciso dominar la sintaxis de las mismas y ser lógicos en el diseño; es muy común que se cometan fallos como los indicados a continuación:

=SI(C7>C8; 1;SI(C7<=C8;0;2))

En esta función lógica comparamos el contenido de las celdas C7 y C8, de manera que, en el caso de que C7 sea mayor que C8 se asigne el valor l; en caso contrario, comparamos si C7 es menor o igual que C8: podemos observar como esta última comparación carece de lógica, ya que siempre se cumplirá. En otras palabras, nunca se asignará al valor 2 en la condicional anterior.

=SI(A1>B1;"Al es mayor que B1";SI(Al=B1;"A1 es igual que B1";SI(A1<B1;"Al es menor que B1 ";"¿Y aquí qué pongo?"))

En esta función sobra la última condicional ya que siempre se cumplirá: en el caso de que las dos primeras sean falsas, por tanto Al no sea ni mayor ni igual que Bl,  la tercera condición (A1 < B1) siempre será cierta (es decir, Al será menor que B1). La construcción correcta será:

=SI(A 1 >B 1;"A 1 es mayor que B1 ";SI(A1 =B 1;"A1 es igual que B1 "; "Al es menor que B1"))    

FUNCIÓN BUSCAR V 

Busca un valor específico en la columna más a la izquierda de una matriz y devuelve el valor de la celda cuya fila sea la fila donde encuentre el valor y cuya columna sea aquella de la matriz que corresponda a un número especificado. Esta función se utiliza normalmente para obtener un valor correspondiente a otro dado, p.e dado el código de un artículo obtener su precio, dado un número de cliente obtener su nombre.

SINTAXIS· 

BUSCARV(valor_buscado;matriz_de_comparación;indicador_columna;ordenado) donde:

Valor_buscado es el valor que se busca en la primera columna de la matriz. Valor_buscado puede ser un valor, una referencia o una cadena de texto.

Matriz_de_comparación es el conjunto de información donde se buscan los datos. Se puede utilizar una referencia a un rango, como por ejemplo B3:H14 o Clientes!C10:E101, o un nombre de rango, como por ejemplo Base_de_datos o Lista. Si el argumento ordenado es VERDADERO, los valores de la primera columna del argumento matriz_de_comparación deben colocarse en orden ascendente.

Indicador columna es el número de columna de la matriz_de_comparación desde la cual debe devolverse el valor coincidente. Si el argumento indicador columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_de_comparación; si el argumento indicador columna es igual a 2, devuelve el valor de la segunda columna de la matriz_de_comparación y así sucesivamente.  Si indicador columna es menor que 1, BUSCARV devuelve el valor de error #¡VALOR!; si indicador columna es mayor que el número de columnas de matriz_de_comparación, BUSCARV devuelve el valor de error #¡REF!

Ordenado Es un valor lógico que indica si desea que la función BUSCARV busque un valor igual o aproximado al valor especificado. Si el argumento ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no encuentra un valor exacto, devolverá el valor inmediatamente menor que valor_buscado. Si ordenado es FALSO, BUSCARV devuelve el valor buscado. Si no encuentra ningún valor, devuelve el valor de error #N/A.

OBSERVACIONES

Si BUSCARV no puede encontrar valor_buscado y ordenado es VERDADERO, utiliza el valor más grande que sea menor o igual al valor_buscado

Si el valor_buscado es menor que el menor valor de la primera columna de matriz_de_comparación y ordenado es VERDADERO, BUSCARV devuelve el valor de error #N/A.

Si BUSCARV no puede encontrar valor_buscado y ordenado es FALSO, devuelve el valor de error #N/A.

Ejemplos:

En la hoja de cálculo de la figura anterior, donde al rango A4:C12 se le da el nombre Rango. En este rango podremos encontrar dada una densidad:

su  viscosidad ( para ello el indicador de columna debe ser 2) su temperatura (el indicador de columna debe ser el 3)

en este contexto: BUSCARV(1;Rango;1;VERDADERO) es igual a 0,946

BUSCARV(1;Rango;2) es igual a 2,17

BUSCARV(1;Rango;3;VERDADERO) es igual a 100

BUSCARV(0,746;Rango;3;FALSO) es igual a 200

BUSCARV(0,1;Rango;2;VERDADERO) es igual a #N/A debido a que 0,1 es menor que el menor valor de la columna A

BUSCARV(2;Rango;2;VERDADERO) es igual a 1,71

de tal manera que, si por ejemplo, la celda E7 tiene el valor 0,835, la celda D7 el valor 160 y la celda E7, la fórmula =SI(BUSCARV(E7;Rango;3;FALSO)<=D7;"La temperatura es igual o menor que "&D7;"La temperatura supera los "&D7)

el valor que aparecería en la celda E7 sería La temperatura es igual o menor que 160

Analizando la fórmula de la celda E7 vemos que es una función condicional donde el primer argumento de la condición es la función BUSCARV(E7;Rango;3;FALSO), que para los valores dados devuelve 150, que al ser menor que el contenido de D7 (160) hace que la condición se evalué como verdadera, por lo que se ejecutará la acción1 ("La temperatura cae por debajo de "&D7); esta acción es la concatenación del texto La temperatura es igual o menor que  con el contenido de la celda D7, lo que hace que el resultado de la fórmula en E7 dé, en este caso, La temperatura es igual o menor que 160     

ORDENAR 

Excel permite la ordenación de un rango de celdas por orden alfabético, numérico o temporal, de una forma rápida y sencilla, haciendo uso de la opción DATOS|ORDENAR (con esta simbología queremos decir que primero se pulsa la opción de la barra de menú DATOS y, a continuación, la opción ORDENAR). La ordenación consiste en cambiar los contenidos de unas celdas por el de otras, de forma que aparezca en las distintas columnas (o filas) ordenados. Para ello, en primer lugar, habrá que seleccionar el rango a ordenar; este rango puede incluir también otras celdas en la misma fila (o columna) que las que se vayan a ordenar, conservándose después de la ordenación la misma posición respecto a las celdas clave de la ordenación. Al pulsar sobre el submenú indicado aparece el cuadro Ordenar 

En este cuadro se puede indicar por qué columna (o fila) se quiere realizar la ordenación en primer lugar (Ordenar por). En caso de que haya varias celdas con el mismo contenido, es posible ordenar las filas (o columnas) en función de los datos de una segunda o tercera columna (Luego por). Además, es posible indicar también si la ordenación será Ascendente (por orden alfabético, de menor a mayor, o de anterior a más reciente, según la ordenación sea alfabética, numérica o

cronológica) o bien será Descendente. En el caso de que el rango tenga una columna (o fila) de títulos, que no se deba ordenar, deberá activarse la opción Sí en La lista tiene fila de encabezamiento. Si la ordenación no es en sentido vertical, sino horizontal, deberá señalarse desde el cuadro que aparece al pulsar sobre el botón Opciones, en el campo Orientación. 

 En este cuadro puede indicarse, también, si la ordenación tendrá en cuenta si las letras son mayúsculas o no; si se tiene en cuenta (marcando sobre la casilla Coincidir mayúsculas y minúsculas), en caso de dos textos iguales, aparecerá primero el que tenga las letras minúsculas. El campo Primer

criterio de ordenación se utiliza cuando los datos no obedecen a un orden normal (números, fechas o textos), sino que son textos que representan los días de la

semana o los meses del año.   ESCENARIOS

Hasta el momento, cada celda de una hoja sólo podía tener asociado un valor (fuera un dato tecleado o el resultado de una fórmula). Gracias a los escenarios se va a conseguir que una misma celda tenga asociados distintos valores, uno por cada escenario, de manera que seleccionando un escenario hacemos que la celda tome el valor que está almacenado en el mismo.

Pensemos en una hoja de cálculo en la que se modela la previsión de las ventas de una empresa; imaginemos que el aumento de ventas depende del incremento del mercado, del crecimiento del Producto Interior Bruto y del comportamiento de las empresas competidoras. Estas tres variables se miden mediante unos índices situados resspectimanebte  en las celdas Al, A2 y A3. Concretamente, tal como se ve en la figura para el primer mes, la fórmula del cálculo de la previsión es:

 previsión = base* (1 + 2*%inc demanda - % inc PIB - % inc competencia)

 

La empresa se plantea tres posibilidades (escenarios): optimista (incremento de demanda del 10%, incremento del PIB del 4% e incremento de la competencia del 0%), neutra (incremento de demanda del 5%, incremento del PIB del 2% e incremento de la competencia del 2%), y pesimista (incremento de demanda del 2%, incremento del PIB del l% e incremento de la competencia del 8%). Si

quisiéramos analizar la previsión de ventas optimista, introduciríamos la terna de valores en el rango A1:A3 y observaríamos los resultados; si deseáramos analizar la previsión pesimista, volveríamos a realizar la operación pero con los datos que correspondan a esa previsión, y así cada vez que deseáramos analizar la previsión de ventas de alguno de los tres posibles escenarios que nos hemos planteado.

Para facilitar este tipo de problemática y no tener que introducir los valores cada vez que se quiera ver cuál es el comportamiento en cada situación, disponemos de los escenarios en Excel.

En primer lugar, seleccionamos HERRAMIENTAS|ESCENARIOS. La ventana Administrador de escenarios aparecerá en la pantalla, indicando que no hay definido escenario alguno y que debemos pulsar sobre Agregar para crear nuestro primer escenario.

Una vez pulsemos el botón indicado, una nueva ventana, Agregar escenario, nos preguntará acerca del escenario a crear. Deberemos, en primer lugar, asignar un nombre que identifique el escenario; en nuestro ejemplo, podemos denominar OPTIMISTA al primer escenario.

En segundo lugar, indicar cuáles serán las celdas que contendrán los valores del escenario, en el cuadro Celdas cambiantes, que, en nuestro

ejemplo se corresponden con $A$1, $A$2 y $A$3 (Excel escribe las direcciones con referencias absolutas, aunque ello carece totalmente de importancia, ya que no vamos a copiar las direcciones citadas). Por último, podremos escribir un comentario que documente el escenario, así como activar dos opciones: Evitar cambios y Ocultar.

Las dos opciones citadas en el párrafo anterior son útiles para proteger los escenarios: con Evitar cambios se impedirá la edición del escenario y, por ello, no podrán alterarse sus valores. Con Ocultar el nombre del escenario desaparecerá de la lista, por lo que no podrá seleccionarse para ser alterado. Ambas opciones se activan (si están seleccionadas), sólo

cuando la hoja esté protegida y así se haya indicado.

Cuando pulsemos en el botón Aceptar, una nueva ventana, Valores del escenario, nos interrogará acerca de los valores de las celdas. Basta con teclear los valores de nuestro escenario OPTIMISTA.

Con esta operación, terminamos la creación de nuestro primer escenario. Al pulsar sobre Agregar, volveremos a la ventana Agregar escenario, crearemos el que hemos

llamado NEUTRO y escribiremos los valores de las celdas cambiantes. De forma similar procederemos para el escenario PESIMISTA. Una vez finalicemos, dispondremos de tres escenarios, tal como aparece en la ventana Administrador de escenarios

 Una vez seleccionado uno de los escenarios, al pulsar sobre el botón Mostrar, los datos de las celdas cambiantes del escenario en cuestión se situarán en las celdas Al, A2 y A3. Estas tienen, por tanto, tres ternas de valores asociados, una terna por cada escenario creado.    

TABLAS 

Las tablas permiten conocer la influencia de una o dos variables sobre una fórmula dada, estudiando el resultado de la misma cuando dichas variables toman una serie de valores concretos. Por ejemplo, si el coste del agua depende de su consumo y del precio por metro cúbico, podríamos conocer mediante una tabla cuáles serían los diferentes costes cuando el coste/m3 varíe entre 50 y 250 Pta. de 10 en 10 Pta., y el consumo entre 5 y 25 m3, de 5 en 5 m3. Una vez que tenemos la tabla, podemos saber inmediatamente el coste de 15 m3 a 70 Pta., mirando en la intersección de la fila y la columna que contienen esos datos. Podemos crear también una tabla de una sola entrada, en la que varía un solo valor, por ejemplo, si suponemos fijo el precio y únicamente varía el consumo.

Vamos a ver la utilización de esta herramienta realizando un ejemplo sencillo, la tabla de multiplicar: 

 

El resultado de una multiplicación concreta, por ejemplo, 5 x 6, se encuentra en la intersección de la columna del cinco con la fila del 6, así, en F7 tenemos el valor 30. Esta tabla se puede crear de dos modos:

haciendo uso de fórmulas utilizando la herramienta tabla.

En el primer caso, bastaría con poner en la celda B2 la fórmula =B$1*$A2, y copiarla en el resto de la tabla. Para este caso concreto no es difícil crear la fórmula apropiada pero, de todas formas, hay que pensarla un poco (cualquier otra forma de escribir B1 y A2, p.e  =$B1*A$2, no producirá el resultado esperado).

Utilizando la herramienta tabla nos liberamos de pensar en que formato escribir la referencia a las celdas de la fórmula. Para ello, en primer lugar crearemos los datos iniciales de la tabla, en la primera

columna (A2:A11 en nuestro ejemplo) pondremos los valores de una variable, y en la fila superior (B1:Kl) los valores de la otra. Normalmente, el relleno de series suele ser muy útil aquí, si los datos siguen un cierto orden. En la celda de intersección de la fila y columna de los datos, Al, tendremos que poner la fórmula o una referencia a la fórmula que se quiere calcular (en nuestro ejemplo, ponemos una referencia a la fórmula que habremos  creado en Al6, siendo esta =A14*A15, aunque no se muestra en la Figura; así, en Al pondremos la fórmula =Al6).

Una vez que tenemos creado el esqueleto de la tabla, tenemos que indicar a Excel que obtenga los resultados. Para ello, en primer lugar, deberemos seleccionar

toda la tabla, incluyendo la fila y columna de los datos (A1:K11 en el ejemplo). Una vez seleccionado el rango, utilizaremos el menú DATOS|TABLA, apareciendo un

cuadro en el que deberemos indicar las Celdas de entrada (fila y columna). 

 

Celda de entrada (fila) pregunta por la referencia de la celda en la fórmula original cuyos valores los obtendrá de la fila superior del rango. Si en nuestro ejemplo, la fórmula original, situada en Al6, es =A14*A15, podemos decir que es la celda Al4. Celda de entrada (columna) hace lo propio para la celda cuyos valores se encuentran en la primera columna (A15).

Cuando pulsemos sobre el botón Aceptar, se rellenará la tabla con los valores adecuados. Veamos cómo se han obtenido estos valores. Por ejemplo, en la celda F7 tenemos el valor 30. La fórmula original era =K14*K15, e indicamos a Excel que los valores de la celda Kl4 se tomaran de la fila superior, y los de la Kl5, de la primera columna. El valor de Kl4 para F7 se tomará entonces de Fl (5), y el de K 15 de A7 (6); sustituyendo estos valores en la fórmula, el resultado será 30 (5*6).

Si desea conocer el resultado en el caso de que varíen los valores de más de dos celdas simultáneamente, no será posible hacerlo, ya que sólo se admiten tablas bidimensionales; no se pueden enlazar con otras hojas, ya que, y esto es importante, la tabla a crear debe estar en la misma hoja donde se encuentran los datos.

Si se desea crear una tabla de una sola entrada en la que sólo varíe un dato, sí es posible hacerlo con Excel. Para ello sólo será necesario crear la columna (o fila) donde se encuentran los nuevos valores de la celda cambiante; la referencia a la fórmula original se coloca ahora no sobre la columna de los datos (o a la izquierda

de la fila), sino sobre la columna adyacente de la izquierda (o fila inferior), justo encima de donde se obtendrán los resultados (o justo a la izquierda).

Es posible que cuando se cree la tabla, los resultados no sean los correctos (normalmente serán todos iguales al primero). Esto se debe a que es posible que esté inhabilitado el recalculo automático. Para obtener los resultados, en este caso, será necesario pulsar sobre la tecla <F9>, o elegir el menú HERRAMIENTAS|OPCIONES, y pulsar sobre el botón Calcular ahora que aparece en la pestaña Calcular.    

Simulación y optimización Buscar objetivo

Buscar objetivo es una herramienta que de forma simple y efectiva consigue que obtengamos el valor deseado en una celda cambiando para ello el valor de otra celda. Su potencialidad radica en que entre la celda objetivo y la que se ha de cambiar pueden existir un gran número de celdas relacionadas, que también verán alterado su valor para conseguir el resultado deseado.

Para llevar a cabo una búsqueda de objetivo hay que ejecutar HERRAMIENTAS|BUSCAR OBJETIVO, apareciendo una única ventana en la que hemos de indicar la celda objetivo y el valor que deseamos obtener en ella y la celda que deseamos que varíe su valor para conseguirlo. Para mostrar su funcionamiento vamos a seguir el siguiente ejemplo.

Supongamos que estamos pensando en abrir una tienda de "todo a 100", para lo que deseamos conocer cuántos productos hemos de vender en un mes para conseguir una cifra objetivo de beneficio de 250.000 pesetas mensuales, teniendo en cuenta los gastos mensuales propios de ese tipo de negocio, salarios (95.000), alquiler (125.000) y resto de gastos (85.000), siendo el precio de compra de todos los productos de 72 pesetas y el de venta de 100.

El beneficio mensual esperado será igual al número de productos a vender por la diferencia entre el precio de compra y venta, menos los gastos, B8=B7*(B6-B5)-B2- B3-B4. Si deseamos que sea 250.000, indicaremos en la ventana de buscar objetivo que deseamos definir la celda B8 con un valor 250.000, haciéndola depender del número de productos a vender, B7.

El resultado de la búsqueda de objetivo es que hemos de vender mas de 11.785 productos para conseguir dicho beneficio, lo que supone 491 productos al día, 61 a la hora, más de 1 al minuto (un análisis tan simple como este podría haber evitado el cierre de un gran número de establecimientos de este tipo).

Sol ver 

sol ver es una herramienta que nos permite optimizar los valores de una hoja en función de un conjunto de celdas a las que se pueden indicar que cumplan una serie de restricciones. Es un instrumento muy útil para realizar simulaciones y conseguir resultados de forma relativamente fácil utilizando, sin darnos cuenta, mecanismos de cálculo complejos.

Observemos su funcionamiento mediante un pequeño ejemplo, en el que suponemos que deseamos comercializar vehículos pequeños, de los que no necesitan carnet de conducir, y pretendemos determinar si nos interesa más

comprar un coche acabado o montar nosotros las piezas. Los datos son los siguientes, teniendo en cuenta que al hacerlo nosotros tendremos un 23% de gastos, sobre el coste de las piezas y la mano de obra y que hemos de financiar el coste de fabricación, lo que supone un 3%. El precio de venta, en ambas opciones, será el coste del vehículo más el 10%.

Como puede observarse a priori, parece mejor encargar el vehículo, pero si  la empresa pudiese negociar todas las partidas correspondientes al montaje del vehículo para comprobar que también si es mejor  montarlo y obtener, incluso, un margen algo mayor. Supongamos que: el coste de las piezas no se puede variar, la mano de obra se puede bajar hasta 190.000 pesetas, se  podría rebajar otros gastos hasta el 21,5%, y la financiación en un 0,25%, y deseamos un margen mínimo del 10,75%.

Bajo estas restricciones deseamos conocer si nos interesa más montar los vehículos o pedirlos por encargo, para lo cual recurrimos a la herramienta solver, ejecutando HERRAMIENTAS|SOLVER. Aparece una ventana en la que nos pregunta la celda objetivo, en este caso la del precio de venta del vehículo montado por nosotros, D13, al que deseamos que tome un valor de 605.000 pesetas, el mismo que para el vehículo por encargo.

Las celdas que deseamos variar son las de los costes que pueden cambiar C9:C11(C9, C10 y C11) y la del margen Dl2, . Indicamos al modelo las restricciones pulsando Agregar, apareciendo una ventana en la que pondremos las celdas y los valores máximos o mínimos deseados 

Las celdas de las restricciones y aquéllas que pueden variar, deben estar relacionadas mediante fórmulas con la celda objetivo, de forma que, al pulsar Resolver, se realizan una serie de cálculos en iteraciones que intentan conseguir unos valores de las celdas implicadas que cumplan el objetivo y todas las restricciones. Si la encuentra nos muestra la ventana siguiente.

Si no ha encontrado una solución válida nos lo indica, de forma que hemos de volver a plantear el problema o especificar mejor las posibles variaciones de las celdas a cambiar mediante restricciones más precisas.

Una vez conseguida la solución deseada, podemos cambiar los datos de la hoja con los valores calculados por solver eligiendo la opción Utilizar solución de Solver. 

Solver es una herramienta de una gran potencia cuya explicación en detalle cae fuera del alcance de este curso.

Si no encuentra Solver al pulsar de HERRAMIENTAS debe instalarla desde HERRAMIENTAS|COMPLEMENTOS, teniendo en cuenta que debe tener el fichero SOLVER.XLA en el CD-ROM de instalación o en su disco duro. Si no lo encuentra inicie el proceso de instalación de Excel.  

Tablas dinámicas

 Las tablas dinámicas son muy útiles para organizar, de una manera sencilla y rápida, grandes cantidades de datos procedentes de una hoja de calculo. Permiten agrupar y filtrar la información por cualquiera de los campos y de múltiples maneras, creando una tabla de resumen. Gracias a las tablas dinámicas se puede analizar los datos de una base de datos en función de determinados campos interrelacionados. Estas tablas se califican de dinámicas porque en ellas es muy fácil modificar la agrupación de datos realizada.

Para poder crear una tabla dinámica a partir de los datos de una hoja de cálculo, es necesario que los datos estén organizados en listas (cada campo con sus datos en una columna) con sus respectivos rótulos, es decir, como una base de datos de Excel, en donde los registros serían las filas y, los campos las columnas.

El procedimiento a seguir para su creación, utilizando el asistente para tablas dinámicas, es el siguiente: Ejecutar el comando DATOS|TABLAS DINÁMICAS.

Aparecerá el primer cuadro del Asistente para tablas dinámicas.  

donde hay  que  indicar la procedencia de los datos (Lista o base de datos de Microsoft Excel, Fuente de datos externa o Rangos de consolidación múltiples). Pulsando sobre el botón Siguiente se muestra el siguiente cuadro de diálogo del asistente de tablas dinámicas. 

En él se escribe o selecciona con el ratón el rango de celdas de la hoja (incluido los rótulos, que se utilizarán como nombres de los campos) que se desean organizar en una tabla dinámica. Si previamente se activó el comando DATOS|TABLAS DINÁMICAS, habiendo seleccionado previamente el rango, el asistente sugiere el citado rango. Veamos esto con un ejemplo. Supongamos que disponemos de una hoja de cálculo que contiene las horas trabajadas durante una semana por los programadores de una empresa en unos determinados proyectos, tal como aparece en la Figura siguiente

El rango de celdas con los datos que se desean organizar en una tabla dinámica será: A1:D10. Obsérvese como este rango tiene una estructura similar a las tablas de una base de datos: la información de cada empleado en una fila, como registro, organizada en cuatro columnas o campos. En este segundo paso del asistente, le indicamos o confirmamos (si el asistente propone el rango correctamente) el rango de celdas con los datos fuentes: A1:D10.

En este cuadro de diálogo aparece el comando Examinar, que sirve para elegir un libro de trabajo, especificando la unidad y directorio, es decir la ruta en donde se encuentre. Esta opción se utilizará cuando los datos fuente se encuentran en un libro que no estuviera abierto.

Se deberá pulsar sobre el botón Siguiente para que se muestre el tercer cuadro de diálogo del asistente de tablas dinámicas.

En el tercer cuadro se diseñará la distribución de los campos en la tabla a crear. En la parte derecha de la ventana se muestra un botón para cada campo del rango de datos (ver en la Figura siguiente los botones Empleado, Proyecto, Fecha

y Horas) y en la parte izquierda aparece el área dinámica en donde se diseñará la tabla, que está dividida en cuatro secciones (PÁGINA, FILA, COLUMNA y DATOS), en las que se pueden colocar los distintos campos (pulsando sobre el botón del campo y arrastrándolo a una sección), teniendo presente que se organizarán los datos en la tabla así:

El campo que se coloque en la sección PÁGINA, presentará cada uno de sus valores en una página diferente. Este tipo de campo aparecerá en forma de una lista desplegable, desde la que se podrá seleccionar aquél elemento del que se desee mostrar el resumen.

El campo que se coloque en la sección FILA, mostrará sus elementos como encabezados o títulos de las filas en la tabla.

El campo que se coloque en la sección COLUMNA, mostrará sus elementos como encabezados de las columnas de la tabla.

Las entradas del campo que se coloque en la sección DATOS, se someterán a una determinada operación de cálculo o, también denominada en este contexto, función de resumen: Suma (que es la que se ofrece por defecto cuando las celdas con los datos contienen solamente valores numéricos), Contar (que es la que se ofrece por defecto para el resto de los casos), Promedio, Mínimo, Máximo, Producto, etc. Si la operación de cálculo que Excel ofrece por defecto no es la que queremos utilizar, deberá hacerse clic sobre ella para elegir otra. Se podrá añadir más de un campo en cada sección y es imprescindible colocar al menos uno en la sección DATOS.

Siguiendo con el ejemplo, vamos a crear una tabla dinámica que muestre, para cada programador de la empresa, en páginas individuales, el total de horas trabajadas en cada uno de los proyectos durante la última semana. Para ello, tal como se muestra en la Figura siguiente, hemos colocado el campo Empleado en la sección PÁGINA, lo que permitirá filtrar los datos por empleado, es decir, poder mostrar sólo el trabajo realizado durante la semana de cualquiera de los tres trabajadores, o de todos, si así lo deseamos; el campo Proyecto en la sección FILA para que los diferentes proyectos aparezcan como encabezados de las filas; el campo Fecha en la sección COLUMNA para que las fechas aparezcan como encabezados de las columnas; el campo Horas, que contiene los valores que pretendemos sumar, en la sección DATOS, aceptando como función de resumen,

la Suma, que Excel propone por defecto. 

Cada campo del rango de datos a incluir en la tabla dinámica se puede personalizar (en el ejemplo serán los campos Empleado, Proyecto, Fecha y Horas); bastará con pulsar con el ratón, dos veces, sobre el campo a modificar, para que se muestre el cuadro de diálogo de campo de la tabla dinámica, desde donde se puede, entre otras opciones:

insertar subtotales a los campos filas y columnas, cambiar el nombre del campo en la tabla, cambiar su posición en las secciones de la tabla (orientación de los

elementos del campo) ocultar algunos de sus elementos.

Una vez diseñada la tabla, al pulsar sobre el botón Siguiente se mostrará el cuarto y último cuadro de diálogo del asistente de tablas dinámicas. 

 Desde este último cuadro de dialogo, debemos indicar la posición de destino de la tabla dinámica (eligiendo entre Hoja de cálculo nueva u Hoja de cálculo existente), y tecleando, o seleccionando si la hoja destino estuviera abierta, la dirección de la celda superior izquierda a partir de la cual se colocará la tabla. Si la hoja de

cálculo destino no estuviera abierta, se deberá teclear la ruta de acceso, el nombre de libro de trabajo, el nombre de la hoja y la dirección de la celda.

También se puede pulsar sobre el botón Opciones para ponerle un nombre a la tabla, indicar si se desea o no que se calculen totales para las filas y/o columnas, aplicar o no un autoformato, guardar o no los datos fuente duplicados junto a la tabla creada, actualizar o no los datos siempre que se abra el libro, etc. Sólo resta pulsar sobre el botón Terminar para que Excel cree la tabla dinámica.

Aunque los datos de una tabla dinámica tienen el mismo aspecto que cualquier hoja de cálculo según se ve en las siguientes figuras, no se podrá introducir ni editar los datos directamente en el área de datos, ya que la tabla se encuentra vinculada a unos datos fuentes, y serán éstos los que se puedan modificar.  

  Obsérvese en las figuras citadas, como el campo Empleado, que actúa como una tercera dimensión, funciona como filtro, de manera que si pulsamos sobre el botón de filtro situado en la celda B1, se muestra una lista desplegable con los nombres de los empleados y con la opción Todas. Sólo tenemos que seleccionar el empleado en cuestión para que la tabla muestre únicamente las horas invertidas por ese trabajador en los diferentes proyectos (su página correspondiente). A continuación se muestra la tabla dinámica para cada uno de los empleados. 

 

Una vez creada la tabla, se puede editar su diseño fácilmente, utilizando cualquiera de estos dos métodos:

Volver a repetir los cuatro pasos del asistente para tablas dinámicas. Directamente desde la tabla, podemos colocar el cursor sobre el botón

sombreado con el nombre de un campo y arrastrarlo hasta una nueva posición. También se puede activar el menú contextual asociado a la tabla, pulsando con el botón derecho del ratón sobre cualquier parte de ella para acceder, entre otras opciones, por ejemplo al comando Campo, con el que se muestra el cuadro de diálogo de campo de la tabla dinámica desde el que se pueden modificar algunos de los parámetros que le afectan.

Las tablas dinámicas no se actualizan automáticamente cuando los datos fuentes cambian, sino que una vez tengamos seleccionada una celda de la tabla, se tiene que ejecutar el comando Actualizar datos del menú contextual o en la barra de herramientas para las tablas dinámicas para que se recalcule el contenido de la tabla.

5.¨AGREGADO DE BORDES Y SOMBRAS¨

Bordes y sombreado de celdas

Bordes de una celda

Excel permite agregar bordes a una celda de manera que se pueda dar un mayor énfasis a ciertas secciones de una hoja o a una tabla de datos. Se puede utilizar el botón Bordes para aplicar diferentes estilos de borde.

En primer lugar debes seleccionar las celdas a las que se aplicará el formato y hacer clic sobre el botón Bordes que se encuentra en la ficha Inicio dentro del grupo Fuente. Se mostrará un menú con las diferentes opciones de bordes que se pueden aplicar a la selección.

Haz clic sobre el tipo de línea que deseas aplicar sobre las celdas seleccionadas teniendo las siguientes consideraciones.

Si deseas que Excel aplique solamente bordes en las orillas exteriores de las celdas seleccionadas debes hacer clic sobre la opción Bordes externos o la opción Borde de cuadro grueso.

Si quieres aplicar bordes en todas las orillas de cada celda seleccionada debes hacer clic en la opción Todos los bordes.

Si deseas cambiar el grueso de la línea de los bordes, o su color, debes abrir el cuadro de diálogo Formato de celdas (CTRL + 1) e ir a la sección Bordes donde podrás configurar las opciones adecuadas.

Para remover los bordes de una celda o de un grupo de celdas debes seleccionarlas y hacer clic en el botón Bordes y elegir la opción Sin bordes dentro del menú mostrado.

Aplicar bordes a la celda

1. Seleccione la celda o el rango de celdas a las que desea agregar un borde.

2. En el grupo Fuente de la ficha Inicio, haga clic en la flecha que aparece junto a Bordes y, a continuación, haga clic en el estilo de borde que desee.

Aplicar o quitar bordes de celda en una hoja de cálculo

Mediante estilos de borde predefinidos, puede agregar rápidamente un borde alrededor de las celdas o rangos de celdas. Si los bordes de celdas predefinidos no se adaptan a sus necesidades, puede crear un borde personalizado.

NOTA  Los bordes de celdas que aplica aparecen en las páginas impresas. Si no usa bordes de celdas pero desea que se vean los bordes de las líneas de división de la hoja de cálculo en las páginas impresas, puede mostrar las líneas de división.

Para obtener más información, vea el tema sobre imprimir con o sin las líneas de división de celdas.

Aplicar un borde de celda predefinido

1. En una hoja de cálculo, seleccione la celda o el rango de celdas al que desea agregar un borde, cambiar el estilo de borde o del que desea quitar un borde.

Cómo seleccionar una celda o un rango

2. En la ficha Inicio, en el grupo Fuente, siga uno de los procedimientos siguientes:

Para aplicar un estilo de borde nuevo o distinto, haga clic en la flecha que aparece junto a Bordes   y haga clic en un estilo de borde.

SUGERENCIA  Para aplicar un estilo de borde personalizado o un borde diagonal, haga clic en Más bordes. En el cuadro de diálogo Formato de celdas, en la ficha Borde, debajo de Línea y Color, haga clic en el estilo de línea y en el color que desea. Debajo de Preestablecidos y Borde, haga clic en uno o en varios botones para indicar la ubicación del borde. En Borde hay disponibles dos botones de borde diagonal      .

Para quitar bordes de celda, haga clic en la flecha que aparece junto a Bordes   y, a continuación, haga clic en Sin borde  .

NOTAS

El botón Bordes muestra el estilo de borde usado más recientemente. Para aplicar ese estilo, puede hacer clic en el botón Bordes (no en la flecha).

Si aplica un borde a una celda seleccionada, el borde también se aplica a las celdas adyacentes que comparten un límite de celda con borde. Por ejemplo, si aplica un borde de cuadro para rodear el rango B1:C5, las celdas D1:D5 recibirán un borde izquierdo.

Si aplica dos tipos distintos de bordes a un borde de celda compartido, se muestra el borde aplicado más recientemente.

Un rango de celdas seleccionado recibe el formato como si se tratara de un único bloque de celdas. Si aplica un borde derecho al rango de celdas B1:C5, se muestra el borde sólo en el lado derecho de las celdas C1:C5.

Si desea imprimir el mismo borde a celdas que están separadas por un salto de página pero el borde sólo aparece en una página, puede aplicar un borde interior. De esta manera, puede imprimir un borde en la parte inferior de la última fila de una página y en la superior de la primera fila de la página siguiente. Siga este procedimiento:

1. Seleccione las filas en ambos lados del salto de página.

2. Haga clic en la flecha que aparece junto a Bordes   y, a continuación, en Más bordes.

3. En Preestablecidos, haga clic en el botón Interior .

4. En Borde, dentro del diagrama de vista previa, quite el borde vertical haciendo clic en él.

Quitar el borde a una celda

1. En la hoja de cálculo, seleccione la celda o el rango de celdas al que desea quitar un borde.

Procedimiento para seleccionar una celda o un rango

2. En la ficha Inicio, en el grupo Fuente, haga clic en la flecha situada junto a Bordes   y, a continuación, haga clic en Sin bordes .

Crear un borde de celda personalizado

Puede crear un estilo de celda que incluya un borde personalizado y, a continuación, aplicar ese estilo de celda cuando desee mostrar el borde personalizado alrededor de las celdas seleccionadas.

1. En el grupo Estilos de la ficha Inicio, haga clic en Estilos de celda.

SUGERENCIA  Si no puede ver el botón Estilos de celda, haga clic en Estilos y luego en el botón Más   que se encuentra junto al cuadro de estilos de celda.

2. Haga clic en Nuevo estilo de celda.

3. En el cuadro Nombre de estilo, escriba un nombre adecuado para el nuevo estilo de celda.

4. Haga clic en Formato.

5. En la ficha Borde, en Línea, en el cuadro Estilo, haga clic en el estilo de línea que desea usar para el borde.

6. En el cuadro Color, seleccione el color que desea usar.

7. En Borde, haga clic en los botones de bordes para crear el borde que desea usar.

8. Haga clic en Aceptar.

9. En el cuadro de diálogo Estilo, en El estilo incluye (según el ejemplo), desactive las casillas correspondientes al formato que no desea incluir en el estilo de celda.

10.Haga clic en Aceptar.

11.Para aplicar el estilo de celda, realice lo siguiente:

1. Seleccione las celdas a las que desea aplicar formato con el borde de celda personalizado.

Procedimiento para seleccionar celdas, rangos, filas o columnas

2. En el grupo Estilos de la ficha Inicio, haga clic en Estilos de celda.

3. Haga clic en el estilo de celda que desea aplicar.

http://office.microsoft.com/es-mx/excel-help/aplicar-o-quitar-bordes-de-celda-en-una-hoja-de-calculo-HP001216433.aspx

Aplicar sombreado a la celda

1. Seleccione la celda o rango de celdas a las que desea aplicar el sombreado.

2. En el grupo Fuente de la ficha Inicio, haga clic en la flecha que aparece junto a Color de relleno y, a continuación, en Colores del tema o Colores estándar, haga clic en el color que desee.

Aplicar o quitar el sombreado de celdas

Puede agregar sombreado a las celdas rellenándolas con colores sólidos o tramas específicas. Si experimenta problemas relacionados con la impresión de sombreado en colores, compruebe que las opciones de impresión estén configuradas correctamente.

Además, cuando ya no necesite el sombreado en las celdas, puede quitarlo.

Rellenar celdas con colores sólidos

1. Seleccione las celdas a las que desea aplicar o quitar el sombreado.

Cómo seleccionar celdas, rangos, filas o columnas

2. En la ficha Inicio, en el grupo Fuente, siga uno de los procedimientos siguientes:

Para rellenar las celdas con un color sólido, haga clic en la flecha que aparece junto a Color de relleno   en el grupo Fuente de la ficha Inicio y, a continuación, haga clic en el color de la paleta que desea utilizar.

Para aplicar el último color seleccionado, haga clic en Color de relleno .

   

SUGERENCIA Si desea aplicar un color de fondo distinto para toda la hoja de cálculo, haga clic en el botón Seleccionar todo antes de hacer clic en el color que desea usar. Este procedimiento ocultará las líneas de división, pero es posible facilitar la lectura de la hoja mostrando los bordes de las celdas alrededor de todas las celdas.

Rellenar celdas con tramas

1. Seleccione las celdas que desea rellenar con una trama.

Cómo seleccionar celdas, rangos, filas o columnas

2. En la ficha Inicio, en el grupo Fuente, haga clic en el Selector de cuadro de diálogo , situado junto a Fuente.

Método abreviado de teclado  También puede presionar CTRL+MAYÚS+F.

3. En el cuadro de diálogo Aplicar formato a celdas, en la ficha Relleno, dentro de Color de fondo, haga clic en el color de fondo que desea usar.

4. Siga uno de los procedimientos siguientes:

Para utilizar una trama con dos colores, haga clic en otro color en el cuadro Color de Trama y, a continuación, haga clic en un estilo de trama en el cuadro Estilo de Trama.

Para utilizar una trama con efectos especiales, haga clic en Efectos de relleno y, a continuación, haga clic en las opciones que desee en la ficha Degradado.

Verificar opciones de impresión para imprimir sombreado en colores

Si las opciones de impresión están establecidas en Blanco y negro o Calidad de borrador (ya sea a propósito o porque el libro contiene hojas de cálculo complejas o de gran tamaño y gráficos que provocan la activación automática del modo borrador), el sombreado de las celdas no podrá imprimirse en colores.

1. En la ficha Diseño de página, en el grupo Configurar página, haga clic en el Selector de cuadro de diálogo , situado junto a Configurar página.

2. En el cuadro de diálogo Diseño de página, en la ficha Hoja, dentro de Impresión, compruebe que estén desactivadas las casillas Blancas y negras y Calidad de borrador.

 NOTA   Si no puede ver colores en la hoja de cálculo, es posible que esté trabajando en el modo contraste alto. Si no puede ver colores en la vista previa antes de imprimir, es posible que no haya seleccionado una impresora de color.Quitar el sombreado de las celdas

1. Seleccione las celdas que contienen un color o trama de relleno.

Cómo seleccionar celdas, rangos, filas o columnas

2. En el grupo Fuente de la ficha Inicio, haga clic en la flecha situada junto a Color de relleno y, a continuación, en Sin relleno.

http://office.microsoft.com/es-mx/excel-help/aplicar-o-quitar-el-sombreado-de-celdas-HP001216434.aspx