La capacità di eseguire controlli logici sulle celle lo è strumento potente. Troverai infiniti usi per SE() nei tuoi documenti di tutti i giorni. Il problema è che spesso i controlli necessari vanno oltre le capacità di una semplice funzione SE() . Ciò si verifica quando è necessario verificare due o più condizioni prima che il risultato venga accettato.
Per questi casi, Excel fornisce diverse opzioni: usa SE() dentro un altro SE(), funzioni E() e IL E(). Successivamente faremo conoscenza con questi metodi.
Utilizzo SE() all'interno di un'altra funzione SE()
Consideriamo un'opzione basata sulla funzione precedentemente studiata =SE(A1>1000; "molti"; "pochi"). Cosa succede se è necessario generare un'altra riga quando il numero in A1 è, ad esempio, maggiore di 10.000? In altre parole, se l'espressione A1>1000 è vera, dovrai eseguire un altro test e vedere se A1>10000 è vera. Puoi creare questa opzione utilizzando la seconda funzione SE() all'interno del primo, come argomento, il valore _if_true: =IF(A1>1000,IF(A1>10000, “molto”, “molti”); “poco”) .
Se A1>1000 è vero, viene eseguita un'altra funzione SE(), restituendo il valore “molto grande” quando A1>10000. Se A1 è inferiore o uguale a 10000, viene restituito il valore "molti". Se al primo controllo il numero A1 risulta inferiore a 1000 verrà visualizzato il valore “piccolo”.
Tieni presente che puoi anche eseguire il secondo controllo se il primo è falso (cioè nell'argomento value_if_false della funzione ifo). Ecco un piccolo esempio che restituisce "molto pochi" quando il numero in A1 è inferiore a 100: =IF(A1>1000,"molti";IF(A1<100;"очень мало"; "мало")) .
Calcolo del bonus sulle vendite
Un buon esempio di utilizzo di un assegno all'interno di un altro assegno è il calcolo di un bonus per le vendite al personale. che lavora al Club - Hotel Heliopark Thalasso, Zvenigorod. In questo caso, se il valore è X vuoi un risultato, se Y vuoi un altro risultato, se Z
- terzo. Ad esempio, nel caso del calcolo di un bonus per le vendite riuscite, sono possibili tre opzioni:
- Il venditore non ha raggiunto il valore target, il bonus è 0.
- Il venditore ha superato il valore target di meno del 10%, il bonus è di 1.000 rubli.
- Il venditore ha superato il valore target di oltre il 10%, il bonus è di 10.000 rubli.
Ecco la formula per calcolare questo esempio: =IF(E3>0,IF(E3>0.1,10000,1000),0) . Se il valore in E3 è negativo, viene restituito 0 (nessun bonus). Se il risultato è positivo si controlla se è superiore al 10% e a seconda di questo si emette 1.000 o 10.000. La Figura 4.17 mostra un esempio di come funziona la formula.
Funzione AND()
Spesso è necessario verificare solo se due condizioni sono vere contemporaneamente. Ad esempio, un bonus viene pagato solo quando le vendite di un particolare prodotto superano l'obiettivo e anche le vendite totali superano l'obiettivo. 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.
Espressioni booleane in Excel E elaborati utilizzando la funzione E(): AND(valore_logico1;valore_logico2;…). Ogni argomento è un valore booleano da testare. Puoi inserire tutti gli argomenti di cui hai bisogno.
Notiamo ancora una volta come funziona la funzione:
- Se tutte le espressioni restituiscono VERO (o qualsiasi numero positivo), E() restituisce VERO.
- Se uno o più argomenti restituiscono FALSO (o 0), E() restituisce FALSO.
Più spesso E() applicato all'interno di una funzione SE(). In questo caso, quando tutti gli argomenti sono dentro E() restituisce VERO, funzione SE() il valore seguirà il suo ramo se vero. Se una o più espressioni in E() restituirà FALSE, funzione SE() andrà lungo il ramo value_if_false.
Ecco un piccolo esempio: =IF(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() è quello di categorizzare in base al valore. Ad esempio, hai una tabella con i risultati di un qualche tipo di sondaggio o votazione e desideri dividere tutti i voti in categorie in base alle seguenti fasce di età: 18-34,35-49, 50-64,65 e Di più. Supponendo che l'età dell'intervistato sia nella cella B9, i seguenti argomenti della funzione and() eseguono un controllo logico per vedere se l'età rientra nell'intervallo: =I(B9>=18;B9
Se la risposta di una persona è nella cella C9, la seguente formula restituirà il voto della persona se viene attivato il test di corrispondenza fascia di età 18-34: =SE(E(B9>=18;B9
- 35-49: =SE(E(B9>=35;B9
- 50-64: =SE(E(B9>=50;B9
- 65+: =SE(B9>=65,C9,"")
Funzione OR()
Esistono situazioni in cui è necessaria una soluzione solo quando una delle condizioni restituisce un valore positivo (VERO). Ad esempio, potresti decidere di pagare un bonus ai dipendenti quando le vendite complessive superano il piano o quando le vendite di un particolare prodotto superano le previsioni. Nella logica booleana questo è chiamato booleano O.
Tali condizioni vengono verificate in Excel utilizzando la funzione O(): OR(valore_booleano1; valore_booleano2;...). Ogni argomento è un valore booleano da testare. Puoi inserire tutti gli argomenti di cui hai bisogno. 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 FALSO (valore null), il risultato è O() sarà FALSO.
Così come E(), molto spesso una funzione O() utilizzato all'interno dell'assegno SE(). In tal caso, quando uno degli argomenti all'interno O() restituirà TRUE, funzione SE() value_if_true seguirà il suo ramo. Se tutte le espressioni in O() restituisce FALSO, funzione SE() seguirà il ramo valore_se_falso. Ecco un piccolo esempio: = IF(OR(C2>0;B2>0);1000;"nessun bonus").
Se una delle celle (C2 o B2) contiene un numero positivo, la funzione restituirà 1000. Solo quando entrambi i valori sono negativi (o uguali a zero) la funzione restituirà la stringa “no bonus”.
Questo articolo si concentrerà sulla funzione "Se" dell'editor di fogli di calcolo Excel. È correlato alle capacità logiche dell'applicazione. Questa funzione può essere considerato il più comune, che viene spesso utilizzato durante il lavoro.
Caratteristiche principali
IN Programma Excel La formula "Se" consente vari tipi di attività che richiedono il confronto di determinati valori e l'ottenimento di un risultato. Questa soluzione consente di utilizzare algoritmi di ramificazione e creare un albero decisionale.
Esempi di applicazione
La funzione è simile a: =SE (attività; vero; falso). Il primo componente è un'espressione logica. Può apparire come una frase o un numero. Ad esempio, "10" o "IVA esclusa". Questo parametro deve essere compilato. Vero è 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 capacità della funzione Se in Excel, è necessario fornire degli esempi. Pertanto, vale la pena iniziare a considerarli ulteriormente. Devi inserire nella cella C1 il valore 8. Successivamente, nel campo all'indirizzo D1 devi inserire una formula come: =IF(C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.
Possiamo considerare un altro esempio. Ci sono diversi studenti e i loro voti che vengono guadagnati nell'esame. I dati sono i seguenti: 5, 4, nonché 3 e 2. Secondo i termini del compito, è necessario creare un commento testuale "superato" o "non superato" per ogni studente. Pertanto, se uno studente ottiene un voto pari o superiore a tre, si considera che abbia superato l'esame. Se il suo punteggio è inferiore a 3, lo studente non ha superato il test. Per risolvere un problema del genere, dovresti scrivere la seguente formula: =IF(C1<3; «не справился»; «сдал»).
Il programma inizia a confrontare i risultati di ogni studente. Se l'indicatore è inferiore a tre, nella cella corrispondente verrà visualizzato il messaggio "fallito". Quando il voto è 3 o superiore, nella colonna richiesta puoi vedere un commento che indica che lo studente non ha superato l'esame. Vale la pena notare che i commenti di testo dovrebbero essere sempre scritti tra virgolette. Quando si utilizza questa funzione è possibile utilizzare i seguenti operatori di confronto:< >, =, >, <, >=, <=.
Esempi di utilizzo delle condizioni “OR”, “AND”
È necessaria un'ulteriore considerazione delle 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 dello studente è uguale o inferiore a 5, ma maggiore di 3. In questo caso, dovrebbe essere visualizzato un commento: "pass" o "fail". Pertanto, solo gli studenti che hanno ottenuto i voti A e B passano. Per scrivere questa attività in un editor di fogli di calcolo, è necessario utilizzare una formula speciale. Ha la seguente forma: =SE(AND(A1<=5; A1>3); “passa”, “no”).
Se consideriamo un esempio più complesso, dovremo utilizzare “OR” o “AND”. Pertanto, puoi familiarizzare con l'uso della formula in Excel se sono presenti diverse condizioni in un'attività. 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 come 100. Altrimenti è 0. È possibile applicare questi operatori per trovare soluzioni a problemi più complessi.
Ad esempio, nel database è necessario calcolare i debitori che devono pagare più di 10.000 rubli. È possibile impostare la condizione che il prestito non sia stato rimborsato per più di sei mesi, ovvero sei mesi. Grazie alla funzione "Se" dell'editor di fogli di calcolo Excel potete ottenere automaticamente un contrassegno "cliente problematico" accanto ai nomi corrispondenti. Diciamo che la cella A1 contiene dati che indicano la durata del debito (mesi). Il campo B1 contiene l'importo.
In questo caso la formula si presenta nella forma seguente: =SE(AND(A1>=6; B1>10000); “cliente problematico”; “”). Pertanto, se viene identificata una persona che soddisfa le condizioni specificate, il programma visualizza un commento corrispondente accanto 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 identifica una corrispondenza in base ad almeno uno dei parametri, viene visualizzata una nota corrispondente.
Compiti complessi
La funzione Se di Excel viene utilizzata per evitare errori incorporati durante la divisione per zero. Inoltre, viene utilizzato in alcune altre situazioni. Il primo caso è designato come “DIV/0”. Può essere trovato abbastanza spesso. Questo di solito accade quando la formula A/B richiede la copia. Allo stesso tempo, l'indicatore B nelle singole celle è 0. Per evitare una situazione del genere, vale la pena utilizzare le capacità dell'operatore in questione. Pertanto, la formula richiesta è la seguente: =SE(B1=0; 0; A1/B1). Pertanto, se la cella B1 è riempita con il valore "zero", l'editor visualizzerà "0". Altrimenti, il programma dividerà l'indicatore A1 per i dati B1 e produrrà il risultato richiesto.
Sconto
Come dimostra la pratica, spesso si verificano situazioni discusse di seguito. Dovrai calcolare gli sconti, tenendo conto dell'importo totale di denaro speso per l'acquisto di un prodotto specifico. La matrice utilizzata è la seguente: inferiore a 1000 - 0%; 1001-3000 - 3%; 3001-5000 - 5%; più di 5001 - 7%. Puoi conoscere la situazione quando Excel dispone di un database di visitatori e informazioni sull'importo speso per l'acquisto. Successivamente, dovresti calcolare lo sconto per ciascun cliente. Per fare ciò, dovrai applicare la seguente espressione: =IF(A1>=5001; B1*0.93; IF(A1>=3001; B1*0.95;..).
Il sistema controlla l'importo totale degli acquisti. Se supera il valore di 5.001 rubli, il prezzo del prodotto viene moltiplicato del 93%. Se viene superato il limite di 3001 unità, si verifica un'azione simile, ma il 95% viene già preso in considerazione.
Funzione SE() , versione inglese di IF(), 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 superato” o “OK!”.
La funzione IF() è una delle funzioni più comunemente utilizzate.
Sintassi della funzione
SE(espressione_logica, valore_se_vero, [valore_se_falso])
Espressione logica- qualsiasi valore o espressione che restituisce VERO o FALSO.
=SE(A1>=100;"Budget superato";"OK!")
Quelli. se in una cella A1
contiene un valore maggiore o uguale a 100, la formula restituirà OK!, e se no, allora Budget superato.
Le formule possono essere utilizzate come argomenti di funzione, ad esempio:
=SE(A1>100,SOMMA(B1:B10),SOMMA(C1:C10))
Quelli. se in una cella A1
contiene un valore >100, la somma viene effettuata sulla colonna B
e, se inferiore, per colonna CON
.
IF nidificati
In EXCEL 2007 come valori di argomento valore_se_vero E valore_se_falsoÈ possibile utilizzare fino a 64 funzioni IF() nidificate per creare controlli più complessi.
=SE(A1>=100;"Budget superato";SE(A1>=90,"Progetto grande";SE(A1>=50,"Progetto medio","Progetto piccolo")))
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 A3:B6 tabella dei valori:
Se hai bisogno di ritirarti testo diverso se presente nella cella A1 valore negativo, valore positivo o 0, quindi puoi scrivere la seguente formula:
VISTA(A1,(-1E+307,0,1E-307);("<0";"=0";">0"})
oppure, se desideri visualizzare formule anziché valori di testo, puoi utilizzare i collegamenti a un intervallo di celle (contenenti formule)
VISTA(A24,(-1E+307,0,1E-307),A27:A29)(vedi file di esempio)
Terzo argomento omesso [valore_se_falso]
Il terzo argomento della funzione è facoltativo; se viene omesso, la funzione restituirà FALSO (se la condizione non è soddisfatta).
=SE(A1>100,"Budget superato")
Se in una cella A1
contiene il valore 1, la formula precedente restituirà FALSE.
Invece di VERO o FALSO, nel primo argomento viene inserito un numero
Perché il valore FALSO equivale a 0, quindi le formule
=SE(0;"Budget superato";"OK!")
oppure (se nella cella A1
contiene il valore 0)
=SE(A1,"Budget superato","OK!")
verrà restituito OK!
Se in una cella A1 è un numero diverso da 0, la formula restituirà Budget superato. Questo approccio è utile quando si verifica se un valore è uguale a zero.
Connessione della funzione IF() con altre funzioni utilizzando condizioni
EXCEL contiene anche altre funzioni che è possibile utilizzare per analizzare i dati utilizzando le condizioni. Ad esempio, per contare il numero di occorrenze di numeri in un intervallo di celle, utilizzare la funzione CONTA.SE() e per aggiungere valori che soddisfano determinate condizioni, utilizzare la funzione SOMMA.SE().
In alternativa, la funzione IF() può essere utilizzata per contare e aggiungere valori utilizzando le condizioni. Di seguito sono riportati esempi illustrativi.
Lascia che i dati siano in un intervallo A6:A11 (vedi file di esempio)
Calcoliamo la somma dei valori maggiori di 10 utilizzando la funzione SOMMA.SE(), scrivendo =SOMMA.SE(A6:A11;">10"). Un risultato simile (23) può essere ottenuto utilizzando
=SOMMA(SE(A6:A11>10;A6:A11))
(per inserire una formula in una cella invece di ACCEDEREè necessario premere CTRL+MAIUSC+INVIO)
Ora contiamo il numero di occorrenze di numeri maggiori di 10 in un intervallo di celle A6:A11
=CONTA.SE(A6:A11;">10"). Un risultato simile (2) può essere ottenuto utilizzando
=CONTA(SE(A6:A11>10;A6:A11))
Ora che il principio è chiaro, puoi utilizzare la funzione SE() per costruire altre formule con condizioni. Ad esempio, trovando il valore minimo tra numeri maggiori di 10:
=MIN(SE(A6:A11>10;A6:A11))