Sistemas de información II AUTOMATIZACION DE PROCESOS CON EXCEL Factura: FUNCIONES Y RANGOS DINÁMICOS En esta sección se creará una factura comercial para registrar las ventas de productos a nuestros clientes. Antes de iniciar a realizar la factura debemos conocer algunos conceptos y cómo crearlos. NOMBRES DE RANGOS DINAMICOS Al momento de trabajar en Excel nos encontramos que una tabla de datos cambia de tamaño continuamente, lo que nos obliga a redefinir rangos de datos, para evitar este problema, se crea un nombre de Rango dinámico el cual tomará el tamaño necesario de forma automática. A continuación se expone un método para crear un rango dinámico. Función DESREF La función DESREF es una función que puede emplearse de dos maneras: 1. Utilizando los 3 primeros argumentos permite extraer un valor de una celda que se encuentre en una tabla. Para ello debemos indicar desde que celda debemos comenzar a contar (punto de partida), indicando el número de filas hacia abajo que nos hemos de desplazar, y el número de columnas a la derecha que nos debemos mover. Esta forma de emplear DESREF no es matricial. 2. Utilizando los 5 argumentos de la función. En este caso la función es matricial y permite extraer un bloque de celdas. Se ha de indicar el punto de partida, la celda de la esquina superior izquierda y las dimensiones del bloque. La sintaxis de DESREF en su versión matricial es: =DESREF(ref;filas;columnas;alto;ancho) Donde Argumento ref filas columnas alto ancho Descripción Es la celda desde la que partimos. Es el punto de partida Es el número de filas hacia abajo que nos hemos de desplazar para llegar a la celda que marca la esquina superior izquierda del rango que deseamos extraer. Si las filas son negativas nos movemos hacia arriba, siempre tomando como referencia nuestro punto de partida Es el número de columnas a la derecha que nos hemos de desplazar para llegar a la celda que marca la esquina superior izquierda del rango que deseamos extraer. Si las columnas son negativas indica que nos movemos a la izquierda de nuestro punto de partida Es el número de filas que contiene el rango que deseamos extraer Es el número de columnas que contiene el rango que deseamos extraer Pág. 18 Sistemas de información II AUTOMATIZACION DE PROCESOS CON EXCEL Si queremos utilizar esta fórmula debemos considerar que se trata de una fórmula matricial y que por tanto se han de dar los tres pasos que siempre requieren este tipo de fórmulas: 1. Primero debemos seleccionar el rango donde la fórmula matricial dejará su resultado. Esto es importante, ya que muchas fórmula matriciales no dejan su resultado un una sola celda, sino en un rango de celdas. Este primer paso indica que este rango debemos seleccionarle. 2. Escribimos la fórmula matricial de que se trate. En este caso sería DESREF si queremos probar con ella. 3. Para validar la fórmula no pulse ENTER. Se deben pulsar simultáneamente las tres teclas siguientes: CONTROL + MAYUSCULAS + ENTER Función CONTARA Para contar existen dos funciones:  CONTAR: cuenta las celdas numéricas del rango que indiquemos  CONTARA: Cuenta todo, números y texto Aplicadas estas funciones sobre la columna E, donde se encuentra la facturación los resultados obtenidos son:   =CONTAR(E:E) da como resultado 20, que son los registros de la tabla =CONTARA(E:E) da como resultado 21, que son los registros numéricos más la cabecera Pág. 19 Sistemas de información II AUTOMATIZACION DE PROCESOS CON EXCEL Función buscarv() La función BUSCARV en Excel nos permite buscar un valor dentro de un rango de datos, es decir, nos ayuda a obtener el valor de una tabla que coincide con el valor que estamos buscando. La función BUSCARV tiene 4 argumentos:     Valor_buscado (obligatorio): Este es el valor que se va a buscar en la primera columna de la tabla. Podemos colocar el texto encerrado en comillas o podemos colocar la referencia a una celda que contenga el valor buscado. Excel no hará diferencia entre mayúsculas y minúsculas. Matriz_buscar_en (obligatorio): La tabla de búsqueda que contiene todos los datos donde se tratará de encontrar la coincidencia del Valor_buscado. Indicador_columnas (obligatorio): Una vez que la función BUSCARV encuentre una coincidencia del Valor_buscadonos devolverá como resultado la columna que indiquemos en este argumento. El Indicador_columnas es el número de columna que deseamos obtener siendo la primera columna de la tabla la columna número 1. Ordenado (opcional): Este argumento debe ser un valor lógico, es decir, puede ser falso o verdadero. Con este argumento indicamos si la función BUSCARV realizará una búsqueda exacta (FALSO) o una búsqueda aproximada (VERDADERO). En caso de que se omita este argumento o que especifiquemos una búsqueda aproximada se recomienda que la primera columna de la tabla de búsqueda esté ordenada de manera ascendente para obtener los mejores resultados. Pág. 20 Sistemas de información II AUTOMATIZACION DE PROCESOS CON EXCEL CREACION DE RANGOS Se creará un rango dinámico llamado “LISTADO_DESCUENTOS” que incluirá todos los datos que se encuentran en la columna “D” de la hoja “Datos” Inicialmente el rango será D3:D6 ya que la tabla inicialmente tiene 5 registros. Pero al indicar el rango que se asocia a ese nombre de rango lo haremos con fórmula para que sea automático y se ajuste a la longitud de la columna D que exista en cada momento. La fórmula para el nombre de rango LISTADO_DESCUENTO es: =DESREF(DATOS!$D$2,1,0,CONTARA(DATOS!$D:$D)-1,1) ASIGNANDO UN NOMBRE AL RANGO Excel nos permite poner nombres a los rangos de celdas de manera que los podamos identificar adecuadamente al usarlos en nuestras fórmulas. Seleccionamos la pestaña de “FORMULAS” y la opción de “Administrador de nombres” Pág. 21 Sistemas de información II AUTOMATIZACION DE PROCESOS CON EXCEL En el cuadro que aparece de “Administrador de Nombres” damos un clic al botón nuevo y nos muestra otro recuadro para ingresar la información. Ingresamos:  Nombre: LISTADO_DESCUENTO  Ámbito: Libro, para que nuestro nombre sea reconocido dentro de todo el libro de Excel  Comentarios Hace referencia a: =DESREF(DATOS!$D$2,1,0,CONTARA(DATOS!$D:$D)-1,1) Una vez realizado esto nos aparece dentro del listado Realizaremos el anterior procedimiento para:  LISTADO DE DESCUENTOS  LISTADO DE FORMAS DE PAGO  OTROS Pág. 22