jueves, 17 de junio de 2010

Celdas con referencia de estilo F1C1.

Llevaba un tiempo queriendo hablar sobre los dos tipos de referencias que existen en Excel, referencias de estilo A1 y F1C1; pues ayer un lector pidió ayuda para realizar una operación simple en la que disponía de distintos valores colocados en filas, pero quería resumir a modo de suma sus datos por columnas. Es decir, quería arrastrar su fórmula hacia abajo pero sumando hacia la izquierda....

...como puedo hacer cuando tengo valores en varias columnas, por ejemplo
a b c
1 5 8 7
2 5 5 5
y quiero el resultado de la siguiente manera
saldo 1 = a1 + a2
saldo 2 = b1 + b2
saldo 3 = c1 + c2

como hago para copiar la formula hacia abajo y que cambie nada mas la columna, siendo las filas las mismas...



Antes de dar una solución al problema conviene explicar qué ese eso de la referencia de celda en estilo F1C1. De una manera muy rápida y sencilla todos reconocemos la forma de escribir la referencia de una celda, por ejemplo la celda A1 o C3, identificando el primer caracter (la letra) como el de la columna y el segundo (el número) como la fila; sabiendo además que en función del símbolo dolar $ combinamos y obtenemos referencias relativas y absolutas. Pues además de esta forma (la más habitual) Excel trabaja con otro estilo de referencia, el F1C1, con el que podemos trabajar si lo activamos (Botón Office > Opciones de Excel > Fórmulas > Trabajando con fórmulas > Estilo de referencia F1C1). Esta opción nos permitirá trabajar con números tanto para definir las columnas como las filas, pero ojo!!, se cambia el orden habitual, con este estilo primero indicamos la fila y en segundo lugar la columna.

Para dar una solución al problema del lector seguiré trajanado con el estilo A1, pero aprovechándome de las ventajas del estilo F1C1 y de las ventajas de la función INDIRECTO; veamos una tabla de valores con los que trabajaremos:



Queremos formular, en nuestro ejemplo, la suma de las celdas A2+A3, de B2+B3, etc y disponer de esas sumas formuladas al arrastrar hacia abajo, lo que se hace difícil o imposible empleando las referencias estándar A1; es por ello que aplicaremos las referencias de estilo F1C1. Sabemos que la celda A2 la podríamos escribir como F2C1, la celda A3 como F3C1 o la celda B2 como F2C2...
Aprovechando que podemos dirigirnos a celdas empleando números, definimos las siguiente función en la celda A7:
=INDIRECTO("F2C"&FILA()-6;0)+INDIRECTO("F3C"&FILA()-6;0)


La función INDIRECTO consigue hacer entender a Excel la nomenclatura F1C1, ya que el segundo argumento de la función -FALSO ó 0-, i.e., el primer argumento lo interpretará como una referencia de estilo F1C1.
Definimos entonces el primerr argumento, dejando como fija la primera parte de la celda 'F2C', es decir, me interesa la fila 2 y la columna que obtenga de restar 6 a la fila en que me encuentro; de igual forma para 'F3C', es decir, los valores de la fila 3.
Es precisamente este número de columna obtenido de manera formulada, el primer valor sumado está en la fila 7, por lo que si le resto 6 me queda 1, lo que me dirije a la columna B, la segunda suma una fila más abajo-fila 8, al restarle 6 obtengo un valor 2, lo que indicaría la columna B, etc lo que facilita poder arrastrar hacia abajo la fórmula y que vaya identificando cada columna a sumar.

36 comentarios:

  1. ¿Y cómo referencio si el origen está en una hoja diferente?

    ResponderEliminar
  2. Hola!
    Si la hoja de trabajo es una diferente, podrías incluirla dentro de la función; asi:

    =INDIRECTO("Hoja1!F2C"&FILA()-6;0)+INDIRECTO("Hoja1!F3C"&FILA()-6;0)

    Un saludo y espero te sirva

    ResponderEliminar
  3. ola tengo un problema con excel 2010 no puedo lograr escribir los numeros de forma normal , se autocorrige a fecha , he probado de todo y nada , por fa agradecia que me ayuden , para que me entiendan mejor , esto sucede:
    yo quiero poner
    1
    2
    3
    4
    y me sale
    01 - agos
    como hago ya trate de desactivar pero nada

    ResponderEliminar
  4. Hola,
    la verdad es que resulta extraño, sobre todo por que al introducir el valor 1 te devuelva 01-ago (i.e., 01-08 !!!). He estado revisando algún manual y no consigo encontrar nada similar...
    Lo único que se me ocurre es que tenga algo que ver con Windows (quizá tengas Vista?), pero no es seguro.
    Lamento no tener una solución a tu problema.
    Slds

    ResponderEliminar
  5. TENGO ARCHIVOS DE EXCEL Y AHORA QUIERO COLOCAR NÚMEROS EN LAS CELDAS Y NO PUEDO, SALTA A LA SIGUIENTE CELDA...¿QUÉ PASA?

    ResponderEliminar
    Respuestas
    1. Hola que tal!!??
      es dificil saber, pero prueba en primer lugar que no tengas bloqueado el teclado numérico; luego que no esté protegida la hoja de forma que impida introducir valores...
      no sé me ocurre que más podría ser, investigaré un poco al respecto.
      Un atento saludo

      Eliminar
  6. Hola excelforo
    Una consulta si esta fórmula =INDIRECTO("F2C"&FILA()-6;0)+INDIRECTO("F3C"&FILA()-6;0)
    me dice que me suma las fila 2 y la fila 3 del campo1 y me da como resultado 17 hasta ahí bien, pero quiero saber como se hace con cien filas porqué si sigo el mismo método mi fórmula sería enorme.
    y muchas gracias por crear esta blog, para mí es excelente
    atentamente
    Graciela

    ResponderEliminar
    Respuestas
    1. Hola Graciela,
      bueno, yo usaría la notación 'normal' A1, quiero decir, si quieres sumar las cien filas de un rango, usa mejor:
      =SUMA(A2:A100)
      o si quieres emplear la notación F1C1, sería algo así:
      =SUMA(INDIRECTO("F2C"&FILA()-6&":"&"F100C"&FILA()-6;0))
      Espero te sirva,
      Un saludo y muchas gracias

      Eliminar
  7. Hola cuando pongo F2 para ir a la fila 2 me salta a la fila 20

    ResponderEliminar
  8. Osea tengo esta funcion =+INDIRECTO("Hoja3!"&"F2"&COLUMNA()-1&"C1";FALSO)
    y quiero arrancar de la fila 2 pero lo hace desde la 20....

    Saludos Gracias

    ResponderEliminar
    Respuestas
    1. Hola Gaston,
      bueno, es que has añadido después de F2 un valor definido por COLUMNA()-1, como entiendo que has situado tu celda en algún lugar de la columna A, lógicamente te devuelve F2 y luego un cero (columna A=1, si luego le restas 1...), con lo que obtienes F20 (fila 20).
      Si quieres la fila 2, la fórmula sería:
      =INDIRECTO("Hoja3!"&"F2"&"C1";FALSO)

      Y si, como adivino, quieres recorrer las filas hacia abajo, añadirías a continuación de C1:
      =INDIRECTO("Hoja3!"&"F2"&"C1"&FILA();FALSO)

      Slds

      Eliminar
  9. Hola!!! Pero ahora no me esta recorriedo la columna.quiero recorrer la columna de una hoja y transponerlo ( en una fila)
    Muchas Gracias

    ResponderEliminar
  10. Ahora me da todos los valores falsos o sea si agrego &filla o &columna se me dispara a cualquier posición tendría que haber una forma para que me pueda recorrer una columna de forma automática y me lo pongo como fila usando esta formula que funciona perfecto =INDIRECTO("Hoja3!"&"F2"&"C1";FALSO)

    Gracias!!

    ResponderEliminar
    Respuestas
    1. Hola Gaston,
      la fórmula del post
      =INDIRECTO("F2C"&FILA()-6;0)+INDIRECTO("F3C"&FILA()-6;0)
      traspone fila en columna.
      Y esta traspone columna en fila:
      =INDIRECTO("F"&COLUMNA()-2&"C1";0)

      OJO con el valor restado, depende de donde tengas situado la fórmula.
      Slds

      Eliminar
  11. Muchas gracias lo pude solucionar!!!

    ResponderEliminar
  12. Excelente funcion.

    Pero tengo la siguiente pregunta quiero sumar un rango de una fila que proviene de varias columnas de una a otra hoja si cumplen una condicion.

    Ejem:

    HOJA 1
    --- A (B) (C) (D)
    1 cod4 2 4 6
    2 cod2 2 2 2
    3 cod3 5 5 5
    4 cod1 6 2 1


    HOJA 2
    -- (A) ( B)
    1 cod1 9
    2 cod2 6
    3 cod3 15
    4 cod4 12

    Esto es lo que quiero hacer:
    Buscar el (codigo 1) de la hoja 2 en la columna (a) de la hoja 1 y que me sume las columnas (b c d )de la fila a que corresponde el codigo encontrado.



    Favor tu ayuda.


    Gracias,

    ResponderEliminar
    Respuestas
    1. Hola,
      en este caso, ya que trabajas con varias hojas, te recomendaría bien emplear la Herramienta Consolidar, o bien con el Asistente de tabals dinámicas 2003, emplear Rangos de consolidación múltiples, sobre ambos rangos.
      En ambos casos es determinante (como en tu ejemplo) que la primera columna de los dos rangos tengan el mismo concepto.
      Slds

      Eliminar
  13. como traspaso el valor de una celda obtenida mediante la función "indice" a otra celda vacía

    ResponderEliminar
    Respuestas
    1. Hola, buenos días, que tal?
      No sé si hay algo más detrás de la pregunta... pero en principio puedes llevar el valor devuelto por una función cualquiera a otra celda con una referencia a esta.
      Por ejemplo, en la celda A1 tienes la función INDICE que te devuelve un valor, si quieres llevar ese valor a otra celda vacía (de momento) C1, en ésta escribe: =A1
      O sencillamente copia y pega como valores.

      Espero te sirva, con la explicación que aportas poco más te puedo ayudar.

      Un atento saludo

      Eliminar
  14. como puedo con esta funcion o con otra si es que exite, que lleve los datos al arrtara la formula de seis columasn mas y no la continua

    ejemplo
    a1=2; a2= 3; a3= 7; a4= 10; a5= 8; a6= 11

    quieo que en otra hoja en la delda D1 me ponga el valor de la celda A1 osea 2, y al arrastrar la formula , que ne la celda D2 me ponga el ponga el valor de la celda A& , osea 11, y asi sucesivamente , vamos que me ponga el valor de cada 6 columnas

    esto es posible

    saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      supongo querías decir el valor de cada 6 filas...
      Sólo hay que encontrar una regla o secuencia con la que consigas ese avance, una por ejemplo sería:
      En D1: =INDIRECTO("F"&FILA()&"C1";0)
      En D2: =INDIRECTO("F"&6*FILAS($E$1:E1)&"C1";0)
      En D3 y sucesivos:
      =INDIRECTO("F"&6*FILAS($E$1:E2)-FILAS($E$1:E1)&"C1";0)

      Slds

      Eliminar
  15. tengo un fichero con dos hojas
    "DINÁMICA DE DATOS RUPTURAS"
    y
    "DATOS MENS. N. MODELO"
    en la hoja "DINÁMICA DE DATOS RUPTURAS" tengo los datos siguientes en la celda E9 =0.13, en la celda K9=0.12, en la celda Q9=0.05, y en la celda V9= 0.25 y sucesivas cada 6 columnas y lo que deseo es llevar
    el valor de las celdas anteriores a la hoja "DATOS MENS. N. MODELO" y en las celdas P20, P21, P22, P23, P24 , etc
    osea que en P21 me ponga el valor de la E9=0.13 y asi sucesivamente
    como ves querría llevar el valor de cada seis columnas y ponerlo en las celdas consecutivas a partir de la P20, P21, P22, P23, P24 de la hoja "DATOS MENS. N. MODELO"
    por supuesto lo que deseo es una formula que al arrastrarla desde la celda P20, me ponga los resultados de la celdas E9, K9, etc
    algo parecido a esta que tengo para filas
    =INDIRECTO("DINÁMICA DE DATOS RUPTURAS"&DIRECCIÓN(6*FILA()-1;5))

    que me pone el dato de una fila, (cuenta 6 filas y resta 1), de la columna 5 y ese dato me pone , donde tengo la formula, pero al revés, osea que me cuente 6 columnas y me reste 1 columna no encuentro como hacerlo
    gracias anticipadas

    ResponderEliminar
    Respuestas
    1. Bueno, el asunto es complicado, ya que como te decía habría que encontrar la fórmula de la secuencia de valores 5,11,17,23 etc.

      Creo que una solución sería disponer esos valores conocidos en una columna, por ejemplo en el rango O20:O30 esos valores, y al lado en P20:P30 la fórmula arrastrada:
      =INDIRECTO(DIRECCION(9;O20))

      Espero te resulte.
      Slds

      Eliminar
  16. oye tengo un problema es que tengo un computador MACBOOK AIR 2012, cuando abro excel inicia desde F1C1 ya intente buscar en opciones sino que no tengo opciones formula para cambiar el estilo de la referencia....pero no encuentro estas opciones ni de formula ni de estilo de referencia

    ResponderEliminar
    Respuestas
    1. Hola que tal, Dannytha.
      Un placer saludarte.
      Lo siento pero no trabajo con Mac y no puedo asegurarte la localización de esta Opción; pero seguro existe entre las Opciones de Excel la posibilidad de marcar /desmarcar este estilo F1C1.
      En versión PC (windows) está:
      Botón Office/ficha Archivo > Opciones de Excel > Fórmulas > Trabajando con fórmulas > Estilo de referencia F1C1
      UN cordial saludo

      Eliminar
  17. Hola Ismael, Cómo estás?
    Estuve viendo este post y no logro resolver algo que entiendo podría hacerse por esta vía. Te comento: tengo que hacer algo similar a lo que publica Anónimo el 8 de abril de 2013.
    En una columna tengo una suma de fórmulas de 12 fórmulas cuyos argumentos están en otra hoja y toman valores en cada celda =BiomasaMIN!$D2*Coeficientes!$Q$25*AuxDispoMIN!BN40+BiomasaMIN!$E2*Coeficientes!$Q$25*AuxDispoMIN!BN41+... hasta llegar a ...+BiomasaMIN!$O2*Coeficientes!$Q$30*AuxDispoMIN!BN51
    La celda justo debajo debería ser igual a =BiomasaMIN!$D3*Coeficientes!$Q$25*AuxDispoMIN!BN52+BiomasaMIN!$E3*Coeficientes!$Q$25*AuxDispoMIN!BN53+... hasta llegar a ...+BiomasaMIN!$O3*Coeficientes!$Q$30*AuxDispoMIN!BN63
    Como verás, cambian dos valores, el referido a BiomasaMIN!$D2 a BiomasaMIN!$D3 y los valores de AuxDispoMIN!. Estos últimos lo hacen a saltos ya que en una celda toman valores desde 40 a 51 y en la celda de abajo deben tomar valores de 52 a 63. El tema es que cuando arrastro la celda hacia abajo me entrega valores de 41 a 51 y no de 52 a 63 como yo deseo... Hay alguna solución posible a este tema? Muchas gracias de antemano!
    Nicolás

    ResponderEliminar
    Respuestas
    1. Hola Nicolás!!
      podrías mejor enviarme el fichero o un ejemplo a
      excelforo@gmail.com
      me cuesta ver la distribución de datos.

      De todas formas, la respuesta será muy parecida a la del comentatio que mencionas, habrá que buscar una regla que sirva y cumpla ese avance al arrastrar o copiar.
      Saludos cordiales

      Eliminar
  18. Tengo la función: CONSULTAH($A26;'D:\i2E\Presup\Paso\SOLCA Consult\Oferta v_01\[SOLCA Consult APU 01 v_01.xls]7.2.2.1'!$A$56:$C$67;5; falso)

    El tema es que la sección 7.2.2.1 se refiere a el nombre de una determinada hoja en otro libro de Excel; el tema es que cada vez se tienen que generar archivos y se debe redireccionar las hojas (todas con diferente información); a groso modo se debe realizar aproximadamente un redireccionamiento de unas 900 filas lo que resulta largo.

    Ahora bien se podría tener en una columna la información del nombre de la columna y lo ideal sería que la referencia ubique el nombre de esa hoja y lo incorpore a la segunda sección de la función CONSULTAH o CONSULTAV como referencia; he intentado con las funciones INDICE y CONCATENAR pero no obtengo el resultado adecuado (me da error); no he utilizado la función INDIRECTO en virtud de que me indica de antemano que los archivos a los cuales debe hacer referencia deben estar abiertos y en algunas ocasiones se hace referencia a mas de 30 archivos cada uno de ellos con 30 hojas o más, lo cual resulta engorroso.

    De antemano agradezco su colaboración si me puede dar una guía de como solucionar el problema planteado

    ResponderEliminar
    Respuestas
    1. Hola Jorge,
      pues la única solución, si no deseas emplear INDIRECTO (por los motivos que expones) sería emplear programación y que fuera la macro quien abriera y cerrara esos 30 libros para traer el valor buscado.
      Bastaría un bucle for next sobre esa nueva columna con los nombres de los libros y sobre la columna de los nombres de las hojas para componer una variable String con el nombre completo de la ruta:
      D:\i2E\Presup\Paso\SOLCA Consult\Oferta v_01\[SOLCA Consult APU 01 v_01.xls]7.2.2.1'!$A$56:$C$67

      luego podríanos usar esa variable para componer la búsqueda, incluso con la función BUSCARV dentro de la macro, teniendo la precacuión de abrir antes cada libro (y luego cerrarlo), con los métodos .Open y .Close

      Espero haberte dado la idea.
      Saludos

      Eliminar
  19. Hola,

    Veo que es posible cambiar la notación de A1 a F1C1 de una suma de X número filas, pero es posible realizar el cambio de notación para una suma de toda una columna? ejemplo:

    Suma de 99 filas en notación A1:
    =SUMA(A2:A100)
    Suma de 99 filas en notación F1C1:
    =SUMA(INDIRECTO("F2C"&FILA()-6&":"&"F100C"&FILA()-6;0))

    Suma de columna en notación A1:
    =SUMA(A:A)
    Suma de columna en notación F1C1:
    =???????

    Saludos y de antemano muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Alejandro,
      si es posible. Ten en cuenta que el estilo F1C1 trabaja en 'relatividad', es decir marca filas y columnas desde la posición en que se encuentra la fórmula.
      Por ejemplo si quiero introducir en D1
      la suma de la columna A, escribiría:
      =SUMA(C[-3])
      es decir, toda la columna que se encuentra 3 posiciones a mi izquierda.
      Saludos

      Eliminar
  20. Buenos dias tengo un problema con excel de MAC donde las columnas me sales numeros en vez de letras. Si alguien supiera la solucion le agradeceria mucho su ayuda gracias

    ResponderEliminar
    Respuestas
    1. Hola Sergi,
      En la versión Windows está en Opciones de Excel > menú Fórmulas > opción estilo L1C1...
      en Mac (algún usuario te lo confirmará) debe estar en algún lugar similar.
      Saludos

      Eliminar
  21. Hola tengo un problema al copiar la formula indirecto a las celdas de abajo ya que se relacionan simepre con la primera del tipo... osea tengo en la primera celda provincia y junto la ciudad lo cual se relaciona muy bien con la formula indirecto pero al momento de arratrar la formula ya q es una base de datos, todas las ciudades dependen de la primera celda de provincia.... ayudenme por favor

    ResponderEliminar
    Respuestas
    1. Hola,
      quizá la referencia que estés arrastrando sea absoluta cuando debería ser relativa o mixta ???

      Es difícil predecir sin tener un ejemplo de la función que empleas, pero debería tener esta forma:
      =INDIRECTO(S$A1)

      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.