Informática Aplicada · M11-19-10 · Unidad 1
Seguimos con la planilla de Atención pacientes en Julio que empezaste en la Clase 1. Ahora vas a aprender a copiar una fórmula a muchas celdas de una sola vez, por qué a veces esa copia "rompe" el cálculo, y cómo evitarlo con referencias absolutas. Después vamos a resumir toda la tabla con las funciones SUMA, PROMEDIO, MÁXIMO y MÍNIMO.
Repaso rápido
En la Clase 1 escribiste una fórmula distinta en cada fila
(F4, F5, F6…). Funciona,
pero no escala: si tu tabla tuviera cien filas, escribir cien
fórmulas a mano sería lento y fácil de arruinar. Excel permite
escribir la fórmula una sola vez y copiarla al resto de las
celdas.
La herramienta
Es el pequeño cuadrado que aparece en la esquina inferior derecha de la celda seleccionada. Al hacer clic y arrastrar sobre él, Excel copia el contenido —o la fórmula— de esa celda a todas las celdas por donde pasás.
Lo importante
Cuando copiás una celda con una fórmula, Excel no repite el resultado que ves: vuelve a calcular la fórmula en la celda de destino. Y ahí entra en juego cómo esté escrita cada referencia dentro de esa fórmula.
Más de un camino
El controlador de relleno es la más visual, pero no es la única manera de copiar una fórmula en Excel. Conviene conocer las cuatro, porque según la situación una te va a resultar más cómoda que otra.
Clic sostenido sobre el cuadradito de la esquina y arrastrás hasta donde necesites.
Copia automáticamente hacia abajo hasta la última fila que tenga datos en la columna de al lado.
Seleccioná la celda, Ctrl+C, seleccioná el destino y Ctrl+V. Ideal para copiar a celdas salteadas.
Seleccioná la celda con la fórmula junto con el rango de destino y usá Ctrl+D (abajo) o Ctrl+R (derecha).
Copiar y pegar con Ctrl+V también trae el formato
de la celda de origen (color, bordes, tamaño de fuente). Si
solo querés la fórmula sin arrastrar el formato, usá "Pegado
especial" (Ctrl+Alt+V) y elegí "Fórmulas".
El concepto central de la clase
Volvamos a la planilla de Atención pacientes en
Julio, ya con una columna nueva —"Fecha de
estimación"— insertada entre Ministro y Pacientes anteriores.
Vas a agregarla vos mismo/a en el ejercicio de más abajo; acá
alcanza con saber que corrió una columna hacia la derecha todo
lo que venía después, así que Pacientes anteriores
ahora es la columna D (antes era C) e
Incremento estimado es la columna G (antes
era F). Vamos a guardar en la celda G2 el
porcentaje de incremento que se aplica a todas las provincias.
El problema aparece justo al intentar copiar la fórmula que lo
usa.
Seleccioná G2, aplicale formato "Porcentaje" y
escribí 8. Esa celda funciona como un parámetro
único: todas las provincias van a usar el mismo porcentaje
para calcular su incremento.
En G4 escribís
=ENTERO(D4*G2): la cantidad de pacientes
anteriores de Buenos Aires (D4) multiplicada por
el porcentaje (G2), redondeada con
ENTERO porque son personas.
Seleccionás G4 y arrastrás el controlador de
relleno hasta G9, para completar el incremento de
las seis provincias de una sola vez.
¿Parece que algo falló?
Al copiar G4 una fila hacia abajo, Excel asume que
todas las referencias de la fórmula se tienen
que mover una fila. Eso está bien para D4 —que sí
queremos que pase a ser D5—, pero es un problema
para G2.
G3 no contiene un número: contiene el texto
"INCREMENTO ESTIMADO", el encabezado de la columna. Excel
intenta multiplicar un número por un texto y devuelve el error
#¡VALOR!. El problema no está en la cuenta, está en
que la referencia a G2 se movió cuando no
queríamos que se moviera.
| Tipo de referencia | Cómo se escribe | Qué pasa al copiar |
|---|---|---|
| Relativa | D4 |
Se ajusta sola: D4 → D5 → D6… |
| Absoluta | $G$2 |
Queda fija: $G$2 → $G$2 → $G$2… |
$G fija la columna G, no importa a qué columna copies$2 fija la fila 2, no importa a qué fila copies$ juntos fijan la celda entera
Atajo: con el cursor sobre la referencia
dentro de la fórmula, la tecla F4 agrega o quita
los signos $ sin que tengas que escribirlos a
mano.
La solución
La fórmula correcta combina una referencia relativa —para que cada fila use su propia cantidad de pacientes— con una referencia absoluta —para que todas usen siempre el mismo porcentaje.
Al copiar =ENTERO(D4*$G$2) hacia abajo, la parte
D4 se convierte en D5, D6…
porque es relativa. La parte $G$2 queda exactamente
igual en las seis filas porque es absoluta. Y como la fórmula lee
el valor de G2 en vez de tener el porcentaje escrito
a mano, si después cambiás ese número —por ejemplo de 8% a 9%— las
seis filas se recalculan solas, sin tocar ninguna fórmula.
Un paso más allá (opcional)
Relativa y absoluta no son las dos únicas formas de escribir una referencia. Existe un tercer tipo, la referencia mixta, que fija solamente la fila o solamente la columna. Sirve para cuando necesitás copiar una misma fórmula en las dos direcciones a la vez: hacia abajo y hacia la derecha.
| Tipo | Se escribe | Al copiar hacia abajo | Al copiar hacia la derecha |
|---|---|---|---|
| Relativa | D4 |
Cambia la fila: D5, D6… |
Cambia la columna: E4, F4… |
| Absoluta | $D$4 |
No cambia nada | No cambia nada |
| Mixta, fila fija | D$4 |
La fila queda en 4 |
Cambia la columna: E$4, F$4… |
| Mixta, columna fija | $D4 |
Cambia la fila: $D5, $D6… |
La columna queda en D |
El signo $ siempre fija lo que tiene inmediatamente
a la derecha. En D$4, el $ está pegado
al 4: fija la fila. En $D4, está
pegado a la D: fija la columna. Y con el atajo
F4 podés ir ciclando entre los cuatro estados sin
escribir el signo a mano: $D$4 →
D$4 → $D4 → D4 →
$D$4 otra vez.
Reto opcional
Imaginá que en vez de un solo porcentaje en G2
querés comparar tres escenarios —3%, 5% y 8%— para cada
provincia, uno al lado del otro. Escribís los porcentajes en
la fila 2, en las celdas J2, K2 y
L2, y armás una sola fórmula en J4
que después puedas copiar hacia abajo y hacia la derecha sin
reescribirla.
$D4 fija la columna D (siempre lee Pacientes
anteriores de esa fila) pero deja libre la fila, así que al
copiar hacia abajo pasa a leer $D5,
$D6… J$2 fija la fila 2 (siempre lee
el porcentaje del encabezado) pero deja libre la columna, así
que al copiar hacia la derecha pasa a leer K$2,
L$2… Con una sola fórmula, copiada una vez hacia
abajo y una vez hacia la derecha, completás las 18 celdas del
cuadro comparativo.
Para cuando algo sale mal
Ya viste el error #¡VALOR! al copiar mal una
referencia. Excel tiene un puñado de errores típicos que vas a
encontrar tarde o temprano; reconocerlos rápido te ahorra
minutos de revisar toda la fórmula letra por letra.
| Error | Causa más común | Cómo solucionarlo |
|---|---|---|
#¡VALOR! |
La fórmula intenta operar con un texto en vez de un número, como pasó con G3 |
Revisá que las celdas referenciadas tengan el tipo de dato correcto |
#REF! |
La fórmula apunta a una celda que ya no existe, porque se borró la fila o columna | Volvé a escribir la referencia apuntando a la celda correcta |
#DIV/0! |
La fórmula divide por una celda vacía o con el valor 0 | Revisá que el divisor tenga un valor válido antes de dividir |
#NOMBRE? |
Excel no reconoce el nombre de la función, por ejemplo por un error de tipeo | Revisá que el nombre de la función esté bien escrito, en el idioma de tu Excel |
##### |
La columna es demasiado angosta para mostrar el contenido de la celda | Ensanchá la columna arrastrando el borde de su encabezado |
El error no siempre está en la celda donde lo ves: a veces esa celda solo heredó el problema de otra celda que referencia. Hacé clic en la celda con error y mirá la barra de fórmulas antes de tocar nada. Y si el error apareció justo después de copiar una fórmula, lo primero que hay que sospechar es de una referencia que debería haber sido absoluta.
Material multimedia requerido
Este video retoma la planilla de la clase anterior y muestra en
vivo el problema de copiar la fórmula sin fijar el porcentaje,
y cómo se soluciona con $G$2. Miralo con tu
planilla abierta al lado para ir reproduciendo cada paso.
Podés abrir dos ventanas al mismo tiempo: una con el video y otra con tu archivo de Excel. Si te surge una duda, planteála en el Foro de la Unidad 1.
Práctica
Abrí la planilla del Ejercicio 1 que armaste en la Clase 1 y seguí estos pasos en orden. Cada uno se apoya en el anterior.
Insertá una fila con los datos de Mendoza: Ministro Susana Benítez, Pacientes anteriores 160, Ingresos 28, Egresos 15. Calculá el Incremento estimado y los Pacientes estimados con las mismas fórmulas de la clase anterior.
Entre "Ministro" y "Pacientes anteriores", insertá una columna con el título "Fecha de estimación". Al insertarla, todas las columnas de la derecha se corren un lugar: lo que era la columna C ahora es D, y así sucesivamente.
Borrá el contenido de toda la columna "Incremento estimado". La vamos a volver a calcular desde cero, esta vez con un porcentaje único guardado en una celda aparte.
Seleccioná G2, aplicale formato "Porcentaje" y
escribí 8. Después, en G4, armá la
fórmula ya con la referencia absoluta.
Fórmula en G4: =ENTERO(D4*$G$2)
Confirmá con Enter, volvé a seleccionar
G4 y arrastrá el controlador de relleno hasta
G9 para completar las seis provincias de una
sola vez.
Con la columna ya calculada, modificá el valor de
G2 de 8% a 9%.
Comprobación: ningún #¡VALOR!,
y las columnas Incremento estimado y Pacientes estimados se
actualizan solas.
Si ves que algo no cambia, revisá que la fórmula tenga los
dos signos $ delante de la G y del 2.
Resultado esperado
Así debería quedar tu planilla después de insertar la fila y la columna, y de aplicar la fórmula con referencia absoluta.
| Provincia | Ministro | Fecha de estimación | Pacientes anteriores | Ingresos | Egresos | Incremento estimado | Pacientes estimados |
|---|---|---|---|---|---|---|---|
| Buenos Aires | Jorge Gómez | 14/07/2020 | 345 | 48 | 39 | 31 | 385 |
| C.A.B.A. | Carlos Mochales | 12/07/2020 | 210 | 32 | 35 | 18 | 225 |
| Mendoza | Susana Benítez | 01/07/2020 | 160 | 28 | 15 | 14 | 187 |
| Córdoba | Isabel Resnik | 21/07/2020 | 170 | 21 | 17 | 15 | 189 |
| Santa Fe | Marcelo Díaz | 09/07/2020 | 133 | 24 | 14 | 11 | 154 |
| Entre Ríos | Beatriz Romero | 19/09/2020 | 98 | 17 | 22 | 8 | 101 |
"Pacientes estimados" sigue calculándose igual que en la Clase
1: Pacientes anteriores + Ingresos + Incremento estimado −
Egresos. Con la columna nueva insertada, en H4
queda como =D4+E4+G4-F4.
Segunda parte de la clase
Una función es una fórmula predefinida con nombre propio, igual
que ENTERO de la clase anterior. Se escribe
=NOMBRE(rango), donde el rango es el grupo de
celdas sobre el que actúa, separado con dos puntos:
celda1:celdaN.
| Función | Resultado |
|---|---|
=SUMA(celda1:celdaN) |
Calcula la sumatoria del rango de celdas especificado. |
=PROMEDIO(celda1:celdaN) |
Calcula el promedio del rango de celdas especificado. |
=MAX(celda1:celdaN) |
Obtiene el valor máximo del rango de celdas especificado. |
=MIN(celda1:celdaN) |
Obtiene el valor mínimo del rango de celdas especificado. |
Para sumar los "Pacientes anteriores" de las seis provincias,
el rango son las celdas D4 a D9.
Fórmula: =SUMA(D4:D9)
D4+D5+D6…
El controlador de relleno también funciona hacia la derecha.
Si arrastrás D13 hasta H13, la
referencia relativa se ajusta por columna en vez de por fila:
SUMA(D4:D9) se convierte en
SUMA(E4:E9), después en
SUMA(F4:F9), y así hasta la última columna. Evitás
escribir la misma función cinco veces.
Un poco más de profundidad
Como cualquier fórmula, arranca con =: le avisa
a Excel que lo que sigue hay que calcularlo, no mostrarlo
como texto.
SUMA le dice a Excel qué operación aplicar. Cada
función tiene un nombre único: PROMEDIO,
MAX, MIN, ENTERO…
(D4:D9) es el dato sobre el que actúa la
función. Puede ser un rango, una celda sola o incluso otra
función.
Atajo en la cinta
En la pestaña "Inicio" o "Fórmulas" hay un botón con el
símbolo Σ. Al hacer clic con una celda vacía seleccionada,
Excel escribe =SUMA( y adivina el rango de
números que tiene al lado, listo para confirmar con
Enter.
Cuando el rango no es continuo
Si necesitás sumar dos bloques de celdas que no están
pegados, separalos con una coma dentro del mismo argumento.
También podés armar ese rango a mano manteniendo apretado
Ctrl mientras hacés clic en cada celda.
Ejemplo:
=SUMA(D4:D9,F4:F9)
Una función puede ir adentro de otra: se llama "anidar"
funciones. Por ejemplo, si querés el promedio de Pacientes
estimados redondeado hacia abajo, podés combinar
PROMEDIO con la función ENTERO que ya
usaste en la Clase 1: =ENTERO(PROMEDIO(H4:H9)).
Excel resuelve primero la función de más adentro
(PROMEDIO) y usa ese resultado como argumento de
la de afuera (ENTERO).
Material multimedia requerido
Este segundo video muestra cómo agregar la fila de totales a la planilla del Ejercicio 1 y aplicar SUMA, PROMEDIO, MÁXIMO y MÍNIMO. Volvé a abrir tu planilla y seguí el paso a paso.
Si te quedaron dudas sobre referencias absolutas de la primera parte, no hace falta resolverlas antes de seguir: las funciones de esta parte no dependen de eso.
Práctica
Seguí trabajando sobre la misma planilla del ejercicio anterior, ya con las ocho columnas y las seis provincias cargadas.
En H10, escribí =SUMA(H4:H9) para
obtener el total de Pacientes estimados de las seis
provincias.
En las celdas A13 a A16, escribí
en cursiva: TOTAL, PROMEDIO, MÁXIMO y MÍNIMO. Agregá bordes a
ese bloque para diferenciarlo del resto de la tabla.
En D13, escribí =SUMA(D4:D9) y
copiala hacia la derecha, hasta H13, para
obtener el total de Ingresos, Egresos, Incremento estimado y
Pacientes estimados.
Repetí la misma lógica en las filas 14, 15 y 16, cambiando solo el nombre de la función.
D14: =PROMEDIO(D4:D9)
D15: =MAX(D4:D9)
D16: =MIN(D4:D9)
Copiá cada una hacia la derecha, hasta la columna
H, igual que hiciste con la fila de TOTAL.
El promedio de Pacientes estimados debería quedar en
206,83, con el máximo en Buenos Aires
(385) y el mínimo en Entre Ríos
(101).
Si algún resultado no coincide, revisá que el rango de la función cubra las seis filas de datos, de la 4 a la 9.
Resultado esperado
| Pacientes anteriores | Ingresos | Egresos | Incremento estimado | Pacientes estimados | |
|---|---|---|---|---|---|
| TOTAL | 1.116 | 170 | 142 | 97 | 1.241 |
| PROMEDIO | 186 | 28,33 | 23,67 | 16,17 | 206,83 |
| MÁXIMO | 345 | 48 | 39 | 31 | 385 |
| MÍNIMO | 98 | 17 | 14 | 8 | 101 |
Guardá tu planilla: en la próxima clase de la unidad vas a seguir trabajando sobre esta misma tabla para armar gráficos.
Para llevártelo de acá
Referencias absolutas y funciones de resumen no son solo para planillas de la facultad: aparecen en cualquier tabla que manejes en tu trabajo o en tu casa. Estos son algunos ejemplos de dónde te van a servir.
| Situación | Qué guardarías en una celda aparte (como G2) | Funciones que usarías |
|---|---|---|
| Gastos del mes | El porcentaje que querés apartar como ahorro | SUMA para el total, PROMEDIO para el gasto diario |
| Notas de un curso | La nota mínima para aprobar | PROMEDIO, MAX y MIN para ver el mejor y el peor resultado |
| Stock de un local | El precio unitario o el porcentaje de reposición | SUMA para el stock total, MIN para saber qué producto está por agotarse |
| Turnos de un consultorio | La duración estándar de cada turno | SUMA de las horas ocupadas del día |
En los cuatro casos hay algo en común: un valor que se repite
en todos los cálculos —el porcentaje de ahorro, la nota
mínima, el precio— conviene guardarlo en una única celda y
referenciarlo con $, en vez de escribirlo adentro
de cada fórmula. Así, cambiarlo una sola vez actualiza todos
los resultados, exactamente como viste hoy con el incremento
de pacientes.
Cierre de la clase
Terminamos las dos primeras clases de la unidad. Antes de seguir, hacé una pausa y repasá mentalmente las fórmulas y funciones que viste hasta acá.
Pensá qué planilla podrías diseñar para resolver alguna situación de tu trabajo o tu vida diaria. Pensalo en términos de utilidad: ¿qué problema te resolvería tenerla armada?
¿Qué fórmulas aplicarías en esa planilla? ¿Necesitarías
referencias absolutas, como $G$2, o te
alcanzaría con las relativas? ¿Qué funciones de las vistas —
SUMA, PROMEDIO, MÁXIMO, MÍNIMO— serían necesarias incluir?
Compartí tus reflexiones en el Foro de la Unidad 1 para intercambiar con tus compañeros y tu profesor/a tutor/a.