V tem članku se bomo naučili, kako šteti datume določenega leta v različnih datumih.
Preprosto povedano, pri delu z datumi moramo včasih prešteti celice, kjer so datumi iz določenega leta.
Kako rešiti težavo?
Za ta članek bomo morali uporabiti funkcijo SUMPRODUCT. Če imamo datume v obsegu podatkov in vrednost določenega leta, moramo šteti vrednosti datumov, kjer datum pripada določenemu letu.
Splošna formula:
= SUMPRODUCT ( - (LETO (obseg) = leto))
range: datumske vrednosti, podane kot obseg
leto: vrednost leta, navedena kot referenca celice.
Primer:
Torej, preizkusimo to formulo tako, da jo zaženemo na spodnjem primeru.
Tu imamo zapise podatkov in poiskati moramo datumske vrednosti v določenem letu ali vrniti številske vrstice, kjer vrednost datuma leži v danem letu.
Najprej moramo poimenovano obdobje za časovno obdobje definirati kot obseg. Samo izberite vrednosti datuma in vnesite ime (obseg) za obseg v zgornjem levem kotu.
Zdaj bomo uporabili naslednjo formulo za štetje datumov, ki so bili v letu 2016.
Uporabite formulo:
= SUMPRODUCT ( - (LETO (obseg) = F4))
obseg: imenovano območje, ki se uporablja za datumske vrednosti D3: D11.
F4: vrednost leta kot referenca celice.
Pojasnilo:
- Funkcija YEAR (obseg) izvleče vrednost leta iz datuma in jo uskladi z danim letom in operatorjem.
= SUMPRODUCT ( - ({2019; 2018; 2019; 2016; 2020; 2019; 2017; 2016; 2020} = 2016))
- = operator ga ujema z letno vrednostjo 2019 in vrne niz vrednosti TRUE in FALSE, odvisno od operacije.
= SUMPRODUCT ( - ({FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}))
- -- operater, ki se uporablja za pretvorbo vrednosti TRUE v 1 in False v 0.
= PODROČJE ({0; 0; 0; 1; 0; 0; 0; 1; 0})
- Funkcija SUMPRODUCT dobi vsoto 1s v vrnjenem nizu, kar bo štelo zahtevane datume ali vrstice.
Tu je vrednost leta podana kot referenca celice, obseg pa kot referenca celice. Pritisnite Enter, da dobite štetje.
Kot lahko vidite, se skupni datum ujema z letom 2016 2. To pomeni, da obstajata 2 datumski vrednosti ali 2 vrstici, kjer je vrednost leta enaka 2016.
Sedaj kopirajte in prilepite formulo v druge celice s kombinacijo CTRL + D ali povlecite možnost Excel navzdol.
Kot lahko vidite na zgornjem posnetku, dobimo vse vrednosti podatkov, ki so bile določene v določenem letu, z uporabo formule Excel. Čeke za iste lahko dobite tudi z možnostjo filtra excel. Uporabite filter za glavo Datumi in počistite izbiro možnosti izberi vse in izberite možnost leta, ki jo želite filtrirati.
Možnost filtriranja kot rezultat vrne 3 vrednosti. Enako naredite z drugimi vrednostmi datumov, da dobite število datumov ali vrstic, ki imajo merila za določeno leto.
Spodaj je nekaj opazovalnih opomb.
Opombe:
- Formula deluje samo s številkami.
- Formula deluje le, če v iskalni tabeli ni dvojnikov.
- Funkcija SUMPRODUCT šteje številske vrednosti kot 0.
- Funkcija SUMPRODUCT upošteva logično vrednost TRUE kot 1 in False kot 0.
- Niz argumentov mora biti enake dolžine kot funkcija.
Upam, da je ta članek o tem, kako vrniti štetje, če je SUMPRODUCT v Excelu razložljiv. Več člankov o funkcijah SUMPRODUCT najdete tukaj. Prosimo, delite svoje vprašanje spodaj v polju za komentar. Mi vam bomo pomagali.
Kako uporabljati funkcijo SUMPRODUCT v Excelu: Vrne SUM po množenju vrednosti v več matrikah v Excelu.
COUNTIFS z dinamičnim obsegom meril : Štetje celic, odvisno od drugih vrednosti celic v Excelu.
COUNTIFS Ujemanje dveh meril : Štetje celic, ki ustrezajo dvema različnima kriterijima na seznamu v Excelu.
RAČUNI Z OR Za več meril : Preštejte celice, ki se ujemajo z več merili, s funkcijo OR.
Funkcija COUNTIFS v Excelu : Šteje celice, odvisno od drugih vrednosti celic.
Kako uporabljati Countif v VBA v programu Microsoft Excel : Štetje celic s kodo Visual Basic za aplikacije.
Kako uporabljati nadomestne znake v Excelu : Preštejte celice, ki se ujemajo z frazami, z uporabo nadomestnih znakov v Excelu
Priljubljeni članki
50 Bližnjica v Excelu za večjo produktivnost
Uredite spustni seznam
Absolutna referenca v Excelu
Če s pogojnim oblikovanjem
e 2019, izberite leto 2019 in kliknite V redu.