1. INTRODUCCIÓN
Partiremos de la hoja de cálculo adjunta: 05-RangosYNombres.xlsx. Definiremos varios rangos con nombres y crearemos una nueva pestaña para la realización de una serie de cálculos.
La hoja, inicialmente, tiene el siguiente aspecto:
• Se trata de una tabla de movimientos de caja con los siguientes datos: fecha del movimiento, tipo (C=Compra o V=Venta), importe base, iva e importe total del movimiento.
2. EJERCICIOS GUIADOS
Paso 1) Crearemos un rango con nombre “Movimientos_Fecha” con las fechas de la tabla.
• Seleccionaremos todos los valores de fecha situándonos en el primero B4 y utilizando el atajo Ctrl+Mayusc+Abajo.
• Con el botón derecho del ratón seleccionaremos “Definir nombre…”:
EJERCICIO DE RANGOS Y NOMBRES
• Indicaremos el nombre “Movimientos_Fecha”:
• El rango para el nombre vendrá ya configurado al haber seleccionado las celdas.
Paso 2) Para crear el siguiente Rango con nombre (Movimientos_Tipo) utilizaremos una forma diferente. Pero solo para mostrar este otro mecanismos, el resultado será realmente el mismo.
• Seleccionaremos las celdas involucradas, desde C4 a C500.
• Y utilizaremos la opción “Asignar nombre” de la categoría “Fórmulas”:
EJERCICIO DE RANGOS Y NOMBRES
• Se indicará el nombre como en el caso anterior:
Paso 3) Igualmente crearemos, con uno u otro mecanismos, los siguientes rangos con nombres:
• Movimientos_Base para el importe base.
• Movimientos_IVA para el importe del IVA.
• Movimientos_Total para el importe total.
Paso 4) Crearemos una pestaña adicional para los cálculos. La llamaremos “Cálculos”. El siguiente trabajo lo haremos en ella.
Paso 5) En primer lugar calcularemos los totales generales.
• Crearemos una tabla para los valores a calcular: Base total compras, Base total ventas, Saldo total, IVA total compras, IVA total ventas y Saldo IVA.
• Dejaremos preparadas las celdas para los valores:
Paso 6) Calcularemos la base total de las compras.
• En la celda C3 utilizaremos la función SUMA.SI.
• Tenemos que sumar el importe base de los movimientos que sean de tipo compra (C).
• La fórmula será: =SUMAR.SI(Movimientos_Tipo;»C»;Movimientos_Base)
• Aplicaremos formato de moneda:
EJERCICIO DE RANGOS Y NOMBRES
Paso 7) De forma similar calcularemos el resto de totales:
• Copiaremos la fórmula en las celdas D3, C4 y D4.
• Modificaremos la columna D (D3 y D4) para que el tipo seleccionado sea el de ventas “V”
• Modificaremos la fila 4 (C4 y D4) para que sume el ranto de IVA y no el de base.
Paso 8) Para el cálculo de saldo bastará con restar las compras a las ventas.
• En E3 tendremos D3-C3
• En E4 tendremos D4-C4
Paso 9) Crearemos una tabla para hacer cálculos mensuales.
• En la primera columna, de nombre “Mes”, se indicará el primer día de cada mes:
1/1/2018, 1/2/2018, etc.
• Crearemos columnas adicionales para el total de compras, ventas y saldo de dicho mes.
• El aspecto final será:
Paso 10) Modificaremos la primera columna para que solo muestre el mes. Aunque el valor seguirá siendo la fecha completa.
• Seleccionamos los valores de fecha.
EJERCICIO DE RANGOS Y NOMBRES
• Modificaremos el formato utilizando un formato personalizado del tipo “mm/aaaa”.
• Quedará:
Paso 11) Calcularemos las compras del mes de enero de 2018.
• Tendremos que sumar los movimientos que cumplan varias condiciones:
◦ El tipo debe ser compra, “C”.
◦ La fecha del movimiento debe ser igual o superior a la de la fila, “01/01/2018”.
◦ La fecha del movimiento debe ser inferior a la del mes siguiente, “01/02/2018” (calculado con la función FECHA.MES).
• Para aplicar varios filtros utilizaremos la función SUMAR.SI.CONJUNTO.
◦ El rango de suma es Movimientos_Total.
◦ El primer filtro será sobre el rango Movimientos_Tipo y debe ser “C”.
◦ El segundo filtro será sobre el rango Movimientos_Fecha y debe ser el texto “>=01/01/2018”. Esto lo haremos con CONCATENAR(“>=”;G3).
◦ El tercer filtro será también sobre el rango Movimientos_Fecha y debe ser el texto “<01/02/2018”. Esto lo haremos con CONCATENAR(“<”;FECHA.MES(G3;1)).
◦ La fórmula final será: = SUMAR.SI.CONJUNTO( Movimientos_Total;
Movimientos_Tipo; «C»; Movimientos_Fecha; CONCATENAR(«>=»;G3);
Movimientos_Fecha; CONCATENAR(«<«;FECHA.MES(G3;1)))
Paso 12) Para poder arrastrar o copiar la fórmula modificaremos la referencias a la fecha del mes para que varíe al ir hacia abajo pero no al ir hacia la derecha. Donde tenemos G3 indicaremos $G3 (hemos fijado la columna).
Paso 13) Copiaremos la fórmula en todo el rango H3:I14.
• La referencia de cada fila a su mes correspondiente será correcta.
• Pero habrá que modificar las fórmulas de la columna I para que en lugar de utilizar el tipo compra “C”, utilicen el tipo venta “V”.
Paso 14) Finalmente calcularemos el saldo mensual como diferencia de ventas y compras.
• El aspecto final será:
EJERCICIO DE RANGOS Y NOMBRES
3. PRÁCTICA
Paso 15) Añade a la tabla generada una nueva columna para el número de movimientos del mes.
• Calcula los movimientos con la función CONTAR.SI.CONJUNTO teniendo en cuenta que no nos importará el tipo de movimiento. Los contaremos todos.
Paso 16) Utilizando el número de movimientos del mes añade una nueva columna con el saldo medio por movimiento.