Ebben a videóban megvizsgáljuk, hogyan lehet kiszámítani és kiemelni a lejárati dátumokat.
Tegyük fel, hogy a cég elindított valamilyen tagsági programot, és a főnöke éppen egy adatsort küldött Önnek. Adott neked egy 1000 fős listát, akik megújították tagságukat az elmúlt egy évben, és több dolgot keres.
Először azt akarja, hogy számoljon lejárati dátumot egy évvel a jövőben, ugyanazon hónap utolsó napján a tagságot megújították.
Másodszor meg akarja nézni, hogy hány nap van még a lejárat előtt.
Harmadszor: „lejárt” állapotot akar látni minden már lejárt tagság esetén, és „hamarosan lejár” minden olyan tagság esetében, amely lejár a következő 30 napban.
Végül azt mondta, jó lenne látni a lejárt tagokat rózsaszínnel, a hamarosan lejárókat sárga színnel.
Továbbá - megkaphatja délben az ebédülése előtt?
Umm, persze.
Először alakítsuk át ezeket az adatokat egy megfelelő Excel táblává. Ez megkönnyíti a képletek bevitelét, mivel az Excel automatikusan másolja őket, ahogy megyünk.
Most számoljuk ki a lejárati dátumokat. Ezeknek egy év múlva állítólag ugyanannak a hónapnak a végén kell lenniük, de előbb csak egy egyszerű csapkodást használjunk, hogy bejussunk a labdaparkba. Amint azt korábbi videókban láthattátok, a dátumok csak sorozatszámok, ezért csak beírhatunk egy képletet, amely 365 napot ad hozzá a Megújított dátumhoz.
Ez egy jó kezdet. Befejezhetjük a megoldás nagyolását, és később visszatérhetünk a probléma megoldására.
Amikor egy összetettebb problémát old meg az Excel programban, érdemes az általános megközelítést érvényesíteni, majd a végén térjen vissza a részletekre. Nem akarsz elakadni egy apróságnál rögtön az elején, különösen, ha a megközelítés megváltozhat.
Most, hogy lejárt a dátum, kiszámolhatjuk a hátralévő napokat. Ezt a jövőben automatikusan frissíteni kell, ezért a TODAY funkciót fogjuk használni, amely mindig a mai dátumot adja vissza.
A képlet egyszerűen E5 mínusz TODAY (). Amikor a visszatérést találom, napok maradnak a lejárat előtt. A negatív számok azt jelzik, hogy a tagság már lejárt.
Állapotként egy egyszerű beágyazott IF képletet használunk. Ha a hátralévő napok száma kevesebb, mint nulla, a tagság lejár. Ellenkező esetben, ha a hátralévő napok száma kevesebb, mint 30, akkor a státusza "Hamarosan lejár". Egyébként az állapot semmi.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Ezután feltételes formázási szabályokat kell létrehoznunk, amelyek kiemelik ezeket az értékeket.
Először válassza ki az adatokat, és állítsa az aktív cellát a jobb felső sarokba. Ezután hozzon létre egy képletszabályt, amely teszteli az aktív cellát a "Lejárt" értékre. Az oszlopot zárolni kell.
Most ismételje meg ugyanazt a folyamatot a „hamarosan lejáró” tagságokkal.
Ez jól néz ki. Csak meg kell határoznunk a lejárati dátumokat, hogy a hónap utolsó napjára érkezzünk.
Nos, kiderült, hogy van egy jó funkció, az EOMONTH (a hónap végére) nevű funkció, amely megkapja a hónap utolsó napját a múltban vagy a jövőben.
A kezdő dátum a megújított dátum, a hónap pedig 12.
És itt van, minden, amit a főnöke kívánt, és ebéd előtt még egy csésze kávéra van ideje.
Tanfolyam
Core FormulaKapcsolódó hivatkozások
Insert táblázat Ctrl + T ⌃ + T kibontása vagy összecsukása szalag Ctrl + F1 ⌘ + ⌥ + R Select táblázat Ctrl + A ⌘ + A Mozgás aktív cella óramutató járásával megegyező irányban a kiválasztási Ctrl + . ⌃ + .
