Come riassumere le singole celle in Excel. Eccellere. Contare e sommare le celle che soddisfano i criteri di formattazione condizionale
È spesso il caso in cui è necessario sommare ogni seconda, terza, quarta cella ecc. In un foglio di calcolo. Ora, grazie al prossimo trucco, può essere fatto.
Excel non fornisce una funzione standard che può sommarli ennesima cella o una stringa. Tuttavia, puoi eseguire questa operazione con diversi file diversi modi... Tutti questi approcci si basano sulle funzioni ROW e MOD.
Funzione RIGA restituisce il numero di riga per il riferimento di cella specificato: RIGA (riferimento), nella versione russa di Excel RIGA (riferimento).
Funzione OSTAT (MOD) restituisce il resto della divisione di un numero per un divisore: MOD (numero; divisore), nella versione russa di Excel OSTAT (numero; divisore).
Posiziona la funzione RIGA nella funzione MOD (per passare un argomento numerico), dividi per 2 (per sommare ogni altra cella) e controlla se il risultato non è zero. In tal caso, la cella viene sommata. 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 seconda cella nell'intervallo $ A $ 1: $ A $ 100 potrebbe essere simile a questa: \u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)), nella versione russa di Excel \u003d SUM (IF (OSTAT (LINE ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)).
Poiché si tratta di una formula di matrice, è necessario inserirla premendo Ctrl + Maiusc + Invio, Excel aggiungerà parentesi graffe per farlo sembrare così: (\u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500), 2) \u003d 0; $ A $ 1: $ A $ 500; 0))), nella versione russa di Excel: (\u003d SUM (IF (Remaining (LINE ($ A $ 1: $ A $ 500) ; 2) \u003d 0; $ A $ 1: $ A $ 500; 0))) È necessario Excel per aggiungere le parentesi graffe da solo; 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 lunga formula include una funzione RIGA ricalcolabile, che trasforma anche la formula grande in una funzione ricalcolabile. Ciò significa che verrà costantemente ricalcolato, indipendentemente da ciò che fai nella cartella di lavoro. Questo è un modo molto brutto!
Ecco un'altra formula leggermente la scelta migliore: \u003d SUMPRODUCT ((MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)), nella versione russa di Excel \u003d SUMPRODUCT ((REST (RIGA ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)).
Ricorda, tuttavia, che questa formula restituirà il #VALORE! (#VALORE!) Se qualche cella nell'intervallo contiene testo invece di numeri. Anche questa formula, sebbene non sia effettivamente una formula di matrice, rallenta ottimo lavorose lo usi troppe volte o se si riferisce ogni volta a un intervallo ampio.
Per fortuna c'è il modo migliore, che non è solo più efficiente, ma anche una soluzione molto più flessibile. Richiede l'utilizzo della funzione DSUM. In questo esempio, abbiamo utilizzato l'intervallo A1: A500 come intervallo su cui deve essere sommata ogni ennesima cella.
Immettere la parola Criteri nella cella E1. Nella cella E2, inserisci la seguente formula: \u003d MOD (ROW (A2) - $ C $ 2-1; $ C $ 2) \u003d 0, nella versione russa di Excel \u003d OSTAT (LINE (A2) - $ C $ 2 -1; $ C $ 2) \u003d 0. Seleziona la cella C2 e seleziona il comando Dati → Convalida.
Nel campo Consenti, seleziona Elenco e nel campo Origine, inserisci 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Assicurati che la casella Elenco sia selezionata valori accettabili (In-Cell) e fare clic sul pulsante OK. Nella cella C1, inserisci il testo SOMMA ogni…. In una cella diversa dalla riga 1, immettere la seguente formula: \u003d DSUM ($ A: $ A; 1; $ E $ 1: $ E $ 2), nella versione russa di Excel \u003d BDSUMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
Nella cella direttamente sopra la cella in cui hai inserito la funzione DSUM, inserisci il testo \u003d "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 la funzione DSUM farà il resto.
Utilizzando la funzione DSUM, puoi sommare le celle all'intervallo specificato. 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 impostare, questo è il caso in cui è difficile da imparare, facile da combattere.
In precedenza, ho descritto come utilizzare una funzione personalizzata per trovare. Sfortunatamente, questa funzione non funziona se le celle sono colorate con formattazione condizionale... Ho promesso di "affinare" 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é utilizzando informazioni da Internet ... ( Aggiornamento del 29 marzo 2017 Dopo altri cinque anni, riuscii ancora 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. Hawley, R. Hawley "Excel 2007. Tricks", che ti permette di fare a meno del codice.
Sia presente un elenco di numeri da 1 a 100, compreso nell'intervallo A1: A100 (Fig. 1; vedere anche il foglio "SUMIF" del file Excel). L'intervallo è stato formattato in modo condizionale per contrassegnare le celle che contengono numeri maggiori di 10 e minori o uguali a 20.
Figura. 1. Gamma di numeri; la formattazione condizionale ha evidenziato 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 che hai appena impostato. Non importa quale tipo di formattazione viene applicato a queste celle, ma è necessario conoscere i criteri in base ai quali vengono evidenziate le celle.
Per aggiungere un intervallo di celle che corrispondono uno criterio, è possibile utilizzare la funzione SOMMA.SE (Fig. 2).
Figura. 2. Somma di celle che soddisfano una condizione
Se hai alcuni condizioni, è possibile utilizzare la funzione SUMIFS (Fig. 3).
Figura. 3. Sommare le celle che soddisfano diverse condizioni
È possibile utilizzare la funzione CONTA.SE per contare il numero di celle che soddisfano un criterio.
È possibile utilizzare la funzione CONTA.SE per contare il numero di celle che soddisfano più criteri.
Excel fornisce un'altra funzione che consente di specificare più condizioni. Questa funzione è inclusa nell'insieme delle funzioni delle basi dati Excel e si chiama BDSUMM. Per verificarlo, utilizzare la stessa serie di numeri nell'intervallo A2: A100 (Fig. 4; vedere anche la scheda “BDSUMM” nel file Excel).
Figura. 4. Utilizzo delle funzioni del database
Seleziona le celle C1: D2 e \u200b\u200bassegna un nome a questo intervallo Criterio digitandolo nella casella del nome a sinistra della barra della formula. Ora seleziona la cella C1 e inserisci \u003d $ A $ 1, che è un riferimento alla prima cella del foglio che contiene il nome del database. Inserisci \u003d $ A $ 1 nella cella D1 e otterrai due copie dell'intestazione della colonna A. Queste copie verranno utilizzate come intestazioni per le condizioni BDSUMM (C1: D2), che hai chiamato Criterion. Nella cella C2, inserisci\u003e 10. Nella cella D2, inserisci<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($ A $ 1: $ A $ 101,1, criterio)
È possibile utilizzare la funzione COUNT per contare il numero di celle che soddisfano più criteri.
Leggendo il libro di John Walkenbach, ho appreso che a partire da Excel 2010, VBA ha una nuova proprietà DisplayFormat (vedere, ad esempio, Range.DisplayFormat Property). Cioè, VBA può leggere il formato visualizzato sullo schermo. Non importa come è stato ricevuto dalle impostazioni dell'utente diretto o utilizzando la formattazione condizionale. Sfortunatamente, MS ha fatto in modo che la proprietà DisplayFormat funzioni solo nelle procedure chiamate da VBA e le UDF basate su questa proprietà generano un #VALUE! Tuttavia, puoi ottenere la somma dei valori in un intervallo per celle di un determinato colore utilizzando una procedura (una macro, ma non una funzione). Apri (contiene codice VBA). Vai attraverso il menu Visualizza -> Macro -> Macro; nella finestra Macro, evidenzia la linea SumColorUsle premete Eseguire... Esegui la macro, seleziona l'intervallo di sommatoria e il criterio. La risposta apparirà nella finestra.
Codice di procedura
Sub SumColorConv () Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor \u003d 0 "Interrogazione intervallo Set UserRange \u003d Application.InputBox (_ Prompt: \u003d" Seleziona intervallo di sommatoria ", _ Titolo: \u003d "Selezione intervallo", _ Predefinito: \u003d ActiveCell.Address, _ Tipo: \u003d 8) "Criterion Request Set CriterionRange \u003d Application.InputBox (_ Prompt: \u003d" Seleziona criterio di sommatoria", _ Title: \u003d" Criterion selection ", _ Default: \u003d ActiveCell.Address, _ Type: \u003d 8)" Sommando le celle "corrette" per ogni i In UserRange If i.DisplayFormat.Interior.Color \u003d _ CriterionRange.DisplayFormat Interior.Color Then SumColor \u003d SumColor + i End If Next MsgBox SumColor End Sub
Sub SumColorUl () Applicazione. Volatile Vero Dim SumColor As Double Dim i come intervallo Dim UserRange As Range Criterio di attenuazione Intervallo come intervallo SumColor \u003d 0 "Intervallo di query Set UserRange \u003d Application.InputBox (_ Prompt: \u003d "Seleziona intervallo di somma", _ Titolo: \u003d "Selezione intervallo", _ Predefinito: \u003d ActiveCell.Address, _ Tipo: \u003d 8) "Proscriterion Imposta CriterionRange \u003d Applicazione. InputBox (_ Prompt: \u003d "Seleziona un criterio di somma", _ Titolo: \u003d "Selezione criterio", _ Predefinito: \u003d ActiveCell. Indirizzo, _ |
Supponi di avere un rapporto sulle vendite come questo:
Da esso devi scoprire quanto matite venduto dal rappresentante di vendita Ivanov nel gennaio.
PROBLEMA: Come riassumere i dati in base a diversi criteri ??
DECISIONE: Metodo 1:
BDSUMM (A1: G16; F1; I1: K2)
Nella versione inglese:
DSUM (A1: G16, F1, I1: K2)
COME FUNZIONA:
Dal database che abbiamo specificato A1: G16 funzione BDSUMM recupera e riepiloga i dati della colonna quantità (discussione " Campo" = F1) secondo il dato nelle celle I1: K2 (Venditore \u003d Ivanov; Prodotti \u003d Matite; Mese \u003d gennaio) criteri.
CONTRO: L'elenco dei criteri dovrebbe essere sul foglio.
APPUNTI: Il numero di criteri di somma è limitato dalla RAM.
AREA DI APPLICAZIONE: Qualsiasi versione di Excel
Metodo 2:
SUMPRODUCT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
Nella versione inglese:
SUMPRODUCT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
COME FUNZIONA:
La funzione SUMPRODUCT forma matrici di valori VERO e FALSO, in base ai criteri selezionati, nella memoria di Excel.
Se i calcoli fossero eseguiti nelle celle del foglio (per chiarezza, dimostrerò l'intero lavoro della formula come se i calcoli si svolgessero sul foglio e non in memoria), gli array sarebbero così:
È ovvio che se, ad esempio, D2 \u003d Matite, il valore sarà TRUE e se D3 \u003d Cartelle, quindi FALSE (poiché il criterio per la selezione di un prodotto nel nostro esempio è il valore Le matite).
Sapendo che TRUE è sempre uguale a 1 e FALSE è sempre uguale a 0, continuiamo a lavorare con gli array come con i numeri 0 e 1.
Moltiplicando sequenzialmente i valori ottenuti degli array tra loro, otteniamo UN array di zeri e uno. Quando tutti e tre i criteri di selezione sono stati soddisfatti, ( IVANOV, MATITE, GENNAIO) cioè tutte le condizioni hanno assunto i valori TRUE, otteniamo 1 (1 * 1 * 1 \u003d 1), ma se almeno una condizione non è soddisfatta, otteniamo 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).
Ora resta solo da moltiplicare l'array risultante per un array contenente dati che dobbiamo sommare come risultato (range F2: F16) e, di fatto, somma ciò che non viene moltiplicato per 0.
Ora confronta gli array ottenuti utilizzando la formula e durante il calcolo passo-passo sul foglio (evidenziato in rosso).
Penso che sia tutto chiaro :)
MENO: SUMPRODUCT - formula di matrice "pesante". Quando si esegue il calcolo su intervalli di dati di grandi dimensioni, il tempo di ricalcolo aumenta notevolmente.
APPUNTI
AREA DI APPLICAZIONE: Qualsiasi versione di Excel
Metodo 3: formula di matrice
SOMMA (SE ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))
Nella versione inglese:
SOMMA (SE ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16))
COME FUNZIONA: Allo stesso modo del metodo n. 2. Ci sono solo due differenze: questa formula viene inserita premendo Ctrl + Maiusc + Inviopiuttosto che semplicemente premendo accedere e la matrice 0 e 1 non viene moltiplicata per l'intervallo di sommatoria, ma selezionata utilizzando la funzione SE.
MENO: Le formule di matrice durante il calcolo su intervalli di dati di grandi dimensioni 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; D2: D16; J2; A2: A16; K2)