Riassumendo alcune celle in Excel. Somma selettiva secondo due criteri

Formulazione del problema

Abbiamo una tabella delle vendite, ad esempio, della seguente forma:

Compito : riassume tutti gli ordini che il manager Grigoriev ha implementato per il negozio "Kopeyka".

Metodo 1. Funzione SUMIF

Se il nostro compito avesse una sola condizione (tutti gli ordini da Grigoriev o tutti gli ordini a "Kopeyka"), allora il compito sarebbe risolto abbastanza facilmente usando la funzione Excel incorporataSUMIF sfogliando la categoria Matematica. Puoi leggere come usarlo. Ma nel nostro caso, ci sono due condizioni, non una, quindi questo metodo non è adatto ...

Metodo 2. Indicatore di colonna

Aggiungiamo un'altra colonna alla nostra tabella, che servirà come una sorta di indicatore: se l'ordine era in "Kopeyka" e da Grigoriev, la cella di questa colonna avrà un valore di 1 altrimenti 0. La formula che deve essere inserito in questa colonna è molto semplice:

\u003d (A2 \u003d "Kopeyka") * (B2 \u003d "Grigoriev")

Le uguaglianze booleane tra parentesi danno VERO o FALSO, che per Excel è uguale a 1 e 0. Quindi, poiché moltiplichiamo queste espressioni, 1 alla fine funzionerà solo se entrambe le condizioni sono vere. Ora resta da moltiplicare il costo del venduto per i valori della colonna risultante e sommare gli importi ottenuti:

Metodo 3. La formula magica dell'array

Se non ti sei mai imbattuto in una meravigliosa funzionalità di Excel come le formule di matrice, ti consiglio di leggere prima molte cose positive su di esse.... Ebbene, nel nostro caso, il problema è risolto da una formula:

SOMMA ((A2: A26 \u003d "Penny") * (B2: B26 \u003d "Grigoriev") * D2: D26)

Dopo aver inserito questa formula, non devi premere Invio, come al solito, maCtrl + Maiusc + Invio - quindi Excel lo percepirà comeformula di matrice e aggiungerò io stesso le parentesi graffe. Non è necessario inserire parentesi dalla tastiera. È facile rendersi conto che questo metodo (come il precedente) è facilmente scalabile di tre, quattro, ecc. condizioni senza alcuna restrizione.

Metodo 4. Funzione database BDSUMM

Nella categoria Database puoi trovare la funzioneBDSUM (DSUM) , che può anche aiutarci a risolvere il nostro problema:

La sfumatura è che affinché questa funzione funzioni, è necessario creare sul foglio un intervallo di celle contenente le condizioni di selezione, quindi specificare questo intervallo della funzione come argomento:

BDSUMM (A1: D26; D1; F1: G2)

Metodo 5. Creazione guidata somma parziale

Questo è il nome di un componente aggiuntivo di Excel che ti aiuta a creare formule complesse per la sommatoria multicriteria. Puoi collegare questo componente aggiuntivo gratuito tramite il menuServizio - Componenti aggiuntivi - Procedura guidata di riepilogo(Strumenti - Componenti aggiuntivi - Creazione guidata somma condizionale)... Dopodiché nel menuServizio il comando dovrebbe apparireImporto parziale per avviare la procedura guidata di sommatoria:

Nella prima fase Procedure guidate di sommatoria è necessario specificare un intervallo con i dati, ad es. seleziona il nostro intero tavolo. Al secondo passaggio, è necessario selezionare una colonna per la somma e le condizioni del modulo per la selezione dei valori, aggiungendo ciascuna condizione all'elenco con il pulsanteAggiungi condizione:

E infine, al 3 ° e 4 ° passo, indichiamo la cella in cui vuoi visualizzare il risultato. E finiamo con quanto segue:

È facile vedere che abbiamo usato qualcosa di simile a questa formula di matrice nel Metodo 3. Solo qui non puoi toccare affatto la tastiera - lunga vita alla pigrizia - il motore del progresso!

Lo sapevate, che cos'è un esperimento mentale, un esperimento gedanken?
Questa è una pratica inesistente, un'esperienza ultraterrena, l'immaginazione di ciò che non è nella realtà. Gli esperimenti mentali sono come i sogni a occhi aperti. Danno alla luce mostri. A differenza di un esperimento fisico, che è un test sperimentale di ipotesi, un "esperimento mentale" sostituisce astutamente un test sperimentale con le conclusioni desiderate, non testate nella pratica, manipolando costruzioni logiche che effettivamente violano la logica stessa usando premesse non dimostrate come provate, cioè per sostituzione. Pertanto, il compito principale di coloro che richiedono "esperimenti mentali" è di ingannare l'ascoltatore o il lettore sostituendo un vero esperimento fisico con la sua "bambola" - ragionamento fittizio sulla parola senza verifica fisica stessa.
Riempire la fisica con "esperimenti mentali" immaginari portò all'emergere di un'immagine assurda del mondo surreale, confusa e confusa. Un vero ricercatore deve distinguere tali "involucri" dai valori reali.

Relativisti e positivisti sostengono che l '"esperimento mentale" è uno strumento molto utile per verificare la coerenza delle teorie (che appaiono anche nella nostra mente). In questo ingannano le persone, poiché qualsiasi verifica può essere effettuata solo da una fonte indipendente dall'oggetto della verifica. Lo stesso richiedente l'ipotesi non può essere una prova della propria affermazione, poiché la ragione di questa affermazione stessa è l'assenza di contraddizioni nella dichiarazione visibile al richiedente.

Lo vediamo nell'esempio di SRT e GRT, che si sono trasformati in una sorta di religione che controlla la scienza e opinione pubblica... Nessuna quantità di fatti che li contraddica può superare la formula di Einstein: "Se un fatto non corrisponde alla teoria, cambia il fatto" (In un'altra versione "- Il fatto non corrisponde alla teoria? - Tanto peggio per il fatto ").

Il massimo che un "esperimento mentale" può vantare è solo la coerenza interna dell'ipotesi nel quadro della logica, spesso per nulla vera, del richiedente. Ciò non verifica l'idoneità della pratica. Il vero test può aver luogo solo in un valido esperimento fisico.

Un esperimento è un esperimento perché non è un affinamento del pensiero, ma una prova del pensiero. Un pensiero che è autoconsistente in se stesso non può verificare se stesso. Ciò è dimostrato da Kurt Gödel.

NOTIZIE FORUM Cavalieri eterici

30/10/2017 - 06:17: -\u003e - Karim_Khaidarov. 19/10/2017 - 04:24: -\u003e - Karim_Khaidarov. 11.10.2017 - 05:10: -\u003e - Karim_Khaidarov. 05.10.2017 - 11:03:

Funzione SUMIF viene utilizzato quando è necessario sommare i valori dell'intervallo che soddisfano la condizione specificata. Ad esempio, supponiamo che in una colonna di numeri si desideri sommare solo valori maggiori di 5. A tale scopo, è possibile utilizzare la seguente formula: \u003d SOMMA.SE (B2: B25, "\u003e 5")

Questo video fa parte corso di formazione Aggiungi numeri in Excel 2013.

Suggerimenti:

Sintassi

SUMIF (intervallo, condizione, [intervallo_somma])

Argomenti della funzione SUMIF sono descritti di seguito.

Gamma ... Argomento richiesto. L'intervallo di celle da valutare per la conformità. Le celle in ogni intervallo devono contenere numeri, nomi, matrici o riferimenti numerici. Le celle vuote e le celle contenenti valori di testo vengono ignorate. L'intervallo selezionato può contenere date nel formato Excel standard (vedere esempi di seguito).

Condizione ... Argomento richiesto. Una condizione, sotto forma di numero, espressione, riferimento di cella, testo o funzione, che determina le celle da sommare. Ad esempio, una condizione può essere rappresentata come 32, "\u003e 32", B5, "32", "mele" o TODAY ().

Importante: Tutte le condizioni di testo e le condizioni con segni logici e matematici devono essere racchiuse tra virgolette doppie ( " ). Se la condizione è un numero, non è necessario utilizzare le virgolette.

Sum_range ... Argomento facoltativo. Celle da cui vengono aggiunti i valori se differiscono dalle celle specificate come gamma ... Se l'argomento intervallo_somma omesso, Excel somma le celle specificate nell'argomento gamma (le stesse celle a cui si applica la condizione).

Nella discussione condizione È possibile utilizzare caratteri jolly: punto interrogativo ( ? ) e un asterisco ( * ). Il punto interrogativo corrisponde a qualsiasi carattere singolo e l'asterisco corrisponde a qualsiasi sequenza di caratteri. Se vuoi trovare direttamente un punto interrogativo (o un asterisco), devi mettere una tilde davanti ad esso ( ~ ).

Appunti

SOMMA.SE restituisce risultati errati se utilizzato per trovare stringhe più lunghe di 255 caratteri o se applicato alla stringa #VALORE !.

Discussione intervallo_somma potrebbe non avere le stesse dimensioni dell'argomento gamma ... La cella in alto a sinistra dell'argomento viene utilizzata come cella iniziale per determinare le celle effettive da aggiungere intervallo_somma , quindi vengono sommate le celle della parte dell'intervallo corrispondente per dimensione all'argomento gamma ... Esempio:

Tuttavia, se gli argomenti gamma e intervallo_somma Poiché le funzioni SOMMA.SE contengono un numero diverso di celle, il ricalcolo del foglio potrebbe richiedere più tempo del previsto.

Esempi di

Esempio 1

Copia i dati di esempio dalla seguente tabella e incollali nella cella A1 del nuovo foglio di lavoro Excel... Per visualizzare i risultati delle formule, selezionali e premi F2, quindi premi Invio. Modificare la larghezza delle colonne secondo necessità per vedere tutti i dati.

Esempio 2

Copia i dati di esempio dalla tabella seguente e incollali nella cella A1 di un nuovo foglio Excel. Per visualizzare i risultati delle formule, selezionali e premi F2, quindi premi Invio. Inoltre, puoi regolare la larghezza delle colonne in base ai dati che contengono.

Buona giornata!

Continuerò il mio tentativo di descrivere la varietà di funzioni in Excel e la prossima per la nostra considerazione. Questo è un altro rappresentante delle funzioni di sommatoria, ma con le sue condizioni specifiche. Funzione BDSUMM in Excel ricerca i numeri nella tabella in base ai criteri definiti, questa è la sua proprietà principale.

In tutta onestà, posso dire che molti calcoli e calcoli possono essere fatti senza di esso usando vari, o SUMPRODUCT, ma se devi fare una selezione complessa usando i caratteri jolly, allora devi assolutamente usare l'eroe del nostro articolo.

Per prima cosa, diamo un'occhiata alla sintassi che utilizza:

\u003d BDSUMM (il tuo intervallo di database; campo di ricerca; termine di ricerca)dove

Quando si lavora con funzione BDSUMM vale la pena notare diverse condizioni a cui prestare attenzione quando si lavora:

• Quando si lavora su un'intera colonna piena di dati, assicurarsi di inserire una riga vuota sotto le intestazioni di colonna nell'intervallo di criteri specificato;
• La gamma di criteri non deve sovrapporsi all'elenco;
• Sebbene sia possibile posizionare i dati per formare l'intervallo di condizioni in qualsiasi punto del foglio, non è necessario posizionarlo dopo l'elenco, poiché i dati che aggiungiamo all'elenco verranno inseriti nella prima riga dopo l'elenco, e se c'è dati nella riga, quindi aggiungerne di nuovi non funzionerà;
• È possibile utilizzare qualsiasi intervallo per le condizioni che contengono almeno un'intestazione e almeno una delle celle che si trova sotto l'intestazione e contiene la condizione.

Quindi, la parte teorica, che considero completata, passiamo ora all'applicazione pratica funzioni BDSUMM nel nostro lavoro, per questo considereremo diversi esempi per l'esecuzione, ho fatto esempi secondo il principio, ma invece di raccogliere i valori per criterio, ci sarà la somma:

Funzione BDSUMM con un criterio numerico

Quindi, per cominciare, diamo un'occhiata a un semplice esempio con un criterio numerico, per questo selezioniamo la colonna "Harvest" e indichiamo che abbiamo bisogno di alberi con una resa "\u003e \u003d 10". Per ottenere il risultato, abbiamo bisogno di una formula di questo tipo (ti consiglio di usare riferimenti assoluti):

\u003d BDSUMM ($ B $ 6: $ G $ 12; E6; E2: E3),

dove, $ B $ 6: $ G $ 12 è l'intervallo in cui riassumeremo, E6 è la colonna in cui riassumeremo ed E2: E3 è l'intervallo in cui abbiamo inserito i criteri per la somma. Di conseguenza, la formula ha trovato 3 posizioni per un totale di 34.

Per ottenere un risultato simile, puoi anche utilizzare le seguenti formule:

\u003d SOMMA.SE (E7: E12, "\u003e \u003d 10")

\u003d SOMMA.SE (E7: E12, E3)

Funzione BDSUMM con un criterio di testo

Vediamo ora come si comporta la funzione BDSUMM con i criteri testuali, in generale tutto rimane uguale nell'esempio precedente, tranne come viene indicato il criterio testuale, ed è indicato solo in questa forma: \u003d "\u003d s. Serovo" e poi otterrai il risultato altrimenti la formula non sarà in grado di riconoscere il tuo criterio. Ora sostituiamo questo criterio nella formula e otteniamo:

\u003d BDSUMM ($ B $ 6: $ G $ 12; E6; C2: C3), come si può vedere, è cambiato solo l'intervallo del criterio.

Per ottenere un risultato simile, avrai bisogno di:

\u003d SUMIF (C7: C12, "s.Serovo", E7: E12)

Somma in base a due criteri su colonne diverse

L'esempio è complicato dall'applicazione di due criteri, ma non applicheremo nulla di fondamentalmente nuovo, indicheremo il criterio testuale "S. Serovo" e il criterio numerico "\u003e \u003d 10", lasciando il campo di sommatoria "Harvest", noi otterrà una modifica nella formula solo per l'ultimo argomento, di conseguenza ... La nostra formula ora sarà simile a questa:

\u003d BDSUMM ($ B $ 6: $ G $ 12; E6; C2: E3), ancora una volta vengono visualizzate le modifiche solo all'indirizzo dell'intervallo di criteri.

Un'opzione alternativa può essere ottenuta usando e in questo modo:

\u003d SOMMA.SE (E7: E12; C7: C12; C3; E7: E12; E3)

\u003d SUMIFS (E7: E12; C7: C12; "s.Serovo"; E7: E12; "\u003e \u003d 10")

Somma di una delle due condizioni in una colonna

Consideriamo un'altra opzione applicata, ma ora non useremo un singolo criterio, ma un doppio, ma per un campo. I due criteri devono essere su linee diverse. L'essenza della formula si riduce al fatto che attraversa due volte lo stesso intervallo, contando ciascuno dei criteri separatamente. Per questo esempio, la formula sarebbe simile a questa:

\u003d BDSUMM ($ B $ 6: $ G $ 12; E6; C2: C4), qui cambiamo di nuovo l'intervallo del criterio, ma non in larghezza, ma in altezza.

Anche come sostituto, puoi usare la somma della funzione SOMMA.SE:

\u003d SOMMA.SE (C7: C12; C3; E7: E12) + SOMMA.SE (C7: C12; C4; E7: E12).

Somma di una delle due condizioni in due colonne differenti

Il quinto esempio applicativo sarà la somma di una delle due condizioni in due colonne differenti e gli argomenti dovranno essere inseriti tutti in nuove righe, questo permetterà loro di essere riassunti a turno. Per questi scopi, abbiamo bisogno di una formula:

\u003d BDSUMM ($ B $ 6: $ G $ 12; D6; C2: D4), il principio di formazione della formula viene preservato, ad eccezione dell'intervallo di criteri, che comprende tre righe: un titolo e due criteri.

Somma per due criteri di testo per due colonne

In questo esempio di lavoro, considereremo un analogo quasi completo dell'esempio considerato in precedenza, quando c'erano due criteri in due colonne, ma c'era un criterio numerico e uno di testo, ma qui considereremo la somma con due criteri di testo e da due colonne. Usiamo i criteri "\u003d" \u003d s.Ivanovo "" e "\u003d" \u003d Cherry "", che indicheremo nella gamma dei criteri. Quindi la nostra formula sarà simile a questa:

\u003d BDSUMM ($ B $ 6: $ G $ 12; D6; B2: C3).

Utilizzo del risultato di una formula per ottenere un criterio di selezione e una somma

In questo esempio della funzione BDSUMM, utilizzo una formula per determinare il criterio di selezione e somma, in questo caso troveremo quali alberi ci portano reddito e per determinare l'argomento condizionale prendiamo il valore medio per le vendite di frutti da alberi e tutto al di sopra della media ci interessa. Per determinare il valore medio che diventerà il nostro VERO criterio, creiamo la formula:

\u003d G7\u003e MEDIA ($ G $ 7: $ G $ 12), non dimenticare di correggere l'intervallo, in modo che quando la formula passa attraverso i valori, questi non scorrono verso il basso, ma il valore G7 dovrebbe scorrere sull'intero intervallo per determinare se è "FALSO" o "VERO".

È anche molto importante che i titoli delle intestazioni non siano duplicati, abbiano una differenza, quindi chiamerò il campo dei criteri "Media". E poi la formula inizierà a funzionare, itererà sull'intero intervallo di $ G $ 7: $ G $ 12 per la presenza di un valore medio, e quando un risultato positivo è "TRUE", sommerà. La seguente formula farà fronte a questo lavoro:

\u003d BDSUMM ($ B $ 6: $ G $ 12; G6; $ G $ 2: $ G $ 3)

E se sei molto interessato a una soluzione alternativa alla domanda, prova l'opzione con la funzione SUMIF in questa forma:

\u003d SOMMA.SE ($ G $ 7: $ G $ 12; "\u003e" & MEDIA ($ G $ 7: $ G $ 12))

Funzione BDSUMM secondo tre criteri

In questo esempio, calcoliamo le vendite medie dei frutti coltivati \u200b\u200bin due villaggi: "villaggio di Ivanovo" e "villaggio di Uyutnoe". Ho già descritto l'idea principale della selezione in base ai criteri, quindi non mi ripeterò, dirò semplicemente che sarà una combinazione dei criteri precedentemente considerati. Per ottenere il risultato, ne abbiamo bisogno in questa forma:

\u003d BDSUMM ($ B $ 6: $ G $ 12; G6; $ C $ 2: $ G $ 4)

Somma per criterio di testo, sensibile al maiuscolo / minuscolo

Come ho detto prima, può cercare non solo con caratteri jolly, ma anche con distinzione tra maiuscole e minuscole, ora questa è esattamente l'opzione. Per cominciare, definiamo la condizione per la selezione di un criterio, se si incontra il nome "s. IVANOVO", in lettere maiuscole, allora eseguiamo la somma, per determinare questo criterio abbiamo bisogno della formula:

\u003d CONSIGLIO ("S. IVANOVO"; C7)

Ma ora possiamo scrivere una funzione che controllerà l'intervallo per la presenza del criterio specificato e, dopo aver ricevuto il valore "TRUE", eseguirà la somma. Nell'esempio, ho indicato specificamente una volta in base alla condizione e, come puoi vedere, la formula ha selezionato con successo tutti gli insediamenti e ha trovato quello desiderato e ha ottenuto il risultato "9". Per questo, è stata utilizzata la formula:

\u003d BDSUMM ($ B $ 6: $ G $ 12; D6; $ C $ 2: $ C $ 3)

Bene, penso di aver descritto i dettagli in molti dettagli, quindi ci saranno poche domande, ma molti vantaggi. Se hai domande, scrivi commenti, attendo i tuoi Mi piace e le tue recensioni. Puoi trovare informazioni su altre funzioni sul mio sito web.

Buona fortuna per i tuoi affari!

"Il denaro, ovviamente, può comprare adorabile cane, ma nessuna somma di denaro può farlo scodinzolare felicemente.
"
D. Billings

Ti consigliamo di leggere

Istruzioni per Android

Cos'è un dephlegmator per un chiaro di luna?

Istruzioni per Android

Ulteriori informazioni sui malti speciali: Caramel Malt

Compresse

Il dispositivo originale: il gorgogliatore sostituisce la scatola asciutta

Istruzioni di Windows

Distillazione pratica

Istruzioni di Windows

Una ricetta semplice per il chiaro di luna di grano a casa

Istruzioni per Android

Come distillare l'acqua del rubinetto