Post on 21-Jan-2018
transcript
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