funzione CERCA ( ) , Versione inglese di SEARCH (), trova la prima occorrenza di una stringa di testo in un'altra stringa e restituisce la posizione iniziale della stringa trovata.

Sintassi della funzione

RICERCA(testo_ricerca;stringa_di ricerca; [posizione di partenza])

Testo_ricercato- il testo da trovare.

Visualizzato_String- il testo in cui cercare Testo_ricercato.

Posizione di partenza- la posizione del carattere nella stringa_di ricerca da cui deve iniziare la ricerca. Se l'argomento posizione di partenza viene omesso, si presume che il valore sia 1.

nell'argomento testo_ricerca può essere utilizzato - punto interrogativo (?) e asterisco (*). Il punto interrogativo corrisponde a qualsiasi carattere; un asterisco - qualsiasi sequenza di caratteri. Se hai bisogno di trovare un punto interrogativo o un asterisco nel testo, dovresti mettere una tilde (~) davanti a loro.

Se lookup_text non viene trovato, viene restituito il valore di errore #VALUE!.

La funzione SEARCH() non rispetta le lettere MAIUSCOLE. Per una ricerca con distinzione tra maiuscole e minuscole, utilizzare la funzione TROVA ().

Esempi di

Formula = CERCA ("a"; "Primo canale") restituirà 8, perché lettera aè in 8° posizione da sinistra.

Lascia entrare la cella la2 riga inserita Channel One è il migliore... Formula = RICERCA (CAR (32), A2) restituirà 7, perché il carattere spazio (codice 32) è in settima posizione.

Formula = CERCA ("# ??? #"; "SKU # 123 # ID") cercherà nella stringa " Articolo # 123 # ID"una sequenza di 5 caratteri che inizia e finisce con un segno #.

Per trovare la posizione della seconda occorrenza della lettera "a" nella stringa "mamma soap frame" usa la formula = CERCA ("a"; "mamma ha lavato il telaio"; CERCA ("a"; "mamma ha lavato il telaio") + 1). Per determinare se c'è una terza occorrenza della lettera "m" nella riga "mamma soap frame" usa la formula = IF (DLSTR (SOSTITUTO ("mum soap frame"; "m"; ""; 3)) = DLSTR ("mum soap frame"); "Nessuna terza occorrenza di m"; "C'è una terza occorrenza di m" )

Formula = CERCA ("tesoro?"; "Memo") restituirà 3, cioè la parola "memo" contiene una parola di 5 lettere, di cui le prime 4 Tesoro(iniziando con la terza lettera della parola rapporto).

Funzione TROVA () vs CERCA ()

La funzione FIND() fa distinzione tra maiuscole e minuscole e non accetta caratteri jolly. Per ricerche senza distinzione tra maiuscole e minuscole e per ricerche utilizzando caratteri jolly utilizzare la funzione CERCA ().

Comunicazione con le funzioni LEFT(), RIGHT() e MID()

La funzione SEARCH () può essere utilizzata insieme alle funzioni LEFT (), RIGHT () e MID ().

Ad esempio, in cella la2 contiene il cognome e il nome "Ivanov Ivan", quindi la formula = SINISTRA (A2, RICERCA (CAR (32), A2) -1) estrarrà il cognome, e = DESTRA (A2, DLSTR (A2) -SEARCH (CHAR (32), A2))- nome. Se è presente più di uno spazio tra il nome e il cognome, utilizzare la funzione TRIM() per far funzionare le formule precedenti.

Lo scopo principale del programma per ufficio Excel è eseguire calcoli. Il documento di questo programma (Book) può contenere molti fogli con lunghe tabelle piene di numeri, testo o formule. Una ricerca rapida automatizzata ti consente di trovare le celle richieste in esse.

Ricerca semplice

Per cercare un valore in una tabella di Excel, aprire l'elenco a discesa dello strumento Trova e sostituisci nella scheda Home e fare clic su Trova. Lo stesso effetto può essere ottenuto utilizzando la scorciatoia da tastiera Ctrl + F.

Nel caso più semplice, nella finestra "Trova e sostituisci" che appare, inserisci il valore desiderato e fai clic su "Trova tutto".

Come puoi vedere, i risultati della ricerca sono apparsi nella parte inferiore della finestra di dialogo. I valori trovati sono sottolineati in rosso nella tabella. Se invece di "Trova tutto" si fa clic su "Trova successivo", verrà cercata la prima cella con questo valore e, quando si fa nuovamente clic, verrà cercata la seconda.

La ricerca del testo viene eseguita allo stesso modo. In questo caso, il testo di ricerca viene digitato nella barra di ricerca.

Se i dati o il testo non vengono cercati nell'intera tabella Excel, è necessario selezionare prima l'area di ricerca.

Ricerca Avanzata

Supponiamo di voler trovare tutti i valori nell'intervallo da 3000 a 3999. In questo caso, digita 3 ??? nella barra di ricerca. Carta jolly "?" sostituisce qualsiasi altro.

Analizzando i risultati della ricerca, si può notare che, oltre ai 9 risultati corretti, il programma ha restituito anche quelli imprevisti, evidenziati in rosso. Sono associati alla presenza del numero 3 in una cella o formula.

Ci si può accontentare della maggior parte dei risultati ottenuti ignorando quelli sbagliati. Ma la funzione di ricerca in Excel 2010 è in grado di funzionare in modo molto più accurato. Per fare ciò, utilizzare lo strumento "Opzioni" nella finestra di dialogo.

Cliccando su "Opzioni", l'utente può effettuare una ricerca avanzata. Prima di tutto, prestiamo attenzione alla voce "Area di ricerca", in cui il valore "Formule" è impostato di default.

Ciò significa che la ricerca è stata eseguita, anche in quelle celle in cui non si trova il valore, ma la formula. La presenza del numero 3 in essi ha dato tre risultati errati. La selezione di Valori come area di ricerca cercherà solo dati e i risultati errati associati alle celle della formula scompariranno.

Per eliminare l'unico risultato errato rimasto nella prima riga, nella finestra di ricerca avanzata, seleziona la voce "Intera cella". Successivamente, il risultato della ricerca diventa accurato al 100%.

Tale risultato potrebbe essere ottenuto selezionando immediatamente la voce "Intera cella" (anche lasciando il valore "Formule" nell'"Area di ricerca").

Passiamo ora alla voce "Cerca".

Se invece dell'impostazione predefinita "Nel foglio" selezioni "Nel libro", non è necessario essere sul foglio delle celle che stai cercando. Lo screenshot mostra che l'utente ha avviato la ricerca mentre si trovava sul foglio bianco 2.

L'elemento successivo nella finestra di ricerca avanzata è "Sfoglia", che ha due significati. Per impostazione predefinita, è impostato su "riga per riga", che indica la sequenza di scansione delle celle riga per riga. Scegliendo un valore diverso - "per colonne", cambierà solo la direzione di ricerca e la sequenza dei risultati.

Quando si effettuano ricerche in documenti Microsoft Excel, è possibile utilizzare un altro carattere jolly - "*". Se considerato "?" significa qualsiasi carattere, quindi "*" sostituisce non uno, ma qualsiasi numero di caratteri. Di seguito è riportato uno screenshot di una ricerca per la Louisiana.

A volte la tua ricerca deve essere sensibile alle maiuscole. Se la parola louisiana è scritta con una lettera minuscola, i risultati della ricerca non cambieranno. Ma se selezioni "Confronta caso" nella finestra di ricerca avanzata, la ricerca non andrà a buon fine. Il programma considererà diverse le parole Louisiana e louisiana e, naturalmente, non troverà la prima di esse.

Cerca varietà

Cerca partite

A volte è necessario trovare valori duplicati in una tabella. Per cercare corrispondenze, devi prima selezionare un intervallo di ricerca. Quindi, nella stessa scheda Home, nel gruppo Stili, apri lo strumento Formattazione condizionale. Quindi selezionare in sequenza le voci "Regole di selezione delle celle" e "Valori duplicati".

Il risultato è mostrato nello screenshot qui sotto.

Se necessario, l'utente può modificare il colore della visualizzazione delle celle abbinate.

Filtrazione

Un altro tipo di ricerca è il filtraggio. Supponiamo che l'utente voglia trovare valori numerici nella colonna B nell'intervallo da 3000 a 4000.

Come puoi vedere, vengono visualizzate solo le righe che soddisfano la condizione inserita. Tutti gli altri sono stati temporaneamente nascosti. Per tornare allo stato iniziale, ripetere il passaggio 2.

Varie opzioni di ricerca sono state esaminate utilizzando l'esempio di Excel 2010. Come effettuare una ricerca in Excel di altre versioni? La differenza nella transizione al filtraggio è nella versione 2003. Nel menu "Dati", è necessario selezionare in sequenza i comandi "Filtro", "Filtro automatico", "Condizione" e "Filtro automatico personalizzato".

Video: ricerca tabella Excel

Anche articoli sull'utilizzo delle tabelle in Excel:

• Formattare le tabelle in Excel
• Creare tabelle in excel
• Crea una tabella pivot in excel
• Come selezionare un'intera tabella in Excel?

Tra le migliaia di righe e decine di colonne di dati manualmente nella tabella Excel, è quasi impossibile trovare qualcosa. L'unica opzione è utilizzare una sorta di funzione di ricerca, quindi vedremo come viene eseguita una ricerca in una tabella di Excel.

Per cercare i dati in una tabella Excel, è necessario utilizzare la voce di menu "Trova e seleziona" nella scheda "Home", in cui è necessario selezionare l'opzione "Trova" o utilizzare la combinazione di tasti "Ctrl + F" per chiamare .

Ad esempio, proviamo a trovare il numero richiesto tra i dati nella nostra tabella, poiché è durante la ricerca di numeri che è necessario prendere in considerazione alcune delle sottigliezze della ricerca. Cerchiamo il numero "10" nella tabella di Excel.

Dopo aver selezionato la voce di menu richiesta nella casella di ricerca che appare, inserire il valore desiderato. Abbiamo due opzioni per trovare i valori nella tabella Excel, questo è trovare tutte le corrispondenze contemporaneamente facendo clic sul pulsante "Trova tutto" o visualizzare immediatamente ogni cella trovata premendo ogni volta il pulsante "Trova successivo". Quando si utilizza il pulsante "Trova successivo", è necessario tenere conto anche della posizione corrente della cella attiva, poiché la ricerca inizierà esattamente da questa posizione.

Proviamo a trovare tutti i valori contemporaneamente, mentre tutto quello trovato verrà elencato nella finestra sotto le impostazioni di ricerca. Se lasci tutte le impostazioni predefinite, il risultato della ricerca non sarà esattamente quello che ci aspettavamo.

Per la corretta ricerca dei dati nella tabella Excel, cliccare sul pulsante "Parametri" e configurare l'area di ricerca. Ora il valore richiesto viene cercato anche nelle formule utilizzate nelle celle per i calcoli. Dobbiamo specificare la ricerca solo in valori e, se lo desideri, possiamo anche specificare il formato dei dati cercati.

Quando cerchi parole in una tabella Excel, dovresti anche prendere in considerazione tutte queste sottigliezze e, ad esempio, puoi persino prendere in considerazione il caso delle lettere.

E infine, diamo un'occhiata a come cercare i dati in Excel solo nell'area richiesta del foglio. Come puoi vedere dal nostro esempio, il valore desiderato "10" si verifica in tutte le colonne di dati contemporaneamente. Se hai bisogno di trovare questo valore, diciamo, solo nella prima colonna, devi selezionare questa colonna o qualsiasi intervallo di valori in cui vuoi cercare, quindi iniziare la ricerca.

La nostra prima colonna ha solo due valori uguali a "10", quindi se applichiamo l'opzione "Trova tutto", nell'elenco dovrebbero apparire solo due risultati di ricerca.

Nei documenti Microsoft Excel, che consistono in un gran numero di campi, è spesso necessario trovare dati specifici, nome di riga, ecc. È molto scomodo quando devi guardare attraverso un numero enorme di righe per trovare la parola o l'espressione desiderata. La ricerca Microsoft Excel integrata ti aiuterà a risparmiare tempo e nervi. Vediamo come funziona e come utilizzarlo.

Funzione di ricerca in Excel

La funzione di ricerca in Microsoft Excel offre la possibilità di trovare il testo o i valori numerici desiderati attraverso la finestra Trova e sostituisci. Inoltre, l'applicazione ha la possibilità di ricerca dati avanzata.

Metodo 1: ricerca semplice

Una semplice ricerca di dati in Excel consente di trovare tutte le celle che contengono un insieme di caratteri (lettere, numeri, parole, ecc.) immessi nella finestra di ricerca, senza distinzione tra maiuscole e minuscole.

1. Essendo nella scheda "Home", fai clic sul pulsante "Trova e seleziona", che si trova sulla barra multifunzione nella barra degli strumenti "Modifica". Nel menu che compare, seleziona la voce "Trova...". Invece di queste azioni, puoi semplicemente digitare la scorciatoia da tastiera Ctrl + F.
2. Dopo aver esaminato gli elementi pertinenti sulla barra multifunzione o premuto la combinazione di "tasti di scelta rapida", si aprirà la finestra "Trova e sostituisci" nella scheda "Trova". Ne abbiamo bisogno. Nel campo "Trova", inserisci la parola, i simboli o le espressioni con cui cercheremo. Fare clic sul pulsante "Trova successivo" o sul pulsante "Trova tutto".
3. Quando fai clic sul pulsante "Trova successivo", ci spostiamo nella prima cella, che contiene i gruppi di caratteri inseriti. La cellula stessa diventa attiva.

   La ricerca e la consegna dei risultati viene eseguita riga per riga. Tutte le celle della prima riga vengono elaborate per prime. Se non sono stati trovati dati corrispondenti alla condizione, il programma inizia la ricerca nella seconda riga e così via, finché non trova un risultato soddisfacente.

   I caratteri di ricerca non devono essere elementi indipendenti. Quindi, se l'espressione "diritti" viene specificata come richiesta, nell'output verranno presentate tutte le celle che contengono questo insieme sequenziale di caratteri, anche all'interno di una parola. Ad esempio, la parola "Giusto" sarà considerata una query pertinente in questo caso. Se inserisci il numero "1" nel motore di ricerca, la risposta sarà costituita da celle che contengono, ad esempio, il numero "516".

   Per passare al risultato successivo, premere nuovamente il pulsante "Trova successivo".

   Questo può essere continuato fino a quando la visualizzazione dei risultati non inizia su un nuovo cerchio.

4. Se, quando si avvia la procedura di ricerca, si fa clic sul pulsante "Trova tutto", tutti i risultati della ricerca verranno presentati sotto forma di elenco nella parte inferiore della finestra di ricerca. Questo elenco contiene informazioni sul contenuto delle celle con dati che soddisfano la query di ricerca, il loro indirizzo di posizione, nonché il foglio e il libro a cui appartengono. Per accedere a uno qualsiasi dei risultati del problema, è sufficiente fare clic su di esso con il tasto sinistro del mouse. Successivamente, il cursore si sposterà sulla cella di Excel su cui l'utente ha fatto clic.

Metodo 2: ricerca per un intervallo di celle specificato

Se si dispone di una tabella di dimensioni piuttosto grandi, in questo caso non è sempre conveniente cercare l'intero foglio, poiché i risultati della ricerca possono contenere un numero enorme di risultati che non sono necessari in un caso particolare. C'è un modo per limitare lo spazio di ricerca solo a un intervallo specifico di celle.

1. Seleziona l'area delle celle in cui vogliamo cercare.
2. Digitiamo la combinazione di tasti Ctrl + F sulla tastiera, dopodiché verrà avviata la già familiare finestra "Trova e sostituisci". Ulteriori azioni sono esattamente le stesse del metodo precedente. L'unica differenza è che la ricerca viene eseguita solo nell'intervallo di celle specificato.

Metodo 3: ricerca avanzata

Come accennato in precedenza, durante una ricerca normale, nei risultati della ricerca vengono incluse assolutamente tutte le celle contenenti un insieme sequenziale di caratteri di ricerca in qualsiasi forma, indipendentemente dal caso.

Inoltre, nei risultati può entrare non solo il contenuto di una cella specifica, ma anche l'indirizzo dell'elemento a cui si riferisce. Ad esempio, la cella E2 contiene una formula che è la somma delle celle A4 e C3. Questo importo è 10 e questo è il numero che appare nella cella E2. Ma, se impostiamo il numero "4" nella ricerca, la stessa cella E2 sarà tra i risultati della ricerca. Come è potuto succedere? È solo che la cella E2 contiene l'indirizzo della cella A4 come formula, che include solo la cifra desiderata 4.

Ma come tagliare questi e altri risultati di ricerca ovviamente inaccettabili? È per questi scopi che esiste una ricerca avanzata in Excel.

1. Dopo aver aperto la finestra "Trova e sostituisci" in uno dei modi sopra indicati, fare clic sul pulsante "Opzioni".
2. Nella finestra vengono visualizzati alcuni strumenti aggiuntivi per la gestione della ricerca. Per impostazione predefinita, tutti questi strumenti si trovano nello stesso stato di una normale ricerca, ma è possibile apportare modifiche se necessario.

   Per impostazione predefinita, le funzioni "Maiuscole/minuscole" e "Celle intere" sono disabilitate, ma se selezioniamo le caselle accanto agli elementi corrispondenti, il caso inserito verrà preso in considerazione quando si forma il risultato e verrà presa la corrispondenza esatta in considerazione. Se inserisci una parola con una lettera minuscola, nei risultati della ricerca, le celle contenenti l'ortografia di questa parola con una lettera maiuscola, come sarebbe per impostazione predefinita, non verranno più incluse. Inoltre, se la funzione "Celle intere" è abilitata, verranno aggiunti all'output solo gli elementi che contengono il nome esatto. Ad esempio, se si immette la query di ricerca "Nikolaev", le celle contenenti il ​​testo "Nikolaev AD" non verranno aggiunte ai risultati della ricerca.

   Per impostazione predefinita, viene cercato solo il foglio di lavoro Excel attivo. Tuttavia, se sposti il ​​parametro "Cerca" nella posizione "Nel libro", la ricerca verrà eseguita in tutti i fogli del file aperto.

   Nel parametro "Sfoglia" è possibile modificare la direzione della ricerca. Per impostazione predefinita, come accennato in precedenza, la ricerca viene eseguita riga per riga in ordine. Spostando il selettore in posizione "Per colonne" è possibile impostare l'ordine con cui vengono generati i risultati di emissione, a partire dalla prima colonna.

   Nella colonna "Area di ricerca" viene determinato quali elementi specifici vengono ricercati. Per impostazione predefinita, si tratta di formule, ovvero i dati che, quando si fa clic su una cella, vengono visualizzati nella barra delle formule. Può essere una parola, un numero o un riferimento di cella. Allo stesso tempo, il programma, quando esegue una ricerca, vede solo il collegamento, non il risultato. Questo effetto è stato discusso sopra. Per cercare esattamente in base ai risultati, in base ai dati visualizzati nella cella e non nella barra della formula, è necessario spostare l'interruttore dalla posizione "Formule" alla posizione "Valori". Inoltre, è possibile effettuare ricerche per note. In questo caso, spostare l'interruttore in posizione "Note".

   Puoi impostare la ricerca in modo ancora più preciso facendo clic sul pulsante "Formato".

   Questo apre la finestra del formato della cella. Qui puoi impostare il formato delle celle che parteciperanno alla ricerca. È possibile impostare restrizioni su formato numero, allineamento, carattere, bordo, riempimento e protezione, uno di questi parametri o una combinazione di essi.

   Se desideri utilizzare il formato di una cella specifica, nella parte inferiore della finestra, fai clic sul pulsante "Utilizza il formato di questa cella ...".

   Successivamente, viene visualizzato uno strumento contagocce. Con esso, puoi selezionare la cella, il formato di cui utilizzerai.

   Dopo aver configurato il formato di ricerca, fare clic sul pulsante "OK".

   Ci sono momenti in cui è necessario cercare non una frase specifica, ma trovare celle che contengono parole di ricerca in qualsiasi ordine, anche se sono separate da altre parole e simboli. Quindi queste parole devono essere evidenziate su entrambi i lati con un "*". Ora i risultati della ricerca mostreranno tutte le celle in cui si trovano le parole date in qualsiasi ordine.

3. Una volta impostate le impostazioni di ricerca, è necessario fare clic sul pulsante "Trova tutto" o "Trova successivo" per accedere ai risultati della ricerca.

Come puoi vedere, Excel è un set di strumenti di ricerca abbastanza semplice, ma allo stesso tempo molto funzionale. Per fare lo squittio più semplice, è sufficiente chiamare la finestra di ricerca, inserire una query e fare clic sul pulsante. Ma, allo stesso tempo, è possibile personalizzare una ricerca individuale con un gran numero di parametri diversi e impostazioni aggiuntive.

Siamo lieti di essere stati in grado di aiutarti a risolvere il problema.

Fai la tua domanda nei commenti, descrivendo in dettaglio l'essenza del problema. I nostri esperti cercheranno di rispondere il più rapidamente possibile.

Questo articolo ti ha aiutato?

Una parte significativa delle attività che vengono risolte con l'aiuto di fogli di calcolo presuppone che l'utente disponga già di almeno alcuni dati iniziali per trovare il risultato desiderato. Tuttavia, Excel 2010 dispone degli strumenti necessari con cui è possibile risolvere questo problema al contrario: selezionare i dati necessari per ottenere il risultato desiderato.

"Cerca una soluzione" è uno di questi strumenti, che è più conveniente per "problemi di ottimizzazione". E se non l'hai mai usato prima, ora è il momento di aggiustarlo.

Quindi, iniziamo installando questo componente aggiuntivo (poiché non apparirà da solo). Fortunatamente, ora questo può essere fatto in modo abbastanza semplice e rapido: apri il menu "Servizio" e già in esso "Componenti aggiuntivi"

Resta solo da indicare "Componenti aggiuntivi di Excel" nella colonna "Gestione", quindi fare clic sul pulsante "Vai".

Dopo questa semplice azione, nella sezione "Dati" verrà visualizzato il pulsante per l'attivazione della "Cerca una soluzione". Come mostrato nell'immagine

Diamo un'occhiata a come utilizzare correttamente Trova soluzioni in Excel 2010 con alcuni semplici esempi.

Esempio uno.

Diciamo che sei a capo di un grande reparto di produzione e devi distribuire correttamente i bonus ai dipendenti. Diciamo che l'importo totale dei bonus è di 100.000 rubli ed è necessario che i bonus siano proporzionali agli stipendi.

Cioè ora occorre trovare il corretto coefficiente di proporzionalità per determinare l'importo del bonus relativo allo stipendio.

Innanzitutto è necessario compilare velocemente (se non esiste già) una tabella in cui verranno archiviate le formule ei dati originali, in base alla quale sarà possibile ottenere il risultato desiderato. Per noi, questo risultato è l'importo totale del premio. E ora, attenzione: la cella di destinazione C8 deve essere collegata alla cella variabile desiderata sotto l'indirizzo E2 utilizzando le formule. Questo è fondamentale. Nell'esempio, li colleghiamo utilizzando formule intermedie, che sono responsabili del calcolo del bonus per ciascun dipendente (C2: C7).

Ora puoi attivare la ricerca di soluzioni. Si aprirà una nuova finestra, in cui è necessario specificare i parametri necessari.

Sotto "" è la nostra cella di destinazione. Ce ne può essere solo uno.

"" Sono possibili opzioni di ottimizzazione. In totale, è possibile selezionare i valori possibili "Massimo", "Minimo" o "Specifico". E se hai solo bisogno di un valore specifico, devi indicarlo nella colonna appropriata.

"" - possono esserci diverse celle modificabili (un intero intervallo o indirizzi specificati separatamente). Dopotutto, è con loro che funzionerà Excel, passando attraverso le opzioni in modo da ottenere il valore specificato nella cella di destinazione.

"" - Se è necessario impostare restrizioni, è necessario utilizzare il pulsante "Aggiungi", ma lo considereremo un po 'più tardi.

"" - un pulsante per passare a calcoli interattivi basati sul programma che abbiamo specificato.

Ma ora torniamo alla possibilità di modificare la nostra attività utilizzando il pulsante "Aggiungi". Questa fase è abbastanza responsabile (non meno della costruzione di formule), poiché è il vincolo che consente di ottenere il risultato corretto in uscita. Tutto qui è fatto nel modo più conveniente possibile, quindi puoi impostarli non solo per l'intero intervallo contemporaneamente, ma anche per determinate celle.

Per fare ciò, è possibile utilizzare una serie di segni specifici (e familiari a tutti gli utenti di Excel 2010) "=", "> =", "

Abbiamo una tabella che registra i volumi di vendita di alcuni beni in diversi mesi. È necessario trovare i dati nella tabella e il criterio di ricerca saranno le intestazioni di righe e colonne. Ma la ricerca deve essere eseguita separatamente su un intervallo di righe o colonne. Cioè, verrà utilizzato solo uno dei criteri. Pertanto, la funzione INDICE non può essere utilizzata qui, ma è necessaria una formula speciale.

Trovare valori in una tabella Excel

Per risolvere questo problema, illustreremo un esempio su una tabella schematica che corrisponde alle condizioni sopra descritte.

Foglio con una tabella per trovare i valori in verticale e in orizzontale:

Sopra la tabella stessa c'è una riga con i risultati. Nella cella B1 inseriamo il criterio per la query di ricerca, ovvero l'intestazione della colonna o il nome della riga. E nella cella D1, la formula di ricerca dovrebbe restituire il risultato del calcolo del valore corrispondente. Successivamente, la seconda formula funzionerà nella cella F1, che utilizzerà già i valori delle celle B1 e D1 come criteri per trovare il mese corrispondente.

Trovare un valore in una stringa di Excel

Ora scopriremo in quale volume massimo e in quale mese è stata la vendita massima del Prodotto 4.

Per cercare tra le colonne:

Trovato in quale mese e qual è stata la più grande vendita del Prodotto 4 per due trimestri.

Il principio della formula per trovare un valore in una riga di Excel:

Il primo argomento della funzione VLOOKUP (Vertical Look) specifica un riferimento alla cella in cui si trova il termine di ricerca. Il secondo argomento specifica l'intervallo di celle da visualizzare durante la ricerca. Il terzo argomento della funzione CERCA.VERT dovrebbe indicare il numero della colonna da cui deve essere preso il valore rispetto alla riga con il nome Prodotto 4. Ma poiché non conosciamo questo numero in anticipo, usiamo la funzione COLONNA per creare un array di numeri di colonna per l'intervallo B4: G15.

Ciò consente alla funzione CERCA.VERT di raccogliere un intero array di valori. Di conseguenza, la memoria memorizza tutti i valori corrispondenti per ciascuna colonna nella riga Prodotto 4 (ovvero: 360; 958; 201; 605; 462; 832). Successivamente, la funzione MAX deve solo prendere il numero massimo da questa matrice e restituirlo come valore per la cella D1, come risultato del calcolo della formula.

Come puoi vedere, la costruzione della formula è semplice e concisa. Sulla base, puoi trovare altri indicatori per un determinato prodotto in modo simile. Ad esempio, il valore minimo o medio del volume delle vendite utilizzando le funzioni MIN o AVERAGE. Niente ti impedisce di applicare questo scheletro della formula utilizzando funzioni più complesse per implementare l'analisi più comoda del report di vendita.

Come ottenere le intestazioni delle colonne dall'inizio di una cella?

Ad esempio, come abbiamo mostrato in modo impressionante il mese in cui c'è stata la vendita massima, utilizzando la seconda formula. Non è difficile notare che nella seconda formula abbiamo utilizzato lo scheletro della prima formula senza la funzione MAX. Struttura della formula principale: CERCA.VERT (B1; A5: G14; COLONNA (B5: G14); 0). Abbiamo sostituito la funzione MAX con CONFRONTA, che utilizza nel primo argomento il valore ottenuto dalla formula precedente. Ora funge da criterio per trovare il mese. E di conseguenza, la funzione CERCA ci restituisce il numero della colonna 2, dove si trova il valore massimo del volume delle vendite per il prodotto 4. Successivamente, la funzione INDICE è inclusa nel lavoro, che restituisce il valore per il numero della data e della colonna dall'intervallo specificato nei suoi argomenti. Poiché abbiamo un numero di colonna di 2 e il numero di riga nell'intervallo in cui sono memorizzati i nomi dei mesi in ogni caso sarà 1. Quindi abbiamo la funzione INDICE per ottenere il valore corrispondente dall'intervallo B4: G4 - Febbraio (secondo mese).



Trovare un valore in una colonna di Excel

La seconda variante del problema sarà una ricerca tabellare utilizzando il nome del mese come criterio. In questi casi, dobbiamo cambiare lo scheletro della nostra formula: sostituire la funzione CERCA.VERT con CERCA.ORIZZ e la funzione COLONNA viene sostituita con RIGA.

Questo ci permetterà di scoprire quale volume e quale prodotto è stata la vendita massima in un determinato mese.

Per trovare quale prodotto ha avuto il volume di vendita più alto in un determinato mese, dovresti:

Il principio della formula per trovare un valore in una colonna di Excel:

Nel primo argomento della funzione HLP (Horizontal View) indichiamo un collegamento ad una cella con un criterio di ricerca. Il secondo argomento è un riferimento all'intervallo della tabella che si sta cercando. Il terzo argomento è generato dalla funzione LINE, che crea una matrice di 10 elementi di numeri di riga in memoria. Poiché abbiamo 10 righe nella sezione tabellare.

Inoltre, la funzione HLP, utilizzando a turno ogni numero di riga, crea un array dei corrispondenti valori di vendita dalla tabella per un mese specifico (giugno). Quindi la funzione MAX deve solo selezionare il valore massimo da questa matrice.

ATTENZIONE! Quando si utilizza la formula scheletro per altre attività, prestare sempre attenzione al secondo e al terzo argomento della funzione di ricerca HLO. Il numero di righe coperte nell'intervallo specificato nell'argomento deve corrispondere al numero di righe nella tabella. E anche la numerazione deve partire dalla seconda riga!

Ciao amici. Quanto spesso devi cercare una corrispondenza in una tabella di Excel per un dato valore? Ad esempio, è necessario trovare l'indirizzo di una persona in una directory o nel listino prezzi: il prezzo di un prodotto. Se si verificano tali attività, questo post è per te!

Eseguo queste procedure ogni giorno e senza le funzioni descritte di seguito, farei davvero fatica. Prendi nota e applicali nel tuo lavoro!

Funzioni di ricerca nella tabella di Excel, CERCA.VERT e CERCA.ORIZZ

Il ruolo di queste funzioni nella vita di un utente ordinario difficilmente può essere sopravvalutato. Ora puoi facilmente trovare un record adatto nella tabella dei dati e restituire il valore corrispondente.

La sintassi per la funzione CERCA.VERT è: = CERCA.VERT ( Valore di ricerca; tabella_per_ricerca; numero_colonna da visualizzare; [match_type]). Considera gli argomenti:

• Valore desiderato- il valore che cercheremo. Questo è un argomento obbligatorio;
• Cerca tabella- l'array di celle in cui verrà effettuata la ricerca. La colonna con i valori desiderati deve essere la prima in questo array. Anche questo è un argomento obbligatorio;
• Numero di colonna visualizzato- il numero ordinale della colonna (a partire dal primo nell'array) da cui la funzione restituirà i dati se i valori richiesti corrispondono. Argomento richiesto;
• Tipo di corrispondenza- selezionare "1" (o "VERO") per una corrispondenza non stretta, "0" ("FALSO") per una corrispondenza completa. L'argomento è facoltativo, se lo ometti, la ricerca verrà eseguita partita sciolta.

Trovare una corrispondenza esatta usando CERCA.VERT

Diamo un'occhiata a un esempio di come funziona la funzione CERCA.VERT quando il tipo di corrispondenza è "FALSO", cercando una corrispondenza esatta. Nella matrice B5: E10 sono indicati i cespiti di una determinata azienda, il loro valore contabile, il numero di inventario e l'ubicazione. La cella B2 contiene il nome per il quale è necessario trovare il numero di inventario nella tabella e inserirlo nella cella C2.

Funzione CERCA.VERT in Excel

Scriviamo la formula: = CERCA.VERT (B2; B5: E10; 3; FALSE).

Qui, il primo argomento indica che la tabella deve essere cercata per il valore dalla cella B2, ad es. la parola "Fax". Il secondo argomento dice che la tabella per la ricerca è nell'intervallo B5: E10 e devi cercare la parola "Fax" nella prima colonna, ad es. nella matrice B5: B10. Il terzo argomento dice al programma che il risultato del calcolo è contenuto nella terza colonna dell'array, ad es. D5: D10. Il quarto argomento è FALSO, cioè è richiesta la corrispondenza completa.

E così, la funzione riceverà la riga "Fax" dalla cella B2 e la cercherà nell'array B5: B10 dall'alto verso il basso. Una volta trovata una corrispondenza (riga 8), la funzione restituirà il valore corrispondente dalla colonna D, ad es. contenuto di D8. Questo è esattamente ciò di cui avevamo bisogno, il problema è stato risolto.

Se il valore richiesto non viene trovato, la funzione verrà restituita.

Trova corrispondenze fuzzy con CERCA.VERT

Grazie a questa opzione nel lavoro VLOOKUP, possiamo evitare formule complesse per trovare il risultato desiderato.

Matrice B5: C12 mostra i tassi di interesse sui prestiti a seconda dell'importo del prestito. Nella cella B2 indichiamo l'importo del prestito e vogliamo ottenere il tasso in C2 per tale operazione. Il compito è difficile perché la somma può essere qualsiasi cosa ed è improbabile che coincida con quelle specificate nell'array; la ricerca per corrispondenza esatta non è adatta:

Quindi scriviamo la formula per una ricerca non rigorosa: = CERCA.VERT (B2, B5: C12, 2, VERO)... Ora il programma cercherà il più piccolo tra tutti i dati presentati nella colonna B. Cioè, per l'importo di 8.000, verrà selezionato il valore 5000 e verrà visualizzata la percentuale corrispondente.

Ricerca VLOOKUP fluida in Excel

Affinché la funzione funzioni correttamente, è necessario ordinare la prima colonna della tabella in ordine crescente. In caso contrario, potrebbe dare un risultato errato.

La funzione CERCA.VERT ha la stessa sintassi della funzione CERCA.VERT, ma cerca il risultato non nelle colonne, ma nelle righe. Cioè, esegue la scansione delle tabelle non dall'alto verso il basso, ma da sinistra a destra e visualizza il numero di riga specificato, non la colonna.

Trovare dati utilizzando la funzione BROWSE

La funzione CERCA funziona in modo simile a CERCA.VERT, ma ha una sintassi diversa. Lo uso quando la tabella dati contiene diverse dozzine di colonne e per utilizzare CERCA.VERT è necessario calcolare ulteriormente il numero della colonna visualizzata. In questi casi, la funzione CERCA facilita il compito. E quindi, la sintassi è: = CERCA ( Valore di ricerca; Array_for_search; Array_to_ display) :

• Valore desiderato- dati o link a dati da ricercare;
• Cerca array- una riga o colonna in cui cerchiamo un valore simile. Dobbiamo ordinare questo array in ordine crescente;
• Array da visualizzare- un intervallo contenente i dati per la visualizzazione dei risultati. Naturalmente, deve essere della stessa dimensione dell'array di ricerca.

Con questa notazione, stai dando un riferimento non relativo all'array dei risultati. E lo indichi direttamente, ad es. non è necessario precalcolare il numero della colonna di output. Usiamo la funzione CERCA nel primo esempio per la funzione CERCA.VERT (cespiti, numeri di inventario): = CERCA (B2, B5: B10, D5: D10)... Il problema è stato risolto con successo!

Funzione CERCA in Microsoft Excel

Ricerca per coordinate relative. Funzioni RICERCA e INDICE

Un altro modo per cercare i dati è combinare le funzioni RICERCA e INDICE.

Il primo di essi viene utilizzato per cercare un valore in un array e ottenere il suo numero ordinale: SEARCH ( Valore di ricerca; guardato_array; [Tipo di corrispondenza] ). Argomenti della funzione:

• Valore desiderato- argomento richiesto
• Array da guardare- una riga o colonna in cui stiamo cercando una corrispondenza. Argomento richiesto
• Tipo di corrispondenza- specificare "0" per cercare una corrispondenza esatta, "1" - il più vicino più basso, "-1" - il più vicino più alto. Poiché la funzione cerca dall'inizio alla fine dell'elenco, quando si cerca il più piccolo più vicino, ordinare la colonna di ricerca in ordine decrescente. E quando cerchi di più, ordinalo in ordine crescente.

La posizione del valore richiesto è stata trovata, ora è possibile visualizzarla a schermo utilizzando la funzione INDICE( Vettore; Numero_linea; [Numero_colonna]) :

• Vettore- l'argomento indica da quale array di celle selezionare un valore
• Numero di linea- specificare il numero ordinale della riga (a partire dalla prima cella dell'array) che si desidera visualizzare. Qui puoi scrivere il valore manualmente o utilizzare il risultato del calcolo di un'altra funzione. Ad esempio, CERCA.
• Numero di colonna- argomento opzionale, specificato se l'array è composto da più colonne. Se omesso, la formula utilizza la prima colonna della tabella.

Ora combiniamo queste funzioni per ottenere il risultato:

Funzioni RICERCA e INDICE in Excel

Questi sono i modi per trovare e visualizzare i dati in Excel. Inoltre, puoi usarli nei calcoli, usarli in una presentazione, eseguire operazioni con essi, specificare altre funzioni come argomenti, ecc.

Senti come le tue conoscenze e abilità stanno crescendo e diventando più forti? Allora non fermarti, continua a leggere! Nel prossimo post prenderemo in considerazione: sarà difficile e interessante!

Funzione RICERCA in Excel viene utilizzato per determinare la posizione del testo all'interno di qualsiasi testo e indicarne la posizione esatta.

Cosa restituisce la funzione

La funzione restituisce un valore numerico che indica la posizione iniziale del testo di ricerca all'interno di altro testo. La posizione designa il numero ordinale del carattere da cui inizia il testo di ricerca.

Sintassi

= CERCA (trova_testo, entro_testo,)- Versione inglese

= CERCA (testo_ricerca; testo_ricerca; [posizione_iniziale])- Versione russa

Argomenti della funzione

• trova_testo (cerca_testo)- il testo o la stringa di testo che vuoi trovare;
• entro_testo (testo_visualizzabile)- il testo all'interno del quale si sta effettuando la ricerca;
• ([posizione di partenza])- un valore numerico indicante la posizione da cui si vuole iniziare la ricerca. Se non specifichi questo argomento, la funzione inizierà la ricerca dall'inizio del testo.

Informazioni aggiuntive

• Se la posizione di inizio della ricerca non è specificata, la ricerca testuale viene eseguita prima del testo;
• La funzione non fa distinzione tra maiuscole e minuscole. Se hai bisogno della distinzione tra maiuscole e minuscole, usa la funzione;
• La funzione può gestire i caratteri jolly. Ci sono tre caratteri jolly in Excel -?, *, ~.
  • cartello "?" - corrisponde a qualsiasi singolo carattere;
  • "*" - corrisponde a eventuali caratteri aggiuntivi;
  • il segno "~" viene utilizzato quando è necessario trovare il punto interrogativo stesso o un asterisco.
• La funzione restituisce un errore se il testo di ricerca non è stato trovato.

Esempi di utilizzo della funzione CERCA in Excel

Esempio 1. Ricerca di una parola all'interno di una stringa di testo (dall'inizio)

Nell'esempio sopra, puoi vedere che quando cerchiamo la parola "buono" nel testo "Buongiorno", la funzione restituisce il valore "1", che corrisponde alla posizione della parola "buono" nel testo " Buon giorno".

Poiché la funzione non fa distinzione tra maiuscole e minuscole, non fa differenza come specifichiamo la parola desiderata "buono", sia esso "GENTILE", "Gentile", "gentile", ecc. la funzione restituirà lo stesso valore.

Se devi fare una ricerca con distinzione tra maiuscole e minuscole, usa la funzione in Excel.

Esempio 2. Cerchiamo una parola all'interno di una stringa di testo (che indica la posizione di partenza della ricerca)

Il terzo argomento della funzione indica la posizione ordinale all'interno del testo, dalla quale verrà eseguita la ricerca. Nell'esempio sopra, la funzione restituisce "1" quando cerca "buono" nel testo "Buongiorno", a partire dalla prima posizione.

Allo stesso tempo, se indichiamo alla funzione che la ricerca deve iniziare dal secondo carattere del testo "Buongiorno", cioè la funzione in questo caso vede il testo come "buongiorno" e cerca la parola " buono", allora il risultato sarà un errore.

Se non specifichi la posizione di inizio della ricerca come argomento, la funzione avvierà automaticamente la ricerca dall'inizio del testo.

Esempio 3. Cerca una parola in presenza di più corrispondenze nel testo

La funzione avvia la ricerca del testo dalla posizione di partenza, che possiamo specificare come argomento, oppure inizierà la ricerca automaticamente dall'inizio del testo. Nell'esempio sopra, stiamo cercando la parola "buono" nel testo "Buongiorno buongiorno" con la posizione di partenza per la ricerca "1". In questo caso, la funzione restituisce "1", poiché la prima parola trovata "Tipo" inizia con il primo carattere del testo.

Se indichiamo alla funzione l'inizio della ricerca, ad esempio, dal secondo carattere, il risultato del calcolo della funzione sarà "8".

Esempio 4. Utilizzo dei caratteri jolly nella funzione CERCA in Excel

La funzione tiene conto dei caratteri jolly durante la ricerca. Nell'esempio sopra, stiamo cercando il testo "c * l". La presenza del carattere jolly "*" in questa query significa che stiamo cercando qualsiasi parola che inizia con la lettera "c" e termina con la lettera "l", e ciò che è tra queste due lettere non è importante. Di conseguenza, la funzione restituisce il valore "3", perché nella parola "Excel" situata nella cella A2, la lettera "c" è nella terza posizione.