Come usare la funzione se poi in Excel. Come in Excel utilizzando le funzioni nidificate se () calcola il bonus sulle vendite?

La capacità di eseguire controlli logici sulle celle è uno strumento potente. Troverai infiniti usi per SE UN() nei tuoi documenti quotidiani. Il problema è che spesso i controlli necessari vanno oltre le capacità di una semplice funzione. SE UN() ... Questo accade quando è necessario verificare due o più condizioni prima di accettare il risultato.

Per tali casi, Excel offre diverse opzioni: utilizzare SE UN() dentro un altro SE UN(), funzioni E() e IL E()... Successivamente, faremo conoscenza con questi metodi.

Usando SE UN() dentro un'altra funzione SE UN()

Consideriamo una variante basata sulla funzione studiata in precedenza = SE (A1> 1000; "molto"; "poco"). Cosa succede se è necessario stampare un'altra stringa quando il numero in A1 è, ad esempio, maggiore di 10.000? In altre parole, se A1> 1000 è vero, vorrai eseguire un altro test e vedere se A1> 10000 è vero. Puoi creare una tale variante applicando la seconda funzione SE UN() all'interno del primo come argomento, il valore _if_true: = IF (A1> 1000; IF (A1> 10000; "molto"; "molti"); "poco").

Se A1> 1000 è vero, viene attivata un'altra funzione SE UN(), che restituisce il valore "molto" quando A1> 10000. Se in questo caso A1 è minore o uguale a 10000, viene restituito il valore "molto". Se durante il primo controllo il numero A1 è inferiore a 1000, verrà visualizzato il valore "poco".

Nota che puoi anche eseguire il secondo controllo se il primo è falso (cioè, nell'argomento valore_se_falso della funzione ifo). Ecco un piccolo esempio che restituisce il valore "molto piccolo" quando il numero in A1 è minore di 100: = SE (A1> 1000; "molti"; SE (A1<100;"очень мало"; "мало")) .

Calcolo del bonus di vendita

Un buon esempio di utilizzo di un assegno all'interno di un altro è calcolare un bonus di vendita al personale. che lavora nel Club - hotel Heliopark Talasso, Zvenigorod. In questo caso, se il valore è X, vuoi un risultato, se Y è un altro, se Z
- il terzo. Ad esempio, nel caso di calcolo di un bonus per vendite andate a buon fine, sono possibili tre opzioni:

Il venditore non ha raggiunto il valore pianificato, il bonus è 0.
Il venditore ha superato il valore pianificato di meno del 10%, il bonus è di 1.000 rubli.
Il venditore ha superato il valore pianificato di oltre il 10%, il bonus è pari a 10.000 rubli.

Ecco la formula per calcolare un tale esempio: = SE (E3> 0; SE (E3> 0,1; 10000; 1000); 0). Se il valore in E3 è negativo, viene restituito 0 (nessun bonus). Se il risultato è positivo, viene verificato se è superiore al 10% e, a seconda di ciò, vengono emessi 1.000 o 10.000. 4.17 mostra un esempio di come funziona la formula.

AND () funzione

Spesso è necessario verificare solo la fedeltà di due condizioni contemporaneamente. Ad esempio, un bonus viene pagato solo quando le vendite di un determinato prodotto hanno superato il piano e anche le vendite totali hanno superato il piano. Se una di queste condizioni non è soddisfatta (o entrambe le condizioni), il premio non viene pagato. Nella logica booleana, questo è chiamato AND logico perché entrambe le condizioni devono essere vere affinché l'intera espressione sia vera.

In Excel, espressioni booleane E vengono elaborati utilizzando la funzione E(): AND (valore_booleano1; valore_booleano2; ...). Ogni argomento è un valore booleano da testare. Puoi inserire tutti gli argomenti che desideri.

Notiamo come funziona di nuovo la funzione:

Se tutte le espressioni restituiscono VERO (o qualsiasi numero positivo), E() restituisce VERO.
Se uno o più argomenti restituiscono FALSE (o 0), E() restituisce FALSO.

Più spesso E() applicato all'interno di una funzione SE UN()... In questo caso, quando tutti gli argomenti sono all'interno E() restituirà VERO, funzione SE UN() seguirà il suo valore di ramo se vero. Se una o più espressioni in E() restituirà FALSE, la funzione SE UN() seguirà il ramo value_if_false.

Ecco un piccolo esempio: = SE (AND (C2> 0; B2> 0); 1000; "nessun bonus"). Se il valore in B2 è maggiore di zero e il valore in C2 è maggiore di zero, la formula restituirà 1000, altrimenti verrà visualizzata la riga "nessun bonus".

Separare i valori in categorie

Un uso utile della funzione and () consiste nella categorizzazione in base al valore. Ad esempio, hai una tabella con i risultati di un sondaggio o di un voto e vuoi dividere tutti i voti in categorie in base alle seguenti fasce di età: 18-34,35-49, 50-64,65 o più. Supponendo che l'età del rispondente sia nella cella B9, i seguenti argomenti della funzione e () eseguono un controllo logico per l'età appartenente all'intervallo: = AND (B9> = 18; B9

Se la risposta della persona è nella cella C9, la seguente formula restituirà il risultato della votazione della persona se viene attivato il controllo di conformità con la fascia di età 18-34: = SE (AND (B9> = 18; B9

35-49: = SE (AND (B9> = 35; B9
50-64: = SE (AND (B9> = 50; B9
65+: = SE (LA9> = 65; DO9; "")

Funzione OR ()

Ci sono situazioni in cui è necessaria una decisione solo quando una delle condizioni è positiva (VERO). Ad esempio, potresti decidere di pagare un bonus ai dipendenti quando le vendite totali superano l'obiettivo o quando le vendite di un singolo articolo sono superiori alle previsioni. In logica booleana, questo si chiama booleano O.

Tali condizioni vengono verificate in Excel utilizzando la funzione O(): OR (booleano1, booleano2, ...). Ogni argomento è un valore booleano da testare. Puoi inserire tutti gli argomenti che desideri. Risultato del lavoro O() dipende dalle seguenti condizioni:

Se uno o più argomenti restituiscono VERO (qualsiasi numero positivo), O() restituisce VERO.
Se tutti gli argomenti restituiscono FALSE (zero), il risultato è O() sarà FALSO.

Proprio come E(), il più delle volte la funzione O() usato all'interno del controllo SE UN()... In questo caso, quando uno degli argomenti all'interno O() restituirà VERO, funzione SE UN() seguirà il suo ramo value_if_true. Se tutte le espressioni in O() restituirà FALSE, la funzione SE UN() seguirà il ramo valore_se_falso... Ecco un piccolo esempio: = SE (OR (C2> 0; B2> 0); 1000; "nessun bonus").

Se è presente un numero positivo in una delle celle (C2 o B2), la funzione restituirà 1000. Solo quando entrambi i valori sono negativi (o uguali a zero), la funzione restituirà la stringa "nessun bonus".

Questo articolo si concentrerà sulla funzione "Se" dell'editor di fogli di calcolo Excel. Si occupa delle capacità logiche dell'applicazione. Questa funzione può essere attribuita al più comune, che viene spesso utilizzato durante il funzionamento.

Caratteristiche principali

NEL Programma Excel la formula "Se" consente vari tipi di attività che richiedono di confrontare determinati valori e ottenere un risultato. Questa soluzione consente di utilizzare algoritmi di ramificazione, creare un albero decisionale.

Esempi di applicazioni

La funzione ha il seguente aspetto: = SE (attività; vero; falso). La prima parte è un'espressione booleana. Può apparire come una frase o un numero. Ad esempio, "10" o "senza IVA". Questo parametro deve essere compilato. True è il valore visualizzato come risultato se l'espressione è vera. False rappresenta i dati che verranno restituiti quando l'attività non è corretta.

Uguaglianza dei parametri di due celle

Per comprendere meglio le funzionalità della funzione If in Excel, è necessario fornire esempi. Pertanto, vale la pena iniziare a considerarli ulteriormente. È necessario inserire il valore 8 nella cella C1. Successivamente, nel campo all'indirizzo D1, è necessario inserire una formula del modulo: = IF (C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.

Si può considerare un altro esempio. Ci sono diversi studenti e i loro voti che vengono guadagnati per l'esame. I dati sono i seguenti: 5, 4, nonché 3 e 2. In base ai termini del compito, si prevede di creare per ogni studente un commento testuale “superato” o “non superato”. Pertanto, se uno studente riceve un voto di tre o più, si considera superato l'esame. Se il suo voto è inferiore a 3, lo studente non è andato bene. Per risolvere un problema del genere, dovresti scrivere la seguente formula: = SE (C1<3; «не справился»; «сдал»).

Il programma inizia confrontando i risultati di ogni studente. Se l'indicatore è inferiore a tre, nella cella corrispondente apparirà la scritta "fallito". Quando il voto è 3 o superiore, nella colonna richiesta puoi vedere un commento che lo studente non ha superato l'esame. Vale la pena notare che i commenti di testo devono essere sempre racchiusi tra virgolette. Quando si utilizza questa funzione, è possibile utilizzare i seguenti operatori di confronto:< >, =, >, <, >=, <=.

Esempi che utilizzano le condizioni "OR", "AND"

È necessario considerare ulteriormente le capacità logiche di questa applicazione. È possibile combinare la funzione If con operatori di confronto. Questi sono i seguenti parametri:

"O";
"E".

È necessario indicare una condizione importante in Excel: quando il voto di uno studente è uguale o inferiore a 5, ma superiore a 3. In questo caso, dovrebbe essere visualizzato un commento: "passa" o "no". Pertanto, solo gli studenti che hanno guadagnato cinque e quattro passano. Per scrivere questa attività in un editor di fogli di calcolo, dovresti applicare una formula speciale. Assomiglia a questo: = SE (AND (A1<=5; A1>3); "Pass", "no").

Se consideri un esempio più complesso, dovrai usare "OR" o "AND". Pertanto, puoi familiarizzare con l'applicazione di una formula in Excel se ci sono più condizioni nel lavoro. Ad esempio: = SE (OR (A1 = 5; A1 = 10); 100; 0). In questo caso, possiamo concludere che quando il valore nella cella A1 è 5 o 10, il programma visualizza il risultato 100. Altrimenti è 0. È possibile utilizzare questi operatori per trovare una soluzione a problemi più complessi.

Ad esempio, nel database è necessario calcolare i debitori che devono pagare più di 10.000 rubli. Puoi impostare una condizione che il prestito non sia stato rimborsato per più di sei mesi, cioè sei mesi. Grazie alla funzione "Se" dell'editor di fogli di calcolo Excel, è possibile ottenere automaticamente l'indicazione "cliente problematico" accanto ai nomi corrispondenti. Supponiamo che la cella A1 contenga dati che indicano la durata del debito (mesi). Il campo B1 contiene l'importo.

In questo caso, la formula si presenta nella seguente forma: = IF (AND (A1> = 6; B1> 10000); "cliente problematico"; ""). Pertanto, se viene identificata una persona che soddisfa le condizioni specificate, il programma visualizza il commento corrispondente davanti al suo nome. Per gli altri membri dell'elenco, questa cella rimarrà vuota.

Puoi anche vedere un esempio quando la situazione è critica. È necessario inserire un commento appropriato. Di conseguenza, la formula sarà la seguente: = SE (OR (A1> = 6; B1> 10000); "situazione critica"; ""). Tuttavia, se il programma rileva corrispondenze per almeno uno dei parametri, viene visualizzata una nota corrispondente.

Compiti impegnativi

La funzione If di Excel viene utilizzata per evitare la divisione incorporata per zero errori. Inoltre, viene utilizzato anche in alcune altre situazioni. Il primo caso è designato come "DIV/0". Si può trovare abbastanza spesso. Questo di solito accade quando la formula "A/B" richiede la copia. Allo stesso tempo, l'indicatore B in singole celluleè 0. Per evitare una situazione del genere, vale la pena utilizzare le capacità dell'operatore in questione. Pertanto, la formula richiesta è: = SE (B1 = 0; 0; A1 / B1). Quindi, se la cella B1 è riempita con un valore di "zero", l'editor visualizzerà "0". In caso contrario, il programma dividerà l'indicatore A1 per i dati B1 e fornirà il risultato richiesto.

Sconto

Come dimostra la pratica, spesso sorgono situazioni, che sono discusse di seguito. Dovrai calcolare gli sconti in base all'importo totale speso per l'acquisto di un determinato articolo. La matrice utilizzata è la seguente: inferiore a 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; più di 5001 - 7%. Puoi vedere la situazione in cui Excel ha un database di visitatori e informazioni sull'importo speso per un acquisto. Il passo successivo è calcolare lo sconto per ogni cliente. Per fare ciò, è necessario applicare la seguente espressione: = IF (A1> = 5001; B1 * 0,93; IF (A1> = 3001; B1 * 0,95; ..).

Il sistema verifica l'importo totale dell'acquisto. Se supera il valore di 5001 rubli, la moltiplicazione viene eseguita per il 93 percento del prezzo dell'articolo. Se viene superato il limite di 3001 unità, si verifica un'azione simile, ma il 95% è già preso in considerazione.

Funzione SE () , versione inglese SE (), utilizzato durante il controllo delle condizioni. Per esempio, = SE (A1> 100; "Budget superato"; "OK!")... A seconda del valore nella cella A1 il risultato della formula sarà "Budget Exceeded" o "OK!"

La funzione IF() è una delle funzioni più comunemente utilizzate.

Sintassi della funzione

SE (test_booleano, valore_se_vero, [valore_se_falso])

Log_Expression- qualsiasi valore o espressione che assume i valori VERO o FALSO.
= SE (A1> = 100; "Budget superato"; "OK!")
quelli. se nella cella A1 contiene un valore maggiore o uguale a 100, quindi la formula ritornerà OK!, e se no, allora Budget superato.

Le formule possono essere utilizzate come argomenti della funzione, ad esempio:
= SE (A1> 100, SOMMA (B1: B10), SOMMA (C1: C10))
quelli. se nella cella A1 contiene un valore > 100, quindi la somma viene eseguita sulla colonna B , e se inferiore, lungo la colonna A PARTIRE DAL .

SE nidificato

In EXCEL 2007 come valori di argomento valore_se_vero e valore_se_falsoè possibile utilizzare fino a 64 funzioni IF() annidate l'una nell'altra per creare controlli più complessi.
= SE (A1> = 100; "Budget superato"; SE (A1> = 90; "Grande progetto"; SE (A1> = 50; "Medio progetto"; "Piccolo progetto")))

VIEW (A1; (0; 50; 90; 100); ("Progetto piccolo"; "Progetto medio"; "Progetto grande"; "Budget superato"))

CERCA.VERT (A1; A3: B6; 2)

Per la funzione CERCA.VERT (), è necessario creare nell'intervallo LA3: SI6 tabella dei valori:

Se vuoi visualizzare un testo diverso se è nella cella A1 valore negativo, valore positivo o 0, quindi puoi scrivere la seguente formula:

CERCA (LA1; (- 1E + 307; 0; 1E-307); ("<0";"=0";">0"})

oppure, se desideri visualizzare formule anziché valori di testo, puoi utilizzare riferimenti a un intervallo di celle (contenenti formule)

CERCA (LA24; (- 1E + 307; 0; 1E-307); A27: A29)(vedi file di esempio)

Terzo argomento omesso [valore_se_falso]

Il terzo argomento della funzione è facoltativo; se lo ometti, la funzione restituirà FALSE (se la condizione non è soddisfatta).
= SE (A1> 100; "Budget superato")
Se in una cella A1 contiene il valore 1, la formula sopra restituirà FALSE.

Il numero è stato inserito al posto di VERO o FALSO nel primo argomento

Perché il valore FALSE è equivalente a 0, quindi le formule
= SE (0, "Budget superato"; "OK!")
o (se nella cella A1 contiene il valore 0)
= SE (A1, "Budget superato"; "OK!")
tornerà OK!

Se in una cella A1 viene trovato qualsiasi numero diverso da 0, quindi la formula ritornerà Budget superato... Questo approccio è utile quando si controlla che un valore sia uguale a zero.

Collegamento della funzione IF () con altre funzioni utilizzando le condizioni

EXCEL contiene anche altre funzioni che è possibile utilizzare per analizzare i dati utilizzando le condizioni. Ad esempio, la funzione CONTA.SE () viene utilizzata per contare il numero di occorrenze di numeri in un intervallo di celle e la funzione SOMMA.SE () viene utilizzata per aggiungere valori che soddisfano determinate condizioni.

La funzione IF(), in alternativa, può essere utilizzata anche per contare e aggiungere valori utilizzando le condizioni. Di seguito vengono forniti esempi illustrativi.

Lascia che i dati siano nell'intervallo LA6: LA11 (vedi file di esempio)

Calcoliamo la somma dei valori maggiori di 10 utilizzando la funzione SOMMA.SE() scrivendo = SOMMA.SE (LA6: LA11, "> 10")... Un risultato simile (23) può essere ottenuto usando
= SOMMA (SE (LA6: A11> 10, A6: A11))
(per inserire una formula in una cella invece di ACCEDERE bisogno di premere CTRL + MAIUSC + INVIO)

Ora contiamo il numero di occorrenze di numeri maggiori di 10 nell'intervallo di celle LA6: LA11 = CONTA.SE (A6: A11, "> 10")... Un risultato simile (2) può essere ottenuto usando
= CONTEGGIO (SE (LA6: LA11> 10, LA6: LA11))

Ora che il principio è chiaro, è possibile costruire altre formule con condizioni utilizzando la funzione IF(). Ad esempio, trovando il valore minimo tra numeri maggiori di 10:
= MIN (SE (LA6: A11> 10, A6: A11))