los mejores trucos excel

LOS MEJORES TRUCOS

O'REILLY*

David y Rama Hawley

Contenido

Introduccin Por qu los mejores trucos de Excel? Cmo obtener y utilizar los trucos Cmo utilizar este libro Cmo est organizado este libro Usuarios de Windows y Macintosh Convenciones utilizadas en este libro

17 17 18 18 19 20 21

Captulo 1. Reducir la frustracin en los libros y en las hojas de clculo.... 23 La regla 80/20 Trucos sobre la estructuracin Trucos sobre el formato Trucos sobre frmulas Crear una vista personal de los libros de Excel Introducir datos en varias hojas de clculo simultneamente Agrupar hojas de clculo manualmente Agrupar hojas de clculo automticamente Impedir que los usuarios realizan ciertas acciones Impedir el comando Guardar como en un libro de Excel Impedir que los usuarios impriman un libro de Excel 23 24 24 25 27 30 30 31 33 33 36

1. 2.

3.

10

Contenido

4.

5. 6.

7. 8. 9.

Impedir que los usuarios inserten ms hojas de clculo 36 Impedir confirmaciones innecesarias 37 Activar las macros cuando no se tenga ninguna 37 Mensajes de confirmacin para guardar cambios que no se han realizado 38 Impedir los avisos de Excel para macros grabadas 39 Ocultar hojas p a r a q u e no p u e d a n ser m o s t r a d a s 41 Personalizar el c u a d r o de dilogo Plantillas y el libro p r e d e t e r m i n a d o .... 42 Crear su propia pestaa de plantillas 43 Utilizar un libro personalizado de forma predeterminada 43 Crear un ndice de hojas en el libro 45 Limitar el r a n g o de d e s p l a z a m i e n t o de la hoja de clculo Bloquear y proteger celdas q u e contienen f r m u l a s 47 51 54 56 58 58 61 62 63 63 64 64 65

10. E n c o n t r a r datos duplicados u t i l i z a n d o el f o r m a t o condicional 1 1 . Asociar b a r r a s de h e r r a m i e n t a s personalizadas a un libro en p a r t i c u l a r 12. Burlar el gestor de referencias relativas de Excel 1 3 . Q u i t a r vnculos "fantasma" en un libro 14. Reducir un libro q u e est h i n c h a d o Eliminar formatos superfluos Puesta a punto de los orgenes de datos Limpiar libros corruptos 1 5 . Extraer datos de un libro c o r r u p t o Si no puede abrir un libro Si no puede abrir el archivo

Captulo 2. Trucos sobre las caractersticas incorporadas en Excel16. Validar datos en base a u n a lista situada en o t r a hoja Mtodo 1. Rangos con nombre Mtodo 2. La funcin INDIRECTO Ventajas y desventajas de ambos mtodos 17. C o n t r o l a r el f o r m a t o condicional con casillas de verificacin Configurar casillas de verificacin para formato condicional Activar o desactivar el resaltado de los nmeros 18. Identificar f r m u l a s con el f o r m a t o condicional

6969 69 70 71 71 71 72 75

19. Contar o s u m a r celdas que se ajustan al criterio del f o r m a t o condicional. 76 Una alternativa 77 2 0 . Resaltar Filas o c o l u m n a s i m p a r e s 78

Contenido

11

2 1 . Crear efectos en 3D en tablas o celdas Utilizar un efecto 3D en una tabla de datos 22. Activar y desactivar el formato condicional y la validacin de datos con una casilla de verificacin 2 3. Admitir mltiples listas en un cuadro de lista desplegable 24. Crear listas de validacin que cambien en base a la seleccin realizada en otra lista 25. Forzar la validacin de datos para hacer referencia a una lista en otra hoja Mtodo 1. Rangos con nombre Mtodo 2. La funcin INDIRECTO Ventajas y desventajas de cada mtodo 2 6. Utilizar Reemplazar para eliminar caracteres no deseados 27. Convertir nmeros de texto en nmeros reales 28. Personalizar los comentarios de las celdas 29. Ordenar ms de tres columnas 30. Ordenacin aleatoria 3 1 . Manipular datos con el filtro avanzado 32. Crear formatos de nmero personalizados 33. Aadir ms niveles de Deshacer a Excel 34. Crear listas personalizadas 35. Subtotales en negritas de Excel El truco sobre el truco 36. Convertir las frmulas y funciones de Excel a valores Utilizar Pegado especial Utilizar Copiar aqu slo valores Utilizar una macro 3 7. Aadir datos automticamente a una lista de validacin 38. Trucar las caractersticas de fecha y hora de Excel Sumar ms all de las 24 horas Clculos de fecha y hora Horas y fechas reales Un fallo de fechas? Captulo 3. Trucos sobre nombres

80 81 82 84 86 88 88 88 89 90 90 92 94 95 97 101 107 107 108 110 111 111 111 112 113 116 116 117 119 119 123

39. Usar direcciones de datos por el nombre 123 40. Utilizar el mismo nombre para rangos en diferentes hojas de clculo . . 1 2 4

12

Contenido

4 1 . Crear funciones personalizadas utilizando n o m b r e s 4 2 . Crear r a n g o s q u e se e x p a n d a n y c o n t r a i g a n 4 3 . A n i d a r r a n g o s dinmicos p a r a obtener u n a f l e x i b i l i d a d m x i m a 4 4 . Identificar r a n g o s con n o m b r e en u n a hoja de clculo Mtodo 1 Mtodo 2

127 129 136 139 139 141

Captulo 4. Trucos sobre tablas dinmicas4 5 . Tablas dinmicas: u n t r u c o e n s m i s m a s Por qu se les llama tablas dinmicas? Para qu cosas resultan buenas las tablas dinmicas? Por qu utilizar tablas dinmicas cuando las hojas de clculo ya ofrecen muchas funciones de anlisis? Los grficos dinmicos como extensin de las tablas dinmicas Crear tablas y listas para ser utilizadas en tablas dinmicas El Asistente para tablas dinmicas y grficos dinmicos 4 6 . C o m p a r t i r tablas dinmicas pero n o sus datos 4 7 . A u t o m a t i z a r la creacin de tablas dinmicas 4 8 . M o v e r los totales finales de u n a tabla dinmica 4 9 . Utilizar de f o r m a efectiva d a t o s de o t r o libro d i n m i c a m e n t e

143143 144 144 145 145 145 147 148 150 153 154

Captulo 5. Trucos sobre grficos5 0 . Separar u n a porcin de un grfico circular 5 1 . Crear dos c o n j u n t o s de porciones en un nico grfico circular 5 2 . Crear grficos que se ajusten a los datos Dibujar los ltimos x valores correspondientes a las lecturas 5 3 . I n t e r a c t u a r con los grficos u t i l i z a n d o controles personalizados Utilizar un rango dinmico con nombre vinculado a una barra de desplazamiento Utilizar un rango dinmico con nombre vinculado a un cuadro de lista desplegable 5 4 . Tres f o r m a s rpidas p a r a a c t u a l i z a r los grficos Utilizar arrastrar y colocar Utilizar la barra de frmulas Arrastrar el rea del borde 55. Crear un simple grfico de tipo t e r m m e t r o 5 6 . Crear un grfico de c o l u m n a s con a n c h o s y altos variables

159159 161 163 166 166 167 169 170 170 171 174 175 178

Contenido

13

5 7. Crear un grfico de tipo velocmetro 58. Vincular los elementos de texto de un grfico a una celda 59. Trucar los datos de un grfico de forma que no se dibujen las celdas en blanco Ocultar filas y columnas Captulo 6. Trucos sobre frmulas y funciones 60. Aadir un texto descriptivo a las frmulas 6 1 . Mover frmulas relativas sin cambiar las referencias 62. Comparar dos rangos de Excel Mtodo 1. Utilizar Verdadero o Falso Mtodo 2. Utilizar el formato condicional 63. Rellenar todas las celdas en blanco en una lista Mtodo 1. Rellenar las celdas en blanco mediante una frmula Mtodo 2. Rellenar las celdas en blanco a travs de una macro 64. Hacer que las frmulas se incrementen por filas cuando las copie a lo largo de las columnas 65. Convertir fechas en fechas con formato de Excel 66. Sumar o contar celdas evitando valores de error 67. Reducir el impacto de las funciones voltiles a la hora de recalcular 68. Contar solamente una aparicin de cada entrada de una lista 69. Sumar cada dos, tres o cuatro filas o celdas 70. Encontrar la ensima aparicin de un valor 71. Hacer que la funcin subtotal de Excel sea dinmica 72. Aadir extensiones de fecha 73. Convertir nmeros con signo negativo a la derecha a nmeros de Excel 74. Mostrar valores de hora negativos Mtodo 1. Cambiar el sistema de fecha predeterminado de Excel Mtodo 2. Utilizar la funcin TEXTO Mtodo 3. Utilizar un formato personalizado 75. Utilizar la funcin BUSCARV a lo largo de mltiples tablas 76. Mostrar el tiempo total como das, horas y minutos 77. Determinar el nmero de das especificados que aparecen en cualquier mes 78. Construir mega frmulas

182 188 189 190 193 193 194 195 195 196 197 198 199 200 202 203 205 206 207 209 212 214 216 218 218 219 219 220 222 223 225

14

Contenido

79. Trucar mega frmulas que hagan referencia a otros libros 80. Trucar una de las funciones de base de datos de Excel para que haga el trabajo de muchas funciones Captulo 7. Trucos sobre macros 81. Acelerar el cdigo y eliminar los parpadeos de la pantalla 82. Ejecutar una macro a una determinada hora 83. Utilizar CodeName para hacer referencias a hojas en los libros de Excel 84. Conectar de forma fcil botones a macros 85. Crear una ventana de presentacin para un libro 86. Mostrar un mensaje de "Por favor, espere" 87. Hacer que una celda quede marcada o desmarcada al seleccionarla 88. Contar o sumar celdas que tengan un color de relleno especfico 89. Aadir el control Calendario de Microsoft Excel a cualquier libro 90. Proteger por contrasea y desproteger todas las hojas de clculo rpidamente 91. Recuperar el nombre y la ruta de un libro de Excel 92. Ir ms all del lmite de tres criterios del formato condicional 93. Ejecutar procedimientos en hojas protegidas 94. Distribuir macros Captulo 8. Conectando Excel con el mundo Cargar un documento XML en Excel Guardar en SpreadsheetML y extraer datos Crear hojas de clculo utilizando SpreadsheetML Importar datos directamente en Excel Ejecutar el truco El truco del truco Hacer que la consulta sea dinmica Utilizar datos diferentes Resultados con grficos 99. Acceder a servicios Web SOAP desde Excel 100.Crear hojas de clculo Excel utilizando otros entornos Spreadsheet::WriteExceI Spreadsheet:: ParseExcel 95. 96. 97. 98.

227 228 237 237 238 240 241 243 246 247 248 250 252 255 256 258 260 267 267 278 288 293 295 296 296 297 300 301 307 307 307

Contenido

15

Jakarta POI JExcelApi

307 307

Glosario ndice alfabtico

315 323

CAPTULO 1

Reducir la frustracin en los libros y en las hojas de clculoTrucos 1 a 15

Los usuarios de Excel saben que los libros son un concepto m u y potente. Pero igualmente, muchos usuarios son conscientes que trabajar con estos libros puede provocar un gran n m e r o de inconvenientes. Los trucos de este captulo le ayudarn a evitar algunos de esos inconvenientes a la vez que sacarn provecho de algunos mtodos ms efectivos, pero en ocasiones desconocidos, con los que puede controlar sus libros de trabajo. Antes de profundizar en dichos trucos, merece la pena echar un vistazo rpido a algunos conceptos bsicos que harn mucho ms sencillo crear trucos efectivos. Excel es u n a aplicacin m u y potente de hojas de clculo, con la que se pueden hacer cosas increbles. Por desgracia, muchas personas disean sus hojas de clculo de Excel con poca previsin, haciendo difcil que puedan reutilizarlas o actualizarlas. En este apartado, proporcionaremos numerosos trucos que puede utilizar para asegurarse de que crea hojas de clculo lo ms eficaces posibles.

La regla 80/20Quiz la regla ms importante a seguir cuando se disea u n a hoja de clculo es tener u n a visin a largo plazo y nunca presuponer que no necesitar aadir ms datos o frmulas a la hoja de clculo, ya que la probabilidad de que ocurra esto es alta. Teniendo esto en mente, deber dedicar alrededor del 80% de su tiempo en planificar la hoja de clculo y alrededor del 20% en implementarla. Aunque esto pueda parecer extremadamente ineficiente a corto plazo, podemos asegurar que a largo plazo ser u n a gran ventaja, adems de que despus de haber hecho varias planificaciones, luego ser mucho ms sencillo. Recuerde que las hojas de

24

Excel. Los mejores trucos

clculo estn pensadas para hacer sencilla la obtencin de la informacin por parte de los usuarios, no slo para presentarla y que tenga buen aspecto.

Trucos sobre la estructuracinSin duda, el fallo nmero u n o que cometen muchos usuarios de Excel cuando crean sus hojas de clculo es que no configuran y organizan la distribucin de la informacin en la manera en la que Excel y sus caractersticas esperan. A continuacin, y sin ninguna orden en particular, mostramos algunos de los fallos ms comunes que cometen los usuarios cuando organizan u n a hoja de clculo: Dispersin innecesaria de los datos a lo largo de diferentes libros. Dispersin innecesaria de los datos a lo largo de diferentes hojas de clculo. Dispersin innecesaria de los datos a lo largo de diferentes tablas. Tener filas y columnas en blanco en tablas con datos. Dejar celdas vacas para datos repetidos.

Los tres primeros puntos de la lista tienen que ver con u n a cosa: siempre debe intentar mantener los datos relacionados en u n a tabla continua. Una y otra vez hemos podido ver hojas de clculo que no siguen esta simple regla y por tanto estn limitadas en su capacidad para aprovechar por completo algunas de las funciones ms potentes de Excel, incluyendo las tablas dinmicas, los subtotales y las frmulas. En dichos escenarios, slo podr utilizar estas funciones aprovechndolas por completo cuando organice sus datos en u n a tabla m u y sencilla. No es una mera coincidencia que las hojas de clculo de Excel puedan albergar 65.536 filas pero solamente 256 columnas. Teniendo esto en mente, debera configurar las tablas con encabezados de columnas que vayan a lo largo de la primera fila y los datos relacionados distribuidos de forma continua directamente debajo de los encabezados apropiados. Si observa que est repitiendo el mismo dato a lo largo de dos o ms filas en u n a de esas columnas, evite la tentacin de omitir los datos repetidos utilizando celdas en blanco para indicar dicha repeticin. Asegrese de que los datos estn ordenados siempre que sea posible. Excel dispone de un excelente conjunto de frmulas de referencia, algunas de las cuales requieren que los datos estn ordenados de manera lgica. Adems, la ordenacin acelerar tambin el proceso de clculo de muchas de las funciones.

Trucos sobre el formatoMs all de la estructura, los formatos tambin pueden causar problemas. Aunque una hoja de clculo debera ser fcil de leer y seguir, esto suele ser a costa

1. Reducir la frustracin en los libros y en las hojas de clculo

25

de la eficiencia. Somos grandes creyentes de "mantenerlo todo sencillo", aunque muchas personas dedican grandes cantidades de tiempo a formatear sus hojas de clculo. Aunque no se den cuenta, este tiempo frecuentemente suele ser a costa de la eficiencia. La sobrecarga de formatos hacen que aumente el t a m a o del libro y aunque ste parezca u n a verdadera obra de arte, puede parecerle horrible a otra persona. Debe considerar la posibilidad de utilizar algunos colores universales para sus hojas de clculo, como puedan ser el negro, el blanco y el gris. Siempre es u n a buena idea dejar al menos tres filas en blanco por encima de la tabla (al menos tres, a u n q u e es preferible dejar ms). Se pueden utilizar estas filas para insertar funciones de base de datos y de filtrado avanzado. Muchas personas tambin se preocupan por cambiar la alineacin de las celdas. De forma predeterminada, los nmeros en Excel se alinean a la derecha y los textos a la izquierda, y realmente existen buenas razones para dejarlo as. Si empieza a cambiar estos formatos, resultar que no podr saberse si el contenido de u n a celda es un texto o un nmero. Es m u y habitual encontrar gente que hace referencia a celdas que parecen nmeros pero en realidad son texto. Si cambia la alineacin predeterminada, conseguir hacerse un lo. La nica excepcin a esta regla p o dran ser los encabezados de las columnas. De formato texto a las celdas slo cuando sea completamente necesario, ya que todos los datos que se introduzcan en dichos celdas se convertirn en texto, incluso si lo que deseaba era introducir un nmero u n a fecha. Peor an, cualquier celda que albergue u n a frmula que haga referencia a u n a celda con formato texto, tambin quedar formatearla como texto. Y normalmente, no desear que las celdas con frmulas estn formateadas as. Tambin pueden crear problemas las celdas combinadas. La base de datos de conocimientos de Microsoft est repleta de problemas frecuentes que se encuent r a n en relacin a las celdas combinadas. Una buena alternativa es utilizar la opcin Centrar en la seleccin, que se encuentra en el cuadro de lista desplegable Horizontal de la pestaa Alineacin del cuadro de dilogo Formato de celdas.

Trucos sobre frmulasOtro de los grandes errores que a menudo cometen los usuarios con las frmulas de Excel es hacer referencia a columnas enteras. Esto hace que Excel tenga que examinar en potencia miles, sino millones de celdas que de otra manera p o dra ignorar. Tomemos, por ejemplo, un caso en el que tiene u n a tabla con datos que se distribuyen desde la celda Al a la celda H1000. Puede decidir que desea utilizar u n a o ms frmulas de referencia de Excel para extraer la informacin requerida. Dado que la tabla continuar creciendo (a medida que aadan nuevos datos), es habitual hacer referencia a toda la tabla, que incorpora todas las filas. En otras

26


palabras, la referencia ser algo parecido a A:H, o posiblemente Al :H65536. Puede utilizar esta referencia de forma que cuando se aaden nuevos datos a la tabla, sern referenciados en las frmulas automticamente. Esto resulta un hbito m u y malo y siempre debera evitarlo. Todava puede eliminar la constante necesidad de actualizar las referencias de las frmulas al incorporar nuevos datos que se aaden a la tabla utilizando nombres de rangos dinmicos, que veremos en u n o de los trucos que presentaremos ms adelante. Otro problema tpico que surge en las hojas de clculo malamente diseadas es el reclculo tremendamente lento. Mucha gente sugiere cambiar el modo de clculo a manual, a travs de la opcin que aparece en la pestaa Calcular del cuadro de dilogo Opciones. Sin embargo, normalmente es un mal consejo, que puede provocar n u m e r o sos problemas. Una hoja de clculo son todas las frmulas y clculos, as como los resultados que producen. Si utiliza una hoja de clculo con el modo de clculo manual, tarde o temprano leer alguna informacin que no haya sido actualizada. Puede que las frmulas estn reflejando valores antiguos en vez de los actualizados, porque cuando se utiliza el modo de clculo manual, debe forzar a Excel a que los realice pulsando la tecla F9. Pero es m u y sencillo olvidarse de hacer esto! Pinselo de esta forma: si los frenos de su coche se estuviesen desgastando tanto que hiciesen que fuera ms lento, desconectara el pedal del freno y utilizara el freno de m a n o en vez de intentar arreglar el problema? Muchos de nosotros no haramos algo as, pero otras personas no tienen ningn inconveniente en poner sus hojas de clculo en modo de clculo manual. Si tiene la necesidad de utilizar la hoja de clculo en modo manual, entonces tiene un problema de diseo. Las frmulas matriciales son otra de las causas comunes de los problemas. Estn pensadas para hacer referencia a celdas simples, pero si los utiliza para hacer referencia a grandes rangos, hgalo lo menos posible. Cuando un gran nmero de colecciones hacen referencia a rangos extensos, el rendimiento del libro se ver afectado, a veces hasta el punto en el que ni siquiera se puede utilizar y tiene que cambiar a modo de clculo manual. Las funciones de base de datos de Excel proporcionan muchas alternativas al uso de frmulas matriciales, como veremos ms adelante en un truco. Adems, la ayuda de Excel ofrece algunos estupendos ejemplos de cmo utilizar estas frmulas en grandes tablas de datos para devolver ciertos resultados en base a m l tiples criterios. Otra alternativa que a menudo es pasada por alto es la utilizacin de las tablas dinmicas de Excel, que veremos en el captulo 4. Aunque las tablas dinmicas puedan parecer sobrecogedoras la primera vez que se ven, le recomendamos encarecidamente que se familiarice con esta potente funcin de Excel, ya que cuando sea un maestro, se preguntar cmo pudo sobrevivir sin ellas.


27

Al final del da, sino recuerda nada ms acerca del diseo de la hoja de clculo, recuerde que Excel funciona mucho mejor cuando todos los datos relacionados estn distribuidos en u n a tabla continua. Eso har que la utilizacin de los t r u cos sea mucho ms sencilla.

Crear una vista personal de los libros de ExcelExcel le permite mostrar varios libros abiertos simultneamente y por tanto presentarlos en una vista personalizada organizada en diferentes ventanas. Entonces, puede guardar el espacio de trabajo como un archivo .xlw y utilizarlo posteriormente cuando lo desee.

A veces, trabajando con Excel, puede que necesite tener ms de un libro abierto en la pantalla, lo que permite utilizar visualizar los datos de mltiples libros de forma fcil y rpida. En los siguientes prrafos describiremos cmo hacer esto de u n a forma organizada y ordenada. Abra todos los libros que desee utilizar. Para abrir ms de un libro a la vez, seleccione la opcin Archivo>Abrir, mantenga pulsada la tecla Control mientras selecciona los libros que desea abrir y finalmente baga clic en el botn Abrir.

\

w

\

Desde cualquiera de los libros de Excel (no importa cul), seleccione la opcin de men Ventana>Organizar. Si est activada la casilla de verificacin Ventanas del libro activo, desactvela y luego seleccione la organizacin que prefiera. Para terminar, haga clic en el botn Aceptar. Si eligi la opcin Mosaico, se le presentarn los libros como un mosaico en la pantalla, tal y como puede verse en la figura 1.1. Si selecciona la opcin Horizontal, se distribuirn los libros de arriba a abajo ocupando todo el ancho de la pantalla, tal y como se muestra en la figura 1.2. Si eligi la opcin Vertical, se distribuirn los libros u n o al lado del otro, de izquierda a derecha, como puede verse en la figura 1.3. Por ltimo, como muestra la figura 1.4, seleccionando la opcin Cascada se mostrarn las ventanas unas encima de otras desde la parte superior izquierda a la parte inferior derecha. Una vez que los libros se muestran de la forma que ms prefiera, puede copiar, pegar, arrastrar, etc. informacin entre ellos fcilmente. Si cree que ms adelante querra volver a utilizar esta vista que acaba de crear, puede guardar la configuracin de la distribucin de las ventanas como un espacio de t r a b a j o . Para ello, s i m p l e m e n t e seleccione la o p c i n de m e n Archivo>Guardar rea de trabajo, introduzca el nombre de archivo en el cuadro

28


de dilogo correspondiente y haga clic en el botn Guardar. Cuando graba un rea de trabajo, la extensin del archivo ser .xlw en vez de .xls. Para recuperar un rea de trabajo de Excel a u n a ventana completa de u n o de los libros en particular, simplemente haga doble clic en la barra de ttulo de la ventana correspondiente. Tambin puede hacer clic en el botn de maximizar de cualquiera de las ventanas del rea de trabajo. Una vez que haya acabado, puede cerrar los libros de Excel de la forma habitual.

Figura 1.1. Cuatro libros abiertos en vista mosaico. Cuando necesite volver a abrir los mismos libros, bastar con abrir el archivo .xlw, con lo que mgicamente se mostrarn con la misma distribucin con la que fueron guardados. Si solamente necesita abrir u n o de los libros, hgalo de la forma habitual. Cualquier modificacin que haga en alguno de los libros que forman parte del rea de trabajo se guardar automticamente cuando cierre el rea de trabajo como conjunto, aunque tambin puede guardar cada libro de forma individual. Si dedica u n a pequea parte de tiempo a configurar algunas vistas personalizadas para realizar tareas repetitivas que requieren de mltiples libros abiertos, encontrar que esas tareas sern ms fciles de gestionar. Quiz decida utilizar diferentes vistas para diferentes tareas repetitivas, dependiendo de cul sea la tarea o cmo se sienta ese da.


29

Figura 1.2. Cuatro libros en vista horizontal.

Figura 1.3. Cuatro libros en vista vertical.

30


Figura 1.4. Cuatro libros en vista cascada.TRUCO

Introducir datos en varias hojas de clculo simultneamenteEs muy comn tener los mismos datos en varias hojas de clculo simultneamente. Puede utilizar la herramienta de Excel para agrupar de forma que los datos introducidos en una hoja se introduzcan automticamente en el resto de hojas al mismo tiempo. Tambin disponemos de una aproximacin ms rpida y ms flexible para hacer esta tarea, que requiere de un par de lneas de cdigo de Visual Basic for Applications (VBA).

El mecanismo que incorpora Excel para hacer que los datos se introduzcan en mltiples lugares al mismo tiempo es u n a funcin llamada "Grupo", la cual funciona agrupando las hojas de clculo de forma que todas estn vinculadas con el libro de Excel.

Agrupar hojas de clculo manualmentePara utilizar la funcin Grupo manualmente, simplemente haga clic en la hoja en la que va a introducir los datos y pulse la tecla Control (tecla Mays en


31

Macintosh) mientras hace clic en las pestaas de las hojas de clculo en las que desea insertar simultneamente los datos. Cuando introduzca datos en cualquiera de las celdas de la hoja de clculo, se introducirn automticamente en el resto de hojas de clculo agrupadas. Misin completada. Para desagrupar las hojas de clculo, bien seleccione u n a hoja de clculo que no sea parte del grupo o bien haga clic con el botn derecho del ratn en cualquiera de las pestaas de las hojas de clculo agrupadas y seleccione la opcin

Desagrupar hojas.Cuando las hojas de clculo estn agrupadas, si echa un vistazo a la barra de ttulo de Excel, ver que aparece la palabra "Grupo" encerrada entre corchetes. Esto le hace saber que todava tiene agrupadas las hojas de clculo. A menos que tenga vista de guila y una memoria de elefante, es ms que probable que no se d cuenta o se olvide de que tiene agrupadas las hojas de clculo. Por tanto, le sugerimos que las desagrupe tan pronto como haya terminado con lo que estuviese haciendo.

/

Aunque este mtodo es fcil, necesita que recuerde agrupar y desagrupar las hojas cuando necesite, corriendo el riesgo de sobrescribir datos en cualquier otra hoja de clculo si se olvida de desagruparlas. Tambin significa que se producirn entradas de datos simultneas independientemente de la celda en la que est situado. Por ejemplo, quiz solamente desee introducir datos simultneamente cuando se encuentre en un cierto rango de celdas en particular.

Agrupar hojas de clculo automticamentePuede evitar estos inconvenientes fcilmente utilizando un cdigo VBA m u y sencillo. Para que pueda funcionar, debe residir dentro del mdulo privado del objeto Sheet (Hoja). Para acceder rpidamente al mdulo privado, haga clic con el botn derecho del ratn en la pestaa con el nombre de la hoja y seleccione la opcin Ver cdigo. Entonces podr utilizar uno de los eventos de Excel para las hojas de clculo, los cuales ocurren dentro de la propia hoja de clculo, como puede ser cambiar u n a celda, seleccionar un rango, activar, desactivar, etc. mediante dichos eventos, podr mover el cdigo dentro del mdulo privado del objeto Sheet. Lo primero que h a y que hacer para que funcione el agrupamiento es dar nombre al rango de celdas que desea tener agrupadas de forma que los datos se introduzcan automticamente en el resto de hojas de clculo. Escriba este cdigo en el mdulo privado:Prvate Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("MiRango"), Target) Is Nothing Then

32


'Hoja5 se ha colocado primero a propsito ya que ser la hoja activa desde la que trabajaremos Sheets(Array("Hoja5", M Hoja3", "Hojal")).Select Else Me.Select End If End Sub

En este cdigo, hemos utilizado el rango cuyo nombre es "MiRango", pero puede cambiar este nombre por el que est utilizando en su hoja de clculo. Tambin deber cambiar los tres nombres de hoja en el cdigo, tal y como se muestra la figura 1.5, con aquellos nombres de hoja que desea agrupar. Cuando haya terminado, cierre la ventana de mdulo o bien pulse Alt/Comando-Capara volver a la ventana principal de Excel.

Figura 1.5. Cdigo para a g r u p a r automticamente hojas de clculo.

Es importante resear que el primer nombre de hoja utilizado en el array debe ser el de la hoja que contiene el cdigo y por tanto la hoja de clculo en la que se introducirn los datos. Una vez que haya escrito el cdigo en el lugar adecuado, cada vez que seleccione cualquier celda de la hoja de clculo, el cdigo comprobar si la celda que ha seleccionado (el objetivo) est dentro del rango llamado "MiRango". Si es as, el cdigo agrupar automticamente las hojas de clculo que desea agrupar. Si por el contrario esto no es as, desagrupar las hojas simplemente activando la hoja en la que se encuentra. La maravilla de este truco es que no hay necesidad de agrupar manualmente las hojas y por tanto correr el riesgo de olvidarse de


33

desagruparlas, por lo que esta aproximacin le ahorrar gran cantidad de tiempo y frustracin. Si desea que aparezcan los mismos datos en las otras hojas pero no en las mismas direcciones de celdas, escriba el siguiente cdigo:Prvate Sub worksheet_Change(ByVal Target As Range) If Not Intersect(Range("MiRango"), Target) Is Nothing Then With Range("MiRango") .Copy Destination:=Sheets("Hoja3").Range("Al") .Copy Destination:=Sheets("Hojal").Range("DIO") End With End If End Sub

Este cdigo tambin necesita estar incluido dentro del mdulo privado del objeto Sheet. Siga los pasos que describimos anteriormente en este mismo truco para poder llegar a dicho mdulo.

Impedir que los usuarios realizan ciertas accionesAunque Excel proporciona proteccin general para los libros y hojas de clculo, esta caracterstica no proporciona privilegios limitados a los usuarios a menos que utilice un truco.

Se pueden gestionar las interacciones de los usuarios con las hojas de clculo monitorizando y respondiendo a los eventos. Los eventos, como su nombre indica, son acciones que ocurren a medida que se trabaja con los libros y las hojas de clculo. Algunos de los eventos ms comunes incluyen abrir un libro, guardarlo y cerrarlo cuando el usuario desee. Se le puede indicar a Excel que ejecute cierto cdigo Visual Basic cuando cualquiera de estos eventos se produzca. Los usuarios pueden saltarse todas estas protecciones si desactivan las macros por completo. Si la seguridad est establecida a nivel medio, sern notificados de que existen macros en el libro abierto, dando la posibilidad de desactivarlas. Un nivel de seguridad alto simplemente desactivar las macros automticamente. Por otro lado, si las hojas de clculo requieren del uso de macros, es ms que probable que los usuarios desean tener las macros activadas. Estos trucos son prcticos y no proporcionan una seguridad de datos que requiera de gran carga de trabajo.

/

Impedir el comando Guardar como en un libro de ExcelSe puede especificar que cualquier libro de Excel sea guardado como slo lect u r a activando la casilla de verificacin Se recomienda slo lectura que se en-

34


cuentra accediendo a la opcin Opciones generales del cuadro de dilogo Guardar. Con esto, se evita que un usuario pueda guardar cualquier cambio que haya realizado al archivo, a menos que lo grabe con un nombre diferente o en u n a ubicacin distinta. A veces, sin embargo, desear impedir que los usuarios puedan guardar u n a copia del libro en otra carpeta con el mismo nombre de archivo o con cualquier otro. En otras palabras, lo que desea es que los usuarios slo puedan guardar sobre el archivo existente y no crear otra copia del mismo. Esto es particularmente interesante cuando hay ms de una persona guardando los cambios en un libro de Excel, porque no desea que haya diferentes copias de un mismo libro guardadas con el mismo nombre pero en carpetas diferentes. El evento Bef o r e S a v e que vamos a utilizar existe desde Excel 97. Como su propio nombre indica, este evento se produce justamente antes de que un libro sea guardado, permitindole interactuar con el usuario mostrando u n a advertencia e impidiendo que Excel continuar grabando. Antes de probar esto en su casa, asegrese de guardar su libro de Excel antes. Si coloca este cdigo sin haber guardado los cambios antes, ya no podr hacerlo. Para insertar el cdigo, abra el libro de Excel, haga clic con el botn derecho del ratn en el icono de Excel situado justo a la izquierda del m e n Archivo y seleccione la opcin Ver cdigo, como puede verse en la figura 1.6.

Figura 1.6. Men de acceso rpido al mdulo privado del objeto Workbook.


35

Este acceso rpido no est disponible en Macintosh. Tendr que abrir el Editor de Visual Basic pulsando Opcin-Fll o bien seleccionando la opcin de men Herramientas>Macro>Editor Visual Basic. Una vez en l, haga clic con el botn derecho en ThisWorkbook que est situado en la ventana de proyectos de la parte izquierda. Escriba el siguiente cdigo en VBE, tal y como se muestra en la figura 1.7 y luego pulse Alt/Comando-CLpara volver a la ventana principal de Excel.

Figura 1.7. Cdigo una vez introducido en el mdulo privado (ThisWorkbook).P r v a t e Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)Dim lReply As Long If SaveAsUI = True Then lReply = MsgBox("No tiene permiso para guardar este " & _ "libro con otro nombre. Desea guardarlo con el mismo nombre?", vbQuestion + vbOKCancel) Cancel = (lReply = vbCancel) If Cancel = False Then Me.Save Cancel = True End If End Sub

Vamos a probarlo. Seleccione la opcin Archivo>Guardar y el libro se guardar de forma normal. Ahora, intente seleccionar la opcin Archivo>Guardar como y

36


entonces ver un mensaje que le indica que no tiene permiso para guardar este libro con otro nombre diferente.

Impedir que los usuarios impriman un libro de ExcelQuiz desee impedir que los usuarios puedan imprimir un libro para que luego seguramente acabe en u n a papelera o tirado en un escritorio a la vista de todos. Utilizando el evento Bef o r e P r i n t , podemos impedir esto. Introduzca el siguiente cdigo, como hicimos anteriormente, en el Editor de Visual Basic:Prvate Sub workbook_BeforePrint(Cancel As Boolean) Cancel = True MsgBox "No puede imprimir este libro.", vblnformation End Sub

Pulse Alt/Comando-Q_cuando haya terminado de introducir el cdigo para guardarlo y volver a la ventana principal de Excel. Ahora, cada vez que los usuarios intenten imprimir este libro, no podrn hacerlo. La lnea de cdigo con la instruccin MsgBox es opcional, pero siempre es buena idea incluirla para que informe al usuario de que no moleste al departamento de Tecnologa Interna diciendo que su programa no funciona. Si desea impedir que los usuarios impriman solamente algunas hojas del libro, utilice este cdigo en vez del anterior:Private Sub workbook_BeforePrint(Cancel As Boolean) Select Case ActiveSheet.ame Case "Hojal", "Hoja2" Cancel = True MsgBox "No puede imprimir esta hoja de este libro.",_ vblnformation End Select End Sub

Observe que hemos especificado las hojas Hojal y Hoja2 como las que tienen prohibido ser impresas. Por supuesto, puede cambiar esos nombres por el de cualquier otra hoja que desee bloquear. Tambin puede aadir ms hojas a la lista, simplemente escribiendo una, seguida del nombre de la hoja entre dobles comillas. Si slo desea impedir la impresin de u n a sola hoja, incluya su nombre entre dobles comillas detrs de la sentencia Case y elimine la coma sobrante.

Impedir que los usuarios inserten ms hojas de clculoExcel le permite proteger la estructura de un libro de forma que los usuarios no puedan eliminar hojas de clculo, reordenarlas, cambiar sus nombres, etc. A


37

veces, sin embargo, desear impedir simplemente que se puedan aadir nuevas hojas de clculo, permitiendo que se realicen el resto de acciones. El siguiente cdigo le permitir hacer esto:Prvate Sub Workbook_NewSheet(ByVal Sh As Object) Application.DisplayAlerts = False MsgBox "No puede aadir nuevas hojas de clculo a este libro.",_ vbInformation Sh.Delete Application.DisplayAlerts = True End Sub

Este cdigo primeramente muestra el cuadro de dilogo con el mensaje y luego, inmediatamente, elimina la nueva hoja que se acaba de aadir, u n a vez que el usuario acepta el mensaje. La instruccin Appl i c a t i n . Di s p l a y A l e r t s = F a l s e impide que Excel muestre la advertencia estndar que pregunta al u s u a rio si realmente desea eliminar la hoja. Con este cdigo, los usuarios sern incapaces de aadir ms hojas de clculo al libro. Otra forma de impedir que los usuarios aadan nuevas hojas de clculo es seleccionar la opcin Herramientas>Proteger>Proteger libro y luego activar la casilla de verificacin Estructura. Sin embargo, como ya dijimos al principio de este truco, el mecanismo de proteccin de Excel es menos flexible y adems de impedir aadir nuevas hojas, tambin impedir otras muchas cosas.

Impedir confirmaciones innecesariasA veces, las interacciones de Excel puedan resultar pesadas: siempre preguntando para pedir confirmacin sobre acciones. Quitemos estos mensajes y dejemos que Excel realice las acciones. El tipo de mensajes a los que nos referimos son aquellos que preguntan si se desean activar las macros (incluso cuando no hay ninguna) o los que nos preg u n t a n si estamos seguros de que queremos eliminar un hoja de clculo. A continuacin mostramos cmo evitar estos tipos de mensajes.

Activar las macros cuando no se tenga ningunaLa memoria de Excel es de acero cuando se trata de recordar que ha grabado u n a macro en un libro. Por desgracia, Excel sigue recordando que se ha grabado u n a macro incluso si la ha eliminado utilizando la opcin Herramientas>Macro> Macros (Alt/Opcin-F8). Despus de hacer esto, si abre el libro de nuevo seguir recibiendo un mensaje que le pregunta si desea activar las macros, incluso a u n que no haya ninguna que activar.

38


Se le pedir confirmacin para activar las macros solamente si el nivel de seguridad est establecido en medio. Si est establecido en bajo, las macros se activan directamente, pero si est establecido en alto, estn desactivadas automticamente. Cuando graba u n a macro, Excel inserta un mdulo de Visual Basic que contendr los comandos y las funciones. Por ello, cuando se abre un libro, Excel comprueba si existe algn mdulo, este vaco o no. Cuando se eliminan las macros de un libro, slo se elimina el cdigo, pero no el mdulo en s (es algo as como beberse toda la leche pero dejarse el bote vaco dentro de la nevera). Para impedir que se muestren este tipo de mensajes innecesarios, deber eliminar tambin el mdulo. As es como puede hacerse esto: Abra VBE seleccionando la opcin Herramientas>Macro>Editor de Visual Basic (o pulsando A l t / C o m a n d o - F l l ) y luego seleccionando la opcin Ver>Explorador de proyectos (en Macintosh, la ventana de proyectos siempre est abierta, por lo que no necesitar abrir el explorador de proyectos). A continuacin podr ver una ventana como la que se muestra en la figura 1.8.

Figura 1.8. Mdulos del Explorador de proyectos con la carpeta Mdulos abierta. Busque el libro en el Explorador de proyectos y haga clic en el icono + situado a su izquierda para visualizar los componentes del libro, en particular los m d u los. Haga clic en el icono + de la carpeta Mdulos para obtener u n a lista de todos los mdulos. Haga clic con el botn derecho del ratn en cada mdulo y elija la opcin Quitar mdulo. Cuando se le pregunte, rechace la opcin de exportar los mdulos. Antes de quitar los mdulos que pudieran tener cdigo til, haga doble clic en cada u n o de ellos para asegurarse de que no los necesite. Al terminar, pulse A l t / C o m a n d o - C t p a r a volver de nuevo a la ventana principal de Excel.

Mensajes de confirmacin para guardar cambios que no se han realizadoProbablemente habr observado que a veces al abrir un libro y echar un vistazo a su informacin es suficiente para que Excel le pregunte si desea guardar los


39

cambios en el libro de macro personal (aunque de hecho no ha realizado ninguno). Lo ms probable es que tenga u n a funcin imprevisible dentro del libro de macro personal. Un libro de macro personal es un libro oculto que se crea la primera vez que graba u n a macro y que se abre cada vez que se utiliza Excel. Una funcin (o frmula) imprevisible es aquella que se recalcula automticamente cada vez que realiza prcticamente cualquier cosa en Excel, incluyendo abrir y cerrar un libro o la aplicacin entera. Dos de las funciones imprevisibles ms comunes son Hoy () y Ahora() . Por tanto, aunque crea que no ha realizado cambios en el libro, puede que esas funciones que se ejecutan en segundo plano s los h a y a n hecho. Esto cuenta como un cambio y hace que Excel le pregunte si desea guardar dichos cambios. Si desea que Excel deje de preguntar por aquellos cambios que no ha realizado, dispone de un par de opciones. La ms obvia es no almacenar funciones imprevisibles al principio dentro del libro de macro personal y luego eliminar cualquier funcin imprevisible que ya exista. La otra opcin, en caso de que necesite utilizar funciones imprevisibles, puede ser utilizar este sencillo cdigo para hacer que Excel crea que el libro de macro personal ha sido guardado en el m o mento en el que se abre:Prvate Sub workbook_Open( Me.Saved = True End Sub )

Este cdigo debe residir en el mdulo privado del libro del libro de macro personal. Para llegar ah desde cualquier libro, seleccione la opcin Ventana>Mostrar, seleccione Personal.xls y luego haga clic en Aceptar. Luego abra VBE e introduzca el cdigo anterior. Finalmente, pulse Alt/Comando-Capara volver a la ventana principal de Excel cuando haya terminado. Por supuesto, si dispone de u n a funcin imprevisible que quiere que sea recalculada y por tanto guardar los cambios que haya realizado, entonces introduzca el siguiente cdigo:Prvate Sub workbook_Open( Me.Save End Sub )

Esta macro guardar el libro de macro personal automticamente cada vez que sea abierto.

Impedir los avisos de Excel para macros grabadasUno de los muchos inconvenientes de las macros grabadas es que, aunque son m u y tiles para reproducir cualquier comando, tienden a olvidar las res-

40


puestas a los avisos que se muestran en pantalla. Elimine u n a hoja de clculo y se le pedir confirmacin; ejecute u n a macro que realice esto mismo y todava se le pedir confirmacin. Veamos cmo desactivar esos avisos. Seleccione la opcin Herramientas>Macro>Macros (Alt/Opcin-F8) para mostrar un listado de todas las macros. Asegrese de que est seleccionada la opcin Todos los libros abiertos en el cuadro de lista desplegable de la parte inferior. Seleccione la macro en la que est interesado y haga clic en el botn Modificar. Coloque el cursor antes de la primera lnea de cdigo (la primera lnea que no tiene un apostrofe delante de ella) y escriba lo siguiente:Application.DisplayAlerts = False

Y al final del todo del cdigo, aada esto:Application.DisplayAlerts = True

Con lo que la macro entera quedara as:Sub MyMacro(i

)

1 1i

MiMacro Macro Elimina la hoja de clculo actual

Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Observe que al final del cdigo volvemos a activar los mensajes de confirmacin para que Excel los muestre cuando estemos trabajando normalmente. Si se olvida de activarlos, Excel no mostrar ninguna alerta, lo cual puede ser peligroso. Si por cualquier razn la macro no se completa (un error de ejecucin, por ejemplo), Excel puede que no llegue a ejecutar la lnea de cdigo en la que se vuelven a activar los mensajes de confirmacin. Si ocurriese esto, probablemente ser mejor salir de Excel y volver a abrirlo para dejar todo en su estado normal.

/

Ahora ya sabe cmo utilizar Excel sin mensajes de confirmacin. Tenga en cuenta, de todas formas, que esos mensajes estn ah por u n a razn. Asegrese de que comprende completamente el propsito de estos mensajes antes de desactivarlos.


41

Ocultar hojas para que no puedan ser mostradasA veces deseara tener un lugar donde colocar informacin que no pueda ser leda o modificada por los usuarios. Puede construir un lugar secreto dentro del libro, un lugar donde almacenar informacin, frmulas y otros recursos que se utilizan en las hojas pero que no desea que se vean.

Una prctica m u y til cuando se configura un nuevo libro de Excel es reservar u n a hoja para almacenar informacin que los usuarios no necesitan ver: clculos de frmulas, validacin de datos, listas, variables de inters y valores especiales, datos privados, etc. Aunque se puede ocultar u n a hoja seleccionando la opcin Formato>Hoja>Ocultar, es importante asegurarse de que los usuarios no puedan volver a mostrarla seleccionando la opcin Formato>Hoja>Mostrar. Por supuesto, simplemente puede proteger la hoja, pero esto todava deja al descubierto los datos privados, las frmulas, etc. Adems, no se puede proteger las celdas que estn vinculadas a cualquiera de los controles disponibles en la barra de herramientas Formularios. En vez de esto, jugaremos con la propiedad V i s i b l e de la hoja, establecindola en x l V e r y H i d d e n . Desde VBE (Herramientas>Macro>Editor de Visual Basic o Alt/Opcin-Fl 1), asegrese de que la ventana de exploracin de proyectos est visible seleccionando la opcin Ver>Explorador de proyectos. Encuentre el n o m bre del libro y expanda su jerarqua haciendo clic en el icono + que aparece a la izquierda de su nombre. Expanda la carpeta Microsoft Excel Objetos para mostrar todas las hojas del libro. Seleccione la hoja que desea ocultar en el explorador de proyectos y muestre sus propiedades seleccionando la opcin Ver>Ventana Propiedades (o pulsando la tecla F4). Asegrese de que est seleccionada la pestaa Alfabtica y busque la propiedad Visible en la lista, que estar situada al final. Haga clic en el cuadro de texto que hay a su derecha y seleccione la ltima opcin: 2 - xISheetVeryHidden, tal y como se muestra en la figura 1.9. Pulse Alt/Comando-CLpara guardar los cambios y volver a la ventana principal de Excel. A partir de ahora, la hoja ya no estar visible desde la interfaz de Excel e incluso tampoco podr mostrarse a travs de la opcin Formato>Hoja>Mostrar.Una vez que h a y a seleccionado la opcin 2 - xISheetVeryHidden en la ventana de propiedades, puede parecer que dicha eleccin no ha tenido efecto. Este fallo visual ocurre a veces y no debera importarle. Siempre que la hoja no aparezca entre las opciones de Formato>Hoja>Mostrar, puede estar seguro de que todo ha ido bien.

s$P* ,r $r

Para revertir el proceso, simplemente siga los pasos anteriores, pero esta vez seleccionando la opcin 1 - xISheetVisible.

42


0H! EuroTool (EUROTOOL.XLA) VBAProject (Libro 1) - v Microsoft Excel Objetos 3 Hojal (Hojal) iQ Hoja2 (Hoja2) Q ThisWorkbook * Mdulos Mdulo 1 =PrimerNum; $A2

AaBbCcYyZz

Agregar J

Eliminar...

|

Aceptar

Figura 2.4. Cuadro de dilogo de formato condicional. Active la casilla de verificacin, con lo que los formatos condicionales que acabamos de configurar se aplicarn automticamente a los nmeros que estn dentro del rango que acabamos de especificar. Desactive la casilla de verificacin y el formato volver a ser el predeterminado. Como puede ver, utilizando u n a casilla de verificacin en combinacin con el formato condicional, puede hacer cosas que nadie imaginara, todo ello sin utilizar cdigo VBA.

Identificar frmulas con el formato condicionalExcel no dispone de una funcin incorporada para identificar frmulas. Una vez que una frmula es introducida en una celda, slo puede descubrir s una celda contiene un valor esttico o un valor calculado a partir de una frmula haciendo clic sobre ella y mirando la barra de frmulas. Este truco soluciona esta carencia.

El cdigo VBA para esta funcin personalizada (tambin llamada funcin definida por el usuario) le permite identificar las celdas que contienen frmulas sin necesidad de tener que hacer clic en 10.000 celdas y examinarlas u n a a una. Para crear un cazador inteligente de frmulas, comience seleccionando la opcin Herramientas>Macro>Editor de Visual Basic ( A l t / O p c i n - F l l ) y luego seleccione lnsertar>Mdulo. Entonces introduzca la siguiente funcin:Function IsFormula(Check_Cell As Range) IsFormula = Check_Cell.HasFormula End Function

Ahora cierre la ventana (pulse Alt/Comando-Q). A partir de entonces, la funcin estar disponible para cualquier celda de cualquier hoja de este libro, introduciendo, por ejemplo, la frmula = I s F o r m u l a ($A$1) . Tambin puede acceder a la funcin a travs de lnsertar>Funcin, seleccionando la opcin Definidas por el usuario en la lista de categoras y luego seleccionando IsFormula. La frmula devuelve VERDADERO si la celda a la que se hace referencia contiene u n a frmula, y FALSO en caso contrario. Puede utilizar este resultado booleano en combinacin con el formato condicional de forma que todas las frmulas se resalten automticamente con el formato que elija. Una de las mejores

76


cosas a la hora de utilizar este mtodo es que el sistema de identificacin de frmulas para la hoja de clculo ser dinmico. Esto significa que si aade o quita u n a frmula, el formato cambiar de acuerdo con ello. A continuacin explicaremos cmo hacer esto. Seleccione un rango de celdas de la hoja de clculo (por ejemplo, A1:J500) e incluya algunas celdas extra en caso de que vaya a aadir ms frmulas posteriormente. Evite la tentacin de seleccionar toda la hoja, ya que esto aadir una sobrecarga de trabajo innecesaria. Teniendo seleccionadas estas celdas, y siendo la celda Al la activa, seleccione Formato>Formato condicional. Seleccione la opcin Formato en el cuadro de lista desplegable e introduzca =IsFormula(Al) en el cuadro de texto situado a la derecha. Haga clic el botn F o r m a t o . . . y elija cualquier formato que desee para identificar las celdas con frmulas. Luego haga clic en A c e p t a r dos veces para cerrar ambos cuadros de dilogo. A veces, cuando se introducen frmulas en el formato condicional, Excel intentar colocar comillas alrededor de las frmulas despus de hacer clic en Aceptar. Esto ocurre porque Excel reconoce que ha introducido un texto, no una frmula. Si ocurre esto, vuelva al cuadro de dilogo de formato condicional, elimine las comillas y pulse Aceptar.

N

Llegados a este punto, la frmula especificada debera aplicarse a todas las celdas de la hoja que contengan u n a frmula. Si elimina o sobrescribe u n a celda que contenga u n a frmula, el formato condicional desaparecer. Igualmente, si introduce u n a nueva frmula en cualquiera de las celdas dentro del rango, quedar resaltada. Este sencillo truco que utiliza el formato condicional hace mucho ms sencillo manejar hojas de clculo cuando es necesario mucho tiempo para poder mantenerla o modificarla.

B

M Contar o sumar celdas que se ajustan al criterio W del formato condicional^H Despus de ver los resultados del formato condicional, quiz desee crear frmulas que hagan referencia solamente a los datos que han sido formateados condicionalmente. Este no entiende bien este tipo de clculos, pero puede aprender a hacerlo.

Normalmente los usuarios de Excel se preguntan: "Cmo puedo hacer clculos solamente en las celdas que tienen un color de fondo en especial?". Esta cues-

2. Trucos sobre las caractersticas incorporadas en Excel

77_

tin surge a menudo porque Excel no dispone de u n a funcin estndar para llevar a cabo esta tarea. Sin embargo, se puede hacer utilizando u n a funcin personalizada, como ya veremos posteriormente en otro truco. El nico problema que surge al utilizar u n a funcin personalizada es que no utiliza cualquier formato que se haya aplicado mediante el formato condicional. Sin embargo, pensando un poco ms, podr obtener los mismos resultados sin tener que utilizar u n a funcin personalizada. Digamos que tiene u n a larga lista de n m e r o s en el r a n g o de celdas $A$2:$A$100. Ha aplicado un formato condicional a estas celdas de forma que cualquier nmero que est entre 10 y 20 est marcado. Entonces tiene que aadir el valor de las celdas que se ajusten al criterio que acaba de establecer y luego realizar la suma de dichos valores utilizando el formato condicional. No tiene que preocuparse por el formato condicional que ha aplicado a esas celdas, pero necesita conocer el criterio que fue utilizado para marcarlas (en este caso, aquellas celdas cuyos valores estn entre 10 y 20). Puede utilizar la funcin SUMAR. SI para aadir un rango de celdas que se ajusten a un cierto criterio, pero slo a uno. Si necesita utilizar ms de un factor en el criterio, puede utilizar u n a frmula matricial. Puede utilizar u n a frmula matricial de esta forma:=SUMA(SI($A$2:$A$10 0>10;SI($A$2:$A$10 010;SI($A$2:$A$10010 y < 2 0 . Para hacer esto, siga estos pasos: Seleccione la celda B l e introduzca la siguiente frmula:=SI(Y(A2>10;A2Rellenar>Hacia abajo.

Ahora ya puede seleccionar cualquier celda en la que desea que aparezca el resultado de la suma, utilizando la funcin estndar SUMA (si lo desea, puede ocultar la columna B, de forma que no vea u n a columna extra llena de los valores devueltos por la frmula). Ciertamente los mtodos anteriores hacen bien su trabajo, pero Excel proporciona otra funcin que le permite especificar dos o ms criterios. Esta funcin es parte de las funciones de base de datos de Excel, y se llama BDSUMA. Para probarla, utilice el mismo conjunto de nmeros situados en el rango A2:A100. Seleccione las celdas C1:D2 y dle el nombre "CriterioSuma" a este rango. Luego seleccione la celda Cl e introduzca = $ A $ 1 , u n a referencia a la primera celda de la hoja. Copie esto a la celda D I , con lo que tendra un duplicado del encabezado de la columna A. Estas copias las utilizaremos como encabezados para el criterio de BDSUMA (C1:D2), que llamamos "CriterioSuma". En la celda C2, introduzca> 10. En la celda D2, introduzca < 2 0 . En la celda de la que desea mostrar el resultado, introduzca el siguiente cdigo:=BDSUMA($A$1:$A$10 0;$A$1;CriterioSuma)

BDSUMA es el mtodo preferido y ms eficaz para trabajar con celdas que deben cumplir un cierto criterio. A diferencia de las matrices, las funciones de base de datos incorporadas estn diseadas especficamente para este propsito, e incluso cuando hacen referencia a un rango m u y amplio y se utilizan con grandes nmeros, los efectos negativos que provocan en el rendimiento son mucho menores que los provocados por las frmulas matriciales.

Resaltar Filas o columnas imparesSeguramente habr visto hojas de clculo de Excel que utilizan colores alternos para las filas. Por ejemplo, las filas impares podran ser blancas y las pares grises. El formato condicional hace que esto sea muy sencillo. Alternar colores o sombreados da un aspecto profesional y puede hacer ms fcil la lectura de los datos. Puede aplicar este formato manualmente, pero como


79

ya se imaginar, resulta u n a tarea m u y pesada si tiene que actualizar dicho formato cada vez que aade o quita datos de la tabla, adems de u n a infinita paciencia. Por fortuna, el formato condicional puede reducir la paciencia necesaria y mejorar en gran medida su imagen profesional. Supondremos que los datos abarcan el rango Al :H 100. Seleccione este rango de celdas, comenzando por la celda A l , de forma que nos aseguramos que sea la celda activa. Entonces vaya a Formato>Formato condicional. Seleccione la opcin Frmula del primer cuadro de lista desplegable y en el cuadro de texto situado a su derecha introduzca la siguiente frmula, tal y como se muestra en la figura 2.5:=RESIDUO(FILA();2)

mmmmmmwm^^mKmCondicin i | Frmula jr] |=RESIDUO(FILA()j2)| Vista previa del formato que desea usar cuando la condicin sea verdadera;

&$$>?&>''

'

2>

Eliminar..,

1

Aceptar

1

Cancelar

]

Figura 2.5. Cuadro de dilogo del formato condicional con la frmula RESIDUO, para especificar un formato a cada fila par. Haga clic en el botn Formato... y elija el formato que desee aplicar a cada fila par, haga clic en Aceptar y luego haga clic otra vez en Aceptar. Entonces el formato que haya especificado debera aplicarse a cada fila par situada en el rango Al :H100. Con esto ya puede reservarse parte de su paciencia para el resto del da. Si desea utilizar este mtodo con las columnas en vez de con las filas, utilice esta frmula:=RESIDUO(COLUMNA();2)

Aunque este mtodo aplica el formato especificado a cada fila o columna par de forma rpida y sencilla, no es dinmico. Las filas que no contengan datos seguirn teniendo el formato especificado. Esto puede no resultar ideal y hacer que la lectura de la hoja de clculo sea algo ms difcil. Hacer que el resaltado de filas o columnas sea dinmico, requiere de un truco un poco ms sofisticado. Nuevamente, seleccione el rango A1:H100, asegurndose de que la celda Al sea la activa. Entonces vaya a Formato>Formato condicional y seleccione la opcin Frmula en el cuadro de lista desplegable. Luego, en el cuadro de texto de la derecha, introduzca la siguiente frmula:=Y(RESIDUO(FILA();2);CONTARA($A1:$H1))

80


^ ^w

Observe que no se hace referencia absoluta a las filas (utilizamos el signo del dlar), pero s a las columnas.

\

Haga clic en el botn Formato... y seleccione el formato que desee, haciendo clic en el botn Aceptar dos veces para cerrar ambos cuadros de dilogo. Con esto, cualquier fila que est en el rango A1:H100 y que no contengan datos, no cambiar su formato. Si elimina datos de u n a fila en particular de la tabla, t a m poco tendr el formato condicional. Si aade nuevos datos en cualquier lugar del rango Al :H100, entonces se le aplicar el formato condicional automticamente. Esto funciona as porque cuando introdujimos la frmula para el formato condicional, la frmula en s debe devolver un valor VERDADERO o FALSO. En el lenguaje de las frmulas de Excel, el valor 0 corresponde con el valor FALSO, mientras que cualquier nmero m a y o r que 0 corresponde con el valor VERDADERO. Cuando utilizamos la frmula =RESIDU0 (FILA () ; 2 ) , devuelve un valor 0 (FALSO) o bien un nmero m a y o r que 0 (VERDADERO). La funcin =FILA () es u n a funcin voltil que siempre devuelve el n m e r o de fila que corresponde con la celda en la que reside. Utilizamos la funcin RESIDUO para devolver el resto de dividir un nmero por otro. En nuestro caso, estamos dividiendo el nmero de fila por 2, por lo que todas las filas pares devolvern un valor de 0, mientras que las impares devolvern un valor mayor que 0. Cuando incluye las funciones FILA () y CONTARA dentro de la funcin Y, significa que deben devolver VERDADERO (o cualquier nmero mayor que 0) las funciones RESIDUO y CONTARA para que la funcin Y devuelva VERDADERO. La funcin CONTARA sirve para contar todas aquellas celdas que no estn vacas.

Crear efectos en 3D en tablas o celdasCuando ve un efecto 3D en un programa como pueda ser Excel, lo que est viendo en realidad es una ilusin creada por un formato en particular. Es sencillo crear esta ilusin aplicando un cierto formato a una celda o rango de celdas.

Para comenzar con un ejemplo sencillo, daremos un efecto 3D a u n a celda para que aparezca en relieve, como si fuera un botn. En u n a hoja en blanco, seleccione la celda D5 (seleccionamos esta celda para que no est en u n o de los bordes y no apreciemos bien el efecto). Vaya a Formato>Celdas>Bordes. En la lista de estilos, elija la segunda lnea ms gruesa. Asegrese de tener seleccionado el color negro (o bien el automtico, en caso de no haber cambiado el valor predeterminado). Haga clic entonces en el borde derecho y luego en el borde inferior que aparece en la muestra de la parte de izquierda. Seleccione ahora el color blanco en el cuadro de lista desplegable de colores. Todava debera estar seleccio-


82

nado el segundo borde ms grueso, por lo que esta vez haga clic en los otros dos bordes que quedan por seleccionar en la celda, es decir, el izquierdo y el superior. Vaya a la pestaa Tramas y seleccione el sombreado gris para la celda. Haga clic en Aceptar y quite la seleccin de la celda D5. Ahora debera ver cmo la celda D5 tiene un efecto relieve que le da un aspecto de botn. Todo ello lo hemos conseguido utilizando los bordes y el sombreado. Si, por diversin o por dar variedad, desea dar a u n a celda la apariencia de estar presionada, seleccione por ejemplo la celda E5 (porque es la que est j u n t o a la D5 y hace que funcione este ejemplo). Vaya a Formato>Celdas>Bordes, seleccione el segundo borde ms grueso para los estilos de lnea y asegrese de que el color elegido es el negro. Aplique el formato a los bordes superior e izquierdo de la celda. Seleccione el color blanco y aplique la lnea blanca a los bordes derecho e inferior de la celda. Haga clic entonces en la pestaa Tramas y cambie el formato de la celda a gris. Haga clic en Aceptar y podr ver cmo la celda aparece con el efecto de estar presionada. Este efecto es ms impresionante si se contrasta con el efecto de la celda D5, que est en relieve.

Utilizar un efecto 3D en una tabla de datosA continuacin, vamos a experimentar con esta herramienta para ver los efectos que podemos aplicar a las tablas o a las hojas de clculo. Seleccione las celdas D5 y E5 y haga clic en el icono Copiar formato (con forma de brocha) situado en la barra de herramientas estndar. Haga clic en la celda F5 y sin soltar el botn del ratn, arrstrelo hasta la celda J5. Ahora seleccione las celdas D5: J5 y de nuevo haga clic en el icono Copiar formato de la barra de herramientas estndar. Haga clic en la celda D6 y sin soltar el botn del ratn, arrstrelo hasta la celda J15. Esto debera provocar un efecto como el que aparece en la figura 2.6. Hemos utilizado un borde bastante grueso para asegurarnos de que el efecto puede verse claramente. Sin embargo, quiz desee matizarlo utilizando un estilo de lnea algo ms fino. Tambin podra utilizar cualquiera de los otros estilos de lnea para producir un efecto a n mayor. La mejor forma de encontrar u n a buena combinacin es utilizar el sistema de prueba y error en u n a hoja en blanco hasta dar con el efecto deseado. La nica limitacin que tiene que es su imaginacin y, quiz, su gusto. Tenga siempre en mente que los efectos 3D puede mejorar la lectura de una hoja de clculo y proporcionar un aspecto ms profesional, pero cuando se utiliza en exceso, puede tener el efecto contrario. Recuerde, utilice todo como delacin.


i

C

:

D

E

F

G

H

i

J

p-^7~^

SlllliMS l l i l l l l ^

SiliiiiliSiSII^MM^IM ^SSimWM&MB

Figura 2.6. Efecto 3D aplicado a un rango de celdas. Si desea dar un paso ms all a la hora de aplicar efectos 3D de forma a u t o mtica y dinmica, puede combinar este truco con el uso del formato condicional, de forma que la aplicacin de estilos sea automtica.

TRUCO

Activar y desactivar el formato condicional y la validacin de datos con una casilla de verificacinLa validacin de datos puede resultar til para evitar que un usuario introduzca accidentalmente datos incorrectos. Sin embargo, algunas veces desear hacer ms sencilla la introduccin de datos que de otra forma sera imposible, bien porque fuese marcada por el formato condicional o bien completamente bloqueada por una validacin de datos.

Normalmente permitir que los usuarios introduzcan datos, que de otra forma no podran, desactivando el formato condicional o la validacin de datos para dichos celdas. De todas formas, existe u n a forma sencilla para hacer esto: puede combinar u n a simple casilla de verificacin con la validacin de datos. Para este ejemplo, aplicaremos un formato condicional a un rango de celdas de forma que cualquier dato que aparezca ms de u n a vez quedar resaltado para su fcil identificacin. Supondremos que la tabla de datos se extiende por el rango $A$1 :$H$100. Para aplicar un formato condicional a este rango de forma que se puedan identificar los duplicados, hacen falta unos cuantos pasos. Primeramente seleccione la celda Kl y dle el nombre CheckBoxLink escribiendo dicho nombre en el cuadro de nombres de la parte superior izquierda de la pantalla. Si la barra de herramientas Formularios no est visible, mustrela. Entonces haga clic en el icono correspondiente a la casilla de verificacin. Luego


83

haga clic en cualquier lugar de la hoja de clculo que est fuera del rango anteriormente citado para aadir la casilla de verificacin. Haga clic con el botn derecho en la casilla de verificacin y seleccione la opcin Formato de control. Luego vaya a la pestaa Control y escriba, en el cuadro de texto, "CheckBoxLink" y haga clic en Aceptar. Seleccione la celda A l , y sin soltar el botn del ratn, seleccione un rango hasta la celda H100. Es importante que la celda Al sea la activa en la seleccin. Vaya a Formato>Formato condicional, seleccione la opcin Frmula en el cuadro de lista desplegable e introduzca la siguiente frmula en el cuadro de texto situado a su derecha (tal y como se muestra en la figura 2.7):=Y(CONTAR.SI($A$1:$H$10 0 ; A 1 ) > 1 ; C h e c k b o x L i n k )

2S\Condicin i | Frmula j * | | = V(CONT AR. 5I($ A$ 1: $H$ 100.; A1) > 1.; CheckboxLink) Vista previa del formato que desea usar cuando la condicin sea verdadera:

1|[Formato... jj j

kC&&* 'Eliminar,., j

'yAceptar

Agre^gar j

Cancelar

Figura 2.7. Cuadro de dilogo de formato condicional con la frmula que da formato resaltando los valores duplicados.

Haga clic en el botn Formato... y en la pestaa Tramas seleccione el color que desea aplicar a los datos duplicados. Haga clic en el botn Aceptar de ambos cuadros de dilogo para salir. Dado que la casilla de verificacin que acabamos de aadir est activada, el vnculo de celda en Kl (CheckBoxLink) leer el valor VERDADERO, por lo que todos los valores duplicados dentro del rango $A$1:$A$100 aparecern resaltados. En el momento en el que desactive la casilla de verificacin, el vnculo de su celda (CheckBoxLink) devolver el valor FALSO, por lo que los valores duplicados no se resaltarn. Esta casilla de verificacin le proporciona un interruptor con el cual poder activar o desactivar el formato condicional de u n a hoja, sin tener que utilizar el cuadro de dilogo Formato condicional. Puede utilizar el mismo principio con la validacin de datos, utilizando la opcin de frmula. Todo esto funciona porque hemos utilizado la funcin Y. Esta funcin devolver el valor VERDADERO si ocurren estas dos siguientes cosas: CONTAR. SI ($ A$ 1:$H$100,A1)>1 debe devolver VERDADERO y el vnculo de celda para la casilla de verificacin (CheckBoxLink) tambin debe devolver VERDADERO. En otras palabras, para que la funcin Y devuelva VERDADERO, a m bas condiciones tambin deben ser verdaderas.

84


TRUCO

Admitir mltiples listas en un cuadro de lista desplegableCuando trabajamos con mltiples listas, podemos forzar que cambie una lista utilizando una combinacin de botones de opcin y un cuadro de lista desplegable.

Externo ofrece numerosas alternativas a los usuarios para seleccionar elementos de una lista, como pueda ser nombres, productos, das de la semana o sea lo que sea que componga la lista. Sin embargo, para acceder a ms de u n a lista de elementos simultneamente, generalmente es necesario utilizar tres controles separados, como por ejemplo, tres cuadros de lista desplegable de la barra de herramientas Formularios. En vez de esto, podemos utilizar un cuadro de lista desplegable en combinacin con botones de opcin (tambin disponibles en la barra de herramientas Formularios) para hacer que u n a lista cambie automticamente de acuerdo al botn de opcin que se haya elegido. Para ver cmo funciona esto, introduzca los nmeros del 1 al 7 en el rango de celdas Al :A7 de u n a nueva hoja. En las celdas Bl :B7 introduzca los das de la semana empezando por el lunes y terminando el domingo. En las celdas C1:C7 introduzca los meses desde enero hasta julio. Las caractersticas de propagacin automtica de Excel pueden hacer este trabajo de forma mucho ms rpida y sencilla. Simplemente introduzca un 1 en la celda Al, seleccinela y mientras mantiene pulsada la tecla Control haga clic en el cuadro de propagacin situado la esquina inferior derecha de la celda. Manteniendo pulsado el botn del ratn y la tecla Control, arrstrelo hasta la celda A7. Es el rellenar automticamente las celdas con los nmeros del 1 al 7. Igualmente, puede introducir "lunes" en la celda Bl y haga doble clic en el cuadro de propagacin de dicha celda. Finalmente, introduzca "enero" en la celda Cl y haga lo mismo que con los das de la semana. Ver como Excel rellenar los das los meses de forma automtica.

S^ w^

Seleccione la opcin Ver>Barras de herramientas>Formularios y haga doble clic en el icono Botn de opcin de dicha barra de herramientas. Luego, haga clic en tres lugares cualesquiera de la hoja de clculo para colocar tres botones de opcin. Igualmente, haga clic en el icono de Cuadro combinado que haga clic en cualquier lugar de la hoja de clculo para insertar un cuadro de lista desplegable en ella. Utilice los marcadores del cuadro de lista desplegable para cambiar su t a m a o y su posicin, as como el de los botones de opcin para que estn situados justo debajo de l.


85

Haga clic con el botn derecho en el primer botn de opcin, seleccione la opcin Modificar texto y entonces reemplace el texto predeterminado por el texto "Nmeros". Haga lo mismo con el segundo botn de opcin, cambiando el texto por "Das de la semana" y con el tercero, cambiando el texto por "Meses". Puede ver el resultado de esto en la figura 2.8.B 1 2 3 4 5 6 7 3 9 10 l Mayo 1 Lunes 2 Martes 3 Mircoles 4 Jueves 5 Viernes 6 Sbado 7 Domingo C Enero Esta celda contendr los nmeros Febrero 1, 2 3, dependiendo del botn Marzo [de opcin seleccionado Abril Mayo Junio Julio $C$1:$C$?

fEsta celda reflejar el elemento elegido en el cuadro de lista I desplegable., y se puede usarcorno el argumento necesario para la frmula de bsqueda

11 12 13 1415 16 17 13

O Nmeros O' Das de la semana '$ Meses

I Esta direccin cambiar con cada seleccin realizada en los botones de opcin, provocando que la lista del cuadro desplegable cambie

D i ! Hojal / Hoja2 / Hoja3 / Figura 2.8. Cuadro de lista desplegable con mltiples listas controlado por botones de opcin.H

19 20

Ahora, mientras mantiene pulsada la tecla Control, haga clic en cada u n o de los botones de opcin de forma que todos queden seleccionados y entonces haga clic con el botn derecho del ratn sobre u n o de ellos y seleccione la opcin Formato de control. En la pestaa Control especifique $F$1 como celda vinculada (asegrese de utilizar esta referencia absoluta con los smbolos del dlar). En la celda E6 introduzca la siguiente frmula:=DIRECCION(l;$F$l) & " : " & DIRECCIN(7;$F$1)

Seleccione la opcin lnsertar>Nombre>Definir. En el cuadro de texto de la parte superior escriba "MiRango" y en el cuadro de texto Se refiere a escrbalo siguiente:=INDIRECTO($E$6)

Haga clic en A g r e g a r y luego en Aceptar. Haga clic con el botn derecho del ratn en el cuadro de dilogo que aadimos anteriormente y seleccione la opcin Formato de control. En la pestaa Control escriba "MiRango" en el cuadro de texto Rango de entrada y la celda $G$1 como vnculo. Pulse entonces el botn Acept a r . Ahora debera ser capaz de seleccionar u n o de los botones de opcin, con lo que la lista contenida en el c u a d r o de lista desplegable debera reflejar

86


automticamente el botn de opcin elegido. Cuando configure todo esto para su propia hoja de clculo, debera utilizar algunas celdas que estuviesen fuera de la pantalla como origen de las listas y vnculos del cuadro de lista desplegable. Incluso desear ocultar estas celdas a los usuarios de forma que los vnculos estn donde deberan. Tambin necesitar modificar las dos funciones DIRECCIN para que reflejen el rango de celdas que est utilizando. En las funciones que hemos utilizado en este ejemplo, el 1 representa la primera fila de la lista, mientras que el 7 representa el n m e r o de la ltima fila.

Crear listas de validacin que cambien en base a la seleccin realizada en otra listaLas necesidades en la validacin pueden variar dependiendo del contexto en el que sean utilizadas. De todas formas, puede crear una hoja de clculo en la que una lista de validacin cambie dependiendo de lo que se seleccione en otra. Para hacer que funcione este truco, lo primero que tiene que hacer es rellenar la hoja de clculo con algunos datos. En una hoja en blanco llamada "Listas" y con la celda Al seleccionada, escriba el siguiente encabezado: "Objetos". En la celda Bl, escriba el encabezado "Lista correspondiente". En las celdas A2:A5, repita la palabra "Cubo". En las celdas A6:A9 repita la palabra "Sof". En las celdas A l 0 : A l 3 repita la palabra "Ducha". En las celdas A14:17 repita la palabra "Coche". Luego, comenzando por la celda B2 y terminando por la celda B17, introduzca las siguientes palabras (que se corresponden con la lista de objetos): "Plomo", "Acero", "Abridor", "Tapa", "Cama", "Asiento", "Saln", "Colchn", "Lluvia", "Caliente", "Fro", "Templado", "Viaje", "Vacaciones", "Sombrero" y "Bota". En la celda Cl introduzca el encabezado "Lista de validacin". A continuacin, para crear u n a lista de entradas nicas, introduzca la palabra "Cubo" en la celda C2, la palabra "Sof" en la celda C3, la palabra "Ducha" en la celda C4 y la palabra "Coche" en la celda C5. Tambin puede utilizar el filtro avanzado para crear una lista de elementos nicos. Seleccione las celdas Al :A17, seleccione Datos>Filtro> Filtro avanzado y entonces active la casilla de verificacin Slo registros nicos y seleccione el botn de opcin Filtrar la listas y moverla a otro lugar. Haga clic en Aceptar y entonces seleccione las celdas A2:A14 (que incluirn las celdas ocultas). Cpielas y pegelas a la celda Al8. Seleccione entonces la opcin Datos>Filtro>Mostrar todos, seleccione la lista de objetos nicos y cpielas y pegelas en la celda A2. Con esto ya tendr la lista.

H^


87

Seleccione la opcin lnsertar>Nombre>Definir y en el cuadro de texto Nombres en el libro escriba la palabra "Objetos". En el cuadro de texto Se refiere a escrbalo siguiente frmula y luego haga clic en Agregar:=DESREF($A$2;0;0;CONTARA($A$1:$A$2 0) ;1)

Ahora escriba en el cuadro de texto Nombres en el libro el nombre "ListaVal" y en el cuadro de texto Se refiere a introduzca $C$2:$C$5. Haga clic en Agregar. Ahora inserte otra hoja, llmela "Hojal" y coloque todos estos datos en ella. Teniendo activa a n la Hojal, seleccione la opcin de men lnsertar>Nombre> Definir. En el c u a d r o de t e x t o de la p a r t e s u p e r i o r a i n t r o d u z c a la p a l a b r a "ListaCorrespondiente" y en el cuadro de texto de la parte inferior introduzca esta frmula y haga clic en Agregar:= DESREF(INDIRECTO(DIRECCIN(COINCIDIR(CeldaVall;Obj ectos;0)+1,2 ; ; ;

"Listas"));0;0;CONTAR.SI(Objectos,CeldaVall);1)

En el cuadro de texto Nombres en el libro escriba la palabra "CeldaVall" y en el cuadro de texto Se refiere a introduzca $D$6 y haga clic en Agregar. De nuevo e introduzca en el primer cuadro de texto la palabra "CeldaVal2" y $E$6 en el segundo, y luego haga clic en Agregar. Ahora haga clic en A c e p t a r para volver a la Hojal y entonces seleccione la celda $D$6. ste es un proceso largo, pero ya estamos cerca del final.Vaya a Datos> Validacin>Configuracin. Seleccione la opcin Lista del cuadro de lista desplegable y en el cuadro de texto Origen escriba " = ListaVal". Asegrese de que est activada la casilla de verificacin Celda con lista desplegable y haga clic en Aceptar. Seleccione ahora la celda E6 y de nuevo vaya a Datos>Validacin>Configuracin. Seleccione la opcin Lista en el cuadro de lista desplegable y en el cuadro de texto Origen escriba "ListaCorrespondiente". Asegrese tambin de que la casilla de verificacin Celda con lista desplegable est activada y haga clic en Aceptar. Seleccione u n o de los objetos de la lista de validacin que aparece en la celda D6 y la celda de validacin en la celda E6 cambiar automticamente para reflejar el objeto que acaba de seleccionar. Ahora ya tiene u n a lista de validacin m u y til, como la que se muestra en la figura 2.9, cuyos contenidos cambiar automticamente basndose en el elemento elegido en la otra lista. En cualquier celda o rango de celdas puede utilizar u n a lista que contenga hasta cinco listas separadas.

88


2 i3 | "4 i 15 1 ! 6 ! 7 ; 8 ! 9 i

_A_

B

['v^^g^:;.

i-=..-;-:-|

D

F

p=f

LU

La tote eofecWM co la Mi efeSeleccione un objeto t/cfto objeto que se encuentra e#i ta hoja "Listas**

Sof

I

H

|

10]H <

l \ Hoja!

XHoja2/Hojas'/

~

""'

J

JNUM

ir

Listo

Figura 2.9. Dos listas de validacin que se corresponden.

TRUCO

Forzar la validacin de datos para hacer referencia a una lista en otra hojaUna de las opciones disponibles en la funcin de validacin de datos es la opcin Lista, que proporciona un cuadro de lista desplegable con elementos especficos que el usuario puede elegir. Un problema que surge con la validacin de datos es que en el momento en el que intenta hacer referencia a una lista que reside en otra hoja, ver que es imposible. Por fortuna, ser posible mediante este truco.

Puede forzar a que la validacin de datos haga referencia a u n a lista que est situada en otra hoja, existiendo para ello dos posibles aproximaciones: rangos con nombre y la funcin INDIRECTO.

Mtodo 1. Rangos con nombreQuiz la forma ms sencilla y rpida para realizar esta tarea es dar nombre al rango en el que reside la lista. Para los propsitos de este ejercicio, supondremos que ha llamado ha dicho rango "MiRango". Seleccione la celda en la que desea que aparezca el cuadro de lista desplegable y luego vaya a Datos>Validacin. Seleccione la opcin Lista en el cuadro de lista desplegable y en el cuadro de texto Origen escriba "=MiRango". Haga clic en Aceptar. Ahora, la lista (que se encuentra en otra hoja) puede ser utilizada por la lista de validacin.

Mtodo 2. La funcin INDIRECTOLa funcin INDIRECTO le permite hacer u n a referencia a u n a celda que contiene un texto que a su vez representa la direccin de otra celda. Puede utilizar la celda que contiene la funcin INDIRECTO como referencia a u n a celda y puede


89

utilizar esta caracterstica para hacer referencia a la hoja en la que reside la lista. Supongamos que la lista est situada en la Hojal en el rango $A$1 :$A$10. Haga clic en cualquier celda de otra hoja en la que desee tener esta lista de validacin. Entonces seleccione Datos>Validacin y seleccione la opcin Lista del cuadro de lista desplegable. En el cuadro de texto Origen escriba la siguiente funcin:= INDIRECTO("Hoj a l ! $ A $ 1 : $ A $ 1 0 " )

Asegrese de que la casilla de verificacin Celda con lista desplegable est activada y entonces haga clic en Aceptar. Ahora la lista que reside en Hojal debera estar en la lista desplegable de validacin. Si el nombre de la hoja en la que reside la lista contiene espacios en blanco, utilice la funcin INDIRECTO de la siguiente forma:=INDIRECTO("'Hoja 1'!$A$1:$A$10")

Aqu hemos utilizado un apostrofe justo despus de las dobles comillas y j u s to antes del signo de exclamacin. Los apostrofes sirven para acotar los lmites del nombre de u n a hoja de Excel. Siempre es una buena idea utilizar el apostrofe, independientemente de que la hoja tenga o no espacios en blanco en su nombre. Siempre podr hacer referencia a hojas que no contengan espacios, por lo que resulta muy til para evitar problemas.

%

Ventajas y desventajas de cada mtodoExisten ventajas y desventajas a la hora de utilizar nombres de rango y la funcin INDIRECTO para forzar la validacin de datos para que haga referencia a u n a lista situada en otra hoja. La ventaja de utilizar nombres de rango en este escenario es que cualquier cambio que realice en el nombre de la hoja no tendr efecto en la lista de validacin. Esto s supone u n a desventaja en el caso de la funcin INDIRECTO, ya que cualquier cambio en el nombre de la hoja no se actualiza automticamente dentro de dicha funcin, por lo que tendr que cambiarlo de forma manual. La ventaja de utilizar la funcin INDIRECTO es que si la primera celda o fila, o la ltima celda o fila es eliminada del rango con nombre, dicho rango devolver un error #REF!. Esto supone u n a desventaja en caso de utilizar rangos con n o m bre: si elimina cualquier celda o fila dentro del rango con nombre, esos cambios no afectarn a la lista de validacin.

90


Utilizar Reemplazar para eliminar caracteres no deseadosCuando importa datos externos o los copia desde otras ubicaciones dentro de Excel, pueden aparecer caracteres no deseados en la hoja de clculo. Mediante este truco, puede evitar el inconveniente de tener que eliminar a mano dichos caracteres.

La funcin Reemplazar de Excel puede ayudarle a eliminar caracteres no deseados de una hoja de clculo, pero requiere de unos cuantos pasos extra. Por ejemplo, puede reemplazar las celdas que contengan los caracteres no deseados con nada (es decir, realmente eliminndolos). Para hacer esto, necesita saber los cdigos de los caracteres que desea eliminar. Todos los caracteres tienen un cdigo y Excel le informar cul es si utiliza la funcin CDIGO sobre ellos. Esta funcin devuelve un cdigo numrico para el primer carcter de u n a cadena de texto. Dicho cdigo corresponde con el conjunto de caracteres que est utilizando el ordenador. Para que esto funcione, seleccione u n a de las celdas que contengan un carcter no deseados. En la barra de frmulas, seleccione el carcter en cuestin y cpielo en el portapapeles. Luego seleccione cualquier celda que no est utilizando (por ejemplo la celda A l ) y copie el carcter en ella. En otra celda introduzca la siguiente frmula:=C0DIG0($A$1)

Esto devolver el cdigo del carcter no deseado. A continuacin seleccione todos los datos y vaya a Editar>Reemplazar. Haga clic en el cuadro de texto Buscar, y mientras mantiene pulsada la tecla Alt o Comando, introduzca utilizando el teclado numrico un 0 seguido del cdigo que devolvi la funcin CDIGO. Por ejemplo, si el cdigo es 163, mantenga pulsada la tecla Alt o Comando mientras escribe en el teclado numrico el nmero 0163. Deje vaco el cuadro de texto Reemplazar con y luego haga clic en el botn Reemplazar todos. Esto eliminar rpidamente todos los caracteres no deseados que coincidan con dicho cdigo. Ahora repita el mismo proceso para el resto de caracteres no deseados.

TRUCO

Convertir nmeros de texto en nmeros realesLos contenidos de una celda pueden parecer nmeros, especialmente si han sido importados, pero probablemente sea imposible utilizar dichos nmeros en los clculos. A continuacin mostramos algunos mtodos para convertir fcilmente esos nmeros "de texto" en verdaderos nmeros.

Recuerde que los nmeros en Excel estn alineados a la derecha de forma predeterminada, mientras que los textos se alinean a la izquierda. Una forma senci-


91

lia de identificar estos nmeros de texto problemticos en u n a columna que se supone debera contener n m e r o s verdaderos dicha c o l u m n a , luego ir a Formato>Celdas>Alineacin, asegurarse de que la alineacin horizontal est establecida a la opcin estndar General y luego hacer clic en Aceptar. Ample el ancho de la columna y ver como los nmeros verdaderos quedarn alineados a la derecha mientras que aquellos nmeros de texto quedarn a la izquierda. Las fechas tambin quedan alineadas a la derecha, ya que en realidad son nmeros. Ahora que ya sabe que tiene nmeros que son tratados como texto, veamos un mtodo rpido y sencillo para convertirlos a verdaderos nmeros, de forma que Excel pueda utilizarlos para sus clculos. Copie cualquier celda en blanco y entonces seleccione aquellas celdas con los nmeros. Vaya a Edicin>Pegado especial y seleccione la opcin Valores. Tambin seleccione la opcin Sumar en el apartado de operaciones y haga clic en Aceptar. Esto har que cualquier nmero que estuviese como texto se convierta a un verdadero nmero. Esto es as porque una celda vaca tiene un valor de 0 y cuando aade cualquier n m e r o a un nmero que Excel est tratando como un texto, forzar a que dicho n m e r o se convierta a un n m e r o verdadero. Puede aplicar esta misma lgica a algunas funciones estndar de Excel, en particular a las funciones TEXTO. Normalmente, cuando utiliza las funciones TEXTO de Excel y el resultado obtenido es un nmero, Excel seguir devolviendo ese nmero como si fuera un texto en vez de un valor numrico. Supongamos que tiene un rango de celdas comenzando por la $A$1. Cada celda contiene u n a cantidad de dinero con el signo del dlar al principio, seguida de un espacio y el nombre de u n a persona. Utilizando la siguiente frmula, que combina las funciones IZQUIERDA y ENCONTRAR, puede extraer dicho smbolo del dlar:=IZQUIERDA(Al;ENCONTRAR(" ";A1)-1)

Si, por ejemplo, la celda Al contuviese el valor "$22,70 Federico", el resultado de la frmula sera $22,70. Sin embargo, dicho resultado sera devuelto como un texto, no como un valor numrico. Por tanto, de forma predeterminada, quedar alineado a la izquierda. Puede modificar esta frmula de forma que el resultado no sea de tipo texto, sino un valor numrico de verdad. Para ello, aada un 0 al final, de la siguiente forma:=IZQUIERDA(Al;ENCONTRAR(" ";A1)-1)+0

Esto obligar a que el valor devuelto sea un verdadero nmero, por lo que quedar alineado a la derecha de forma predeterminada. Todo lo que queda por hacer ahora es dar formato a la celda de forma adecuada. Otro de los problemas que pueden surgir en relacin con los textos y nmeros es cuando mezcla texto y nmeros en u n a misma celda, pero sin haber u n a forma de extraer la parte n u -

92


mrica (como ocurra en el caso anterior). En este caso, tendremos que utilizar u n a funcin personalizada que extraiga la parte numrica de la cadena de texto. Para crear dicha funcin personalizada, pulse A l t / O p c i n - F l l , seleccione lnsertar>Mdulo e introduzca el siguiente cdigo:Function ExtraerNumero(rCell As Range) Dim ICount As Long, L As Long Dim sText As String Dim lNum As String sText = rCell For ICount = Len(sText) To 1 Step -1 If IsNumeric(Mid(sText, ICount, 1)) Then L = L + 1 lNum = Mid(sText, ICount, 1) & lNum End If If L = 1 Then lNum = CInt(Mid(lNum, 1, 1)) Next ICount ExtraerNumero = CLng(lNum)

End Function

Salga del editor y vuelva a la ventana principal de Excel. La funcin que acabamos de crear aparecer en la categora de funciones definidas por el usuario. Utilcela tal y como se muestra en la figura 2.10.A 1 dfgd878sdd77dd 2 jtjt330dfll33 3 fdkfjk332kki92 B C 87877 =ExtraerNumero(A1) 33033 =ExtraerNumero(A2) 332902 =ExtraerNurnero(2i

Figura 2.10. Extraer la parte numrica de un texto. En la figura 2.10, la columna A contiene una mezcla de texto y nmeros, la columna B contiene el resultado de utilizar esta funcin y la columna C muestra la apariencia de la frmula en la columna B.

Personalizar los comentarios de las celdasLos comentarios de celda le permiten colocar el equivalente a una nota en una celda especfica de una hoja. Aunque muchas personas utilizar estos comentarios, la mayora no saben que pueden ser personalizados. Cuando inserta un comentario de celda a travs de la opcin lnsertar>Comentario, Excel, de forma predeterminada, inserta tambin el nombre del usuario del


los mejores trucos excel

Documents