Algunas veces queremos copiar celdas alternas de un rango o cada tercer valor o cada cuarto valor, y así sucesivamente. En todos estos casos, copiar manualmente será una tarea agotadora. ¿Entonces, cuál es la solución? ¿Cómo se copia el valor de cada enésima fila? Hay una formula para esto?
Sí hay. Podemos usar la función OFFSET de Excel para obtener valores de enésimas filas. ¿Cómo? Vamos a verlo.
= DESPLAZAMIENTO (First_Cell_in_List, (FILA (A1) *norte)-1,0)
First_Cell_in_Range: Es la referencia absoluta de la primera celda del rango de la lista desde la que desea copiar. Por ejemplo, si su lista está en A5: A100, entonces First_Cell_in_Range es $ A $ 5. No incluya el encabezado de la columna.
norte: es un número del que obtendrás un valor múltiplo. Por ejemplo, si desea obtener cada segundo valor de la lista, entonces N es 2. Por cada tercer valor, es 3 y así sucesivamente.
FILA (A1): es para obtener múltiplos de N.
Ahora veamos un ejemplo y luego explicaré cómo funciona.
Ejemplo Copiar cada segundo valor de una lista en Excel
Aquí tenemos una lista de artículos. Necesitamos copiar cada segundo valor de la lista en un nuevo rango.
¿Entonces cómo hacemos eso?
Como tenemos 11 elementos en la lista, obtendremos cinco elementos alternativos (2, 4, 5, 6, 10).
Escriba la siguiente fórmula en E4 y copie las celdas.
= COMPENSACIÓN ($ B $ 4, (FILA (A1) *2)-1,0)
Aquí $ B $ 4 es la primera celda en el rango de la lista, y como queremos copiar cada segundo valor (alternativo) de la lista, tenemos nuestra N como 2.
Si desea copiar cada tercer elemento, use esta fórmula.
= COMPENSACIÓN ($ B $ 4, (FILA (A1) *3)-1,0)
Por cada cuarto:
= COMPENSACIÓN ($ B $ 4, (FILA (A1) *4)-1,0)
Etcétera. Puede copiar cualquier valor enésimo de a utilizando esta fórmula de Excel.
Cómo funciona
La fórmula logra esta tarea usando dos funciones de Excel, OFFSET y ROW.
La función DESPLAZAMIENTO devuelve el valor del desplazamiento de fila y columna de una celda determinada.
La función FILA devuelve el número de fila de la referencia dada.
Comenzando desde adentro, FILA (A1) devolverá 1. Dado que A1 pertenece a la 1ra fila.
(FILA (A1) *2 se convertirá en 2.
(FILA (A1) * 2) -1, se convertirá en (1 * 2) -1 = 1.
Cuando copiamos la fórmula, la referencia en FILA cambiará a A2, A3, A4, etc. Para A2, obtendremos el valor final (2 * 2) -1 = 3. Para A3, obtendremos (3 * 3) -1 = 5 y así sucesivamente.
Ahora, nuestra fórmula en E4 se resolvió en = OFFSET ($ B $ 4,1, 0). Esto devolverá el valor 1 fila por debajo de B4, que es el segundo valor de B4.
En E4, la fórmula será = OFFSET ($ B $ 4,3,0), que es el cuarto valor de B4. De manera similar, obtenemos todos los costos alternos.
Ahora sabe cómo copiar cada valor N de la lista en Excel. Esta es la técnica que conozco. Si conoce algún otro truco para copiar la enésima celda de un rango, no dude en compartir en la sección de comentarios a continuación. Si tiene alguna consulta sobre esta o cualquier otra función de Excel 2019, 2016, 2013 y 2010, utilice la sección de comentarios.
Cómo recuperar el último precio en Excel
Recuperar el primer valor de una lista mayor o menor que un valor especificado
Cómo recuperar la fila completa de un valor coincidente
Articulos populares:
La función BUSCARV en Excel
CONTAR.SI en Excel 2016
Cómo usar la función SUMIF en Excel