Cómo indexar y ordenar valores usando la columna de ayuda en Excel.

En este artículo, aprenderemos cómo indexar y ordenar valores usando la columna de ayuda en Excel.

Guión:

A veces se trabaja con datos descortés. Necesitamos obtener la versión más corta como datos requeridos de los datos de la tabla basados ​​en la columna de ayuda. Aquí primero entenderemos cómo obtener la columna auxiliar y luego obtendremos los pequeños datos requeridos ordenados en función de los datos de la tabla.

¿Cómo indexar valores en rango?

Para este artículo, se nos pedirá que usemos la función CONTAR.SI. Ahora haremos una fórmula a partir de la función. Aquí se dan valores mixtos de texto y números en un rango. Necesitamos indexarlos en orden ascendente primero numéricamente y luego alfabéticamente.

Fórmula genérica: -

= CONTAR.SI (lista, "<=" & valor_f) + (CONTAR (lista) * ESTEXTO (valor_f))

lista: lista de números y texto

f_value: primer valor del rango

Explicación:

1. La función CONTAR.SI cuenta el número de valores en la lista devuelta
2. La función COUNT obtiene el recuento de números en el rango.
3. La función ISTEXT comprueba si el valor en la celda seleccionada es texto o no. Devuelve VERDADERO para texto y FALSO para números.

Ejemplo:

Todos estos pueden resultar confusos de entender. Entendamos cómo obtener la columna auxiliar usando la fórmula explicada. Aquí la columna de ayuda se basará en los valores de clasificación en orden (primero números y luego alfabéticamente). Para ello, utilizaremos la fórmula para obtener el índice o la columna de clasificación como columna auxiliar para los datos de gastos que se muestran a continuación.

Aquí para encontrar los valores en rango. Para fines de cálculo, usamos el rango con nombre para la matriz fija D5: D12 como gasto.

Usa la fórmula:

= CONTAR.SI (gasto, "<=" & D5) + (CONTAR (gasto) * ISTEXT (D5))

La fórmula se parece a la que se muestra en la instantánea anterior. El valor y la matriz se dan como rango con nombre y referencia de celda en la fórmula.

Como puede ver, el índice de la primera celda del rango resulta ser 5, lo que significa que si colocamos los gastos en orden de clasificación, Electricidad se colocará en la 5ª posición. Ahora copie la fórmula en otras celdas usando la opción de arrastrar hacia abajo o usando la tecla de método abreviado Ctrl + D como se muestra a continuación para obtener el Índice o la clasificación para el resto de los valores.

Como puede ver, ahora tenemos una lista de índice o rango que clasifica los gastos. Ahora usaremos esta columna de rango como columna auxiliar para obtener la versión más corta de la tabla con el mismo orden en la nueva lista o tabla.

¿Cómo ordenar valores usando la columna auxiliar en Excel?

El número de clasificación en Excel que usa la columna auxiliar debe tener números de índice y el rango requerido para ordenar. La columna auxiliar puede ser de cualquier orden requerida. Por ejemplo, si necesita obtener la lista en el orden aleatorio requerido, simplemente coloque el rango correspondiente al valor en el índice o en la columna de rango que funcionará como columna auxiliar.

1. Función INDICE
2. Función COINCIDIR
3. Función FILAS

Ahora crearemos una fórmula usando las funciones anteriores. La función COINCIDIR devolverá el índice de la coincidencia más baja del rango. La función INDICE toma el índice de fila como argumento y devuelve sus correspondientes resultados requeridos. Esta función encuentra el

Fórmula genérica:

= ÍNDICE (matriz_de_retorno, COINCIDIR (FILAS (referencia_relativa), Índice, 0))

return_array: matriz para devolver el valor de

Referencia relativa: genera un orden ascendente en la fórmula. Puede usarse con cualquier arreglo

Índice: matriz de índice de la tabla

0: coincidencia exacta

Explicación:

1. FILAS (referencia_relativa) devuelve un valor según la longitud de la fórmula extendida. Si se aplica en la primera celda, será una, luego la segunda y continuar hasta que se extienda.
2. La función COINCIDIR hace coincidir el índice con el valor de la fila para obtenerlos en orden ascendente utilizando la función FILAS.
3. La función INDICE devuelve el índice coincidente con el valor correspondiente.

Ejemplo:

Todos estos pueden resultar confusos de entender. Entonces, entendamos esta fórmula ejecutándola en el ejemplo que se muestra a continuación. Aquí clasificamos los datos en orden aleatorio para obtener los primeros tres valores basados ​​en la columna Índice. Utilice la fórmula para obtener el primer valor de la tabla corta requerida.

Usa la fórmula:

= ÍNDICE (B5: B13, COINCIDIR (FILAS (D5: D5), D5: D13,0))

La fórmula se parece a la que se muestra en la instantánea anterior. La matriz de valores se da como rango con nombre y referencia de celda.

Como puede ver, el primer valor es el número del rango que resulta ser "Leche". El índice correspondiente es 1. Ahora copie la fórmula en otras celdas usando la opción de arrastrar hacia abajo o usando la tecla de atajo Ctrl + D como se muestra a continuación para obtener el resto de los valores.

Como puede ver, los valores están indexados en orden ascendente. Obtuvimos todos los valores, ya que también demuestra que la fórmula funciona bien. Podemos extraer los valores numéricos usando la fórmula de la misma fórmula.

Como puede ver, tenemos los valores más cortos y ordenados de la tabla usando la columna auxiliar que está indexada en consecuencia.

Aquí hay algunas notas de observación que se muestran a continuación.

Notas:

1. La fórmula solo funciona con números y texto.
2. La fórmula ignora el valor del texto al comparar números e ignora los números cuando coincide con valores de texto.
3. La columna auxiliar puede ser cualquier tipo de orden de columna requerido.

Espero que este artículo sobre cómo indexar y ordenar valores con la columna auxiliar en Excel sea explicativo. Explore más artículos sobre fórmulas de búsqueda en Excel aquí. Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook. Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico

Cómo utilizar la función SUMPRODUCT en Excel: Devuelve la SUMA después de la multiplicación de valores en varias matrices en Excel.

SUM si la fecha está entre : Devuelve la SUMA de valores entre fechas o períodos dados en Excel.

Suma si la fecha es mayor que la fecha dada: Devuelve la SUMA de los valores posteriores a la fecha o período especificado en Excel.

2 formas de sumar por mes en Excel: Devuelve la SUMA de valores dentro de un mes específico dado en Excel.

Cómo sumar varias columnas con condición: Devuelve la SUMA de valores en varias columnas con condición en Excel

Cómo usar comodines en Excel : Cuente las celdas que coinciden con las frases usando los comodines en Excel

articulos populares

50 atajos de Excel para aumentar su productividad : Acelere su tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.

Cómo usar tLa función BUSCARV en Excel : Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.

Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.

Cómo usar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.