Viens no biežākajiem jautājumiem ziņojumu panelī ir tas, kā izmantot funkciju SumIf ar diviem dažādiem nosacījumiem. Diemžēl atbilde ir tāda, ka SumIf nevar tikt galā ar diviem dažādiem nosacījumiem.
Lai veiktu divus nosacījumus, jums jāizmanto diezgan sarežģīta masīva formula. Nosacījuma summas vedņa pievienojumprogramma ļauj viegli ievadīt šīs sarežģītās formulas.
Šeit ir Excel darblapa ar kolonnām produktam, tirdzniecības pārstāvim un pārdošanai. Dati ir šūnās A2: C29.
Ja vēlaties summēt pārdošanas apjomus, darbosies vienkārša funkcija SUM (). =SUM(C2:C29).
Daudzi izcilnieki atklāj funkciju SumIf. Izmantojot šo funkciju, ir diezgan viegli noskaidrot kopējo produkta ABC pārdošanas apjomu.=SUMIF(A2:A29,E2,C2:C29)
Tāpat ir viegli noskaidrot pārdošanas pārstāvja Džo kopējo pārdošanas apjomu =SUMIF(B2:B29,E2,C2:C29).
Pēc tam jūs pieņemat, ka ir iespējams noskaidrot Džo produkta ABC kopējo pārdošanas apjomu. Tomēr ar SumIf funkciju to nav iespējams izdarīt. Izrādās, ka jāizmanto diezgan sarežģīta masīva vai pielāgotās meklētājprogrammas formula.
Atzīsim - Summas formula ir Excel 101. SumIf formula sarežģītībā nav tālu atpalikusi. Tomēr CSE formula, lai aprēķinātu Džo veikto ABC pārdošanas apjomu, ir pietiekama, lai pat mana galva pagrieztos.
Labās ziņas - Microsoft piedāvā vedni Conditional Sum Wizard, kas pat iesācējam ļauj ievadīt sarežģītas nosacītās formulas, pamatojoties uz 1, 2 vai vairāk nosacījumiem. Nosacījuma summas vednis ir pievienojumprogramma. Lai pievienotu šo funkcionalitāti programmai Excel, dodieties uz izvēlni Rīki un atlasiet Papildinājumi. Dialoglodziņā Pievienojumprogrammas atzīmējiet izvēles rūtiņu blakus Nosacījuma summas vednis un izvēlieties Labi. Iespējams, ka šajā brīdī jums var būt nepieciešams instalācijas kompaktdisks, jo Microsoft noklusējuma instalācijā neiekļauj vedni.
Kad pievienojumprogramma ir veiksmīgi ieslēgta, izvēlnes Rīki apakšdaļā būs izvēle Conditonal Sum….
Datu kopā atlasiet vienu šūnu un izvēlieties Rīki - Nosacījuma summa. Pieņemot, ka jūsu dati ir labi formatēti ar vienu virsrakstu rindu, Excel pareizi uzminēs jūsu datu diapazonu. Izvēlieties Tālāk.
2. solī atlasiet summējamo kolonnu. Šajā gadījumā vednis jau uzminēja, ka vēlaties summēt pirmo (un vienīgo) skaitlisko kolonnu - Pārdošana. Dialoglodziņa vidū ir trīs nolaižamās vadīklas. Pirmā nosacījuma gadījumā tas ir pareizi - produkts ir vienāds ar ABC, tāpēc izvēlieties pogu Pievienot nosacījumu.
Tad jūs varat pievienot savu otro nosacījumu. Šajā gadījumā vēlaties norādīt, ka tirdzniecības pārstāvis ir Džo. Pirmajā nolaižamajā izvēlnē izvēlieties bultiņu. Excel piedāvā alfabētisku pieejamo sleju nosaukumu sarakstu. Izvēlieties tirdzniecības pārstāvi.
Centra nolaižamā izvēlne ir pareiza, taču, lai šeit būtu pilnība, jūs varat redzēt, ka jūs būtu varējuši izvēlēties vienādu, mazāku, lielāku par, mazāku vai vienādu, lielāku vai vienādu vai ne vienādu.
Trešajā nolaižamajā izvēlnē atlasiet Džo.
Izvēlieties pogu Pievienot nosacījumu.
Tagad esat gatavs pāriet uz 3. darbību. Nospiediet pogu Tālāk.
3. solī jums ir divas izvēles iespējas. Pēc pirmās izvēles vednis formulā ievadīs vienu formulu ar vērtībām "ABC" un "Joe". Tas jums sniegs atbildi, taču nebūs iespējas viegli mainīt formulu. Izmantojot otro izvēli, Excel izveidos jaunu šūnu ar vērtību "ABC" un jaunu šūnu ar vērtību "Joe". Trešajā šūnā būs formula, kas veido nosacītu summu, pamatojoties uz šīm divām vērtībām. Izmantojot šo opciju, šūnās varat ierakstīt jaunas vērtības, lai redzētu Adama pārdoto XYZ kopējo daudzumu.
Pēc tam vednis jautās, kur vēlaties ABC vērtību. Atlasiet šūnu un izvēlieties Tālāk. Atkārtojiet, kad vednis lūdz atlasīt Džo šūnu un formulu.
Kad pēdējā solī izvēlaties Pabeigt, Excel izveidos nedaudz atšķirīgu (bet derīgu) PMP formulas versiju.
Šī formula aprēķina, ka Džo pārdeva 33 338 USD no ABC.
Ja mainīsit produkta ievades šūnu no ABC uz DEF, formula tiks pārrēķināta, lai parādītu, ka Džo pārdeva 24 478 USD no DEF.
Nosacījuma Summas vednis ļauj sarežģītas formulas labi sasniegt visiem Excel īpašniekiem.
Papildus informācija:Ja vēlaties izveidot tabulu, kurā tiks parādīti katra produkta pārdošanas apjomi katrā tirdzniecības pārstāvī, ir dažas īpašas “kopšanas un barošanas” iespējas, kas jums jāzina par šīm formulām. Ierakstiet katru tirdzniecības pārstāvi diapazona augšdaļā. Ierakstiet katru produktu diapazona kreisajā kolonnā. Rediģējiet vedņa sniegto formulu. Zemāk esošajā attēlā formula norāda produktu E6 šūnā. Šai atsaucei patiešām jābūt USD E6. Ja atsauci atstājat kā E6 un kopējat formulu kolonnā G, formula skatās uz F6, nevis E6, un tas būtu nepareizi. Pievienojot dolāra zīmi pirms E E6, pārliecinieties, ka formula vienmēr skatās uz produktu E slejā. Formula norāda arī uz pārdošanas pārstāvi šūnā F5. Šai atsaucei tiešām jābūt F $ 5. Ja atsauci atstājāt kā F5 un kopējat uz leju 7. rindā,F5 atsauce mainīsies uz F6, un tas nav pareizi. Pievienojot dolāra zīmi pirms rindas numura, tiks bloķēts rindas numurs, un atsauce vienmēr norādīs uz 5. rindu.
Rediģēšanas režīmā (atlasiet šūnu un nospiediet F2, lai rediģētu) ierakstiet $ pirms E. Ievadiet dolāra zīmi pirms 5 F5. Vēl nav jāsit Enter!
Šī formula ir īpašs formulas veids. Nospiežot Enter, jūs saņemsiet 0, kas nav pareizi.
Tā vietā, lai ievadītu Enter, turiet nospiestu taustiņus Ctrl un Shift, kamēr nospiežat Enter. Šī burvīgā kombinācija C trl + S hift + E nter ir iemesls, kāpēc es saucu šīs PMP formulas.
Pirms formulas kopēšanas pārējā tabulā ir vēl viens apsvērums. Jūsu vēlme varētu būt kopēt F6 un ielīmēt F6: G8. Ja jūs to izmēģināt, Excel jums sniegs mulsinošu ziņojumu "Jūs nevarat mainīt masīva daļu". Excel sūdzas, ka jūs nevarat ielīmēt PMP formulu diapazonā, kurā ir oriģinālā PMP formula.
To ir viegli apiet. F6 kopija. Ielīmēt F7: F8.
F6 kopija: F8. Ielīmēt pie G6: G8. Jums būs PMP formulu tabula, kurā tiks rādītas kopsummas, pamatojoties uz diviem nosacījumiem.
