sábado, 15 de octubre de 2011

Referencias relativas, absolutas y mixtas en Excel


Referencias relativas

Una referencia relativa a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", se ajusta automáticamente para hacer referencia a otras celdas.

Son las referencias más utilizadas y las que, en la mayoría de las veces, se obtienen por defecto en Excel al hacer click en una celda para hacer referencia a ella. Son del tipo  A1.

Por ejemplo, si tenemos la expresión = A1*2 en la celda B1 y utilizamos "Autorrellenar" hacia abajo, las fórmulas que se obtienen van a justando el número de forma que en la celda B2 tendremos la fórmula =A2*2, en la celda B3 tendremos la fórmula =A3*2 y así sucesivamente.

Podemos ver en la siguiente imagen como se ajustan las fórmulas a medida que "arrastramos" con "Autorrellenar":


El resultado sería:


Referencias absolutas

Una referencia absoluta a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", NO se ajusta y queda bloqueada haciendo referencia siempre a la misma celda.

Para hacer una referencia absoluta a una celda, se deben introducir los símbolos del dólar $ antes de la letra y antes del número de una referencia normal del tipo A1. Es decir, una referencia absoluta a la celda A1, sería: $A$1

Con ello, conseguimos que al copiar y pegar o bien al utilizar "Autorrellenar" siempre mantenemos la referncia a la misma celda:


El resultado es:


Referencias mixtas

Una vez aprendidos los conceptos de "referencia relativa" y "referencia absoluta", es fácil entender el concepto de referencia mixta. Una referencia mixta a una celda o rango es aquella que, al copiar la celda donde está escrita y pegarla en otra ubicación o al utilizar "Autorrellenar", ajusta sólo la letra o sólo el número de la referencia, quedando bloqueado sólo el número o sólo la letra respectivamente.

Por lo tanto, si queremos bloquear la letra, debemos colocar el símbolo del dólar "$" antes de la letra y si queremos bloquear el número, debemos colocar el símbolo del dólar "$" antes del número

Las referencias mixtas son útiles en multitud de ocasiones, especialmente cuando tenemos que utilizar "Autorrellenar" en una matriz de celdas (varias celdas x varias celdas), puesto que nos permite "arrastrar" una fórmula introducida en una esquina, hasta la celda opuesta en la matriz, para operar con los encabezados de columnas y filas rápidamente y de una sóla vez. Podemos ver en la siguiente imagen una referencia mixta preparada para "arrastrar":


Teniendo el siguiente efecto, una vez "arrastrada" la fórmula de la celda C3 hasta la celda E5:



Truco: cambio rápido del tipo de referencia con la tecla F4

Estando el cursor dentro de la fórmula, podemos convertir rápidamente el tipo de referencia mediante la tecla F4. Por ejemplo, la primera vez que presionamos dicha tecla de función, la referencia A1 se convierte en una referencia absoluta (por ejemplo $A$1), la segunda vez se convierte en una referencia mixta que bloquea sólo el número de fila  (por ejemplo A$1), la tercera vez se convierte en una referencia mixta que bloquea sólo la letra de la columna  (por ejemplo $A1), la cuarta vez se convierte de nuevo en referencia relativa.

  José Manuel Pomares Medrano

39 comentarios:

  1. Una explicación detallada, concreta y concisa.
    Con pocas palabras se puede entender toda la explicación dada a las celdas relativas, absolutas y mixtas. De todas las formas, hay que tener mucha
    lógica para un buen manejo de Excel.

    ResponderEliminar
    Respuestas
    1. Muchas gracias. Me alegro que le parezca útil.

      Eliminar
    2. Muy Bueno el Blog
      Me sirvio de Mucho
      Ojala sigan Haciendo textos Tan interesantes E importantes Como Estos
      :D

      Eliminar
  2. Esta bueno y sencillo el post, con ejemplo incluido para que quede mas claro.

    Gracias.

    ResponderEliminar
  3. interesante el comentario,muy practico. Gracias

    ResponderEliminar
  4. ¿Sabrías responder a esta pregunta?

    En Excel, las referencias a otras celdas son por defecto absolutas, a no ser que se haga uso del carácter $

    En Excel, las referencias a otras celdas son por defecto relativas, a no ser que se haga uso del carácter $

    En Excel, las referencias a otras celdas son por defecto mixtas, a no ser que se haga uso del carácter &

    En Excel, las referencias a otras celdas son por defecto imperfectas, a no ser que se haga uso del carácter &

    ResponderEliminar
  5. necesito que me ayuden a convertir una formula absoluta de celdas convinadas son 2 celdas convinadas en forma orizontal ejm !$B$43:$C$43 luego las otra celda convinadas, pero necesito que esta sea relativa siempre en celdas convinadas, puede aportar respuestas a magaval_01@hotmail.es, de ante mano gracias..

    ResponderEliminar
  6. Hola, ¿es posible hacer una fórmula que detecte si en otra fórmula se hace uso de referencias absolutas?

    ResponderEliminar
    Respuestas
    1. Para detectar si una fórmula es absoluta, puedes hacerlo mediante el comando "Buscar" de la ficha INICIO, puesto que puede buscar dentro de las fórmulas cualquier carácter (en este caso, el signo $).
      También se podría hacer programando un procedimiento en Visual Basic, pero es más complicado.

      Eliminar
  7. Este comentario ha sido eliminado por un administrador del blog.

    ResponderEliminar
    Respuestas
    1. Este comentario ha sido eliminado por un administrador del blog.

      Eliminar
  8. MUY BUENO ME SIRVIO MUCHO SE LES AGRADECE SUERTE

    ResponderEliminar
  9. que buen post, super explicado y sencillo facil de entender!

    ResponderEliminar
  10. 謝謝
    它是非常有用的

    ResponderEliminar
  11. Buenisimo, Me ayudo muchooo Gracias

    ResponderEliminar
  12. F4 no me funciona para las referencias..me activa opciones de pantalla. Sera porque tengo el windows 8 y office 2013 en un toshiba?? Alguien me puede decir donde puedo cambiar las teclas rapidas para aplicar referencias..??!!!

    ResponderEliminar
  13. estoy comenzando a usarlo pero estoy usando la versión del 2013 y tengo windows 10 y estoy algo confundido pero después de leer esto quedo un poco mas claro.

    ResponderEliminar
  14. Hola, tengo un problema con un trabajo que debo hacer.
    Tengo una tabla en la cual están los datos personales de un empleado (No. Documento, Nombres, Apellidos, etc). En otra tabla debo hacer una formula que con solo digitar el No. del Documento me aparezca el nombre. Lo cual no se como hacer, e intentado con las 3 referencias y no se me ha hecho.
    Por favor me ayudarian con ese "Problemita"??

    ResponderEliminar
    Respuestas
    1. Puedes ver el artículo referente a BUSCARV y con esa función puedes resolver tu problema.
      Saludos

      Eliminar
  15. muy bueno y breve y especifico gracias

    ResponderEliminar
  16. Como diferencio de las mixtas por columna y las mixtas por fila

    ResponderEliminar
    Respuestas
    1. El signo dólar fija la letra o número que tiene a su derecha.
      Si fijas una letra (por ejemplo: $A1) es una referencia mixta fijando la columna
      Si fijas un número (por ejemplo: A$1) es una referencia mixta fijando la fila
      Saludos

      Eliminar
  17. Intenté esto:
    =SI((B2)=H$2,I$2,SI((B2)=H$3,I$3,SI((B2)=H$4,I$4,SI((B2)=H$5,I$5,SI((B2)=H$6,I$6,SI((B2)=H$7,I$7,SI((B2)=H$8,I$8,SI((B2)=H$9,I$9,SI((B2)=H$10,I$10,SI((B2)=H$11,I$11,SI((B2)=H$12,I$12,SI((B2)=H$13,I$13,SI((B2)=H$14,I$14,SI((B2)=H$15,I$15,SI((B2)=H$16,I$16,SI((B2)=H$17,I$17,SI((B2)=H$18,I$18,SI((B2)=H$19,I$19,SI((B2)=H$20,I$20,SI((B2)=H$21,I$21,SI((B2)=H$22,I$22,SI((B2)=H$23,I$23,SI((B2)=H$24,I$24,SI((B2)=H$25,I$25,SI((B2)=H$26,I$26,SI((B2)=H$27,I$27,SI((B2)=H$28,I$28,SI((B2)=H$29,I$29,SI((B2)=H$30,I$30,SI((B2)=H$31,I$31,SI((B2)=H$32,I$32,SI((B2)=H$33,I$33,SI((B2)=H$34,I$34,SI((B2)=H$35,I$35,SI((B2)=H$36,I$36,SI((B2)=H$37,I$37,SI((B2)=H$38,I$38,SI((B2)=H$39,I$39,0))))))))))))))))))))))))))))))))))))))
    Pero Excel me dice lo siguiente: "No se puede utilizar la fórmula especificada porque usa más niveles de anidamiento de los permitidos en el archivo actual."
    ¿Cuál sería la solución? Gracias

    ResponderEliminar
    Respuestas
    1. En vez de usar una función SI con tantos niveles de anidamiento, hacer una tabla de dos columnas: a la izquierda, la condición, por ejemplo B2 = H$2, y a la derecha el resultado, por ejemplo I$2. Al finalizar el listado se tendrá una tabla de resultados. La solución entonces se logrará con un BUSCARV que busque el valor referido en la primera columna de la tabla, y devuelva lo qu encuentra en la segunda columna; algo así; BUSCARV(XX;tablaresultados;2;0).

      Eliminar
  18. Intenté esto:
    =SI((B2)=H$2,I$2,SI((B2)=H$3,I$3,SI((B2)=H$4,I$4,SI((B2)=H$5,I$5,SI((B2)=H$6,I$6,SI((B2)=H$7,I$7,SI((B2)=H$8,I$8,SI((B2)=H$9,I$9,SI((B2)=H$10,I$10,SI((B2)=H$11,I$11,SI((B2)=H$12,I$12,SI((B2)=H$13,I$13,SI((B2)=H$14,I$14,SI((B2)=H$15,I$15,SI((B2)=H$16,I$16,SI((B2)=H$17,I$17,SI((B2)=H$18,I$18,SI((B2)=H$19,I$19,SI((B2)=H$20,I$20,SI((B2)=H$21,I$21,SI((B2)=H$22,I$22,SI((B2)=H$23,I$23,SI((B2)=H$24,I$24,SI((B2)=H$25,I$25,SI((B2)=H$26,I$26,SI((B2)=H$27,I$27,SI((B2)=H$28,I$28,SI((B2)=H$29,I$29,SI((B2)=H$30,I$30,SI((B2)=H$31,I$31,SI((B2)=H$32,I$32,SI((B2)=H$33,I$33,SI((B2)=H$34,I$34,SI((B2)=H$35,I$35,SI((B2)=H$36,I$36,SI((B2)=H$37,I$37,SI((B2)=H$38,I$38,SI((B2)=H$39,I$39,0))))))))))))))))))))))))))))))))))))))
    Pero Excel me dice lo siguiente: "No se puede utilizar la fórmula especificada porque usa más niveles de anidamiento de los permitidos en el archivo actual."
    ¿Cuál sería la solución? Gracias

    ResponderEliminar
    Respuestas
    1. La solución es utilizar Excel 2010 o superior.
      En la versión 2007 solo esposible 7 niveles de anidamiento en funciones.
      Saludos.

      Eliminar
  19. Y COMO HAGO PA LA FORMULA DE FRECUENCIA MIXTA ME SALGA EN EXCEL 2013,CUANDO HAGO LA MISMA FORMULA NO ME SALE

    ResponderEliminar
  20. QUE PAIS ES????????????????????????????????????????????

    ResponderEliminar
  21. Si se tiene una referencia mixta con columna bloqueada Qué pasa cuando se fórmula se copia otras celdas?? es para una tarea porfa

    ResponderEliminar
  22. Si se tiene una referencia mixta con columna bloqueada Qué pasa cuando se fórmula se copia otras celdas?? es para una tarea porfa

    ResponderEliminar
  23. Si se tiene una referencia mixta con columna bloqueada Qué pasa cuando se fórmula se copia otras celdas?? es para una tarea porfa

    ResponderEliminar
  24. El bloqueo es contra la edición por parte de un usuario. Sea que la referencia fuera relativa,absoluta o mixta, la fórmula siempre va a funcionar. Espero haber respondido a tu inquietud! Saludos.

    ResponderEliminar