Gráficos según celda de validación.

Tiempo atrás se me planteó un problema sobre la forma de construir un gráfico condicionando las 'Series' mostradas a una celda validada; en su momento me pareció interesante la manera de utilizar los nombres (ver) dentro del especial mundo de los gráficos.
Lo explicaré sobre un ejemplo sencillo.
Supongamos un origen de datos:

Nuestro trabajo consistirá en mostrar una serie concreta en un gráfico cualquiera, por ejemplo tipo columnas, en función al valor de una celda validad con tipo lista.
Lo más importante en estos casos es asignar correctamente los nombres a los rangos de datos, así como emplearlos en la forma adecuada dentro de las formulaciones de los gráficos.
Los nombres asignados a rangos serán para nuestro ejemplo:
españa =Hoja1!$B$2:$B$7
portugal =Hoja1!$C$2:$C$7
pais =Hoja1!$B$1:$C$1
datos =INDIRECTO(Hoja1!$F$1)
los dos primeros ('españa' y 'portugal') son los rangos donde se encuentran los valores a incluir en el gráfico. El nombre 'pais' servirá para listar los valores en la celda validada. El más importante es el nombre 'datos', ya que será este el que se incluirá en la fórmula del gráfico (como vemos se le ha añadido la función INDIRECTO para que Excel reconozca el texto de la celda validada como un rango).

Seguir leyendo...

La función DESREF aplicada a búsquedas.

Hace algunos días me llegó un correo planteándome una cuestión sobre la forma de construir un condicional SI anidado:

...En la formula siguiente hay algo que está mal y se me escapa: error #¡VALOR!, ¿sabe alquien por qué? Un saludo =SI(Y(A2<=100;C2="contado");$B$14;$C$14);SI(Y(A2>100;A2<150;C2="contado");$B$15;$C$15);SI(Y(A2>150;A2<150;C2="contado");$B$16;$C$16);SI(Y(A2>200;C2="contado");$B$17;$C$17) Te adjunto el problema. En la columna F, tengo que hallar los dtos, que dependerán del tamaño del pedido y de si el pago es al contado o a 30 días exclusivamente...

Mi respuesta, después de ver el ejemplo, fue clara, no siempre la función SI condicional es la respuesta óptima. Mi recomendación fue el uso de la función DESREF, de la que ya hablé en esta entrada del blog.
En definitiva se trata de determinar un porcentaje de 'Descuento' condicionado a dos variables ('Cantidad de pedido' y 'Forma de pago'), siendo nuestras tablas de trabajo:

Sobre el rango A8:E12 deberemos obtener el porcentaje de descuento que le corresponde, descuento sólo válido para los pagos al contado o aplazados 30 días; esto es, para el resto de formas de pago (60 días o 90 días) no existe descuento, o lo que es lo mismo, el descuento es 0%.

Seguir leyendo...

Listar elementos según criterio en una tabla.

Un usuario preguntaba la manera de listar los elementos coincidentes a un criterio dado de un origen de datos.
En concreto planteaba:

...Dispongo de un archivo en el cual tengo 2 columnas. En la columna A el nombre de un producto y en la B su condición. La condición puede estar repetida. Lo que necesito es que por mediante una formula una vez elegida la condición es obtener todos los productos que cumplan dicha condición. Aclaración ya que archivo va hacer utilizado por distintas personas las cuales no tienen el mismo nivel de conocimiento en Excel es que no utilizo filtros avanzados ni macros....

Siendo un ejemplo de origen de datos:

Para conseguir el resultado deseado deberemos añadir un par de columnas auxiliares a nuestro origen de datos.
La primera de ellas aplicaremos a cada elemento la fórmula:
=(B2=$G$2)*1
que nos dirá cuáles de los elementos del campo 'producto' tiene la misma condición buscada (en celda G2).

Seguir leyendo...

VBA: Ejecutar macro antes de cerrar Libro: Workbook_BeforeClose.

En el post de hoy explicaré un evento de bastante utilidad como es Workbook_BeforeClose, en respuesta a un mail de un lector del blog:

...Desearia saber si me pueden ayudar con una macro que uso para cerrar una hoja de Excel y al salir grabar la hoja. La macro es la siguiente: Sub Botón2_Haga_clic_en() ActiveWorkbook.SaveAs "c:\Facturas\" & Range("AI18").Value Application.Quit End Sub La celda AI18 es el numero de la factura, pero quisiera que al cerrar y grabar la hoja también me grabara el nombre de el cliente además del numero de factura....

Emplearé nuestro evento, que se ejecuta antes de cerrar nuestros libros de trabajo, sabiendo que si nuestro libro ha sido modificado, este evento se produce antes de solicitar al usuario que guarde dichas modificaciones. Esto es, que cualquier modificación no quedaría salvada.
Por este motivo añadiremos un código bien sencillo que fuerza, ante cualquier modificación sufrida en nuestro libro de trabajo, una ventana de 'Guardar como'.
Veamos entonces el código a incluir en el Editor de VBA (Alt+F11), dentro de Thisworbook del explorador de proyectos es:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'ruta o path donde queremos guardar el fichero

ruta = "D:\Mis documentos\"

'definimos el nombre del fichero en función a dos celdas de la Hoja1

nombrefile = Sheets(1).Range("A1").Value & "_" & Sheets(1).Range("B1").Value

rutacompleta = ruta + nombrefile

If Not Me.Saved Then

'considero que ha habido alguna modificación desde la última vez que se guardó

pregunta = MsgBox("¿Deseas guardar los cambios realizados en " & rutacompleta, _

vbQuestion + vbYesNoCancel)

Select Case pregunta

Case vbYes

ActiveWorkbook.SaveAs Filename:=rutacompleta, _

FileFormat:=xlOpenXMLWorkbookMacroEnabled

Case vbNo

Me.Saved = True

Case vbCancel

Cancel = True

Exit Sub

End Select

End If

End Sub

El resultado al cerrar nuestro libro de trabajo, si hemos realizado algún cambio en él, es que se abre una ventana diálogo como esta:

haz click en la imagen

cerrándose directamente, sin pedir confirmación de guardado, si no se hubiera producido cambio alguno.

Seguir leyendo...

Obtener los tres mejores tiempos según categorías.

Explicaré hoy una forma de obtener los tres mejores tiempo en una carreara para cada grupo de edad; con ello trataré de contestar la cuestión planteada en un comentario por un lector:

...¿cómo podríamos identificar los 3 primeros, no sólo el máximo? ejemplo... tabla con tiempos de una carrera pero cada corredor es de una categoría, los tres primeros veteranos, cadetes... etc. etc. sin utilizar tablas dinámicas...

Trabajaremos en esta ocasión con una función ya conocida, la función K.ESIMO.MAYOR, pero en un formato matricial (Ctrl+Mayusc+Enter).
Pongamos un ejemplo de una tabla de resultados de una carrera, en el que se intercalan por orden de llegada los diferentes participantes, cada uno correspondiente a una categoría o grupo de edad (alevín, cadete y veterano):

Buscamos completar la siguiente tabla de posiciones para cada 'categoría':

La solución nos la dará combinar o anidar la función K.ESIMO.MAYOR con una función de conteo como es CONTAR.SI, todo ello en ejecutada en forma matricial (Ctrl+Mayusc+Enter):
{=K.ESIMO.MAYOR((Categoria=J$2)*(Tiempo);CONTAR.SI(Categoria;J$2)+1-$I3)}
para trabajar de manera más cómoda, previamente habremos asignado nombres a los diferentes campos de nuestra tabla de tiempos, siendo:
Categoria ='Hoja1'!$A$2:$A$16
Nombre ='Hoja1'!$B$2:$B$16
Tiempo ='Hoja1'!$C$2:$C$16

Seguir leyendo...

Macro para Copiar y Pegar un rango de Excel en un documento de Word.

A través de un comentario realizado en este blog, un lector solicitaba ayuda para poder realizar un copiado y pegado desde un rango de celdas en Excel a un documento de Word:

... Como hago para pasar unas columnas de una Hoja de Excel para Una Hoja de Word.Ejemp. Nombre del Plan de estudio:/Columna de Excel/ Codigo de la Mencion:/Columna de Excel/ Nombre y Apellido:/Columna de Excel/ Cedula de Identidad:/Columna de Excel/ Dia:/Columna de Excel/ Mes;/Columna de Excel/ Año;/Columna de Excel/...

La idea es desarrollar un código de VBA que permite copiar y pegar una tabla desde Excel a Word, además le daremos la propiedad especial de estar vinculados.
Lo más importante antes de comenzar con el desarrollo de nuestra macro es seleccionar una biblioteca de objetos adecuado para nuestro proposito. Después de establecer una referencia a una biblioteca de objetos activando la casilla de verificación situada junto a su nombre, podremos buscar un objeto específico y sus métodos y propiedades en el Examinador de objetos.
Para activar esta referencias deberemos ejecutar el editor de VBA, presionando Alt+F11, y navegando por el menú Herramientas > Referencias seleccionaremos aquellas referencias que sean de utilidad, en nuestro caso Microsoft Word 12.0 Object Library:

La versión 12.0 es válida para trabajar con Excel 2007 y anteriores
Para implantar en nuestros códigos de VBA en Excel estos objetos será necesario incluir las siguientes líneas, que en términos generales para cualquiera de las aplicaciones de Office serían:
Dim otraApp As Otra.Application
Dim otroDoc As Otra.DocTipo
Dim otroObjeto As Otro.ObjetoEspecifico
En concreto para el caso que nos atañe:
Dim WorDApp As Word.Application
Dim WordDoc As Word.Document

Veamos entonces el código a incluir en el Editor de VBA (Alt+F11), dentro de un Módulo del explorador de proyectos es:

Sub Tabla_de_Excel_a_Word()
'Copia el rango de Excel seleccionado en un nuevo documento Word

'copiar rango selecionado
Selection.Copy

'Crea nueva aplicación Word
Dim swMSWord As Word.Application
Set swMSWord = New Word.Application
    With swMSWord
      .Visible = True
      .Activate
    End With
'Abre nuevo documento Word
swMSWord.Documents.Add
'Pegado con vínculo de las celdas Excel
swMSWord.Selection.PasteSpecial link:=True
'Libera el objeto Word
Set swMSWord = Nothing

End Sub

El resultado es que se abre un nuevo documento de Word y en él se pega, con vínculos, el rango seleccionado en nuestra Hoja de cálculo, guardando además todos los formatos existentes en nuestras celdas.

Seguir leyendo...

La función DIA.LAB en Excel.

Ha llegado recientemente a mi mail una consulta que me hizo recordar una de las funciones 'temporales' de poca utilización, hablo de la función =DIA.LAB(fecha; días_laborables; días festivos).
La consulta recibida fue:

...suponiendo que lo resaltado en verde corresponde a la fecha y dia actual aplicando las formulas resaltadas en el mismo color, pretendo que al día actual me reste uno( resultado en amarillo) y que cuando el dia de la semana actual sea lunes me muestre la fecha del viernes anterior...

Recordaremos que existe esta función DIAS.LAB que nos devuelve una Fecha a contar desde la fecha dada, contando hacia adelante o hacia atrás, pero sólo días laborables. Esto es, excluyendo fines de semana y cualquier fecha definida en el tercer argumento.
Por centrar conceptos, entenderemos:

• Fecha: es una fecha que representa la fecha inicial, es decir, desde comenzamos a contar.


• días_laborables: es el número de días laborables (no fines de semana ni festivos definidos en el siguiente argumento) anteriores o posteriores al argumento 'fecha'. Un valor positivo para el argumento días_laborables devuelve una fecha futura, mietras que un número negativo produce una fecha pasada o anterior.


• festivos: es opcional; serán una o varias fechas para excluirse del calendario laboral, como los días festivos nacionales y locales.

Es importante recordar que cuando trabajamos con fechas, en ocasiones para evitar errores, conviene emplear la función FECHA(año; mes; día).

Respondiendo a nuestro lector, y suponiendo nuestra fecha de trabajo situada en la celda B1, escribiríamos en C1:
=SI(DIASEM($B$1;2)=1;DIA.LAB($B$1;-1);B1-1)
observamos como con un condicional SI determinamos qué queremos obtener cuando el día a evaluar sea 'Lunes', lo que conseguimos con la prueba lógica:
DIASEM($B$1;2)=1
si se cumple la condición exigida, nuestro condicional SI ejecutará la función:
DIA.LAB($B$1;-1)
que devuelve un día laborable anterior a la fecha de estudio, ya que el segundo argumento es negativo; con lo que respondemos a la condición dada por el usuario ([...que cuando el dia de la semana actual sea lunes me muestre la fecha del viernes anterior...]); para el resto de días de la semana, simplemente obtendremos un día menos del analizado.

Por ejemplo, para el día de hoy lunes, 07/03/2011 obtendríamos la fecha 04/03/2011 viernes.

Seguir leyendo...

VBA: Condición con macro - Selectionchange.

Un asiduo del blog planteaba la siguiente cuestión:

...tengo una tabla donde existe un campo estado, quisiera que me ayude en lo siguiente: En el campo 'estado' aparezca por ejemplo: Atendido Según lo solicitado si el campo enviado = campo solicitado, Menos de lo solicitado si el campo enviado < campo solicitado, Más de lo solicitado si el campo enviado > campo solicitado, No atendido si el campo enviado no hay ningún valor...

Solicita, mediante un desarrollo en VBA, que para cada registro en su tabla, se evalue qué condición cumple para dos de sus campos a estudio (Enviado - Solicitado) y aparezca en un tercer campo (Estado) su situación.
Veamos un pequeño ejemplo de la estructura de trabajo:

Emplearé algunos términos ya conocidos, como son:
IF...THEN..ELSE
FOR EACH... IN... NEXT
Se trata en definitiva de definir sobre cada una de las celdas de un rango determinado, una serie de condiciones dadas por una función IF.
El código a incluir en el Editor de VBA (Alt+F11), dentro de la Hoja de trabajo es:

Private Sub worksheet_selectionchange(ByVal target As Range)
Dim celda As Object

For Each celda In Range("C2:C5")
 
If Cells(celda.Row, celda.Column - 1).Value _
 = Cells(celda.Row, celda.Column - 2).Value Then _
celda = "Atendido Según lo solicitado"
If Cells(celda.Row, celda.Column - 1).Value _
 < Cells(celda.Row, celda.Column - 2).Value Then _
celda = "Más de lo solicitado"
If Cells(celda.Row, celda.Column - 1).Value _
 > Cells(celda.Row, celda.Column - 2).Value Then _
celda = "Menos de lo solicitado"
If Cells(celda.Row, celda.Column - 2).Value = "" Then _
celda = "No atendido"
    
Next

End Sub

Seguir leyendo...

Formato condicional con fórmula en Excel 2007.

Para contestar la cuestión planteada en un comentario por un usuario deberemos recordar el uso del Formato condicional con fórmula. La pregunta es:

...Utilizo una sencilla tabla para llevar mis cuentas de casa. En la columna de la izquierda, una vez la oreración está realizada, le coloco un número que siempre va incrementándose de 1 en 1. Pues bien lo que me gustaría hacer es que aunque la tabla sea mayor (por debajo de la última fila numerada por mí hay otras filas rellenas pero sin numerar) el valor de la celda de la columna 'saldo' que corresponde a la misma fila que la última numerada por mí aparezca con un formato distinto pues se trata del saldo disponible....

Reproduciré en una tabla el ejemplo que plantea nuestro amigo:

La labor consistirá en encontrar con una fórmula la manera de identificar el registro del campo 'Saldo' correspondiente al último numerado del campo 'Num Oper'.
Para ello, antes de aplicar la fórmula conseguida al Formato condicional con fórmula, añadiremos una serie de columnas auxiliares que permitirán explicar el funcionamiento de nuestra fórmula.
Con la primera de nuestra columnas Auxiliares obtendremos una pista importante, a partir de qué valor del rango a estudio no hay numeración:
=SUMAPRODUCTO($A$2:A2)
fijémonos como la celda que cierra el rango $A$2:A2 es relativa y se mueve de acuerdo a nuestro copiado hacia abajo:

Seguir leyendo...