Formule complesse in Excel (sommario)
- Introduzione alle formule complesse in Excel
- Scegli Funzione come funzione di ricerca
- VLOOKUP Con SCEGLI
- SE Con funzione AND & OR
Introduzione alle formule complesse in Excel
Excel viene fornito con molte formule utili, abbiamo una categoria di Finanza, Logica, Testo, Data e ora, Ricerca e riferimenti e Matematica e Trig ”. Ogni singola formula è abbastanza buona per gli utenti intermedi in cui affrontano situazioni dirette, ma man mano che avanzi al livello successivo la tua abilità deve prendere il sopravvento, quindi a tale proposito l'applicazione di formule più complesse in Excel gioca un ruolo vitale nella risoluzione di situazioni complesse. Tenendo presente questo articolo, ti illustreremo alcune delle formule complesse importanti e tanto necessarie. Continuare a leggere.
Scegli Funzione come funzione di ricerca
Non molti di voi hanno utilizzato la funzione SCEGLI, ma questo è molto utile anche come funzione di ricerca.
Puoi scaricare questo modello di Excel di formule complesse qui - Modello di Excel di formule complesse Ad esempio guarda i dati di seguito.
Sotto questa tabella sono disponibili dati dei clienti su base mensile, ogni tabella dei clienti è disponibile. In questa tabella, nella cella B13 ho creato l'elenco a discesa del mese, in modo che quando questo menu a discesa cambia dovremmo ottenere il totale del mese selezionato.
Crea una tabella come la seguente.
Applicare VLOOKUP alla cella D13.
Ora apri la funzione SCEGLI nella cella B14.
Seleziona il numero INDICE come cella VLOOKUP e rendilo un riferimento assoluto.
Ora seleziona per ogni argomento i numeri delle vendite ogni mese.
Chiudi la parentesi e premi il tasto Invio.
Ora copia e incolla anche la formula nelle celle sottostanti.
Ora SCEGLI formula mostrerà i numeri del mese selezionato.
VLOOKUP con la funzione SCEGLI
Abbiamo visto la tradizione VLOOKUP ma possiamo combinarla anche con la funzione SCEGLI. Ad esempio, guarda i dati seguenti.
Nelle celle da A1 a B11, abbiamo una tabella delle vendite e nella cella D2 ho creato l'elenco a discesa dei mesi e nella cella E2 dobbiamo applicare VLOOKUP e ottenere il valore delle vendite del mese selezionato. Ma il problema per applicare VLOOKUP è una struttura di dati, in cui il valore di ricerca è a destra della colonna dei risultati, quindi VLOOKUP non può recuperare i dati da destra a sinistra, in questo caso, possiamo usare la funzione SCEGLI.
Per selezionare l'array di tabelle, è necessario aprire qui la funzione SCEGLI.
Nell'argomento Numero INDICE in fiore, le parentesi immettono 1 e 2. Ora seleziona il primo intervallo come colonna Mese.
Seleziona la colonna Vendite come secondo intervallo.
Indicare il numero indice della colonna come 2 e l'intervallo di ricerca come 0.
Quindi abbiamo ottenuto il risultato per il mese di aprile dalla tabella. Pertanto, ogni volta che non si dispone di una struttura dati precisa, è possibile selezionare la funzione SCEGLI.
SE Con funzione AND & OR
Le funzioni logiche sono piene di logica per calcolare le cose. Dai un'occhiata ai dati di esempio seguenti.
Dalla tabella sopra, dobbiamo calcolare il bonus in base alle condizioni seguenti.
- Se il servizio ha una durata superiore a 1, 5 anni e se il reparto è Vendite o Assistenza, il bonus è 1000.
- Se non altro, il bonus è 3000.
Per calcolare il bonus dobbiamo applicare IF, AND e OR insieme. Aprire la condizione IF nella cella D2.
Dobbiamo testare più condizioni, aprire la funzione AND.
Ora la logica successiva è Vendite o Supporto, quindi per questa condizione OR aperta.
Verifica se il titolo è Vendite o Supporto.
Quindi, se tutte le condizioni fornite sono VERE, il bonus dovrebbe essere 10000.
Se i test logici forniti sono FALSE, il bonus è 3000.
Chiudi la parentesi e premi il tasto Invio per ottenere il risultato.
Copia e incolla la formula nelle celle sottostanti.
Quindi in base alle condizioni abbiamo ottenuto il risultato. Se il servizio è> 1, 5 anni e il titolo è Vendite o Supporto, ne avremo 10000 oppure 3000.
Formula complessa con caratteri jolly
VLOOKUP richiede un valore di ricerca esatto da abbinare per recuperare i dati. Questo è lo slogan tradizionale ma possiamo ancora recuperare i dati utilizzando il valore di ricerca delle particelle. Ad esempio, se il valore di ricerca è "VIVO" e nella tabella principale se è "VIVO Mobile", possiamo ancora abbinare utilizzando i caratteri jolly. Vedremo ora uno degli esempi, di seguito sono riportati i dati di esempio.
Abbiamo una tabella di ricerca nella colonna A. Nella colonna C abbiamo valori di ricerca, questi valori di ricerca non sono esattamente uguali ai valori della tabella di ricerca. Vedremo quindi come applicare VLOOKUP usando i caratteri jolly.
Innanzitutto, apri la funzione VLOOKUP nella cella D1.
Il primo argomento è il valore di ricerca . Uno dei problemi con il valore di ricerca qui è che non abbiamo una corrispondenza esatta, quindi dobbiamo racchiudere questo valore di ricerca con un asterisco prima e dopo il valore di ricerca.
Qui abbiamo applicato due caratteri asterisco "*" & C2 & "*". Qui l'asterisco indica che qualsiasi cosa tra i caratteri jolly deve essere abbinata e restituisce il risultato correlato.
Anche se il carattere asterisco "Infosys" corrispondeva al valore nella tabella di ricerca e restituiva il risultato esatto come "Infosys Ltd".
Allo stesso modo, nella cella D6, abbiamo ottenuto il valore di errore come #VALORE! perché non c'è una parola "Mintra" nella tabella di ricerca.
Articoli consigliati
Questa è una guida alle formule complesse in Excel. Qui discutiamo alcune delle formule complesse importanti e tanto necessarie con esempi e template di Excel. Puoi anche consultare i nostri altri articoli suggeriti per saperne di più -
- RICERCA Formula in Excel (Suggerimenti e trucchi)
- Tutorial sulla funzione NORMSINV di Excel
- Casella del nome e suoi usi in Excel
- Concatena le stringhe in Excel