Llamar a una foto externa a partir del valor de una celda

En hojas que manejan bases de datos de personal o de inventarios se requiere incluir una foto con la descripción o la información. En esta entrada veremos como insertarla. El formato depende de ti.

1. Se crea una carpeta con el nombre “fotos” dentro de la carpeta donde crearemos esta hoja de Excel. En ella guardamos las imágenes, de preferencia con clave

2. Hay que crear una hoja de datos. Para este ejemplo usaré la hoja 2 de un libro nuevo

3. En la columna A escribo mi lista de nombres y en la B los nombres de los archivos de las fotos

4. A los datos de la columna A (A2:A4) les cambio su denominación a “nombres” para mi lista de validación

5. Al rango de A2:B4 le cambio su denominación a “datos” para la fórmula de búsqueda

6. En la hoja 1 se pone en A2 el título “Nombre”, B2 el título “Clave” y a C2 el título “Foto”

7. En A3 creo una lista desplegable. Para ello voy a Datos – Validación de datos y cambio la configuración a Lista y llamo a “nombres”

8. En B3 se pone la siguiente fórmula: Nota. Cambiar la fórmula por consultav si no funciona el buscarv por las versiones

=buscarv(A2,datos,2,FALSO)

9. Entrar a Programador e introducir este macro. Es importante señalar que este tipo Worksheet_Change reconoce cualquier cambio que ocurra en la hoja. Hay que grabarlo en la hoja1 (donde se utilizará) y se maneja como Private

Private Sub Worksheet_Change(ByVal Target As Range)

‘ Trae una imagen de acuerdo al valor de una celda

On Error Resume Next ‘ Si ocurre un error sale del macro

If Target.Cells = Range(«A3») Then ‘ Si hay cambio en el valor que se encuentra en A3

foto = Range(«B3»).Value ‘ Trae el nombre del archivo que está en B3

Application.ScreenUpdating = False ‘No actualiza la pantalla

ruta = ActiveWorkbook.Path & «\fotos\» & foto ‘ Carpeta donde se encuentran las fotos (es donde está guardada la hoja y dentro la carpeta fotos)

Me.Shapes(«fotoanterior»).Delete ‘ Borra la foto anterior

Range(«C3»).Select ‘ Celda donde se colocará la fotografía

Me.Pictures.Insert(ruta).Select ‘ Inserta la imagen

With Selection

.Name = «fotoanterior» ‘ Le da un nombre para borrarlo si se vuelve a cambiar

.Placement = xlMoveAndSize ‘ Lugar y posición

.PrintObject = True ‘ Impresión

.ShapeRange.LockAspectRatio = msoFalse ‘ Modificación proporcional de la imagen

.ShapeRange.Height = 85# ‘ Alto en pixeles

.ShapeRange.Width = 65# ‘ Ancho en pixeles

.ShapeRange.Rotation = 0# ‘ Angulo de rotación

End With

Application.ScreenUpdating = True ‘Actualiza la pantalla

End If

End Sub

10. Guardar la hoja habilitada para macros

11. Cada vez que se cambie el nombre la fotografía que corresponde se coloca automáticamente en C3

Cómo esconder los ceros

Los ceros, gracias a los mayas, son un número y por tanto tienen valor. A diferencia de una celda vacía o nula, matemáticamente el cero tiene un significado, sobretodo para operaciones como promedios o cuentas.

Sin embargo, muchas veces tenemos hojas llenas de datos y las celdas con ceros nos distraen o quedan perdidas.

Existen varias formas de ocultar este valor:

Formato numérico

Se puede utilizar un formato para cambiar los valores. Se puede aplicar a toda la hoja o sólo a algunas celdas.

Seleccione las celdas que contienen el valor cero que se quiere ocultar
Con un clic derecho se llama al menú y se selecciona Formato de celdas
Aparece un cuadro de diálogo. Hay que dar un clic en la pestaña de número
Dentro de las opciones se escoge Contabilidad. Se cambia el valor del cero por un guion
Clic en Aceptar

También se puede hacer en forma personalizada

Realice los pasos 1 al 3 de la sección anterior
Seleccione dentro de las opciones Personalizada
Escriba el formato 0;-0;;@ en el campo Tipo
Se da un clic en Aceptar

Formato condicional

Se ocultan los ceros con colores. En el momento en que ocurre la excepción entra en funcionamiento el formato condicional

Se seleccionan las celdas donde se encuentran los ceros
Se da un clic en Inicio – grupo Estilos – Formato condicional
En el menú que aparece se elige Nueva regla
En el cuadro de diálogo, en el panel superior se escoge Aplicar formato solamente a las celdas que contengan
En la segunda lista desplegable se busca Igual a
Se pone 0 en el tercer control
Clic en el botón de Formato
Se cambia el color de la fuente por Blanco (o el color del fondo de la hoja)
Clic en Aceptar
Clic en Aceptar

Macro

Se escribe esta función y se ocultan los ceros de la hoja activa

Sub OcultarCeros()

With ActiveWindow

.DisplayZeros = Not .DisplayZeros

End With

End Sub

Macro para mostrar las hojas ocultas

La entrada anterior vimos la forma de ocultar y mostrar hojas. Sin embargo, a la hora de mostrarlas vimos que era un proceso más largo, porque hay que indicar cual queremos “aparecer”.

Una manera rápida de mostrar todas las hojas ocultas es con un sencillo macro:

Sub MuestraHojasOcultas()

‘ Muestra las hojas del libro que están ocultas

intNumeroHojas = ActiveWorkbook.Worksheets.Count

‘Guarda el número de hojas en el libro

i = 1

‘Contador

While i <= intNumeroHojas

‘Desde la primera hasta el número de hojas en el libro

If Not Worksheets(i).Visible Then

‘Si la hoja no está visible

Worksheets(i).Visible = True

‘la muestra

End If

i = i + 1

‘aumenta el contador

Wend

End Sub

Al correrlo todas las hojas ocultas se muestran.

Ocultar y mostrar hojas

Algunas veces se requiere esconder alguna hoja, para impedir que se tenga acceso fácilmente a ella, y con ello proteger los datos, o alejarla de los “mirones”.

Para ocultar una hoja se tienen que seguir los siguientes pasos:

Seleccionar la hoja que se quiere ocultar
Colocarse en la cinta Inicio
En el grupo Celdas se da un clic en la herramienta Formato, con lo que aparece un menú
Se escoge Ocultar y mostrar y luego Ocultar hoja

Otra forma más fácil es colocándose sobre la etiqueta de la hoja, un clic derecho y se pide Ocultar

Para volver a mostrarla hay que seguir estos pasos:

Colocarse en la cinta Inicio
En el grupo Celdas se da un clic en la herramienta Formato, con lo que aparece un menú
Se escoge Ocultar y mostrar y luego Mostrar hoja
Aparece un cuadro de diálogo que muestra las hojas ocultas. Se marca la que se quiere mostrar y se da clic en Aceptar.

Otra opción es con el clic derecho sobre los tabuladores de hojas y seleccionar Mostrar.

Notas sobre los Comentarios

En Excel existe una manera simple de hacer anotaciones o comentarios en cada una de las celdas. Estos aparecen como si fueran un Post-It™.

Gracias a las herramientas que se encuentran en Revisar se pueden manejar estos Comentarios.

Con Nuevo comentario, el cual también puede ser llamado dando un clic derecho en la celda se crea una pequeña anotación, la cual lleva el nombre del autor de la hoja. Si se da un clic derecho se puede llamar a la opción Formato de comentario.

Aparece un cuadro de diálogo que nos permite cambiar el tipo de fuente, su distribución y hasta el color del comentario.

Otras herramientas muy útiles son las de Anterior y Siguiente que se encuentran en la cinta Revisar. Al dar un clic pasan de uno en uno por los comentarios que se encuentran en la hoja.

En el momento en que un comentario está seleccionado, la herramienta Nuevo comentario se convierte a Editar comentario, la cual permite hacer cambios al texto que se introdujo.

También se tiene Borrar comentario, la cual los quita en forma permanente.

Por último se encuentran Mostrar u ocultar comentarios y Mostrar todos los comentarios, los cuales habilitan que se vean o no en la hoja.

Y ahora, déjame un comentario sobre esta entrada.

Cómo escribir SI en macros (IF)

Una de las funciones lógicas más utilizadas es el SI. Lo que hace es validar si se cumple una condición lógica y toma dos alternativas: si la condición es verdadera o falsa.

=SI(Prueba lógica, VERDADERO, FALSO)

Al crear una macro aumentamos potencialmente a las funciones. Un SI permite realizar muchísimas funciones de acuerdo a la respuesta de la prueba lógica.

Sin embargo, tiene que escribirse con cierta estructura:

En el momento en que el VBA (Visual Basic para Aplicaciones) se encuentra con la instrucción IF sabe que tiene que validar una prueba lógica o condición. Si la condición es verdadera entonces se ejecutan las líneas de programa que vienen a continuación del THEN (entonces). No importa si es una o varias. El VBA sabrá cuando terminan porque se encuentran con el ELSE (en otro caso) o el fin del IF.

La cláusula ELSE es opcional, pero si se presenta ejecuta las líneas de comando que corresponden por no cumplirse la condición.

La instrucción, como casi todas las estructuras en un macro termina con un END y en este caso, para separarlo de otros es un END IF

A veces se requieren IFs uno dentro de otro (anidado).

Debido a que el programa se encuentra escrito para fácil lectura se observa como cada END IF cierra cada condición. Siempre se cierra la condición interna primero y luego la externa.

Contar el número de datos que estén entre dos fechas

El siguiente ejemplo es de una empresa de refrigeración qiue guarda su información en hojas de Excel. En una de ella tiene las pólizas de mantenimiento de equipo. A Carlos se le asignó que obtuviera una lista de las que se van a vencer por mes.

Se que al mencionar esto, ya estás pensando en varias propuestas para resolverle la tarea a Carlos. Orden de datos, filtros por fechas y hasta tablas dinámicas se pueden utilizar.

Para esta entrada vamos a revisar la función CONTAR.SI.CONJUNTO. Esta función se incluyó a partir de la versión 2007. Lo que hace es determinar el número de datos que cumplen con varias condiciones.

La forma de escribirla es

CONTAR.SI.CONJUNTO(rango, condición1, rango, condición2, …).

En la tabla se muestran las pólizas y sus fechas de vencimiento. Los valores a buscar se colocan en las celdas D1 y D2. Es importante mencionar que D2 siempre será igual o mayor a D1.

Para calcular el número de pólizas que cumplen con esta condición escribimos en E2

=CONTAR.SI.CONJUNTO(D5:D15,»>=»&D1,D5:D15,»<=»&D2)

El primer parámetro es el rango de fechas de la tabla, el segundo es la condición que marca todas las fechas que sean mayores de D1. Para este parámetro se pone parte de la fórmula en texto (separado por comillas) y se concatena con el valor que tenga la celda D1. Para los siguientes parámetros seleccionamos el siguiente rango y para el cuarto se seleccionan los que tengan fecha menor a D2.

Si aparte queremos indicar cuales son las pólizas que están entre esas fechas podemos utilizar el formato condicional:

Se selecciona la lista de fechas (D5 a D15 en el ejemplo)
Se llama a Inicio – Formato condicional – Nueva regla
Se selecciona Aplicar formato únicamente a las celdas que contengan
Se escriben los valores =$D$1 y =$D$2 y se cambia el color de fondo del formato
Clic en Aceptar

Relleno mejorado

Idea original de Allen Wyatt Excel Tips.

Es conocido por muchos que Excel cuenta con una función que permite rellenar las celdas de forma rápida. Si queremos una secuencia de números o de fechas, o simplemente repetir un texto en varias celdas, seleccionamos las primeras de la lista y aparece un pequeño cuadro en la esquina inferior derecha.

Se hace un clic arrastrado y se marcan las celdas que se quieren rellenar. Si la operación se hace “hacia adentro”, es decir con dirección hacia la parte izquierda y superior se borran los rellenos.

Existe una manera de tener un mayor control sobre cómo se realizará el relleno, como comentó Allen Wyatt en su blog.

El truco es marcar el pequeño cuadro negro con el clic derecho y luego se arrastra. Al finalizar aparece un menú que pregunta qué tipo de relleno se utilizará

¿Cómo saber si las celdas de una columna o hilera están vacías?

A veces queremos trabajar con libros u hojas que no son nuestros y queremos copiar rango de valores a nuestras hojas. Cuál será la sorpesa cuando observamos que algunas celdas marcan errores en los cálculos debido a que llaman a valores que no vienen en la copia.

Resulta que el autor de la hoja original “escondió” algunos valores poniendo las letras en blanco o los colocó en sitios alejados de la hoja.

Para saber donde se encuentran esos datos podemos utilizar una combinación de teclas.

1. Nos colocamos en la primera celda de cada columna

2. Oprimimos la tecla Ctrl y la flecha hacia abajo (puede ser la dirección que se quiera. Aquí en el ejemplo usamos hacia abajo)

3. El cursor se coloca en la última hilera del rango.

4. Si volvemos a oprimir la tecla hacia abajo sin soltar el Ctrl nos llevará al fin de la hoja, con lo que sabremos que no hay nada más

5. Al hacerlo en la columna D nos damos cuenta que al oprimir el Ctrl y flecha hacia abajo dos veces, nos lleva a la última celda y no es la de la hoja, sino donde se encuentra el valor “oculto”.

Esta solución es sencilla si no son muchas columnas o hileras, pero si es una hoja grande tardaremos mucho. Es por eso que sugiero el uso de un macro:

Sub UltimaCelda()

ActiveCell.SpecialCells(xlLastCell).Select

End Sub

La instrucción ActiveCell.SpecialCells(xlLastCell) nos lleva a la última celda con datos en esa hoja

En este caso la celda E12 es la última de la hoja, ya que el mayor número de columnas ocupadas es la E y de las hileras es la 12 (en D12 está el último valor). Ahora sabemos todo el rango que hay que copiar.

Convertir un número a palabras

En los cursos siempre me preguntan por una función que convierta números a palabras, ya que Excel™ no cuenta con una. Recientemente mi amigo Salvador Aguilera Hernández también buscaba algo que le resolviera ese problema. Para eso tenemos que crear un macro, en este caso una función que lo haga.

Lo primero es crear un libro y llamamos al VisualBasic para aplicaciones. Nos colocamos sobre el nombre del libro y le damos un clic derecho para que aparezca el menú y seleccionamos Insertar – Módulo. En el área en blanco donde escribimos las funciones copiamos el siguiente texto:

Function NumeroTexto(fCantidad As Currency) As String

‘ Recibe una cantidad como número y la regresa como texto

Dim iEntero As Currency, iCentavos As Currency

Dim bDigito As Byte, bPrimerDigito As Byte, bSegundoDigito As Byte, bTercerDigito As Byte, bNumeroBloques As Byte, bBloqueCero as Byte

Dim sBloque As String

Dim asUnidades As Variant, asDecenas As Variant, asCentenas As Variant

fCantidad = Round(fCantidad, 2)

iEntero = Int(fCantidad)

iCentavos = (fCantidad – iEntero) * 100

asUnidades = Array(«UN», «DOS», «TRES», «CUATRO», «CINCO», «SEIS», «SIETE», «OCHO», «NUEVE», «DIEZ», «ONCE», «DOCE», «TRECE», «CATORCE», «QUINCE», «DIECISEIS», «DIECISIETE», «DIECIOCHO», «DIECINUEVE», «VEINTE», «VEINTIUN», «VEINTIDOS», «VEINTITRES», «VEINTICUATRO», «VEINTICINCO», «VEINTISEIS», «VEINTISIETE», «VEINTIOCHO», «VEINTINUEVE»)

asDecenas = Array(«DIEZ», «VEINTE», «TREINTA», «CUARENTA», «CINCUENTA», «SESENTA», «SETENTA», «OCHENTA», «NOVENTA»)

asCentenas = Array(«CIENTO», «DOSCIENTOS», «TRESCIENTOS», «CUATROCIENTOS», «QUINIENTOS», «SEISCIENTOS», «SETECIENTOS», «OCHOCIENTOS», «NOVECIENTOS»)

bNumeroBloques = 1

bPrimerDigito = 0

bSegundoDigito = 0

bTercerDigito = 0

sBloque = «»

bBloqueCero = 0

For I = 1 To 3

bDigito = iEntero Mod 10

If bDigito <> 0 Then

Select Case I

Case 1

sBloque = » » & asUnidades(bDigito – 1)

bPrimerDigito = bDigito

Case 2

If bDigito <= 2 Then

sBloque = » » & asUnidades((bDigito * 10) + bPrimerDigito – 1)

Else

sBloque = » » & asDecenas(bDigito – 1) & IIf(bPrimerDigito <> 0, » Y», Null) & sBloque

End If

bSegundoDigito = bDigito

Case 3

sBloque = » » & IIf(bDigito = 1 And bPrimerDigito = 0 And bSegundoDigito = 0, «CIEN», asCentenas(bDigito – 1)) & sBloque

bTercerDigito = bDigito

End Select

Else

bBloqueCero = bBloqueCero + 1

End If

iEntero = Int(iEntero / 10)

If iEntero = 0 Then

Exit For

End If

Next I

Select Case bNumeroBloques

Case 1

NumeroTexto = sBloque

Case 2

NumeroTexto = sBloque & IIf(bBloqueCero = 3, Null, » MIL») & NumeroTexto

Case 3

NumeroTexto = sBloque & IIf(bPrimerDigito = 1 And bSegundoDigito = 0 And bTercerDigito = 0, » MILLON», » MILLONES») & NumeroTexto

End Select

bNumeroBloques = bNumeroBloques + 1

Loop Until iEntero = 0

NumeroTexto = «Son: (» & NumeroTexto & IIf(fCantidad > 1, » PESOS «, » PESO «) & Format(Str(iCentavos), «00») & «/100 M.N.)»

End Function

Sé que parece un poco complicado pero en realidad va tomando cada sección del número que se pone en la función y lo va revisando con las variables de matriz que tienen las palabras que se cambiarán por los números.

Ahora sólo tienen que poner un valor y llamar a la función

=NumeroTexto(1234.50) ó =NumeroTexto(B2)

De todas maneras dejo la hoja para descarga. Es importante que al abrirla le habiliten los macros.

Descargar hoja con macro NumeroTexto

Excel para humanos

Por Alfredo López Luján

Llamar a una foto externa a partir del valor de una celda

Cómo esconder los ceros

Macro para mostrar las hojas ocultas

Ocultar y mostrar hojas

Notas sobre los Comentarios

Cómo escribir SI en macros (IF)

Contar el número de datos que estén entre dos fechas

Relleno mejorado

¿Cómo saber si las celdas de una columna o hilera están vacías?

Convertir un número a palabras