Caja Registradora en Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Deseamos realizar una plantilla / simulador de TPV (Terminal Punto de Venta) o de caja registradora en Excel.

Al insertar el precio a pagar y el importe entregado por el cliente, nos indica el importe a devolver y el desglose de billetes y monedas que deberemos de entregar al cliente.
Simulador de caja registradora en Excel: Devolver 14,75 €. 1 billete de 10, 2 monedas de 2€, 1 de 50 cts, 1 de 20 cts y 1 de 5 cts.

Emplearemos las siguientes funciones, anidándolas para el simulador de Caja Registradora:

  • Función ABS. Devuelve el valor absoluto de un número.

  • Función REDONDEAR. Redondea un número a un número especificado de dígitos.

  • Función RESTO. Devuelve el resto de la división.

  • Función SI. Especifica un texto lógico para ejecutar.

  • ENTRADA DE DATOS. Para el simulador de Caja Registradora.
  1. Ir a: Celda F2 / Escribir: 35,25. O la cantidad a pagar deseada.

  2. Ir a: Celda F3 / Escribir: 50,00. O la cantidad entregada por el cliente para el pago.

  3. Ir a: Celda F5 / Escribir:

    =F3-F2

    Como la cantidad para devolver al cliente.

    Simulador de caja registradora en Excel: Celda F5 con fórmula =F3-F2 (50 € - 35,25 € = 14,75 €).
  • DATOS DE REFERENCIA. Aplicamos los cálculos y referencias necesarios en el rango P1:AE4, posteriormente se ocultará.
  1. Ir a: Celda P1 / Escribir:

    =ABS(F5)

    Como el valor a devolver sin símbolo

  2. Ir a: Celda Q1 / Escribir:

    =REDONDEAR(RESTO(P1;Q3);2)

    Como el resto de la división del importe a devolver entre el primer tipo de billete (más elevado) y redondeando el resultado a dos decimales.

  3. Copiar la celda Q1.

  4. Seleccionar el rango R1:AE1 / Pegar como fórmula.

  5. MONEDAS Y BILLETES. Escribir en el rango Q3:AE3, los valores de billetes y monedas existentes:

     QRSTUVWXYZAAABACADAE
    35002001005020105210,500,200,100,050,020,01
  6. Ir a: Celda Q4 / Escribir:

    =ENTERO((P1)/Q3)

    Como la cantidad a devolver

  7. Copiar la celda Q4.

  8. Seleccionar el rango Q4:AE4 / Pegar como fórmula.

    Como la cantidad a devolver entre el primer tipo de billete (más elevado), redondeando el resultado sin decimales.

  • SIMULADOR BILLETES: CÁLCULOS. Muestra el numero de billetes de cada tipo que tendremos que devolver para la caja registradora.
  1. Ir a: Celda C8 / Escribir:

    =SI(Q4=0;"";Q4)

    Para mostrar la celda vacía o la cantidad a devolver.

  2. Copiar la celda C8.

  3. Seleccionar el rango D8:I8 / Pegar como fórmula.

  • SIMULADOR MONEDAS: CÁLCULOS.Muestra el numero de monedas de cada tipo que tendremos que devolver.
  1. Ir a: Celda C13 / Escribir:

    =SI(X4=0;"";X4)

    Para mostrar la celda vacía o la cantidad a devolver.

  2. Copiar la celda C13.

  3. Seleccionar el rango C13:J13 / Pegar como fórmula.

  • SIMULADOR BILLETES: FORMATO CONDICIONAL. Aplicamos formato condicional a los billetes para que remarque la celda con el valor a devolver.
  1. Seleccionar el rango C8:I8. Rango donde aplicar el formato condicional.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva regla. Mostrará cuadro de diálogo de nueva regla.

    Simulador de caja registradora en : Cuadro de diálogo Editar regla de formato, con fórmula =Q4>=1.
  3. Ir a: Seleccionar un tipo de regla / Clic sobre: Utilice una fórmula que dExceletermine las celdas para aplicar formato. Tipo de regla deseado.

  4. Ir a: Dar formato a los valores donde esta fórmula sea verdadera / Escribir:

    =Q4>=1

    Escribir Q4 como referencia relativa (sin signo de dólar).

  5. Pulsar el botón: Formato. Mostrará el cuadro de diálogo de formato de celdas.

  6. Ir a: Pestaña Relleno / Clic sobre color amarillo. O el color deseado para el fondo de la celda.

  7. Ir a: Pestaña Bordes / Clic sobre botón Contorno. O el estilo y color deseado para la celda.

  8. Pulsar el botón Aceptar. Cierra la ventana de formato de celdas.

  9. Pulsar el botón Aceptar. Cierra la ventana de nueva regla de formato.

  • SIMULADOR MONEDAS: FORMATO CONDICIONAL. Aplicamos formato condicional a las monedas para que remarque la celda con el valor a devolver.
  1. Seleccionar el rango C13:J13. Rango donde aplicar el formato condicional.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva regla. Mostrará cuadro de diálogo de nueva regla.

  3. Ir a: Seleccionar un tipo de regla / Clic sobre: Utilice una fórmula que determine las celdas para aplicar formato. Tipo de regla deseado.

  4. Ir a: Dar formato a los valores donde esta fórmula sea verdadera / Escribir:

    =X4>=1

    Escribir X4 como referencia relativa (sin signo de dólar).

  5. Pulsar el botón: Formato. Mostrará el cuadro de diálogo de formato de celdas.

  6. Ir a: Pestaña Relleno / Clic sobre color amarillo. O el color deseado para el fondo de la celda.

  7. Ir a: Pestaña Bordes / Clic sobre botón Contorno. O el estilo y color deseado para la celda.

  8. Pulsar el botón Aceptar. Cierra la ventana de formato de celdas.

  9. Pulsar el botón Aceptar. Cierra la ventana de nueva regla de formato.

  • FORMATOS. Aplicamos el formato deseado a la hoja.
  1. OCULTAR COLUMNAS P:AE. Evitando errores de manipulación.

  2. COMBINAR CELDAS. Unir las celdas deseadas.

  3. OCULTAR LÍNEAS DE CUADRÍCULA. Para dar apariencia de formulario.

  4. FORMATO DE NÚMEROS. Aplicamos el número de decimales deseado.

  5. RÓTULOS. Escribimos los encabezados de los datos.

  6. BORDES Y RELLENOS. Aplicamos bordes y rellenos a las celdas deseadas.

  7. Podemos mejorar la apariencia, ocultando los valores de error si no existen datos en F2 o F3.

    – Celda F5: =SI(O(F2="";F3="");"";F3-F2).

    – Celda P1: =SI(F5="";"";ABS(F5)).

    – Celda Q1: =SI($P$1="";"";REDONDEAR(RESTO(P1;Q3);2)); Copiar y pegar en Q1:AE1.

    – Celda Q4: =SI(P1="";"";ENTERO((P1)/Q3)); Copiar y pegar en Q4:AE4.

    – Celda C8: =SI(O(Q4="";Q4=0);"";Q4). Copiar y pegar en D8:I8.

    – Celda C13: =SI(O(X4="";X4=0);"";X4). Copiar y pegar en C13:J13.

    – Formato Condicional Billetes: =Y(Q4>=1;Q4<>"").

    – Formato Condicional Monedas: =Y(X4>=1;X4<>"").

Así podemos crear un plantilla o simulador de Caja Registradora en Excel.

 

Nivel de dificultad: Avanzado Excel Avanzado

 

10 comentarios en “Caja Registradora en Excel

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *