excel la herramienta del mundo laboral

EXCEL, LA HERRAMIENTA DEL MUNDOLABORAL

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. INTRODUCCIÓN

II. MANEJO BÁSICOCrear nuevo libro

Compartir libro

Formato

Autoajuste de columnas

III. TABLAS

IV. GRÁFICOS

V. ORDENACIÓN DE DATOS

VI. TEXTO EN COLUMNAS

VII. VALIDACIÓN DE DATOS

VIII. QUITAR DUPLICADOS

IX. USO DE FILTROS

X. FILTROS AVANZADOS

XI. TABLAS DINÁMICAS

XII. GRÁFICOS DINÁMICOS

XIII. FÓRMULAS EN EXCELFUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS

FUNCIONES DE TEXTO

FUNCIONES LÓGICAS

FUNCIONES DE INFORMACIÓN Y BÚSQUEDA

XIV. FORMATO CONDICIONAL

XV. ORGANIZACIÓN DE VISTA DE TRABAJO

XVI. GRABAR MACRO

XVII. MANEJO EFICIENTE

XVIII. RESUMEN FINAL

3

PRÓLOGOTras 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ÓNEl 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ÁSICOComenzaremos 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

10

1l

12

Peslañas del libro

J, _______ ~ 4 • 1 ojal

8

G

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.

TABLASLa 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 DATOSEs 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 COLUMNASEn 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 DATOSEn 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 DUPLICADOSEn 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 FILTROSEn 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 AVANZADOSA 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ÁMICASSin 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ÁMICOSLos 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 EXCELExcel 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 CONDICIONALYa 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

TRABAJOAlgo 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 MACROEn 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 EFICIENTEComo 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 FINALA 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