Guía de Clase Número 10: EXCEL
Referencias:
Una referencia en Excel es un identificador que señala una celda o un conjunto de celdas dentro de una hoja de
cálculo. Por ejemplo, si tienes una fórmula como =3*A3, eso significa que la celda está haciendo referencia a A3. Elegir el
tipo de referencia correcto ya sea relativa, absoluta o mixta te permite reutilizar las mismas fórmulas en diferentes celdas
sin necesidad de modificarlas manualmente. Aprovechar estas opciones que ofrece Excel es clave para optimizar tu trabajo
y hacerlo más eficiente.
Referencias relativas:
Son aquellas que indican la posición de una celda en relación a la celda en la que se está utilizando la referencia. Por
ejemplo, si en la celda B6 haces referencia a A4, esto se interpreta como una celda que se encuentra dos filas por encima y
una columna a la izquierda de B6. Por lo tanto, al copiar esta referencia a C7, cambiará automáticamente a B5, ya que sigue
manteniendo la misma relación de "dos filas por encima y una columna a la izquierda". Esta característica es muy útil para
realizar cálculos dinámicos sin tener que ajustar manualmente las referencias.
Fórmula:
=A1
Ejemplo Práctico:
Imagina que tienes una tabla con los siguientes datos en la columna A Y B:
Producto Precio
Manzanas 2
Plátanos 1.5
Naranjas 3
Supongamos que deseas calcular el total para cada producto, multiplicando el precio por una cantidad vendida que
se encuentra en la columna C:
Producto Precio Cantidad Total
Manzanas 2 10
Plátanos 1.5 20
Naranjas 3 15
Para calcular el total en la celda D2 (para Manzanas), escribirías la fórmula y arrastre hacia abajo:
=B2*C2
Referencias absolutas:
Son aquellas que señalan una celda por su posición fija dentro de la hoja de cálculo. Por ejemplo, si en la celda B6
utilizas la referencia $A$4, esto se interpreta como una referencia directa a la celda A4, sin importar dónde se copie la
fórmula. Así, al replicar esta referencia en C7, seguirá apareciendo como $A$4. Esta característica es especialmente útil
cuando necesitas mantener constante una celda específica en tus cálculos, independientemente de dónde se utilice la
fórmula.
Fórmula:
=$A$1
Ejemplo Práctico:
Imagina que tienes una tabla con los siguientes datos:
Producto Precio Descuento
Manzanas 2 0.1
Plátanos 1.5 0.1
Naranjas 3 0.1
Supongamos que deseas calcular el precio final después de aplicar un descuento a cada producto. Para ello,
puedes utilizar la siguiente fórmula en la celda D2 para calcular el precio final:
=B2*(1-$C$2)
Referencias mixtas:
Permiten combinar la especificación absoluta y relativa en una misma referencia. Esto significa que puedes fijar la
columna de forma absoluta y dejar la fila como relativa, como en $A4, o viceversa, fijando la fila y manteniendo la columna
como relativa, como en A$4. Estas referencias mixtas son útiles cuando deseas controlar el comportamiento de una parte
de la referencia mientras dejas que otra parte se ajuste automáticamente al copiar la fórmula.
Fórmula:
=$A$1 * B2
Ejemplo Práctico:
Imagina que tienes una tabla donde calculas el precio total de diferentes productos, teniendo en cuenta un tipo de
cambio constante. La tabla podría ser algo así:
Producto Precio Tipo de Cambio
Manzanas 2 1.2
Plátanos 1.5
Naranjas 3
Supongamos que el tipo de cambio está en la celda C1 y deseas calcular el precio total en dólares para cada
producto. Para ello, puedes utilizar la siguiente fórmula en la celda D2 para calcular el precio total:
=B2*$C$1
Referencias a celdas de otras hojas:
Para hacer referencia a una celda ubicada en otra hoja de cálculo, es necesario preceder la referencia de la celda
con el nombre de la hoja, seguido de un signo de admiración (!). Si tanto la celda que se está referenciando como la que
contiene la referencia están en el mismo libro de trabajo, solo necesitas escribir el nombre de la hoja. Si el nombre de la
hoja incluye espacios en blanco, deberás encerrarlo entre comillas simples. Por ejemplo, si deseas hacer una referencia
absoluta a la celda F7 de la hoja Auxiliar desde una celda en la hoja Resultados, deberías escribir Auxiliar!$F$7.
Fórmula:
=Auxiliar!$F$7
Ejemplo Práctico:
Supongamos que tienes un libro de Excel con dos hojas: "Ventas" y "Resumen".
Hoja 1: Ventas
Producto Total Ventas
Manzanas 500
Plátanos 300
Naranjas 400
Hoja 2: Resumen
En la hoja "Resumen", quieres mostrar el total de ventas de cada producto. Para hacer referencia a los datos de la
hoja "Ventas", puedes usar la siguiente fórmula en la celda A2 de la hoja "Resumen":
=Ventas!B2
Aquí, `Ventas` es el nombre de la hoja y `B2` es la celda específica que estás queriendo referenciar. Esta fórmula
traerá el total de ventas de Manzanas (500) a la celda A2 en "Resumen".
Si deseas obtener los totales de Plátanos y Naranjas, puedes escribir en las celdas A3 y A4 de la hoja "Resumen":
- En A3:
=Ventas!B3 - En A4:
=Ventas!B4
Resultado en la Hoja Resumen:
Total Manzanas 500
Total Plátanos 300
Total Naranjas 400
Barra de nombres:
La barra de nombres es un elemento clave en Microsoft Excel, ubicada a la izquierda de la barra de fórmulas. Su
función principal es mostrar el nombre o la referencia de la celda o rango seleccionado, lo que mejora la eficiencia al
trabajar con datos.
Una de sus características más útiles es la navegación rápida: al escribir una referencia
(como "A1") o un nombre definido, puedes ir directamente a esa celda sin buscarla manualmente. Además, permite
asignar nombres personalizados a rangos, lo que hace que las fórmulas sean más claras y comprensibles. Por ejemplo,
en lugar de usar "B2:B10", puedes referirte a "VentasQ1", facilitando así el manejo de tus cálculos.
Para deshacer o eliminar los nombres asignados a los rangos de celdas en Excel, puedes seguir estos pasos:
1-Abrir el Administrador de Nombres: - Ve a la pestaña "Fórmulas" en la cinta de opciones. - Haz clic en
"Administrador de nombres". Esto abrirá una ventana que muestra todos los nombres definidos en tu hoja de cálculo.
2-Seleccionar el nombre a eliminar: - En la lista del Administrador de nombres, busca el nombre que deseas eliminar. Haz
clic sobre él para seleccionarlo.
3-Eliminar el nombre: - Una vez seleccionado, haz clic en el botón "Eliminar" en la parte derecha de la ventana. - Se te
pedirá confirmar la eliminación; haz clic en "Aceptar" para confirmar.
4-Cerrar el Administrador de Nombres: - Después de eliminar los nombres que desees, puedes cerrar la ventana del
Administrador de nombres.
Ejercicio 1: Crear un rango nombrado
1-Abre una nueva hoja de Excel, 2-En la columna A, ingresa los siguientes datos del 1 al 5 (en las celdas A1 a A5): 1,
2, 3, 4, 5.
3-Selecciona las celdas A1:A5 y nómbralas como "Numeros". 4-En otra celda, escribe la fórmula `=SUMA(Numeros)`
y verifica que te dé el resultado correcto.
El formato condicional:
Es una herramienta poderosa que te permite aplicar diferentes formatos a las celdas de una hoja de cálculo según
ciertas condiciones o criterios. Esto te ayuda a resaltar información importante, identificar tendencias y hacer que tus datos
sean más visuales y fáciles de interpretar.
Resaltar reglas para celdas:
Permite aplicar formatos específicos a las celdas que cumplen con ciertos criterios, facilitando la visualización de
datos importantes. Por ejemplo, puedes resaltar automáticamente celdas que contengan valores por encima de un umbral
específico, o aquellas que cumplan con condiciones como ser fechas pasadas o estar vacías.
Esto no solo ayuda a identificar información clave rápidamente, sino que también mejora la presentación general
de tus datos. Al utilizar esta función, puedes hacer que tu hoja de cálculo sea más intuitiva y fácil de leer.
Vamos a verlo de forma práctica con este ejemplo, tenemos estos datos muy sencillos, una serie de meses y unas
ventas:
Si yo quisiera representar de forma gráfica y visual los datos de los meses que más ventas han tenido, y que además
esta representación sea dinámica, primero seleccionaremos los datos a los que queremos aplicarlo y posteriormente
pincharemos en el menú Inicio > y abriremos el menú del formato condicional.
En este caso desplegaremos el primer submenú reglas para resaltar celdas:
Y damos click en la opción de Es mayor que… a continuación se nos abre este pequeño asistente, donde
indicaremos el criterio para aplicar el formato en el caso que las celdas tengan un resultado superior, y además podremos
seleccionar que formato le aplicamos.
Como puedes observar vienen configurados formatos por defectos, como estos rellenos rojo, amarillo o verde con
el texto en el mismo color oscuro, ya que nos simularan el efecto semáforo para saber si nuestros datos van bien, estando
en verde, si los tenemos en observación al estar en amarillo, o si hay algún problema si se nos muestra en rojo.
Como puedes ver al darle a aceptar, se nos muestra automáticamente en el formato seleccionado todas las celdas que
superan ese importe, lógicamente de la selección previa que hubiéramos hecho:
Además, otra de las virtudes de este formato condicional es que podemos aplicar más de una regla a nuestros
datos, por ejemplo, al igual que hemos aplicado una regla para los valores que superan una cifra y así saber los meses con
más ventas, podemos también indicarle una regla para identificar los valores con las ventas más bajas.
Para ello volvemos a seleccionar todos los datos, y nos vamos a formato condicional > Reglas para resaltar celdas,
y es menor que, aquí indicaremos en este caso un valor de menor de 22.500 y el formato que aparece por defecto rojo.
El resultado sería el que te muestro a continuación:
Es herramienta útil para identificar datos en función de un criterio.
También vamos a poder utilizarlo para identificar los valores duplicados, para ello nos ponemos en la situación de
tener estos datos:
Podemos ver por encima que puede que tengamos algún valor duplicado entre los nombres, pero claro entre
los DNI´s ya nos perdemos a menos que vayamos línea por línea, bueno aun así sería un poco lento y complejo.
Para identificarlos de forma muy simple, vamos a seleccionar todo el rango de datos, y nos iremos a Formato
condicional > reglas para resaltar celdas > Valores duplicados.
Como puedes ver en este asistente no tenemos que decirle ningún criterio, ya que simplemente nos identifica los
valores duplicados, lo único que tendremos que hacer es seleccionar el formato que queremos aplicar.
Si por defecto dejamos el color rojo que nos aparece, nuestros datos tendrán el siguiente aspecto:
Reglas para valores superiores e inferiores:
Son funcionalidades muy útiles en hojas de cálculo que te permiten resaltar celdas en función de su contenido
numérico en relación con un umbral específico.
Tenemos unos datos muy similares a los anteriores, unos meses con unas ventas, y queremos identificar los tres
primeros meses con más ventas y los tres últimos de forma simultánea:
Seleccionamos los datos de la columna B y nos vamos a Inicio > Formato condicional > Reglas para valores superiores o
inferiores, y seleccionamos 10 superiores.
Se nos abrirá un asistente donde podemos subir o bajar el nº de meses Top, pudiendo dejarlo de momento en el listado 3
Top.
Aplicándole un formato tenemos nuestro ejercicio resuelto y de la misma forma realizaríamos el caso contrario, para los
tres primeros meses con las ventas más bajas.
Es importante aplicarle lógicamente un formato diferente para no llevarnos lugar a error.
Y el resultado sería el que ves a continuación:
Como puedes ver, una utilidad muy práctica para hacer que tus datos tengan sentido no solo en el valor sino a nivel visual.
Barra de datos y conjunto de iconos:
Barra de datos: Esta función permite visualizar rápidamente el valor de una celda mediante una barra gráfica que
se extiende dentro de ella. Cuanto mayor sea el valor de la celda, más larga será la barra. Esto es especialmente útil cuando
estás comparando un conjunto de números, ya que proporciona una representación visual clara del tamaño relativo de
cada valor, haciendo más fácil identificar tendencias o patrones.
Un ejemplo práctico muy sencillo, imaginemos que tenemos estos datos:
Como ves, unas líneas de negocio o unos tipos de servicio y sus ventas a lo largo de unos meses.
Bien si yo quisiera interpretar los datos, de una forma muy rápida, sin necesidad de tener que ir analizando celda por
celda o fila por fila, tan sencillo como seleccionar la primera fila, e ir a Inicio, > Formato condicional > Barra de datos, y
elegir el tipo y color que quiera, en este caso un verde degradado, y mira lo que sucede solo con pinchar aquí:
Como puedes ver lo que sucede es que coge todos los datos, al de mayor importe le aplica la barra completa y al
resto su proporción, de tal forma que puedo analizar cómo se comportan las ventas en cada mes respecto del total.
Esto lo realiza dentro de cada conjunto de datos, es decir que, si seleccionara todos los datos, las tres filas e hiciera
lo mismo, sucedería esto:
Bien en cuestión al formato, como puedes ver, simplemente podemos elegir entre un relleno solido o degradado y
diferentes colores, pero más que suficiente para tener un impacto visual en cualquier persona que vaya a revisar o analizar
nuestros datos.
Escala de calor: Esta función te permite aplicar un gradiente de colores a un rango de celdas, donde cada color
representa un valor diferente. Por ejemplo, puedes configurar una escala de colores que vaya del rojo al verde. En este
caso, los valores más bajos podrían estar en rojo y los valores más altos en verde.
Si limpiamos o borramos la anterior regla, y volvemos a seleccionar todo el conjunto de datos podemos seleccionar
otra opción, como es la escala de calor, y sucede lo siguiente:
Por último dentro de estas opciones tenemos los conjuntos de iconos, otra opción que dará autenticad vida a tus datos.
Conjunto de íconos: Similar a las barras de datos, esta opción utiliza íconos para representar diferentes rangos o
categorías de valores. Por ejemplo, puedes usar semáforos (rojo, amarillo, verde) para indicar el rendimiento: rojo para
bajo, amarillo para medio y verde para alto.
Para trabajar con ellos solo tienes que seleccionar los datos de nuevo e ir a Inicio > Formato condicional >
conjunto de iconos, y seleccionar los que más te gusten o apliquen a tus datos.
Te recomiendo utilizar los iconos de flecha y circulares que proporcionan un efecto semáforo muy intuitivo que
todo el mundo sabrá interpretar.
El efecto quedaría así:
Como puedes ver con estos iconos podemos saber fácilmente que meses van bien o van mal, en función de la flecha y el
color.
Puedes modificar el icono en cualquier momento, tan sencillo como seleccionar los datos y pinchar en el icono que quieres:
Como ves son muy versátiles y muy sencillos de aplicar. Además, puedes editar cualquier regla, no solo de estos últimos
datos, sino cualquier regla que hayas podido incorporar, vas a poder modificar los criterios o eliminarla.
Para ello dentro de nuestra selección de datos, donde tengamos la regla en cuestión, nos vamos de nuevo a Inicio >
Formato condicional > Administrar reglas que se encuentra al final del todo.
Nos aparecerá un asistente como este:
Como ves, además de poder crear una nueva regla y eliminarla, como nos muestra en la fila superior, vamos a poder
editarla.
Para ello si pinchamos en ella y pulsamos sobre el botón editar nos lleva a este último asistente:
En este panel, vamos a poder cambiar los criterios de porcentajes o valores respecto a cuándo aplica cada icono, por
ejemplo. Lógicamente este panel cambiará en función de que regla hayas aplicado, siendo más sencillo o con más
posibilidades de modificación en cada caso.
En este tipo de reglas me parece muy útil por ejemplo poder quitar iconos, es decir, si yo solo quisiera que me mostrara los
iconos verde y rojo, solo tendría que pinchar en el icono amarillo y dentro de él, indicarle que no hay icono de celda, y el
efecto sobre los datos sería el siguiente:
Como ves consigo una mayor visualización, en el caso de que solo me interese ver claramente los datos con la visualización
en verde y rojo.
Ejercicio 1: Resaltar Ventas Altas
Objetivo: Resaltar automáticamente las celdas de ventas que superen un umbral específico 1000, facilitando la
identificación de los vendedores con alto rendimiento.
Crea la tabla: En la celda A1 escribe "Vendedor" y en la celda B1 escribe "Ventas" Ingresa los nombres de algunos
vendedores en la columna A (A2, A3, A4...) y sus respectivas ventas en la columna B (B2, B3, B4...).
Por ejemplo:
Vendedor Ventas
Juan 1200
María 800
Pedro 1500
Ana 600
Ejercicio 2: Comparar Resultados con el Promedio
Objetivo: Resaltar las calificaciones que están por encima o por debajo del promedio para facilitar el análisis del
rendimiento académico.
Crea la tabla: Escribe en A1 "Estudiante" y en B1 "Calificación". Ingresa el nombre de varios estudiantes y sus respectivas
calificaciones.
Por ejemplo:
Estudiante Calificación
Luis 85
Carla 92
Jorge 78
Ana 88
Ejercicio 3: Control de Inventario
Objetivo: Resaltar productos cuyo inventario está por debajo del nivel mínimo establecido, ayudando a gestionar mejor los
stock.
Crea la tabla de inventario: Escribe en A1 "Producto", en B1 "Cantidad Actual" y en C1 "Nivel Mínimo". Llena la tabla
con algunos productos y sus cantidades actuales junto con su nivel mínimo.
Por ejemplo:
Producto Cantidad Nivel Mínimo
Actual
Producto A 50 30
Producto B 15 20
Producto C 25 10
