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...

UDF para sumar valores en negrita en un rango de Excel.

A través de un correo reciente un lector del blog consultaba la posibilidad de Sumar sólo los valores en negrita para un rango de celdas dadas. La consulta decía:

...Quiero consultar sobre cómo puedo sumar los números que están en negrita de un rango de datos, ósea de una columna que contenga números en negrita y sin negrita, que me sume solamente los que están en negrita...

Por desgracia no existe una forma de realizar esta acción con las herramientas estándar de Excel, por lo que he decidido generar nuestra propia función personalizada que sume los valores correspondientes.
Supongamos el siguiente listado de valores de una Tabla:

El objetivo es claro, desarrollar una función en Excel que sume, para un rango dado, los valores con un formato de negrita.

En un módulo del Editor de VBA insertaremos el siguiente código:

Public Function sumabold(Rng As Range)
Dim celda As Object
Application.Volatile ' opcional
'La función se actualizará cuando se efectúe un cálculo en alguna celda de la hoja
'en la que aparezca esta función.

For Each celda In Rng
    If celda.Font.Bold = True Then
    sumabold = sumabold + celda.Value
    End If
Next
sumabold = sumabold

End Function

Antes de volver a nuestra Hoja de cálculo, es importante para que nuestra función sumabold actualice ante cualquier cambio que haya en nuestra hoja, añadir al código de la 'Hoja1' (en nuestro caso) la siguiente instrucción:

Private Sub Worksheet_selectionchange(ByVal Target As Range)
'Para todos los libros abiertos, fuerza un cálculo completo de los datos
'y vuelve a establecer las dependencias
Application.CalculateFullRebuild
End Sub

Ya estamos listos para trabajar con nuestra función en nuestro Libro de trabajo, si insertamos nuestra función sumabold vemos:

Seguir leyendo...

Modelo 347 por trimestres para el 2012 con Excel.

En un correo reciente un lector del blog consultaba la posibilidad de automatizar la agrupación, por ciertas condiciones, de ciertos datos; concretamente quería poder aplicarlo sobre la elaboración de un impuesto (modelo 347 - Declaración anual de operaciones con terceras personas (ver)). Para esto utilizaremos las Tablas dinámicas de Excel, empleando sus herramientas de agrupación y filtro.
La consulta decía:

...En Febrero de 2012 he de declarar la facturación total del 2011 de clientes y proveedores que superen los 3005,06 euros, en el Modelo 347 de Hacienda. Los importes los hallo mediante SUBTOTALES por CIF y suma de Total Fra., pero la novedad y como consecuencia problema, es que solicitan también los importes trimestrales de los que superen dicha cifra y no sé como detallar estos datos de forma automática...

Logícamente partiremos de un listado de las operaciones realizadas durante el año con nuestros proveedores (igual proceder para nuestros clientes). Supongamos un listado de proveedores con los que hemos trabajado:

haz click en la imagen

A simple vista podemos comprobar cómo para cada proveedor, todas sus facturas acumuladas por trimestres, ya algunos de ellos sobrepasan la cantidad estipulada (3.005,06 euros) en el año.

Para conseguir nuestro objetivo, lo primero que haremos será Insertar una Tabla dinámica (o en nuestro caso Resumir la Tabla con Tabla dinámica); agregando el campo 'FECHA' al área de columnas, el campo 'CIF' al área de filas, y el Campo 'Total fra.' al área de datos. También le daremos un aspecto Tabular al diseño de nuestro informe (ver como). Nos quedaría:

Seguir leyendo...

La función SUBTOTALES en Tablas de Excel.

En un correo reciente un lector del blog preguntaba por una función de conteo en Excel:

...Necesito generar reportes de una base de datos, previamente filtrada, para ello tengo que estar cambiando manualmente el numero de resultados encontrados al aplicar el filtro, resultados que por lo general indican el número de filas encontradas (resultado que aparece en la parte inferior izquierda de la barra de estado al filtrar la información). Mi pregunta es “Existe alguna Fórmula que automáticamente devuelva el número de las filas encontrados al ir filtrando la información”...

La respuesta la podemos encontrar en un post de este mismo blog que explicaba el uso de la función SUBTOTALES (ver)... sin embargo, aprovechando esta cuestión, añadiré algo más sobre el uso de esta función en Excel 2007 o 2010 al combinar su uso con el de Tablas (o Listas).
Trabajaremos sobre una base de datos sencilla con dos campos 'Concepto' e 'Importe':

Como podemos ver, nuestro origen de datos es una Tabla de Excel, sabiendo que el método más sencillo para convertir un rango de datos en Tabla es presionando Ctrl+t (para Excel 2010) y Ctrl+q para Excel 2007 y anteriores.
El objetivo es contar el número de elementos mostrados una vez filtrado por algún o algunos de los elementos de la Tabla, por ejemplo, por el elemento 'b' del campo 'Concepto'; para ello nos iremos a la celda B13 (debajo del último registro de nuestra tabla) e insertaremos la función
=SUBTOTALES(102;[Importe])
con lo que nos aseguraremos que sólo cuente los registros visibles de nuestra tabla filtrada

Seguir leyendo...

VBA: una macro en Excel para trabajar en una hoja protegida con el Esquema.

Algunos días atrás, en un comentario un lector preguntaba sobre la posibilidad de trabajar con los Esquemas en una hoja protegida:

...estoy necesitando "utilizar el esquema" en una hoja con protección. ¿Esto es imposible?...

La respuesta que le dí era que no si se trabajaba con el estándar de Excel, pero que sí podría hacerlo si incluía el siguiente código.
Si trabajamos sobre el siguiente ejemplo, donde hemos desarrollado un Esquema:

es fácil comprobar cómo si tenemos nuestra hoja de trabajo protegida, no es posible operar con las herramientas 'Agrupar'/'Desagrupar' o con la de 'Mostrar' u 'Ocultar detalle'...

Sin embargo, mediante el uso de macros podremos saltar este inconveniente. Incluiremos el siguiente código en el Explorador de Proyectos en ThisWorkBook:

Private Sub Workbook_Open()
With Sheets("Hoja1")
.Protect Password:="xxx", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub

Conseguiremos, entonces con este evento Workbook_Open que cada vez que abramos este Libro de trabajo, se proteja la 'Hoja1' con la contraseña 'xxx' y que además con la propiedad UserInterfaceOnly:=True se proteja sólo la interfaz de usuario, pero no las macros.
Por último, la línea de código VBA
WorkSheet.EnableOutlining = True
activa los símbolos de esquema en una hoja de cálculo protegida.

es decir, habilita poder trabajar con el Esquema en nuestras hojas de Excel protegidas.

Seguir leyendo...

VBA: Asociar los elementos de un ComboBox a un campo de tabla dinámica en Excel.

Hace algún tiempo me llegó un mail solicitando ayuda sobre un tema que ya entonces llevaba tiempo pensando en subir al blog. Se trata de asociar los elementos de un ComboBox a un campo de tabla dinámica en Excel. Concretamente lo explicaré basándome en la petición del lector.:

...Tengo 15 graficas (pivot charts) que he conseguido con una macro se actualicen al mismo tiempo cuando cambio el filtro en una de ellas. Ahora le jefe quiere que en cada una de esas graficas distinga con un color or etiqueta una sola de las series. Me explico: Los graficos son serie de barras, cada serie es un cliente y cada grafico es un producto diferente. Nuestro departamento quiere enviar cada mes esos graficos a cada cliente para informarle como esta su consumo en relacion al resto, pero sin que conozca quienes son el resto de clientes. Neceisto identificar ese cliente al que le enviare los graficos, con un color de barra diferente o con una etiqueta en la serie ¿Es posible construir una macro para hacer esto o hay alguna otra manera?...

Lo que pide nuestro amigo es que , basándonos en los elementos de un campo de una Tabla dinámica, poder seleccionar fuera de la tabla dinámica, por ejemplo, con un ComboBox uno de los elementos, y que ese elemento aparezca en el Gráfico dinámico con una columna de color distinto a los demás elementos.
Veamos cuál es nuestro origen de datos y como queda tanto nuestra Tabla dinámica como nuestro Gráfico dinámico:

Simplemente hemos generado una Tabla (Ctrl+q) sobre nuestro origen de datos, y la hemos resumido con una Tabla dinámica, de la cual hemos generado un Gráfico dinámico tal cual muestra la imagen.
Nuestro objetivo ahora es conseguir que a nuestra elección, sobre un Concepto cambie nuestro gráfico, mostrando la columna del 'Concepto' seleccionado de otro color.
Para ello, en primer lugar, insertaremos un Combobox(cuadro combinado) desde la Ficha Programador > Grupo opciones Controles > Insertar > Controles ActiveX > Cuadro Combinado

Seguir leyendo...

Trabajar con Fechas y Horas en Excel.

Tiempo atrás expliqué en un post la función CONVERTIR (ver), fundamental para poder trabajar con fechas y horas en nuestras hojas de cálculo.
Hay atenderemos la petición de un lector con problemas para obtener la diferencia de horas trancurridas entre dos fechas y horas diferentes, que además se encuentran partidas en celdas diferentes.
Veamos el planteamiento:

Para poder contabilizar la diferencia entre ambas fechas y horas, en primer lugar debemos comprender que si sumamos una fecha a una hora, obtendríamos un valor equivalente al de una única celda con ese formato, por ello, el problema se resuelve de manera sencilla, ya que si restamos las sumas de ambas fechas y horas tendríamos un decimal que representa la fracción del día trasncurrida entre una y otra. Es decir, si sumamos D4+E4 obtendríamos la fecha 12/10/2011 11:38, con el mismo valor que existiera en una sóla celda.

En nuestro ejemplo, si restamos 12/10/2011 11:38 a 11/10/2011 19:00 tendríamos que han pasado 0,69 días; pero como necesitamos saber a cuántas horas equivale, le aplicaríamos la función CONVERTIR:
=CONVERTIR(D4+E4-(F4+G4);"day";"hr")
que pasa de días a horas.

haz click en la imagen

Seguir leyendo...

Matriz de constantes: una matricial para contar valores divisibles por otro dado.

De manera similar a como hicimos en una entrada anterior (Matriz de constantes: una matricial para contar valores pares), en esta ocasión, determinaremos para el mismo rango de datos, cuántos valores son divisibles por 50, para lo que emplearemos la función RESIDUO.
El listado a evaluar es:

De la entrada anterior (ver) sabemos cómo trabajar, por lo que ahorraremos algunos pasos idénticos.
Con la función
{=SI(RESIDUO(1*EXTRAE($A2;{4;8;12;16;20;24;28;32}-3;3);50)=0;1;0)}
tendríamos un rango de 1 y 0 para cada uno de los ocho valores y cada una de las celdas del rango a estudiar A2:A11:

Seguir leyendo...

Matriz de constantes: una matricial para contar valores pares.

En este post explicaré el uso de una Matriz de constantes; en concreto usaremos este tipo de Matriz para conseguir calcular el número de valores pares existente en un listado de datos algo peculiar...
Veamos este listado:

Observamos que nuestros datos se encuentran en el rango A2:A11 por filas, pero que existen hasta ocho valores en cada celda, separados por el caracter '/'. Nuestro trabajo será construir una única fórmula matricial que calcule el número total de valores pares existente entre todos esos valores.
Lo más importante será determinar una Matriz de constantes sobre la que trabajar para poder, y posteriormente, incorporar a nuestra definitiva matricial.
Una Matriz de constantes no es más que una matricial cuyos elementos son valores fijos. Esta matriz la podemos construir bien dándole una orientación horizontal, como será la de nuestro ejemplo, algo del tipo ={1;2;3;4}; o bien con una orientación vertical ={1\2\3\4}... por supuesto, combinando ambos signos de puntuación, tendríamos una matriz de constantes bidimensional ={1;2;3\4;5;6}.
Conviene, normalmente, ejecutarlas como cualquier otra matricial, esto es, presionando Ctrl+Mayusc+Enter.
Nuestra matriz de constantes será:
={4;8;12;16;20;24;28;32}
que son precisamente las posiciones de cada uno de los caracteres '/' que dividen cada celda en ocho valores.
De hecho, podríamos obtener con una función EXTRAE los ocho valores, cada uno en una celda empleando esas posiciones
=EXTRAE($A2;C$1-3;3):

Seguir leyendo...

UDF para convertir números en letras.

En varias ocasiones me han preguntado si existe alguna forma de convertir en texto nuestro números, y aunque es un tema muy recurrido por todo internet, creo oportuno incluir aquí una de las funciones que más me han gustado entre las que he encontrado en el tiempo. Por supuesto existen muchas más versiones, unas más perfectas que otras...
En concreto, para resolver la duda de uno de los lectores, será suficiente con el código VBA de este post. Ojo, ya que la función sólo es válida para valores entre 0 y 999.999:

Hola quiero saber si hay alguna formula que convierta los números en letras Ejemp.tengo un cuadro de notas y necesito que al colocar las Notas en una columna en número estas me las coloque en otro columna en letras. Ejemplo. Notas en letra 15 Quince 10 Diez 12 Doce 05 Cinco 09 Nueve

Incluiremos los siguientes procedimientos Function en uno de los módulos del Editor de VBA:

Function num2let(importe)
If Int(importe) = 1 Then final = " euro" Else final = " euros"
If importe > 999 Then num2let = num2letras(Int(importe / 1000)) + " mil "

num2let = num2let + num2letras(importe - Int(importe / 1000) * 1000) + final

If Int(importe) = 0 Then num2let = "cero euros"

If importe <> Int(importe) Then
If (100 * (importe - Int(importe))) = 1 Then centfin = " céntimo." Else centfin = " céntimos."
num2let = num2let + " con " + num2letras((importe - Int(importe)) * 100) + centfin
End If
End Function
Function num2letras(importe)
'Pasa de numero a letras, importes entre 0 y 999
'Centena ----------------------------------------
centena2 = Int(importe / 100)
Select Case centena2
Case 0: num2letras = ""
Case 1: num2letras = "cien"
Case 2: num2letras = "doscientos"
Case 3: num2letras = "trescientos"
Case 4: num2letras = "cuatrocientos"
Case 5: num2letras = "quinientos"
Case 6: num2letras = "seiscientos"
Case 7: num2letras = "setecientos"
Case 8: num2letras = "ochocientos"
Case 9: num2letras = "novecientos"
End Select
'Decena ----------------------------------------------
decena2 = Int((importe - centena2 * 100) / 10)
If centena2 = 1 Then num2letras = num2letras + "to"
Select Case decena2
Case 1: num2letras = num2letras + " diez"
Case 2: num2letras = num2letras + " veinte"
Case 3: num2letras = num2letras + " treinta"
Case 4: num2letras = num2letras + " cuarenta"
Case 5: num2letras = num2letras + " cincuenta"
Case 6: num2letras = num2letras + " sesenta"
Case 7: num2letras = num2letras + " setenta"
Case 8: num2letras = num2letras + " ochenta"
Case 9: num2letras = num2letras + " noventa"
End Select
'Unidad ----------------------------------------------
unidad2 = Int((importe - centena2 * 100 - decena2 * 10))
If decena2 = 0 Then
Select Case unidad2
Case 1: num2letras = num2letras + " un"
Case 2: num2letras = num2letras + " dos"
Case 3: num2letras = num2letras + " tres"
Case 4: num2letras = num2letras + " cuatro"
Case 5: num2letras = num2letras + " cinco"
Case 6: num2letras = num2letras + " seis"
Case 7: num2letras = num2letras + " siete"
Case 8: num2letras = num2letras + " ocho"
Case 9: num2letras = num2letras + " nueve"
End Select
End If

If decena2 = 1 Then
num2letras = Mid(num2letras, 1, Len(num2letras) - 4)
Select Case unidad2
Case 0: num2letras = num2letras + " diez"
Case 1: num2letras = num2letras + " once"
Case 2: num2letras = num2letras + " doce"
Case 3: num2letras = num2letras + " trece"
Case 4: num2letras = num2letras + " catorce"
Case 5: num2letras = num2letras + " quince"
Case 6: num2letras = num2letras + " diceciseis"
Case 7: num2letras = num2letras + " diecisiete"
Case 8: num2letras = num2letras + " dieciocho"
Case 9: num2letras = num2letras + " diecinueve"
End Select
End If

If decena2 = 2 Then
If unidad2 <> 0 Then num2letras = Mid(num2letras, 1, Len(num2letras) - 6)
Select Case unidad2
Case 1: num2letras = num2letras + " veintiun"
Case 2: num2letras = num2letras + " veintidos"
Case 3: num2letras = num2letras + " veintitres"
Case 4: num2letras = num2letras + " veinticuatro"
Case 5: num2letras = num2letras + " veinticinco"
Case 6: num2letras = num2letras + " veintiseis"
Case 7: num2letras = num2letras + " veintisiete"
Case 8: num2letras = num2letras + " veintiocho"
Case 9: num2letras = num2letras + " veintinueve"
End Select
End If

If decena2 > 2 Then
Select Case unidad2
Case 1: num2letras = num2letras + " y un"
Case 2: num2letras = num2letras + " y dos"
Case 3: num2letras = num2letras + " y tres"
Case 4: num2letras = num2letras + " y cuatro"
Case 5: num2letras = num2letras + " y cinco"
Case 6: num2letras = num2letras + " y seis"
Case 7: num2letras = num2letras + " y siete"
Case 8: num2letras = num2letras + " y ocho"
Case 9: num2letras = num2letras + " y nueve"
End Select
End If

If importe = 100 Then num2letras = "cien"
End Function

Aunque existen realmente dos funciones en este procedimiento, la función a aplicar será
=num2let(valor)
es ésta la que convierte a texto cualquier valor entre 0 y 999.9999.

Este código lo conseguí navegando hace algún tiempo, y encontré en algún foro de Excel, donde alguien (no puedo recordar quién, y lo siento de veras...) lo subió y compartió con todos... Gracias a esta persona!!

Seguir leyendo...

VBA: La funcion SPLIT en una macro de Excel.

Hoy pondré un ejemplo del uso de la función SPLIT en nuestras macros. Se trata de responder a la cuestión planteada por un lector:

...Mi problema es el siguiente en una celda me vota un dato separado por comas (2654500543210,211,213,214) lo que he realizo hasta el momento es evaluar el largo y decirme cuantos items hay, en este caso serian 4, lo que quiero hacer es que al encontrar mas de 1 item me inserte una fila por cada item nuevo con los mismos datos que hay en la misma fila y coloque un nuevo registro quedando de la siguiente forma 2654500543210 - IUYDHN 2654500543211 - IUYDHN 2654500543212 - IUYDHN 2654500543213 - IUYDHN

Lo que haremos será evaluar la celda en cuestión, de tal forma que mediante la función SPLIT, separemos los diferentes valores existentes en la celda, asignándolos posteriormente a elementos de una matriz o Array.
La última parte de la macro consistirá en añadirle el texto " - IUYDHN", y sustituir los últimos dígitos del valor principal.
Sabiendo que nuestros datos se encuentran en la 'Hoja1' y que la celda a evaluar es la A1, el código a incluir en un módulo del Editor de VBA (Alt+F11) será:

Sub separar()
Dim cadena As String, n As Long
Dim matriz() As String
Dim i As Long, izq As Long

'cadena será el contenido de la celda a evaluar
cadena = Range("A1").Value

'asignamos cada parte como un elemento de la matriz
'la función SPLIT devuelve una matriz que contiene un número especificado de subcadenas
'obtenidas o delimitadas por el operador dado
matriz = Split(Trim(cadena), ",")

'contamos el número total de elementos
n = UBound(matriz) + 1
izq = Len(matriz(0)) - 3

'obtenemos en las celdas siguientes los textos obtenidos
For i = 1 To n
If i = 1 Then
Range("A1").Offset(0, i + 1).Value = matriz(i - 1) & " - IUYDHN"
Else
Range("A1").Offset(0, i + 1).Value = Left(matriz(0), izq) & matriz(i - 1) & " - IUYDHN"
End If
Next
End Sub

El resultado será el esperado al ejecutar la macro, ya que en las columnas anexas nos dispone los valores de texto correctos:

haz click en la imagen

Seguir leyendo...

Obtener la letra de una columna en Excel.

Me llegaba hace algunos días una petición curiosa, un usuario quería conocer, en base a unas coincidencias, cuál era la letra de una columna concreta. Como el ejercicio era algo complejo, me centraré sólo en la forma de conseguir para un caso general, la letra de una columna particular; fijémosnos que digo letra y no número, ya que este último es fácil de lograr con la función COLUMNA.
El asunto no es tan sencillo como pudiera parecer en un principio, de hecho vamos a necesitar anidar varias funciones una dentro de otra.
Las funciones a emplear ya son viejas conocidas:

• SUSTITUIR: con esta reemplazaremos, realmente eliminaremos, caracteres que nos estorban para nuestro objetivo


• DIRECCION: definiremos la celda a evaluar, para obtener de ella la letra de su columna.


• FILA y COLUMNA: la base de toda la propuesta, con éstas obtendremos una numeración para definir una celda.

Combinando todas estas funciones obtendríamos la siguiente fórmula:
=SUSTITUIR(SUSTITUIR(DIRECCION(FILA();COLUMNA());"$";"");FILA();"")
que devolverá, se encuentre donde se encuentre, la letra de la columna, sin restricción.
La analizaremos de dentro hacia afuera:
con =DIRECCION(FILA();COLUMNA()) tendremos la referencia absoluta de la celda donde se encuentre la fórmula.
El siguiente paso será quitar de esa referencia lo que no nos hace falta, es decir, como sólo quiero la letra de la columna, tendremos que SUSTITUIR el símbolo del dolar $ y el número de la FILA,
lo que conseguimos con un doble anidamiento con la función SUSTITUIR, reemplazando $ y la FILA() por nada "".
Podemos comprobar como independientemente de donde copiemos dicha fórmula siempre obtendremos la letra de la columna buscada:

Seguir leyendo...

Formato de eje tipo texto.

Pregunta un lector, a través de un comentario del blog:

...Estoy usando un gráfico con fechas y datos númericos. En la columna de fecha solo introduje los días laborables, sin embargo en el gráfico me aparecen todos los días consecutivos y por consiguiente los dias festivos que no hay datos en el gráfico se produce un salto que distorsiona el resultado. ¿Como hago que en el eje de fechas solo aparezan los dias laborables?...

El problema está en que Excel al entender que los valores del eje son Fechas no sabe discriminar entre los valores reales de nuestro origen de datos y los que él entiende que son valores de una Serie cronológica.
Supongamos una serie de datos sobre los días laborables de dos meses cualesquiera:

Podemos ver cómo aparecen por defecto los valores en nuestro eje, esto es, todas los días del mes, laborables o no... con la consiguiente distorsión del gráfico:

La clave para resolver este asunto es engañar a Excel, haciéndole pensar que nuestras Fechas laborables no son Fechas, si no que son TEXTO; y esto lo conseguiremos, con el grafico ya creado, desde Dar Formato a eje..., y luego buscaremos en las Opciones del eje > Tipo de eje: Eje de texto

Seguir leyendo...

La función RESIDUO, otra forma de comprobar si un número es divisible de otro dado.

En una entrada anterior comprobamos en qué forma podíamos verificar que un valor era divisible por otro (ver). En este post aprenderemos como realizar la misma operación, esto es, cómo comprobar si un número es divisible de otro dado utilizando para ello la función RESIDUO.
Veamos la sintaxis de la función:
=RESIDUO(número; divisor)
Al igual que en el ejemplo anterior partimos de un listado de valores, de los que deseamos conocer cuáles son divisibles por 7:

La función para cada valor es entonces:
=RESIDUO(valor;7)
sabiendo que esta función devuelve el resto del cociente entre el valor y el divisor, concluimos que el número a evaluar será divisible por 7 cuando el resto o RESIDUO sea cero.
Situación que se confirma para los valores múltiplos de 7 que aparecen en nuestro listado (7, 14, 21, 28, 42, 49 y 70).
En la columna H de nuestro ejemplo, sólo hemos anidado este resultado en un condicional para que aparezca el texto 'Divisible' o 'No':
=SI(RESIDUO(A2;7)=0;"Divisible";"No").

Seguir leyendo...

Cómo comprobar si un número es divisible de otro dado.

Hoy analizaremos una utilidad de las funciones MULTIPLO.INFERIOR y MULTIPLO.SUPERIOR, usando ambas para comprobar si un valor es divisible por otro.
Conozcamos, antes de continuar, cuál es la sintáxis de ambas:

• MULTIPLO.INFERIOR(número; cifra_significativa)


• MULTIPLO.SUPERIOR(número; cifra_significativa)

para ambas funciones el significado de sus argumentos será:
número: es el valor numérico que se desea redondear;
cifra significativa: es el múltiplo al que se desea redondear.
esto es, para un valor determinado la cifra significativa es el valor o múltiplo de este al que nos acercará nuestra función.
Bien, no nos dispersemos, se trata de averiguar si un número es divisible de otro.
Disponemos de un listado de valores, al azar, del que deseamos discernir si son divisibles por 7.

Analicemos el significado de estas fórmulas. Por ejemplo, para el primer valor (celda A2 = 7), al aplicar la función MULTIPLO.INFERIOR vemos que devuelve el valor 7 al igual que aplicar la función MULTIPLO.SUPERIOR, lógico, ya que el múltiplo de 7 más cercano por arriba-superior y por debajo-inferior es él mismo. Para un valor de 17, el múltiplo de 7 más cercano a 17 si llegar a éste sería 14, y por encima alcanzaríamos el 21.
Una vez visto este punto claro, la consecuencia para resolver nuestra cuestión, parece clara, un valor será divisible exactamente por 7, cuando ambas funciones MULTIPLO.INFERIOR y MULTIPLO.SUPERIOR tengan el mismo valor; es decir, cuando el resultado de la prueba lógica
=MULTIPLO.INFERIOR(A2;7)=MULTIPLO.SUPERIOR(A2;7)
sea VERDADERO.

Seguir leyendo...

BUSCARV como argumento en una función SI.

Habitualmente trabajamos con la función BUSCARV pensando sólo en búsquedas verticales, olvidando otros usos o utilidades; en este ejemplo de hoy veremos como nos aprovecharemos de ésta, para anidarla en una función SI condicional, como parte de la prueba lógica.
En el ejemplo que trataré hoy buscaremos una respuesta al siguiente problema. Si partimos de una tabla donde vemos para diferentes elementos qué meses están operativos, en concreto tenemos un listado de cruceros y el intervalo de meses entre los cuales, dicho crucero, está operativo; para un listado de posibles reservas atendiendo a un crucero determinado y a una fecha de embarque, tendremos que obtener una SI o un NO que determine si para dicha Fecha y Crucero tal reserva es posible.
Vemos nuestras tablas de trabajo:

haz click en la imagen

La finalidad es combinar ambos requisitos solicitados en una única prueba lógica de nuestra función condicional SI, de tal forma que devuelva un SI o un NO si se verificase o no.
Debemos construir un condición que nos diga si para un Crucero concreto la fecha de reserva propuesta cumple o está dentro del intervalo de meses definido (rango E2:G5).
Esto lo conseguiremos incluyendo la función Y como prueba lógica, de tal forma que en esta unimos dos de las condiciones a cumplir, para cada Fecha de reserva sea mayor o igual que el primer mes de inicio de operaciones y que sea menor o igual que el último mes operativo del crucero:
Y(Fecha reserva>=Fecha inicio operaciones; Fecha reserva<=Fecha fin operaciones)
Como estamos trabajando con meses, trataremos los datos con la función MES.
La clave, en todo caso de esto, es determinar correctamente cuál es la Fecha de inicio y fin de operaciones para cada Crucero a reservar. Esto lo conseguiremos con la función BUSCARV, siendo las condiciones finales:
MES(Fecha reserva)>=BUSCARV(Crucero;$E$2:$G$5;2;0)
MES(Fecha reserva)<=BUSCARV(Crucero;$E$2:$G$5;3;0)
todo unido en una sóla función, y tomando las celdas a evaluar para el primer registro, tenemos la siguiente Prueba lógica:
Y(MES(B2)>=BUSCARV(A2;$E$2:$G$5;2;0);MES(B2)<=BUSCARV(A2;$E$2:$G$5;3;0))

Seguir leyendo...

Encontrar fechas con matriciales.

Hoy recuperaremos nuestra matriciales para encontrar un dato dentro de una Tabla que cumpla varios requisitos al tiempo. Se trata de localizar la primera fecha que corresponda al último trabajo desarrollado por cada empleado de un listado:

...Imaginemos que tenemos una lista de Nombres, (en el ejemplo pongo dos, Pepe (25) y Paco (50) pero pueden ser muchos más). Todos ellos tienen un histórico con los puestos que han ocupado en sus trabajos, con la fecha de inicio y los centros en los que han trabajado. Lo que yo necesito es, (y está en rojo). Sacar un listado con el nombre y LA FECHA EN LA QUE EMPEZÓ EN SU ÚLTIMO PUESTO. Por ejemplo; Pepe es Cocinero desde 01/05/2004 y Paco es arquitecto desde 01/04/2007. Necesito una fórmula que discrimine el resto de fechas y solo tenga en cuenta la fecha en la que comenzaron su trabajo y sigan actualmente en el mismo....

Asignamos nombres a los diferentes campos del listado:
centro =Hoja1!$E$2:$E$12
Fecha =Hoja1!$D$2:$D$12
ID =Hoja1!$A$2:$A$12
Nombre =Hoja1!$B$2:$B$12
Profesion =Hoja1!$C$2:$C$12
Fijémosnos en que las condiciones son tres, la primera por ID de empleado, la segunda por Profesión desarrollada y la tercera que deberá ser la menor de las fechas de la última Profesión desarrollada.
La clave de la función matricial que vamos a implementar es encontrar en primer lugar la última Profesión desarrollada de cada empleado, esto lo lograríamos con una matricial (Ctrl+Mayus+enter) de este tipo:
{=MAX(SI(ID=G2;Fecha))}
que nos dice la última Fecha en la que comenzó alguna Profesión; si anidamos esta Fecha en una función INDICE obtendremos la última Profesión:
{=INDICE(Profesion;COINCIDIR(MAX(SI(ID=G2;Fecha));Fecha;0))}
para el empleado con ID igual al valor de la celda G2, buscamos la última fecha, para luego encontrar la correspondencia sobre el rango Profesión.
Acabamos con la matricial:
{=MIN(SI(Profesion=INDICE(Profesion;COINCIDIR(MAX(SI(ID=G2;Fecha));Fecha;0));Fecha))}

Seguir leyendo...

Validación de datos personalizada.

Solucinaremos el problema de un lector con la Validación de datos de una celda formulada, o lo que es lo mismo con una Validación de datos personalizada:

...estoy trabajando en una hoja de cálculo y pretendo que en una casilla me sume un rango de celdas determinado, hasta aquí facil y sin problema; peró además he intentado introducir en esta casilla una regla de validación mediante la cual si se sobrepasa una cantidad fijada en la regla me salga un mensaje de error y no consigo que funcione, si introduzco directamente una cantidad superior a la permitida en la casilla si que sale el aviso, peró mediante la suma no, no se si es que no permite esta función o si debo hacerlo medianta algun otro sistema....

El problema es claro, en una celda tenemos introducida un fórmula de SUMA sobre otro rango de celdas, y pretendemos que la primera está validada, permitiendo sólo determinado rango de valores; sin embargo, Excel, con esta herramienta de Validación de datos sólo restringe la introducción directa de datos.
¿Cómo solucionar este inconveniente?, atacaremos por detrás a la celda a restringir.
Lo vemos con un sencillo ejemplo como siempre.
Tenemos un rango A1:C1 con diferentes valores, que sumaremos en D1. Será la celda D1 donde sumaremos el rango anterior, esto es, en D1:
=SUMA(A1:C1)
es precisamente la celda D1 la que queremos limitar entre un rango de valores, por ejemplo, que esté entre 5 y 8, es decir, pretendemos que la suma del rango A1:C1 quede siempre dentro del intervalo 5 y 8.

Seleccionamos el rango de celdas A1:C1, y desde la Ficha Datos > Herramientas de datos > Validación de datos

Seguir leyendo...

Informe de gráfico dinámico según elemento.

Tiempo atrás expliqué diferentes formas, con y sin macros, de mostrar series de un gráfico con diferentes formatos (ejemplo 1 y ejemplo 2).
En esta ocasión conseguiremos un efecto similar, empleando el Informe de gráfico dinámico.
En concreto se trata de distinguir uno de los elementos de una serie del origen de datos, elegido por nosotros mediante una celda validada.
Nuestros datos de partida están en la Tabla siguiente:

Vemos como se trata de un listado de registros, en los que para cada uno de ellos nos aporta información del producto vendido (pdto1, pdto2 ó pdto3) a un cliente concreto (clienteA, clienteB ó clienteC) por una cantidad.
Queremos obtener un gráfico de barras que muestre resumido el volumen de pedidos, marcando de otro color al cliente seleccionado o elegido por nosotros.
Para ello, comenzamos configurando una Celda validada tipo lista con los tres clientes, por ejemplo en la celda G2.
También añadiremos a nuestra 'Tabla' de datos dos columnas auxiliares (una con los datos del cliente seleccionado y otra para el resto de clientes). Como estamos trabajando con 'Tablas' la fórmula de nuestras columnas auxiliares serán:
importeCliente: =SI(Tabla1[[#Esta fila];[Clientes]]=$G$2;Tabla1[[#Esta fila];[importes]];"")
importeResto: =SI(Tabla1[[#Esta fila];[Clientes]]=$G$2;"";Tabla1[[#Esta fila];[importes]])
éstas son simplemente condicionales que devuelven el importe si el código de 'Clientes' coincide con el valor de la celda G2, es decir, con el cliente seleccionado.

Seguir leyendo...

Asignar un nombre a una fórmula en Excel.

Sí, has leído bien, no se trata de Asignar un nombre a un rango, si no de Asignar un nombre a una fórmula. La entrada de hoy explicará cuáles son las ventajas de dar nombres a nuestras fórmulas.
Como siempre, veámoslo con un ejemplo.
Tenemos una tabla de categorías con un valor correspondiente; y sobre estos valores tenemos un listado de 15 registros aleatorios:

Sobre este listado aleatorio encontraremos:

• Cálculo del minimo valor acumulado por categoría(sin contar el valor cero)


• En qué Categoría la suma de sus Valores fue mínimo(sin contar el valor cero)

Para responder ambas cuestiones, partiremos del mismo trabajo.
En primer lugar, por comodidad Asignaremos un nombre a los rangos del listado a evaluar:
Categoría =Hoja1!$D$3:$D$17
Valor =Hoja1!$E$3:$E$17

Ahora ya podemos construir una fórmula matricial a la que asignaremos un nombre.
Una para cada 'Categoría':
Cat_a =SUMA(SI(Categoría="a";Valor))
Cat_b =SUMA(SI(Categoría="b";Valor))
Cat_c =SUMA(SI(Categoría="c";Valor))
Cat_d =SUMA(SI(Categoría="d";Valor))
Cat_e =SUMA(SI(Categoría="e";Valor))
Cat_f =SUMA(SI(Categoría="f";Valor))

Seguir leyendo...

BUSCARV y COINCIDIR: una búsqueda cruzada.

Estamos acostumbrados a trabajar con la función BUSCARV pensando sólo en búsquedas verticales, digamos en una sóla dimensión, debiendo hacer uso de otras funciones, como DESREF para otros tipos de búsquedas; sin embargo, veremos hoy como combinándola con la función COINCIDIR podremos encontrar nuestro valor buscado sobre las dos dimensiones de nuestras tablas.
Nuestro ejemplo será muy sencillo, pero representativo. Necesitamos obtener un valor en función de dos variables ('Categoría hotel' y 'Tipo habitación'); ambos parámetros cruzan en una tabla, de la que obtendremos el precio para cada combinación posible.
Veámoslo:

haz click en la imagen

Se trata por tanto, en base a los valores de las columnas A (Categoría) y B (Tipo habitación), determinar cuál es el precio de la habitación.
Nos aprovecharemos de la disposición de valores en la tabla de precios (E1:I8), donde por filas podemos reconocer la 'Categoría' del hotel y por columnas el 'Tipo de habitación'; con la función
COINCIDIR(B2;$F$2:$I$2;0)+1
obtendremos el número de columna coincidente con el Tipo de habitación, que emplearemos con argumento Indicador de columna de la función BUSCARV.
Si la anidamos, tenemos
=BUSCARV(A2;$E$3:$I$8;COINCIDIR(B2;$F$2:$I$2;0)+1;0)
es decir, buscamos en la matriz o tabla E3:I8, el valor de la celda A2 (esto es, la Categoría del hotel), y buscamos su precio en el número de columna obtenido con la función COINCIDIR.

Seguir leyendo...

Conciliar una partida con Solver.

Vamos a ver cómo podemos encontrar, de un listado de valores, aquellos que suman una cantidad definida por nosotros; esto es, vamos a conciliar una partida con un listado de valores. Para esto emplearemos Solver.
Si bien, hay que advertir que esta aplicación será válida sólo en aquellos casos que la combinación de valores que suman el valor a conciliar (el valor buscado) es única.
Veamos nuestro listado de valores:

Convertiremos nuestro listado en una Tabla (Ctrl+q), y luego con la Tabla seleccionada, Insertaremos una columna de tabla a la derecha, que nombraremos como 'binario'.
También asignaremos nombre a los dos campos:
binario =Hoja1!$B$2:$B$17
Cantidades =Hoja1!$A$2:$A$17
Por último, como parte importante a la hora de configurar Solver, en la celda D2 insertaremos la función:
=SUMAPRODUCTO(binario;Cantidades)
esta función se encargará de multiplicar fila a fila, elemento a elemento, los valores del rango 'binario' y del rango 'Cantidades'.
Esta celda, será considerada en la configuración como la Celda objetivo.
Ejecutamos Solver, desde Datos > Análisis > Solver:

Seguir leyendo...