Contar cantidad de días laborales entre dos fechas considerando los sábados con Microsoft Excel

En post anteriores se mostró como se realiza el cálculo de la cantidad de días laborales entre dos fechas utilizando la función DIAS.LABS que calcula la cantidad de días entre dos fechas sin incluir los sábados ni domingos, y en forma opcional los festivos.

El problema de esta función interpreta al sábado como un día no laborable, y como en muchas empresas se trabajan los sábados (ya sea medio horario o jornada completa) se estarían contando la cantidad de días en forma incorrecta.

Aquí se mostrará como calcular la cantidad de días laborales entre dos fechas considerando al sábado como un día laborable.

Para comenzar se recomienda leer un post anterior que muestra como calcular la cantidad de días, entre caso interesa el sábado, entre dos fechas.

Uniendo lo explicado en post anteriores, se calculan la cantidad de días laborales de lunes a viernes, a los que se le suman la cantidad de sábados.

Calcular cantidad de días laborales incluyendo sábado entre dos fechas:

=DIAS.LAB(B2; B3) + TRUNCAR((B3 - B2 - DIASEM(B3 - 6) + 8) / 7)

Y si la jornada del sábado es de medio horario, se deberá de dividir la cantidad de sábados entre 2.

Calcular cantidad de días laborales incluyendo medio día del sábado entre dos fechas:

=DIAS.LAB(B2; B3) + (TRUNCAR((B3 - B2 - DIASEM(B3 - 6) + 8) / 7) / 2)

Si la cantidad de horas del sábado no es exactamente a la mitad, en lugar de dividir entre dos se divide por la cantidad de horas de una jornada completa y se multiplica por la cantidad de horas laborables el sábado.

Quitar caracteres como Enter y Tabulado

Varios usuarios han reportado problemas al generar un reporte, mediante una consulta SQL, donde el resultado de cada fila lo entrega en dos o más líneas. También las columnas quedan desplazadas, apareciendo filas con más columnas que las debidas.

La mayoría de las veces este problema se debe a que un dato extraído en el reporte contiene los caracteres de control Enter y/o Tabulado.

Para evitar este problema se debe de reemplazar el o los caracteres de control por un espacio o cualquier otro carácter (puede ser incluso carácter nulo).

El Enter está formado por un salto de línea (carácter 13) y un retorno de carro (carácter 10). Para quitarlo se debe de reemplazar esta secuencia de caracteres por un espacio o cualquier texto que se desee.

El Tabulado es representado con el carácter número 9, por lo que para quitarlo se reemplaza por un espacio o el carácter deseado.

Quitar caracteres de control (Enter y Tabulado) de columnas en una consulta con Oracle:

select datos_sin_problema, replace(nombre_del_campo, chr(13)||chr(10), ' ') dato_sin_enter, replace(nombre_del_campo, chr(9), ' ') dato_sin_tabulado, replace(replace(nombre_del_campo, chr(13)||chr(10), ' '), chr(9), ' ') dato_sin_enter_ni_tabulado from nombre_de_tabla;

En esta consulta se extraen el o los datos sin problemas, y los datos con problemas se extraen en 3 formas: quitando solo el Enter, quitando solo el Tabulado y por último quitando Enter y Tabulado. Se utilizan las funciones REPLACE para sustituir los caracteres, y CHR para obtener la representación del carácter. Los caracteres se concatenan con el operador ||.

Quitar caracteres de control (Enter y Tabulado) de columnas en una consulta con Microsoft Access:

select datos_sin_problema, replace(nombre_del_campo,chr(13) + chr(10), ' ') as dato_sin_enter, replace(nombre_del_campo,chr(9), ' ') as dato_sin_tabulado, replace(replace(nombre_del_campo, chr(13) + chr(10), ' '), chr(9), ' ') as dato_sin_enter_ni_tabulado from nombre_de_tabla;

Al igual que en Oracle, esta consulta se extraen el o los datos sin problemas, y para los datos que tienen problemas se extraen en 3 formas: solo Enter, solo Tabulado y por último Entero y Tabulado.

Se utilizan las funciones REPLACE para sustituir los caracteres, y CHR para obtener la representación del carácter. La diferencia es que el texto se concatena con el operador +.

La solución presentada corrige el problema, pero es preferible evitarlo. Antes que nada se debe de analizar si el campo en cuestión debería o no almacenar estos caracteres de control. Por ejemplo si se trata de nombre del cliente no lo debería tener, pero si se trata de observaciones es válido que lo tenga.

En caso de que no sea válido, es preferible corregir el problema de raíz. Si el sistema con que se introducen los datos permite caracteres de control, se recomienda modificar dicho sistema de manera de que antes de grabar se realice la “limpieza” del texto.

Una vez controlado el ingreso de los datos, se deberá corregir los datos que ya están almacenados con caracteres de control, para esto se utiliza una simple sentencia UPDATE con las funciones antes mostradas.

Quitar caracteres de control (Enter y/o Tabulado) con Oracle:

Quitar el carácter Enter con Oracle:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(13)||chr(10), ' ') where nombre_del_campo like '%' || chr(13)||chr(10) || '%';

Quitar el carácter Tabulado con Oracle:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(9), ' ') where nombre_del_campo like '%' || chr(9) ||'%';

Quitar ambos caracteres:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, replace(nombre_del_campo, chr(13)||chr(10), ' '), chr(9), ' ') where nombre_del_campo like '%' || chr(9) ||'%' or nombre_del_campo like '%' || chr(13)||chr(10) || '%';

Quitar caracteres de control (Enter y/o Tabulado) con Microsoft Access:

Quitar el carácter Enter con Microsoft Access:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(13) + chr(10), ' ') where nombre_del_campo like '*' + chr(13) + chr(10) + '*';

Quitar el carácter Tabulado con Microsoft Access:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(9), ' ') where nombre_del_campo like '*' + chr(9) + '*';

Quitar ambos caracteres con Microsoft Access:

update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(9), ' ') where nombre_del_campo like '*' + chr(9) + '*' or nombre_del_campo like '*' + chr(13) + chr(10) + '*';

Si se desea quitar algún otro carácter de control, primero se deberá de identificar de cual carácter se trata y seguir los pasos presentados.

Calcular horas laborales entre dos horas considerando horario de receso

Como complemento del artículo anterior, donde se muestra como calcular la cantidad de horas laborales entre dos horas de misma o diferentes fechas, se incluye una mejora en la que contempla un receso en el medio de la jornada laboral.

Partiendo del proceso ya mostrado los cambios para no incluir en el resultado final el tiempo del descanso intermedio son los siguientes:

Para el caso en que el inicio y fin de la tarea se den en el mismo día, se deberá identificar si existe intersección en el horario de la tarea y el horario del receso, si existe, al resultado final se le deberá restar el tiempo transcurrido desde la hora mayor entre la hora de inicio de la tarea y la de hora de inicio del receso hasta la hora menor entre el final de la tarea y el final del receso.

De lo contrario, cuando la hora de inicio y fin se den en distintos días, se deberán realizar 3 cálculos adicionales:

1. Si la hora de inicio de la tarea es menor o igual a la hora de inicio del período de descanso, al resultado final se le deberá restar el tiempo transcurrido desde la hora mayor entre el inicio de la tarea y el inicio del descanso hasta el final del descanso.

2. Para los días laborales completos intermedios, la duración de la jornada laboral se deberá modificar de manera que no incluya el período de descanso (en el ejemplo anterior eran 10 horas de trabajo, si el descanso es de 1 hora, en este nuevo ejemplo serán 9 horas laborales).

3. Si la hora de cierre de la tarea es mayor o igual a la hora de fin del período de descanso, al resultado final se le deberá restar el tiempo transcurrido desde la hora del fin del descanso hasta la hora menor entre el cierre de la tarea y el fin del descanso y el final del descanso.

La fórmula en Microsoft Excel para el cálculo de las horas laborales entre dos horas considerando tiempo de receso es la siguiente:

=SI($A2=$C2;SI(DIAS.LAB($A2;$C2;DIAS_FESTIVOS)=1;MIN($D2;HH_FIN)-MAX($B2;HH_INI)-SI(Y($B2<HH_RES_F;HH_RES_I<$D2);MIN($D2;HH_RES_F)-MAX($B2;HH_RES_I);0);0);SI(Y(DIAS.LAB($A2;$A2;DIAS_FESTIVOS)=1;$B2<=HH_FIN);HH_FIN-MAX($B2;HH_INI)-SI($B2<=HH_RES_I;HH_RES_F-MAX($B2;HH_RES_I);0);0)+(MAX(DIAS.LAB($A2+1;$C2-1;DIAS_FESTIVOS);0)*HH_JOR)+SI(Y(DIAS.LAB($C2;$C2)=1;$D2>=HH_INI);MIN($D2;HH_FIN)-HH_INI-SI($D2>=HH_RES_F;MIN(HH_RES_F;D2)-HH_RES_I;0);0))

Las referencias utilizadas contienen:
A2 y B2: Fecha y hora del inicio de la tarea.
C2 y D2: Fecha y hora del final de la tarea.
HH_INI: Hora de inicio de la jornada laboral
HH_FIN: Hora de fin de la jornada laboral.
HH_JOR: Duración de la jornada laboral.
HH_RES_I: Hora de inicio del receso intermedio.
HH_RES_F: Hora de fin del receso intermedio.
DIAS_FESTIVOS: Lista de días festivos no laborables.

El fichero con el ejemplo actualizado se encuentra aquí.

Calcular horas laborales entre dos horas de igual o distintas fechas

En su momento se mostró como calcular la cantidad de días laborales entre dos fechas, ahora se mostrará como calcular las horas laborables entre dos horas de la misma o distintas fechas.

Por ejemplo si la jornada laboral es de lunes a viernes de 9:00 hs a las 19:00 hs, llega una petición a las 10:00 hs y se resuelve a las 14:00 hs el plazo de atención es de 4 horas. Si la solicitud del trabajo llega a las 18:00 hs de un viernes y se finaliza a las 10:00 hs del lunes siguiente la misma ha durado solo 2:00 hs de duración.

Para complicar un poco más la situación se permite el ingreso de las peticiones antes de la hora laboral, y el cierre después de la misma (aunque esta última parte no tiene mucho sentido).

Como caso particular, si la solicitud y la resolución del trabajo se realizó el mismo día, la duración se calculará como el tiempo transcurrido entre las siguientes horas:

HoraFin = Hora mínima entre la hora final de la jornada laboral y la hora de cierre del trabajo.
HoraInicio =Hora máxima entre la hora inicial de la jornada laboral y la hora de solicitud del trabajo.
Tiempo = HoraFin - HoraInicio

Y para el caso de que se resolvió en otra fecha, la siguiente parte del problema se separará en tres:

1. Tiempo transcurrido desde la máxima hora entre la solicitud del trabajo y el inicio de la jornada laboral, hasta el final de la jornada laboral. Esto aplica solo si el día es laborable, y la petición llegó dentro de la jornada laboral.

2. Cantidad de días laborables entre el siguiente día en que se recibió la solicitud del trabajo y el día anterior al que se finalizó multiplicado por la cantidad de horas de la jornada laboral.

3. Tiempo transcurrido desde el inicio de la jornada laboral hasta la hora mínima entre el cierre del trabajo y el final de la jornada laboral. Esto aplica solo si el día es laborable, y la petición se cerró dentro de la jornada laboral.

Formula para el cálculo de horas laborales con Microsoft Excel:

Suponiendo que en A2 y B2 se encuentran la fecha y hora de inicio respectivamente, y en C2 y D2 la fecha y hora de fin respectivamente, la formula, un poco compleja, es la siguiente:

=SI($A2=$C2;SI(DIAS.LAB($A2;$C2)=1;MIN($D2;HORANUMERO("19:00"))-MAX($B2;HORANUMERO("09:00"));0);SI(Y(DIAS.LAB($A2;$A2)=1;$B2<=HORANUMERO("19:00"));HORANUMERO("19:00")-MAX($B2;HORANUMERO("09:00"));0)+(MAX(DIAS.LAB($A2+1;$C2-1);0)*HORANUMERO("10:00"))+SI(Y(DIAS.LAB($C2;$C2)=1;$D2>=HORANUMERO("9:00"));MIN($D2;HORANUMERO("19:00"))-HORANUMERO("9:00");0)) Se recomienda ver el uso de la función DIAS.LAB (la cual se debe de activar).

La función HORANUMERO convierte una hora en formato de texto a un tipo de dato fecha/hora.

Como versión mejorada se pueden definir celdas para la hora de inicio de la jornada laboral (HH_INI), la hora de fin (HH_FIN), y su duración (HH_JOR). También aprovechar la función de días laborales para que también tome en cuenta los festivos, par lo cual se le asigna como tercer parámetro los días laborales que apliquen.

La formula queda así:

=SI($A2=$C2;SI(DIAS.LAB($A2;$C2;DIAS_FESTIVOS)=1;MIN($D2;HH_FIN)-MAX($B2;HH_INI);0);SI(Y(DIAS.LAB($A2;$A2;DIAS_FESTIVOS)=1;$B2<=HH_FIN);HH_FIN-MAX($B2;HH_INI);0)+(MAX(DIAS.LAB($A2+1;$C2-1;DIAS_FESTIVOS);0)*HH_JOR)+SI(Y(DIAS.LAB($C2;$C2)=1;$D2>=HH_INI);MIN($D2;HH_FIN)-HH_INI;0)) En el siguiente link se encuentra disponible un archivo Microsoft Excel que contiene el desarrollo de ambas formulas.

Como una versión mejorada se muestran los cambios necesarios para que en la sumatoria de las horas no incluya el tiempo correspondiente al descanso intermedio en la jornada laboral.

Se recomienda ver una versión mejorada que contempla un receso intermedio.

Identificar o eliminar repetidos con Microsoft Access

Como complemento a la publicación anterior, donde se muestra como identificar registros repetidos en hojas de cálculo, se dearrollaron técnicas similares para bases de datos. Este es el turno de Microsoft Access.

Como ejemplo se utilizará una supuesta tabla "nombre_tabla", que contiene al menos los campos "campo1", "campo2" y "campo3", y se considera que un registro está duplicado cuando estos tres campos son iguales.

Esta operación no se pude realizar directamente con Microsoft Access (o se desconoce) pero si se logra realizando un par de pasos intermedios.

Primero se debe de crear un nuevo campo al que se le llamará "rownum" de tipo Autonumérico (se selecciona la tabla "nombre_tabla", se presiona Vista de Diseño, y se agrega el campo al final).

Al confirmar la operación, Microsoft Access creará la nueva columna "rownum" y la completará con números del 1 hasta la cantidad de filas que tenga la tabla.

Luego se debe de crear una consulta de actualización sobre esta tabla (dentro de las consultas, se selecciona nueva consulta en vista de diseño, se selecciona la tabla "nombre_tabla" o cualquier otra, se presiona el botón SQL, y se cambia lo que está escrito por la siguiente sentencia:

delete from nombre_tabla where nombre_tabla.rownum > ( select min(nombre_tabla_2.rownum) from nombre_tabla nombre_tabla_2 where nombre_tabla_2.campo1 = nombre_tabla.campo1 and nombre_tabla_2.campo2 = nombre_tabla.campo2 and nombre_tabla_2.campo3 = nombre_tabla.campo3);

Esta sentencia borrará los registros que para iguales campos 1 al 3 tengan el valor de rownum mayor al mínimo.

Al ejecutarla se deberá confirmar la operación (indicará la cantidad de registros borrados).

En caso de que no se desee borrar los registros duplicados, sino solamente marcarlos se deberá de crear otro campo adicional por ejemplo de tipo texto con largo 2, en este ejemplo con nombre "EsRepetido".

Al crear el campo, si no se especifica un valor predeterminado para los registros ya existentes, Microsoft Access le asigna el valor nulo. Si se desea se le puede asignar como valor predeterminado el texto No. O de lo contrario ejecutar la siguiente consulta de actualización:

update nombre_tabla set EsRepetido = 'No';

Aquí se está inicializando el atributo que indica el registro está o no repetido.

Posteriormente se ejecuta la siguiente consulta de actualización para marcar los repetidos.

update nombre_tabla set EsRepetido = 'Si' where nombre_tabla.rownum > ( select min(nombre_tabla_2.rownum) from nombre_tabla nombre_tabla_2 where nombre_tabla_2.campo1 = nombre_tabla.campo1 and nombre_tabla_2.campo2 = nombre_tabla.campo2 and nombre_tabla_2.campo3 = nombre_tabla.campo3);

Esta actualización utiliza el mismo criterio que el borrado.

En el caso que se requiera marcar los no repetidos se logra con la siguiente consulta de actualización:

update nombre_tabla set = 'No' where nombre_tabla.rownum = ( select min(nombre_tabla_2.rownum) from nombre_tabla nombre_tabla_2 where nombre_tabla_2.campo1 = nombre_tabla.campo1 and nombre_tabla_2.campo2 = nombre_tabla.campo2 and nombre_tabla_2.campo3 = nombre_tabla.campo3);

Para este caso se marcan como no repetidos aquellos registros que su "rownum" sea igual al mínimo que existe entre todos los registros que tengan mismo campos 1 al 3.

El nuevo campo "rownum" se puede eliminar o dejarlo para posteriores depuraciones.

Validación con Listas desplegables: información dinámica, en otras hojas y más con Excel o similar

Cuando se requiere que ingresar información en una planilla de cálculo los datos sean seleccionados de una lista desplegable se debe de realizar una validación del tipo lista, esta actúa tanto como ayuda para el ingreso datos como para validar la calidad de los mismos.

En la web hay muchas preguntas sobre como realizar estas listas con valores dinámicos dependiendo del contenido de otra celda (ej. si se están ingresando países y ciudades, al mostrar la lista de ciudades muestre solo las del país antes ingresado), como hacer cuando la información a validar no se encuentra en la propia hoja o libro, evitar valores repetidos, y otras más; muchas de estas no están respondidas.

Este artículo pretende ser una guía de como realizar estas listas intentando abarcar todas las posibilidades.

Está basado en Microsoft Excel, también se mencionará como hacerlo en OpenOffice.org Calc donde la operativa es más permisible. No se incluye información sobre Google Docs ya que este tipo de validación no está disponible.

El contenido es el siguiente:

1. Listas de validación básicas en la misma hoja
2. Listas de validación con origen de datos en otra hoja del mismo libro.
3. Listas de validación con origen de datos en otra hoja de otro libro.

Como se está publicando a medida en que se desarrolla cada capítulo, proximamente estarán disponible la continuación que incluye:

4. Quitar valores repetidos.
5. No incluir espacios en blanco.
6. Listas de validación con información variable según valor de otra celda.
7. Listas de validación con información variable según valor de otra columna.

Si hay algún otra parte que consideras que se deba de incluir, se agradecerá hacerlo llegar mediante comentarios o correo electrónico.

Listas de validación con origen de datos almacenados en otro libro

En el capítulo anterior de la serie validación con listas desplegables se mostró como realizar una validación utilizando listas con origen de datos en otra hoja del mismo libro que los datos a validar, ahora se mostrará una variante para cuando la lista a validar se encuentra en otro libro.

Se trabajará con un ejemplo de una lista de personas donde en la primer columna se tendrá el nombre, en la segunda el país de residencia y por último la ciudad donde vive.

La información sobre los países y las ciudades se validará mediante una lista de manera que el dato a ingresar sea mostrado mediante listas predefinidas.

La lista de los países válidos se encuentra en un segundo libro que tiene una hoja llamada "Lista" donde en el rango I3:I6 de la hoja, y la lista de las ciudades en el rango K3:K33.

El fichero con el ejemplo con la lista de validación se encuentra aquí, y el que tiene la lista de los datos aquí. Se deberán de bajar ambos ficheros y dejarlos en una misma carpeta para que funcione correctamente.

Para comenzar se deben tener abiertos ambos libros, y estar en el mismo grupo de libros abiertos.

Desde el archivo donde se encuentran las listas a validar se deberán de crear los nombres de rangos de celdas que apunten al otro libro, esta vez en la referencia se deberá de anteponer el nombre del archivo donde se encuentran los datos a validar.

Al igual que en el capítulo anterior acceder a definir los nombres de las referencias (desde Insertar / Nombre / Definir), para facilitar la operación, presionar el botón que se encuentra a la derecha del "Se refiere a" donde aparecerá una pequeña ventanita, pasarse al siguiente libro con Alt+Tab, seleccionar el rango de países, y volver a presionar el botón de la derecha.

Paso siguiente se deben de definir las listas de validación para cada columna, primero seleccionar la columna correspondiente a los países, elegir la validación tipo lista (en Datos / Validación).

Si se asigna en "Origen" el nombre del rango correspondiente a la lista de países Excel no lo permitirá, emitiendo el siguiente error: "No se pueden usar referencias a otros libros u hojas de cálculo para criterios de Validación de datos".

Por lo que se deberá de utilizar la función INDIRECTO para que pase la validación "engañándo" a Excel.

Esta solución es muy similar a la tercer variante del ejemplo del capítulo anterior. La segunda también es válida, donde a la función indirecto se le pasa la dirección como tal, pero se considera mejor la opción con nombre de referencia ya que si realizan modificaciones sobre el rango de origen ésta se actualiza sola.

Como punto en contra de esta solución es que al utilizar la función INDIRECTO el libro con el origen de los datos debe de estar abierto, de lo contrario al actualizar la información retorna un error.

Realizar los mismos pasos para las ciudades, tanto el rango de datos como la validación.

Listas de validación con origen de datos almacenados en otra hoja

En el capítulo anterior de la serie validación con listas desplegables se mostró como realizar una validación utilizando listas básicas donde los datos que la componen se encuentran en la misma hoja, ahora se mostrará como hacer para crear las listas cuando los datos se encuentran en otra hoja del mismo libro.

Al momento de definir el origen de datos que componen las listas de validación, Microsoft Excel verifica que la referencia a estos datos estén en la misma hoja de cálculo que la celda a validar, por lo que en principio no se puede almacenar los datos de las listas en otra hoja (por ejemplo Lista).

Al verificar la referencia, Microsoft Excel revisa que en la misma no incluya el nombre de hoja de cálculo o libro, si lo detecta lo impide y presenta el siguiente error:

Para evitarlo, la idea es lograr escribir una referencia a un rango que apunte a otra hoja sin poner el nombre de la hoja.

Aquí se mostrarán 3 formas de como validar datos mediante una lista desplegable donde su origen se encuentre en otra hoja de cálculo del mismo libro.

Se trabajará con un ejemplo de una lista de personas donde en la primer columna se tendrá el nombre, en la segunda el país de residencia y por último la ciudad donde vive (hoja Datos).

La información sobre los países y las ciudades se validará mediante una lista de manera que el dato a ingresar sea mostrado mediante listas predefinidas.

La lista de los países válidos se encuentra en la hoja Lista en el rango I3:I6 de la hoja, y la lista de las ciudades en el rango K3:K33.

El fichero con el ejemplo completo está disponible aquí.

La primer forma de "engañarlo" es crear un nombre de rango y hacer referencia a éste en la casilla de validación.

Para esto se debe de seleccionar el rango con los datos a desplegar, y luego la opción "Nombre" del menún "Insertar" y allí seleccionar "Definir ...".

Allí se debe definir un nombre, en este ejemplo LISTA_PAISES, y donde contiene "Se refiere a" debe de estar la referencia del rango seleccionado (deberá aparecer con referencias absolutas, e incluir el nombre de la hoja).

Presionar Agregar para confirmar el nombre.

Paso siguiente se selecciona las celdas donde se ingresarán los países (de la misma forma que en el caso anterior), pero en la opción "Origen" se debe de ingresar el nombre del rango definido como

=LISTA_PAISES

Como segunda forma se puede utilizar la función INDIRECTO. Esta función convierte un texto que contiene una referencia a una celda o rango en una referencia como tal.
Aquí se debe de escribir la la dirección del rango completo en modo de texto.

En Origen se debe de ingresar:

=INDIRECTO("Lista!$A$2:$A$5")

Lo malo de esta forma es que si hay cambios en la referencia tales como cambio del nombre de la hoja, se inserta una columna antes de la A, o se insertan filas la dirección no será recalculada automáticamente y quedará apuntando a una lista errónea.

Y la tercer forma es la combinación de las anteriores: utilizando la función INDIRECTO a la que se le pasa como parámetro el nombre del rango en forma de texto.

=INDIRECTO("LISTA_PAISES")

Esta versión no presenta el problema antes mencionado ya que se trata de un nombre de rango que es modificado automáticamente por Microsoft Excel cuando se realiza un cambio.

No cabe deudas que la primer opción es la mejor y más sencilla, las otras variantes se muestran debido a que en los siguientes capítulos se van a utilizar.

Hacer el mismo procedimiento para la lista de ciudades, esta vez definiendo al rango C2:C32 como LISTA_CUIDADES.

Para la primer opción en Origen se tiene que definir:

=LISTA_CUIDADES

Para la segunda opción en Origen se tiene que definir:

=INDIRECTO("Lista!$C$2:$C$32")

Y para la tercer opción en Origen se tiene que definir:

=INDIRECTO("LISTA_CUIDADES")

Listas de validación básicas en la misma hoja con Excel

Este artículo es el primer capitulo de la serie Listas desplegables

Para comenzar se mostrará un ejemplo básico de como realizar la validación tipo lista tal como lo contempla Microsoft Excel, el objetivo es conocer la operativa y las limitantes de la herramienta.

trabajará con un ejemplo de una lista de personas donde en la primer columna se tendrá el nombre, en la segunda el país de residencia y por último la ciudad donde vive. La información sobre los países y las ciudades se validará mediante una lista de manera que el dato a ingresar sea mostrado mediante listas predefinidas.

La lista de los países válidos se encuentra en el rango I3:I6, y la lista de las ciudades en el rango K3:K33.

El fichero con el ejemplo completo está disponible aquí.

Los pasos para realizar una lista de selección son los siguientes:

Seleccionar el rango de celdas, o columna completa, donde se ingresará y validará la información de los países, para el ejemplo se seleccionará el rango C3:C30.

Acceder a la opción "Validación ..." del menú "Datos".
Allí se debe de definir el Criterio de validación, para esto se deberá seleccionar "Lista" en la opción Permitir. En la opción Origen se debe de introducir el rango de las datos que se desean mostrar en la lista, en este caso se trata del rango $I$3:$I$6 (el rango debe de estar definido mediante referencias absolutas). Se deberá dejar seleccionada la opción "Celda con lista desplegable".

Si se desea, en la pestaña "Mensaje entrante" se puede ingresar un mensaje que muestra un comentario al momento de seleccionar la celda para ingresar la información. Si no se ingresa nada no muestra ningún comentario.

También como opcional, en la pestaña "Mensaje de error" se puede personalizar un mensaje de error para el caso de que se ingrese un dato no valido, en este caso un país que no se encuentra en la lista (o está mal escrito). De no definirse, al ingresar un dato erróneo aparecerá un mensaje genérico.

Con estos pasos la lista ya está definida, ahora al hacer clic sobre una celda para ingresar los países aparecerá una lista con los países válidos.

Los comentarios ingresados se verán de la siguiente manera:

Aunque se tenga la lista para el ingreso de datos, es permitido ingresar el valor digitándolo directamente, aquí es donde entra a tomar sentido la validación, en caso de que el valor ingresado no esté dentro de la lista, y se haya definido un mensaje de error el se vería de la siguiente manera:

Hay que tener en cuenta de que si en la lista seleccionada existen datos duplicados, al desplgarse los datos también estarán repetidos (más adelante se mostrará como evitarlo). Si la lista original cuenta con celdas nulas, mostrará los mismos renglones vacíos en la lista.

Ahora si se desea mostrar una segunda lista con las ciudades, se deberá realizar el mismo proceso utilizando la lista de ciudades. El principal con la restricción que no se puede desplegar solamente las ciudades que pertenezcan al país seleccionado en la misma fila, se mostrarán todas.

En el ejemplo se deberá por error se podrá ingresar como país Colombia y ciudad Buenos Aires. También hay problemas con ciudades de diferentes países que tienen el mismo nombre como ser Córdoba que está en España, México y Argentina. En la lista aparecerá tres veces.

Si se intenta introducir un origen de datos de datos válidos que se encuentre en una hoja de cálculo diferente a donde se encuentra la o las celdas a validar, Microsoft Excel no lo permite.

Desglosar dinero en billetes y monedas de diferentes denominaciones con Excel o similar

Cuando se tiene, por ejemplo, pagar nómina de contado es necesario conocer cuantos billetes y monedas de diferentes denominaciones se requieren para solicitarlos en el banco de manera de poder pagar con el monto exacto a cada uno.

El proceso es el siguiente:

- En una hoja de cálculo se deberá de poner en una columna el importe que se quiere desglosar y tantas columnas como diferentes denominaciones de la moneda corriente existan, desde el billete de mayor valor hasta la moneda de menor valor. Como título de estas columnas se pondrá el propio valor y debe de ser en formato numérico ya que se va a utilizar para realizar cálculos.

- El propio cálculo se separará en dos condiciones: uno para el billete de mayor denominación y otro para el resto.

- Para el de mayor denominación simplemente el importe se divide por el valor del billete, y se trunca el resultado de manera de tener la cantidad de billetes necesarios. A nivel de fórmulas, queda de la siguiente manera:

=TRUNCAR($B2/C$1)

Esta fórmula se encuentra en C2, en B2 el importe y en C1 el valor del billete. Se debe de copiar para todos los importes a distribuir.

Para el resto de denominaciones la formula cambia ya que antes de hacer la operación arriba mostrada se debe de restar al importe original lo ya distribuido.
La formula para el segundo billete de mayor dominación es la siguiente:

=TRUNCAR(REDONDEAR(($B2-SUMAPRODUCTO($C2:C2*$C$1:C$1))/D$1;5))

Donde:

B2 es el importe a procesar, nótese que tiene referencia mixta, se fija la columna importe y se deja libre la fila.
En D1 se encuentra el valor del billete, también tiene referencia mixta pero esta vez queda libre la columna para poder cambiarse de nominación al copiar la celda y siempre fija la fila 1.
El rango $C$1:C$1 contiene los valores de las nominaciones anteriores al que se está procesando, para este caso solo la columna C. El inicio del rango siempre será fijo, y en para el final se irá variando la columna de manera de que al copiar se extienda el rango a todas las nominaciones anteriores. Para la siguiente será $C$1:D$1.
El rango $C2:C2 contiene las cantidades ya distribuidas.
Con SUMAPRODUCTO se irá acumulando el producto de ambos rango celda a celda: C1*C2 + D1*D2, de esta manera se obtiene lo ya distribuido.
Hay una función REDONDEAR que parece estar de más, pero es para evitar problemas de precisión de Excel (cuando el resultado esperado es 30 puede que realmente retorne 29,9999999), se redondea a 5 decimales de manera de no interferir con la operativa.

Esta función se debe de copiar para el resto de las nominaciones y todos los importes a distribuir.

Al final se suman todas las cantidades de cada denominación de manera de saber el total requerido.

Desglosar dinero en billetes y monedas de diferentes denominaciones con Microsoft Excel

Las formulas son las utilizadas en la muestra del proceso.

En C2 introducir esta fórmula para el cálculo de la cantidad de billetes con denominación mas alta:

=TRUNCAR($B2/C$1)

En D2 introducir esta fórmula para el cálculo de la cantidad de billetes con la segunda denominación más alta:

=TRUNCAR(REDONDEAR(($B2-SUMAPRODUCTO($C2:C2*$C$1:C$1))/D$1;5))

Copiarla para el resto de billetes y monedas.

Aquí se encuentra disponible un archivo de ejemplo utilizando Euros que contiene dos hojas: en la primera un detalle donde se desglosan los importes y en la segunda un resumen con la información total.

Desglosar dinero en billetes y monedas de diferentes denominaciones con OpenOffice.org Calc

Las formulas a utilizar son las siguientes:

En C2 introducir esta fórmula para el cálculo de la cantidad de billetes con denominación mas alta:

=TRUNC($B2/C$1)

En D2 introducir esta fórmula para el cálculo de la cantidad de billetes con la segunda denominación más alta

=TRUNC(ROUND(($B2-SUMPRODUCT($C2:C2*$C$1:C$1))/D$1;5))

Copiarla para el resto de billetes y monedas.

Al igual que en Excel, aquí se encuentra disponible un archivo de ejemplo en OpenOffice.org Calc utilizando euros que contiene dos hojas: en la primera un detalle donde se desglosan los importes y en la segunda un resumen con la información total.

Escribir números con letras con Oracle

Siguiendo la misma lógica que se utilizó para escribir un número con letras con planillas de cálculo, aquí se mostrará como realizar esta misma operación con Oracle.

Como primer paso se debe de crear la tabla con los nombres de los números 0 al 999, ésta se llamará nombres_de_numeros y tendrá la siguiente estructura:

CREATE TABLE NOMBRES_DE_NUMEROS ( NUMERO NUMBER(3) NOT NULL, NOMBRE VARCHAR2(50) NOT NULL );

Se definirá una clave primaria:

ALTER TABLE NOMBRES_DE_NUMEROS ADD ( CONSTRAINT PK_NOMBRES_DE_NUMEROS PRIMARY KEY (NUMERO) USING INDEX );

Esta tabla se puede configurar para que se almacene en el KEEP de manera de que Oracle la mantenga en memoria para mejorar el rendimiento.

Una vez creada la tabla se deberá cargar la información con los nombres de los números de 0 al 999 disponible aquí.

Consulta SQL para escribir un número con letras en Oracle:

Como se han presentando errores en la edición de este artículo, el código SQL se encuentra disponible aquí.Cambiar la consulta sobre dual por la tabla donde se encuentren los números a convertir.

Si esta operación se requiere con alta frecuencia, analizar la posibilidad de crear una función Oracle que realice la conversión de manera evitar accesos repetidos y simplificar la sintaxis.

Escribir o convertir números a letras

Una necesidad frecuente es escribir un número con letras (convertir número en letras), las planillas de cálculo al igual que los manejadores de base de datos no disponen de una herramienta para dicha tarea, pero es factible realizarla.

En la web hay disponibles diversos complementos y macros en Visual Basic para el caso de Microsoft Excel, y ejemplos con funciones, también para Microsoft Excel que utilizando columnas intermedias. El código de estas funciones se puede convertir al lenguaje que se requiera.
Independientemente del lenguaje con que se implemente en grandes rasgos el criterio para escribir un número con letras es el siguiente:

• Tomar la parte entera del número.
• Considerar la casuística especial para el cero.
• Separar las cifras en miles (grupos de a 3 pociones a partir de la derecha), se tendrán tantos grupos como se deseen implementar, para números normales pueden tomarse: miles de millones, millones, miles, y unidades. La cifra de cada grupo se debe de tratar de la misma manera por lo que la misma funcionalidad utilizada en una es válida para el resto.
  
  • Ver casuísticas especiales como ser el cien.
  • Tomar el valor de la centena de una lista predefinida (ciento, doscientos, ...).
  • Si la decena es corresponde a 1 (10 al 19), tomarlo de una lista exclusiva de para estos (diez, once, doce ...).
  • Tomar el valor de las unidades de una lista predefinida (un, dos, tres, ...).
  • Tener consideraciones con los valores como el 1 = uno / un, 20 (veinte, veinti).

• Luego de convertir cada cifra de miles se debe de poner su denominación: mil, millones, etc.
• Por último si se desean incluir los decimales se deberán de poner en forma fraccionaria, o utilizar la misma función para escribirlos con letra (normalmente se utiliza la primer opción).

Aquí se pretende publicar una función que sea relativamente fácil de comprender, sin el uso de macros, ni columnas adicionales de manera que sea fácil de insertar en una hoja de cálculo.

A diferencia de las funciones disponibles la que se presenta aquí parte de una lista pre convertida de los números del 0 al 999, como se trata de solo 1000 números no ocupa espacio y tendrá un mayor rendimiento.

Función para escribir números con letras con Microsoft Excel en español:

=SI(A2<0;"menos ";"") & ESPACIOS(SI(TRUNCAR(A2)=0;"cero";SI(ABS(A2) > POTENCIA(10;3*3);BUSCARV(RESIDUO(TRUNCAR(ABS(A2) / POTENCIA(10;3*3));1000);N!$A$1:$B$1001;2;0) & " mil millones ";"")&SI(ABS(A2)>POTENCIA(10;3*2); BUSCARV(RESIDUO(TRUNCAR(ABS(A2) / POTENCIA(10;3*2));1000) ;N!$A$1:$B$1001;2;0) & SI(RESIDUO(TRUNCAR(ABS(A2) / POTENCIA(10;3*2));1000)=1; " millón "; " millones ");"")&SI(ABS(A2) > POTENCIA(10;3*1); BUSCARV(RESIDUO(TRUNCAR(ABS(A2) / POTENCIA(10;3*1)); 1000); N!$A$1:$B$1001;2;0) & " mil ";"") & BUSCARV(RESIDUO(TRUNCAR(ABS(A2) / POTENCIA(10;3*0));1000);N!$A$1:$B$1001;2;0)) & SI(Y(DERECHA(TRUNCAR(ABS(A2));1)="1"; DERECHA(TRUNCAR(ABS(A2));2)<>"11");"o";""))&SI((ABS(A2)-TRUNCAR(ABS(A2)))<>0;" con "&(TEXTO(ABS(A2-TRUNCAR(A2))*100;"0"))&"/100";"")

Asumiendo que en las columnas A y B de otra hoja llamada N se encuentran los nombres de los números del 0 al 999, y en celda A2 de la misma se encuentra el número a convertir. El separador de listas es el símbolo ; (punto y coma).

La lista de los números junto el ejemlpo completo para Microsoft Excel está disponible aquí.

Función para escribir números con letras con OpenOffice.org Calc:

=IF(A2<0;"menos>POWER(10;3*3);VLOOKUP(MOD(TRUNC(ABS(A2)/POWER(10;3*3));1000);$N.$A$1:$B$1001;2;0)&" mil millones ";"")&IF(ABS(A2)>POWER(10;3*2);VLOOKUP(MOD(TRUNC(ABS(A2) / POWER(10;3*2));1000);$N.$A$1:$B$1001;2;0)&IF(MOD(TRUNC(ABS(A2) / POWER(10;3*2));1000)=1;" millón ";" millones ");"")&IF(ABS(A2)>POWER(10;3*1);VLOOKUP(MOD(TRUNC(ABS(A2) / POWER(10;3*1));1000);$N.$A$1:$B$1001;2;0)&" mil ";"")& VLOOKUP(MOD(TRUNC(ABS(A2)/POWER(10;3*0));1000);$N.$A$1:$B$1001;2;0))& IF(AND(RIGHT(TRUNC(ABS(A2));1)="1";RIGHT(TRUNC(ABS(A2));2)<>"11");"o";""))&IF((ABS(A2)-TRUNC(ABS(A2)))<>0;" con "&(TEXT(ABS(A2-TRUNC(A2))*100;"0"))&"/100";"")

Asumiendo que en las columnas A y B de otra hoja llamada N se encuentran los nombres de los números del 0 al 999, y en celda A2 de la misma se encuentra el número a convertir. El separador de listas es el símbolo ; (punto y coma).

La lista de los números junto con el ejemplo completo para OpenOffice.org Calc está disponible aquí. Función para escribir números con letras con Microsoft Excel en ingles, Google Docs, Numbers:

Con respecto al anterior aquí cambia la dirección de la lista de números.

=IF(A2<0;"menos>POWER(10;3*3);VLOOKUP(MOD(TRUNC(ABS(A2)/POWER(10;3*3));1000);N!$A$1:$B$1001;2;0)&" mil millones ";"")&IF(ABS(A2)>POWER(10;3*2); VLOOKUP(MOD(TRUNC(ABS(A2)/POWER(10;3*2));1000);N!$A$1:$B$1001;2;0)& IF(MOD(TRUNC(ABS(A2)/POWER(10;3*2));1000)=1;" millón ";" millones ");"")&IF(ABS(A2)>POWER(10;3*1);VLOOKUP(MOD(TRUNC(ABS(A2)/ POWER(10;3*1));1000);N!$A$1:$B$1001;2;0)&" mil ";"")&VLOOKUP(MOD(TRUNC(ABS(A2) / POWER(10;3*0));1000);N!$A$1:$B$1001;2;0))& IF(AND(RIGHT(TRUNC(ABS(A2));1)="1";RIGHT(TRUNC(ABS(A2));2)<>"11");"o";""))&IF((ABS(A2)-TRUNC(ABS(A2)))<>0;" con "&(TEXT(ABS(A2-TRUNC(A2))*100;"0"))&"/100";"")

Asumiendo que en las columnas A y B de otra hoja llamada N se encuentran los nombres de los números del 0 al 999, y en celda A2 de la misma se encuentra el número a convertir. El separador de listas es el símbolo ; (punto y coma).

Como usar este ejemplo en todas las planillas de cálculo:

Para el uso de esta operación en planillas de cálculo se deberá utilizar el propio archivo que se encuentra disponible, o en el caso de desarrollarlo de cero se deben de realizar los siguientes pasos:

• Crear o utilizar una hoja en blanco con el nombre N, y copiar los datos con los nombres del 0 al 999 (información disponibles en los archivos de ejemplo).
• En otra hoja introducir un número de prueba en la celda A2.
• Poner la función presentada en la celda B2, al hacerlo deberá de mostrar en frase el número introducido en A2.
• Si se quiere mostrar el número en otra celda que no sea A2 y B2 se debe de mover (cortar y pegar) la celda A2 a la primer o única fila donde estará el número a convertir, y mover la celda B2 donde se quiere el primer resultado (otra opción es cambiar A2 por la celda correspondiente en la propia formula).

Una vez familiarizado con el uso de la misma se pueden omitir pasos, incluso dejar la lista en un archivo separado.

Función para escribir números con letras con Oracle:

Para simplificar este artículo este desarrollo se realizó por separado, para verlo acceder aquí.

Realizar promedios sin incluir ceros

Varios usuarios han preguntado como hacer para contar o promediar valores de un rango sin incluir el 0 (cero), aquí se mostrará una forma de hacerlo.

Las funciones que cuentan, por ejemplo CONTAR de Microsoft Excel, consideran las celdas que contengan datos (no vacías), no sirve cuando se desean ignorar los ceros (o cualquier otro valor), las funciones que permiten contemplan condiciones normales, por ejemplo CONTAR.SI, cuentan según una única condición, y si consideran las celdas vacías, por lo que se requieren dos condiciones: que no sean vacías y diferentes de cero.

Básicamente la idea es contar y/o sumar en forma condicionada.

Es recomendable mirar lo publicado hace un tiempo atrás que muestra como contar o sumar filas con condiciones múltiples.

Contar registros sin tomar en cuenta los valores 0 con Microsoft Excel:

=SUMAPRODUCTO(($A$1:$A$10 <> 0) * ($A$1:$A$10 <> ""))

Esta opción utiliza dos condiciones: que el valor sea diferente de cero y diferente de nulo (celda vacía). Otra forma de hacerlo es mediante restas:

=CONTAR(A1:A10) - CONTAR.SI(A1:A10; 0)

Se cuentan todos las celdas con contendido (no nulas) y se restan las que contienen cero.

Sumar registros sin tomar en cuenta los valores 0 con Microsoft Excel:

=SUMA(A1:A10)

En este caso la suma se puede hacer directamente ya que sumar 0 o nulos no cambia el resultado (solo en caso de planillas de cálculo). Si por ejemplo se desean solo los valores positivos la formula sería así:

=SUMAR.SI(A1:A10; ">0"; A1:A10)

Promedio sin tomar en cuenta los valores 0 con Microsoft Excel:

Para realizar el promedio se juntan ambas operaciones mostradas, con sumas condicionadas:

=SUMA(A1:A10) / SUMAPRODUCTO((A1:A10 <> 0) * (A1:A10 <> ""))

ó con restas:

=SUMA(A1:A10) / CONTAR(A1:A10) - CONTAR.SI(A1:A10; 0)

Para el caso del promedio de valores no nulos positivos (> 0 / mayor que cero):

=SUMAR.SI(A1:A10;">0";A1:A10) / CONTAR.SI(A1:A10;">0")

Evitar errores al realizar Promedio con Microsoft Excel:

Cuando se intenta realizar un promedio, ya sea mediante la función PROMEDIO o las indicadas aquí, y todos los valores son nulos el resultado es #¡DIV/0! (o cero para estos casos).

El error se evita preguntando antes si la cantidad (valor del denominador) es cero, en caso positivo se muestra nulo (o cero), y de lo contrario se muestra el resultado de la operación.

Utilizando la función normal que no considera nulos pero si los ceros:

=SI(CONTAR(A1:A10) = 0; ""; PROMEDIO(A1:A10))

Utilizando formulas para no considerar los ceros ni los nulos:

=SI(SUMAPRODUCTO((A1:A10 <> 0) * (A1:A10 <> "")) = 0; "";SUMA(A1:A10) / SUMAPRODUCTO((A1:A10 <> 0) * (A1:A10 <> "")))

ó

=SI(CONTAR(A1:A10) - CONTAR.SI(A1:A10; 0) = 0; ""; SUMA(A1:A10) / (CONTAR(A1:A10) - CONTAR.SI(A1:A10; 0)))

Promediando valores positivos:

=SI(CONTAR.SI(A1:A10; ">0") = 0; ""; SUMAR.SI(A1:A10; ">0"; A1:A10) / CONTAR.SI(A1:A10; ">0"))

Promedio sin tomar en cuenta los valores 0 (cero) con OpenOffice.org Calc:

Función promedio normal:

=IF(COUNT(A1:A10) = 0; ""; AVERAGE(A1:A10))

Función de promedio con condiciones múltiples:

=IF(SUMPRODUCT((A1:A10 <> 0) * (A1:A10 <> "")) = 0; ""; SUM(A1:A10) / SUMPRODUCT((A1:A10 <> 0) *(A1:A10 <> "")))

Función de promedio con restas:

=IF((COUNT(A1:A10) - COUNTIF(A1:A10;0)) = 0; ""; SUM(A1:A10) / (COUNT(A1:A10) - COUNTIF(A1:A10; 0)))

Función de promedio tomando en cuenta solamente los valores positivos:

=IF(COUNTIF(A1:A10; ">0") = 0; ""; SUMIF(A1:A10; ">0") / COUNTIF(A1:A10; ">0"))

Próximamente se completará este artículo con las demás planillas de cálculo y base de datos.

Referencias relativas, absolutas y mixtas en Excel o similar

Para poder trabajar sin problemas con las planillas de cálculo es necesario tener muy claro como funcionan las referencias a celdas y/o rangos, aquí se plantea un artículo que pretende explicar su funcionamiento.

Parte de la información que se presenta fue extraída de la ayuda de Microsoft Excel, si bien fue escrita para los productos de Microsoft es válida para todas las plantillas de cálculo como ser: OpenOffice.org Calc, Google Docs, Numbers, etc.

Según la gente de Microsoft: “Una referencia identifica una celda o un rango de celdas en una hoja de cálculo e indica a Microsoft Excel en qué celdas debe buscar los valores o los datos que desea utilizar en una fórmula. En las referencias se puede utilizar datos de distintas partes de una hoja de cálculo en una fórmula, o bien utilizar el valor de una celda en varias fórmulas. También puede hacerse referencia a las celdas de otras hojas en el mismo libro y a otros libros. Las referencias a celdas de otros libros se denominan vínculos.".

Existen tres tipos de referencias: relativas, absolutas y mixtas.

Referencias relativas Una referencia relativa en una fórmula, como A1, se basa en la posición relativa de la celda que contiene la fórmula y de la celda a la que hace referencia. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia. Si se copia la fórmula en filas o columnas, la referencia se ajusta automáticamente. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas. Por ejemplo, si copia una referencia relativa de la celda B2 a la celda B3, se ajusta automáticamente de =A1 a =A2."
La figura muestra el copiado de la celda B2 a B3, como se puede observar se ha desplazado una fila, y de haberse copiado a la celda C3, el contenido de la misma estaría apuntando a B2.

Referencias absolutas Una referencia de celda absoluta en una fórmula, como $A$1, siempre hace referencia a una celda en una ubicación específica. Si cambia la posición de la celda que contiene la fórmula, la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia absoluta no se ajusta. De forma predeterminada, las nuevas fórmulas utilizan referencias relativas y es necesario cambiarlas a referencias absolutas. Por ejemplo, si copia una referencia absoluta de la celda B2 a la celda B3, permanece invariable en ambas celdas =$A$1."
La figura muestra el copiado de la celda B2 a B3, como se puede observar que ahora no se ha desplazado ninguna fila, y de haberse copiado a la celda C3, el contenido de la misma estaría también apuntando a B1.

Referencias mixtas Una referencia mixta tiene una columna absoluta y una fila relativa, o una fila absoluta y una columna relativa. Una referencia de columna absoluta adopta la forma $A1, $B1, etc. Una referencia de fila absoluta adopta la forma A$1, B$1, etc. Si cambia la posición de la celda que contiene la fórmula, se cambia la referencia relativa y la referencia absoluta permanece invariable. Si se copia la fórmula en filas o columnas, la referencia relativa se ajusta automáticamente y la referencia absoluta no se ajusta. Por ejemplo, si se copia una referencia mixta de la celda A2 a B3, se ajusta de =A$1 a =B$1"
La figura muestra el copiado de la celda B2 a C3, como se puede observar no se ha desplazado la fila, pero si la columna. Si el contenido de B2 hubiese sido =$A1 al copiarla a C3 tendría =$A2.

Si se edita la formula, por ejemplo presionado F2 sobre la celda, y se sitúa el cursor sobre la referencia, al presionar F4 se van alternando las combinaciones mencionadas. Esto es para evitar digitar los símbolos de $.

A continuación se mostrarán un par de ejercicios o ejemplos:

Ejemplo 1:

En una hoja de cálculos se realizaron 5 cuadros, en el cuadro número 1 se ingresaron números del 1 al 9 ordenados.
En el centro del cuadro 2, se ingresó una referencia relativa al centro del cuadro 1: =C5.
En el centro del cuadro 3, se ingresó una referencia absoluta al centro del cuadro 1: =$C$5.
En el centro del cuadro 4, se ingresó una referencia mixta al centro del cuadro 1 fijando la fila: =C$5.
En el centro del cuadro 5, se ingresó una referencia mixta al centro del cuadro 1 fijando la columna: =$C5.
En los cuadros 2 al 5 se copia la celda del centro a las 8 celdas vecinas de los bordes.

Los resultados muestran que:

• Al utilizar referencias relativas en el cuadro 2 se logró reproducir exactamente la muestra.
• Al utilizar referencias absolutas en el cuadro 3 se llenó con el valor del centro de la muestra.
• Al utilizar referencias mixtas fijando la fila en el cuadro 4 solo varía las celdas de las columnas.
• Al utilizar referencias mixtas fijando la columna en el cuadro 5 se ve lo contrario al cuadro 4.

Ejemplo 2:

En la siguiente figura las formulas introducidas en las celdas con colores se visualizan a la izquierda de cada una.

Aquí se ingresó información desde la celda C2 hasta G6 alterando con celdas sin datos.

En las celdas E14 a E17 se definieron referencias a la celda E4 que contiene la palabra Centro con los tres tipos de referencia relativas, absolutas, y mixtas (una para fila y otra para columna).

Por ultimo se copió el rango E14 hacia las celdas C12, G12, C16 y G16 (defasaje de 2 celdas en diagonal para todas las direcciones).

Luego de realizar el copiado se puede ver los diferentes resultados según las referencias introducidas.

Errores frecuentes con referencias:

Uno de los errores mas frecuentes cuando se trabaja con diversas funciones se debe a no utilizar referencias absolutas.

Por ejemplo cuando se utiliza la famosa función BUSCARV o VLOOKUP, si a la matriz a buscar se la define con referencias relativas en lugar de absolutas, y cuando la función final se copia a las filas de abajo, la matriz también se desplazará hacia abajo provocando que apunte a un área donde no se encuentre el dato a buscar.

Uso erróneo de la función:

=BUSCARV(A1;F1:H100;2;FALSO)

NOTA: No siempre está mal utilizar referencias relativas en esta función, hay veces -generalmente muy pocas- en que se necesita este desplazamiento.

Uso correcto de la función:

=BUSCARV($A1;$F$1:$H$100;2;FALSO)

Normalmente se fija la columna done se encuentra el dato a buscar ($A1), que es utilizado cuando se quiera copiar la formula a la derecha para buscar una segunda columna de datos.

La matriz a buscar generalmente ($F$1:$H$100) es fija.

Separar los componentes de nombres de personas

Extraer los componentes de nombres de personas no es una tarea sencilla, aquí se mostrará una manera muy básica de separar los nombres propios en sus dos apellidos y nombres.

Para este caso se parte de la premisa que los componentes del nombre están separados por espacios, e ingresados siempre en el mismo orden: apellido uno, apellido dos, y nombres.

La idea del proceso es extraer el primer apellido a partir de la primer posición del nombre completo hasta antes de la primer ocurrencia del espacio.

Para extraer el segundo apellido se parte del texto que se encuentra a partir del primer espacio hasta la ubicación del siguiente espacio.

Y por ultimo para extraer los nombres se parte desde la posición siguiente al segundo espacio (largo del primer apellido + largo del segundo apellido + 2 espacios + 1) hasta el final de la cadena de texto.

Este método tiene grandes limitantes tales como:

• No extrae apellidos o nombres compuestos (ej: DEL CAMPO).
• El orden de los componentes es fijo para todos los casos.
• Genera anomalías cuando no existen algunos de los apellidos o nombres.

Separar nombres con Microsoft Excel en español:

Partiendo de que el nombre completo se encuentra en la columna A, se dejará el primer apellido en la columna B, el segundo apellido en la columna C, y los nombres en la columna D.

Primer Apellido (B2)_

=EXTRAE(ESPACIOS(A2); 1; HALLAR(" "; ESPACIOS(A2)) - 1)

Segundo Apellido (C2):

=EXTRAE(EXTRAE(ESPACIOS(A2); LARGO(B2) + 2; LARGO(A2)); 1; HALLAR(" "; EXTRAE(ESPACIOS(A2); LARGO(B2) + 2; LARGO(A2))) - 1)

Nombres (D2):

=EXTRAE(ESPACIOS(A2); LARGO(B2) + LARGO(C2) + 3; LARGO(A2))

Separar nombres con Microsoft Excel en inglés, Google Docs, OpenOffice.org Calc, Numbres:

Se parten de las mismas premisas que en ejemplo anterior.

Primer Apellido (B2):

=MID(TRIM(A2); 1; SEARCH(" "; TRIM(A2)) - 1)

Segundo Apellido (C2):

=MID(MID(TRIM(A2); LEN(B2) + 2; LEN(A2)); 1; SEARCH(" "; MID(TRIM(A2); LEN(B2) + 2; LEN(A2))) - 1)

Nombres (D2):

=MID(TRIM(A2); LEN(B2) + LEN(C2) + 3; LEN(A2))

Separar nombres con Oracle:

Se realizará una consulta con una lógica similar a la de las hojas de cálculo, la diferencia más importante radica en que no se dispone del valor de la celda anterior para conocer su largo.

SELECT nombre_completo, substr(nombre_completo, 1, instr(nombre_completo, ' ')-1) apellido_uno, substr(substr(nombre_completo, instr(nombre_completo, ' ') + 1), 1, instr(substr(nombre_completo, instr(nombre_completo, ' ') + 1), ' ') - 1) apellido_dos, substr(nombre_completo, instr(nombre_completo, ' ', instr(nombre_completo, ' ') + 1) + 1) nombres FROM (select 'martinez gonzalez martin' nombre_completo from dual);

Esta sentencia es para prueba, cuando se disponga de una tabla cambiar el contenido del from por la tabla donde se encuentre el nombre a procesar, y modificar los nombres de campos.

Separar Nombres con Microsoft Access:

Se realizará una consulta similar a la planteada en Oracle.

SELECT nombre_completo, mid(nombre_completo, 1, instr(nombre_completo, ' ') -1) as apellido_uno, mid(mid(nombre_completo, instr(nombre_completo, ' ') + 1), 1, instr(mid(nombre_completo, instr(nombre_completo, ' ') + 1), ' ')-1) as apellido_dos, mid(nombre_completo, instr(instr(nombre_completo, ' ') + 1, nombre_completo, ' ') + 1) as nombres FROM tabla_nombres;

Tomando los datos de una tabla llamada tabla_nombres que tiene un campo nombre_completo con contiene la información a dividir.

Otras alternativas:

Vale la pena comentar que otra forma muy básica de hacer este proceso es utilizar la herramienta de Microsoft Excel de separar texto en columnas. Aparte de ser muy básica esta operación se debe de hacer por procesos y no definida en formulas como la que aquí se presenta.

Próximamente se publicarán maneras más completas de realizar estas operaciones. Soluciones con mayor inteligencia son realizadas con algoritmos más complejos basados en árboles de decisión y redes neuronales.

Aprovecho el tema para ofrecer mis servicios profesionales para la normalización de nombres propios en bases de datos a gran escala, utilizando algoritmos que interpretan los componentes sin importar el orden, formato y cantidad de componentes del mismo. Lo complementan operaciones adicionales como la identificación del sexo, e identificación nombre repetidos aun cuando estén definidos en formas diferentes (nombres completos vs iniciales, con faltas de ortografía, por sonido, etc.).

Por consultas realizarlas por este correo

Teclas para moverse en Microsoft Exel o similar

Una manera de optimizar considerablemente el tiempo de trabajo con planillas de cálculo es utilizando teclas para moverse y desplazarse dentro de la planilla.

Cuando el uso de estas teclas se realice en forma natural o inconsciente, la optimización del tiempo de trabajo será considerable.

Las teclas que más importantes para la reducción del tiempo de trabajo con Microsoft Excel en español e inglés y Google Docs son:

CTRL + teclas de dirección (flechas): Ir hasta el extremo de la región de datos actual. El cursor queda en la celda con información anterior a la primer celda vacía encontrada según la dirección seleccionada.


CTRL + INICIO: Ir hasta el comienzo de una hoja de cálculo. Se para en la celda A1.


CTRL+ FIN: Ir a la última celda de la hoja de cálculo, que es la celda ubicada en la intersección de la columna situada más a la derecha y la fila ubicada más abajo (en la esquina inferior derecha) o la celda opuesta a la celda inicial, que es normalmente la celda A1.

Si a la combinación de estas teclas se le agrega la tecla MAYUSCULAS (o SHIFT) el cursor se desplazará y seleccionará el área cubierta desde la celda original a la final del desplazamiento. Esta es la mejor manera de seleccionar celdas ya sea para copiar, pegar o dar formato.

Desplazarse solamente con las telas de dirección o avance de página dentro de listas de decenas de miles de filas es una manera muy ineficiente de hacerlo. Haciendo un simple cálculo: teniendo buena resolución de pantalla entran> un poco mas de 40 filas por página, por lo que llegar a la fila 20 mil mediante avance de páginas requiere presionar dicha tecla unas 500 veces.

Otra tecla importante, pero solo de Microsoft Excel y OpenOffice.org Calc es:

F5: Mostrar el cuadro de diálogo Ir a. Al ingresar una referencia de dirección en este cuadro el cursor se desplaza hacia la misma. Por ejemplo al ingresar F4500 va a dicha celda. En OpenOffice.org Calc la ventana de diálogo es un poco mas completa.

Ejemplo:

Para seleccionar todas las celdas que tienen datos de una columna,se debe de parar al comienzo de la misma, y presionar CTRL + MAYUSC + ABAJO, en caso de no existir ninguna celda vacía el cursor llegará hasta la fila final.

Si existen celdas vacías intermedias, se deberá presionar esta combinación de teclas tantas veces como huecos se encuentren hasta llegar al final. Como alternativa cuando existen muchas celdas vacías es ir hasta el fin de la planilla (CTRL + MAYSC + FIN), de esta manera el cursor habrá quedado en la fila final pero también en la columna final, luego retroceder columnas hacia la izquierda hasta dejar seleccionada la columna que se desea.

Otra alternativa es "bajar" por una columna que no tenga datos (CTRL + MAYUSC + ABAJO) que llegará hasta la fila 64 mil y pico, posicionarse el la columna deseada y regresar para arriba con CTRL + MAYUSC + ARRIBA.

Este ejemplo aplica también sin el uso de la tecla MAYUSC lo cual solo servirá para desplazarse.

Se recomienda tomar un archivo que tenga datos ingresados y jugar para familiarizarse con el uso de las mismas. "Jugar" con estas teclas sin miedo ya que no se rompe nada. Primero probar solo el desplazamiento y luego la selección de áreas.

Otras teclas de desplazamiento:

Teclas de dirección: Moverse una celda hacia arriba, hacia abajo, hacia la izquierda o hacia la derecha.


INICIO: Ir hasta el comienzo de una fila. (*)


AV PÁG: Desplazarse una pantalla hacia abajo.


RE PÁG: Desplazarse una pantalla hacia arriba.


ALT + AV PÁG: Desplazarse una pantalla hacia la derecha. (*)


ALT + RE PÁG: Desplazarse una pantalla hacia la izquierda. (*)


CTRL + AV PÁG: Ir a la siguiente hoja del libro.


CTRL + RE PÁG: Ir a la hoja anterior del libro.


CTRL+ F6 o CTRL + TAB: Ir al siguiente libro o a la siguiente ventana. (*) (**)


CTRL + MAYÚS + F6 ó CTRL + MAYÚS + TAB: Ir al libro o a la ventana anterior. (*) (**)


F6: Mover al siguiente panel de un libro que se ha dividido. (*) (**)


MAYÚS + F6: Mover al anterior panel de un libro que se ha dividido. (*) (**)


CTRL + RETROCESO: Desplazarse para ver la celda activa. (*) (**)


MAYÚS + F5: Mostrar el cuadro de diálogo Buscar. (*) (**)


MAYÚS + F4: Repetir la última acción de Buscar (igual a Buscar siguiente). (*)


TAB: Desplazarse entre celdas desbloqueadas en una hoja de cálculo protegida.

Notas:

• Texto tomado de la ayuda de Microsoft Excel al que se le incluyeron algunos comentarios.
• (*) No disponibles en Google Docs.
• (**) No disponibles en OpenOffice.org Calc.

Identificar registros repetidos Excel o similar

Identificar registros repetidos en una tabla de datos es una operación que se realiza con alta frecuencia, aquí se mostrará una manera muy fácil de hacerlo.

La idea es la siguiente:

1. Identificar la clave de registro único, definir que columnas o combinación de estas la componen.
2. Ordenar los datos por la clave de registro único.
3. Comparar los componentes de la clave de una fila con la fila siguiente, si son iguales se trata de un registro duplicado, si lo son se marcan como tales.

Se mostrarán ejemplos partiendo de los siguientes datos:

Ejemplo 1: Eliminar registros repetidos dejando los valores con fechas mas recientes.

Los pasos son los siguientes:

• Ordenar la lista, en este caso por Código y luego por Fecha. De esta manera quedan todos los registros con el mismo código juntos, y el que tiene la fecha mayor en el último lugar.
• Se crea una nueva columna "EsRepetido" en la cual simplemente se comparará el contendido de la columna "Código" para la fila actual con la siguiente columna, obteniendo dos valores posibles: Verdadero o Falso.
  

  =A2 = A3
  

• A la lista de datos se le aplica un Autofiltro, y se seleccionan de la nueva columna "EsRepetido" todas aquellas filas que tienen Verdadero. Estas se deben de eliminar.

Si el criterio para determinar valores repetidos fuese las columnas "Nombre" y "Apellido" la consulta se haría por ambas columnas de la siguiente manera:

Formula en Microsoft Excel en Español:

=Y(C2 = C3; B2 = B3)

Formula en Google Docs, Microsoft Excel en Inglés, OpenOffice.org Calc, Numbers:

=AND(C2 = C3; B2 = B3)

Para este caso los datos se debieron de ordenar por "Apellido" y luego por "Nombre" (o viceversa), y luego por la fecha.

La fecha se incluye en el orden para tener un criterio al momento de eliminar el registro. En caso de necesitar eliminar los registros con fechas mas antiguas el orden de la fecha será descendente.

Resultados diferentes se obtienen si al momento de comparar la fila actual se lo hace respecto a la anterior en lugar de la siguiente (eliminaría registros diferentes).

Ejemplo 2: Sumar la deuda actualizada de los clientes, contar cantidad de registros únicos.

En este caso, deuda actualizada se entiende como el registro con la fecha mayor para cada cliente.

Los pasos son los siguientes:

• Al igual que en el caso anterior la lista se debe de ordenar por código (o nombre y apellido) y luego por fecha, también se crea la columna EsRepetido utilizando los mismos criterios.
• Para contar los registros únicos se creará una columna Cantidad en la que en cada fila se pondrá 1 si el valor de la columna EsRepetido de su correspondiente fila es Falso, de lo contrario se pone 0. Al sumar todos los datos de esta nueva columna se obtiene la cantidad de registros únicos.

Formula en Microsoft Excel en Español:

=SI(F2 = FALSO; 1; 0)

Formula en Google Docs, Microsoft Excel en Inglés, OpenOffice.org Calc, Numbers:

=IF(F2 = FALSE; 1; 0)

Ahora para sumar el valor de la deuda tomando en cuenta solo el registro que contiene la fecha mayor se debe de verificar la columna EsRepetido, si no se trata de un repetido se toma el valor de la deuda, de lo contrario cero.

Formula en Microsoft Excel en Español:

=SI(F2 = FALSO; E2; 0)

Formula en Google Docs, Microsoft Excel en Inglés, OpenOffice.org Calc, Numbers:

=IF(F2 = FALSE; E2; 0)

El registro de la fecha mayor se obtiene gracias al orden de los datos, no requiere realizar nada a nivel de formulas.

Estas formulas se pueden realizar sin tener la columna auxiliar EsRepetido:

Cuenta sin repetidos en Microsoft Excel en Español:

=SI(NO(A2 = A3); 1; 0)

Cuenta sin repetidos en Google Docs, Microsoft Excel en Inglés, OpenOffice.org Calc, Numbers:

=IF(NOT(A2 = A3); 1; 0)

Suma de deuda actualizada en Microsoft Excel en Español:

=SI(NO(A2 = A3); E2; 0)

Suma de deuda actualizada en Google Docs, Microsoft Excel en Inglés, OpenOffice.org Calc, Numbers:

=IF(NOT(A2 = A3); E2; 0)

Como utilizar la función BUSCARV o VLOOKUP y errores más frecuentes

Una de las herramientas mas utilizadas de las planillas de cálculo es la función BUSCARV o VLOOKUP, una vez que te familiarizas con ella le “tomarás cariño” ya que permite optimizar el procesamiento de datos. Aquí se muestra como se utiliza y cuales son los errores más comunes que se dan al utilizarla.

Según la ayuda de Microsoft Excel la función Busca un valor en la primera columna de la izquierda de una tabla y luego devuelve un valor en esa misma fila desde una columna especificada. De forma predeterminada, la tabla se ordena de forma ascendente.

Su sintaxis es la siguiente:

BUSCARV(
valor_buscado;
matriz_a_buscar_en;
indicador_de_columnas;
ordenado
)

Donde:

• Valor_buscado: es el valor buscado en la primera columna de la tabla y puede ser un valor, referencia o una cadena de texto.
• Matriz_a_buscar_en: Es una tabla de texto, números o valores lógicos en los cuales se recuperan datos. Puede ser una referencia a un rango o un nombre de rango.
• Indicador_de_columnas: Es el número de columna de la matriz matriz_a_buscar_en desde la cual se debe de devolverse el valor que coincida. La primera columna de valores en la tabla es la columna 1.
• Ordenado:es un valor lógico para encontrar la coincidencia más cercana en la primera columna (ordenada de forma ascendente) = VERDADERO u omitido; para encontrar la coincidencia exacta = FALSO.

La función VLOOKUP de Microsft Excel en Inglés, Google Docs, OpenOffice.org Calc se comporta de la misma manera que BUSCARV, solo difiere en su nombre.

Los errores mas comunes que se presentan con esta función son los siguientes:

1. Al escribir la función en la primer fila funciona bien, pero al copiarlo a las filas de abajo comienzan a salir #N/A cuando el valor buscado se encuentra en la matriz a buscar.
   Las referencias de la matriz a buscar están en forma variable. Por ejemplo cuando se escribe un rango A1:B100 en la fila 2 y se copia a la fila 3 este pasa a ser A2:B101, y así sucesivamente.
   La solución es siempre poner el rango de la matriz a buscar en forma fija de la manera $A$1:$B$100 (se escribe el símbolo $ antes de de cada elemento de la dirección, es mas fácil hacerlo con la tecla F4 cuando se está editando la formula y el cursor está sobre las direcciones).
   Otra solución es definirle un nombre al rango y utilizar este nombre en la función.
2. El dato que recupera es incorrecto, no pertenece a el valor buscado.
   La mayoría de las veces se requiere que la función retorne los valores exactos, y esto se hace ingresando Falso (ó 0) en el cuarto parámetro. En el caso que se omita o se ingresa Verdadero y el valor no se encuentra, retorna el primer dato mayor al valor buscado que encuentre.
3. La función retorna #N/A aun cuando el dato a buscado se encuentra en la matriz a buscar.
   La función solo busca en la primer columna, por lo que si el dato se encuentra en otra ésta no lo encontrará.
   Se deben de reordenar las columnas de manera que donde se encuentra el valor buscado quede de primera, en caso de que no se pueda se deberá de crear una nueva columna al inicio de la matriz con los mismos datos que la original (por ejemplo si no se puede mover la columna D, debo tener en la columna a una copia de D: en A2: =D2).
4. La función retorna #N/A sin importar si el dato esté o no en la matriz a buscar.
   Se está tomando una columna que no pertenece al rango. El rango debe de contener desde la columna que contiene los datos a buscar hasta como mínimo la columna donde se encuentran los valores a devolver.

Ver como quitar los resultados #N/A que se obtienen cuando no se encuentra el valor.

Lo expuesto aquí también aplica para las funciones de búsquedas horizontales BUSCARH o HLOOKUP considerando que las búsquedas se hacen por filas en lugar de columnas.

Quitar los valores #N/A en Excel o similar

Cuando se utiliza la función BUSCARV o VLOOKUP y el dato a buscar no se encuentra retorna el valor #N/A el cual no es posible utilizarlo en sumas, cuentas, ni demás operaciones que se requieran aplicarle a los datos que si fueron identificados.

Se deben de quitar estos valores para continuar con las operaciones. La primer opción que se viene a la mente es borrar las celdas que contienen #N/A, creando un nuevo posible problema ya que se pierden las fórmulas.

La manera mas limpia de quitarlos es preguntar por el resultado de la función, si es #N/A se asigna un “valor nulo”, que puede ser el propio nulo (“”), cero (0), las palabras “No se encuentra”, o el valor que se requiera. En caso de no serlo se ejecuta nuevamente la función para mostrar el resultado obtenido.

La función ESNOD (o ISNA para versiones en inglés) retorna verdadero si el valor que se le pasa es #N/A, de lo contrario falso.

La forma de evitar el valor #N/A en Microsoft Excel es:

=SI(ESNOD(BUSCARV(E1; $A$2:$B$22; 2; 0)); "Valor Nulo"; BUSCARV(E1; $A$2:$B$22; 2; 0))

La forma de evitar el valor #N/A en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:

=IF(ISNA(VLOOKUP(E1; $A$2:$B$22; 2; 0)); "Valor Nulo"; VLOOKUP(E1; $A$2:$B$22; 2; 0))

Esta solución aplica para todas las funciones que puedan retornar el valor #N/A.

Calcular primer y ultimo día hábil del mes

En este artículo se mostrará como calcular el primer y/o último día hábil del mes, en principio sin contemplar los días festivos.

Para hallar el primer día hábil del mes, la idea es la siguiente:

1. Calcular la fecha del primer día del mes.
2. Ver el día de la semana que "cae" primer día del mes.
3. Si se trata de un sábado hay que sumarle dos días, de ser un domingo se suma uno, y para el resto de los días es el propio primero.

Para hallar el último día hábil del mes, la idea es la siguiente:

1. Calcular la fecha del último día del mes.
2. Ver el día de la semana que "cae" el último día del mes.
3. Si se trata de un sábado hay que restarle un día, de ser un domingo se restan 2, y para el resto de los días el mismo fin de mes.

Calcular primer y último día laborable en Microsoft Excel en español:

Primer día laborable del mes:

=A1+SI(DIASEM(A1) = 1; 1; SI(DIASEM(A1) = 7; 2; 0))

Último día laborable del mes:

=FIN.MES(A1;0) - SI(DIASEM(FIN.MES(A1; 0)) = 1; 2; SI(DIASEM(FIN.MES(A1; 0)) = 7; 1; 0))

Partiendo del supuesto que en A1 se encuentra la fecha perteneciente al mes en cuestión. Si se cambia por HOY() se obtiene el dato respecto al día actual.

Variante al Cálculo en Microsoft Excel en español:

En Excel hay una alternativa en la que si se pueden tener en cuenta dos días festivos.

Calcular el primer día hábil considerando los festivos se identifica el último día calendario del mes anterior y a este se le suma 1 día laborable.

Otra fórmula para calcular el primer día hábil del mes en Microsoft Excel sin considerar festivos.

=DIA.LAB(FIN.MES(FECHA(AÑO(A1); MES(A1) - 1; 1); 0); 1)

Otra fórmula para calcular el primer día hábil del mes en Microsoft Excel considerando festivos.

=DIA.LAB(FIN.MES(FECHA(AÑO(A1);MES(A1)-1;1);0);1;RANGO_DIAS_FESTIVOS)

Donde RANGO_DIAS_FESTIVOS corresponde a un rango donde se encuentra la lista de días festivos.

Calcular el primer día hábil considerando los festivos se identifica el último día calendario del mes corriente, a este se le suma 1 día calendario, y se le resta un día laborable.

Otra fórmula para calcular el ultimo día hábil del mes en Microsoft Excel sin considerar festivos.

=DIA.LAB(FIN.MES(A1; 0) + 1; -1)

Otra fórmula para calcular el ultimo día hábil del mes en Microsoft Excel considerando festivos.

=DIA.LAB(FIN.MES(A1;0)+1;-1;RANGO_DIAS_FESTIVOS)

Calcular primer y último día laborable del mes en Google Docs, OpenOffice.org Calc, Microsoft Excel en inglés:

Calcular primer día laborable del mes:

=A1 + IF(WEEKDAY(A1) = 1; 1; IF(WEEKDAY(A1) = 7; 2; 0))

Calcular ultimo día laborable del mes:

=(date(year(A1); month(A1) + 1; 1) - 1)- if(weekday((date(year(A1); month(A1) + 1; 1) - 1)) = 1; 2; if(weekday((date(year(A1); month(A1) + 1; 1) -1)) = 7; 1; 0))

Al no tener la función que identifica el último día del mes la formula queda un poco mas compleja.

Calcular primer y último día laborable del mes en Oracle:

Cálculo para el primer día laborable del mes:

select to_date('200902', 'yyyymm') + decode(to_char(to_date('200902', 'yyyymm'), 'd'), '1', 1, '7', 2, 0) fecha_fija, trunc(sysdate, 'mm') + decode(to_char(trunc(sysdate, 'mm'), 'd'), '1', 1, '7', 2, 0) respecto_a_hoy from dual;

Cálculo para el ultimo día laborable del mes:

select last_day(sysdate) - decode(to_char(last_day(sysdate), 'd'), '1', 2, '7', 1, 0) respecto_a_hoy, last_day(to_date('200803', 'yyyymm')) - decode(to_char(last_day(to_date('200803', 'yyyymm')), 'd'), '1', 2, '7', 1, 0) fecha_fija from dual;

Calcular primer y último día laborable del mes en MySQL:

Cálculo para el primer día laborable del mes:

select adddate(date(concat(extract(year_month from '2009-03-15'), '01')), case weekday(date(concat(extract(year_month from '2009-03-15'), '01'))) when 6 then 2 when 5 then 1 else 0 end) fecha_fija, adddate(date(concat(extract(year_month from sysdate()), '01')), case weekday(date(concat(extract(year_month from sysdate()), '01'))) when 6 then 2 when 5 then 1 else 0 end) respecto_a_hoy;

Cálculo para el ultimo día laborable del mes:

select ADDDATE(last_day(sysdate()), - case weekday(last_day(sysdate())) when 6 then 2 when 5 then 1 else 0 end) respecto_a_hoy, ADDDATE(last_day('2008-08-01'), - case weekday(last_day('2008-08-01')) when 6 then 2 when 5 then 1 else 0 end) fecha_fija;

Calcular primer y último día laborable del mes en Microsoft Access:

Cálculo para el primer día laborable del mes:

SELECT cdate("1/02/2009") + IIf(Weekday(cdate("1/02/2009")) = 1, 1, IIf(Weekday(cdate("1/02/2009")) = 7, 2, 0)) as primer_dia_fecha_fija, DateSerial (year(date()), month(date()), 1) + IIf(Weekday(DateSerial (year(date()), month(date()), 1)) = 1, 1, IIf(Weekday(DateSerial (year(date()), month(date()), 1)) = 7, 2, 0)) as resp_a_hoy FROM datos;

Consulta que toma datos de una supuesta tabla "datos".

Cálculo para el ultimo día laborable del mes:

SELECT (DateSerial (year(date()), month(date()) + 1, 1)-1) - IIf(Weekday( (DateSerial (year(date()), month(date()) + 1, 1)-1)) = 1, 2, IIf(Weekday( (DateSerial (year(date()), month(date()) + 1, 1)-1)) = 7, 1, 0)) as respecto_a_hoy FROM datos;

Consulta que toma datos de una supuesta tabla "datos". Al no disponer de una función que calcule el ultimo día del mes calendario, se calcula el primer día del mes siguiente y se le resta uno (funciona aun para diciembre: 12 + 1 = enero).