p. 1

ya sé excel pero necesito más Índice introducción 12 he visto con mis propios ojos 14 yo siempre he trabajado así 16 convenciones 17 suposiciones 19 sobre la versión 2007 21 un recordatorio rápido 23 archivos 23 extensiones de archivos 23 plantillas por defecto para hoja y libro 24 vínculos 24 fórmulas 26 referencias absolutas y relativas 26 extender fórmulas 27 rellenar y series 28 nombres 31 complementos 35 moverse con el teclado 37 edición 39 pegado especial 39 seleccionar celdas especiales 42 justificar texto 43 comentarios 45 visualización 47 modos de ver las referencias 47 ver información en la barra de estado 49 vista previa de salto de página 50 www.necesitomas.com 5

[close]

p. 2

ya sé excel pero necesito más inmovilizar movilizar paneles 51 dividir ventana 52 varias ventanas de una misma hoja de cálculo 53 vistas personalizadas 55 Área de trabajo 56 ver los nombres de los rangos 57 ver las fórmulas 57 ver las macros disponibles 57 ver el editor de visual basic 58 ver todos los comandos de los menús 58 tomar fotos de celdas 59 con estilo 60 estilos de celda 60 temas 62 imprimir 64 encajar en una página 64 centrado horizontal vertical 65 repetir filas columnas 65 imágenes en encabezados y pies de página 66 ayuda 67 grupos de funciones para no olvidar 68 búsquedas y referencias 69 funciones de base de datos 72 convertir unidades 75 funciones de información 75 funciones para errores 76 funciones de texto 77 funciones de fecha 78 números complejos 79 redondeo 79 www.necesitomas.com 6

[close]

p. 3

ya sé excel pero necesito más fórmulas matriciales 81 formato 85 formato de número personalizado 85 números 88 fechas 92 condicionales 94 alineación de celda 95 formato condicional 98 gráficos 101 eje de abscisas proporcional a los valores 101 eje secundario 103 representación de valores nulos 104 línea de tendencia 107 detectar y corregir errores 110 auditoría de fórmulas 110 prevención de errores 112 acerca de los datos 114 filtrado 117 ordenar 122 validación 123 esquema 127 subtotales 130 consolidar datos 132 a mano 133 a máquina 134 tablas 137 obtener datos externos 143 www.necesitomas.com 7

[close]

p. 4

ya sé excel pero necesito más ventajas 144 el proceso de obtener datos externos 145 desde archivo de texto 147 desde una página web 152 desde access 155 desde un servidor sql 156 a través de query 159 propiedades de un rango de datos externos 166 desde texto y web 166 desde bases de datos 169 propiedades de la conexión 170 consultas con parámetros 173 lenguaje sql 175 modificar una consulta existente 176 eliminar la conexión con un origen de datos 177 tablas dinámicas 179 creación 180 configurar los campos 184 agrupar 187 mostrar y ocultar detalles 189 campos calculados 191 elementos calculados 192 filtro de informe 194 configuración de la tabla 195 varias tablas dinámicas con los mismos datos 198 gráfico dinámico 199 análisis y si 200 tablas de 1 y 2 variables 201 escenarios 204 www.necesitomas.com 8

[close]

p. 5

ya sé excel pero necesito más resolver problemas 210 cálculos iterativos 210 buscar objetivo 212 solver 215 facilitando el trabajo a los demás 221 controles de formulario en tus hojas 221 proteger el libro 224 macros y funciones 228 ten confianza en ti 229 lee esto antes de grabar una macro 230 vba en dos palabras 233 el entorno de programación 234 depuración 239 lo básico de visual basic 241 tipos de procedimientos 241 variables 245 constantes 249 bucles 252 varias instrucciones con un mismo objeto 256 condicionales 256 interactuar 260 tratamiento de errores 267 funciones disponibles en vba 270 las ideas claras 272 organización 272 opciones del módulo 273 alcance 274 desplazarse por las hojas 275 nomenclatura útil 277 www.necesitomas.com 9

[close]

p. 6

ya sé excel pero necesito más configuración regional 278 bien está lo que bien acaba 278 guarda tus procedimientos 279 añadir una descripción y categoría al procedimiento 279 proteger el código 282 algunos bucles y ejemplos útiles 282 recorrer todos los libros abiertos 283 recorrer todos los escenarios 283 recorrer todas las vistas personalizadas 284 recorrer todas las celdas del rango seleccionado 284 recorrer todas las hojas del libro 285 recorrer las hojas seleccionadas 286 recorrer una lista de datos 287 recorrer todas las hojas de gráfico 288 recorrer las hojas de gráfico seleccionadas 289 recorrer todos los gráficos de una hoja 290 abrir y cerrar libros 291 generar escenarios 292 ganando tiempo al tiempo 297 epílogo 298 Índice alfabético www.necesitomas.com 10

[close]

p. 7

ya sé excel pero necesito más www.necesitomas.com 11

[close]

p. 8

ya sé excel pero necesito más introducción en la mayor parte de los libros de excel que he consultado y en los cursos a los que asistí como alumno con los que he ido aprendiendo a lo largo de los años he notado que ciertos aspectos del programa tal vez por más infrecuentes no se comentan siquiera o no están tan explicados como otros más comunes puede que no todo el mundo tenga que manejar demasiados datos y por ello determinadas funcionalidades del programa no se consideran de interés general para los usuarios del programa o parece que no se les da tanta importancia pues no está clara la ventaja de su utilización cuando se maneja un escaso volumen de información no comparto esta opinión puesto que desconocer hasta dónde puede dar de sí éste y cualquier otro programa que se utilice supone que llegado el caso de enfrentarse a un caso no convencional se plantee su solución de manera artesanal con una pérdida de tiempo considerable en tareas que podría hacer en este caso excel automáticamente y con una alta posibilidad de equivocarse en estos procesos manuales en este libro he tratado de recopilar esas capacidades del programa de uso no tan habitual que en el ejercicio de mi trabajo como ingeniero de caminos para el que excel no es sino una herramienta más han supuesto una mejora más notable de la productividad esto es realizar más trabajo que antes en el mismo tiempo o realizar el mismo trabajo en menos tiempo excel tiene muchas capacidades por supuesto pero sólo he querido incluir las que me han sido de utilidad en alguna ocasión con esto quiero decir que sólo he considerado en los capítulos que componen este libro funcionalidades que he probado por mí mismo por lo que puedo decir que he sacado provecho de las mismas y también que he conocido y a veces sufrido sus limitaciones para aquellos que estén empezando o que no lleven demasiado tiempo trabajando con el programa y para los que llevan tanto tiempo como para haber olvidado algunas cosas básicas he incluido un recordatorio rápido de los aspectos generales más interesantes desde mi punto de vista tanto porque se emplearán más adelante en este libro como porque tienen un gran potencial en sí mismos la mayoría de las veces desaprovechado www.necesitomas.com 12

[close]

p. 9

ya sé excel pero necesito más no debes olvidar querido lector que salvo que seas el gurú de la informática en tu trabajo excel es una herramienta más por tanto no es el objetivo en sí mismo sino un medio para conseguir hacer tu trabajo de una manera más eficiente cuanto menos tiempo se dedique a la fase operativa a las tareas repetitivas y mecánicas más tiempo nos queda para la fase creativa para la innovación para la mejora para la fase humana en definitiva es por esto que se hace fundamental conocer lo mejor posible las herramientas que se pueden utilizar conocer sus posibilidades y el alcance de las mismas para llegado el momento de implementar una solución para un problema que se nos plantee poder resolverlo de forma rápida eligiendo la herramienta adecuada para sacarle el máximo partido sin tener que volver atrás a hacer grandes rectificaciones posteriormente con los trucos consejos explicaciones contenidas en este libro trato de ayudar a los usuarios de esta poderosa arma que es excel a conocerla mejor a conocer unas posibilidades más avanzadas para manejar datos operar con ellos resumirlos manipularlos presentarlos trato en definitiva de ayudar a que el ordenador trabaje para ti y no al contrario espero que la lectura de este libro bien a modo de guía o manual de formación bien como referencia ocasional para un tema específico te sea de utilidad y logres recuperar con creces el tiempo que inviertas con él entre tus manos www.necesitomas.com 13

[close]

p. 10

ya sé excel pero necesito más he visto con mis propios ojos hay ocasiones en que el engranaje que une nuestra manera de pensar con el funcionamiento del ordenador no encaja a la perfección a lo largo del tiempo he visto cosas que me han llamado especialmente la atención y tengo que comentar algunas ideas al respecto no hay que volver a teclear algo que ya se ha introducido anteriormente en el ordenador evitamos errores en la transcripción lo más importante pero también es una pérdida de tiempo en algo que ya estaba hecho lo mismo puede decirse con las salidas de un programa cuando queremos analizarlas con otro programa siempre que se pueda hay que evitar volver a teclear he visto con mis propios ojos cómo se imprimían hojas y hojas de papel para volver a introducir los datos impresos hay que guardar los datos una vez introducidos si estás introduciendo la información en el ordenador no está de más que la almacenes allí porque antes o después volverás a necesitarla he visto con mis propios ojos cómo se escribían los datos en una hoja excel se imprimía una ficha se sobrescribían los datos se imprimía la siguiente ficha así hasta el infinito sin guardar ninguna ¿y si quisiésemos un listado otra vez a teclear hay que evitar anotar la misma información en diferentes lugares el objetivo es anotar el dato una sola vez con lo que evitamos errores tipográficos errores por emplear una versión de los datos que no esté actualizada y por supuesto evitamos pérdidas de tiempo luego de esos datos generaremos todos los resúmenes que sean necesarios he visto con mis propios ojos cómo se introducían los datos en una hoja resumen conjunta en otras hojas según la fecha de los datos en otras hojas según la procedencia de los datos así hasta cinco o seis veces según otros tantos criterios de ordenación ¿y si quisiésemos agruparlos por otro criterio me ofrezco voluntario para no teclear esos datos otra vez no basta con tener los datos en un mismo archivo si tienes que manejar muchos datos con excel más de dos datos empiezan a ser ya muchos júntalos todos organizándolos por filas cada fila un registro diferente cada columna un campo diferente he visto con mis propios ojos cómo se perdía un tiempo precioso agrupando y reorganizando los datos para que excel pudiera operar adecuadamente con ellos no haber concebido bien la hoja desde el principio lo que requiere pensar tan solo unos segundos ocasiona más adelante trastornos y limitaciones a la www.necesitomas.com 14

[close]

p. 11

ya sé excel pero necesito más hora de interpretar y representar los datos y por supuesto acaba generando el trabajo adicional de poner las cosas en orden no hay que realizar operaciones matemáticas por sencillas que sean siempre vamos a ser más lentos que la máquina y puede que nos equivoquemos he visto con mis propios ojos cómo se utilizaba excel con una calculadora de bolsillo encima de la mesa en la que se realizaban las operaciones para comprobar porque no se fiaban del resultado que proporcionaba el ordenador los errores en las operaciones suelen deberse a equivocaciones en la introducción de las fórmulas o de los datos errores humanos es muy raro encontrar un error de la máquina el ordenador no piensa sólo hace lo que le mandan y muy deprisa no digo que no haya que asegurarse de que la hoja funciona correctamente pero lo que hay que comprobar no son las operaciones sino las fórmulas que hemos introducido para ver si calculan lo que queríamos calcular u otra cosa diferente no hay que resumir los datos a mano ni contar ni sumar ni hallar el máximo o el mínimo ni agrupar por fechas ni por categorías ni nada por el estilo de eso ya se ocupa excel sólo hay que tener la precaución de tener los datos de tal modo que el programa los pueda entender excel y cualquiera he visto con mis propios ojos cómo se sumaban y volvían a sumar a mano las cuentas de gastos de varios trabajos similares almacenadas en archivos diferentes por no tener una estructura homogénea no sólo entre diferentes archivos sino entre el principio y el final de un mismo archivo que permitiese al ordenador entender los datos para poder sumarlos hay que ser organizado y claro para facilitar la comprensión de nuestro trabajo a los demás puede que una hoja para el seguimiento de los gastos de la casa sólo la utilices tú con lo que no tendrás problemas pero si utilizas excel en el trabajo antes o después los libros pasarán por otras manos he visto con mis propios ojos cómo alguien era incapaz de explicar qué era lo que presentaba en papel qué eran los datos y qué estaba calculado tan sólo unos pocos días después de haber realizado el trabajo no quiero ni pensar qué sería capaz de recordar un año más tarde hay que documentarse estudiar y practicar no basta con tener un buen ordenador con muchos programas instalados hay que saber manejarlos y eso sólo se consigue invirtiendo tiempo en estudiar y poner en práctica lo aprendido para resolver problemas y si son problemas reales mejor que ejemplos ficticios he visto con mis propios ojos a personas quejándose de que su equipo de música no funcionaba www.necesitomas.com 15

[close]

p. 12

ya sé excel pero necesito más cuando la solución al problema estaba en la segunda página del manual de instrucciones que aún no habían abierto yo siempre he trabajado así el ordenador no es sólo una máquina de escribir sofisticada con muchas fuentes y posibilidad de incorporar colorines y dibujitos que por cierto lo hace muy bien y no digo que no sea para lo que más se utilice un ordenador puede ir mucho más allá a la hora de ayudarnos con nuestro trabajo de hecho puede hacer la mayor parte de muchos trabajos de oficina o gabinete sin embargo a veces resulta difícil ver hasta dónde te puede ayudar la máquina sobre todo si has estado realizando el trabajo manualmente durante bastante tiempo como casi todo pienso que es cuestión de práctica de prestar atención a lo que hacen los demás a tu alrededor y de no tener miedo a dar pasos aunque sean pequeños hacia delante muchas veces estamos acostumbrados a trabajar de una determinada manera y somos muy reticentes a introducir cambios aunque nos aseguren que van a resultarnos de utilidad antes de contestar no es que yo siempre he trabajado así dale una oportunidad a las nuevas ideas y analiza las ventajas e inconvenientes que conllevan en ahorro de tiempo en reducción de errores en esfuerzo en complicación comparándolas con tu forma actual de trabajar si no te aportan nada sigue como hasta ahora pero no dudes en incorporarlas a tu trabajo en hacerlas tuyas si suponen una mejora no te quedes estancado avanza www.necesitomas.com 16

[close]

p. 13

ya sé excel pero necesito más convenciones puesto que en el mundo hay personas diestras y zurdas empleo la denominación de botón principal del ratón para referirme al que se emplea para seleccionar y botón secundario del ratón al utilizado para mostrar menús contextuales en la maquetación del libro se han empleado estos estilos para resaltar cierta información consejos trucos textos destacados ruta de menús en versiones anteriores ruta de menús en versión 2007 código de programación visual basic para aplicaciones las combinaciones de teclas se mostrarán entre corchetes [ctrl fin subtotales nombres de las funciones en mayúsculas para los ejemplos de este libro se ha empleado la versión excel 2007 beta 2 technical refresh 12.0.4407.1004 en español de españa las funciones figuras y referencias al programa están en este idioma que en la codificación que emplea microsoft es el 3082 c archivos de programa microsoft office office 3082 se emplean las denominaciones libro y hoja del mismo modo que excel un libro es un archivo que puede contener varias hojas de cálculo se suele generalizar y llamar hojas de cálculo a los archivos que utiliza excel supongo que debido a que hasta la versión 4 del programa sus archivos únicamente podían contener una hoja existiendo un tipo de archivo diferente denominado libro que podía agrupar en cierto modo varios archivos de hoja de cálculo todo esto claro está ya no tiene sentido aunque permanezca la confusión de nombres en español las cosas tienen género y cuando tenga que referirme a excel lo haré con género masculino pensando que es un programa informático aunque soy consciente de que hay también bastante gente se refiere a excel en femenino supongo que pensando en una hoja de cálculo por último no hay que olvidar que microsoft es una empresa estadounidense y que la versión original de excel y la concepción inicial del programa están en www.necesitomas.com 17

[close]

p. 14

ya sé excel pero necesito más inglés lo que saldrá a la luz antes o después sobre todo en la programación de macros y con los caracteres empleados para separación de decimales y millares en general no suele dar problemas por eso cuando ocurre no lo esperas en españa utilizamos la coma como separador decimal sin embargo no hay que olvidar que la mayor parte del mundo emplea el punto para este fin excel emplea la configuración definida en el equipo para interpretar y mostrar los números no obstante en ciertas operaciones por ejemplo mostrar escenarios cuando una hoja pasó por un ordenador con configuración de coma y luego por otro ordenador con configuración de punto es posible que excel interprete como texto lo que aparentemente es un número personalmente hace tiempo que opté por configurar mi ordenador con punto para los decimales y espacios para separar los miles evitando así conflictos con programas pensados exclusivamente para funcionar con puntos que no es el caso de excel www.necesitomas.com 18

[close]

p. 15

ya sé excel pero necesito más suposiciones supongo que ya has trabajado algo con excel lo suficiente como para estar familiarizado con él y como para haber tenido que enfrentarte a él en la resolución de algún problemilla es éste un manual avanzado por lo que no va a contener explicaciones sobre aspectos básicos comunes a la mayoría de los programas informáticos como abrir un archivo o guardarlo ni tampoco sobre los conceptos básicos de excel como introducir funciones o ajustar el ancho de las celdas por poner unos ejemplos supongo que todo esto ya lo conoces de sobra supongo también que sabes que puedes programar macros y funciones personalizadas en excel mediante visual basic para aplicaciones no supongo que sepas programar sólo supongo que sabes que existe pese a que en este libro no se trata la programación de macros en excel de una manera exhaustiva para conseguir un ahorro importantísimo de tiempo en las tareas repetitivas sí resulta útil recurrir a las macros y le dedico un amplio capítulo a ello incluyendo algunos ejemplos interesantes dado que excel no ha experimentado cambios sustanciales en los últimos años prácticamente todo lo explicado en este libro es válido en algunos casos con ligeras variaciones al menos para las versiones 2000 2002 xp 2003 y 2007 la esencia del programa permanece inalterada desde hace bastante tiempo y los cambios de una versión con la anterior se limitan a cuestiones de forma más que de fondo las figuras incluidas y las indicaciones de cómo emplear los comandos se refieren a la última versión del programa si bien en los casos necesarios se hace también referencia a las anteriores supongo también que sabes que la apariencia de las ventanas de los programas en windows puede modificarse a gusto del usuario en lo que se refiere a los colores botones esquinas redondeadas animaciones etc no soy muy partidario de esta personalización llevada al extremo aunque entiendo que tiene su público simplemente te comento que opté por el estilo clásico que ofrece windows xp con la menor cantidad posible de abusos estéticos y es lo que aparecerá en las capturas de pantalla que ilustran este libro www.necesitomas.com 19

[close]