Estimados lectores de Excel Signum:
Durante unas semanas estaré de vacaciones, por ello no podré contestar a las consultas o dudas que me enviéis.
Pero no os preocupéis, a la vuelta estaré de nuevo con todos vosotros.
Vamos a descansar un poco 🙂
Segu Miguéns
Hola a todos 🙂
Hace pocos días que publiqué mi último post pero hoy aprovecho que tengo algo de tiempo para escribir uno nuevo.
Será un post muy breve dado que se trata de la modificación del código de un post anterior: INSERTAR FILAS INTERCALADAS EN BLANCO EN EXCEL
Y el motivo de la modificación del código es una consulta recibida en la que me preguntaban “buenísimo! y si quiero que lo haga a nivel celda en una sola columna? =)”
Pues es relativamente sencillo, dado que en el código anterior insertábamos filas en blanco, en esta versión insertaremos celdas en blanco en la columna seleccionada.
Siguiendo con el ejemplo, tenemos la siguiente tabla:
Y lo que vamos a hacer es intercalar una celda en blanco en las columnas de “Jueves” y “Sábado”. Para ello, vamos a utilizar
Sub Insertar_celdas()
'Definimos variables
Dim tTop, rFila, i, j As Double
'Buscamos el número de columna en el que estamos
nColum = ActiveCell.Column
'Buscamos la letra de la columna en la que estamos
lColum = Split(ActiveCell.Address, "$")(1)
'Determinamos el rango para definir la longitud del bucle for-next
Rango = lColum & ":" & lColum
'Indicamos el número de línea sobre el que comenzamos a añadir celdas en blanco
tTop = 3
'Indicamos el número de celdas en blanco a incluir
rFila = 1
fin = Application.CountA(ActiveSheet.Range(Rango))
For j = 2 To fin
'Si la primera celda (en este caso la 3) contiene un valor,
'entonces comenzamos a insertar celdas intercaladas
If Not IsEmpty(Cells(tTop, nColum)) Then
For i = 1 To rFila
Cells(tTop, nColum).Insert Shift:=xlDown
Next i
tTop = tTop + rFila + 1
End If
Next j
End Sub
Para acabar, en el ejemplo que os dejo, he utilizado, al igual que en la entrada anterior, otra macro para activar esta macro:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.OnKey "{ESCAPE}", "Insertar_celdas"
End Sub
La macro se activa pulsando la tecla “Escape”. Pero podéis ejecutarla perfectamente vinculándola a un botón o ejecutándola directamente sobre el código.
Es decir, os posicionáis con el cursor encima de la columna “E” (Jueves) y pulsáis la tecla “Esc” y hacéis lo mismo con la columna “G” (Sábado).
El resultado es el siguiente:
Y ya está, como habéis podido observar, solo hemos cambiado la línea de código que insertaba filas vacías por esta otra:
Cells(tTop, nColum).Insert Shift:=xlDown
Y eso es todo, una pequeña modificación en la macro y obtenemos lo que necesitamos 🙂
Dado que estoy utilizando una macro que usa la combinación de teclas para activar la macro principal, a la hora de descargar el archivo os generará un error en tiempo de ejecución del método On Key. Este se puede corregir usando On Error Resume Next:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.OnKey "{ESCAPE}", "Insertar_celdas"
On Error GoTo 0
End Sub
Pero no tiene mucho sentido dado que solo os ocurrirá cuando descarguéis el archivo, de todas formas os lo comento para vuestra información.
Una vez guardado, no se volverá a reproducir este error. O, como ya dije, podéis ejecutarlo vinculándolo a un botón o cualquier otro evento.
Descarga el archivo de ejemplo pulsando en: INSERTAR CELDAS INTERCALADAS EN BLANCO EN EXCEL
Hola a todos 🙂
Espero que os vaya muy bien!. En el post de hoy vamos a trabajar con ADO para dar formato de fecha a una cadena de texto.
El tema de las fechas aunque puede parecer sencillo, siempre es susceptible de complicarse. Y en muchas ocasiones el problema se debe al formato con el que llegan los datos que nos envían. En el post de hoy vamos a trabajar uno de esos formatos.
Vamos a ilustrarlo con un ejemplo, imaginad que abrís una petición en la red a favor del control de horarios y ruidos de los locales de ocio nocturno (¡todo un reto!). Y la aplicación que ha recogido los datos os envía un archivo con la siguiente información:
Efectivamente, los campos NOMBRE, PETICIÓN y FIRMA no tienen nada de particular, pero el campo FECHA DE FIRMA debería contar con un formato de fecha y si embargo nos llega como un texto. Esto es un problema, dado que a priori no vamos a poder trabajar fácilmente con este tipo de fechas y es más, necesitamos exportar algunas de las columnas del fichero a otra aplicación para generar otro tipo de informaciones.
Hemos elegido ADO para transferir la información de la hoja BASE a la hoja INFORMACIÓN y será en ese proceso en el que vamos a utilizar SQL para formatear las fechas y también controlar aquellas celdas que se encuentren vacías.
Para hacer el trabajo, os dejo esta macro que es capaz formatear solo las celdas que contienen la fecha:
Sub CONEXION_SQL_FECHAS()
'Declaramos variables
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection
Dim Fin As Integer, i As Long, MiLibro As String, Tit As String
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Eliminamos datos de hoja INFORMACION anteriores
Sheets("INFORMACION").Select
With Sheets("INFORMACION")
'Eliminamos datos de la consulTa SQL anterior
Fin = Application.CountA(.Range("A:A"))
If Fin > 0 Then .Range("A2:D" & Fin).ClearContents
'Realizamos consulta SQL, y componemos un string para crear los datos y darle formato de fecha
obSQL = "SELECT [BASE$].[NOMBRE], [BASE$].[PETICION], [BASE$].[FIRMA], " & _
"IIF(NOT ISNULL([BASE$].[FECHA DE FIRMA]),CDATE(MID([BASE$].[FECHA DE FIRMA],1,2) & '/' & MID([BASE$].[FECHA DE FIRMA],3,2) & '/' & MID([BASE$].[FECHA DE FIRMA],5,4)),NULL) AS [FECHA DE FIRMA] " & _
"FROM [BASE$] "
MiLibro = ActiveWorkbook.Name
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Grabamos la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'Pegamos datos y añadimos encabezados
Do Until Dataread.EOF
Dataread.MoveFirst
.Cells(2, 1).CopyFromRecordset Dataread
For i = 0 To Dataread.Fields.Count - 1
Tit = Dataread.Fields(i).Name
.Cells(1, i + 1) = Tit
Next
Loop
'Por seguridad formateamos la columna FECHA DE FIRMA a fecha
.Columns("D:D").NumberFormat = "m/d/yyyy"
'liberamos y desconectamos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End With
Application.ScreenUpdating = True
End Sub
Como podéis observar, usamos ADO para importar la información de una hoja a la otra, y en el proceso aprovechamos para realizar los cambios y controles necesarios. En concreto, en esta sentencia SQL:
"IIF(NOT ISNULL([BASE$].[FECHA DE FIRMA]),CDATE(MID([BASE$].[FECHA DE FIRMA],1,2) & '/' & MID([BASE$].[FECHA DE FIRMA],3,2) & '/' & MID([BASE$].[FECHA DE FIRMA],5,4)),NULL) AS [FECHA DE FIRMA]
Donde controlamos que solo sean formateadas las celdas que contienen datos usando un IIF que valida si son nulas. Si no lo son, componemos una nueva cadena de texto creando la fecha y aplicamos la función CDate que nos devolverá una expresión tipo fecha, ¡Justo lo que queremos!.
Una vez ejecutada la macro, el resultado es el siguiente:
Este es un ejemplo muy concreto donde los datos a formatear deben ser texto, de hecho, cuando no nos envían la información con formato de fecha, casi siempre viene así.
Antes de finalizar, os recuerdo la necesidad de marcar en las referencias la librería de ADO Activex Data Objects 2.8 Library, es importante que lo hagáis, de lo contrario, la macro no va a funcionar:
Y eso es todo, espero que os resulte de utilidad a la hora de trabajar con fechas en Excel 🙂
Descarga el archivo de ejemplo pulsando en: APLICAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA
Hola a todos:
Aunque en esta web suelo publicar muchos contenidos basados en programación VBA, también me gusta dar protagonismo a las fórmlas de Excel. Y es que en muchas (muchísimas) ocasiones, una fórmula puede ser más efectiva que usar código, ya sea por tiempo, por hábito, etc. El saber utilizarlas y combinarlas, hacen que sean una alternativa al uso de macros (aunque para algunos ejercicios concretos solo la programación nos puede ayudar).
Por lo tanto, hoy me gustaría contestar a la pregunta: ¿Qué fórmulas puedo usar para indicar que un valor está en un rango determinado de celdas?.
Pues bien, existen varias fórmulas para hacerlo, algunas con restricciones como que solo funcionan con datos numéricos o que solo es posible utilizar una columna como rango de búsqueda y otras que realizar el trabajo sin los límites comentados.
Aunque seguramente podría desarrollar otras fórmulas más complejas, finalmente me quedo con 9 métodos para realiza este trabajo. Vamos a ver en detalle cada una de ellos 🙂
Imaginad que tenéis dos columnas, una denominada RANGO y otra denominada DATO, y lo que queremos es utilizar una función que nos permita indicar cuáles son los valores de la columna DATO que se encuentran en la columna RANGO y en caso de existir coincidencia pondremos un “SI” y si no están un “NO” en la columna FÓRMULA:
También os especificaré si el rango de búsqueda puede aplicarse en varias columnas o únicamente funciona seleccionando una única columna.
Para el primer ejemplo vamos a utilizar las funciones CONTAR.SI y SI, que nos van a permitir contar las veces que el valor buscado se repite en la columna RANGO por lo tanto, si es mayor que cero indicamos “SI”, de lo contrario será “NO”. El rango de búsqueda pueden ser varias columnas:
=SI(CONTAR.SI($A$4:$A$11;B4)>0;"SI";"NO")
En el segundo ejemplo vamos a usar las función COINCIDIR, SI y SI.ERROR de manera que si la posición del valor buscado al coincidir en el rango de búsqueda es mayor que cero indicará que existe y lo marcamos como un “SI”, de los contrario (si es un error) será “NO”. El rango de búsqueda debe ser una única columna:
=SI.ERROR(SI(COINCIDIR(F4;$E$4:$E$11;0)>0;"SI");"NO")
En el tercer ejemplo vamos a usar las funciones BUSCARV, SI y ESERROR. Usando BUSCARV buscamos el valor de DATO en la columna rango, si devuelve el mismo valor es que existe, por lo tanto indicamos un “SI”, si lo que devuelve es un error, marcamos un “NO”. El rango de búsqueda debe ser una única columna:
=SI(ESERROR(BUSCARV(J4;$I$4:$I$11;1;0));"NO";"SI")
En el cuarto ejemplo vamos usar las funciones SUMAPRODUCTO y SI. De forma que si la función SUMAPRODUCTO nos devuelve un valor mayor a 0 marcaremos “SI”, de lo contrario marcamos un “NO”. El rango de búsqueda pueden ser varias columnas:
=SI(SUMAPRODUCTO(($M$4:$M$11=N4)*1)>0;"SI";"NO")
En el quinto ejemplo vamos a usar las funciones SUMAR.SI y SI. La función SUMAR.SI en el momento que encuentre coincidencia sumará las veces que se repite dicho valor, si es mayor que 0 marcaremos que “SI”, en caso contrario marcamos “NO”. Esta función solo es válida si trabajamos con datos numéricos y permite que el rango de búsqueda puedan ser varias columnas:
=SI(SUMAR.SI($Q$4:$Q$11;R4;$Q$4:$Q$11)>0;"SI";"NO")
Como habéis visto, combinamos varias funciones para optener el resultado esperado. Al tener que codificar un “SI” o un “NO” (o cualquier indicativo que estimemos oportuno) nos fuerza a usar el SI condicional, que se usa en todas las funciones, y como algunas fórmulas arrojan error cuando no encuentran el dato, debemos usar las funciones ESERROR y SI.ERROR para controlar el error y codificar el valor que necesitamos.
Además de estas funciones, existe otra variante con funciones matriciales que también nos pueden ofrecer el mismo resultado. Os muestro cuatro posibilidades (las de rojo) y os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2, seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR
El sexto ejemplo es una fórmula matricial, usamos las funciones SI y O, determinando que si el valor buscado existe en el rango indicaremos un “SI” y en caso contrario un “NO”. Permite que el rango de búsqueda puedan ser varias columnas:
{=SI(O($A$17:$A$24=B17);"SI";"NO")}
El séptimo ejemplo es una variante de la fórmula matricial anterior, solo que combinamos con la función IGUAL, lo que nos va a permitir distinguir mayúsculas de minúsculas para búsquedas más exhaustivas. Permite que el rango de búsqueda puedan ser varias columnas:
{=SI(O(IGUAL($E$17:$E$24;F17));"SI";"NO")}
El octavo ejemplo es una fórmula (matricial) en la que usamos las funciones SI y SUMA de forma que sumamos las veces en las que aparece el valor en el rango de búsqueda. Si el valor es diferente de cero será verdadero y codificaremos “SI”, en caso contrario será falso y codificaremos “NO”. Permite que el rango de búsqueda puedan ser varias columnas:
{=SI(SUMA(($I$17:$I$24=J17)*1);"SI";"NO")}
La novena y última fórmula (matricial) combina las funciones SI y CONTAR. Cuando el valor que arroje la función sea mayor que cero indicará que el valor existe y por lo tanto marcamos un “SI”, en caso contrario un “NO”. Esta función solo es válida si trabajamos con datos numéricos y permite que el rango de búsqueda puedan ser varias columnas:
{=SI(CONTAR(SI(($M$17:$M$24=N17);$M$17:$M$24))>0;"SI";"NO")}
Como habéis podido observar, existen bastantes alternativas para saber si un valor está en un rango determinado, cada una con sus especificaciones para que podáis adaptarlas a vuestros trabajos y proyectos.
Por otra parte, en todos los casos podéis prescindir de las condiciones y las funciones de error para que veáis el resultado real que arroja la fórmula, de forma que algunas las podríais modificar para saber, por ejemplo, si el valor buscado se encuentra en el rango y si se repite varias veces (pero eso ya lo veremos en otro momento).
Es un post bastante extenso, pero creo que merece la pena hacerlo 🙂
Os dejo un archivo con el ejemplo de todas las fórmulas vistas y un resumen de algunas de sus características:
Como siempre, espero que os sea de utilidad.
Descarga el archivo de ejemplo pulsando en: FÓRMULAS PARA INDICAR SI UN VALOR EXISTE EN UN RANGO
Hola a todos, espero que estéis bien y supongo que ya disfrutando de vacaciones (a mí aún me quedan unas semanas para comenzarlas).
En el post de hoy vamos a tratar de resolver una consulta que me enviaron la semana pasada sobre un tema que ya hace tiempo que tenía ganas de abordar. La pregunta era cómo se podía calcular la edad en un formulario de Excel.
Bien, existen varios post sobre el cálculo de la edad en esta web, y en todas ellas usamos la función SIFECHA() de excel, que normalmente la usamos en la hoja y en menor medida en VBA. En VBA esta función se denomina DATEDIF (y NO es lo mismo que otra función denominada DATEDIFF, que trabaja con valores absolutos).
Esta función, si utilizamos la grabadora de datos para obtener su traducción en VBA sería algo así:
AÑO = "=DATEDIF(RC[-12],TODAY(),""Y"")"
Es decir, nos ofrece la posibilidad de generar la fórmula en la hoja desde VBA. Pero lo cierto es que queremos trabajar con esta función sin acudir a la hoja en ningún momento, simplemente utilizar el formulario y los parámetros que hayamos incluido para calcular la edad de una persona.
Aunque esta función no existe en VBA como tal, podemos invocarla a través del método EVALUATE, aunque eso lo iremos viendo en el caso particular que nos ocupa.
Vamos a imaginar que tenemos un formulario con un cuadro de texto (textbox1) en el que debemos incluir la fecha de nacimiento, un botón de comando para ejecutar la macro y otro cuadro de texto (textbox2) en el que vamos a mostrar la edad completa:
Para calcular la edad vamos a incluir en modulo asociado al botón de comando el siguiente código:
Private Sub CommandButton1_Click()
'Definimos variables
Dim validarfecha As Boolean
Dim hoy As String
Dim f_nac As String
Dim año As Double, mes As Double, dia As Double
'Trabajamos con el formulario1
With UserForm1
'Cada vez que ejecutemos la macro vaciaremos el textbox1
.TextBox2 = Empty
'Si no existe fecha o el dato no es una fecha o está mal escrita, activamos mensaje y salimos el cálculo
validarfecha = IsDate(.TextBox1.Value)
If .TextBox1.Value = Empty Or validarfecha = False Or Len(.TextBox1.Value) > 10 Then
MsgBox ("DEBES INTRODUCIR UNA FECHA Y VERIFICAR QUE EL FORMATO SEA EL ADECUADO"), vbExclamation, "CONTROL"
Exit Sub
End If
'Si la fecha es mayor que la fecha actual, activamos mensaje y salimos el cálculo
If CDate(.TextBox1.Value) > Date Then
MsgBox ("LA FECHA NO PUEDE SER MAYOR QUE EL DÍA ACTUAL"), vbExclamation, "CONTROL"
Exit Sub
Exit Sub
End If
'Indicamos la fecha de hoy con formato mm/dd/aaaa y lo componemos en un string
hoy = (Month(Date) & "/" & Day(Date) & "/" & Year(Date))
'Indicamos la fecha de hoy con formato mm/dd/aaaa y lo componemos en un string
f_nac = (Month(.TextBox1.Value) & "/" & Day(.TextBox1.Value) & "/" & Year(.TextBox1.Value))
'Evaluamos la función Datedif para año, mes y día
año = Evaluate("DATEDIF(""" & f_nac & """,""" & hoy & """,""Y"")")
mes = Evaluate("DATEDIF(""" & f_nac & """,""" & hoy & """,""YM"")")
dia = Evaluate("DATEDIF(""" & f_nac & """,""" & hoy & """,""MD"")")
'Pasamos el string con los años, meses y días de nuestra edad actual al textbox2
.TextBox2 = año & IIf(año = 1, " año", " años") & ", " & mes & IIf(mes = 1, " mes", " meses") & " y " & dia & IIf(dia = 1, " dia", " días")
End With
End Sub
Es importante tener en cuenta que la función reconoce la fecha con formato mm/dd/aaaa por lo que debemos especificar esto tanto para la variable que contiene la fecha actual “hoy” como para la que contiene la fecha de nacimiento “f_nac”. Esto lo podemos solucionar componiendo un string con la fecha en el orden indicado.
En el cuadro de texto de la fecha de nacimiento el formato es el habitual: días, meses y años. Si necesitáis introducir los datos con otro formato, tendréis que modificar la macro en los dos string creados para que la función lea adecuadamente la información.
Luego debemos adaptar la función para trabajar con variables y evaluarla con el método Evaluate. Importante el uso de las comillas.
El resultado es el siguiente (calculado sábado 22 de Julio de 2017 a las 23:22 horas):
Y el resultado es el correcto (y también lo es que me voy haciendo más viejo). El formulario me muestra información completa de la edad que tengo en el momento de ejecutar el código.
He incluido en la macro varios controles: si no existe fecha, si la fecha está mal escrita o si es mayor que el día actual. Si sucede alguna de estas cosas, mostrará un mensaje y proceso finalizará.
Según configuraciones regionales las fechas tendrán formatos distintos, sin embargo, creo que adaptando la macro a meses, días y años funcionará en todos los lugares (siempre que escribamos la fecha de nacimiento con día, mes y año.
Pues esto ha sido todo, espero que os sea de utilidad a la hora de crear formularios o de poner en práctica la función Datedif en VBA.
Descarga el archivo de ejemplo pulsando en: CALCULAR LA EDAD EN UN FORMULARIO DE EXCEL
Hola a todos 🙂
Siguiendo con una consulta recibida el fin de semana pasado, un lector solicitaba una macro que recorriese varias columnas y otro bucle anidado que recorriese las celdas de cada columna. A su vez preguntaba la forma de detectar un importe determinado y las aproximaciones según un rango establecido.
Aunque ya existen en la web algunas macros que realizar este trabajo en parte (realizando algunas modificaciones), creo que puede ser interesante compartir con vosotros un nuevo desarrollo.
Para ejemplificar el ejercicio, imaginad que tenéis una tabla varias columnas identificadas con los meses del año, y en cada columna una serie de importes (podéis poner la moneda que queráis, yo lo he dejado con euros):
Pues bien, necesitamos una macro que nos busque un importe determinado que vamos a colocar en la celda “N2” en el campo denominado IMPORTE y además que detecte aquellos valores que se aproximen a ese número según un rango que establezcamos en la celda “O2” en el campo denominado “APROX”.
Para este ejemplo vamos a buscar aquellas celdas que sean igual a 50.000 euros y con aproximaciones de 5.000 euros.
Es decir, debemos marcar además de los 50.000 euros (Rojo), cualquier cifra hasta los 55.000 euros (Verde) y cualquier cifra hasta los 45.000 euros (Azul).
Para ello vamos a usar esta macro que he programado con la que realizaremos el trabajo que nos han pedido:
Sub BUSCAR_VALORES_APROXIMADOS()
'Definimos variables
Dim i As Integer
Dim nNUM As Double
Dim sVAR As Double
'Desactivamos actualizacion de pantalla
Application.ScreenUpdating = False
'Hacemos referencia a la Hoja1 para los cálculos
With Sheets("Hoja1")
'Eliminamos colores de relleno anteriores
.Range("A2", Range("A2").End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Interior.Pattern = xlNone
'Incluimos en las variables cifra a buscar y aproximaciones
nNUM = .Range("N2")
sVAR = .Cells(2, 15)
'Determinamos cuantas columnas tenemos (datos consecutivos)
Fin = Application.CountA(.Range("A1", Range("A1").End(xlToRight)))
'Iniciamos primer bucle por columnas
For i = 1 To Fin
'Determinamos el rango de cada columnas, desde la celda 1 hasta el final con datos
Final = Application.CountA(.Range(Cells(1, i), Cells(1, i).End(xlDown)))
'Iniciamos segundo bucle por celda en cada columna
For j = 2 To Final
'Si el valor de la celda es igual a nNUM marcamos en rojo
If .Cells(j, i) = nNUM Then
.Cells(j, i).Interior.Color = vbRed
'Si el valor de la celda esta entre nNUM y la aproximación en "positivo" marcamos en verde
ElseIf .Cells(j, i) <= nNUM + sVAR And .Cells(j, i) > nNUM Then
.Cells(j, i).Interior.Color = RGB(51, 204, 51)
'Si el valor de la celda esta entre nNUM y la aproximación en "negativo" marcamos en azul
ElseIf .Cells(j, i) >= nNUM - sVAR And .Cells(j, i) < nNUM Then
.Cells(j, i).Interior.Color = RGB(60, 144, 246)
End If
Next
Next
.Range("N2").Select
End With
Application.ScreenUpdating = True
End Sub
Como podéis ver, según vais leyendo los comentarios de la macro, mediante dos bucles, uno de ellos anidado, vamos buscando por cada celda según la columna seleccionada los valores que hemos indicado en el condicional.
Esto nos permitirá modificar las condiciones y los parámetros para adaptar el código a nuestras necesidades.
El resultado que se muestra es este:
Para este ejercicio he utilizado un loop for – next, pero se podría realizar con otros tipos de bucle y otros métodos de búsqueda, por ejemplo con el método Range.Find, etc.Pero creo que esta forma es más adecuada según los parámetros de la consulta.
Es interesante su uso en aplicaciones o informes con grandes cantidades de información y en el que necesitemos resaltar, extraer o modificar valores determinados.
Descarga el archivo de ejemplo pulsando en: REALIZAR BUCLES ANIDADOS Y BUSCAR VALORES APROXIMADOS
Hola a todos:
En muchas de las consultas que recibo, un tema recurrente es el de la confección de informes. Normalmente siempre preguntas y referencias a las tablas dinámicas (campos calculados, orden de etiquetas, etc…).
Y es que realmente la función de las tablas dinámicas es esa: mostrar información dinámica en la confección informes.
Sin embargo, una buen alternativa a las tablas dinámicas es hacer nuestro informe utilizando fórmulas y siempre con la ventaja de que no vamos a depender de las limitaciones típicas de las tablas dinámicas (colocación de información, límites, etc).
Por ello, en el post de hoy me gustaría trabajar con algunas funciones que nos van a permitir realizar interesantes informes y sin necesidad de usar tablas dinámicas.
Utilizaremos una de las bases de datos que uso habitualmente como ejemplo, una plantilla ficticia de unos grandes almacenes (si quereis saber cómo he generado los nombres, podeís visitar este post: COMBINAR LA FUNCIÓN HALLAR Y LA FUNCIÓN LARGO)
Imaginad que tenemos esta base de datos:
Y necesitamos utilizar las fórmulas adecuadas para completar los datos de este informe (que ahora está vacío):
Para los campos de Sexo, Idioma y Estudios necesitamos funciones que cuenten y a su vez que admitan varios criterios de condición.
Para el campo de la edad necesitamos funciones que puedan obtener promedios, máximos y mínimos.
He creado cuatro hojas con el mismo informe, en cada una trabajaremos una fórmula distinta para contar.
En la hoja INFORME_SUMAPRODUCTO utilizaremos la función SUMAPRODUCTO, la cual vamos a modificar para que cuente los datos que necesitamos. Por ejemplo el número de Hombres en la sección de Deportes:
Solo tenemos que seleccionar un rango de la columna “C” (SECCION) de la Hoja BBDD y que sea igual a la celda A3 de nuestro informe (Deportes) e introducimos un segundo criterio, seleccionando la columna E de la hoja BBDD (SEXO) y la celda B2 de nuestro informe (Hombre).
=SUMAPRODUCTO((BBDD!$C$2:$C$65000=A3)*1;(BBDD!$E$2:$E$65000=$B$2)*1)
Para el resto de campos va a ser igual, excepto en IDIOMA (OTROS), donde debemos especificar que el idioma debe ser diferente de INGLES, FRANCES Y ALEMAN:
=SUMAPRODUCTO((BBDD!$C$2:$C$65000=A3)*1;(BBDD!$F$2:$F$65000<>$E$2)*1;(BBDD!$F$2:$F$65000<>$F$2)*1;(BBDD!$F$2:$F$65000<>$D$2)*1)
Como podéis observar, vamos introduciendo las condiciones de forma anidada y vinculando con el idioma que queremos excluir del proceso de contar empleados.
En la hoja INFORME_CONTAR.SI.CONJUNTO utilizaremos la función CONTAR.SI.CONJUNTO, presente desde Excel 2007 y es una buena opción para contar los elementos de nuestro informe. Siguiendo el ejemplo anterior (número de Hombres en la sección de Deportes) sería así:
=CONTAR.SI.CONJUNTO(BBDD!$C$2:$C$65000;A3;BBDD!$E$2:$E$65000;$B$2)
En la sintaxis seleccionamos rango de la columna SECCIÓN de la hoja BBDD y que sea igual a A3 y también seleccionamos la columna SEXO y que sea igual a B2 (Hombre).
Para el resto de campos va a ser igual, excepto en el IDIOMA (OTROS) donde usaremos la misma fórmula pero indicando los idiomas que no queremos que se tengan en cuenta:
=CONTAR.SI.CONJUNTO(BBDD!$C$2:$C$65000;A3;BBDD!$F$2:$F$65000;”<>” & $E$2;BBDD!$F$2:$F$65000;”<>” & $F$2;BBDD!$F$2:$F$65000;”<>” & $D$2)
Efectivamente usamos las clausulas <> para indicar que cuente aquellos que sea distintos de INGLES, FRANCES Y ALEMAN.
Pues bien, con cualquiera de estas fórmulas podríais realizar el informe perfectamente. Pero me gustaría dejaros otras dos fórmulas (matriciales) para realizar el mismo proceso:
En la hoja INFORME_MATRICIAL utilizaremos una matricial muy sencilla que realiza prácticamente el mismo trabajo que SUMAPRODUCTO (pero en matricial).
Usamos el mismo ejemplo que el de las fórmulas anteriores: Número de hombres en la sección “Deportes”:
{=SUMA((BBDD!$C$2:$C$65000=A3)*(BBDD!$E$2:$E$65000=$B$2))}
Y el resultado será el mismo que en las fórmulas anteriores, es decir 7.
El resto de campos será igual pero cambiando los parámetros, excepto en IDIOMA (OTROS), aquí tendremos que ampliar la fórmulas con más criterios:
{=SUMA((BBDD!$C$2:$C$65000=A3)*(BBDD!$F$2:$F$65000<>$E$2)*(BBDD!$F$2:$F$65000<>$F$2)*(BBDD!$F$2:$F$65000<>$D$2))}
Indicando qué idioma no queremos contar. Esta matricial, me parece muy sencilla y a la vez efectiva, es una de mis favoritas 🙂
En la hoja INFORME_MATRICIAL_2 utilizaremos otra matricial pero en la que usaremos la función CONTAR.
Para el cálculo del número de hombres en la sección “Deportes”, sería así:
{=CONTAR(SI((BBDD!$C$2:$C$65000=A3);SI(BBDD!$E$2:$E$65000=$B$2;BBDD!$A$2:$A$65000)))}
Sin embargo, esta fórmula tiene una peculiaridad, solo va a contar si el rango sobre el que se cuenta es numérico, en nuestro ejemplo podríamos hacer referencia a la columna A o a la D, en nuestro caso, he marcado la A en rojo en la fórmula. Por ello no la podréis usar si vuestra base de datos no tiene una columna con datos numéricos, tipo edad, ID, etc.
Para el contar las celdas de IDIOMA (OTROS), tendremos que incluir los criterios en la función, así:
{=CONTAR(SI((BBDD!$C$2:$C$65000=A3);SI(BBDD!$F$2:$F$65000<>$E$2;SI(BBDD!$F$2:$F$65000<>$F$2;SI(BBDD!$F$2:$F$65000<>$D$2;BBDD!$A$2:$A$65000)))))}
Y el resultado es el mismo para todos los casos:
Aunque no era el objeto de este post, os he incluido tres fórmulas más (matriciales) para calcular el promedio, el máximo y el mínimo de la edad por cada sección.
Para calcular el Promedio:
{=REDONDEAR(PROMEDIO(SI(BBDD!$C$2:$C$65000=A3;BBDD!$D$2:$D$65000));0)}
y usamos la función redondear para eliminar todos los decimales (podríamos usar entero).
Para el cálculo de Max:
{=REDONDEAR(MAX(SI(BBDD!$C$2:$C$65000=A3;BBDD!$D$2:$D$65000));0)}
Para el cálculo de Min:
{=REDONDEAR(MIN(SI(BBDD!$C$2:$C$65000=A3;BBDD!$D$2:$D$65000));0)}
El resultado es este (el mismo para las cuatro pestañas):
Y las cuatro pestañas tienen que mostrar la misma información.
Hemos utilizado fórmulas distintas para contar numero de empleados por sección o departamento, según sexo, idioma o estudios. Y también hemos calculado algunos estadísticos para enriquecer la información.
Os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2 , seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR
Descarga el archivo de ejemplo pulsando en: ALGUNAS FÓRMULAS PARA CONFECCIONAR NUESTROS INFORMES EN EXCEL
