los mejores trucos (anaya multimedia)

GELtOS NE.IORES TRUGOS

/,rN/Y/,rO'REILLY' oaYtd y nahn thuw

Contenido

ZPor qu los mejores trucosCmo obtener v utilizar los

de Excel?trucos

7 71 B1 B7 9202 7

23

2 32424252 730303 1333 33 6

Cmo utilizar este libroCmo est organizado este l ibro.. . . . . . . . . .Usuarios de Windows y Macintosh.. . . . . . . . . . . . .Convenciones utilizadas en este libro . . ........ .

Captulo 1. Reducir lafrustracin en los l ibrosyen las hojas de clculo.. . .L a r e g l a B O / 2 0 . . .Tiucos sobre la estructuracinTiucos sobre el formatoTiucos sobre frmulas . . . . . . . . . . . . . . .

1. Crear una vista personal de los l ibros de Excel ..........2. Introducir datos en varias hojas de clculo simultneamente.................

Agrupar hojas de clculo manualmente ..................Agrupar hojas de clculo automticamente ............

3. Impedir que los usuarios realizan ciertas accionesImpedir el comando Guardar como en un libro de ExcelImpedir que los usuarios impriman un libro de Excel

1 0 Conten ido

4 .Impedir que los usuarios inserten ms hojas de clculoImpedir confirmaciones innecesariasActivar las macros cuando no se tenga ningunaMensajes de confirmacin para guardar cambios que no se han realizado .Impedir los avisos de Excel para macros grabadasOcultar hojas para que no puedan ser mostradasPerson alizar el cuadro de dilogo Plantillas y el libro predeterminado . ...Crear su propia pestaa de plantillastrtilizar un libro personalizado de forma predeterminadaCrear un ndice de hojas en el libroLimitar el rango de desplazamiento de la hoja de clculoBloquear y proteger celdas que contienen frmulas .Encontrar datos duplicados utilizando el formato condicionalAsociar barras de herramientas personalizadas a un libro

en particularBurlar el gestor de referencias relativas de ExcelQuitar vnculos "fantasma" en un libro ..Reducir un libro que est hinchadoEliminar formatos superfluosPuesta a punto de los orgenes de datosLimpiar l ibros corruptos ..............

15. Extraer datos de un libro corruptoSi no puede abrir un l ibro ............Si no puede abrir el archivo

1. Validar datos en base a una lista situada en otra hoja ...........Mtodo 1. Rangos con nombreMtodo 2. La funcin INDIRECTOVentajas y desventajas de ambos mtodos

1 7. Controlar el formato condicional con casillas de verificacin ...... .. . . . . .....Configurar casillas de verificacin para formato condicionalActivar o desactivar el resaltado de los nmeros................

18. Identificar frmulas con el formato condicional19. Contar o sumar celdas que se ajustan al criterio del formato condicional .

Una a l ternat iva. . . . . . . . . . . . . . .20. Resaltar Filas o columnas impares

3 63 73 73 B3 94 7424343454 75 754

5 65 B5 B6 7626 36 364646 5

5 .6 .

7 .B .9 .1 0 .7 7 .

72 .1 3 .7 4 .

Captulo 2. Trucos sobre las caractersticas incorporadas en Exce|.............. G96 96 9707 77 77 77275767 77B

Conten ido

21. Crear efectos en 3D en tablas o celdas . . . . . . . . . . . . . . B0Ut i l izar un efecto 3D en una tabla de datos. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

22. Activar y desactivar el formato condicional y la validacin de datoscon una casi l la de ver i f icac in . . . . . . . . . . . . . . . . . . . 82

23. Admitir mltiples l istas en un cuadro de l ista despIegabIe...................... 8424. Crear listas de validacin que cambien en base a la seleccin realizada

e n o t r a l i s t a . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 625. Forzar la validacin de datos para hacer referencia a una lista

e n o t r a h o j a . . . . . . . . . . . . . . . . . . . . . . . . . . . . B BMtodo 1 . Rangos con nombre . . . . . . . . . . . . . . BBMtodo 2. La funcin INDIRECTO ... . . . . . . BBVenta jas y desventa jas de cada mtodo . . . . . . . . . . . . . . . . . . . . . . 89Uti l izar Reemplazar para el iminar caracteres no deseados... . . . . . . . . . . . . . . . . . . . 90

1 1

2 6 .2 7 .28 .2 9 .30 .3 1 .3 2 .3 3 .34 .3 5 .

Convertir nmeros de texto en nmeros realesPersonalizar los comentarios de las celdasOrdenar ms de tres columnasOrdenacin a leator ia . . . . . . . . . . . . . . . . . . . . . 95Manipular datos con el f i l tro avanzado ............ 97Crear formatos de nmero personal izados . . . . . . . . . . . . . . . . . . . . . . . 101Aadir ms niveles de Deshacer a Excel ....... ... "lO7Crea r l i s t as pe rsona l i zadas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . " lO7Subtotales en negritas de Excel ..... 1OBE l t r uco sob re e l t r uco . . . . . . . . . . . . . . . . . . . . . . . . . 110

3. Convertir las frmulas y funciones de Excel a valores ...... 111Ut i l izar Pegado especia l . . . . . . . . . . . . . . . . . . . . . . . 111tltilizar Copiar aqu slo valores

909294

7 7 7Util izar una macro ......... 112

37. Aadir datos automt icamente a una l is ta de val idacin. . . . . . . . . . . . . . . . . . . . . . 11338. Tiucar las caractersticas de fecha y hora de Excel ...... .......776

Sumar ms a l l de l as 24 ho ras . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116Clculos de fecha v hora 7 7 7Horas v fechas reales .. . . . . . 119ZUn fallo de fechas? 779

Captulo 3. Trucos sobre nombres ........123

39. Usar direcciones de datos por el nombre . . . . . . . . . . . . . . . . . . . . . . . . . . .72340. Utilizar el mismo nombre para rangos en diferentes hojas de clculo .. 724

1 2 Conten ido

4"1 . Crear funciones personalizadas utilizando nombres42. Crear rangos que se expandan y contraigan ...............43. Anidar rangos dinmicos para obtener una flexibilidad mxima..........44. Identificar rangos con nombre en una hoja de clculo

Mtodo 1Mtodo 2

45. Tablas dinmicas: un truco en s mismas.. . . . . . . . . . . . . . .ZPor qu se les l lama tablas dinmicasT .. . . . . . . . . . .ZPara qu cosas resultan buenas las tablas dinmicas?ZPor 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 dinmicasEl Asistente para tablas dinmicas y grficos dinmicos

46. Compartir tablas dinmicas pero no sus datos47. Automatizar la creacin de tablas dinmicas48. Mover los totales finales de una tabla dinmica.............49. Utll izar de forma efectiva datos de otro l ibro dinmicamente ..............

143744744

7457457457 4 77481 5 01 5 3754

7 2 77297 3 6739739747

Captulo 4. Trucos sobre tablas dinmicas. ............. 143

Captulo 5. Trucos sobre grficos .......... 15950. Separar una porcin de un grfico circular .... 75951. Crear dos co4juntos de porciones en un nico grfico circular .............76752. Crear grficos que se ajusten a los datos ........ "163

Dibujar Ios ltimos x valores correspondientes a las lecturas ...... 16653. Interactuar con los grficos utilizando controles personalizados.......... 766

Utilizar un rango dinmico con nombre vinculado a una barrade desp lazamien to . . . . . . . . . . . . . . . . . . . . . . . 167

Utilizar un rango dinmico con nombre vinculado a un cuadro de listadesp legab le . . . . . . . . . . . . . . 169

54. T ies formas rpidas para actual izar los grf icos . . . . . . . . . . . . . . . .770Ut i l izar arrastrar y colocar . . . . . . . . . . . . . . . . . ' l7OUt i l izar la barra de frmulas . . . . . . . . . . . . . . . 77 ' lArrastrar e l rea del borde . . . . . . . . . . . . . . . . . . .174

55. Crear un s imple grf ico de t ipo termmetro . . . . . . . . . . . . . . . . . . . . . 7755. Crear un grfico de columnas con anchos y altos variables ................. 1,78

Contenido 1 3

57. Crear un grfico de tipo velocmetro ............... 78258. Vincular los elementos de texto de un grfico a una celda ................... 18859. Tiucar los datos de un grfico de forma que no se dibujen las celdas

en b lanco . . . . . . . . . . . . 189Ocul tar f i las y columnas . . . . . . . . . . . . . . . . . . . . . 190

Captulo 6. Trucos sobre frmulas y funciones ....... 193

60. Aadir un texto descr ipt ivo a las frmulas . . . . . . . . . . . . . . . . . . . . . . . 19361. Mover frmulas relativas sin cambiar las referencias .......................... 79462. Cornparar dos rangos de Excel .... '195

Mtodo 1. Utilizar Verdadero o Falso ... '195

Mtodo 2. Util izar el formato condicional ............... ^196

63. Rel lenar todas las celdas en b lanco en una I is ta. . . . . . . . . . . . . . . . . . 797Mtodo 1. Rellenar Ias celdas en blanco mediante una frmula ... "l9BMtodo 2. Rellenar las celdas en blanco a travs de una macro ........................ 799

64. Hacer que las frmulas se incrementen por filas cuando las copiea l o l a rgo de l as co lumnas . . . . . . . . . . . . . . . . . . . . . . . 200

65. Convertir fechas en fechas con formato de Excel ...............2026. Sumar o contar celdas evitando valores de error ...............2037. Reducir el impacto de las funciones voltiles a la hora de recalcular..... 20568. Contar solamente una aparicin de cada entrada de una lista ..............20669. Sumar cada dos, tres o cuatro fi las o celdas ...2O770. Encontrar Ia ensima aparicin de un valor .... 2O971. Hacer que la funcin subtota l de Excel sea d inmica . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21272. Aadir extensiones de fecha . . . . . . . . .27473. Convertir nmeros con signo negativo a la derecha a nmeros

74.d e E x c e l . . . . . . . . . . . . . . 2 7 6

Mostrar valores de hora negat ivos . . . . . . . . . . . . . . . . .21,8Mtodo 1. Cambiar el sistema de fecha predeterminado de Excel . . . . . . . . . . . . . . . . . . . . . 2' lBMtodo 2 . Ut i l i za r la func in TEXTO . . . . . . . . . . . . . . . . . . . . . .219Mtodo 3 . Ut i l i za r un fo rmato persona l izado . . . . . . . . . . . . . . . . . . . . . . . . . . . . .21 '9Utilizar la funcin BUSCARV a lo largo de mltiples tablas .................22OMostrar el tiempo total como das, horas y minutos ........222Determinar el nmero de das especificados que aparecen

en cua lqu ie r mes . . . . . . . . . . . . . . . . . .223Const ru i r mega f rmulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .225

7 5 .7 6 .7 7 .

7 8 .

1 4 Conten ido

el trabajo de muchas funciones

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

81. Acelerar el cdigo y eliminar los parpadeos de la pantalla82. $ecutar una macro a una determinada hora83. Utilizar CodeName para hacer referencias a hoias en los libros

d e E x c e l . . . . . . . . . . . . . . 2 4 0Conectar de forma fcil botones a macros .....241Crear una ventana de presentacin para un libro ..............243Mostrar un mensaje de "Por favor, espere" . . . . . . . . . . . . . . . . . . . . . . . .246Hacer que una celda quede marcada o desmarcada al seleccionarla ......247Contar o sumar celdas que tengan un color de relleno especfico .........248Aadir el control Calendario de Microsoft Excel a cualquier libro ........ 250Proteger por contrasea y desproteger todas las hojas de clculo

7 9 .80 .

8 4 .B s .8 6 .8 7 .B B .8 9 .9 0 .

9 7 .9 2 .9 3 .9 4 .

Tiucar mega frmulas que hagan referenciaTiucar una de las funciones de base de datos

a otros libros 227de Excel para que haga

228

2372 3 7238

2s22 5 52 5 6258260

267

2 6 72 7 82 8 B2 9 32 9 52 9 62 9 62 9 73003 0 13 0 73073 0 7

rpidamenteRecuperar el nombre y la ruta de un libro de ExcelIr ms all del lmite de tres criterios del formato condicional ..Ejecutar procedimientos en hojas protegidasDistribuir macros

Captulo B. Conectando Excelcon el mundo95. Cargar un documento XML en Excel96. Guardar en SpreadsheetMl y extraer datos.........97. Crear hojas de clculo uti l izando SpreadsheetMl ............98. Importar datos directamente en Excel

Ejecutar el trucoEl truco del truco

Hacer que la consulta sea dinmicatltilizar datos diferentesResultados con grficos

99. Acceder a servicios Web SOAP desde Excel100.Crear hojas de clculo Excel utilizando otros entornos

Spreadsheet : : WriteExcei ...Spreadsheet: :ParseExcel

Conten ido 1 5

Cnprurc I

Reducir la frustracinen los libros y en las hoias

de clculoTrucos I a 15

Los usuarios de Excel saben que los libros son un concepto muy potente. Peroigualmente, muchos usuarios son conscientes que trabajar con estos libros pue-de provocar un gran nmero de inconvenientes. Los trucos de este captulo leayudarn a etar algunos de esos inconvenientes a la vez qu.e sacarn provechode algunos mtodos ms efectivos, pero en ocasiones desconocidos, con los quepuede controlar sus libros de trabajo.

Antes de profundizar en dichos trucos, merece la pena echar un vistazo rpi-do a algunos conceptos bsicos que harn mucho ms sencillo crear trucos efec-tivos. Excel es una aplicacin muy potente de hojas de clculo, con la que sepueden hacer cosas increbles. Por desgracia, muchas personas disean sus hojasde clculo de Excel con poca previsin, haciendo difcil que puedan reutilizarlas oactualizarlas. En este apartado, proporcionaremos numerosos trucos que puedeutilizar para asegurarse de que crea hojas de clculo lo ms eficaces posibles.

La regla BO/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 aadirms datos o frmulas a la hoja de clculo, ya que la probabilidad de que ocurraesto es alta. Teniendo esto en mente, deber dedicar alrededor del BO% de su tiem-po en planificar la hoja de clculo y alrededor del2Oo/o en implementarla. Aunqueesto pueda parecer extremadamente ineficiente a corto plazo, podemos asegurarque a largo plazo ser una gran ventaja, adems de que despus de haber hechovarias 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 porparte de los usuarios, no slo para presentarla y que tenga buen aspecto.

Trucos sobre la 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 lainformacin en la manera en la que Excel y sus caractersticas esperan. A conti-nuacin, y sin ninguna orden en particular, mostramos algunos de los fallosms comunes que cometen los usuarios cuando organizan una 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 una cosa: siempre debe

intentar mantener los datos relacionados en una tabla continua. Una y otravezhemos podido ver hojas de clculo que no siguen esta simple regla y por tantoestn limitadas en su capacidad para aprovechar por completo algunas de lasfunciones ms potentes de Excel, incluyendo las tablas dinmicas, los subtotalesy las frmulas. En dichos escenarios, slo podr utilizar estas funciones aprove-chndolas por completo cuando organice sus datos en una tabla muy sencilla.

No es una mera coincidencia que las hojas de clculo de Excel puedan albergar65.536 filas pero solamente 256 columnas. Teniendo esto en mente, debera con-figurar las tablas con encabezados de columnas que vayan a lo largo de la prime-ra fila y los datos relacionados distribuidos de forma continua directamente debajode los encabezados apropiados. Si observa que est repitiendo el mismo dato a lolargo de dos o ms filas en una de esas columnas, evite la tentacin de omitir losdatos repetidos utilizando celdas en blanco para indicar dicha repeticin.

Asegrese de que los datos estn ordenados siempre que sea posible. Exceldispone de un excelente conjunto de frmulas de referencia, algunas de las cualesrequieren que los datos estn ordenados de manera lgica. Adems, la ordena-cin 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. Reduci r la f rust rac in en los l ibros y en las hojas de clculo 2 5

de la eficiencia. Somos grandes creyentes de "mantenerlo todo sencillo", aunquemuchas personas dedican grandes cantidades de tiempo a formatear sus hojas declculo. Aunque no se den cuenta, este tiempo frecuentemente suele ser a costade la eficiencia. La sobrecarga de formatos hacen que aumente el tamao dellibro y aunque ste parezca una verdadera obra de arte, puede parecerle horriblea otra persona. Debe considerar la posibilidad de utilizar algunos colores univer-sales para sus hojas de clculo, como puedan ser el negro, el blanco y el gris.

Siempre es una buena idea dejar al menos tres filas en blanco por encima de latabla (al menos tres, aunque es preferible dejar ms). Se pueden utilizar estasfilas para insertar funciones de base de datos y de filtrado avanzado. Muchaspersonas tambin se preocupan por cambiar la alineacin de las celdas. De formapredeterminada, los nmeros en Excel se alinean a la derecha y los textos a laizquierda, y realmente existen buenas razones para dejarlo as. Si empieza a cam-biar estos formatos, resultar que no podr saberse si el contenido de una celdaes un texto o un nmero. Es muy habitual encontrar gente que hace referencia aceldas que parecen nmeros pero en realidad son texto. Si cambia la alineacinpredeterminada, conseguir hacerse un lo. La nica excepcin a esta regla po-dran ser los encabezados de las columnas.

De formato texto a las celdas slo cuando sea completamente necesario, yaque todos los datos que se introduzcan en dichos celdas se convertirn en texto,incluso si lo que deseaba era introducir un nmero una fecha. Peor an, cual-quier celda que albergue una frmula que haga referencia a una celda con for-mato texto, tambin quedar formatearla como texto. Y normalmente, no desearque las celdas con frmulas estn formateadas as'

Thmbin pueden crear problemas las celdas combinadas. La base de datos deconocimientos de Microsoft est repleta de problemas frecuentes que se encuen-tran en relacin a las celdas combinadas. Una buena alternativa es utilizar laopcin Centrar en la seleccin, que se encuentra en el cuadro de lista desplegableHorizontal 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 fr-

mulas de Excel es hacer referencia a columnas enteras. Esto hace que Excel tengaque examinar en potencia miles, sino millones de celdas que de otra manera po-dra ignorar.

Tomemos, por ejemplo, un caso en el que tiene una tabla con datos que sedistribuyen desde la celda A1 a la celda H1O0O. Puede decidir que desea utilizaruna o ms frmulas de referencia de Excel para extraer la informacin requerida.Dado que la tabla continuar creciendo (a medida que aadan nuevos datos), eshabitual hacer referencia a toda la tabla, que incorpora todas las filas. En otras

2 6 Excel. Los mejores trucos

palabras, la referencia ser algo parecido a A:H, o posiblemente A1 :H5536. puedeutilizar esta referencia de forma que cuando se aaden nuevos datos a la tabla,sern referenciados en las frmulas automticamente. Esto resulta un hbitomuy malo y siempre debera evitarlo. Todava puede eliminar la constante nece-sidad de actualizar las referencias de las frmulas al incorporar nuevos datos quese aaden a la tabla utilizando nombres de rangos dinmicos, que veremos enuno de los trucos que presentaremos ms adelante.

Otro problema tpico que surge en las hojas de clculo malamente diseadases el reclculo tremendamente lento. Mucha gente sugiere cambiar el modo declculo a manual, a travs de la opcin que aparece n h pestaa Calcular delcuadro de dilogo Opciones.

sin embargo, normalmente es un mal consejo, que puede provocar numero-sos problemas. una hoja de clculo son todas las frmulas yilculos, as comoIos resultados que producen. Si util iza una hoja de clculo con el modo de clculomanual, tarde o temprano leer alguna informacin que no haya sido actualiza-da. Puede que las frmulas estn reflejando valores u.t1ig..or envezde los actua*lizados, porque cuando se utiliza el modo de clculo manual, debe forzar a Excela que los realice pulsando la tecla F9.

iPero es muy sencillo oldarse de hacer esto! Pinselo de esta forma: si losfrenos de su coche se estuviesen desgastando tanto que hiciesen que fuera mslento, Zdesconectara el pedal del freno y utilizara

"l f"etro de mano en vez de

intentar arreglar el problema? Muchos de nosotros no haramos algo as, perootras personas no tienen ningn inconveniente en poner sus hojas de clcul,o enmodo de clculo manual. Si tiene la necesidad de utilizar la hja de clculo enmodo 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 parahacer referencia a grandes rangos, hgalo lo menos poiiute. cuando .,r, g"unnmero de colecciones hacen referencia a rangos extensos, el rendimiento detlibro se ver afectado, a veces hasta el punto en el que ni siquiera se puede utili-zar y tiene que cambiar a modo de clculo manual.

Las funciones de base de datos de Excel proporcionan muchas alternativas aluso de frmulas matriciales, como veremos ms adelante en un truco. Adems,la ayuda de Excel ofrece algunos estupendos ejemplos de cmo utilizar estas fr-mulas en grandes tablas de datos para devolver ciertos resultados en base a ml-tiples criterios.

Otra alternativa que a menudo es pasada por alto es la utilizacin de las ta-blas dinmicas de Exiel, que veremos n el.upit,rlo 4. Aunque las tablas dinmi-cas puedan parecer sobrecogedoras la primeravez que se ven, le recomendamosencarecidamente que se familiarice con esta potente funcin de Excel, ya quecuando sea un maestro, se preguntar cmo pudo sobrevivir sin ellas.

1. Reduc i r la f rus t rac in en los l ib ros y en las ho jas de c lcu lo 2 7

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 relacionadosestn distribuidos en una tabla continua. Eso har que la utilizacin de los tru-cos sea mucho ms sencilla.

Crear una vsta personal de los lbros de ExcelExcel le permite mostrar varos l ibros abiertos simultneamente y por tantopresentarlos en una vista personalizada organzada en diferentes ventanas.Entonces, puede guardar el espacio de trabajo como un archivo .xlw yut i l izar lo poster iormente cuando lo desee.

A veces, trabajando con Excel, puede que necesite tener ms de un libro abier-to en Ia pantalla, lo que permite utilizar sualizar los datos de mltiples librosde forma fcil y rpida.

En los siguientes prrafos describiremos cmo hacer esto de una forma orga-nizada y ordenada. Abra todos los libros que desee utilizar.

E

\o \

Para abrir ms de un libro a la vez, seleccione la opcin Archivo>Abrir,mantenga pulsada la tecla Control mientras selecciona los libros quedesea abrir y finalmente haga clic en el botn Abrir.

Desde cualquiera de los libros de Excel (no importa cul), seleccione la opcinde men Ventana>Organizar. Si est activada la casilla de verificacin Ventanasdel libro activo, desactvela y luego seleccione la organizacin que prefiera. Paraterminar, haga clic en el botn Aceptar.

Si eligi la opcin Mosaico, se le presentarn los libros como un mosaico en lapantalla, tal y como puede verse en la figura 1.1.

Si selecciona la opcin Horizontal, se distribuirn los libros de arriba a abajoocupando 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, deizquierda a derecha, como puede verse en la figura 1.3.

Por ltimo, como muestra la figura 1.4, seleccionando la opcin Cascada semostrarn las ventanas unas encima de otras desde la parte superior izquierda ala parte inferior derecha. llna vez que los libros se muestran de la forma que msprefiera, 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 espa-cio de trabajo. Para el lo, s implemente seleccione la opcin de menArchivo>Guardar rea de trabajo, introduzca el nombre de archivo en el cuadro

2B Excel. Los mejores trucos

de dilogo correspondiente y haga clic en el botn Guardar. Cuando graba unrea de trabajo, la extensin del archivo ser .xlw e\vez de .xls. para recuperarun rea de trabajo de Excel a una ventana completa de uno de los libros en parti-cular, simplemente haga doble clic en la barra de ttulo de la ventana correspon-diente. thmbin puede hacer clic en el botn de maximizar de cualquiera de lasventanas del rea de trabajo. lrna vez que haya acabado, puede cerrar los librosde Excel de la forma habitual.

$rchivo Edicin [er lhsertar formato ![erramient,rs Dalos Veqtana l . ' ) i i . : , : : i ' l i

J # # S i r . $ t r & - ; , ; g - ; s : i - , ' : , ' f f i S , r - t l i l & g q l r n u ' ' " - f r . f f iArial

n 4

. l r l - , I i I K $ = = = S $ ' x i g * n $ : = : = - , , 1 + - . f f iv f *

.,.iiir:.:! jt j

iII

iIl

,

t j- . . . . - . Ij

ItIill1

i

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 quefueron guardados. si solamente necesita abrir uno de los libros, hgalo de laforma habitual. Cualquier modificacin que haga en alguno de los libros queforman parte del rea de trabajo se guardar automticamente cuando cierr elrea de trabajo como conjunto, aunque tambin puede guardar cada libro deforma individual.

5i dedica una pequea parte de tiempo a configurar algunas stas personali-zadas para realizar tareas repetitivas que requieren de mltiples libroi abiertos,encontrar que esas tareas sern ms fciles de gestionar. Quiz decida utilizardiferentes stas para diferentes tareas repetitivas, dependiendo de cul sea latarea o cmo se sienta ese da.

1. Reduci r la f rust rac in en los l ibros y en las hojas de clculo 29

grchiw fdicion [er lnserta' formato lBrramientas Dalos Veqtane I

- j # # 3 " * , C t r i l ! $ - i ; S - ; d i ' ' ' , i ' r , i " , m g p # E - * i l f f i r * p r ' : n + - * f f i, A r i a l

- 1 0 - H l { S = = = S . $ ' " t " * A : \ f $ F : = : : i : ? ' j 3 * . & -

r


':. i i i i i i : i i : : l l .1.. l l i .1.1...... ' :. ' ' .&rchiva fdicin U.er lnserter fornratr: l[rrranrienlas alos \ed:na i , .,,., ,

- j , ; , # i * ; i - s , l i ; + ' i , " $ - . 3 , , & - d ' ' : ' j , ' f f i r $ , E - i i l i l d r i " $ l r l % - u r n , *;rril:ri

A r i l - 1 - N j f S = = = : i l * $ * t , : u * u ] j . l i : = t = : , - " t + - * k . , lA 1 Y #

. . . ' :1l

+; ,T .

{1LI

9 .' ' j

1 0 '1 1

.

1 t1,11 4 i1 5 iI t f

l /

1 8 .r{ { } rr \noar,,i j l fu:ai ,

**{p_JxJ

, , 1tlUI"l

Figura 1.4. Cuatro libros en vista cascada.

I lntroducir datos en varas hojas de clculosimultneamenteEs muy comn tener los mismos datos en varas hojas de clculosimul tneamente. Puede ut i l izar la herramienta de Excel para agrupar deforma que los datos ntroducidos en una hoja se int roduzcanautomt icamente en e l resto de hojas a l mismo t iempo. Tambindisponemos de una aproximacin ms rpida y ms f lex ib le para hacer estatarea, que requiere de un par de l neas de cdigo de Visual Basic forAppl icat ions (VBA).

El mecanismo que incorpora Excel para hacer que los datos se introduzcan enmltiples lugares al mismo tiempo es una funcin llamada "Grupo", la cual fun-ciona agrupando las hojas de clculo de forma que todas estn nculadas con ellibro 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

1. Reduc i r la f rus t rac in en los l ib ros y en las ho jas de c lcu lo 3 1

Macintosh) mientras hace clic en las pestaas de las hojas de clculo en las quedesea insertar simultneamente los datos. Cuando introduzca datos en cualquie-ra de las celdas de la hoja de clculo, se introducirn automticamente en el restode hojas de clculo agrupadas. Misin completada.

Para desagrupar las hojas de clculo, bien seleccione una hoja de clculo queno sea parte del grupo o bien haga clic con el botn derecho del ratn en cual-quiera de las pestaas de las hojas de clculo agrupadas y seleccione la opcinDesagrupar hojas.

Cuando las hojas de clculo estn agrupadas, si echa un vistazo a labarra de ttulo de Excel, ver que aparece la palabra "Grupo" encerradaentre corchetes. Esto le hace saber que todava tiene agrupadas lashojas de clculo. A menos que tenga vista de guila y una memoria deelefante, es ms que probable que no se d cuenta o se olvide de quetiene agrupadas las hojas de clculo. Por tanto, le sugerimos que lasdesagrupe tan pronto como haya terminado con lo que estuviesehaciendo.

Aunque este mtodo es fcil, necesita que recuerde agrupar y desagrupar lashojas cuando necesite, corriendo el riesgo de sobrescribir datos en cualquier otrahoja de clculo si se olvida de desagruparlas. Thmbin significa que se producirnentradas de datos simultneas independientemente de la celda en la que est si-tuado. Por ejemplo, qtz solamente desee introducir datos simultneamentecuando se encuentre en un cierto rango de celdas en particular.

Agrupar hojas de clculo automticamentePuede evitar estos inconvenientes fcilmente utilizando un cdigo VBA muy

sencillo. Para que pueda funcionar, debe residir dentro del mdulo privado delobjeto Sheet (Hoja). Para acceder rpidamente al mdulo privado, haga clic con elbotn derecho del ratn en la pestaa con el nombre de la hoja y seleccione laopcin Ver cdigo. Entonces podr utilizar uno de los eventos de Excel para lashojas de clculo, los cuales ocurren dentro de la propia hoja de clculo, comopuede ser cambiar una celda, seleccionar un rango/ activar, desactivar, etc. me-diante dichos eventos, podr mover el cdigo dentro del mdulo privado del obje-to Sheet. Lo primero que hay que hacer para que funcione el agrupamiento esdar nombre al rango de celdas que desea tener agrupadas de forma que los datosse introduzcan automticamente en el resto de hoias de clculo. Escriba este c-digo en el mdulo privado:

Private Sub Worksheet_Sel-ect ionChange (ByVa1 Target As Range)I f No t I n t e r sec t (Range ( "M iRango " ) . Ta rge t ) I s No th i ng Then


rHo ja5 se ha co locado pr imero a p rops i to ya que ser' la ho ja ac t iva desde l -a que t raba ja remos

S h e e t s ( e r r a y ( i l H o j a 5 t , " H o j a 3 ' , , " H o j a 1 u ) ) . S e l e c t

E l s eM e . S e l e c t

lind t r.E;Nd SUlf

En este cdigo, hemos utilizado el rango cuyo nombre es "MiRango", peropuede cambiar este nombre por el que est utilizando en su hoja de clculo. Thm-bin deber cambiar los tres nombres de hoja en el cdigo, tal y como se muestrala figura 1.5, con aquellos nombres de hoja que desea agrupar. Cuando hayaterminado, cierre la ventana de mdulo o bien pulse Alt/Comando-epara vol-ver a la ventana principal de Excel.

ffis#x

II{1t{

$ : H$ velrroect (Librol):j ., ::* fvlirrsrf t Exrel tlbiel:r-rs

, S] noar {noarEl nnae {HniatJffi r-rna: iHoje3)Etil noia+ {Hoia4j

ffl$lorksheet J lSeleetionChanse J

Fr i a te , - :1 i - r Io rk=Lreer r_ l fEreu : r -1 , : r r r lh*n 'Je iEy 'v '1 Target As Rf i :r f I ' 1 , : t . rn r : .e rsec t . iF .anqe i " l , f iF .ang"1 . Target - l r= : I ' , I ' : t .h in ' " . "

' I { i , l ' 3 . 5 s * i ? , 3 ' : ' : 1 , : , : , : . ; 1 , : F , f l t { i e I - , : , i I r i - r : r F i r : r 5 : t . : 1 r i 3 , : { i - t * S *

I i , * l i r - j r 3 : r l l . i t . r a t l * * d * i i r : { i - r * t . I , : J : , R l i { t - * : t t , ; ? * j

l j F r e e r - = i J r r r a l r ( " H , _ r f t S " , " H r _ 1 _ i f l l " , " H c ' j i r 1 " i i . 5 e I e r = tE l s e

H e . 5 e l - e c t : .

E r r d I fEr d 5 t :

*Erm ;,1lnoas ,/'/crhsheet

,{lfabtica lfor cafeqor,.as I=

Table False

* { t J I { { |.: l .- :.. i .. . ..._...

Figura L.5. Cdigo para agrupar automticamente hojas de clculo.

Es importante resear que el primer nombre de hoja utilizado en el array debeser el de la hoja que contiene el cdigo y por tanto la hoja de clculo en la que seintroducirn los datos.

Una vez que haya escrito el cdigo en el lugar adecuado, cadavez que selec-cione cualquier celda de la hoja de clculo, el cdigo comprobar si la celda queha seleccionado (el objetivo) est dentro del rango llamado "MiRango". si es as, elcdigo agrupar automticamente las hojas de clculo que desea agrupar. Si porel contrario esto no es as, desagrupar las hojas simplemente activando la hojaen la que se encuentra. La maralla de este truco es que no hay necesidad deagrupar manualmente las hojas y por tanto correr el riesgo de olvidarse de

nt - ll { i

I rr:i!

l:l$'\v


desagruparlas, por lo que esta aproximacin le ahorrar gran cantidad de tiem-po y frustracin. Si desea que aparezcan los mismos datos en las otras hojas perono en las mismas direcciones de celdas, escriba el siguiente cdigo:

Private Sub worksheet_Change (ByVa1 Target . As Range)I f No t I n t . e r sec t (Range ( "M iRango t r ) , Ta rge t ) f s No th i ng Then

Wi th Range ( 'M iRango " ). Copy Des t i na t i on : =Shee t . s ( ' ,Ho ja3

" ) . Range ( "A1 t r ). C o p y D e s t i n a t i o n : = S h e e t s (

" H o j a 1 ' ) . R a n g e ( ' D 1 0 , , ;no wl-tn

Eno l r.E;no suD

Este cdigo tambin necesita estar incluido dentro del mdulo privado del ob-jeto Sheet. Siga los pasos que describimos anteriormente en este mismo trucopara poder llegar a dicho mdulo.

lmpedir que los usuarios realizan ciertas acconesAunque Excel proporciona proteccin general para los l ibros y hojas declculo, esta caracterst ica no proporcona privi legios l imitados a losusuar ios a menos que ut l ce un t ruco.

Se pueden gestionar las interacciones de los usuarios con las hojas de clculomonitorizando y respondiendo a los eventos. Los eventos, como su nombre indi-ca, son acciones que ocurren a medida que se trabaja con los libros y las hojas declculo. Algunos de los eventos ms comunes incluyen abrir un libro, guardarloy cerrarlo cuando el usuario desee. Se le puede indicar a Excel que ejecute ciertocdigo Visual Basic cuando cualquiera de estos eventos se produzca.

Los usuarios pueden saltarse todas estas protecciones si desactivan lasmacros por completo. Si la seguridad est establecida a nivel medio,sern notificados de que existen macros en el libro abierto, dando laposibilidad de desactivarlas. Un nivel de seguridad alto simplementedesactivar las macros automticamente. Por otro lado, si las hojas declculo requieren del uso de macros, es ms que probable que losusuarios desean tener las macros activadas. Estos trucos son prcticosy no proporcionan una seguridad de datos que requiera de gran cargade trabajo.

lmpedir el comando Guardar como en un libro de ExcelSe puede especificar que cualquier libro de Excel sea guardado como slo lec-

tura activando la casilla de verificacin Se recomienda slo lectura que se en-


cuentra accediendo a la opcin Opciones generales del cuadro de dilogo Guar-dar. Con esto, se eta que un usuario pueda guardar cualquier cambio que hayarealizado al archivo, a menos que lo grabe con un nombre diferente o en unaubicacin distinta.

A veces, sin embargo, desear impedir que los usuarios puedan guardar unacopia del libro en otra carpeta con el mismo nombre de archivo o con cualquierotro. En otras palabras, lo que desea es que los usuarios slo puedan guardarsobre el archivo existente y no crear otra copia del mismo. Esto es particular-mente interesante cuando hay ms de una persona guardando los cambios en unlibro de Excel, porque no desea que haya diferentes copias de un mismo libroguardadas con el mismo nombre pero en carpetas diferentes.

El evento Bef oreSave que vamos a utilizar existe desde Excel 97. Como supropio nombre indica, este evento se produce justamente antes de que un librosea guardado, permitindole interactuar con el usuario mostrando una adver-tencia e impidiendo que Excel continuar grabando.

Antes de probar esto en su casa, asegrese de guardar su libro de Excelantes. Si coloca este cdigo sin haber guardado los cambios antes, yano podr hacerlo.

Para insertar el cdigo, abra el libro de Excel, haga clic con el botn derechodel ratn en el icono de Excel situado justo a la izquierda del men Archivo yseleccione la opcin Ver cdigo, como puede verse en la figura 1..

1.,,:,,,,,,: .,,,, =

jp{ .nf

[ r a l o r t / e l t a n i - d x

,E ',g,, E - :ll ,fug s.$, i$ii$f

*$ " , , ' ; : = : i , * ' i . * . 9 . i

i ganiear" 'i -'i rl|]n'|, , ,

i -

. t

i +J \rer cdisob-r-*-**-- -b i

--"-- *--l

1 jJ"J" .12..)1 3 11 4 r

-^--...i

1 5 1i"T""t ;ri\"r:ii- rr-"-r'-e_.1_irqi;i- I$ji no_"s /" "''Listo

l r , lI!UIq

! F i

r l r

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


%,,, Este acceso rpido no est disponible en Macintosh. Tendr que abrirjt,:, el Editor de Visual Basic pulsando Opcin-Fl1 o bien seleccionando\ la opcin de men Herramientas>Macro>Editor Visual Basic. Una vez\ /

en 1, haga clic con el botn derecho en ThisWorkbook que est situadoen la ventana de proyectos de la parte izquierda.

Escriba el siguiente cdigo en VBE, tal y como se muestra en la figura 1.7 yluego pulse Alt/Comando-Qpara volver a la ventana principal de Excel.

i{#iIlj##jiii::,.lililil.'.....''...'...'..,.:;'$ grrnivo Edicin Uer lnsertar lormatn Qepuracin Ejecular l[erramientas funrplementns Uentana

ffi jiJ - .d ' .,. .,r, .:,,,,, ,i ;| ' 3 i | _ - l l t : :r --*l llj #,

J lt;;kk-eitoresare

= iHjx.i- f , x

IthsWorkbook \,\':rl.h. *J&tFbtica ]for categnrias fl

l {General lFr i ' . ' ,a l :e i i r - I r r ' r r ld :oak_EefnreSaue {E i i r t l ;a1 ,1a l 'eL=LI I l r s E ' :n1e '=n r la r r re IL' rrri I F.e r l it As L rt 11

If Sare,L=Uf = Trr:e TLrErrI F . e p I y = I ' I s E o x ( " I r I a r : . i e r r E r e r i l i s r r p E r a g u a r c l a r e 5 t . e I l h r " 6

t t ' lOn L- ] t rD t lL - r tT : r fE . t 'e= le ' l r f , l_ r , : t fL lA f I ' l ' l ' f I l . e l r t iS tnO nn l t re?r f ,' .' f ,r :er=t i ,rn + ' , .HCanr=e I I

C a t c e I = { I F . e p I l t = ' v - b L l a r l r : : e I )I f i l a r r c e L = F a I s e T i ' r e r r l { e . ! l a n eCan_ :e I = Tr l_ te

E n r l I f

End l ir-

E = i l r l I- j l . . . . . . 1 . . . . . . . . . , i

This"/uor[o'-rk abelslnFr False

rl0

nFlictFles':lutior I - xlUserR*soh-19tl+ False

a''uin'[ -41l-]+ - ill[:'isF l,In[,.t-c,rnrnr Tru'=

TrueFalse

EuroTool (EUROTOOL.XLA)S vn*rroiect {Librot};:j ,i:'* trtir:rnsofl: E.rrel {rbietns

Figura 1.7. Cdigo una vez introducido en el mdulo privado (ThisWorkbook).

Pr ivate Sub workbook_Beforesave (ByVa1 SaveAsUI As Boolean, CanceI AsBoo lean )ur-m -LRepJ_y As rrong

I f SaveAsUI = True ThenlRep l y = MsgBox ( r 'No t i ene pe rm iso pa ra gua rda r esEe " &

_

" f i b ro con o t r o nombre . Desea gua rda r l o con e I m i smo nombre? , ' ,vbQuest ion + vboKcancel)

Cance l = ( lRep l y = vbCance f )I f Cance l - = Fa l se Then Me .Save

Cancel = TrueEincl r f

-E;nd sutf

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


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

lmpedir que los usuarios imprman un l ibro de ExcelQuiz desee impedir que los usuarios puedan imprimir un libro para que lue-

go seguramente acabe en una papelera o tirado en un escritorio a la vista detodos. Utilizando el evento BeforePrint, podemos impedir esto. Introduzca elsiguiente cdigo, como hicimos anteriormente, en el Editor de Visual Basic:

P r i v a t e S u b w o r k b o o kC a n c e l = T r u eM s g B o x r r N o p u e d e

End Sub

_ B e f o r e P r i n t ( C a n c e l A s B o o l e a n )

i m p r i m i r e s t e l i b r o . " , v b f n f o r m a t i o n

Pulse AltlComando-Qcuando haya terminado de introducir el cdigo paraguardarlo y volver a la ventana principal de Excel. Ahora, cada vez que los usua-rios intenten imprimir este libro, no podrn hacerlo. La lnea de cdigo con lainstruccin MsgBox es opcional, pero siempre es buena idea incluirla para queinforme al usuario de que no moleste al departamento de Tecnologa Internadiciendo que su programa no funciona.

Si desea impedir que los usuarios impriman solamente algunas hojas del li-bro, utilice este cdigo envez del anterior'

Private Sub workbook_BeforePr int (Cancel As Boolean)Se lec t Case Ac t i veshee t .Name

case , 'Ho j a1 " , uHo j a2 uCancel = TrueMsgBox

"No puede imp r im l - r es ta ho ja de es te l i b ro . " ,vblnformat ion

End Se lec tE;NO sjl]D

Observe que hemos especificado las hojas Hojal y }{oja2 como las que tienenprohibido ser impresas. Por supuesto, puede cambiar esos nombres por el de cual-quier otra hoja que desee bloquear. Thmbin puede aadir ms hojas a la lista,simplemente escribiendo una, seguida del nombre de la hoja entre dobles comi-llas. Si slo desea impedir la impresin de una sola hoja, incluya su nombre entredobles comillas detrs de la sentencia Case y elimine la coma sobrante.

lmpedir que los usuaros 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


veces, sin embargo, desear impedir simplemente que se puedan aadir nuevashojas de clculo, permitiendo que se realicen el resto de acciones.

El siguiente cdigo le permitir hacer esto:Pr iva te Sub Workbook_NewSheet (ByVaI Sh As

A p p l i c a t i o n . D i s p l a y A l e r t . s = F a l s eMsgBox ' f No puede aad i r nuevas ho j as

v b l n f o r m a t i o nS h . D e l e t e

A p p l i c a t i o n . D i s p l a y A l e r t . s = T r u eEnd Sub

ob j ec t )

d e c l c u l o a e s L e l i b r o . "

B

Este cdigo primeramente muestra el cuadro de dilogo con el mensaje y lue-go, inmediatamente, elimina la nueva hoja que se acaba de aadir, una vez queel usuario acepta el mensaje. La instruccin Application. DisplayAlerts =False impide que Excel muestre la advertencia estndar que pregunta al usua-rio si realmente desea eliminar la hoja. Con este cdigo, los usuarios sern inca-paces de aadir ms hojas de clculo al libro.

Otra forma de impedir que los usuarios aadan nuevas hojas de clculo esseleccionar la opcin Herramientas>Protege>Proteger libro y luego activar la ca-silla de verificacin Estructura. Sin embargo, como ya dijimos al principio de estetruco, el mecanismo de proteccin de Excel es menos flexible y adems de impe-dir aadir nuevas hojas, tambin impedir otras muchas cosas.

lmpedir confrmacones innecesariasA veces, las interacciones de Excel puedan resultar pesadas: semprepreguntando para pedir confirmacin sobre accones. Quitemos estosmensaies y dejemos que Excel real ice las acciones.

El tipo de mensajes a los que nos referimos son aquellos que preguntan si sedesean activar las macros (incluso cuando no hay ninguna) o los que nos pre-guntan si estamos seguros de que queremos eliminar un hoja de clculo. A con-tinuacin 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

una macro en un libro. Por desgracia, Excel sigue recordando que se ha grabadouna macro incluso si la ha eliminado utilizando la opcin Herramientas>Macro>Macros (Alt,zOpcin-F8). Despus de hacer esto, si abre el libro de nuevo seguirrecibiendo un mensaje que le pregunta si desea activar las macros, incluso aun-que no haya ninguna que activar.

3 B Excel. Los mejores trucos

Se le pedir confirmacin para activar las macros solamente si el nivelde seguridad est establecido en medio. Si est establecido en bajo, lasmacros se activan directamente, pero si est establecido en alto, estndesactivadas automticamente.

Cuando graba una macro, Excel inserta un mdulo de Visual Basic que con-tendr los comandos y las funciones. Por ello, cuando se abre un libro, Excelcomprueba si existe algn mdulo, este vaco o no. Cuando se eliminan las macrosde un libro, slo se elimina el cdigo, pero no el mdulo en s (es algo as comobeberse toda la leche pero dejarse el bote vaco dentro de la nevera). Para impedirque se muestren este tipo de mensajes innecesarios, deber eliminar tambin elmdulo. As es como puede hacerse esto: Abra VBE seleccionando la opcinHerramientas>Macro>Editor de Visual Basic (o pulsando AltlComando-F11) yluego seleccionando la opcin VeeExplorador de proyectos (en Macintosh, la ven-tana de proyectos siempre est abierta, por lo que no necesitar abrir el explora-dor de proyectos). A continuacin podr ver una ventana como la que se muestraen la figura 1.8.

il G+-i S EuroTool (EUROTOOL.XLA), i S Y8APro iec t (L ib ro l )

--, , , _r i lul trrsf l : E:rrel t ] l t r jelrs

El nt-, | ' r t iHr- ' j1itfl f fi5tl/r[..1r rr1[,:

: t lrlrJuhrsd* t ' , jutntd*ilHiH

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 * situadoa su izquierda para visualizar los componentes del libro, en particular los mdu-los. Haga clic en el icono + de la carpeta Mdulos para obtener una lista de todoslos mdulos. Haga clic con el botn derecho del ratn en cada mdulo y ellja laopcin Quitar mdulo. Cuando se le pregunte, rechace la opcin de exportar losmdulos. Antes de quitar los mdulos que pudieran tener cdigo til, haga dobleclic 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 de confirmacin para guardar cambiosque no se han realizado

Probablemente habr observado que a veces al abrir un libro y echar un sta-zo a su informacin es suficiente para que Excel le pregunte si desea guardar los


cambios en el libro de macro personal (aunque de hecho no ha realizado ningu-no). Lo ms probable es que tenga una funcin impresible dentro del libro demacro personal.

un libro de macro personal es un libro oculto que se crea la primera vez quegraba una macro y que se abre cada vez que se utiliza Excel. una funcin (ofrmula) imprevisible es aquella que se recalcula automticamente cada vez querealiza prcticamente cualquier cosa en Excel, incluyendo abrir y cerrar un libroo la aplicacin entera. Dos de las funciones impresibles ms comunes son Hoy oy Ahora O .

Por tanto, aunque crea que no ha realizado cambios en el libro, puede queesas funciones que se ejecutan en segundo plano s los hayan hecho. Esto cuentacomo 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 realiza-do, dispone de un par de opciones. La ms obvia es no almacenar funciones im-previsibles al principio dentro del libro de macro personal y luego eliminarcualquier funcin imprevisible que ya exista. La otra opcin, en caso de que ne-cesite utilizar funciones imprevisibles, puede ser utilizar este sencillo cdigo parahacer que Excel crea que el libro de macro personal ha sido guardado en el mo-mento en el que se abre:

P r i v a t e S u b w o r k b o o k _ O p e n (M e . S a v e d = T r u e

End Sub

Este cdigo debe residir en el mdulo privado del libro del libro de macro per-sonal. Para llegar ah desde cualquier libro, seleccione la opcin Ventana>Mostrar,seleccione Personal.xls y luego haga clic enAceptar. Luego abra VBE e introduz-ca el cdigo anterior. Finalmente, pulse Alt/comando-Qpara volver a la venta-na principal de Excel cuando haya terminado. Por supuesto, si dispone de unafuncin imprevisible que quiere que sea recalculada y por tanto guardar los cam-bios que haya realizado, entonces introduzca el siguiente cdigo:

Private Sub workbook_Open ( )Me . Save

.E;NO SUj]

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

lmpedir los avisos de Excel para macros grabadasUno de los muchos inconvenientes de las macros grabadas es que, aunque

son muy tiles para reproducir cualquier comando, tienden a olvidar las res-


puestas a los avisos que se muestran en pantalla. Elimine una hoja de clculo yse le pedir confirmacin; ejecute una macro que realice esto mismo y todava sele pedir confirmacin. Veamos cmo desactivar esos asos.

Seleccione la opcin H erram ientas> Macro>Macros (AltlOpcin-F8 ) para mos-trar un listado de todas las macros.

Asegrese de que est seleccionada la opcin Todos los libros abiertos en elcuadro de lista desplegable de la parte inferior. Seleccione la macro en la que estinteresado y haga clic en el botn Modificar. Coloque el cursor antes de la pri-mera lnea de cdigo (la primera lnea que no tiene un apstrofe delante de ella) yescriba lo siguiente:

App l i ca t . i on . D i sp l - ayA le r t s = Fa l se

Y al final del todo del cdigo, aada esto:

App l i ca t i on .D i sp layA le r t s = T rue

Con lo que la macro entera quedara as:

?"O MyMacro ( )

' M iMac ro Mac ro

: t t i m i n a l a h o j a d e c l c u l o

App l i ca t i on . D i sp l ayA le r t sA c t i v e S h e e t . D e l e t eApp l i ca t ion . D i sp layA ler t s

End Sub

a c t u a I

= F a I s e

= True

Observe que al final del cdigo volvemos a activar los mensajes de confirma-cin para que Excel los muestre cuando estemos trabajando normalmente. Si seolvida de activarlos, Excel no mostrar ninguna alerta, lo cual puede ser peli-groso.

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 cdigoen la que se vuelven a activar los mensajes de confirmacin. Si ocurrieseesto, probablemente ser mejor salir de Exc el y volver a abrirlo paradejar todo en su estado normal.

Ahora ya sabe cmo utilizar Excel sin mensajes de confirmacin. Tenga encuenta, de todas formas, que esos mensajes estn ah por una razn. Asegresede que comprende completamente el propsito de estos mensajes antes dedesactivarlos.

1. Reduci r la f rust rac in en los l ibros y en las hojas de clculo 4',1

H Ocultar hojas para gue no puedan ser mostradasA veces deseara tener un lugar donde colocar informacin que no puedaser leda o modif icada por los usuarios. Puede construir un lugar secretodentro del l ibro/ un lugar donde almacenar informacin, frmulas y otrosrecursos que se ut i l izan en las hojas pero que no desea que se vean.

Una prctica muy til cuando se configura un nuevo libro de Excel es reser-var una hoja para almacenar informacin que los usuarios no necesitan ver:clculos de frmulas, validacin de datos, listas, variables de inters y valoresespeciales, datos privados, etc. Aunque se puede ocultar una hoja seleccionandola opcin Formato>Hoja>Ocultar, es importante asegurarse de que los usuariosno puedan volver a mostrarla seleccionando la opcin Formato>Hoja>Mostrar.

Por supuesto, simplemente puede proteger la hoja, pero esto todava deja aldescubierto los datos privados, las frmulas, etc. Adems, no se puede protegerlas celdas que estn vinculadas a cualquiera de los controles disponibles en labarra de herramientas Formularios.

Envez de esto, jugaremos con la propiedad vi-sible de la hoja, establecin-dola en xlveryHidden. Desde VBE (Herramientas>Macro>Editor de Visual Basico AltlOpcin-Fl 1), asegrese de que la ventana de exploracin de proyectos estvisible seleccionando la opcin Ver>Explorador de proyectos. Encuentre el nom-bre del libro y expanda su jerarqua haciendo clic en el icono + que aparece a laizquierda de su nombre. Expanda la carpeta Microsoft Excel Objetos para mos-trar todas las hojas del libro.

Seleccione la hoja que desea ocultar en el explorador de proyectos y muestresus propiedades seleccionando la opcin VepVentana Propiedades (o pulsando latecla F4). Asegrese de que est seleccionada la pestaa Alfabtica y busque lapropiedad Visible en la lista, que estar situada al final. Haga clic en el cuadro detexto que hay a su derecha y seleccione la ltima opcin: 2 - xlSheetVeryHidden,tal y como se muestra en la figura 1.9. PulseAlt/Comando-Qpara guardar loscambios y volver a la ventana principal de Excel. A partir de ahora, la hoja ya noestar sible desde la interfaz de Excel e incluso tampoco podr mostrarse atravs de la opcin Formato>Hoja>Mostrar.

Una vez que haya seleccionado la opcin 2 - xlSheetVeryHidden en laventana de propiedades, puede parecer que dicha eleccin no ha tenidoefecto. Este fallo visual ocurre a veces y no debera importarle. Siempreque 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 pasos anteriores, pero esta vezseleccionando la opcin 1 - xlSheetVisible.


,f ,'i; E #$ ruroto;a iEudorootJrt*)$ vn*eroiect (Libror)l.l t Micros':ft Excelbietas, I El Hojl (Hcjal): : S] nnar {Hoja?}i fi rhisworkbc',:k

i: i-:-$ f"ldulr.rsI d* ttdul,:t'

rl r'oduh,f

Hoial \orksheel:

ltbtica lRor categoras I

ageBreal-t Falseispl,:yRightToLeFl: FlsenableAutaFil ler Flsenbler-alculal ion Truenblef-utlining False

vul:Tble False

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

Personalizar el cuadro de dlogo Plantillasy el l ibro predeterminadoSi suele real izar las mismas tareas o ut l izar las mismas distr ibuciones parahojas de clculo con frecuencia, puede construir su propa pestaa deplant i l las en e l cuadro de d i logo estndar Plant i l las para proporc ionar unacceso rpido al inicio.

Imagnese que tiene una hoja de clculo que contiene los das del ao y fr-mulas que resumen diversas informaciones para esos das. Ha formateado estahoja de clculo cuidadosamente con los colores de su empresa, el logotipo y conlas frmulas necesarias, todo ello para utilizarla con frecuencia. En vez de tenerque 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 par-tida para los siguientes proyectos, permitindole saltarse la configuracin ini-cial, el proceso de dar formato, la construccin de frmulas, etc. Guardar unaplantilla de una hoja de clculo simplemente significa abrir un libro nuevo, bo-rrar todas las hojas excepto una y luego crear una plantilla bsica que ser la

E


que utilice. llna vez terminada, seleccionar la opcin Archivo>Guardar como yelegir la opcin Plantilla del cuadro de lista desplegable con los tipos de archivosposibles. Si la plantilla es de un libro (es decir, que contendr ms de una hoja),entonces cree un nuevo libro, haga todos los cambios necesarios y luego seleccio-ne la opcin Archivo>Guardar como y gurdelo como una plantilla.

Con la plantilla terminada, puede crear una copia exacta de la misma en cual-quier momento seleccionando la opcin Archivo>Nuevo y luego seleccionandouna plantilla de libro, o bien haciendo clic con el botn derecho en una pestaade hoja y seleccionando la opcin lnsertar desde el men contextual para insertaruna nueva hoja a partir de una plantilla. ZNo sera interesante poder tener todasesas plantillas disponibles desde el cuadro de dilogo estndar Plantillas o confi-gurar su libro preferido como predeterminado? Puede hacer todo esto creando supropia pestaa de plantillas.

Este truco presupone que tiene una nica instalacin de Excel en suordenador. Si dispone de mltiples copias o versiones de Excel, puedeque no funcione.

Crear su propa pestaa de plantillasSi dispone de una serie de plantillas (tanto de libros como de hojas de clculo)

que desea utilizar con regularidad, puede agruparlas todasjuntas en el cuadrode dilogo Insertar o Plantillas.

Desde cualquier libro, seleccione la opcin Archivo>Guardar como y, desde elcuadro de lista desplegable de tipos de archivo, seleccione la opcin Plantilla (..xlt).De forma predeterminada, Excel seleccionar la carpeta estndar Plantillas deldisco duro en donde se almacenan todas las plantillas del usuario. Si no existeuna carpeta llamada "Mis plantillas", cree una utilizando el botn Nueva carpe-ta. Luego, seleccione la opcin Archivo>Nuevo en la barra de mens (en Excel2OOO y posteriores, seleccione Plantillas generales en el cuadro de diIogo Nuevolibro. En Excel 2O03, debe seleccionar la opcin En mi PC del panel de tareas).Entonces, debera haber una pestaa que representa la carpeta Mis plantillas queacaba de crear (vase figura 1.1O). Tmbin debera ver las plantillas de libros yhojas de clculo que guarde en dicha carpeta.

Utilizar un libro personalzado de forma predeterminadaAl iniciar Excel, se abre de forma predeterminada un libro en blanco llamado

Librol, que contiene tres hojas en blanco. Esto est bien si desea comenzar denuevo cada vez que inicia Excel. Sin embargo, es probable que trabaje normal-


mente con un libro. Por tanto, resulta pesado tener que abrir Excel y luego bus-car el libro que se desea abrir. Si desea configurar Excel para que automticamentese inicie con un cierto libro abierto, siga leyendo.

5eleccilne un icono parar/Br una visla previa,

" -tPTfik:,J:" ?Jfr: *f: . - | l, ''''t;1-1 --,::1f:1"::.-. iFigura 1.10. El cuadro de dilogo Plantil las.

Para ello, guarde su libro predeterminado (plantilla) en la carpeta XLSTART(que normalmente se encuentra en la carpeta c:\Documents and settings\Nombrede usuario\Application Data\Microsoft\Excel\XlsTART en Windows y en la car-peta Applications,/Microsoft Office X/Office/Startup,/Excel en Macintosh). Unavez que haya hecho esto, Excel utilizar cualquiera de los libros que haya inclui-do en esta carpeta como predeterminados.

La carpeta XLSTART es donde se crea y guarda automticamente ellibro de macros personales cuando graba una macro. El libro de macrospersonales es un libro oculto. Lambin puede tener sus propios librosocultos abiertos en segundo plano si lo desea, abriendo dicho libro,seleccionando la opcin Ventana>Ocultar, cerrando Excel y luegohaciendo clic en S para guardar los cambios en 1. Luego coloque eselibro en la carpeta XLSTART Todos los libros que oculte y coloquedentro de la carpeta XLSTART se abrirn como libros ocultos cada vezque inicie Excel.

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


IB,l,KEry

Crear un ndice de hojas en el libroSi ha dedicado mucho t iempo en un l ibro que contiene muchas hojas, sabeperfectamente lo complicado que puede ser encontrar una hoja enpart icular. En estos casos, es imprescindible tener una hoja ndice parapoder navegar por el resto de hojas de l ibro.

Utilizar una hoja de ndice le permitir explorar de forma rpida y sencilla ellibro de forma que con un solo clic de ratn pueda ir directamente al lugar quedesee. Se puede crear un ndice de dos formas. Podra tener la tentacin de crearel ndice a mano. Cree una nueva hoja, llmela "ndice" o algo parecido, intro-duzca en ella una lista de todos los nombres de las hojas e incluya vnculos acada una de ellas mediante la opcin de men Insertar>Vnculo o pulsando Con-trollComando-K. aunque este mtodo pueda ser suficiente en casos de los queno hay demasiadas hojas y no hay muchos cambios, puede ser muy tedioso te-ner que mantener el ndice manualmente.

El siguiente cdigo crear automticamente un ndice con vnculos a todas lashojas que estn incluidas en el libro. Este ndice se vuelve a generar cada vez quela hoja que contiene el cdigo es activada. Este cdigo debera residir en el mdu-lo privado del objeto Sheet. Inserte una nueva hoja en el libro de Excel y llmelacon algn nombre apropiado, como pueda ser "ndice". Luego haga clic con elbotn derecho del ratn sbrela pestaa de dicha hoja y seleccione la opcin Vercdigo. En la ventana de cdigo de Visual Basic escriba lo siguiente:

Private Sub Worksheet_Act ivate ( )Dim wSheet As WorksheetDim L As LongT . - 1

Wi th Me. co l umns (1 ) . C f ea rCon ten t s. Ce l t s ( 1 , 1 ) = , , NDTCE i l. c e 1 1 s ( 1 , 1 ) . N a m e = " I n d i c e "

Eno wa tn

For Each wsheet In worksheetsI f wshee t .Name Me .Name Then

L = L + l -With t rsheet

. R a n g e ( " A 1 ' ) . N a m e = " I n i c i o i l & ! / s h e e t . I n d e x

. H y p e r l i n k s . A d d A n c h o r : = . R a n g e ( t r A 1 " ) , A d d r e s s : = " " , -subAdd ress t= r r i i . . t r , Tex tToD isp lav := "vo f ve r a l nd i ce "

F;nd $ 1t:nM e . H y p e r l i n k s . A d d A n c h o r : = M e . C e f l s ( 1 , 1 ) , A d d r e s s : = " " , -

SubAdd ress :=n In ' c o " & wshee t . I ndex , Tex tToD isp lay :=wshee t .NameEJNO I I

Next \rsheeE.E;Nd SUjf


Pulse AltlComando-Qpara volver al libro y guardar los cambios. Observeque el cdigo da el nombre "Inicio" (al igual que cuando da nombre a una celda oun rango de celdas en Excel) a la celda A1 de cada hoja, adems de un niconmero que representa el nmero de ndice para dicha hoja. Esto asegura que lacelda A1 de cada hoja tiene un nombre diferente. Si la celda A1 de la hoja ya tieneun nombre, debera considerar cambiar cualquier mencin a A1 en el cdigo poralgo ms adecuado (por ejemplo, alguna celda no utilizada que est situada encualquier parte de la hoja).

Debe tener en cuenta que si selecciona la opcin Archivo>Propiedades>Resumen e introduce una direccin URL como vnculo base, el ndiceque se crea por el cdigo anterior posiblemente no funcione. Un vnculobase es una ruta o LIRL que desea util izar para todos los vnculos conla misma direccin base y que estn incluidos en el documento actual.

Otra forma de construir un ndice, que es ms sencilla para el usuario, esaadir un vnculo a la lista de hojas como un elemento de men contextual, alque se puede acceder haciendo clic con el botn derecho del ratn. Haremos quedicho vnculo abra el men estndar de hojas. Normalmente puede abrir estemen haciendo clic con el botn derecho del ratn en cualquiera de los botonesde desplazamiento que se encuentran a la izquierda de donde se muestran lassolapas de cada hoja, tal y como se muestra en la figura 1.11.

:+:li;ii''$J &rchivo fdicin Ver [nserlar formalo l[erranrienlas Dalos t/enlan t-

A 1 Y f *

t

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

"l,nj-*- A x

Figura 1.L1. Men con las hojas disponibles que se muestra al hacer clic con el botnderecho sobre los botones de desplazamiento entre hojas.

1 . Reduci r la f rust rac in en los l ibros y en las hojas de clculo 47

Para vincular ese men con el hecho de hacer clic con el botn derecho delratn en cualquier celda, escriba el siguiente cdigo en VBE:

Private Sub V' lorkbook_SheetBeforeRightCl ick(Byval Sh As Object , ByvalTarget As Range, Cancel As Boolean)Dim ccont. As CommandBarButton

On Error Resume NextAppl icat ion. CommandBars ( "ceI1 " ) . contro ls ( " Indice de hoj as " ) . De1eteOn Error GoTo 0

Se ! ccon t = App l i ca t i on . commandBars ( " ce1 l ' ) .Con t ro l s .Add _

( Type : =msoControlBut ton, Temporary: =True )Irith cCont.

.Cap t i on = " I nd i ce de ho jas ". OrrAction = 'r IndexCode "

Eino w l tnE;no suD

A continuacin, deber insertar un mdulo estndar que almacene Ia macro"IndexCode", que es llamada por este cdigo que acabamos de introducir en elmomento en el que el usuario hace clic con el botn derecho del ratn en unacelda. Es fundamental que utilice un mdulo estndar a continuacin, ya que sicoloca el cdigo en el mismo mdulo que el cdigo anterior, Excel no sabr dndeencontrar una macro llamada "IndexCode". Seleccione InsertapMdulo v escribael siguiente cdigo:

Sub IndexCode ( )Appl i cat ion. CommandBars (

" rlrorkbook Tabs " ) . Sho$tPopup

Eino su.o

Pulse AltlComando-Qpara volver a la ventana principal de Excel. A conti-nuacin haga clic con el botn derecho en cualquier celda y ver un nuevo ele-mento de men llamado "ndice de hojas", que al seleccionarlo mostrar un listadode todas las hojas que contiene este libro.

Limitar el rango de desplazamento de la hojade clculoSi se desplazaa menudo por la hoja de clculo o s i t iene datos que nodesea que sean visual izados por los lectores, puede ser t i l l imitar el reavisible de la hoja de clculo slo al rango que actualmente t iene datos.

Todas las hojas de Excel creadas a partir de Excel 97 disponen de 256 colum-nas (de la A a la IV) y de 65.536 filas. En la mayora de los casos, las hojas sloutilizarn un pequeo porcentaje de todas las celdas disponibles. Existe la posibi-lidad de establecer el rea por el que se puede desplazar el usuario de forma queslo puedaver los datos que desee. Luego, puede colocar datos que no deben ser

t;ll : i l i l

l i s

,l:li \t7

4B Excel. Los mejores trucos

vistos fuera de esa rea. Esto tambin puede hacer ms sencillo desplazarse poruna hoja de clculo y que los usuarios no se encuentran en la fila 5o.ooo pratener que empezar a buscar los datos que desea.

La manera ms sencilla para establecer los lmites es simplemente ocultar to-das las columnas y filas que no se utilizan. Estando en una hoja, localice la lti-ma fila que contiene datos y seleccione la fila entera que est debajo de ella haciendoclic en el selector de fila. Mantenga pulsadas las teclas control y Mays mien-tras pulsa la tecla Flecha abajo para seleccionar todas las filas hacia abajo. Se-leccione entonces la opcin Formato>Fila>ocultar para ocultarlas todas. Hagaesto mismo para las filas no utilizadas: busque la ltima columna, seleccionetoda la columna siguiente y manteniendo pulsadas las teclas control y Mays,pulse la tecla Flecha derecha hasta seleccionar todas las columnas. Luego selec-cione la opcin Formato>Columna>ocultar. una vez hecho esto, el rutgo de cel-das tiles quedar rodeado de una zona gris por la que no se puede desplazar.

La segunda alternativa para establecer los lmites es especificar un rango v-lido en la ventana de propiedades de la hoja. Haga clic con el botn derecho en lapestaa de la hoja que est situada en la parte inferior izquierdo de la ventana yluego seleccione la opcin Ver cdigo. Entonces, seleccione la opcin Ver>Exploradorde proyectos (control-R en windows o Comando-R en Mac os X) para mostrarla ventana de proyectos. si la ventana de propiedades no est visible, pulse latecla F4. Seleccione la hoja adecuada y busque la propiedad ScrollArea en h rre.r-tana de propiedades (vase figura 1.12).

Introduzca entonces en el cuadro de texto de dicha propiedad los lmites parala hoja (por ejemplo, $A$1:gcgso).

una vez hecho esto, no podr desplazarse fuera del rea que haya especifica-do. Por desgracia, Excel no guarda esta configuracin despus de cerraise. Estosignifica que necesitamos una simple macro que automticamente establezca elrea de desplazamiento al rango deseado, escribiendo el cdigo para el eventoworksheet_Act ivate.

Para ello, haga clic con el botn derecho sobre la pestaa de la hoja en la quedesea limitar el desplazamiento y seleccione la opcin Ver cdigo, introduciendoa continuacin:

P r i v a t e S u b W o r k s h e e t _ A c t i v a t e (M e . S c r o l l A r e a = r r A l : G 5 0 "End Sub

Como siempre, pulse AltlComando-epara volver a la ventana principal deExcel y guardar los cambios.

Aunque en este caso no habr una indicacin clara, como pueda ser la zonagris que se mostraba con el primer mtodo, ser incapaz de desplazarse o selec-cionar cualquier cosa fuera del rea especificada.

1. Reduc i r la f rus t rac in en los l ib ros y en las ho jas de c lcu lo 49

r s GEuroTool (EUROTUOL.XLA)

r, S vr*Proiect {Libro?}- $, YBAProiect {Libro8}

i-: ,--r f"li ':r':srFt E.rcel bjel:sffil H'-rit iHr'i1El n'r':f rlHr-riIiE] t-toa: iHojasirS tFir','uot['-rnk.

Hoia3 ',,orksheet

Altahtica !rnr rateqarias I

BrlEEf E,3h-{

tiisF |'/Fii,_tl-rtT Lef l:Ent ' leAutFi l ler

alculal ir :rn

iv lTab le

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

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

Si las macros seleccionan un rango fuera del rea de desplazamiento, puedemodificarlas de forma que no estn limitadas a dicha hara mientras realicen sustareas. Para ello, simplemente seleccione la opcin Herramientas>Macro>Macros(Alt-F8), busque el nombre de la macro, seleccinela y luego haga clic en el bo-tn Modificar. Escrbala siguiente lnea de cdigo al principio del todo:

Ac t i veShee t .Sc ro l lA rea = " "

Y al final del todo de la macro. escriba:

A c t i v e s h e e t . s c r o l l A r e a = " $ A $ 1 : $ G $ 5 0 '

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

Sub MyMacro (I


M i M a c r o M a c r oM a c r o g r a b a d a e l L 9 / 9 / 2 0 0 3 b y O z G r i d . c o m

Ac t i veshee t .S . t o l 1A . " " = u "

Range ( ) .Se lec tSe lec t i on .Fon t .Bo ld = T rue

Ac t i veshee t . sc ro l lA rea = "$A$1 - :$G$50 '

Shee ts ( "P resupues to d i a r i o , ' ) . Se fec t

Ac t i veShee t .Sc roL lA rea = u "R a n g e ( ) . S e l e c tS e f e c t i o n . F o n t . B o l d = F a l s e

A c t i v e s h e e t . S c r o l l A r e a = ' $ A $ 1 : $ H $ 2 5 "

Eno sulf

Nuestra macro selecciona la celda zloo y le da formato negrita. Luego selec-ciona la hoja llamada "Presupuesto diario", selecciona la celda T5OO de dicha hojay quita el formato negrita. Hemos aadido ActiveSheet . ScrollArea = ,r rlde forma que pueda seleccionarse cualquier celda y ms adelante volvemos aestablecer los lmites del rea de desplazamiento al valor deseado. Cuando selec-ciona amos otra hoja (Presupuesto diario), volvemos a permitir al cdigo selec-cionar cualquier celda, y despus de que la macro realice sus tareas, volvemos aestablecer el rango a los lmites deseados. Un tercer mtodo, el ms flexible, limi-ta automticamente el rea de desplazamiento al rango que est siendo usado enla hoja en la que escribe el cdigo. Para utilizar este mtodo, haga clic con elbotn derecho en la pestaa de la hoja en la que desea limitar el rea de desplaza-miento, seleccione la opcin Ver cdigo y escriba lo siguiente:

Private Sub Worksheet_Act. ivate ( )Me .Sc ro l lA rea = Range (Me .UsedRange , Me .UsedRange (2 .2 ) ) .Add ress

Eino su-r)

Luego pulse AltlComando-Qo haga clic en el botn para cerrar la ventanade Visual Basic para volver a la ventana principal y guardar los cambios.

La macro anterior se ejecutar automticamente cada vez que se active lahoja en la cual introdujo este cdigo. sin embargo, puede encontrarse un proble-ma con esta macro cuando necesite introducir datos fuera del rea utilizable.Para evitar este problema, simplemente utilice una macro estndar que resta-blezca el rea de desplazamiento de nuevo a toda la hoja. Para ello, seleccione laopcin Herramientas>Macro>Editor de Visual Basic, seleccione luego la opcinInsertar>Mdulo e introduzca el siguiente cdigo:

Sub ResetScrol- lArea ( )Ac t i veShee ! .S . . o f lO r . " = u , '

ino suD

B1. Reduc i r la f rus t rac in en los l ib ros y en las ho jas de c lcu lo 5 1

Entonces pulse AltlComando-Qpara volver a la ventana principal de Excel yguardar el trabajo.

Si lo desea, puede hacer que la macro sea fcilmente accesible asignndoleuna tecla de acceso rpido. Seleccione la opcin Herramientas>Macro>Macros (AltlOpcin-F8), seleccione ResetScrollArea (el nombre que le dimos a la macro ante-rior), haga clic en Opciones y luego asigne una tecla de acceso rpido.

Cadavez que necesite aadir datos fuera de los lmites establecidos de la hoja,ejecute esta macro que quita dicha limitacin. Entonces haga aquellos cambiosque no poda hacer cuando el lmite estaba establecido y cuando haya termina-do, active cualquier otra hoja y luego vuelva a activar sta para que se vuelva alimitar el rea de desplazamiento. La activacin de la hoja har que se ejecute elcdigo inicial que escribimos, el cual limitaba el rea de desplazamiento.

Bloquear y proteger celdas que contenen frmulasQuiz desee permit i r a los usuar ios cambiar celdas que cont ienen datospero no permit i r les cambiar las frmulas. Puede mantener b loqueadas lasceldas que cont ienen frmulas s in tener que proteger toda la hoja o e l l ibro.

Cuando creamos una hoja de clculo, muchos de nosotros necesitamos utili-zar frmulas de algn tipo. A veces, sin embargo, no desear que otros usuariospuedan estropear, eliminar o sobrescribir cualquiera de las frmulas incluidas enIa hoja de clculo. La forma ms fcil y rpida de impedir que las personas jue-guen con las frmulas es proteger la hoja de clculo. Sin embargo, proteger lahoja de clculo no slo evita que los usuarios estropeen las frmulas, sino quetambin evitan que se pueda introducir cualquier informacin. Y a veces no que-rr ir tan lejos en la seguridad.

De forma predeterminada, todas las celdas de una hoja de clculo estn blo-queadas, aunque esto no tiene efecto hasta que se aplique la proteccin de lamisma. A continuacin mostramos un mtodo muy sencillo para aplicar unaproteccin a la hoja de clculo de forma que slo las celdas con frmulas estnbloqueadas y protegidas.

Seleccione todas las celdas de la hoja, bien pulsando Control/Comando-E obien pulsando el cuadrado gris situado en la interseccin de la columna A y lafila 1. Entonces vaya a Formato>Celdas>Proteger y desactive la casilla de verifi-cacin Bloqueada. Haga clic en Aceptar.

Ahora seleccione cualquier celda, seleccione Edicin>lr a (Control-I F5) yhaga clic en eI botn Especial. Ver entonces un cuadro de dilogo como el quese muestra en la figura 1.13.

Seleccione el botn de opcin Celdas con frmulas del cuadro de dilogo lr aespecial y, si es necesario, limite las frmulas a los tipos subyacentes. Luego


haga clic en Aceptar. Una vez estn seleccionadas las celdas con las frmulas,vaya a Formato>Celdas>Proteger y active la casilla de verificacin Bloqueada.Haga clic enAceptar. Ahora seleccione la opcin Herramientas>Proteger>Protegerhoja para proteger la hoja de clculo y utilizar una contrasea si es requerid.

:i,,.., , f-J5elecciunarf f,gmentarins f' DiFerencias enlre filagfl snslanies fl SiFerencias entre rolurnnes {!J$ f-tldg--qg-f-L {- celdas srecedentes

l7 Hgeros f' detdas,J+pendientesIt Tegto fl

..',, ,.. ,"..;.:,:-,i''i::: :,.:,ir:ri-?: jr j:;i--i :i.r.:.ffi valores lgrrrs f* l',.,,, r",' r: ir.i, r :i ,'f -ii;,r':l

l? g,rrores {* klrn reldaf* celdas en [lanco f sdb cel,Jas visibles {'$l* Begin artual f* Celds con lormatrs condicionales'{*' t4,3trit actual f* CeldaE con vlidacidn de ,J,alosf' bietos ul? , -, ,.

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

f-lllrrry*l _fr:l:p {

Figura 1.13. El cuadro de dilogo Ir a especial.

Este mtodo realmente ahorra gran cantidad de tiempo y elimina posibleserrores al buscar las frmulas, de forma que pueda protegerlas. por desgracia,tambin evita que los usuarios puedan utilizar otras funciones, como puede serordenar, cambiar el formato, alinear el texto y otras muchas, incluso iuando lacelda no est bloqueada. Puede solucionar este problema de dos formas:

La primera aproximacin consiste en no utilizar la proteccin de la hoja, sinola validacin de datos en lugar de ello.

La validacin de datos est lejos de ser del todo segura a la hora deevitar que los usuarios introduzcan datos no vlidos en celdas. Losusuarios todava pueden pegar en una celda con validacin cualquiertipo de dato, quitando la validacin de dicha celda a menos que lacelda original que se est copiando tambin tuviese algn tipo devalidacin, en cuyo caso tambin se estara sobrescribiendo.

Para ver a qu nos referimos, seleccione cualquier celda, seleccione la opcinEdicin>lr a y luego haga clic en el botn Especial. Ahora seleccione la opcinceldas con frmulas en el cuadro de dilogo y, si es necesario, especifiqu quetipos de frmulas desea buscar. Haga clic en el botn Aceptar.

Ahora que slo tenemos seleccionadas las celdas con frmulas, seleccione laopcin Datos>Validacin y en la pestaa Configuracin seleccione la opcin Per-sonalizada en el cuadro de lista desplegable , y en el cuadro de texto Frmula es-


criba :"", tal y como se muestra en la figura 1.14. Luego haga clic en el botnAceptar.

iliifiifontiguracin I nens"* entrante I mensae de error N

Criterio de validacidn

{? nmtir blanc,:s

| _,,,,t -

** .

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

-xJ

Figura 1.14. Frmulas de validacin.

Este mtodo evitar que un usuario sobrescriba accidentalmente una celdaque tenga una frmula (aunque como dijimos anteriormente, no es un mtodototalmente seguro y slo debera ser utilizado para evitar sobrescribir accidental-mente). De todas formas, la gran ventaja de utilizar este mtodo es que todas lasfunciones de Excel todava se pueden utilizar en la hoja de clculo.

El ltimo mtodo tambin permite utilizar todas las funciones de Excel, perosolamente cuando se encuentra una celda que no est bloqueada. Para empezar,asegrese de que solamente las celdas que desea proteger estn bloqueadas y queel resto no lo estn. Haga clic con el botn derecho del ratn en la pestaa de lahoja en cuestin, seleccione la opcin Ver cdigo e introduzca el siguiente cdigo:

Private Sub Worksheet_Select ionChange (ByVa1 Target As Range)I f Ta rge t . Locked = T rue Then

Me . P ro tec t Password : = t 'Sec re ta "-E;-LSe

Me. Unprotect Passr4rord : = " Secreta"Eino r r

EJNd SUlf

Si no desea uti l izar una contrasea, omita la parte Password : =trSecretart.Si por el contrario quiere utilizar una entonces debe cambiar la palabra "Secreta"por aquella contrasea que desee. Luego pulse AltlComando-Qo cierre la ven-tana para volver a Excel y guardar los cambios. Ahora, cada vez que seleccioneuna 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, lahoja se desbloquear.


Gt {El .JI l ) l

li!i{ii)v

Este truco no funciona perfectamente, aunque normalmente funcionalo 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 unrango de celdas (con la celda activa estando desbloqueada), puedee l im inar la se lecc ione en tera porque la ce lda ob je t i vo es tabadesbloqueada y, por tanto, la hoja se ha desprotegido automticamentea s misma.

Encontrar datos duplcados utlizando el formatocondicionalEf formato condicional de Excel se ut i l iza normalmente para identi f icarvalores en rangos en part icular, pero podemos usar un truco con estacaracterst ica para identi f icar datos duplicados dentro de una l ista o unatabla.

Normalmente la gente tiene que identificar datos duplicados dentro de unalista o tabla. Hacer esto manualmente puede llevar mucho tiempo y a veces sepueden cometer errores. Le aconsejamos que para hacerlo mucho ms sencillo,utilice un truco sobre una de las caractersticas estndar de Excel, el formatocondicional.

Tomemos, por ejemplo, una tabla con datos en el rango $A$1:$H$100. Selec-cione la celda superior izquierda (A1 ) y arrastre el cursor del ratn hasta la celdaH1oo. Es importante queAl sea la celda activa en la seleccin, por lo que no es lomismo seleccionar primero la celda H1OO y luego arrastrar hasta la celda A1 . Se-leccione entonces la opcin Formato>Formto condicionaly, en el cuadro de dilogoFormato condicional, seleccione la opcin Frmula en el primer cuadro de listadesplegable. En el campo que hay a su derecha, introduzca el siguiente cdigo:

=CoNTAR. S I ( gAg l : gHg100 ; 41 ) >1

Haga clic en el botn Formato... y luego seleccione la pestaa Tramas y selec-cione un color que desee aplicar para identificar visualmente los datos duplica-dos. Haga clic en Aceptar para volver al cuadro de dilogo anterior y vuelva ahacer clic en Aceptar para aceptar el formato.

Todas aquellas celdas que contengan datos duplicados deberan aparecer aho-ra como un rbol de Navidad con el color que eligi, haciendo mucho ms senci-llo el hecho de localizar datos duplicados para as poder eliminarlos, moverlos, oalterarlos.

Es muy importante comentar que como la celda A1 era la activa, la direccinde la celda es una referencia relativa y no absoluta, como en la tabla de datos,

1 . Reduci r la f rust rac in en los l ibros y en |as hojas de clculo 5 5

$A$1:$H$100. Utilizando el formato condicional de esta forma, Excel sabeautomticamente que debe utilizar la celda correcta como el criterio de la fun-cin coltaR. SI. Con esto queremos decir que la frmula de formato condicio-nal en la celda A1 se leera as:

=CoNTAR. S I ( $A$1 ; $H$100 ;A1 ) > l -

Mientras que en la celda A2, se leera:

=CoNTAR. S I ( $A$1 : $H$100 ;A2 ) >1

En la celda A3 se leera:

=CoNTAR. s r ( $A$1 : $H$100 ;A3 ) >1

y as sucesivamente.Si necesita identificar datos que aparecen dos o ms veces, puede utilizar el

formato condicional con tres condiciones diferentes y cdigos de color para cadauna de las condiciones, todo ello para conseguir una identificacin visual. Parahacer esto, seleccione la celda A1 (la celda que est situada en la parte superiorizquierda de la tabla) y arrastre el cursor del ratn hasta la celda H1OO. De nue-vo, es importante que la celda A1 sea la celda activa en la seleccin.

Ahora seleccione la opcin Formato>Formato condicional y seleccione la op-cin Formato en el cuadro de lista desplegable. En el cuadro de texto situado a suderecha, introduzca el siguiente cdigo:

= C O N T A R . s r ( $ A $ 1 : $ H $ 1 0 0 ; A 1 ) > 3

Haga clic en el botn Formato... y luego vaya a la pestaa Tramas para selec-cionar el color que desee aplicar para identificar los datos que aparecen ms detres veces. Haga clic en Aceptar, luego haga clic en el botn Agregar y en elcuadro de lista desplegable para la Condicin 2, seleccione la opcin Frmula yluego escriba el siguiente cdigo en el cuadro de texto situado a su derecha:

=CoNTAR. S r ( $A$1 : gHg100 ;A l_ ) =3

A En vez de tener que reescribir la frmula, mrquela en el cuadro de\* texto de la Condicin 1, pulse la tecla Control,/Comando-C para

\ copiarla en el portapapeles, haga clic en el cuadro de texto de la* Condicin 2, pulse Control,/Comando-V para pegarla ah y luego

cambie>3 Dor =3.

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


mente tres veces. Haga clic en Aceptar y luego haga clic en Agregar. En el cua-dro de lista desplegable de la Condicin 3, seleccione la opcin Frmula y escribalo siguiente en el cuadro de texto situado a su derecha:

=CoNTAR. S I ( $A$1 : gHg l -00 ;A ] - ) =2

Para terminar, haga clic en el botn Formato..., elija la pestaa Tramas yseleccione ah el color que desea aplicar a los datos que aparecen exactamente dosveces. Luego haga clic en el botn Aceptar. Ahora ya tenemos colores diferentesde celda dependiendo del nmero de veces en el que aparecen los datos dentro dela tabla. De nuevo, es importante recordar que la celda A1 debe ser la celda activaen la seleccin, puesto que la direccin de celda es una referencia relativa y noabsoluta, como en la tabla de datos, $A$1:$H$100. Utilizando el formato condi-cional de esta forma, Excel sabr utilizar la celda correcta como criterio de lafuncin coNTAR.sr.

Asociar barras de herramientas personalizadasa un libro en partcularA pesar de que la mayora de barras de herramientas que cree sirven paraprcticamente todos los l ibros con los que trabaje, a veces la funconalidadde una barra de herramientas person al izada solamente es apl icable a unl ibro en part icular. Con este truco podremos asociar barras de herramientaspersonalizadas a sus respectivos l ibros.

si nunca ha creado una barra de herramientas personarizada, sin duda sehabr dado cuenta que las barras herramientas se cargan y son visibles indepen-dientemente de que libro tenga abierto. ZQu ocurre si su barra de herramientaspersonalizada contiene macros grabadas que slo tienen sentido con un libro enparticular? Probablemente es mejor poder asociar barras de herramientas perso-nalizadas cuyo propsito sea especial con los libros apropiados, para as evitarcualquier tipo de confusin y otros problemas. Podemos hacer esto insertandoun cdigo muy sencillo en el mdulo privado del libro. Para acceder al mduloprivado, haga clic con el botn derecho en el icono de Excel, que encontrar en laesquina superior izquierda de la pantalla, cerca del men Archivo, y luego selec-cione la opcin Ver cdigo.

nLJt l ?

J t;,,it\:./

u-\ \

Es