Come alfabetizzare una tabella in excel. Elenco a discesa collegato in Excel in ordine alfabetico

Come creare un elenco a discesaExcel in ordine alfabetico selezionare un cognome per lettera dell'alfabeto? Quando nell'elenco a discesa sono presenti molti cognomi, è conveniente creare un alfabeto nell'elenco a discesa, quindi, selezionando la lettera desiderata, selezionare il cognome. Per questo devi farlo elenchi a discesa di Excel collegati.
Ad esempio, sul primo foglio abbiamo la tabella principale, nella quale inseriremo un elenco a tendina con i cognomi.
Sul secondo foglio eccellere libri Facciamo una lista di nomi. Ordina questo elenco in ordine alfabetico. L'elenco è così.
Come distinguere grande lista con cognomi duplicati un elenco senza ripetizioni per un elenco a discesa, vedere l'articolo "Creazione di un elenco in Excel senza ripetizioni".
Ora diamo un nome a questa lista. Come assegnare un nome a un intervallo, leggi l'articolo "Intervallo in Excel". Abbiamo chiamato la gamma - "Cognomi".
Affinché i limiti dell'intervallo cambino automaticamente durante l'aggiunta o la sottrazione di cognomi, è necessario renderlo dinamico. Vedere l'articolo "Ridimensionare automaticamente una tabella di Excel".
Ora dobbiamo creare un elenco di lettere che iniziano con i cognomi nel nostro elenco.
Come scrivere l'alfabeto in Excel con una formula.
Innanzitutto, nella colonna C, scriviamo il solito alfabeto. Questo può essere fatto rapidamente con una formula. Nella cella C, scrivi la formula.
=CAR(192+STRINGA(Z1)-1)
192 è il codice per la lettera A. Copia la formula in basso nella colonna fino alla lettera Y. L'alfabeto è pronto.
Ora nella colonna successiva devi scrivere solo quelle lettere con cui iniziano i nomi nel nostro elenco. Qui ci aiuteràFormula matrice di Excel. Nella cella D2, abbiamo scritto una formula così lunga.
=SEERRORE(INDICE($C$2:$C$33,PICCOLO(SE(CONT.SE
(Cognomi;$C$2:$C$33&"*")>0;STRING($C$2:$C$33);"");
STRINGA(Z1))-ROW($C$1));"")
Questa è una formula di matrice, p.e. premiamo non solo "Invio", ma la combinazione di tasti "Maiusc" + "Ctrl" + "Invio". La formula sarà inserita bretelle. Trascina la formula lungo la colonna. Ce l'abbiamo fatta così.

Qualunque cosa. Ora nella cella D1 selezioniamo una lettera e nella cella E1 apparirà un elenco a discesa di cognomi solo per questa lettera. Per esempio. o

C'è una categoria Utenti di Excel, che funziona con libri che contengono, invece di 3 fogli standard, un numero molto maggiore di essi. Qualcuno in questi libri di stime, i cui numeri sono i nomi dei fogli, qualcuno ha atti di lavoro eseguiti da appaltatori, qualcuno ha il consumo di carburante dei taxi a rotta fissa, qualcuno ha un'assemblea di calcoli economici e qualcuno caratteristiche elettriche apparecchiature tecnologiche registrate giornalmente su Foglio separato(o generalmente ogni ora). Bene, se i fogli di un libro del genere vengono ordinati in anticipo almeno in qualche modo, ma in caso contrario? Anche una semplice ricerca del foglio desiderato nel libro diventa tutt'altro che un'attività divertente. Quindi voglio allineare i fogli in ordine alfabetico, ma in standard Eccellere non esiste una tale possibilità.

Vi presento un facile da usare componente aggiuntivo (macro) per Excel, che permette ordinare i fogli cartella di lavoro Excel sia ascendente che discendente . Se qualcuno si è imbattuto in un ordinamento non del tutto corretto, quando Foglio11 e non Foglio2 segue immediatamente Foglio1, potrebbero non preoccuparsi. In questo componente aggiuntivo, questo problema viene risolto, ordina i fogli con la stessa qualità, i cui nomi sono costituiti da lettere, numeri e una combinazione di lettere, numeri, spazi e simboli. E lo farà anche con fogli nascosti. Il componente aggiuntivo ordina correttamente e rapidamente i fogli con nomi costituiti solo da numeri, come 1, 2, 3, 4, 5, nonché fogli con nomi come 1-1-1, 1-1-2, 1-1 -3.

Il componente aggiuntivo viene lanciato da un pulsante visualizzato nel menu di Excel, quando premuto, l'utente vede una finestra di dialogo con la quale può scegliere come ordinare i fogli in ordine crescente o decrescente. C'è ancheordina i fogli per colore della scheda ed elenchi personalizzati.

È anche possibile. Il componente aggiuntivo è facile da installare e per coloro che non hanno ancora incontrato l'installazione dei componenti aggiuntivi in ​​Excel, c'è istruzioni passo passo. A mio parere questo degna alternativa trascinamento manuale dei fogli.

IN questo esempio Diamo un'occhiata al più veloce metodi efficaci ordinare i dati in Excel secondo vari criteri: in numero crescente o decrescente, per data dal vecchio al nuovo e dal nuovo al vecchio, in ordine alfabetico dalla A alla Z e viceversa ordine alfabetico.

Ordina le righe per colonna in Excel

La figura seguente mostra una selezione di informazioni dal database sui dipendenti dell'azienda:

Diciamo che dobbiamo ordinare la tabella rispetto alla colonna "Bonus" in ordine decrescente di importi. Per risolvere questo problema, utilizzeremo gli strumenti di ordinamento più semplici (di base) in Excel.

Sposta il cursore della tastiera su qualsiasi cella della colonna G (puoi anche andare all'intestazione della colonna G1). E quindi seleziona lo strumento: "HOME" - "Modifica" - "Ordina e filtra" - "Ordina decrescente".

Di conseguenza, i dati sono stati ordinati e organizzati in una determinata struttura:

Come si è scoperto, il dipendente "Eugene Prutkiy" ha ricevuto il premio più alto.

Attenzione! L'elenco da ordinare non deve contenere celle unite.

Nota. Lo strumento per l'ordinamento di base degli intervalli è disponibile anche nella scheda: "DATI" - "Ordina in ordine crescente/decrescente".

E integrato nei menu a discesa delle intestazioni delle tabelle in modalità filtro automatico o tabella intelligente:

Anche integrato menù contestuale, che appare quando fai clic fare clic con il tasto destro i topi:

Come rimuovere l'ordinamento in Excel

Se la tabella deve essere ripristinata nella sua forma originale, prima di ogni ordinamento creare una colonna di numerazione delle righe. In questo esempio, questa è la colonna A (n. p / p). Spesso è necessario riportare una tabella alla sua forma originale dopo aver eseguito numerosi e complessi ordinamenti di dati su di essa in base a colonne, criteri o condizioni differenti. Quindi è sufficiente ordinare per colonna con numeri di riga in modo che la tabella assuma la sua forma originale.

Come ordinare una colonna in Excel

A volte è necessario eseguire un ordinamento indipendente solo all'interno di una colonna di una tabella senza associare o modificare altre colonne. Ad esempio, la colonna B è "Nome". Questo problema può essere risolto altrettanto facilmente:

1. Seleziona l'intera colonna B facendo clic con il pulsante sinistro del mouse sull'intestazione della colonna del foglio.
2. Selezionare lo strumento: "HOME" - "Modifica" - "Ordina e filtra" - "Ordina decrescente".
3. Nella finestra di dialogo visualizzata "Dati rilevati in un intervallo non specificato", selezionare l'opzione: "ordina all'interno della selezione specificata". E fai clic sul pulsante Ordina.

Nota che i valori delle celle nelle colonne rimanenti rimangono invariati:

Questo metodo dovrebbe essere utilizzato solo come ausiliario.

Ordinamento su più colonne in Excel

Come impostare l'ordinamento in Excel? Supponiamo di voler ordinare i dipendenti per data di nascita (dal più vecchio al più recente) in base a ciascuna città (luogo di nascita). Per questo:

La tabella ha acquisito la seguente struttura:

Innanzitutto, i suoi valori sono ordinati rispetto ai nomi delle città in ordine alfabetico. Più in relazione a ciascuna città, le date di nascita sono distribuite dalla più antica alla più recente.

Se lavoriamo solo con un frammento di tabella in cui non ci sono intestazioni di colonna, ma solo un intervallo di celle con valori correnti, come mostrato nella figura seguente:

Quindi nella finestra di dialogo dello strumento Ordinamento personalizzato, dovresti disabilitare l'opzione "I miei dati hanno intestazioni di colonna". Di conseguenza, come criteri per ogni primo elenco a discesa della sezione "Colonna" di ogni livello, verranno utilizzate le intestazioni delle colonne del foglio di lavoro. foglio Excel(colonna A, colonna B, C, ecc.). E quando tutti i parametri vengono applicati, tutte le righe e le celle dell'intervallo originale (frammento di tabella) verranno incluse nell'ordinamento, che in questo caso è il risultato corretto.

Nota. Il pulsante per richiamare lo strumento per l'ordinamento personalizzato è disponibile anche nella scheda "DATI" - "Ordina".

Tabelle di dati da cui riceviamo dipendenti diversi nella maggior parte dei casi, non sono snelli ed è difficile lavorare con loro senza organizzazione. È necessario ordinare: valore del testo in ordine alfabetico, numerico in ordine crescente e date dal più vecchio al più nuovo. L'ordinamento manuale di tutte le colonne singolarmente non lo è soluzione migliore per questo compito. Soprattutto se ci sono più di mille colonne. Pertanto, è meglio utilizzare uno speciale integrato Strumento Excel- ordinamento personalizzato. Questo strumento ordina automaticamente i dati per più colonne contemporaneamente. È così flessibile che può essere applicato a quasi tutti i tipi con qualsiasi livello di complessità.

In questo articolo, ti mostrerò come ordinare i dati in Excel per più colonne, per intestazioni di colonna in ordine alfabetico e per valori in qualsiasi riga. Imparerai anche come ordinare i dati modi non standard quando l'ordinamento alfabetico o per valore numerico non è applicabile.

Penso che tutti sappiano come ordinare per colonna in ordine alfabetico o in ordine crescente/decrescente. Questo viene fatto con il clic di un pulsante. E IO(A-Z) e Z-A(Z-A) nella sezione La modifica scheda (Modifica). casa(Home) o nella sezione Ordina e filtra scheda (Ordina e filtra). Dati(Dati):

Tuttavia, l'ordinamento in Excel ha molte più opzioni e modalità operative personalizzabili che non sono così ovvie, ma possono essere molto convenienti:

Ordinamento su più colonne

Ti mostrerò come ordinare i dati in base a due o più colonne in Excel. Il funzionamento dello strumento è mostrato in Esempio di Excel 2010 - questa è la versione installata sul mio computer. Se stai utilizzando una versione diversa dell'applicazione, non dovresti avere problemi perché l'ordinamento funziona più o meno allo stesso modo in Excel 2007 ed Excel 2013. La differenza si vede solo nel colore finestre di dialogo e la forma dei bottoni. Quindi iniziamo...

Ordinare i dati per più colonne in Excel non è stato affatto difficile, giusto? Tuttavia, nella finestra di dialogo Ordinamento(Ordina) si trova in modo significativo più possibilità. Più avanti in questo articolo, ti mostrerò come ordinare per riga anziché per colonna e come ordinare i dati su un foglio di lavoro in ordine alfabetico per intestazioni di colonna. Imparerai anche come ordinare i dati in modi non standard quando l'ordinamento alfabetico o per valore numerico non è applicabile.

Ordina i dati in Excel per intestazioni di riga e colonna

Credo che il 90% delle volte Excel ordini i dati in base al valore in una o più colonne. Tuttavia, a volte non ci sono insiemi semplici dati da ordinare per riga (orizzontale), ovvero riordinare le colonne da sinistra a destra in base alle intestazioni o ai valori delle colonne in una determinata riga.

Di seguito è riportato un elenco di telecamere fornite dal rivenditore locale o scaricate da Internet. L'elenco contiene una varietà di caratteristiche, specifiche e prezzi e ha un aspetto simile a questo:

Dobbiamo ordinare questo elenco di telecamere in base ai parametri per noi più importanti. Ad esempio, prima di tutto, ordiniamo per nome del modello:

Come risultato dell'ordinamento, dovresti ottenere qualcosa del genere:

Nel nostro esempio, l'ordinamento per intestazioni di colonna non ha un valore pratico serio e viene eseguito solo per mostrarti come funziona. Allo stesso modo, possiamo ordinare il nostro elenco di telecamere in base alla riga che specifica dimensioni, risoluzione, tipo di sensore o qualsiasi altro parametro che riteniamo più importante. Facciamo un altro tipo, questa volta per prezzo.

Il nostro compito è ripetere i passaggi da 1 a 3 sopra descritti, quindi al passaggio 4, invece della riga 1 seleziona una riga 4 , che contiene i prezzi al dettaglio (prezzo al dettaglio). Come risultato dell'ordinamento, la tabella apparirà così:

Si noti che i dati sono stati ordinati non solo nella riga selezionata. Intere colonne vengono scambiate, ma i dati non vengono mescolati. In altre parole, lo screenshot qui sopra è un elenco di telecamere, ordinate dalla più economica alla più costosa.

Spero che ora sia chiaro come funziona l'ordinamento per riga in Excel. Ma cosa succede se i nostri dati devono essere ordinati in un ordine non alfabetico e non crescente/decrescente?

Ordinamento casuale (elenco personalizzato)

Se è necessario disporre i dati in un ordine speciale (non in ordine alfabetico), è possibile utilizzare gli elenchi personalizzati integrati in Excel o crearne di propri. Con elenchi personalizzabili integrati, puoi ordinare per giorni della settimana o mesi dell'anno, ad esempio. Microsoft Excel offre due tipi di liste pronte- con nomi abbreviati e completi.

Diciamo che abbiamo un elenco di faccende settimanali in casa e vogliamo ordinarle per giorno della settimana o per importanza.

Pronto! Ora le faccende domestiche sono ordinate per giorno della settimana:

Commento: Se prevedi di apportare modifiche a questi dati, tieni presente che i nuovi dati aggiunti o quelli esistenti modificati non verranno ordinati automaticamente. Per ripetere l'ordinamento, fare clic sul pulsante Ripetere Sezione (Riapplica). Ordina e filtra scheda (Ordina e filtra). Dati(Dati).

Come puoi vedere, ordinare i dati in Excel in base a un elenco personalizzato non è affatto un compito difficile. Un altro trucco che dobbiamo imparare è ordinare i dati nel nostro elenco personalizzato.

Ordina i dati in base al tuo elenco personalizzato

La nostra tabella ha una colonna priorità- indica le priorità dei compiti. Per usarlo per organizzare le tue attività settimanali dal più importante al meno importante, segui questi passaggi:

Ripetere i passaggi 1 e 2 dell'esempio precedente. Quando si apre la finestra di dialogo Elenchi(Elenchi personalizzati), nella colonna omonima a sinistra, fare clic su NUOVO ELENCO(NUOVA LISTA) e compilare valori desiderati campo Elementi della lista(Elenca voci). Inserisci con attenzione gli elementi della tua lista nell'ordine esatto in cui dovrebbero essere ordinati.

Clic Aggiungere(Aggiungi) e l'elenco che hai creato verrà aggiunto a quelli esistenti. Successivo clic ok.

Ecco come appare il nostro elenco di faccende domestiche, ordinate per importanza: