Icono del sitio Desafiando Excel

Hallar celdas cada N filas o columnas en Excel

Funciones utilizadas: DESREF, FILA, COLUMNA

Suponiendo un rango de datos en A2:A15, planteamos los siguientes métodos para hallar datos cada 4 filas, aplicado sobre la celda C2 y arrastrando hacia abajo hasta completar con el último dato válido:


HALLAR CELDAS CADA N FILAS EN EXCEL (SIN CONSIDERAR PRIMER VALOR)

=DESREF($A$2;(FILA(C1)*4)-1;0)

Donde «$A$2» corresponde al primer dato del rango original fijado de forma absoluta para poder arrastrar la fórmula, «C1» se aplica para obtener el valor de la primera fila (puede ser cualquier celda de la fila 1 de referencia) y «*4» aplica un patrón de «cada 4 valores» para obtener el resultado deseado.

Combinamos las funciones DESREF y FILA para obtener el resultado deseado.

Asignamos la primera celda del rango objetivo como argumento de referencia absoluto para la función DESREF, y la función FILA apunta a la primera celda de cualquier columna (en este caso C1) devolviendo el valor «1». Luego este resultado es multiplicado por 4 (valor N) que al arrastrar hacia abajo la fórmula permite incrementar el valor de fila por 1, creando así el patrín «cada N» requerido.

Finalmente restamos con «-1» debido a que la función DESREF no incluye la celda de referencia cuando se aplica el argumento de fila.


HALLAR CELDAS CADA N FILAS EN EXCEL (CONSIDERANDO PRIMER VALOR)

=DESREF($A$2;(FILA(C1)-1)*4;0)

Donde «$A$2» corresponde al primer dato del rango original fijado de forma absoluta para poder arrastrar la fórmula, «C1» se aplica para obtener el valor de la primera fila (puede ser cualquier celda de la fila 1 de referencia) y «*4» aplica un patrón de «cada 4 valores» para obtener el resultado deseado.

En este caso invertimos las expresiones «*4» y «-1» respecto al caso anterior, para así considerar e incluir al primer valor como resultado en el rango final.

 

Suponiendo un rango de datos en B1:O1, planteamos los siguientes métodos para hallar datos cada 4 columnas, aplicado sobre la celda B3 y arrastrando hacia la derecha hasta completar con el último dato válido:


HALLAR CELDAS CADA N COLUMNAS EN EXCEL (SIN CONSIDERAR PRIMER VALOR)

=DESREF($B$1;0;(COLUMNA(A3)*4)-1)

Donde «$B$1» corresponde al primer dato del rango original fijado de forma absoluta para poder arrastrar la fórmula, «A3» se aplica para obtener el valor de la primera columna (puede ser cualquier celda de la columna 1 de referencia) y «*4» aplica un patrón de «cada 4 valores» para obtener el resultado deseado.

A diferencia del método anterior por fila, reemplazamos la función FILA por COLUMNA y también invertimos los dos argumentos de filas y columnas de la función DESREF.


HALLAR CELDAS CADA N COLUMNAS EN EXCEL (CONSIDERANDO PRIMER VALOR)

=DESREF($B$1;0;(COLUMNA(A3)-1)*4)

Donde «$B$1» corresponde al primer dato del rango original fijado de forma absoluta para poder arrastrar la fórmula, «A3» se aplica para obtener el valor de la primera columna (puede ser cualquier celda de la columna 1 de referencia) y «*4» aplica un patrón de «cada 4 valores» para obtener el resultado deseado.

En este caso invertimos las expresiones «*4» y «-1» respecto al caso anterior, para así considerar e incluir al primer valor como resultado en el rango final.

 

De estas formas, logramos hallar celdas cada N filas o columnas dentro de un rango objetivo de forma simple y rápida en Excel.

Te recomiendo ver los siguientes artículos relacionados sobre como transponer patrones de datos en Excel:

Transponer patrones de celdas de una sola columna en varias columnas – Método Desref

Transponer patrones de celdas de una sola columna en varias columnas – Método por reemplazo

Puedes encontrar más formas simples y efectivas de sumar, contar y hallar valores en Excel en el ebook gratuito (freebook) disponible en la tienda, o bien en el anuario de Desafiando Excel en el siguiente link.

 

Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.

Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores. 

Salir de la versión móvil