Supponiamo di avere il seguente report sulle vendite dei rappresentanti di vendita:
Da esso devi scoprire quanto matite venduto dal rappresentante di vendita Ivanov v Gennaio.
PROBLEMA: Come riassumere i dati in base a più criteri??
SOLUZIONE: Metodo 1:
SOMM.SOMMA(A1:SOL16;F1;I1:R2)
Nella versione inglese:
SOMMA DS(A1:G16;F1;I1:K2)
COME FUNZIONA:
Dal database che abbiamo specificato A1:G16 funzione BDSUM recupera e somma i dati della colonna Quantità(discussione " Campo" = F1) secondo quanto indicato nelle celle I1:K2 (Venditore = Ivanov; Prodotti = Matite;Mese = gennaio) criteri.
CONTRO: L'elenco dei criteri dovrebbe essere sul foglio.
APPUNTI: Il numero di criteri di sommatoria è limitato dalla RAM.
AREA DI APPLICAZIONE: qualsiasi versione di Excel
Metodo 2:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
Nella versione inglese:
SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)
COME FUNZIONA:
La funzione SUMPRODUCT genera array di valori VERO e FALSO, in base a criteri selezionati, nella memoria di Excel.
Se i calcoli sono stati eseguiti nelle celle del foglio (per chiarezza, dimostrerò l'intera operazione della formula come se i calcoli si svolgessero sul foglio e non in memoria), allora gli array sarebbero simili a questo:
È ovvio che se, ad esempio, D2=Matite, il valore sarà VERO e se D3=Cartelle, quindi FALSE (poiché il criterio di selezione del prodotto nel nostro esempio è il valore Matite).
Sapendo che VERO è sempre uguale a 1 e FALSO è sempre uguale a 0, continuiamo a lavorare con gli array come con i numeri 0 e 1.
Moltiplicando in sequenza i valori ottenuti degli array tra loro, otteniamo UN array di zeri e uno. Dove sono stati soddisfatti tutti e tre i criteri di selezione, ( IVANOV, MATITE, GENNAIO) cioè. tutte le condizioni erano TRUE, otteniamo 1 (1*1*1 = 1), ma se almeno una condizione non è stata soddisfatta, otteniamo 0 (1*1*0 = 0 ; 1*0*1 = 0 ; 0* 1*1 = 0).
Ora non resta che moltiplicare l'array risultante per l'array contenente i dati che dobbiamo sommare come risultato (intervallo F2:F16) e, di fatto, somma ciò che non viene moltiplicato per 0.
Ora confronta gli array ottenuti con l'aiuto della formula e con il calcolo passo-passo sul foglio (evidenziato in rosso).
Penso che sia tutto chiaro :)
CONTRO: SUMPRODUCT - formula di matrice "pesante". Quando si calcola su grandi intervalli di dati, il tempo di ricalcolo aumenta notevolmente.
APPUNTI
AREA DI APPLICAZIONE: qualsiasi versione di Excel
Metodo 3: formula matrice
SOMMA(SE((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
Nella versione inglese:
SOMMA(SE((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))
COME FUNZIONA: Proprio come il metodo n. 2. Ci sono solo due differenze - data formula inserito premendo Ctrl+Maiusc+Invio invece di premere semplicemente accedere e l'array di 0 e 1 non viene moltiplicato per l'intervallo di sommatoria, ma viene selezionato utilizzando la funzione IF.
CONTRO: Le formule di matrice durante il calcolo su grandi intervalli di dati aumentano notevolmente il tempo di ricalcolo.
APPUNTI: il numero di array elaborati è limitato a 255.
AREA DI APPLICAZIONE: qualsiasi versione di Excel
Metodo 4:
SOMMA.SE(F2:F16;B2:B16;I2;RE2:D16;J2;A2:A16;K2)
In precedenza, ho descritto come utilizzare una funzione personalizzata per trovare i file . Sfortunatamente, questa funzione non funziona se le celle sono colorate con formattazione condizionale. Ho promesso di "finire" la funzione. Ma nei due anni trascorsi dalla pubblicazione di quella nota, non sono stato in grado di scrivere codice digeribile né da solo né con l'aiuto di informazioni da Internet ... ( Aggiornamento 29 marzo 2017 Dopo altri cinque anni, sono ancora riuscito a scrivere il codice; vedi la parte finale della nota). E proprio di recente mi sono imbattuto in un'idea contenuta nel libro di D. Holy, R. Holy "Excel 2007. Tricks", che ti permette di fare a meno del codice.
Sia presente una lista di numeri da 1 a 100, posti nell'intervallo A1: A100 (Fig. 1; vedi anche il foglio "SUMIS" del file Excel). L'intervallo ha una formattazione condizionale che contrassegna le celle che contengono numeri maggiori di 10 e minori o uguali a 20.
Riso. 1. Intervallo di numeri; la formattazione condizionale evidenzia le celle contenenti valori da 10 a 20
Scarica nota in formato , esempi in formato
Ora devi aggiungere i valori nelle celle che soddisfano i criteri appena stabiliti. Non importa quale formattazione viene applicata a queste celle, ma è necessario conoscere i criteri in base ai quali le celle vengono evidenziate.
Per aggiungere un intervallo di celle corrispondente a solo criterio, è possibile utilizzare la funzione SOMMA.SE (Fig. 2).
Riso. 2. Somma delle celle che soddisfano la stessa condizione
Se hai Alcuni condizioni, è possibile utilizzare la funzione SOMMA.PIÙ.SE (Fig. 3).
Riso. 3. Somma di celle che soddisfano più condizioni
È possibile utilizzare la funzione CONTA.SE per contare il numero di celle che soddisfano gli stessi criteri.
È possibile utilizzare la funzione CONTA.PIÙ.SE per contare il numero di celle che soddisfano più criteri.
Excel fornisce un'altra funzione che consente di specificare più condizioni. Questa funzionalità fa parte del set di funzionalità di base. eccellere dati e si chiama BDSUMM. Per verificarlo, utilizzare lo stesso insieme di numeri nell'intervallo A2:A100 (Fig. 4; vedere anche il foglio "BDSUMM" del file Excel).
Riso. 4. Utilizzo delle funzioni di database
Seleziona le celle C1:D2 e denomina questo intervallo Criteri digitandolo nella casella del nome a sinistra della barra della formula. Ora seleziona la cella C1 e inserisci =$A$1, che è il collegamento alla prima cella del foglio di lavoro che contiene il nome del database. Inserisci =$A$1 nella cella D1 e otterrai due copie dell'intestazione della colonna A. Queste copie verranno utilizzate come intestazioni per le condizioni di BDSUMM (C1:D2), che hai chiamato Criteri. Nella cella C2, inserisci >10. Digitare nella cella D2<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUM($A$1:$A$101.1, criteri)
È possibile utilizzare la funzione COUNT per contare il numero di celle che soddisfano più criteri.
Durante la lettura del libro di John Walkenbach, ho appreso che a partire da Excel 2010, VBA ha introdotto una nuova proprietà DisplayFormat (vedere, ad esempio, Proprietà Range.DisplayFormat). Cioè, VBA può leggere il formato visualizzato sullo schermo. Non importa come è stato ottenuto, tramite impostazioni utente dirette o utilizzando la formattazione condizionale. Sfortunatamente, gli sviluppatori MS hanno fatto in modo che la proprietà DisplayFormat funzioni solo nelle procedure chiamate da VBA e le funzioni definite dall'utente basate su questa proprietà generino il #VALUE! Tuttavia, puoi ottenere la somma dei valori in un intervallo su celle di un determinato colore utilizzando una procedura (una macro, non una funzione). Apri (contiene codice VBA). Passa attraverso il menu Visualizzazione -> Macro -> Macro; nella finestra Macro, evidenziare la linea SommaColoreCond. e premere Correre. Eseguire la macro, selezionare l'intervallo di sommatoria e il criterio. La risposta apparirà nella finestra.
Codice di procedura
Sub SumColorCond() Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor = 0 " Range Request Set UserRange = Application.InputBox(_ Prompt:="Select Sum Range", _ Title: ="Range Selection", _ Default:=ActiveCell.Address, _ Type:=8) " Criterion Query Set CriterionRange = Application.InputBox(_ Prompt:="Seleziona criterio di sommatoria", _ Title:="Criteri di selezione", _ Default:=ActiveCell.Address, _ Type:=8) " Somma le celle "corrette" For Each i In UserRange If i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat Interior.Color Then SumColor = SumColor + i End If Next MsgBox SumColor End Sub
Sub SommaColorCond() applicazione. Volatile Vero Dim Sum Colore come doppio Dim i As Range Dim UserRange come intervallo Dim CriterionRange come intervallo SommaColore = 0 "Richiesta intervallo Imposta UserRange = Application.InputBox(_ Prompt:="Seleziona intervallo di sommatoria", _ Title:="Selezione intervallo", _ Predefinito:=ActiveCell.Address, _ Tipo:=8) "Criteri di richiesta Imposta CriterionRange = Applicazione . InputBox(_ Prompt := "Seleziona criterio di sommatoria", _ Titolo : = "Criteri di selezione" , _ Predefinito := ActiveCell . Indirizzo , _ |
Accade spesso che sia necessario sommare ogni seconda, terza, quarta, ecc. Cella in un foglio di calcolo. Ora, grazie al seguente trucco, si può fare.
Excel non fornisce una funzione standard che può sommare ciascuno ennesima cella o una stringa. Tuttavia, puoi eseguire questa operazione in diversi modi. Tutti questi approcci sono basati sulle funzioni ROW (ROW) e MOD (MOD).
Funzione RIGA restituisce il numero di riga per il riferimento di cella dato: ROW(reference), nella versione russa di Excel ROW(reference).
Funzione MOD (MOD) restituisce il resto dopo aver diviso un numero per un divisore: MOD(numero;divisore), nella versione russa di Excel MOD(numero;divisore).
Inserisci la funzione RIGA nella funzione MOD (per passare un argomento numerico), dividi per 2 (per sommare ogni seconda cella) e controlla se il risultato non è zero. In tal caso, la cella viene riassunta. Queste funzioni possono essere utilizzate in un'ampia varietà di modi: alcune forniranno risultati migliori di altre. Ad esempio, una formula di matrice per sommare ogni altra cella nell'intervallo $A$1:$A$100 potrebbe essere simile a questa: =SUM(IF(MOD(ROW($A$1:$A$500);2)=0;$ A$1: $A$500;0))
Poiché si tratta di una formula di matrice, è necessario inserirla premendo Ctrl+Maiusc+Invio, Excel aggiungerà le parentesi graffe in modo che assomigli a questo: (=SUM(IF(MOD(ROW($A$1:$A$500), 2)= 0;$A$1:$A$500;0))) $500;0))) Abbiamo bisogno di Excel per aggiungere le parentesi graffe; se li aggiungi tu stesso, la formula non funzionerà.
Sebbene l'obiettivo sia raggiunto, questo metodo influisce negativamente sul design. foglio di calcolo. Questa è un'applicazione non necessaria di una formula di matrice. A peggiorare le cose, questa formula lunga ha una funzione ROW ricalcolabile annidata al suo interno, rendendo ricalcolabile anche la formula più grande. Ciò significa che verrà costantemente ricalcolato, indipendentemente da ciò che fai cartella di lavoro. Questo è un modo molto brutto!
Ecco un'altra formula, che è un po ' la scelta migliore: =SOMMAPRODOTTO((MOD(RIGA($A$1:$A$500);2)=0)*($A$1:$A$500)) :$A$500);2)=0)*($A$1 :$A$500)) .
Tuttavia, tieni presente che questa formula restituirà il valore #VALUE! (#VALORE!) se le celle nell'intervallo contengono testo anziché numeri. Anche questa formula, sebbene non sia effettivamente una formula di matrice, rallenta Lavoro eccellente se viene utilizzato troppe volte o se si riferisce ogni volta a un intervallo ampio.
Fortunatamente c'è Il modo migliore, che non è solo più efficiente, ma anche molto più flessibile. Richiede l'uso della funzione DSUM. In questo esempio, abbiamo utilizzato l'intervallo A1:A500 come intervallo in cui sommare ogni n-esima cella.
Nella cella E1, inserisci la parola Criteri. Nella cella E2, inserisci la seguente formula: =MOD(ROW(A2)-$C$2-1,$C$2)=0 =0 . Selezionare la cella C2 e selezionare Dati → Convalida.
Nel campo Tipo di dati (Consenti), selezionare Elenco (Elenco) e nel campo Origine (Sorgente), immettere 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Assicurarsi che l'Elenco di i valori consentiti è selezionato (In-Cell) e fare clic sul pulsante OK. Nella cella C1, inserisci il testo SUM every…. In qualsiasi cella tranne la riga 1, inserisci la seguente formula: =DSUM($A:$A;1;$E$1:$E$2) :$E$2) .
Nella cella immediatamente sopra quella in cui hai inserito la funzione DSUM, inserisci il testo ="Summing Every" & $C$2 & CHOOSE($C$2;"st";"nd";"rd";"th";" th";"th";"th";"th";"th";"th") & "Cell" . Ora resta solo da selezionare il numero desiderato nella cella C2, e il resto sarà fatto dalla funzione DSUM.
Utilizzando la funzione DSUM, puoi sommare le celle a intervalli specificati. La funzione DSUM è molto più efficiente di una formula di matrice o della funzione SUMPRODUCT. Anche se ci vuole un po' più di tempo per prepararsi, questo è un caso difficile da imparare, facile da combattere.