La funzione del SUMMESLE, così come i SUMMESLES secondo due criteri

  1. Cerca per tag
azione furtiva »11 giugno 2011 Dmitry 243582 visualizzazioni

Immagina una tabella in cui i nomi dei reparti (o account o qualcos'altro) sono elencati in righe di fila.

Sommare le celle per criterio
È necessario calcolare l'importo totale per ciascun dipartimento. Molti lo fanno con un filtro e scrivendo con le penne nelle celle.
Anche se può essere fatto facilmente e semplicemente con una sola funzione - SUMMESLI .
SUMMESLES (SUMIF): esegue il backup delle celle che soddisfano una determinata condizione (è possibile specificare una sola condizione). Questa funzione può essere utilizzata anche se la tabella è divisa in colonne per periodi (mensili, in ciascun mese, tre colonne - Entrate | Spese | Differenza) e devi calcolare l'importo totale per tutti i periodi solo per Entrate, Spese e Differenza.

Ci sono tre argomenti in totale per SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESLE (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

  • Intervallo (A1: A20000): indica l'intervallo con i criteri. ie La colonna in cui cercare il valore indicato dall'argomento Criterio .
  • Il criterio (A1) è il valore (testo o numerico, nonché la data) che deve essere trovato nell'intervallo. Può contenere i caratteri jolly "*" e "?". ie specificando il criterio "* massa *" per riepilogare i valori in cui si verifica la parola "massa". Allo stesso tempo, la parola "massa" può avvenire in qualsiasi parte del testo, oppure può esserci solo una parola in una cella. E specificando "massa *", verranno sommati tutti i valori che iniziano con "massa". "?" - sostituisce solo un carattere, ad es. specificando "mas? a" puoi sommare le righe con il valore "massa" e il valore "maschera", ecc.
    Se il criterio è scritto in una cella e hai ancora bisogno di utilizzare caratteri jolly, puoi creare un collegamento a questa cella aggiungendo quello necessario. Supponiamo di dover riassumere i valori che contengono la parola "totale". La parola "totale" è scritta nella cella A1, mentre nella colonna A possono essere presenti vari valori di ortografia contenenti la parola "totale": "totali per giugno", "totali per luglio", "totali per marzo". La formula dovrebbe quindi assomigliare a questa:
    = ESTATE (A1: A20000; "*" & A1 & "*"; B1: B20000)
    "*" & A1 & "*" - il segno & (e commerciale) combina diversi valori in uno. ie il risultato sarà "* risultato *".
    Per capire meglio il principio di come funzionano le formule, è meglio usare lo strumento Calcola formula : Come visualizzare i passaggi per calcolare le formule
    Tutti i criteri testuali e i criteri con segni logici e matematici devono essere racchiusi tra virgolette (= SUMMESLI (A1: A20000; "totale"; B1: B20000)). Se il criterio è un numero, le virgolette non sono obbligatorie. Se vuoi trovare direttamente un punto interrogativo o un asterisco, devi metterne una tilde (~) di fronte.
    Informazioni sulla tilde e le sue caratteristiche sono disponibili in questo articolo: Come sostituire / rimuovere / trovare l'asterisco?
  • Sum_Range (B1: B20000) (argomento opzionale) - specifica l'intervallo di somme o valori numerici da sommare.

Come funziona: la funzione cerca nell'intervallo il valore specificato dall'argomento Criterio e, quando viene trovata una corrispondenza, somma i dati indicati dall'argomento Range_Amount. ie se abbiamo un nome di reparto nella colonna A e un importo nella colonna B, quindi specificando il Dipartimento di sviluppo come criterio si otterrà la somma di tutti i valori della colonna B, opposti a quelli del Reparto Sviluppo che si trovano nella colonna A. In effetti, il SumArrangement potrebbe non avere la stessa dimensione dell'argomento Range e questo non causerà un errore della funzione stessa. Tuttavia, quando si definiscono le celle per la sommatoria, la cella in alto a sinistra dell'argomento Range_Amount verrà utilizzata come cella di partenza per la sommatoria, quindi verranno sommate le celle corrispondenti in dimensione e forma all'argomento Intervallo.

Alcune funzionalità
L'ultimo argomento della funzione (Sum_And_Band: B1: B20000) è facoltativo. Ciò significa che non può essere specificato. Se non lo specifichi, la funzione sommerà i valori specificati dall'argomento Range . A cosa serve Ad esempio, è necessario ottenere la somma di solo quei numeri che sono maggiori di zero. Nella colonna A dell'importo. Quindi la funzione sarà simile a questa:
= ESTATE (A1: A20000; "> 0")

Cosa dovrebbe essere considerato: il range_summing e l' intervallo dovrebbero essere uguali nel numero di righe. Altrimenti, puoi ottenere il risultato sbagliato. In modo ottimale, se assomiglierà alle formule che ho dato: la gamma e l' intervallo delle somme partono da una riga e hanno lo stesso numero di righe: A1: A20000; B1: B20000

Sommatoria su due o più criteri
Ma cosa fare quando i criteri per la sommatoria 2 e altro? Supponiamo di dover riassumere solo gli importi che appartengono a un dipartimento e solo per una certa data. I proprietari felici delle versioni Office 2007 e successive possono utilizzare la funzione SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Il primo argomento specifica l'intervallo di celle contenenti gli importi che verranno raccolti in uno.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 - Range_Criteria. Specifica l'intervallo di celle in cui si desidera cercare una corrispondenza per criterio.
$ I $ 3, $ H8 - criterio. Qui, come in SUMMESLI, sono ammessi i caratteri jolly * e ? e funzionano allo stesso modo.

Specifici di specificare argomenti: in primo luogo, l'intervallo di criteri è specificato (sono numerati), quindi il valore (criterio) è indicato direttamente in punto e virgola, che in questo intervallo deve essere trovato - $ A $ 2: $ A $ 50; $ I $ 3. E nient'altro. Non dovresti provare a specificare prima tutti gli intervalli, e poi i criteri per loro - la funzione darà un errore, o non sommerà ciò che è necessario.

Tutte le condizioni sono confrontate secondo il principio I. Ciò significa che se tutte le condizioni elencate sono soddisfatte. Se almeno una condizione non viene soddisfatta, la funzione salta la linea e non aggiunge nulla.
Per quanto riguarda gli ESTATE, gli intervalli di sommatoria e dei criteri dovrebbero essere uguali nel numero di righe.

perché SUMMESLIMN è apparso solo nelle versioni di Excel, a partire dal 2007, quindi come possono essere gli utenti infelici delle versioni precedenti in questi casi? Molto semplice: usa un'altra funzione - SUMPRODUCT. Non voglio dipingere gli argomenti, perché Ce ne sono molti e sono tutti matrici di valori. Questa funzione moltiplica gli array indicati dagli argomenti. Proverò a descrivere il principio generale dell'utilizzo di questa funzione per riassumere i dati su diverse condizioni.
Per risolvere il problema di sommatoria con diversi criteri, la funzione sarà simile a questa:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - intervallo di date. $ I $ 3 è la data del criterio per cui è necessario sommare i dati.
$ B $ 2: $ B $ 50 - i nomi dei dipartimenti. H5 - il nome del dipartimento, i dati su cui deve essere sommato.
$ C $ 2: $ C $ 50 - intervallo con importi.

Analizziamo la logica, perché per molti, sarà completamente chiaro solo guardando questa funzione. Se solo perché nell'aiuto questa applicazione non è descritta. Per una maggiore leggibilità, ridurre le dimensioni degli intervalli:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Quindi, l'espressione ($ A $ 2: $ A $ 5 = $ I $ 3) e ($ B $ 2: $ B $ 5 = H5) sono logici e restituiscono array di FALSE logico e VERO. VERO se la cella dell'intervallo $ A $ 2: $ A $ 5 è uguale al valore della cella $ I $ 3 e la cella dell'intervallo $ B $ 2: $ B $ 5 è uguale al valore della cella H5. ie abbiamo il seguente:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
Come puoi vedere, nel primo array ci sono due corrispondenze per la condizione e nella seconda. Inoltre, questi due array vengono moltiplicati (il segno di moltiplicazione (*) ne è responsabile). Quando si verifica la moltiplicazione, si verifica la conversione implicita degli array FALSE e TRUE alle costanti numeriche 0 e 1, rispettivamente ({0; 1; 1; 0} * {0; 0; 1; 0}). Come sai, quando moltiplicato per zero, otteniamo zero. E il risultato è un singolo array:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Quindi l'array {0; 0; 1; 0} viene moltiplicato per una matrice di numeri nell'intervallo $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40})
E come risultato, otteniamo 30. Ciò di cui avevamo bisogno - otteniamo solo l'importo che soddisfa il criterio. Se ci sono più di una somma che soddisfa il criterio, allora saranno riassunti.

Vantaggio di SUMMYROIZV
Se gli argomenti hanno il segno più invece del segno di moltiplicazione:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
quindi le condizioni saranno confrontate secondo il principio OR: cioè somme totali saranno riassunte se almeno una condizione è soddisfatta: o $ A $ 2: $ A $ 5 è uguale al valore della cella $ I $ 3 o cella dell'intervallo $ B $ 2: $ B $ 5 è uguale al valore della cella H5.
Questo è il vantaggio di SUMMRODOTTO su SUMMESLIMN. SUMMESLIMN non può sommare i valori secondo il principio OR, solo secondo il principio AND (tutte le condizioni devono essere soddisfatte).

carenze
SUMPRODUCT non può utilizzare caratteri jolly * e ?. È possibile utilizzare più precisamente, ma saranno percepiti non come caratteri speciali, ma come un asterisco e un punto interrogativo. Penso che questo sia uno svantaggio significativo. E anche se questo può essere aggirato, io uso altre funzioni all'interno di SUMPRODUCT - sarebbe comunque fantastico se la funzione potesse in qualche modo utilizzare i caratteri jolly.

Nell'esempio troverai un paio di esempi di funzioni per una migliore comprensione di quanto scritto sopra.

Scarica un esempio

Importo secondo diversi criteri (41,5 KiB, 10.477 download)

Vedi anche:
Sommare le celle per colore di riempimento
Sommatoria delle celle per colore del carattere
Sommare le celle in base al formato della cella
Calcola la quantità di celle per colore di riempimento
Calcola la quantità di celle per colore del carattere
Come riassumere i dati da diversi fogli, inclusa la condizione

Articolo aiutato? Condividi il link con i tuoi amici! Tutorial video

{"Bottom bar": {"textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 "texteffectdelay1": 1000 textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: a lpha (opacity = 60); "," titlecss ":" display: block; posizione: relativa; font: grassetto 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; posizione: relativa; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; colore: #fff; margin-top: 8px; "," buttoncss ":" display: block; posizione: relativa; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none: important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Cerca per tag

accesso orologio di apple Multex prospettiva Power Query e Power BI VBA lavora nell'editor Gestione del codice VBA Componenti aggiuntivi gratuiti Data e ora Grafici e grafici documenti Protezione dei dati Internet Immagini e oggetti Fogli e libri Macro e VBA Componenti aggiuntivi registrazione stampa Cerca dati Informativa sulla privacy posta programmi Lavora con le applicazioni Lavora con i file Sviluppo di applicazioni Tabelle riassuntive liste Corsi di formazione e webinar finanziario formattazione Formule e funzioni Funzioni di Excel Funzioni VBA Celle e gamme Azioni MulTEx analisi dei dati bug e glitch in Excel riferimenti Può contenere i caratteri jolly "*" e "?
Quot;?
Specificando "mas?
Perché SUMMESLIMN è apparso solo nelle versioni di Excel, a partire dal 2007, quindi come possono essere gli utenti infelici delle versioni precedenti in questi casi?