Trabajar con Minigráficos (o Sparklines) en Excel 2010.

Será este el último post del año, así que antes de continuar, quería desear en especial a todos aquellos que han seguido mi blog personal a lo largo de este año 2011 que acaba, y a todo el mundo por supuesto, los mejores deseos para estas fiestas que llegan, y que el próximo año 2012 sea al menos tan bueno como lo ha sido este (no todo en la vida ha sido crisis económica... hay cosas mucho más importantes).
Dicho esto, anticiparé de que voy a hablar hoy. Llevamos ya casi dos años con la última versión de Office 2010, y realmente no he tenido ocasión de hablar de las novedades de Excel 2010 frente a sus predecesores; así que no quería acabar el año sin comentar y aplicar sobre un pequeño ejemplo la herramienta de los Minigráficos, esos pequeños graficos limitados al fondo de una celda, a diferencia de los gráficos 'normales' que se construyen como Objetos.
Como particularidad de los Minigráficos de Excel 2010 decir que, como consecuencia de lo dicho en el párrafo anterios, esto es que es un pequeño gráfico incrustado en una celda, podremos escribir texto en dicha celda y usar un minigráfico como fondo.
Para trabajar con esta 'nueva' herramienta, partiremos como siempre de una Tabla de datos. Para nuestro ejemplo, del detalle de las ventas reales producidas para cuatro productos en diferentes años, así como los datos de ventas presupuestadas para el año siguiente:

Generar estos minigráficos es bien sencillo, seleccionamos el rango de datos B2:F5 y desde la ficha Insertar > Minigráficos seleccionaremos alguno de los tipos que nos ofrece:

Yo me he decantado por el Minigráfico en columnas. Lo que vemos a continuación es una ventana diálogo donde indicar y seleccionar el origen de datos y dónde ubicar los minigráficos:

Seguir leyendo...

Búsqueda matricial triple sobre listados de Excel.

En otro foro en el que participo (cuando puedo), respondí una cuestión que me pareció interesante. Se trataba de averiguar si los registros de una Tabla coincidian con alguno de una segunda Tabla, situada en otra Hoja de cálculo de nuestro Excel:

...necesito realizar una búsqueda en dos diferentes hojas por ejemplo en hoja1 tengo 3 columnas con numero de parte cantidad y localidad y en hoja2 tengo las mismas 3 columnas con diferentes datos, necesito que si coinciden las tres columnas se ponga ok o algún valor en la columna 4 ...

Partiremos de dos listados de datos en hojas distintas, para cumplir con los requisitos de la consulta:

Se trata, por tanto, de identificar cuáles de los registros de la tabla de la Hoja1 coinciden (en los tres datos) con algún registro de la Tabla de datos de la Hoja2; que como vemos serán el primero y último de la lista...
El trabajo será construir una matriz o un rango sobre el que comparar nuestros tres valores. Expondré en primer lugar la función que nos resolverá la cuestión, para luego desgranarla y ver su funcionamiento. Nuestra función matricial (Ctrl+Mayusc+Enter) para cada registro de la Tabla de la Hoja1 será:
{=SI(ESERROR(BUSCARV(A2&B2&C2;campo1&campo2&campo3;1;0));"";"ok")}

Nuestra labor comenzó definiendo o asignando nombres a las columnas de datos de la tabla de datos de la Hoja2, en concreto serían:
campo1 =Hoja2!$A$2:$A$5
campo2 =Hoja2!$B$2:$B$5
campo3 =Hoja2!$C$2:$C$5
Este paso es muy importante, ya que además de la facilidad para crear nuestra matriz de búsqueda, nos permitirá trabajar entre hojas diferentes.

Seguir leyendo...

Segmentación de datos en tablas dinámicas de Excel 2010.

Llevaba tiempo queriendo escribir algo sobre el empleo de esa nueva utilidad que nos aportan, con Excel 2010, nuestras tablas dinámicas; en concreto hablaré de la Segmentación de datos.
Realmente esta utilidad sólo aporta facilidad, que no nueva funcionalidad, ya que esta Segmentación de datos muestra controles visuales que nos permiten filtrar datos rápidamente en nuestra tabla dinámica de forma interactiva e intuitiva; usando botones para segmentar y/o filtrar rápidamente los datos para mostrar únicamente lo que se necesita. Desde luego son funcionalidades que anteriromente hacíamos con los botones de filtro bien de los campos de tabla o campos de filtro, claro está de campos añadidos en nuestra tabla dinámica... quizá aquí es donde esta herramienta aporta mayor novedad, ya que ésta no requiere que el campo 'segmentado' esté incluido en la estructura de tabla dinámica.

Veamos el uso con un pequeño ejemplo, como siempre.
Dispondremos de un origen de datos sobre el que crearemos una tabal dinámica, igual que tantas veces:

Vemos como hemos llevado al área de filas el campo 'fecha' (campo sobre el que hemos realizado una agrupación por años), al área de columnas el campo 'departamento' y al área de datos el campo 'total'.
Con la tabla dinámica activada, nos iremos a la Barra de herramientas de tabla dinámica > Opciones > Ordenar y filtrar > Insertar Segmentación de datos:

Al activar la herramienta nos pregunta sobre qué campo o campos deseamos realizar el análisis, en nuestro caso seleccionaremos 'fecha' y 'concepto', por desplegar uno incluido en la tabla y otro que no.

Seguir leyendo...

Conciliar en Excel por aproximación con Solver.

Tiempo atrás, expliqué en un post la manera de conciliar ciertos importes, empleando para ello la herramienta de Excel Solver (ver). Al hilo de este post un usuario preguntaba a través de un comentario:

...¿podríamos hacer que las posibilidades de suma fueran por un aproximado de por ejemplo 5 unidades arriba y 5 abajo?...

La respuesta a esta cuestión sería afirmativa, claro está bajo ciertos supuestos, ya que en caso de múltiples coincidencias solver siempre se decantaría por una de las posibles soluciones. sin embargo construiremos un modelo en Excel que nos muestre en todo caso, de todas las aproximaciones posibles a buscar, la que sea más próxima al valor buscado.
Importante aclarar, buscamos por aproximación al total de la suma.
Veamos nuestros datos de partida:

Realmente, nuestra base de trabajo es similar a la expuesta en la entrada Conciliar una partida con Solver, si bien, necesitaremos ciertas modificaciones. En primer lugar, construiremos dos columnas C y D anexas, ambas con la misma fórmula
=A2*B2
para luego acabar sumando estos valores en C8 y D8
=SUMA(C2:C7) y =SUMA(D2:D7); sumas que emplearemos después al Agregar nuestras condiciones de Solver.
Importante también es definir correctamente la que será celda objetivo E8, con la fórmula:
=ABS(((G2*2)-(D8+C8))/2)
sabiendo que nuestro modelo es el siguiente, con la disposición de celdas que vemos en la imagen:

Seguir leyendo...

Las funciones DERECHA, LARGO y ENCONTRAR en Excel.

Abordaré hoy el uso combinado (más bien anidado) de varias funciones de texto, como son DERECHA, LARGO y ENCONTRAR. Pretendo dar solución con ello al problema planteado por un lector que preguntaba la forma de ordenar una tabla de datos según una parte del texto de una columna.
Veamos cuales son los datos de partida y cómo queremos obtenerlos:

Podemos ver más claro en la imagen cuál es nuestro objetivo. Debemos ordenar no por Primer Apellido, si no por el Nombre, que encontramos a la derecha de cada celda, únicamente separado por espacios...
Nuestra labor comienza entonces por convertir nuestro rango de datos en una Tabla de Excel(Ctrl+q para Excel 2007 ó Ctrl+t para Excel 2010), para luego en la columna D anexa a la tabla, incorporar la siguiente función, que nos devolverá el Nombre de cada celda:
=DERECHA(DERECHA(B4;LARGO(B4)-ENCONTRAR(" ";B4));LARGO(DERECHA(B4;LARGO(B4)-ENCONTRAR(" ";B4)))-ENCONTRAR(" ";DERECHA(B4;LARGO(B4)-ENCONTRAR(" ";B4))))

Seguir leyendo...

Asociar a un TextBox el valor seleccionado de un ListBox.

Seguiré hoy con el tema de los últimos días, esto es con Formularios (UserForm) en Excel. Hoy en concreto veremos como asociar a un TextBox el valor seleccionado de un ListBox, es decir, veremos cómo podemos desde un ListBox creado rellenar los TextBox o Cuadros de Texto incluidos en nuestro formulario; además aprenderemos una forma de Cambiar el nombre (propiedad Caption) de nuestras Etiquetas (Labels).
Partiremos del trabajo realizado en la entrada anterior (ListBox con varias columnas y encabezados), donde llegamos a este diseño:

Accedemos al Editor de VB (Alt+F11) y dentro del Editor buscamos nuestro formulario UserForm1, activamos dicho formulario con lo que nos aparecerá el Cuadro de herramientas o controles. En este buscaremos los siguientes controles:

1. Marco (Frame): con este delimitaremos, y haremos independiente de otros controles, lo que incluyamos en él.
2. Etiquetas (Labels): no editable, para nombrar los Cuadros de texto que situaremos a su lado.
3. Cuadro de Texto (TextBox): donde vincularemos los valores seleccionados en el Cuadro de Lista (ListBox).

Bien, en ese orden, primero seleccionaremos el área del marco(Frame), para luego colocar tres etiquetas(Labels) y a su lado respectivamente tres Cuadro de Texto (TextBox), asociando cada uno de éstos a una columna del Cuadro de Lista (ListBox) que tenemos ya creado.
El diseño nos quedaría así:

Una vez ha quedado acorde a nuestro control de calidad visual, podemos añadirle la funcionalidad buscada.
En primer lugar, modifcaremos ciertas propiedades de los diferentes controles incluidos en nuestro formulario. En concreto cambiaremos los nombres que se muestran en las tres etiquetas por la cabecera o rótulos de la tabla origen de datos, también asignaremos un nombre definido por nosotros al Marco (O Frame):

Sub UserForm_Initialize()
Dim fila As Single

'le decimos cuántas columnas tendrá
ListBox1.ColumnCount = 3
'que sí que tiene encabezado
ListBox1.ColumnHeads = True
'el origen de datos en nuestra hoja de cálculo
ListBox1.RowSource = "Tabla1"
'renombramos el Marco
Frame1.Caption = "Datos seleccionados:"
'damos nombre a las etiquetas creadas, empleando la cabecera de la Tabla1
fila = ListBox1.ListIndex + 2
With Hoja1.Range(ListBox1.RowSource)
    Label1.Caption = .Cells(fila, 1).Offset(-1, 0).Value
    Label2.Caption = .Cells(fila, 2).Offset(-1, 0).Value
    Label3.Caption = .Cells(fila, 3).Offset(-1, 0).Value
End With
End Sub

Seguir leyendo...

VBA: ListBox con varias columnas y encabezados.

Continuando con algún post anterior en el que veíamos como trabajar con un Formulario (UserForm), explicaré hoy como incluir un ListBox en un formulario con datos extraidos de nuestra hoja de cálculo, pero con varias columnas de datos y respetando la cabecera de rótulos:
Lo más cómodo e importante para emezar a trabajar será convertir nuestro rango de datos en una Tabla de Excel(Ctrl+q para Excel 2007 y Ctrl+t para Escel 2010). Veamos nuestro origen de datos nombrada como Tabla1:

Con nuestra Tabla1 creada, ya podemos irnos al Editor de VBA (Alt+F11) e insertar un UserForm (Formulario), buscaremos en la ventana de controles la opción Cuadro de lista, y lo desplegaremos dentro del Formulario donde deseemos::

Una vez incluido el ListBox en nuestro formulario, deberes configurarlo para que recoja los registros de nuestra Tabla1 de la Hoja de cálculo como elementos del recien creado ListBox1, además deberemos definirlo de tal forma que el encabezado de la Tabla1 aparezca como cabecera de nuestro ListBox1, por supuesto, cada campo deberá aparecer en una columna independiente.
Para ello deberemos trabajar con distintas propiedades de este ListBox, como son:

• ColumnCount: en el que indicaremos cuantas columnas vamos a crear, para nuestro ejemplo, le asignaremos un valor de tres (una por cada campo 'Producto', 'Unidades' y 'Precio')
• ColumnHead: donde seleccionaremos True o False dependiendo de si queremos mostrar encabezado o no.
  Ojo, por que en este caso, es importante para evitar posibles fallos, haber definido nuestro origen de datos, en la Hoja de cálculo, como Tabla Lista.
• RowSource: donde le indicaremos dónde se encuentra el origen de datos, para nosotros será 'Tabla1'.

Estas propiedades del ListBox se pueden definir bien desde la Ventana de propiedades, o bien directamente con código.
Veamos cómo definimos estas propiedades con código VBA, para ello haremos doble click sobre nuestro UserForm y añadiremos el código:

Private Sub UserForm_Initialize()
'le decimos cuántas columnas tendrá
ListBox1.ColumnCount = 3
'que sí que tiene encabezado
ListBox1.ColumnHeads = True
'el origen de datos en nuestra hoja de cálculo
ListBox1.RowSource = "Tabla1"

End Sub

Con la intrucción Private Sub UserForm_Initialize() conseguimos que el ListBox se cargue cada vez que iniciamos el formulario.
El resultado del Formulario lo podemos ver al ejecutarlo:

Seguir leyendo...

VBA: Crear una matriz (Array) a partir de un rango de Excel.

Alguna vez he hablado del concepto de matriz o Array en el contexto de nuestras macros (ver), normalmente empleándola o definiéndola a partir de elementos constantes, sin embargo, en ocasiones, se hace necesario emplear estas matrices sobre rangos dinámicos alojados en nuestras celdas de nuestras hojas de cálculo.
Hoy hablaré de la forma de crear una Array a partir de los valores de un rango de celdas.
Lo aplicaremos para el supuesto en que deseamos añadir un listado de clientes a un desplegable o Cuadro combinado, que se encuentra en la 'Hoja1' de nuestro Libro de trabajo y que empieza en la celda A2 y no sabemos dónde acaba...

Nuestro trabajo consistirá en pasar un rango a matriz, para luego emplear los elementos de esa matriz como parte de un ComboBox o desplegable (controles ActiveX).
Lo primero que haremos será incrustar un Cuadro combinado (control ActiveX) en nuestra 'Hoja1', lo que haremos desde la Ficha Programador > Controles > Insertar controles > Controles ActiveX > Cuadro combinado:

En el siguiente paso, haciendo doble click sobre el Control pasaremos a la ventana de código, donde incluiremos lo siguiente:

Private Sub ComboBox1_gotfocus()
Dim clientes As Long
'cuenta el número de elementos en la Hoja1
clientes = Sheets("Hoja1").Range(Range("A2"), Range("A2").End(xlDown)).Rows.Count

'definimos una Matriz sin dimensiones determinadas
Dim MyArray() As Variant
'sabiendo el número de elementos a incluir, redimensionamos nuestra Matriz
ReDim MyArray(1 To clientes, 1) As Variant
'En caso de error, que continúe
On Error Resume Next
'limpiamos el ComboBox
ComboBox1.Clear
'añadimos cada valor del rango definido de la Hoja1 como elemento de la Matriz
For i = 1 To clientes
    MyArray(i, 1) = Worksheets("Hoja1").Cells(i + 1, 1).Value
    'incluímos cada elemento de la Matriz dentro del Cuadro combinado
    ComboBox1.AddItem MyArray(i, 1)
Next i
End Sub

Lo más importante en este ejemplo ha sido ser capaces de Redimensionar nuestra Matriz 'MyArray' a una dimensión variable en cada momento, ajustada al número de elementos que componen el rango de celdas.

Seguir leyendo...

Crear un formulario (UserForm) en Excel y II.

Continuando con la entrada anterior (ver), donde empezamos a explicar cómo construir un UserForm en el Editor de VBA de Excel, empezaremos por recordar el aspecto logrado para nuestro Formulario:

Los códigos que debemos añadir para cada uno de los controles son.
Para el ComboBox1 que desplegará los nombres de las Hojas (Países), salvo la hoja 'Consolidado':

Private Sub ComboBox1_enter()
'En caso de error, que continúe
On Error Resume Next
'limpiamos el ComboBox
ComboBox1.Clear

'Vamos a llenar dinámicamente el combobox
pais = ActiveWorkbook.Worksheets.Count
For i = 1 To pais
    'Añadimos todas las hojas del libro al Combobox, excepto la llamada 'Consolidado'
    If Sheets(i).Name <> "Consolidado" Then ComboBox1.AddItem Sheets(i).Name
Next

End Sub

Para el ComboBox2 que desplegará los nombres de los clientes:

Private Sub ComboBox2_enter()
'En caso de error, que continúe
On Error Resume Next
'limpiamos el ComboBox
ComboBox2.Clear
'Añadimos los valores de las celdas del rango A2:A6 de la hoja 'Consolidado'
ComboBox2.RowSource = "'Consolidado'!A2:A6"
End Sub

Para que el TextBox1 sólo admita valores numéricos:

Private Sub TextBox1_Change()
    'verificamos que el valor introducido es numérico
    If Not IsNumeric(TextBox1.Value) And TextBox1.Value <> vbNullString Then
        MsgBox "Solo números!!!"
        TextBox1.Value = vbNullString
    End If

End Sub

Para que el CommandButton1 ejecute la búsqueda e introduzca el valor del TextBox1 en la hoja seleccionada con el ComboBox1 (País) y asociada al cliente elegido con el ComboBox2:

Private Sub CommandButton1_Click()
Dim fila As Integer
'buscamos la celda correspondiente a la hoja seleccionada en el ComboBox1
'y el cliente seleccionado en el ComboBox2
For i = 2 To 6
If Worksheets(ComboBox1.Value).Cells(i, 1).Value = ComboBox2.Value Then fila = i
Next i

'comprobamos si está rellena la celda o no
If Worksheets(ComboBox1.Value).Cells(fila, 2).Value = "" Then
Worksheets(ComboBox1.Value).Cells(fila, 2).Value = TextBox1.Value
Else
mensaje = MsgBox("La celda está ya rellena.", vbOKCancel, "Atención!!")
    If mensaje = vbCancel Then
    UserForm1.ComboBox1.SetFocus
    End If
End If

End Sub

Ya sólo nos falta llamar a nuestro formulario desde nuestra hoja de trabajo 'Consolidado', lo que haremos, por ejemplo, asignando la siguiente macro a un botón (macro insertada en un Módulo del Explorador de proyecto dentro del Editor de VBA):

Sub formulario()
UserForm1.Show
End Sub

Seguir leyendo...

Crear un formulario (UserForm) en Excel I.

Hace mucho tiempo que estoy buscando el momento para poder subir al blog una explicación de como crear un UserForm (o sea un formulario) desde el Editor de VBA de Excel, esto es , vamos a generar un formulario personalizado.
Lo primero es tener claro cómo será este formulario, y para no complicar demasiado el asunto, he decidido que nuestro formulario tendrá dos ComboBox (también conocido como Cuadro combinado) y un TextBox (o Cuadro de texto), así como un CommandButton (o Botón de comando), además de algunas Labels (o Etiquetas).
La idea, para no dispersarme demasiado es crear la estructura de un Formulario en el que aparezca junto a una etiqueta un Cuadro combinado, y debajo de estos una nueva etiqueta junto a un Cuadro de texto, acabando el formulario con un Botón de comando que ejecute una acción. Quedará algo así:

La idea es que mediante uno de los ComboBox podamos elegir una de las hojas del libro activo, mientras que con el segundo Combobox seleccionemos de un listado dado uno de sus elementos, para que una vez añadido un valor en el TextBox, al presionar el CommandButton copie dicho valor en la hoja correspondiente y en el lugar asociado al elemento del segundo Combobox
Veamos cual es nuestro Libro de trabajo, sobre el cual va a funcionar nuestro Userform. En este Libro existen tres hojas ('España', 'México' y 'Colombia'),y una más ('Consolidado'), donde se Consolidan las anteriores; cada hoja (cada país) tiene un listado de clientes único para todas ellas, que corresponden a un número de unidades de un determinado producto y colocadas en las mismas referencias:

haz click en la imagen

Como ya conocemos nuestro Libro de trabajo, podemos empezar a crear el formulario, para ello abriremos el Editor de VBA presionando Alt+F11, y buscaremos el Menú Insertar > UserForm:

Seguir leyendo...

Repetir etiquetas de elementos en Tablas dinámicas de Excel.

Trataré hoy una opción poco empleada, como es la de Repetir las etiquetas de elementos al construir nuestras tablas dinámicas:

...¿cómo puedo repetir las etiquetas de elementos en una tabla dinámica?...

Como siempre trabajaremos sobre un sencillo ejemplo. Partimos de la siguiente Tabla de datos (o Lista):

El siguiente paso es construir en nuestro Excel la Tabla dinámica, para lo cual agregaremos el 'campo1' y el 'campo2' al área de filas, y el 'campo3' al área de datos, asignándole al Diseño de informe, desde Herramientas de tabla dinámica > Pestaña Diseño > Diseño > diseño de informe > Mostrar en forma de esquema (ó Mostrar en forma tabular).
Podemos ver como quedaría:

Seguir leyendo...