Šajā videoklipā aplūkosim, kā aprēķināt un izcelt derīguma termiņus.
Pieņemsim, ka jūsu uzņēmums ir uzsācis kaut kāda veida dalības programmu, un jūsu priekšnieks tikko nosūtīja jums datu kopu. Viņa jums ir iedevusi sarakstu ar 1000 cilvēkiem, kuri atjaunojuši dalību aptuveni pēdējā gada laikā, un viņa meklē vairākas lietas.
Pirmkārt, viņa vēlas, lai jūs vienu gadu nākotnē aprēķinātu derīguma termiņu tā paša mēneša pēdējā dienā, kad dalība tika atjaunota.
Otrkārt, viņa vēlas uzzināt, cik dienas ir palikušas līdz termiņa beigām.
Treškārt, viņa vēlas redzēt statusu “Beidzies” visām abonementiem, kuru derīguma termiņš jau ir beidzies, un “Drīz beigsies” visiem abonementiem, kuru derīguma termiņš beigsies nākamo 30 dienu laikā.
Visbeidzot, viņa teica, ka būtu patīkami redzēt locekļus, kuriem beidzies derīguma termiņš, rozā krāsā, bet tos, kuru termiņš beidzas, - dzelteni.
Arī - vai viņa to var dabūt pirms pusdienas tikšanās pusdienlaikā?
Umm, protams.
Pirmkārt, pārveidosim šos datus pareizā Excel tabulā. Tas padarīs formulas daudz vieglāk ievadāmas, jo Excel tās automātiski kopēs uz leju, kā mēs ejam.
Tagad aprēķināsim derīguma termiņus. Paredzams, ka tie būs tā paša mēneša beigās gadu vēlāk, bet vispirms izmantosim vienkāršu uzlaušanu, lai nokļūtu balles laukumā. Kā jūs redzējāt iepriekšējos videoklipos, datumi ir tikai sērijas numuri, tāpēc mēs varam vienkārši ievadīt formulu, kas atjaunošanas datumam pievieno 365 dienas.
Tas ir labs sākums. Mēs varam pabeigt risinājuma raupjumu un atgriezties, lai vēlāk to labotu.
Kad Excel risina sarežģītāku problēmu, ieteicams apstiprināt vispārējo pieeju, pēc tam atgriezieties pie detaļām beigās. Sākumā nevēlaties iestrēgt pie sīkumiem, it īpaši, ja pieeja var mainīties.
Tagad, kad mums ir derīguma termiņš, mēs varam aprēķināt atlikušās dienas. Turpmāk tas ir jāatjaunina automātiski, tāpēc mēs izmantosim funkciju ŠODIEN, kas vienmēr atgriež šodienas datumu.
Formula ir vienkārši E5 mīnus ŠODIEN (). Kad es nospiedu atgriešanos, mēs saņemam atlikušās dienas pirms derīguma termiņa beigām. Negatīvie skaitļi norāda, ka dalība jau ir beigusies.
Lai izmantotu statusu, mēs izmantosim vienkāršu ligzdotu IF formulu. Ja atlikušo dienu skaits ir mazāks par nulli, dalības termiņš ir beidzies. Pretējā gadījumā, ja atlikušo dienu skaits ir mazāks par 30, statusam jābūt “Drīz beigsies”. Pretējā gadījumā statuss nav nekas.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Tālāk mums jāizveido nosacījuma formatēšanas kārtulas, kas izceļ šīs vērtības.
Vispirms atlasiet datus un iestatiet aktīvo šūnu augšējā labajā stūrī. Pēc tam izveidojiet formulas kārtulu, kas pārbauda aktīvās šūnas vērtību “Beidzies”. Kolonna ir jānoslēdz.
Tagad atkārtojiet to pašu procesu dalībniekiem, kuru termiņš drīz beigsies.
Tas izskatās labi. Mums vienkārši jānosaka derīguma termiņi, lai nokļūtu mēneša pēdējā dienā.
Izrādās, ka ir kāda forša funkcija, ko sauc par MĒNESI (mēneša beigām), kas mēness pēdējo dienu saņem pagātnē vai nākotnē.
Sākuma datums ir atjaunots datums, un mēneši ir 12.
Un jums tas ir, viss, ko jūsu priekšnieks vēlējās, un jūs vēl atvēlat laiku kafijas tasei pirms pusdienām.
Protams
Galvenā formulaSaistītie saīsnes
Ievietot tabulu Ctrl + T ⌃ + T Izvērst vai sakļaut lenti Ctrl + F1 ⌘ + ⌥ + R Atlasīt tabulu Ctrl + A ⌘ + A Pārvietot aktīvo šūnu pulksteņrādītāja virzienā atlasē Ctrl + . ⌃ + .
