Hacer factura con macros

Video1
Video2
Video3
Video4
Video5
Video6
Video7
Video8

Buscar objetivo

Esta herramienta te ayudará a hacer muchas pruebas de valores en una fórmula hasta encontrar el valor exacto que cumpla con las condiciones establecidas. Es decir, si sabemos el resultado de una fórmula, pero no estamos seguros del valor de uno de sus argumentos, entonces Buscar objetivo nos ayudará a probar diversos escenarios hasta encontrar el valor exacto que necesitamos. Por ejemplo, supongamos que tenemos el siguiente problema.

Tengo un número que multiplicado por otro número X me deberá dar el resultado mostrado. Aunque este es un problema muy fácil de resolver matemáticamente será de utilidad para mostrar la funcionalidad de Buscar objetivo. Empezaré por definir una fórmula importante. En la celda C3 colocaré la fórmula que se deberá cumplir para obtener el resultado deseado, es decir, =B1*B2 deberá dar como resultado 439482.

Por ahora el resultado en C3 será cero, porque no hay valor en la celda B2 sin embargo, al momento de ir buscando el valor adecuado para B2, el valor de la celda C3 llegará a ser el mismo que B3 cuando se haya encontrado el valor correcto.

El comando Buscar objetivo

Ahora deberás hacer clic en la ficha Datos y seleccionar Análisis Y si donde se desplegará un menú de opciones y deberás elegir Buscar objetivo.

De inmediato se mostrará el cuadro de diálogo Buscar objetivo donde deberás llenar los siguientes cuadros de texto.

• Definir la celda. Es la celda que contiene la fórmula
• Con el valor. Este es el número que deseamos como resultado de la fórmula.
• Para cambiar la celda. La celda que será modificada hasta tener el resultado deseado.

Una vez que se han establecido los parámetros debes hacer clic en el botón Aceptar y Excel comenzará a realzar los cálculos necesarios. Cuando haya encontrado un resultado adecuado se mostrará el cuadro de dialogo Estado de la búsqueda de objetivo.

Este cuadro de diálogo simplemente notifica que se ha llegado al objetivo modificando la celda especificada. Al cerrar el cuadro de diálogo podrás observar el resultado. Para nuestro ejemplo Excel fue haciendo diversas pruebas hasta llegar al resultado de 520.0970 que es el número que  multiplicado por 845 nos dará el resultado esperado.

Función INDIRECTO

Descripción de la función INDIRECTO en Excel

La función INDIRECTO en Excel sirve para que, a partir de un texto que sea una referencia a una celda como A2, extraiga el valor que hay en la celda A2. Por ejemplo: la celda A1 tiene el valor numérico 10, la celda A2 tiene el texto A1 (referencia), al aplicar en la celda A3 la función =INDIRECTO(A2), el resultado es 10, porque la celda A2 hace referencia a la celda A1 que tiene el valor numérico 10.

Sintaxis

INDIRECTO(ref, [a1])

• ref: Es un argumento obligatorio y es la referencia a una celda que contiene una referencia de tipo A1 o F1C1, un nombre definido como referencia o una referencia a una celda como cadena de texto. Si ref no es una referencia de celda válida, INDIRECTO devuelve el valor de error #¡REF!.
• a1: Es un argumento opcional y es un valor lógico que especifica el tipo de referencia que contiene la celda ref:
  • Si a1 es VERDADERO o se omite, ref se interpreta como una referencia estilo A1.
  • Si a1 es FALSO o se omite, ref se interpreta como una referencia estilo F1C1.

Anotaciones

• Si ref hace referencia a otro libro (una referencia externa), el otro libro debe estar abierto.
• Si el libro de origen no está abierto, INDIRECTO devolverá el valor de error #¡REF!
• Si ref hace referencia a un rango de celdas fuera del límite de filas de 1.048.576 o del límite de columnas de 16.384 (XFD), INDIRECTO devolverá el error #¡REF!.
• Este comportamiento es diferente en las versiones de Excel anteriores a Microsoft Office Excel 2007, que no tienen en cuenta el límite excedido y devuelven un valor.
• Cuando cree una fórmula que haga referencia a una celda, la referencia a la celda se actualizará si:
  • La celda se desplaza utilizando el comando Cortar para eliminarla, o
  • Si la celda se desplaza porque se insertan o eliminan filas o columnas. Si desea que la fórmula siempre haga referencia a la misma fórmula sin tener en cuenta si se elimina o desplaza la fila sobre la celda, utilice la función de hoja de cálculo INDIRECTO. Por ejemplo, si desea que siempre se haga referencia a la celda A10, utilice la sintaxis siguiente:

=INDIRECTO(“A10”)

En la columna A tenemos representados los SUELDOS, en la columna B las COMISIONES y en la columna C el SUELDO TOTAL que es la suma de los SUELDOS+COMISIONES. Estos son los datos que vamos a usar para explicar cómo se comporta la función INDIRECTO en Excel.

En la columna E tenemos la REFERENCIA que vamos a usar como argumento en la función INDIRECTO.

En la columna F llamada FUNCIÓN INDIRECTO está escrita la función que empleamos en la columna G, RESULTADO FUNCIÓN INDIRECTO.

Vamos a ver cada ejemplo:

• En la celda G2 estamos aplicando la función:

=INDIRECTO(E2)

Lo que nos indica que la función INDIRECTO va a tomar la referencia que esté en la columna E2, que este casi es A2, y va a extraer su valor. El valor que contiene la celda A2 es 4,500 por lo que ese es el resultado.

• En la celda G3 aplicamos:

=INDIRECTO(E3)

Es decir, estamos indicándolo que nos de el valor a la que haga referencia la celda B3, en este caso hace referencia al valor de la celda B3 que es 450.

• Para finalizar en la celda G4 aplicamos:

=INDIRECTO(E4)

El argumento hace referencia a la celda E4, esta a su vez hace referencia a la celda C4, y el valor que tiene la celda C4 es 13,200.

Función DESREV

Caso 1

En este caso necesitas saber las ventas de la Sucursal Oeste del año 2010, para saber puedes usar varias formas; en este tutorial vamos a usar la función DESREF en Excel de la siguiente manera:

=DESREF(A4,0,2)   --->  Resultado $7,500

En este ejemplo le estamos indicando a la función DESREF que parta de la celda A4 que contiene el texto “Sucursal Oeste” y que de ahí se desplace cero filas, es decir sobre la misma fila 4. En número de filas que se va a desplazar son dos. Por lo tanto la referencia resultante es C4 con un valor de $7,500, total de las ventas en el año 2010.

Caso 2

Aquí queremos obtener las ventas de la Sucursal Sur del año 2009. En este ejemplo vamos a partir de la referencia D6 que tiene el total de ventas de todas las sucursales durante el año 2011. El objetivo es llegar a obtener el valor de $8,200 que está en la referencia B3. Aplicamos la siguiente fórmula:

=DESREF(D6,-3,-2)   --->   Resultado $8,200

Partiendo de la celda D6 debemos desplazarnos 3 filas hacia arriba o lo que es igual a -3. Sino indicamos que el valor es negativo, la función DESREF buscará el valor hacia abajo y nos posicionaría en la fila 9. Después le indicamos que se desplace 2 columnas hacia la izquierda con el valor -2, porque de lo contrario se iría hasta la columna F. El resultado de la función DESREF es $8,200.

Caso 3

Ahora necesitamos saber el total de ventas del año 2009, es decir la sumatoria que previamente hicimos que está en la celda B6, para esto partimos de la celda B1 con la siguiente fórmula:

=DESREF(B1,5,0)   --->   Resultado $37,000

La celda B1 tiene como valor el texto “2009”, entonces de ahí nos desplazamos 5 filas hacia abajo. El número de columnas es cero porque estamos en el año que necesitamos.

El resultado nos lleva a la referencia B6 que tiene como valor $37,000.

Caso 4

Necesitas saber el total de todas las ventas de todos los años de todas las sucursales, pero esta es una cifra que no has calculado previamente en la tabla. ¿Cómo puedes sacar la suma total con la función DESREF en Excel? Vamos a usar dos argumentos más de la función que son alto y ancho de la siguiente manera:

=SUMA(DESREF(D2,0,-2,4,3))   --->   Resultado $129,200

La función parte de la esquina superior derecha que hace referencia a la celda D2. Ese es nuestro punto de partida. Ahora, el valor de esa referencia tiene que estar dentro de la suma por lo que debemos indicarle a la función que no se desplace en el eje de las filas, por lo tanto el valor del argumento es cero. Después tenemos que indicarle las columnas que desplazarse, aquí van a ser dos a la izquierda  es decir -2.

Por último para que la función incluya todos los valores que necesitamos sumar, necesitamos indicarle el alto y ancho de nuestra selección. El alto debe ser de 4, mientras que el ancho de 3. Con esto ya tenemos todos los valores.

Para finalizar tenemos que indicarle que sume todos estos valores, por lo que anteponemos la función SUMA. El resultado es $129,200.