Generar una lista de archivos en Excel

En mi nota sobre como importar una lista de archivos a uno hoja de Excel vimos que podíamos usar las macrofunciones (funciones XLM) para realizar esta tarea.
Uno de mis lectores intentó crear una lista de un directorio que contiene 1600 archivos. El resultado fue que la macrofunción crea una lista de 256 archivos, y partir de allí recibimos #REF como resultado.
Mi sugerencia de usar TRANSPONER tampoco parece dar resultados, así que la solución es usar una macro (Vba) para crear la lista.
Una macro de este tipo fue publicada en el sitio Vbax con las correspondientes explicaciones.
Para los lectores que prefieren las explicaciones en español, procedemos de la siguiente manera:

1 – descargamos la macro en el enlace de arriba o aquí
2 – copiamos la macro a un módulo del editor de Vba (Herramientas—Macro—Editor VBa o Alt+F11)
3 – creamos una referencia al al Windows Script Host Object Model en el menú Herramientas—Referencias del editor de Vba




marcando la casilla al del Windows Script Host Object Model (antes de marcarla, los objetos aparecen en orden alfabético)



4 – Hacemos correr la macro con Herramientas—Macro—Ejecutar



Se abrirá una ventanilla donde elegimos el directorio/carpeta cuyos archivos queremos poner en la lista. La macro genera un nuevo cuaderno con la lista de los archivos y sus propiedades.

Technorati Tags: MS Excel

Cálculo de días específicos con Excel

El cálculo de fechas con Excel es muy flexible y ofrece muchas posibilidades. Para ejemplificar esto, responderé en esta nota a tres preguntas que me hace un lector del Perú:

1.Como podría hacer para calcular cuantos sábados y/o domingos hay en un mes dado el primer y ultimo día.

2.Para saber que día cae un segundo o tercer domingo de un mes X, por ejemplo el día del padre o de la madre.

3.Calcular el día de Pascua (Domingo Santo)


Calcular la ocurrencia de un día determinado.

Para calcular cuantas ocurrencias tiene un día determinado de la semana en un mes (o cualquier otro período) usamos esta fórmula

={SUMA(SI(DIASEM(A2-1+FILA(INDIRECTO("1:"&TRUNCAR(B2-A2)+1));2)=C2;1;0))}

Esta es una fórmula matricial y se pone en la celda apretando simultáneamente Ctrl + Mayúscula + Enter




En la celda A2 ponemos la fecha del primer día del mes/período, en la celda B2 el último día del mes/período, en C2 el número de orden del día de la semana (1 = Lunes, 2= Martes, etc). Es decir si queremos saber cuántos domingos hay en un período, ponemos 7 en la celda C2.

Calcular la fecha de un día determinado, dado el mes y el año.
Si queremos calcular, por ejemplo, la fecha del tercer viernes de abril de este año, usamos esta fórmula

=FECHA(E2;D2;1+((C2-(B2>=DIASEM(FECHA(E2;D2;1);2)))*7)+(B2-DIASEM(FECHA(E2;D2;1);2)))

donde B2 contiene el día buscado (1 = Lunes, 2 = Martes, etc); C2 contiene el número de orden dentro del mes; D2 contiene el mes y E2 el año



También aquí usamos DIASEM con el argumento Tipo = 2.

Calcular la fecha de Pascua con Excel.

Este cálculo es más complicado y mostraré aquí tres posibilidades

1 – una función definida por el usuario (UDF), citada por Chip Pearson


Public Function EasterDate(Yr As Integer) As Date

Dim d As Integer
d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 + _
d + (d > 48) + 1) Mod 7)

End Function

Esta función hay que copiarla a un módulo del editor de Vba, y luego a activa con el asistente de fórmula (categoría: definidas por el usuario)



2 – con la fórmula =MULTIPLO.INFERIOR(FECHA(A3;5;DIA(MINUTO(A3/38)/2+56));7)-34

donde A3 contiene el año

3 – con esta fórmula, citada por John Walkenbach, sobre la cual ni él, y mucho menos yo, tenemos la más mínima idea de cómo funciona

=MONEDA(("4/"&A3)/7+RESIDUO(19*RESIDUO(A3;19)-7;30)*14%;)*7-6

Nuevamente A3 contiene el año.






Technorati Tags: MS Excel

Como convertir números en palabras en Excel en Español

Un compañero de trabajo me preguntó si había alguna función en Excel para convertir números a palabras(expresar los números en letras). Es decir, si el la celda A1 pongo 123,45 en la celda B1 donde pongo la función aparece "ciento veintitrés pesos con cuarenta y cinco centavos". Una rápida búsqueda en Google me llevó a está página de la base de datos de conocimientos de Microsoft sobre Cómo convertir un valor numérico en palabras en Excel en inglés que es una traducción automática del articulo How to convert a numeric value into English words in Excel.
Esta UDF (user defined function = función definida por el usuario) de Microsoft tiene varios problemas:
1 – sólo sirve para convertir números al inglés
2 – sólo usa el dólar como moneda
3 – sólo usa el punto como separador de decimales.

Para adaptar esta función a los usuarios de Excel en español, tuve que introducir varios cambios. Por ejemplo, en la mayoría de los países hispano parlantes se usa la coma como separador de decimales. Pero en Guatemala, Honduras, República Dominicana, México, Nicaragua, Puerto Rico y El Salvador, se usa el punto, como en los Estados Unidos.
También la moneda cambia de acuerdo al país.

Aquí pueden descargar una adaptación de la función para convertir números al español, que toma en cuenta todos estos problemas.



Para usarla hay que copiar el código de la función a un módulo del editor de Vba, por ejemplo en el Personal.xls. Luego podemos usar el asistente de funciones,




Esta versión de la función tiene tres argumentos, la celda que contiene el número (MyNumber), el separador (Sep) y la moneda (Mon)



El resultado:




Technorati Tags: MS Excel