EXCEL, LA HERRAMIENTA DEL MUNDO LABORAL Aprenda Excel desde cero de una manera eficiente
© Iván Pinar Domínguez, 2015 Reserv ados todos los derechos. No se permite la reproducción total o parcial de esta obra, ni su incorporación a un sistema inf ormático, ni su transmisión en cualquier f orma o por cualquier medio (electrónico, mecánico, f otocopia, grabación u otros) sin autorización prev ia y por escrito de Iv án Pinar Domínguez. La inf racción de dichos derechos puede constituir un delito contra la propiedad intelectual.
INDICE 2
I. II.
INTRODUCCIÓN MANEJO BÁSICO
Crear nuevo libro Compartir libro Formato Autoajuste de columnas
III.
TABLAS
IV.
GRÁFICOS
V.
ORDENACIÓN DE DATOS
VI.
TEXTO EN COLUMNAS
VII. VIII. IX.
VALIDACIÓN DE DATOS QUITAR DUPLICADOS USO DE FILTROS
X.
FILTROS AVANZADOS
XI.
TABLAS DINÁMICAS
XII.
GRÁFICOS DINÁMICOS
XIII.
FÓRMULAS EN EXCEL
FUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS FUNCIONES DE TEXTO FUNCIONES LÓGICAS FUNCIONES DE INFORMACIÓN Y BÚSQUEDA
XIV. XV. XVI. XVII. XVIII.
FORMATO CONDICIONAL ORGANIZACIÓN DE VISTA DE TRABAJO GRABAR MACRO MANEJO EFICIENTE RESUMEN FINAL
3
PRÓLOGO Tras el desembarco en el mundo laboral después de realizar la correspondiente titulación univ ersitaria, todos pensamos que manejaremos las múltiples herramientas sof tware que hemos ido utilizando en nuestra rama específ ica, sin embargo al poco tiempo te das cuenta de que esto no es más que f ruto de nuestros deseos de amortizar el conocimiento adquirido durante esos años de f ormación y que la realidad es otra bastante dif erente, y a que la herramienta más utilizada por el 90% de las personas una v ez consiguen un empleo son las Hojas de Cálculo en sus múltiples v ariantes, aunque principalmente Excel y que es en la cual nos enf ocaremos en este libro, aunque el conocimiento que adquirirá usted puede extrapolarse al resto de aplicaciones sof tware similares.
Este libro está orientado a todos aquellos que deseen aprender a utilizar Excel ef icientemente y que tengan un mínimo conocimiento de of imática. No son necesarios conocimientos prev ios con la herramienta y a que comenzaremos con una introducción al manejo básico aunque iremos prof undizando en cada uno de los aspectos de Excel como tablas simples y dinámicas, gráf icos simples y dinámicos, f iltros sencillos y av anzados, f órmulas en Excel, f ormatos condicionales, grabación de macros y las múltiples posibilidades que la herramienta nos brinda. Por tanto este libro también es muy recomendable para aquéllas personas que tengo conocimiento prev io de Excel.
Estoy conv encido de que al f inalizar la lectura se le abrirá un gran 4
abanico de posibilidades para aplicar en su trabajo y en el día a día personal que le ay udarán a ser más ef iciente.
5
I. INTRODUCCIÓN El objetiv o de este libro es proporcionar al lector el conocimiento necesario para manejar ef icientemente las herramientas de Hoja de Cálculo. No es necesario conocimientos prev ios de la herramienta para poder seguir los pasos expuestos.
La metodología seguida en este libro trata de buscar un aprendizaje continuo y de dif icultad creciente conf orme se av anza en el mismo, comenzando desde el manejo más básico y genérico para cualquier aplicación sof tware hasta la grabación de macros para automatización de tareas con lenguaje VBA. Durante todo este recorrido se prof undizará en tablas, gráf icos, f iltros, f ormatos, f órmulas propias de Excel, ordenación de datos, f ormatos condicionales, organización de v istas y demás particularidades de gran utilidad.
Los ejemplos mostrados son específ icos de Microsof t Excel extrapolables a cualquier otra herramienta de similares características. Le aconsejo que tras cada capítulo practique con la herramienta para asentar el conocimiento adquirido.
6
II. MANEJO BÁSICO Comenzaremos con el manejo básico de la herramienta como no podía ser de otra manera. Una v ez abramos la Hoja de Cálculo, tenemos una serie de acciones básicas que muy probablemente el lector hay a realizado en múltiples ocasiones:
7
Crear nuevo libro Tras abrir la aplicación , podemos crear un nuevo libro seleccionando
Archivo » Nuevo libro _En las diferentes versiones de Excel vaña ligeramente la creación de un nuevo libro pero que podrá ident ificar fácilmente _ El nuevo libro creado de manera general contiene 3 pestañas presentando un aspecto como el siguiente:
Al G
10
Peslañas dellibro
1l 12
J,_______ 4
•
1
~
ojal 8
Guardar e indicamos el nombre y ruta donde guardarlo al igual que con cualquier otra aplicación. Si lo desea, puede guardar el libro con contraseña de apertura y de escritura si una v ez se abre la v entana para guardar pulsa en Herramientas >> Opciones generales y selecciona la contraseña deseada.
14
Compartir libro Una opción interesante si v arias personas v an a acceder al mismo libro Excel y queremos que todos puedan a la v ez editar el libro (muy útil en cualquier empresa o grupo de trabajo) es la opción de “Compartir libro”. Para ello pulsamos en el menú superior en REVISAR >> Compartir libro. Se nos abrirá un cuadro de diálogo donde tenemos que seleccionar la opción que permite la modif icación de v arios usuarios a la v ez:
15
nuev o que ha insertado sobrescribiendo lo anterior. Como consejo, la opción de compartir libro requiere una coordinación prev ia entre los usuarios para que cada uno sepa perf ectamente cual es lo que le compete modif icar. También es útil que antes de hacer modif icaciones guarde el f ichero y a que esto hace que automáticamente se actualicen las celdas que el resto de usuarios hay an guardado en el documento hasta ese momento.
17
Formato Una v ez insertada la inf ormación en bruto en cada una de las celdas (generalmente en celdas contiguas f ormando una estructura de tabla), para que la inf ormación sea más legible y v isualmente más agradable debemos dar f ormato a nuestros datos. Este paso es similar para las múltiples herramientas sof tware, entre las opciones de f ormato podemos seleccionar las siguientes (estas opciones se encuentran en la pestaña Inicio de Excel): 1.
Fuente: Seleccionamos las celdas a las que aplicar el f ormato (click izquierdo del ratón y sin soltar arrastramos sobre las celdas de interés, si queremos seleccionar celdas salteadas mantenemos pulsado la tecla CTRL y pinchamos en las dif erentes celdas). A continuación seleccionamos entre las opciones que se nos presenta: i) Negrita/Cursiv a/Subray ado (1 en la imagen posterior) ii) Bordes (2 en la imagen posterior) iii) Color de relleno de celda y de f uente (3 en la imagen posterior) iv ) Tipo de letra (4 en la imagen posterior ) v) Tamaño de letra (5 en la imagen posterior) 18
III. TABLAS La mejor manera de estructurar la inf ormación es mediante tablas, es uno de los puntos f uertes de las aplicaciones de Hojas de Cálculo como por ejemplo Excel. Usted puede representar los datos en f ilas y columnas contiguas con los datos en bruto y aplicar el f ormato correspondiente manualmente que se ha v isto anteriormente. Sin embargo, hay una característica importante una v ez tenemos los datos en bruto y es la opción de Insertar >> Tabla en Excel, a partir de la cual se puede dar un f ormato predef inido y podemos ref erenciar la tabla completa con un nombre concreto, lo que será útil de cara a operar con los datos como v eremos posteriormente en el capítulo Fórmulas en Excel. La mejor manera de aclarar el concepto es mediante un ejemplo, supongamos que tenemos los siguientes datos en bruto:
25
de los datos en bruto que le abrirá un gran abanico de posibilidades.
31
concretos de nuestro día a día.
Con este ejemplo se f inaliza el capítulo de gráf icos, inv ito al lector a que practique con los múltiples tipos de gráf ico para que sepa en cada momento el que mejor representa la inf ormación que desea transmitir.
47
V. ORDENACIÓN DE DATOS Es habitual tener grandes cantidades de datos sin ordenar o bien ordenados conf orme un criterio que no es el deseado. En Excel hay una opción muy útil para ordenar los datos según los campos que más nos conv engan y por niv eles. Lo v eremos con un ejemplo, imaginemos que tenemos los siguientes datos de alumnos de primaria:
48
VI. TEXTO EN COLUMNAS En este apartado analizaremos la característica de Excel que permite separar la inf ormación de una celda en columnas o bien los datos de una sola columna en v arias columnas. Es útil cuando obtenemos la inf ormación de una f uente en la que los datos no v ienen estructurados en f ilas y columnas como por ejemplo un archiv o de texto plano. Veamos el siguiente ejemplo donde analizaremos las dif erentes posibilidades, imaginemos que queremos ir de v iaje de Madrid a París y hemos encontrado los siguientes v uelos disponibles, descargando la inf ormación de un portal de internet que nos da la inf ormación en f ormato texto y que si lo abrimos con Excel contiene la siguiente estructura:
54
conseguir que la inf ormación sea más legible. Se v an a presentar las dif erentes maneras de realizarlo: Método 1: Separación en columnas de ancho fijo 1) Seleccionamos todas las celdas (o directamente toda la columna A) y pulsamos en Datos >> Texto en columnas. Se nos abrirá la siguiente v entana donde seleccionaremos “De ancho fijo”:
56
Seleccionamos todas las celdas (o directamente toda la columna A) y pulsamos en Datos >> Texto en columnas >> Delimitados. Esta opción nos permite elegir el carácter a partir del cual se div ide en columnas. 2) En nuestro caso, v emos que la inf ormación v iene separada por el carácter “;”, por tanto la opción que debemos elegir para separar por columnas es la siguiente:
61
columnas, sino que es común obtener por ejemplo el f ormato .csv (comma separated v alues) en el cual las columnas se separan por comas y las f ilas por saltos de línea.
65
VII. VALIDACIÓN DE DATOS En ocasiones, podemos requerir que una determinada celda o conjunto de celdas no puedan tomar cualquier v alor, sino que esté dentro de un rango numérico, dentro de un interv alo de f echa, que sea un v alor/cadena de una lista dada,… Para realizar esto, seleccionamos las celdas en las que queremos aplicar la v alidación de datos y pulsamos en Datos >> Validación de datos, apareciendo la siguiente v entana:
66
Longitud de texto: Permite insertar una cadena conf orme a las restricciones de longitud que especif iquemos, por ejemplo, la cadena a insertar debe tener una longitud igual a 5 caracteres. Personalizada: Que cumpla los v alores de una f órmula especif icada.
También puede elegir el mensaje de entrada, en el ejemplo indicado anteriormente en cuanto a calif icaciones, podemos especif icar lo siguiente:
73
VIII. QUITAR DUPLICADOS En este apartado v eremos cómo podemos eliminar datos duplicados de un conjunto de datos conf orme al criterio que deseemos. Para realizar esta acción, en Excel seleccionamos los datos sobre los que queremos buscar las duplicidades y pulsamos en Datos >> Quitar duplicados. Veámoslo con el siguiente ejemplo, imagine que tenemos una granja y tenemos registrados los siguientes animales ordenados por la f inca en la que se encuentren:
77
para limpiar los datos de errores como para obtener inf ormación de los mismos.
82
IX. USO DE FILTROS En este capítulo el lector aprenderá una de las propiedades más importantes que nos of rece Excel y que no es otra que la posibilidad de f iltrar la inf ormación según los campos que deseemos en cada momento. Vamos a partir de la siguiente tabla de datos sobre calif icaciones:
83
No es igual: Es el caso opuesto al anterior, f iltraremos por todas las f ilas que no sean igual a una cadena dada. Comienza por: Se f iltra por los campos que comiencen por una cadena dada. Termina con: Se f iltra por los campos que terminen por una cadena dada, en el ejemplo si se selecciona este f iltro e indicamos “a”, se f iltraría los alumnos María, Blanca y Clara. Contiene: Se f iltra por los campos que contienen una cadena concreta, en el ejemplo si seleccionamos este tipo de f iltro y “ar”, f iltraríamos la inf ormación por los alumnos María y Clara. No contiene: Se f iltra por los campos que no contienen la cadena indicada. Filtro personalizado: Cualquier combinación de las anteriores y alguna opción extra, indicar que se pueden seleccionar dos opciones de f iltrado por campo, por ejemplo, imaginad que queremos f iltrar por aquéllos alumnos cuy a nota es may or que 5 y menor que 7, para ello podríamos seleccionar lo siguiente:
90
X. FILTROS AVANZADOS A pesar de que los f iltros básicos nos proporcionan una característica muy importante y que seguro el lector utilizará muy a menudo, en determinadas ocasiones necesitamos realizar f iltrados de may or complejidad que harían muy engorroso el proceso con los f iltros simples que hemos v isto. Es por ello que los denominados f iltros av anzados son un recurso a tener en cuenta en multitud de ocasiones. Un f iltro av anzado se basa en establecer una tabla secundaria que proporciona las condiciones de f iltrado y que tiene que tener como característica imprescindible que las cabecera/s por las que queremos f iltrar coincidan exactamente para que Excel pueda interpretar la columna por la que deseamos f iltrar. Como siempre, la mejor manera de v erlo es con un ejemplo, supongamos que tenemos la siguiente tabla con los v alores, cotización, tendencia y benef icio bruto del conjunto de empresas de un país:
94
mencionado anteriormente sería: Cotización por acción < 15 Y Tendencia = ALZA Y Benef icio Compañía > 1000 O bien Cotización por acción >20 Y Tendencia = ALZA Y Benef icio Compañía > 3000 Ahora para realizar el f iltro av anzado en base a esta tabla, realizamos los siguientes pasos: 1) Seleccionamos la tabla que queremos f iltrar y pulsamos en Datos >> Filtro avanzado. 2) En la v entana que se abre, seleccionamos el “Rango de criterios” que será el de la tabla secundaria que utilizaremos para el f iltrado:
97
como comodín y por tanto no se hace f iltrado por ese campo en concreto. En los f iltros av anzados no tenemos porqué f iltrar por todas las columnas, como hemos v isto, la tabla secundaria solo tiene que contener las columnas concretas por las que deseamos f iltrar. Una v ez que se f amiliarice con esta técnica, estoy conv encido de que será algo de gran utilidad para su uso diario.
99
XI. TABLAS DINÁMICAS Sin lugar a dudas, las tablas dinámicas son la mejor manera de resumir la inf ormación empleando el menos tiempo posible. Una tabla dinámica no es más que una representación de la inf ormación en bruto y que, como su nombre indica, puede v ariar dinámicamente conf orme los datos de origen son modif icados y cuy os datos de f ilas y columnas se basan en lo que el usuario quiera mostrar en cada momento incluso aplicando f iltros en el campo correspondiente.
Para insertar una tabla dinámica, seleccione todos los datos en bruto y presione en Insertar >> Tabla dinámica. Vamos a seguir con el ejemplo mostrado en el capítulo Tablas para que el lector v ea su utilidad, la inf ormación de partida es la siguiente:
100
1) Precio total de todos los artículos dif erenciado por tipo de artículo i) Filas: CAMPO PRODUCTO (para llev ar los campos a las dif erentes áreas simplemente arrastramos de la parte superior a la inf erior). ii) Valores: Suma de Precio. Con esto se obtiene el siguiente resultado:
105
XII. GRÁFICOS DINÁMICOS Los gráf icos dinámicos tienen la misma f ilosof ía que las tablas dinámicas aplicada a los gráf icos, de hecho un gráf ico dinámico siempre tiene asociada una tabla dinámica, bien porque ésta y a estuv iera creada prev iamente o bien porque se v a creando conf orme incorporamos campos al gráf ico dinámico. En un gráf ico dinámico v amos a poder ir v ariando las series que se muestran, los ejes, los subtotales (cuenta, suma, promedio, máximos,…) y los campos por los que se f iltran al igual que sucedía con las tablas dinámicas. Veamos un ejemplo para que se entienda su utilidad, cogemos los siguientes datos mostrados y a prev iamente en anteriores capítulos:
121
cada área podrían ser: i) LEYENDA (SERIE): PRODUCTO ii) EJES (CATEGORÍAS): FECHA DE VENTA iii) VALORES: Suma de PRECIO Esto haría que se crease el siguiente gráf ico dinámico:
124
XIII. FÓRMULAS EN EXCEL Excel nos permite insertar en una determinada celda una f órmula a partir de la cual se calcula un v alor o cadena de texto. Es una de las v entajas de trabajar con esta herramienta y a que permite agilizar sobremanera cualquier tipo de cálculo. Para insertar una f órmula, el primer carácter de la celda debe ser “=”, de esta manera Excel interpreta que lo que v iene a continuación es una f órmula (si queremos que en una celda hay a una cadena de texto que empiece por este carácter, entonces el f ormato de la celda debe ser “Texto” para que no lo interprete como f órmula). A continuación se v an a explicar las tipologías y f órmulas más utilizadas con los ejemplos oportunos para que el lector ratif ique su gran utilidad.
129
ALEATORIO.ENTRE: Se utiliza para obtener un número aleatorio entre los que especif iquemos como argumentos de la f órmula. Por ejemplo si se inserta en una celda la f unción “=ALEATORIO.ENTRE(0;10)”, Excel nos dará un número entero aleatorio entre 0 y 10. Cada v ez que se llev e a cabo una acción en Excel este v alor cambiará aleatoriamente.
RESIDUO: Con esta f unción se obtiene el residuo después de div idir un número por un div isor dado. Si en una celda se especif ica “=RESIDUO(10;3)” el resultado será “1”.
SENO/COS/TAN: Proporcionan el seno, coseno y tangente respectiv amente de un ángulo dado en radianes. Por ejemplo, la f órmula “=SENO(PI()/4)” dará el resultado “0,707”, “=COS(PI())” dará el v alor “1” y “=TAN(PI()/8)” resulta en “0,414”.
SUMA: Con esta f unción podemos sumar un rango de celdas o bien un conjunto salteado de celdas. Supongamos que queremos sumar todos los resultados del ejemplo anterior, para ello:
135
del “TELEVISOR” el resultado es 2790€. Podemos arrastrar la f órmula al resto de productos pero mucho cuidado y a que si arrastramos necesitamos insertar símbolos “$” para que los rangos no se arrastren de la misma manera (sin embargo el criterio de la f órmula sí que debe arrastrarse para que v aríe conf orme al resto de productos, por tanto no se le insertar el carácter “$”):
145
hipoteca a un 3% anual (por tanto 0,25% mensual), en un plazo de 20 años (240 meses) y por un importe de 150000€ (en la f órmula lo insertaremos como -150000€ y a que es un importe que debemos). Para calcular la cuota mensual, debemos insertar en la celda deseada la siguiente f órmula “=PAGO(0,25%;240;-150000)”, con lo que obtenemos un resultado de “822,91 €”.
147
FUNCIONES LÓGICAS SI: Esta f órmula de Excel es de las más utilizadas y a que en f unción de la condición se podrá obtener un resultado en caso de que se cumpla dicha condición y sino otro resultado dif erente, pudiendo anidar a su v ez v arias sentencias “SI”. La mejor f orma de v erlo es con un ejemplo, imaginemos que tenemos la siguiente tabla de calif icaciones:
158
Si se cumple la condición dada en el primer argumento de la f unción (D2<5) entonces la celda toma la cadena o v alor dada en el segundo argumento (“Suspenso”) y sino la celda toma la cadena o v alor dada en el tercer argumento (“Aprobado”). Podemos ir más allá y anidar v arias sentencias “SI” para que el resultado tome más rangos, por ejemplo: Suspenso (<5), Aprobado (>=5 y <7), Notable (>=7 y <9) y Sobresaliente (>=9). Para ello, el tercer argumento de cada f unción “SI” será de nuev o otra sentencia “SI” de tal manera que la f órmula insertada sea “=SI(D2<5;"Suspenso";SI(D2<7;"Aprobado";SI(D2<9;"Notable";"So
161
toma el v alor o cadena del tercer argumento que en este caso es una nuev a f unción “SI”, por tanto si se cumple que D2<7 (y may or o igual que 5 y a que sino se hubiera tomado la cadena “Suspenso”) entonces la celda toma la cadena “Aprobado”, si no ocurre esta condición entonces la celda toma el v alor del tercer argumento que v uelv e a ser otra condición “SI”, de tal manera que si se cumple la condición D2<9 la celda tomará la cadena “Notable” y sino “Sobresaliente”. Importante que, como v emos, se cierran al f inal con el carácter “)” las 3 sentencias “SI”.
Y: Es la f órmula lógica que comprueba si todos sus argumentos son v erdaderos y si es así entonces dev uelv e “VERDADERO”, sino “FALSO”. Sobre el ejemplo anterior, supongamos que se decide calif icar con “Matrícula de Honor” a aquéllos alumnos que tengan la calif icación “10” en la asignatura “Lenguaje”, es decir, tenemos 2 condiciones que cumplir, para ello podemos utilizar la f unción “Y” de tal manera que sea “=Y(D2=10;C2="Lenguaje")” y arrastramos, obteniendo lo siguiente:
163
y además la celda C2 es “Lenguaje” entonces se inserta la cadena “APLICA” y sino la cadena “NO APLICA”.
O: Con esta f órmula lógica comprobamos si alguno de los argumentos son v erdaderos y en ese caso dev uelv e “VERDADERO”, en caso contrario dev uelv a “FALSO”. Sobre el ejemplo que v enimos v iendo, imaginemos que queremos seleccionar a aquéllos alumnos que han tenido calif icación de “Notable” o “Sobresaliente” para darles alguna recompensa, para ello podemos insertar la f órmula “=O(E2="Notable";E2="Sobresaliente")” y arrastramos al resto de f ilas, obteniendo:
166
Es decir, si se cumple alguna de las 3 condiciones que hay dentro de la sentencia “O” que a su v ez son condiciones “Y” y por tanto para que dev uelv an v erdadero se tienen que cumplir todos los argumentos, entonces la celda tomará el v alor “APLICA” y sino “NO APLICA”.
169
FUNCIONES DE INFORMACIÓN Y BÚSQUEDA BUSCARV: Sin lugar a duda, esta f unción es de las más útiles que Excel nos proporciona para realizar una búsqueda de un determinado v alor o cadena dentro de una tabla y obtener el campo de la tabla que queramos asociado a ese v alor buscado. La f unción BUSCARV tiene los siguientes argumentos: i) Valor buscado: Es el v alor por el cual queremos realizar la búsqueda ii) Matriz de búsqueda: Es la tabla donde queremos buscar, la primera columna de esta tabla debe contener el v alor buscado para que la f unción proporcione algún resultado. iii) Indicador de columnas: Es la columna en la que se encuentra el v alor que queremos extraer. Se expresa como un v alor numérico de la matriz de búsqueda. iv ) Coincidencia: Indicaremos “0” si requerimos coincidencia exacta entre el v alor buscado y el v alor a encontrar en la primera columna de la matriz o bien “1” para coincidencia aproximada. Por regla general se requiere coincidencia exacta. Como v enimos haciendo en el resto del libro, v amos a plasmar el concepto con un ejemplo sencillo, supongamos que tenemos 170
realizar una búsqueda sobre la pestaña/libro. Sin embargo utilizar la f unción BUSCARV es mucho más ef iciente para realizar esta búsqueda. Supongamos que v amos a insertar el v alor que queremos buscar en la celda F2 y queremos obtener el resultado en la celda G2, para ello insertamos en la celda G2 lo siguiente “=BUSCARV(F2;A:D;4;0)” que signif ica lo siguiente: i) Valor buscado = F2, es decir, la f órmula buscará el v alor o cadena que insertemos en F2. ii) Matriz de búsqueda = A:D, por tanto el v alor buscado debe estar en la columna A para que la f unción BUSCARV obtenga algún resultado. iii) Indicador de columnas = 4, es decir, como queremos obtener el “Beneficio Compañía” que se encuentra en la columna D y ésta es la cuarta columna de la matriz, necesitamos indicar el v alor 4. Si el indicador de columnas es un número superior que el número de columnas de la matriz, entonces la f unción BUSCARV no dev olv erá ningún resultado. Si en lugar del benef icio hubiéramos querido obtener la cotización, podríamos haber dejado la misma matriz A:D y haber seleccionado el indicador de columnas “2”, aunque en ese caso la matriz también podría haber sido A:B. iv ) Coincidencia = 0, queremos que el v alor buscado coincida exactamente con alguno de los v alores de la columna A. 172
COINCIDIR: Esta f unción dev uelv e la posición relativ a del v alor buscado en la matriz seleccionada, por ejemplo, si tenemos la tabla de cotizaciones anterior y en una celda insertamos la f órmula “=COINCIDIR("Valor_5";A:A;0)”, el resultado será 6 y a que es la posición dentro de la matriz A:A en la que se encuentra la cadena “Valor_5”.
INDICE: Con esta f unción podemos obtener el v alor en una intersección dado una f ila y una columna en particular. Si en el ejemplo de las cotizaciones indicamos en una celda la f unción “=INDICE(D:D;6)”, el resultado será la intersección de la columna D y la f ila 6, es decir, 3221. Puede que y a se hay a dado usted cuenta pero si concatenamos la f unción INDICE con la f unción COINCIDIR, podemos obtener un resultado similar a las f órmulas BUSCARV/BUSCARH, es decir, podríamos insertar en la celda G2 la siguiente f órmula con la cual obtendríamos el mismo resultado que con BUSCARV: “=INDICE(D:D;COINCIDIR(F2;A:A;0))”
175
179
XIV. FORMATO CONDICIONAL Ya v imos al inicio de este libro cómo dar f ormato a las celdas y a las tablas, sin embargo podemos necesitar que una celda o conjunto de celdas tomen un f ormato en f unción del v alor o cadena de dicha celda. Para ello, Excel nos proporciona lo que se denomina como formato condicional. Para aplicarlo, seleccionamos el conjunto de celdas cuy o f ormato queremos que dependa de su v alor y pulsamos en Inicio >> Formato condicional. Vamos a v er cada una de las opciones con dif erentes ejemplos:
180
XV. ORGANIZACIÓN DE VISTA DE TRABAJO Algo que se suele obv iar al trabajar con programas de hojas de cálculo es organizar la v ista de trabajo y es un punto muy importante para que usted se encuentre lo más cómodo posible y por tanto mejore la ef iciencia al trabajar con este tipo de sof tware. En Excel tenemos v arias opciones para organizar la inf ormación y que nos será de gran ay uda. Estas opciones son: Agrupar/Desagrupar f ilas/columnas: Podemos agrupar las f ilas y columnas que deseemos para contraer/expandir conf orme requiramos. Imaginemos que tenemos la siguiente tabla de inf ormación de nuestros clientes:
187
ser más ef icientes con nuestras hojas de cálculo de Excel.
200
XVI. GRABAR MACRO En su trabajo diario seguro que realiza determinadas acciones de manera repetitiv a que podrían automatizarse gracias a una macro en Excel. Una macro no es más que código en lenguaje VBA (Visual Basic para Aplicaciones) que Excel interpreta de tal manera que se pueden automatizar tareas. Usted puede aprender lenguaje VBA para escribir su propio código y ejecutarlo para realizar determinadas acciones en Excel o bien grabar una macro que lo que hace es conv ertir lo que usted llev e a cabo durante la grabación a código VBA y después pueda ejecutarlo cuantas v eces desee, de tal manera que una tarea que por ejemplo le llev a 5 minutos al día y es repetitiv a la puede grabar la primera v ez y en días posteriores solo ejecutar dicha macro (si usted trabaja unos 240 días al año, estaría ahorrándose 1200 minutos anuales en esa tarea). Para grabar una macro, en primer lugar le debe aparecer la pestaña Desarrollador, por def ecto está oculta en Excel y para mostrarla debe seleccionar Archivo >> Opciones >> Personalizar cinta de opciones >> Pestañas principales >> Activar Desarrollador. Una v ez le aparezca, para grabar pulse en Desarrollador >> Grabar macro, de esta manera se estará grabando los pasos que realice hasta seleccionar “Detener Grabación” y después podrá asignar el código VBA generado automáticamente a un botón por ejemplo para ejecutar el código grabado. Como siempre, v amos a v er unos ejemplos para ilustrar el concepto.
201
Ejemplo 1: Paso a columnas – Formato tabla – Formato Centrado – Cabeceras negrita y cursiv a Vamos a recuperar el ejemplo que v imos en el capítulo de Texto en columnas donde teníamos el listado de v uelos Madrid – París, para grabar el paso a columnas según el carácter “;”, rellenaremos todos los bordes, centraremos el texto completo y la cabecera tendrá relleno en negro y f uente blanca además de negrita y cursiv a. Los datos de entrada son:
202
Ejemplo 2: Acciones básicas – Pegar v alores En el ejemplo anterior v imos el tiempo que podemos ganar en un trabajo repetitiv o que podemos tener que hacer diariamente gracias a la grabación de macros. En este ejemplo, animo al lector a que grabe acciones básicas y le asigne el icono que desee en la barra de acceso rápido para disminuir el tiempo de todas aquellas microoperaciones que realiza en multitud de ocasiones diariamente. Un ejemplo de esto podría ser la acción de copiar y pegar como v alores una determinada celda (en lugar de pegar directamente y a que con ello se pegarían las f órmulas de la celda origen por ejemplo), v eamos una comparativ a de esta microoperación suponiendo que lo hacemos 10 v eces al día en los 240 días de nuestro trabajo: Manualmente: Seleccionar la celda a copiar >> CTRL + C para copiar >> seleccionar la celda destino donde v amos a pegar >> click derecho >> Pegado especial >> Valores >> Aceptar. Si realizar este proceso nos llev a 10 segundos, multiplicamos por 10 v eces al día y 240 días año supone un total de 400 minutos anuales. Grabación de macro: Seleccionar la celda a copiar >> CTRL + C para copiar >> seleccionar la celda destino donde v amos a pegar >> pulsamos en Desarrollador >> Grabar macro >> click derecho en la celda que estaba seleccionada >> Pegado especial >> Valores >> Aceptar. En grabar la macro imaginemos 208
que nos llev a 20 segundos pero cada una de las siguientes ocasiones en las que tengamos que realizarlo nos llev ará 2 segundos, por tanto haría un total de 80,3 minutos anuales. Seguro que usted realiza bastantes operaciones de este tipo diariamente y, como ha observ ado, en el caso del ejemplo hay una reducción en tiempo del 80%.
Con todo lo aprendido en este capítulo, v emos el poder que tiene el uso del lenguaje VBA para automatizar tareas en Excel consiguiendo que seamos muy ef icientes en el uso de la herramienta. En lugar de grabar macros también podría programar en lenguaje VBA directamente. El aprendizaje de este lenguaje está f uera del alcance de este libro aunque en el momento de escribir este documento estoy poniendo en marcha otro libro alternativ o para enseñar al lector interesado el lenguaje VBA de tal manera que pueda automatizar sus tareas de una manera más v ersátil que únicamente grabando macros. Sin duda es muy satisf actorio automatizar las tareas de manera que nos ahorren gran parte de nuestro tiempo.
209
XVII. MANEJO EFICIENTE Como en la may oría de aplicaciones, existen v arias maneras de hacer una determinada acción, sin embargo siempre hay una más rápida que las demás. A continuación aparecen una serie de atajos en Excel, la may oría relacionados con el uso del teclado suplantando a los clicks de ratón: CTRL + click izquierdo: Selección de v arias celdas salteadas manteniendo la tecla CTRL y pinchando en dichas celdas. Esto nos será útil para elegir por ejemplo un f ormato en aquellas celdas que nos interese. Tecla May úsculas + Flecha: Para seleccionar v arias celdas contiguas, podemos hacerlo con el ratón arrastrando a todo el rango o bien podemos seleccionar una celda, mantenemos la tecla May úsculas y seguimos ampliando la selección con las f lechas del teclado. CTRL + Flecha: Con esta combinación podemos ir hasta el f inal de una tabla. De manera genérica iremos hasta la última celda de la dirección en la que pulsemos la f lecha que contenga datos. Por ejemplo, si partimos de la siguiente tabla donde tenemos seleccionada la celda A1:
210
dirección. Combinación May úsculas + CTRL + Flecha: Esto es unif icar los casos anteriores. Si por ejemplo estamos en la tabla anterior expuesta en la que está seleccionada la celda A1, mantenemos tanto la tecla May úsculas como CTRL y pulsamos la f lecha abajo, seleccionaríamos toda la columna hasta que no hubiera datos, es decir, el rango A1:A27:
212
pulsamos en Av Pág, nos iremos a la pestaña siguiente del libro. Si en lugar de pulsar Av Pág pulsamos Re Pág iremos a la pestaña anterior. CTRL+C – CTRL+V: Esto es algo que seguro el lector utiliza habitualmente. Es la combinación de CTRL + C para copiar (una celda, un rango, la pestaña completa,…) y CTRL + V para pegar. Desde luego es una de las combinaciones que más ef iciencia proporcionan en el manejo no solo de Excel, sino de la of imática en general y a que es una acción cotidiana. CTRL+B: Con esta combinación podemos hacer una búsqueda en Excel o bien reemplazar datos. Para buscar inf ormación tenemos v arias opciones según se muestra en la v entana que se abre al pulsar la combinación:
214
cálculo >> Manual, de esta manera solo se actualizaría su libro cuando pulsásemos la tecla F9.
Conf orme utilice Excel, v erá cómo el manejo ef iciente de la herramienta es algo esencial para que seamos más productiv os en nuestro trabajo. Todos los atajos aquí mostrados necesitan de un tiempo de adaptación por su parte para que su cerebro los absorba y los utilice de manera automática, es por ello que le inv ito a practicar con los mismos todo lo que pueda.
219
XVIII. RESUMEN FINAL A lo largo de este libro hemos v isto desde cero las características más relev antes que nos of rece Excel de una manera didáctica con numerosos ejemplos para que el lector pueda aplicar cada una de las propiedades en sus tareas diarias de una manera ef iciente. Como todo en la v ida, le he tratado de enseñar lo mejor posible cada uno de los apartados pero para asimilar todos los conceptos usted debe ponerlos en práctica y llev arlo a su terreno personal y prof esional. Si está interesado en af ianzar conceptos utilizando automatizaciones a partir del lenguaje VBA, durante el tiempo de construcción de este libro, este autor está escribiendo un documento guía para que pueda prof undizar sobre ello si así lo desea. Le animo a que deje su opinión sobre este libro, tanto si le ha gustado como sino para f uturos lectores y para el autor, y a que es muy importante conocer su punto de v ista. Por otra parte, agradecerle el tiempo dedicado a la lectura de este libro y deseo que hay a sido de su agrado y le hay a ay udado a descubrir y af ianzar el conocimiento con esta magníf ica aplicación.
220
