Trabajando con horas, minutos, y segundos miércoles 21 de mayo de 2008
Uno de los dolores de cabeza con los que nos hemos encontrado quienes hemos empezado con excel, ha sido trabajar con unidades de tiempo, para calcular horas de espera, tiempo trabajado, descansos, etc. Al principio, por falta de conocimientos, nos planteamos soluciones inverosímiles, que si bien funcionan, no siempre nos permiten obtener los mejores resultados, en especial si trabajamos con diferencias horarias superiores a 24 horas. Es por eso, que excel nos provee de una herramienta apta para el cálculo de horas, minutos y segundos, de una manera muy sencilla.
Imaginemos que tenemos esta tabla en excel (los datos de las columnas C y D tienen formato de h:mm):
¿Cómo podemos calcular la diferencia horaria entre la hora de entrada y la hora de salida?. Pues de una forma muy sencilla. Tan solo tendremos que restarle a la columna D, la columna C, y darle un formato especial:
¿Qué formato hay que darle a la columna E?. Pues este [h]:mm:
Para ello, seleccionaremos desde E4 hasta E8, y en el menú Formato, dentro de la opción Celdas..., y en la pestaña Número que es la que nos sale en primer plano, por defecto, en la categoría Personalizada, seleccionaremos la que aparece en la siguiente imagen (si deseamos controlar no solo horas y minutos, sino también segundos, entonces seleccionaremos la opción que aparece arriba como [h]:mm:ss):
La suma total de horas que aparece en la celda E9, será una suma normal, pero a la que le habremos dado el mismo formato [h]:mm.
¿Y cómo podemos obtener las horas y fracciones de hora?. Ya sabéis que las 7:15, son 7,25 horas. Las 7:30 son en realidad 7,5 horas, y así sucesivamente. Mirad la siguiente imagen:
En F4 simplemente estamos multiplicando el dato de celda de la columna anterior, por 24. Este dato deber tener formato numérico, no de [h]:mm.
La cosa se complica un poco más, cuando queremos controlar por ejemplo la hora de entrada y salida de un trabajador, o un control horario de cualquier otro tipo, donde la entrada y la salida se realizan en fechas diferentes, y donde hay una diferencia horaria superior a las 24 horas. ¿En realidad es tan complicado?. Para nada, es bastante sencillo, una vez le hemos pillado el truco.
Fijaos que simplemente estamos sumando día y hora de salida, y a ese dato, le restamos el día y la hora de entrada (lo podéis ver en la barra de fórmulas de la imagen anterior, donde se muestra la fórmula de la celda F4). El dato de esa columna, deberá llevar también el formato [h]:mm.
Creo que con esto, ya tendremos resueltas nuestras necesidades en cuanto a la forma de operar con unidades de tiempo, horas, minutos, y segundos.
Publicado por Javier Marco 2 comentarios
Etiquetas: formatos
Ocultar filas y columnas mediante macros miércoles 14 de mayo de 2008
Muchos de los internautas que aterrizan en este blog, lo hacen buscando unas pequeñas líneas de código, o un macro, que le permitan ocultar filas o columnas para completar algún procedimiento que tienen entre manos. Como es mi interés dar respuesta a sus inquietudes, vamos a tratar hoy este tema.
Si lo que deseamos es ocultar la fila activa, es decir, la fila donde estamos situados, nos bastará copiar el presente código en un módulo VBA (ya sabéis, Alt+F11, para entrar en modo VBA):
| Sub Ocultar_fila() 'Nos fijamos en la celda 'donde estamos situados celda = ActiveCell.Address 'Seleccionamos la fila a la 'que pertenece la celda activa ActiveCell.EntireRow.Select 'Ocultamos la fila Selection.EntireRow.Hidden = True 'Nos situamos en la fila anterior, 'siempre que no estemos ya en la primera If ActiveCell.Row <> 1 Then 'vamos a la fila anterior Range(celda).Offset(-1, 0).Select Else 'vamos a la fila siguiente Range(celda).Offset(1, 0).Select End If End Sub |
Si lo que deseamos es ocultar la columna de la celda activa, nos bastará con modificar ligeramente el código anterior, para dejarlo de la siguiente forma:
| Sub Ocultar_columna() 'Nos fijamos en la celda 'donde estamos situados celda = ActiveCell.Address 'Seleccionamos la columna a la 'que pertenece la celda activa ActiveCell.EntireColumn.Select 'Ocultamos la columna Selection.EntireColumn.Hidden = True 'Nos situamos en la columna anterior, 'siempre que no estemos ya en la primera If ActiveCell.Column <> 1 Then 'vamos a la columna anterior Range(celda).Offset(0, -1).Select Else 'vamos a la columna de la derecha Range(celda).Offset(0, 1).Select End If End Sub |
Pero quizás deseéis ocultar varias filas de golpe. En este caso, informaremos dentro del propio código, de las filas a ocultar. En el caso del ejemplo que os pongo a continuación, ocultaremos desde la fila 5 a la fila 20:
| Sub Ocultar_varias_filas() 'Seleccionamos las filas a ocultar Rows("5:20").Select 'Las ocultamos Selection.EntireRow.Hidden = True 'Nos situamos en A1 Range("A1").Select End Sub |
Si por el contrario, queremos ocultar un grupo de columnas, nos bastará con informar de ello en el macro, a la vez que modificamos el código anterior para hacer referencia a las columnas (columns), en lugar de a las filas (rows):
| Sub Ocultar_varias_columnas() 'Seleccionamos las columnas a ocultar Columns("F:M").Select 'Las ocultamos Selection.EntireColumn.Hidden = True 'Nos situamos en A1 Range("A1").Select End Sub |
Publicado por Javier Marco 2 comentarios
Etiquetas: macros en excel
Simultanear filas de colores miércoles 7 de mayo de 2008
Vamos a ver como podemos aplicar la utilidad del Formato condicional, para simultanear una fila de un color, y otra fila de otro color, en una tabla con datos, para hacer más legibles los datos, dándoles además, un toque elegante.
Imaginemos que tenemos esta tabla del ejemplo, con 10 filas con datos y una fila adicional con los totales:
Si creamos una tabla como la anterior, y queremos que nos salgan alternados los colores de las filas, nos situaremos en la primera celda de la tabla (en el ejemplo en B3), y seleccionaremos en el menú Formato, la opción Formato condicional…, e incluiremos esta condición que veis a continuación, como fórmula, sin olvidar de incluir el formato de color gris de la trama de fondo:
Con esa fórmula le estamos diciendo a excel que si estamos en una fila par (si al dividir por dos, el número de la fila, el resto que obtenemos es cero), que le ponga el fondo de color gris, por lo que en el caso contrario, si estamos en una celda impar, no se cumplirá esa condición, y no pondrá por tanto, de color gris el fondo de la celda.
Si queréis hacer un copiar y pegar, aquí os pongo la fórmula (no olvidéis darle color de fondo a la celda):
| =SI(RESIDUO(FILA();2)=0;VERDADERO;FALSO) |
Una vez tengamos dado ese formato condicional a la celda B3, copiaremos el formato de esa celda, y lo pegaremos en el resto de la tabla (hasta D12, en el ejemplo). Siguiendo con el ejemplo anterior, rellenaremos los datos con las fechas, los importes y la fórmula de porcentaje que le corresponda, y pondremos en la fila 13 las sumas totales, a la que le habremos dado un formato con color de fondo amarillo (pero no un formato condicional, sino un formato normal), igual que a la cabecera de la tabla donde figuran los conceptos.
Ahora imaginemos que queremos insertar encima de los totales, dos nuevas fecha, como por ejemplo la correspondiente al 08-05-2008, y la del 09-05-2008. Para ello, nos situaremos en la celda B13, y seleccionaremos en el menú Insertar, la opción Filas. Haremos esta operación dos veces, pues insertaremos dos filas, una para cada fecha. Si nos fijamos bien, la primera fila no tendrá color de fondo, pues le correspondería el blanco por defecto, pero en cambio, la segunda fila sí que tendrá el fondo de color gris. ¿Por qué?. Pues sencillamente porque como hemos insertado filas, se ha heredado el formato condicional de la fila inmediatamente anterior, y en esa fila aparece aquello de que si es par que le ponga el fondo de color gris, y si es impar, que no haga nada.
Si insertamos una fila (o más) en mitad de la tabla, el efecto será el mismo, pues los colores se irán simultaneando en función de si estamos en una fila par o impar.
Este es un procedimiento muy sencillo, que además nos permitirá leer mucho mejor los datos de las tablas, porque tendremos las filas separadas por colores.
Publicado por Javier Marco 0 comentarios
Etiquetas: formatos
Leer una base de datos Access miércoles 30 de abril de 2008
Hoy nos toca hablar de algo muy interesante y útil. Vamos a analizar como podemos leer una base de datos access, importando los datos de una de sus tablas.
Para poder leer una base de datos, necesitaremos seleccionar desde VBA, dentro del menú Herramientas, la opción Referencias, poniéndole una muesca a Microsoft ActiveX Data Objects 2.8 Library, tal y como podéis comprobar en la siguiente imagen (esta opción la encontraréis bajando hasta la letra M de Microsoft, pues las referencias están ordenadas alfabéticamente. En cuando le pongáis una muesca, os aparecerá arriba del todo como en la imagen):
Ahora tan solo nos quedará copiar el macro que veréis a continuación, y donde informaremos de lo siguiente:
- La ruta donde se encuentra la base de datos (en el macro hemos considerado que la base de datos está en la misma carpeta donde tenemos el fichero de excel con el macro y desde el que queremos leer la base de datos).
- El nombre de la base de datos.
- El nombre de la tabla de la base de datos que queremos leer.
- La celda inicial donde vamos a empezar a escribir los datos.
| Sub conectar_con_la_base_de_datos() '************************************************** ' Para que esto funcione, debemos seleccionar ' en el menú Herramientas (desde VBA), la opción ' Referencias, y le pondremos una muesca a ' "Microsoft ActiveX Data Objects 2.8 Library" '************************************************** 'Indicamos la ruta de la base de datos: 'Supondremos que la base de datos 'está en la misma ruta donde tenemos 'guardado este fichero de excel 'si la ruta fuera otra, la pondremos 'entre comillas, por ejemplo así, con 'cuidado de no poner la antibarra del final: 'ruta = "C:\Mis documentos". 'Indicaremos también el nombre de la 'base de datos, la tabla, y la celda 'inicial donde empezaremos a escribir ruta = ThisWorkbook.Path base_de_datos = "frases-celebres.mdb" tabla = "frases" celda_inicial = "A1" '************************************************** 'Ocultamos el procedimiento Application.ScreenUpdating = False 'Si hay errores, que siga 'procesando esta página On Error Resume Next 'Creamos el objeto conexión Set Conn = New ADODB.Connection 'Creamos el objeto recordset Set rs = New ADODB.Recordset 'Nos conectamos a la base de datos Conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & ruta & "\" & base_de_datos) 'Montamos la sentencia SQL para 'mostrar todos los datos de la tabla Sql = "Select * from " & tabla 'Abrimos la base de datos rs.Open Sql, Conn, adOpenStatic, adLockOptimistic 'contamos los registros totales registros_totales = rs.RecordCount 'Si ha errores, mostramos un mensaje 'y finalizamos el macro If Err > 0 Then 'mostramos un mensaje MsgBox (Chr(13) + " Lo sentimos, se han producido errores, " + _ Chr(13) + " y no se ha podido completar la operación. " + _ Chr(13) + Chr(13)), vbOKOnly, " Importación incorrecta" 'finalizamos el macro Exit Sub End If 'Recorremos todos los registros 'empezando a escribir en la celda A1 Range(celda_inicial).Select 'contamos los campos que tenemos 'en la base de datos numero_de_campos = rs.Fields.Count 'comenzamos con el bucle, a 'recorrer los encabezados For i = 0 To numero_de_campos - 1 'Ponemos en negrita el encabezado Selection.Font.Bold = True 'ponemos el nombre del encabezado 'y además, en mayúsculas ActiveCell = UCase(rs.Fields(i).Name) 'pasamos a la siguiente columna ActiveCell.Offset(0, 1).Select Next 'miraremos el máximo de filas a importar If Rows.Count = 65536 Then 'si tenemos una versión anterior a 'excel 2007, importaremos el máximo 'que nos permite excel: 65536 filas 'de filas, pero restaremos las filas 'necesarias, dependiendo de donde 'empezemos a escribir en la celda_inicial total_filas = 65536 maximo = total_filas - Range(celda_inicial).Row Else 'en caso contrario, importaremos 'un máximo de un millón de filas total_filas = 1000000 maximo = total_filas - Range(celda_inicial).Row End If 'seleccionamos la fila siguiente 'para escribir los datos de la tabla, 'mientras haya datos, y mientras estos 'no ocupen más que las filas disponibles 'que nos quedan en la hoja de cálculo Range(celda_inicial).Offset(1, 0).Select 'creamos un contador para contar 'los registros que llevamos contador = 1 Do While Not rs.EOF And contador <= maximo 'comenzamos a escribir los datos For i = 0 To numero_de_campos - 1 ActiveCell = rs.Fields(i) 'pasamos a la siguiente columna ActiveCell.Offset(0, 1).Select Next 'volvemos a la columna inicial 'donde estábamos al principio ActiveCell.Offset(0, -numero_de_campos).Select 'pasamos a la siguiente fila, siempre 'y cuando no estemos ya en la última 'fila que nos permite la hoja de cálculo If ActiveCell.Row < total_filas Then ActiveCell.Offset(1, 0).Select 'nos desplazamos al siguiente 'registro de la base de datos rs.MoveNext 'sumamos 1 al contador contador = contador + 1 Loop 'cerramos la conexión Conn.Close 'limpiamos los objetos Set Conn = Nothing Set rs = Nothing 'Mostramos el procedimiento Application.ScreenUpdating = True 'mostraremos un mensaje dependiendo de 'si se han importado todos los registros 'de la base de datos, o solo una parte If registros_totales = contador - 1 Then MsgBox (Chr(13) + " Se importaron correctamente todos los registros " + _ Chr(13) + " de la base de datos """ & base_de_datos & """, y de la tabla " + _ Chr(13) + " llamada """ & tabla & """. " + _ Chr(13) + Chr(13) + " Se han importado los " & contador - 1 & " registros de la tabla. " + _ Chr(13) + Chr(13)), vbOKOnly, " Importación incompleta" Else MsgBox (Chr(13) + " Se importaron correctamente solo algunos registros " + _ Chr(13) + " de la base de datos """ & base_de_datos & """, y de la tabla " + _ Chr(13) + " llamada """ & tabla & """. " + _ Chr(13) + Chr(13) + " En concreto solo se importaron " & contador - 1 & " registros, de " + _ Chr(13) + " los " & registros_totales & " registros disponibles. " + _ Chr(13) + Chr(13)), vbOKOnly, " Importación completa" End If End Sub |
Con esto ya tendremos resueltas nuestras necesidades, para leer correctamente una tabla de una base de datos.
Desde aquí podéis descargar el fichero de excel, con todo el código que os presento en este artículo, más la base de datos que he utilizado como ejemplo, que contiene una serie de frases célebres, para que así podáis importarla desde excel, y ver como funciona todo.
Publicado por Javier Marco 2 comentarios
Etiquetas: ADO, macros en excel
Calcular la letra del NIF (o del DNI) miércoles 23 de abril de 2008
Hoy os presento una utilidad bastante sencilla, y que muchos ya conocéis, especialmente los españoles, pues esta utilidad es de aplicación por estas tierras de Dios. Vamos a tratar de obtener la letra del NIF (Número de Identificación Fiscal) o la letra del DNI (Documento Nacional de Identidad), pues desde hace ya unos años, el DNI también incorpora la famosa letra de marras.
Antes de comenzar, veremos como se determina la letra que le corresponde a un NIF (o DNI), y luego pondremos eso en práctica de diferentes formas: utilizando una función personalizada, y utilizando un macro.
Para calcular la letra, tomaremos el número del NIF (o del DNI), y lo dividiremos entre 23, y al resto que obtengamos de esa división, le sumaremos 1. Esa cifra nos servirá para asignarle una letra al NIF/DNI. El número máximo que obtendremos después de hacer esa operación, será el 24. Veamos todo esto con un ejemplo práctico:
Si tenemos el NIF/DNI número 30.655.784, con esta fórmula que podemos poner en cualquier celda, obtendremos ese número:
| =RESIDUO(30655784;23)+1 |
En lugar de poner directamente en la fórmula, el número del NIF/DNI, podemos poner la referencia a una celda (A1, A23, B12, M55, etc...), siempre y cuando, en esa celda tengamos el número del NIF/DNI.
Esa fórmula de nuestro ejemplo, nos devuelve como cifra, el número 5. Ahora solo nos bastará obtener la letra a partir de ese número, a través de la siguiente tabla:
| 1 = T 2 = R 3 = W 4 = A 5 = G 6 = M 7 = Y 8 = F 9 = P 10 = D 11 = X 12 = B 13 = N 14 = J 15 = Z 16 = S 17 = Q 18 = V 19 = H 20 = L 21 = C 22 = K 23 = E 24 = T |
Siguiendo con el ejemplo anterior, podemos observar que al 5, le corresponde la letra G, con lo cual, al NIF/DNI 30.655.784, le corresponde la letra G.
Vamos a ver como se puede resolver esto con una función personalizada:
| Function LetraNIF(NIF As Long) 'Controlamos que no sea un número 'mayor de 8 cifras If NIF = 0 Or NIF > 99999999 Then LetraNIF = "NIF incorrecto" Else 'Dividimos el NIF entre 23, y nos 'quedamos con el resto resto = (NIF Mod 23) + 1 'la línea anterior la podíamos haber complicado 'poniendo esto otro: 'resto = NIF - ((Int(NIF / 23)) * 23) + 1 'Ahora seleccionaremos la letra que 'le corresponda al NIF introducido, 'dependiendo del resto obtenido Select Case resto Case 1, 24 LetraNIF = "T" Case 2 LetraNIF = "R" Case 3 LetraNIF = "W" Case 4 LetraNIF = "A" Case 5 LetraNIF = "G" Case 6 LetraNIF = "M" Case 7 LetraNIF = "Y" Case 8 LetraNIF = "F" Case 9 LetraNIF = "P" Case 10 LetraNIF = "D" Case 11 LetraNIF = "X" Case 12 LetraNIF = "B" Case 13 LetraNIF = "N" Case 14 LetraNIF = "J" Case 15 LetraNIF = "Z" Case 16 LetraNIF = "S" Case 17 LetraNIF = "Q" Case 18 LetraNIF = "V" Case 19 LetraNIF = "H" Case 20 LetraNIF = "L" Case 21 LetraNIF = "C" Case 22 LetraNIF = "K" Case 23 LetraNIF = "E" End Select End If End Function |
Y llamaremos a la función, desde excel, de la siguiente forma:
| =letraNIF(celda) |
celda: corresponde a la celda donde tenemos el NIF/DNI (A12, B33, M58, o la celda de que se trate), aunque podemos poner directamente el NIF/DNI en la fórmula, sin necesidad de referenciarlo a una celda.
Quizás te preguntes: "¿Se puede resumir esa función, para que no sea tan larga?". La respuesta es sí. Vamos a aplicar esta otra función que hace exactamente lo mismo, pero con menos líneas de código (ojo, a esta nueva función la llamaremos "LetradelNIF", mientras que la que acabamos de ver se llama "LetraNIF"):
| Function LetradelNIF(NIF As Long) 'Controlamos que no sea un número 'mayor de 8 cifras If NIF = 0 Or NIF > 99999999 Then letradelNif = "NIF incorrecto" Else 'Tenemos una constante que contiene las 'posibles letras que puede tener un NIF Lista_de_letras = "TRWAGMYFPDXBNJZSQVHLCKE" 'Cogemos el NIF, lo dividimos entre 23 'y nos quedamos con el resto. Luego 'al resto le sumamos 1, y obtenemos de 'la lista de letras, la que corresponda 'con ese número de NIF letradelNif = Mid(Lista_de_letras, (NIF Mod 23) + 1, 1) End If End Function |
Y llamaremos a la función, desde excel, de la siguiente forma:
| =letradelNIF(celda) |
celda: corresponde a la celda donde tenemos el NIF/DNI (A12, B33, M58, o la celda de que se trate), aunque podemos poner directamente el NIF/DNI en la fórmula, sin necesidad de referenciarlo a una celda.
Si no queremos usarla con una función, y preferimos utilizar un inputbox para que el usuario introduzca el NIF/DNI, y nosotros devolvamos la letra en una celda cualquiera, podemos utilizar este procedimiento (en el ejemplo pondremos la letra que le corresponda al NIF/DNI introducido, en la celda B18):
| Sub calcular_letra_del_NIF() NIF = InputBox("Introduce el NIF cuya letra quieres calcular:", "NIF") 'Controlamos que no sea un número 'mayor de 8 cifras If NIF = 0 Or NIF > 99999999 Then Range("B18") = "NIF incorrecto" Else 'Tenemos una constante que contiene las 'posibles letras que puede tener un NIF Lista_de_letras = "TRWAGMYFPDXBNJZSQVHLCKE" 'Cogemos el NIF, lo dividimos entre 23 'y nos quedamos con el resto. Luego 'al resto le sumamos 1, y obtenemos de 'la lista de letras, la que corresponda 'con ese número de NIF Range("B18") = Mid(Lista_de_letras, (NIF Mod 23) + 1, 1) End If End Sub |
¿Y si lo queremos a través de un bonito UserForm?. ...¡¡¡Joder, sí que pedís cosas!!!. Bueno, vale, aquí os dejo la otra opción, la de utilizar un UserForm. Para los que no lo sepan, aquí explico como crear un USerForm. Para ello, crearemos un UserForm como este (doble clic en la imagen, para verla más grande), al que llamaremos Letra_NIF:
Haremos clic en el botón del UserForm llamado "Calcular la letra", y pegaremos este código:
| Private Sub CalcularLetra_Click() On Error GoTo Fin 'Si el número del NIF está vacío If NumeroNIF = Empty Then NumeroNIF.SetFocus MsgBox (Chr(13) + " Por favor, introduce el número del N.I.F. " _ + Chr(13) + Chr(13)), vbOKOnly, " Datos incompletos" End If 'Si el número del NIF no es numérico If Not IsNumeric(NumeroNIF) Then 'que elimine la entrada NumeroNIF = Empty NumeroNIF.SetFocus End If 'Si el número del NIF tiene separador de miles, 'lo envía a la Linea1 If NumeroNIF = Format(NumeroNIF, "#,##0") Then GoTo Linea1 End If 'Si el formato es distinto del numérico If NumeroNIF <> Format(NumeroNIF, "##0") Then 'que elimine las entrada NumeroNIF = Empty Texto2.Caption = Empty Texto3.Caption = Empty NumeroNIF.SetFocus End If Linea1: 'si el número del NIF es numérico If IsNumeric(NumeroNIF) And NumeroNIF > 0 Then 'le da formato con punto de millar NumeroNIF = Format(NumeroNIF, "##,##0") 'Tenemos una constante que contiene las 'posibles letras que puede tener un NIF Lista_de_letras = "TRWAGMYFPDXBNJZSQVHLCKE" 'Cogemos el NIF, lo dividimos entre 23 'y nos quedamos con el resto. Luego 'al resto le sumamos 1, y obtenemos de 'la lista de letras, la que corresponda 'con ese número de NIF Texto2.Caption = Mid(Lista_de_letras, (NumeroNIF Mod 23) + 1, 1) 'ponemos la etiqueta correspondiente Texto3.Caption = NumeroNIF & "-" & Texto2.Caption End If Fin: End Sub |
Justo debajo de ese código, pondremos este otro:
| Private Sub NumeroNIF_Enter() On Error GoTo Fin 'eliminamos las entradas NumeroNIF = Empty Texto2.Caption = Empty Texto3.Caption = Empty Fin: End Sub |
Y este otro:
| Private Sub NumeroNIF_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) On Error GoTo Fin 'eliminamos las entradas NumeroNIF = Empty Texto2.Caption = Empty Texto3.Caption = Empty Fin: End Sub |
Y este otro también:
| Private Sub cerrar_Click() On Error GoTo Fin 'Descarga el formulario de la memoria Unload Me Fin: End Sub |
Y ya para finalizar, solo nos bastará incluir este código en un módulo:
| Sub calcular_letra_NIF() On Error GoTo Fin Letra_NIF.Show Fin: End Sub |
Desde aquí podéis descargar el fichero de excel, con todo el código que os presento en este artículo.
Publicado por Javier Marco 0 comentarios
Etiquetas: funciones personalizadas, macros en excel
