TABLAS DINÁMICAS.

Las tablas dinamicas son las grandes desconocidas dentro de excel. Muchos usuarios, incluso usuarios avanzados, desconocen su uso y su increíble potencialidad. Podría decirse, sin exagerar que las tablas dinámicas son las herramientas más potentes de excel.

Una tabla dinámica nos permite realizar síntesis de los datos de que disponemos para poder analizarlos. De hecho en muchas empresas se malgastan recursos para obtener unos resultados que tienen muy a mano sabiendo utilizar las tablas dinamicas.

Para ir conociendo en profundidad la enorme potencia de las tablas dinámicas, vamos a ver un sencillo ejercicio. Poco a poco ya lo iremos complicando.

Partiremos de la siguiente tabla con los datos de una seriede conferenciantes, con las distintas ciudades, sueldo percibido y horas trabajadas.

En esta tabla están los datos que necesitamos para responder a las siguientes preguntas:

¿Cuantas conferencias se han dado en Madrid?, ¿Cuanto ha ganado Nadia Vidal y Cristina Garcia? ¿Cual es el numero de horas que Nadia Vidal ha dado en Barcelona? ¿Cual es el sueldo promedio de cada conferenciante? ¿ Y el sueldo medio por ciuidad? Y así, con este tipo de preguntas podíamos seguir y seguir.

Puede hallarse la solución sin emplear tablas dinámicas que, como ya hemos dicho, es la solución que emplean en muchas empresas, pero es muy complicada, laboriosa y, sobre todo, de un sólo uso, esto es, podemos diseñar complicadas fórmulas empleando funciones lógicas, por ejemplo, pero sólo valdrán para el caso concreto que estoy usando. Por contra, la solución mediante tablas dinámicas, como su propio nombre indica, es eso dinámica, con apenas unos cambios que tardamos 10 segundos en hacer podremos responder a varias preguntas disitintas.

Vamos a responder una a una a cada una de las preguntas que hemos hecho utilizando tablas dinámicas.

1) ¿Cuantas conferencias se han dado en Madrid?

Lo primero que haremos será, ahora y siempre,seleccionar el rango de datos que queremos usar para la tabla dinamica. En este caso sería el rango A1:D15. Es buena práctica seleccionar las columnas completas A, B, C y D, pulsando [CTRL] + el botoncito de su rótulo, para que, en el caso que la tabla se agrande, tenga más valores, las tablas dinámicas siguan valiéndonos.

Una vez seleccionado el rango, en nuestro caso, las columnas A, B, C y D. Nos vamos al menú Insetar, opción Tabla dinámica.

En principio, dejaremos las opciones que hay seleccionadas: dos para el rango de datos, que ya hemos seleccionado previamente, y otras dos para la ubicación de la tabla dinámica, que por defecto es en una nueva hoja.

Le damos a aceptar, y nos aparece, ya en la hoja nueva, una serie deopciones y menús para generar nuestra tabla dinámica.

Elegiremos el campo delegación (la columna de las ciudades) y, para saber cuantas conferencias ha habido, cualquier otro campo, ya que dependerá de la función que le asignemos, nosotros hemos elegido el campo ponente.

Arrastramos el campo Delegación al área Etiqueta de la Fila y el campo Ponente al área Valores.

 Vemos que en el área Valores, pone Cuenta de Ponentes, en un desplegable (marcado en rojo en la imagen). En este caso lo dejaremos como está porque lo que queremos es que cuente el número de ponentes, pero ahí podríamos elegir otras funciones como la función SUMA, PROMEDIO, etc.

Llegados a este punto se nos ha generado la siguiente tabla dinámica:

En la que se ve el número de conferencias por cada ciudad y el total.

Como quiera que en el ejercicio nos piden sólo las de Madrid, elegiríamos Madrid del deplegable rotulado como Etiquetas de Fila.

El resultado es una tabla similar a la anterior, pero en la que sólo aparece el dato de Madrid:

De este modo, en un instante podemos obtener la información por las localidades que queramos.

En este ejemplo, hemos visto claramente porque se llaman tablas dinámicas, porque son tablas que se modifican muy fácilmente en función de lo que nos interese.

VALIDACIÓN DE DATOS.

LOS DATOS

Ya hemos visto que Excel se utiliza principalmente para introducir datos, ya sea literales como fórmulas. En este tema nos vamos a centrar en algunas de las operaciones típicas que se pueden realizar sobre ellos.

ELIMINAR FILAS DUPLICADAS

Frecuentemente, cuando trabajamos con un gran volumen de información o recopilamos datos desde varios orígenes diferentes, aparecen en nuestro libro de trabajo filas idénticas. A menos que lo que nos interese sea estudiar la frecuencia con la que aparece un determinado registro, la mayoría de las veces no nos interesará tener duplicados, porque no aportan información adicional y pueden comprometer la fiabilidad de las estadísticas basadas en los datos.

Por ejemplo, si disponemos de un listado de trabajadores y queremos saber la media de edad, el cálculo se vería comprometido en el caso de que un mismo trabajador apareciese varias veces.

Para eliminar filas duplicadas:

- Deberemos tener como celda activa uno de los registros a comprobar, de forma que, si existen varias tablas distintas, Excel sepa interpretar a cuál nos referimos. Visualizarás un marco alrededor de todos los registros que se verán afectados por la comprobación.

- En la prestaña Datos pulsamos Quitar duplicados 

Si te quieres asegurar de que realmente se comprueban las filas que deseas, puedes seleccionarlas manualmente antes de pulsar el botón.

VALIDACIÓN DE DATOS

La validación de datos nos permite asegurarnos de que los valores que se introducen en las celdas son los adecuados; pudiendo incluso mostrar un mensaje de error o aviso si nos equivocamos.

Para aplicar una validación a una celda.

- Seleccionamos la celda que queremos validar.

- Accedemos a la pestaña Datos y pulsamos Validación de datos. En él podremos escoger remarcar los errores con círculos o borrar estos círculos de validación. Pero nos vamos a centrar en la opción Validación de datos....

Nos aparece un cuadro de diálogo Validación de datos como el que vemos en la imagen donde podemos elegir entre varios tipos de validaciones.

En la sección Criterio de validación indicamos la condición para que el datos sea correcto.

Dentro de Permitir podemos encontrar Cualquier valor, Número entero, Decimal, Lista, Fecha, Hora, Longitud de texto y personalizada. Por ejemplo si elegimos Número entero, Excel sólo permitirá números enteros en esa celda, si el usuario intenta escribir un número decimal, se producirá un error.

Podemos restringir más los valores permitidos en la celda con la opción Datos, donde, por ejemplo, podemos indicar que los valores estén entre 2 y 8.

Si en la opción Permitir: elegimos Lista, podremos escribir una lista de valores para que el usuario pueda escoger un valor de los disponibles en la lista. En el recuadro que aparecerá, Origen: podremos escribir los distintos valores separados por ; (punto y coma) para que aparezcan en forma de lista.

En la pestaña Mensaje de entrada podemos introducir un mensaje que se muestre al acceder a la celda. Este mensaje sirve para informar de qué tipos de datos son considerados válidos para esa celda.

En la pestaña Mensaje de error podemos escribir el mensaje de error que queremos se le muestre al usuario cuando introduzca en la celda un valor incorrecto.

 Para practicar estas operaciones te aconsejamos realizar el Ejercicio Crear una validación de datos.

FUNCIONES.

MANEJO DE FUNCIONES EN EXCEL

¿Que es una funcion  en excel?

Una función es una fórmula predefinida por Excel que opera sobre uno o más valores (argumentos) en un orden determinado (estructura). El resultado se mostrará en la celda donde se introdujo la formula.

El tipo de argumento que utiliza una función es específico de esa función. Así, 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. Un argumento puede ser una constante, una fórmula o incluso otra función.

Excel cuenta con una gran variedad de funciones dependiendo del tipo de operación o cálculo que realizan. Estas funciones pueden ser 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.

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 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 formula 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, ademas 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 anidadad. 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.

Creación de Gráficos

Una de las utilidades más apreciadas de las Hojas de Cálculo es la posibilidad de Insertar gráficos de todo tipo en los estudios e informes que se confeccionen. Excel 2010 posee una gestión de gráficos bastante buena, y en su uso en la enseñanza y en Pequeñas oficinas  permite cumplir la mayoría de los objetivos. Además, en esta Versión se ha mejorado la calidad visual y las opciones de formato.

Los términos más comunes que se utilizan en los gráficos son :

• Filas: se refiere a la forma de ver el gráfico, si se elige en forma de filas el gráfico aparecerá en forma horizontal.

• Columnas: se refiere a la forma de ver el gráfico, si se elige en forma de columnas el gráfico aparecerá en forma vertical.

• Barras: es un tipo de gráfico tipo filas u horizontal.

• Rango de datos: se refiere al conjunto de valores o datos que conforman el gráfico, que por lo general es un conjunto de celdas.

• Series: las son conjuntos de datos en forma de filas o columnas que tienen valores de un mismo rubro o categoría, por lo cual sí hay más de uno se deben nombrar las series, colocando un nombre significativo a los valores que representará en el gráfico.

• Leyenda: es la serie o series que se mostraran en el gráfico, o de otra manera es un cuadro que se puede colocar arriba, abajo, izquierda o derecha del área de trazado del gráfico, ésta representará cada uno de los rubros o categorías de los valores graficados.

• Área de trazado: el área de trazado de un gráfico es el cuadro interior del gráfico en donde se muestran los objetos o figuras que representan los valores que se eligieron para graficar.

Los pasos a seguir para crear un gráfico son los siguientes:

   1. Seleccione los datos a representar en el gráfico.

   2. Seleccione el menú Insertar.

   3. Elige la opción Gráfico y escoja el tipo de grafico que desea...

Para confeccionar un gráfico necesitaremos una tabla previa de datos. Como por ejemplo:

FORMULAS.

¿QUE ES UNA FORMULA EN EXCEL?

Manejo de Formulas

Las fórmulas en Excel son expresiones que se utilizan para realizar cálculos o procesamiento de valores, produciendo un nuevo valor que será asignado a la celda en la cual se introduce dicha fórmula. En una fórmula, por lo general,  intervienen valores que se encuentran en una o más celdas de un libro de trabajo. Las fórmulas están conformadas por operadores de cálculo, operandos y, con frecuencia, por funciones. Para introducir una fórmula en una celda, se debe entrar como primer carácter el signo igual ( El signo igual  =  le indica a Excel que los caracteres que le siguen  constituyen una fórmula ). Cuando se escribe la fórmula no se deben dejar espacios en blanco dentro de la misma.

Por ejemplo,  la  fórmula   =A5+A4*3  expresa que  se multiplique  el valor que contiene la celda A4 por el valor constante  3 y, a continuación, se le sume el valor que contiene la celda A5 al anterior resultado ( como se verá mas adelante la multiplicación * tiene mayor prioridad que la suma + ).  Si la celda A5 contiene el valor numérico 15, la celda A4 contiene el valor numérico 4, entonces, esta fórmula al ser calculado por el Excel producirá  27 como resultado final, el cual será asignado a la celda en la que se ingresó la fórmula.

1.1.  Componentes de una fórmula

Una fórmula en Excel puede contener cualesquiera de los siguientes elementos: referencias a celdas, constantes, operadores y funciones.

Por ejemplo, la fórmula que permite calcular el área de la superficie que está dentro de una circunferencia,  A = p R², se puede escribir en Excel como:

=PI()*B1^2

En esta fórmula podemos identificar los elementos nombrados anteriormente:

-  Referencias a celdas:  B1.  Se hace referencia al valor que contiene  la celda B1.

-  Constantes:  el valor 2. Números o valores escritos directamente en una fórmula.

- Operadores:  ^ y  *. El operador ^ (acento circunflejo) eleva un número a una potencia, y el operador * (asterisco) multiplica.

-  Funciones: la función  PI(). Esta función al ser invocada devuelve el valor de pi: 3,141592...

2.  OPERADORES

Operador aritmético	Significado	Ejemplo
+          (signo más)	Suma	A3+3
-           (signo menos)	Resta Cambio de signo	B3-B1      -A1
*           (asterisco)	Multiplicación	B3*C3
/            (barra diagonal o slash)	División	D3/3
%         (signo de porcentaje)	Porcentaje	20%
^          (acento circunflejo)	Exponenciación	C3^2

Operador de comparación	Significado	Ejemplo
=        ( igual )	Igual a	A1=B1
>        ( mayor )	Mayor que	A1>B1
<        ( menor )	Menor que	A1<B1
>=     ( mayor o igual )	Mayor o igual que	A1>=B1
<=     ( menor o igual)	Menor o igual que	A1<=B1
<>     (distinto)	Distinto de	A1<>B1

Operador de texto	Significado	Ejemplo
&     ( "y" comercial)	Concatena o une dos valores para generar un nuevo valor de texto continuo.	-  "Sierra"&" Nevada"    produce el  valor “Sierra Nevada” -  A3&B3    crea un nuevo valor de texto formado por     el valor de texto que contiene la celda A3    concatenado o unido con el valor de texto    que  contiene la celda B3

Operador de referencia	Significado	Ejemplo
:     (dos puntos)	Operador de rango que genera una referencia a todas las celdas  que se encuentran entre dos referencias a celdas.	B5:B15  Hace referencia a todas las celdas que se encuentran en el rango  B5 hasta B15
;    (punto y coma)	Operador de unión que combina varias referencias en una sola.	B5:B15;D5:D15  Hace referencia a las celdas que se encuentran en el rango B5 hasta B15 más las celdas en el rango D5 hasta D15
(espacio)	Operador de intersección que genera una referencia a celdas comunes     a las dos referencias.	B7:D7 C6:C8  Hace referencia a la celda C7

Orden	Operador	Descripción
1º	:  (dos puntos)     (un solo espacio)  ;  (punto y coma)	Operadores de referencia
2º	-	Cambio de signo (como en  -A10)
3º	%	Porcentaje
4º	^	Exponenciación
5º	*  y  /	Multiplicación y división
6º	+  y  -	Suma y resta
7º	&	Une dos cadenas de texto    (concatenación)
8º	=  <  >  <=  >=  <>	Comparación

3  OPERANDOS EN LAS FÓRMULAS

	Hace referencia a:
A10	La celda que se encuentra en la columna A y la fila 10
A10:A20	El rango de celdas de la columna A y de las filas de la 10 a la 20.
B15:E15	El rango de celdas de la fila 15 y de las columnas B a E.
A10:E20	El rango de celdas de las columnas A a E y de las filas 10 a 20.
5:5	Todas las celdas de la fila 5
5:10	Todas las celdas de las filas 5 a 10.
H:H	Todas las celdas de la columna H
H:J	Todas las celdas desde la columna H hasta la columna J

apertura, los argumentos de la función separados por punto y coma[2]  y un paréntesis de cierre.
3.3.2   Funciones Anidadas
3.4   Introducir una fórmula

Fórmula	Acción
=128+345	Suma 128 y 345
=5,25^2	Halla el cuadrado de 5,25

1. Seleccionar la celda en que desee introducir la fórmula.

1. Escribir el signo igual (=).

1. Introducir la fórmula.

1. Presionar ENTRAR.

Fórmula	Acción
=C2	Utiliza el valor de la celda C2
=Hoja2!B2	Utiliza el valor de la celda B2 de Hoja2
=-A10	Cambia de signo al valor numérico que se encuentra en la celda  A10
=A1+23	Suma al valor que contiene la celda A1 el número 23

1. Seleccionar la celda en que desee introducir la fórmula.

1. En la celda seleccionada o en la barra de fórmulas  , escribir = (signo igual).

1. Escribir a continuación la fórmula.

1. Al  finalizar de escribir la fórmula se presiona ENTRAR o se presiona el botón introducir  .

1. Seleccionar la celda en que desee introducir la fórmula.

1. En la celda seleccionada o en la barra de fórmulas  , escribir = (signo igual).

1. Comenzar a escribir la fórmula y para ingresar las referencias a celdas dentro de la fórmula, se selecciona una celda, un rango de celdas, una ubicación de otra hoja de cálculo o una ubicación de otro libro. Puede arrastrar el borde de la selección de celdas para mover la selección, o bien arrastrar la esquina del borde para ampliar la selección.

1. Al  finalizar de escribir la fórmula se presiona ENTRAR o se presiona el botón introducir  .

Fórmula	Acción
=SUMA(A:A)	Suma todos los valores numéricos en la columna A
=PROMEDIO(A1:B4)	Calcula  el promedio de todos los valores  numéricos en el rango A1:B4

1. Seleccionar la celda en que se desee introducir la fórmula.

1. Para iniciar la fórmula con la función, se debe hacer clic en Insertar función    en la barra de fórmulas  , o abrir el menú insertar y seleccionar la opción Función….

1. Seleccionar la función que desee utilizar. Se puede seleccionar de la lista de funciones que se muestra el nombre de la función; si se hace difícil localizar el nombre de la función que interesa, se puede seleccionar la categoría a la cual pertenece la función o escribir una pregunta que describa lo que desee hacer en el cuadro Buscar una función (por ejemplo, al escribir "sumar números" devuelve la función SUMA).

1. Luego se introducen los argumentos en su correspondiente cuadro de diálogo que se muestran en la ventana Argumentos de función, siguiendo las especificaciones indicadas  para cada argumento. Para introducir referencias de celdas como argumentos, se pueden escribir directamente con el teclado o, como es más fácil y seguro, presionando el botón   Contraer diálogo  , para ocultar el cuadro de diálogo temporalmente, y se procede a seleccionar  la celda o  celdas en la hoja de cálculo, después se  presiona el botón Expandir diálogo  , para volver al cuadro de diálogo.

1. Una vez que se tenga completa la fórmula se debe presionar el botón aceptar, para que la fórmula se introduzca en la celda y se calcule su resultado.

1. Seleccionar la celda en que desee introducir la fórmula.

1. Para iniciar la fórmula con la función, se debe hacer clic o presionar el botón Insertar función   en la barra de fórmulas .

1. Seleccionar la función que se necesita utilizar como función de primer nivel. Se puede seleccionar de la lista de funciones que se muestra el nombre de la función correspondiente; si se hace difícil localizar el nombre de la función, se puede seleccionar la categoría a la cual pertenece la función o escribir una pregunta que describa lo que desee hacer en el cuadro Buscar una función (por ejemplo, al escribir "sumar números" devuelve la función SUMA).

1. Se introducen los argumentos en su correspondiente cuadro de diálogo que se muestran en la ventana Argumentos de función, siguiendo las especificaciones indicadas  para cada argumento.

• Para introducir referencias de celdas como argumentos, se pueden escribir directamente con el teclado o, como es más fácil y seguro, presionando el botón   Contraer diálogo  , para ocultar el cuadro de diálogo temporalmente, y se procede a seleccionar  la celda o  celdas en la hoja de cálculo, después se  debe presionar el botón Expandir diálogo  , para volver al cuadro de diálogo.

• Para escribir otra función como uno de los argumentos, se introduce la función en el cuadro de argumento deseado. Por ejemplo, se puede agregar SUMA(G2:G5) en el cuadro de ediciónvalor_si_verdadero.

1. Para cambiar las partes de la fórmula mostradas en el cuadro de diálogo Argumentos de función, se debe hacer clic en el nombre de función en la barra de fórmulas  . Por ejemplo, si se hace clic en SI, aparecerá la descripción de la estructura de la función SI, con una lista de los argumentos requeridos por esta función.

• Para introducir la misma fórmula en un rango de celdas, se debe seleccionar en primer lugar el rango, luego se introduce la fórmula y, a continuación, se presionan simultáneamente las teclas <CTRL> y <ENTRAR>.

• Si se está familiarizado con los argumentos de una función, se puede utilizar la información sobre funciones que aparece después de escribir el nombre de la función y el paréntesis de apertura. Se puede hacer clic en el nombre de la función para ver el tema de la Ayuda correspondiente a la función o hacer clic en un nombre de argumento para seleccionar el argumento correspondiente de la fórmula. Para ocultar la información sobre funciones, en el menú Herramientas hacer clic en Opciones y desactivar la casilla de verificación Información sobre herramientas de funciones de la ficha o pestaña General.

4   COPIAR UNA FÓRMULA

1. Seleccionar la celda que contenga la fórmula a copiar.

1. Comprobar que las referencias de celda utilizadas en la fórmula producirán el resultado deseado. Si se requiere, se debe cambiar el tipo de referencia de celda, por ejemplo, si en una fórmula se necesita que siempre se haga referencia a una misma celda, entonces esta referencia se debe cambiar a referencia absoluta o mixta.

1. Abrir el menú Edición, y elegir  la opción Copiar.

1. Luego seleccionar la celda o celdas en la que se desea copiar la fórmula.

1. Abrir de nuevo el menú Edición. Para copiar la fórmula y el formato hacer clic en Pegar.

1. Para copiar la fórmula solamente hacer clic en Pegado especial y, a continuación, en Fórmulas.

 selección.    Cuando se sitúa el cursor del mouse (ratón) sobre el controlador de relleno, el apuntador cambia a una cruz  negra)   .

1. Seleccionar  la celda que contenga la fórmula a copiar.

1. Comprobar que las referencias de celda utilizadas en la fórmula producirán el resultado deseado. Si se requiere, se puede cambiar el tipo de referencia de celda, por ejemplo, si en una fórmula se necesita que siempre se haga referencia a una misma celda, entonces esta referencia se debe cambiar a referencia absoluta o mixta.

1. Colocar  el cursor  sobre el cuadro de llenado y, a continuación, arrastrar el cuadro de  llenado, presionando el botón izquierdo del mouse o ratón,  hasta la celda o  rango de celdas en las que se desear copiar la fórmula.

Fórmula en C1 (origen)	Tipo de referencias	Fórmula en C3
=A2*B3	(columna relativa y fila relativa)	=C4*D5
=A$2*B$3	(columna relativa y fila absoluta)	=C$2*D$3
=$A2*$B3	(columna absoluta y fila relativa)	=$A4*$B5
=$A$2*$B$3	(columna absoluta y fila absoluta)	=$A$2*$B$3
=$A2*B$3	(columna o fila absoluta o relativa)	=$A4*D$3
=A2*$B$3	(columna o fila absoluta o relativa)	=C4*$B$3
=A2*B$3	(columna o fila absoluta o relativa)	=C4*D$3
=A$2*$B$3	(columna o fila absoluta o relativa)	=C$2*$B$3

 
5.    MODIFICAR O EDITAR  UNA FÓRMULA

1. Haga doble clic en la celda que contiene la fórmula que desea modificar.

1. Modifique el contenido de la celda preferiblemente en la barra de fórmulas    

1. Realice las modificaciones que sean necesarias (insertar,  cambiar o eliminar caracteres en la fórmula), utilizando para desplazarse en la misma el cursor del mouse  y/o las teclas de direccionamiento del cursor.

1. Para finalizar la edición y aceptar  las modificaciones realizadas presione la tecla ENTRAR o  presione el botón introducir    de la barra de fórmulas.

6.   ELIMINAR  FÓRMULAS

1. Seleccionar  la celda que contenga la fórmula.

1. Presionar la tecla <SUPR>.

7.    CORREGIR FÓRMULAS

Los operadores especifican el tipo de operación o procesamiento que se desea realizar con los elementos de una fórmula. Microsoft Excel incluye cuatro tipos diferentes de operadores: aritméticos, de comparación, texto y de referencia.

2.1.  Tipos de operadores

- Operadores aritméticos:   Se utilizan para realizar las operaciones matemáticas básicas como suma, resta o multiplicación (combinando los valores numéricos y generando resultados numéricos). Se pueden utilizar los siguientes operadores aritméticos.

-   Operadores de comparación:   Se utilizan cuando se requiere comparar entre si dos valores. Como operadores de comparación tenemos los siguientes:

( Cuando se comparan dos valores utilizando estos operadores de comparación, el resultado es un valor lógico: VERDADERO o FALSO ).

-   Operador de concatenación de texto:   Se utiliza el signo ( & “ampersand” ) para unir o concatenar una o varias cadenas de texto con el fin de generar un solo elemento de texto.

-   Operadores de referencia:   Permiten utilizar rangos de celdas, produciendo referencias a una lista de celdas, para ser utilizadas por otras operaciones en los cálculos; por ejemplo, como argumentos en las funciones.

Una fórmula simple  en Excel ( tiene un solo operador ), se puede representar de la forma siguiente:

                     = < operando_A >   < operador >    < operando_B >  

2.2    Fórmulas compuestas en Excel

No siempre las fórmulas que se requieren utilizar son fórmulas simples ( fórmulas en las que sólo se utiliza un operador ), ya que es mas frecuente necesitar fórmulas en donde  se requieren  dos o mas operadores, lo cual implica cierta dificultad tanto para expresar correctamente la fórmula, así como también, para la evaluación de los diferentes operadores de manera que se obtenga el resultado correcto, como por ejemplo,  la fórmula =PI()*B1^2 que se presentó anteriormente, es una fórmula compuesta  ya que tiene 2 operadores aritméticos ( * y ^ ).

Una fórmula compuesta se puede representar de la forma siguiente:

= <operando_A> <operador 1> <operando B> <operador  2> <operando_C > …….

Cualquiera de los operandos puede ser a su vez  una fórmula, esto es, puede estar formado por otros operandos y operadores.

2.2.1  Orden en que Excel realiza las operaciones en las fórmulas

Como se mencionó anteriormente,  una fórmula está compuesta por los elementos o valores que se van a procesar ( los operandos ), combinados mediante los operadores. Excel realiza las operaciones especificadas en la fórmula, indicadas por los operadores, de acuerdo a un orden que ya tiene preestablecido. Para el cálculo de la fórmula Excel primero evalúa, de izquierda a derecha, los distintos operadores que la conforman determinando en que orden los debe procesar, para luego realizar los diferentes cálculos u operaciones según el orden encontrado.

2.2.2  Precedencia de los operadores

Si se combinan varios operadores en una única fórmula, Excel ejecutará las operaciones en el orden correspondiente a cada operador según la tabla que se muestra a continuación. Si una fórmula contiene operadores con la misma precedencia (por ejemplo, si una fórmula contiene un operador de multiplicación y otro de división), Excel realizará primero la operación que esté mas a la izquierda.

                                              Tabla de precedencia de los operadores

2.2.3  Uso de paréntesis

Para cambiar el orden de evaluación de los operadores, se debe escribir entre paréntesis la parte de la fórmula a la que se requiere cambiar el orden preestablecido, de tal forma que ésta se procese antes que las demás. Los paréntesis se deben colocar por pares, es decir, un paréntesis que abre y otro paréntesis que cierra.

Por ejemplo, la siguiente fórmula:          =5+2*3              

Produce como resultado  11 porque Excel calcula la multiplicación (5º lugar según la tabla) antes que la suma (6º lugar según la tabla). La fórmula multiplica 2 por 3 y luego suma 5 al resultado.

Por el contrario, si se utilizan paréntesis para cambiar el orden de evaluación, la fórmula se puede escribir como:                 =(5+2)*3

En este caso Excel sumará 5 más  2 y luego multiplica el resultado por 3, con lo que se obtiene 21.

En el siguiente ejemplo, los paréntesis que rodean la primera parte de la fórmula le indican a Excel que calcule primero la suma del valor que contiene la celda B4 más el valor constante 25, y después divida el resultado obtenido entre la sumatoria de los valores que contienen o se encuentran en  las celdas D5, E5 y F5.

=(B4+25)/SUMA(D5:F5)

En una fórmula los operandos son los distintos valores que se utilizan para realizar las operaciones especificadas por los distintos operadores. Estos operandos pueden ser valores constantes, referencias a celdas, funciones.

3.1   Constantes

Una constante es un valor que no se calcula ya que el mismo representa su valor. Por ejemplo, la fecha 9-10-2008, el número 210 y el texto "Ganancias trimestrales" son constantes. Una referencia a una celda, una fórmula, o un valor obtenido como resultado de una fórmula, no son  constantes.

Si se utilizan sólo constantes en una fórmula en vez de referencias a celdas (por ejemplo, =30+70+110), el resultado cambia sólo si modifica la fórmula, por lo que no tiene sentido utilizar este tipo de fórmulas.

3.2   Referencias a celdas

Una referencia a celda o celdas, identifica una celda o un rango de celdas en una hoja de cálculo e indica a Excel en qué celdas debe buscar los valores o los datos que se requieren en una fórmula. En las referencias se puede utilizar celdas de distintas partes de una hoja de cálculo. También puede hacerse referencia a las celdas de otras hojas en el mismo libro de trabajo y a otros libros de trabajo. Las referencias a celdas de otros libros de trabajo se denominan vínculos.

3.2.1   Estilo de referencia A1

De forma predeterminada, Excel utiliza el estilo de referencia A1, que se refiere a las columnas identificadas mediante letras (de A a IV, para un total de 256 columnas) y a las filas identificadas mediante números (del 1 al 65.536). Estas letras y números se denominan títulos o nombres de fila y de columna. Para hacer referencia a una celda, se debe escribir la letra de la columna seguida del número de fila. Por ejemplo, B2 hace referencia a la celda que se encuentra ubicada en la intersección de la columna B y la fila 2.

3.2.2     Referencia a celdas en otra hoja de cálculo

En el siguiente ejemplo, la función PROMEDIO calcula el valor promedio de los valores contenidos en el rango B1:B10 de la hoja de cálculo denominada Marketing del mismo libro de trabajo.

Nombre de la hoja          Referencia a una celda o rangos de celdas de la hoja                                   

=PROMEDIO(Marketing!B1:B10)

                   Separa la referencia de la hoja y la referencia de las celdas            

En este ejemplo se puede observar la referencia a otra hoja de cálculo en el mismo libro, donde el nombre de la hoja de cálculo seguido de un signo de exclamación (!) preceden a la referencia de un rango de celdas.

3.2.3   Referencias relativas y absolutas en las fórmulas

-  Referencias relativas   Una referencia relativa de celda en una fórmula, siempre conserva la posición relativa entre la celda que contiene la fórmula y la celda a la que hace referencia. Si se traslada dicha fórmula de una celda a otra celda (se mueve o se copia) se modifica la referencia de celda en la fórmula que se crea en la celda destino. De forma predeterminada, cuando se ingresa una fórmula se utilizan referencias relativas.

Cuando se copia una fórmula de una celda a otra celda, la referencia a celdas en las fórmula que se crea en la celda destino  se ajusta automáticamente; como por ejemplo, si la celda B2 contiene la fórmula  =A1 (que hace una referencia relativa a la celda A1) y se copia a la celda B3, se modifica automáticamente la  fórmula obteniéndose en la celda B3  la nueva fórmula como   =A2.

      Fórmula, con referencia relativa,  copiada de la celda B2 a la celda B3

-  Referencias absolutas   Una referencia absoluta de celda en una fórmula, siempre hace referencia a la misma celda ubicada en una columna y fila específica. Si se traslada dicha fórmula de una celda a otra celda (se mueve o se copia) la referencia absoluta de celda en la celda destino no varia. De forma predeterminada cuando se ingresa una fórmula se utilizan referencias relativas y para  cambiarlas a referencias absolutas,  se debe anteponer el signo $ antes del nombre de columna y del número de  fila de  la celda, por ejemplo  $A$1.

Si  una fórmula que contiene referencias absolutas se copia a otra u otras celdas, la referencia absoluta no se modifica. Por ejemplo, si la fórmula =$A$1, que contiene una referencia absoluta a la celda A1, se copia de la celda B2 a la celda B3, la fórmula es la misma en ambas celdas.

     Fórmula, con referencia absoluta, copiada de la celda B2 a la celda B3

-  Referencias mixtas   Una referencia mixta de celdas 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.; mientras que una referencia de fila absoluta adopta la forma A$1, B$1, etc. Si  se traslada dicha fórmula de una celda a otra celda (se mueve o se copia), se cambia la referencia relativa y la referencia absoluta permanece invariable.

Si una fórmula que contiene referencias mixtas se copia a otra u otras celdas, la referencia relativa se modifica automáticamente y la referencia absoluta no se modifica. Por ejemplo, si la fórmula  =A$1, que contiene una referencia mixta que indica que la fila no debe variar,  se copia de la celda B2 a  la celda C3, la nueva fórmula que contendrá la celda C3 es entonces  =B$1.

     Fórmula, con referencia mixta, copiada de la celda B2 a la celda C3

3.3    Funciones

Las funciones son fórmulas predefinidas que proporciona Excel, las cuales ejecutan cálculos utilizando los valores especificados (denominados argumentos) en un orden determinado, para  producir un nuevo valor o grupo de valores. Las funciones pueden utilizarse para ejecutar operaciones simples o complejas. Por ejemplo, la función REDONDEAR(A10;2)  redondea un valor numérico que está en la celda A10 hasta 2 posiciones decimales.

3.3.1     Estructura de las funciones

Todas las funciones incluidas en Excel tienen la siguiente estructura:

                                    Nombre(arg ₁;arg ₂;……;arg _n)

La estructura de una función comienza por el nombre de la función, un paréntesis de  

- Nombre de función. Cada una de las funciones incluidas en Excel tiene un nombre único que las diferencia unas de otras, este nombre es una cadena de caracteres alfabéticos, por ejemplo la función PROMEDIO. En algunos casos el nombre incluye uno o más puntos (.) dentro de la cadena de caracteres que lo conforman, como por ejemplo la función PAGO.INT.ENTRE.

   Para obtener una lista de funciones disponibles, se selecciona una celda y se presiona  el  botón  para  insertar una función     en la barra de fórmulas  , si está activa esta barra de fórmulas;  o  se abre el menú  Insertar y a continuación se selecciona la opción  Función….

- Argumentos. Los argumentos es una lista de valores separados por punto y coma (;), y pueden ser números, referencias de celda, texto entre comillas, valores lógicos como VERDADERO o FALSO, matrices, o valores de error como #N/A. Los argumentos pueden ser también constantes, fórmulas u otras funciones. La cantidad de argumentos que deban ingresarse a una función es definido por la función misma, y cada uno de estos  argumentos  deberá ser un valor válido y del tipo requerido por el correspondiente orden en que es especificado por la función. Algunas funciones no necesitan argumentos como la función PI( ). Nótese que a pesar de no tener argumentos esta función, es obligatorio colocarle tanto el paréntesis de apertura como el de cierre.

Información sobre la estructura de la función. Cuando se escribe la función, aparece la información sobre la estructura de esta función, con su sintaxis y sus argumentos. Por ejemplo, al ingresar en una celda =REDONDEAR(  aparecerá la descripción de esta función. La información de la estructura de las funciones  sólo aparece para las funciones que vienen integradas en Excel[3]. En este ejemplo, el signo igual ( = ) se ingresa antes del nombre de la función para indicar que la función está incluida en una fórmula.

En algunos casos, puede ser necesario utilizar una función como uno de los argumentos de otra función. Por ejemplo, la siguiente fórmula utiliza la función SI (=SI(arg₁;arg₂;arg₃)), la cual compara el resultado producido por la  función  PROMEDIO  (función anidada) con el valor 50, para determinar cual valor se le va a asignar a la celda en la que se introduce la fórmula.

       

Resultados válidos   Cuando se utiliza una función anidada como argumento, ésta deberá devolver el mismo tipo de valor que el requerido por  este argumento. Por ejemplo, si el argumento especifica  un valor VERDADERO o FALSO, la función anidada deberá devolver VERDADERO o FALSO. Si éste no es el caso, Excel mostrará el valor de error  #¡VALOR!

Límites del nivel de anidamiento   Una fórmula puede contener como máximo siete niveles de funciones anidadas. Si la Función B se utiliza como argumento de la Función A, la Función B es una función de segundo nivel. En el ejemplo anterior, la función PROMEDIO y la función SUMA son ambas funciones de segundo nivel porque son argumentos de la función SI. Una función anidada dentro de la función PROMEDIO será una función de tercer nivel, etc.

Como se mencionó anteriormente, las fórmulas son expresiones que efectúan cálculos con los valores que se encuentran en la misma hoja de cálculo o en otra hoja del mismo libro de trabajo, para producir un nuevo valor que se va a asignar a la celda en la cual se introduce la fórmula. Para introducir una fórmula en una celda, se debe entrar como primer carácter el signo igual ( = ).

3.4.1   Introducir una fórmula sencilla

Una fórmula sencilla o simple  contiene un solo operador y uno o dos operandos, ejemplos de estas fórmulas se muestran a continuación:

Para introducir estas fórmulas se puede seguir el siguiente procedimiento:

Otra forma para ingresar una fórmula, es utilizar la barra de fórmulas    si está activada.

3.4.2        Introducir una fórmula que contenga referencias de celdas:

Las siguientes fórmulas contienen referencias relativas de celdas. La celda que contiene la fórmula se denomina celda dependiente cuando su valor depende de los valores de otras celdas. Por ejemplo si la celda B2 contiene la fórmula  =C2, entonces se tiene que la celda B2 es dependiente de la celda C2.

Para introducir estas fórmulas se puede seguir cualquiera de los siguientes procedimientos:

-  Procedimiento 1

-  Procedimiento 2

Cuando se introduce una fórmula que contiene referencia de celdas  Excel muestra en colores diferentes las distintas referencias. Por ejemplo, al introducir en la celda D9 la fórmula  =(C9*B9) se podrá observar algo como lo siguiente:

 

3.4.3   Introducir una fórmula que contenga una función

Las siguientes fórmulas contienen funciones.

Para introducir en Excel una fórmula que contenga funciones, como las mostradas en el cuadro anterior, se puede seguir el siguiente procedimiento:

3.4.4   Introducir una fórmula con funciones anidadas

Como se mencionó anteriormente, una función anidada es aquella en que uno o varios de sus argumentos  son a su vez funciones. Como por ejemplo:

            =SI(PROMEDIO(F2:F5)>50;SUMA(G2:G5);0)

Esta fórmula produce como resultado la sumatoria de un conjunto de números (SUMA) que se encuentran en el rango de celdas comprendido desde la celda G2 hasta la celda G5 (G2:G5), sólo si el PROMEDIO de otro conjunto de números, que se encuentran en el rango de celdas comprendido desde la celda F2 hasta la celda F5 (F2:F5), es mayor que 50; en caso contrario, produce como resultado el valor  0 (cero).  

             

Para introducir esta fórmula se puede seguir el siguiente procedimiento:

Cuando se trabaja con una hoja de cálculo, el objetivo primordial es expresar los cálculos requeridos que llevan a la solución de un problema mediante una o más fórmulas. Pero es frecuente que, por la naturaleza del problema, una fórmula que se ha introducido en una celda se necesite copiarla a otra u otras celdas donde se aplica la misma fórmula. Al copiarse una fórmula de una celda a otras celdas, las referencias de celda pueden cambiar de acuerdo al tipo de referencia que se utilice.

Para copiar se pueden utilizar los siguientes procedimientos:

-  Procedimiento 1

-  Procedimiento 2

Si la fórmula que se necesita copiar va a ser copiada a una celda o rango de celdas que están contiguas o  adyacentes a la misma, se puede copiar la fórmula utilizando el cuadro de llenado o botón de relleno (cuadrado negro pequeño situado en la esquina inferior derecha de la 

4.1    Procedimiento utilizado por Excel para crear las fórmulas que se copian

Cuando se copia una fórmula de una celda a otras u otras celdas el Excel aplica el siguiente procedimiento:

1.      Obtiene el desplazamiento en columnas y filas que existe entre la celda origen y la celda destino.

2.      Para crear la fórmula en la celda destino, utiliza como base la fórmula existente en la celda origen o celda que se copia, modificando, en cada una de las referencia de celdas que se encuentran en esta fórmula, la referencia a la columna  sumándole o restándole el desplazamiento de columna obtenido antes, y luego hace lo mismo con la referencia a la fila. Si la referencia a columna o fila está escrita en forma absoluta, el desplazamiento encontrado no es aplicado, esto es, la referencia a la  columna o fila permanece igual o no es modificada.

En el ejemplo siguiente se explicará que sucede con las referencias de celda cuando se copia una fórmula de la celda A1 a la celda C3:

    Fórmula que se copia de la celda A1 a la celda C3

Como se puede observar, entre las celdas A1 y C3 hay un desplazamiento en columnas de 2 y un desplazamiento de filas de 2.

-          Si la fórmula que se tiene en la celda A1 es =A2*B3 (columna relativa y fila relativa), entonces la fórmula que se tendrá en la celda C3 es  =C4*D5, en donde se observa que a cada columna de las referencias de celda de la fórmula original se le suma 2, y a cada fila también se le suma 2.

-          Si la fórmula que se tiene en la celda A1 es =A$2*B$3 (columna relativa y fila absoluta), entonces la fórmula que se tendrá en la celda C3 es  =C$2*D$3, en donde se observa que a cada columna de las referencias de celda de la fórmula original se le suma 2, y las filas permanecen sin variar o fijas ya que están escritas como absolutas (el signo $ antes del número de fila).

-          Si la fórmula que se tiene en la celda A1 es =$A2*$B3 (columna absoluta y fila relativa), entonces la fórmula que se tendrá en la celda C3 es  =$A4*$B5, en donde se observa que  las  columnas de las referencias de celda de la fórmula original  permanecen sin variar o fijas ya que están escritas como absolutas (el signo $ antes del nombre de columna) y a cada fila  se le suma 2.

-          Si la fórmula que se tiene en la celda A1 es =$A$2*$B$3 (columna absoluta y fila absoluta), entonces la fórmula que se tendrá en la celda C3 es  =$A$2*$B$3, en donde se observa que  tanto las  columnas y filas de las referencias de celda de la fórmula original  permanecen sin variar o fijas ya que están escritas como absolutas (el signo $ antes del nombre de columna).

-          Si la fórmula que se tiene en la celda A1 es =$A2*B$3, entonces la fórmula que se tendrá en la celda C3 es  =$A4*D$3.

-          Si la fórmula que se tiene en la celda A1 es =A2*$B$3, entonces la fórmula que se tendrá en la celda C3 es  =C4*$B$3.

-          Si la fórmula que se tiene en la celda A1 es =A2*B$3, entonces la fórmula que se tendrá en la celda C3 es  =C4*D$3.

-          Si la fórmula que se tiene en la celda A1 es =A$2*$B$3, entonces la fórmula que se tendrá en la celda C3 es  =C$2*$B$3.

Sin por alguna razón, una fórmula que ya se ha ingresado en una celda necesita ser modificada, no es conveniente volver a escribir la fórmula desde el principio, sino utilizar el procedimiento para modificación o edición del contenido de una celda, el cual se muestra a continuación:

( para cancelar los cambios presione la tecla  ESCape o presione el botón  ).

Para eliminar fórmulas que se encuentren en una hoja de cálculo, el procedimiento más directo y sencillo es el siguiente:

Se pueden eliminar todas las fórmulas que están en un rango de celdas, para lo cual se debe seleccionar primero el rango de celdas y luego se presiona la tecla  <SUPR>.

Al igual que un corrector gramatical, Excel emplea algunas reglas para comprobar si hay problemas en las fórmulas. Estas reglas no garantizan que la hoja de cálculo no tenga ningún problema, pero ayudan en gran medida a encontrar los errores más comunes.

Los problemas se pueden revisar de dos formas: de uno en uno, como con el corrector ortográfico, o inmediatamente sobre la hoja de cálculo mientras se trabaja. Cuando Excel detecta un problema, muestra un pequeño triángulo en la esquina superior izquierda de la celda. Ambos métodos presentan las mismas opciones.

          Celda que posiblemente tiene un problema de fórmula

Si se selecciona la celda que muestra este triángulo aparece al lado de la misma el  botón    que al hacer clic en él muestra un conjunto de opciones. Se puede solucionar el problema utilizando alguna de las opciones que aparecen u omitirlo. Si se omite, ya no volverá a aparecer en las comprobaciones de errores subsiguientes. Sin embargo, todos los errores que haya omitido se pueden restablecer para que vuelvan a mostrarse.

Es frecuente que en una hoja de cálculo nos aparezca este indicador de error en celdas que con toda seguridad sabemos que no tienen errores,  como cuando Excel detecta que al copiarse una fórmula a una celda, una de las celdas contiguas a ésta contiene una fórmula que es diferente, lo cual lo podríamos interpretar como una señal de precaución.

Más adelante se explicará con  detalle estos y otros procedimientos para corregir errores.