GIRAR 180 GRADOS EL CONTENIDO DE UN RANGO SELECCIONADO EN EXCEL

Hola  a todos!.

Hace unos días un lector me enviaba una consulta acerca de la posibilidad de mostrar información en una celda en modo “imagen reflejada”, esto es, girar los caracteres de una palabra o frase 180 grados. La utilidad en estos casos suele ser para imprimir esa información y aplicarla en diferentes contextos, por ejemplo las ambulancias, que lo usan en el frontal del vehículo para que los conductores a los que necesita adelantar puedan leer que se trata de una ambulancia.

Si bien, para realizar esta tarea las impresoras suelen estar dotadas esta función, normalmente es necesario activarla en las características de la impresora o en el acabado de la hoja, etc …, en algunos casos o bien no existe esa posibilidad o bien la opción está desactivada por un administrador y no podemos realizar nuestro trabajo.

En Excel, se pueden girar los caracteres en una celda, pero el giro está limitado hasta los 90 grados, de forma que no lo podremos hacer directamente desde el menú. Pero existe una opción que sí nos permitiría girar los 180 grados, y es pasando el contenido de la celda a imagen y luego realizar el giro (en giro 3D):

GIRAR 180 GRADOS EL CONTENIDO DE UN RANGO SELECCIONADO EN EXCEL

Siguiendo con este método, podemos utilizar un poco de programación para que sea un poco más automático, de forma que solo tendremos que seleccionar el área o rango de celdas con la información y con tan solo pulsar un botón obtendremos los datos tal y como los necesitamos.

Imaginad que tenemos este texto y lo queremos mostrar reflejado:

GIRAR 180 GRADOS EL CONTENIDO DE UN RANGO SELECCIONADO EN EXCEL1

Para realizar esta tarea vamos a utilizar este código:

Sub GENERAR_IMAGEN_REFLEJADA()
'Definimos variables
Dim Area As Object
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Controlamos que existan datos en la selección
On Error GoTo Control
With ActiveSheet
Set Area = Application.Intersect(Selection, .UsedRange)
Area.Select
'Centramos horizontal y verticalmente el texto seleccionado
With Area
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
'Copiamos la selección y la pegamos en "C2"
Selection.Copy
.Range("C2").Select
.Pictures.Paste.Select
End With
'Rotamos la imagen 180 grados
With Selection
.ShapeRange.ThreeD.RotationX = -180
.Copy = False
End With
'Si no hemos seleccionado datos, mostramos mensaje de advertencia
Control: If Err.Number = "91" Then MsgBox ("EL RANGO SELECCIONADO NO CONTIENE DATOS"), vbExclamation, "SELECCIONA RANGO"
Application.ScreenUpdating = True
End Sub

Para este ejemplo he dejado un botón de comando para que probéis, pero también se podría modificar para ejecutarlo pulsando una tecla.

El resultado es el siguiente:

GIRAR 180 GRADOS EL CONTENIDO DE UN RANGO SELECCIONADO EN EXCEL2

Seleccionáis desde A2 a A3 y al pulsar el botón se generará la imagen reflejada automáticamente.

La imagen se pegará a partir de la celda “C2” (podéis especificar el lugar que más os convenga) y luego tan solo tendréis que establecer un área de impresión entorno a la imagen e imprimir.

Y eso es todo! 🙂

Descarga el archivo de ejemplo pulsando en: GIRAR 180 GRADOS EL CONTENIDO DE UN RANGO SELECCIONADO EN EXCEL

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Anuncios

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3: ANÁLISIS DE LOS DATOS

Hola a todos!. Espero que todo vaya bien 🙂

Hoy vamos a seguir con la serie de post dedicados a trabajar con grandes bases de datos en Excel. Y en esta ocasión hablaremos sobre el análisis de la información.

Al igual que en las dos partes anteriores, usaremos los datos que estamos usando como ejemplo: Encuesta Europea de Salud en España y los dos post anteriores que podéis consultar son los siguientes:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 1: IMPORTAR LA INFORMACIÓN

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 2: EXTRAER LA INFORMACIÓN

Bien, siguiendo con nuestros ejercicios, llegamos al momento de buscar y obtener información relevante y útil en los datos, es decir, al análisis de la información.

Ni que decir tiene, que aquí podemos aplicar cientos de técnicas, cada una con indicadores y métodos diferentes. Así que voy a elegir una función para realizar un ejemplo complejo de obtener información y mostrarla con gráficos.

La función que voy a desarrollar en VBA es el Coeficiente de Correlación que como veremos, nos va a servir para comprobar si existe relación entre dos variables, y a su vez determinar el tipo de relación (si existe). Esta función necesita 2 matrices (variables) para poder ser calculada, y en nuestro ejemplo, me ha parecido interesante usar la edad y “Consumo de tabaco”, ambas variable se corresponden en nuestros datos con EDADa Y V121 respectivamente.

Una vez que hemos extraído la información seleccionando los datos en el listbox de la primera hoja, tendremos la siguiente información en la hoja “QUERY” y vamos a aprovechar para añadir otra hoja y nombrarla como “CORRELACIONES”.

Ahora que tenemos la estructura preparada, vamos a incluir esta serie de macros en un módulo estándar. Son varias macros, pero en realidad ejecutamos la primera, dado que realizamos varios “Call” al resto de macros. Creo que así se comprende de forma más clara la funcionalidad del código.

Se trata de una macro con cierto nivel de complejidad, así que la iremos viendo poco a poco, estas son las macros:

En primer lugar vamos a ejecutar esta macro que en su proceso va a llamar (“Call”) a otras macros para completar toda la instrucción. Es la macro principal que realiza una conexión ADO y obtiene ordenamos los datos para poder aplicar el coeficiente de correlación entre las diferentes variables.

Sub CONEXION_SQL_CORRELACION()
'Declaramos variables
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection
Dim Fin As Integer, Final As Integer, i As Integer, j As Variant, Comodin As String
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Eliminamos datos de correlaciones anteriores
Sheets("CORRELACIONES").Select
With Sheets("CORRELACIONES")
'Eliminamos imágenes y correlaciones anteriores
Call ELIMINAR_IMAGENES
'Creamos variable para conocer el número más alto del rango indicado
Final = Application.WorksheetFunction.Max(Sheets("QUERY").Range("B:B"))
'Creamos un bucle para generar tantas consultas SQL como máximo sea el rango.
For j = 1 To Final + 1
'Creamos un caracter comodín para mostrar todos los items.
Comodin = "'%" & "%'"
'Cuando superemos el máximo de valores, el valor +1 será el comodín
If j = Final + 1 Then j = Comodin
'Eliminamos datos de la consutla SQL anterior
Fin = Application.CountA(.Range("A:A"))
If Fin > 0 Then .Range("A2:C" & Fin).ClearContents
'Realizamos consulta SQL, en este caso son dos variables, la edad y el habito de fumar según varias preguntas
obSQL = "SELECT [QUERY$].[EDADa], [QUERY$].[V121], COUNT ([QUERY$].[V121]) AS CUENTA " & _
"FROM [QUERY$] " & _
"WHERE [QUERY$].[V121] like " & j & " " & _
"GROUP BY [QUERY$].[EDADa], [QUERY$].[V121] " & _
"ORDER BY [QUERY$].[V121]"
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
'Si el caracter no es el comodín, realizamos la correlación de los datos
If j <> Comodin Then
Call CORRELACION
End If
'Indicamos que no se muestre el error cuando pasamos la variable string
'en el último ciclo del bucle
On Error Resume Next
Next
On Error GoTo 0
'liberamos y desconectamos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
'Movemos las imagenes a la altura de la celda M3
.Pictures.Select
Selection.ShapeRange.IncrementTop -368
.Range("M3").Select
End With
Application.ScreenUpdating = True
End Sub

Con esta macro, realizamos la correlación entre la edad y cada pregunta realizada sobre la frecuencia de consumo de tabaco. Se ejecuta con la macro CONEXION_SQL_CORRELACION.

Sub CORRELACION()
'Definimos variables
Dim CORRELACION As Double, D As Integer, A As Integer
Dim i As Integer, Fin As Integer, Final As Integer
With Sheets("CORRELACIONES")
Final = Application.WorksheetFunction.Max(Sheets("QUERY").Range("B:B"))
Fin = Application.CountA(.Range("A:A"))
.Cells(1, 5) = "ID"
.Cells(1, 6) = "CORRELACION"
.Cells(1, 7) = "R2"
'Con un bucle realizamos la correlación hasta el máximo de rango indicado en "Final"
For i = 1 To Final
'Evitamos errores cuando con los datos no sea posible extraer correlación
On Error Resume Next
CORRELACION = Application.WorksheetFunction.Correl(.Range("A2:A" & Fin), .Range("C2:C" & Fin))
On Error GoTo 0
'Mostramos ID (pregunta de la encuesta)
'Mostramos correlación

If .Cells(2, 2) = i Then
D = Application.CountA(.Range("E:E"))
A = D + 1
.Cells(A, 5) = .Cells(2, 2)
.Cells(A, 6) = CORRELACION
'Generamos gráficos
Call GRAFICO_IMAGEN
End If
Next
End With
End Sub

Desde la macro anterior llamamos a esta otra macro, que generará cada gráfico después de haber realizado cada correlación por Item. Una vez realizado el gráfico, se pasa a imagen y se borra el gráfico. Se ejecuta con la macro “CORRELACION”

Sub GRAFICO_IMAGEN()
'Declaramos variables
Dim D As Integer, X As Double, nITEM As String, r2 As Double
Dim id1 As String, id2 As String
With Sheets("CORRELACIONES")
'Generamos gráficos de dispersión, los configuramos
'y los pasamos a imagenes y eliminamos el gráfico.
D = Application.CountA(.Range("E:E"))
X = Round(D * 30 / 2, 0)
.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range("A:A,C:C")
.ChartObjects.Width = 320
.ChartObjects.Height = 190
'Adaptamos el tipo de puntos en la nube de puntos y tamaño
With ActiveChart.SeriesCollection(1)
.Select
Selection.MarkerStyle = 8
Selection.MarkerSize = 3
ActiveChart.PlotArea.Select
.Trendlines.Add
.Trendlines(1).Select
Selection.DisplayEquation = True
Selection.DisplayRSquared = True
Selection.NameIsAuto = True
'Seleccionamos línea de tendencia Polinomial
'de grado 3, que es el que mejor se ajusta
'a nuestros datos
With Selection
.Type = xlPolynomial
.Order = 3
End With
'Movemos la fórmula y R2 arriba a la derecha
.Trendlines(1).DataLabel.Select
Selection.Left = 178
Selection.Top = 34
End With
'Eliminamos lineas divisorias horizontales
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
'Nombramos el gráfico
nITEM = .Cells(2, 2)
ActiveChart.ChartTitle.Text = nITEM
'Obtenemos Rcuadrado y se aplicamos su raiz cuadrada para
'obtener la correlación correcta

W = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
id1 = InStr(W, "R²")
id2 = InStr(id1, W, "=")
r2 = Trim(Mid(W, id2 + 1))
Sheets("CORRELACIONES").Cells(D, 7) = Round(r2, 2)
'Pasamos el gráfico a imagen
.ChartObjects(1).Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy
.Range("M" & X).Select
.Pictures.Paste.Select
.ChartObjects(1).Delete
End With
End Sub

Con esta macro eliminamos todas las imágenes de tipo “Picture” en la hoja. Se ejecuta con la macro CONEXION_SQL_CORRELACION.

Sub ELIMINAR_IMAGENES()
Dim Shape As Excel.Shapes, Fin As Integer
'Eliminamos todas las imagenes de la hoja CORRELACIONES
With Sheets("CORRELACIONES")
Fin = Application.CountA(.Range("A:A"))
For Each Shapes In .Shapes
With Shapes
If .Type = 13 Then
.Delete
End If
End With
Next
If Fin > 0 Then .Range("E2:G" & Fin).ClearContents
End With
End Sub

En rojo os he marcado aquellas macros que ejecutamos con un procedimiento “Call”. Imaginad que incorporamos todas las macros en una sola (sería demasiado confuso).

Recordamos las preguntas de la encuesta sobre hábitos y consumo de tabaco:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS

Pues bien, este es el resultado de realizar la correlación y generar los gráficos de puntos (pulsad en el gráfico para hacer zoom en la imagen)

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS2

Como podéis observar, en los casos 1, 2, 3 y 4 existe una relación entre ambas variables, NO es una relación lineal (tal y como podéis ver) y además el R cuadrado que mostramos en el gráfico NO se corresponde con la correlación obtenida cuando hemos aplicado el coeficiente de Correlación.

Por lo tanto, si bien podemos apreciar que existe relacion entre las 4 primeras preguntas, no se puede tener como referencia el coeficiente de correlación que hemos calculado en la columna “CORRELACIÓN”, dado que los datos no son lineales y, por ejemplo, en la primera pregunta vemos una correlación de -0.45 cuando la correlación debería ser muy superior.

Para poder obtener la relación correcta, deberíamos calcular la “Razón de Correlación” que se aplica a relaciones no lineales, en este caso curvilíneas. Pero si os habéis fijado, en cada uno de los gráficos tenemos, por una parte la fórmula utilizada para calcular la nube de puntos y por otra el Coeficiente de Determinación o R cuadrado.

R2 es un índice estadístico que establece una medida del grado de asociación lineal entre la variable dependiente y la variable independiente, concretamente entre la variable dependiente y la recta de regresión estimada (o en este caso, la curva de regresión estimada).

Teniendo esto en cuenta y dado que el coeficiente de correlación que hemos calculado no podemos tenerlo en cuenta porque que se trata de datos no lineales, utilizaremos como índice estadístico el R2 para explicar los gráficos. En el primer gráfico, que se corresponde con la primera respuesta “SÍ, FUMO A DIARIO”. Podemos observar como R2 es de un 90% y podemos ver como la nube de punto se agrupa en torno a la línea de tendencia.

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS_PRIMERA PREGUNTA
Según este gráfico, podemos establecer que los casos de personas que afirman fumar todos los días aumentan a medida que su edad aumenta, pero descienden a medida que la edad pasa de un determinado umbral, (a partir de los 70 años).

En el segunda gráfico nos encontramos con que la nube de puntos no está tan cohesionada entorno a la linea de tendencia, también nos lo indica R2 con un 77%.

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS_SEGUNDA PREGUNTA
Aquí la respuesta es de “SÍ FUMO, PERO NO A DIARIO”, aunque nos encontramos en que el número de casos en determinadas edades rompe con la tendencia y aleja los puntos. Es decir, que en algunos casos la edad no puede explicar que una persona fumara antes y ahora no lo haga (existen otras variables que pueden explicar esto: enfermedades, percepción social … etc. Aunque la relación entre variables es significativa.

En la tercera pregunta, volvemos a los índices de la primera pregunta, R2 es de un 90% (podemos comprobarlo en el gráfico).

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS_TERCERA PREGUNTA

La respuesta es, “NO FUMO ACTUALMENTE, PERO HE FUMADO ANTES”. Se trata de ex fumadores, de nuevo la mayor cantidad de casos se encuentra en edades centrales, siendo en las edades “extremas” donde se encuentra el menor número de casos.

En el caso de la cuarta respuesta, “NO FUMO NI HE FUMADO NUNCA DE MANERA HABITUAL”.

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS_CUARTA PREGUNTA

Existe una menor relación entre ambas variables, es decir, que la edad no explicar de forma rotunda que las personas no fumen ni hayan fumado, es decir, existen otras variables que explicarían esta respuesta respecto al hábito de fumar.

Para los casos 8 y 9, NO SABE y NO CONTESTA aunque hemos calculado la correlación, R2 y el gráfico, no son representativos estadísticamente, y no los tendremos en cuenta.

Finalmente sacaremos de cada gráfico el dato de los coeficientes de determinación y los pondremos al lado de las correlaciones que hemos calculado y que no nos sirven.

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS3

Podríamos obtener la misma información, pero introduciendo los datos de hábitos de alcohol (o cualquier otra temática). No he automatizado la parte en la que obtenemos la información y generamos los gráficos y las correlaciones, si decidís actualizar otra información, debéis indicarlo en la cadena SQL:

obSQL = "SELECT [QUERY$].[EDADa], [QUERY$].[V121], COUNT ([QUERY$].[V121]) AS CUENTA " & _
"FROM [QUERY$] " & _
"WHERE [QUERY$].[V121] like " & j & " " & _
"GROUP BY [QUERY$].[EDADa], [QUERY$].[V121] " & _
"ORDER BY [QUERY$].[V121]"

Y en esta parte podríamos seguir jugando con otros indicadores estadísticos, otras técnicas, otros datos, etc. Pero creo que como ejemplo es suficiente.

Podríamos establecer nuevas variables independientes que nos ayuden a entender la relación, como el cambio de percepción de este tipo de hábito, las enfermedades, el gasto, etc, que sin ninguna duda juntas, explicarían el numero de casos por edad en cada pregunta. Pero como podéis ver, ¡esta investigación no ha hecho más que empezar!!

Pueden parece macros demasiado complejas, pero en realidad no lo son, si observáis los procesos con detenimiento podréis visualizar perfectamente el funcionamiento de la macro.

Interesante el proceso para obtener el R2 de los gráficos, también la utilización de la correlación como primera alternativa (que en caso de ser una relación lineal serviría perfectamente).

He dejado el archivo TXT importado en el Excel por eso tiene 18 mb. Para obtener el TXT solo tenéis que visitar alguno de los post anteriores.

Descarga el archivo de ejemplo pulsando en: TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. ANÁLISIS DE LA INFORMACIÓN

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 2: EXTRAER LA INFORMACIÓN

Hola a todos!. Espero que todo vaya bien 🙂

Hoy vamos a seguir con la serie de post dedicados a trabajar con grandes bases de datos en Excel. Y en esta ocasión hablaremos sobre la extracción de información.

En esta web hay varios post dedicados a la extracción de la información, en algunos casos con tablas dinámicas, en otros con ADO usando SQL y también directamente con código VBA usando diferentes métodos.

Cuando trabajamos con grandes volúmenes de información suele ocurrir que con tanta información se hace complicado, tanto la gestión de los datos, como la implementación de herramientas de cálculo.

Y no es un problema menor, dado que seleccionar la información de forma manual o embarcarnos a utilizar una tabla dinámica con 428 campos o trabajar con ADO nos hará perder mucho tiempo y seguramente cometer errores.

Por ello, vamos a proponer una forma de realizar esta extracción de información. Siguiendo con nuestro ejemplo del post anterior (TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 1: IMPORTAR LA INFORMACIÓN), en el que habíamos extraído los datos del archivo TXT de la Encuesta Europea de Salud en España, ahora nos encontramos con una base de datos bastante grande, no tanto en longitud de filas pero sí de columnas (428).

Dado que los límites que tiene Access y ADO (Microsoft ACE.OLEBD)  son de 255 columnas no nos van a permitir realizar consultas ni tampoco extraer columnas con información relevante, ni en Excel ni en Access (con dichas herramientas).

Sí podríamos utilizar tablas dinámicas (no gráficos dinámicos) en versiones actuales de Excel donde el límite está en la memoria disponible para mostrar campos y filas (aunque los campos de valores tendrán el límite de 256). O también podríamos realizar instrucciones con VBA para recuperar aquellas columnas o rangos que nos interesen.

Aunque la tabla dinámica podría ser una buena opción, para este ejemplo utilizaré VBA de tal manera que me permita extraer de la base de datos aquellos campos que voy a necesitar para posteriormente aplicar consultas, fórmulas, gráficos o tablas dinámicas.

Una vez que he acabado con la parte más teórica o de reflexión, ¡vamos con la teoría!.

Para poder confeccionar la herramienta necesito utilizar algo que me permita seleccionar cada ítem o campo de la tabla y exportarlo a una nueva hoja. Por ello, creo que la figura, o mejor, el objeto LISTBOX es perfecto para este cometido.
Por ello, en la hoja ESPECIFICACIONES vamos a insertar un Control ActiveX, un cuadro de lista o ListBox (que será ListBox1). Una vez que lo hemos insertado, tenemos que programarlo para realizar las siguientes acciones, cargar los campos de la tabla y vaciarlos o desmarcarlos.

Para cada acción crearemos una macro que vamos a pegar en un módulo estándar de nuestro editor de VBA:

Para cargar la información de  todos los campos:

Vamos a pegar este código en nuestro módulo de VBA:

Sub CARGAR_LISTBOX()
'Definimos variables
Dim fin As Integer
fin = Application.CountA(Sheets("ESPECIFICACIONES").Range("A:A"))
'Cargamos datos en el listbox de la hoja espeficificaciones
'Dimensionamos listbox
'indicamos propiedad Multiselect
With Sheets("ESPECIFICACIONES").ListBox1
.Height = 220 'Alto
.Width = 180 'Ancho
.Top = 186.75 'Arriba
.Left = 635 'Izquierda
.MultiSelect = fmMultiSelectMulti
.List = Sheets("ESPECIFICACIONES").Range("A2:A" & fin).Value
End With
End Sub

Como podéis observar además de cargar los datos del ListBox, estoy indicando los parámetros necesarios para dimensionarlo (dado que la propiedad IntegralHeight está como true y si no indicamos sus dimensiones, cada vez que carguemos datos el listbox se irá reduciendo de tamaño).  También indicamos que se pueden seleccionar varios elementos (multiselect).

Para vaciar la información del listbox

Vamos a pegar este código en nuestro módulo de VBA:

Sub VACIAR_LISTBOX()
'Vaciamos los datos que hemos cargado en el listbox
With Sheets("ESPECIFICACIONES")
.ListBox1.Clear
End With
End Sub

Para desmarcar la información de los campos seleccionados:

Vamos a pegar este código en nuestro módulo de VBA:

Sub DESMARCAR_LISTBOX()
'Definimos variables
Dim i As Integer
'Desmarcamos los datos seleccionados en el listbox
With Sheets("ESPECIFICACIONES")
For i = 0 To .ListBox1.ListCount – 1
.ListBox1.Selected(i) = False
Next
End With
End Sub

En este caso con una sencilla instrucción For desmarcamos todos los ítems seleccionados.

Ahora que ya tenemos nuestro ListBox y las macros que lo controlan preparadas:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL PARTE 2 EXTRAER LA INFORMACION

ya podemos pegar esta otra macro extraer la información que seleccionemos en el listbox. Por lo tanto vamos a pegar esta macro en el mismo editor de VBA:

Sub EXTRAER_INFORMACION()
'Definimos las variables
Dim Item As Integer, nItem As Variant, nSel As Integer
Dim campo As Integer, fin As Integer
Dim Origen As Worksheet, Destino As Worksheet
'Desactivamos actualización de plantalla
Application.ScreenUpdating = False
'Eliminamos información que pueda contener la Hoja Query
Sheets("QUERY").Select
With Sheets("QUERY")
.Range(.Cells(1, 1), ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Delete Shift:=xlToLeft
.Range("A1").Select
End With
'Detectamos el nombre de cada campo seleccionado (nItem) y el número de items seleccionados (nSel)
With Sheets("ESPECIFICACIONES")
For Item = 0 To .ListBox1.ListCount – 1
If .ListBox1.Selected(Item) = True Then
nItem = .ListBox1.List(Item)
nSel = nSel + 1
'Identificamos el número de columna en la hoja datos con el nombre del cada Item
campo = Application.Match(nItem, Sheets("DATOS").Range("1:1"), 0)
'Pasamos las columnas detectadas en la hoja Datos a la hoja Query
Set Origen = Sheets("DATOS")
Set Destino = Sheets("QUERY")
fin = Application.CountA(Sheets("DATOS").Range("A:A"))
With Destino
.Range(.Cells(1, nSel), .Cells(fin, nSel)).Value = Origen.Range(Origen.Cells(1, campo), Origen.Cells(fin, campo)).Value
Columns(nSel).EntireColumn.AutoFit
End With
End If
Next
End With
End Sub

Es un código muy sencillo y a la vez efectivo. Simplemente, mediante un bucle, por cada ítem seleccionado en el listbox buscamos el valor de la columna en la tabla de la hoja “DATOS” y por medio de una instrucción SET pasamos el valor a la hoja “QUERY”.

Por ejemplo, vamos a seleccionar en nuestro listbox el campo CCAA y Edad (EDADa):

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL PARTE 2 EXTRAER LA INFORMACION2

Y una vez ejecuta la consulta pulsando en el botón EXTRAER INFORMACIÓN, este es el resultado en la hoja “QUERY”:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL PARTE 2 EXTRAER LA INFORMACION3

Como podéis ver, los datos que hemos extraído de una hoja a la otra son idénticos, simplemente movemos información para luego poder tratarla de una forma mucho más efectiva.

Por ejemplo, si quisiera conocer la correlación entre la edad y la frecuencia del consumo de tabaco, a priori, solo tendría que traer dos columnas, la edad y el campo V121, y luego utilizar funciones que me permitan realizar el cálculo, o determinar si es estadísticamente existe una relación entre la cantidad y frecuencia de consumo de alcohol y el consumo de tabaco, la edad y la comunidad autónoma de procedencia, etc.  Pero eso será en el siguiente post 🙂

Recordad que cuando trabajamos en Excel, la solución más sencilla puede ser la más eficaz 🙂

Os dejo el archivo de ejemplo para que realicéis pruebas, investiguéis el código o lo que necesitéis. El archivo pesa unos 18,5 megas y se debe a que tiene la información importada del TXT grabada en la hoja “DATOS” para que no tengáis que volver a importar la información, sin embargo, si la borráis o la queréis volver a importar solo tenéis que ir al primer post y bajaros la información.

Descarga el archivo de ejemplo pulsando en: TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. EXTRAER LA INFORMACIÓN

GENERAR ORGANIGRAMA JERÁRQUICO POR ÁREAS CON SMARTART

Hola  todos. ¿Qué tal estáis? 🙂 espero que bien!

En esta web ya llevo varios post dedicados al mundo de SmartArt en Excel, pero complementándolo con VBA. SmartArt ofrece un sensacional efecto visual y un acabado muy profesional en nuestro trabajos. Existen multitud de gráficos y cada uno tiene unas especificaciones concretas en cuanto a utilidad y fin, y eso también se aplica a VBA a la hora de programar un gráfico concreto.

Podréis ver varios ejemplos si escribís en el el buscador de la web la palabra SmartArt, tanto para generar organigramas de varios tipos, como árboles de decisión o estructuras de procesos.

Hoy quiero dedicar un post a un tipo concreto de gráfico de jerarquía, que se denomina en SmartArt como “Jerarquía de tabla”, lo podéis ver en la ficha:

generar-organigrama-jerarquico-por-areas-con-smartart4

Para realizar un ejemplo, he rescatado unos datos usados en uno de los últimos post dedicados a este tema:

generar-organigrama-jerarquico-por-areas-con-smartart2

 

Tenemos la distribución por áreas de una empresa, y necesitamos crear un gráfico que nos muestre un organigrama, empezando por las últimas áreas en la jerarquía hasta la última (Gerencia). No queremos nombres de personas, solo áreas y que se visualicen en bloques.

Para hacerlo vamos a utilizar el siguiente código:

Sub JERARQUIA_POR_AREAS()
'Declaramos variables
Dim Diseño As SmartArtLayout
Dim Shape As Excel.Shape
Dim oNodos As SmartArtNodes
Dim i As Integer, Fin As Integer
With Sheets("ESTRUCTURA")
.Select
'Eliminamos TODOS objetos en la hoja "ESTRUCTURA"
For Each Shape In .Shapes
Shape.Delete
Next
'Insertamos objeto SmartArt, en este caso "Jerarquía de tabla"
Set Diseño = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2005/8/layout/hierarchy4")
Set inserta = .Shapes.AddSmartArt(Diseño)
Set oNodos = inserta.SmartArt.AllNodes
'Verificamos número de nodos necesarios contando los ítems de la página "DATOS"
Fin = Application.CountA(Sheets("DATOS").Range("A:A"))
'Creamos nodos
Do While oNodos.Count < Fin
oNodos.Add.Promote
Loop
'Eliminamos nodos sobrantes y los nombramos con la información de la hoja "DATOS"
For i = 2 To Fin
Do While oNodos(i - 1).Level < Sheets("DATOS").Range("B" & i).Value
oNodos(i - 1).Demote
Loop
With oNodos(i - 1)
.TextFrame2.TextRange.Text = Sheets("DATOS").Range("A" & i)
End With
Next
'Eliminamos último nodo (estará vacío al tener encabezado la hoja "DATOS")
oNodos(Fin).Delete
'aplicamos estilos
For Each Shape In .Shapes
'Colores
Shape.SmartArt.Color = Application.SmartArtColors("urn:microsoft.com/office/officeart/2005/8/colors/accent2_1")
'Estilos rápidos
Shape.SmartArt.QuickStyle = Application.SmartArtQuickStyles("urn:microsoft.com/office/officeart/2005/8/quickstyle/simple2")
'Dimensionamos la imagen
With .Shapes(1)
.Height = 581.25 'Alto del objeto
.Width = 1375.5 'Ancho del objeto
.Top = 6.749921 ' Altura en la hoja
.Left = 14.25 ' A la izquierda de la hoja
End With
Next
End With
End Sub

El resultado es el siguiente:

generar-organigrama-jerarquico-por-areas-con-smartart1

Como podéis apreciar, obtenemos el efecto deseado, para este ejemplo he utilizado un estilo rápido y colores específicos, pero podéis indicar los que más os gusten, por ejemplo, con colores por cada unidad jerárquica:

generar-organigrama-jerarquico-por-areas-con-smartart3

Para obtener los ID y los nombres, podéis visitar este post: OBTENER NOMBRE E ID DE LOS DISEÑOS, COLORES Y ESTILOS RÁPIDOS DE SMARTART PARA VBA 

Otro consejo 🙂 para este ejemplo estoy utilizando una pantalla grande, es posible que deseéis cambiar el tamaño del gráfico y la posición, para hacer de una forma eficaz, utilizad la pantalla de “inmediato” de vuestro editor de VBA. Primero configurar manualmente el gráfico en la pantalla del ordenador (el aspecto y tamaño que os gustaría que tuviese) y luego, en la pantalla de inmediato, mediante estos comandos obtendréis el ancho, el largo y posición dentro de la hoja, por ejemplo, la altura que debemos indicar:

generar-organigrama-jerarquico-por-areas-con-smartart5

Y este ha sido el ejercicio de hoy. Espero que con esta macro podáis aplicar un estilo diferente a vuestro proyectos 🙂

Descarga el archivo pulsando en: GENERAR ORGANIGRAMA JERÁRQUICO POR ÁREAS CON SMARTART

 

ELIMINAR TODAS LAS IMÁGENES (FORMAS) DE UNA HOJA O UN LIBRO EN EXCEL CON VBA

Hace unos días recibí una consulta sobre cómo se podría modificar el tamaño de todas las imágenes de una hoja o de un libro mediante una macro.

Aunque este tema ha sido muy tratado en diferentes webs y foros de internet, me ha parecido útil escribir un post acerca del tratamiento de imágenes (o mejor, de cualquier objeto de la colección shapes).

Es decir, en Excel podemos estar trabajando con una fotografía, un organigrama de SmarArt, un gráfico o un objeto Ole, etc).  Todo ellos objetos que forman parte de la colección Shapes.

Siguiendo un caso práctico, imaginad que tenemos un libro con las siguientes formas (imágenes, autoformas, llamadas, objeto SmartArt):

eliminar-todas-las-imagenes-formas-de-una-hoja-o-un-libro-en-excel-con-vba

Dicho esto, si quisiéramos eliminar todos los objetos de la primera hoja de nuestro libro, lo haríamos así:

Sub Borrar_Hoja()
Dim Shape As Excel.Shapes
'Por cada forma en la hoja 1
For Each Shapes In Sheets(1).Shapes
' Eliminamos forma
With Shapes
.Delete
End With
Next
End Sub

Si quisiéramos eliminar todas las formas (Shapes) que existen el libro, tendríamos que utilizar la macro anterior pero dentro de un bucle for – next que recorra todas las hojas del libro:

Sub Borrar_Libro()
Dim nHoja As Integer
Dim Shape As Excel.Shapes
'Contamos las hojas del libro activo
nHoja = ActiveWorkbook.Worksheets.Count
'Inicimiamos bucle.
For i = 1 To nHoja
'En cada hoja seleccionamos todas las formas
For Each Shapes In Sheets(i).Shapes
'y las borramos
With Shapes
.Delete
End With
Next
Next i
End Sub

Sin embargo, si solo queremos eliminar un tipo de forma, tendríamos que especificar en el código el tipo que deseamos eliminar. Antes de seguir, os dejo un enlace a Microsoft donde se especifica el nombre y valor de todas las formas de la colección shapes:

https://msdn.microsoft.com/en-us/library/office/ff860759.aspx

eliminar-todas-las-imagenes-formas-de-una-hoja-o-un-libro-en-excel-con-vba1

*en el caso del valor 24, como observaréis, he modificado el Name que aparece publicado en Microsoft en el enlace de Microsoft (msoIgxGraphic) por (msoSmartArt), el motivo es que he probado el nombre msoIgxGraphic en todas las versiones desde 2010 en adelante y no funciona, pero sí lo hace con msoSmartArt.

Por lo tanto, para realizar la prueba vamos a elegir un tipo determinado de forma a eliminar, tanto en la primera hoja como en todo el libro. La elección serán las fotografías, que equivalen según el cuadro anterior a msoPicture y con valor 13.

Para eliminar las fotografías de la primera hoja, usaremos el siguiente código:

Sub Borrar_Hoja_Tipo()
Dim Shape As Excel.Shapes
'Por cada forma en la hoja 1
For Each Shapes In Sheets(1).Shapes
' Eliminamos forma
With Shapes
'Si la forma es entonces la borramos
If .Type = 13 Then
.Delete
End If
End With
Next
End Sub

Y para eliminar las fotografías en todo el libro, usaremos el siguiente:

Sub Borrar_Libro_Tipo()
Dim nHoja As Integer
Dim Shape As Excel.Shapes
'Contamos las hojas del libro activo
nHoja = ActiveWorkbook.Worksheets.Count
'Inicimiamos bucle.
For i = 1 To nHoja
'En cada hoja seleccionamos/detectamos todas las formas
For Each Shapes In Sheets(i).Shapes
With Shapes
'Si la forma es entonces la borramos
If .Type = 13 Then
.Delete
End If
End With
Next
Next i
End Sub

El resultado después de aplicar la macro sería este:

eliminar-todas-las-imagenes-formas-de-una-hoja-o-un-libro-en-excel-con-vba2

Y hemos eliminado las fotografías en todo el libro.

Evidentemente, podemos utilizar las macros para cualquier otro tipo de acción sobre las formas, como por ejemplo darle formato, colores, alto, ancho, etc … Pero para este ejemplo, nos hemos limitado a eliminar (en otros post que tengo pensado ir publicando iremos trabajando otras acciones).

En el archivo adjunto os dejo las macros. Sin embargo los botones que he dejado para ejecutarlas, solo son para los casos donde determinamos el tipo de forma a eliminar. Las macros que eliminan todas las formas de la hoja y del libro, si pusiese un botón en la hoja, directamente lo eliminaría, dado que también se trata de una forma (control de formulario).

Espero que os sea de utilidad 🙂

Descarga el archivo de ejemplo pulsando en: ELIMINAR TODAS LAS IMÁGENES DE UNA HOJA O UN LIBRO EN EXCEL CON VBA