excel. los mejores trucos anaya multimedia

tOSNE.IORES TRUGO

GEL

/,rN/Y/,rO'REILLY'y oaYtdnahn thuw

Contenido

ZPor qu los mejores trucos de Excel? Cmo obtener v utilizar los trucos

Cmo utilizar estelibro Cmo estorganizado libro........... este Usuarios Windowsy Macintosh.............. de Convenciones utilizadas en estelibro .......... .

77 1B 1B 79 20 27

Captulo 1. Reducirlafrustracin en los librosyen las hojas de clculo....2 3 23 24 Tiucos sobre el formato 24 T i u c o ss o b r e r m u l a s . . . . . . . . . . . . . . . f 25 personal de los libros de Excel .......... Crear una vista 27 Introducir datos en varias hojas de clculo simultneamente................. 0 3 Agrupar hojasde clculomanualmente .................. 30 Agrupar hojasde clculoautomticamente ............ 31 Impedir que los usuarios realizan ciertas acciones 33 Impedir el comando Guardar como en un libro de Excel 33 Impedir que los usuarios impriman un libro de Excel 36L a r e g l aB O / 2 0 . . . Tiucos sobre la estructuracin

1. 2.

3.

10

Contenido

4.

5. 6.

7. B. 9.

36 Impedir confirmaciones innecesarias 37 Activar las macros cuando no se tenga ninguna 37 Mensajes confirmacin para guardar cambiosque no se han realizado. de 3B Impedir los avisosde Excelpara macros grabadas 39 Ocultar hojas para que no puedan ser mostradas 47 Personalizar el cuadro de dilogo Plantillas y el libro predeterminado . ... 42 Crear su propia pestaade plantillas 43 trtilizar un libro personalizado forma predeterminada de 43 Crear un ndice de hojas en el libro 45 Limitar el rango de desplazamiento de la hoja de clculo Bloquear y proteger celdas que contienen frmulas . 47 57 54 56 5B 5B 67 62 63 63 64 64 65

Impedir que los usuarios insertenms hojas de clculo

1 0 . Encontrar datos duplicados utilizando el formato condicional 7 7 . Asociar barras de herramientas personalizadas a un libro en particular 7 2 . Burlar el gestor de referencias relativas de Excel 1 3 . Quitar vnculos "fantasma" en un libro .. 7 4 . Reducir un libro que est hinchado Eliminar formatos superfluos Puestaa punto de los orgenesde datos Limpiar libroscorruptos .............. 15. Extraer datos de un libro corrupto Si no puedeabrir un libro ............ Si no puede abrir el archivo

Captulo2. Trucossobrelas caractersticas incorporadas Exce|.............. en G91. Validar datos en base a una lista situada en otra hoja ........... Mtodo 1. Rangoscon nombre Mtodo 2. La funcin INDIRECTO Ventajasy desventajasde ambos mtodos 1 7. Controlar el formato condicional con casillas de verificacin ........ . . . . ..... Configurar casillasde verificacin para formato condicional Activar o desactivar resaltado los nmeros................ el de 18. Identificar frmulas con el formato condicional 19. Contar o sumar celdas que se ajustan al criterio del formato condicional . Una alternativa............... 20. Resaltar Filas o columnas impares 69 69 70 77 77 77 72 75 76 77 7B

Contenido

11

. . . . . . . . . . . . .B 0 . 2 1 . C r e a r e f e c t o se n 3 D e n t a b l a s o c e l d a s . . . . . . . . . . . . . . . . . . . . . 1. . 8. U t i l i z a ru n e f e c t o D e n u n a t a b l ad e d a t o s . . . . . . . . . . . 3 22. Activar y desactivar el formato condicional y la validacin de datos c o n u n a c a s i l l ad e v e r i f i c a c i n . . . . . . . . . . . . . . . . . . .8 2 84 23. Admitir mltiples listas en un cuadro de lista despIegabIe...................... 24. Crear listas de validacin que cambien en base a la seleccin realizada . . . . . . . . . . . . . .8 6. .. e n o t r a l i s t a. . . . . . . . . . . 25. Forzar la validacin de datos para hacer referencia a una lista

e n o t r ah o j a . . . . . . . . . . .M t o d o 1 . R a n g o sc o n n o m b r e Mtodo 2. La funcin INDIRECTO d V e n t a j a sy d e s v e n t a j a s e c a d a m t o d o

. . . . . . . . . . . . .B.B . . .. . . . . . . . . . . . .B B . ......... BB . . . . . . . . . . . . . . . . . . . . .9 8.

2 6 . Utilizar Reemplazar para eliminar caracteres no deseados...................... 90 90 2 7 . Convertir nmeros de texto en nmeros reales 92 2 8 . Personalizar los comentarios de las celdas 94 2 9 . Ordenar ms de tres columnas . . . . . . . . . . . . . . . . . . .9 . .5 3 0 . O r d e n a c i na l e a t o r i a ............97 3 1 . Manipular datos con el filtro avanzado ........... 01 1 . 3 2 . Crearformatos de nmero personalizados........... ... "lO7 3 3 . Aadir ms niveles de Deshacera Excel....... . . . . . . . . . . . . . .".l.O 7 3 4 . C r e a rl i s t a sp e r s o n a l i z a d a s . . . . . . . . . . . . . ..... 1OB 3 5 . Subtotales en negritas de ExcelE l t r u c os o b r e l t r u c o . . . . . . . . . . . e 3. Convertir las frmulas y funciones de Excel a valores U t i l i z a rP e g a d e s p e c i a l otltilizar Copiar aqu slo valores

. . . . . . . . . . . 1.1 0 . . ...... 111 . . . . . . . . . . . . . . . . . . .1 . . 1 .1.777

.........112 Utilizar una macro . . 3 7 . A a d i r d a t o s a u t o m t i c a m e n t ea u n a l i s t a d e v a l i d a c i n . . . . . . . . . . . . . . . . . . . 1.1 3 de 38. Tiucar las caractersticas fecha y hora de Excel...... m sa l l d e l a s2 4 h o r a s. . . . . . . . . . SumarClculos de fecha v hora Horas v fechas reales ZUn fallo de fechas?

.......776 . . . . . . . . . . . . . . . . . 1.6 1. .777 ....... 119 779

Captulo 3. Trucos sobre nombres

........123

.............723 de 39. Usar direcciones datospor el nombre.............. 40. Utilizar el mismo nombre para rangos en diferenteshojas de clculo .. 724

12

Contenido

4"1 . 42. 43. 44.

Crear funciones personalizadasutilizando nombres 727 Crearrangosque seexpandan contraigan............... y 729 Anidar rangos dinmicospara obteneruna flexibilidadmxima.......... 7 3 6 Identificar rangos con nombre en una hoja de clculo 739Mtodo 1 Mtodo 2

739 747

Captulo4. Trucossobretablasdinmicas.45. Tablas dinmicas: truco en s mismas................ unZPorqu se les llama tablas dinmicasT ............

............. 143143 744 744

745 Los grficos dinmicos como extensin de las tablas dinmicas ....................... 4 5 7 Crear tablas y listas para ser utilizadas en tablas dinmicas El Asistente para tablas dinmicas y grficos dinmicos 745 747 748 150

ZPara qu cosas resultan buenas las tablas dinmicas? ZPor qu utilizar tablas dinmicas cuando las hojas de clculo ya ofrecen muchas funciones de anlisis?

46. Compartir tablas dinmicas pero no sus datos 47. Automatizar la creacin de tablas dinmicas

48. Mover los totales finales de una tabla dinmica............. 153 49. Utllizar de forma efectiva datos de otro libro dinmicamente ..............7 5 4

Captulo5. Trucossobregrficos

.......... 159

50. Separaruna porcin de un grfico circular .... 759 51. Creardos co4juntosde porciones un nico grficocircular.............767 en 52. Creargrficosque seajustena los datos ........"163Dibujar Ios ltimos x valores correspondientes a las lecturas ...... 166

53. Interactuarcon los grficosutilizandocontroles personalizados.......... 766Utilizar un rango dinmico con nombre vinculado a una barra d ed e s p l a z a m i e n t o . . . . . . . . . . . . . . . . . . . . .1.6 7 . Utilizar un rango dinmico con nombre vinculado a un cuadro de lista desplegable ..............169 5 4 . T i e s f o r m a s r p i d a sp a r a a c t u a l i z a r l o s g r f i c o s ................770 U t i l i z a ra r r a s t r a r c o l o c a r y .................'l7O U t i l i z a rl a b a r r ad e f r m u l a s . . . . . . . . . . . . .7 7 ' l .. A r r a s t r a re l r e ad e lb o r d e ...................174 55. Crearun simple grfico de tipo termmetro . . . . . . . . . . . . . . . . . . .7.7 5 . 5. Crear un grfico de columnas con anchos y altos variables .................1,78

Contenido

13

57. Crear un grfico de tipo velocmetro

...............782

188 58. Vincular los elementos de texto de un grfico a una celda ................... 59. Tiucar los datos de un grfico de forma que no se dibujen las celdas enblanco . O c u l t a rf i l a sy c o l u m n a s . . . . . . . . . . . . . . . . . . . . . . . .1 8 9 . 1 ....... 90

Captulo 6. Trucos sobre frmulas y funciones

.......193

193 .......... a 60. Aadir un texto descriptivo las frmulas............. 794 .......................... 61. Mover frmulas relativassin cambiarlas referencias ....'195 dos rangosde Excel 62. Cornparar

... '195 o Mtodo 1. Utilizar Verdadero Falso ^196 ............... Mtodo2. Utilizar el formato condicional . . . . . . .7 9 7 6 3 . R e l l e n a r o d a s l a s c e l d a se n b l a n c o e n u n a I i s t a . . . . . . . . . . . t ... "l9B Mtodo 1. Rellenar celdasen blanco medianteuna frmula Ias 799 las Mtodo2. Rellenar celdas blancoa travsde una macro ........................ en Hacer que las frmulas se incrementen por filas cuando las copie 64. ...........200 a l o l a r g o d e l a s c o l u m n a s. . . . . . . . . . . . 65. Convertir fechas en fechas con formato de Excel ...............202 ...............203 6. Sumar o contar celdasevitando valores de error las funciones voltiles a la hora de recalcular..... 205 7. Reducir el impacto de 68. Contar solamente una aparicin de cada entrada de una lista ..............206 ...2O7 69. Sumar cada dos, tres o cuatro filas o celdas 70. Encontrar Ia ensima aparicin de un valor .... 2O9 . 7 1 . H a c e r q u e l a f u n c i n s u b t o t a l d e E x c e ls e ad i n m i c a . . . . . . . . . . . . . ...... . . . . . . . . 2 1 2 .........274 d 72. Aadir extensiones e fecha negativo a la derecha a nmeros 73. Convertir nmeros con signo

d eE x c e l7 4 . Mostrarvalores hora negativos deMtodo 2. Utilizar la funcin TEXTO M t o d o 3 . U t i l i z a r u n f o r m a t o p e r s o n a l i z a d o. . . . . . . . . . . . . . . . .

..............276 .................21,8......................219 ............21'9

2'lB Mtodo 1. Cambiar el sistema de fecha predeterminado de Excel .....................

7 5 . Utilizar la funcin BUSCARV lo largo de mltiplestablas.................22O a 7 6 . Mostrar el tiempo total como das,horas y minutos

........222 .......223 .................225

que aparecen 7 7 . Determinar el nmero de das especificados e n c u a l q u i em e s . . . . . . . . . . . r7 8 . C o n s t r u i r e g af r m u l a s . . . . . . . . . . . . . m

14

Contenido

7 9 . Tiucar mega frmulas que hagan referencia a otros libros 8 0 . Tiucar una de las funciones de base de datos de Excel para que haga el trabajo de muchas funciones

227

228 237

Captulo 7. Trucos sobre macros......

81. Acelerarel cdigo y eliminar los parpadeos la pantalla de 237 82. $ecutar una macro a una determinadahora 238 para hacer referencias hoias en los libros 83. Utilizar CodeName a d eE x c e l ..............240 8 4 . Conectar forma fcil botonesa macros de .....241 B s .Crearuna ventanade presentacin un libro para ..............243 8 6 . Mostrarun mensaje "Porfavor,espere" de ............... .........246 8 7 . Hacerque una celdaquedemarcadao desmarcada seleccionarla al ......247 B B . Contaro sumar celdas que tenganun color de rellenoespecfico .........248 8 9 . Aadir el control Calendariode Microsoft Excela cualquier libro ........ 250 9 0 . Protegerpor contrasea desproteger y todas las hojas de clculorpidamente 9 7 . Recuperar el nombre y la ruta de un libro de Excel 9 2 . Ir ms all del lmite de tres criterios del formato condicional .. 9 3 . Ejecutar procedimientos en hojas protegidas 9 4 . Distribuir macros

2s2255 256 258 260 267 267 278 28B 293 295 296 296 297 300 301 307 307 307

CaptuloB. Conectando Excelconel mundo95. Cargar un documento XML en Excel 96. Guardar en SpreadsheetMl y extraer datos......... 97. Crear hojas de clculo utilizando SpreadsheetMl ............ 98. Importar datos directamente en Excel Ejecutarel truco El truco del trucoHacer que la consulta sea dinmica tltilizar datos diferentes

Resultados grficos con 99. Acceder a servicios Web SOAP desde Excel 100.Crear hojas de clculo Excel utilizando otros entornos Spreadsheet : :WriteExcei ... Spreadsheet: :ParseExcel

Contenido

15

CnprurcI

Reducir la frustracin en los libros y en las hoias de clculoTrucos I a 15

Los usuarios de Excel saben que los libros son un concepto muy potente. Pero igualmente, muchos usuarios son conscientes que trabajar con estos libros puede provocar un gran nmero de inconvenientes. Los trucos de este captulo le ayudarn a etar algunos de esos inconvenientes a la vez qu.esacarn 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 una aplicacin muy 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 reglaBO/2OQuiz la regla ms importante a seguir cuando se disea una hoja de clculo es tener una 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 BO%de su tiempo en planificar la hoja de clculo y alrededor del2Oo/o implementarla. Aunque en esto pueda parecer extremadamente ineficiente a corto plazo, podemos asegurar que a largo plazo ser una gran ventaja, adems de que despus de haber hecho varias planificaciones, luego ser mucho ms sencillo. Recuerde que las hojas de

24

Excel.Los mejorestrucos

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.

Trucossobrela estructuracinSin duda, el fallo nmero uno 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 una hoja de clculo: . Dispersininnecesariade los datos a lo largo de diferenteslibros. ' . . . Dispersin innecesariade los datos a lo largo de diferentes hojas de clculo. Dispersininnecesariade los datos a lo largo de diferentestablas. Tener filas y columnas en blanco en tablas con datos. Dejar celdasvacaspara datos repetidos.

Los tres primeros puntos de la lista tienen que ver con una cosa:siempredebe intentar mantener los datos relacionadosen una tabla continua. Una y otravez hemos podido ver hojas de clculo que no siguen esta simple regla y por tanto estn limitadas en su capacidadpara aprovechar por completo algunas de las funciones ms potentesde Excel,incluyendo las tablas dinmicas, los subtotales y las frmulas. En dichos escenarios, slo podr utilizar estasfunciones aprovechndolaspor completo cuando organice sus datos en una tabla muy sencilla. No esuna mera coincidencia que las hojas de clculo de Excelpuedan albergar 65.536 filas pero solamente256 columnas. Teniendoesto en mente, deberaconfigurar las tablas con encabezados columnas que vayan a lo largo de la primede ra fila y los datos relacionadosdistribuidos de forma continua directamente debajo de los encabezados apropiados.Si observaque estrepitiendo el mismo dato a lo largo de dos o ms filas en una de esascolumnas, evite la tentacin de omitir los datos repetidosutilizando celdasen blanco para indicar dicha repeticin. Asegresede que los datos estn ordenados siempre que sea posible. Excel dispone de un excelenteconjunto de frmulas de referencia, algunas de las cuales requieren que los datos estn ordenadosde manera lgica. Adems, la ordenacin acelerartambin el procesode clculo de muchas de las funciones.

Trucossobreel 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

e 1 . R e d u c i rl a f r u s t r a c i n n l o s l i b r o sy e n l a s h o j a sd e c l c u l o

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 tamao del libro y aunque steparezcauna verdaderaobra de arte, puedeparecerlehorrible a otra persona. Debeconsiderar la posibilidad de utilizar algunos colores universalespara sus hojas de clculo, como puedan ser el negro, el blanco y el gris. Siempre esuna buena idea dejar al menos tres filas en blanco por encima de la tabla (al menos tres, aunque es preferible dejar ms). Se pueden utilizar estas filas para insertar funciones de base de datos y de filtrado avanzado. Muchas personas tambin sepreocupan 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 sabersesi el contenido de una celda es un texto o un nmero. Es muy habitual encontrar gente que hace referencia a celdasque parecennmeros pero en realidad son texto. Si cambia la alineacin predeterminada, conseguir hacerseun lo. La nica excepcina esta regla pode dran ser los encabezados 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 deseabaera introducir un nmero una fecha. Peor an, cualquier celda que albergue una frmula que haga referencia a una celda con formato texto, tambin quedar formatearla como texto. Y normalmente, no desear que las celdascon frmulas estn formateadas as' Thmbin pueden crear problemas las celdas combinadas. La base de datos de conocimientos de Microsoft est repleta de problemas frecuentes que se encuentran en relacin a las celdas combinadas. Una buena alternativa es utilizar la opcin Centraren la seleccin,que se encuentra en el cuadro de lista desplegable Horizontalde la pestaaAlineacindel cuadro de dilogo Formatode celdas.

TrucossobrefrmulasOtro de los grandes errores que a menudo cometen los usuarios con las frmulas de Excel es hacer referencia a columnas enteras. Esto hace que Exceltenga que examinar en potencia miles, sino millones de celdas que de otra manera podra ignorar. Tomemos, por ejemplo, un caso en el que tiene una tabla con datos que se distribuyen desdela celdaA1 a la celda H1O0O.Puededecidir que deseautilizar una o ms frmulas de referencia de Excelpara 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 referenciaseralgo parecidoa A:H, o posiblemente :H5536. puede A1 utilizar esta referencia de forma que cuando se aaden nuevos datos a la tabla, sern referenciados en las frmulas automticamente. Esto resulta un hbito muy malo y siempre debera evitarlo. Todava puede eliminar la constante necesidad de actualizar las referenciasde las frmulas al incorporar nuevos datos que se aaden a la tabla utilizando nombres de rangos dinmicos, que veremos en uno de los trucos que presentaremosms 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 aparecen h pestaa Calculardel cuadro de dilogo Opciones. sin embargo, normalmente es un mal consejo,que puedeprovocar numerosos problemas. una hoja de clculo son todas las frmulas yilculos, as como Ios resultados que producen.Si utiliza una hoja de clculocon el modo de clculo manual, tarde o temprano leer alguna informacin que no haya sido actualizada. Puedeque las frmulas estn reflejando valores u.t1ig..or envezde los actua* lizados, porque cuando seutiliza el modo de clculo manual, debeforzar a Excel a que los realicepulsando la tecla F9. iPero es muy sencillo oldarse de hacer esto! Pinselode esta forma: si los frenos de su coche se estuviesendesgastandotanto que hiciesenque fuera ms lento, Zdesconectarael pedal del freno y utilizara f"etro de mano en vez de intentar arreglar el problema? Muchos de nosotros "l haramos algo as, pero no otras personasno tienen ningn inconvenienteen poner sus hojas de clcul,oen modo de clculo manual. Si tiene la necesidadde utilizar la hja de clculo en modo manual, entoncestiene un problema de diseo. Las frmulas matriciales son otra de las causascomunes de los problemas. Estn pensadaspara hacer referencia a celdas simples, pero si los utiliza para hacer referencia a grandes rangos, hgalo lo menos poiiute. cuando .,r, g"un nmero de colecciones hacen referencia a rangos extensos,el rendimiento det libro sever afectado,a veceshasta el punto en el que ni siquiera se puede utilizar y tiene que cambiar a modo de clculo manual. Las funciones de basede datos de Excelproporcionan muchas alternativas al uso de frmulas matriciales, como veremos ms adelanteen un truco. Adems, la ayuda de Excelofrecealgunos estupendosejemplosde cmo utilizar estasfrmulas en grandes tablas de datos para devolver ciertos resultados en base a mltiples criterios. Otra alternativa que a menudo es pasada por alto es la utilizacin de las tablas dinmicas de Exiel, que veremos n el.upit,rlo 4. Aunque las tablas dinmicas puedan parecer sobrecogedoras primeravez que seven, le recomendamos la encarecidamente que se familiarice con esta potente funcin de Excel, ya que cuando seaun 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 una tabla continua. Eso har que la utilizacin de los trucos sea mucho ms sencilla.

E

de de Crearuna vstapersonal los lbros ExcelExcelle permite mostrar varos libros abiertos simultneamentey por tanto presentarlosen una vista personalizadaorganzadaen diferentesventanas. Entonces,puede guardar el espacio de trabajo como un archivo .xlw y u t i l i z a r l o p o s t e r i o r m e n t ec u a n d o l o d e s e e .

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

\o\

la Desdecualquiera de los libros de Excel(no importa cul), seleccione opcin Si de men Ventana>Organizar. est activada la casilla de verificacin Ventanas la del libro activo, desactvelay luego seleccione organizacin que prefiera. Para terminar, haga clic en el botn Aceptar. Si eligi la opcin Mosaico,sele presentarnlos libros como un mosaico en la pantalla, tal y como puedeverseen la figura 1.1. la Si selecciona 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 uno al lado del otro, de izquierda a derecha,como puedeverse en la figura 1.3. la Por ltimo, como muestra la figura 1.4, seleccionando opcin Cascada se la mostrarn las ventanas unas encima de otras desde parte superior izquierda a la parte inferior derecha.llna vez que los libros se muestran de la forma que ms prefiera, puedecopiar, 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 trabajo. Para ello, simplemente seleccione la opcin de men rea de trabajo, introduzca el nombre de archivo en el cuadro Archivo>Guardar

2B


de dilogo correspondiente y haga clic en el botn Guardar. Cuando graba un rea de trabajo, la extensindel archivo ser .xlw e\vez de .xls. para recuperar un rea de trabajo de Excela una ventana completa de uno de los libros en particular, simplemente haga doble clic en la barra de ttulo de la ventana correspondiente. thmbin puede hacer clic en el botn de maximizar de cualquiera de las ventanas del rea de trabajo. lrna vez que haya acabado, puede cerrar los libros de Excel de la forma habitual.

$rchivo

Edicin

[er

lhsertar

formato

![erramient,rs

Dalos

Veqtana

l . '

) i i . : , : : i ' l i

J##SirArialn 4

.$v f *

tr&

- ; , ; g - ; s : i - , ' : , 'f f i S , r - t l i l= = = S $'x

& g q l r n u-'f' r . f f i "- , , 1 + - . f f i.,.iiir:.:!j

. lrl -, IiI K $

ig *n$ := :=

tji I I i I l

,

-.

.

..-. I j

tjI t I il l 1

i

Figura 1.1. Cuatro libros abiertos en vista mosaico.

Cuando necesitevolver 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 uno 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 cierr el rea de trabajo como conjunto, aunque tambin puede guardar cada libro de forma individual. 5i dedica una pequea parte de tiempo a configurar algunas stas personalizadas para realizar tareas repetitivas que requieren de mltiples libroi abiertos, encontrar que esastareas sern ms fciles de gestionar. Quiz decida utilizar diferentes stas para diferentes tareas repetitivas, dependiendo de cul sea la tarea o cmo se sienta eseda.


29

grchiw

fdicion

[er

lnserta'

formato

lBrramientas

Dalos

Veqtane

I

-j##3"*,C,Arial -10 -

tril! $ -i;S-;dH l{ S ===

i ' ' ' , i ' r , i " , m g p # E - * fi i l* p r ' : n- + f f i * f rS.$'"t"*A:\f$F:=::i:?'j3*.&-

rProtege>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.

B

lmpedir confrmacones innecesariasA veces,las interaccionesde Excelpuedan resultar pesadas:sempre preguntando para pedir confirmacin sobre accones.Quitemos estos mensaiesy dejemos que Excelrealice las acciones.

El tipo de mensajesa los que nos referimos son aquellos que preguntan si se deseanactivar las macros (incluso cuando no hay ninguna) o los que nos preguntan si estamossegurosde que queremos eliminar un hoja de clculo. A continuacin mostramos cmo evitar estostipos de mensajes.

Activar las macros cuando no se tenga ningunaLa memoria de Exceles de acero cuando se trata de recordar que ha grabado una macro en un libro. Por desgracia,Excelsigue recordando que se ha grabado una macro incluso si la ha eliminado utilizando la opcin Herramientas>Macro> Macros (Alt,zOpcin-F8).Despus hacer esto, si abre el libro de nuevo seguir de recibiendoun mensajeque le pregunta si deseaactivar las macros, incluso aunque no haya ninguna que activar.

3B


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 una 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 existealgn mdulo, estevaco o no. Cuando seeliminan las macros de un libro, slo se elimina el cdigo, pero no el mdulo en s (esalgo as como beberse toda la lechepero dejarseel bote vaco dentro de la nevera).Paraimpedir que se muestren estetipo de mensajesinnecesarios,debereliminar tambin el mdulo. As es como puede hacerse esto: Abra VBE seleccionandola opcin Herramientas>Macro>Editor Visual Basic (o pulsando AltlComando-F11) y de luego seleccionando opcin VeeExploradorde proyectos(en Macintosh, la venla tana de proyectos siempre est abierta, por lo que no necesitar abrir el explorador de proyectos). A continuacin podr ver una ventana como la que semuestra en la figura 1.8.il+-iS ,i S

GEuroTool (EUROTOOL.XLA) Y 8 A P r o i e c t( L i b r o l )El tfl : nt-,|'rt iHr-'j1i f fi5tl/r[..1r rr1[,: --, ,, _ri lultrrsfl: E:rrel t]ltrjelrs

t lrlrJuhrs d* t',jutnt

d*ilHiH

Mdulosabierta. Figura 1.8. Mdulosdel Explorador proyectos la carpeta de con 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 mdulos. Haga clic en el icono + de la carpeta Mdulos para obtener una lista de todos los mdulos. Haga clic con el botn derechodel ratn en cada mdulo y ellja la opcin Quitar mdulo. Cuando se le pregunte, rechacela opcin de exportar los mdulos. Antes de quitar los mdulos que pudieran tener cdigo til, haga doble clic en cada uno de ellos para asegurarse de que no los necesite. Al terminar, pulse AltlComando-Qpara volver de nuevo a la ventana principal de Excel.

Mensajes confirmacinpara guardarcambios de que no se han realizadoProbablementehabr observado que a vecesal abrir un libro y echar un stazo a su informacin es suficiente para que Excelle pregunte si deseaguardar los

1 . R e d u c i rl a f r u s t r a c i n n l o s l i b r o sy e n l a s h o j a sd e c l c u l o e

39

cambios en el libro de macro personal (aunque de hecho no ha realizado ninguno). Lo ms probable es que tenga una funcin impresible dentro del libro de macro personal. un libro de macro personal es un libro oculto que se crea la primera vez que graba una 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 impresibles ms comunes son Hoy o y Ahora O . Por tanto, aunque crea que no ha realizado cambios en el libro, puede que esasfunciones que se ejecutanen segundoplano s los hayan hecho. Esto cuenta como un cambio y hace que Excelle pregunte si deseaguardar dichos cambios. si deseaque Exceldeje de preguntar por aquellos cambios que no ha realizado, disponede 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 casode que necesiteutilizar funciones imprevisibles,puedeser utilizar estesencillocdigo para hacer que Excelcrea que el libro de macro personal ha sido guardado en el momento en el que se abre:Private End Sub Sub Me. Saved workbook_Open = True (

Estecdigo deberesidir en el mdulo privado del libro del libro de macro personal. Parallegar ah desdecualquier libro, seleccione opcin Ventana>Mostrar, la seleccione Personal.xls luego haga clic enAceptar. Luegoabra VBE e introduzy ca el cdigo anterior. Finalmente, pulse Alt/comando-Qpara volver a la ventana principal de Excelcuando haya terminado. Por supuesto, si dispone de una funcin imprevisible que quiere que searecalculada y por tanto guardar los cambios que haya realizado, entoncesintroduzca el siguiente cdigo:Private Sub workbook_Open Me. Save.E;NO SUj]

(

)

Esta macro guardar que sea abierto.

el libro de macro personal automticamente

cada vez

lmpedir los avisosde Excelpara macrosgrabadasUno de los muchos inconvenientes de las macros grabadas es que, aunque son muy tiles para reproducir cualquier comando, tienden a olvidar las res-

40


puestas a los avisos que se muestran en pantalla. Elimine una hoja de clculo y se le pedir confirmacin; ejecuteuna macro que realice esto mismo y todava se le pedir confirmacin. Veamoscmo desactivaresosasos. (AltlOpcin-F8 ) para mosSeleccione opcin Herramientas> la Macro>Macros trar un listado de todas las macros. Asegresede que est seleccionada opcin Todos los libros abiertos en el la cuadro de lista desplegablede la parte inferior. Seleccione macro en la que est la interesadoy haga clic en el botn Modificar. Coloque el cursor antes de la primera lnea de cdigo (la primera lnea que no tiene un apstrofe delante de ella) y escribalo siguiente:Applicat.ion. Displ-ayAlerts = False

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

Con lo que la macro entera quedara as:?"O MyMacro( )

' MiMacro Macro ttimina la hoja :

de clculo

ac tua I

Appl icat ion . Di splayAlert ActiveSheet. Delete Appl i cat ion . Di splayAlert End Sub

s = FaI se s = True

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 esosmensajesestn ah por una razn. Asegrese de que comprende completamente el propsito de estos mensajes antes de desactivarlos.


4',1

H

Ocultarhojasparague no puedanser mostradasA veces desearatener 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 recursosque se utilizan en las hojas pero que no desea que se vean.

Una prctica muy til cuando se configura un nuevo libro de Excel es reservar una 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 una hoja seleccionando la opcin Formato>Hoja>Ocultar, importante asegurarsede que los usuarios es no puedan volver a mostrarla seleccionando opcin Formato>Hoja>Mostrar. la Por supuesto, simplemente puede proteger la hoja, pero esto todava deja al descubiertolos 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. Envez de esto,jugaremos con la propiedad vi-sible de la hoja, establecindola en xlveryHidden. Desde VBE (Herramientas>Macro>EditorVisualBasic de o AltlOpcin-Fl 1), asegrese que la ventana de exploracinde proyectos est de visible seleccionando opcin Ver>Explorador proyectos.Encuentre el nomla de bre del libro y expanda su jerarqua haciendo clic en el icono + que aparece a la izquierda de su nombre. Expanda la carpeta MicrosoftExcel Objetos para mostrar todas las hojas del libro. Seleccione hoja que deseaocultar en el explorador de proyectos y muestre la (o sus propiedadesseleccionando opcin VepVentana Propiedades pulsando la la tecla F4). Asegresede que est seleccionada pestaa Alfabticay busque la la propiedad Visibleen la lista, que estar situada al final. Haga clic en el cuadro de texto que hay a su derechay seleccione ltima opcin: 2 - xlSheetVeryHidden, la tal y como se muestra en la figura 1.9. PulseAlt/Comando-Qpara guardar los cambios y volver a la ventana principal de Excel.A partir de ahora, la hoja ya no estar sible desdela interfaz de Excel e incluso tampoco podr mostrarse a travs de la opcin Formato>Hoja>Mostrar.Una vez que haya seleccionado la opcin 2 - xlSheetVeryHidden 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.

Para revertir el proceso,simplemente siga los pasosanteriores, pero esta vez seleccionando opcin 1 - xlSheetVisible. la

42


,f ,'i; E$ ruroto;a iEudorootJrt*) $ vn*eroiect (Libror) Excelbietas l.l t Micros':ft , I El Hojl (Hcjal) : : S] nnar {Hoja?} i fi rhisworkbc',:k i: i-:-$f"ldulr.rs I d* ttdul,:t ' rl r'oduh,f

#

Hoial

\orksheel: lRor categoras I

ltbtica

ageBreal-t False

ispl,:yRightToLeFl: FlsenableAutaFiller nbler-alculalion nblef-utlining vul:Tble Flse True False False

Figura 1.9. Ventana de propiedades de una hoja con la propiedad Visible establecida en 2 - xlSheetVeryHidden.

E

Personalizar cuadrode dlogo el Plantillas y el libro predeterminadoSi suele realizar las mismas tareas o utlizar las mismas distribuciones para hojas de clculo con frecuencia, puede construir su propa pestaa de p l a n t i l l a se n e l c u a d r o d e d i l o g o e s t n d a rP l a n t i l l a sp a r a p r o p o r c i o n a r u n acceso rpido al inicio.

Imagnese que tiene una hoja de clculo que contiene los das del ao y frmulas que resumen diversasinformaciones para esosdas. Ha formateado esta hoja de clculo cuidadosamentecon los coloresde su empresa,el logotipo y con las frmulas necesarias, todo ello para utilizarla con frecuencia.En vez de tener que reinventar la rueda (o copiar y eliminar las cosas que no necesita) cada da, puede ahorrar un montn de tiempo y de problemas creando una plantilla. Las plantillas para libros y hojas de clculo le proporcionan un punto de partida para los siguientes proyectos, permitindole saltarse la configuracin inicial, el proceso de dar formato, la construccin de frmulas, etc. Guardar una plantilla de una hoja de clculo simplemente significa abrir un libro nuevo, borrar todas las hojas excepto una y luego crear una plantilla bsica que ser la


43

que utilice. llna vez terminada, seleccionar opcin Archivo>Guardar la como y elegirla opcin Plantilladel cuadro de lista desplegable con los tipos de archivos posibles.Si la plantilla es de un libro (esdecir, que contendr ms de una hoja), entoncescreeun nuevo libro, haga todos los cambios necesariosy luego seleccione la opcin Archivo>Guardar como y gurdelo como una plantilla. Con la plantilla terminada, puede crear una copia exacta de la misma en cualquier momento seleccionandola opcin Archivo>Nuevoy luego seleccionando una plantilla de libro, o bien haciendo clic con el botn derecho en una pestaa de hoja y seleccionando opcin lnsertardesdeel men contextual para insertar la una nueva hoja a partir de una plantilla. ZNo sera interesante poder tener todas esasplantillas disponiblesdesdeel cuadro de dilogo estndar Plantillaso configurar su libro preferido como predeterminado? Puedehacer todo esto creando su propia pestaade plantillas.Este truco presupone que tiene una nica instalacin de Excel en su ordenador. Si dispone de mltiples copias o versiones de Excel, puede que no funcione.

Crearsu propapestaa plantillas deSi dispone de una serie de plantillas (tanto de libros como de hojas de clculo) que deseautilizar con regularidad, puede agruparlas todasjuntas en el cuadro de dilogo Insertar Plantillas. o Desdecualquier libro, seleccione opcin Archivo>Guardar la como y, desdeel la cuadro de lista desplegable tipos de archivo, seleccione opcin Plantilla(..xlt). de De forma predeterminada, Excel seleccionarla carpeta estndar Plantillas del disco duro en donde se almacenan todas las plantillas del usuario. Si no existe una carpeta llamada "Mis plantillas", cree una utilizando el botn Nueva carpeta. Luego, seleccione opcin Archivo>Nuevoen la barra de mens (en Excel la y 2OOO posteriores,seleccione Plantillasgeneralesen el cuadro de diIogo Nuevo libro. En Excel 2O03, debe seleccionarla opcin En mi PC del panel de tareas). Entonces,deberahaber una pestaa que representa la carpeta Mis plantillas que acaba de crear (vasefigura 1.1O).Tmbin deberaver las plantillas de libros y hojas de clculo que guarde en dicha carpeta.

Utilizar un libro personalzado forma predeterminada deAl iniciar Excel, se abre de forma predeterminada un libro en blanco llamado Librol, que contiene tres hojas en blanco. Esto est bien si deseacomenzar de nuevo cada vez que inicia Excel. Sin embargo, es probable que trabaje normal-

44


mente con un libro. Por tanto, resulta pesadotener que abrir Excely luego buscar el libro que sedeseaabrir. Si deseaconfigurar Excelpara que automticamente se inicie con un cierto libro abierto, siga leyendo.

5eleccilne un icono para r/Br una visla previa,

?Jfr: .-| *f: " -tPTfik:,J:"

i l,''''t;1-1 --,::1f:1"::.-.

Figura 1.10. El cuadro de dilogo Plantillas.

Para ello, guarde su libro predeterminado (plantilla) en la carpeta XLSTART (que normalmente se encuentra en la carpeta c:\Documents and settings\Nombre de usuario\Application Data\Microsoft\Excel\XlsTART en Windows y en la carpeta Applications,/Microsoft Office X/Office/Startup,/Excel en Macintosh). Una vez que haya hecho esto, Excelutilizar cualquiera de los libros que haya incluido en esta carpeta como predeterminados.La carpeta XLSTART es donde se crea y guarda automticamente el libro de macros personales cuando graba una macro. El libro de macros personales es un libro oculto. Lambin puede tener sus propios libros ocultos abiertos en segundo plano si lo desea, abriendo dicho libro, seleccionando la opcin Ventana>Ocultar, cerrando Excel y luego haciendo clic en S para guardar los cambios en 1.Luego coloque ese libro en la carpeta XLSTART Todos los libros que oculte y coloque dentro de la carpeta XLSTART se abrirn como libros ocultos cada vez que inicie Excel.

Evite la tentacin de colocar muchos libros en esta carpeta, especialmente si son grandes,dado que todos ellos se abrirn cuando inicie Excel.Si se tiene muchos libros abiertos se puede reducir considerablemente el rendimiento de Excel. Naturalmente si cambia de opinin y deseaque al iniciar Excel aparezca un libro en blanco, simplemente elimine los libros o plantillas de la carpeta XLSfART


45

,l,KEry

IB

Crearun ndicede hojasen el libroSi ha dedicado mucho tiempo en un libro que contiene muchas hojas, sabe perfectamente lo complicado que puede ser encontrar una hoja en particular. En estos casos,es imprescindible tener una hoja ndice para poder navegar por el resto de hojas de libro.

Utilizar una hoja de ndice le permitir explorar de forma rpida y sencilla el libro de forma que con un solo clic de ratn pueda ir directamente al lugar que desee. puede crear un ndice de dos formas. Podratener la tentacin de crear Se el ndice a mano. Cree una nueva hoja, llmela "ndice" o algo parecido, introduzca en ella una lista de todos los nombres de las hojas e incluya vnculos a o cada una de ellas mediante la opcin de men Insertar>Vnculo pulsando ControllComando-K. aunque este mtodo pueda ser suficiente en casos de los que no hay demasiadashojas y no hay muchos cambios, puede ser muy tedioso tener que mantener el ndice manualmente. El siguiente cdigo crear automticamente un ndice con vnculos a todas las hojas que estn incluidas en el libro. Estendice sevuelve a generar cada vez que la hoja que contiene el cdigo es activada. Estecdigo debera residir en el mdulo privado del objeto Sheet. Inserte una nueva hoja en el libro de Excel y llmela con algn nombre apropiado, como pueda ser "ndice". Luego haga clic con el botn derecho del ratn sbrela pestaa de dicha hoja y seleccionela opcin Ver cdigo. En la ventana de cdigo de Visual Basicescribalo siguiente:Private Sub Worksheet_Activate Dim wSheet As Worksheet Dim L As LongT. 1

(

)

With

Me . columns (1) . Cf earContents . C e l t s ( 1 , 1 ) = , , N D T C Ei l .ce11s (1, 1) .Name= "Indice" Eno watn For Each wsheet In worksheets If wsheet.Name Me.Name Then L = L + l With trsheet & !/sheet.Index .Range("A1 ').Name = "Inicioil Anchor:=.Range ( trA1"), Address: =" ",.Hyperlinks.Add al ndice" TextToDisplav:="vofver subAddresst=rrii..tr,F;nd $1t:n

Me.Hyperlinks.Add SubAddress:=nIn'cEJNO I I

Anchor:=Me.Cefls (1, 1), Address:="",o" & wsheet. Index, TextToDisplay:=wsheet.Name

Next.E;Nd SUjf

\rsheeE

46


Pulse AltlComando-Qpara volver al libro y guardar los cambios. Observe que el cdigo da el nombre "Inicio" (al igual que cuando da nombre a una celda o un rango de celdas en Excel) a la celda A1 de cada hoja, adems de un nico nmero que representa el nmero de ndice para dicha hoja. Esto asegura que la celdaA1 de cada hoja tiene un nombre diferente. Si la celdaA1 de la hoja ya tiene un nombre, debera considerar cambiar cualquier mencin a A1 en el cdigo por algo ms adecuado (por ejemplo, alguna celda no utilizada que est situada en cualquier parte de la hoja).Debe tener en cuenta que si seleccionala opcin Archivo>Propiedades> Resumen e introduce una direccin URL como vnculo base, el ndice que se crea por el cdigo anterior posiblemente no funcione. Un vnculo base es una ruta o LIRL que desea utilizar para todos los vnculos con la misma direccin base y que estn incluidos en el documento actual.

Otra forma de construir un ndice, que es ms sencilla para el usuario, es aadir un vnculo a la lista de hojas como un elemento de men contextual, al que se puede acceder haciendo clic con el botn derecho del ratn. Haremos que dicho vnculo abra el men estndar de hojas. Normalmente puede abrir este men haciendo clic con el botn derecho del ratn en cualquiera de los botones de desplazamiento que se encuentran a la izquierda de donde se muestran las solapas de cada hoja, tal y como se muestra en la figura 1.11.:+:li;ii''$J &rchivo fdicin Ver Y f * A 1 [nserlar formalo l[erranrienlas Dalos t/enlan t-

"l,nj-* - A x

pirn:Siyi*d.-iii jrFl:'-]Jffiei-llgli"Jr"l. ispe-Tff

t

Figura 1.L1. Men con las hojas disponiblesque se muestra al hacer clic con el botn derecho sobre los botones de desplazamiento entre hojas.

1. R e d u c i rl a f r u s t r a c i n n l o s l i b r o sy e n l a s h o j a sd e c l c u l o e

47

Para vincular ese men con el hecho de hacer clic con el botn derecho del ratn en cualquier celda, escribael siguiente cdigo en VBE:Byval Private Sh As Object, Sub V'lorkbook_SheetBeforeRightClick(Byval Target As Range, Cancel As Boolean) Dim ccont. As CommandBarButton On Error Resume Next ( " Indice Application. CommandBars ( "ceI1 " ) . controls de hoj as " ) . De1ete On Error GoTo 0 ("ce1l') .Controls.Add Se! ccont = Application.commandBars _ (Type : =msoControlButton, Temporary: =True ) Irith cCont. .Caption = "Indice de hojas" = 'r IndexCode " . OrrAction Eino wl tn E;no suD

A continuacin, deber insertar un mdulo estndar que almacene Ia macro que es llamada por este cdigo que acabamosde introducir en el "IndexCode", momento en el que el usuario hace clic con el botn derecho del ratn en una celda. Es fundamental que utilice un mdulo estndar a continuacin, ya que si coloca el cdigo en el mismo mdulo que el cdigo anterior, Excelno sabr dnde InsertapMdulov escriba encontrar una macro llamada "IndexCode". Seleccione el siguiente cdigo:Sub IndexCode ( ) Appl i cat ion. CommandBars ( " rlrorkbook Eino su.o Tabs " ) . Sho$tPopup

Pulse AltlComando-Qpara nuacin haga clic con el botn mento de men llamado "ndice de todas las hojas que contiene

volver a la ventana principal de Excel. A contiderecho en cualquier celda y ver un nuevo elede hojas", que al seleccionarlo mostrar un listado este libro.

t;ll:ilil

lis ,l:li \ t7

Limitarel rangode desplazamento la hoja de de clculoSi se desplazaa menudo por la hoja de clculoo si tiene datosque no desea que sean visualizadospor los lectores,puede ser til limitar el rea visible de la hoja de clculo slo al rango que actualmente tiene datos.

Todaslas hojas de Excelcreadasa partir de Excel97 disponen de 256 columnas (de la A a la IV) y de 65.536 filas. En la mayora de los casos,las hojas slo utilizarn un pequeo porcentaje de todas las celdasdisponibles. Existe la posibilidad de establecer rea por el que sepuede desplazarel usuario de forma que el Luego, puede colocar datos que no debenser slo puedaver los datos que desee.

4B


vistos fuera de esarea. Esto tambin puede hacer ms sencillo desplazarse por una hoja de clculo y que los usuarios no se encuentran en la fila 5o.ooo pra tener que empezar a buscar los datos que desea. La manera ms sencillapara establecer lmites es simplementeocultar tolos das las columnas y filas que no se utilizan. Estandoen una hoja, localicela ltima fila que contienedatos y seleccione fila entera que estdebajode ella haciendo la clic en el selector de fila. Mantenga pulsadas las teclas control y Mays mientras pulsa la tecla Flecha abajo para seleccionar todas las filas hacia abajo. Seleccione entonces la opcin Formato>Fila>ocultarpara ocultarlas todas. Haga esto mismo para las filas no utilizadas: busque la ltima columna, seleccione toda la columna siguiente y manteniendo pulsadaslas teclascontrol y Mays, pulse la tecla Flecha derecha hasta seleccionar todas las columnas. Luego seleccione la opcin Formato>Columna>ocultar. una vez hecho esto, el rutgo de celdas tiles quedar rodeadode una zona gris por la que no se puede desplazar. La segundaalternativa para establecer lmites es especificarun rango vlos lido en la ventana de propiedadesde la hoja. Haga clic con el botn derechoen la pestaa de la hoja que est situada en la parte inferior izquierdo de la ventana y luego seleccione opcin Ver cdigo. Entonces,seleccione opcin Ver>Explorador la la de proyectos(control-R en windows o Comando-R en Mac os X) para mostrar la ventana de proyectos. si la ventana de propiedadesno est visible, pulse la tecla F4. Seleccione hoja adecuaday busque la propiedad ScrollAreaen h rre.rla tana de propiedades(vase figura 1.12). Introduzca entonces en el cuadro de texto de dicha propiedad los lmites para la hoja (por ejemplo,$A$1:gcgso). una vez hecho esto, no podr desplazarse fuera del rea que haya especificado. Por desgracia,Excelno guarda esta configuracin despusde cerraise. Esto significa que necesitamosuna simple macro que automticamente establezca el rea de desplazamientoal rango deseado,escribiendoel cdigo para el evento worksheet_Activate. Para ello, haga clic con el botn derechosobre la pestaa de la hoja en la que desealimitar el desplazamientoy seleccione opcin Ver cdigo, introduciendo la a continuacin:Private Me . Scrol End Sub Sub lArea Worksheet_Activate = rrAl : G5 0 " (

Como siempre, pulse AltlComando-epara volver a la ventana principal de Excely guardar los cambios. Aunque en este caso no habr una indicacin clara, como pueda ser la zona gris que se mostraba con el primer mtodo, ser incapaz de desplazarse seleco cionar cualquier cosa fuera del rea especificada.

1 . R e d u c i r a f r u s t r a c i n n l o s l i b r o sy e n l a s h o j a sd e c l c u l o l e

49

rsGEuroTool (EUROTUOL.XLA) r, S vr*Proiect {Libro?} $, YBAProiect {Libro8} E.rcel bjel:s i-: ,--r f"li':r':srFt ffil H'-ritiHr'i1 El n'r':f rlHr-riIi E] t-toa:iHojasi rS tFir','uot['-rnk.

Hoia3 ',,orksheet Altahtica !rnr rateqariasIBrlEEf E,3h-{

tiisF |'/Fii,_tl-rtT Lef l: Ent'leAutFiller alculalir:rn

ivlTable

Figura 1 .12. Ventana de propiedades y del explorador de proyectos.

Cualquier macro que intente seleccionar un rango fuera de esta rea de desplazamiento (incluyendo la seleccin de filas o columnas enteras) no podr hacerlo. Esto es particularmente cierto para aquellas macros grabadas, que a menudo hacen uso de las selecciones.

Si las macros seleccionanun rango fuera del rea de desplazamiento,puede modificarlas de forma que no estn limitadas a dicha hara mientras realicen sus la tareas. Para ello, simplemente seleccione opcin Herramientas>Macro>Macros (Alt-F8), busque el nombre de la macro, seleccinela luego haga clic en el boy tn Modificar. Escrbala siguiente lnea de cdigo al principio del todo:ActiveSheet.ScrollArea = ""

Y al final del todo de la macro. escriba:Activesheet.scrollArea = "$A$1: $G$50'

Con esto, el cdigo de la macro quedara ms o menos as:SubI

MyMacro

(

50


MiMacro Macro

Macro grabada el L9 / 9 / 2003 by OzGrid. com

= u" Activesheet.S.tol1A."" Range ( ) .Select = True Selection.Font.Bold Activesheet.scrollArea = "$A$1-:$G$50' Sheets ( "Presupuesto diario,') . Sefect = u" ActiveSheet.ScroLlArea Range ( ).Select = False Sefection.Font.Bold ='$A$1 : $H$25" Activesheet.ScrollArea

Eno sulf

Nuestra macro selecciona la celda zloo y le da formato negrita. Luego selecciona la hoja llamada "Presupuesto diario", selecciona la celda T5OO de dicha hoja y quita el formato negrita. Hemos aadido ActiveSheet = ,rrl . ScrollArea de forma que pueda seleccionarse cualquier celda y ms adelante volvemos a establecer los lmites del rea de desplazamiento al valor deseado. Cuando selecciona amos otra hoja (Presupuesto diario), volvemos a permitir al cdigo seleccionar cualquier celda, y despus de que la macro realice sus tareas, volvemos a establecer el rango a los lmites deseados. Un tercer mtodo, el ms flexible, limita automticamente el rea de desplazamiento al rango que est siendo usado en la hoja en la que escribe el cdigo. Para utilizar este mtodo, haga clic con el botn derecho en la pestaa de la hoja en la que desea limitar el rea de desplazamiento, seleccione la opcin Ver cdigo y escriba lo siguiente:Private ( Sub Worksheet_Act.ivate ) = Range (Me.UsedRange, Me.UsedRange (2.2) ) .Address Me.ScrollArea Eino su-r)

Luego pulse AltlComando-Qo haga clic en el botn para cerrar la ventana de Visual Basicpara volver a la ventana principal y guardar los cambios. La macro anterior se ejecutar automticamente cada vez que se active la hoja en la cual introdujo estecdigo. sin embargo, puedeencontrarseun problema con esta macro cuando necesiteintroducir datos fuera del rea utilizable. Para evitar este problema, simplemente utilice una macro estndar que restablezca el rea de desplazamientode nuevo a toda la hoja. Para ello, seleccione la opcin Herramientas>Macro>Editor Visual Basic, seleccione de luego la opcin Insertar>Mdulo introduzca el siguiente cdigo: e( Sub ResetScrol-lArea ) ActiveShee!.S..oflOr." ino suD = u,'


51

Entonces pulse AltlComando-Qpara volver a la ventana principal de Excel y guardar el trabajo. Si lo desea,puede hacer que la macro sea fcilmente accesibleasignndole (Altl la rpido. Seleccione opcin Herramientas>Macro>Macros una tecla de acceso (el ResetScrollArea nombre que le dimos a la macro anteOpcin-F8), seleccione rpido. rior), haga clic en Opciones y luego asigneuna tecla de acceso Cadavez que necesiteaadir datos fuera de los lmites establecidosde la hoja, ejecute esta macro que quita dicha limitacin. Entonces haga aquellos cambios que no poda hacer cuando el lmite estaba establecidoy cuando haya terminado, active cualquier otra hoja y luego vuelva a activar sta para que se vuelva a limitar el rea de desplazamiento. La activacin de la hoja har que se ejecute el cdigo inicial que escribimos, el cual limitaba el rea de desplazamiento.

B

frmulas y Bloquear protegerceldasque contenenQ u i z d e s e e p e r m i t i r a l o s u s u a r i o sc a m b i a r c e l d a sq u e c o n t i e n e n d a t o s p e r o n o p e r m i t i r l e s c a m b i a r l a s f r m u l a s . P u e d e m a n t e n e r b l o q u e a d a sl a s c e l d a sq u e c o n t i e n e n f r m u l a s s i n t e n e r q u e p r o t e g e r t o d a l a h o j a o e l l i b r o .

Cuando creamosuna hoja de clculo, muchos de nosotros necesitamosutilizar frmulas de algn tipo. A veces,sin embargo, no desearque otros usuarios puedan estropear,eliminar o sobrescribir cualquiera de las frmulas incluidas en Ia hoja de clculo. La forma ms fcil y rpida de impedir que las personasjueguen con las frmulas es proteger la hoja de clculo. Sin embargo, proteger la hoja de clculo no slo evita que los usuarios estropeen las frmulas, sino que tambin evitan que se pueda introducir cualquier informacin. Y a vecesno querr ir tan lejos en la seguridad. De forma predeterminada, todas las celdasde una hoja de clculo estn bloqueadas,aunque esto no tiene efecto hasta que se aplique la proteccin de la misma. A continuacin mostramos un mtodo muy sencillo para aplicar una proteccin a la hoja de clculo de forma que slo las celdas con frmulas estn bloqueadasy protegidas. Seleccionetodas las celdas de la hoja, bien pulsando Control/Comando-E o bien pulsando el cuadrado gris situado en la interseccin de la columna A y la y fila 1. Entoncesvaya a Formato>Celdas>Proteger desactivela casilla de verificacin Bloqueada.Haga clic en Aceptar. Edicin>lra (Control-I F5) y Ahora seleccione cualquier celda, seleccione haga clic en eI botn Especial. Ver entonces un cuadro de dilogo como el que semuestra en la figura 1.13. el Seleccione botn de opcin Celdas con frmulas del cuadro de dilogo lr a especial y, si es necesario,limite las frmulas a los tipos subyacentes.Luego

52


haga clic en Aceptar. Una vez estn seleccionadas celdascon las frmulas, las vaya a Formato>Celdas>Proteger active la casilla de verificacin Bloqueada. y Haga clic enAceptar. Ahora seleccione opcin Herramientas>Proteger>Proteger la hoja para proteger la hoja de clculo y utilizar una contrasea si es requerid., :i,,..,5elecciunar f f,gmentarins fl snslanies $ f-tldg--qg-f-L l7 HgerosIt Tegto

f-J

f' DiFerencias enlre filag fl SiFerencias entre rolurnnes{!J {- celdas srecedentes f' detdas,J+pendientesfl f* {* f f* jrj:;i--i :,.:,ir:ri-?: :i.r.:. ..',,,.. ,"..;.:,:-,i''i::: -ii;,r':l r l',.,,, r:ir.i,:i ,'f r",'

ffi valores lgrrrs l? f* l* g,rrores

klrn relda sdb cel,Jas visibles {'$ Celds con lormatrs condicionales de ,J,alos

celdas en [lanco Begin artual

'{*' t4,3trit actual f' bietos

f* CeldaE con vlidacidn ul? , -, ,.{. :iilr:lir;:i,:r

. . ; : . l t - , : t: : i . i : t . , ; ,

f-lllrrry*l _fr:l:p { Figura 1.13. El cuadrode dilogoIr a especial. Este mtodo realmente ahorra gran cantidad de tiempo y elimina posibles errores al buscar las frmulas, de forma que pueda protegerlas. por desgracia, tambin evita que los usuarios puedan utilizar otras funciones, como puede ser ordenar, cambiar el formato, alinear el texto y otras muchas, incluso iuando la celdano estbloqueada.Puedesolucionar esteproblema de dos formas: La primera aproximacin consiste en no utilizar la proteccin de la hoja, sino la validacin de datos en lugar de ello.La validacin de datos est lejos de ser del todo segura a la hora de evitar que los usuarios introduzcan datos no vlidos en celdas. Los usuarios todava pueden pegar en una celda con validacin cualquier tipo de dato, quitando la validacin de dicha celda a menos que la celda original que se est copiando tambin tuviese algn tipo de validacin, en cuyo caso tambin se estara sobrescribiendo.

Para ver a qu nos referimos, seleccione cualquier celda,seleccione opcin la Edicin>lra y luego haga clic en el botn Especial.Ahora seleccione opcin la celdas con frmulas en el cuadro de dilogo y, si es necesario,especifiquque tipos de frmulas desea buscar. Haga clic en el botn Aceptar. Ahora que slo tenemos seleccionadas celdascon frmulas, seleccione las la opcin Datos>Validacin en la pestaa Configuracin y seleccione opcin Perla sonalizadaen el cuadro de lista desplegabley en el cuadro de texto Frmulaes,


53

criba :"", tal y como se muestra en la figura 1.14. Luego haga clic en el botn Aceptar.iliifiifontiguracin de I nens"* entrante I mensae error N Criteriode validacidn

-xJ

{? nmtir blanc,:s

|_,,,,t-

** . *rsl?tii I l--+r-t l -.Jryr:ls :l

Figura 1.14. Frmulas de validacin.

Este mtodo evitar que un usuario sobrescriba accidentalmente una celda que tenga una frmula (aunque como dijimos anteriormente, no es un mtodo totalmente seguro y slo debera ser utilizado para evitar sobrescribir accidentalmente). De todas formas, la gran ventaja de utilizar estemtodo es que todas las funciones de Excel todava se pueden utilizar en la hoja de clculo. El ltimo mtodo tambin permite utilizar todas las funciones de Excel, pero solamente cuando se encuentra una celda que no estbloqueada. Para empezar, proteger estnbloqueadasy que asegrese que solamentelas celdasque desea de el resto no lo estn. Haga clic con el botn derechodel ratn en la pestaa de la la hoja en cuestin, seleccione opcin Ver cdigoe introduzca el siguientecdigo:Private If (ByVa1 Target Sub Worksheet_SelectionChange = True Then Target.Locked Password : =t'Secreta" Me. Protect P a s s r 4 r o r d: = " S e c r e t a " As Range)

-E;-LSe

Me. Unprotect Eino r rEJNd SUlf

Si no deseautilizar una contrasea, omita la parte Password : =trSecretart. Si por el contrario quiere utilizar una entonces debe cambiar la palabra "Secreta" cierre la venpor aquella contrasea que desee. Luego pulse AltlComando-Qo tana para volver a Excel y guardar los cambios. Ahora, cada vez que seleccione una celda que est bloqueada, la hoja se bloquear a s misma automticamente. En el momento en el que seleccione cualquier celda que no est bloqueada, la hoja se desbloquear.

54


Este truco no funciona perfectamente, aunque normalmente funciona lo suficientemente bien. La palabra clave utilizada en el cdigo, Target, slo se refiere a la celda que est activa el momento de la seleccin. Por esta razrt, es importante destacar que si el usuario selecciona un rango de celdas (con la celda activa estando desbloqueada), puede eliminar la seleccione entera porque la celda objetivo estaba desbloqueada y, por tanto, la hoja se ha desprotegido automticamente a s misma.

G{t

l.J li!i{ii)I l ) l v

E

Encontrar datosduplcados utlizando formato el condicionalEf formato condicional de Excelse utiliza normalmente para identificar valores en rangos en particular, pero podemos usar un truco con esta caractersticapara identificar datos duplicados dentro de una lista o una tabla.

Normalmente la gente tiene que identificar datos duplicados dentro de una lista o tabla. Hacer esto manualmente puede llevar mucho tiempo y a vecesse pueden cometer errores. Le aconsejamosque para hacerlo mucho ms sencillo, utilice un truco sobre una de las caractersticasestndar de Excel, el formato condicional. Tomemos, por ejemplo,una tabla con datosen el rango $A$1:$H$100.Seleccione la celdasuperior izquierda (A1) y arrastre el cursor del ratn hasta la celda H1oo. Esimportante queAl seala celdaactiva en la seleccin, por lo que no eslo mismo seleccionarprimero la celda H1OOy luego arrastrar hasta la celdaA1 . Seleccione entonces opcin Formato>Formto la condicionaly,en el cuadro de dilogo Formato condicional,seleccione opcin Frmula en el primer cuadro de lista la desplegable. el campo que hay a su derecha,introduzca el siguiente cdigo: En= C o N T A R . I ( g A g l : g H g 1 0 0; 4 1 ) > 1 S

Haga clic en el botn Formato... y luego seleccione pestaaTramasy selecla cione un color que deseeaplicar para identificar visualmente los datos duplicados. Haga clic en Aceptar para volver al cuadro de dilogo anterior y vuelva a hacer clic en Aceptar para aceptar el formato. Todasaquellas celdasque contengan datos duplicados deberan aparecer ahora como un rbol de Navidad con el color que eligi, haciendo mucho ms sencillo el hecho de localizar datos duplicadospara as poder eliminarlos, moverlos, o alterarlos. Es muy importante comentar que como la celda A1 era la activa, la direccin de la celda es una referencia relativa y no absoluta, como en la tabla de datos,

1. R e d u c i rl a f r u s t r a c i n n l o s l i b r o sy e n | a s h o j a sd e c l c u l o e

55

$A$1:$H$100. Utilizando el formato condicional de esta forma, Excel sabe automticamente que debe utilizar la celda correcta como el criterio de la funcin coltaR. SI. Con esto queremos decir que la frmula de formato condicional en la celdaA1 se leera as:=CoNTAR. I ( $A$1; $H$100;A1 ) >lS

Mientras que en la celda A2, se leera:=CoNTAR. I ($A$1 : $H$100;A2 ) >1 S

En la celda A3 se leera:=CoNTAR. r ($A$1 : $H$100;A3 ) >1 s

y as sucesivamente. Si necesitaidentificar datos que aparecendos o ms veces,puede utilizar el formato condicional con tres condiciones diferentes y cdigos de color para cada una de las condiciones, todo ello para conseguir una identificacin visual. Para la hacer esto, seleccione celda A1 (la celda que est situada en la parte superior izquierda de la tabla) y arrastre el cursor del ratn hasta la celdaH1OO.De nuevo, es importante que la celda A1 sea la celda activa en la seleccin. la condicionaly seleccione opla Ahora seleccione opcin Formato>Formato En cin Formatoen el cuadro de lista desplegable. el cuadro de texto situado a su derecha,introduzca el siguiente cdigo:= C O N T A R .r ( $ A $ 1 : $ H $ 1 0 0 ; A 1 ) > 3 s

Haga clic en el botn Formato... y luego vaya a la pestaaTramaspara seleccionar el color que deseeaplicar para identificar los datos que aparecen ms de tres veces.Haga clic en Aceptar, luego haga clic en el botn Agregar y en el la para la Condicin 2, seleccione opcin Frmulay cuadro de lista desplegable luego escriba el siguiente cdigo en el cuadro de texto situado a su derecha:= C o N T A R . r ( $ A $ 1 : g H g 1 0 0 ; A l _ )= 3 S

A \* \ *

En vez de tener que reescribir la frmula, mrquela en el cuadro de para texto de la Condicin 1, pulse la tecla Control,/Comando-C copiarla en el portapapeles, haga clic en el cuadro de texto de la para pegarla ah y luego Condicin 2, pulse Control,/Comando-V cambie>3 Dor =3.

la Haga clic en el botn Formato... y luego seleccione pestaa Tramas para utilizar para identificar los datos que aparecenjustaseleccionarel color que desee

56


mente tres veces.Haga clic en Aceptar y luego haga clic en Agregar. En el cuadro de lista desplegable la Condicin 3, seleccione opcin Frmulay escriba de la lo siguiente en el cuadro de texto situado a su derecha:= C o N T A R . I ( $ A $ 1 : g H g l - 0 0; A ] - ) = 2 S

Para terminar, haga clic en el botn Formato..., elija la pestaa Tramas y seleccione ah el color que desea aplicar a los datos que aparecen exactamente dos veces. Luego haga clic en el botn Aceptar. Ahora ya tenemos colores diferentes de celda dependiendo del nmero de veces en el que aparecen los datos dentro de la tabla. De nuevo, es importante recordar que la celda A1 debe ser la celda activa en la seleccin, puesto que la direccin de celda es una referencia relativa y no absoluta, como en la tabla de datos, $A$1:$H$100. Utilizando el formato condicional de esta forma, Excel sabr utilizar la celda correcta como criterio de la funcin coNTAR.sr.

LJtl?

n

\:./

Jt;,,it

Asociar barras herramientas de personalizadas a un libro en partcularA pesar de que la mayora de barras de herramientasque cree sirven para prcticamente todos los libros con los que trabaje, a veces la funconalidad de una barra de herramientas personalizadasolamente es aplicable a un libro en particular. Con este truco podremos asociar barras de herramientas personalizadas sus respectivoslibros. a

si nunca ha creado una barra de herramientas personarizada, sin duda se habr dado cuenta que las barras herramientas se cargan y son visibles independientemente de que libro tenga abierto. ZQu ocurre si su barra de herramientas personalizada contiene macros grabadas que slo tienen sentido con un libro en particular? Probablemente es mejor poder asociar barras de herramientas personalizadas cuyo propsito sea especial con los libros apropiados, para as evitar cualquier tipo de confusin y otros problemas. Podemos hacer esto insertando un cdigo muy sencillo en el mdulo privado del libro. Para acceder al mdulo privado, haga clic con el botn derecho en el icono de Excel, que encontrar en la esquina superior izquierda de la pantalla, cerca del men Archivo, y luego seleccione la opcin Ver cdigo.Este acceso directo no est disponible en Macintosh. Tendr que abrir el Editor de Visual Basic pulsando Opcin-Fl1 o bien seleccionando la opcin de men Herramientas>Macro>Editorde Visual Basic. Una vez ah, haga clic con el botn derecho del ratn (o clic mientras mantiene pulsada la tecla Control) en ThisWorkbook, que aparece en la ventana de proyectos.

u-\

\


57

Entonces,introduzca estecdigo:( Private Sub Workbook_Activate ) On Error Resume Next With Appl icat ion . CommandBars ( " MiBarraPersonal .Enabled = True = True .Visibfe Einct w lcn On Error GoTo 0 L;no :juD ( Private Sub Workbook_DeactivaEe ) On Error Resume Next Application. CommandBars ( "MiBarraPersonalizada" On Error GoTo 0 -E;ncl suj]

i zada " )

) . EnabLed = False

Cambie el texto de "MiBarraPersonalizada" por el nombre que desee darle a la barra de herramientas personalizada. Para volver a la ventana principal de Excel, En cuanto abra o active cierre la ventana de mdulo o pulse Alt,/Comando-Q otro libro, la barra de herramientas personalizada desaparecer y no podr ser utilizada. Reactivando el libro adecuado, la barra volver a aparecer. Todava podemos llegar ms lejos, haciendo que una barra de herramientas personalizada est disponible solamente para una hoja en particular del libro. Haga clic con el botn derecho del ratn sobre el nombre de una hoja en la que desea activar la barra de herramientas, seleccionando la opcin Ver cdigo. Entonces introduzca el siguiente cdigo:( Private Sub Worksheet_Deactivate ) Resume Next On Error commandBars ( t'MiBarraPersonaf ApplicaEon. on Error GoTo 0 ind sul]

izada " ) . Enabf ed = False

( Private Sub Worksheet_Activate ) On Error Resume Next commandBars ( "MiBarraPersonalizada" With Application. . Enabled = True = True .VisibfeE;nd wltn

)

On Error Eno :'ujf

GoTo 0

Ahora pulse AltlComando-Qo cierre la ventana para volver a Excel. El primer procedimiento ({orksheet_Deactivate O ) seejecutarautomticamente cada vez que deje hoja en particular y active otra. Este cdigo cambia la propiedad Enabf ed de la barra de herramientas personalizadaa Fa1se, de forma que no pueda ser vista o utilizada. El segundoprocedimiento se ejecutacadavez que

5B


se activa la hoja en cuestin, estableciendo la propiedad Enab]ed a True, con lo que la barra vuelve a ser visible. La lnea de cdigo que dice Application. CommandBars ( "MyCustomTool_bar" ) .Visibl_e = True simplemente mues_ tra la barra de herramientas de nuevo, de forma que el .,rrraiio pueda verla. Cambie de una hoja a otra y ver como la barra de herramientas desaparece y vuelve a aparecer dependiendo de la hoja que tenga seleccionada.

B

Burlarel gestorde referencias relativas Excel deEn Excel,una referencia de frmula puede ser o bien relativa o bien absoluta, pero a vecesdesear mover celdas que utilicen referenciasrelativassin tener que hacer las referencasabsolutas.Veamos cmo hacer esto.

Cuando se necesita hacer una frmula absoluta, se utiliza el signo del dlar ($) delante de la letra de la columnay/o del nmero de la fila en la referencia a la celda, como por ejemplo en $A$1. [Ina vez haya hecho esto, no importa dnde copie la celda que la frmula seguida haciendo referencia a la misma celda o celdas. A veces, sin embargo, ya ha creado numerosas frmulas que no contienen referencias absolutas, sino relativas. Normalmente hace esto cuando desea copiar la frmula original o propagarla por la hoja y que Excel cambie las referencias de forma adecuada. Si ya ha creado las frmulas utilizando solamente referencias relativas, o quizs utilizando una mezcla entre referencias absolutas relativas, puede reproducir las mismas frmulas en cualquier otro rango y en la misma hoja, en otra hoja del mismo libro, o incluso en otra hoja situadL en otro libro. Para hacer esto sin cambiar ninguna de las referencias que hay dentro de las frmulas, seleccione el rango de celdas que desea copiar y luego seleccione la opcin Edicin>Reemplazar. En el cuadro de texto Buscar, introduzca el signo = y en el cuadro de texto Reemplazar con el smbolo @ (por supuesto, puede utilizar cualquier otro smbolo que est seguro no se utiliza en cualquiera de las frmulas). Luego haga clic en el botn Reemplazar todos. El signo :de todas las frmulas de la hoja ser reemplazado con el smbolo @. Ahora puede copiar este rango y pegarlo en el destino que desee. Luego seleccione el rango que acaba de pegar y vuelva a seleccionar la opcin Edicin>Reemplazar. Esta vez reemplace el smbolo @ por el smbolo =. Con esto, las frmulas quedarn con las mismas referencias que las originales.

l I ii!:l tig liil

f-l

Quitar vnculos"fantasma"en un libroAh! Vnculos fantasmas.Al abrir un libro se le pregunta si desea "actualizar los vnculos", pero no hay ningn vnculo! Cmo puede actualizar los vnculos cuando no exsten?

@9

Los vnculos externos son vnculos que hacen referencia a otros libros. El hecho de que seproduzcan vnculos externos no esperadospuede darse por diferen-

l 1 . R e d u c i r a f r u s t r a c i n n l o s l i b r o sy e n l a s h o j a sd e c l c u l o e

59

tes razones, la mayora de las vecespor mover o copiar grficos, hojas de grficos u hojas a otros libros. An as, saber dnde estn no siempre le ayudar a encontrarlos. A continuacin le mostramos algunas alternativas para solucionar el problema de los vnculos fantasmas. Primeramente, debe comprobar si realmente tiene vnculos externos (que no son fantasmas), de los cualesnos olvidaremos. Si no est seguro, si tiene vnculos externosreales,comiencebuscandoen el lugar ms obvio: las frmulas. Puede de hacer esto asegurndose que no hay otros libros abiertos y entoncesbuscando por [*] dentro de las frmulas de cada hoja. Cierre todos los dems libros para asegurarsede que cualquier vnculo de frmula incluir [*], donde el asterisco representael carcter comodn de bsqueda.Excel 97 no proporciona una opcin parabuscar en todo el libro, pero puede buscar en todas las hojas de un libro si las agrupa. Puede hacer esto haciendo clic con el botn derecho en el nombre de cualquiera de las hojas y eligiendo la opcin Seleccionar todas las hojas. En versiones posteriores de Excel, la funcin de Buscar y reemplazar admite la posibilidad de buscar dentro de una hoja o de todo un libro.

ffi;r,, Eiii,,i : a

L*"'\

\

tlna vez que haya encontrado los vnculos de frmula, simplemente cambie la frmula de forma adecuada o bien elimnela. Cambiar o eliminar la frmula dependede la situacin y debeser usted el que decida qu hacer.Tmbin puede ir de al centro de descargas Microsoft Office, ubicado enhttp://office.microsoft.com/ y Downloads,zdefault.aspx desdela categora de complementos, seleccionarel Asistentede eliminacin de vnculos. Esteasistenteestdiseadopara encontrar y eliminar vnculos tales como vnculos de nombres definidos, vnculos de nombres ocultos, vnculos a grficos, vnculos a consultas de Microsoft y vnculos a no objetos.De todas formas, por nuestra experiencia, es capazde encontrar vnfantasmas. Una vez que est seguro de que no hay vnculos de frmula, culos debe asegurarse de que no tiene ningn otro vnculo que no sea fantasma. Para ello, solemoscomenzar desdeel libro de Excelque contiene los vnculos fantasa Desplcese lo largo de la la mas. Seleccione opcin Insertar>Nombre>Definir. nombres seleccionandocada uno de ellos y mirando en eI cuadro de texto lista de Se refiere a situado en la parte inferior. Asegresede que ninguno de estos nombres est haciendoreferenciaa un libro diferente.En vez de tener que hacer clic en cada nombre, puede insertar una nueva hoja y seleccionar la opcin Insertar>Nombre>Pegar. Luego, desde el cuadro de dilogo, haga clic en Pegar vnculo. Esto crear una lista de todos los nombres de libro, con sus rangos referenciados en la columna correspondiente.

60


Si alguno de los nombres se refiere a un elemento que est fuera de libro, ha encontrado el origen de al menos uno de los vnculos a los cuales hace referencia el mensaje de actualizar los vnculos. Ahora es decisin suya si desea cambiar e_ste nombre de rango para que haga referencia solamente al propio libro, o bien dejarlo como est. Otra fuente potencial de vnculos son sus grficos. Es posible que los grficos tengan el mismo problema que acabamos de explicar. Debera comprobar que los rangos de datos y las etiquetas del eje X del trfico no estn haciendo referencia a libros externos. De nuevo, debe tomar la decisin de si los vnculos que ha encontrado son o no correctos. Los vnculos tambin pueden acechar en los objetos, como puedan ser dos cuadros de texto, las autoformas, etc. Los objetos pueden intentar hacer referencia a un libro externo. La forma ms sencilla de localizar los objetos es seleccionar cualquier celda de cada hoja y luego seleccionar la opcin Edicin>lr a (F5). Desde el cuadro de dilogo, haga clic en el botn Especial y luego active la casilla de verificacin objetos, y haga clic en Aceptar para comenrarlabsqueda. con esto seleccionaremos todos los objetos de la hoja. En cualquier caso, debera hacer esto en una copia del libro. Despus, una vez tengamos todos los objetos seleccionados, puede eliminar, guardar, cerrar y volver a abrir la copia del iibro para ver si con esto hemos solucionado el problema. Finalmente, el lugar que no es tan obvio comprobar es en las hojas ocultas que puede haber creado y de las que se ha olvidado. Vuelva a mostrar esas hojas seleccionando la opcin Formato>Hoja>Mostrar. si la opcin Mostrar eita desactivada, significa que no hay hojas ocultas. Ahora que ya ha eliminado la posibilidad de vnculos reales, es hora de eliminar los vnculos fantasmas. vaya al libro en cuestin en el que haya vnculos fantasmas y seleccione la opcin Edicin>Vnculos. A veces simplemente puede seleccionar los vnculos no deseados, hacer clic en cambiar origen y luego hacer que el vnculo haga referencia a s mismo. A menudo, de todas forms, se le informar que alguna de las frmulas contiene un error y entonces no podr hacer esto. si no puede tomar el camino sencillo, apntese a qu libro de Excel cree que puede estar vinculado (llamaremos a ese libro "el libro bueno"). Cree un vnculo real entre ambos, abriendo los dos. vaya al libro problemtico y, en cualquier celda de cualquier hoja, escriba =. Ahora haga clic en una celda del libro bueno y pulse Intro para tener un vnculo externo real con dicho libro. Guarde ambos libros, pero no los cierre todava. Estando todava en el libro con vnculos fantasmas, seleccione la opcin Edicin>Vnculos y utilice el botn cambiar origen para referenciar todos los vnculos con el mrevo libro con el que acabamoi de crear el vnculo. Guarde el libro de nuevo y elimine la celda en la que cre el vnculo real. Para terminar, guarde el archivo.


61

A menudo esto elimina el problema de los vnculos fantasmas, dado que ahora Excel es consciente de que ha eliminado el vnculo externo con dicho libro. Si esto no ha solucionadoproblema, pruebe con los siguientespasos,pero asegresede guardar una copiaprimero.El siguiente proceso implica eliminar datos permanentemente. Por tanto, antes de comertzar, tiene que crear una copia de seguridad del libro de Excel, ya que si no lo hace, puede crearle nuevos problemas.

Con el libro problemtico abierto, elimine una hoja, guarde el libro, cirrelo y vuelva a abrirlo. Si al hacerlo no se le pregunta sobre actualizar los vnculos perdidos, entonces es que la hoja que acaba de eliminar era la que tena los vnculos fantasmas. Esto deberaarreglar el problema, pero si no fuera as, repita el primer paso para cada una de las hojas del libro. Necesitaraadir una nueva hoja antes de eliminar la ltima, ya que cualquier libro debetener al menos una hoja. Supongamos que esta tcnica ha funcionado. Entoncsveamos qu hay que hacer a continuacin. Abra la copia del libro (la que todava tiene los datos dentro de ella) y cree otra copia. Deber trabajar con la hoja problemtica (u hojas) y utilizar el procesode eliminacin para descubrir dnde est el problema. Teniendo activada la hoja problemtica, seleccioneun grupo de celdas (de alrededor de 1Ox1O)y entonces seleccione opcin Edicin>BorrapTodo.ZEst la totalmente seguro de que ha guardado una copia? Si esas, guarde, cierre y vuelva a abrir la hoja. Si no sele pregunta acercade actualizar los vnculos, entonces es que ha encontrado el problema y el objetivo servolver a restablecereseconjunto de celdas.Si vuelve a recibir el mensajede actualizar los vnculos, contine eliminando celdashasta que ya no se le pregunte. Luego, tendr que restaurar las celdasproblemticas. Esperamosque estastcnicasle hayan solucionadoalgo la frustracin de tener vnculos fantasmas en sus libros de Excel. No es sencillo ni divertido, pero puedeayudarle a solucionar el problema.

H

Reducir libro que esthinchado un N u n c ah a o b s e r v a d oq u e u n l i b r o e s t a u m e n t a n d o d e t a m a o a u n r i t m o alarmante sin una razn aparente?Existennumerosascausasque pueden provocarlo y algunas solucionespara ello.

ZNunca ha comido tanto que no puede funcionar correctamente? Lo mismo ocurre con las hojas de Excel. Un libro de Excel que engorda tanto es aquel en el que se han hecho tantas cosas que ha llegado a un tamao tal en el que ya no puedefuncionar correctamente.

62


Comprobamos el tamao de un libro tpico que contena gran cantidad de datos y observamos que, slo con datos, el tamao del archivo era de 1,37 ly','b. Entonces aadimos una tabla dinmica que haca referencia a cuatro columnas enteras como origen de los datos y observamos que el tamao del archivo se increment espectacularmente hasta los 2,4 ly''b. Aada algunos formatos y el tamao del libro se incrementar prcticamente al doble slo haciendo algunas acciones. una de las causas ms comunes por las que un archivo engorda, particularmente en las versiones anteriores de Excel, es la aplicacin de formatos a columnas o filas completas ervez de a un rango de datos que se est utilizando. Otro error es referenciar columnas enteras como fuente de datos para grficos y tablas dinmicas, en vez de referenciar solamente las celdas que actualmente contienen datos. Para solucionar estos problemas, tendremos que eliminar todos los formatos superfluos y restringir el origen de datos solamente a aquellos rangos de celdas tiles.Antes de hacer estos arreglos, haga siempre una copia del libro por seguridad.

Eliminar Formatos superfluosEl primer paso para eliminar formatos superfluos es averiguar cul es la esquina inferior derecha.enla que acabarn los datos. No se confe en la opcin Edicin>lr a>Especial>ltma celda,ya que puedellevarle a la ltima celdaque contieneformato, no datos. Habiendo localizadomanualmente la celdaque sabe que es la ltima que contiene datos reales, marque la fila que est inmediatamente debajo de ella. Mientras pulsa las teclas Control y Mays, pulse la tecla Flecha abajo del teclado para marcar todas las filas que se encuentran por debajo. Luego seleccione opcin Edicin>BorrapTodos la para eliminarlas. Ahora utilice el mismo mtodo para eliminar todas las columnas sobrantes. Localicela celda que se encuentre ms a la derechay que an contenga datos y seleccione columna inmediatamente posterior. Luego, mientras mantiene pulla sadaslas teclasControl y Mays, pulse la tecla Flecha derecha del tecladopara seleccionar todas las columnas hasta el final, y finalmente, seleccione opcin la de men Edicin>Borrar>Todos.Evite la tentacin de eliminar por completo todas esas filas que columnas en vez de borrarlas, ya que al hacerlo, se produce normalmente errores de tipo #REF! en cualquiera de las celdas con frmulas que pudiesen estar haciendo referencia a ellas.


63

Guarde el libro y tome buena nota del cambio producido en su tamao, a travs de la opcin Archivo>Propiedades y mirando en la pestaa General. Si tiene macros, ahora tendr que dirigir los mdulos en los que reside el cdigo de las macros. Este ser un proceso rpido, directo y sencillo, que exporta todos los mdulos y formularios de usuario al disco duro, que luego los elimina y vuelve a importarlos de nuevo. Para hacer esto, vaya al Editor de Visual Basic y desde el explorador de proyectos, haga clic con el botn derecho en cada mdulo y seleccione la opcin Quitar mdulo1 (o cualquier otro nombre que tuviese el mdulo). Cuando se le pregunte si desea exportar el mdulo antes de quitarlo, diga S, teniendo en cuenta la ruta que seleccione. Haga esto mismo para cada mdulo, as como para todos los formularios de usuario que pueda tener. No se olvide de los mdulos privados del libro y hojas si stos contienen cdigo. llna vez que haya hecho todo, guarde el libro. Entonces seleccione la opcin Archivo>lmportar archivo e importe cada mdulo y cada formulario de usuario de nuevo al libro. Siguiendo este proceso se crear un archivo de texto para cada mdulo, lo cual eliminar cualquier residuo que pudiesen contener. Existen en Internet algunas utilidades gratis que pueden automatizar esta tarea en cierto grado, pero hemos odo casos en los que estas herramientas han desorganizado el cdigo o incluso han aumentado ms el tamao de los archivos. En caso de que utilice una de estas herramientas, siempre haga una copia de seguridad primero, ya que los desarrolladores no se harn responsables de cualquier prdida de datos.

Puesta punto de los orgenes datos a deSi despus de llevar a cabo los pasos preos todava cree que el tamao del archivo es muy grande, otra causa posible es que hay referencias a celdas no utilizadas dentro de tablas o grficos dinmicos. Esto suele ocurrir normalmente con las tablas dinmicas, ya que muchas personas hacen referencia a las 5.53 filas para etar tener que actualizar manualmente los rangos cada vez que se aaden nuevos datos.

LimpiarlibroscorruptosSi todava cree que el tamao del libro es demasiado grande, es posible que el libro o sus hojas estn corruptos. Por desgracia,encontrar el punto de corrupcin requiere de un proceso manual de eliminacin.De nuevo le recomendamos encarecidamente que haga una copia de seguridad antes de proceder.

64


Paraasegurarse que no sepierde nada, muestre todas las hojas que puedan de estar ocultas seleccionando opcin Formato>Hoja>Mostrar. caso de que la la En opcin estdesactivada,es que no tiene hojas ocultas por las que preocuparse. Teniendotodas las hojas visibles, comiencepor la hoja que estms a la izquierda y vaya recorriendo una a una hacia la derecha. Para cada una de ellas, elimnela, guarde el libro y anote el tamao del mismo a travs de Archivo>Propiedades> General.Si el tamao del archivo sereducedrsticamenteconsiderandola cantidad de datos que haba en dicha hoja, entonces es que probablemente haya encontrado el punto de corrupcin. Para reemplazar una hoja corrupta de un libro, creeuna nueva hoja, seleccione manualmente los datos situados en la hoja corrupta, corte (no utilice nunca copiar) y peguedichos