M1 AE3 Contenidos
M1 AE3 Contenidos
M1 AE3 Contenidos
3. Referencia en Excel
Las referencias en Excel son parte fundamental de la hoja de cálculo ya que a través
de ellas podemos identificar cada una de las celdas de un libro de trabajo y de esa
manera acceder al valor contenido en cualquiera de ellas.
Ya que todos los valores de una hoja de cálculo están almacenados en las celdas, es
de suma importancia para los usuarios de Excel conocer la manera en que deberán
acceder a dichos valores utilizando las referencias.
Todo usuario de Excel debe saber que todas las hojas están divididas en columnas y
filas. Esta división se hace evidente en la interfaz gráfica de Excel de la siguiente
manera:
Una referencia siempre será la dirección de una celda dentro de una hoja y siempre
estará formada por la columna y fila en donde se ubica la celda. Así que, cuando
hablamos de tipos de referencias en Excel estamos hablando sobre los diferentes tipos
de comportamiento que pueden tener las referencias al momento de ser copiadas a
otras celdas.
Es muy fácil copiar fórmulas en Excel, pero ¿qué sucede con las referencias de dicha
fórmula al momento de hacer la copia? Es ahí en donde su comportamiento
dependerá del tipo de referencia que se haya utilizado y analizaremos las alternativas
que tenemos en las próximas tres secciones.
En este ejemplo hemos copiado la fórmula hacia una celda de la misma columna, pero
dos filas por debajo y eso es lo que indicará a Excel el ajuste a realizar en las
referencias. Ya que no hubo cambio en la columna, ésta permanecerá igual en la nueva
fórmula, pero ya que copiamos una celda que está dos filas hacia abajo, Excel realizará
dicho ajuste sumando la misma cantidad de filas a las referencias. Ahora observa lo
que sucede si vuelvo a copiar la fórmula de la celda C1 y la pego en la celda F2.
En esta ocasión la nueva celda se encuentra una fila hacia abajo y tres columnas a la
derecha de la celda original y por lo tanto las referencias se adecúan a tal movimiento
añadiendo tres columnas y una fila a cada referencia de la fórmula copiada.
Las referencias relativas son muy útiles cuando necesitamos aplicar una misma
fórmula sobre un rango de datos. Por ejemplo, en la siguiente imagen puedes ver una
hoja donde las columnas A y B tienen valores numéricos y ahora necesito colocar la
multiplicación de ambos valores en la columna C. Para hacerlo rápidamente, se coloca
la fórmula =A1*B1 en la celda C1 y posteriormente se copia dicha fórmula hacia abajo:
En este caso la celda C3 devolverá el mismo resultado que la celda C1 porque ambas
fórmulas hacen referencia a las mismas celdas aún después de haberla copiado.
Tendremos el mismo efecto al copiar la fórmula de la celda C1 hacia la derecha:
Todas las fórmulas harán referencia a la celda F1 porque está establecida como
absoluta, pero Excel modificará correctamente las referencias a las celdas de la
columna B para obtener el resultado correcto.
Con esto hemos cubierto los dos tipos de referencias en Excel más utilizados. Si
quieres ver algunos ejemplos adicionales sobre estos dos tipos de referencias te
recomiendo consultar el siguiente video tutorial: Referencias relativas y absolutas en
Excel.
En los ejemplos anteriores sobre referencias absolutas utilizamos el símbolo “$” para
fijar tanto la columna como la fila. Sin embargo, es posible fijar sólo la columna o fijar
sólo la fila y a estas variaciones se les conoce como referencias mixtas.
Un consejo muy útil para hacer más eficiente el tiempo invertido al ingresar cualquier
referencia en una fórmula es el uso de la tecla F4 cuando nos encontramos en el modo
de edición. Cuando el cursor de edición se encuentra sobre una referencia y pulsamos
la tecla F4, Excel cambiará el tipo de referencia sin la necesidad de que ingresamos
manualmente el símbolo “$”. Hagamos el siguiente ejemplo para conocer el
funcionamiento de este atajo de teclado:
La tecla F4 solamente cambiará la referencia que está sobre el cursor de edición así
que, si quieres aplicar este cambio en varias referencias de una misma fórmula,
entonces deberás colocar el cursor sobre cada referencia que será cambiada y pulsar
F4 cada vez.
Hasta ahora hemos visto cómo hacer referencia a una sola celda, pero Excel nos
permite hacer referencia a un conjunto de celdas adyacentes utilizando el operador
de rango que está representado por los dos puntos (:) y que deberá colocarse entre
dos referencias de celdas. Sin embargo, esas dos referencias de celdas no son al azar,
sino que deben ser el extremo superior izquierdo y el extremo inferior derecho de las
celdas adyacentes a las que deseamos referirnos.
Considera el siguiente grupo de celdas adyacentes donde cada una de ellas tiene un
valor numérico. La referencia para este rango de celdas será B2:D4 y puedes ver que
dicho rango es reconocido por Excel al momento de hacer la suma.
Esto definitivamente es una gran ventaja ya que de lo contrario tendríamos que
indicar todas las referencias a las nueve celdas, pero con este método podemos
referirnos fácilmente a todo el rango de celdas. Solo debes recordar que la primera
referencia corresponde a la celda del extremo superior izquierdo y la segunda
referencia a la celda inferior derecha.
En este ejemplo ambas referencias son relativas, pero nada impide que utilicemos
referencias absolutas al crear una referencia a un rango, por ejemplo: $B$2:$D$4.
Todas las reglas que explique anteriormente para las referencias relativas, absolutas
y mixtas también aplicarán para las referencias a rangos de celdas.
En todos los ejemplos mostrados hasta este punto he utilizado referencias dentro de
la misma hoja pero en más de una ocasión nos veremos en la necesidad de hacer
referencia a una celda que se encuentra en una hoja diferente.
La única regla que debemos seguir es utilizar el nombre de la hoja donde se encuentra
la celda de nuestro interés e inmediatamente colocar el signo de exclamación (¡)
seguido de la referencia a la celda. De esta manera, si queremos referirnos a la celda
C5 de la Hoja2, debemos hacerlo de la siguiente manera:
Una práctica que es altamente recomendable en las referencias a celdas de otra hoja,
es rodear el nombre de la hoja con comillas simples (‘) ya que eso evitará cualquier
error en caso de que el nombre de la hoja tenga un espacio en blanco, por ejemplo:
Si la celda que necesitas acceder se encuentra en otro libro, entonces deberás indicar
el nombre del libro encerrado entre corchetes [] antes del nombre de la hoja de la
siguiente manera:
Las referencias 3D son útiles cuando necesitamos hacer operaciones con las mismas
celdas en diferentes hojas. Por ejemplo, si vamos a sumar la celda A1 de la Hoja1,
Hoja2 y Hoja3, normalmente utilizamos la siguiente fórmula:
A diferencia de esta nomenclatura, las referencias 3D nos permiten indicar las mismas
referencias de una manera abreviada:
=SUMA(Hoja1:Hoja3!A1)
Es muy importante mencionar que no es posible utilizar este tipo de referencias con
cualquier función de Excel. Las funciones matemáticas y estadísticas son las que
permitirán su uso pero no podrás utilizar este tipo de referencias en funciones de
búsqueda como BUSCARV o COINCIDIR.
Además de acceder una sola celda en varias hojas utilizando las Referencias 3D,
también puedes crear una referencia a un rango de celdas de la siguiente manera:
=SUMA(Hoja1:Hoja3!A1:C25)
Para que Excel utilice este tipo de referencia debes ir a Archivo > Opciones > Fórmulas
> Trabajando con fórmulas > Estilo de referencia F1C1.
Al seleccionar esta opción y aceptar los cambios, notarás de inmediato un cambio en
los encabezados de columna ya que dejarán de ser letras y se mostrarán como
números. Además podrás observar el estilo de referencia F1C1 en el Cuadro de
nombres y ya no estará permitido utilizar el estilo de referencia anterior.
La verdad es poco probable que alguna vez te veas forzado a utilizar el estilo de
referencia F1C1, pero el concepto de utilizar valores numéricos para referirse a las
columnas y filas es muy utilizado al programar en VBA ya que es mucho más fácil
referirse programáticamente a las columnas por su número que por su letra.
Si tienes hojas de cálculo en Excel que contienen una gran cantidad de fórmulas, antes
de pensar en editar cualquier valor será de gran utilidad conocer las celdas que están
relacionadas con dicha fórmula.
Excel provee de una herramienta que nos permite conocer fácil y gráficamente todas
las celdas involucradas en el cálculo de una fórmula. Considera el siguiente ejemplo
donde la fórmula hace la suma de las celdas B1 y B2:
Para rastrear las celdas involucradas en el cálculo debes elegir el comando Rastrear
precedentes que se encuentra en la ficha Fórmulas dentro del grupo Auditoría de
fórmulas.
Excel colocará unas flechas desde las celdas que son utilizadas en la fórmula
haciéndonos saber gráficamente cuáles son las celdas precedentes.
Excel puede ir un poco más lejos y también ayudarnos a saber si el valor de la celda
actual es utilizado en el cálculo de alguna otra celda. Para saberlo es suficiente con
hacer clic en el comando Rastrear dependientes.
Si Excel encuentra alguna celda cuyo valor dependa de la celda actual, entonces
trazará una flecha que partirá de la celda actual hacia dicha celda.
Estos nombres se pueden utilizar dentro de una fórmula para ayudar en la compresión
de la misma posteriormente. Para asignar un nombre a una celda sigue los siguientes
pasos. Selecciona la celda o rango a la que asignarás un nombre y haz clic en el
cuadro Nombre que se encuentra en el extremo izquierdo de la barra de fórmulas:
Como ejemplo final utilizaremos el nombre que acabamos de crear dentro de una
fórmula para que observes cómo Excel interpreta correctamente el nuevo nombre del
rango, observa la barra de fórmulas:
Ahora ya sabes que puedes nombrar tanto celdas como rangos y utilizar ese nombre
dentro de tus fórmulas para facilitar tu trabajo.
3.13. Manipulación y búsqueda de Datos
Una de las acciones más comunes en Excel es la de buscar un valor dentro de una
columna, razón por la cual la función BUSCARV es una de las funciones más utilizadas,
sin embargo no es la única alternativa que tenemos para buscar en Excel.
La función BUSCARH en Excel busca un valor dentro de una fila y devuelve el valor que
ha sido encontrado o un error #N/A en caso de no haberlo encontrado. Esta función
es similar, en cierto sentido, a la función BUSCARV.
En la siguiente tabla tengo la información de los artículos que ha vendido cada uno de
los vendedores en los últimos meses. Como título de columnas están los nombres de
los vendedores y como filas los meses.
Ahora quiero saber los productos vendidos en el mes de Febrero por Paco y para ello
puedo utilizar la función BUSCARH de la siguiente manera:
El tercer argumento es el número de fila que deseo que la función BUSCARH regrese
como resultado. Ya que la fila con los nombres es la fila uno, entonces la fila para el
mes de febrero es la fila número 3.
Finalmente coloca el valor FALSO en el cuarto argumento para indicar que deseo una
coincidencia exacta al buscar a Paco. El resultado de esta fórmula es el siguiente:
Si quisiera busca la información del mismo mes para Luis, entonces la función
cambiará de argumentos de la siguiente manera:
Si por el contrario quiero conocer los productos vendidos por Hugo en el mes de Abril,
entonces la función sería la siguiente:
De esta manera, la función BUSCARH nos permite hacer una búsqueda en una fila
(búsqueda horizontal) y encontrar fácilmente el valor requerido.
Devuelve la posición relativa de un elemento en una matriz, que coincide con un valor
dado en un orden especificado, tiene tres argumentos y solo dos de ellos son
obligatorios.
El primer argumento de la función COINCIDIR es el valor a buscar. En el
ejemplo anterior será el valor de la celda B1
Como resultado obtenemos el valor 3 que nos indica que “Diana” es el tercer nombre
dentro del rango D2:D6. El hecho de obtener esta posición es de mucha ayuda porque
si queremos conocer el teléfono de “Diana” debemos obtener el valor en la posición
3 bajo la columna Teléfono y eso es precisamente lo que haremos en el siguiente paso.
En Excel podemos cambiar los datos que se encuentran en una fila de manera que
aparezcan como títulos de columna y viceversa. Esta acción la logramos a través del
comando Transponer que se encuentra dentro del menú del botón Pegar.
Para hacer este cambio debemos seguir los siguientes pasos. Selecciona los datos que
vas a transponer, ya sea que se encuentren en una fila o en una columna.
Copiamos los datos al portapapeles ya sea pulsando el botón Copiar u oprimiendo la
combinación de teclas CTRL + C. Enseguida debemos seleccionar la celda en donde se
comenzarán a pegar las celdas transpuestas y finalmente pulsar el botón Pegar para
elegir la opción Transponer.
En cuanto se elige esta opción Excel hace una copia de los datos y los distribuye
adecuadamente en columnas o filas según sea el caso. En el siguiente ejemplo
pasamos de una lista de valores en una fila a valores en diferentes columnas:
De la misma manera podemos transponer valores que se encuentran en diferentes
columnas a una sola fila.
Seguramente alguna vez has intentado concatenar una fecha a un texto solo para
darte cuenta que el resultado no es lo que esperabas ya que Excel toma en cuenta el
valor numérico de la fecha y terminamos con un número concatenado en el texto.
Para dejar en claro a lo que me refiero realiza la siguiente prueba. En la celda A1 coloca
la fecha 12/05/2014 y en la celda B1 ingresa el texto “La fecha de hoy es”. Ahora en la
celda C1 coloca la fórmula =CONCATENAR(B1, ” “, A1) y observa el resultado:
=TEXTO(A1, "dd/mm/aaaa")
[1] Excel
https://exceltotal.com/