{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Period Functions Example","הדגמה של יישומים שימושיים"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":1,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Period Functions Example","index":0,"n_rows":27,"n_cols":14,"rows_kept":27,"cut":false,"hidden":false,"merged":[[5,13,6,14]]}
[]
[null,"הדגמת שימוש בפורמט מקודד של תקופות חודשיות"]
[]
[null,"תאריכים",null,"שימוש בפונקציית date2period להמרת התאריך לקוד תקופה",null,"שימוש בפונקציית period_shift כדי לקדם את התקופה במספר נתון של  חודשים/תקופות",null,null,"שימוש בפונקציית period2date להמרת קוד תקופה לתאריך (סוף חודש)",null,"שימוש בפונקציית period2date להמרת קוד תקופה לתאריך (תחילת חודש)"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"אהבתם? עקבו אחרי עמוד הבלוג בפייסבוק:\nhttps://www.facebook.com/AnonymousAnalysts"]
[]
[null,"תאריכים תקינים",null,"קוד תקופה",null,"הפרש תקופות","קוד תקופה חדש",null,"תאריך",null,"תאריך"]
[null,"2012-01-01",null,{"$f":"date2period(B9)","v":201201},null,12,{"$f":"period_shift(D9,F9)","v":201301},null,{"$f":"period2date(G9)","v":"2013-01-31"},null,{"$f":"period2date(G9,FALSE)","v":"2013-01-01"}]
[null,"2012-02-01",null,201202,null,3,201205,null,{"$f":"period2date(G10)","v":"2012-05-31"},null,{"$f":"period2date(G10,FALSE)","v":"2012-05-01"}]
[null,"2012-11-04",null,201211,null,-5,201206,null,"2012-06-30",null,"2012-06-01"]
[null,"2012-10-15",null,201210,null,{"$f":"ROUND(AVERAGE(F9:F11),0)","v":3},201301,null,"2013-01-31",null,"2013-01-01"]
[null,"2012-02-26",null,201202,null,null,201202,null,"2012-02-29",null,"2012-02-01"]
[null,"2013-02-10",null,201302,null,0,201302,null,"2013-02-28",null,"2013-02-01"]
[null,"2012-12-31",null,201212,null,1000,209604,null,"2096-04-30",null,"2096-04-01"]
[null,"1987-02-06",null,198702,null,-1000,190310,null,"1903-10-31",null,"1903-10-01"]
[null,"2033-06-26",null,203306,null,"טקסט",{"$error":"#VALUE!"},null,{"$error":"#N/A"},null,{"$error":"#N/A"}]
[null,"14 05 1999",null,199905,null,{"$f":"NA()","v":{"$error":"#N/A"}},{"$error":"#VALUE!"},null,{"$error":"#N/A"},null,{"$error":"#N/A"}]
[]
[]
[null,"תאריכים לא תקינים"]
[null,"31/02/2012",null,{"$f":"date2period(B22)","v":{"$error":"#N/A"}}]
[null,"-1/3/2011",null,{"$error":"#N/A"}]
[null,"חמישי למרץ 2012",null,{"$error":"#N/A"}]
[null,"ט\"ו בשבת 2000",null,{"$error":"#N/A"}]
[null,"שביעי של פסח 1999",null,{"$error":"#N/A"}]
[null,"היום שבו אני זוכה בלוטו",null,{"$error":"#N/A"}]

{"sheet":"הדגמה של יישומים שימושיים","index":1,"n_rows":113,"n_cols":12,"rows_kept":113,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"נתוני עסקאות",null,null,null,null,null,"ניתוח סטטיסטי"]
[]
[null,"תאריך","ערך","תקופה (שדה מחושב)",null,null,null,"מכירות ב-12 חודשים אחרונים"]
[null,"2007-04-25T11:20:21.681",818.97288610112309,200704]
[null,"2007-05-01T00:16:54.939",741.39246855839201,200705,null,null,null,"הזן תאריך בדיקה","2012-02-29"]
[null,"2007-05-14T03:18:31.049",563.50747365723248,200705]
[null,"2007-05-21T08:40:37.351",563.01047366804858,200705,null,null,null,"תקופה",{"$f":"date2period(I7)","v":201202},null,"תקופת התייחסות",{"$f":"ytd_period(I9)","v":201112}]
[null,"2007-05-27T23:36:04.766",714.95719094824176,200705]
[null,"2007-05-31T17:56:25.438",737.38583131295752,200705,null,null,null,"מכירות YTD (כולל תקופת הבדיקה)",{"$f":"SUMPRODUCT((I9>=$D$6:$D$113)*(L9<$D$6:$D$113)*$C$6:$C$113)","v":1669.6378626378328}]
[null,"2007-06-15T17:34:30.675",555.95146037927805,200706]
[null,"2007-06-28T14:21:21.991",533.94739539554826,200706,null,null,null,"מכירות בתקופה מקבילה אשתקד",{"$f":"SUMPRODUCT((period_shift(I9,-12)>=$D$6:$D$113)*(L13<$D$6:$D$113)*$C$6:$C$113)","v":2486.3122629548061},null,"תקופת התייחסות אשתקד",{"$f":"ytd_period(period_shift(I9,-12))","v":201012}]
[null,"2007-07-06T19:13:40.389",773.30146482864893,200707]
[null,"2007-07-07T23:21:19.978",988.13015980578143,200707,null,null,null,"מכירות חודשיות, 12 חודשים אחרונים","#","תקופה","חודש","סה\"כ מכירות"]
[null,"2007-07-16T01:49:02.274",550.61916024554193,200707,null,null,null,null,1,{"$f":"date2period(I7)","v":201202},{"$f":"TEXT(period2date(J16),\"mmm-yy\")","v":"Feb-12"},{"$f":"SUMPRODUCT(($J16=$D$6:$D$113)*$C$6:$C$113)","v":838.98740291363038}]
[null,"2007-08-07T12:58:55.957",912.03644674019461,200708,null,null,null,null,{"$f":"I16+1","v":2},{"$f":"period_shift(J$16,-I16)","v":201201},{"$f":"TEXT(period2date(J17),\"mmm-yy\")","v":"Jan-12"},830.65045972420239]
[null,"2007-08-25T07:06:21.418",649.21309363194234,200708,null,null,null,null,{"$f":"I17+1","v":3},201112,"Dec-11",2372.9861405600886]
[null,"2007-09-29T10:31:29.140",549.41185291009015,200709,null,null,null,null,4,201111,"Nov-11",2459.4735355505877]
[null,"2007-10-09T07:01:41.861",982.48640643238491,200710,null,null,null,null,5,201110,"Oct-11",0]
[null,"2007-10-16T00:38:27.101",739.81635939845569,200710,null,null,null,null,6,201109,"Sep-11",686.62343324465621]
[null,"2007-10-29T16:54:20.673",904.6714750490687,200710,null,null,null,null,7,201108,"Aug-11",844.16946299554627]
[null,"2007-11-14T19:58:11.445",823.26049933647016,200711,null,null,null,null,8,201107,"Jul-11",618.40164036263741]
[null,"2007-11-14T21:08:29.584",587.62337252333498,200711,null,null,null,null,9,201106,"Jun-11",722.67717731468872]
[null,"2007-11-18T03:22:12.863",598.28551285896663,200711,null,null,null,null,10,201105,"May-11",1847.7643640387384]
[null,"2007-12-08T16:40:43.718",943.58110007578762,200712,null,null,null,null,11,201104,"Apr-11",899.77444233870949]
[null,"2007-12-20T16:37:39.138",822.41443213885964,200712,null,null,null,null,12,201103,"Mar-11",577.92258484438742]
[null,"2007-12-29T17:44:49.939",830.54851096063112,200712]
[null,"2008-01-03T08:51:28.343",662.60050465512336,200801,null,null,null,"מכירות ממוצעות לתקופה (ללא תקופות \"מתות\") - 12 חודשים:",{"$f":"SUMIF($L$16:$L$27,\"<>0\")/COUNTIF($L$16:$L$27,\"<>0\")","v":1154.4936948988977}]
[null,"2008-01-04T13:59:05.182",921.54871203064647,200801]
[null,"2008-01-26T22:36:24.749",694.53825076474106,200801]
[null,"2008-02-09T08:49:28.285",777.48805697639159,200802]
[null,"2008-02-14T18:30:54.689",838.63939717587232,200802]
[null,"2008-02-15T23:47:37.912",845.29177816522201,200802]
[null,"2008-02-18T20:26:01.452",766.95518482750731,200802]
[null,"2008-03-21T07:53:07.153",579.41687384363229,200803]
[null,"2008-04-17T16:24:34.641",782.25335110084359,200804]
[null,"2008-04-17T21:08:53.776",996.15132916922812,200804]
[null,"2008-04-30T09:25:34.525",613.08344787186468,200804]
[null,"2008-05-21T16:29:07.568",591.27991630905967,200805]
[null,"2008-06-19T02:28:34.276",869.65334526121524,200806]
[null,"2008-06-26T22:19:14.240",957.90993753138127,200806]
[null,"2008-07-04T23:08:21.281",950.43369721352144,200807]
[null,"2008-07-22T10:42:31.017",518.61851757514228,200807]
[null,"2008-07-22T14:46:08.928",784.43886043670784,200807]
[null,"2008-08-22T11:28:32.605",826.16692249576045,200808]
[null,"2008-09-03T14:10:50.208",625.80978132276505,200809]
[null,"2008-09-21T10:13:55.114",949.38321886758763,200809]
[null,"2008-10-05T04:57:25.586",896.06197070317251,200810]
[null,"2008-11-06T18:59:39.118",881.44019219559459,200811]
[null,"2008-11-09T09:30:18.605",966.5628824482817,200811]
[null,"2008-11-29T00:14:56.518",890.59520200879342,200811]
[null,"2008-12-05T09:36:25.902",646.7932940814278,200812]
[null,"2008-12-14T05:55:06.077",691.08006213255305,200812]
[null,"2008-12-14T18:06:20.111",778.69860477186739,200812]
[null,"2008-12-27T04:58:37.419",576.53013748592753,200812]
[null,"2008-12-30T12:36:14.138",665.58875732194792,200812]
[null,"2009-01-24T15:53:10.624",807.55920886703507,200901]
[null,"2009-01-28T15:40:21.498",763.90517237055099,200901]
[null,"2009-02-02T05:46:49.431",692.00951493852324,200902]
[null,"2009-02-11T07:09:13.422",758.11088034964041,200902]
[null,"2009-02-21T02:59:56.035",611.06784876583788,200902]
[null,"2009-05-17T02:49:06.091",587.08197991961219,200905]
[null,"2009-06-22T16:19:46.237",732.1107613689137,200906]
[null,"2009-08-04T14:41:46.561",658.17878926118078,200908]
[null,"2009-09-08T23:02:21.292",758.045462278983,200909]
[null,"2009-10-21T17:09:00.603",798.99571861414893,200910]
[null,"2009-11-13T21:11:28.598",654.43156710145843,200911]
[null,"2009-11-25T21:00:09.872",541.23311422748986,200911]
[null,"2009-11-26T10:41:20.825",645.41216755862115,200911]
[null,"2009-11-27T05:23:00.800",964.9295234767369,200911]
[null,"2009-12-20T20:32:34.538",673.99979137286789,200912]
[null,"2010-01-15T17:52:56.504",566.25018387842556,201001]
[null,"2010-01-16T14:16:02.494",530.98947274075749,201001]
[null,"2010-01-30T10:06:02.540",893.59714743053689,201001]
[null,"2010-02-07T19:06:57.144",670.76812709070623,201002]
[null,"2010-02-18T02:40:30.535",502.58960643123186,201002]
[null,"2010-03-28T02:28:10.478",630.54923893439172,201003]
[null,"2010-04-23T22:37:39.526",637.09640459755849,201004]
[null,"2010-04-25T20:11:15.481",659.00560837482135,201004]
[null,"2010-06-08T10:47:21.310",784.44940361711133,201006]
[null,"2010-07-20T07:38:42.230",995.22301959400488,201007]
[null,"2010-08-06T08:14:58.229",939.12377950106679,201008]
[null,"2010-08-10T06:53:56.748",883.11403932826283,201008]
[null,"2010-08-16T07:14:04.313",934.33876033162119,201008]
[null,"2010-08-28T09:01:53.460",610.14783186181148,201008]
[null,"2010-09-26T21:02:37.350",569.11349586172366,201009]
[null,"2010-10-10T00:27:33.467",506.41586041850007,201010]
[null,"2010-11-19T21:25:40.984",707.28431409296945,201011]
[null,"2010-11-28T23:02:33.112",806.36381671816605,201011]
[null,"2010-12-06T15:38:12.081",949.74716504468165,201012]
[null,"2010-12-19T13:11:44.290",733.46088240654831,201012]
[null,"2010-12-26T01:28:05.140",596.42924129124276,201012]
[null,"2011-01-21T12:24:19",940.90807524249203,201101]
[null,"2011-02-14T07:48:13.027",969.78598181101074,201102]
[null,"2011-02-21T16:56:42.348",575.61820590130355,201102]
[null,"2011-03-24T18:57:08.266",577.92258484438742,201103]
[null,"2011-04-10T09:15:34.587",899.77444233870949,201104]
[null,"2011-05-27T00:13:01.778",956.05797415894142,201105]
[null,"2011-05-28T01:05:10.363",891.70638987979703,201105]
[null,"2011-06-23T12:30:50.646",722.67717731468872,201106]
[null,"2011-07-02T10:54:51.115",618.40164036263741,201107]
[null,"2011-08-14T06:24:41.218",844.16946299554627,201108]
[null,"2011-09-22T17:58:22.517",686.62343324465621,201109]
[null,"2011-11-09T23:44:55.969",797.03716783271648,201111]
[null,"2011-11-16T00:39:36.518",940.50846508896507,201111]
[null,"2011-11-29T14:21:37.788",721.92790262890628,201111]
[null,"2011-12-25T07:58:27.741",617.35655396114055,201112]
[null,"2011-12-28T06:32:16.518",924.50982188824923,201112]
[null,"2011-12-29T14:25:12.519",831.11976471069852,201112]
[null,"2012-01-02T14:56:43.540",830.65045972420239,201201]
[null,"2012-02-24T16:09:24.308",838.98740291363038,201202]
[null,"2012-04-11T14:46:40.309",914.91995831759175,201204]