{"id":17391,"date":"2023-07-13T06:00:00","date_gmt":"2023-07-12T21:00:00","guid":{"rendered":"https:\/\/www.muscle-hypertrophy.com\/?p=17391"},"modified":"2023-02-21T12:55:22","modified_gmt":"2023-02-21T03:55:22","slug":"%e7%86%b1%e4%b8%ad%e7%97%87%e6%90%ac%e9%80%81%e4%ba%ba%e5%93%a1%e6%95%b0%e3%81%ae%e4%ba%88%e6%b8%ac%e3%81%a8%e5%ae%9f%e9%9a%9b","status":"publish","type":"post","link":"https:\/\/www.muscle-hypertrophy.com\/?p=17391","title":{"rendered":"2019\u5e74\u306e\u71b1\u4e2d\u75c7\u642c\u9001\u4eba\u54e1\u6570\u306e\u4e88\u6e2c\u3068\u5b9f\u969b\u3092EXCEL\u306e\u7d44\u307f\u5408\u308f\u305b\u30b0\u30e9\u30d5\u3067\u63cf\u304f"},"content":{"rendered":"\n<p>\u3000\u3053\u308c\u307e\u3067\u306e\u8a18\u4e8b\u3067\u65e5\u6700\u9ad8\u6c17\u6e29\u3068\u5e73\u5747\u6c34\u84b8\u6c17\u5727\uff0c\u5404\u90fd\u9053\u5e9c\u770c65\u6b73\u4ee5\u4e0a\u4eba\u53e3\u304a\u3088\u3073\u6708\u304b\u3089\u71b1\u4e2d\u75c7\u306e\u642c\u9001\u4eba\u54e1\u6570\u3092\u4e88\u6e2c\u3059\u308b\u56de\u5e30\u5f0f\u306e\u56de\u5e30\u4fc2\u6570\u3092\u63a8\u5b9a\u3057\u3066\u304d\u305f\uff0e<\/p>\n<p>\u3000\u4eca\u56de\u306f\u305d\u306e\u56de\u5e30\u5f0f\u3092\u5143\u306b\u5b9f\u969b\u306e\u30c7\u30fc\u30bf\u3068\u6bd4\u8f03\u3057\u3066\u307f\u305f\u3044\uff0e\u5bfe\u8c61\u306f2019\u5e74\u306e47\u90fd\u9053\u5e9c\u770c\u3068\u3059\u308b\uff0e<\/p>\n<p><!--more--><\/p>\n<h2>\u30af\u30a8\u30ea<\/h2>\n<p>\u3000\u4e0b\u8a18\u30af\u30a8\u30ea\u3092\u767a\u884c\u3057\u3066SQL Server\u304b\u3089\u30c7\u30fc\u30bf\u3092\u62bd\u51fa\u3059\u308b\uff0e\u7d50\u679c\u3092EXCEL\u306b\u30b3\u30d4\u30da\u3057\u3066\u30c6\u30fc\u30d6\u30eb\u5316\u3059\u308b\uff0e<\/p>\n<pre>USE HeatStrokeDB;\nGO\nSELECT\tYear(H.\u65e5\u4ed8)\tAS Year\n,\tMONTH(H.\u65e5\u4ed8)\tAS Month\n,\tH.\u65e5\u4ed8\tAS Date\n,\tP.\u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9\tAS PrefCode\n,\tP.\u90fd\u9053\u5e9c\u770c\tAS Pref\n,\tT.\u65e5\u6700\u9ad8\u6c17\u6e29\tAS Temp\n,\tV.\u65e5\u5e73\u5747\u84b8\u6c17\u5727\tAS Vapor\n,\tP.[15\u6b73\u672a\u6e80\u4eba\u53e3]\tAS Pop14\n,\tP.[15\uff5e64\u6b73\u4eba\u53e3]\tAS Pop14_65\n,\tP.[65\u6b73\u4ee5\u4e0a\u4eba\u53e3]\tAS Pop65\n,\tH.[\u642c\u9001\u4eba\u54e1\uff08\u8a08\uff09]\tAS Num\n,\tCAST(EXP(-21.43 + 0.2858 * T.\u65e5\u6700\u9ad8\u6c17\u6e29 + 0.08022 * V.\u65e5\u5e73\u5747\u84b8\u6c17\u5727 - 0.3035 * MONTH(H.\u65e5\u4ed8) + LOG(P.[15\u6b73\u672a\u6e80\u4eba\u53e3] + P.[15\uff5e64\u6b73\u4eba\u53e3] + P.[65\u6b73\u4ee5\u4e0a\u4eba\u53e3])) AS int)\tAS MODEL1\n,\tCAST(EXP(-20.50 + 0.2850 * T.\u65e5\u6700\u9ad8\u6c17\u6e29 + 0.08056 * V.\u65e5\u5e73\u5747\u84b8\u6c17\u5727 - 0.2976 * MONTH(H.\u65e5\u4ed8) + LOG(P.[15\u6b73\u672a\u6e80\u4eba\u53e3] + P.[65\u6b73\u4ee5\u4e0a\u4eba\u53e3])) AS int)\tAS MODEL2\n,\tCAST(EXP(-20.11 + 0.2844 * T.\u65e5\u6700\u9ad8\u6c17\u6e29 + 0.08155 * V.\u65e5\u5e73\u5747\u84b8\u6c17\u5727 - 0.2957 * MONTH(H.\u65e5\u4ed8) + LOG(P.[65\u6b73\u4ee5\u4e0a\u4eba\u53e3])) AS int)\tAS MODEL3\nFROM\tdbo.T_HeatStroke\tAS H\nINNER\tJOIN\tdbo.T_MaxTemperature\tAS T\nON\tH.\u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9 = T.\u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9\nAND\tH.\u65e5\u4ed8 = T.\u5e74\u6708\u65e5\nINNER\tJOIN\tdbo.T_VaporPressure\tAS V\nON\tH.\u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9 = V.\u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9\nAND\tH.\u65e5\u4ed8 = V.\u5e74\u6708\u65e5\nINNER\tJOIN\tdbo.T_Population\tAS P\nON\tH.\u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9 = P.\u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9\nAND\tYEAR(H.\u65e5\u4ed8) = P.\u8abf\u67fb\u5e74\nORDER\tBY\tH.\u90fd\u9053\u5e9c\u770c\u30b3\u30fc\u30c9, H.\u65e5\u4ed8<\/pre>\n<h2>VBA\u3067\u30b0\u30e9\u30d5\u4f5c\u6210<\/h2>\n<p>\u3000\u4e0b\u8a18\u30b3\u30fc\u30c9\u3092\u5b9f\u884c\u3059\u308b\uff0e<\/p>\n<pre>Option Explicit\n\nSub makechart()\n\nDim mySh1   As Worksheet\nDim mySh2   As Worksheet\n\nSet mySh1 = Worksheets(\"Sheet1\")\nSet mySh2 = Worksheets.Add\n\nDim myLstObj As ListObject\n\nSet myLstObj = mySh1.ListObjects(1)\n\nDim myRng1  As Range\nDim i       As Long\nDim Cht     As Chart\n\nDim j       As Long\nDim myXValue()  As Date\nDim myValue1()  As Integer\nDim myValue2()  As Integer\n\nDim SeriesM As Series\nDim SeriesR As Series\nDim myPref  As String\n\nWith mySh2\n    For i = 1 To 47\n        Set Cht = .Shapes.AddChart2(XlChartType:=xlColumnClustered, _\n                                           Left:=200 * ((i - 1) Mod 6), _\n                                            Top:=200 * ((i - 1) \\ 6), _\n                                          Width:=200, _\n                                         Height:=200).Chart\n        With myLstObj\n            .Range.AutoFilter field:=1, Criteria1:=\"2019\"\n            .Range.AutoFilter field:=4, Criteria1:=i\n            Set myRng1 = Intersect(.DataBodyRange, _\n                                   .Range.SpecialCells(xlCellTypeVisible))\n            myPref = myRng1.Cells(1, 5)\n            For j = 0 To myRng1.Rows.Count - 1\n                ReDim Preserve myXValue(j)\n                ReDim Preserve myValue1(j)\n                ReDim Preserve myValue2(j)\n                \n                myXValue(j) = myRng1.Cells(j + 1, 3)\n                myValue1(j) = myRng1.Cells(j + 1, 14)\n                myValue2(j) = myRng1.Cells(j + 1, 11)\n            Next j\n            Set SeriesM = Cht.SeriesCollection.NewSeries\n            With SeriesM\n                .Name = \"Model\"\n                .XValues = myXValue\n                .Values = myValue1\n                .ChartType = xlLine\n                .Format.Line.Weight = 1#\n                '.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2\n            End With\n            Set SeriesR = Cht.SeriesCollection.NewSeries\n            With SeriesR\n                .Name = \"Real\"\n                .XValues = myXValue\n                .Values = myValue2\n                .ChartType = xlColumnClustered\n            End With\n            .Range.AutoFilter field:=4\n            .Range.AutoFilter field:=1\n        End With\n        With Cht\n            .HasTitle = True\n            .ChartTitle.Caption = myPref\n            .ChartGroups(1).GapWidth = 0\n        End With\n    Next i\nEnd With\n\nEnd Sub<\/pre>\n<h2>\u7d50\u679c<\/h2>\n<p>\u3000\u4e0b\u56f3\u306b\u793a\u3059\uff0e\u9752\u306e\u6298\u308c\u7dda\u30b0\u30e9\u30d5\u304c\u4e88\u6e2c\u30c7\u30fc\u30bf\uff0c\u6a59\u306e\u68d2\u30b0\u30e9\u30d5\u304c\u5b9f\u969b\u306e\u30c7\u30fc\u30bf\u3067\u3042\u308b\uff0e\u4e88\u6e2c\u304c\u3046\u307e\u304f\u884c\u3063\u3066\u3044\u308b\u770c\u3082\u3042\u308c\u3070\u904e\u5c0f\u63a8\u5b9a\uff0c\u904e\u5927\u63a8\u5b9a\u3057\u3066\u3044\u308b\u770c\u3082\u3042\u308b\uff0e\u56de\u5e30\u5f0f\u306b\u53d6\u308a\u8fbc\u3081\u306a\u304b\u3063\u305f\u5730\u57df\u5dee\u304c\u95a2\u4fc2\u3057\u3066\u3044\u308b\u3068\u601d\u3044\u305f\u3044\uff0e<\/p>\n<figure id=\"attachment_17392\" aria-describedby=\"caption-attachment-17392\" style=\"width: 525px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.muscle-hypertrophy.com\/wp-content\/uploads\/2023\/02\/00-2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-large wp-image-17392\" src=\"https:\/\/www.muscle-hypertrophy.com\/wp-content\/uploads\/2023\/02\/00-2-768x1024.png\" alt=\"\u71b1\u4e2d\u75c7\u642c\u9001\u4eba\u54e1\u6570\u306e\u4e88\u6e2c\u3068\u5b9f\u969b\" width=\"525\" height=\"700\" srcset=\"https:\/\/www.muscle-hypertrophy.com\/wp-content\/uploads\/2023\/02\/00-2-768x1024.png 768w, https:\/\/www.muscle-hypertrophy.com\/wp-content\/uploads\/2023\/02\/00-2-225x300.png 225w, https:\/\/www.muscle-hypertrophy.com\/wp-content\/uploads\/2023\/02\/00-2-1152x1536.png 1152w, https:\/\/www.muscle-hypertrophy.com\/wp-content\/uploads\/2023\/02\/00-2-1536x2048.png 1536w, https:\/\/www.muscle-hypertrophy.com\/wp-content\/uploads\/2023\/02\/00-2.png 1600w\" sizes=\"auto, (max-width: 525px) 100vw, 525px\" \/><\/a><figcaption id=\"caption-attachment-17392\" class=\"wp-caption-text\">\u71b1\u4e2d\u75c7\u642c\u9001\u4eba\u54e1\u6570\u306e\u4e88\u6e2c\u3068\u5b9f\u969b<\/figcaption><\/figure>\n<h2>\u307e\u3068\u3081<\/h2>\n<p>\u3000\u6700\u9ad8\u6c17\u6e29\u3068\u5e73\u5747\u6c34\u84b8\u6c17\u5727\uff0c\u90fd\u9053\u5e9c\u770c\u4eba\u53e3\u304a\u3088\u3073\u6708\u304b\u3089\u71b1\u4e2d\u75c7\u642c\u9001\u4eba\u54e1\u6570\u3092\u4e88\u6e2c\u3057\u5b9f\u969b\u306e\u642c\u9001\u4eba\u54e1\u6570\u3068\u6bd4\u8f03\u3057\u305f\uff0e<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3000\u3053\u308c\u307e\u3067\u306e\u8a18\u4e8b\u3067\u65e5\u6700\u9ad8\u6c17\u6e29\u3068\u5e73\u5747\u6c34\u84b8\u6c17\u5727\uff0c\u5404\u90fd\u9053\u5e9c\u770c65\u6b73\u4ee5\u4e0a\u4eba\u53e3\u304a\u3088\u3073\u6708\u304b\u3089\u71b1\u4e2d\u75c7\u306e\u642c\u9001\u4eba\u54e1\u6570\u3092\u4e88\u6e2c\u3059\u308b\u56de\u5e30\u5f0f\u306e\u56de\u5e30\u4fc2\u6570\u3092\u63a8\u5b9a\u3057\u3066\u304d\u305f\uff0e \u3000\u4eca\u56de\u306f\u305d\u306e\u56de\u5e30\u5f0f\u3092\u5143\u306b\u5b9f\u969b\u306e\u30c7\u30fc\u30bf\u3068\u6bd4\u8f03\u3057\u3066\u307f\u305f\u3044\uff0e\u5bfe\u8c61\u306f2019\u5e74\u306e47\u90fd &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/www.muscle-hypertrophy.com\/?p=17391\" class=\"more-link\"><span class=\"screen-reader-text\">&#8220;2019\u5e74\u306e\u71b1\u4e2d\u75c7\u642c\u9001\u4eba\u54e1\u6570\u306e\u4e88\u6e2c\u3068\u5b9f\u969b\u3092EXCEL\u306e\u7d44\u307f\u5408\u308f\u305b\u30b0\u30e9\u30d5\u3067\u63cf\u304f&#8221; \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":17392,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[12918,4601,5436,5689,7594,4525,6841,5488,4531,5463,4689,2884,6766,265,11871,5462,4883,4673,6772,5688,5411,5459,5120,5914,4884,5435,2474,4528,5477,4634,4530,5484,4672,4561,4585,4600,5121,2740,4772,4635,7154,5479,459,4894,4523,5464,5425,6401,6397,5478,2783,13268,3615,11240,5852,2893,13325,13406,4439],"class_list":["post-17391","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology","tag-12918","tag-addchart2","tag-autofilter","tag-caption-2","tag-cast","tag-chart","tag-chartgroups","tag-charttitle","tag-charttype","tag-criteria1","tag-databodyrange","tag-date","tag-dim","tag-excel","tag-exp","tag-field","tag-format","tag-from","tag-gapwidth","tag-hastitle-2","tag-inner","tag-integer","tag-intersect","tag-join","tag-line","tag-listobject","tag-long","tag-name","tag-newseries-2","tag-option-explicit","tag-range","tag-redim-preserve","tag-select","tag-series","tag-seriescollection","tag-shapes","tag-specialcells","tag-sql-server","tag-string","tag-sub","tag-use","tag-values-2","tag-vba","tag-weight","tag-worksheet","tag-xlcelltypevisible","tag-xlcharttype","tag-xlcolumnclustered","tag-xlline","tag-xvalues-2","tag-2783","tag-13268","tag-3615","tag-11240","tag-5852","tag-2893","tag-13325","tag-13406","tag-4439"],"_links":{"self":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/17391","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=17391"}],"version-history":[{"count":6,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/17391\/revisions"}],"predecessor-version":[{"id":17464,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/posts\/17391\/revisions\/17464"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=\/wp\/v2\/media\/17392"}],"wp:attachment":[{"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=17391"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=17391"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muscle-hypertrophy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=17391"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}