☰

¿Cómo obtener el valor de la fila más a la derecha en Excel?

tag-icon • tag-icon microsoft-excel
¿Cómo obtener el valor de la fila más a la derecha en Excel?

Necesito mostrar el valor de la celda que no está en blanco más a la derecha de cada fila en una matriz. ¿Cómo se puede lograr esto en Excel?

En esta tabla de ejemplo, la columna [Actual] tiene el resultado deseado:

+---------+----------+---------+----------+
|  2016   |   2017   |  2018   | Current  |
+---------+----------+---------+----------+
|     700 |          |   200   |     200  |
|         |          |         |          |
|         |     450  |         |     450  |
|         |          |  2,700  |   2,700  |
|         |          |         |          |
|  42,350 |  71,500  |         |  71,500  |
|  2,660  |          |         |   2,660  |
|         |   1,100  |         |   1,100  |
|         |          |         |          |
|    470  |          |         |     470  |
+---------+----------+---------+----------+

Las variaciones sobre el tema serían el valor más a la izquierda, más arriba y más abajo; o valor mayor quenorte, etc. Excel de escritorio de Office 2016 si la versión es relevante.

Respuesta1

ingrese la descripción de la imagen aquí

  • Ingrese esta fórmula E2y rellénela.

=LOOKUP(2,1/(A2:C2<>""),A2:C2)

Cómo funciona:

  • La fórmula reconoce que el valor de búsqueda 2 es deliberadamente mayor que cualquier valor que aparecerá en el vector de búsqueda.
  • La expresión A2:C2<>""devuelve una matriz de valores Truey False.
  • 1luego se divide por esta matriz y crea una nueva matriz compuesta de errores de unos o de división por cero (#DIV/0!): {1,0,1,...}.
  • Esta matriz es el vector de búsqueda.
  • Cuando la fórmula no puede encontrar el valor de búsqueda, entonces
    Lookupcoincide con el siguiente valor más pequeño.
  • En este caso, el valor de búsqueda es 2, pero el valor más grande en la matriz de búsqueda es 1, por lo que la búsqueda coincidirá con el último 1en la matriz.
  • LOOKUP devuelve el valor correspondiente en el vector de resultados, que es el valor en la misma posición.

:Editado:

  • Para Google Sheet esta es la fórmula a utilizar:

    =(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

  • Terminarlo conCtrl+Mayús+Entrar, la fórmula se verá así,

    =ArrayFormula(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

Respuesta2

Aunque ya existen múltiples soluciones para este problema, esta es mi preferida, para mí es la más cercana al pensamiento natural:

=INDEX(A2:C2,MAX(IF(A2:C2="","",COLUMN(A2:C2))))- Esta es una fórmula matricial, así que presione CTRL+ SHIFT+ ENTERdespués de escribirla.

ingrese la descripción de la imagen aquí

Cómo funciona:

  • IF(A2:C2="","",COLUMN(A2:C2))- para cada celda de la fila devuelve una cadena vacía si la celda está vacía y el número de columna en caso contrario
  • MAX( ... )- selecciona el número de columna más alto devuelto
  • =INDEX(A2:C2, ... )- selecciona la celda de la fila según el número de columna más alto

Advertencia: funciona correctamente solo si su rango comienza desde la primera columna; de lo contrario, debe compensar el cambio, por ejemplo, para un rango que comienza desde la columna C:
=INDEX(C2:X2,MAX(IF(C2:X2="","",COLUMN(C2:X2)))-2)

Respuesta3

Supongamos que su tabla está dispuesta en C2:F12, con la fila del encabezado como la fila 2 y la columna de resumen como la F. Coloque la siguiente fórmula en F3 y cópiela.

=IFERROR(INDEX(3:3,AGGREGATE(14,6,column($C3:$E3)/($C3:$E3<>""),1)),"")

POS

NOTA:

  • AGREGAR realiza operaciones de matriz con las opciones de fórmula 14 y 15. Como resultado, no utilice referencias completas de columnas/filas dentro de la función AGREGAR, ya que puede terminar atascando su sistema o bloqueándolo con la cantidad de cálculos que se realizarán. Usar referencias de columnas completas fuera de funciones de tipo matriz está bien. tenga en cuenta que se utiliza 3:3 para ÍNDICE.

  • Al insertar una nueva columna, si se selecciona la columna F y se realiza la inserción, deberá actualizar la fórmula en F para que C3:F3 sea el nuevo rango. Si tiene seleccionada la columna E e inserta una nueva columna, el rango se actualizará automáticamente pero ahora sus datos están en la columna incorrecta. Si dejó la columna F en blanco, colocó las fórmulas en la columna G y usó C3:F3 como rango dentro de AGREGAR, en el futuro podría seleccionar la columna F para insertar y sus fórmulas se actualizarán y podrá ingresar nuevos datos en F. Aún tendría una columna en blanco a la derecha para que la selección en el próximo año repita el proceso.

Respuesta4

Otro enfoque, poco elegante, más brutal, pero fácilmente comprensible es usar TEXTJOIN(), ahora que lo tenemos.

Usando A2:C2 para la primera fila, coloque lo siguiente en D2, luego copie y pegue. O Rellenar, o... ya entiendes la idea:

Paracadena de unión de textoa continuación, utilice la función TEXTJOIN() para concatenar todo el rango de celdas que desea examinar. Utilice "VERDADERO" para omitir espacios en blanco para acortar la cadena y, como delimitador, utilice un carácter que, de manera realista, NUNCA aparecerá en sus datos. Utilizo "Ŧ" a continuación (y para el carácter sustituir el último por "Ų"). Usar comas como se hace a menudo con TEXTJOIN() y sus parientes puede causar problemas.

=RIGHT( Textjoin string,
LEN( Textjoin string ) -
FIND( "Ų", SUBSTITUTE( Textjoin string, "Ŧ", "Ų",
LEN( Textjoin string **with** delimiter ) - LEN( Textjoin string **without** delimiter )
)))

y es más fácil de entender. SUBSTITUTE() puede hacer su trabajo comenzando en unInstancia #lo que le permitiría encontrar el último uso de su delimitador enCadena de unión de texto con delimitador. En la última línea, encuentra el LEN() de la cadena Textjoin con y sin el delimitador y encuentra la diferencia por resta. Ese es el número de delimitadores y por lo tanto elInstancia #necesitas.

En la penúltima línea, sustituye esa instancia por un carácter diferente y luego usa FIND() para obtener su posición en la cadena.

La segunda línea resta esa posición del LEN() general de la cadena para saber cuántos caracteres la siguen. Eso te indica cuántos caracteres debes quitar del lado derecho de la cadena que creaste.

La primera línea hace precisamente eso, dejándote con el contenido de la última celda que estuvo en el rango.

La longitud de la cadena que utilizará Excel varía según la función, algunas en el rango de 6 a 7 000, por ejemplo, otras más como 32 000. Con eso en mente (es por eso que especifica "VERDADERO"), se puede hacer un rango ENORME en lugar de A2:C2.

Observe que entonces está trabajando con la cadena unida, no con las celdas, así que:

  1. Nunca tendrás que buscar direcciones de celulares, etc.
  2. De hecho, puede usarlo en un rango compuesto por "subrangos" unidos y rangos compuestos por celdas REALMENTE separadas. Las gamas discontinuas sois vosotros amigos y aliados.

Debido a la forma en que los datos existen dentro de las piezas que Excel evalúa dentro de una fórmula, dividir los fragmentos en rangos con nombre puede causar problemas, o no, ya que los resultados provisionales que Excel crea y utiliza al evaluar la fórmula PUEDEN tener una forma diferente a la resultado final que presenta un rango con nombre, a veces no se pueden usar rangos con nombre en piezas para diseñar la lógica de una fórmula para facilitar el futuro. Pero nada de lo anterior presenta ese problema, por lo que puede crear rangos con nombre para, digamos, TEXTJOIN e ingresar el resto de forma nativa para que cualquiera que haga clic en la celda pueda ver la lógica. O divida las piezas en algo lógico como "Número de instancia" (rango con nombre) para que se lea aún más fácilmente. Créelo y luego vuelque todo en un rango con nombre. O no se moleste en absoluto con los rangos con nombre.

Como digo, poco elegante. Más larga que algunas soluciones, pero no realmente "bruta" como son algunas cosas. No hay columnas de ayuda u otras cosas que la gente a menudo no puede usar. O no lo hará. Sin fórmulas {matriz}.

(Y puede usar los rangos discontinuos cuando sea necesario). El enfoque también puede tomar una pila de texto y datos que un motor de informes genera en PDF y que luego se extrae a Excel pero se divide de manera diferente en celdas para cada conjunto relacionado (es decir, información sobre 10 clientes). , cada uno configurado en un bloque de 10 columnas por 13 filas, pero la dirección de uno está en la celda 4,6 y en la celda 3,8 para otro, pero sigue el mismo flujo, solo llena diferentes celdas cuando se importa) y convirtiéndolo en un una sola cuerda, le permite buscar las piezas mediante fórmulas. Al menos a menudo. O tome un bloque de celdas y vea si aparece un poco de datos en algún lugar dentro de ellas usando funciones, no macros o matrices o una celda auxiliar para cada una en el bloque.

información relacionada