Consolidación de datos de distintos cuadernos.

En el pasado hemos mostrado cómo consolidar datos de distintas hojas de un cuaderno usando Datos-Consolidar, Tablas Dinámicas, y MS Query.
Naturalmente se presentan situaciones en las cuales queremos consolidar datos que se encuentran en hojas de distintos cuadernos.
En esta nota veremos algunas de las técnicas posibles. Supongamos estos dos cuadernos con datos de ventas del primer trimestre de una cadena de tiendas, un cuaderno para el año 2007 (2007.xls) y el otro para el 2008 (2008.xls).

Técnica #1 - transferir los datos a un único cuaderno.
La ventaja de esta técnica es que nos permite usar con facilidad Datos-Consolidar o Tablas Dinámicas. Podemos, por ejemplo, crear un nuevo cuaderno y transferir a él los datos en hojas separadas o en una única hoja. Por ejemplo, creamos el cuaderno "Consolidado" y pasamos a él las hojas de los cuadernos 2007 y 2008




Cambiamos el nombre de las hojas transferidas a "2007" y "2008"



Todo lo que nos queda por hacer es crear una hoja consolidada con Datos-Consolidar



Alternativamente podemos usar tablas dinámicas con rangos de consolidación múltiples



Técnica #2 - Datos en cuadernos separados
Usando Datos-Consolidar.
Como en el caso anterior, empezamos creando un cuaderno donde consolidaremos los datos (Consolidado.xls).
Una vez creado el cuaderno abrimos el menú datos consolidar y creamos la referencia al rango relevante en el cuaderno 2007.xls



Apretamos "agregar". Ahora veremos que Excel no nos permite crear una referencia al rango relevante en el cuaderno 2008.xls. Lo que haremos será crear la referencia manualmente



Sencillamente tecleamos la referencia manualmente, cambiando 2007 por 2008 y extendiendo el rango a D8.

Pulsamos "Aceptar" y Excel consolidará los datos.



Usando Tablas dinámicas.
También aquí usamos rangos de consolidación múltiples, creando referencias a las hojas relevantes de los distintos cuadernos. A diferencia de Datos-Consolidar, creamos las referencias directamente apuntando a los rangos en las hojas de los distintos cuadernos



El resultado es

Technorati Tags: MS Excel

Listas desplegables dependientes múltiples

En la nota anterior sobre listas desplegables dependientes mostramos cómo crearlas con validación de datos.
En esa nota vimos como crear una lista desplegable de países y ciudades. Una vez elegido el país, la segunda lista desplegable mostraba sólo ciudades de ese país.
Algunos lectores me consultan como hacer lo mismo pero con más de dos niveles de dependencia. Por ejemplo, continentes-países-ciudades.
La técnica es básicamente la misma. Incluimos las listas en rangos nominados (dentro de nombres, usando Insertar-Nombres-Definir) y luego usamos validación de datos con la opción Lista y en Origen usamos fórmulas con la función INDIRECTO.
Supongamos este cuaderno con cuatro hojas



En la hoja Continentes tenemos una lista de los continentes



Estos valores nos servirán como referencia a los nombres que contendrán la lista de países de cada continente. Definimos el rango A1:A7 dentro del nombre "continente"

continente=Continentes!$A$1:$A$7

En la hoja Países creamos campos con las listas de los países de cada continente. Por comodidad (la mía) he puesto sólo dos países por continente



Finalmente ponemos listas de las ciudades por países en la hoja Ciudades




Ahora tenemos que definir los nombres. Una forma de ahorrar tiempo es usar el método Insertar-Nombre-Crear. En la hoja Países seleccionamos la celda A1 y abrimos el menú Insertar-Nombre-Crear



Como queremos que Excel use la fila superior como rótulo para los nombres, señalamos la opción "crear nombres en fila superior".

El resultado será:

África=Paises!$A$2:$A$3
América_del_Norte=Paises!$B$2:$B$3
América_Central=Paises!$C$2:$C$3
América_del_Sur=Paises!$D$2:$D$3
Asia=Paises!$E$2:$E$3
Europa =Paises!$F$2:$F$3
Oceanía=Paises!$G$2:$G$3
Como verán Excel ha agregado "_" en los casos que el nombre del continente está formado por más de una palabra. Esto se debe a que por definición los nombres en Excel no pueden tener espacios en blanco (ni símbolos especiales).

Usamos el mismo método para definir los nombres en la hoja Ciudades, con este resultado

Congo =Ciudades!$A$2:$A$61
Canadá =Ciudades!$B$2:$B$61
México=Ciudades!$C$2:$C$61
Venezuela=Ciudades!$D$2:$D$61
China=Ciudades!$E$2:$E$61
Alemania=Ciudades!$F$2:$F$61
Australia=Ciudades!$G$2:$G$61
Angola =Ciudades!$H$2:$H$61
Estados_Unidos=Ciudades!$I$2:$I$61
Guatemala=Ciudades!$J$2:$J$61
Colombia=Ciudades!$K$2:$K$61
Vietnam=Ciudades!$L$2:$L$61
Francia =Ciudades!$M$2:$M$61
Islas_Fidji=Ciudades!$N$2:$N$61

Como ven, todos los nombres tiene el mismo tamaño de rango (de la fila 2 a la 61), lo que hará que en la lista desplegable aparezcan espacios en blanco. Más adelante veremos como solucionar este problema.

Ahora que hemos definido todos los nombres, definimos las listas desplegables en la hoja "Elección"

En la celda B1 definimos la lista con la opción Lista y la fórmula "=continente". Esto crea una referencia al rango que contiene los nombres de los continentes



En la celda B2 creamos la lista de países que será dependiente del continente elegido en la celda B1



En este caso creamos la referencia al rango usando la función INDIRECTO. Además tenemos que usar la función SUSTITUIR para poner las líneas "_" en lugar de los espacios entre las palabras, para que el valor de la celda coincida con el nombre del rango

=INDIRECTO(SUSTITUIR(B1," ","_"))

Usamos la misma técnica en la celda B3, usando como referencia el valor de la celda B2



Si elegimos el continente América del Sur, podremos elegir sólo Venezuela o Colombia. Si elegimos Colombia podremos elegir una de las ciudades que hemos incluido en la columna Colombia de la hoja Ciudades



Si fuera necesario podríamos agregar más listas dependientes creando los campos adecuados (barrios, jurisdicciones, etc.).

La técnica que hemos mostrado adolece de un defecto estético, los espacios en blanco. Para solucionar este problema tenemos que usar rangos dinámicos. Normalmente usamos la función DESREF para crear rangos dinámicos. El problema en nuestro caso es que la función INDIRECTO solo acepta rangos como argumento.
Para superar este problema usaremos esta fórmula en la creación de la lista desplegable dependiente de la celda B3 (ciudades)

=DESREF(INDIRECTO(SUSTITUIR(B2," ","_")),0,0,CONTARA(INDIRECTO(SUSTITUIR(B2," ","_"))),1)



Ahora la lista dependientes de ciudades no mostrará espacios en blanco.



La fórmula fue tomada del excelente sitio Contextures de Debra Dalgleish.

El cuaderno con el ejemplo y las fórmulas puede descargarse aquí.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

Ultimo valor en el rango (fila o columna)

En la nota sobre hipervínculos describimos una fórmula para obtener la dirección de la última celda en la columna que contiene un valor.
La fórmula en cuestión nos da la dirección de la última celda no vacía a condición que todos los valores del rango, o por lo menos el de la última celda, sean texto.
Por supuesto hay casos en que los valores en el rango son numéricos, en otros casos son sólo texto y también hay casos en que hay valores de ambos tipos.
Supongamos estos dos rangos de valores



La fórmula que nos dará la dirección de la última celda en la columna que contiene un valor de tipo texto es

=DIRECCION(COINCIDIR("*",A:A,-1),1)



El problema con esta fórmula es que si en una celda aparece el símbolo * como texto, dará como resultado la dirección de esa celda. Podemos reemplazar esa fórmula por ésta

=DIRECCION(COINCIDIR(REPETIR("z",250),A:A),1)



Hemos reemplazado el valor de la celda A4 por "*". Usando la primer fórmula (en la celda D3) obtenemos el resultado $A$4, que es incorrecto. Con nuestra nueva fórmula obtenemos el resultado correcto (en la celda D4).

Si el rango a evaluar contiene sólo valores numéricos, como en la columna B, usamos esta fórmula

=DIRECCION(COINCIDIR(9.99999999999999E+307,B:B),2)



La expresión 9.99999999999999E+307 es el mayor número que Excel puede considerar.

Para obtener el valor de la última celda podemos usar la función INDIRECTO usando como referencia la celda que contiene la dirección o anidando la fórmula dentro de INDIRECTO. Por ejemplo =INDIRECTO(D3) dará como resultado "Laura"



También podemos usar las funciones anidadas en una única fórmula

=INDIRECTO(DIRECCION(COINCIDIR("*",A:A,-1),1))

Y en el caso de la columna B con valores numéricos

=INDIRECTO(DIRECCION(COINCIDIR(9.99999999999999E+307,B:B),2))

Para encontrar la dirección de la última celda en un rango mixto (valores numéricos y texto) podemos usar esta fórmula matricial

={MAX((FILA(A1:A65535)*(A1:A65535<>"")))}

o esta que es la misma pero con SUMAPRODUCTO

=DIRECCION(SUMAPRODUCTO(MAX((FILA(A1:A65535)*(A1:A65535<>"")))),1)

La expresión (A1:A65535<>"") crea una matriz de valores VERDADERO o FALSO.
La expresión crea una matriz con el número de fila. Al multiplicar los vectores, el número de fila de toda celda vacía será 0. Por lo tanto la función MAX nos dará el número de fila de la última celda no vacía.

Otro tipo de solución es utilizar funciones definidas por el usuario (UDF) como las que publicó John Walkenbach hace ya mucho tiempo.

Aquí pueden descargar el cuaderno con las funciones y las macros.

Technorati Tags: MS Excel

Hipervínculos en Excel

Excel permite crear hipervínculos con facilidad. Todo lo que tenemos que hace es seleccionar una celda y pulsar Ctrl+Alt+K,




o usar el menú contextual pulsando el botón derecho del Mouse sobre la celda



o usar el menú Insertar-Hipervínculo



En este caso



creamos un vínculo a la celda A1 de la hoja Lista en nuestro cuaderno.

De la misma manera podemos crear vínculos a celdas en otras hojas del cuaderno, celdas en hojas de cuadernos remotos y también a páginas en la WEB.



Al señalar la celda con un hipervínculo, podemos ver la información de pantalla, dato que podemos modificar en el diálogo de creación del hipervínculo.

Además podemos agregar hipervínculos no sólo a celdas sino también a otros objetos como gráficos o imágenes, por ejemplo.

Otra forma de crear un hipervínculo es usar la función HIPERVINCULO. Por ejemplo, podemos crear el mismo hipervínculo como en la celda A1, usando el asistente de funciones


Sin embargo al referirse a un rango en una hoja del cuaderno, la función no funcionará





Para solucionar este problema usamos el símbolo # combinado con el nombre de la hoja y la referencia a la celda, en forma de texto, como argumento de la función
=HIPERVINCULO("#Clientes!A1","Pasar a la hoja Clientes)



La ventaja de usar la función en lugar del menú, es que podemos crear vínculos dinámicos.

En esta nota nos referiremos sólo a vínculos dentro del cuaderno activo.

Por ejemplo, supongamos que tenemos un cuaderno con varias hojas, una de ellas llamada "Clientes" y otra llamada "Proveedores".
En lugar de crear dos hipervínculos, uno a cada hoja, podemos crear una estableciendo la hoja a la que queremos pasar en forma dinámica.
Por ejemplo, en la celda A1 creamos una lista desplegable (usando el menú Validación de Datos) con los nombres de las hojas del cuaderno



En la celda C1 ponemos está fórmula con la función HIPERVINCULO

=HIPERVINCULO("#"&A1&"!A1","Pasar a la hoja "&A1)

Como ven, usamos el valor de la celda A1 también para crear la referencia a la hoja y también para crear el nombre descriptivo



Tanto la referencia como el nombre descriptivo cambian de acuerdo a la selección en la celda A1.

Hasta aquí hemos creado hipervínculos que nos llevan a la celda A1 de la hoja buscada. Ahora supongamos que queremos que el vínculo nos lleve automáticamente a la primer celda vacía de una determinada columna.
En la hoja Clientes tenemos una lista de clientes y sus direcciones. Queremos que el hipervínculo nos lleve, en nuestro ejemplo, a la celda A9 de la hoja Clientes



Dado que la columna A de la hoja Clientes contiene valores de texto, podemos usar esta fórmula para encontrar la dirección de la primer celda vacía

=DIRECCION(COINCIDIR("*",Clientes!A:A,-1)+1,1)

En esta fórmula estamos suponiendo que el símbolo * no aparece como texto en ninguna de las celdas del rango de búsqueda.
Ponemos esta fórmula en la celda B1 y la usamos como referencia en la función HIPERVINCULO



En lugar de usar una celda auxiliar, podemos incluir la fórmula de la celda B1 como argumento en la función HIPERVINCULO

=HIPERVINCULO("#"&A1&"!"&DIRECCION(COINCIDIR("*",Clientes!A:A,-1)+1,1),"Pasar a la hoja "&A1)

Finalmente podemos dar un paso más adelante y vincular dinámicamente también la búsqueda de la primer celda libre en función del valor de la celda A1

=HIPERVINCULO("#"&A1&"!"&DIRECCION(COINCIDIR("*",INDIRECTO(A1&"!A:A"),-1)+1,1),"Pasar a la hoja "&A1)
Aquí tenemos que usar la función INDIRECTO para que Excel considere el valor de la celda A1 en la función COINCIDIR.



El archivo con la fórmula se puede descargar aquí





Technorati Tags: MS Excel

Ajuste automático de fórmulas en Excel

Excel ajusta las referencias dentro de una fórmula automáticamente. Supongamos esta operación en una hoja de Excel



La celda B7 calcula el total de unidades vendidas con =SUMA(B2:B6). Como ya habrán notado, hemos olvidado de incluir el Producto 4.
Como queremos guardar el orden en nuestra lista de productos incluimos el Producto 4 entre la línea 4 y la línea 5



Como pueden ver, Excel ha corregido automáticamente el rango en la función SUMA.

Agreguemos una nueva columna a nuestra tabla, Ingresos. En la celda A12 hemos puesto una lista desplegable con los productos y en la celda B12 una fórmula con la función BUSCARV que nos muestra los ingresos del producto elegido



Ahora agreguemos la columna Precio entre las columnas "Unidades Vendidas" e "Ingresos"



Como pueden ver Excel ha ajustado el rango de la fórmula de A2:C7 a A2:D7 para incluir la nueva columna, pero no ha modificado el tercer argumento de la función BUSCARV que sigue siendo 3. Es decir, BUSCARV sigue buscando en la tercer columna a la derecha de la columna A, que ahora es Precio y no Ingresos.
No se trata de un "bug" de Excel, ya que el argumento es un número de referencia estático y no un rango.
Veamos algunas técnicas para sobreponernos a este problema. Si nuestra función BUSCARV siempre se refiere a la última columna en el rango, podemos crear una referencia dinámica con la función COLUMNAS.

Empecemos por incluir nuestra tabla de datos en un nombre, tabla_productos



Ahora modificamos nuestra fórmula de la siguiente manera

=BUSCARV(A12,$A$2:$D$7,COLUMNAS(tabla_productos),0)

El resultado es el siguiente



Como ven, nuestra fórmula se ha ajustado automáticamente. Lo mismo sucederá si agregamos una nueva columna dentro del rango



La función COLUMNAS cuenta el número de columnas en una matriz o rango al cual se refiere.
Si queremos que la función se refiera dinámicamente a la anteúltima columna, usaremos la expresión COLUMNAS(referencia)-1.

Technorati Tags: MS Excel

Cálculo de fecha de pago con Excel.

Un caso particular del cálculo de fechas es el de fechas de pago. Si las condiciones de pago de una factura, o cualquier otra obligación, son, por ejemplo, 30 días de la fecha de la factura, el cálculo con Excel es muy sencillo. Para calcular la fecha de pago de una factura cuya fecha es el 25/07/2008 y las condiciones de pago son fecha factura + 30 días, usamos



Pero si la intención es que la fecha de pago sea el mismo día de la fecha de la factura pero un mes más tarde, usamos la función FECHA.MES (en algunos sistemas tendremos que usar la versión inglesa: EDATE)



Para poder usar esta función necesitamos que esté instalado el complemento Analysis ToolPak.
Como puede verse, los resultados son distintos. En el primer caso, 24/08/2008 es exactamente 30 días; el segundo resultado es el 25 del mes siguiente.

Ciertas empresas e instituciones tienen días de pago determinados. Por ejemplo, supongamos una empresa que paga sólo los 15 de cada mes. Si la condición de pago es "fecha de la factura + 30 días", las facturas con fecha posterior al 15 del mes serán pagadas de hecho dos meses más tarde.
Veamos este caso



La fecha de pago de la segunda factura será un mes más tarde que el de la primera. Cómo hacemos para calcular estas fechas con Excel? Obviamente creando un fórmula condicional con la función SI

=SI(DIA(A3+30)<=15,FECHA(AÑO(A3+30),MES(A3+30),15),FECHA(AÑO(A3+60),MES(A3+60),15))



Usamos la función FECHA para "armar" la fecha de pago basándonos en la fecha de la factura.
En caso que queramos establecer un mes después de la fecha de la factura (o dos, o cualquier otro número de meses) usaremos la función FECHA.MES

Technorati Tags: MS Excel

Cancelar botones de pegado y autorrelleno en Excel

Excel tiene varias funcionalidades para agilizar o hacer más eficiente el trabajo de usuario. Algunas son muy prácticas, otras pueden resultar molestas.
En esta última categoría encuadran, para mi gusto personal, los botones de autorrelleno y pegado. Sobre el molesto Autocompletar ya me explayado en otra oportunidad.
Estos botones aparecen, sin que nadie los haya llamado, cuando copiamos un rango




o arrastramos una serie de celdas para crear una serie



Para desactivar estos botones tenemos que abrir el menú Herramientas-Opciones y en la pestaña Modificar, quitar la marca de las opciones correspondientes

Technorati Tags: MS Excel

Extraer direccion de celda en matriz

Supongamos que tenemos esta matriz



¿Cómo haríamos para encontrar la dirección de la celda que contiene el valor 455? Excel cuenta con la función DIRECCION a la cual le podemos pasar el número de fila y el número de columna y nos dará la dirección en forma de texto. Luego usando la función INDIRECTO podemos usar este texto como referencia real a la celda.
Si nuestra tuviera sólo una fila o una columna podríamos resolver el problema con facilidad con esta fórmula

=DIRECCION(1,COINCIDIR(A7,A1:E1))



El problema es que el valor buscado puede estar en cualquiera de las filas y columnas de la matriz y COINCIDIR no funciona cuando la matriz de búsqueda no es unidimensional.

No he profundizado en una solución basada en funciones nativas de Excel pero podemos encontrar una solución bastante sencilla creando una función definida por el usuario (UDF):

Public Function busc_mat(matriz As Range, valor_busc)
    Dim Celda As Range
    
    For Each Celda In matriz
        If Celda.Value = valor_busc Then _
                busc_mat = Celda.Address
    Next
        
End Function


Los argumentos de la función son

Matriz: la tabla de búsqueda
Valor_busc: el valor de referencia

Copiamos el código de la función a un módulo del editor de Vba (preferentemente en el cuaderno Personal.xls) y aplicamos la función a nuestro ejemplo




Esta función tiene un inconveniente: si el valor aparece más de una vez en la matriz, siempre dará como resultado el valor que se encuentre más abajo y a la derecha en la tabla. Por ejemplo, si agregamos algunos valores a nuestra tabla




Vemos que el resultado es $A$4. El código busca hacia la derecha y de arriba hacia abajo y retiene el último valor encontrado.
Si queremos obtener un resultado que no sea la dirección del último valor en la tabla, tendremos que modificar nuestra función.
Lo que hacemos es agregar un argumento opcional, el número de orden (nro_orden). Lo definimos como opcional ya que suponemos que no sabemos de antemano cuantas instancias del valor buscado hay en la tabla. Si dejamos el argumento en blanco, la función deberá dar el último valor; si ponemos un número de orden inexistente, la función dará un resultado de error (#VALOR!).
El código modificado de la función es:

Public Function busc_mat2(matriz As Range, valor_busc, _
                                Optional nro_orden As Integer)
    
    Dim Celda As Range, tmpRng(), tmpSize As Long, Counter As Integer
    
    If nro_orden = 0 Then
    
        For Each Celda In matriz
            If Celda.Value = valor_busc Then _
                    busc_mat2 = Celda.Address
        Next
    
    Else
    
        tmpSize = WorksheetFunction.CountIf(matriz, valor_busc)
    
        ReDim tmpRng(tmpSize)
        
        Counter = 0
        For Each Celda In matriz
            If Celda.Value = valor_busc Then
            Counter = Counter + 1
                tmpRng(Counter) = Celda.Address
            End If
        Next
        
    busc_mat2 = tmpRng(nro_orden)
    
    End If
    
End Function


Si nro_orden está en blanco, usamos el código de la primer función. Si ponemos un número de orden, creamos primero un vector (array) con las direcciones de las celdas cuya valor coincide con el valor de búsqueda y luego usamos el argumento nro_orden para extraer el elemento correspondiente del vector.

Un uso posible sería en un caso como este:



Podemos saber en qué mes se dio la instancia buscada, buscando el valor en la matriz y calculando la columna del mes correspondiente. La fórmula para calcular el mes es

=INDICE(A1:E1,COLUMNA(INDIRECTO(busc_mat2(A2:E6,B8,B9))))

Usamos INDIRECTO para interpretar la dirección de la celda como tal y no como texto.

Technorati Tags: MS Excel