Autore: Andrea Pacchiarotti

La funzione CERCA.VERT ricerca un valore nella prima colonna di un intervallo di celle e  restituisce un altro valore da qualsiasi cella sulla stessa riga dell'intervallo, cerca quindi verso destra.
(Gli argomenti in rosso sono obbligatori, quelli in corsivo sono facoltativi)

Sintassi
CERCA.VERT(valore;matricetabella;indice;intervallo)

  • valore Valore da cercare nella prima colonna della tabella o dell'intervallo. Valore può essere un valore o un riferimento.
  • matricetabella Intervallo di celle contenenti i dati. È possibile usare un intervallo (es. A1:C5) o un nome di intervallo (es. Reddito). Non viene rilevata la distinzione tra maiuscole e minuscole.
  • indice  Numero di colonna dell'argomento matricetabella dal quale deve essere restituito il valore corrispondente; ad esempio un argomento indice con valore 3 restituisce il valore della terza colonna di matricetabella. È possibile usare RIF.COLONNA(rif) al posto dell’indice numerico.
  • intervallo  Valore logico che specifica se si desidera trovare una corrispondenza esatta (FALSO) o approssimativa (VERO); se non lo specifico è VERO
    • Se intervallo è VERO (oppure 1) o è omesso, verrà restituita una corrispondenza esatta o approssimativa. Se non viene trovata alcuna corrispondenza esatta, verrà restituito il successivo valore più grande, inferiore a valore.
      Se intervallo è VERO o è omesso, i valori nella prima colonna di matricetabella dovranno essere collocati in ordine crescente.
    • Se intervallo è FALSO (oppure 0), non è necessario ordinare i valori della prima colonna di matricetabella. FALSO restituisce una corrispondenza esatta. Se nella prima colonna di matricetabella è presente più di un valore corrispondente a valore, verrà utilizzato il primo valore trovato. Se non viene trovata alcuna corrispondenza esatta, verrà restituito il valore di errore #N/D (Non Disponibile).

 

Supponendo che i dati si trovano in Foglio1 del file cerca.vert.xlsx:

  • cerca in E2 il reddito di Sara

=CERCA.VERT("Sara";A1:C5;3;FALSO)

  • cerca in F3 il reddito di Sara inserendo il nome in E3

=CERCA.VERT(E3;A1:C5;3;FALSO)

=CERCA.VERT(E3;A1:C5;RIF.COLONNA(C1);FALSO)

  • cerca in E3 di Foglio2 il reddito di Sara inserendo il nome in D3 di Foglio2

=CERCA.VERT(D3;Foglio1!A1:C5;3;FALSO)

  • cerca in F3 di Foglio1 il reddito di Sara inserendo il nome in E3 di Foglio1 del file Altro.xlsx

=CERCA.VERT(E3;[Altro.xlsx]Foglio1!A1:C5;3;FALSO)

 

Inserendo l’età in D1 estrarre la categoria:


=CERCA.VERT(D1;A1:B6;2;VERO)

 

La funzione CERCA.ORIZZ ricerca un valore nella prima riga di un intervallo di celle e  restituisce un altro valore nella riga indicata in corrispondenza della colonna in cui è stato trovato.
Sintassi
CERCA.ORIZZ(valore;matricetabella;indice;intervallo)
Del tutto simile al CERCA.VERT ha l'unica differenza nell'argomento indice che questa volta indica il numero di riga da restituire anziché il numero di colonna

  • cerca in E2 quante tonnellate di frutta ha prodotto la Sicilia

=CERCA.ORIZZ("Frutta";A1:D4;4;FALSO)

Segui ML Training sui Social:
Facebook - LinkedIn - Twitter - Google+

Posted: 26 Set 16 By: Category: Blog Read 1358 times

Autore: Andrea Pacchiarotti

Per verificare se un valore rispetta una condizione impostata e dare un risultato se la condizione restituisce VERO ed un altro risultato se restituisce FALSO, Excel mette a disposizione la funzione:
SE
(Gli argomenti in rosso sono obbligatori, quelli in corsivo sono facoltativi)

SE

SE(test;severo;sefalso)
Prevede i seguenti argomenti:
test condizione da verificare sul valore sotto forma di testo logico. Prevede l'utilizzo degli operatori di confronto.
severo valore che viene restituito se l'argomento test è VERO.
sefalso valore che viene restituito se l'argomento test è FALSO.

Se l'esito del SE deve essere una parola o frase, tale testo va tra virgolette, ad esempio: 
=SE(A1>10,"Maggiore di 10","Minore o uguale a 10") restituisce il testo "Maggiore di 10" se il valore contenuto in A1 è maggiore di 10 ed il testo "Minore o uguale a 10" se il valore contenuto in A1 è minore o uguale a 10. 

  1. Far apparire da C2 a C5 Risparmio se la PREVISIONE è maggiore del PREZZO FINALE, altrimenti Perdita
    =SE(A2>B2;"Risparmio";"Perdita")
  2. Far apparire da C2 a C5 Diverso se la PREVISIONE è diversa del PREZZO FINALE, altrimenti lasciare vuota la cella
    =SE(A2<>B2;"Diverso";"")
  3. Far apparire in D2 Iniziare i lavori se la somma del PREZZO FINALE è inferiore a 10.000 euro, altrimenti Non iniziare i lavori
    =SE(SOMMA(C4:C7)<=10000;"Iniziare i lavori";"Non iniziare")
  4. Far apparire in D3 il Costo previsionale medio senza usare la funzione MEDIA =SE(SOMMA(A4:A7);SOMMA(A4:A7)/CONTA.NUMERI(A4:A7))

 

 

  1. Far apparire da B2 a B6 se il Fatturato raggiunto dall’agente è stato pari almeno a C2, altrimenti far apparire No
    =SE(A2>=C$2;”Sì”;”No”)

 

       
  1. Far apparire in D3 Ammesso, se l’allievo ha ottenuto almeno 6 sia allo scritto che all’orale, altrimenti Respinto
    =SE(E(C13>=6;D13>=6);"Ammesso";"Respinto")
  2. Far apparire in E3 Ammesso, se l’allievo ha ottenuto almeno 6 nello scritto o nell’orale, altrimenti Respinto
    =SE(O(C13>=6;D13>=6);"Ammesso";"Respinto")
  3. Far apparire in G3 Ammesso, se l’allievo ha ottenuto almeno 6 nello scritto, nell’orale e nella pratica, altrimenti Respinto
    =SE(E(B3>=6;C3>=6;F3>=6);"Ammesso";"Respinto")
  4. Farapparire in H3 Promosso, se la media è da 6 in su, Rimandato se è tra 5,5 ed inferiore a 6, Respinto se inferiore a 5,5
    =SE(MEDIA(B3;C3;F3)>=6;"Promosso";SE(MEDIA(B3;C3;F3)<5,5;"Respinto";"Rimandato")

 

  1.  Far apparire da B2 a B6 A se il voto va da 90 in su, B da 80 a 89, C da 70 a 79, D da 60 a 69, E da 0 a 59
    =SE(A2>89;"A";SE(A2>79;"B";SE(A2>69;"C";SE(A2>59;"D";SE(A2>=0;"E")))))

Segui ML Training sui Social:
Facebook - LinkedIn - Twitter - Google+

Posted: 26 Set 16 By: Category: Blog Read 875 times

Autore: Andrea Pacchiarotti

Per eseguire un subtotale dei valori presenti in un intervallo Excel mette a disposizione la funzione:
SUBTOTALE
(Gli argomenti in rosso sono obbligatori, quelli in corsivo sono facoltativi)

SUBTOTALE

È possibile creare un subtotale anche dalla scheda Dati, gruppo Struttura, icona Subtotale.
SUBTOTALE(numerofunzione;rif1;rif2;...)
Prevede i seguenti argomenti:
Numerofunzione numero tra 1 e 11, per includere i valori nascosti (tramite la scheda Home del gruppo Celle, icona Formato/ Nascondi e scopri/Nascondi righe), o tra 101 e 111, per escludere i valori nascosti, che indica la funzione per definire il subtotale: 1/101 Media, 2/102 Conta.numeri; 3/103 Conta.valori, 4/104 Max, 5/105 Min, 6/106 Prodotto, 7/107 Dev.st, 8/108 Dev.st.pop, 9/109 Somma, 10/110 Var, 11/111 Var.pop.
Rif1 intervallo di calcolo del subtotale.
Rif2;... eventuali ulteriori intervalli di calcolo, fino a 254, del subtotale.
Altri subtotali in rif1, rif2,... o subtotali annidati, non sono conteggiati.
SUBTOTALE trascura le righe escluse da un filtro e lavora su intervalli verticali; usato in intervalli orizzontali con numeri da 101 a 111 e nascondendo una colonna, non influenza il risultato, ma nascondendo una riga sì.

 

Per ottenere il subtotale degli stipendi per zona, è utile partire dalla scheda Dati, gruppo Struttura, icona Subtotale:

  • Ordinare i record per zona
  • Cliccare su scheda Dati, gruppo Struttura, icona Subtotale, configurare come segue e dare OK:
  • Si otterrà quanto segue:
  • Per rimuovere i subtotali riaprire la finestra di dialogo Subtotali e cliccare Rimuovi tutti

Segui ML Training sui Social:
Facebook - LinkedIn - Twitter - Google+

Posted: 26 Set 16 By: Category: Blog Read 950 times

Autore: Andrea Pacchiarotti

Per mediare i valori presenti in un intervallo, potendo eventualmente specificare uno o più criteri, Excel mette a disposizione 3 funzioni:
MEDIA, MEDIA.SE, MEDIA.PIÙ.SE
(Gli argomenti in rosso sono obbligatori, quelli in corsivo sono facoltativi)

MEDIA

Esegue la media aritmetica degli argomenti.
Sintassi: MEDIA(num1;num2;...)
Prevede i seguenti argomenti:
num1 numero da mediare.
num2;... da 2 a 255 argomenti da mediare.
L'argomento può essere un intervallo, un riferimento di cella, un numero; testo, valori logici e celle vuote sono ignorati, ma le celle con valore zero sono calcolate.
Se gli argomenti sono valori di errore o testo non convertibile in numeri generano errori.
Se da File/Opzioni di Excel/Impostazioni avanzate/Opzioni di visualizzazione per il foglio di lavoro si seleziona la casella di controllo Visualizza zero nelle celle con valore zero le celle vuote non verranno contate, a differenza delle celle contenenti il valore zero.

MEDIA.SE

Esegue la media aritmetica dei valori di un intervallo che soddisfano un criterio specificato.
Sintassi: MEDIA.SE(intervallo;criteri;Intervallodamediare)
Prevede i seguenti argomenti:
intervallo intervallo, contenente numeri, nomi, riferimenti o matrici, di celle da selezionare in base al criterio.
criterio numero, espressione, riferimento di cella, testo che specifica le celle da mediare.
ll criterio può usare riferimenti di cella, operatori relazionali e virgolette (A1, ">=10", "Rossi"). Inoltre contempla l’uso dei caratteri jolly (* e ?) e non fa distinzione tra maiuscole e minuscole.
Intervallodamediare 
indica le celle da mediare. Se intervallodamediare è omesso, saranno mediate le celle di intervallo. Le celle vuote in intervallodamediare sono ignorate.
Se l'intervallo è vuoto, contiene testo o non ha celle che soddisfano i criteri, MEDIA.SE restituisce l'errore #DIV0!.
Le celle vuote in criterio sono considerate come valori 0.

MEDIA.PIÙ.SE

Esegue la media aritmetica dei valori che soddisfano più di un criterio.
Sintassi: MEDIA.PIÙ.SE(intervallodamediare;intervallocriterio1;criterio1;intervallocriterio2; criterio2;...)
Prevede i seguenti argomenti:
Intervallodamediare indica le celle da mediare, inclusi numeri, nomi, intervalli o riferimenti di cella.
intervallocriterio1 intervallo in cui valutare il criterio associato.
criterio1 numero, espressione, riferimento di cella, testo che specifica le celle da addizionare.
ll criterio può usare riferimenti di cella, operatori relazionali e virgolette (A1, ">=10", "Rossi"). Inoltre contempla l’uso dei caratteri jolly (* e ?) e non fa distinzione tra maiuscole e minuscole.
intervallocriterio2;criterio2;... eventuali ulteriori intervalli e criteri fino ad un massimo di 127 coppie con il medesimo numero di righe e colonne di intervallocriterio1.
Se intervallodamediare è vuoto, è un valore di testo o contiene celle che non possono essere convertite in numeri, si avrà l’errore #DIV0!
Se non sono presenti celle che soddisfano tutti i criteri, si avrà l’errore #DIV/0!.
Le celle vuote in criterio sono considerate come valori 0.
Ogni intervallocriterio deve avere la stessa dimensione e la stessa forma di intervallodamediare.

Mediare con Excel di Andrea Pacchiarotti

Segui ML Training sui Social:
Facebook - LinkedIn - Twitter - Google+

Posted: 26 Set 16 By: Category: Blog Read 1005 times

Corso di Excel Avanzato in partenza a Roma

 

PROGRAMMA

 

Contattaci per maggiori informazioni !

 

E-mail: Questo indirizzo email è protetto dagli spambots. È necessario abilitare JavaScript per vederlo.

Tel. 06-83.089306

Cell. 340-4997705

 

 

 

 

Posted: 22 Set 16 By: Category: Blog Read 568 times

Autore: Andrea Pacchiarotti

Per sommare i valori presenti in un intervallo, potendo eventualmente specificare uno o più criteri, Excel mette a disposizione 3 funzioni:
SOMMA, SOMMA.SE, SOMMA.PIÙ.SE
(Gli argomenti in rosso sono obbligatori, quelli in corsivo sono facoltativi)

SOMMA

Addiziona i valori indicati negli argomenti.
Sintassi: SOMMA(num1;num2;...)
Prevede i seguenti argomenti:
num1 numero da sommare.
num2;... da 2 a 255 argomenti da sommare.
L'argomento può essere un intervallo, un riferimento di cella, una matrice, una costante, una funzione o il risultato di un'altra funzione; celle vuote, valori logici e testo sono ignorati.

SOMMA.SE

Addiziona i valori di un intervallo che soddisfano un criterio specificato.
Sintassi: SOMMA.SE(intervallo,criterio,intervallodasommare)
Prevede i seguenti argomenti:
intervallo intervallo, contenente numeri, nomi, riferimenti o matrici, di celle da selezionare in base al criterio; le celle vuote sono ignorate.
criterio numero, espressione, riferimento di cella, testo che specifica le celle da addizionare.
ll criterio può usare riferimenti di cella, operatori relazionali e virgolette (A1, ">=10", "Rossi"). Inoltre contempla l’uso dei caratteri jolly (* e ?) e non fa distinzione tra maiuscole e minuscole.
intervallodasommare indica le celle da addizionare. Se  intervallodasommare è omesso, saranno addizionate le celle di intervallo.

SOMMA.PIÙ.SE

Addiziona i valori di un intervallo che soddisfano più di un criterio.
Sintassi: SOMMA.PIÙ.SE(intervallodasommare;intervallocriterio1;criterio1;intervallocriterio2;criterio2;...)
Prevede i seguenti argomenti:
intervallodasommare indica le celle da addizionare, inclusi numeri, nomi, intervalli o riferimenti di cella; le celle vuote sono ignorate.
intervallocriterio1 intervallo in cui valutare il criterio associato.
criterio1 numero, espressione, riferimento di cella, testo che specifica le celle da addizionare.
ll criterio può usare riferimenti di cella, operatori relazionali e virgolette (A1, ">=10", "Rossi"). Inoltre contempla l’uso dei caratteri jolly (* e ?) e non fa distinzione tra maiuscole e minuscole.
intervallocriterio2;criterio2;... eventuali ulteriori intervalli e criteri fino ad un massimo di 127 coppie con il medesimo numero di righe e colonne di intervallocriterio1.

 Sommare con Excel di Andrea Pacchiarotti

Segui ML Training sui Social:
Facebook - LinkedIn - Twitter - Google+

Posted: 21 Set 16 By: Category: Blog Read 916 times
ML TRAINING

    Via Sergio Forti, 23
00144 Roma // Italy

    +39 06 830 893 06
   +39 06 830 893 06

    Questo indirizzo email è protetto dagli spambots. È necessario abilitare JavaScript per vederlo.