Skaitytojas šią savaitę man atsiuntė įdomią formulės problemą, todėl pamanė, kad pasidalysiu ja kaip formulės iššūkis. Problema yra ši:
Turite fiksuotą mėnesinį mokėjimą, pradžios datą ir nurodytą mėnesių skaičių. Kokią formulę galite naudoti susumuodami visus mokėjimus pagal metus, remdamiesi šiuo darbalapiu:
Kitaip tariant, kokia formulė veikia E5, nukopijuota į I5, kad gautumėte sumą už visus parodytus metus?
Aš pats sugalvojau formulę, bet norėčiau pamatyti ir jūsų idėjas. Jei jus domina, palikite komentarą su jūsų pasiūlyta formule.
Jei norite, formulėje galite naudoti šiuos nurodytus diapazonus: mos (C5), suma (C6), pradžia (C7), pabaiga (C8).
Darbalapį galite atsisiųsti žemiau.
Atsakymas (spustelėkite, jei norite išplėsti)Tiek daug puikių formulių! Ačiū visiems, kurie skyrė laiko atsakymui pateikti. Žemiau pateikiamos mano nerimą keliančios mintys apie problemą ir keletas toliau pateiktų sprendimų.
Pastaba: aš niekada neaiškiau, kaip reikėtų tvarkyti mėnesio ribas. Aš kaip tik sekiau kitą darbalapį. Pagrindinė informacija yra 30 mokėjimų, pradedant nuo kovo 1 d .: 10 mokėjimų 2017 m., 12 mokėjimų 2018 m. Ir 8 mokėjimai (likutis) 2019 m.
Taigi, jei stengiatės suprasti, kaip galite pabandyti išspręsti tokią problemą, pirmiausia sutelkite dėmesį į mokėjimus. Sužinoję, kiek mokėjimų yra per metus, galite tiesiog padauginti iš skaičiaus iš sumos ir viskas.
Taigi, kaip galite sužinoti mokėjimų skaičių tam tikrais metais? Žemiau esančiuose komentaruose rasite daug gerų idėjų. Yra keletas modelių, kuriuos pastebėjau, ir keletą jų išvardinau žemiau. Tai nebaigtas darbas …
Dizaino modeliai
IF + AND/OR + YEAR + MONTH
IF yra patikimas budėjimas tiek formulių, ir jis naudojamas daugelyje siūlomų formulių, siekiant išsiaiškinti, ar įdomūs metai yra „ribos“ nuo pradžios ir pabaigos datos. Daugeliu atvejų IF derinamas su OR arba AND, kad formulės būtų kompaktiškos.
IFERROR + DATEDIF + MAX + MIN
DATEDIF gali grąžinti dviejų datų skirtumą mėnesiais, todėl idėja yra naudoti MAX ir MIN (trumpumui, o ne IF) kiekvienų metų pradžios ir pabaigos datai apskaičiuoti ir leisti DATEDIF gauti mėnesius tarp jų. DATEDIF išmeta #NUM klaidą, kai pradžios data yra ne mažesnė nei pabaigos data, todėl IFERROR naudojama klaidai sugauti ir grąžinti nulį. Žiūrėkite žemiau pateiktas 闫 强, Aruno ir Deivido formules.
MAX + MIN + YEAR + MONTH
Roberto ir Petro formulės beveik visą darbą atlieka su MAX ir MIN, nematant IF. Nuostabu. Jei idėja naudoti MAX ir MIN, norint pakeisti IF, jums yra nauja, šiame straipsnyje paaiškinama sąvoka.
DAYS360
„Excel DAYS360“ funkcija pateikia dienų skaičių tarp dviejų datų, pagrįstą 360 dienų metais. Tai būdas apskaičiuoti mėnesius remiantis idėja, kad kiekvienam mėnesiui reikia 30 dienų.
SUM + DATE
Tai mano neefektyvus (bet elegantiškas!) Metodas, naudojant DATE funkciją ir masyvo konstantą su skaičiumi kiekvienam mėnesiui. funkcija DATA sukuria kiekvieno metų mėnesio datą naudodama masyvo konstantą, o sutapimui tikrinti naudojama loginė logika.
