Kimm ha un foglio di lavoro che contiene tre colonne. La colonna A contiene il cognome di una persona, la colonna B contiene il nome e la colonna C contiene il numero di telefono della persona. Se Kimm conosce il nome e il cognome della persona (diciamo che sono rispettivamente nelle celle F1 e F2), si chiede che tipo di formula di ricerca userebbe per restituire il numero di telefono della prima persona che corrisponde a quel nome e cognome.

In realtà ci sono diverse formule diverse che potresti usare per capire il numero di telefono. La maggior parte degli approcci prevede l’utilizzo di formule di matrice, che vengono sempre immesse in una cella utilizzando Ctrl + Maiusc + Invio. Per rendere le formule più facili da capire, è meglio lavorare con intervalli denominati. Ad esempio, imposta i seguenti nomi:

  • Seleziona tutti i cognomi, nella colonna A, e assegna loro un nome come LNames.

  • Seleziona tutti i primi nomi, nella colonna B, e assegna loro un nome come FNames.

  • Seleziona tutti i numeri di telefono, nella colonna C, e assegna loro un nome come Telefoni.

Ecco una varietà di formule di matrice che potresti usare per trovare il numero di telefono:

=SUMPRODUCT(--(LNames=F2),--(FNames=F1),Phones)

=INDEX(Phones,MATCH(F1&F2,FNames&LNames,))

=INDEX(Phones,INDEX(MATCH(F1&F2,FNames&LNames,0),))

=OFFSET(C1,MATCH(F1&F2,FNames&LNames,),)

Inoltre, puoi costruire una formula di matrice che si basa sulla funzione ROW, come mostrato qui:

=INDEX(Phones,SUMPRODUCT((F1&F2=FNames&LNames)*(ROW(FNames)-1)))

=INDEX(Phones,MIN(IF((FNames=F1)*(LNames=F2),(ROW(Phones)-1))))

Se si utilizza questo approccio (basandosi sulla funzione RIGA), potrebbe essere necessario modificare la parte “-1” delle formule in modo da riflettere il numero di righe visualizzate prima dei dati effettivi. In questo caso, la riga viene decrementata di uno perché la prima riga della tabella dati è costituita dalle intestazioni di ciascuna colonna; i dati stessi iniziano nella riga 2.

Si noti che tutte le formule utilizzate finora si basano sulla combinazione del nome con il cognome per effettuare un confronto. Ciò ha la possibilità di incorrere in “falsi positivi” in alcuni casi. Ad esempio, supponiamo che due dei nomi che hai nei tuoi dati siano Thom Astonfield e Thomas Tonfield. Poiché il caso delle lettere nei nomi non ha importanza in queste formule, quando si combinano il nome e il cognome di questi individui, sono esattamente gli stessi. Pertanto, se stai cercando il numero di telefono di Thomas Tonfield e il suo nome appare nell’elenco dopo Thom Astonfield, otterrai sempre il numero di telefono di Thom invece di quello di Thomas.

Per aggirare questo potenziale problema, potresti voler includere una sorta di separatore tra il nome e il cognome. Utilizzando una delle formule precedenti nel suggerimento, tutto ciò che dovresti fare è apportare una regolazione, come mostrato qui:

=INDEX(Phones,SUMPRODUCT((F1&":"&F2=FNames&":"&LNames)*(ROW(FNames)-1)))

L’aggiunta dei due punti tra il nome e il cognome funge da separatore, eliminando la probabilità di falsi positivi.

Dovresti anche renderti conto che se le celle F1 e F2 contengono un nome che non appare affatto nei dati, le informazioni che ottieni dalla formula saranno errate. A seconda della variazione della funzione utilizzata, potresti recuperare una condizione di errore reale (come # N / A o #REF)

oppure potresti recuperare dati semplicemente sbagliati. In quei casi in cui viene restituita una condizione di errore, potresti voler modificare la tua formula per tenere conto della possibilità di non trovare una corrispondenza, in questo modo:

=IF(ISERROR(INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0))), "no phone",INDEX(Phones,MATCH(F1&":"&F2,FNames&":"&LNames,0)))

Ci sono alcune cose che dovresti tenere a mente quando usi questi tipi di formule. Innanzitutto, l’affidabilità delle informazioni che ottieni dipenderà in gran parte dalla qualità delle informazioni nel tuo elenco di dati. Se i tuoi dati contengono errori di ortografia, contengono spazi vuoti, sono ordinati in un ordine strano o ci sono più voci per la stessa persona, ciò può influenzare il risultato della formula.

Se non sei troppo sicuro della qualità dei tuoi dati, potresti semplicemente utilizzare le funzionalità di filtro di Excel anziché una formula. Applica un filtro automatico e puoi utilizzare le prime due colonne dei tuoi dati per scegliere il nome e il cognome. In questo modo verranno restituiti i numeri di telefono della persona selezionata. È molto semplice da fare e semplifica la selezione dei dati necessari.

Infine, dovresti renderti conto che ci sono altri approcci che puoi usare per affrontare il problema. Ad esempio, è possibile creare una formula che utilizza la funzione DGET, ma ciò richiederebbe l’aggiunta di una piccola tabella dei criteri al foglio o alla cartella di lavoro. Poiché Kimm ha specificato che non poteva aggiungere risultati intermedi in un foglio di lavoro, è stata presa la decisione editoriale di non includere la funzione DGET come soluzione poiché richiederebbe l’aggiunta della tabella dei criteri.

Inoltre, se hai dimestichezza con l’utilizzo delle macro, potresti anche creare una funzione definita dall’utente che esaminerebbe i dati e restituirebbe il numero di telefono richiesto. Il vantaggio di utilizzare un tale approccio è che fornisce una maggiore flessibilità nell’elaborazione delle informazioni effettivamente restituite dalla funzione.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (10478) si applica a Microsoft Excel 97, 2000, 2002 e 2003.

Puoi trovare una versione di questo suggerimento per l’interfaccia a barra multifunzione di Excel (Excel 2007 e versioni successive) qui:

Pulling a Phone Number with a Known First and Last Name.