LIBRO DE EXCEL DEMASIADO GRANDE

15/05/2008 - 11:28 por japbcn | Informe spam

Hola a tod@s

Intentaré explicarme lo mejor posible. Si no, os ruego me pregunteis
sin problemas lo que creais necesario, pero es que me urge un poco
tomar una decisión respecto a lo que estoy haciendo.

Partimos de la base que cada mes se generan en la empresa unos 15.000
albaranes, que hay que facturar, cosa de la que se encarga el programa
de gestión, pero sobre los cuales posteriormente, nos vienen en una
Factura desde la central, los cargos por los trabajos realizados.
Viene a ser como una franquicia, en la que por ejemplo nos cobran "un
tanto" por gestión realizada. Y hay que comprobar esa Factura, para lo
que se me ha pedido una Hoja de cálculo, a la que volcándole el
archivo con los Albaranes, calcule la Factura que deberíamos recibir
desde la Central.

El volcado de datos y su ordenación ya está solucionado, (el programa
vuelca los datos sobre una hoja de Excel que solo tengo que Copiar y
Pegar, y de la ordenación, ya he hecho una Macro que se encarga de
ello).

El caso es que hay unos 12 casos de Tarifas distintas, y cada una de
ellas con 6 posibilidades, (para entendernos, Provincial, Regional,
Nacional, Internacional, Aereo, etc..., con los casos de Urgente,
Normal, Depósito, etc... para cada una de ellas), lo que me genera una
gran cantidad de Tablas a las que acudo continuamente en busca de
datos para realizar los cálculos.

En ocasiones tengo celdas con fórmulas del tipo:

=SI(AA5>0;AM5;SI(H5="";"";SI(I5=0;SI($J5="S";BUSCARV("A";INDIRECTO(S!
$P5);3);SI(SUMA($L5:$N5)<=5;BUSCARV("B";INDIRECTO($P5);
3);SI(SUMA($L5:$N5)<;BUSCARV("C";INDIRECTO($P5);
3);BUSCARV("D";INDIRECTO($P5);3))));SI($J5="S";BUSCARV("A";INDIRECTO(S!
$P5);4);SI(SUMA($L5:$N5)<=5;BUSCARV("B";INDIRECTO($P5);
4);SI(SUMA($L5:$N5)<;BUSCARV("C";INDIRECTO($P5);
4);BUSCARV("D";INDIRECTO($P5);4))))))

pero digamos que me voy apañando.

El problema: Estoy manejando cerca de 800.000 celdas con fórmulas, y
todavía estoy cerca del 60% del total de "programación" de la Hoja.
El "peso" ya es de 32 Mb, lo que me hace pensar, que si sigo
trabajando en la Hoja como vengo haciéndolo hasta ahora, poniendo
fórmulas en cada delda y utilizando celdas de apoyo, dentro de poco
manejar una hoja con semejante "peso" que puede llegar a alcanzar,
puede resultar engorroso, a la par que lento, y por tanto hacer que
todo el trabajo que vengo realizando sea inutil.

Se os ocurre alguna manera de realizar una hoja tan grande que
"aligere" el peso final?
Es correcto tener celdas con fórmulas tan grandes? O es que estoy
complicándome la vida?
Mis conocimientos de VBA son muy básicos.

Se agradecen opiniones.

Gracias por adelantado.

Joan Antoni

Siga el debate

6 respuestas

Tengo una respuesta

Preguntas similare

Importar a Excel - tamaño final demasiado grande

Leer las respuestas

#1 japbcn

15/05/2008 - 11:30 | Informe spam

Perdón, sólo aclarar que estoy utilizando Excel 2000.

Gracias

Joan Antoni

Responder a este mensaje

#2 Infinitogool

15/05/2008 - 11:39 | Informe spam

Hola japbcn
..sin tener claro la estructura de los datos.. y las necesidades de
calculo..
has probado con las "geniales" tablas dinamicas... o con filtros... o con
subtotales...

un saludo
Pedro J.

Perdón, sólo aclarar que estoy utilizando Excel 2000.

Gracias

Joan Antoni

Responder a este mensaje

#3 japbcn

15/05/2008 - 11:52 | Informe spam

Hola Infinitogool

Gracias por tu rapida respuesta.
Mi fuerte no son las Tablas dinámicas, pero te cuento:
La estructura del archivo principal sería:
Fecha, nº Albarán, Linea del albarán, codigo cliente, nombre cliente,
tipo de servicio, código Postal de destino, y peso.
Hay que "juntar" nº de albaran con su linea, para formar la referencia
completa, y luego según destino, tipo de servicio y peso, del que hay
6 posibilidades, una de ellas con cálculo, entrar en distintas Tablas,
(Tarifas), para determinar el coste, que es lo que nos llega en la
Factura. De no cuadrar el Total Factura, hay que poder comprobar, por
referencia de Albarán el coste que se ha aplicado por cada servicio,
por lo que tengo que tener claro y anotado el coste por cada una de
las 15.000 lineas del fichero.

No se si así queda mejor explicado...

Gracias

Joan Antoni

Responder a este mensaje

#4 Héctor Miguel

17/05/2008 - 08:31 | Informe spam

hola, Joan !

1) ciertamente que (el solo "intento" de) explicar "semejante" libro (32Mb / ~800Kb formulas / etc.)
no es cosa para un mensaje de consulta en el foro, ni creo "sano" ponerlo a disposicion de algun interesado :-((

2) (hasta donde se) este "tipo" de libros (exageradamente "pesados") requiere de una (profunda) re/ingenieria de:
- la "logica" con que se plantea la problematica a resolver de cada formula (o grupo de formulas)
- si se hace uso (excesivo ?) de funciones "volatiles" (como indirecto) o si'es-condicionales ("consumidoras" de recursos)
- si se efectuan calculos sobre celdas "a futuro" (o sea, hacer referencia en formulas a celdas abajo y/o a la derecha)
- y (mas segura que) probablemente a una serie mas de "factores" y situaciones (incluso "combinadas") :-((

3) (p.e.) la "kilometrica" formula que expones
-> usando aproximadamente 390 caracteres y "llamadas" a 29 funciones (segun yo)...
podrias recortarla/optimizarla/... si la cambias +/- a lo siguiente
-> de solo 147 caracteres y con "llamadas" a 9 funciones solamente

=si(aa5>0;am5;si(h5="";"";buscarv(si($j5="s";"a";elegir(coincidir(suma($l5:$n5);{0\6\11});"b";"c";"d"));indirecto(si($j5="s";s!$p5;$p5));4-(i5=0))))

4) sin "ahondar" en mas averiguaciones, parece que ese archivo necesita (con urgencia) una "cirugia mayor"

para temas generales (consejos/trucos/etc.) acerca de mejorar el rendimiento en modelos particulares...
-> http://www.decisionmodels.com/optspeed.htm
-> http://www.ozgrid.com/News/GoodVsBa...Events.htm
-> http://www.mvps.org/dmcritchie/excel/slowresp.htm

para el caso de excel 2007 puedes revisar el siguiente articulo:
-> Improving Performance in Excel 2007
http://msdn2.microsoft.com/en-us/li...30921.aspx
(escrito por Charles Williams del primer link en el parrafo anterior)

si cualquier duda (o informacion adicional)... comentas ?
saludos,
hector.

__ la consulta original __

... me urge un poco tomar una decision respecto a lo que estoy haciendo.
... cada mes se generan... unos 15.000 albaranes, que hay que facturar... que se encarga el programa de gestion
... posteriormente... en una Factura desde la central... cargos por los trabajos realizados... como una franquicia
... nos cobran "un tanto" por gestion realizada. Y hay que comprobar esa Factura
... se me ha pedido una Hoja... que... calcule la Factura que deberiamos recibir desde la Central.
... volcado de datos y su ordenacion ya esta solucionado... solo tengo que Copiar y Pegar, y... ya he hecho una Macro
... hay unos 12 casos de Tarifas distintas, y cada una... con 6 posibilidades (Provincial, Regional, Nacional, Internacional, Aereo, etc...
con los casos de Urgente, Normal, Deposito, etc...), lo que me genera una gran cantidad de Tablas
a las que acudo continuamente en busca de datos para realizar los calculos.
En ocasiones tengo celdas con formulas del tipo:
=SI(AA5>0;AM5;SI(H5="";"";SI(I5=0;SI($J5="S";BUSCARV("A";INDIRECTO(S!$P5);3);SI(SUMA($L5:$N5)<=5;BUSCARV("B";INDIRECTO($P5);

3);SI(SUMA($L5:$N5)<;BUSCARV("C";INDIRECTO($P5);3);BUSCARV("D";INDIRECTO($P5);3))));SI($J5="S";BUSCARV("A";INDIRECTO(S!$P5);4);SI(SUMA($L5:$N5)<=5;BUSCARV("B";INDIRECTO($P5);4);SI(SUMA($L5:$N5)<;BUSCARV("C";INDIRECTO($P5);4);BUSCARV("D";INDIRECTO($P5);4))))))

El problema: Estoy manejando cerca de 800.000 celdas con formulas, y todavia estoy cerca del 60% del total de "programación" de la Hoja.
El "peso" ya es de 32 Mb, lo que me hace pensar, que si sigo trabajando en la Hoja como vengo haciendolo hasta ahora
poniendo formulas en cada delda y utilizando celdas de apoyo, dentro de poco manejar una hoja con semejante "peso"
que puede llegar a alcanzar, puede resultar engorroso, a la par que lento, y por tanto hacer que todo el trabajo que vengo realizando sea inutil.
Se os ocurre alguna manera de realizar una hoja tan grande que "aligere" el peso final?
Es correcto tener celdas con fórmulas tan grandes? O es que estoy complicandome la vida?
Mis conocimientos de VBA son muy basicos.
Se agradecen opiniones.
Gracias por adelantado.
Joan Antoni

Responder a este mensaje

#5 JAP

17/05/2008 - 14:10 | Informe spam

Gracias Hector

Te cuento:
La "cirugia mayor" ya es el remedio que le apliqué. Es muy pesado tirar a la
basura lo hecho y volver a empezar, pero a veces no hay mas remedio.
Tengo que reconocer que le estuve dando vueltas al tema hasta que
reconsideré el planteamiento, y lo cambié por este que utilizo ahora.
He construido tablas intermedias que me "aligeran" la longitud de las
fórmulas, concentrando información en vez de creas celdas con multiples
si(...), y ahora, que ya estoy al mismo nivel que donde estaba antes, el
archivo "pesa" 13,5Mb.
De todos modos, me resultan de mucha utilidad varios de los comentarios que
haces:

- la función indirecto() consume muchos recursos? Por que resulta que lo
utilizo bastante ya que no se como hacerlo de otro modo. Verás, en una
celda, (a2 por ejemplo), concateno varias letras, (que corresponden a
condiciones de cada caso), hasta que forman unas siglas. Estas siglas,
coinciden con una tabla, donde tengo los precios, por ejemplo.
Si en el caso que en a2 obtuviera como resultado "MB12" por ejemplo, luego
al utilizar la formula para buscar un valor "x" en la tabla MB12 si utilizo:
buscarv("x";a2;2) no me funciona, y tengo que poner
buscarv("x";indirecto(a2);2) y entonces si funciona.
Está bien hecho así, o puedo hacerlo mejor para no consumir tantos recursos?
O mejor aún... cual sería la manera correcta de hacer esa búsqueda?

- los calculos como tu llamas "a futuro" (abajo y/o derecha) consumen más?
debo entender que excel "analiza" las celdas de columna A en adelante y de
la fila 1 para abajo?
(Es para no incurrir en ello...)

- La solución a la "kilométrica" fórmula, contiene funciones, (coincidir,
elegir), que no se muy bien como funcionan, lo que me obligará a "hicar
codos" a ver si aprendo...

- Gracias por las direcciones que me das, aunque debo reconocer :-(( que mi
dominio del idioma inglés es peor que mi dominio de excel, o sea que no
veas...
Aunque aprovecharé lo que pueda, que a base de traductores, algo "se pilla"

En general, gracias por tus consejos.

Como ves, ya había practicado "cirujía", pero me gustaría mucho poder ir
ahondando en el tema, para o bien reducir un poco mas, o bien para futuras
aplicaciones.

Muchas gracias.

Recibe un muy cordial saludo desde Barcelona, España.

Joan Antoni

Responder a este mensaje

Ads by Google

Hacer una preguntaSiguiente

Tengo una respuesta

Busqueda sugerida