¿Cómo buscar y devolver el penúltimo valor de una fila o columna específica en Excel?
Al trabajar con hojas de cálculo grandes en Excel, a menudo no basta con identificar el último valor de una fila o columna: es necesario obtener específicamente el penúltimo. Por ejemplo, como se muestra en la siguiente captura de pantalla, dada la tabla del rango A1:E16, es posible que necesite recuperar el penúltimo valor de la sexta fila o de la columna B. Esta necesidad surge con frecuencia al realizar un seguimiento de cambios recientes, supervisar datos de series temporales o analizar las entradas más recientes que no sean las actuales en un conjunto de datos. En comparación con la tarea sencilla de encontrar el último valor, localizar el penúltimo puede resultar menos directo, especialmente cuando los datos contienen celdas vacías o se actualizan con frecuencia. Este artículo le ofrece soluciones claras y paso a paso para abordar este reto, optimizando su flujo de trabajo y ayudándole a evitar errores comunes.
- Buscar y devolver el penúltimo valor de una fila o columna determinada mediante fórmulas
- Código VBA – Usar una macro para encontrar el penúltimo valor en una fila o columna especificada
- Otros métodos integrados de Excel – Filtrar todas las celdas en blanco e identificar manualmente el penúltimo valor
Buscar y devolver el penúltimo valor de una fila o columna determinada mediante fórmulas
Como se muestra en la captura de pantalla anterior, cuando necesite buscar y devolver el penúltimo valor ya sea en la sexta fila o en la columna B dentro del rango A1:E16, las fórmulas de Excel ofrecen una solución dinámica y eficaz. Son ideales en escenarios donde los datos se actualizan con frecuencia o la posición del penúltimo valor puede cambiar debido a adiciones o eliminaciones. A diferencia de los métodos manuales, las fórmulas garantizan que el resultado se ajuste automáticamente a la última entrada de datos.
Buscar y devolver el penúltimo valor en la columna B
1. Seleccione una celda en blanco donde quiera mostrar el penúltimo valor. Introduzca la siguiente fórmula matricial en la Barra de fórmulas y, a continuación, pulse Ctrl + Mayús + Intro (en versiones anteriores de Excel) para confirmarla como fórmula matricial. En Excel 365 o Excel 2021, basta con pulsar Intro, ya que las fórmulas matriciales se gestionan automáticamente.
=INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2))
Nota: En esta fórmula, B:B hace referencia a la columna B. Si necesita el penúltimo valor en otra columna, simplemente sustituya B:Bpor la referencia de su columna deseada (por ejemplo, cámbiela a)C:C para la columna C). Este método funciona en rangos con o sin celdas vacías, pero si hay valores ocultos o datos filtrados, tenga en cuenta posibles discrepancias. Compruebe siempre su rango de datos si no ve el valor esperado.
Buscar y devolver el penúltimo valor en la fila 6
Seleccione una celda en blanco para mostrar el resultado de la sexta fila. A continuación, introduzca la siguiente fórmula en la Barra de fórmulas y pulse Intro para confirmarla.
=OFFSET($A$6,0, COUNTA(6:6)-2,1,1)
Nota: En la fórmula anterior, $A$6 es la primera celda de la fila 6, y 6:日晚间designa la fila completa 6. Ajuste estas referencias si necesita apuntar a otras filas. La fórmula cuenta dinámicamente el número de celdas no vacías en la fila 6 y luego realiza un desplazamiento desde la celda inicial para localizar la penúltima celda rellena. Si su fila incluye fórmulas que devuelven cadenas vacías ()""), la función CONTARA podría contarlas igualmente como no vacías, lo que podría afectar al resultado. En rangos con una combinación de constantes y fórmulas, verifique siempre los resultados para evitar errores.
Consejos para usar fórmulas:
- En conjuntos de datos grandes, el uso de referencias a columnas completas (p. ej.,)
B:B) puede afectar el rendimiento. Si es posible, limita el rango únicamente al área que necesites (p. ej.,B1:B100). - Si los datos incluyen filas ocultas o están filtrados, las fórmulas seguirán contando las celdas ocultas y filtradas. Para datos filtrados, considere usar funciones especiales de subtotal o columnas auxiliares.
- Si todos los valores de la fila o columna objetivo están en blanco, estas fórmulas podrían generar un error o un resultado inesperado; asegúrese de que sus datos incluyan al menos dos valores no vacíos.
Código VBA – Usar una macro para encontrar el penúltimo valor en una fila o columna especificada
Para usuarios que necesitan con frecuencia encontrar el penúltimo valor en rangos variables o extensos, automatizar este proceso mediante una macro de VBA puede ahorrar una cantidad considerable de tiempo, especialmente al trabajar con tablas dinámicas o estructuras complejas. Las macros resultan particularmente útiles cuando el tamaño del conjunto de datos varía y desea evitar actualizar fórmulas manualmente una y otra vez, o cuando necesita extraer valores desde múltiples posiciones distintas aplicando el mismo método. A continuación, encontrará una solución en código VBA que le pedirá la fila o columna objetivo y devolverá automáticamente el penúltimo valor, eliminando así cualquier conteo o ajuste manual.
1. Vaya a Herramientas para desarrolladores en la Cinta de opciones de Excel, haga clic en Visual Basic para abrir el editor de VBA. En el editor, haga clic en Insertar > Módulo. A continuación, pegue el siguiente código VBA en el módulo recién creado:
Sub GetSecondToLastValue()
Dim rng As Range
Dim arr As Variant
Dim values As Collection
Dim i As Long
Dim secondLast As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the row or column range to analyze", xTitleId, Selection.Address, Type:=8)
If rng Is Nothing Then Exit Sub
arr = rng.Value
Set values = New Collection
If rng.Rows.Count = 1 Then
For i = 1 To rng.Columns.Count
If arr(1, i) <> "" Then
values.Add arr(1, i)
End If
Next i
ElseIf rng.Columns.Count = 1 Then
For i = 1 To rng.Rows.Count
If arr(i, 1) <> "" Then
values.Add arr(i, 1)
End If
Next i
Else
MsgBox "Please select a single row or single column range.", vbExclamation
Exit Sub
End If
If values.Count < 2 Then
MsgBox "There are less than two non-blank values in the selected range.", vbInformation
Exit Sub
End If
secondLast = values(values.Count - 1)
MsgBox "The second-to-last value is: " & secondLast, vbInformation
End Sub2. Tras introducir el código, vuelva a Excel y ejecute la macro haciendo clic en el botón 
Ejecutar, pulsando Alt + F8 y seleccionando GetSecondToLastValue en la lista. A continuación, se le pedirá que seleccione un rango de una sola fila o columna (por ejemplo, B1:B16 para una columna o A6:E6 para una fila). Tras confirmar su selección, la macro mostrará el penúltimo valor en un cuadro de diálogo.
Notas:
- Esta macro cuenta únicamente las celdas no vacías, omitiendo automáticamente las celdas vacías dentro de la fila o columna seleccionada.
- Si su selección contiene menos de dos valores no vacíos, recibirá un mensaje de advertencia y no se devolverá ningún valor.
- La macro está diseñada para selecciones de una sola fila o una sola columna. Si selecciona varias filas y columnas a la vez, le pedirá que ajuste su selección.
- Para automatizarlo aún más, puede modificar el código para que copie el resultado directamente en una celda específica de la hoja de cálculo, en lugar de mostrarlo en un cuadro de mensaje.
Esta solución VBA resulta especialmente ventajosa para usuarios que trabajan con tablas dinámicas o conjuntos de datos en constante cambio, ya que minimiza errores manuales y automatiza tareas repetitivas.
Otros métodos integrados de Excel – Filtrar todas las celdas en blanco e identificar manualmente el penúltimo valor
Aunque las fórmulas y las macros ofrecen formas automatizadas de encontrar el penúltimo valor, a veces puede preferir un enfoque visual rápido, especialmente con datos irregulares o no contiguos, o cuando solo necesita verificar un valor ocasionalmente. Las herramientas de filtrado integradas de Excel le permiten ocultar temporalmente las celdas en blanco o irrelevantes, facilitando así la identificación de la entrada deseada sin necesidad de editar ni escribir fórmulas.
Método de filtrado:
- Seleccione el rango de datos correspondiente a su columna o fila: para columnas, elija las celdas B1:B16; para filas, seleccione A6:E6 en su hoja de cálculo.
- En la Cinta de opciones de Excel, haga clic en Datos > Filtrar para activar las flechas de filtro. A continuación, en la cabecera de la columna o junto a su rango seleccionado, haga clic en el menú desplegable del filtro.
- Desmarque (En blanco) para ocultar las celdas vacías. Ahora, la visualización de los datos muestra únicamente los valores no vacíos.
- En una columna, desplácese hasta el final de la lista filtrada y observe el penúltimo valor. En una fila (tras aplicar el filtro, si es posible), cuente desde la derecha para identificar la penúltima celda no vacía.
Notas y consejos:
- El filtrado es la opción más rápida para listas pequeñas o medianas, o cuando necesite confirmar visualmente la ubicación de los valores.
- Si su conjunto de datos es muy grande, el filtrado podría ralentizar el rendimiento y no es ideal para automatización ni para usos repetitivos.
- Cuando aplique filtros, asegúrese de estar visualizando el contexto correcto, ya que las listas filtradas pueden omitir filas o columnas ocultas.
- Para quitar los filtros, haga clic en Borrar en la pestaña Datos.
Este método no es ideal para análisis dinámicos continuos ni para conjuntos de datos grandes, pero sí ofrece una comprobación manual y transparente de la penúltima entrada no vacía, lo que resulta útil para verificaciones rápidas o la resolución ágil de problemas.
Artículos relacionados:
- ¿Cómo encontrar la posición del primer o último número en una cadena de texto en Excel?
- ¿Cómo encontrar el primer o último viernes de cada mes en Excel?
- ¿Cómo realizar una búsqueda vertical (BUSCARV) en Excel para encontrar la primera, segunda o enésima coincidencia?
- ¿Cómo encontrar el valor que aparece con mayor frecuencia en un rango de Excel?
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos