PDF de programación - Solución caso cálculo de gastos de viaje

SOLUCIÓN CASO CÁLCULO DE GASTOS DE VIAJE



La cumplimentación del parte de dietas requerirá de la introducción de algunos datos
como: el nombre del empleado, la fecha de inicio del viaje, la fecha de finalización, la categoría
profesional, el medio de transporte utilizado, el número de Kms. recorridos si el vehículo es el
automóvil o el importe de los billetes si se trata de un transporte público. Una vez introducidos
dichos datos se procederá a realizar los cálculos necesarios para hallar el importe total de la dieta.

Para desarrollar el caso, empezamos por desarrollar un esquema de la hoja de acuerdo a

las necesidades del caso. La estructura podría contener tres zonas:

•
•

•

una zona de entrada de datos,
una zona con las tablas: una que contenga las asignaciones por conceptos, y otra con
los descuentos (importe de las dietas que se incorpora a la nómina), y
la zona de cálculos

hay que tener en cuenta que las dos últimas zonas no deberán ser alteradas en ningún
caso por el usuario de la hoja de cálculo a la hora de elaborar los partes de dietas. El modelo
podría quedar como se muestra en la tabla siguiente:



Las celdas de



introducción de datos podemos sombrearlas para facilitar su
reconocimiento, el resto de las celdas, o bien contienen datos que no deben alterarse o bien
fórmulas que se calcularán automáticamente para llegar al importe final de la dieta.

Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO GASTOS DE VIAJE - 1



Así, pasamos a analizar el contenido de las celdas que no están sombreadas. Podemos ver
que en la zona de introducción de datos aparece una única celda que no está sombreada, se trata de
la celda B5.

En dicha celda queremos introducir la fecha en que se va a completar el parte de dietas. En
esta celda podríamos teclear la fecha al igual que haremos en las correspondientes al inicio y final
del viaje, sin embargo, para ahorrar tiempo y reducir riesgos de errores podemos utilizar una
función de Excel que proporciona la fecha actual, la función HOY.



La función HOY

Esta función devuelve la fecha del día de hoy. No tiene ningún argumento:

=HOY()


De todas formas, la función devuelve la fecha que marca el ordenador, de
manera que si el ordenador tiene mal definida la fecha, la función devolverá
un valor incorrecto.



Además, la celda A12 contendrá una condicional para introducir un texto u otro en función

de que el viaje se haya realizado en coche propio o en un transporte público:



Por lo que se refiere a las tablas se introducirán todos los datos y, dado que se trata de
datos que no van a variar habitualmente, habrá que impedir que el usuario modifique estas celdas, a
través de los sistemas de protección de celdas y de hojas que comentaremos más adelante.

La tabla de asignaciones por conceptos la copiaremos directamente del enunciado. En
cuanto a la tabla de descuentos habrá que calcularla mediante sencillas fórmulas de modo que se
recalcule ante cualquier cambio en los importes de la tabla anterior:

Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO GASTOS DE VIAJE - 2







Pasamos ahora a definir las fórmulas a emplear en la zona de cálculo. Muchas de las
fórmulas incorporan funciones condicionales que aseguren que los cálculos sólo se realicen cuando
las celdas de datos no estén en blanco, dado que los mecanismos de validación que se introducirán
en dichas celdas, no impiden que la ausencia de datos origine errores en otras celdas que contengan
una fórmula asociada. Las celdas B15 a B23 quedarán:



Como podemos ver, en algunas celdas aparece una función que todavía no conocemos, la

función BUSCARV, cuyo funcionamiento vamos a explicar a continuación.



Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO GASTOS DE VIAJE - 3



La función BUSCARV

Esta función localiza el valor buscado en la primera columna de una tabla o
matriz de búsqueda, se posiciona en el valor buscado y de esa fila devuelve
el valor de la columna indicada en indicador-columna. Una de las
condiciones de esta función es que la primera columna de la matriz esté
ordenada de menor a mayor, de lo contrario, a pesar de que Excel no dará un
mensaje de error, el valor encontrado puede no ser correcto:

=BUSCARV(valor-buscado;matriz-buscar-en;indicador-columna;ordenado)


• valor-buscado: es el valor que se va a comparar con la primera columna
de la tabla de datos. Puede ser un texto, un dato numérico o una
referencia a una celda que contiene un valor.

• matriz-buscar-en: es la tabla que contiene los datos organizados por

•

columnas.
indicador-columna: es el número de la columna de la matriz de datos
donde se encuentra el dato que se quiere devolver. Este número estará
entre 1 y el número de columnas de la matriz.

• Ordenado: es un argumento opcional, puede tomar los valores
VERDADERO o FALSO. Si el valor es VERDADERO o se omite,
Excel buscará un valor igual (si existe) o el menor más próximo al valor-
buscado. Si el valor es FALSO la búsqueda es exacta, es decir, si no
encuentra en la primera columna el valor-buscado, se producirá un
mensaje de error del tipo #N/A.



Existe otra función similar, la función BUSCARH, que debe ser utilizada en
caso de que la información esté organizada por filas y no por columnas. El
funcionamiento y los argumentos son los mismos que para BUSCARV.



Así, en la celda B16 queremos obtener el importe del alojamiento diario y multiplicarlo por
el número de días de duración del viaje. Lo primero que hay que averiguar es el importe diario del
alojamiento, dato que está contenido en la tabla de asignaciones que hemos copiado en la hoja de
cálculo. Para determinarlo, realizaremos una “búsqueda inteligente” a través de la función que
acabamos de explicar: BUSCARV.

Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO GASTOS DE VIAJE - 4



La función básica de búsqueda que se debe utilizar en la celda B16 será:

=BUSCARV(B10;D6:G9;2)

donde B10 es la celda en la que el usuario introducirá su categoría profesional, que será el
valor que queremos buscar en la matriz representada por el segundo argumento; una vez localizado
este valor, la función localizará dentro de la misma fila el valor correspondiente en la columna 2ª,
tal como indica el tercer argumento de la función.

En cualquier caso, hemos visto que la fórmula no aparece así exactamente, sino que la

matriz viene referenciada por un nombre y no por las celdas que la conforman.

=BUSCARV(B10;Dietas;2)

En Excel se pueden definir nombres para identificar una celda o un rango de celdas, esto se
consigue a través de la opción Insertar/Nombre/Definir, con lo que aparece una ventana en la que
podemos definir el nombre. En nuestro caso seleccionamos el rango D5:G9 y escribimos:

(En Excel 2007 se accede desde la FICHA: Fórmulas / GRUPO: Nombres definidos /

OPCIÓN: Asignar nombre a un rango y elegimos Definir nombre)

Del mismo modo, la tabla de descuentos (rango D13:E17) la podemos nombrar “nómina”.



En cualquier caso, la celda B16 deberemos completarla, ya que la función definida solo
nos proporcionará el importe diario del alojamiento, y este deberá multiplicarse por la duración del
viaje que habrá sido calculada en la celda B15.

Las celdas B17 y B18 se resolverán de forma similar.

Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO GASTOS DE VIAJE - 5



La celda B21 contiene una función condicional para determinar el posible importe que
habrá que llevar a la nómina en caso de que el viaje se haya realizado en automóvil y el importe del
kilometraje exceda el límite libre de impuestos.

Validación de los datos de entrada.

Vamos a ver cómo introducir criterios de validación en las celdas de entrada de datos, de
manera que el programa detecte si se intenta introducir un dato erróneo, mostrando en ese caso un
mensaje de error.

La primera celda de introducción de datos -B4- contiene el nombre del empleado por lo

que en principio no podemos introducir ninguna limitación.

Celda B7. Es la celda que contiene la fecha de inicio del viaje. En ella el usuario tendrá

que introducir un dato que cumpla las siguientes condiciones:

• Ser fecha

• Ser menor o igual que la fecha de finalización del viaje, es decir, que la fecha contenida

en la celda B8.

Por tanto, vamos a utilizar los mecanismos de validación para que se cumplan las
anteriores condiciones. Para ello, situamos el cursor en la celda que deseamos validar, en nuestro
caso, B7. A continuación abrimos el menú Datos/Validación con lo que aparecerá la siguiente
pantalla:
(En Excel 2007 se accede desde la FICHA: Datos/ GRUPO: Herramientas de datos/ OPCIÓN:
Validación de datos)

Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO GASTOS DE VIAJE - 6



La primera ficha que aparece en la pantalla, llamada Configuración, permite definir los
criterios de validación. El primer menú desplegable que aparece bajo la palabra Permitir indica el
tipo de dato que puede ser insertado en la celda. Entre los diferentes tipos disponibles que aparecen
elegimos Fecha y pasamos a la siguiente ventana desplegable, la opción Datos, en la que
elegiremos menor o igual que y teclearemos como fecha final la contenida en la celda B8.

La casilla Omitir blancos ofrece la opción de permitir entradas en blanco en la celda. Si

no se activa, las entradas en blanco no serán válidas.

Así, la pantalla quedará:

En la ficha Mensaje entrante tenemos la o