Come indicizzare e ordinare i valori utilizzando la colonna helper in Excel.

Anonim

In questo articolo, impareremo come indicizzare e ordinare i valori utilizzando la colonna helper in Excel.

Scenario:

A volte lavorare con dati scortesi. Dobbiamo ottenere la versione più breve come dati richiesti dai dati della tabella in base alla colonna helper. Qui prima capiremo come ottenere la colonna helper e quindi ottenere i piccoli dati richiesti ordinati in base ai dati della tabella.

Come indicizzare i valori nell'intervallo?

Per questo articolo ci verrà richiesto di utilizzare la funzione CONTA.SE. Ora faremo una formula fuori dalla funzione. Qui dato testo e numero di valori misti in un intervallo. Dobbiamo indicizzarli in ordine crescente prima numericamente poi alfabeticamente.

Formula generica:-

= CONTA.SE ( lista , "<=" & valore_f) + ( CONTA.SE (lista) * TESTO (valore_f) )

list : elenco di numeri e testo

f_value: primo valore dell'intervallo

Spiegazione:

  1. La funzione CONTA.SE conta il numero di valori nell'elenco restituito
  2. La funzione COUNT ottiene il conteggio dei numeri nell'intervallo.
  3. La funzione ISTEXT controlla se il valore nella cella selezionata è testo o meno. Restituisce VERO per il testo e FALSO per i numeri.

Esempio:

Tutti questi potrebbero essere fonte di confusione da capire. Capiamo come ottenere la colonna helper usando la formula spiegata. Qui la colonna helper da basare sui valori di ordinamento in ordine (prima numeri e poi in ordine alfabetico). Per questo utilizzeremo la formula per ottenere la colonna Indice o classifica come colonna di supporto per i dati di spesa mostrati di seguito.

Qui per trovare i valori nell'intervallo. Ai fini del calcolo usiamo l'intervallo denominato per l'array fisso D5:D12 come spesa.

Usa la formula:

= CONTA.SE ( spesa , "<=" & D5 ) + ( CONTEGGIO (spesa) * ISTEXT (D5) )


La formula è simile a quella mostrata nell'istantanea sopra. Il valore e la matrice vengono forniti come intervallo denominato e riferimento di cella nella formula.


Come puoi vedere, l'indice della prima cella nell'intervallo risulta essere 5, il che significa che se posizioniamo le spese in ordine, Elettricità verrà piazzato al 5° posto. Ora copia la formula in altre celle utilizzando l'opzione di trascinamento verso il basso o utilizzando il tasto di scelta rapida Ctrl + D come mostrato di seguito per ottenere l'indice o classificare il resto dei valori.

Come puoi vedere, ora abbiamo un elenco di indice o rango che è il modo di ordinare le spese. Ora useremo questa colonna Rank come colonna helper in modo da ottenere la versione più corta della tabella con lo stesso ordine nella nuova lista o tabella.

Come ordinare i valori utilizzando la colonna helper in Excel?

Il numero di ordinamento in Excel utilizzando la colonna helper deve avere numeri di indice e l'intervallo richiesto per l'ordinamento. La colonna Helper può essere qualsiasi ordine richiesto. Ad esempio, se è necessario ottenere l'elenco nell'ordine casuale richiesto, è sufficiente posizionare il rango corrispondente al valore nell'indice o nella colonna del rango che funzionerà come colonna di supporto.

  1. Funzione INDICE
  2. Funzione CONFRONTA
  3. Funzione RIGHE

Ora creeremo una formula usando le funzioni sopra. La funzione CONFRONTA restituirà l'indice della corrispondenza più bassa dell'intervallo. La funzione INDEX accetta l'indice di riga come argomento e restituisce i risultati richiesti corrispondenti. Questa funzione trova il

Formula generica:

= INDICE ( matrice_ritorno , CONFRONTA ( RIGHE (riferimento_relativo) , Indice , 0 ) )

return_array: array da cui restituire il valore

Riferimento relativo: genera un ordine crescente nella formula. Può essere utilizzato con qualsiasi array

Indice: array indice della tabella

0: corrispondenza esatta

Spiegazione:

  1. ROWS (relative_reference) restituisce un valore in base alla lunghezza della formula estesa. Se applicato nella prima cella, sarà uno, quindi il secondo e continuerà fino a quando non si estenderà.
  2. La funzione MATCH abbina l'indice con il valore della riga per ottenerli in ordine crescente utilizzando la funzione ROWS.
  3. La funzione INDEX restituisce l'indice corrispondente con il valore corrispondente.

Esempio:

Tutti questi potrebbero essere fonte di confusione da capire. Quindi, comprendiamo questa formula eseguendola nell'esempio mostrato di seguito. Qui abbiamo classificato i dati in ordine casuale per ottenere i primi tre valori in base alla colonna Indice. Usa la formula per ottenere il primo valore della short table richiesta.

Usa la formula:

= INDICE ( B5:B13 , CORRISPONDENZA ( RIGHE (D5:D5),D5:D13,0))

La formula è simile a quella mostrata nell'istantanea sopra. L'array di valori viene fornito come intervallo denominato e riferimento di cella.

Come puoi vedere, il primo valore è il numero dall'intervallo che risulta essere "Latte" l'indice corrispondente è 1. Ora copia la formula in altre celle usando l'opzione di trascinamento verso il basso o usando il tasto di scelta rapida Ctrl + D come mostrato di seguito per ottenere il resto dei valori.

Come puoi vedere i valori sono indicizzati in ordine crescente. Abbiamo ottenuto tutti i valori in quanto dimostra anche che la formula funziona bene. Possiamo estrarre i valori numerici usando la formula la stessa formula.

Come puoi vedere, abbiamo i valori più brevi e ordinati dalla tabella usando la colonna helper che è indicizzata di conseguenza.

Di seguito sono riportate alcune note osservative.

Appunti:

  1. La formula funziona solo con numeri e testo.
  2. La formula ignora il valore del testo durante il confronto dei numeri e ignora i numeri quando corrisponde ai valori del testo.
  3. La colonna di supporto può essere qualsiasi tipo di ordine di colonna richiesto.

Spero che questo articolo su Come indicizzare e ordinare i valori con la colonna helper in Excel sia esplicativo. Esplora altri articoli sulle formule di ricerca in Excel qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica

Come utilizzare la funzione SUMPRODUCT in Excel: restituisce la SOMMA dopo la moltiplicazione dei valori in più matrici in Excel.

SOMMA se la data è compresa tra : Restituisce la SOMMA dei valori tra date o periodi specificati in Excel.

Somma se la data è maggiore della data indicata: Restituisce la SOMMA dei valori dopo la data o il periodo specificati in Excel.

2 modi per sommare per mese in Excel: Restituisce la SOMMA dei valori all'interno di un determinato mese specifico in Excel.

Come sommare più colonne con condizione: Restituisce la SOMMA dei valori su più colonne con condizione in excel

Come usare i caratteri jolly in Excel : Conta le celle che corrispondono alle frasi usando i caratteri jolly in Excel

Articoli popolari

50 scorciatoie Excel per aumentare la produttività : Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.

Come usare tla funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.

Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.

Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.