Crear fórmula en excel para calculo de tarifas

Si lo que quieres es crear un función personalizada, debes ir al editor VBA
(ALT + F11) y luego insertas un Modulo; luego de esto creas

Function mifuncion (Param1 as Tipo1, Param2 as Tipo2,..., ParanN as TipoN)
as TipoResultado

lineas de codigo

End Function

y luego la ejecutas como cualquier funcion normal del excel

=mifuncion(x1,x2,...,xN)

Saludos y espero te sirva

Gustavo

"inesus" escribió:

Hola compañeros,

Cada día usamos hojas de cálculo Excel con multitud de líneas
(20000-30000) para comprobar que la facturación que nos indican es
correcta (línea a línea).

Para ello capturamos en Excel toda la información relevante y a
través de fórmulas comprobamos que coincida cada línea de factura
con las tarifas.

Nuestra solución siempre se ha basado en un formula con cantidad de
funciones anidadas (llegando a tener de 40-50 funciones por fórmula).

Esto nos genera dos problemas:
1- hojas muy pesadas que afectan al tempo de respuesta
2- Grandes problemas para hacer seguimiento y en especial cuando cambia
alguna variable de la fórmula.

Lo ideal sería crear una función en Excel en el cual le indicaramos
los parametros. Por ejemplo para facturas de transporte:
tarifa(proveedor,fecha, peso, origen, destino, coste mercancia, seguro
adicional).
¿es posible?¿hay algún tutoríal que nos pueda ayudar?
¿pesaría menos la hoja?
¿sabeis algún lugar que hayan tarificadores ya creados en excel?

Gracias por anticipado

Hola chicos,

"Gustavo" wrote in message news:

Si lo que quieres es crear un función personalizada, debes ir al editor VBA
(ALT + F11) y luego insertas un Modulo; luego de esto creas...

Antes de que inesus cometa el suicidio llamado "crear una funcion personalizada VBA" :-) comentaros que las funciones VBA son
muchiiiiiiiiiisimo mas lentas que las de la hoja de calculo en un 99% de los casos. En este caso, como he dicho seria un suicidio.
La hoja pesaria menos, pero el recalculo podria llevar dias!!!

El nivel de detalle en la consulta es muy bajo, por lo que seria dificil ofrecer una solucion concreta, pero yo intentaria optimizar
la estructura, las formula y tal vez el propio metodo de la evaluacion. Es muy importante para el funcionamiento optimo de los
modelos como el descrito que se usen estructuras, funciones, etc. adecuados. Y nada de imagenes, formatos (colores, fuentes,
formatos condicionales, filas/columnas ocultas, validaciones, etc.).

Los siguientes enlaces contienen informacion que podria ayudarle a uno a optimizar los calculos de manera imprortante (prestar mucha
atencion a los enlaces en la parte superior de cada una de las paginas indicadas mas abajo) :

http://www.decisionmodels.com/calcsecrets.htm
http://www.decisionmodels.com/optspeed.htm
http://www.decisionmodels.com/memlimits.htm

Ademas, la siguiente herramienta que a mi ver tiene un coste razonable podria ser de mucha ayuda:
http://www.decisionmodels.com/fastexcelD.htm

Saludos,
KL

Hola de nuevo,

Creo que ayudaria mucho si pudieramos tener:
1) una muestra de los datos (no es necesario poner 30000 bastaria con 5)
2) las formulas que se usan (si una formula con 7 variables usa 50 funciones - seguramente es optimizable)
3) una ejemplo de resultado basado en los dos puntos anteriores.

Saludo,
KL


"KL" wrote in message news:

Hola chicos,

"Gustavo" wrote in message news:

Si lo que quieres es crear un función personalizada, debes ir al editor VBA
(ALT + F11) y luego insertas un Modulo; luego de esto creas...

Antes de que inesus cometa el suicidio llamado "crear una funcion personalizada VBA" :-) comentaros que las funciones VBA son
muchiiiiiiiiiisimo mas lentas que las de la hoja de calculo en un 99% de los casos. En este caso, como he dicho seria un suicidio.
La hoja pesaria menos, pero el recalculo podria llevar dias!!!

El nivel de detalle en la consulta es muy bajo, por lo que seria dificil ofrecer una solucion concreta, pero yo intentaria
optimizar la estructura, las formula y tal vez el propio metodo de la evaluacion. Es muy importante para el funcionamiento optimo
de los modelos como el descrito que se usen estructuras, funciones, etc. adecuados. Y nada de imagenes, formatos (colores,
fuentes, formatos condicionales, filas/columnas ocultas, validaciones, etc.).

Los siguientes enlaces contienen informacion que podria ayudarle a uno a optimizar los calculos de manera imprortante (prestar
mucha atencion a los enlaces en la parte superior de cada una de las paginas indicadas mas abajo) :

http://www.decisionmodels.com/calcsecrets.htm
http://www.decisionmodels.com/optspeed.htm
http://www.decisionmodels.com/memlimits.htm

Ademas, la siguiente herramienta que a mi ver tiene un coste razonable podria ser de mucha ayuda:
http://www.decisionmodels.com/fastexcelD.htm

Saludos,
KL

Gracias Gustavo y KL por vuestras respuestas.

Aunque en un principio no quería bajar al detalle, os explico más por
si os pudiera interesar:

Nosotros tenemos que revisar facturas de transporte, electricidad,
telefonía, etc. cada una con sus particularidades.

En el caso que os hable, nuestro mayor problema está en que
necesitamos formulas más o menos universales para cada tipo de área
que nos simplifique el trabajo. Ahora lo que hacemos es cada vez que
hay una factura p.e. de transporte creamos una nueva fórmula.

En este caso, para poderla seguir fácilmente, ponemos funciones como
Sumar (en lugar de +) o concatenar (en lugar de &) para ver resultados
parciales dentro de la fórmula con el botón fx. Todo esto lo perdemos
cuando ponemos direcciones indirectas.

El problema de velocidad aunque es un problema pasa a ser segundario
pues, si va muy lento o ponemos que calcule manual o partimos el
fichero en dos.

TARIFAS DE TRANSPORTE

Tablas
Para calcular la tarifa de transporte tenemos las siguientes tablas:
- Tarifas: según fecha inicio tarifa, proveedor, tipo de servicio
(servicio ofrecido (urgente, normal,...), tarifa aplicable(depende del
origen y destino) y un escalado de pesos nos da una tarifa.
- Complementos a la tarifas: Las tarifas pueden tener suplementos a
partir de una fecha (por ejemplo subida de n % o suplemento de
carburante o un descuento), recargos adicionales si el cliente lo pide
o se requiere (p.e. suplemento por mercancía peligrosa o seguro
adicional de la mercancía que se calcula según valor de mercancía o
un porcentaje del envío).
- Código de tarifa: Según un origen y un destino cada proveedor tiene
un código de tarifa. Estos códigos de tarifas algunas veces se
calculan a partir de códigos postales, otras a partir de provincias,
otras a partir de comunidades autónomas y otras a partir de
poblaciones. Nosotros lo que hacemos es transformar para cada línea de
factura en los códigos postales de origen y de destino. A partir de
aquí, a cada una de estas parejas le damos una código de tarifa. Me
explico: un envío entre Madrid y Barcelona lo transformamos en un
envío entre el CP28003 y el CP08003 y según el transportista le damos
el código de la tarifa correspondiente ("Tarifa bcn" del transportista
A o "Tarifa 12" del trasnportista B).

Otras consideraciones:
- Adicionalmente los Kg por los que se calcula la tarifa se pueden
redondear a la decena o la centena o a la tonelada.
- Por otra parte a la hora de calcular la tarifa se debe tener en
cuenta el máximo del escalado anterior. Por ejemplo si tengo que
calcular 525kg y las tarifas cambian cada 100kgs debere calcular el
máximo de 500 por la tarifa hasta 500 y 525 por la tarifa hasta 600.
- También se deberá tener en cuenta el camión completo, es decir hay
un precio máximo.
- Las tarifas y los ajustes varían en el tiempo y para cada proveedor
tenemos la fecha a la que se aplica el cambio.

Es por ello que con los datos tarifa(proveedor, origen, destino,fecha,
servicio, kv, seguro, material peligroso, otros suplementos, precio de
mercancia) puedo calcular la tarifa.

Relaciones:
Proveedor, origen, destino --> tarifa aplicable
Proveedor --> Tipo de redondeo
Proveedor, fecha, tarifa aplicable, redondeo, servicio, kv --> tarifa
base (teniendo en cuenta el máximo del escalado anterior y el camión
completo)
Proveedor,fecha (valor mercancía, seguro) o (tarifa base, seguro) -->
coste seguro
Proveedor, fecha, suplemento, tarifa base --> coste suplemento
Proveedor, fecha, suplemento, kgs--> coste suplemento

No se si es mucho detalle.
Os puedo enviar las fórmulas, pero de antemano os recuerdo que lo que
hacemos cada vez es quitar las partes que para ese proveedor no se
utilizan y las ponemos de tal forma que se pueden seguir con el botón
fx



Tampoco os quiero asustar, en un principio quería:
- Indicaciones genéricas como hacerlo
- Saber si habían librerías
- Saber lo que me impactaría en recursos hacer mi propia función.

Gracias por anticipado, pues no se/sabía como empezae
Un saludo

Hola inesus,

La explicacion que das es muy util para evaluar la "gravedad del caso", pero como entenderas no es suficiente para darte una
solucion. Lo que describes no solo no me asusta sino que en mi opinion no tiene nada de otro mundo y es perfectamente manejable y
sin tener que cambiar las formulas a mano. Como ya he dicho antes, es cuestion de organizar los datos, crear tablas de referencia y
usar funciones de busqueda adecuadas. Por eso mismo te pedi que facilitaras ejemplos (especialmente de formulas) ya que es posible
que muchos de los problemas se solucionen optimizando las formulas existentes.
Tambien, si quieres, me envias el fichero a mi correo electronico quitando NOSPAM y PLEASE.

Saludos,
KL


"inesus" wrote in message news:
Gracias Gustavo y KL por vuestras respuestas.

Aunque en un principio no quería bajar al detalle, os explico más por
si os pudiera interesar:

Nosotros tenemos que revisar facturas de transporte, electricidad,
telefonía, etc. cada una con sus particularidades.

En el caso que os hable, nuestro mayor problema está en que
necesitamos formulas más o menos universales para cada tipo de área
que nos simplifique el trabajo. Ahora lo que hacemos es cada vez que
hay una factura p.e. de transporte creamos una nueva fórmula.

En este caso, para poderla seguir fácilmente, ponemos funciones como
Sumar (en lugar de +) o concatenar (en lugar de &) para ver resultados
parciales dentro de la fórmula con el botón fx. Todo esto lo perdemos
cuando ponemos direcciones indirectas.

El problema de velocidad aunque es un problema pasa a ser segundario
pues, si va muy lento o ponemos que calcule manual o partimos el
fichero en dos.

TARIFAS DE TRANSPORTE

Tablas
Para calcular la tarifa de transporte tenemos las siguientes tablas:
- Tarifas: según fecha inicio tarifa, proveedor, tipo de servicio
(servicio ofrecido (urgente, normal,...), tarifa aplicable(depende del
origen y destino) y un escalado de pesos nos da una tarifa.
- Complementos a la tarifas: Las tarifas pueden tener suplementos a
partir de una fecha (por ejemplo subida de n % o suplemento de
carburante o un descuento), recargos adicionales si el cliente lo pide
o se requiere (p.e. suplemento por mercancía peligrosa o seguro
adicional de la mercancía que se calcula según valor de mercancía o
un porcentaje del envío).
- Código de tarifa: Según un origen y un destino cada proveedor tiene
un código de tarifa. Estos códigos de tarifas algunas veces se
calculan a partir de códigos postales, otras a partir de provincias,
otras a partir de comunidades autónomas y otras a partir de
poblaciones. Nosotros lo que hacemos es transformar para cada línea de
factura en los códigos postales de origen y de destino. A partir de
aquí, a cada una de estas parejas le damos una código de tarifa. Me
explico: un envío entre Madrid y Barcelona lo transformamos en un
envío entre el CP28003 y el CP08003 y según el transportista le damos
el código de la tarifa correspondiente ("Tarifa bcn" del transportista
A o "Tarifa 12" del trasnportista B).

Otras consideraciones:
- Adicionalmente los Kg por los que se calcula la tarifa se pueden
redondear a la decena o la centena o a la tonelada.
- Por otra parte a la hora de calcular la tarifa se debe tener en
cuenta el máximo del escalado anterior. Por ejemplo si tengo que
calcular 525kg y las tarifas cambian cada 100kgs debere calcular el
máximo de 500 por la tarifa hasta 500 y 525 por la tarifa hasta 600.
- También se deberá tener en cuenta el camión completo, es decir hay
un precio máximo.
- Las tarifas y los ajustes varían en el tiempo y para cada proveedor
tenemos la fecha a la que se aplica el cambio.

Es por ello que con los datos tarifa(proveedor, origen, destino,fecha,
servicio, kv, seguro, material peligroso, otros suplementos, precio de
mercancia) puedo calcular la tarifa.

Relaciones:
Proveedor, origen, destino --> tarifa aplicable
Proveedor --> Tipo de redondeo
Proveedor, fecha, tarifa aplicable, redondeo, servicio, kv --> tarifa
base (teniendo en cuenta el máximo del escalado anterior y el camión
completo)
Proveedor,fecha (valor mercancía, seguro) o (tarifa base, seguro) -->
coste seguro
Proveedor, fecha, suplemento, tarifa base --> coste suplemento
Proveedor, fecha, suplemento, kgs--> coste suplemento

No se si es mucho detalle.
Os puedo enviar las fórmulas, pero de antemano os recuerdo que lo que
hacemos cada vez es quitar las partes que para ese proveedor no se
utilizan y las ponemos de tal forma que se pueden seguir con el botón
fx



Tampoco os quiero asustar, en un principio quería:
- Indicaciones genéricas como hacerlo
- Saber si habían librerías
- Saber lo que me impactaría en recursos hacer mi propia función.

Gracias por anticipado, pues no se/sabía como empezae
Un saludo