domingo, 29 de abril de 2012

Cambiar el ámbito de un Nombre definido de Local a Global (de Hoja a Libro).

Seguro que muchas veces nos ha ocurrido lo mismo, tener un Nombre definido con un ámbito de aplicación local (de una sóla Hoja de nuestro Libro) y querer editarlo para cambiarlo a ámbito global (a todo el Libro) y no poder hacerlo desde el Adminstrador de nombres; viéndonos obligados, en muchos casos, a borrar el viejo y generar uno exactamente igual, pero sin olvidarnos de aplicar un ámbito a todo el Libro.

Cambiar el ámbito de un Nombre definido de Local a Global (de Hoja a Libro).

Vemos cómo el campo, al editar el nombre es No editable..

Cambiar el ámbito de un Nombre definido de Local a Global (de Hoja a Libro).


En esta entrada aprenderemos un truco, mediante macros, que replican de alguna manera esa forma manual que contabamos al principio. Con la macro que veremos un poco más abajo, buscaremos en cada Hoja del Libro todas los Nombres definidos locales (aplicados a Hojas) y tras 'procesarlos', los borraremos y generaremos otro Nombre, con las mismas características de Nombre y Hacer referencia a, pero con un Ámbito a Libro.
Pero antes, para comprender mejor el siguiente código debemos recordar algo que diferencia un Nombre local de un Nombre global:
Todos los nombres tienen un ámbito, ya sea una hoja de cálculo concreta (ámbito local) o el libro completo (ámbito global). El ámbito de un nombre es, por tanto, la ubicación dentro de la cual se reconoce el Nombre definido.

Por ejemplo, si hemos definido un nombre, como Nombrelocal, y su ámbito es Hoja1, este nombre sólo se reconocerá en Hoja1, pero no en el resto de hojas. Para poder emplear este Nombre local en otra hoja de cálculo, tendremos que precedorlo del nombre de la hoja de cálculo, como sigue:
Hoja1!Nombrelocal.
Por otro lado, si hemos definido un nombre, como NombreLibro, y su ámbito es todo el Libro de trabajo, éste se reconocerá en todas las hojas de cálculo del libro, pero no en otros libros.

Es importante recordar (Excel lo hace) que un nombre siempre debe ser único en su ámbito; sin embargo, es posible utilizar el mismo nombre en diferentes ámbitos. Por ejemplo, podríamos definir un nombre, como MismoNombre, para las Hoja1, Hoja2 y Hoja3 del mismo libro de trabajo. Aunque todos los nombres son iguales, cada uno es único en su ámbito. Incluso es posible definir el mismo nombre, MismoNombre, para el nivel de libro global, aunque una vez más el ámbito es único; aunque en este caso puede producirse un conflicto de nombre.


Lo importante para nosotros de todo lo explicado es que los Nombres con ámbito de Hoja, internamente Excel los nombra como:
Hoja1!Nombrelocal
es decir, nombre de la hoja seguido de la exclamación y el Nombre definido.. como en tantas otras herramientas o utilidades.
Este aspecto es del que nos aprovecharemos para localizar estos Nombres definidos 'locales' y cambiarles el ámbito a Libro.

Abriremos nuestro editor de VBA (Alt+F11) e insertaremos un módulo, en el cual añadiremos el siguiente código:

Sub CambiarAmbitoNombresDefinidos()
Dim HojaTrabajo As Worksheet
Dim NombreDefinido As Name
Dim nameNombreDefinido As String
Dim refersTo As String

'recorremos todas las hojas del Libro.
For Each HojaTrabajo In ThisWorkbook.Worksheets
    'pasamos por todos los Nombres definidos existentes en cada hoja
    For Each NombreDefinido In HojaTrabajo.Names
    'creamos variable con cada Nombre
    nameNombreDefinido = NombreDefinido.Name
        'evaluamos si tiene el símbolo !
        'lo que significa que es un Nombre con ámbilo de Hoja
        If InStr(nameNombreDefinido, "!") > 0 Then
        'si es cierto que tiene !, entonces generamos una variable Nombre
        'pero sin la primera parte del nombre anterior
        nameNombreDefinido = Mid(nameNombreDefinido, InStr(nameNombreDefinido, "!") + 1)
        End If
    'creamos variable con cada 'Se refiere a' de cada Nombre
    refersTo = NombreDefinido.refersTo
    'se borra el antigüo Nombre
    NombreDefinido.Delete
    'Creamos una nuevo con el nuevo Nombre referido al mismo rango que antes
    'pero en este cambio el ámbito de aplicación es todo el Libro
    ThisWorkbook.Names.Add nameNombreDefinido, refersTo
    Next
Next
End Sub


Como decíamos esta macro realiza, de alguna manera, lo que habitualmente hacíamos nosotros 'a mano', duplicamos el 'Nombre' y el 'Hacer referencia a', cambiando a Libro el ámbito.
Podemos comprobar si ejecutamos la macro, cómo efecivamente el resultado es el esperado, esto es, convierte el Nombre definido llamado Nombrelocal, cuyo ámbito es la Hoja1, a un Nombre definido llamado Nombrelocal con ámbito todo el Libro.

lunes, 23 de abril de 2012

Gráfico Excel con máximos y mínimos.

Hablaré hoy de cómo personalizar un gráfico con columnas para conseguir ver dos líneas horizontales (una para el valor máximo y otra para el mínimo), pero haciendo llegar dichas líneas hasta los ejes verticales.
Leámos la cuestión planteada:

...¿Cómo es posible con Excel 2007/2010 crear un gráfico de barras con 2 series de datos y que además contenga sendas líneas horizontales que delimiten un valor como límite superior e inferior?; el tema es que me gustaría que estas líneas de máx y mín se representaran desde el eje vertical primario hasta el secundario, y no aparezcan entre las series de datos verticales....

Partiremos de nuestra tabla de datos original con dos series de datos, que representan la facturación de seis empresas para dos ejercicios:

Gráfico Excel con máximos y mínimos.


En primer lugar añadiremos dos Series auxiliares más, una para el valor máximo y otra para el mínimo, lo que conseguiremos mediante las funciones =MAX($B$2:$C$7) para todas las celdas del rango D2:D7 y =MIN($B$2:$C$7) para todas las celdas del rango E2:E7. Con lo que ya estaremos en disposición de generar el siguiente gráfico:

Gráfico Excel con máximos y mínimos.


Como podemos ver a las dos series auxiliares Máximo y Mínimo les hemos cambiado el tipo de gráfico por una de Línea. Después de asignarle etiquetas de datos a las Series principales de facturación y a las auxiliares veríamos el siguiente gráfico, casi definitivo:

jueves, 19 de abril de 2012

Una matricial para un Formato condicional de Excel.

Me planteaba un lector, a través de un correo, la posibilidad de remarcar, mediante el Formato condicional, para un listado de valores, los cuatro mayores importes correspondientes a un elemento particular:
...Quisiera saber si se puede en formato condicional que me marque de color las celdas C2:C31, con respecto a la celda $F$2, ahora tiene el numero cuatro, entonces que me marque de color la suma de los tres elementos como se muestra ahora de color amarillo...

Para entenderlo mejor veamos el listado de nuestra hoja de cálculo. La idea es clara, para la persona llamada 'Andrés' remarcar los cuatro mayores valores.:

Una matricial para un Formato condicional de Excel


El asunto es complicado ya que para configurar nuestro Formato condicional deberíamos incluir una fórmula matricial que defina el condicional, esto es, la fórmula que diga para cada registro evaluado del rango C2:C31 si correponde a la persona 'Andrés' y que además su valor está entre los cuatro más altos para dicha persona.
Al no admitir directamente una función matricial la herramienta Formato condicional, tendremos que recurrir a otra de las funcionalidades potentes de Excel, los Nombres definidos.

Para comenzar a trabajar, y para facilitar el trabajo posterior, Asignaremos nombres a nuestros campos del listado, de la hoja 'FormCond':
personas =FormCond!$A$2:$A$31
valores =FormCond!$C$2:$C$31
y acabaremos por construir una función matricial como un Nombre definido, lo que nos permitirá posteriormente incluirla dentro de nuestro Formato condicional.
En este caso la hemos llamado identidad:
identidad =(personas=$G$3)*valores

Una matricial para un Formato condicional de Excel


Una vez generado esta identidad ya podemos dar nuestro Formato condicional al rango C2:C31; asi que seleccionando dicho rango, desde la Ficha Inicio > Estilos > Formato condicional activamos la ventana diálogo, dentro de la cual elegiremos la opción de 'Fórmula':
=Y($A2=$G$3;$C2>=K.ESIMO.MAYOR(identidad;$F$2))

lunes, 16 de abril de 2012

Más sumas acumuladas en Excel.

Parece que algo muy habitual en nuestras hojas de cálculo de Excl es trabajar, en diferentes formas, con importes a acumular... nuestros problemas aparecen en función de distribuciones concretas en la hoja de trabajo.
Veamos el planteamiento de un lector, que tiene una dificultad añadida a la suma acumulada, y es que los importes a acumular están en columnas alternas:

...quisiera sumar las celdas b6,d6 y f6 , asi sucesivamente, en la celda $p$6 la suma obviamente con relacion a la celda $b$3.
Y que me coloree con formato condicional las celdas de acuerdo a los meses sumados...

Veamos la imagen del informe:

Más sumas acumuladas en Excel.


Antes de explicar nuestras fórmulas, analizaremos la colocación de los importes a sumar en base al mes desplegado (utilizando un Control de número - control de formulario); se ve claramente como alternan por columnas los importes y un porcentaje (que no nos interesa), particularmente los importes a sumar están situados en columnas pares.
Este control desplegará un valor que hemos asociada en la celda C3, valor en nuestro ejemplo que va de 1 a 6, que luego convertiremos, en la celda B3, en FECHA con un sencillo =BUSCARV(C3;$V$5:$W$10;2;0) que buscará en una tabla anexa que relaciona 1 a ene-12, 2 a feb-12, 3 a mar-12, etc
Bajo estas premisas trabajaremos matricialmente en primer lugar, en P6 escribiremos:
{=SUMA(SI(B5:M5<=$B$3;($B$6:$M$6)*ES.PAR(COLUMNA($B$6:$M$6))))}
que ejecutada matricialmente nos proporciona la suma acumulada de Ventas hasta la fecha desplegada, el valor de la celda B3. Esta matricial sólo sumará aquellos valores de B6:M6 colocados en una columna PAR, siempre que la fecha correspondiente de B5:M5 sea menor o igual a la desplegada en B3.
Vemos en la imagen el resultado obtenido desplegando otra fecha:

miércoles, 11 de abril de 2012

VBA: Macro para un formato condicional en Excel.

Estamos muy acostumbrados a trabajar con las últimas versiones de Excel (2007 o 2010) donde podemos asignar formatos condicionales con más de tres condiciones sobre una celda; sin embargo, no hace mucho esta posibilidad no existía en el estándar, y debíamos cosntruirnos un código o macro que ejecutara y diera los formatos deseados.
Aún hoy siguen quedando usuarios rezagados de versiones un poco 'viejas', y que requieren dar un Formato condicional con más de esas tres condiciones límites. Para dar respuesta a un lector del blog daré un código sencillo pero efectivo que replica dicho formato:

...Por razones profesionales no puedo usar las versiones superiores a la 2000, y a veces me encuentro un poco limitado con las funciones nuevas de los programas más modernos.
La pregunta es: ¿Puedo reducir a UNA sola condición, a través de fórmula, el formato de celda, en función del texto contenido?
Te pongo el ejemplo:
En una sola celda puedo necesitar meter distinto texto: GUARDIA, LIBRA, PERMISO, CONSULTA. Según el contenido le doy formato diferente a la celda. Pero al tener 4 tipos de texto, y el formato condicional disponer de solo 3 condiciones, ... ya no puedo hacerlo de esta forma.
¿Sería posible con una fórmula aplicar un formato diferente según el texto?...

La idea es clara, buscaremos un código que asigne un color de fondo a la celda donde se encuentre algunos de los cuatro textos indicados (GUARDIA, LIBRA, PERMISO y CONSULTA)
Veamos entonces nuestro código completo, que incluiremos en un módulo del Editor de VBA:

Sub formato()
Dim celda As Object
Dim rng As Range
'con INPUTBOX seleccionamos un rango de celdas
Set rng = Application.InputBox("en que rango quieres aplicar el formato??", Type:=8)

'recorremos cada celda del rango seleccionado
For Each celda In rng
valor = celda.Value
    'asignamos colores según el valor de la celda
    If valor = "LIBRA" Then
    celda.Interior.Color = 65535
    ElseIf valor = "PERMISO" Then
    celda.Interior.Color = 15773696
    ElseIf valor = "GUARDIA" Then
    celda.Interior.Color = 255
    ElseIf valor = "CONSULTA" Then
    celda.Interior.Color = 5296274
    End If
Next celda
End Sub



El funcionamiento de la macro 'formato' es sencillo, indicar sobre que rango de celdas queremos aplicar los formatos de colores, y a continuación si detecta el valor indicado cambiará el color de fondo de la celda.
Podemos ver en la imagen el resultado final para el rango A1:A4, donde hemos desplegado los cuatro textos; y como al presionar un Botón al que previamente hemos asignado nuestra macro 'formato', preguntará el rango de celdas y luego asignará los colores:

VBA: Macro para un formato condicional  en Excel.


martes, 10 de abril de 2012

Obtener Dígito Control del codigo EAN 13 con Excel.

Un asíduo del blog me planteaba la siguiente cuestión, que me pareció interesante, por lo que puede aportar Excel a nuestra vida o 'problemas' corrientes.
En concreto se plantea la duda de cómo conseguir un Dígito Control sobre el código EAN-13 que se emplea para codificar los diferentes productos que nos encontramos en cualquier tienda (el de los códigos de barras):

...necesito calcular el digito verificador para un codigo de barras EAN-13.
La idea es poner un codigo en, por ejmplo, la celda A1 (779505123456) y que en B1 me ponga el mismo codigo con el digito verificador...

Tenemos que saber que estos códigos son otorgados por organismos autorizados, y que no es algo que podamos inventarnos, toda la numeración tiene un sentido (podemos leer algo al respecto en wikipedia). Básicamente se explica que el European Article Number 13 es una numeración de trece dígitos que se compone de:
  • Código del país en donde radica la empresa, compuesto por 3 digitos.
  • Código de empresa. Es un número compuesto por 4 o 5 dígitos, que identifica al propietario de la marca.
  • Código de producto. Completa los 12 primeros dígitos.
  • Dígito de control. (que es el que vamos a aprender a calcular en base a las indicaciones del organismo europeo).
Para comprobar el Dígito de control numeraremos los dígitos de derecha a izquierda. A continuación se suman los dígitos de las posiciones impares, el resultado se multiplica por 3, y se le suman los dígitos de las posiciones pares. Se busca la decena inmediatamente superior y se le resta el resultado obtenido. El resultado final es el Dígito de control de la codifación EAN-13. Si el resultado es múltiplo de 10 el dígito de control será 0.

Visto las normas impuestas para determinar ese Dígito Control, procederemos a desgranar las partes de nuestra fórmula:
=REDONDEAR.MAS(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.PAR(COLUMNA(2:2))))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.IMPAR(COLUMNA(2:2))*1)*1);-1)-(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.PAR(COLUMNA(2:2))))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.IMPAR(COLUMNA(2:2))*1)*1))
ó
=10-RESIDUO(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(RESIDUO(COLUMNA(2:2);2)=0))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(RESIDUO(COLUMNA(2:2);2))*1);10)
ó una mezcla de las dos anteriores
=10-RESIDUO(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.PAR(COLUMNA(2:2))))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.IMPAR(COLUMNA(2:2)))*1);10)

Una primera línea para ver el efecto matricial de la función {=COLUMNA(2:2)}; con la que conseguimos una ordenación:

Obtener Dígito Control del codigo EAN 13 con Excel.


A continuación explicaré como conseguir bien el desglose por dígito de manera inversa (de derecha a izquierda) o bien de manera directa (de izquierda a derecha) sobre el código a estudiar. Realmente da igual realizar esta ordenación, siempre que tengamos claro que, en sentido directo, los dígitos en posición impar, se encuentran en posición par en sentido inverso; lo caul es importante a la hora de multiplicar unos u otros por 1 ó por 3.
La fórmula matricial para sacar los dígitos numerados de derecha a izquierda es:
{=1*EXTRAE($A2;LARGO($A$2)-COLUMNA(2:2)+1;1)}
y en sentido de izquierda a dercha es:
{=EXTRAE($A2;COLUMNA(2:2);1)}
vemos que anidamos el resultado de COLUMNA(2:2) para conseguir la posición inicial de EXTRAER

lunes, 9 de abril de 2012

Cuadrar suma de sumandos redondeados en Excel.

En ocasiones nuestras operaciones en Excel nos devuelven resultados inesperados, por ejemplo, algo muy típico es que al realizar un reparto proporcional sobre una cantidad, si aplicamos redondeo de dos decimales, al sumar ese reparto, el sumatorio obtenido no coincide con la cantidad repartida, siempre hay una pequeña diferencia.
Veamos la cuestión planteada:

...En un reparto proporcional realizado en excel, en el cual se incluyen y se redondea a dos decimales, el resultado de sumar con la formula =suma( ), no es el mismo
Ejemplo repartir 42,11 €
89,95 37,88 esta saldría de =D13*E7/100 de donde D13 seria 42,11 y E7 seria 85,95
2,89 1,22
2,89 1,22
1,16 0,49
0,57 0,24
2,54 1,07
Total 42,11 Estos 42,11 serian autosuma.
Pues si lo hacemos con una calculadora, el resultado seria 42,12 que el es real.
¿Como puedo hacer para que el resultado sea el real, y no tener que comprobarlo con la calculadora?...

Esto es normal, es un tema matemático (no de Excel); fijémosnos en el planteamiento, en el que se comenta que se realiza un redondeo a dos decimales cada operación, por lo que realmente estamos perdiendo precisión en el cálculo final acumulado, ya que los redondeos individuales nos llevan en algunos casos, como en el planteado a un error de +/- 0.01.
La solución más sencilla sería NO redondear las operaciones, así nos aseguraríamos que el reparto coincide con la cantidad a repartir. Pero si por algún motivo se necesitara este redondeo, habría que 'forzar' el cuadre, añadiendo a uno de los elementos esa diferencia... que es lo que vamos a explicar en este post.

Veamos en una primera imagen como aplicando una fórmula sin REDONDEAR conseguimos el reparto perfecto:
=$B$1*A2/100

Cuadrar suma de sumandos redondeados en Excel.


Como podemos observar, la suma resultante cuadra perfectamente con la cantidad de inicio, lógicamente al no aplicar redondeo en nuestra operación no tendremos resultados a cuadrar. Sin embargo, si aplicamos un REDONDEAR con dos decimales a nuestra fórmula el resultado varía:
=REDONDEAR($B$1*A2/100;2)

Cuadrar suma de sumandos redondeados en Excel.


Y es aquí cuando empieza nuestra tarea, ¿cómo conseguimos redondear nuestra fórmula que la suma acumulada de esos redondeos cuadre con la cantidad inicial a repartir?. Para ello construiremos una función que fuerce el cuadre por diferencias en el último elemento. Ejecutaremos la siguiente fórmula matricialmente (Ctrl+Mayusc+Enter) para cada elemento del reparto:
{=SI(SUMA(REDONDEAR($C$2:C2;2))<$B$1;REDONDEAR(B2;2);REDONDEAR(B2;2)-(SUMA(REDONDEAR($C$2:C2;2))-$B$1))}
Como podemos ver en la siguiente imagen: