Trucos Excel. Repetir filas o columnas con la función DESREF

21 Abr

La función DESREF va a ser la protagonista de 2 trucos de Excel. Vamos a repetir filas o columnas con esta función. En nuestro caso la función va a devolver el valor de una celda referenciada del modo DESREF(<Celda inicial anclada>;<Filas por debajo de la referenciada>;<Columnas a la derecha de la fila referenciada>). Para nuestro caso el funcionamiento de la función DESREF será:

ejemplo-de-uso-desref-2.png

Repito, en este caso la función DESREF lo que hace es referenciar celdas en función de una celda inicial, de modo que el primer parámetro que le pasamos a la función es la referencia, el segundo parámetro es el número de celdas que nos movemos hacia abajo y el tercer parámetro el número de celdas que nos movemos a la derecha.  En nuestro caso fijamos la celda B3 como referencia y si deseamos repetir columnas (menos habitual) sólo hacemos DESREF(B3;0;0). Si lo que queremos es repetir filas lo primero que tenemos que hacer es crear el valor incremental sobre nuestra referencia. En el ejemplo deseamos repetir el número en 3 ocasiones y que después cambie, bien el autonumérico irá del 0 al 11, del 0 al 4×3 – 1. Hacemos una función REDONDEAR.MENOS donde dividimos nuestro autonumérico entre el número de veces que queremos repetir, en este caso 3. Y esa será la forma en la que se incrementará nuestra referencia.

Creo que he sido bastante claro con la exposición, pero si alguien tiene alguna duda en este link tiene el ejemplo utilizado para entenderlo mejor. En breve vuelvo con esta función.

75 respuestas a «Trucos Excel. Repetir filas o columnas con la función DESREF»

    • Cuando escribes del 1 al 10 y quieres repetirlo consecutivamente SIN seguir su numeración, es decir, que copies del 1 al 10 y volver a empezar y así consecutivamente…
      solo selecciona del 1 al 10 y deja presionado el «Ctrl» y en el numero 10 en la esquina inferior derecha te aparecerá dos cruces una mas grande que la otra y empieza a arrastrar sin soltar el «Ctrl» hasta donde desees copear.

  1. Gracias por la explicación!
    Si no fuera por el link seria como un libro sin hojas!

    ¿Escribir la función de B3 en el texto era muy difícil no?

  2. ¿Cómo lo harías si el número de repeticiones varía?

    Tengo que escribir un dato tantas veces como aparezca en una lista (A 3 veces, B 4 veces; C 3 veces,..), y no siempre son tres vees.

  3. Buenas, necesito hacer una planilla donde se controlen la entrada de mercaderia por REMITOS. Quiero hacer una tabla en donde se coloque la fecha, número de remito y proveedor. El tema es que necesito una formula que me imprima un mensaje de error cuando se colocan repedo los número de remito. Por ejemplo: si me ingresó un remito con el número 123 si en una semana me traen un remito con el mismo número (123) que me salga un mensaje que me diga error. Se que se puede hacer pero no encuentro la formula.
    Gracias.

    • Una forma es la validación de datos.
      Por ejemplo en la fila B (Donde está el número de remito)
      es Seleccionar toda esa fila hasta donde desees e irte después al menú donde dice «Datos» luego a «Validación de datos» darle en la flechita que esta a lado del nombre y darle «validación de datos…»
      Aparecerá un recuadro con 3 pestañas la primer pestaña es configuración y ahí es darle en «Numero exacto» en donde dice «Permitir» y en donde dice «Datos» es ponerle «Igual a» o «entre» y en Valor colocas el numero que quieres que no se repita en este caso el 123.
      En la 3ra pestaña que es «Mensaje de error»
      en «Estilo» es Detener
      En «Titulo» es titulo del mensaje ejemplo «Advertencia»
      En «Mensaje de error» pues como lo dice Muestra el mensaje del error que hubo por ejemplo «Remito Igualado o repetido»
      y ya le das en aceptar y listo.

  4. Muy buena funcion,pero solo funciona con valores numéricos? o se puede utilizar para repetir textos existe alguna forma de hacerlo por ejemplo, deseo.repetir el monbre ana 3 veces

  5. a ver… miré el excel, el desref, pero no veo la utilidad, lo que has hecho con ese largo proceso es que si yo tengo una columna con datos te muestre en otra columna los datos de la primera, y en caso de cambiar alguno se actualice, eso se puede hacer poniendo =Celdaelegida y te hará lo mismo que has hecho. creo que el «repetir» una celda se referirá a que si yo tengo(por ejemplo) u archivo inventario, y necesito que al indicar que poseo 5 unidades de un producto, inmediatamente en otra hoja me cree un grupo de celdas de igual cantidad a las unidades del producto con dato de identificacion y valor, y que al cambiar el número de unidades inmediatamente en la otra hoja se redusca la cantidad de celdas, a modo de crear un sistema de etiquetadonn1

  6. Buenos días.
    tengo una tabla con 400 líneas y necesito que cada línea se repita 4 veces.
    Cómo puedo hacerlo??
    Gracias anticipadas

  7. Pingback: Trucos Excel. Repetir filas con DESREF » Análisis y decisión

  8. Buenos Dias,

    Una pregunta como aplico esta formula si quiero que repita los datos por fila pero un numero variable de veces cada vez

    datos en la columba B, repeticiones en la columna C
    B3 repetir 5 veces, B4 repetir 8, B5 repetir 2 veces y asi en adelante.

    Gracias de antemano

  9. rvaquerizo, gracias por el aporte.No importa la fecha en la que lo subiste,lo importante es que esta vigente y que en su momento requirio tiempo y dedicacion.Me sirvio mucho para la tabla que necesitaba.

    Cordial saliudo y de nuevo gracias.

  10. Muy útil!!!

    Mi caso era duplicar n veces valor/referencias de una celda. Recomiendo que miréis el ejemplo que se ve la fórmula (son dos pasos).

    Gracias!!!

  11. Tengo una tabla de datos donde en una misma columna, cada ciertas filas esta una fecha determinada y abajo hay varias celdas en blanco pero de forma indistinta. Necesito que se repita la fecha en las celdas en blanco que están abajo de cada fecha, pero la cantidad de veces es diferente en cada caso. como lo hago?

  12. Tengo una hoja en la q debo dejar dos de las columnas en una. Lo q quiero es q en las celdas de la columna «a» q haya 0, copie el valor de la celda de la columna «b» q esta a su derecha.
    Gracias

  13. Tengo un informe con mas de 200 datos de facturacion. El sistema lo arroja con el nombre del cliente y debajo las facturas. Entre cliente y cliente quedan celdas en blanco que tengo que rellenar con el nombre de cada cliente manualmente. Sera que excel tien forma de llenarlo automaticamente?

  14. tengo una tabla con un codigo en una columna y en otra cantidad de productos y quiero que inserte las lineas la misma cantidad de veces que productos existan y que me mantenga el código

    codigo productos
    410986 2

  15. como resuelvo este problema.
    necesito capturar por dia y por cantidad unos datos y que me los arroje en columnas diferentes por fecha y no acumulados, que hago

    Dia Tienda Producto cantidad Clave Tienda 1° 2° 3° 4° 5°

    1 del rio papaya 15 341 15
    2 del rio papaya 25 341 25
    3 del rio papaya 10 341 10

    pero no se como hacerlo, utilizo el suma.si pero me acumula en un solo dia, puede ayudarme, gracias

  16. Hola buenas tardes disculpa tengo un problema con los rangos no se si podrias ayudarme ya que lo que intento hacer es que de una columna de datos copie la primera supongamos que es J2 y la pegue en la celda activa de la columna A y luego copie J3 y la pegue nuevamente en la celda activa de la columna A y asi sucesivamente hasta que se terminen los datos de la columna J.

    Te agradeceria mucho tu apoyo

  17. Buen dia.
    Tengo una duda, se supone que tengo una columna, pero quiero reinicar LA SUMA cada xxx (cantidad)
    Como seria la formula para esto

  18. De veras que si eres un genio…
    estuve todo el sabado y domingo tratando de hacer ello
    y con vos lo conseguí
    graciassssss

    • =extrae(texto, pos_inicial, #caracteres)

      Si nombre y apellido están separados por un espacio, o un guión, tendrás que buscar ese lugar dentro de la cadena Nombre + Apellido con la función ENCONTRAR.

      Sería más o menos así:

      =EXTRAE(texto, 1, ENCONTRAR(» «,texto,1))

  19. Hola
    Por favor puedes ayudarme con lo siguiente.
    tengo una planilla con «n» registros, que en la columna C guarda una variable que indica la cantidad de veces que debo repetir el registro, por ejemplo si indica 2, debo repetir una sola vez el registro, si indica 12, debo repetir 11 veces el registro, etc
    Seré eternamente agradecida

  20. Que tal amigo lo que busco es repetir la a2, n veces, luego a3, n veces,

    Hola /2
    Adios / 3

    Hola
    Hola
    Adios
    Adios
    Adios

    me podrías ayudar

  21. Buenas tardes. Un favor. Me podrias ayudar con esto. Tengo una planilla con datos (fecha, cliente, articulo, cantidad) en una tabla dinamica, y necesito copiar estos datos en otra tabla dinamica para control cantidad de articulos, el problema que tengo es que al momento de copiar los valores de una tabla a otra, se sobreescribe en los valores, borrando los anteriores, como puedo evitarlo. Me puedes ayudar porfavor. Gracias

  22. Buenas tardes. Quiero que, si se introduce un valor repetido en un rango, se dupliquen los datos de las celdas que se encuentran a la derecha de ese valor existente en las celdas adyacentes a la que se esta editando

  23. Buen día, tengo una pregunta tengo datos de una tabla dinámica unidades y valores, como hago para colocar unidades en una fila y valores en otra fila debajo de las unidades??
    Gracias

  24. Si la celda que quiero que se reprita es variable pongamos un ejemplo
    luis se debe repetir 2
    angel se debe repetir 4
    Cual seria la formula en este caso

  25. Como puedo copiar cada numero diferente numero de veces? Por ejemplo si tengo una matriz de los numeros 20 y 30 y quiero repetir el numero 20 por 5 filas pero el 30 por 6 filas.

  26. Hola, no se si me puedas ayudar con lo siguiente, Tengo en hoja1, A1 matemáticas, A2 química, A3 física y en hoja2 tengo A1 10, B1 8, C1 9, necesito copiar en hoja1 en B1 el valor de hoja2 celda A1, hasta ahí no tengo problema, la formula me queda de la siguiente manera =Hoja2!A1, pero el problema me surge cuando yo quiero copiar la formula en Hoja1 en la celda B2 ya que la formula me queda de la siguiente manera =Hoja2!A2 y yo necesito que se copie el valor de Hoja2 celda B1, ya intente con referencias absolutas pero no puedo hacer que la columna sea relativa para que cambie A, B, C, etc. y que la fila siempre sea 1. Le agradezco cualquier ayuda que me pueda proporcionar.

  27. Cordial saludo, tengo que programar que cada que se encuentre un valor, excel lo copie y pegue en las celdas inferiores que no tengan valor, es posible? necesito ayuda, gracias

  28. tengo una planilla en donde los datos de columnas y lineas los invierto en otra planilla. Las columnas pasan a ser lineas y las lineas en columna en la otra planilla.
    Las celdas son alimentadas con valores en la primera planilla. Ahora viene la pregunta.
    Como automatizo la segunda planilla de tal forma que al modificar los datos en la primera automaticamente se modifiquen en la segunda.
    Ya intente haciendo lo siguiente: Un link desde la primera celda de la segunda planilla buscando el dato en la primera planilla, el dato es leido y llega a la segunda planilla, luego ese link lo intento copiar de todas las formas posibles al resto de la segunda planilla, pero no funciona de ninguna forma. Necesito un socorro, Muchas gracias

  29. Quiero hacer una tabla , con un personal de turno que trabaja 2X2 es decir trabaja 2 dias y descansa 2. Pero quiero saber que funcion me permite realizar en el excel que se me repita el personal cada dos dias, cuando yo ponga la fecha, automaticamente salgan las personas que le toca ese día segun los turnos.
    Por favor si alguien sabe hacerlo me pueden escribir al correo dailyn9302@nauta.cu

  30. Utilizamos el excel para base de datos de tarjetas de presentacion.
    Como puedo programar en excel, para que al llamarlo como csv, al Indesign, le indique a este, la cantidad de tarjetas que debe repetir, de un cliente x, de esa base de datos.

  31. Que formula utilizo para repetir una fila x cantidad de veces, utilizando base de datos en excel y desde excel indicarle al programa donde exporto la base de datos cuantas veces quiero repetir los datos (el programa sería Smartream Designer)

  32. necesito ayuda, tengo una tabla de datos:
    vestido a 4
    vestido b 2
    vestido c 5

    y quiero que en otra hoja de excel se haga una lista asi:
    vestido a
    vestido a
    vestido a
    vestido a
    vestido b
    vestido b
    vestido c
    vestido c
    vestido c
    vestido c
    vestido c
    depende del numero de piezas que se copie en una lista y el siguiente elemento continue la lista de igual forma.

  33. Hola,
    Tengo una hoja con múltiples tablas en cuyas primera columna, continental datos que tengo que unificarlos todos en una tabla diferente
    Hoja1 Hoja2 hoja 3 HOJA RESULTADO
    h11 h21 h31 h11
    h12 h22 h32 h12
    h13 h33 h13
    h14 h14
    h21
    h22
    h31
    h32
    h33

    Alguien puede ayudarme?
    Gracias

  34. 1) ¿Por que abajo a la izquierda aparece Hoja1, Hoja2, Hoja3 ? ¿que pasa si hacemos clic ahí?

    2) ¿Cual es el nombre del documento? ¿Por qué podría tener ese nombre?

  35. Hola, estoy buscando una función o condición en donde yo pueda Hacer mis números de letras a pagar, ejemplo:
    1/12
    2/12
    3/12
    Y así seguir, pero en una celda ya tengo el número de letras que son (ejemplo: meses 12), como puede hacer que en donde van los 1/12, 2/12, 3/12, etc… se haga en automático?

  36. favor su ayuda no puedo repetir una palabra en excel con tan solo poner una letra , como generalmente hacia, ojo que no puedo hacer la accion de copiar y pegar porque lleva mucho tiempo gracias…

  37. Buenas noches, tengo una tabla con dos valores e busqueda que se repiten por ejemplo lunes y 11/02/2020, me gustaria que busque si ambos criterios coinciden coloque verdadero si no falso, pero si uso BuscarV me trae el primero que encuentra se que el blog es con Desref pero ya he agotado todas las formilas creo.

    Gracias

    • Hola, cuando me he encontrado en esas situaciones creo que lo mejor es concatenar. Creas una celda «11/02/2020 – Lunes» y eso es lo que buscas, es como hacer una clave compuesta. Saludos.

  38. Estoy intentando encontrar el ultimo valor de una columna en una hoja y llevarlo a una celda de otra hoja con la siguiente formula =DESREF(DICIEMBRE!N1;CONTAR(N:N)+1;0)

    DICIEMBRE es la hoja origen, y N la columna , pero cuando inserto esta formula en la hoja destino me sale solo el valor de la segunda celda de la columna, no de la ultima. Sin embargo si la pongo en la hoja origen funciona perfectamente. Alguien me puede ayudar

  39. Buenas tardes, podrías por favor ayudarme con una función que permita repetir datos de celdas saltadas (he querido trabajar en una macro con esa función y no logro replicar el proceso). Lo que deseo es automatizar una función que permita replicar una celda en espacios en blanco y que se detenga la función hasta que detecte otra celda con datos. Es decir, en vez de quedar.
    X 1
    2
    3
    Y 2
    3
    Z 1
    4
    5
    quede
    X 1
    X 2
    X 3
    Y 2
    Y 3
    Z 1
    Z 4
    Z 5
    Como el arrastre común de celda, pero de manera automática.

  40. Hola que tal, no soy muy bueno en excel y necesito una función que realice lo siguiente cientos de veces. Explico.
    Tengo un listado de numeros del 1 al 20 ( o mas) en forma vertical de los cuales necesito asignar 50 datos a esos 500 numeros con un determinado contador. Ejemplo

    Los datos numero de veces a repetir
    Legal 5 veces
    Finanzas 7 veces
    Compras 3 veces
    Contabilidad 2 veces
    Marketing 3 veces

    Con esos datos asignar esa cantidad de veces en forma vertical, para que quede:

    1 – Legal
    2 – Legal
    3 – Legal
    4 – Legal
    5 – Legal
    6 – Finanzas
    7 – Finanzas
    8 – Finanzas
    9 – Finanzas
    10 – Finanzas
    11 – Finanzas
    12 – Finanzas
    13 – Compras
    14 – Compras
    15 – Compras
    16 – Contabilidad
    17 – Contabilidad
    18 – Marketing
    19 – Marketing
    20 – Marketing

    No siempre son los mismos numeros a repetir, el listado de numeros consecutivos puede ser rangos de 5 hasta 500.
    No se si se pueda que mientras jale hacia abajo la formula vaya poniendo el texto.
    Espero me haya explicado.
    Saludos y gracias por su ayuda

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *