Este es uno de esos post para no olvidar las cosas que vamos aprendiendo por el camino.

Desconozco el motivo/causa de los errores de Microsoft Office 2003, entre ellos, hay algunos cuya solución es sencilla y sin embargo puede traernos verdaderos dolores de cabeza. Por ejemplo, el siguiente error con el fichero SKU011.CAB:

Síntomas:

1. No podemos abrir Excel,
2. Al abrir Excel se lanza la instalación de nuevos componentes.
3. Muestra un mensaje de error: Falta el fichero SKU011.CAB.
4. Intentamos introducir el CD de instalación, pero tampoco encuentra el dichoso fichero.

Solución:

1. Inicio -> Ejecutar: regedit
2. Nos abre el editor del registro de Windows (mucho cuidado con lo que cambiáis aquí)
3. Desplegar el árbol de keys siguiente
   • [HKEY_LOCAL_MACHINE] -> [SOFTWARE] -> [Microsoft] -> [Office] -> [11.0] -> [Delivery] -> {90000409-6000-11D3-8CFE-0150048383C9}
   • Nota: El valor {90000409-6000-11D3-8CFE-0150048383C9} puede ser diferente.
4. Cambiar el valor de [CDCache] a [0].

Fácil y sencillo, ahora ya debería funcionar

Encontré la solución en el blog de Technology-M. Un blog realmente lleno de soluciones a pequeños problemas.

Compártelo

Muchas veces utilizamos Microsoft Excel para tener informes/datos/sumas de diferentes empresas/sedes/productos que ponemos en hojas diferentes de Excel, para finalmente necesitamos acabamos creando una hoja resumen donde poder ver la visión global de todo.

Para crear un resumen utilizaremos varias funciones que ya hemos explicado anteriormente en Xperimentos: SUMAPRODUCTO, INDIRECTO y DESREF. Con estas funciones y un poco de cuidado a la hora de crear las hojas de detalle obtendremos nuestro resumen de forma automática.

NOTA: Dependiendo del caso, yo personalmente utilizaría una solución basada en una base de datos (MS Access o similar), pero la realidad es que muchas personas se sienten más cómodas con Excel y prefieren utilizar las herramientas que conocen.

Un ejemplo de hoja resumen Avanzado:

NOTA: Vamos a usar el mismo ejemplo que utilizamos en el post de la función INDIRECTO, os recomiendo empezar leyendo el primer ejemplo inicial ya que en este caso daremos muchas cosas básicas por explicadas, en este caso vamos a utilizar las fórmulas SUMAPRODUCTO e INDIRECTO. También os recomiendo descargaros el Excel de ejemplo a la vez para tenerlo abierto a la vez que leéis el post.

Supongamos, por ejemplo, que tenemos un Excel con las compras de cada una de las sedes de nuestra empresa: A, B, C y D.

En cada una de las hojas tenemos el resumen de las compras realizadas con las siguientes columnas de información (cómo se puede ver en la imagen):

• Fecha
• Descripción
• Responsable
• P/U (Precio unitario)
• Importe

Ahora queremos crear un resumen en el cual obtengamos la suma total de cada una de las sedes de forma automática y sencilla:

1) En cada una de las hojas creamos un resumen en la primera fila, es importante que en todas las hojas de las sedes estén los datos en las mismas celdas. En el ejemplo, el Total del importe estará en la celda “F1”.

2) Creamos una hoja Resumen, donde recogeremos los datos totales de cada una de las sedes. En este ejemplo inicialmente sólo  recogeremos los totales del importe y la cantidad de cada una de las sedes.

Primero creamos una tabla donde en la primera columna aparecerán los nombres de las diferentes hojas Excel, escritos exactamente igual, y en la primera fila pondremos el nombre de la celda donde están los datos que deseamos recoger de todas las hojas de las Sedes. Con la fórmula  =INDIRECTO(”‘”&$B3&”‘!”&C$2) ya tenemos un primer resumen con los datos totales.

3) Ahora vamos a sacar un resumen más avanzado utilizando la función SUMAPRODUCTO y INDIRECTO. Una vez que conocemos los datos totales para cada sede, seguro que nos interesará conocer las compras realizadas en cada uno de los proveedores o incluso que productos se han comprado.

Con una tabla dinámica podríamos conseguir estos datos fácilmente para una hoja Excel, pero cuando se combinan varias hojas Excel debemos utilizar un método más refinado.  

Con la fórmula  =INDIRECTO(”‘”&C$18&”‘!”&”B3:B15″) obtenemos el rango de celdas entre B3 y B15 (columna donde están los nombres de los proveedores) de la hoja de Excel con el nombre que aparece en la celda C$18 (es decir, “Sede A“). Ahora comparamos la matriz de datos obtenida con $B19 (el nombre del proveedor, en este caso “Pro 1“)  =INDIRECTO(”‘”&C$18&”‘!”&”B3:B15″)=$B19 , esta fórmula nos devuelve una matriz de 0s y 1s que indican si la fila es o no del proveedor (coincide con el nombre del proveedor).

Por último, sólo es necesario multiplicar la matriz por los valores de las compras realizadas que los obtenemos con la siguiente fórmula INDIRECTO(”‘”&C$18&”‘!”&”F3:F15″). El resultado de la formula final es:

=SUMAPRODUCTO(1*(INDIRECTO(”‘”&C$18&”‘!”&”B3:B15″)=$B19)*INDIRECTO(”‘”&C$18&”‘!”&”F3:F15″))

Aquí tenéis una imagen con el ejemplo (click para ampliar).

4) Ya tenemos un resumen que permite obtener el detalle de cada proveedor en cada una de las sedes, a partir de aquí podéis realizar cualquier tipo de informe utilizando la misma técnica, en el Excel de ejemplo tenéis dos ejemplos más. Mucha suerte y ánimo que es más sencillo de lo que parece. 

Descargas:

Excel creado para el ejemplo de resumen avanzado.

Compártelo

Cuando tenemos una hoja de Excel con los datos organizados de forma mensual, es decir, en una columna Enero, luego Febrero, Marzo,…etc. Es habitual tener uno de los siguientes problemas:

• Los datos son mensuales y queremos obtener los datos acumulados.
• Los datos son acumulados y deseamos obtener los datos mensuales.

Para calcular de forma sencilla los datos acumulados/mensuales utilizaremos la función DESREF de Excel. También se puede realizar en base a sumas sencillas: mes n = mes n + mes n-1; el único problema es que esta fórmula no es válida para primer mes y debería ser diferente: mes 1 = mes 1 (hay que tener cuidado para evitar errores).

La función DESREF:

Con la función DESREF devuelve un rango de celdas (una o varias celdas) a partir de una celda o rango base, permite especificar un desplazamiento y después el alto y ancho del rango de celdas. El formato de la función es el siguiente:

     DESREF( ref; filas; columnas; [alto]; [ancho])

Donde:

• ref - La referencia base. Debe referirse a una celda o rango de celdas. (Ejemplo A1 o A1:B3)
• filas - Número de filas de desplazamiento (hacia arriba o hacia abajo). Si el argumento es 5, la celda de referencia pasa a estar cinco filas más abajo de ref.
• Columnas - Número de columnas de desplazamiento(hacia la derecha o izquierda). Si el argumento es 5, la celda de referencia pasa a estar cinco columnas hacia la derecha de ref.
• Alto - (Opcional) número de filas, que tendrá la referencia devuelta. Debe ser positivo.
• Ancho - (Opcional) número de columnas, que tendrá la referencia devuelta. Debe ser positivo.

Ejemplos:

Ejemplo de datos mensuales acumulados:

Supongamos que tenemos un informe con datos mensuales correspondientes al primer cuatrimestre del año, a partir de los datos mensuales deseamos generar los datos acumulados de los mismos meses del primer cuatrimestre. De esta forma “Febrero Acumulado” será igual a la suma de “Enero” + “Febrero“.

Una primera aproximación que podemos utilizar es “Ene A” =A3 y “Feb A” =B2+E2 ; esta fórmula podemos arrastrarla y completar “Mar A” y “Abr A” de forma sencilla, pero los datos de Enero tiene que ser calculados de forma diferente.

Ahora vamos a tratar de resolver el problema con la función DESREF. Con la función DESREF podemos seleccionar el conjunto de celdas que deseamos sumar, la formula final es:

         =SUMA(DESREF($A3;0;0;1;COLUMNA(A3)-COLUMNA($A3)+1))

Donde:

• COLUMNA(A3)
  • devuelve el número de la columna A = 1.
• COLUMNA($A3)
  • devuelve siempre el número de la columna A = 1.
• COLUMNA(A3)-COLUMNA($A3)+1
  • devuelve la diferencia entre las columnas.
• DESREF($A3;0;0;1;COLUMNA(A3) - COLUMNA($A3) + 1)
  • La celda de referencia está siempre en la columna A.
  • No hay desplazamiento de la celda de referencia.
  • El rango será de una fila.
  • Número de columnas a sumar, para Enero será 1, y se irá incrementando hasta para abril donde serán 4, es decir, SUMA(A3:D3).

En la imagen de arriba puede apreciarse la fórmula para Enero acumulado, Suma(A3:A3). Abajo aparece la fórmula para el mes de Abril acumulado, Suma(A3:D3).

Parece un poco más complejo, pero es la misma formula para todos los meses y un buen ejemplo del potencial de la fórmula DESREF.

Descargar:

Excel creado para el ejemplo de la función DESREF

Compártelo

Muchas veces utilizamos Microsoft Excel para tener informes/datos/sumas de diferentes empresas/sedes/productos que ponemos en hojas diferentes de Excel, para finalmente necesitamos acabamos creando una hoja resumen donde poder ver la visión global de todo.

Para crear un sencillo resumen podemos utilizar la función INDIRECTO de Excel, con esta función y un poco de cuidado a la hora de crear las hojas de detalle obtendremos nuestro resumen de forma automática.

NOTA: Este es un ejemplo sencillo que os recomiendo leer antes de pasar a otro ejemplo más avanzado. Sí queremos obtener resúmenes más complejos podemos combinar las fórmulas INDIRECTO, SUMAPRODUCTO y DESREF podéis ver un ejemplo en: Resumen avanzado con datos de diferentes hojas Excel (Será publicado en breve).

La función INDIRECTO:

En la ayuda de Excel encontramos la siguiente definición:

“Devuelve la referencia especificada por una cadena de texto. Las referencias se evalúan de inmediato para presentar su contenido. Use INDIRECTO cuando desee cambiar la referencia a una celda en una fórmula sin cambiar la propia fórmula.”

Ahora ya sabéis lo que es, ¿no?. Ciertamente con esa definición yo no me he enterado de mucho, así que lo explicaré con otras palabras y un ejemplo.

La función INDIRECTO nos permite utilizar un texto como referencia, es decir, convierte el texto en una parte de la función de Excel. Un ejemplo muy sencillo de cómo funciona, lo podemos ver en la siguiente imagen:

En la celda B2, hemos escrito:

            =INDIRECTO(A1&A2)

Donde:

• A1 contiene “C”.
• A2 contiene “1”.
• Concatenando A1 y B2 (es decir, =A1&B2) obtendríamos “C1”.
• Finalmente al aplicar la función INDIRECTO() sobre el contenido de A1 y B2, obtenemos el mismo resultado que si hubiéramos puesto en la celda “=C1”, en el este caso “Ejemplo INDIRECTO”.

NOTA: También es interesante utilizar la función INDIRECTO para que una fórmula siempre haga referencia a las mismas celdas, porque Excel actualiza las fórmulas automáticamente cuando las celdas se desplazan al eliminar/insertar/cortar/pegar filas o columnas.

Un ejemplo de hoja Resumen:

Supongamos, por ejemplo, que tenemos un Excel con las compras de cada una de las sedes de nuestra empresa: A, B, C y D.

NOTA: Otros ejemplos o casos típicos son tener una hoja Excel por mes/año, por producto, por cliente… etc. Cuando el problema se complica, por ejemplo, si tenemos pestañas por meses y un documento Excel para cada Sedes, la solución más fácil es utilizar vínculos entre documentos Excel. Personalmente, estoy en contra de los vínculos en Excel, ya que son siempre foco de problemas (enlaces rotos, desactualizados,…). Para estos casos os recomendaría utilizar una herramienta de Business Intelligence integrada con Excel, por ejemplo PALO que además es gratuita.

En cada una de las hojas tenemos el resumen de las compras realizadas con las siguientes columnas de información (cómo se puede ver en la imagen):

• Fecha
• Descripción
• Responsable
• P/U (Precio unitario)
• Importe

Ahora queremos crear un resumen en el cual obtengamos la suma total de cada una de las sedes de forma automática y sencilla:

1) En cada una de las hojas creamos un resumen en la primera fila, es importante que en todas las hojas de las sedes estén los datos en las mismas celdas. En el ejemplo, el Total del importe estará en la celda “F1”.

2) Creamos una hoja Resumen, donde recogeremos los datos totales de cada una de las sedes. En este ejemplo inicialmente sólo  recogeremos los totales del importe y la cantidad de cada una de las sedes.

Primero creamos una tabla donde en la primera columna aparecerán los nombres de las diferentes hojas Excel, escritos exactamente igual, y en la primera fila pondremos el nombre de la celda donde están los datos que deseamos recoger de todas las hojas de las Sedes.

Con la fórmula  =’SEDE A’!D1 obtendríamos el valor de la cantidad total de la Sede A, si ahora utilizamos la fórmula INDIRECTO quedaría =INDIRECTO(”‘SEDE A’!D1″). Finalmente sustituyendo “Sede A” y “D1” por el valor de las celdas B3 y C2 nos queda =INDIRECTO(”‘”&$B3&”‘!”&C$2). Ponemos “$” para que nos valga la fórmula para toda la tabla que hemos creado según se puede ver en la siguiente imagen:

Sólo nos queda ocultar la segunda fila y ya estará la primera tabla del resumen terminada

3) Imaginación al poder; utilizando la misma técnica podemos crear todo tipo de resúmenes, no obstante está técnica se queda corta si queremos obtener cosas un poco más complejas, por ejemplo: ¿Cuál ha sido el importe total de las compras al proveedor número 6 (”pro6″)? esto lo veremos en el próximo post: Resumen avanzado con datos de diferentes hojas Excel (Será publicado en breve).

Descargas:

Excel creado para el ejemplo de la función INDIRECTO.

Compártelo

Una de las preguntas que se plantea un responsable del área de organización y sistemas es si está cubriendo realmente las necesidades de su empresa, en este punto toman especial importancia, las herramientas que tiene la empresa para la toma de decisiones.

Si nos preguntan si Excel (u otra herramienta de hojas de cálculo) debería ser la herramienta para la toma de decisiones de nuestra empresa, la respuesta es rápida y contundente, NO. Ahora bien, tomémonos un poco más de tiempo y meditemos esta respuesta mirando a nuestro entorno, a los departamentos de control de gestión, dirección, finanzas,…

Hace unos nueve meses, leí un interesante artículo de Charley Kyd (Don’t discard those spreadsheets: The power of Excel-Friendly OLAP) donde ponía en evidencia que los sistemas de Business Performance Management (BPM) no habían conseguido deshacerse de las hojas de cálculo (especialmente de Excel), doy fé de que es así. Ha pasado casi un año desde que se publicó el artículo de Charley Kyd pero no ha dejado de ser actualidad, el único “pero” es que Charley Kyd se dedica plenamente a escribir y mantener el portal ExcelUser y su enfoque puede ser quizás un poco parcial.

Sin cuestionar que Excel es una buena herramienta, su uso como BPM puede ser un “infierno”: diferentes versiones de la misma información, incoherencia de datos, trazabilidad inexistente, enlaces rotos, datos desactualizados, seguridad (autentificación y autorización), formatos, macros no documentadas,… podría llenar un libro sobre los quebraderos de cabeza que me han dado las “soluciones” basadas en Excel.

La conclusión obvia es que cuanto menos Excel menos problemas. Sin embargo, cuando sumamos la potencia de Excel y una herramienta OLAP integrada la situación cambia, muchos o casi todos los problemas desaparecen y obtenemos algunas ventajas.

Me atrevería a decir que todos los productos OLAP del mercado permiten exportar datos a hojas de cálculo, pero muy pocos son los que proporcionan funciones que puedan leer o escribir datos directamente desde Excel a cubos OLAP. En base a esta característica podemos dividir los productos OLAP en dos tipos: los Normales; y usando las palabras de Charley, los “Excel-Friendly”.

• OLAP Normal: Típicamente los clientes para base de datos OLAP incluyen su propio interfaz a través del cual los usuarios deben navegar, finalmente los datos suelen acabar como un bloque de números en una hoja Excel. Si el usuario tiene suerte, detrás tendrá a un “usuario avanzado” o administrador del sistema que le ayudará a definir un informe en el formato deseado, sino tendrá que trabajar un poco más para ajustar el formato.

• OLAP Excel-Friendly: Estas herramientas se integran con Excel y a través de formulas extraen o escriben directamente los datos de los cubos OLAP. Las hojas de cálculo no contienen números, contienen fórmulas que dinámicamente se conectan a la base de datos OLAP y obtienen la información actualizada.

¿Realmente necesitamos Excel? ¿no tenemos toda la información necesaria en nuestros sistemas BPM?

Desafortunadamente, con toda la información que tenemos en nuestros sistemas integrados siempre hay algo fuera de ellos, las famosas islas de información: Datos de la Web, del canal, de subcontratas, del mercado, comprados a terceros, de la nueva división, de la administración pública… y lo peor de todo, los directores quieren conocerla y sus plazos van desde anteayer hasta mañana a primera hora (sino fuera por esto la palabra “integración” y unos cuantos meses resolverían el problema).

También podemos añadir nuestro gran amigo el Shadow IT, es como el resfriado de invierno que vuelve cada año, complica todavía más el problema. Cada usuario crea su pequeña herramienta o base de datos con un simple Excel y que contiene la información más crítica/fiable/pulida de la empresa.

Los constantes cambios en el entorno empresarial provocan que muchas veces se trabaje consolidando la información fuera de los sistemas existentes. Un ejemplo claro son las compras/ventas de empresas, en la fusión entre HP y Compaq la información consolidada se estuvo realizando en Excel durante meses y sus sistemas de información eran envidiables.

Hay muchas razones por las cuales la información sigue fluyendo a través de enormes documentos Excel, Charley da varias razones: Data Silos, Mergers and acquisitions, System conversions, External data, Forecast, Data corrections. Finalmente, cada empresa es un mundo y tiene sus propios motivos, pero el hecho es que utilizan Excel para la toma de decisiones.

¿Qué aporta una herramienta OLAP Excel-Friendly?

Sí preguntamos a un vendedor/consultor seguro que puede hasta hacernos la cena :-), pero bromas a parte, estas herramientas tienen una gran ventaja y es que Excel es una herramienta tremendamente utilizada en las empresas y muy arraigada en los usuarios. Un usuario no percibirá un cambio, sino una mejora de su herramienta de trabajo.

La adaptación no es inmediata, los conceptos de dimensiones, cubos,… etc no son triviales y es necesaria la misma formación que se utiliza para cualquier sistema BMP. Sin embargo, las posibilidades de explotar la información para un usuario que haya trabajado ya en Excel son indudablemente mucho mayores.

Un usuario ya no necesitará esperar a que el departamento de IT le desarrolle un nuevo informe, sólo necesita conocer las fórmulas de Excel con las que obtener la información y él mismo puede crear un nuevo informe, compartirlo, mejorarlo, etc. Al final, el usuario obtiene una mayor satisfacción/valor y al mismo tiempo descarga de trabajo al departamento de IT.

Es cierto que hay herramientas muy buenas y sencillas para generar informes en los sistemas OLAP “normales”, pero tienen dos inconvenientes:

1. Es necesario que los usuarios aprendan a utilizar una nueva herramienta.
2. Normalmente, los usuarios crean informes para exportarlos finalmente a Excel, luego realizan un doble trabajo. La información que manejen en Excel será estática (deberán realizar este paso siempre que quieran analizar los datos).

La conclusión es que con las herramientas Excel-Friendly, los usuarios pueden dedicar más tiempo al análisis y menos tiempo a extraer datos y mantener hojas de cálculo.

Productos OLAP Excel-Friendly

El producto OLAP Excel-Friendly más conocido es sin duda Microsoft Analysis Services. La ultima versión de Excel (Excel 2007) incluye de forma nativa funciones para poder leer datos de Analysis Services, pero no para escribir. Complementando la funcionalidad de Analysis Services existen herramientas que permiten escribir de forma integrada desde la versión Excel XP (XLCubed, IntelligentApps, BIXL)

Applix TM1, es propiedad de la multinacional IBM (adquirida junto con Cognos) y es posiblemente la mejor herramienta OLAP Excel-Friendly del mercado.

La competencia de TM1 es PowerOLAP, de la empresa Paris Technologies. Ambas soluciones incluyen conectividad con todo tipo de cubos OLAP y buenas herramientas de análisis, tanto integradas con Excel como basadas en Web.

Por último, no puedo olvidarme de la opción OpenSource/freeware, PALO de Jedox, si bien no es un producto de la categoría de los dos anteriores, es lo suficiente maduro para ser considerado como una alternativa.

Compártelo