REFERENCIAS RELATIVAS Y ABSOLUTAS

Referencias relativas en Excel 

 De manera predetermina Excel utiliza referencias relativas al momento de insertar una referencia de celda en una fórmula. Las referencias relativas son actualizadas automáticamente por Excel al momento de copiar la fórmula a otra ubicación.
 En el siguiente ejemplo tenemos una fórmula en la celda E6 que realiza la suma de varias celdas.
 La imagen tiene habilitado el rastreo de precedentes para poder visualizar las celdas involucradas en el cálculo.



 Referencias relativas en Excel 2010 Al momento de copiar la fórmula una celda hacia abajo, descubriremos que Excel modifica todas las referencias agregando una fila a todas ellas. Observa el cambio en las referencias después de haber copiado la fórmula:




 Referencias relativas en una fórmula de Excel Si en lugar de copiar la fórmula hacia abajo, la copiamos hacia la derecha, entonces Excel modificará las referencias de celda agregando una columna a cada una de ellas de la siguiente manera:




 De esta manera comprobamos que Excel actualiza las referencias de celdas al momento de copiar una fórmula. Estas referencias se conocen como relativas porque serán modificadas por Excel en relación con la celda a donde se copie la fórmula.

 Referencias absolutas en Excel 

 A diferencias de las referencias relativas, las referencias absolutas no se modifican aunque copiemos la fórmula a otras celdas. Para hacer que una referencia se convierta en absoluta utilizamos el símbolo de moneda ($) anteponiéndolo a la columna y fila de la referencia. Tomando el mismo ejemplo anterior, voy a convertir la referencia a la celda A1 en una referencia absoluta.
 En la siguiente imagen puedes ver en la barra de fórmulas que de todas las referencias incluidas en la fórmula solo la referencia a la celda A1 es absoluta:




 Referencias absolutas en Excel 2010 Puedes notar que es una referencia absoluta porque está escrita como $A$1. Hasta este momento no podemos notar diferencia alguna con el ejemplo anterior ya que la suma sigue siendo la misma, pero veremos una diferencia al momento de copiar la fórmula. Observa lo que sucede cuando copio la fórmula hacia abajo:



 Copia de una referencia absoluta en Excel Toda las referencias has sido modificadas agregando una fila, pero la referencia a la celda A1 se mantenido fija porque es la única referencia absoluta en nuestra fórmula. Ahora observa lo que sucede al copiar la fórmula de la celda E6 a la derecha:



 Referencias relativas y absolutas en Excel 2010 Todas las referencias se desplazan una columna a la derecha, pero la referencia a la celda A1 permanece constante aún después de haber copiado la fórmula. Las referencias absolutas serán de mucha ayuda cuando necesitamos indicar a Excel que considere siempre el valor de una misma celda sin importar que la fórmula sea copiada a otra ubicación. Un atajo para convertir una referencia relativa en una referencia absoluta es colocar el cursor de la barra de fórmulas sobre la referencia que deseamos transformar y entonces pulsar la tecla F4 para que Excel introduzca los símbolos de moneda a un lado de la columna y de la fila.

MANEJO DE TABLAS DINAMICAS

TABLAS Y GRAFICOS DINAMICOS

¿Por qué se llaman tablas dinámicas? Seguramente te preguntarás por qué se llaman Tablas Dinámicas. Básicamente, las Tablas Dinámicas te permiten girar o mover datos para generar respuestas a preguntas. Cuando hayas creado una Tabla Dinámica, podrás ver fácilmente cuál es el efecto de girar los datos en la información de la Hoja de Cálculo.
 Crear un reporte de tabla dinámica
 Paso 1: Selecciona las Celdas que quieras usar en tu reporte de Tabla Dinámica, en tu Hoja de Cálculo. Paso 2: Selecciona la pestaña Insertar y haz clic en el botón Tabla Dinámica. Cear una tabla dinámica
Paso 3: Haz clic nuevamente en Tabla Dinámica. Excel selecciona las Celdas en la actual Hoja de Cálculo y una ventana para Crear una Tabla Dinámica abrirá. Cear una tabla dinámica

 Paso 4: Selecciona una tabla o rango que ya esté seleccionado y en el campo Tabla o Rango mostrará el rango de los datos seleccionado. Nueva Hoja de Cálculo también está seleccionada predeterminadamente como el área donde el reporte aparecerá.
Paso 5: Haz clic en Hoja de Cálculo Existente y selecciona una Hoja de Cálculo, si no quieres que aparezca la Tabla Dinámica en una nueva hoja.



Paso 6: Haz clic en Aceptar. Generar el reporte Crear un reporte con tabla dinámica Si usas la Hoja de Cálculo del ejemplo para crear una Tabla Dinámica, verás que los títulos de la Columna son Vendedor, Región, Cuenta, Monto de orden, y Mes. Cuando creas una Tabla Dinámica, cada encabezado de Columna en tus datos se convierte en un campo que puede ser usado en el reporte. La Lista de Campo aparece al lado derecho del reporte, mientras que el Área de Diseño aparece a la izquierda.

 Crear un reporte con tabla dinámica 

 Paso 1: Determina qué pregunta quieres que tu reporte de Tabla Dinámica responda. Por ejemplo, usando la Hoja de Cálculo, querrás saber cuál vendedor vendió más.
 Paso 2: Determina los campos que son necesarios para responder estas pregunta. En este ejemplo, vendedor y monto de la orden.
 Paso 3: Selecciona las casillas al lado del Campo Vendedor en la Lista de Campos de Tabla Dinámica. El campo aparecerá en el área de Arrastrar campos, en la parte inferior de la lista de campos y el área de diseño. El monto de ordena parece a la derecha.
 Paso 4: Selecciona la casilla al lado de campo de Monto de la Orden en la Lista de Campos de Tabla Dinámica. El campo aparecerá en el área de Arrastrar campos, en la parte inferior de la Lista de Campos y el área de diseño. Verás los datos de los vendedores en la izquierda. Ahora podrás ver la respuesta a tu pregunta en el reporte al lado izquierdo.

 Añadir un Filtro de reporte
 Paso 1: Selecciona un campo en la Lista de Campos. En este ejemplo, escoge Región. Predeterminadamente, éste aparecerá en el grupo de Rótulos de Fila.
 Paso 2: Haz clic izquierdo y arrastra Región a la sección Filtro de Informe. Libera el botón del mouse. La región aparecerá al final del reporte como un filtro.
 Paso 3: La flecha por campo de Región muestra (Todo). Para visualizar únicamente los datos para una región específica, haz clic en la flecha desplegable y selecciona la región. Para ver múltiples regiones, haz clic en la casilla de Múltiples Regiones, selecciona la región a visualizar, y haz clic en Aceptar.

Microsoft Excel

CREANDO FÓRMULAS sin importar la cantidad de fórmulas que vaya a crear, todas deberán seguir las mismas reglas en especial la regla que indica que todas las fórmulas deben empezar con un símbolo igual (=). Considera la siguiente fórmula para la celda A1 ingresada en la barra de fórmulas:

PARTES DE UNA FÓRMULA DE EXCEL Todas las fórmulas de Excel consisten de cualquier de los siguientes elementos:
Constantes o texto. Un ejemplo de una constante es el valor 7. Un texto también puede ser utilizado dentro de una fórmula pero siempre deberá estar encerrado por dobles comillas como “Marzo”.

Operadores. Los operadores utilizados en Excel son los mismos operadores matemáticos que conocemos como el símbolo + para la suma o el símbolo * para la multiplicación.

Funciones de Excel. Dentro de las fórmulas de Excel podemos utilizar funciones de Excel. Un ejemplo de una función de Excel es la función SUMA la cual podemos incluir como parte de una fórmula.

CONSTANTES Y REFERENCIAS DE CELDA Las fórmulas nos permiten utilizar Excel como si fuera una calculadora, solamente debemos introducir la ecuación en la Barra de fórmulas, pulsar la tecla Entrar y Excel calculará el resultado. En el siguiente ejemplo puedes observar un ejemplo de fórmula que utiliza solamente números:

Estos números son constantes, lo que significa que su valor nunca cambiará y la fórmula siempre regresará el mismo resultado mientras tanto y no modifiquemos los números. Sin embargo una de las ventajas más grandes al utilizar fórmulas en Excel es que podemos utilizar referencias de celda dentro de la ecuación.

Referencias de celda en fórmulas La celda A2 tiene el valor 1, la celda B2 el valor 2 y la celda C2 el valor 3. En la celda D2 crearé la siguiente fórmula =A2+B2+C2 y al pulsar la tecla Entrar obtendré el resultado de la operación:

La diferencia entre utilizar contantes y referencias de celda es que el resultado de una fórmula con referencias de celda dependerá del valor de otras celdas. Si el valor de cualquiera de las celdas referenciadas cambia, entonces el resultado de la fórmula también será actualizado. Si actualizo el valor de la celda B2 por 6, tan pronto como pulse la tecla Entrar habrá dos actualizaciones en la hoja de cálculo. En primer lugar se actualizará el valor de la celda B2 y en segundo lugar el resultado de la fórmula de la celda D2 también será actualizado.

USO DE PARÉNTESIS CON OPERADORES ARITMÉTICOS Aun cuando existe un orden predeterminado para los operadores, podemos influir en la precedencia de operadores al utilizar paréntesis los cuales tendrán la preferencia sobre los operadores. Considera la siguiente fórmula:

INSERTAR FUNCIONES
Desplegar el cuadro de diálogo Insertar función
Existen tres maneras de mostrar este cuadro de diálogo. La primera es haciendo clic sobre el botón Insertar función de la ficha Fórmulas. La otra alternativa es utilizar el icono mostrado en la barra de fórmulas el cual se muestra como fx. El tercer método es seleccionar la opción de menú Más funciones que se muestra al pulsar el botón Autosuma.

Seleccionar la categoría de la función de Excel De manera predeterminada se mostrará la categoría de funciones Usadas recientemente. Esto permitirá que hagas una revisión rápida sobre la lista para saber si la función que buscas se encuentra ahí.

De lo contrario, puedes seleccionar la categoría de la función que estás buscando para poder encontrarla rápidamente.
Seleccionar la función Una vez que has encontrado la función que necesitas, debes seleccionarla y hacer clic en el botón Aceptar o también puedes hacer doble clic sobre su nombre y de inmediato Excel mostrará el cuadro de diálogo Argumentos de Función.

Ingresar los argumentos de la función Dentro de este nuevo cuadro de diálogo deberás seleccionar las celdas que contienen cada uno de los argumentos de la función. Una vez que hayas terminado de especificar los argumentos deberás pulsar el botón Aceptar para terminar con la inserción de la función de Excel.

TRUCOS PARA LA AUTOSUMA EN EXCEL Obtener la suma de una columna
Selecciona la celda justo debajo de la columna que deseas sumar:

Oprime la combinación de teclas ALT + = y Excel seleccionará y sumará correctamente los valores numércios de la columna:

Sumar todas las columnas y filas
Antes de mostrarte el siguiente truco debo resaltar un comportamiento extraño de Excel. Tomaré la misma tabla de datos de ejemplo y comenzaré a sumar las filas hasta llegar a la tercera fila en donde Excel realmente no sabrá qué hacer:

Observa cómo despues de haber sumado dos filas y llegar a la tercera fila Excel intenta sumar la columna en lugar de la fila.
El segundo truco que te mostraré ayuda a resolver la confusión de Excel y a sumar rápidamente tanto filas como columnas. Para aplicar el truco debes seleccionar todas las celdas de valores numércios e incluir una fila y una columna adicionales donde se colocará los resultados:

Después de seleccionar las celdas oprime el botón de suma ó el atajo de teclado que acabamos de revisar ALT + = y Excel sumará automáticamente tanto columnas como filas y colocará los resultados en las celdas vacías:

CALCULAR PORCENTAJES Obtener el porcentaje del total en Excel
Utilizaremos la tercera columna para calcular el porcentaje del total de cada mes respecto al total anual utilizando la siguiente fórmula:

Observa cómo el divisor es la celda B14 que corresponde al total anual pero he colocado el símbolo “$” de manera que sea una referencia absoluta para todos los meses y que no se modifique al copiar la fórmula. Al oprimir la tecla Entrar obtengo el resultado:

Para dar formato de porcentaje a la celda oprimiré el comando Estilo porcentual que se encuentra en el grupo Número de la ficha Inicio:

Eso convertirá el valor en estilo porcentual pero de un solo dígito. Ahora dentro del mismo grupo Número haré dos veces clic en el comando Aumentar decimales:

Después de estos cambios de formato el valor de la celda quedará de la siguiente manera:

Finalmente utiliza el autorrelleno para copiar la fórmula hacia abajo y obtendrás el resultado correspondiente para cada mes del año:

TIPOS DE ERRORES
Si sabemos interpretar el error, podremos corregirlo fácilmente. Téngase en cuenta que para deshacerse del error puede ser necesario modificar ya sea la fórmula misma, o bien alguna de las celdas a las que hace referencia la fórmula.
En Excel existen siete resultados de error:
#¡N/A!
#¡REF!
#¡NUM!
#¿NOMBRE?
#¡DIV/0!
#¡VALOR!
#¡NULO!
Veamos que significa cada uno de ellos. De esta manera, podremos depurarlo y corregirlo fácilmente.

#¡N/A! Este error se produce cuando una fórmula de búsqueda o referencia no encuentra ninguna coincidencia exacta en la correspondiente matriz de búsqueda. Significa que el valor buscado no existe en la matriz de búsqueda.

#¡REF! Este tipo de error surge cuando tenemos una referencia de celda inválida en la fórmula. Por ejemplo, en la fórmula: =BUSCARV("mi_string",A2:B8,3,FALSO), obtenemos #¡REF! ya que no podemos buscar en la tercera columna de una matriz que solo tiene dos columnas. En esta otra: =DESREF(Hoja1!A1, -1,0,1,1) también obtenemos #¡REF! ya que no hay ninguna fila encima de la celda A1. Siguiendo con esta fórmula, si eliminamos la primera fila de la hoja "Hoja1", o si eliminamos la Hoja1, la fórmula mostrará #¡REF!, ya que se ha "perdido" la referencia a la celda Hoja1!A1.

#¡NUMERO! Este se produce cuando ingresamos algún valor no numérico como un argumento de función que Excel espera que sea argumento numérico (o una referencia a un valor numérico). Otra posibilidad es ingresar un número inválido, como uno negativo cuando se espera uno positivo, o un 2 cuando el argumento solo admite 0 ó 1. La fórmula =COINCIDIR(123, B1:B10,3) devuelve #NUM!, ya que el último argumento de COINCIDIR solo puede ser -1, 0 ó 1.

#¡NOMBRE! Este error lo obtenemos cuando escribimos mal el nombre de alguna función. También puede surgir cuando utilizamos alguna función personalizada y tenemos deshabilitadas las macros o el complemento correspondiente. Otra situación que dispara este error es el escribir mal el nombre de algún rango nombrado. La fórmula =SUMARSI(A2:A10,"criterio",C2:C10) devuelve #¡NOMBRE! porque la función SUMARSI no existe (más bien está mal escrita. Lo correcto es SUMAR.SI). Finalmente puede suceder también que no utilizamos comillas al ingresar un argumento de texto.

#¡DIV/0! Este es fácil. Se produce al hacer una división por cero, o bién, por una referencia a un cero.

#¡VALOR! Similar a #¡NUMERO!, lo obtenemos cuando el tipo de argumento solicitado por la función, es distinto al ingresado por el usuario. Por ejemplo, al ingresar un argumento lógico cuando la función requiere un rango, o un número cuando la función espera texto.

#¡NULO!
Este es muy poco frecuente. Una fórmula devolverá #¡NULO! cuando la celda de intersección de dos rangos, no existe. En Excel, el operador de intersección es un espacio en blanco. Por tanto, la fórmula =A2:D2 J1:J10, devuelve #¡NULO! ya que los rangos A2:D2 y J1:J10 no se intersectan en ningún punto. En cambio, =A2:D2 C1:C10 devuelve C2, celda común a ambos rangos. A menudo sucede que una celda de error está correctamente escrita pero, al hacer referencia a un resultado de error, refleja este resultado. Para saber cuál es la celda exacta que está generando el error, podemos ejecutar (previa selección de la celda con error) Herramientas - Auditoría de fórmulas - Rastrear error. Excel señalará con una línea roja la celda que está produciendo el error. Otro error común es cuando la celda aparece llena de símbolos #. Esto se debe a que la celda no es lo suficientemente ancha para mostrar el resultado o bien, cuando contiene una fecha inválida.