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

Usar BUSCARV (Excel) en listas con valores repetidos.

Usamos BUSCARV para encontrar en una tabla el valor correspondiente a otro determinado valor. Esta función, así como otras funciones de búsqueda en Excel, tiene una limitación. Si el valor de búsqueda tiene más de un valor correspondiente en la matriz de búsqueda, Excel dará como resultado el primer valor (en orden de aparición).

Supongamos esta lista de órdenes de compra.



Cada producto aparece tres veces, cada vez con distinto precio. Si usamos BUSCARV para averiguar el precio de los tornillos, el resultado será 7.65



Como explicamos antes, BUSCARV nos dará siempre el valor que aparece en primer lugar en la matriz. Si queremos encontrar el valor correspondiente al argumento de búsqueda que no sea el primero en la matriz, tenemos que usar una fórmula matricial como esta propuesta por Chip Pearson

={INDICE(compras,K.ESIMO.MENOR(SI(DESREF(compras,0,0,FILAS(compras),1)=B12,FILA(DESREF(compras,0,0,FILAS(compras),1))-FILA(DESREF(compras,0,0,1,1))+1,FILA(DESREF(compras,FILAS(compras)-1,0,1,1))+1),B13),3)}



Esta fórmula supone que sabemos cuantas instancias de Tornillos hay en la lista. Si buscamos una instancia inexistente, por ejemplo el cuarto valor de Tornillos, obtenemos un resultado #REF!

Podemos adaptar la fórmula para que dé el último valor de la lista

={INDICE(compras,K.ESIMO.MENOR(SI(DESREF(compras,0,0,FILAS(compras),1)=B12,FILA(DESREF(compras,0,0,FILAS(compras),1))-FILA( DESREF(compras,0,0,1,1) )+1,FILA( DESREF(compras,FILAS(compras)-1,0,1,1))+1),CONTAR.SI(DESREF(compras,0,0,FILAS(compras),1),B12)),3)}



Si queremos buscar el máximo valor de Tornillos, sin relación al orden de aparición en la lista, podemos usar esta fórmula matricial:

={MAX((A2:A10=B12)*(C2:C10))}



El archivo con las fórmulas se puede descargar aquí.

Actualización (Julio 2019): once años después de la publicación de esta nota Power Query, que Microsoft ha incorporado a Excel en los últimos años, nos permite realizar esta tarea mas eficientemente. Les invito a consultar esta nota.


Technorati Tags: MS Excel

Quitar acentos (tildes) de un texto en Excel.

Un lector me consulta cómo quitar los acentos (tildes) de un texto en una celda de Excel. Por ejemplo, si en la celda A1 tenemos el texto "María Angélica Esdrújula", nuestra tarea es transformar el texto en "Maria Angelica Esdrujula".

Coincidamos que ésta no es una tarea corriente en Excel. Pero la solución nos permitirá mostrar algunas técnicas útiles para construir funciones definidas por el usuario (UDF).

Empecemos por el caso más sencillo. Supongamos que en la celda A1 tenemos el texto "María". Queremos quitar el acento y transformar el texto en "Maria". En este caso sencillo podemos usar la función SUSTITUIR de la siguiente manera

=SUSTITUIR(A1,"í","i")



El problema con esta solución surge si tenemos varias palabras con acentos en distintas vocales.
En ese caso tendremos que usar una función definida por el usuario (UDF). Es decir, tendremos que escribir por nuestra cuenta el código de la función.

Empecemos por definir el problema. En castellano ponemos acentos sólo sobre vocales. Esto significa que tendremos cinco casos: á, é, í, ó y ú.
Lo primero que tenemos que saber es cuál es el código ASCII de estás vocales acentuadas, y cuáles son los códigos de las vocales sin acento. Para esto podemos usar la función CODIGO



Nuestra función tendrá que descomponer le texto en cuestión y reemplazar las vocales acentuadas por vocales sin acentos.
Empecemos por el final mostrando el código de la función:

Function txtNoAcc(texto) As String
    Dim largoTexto As Long, iX As Long
    Dim Lett As Long
  
    txtNoAcc = ""
  
    largoTexto = Len(texto)
  
    For iX = 1 To largoTexto
        Lett = Asc(Mid(texto, iX, 1))
        Select Case Lett
            Case Is = 225
                txtNoAcc = txtNoAcc & Chr(97)
            Case Is = 233
                txtNoAcc = txtNoAcc & Chr(101)
            Case Is = 237
                txtNoAcc = txtNoAcc & Chr(105)
            Case Is = 243
                txtNoAcc = txtNoAcc & Chr(111)
            Case Is = 250
                txtNoAcc = txtNoAcc & Chr(117)
            Case Else
                txtNoAcc = txtNoAcc & Mid(texto, iX, 1)
        End Select
    Next iX
End Function

Nuestra función tiene un solo argumento: "texto", que es el texto contenido en la celda cuyo contenido queremos transformar. Hemos definido tres variables, cuya función será evidente más adelante.

La sentencia
"largoTexto = Len(texto)"
define cuantas letras y espacios hay en la celda.

Luego usamos la construcción For Next para descomponer el texto en sus componentes, los cuales analizamos para ver si hay alguna vocal con acento.
Para esto hemos definido la variable

Lett = Asc(Mid(texto, iX, 1))

que nos da el número de código de cada una de las letras del texto. Una vez que tenemos el número de código ASCII de cada letra, lo sometemos al examen de la construcción Select Case. En el caso que se trate de alguno de los cinco casos de letras con acento, ésta será reemplazada por la letra sin acento. En caso contrario, dejaremos la letra original.
Todo esto se va concatenando en la variable " txtNoAcc", el nombre de la función.

Para usar la función copiamos el código en un módulo del editor de VBa (preferentemente en el cuaderno Personal.xls, de manera que la función esté a nuestra disposición cualquiera sea el cuaderno abierto).
Luego, abrimos el asistente de funciones en la categoría "Definidas por el usuario"




y elegimos la función que acabamos de definir



El resultado será el esperado

Technorati Tags: MS Excel

Numeros con ceros precedentes en Excel.

El tema de hoy es cómo agregar ceros precedentes a un valor en Excel. Esta es una tarea bastante común para todos aquellos que trabajan con sistemas como SAP o Oracle Applications e importan datos del sistema a Excel.
Uno de los problemas que suelen surgir al importar archivos .csv es que Excel convierte todo lo que parece un número a valor numérico. Si tenemos un campo con números de catálogo cuya primer cifra es cero, por ejemplo 0123456, Excel lo transformará a 123456. Podemos superar este problema usando las técnicas explicadas en la nota sobre cómo importar datos a Excel, pero hay situaciones en las cuales recibimos un archivo ya sin los ceros precedentes.

Veamos este ejemplo, donde en la columna A tenemos los valores originales (texto) y en la columna B tal como aparecen en Excel después de importarlos



Veamos las distintas alternativas a nuestra disposición.
Si queremos transformar el número a texto agregando un cero precedente y todos los valores tienen el mismo número de cifras, podemos optar por la opción de concatenar el valor en la columna B con un cero



Podemos también usar la función CONCATENAR en lugar del operador "&". Excel dará automáticamente formato de texto al resultado.

Si queremos agregar un cero precedente, pero que se mantenga el formato de número, tendremos que aplicar un formato personalizado como este: "0"#



Estas técnicas no se pueden aplicar al valor en la celda B3, ya que allí necesitamos dos ceros precedentes. Podemos aplicar la fórmula ="00"&B3 o aplicar el formato personalizado "00"#. Pero estas técnicas nos obligan a escribir una fórmula distinta para cada caso.

Si todos los valores tienen la misma cantidad de dígitos, por ejemplo 7 como los valores en el rango A2:A4, podemos aplicar el formato personalizado "0000000"



Los valores mostrarán ahora el número de ceros precedentes requerido pero seguirán siendo números. Si copiamos y pegamos los valores en el rango C2:C4 con Pegado Especial-Valores, veremos en la barra de las fórmulas que Excel no "ve" los ceros precedentes



Si queremos convertir los valores a texto, podemos usar la función TEXTO, de esta manera

=TEXTO(B2,"0000000")

Technorati Tags: MS Excel

JLD de vacaciones

A partir de hoy y hasta el lunes 14 de julio, estaré de vacaciones terminando mi tantas veces postergado curso de buceo.
Respuestas a los comentarios y mails se demorarán hasta entonces. Mientras tanto, gracias a todos y buen fin de semana.

Technorati Tags: MS Excel

Convertir valores numéricos a texto en Excel

Hay situaciones en las cuales queremos transformar valores numéricos a texto. Uno de los casos más corrientes es con las números de catálogo. Estos códigos pueden estar formados sólo por cifras o contener letras y cifras. Debido a esto son guardados en la base de datos original como texto.
Dependiendo del método que usemos para importar los datos, tendremos o no control sobre el formato de los datos en la hoja de Excel (recordemos que también podemos usar el MS Query).
Supongamos que hemos importado esta lista de números de catálogo. Aquellos que contienen alguna letra han sido convertidos a texto. Pero aquellos que contienen sólo cifras han sido transformados a números, a pesar de que en la case de datos original hayan sido guardados como texto



En la columna auxiliar "chequeo" uso la función ESNUMERO para verificar a qué tipo han sido transformados los datos.
Como podemos ver, los datos en las celdas A2 y A3 han sido transformados a valores numéricos.
En esta nota mostraremos las distintas posibilidades que nos ofrece Excel para transformarlos a texto.

1 –Usar la función TEXTO. Esta función no sólo convierte el valor numérico en texto, sino que también nos permite establecer el formato.



En este ejemplo hemos empleado el formato "General". En lugar de la palabra general podemos emplear el símbolo que lo representa: "@".
Como pueden ver la columna de chequeo (E) muestra ahora FALSO para todas las filas.
Si hace falta, podemos pegar los valores convertidos a texto en el rango original (la columna A en nuestro ejemplo), usando Pegado Especial-Valores.



Esto hará que el formato de texto se mantenga también en la columna A



2 – Usar el menú Datos-Texto en columnas. Seleccionamos el rango a convertir y abrimos el menú indicado



En el diálogo que se abre pasamos al paso 3 y marcamos Texto en la opción Formato de los datos en la columna



Apretamos Finalizar y veremos que los datos han sido convertidos a texto



Una confusión bastante común en los usuarios principiantes es intentar aplicar el formato de celda "Texto" a toda la columna. Esto no influye en los datos que ya se encuentran en las celdas de la columna. Si aplicamos el formato Texto a una celda que contiene un valor numérico, deberemos editarla para que la conversión tome efecto.
Por ejemplo, seleccionamos la celda A2 en nuestro ejemplo. Aplicamos el formato Texto con el menú Formato de celdas. Luego apretamos F2 (modo de edición) e inmediatamente Enter. El número se convertirá en Texto. Lo que hemos hecho equivale a haber tipeado el número en la celda después de haber puesto el formato de texto.

Technorati Tags: MS Excel

Totales en el tope de una tabla dinámica de Excel

Cuando creamos una tabla dinámica y no cancelamos los totales por columna (con el menú Opciones de tabla), Excel pone los totales al pie de los campos de datos de la tabla



Hay situaciones en las cuales, a diferencia del ejemplo en esta nota, tenemos muchas líneas en nuestra tabla. Por ejemplo, supongamos que en lugar de 5 departamentos de una tienda, tenemos 100 o 200 sucursales de una red.
En esos casos es conveniente mostrar el total de la tabla en la parte superior, en lugar de forzar al destinatario del informe a moverse hasta el final de la tabla.
Excel no tiene un método integrado para hacer esto, pero podemos lograrlo con un pequeño truco.
Como en la nota sobre el cálculo de porcentaje para subtotales, lo que haremos es agregar una columna (campo) auxiliar.
Siguiendo con nuestro ejemplo, insertamos una columna entre Mes y Ventas (Total)



Al insertar el nuevo campo entre dos existentes no tenemos que actualizar el rango de la tabla dinámica ya existente.

Apretamos el icono de Actualización de datos para que el nuevo campo aparezca en la lista de campos. Lo arrastramos al área de campos de fina asegurándonos que sea el primer campo del área (a la izquierda)



El próximo paso es abrir el menú de configuración del campo, apretamos el botón Diseño y marcamos las opciones Mostrar elementos en formulario esquemático y Mostrar subtotales en la parte superior del grupo



Apretamos Aceptar dos veces y obtenemos esta tabla



Todo lo que nos queda por hacer es deshacernos de la línea de Total General al pie de la tabla. Para esto abrimos el menú de Opciones de la tabla y quitamos la marca de la opción Totales generales de columnas



con lo cual logramos el resultado esperado

Technorati Tags: MS Excel

Una manera fácil de publicar hojas Excel en la WEB.

Hasta ahora no me ocupado en este blog de temas que no estuvieran directamente relacionados con Excel y análisis y presentación de datos. Pero en esta entrada haré un excepción para contarles sobre el sitio Widgenie.
La propuesta del sitio es sencilla: crear un "widget" a partir de archivos Excel, o .csv que nos permita publicarlo en nuestra hoja, ya sea un blog o cualquier otro tipo de sitio (Facebook, iGoogle, etc.).
Lo que más me ha resultado atractivo de este sitio es la facilidad de uso.
Por ejemplo, después de haber creado una hoja con los datos de las visitas al blog en los primeros seis meses del año,



quiero publicarla en el blog. Después de haber creado una cuenta en el sitio, me conecto y entro en mi cuenta



Elijo crear un nuevo archivo de datos (data file)



En este caso elegimos el formato de Excel y subimos el archivo al sitio usando la interfaz de importación



Hay que prestar atención a las instrucciones en el cuadro a la derecha de la página, ya que hay ciertas limitaciones

Al apretar el botón Upload, se abre un formulario donde en la pestaña "Information" podemos agregar una descripción de los datos. En la pestaña "Columns" podemos borrar columnas de la tabla que no queremos publicar y finalmente, en la pestaña "Linked Widgets" determinamos la forma de presentación de los datos



Las posibilidades son expuestas en forma gráfica e intuitiva



En mi caso, he elegido el gráfico de columnas animadas, lo que abre el diálogo de diseño. Aquí arrastramos los datos que queremos que aparezcan en sobre el eje de las X (categorías) y los que aparecerán sobre el eje de la Y. Después de arrastrar los datos, aparecerá una imagen previa del gráfico



En el próximo paso podemos agregar títulos al gráfico y a los ejes y también cambiar la orientación (de columnas a barras). Finalmente, pasamos a la pestaña "Publish" donde obtenemos el código para publicarlo (en mi caso, obviamente, "Blogger")



El resultado lopueden ver en la parte inferior de la columna izquierda del blog.

A pesar de la facilidad de uso, existen ciertos inconvenientes. Por ejemplo, al usar los nombres de los meses (en inglés), las columnas del gráfico no aparecen en el orden original de la tabla de los datos. Como pueden ver, en el gráfico que publico en el blog, he reemplazado los nombre de los meses por sus números de orden.
Otro inconveniente es que no parece ser posible el uso de caracteres castellanos como los acentos. De todas maneras, la facilidad de uso hace que recomiende este sitio con entusiasmo.

Technorati Tags: MS Excel