Condiciones o Pruebas lógicas en funciones de Excel.

Estoy firmemente convencido de que nunca nos acostaremos sin aprender algo nuevo; y que por mucho que pensemos que sabemos sobre algo, siempre descubriremos lo equivacados que estamos... Y eso precisamente me pasó hace algunos días.
Siempre había pensado, y por tanto trabajado sobre esa hipótesis, que las pruebas lógicas o condiciones que aplicaba en las fórmulas o funciones, debían responder a un 'VERDADERO' o 'FALSO', a un 'SI' o un 'NO', a un 'CUMPLE' o 'NO CUMPLE', a un '0' ó a un '1'; sin embargo, con gran sorpresa por mi parte, me demostraron cómo Excel reconoce como CIERTO cualquier valor mayor a cero, y como FALSO cuando es cero... Situación muy útil para encontrar valores o texto dentro de celdas, por ejemplo.
Plantearemos la siguiente situación. Queremos que una fila de una base de datos aparezca en verde cuando una celda concreta contenga un texto específico.
Por poner un ejemplo concreto, queremos que si en la celda B2 aparece el texto 'excelforo' toda la fila 2, cambie su fondo por el color verde.
Para ello, a la hora de aplicar el Formato condicional tipo Fórmula, sólo deberemos insertar la condición:
=HALLAR("ExcelForo";$B2)

Tras aplicar este Formato condicional a la fila 2: $2:$2:

Comprobamos como efectivamente, Excel entiende que ha encontrado el 'texto buscado', y que al ser CIERTO cambia el formato de las celdas de la fila en cuestión:

Seguir leyendo...

Sumar valores en negrita en Excel.

Hace algún tiempo publiqué una entrada en la que se explicaba como, mediante una función personalizada, podíamos sumar valores en negrita para un rango de celdas (ver). En la entrada de hoy veremos cómo realizar la misma operación empleando las macrofunciones de Excel 4.0 o funciones GET. (INDICAR.). Para esta labor utilizaremos la función:
=INDICAR.CELDA(58;celda)
que nos indica el tipo de estilo de la fuente de la celda seleccionada.
Partimos del siguiente rango de celdas, donde tenemos algunos valores en negrita:

Para poder trabajar con estas macrofunciones de Excel4.0 deberemos Crear un nombre definido, que llamaremos rngnegrita:

Si aplicamos esta macrofunción a nuestros elementos del rango A2:A11 obtendríamos

Seguir leyendo...

Cambiar forma de un comentario en Excel 2010.

Hace un par de días me preguntaba un compañero de trabajo si habría alguna forma de cambiar el aspecto del Globo (sandwich) de los comentarios que insertamos en nuestras celdas. Inmediatamente me acordé de lo fácil que era hacerlo con versiones 2003 o anteriores, donde desde la Barra de herramientas de dibujo, desplegabas la opción de Cambiar Autoforma, y podías elegir entre todas las posibilidades que nos ofrecen las Formas...

Sin embargo, me puse a buscar esta opción en Excel 2007 o incluso en Excel 2010 y no dí con él a la primera. Para replicar esta opción en Excel 2007 o Excel 2010 deberemos agregar a nuestra Barra de herramientas de acceso rápido el Comando Cambiar Forma:

Con el comando desplegado ya podremos ejecutar el cambio sobre un globo de un comentario activado:

Por supuesto también podremos modificar el Formato del comentario, en cuanto a fuente, color, borde, etc... desde el menú contextual (botón derecho del ratón):

Seguir leyendo...

Gráfico Columnas en paralelo usando Eje Secundario de Excel.

Veremos hoy cómo engañar a Excel para conseguir un gráfico que muestre dos columnas, una al lado de otra, para series diferentes relacionadas una al Eje principal y la otra al Eje secundario.
El truco surge por la necesidad de visualizar de manera no superpuestas ambas series de datos.
Respondiendo a la consulta de un lector:

...Mi consulta es si se puede (en ese mismo ejemplo) los datos de facturado, como una barra AL COSTADO de las barras de empleados y no encima. He intentado hacerlo con una data similar y no he podido, pero si se puede cuando el gráfico es lineal y de dispersión....

Veamos nuestra tabla de datos de partida, y el resultado final a obtener:

Nuestro trabajo comienza incorporando una nueva columna de datos a la tabla, que llamaremos 'Vacía', y en la que además no incluiremos ningún valor:

Seguir leyendo...

Número de página en una celda de Excel.

Hacía muchos años, desde que usaba la versión de Excel 2000, que no utilizaba las funciones GET., o INDICAR. en español, tanto que las tenía totalmente olvidadas. Me las hizo recordar la cuestión planteada por un lector sobre la forma de incluir en una celda de Excel la numeración de las páginas del Área de impresión configurada. Agradecer a officefull.es la explicación dada a este problema y que me ha permitido recordar viejos tiempos y herramientas.
La cuestión planteada decía:

...quisiera saber si hay alguna forma de obtener el número de página en una celda de excel...

Estas funciones GET. (o macrofunciones como en algunos sitios les llaman), son una reminiscencia, un trozito de historia viva de la versión Excel 4.0; son parte del lenguaje de programación específico de aquella versión, que aún hoy, siguen entre nosotros. Estas funciones son algo especiales, ya que no pueden ser utilizadas directamente en nuestras hojas de cálculo (por su particularidad de ser funciones de VBA), pero con las que si podremos interactuar a través de la definición de nombres.
Por concretar y responder al lector, se trata de asignar mediante funciones la numeración de las páginas configuradas dentro del área de impresión:

Como se puede observar, en cada página aparece en diferentes celdas, la numeración (según la ordenación indicada en la Configuración de página - abajo>derecha ó derecha>abajo) correspondiente.

Nuestro trabajo empieza por comentar cuales serán las funciones INDICAR. que usaremos en este ejercicio:
INDICAR.DOCUMENTO: que asignándole diferentes valores aportará diferente información sobre el documento; por ejemplo:

• =INDICAR.DOCUMENTO(50;Hoja) nos indica el número total de páginas dentro del Área de impresión configurada de la Hoja definida como segundo argumento.
• =INDICAR.DOCUMENTO(64;Hoja) nos indica los números de fila donde están los saltos de página horizontales.
• =INDICAR.DOCUMENTO(65;Hoja) los números de columna donde están los saltos de página verticales.
• =INDICAR.DOCUMENTO(61;Hoja) el sentido de la impresión de las páginas. Si es 1 el sentido es Abajo > Derecha, y si es 2 Derecha > Abajo.

INDICAR.CELDA: de manera similar aporta información sobre la celda indicada. Emplearemos sólo:

• =INDICAR.CELDA(32;celda) que nos dirá cuál es el nombre de la hoja donde se encuentra dicha celda.

Con estos aspectos aclarados, procederemos a Asignar nombres a unas cuantas macrofunciones (o funciones de Excel 4.0):
Área_de_impresión =pagcelda!$A$1:$I$15
AreaImpr =LARGO(INDICAR.DOCUMENTO(81;Hoja))=0
FinalXdeY ="Pagina "&PagN&" de "&PagsN+0*AHORA()
Hoja =EXTRAE(INDICAR.CELDA(32;!$A$1);ENCONTRAR("]";INDICAR.CELDA(32;!$A$1))+1;31)
numCols =CONTAR(SaltosVert)
numFils =CONTAR(SaltosHor)
NumSaltosHor =SI(ESERROR(COINCIDIR(FILA();SaltosHor));1;1+COINCIDIR(FILA();SaltosHor))
NumSaltosVert =SI(ESERROR(COINCIDIR(COLUMNA();SaltosVert));1;1+COINCIDIR(COLUMNA();SaltosVert))
PagN =SI(SentidoImpr=1;(NumSaltosVert-1)*(numFils+AreaImpr)+NumSaltosHor;(NumSaltosHor-1)*(numCols+AreaImpr)+NumSaltosVert)
PagsN =INDICAR.DOCUMENTO(50;Hoja)
SaltosHor =INDICAR.DOCUMENTO(64;Hoja)
SaltosVert =INDICAR.DOCUMENTO(65;Hoja)
SentidoImpr =INDICAR.DOCUMENTO(61;Hoja)

Seguir leyendo...

Modificar un rango de una hoja protegida de Excel.

Vamos a repasar hoy una manera de trabajar sobre hojas de cálculo de Excel protegidas, en concreto veremos cómo se puede habilitar la opción de trabajar sobre unos rangos concretos predefinidos sobre una hoja protegida; me refiero a la opción de Permitir que los usuarios modifiquen rangos.
Supongamos tenemos un pequeño informe en nuestra hoja de trabajo, que no deseamos sea modificada por cualquier usuario, esto es, queremos que sea visible por todo el mundo (compañeros y responsables), pero sí sea accesible mediante contraseña un área o rango concreto dentro de la hoja (sólo una parte de la hoja, NO toda!!). Veamos nuestra estructura en la imagen:

El tema es habilitar, mediante una contraseña, el acceso al rango B2:D3; para ello accederemos a la Ficha > Revisar > Cambios > Permitir que los usuarios modifiquen rangos, con lo que se desplegará la siguiente ventana diálogo:

Presionamos Nuevo para definir el rango al que deseemos permitir el acceso (en nuestro ejemplo B2:D3:

Seguir leyendo...

Libro de macros personal en Excel (Personal.xlsb).

Estamos muy acostumbrados a trabajar con macros o procedimientos Sub y Function de todo tipo en nuestros libros de trabajo de Excel, y en ocasiones determinadas macros las necesitamos recurrentemente en todos nuestros libros; es en estas ocasiones cuando en lugar de grabar estas macros en nuestro libro lo haremos en el Libro de macros personal (Personal.xlsb).
Debemos recordar que de forma predeterminada, cuando se crea una macro en Excel, la macro sólo funciona en el libro que lo contiene. Esto es lo normal y correcto siempre y cuando no sea necesario utilizar la macro en otros libros diferentes. Sólo las macros que guardamos en el Libro de macros personal estarán disponibles en cualquier libro cada vez que inicie Excel en el mismo equipo.
Al hilo de la cuestión de un comentario explicaré cómo y qué pasos tenemos que realizar para crear nuestro Libro de macros personal, y cómo Guardar macros en él.

...como hago para que la funcion definida se habra desde cualquier libro?...

Para tener siempre disponibles nuestras macros en Excel, independientemente del libro de trabajo que tengamos abierto, siempre en nuestro PC (eso sí), deberemos trabajar sobre un libro de trabajo 'especial': PERSONAL.XLS para versiones de Excel anteriores a 2007, y PERSONAL.XLSB para Excel 2007 ó 2010.
La siguiente cuestión a plantear es ¿dónde se guarda este libro de macros personal?. Localizarlo es sencillo, ya que podemos obtener la ruta de una manera muy sencilla.
Primero abriremos nuestro Editor de VBA, habilitando la Ventana de inmediato (Ctrl+G), y dentro de esta, escribiremos el siguiente código:
?Application.StartupPath ó print Application.StartupPath
al ejecutar esta instrucción veremos la ruta en cuestión, que dependerá de la versión de Windows con la que trabajemos; en mi caso, sigo trabajando con WindowsXP, asi que será:
C:\Documents and Settings\NombreUsuario\Datos de programa\Microsoft\Excel\XLSTART
para Windows7 o Vista la ruta sería:
C:\Users\NombreUsuario\AppData\Roaming\Microsoft\Excel\XLSTART.

Mencionar que esta carpeta tiene el atributo de oculto, por lo que si quisieramos visualizar su contenido, la manera más sencilla sería copiar la ruta obtenida, tras modificar la característica en el Explrador de Windows Herramientas > Opciones de Carpeta > Ver > Mostrar todos los archivo y carpetas ocultas:
C:\Documents and Settings\NombreUsuario\Datos de programa\Microsoft\Excel\XLSTART
en el explorador de Windows, lo que nos abriría la carpeta directamente, pudiendo acceder al fichero en cuestión: PERSONAL.XLSB.

Para crear por primera vez este Libro de macros personal tendremos que usar el Asistente de grabación de macros y grabar cualquier proceso de prueba, bastaría con Grabar y Detener, nosotros es lo que haremos. Asi que, por ejemplo, desde la Ficha Programador > Código > Grabar Macro:

Seguir leyendo...

VBA: objeto Collection para lograr listar valores únicos.

En una entrada anterior explicaba cómo poder obtener un listado de valores únicos desde un rango de celdas en Excel. Lo que veremos en el post de hoy es cómo conseguir lo mismo con una macro, mediante el uso de un objeto Collection.
Debemos aclarar antes de empezar qué es una Colección dentro del VBA, la definición es sencilla, ya que sólo es un conjunto ordenado de elementos a los que se puede hacer referencia como una unidad; pudiendo trabajar sobre ese conjunto igual que ,por ejemplo, con una Array (Matriz), recorriendo sus diferentes elementos con bucles tipo FOR...NEXT o cualquier otro.
Para comprobar la eficacia de estas Collection, trabajaremos con la misma tabla de datos que en la entrada a la que nos referíamos al comienzo de este post. Recordemos que hemos asignado un nombre a nuestro rango de estudio:
Equipo =Hoja1!$B$2:$B$20

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

Sub elementosunicos()

Dim celda As Object
Dim i As Integer

'generamos la coleccion
Set unicos = New Collection
'loop en todas las celdas y agregarlas a la coleccion
For Each celda In Range("equipo")
    'cuando encuentre un item repetido, daría un error
    'que salvamos con la instrucción On Error Resume Next
    On Error Resume Next
    'por tanto, nuestra coleccion solo agrega elementos no repetidos
    'objeto.Add item, key, before, after
    'ocurre un error si una key especificada duplica la key de un miembro existente de la colección
    unicos.Add celda.Value, CStr(celda.Value)
    On Error GoTo 0
Next celda

'escribir los datos unicos en la Hoja de cálculo
For i = 1 To unicos.Count
    Sheets(1).Range("E2").Offset(i - 1, 0).Value = unicos(i)
Next i

End Sub

Si asignamos nuestra macro a un botón (control de formulario) en nuestra hoja de cálculo de Excel, al ejecutarla conseguiremos nos lleve los registros únicos, sin repetir, a partir de la celda E2 hacia abajo.

Seguir leyendo...