Ejecutar BUSCAR OBJETIVO en Excel 2007.

Al hilo de la entrada anterior sobre las teclas de acceso, voy a remarcar un par de carencias con la que me he encontrado en Excel 2007, y que se pueden suplir de una manera muy simple empleando teclas de acceso de la versión Excel 2003.
Por ejemplo, la herramientas de 'Formularios' de datos; seguro que la echáis en falta, y es que este comando ha desaparecido de los menús o fichas, aunque sigue existiendo, pero si lo queremos tener accesible deberemos mostrarlo en la Barra de herramientas de acceso rápido de nuestro Excel 2007, o bien, ejecutarlo con el teclado de acceso de Excel 2003, es decir, pulsando ALT+t+f.
Otro ejemplo que, a mi me desespera, es encontrar la herramienta 'Busqueda objetivo' en Excel 2007; todos sabemos que está en la 'Ficha Datos'>'Grupo opciones - Herramientas de datos'>'AnálisiYsi'>'Busqueda objetivo

haz click en la imagen

pero como siempre la 'pierdo' y me cuesta encontrarlo (debería estar junto a Solver, como siempre!!!) he acabado ejecutándolo con el teclado de acceso de 2003, es decir, ALT+h+u.
Ya comentamos la combinación para abrir el asistente de tablas dinámicas de Excel 2003, ALT+t+b.
Y un largo etcétera...

Seguir leyendo...

Teclas de acceso en Excel 2007: ALT+...

Quizá al usar la versión Excel 2007 algunos de vosotros hayáis echado de menos una utilidad de la que disponíamos en versiones anteriores. Las teclas de acceso a los menús mediante combinaciones ALT+(letra).
En versiones previas de Excel teníamos para cada menú, o elemento que se desplegaba dentro de éstos, una letra subrayada que nos daba acceso a ese contenido, lo cual facilitaba la ejecución de distintas utilidades y herramientas con una simple combinación de teclas; por ejemplo, en Excel 2003, para abrir y trabajar con un Formato condicional, navegábamos hasta el menú Formato y después hasta Formato condicional, o también, ALT+F y una vez desplegado los elementos de Formato, pulsabamos la 'm'

Pero en Excel 2007 no disponemos de estas letras subrayadas en los menús (o Fichas, que es así como se llaman); entonces, ¿no existe esta funcionalidad de acceso?. Sí, nada más lejos de la realidad, es más, Excel potencia el uso de estas teclas de acceso.
Tan sólo pulsando la tecla ALT nos muestra, asociado a cada Ficha (o menú) y a todos los comandos que tengamos extraidos en la Barra de herramientas de acceso rápido, una 'etiqueta' (o identificador), el cual nos llevará a los Grupos de opciones de la Ficha elegida, mostrando para cada 'Comando' su etiqueta o identificador.

Para volver al nivel principal, es decir, el de las Fichas y Barra de herramientas, deberemos pulsar ESC. También es posible desplazarse entre los diferentes elementos, dependiendo de qué zona tengamos activa, bien con el cursor (las flechas de desplazamiento) o bien con el Tabulador, i.e., con las teclas TAB y MAY+TAB.
Si te resulta demasiado complejo todo esto, no desesperes, podemos seguir usando las mismas combinaciones de teclas de acceso de Excel 2003!!!, eso si, las tendrás que recordar y aprender de memoria, por que en ninguna parte de Excel 2007 te las mostrará... siempre hay que pagar un precio.
Esto significa que si quiero ejecutar el Formato condicional en Excel 2007, tengo la posibilidad de ejecutarlo también con la combinación ALT+F+M; por supuesto siempre me quedará el ratón para moverme. Esta alternativa ha quedado habilitado para la mayoría de los casos.
Recuerda, entonces, que en Excel 2007 podemos seguir empleando las combinaciones de teclas de acceso que existían en Excel 2003!!!.

Seguir leyendo...

Función CONTAR.SI.CONJUNTO en Excel 2007.

Solicitaba ayuda un lector sobre la manera de CONTAR registros que cumplieran dos condiciones dadas:

...deseo realizar un cuadro donde en una columna escriba los símbolos P que significa promovido, R repite, RR requiere recuperación, etc. como puedo hacer para no contar. además debo contar segun sexo, es decir cuantos hombres y mujeres son promovidos, etc...

Planteando la siguiente tabla de datos:

Para conocer cuantos individuos tienen un determinado 'Sexo' además de tener un 'Concepto' determinado propondría tres posibles soluciones:

• Usando la función CONTAR.SI.CONJUNTO (sólo para versiones Excel 2007).
  Esta función aplica criterios a las celdas en varios rangos y cuenta cuántas veces se cumplen dichos criterios
  =CONTAR.SI.CONJUNTO(rango_criterio1; criterio1; rango_criterio2; criterio2;...)
  siendo para nuestro ejemplo
  rango_criterio2 = rango de la Tabla donde estén definidos los 'Conceptos'
  criterio2 = lo determinaría el 'Concepto' a analizar
  rango_criterio2 = rango de la Tabla donde esté definido el 'Sexo' de cada individuo
  criterio2 = lo determinaría el 'Sexo' a analizar
  

  
  haz click en la imagen

  
  Vemos que la función nos ha contado cuantos registros cumplen al tiempo un Concepto y un Sexo definido.


• Usando Tablas dinámicas. Construimos una Tabla dinámica sobre nuestra Tabla de datos y le damos la siguiente estructura de áreas y configuraciones de campo.
  

  



• Empleando una SUMA CONDICIONAL matricial, escribimos la siguiente función:
  {=SUMA(SI($A$2:$A$11=B49;SI($C$2:$C$11=A49;1;0)))}
  

  

  
  conseguimos con esta función matricial que nos sume 1 cada vez que el rango de Concepto sea un valor definido y además el de Sexo coincida con uno dado.

Aunque todas las opciones son válidas, claramente la Tabla dinámica sería la mejor elección, ya que nos actualizará los elementos según aparezcan; mientras con las dos opciones restantes tendríamos que configurar las posibles combinaciones entre ambos campos a estudio de manera manual.

Seguir leyendo...

Modificar vínculos en Excel.

Cuantas veces hemos necesitado cambiar la dirección de origen de nuestros vínculos en nuestras hojas de cálculo, por que o bien, conscientemente, tenemos necesidad de dirigirlos a otro fichero, o bien algún compañero despistado ha cambiado de lugar en el servidor nuestro fichero base. La solución a esta situación pasaría por Modificar vínculos empleando la herramienta de la que dispone Excel; que para la versión Excel 2003 la encontramos en Menú Edición > Vínculos

y para la versión Excel 2007 desde el Menú Datos > Conexiones > Editar vínculos

En cualquiera de los dos casos la ventana diálogo que nos abrirá será:

Aclarar que esta opción está sólo disponible en el Libro destino, es decir, donde tenemos las referencias externas a otro fichero o libro.
Las opciones que podemos ver con esta herramienta son:

• Actualizar valores: con este botón conseguimos la actualización más reciente posible a cualquier cambio en el Libro origen. Es recomendable, por supuesto, tener la información o datos permanentemente actualizada.


• Cambiar origen: Este es el punto de interés para este post. Previa a cualquier actualización deberemos cambiar el origen; desplegando una ventana de explorador, donde localizaremos la nueva ruta del archivo origen; concretando en qué Hoja del libro deseamos dentrar el vínculo. NO PODREMOS CAMBIAR LA CELDA O RANGO!!
  

  



• Abrir origen: si queremos abrir el Libro origen.


• Romper vínculo: cuidado con esta opción!!. Si rompemos el vínculo perderemos la conexión entre ambos Libros de trabajo (origen y destino); obtendremos simplemente un valor. No se podrá recuperar dicho vínculo posteriormente.


• Comprobar estado: Nos comprueba posible errores sobre la conexión...


• Pregunta Inicial: con esta configuraremos la posibilidad que nos daría Excel al abrir el fichero destino con nuestra referencia externa o vínculo.
  

  

Podemos asegurar entonces que esta herramienta es sencilla y muy práctica de usar, y desde luego segura...

Seguir leyendo...

Histograma acumulado con Tablas dinámicas.

Ya vimos en una entrada anterior cómo construir un Histograma utilizando una tabla dinámica; en la entrada de hoy veremos la manera de incorporar a nuestro Histograma nuevos datos, que nos muestren los porcentajes acumulados de las frecuencias obtenidas.
Nuestros datos son:

Para construir nuestro Histograma necesitamos incorporar una nueva columna a nuestra base de datos, en la que incluiremos la siguiente fórmula matricial FRECUENCIA(siendo Edad el nombre asignado al rango de datos B2:B31):
{=FRECUENCIA(Edad;Edad)/CONTAR(Edad)}
obteniendo con ella la proporción de cada valor único del campo 'Edad' sobre el total de elementos.

Construimos nuestra Tabla dinámica incluyendo nuestra nueva columna, agrupando los datos como ya vimos en Histograma utilizando una tabla dinámica, con lo que obtenemos el siguiente resultado:

La configuración del campo Frecuencia acumulada es:

Como se puede comprobar el resultado del Histograma obtenido mediante Tabla dinámica y con la herramienta de Análisis de datos Histograma es exactamente igual.
Ya estamos preparados para construir nuestro gráfico, a semejanza del que genera la herramienta de Análisis de datos > Histograma. Desde la tabla dinámica insertamos un gráfico de columnas, y configuramos la serie de Frecuencia para que nos la muestra en el Eje secundario, cambiando el Tipo de gráfico de esta serie al de 'Línea'.

Seguir leyendo...

Búsqueda de referencias cruzadas en Excel.

Tiempo atrás planteé un ejercicio de cómo emplear la herramienta Suma condicional, en la que ser realizaba una suma de datos referenciado al cumplimiento de varias condiciones; en realidad era una Búsqueda encubierta de datos sobre una tabla.
En esta ocasión nos plantea un lector el siguiente problema:

...Tengo una lista con los pagos a realizar cada mes. En columnas tengo entre otros (y por simplificar) 'empresa' 'banco' y los distintos meses 'ene', 'feb'…,'nov' y 'dic'. Intento tener una formula que me resuelva, lo siguiente: empresa (lista desplegable) banco (lista desplegable) mes (lista desplegable) = resultado...

digamos que tenemos la siguiente estructura de datos:

Para obtener un resultado a esta situación expondremos dos escenarios posibles:

• Los elementos del campo 'Empresa' son únicos, es decir, no se pueden repetir.


• Los elementos del campo 'Empresa' son repetibles

Previo a cualquiera de ambos trabajos definimos los siguientes nombres en cada una de las hojas de trabajo:

abr_09 ='con repetecion'!$G$2:$G$9 ago_09 ='con repetecion'!$K$2:$K$9 Banco ='con repetecion'!$B$2:$B$9 dic_09 ='con repetecion'!$O$2:$O$9 Empresa ='con repetecion'!$A$2:$A$9 ene_09 ='con repetecion'!$D$2:$D$9 feb_09 ='con repetecion'!$E$2:$E$9 jul_09 ='con repetecion'!$J$2:$J$9 jun_09 ='con repetecion'!$I$2:$I$9 mar_09 ='con repetecion'!$F$2:$F$9 may_09 ='con repetecion'!$H$2:$H$9 nov_09 ='con repetecion'!$N$2:$N$9 oct_09 ='con repetecion'!$M$2:$M$9 orden ='con repetecion'!$C$2:$C$9 sep_09 ='con repetecion'!$L$2:$L$9

abr_09 ='sin repetir'!$G$2:$G$9 ago_09 ='sin repetir'!$K$2:$K$9 Banco ='sin repetir'!$B$2:$B$9 dic_09 ='sin repetir'!$O$2:$O$9 Empresa ='sin repetir'!$A$2:$A$9 ene_09 ='sin repetir'!$D$2:$D$9 feb_09 ='sin repetir'!$E$2:$E$9 jul_09 ='sin repetir'!$J$2:$J$9 jun_09 ='sin repetir'!$I$2:$I$9 mar_09 ='sin repetir'!$F$2:$F$9 may_09 ='sin repetir'!$H$2:$H$9 nov_09 ='sin repetir'!$N$2:$N$9 oct_09 ='sin repetir'!$M$2:$M$9 orden ='sin repetir'!$C$2:$C$9 sep_09 ='sin repetir'!$L$2:$L$9

Adicionalmente para semi-automatizar las búsquedas insertaremos tres celdas validadas, cada una de ellas vinculadas a un rango de elementos de nuestra tabla de datos.

Para resolver el primero de los casos, i.e., que los elementos del campo 'Empresa' sean únicos, podremos plantear una 'Suma condicional', obviamente en su forma matricial:
{=SUMA(SI(Empresa=$Q$2;SI(Banco=$R$2;BUSCARH($S$2;$D$1:$O$9;1+COINCIDIR($Q$2;Empresa);0))))}
con esta función SUMA, aprovechándonos de que los cruces entre estas tres variables son únicos, obtenemos el valor buscado. Hemos indicado con los dos primeros SI la coincidencia en el rango 'Empresa' y con el rango 'Banco' de los elementos a buscar, para determinar el últimos criterio de búsqueda he empleado la función BUSCARH
BUSCARH($S$2;$D$1:$O$9;1+COINCIDIR($Q$2;Empresa);0)
en la que buscamos, previa coincidencia de las dos condiciones anteriores, el 'mes' requerido dentro de la matriz $D$1:$O$9 (datos correspondientes exclusivamente a los meses del año), con esta operación localizaré el importe del mes concreto; sólo queda entonces hacerlo coincidir con la fila adecuada, para lo que incluimos como Indicador de fila la función
1+COINCIDIR($Q$2;Empresa);0)
con la que resulta el número de fila que corresponde a la empresa buscada. Fijémonos que encuentra un único valor de la empresa buscada en el rango 'Empresa'.
Por ejemplo, si queremos buscar el importe en euros que corresponda a la Empresa6, con el Banco2 del mes de sep-09, obtendremos:

haz click en la imagen

Para resolver el segundo de los casos, es decir, que los elementos del campo 'Empresa' puedan ser repetidos, podremos plantear una 'Búsqueda condicionada'; se realizará en dos partes:
Una en la que obtengo el valor de orden en la tabla de aquel elemento buscado que cumpla simultaneamente las condiciones de coincidencia de 'Empresa' y 'Banco', mediante la siguiente fórmula matricial:
{=SUMA(SI(Empresa=$Q$2;SI(Banco=$R$2;orden)))}
La siguiente parte emplearé el resultado de orden obtenido con la función anterior, y empleando la función directa INDICE:
=INDICE($A$2:$O$9;R5;COINCIDIR($S$2;$A$1:$O$1;))
con la que busco en el rango de datos $A$2:$O$9 el número de fila obtenido con la anterior fórmula matricial y el número de columna lo obtengo con la función COINCIDIR, igual que hice en el apartado anterior.

haz click en la imagen

Adjunto fichero para su análisis.

Triple busqueda-cr...

Hosted by eSnips

Seguir leyendo...

Referencias relativas en el Asistente para Macros.

En ocasiones cuando utilizamos el 'Asistente para Macros' para Grabar Macros sencillas, que requieran sólo de una secuencia de acciones, nos hemos dado cuenta de la importancia de marcar o no la opción de Usar referencias relativas, y ésto funciona igual en las versiones Excel 2003 y Excel 2007.
Pongamos por ejemplo que deseamos grabar una macro que de color al fondo de la celda que tenga activa en ese momento. Si accedo al Asistente para Macros y Grabo mi macro sin seleccionar 'Usar referencias relativas' el resultado es este:

Sub Color_fondo() ' ' Color_fondo Macro ' Da color amarillo al fondo de la celda C4 ' ' Acceso directo: CTRL+h ' Range("C4").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End Sub

vemos en la programación que hemos obtenido (Alt+F11 editor Visual Basic) que exclusivamente se seleccionará al ejecutar la Macro 'Color_fondo' la celda C4.
Si repetimos la misma operación, pero esta vez, pulsando la opción de 'Usar referencias relativas' vemos el código desarrollado por el Asistente:

Sub Color_fondo_2() ' ' Color_fondo_2 Macro ' Da color verde al fondo de la celda activa ' ' Acceso directo: CTRL+j ' ActiveCell.Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With End Sub

Observamos la diferencia entre un desarrollo y otro, y es que en el caso de haber pulsado 'Referencias relativas' el código VBA generado por el Asistente ha sido ActiveCell.Select, mientras que en el primer caso, sin activar 'Referencias relativas' el código VBA fue Range("C4").Select:

• ActiveCell.Select: selecciona la celda activa en ese momento, y es a esa celda a la que dará color.


• Range("C4").Select: selecciona exclusivamente la celda C4, y por tanto será a la única celda que dará color.

En Excel 2003 el botón de 'Referencia relativa' aparece en la barra de herramientas Detener grabación que se activa cuando estamos grabando una Macro con el Asistente:

En Excel 2007 el botón de 'Referencia relativa' aparece en el menú Programador> Opciones de Código:

Seguir leyendo...

Los ejes secundarios en un Gráfico de Excel.

Supongamos una tabla de datos con distintos campos con valores muy dispares:

Si a partir de esta tabla generaramos un gráfico de columnas, por ejemplo, veríamos el siguiente resultado:

Como se puede observar la diferencia entre las magnitudes de ambos campos es demasiado grande como para que quede adecuadamente reflejada en el gráfico, es decir, los valores de lo facturado (en millones) hace invisible el número de empleados (en unidades o decenas).
Una posible solución a este problema es llevar los datos de una de las series, en el ejemplo el número de empleados, a un Eje secundario. Haciendo click en la serie de datos dentro del gráfico y una vez seleccionada la serie con el botón derecho del ratón, desplegamos el menú contextual concreto:

Elegimos la opción de Dar formato a series de datos... y en la siguiente ventana diálogo seleccionaremos Trazar serie en Eje Secundario

Si, como en el ejemplo, se hace difícil seleccionar la serie en cuestión siempre podremos marcarla yéndonos al complemento de 'Herramientas de gráficos' y en su pestaña de 'Formato', dirigirnos al grupo de opciones de 'Selección de datos', para desplegar el 'elemento del gráfico' deseado. Los pasos siguientes serán los mismos que los explicados en el párrafo anterior.

haz click en la imagen

Obtenemos el resultado, donde vemos que una de las series 'Facturado' se referencia con el 'Eje principal' con su escala correspondiente, y la serie 'Empleados' con el 'Eje secundario', i.e., el opuesto al Eje principal, igualmente con su escala apropiada.

Para hacer más visible los datos del gráfico cambiaremos el Tipo de gráfico de la serie 'Empleados'. Aprovechando que ya tenemos seleccionada la serie, desde el botón derecho del ratón accedemos a la opción de Cambiar Tipo de gráfico de serie y seleccionaremos uno de Línea, tras aceptar tendremos el gráfico final buscado:

Seguir leyendo...

Automatizar los días del mes: función FIN.MES.

Cierto día comentando con un amigo me expuso un problema con el que se encontraba con cierta frecuencia en su trabajo, y es que debía modificar cada mes la serie de días del mes, de acuerdo con el mes del año en que se encontraba, para actualizar ciertos registros de datos asociados a cada día; se quejaba amargamente que lo tenía que cambiar a mano. Improvisé una solución con el tiempo se quedó como definitiva por su sencillez y comodidad.
Combinaremos lo aprendido con la herramienta Validación con la función FIN.MES(fecha_inicial; meses) y , por supuesto, con una función condicional SI.
En primer lugar en alguna celda de nuestra hoja de cálculo configuraremos dos celdas validadas, una que despliegue con la opción Lista los meses del año (de 1 a 12) y otra que nos despliegue algunos años (2008, 2009 y 2010); en ambos casos podemos optar por definirlo dentro de la herramienta de validación en lugar de referenciarlo.

El siguiente paso lógico es definir, en base a estas celdas con Validación de lista, la fecha del mes; para lo que emplearemos la siguiente función:
=FECHA($E$2;$E$1;1)
es decir,
=FECHA(Año;Mes;Día)
con lo que hemos creado, de forma móvil, nuestra fecha, concretamente el día 1 del mes y año deseado.
Será a partir de esta fecha desde donde generaremos el resto de días del mes del mes y año definidos con nuestra validación, sabiendo que en todo caso el máximo número de días de un mes es 31. Arrastraremos y copiaremos la siguiente función anidada en todas las celdas necesarias:
=SI(O(A6+1>FIN.MES($A$5;0);MES(A6+1)<>MES($A$5));"";A6+1)
lo más importante en este caso radica en discrimar todas aquellas fechas que no correspondan al mes de estudio, para lo que hemos dado como prueba lógica una función O
O(A6+1>FIN.MES($A$5;0);MES(A6+1)<>MES($A$5))
si la fecha anterior incrementada en un día es mayor al último día del mes analizado o el mes de la fecha difiere del mes de estudio; entonces aplicaremos un condicional SI
=SI(prueba lógica con O;"";fecha anterior + 1)

Como aclaración diré que la función FIN.MES(fecha; meses) nos devuelve la última fecha del mes solicitado, anterior o posterior a la fecha del número de mes indicado; en nuestro caso, al necesitar la fecha de fin de mes del mismo en que nos encontramos le asignamos un valor 0.
Obtenemos como resultado que los valores de fecha que no correspondan al mes de estudio aparecerán sin valor, que es lo que se pretendía.

Para aportar algo más de valor añadido al informe, en una columna adyacente a las fechas del mes, se incluye el día de la semana de cada fecha, empleando una función ya vista DIASEM
=SI(A7="";"";ELEGIR(DIASEM(A7;2);"Lun";"Mar";"Mie";"Jue";"Vie";"Sab";"Dom"))
para todas las celdas de la columna. Además podemos mediante un formato condicional colorear aquellas fechas que caigan en Sábado o Domingo:

El resultado lo podemos ver en el archivo adjunto.

Fecha FINMES.xls

Hosted by eSnips

Seguir leyendo...

Asistente para Tablas dinámicas versión 2003 en Excel 2007.

Todos hemos generado en algún momento una Tabla dinámica en Excel 2007, y hemos visto que el Asistente difiere un poco del que existía en la versión Excel 2003. Sabemos que accediendo al menú Insertar > grupo Tablas > Tabla dinámica

Seguro que muchos de nosotros echamos de menos aquella opción de analizar Rangos de consolidación múltiples de la que disponíamos con Excel 2003:

No debemos desesperar, lo bueno de las últimas versiones es que normalmente respetan todo lo alcanzado en anteriores; con Excel 2007 ocurre lo mismo, aunque no lo veamos está ahí, sólo tenemos que saber buscar. Para obtener el viejo asistente para tablas dinámicas en versión excel 2003, con Excel 2007, tenemos dos opciones:

• Agregamos un botón en la barra de herramientas de acceso rápido: Botón Office >Opciones excel >Personalizar > Todos los comandos > Agregar 'Asistente para tablas y gráficos dinámicos'
  

  



• Empleando el teclado con método abreviado, pulsando Alt+t+b

En ambos casos nos habilita el asistente de la versión Excel 2003, con lo que ya podremos trabajar con nuestros rangos múltiples.

Seguir leyendo...

Solver un ejemplo de programación lineal en Excel.

Me escribió un usuario solicitando ayuda con un problema de programación lineal:

...Se trata de solucionar un probelma de programación lineal mediante la herramienta solver de excel. Datos: Xij= Producción en el mes i [=1(enero), 2(febrero), 3(marzo), 4(abril)] propia (j =P) o adquirida al proveedor ajeno (j=A). Ii = Unidades almacenadas en el mes i. Min 120(X1P X2P X3P X4P)+125(X1A X2A X3A X4A)+6(I1+I2+I3+I4) sa: X1P+X1A-I1=2100 X2P+X2A+I1-I2=3750 X3P+X4A+I2-I3=6000 X4P+X4A+I3-IA=5000 XiP menor o igual 4000 i=1,2,3,4 XiA menor o igual 1000 i=1,2,3,4

En primer lugar construimos una plantilla donde se recojan la función a minimizar y todas las condiciones:

Se ha construido en la primera parte de la plantilla la función que define el número de unidades por mes, así como su coste asociado, tanto para producciones propias como ajenas; el objetivo a minimizar será precisamente el producto uno a uno de coste mensual por unidades de ese mes:
=SUMAPRODUCTO(B4:M4;B3:M3)
o lo que es lo mismo
=SUMAPRODUCTO(unidades; coste unitario)
La segunda parte del informe sirve para definir las condiciones particulares o restricciones existentes respecto al número de unidades posibles:
X1P+X1A-I1=2100 la producción propia de enero más la ajena de enero minorado por las unidades de almacén del primer mes deben sumar 2.100 unidades de producto.
X2P+X2A+I1-I2=3750 la producción propia de febrero más la ajena de febrero más las unidades de almacén del primer mes minorado por las de febrero deben sumar 3.750 unidades de producto.
X3P+X4A+I2-I3=6000 misma interpretación que las anteriores
X4P+X4A+I3-IA=5000 misma interpretación que las anteriores
XiP menor o igual 4000 i=1,2,3,4 en cada més la producción propia debe ser menor o igual que 4.000 unidades producidas
XiA menor o igual 1000 i=1,2,3,4 en cada més la producción ajena debe ser menor o igual que 1.000 unidades producidas
empleando de nuevo la función
=SUMAPRODUCTO(unidades; restricciones)
podremos determinar, una vez configuremos Solver, todas las restricciones.
Nuestro siguiente paso es, por tanto, diseñar Solver. Desde el menú Herramientas > Solver (para Excel 2003) o Menú Datos > grupo Análisis > Solver (para Excel 2007) accedemos a la siguiente ventana:

Seleccionamos como celda objetivo el resultado de multiplicar 'Unidades' por 'Coste unitario', marcando la opción de valor mínimo, y en la sección de 'Sujeta a las siguientes restricciones' agregamos todas las condiciones exitentes; es decir, cada una de las doce celdas del rango N7:N18, las cuales resumen las condiciones dadas, las comparamos con los valores dados restrictivos del rango O7:O18.
Una vez definidas las doce restricciones en Solver, podremos ejecutar la Resolución:

lo que nos devolverá un resultado en la celda objetivo N4 de 1.916.750 eur, el cual cumple todas las restricciones dadas; podremos analizar algunos detalles en los informes de respuesta, sensibilidad y límites generados.
Adjunto fichero para su análisis.

Ejercicio Solver m...

Hosted by eSnips

Seguir leyendo...

Búsqueda de diferencias en una base de datos en Excel.

Solicita ayuda un lector para localizar las diferencias entre dos bases de datos en Excel 2007:

...tengo dos archivos en excel 2007, en uno tengo miles de datos con los cuales yo siempre trabajo, mas sin embargo cada mes recibo otro archivo excel 2007 con los cambios de algunos cientos o miles de datos que tengo que pasar en una nueva columna del primer archivo. Aqui el punto es que no todos cambian y los que cambian estan saltados, es decir cambia un solo dato de la fila 20 y el siguiente dato que cambia esta en la fila 524 y la siguiente en la 530 etc. osea no hay un orden. Y para pasar dato por dato es muy dificil para mi, pues tengo que hacer copiar el dato a buscar en el primer archivo, buscar en el segundo archivo y ya encontrado copiar los cambios y pegarlos en el primer archivo y asi hasta 1500 veces, me tardo una eternidad.

Partiremos de las siguientes hojas a comparar; observamos que exiten registros nuevos y modificaciones sobre algunos de los exitentes:

Para facilitar el trabajo, en primer lugar copiaremos la Hoja nueva en el mismo Libro donde se encuentre la bases de datos original de trabajo. Observamos cómo existen algunas diferencias, es decir, algunas modificaciones de campos y otros se mantienen sin cambios, además de un posible caos en la ordenación.
El fin de este ejercicio es encontrar, marcar y adjuntar todos esos cambios.
La primera parte es sencilla, y la podemos realizar de dos maneras, una de ellas es dando un formato condicional con fórmula sobre la hoja de cada mes nueva; para ello necesitamos Asignar nombres a todos los campos de nuestras bases de datos (origen y nueva):
Código ='Hoja Base'!$A$2:$A$11
CódigoNuevo ='Nueva mes'!$A$2:$A$11
Concepto ='Hoja Base'!$C$2:$C$11
ConceptoNuevo ='Nueva mes'!$C$2:$C$11
Criterios ='Registros cambio'!$A$1:$D$5
Descripción ='Hoja Base'!$B$2:$B$11
DescripciónNuevo='Nueva mes'!$B$2:$B$11
Precio ='Hoja Base'!$D$2:$D$11
PrecioNuevo ='Nueva mes'!$D$2:$D$11
para, una vez generados los nombres, crearr la siguiente fórmula que agregaremos a cada Formato condicional de la base de datos nueva:
=CONTAR.SI(Código;A2)<>0 que nos cuenta aquellos elementos del campo Código que no existen en la base de datos origen
=CONTAR.SI(Descripción;B2)<>0 que nos cuenta aquellos elementos del campo Descripción que no existen en la base de datos origen
=CONTAR.SI(Concepto;C2)<>0 que nos cuenta aquellos elementos del campo Concepto que no existen en la base de datos origen
=CONTAR.SI(Precio;D2)<>0 que nos cuenta aquellos elementos del campo Precio que no existen en la base de datos origen
Aplicados en el formato condicional con fórmula cada uno de ellos:

Lo que nos marcará con un formato de colores qué elementos de cada campo se encuentran repetidos, y por exclusión cuáles son nuevos, que era lo que queríamos conocer.
Adicionalmente podemos construir, al margen de nuestra base de datos nueva, unas fórmulas por cada campo que nos indique la misma situación de cambio o novedad; con una función similar a la empleada en el formato condicional. Combinando algunas de las funciones ya vistas en entradas anteriores llegamos a:
=SI(ESERROR(COINCIDIR(A2;Código;0));"Cambio!!!";"sin cambio")
en la que preguntamos que si no encuentra ninguna coincidencia del elemento A2 en el rango de datos 'Código' de la base de datos origen, nos lo advierta con el texto 'Cambio!!!', en caso contrario, es decir, que si ha encontrado un elemento igual en el origen nos confirme que 'sin cambio'.

Dependiendo del número de modificaciones o registros nuevos emplearemos una u otra, o quizá ambas al tiempo.
El siguiente paso es extraer, de nuestra base de datos nueva, todos aquellos registros que o son nuevos o han sufrido algún cambio. Emplearemos un Filtro avanzado sobre la base de datos nueva, pero extendiéndola también a esos campos adicionales obtenidos que nos indican si hay cambio o no. En nuestro ejemplo aplicaremos un filtro avanzado sobre el rango A1:I11, desde una hoja nueva de nuestro libro, accedemos al menú Datos > Grupo Ordenar y filtrar > Avanzadas

con lo que obtenemos sólo los registros que presenten algún cambio o los nuevos.

Adjunto fichero

Cambios entre hoja...

Hosted by eSnips

Seguir leyendo...

Deshabilitar controles con VBA en Excel 2003.

Desarrollaremos en esta ocasión, empleando VBA, una macro que Habilite o Deshabilite los controles de Guardar y de Guardar como... en Excel 2003, condicionándolo a que algunas celdas estén completadas.
Esto es lo que nos pedía un lector:

...agradecería mucho me pudieran ayudar para conocer las opciones que tengo para poder condicionar el guardar un archivo en excel. Actualmente tengo una hoja en excel con celdas protegidas, no protegidas y validaciones en las mismas, para que el usuario no pueda modificar formato solo llenar con información, la intención es que no pueda guardar el archivo sin antes completar en su totalidad las celdas que debe llenar.

Supongamos un informe que requiere ser rellenado, en algunos campos, por un usuario final, el cual tiene Protegido la Hoja de trabajo, además de con celdas validadas; pretendemos que el usuario no pueda Guardar el fichero hasta que complete la información requerida. Algo sencillo y cómodo de explicar sería plantear una hoja de liquidación de los gastos de viaje, para lo que se requiere al menos identificar a la persona que ha realizado el viaje y el mes al que corresponden los gastos:

Nuestras celdas obligatorias para rellenar son la celda A1 y la celda A2, mientras ambas no estén completadas nuestra Macro no habilitará las opciones de Guardar o Guardar como...
El conjunto de macros a desarrollar, dentro de la ventana de código de la Hoja de trabajo concreta-Hoja1-, serán entonces:

'creamos una macro que deshabilite los controles de Guardar y Guardar como... Private Sub Deshabilitar() Dim cCtl As CommandBarControl With CommandBars("file") Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro del menú archivo If Not cCtl Is Nothing Then cCtl.Enabled = False Set cCtl = .FindControl(ID:=748) 'ID 748 para Guardar como... dentro del menú archivo If Not cCtl Is Nothing Then cCtl.Enabled = False End With With CommandBars("document") Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro del Documento If Not cCtl Is Nothing Then cCtl.Enabled = False Set cCtl = .FindControl(ID:=748) 'ID 748 para Guardar como.. dentro del Documento If Not cCtl Is Nothing Then cCtl.Enabled = False End With With CommandBars("standard") Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro de la barra de herramientas Standard If Not cCtl Is Nothing Then cCtl.Enabled = False End With Set cCtl = Nothing Application.OnKey "^g", "" 'deshabilita el método abreviado de guardar Ctrl+g End Sub

Esta primera macro nos deshabilitará las opciones de Guardar y Guardar como... de los diferentes lugares en que puede aparecer dentro de Excel, ya que hemos indicado la instrucción Enabled =False en todos ellos.
La siguiente macro será:

'creamos una macro que habilite los controles de Guardar y Guardar como... Private Sub Habilitar() Dim cCtl As CommandBarControl With CommandBars("file") Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro del menú archivo If Not cCtl Is Nothing Then cCtl.Enabled = True Set cCtl = .FindControl(ID:=748) 'ID 748 para Guardar como... dentro del menú archivo If Not cCtl Is Nothing Then cCtl.Enabled = True End With With CommandBars("document") Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro del Documento If Not cCtl Is Nothing Then cCtl.Enabled = True Set cCtl = .FindControl(ID:=748) 'ID 748 para Guardar como.. dentro del Documento If Not cCtl Is Nothing Then cCtl.Enabled = True End With With CommandBars("standard") Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro de la barra de herramientas Standard If Not cCtl Is Nothing Then cCtl.Enabled = True End With Set cCtl = Nothing Application.OnKey "^g" 'habilita el método abreviado de guardar Ctrl+g End Sub

Esta macro nos habilitará las opciones de Guardar y Guardar como... de los diferentes lugares en que puede aparecer dentro de Excel, ya que hemos indicado la instrucción Enabled =True en todos ellos.
Lo último que nos queda por programar es una llamada a las dos anteriores, condicionada a que las celdas A1 y A2 tengan algún valor editado:

'creamos una macro que 'llame' a las dos anteriores siempre que cambie el contenido de las celdas A1 y A2 Private Sub Worksheet_Change(ByVal Target As Range) 'si A1 o A2 no tienen ningún valor introducido If Range("A1").Value = "" Or Range("A2").Value = "" Then 'ejecuta la macro anterior llamada Deshabilitar Deshabilitar Else 'de lo contrario ejecuta la macro Habilitar Habilitar End If End Sub

Probamos su funcionamiento y vemos que, efectivamente, cuando alguno de los valores exigidos no está completado las opciones de Guardar y Guardar como... están deshabilitadas:

Seguir leyendo...