cuntenutu
In questu tutoriale truverete alcuni esempi interessanti chì mostranu cumu utilizà a funzione VPR (VLOOKUP) cù SUM (SUM) o SUMMESLI (SUMIF) in Excel per circà è somma i valori basatu annantu à unu o più criteri.
Pruvate di creà un schedariu di riassuntu in Excel chì identificà tutte e istanze di un valore particulari è somma l'altri valori assuciati cù questu? O avete bisognu di truvà tutti i valori in un array chì risponde à una determinata cundizione è poi somma i valori cunnessi da un altru fogliu? O forse avete un compitu ancu più difficiule, cum'è guardà à traversu una tavola di tutte e fatture di a vostra cumpagnia, truvendu trà elli e fatture di un certu venditore è riassume?
I travaglii pò esse diffirenti, ma u so significatu hè u listessu - avete bisognu di truvà è somma i valori uXNUMXbuXNUMXbof unu o più criteri in Excel. Chì sò sti valori ? Ogni numeru. Chì sò questi criteri? Qualchese ... Da un numeru o una riferenza di cellula chì cuntene u valore desideratu à l'operatori lògichi è i risultati di a formula Excel.
Allora, ci hè una funziunalità in Microsoft Excel chì pò affruntà i travaglii descritti? Benintesa! A suluzione si trova in a cumminazzioni di funzioni VPR (VLOOKUP) o Visu (LOOKUP) cù funzioni SUM (SUM) o SUMMESLI (SUMIF). L'esempii di furmula quì sottu vi aiuterà à capisce cumu queste funzioni funzionanu è cumu si usanu cù dati veri.
Per piacè nutate chì l'esempii furniti sò pensati per un utilizatore avanzatu chì hè familiarizatu cù i principii basi è a sintassi di a funzione. VPR. Sè vo site sempre luntanu da stu livellu, ricumandemu chì fate attenzione à a prima parte di u tutoriale - Funzione VLOOKUP in Excel: sintassi è esempi.
VLOOKUP è SUM in Excel - calculate a summa di i valori currispondenti truvati
Sè vo travaglià cù dati numerichi in Excel, tandu abbastanza spessu ùn deve micca solu estratti dati cunnessi da un altru tavulu, ma ancu sume parechje colonne o fila. Per fà questu, pudete combine e funzioni SUM и VPR, cum'è mostratu sottu.
Diciamu chì avemu una lista di prudutti cù parechji mesi di dati di vendita, cù una colonna separata per ogni mese. Fonte di dati - Sheet Vendite Mensili:
Avà avemu bisognu di fà una tavola di totali cù i quantità di vendita per ogni pruduttu.
A suluzione à stu prublema hè di utilizà un array di custanti in l'argumentu col_index_num (column_number) funzioni VPR. Eccu un esempiu di formula:
=SUM(VLOOKUP(lookup value, lookup range, {2,3,4}, FALSE))
=СУМ(ВПР(искомое_значение;таблица;{2;3;4};ЛОЖЬ))
Comu pudete vede, avemu usatu un array 2,3,4 {} per u terzu argumentu per circà parechje volte in a listessa funzione VPR, è uttene a summa di i valori in e colonne 2, 3 и 4.
Avà applichemu sta cumminazione VPR и SUM à i dati in a nostra tavula per truvà a quantità tutale di vendite in culonni cù B by M:
=SUM(VLOOKUP(B2,'Monthly sales'!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))
=СУМ(ВПР(B2;'Monthly sales'! $A$2:$M$9;{2;3;4;5;6;7;8;9;10;11;12;13};ЛОЖЬ))
Impurtante! Sè vo entre in una formula array, assicuratevi di appughjà a cumminazione Ctrl + Shift + Enter invece di pressione normale Rinsignate. Microsoft Excel includerà a vostra formula in parentesi curly:
{=SUM(VLOOKUP(B2,'Monthly sales'!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))}
{=СУМ(ВПР(B2;'Monthly sales'!$A$2:$M$9;{2;3;4;5;6;7;8;9;10;11;12;13};ЛОЖЬ))}
Se ci limitemu à un clicu simplice Rinsignate, u calculu serà realizatu solu nantu à u primu valore di l'array, chì portarà à un risultatu incorrectu.
Puderete esse dumandate perchè a formula in a figura sopra mostra [@Produttu]cum'è u valore desideratu. Questu hè chì i mo dati sò stati cunvertiti in una tavula cù u cumandimu Table (Table) tab Inserzione (Inserisci). Sò più còmode di travaglià cù fogli di calculu Excel cumpletu cà intervalli simplici. Per esempiu, quandu inserite una formula in una di e cellule, Excel a copia automaticamente in tutta a colonna, salvendu uni pochi seconde preziosi.
Comu pudete vede, utilizate e funzioni VPR и SUM in Excel hè abbastanza simplice. In ogni casu, questu hè luntanu da una suluzione ideale, soprattuttu s'ellu ci vole à travaglià cù grande tavule. U fattu hè chì l'usu di formule di array pò rallentà l'applicazione, postu chì ogni valore in u array face una chjama di funzione separata. VPR. Risulta chì più valori in a matrice, più formule di matrice in u libru di travagliu è u travagliu più lento di Excel.
Stu prublema pò esse superatu cù una cumminazione di funzioni INDEX (INDICE) è PARTITU (POISKPOZ) invece CERCA V (VLOOKUP) è SUM (SUM). Più tardi in stu articulu vi vede qualchi esempi di tali formule.
Esegue altri calculi utilizendu a funzione VLOOKUP in Excel
Avemu vistu solu un esempiu di cumu pudete estrattà i valori da parechje colonne di una tavula è calculà a so summa. In u listessu modu, pudete fà altre operazioni matematiche nantu à i risultati restituiti da a funzione. VPR. Eccu alcuni esempi di formule:
Calculemu a media:
{=AVERAGE(VLOOKUP(A2,'Lookup Table'!$A$2:$D$10,{2,3,4},FALSE))}
{=СРЗНАЧ(ВПР(A2;'Lookup Table'!$A$2:$D$10;{2;3;4};ЛОЖЬ))}
A formula cerca u valore da a cell A2 in u fogliu di travagliu Tavola di ricerca è calcula a media aritmetica di i valori chì sò à l'intersezzione di a fila truvata è e colonne B, C è D.
Truvà u massimu:
{=MAX(VLOOKUP(A2,'Lookup Table'!$A$2:$D$10,{2,3,4},FALSE))}
{=МАКС(ВПР(A2;'Lookup Table'!$A$2:$D$10;{2;3;4};ЛОЖЬ))}
A formula cerca u valore da a cell A2 in u fogliu di travagliu Tavola di ricerca è torna u massimu di i valori chì sò à l'intersezzione di a fila truvata è e colonne B, C è D.
Truvà u minimu:
{=MIN(VLOOKUP(A2,'Lookup Table'!$A$2:$D$10,{2,3,4},FALSE))}
{=МИН(ВПР(A2;'Lookup Table'!$A$2:$D$10;{2;3;4};ЛОЖЬ))}
A formula cerca u valore da a cell A2 in u fogliu di travagliu Tavola di ricerca è torna u minimu di i valori chì sò à l'intersezzione di a fila truvata è e colonne B, C è D.
Calculemu % di a quantità:
{=0.3*SUM(VLOOKUP(A2,'Lookup Table'!$A$2:$D$10,{2,3,4},FALSE))}
{=0.3*СУММ(ВПР(A2;'Lookup Table'!$A$2:$D$10;{2;3;4};ЛОЖЬ))}
A formula cerca u valore da a cell A2 in u fogliu di travagliu Tavola di ricerca, poi somma i valori chì si trovanu à l'intersezzione di a fila truvata è e colonne B, C è D, è solu dopu calculate 30% di a somma.
Se aghjunghjemu e formule elencate sopra à a tavula da l'esempiu precedente, u risultatu serà cusì:
In u casu induve u vostru valore desideratu hè un array, a funzione VPR diventa inutile perchè ùn sapi micca cumu travaglià cù array di dati. In una tale situazione, pudete aduprà a funzione Visu (LOOKUP) in Excel, chì hè simile à VPR, è travaglia cù arrays in u listessu modu cum'è cù valori unichi.
Fighjemu un esempiu per fà più chjaru di ciò chì avemu parlatu. Eppo supponi chì avemu una tavula chì lista i nomi di i clienti, l'articuli acquistati è e so quantità (tavula Main table). Inoltre, ci hè una seconda tavula chì cuntene i prezzi di e merchenzie (Tavola di ricerca). U nostru compitu hè di scrive una formula chì truverà a summa di tutti l'ordini di un determinatu cliente.
Comu vi ricordate, ùn pudete micca aduprà a funzione VPRse u valore di ricerca si trova parechje volte (hè un array di dati). Aduprate invece una cumminazione di funzioni SUM и Visu:
=SUM(LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))
=СУММ(ПРОСМОТР($C$2:$C$10;'Lookup table'!$A$2:$A$16;'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))
Siccomu hè una formula array, ùn vi scurdate di appughjà a cumminazzioni Ctrl + Shift + Enter quandu l'input hè cumpletu.
Tavola di ricerca hè u nome di u fogliu induve si trova a gamma di vista.
Scupritemu l'ingredienti di a formula per capisce cumu funziona è pudete persunalizà per adattà à i vostri bisogni. Funzione SUM lascemu per avà, postu chì u so scopu hè evidenti.
LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)ПРОСМОТР($C$2:$C$10;'Lookup table'!$A$2:$A$16;'Lookup table'!$B$2:$B$16)funzioni Visu cerca i prudutti elencati in a colonna C di a tavola Principale è torna u prezzu currispundente da a colonna B di a tavola di ricerca.
- $D$2:$D$10 - u numeru di merchenzie acquistate da ogni cliente chì u nome hè in a colonna D di a tavola principale. Multiplicà a quantità di un articulu per u prezzu tornatu da a funzione Visu, avemu u costu di ogni pruduttu acquistatu.
- $B$2:$B$10=$G$ 1 - A formula compara i nomi di i clienti in a colonna B di a tavola principale cù u nome in a cellula G1. Se ci hè un match, torna 1, altrimente 0. Cusì, i nomi di i clienti chì differenu da quelli specificati in a cellula G1 sò scartati, perchè tutti sapemu chì a multiplicazione per zero dà zero.
Siccomu a nostra formula hè una formula array, ripete i passi sopra per ogni valore in a matrice di ricerca. Infine, a funzione SUM calcula a somma di i valori risultatu da a multiplicazione. Ùn hè micca difficiule, sì d'accordu?
Cumentu. Per funziunà Visu hà travagliatu bè, a colonna vista deve esse ordinata in ordine crescente.
VLOOKUP è SUMIF - truvate è somma i valori chì soddisfanu un certu criteriu
funzioni SUMMESLI (SUMIF) in Excel hè simile à SUM (SUM), chì avemu ghjustu discututu, perchè ancu sume i valori. L'unica diferenza hè chì SUMMESLI sume solu quelli valori chì rispondenu à i criteri chì specificate. Per esempiu, a formula più simplice cù SUMMESLI:
=SUMIF(A2:A10,">10")
=СУММЕСЛИ(A2:A10;">10")
- somma tutti i valori di cellule in un intervallo A2: A10, chì sò più 10.
Assai sèmplice, nò? Avà fighjemu un esempiu un pocu più cumplessu. Supponete chì avemu una tavula chì lista i nomi di i venditori è i so numeri ID (tavula di ricerca). Inoltre, ci hè un altru tavulu in quale u listessu ID ligata à i dati di vendita (tavula principale). U nostru compitu hè di truvà a quantità di vendite per un vinditore. Ci sò 2 circustanze aggravanti quì:
- A tavola principale cuntene parechji registri per unu ID in ordine casuale.
- Ùn pudete micca aghjunghje una colonna di nome di venditore à a tavola principale.
Scrivemu una furmula chì truverà tutte e vendite fatte da un venditore determinatu è ancu riassume i valori truvati.
Prima di principià, lasciami ricurdà a sintassi di a funzione SUMMESLI (SUMIF):
SUMIF(range,criteria,[sum_range])
СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])
- catalogu (range) - l'argumentu parla per sè stessu. Hè solu una gamma di cellule chì vulete evaluà da i criterii dati.
- Criterium (criteri) hè una cundizione chì dice à a formula chì i valori à summa. Pò esse un numeru, riferimentu di cellula, espressione, o una altra funzione Excel.
- sum_range (summation_range) hè un argumentu opcional ma assai impurtante per noi. Definisce a gamma di cellule relative chì saranu sommate. Se ùn hè micca specificatu, Excel suma i valori di e cellule in u primu argumentu di a funzione.
Mettite tuttu inseme, definiscemu u terzu argumentu per a nostra funzione SUMMESLI. Comu vi ricordate, vulemu riassume tutte e vendite fattu da un vinditore particulari chì u nome hè datu in a cell F2 (vede a figura sopra).
- catalogu (scala) - postu chì circhemu ID venditore, i valori di questu argumentu seranu i valori in a colonna B di a tavola Principale. Pudete stabilisce a gamma B: B (colonna sana) o, cunvertisce e dati à una tavula, utilizate u nome di a colonna Main_table[ID].
- Criterium (criteri) - postu chì i nomi di i venditori sò registrati in a tabella di ricerca (tavula di ricerca), usemu a funzione VPR Per a ricerca ID, currispondente à u venditore datu. U nome hè scrittu in a cellula F2, cusì per a ricerca usemu a formula:
VLOOKUP($F$2,Lookup_table,2,FALSE)ВПР($F$2;Lookup_table;2;ЛОЖЬ)Di sicuru, pudete inserisce u nome cum'è un valore di ricerca direttamente in a funzione VPR, ma hè megliu aduprà una riferenza di cellula assoluta, perchè cusì creamu una formula universale chì travaglià per qualsiasi valore inseritu in questa cellula.
- sum_range (summation_range) hè a parte più faciule. Siccomu i dati di vendita sò registrati in a colonna C, chì hè chjamatu Ventes, allora scrivimu solu Main_table[Vendite].
Tuttu ciò chì duvete fà hè unisce e parti in un sanu, è a formula SUMIF+VLOOKUP sarà pronta:
=SUMIF(Main_table[ID],VLOOKUP($F$2,Lookup_table,2,FALSE),Main_table[Sales])
=СУММЕСЛИ(Main_table[ID];ВПР($F$2;Lookup_table;2;ЛОЖЬ);Main_table[Sales])
