CURSO EXCEL – Segunda Parte

Contenido
1. FUNCIONES LÓGICAS................................................................................................................... 3
1.1. Función “SI” ......................................................................................................................... 3
1.2. “SI” anidados ....................................................................................................................... 5
1.3. Función “Y” y “O” ................................................................................................................ 6
1.4. Control de errores: Función “SI.ERROR”. ............................................................................ 7
2. FUNCIONES MATEMÁTICAS ........................................................................................................ 9
3. FUNCIONES DE TEXTO ............................................................................................................... 11
4. FUNCIONES DE INFORMACION: ................................................................................................ 12
5. FUNCIONES DE BUSQUEDA ....................................................................................................... 13
5.1. “Buscar V” y “Buscar H” .................................................................................................... 13
5.1.1. Función “Buscar V” ........................................................................................................ 13
5.1.2. Función “Buscar H”........................................................................................................ 16
5.2. “Coincidir” e “Índice” ........................................................................................................ 17
5.2.1. Función “Coincidir” ....................................................................................................... 17
5.2.2. Función “Índice”. ........................................................................................................... 18
6. FUNCIONES DE BASE DE DATOS ................................................................................................ 21
7. VALIDACION DE DATOS ............................................................................................................. 25
8. FORMATO CONDICIONAL .......................................................................................................... 26
8.1. Características y tipos de formatos condicionales ............................................................ 26
8.2. Reglas basadas en fórmulas .............................................................................................. 31
9. CALCULOS CONDICIONALES ...................................................................................................... 33
9.1. Funciones con condiciones ............................................................................................... 33
9.2. Funciones “Conjunto” ....................................................................................................... 35
10. PROTECCION DE PLANILLAS DE CÁLCULO. ............................................................................ 37
11. GESTION DE DATOS EXTERNOS ............................................................................................. 39
11.1. Importar/Exportar Datos ................................................................................................... 39
11.2. Consultas Web................................................................................................................... 40
11.3. Relación entre distintos libros de Excel ............................................................................ 42
12. FILTRO AVANZADO ................................................................................................................ 44

P á g i n a 1 | 56
Elaborado por: Revisado por: Aprobado por:
13. TABLAS Y GRAFICOS DINAMICOS .......................................................................................... 47
13.1. Concepto ........................................................................................................................... 47
13.2. Confección de una TD ....................................................................................................... 47
13.3. Confección de un Grafico Dinamico. ................................................................................. 52
13.4. Opciones comunes para TD y GD ...................................................................................... 53
13.5. Campos Calculados............................................................................................................ 55

P á g i n a 2 | 56
Elaborado por: Revisado por: Aprobado por:
1. FUNCIONES LÓGICAS

1.1. Función “SI”

Una de las más famosas y más utilizadas fórmulas en Excel, es la denominada “función
SI”. La misma permite, dada una prueba lógica, determinar un resultado en caso que esa
prueba lógica se cumpla (es decir, sea verdadera), y otro resultado si la misma no se
cumple (sea falsa).

La función consta de 3 argumentos:

• PRUEBA_LOGICA: Es el primer argumento, y el más importante, ya que es donde

debemos establecer una condición, que el Excel evaluará como verdadera o falsa
(dicho de otra manera, si se cumple o no). En este punto, se pueden combinar
números, fechas y textos, junto con los operadores matemáticos (<,>,=, <>) y
con funciones propias de Excel. Además se puede trabajar con datos fijos o
referencias a otras celdas.

• VALOR_SI_VERDADERO: Aquí indicaremos qué resultado queremos obtener en la

celda, si el primer argumento se cumple. Es decir, si la “Prueba Lógica” es
verdadera, el Excel ejecutará lo que hayamos escrito en este argumento
“Valor_si_Verdadero”. Caso contrario, aplica lo que haya en el último argumento.

• VALOR_SI_FALSO: es el valor que se devolverá si la prueba lógica es falsa. Es

decir, si la condición establecida en el primer argumento no se cumple (es falsa), el
Excel devolverá en la celda, lo que hayamos indicado en este último argumento.

P á g i n a 3 | 56
Elaborado por: Revisado por: Aprobado por:
Ejemplo: Un profesor desea establecer la condición de los alumnos, según el promedio
final, la cual es:
• Regular: Promedio mayor o igual a 6
• Libre: Promedio menor a 6

Alumno Promedio Condición

CARLOS AGUSTIN 7
MARIO GABRIEL 5
CLAUDIO FABIAN 3
GUSTAVO FABIAN 2
RICARDO OSCAR 8
JOSE LUIS 10
MIGUEL ANGEL 9
RUBEN DARIO 4
ZULEMA MERCEDES 8

La resolución debería ser:

*Para practicar, pruebe reemplazar los valores si verdadero y falso, por números y
operaciones matemáticas. A su vez, reemplace el número 6 por una celda, suponiendo
que las condiciones de regularizado de la materia dependen de cada profesor.

*IMPORTANTE: Recuerde que siempre que escriba un texto dentro de una fórmula, el
mismo debe estar entre comillas.

P á g i n a 4 | 56
Elaborado por: Revisado por: Aprobado por:
 1.2. “SI” anidados

En la vida real, sucede muchas veces que la cantidad de situaciones posible excede a 2
(una verdadera y una falsa), por lo que es necesario “ampliar” la función SI, para poder
abarcar todos los escenarios existentes. Para ello, se procede a trabajar con los
denominados “SI ANIDADOS”, que no son más que varios “si” uno dentro de otro.

La finalidad de la anidación, es poder cubrir todos los casos que se nos presenten.

*IMPORTANTE 1: La anidación, por lo general, se hace en el argumento

“VALOR_SI_FALSO” de cada “SI” que armemos. Es decir, vuelvo a armar otro “si” en el
último argumento del primero.

*IMPORTANTE 2: Si tengo que establecer 3 condiciones, por ejemplo, sólo es necesario

añadir un solo “si”, ya que con 2 funciones, cubro 3 condiciones. Con el ejemplo va a
quedar más claro este punto.

Supongamos que el gerente comercial tiene que liquidar las comisiones de sus
vendedores, y tiene los siguientes parámetros:
• Si la venta fue en Córdoba, el porcentaje de comisión es del 10%
• Si fue en La Pampa, es del 15%
• Si fue en Mendoza, la comisión es del 20%

Provincia Monto
La Pampa $ 5.000,00
La Pampa $ 10.000,00
Córdoba $ 2.000,00
La Pampa $ 1.000,00
Córdoba $ 1.500,00
Mendoza $ 25.000,00
Mendoza $ 30.000,00
Mendoza $ 5.500,00
La Pampa $ 10.500,00

P á g i n a 5 | 56
Elaborado por: Revisado por: Aprobado por:
Solución:

2da Condición

1ra Condición 3ra Condición

SI ANIDADO

1.3. Función “Y” y “O”

Dentro de las funciones lógicas, existen 2 que suelen integrarse a la función “SI”: La
función “Y” y la función “O”.

Ambas devuelven como resultado la expresión “VERDADERO” o “FALSO”, si se cumple lo

establecido en ellas. La función “Y” va a devolver “VERDADERO” si todas las condiciones
que se especificaron se cumplen. Si al menos una de ellas no es verdadera, la función
devuelve “FALSO”.

En tanto la función “O”, va a devolver “VERDADERO” cuando al menos uno de sus

argumentos (condiciones) se cumple. Si ninguno se cumple, devuelve “FALSO”.

La gran utilidad que representan estas dos funciones, es que complementadas con el “SI”,
nos permiten abarcar todas las condiciones que tengamos que establecer. Esto es así,

P á g i n a 6 | 56
Elaborado por: Revisado por: Aprobado por:
porque dentro de cada función Y u O, podremos establecer criterios que, en caso de
cumplirse, la fórmula devolverá “VERDADERO” o “FALSO” y ese texto, dentro del
argumento “PRUEBA_LOGICA” de la función “si”, accionará lo que haya en
“VALOR_SI_VERDADERO” o “VALOR_SI_FALSO” según corresponda.

Mientras que con los SI anidados podíamos agregar más de una prueba lógica, lo que nos
permiten las funciones Y y O, es agregar condiciones a esas pruebas lógicas.

Ejercicio: Retomando el práctico del profesor y las condiciones de los alumnos,

supongamos que las nuevas condiciones ahora son:
• Promoción: promedio mayor o igual a 8
• Regular: promedio mayor o igual a 6 y menor que 8
• Libre: promedio menor a 6
El ejercicio se debería resolver:

1.4. Control de errores: Función “SI.ERROR”.

Para cerrar el tema de funciones lógicas, mostraremos una función cuya utilidad es
considerable para lo que en Excel se denomina “control de errores”.

El control de los errores que pueden aparecer en una planilla de cálculo es necesario para
saber cuándo se está en presencia de uno de ellos y también para que, estéticamente,
una planilla quede mejor presentada.

Puede suceder muchas veces, que uno arma fórmulas con la intención de que el modelo
sirva para un largo período de tiempo, y como todavía no hay datos cargados en
determinadas columnas, las fórmulas devuelvan errores, entre ellos:

P á g i n a 7 | 56
Elaborado por: Revisado por: Aprobado por:
 #¡DIV/0! #¡REF! #¡VALOR!

Tenga en cuenta que siempre hay que controlar si estos errores son a causa de que
elaboramos mal una fórmula o ingresamos mal los datos, o bien, como se comentó antes,
es porque falta cargar información pero la fórmula está bien hecha. Esto puede suceder
cuando uno quiere dejar modelada una planilla donde todos los días, por ejemplo, se
carguen las ventas, y que automáticamente se calculen los días de cobro, comisiones, etc.
Querremos armar las fórmulas para un rango amplio, de manera de no tener que estar
todos los días modificando los datos. Al hacer esto, pueden aparecer los errores antes
descriptos.

Una forma cómoda y simple de evitar que esto suceda, es utilizar la fórmula “SI.ERROR”.
La misma es un “SI” pero con un argumento menos. Lo que nos va a pedir es que
indiquemos en el primer argumento la celda donde puede aparecer un error y en el
segundo argumento, colocar qué resultado queremos que sea vea en dicha celda, en caso
de aparecer un error.

*TIP Útil: es muy común utilizar la expresión vacío (“”), para que la celda quede en
blanco, cuando aparezca un error.

Ejemplo: El área logística de una empresa quiere registrar todos los días la cantidad de
unidades que se cargaron en los camiones y sabiendo la especificación estándar
(“Unidades por pallet”) desea saber cuántas tarimas cargó por día. Pero el gerente desea
que el modelo funcione para todo el mes, por ello ustedes armarían la fórmula una sola
vez, y luego la “arrastrarían” hasta el final. El problema es que al haber una división donde
el denominador es vacío, la celda devuelve error:

Queda claro que la fórmula empleada es correcta, pero que visualmente no queda bien
que haya celdas con errores. Para solucionar esto, aplicaremos la fórmula si.error de la
siguiente manera:

P á g i n a 8 | 56
Elaborado por: Revisado por: Aprobado por:
De esta manera, al poner como “VALOR_SI_ERROR” dos comillas (lo que en Excel se lee
como “Vacío”), al detectar un error (celdas D7 y D8), deja la celda en blanco y así
mejoramos el aspecto de nuestro modelo.

2. FUNCIONES MATEMÁTICAS

Si bien las funciones matemáticas más importantes se vieron en el curso básico, aquí
agregaremos algunas más que pueden serles útiles a futuro:

A continuación, la lista de funciones:

• F. ENTERO: Es la que redondea un número hasta el entero inferior más próximo.

Tabla de ejemplo con los resultados de aplicar la función entero a los números de la
primera columna:

Numero Resultado
1,4 1
1,7 1
-1,7 -2
158,7634 158
-158,7634 -159
158,7634 158
-158,7634 -159

P á g i n a 9 | 56
Elaborado por: Revisado por: Aprobado por:
 • F. POTENCIA: Devuelve el resultado de elevar el número a una potencia.

Función Resultado
=POTENCIA(10;2) 100

• F. RAIZ: Devuelve la raíz cuadrada de un número.

Función Resultado
=RAIZ(36) 6

• F. SUMAPRODUCTO: devuelve la suma de los productos de dos o más rangos. Si

se complementa con la función Suma, es muy útil para calcular Promedios
Ponderados:

Ejemplo: Dada la nómina de empleados y los sueldos, se desea calcular el Sueldo

Promedio Ponderado de la empresa. Se cuenta con la siguiente información:

Si uno calcula un promedio simple (con la función “promedio” sobre la primer columna)
obtendría como resultado $13083.3, pero a simple vista se puede observar que de los 19
empleados, 15 cobran menos de $10000 mensuales, por ello un promedio simple no
siempre es un dato fiable.
Para solucionar esto, precisamos usar las funciones suma y sumaproducto para obtener el
promedio ponderado, el cual es $8447.37 y se obtiene de aplicar la fórmula:
=SUMAPRODUCTO(A2:A7;B2:B7)/SUMA(B2:B7)

P á g i n a 10 | 56
Elaborado por: Revisado por: Aprobado por:
3. FUNCIONES DE TEXTO

Otra rama de funciones de Excel son las conocidas como de “texto”, las cuales permiten
operar con la información contenida en una celda o rango, cuando la misma sea del tipo
texto. Alguna de ellas son:

• F. LARGO: Devuelve el número de caracteres de un texto, es decir la

cantidad de letras y espacios en blanco que hay en una celda.

Nombre Función Resultado

Juan P. =LARGO(A2) 7

• F. EXTRAE: Devuelve la parte de un texto de una celda, indicada una

posición y longitud inicial

Nombre Función Resultado

Juan P. =EXTRAE(A2;1;3) Jua

• F. IZQUIERDA: Devuelve la cantidad de caracteres que indiquemos, desde

el principio del texto de la celda.

Nombre Función Resultado

Juan P. =IZQUIERDA(A2;2) Ju

• F. DERECHA: Símil a la función anterior, pero devuelve caracteres

empezando desde el final del texto.

Nombre Función Resultado

Juan P. =DERECHA(A2;2) P.

• F. HALLAR: Devuelve la posición donde se ubica un texto (puede ser una

letra o una palabra), dentro de otro texto de una celda, leyendo de
izquierda a derecha.

P á g i n a 11 | 56
Elaborado por: Revisado por: Aprobado por:
 Palabra Función Resultado
Argentina =HALLAR("tina";A2) 6

• F. CONCATENAR: Cumple la misma función que el símbolo Ampersend (&).

La función une varios elementos de texto en uno solo

Nombre Apellido Función Resultado

Lionel Messi =CONCATENAR(A2;" ";B2) Lionel Messi

• F. REEMPLAZAR: Sirve para cambiar una parte de un texto por otra

Palabra Función Resultado

Casa =REEMPLAZAR(A2;3;1;"r") Cara

• F. MAYUSC: Convierte un texto a mayúscula.

• F. MINUSC: Convierte un texto a minúscula.

Palabra Función Resultado

argentina =MAYUSC(A2) ARGENTINA
DEPORTE =MINUSC(A3) deporte

*A TENER EN CUENTA: Todas las funciones de texto explicadas se pueden combinar

entre sí y con funciones de otro tipo (matemáticas, de búsqueda, etc.) por lo que el
alcance de las mismas es ilimitado.

4. FUNCIONES DE INFORMACIÓN:

En esta parte se verán fórmulas cuyo uso es para obtener información sobre el tipo de
dato que contiene la celda. Obtendremos la respuesta en expresiones de “Verdadero” o
“falso”.

Todas las funciones enlistadas a continuación, en caso de que sean correctas, darán
“verdadero” o “falso” como resultado.

• ES.IMPAR
• ES.PAR
• ESBLANCO
P á g i n a 12 | 56
Elaborado por: Revisado por: Aprobado por:
 • ESERROR
• ESNOTEXTO
• ESNUMERO
• ESTEXTO
Ejemplo:

5. FUNCIONES DE BÚSQUEDA

Las funciones de búsqueda son de gran utilidad para resolver problemas cotidianos de la
vida laboral, lo que las convierte en las más usadas dentro de una empresa. Es
imprescindible aprender a utilizarlas de manera correcta y explotar todo su potencial. De
todas las funciones de búsqueda que existen, veremos las cuatro que consideramos más
importantes, pero queda en el lector ampliar sus conocimientos sobre las otras funciones.

5.1. “Buscar V” y “Buscar H”

5.1.1. Función “Buscar V”

Como mencionamos recién, en el día a día de las empresas, todo el tiempo es necesario
contar con datos que pueden estar ubicados en otras partes de la hoja (o en otras hojas o
libros), para ello, las funciones “Buscar V” y “Buscar H” nos van a ser de gran ayuda.

La función Buscar V se caracteriza por buscar un valor determinado (“VALOR_BUSCADO”),

en la primera columna de una matriz de datos (“MATRIZ_BUSCAR_EN”) y una vez que lo
encuentra, devuelve el dato contenido en la columna hacia la derecha que le indiquemos
(“INDICADOS_COLUMNAS”).
P á g i n a 13 | 56
Elaborado por: Revisado por: Aprobado por:
Por lo tanto los criterios son:

*ASPECTOS IMPORTANTES:

✓ El “Valor_buscado” puede ser un texto, un número, una fecha, una expresión

matemática o la referencia a una celda. Lo clave, es que ese valor se va a buscar
en la primera columna de la matriz de búsqueda, de manera exhaustiva o de
manera aproximada (lo explicamos en el último argumento)
✓ Tener en cuenta si hay valores repetidos en la primera columna de la matriz de
búsqueda. De ser así, la función devuelve el primero que encuentre.
✓ La “Matriz_buscar_en” puede componerse de una o más columnas, y de tantas
filas como deseemos. Lo importante es que la primera columna de la matriz que
elijamos debe contener al “Valor_buscado”.
✓ “Ordenado”: el último argumento es optativo, y tal vez el más importante. Es el
que vamos a utilizar para indicarle al Excel que el “Valor_buscado” lo encuentre de
manera exacta o bien, de manera aproximada. Por la tanto, las dos opciones para
elegir son:
o FALSO (o el número 0): indica que la búsqueda debe ser exacta. Si la
fórmula no encuentra el valor buscado (tal cual se escribió) en la primera
columna de la matriz, devuelve un error.
o APROXIMADO (o el número 1): La búsqueda la realiza de manera
aproximada. Es decir, busca la coincidencia más cercana al valor buscado.
Tener en cuenta que los datos deben estar ordenados de manera
ascendente para el correcto uso de este argumento. Si se omite el último
argumento, toma por defecto una búsqueda “aproximada”

Ejemplo 1: Coincidencia Exacta

P á g i n a 14 | 56
Elaborado por: Revisado por: Aprobado por:
 Tenemos la siguiente tabla con deportistas famosos, su país de nacimiento y el
deporte que practican. Se requiere colocar en una celda el nombre del deportista, y que
automáticamente en otra celda, aparezca el deporte que realiza. Para ello nos valdremos
de la función BuscarV:

En B8 colocaríamos el nombre del deportista, y en B9 la fórmula. Debería quedar:

Nombre: Michael Jordan

Deporte: =BUSCARV(B8;A1:C6;3;0)

Se puso el Cero (o coincidencia exacta) porque

quiero buscar el nombre tal cual lo escribí.

Ejemplo 2: Coincidencia Aproximada

Suponga que tiene una tabla con los precios de la energía eléctrica, según el consumo.
Colocando en una celda aparte cuanto consumió en el mes, quiere obtener de manera
automática, el monto a pagar.

Si el consumo fue 122kw, ¿cuánto debería pagar?

Si fuese 650kw, ¿cuánto pagaría?

P á g i n a 15 | 56
Elaborado por: Revisado por: Aprobado por:
Para poder armar un modelo, en el que cada vez que cambie el consumo, me devuelva en
otra celda el monto a pagar, debo utilizar la función BuscarV pero con una particularidad.
Como el “Valor buscado” no necesariamente coincide con los datos de la primera columna
(columna “desde”), si en el último argumento de la función coloco “FALSO” (búsqueda
exacta), la fórmula devolvería error. Esto sucede porque, por ejemplo, los 122kw no están
en la primera columna, pero sí sabemos que se encuentran en el intervalo 100 – 350, por
lo que debería pagar $520. Para que el modelo funcione, suponiendo que el consumo se
coloca en la celda B8, la función debería quedar así:
Consumo 122
Respuesta $ 520,00
Fórmula =BUSCARV(B8;A1:C5;3;1)

5.1.2. Función “Buscar H”

Se puede dar en la realidad, tal vez no con tanta frecuencia, que la información que
precisamos se encuentre en filas, y no en columnas como vimos hasta ahora. Para
solucionar ello, usaremos la función Buscar H, que es muy similar a la Buscar V, sólo que
en vez de buscar de izquierda a derecha y devolver una columna como resultado, esta
función va a buscar de arriba hacia abajo, y devuelve una fila.

Para esta función, son válidas todas las reglas vistas en el apartado 5.1, sólo que
cambiando la “perspectiva”; dado que en vez de indicadores columnas, vamos a tener
indicadores filas, y el valor buscado no estará en la primera columna, sino en la primera
fila.

Ejemplo: Dada una tabla con países, capitales e idioma, se desea obtener en una celda la
capital del país que se haya ingresado en otra celda.

Para resolver esto, no podríamos utilizar el buscar V porque como bien ya sabemos,
trabaja de izquierda a derecha, buscando el valor en la primera columna. Por lo que si
quisiéramos obtener la capital de Uruguay, el valor buscado (“Uruguay” en este caso), lo
deberíamos buscar en la primera fila y no en la primera columna. Por ello, usaremos
Buscar H de la siguiente manera:

P á g i n a 16 | 56
Elaborado por: Revisado por: Aprobado por:
*Importante: En el ejemplo se trabajó con una búsqueda exacta (último argumento
“FALSO” o “0”), pero si se tiene que hacer una búsqueda aproximada, aplica todo lo visto
anteriormente.

5.2. “Coincidir” e “Índice”

5.2.1. Función “Coincidir”

Dentro del universo de funciones de búsqueda, la “Coincidir” tal vez no es la más

conocida pero no por ello tiene poca utilidad. Lo que esta fórmula devuelve es la
posición de un valor, en un vector fila o vector columna.

Resaltamos las palabras posición y vector, porque es muy común cometer errores con
esta fórmula, debido a esas dos premisas. La función Coincidir, siempre va a devolver
una ubicación, es decir, un número entero que nos indica en qué parte se encuentra el
valor, dentro de un rango de datos. Si no lo encuentra (y se está buscando de manera
exacta), la función devuelve error. El segundo punto importante, es que la búsqueda
se realiza en un vector, es decir, en una fila o una columna. A diferencia de las
funciones Buscar V y H, y la función índice que veremos a continuación, el Coincidir,
sólo trabaja con filas o columnas, pero no con matrices. Es decir, puedo buscar un
valor en el rango, por ejemplo, “A1:A500”, o bien, “A1:F1”, pero nunca en una matriz
“A1:F500”.

Ejemplo: Ingresado el nombre de un país en una celda, queremos saber en dónde se

ubica.

P á g i n a 17 | 56
Elaborado por: Revisado por: Aprobado por:
 *Tener en cuenta que el resultado fue “3”, que no necesariamente coincide con el
número de fila. Esto es importante, ya que el Coincidir devuelve la posición pero
teniendo en cuenta el vector fila o columna que hayamos elegido, y no el número de
fila o columna real. Por ello, como el rango empezó en A2, la posición de Uruguay a
partir de allí, es la tercera.

Principales utilidades:

• Encontrar rápidamente un valor en una fila o columna

• Saber si un valor existe en una fila o columna. Si buscamos un dato utilizando
el Coincidir, y dicho dato no se encuentra en el vector, la fórmula devuelve
error (de esta manera sabemos que no se encuentra en la lista)
• Como argumento de la función Índice (se verá en el próximo apartado)

*Importante: La función Coincidir tiene como último argumento

“TIPO_DE_COINCIDENCIA”, que cumple el mismo rol que el argumento “ORDENADO” del
Buscar V/H. Es decir, tenemos dos opciones:

o FALSO (o el número 0): indica que la búsqueda debe ser exacta. Si la

fórmula no encuentra el valor buscado (tal cual se escribió), devuelve un
error.
o APROXIMADO (o el número 1): La búsqueda la realiza de manera
aproximada. Es decir, busca la coincidencia más cercana al valor buscado.
Tener en cuenta que los datos deben estar ordenados de manera
ascendente para el correcto uso de este argumento. Si se omite el último
argumento, toma por defecto una búsqueda “aproximada”

5.2.2. Función “Índice”.

Esta función trabaja como el reconocido juego infantil “Batalla Naval”. Le debemos indicar
una matriz, y luego una fila y una columna, y la fórmula devolverá el valor que haya en
esa ubicación dada.

Los argumentos son:

P á g i n a 18 | 56
Elaborado por: Revisado por: Aprobado por:
 • “REF”: Aquí deberemos seleccionar la matriz donde se encuentre el dato que
queremos encontrar. Tener en cuenta que puede ser una fila, una columna, o una
matriz (más de una fila o más de una columna)

• “NUM_FILA”: se debe indicar en qué fila se encuentra el valor que estamos

buscando, dentro de la matriz del primer argumento.

• “NUM_COLUMNA”: Ídem al punto anterior, sólo que se debe indicar la columna.

Ejemplo del uso de la función INDICE. Tener en cuenta que dependiendo del rango
elegido en el primer argumento (“REF”), es cómo van a ser consideradas las filas y
las columnas.

PRINCIPAL UTILIDAD: La función Índice, complementada con la función Coincidir,

permite obtener de manera rápida y flexible, información de una base de datos compleja.
Además, permite solucionar la limitación que tiene la función Buscar V, dado que si el dato
que precisamos se encuentra a la izquierda, de la primera columna de la
“MATRIZ_BUSCAR_EN”, no podremos resolver el asunto porque el Buscar V trabaja de
izquierda a derecha.

La idea es hacer “variable” los argumentos “Num_fila” y/o “Num_columna” de la función

Índice, utilizando para ello el Coincidir. Con un ejemplo vamos a mostrar la gran utilidad:
P á g i n a 19 | 56
Elaborado por: Revisado por: Aprobado por:
Supongamos que tenemos una base de datos, con el nombre, domicilio y DNI de los
afiliados a un club deportivo, y que ingresando el DNI en una celda, obtengamos quién es
el afiliado en cuestión. El supuesto está hecho con 4 personas, pero lo común es que las
bases de datos cuenten con miles de usuarios.

Como mencionamos antes, el Input que vamos a tener es el número de DNI, por lo que
no podríamos aplicar la función Buscar V, ya que el dato que precisamos (“Apellido y
Nombre”), se encuentra a la izquierda del DNI.
Es por ello que usaremos el Índice y el Coincidir para solucionar el asunto. Sabemos que el
Índice devuelve un dato, habiendo indicado previamente en que matriz buscarlo, el
número de fila y de columna. En este caso, la matriz será A1:C5. Sabemos que el número
de columna que precisamos es la 1, ya que es la que contiene los datos del afiliado. En
tanto el número de fila, es el que deberíamos dejar variable, para que vayamos
obteniendo el nombre correcto según el DNI ingresado. Esto lo logramos con la función
Coincidir, ya que es la que nos devuelve la posición de un dato en una fila o columna. En
este caso, si buscamos el DNI que se ingresa, en la columna “C”, obtendremos qué lugar
ocupa ese valor en la columna. Ese resultado se expresa en un número entero, que es el
que va a ocupar el argumento “NUM_FILA” de la función INDICE.

De esta manera, la persona que utilice el modelo, va a ingresar el DNI en la celda “E2” y
automáticamente obtendrá en “F2”, el nombre del afiliado.

P á g i n a 20 | 56
Elaborado por: Revisado por: Aprobado por:
6. FUNCIONES DE BASE DE DATOS

Cuando hablamos de base de datos, estamos haciendo referencias a planillas de Excel con
gran cantidad de información. Es el escenario más común en las empresas, y por eso
todas las herramientas que se presentan en este curso (y el básico) apuntan a agilizar las
operaciones cuando existen muchas filas y columnas.

Para esto, Excel tiene un conjunto de funciones de base de datos, que se identifican
fácilmente porque todas comienzan con la palabra “BD” y luego la función. Al colocar el
igual en una celda y comenzar a escribir BD, se despliega un menú con todas las
funciones disponibles:

Como se puede observar, las funciones “BD” son funciones que ya hemos enseñado (max,
min, suma, promedio, etc.) pero antecedidas por la abreviatura de “Base de datos”. La
particularidad que tienen, es que nos van a permitir obtener información de una manera
más rápida que si lo hiciéramos con fórmulas comunes, o aplicando filtros y subtotales.

Los argumentos son:

• “BASE_DE_DATOS”: aquí se deberá seleccionar el rango de celdas donde se

encuentran los datos. Es recomendable asignarle un nombre a la base de datos

P á g i n a 21 | 56
Elaborado por: Revisado por: Aprobado por:
para así trabajar de manera más sencilla. Lo que debe hacer es seleccionar todos
los datos (recuerde usar el Ctrl + Shift + flechas para agilizar este paso), luego
dirigirse a la ficha “Fórmulas” -> “Administrador de Nombres” -> “Nuevo”. Allí
deberá definir un nombre y controlar que el rango esté correcto (debe contemplar
toda la base de datos). Luego, en el primer argumento de la función BD, en vez de
seleccionar todos los datos, directamente escribirá el nombre que definió en el
paso anterior.

1
2

P á g i n a 22 | 56
Elaborado por: Revisado por: Aprobado por:
 6

• “NOMBRE_DE_CAMPO”: Se debe colocar el nombre de la columna entre comillas,

un número que represente su posición en la lista o la referencia (“A1” por
ejemplo), a la cual se le debe realizar la operación matemática elegida (suma,
max, min, etc.).

• “CRITERIOS”: Corresponde al área donde se especifican las condiciones. Debe

incluir un rótulo de columna y debajo, en otra celda, la condición. Algunas
consideraciones:

o Los criterios que se escriban en la misma fila, son considerados como “Y” y
los que se coloquen uno debajo de otro, se leen como “O”.
o Números y Fechas admiten los operadores matemáticos <,>,<>,=. Si no
se indica ningún operador, se toma como “igual a”
o Textos: se pueden escribir directamente sin el operador “=”, pero el criterio
escrito debe ser exactamente igual a cómo figura en la base de datos
(tener cuidado con los acentos).

Ejemplo de funciones BD: Supongamos que tenemos la nómina de empleados de la

empresa, le asignamos el nombre “Empleados” a la base de datos, y queremos realizar los
siguientes cálculos:
1. La suma de sueldo menores a $15000
2. La cantidad de empleados que ingresaron después del 01/01/2016 y trabajan en
La Pampa
3. Cuál es la edad máxima de los empleados que trabajan en la sucursal de Córdoba
4. Cuántos empleados tienen más de 3 años de antigüedad o ingresos mayores a
$17000

P á g i n a 23 | 56
Elaborado por: Revisado por: Aprobado por:
(Colocamos sólo una parte de la nómina, siendo que la empresa cuenta con más de 1000
empleados).

Para resolver los puntos anteriores, podemos aplicar las funciones BD. Luego de asignar el
nombre a la base de datos, procedemos a armar los criterios y las fórmulas:

P á g i n a 24 | 56
Elaborado por: Revisado por: Aprobado por:
7. VALIDACIÓN DE DATOS

Una herramienta útil para complementar todo lo visto hasta ahora, es la conocida como
“Validación de datos”. Ésta, entre otras funciones, permite limitar el accionar del usuario,
impidiendo que ingrese datos que no correspondan, o no existan, y además, alertarle
sobre dónde está el error.

Veremos sólo la opción de “lista” del menú de validación, pero queda para el lector
ampliar este tema según sus necesidades.

En todos los ejercicios anteriores, siempre supusimos que el usuario ingresaría

correctamente los datos (nombre del país, jugador afiliado, DNI, etc.), y así nuestras
fórmulas funcionarían ok. El problema es que si el usuario tipea mal un dato o ingresa uno
inexistente, nuestra fórmula deja de funcionar y genera una mala experiencia de uso de la
planilla. Para solucionar esto, podemos utilizar la opción “lista” del menú “validación de
datos” en la ficha “datos”. Pero primeramente, en una nueva columna (que luego
podemos ocultar), deberemos escribir la lista de datos que el usuario podrá elegir (nombre
de los jugadores, países, etc.). Luego, posicionados en la celda donde la persona debería
escribir el país (en este caso), ingresar al menú como se indicó antes:

3
4

5
2
1

P á g i n a 25 | 56
Elaborado por: Revisado por: Aprobado por:
8. FORMATO CONDICIONAL

8.1. Características y tipos de formatos condicionales

En el curso de Excel, nivel básico, se abordó el tema de los formatos, distinguiendo entre:

• Formatos Personalizados: que son aquellos que permitían cambiar el “estilo” de

una celda, sin alterar el dato originario. Por ejemplo, transformar el número “1” en
“$1” o en “100%”.
• Formatos Condicionales: Permiten cambiar el estilo (formato) de una celda, si se
cumple una condición determinada. Se basan en “reglas” que establecen
condiciones para que, en caso de cumplirse, se ejecute el formato elegido.

En este primer capítulo, vamos a interiorizarnos en el segundo de los formatos.

Es importante distinguir la ubicación dentro de la ficha “INICIO”, que caracteriza a cada

uno de los formatos, por ello en la imagen debajo se explicita este aspecto:

Form. Personalizado Form. Condicional

Antes de comenzar con cada uno de los formatos condicionales, es importante saber que
para aplicar los mismos, tenemos dos opciones:

A. Seleccionar todas las celdas a las cuales le queremos aplicar el formato, y luego
seleccionar el tipo de formato.
B. Elegir el formato, y luego configurar a qué celdas se aplica.

Una vez dentro del menú “Formato Condicional”, veremos que se despliega un menú con
varias opciones. Analizaremos cada una:

P á g i n a 26 | 56
Elaborado por: Revisado por: Aprobado por:
 1) “RESALTAR REGLAS DE CELDAS”: En este apartado encontraremos una serie de
opciones que nos permitirán aplicar formatos a las celdas, si las mismas cumplen
con una condición numérica, de texto, de fecha o si el valor es duplicado:

a. “Mayor/Menor que”, “Entre”, “Es igual a”: Permiten cambiar el estilo

de una o más celdas, si las mismas cumplen con el criterio (numérico)
elegido.
b. “Texto que contiene”: útil para resaltar o personalizar celdas que tienen
alguna letra o texto en su interior.
c. “Una fecha”: similar a los puntos anteriores, pero la condición para aplicar
el formato será una fecha. Tenga en cuenta que el Excel actualiza
automáticamente la fecha del día de hoy.
d. “Duplicar valores”: Personaliza todas las celdas que están repetidas. Es
útil para identificar rápidamente si hay valores que aparecen más de una
vez en una base de datos.

Debajo, un ejemplo de una tabla de registro de pacientes y los turnos, donde con el uso
de formatos condicionales, se precisó resaltar:
• Los apellidos repetidos
• Los nombres que tuviesen la letra “F”
• Los mayores a 20 años
• Los que tuviesen turno al día siguiente (la planilla se elaboró el 11/07)

P á g i n a 27 | 56
Elaborado por: Revisado por: Aprobado por:
 Apellido Nombre Edad Día Turno
Perez Luis 15 12-jul
Martinez Juan 28 12-jul
Peura Florencia 29 15-jul
Perez Martina 14 16-jul
Damiani Felix 10 16-jul

2) “REGLAS SUPERIORES E INFERIORES”: Lo importante de los formatos que se

incluyen en este apartado, es que el Excel va a realizar algunos cálculos básicos
(promedio, máximo, mínimo, por ejemplo) de todas las celdas elegidas, y luego va
a aplicar el formato a las que cumplan la condición que precisamos.

a. “10 (%) superiores/inferiores”: Las 4 primeras reglas, permiten

personalizar celdas cuyos valores sean los máximos o mínimos, absolutos o
relativos, de todas las celdas elegidas. Si bien el menú infiere “10
superiores”, se pueden elegir valores entre 1 y 1000 (es decir, se podría
armar los “5 superiores” o los “999 inferiores”, por ejemplo).
b. “Por encima/debajo del promedio”: En este punto, el Excel calcula el
promedio de las celdas elegidas, y aplica el formato elegido a aquellas cuyo
valor esté por encima (debajo) del promedio calculado por el programa.

En la tabla siguiente, registraron las notas de dos exámenes y el promedio de cada

alumno. Con el uso de formatos, se resaltó:
• Las 2 menores “Nota 1”
• Las 3 “Nota 2” superiores
• En la columna promedio, las celdas que estén por encima del promedio general de
la columna.

Alumno Nota 1 Nota 2 Promedio

Luis 7 7 7
Juan 5 3 4
Florencia 9 7 8
Martina 9 9 9
Félix 2 4 3
Prom de la columna: 6,2

3) “BARRA DE DATOS”: Este tipo de formato va a aplicar una barra de intensidad,

dentro de las celdas seleccionadas, según el valor que cada celda contenga. Por
defecto, el Excel va a interpretar que a la celda con mayor valor de todas las
P á g i n a 28 | 56
Elaborado por: Revisado por: Aprobado por:
 elegidas, le va a corresponder la barra de mayor longitud (o mayor intensidad) y al
valor más pequeño, la de menor largo. Debajo explicamos cómo configurar esta
opción y las demás.

A continuación, un ejemplo con las deudas de los clientes de una empresa:

4) “ESCALAS DE COLOR”: Similar a la barra de datos, sólo que se aplica un color

según el valor contenido. Por defecto a mayor valor, color más intenso y viceversa.
Explicaremos cómo modificar esto.

Tomando la tabla del ejemplo anterior, pero utilizando las escalas de color.

5) “CONJUNTO DE ICONOS”: Aquí encontraremos una vasta cantidad de símbolos e

íconos que nos permitirán resaltar celdas según el criterio que hayamos
establecido. Una vez elegido el ícono, el Excel lo aplica según parámetros
automáticos, y es muy probable que sea necesario modificar.

Utilizando iconografía semáforo, se estableció que el semáforo rojo, debe aparecer cuando
la demora de pago es mayor o igual a 15 días. El amarillo, aplica cuando la demora está
entre 10 y 15, y el verde para los casos menores a 10.

P á g i n a 29 | 56
Elaborado por: Revisado por: Aprobado por:
Tanto para los últimos 3 incisos, como para los anteriores, si uno desea modificar algún
aspecto del formato condicional, la forma más práctica para hacerlo es seleccionando
“Administrar reglas” que se encuentra al final de las reglas antes mencionadas.

Dentro de este menú, tenga en cuenta que por defecto, el Excel muestra las reglas (o sea,
los formatos condicionales) sólo aplicados a la celda donde se está posicionado. Debe
cambiar la opción y elegir “esta hoja”:

Una vez dentro del menú, y visualizando todas las reglas creadas, puede:

• Crear una nueva regla: “Nueva regla”

• Cambiar los criterios, colores, fuentes, estilos y demás, de alguna regla existente:
“Editar regla” (primero debe hacer un clic en la regla que desea cambiar)
• Cambiar las celdas a donde se está ejecutando el formato: “Se aplica a” contiene la
celda o rango donde el formato se está aplicando.
• Eliminar una regla: primero la selecciona, y luego elige “Eliminar Regla”

*Tip Importante: Se pueden hacer variables los criterios de los formatos explicados
anteriormente. Se puede, por ejemplo, resaltar las celdas que sean mayores a un número
que el usuario ingrese en la celda “K1”. Para ello, dentro de la “regla” del formato elegido,

en vez de colocar un número, se puede elegir el botón y luego hacer clic en la celda
donde se colocará el valor.

P á g i n a 30 | 56
Elaborado por: Revisado por: Aprobado por:
 8.2. Reglas basadas en fórmulas

Todo lo visto en los puntos anteriores, fueron reglas que se aplicaban, basándose en los
valores que contenían las celdas. En este apartado, se explicarán cómo se pueden armar
formatos condicionales pero cuyas reglas se basen en fórmulas.

Esta opción que nos brinda el formato condicional, tal vez es la más utilizada por su
flexibilidad y potencialidad, pero requiere cierta lógica y práctica para su implementación.
Lo que se hace es armar una “prueba lógica”, donde se pueden aplicar todas las fórmulas
ya vistas (y las que veremos más adelante), y que, de cumplirse esa prueba lógica en la
celda, se aplica un formato establecido. Para escribir la fórmula y seleccionar el formato a
aplicar, se debe ingresar a la opción “Nueva regla” –> “Utilice una fórmula que determine
las celdas para aplicar formato”

P á g i n a 31 | 56
Elaborado por: Revisado por: Aprobado por:
Debajo se mostrará con un ejemplo sencillo, pero a lo largo del curso retomaremos este
punto, para ir mejorando su uso con las nuevas fórmulas que veamos.

Dada una lista de servicios que se deben pagar y el saldo que se dispone en el banco, se
desea resaltar con rojo, de manera automática, cuando el saldo adeudado supere a la
disponibilidad de dinero en el banco.

P á g i n a 32 | 56
Elaborado por: Revisado por: Aprobado por:
9. CÁLCULOS CONDICIONALES

9.1. Funciones con condiciones

A lo largo del curso anterior y lo visto hasta ahora, se han ido enseñando distintas
fórmulas, desde matemáticas hasta lógicas, pasando por fechas y búsqueda, que todas
tienen su particularidad y su funcionalidad. En este apartado se mostrarán funciones con
condiciones, que son aquellas que combinan dos tipos de funciones, por un lado las
matemáticas (suma, promedio, contar) y por otro lado la condicional, es decir, la función
“SI”.

Cuando tengan casos reales en donde se debe sumar una cantidad vendida pero sólo si la
misma fue hecha por un vendedor en particular, o en una fecha determinada, o si el
monto vendido es mayor a un número, pueden utilizar filtro (vistos en el curso anterior) o
bien, funciones con condiciones. Ellas son:

• “SUMAR.SI”: va a sumar un determinado rango, si ese u otro rango cumple con

una condición dada.
• “PROMEDIO.SI”: idéntico a la anterior, sólo que aplica la operación matemática
“promedio”
• “CONTAR.SI”: Devuelve la cantidad de celdas que cumplen una condición dada.

Como se puede observar, estas tres funciones las vamos a usar en casos donde la
operación matemática no se pueda aplicar a todo el rango de datos, sino a una parte que
cumpla una determinada condición.

*Tip Importante: Es clave destacar que estas 3 funciones indicadas, aplican si la regla
(condición) es única. Cuando tengamos más de un requisito para
sumar/promedias/contar, precisaremos valernos de las funciones conjunto. Es decir, si
queremos sumar las ventas del vendedor 6, por ejemplo, usaremos “sumar.si”, pero si
precisamos sumar las del vendedor 6 y las que hizo sólo en Córdoba, al tener más de una
condición, aplicaremos “sumar.si.conjunto”.

Argumentos:

➢ “RANGO”: Aquí deberemos elegir el conjunto de celdas que deberán

cumplir el criterio que vamos a aplicar. Esto es clave, ya que no se puede
elegir un rango donde luego no se pueda aplicar el criterio.
➢ “CRITERIO”: Es la condición necesaria para que el Excel aplique la fórmula
matemática a las celdas que cumplan dicho criterio. Lo importante, es la
relación directa entre “criterio” y “Rango”. Si decido sumar las ventas que
se hayan hecho en Córdoba, el “criterio” será “Córdoba” y el “rango” es
toda la columna (o fila) donde estén las provincias (Entre ellas el criterio
buscado, en este caso “Córdoba”)
P á g i n a 33 | 56
Elaborado por: Revisado por: Aprobado por:
 ➢ “RANGO SUMA (PROMEDIO)”: Es el rango al que finalmente se le va a
aplicar la operación matemática. Es lo que se va a sumar/promediar/contar.
Siguiendo el ejemplo anterior, luego de elegir el “Rango” (columna de
“Provincias” y el “criterio” (“Córdoba”), el rango suma será la columna
donde estén todas las ventas.

Criterios: Un tema crucial en este apartado es el de cómo se escriben los criterios de las
fórmulas mencionadas (y las que vienen a continuación), ya que son los que van a
permitir obtener el resultado deseado. Van algunas consideraciones:

✓ NÚMEROS:

o Igual a un número: colocar directamente el número

o Mayor/Menor: se debe colocar entre comillas, el operador matemático (> o
<), el símbolo ampersand (&) y luego el número deseado. Si se precisa
“mayor igual o menor igual”, se procede de la misma manera pero se
coloca el símbolo igual (“=”) luego del signo “>” o “<”.
o Diferente de: si se desea excluir de la suma/promedio/contar, ciertos
números, se utiliza la expresión “<>”, de la misma forma que la indicada
en el punto anterior

Ej.: las ventas mayores o iguales a $2000, el criterio se escribiría:

“>=” & 20000
Las ventas que no hayan sido del vendedor 6:
“<>” & 6

✓ FECHAS:
o Igual, mayor, menor a una fecha: símil a la expresión del punto anterior, es
decir Operador Matemático (=, <,>, <=,>=, <>) más el Ampersand (&) y
luego debería ir la fecha. Para ello, entre todas las opciones que hay,
vamos a indicarle dos:
▪ Usar la función “FECHA”, y allí colocar el dato.
▪ Hacer referencia a una celda donde este la fecha.

Ej.: las ventas que hayan sido después del 20/06/2017, el criterio se escribiría:
• Usando Función Fecha: “>=” & fecha (2017;06;20)

• Suponiendo que la fecha está en “B1”: “>=” & B1

✓ TEXTOS:
o Igual: se debe escribir el criterio entre comillas.

P á g i n a 34 | 56
Elaborado por: Revisado por: Aprobado por:
 o Distinto de: se usa entre comillas la expresión “<>”, más el & y luego el
texto entre comillas.

Ejemplo Integrador:

Dada la tabla anterior, que contiene las ventas realizadas en un período de tiempo, se
desea conocer:
1. El monto total, pero de las ventas mayores a $10000
2. El promedio de las ventas del vendedor 1
3. La cantidad de operaciones que se hicieron después del 27/05/2017
4. El monto vendido total, excluyendo las realizadas a “Cobal SRL”.
Note que en el punto 1, se podría excluir el “rango de suma” porque coincide con el rango de
criterio.

Van las respuestas:

Fórmula Resultado
=SUMAR.SI(C2:C9;">" & 10000;C2:C9) $ 127.000,00
=PROMEDIO.SI(D2:D9;1;C2:C9) $ 9.000,00
=CONTAR.SI(A2:A9;">="& FECHA(2017;5;27)) 5
=SUMAR.SI(B2:B9;"<>" & "Cobal SRL";C2:C9) $ 129.000,00

9.2. Funciones “Conjunto”

Como se indicó en el apartado anterior, las 3 funciones condicionales que se vieron tienen
su limitación cuando precisamos sumar, promediar o contar rangos, pero con más de un
criterio. Para solucionar esto, existen 3 funciones similares, pero que permiten agregar
todos los criterios que precisemos. Ellas son:

P á g i n a 35 | 56
Elaborado por: Revisado por: Aprobado por:
 • SUMAR.SI.CONJUNTO
• PROMEDIO.SI.CONJUNTO
• CONTAR.SI.CONJUNTO

La funcionalidad es la misma que la ya explicada, sólo que vamos a realizar operaciones

matemáticas a las celdas que cumplan más de un criterio. Lo único importante de
destacar, es que en estas fórmulas el argumento “rango suma (promedio)” se encuentra
primero, y luego lo siguen el “rango criterio 1” y el “Criterio 1” y así sucesivamente.

Ejercicio: tomando los datos del ejercicio anterior, se desea calcular:

1. El monto total, pero de las ventas mayores a $10000 y menores a $28000
2. El promedio de las ventas menores a $22000 y que no sean del vendedor 1
3. La cantidad de operaciones que se hicieron entre el 27/05/2017 y el 25/06/2017
4. El monto vendido total después del 11/05/2017 y a su vez, excluyendo las
realizadas a “Cobal SRL”.

Fórmula Resultado
=SUMAR.SI.CONJUNTO(C2:C9;C2:C9;">" & 10000;C2:C9;"<" & 28000) $ 47.000,00
=PROMEDIO.SI.CONJUNTO(C2:C9;C2:C9;"<" & 22000;D2:D9;"<>"& 1) $ 12.000,00
=CONTAR.SI.CONJUNTO(A2:A9;">="& FECHA(2017;5;27);A2:A9;"<=" &FECHA(2017;6;25)) 4
=SUMAR.SI.CONJUNTO(C2:C9;A2:A9;">" & FECHA(2017;5;11);B2:B9;"<>" & "Cobal SRL") $ 121.000,00

P á g i n a 36 | 56
Elaborado por: Revisado por: Aprobado por:
10. PROTECCION DE PLANILLAS DE CÁLCULO.

Teniendo todas las herramientas vistas hasta el momento, es probable que las planillas
que armen a futuro tengan cálculos complejos y una funcionalidad específica, por lo que
es necesario, por un lado, cuidar de que el usuario no modifique ni elimine celdas con
fórmulas, y por el otro, resguardar el “know-how” para evitar que alguien pueda “copiar”
lo que ustedes hayan realizado.

Para ello, una vez finalizado el modelo en planilla de cálculo, se recomienda proteger
hojas, libros, y/o celdas, ingresando a las opciones que se encuentran en la ficha
“REVISAR”.

• “PROTEGER HOJA”: Una vez seleccionado este menú, se nos despliega una lista de
acciones que deberemos tildar para habilitárselas o no, al futuro usuario. Lo que
esté con marcado, se podrá realizar estando la hoja protegida. Además, tiene la
opción de utilizar una contraseña para aumentar la seguridad (si no coloca
ninguna, sólo con entrar a la ficha “REVISAR” y elegir “Desproteger hoja”, el libro
vuelve al normal funcionamiento).

• “PROTEGER LIBRO”: Si uno desea que el usuario no agregue, modifique ni elimine

hojas, deberá seleccionar esta opción. Como se comentó en el punto anterior, aquí
también se podrá utilizar una contraseña para elevar el nivel de seguridad.

• PROTECCION DE CELDAS: Es importante configurar, antes de proteger la hoja,

cuáles celdas se desean bloquear y/u ocultar. Esto se determina posicionado en la
celda que se desea bloquear, ingresando al formato personalizado (en la ficha
P á g i n a 37 | 56
Elaborado por: Revisado por: Aprobado por:
Inicio, o bien, haciendo Ctrl + 1), y allí, en el menú “Proteger”, tildar la opción
“Bloqueada”. Seleccionando esto y luego protegiendo la hoja, el usuario no podrá
modificar el contenido de la celda (salvo que desproteja la hoja previamente). Si
además se quiere ocultar la fórmula que se elaboró en alguna celda, deberá tildar
la opción que dice “Oculta”. De esta manera, la persona no podrá modificar el
contenido de una celda ni ver en la barra de fórmulas, justamente la fórmula que
se realizó:

P á g i n a 38 | 56
Elaborado por: Revisado por: Aprobado por:
 11. GESTIÓN DE DATOS EXTERNOS

Es importante entender que el uso de las planillas de cálculo no se limita a datos que uno
ingrese manualmente y que luego puede imprimir o enviar por mail.

Al ser un programa de Microsoft, se tiene una excelente compatibilidad con los otros
desarrollos de la empresa, como son Access, Word, PowerPoint, y además, con otros
recursos como archivos de texto (txt) o fuentes de programación (SQL, XML, otros).

11.1. Importar/Exportar Datos

Puede suceder en el ámbito laboral o personal que tengamos información en otras
aplicaciones (de Office u otros programas) y queramos tenerla en un Excel para trabajar
con todo lo aprendido hasta ahora. Esto se puede hacer ingresando a la ficha “DATOS” -
>“Obtener datos externos”, y eligiendo de qué fuente queremos importar la información.
Allí se nos desplegará un menú donde deberemos elegir la ubicación del archivo en
nuestra PC y luego Excel nos indicará cómo avanzar para hacer una correcta importación.

Para exportar las planillas de cálculo que elaboremos, debemos ingresar a “Archivo” ->
“Exportar” y allí elegir la opción que deseemos. Esto es útil cuando se quiere obtener un
PDF del trabajo que hayamos realizado en Excel. Tener en cuenta que si se desea

P á g i n a 39 | 56
Elaborado por: Revisado por: Aprobado por:
exportar a otro programa (Access, Power Point, etc), se deberá ingresar a dicho
programa, y desde allí elegir importar desde planilla de cálculo, o bien, valerse del
portapapeles.

*IMPORTANTE: Al importar datos externos, cuando se modifiquen dichos datos en la

fuente original y se seleccione “Actualizar todo” en Excel, automáticamente las
modificaciones aparecerán en nuestra hoja de cálculo. Si uno exporta planillas de Excel a
otras fuentes, cuando las modifique y actualice, los cambios se verán reflejados en los
programas de destino que estén vinculados. Es decir, altero datos de la fuente, y los datos
del destino también se cambian.

*TIP: Entre muchos programas, es compatible el uso del portapapeles para

importar/exportar datos. Para ello, deberá seleccionar los datos y elegir “copiar” (Ctrl + C)
y luego Pegar (Ctrl + V).

11.2. Consultas Web

Siguiendo lo visto en el punto anterior, puede darse la situación de que la información que
precisamos incorporar se encuentre en algún sitio web y no en un archivo alojado en
P á g i n a 40 | 56
Elaborado por: Revisado por: Aprobado por:
nuestra pc. Para ello, Excel permite elegir la dirección web y qué parte de la página se
desea incorporar. Lo importante de esta herramienta, es que cada vez que actualicemos,
los datos incorporados desde la web, si cambiaron, se actualizarán en la hoja de cálculo
(esto es importante, por ejemplo, para tener la cotización de monedas actualizada).

*TIP: Para configurar las propiedades de los datos externos, entre ellos el período de
actualización, deberá ingresar a “DATOS” -> “Propiedades”.

P á g i n a 41 | 56
Elaborado por: Revisado por: Aprobado por:
 11.3. Relación entre distintos libros de Excel

Si bien algo se explicó en el curso anterior, vamos a detallar cómo trabajar con
información que esté contenida en las hojas u otros libros de Excel.

• TRABAJAR CON DISTINTAS HOJAS: Excel nos permite utilizar información que está
contenida en otras hojas del mismo libro. En la práctica, sólo hay que seleccionar
la celda o rango de la hoja donde se encuentre el dato. Sí es importante saber,
que en la celda veremos que se escribe primero el nombre de la hoja, seguido por
el símbolo “!” y luego la celda o rango que seleccionamos. Esto es lo que nos
permitirá saber si estamos trabajando con datos de la hoja activa, o de otra hoja.

La estructura debería quedar =NombreDeLaHoja!A1:B1

Ejemplo: Supongamos que tenemos un libro con dos hojas, una con la lista de insumos de
una empresa (“insumos”) y otra donde todos los días se actualiza el tipo de cambio
(“Cotizaciones”). Al estar los insumos en dólares, necesito tener todos los días el precio en
pesos, pero actualizado según el tipo de cambio. Lo que se puede hacer, es tomar el dato
que está en la otra hoja, y aplicar la operación matemática necesaria:

P á g i n a 42 | 56
Elaborado por: Revisado por: Aprobado por:
• TRABAJAR CON DISTINTOS LIBROS: Es común ver que las personas trabajan con
varios libros de Excel al mismo tiempo, y muchos datos se duplican o se repiten
cálculos por no saber que se pueden conectar estos libros.
La metodología es idéntica a la de trabajar con otras hojas; sólo va a cambiar la
forma en que Excel nos indica que la hoja se encuentra en otro libro. Si uno desea
tomar datos de hojas de otros libros, solamente debe tenerlo abierto, y luego
dirigirse a la celda o rango con el que quiere interactuar. Excel va a mostrarnos
entre comillas simples, primero el nombre del libro (entre corchetes), luego el
nombre de la hoja, el símbolo “!” y luego la celda o rango:

= '[Libro1.xlsx]Hoja'!$A$1

Ejemplo: Siguiendo el ejemplo anterior, supongamos que todos los días por mail
nos envían las cotizaciones de las monedas en un libro llamado “Cotiz_Monedas”,
que tiene una hoja por cada divisa. Si la cotización del dólar está en la celda “D4”,
de la hoja “Dólar”, entonces Excel lo mostrará:

P á g i n a 43 | 56
Elaborado por: Revisado por: Aprobado por:
 *IMPORTANTE: Si tiene celdas que dependan de datos en otros libros y cierra
estos libros, los datos no se modificarán, sólo cambiará visualmente la referencia,
ya que aparece la dirección completa de dónde esté alojado el archivo. Cuando
vuelva a abrir el libro, esto desaparecerá y se actualizarán (si hubo algún cambio)
todas las hojas relacionadas.

12. FILTRO AVANZADO

Excel posee dos tipos de filtros, el conocido como “Autofiltro” y el “Filtro Avanzado”. En el
curso básico nos encargamos del primero, por lo que ahora explicaremos el segundo.

La finalidad del filtro avanzado es similar a la del autofiltro, en cuanto que nos permite
visualizar, sobre una base de datos, sólo aquellas filas que cumplan determinadas
condiciones. La ventaja que presenta esta herramienta en comparación del autofiltro, es
que vamos a poder trasladar automáticamente a otra hoja (o a otro lugar de la misma
hoja) los datos que cumplan los criterios establecidos. Con esto, solucionamos la
“limitante” que tiene el autofiltro de que, al borrar el mismo, se reestablece la base de
datos original y se “pierden” los filtros que habíamos armado.

Posicionado en alguna celda de la tabla a filtrar, ingresamos a la opción “filtro avanzado”

en la misma ficha donde está el autofiltro. Aparecerá un menú similar a éste:

P á g i n a 44 | 56
Elaborado por: Revisado por: Aprobado por:
 ➢ Las primeras dos opciones, son las que permiten aplicar el filtro avanzado
sobre la base de datos, y visualizar allí mismo las celdas que cumplen los
criterios (sería lo mismo que hacer un autofiltro) o bien, “trasladar” la tabla
filtrada a un nuevo lugar, que puede ser en la misma hoja u en otra (esto
se selecciona en el argumento “COPIAR A:”

➢ “Rango de la lista”: aquí deberemos seleccionar toda la base de datos

donde queremos que aplique el filtro.

➢ “Rango de criterios”: definiremos el rango donde escribimos los criterios.

Éstos los expondremos igual que como lo hicimos en las funciones BD,
indicando el rótulo de la columna, y debajo el criterio. Recuerde:

o Los que estén ubicados en la misma fila serán considerados como

“y” y a los criterios que estén en distintas filas, el Excel los toma
como “o”.
o Números y Fechas admiten los operadores matemáticos <,>,<>,=.
Si no se indica ningún operador, se toma como “igual a”.
o Textos: se pueden escribir directamente sin el operador “=”, pero el
criterio escrito debe ser exactamente igual a como figura en la base
de datos (tener cuidado con los acentos).

Ejemplo: tenemos una base de datos con los registros de ventas de la empresa, y
deseamos poder visualizar:
1. Las ventas que hayan sido en la provincia de Córdoba
2. Las que hayan sido mayores a $30000 y en Buenos Aires
3. Las que hayan tenido precios de venta entre $100 y $1000 y se hayan efectuado en
Febrero y Marzo, o el cliente haya sido “Cross SRL”

P á g i n a 45 | 56
Elaborado por: Revisado por: Aprobado por:
Van los filtros aplicados junto con el criterio correspondiente:

1)

2)

P á g i n a 46 | 56
Elaborado por: Revisado por: Aprobado por:
 3)

13. TABLAS Y GRAFICOS DINÁMICOS

13.1. Concepto
Tal vez una de las herramientas más poderosas que tiene Excel para la presentación de
informes, es la Tabla Dinámica (y gráfico dinámico). Su popularidad se debe a que nos
permite, partiendo de una base de datos, armar a nuestro criterio infinitas combinaciones
de esos datos, y así arribar a informes o presentaciones mucho más útiles, que ver los
datos como estaban originalmente. Además, permite adicionar cálculos matemáticos
(“campos calculados”), filtrar la tabla dinámica según criterios y resumir o segmentar
información de manera sencilla. Otro punto clave a destacar, es el “dinamismo” de estas
herramientas, ya que al modificarse los datos originales, se actualizan las tablas y gráficos
dinámicos que dependan de esos datos (no es automático, sino que uno debe seleccionar
“actualizar datos”).

13.2. Confección de una TD

Posicionados en una base de datos que tengamos en una hoja, iremos a la ficha
“INSERTAR” -> “TABLA DINAMICA”. Allí apareceré un menú como el siguiente:

P á g i n a 47 | 56
Elaborado por: Revisado por: Aprobado por:
Donde en la primera opción, elegiremos el rango de datos que estarán incluidos en la
Tabla Dinámica (TD). En este caso, la hoja “Ventas”, rango A1:K200.

Luego definiremos si queremos a la TD en la misma hoja donde está la BD, o bien, en una
nueva hoja.

Una vez definidas estas opciones, veremos que se nos despliega un menú a la derecha
(“Lista de Campo”), donde se verán los rótulos de las columnas de la BD, y debajo, cuatro
cuadrantes. Antes de armar la TD, es importante tener en claro a dónde se quiere llegar;
en otras palabras, cómo es el informe que queremos armar. Luego, se debe ir
“arrastrando” (o haciendo clic) cada rótulo de columna al cuadrante correspondiente.
Veremos que a medida que hacemos esto, en la hoja se irá colocando la información de la
BD originaria, pero adaptada a nuestro criterio.

P á g i n a 48 | 56
Elaborado por: Revisado por: Aprobado por:
Los cuadrantes son:

• “FILTROS”: Aquí colocaremos aquellas

columnas que trabajaran como filtros de la
información de la TD. Aplica igual que los filtros
comunes, por lo que su uso es idéntico.

• “FILAS”: Al estar armando informes similares a

cuadros de doble entrada, aquí deberemos incluir
todas las columnas de la BD original, que en la TD
van a ser colocadas como filas.
COLUMNAS
DE LA BD • “COLUMNAS”: ídem al inciso anterior, sólo que
serán las columnas de la TD.

• “VALORES”: es la parte más importante, ya

que es el “relleno” de la TD. Son los datos que se
quiere mostrar en cada fila o cada intersección de
fila y columna.

Ejemplo. Tomando los datos de las ventas y devoluciones de venta de una empresa,
armaremos distintos informes.

P á g i n a 49 | 56
Elaborado por: Revisado por: Aprobado por:
1) Ventas netas agrupadas por líneas.

P á g i n a 50 | 56
Elaborado por: Revisado por: Aprobado por:
2) Ventas brutas y cantidad, agrupadas por Subrubro. (Observe que esta filtrado sólo
las FACT A y no las notas de crédito).

3) Monto de facturas y notas de crédito, de Febrero a Julio, por Subrubro.

P á g i n a 51 | 56
Elaborado por: Revisado por: Aprobado por:
 13.3. Confección de un Grafico Dinámico.

Luego de haber visto y practicado el punto anterior, le será fácil abordar el tema de
Gráficos Dinámicos (GD). Los mismos trabajan con la misma lógica que las TD, ya que
parten de una base de datos, se confeccionan a gusto del usuario y se actualizan según
haya cambios en la BD.

Lo importante a saber, es que cuando creemos un GD, el Excel nos habilitará también el
menú de TD, dado que las dos herramientas trabajan de manera complementaria. El
gráfico se elabora a partir de la TD, por ello veremos que la lista de campo es igual y la
interacción con los cuadrantes también.

Luego de insertar un GD (“INSERTAR” -> GRAFICOS -> “Grafico Dinámico”), podremos

confeccionarlo a nuestro interés (igual que como se mostró en el punto anterior) y
además, configurarlo según el reporte que precisemos armar.

Algunos ejemplos manteniendo los datos vistos en el punto anterior:

Cuando trabajen con

gráficos, y tengan
que cambiar alguna
configuración sea de
formato o de valores,
utilice los dos botones
que aparecen al
margen derecho del
gráfico.

P á g i n a 52 | 56
Elaborado por: Revisado por: Aprobado por:
 13.4. Opciones comunes para TD y GD

A continuación enunciaremos características comunes que tienen las dos herramientas

vistas:

✓ Cuando se inserte una TD o un GD, automáticamente veremos un nuevo menú

sobre las fichas ya conocidas, con la leyenda: “Herramientas de Tabla
(Grafico) Dinámico”. Tener en cuenta que esto se habilita sólo si uno está
posicionado en la tabla o gráfico. Este menú es el que nos permitirá configurar
todas las opciones que estas herramientas poseen. Allí dentro tendremos 2 o 3
fichas dependiendo de qué estemos utilizando:

o “ANALIZAR”: Aquí lo más importante es el poder definir un nombre para la

tabla o gráfico, actualizar los datos (recuerde que la actualización no es
automática), cambiar el origen (importante si alteramos el rango en la BD
originaria), segmentar la información e insertar campos calculados
(explicado más adelante)

o “DISEÑO”: incluye todas las opciones para mejorar la visualización de la

información. En el caso de gráficos, nos permitirá cambiar el tipo de gráfico
y elegir diseños predeterminados.

o “FORMATO”: Sólo disponible para GD, es el que nos permitirá adicionar

elementos al gráfico para que éste quede aún mejor. Por ejemplo, utilizar
formas (flechas, cuadros) para agregar/aclarar información del gráfico.

P á g i n a 53 | 56
Elaborado por: Revisado por: Aprobado por:
✓ ORDEN Y FILTRADO: podremos disponer de todas las herramientas de filtros y
orden que vimos anteriormente, aplicadas a TD y GD. Lo primero se hace desde el
botón de filtro que aparece en los encabezados, en tanto el orden se configura con
el botón derecho sobre la tabla o gráfico.

✓ “CONFIGURACIÓN DE CAMPO DE VALOR”: Esto es sumamente importante, ya que

es el menú desde el cual elegiremos cómo mostrar los datos en la TD o GD. De los
4 cuadrantes, el campo “valores” admite, dentro del menú que se despliega
presionando el botón al final de rótulo de la columna, esta opción la cual consta de
dos partes importantes:

o “Resumir valores por”: elegiremos la operación matemática que queremos

que se aplique a los datos del campo.
o “Mostrar valores como”: útil para transformar los datos en cálculos sobre el
total de filas o de columnas, por ejemplo. De esta manera, la información
se presenta con un valor agregado sin necesidad de hacer cálculos
adicionales.

P á g i n a 54 | 56
Elaborado por: Revisado por: Aprobado por:
 13.5. Campos Calculados

Un último complemento que vamos a ver en este módulo, son los Campos Calculados.
Cabe aclarar que Tablas/Gráficos dinámicos constan de muchísimas herramientas más,
que las pueden explorar desde las opciones ya explicadas.

Una bondad que poseen TD y GD es la posibilidad de agregar operaciones matemáticas,

sin necesidad de alterar los datos de la BD originaria. Es decir, se puede evitar agregar
columnas a los datos originarios utilizando la opción de “Campos Calculados” (CC). Lo que
estos permiten es aplicar una operación matemática entre una o más columnas, y la
misma se añade a la TD como una columna extra. Es importante tener en cuenta que un
CC realiza la operación con todos los datos de la columna interviniente; por ello no
siempre se puede utilizar esta herramienta (con un ejemplo lo dejaremos en evidencia).
Además, al ser también un concepto dinámico, cambiará cuando se modifiquen los datos
originarios.

Los CC se crean desde la ficha “ANALIZAR” -> “CALCULOS ->”Campos, elementos y

conjuntos” -> “Campo Calculado”.

Veremos que tenemos dos ítems a completar, uno es el nombre que llevara el CC y el
otro es la fórmula matemática. Tener en cuenta que debajo se nos habilitan todos los
nombres de las columnas de la BD originaria más los CC que hayamos creado. Haciendo
doble clic los elegimos, y luego podemos utilizar cualquier operador matemático enseñado.

En el ejemplo, se puede ver que al no haber calculado el IVA en la BD originaria, se puede

crear un CC con el producto entre el monto y el 21%. Así la TD constará de esa
información, pero sin alterar la BD original.

P á g i n a 55 | 56
Elaborado por: Revisado por: Aprobado por:
*Usos más comunes:

➢ Promedio Ponderado
➢ Margen Bruto
➢ Comisiones
➢ Porcentajes de una columna

*Importante: Por las características del CC no se puede utilizar para calcular, entre otras
cosas, un monto (precio por cantidad), ya que el CC sumará todos los precios y los
multiplicará por todas las cantidades, y esto es incorrecto.

P á g i n a 56 | 56
Elaborado por: Revisado por: Aprobado por: