Excel la herramienta del mundo laboral

220

Transcript of Excel la herramienta del mundo laboral

Page 2: 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

Page 3: Excel la herramienta del mundo laboral

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

Page 4: Excel la herramienta del mundo laboral

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

Page 5: Excel la herramienta del mundo laboral

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

Page 6: Excel la herramienta del mundo laboral

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

Page 7: Excel la herramienta del mundo laboral

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

Page 8: Excel la herramienta del mundo laboral

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

Page 9: Excel la herramienta del mundo laboral
Page 10: Excel la herramienta del mundo laboral
Page 11: Excel la herramienta del mundo laboral
Page 12: Excel la herramienta del mundo laboral
Page 13: Excel la herramienta del mundo laboral
Page 14: Excel la herramienta del mundo laboral

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

Page 15: Excel la herramienta del mundo laboral

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

Page 16: Excel la herramienta del mundo laboral
Page 17: Excel la herramienta del mundo laboral

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

Page 18: Excel la herramienta del mundo laboral

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

Page 19: Excel la herramienta del mundo laboral
Page 20: Excel la herramienta del mundo laboral
Page 21: Excel la herramienta del mundo laboral
Page 22: Excel la herramienta del mundo laboral
Page 23: Excel la herramienta del mundo laboral
Page 24: Excel la herramienta del mundo laboral
Page 25: Excel la herramienta del mundo laboral

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

Page 26: Excel la herramienta del mundo laboral
Page 27: Excel la herramienta del mundo laboral
Page 28: Excel la herramienta del mundo laboral
Page 29: Excel la herramienta del mundo laboral
Page 30: Excel la herramienta del mundo laboral
Page 31: Excel la herramienta del mundo laboral

de los datos en bruto que le abrirá un gran abanico de posibilidades.

31

Page 32: Excel la herramienta del mundo laboral
Page 33: Excel la herramienta del mundo laboral
Page 34: Excel la herramienta del mundo laboral
Page 35: Excel la herramienta del mundo laboral
Page 36: Excel la herramienta del mundo laboral
Page 37: Excel la herramienta del mundo laboral
Page 38: Excel la herramienta del mundo laboral
Page 39: Excel la herramienta del mundo laboral
Page 40: Excel la herramienta del mundo laboral
Page 41: Excel la herramienta del mundo laboral
Page 42: Excel la herramienta del mundo laboral
Page 43: Excel la herramienta del mundo laboral
Page 44: Excel la herramienta del mundo laboral
Page 45: Excel la herramienta del mundo laboral
Page 46: Excel la herramienta del mundo laboral
Page 47: Excel la herramienta del mundo laboral

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

Page 48: Excel la herramienta del mundo laboral

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

Page 49: Excel la herramienta del mundo laboral
Page 50: Excel la herramienta del mundo laboral
Page 51: Excel la herramienta del mundo laboral
Page 52: Excel la herramienta del mundo laboral
Page 53: Excel la herramienta del mundo laboral
Page 54: Excel la herramienta del mundo laboral

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

Page 55: Excel la herramienta del mundo laboral
Page 56: Excel la herramienta del mundo laboral

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

Page 57: Excel la herramienta del mundo laboral
Page 58: Excel la herramienta del mundo laboral
Page 59: Excel la herramienta del mundo laboral
Page 60: Excel la herramienta del mundo laboral
Page 61: Excel la herramienta del mundo laboral

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

Page 62: Excel la herramienta del mundo laboral
Page 63: Excel la herramienta del mundo laboral
Page 64: Excel la herramienta del mundo laboral
Page 65: Excel la herramienta del mundo laboral

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

Page 66: Excel la herramienta del mundo laboral

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

Page 67: Excel la herramienta del mundo laboral
Page 68: Excel la herramienta del mundo laboral
Page 69: Excel la herramienta del mundo laboral
Page 70: Excel la herramienta del mundo laboral
Page 71: Excel la herramienta del mundo laboral
Page 72: Excel la herramienta del mundo laboral
Page 73: Excel la herramienta del mundo laboral

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

Page 74: Excel la herramienta del mundo laboral
Page 75: Excel la herramienta del mundo laboral
Page 76: Excel la herramienta del mundo laboral
Page 77: Excel la herramienta del mundo laboral

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

Page 78: Excel la herramienta del mundo laboral
Page 79: Excel la herramienta del mundo laboral
Page 80: Excel la herramienta del mundo laboral
Page 81: Excel la herramienta del mundo laboral
Page 82: Excel la herramienta del mundo laboral

para limpiar los datos de errores como para obtener inf ormación de

los mismos.

82

Page 83: Excel la herramienta del mundo laboral

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

Page 84: Excel la herramienta del mundo laboral
Page 85: Excel la herramienta del mundo laboral
Page 86: Excel la herramienta del mundo laboral
Page 87: Excel la herramienta del mundo laboral
Page 88: Excel la herramienta del mundo laboral
Page 89: Excel la herramienta del mundo laboral
Page 90: Excel la herramienta del mundo laboral

-

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

Page 91: Excel la herramienta del mundo laboral
Page 92: Excel la herramienta del mundo laboral
Page 93: Excel la herramienta del mundo laboral
Page 94: Excel la herramienta del mundo laboral

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

Page 95: Excel la herramienta del mundo laboral
Page 96: Excel la herramienta del mundo laboral
Page 97: Excel la herramienta del mundo laboral

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

Page 98: Excel la herramienta del mundo laboral
Page 99: Excel la herramienta del mundo laboral

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

Page 100: Excel la herramienta del mundo laboral

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

Page 101: Excel la herramienta del mundo laboral
Page 102: Excel la herramienta del mundo laboral
Page 103: Excel la herramienta del mundo laboral
Page 104: Excel la herramienta del mundo laboral
Page 105: Excel la herramienta del mundo laboral

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

Page 106: Excel la herramienta del mundo laboral
Page 107: Excel la herramienta del mundo laboral
Page 108: Excel la herramienta del mundo laboral
Page 109: Excel la herramienta del mundo laboral
Page 110: Excel la herramienta del mundo laboral
Page 111: Excel la herramienta del mundo laboral
Page 112: Excel la herramienta del mundo laboral
Page 113: Excel la herramienta del mundo laboral
Page 114: Excel la herramienta del mundo laboral
Page 115: Excel la herramienta del mundo laboral
Page 116: Excel la herramienta del mundo laboral
Page 117: Excel la herramienta del mundo laboral
Page 118: Excel la herramienta del mundo laboral
Page 119: Excel la herramienta del mundo laboral
Page 120: Excel la herramienta del mundo laboral
Page 121: Excel la herramienta del mundo laboral

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

Page 122: Excel la herramienta del mundo laboral
Page 123: Excel la herramienta del mundo laboral
Page 124: Excel la herramienta del mundo laboral

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

Page 125: Excel la herramienta del mundo laboral
Page 126: Excel la herramienta del mundo laboral
Page 127: Excel la herramienta del mundo laboral
Page 128: Excel la herramienta del mundo laboral
Page 129: Excel la herramienta del mundo laboral

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

Page 130: Excel la herramienta del mundo laboral
Page 131: Excel la herramienta del mundo laboral
Page 132: Excel la herramienta del mundo laboral
Page 133: Excel la herramienta del mundo laboral
Page 134: Excel la herramienta del mundo laboral
Page 135: Excel la herramienta del mundo laboral

-

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

Page 136: Excel la herramienta del mundo laboral
Page 137: Excel la herramienta del mundo laboral
Page 138: Excel la herramienta del mundo laboral
Page 139: Excel la herramienta del mundo laboral
Page 140: Excel la herramienta del mundo laboral
Page 141: Excel la herramienta del mundo laboral
Page 142: Excel la herramienta del mundo laboral
Page 143: Excel la herramienta del mundo laboral
Page 144: Excel la herramienta del mundo laboral
Page 145: Excel la herramienta del mundo laboral

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

Page 146: Excel la herramienta del mundo laboral
Page 147: Excel la herramienta del mundo laboral

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

Page 148: Excel la herramienta del mundo laboral
Page 149: Excel la herramienta del mundo laboral
Page 150: Excel la herramienta del mundo laboral
Page 151: Excel la herramienta del mundo laboral
Page 152: Excel la herramienta del mundo laboral
Page 153: Excel la herramienta del mundo laboral
Page 154: Excel la herramienta del mundo laboral
Page 155: Excel la herramienta del mundo laboral
Page 156: Excel la herramienta del mundo laboral
Page 157: Excel la herramienta del mundo laboral
Page 158: Excel la herramienta del mundo laboral

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

Page 159: Excel la herramienta del mundo laboral
Page 160: Excel la herramienta del mundo laboral
Page 161: Excel la herramienta del mundo laboral

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

Page 162: Excel la herramienta del mundo laboral
Page 163: Excel la herramienta del mundo laboral

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

Page 164: Excel la herramienta del mundo laboral
Page 165: Excel la herramienta del mundo laboral
Page 166: Excel la herramienta del mundo laboral

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

Page 167: Excel la herramienta del mundo laboral
Page 168: Excel la herramienta del mundo laboral
Page 169: Excel la herramienta del mundo laboral

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

Page 170: Excel la herramienta del mundo laboral

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

Page 171: Excel la herramienta del mundo laboral
Page 172: Excel la herramienta del mundo laboral

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

Page 173: Excel la herramienta del mundo laboral
Page 174: Excel la herramienta del mundo laboral
Page 175: Excel la herramienta del mundo laboral

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

Page 176: Excel la herramienta del mundo laboral
Page 177: Excel la herramienta del mundo laboral
Page 178: Excel la herramienta del mundo laboral
Page 179: Excel la herramienta del mundo laboral

179

Page 180: Excel la herramienta del mundo laboral

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

Page 181: Excel la herramienta del mundo laboral
Page 182: Excel la herramienta del mundo laboral
Page 183: Excel la herramienta del mundo laboral
Page 184: Excel la herramienta del mundo laboral
Page 185: Excel la herramienta del mundo laboral
Page 186: Excel la herramienta del mundo laboral
Page 187: Excel la herramienta del mundo laboral

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

Page 188: Excel la herramienta del mundo laboral
Page 189: Excel la herramienta del mundo laboral
Page 190: Excel la herramienta del mundo laboral
Page 191: Excel la herramienta del mundo laboral
Page 192: Excel la herramienta del mundo laboral
Page 193: Excel la herramienta del mundo laboral
Page 194: Excel la herramienta del mundo laboral
Page 195: Excel la herramienta del mundo laboral
Page 196: Excel la herramienta del mundo laboral
Page 197: Excel la herramienta del mundo laboral
Page 198: Excel la herramienta del mundo laboral
Page 199: Excel la herramienta del mundo laboral
Page 200: Excel la herramienta del mundo laboral

ser más ef icientes con nuestras hojas de cálculo de Excel.

200

Page 201: Excel la herramienta del mundo laboral

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

Page 202: Excel la herramienta del mundo laboral

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

Page 203: Excel la herramienta del mundo laboral
Page 204: Excel la herramienta del mundo laboral
Page 205: Excel la herramienta del mundo laboral
Page 206: Excel la herramienta del mundo laboral
Page 207: Excel la herramienta del mundo laboral
Page 208: Excel la herramienta del mundo laboral

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

Page 209: Excel la herramienta del mundo laboral

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

Page 210: Excel la herramienta del mundo laboral

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

Page 211: Excel la herramienta del mundo laboral
Page 212: Excel la herramienta del mundo laboral

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

Page 213: Excel la herramienta del mundo laboral
Page 214: Excel la herramienta del mundo laboral

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

Page 215: Excel la herramienta del mundo laboral
Page 216: Excel la herramienta del mundo laboral
Page 217: Excel la herramienta del mundo laboral
Page 218: Excel la herramienta del mundo laboral
Page 219: Excel la herramienta del mundo laboral

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

Page 220: Excel la herramienta del mundo laboral

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