Bendroji formulė
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))
Santrauka
Norėdami susumuoti duomenis pagal grupę ar etiketę, tiesiogiai lentelėje, galite naudoti formulę, pagrįstą funkcija SUMIF.
Parodytame pavyzdyje formulė D5 yra:
=IF(B5=B4,"",SUMIF(B:B,B5,C:C))
Pastaba: norint gauti pagrįstus rezultatus, duomenys turi būti rūšiuojami pagal grupavimo stulpelį.
Paaiškinimas
Šios formulės pagrindas yra pagrįstas IF, kuris tikrina kiekvieną B stulpelio vertę, kad įsitikintų, ar ji yra tokia pati kaip „aukščiau esančio langelio“ reikšmė. Kai reikšmės sutampa, formulė nieko negrąžina (""). Kai reikšmės skiriasi, IF funkcija iškviečia SUMIF:
SUMIF(B:B,B5,C:C)
Kiekvienoje eilutėje, kur SUMIF suaktyvina IF, SUMIF apskaičiuoja visų atitinkančių C stulpelio eilučių sumą (C: C). SUMIF naudojami kriterijai yra dabartinė B stulpelio (B5) eilutės vertė, patikrinta pagal visą B stulpelį (B: B).
Visos tokios stulpelių nuorodos yra šaunios ir elegantiškos, nes jums nereikia jaudintis dėl to, kur prasideda ir baigiasi duomenys, tačiau turite būti tikri, kad virš ar po lentele nėra papildomų duomenų, kuriuos gali užklupti SUMIF.
Spektaklis
Gali atrodyti, kad viso stulpelio nuoroda yra bloga idėja, nes dabartinėse „Excel“ versijose yra daugiau nei 1 m eilučių. Tačiau bandymai parodė, kad „Excel“ vertina tik „panaudoto diapazono“ duomenis (A1 iki paskutinio sunaudoto stulpelio ir paskutinio panaudoto eilutės susikirtimo adreso) su šio tipo formule.
Charlesas Williamsas, dirbantis „Fast Excel“, turi gerą straipsnį šia tema su visu laiko rezultatų rinkiniu.
Kodėl apie „Pivot“ lenteles?
Šis pavyzdys skirtas parodyti, kaip veikia visos stulpelių nuorodos ir kaip jas galima tiesiogiai naudoti duomenų lentelėje. „Pivot“ lentelės išlieka puikus būdas grupuoti ir apibendrinti duomenis.
