Calcular intervalos en Excel con SIFECHA

Supongamos que la fecha de hoy es el 1/1/2007 y nos preguntamos cuántos días faltan hasta la apertura de los Juegos Olímpico. Si queremos hacer el cálculo con Excel, ponemos la fecha actual en una celda, la fecha de la apertura en otra y en una tercera efectuamos la resta:




Como vemos, 583 días. Si queremos expresar el resultado en términos de años, meses y días, podemos usar la función SIFECHA (DATEDIF en la versión inglesa). Esta función, que es una función total y absolutamente normal de Excel pero no está documentada, excepto en XL2002, a pesar que existe desde la versión 5 de Excel.

La función SIFECHA usa tres argumentos: fecha de inicio, fecha final y tipo de intervalo. Siguiendo con nuestro ejemplo, podemos usar esta fórmula para calcular el intervalo en días

=SIFECHA(A1,B1,"d") que da 583

si queremos calcular la cantidad de meses usamos

=SIFECHA(A1,B1,"m") que da 19, es decir la cantidad entera de meses en el intervalo

y si queremos la cantidad de años

=SIFECHA(A1,B1,"y") que da 1, la cantidad entera de años.

Si queremos calcular la cantidad de días por encima de la cantidad entera de años en el intervalo, usamos el argumento "yd" de la siguiente manera

=SIFECHA(A1,B1,"yd") que da 217, es decir la cantidad de días desde el 01/01/2008 hasta el 06/08/2008

De la misma manera podemos calcular la cantidad entera de meses, por encima de la cantidad de años en el intervalo con

=SIFECHA(A1,B1,"ym") que da 7 (hay que recordar que siempre obtenemos números enteros)

o días no incluidos en la cuenta de los meses

=SIFECHA(A1,B1,"md") que da 5

Como ven, podemos combinar estas fórmulas y exhibir el resultado de la siguiente manera



La función SIFECHA no tiene un argumento que nos permita calcular las semanas. Para esto podemos usar la fórmula

=ENTERO((B1-A1)/7) que da 83

Si queremos expresar el intervalo hasta las Olimpíadas en términos de años, meses, semanas y días, tenemos que usar esta fórmula para calcular las semanas

=ENTERO(SIFECHA(A2;B2;"md")/7), es decir, primero calculamos la cantidad de días "libres" (no incluidos en la cuenta de los meses) y los dividimos por 7.



Si queremos expresar el resultado en una sola celda, incluyendo el texto, podemos usar la concatenación de esta manera

=SIFECHA(A2;B2;"y")&" año "&SIFECHA(A2;B2;"ym")&" meses "&SIFECHA(A2;B2;"md")&" días"

Nuestra querida lengua castellana nos complica un poco las cosas, ya que debemos respetar los singulares y los plurales, para lo cual usaremos la función SI

=SIFECHA(A2;B2;"y")&SI(SIFECHA(A2;B2;"y")=1;" año ";" años ")&SIFECHA(A2;B2;"ym")&SI(SIFECHA(A2;B2;"ym")=1;" mes ";" meses ")&"y "&SIFECHA(A2;B2;"md")&SI(SIFECHA(A2;B2;"md")=1;" día";" días")

Como esta fórmula es un tanto larga y engorrosa, podemos incluirla en un nombre para facilitar la lectura de las celdas en nuestra hoja. Lo que hacemos es poner esta fórmula en un nombre



Una aplicación más práctica de esta función es crear una tabla de antigüedad de empleados, como me pedía uno de mis lectores:



En la celda B1 ponemos la función =HOY(), para obtener el día corriente; en las celdas donde queremos calcular la antigüedad usamos el nombre "lapso_rel" que contiene la fórmula usada en "lapso" con una pequeña pero importante modificación. Como hemos de copiar este nombre a lo largo de un rango, debemos usar referencias relativas a las celdas de la fecha inicial de los empleados, pero la referencia a la fecha corriente debe ser absoluta:

=SIFECHA(Hoja1!B3;Hoja1!$B$1;"y")&SI(SIFECHA(Hoja1!B3;Hoja1!$B$1;"y")=1;" año ";" años ")&SIFECHA(Hoja1!B3;Hoja1!$B$1;"ym")&SI(SIFECHA(Hoja1!B3;Hoja1!$B$1;"ym")=1;" mes ";" meses ")&"y "&SIFECHA(Hoja1!B3;Hoja1!$B$1;"md")

Otra variante sería utilizar la función HOY() dentro de la fórmula, en lugar de ponerla en una celda auxiliar

=SIFECHA(Hoja1!B3;Hoja1!HOY();"y")&SI(SIFECHA(Hoja1!B3;Hoja1!HOY();"y")=1;" año ";" años ")&SIFECHA(Hoja1!B3;Hoja1!HOY();"ym")&SI(SIFECHA(Hoja1!B3;Hoja1!HOY();"ym")=1;" mes ";" meses ")&"y "&SIFECHA(Hoja1!B3;Hoja1!HOY();"md")




Technorati Tags: MS Excel

Exportar datos de Excel a archivo texto.

Una tarea corriente es importar archivos de texto a Excel, para lo cual existen varias posibilidades.
Otra tarea similar es generar un archivo texto para exportar datos de una hoja Excel a una aplicación externa. Algunas de estas aplicaciones aceptan archivos texto con formato .csv (comma separated values); otras aplicaciones exigen archivos texto delimitado con tabulaciones.
En ambos casos usamos el menú Guardar como y elegimos alguna de las opciones de texto




Si elegimos la opción .csv Excel genera un archivo de texto cuyos campos están delimitados por comas. Supongamos esta hoja Excel



Si la exportamos usando la opción .csv obtenemos este resultado



Como pueden ver, un archivo texto cuyos campos están separados por ";".

En los casos de exportar a una aplicación que no acepta este formato, usamos la opción texto delimitado con tabulaciones. El resultado es



El problema surge cuando la aplicación a la cual queremos exportar los datos exige que los campos tengan una longitud determinada. En esta nota mostraremos como crear un archivo texto con campos de longitud determinada por el usuario.

A la hoja Excel que contiene los datos a exportar la llamaremos "BD" (base de datos). Agregamos una fila por encima de la fila 1, de manera que los encabezamientos queden en la fila 2. Ahora ponemos en las celdas correspondientes de la fila 1 el "ancho" deseado para cada campo



El próximo paso consiste en agregar una hoja, que llamaremos "ascii". En celda A1 de esta hoja ponemos esta fórmula

=BD!A3&REPETIR(" ";BD!A$1-LARGO(BD!A3))

Esta fórmula crea un valor compuesto por el contenido de la celda A3 (siendo 3 la primer fila de la hoja BD con datos a exportar) y una cantidad de espacios en blanco hasta completar el largo deseado para el campo. En nuestro caso "Jorge" tiene un largo de cinco, pero el campo "Nombre" tiene que tener 15. Los 10 espacios necesarios son calculados por la expresión BD!A$1-LARGO(BD!A3)
Copiamos esta fórmula a lo largo del rango necesario



Ahora tenemos que guardar esta hoja como archivo texto delimitado con tabulaciones, tal como mostramos más arriba. Es recomendable crear una copia de la hoja usando la opción Mover o copiar (clic con el botón derecho en la pestaña de la hoja) a un libro nuevo y asegurándonos de haber señalado la opción "crear una copia"



Al guardar este nuevo libro, que contiene sólo la hoja con los datos, recibimos este mensaje



Apretamos "Si" y guardamos el libro. El resultado es



Como ven, el ancho de los campos está de acuerdo a nuestras definiciones.

Un problema potencial se presenta cuando el largo del valor de la celda en BD es mayor que el ancho requerido para el campo.
Por ejemplo, si el ancho para el campo "Ciudad" es sólo de 15 caracteres, cuando ponemos Santiago de Chile (17 caracteres) en la hoja BD, obtensmo un resultado de error en la hoja "ascii"



Esto se debe a que la función REPETIR no puede aceptar un número negativo como segundo argumento.
En esto casos tenemos dos opciones:
1 – cambiar el parámetro del ancho del campo (lel valor en la celda C1 en nuestro ejemplo);
2 – cambiar la fórmula para que "recorte" el texto al ancho requerido, desechando los caracteres supernumerarios. Para eso usamos esta fórmula

=SI(LARGO(BD!C5)>BD!C$1;IZQUIERDA(BD!C5;BD!C$1);BD!C5&REPETIR(" ";BD!C$1-LARGO(BD!C5)))

que nos dará como resultado



es decir, recortando Santiago de Chile (17 caracteres) a Santiago de Chi (15 caracteres).

El archivo con el ejemplo esta a vuestra disposición aquí.

Technorati Tags: MS Excel

Validación de datos entre varias hojas Excel – otra variante

El método que propongo en mi nota anterior para validación de datos entre varias hojas de Excel no funciona en algunos casos, como le ha sucedido a uno de mis lectores. No he logrado encontrar una explicación por qué en mi ejemplo el método funciona pero en el archivo de mi lector no.


18/02/2012
Esta entrada ha sido actualizada con esta nota.

Validación de Datos entre varias hojas de Excel.

Ya hemos visto que cuando queremos aplicar validación de datos refiriéndonos a una lista remota (es decir, que no se encuentra en la hoja a la que queremos aplicar validación de datos), tenemos que usar nombres para crear la referencia a la lista.
Un lector me consulta cómo aplicar validación de datos para evitar registros duplicados, teniendo en cuenta los valores que aparecen en varias hojas del cuaderno.

En una breve búsqueda en la Internet encontré esta respuesta de Bob Umlas (un Excel MVP):


Data validation across sheets isn't supported. You may need an event macro with code to do the checking for you as the worksheet changes (no se puede aplicar validación de datos a través de varias hojas. Habría que usar una macro de tipo evento para hacer la comprobación a medida que se van introduciendo cambios en la hoja).

Estaba a punto de ponerme a escribir la macro, cuando decidí intentar hacerlo de todas maneras sin macros.


El resultado es que si se puede. El truco parece residir en no crear ninguna referencia explícita a las hojas del cuaderno. Paso a explicar.

Esta entrada ha sido actualizada en esta nota.

Tabla de amortización con Excel

Varias veces en el pasado he recibido consultas sobre cómo construir una tabla de amortización de préstamos con Excel.

Aclaremos que hay varios sistemas de amortización, y aquí mostraré como construir la tabla de amortización con cuota fija. A quien quiera ver otras posibilidades le recomiendo la página Excel Avanzadode Adolfo Aparicio. Allí podrán descargar un archivo Excel (Prestamos.xls) con muchas variantes de tablas de amortización.

En esta nota veremos como construir un cuadro de amortización de prestamos que sea flexible en cuanto al tipo de plazo (mensual, trimestral, anual, etc.) y la cantidad de plazos. Nuestro modelo se divide en dos partes: el cuadro de datos





y la tabla de amortización



El cuadro de datos contiene los parámetros necesarios para el cálculo: monto/importe del préstamo, tasa de interés anual, período del pago en meses (1: mensual, 3: trimestral, 12: anual) y número de períodos. Así por ejemplo, en un préstamo a tres años con pagos trimestrales el período de pago será 3 y el número de períodos 12.

Las fórmulas en la tabla de amortización son las siguientes:

Pago (columna B): =PAGO($C$4*($C$5/12);$C$6;-$C$3).
La expresión ($C$4*($C$5/12) nos permite expresar la tasa de interés anual en términos del plazo de pago del préstamo.

Cuota del interés (columna C): =PAGOINT($C$4*($C$5/12);A11;$C$6;-$C$3)

Cuota del capital (columna D): =PAGOPRIN($C$4*($C$5/12);A11;$C$6;-$C$3)

Saldo (columna E): =E10-D11, es decir, resta el pago a cuenta del capital del saldo anterior.

Capital amortizado: =D11+F10, el complementario de la la columna anterior.

La tabla contiene 120 filas con fórmulas, es decir sirve para préstamos de hasta 10 años. Para hacer invisibles las filas que están fuera del rango del préstamo, usamos Formato Condicional a lo largo de todas las columnas del cuadro de amortización:



En la fórmula del formato condicional, =$A12>$C$6, hay que prestar atención a la dirección semi-absoluta del primer miembro.

Technorati Tags: MS Excel

Tablas dinámicas en lugar de Autofiltro.

Uno de mis lectores me pregunta si se puede aplicar autofiltro simultáneamente a varias hojas. En sus propias palabras:

¿se puede aplicar autofiltro a varias hojas de una sola vez? Tengo 25 hojas y
quiero filtrar en todas desde una fecha determinada. ej: desde ej 01/06/2007.

La respuesta es no, no se puede. Pero podemos usar tablas dinámicas para lograr efectos parecidos a los de Autofiltro. Para esto usaremos tablas dinámicas con la opción "rangos de consolidación múltiples".

Supongamos un cuaderno Excel (que se puede descargar aquí) con listas de datos compuestas de fechas e importes, en tres hojas. Nuestro objetivo en consolidar los datos en una tabla y mostrar los datos de determinadas fechas.

Construimos la tabla dinámica con rangos de consolidación múltiples








Agregamos las listas de las distintas hojas



Y ponemos la tablas en una hoja aparte (Reporte)



Obtenemos



Ahora empezamos a mejorar el resultado. Eliminamos el elemento Columna arrastrándolo fuera de la tabla y movemos el elemento Página1 a la zona de campos de columna



Reemplazamos los rótulos de la tabla por otros más significativos



Para filtrar los datos según fechas abrimos el menú de ítems del campo



Y seleccionamos los elementos deseados



Este método es muy efectivo si queremos seleccionar sólo una fecha o un rango reducido. Si tenemos muchos elementos (fechas) en nuestras listas el método puede ser muy trabajoso.

Technorati Tags: MS Excel

Presupuesto de viaje con Excel

Desde el 7 al 29 de setiembre estaré de viaje con mis hijas. Pasaremos nuestras vacaciones en la Argentina donde visitaremos Puerto Madryn para hacer el avistaje de ballenas en la península de Valdés, Bariloche y San Martín de los Andes, las Cataratas del Iguazú y por supuesto Buenos Aires, mi ciudad natal.

Cuento todo esto por dos motivos. El primero es que durante esas tres semanas el blog estará inactivo. El segundo es para sugerir cómo se puede construir con Excel un modelo sencillo pero efectivo para programar el presupuesto de un viaje (o de cualquier otra actividad).
Los "ingredientes" de nuestro "plato" serán: tablas dinámicas, validación de datos y rangos dinámicos definidos con nombres y la función DESREF.
También usaremos un evento para validar fechas.

La tabla dinámica funciona como un generador de reportes que nos permitirá agrupar los gastos según los distintos conceptos que hayamos definido (tipo de gastos como vuelos, alojamiento, excursiones, etc.; o gastos por tramos).

Usaremos Validación de Datos para generar listas desplegables en los distintos campos de la lista que será la base de la tabla dinámica.

Aquí pueden descargar el archivo con el ejemplo del presupuesto de viaje.

Empezamos por crear la lista, las base de datos que servirá a la tabla dinámica (en la hoja BD en nuestro ejemplo). Aquí definimos que campos queremos que contenga la tabla. En nuestro ejemplo definimos (empezando por la columna A): Tipo de Gasto, Fecha, Tramo, Detalle, Moneda, Suma, Peso, Dólar, Euro.

Agregamos las últimas cuatro columnas bajo la suposición que tendremos gastos en distintas moneda. En la columna Moneda pondremos la denominación de la moneda en la cual realizamos el gasto (peso, dólar o euro); en las últimas tres convertimos cada suma a su equivalente con fórmulas que mostraremos luego.

En esta hoja hemos agregado también un evento que abre un calendario cuando queremos introducir una fecha en alguna celda de la columna B. Esta técnica la hemos mostrado en la nota sobre validación de fechas en Excel.

Ahora creamos una hoja que llamamos "Parámetros" donde tenemos los valores de tipo de gastos que servirá a la lista desplegable de la validación de datos. Además tenemos un cuadro de cambio cruzado de monedas para las conversiones.



En esta hoja definimos los nombres:

cambio =parametros!$C$2:$F$5
db_range =DESREF(BD!$A$1;0;0;CONTARA(BD!$A:$A);CONTARA(BD!$1:$1))
monedaH=parametros!$C$2:$F$2
monedaV=parametros!$C$2:$C$5
Tipo_de_Gasto=DESREF(parametros!$A$3;0;0;CONTARA(parametros!$A:$A)-1;1)



Antes de crear la hoja con el reporte, introducimos algunos datos en la hoja BD:





Ahora ya podemos crear nuestro reporte, usando una tabla dinámica que ponemos en la hoja Reporte. Por ejemplo si queremos ver el presupuesto por tipo de gasto:



o por tramo



de acuerdo a los campos que arrastremos a la zona de campos de fila.

Technorati Tags: MS Excel

Contar valores únicos en un rango de Excel

En ciertas situaciones necesitamos saber cuantos valores únicos hay en un rango. Supongamos una hoja Excel donde tenemos esta tabla:



Si queremos calcular las ventas promedio por agente, tenemos que saber primero cuántos agentes hay en nuestra lista. En nuestro ejemplo hay cuatro agentes, Pedro, Roberto, Juan Carlos y Alberto, en doce filas de la tabla. Pero en la vida real nos enfrentamos con tablas que tienen cientos o miles de filas y por lo tanto necesitamos una forma más práctica de contar.
Para contar cuantos valores únicos hay en una lista usamos esta fórmula matricial:

={SUMA(1/CONTAR.SI(rango;rango))}

donde rango es un nombre que define el rango de celdas donde queremos contar los valores únicos

En nuestro caso rango = A2:A13, nuestra fórmula será

={SUMA(1/CONTAR.SI($A$2:$A$13,$A$2:$A$13))}



Esta es una buena oportunidad para volver a explicar cómo funcionan las fórmulas matriciales.

En primer lugar recordemos que al introducir estas fórmulas en una celda, apretamos simultáneamente Ctrl+Mayúsculas(Shift)+Enter. Los corchetes aparecen al introducir la fórmula de esta manera, y no deben ser puestos por el usuario.

Para ver los pasos del cálculo de la fórmula matricial, hay que pulsar el botón "Ver Detalle".

En nuestra fórmula matricial, la función CONTAR.SI crea una matriz de resultados que es número de veces que cada nombre aparece, como vemos en la columna B donde hemos introducido esta formula (no matricial)
=CONTAR.SI($A$2:$A$13,A2)



En la columna C calculamos la inversa de los valores de la columna B, por ejemplo en C2 ponemos la fórmula =1/B2

Luego sumamos todos los valores de la columna C, lo que nos da el número de valores únicos en el rango.

Todo esto es hecho en una sola fórmula, usando la técnica de fórmulas matriciales. El archivo con el ejemplo se puede descargar aquí.

Technorati Tags: MS Excel