[ ‰‚ß‚Ä‚Ì•û‚Ö | ˆê——(ÅVXV‡) | ‘S•¶ŒŸõ | ‰ß‹ŽƒƒO ]
@
w‚P“ú‚Ìì‹ÆH’ö‚ðƒKƒ“ƒgƒ`ƒƒ[ƒg•\Ž¦‚É‚µ‚½‚¢xiyurij‚P“ú‚ði05Žž`01ŽžjŽžŠÔ’PˆÊi‚P‚O•ª’PˆÊj‚Åì‹ÆH’ö‚ðƒKƒ“ƒgƒ`ƒƒ[ƒg•\Ž¦‚É‚µ‚Ä •\‚µ‚½‚¢‚ÆŽv‚Á‚Ä‚¢‚Ü‚·BFX’²‚ׂÄAƒtƒŠ[‚̃Kƒ“ƒgƒ`ƒƒ[ƒg‚ðDL‚µ‚ÄŽŽ‚µ‚Ä‚Ý‚Ü‚µ ‚½‚ªAƒsƒbƒ^ƒŠ‡‚킸‚Å‚µ‚½B‚Ç‚¤‚©‚²‹³Ž¦‚‚¾‚³‚¢B @ sheet1‚ÍŠeì‹ÆH’öiì‹ÆŽžŠÔj‚ð“ü—Í‚·‚éDATA—p‚Æ‚µ‚ÄŽg—p‚µ‚Ü‚·B 쬂µ‚Ä‚¢‚é“à—e‚ÍA @ @@ A @@@ B @ C @@@D @@ E@@ @F @@@G H I J 10 ‘æ1ƒOƒ‹[ƒv@@@•Ö–¼ “ž’…ŽžŠÔ •Ö–¼@o”ŽžŠÔ@ 11 ››•Ö 06:00 ››•Ö 08:00@EEEEEE 12 ì‹ÆŽžŠÔ 6:15 `@@@7:30@EEEEE
14 ‘æ2ƒOƒ‹[ƒv@@@@•Ö–¼@“ž’…ŽžŠÔ@@•Ö–¼@o”ŽžŠÔEEEEE
15@@@@@@@@@@@››•Ö@09:00 @››•Ö@10:30 EEEEE
16@@@@@@@@@@@ì‹ÆŽžŠÔ 9:15 @`@@ 10:00EEEEE
@
‚ÆŒ¾‚¤‚Ó‚¤‚ɃZƒ‹C,D,E,F‚Å‚P‰ñ‚Ìì‹Æ‚Æ‚È‚Á‚Ä‚¨‚èAƒZƒ‹GˆÈ~‚ÍC,D,E,F,‚ª ‚P‚O‰ñ“¯‚¶‚Ì‚ªŒJ‚è•Ô‚è‚Ü‚·B c‚̃Oƒ‹[ƒv‚Å‚·‚ªAŠeƒOƒ‹[ƒv‚ÌŠÔ‚É‚PsŠJ‚¯‚ÄA‘æ30ƒOƒ‹[ƒv‚Ü‚Å‚ ‚è‚Ü‚·B @ sheet2‚̓Kƒ“ƒgƒ`ƒƒ[ƒg•\‚ÅA“à—e‚Í @@@A B C D E F G H I J K L EEEEE 1 5Žž 6Žž@@@ EEEEE01Žž‚Ü‚Å 2 10 20 30 40 50 10 20 30 40 50 @EEEEE 3 ‘æ1ƒOƒ‹[ƒv ------------------------------------------------------------------------ 4 5 ‘æ2ƒOƒ‹[ƒv ------------------------------------------------------------------------ 6 7 ‘æ3ƒOƒ‹[ƒv -------------------------------------------------------------------------- 8 @ ‚ÆŒ¾‚¤‚Ó‚¤‚Èì‚è‚É‚È‚Á‚Ä‚¢‚Ü‚·Bƒ`ƒƒ[ƒg‚Ì•\Ž¦‚ɂ‚¢‚Ä‚ÍAsheet1‚Ìdata“ü—Í‚Å ŠeƒOƒ‹[ƒv‹¤Aʼn‚Ìì‹Æ‚©‚ç‚T‰ñ–Ú‚Ìì‹Æ‚Ü‚Åã’ii‘æ2ƒOƒ‹[ƒv‚̃`ƒƒ[ƒg‚ð—á‚É ‚µ‚Ü‚·‚Æ‚Ts–Ú‚ÉA‚U‰ñ–ÚˆÈ~‚͉º’ii‚Us–Új‚Ƀ`ƒƒ[ƒg‚ª“ü‚è‚Ü‚·B @ –”A•Ö/“ž’…ŽžŠÔE•Ö/o”ŽžŠÔ‚ňê‚‚̃`ƒƒ[ƒg‚ª“ü‚èA‚»‚̃`ƒƒ[ƒg‚Éd‚Ë‚Äì‹Æ ŽžŠÔ‚̃`ƒƒ[ƒg‚ð“ü‚ꂽ‚¢‚ÆŽv‚¢‚Ü‚·B @ ‚Ç‚¤‚©‚²‹³Žö‰º‚³‚¢B‚¨Šè‚¢‚µ‚Ü‚·B [Excel2010 / OS win7]
‚È‚©‚È‚©‰ñ“š‚ª•t‚©‚È‚¢‚Ì‚ÅŠm”F‚Å‚·‚ªA10•ª‚²‚Ƃ̃XƒP[ƒ‹‚ÅA15 •ª‚Æ‚¢‚¤‚Ì‚Í‚Ç‚Ì ‚悤‚É•\Ž¦‚µ‚½‚¢‚Ì‚Å‚µ‚傤‚©B ‚Ü‚½A1ŽžŠÔ‚ð5ƒZƒ‹‚ÅA‚Æ‚¢‚¤‚悤‚ÉŒ©‚¦‚é‚Ì‚Å‚·‚ªA‚»‚¤‚·‚é‚Æ1ƒZƒ‹‚Í10•ªƒXƒP[ƒ‹ ‚Å‚Í‚È‚‚È‚è‚Ü‚·‚æ‚ËH
ƒKƒ“ƒgƒ`ƒƒ[ƒg‚̓Zƒ‹‚É¡‚È‚Ç‚Ì•¶Žš‚Å‚Í‚È‚AƒI[ƒgƒVƒF[ƒv‚È‚Ç‚ðŽg‚¢‚½‚¢‚Ì‚Å‚µ‚傤‚©B ‚ ‚ÆAŽžŠÔ‚Ì”ÍˆÍ‚Í AM 5:00 ` PM 1:00 ‚Å‚µ‚傤‚©B–¾‚¯‚Ä AM 1:00 ‚Ü‚Å‚Å‚µ‚傤‚©B (Mook)
VBA‚ð’m‚Á‚Ä‚¢‚é‚Æ‚¢‚¤‘O’ñ‚̃Tƒ“ƒvƒ‹ƒR[ƒh‚Å‚·B
V‹KƒuƒbƒN‚É‚Äi¶’[‚©‚ç@Sheet1ASheet2ASheet3‚Æ‚¢‚¤ƒV[ƒg‚ª‚ ‚éƒuƒbƒNj‚É‚ÄA
•W€ƒ‚ƒWƒ…[ƒ‹iModule1j‚É@ƒTƒ“ƒvƒ‹ƒf[ƒ^‹y‚ÑAƒKƒ“ƒgƒ`ƒƒ[ƒg쬃V[ƒg‚̃ŒƒCƒAƒEƒgì¬
'================================================================ Option Explicit Sub mk_sample() Dim g0 As Long Dim g1 As Long With Worksheets("sheet1") .Cells.ClearContents .DrawingObjects.Delete .Columns("a:d").ColumnWidth = 15 .Range("a1:d1").Value = Array("ƒOƒ‹[ƒv–¼", "•Ö–¼", "“ž’…ŽžŠÔ", "o”ŽžŠÔ") .Range("a2:d3").Value = [{"‘æ1ƒOƒ‹[ƒv","‚`‚`•Ö","6:00","8:00";"","ì‹ÆŽžŠÔ","6:15","7:30"}] .Range("a4:d5").Value = [{"‘æ2ƒOƒ‹[ƒv","‚a‚a•Ö","9:00","10:30";"","ì‹ÆŽžŠÔ","9:15","10:00"}] .Range("a6:d7").Value = [{"‘æ3ƒOƒ‹[ƒv","‚b‚b•Ö","7:20","9:00";"","ì‹ÆŽžŠÔ","7:35","8:45"}] End With With Worksheets("sheet2") .Cells.ClearContents .DrawingObjects.Delete .Columns("a:a").ColumnWidth = 15 .Columns("b:bc").ColumnWidth = 5 g1 = 5 For g0 = 2 To 50 Step 6 With .Range(.Cells(1, g0), .Cells(1, g0 + 5)) .MergeCells = True .Value = ((g1 - 1) Mod 12) + 1 & "Žž" .HorizontalAlignment = xlCenter With .Offset(1, 0).Resize(, 6) .Value = Array(0, 10, 20, 30, 40, 50) .HorizontalAlignment = xlLeft .NumberFormat = "general" End With End With g1 = g1 + 1 Next With .Range("a1:a2") .MergeCells = True .HorizontalAlignment = xlCenter .Value = "ƒOƒ‹[ƒv–¼" End With .Rows("1:2").RowHeight = 13.5 With .Rows("3:10") .RowHeight = 30 End With End With End Sub
•W€ƒ‚ƒWƒ…[ƒ‹iModule2j‚ɃKƒ“ƒgƒ`ƒƒ[ƒg쬃vƒƒOƒ‰ƒ€ƒpƒbƒN
'====================================================================== '============================================================ Private st_col As Single Private st_point As Single Private myscale As Single Private sswidth As Single Private mentwidth As Double Private Œö· As Single Private ‰Šú’l As Single Sub open_scale(ŠJŽn—ñ, ŠJŽn—ñ‚܂ł̃Zƒ‹‹Ð, –Ú·‚è‹Ð, Optional sht As Worksheet) '@ƒ`ƒƒ[ƒg쬂·‚éƒV[ƒgî•ñ‚ð“o˜^‚·‚é ' input : ŠJŽn—ñ@--ƒ`ƒƒ[ƒg쬊JŽn—ñ ' ŠJŽn—ñ‚܂ł̃Zƒ‹‹Ð--- —ñ•‚̇Œv’l ' –Ú·‚è‹Ð------------–Ú·‚è‚Æ‚È‚é—ñ‚Ì—ñ• ' sht -------ƒ`ƒƒ[ƒg쬑ÎÛƒV[ƒg If sht Is Nothing Then Set sht = ActiveSheet With sht.Next .Columns("a").ColumnWidth = 12 .Columns("b:c").ColumnWidth = 6 mentwidth = .Range("b1:c1").Width - .Range("a1").Width End With Call calc_Œö·_‰Šú’l(sht.Next) st_col = ŠJŽn—ñ st_point = ŠJŽn—ñ‚܂ł̃Zƒ‹‹Ð myscale = –Ú·‚è‹Ð sswidth = get_point(myscale) End Sub '========================================================================= Function mk_shape(ByVal rng As Range, ByVal ŠJŽn As Single, ByVal ‹Ð As Single, Optional s_type As MsoAutoShapeType = msoShapeRectangle, Optional ByVal sht As Worksheet = Nothing) As Shape 'Žw’肳‚ꂽs‚ÉŠJŽnˆÊ’uC‹Ð‚Ìî•ñ‚©‚çAƒ`ƒƒ[ƒg‚ð쬂·‚é 'input : rng---쬂·‚és‚ð•\‚·RangeƒIƒuƒWƒFƒNƒg '@@@@@@ŠJŽn---ƒ`ƒƒ[ƒg쬊JŽnˆÊ’u‚ðŠJŽn—ñ‚©‚ç‚Ì—ñ•’PˆÊ‚ÅŽw’è ' ‹Ð-----ƒ`ƒƒ[ƒg쬋Ђð—ñ•’PˆÊ‚ÅŽw’è ' s_type--쬂·‚éƒI[ƒgƒVƒFƒCƒv‚ÌŽí—ÞiMsoAutoShapeType ƒNƒ‰ƒX‚̒蔂ɀ‹’j ' sht----ƒ`ƒƒ[ƒg‚ð쬂·‚éƒV[ƒgƒIƒuƒWƒFƒNƒg ' 'output : mk_shape----쬂µ‚½ShapeƒIƒuƒWƒFƒNƒg Dim mkleft As Single Dim mkwidth As Single Dim mk_inf As Variant If sht Is Nothing Then Set sht = ActiveSheet mk_inf = get_mk_locate_inf(rng, ŠJŽn, ‹Ð, sht) mkleft = mk_inf(1) mkwidth = mk_inf(2) With rng Set mk_shape = sht.Shapes.AddShape(s_type, mkleft, .Top, mkwidth, .Height) End With End Function '========================================================================== Function mk_line(ByVal rng As Range, ByVal ŠJŽn As Single, ByVal ‹Ð As Single, Optional ByVal t_rate As Long = 50, Optional ByVal sht As Worksheet = Nothing) As Shape 'Žw’肳‚ꂽs‚ÉŠJŽnˆÊ’uC‹Ð‚Ìî•ñ‚©‚çAƒ`ƒƒ[ƒg(ƒ‰ƒCƒ“j‚ð쬂·‚é 'input : rng---쬂·‚és‚ð•\‚·RangeƒIƒuƒWƒFƒNƒg '@@@@@@ŠJŽn---ƒ`ƒƒ[ƒg쬊JŽnˆÊ’u‚ðŠJŽn—ñ‚©‚ç‚Ì—ñ•’PˆÊ‚ÅŽw’è ' ‹Ð-----ƒ`ƒƒ[ƒg쬋Ђð—ñ•’PˆÊ‚ÅŽw’è ' ‚”_rate----ƒ‰ƒCƒ“‚ð쬂·‚é‚‚³itopjˆÊ’u‚ðRng.heght‚ÌŠ„‡‚ÅRng.TopˆÊ’u‚©‚ç쬂·‚é(0`100‚ÅŽw’èj ' —á@@rng‚Æ‚µ‚ÄRange("A1")At_rate‚Æ‚µ‚ÄA20‚ªŽw’肳‚ê‚½ê‡ ' @@@@@@range("a1").top+range("a1").height*t_rate/100 ‚ªƒ‰ƒCƒ“쬂·‚écˆÊ’u‚Æ‚È‚é ' sht----ƒ`ƒƒ[ƒg‚ð쬂·‚éƒV[ƒgƒIƒuƒWƒFƒNƒg 'output : mk_line----쬂µ‚½ShapeƒIƒuƒWƒFƒNƒg Dim mkleft As Single Dim mkright As Single Dim mk_inf As Variant If sht Is Nothing Then Set sht = ActiveSheet mk_inf = get_mk_locate_inf(rng, ŠJŽn, ‹Ð, sht) mkleft = mk_inf(1) mkright = mkleft + mk_inf(2) With rng Set mk_line = sht.Shapes.AddLine(mkleft, .Top + .Height * t_rate / 100, mkright, .Top + .Height * t_rate / 100) End With End Function '========================================================================== Private Function get_mk_locate_inf(ByVal rng As Range, ByVal ŠJŽn As Single, ByVal ‹Ð As Single, ByVal sht As Worksheet) As Variant Dim mli(1 To 2) As Single Dim wk As Single Dim wk2 As Single Dim ha As Single Dim ha2 As Single Dim cnv_left As Single Dim cnv_width As Single wk = Int(ŠJŽn / myscale) * myscale wk2 = (ŠJŽn - wk) / myscale ha = Int(‹Ð / myscale) * myscale ha2 = Application.Round((‹Ð - ha) / myscale, 3) cnv_left = get_point(wk + st_point) cnv_width = get_point(ha) If wk2 = 0 Then mli(1) = cnv_left + mentwidth * (st_col - 1 + Int((ŠJŽn - 0.1) / myscale)) Else mli(1) = cnv_left + mentwidth * (st_col - 1 + Int((wk - 0.1) / myscale)) + sswidth * wk2 End If If ha2 = 0 Then If ha = 0 Then mli(2) = cnv_width Else mli(2) = cnv_width + mentwidth * Int((ha - 0.1) / myscale) End If Else If ha = 0 Then mli(2) = sswidth * ha2 Else mli(2) = cnv_width + mentwidth * Int((ha - 0.1) / myscale) + sswidth * ha2 End If End If get_mk_locate_inf = mli() Erase mli() End Function '========================================================================== Private Function get_point(ƒZƒ‹•) get_point = ƒZƒ‹• * Œö· + ‰Šú’l End Function '========================================================================== Private Sub calc_Œö·_‰Šú’l(sht As Worksheet) Dim wk1 As Double Dim wk2 As Double With sht.Cells(1, 1) .ColumnWidth = 1 wk1 = .Width .ColumnWidth = 2 wk2 = .Width End With ‰Šú’l = wk1 * 2 - wk2 Œö· = wk1 - ‰Šú’l End Sub
•W€ƒ‚ƒWƒ…[ƒ‹iModule3j‚É
Option Explicit '============================================================================== Sub main() Const ss = 2 Dim hh As Single Dim idx As Long Dim rw As Long Dim row1 As Long Dim st As Single Dim wk As Double Dim shp As Shape Dim shpnm As String Dim sumwidth As Single Dim sht As Worksheet Set sht = Worksheets("sheet1") With sht row1 = .Cells(.Rows.Count, "a").End(xlUp).Row End With With Worksheets("sheet2") On Error Resume Next .DrawingObjects.Delete On Error GoTo 0 For idx = 1 To ss - 1 sumwidth = sumwidth + .Columns(idx).ColumnWidth Next hh = .Range("b:b").ColumnWidth Call open_scale(ss, sumwidth, hh, Worksheets("sheet2")) idx = 2 rw = 3 Do Until idx > row1 .Cells(rw, 1).Value = sht.Cells(idx, 1).Value wk = (sht.Cells(idx, 4).Value - sht.Cells(idx, 3).Value) / TimeSerial(0, 10, 0) st = (sht.Cells(idx, 3).Value - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_shape(.Rows(rw), st, wk * hh, msoShapeRectangle, Worksheets("sheet2")) With shp .TextFrame.Characters.Text = sht.Cells(idx, 2).Value & " “ž’… : " & Format(sht.Cells(idx, 3).Value, "hh:mm") & " o” : " & Format(sht.Cells(idx, 4).Value, "hh:mm") .TextFrame.HorizontalAlignment = xlHAlignCenter .Fill.ForeColor.RGB = vbRed .Fill.Transparency = 0.75 End With wk = (sht.Cells(idx + 1, 4).Value - sht.Cells(idx + 1, 3).Value) / TimeSerial(0, 10, 0) st = (sht.Cells(idx + 1, 3).Value - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_line(.Rows(rw), st, wk * hh, 80, Worksheets("sheet2")) With shp .Line.ForeColor.RGB = vbBlack .Line.Transparency = 0 .Line.EndArrowheadLength = msoArrowheadLengthMedium .Line.EndArrowheadWidth = msoArrowheadWidthMedium .Line.EndArrowheadStyle = msoArrowheadTriangle .Line.BeginArrowheadStyle = msoArrowheadTriangle End With idx = idx + 2 rw = rw + 1 Loop End With End Sub
Žg—p•û–@
mk_sample‚ðŽÀs‚µ‚Ä‚‚¾‚³‚¢B
Sheet1‚É‚ÍAƒTƒ“ƒvƒ‹ƒf[ƒ^ASheet2‚É‚ÍAƒKƒ“ƒgƒ`ƒƒ[ƒg쬗pƒŒƒCƒAƒEƒg‚ª“WŠJ‚³‚ê‚Ü‚·B
Sheet1‚É•\Ž¦‚³‚ê‚Ä‚¢‚éƒf[ƒ^‚ðŠî‚ÉSheet2‚ɃKƒ“ƒgƒ`ƒƒ[ƒg‚ð쬂µ‚Ü‚·B
main‚ðŽÀs‚µ‚Ä‚Ý‚Ä‚‚¾‚³‚¢B Sheet2‚ɃKƒ“ƒgƒ`ƒƒ[ƒg‚ªì¬‚³‚ê‚Ü‚·B
ŽlŠpŒ`‚ª“ž’…‚©‚ço”‚Ü‚Å‚ÌŽžŠÔ‚ð‚ ‚ç‚킵A—¼–îˆó‚ªì‹ÆŽžŠÔ‚ð‚ ‚ç‚킵‚Ä‚¢‚Ü‚·B
®ASheet3‚ÍAƒvƒƒOƒ‰ƒ€‚ªì‹ÆƒV[ƒg‚Æ‚µ‚ÄŽg‚Á‚Ä‚¢‚Ü‚·‚©‚çA
ˆÚ“®‚â휂͂µ‚È‚¢‚Å‚‚¾‚³‚¢B
ƒf[ƒ^“à—e‚Í‚²Ž¿–â‚Ƃ͈Ⴂ‚Ü‚·‚ªA‚»‚ê‚ÍAmain‚ð‰ðÍ‚µ‚ÄA–{—ˆ‚̃f[ƒ^—p‚É •ÏX‚µ‚Ä‚Ý‚Ä‚‚¾‚³‚¢B
ichinose
‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
‘‚«•û‚ªˆ«‚‚Ä\‚µ–ó‚ ‚è‚Ü‚¹‚ñ‚Å‚µ‚½B
10•ª‚²‚Ƃ̃XƒP[ƒ‹‚ÅA15 •ª‚Æ‚¢‚¤‚̂͂ǂ̂悤‚É•\Ž¦‚µ‚½‚¢‚Ì‚Å‚µ‚傤‚©?
‚Ü‚½A1ŽžŠÔ‚ð5ƒZƒ‹‚ÅA‚Æ‚¢‚¤‚悤‚ÉŒ©‚¦‚é‚Ì‚Å‚·‚ªA‚»‚¤‚·‚é‚Æ1ƒZƒ‹‚Í10•ªƒXƒP[ƒ‹ ‚Å‚Í‚È‚‚È‚è‚Ü‚·‚æ‚ËH
Šm‚©‚ÉA‚PŽžŠÔ‚TƒZƒ‹‚Å‚Í–³‚©‚Á‚½‚Å‚·B‚·‚Ý‚Ü‚¹‚ñB•\Ž¦‚ɂ‚¢‚Ä‚Íichinose—l‚Ì‚²‹³Ž¦‰º‚³‚Á‚½•\Ž¦‚ÅOK‚Å‚·B
ŽžŠÔ‚Ì”ÍˆÍ‚Í AM 5:00 ` PM 1:00 ‚Å‚µ‚傤‚©B–¾‚¯‚Ä AM 1:00 ‚Ü‚Å‚Å‚µ‚傤‚©B
ŽžŠÔ‚ɂ‚¢‚Ä‚ÍA–¾‚¯‚Ä‚ÌAM1F00‚Ü‚Å‚Å‚·B
Ichinose—l
‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
ʼn‚ÉA‚·‚Ý‚Ü‚¹‚ñBVBA‚Í‘S‚‚̉SŽÒ‚Å‚·B
‘‘¬A쬂µ‚Ä’¸‚¢‚½ƒR[ƒh‚ðŽŽ‚µ‚Ä‚Ý‚Ü‚µ‚½B•\Ž¦‚ÌŽd•û‚ɂ‚¢‚Ä‚ÍA‘å–ž‘«‚Å‚·B
Ž„‚ÌDATA‚ɇ‚킹‚È‚ª‚çmain‰ðÍ‚µ‚Ä‚Ý‚Ü‚µ‚½‚ªAƒMƒuƒAƒbƒv‚Å‚·B
•ÏX‚Å‚«‚½‚Ì‚ÍAŽžŠÔ‚ðAM01:00‚ÉL‚΂µ‚½‚Ì‚ÆA‚Q‚SŽžŠÔ•\Ž¦‚É‚Å‚«‚½‚¾‚¯‚Å‚·B
‚à‚¤‚µÚ‚µ‚‚¨Šè‚¢‚µ‚Ü‚·B
E@ˆê‚‚̃Oƒ‹[ƒv‚Ì‚P“ú‚Ìì‹ÆH’ö‚ª–ñ10ì‹ÆiH’öj‚ ‚é‚Ì‚ÅA‚»‚ê‚ɑΉž‚µ‚½ @@ƒ`ƒƒ[ƒg‚ª•\Ž¦‚³‚ê‚éƒR[ƒhB
E‚P‚‚̃Oƒ‹[ƒv‚ð‚Q’i‚É•ª‚¯Aʼn‚ÌH’ö`‚T‰ñ–Ú‚ÌH’ö‚Ü‚Å‚ðã’i‚É•\Ž¦A‚U‰ñ–Ú @@‚ÌH’ö`ÅŒã‚ÌH’ö‚Ü‚Å‚ð‰º’i‚É•\Ž¦‚³‚ê‚éƒR[ƒhB
‚ð‚¨Šè‚¢‚¢‚½‚µ‚Ü‚·B
(Yuri)
>ˆê‚‚̃Oƒ‹[ƒv‚Ì‚P“ú‚Ìì‹ÆH’ö‚ª–ñ10ì‹ÆiH’öj‚ ‚é‚Ì‚ÅA‚»‚ê‚ɑΉž‚µ‚½ >@@ƒ`ƒƒ[ƒg‚ª•\Ž¦‚³‚ê‚éƒR[ƒhB >‚P‚‚̃Oƒ‹[ƒv‚ð‚Q’i‚É•ª‚¯Aʼn‚ÌH’ö`‚T‰ñ–Ú‚ÌH’ö‚Ü‚Å‚ðã’i‚É•\Ž¦A‚U‰ñ–Ú >@@‚ÌH’ö`ÅŒã‚ÌH’ö‚Ü‚Å‚ð‰º’i‚É•\Ž¦‚³‚ê‚éƒR[ƒhB
‚±‚ê‚ç‚̃R[ƒh‚ð“Še‚µ‚Ä‚µ‚Ü‚¦‚ÎAŽ„‚ª‘S•”ì‚邱‚Æ‚É‚È‚Á‚Ä‚µ‚Ü‚¢‚Ü‚·B Ž„‚ÍA‚»‚¤‚¢‚¤“Še‚ð‚È‚é‚ׂ‚µ‚È‚¢—l‚É‚µ‚Ä‚¢‚Ü‚·B
‚Ü‚¸A CADAEAF—ñ‚̈ê‰ñ–ڂ̃f[ƒ^‚¾‚¯AƒKƒ“ƒgƒ`ƒƒ[ƒg‚ð쬂·‚鎖‚ðl‚¦‚Ä‚‚¾‚³‚¢B
o—ˆ‚½‚çAƒR[ƒh‚ð’ñŽ¦‚µ‚Ä‚‚¾‚³‚¢iMain‚̃R[ƒh‚¾‚¯‚Å‚æ‚¢‚Å‚·‚ËjB
ŽŸ‚É@5‰ñ‚܂ł̃f[ƒ^‚ŃKƒ“ƒgƒ`ƒƒ[ƒg쬂ðl‚¦‚Ä‚‚¾‚³‚¢B ‚±‚ê‚ÍAƒ‹[ƒvˆ—iDo`Loop‚©AFor`Next‚ðŽg‚¤j‚ðŽg‚¦‚Ή”\‚Å‚·B ˆê‰ñ–ڂ̃f[ƒ^‚©‚ç2‰ñ–ڂɈڂ邽‚ß‚É‚ÍH@–”A2‰ñ–ڂ̃f[ƒ^‚©‚ç3‰ñ–ڂ̃f[ƒ^‚Ɉڂé‚É‚ÍH ‚Ç‚¤‚·‚ê‚΂悢‚©‚ðl‚¦‚Ä‚‚¾‚³‚¢B‰½‚ç‚©‚Ì‹K‘¥‚ª‚ ‚é‚Í‚¸‚Å‚·B‚»‚ꂪ‚í‚©‚ê‚ÎA쬂·‚邱‚Æ‚ª‚Å‚«‚Ü‚·B
ƒ‹[ƒvˆ—‚ª‰½‚È‚Ì‚©‚í‚©‚ç‚È‚¯‚ê‚ÎAƒlƒbƒg‚ðŒŸõ‚µ‚½‚èAExcel/VBA‚ÌHelp‚É‚àà–¾‚ª‚³‚ê‚Ä‚¢‚Ü‚·‚©‚çAŒ¤‹†‚µ‚Ä‚Ý‚Ä‚‚¾‚³‚¢B
‚±‚±‚Ü‚Åo—ˆ‚ê‚ÎA6‰ñ–ÚˆÈ~Aì¬s‚ð•Ï‚¦‚ê‚΂悢‚Ì‚Å‚·‚©‚炨‚»‚ç‚‚Å‚«‚Ü‚·B
ˆÈã‚̂悤‚ÈH’ö‚Ål‚¦‚Ä‚Ý‚Ä‚ÍA‚¢‚©‚ª‚Å‚·‚©II
ichinose
FX‚ƃqƒ“ƒg‚ð‚ ‚è‚ª‚Æ‚¤‚²‚´‚¢‚Ü‚·B
‚±‚ê‚©‚çA‚¿‚å‚Á‚Æl‚¦‚Ä‚Ý‚Ü‚·B
(yuri)
ichinose—l
‚·‚Ý‚Ü‚¹‚ñBichinose—l‚ª‚¨‚Á‚µ‚á‚é’Ê‚èA‚Æ‚è‚ ‚¦‚¸C,D,E,F‚ðƒKƒ“ƒgƒ`ƒƒ[ƒg‚Å•\‚·ˆ×Amain‚̃R[ƒh‚ð‚µ•ÏX‚µ‚ÄA‚â‚Á‚Ä‚Ý‚Ü‚µ‚½‚ªA‚¤‚Ü‚s‚«‚Ü‚¹‚ñB
‚à‚¤‚µAÚ‚µ‚‚¨Šè‚¢‚µ‚Ü‚·B
ƒR[ƒh‚̉½ˆ‚ð•ÏX‚·‚é‚Ì‚©A–¢‚¾‰ð“Ç‚Å‚«‚Ä‚¢‚Ü‚¹‚ñB
(YURI)
>main‚̃R[ƒh‚ð‚µ•ÏX‚µ‚ÄA‚â‚Á‚Ä‚Ý‚Ü‚µ‚½‚ªA‚¤‚Ü‚s‚«‚Ü‚¹‚ñB ‚ǂ̂悤‚ɃR[ƒh‚ð•ÏX‚µ‚½‚Ì‚Å‚µ‚傤‚©H ƒR[ƒh‚ð’ñŽ¦‚µ‚Ä‚‚¾‚³‚¢B
VBA‚ÌŽ¿–â‚Ìê‡A Žd—l‚Ì’ñŽ¦ ‹@”\ŠT—v‚Ì‹Lq “ü—̓f[ƒ^‚ÌÚ×à–¾i—á‘èƒf[ƒ^‚Ì’ñŽ¦j o—̓f[ƒ^‚ÌÚ×à–¾i—á‘èƒf[ƒ^‚Ì’ñŽ¦j
o—ˆ‚Ä‚¢‚éƒR[ƒh–”‚ÍA‚²Ž©•ª‚ÅŽŽ‚µ‚½ƒR[ƒh‚Ì’ñŽ¦
‚Ü‚Å‚ÍAŽ„‚ÍŽ¿–âŽÒ‚³‚ñ‚Ì•K{’ñŽ¦€–Ú‚¾‚ÆŽv‚Á‚Ä‚¢‚Ü‚·B
YURI‚³‚ñ‚ÍAŽd—l‚Ì‹Lq‚ÉŠÖ‚µ‚Ä‚ÍA‰SŽÒ‚Æ‚ÍŽv‚¦‚È‚¢‚Ù‚Ç‚æ‚‹Lq‚³‚ê‚Ä‚¢‚é@‚Æ Ž„‚ÍŽv‚¢‚Ü‚·B
‚±‚±‚ÍA•×‹‚Ìê‚Å‚·‚©‚çAì“®‚µ‚È‚¢ƒR[ƒh‚Å‚à‚æ‚¢‚Ì‚Å‚²Ž©•ª‚ªl‚¦‚Ä쬂µ‚½ƒR[ƒh‚ð ’ñŽ¦‚µ‚Ä‚‚¾‚³‚¢B
쬈˗Š‚ÌŒfŽ¦”‚ł͂Ȃ¢‚Å‚·‚æ‚ËH ‚à‚µA쬈˗Š‚à‚æ‚¢‚È‚çAŽ„‚àì‚Á‚Ä‚à‚ç‚¢‚½‚¢‚Å‚·‚ËII ‚»‚¤‚·‚ê‚ÎAlŒ”ï•‚©‚è‚Ü‚·B
–{‘è‚Å‚·B
‘O‰ñ’ñŽ¦‚µ‚½ mk_sample‚Å쬂³‚ê‚é@ƒKƒ“ƒgƒ`ƒƒ[ƒg‚ÌŠîƒf[ƒ^iSheet1jA ƒKƒ“ƒgƒ`ƒƒ[ƒgƒŒƒCƒAƒEƒgiSheet2jA‚±‚ê‚ÆYURI‚³‚ñ‚Ì•ø‚¦‚Ä‚¢‚é–â‘è‚̈Ⴂ‚ÍH
Šîƒf[ƒ^‚Ì‹Lq‚³‚ê‚Ä‚¢‚éƒZƒ‹”Ô’n‚̈Ⴂ‚¾‚¯‚Å‚·‚æ‚ËII Sheet2‚̃ŒƒCƒAƒEƒg‚ª
>ŽžŠÔ‚ðAM01:00‚ÉL‚΂µ‚½‚Ì‚ÆA‚Q‚SŽžŠÔ•\Ž¦
‚Æ‚¢‚¤‚Ì‚ÍA‚±‚±‚Å‚ÍA‚»‚ê‚Ù‚Ç‘å‚«‚¢–â‘è‚Å‚Í‚ ‚è‚Ü‚¹‚ñB
mk_sample‚Å쬂³‚ꂽ@Sheet1‚̃f[ƒ^‚ðŒ©‚Ä‚Ý‚é‚ÆEEEA
“ž’…ŽžAo”Žž‚ÍA‚»‚ꂼ‚ê@‚b—ñA‚c—ñiŽlŠpŒ`‚Ìì}‚ÉŽg—pj ì‹Æ‚ÌŠJŽnEI—¹Žž‚à‚»‚ꂼ‚ê@‚b—ñA‚c—ñi—¼–îˆó‚Ìì}‚ÉŽg—pj
‚Å‚·‚ËII
–”Aƒf[ƒ^ŠJŽns‚Í‚Qs–Ú‚©‚çAŠJŽn‚³‚êAˆê‚‚̃Oƒ‹[ƒv‚Å‚QsŽg‚Á‚Ä‚¢‚Ü‚·‚æ‚ËH
ŽŸ‚Émain‚Æ‚¢‚¤ƒvƒƒVƒWƒƒ[‚ð‚Ý‚Ä‚Ý‚Ü‚µ‚傤B
'============================================================================== Sub main() Const ss = 2 Dim hh As Single Dim idx As Long Dim rw As Long Dim row1 As Long Dim st As Single Dim wk As Double Dim shp As Shape Dim shpnm As String Dim sumwidth As Single Dim sht As Worksheet Set sht = Worksheets("sheet1") With sht row1 = .Cells(.Rows.Count, "a").End(xlUp).Row End With With Worksheets("sheet2") On Error Resume Next .DrawingObjects.Delete On Error GoTo 0 For idx = 1 To ss - 1 sumwidth = sumwidth + .Columns(idx).ColumnWidth Next hh = .Range("b:b").ColumnWidth Call open_scale(ss, sumwidth, hh, Worksheets("sheet2")) idx = 2 rw = 3 Do Until idx > row1 .Cells(rw, 1).Value = sht.Cells(idx, 1).Value wk = (sht.Cells(idx, 4).Value - sht.Cells(idx, 3).Value) / TimeSerial(0, 10, 0) st = (sht.Cells(idx, 3).Value - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_shape(.Rows(rw), st, wk * hh, msoShapeRectangle, Worksheets("sheet2")) With shp .TextFrame.Characters.Text = sht.Cells(idx, 2).Value & " “ž’… : " & Format(sht.Cells(idx, 3).Value, "hh:mm") & " o” : " & Format(sht.Cells(idx, 4).Value, "hh:mm") .TextFrame.HorizontalAlignment = xlHAlignCenter .Fill.ForeColor.RGB = vbRed .Fill.Transparency = 0.75 End With wk = (sht.Cells(idx + 1, 4).Value - sht.Cells(idx + 1, 3).Value) / TimeSerial(0, 10, 0) st = (sht.Cells(idx + 1, 3).Value - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_line(.Rows(rw), st, wk * hh, 80, Worksheets("sheet2")) With shp .Line.ForeColor.RGB = vbBlack .Line.Transparency = 0 .Line.EndArrowheadLength = msoArrowheadLengthMedium .Line.EndArrowheadWidth = msoArrowheadWidthMedium .Line.EndArrowheadStyle = msoArrowheadTriangle .Line.BeginArrowheadStyle = msoArrowheadTriangle End With idx = idx + 2 rw = rw + 1 Loop End With End Sub
ƒ‹[ƒvˆ—‚Ì’†‚Ås‚Á‚Ä‚¢‚é‚Ì‚ªSheet1‚̈ê‚‚̃Oƒ‹[ƒv‚ɑ΂·‚éSheet2‚Ö‚Ìì}‚Å‚·B
idx = 2@@'Sheet1‚ÌŠîƒf[ƒ^‚Ì2s–Ú‚©‚çŽQÆ‚·‚é rw = 3@@'Sheet2‚̃Kƒ“ƒgƒ`ƒƒ[ƒg쬊JŽns @@@E @@@E
.Cells(rw, 1).Value = sht.Cells(idx, 1).Value '‚P@ƒOƒ‹[ƒv–¼‚ðƒZƒbƒg '«‚Q@ŽlŠpŒ`‚Ìì}@ wk = (sht.Cells(idx, 4).Value - sht.Cells(idx, 3).Value) / TimeSerial(0, 10, 0) fª‚Q|‚P@“ž’…‚©‚ço”‚ÌŽžŠÔ‚©‚ç10•ªŠÔŠu‚̃Zƒ‹•‚ÌŽg—p”‚ÌŒvŽZ st = (sht.Cells(idx, 3).Value - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh
' ª‚Q|‚Q@ŽlŠpŒ`ì}ŠJŽnˆÊ’u‚ÌŒvŽZ Set shp = mk_shape(.Rows(rw), st, wk * hh, msoShapeRectangle, Worksheets("sheet2")) With shp .TextFrame.Characters.Text = sht.Cells(idx, 2).Value & " “ž’… : " & Format(sht.Cells(idx, 3).Value, "hh:mm") & " o” : " & Format(sht.Cells(idx, 4).Value, "hh:mm") .TextFrame.HorizontalAlignment = xlHAlignCenter .Fill.ForeColor.RGB = vbRed .Fill.Transparency = 0.75 End With ' ª‚Q|‚R@ŒvŽZ’l‚©‚çAŽlŠpŒ`‚Ìì}‚ÆF‚âƒeƒLƒXƒg‚ÌÝ’è
'«‚R@—¼–îˆóü‚Ìì}@ wk = (sht.Cells(idx + 1, 4).Value - sht.Cells(idx + 1, 3).Value) / TimeSerial(0, 10, 0) fª‚R|‚P@ì‹ÆŠJŽnŽžŠÔ‚©‚ç10•ªŠÔŠu‚̃Zƒ‹•‚ÌŽg—p”‚ÌŒvŽZ st = (sht.Cells(idx + 1, 3).Value - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh ' ª‚R|‚Q@—¼–îˆóüì}ŠJŽnˆÊ’u‚ÌŒvŽZ Set shp = mk_line(.Rows(rw), st, wk * hh, 80, Worksheets("sheet2")) With shp .Line.ForeColor.RGB = vbBlack .Line.Transparency = 0 .Line.EndArrowheadLength = msoArrowheadLengthMedium .Line.EndArrowheadWidth = msoArrowheadWidthMedium .Line.EndArrowheadStyle = msoArrowheadTriangle .Line.BeginArrowheadStyle = msoArrowheadTriangle End With ' ª‚R|‚R@ŒvŽZ’l‚©‚çA—¼–îˆóü‚Ìì} idx = idx + 2@'‚S@ŽŸ‚̃Oƒ‹[ƒv‚̃f[ƒ^‚ª‚ ‚és‚É•Ï”idx‚ðÝ’è‚·‚é rw = rw + 1 '‚T@ŽŸ‚̃Oƒ‹[ƒv‚̃`ƒƒ[ƒgì}s‚É•Ï”rw‚ðÝ’è‚·‚é
ˆÈãAŠÈ’P‚ɃRƒƒ“ƒg‚ð•t‚¯‚Ü‚µ‚½B
‚Q|‚P
wk = (sht.Cells(idx, 4).Value - sht.Cells(idx, 3).Value) / TimeSerial(0, 10, 0)
sht.Cells(idx, 4).Value@‚±‚ê‚ÍASheet1‚Ìidxs‚Ì‘æ4—ñ‚̃Zƒ‹‚Ì’l‚Å‚·B ‚‚܂èAo”Žži‚c—ñj‚Ì’l‚Å‚·
sht.Cells(idx, 3).Value)@‚±‚ê‚ÍASheet1‚Ìidxs‚Ì‘æ3—ñ‚̃Zƒ‹‚Ì’l‚Å‚·B ‚‚܂èA“ž’…Žži‚b—ñj‚Ì’l‚Å‚·
3‚Æ‚©4‚ÍA‚`—ñ‚©‚çA1,2,3EEE‚Æ”‚¦‚½”Žš‚Å‚·B
‚³‚ÄA–{ƒf[ƒ^‚Ì“ž’…ŽžAo”Žž‚É•Ï‚¦‚é‚É‚ÍH
“¯‚¶—v—Ì‚Å@‚Q|‚RA‚R|‚PA‚R|‚Q‚ð‚ǂ̂悤‚É•Ï‚¦‚ê‚΂悢‚Ì‚Å‚µ‚傤‚©H
‚S@‚â@‚T‚Íi‰ŠúÝ’è‚àŠÜ‚ß‚ÄjA•ÏX‚·‚éH@‚»‚ê‚Æ‚à‚µ‚È‚¢‚Ì‚©H
l‚¦‚Ä‚Ý‚Ä‚‚¾‚³‚¢
ichinose
‰½“x‚à‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B–”A•ª‚©‚èˆÕ‚‚²‹³Žö‚µ‚Ä’¸‚«Š´ŽÓ‚µ‚Ä‚¨‚è‚Ü‚·B
vba‚ÉŠÖ‚µ‚Ä‚ÍAŽ„‚àˆÈ‘O‚©‚ç•×‹‚ð‚µ‚Ä‚µ‚Å‚àŠo‚¦‚È‚‚Ä‚ÍEEE‚ÆŽv‚Á‚Ä‚Í‚¢‚½‚à‚Ì‚ÌA“‚‚ÄEEB
ichinose—l‚Ì‚¨‚Á‚µ‚á‚é’Ê‚èAŽ„‚ÍŠF‚³‚ñ‚Éì‹ÆˆË—Š‚ð‚¨Šè‚¢‚µ‚Ä‚¢‚é‚悤‚ÈŠ´‚¶‚Å‚·‚ËB\‚µ–ó‚ ‚è‚Ü‚¹‚ñB
‚³‚ÄAichinose‚³‚ñ‚ªÚ‚µ‚‘‚©‚ꂽŽ–‚ðŠîDATA‚É“–‚Ä‚Í‚ß‚È‚ª‚çˆê‚‚‚â‚Á‚Ä‚Ý‚Ü‚µ‚½B“r’†AFX‚ƃGƒ‰[‚ào‚Ü‚µ‚½‚ªA‚È‚ñ‚Æ‚©‘æˆê’iŠK‚Í‚Å‚«‚½‚Ý‚½‚¢‚Å‚·B
‚±‚ñ‚ÈŠ´‚¶‚Åmain‚̃R[ƒh‚ð•ÏX‚µ‚Ä‚Ý‚Ü‚µ‚½B
Sub main() Const ss = 2 Dim hh As Single Dim idx As Long Dim rw As Long Dim row1 As Long Dim st As Single Dim wk As Double Dim shp As Shape Dim shpnm As String Dim sumwidth As Single Dim sht As Worksheet Set sht = Worksheets("sheet1") With sht row1 = .Cells(.Rows.Count, "a").End(xlUp).Row End With With Worksheets("sheet2") On Error Resume Next .DrawingObjects.Delete On Error GoTo 0 For idx = 1 To ss - 1 sumwidth = sumwidth + .Columns(idx).ColumnWidth Next hh = .Range("b:b").ColumnWidth Call open_scale(ss, sumwidth, hh, Worksheets("sheet2")) idx = 11 rw = 3 Do Until idx > row1 .Cells(rw, 1).Value = sht.Cells(idx, 1).Value wk = (sht.Cells(idx, 5).Value - sht.Cells(idx, 3).Value) / TimeSerial(0, 10, 0) st = (sht.Cells(idx, 3).Value - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_shape(.Rows(rw), st, wk * hh, msoShapeRectangle, Worksheets("sheet2")) With shp .TextFrame.Characters.Text = sht.Cells(idx, 2).Value & " “ž’… : " & Format(sht.Cells(idx, 3).Value, "hh:mm") & "^" & sht.Cells(idx, 4).Value & " o” : " & Format(sht.Cells(idx, 5).Value, "hh:mm") .TextFrame.HorizontalAlignment = xlHAlignCenter .Fill.ForeColor.RGB = vbRed .Fill.Transparency = 0.5 End With wk = (sht.Cells(idx + 1, 5).Value - sht.Cells(idx + 1, 4).Value) / TimeSerial(0, 10, 0) st = (sht.Cells(idx + 1, 4).Value - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_line(.Rows(rw), st, wk * hh, 80, Worksheets("sheet2")) With shp .Line.ForeColor.RGB = vbBlack .Line.Transparency = 0 .Line.EndArrowheadLength = msoArrowheadLengthMedium .Line.EndArrowheadWidth = msoArrowheadWidthMedium .Line.EndArrowheadStyle = msoArrowheadTriangle .Line.BeginArrowheadStyle = msoArrowheadTriangle End With idx = idx + 2 rw = rw + 1 Loop End With End Sub
‚±‚ê‚ÅA‚Æ‚è‚ ‚¦‚¸Å‰‚Ì‚P‰ñ–Úì‹Æ‚Í•\Ž¦‚Å‚«‚é‚悤‚É‚È‚Á‚½‚Ì‚ÅA‚Q‰ñ–ÚˆÈ~‚Ìì‹Æ‚ª•\Ž¦‚Å‚«‚é‚悤‚ÉAŽŸ‚Ì’iŠK‚Æ‚µ‚ÄDo`Loop‚ÆŒ¾‚¤‚Ì‚ðŒ¤‹†‚µ‚Ä‚Ý‚Ü‚·B
‚Ü‚½‚æ‚낵‚‚¨Šè‚¢‚¢‚½‚µ‚Ü‚·B
(yuri)
‚±‚ê‚ÍAƒ‹[ƒvˆ—iDo`Loop‚©AFor`Next‚ðŽg‚¤j‚ðŽg‚¦‚Ή”\‚Å‚·B
ˆê‰ñ–ڂ̃f[ƒ^‚©‚ç2‰ñ–ڂɈڂ邽‚ß‚É‚ÍH@–”A2‰ñ–ڂ̃f[ƒ^‚©‚ç3‰ñ–ڂ̃f[ƒ^‚Ɉڂé‚É‚ÍH ‚Ç‚¤‚·‚ê‚΂悢‚©‚ðl‚¦‚Ä‚‚¾‚³‚¢B‰½‚ç‚©‚Ì‹K‘¥‚ª‚ ‚é‚Í‚¸‚Å‚·B‚»‚ꂪ‚í‚©‚ê‚ÎA쬂·‚邱‚Æ‚ª‚Å‚«‚Ü‚·
ichinose‚³‚ñ‚ªƒqƒ“ƒg‚ð‰º‚³‚Á‚½h‰½‚ç‚©‚Ì‹K‘¥h‚ª•ª‚©‚è‚Ü‚¹‚ñ‚Å‚µ‚½B
› ‡”Ô‚ÉŽQÆ‚·‚é•û–@‚ÆFX‚ð’T‚µ‚Ä‚¢‚é‚ÆŒ©‚‚¯‚½‚Ì‚Å‚·‚ªA
Dim LoopArea As Range
Set LoopArea = Selection
For i = 1 To LoopArea.Count LoopArea.Cells(i).Value = i Next
@Œ‹‹ÇŽg‚¢•û‚ªHH@‚í‚©‚炸‚Å‚·B
@Ä“xA‚²‹³ŽöŠè‚¢‚Ü‚·B
(yuri)
>h‰½‚ç‚©‚Ì‹K‘¥h‚ª•ª‚©‚è‚Ü‚¹‚ñ‚Å‚µ‚½B
‹K‘¥«‚ÆŒ¾‚Á‚Ä‚à‚»‚Ì–w‚Ç‚ª“™·”—ñ‚Å‚·B‚»‚êˆÈŠO‚¾‚ÆŒ‹\“‚¢Žd—l‚¾‚ÆŽv‚¢‚Ü‚·B
—Ⴆ‚ÎA
1 4 7 10 13 16 19 22
‚Æ‚¢‚¤‚悤‚È”Žš‚ª‚ ‚éê‡A@1`4‚Ì·‚Í3A4`7‚Ì·‚ÍA3AEEEEE19`22‚Ì·‚ÍA3
—ׂǂ¤‚µ‚Ì”Žš‚Ì·‚ª3‚Æ‚¢‚¤‹K‘¥‚ªŒ©‚‚©‚è‚Ü‚·B
‚±‚Ì”Žš‚ð‡‚É•\Ž¦‚·‚éƒR[ƒh‚ÍAEEEB
•W€ƒ‚ƒWƒ…[ƒ‹‚É
Sub test() Dim Œö· As Long Dim ’l As Long Œö· = 3 ’l = 1@@'‰€Ý’è Do Until ’l > 22 MsgBox ’l ’l = ’l + Œö· Loop End Sub
‚±‚ñ‚ȃR[ƒh‚É‚È‚è‚Ü‚·B
–â‘è‚É–ß‚Á‚ÄEEEB
1‰ñ–Ú‚Ì—ñ”Ô†A2‰ñ–ÚA3‰ñ–Ú‚Æ‚Ý‚Ä‚‚¾‚³‚¢
1‰ñ–Ú
2—ñ–Ú@››•Ö‚Ì–¼Ì 3—ñ–Ú@“ž’…Žž 5—ñ–Ú@o”Žž
2‰ñ–Ú
E E
‚Æ‹Lq‚µ‚Ä‚¢‚¯‚ÎA—ñ”Ô†‚Ɉê’è‚Ì‹K‘¥‚ªŒ©‚¦‚Ä‚«‚Ü‚¹‚ñ‚©H
‚‚܂èA“¯‚¶ŠÔŠu‚Åݒ肳‚ê‚Ä‚¢‚é‚Í‚¸‚È‚ñ‚Å‚·‚ªEEEB
’²‚ׂĂ݂Ă‚¾‚³‚¢
ichinose
‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
¡Œ»Ý‚Ì\¬‚Å‚·‚Æ
A | B C D E | F @@@@@G E 9 @@@ | 1‰ñ–Úì‹Æ@@@@@@@@@@@ | 2‰ñ–Úì‹Æ 10 ƒOƒ‹[ƒv–¼ | “ž’…•Ö–¼ “ž’…Žž o”•Ö–¼ o”Žž |“ž’…•Ö–¼@“ž’…Žž 11 ‘æ1ƒOƒ‹[ƒv | AA•Ö 07:30 BB•Ö 09:00 | 12 | ì‹ÆŽžŠÔ 07:40 8:30 |
A | B C D E | F @@@@@G E 13 @@@ | 1‰ñ–Úì‹Æ@@@@@@@@@@@| 2‰ñ–Úì‹Æ 14 ƒOƒ‹[ƒv–¼ | “ž’…•Ö–¼ “ž’…Žž o”•Ö–¼ o”Žž |“ž’…•Ö–¼@“ž’…Žž 15 ‘æ2ƒOƒ‹[ƒv | CC•Ö 06:30 DD•Ö 08:00 | 16 | ì‹ÆŽžŠÔ 06:40 7:30 |
‚ÆŒ¾‚¤‚Ó‚É‚È‚è‚Ü‚µ‚ÄA—ñ”Ô†‚̈ê’è‚Ì‹K‘¥‚Í‚Q—ñ–ÚA‚R—ñ–ÚA‚S—ñ–ÚA‚T—ñ–Ú‚Ü‚Å‚Ì‚S—ñ‚ňê‚‚Ìì‹Æ‚É‚È‚Á‚Ä‚¢‚Ü‚·B‚Q‰ñ–ÚˆÈ~‚à“¯‚¶ŠÔŠu‚É‚È‚Á‚Ä‚¢‚Ü‚·B
‚»‚ê‚ÅAŒö·=3 ‚Æ‚©’l=1‚ªŠÖŒW‚·‚é‚ñ‚Å‚·‚ËH@’l‚̓Xƒ^[ƒgˆÊ’u‚ÅŒö·‚Í‚R‚”ò‚΂µ‚Æ‚©‚S‚”ò‚΂µ‚Æ‚©EEEHH
ˆá‚Á‚Ä‚¢‚½‚ç‚·‚Ý‚Ü‚¹‚ñ‚Å‚·B
(yuri)
@
>‚»‚ê‚ÅAŒö·=3 ‚Æ‚©’l=1‚ªŠÖŒW‚·‚é‚ñ‚Å‚·‚ËH@’l‚̓Xƒ^[ƒgˆÊ’u‚ÅŒö·‚Í‚R‚”ò‚΂µ‚Æ‚©‚S‚”ò‚΂µ‚Æ‚©EEEHH
‚»‚¤‚¢‚¤‚±‚Æ‚Å‚·II
“ž’…•Ö–¼‚É’…–Ú‚·‚ê‚ÎAˆê‰ñ–Ú‚Ì“ž’…•Ö–¼‚Ì‚ ‚é—ñ”Ô†‚ÍA‚a‚Å‚·‚©‚çA2‚Å‚·‚æ‚ËII
2‰ñ–Ú‚Ì“ž’…•Ö–¼‚Ì‚ ‚é—ñ”Ô†‚ÍA‚e‚Å‚·‚©‚çA6‚Å‚·‚æ‚ËII
3‰ñ–Ú‚Ì‚»‚ê‚ÍAJ‚Å‚·‚©‚ç@10‚Å‚·‚ËII
‚»‚¤‚·‚é‚Æ@ʼn‚Ì’l‚ª2‚ňÈ~A‚S‚¸‚‘‰Á‚·‚éB
‚±‚ê‚Å2‰ñ–ÚA3‰ñ–Ú‚Ì“ž’…•Ö–¼‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚ÍA‘O‰ñƒR[ƒh‚Ì—á‚̂悤‚ÈŒJ‚è•Ô‚µˆ—‚ð s‚¦‚Ύ擾‚Å‚«‚Ü‚·‚æ‚ËII
’l‚É“ž’…•Ö–¼‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚ªŽæ“¾‚Å‚«‚ê‚ÎA
“ž’…Žž‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚Í@’l+1 o”•Ö–¼‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚Í@’l+2 o”Žž‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚Í@’l+3 ì‹ÆŽžŠÔŠJŽnŽž‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚Í@’l+2is”Ô†‚àˆê‚‰ºj ì‹ÆŽžŠÔI—¹Žž‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚Í@’l+3is”Ô†‚àˆê‚‰ºj
ì}‚ð‚·‚é‚Ì‚É•K—v‚ȃf[ƒ^‚ªŽæ“¾‚Å‚«‚Ü‚·B Œã‚ÍA‚±‚̃‹[ƒv“à‚Éì}‚ðs‚¤ƒR[ƒh‚ð‹Lq‚·‚é
‚Æ‚¢‚¤Žè‡‚Å‚·B
ŒJ‚è•Ô‚µ‚͈—A‹K‘¥«‚𓱂«o‚µA‚»‚ê‚ðƒR[ƒh‰»‚·‚éB
ƒvƒƒOƒ‰ƒ€‚Ì—v‚Å‚·B‚±‚ꂪo—ˆ‚È‚¢‚ƃvƒƒOƒ‰ƒ€‚ÍA‚‚‚ê‚È‚¢@‚ÆŒ¾‚Á‚Ä‚à‚æ‚¢‚ÆŽv‚¢‚Ü‚·B
‚à‚¤‚¿‚å‚Á‚Æ‚Å‚·‚æII
ichinose
’l‚É“ž’…•Ö–¼‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚ªŽæ“¾‚Å‚«‚ê‚Î
‚̎擾‚Å‚·‚ªA worksheets("sheet1").cells(11,2).value‚Å“ž’…•Ö–¼‚ªŽæ“¾‚Å‚«‚Ü‚·‚©H FX‚ÆŽŽ‚µ‚Ä‚Í‚¢‚é‚à‚Ì‚ÌAƒGƒ‰[‚΂©‚è‚ÅEEE
ƒXƒ€[ƒY‚Éæ‚És‚¯‚È‚‚Ä\‚µ–ó‚ ‚è‚Ü‚¹‚ñB
(Yuri)
>worksheets("sheet1").cells(11,2).value‚Å“ž’…•Ö–¼‚ªŽæ“¾‚Å‚«‚Ü‚·‚©H ‚±‚̪’l‚Å“ž’…•Ö–¼‚ªŽæ“¾‚Å‚«‚Ä‚¢‚é‚©‚çAYuri‚³‚ñ‚ª“Še‚³‚ꂽƒR[ƒh
>.TextFrame.Characters.Text = sht.Cells(idx, 2).Value & " “ž’… : " & Format(sht.Cells(idx, 3).Value, "hh:mm") & "^" & sht.Cells(idx, 4).Value & " o” : " & Format(sht.Cells(idx, 5).Value, "hh:mm")
‚ÅŽlŠpŒ`“à‚ɃeƒLƒXƒg‚ª³‚µ‚•\Ž¦‚³‚ê‚Ä‚¢‚é‚Ì‚Å‚·‚æ‚ËII ƒR[ƒh“à‚Ì ¨@ sht.Cells(idx, 2).Value@@‚ª‚»‚¤‚Å‚·‚ËII(sht‚ÍWorksheets("Sheet1")‚ðŽw‚µ‚Ä‚¢‚Ü‚·j
idx=11 ‚ƉŠúݒ肳‚ê‚Ä‚¢‚Ü‚·B‚‚܂èAƒ‹[ƒvˆ—‚Ìʼn‚Å
11s–Ú‚Ì‚a—ñ‚Ì“ž’…•Ö–¼‚ðŽæ“¾‚Å‚«‚Ä‚¢‚Ü‚·B
idx‚Æ‚¢‚¤•Ï”‚ðƒ‹[ƒv“à‚ŕω»‚³‚¹‚邱‚Æ‚É‚æ‚Á‚ÄA11s–ÚA13s–ÚA15s–ڂ̃f[ƒ^‚ªŽæ‚èo‚¹‚Ü‚·B
sht.Cells(idx, 2).Value —ñ•ûŒü‚Ì’l‚ÍA2‚ƒ蔂ð“ü‚ê‚Ä‚¢‚é‚Ì‚Å‚±‚Ì‚Ü‚Ü‚Å‚ÍA 2@‚‚܂èA‚a—ñ‚Ì’l‚¾‚¯‚µ‚©Žæ“¾‚Å‚«‚Ü‚¹‚ñ‚æ‚ËII
‚±‚±‚às‚Æ“¯‚¶‚•Ï”‚ðŽg‚¤‚Ì‚Å‚·B‰¼‚É‚»‚Ì•Ï”‚ð@JDX@‚Æ‚µ‚Ü‚· ‚»‚ÌJDX‚ðŽg‚Á‚Äis‚ÌŒJ‚è•Ô‚µˆ—‚Ì’†‚É‚à‚¤ˆê‚‚̌J‚è•Ô‚µˆ—jAƒ‹[ƒvˆ—‚ð\’z‚µ‚Ü‚·B ƒ‹[ƒv‚ÌŠO‚ÅJDX‚ɉŠú’l‚ð—^‚¦A‚»‚̃‹[ƒv“à‚Å‚ÍAŒ©o‚µ‚½‹K‘¥«‚ÅJDX‚ð•Ï‰»‚³‚¹‚Ü‚·B
JDX‚̕ω»‚Åì}‚É•K—v‚È@1‰ñ–ڂ̃f[ƒ^A2‰ñ–ڂ̃f[ƒ^AEEE‚ªŽæ“¾‚Å‚«‚é‚Í‚¸‚Å‚·B
>FX‚ÆŽŽ‚µ‚Ä‚Í‚¢‚é‚à‚Ì‚ÌAƒGƒ‰[‚΂©‚è‚ÅEEE
Ä“xAl‚¦‚Ä‚Ý‚Ä‚‚¾‚³‚¢B‚»‚Ìã‚Åì“®‚µ‚È‚¢‚ç‚ÎA‚»‚Ìì“®‚µ‚È‚¢ƒR[ƒh‚ð’ñŽ¦‚µ‚Ä‚‚¾‚³‚¢
‚»‚¤‚µ‚È‚¢‚ÆA‚Ç‚±‚ª—‰ð‚µ‚Ä’¸‚¢‚Ä‚¢‚È‚¢‚Ì‚©‚ª‚í‚©‚è‚Ü‚¹‚ñ‚Ì‚ÅEEEB
“ú’†‚ÍA•ÔM‚Í‚Å‚«‚Ü‚¹‚ñ‚ªEEEB
ichinose
>‚±‚ñ‚ÈŠ´‚¶‚Åmain‚̃R[ƒh‚ð•ÏX‚µ‚Ä‚Ý‚Ü‚µ‚½B ‚Ƌ‚Á‚Ä“Še‚³‚ꂽƒR[ƒh‚ðˆÈ‰º‚̂悤‚É•ÏX‚µ‚Ü‚µ‚½B
'=============================================================================================================== Sub main2() Const ss = 2 Dim hh As Single Dim idx As Long Dim rw As Long Dim row1 As Long Dim sumwidth As Single Dim sht As Worksheet Set sht = Worksheets("sheet1") With sht row1 = .Cells(.Rows.Count, "a").End(xlUp).Row End With With Worksheets("sheet2") On Error Resume Next .DrawingObjects.Delete On Error GoTo 0 For idx = 1 To ss - 1 sumwidth = sumwidth + .Columns(idx).ColumnWidth
Next hh = .Range("b:b").ColumnWidth End With Call open_scale(ss, sumwidth, hh, Worksheets("sheet2")) idx = 11 rw = 3 Do Until idx > row1 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, 2).Value, sht.Cells(idx, 3).Value, sht.Cells(idx, 4).Value, _ sht.Cells(idx, 5).Value, sht.Cells(idx + 1, 4).Value, sht.Cells(idx + 1, 5).Value, hh) idx = idx + 2 rw = rw + 1 Loop
End Sub '=============================================================================================================== Sub ì}(ByVal ƒV[ƒg As Worksheet, ByVal ì¬s As Long, “ž’…•Ö As String, ByVal “ž’…Žž As Date _ , ByVal o”•Ö As String, ByVal o”Žž As Date _ , ByVal ì‹ÆŠJŽnŽž As Date, ByVal ì‹ÆI—¹Žž As Date, hh As Single) Dim st As Single Dim wk As Double Dim shp As Shape wk = (o”Žž - “ž’…Žž) / TimeSerial(0, 10, 0) st = (“ž’…Žž - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_shape(ƒV[ƒg.Rows(ì¬s), st, wk * hh, msoShapeRectangle, ƒV[ƒg) With shp .TextFrame.Characters.Text = “ž’…•Ö & " “ž’… : " & Format(“ž’…Žž, "hh:mm") & "^" & o”•Ö & " o” : " & Format(o”Žž, "hh:mm") .TextFrame.HorizontalAlignment = xlHAlignCenter .Fill.ForeColor.RGB = vbRed .Fill.Transparency = 0.5 End With wk = (ì‹ÆI—¹Žž - ì‹ÆŠJŽnŽž) / TimeSerial(0, 10, 0) st = (ì‹ÆŠJŽnŽž - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_line(ƒV[ƒg.Rows(ì¬s), st, wk * hh, 80, ƒV[ƒg) With shp .Line.ForeColor.RGB = vbBlack .Line.Transparency = 0 .Line.EndArrowheadLength = msoArrowheadLengthMedium .Line.EndArrowheadWidth = msoArrowheadWidthMedium .Line.EndArrowheadStyle = msoArrowheadTriangle .Line.BeginArrowheadStyle = msoArrowheadTriangle End With End Sub
‚±‚ê‚Ń‹[ƒv“à‚Å‚ÍAì}‚É•K—v‚ȃf[ƒ^‚ðuì}v‚Æ‚¢‚¤ƒvƒƒVƒWƒƒ[‚É‘—‚ê‚ÎA ƒ`ƒƒ[ƒg‚ð쬂·‚é‚悤‚É•ÏX‚µ‚Ü‚µ‚½B (•W€ƒ‚ƒWƒ…[ƒ‹iModule2j‚ɃKƒ“ƒgƒ`ƒƒ[ƒg쬃vƒƒOƒ‰ƒ€ƒpƒbƒN‚ÍA•K—v‚Å‚·jB
ì}‚É•K—v‚ȃf[ƒ^‚ÍA
ƒKƒ“ƒ`ƒƒ[ƒg쬂·‚éƒV[ƒgƒIƒuƒWƒFƒNƒg ƒKƒ“ƒ`ƒƒ[ƒg쬂·‚és “ž’…•Ö “ž’…Žž o”•Ö o”Žž ì‹ÆŠJŽnŽž ì‹ÆI—¹Žž hh(10•ª‚ð•\‚·ƒZƒ‹•j
‚Å‚·B
‚±‚Ì“àA쬂·‚éƒV[ƒgƒIƒuƒWƒFƒNƒg‚Æhh‚ÍA—á‘è‚̂悤‚Éworksheets("sheet2")@‹y‚ÑAhh ‚ŌŒè‚Å‚æ‚¢‚Å‚·B Žc‚è‚̃f[ƒ^‚ðs‚Ɖ½‰ñ–Ú‚Ìì}‚©‚Ńf[ƒ^‚ð•Ï‚¦‚È‚¯‚ê‚΂Ȃè‚Ü‚¹‚ñi‚±‚±‚ª–â‘è‚̃‹[ƒvˆ—‚Å‚·jB
’ñŽ¦‚µ‚½ƒR[ƒh‚ÍAƒOƒ‹[ƒv–ˆ‚É‚ÍAì}‚³‚ê‚Ä‚¢‚Ü‚·‚æ‚ËII ‚±‚ê‚ÍA
idx = 11 rw = 3 Do Until idx > row1 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, 2).Value, sht.Cells(idx, 3).Value, sht.Cells(idx, 4).Value, _ sht.Cells(idx, 5).Value, sht.Cells(idx + 1, 4).Value, sht.Cells(idx + 1, 5).Value, hh) idx = idx + 2 rw = rw + 1 Loop
ã‹L‚ÌDo Until@`@Loop
‚ÌŒJ‚è•Ô‚µˆ—‚ÅŠeƒOƒ‹[ƒv‚̈ê‰ñ–ڂ̃f[ƒ^‚ðƒvƒƒVƒWƒƒ[uì}v‚É“n‚µ‚Ä‚¢‚é‚©‚ç‚Å‚·B
‚Å‚ÍAˆê‰ñ–ÚˆÈ~A2‰ñ–ÚA3‰ñ–ÚEEE5‰ñ–Ú‚Ü‚Å‚àì}‚·‚é‚É‚ÍA
ã‹L‚ÌDo Until@`@Loop“à‚ÅX‚Ɉê‰ñ–ÚˆÈ~A2‰ñ–ÚA3‰ñ–ÚEEE5‰ñ–ڂ܂ł̃‹[ƒvˆ—‚ð ƒR[ƒh‰»‚µ‚È‚¯‚ê‚΂Ȃè‚Ü‚¹‚ñB‚»‚Ìê‡Aˆê—á‚Å‚·‚ªAsht.Cells(idx, 2).Value‚Ì2‚Ì”’l‚à •Ï”‰»‚·‚é•K—v‚ª‚ ‚è‚Ü‚·B
V‹KƒuƒbƒN‚Ì•W€ƒ‚ƒWƒ…[ƒ‹iModule1j‚Ɉȉº‚̃R[ƒh‹Lq‚µAŽÀs‚µ‚Ä‚Ý‚Ä‚‚¾‚³‚¢
Sub ŽQlƒR[ƒh() Dim rw As Long Dim col As Long rw = 1 col = 1 Do Until rw > 4 col = 1 Do Until col > 4 Cells(rw, col).Select Application.Wait Now() + TimeValue("00:00:01") DoEvents col = col + 1 Loop rw = rw + 1 Loop End Sub
ã‹L‚̃R[ƒhuŽQlƒR[ƒhv ƒZƒ‹A1‚©‚çAB1,C1,D1‚ƈڂÁ‚Ä‚¢‚«AA2,B2,C2,D2‚ƈڂèAA3,B3,C3,D3,A4,B4,C4,D4‚ƈڂè I—¹‚·‚é‚Æ‚¢‚¤‚»‚ꂾ‚¯‚ÌŽd—l‚̃R[ƒh‚Å‚·B
‚±‚ê‚ç‚ðŽQl‚ÉŠ®¬‚³‚¹‚Ä‚‚¾‚³‚¢
ichinose
‚¨¢˜b‚É‚È‚Á‚Ä‚¢‚Ü‚·B
FX‚Æׂ©‚‚²‹³Ž¦‚µ‚Ä‚¢‚½‚¾‚«A‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
‚Å‚ÍAˆê‰ñ–ÚˆÈ~A2‰ñ–ÚA3‰ñ–ÚEEE5‰ñ–Ú‚Ü‚Å‚àì}‚·‚é‚É‚ÍA
ã‹L‚ÌDo Until@`@Loop“à‚ÅX‚Ɉê‰ñ–ÚˆÈ~A2‰ñ–ÚA3‰ñ–ÚEEE5‰ñ–ڂ܂ł̃‹[ƒvˆ—‚ðƒR[ƒh‰»‚µ‚È‚¯‚ê‚΂Ȃè‚Ü‚¹‚ñB‚»‚Ìê‡Aˆê—á‚Å‚·‚ªAsht.Cells(idx, 2).Value‚Ì2‚Ì”’l‚à•Ï”‰»‚·‚é•K—v‚ª‚ ‚è‚Ü‚·B
‚»‚ê‚ÅAŽ„‚ªŽv‚¢‚‚¢‚½‚Ì‚ªA‰º‹L‚̃R[ƒh‚Å‚·B
idx = 11 rw = 3 Do Until idx > row1 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, 2).Value, sht.Cells(idx, 3).Value, sht.Cells(idx, 4).Value, _ sht.Cells(idx, 5).Value, sht.Cells(idx + 1, 4).Value, sht.Cells(idx + 1, 5).Value, hh)
Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, 6).Value, sht.Cells(idx, 7).Value, sht.Cells(idx, 8).Value, _ sht.Cells(idx, 9).Value, sht.Cells(idx + 1, 8).Value, sht.Cells(idx + 1, 9).Value, hh)
Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, 10).Value, sht.Cells(idx, 11).Value, sht.Cells(idx, 12).Value, _ sht.Cells(idx, 13).Value, sht.Cells(idx + 1, 12).Value, sht.Cells(idx + 1, 13).Value, hh)
Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, 14).Value, sht.Cells(idx, 15).Value, sht.Cells(idx, 16).Value, _ sht.Cells(idx, 17).Value, sht.Cells(idx + 1, 16).Value, sht.Cells(idx + 1, 17).Value, hh)
Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, 18).Value, sht.Cells(idx, 19).Value, sht.Cells(idx, 20).Value, _ sht.Cells(idx, 21).Value, sht.Cells(idx + 1, 20).Value, sht.Cells(idx + 1, 21).Value, hh)
idx = idx + 2 rw = rw + 1 Loop End Sub
ŽQlƒR[ƒh‚ÍŽÀs‚µ‚Ä‚Ý‚Ü‚µ‚½B
ŽQlƒR[ƒh‚Ìrw = 1‚ð11‚ÉAcol=1‚ð2‚É•ÏX‚µAFX‚ÆŽŽsöŒë‚µ‚Ä‚¢‚Ü‚·B
‚Ü‚¾ŽžŠÔ‚ª‚©‚©‚è‚»‚¤‚Å‚·B
(yuri)
>‚»‚ê‚ÅAŽ„‚ªŽv‚¢‚‚¢‚½‚Ì‚ªA‰º‹L‚̃R[ƒh‚Å‚·B
‚È‚é‚Ù‚ÇA‚±‚ꂾ‚Æ@ì“®‚µ‚Ü‚·‚æ‚ËII
‚Å‚àAEEEEB
ÅI“I‚É‚ÍA10‰ñ–Ú‚Ü‚Å‚ ‚é‚ñ‚Å‚·‚æ‚ËII ‚»‚ÌŽž‚ÍA“¯‚¶‚悤‚ȃR[ƒhiCall ì}iEEEjj‚ð10‰ñ‹Lq‚µ‚Ü‚·‚©H
ì}‚É•K—v‚ȃf[ƒ^‚ª4—ñ‚¨‚«‚É”z’u‚³‚ê‚Ä‚¢‚é‚Æ‚í‚©‚Á‚Ä‚¢‚é‚Ì‚ÉH
ˆö‚Ý‚É‘O‰ñ“Še‚Ì@ŽQlƒR[ƒh
Sub ŽQlƒR[ƒh() Dim rw As Long Dim col As Long rw = 1 col = 1 '©‚±‚êíœ Do Until rw > 4 col = 1 Do Until col > 4 Cells(rw, col).Select Application.Wait Now() + TimeValue("00:00:01") DoEvents col = col + 1 Loop rw = rw + 1 Loop End Sub
ã‹L‚̂悤‚É’ù³‚µ‚Ä‚‚¾‚³‚¢B“®ì‚É‚ÍA‰e‹¿‚ ‚è‚Ü‚¹‚ñ‚ªAƒR[ƒhƒ~ƒX‚Å‚·B
ŽQlƒR[ƒh‚ð‚æ‚‚Ý‚Ä‚‚¾‚³‚¢A
>col=1‚ð2‚É•ÏX‚µAFX‚ÆŽŽsöŒë‚µ‚Ä‚¢‚Ü‚·B
‚±‚ê‚àŽŽ‚·‚É‚ÍA‚æ‚¢‚Å‚·‚Ë
‚ ‚Æ‚ÍA
col = col + 1
‚±‚ê‚Å‚·B
—ñ‚ðˆê—ñ‚•ω»‚³‚¹‚é‚©‚çA
col = col + 1
‚Æ‚·‚é‚Ì‚Å‚·‚æ‚ËH
ˆê‚‚¨‚«‚¾‚Á‚½‚çA col = col + 2
‚Æ‚µ‚Ü‚·B
‚Å‚ÍA‚S—ñ‚²‚Æ‚¾‚Á‚½‚çAHHH
‹K‘¥«‚ÍA‚±‚±‚ÅŠˆ‚©‚·‚Ì‚Å‚·‚æII
–”A
col=2 E E msgbox sht.Cells(idx, col).Value
‚Æ‚·‚ê‚ÎA‚Q—ñ–Ú‚Ì“ž’…•Ö‚ªŽæ“¾‚Å‚«‚Ü‚·B ‚Å‚ÍAB“¯‚¶•Ï”col‚ðŽg‚Á‚ÄA“ž’…Žž‚âo”•ÖAo”Žž“™ ì}‚É•K—v‚ȃf[ƒ^‚ÍA•Ï”col‚ðŽg‚Á‚Ď擾‚·‚é‚É‚ÍA ‚ǂ̂悤‚É‚·‚ê‚΂悢‚©H
> ’l‚É“ž’…•Ö–¼‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚ªŽæ“¾‚Å‚«‚ê‚ÎA >“ž’…Žž‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚Í@’l+1 >o”•Ö–¼‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚Í@’l+2 >o”Žž‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚Í@’l+3 >ì‹ÆŽžŠÔŠJŽnŽž‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚Í@’l+2is”Ô†‚àˆê‚‰ºj >ì‹ÆŽžŠÔI—¹Žž‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚Í@’l+3is”Ô†‚àˆê‚‰ºj
‚±‚ê‚ðŽv‚¢o‚µ‚Ä‚Ý‚Ä‚‚¾‚³‚¢
ichinose
‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
ŽŽ‚µ‚Ä‚Ý‚Ü‚µ‚½B
col=col+4‚Å“ž’…•Ö‚ð‚P‚O‰ñ‘S‚Ď擾‚µ‚Ä‚¢‚Ü‚·B
‚ ‚Æ–â‘è‚ÍA“ž’…Žž‚Æ‚©‚Ì@’l+1‚Æ‚©‚Ì•”•ª‚Å‚·B
‚»‚Ì‚Ü‚Ü’l+1‚Æ‚©’l+2‚Æ‚©‚É‚µ‚Ä‚àA‚¤‚Ü‚“®‚©‚È‚¢‚Å‚·‚æ‚ËHH
Sub ŽQlƒR[ƒh()
Dim rw As Long Dim col As Long rw = 11 col = 1 Do Until rw > 14 col = 2 Do Until col > 40 col = 2 + 1 col = 2 + 2
Cells(rw, col).Select Application.Wait Now() + TimeValue("00:00:01") DoEvents col = col + 4
Loop rw = rw + 1 Loop End Sub
‚±‚̂悤‚É•Ï‚¦‚Ä‚àA–³—‚Å‚µ‚½B
•Ï”col‚ðŽg‚Á‚Ăǂ̂悤‚Ɏ擾‚·‚ê‚ÎHH
‚ª‚Ç‚¤‚µ‚Ä‚à‚í‚©‚è‚Ü‚¹‚ñB
ׂ©‚‹³‚¦‚ĉº‚³‚Á‚Ä‚¢‚é‚Ì‚ÉA\‚µ–ó‚ ‚è‚Ü‚¹‚ñB
(yuri)
Ÿ‚±‚̂悤‚É•Ï‚¦‚Ä‚àA–³—‚Å‚µ‚½B
Ÿ•Ï”col‚ðŽg‚Á‚Ăǂ̂悤‚Ɏ擾‚·‚ê‚ÎHH
‚¨‚»‚ç‚‚¢‚Ü‚ÍAu‚±‚¤‚µ‚½‚ç‚Å‚«‚é‚©‚ÈHvu‚ ‚ ‚µ‚½‚ç‚Å‚«‚é‚©‚ÈHv‚Æ
l‚¦‚Ä‚¨‚ç‚ê‚é‚ÆŽv‚¢‚Ü‚·B‚à‚¿‚ë‚ñ‚»‚ê‚à‘åØ‚Å‚·‚ªA‚»‚ê‚ɉÁ‚¦‚Ä
u‚±‚ꂪ‚Å‚«‚½‚Æ‚µ‚Ä ‚»‚Ì‚Æ‚«col‚Í‚Ç‚¤‚È‚Á‚Ä‚¢‚éƒnƒY‚©Hv‚Æ‹tŽZ‚µ‚Ä
l‚¦‚Ä‚Ý‚Ä‚‚¾‚³‚¢Bi–À˜H‚ðƒS[ƒ‹‚©‚ç‰ð‚‚悤‚È‚à‚Ì‚ÅA‚¤‚Ü‚‚¢‚‚±‚Æ
Œ‹\‚ ‚è‚Ü‚·Bj
|‰À|@¦ƒjƒbƒNƒl[ƒ€—“‚ªŒ©“–‚½‚è‚Ü‚¹‚ñ
ij
yuri‚³‚ñ‚ÍA‹“G‚Å‚·‚Ë‚¦II
Do Until col > 40 col = 2 + 1 col = 2 + 2 'ª‚±‚±‚ÅA•Ï”col‚É2+2A‚‚܂è4‚ðDo`Loop“à‚Åݒ肵‚Ä‚µ‚Ü‚Á‚Ä‚¢‚Ü‚·
Cells(rw, col).Select Application.Wait Now() + TimeValue("00:00:01") DoEvents col = col + 4 'col‚ÍA4‚ªÝ’肳‚ê‚Ä‚¢‚é‚Ì‚Å‚·‚©‚çA‚»‚ê‚Écol+4‚Åcol‚ÍA8‚Å‚·B 'Do`loop“à‚Å‚ÍAcol‚Éʼn‚É4‚ªÝ’肳‚êA‚»‚ê‚É+4‚³‚ê‚Ä8‚É‚È‚è‚Ü‚· '‚¢‚‚܂ł½‚Á‚Ä‚àcol‚ÍA40‚È‚ñ‚ĉz‚¦‚Ü‚¹‚ñB’´‚¦‚È‚¢‚Ì‚Å‚¢‚‚܂łà 'ƒ‹[ƒv‚µ‚Á‚Ï‚È‚µ‚Å‚·B@@@@@@
Loop
Do`Loop‚Í@col‚ð•Ï‰»‚³‚¹‚È‚ª‚çi4‚ð‡X‚É‚½‚µ‚Ä‚¢‚jˆ—‚ð‚µAðŒ‚É‚æ‚èAƒ‹[ƒv‚©‚甲‚¯‚éi‚±‚±‚Å‚ÍAcol‚ª40‚ð’´‚¦‚½‚烋[ƒv‚𔲂¯‚éj
‚Æ‚¢‚¤ƒXƒe[ƒgƒƒ“ƒg‚Å‚·B
‚Å‚·‚©‚çAƒ‹[ƒv“à‚Å–ˆ‰ñŒˆ‚Ü‚Á‚½”Žšicol = 2 + 2j‚ðݒ肵‚Ä‚µ‚Ü‚Á‚Ä‚ÍAƒ‹[ƒvˆ—‚ª¬—§‚µ‚Ü‚¹‚ñB
col = 1 Do Until col > 4 Cells(rw, col).Select Application.Wait Now() + TimeValue("00:00:01") DoEvents col = col + 1 Loop
’ñŽ¦‚µ‚½ŽQlƒR[ƒh‚Å‚ÍADo`Loop“à‚Åcol‚É’¼Ú’è”i”Žšj‚ðݒ肵‚Ä‚¢‚Ü‚¹‚ñ‚æ‚Ë
>•Ï”col‚ðŽg‚Á‚Ăǂ̂悤‚Ɏ擾‚·‚ê‚ÎHH
ˆÈ‰º‚̃R[ƒh‚¾‚Ƴ‚µ‚“ž’…•Ö‚ð‘I‘ð‚µ‚Ü‚·‚æ‚ËH
Sub ŽQlƒR[ƒh() Dim rw As Long Dim col As Long rw = 11 col = 1 Do Until rw > 14 col = 2 Do Until col > 40 Cells(rw, col).Select Application.Wait Now() + TimeValue("00:00:01") DoEvents col = col + 4 Loop rw = rw + 1 Loop End Sub
ʼn‚É@ Cells(rw, col).Select@‚ð‘I‘ð‚µ‚½ŽžAB11‚ð‘I‘ð‚µ‚Ä‚¢‚é‚Ì‚Å‚·‚©‚çA•Ï”col‚É‚ÍA2‚ª ݒ肳‚ê‚Ä‚¢‚Ü‚·B
2“x–Ú‚ÍAF11‚ª‘I‘ð‚³‚ê‚Ü‚·B‚±‚ÌŽž‚Ì•Ï”col‚ÍA6‚ªÝ’肳‚ê‚Ä‚¢‚Ü‚·B
B11‚ª‘I‘ð‚³‚ê‚Ä‚¢‚鎞A‚‚܂èAcol=2‚Ì‚Æ‚«A“ž’…Žž‚̃Zƒ‹C11‚ðŽæ“¾‚·‚邽‚ß‚É‚ÍH
col‚ª3‚É‚È‚ê‚΂悢‚Å‚·‚æ‚ËH‚Å‚àAcol=3‚Æ”Žš‚ðƒ‹[ƒv“à‚Åcol‚ÉÝ’è‚Å‚«‚È‚¢EEEB
‚È‚ç‚ÎA Cells(rw, col+1).Value ‚Æ‚·‚ê‚ÎAcol‚Ì’l‚à•Ï‚í‚è‚Ü‚¹‚ñ‚µAcol‚Æ‚¢‚¤•Ï”‚ðŽg‚Á‚Ä “ž’…Žž‚Ì‚ ‚éƒZƒ‹‚Ì—ñ”Ô†‚ð•\Œ»‚Å‚«‚Ü‚·B
Sub ŽQlƒR[ƒh() Dim rw As Long Dim col As Long rw = 11 col = 1 Do Until rw > 14 col = 2 Do Until col > 40 Range(Cells(rw, col), Cells(rw, col + 1)).Select Application.Wait Now() + TimeValue("00:00:01") DoEvents col = col + 4 Loop rw = rw + 1 Loop End Sub
‚±‚̂悤‚É‚·‚é‚ÆA¡“x‚ÍA“ž’…•Ö‚Æ“ž’…Žž‚ð‡ŽŸ‘I‘ð‚µ‚Ä‚¢‚«‚Ü‚·B
‘¼‚̃Zƒ‹‚à“¯‚¶—v—Ì‚ÅŽŽ‚µ‚Ä‚Ý‚ÄA‚¤‚Ü‚‚¢‚‚悤‚È‚çA–{‘è‚É–ß‚Á‚Ä‚Ý‚Ä‚‚¾‚³‚¢B
(ichinose)
ŠF—lFX‚Æ‚²‹³Ž¦‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
ichinose‚³‚ñA‚ ‚Ì‚£`‹“G‚Èyuri‚Å‚·B
‚¢‚Â‚à’š”J‚É‚²‹³Žö‚µ‚Á‚Ä’¸‚¢‚Ä‚¢‚é‚Ì‚ÉA—‰ð•s‘«‚Å‚²–À˜f‚ð‚¨Š|‚¯‚µ‚Ä‚¢‚Ü‚·B
¡“x‚Í‚Ç‚¤‚Å‚µ‚傤B‚±‚ñ‚ÈŠ´‚¶‚Å‚â‚Á‚Ä‚Ý‚Ü‚µ‚½B
@ Do Until idx > row1
Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, 2).Value, sht.Cells(idx, 3).Value, sht.Cells(idx, 4).Value, _ sht.Cells(idx, 5).Value, sht.Cells(idx + 1, 4).Value, sht.Cells(idx + 1, 5).Value, hh) idx = idx + 2 rw = rw + 1 Loop
rw = 11 col = 1 Do Until rw > 20 col = 2 Do Until col > 41 Range(Cells(rw, col), Cells(rw, col + 1)).Select Range(Cells(rw, col), Cells(rw, col + 2)).Select Range(Cells(rw, col), Cells(rw, col + 3)).Select Range(Cells(rw, col), Cells(rw + 1, col + 2)).Select Range(Cells(rw, col), Cells(rw + 1, col + 3)).Select
Application.Wait Now() + TimeValue("00:00:01") DoEvents col = col + 4 Loop rw = rw + 2 Loop End Sub
‚æ‚낵‚‚¨Šè‚¢‚µ‚Ü‚·B
(yuri)
yuri‚³‚ñ‚ª’ñŽ¦‚³‚ꂽƒR[ƒhEEEA Cells(rw, col)‚Æ‚¢‚¤Žw’è‚Å@“ž’…•Ö‚ðŽ¦‚·ƒZƒ‹‚ªŽæ“¾‚Å‚«‚½‚Æ‚«A
Cells(rw, col + 1)‚ª@“ž’…Žž Cells(rw, col + 2)‚ªAo”•Ö Cells(rw, col + 3)‚ªAo”Žž Cells(rw + 1, col + 2)‚ªAì‹ÆŠJŽnŽž Cells(rw + 1, col + 3)‚ªAì‹ÆI—¹Žž
‚»‚ꂼ‚ê‚̃Zƒ‹‚ð•\‚µ‚Ä‚¢‚邱‚Æ‚ª—‰ð‚Å‚«‚Ü‚µ‚½‚©H
ŽQlƒR[ƒh‚Å‚ÍAƒZƒ‹‚ð‘I‘ð‚µ‚½‚¾‚¯‚Å‚·‚ªA
Œ³‚̃R[ƒh‚É–ß‚Á‚ÄA1‰ñ–Ú‚Ìì}‚Ü‚Å‚Ío—ˆ‚Ä‚¢‚é‚Ì‚Å‚·‚©‚çA 2‰ñ–ÚA3‰ñ–ÚAEEE5‰ñ–Ú‚Ìì}‚ª‚Å‚«‚é‚悤‚ɃR[ƒh‚ð쬂µ‚Ä‚‚¾‚³‚¢B
iichinose j 2013/10/18(‹à) 18:36
‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
>‚»‚ꂼ‚ê‚̃Zƒ‹‚ð•\‚µ‚Ä‚¢‚邱‚Æ‚ª—‰ð‚Å‚«‚Ü‚µ‚½‚©H ‚Í‚¢A‚悤‚â‚—‰ð‚Å‚«‚Ü‚µ‚½B
‚±‚ê‚©‚çA‚P‰ñ–Ú‚Ìì}‚ðŽQl‚ÉA‚Q‰ñ–ÚˆÈ~‚Ìì}ƒR[ƒh‚ðl‚¦‚Ä‚Ý‚Ü‚·B
(yuri)
–{“–‚ÉA¡‚Ü‚Å‚²‹³Žö‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
‚à‚¤ˆê“xAʼn‚©‚炲‹³Ž¦‰º‚³‚Á‚½Ž–‚ð®—‚µA•Ï”‚ɂ‚¢‚Ä’²‚×A‚悤‚â‚A‚±‚±‚Ü‚Å‚Å‚«‚Ü‚µ‚½B
‚Å‚«‚½‚Ì‚ªA‰º‹LƒR[ƒh‚Å‚·B
idx = 11 col = 2 rw = 3 Do Until idx > row1 col = 2 Do Until col > 41 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh) col = col + 4 Loop idx = idx + 2 rw = rw + 1 Loop
End Sub
‚µ‚©‚µAsheet2‚̃`ƒƒ[ƒg‚ÅAì‹ÆŽžŠÔ‚Ì•”•ª‚Åü‚ª“ü‚é‚Ì‚Å‚·‚ªA‚à‚¤‚P–{‘½‚“ü‚Á‚Ä‚µ‚Ü‚¢‚Ü‚·Bü‚Ì“ü‚éꊂÍʼn‚©‚玞ŠÔ‚ÅŒ¾‚¤‚Æ17F00‚‚ç‚¢‚Ü‚Å“ü‚è‚Ü‚·B
Še‰ñ‚̘g‚Ì’†‚É‚Íì‹ÆŠJŽn‚©‚çI—¹‚Ü‚Å‚Ìü‚ÍA‚«‚¿‚ñ‚Æ“ü‚Á‚Ä‚¢‚é‚Ì‚Å‚·‚ªA‚»‚Ìã‚É
17:00 ‚Ü‚Å’·‚¢ü‚ª“ü‚Á‚Ä‚µ‚Ü‚¢‚Ü‚·B
‚Ç‚¤‚µ‚Ä‚Å‚µ‚傤‚©H
(yuri)
>‚à‚¤‚P–{‘½‚“ü‚Á‚Ä‚µ‚Ü‚¢‚Ü‚·Bü‚Ì“ü‚éꊂÍʼn‚©‚玞ŠÔ‚ÅŒ¾‚¤‚Æ17F00‚‚ç‚¢‚Ü‚Å“ü‚è‚Ü‚·B
‚±‚̃f[ƒ^i“ž’…•Ö‚©‚çì‹ÆI—¹Žž‚Ì‚·‚ׂĂł·j‚ðŒ©‚¹‚Ä‚‚¾‚³‚¢B
iichinosej 2013/10/22(‰Î) 09:13
‚±‚̂悤‚É‚È‚Á‚Ä‚¢‚Ü‚·B
Sub main2() Const ss = 2 Dim hh As Single Dim idx As Long Dim rw As Long Dim row1 As Long Dim col As Long Dim sumwidth As Single Dim sht As Worksheet Set sht = Worksheets("sheet1") With sht row1 = .Cells(.Rows.Count, "a").End(xlUp).Row End With With Worksheets("sheet2") On Error Resume Next .DrawingObjects.Delete On Error GoTo 0 For idx = 1 To ss - 1 sumwidth = sumwidth + .Columns(idx).ColumnWidth
Next hh = .Range("b:b").ColumnWidth End With Call open_scale(ss, sumwidth, hh, Worksheets("sheet2")) idx = 11 col = 2 rw = 3 Do Until idx > row1 col = 2 Do Until col > 41 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh) col = col + 4 Loop idx = idx + 2 rw = rw + 1 Loop
End Sub
(yuri)
ƒR[ƒh‚Ì’ñŽ¦‚à‚Å‚·‚ªASheet1‚̃f[ƒ^‚Å‚·B
‚»‚Ì >‚à‚¤‚P–{‘½‚“ü‚Á‚Ä‚µ‚Ü‚¢‚Ü‚·
‚Æ‚¢‚¤Sheet1‚̃f[ƒ^‚ð1s‚Å‚æ‚¢‚Å‚·‚©‚çA‘S•”‚Ý‚¹‚Ä‚‚¾‚³‚¢
iichinosej 2013/10/22(‰Î) 18:33
sheet1‚Í‚±‚̂悤‚É‚È‚Á‚Ä‚¢‚Ü‚·B
A | B C D E | F @@@@@G HI 9 @@@ | 1@@@@@@@@@@@ | 2 10 ƒOƒ‹[ƒv–¼ | “ž’…•Ö–¼ “ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ |“ž’…•Ö–¼@“ž’…ŽžŠÔ 11 ‘æ1ƒOƒ‹[ƒv | 1•Ö 07:30 2•Ö @09:00 |@‚R•Ö 10:00 12 | ì‹ÆŽžŠÔ 07:40 8:30 | ì‹ÆŽžŠÔ
B C D E‚Å‚P‰ñ‚Ìì‹Æ‚Æ‚µ‚ÄAF—ñ‚©‚ç‚Q‰ñ–Ú‚Ìì‹Æ‚ÅAO—ñ‚Ü‚Å‚P‚O‰ñ‚Ìì‹Æ‚Æ‚µ‚Ä‚¢‚Ü‚·B‚P‚O‰ñ–Ú‚Ìì‹Æ‚Ü‚Å‘S•”‹L‚µ‚½‚Ù‚¤‚ª‚æ‚¢‚Å‚·‚©‚ËH‚¤‚Ü‚“ü—Í‚Å‚«‚È‚‚ÄEEE
(yuri)
>‚P‚O‰ñ–Ú‚Ìì‹Æ‚Ü‚Å‘S•”‹L‚µ‚½‚Ù‚¤‚ª‚æ‚¢‚Å‚·‚©‚ËH ‚»‚¤‚Å‚·‚ËII@
iichinosej 2013/10/22(‰Î) 21:46
—¹‰ð‚µ‚Ü‚µ‚½B
A B C D E F @@ G @ H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO 1 2 3 5 6 7 8 9 10 10 ƒOƒ‹[ƒv–¼ “ž’…•Ö–¼ “ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ “ž’…•Ö–¼@ “ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ “ž’…•Ö–¼@ “ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ “ž’…•Ö–¼@“ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ “ž’…•Ö–¼@ “ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ “ž’…•Ö–¼@“ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ “ž’…•Ö–¼@ “ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ “ž’…•Ö–¼@ “ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ “ž’…•Ö–¼@ “ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ “ž’…•Ö–¼@ “ž’…ŽžŠÔ o”•Ö–¼ o”ŽžŠÔ
11 ‘æ1ƒOƒ‹[ƒv @
12 ì‹ÆŽžŠÔ ì‹ÆŽžŠÔ ì‹ÆŽžŠÔ ì‹ÆŽžŠÔ ì‹ÆŽžŠÔ ì‹ÆŽžŠÔ ì‹ÆŽžŠÔ ì‹ÆŽžŠÔ ì‹ÆŽžŠÔ ì‹ÆŽžŠÔ
‚Ç‚¤‚µ‚Ä‚à‚¸‚ê‚Ä‚µ‚Ü‚¢‚Ü‚·‚ªA‚±‚̂悤‚É‚È‚Á‚Ä‚¢‚Ü‚·B
(yuri)
‚¢‚¦AŽÀۂ̃f[ƒ^‚ª‚Ý‚½‚¢‚Ì‚Å‚·B •s‹ï‡‚ª‚ ‚éê‡A‚ ‚éƒf[ƒ^‚ɑ΂µ‚ÄA‚¤‚Ü‚‚¢‚©‚È‚¢@‚È‚ñ‚Ä‚±‚Æ‚Í@‚æ‚‚ ‚邱‚Æ‚Å‚·B
ƒvƒƒOƒ‰ƒ€‚Á‚ÄA“ü—̓f[ƒ^‚ª‚±‚ñ‚ȃf[ƒ^‚¾‚Á‚Ä‘z’肵‚Ä쬂·‚é‚ñ‚Å‚·‚æ‚ËII ‚Å‚àA‚«‚¿‚ñ‚Æ“ü—̓f[ƒ^‚ð”cˆ¬‚µ‚«‚ê‚Ä‚¢‚È‚¢ê‡A•s‹ï‡‚ª”¶‚µ‚Ü‚·B
‚æ‚‚±‚ÌŒfŽ¦”Â‚Å‚àŒ©‚©‚¯‚Ü‚·B‚â‚Á‚Ä‚Ý‚Ü‚µ‚½‚ªA‚¤‚Ü‚‚¢‚«‚Ü‚¹‚ñA‰½‚ªˆ«‚¢‚Ì‚Å‚µ‚傤‚©H
•s‹ï‡‚ª‚ ‚ê‚΃gƒ‰ƒuƒ‹ƒV[ƒg‚Æ‚¢‚¤ƒhƒLƒ…ƒƒ“ƒg‚ðŽ„‚½‚¿‚ÍAƒvƒƒtƒ‰ƒ€‚Ì쬎҂ɒño‚µ‚Ü‚·‚ªA ‚±‚ñ‚ȃRƒƒ“ƒg‚ð‚³‚ê‚Ä‚à‰½‚ªˆ«‚¢‚Ì‚©‚í‚©‚è‚Ü‚¹‚ñB
“ü—̓f[ƒ^‚ðˆÈ‰º‚̂悤‚ȃf[ƒ^‚ŃvƒƒOƒ‰ƒ€‚ðì“®‚³‚¹‚½‚Æ‚±‚ë@‚˜‚˜‚˜‚˜‚Æ‚¢‚¤Œ‹‰Ê‚É‚È‚èA Žd—l‚Ç‚¨‚è‚ÌŒ‹‰Ê‚É‚È‚è‚Ü‚¹‚ñ‚Å‚µ‚½B
“ü—̓f[ƒ^‚Ì’ñŽ¦
@@E
@@E
‚Æ‚¢‚¤‹Lq‚ª‚ ‚ê‚ÎA‚»‚Ì•s‹ï‡‚Ì‚ ‚Á‚½ƒf[ƒ^‚ŃvƒƒOƒ‰ƒ€‚𸸂ł«‚Ü‚·‚æ‚ËII
‚È‚Ì‚ÅA @ >‚»‚Ì >‚à‚¤‚P–{‘½‚“ü‚Á‚Ä‚µ‚Ü‚¢‚Ü‚· >‚Æ‚¢‚¤Sheet1‚̃f[ƒ^‚ð1s‚Å‚æ‚¢‚Å‚·‚©‚çA‘S•”‚Ý‚¹‚Ä‚‚¾‚³‚¢
‚ÆA\‚µ‚Ü‚µ‚½B
‹C‚É‚È‚Á‚Ä‚¢‚é‚Ì‚ÍA0F15‚È‚ñ‚ÄŽž‚ª‚ ‚è‚Ü‚¹‚ñ‚©H
iichinosej 2013/10/23(…) 00:21
‚¨‚͂悤‚²‚´‚¢‚Ü‚·B
>‹C‚É‚È‚Á‚Ä‚¢‚é‚Ì‚ÍA0F15‚È‚ñ‚ÄŽž‚ª‚ ‚è‚Ü‚¹‚ñ‚©H ‚·‚Ý‚Ü‚¹‚ñB‚ ‚è‚Ü‚µ‚½B6‰ñ–Ú‚Ìì‹ÆŠJŽnŽžŠÔ‚ª14:45‚Ì‚Æ‚±‚ë1:45‚É‚È‚Á‚Ä‚Ü‚µ‚½B ‚±‚ê‚ÅA—]Œv‚Èü‚ÍÁ‚¦‚Ü‚µ‚½B
‚¨‚©‚°‚³‚Ü‚ÅA‚悤‚â‚‚±‚±‚Ü‚Å—ˆ‚Ü‚µ‚½B ŒãAŽ„‚ªs‚¢‚½‚¢‚Ì‚Í6‰ñ–ÚˆÈ~‚̃`ƒƒ[ƒg‚Í1`5‰ñ–ڂ̉º‚É•`‚‚±‚ÆB
‚ÆA¡10ƒOƒ‹[ƒv‚Ù‚ÇAì‚Á‚Ä‚Ý‚Ü‚µ‚½‚ªŽlŠp‚̃`ƒƒ[ƒg‚ÌF‚ª“¯‚¶F‚È‚Ì‚ÅA‚Å‚«‚ê‚΃Oƒ‹[ƒv‚à25ƒOƒ‹[ƒv‚ ‚é‚Ì‚ÅA5FˆÊŒðŒÝ‚ÉF•ª‚¯‚µ‚½‚¢‚ÆŽv‚¢‚Ü‚·B
ichinose‚³‚ñBX‚É‚²‹³Žö‚ðA‚¨Šè‚¢‚µ‚Ü‚·B
(yuri)
>Ž„‚ªs‚¢‚½‚¢‚Ì‚Í6‰ñ–ÚˆÈ~‚̃`ƒƒ[ƒg‚Í1`5‰ñ–ڂ̉º‚É•`‚‚±‚ÆB ¡Ždオ‚Á‚Ä‚¢‚éƒR[ƒh‚̈Ӗ¡‚ð‚æ‚—‰ð‚Ä‚‚¾‚³‚¢‚Ë
ì}ƒV[ƒg‚̃ŒƒCƒAƒEƒg‚ðˆÈ‰º‚̃R[ƒh‚Å쬂³‚ê‚é‚悤‚È‚à‚Ì‚É•ÏX‚µ‚Ü‚µ‚傤B
•W€ƒ‚ƒWƒ…[ƒ‹‚É
Sub mk_sample2() Dim g0 As Long Dim g1 As Long With Worksheets("sheet2") .Cells.Delete .DrawingObjects.Delete .Columns("a:a").ColumnWidth = 15 .Columns("b:bc").ColumnWidth = 5 g1 = 5 For g0 = 2 To 110 Step 6 With .Range(.Cells(1, g0), .Cells(1, g0 + 5)) .MergeCells = True .Value = ((g1 - 1) Mod 24) + 1 & "Žž" .HorizontalAlignment = xlCenter With .Offset(1, 0).Resize(, 6) .Value = Array(0, 10, 20, 30, 40, 50) .HorizontalAlignment = xlLeft .NumberFormat = "general" End With End With g1 = g1 + 1 Next With .Range("a1:a2") .MergeCells = True .HorizontalAlignment = xlCenter .Value = "ƒOƒ‹[ƒv–¼" End With .Rows("1:2").RowHeight = 13.5 With .Rows("3:60") .RowHeight = 15 End With With .Columns(1) .Rows("3:4").MergeCells = True .Rows("3:4").AutoFill Destination:=.Rows("3:62"), Type:=xlFillDefault End With End With End Sub
‚¢‚©‚ª‚Å‚µ‚傤‚©H
—v‚ÍAˆê‚‚̃Oƒ‹[ƒv‚Å@2sŽg‚¤‚±‚Æ‚É‚µ‚Ü‚µ‚½B ‚±‚ê‚ňê‚‚̃Oƒ‹[ƒv‚Å2s‚Ìì}‚ª‚Å‚«‚Ü‚·‚æ‚ËH
Œ»Ý‚̃R[ƒh‚ð‚ǂ̂悤‚É’¼‚¹‚΂悢‚©H
‚Ü‚¸‚ÍA‚±‚̃ŒƒCƒAƒEƒg‚É‚«‚¿‚ñ‚Æ¡‚Ü‚Å‚Ìó‘Ô‚Æ“¯‚¶‚悤‚É ì}‚·‚邽‚ß‚É‚ÍA‚Ç‚±‚ð•Ï‚¦‚ê‚΂悢‚©@’T‚Á‚Ä‚Ý‚Ä‚‚¾‚³‚¢ (1s‚É‚P`10‰ñ–Ú‚Ìì}‚ð‚·‚é@‚Æ‚¢‚¤Œ»Ý‚Å‚«‚Ä‚¢‚éŽd—l‚ðVƒŒƒCƒAƒEƒg‚Å ŽÀŒ»‚µ‚Ä‚‚¾‚³‚¢)
ˆá‚¢‚ÍAì}ƒV[ƒg‚Å@¡‚Ü‚Å‚ÍA1s‚Å1ƒOƒ‹[ƒv‚Å‚µ‚½‚ªA¡“x‚Í@2s‚Å1ƒOƒ‹[ƒv‚ðì}‚·‚é‚ñ‚Å‚·‚æ‚ËH
F‚ÍA‚±‚ꂪo—ˆ‚Ä‚©‚ç‚É‚µ‚Ü‚µ‚傤i‚±‚Á‚¿‚Ì•û‚ªŠÈ’PH‚¾‚Æ‚¨‚à‚¢‚Ü‚·‚©‚çj
iichinosej 2013/10/24(–Ø) 04:51
Žd—l•ÏX‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
‚È‚ñ‚Æ‚©Šæ’£‚Á‚ÄA‰º‹L‚̂悤‚ȃR[ƒh‚Æ‚È‚è‚Ü‚µ‚½B
Sub main2() Const ss = 2 Dim hh As Single Dim idx As Long Dim rw As Long Dim row1 As Long Dim col As Long Dim sumwidth As Single Dim sht As Worksheet Set sht = Worksheets("sheet1") With sht row1 = .Cells(.Rows.Count, "a").End(xlUp).Row End With With Worksheets("sheet2") On Error Resume Next .DrawingObjects.Delete On Error GoTo 0 For idx = 1 To ss - 1 sumwidth = sumwidth + .Columns(idx).ColumnWidth
Next hh = .Range("b:b").ColumnWidth End With Call open_scale(ss, sumwidth, hh, Worksheets("sheet2")) idx = 11 col = 2 rw = 3 Do Until idx > row1 col = 2 Do Until col > 21 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh) col = col + 4 Loop idx = idx + 2 rw = rw + 2 Loop
idx = 11 col = 2 rw = 4 Do Until idx > row1 col = 22 Do Until col > 41 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh) col = col + 4 Loop idx = idx + 2 rw = rw + 2.5 Loop
End Sub
‚Å‚·‚ªA‚à‚Á‚Æ‚·‚Á‚«‚肵‚½‘g‚Ý•û‚Å‚à‚ ‚é‚Ì‚Å‚ÍH‚ÆŽv‚¢‚È‚ª‚çB‚Å‚àA‚í‚©‚è‚Ü‚¹‚ñ‚Å‚µ‚½B
(yuri)
‚æ‚Œ¤‹†‚³‚ꂽ‚ÆŽv‚¢‚Ü‚·B¡‰ñ‚ÍA rw = rw + 2@‚±‚ꂪ‚Å‚«‚ê‚Î@—Ç‚¢@ Žv‚Á‚Ä‚¢‚½‚Ì‚Å@‚悵‚Æ‚µ‚Ü‚µ‚傤B
rw = rw + 2.5 ‚±‚ê‚ÍA‚¢‚¯‚Ü‚¹‚ñB‚±‚ê‚ÍA‚½‚Ü‚½‚Üì“®‚µ‚Ä‚¢‚½‚ñ‚Å‚·‚æII
sub test1() dim rw as long rw=1 rw=rw+2.5 msgbox rw end sub@
sub test2() dim rw as long rw=2 rw=rw+2.5 msgbox rw end sub
‚Ç‚¿‚ç‚à4‚Æ•\Ž¦‚³‚ê‚Ü‚·B@
‚±‚Ì——R‚ðlŽ@‚·‚é‚Ì‚à‚æ‚¢•×‹‚É‚È‚é‚©‚ÆŽv‚¢‚Ü‚·B
¡‰ñ‚ÍAƒ‰ƒbƒL[‚Å‚µ‚½‚ªA‹C‚ð•t‚¯‚Ä‚‚¾‚³‚¢B
‚à‚¤ˆê‚ÂA
Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value
@‚±‚ê‚ÍAƒOƒ‹[ƒv–¼‚ð‚`—ñ‚ÉÝ’è‚·‚éƒR[ƒh‚Å‚·B
Do Until col > 21 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value Call ì}(Worksheets("sheet2"), rw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh) col = col + 4 Loop
‚±‚ê‚ðì}‚Æ“¯‚¶ƒ‹[ƒv“à‚Å‹Lq‚µ‚Ä‚¢‚Ü‚·‚ªA
‚±‚ê–{—ˆAƒOƒ‹[ƒv–ˆ‚¢‚¦As–ˆ‚Ɉê‰ñÝ’è‚·‚ê‚΂悢‚Å‚·‚æ‚ËH ã‹L‚̃R[ƒh‚ÍA1‰ñ–Ú‚Ìì}ŽžA2‰ñ–Ú‚Ìì}ŽžAEEE10‰ñ–Ú‚Ìì}Žž‚É‚»‚ꂼ‚ꓯ‚¶“à—e‚ð ݒ肵‚Ä‚¢‚Ü‚·B‚±‚ê‚àƒ‰ƒbƒL[‚È‚ñ‚Å‚·B‚½‚Ü‚½‚Ü“¯‚¶“à—e‚Ìݒ肾‚Á‚½‚Ì‚Å ‚¤‚Ü‚‚¢‚Á‚½‚ñ‚Å‚·B “¯‚¶“à—e‚ÌÝ’è‚È‚çAs‚ɂ‚«@ˆê‰ñÝ’è‚Å‚æ‚¢‚Å‚·‚æ‚ËH
ƒ‹[ƒv‚ðƒOƒ‹[ƒv‚É•t‚«@ã‚Ìs‚Ìì}‚Ɖº‚Ìs‚Ìì}‚𕪂¯‚Ä‚¢‚Ü‚·‚ªA •Ï”Col‚ª21ˆÈ‰º‚Ì‚Æ‚«‚ÍAã‚ÌsA‚»‚ê‚æ‚¢‘å‚«‚¢ê‡‚ÍA‰º‚Ìs‚Éì}‚È‚ñ‚Å‚·‚©‚çA Col‚ª21ˆÈ‰º‚Æ‚»‚ê‚æ‚èã‚ł̈—‚©‚¦‚ê‚΂悢‚Å‚·B
Sub main2() Const ss = 2 Dim hh As Single Dim idx As Long Dim rw As Long Dim row1 As Long Dim mkrw As Long Dim col As Long Dim sumwidth As Single Dim sht As Worksheet Set sht = Worksheets("sheet1") With sht row1 = .Cells(.Rows.Count, "a").End(xlUp).Row End With With Worksheets("sheet2") On Error Resume Next .DrawingObjects.Delete On Error GoTo 0 For idx = 1 To ss - 1 sumwidth = sumwidth + .Columns(idx).ColumnWidth Next hh = .Range("b:b").ColumnWidth End With Call open_scale(ss, sumwidth, hh, Worksheets("sheet2")) idx = 11 rw = 3 Do Until idx > row1 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value col = 2 Do Until col > 41 If col <= 21 Then mkrw = rw Else mkrw = rw + 1 Call ì}(Worksheets("sheet2"), mkrw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh) col = col + 4 Loop idx = idx + 2 rw = rw + 2 Loop End Sub
‚Å‚àArw = rw + 2@‚Ì‹Lq‚ª‚ ‚Á‚½‚̂ł悵‚Æ‚µ‚Ü‚µ‚傤‚©II
F‚ÍŽŸ“Še‚É‚Äà–¾‚µ‚Ü‚·
iichinosej 2013/10/25(‹à) 06:08
‚Å‚ÍAF‚ð•t‚¯‚邱‚Æ‚ðl‚¦‚Ü‚·BŒ»óAì}‚Æ‚¢‚¤ƒvƒƒVƒWƒƒ[‚ÍAŽlŠpŒ`‚ÌF‚ÌŽw’è‚ÍA‚Å‚«‚È‚¢‚Ì‚Å o—ˆ‚é‚悤‚É•ÏX‚µ‚Ü‚µ‚傤B
@'=============================================================================================================== Sub ì}(ByVal ƒV[ƒg As Worksheet, ByVal ì¬s As Long, “ž’…•Ö As String, ByVal “ž’…Žž As Date _ , ByVal o”•Ö As String, ByVal o”Žž As Date _ , ByVal ì‹ÆŠJŽnŽž As Date, ByVal ì‹ÆI—¹Žž As Date, hh As Single _ , Optional ByVal Reccolor As Long = vbRed) Dim st As Single Dim wk As Double Dim shp As Shape wk = (o”Žž - “ž’…Žž) / TimeSerial(0, 10, 0) st = (“ž’…Žž - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_shape(ƒV[ƒg.Rows(ì¬s), st, wk * hh, msoShapeRectangle, ƒV[ƒg) With shp .TextFrame.Characters.Text = “ž’…•Ö & " “ž’… : " & Format(“ž’…Žž, "hh:mm") & "^" & o”•Ö & " o” : " & Format(o”Žž, "hh:mm") '.TextFrame.Characters.Font.Color = vbBlack .TextFrame.HorizontalAlignment = xlHAlignCenter .TextFrame.VerticalAlignment = xlVAlignCenter .Fill.ForeColor.RGB = Reccolor .Fill.Transparency = 0.5 End With wk = (ì‹ÆI—¹Žž - ì‹ÆŠJŽnŽž) / TimeSerial(0, 10, 0) st = (ì‹ÆŠJŽnŽž - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_line(ƒV[ƒg.Rows(ì¬s), st, wk * hh, 80, ƒV[ƒg) With shp .Line.ForeColor.RGB = vbBlack .Line.Transparency = 0 .Line.EndArrowheadLength = msoArrowheadLengthMedium .Line.EndArrowheadWidth = msoArrowheadWidthMedium .Line.EndArrowheadStyle = msoArrowheadTriangle .Line.BeginArrowheadStyle = msoArrowheadTriangle End With End Sub
ì}‚Æ‚¢‚¤ƒvƒƒVƒWƒƒ[‚ðã‹L‚̂悤‚É•ÏX‚µ‚Ä‚‚¾‚³‚¢B ‚±‚ê‚ÅŽlŠpŒ`‚ÌF‚ÌŽw’肪‚Å‚«‚é‚悤‚É‚È‚è‚Ü‚µ‚½B‘O‰ñ“Še‚Ìmain2‚ÍAˆÈ‰º‚̂悤‚É‚·‚é‚ÆA
Sub main2() Const ss = 2 Dim hh As Single Dim idx As Long Dim rw As Long Dim row1 As Long Dim mkrw As Long Dim col As Long Dim sumwidth As Single Dim sht As Worksheet Set sht = Worksheets("sheet1") With sht row1 = .Cells(.Rows.Count, "a").End(xlUp).Row End With With Worksheets("sheet2") On Error Resume Next .DrawingObjects.Delete On Error GoTo 0 For idx = 1 To ss - 1 sumwidth = sumwidth + .Columns(idx).ColumnWidth Next hh = .Range("b:b").ColumnWidth End With Call open_scale(ss, sumwidth, hh, Worksheets("sheet2")) idx = 11 rw = 3 Do Until idx > row1 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value col = 2 Do Until col > 41 If col <= 21 Then mkrw = rw Else mkrw = rw + 1 Call ì}(Worksheets("sheet2"), mkrw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh, vbBlue) col = col + 4 Loop idx = idx + 2 rw = rw + 2 Loop End Sub
¡“x‚ÍA‚ŎlŠpŒ`‚ªì¬‚³‚ê‚Ü‚·B‚±‚ê‚ðŽg‚Á‚Äs–ˆF‚ð•Ï‚¦‚Äì}‚·‚ê‚΂悢‚Ì‚Å‚·‚ªEEEB
‚Å‚ÍA‚ǂ̂悤‚És–ˆF‚ð‚©‚¦‚ê‚΂悢‚Ì‚Å‚µ‚傤‚©H
F‚ÍAÔ@Â@ƒ}ƒWƒFƒ“ƒ^A‰©FA—Î@‚ÅŽlŠpŒ`‚ð‚‚‚邱‚Æ‚Æ‚µ‚Ü‚·B ‚±‚ÌŒJ‚è•Ô‚µ‚ÅF‚ÌÝ’è‚ð‚µ‚Ü‚·B
F‚ð•\‚·RGB’l‚ÍA‚»‚ꂼ‚ê@255A16711680A16711935A65535A65280
‚Æ‚±‚Ì”Žš‚ÌŠÔ‚É‹K‘¥«‚È‚ÇŒ©‚‚¯‚ç‚ê‚Ü‚¹‚ñII
‚±‚̂悤‚Èê‡A‹K‘¥«‚Ì‚ ‚é•Ï”‚É‚¢‚ê‚Ä–³—‚â‚è‹K‘¥«‚ðì‚é•û–@‚ª‚ ‚è‚Ü‚·B
”z—ñ‚ð‚‚‚è‚Ü‚·B
V‹KƒuƒbƒN‚Ì•W€ƒ‚ƒWƒ…[ƒ‹‚É
Sub hairetu_smp() Dim carray As Variant Dim g0 As Long carray = Array(vbRed, vbBlue, vbMagenta, vbYellow, vbGreen) For g0 = 0 To 4 Range("a3").Interior.Color = carray(g0) MsgBox carray(g0) Next End Sub
ã‹L‚̃R[ƒh‚̓Zƒ‹A3‚ðÔ@Â@ƒ}ƒWƒFƒ“ƒ^A‰©FA—΂̇˜‚ŇŽŸ“h‚è‚‚Ԃµ‚Ä‚¢‚ƒR[ƒh‚Å‚·B •\Ž¦‚³‚ê‚锎š‚ÍA‘ÎÛ‚ÌF‚ÌRGB’l‚Å‚·B
‚±‚̂悤‚É”z—ñ‚ð—˜—p‚·‚é‚ÆAŠÖ˜A‚È‚¢ƒf[ƒ^‚ªA0`4‚Ì”’l‚Å‹K‘¥«‚ðŽ‚Á‚ÄŽæ‚èo‚·‚±‚Æ‚ª‚Å‚«‚Ü‚·B
ã‹L‚Ì—á‚Å‚Í
0 Ô@1@Â@2@ƒ}ƒWƒFƒ“ƒ^@3@‰©@4@—Î
‚Æ”Žš‚ÆF‚ÌRGB’l‚ª‘Ήž‚·‚é‚悤‚É‚È‚è‚Ü‚µ‚½B
—á‚Å‚ÍAƒZƒ‹‚ð“h‚è‚‚Ԃµ‚Ü‚µ‚½‚ªA}‚ÌŽlŠpŒ`‚Å‚à“¯‚¶‚Å‚·B
‚Å‚ÍAÔ@Â@ƒ}ƒWƒFƒ“ƒ^A‰©FA—΂Ì5F‚ÅŒJ‚è•Ô‚µ“h‚è‚‚Ԃ·‚Ì‚ÍA‚ǂ̂悤‚É‚·‚ê‚Î ‚æ‚¢‚Å‚µ‚傤‚©H
‘±‚«‚ÍAŽŸ“Še‚É‚Ä
iichinosej 2013/10/25(‹à) 16:39
¡AƒZƒ‹A1`A30‚Ü‚Å@Ô@Â@ƒ}ƒWƒFƒ“ƒ^A‰©FA—΂Ƃ¢‚¤F‚Å“h‚è‚‚Ԃµ‚Ä‚¢‚‚±‚Æ‚ðl‚¦‚Ü‚·B
A1‚©‚çA5‚Ü‚Å‚È‚çŠÈ’P‚Å‚·B Sub F‚ð“h‚è‚‚Ԃ·() Dim carray As Variant Dim rw As Long carray = Array(vbRed, vbBlue, vbMagenta, vbYellow, vbGreen) rw = 1 Do Until rw > 5 Cells(rw, 1).Interior.Color = carray(rw - 1) rw = rw + 1 Loop End Sub
6s–ÚˆÈ~‚ÍA–”@”z—ñcarray‚ɑΉž‚·‚锎ši“Y‚¦Žš‚Æ‚¢‚¢‚Ü‚·j‚ð0‚É–ß‚·‚É‚ÍHHH
‚±‚̂悤‚Èê‡AŠ„‚èŽZ‚Ì—]‚è‚Ì”‚ð—˜—p‚·‚é‚ÆŠÈ’P‚ÉŽÀŒ»‚ª‚Å‚«‚Ü‚·B
¡‰ñ‚ÍA0`4‚̌܂‚̔Žš‚ÌŒJ‚è•Ô‚µ‚Å‚·‚©‚çAs‚ð5‚ÅŠ„‚Á‚½—]‚è‚ð—˜—p‚µ‚Ü‚·B
®”‚ð5‚ÅŠ„‚Á‚½—]‚è‚ÍA•K‚¸A0`4‚Å‚·‚æ‚ËII
—]‚è‚ÌŒvŽZ‚Í@MOD‰‰ŽZŽq‚ðŽg‚¤‚ÆŒvŽZ‚µ‚Ä‚‚ê‚Ü‚·B
‚·‚é‚ÆEEEA
Sub F‚ð“h‚è‚‚Ԃ·() Dim carray As Variant Dim rw As Long carray = Array(vbRed, vbBlue, vbMagenta, vbYellow, vbGreen) rw = 1 Do Until rw > 30 Cells(rw, 1).Interior.Color = carray((rw - 1) Mod 5) rw = rw + 1 Loop End Sub
‚±‚ê‚ÅA1`A30‚ªÔ@Â@ƒ}ƒWƒFƒ“ƒ^A‰©FA—΂̇˜‚ÌŒJ‚è•Ô‚µ‚Å“h‚è‚‚Ԃ·‚±‚Æ‚ªo—ˆ‚Ü‚µ‚½B
‚Å‚ÍA–{‘è‚É–ß‚Á‚ÄEEEB
ŽŸ“Še‚É‚ÄB
@
iichinosej 2013/10/25(‹à) 17:02
–{‘è‚É–ß‚Á‚ÄAŠù“Še‚Ìmain2‚ðŒ©‚Ä‚Ý‚é‚ÆEEEEA
ì}ŠJŽns‚ÍArw=3‚æ‚è@rw‚Ì3‚Å@‚»‚±‚©‚çA1s‚¨‚«‚Éì}‚µ‚Ä‚¢‚Ü‚·‚æ‚Ë
‚‚܂èA
3@5@7@9@11@13@15@EEEEE‚Æ‘±‚‚킯‚Å‚·‚ªA
‚±‚Ìs‚©‚ç0@1@2@3@4@0@1@2@3@4@‚ð‹‚ß‚é‚É‚ÍH
‘O‰ñ“Še‚̂悤‚É@‚«‚ê‚¢‚É@s‚ª@1,2,3,4,5EEE‚ƘA‘±‚µ‚Ä‚¢‚È‚¢‚Ì‚Å‚·‚ªA ‰½‚Æ‚©ŒvŽZ‚Å‚Å‚«‚È‚¢‚©‚Æ@l‚¦‚é‚ÆEEEA
‰Šú’l‚ª3‚Å‚·‚©‚ç@‚±‚ê‚ð0‚É‚·‚邽‚ß‚É‚Í@3‚ðˆø‚¢‚Ä‚¨‚¯‚΂悢‚Å‚·‚Ë‚¦
3-3‚ÍA0‚Å‚·‚©‚ç‚Ë
ŽŸ‚Ì5‚à@3‚ðˆø‚‚Æ@@2‚Å‚·B‚±‚ê‚ð1‚É‚·‚é‚É‚ÍA2‚ÅŠ„‚ê‚Î@2/2@1‚Å‚·‚Ë
ŽŸ‚Ì‚V‚ÍA@7-34@‚±‚ê‚ð2‚ÅŠ„‚Á‚Ä@4/22
‚±‚ê‚Å‚¢‚¯‚»‚¤‚Å‚·‚ËII@‚à‚¤‚ЂƂŒŸŽZ
(9-3)/2=3 ‚à‚¤Š®àøII
(rw-3)/2 ‚±‚ê‚Å@0,1,2,3,4,5,6,7@‚ƘA‘±‚µ‚½’l‚ª“¾‚ç‚ê‚Ü‚·B
‚ ‚Æ‚ÍA‚±‚Ì’l‚ð5‚ÅŠ„‚Á‚½—]‚è‚ðo‚¹‚ÎA”z—ñcarray‚Ì“Y‚¦Žš‚ª‚à‚Æ‚ß‚ç‚ê‚Ü‚·‚Ë‚¦
‚»‚ê‚ð@ƒvƒƒVƒWƒƒ[ì}‚̃pƒ‰ƒ[ƒ^‚Æ‚µ‚Ä“n‚¹‚ÎAF‚ª5s–ˆ‚Ɉá‚Á‚½ŽlŠpŒ`‚ªì}‚³‚ê‚Ü‚·B
‚±‚ê‚ðƒR[ƒh‰»‚µ‚Ä‚‚¾‚³‚¢B‚±‚ê‚ÅŠ®¬‚·‚é‚Í‚¸‚Å‚·B
iichinosej 2013/10/25(‹à) 17:52
‚²‹³Žö‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
‚Ü‚¸‚ÍA‚Q’i‚É•ª‚¯‚Äì}‚ð‚·‚éƒR[ƒh‚ɂ‚«‚Ü‚µ‚Ä‚ÍA¦‚¢‚Å‚·BŠm‚©‚É“¯‚¶ƒR[ƒh‚ð‚Q‰ñ‚àݒ肵‚È‚‚Ä‚àA‚±‚ñ‚È‚É‚·‚Á‚«‚è‚Å‚«‚é‚Æ‚ÍEEE
–{“–‚ÉA‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚µ‚½B–”A‚Æ‚Ä‚à•×‹‚É‚È‚è‚Ü‚µ‚½B
‚³‚ÄAŽŸ‚ÌF‚ɂ‚¢‚Ä‚Å‚·‚ªAƒR[ƒh‚ð•ÏX‚µ‚Ü‚µ‚½BŠm‚©‚É‚Åì}‚³‚ê‚Ä‚¨‚è‚Ü‚·B
‚±‚ê‚©‚çAƒR[ƒh‰»‚ÉŒü‚¯‚ÄA‚â‚Á‚Ä‚Ý‚Ü‚·B
‚»‚ê‚©‚çA‚¨•·‚«‚µ‚½‚¢Ž–‚ª‘‚¦‚Ü‚µ‚½B
‚Ü‚¸A
1, sheet2‚ÌŽlŠp‚Ì}‚Å‚·‚ªA‚«‚¿‚ñ‚Æ•\Ž¦‚Í‚³‚ê‚Ä‚¢‚Ü‚·‚ªA‚»‚ÌꊈȊO‚ÉŽlŠp‚ªƒMƒ…‚Á‚Æ‹Ãk‚³‚ê‚Ä•\Ž¦‚³‚ê‚Ä‚µ‚Ü‚¢‚Ü‚·B
ꊂÍAsheet2‚ÌAs‚̃Oƒ‹[ƒv–¼‚ª“ü‚銂ÌAƒZƒ‹A‚̈ê”Ô¶‚ÉŽlŠp‚Ì}‚Æ–îˆó‚ª•\Ž¦‚³‚ê‚Ü‚·Bˆê‰ñ‘S‚Ä”CˆÓ‚ÉÁ‚µ‚Ü‚µ‚½‚ªA‚â‚Í‚è‚Ü‚½o‚Ä‚µ‚Ü‚¢‚Ü‚·B
2, Asheet2‚̃`ƒƒ[ƒg•\‚ÅŠeƒOƒ‹[ƒv–ˆ‚ÉŒrü‚ðˆø‚«‚Ü‚µ‚½‚ªAƒ}ƒNƒ‚ðƒXƒ^[ƒg‚³‚¹‚é‚ÆŒrü‚ª‘S‚ÄÁ‚¦‚Ü‚·B‚±‚ê‚ÍŽd•û‚Ì–³‚¢‚±‚Æ‚È‚Ì‚Å‚µ‚傤‚©H
(yuri)
‚P ‚ɂ‚¢‚Ä
>ƒZƒ‹A‚̈ê”Ô¶‚ÉŽlŠp‚Ì}‚Æ–îˆó‚ª•\Ž¦‚³‚ê‚Ü‚· ³‚µ‚ŽlŠpŒ`‚ªì}‚³‚ê‚È‚¢Žž‚ª‚ ‚é‚©‚炾‚ÆŽv‚¢‚Ü‚·B ‚»‚ÌŽž‚ª‰½‰ñ–Ú‚È‚Ì‚©‹³‚¦‚Ä‚‚¾‚³‚¢B ‚»‚ê‚ÆAŠeƒOƒ‹[ƒv‚ÍA•K‚¸A1‰ñ–Ú‚©‚ç10‰ñ–ڂ܂Ńf[ƒ^i“ž’…•Ö–¼‚©‚çì‹ÆI—¹Žžj‚ª‚ ‚é‚Æ ‰¼’肵‚ẴvƒƒOƒ‰ƒ€‚Å‚·B
–”‚ÍAì}‚³‚ê‚È‚¢ƒOƒ‹[ƒv‚Ì1‰ñ–Ú‚©‚ç10‰ñ–ڂ̃f[ƒ^‚ð‚·‚×‚Ä‹Lq‚µ‚Ä‚‚¾‚³‚¢B
‚Q‚ɂ‚¢‚Ä
mk_sample2‚ðˆÈ‰º‚̂悤‚É•ÏX‚µ‚Ä‚‚¾‚³‚¢BƒvƒƒOƒ‰ƒ€‚ÅŒrü‚à•t‚¯‚Ü‚µ‚½B
'================================================================== Sub mk_sample2() Dim g0 As Long Dim g1 As Long With Worksheets("sheet2") .Cells.Delete .DrawingObjects.Delete .Columns("a:a").ColumnWidth = 15 .Columns("b:dk").ColumnWidth = 5 g1 = 5 For g0 = 2 To 110 Step 6 With .Range(.Cells(1, g0), .Cells(1, g0 + 5)) .MergeCells = True .Value = ((g1 - 1) Mod 24) + 1 & "Žž" .HorizontalAlignment = xlCenter With .Offset(1, 0).Resize(, 6) .Value = Array(0, 10, 20, 30, 40, 50) .HorizontalAlignment = xlLeft .NumberFormat = "general" End With End With g1 = g1 + 1 Next With .Range("a1:a2") .MergeCells = True .HorizontalAlignment = xlCenter .Value = "ƒOƒ‹[ƒv–¼" End With .Rows("1:2").RowHeight = 13.5 With .Rows("3:60") .RowHeight = 15 End With With .Columns(1) .Rows("3:4").MergeCells = True .Rows("3:4").AutoFill Destination:=.Rows("3:62"), Type:=xlFillDefault
End With With .Range("a1:dk62") With .Borders .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone
End With End With End Sub
–”A¡‰ñ‚Ì–â‘è‚Æ‚ÍŠÖŒW‚ ‚è‚Ü‚¹‚ñ‚ªAˆÈ‰º‚悤‚Émain2@ì}@‚Æ‚¢‚¤ƒvƒƒOƒ‰ƒ€‚ð•ÏX‚µ‚Ä‚‚¾‚³‚¢B
Sub main2() Const ss = 2 Dim hh As Single Dim idx As Long Dim rw As Long Dim row1 As Long Dim mkrw As Long Dim col As Long Dim sumwidth As Single Dim sht As Worksheet Set sht = Worksheets("sheet1") With sht row1 = .Cells(.Rows.Count, "a").End(xlUp).Row End With With Worksheets("sheet2") On Error Resume Next .DrawingObjects.Delete On Error GoTo 0 For idx = 1 To ss - 1 sumwidth = sumwidth + .Columns(idx).ColumnWidth Next hh = .Range("b:b").ColumnWidth End With Call open_scale(ss, sumwidth, hh, Worksheets("sheet2")) idx = 11 rw = 3 Do Until idx > row1 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value col = 2 Do Until col > 38 If col <= 18 Then mkrw = rw Else mkrw = rw + 1 Call ì}(Worksheets("sheet2"), mkrw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh, vbBlue) col = col + 4 Loop idx = idx + 2 rw = rw + 2 Loop End Sub '=============================================================================================================== Sub ì}(ByVal ƒV[ƒg As Worksheet, ByVal ì¬s As Long, “ž’…•Ö As String, ByVal “ž’…Žž As Date _ , ByVal o”•Ö As String, ByVal o”Žž As Date _ , ByVal ì‹ÆŠJŽnŽž As Date, ByVal ì‹ÆI—¹Žž As Date, hh As Single _ , Optional ByVal Reccolor As Long = vbRed) Dim st As Single Dim wk As Double Dim shp As Shape wk = (o”Žž - “ž’…Žž) / TimeSerial(0, 10, 0) st = (“ž’…Žž - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_shape(ƒV[ƒg.Rows(ì¬s), st, wk * hh, msoShapeRectangle, ƒV[ƒg) With shp .TextFrame.Characters.Text = “ž’…•Ö & " “ž’… : " & Format(“ž’…Žž, "hh:mm") & "^" & o”•Ö & " o” : " & Format(o”Žž, "hh:mm") .TextFrame.Characters.Font.Color = vbBlack .TextFrame.HorizontalAlignment = xlHAlignCenter .TextFrame.VerticalAlignment = xlVAlignCenter .Fill.ForeColor.RGB = Reccolor .Fill.Transparency = 0.5 End With wk = (ì‹ÆI—¹Žž - ì‹ÆŠJŽnŽž) / TimeSerial(0, 10, 0) st = (ì‹ÆŠJŽnŽž - TimeSerial(5, 0, 0)) / TimeSerial(0, 10, 0) * hh Set shp = mk_line(ƒV[ƒg.Rows(ì¬s), st, wk * hh, 80, ƒV[ƒg) With shp .Line.ForeColor.RGB = vbBlack .Line.Transparency = 0 .Line.EndArrowheadLength = msoArrowheadLengthMedium .Line.EndArrowheadWidth = msoArrowheadWidthMedium .Line.EndArrowheadStyle = msoArrowheadTriangle .Line.BeginArrowheadStyle = msoArrowheadTriangle End With End Sub
mk_sample2‚ÌŽÀs‚Å쬂³‚ꂽSheet2‚̃ŒƒCƒAƒEƒg‚Å@main2‚ðŽÀs‚µ‚Ä‚Ý‚Ä‚‚¾‚³‚¢B
Œrü‚ÍAŽ„‚Ì•û‚Å‚ÍAÁ‚¦‚Ü‚¹‚ñiŽ„‚̊‹«‚Å‚Í@‚à‚Á‚Æ‚àŒrü‚ÍAʼn‚©‚çÁ‚¦‚Ä‚¢‚Ü‚¹‚ñ‚ªjB
ˆÈã‚Å‚·B
iichinosej 2013/10/26(“y) 08:45
‘å•ÏŽ¸—炵‚Ü‚µ‚½B
1.‚ÌŽ¿–â‚ɂ‚¢‚Ä‚Í
>ŠeƒOƒ‹[ƒv‚ÍA•K‚¸A1‰ñ–Ú‚©‚ç10‰ñ–ڂ܂Ńf[ƒ^i“ž’…•Ö–¼‚©‚çì‹ÆI—¹Žžj‚ª‚ ‚é‚Ɖ¼’肵‚ẴvƒƒOƒ‰ƒ€‚Å‚·B
@Šm‚©‚ÉAsheet1‚Ìdata‚É‹ó—“‚ª‚ ‚鎞‚É‚Ì‚Ý”¶‚µ‚Ä‚Ü‚µ‚½B1‚©‚ç10‰ñ‚Ü‚Å“ü‚é‚ÆÁ‚¦‚Ü‚·B
2.‚ɂ‚¢‚Ä‚Í
@Œrü‚ðƒ}ƒNƒ‚ňø‚‚É‚ÍA‚±‚̂悤‚ȃR[ƒh‚É‚È‚é‚ñ‚Å‚·‚ËEEE
@‚±‚ê‚àAŽ„“I‚É‚¿‚å‚Á‚ƕ׋‚µ‚Äcƒ‰ƒCƒ“(Vertical•ûŒü)‚ðxlDot‚É‚µ‚Ä‚Ý‚Ü‚µ‚½B
@‚Å‚«‚½‚çA‚È‚é‚Ù‚Ç‚Á‚ÄŠ´“®‚µ‚Ä‚¢‚Ü‚·B
‚Ù‚ñ‚Æ‚¤‚ÉAƒR[ƒh•ÏX‚µ‚Ä’¸‚«A‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
ÅŒã‚̉ۑèuFv‚ÍA“‚‚Ä”Y‚ñ‚Å‚¢‚銂ł·B
(yuri)
>Šm‚©‚ÉAsheet1‚Ìdata‚É‹ó—“‚ª‚ ‚鎞‚É‚Ì‚Ý”¶‚µ‚Ä‚Ü‚µ‚½B1‚©‚ç10‰ñ‚Ü‚Å“ü‚é‚ÆÁ‚¦‚Ü‚·B ŽÀۂ̃f[ƒ^‚É‚à‹ó—“‚ª‚ ‚é‚Ì‚È‚çA‚»‚ê‚ðˆÓŽ¯‚µ‚½ƒR[ƒh‚É‚·‚é•K—v‚ª‚ ‚é‚ñ‚Å‚·‚æII —Ⴆ‚ÎA“ž’…•Ö–¼‚̃Zƒ‹‚ª–¢“ü—Í‚¾‚Á‚½‚çA@Call ì}iEEEj‚ÍAŽÀs‚µ‚È‚¢‚悤‚É‚·‚é ‚Æ‚©@l‚¦‚é‚Æ•û–@‚Í‚ ‚é‚ÆŽv‚¢‚Ü‚·‚æII
>ÅŒã‚̉ۑèuFv‚ÍA“‚‚Ä”Y‚ñ‚Å‚¢‚銂ł·B
F‚𕪂¯‚él‚¦•û‚ÍAà–¾‚µ‚½‚‚à‚è‚Å‚·B
“Še‚ð“Ç‚ñ‚ÅAŠeƒR[ƒh‚ðŽÀs‚µ‚»‚̈Ӗ¡‚𒲂×l‚¦‚Ä’¸‚«A ‚±‚±‚Ü‚Å‚ÍA—‰ð‚Å‚«‚é@‚ªA‚±‚±‚ª‚í‚©‚ç‚È‚¢“™‚̃|ƒCƒ“ƒg‚ði‚Á‚½‹Lq‚ð‚µ‚Ä‚Ý‚Ä‚‚¾‚³‚¢B
yuri‚³‚ñ‚Ì‚±‚̃XƒŒƒbƒh‚Ì“Še‚ªæŒŽ26“ú‚Å‚·Bˆêƒ–ŒŽŒo‚Á‚Ä‚µ‚Ü‚¢‚Ü‚µ‚½B ÅŒãA¥”ñ@‚²Ž©•ª‚ÅŠ®¬‚³‚¹‚Ä‚‚¾‚³‚¢B
iichinosej 2013/10/26(“y) 20:59
FX‚Æ‚¨¢˜b‚É‚È‚è‚Ü‚·B
sheet1‚Ì“ü—Í•\‚Í–ˆ“úˆê‰ñ‘S•”Á‚µ‚ÄŽg—p‚·‚é‚Ì‚ÅA¡‰ñ‘S•”Á‚µ‚Ä‚Ý‚½‚çŽlŠp‚Ì}‚ªsheet2‚Ì’[‚Éì}‚³‚ꂽŽ–‚É‹C•t‚«‚Ü‚µ‚½B
ŽÀÛ‚É‚ÍAsheet1‚Ìdata‚Í‘S•”–„‚܂鎖‚ðl‚¦‚Ä‚¢‚Ü‚µ‚½‚ªA—j“ú‚É‚æ‚Á‚Ä‚ÍAŠeƒOƒ‹[ƒv2‰ñˆÊ‹ó—“‚ª‚Å‚«‚鎖‚ª‚í‚©‚è‚Ü‚µ‚½B
‚¨Žè”‚Å‚·‚ªA“ž’…•Ö–¼‚ª–¢“ü—Í‚ÌꇂÍAì}‚µ‚È‚¢B‚悤‚É‚¨Šè‚¢‚µ‚½‚¢‚Ì‚Å‚·‚ªH
–”AF‚ɂ‚¢‚Ä‚ÍA‚È‚ñ‚Æ‚©Šæ’£‚Á‚Ä‚Ý‚Ü‚·B
‚P‚©ŒŽ‚à‚²‹³Žö‚µ‚Ä’¸‚«AŠ´ŽÓ‚µ‚Ä‚¢‚Ü‚·B
(yuri)
uFvo—ˆ‚Ü‚µ‚½B@Œ©‚Ä‚‚¾‚³‚¢B
Sub main2() Const ss = 2 Dim hh As Single Dim idx As Long Dim rw As Long Dim row1 As Long Dim mkrw As Long Dim col As Long Dim carray As Variant Dim sumwidth As Single Dim sht As Worksheet Set sht = Worksheets("sheet1") With sht row1 = .Cells(.Rows.Count, "a").End(xlUp).Row End With With Worksheets("sheet2") On Error Resume Next .DrawingObjects.Delete On Error GoTo 0 For idx = 1 To ss - 1 sumwidth = sumwidth + .Columns(idx).ColumnWidth Next hh = .Range("b:b").ColumnWidth End With Call open_scale(ss, sumwidth, hh, Worksheets("sheet2")) carray = Array(vbRed, vbBlue, vbMagenta, vbYellow, vbGreen)
idx = 11 rw = 3 Do Until idx > row1 Worksheets("sheet2").Cells(rw, 1).Value = sht.Cells(idx, 1).Value col = 2 Do Until col > 38 If col <= 18 Then mkrw = rw Else mkrw = rw + 1 Call ì}(Worksheets("sheet2"), mkrw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh, carray((rw - 3) / 2 Mod 5))
col = col + 4 Loop idx = idx + 2 rw = rw + 2 Loop End Sub
‚±‚̂悤‚É‚È‚è‚Ü‚µ‚½BOK‚Å‚µ‚傤‚©H
(yuri)
>‚±‚̂悤‚É‚È‚è‚Ü‚µ‚½BOK‚Å‚µ‚傤‚©H ‚n‚j‚Å‚·‚Ë‚¦II
‚ ‚ƈê‚ÂA
>>ŽÀۂ̃f[ƒ^‚É‚à‹ó—“‚ª‚ ‚é‚Ì‚È‚çA‚»‚ê‚ðˆÓŽ¯‚µ‚½ƒR[ƒh‚É‚·‚é•K—v‚ª‚ ‚é‚ñ‚Å‚·‚æII >>—Ⴆ‚ÎA“ž’…•Ö–¼‚̃Zƒ‹‚ª–¢“ü—Í‚¾‚Á‚½‚çA@Call ì}iEEEj‚ÍAŽÀs‚µ‚È‚¢‚悤‚É‚·‚é >>‚Æ‚©@l‚¦‚é‚Æ•û–@‚Í‚ ‚é‚ÆŽv‚¢‚Ü‚·‚æII
>ŠeƒOƒ‹[ƒv2‰ñˆÊ‹ó—“‚ª‚Å‚«‚鎖‚ª‚í‚©‚è‚Ü‚µ‚½B
‚Å‚ÍA‰½‚ð‚Ý‚Ä‚»‚̉ñ‚ª–¢“ü—Í‚Æ‚Ý‚È‚·‚©H ãq‚µ‚½‚悤‚ÉA“ž’…•Ö–¼‚̃Zƒ‹‚ª–¢“ü—Í‚¾‚Á‚½‚çì}‚µ‚È‚¢‚Æ‚¢‚¤Žd—l‚µ‚Ü‚µ‚傤B
“ž’…•Ö–¼‚ªÝ’肳‚ê‚Ä‚¢‚éƒZƒ‹‚Ì’†g‚Á‚Ämain2‚Ì‚Å‚ÍA sht.Cells(idx, col).Value@‚Å‚·‚æ‚ËII
‚Å‚ÍA‚±‚Ì’†g‚ðƒ`ƒFƒbƒN‚·‚ê‚΂悢‚Å‚·‚æ‚ËH
If sht.Cells(idx, col).Value<>"" then call ì}iEEEj
‚Å‚·B
ÅŒã‚ÍA‚¨‚Ü‚¯‚Á‚Û‚¢‚¯‚ÇA‚æ‚¢‚Å‚µ‚傤B
‚±‚ÌŽdオ‚Á‚½ƒR[ƒh‚æ‚Œ©’¼‚µ‚Ä‚‚¾‚³‚¢B
@
iichinosej 2013/10/27(“ú) 16:41
1‚©ŒŽA‚²‹³Žö‚µ‚Ä’¸‚«‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚µ‚½B
ÅŒã‚̃R[ƒh‚Å‚·B
If sht.Cells(idx, col).Value <> "" Then If sht.Cells(idx + 1, col + 2).Value <> "" Then Call ì}(Worksheets("SKD•\"), mkrw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh, carray((rw - 3) / 2 Mod 5))
‚±‚ê‚ÅAŽlŠp‚Ì}‚Æ–îˆó‚ÍÁ‚¦‚Ü‚µ‚½B
(yuri)
If sht.Cells(idx, col).Value <> "" And sht.Cells(idx + 1, col + 2).Value <> "" Then Call ì}(Worksheets("SKD•\"), mkrw, sht.Cells(idx, col).Value, sht.Cells(idx, col + 1).Value, sht.Cells(idx, col + 2).Value, _ sht.Cells(idx, col + 3).Value, sht.Cells(idx + 1, col + 2).Value, sht.Cells(idx + 1, col + 3).Value, hh, carray((rw - 3) / 2 Mod 5)) End If
‚±‚±‚ÍA‚±‚̂悤‚É‚·‚é‚ÆA‚æ‚¢‚Å‚·‚ËII
If•¶‚à‚¢‚‚‚©‚̃pƒ^[ƒ“‚ª‚ ‚è‚Ü‚·‚©‚çA‚æ‚’²‚ׂĂ݂Ă‚¾‚³‚¢B
‘S•”‚̃pƒ^[ƒ“‚ª‚©‚È‚è‚Ì•p“x‚ÅŽg‚í‚ê‚Ä‚¢‚Ü‚·‚æB
‚Æ‚É‚©‚A‚±‚ê‚Å‚¨‚¨‚æ‚»Ždオ‚è‚Ü‚µ‚½‚ËII ‚ ‚Æ‚ÍA‚悃eƒXƒg‚µ‚½‚èAƒAƒ‹ƒSƒŠƒYƒ€‚ðŠm”F‚µ‚Ä‚Ý‚Ä‚‚¾‚³‚¢
iichinosej 2013/10/28(ŒŽ) 07:02
ƒR[ƒh‚Ì•ÏXA‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
•×‹‚É‚È‚è‚Ü‚·B
>‚悃eƒXƒg‚µ‚½‚èAƒAƒ‹ƒSƒŠƒYƒ€‚ðŠm”F‚µ‚Ä‚Ý‚Ä‚‚¾‚³‚¢
‚Í‚¢BŒ»ÝƒeƒXƒg’†‚Å‚·BŽlŠp‚Ì’†‚Ì•¶Žš‚ð‚µ‘¾‚‚µ‚½‚èA‚¿‚å‚Á‚Æ•ÏX‚µ‚Ä‚¢‚Ü‚·B
‚ ‚ÆAƒR[ƒh‚ðÄ“xŠm”F‚µA—‰ð‚Å‚«‚é‚悤•×‹‚µ‚Ü‚·B
(yuri)
Œ³ƒf[ƒ^‚Ísheet4‚É“ü‚ê‚Ü‚µ‚½B
sheet‚Ìì‚è‚Í
B C E G H J M N 4 “ž’…•Ö–¼ o”•Ö–¼ “ž’…Žž o”Žž ’S“–ƒOƒ‹[ƒv–¼ ì‹ÆŠJŽnŽžŠÔ I—¹ŽžŠÔ •\Ž¦
5 AAA•Ö BBB•Ö 06:30 08:30 “c’† 06:40 07:40 ã 6 CCC•Ö DDD•Ö 06:30 08:30 ²“¡ 06:40 07:40 ã 7 EEE•Ö FFF•Ö 06:30 08:30 ‹g“c 06:40 07:40 ã 8 GGG•Ö GGG•Ö 06:30 08:30 “c’† 06:40 07:40 ‰º
@s‚ª5‚©‚ç84s@88s–Ú‚©‚ç105s
“¯‚¶‚悤‚ÉA
R S U W X Z AB AD 4 “ž’…•Ö–¼ o”•Ö–¼ “ž’…Žž o”Žž ’S“–ƒOƒ‹[ƒv–¼ ì‹ÆŠJŽnŽžŠÔ I—¹ŽžŠÔ •\Ž¦
5 HHH•Ö III•Ö 06:30 08:30 “c’† 06:40 07:40 ã 6 JJJ•Ö KKK•Ö 06:30 08:30 ²“¡ 06:40 07:40 ã 7 LLL•Ö MMM•Ö 06:30 08:30 ‹g“c 06:40 07:40 ã 8 NNN•Ö PPP•Ö 06:30 08:30 “c’† 06:40 07:40 ‰º
s‚ª5`84s ‚±‚̂悤‚Èì‚è‚É‚È‚Á‚Ä‚¢‚Ü‚·B
‚±‚ê‚ðSheet1‚É“]‹L‚µ‚½‚¢‚ÆŽv‚Á‚Ä‚¢‚Ü‚·B
?@Šî–{“I‚É‚ÍAã‚©‚çA‚‚܂èB`N‘¤‚Ì5s`105s‚ðAŽŸ‚ÉR`AD‘¤‚Ì5s`84s‚ð
@sheet1‚Ìã‚©‚ç“]‹L‚·‚éB
?AƒOƒ‹[ƒv–¼‚ª“¯‚¶–¼‘O‚ª‚ ‚éꇂÍA‚»‚Ìì‹Æ‚Í2‰ñ–Ú‚Ìì‹Æ‚É“]‹L‚³‚ê‚éB3‰ñ“¯‚¶–¼‘O‚ª‚ ‚鎞‚Í3‰ñ–Ú‚Ìì‹Æ‚Æ‚È‚è‚Ü‚·B
?BN—ñ‚ÆAD—ñ‚Ìu•\Ž¦v‚Å‚·‚ªAuãv‚Æ“ü—Í‚µ‚½ê‡‚Í1‰ñ–Ú`5‰ñ–Úì‹Æ‚ÌŠ‚É“]‹L‚³‚êAu‰ºv“ü‚ê‚ê‚ÎA6‰ñ–Ú`10‰ñ–Úì‹Æ‚É“]‹L‚³‚ê‚éB
sheet1‚Ìì‚è‚Å‚·B
A | B C D E | F @@@@@G E 9 @@@ | 1‰ñ–Úì‹Æ@@@@@@@@@@@ | 2‰ñ–Úì‹Æ 10 ƒOƒ‹[ƒv–¼ | “ž’…•Ö–¼ “ž’…Žž o”•Ö–¼ o”Žž |“ž’…•Ö–¼@“ž’…Žž 11 ‘æ1ƒOƒ‹[ƒv | AA•Ö 07:30 BB•Ö 09:00 | 12 | ì‹ÆŽžŠÔ 07:40 8:30 |
A | B C D E | F @@@@@G E 13 @@@ | 1‰ñ–Úì‹Æ@@@@@@@@@@@| 2‰ñ–Úì‹Æ 14 ƒOƒ‹[ƒv–¼ | “ž’…•Ö–¼ “ž’…Žž o”•Ö–¼ o”Žž |“ž’…•Ö–¼@“ž’…Žž 15 ‘æ2ƒOƒ‹[ƒv | CC•Ö 06:30 DD•Ö 08:00 | 16 | ì‹ÆŽžŠÔ 06:40 7:30 |
‚ÆA–ñ30ƒOƒ‹[ƒv‚ ‚è‚Ü‚·B
ãŽè‚à–¾‚ªo—ˆ‚Ä‚¢‚È‚¢‚©‚à’m‚ê‚Ü‚¹‚ñB–”Aƒ}ƒNƒ‚ʼn½ˆ‚܂ʼn”\‚©‚ªA•ª‚©‚è‚Ü‚¹‚ñ‚ªA‚²‹³Ž¦‚Ì‚Ù‚Ç‚æ‚낵‚‚¨Šè‚¢‚¢‚½‚µ‚Ü‚·B
ichinose—l‚É‚ÍA‚¨ŽžŠÔ‚ð‚³‚¢‚ÄA‚²‹³Žö‚µ‚Ä’¸‚¢‚½‚Ì‚ÉA^‚É\‚µ–ó‚ ‚è‚Ü‚¹‚ñB
(yuri)
>•Ê‚Ìsheet‚É“ü—Í‚µ‚Ä‚ ‚é“à—e‚ðA‚±‚Ìsheet1‚É“ü—Í‚µ‚Ä‚¢‚‚É‚ÍA‚©‚Ȃ莞ŠÔ‚ªŠ|‚©‚é‚Ì‚Å ‚Ç‚È‚½‚©‚É@‚»‚̂悤‚ÉŒ¾‚í‚ê‚Ä‚Ì‚Å‚·‚©H
‚»‚ê‚Í‚Æ‚à‚©‚AŽd—l‚ÌŠm”F‚ð‚µ‚Ü‚µ‚傤II
Sheet4‚̃f[ƒ^@--->@Sheet1‚É•\‚ðì¬
Sheet1‚Ì•\@@@--->@Sheet2‚ɃKƒ“ƒgƒ`ƒƒ[ƒgì¬@iŠùìj
Sheet1‚Ì•\‚ðƒrƒWƒ…ƒAƒ‹‰»‚µ‚½‚à‚Ì‚ªSheet2‚̃Kƒ“ƒgƒ`ƒƒ[ƒg‚Å‚·‚æ‚ËH ‚»‚¤‚È‚ê‚ÎA“¯‚¶Ží—Þƒf[ƒ^‚ðSheet1‚ÆSheet2‚ÉŽc‚·•K—v‚ª‚ ‚é‚Ì‚©H ‚Æ‚¢‚¤‹^–₪o‚Ä‚«‚Ü‚·B
Sheet4‚̃f[ƒ^@--->@Sheet2‚̃Kƒ“ƒgƒ`ƒƒ[ƒgì¬
‚Å‚æ‚¢‚Å‚Í‚È‚¢‚©H@‚Æ‚¢‚¤‚±‚Æ‚Å‚·B‚¢‚©‚ª‚Å‚·‚©HH
Sheet4‚̃f[ƒ^‚Å‚·‚ªEEEB
>s‚ª5‚©‚ç84s@88s–Ú‚©‚ç105s
ª‚±‚̈Ӗ¡‚ª‚Í‚Á‚«‚è”cˆ¬‚Å‚«‚Ü‚¹‚ñ‚ªAƒf[ƒ^‚ð“r’†‚ÅÜ‚è•Ô‚µ‚Ä‚¢‚é@‚Æ‚¢‚¤‚±‚Æ‚Å‚·‚©H
•\‚Æ‚µ‚ÄA‚a—ñ‚©‚ç‚m—ñ‚É‚¾‚¯ƒf[ƒ^‚ð“ü‚ê‚Ä‚¢‚@‚Æ‚¢‚¤ƒf[ƒ^Œ`Ž®‚Å‚Í–â‘肪‚ ‚é‚Ì‚Å‚·‚©H
VBA‚ŃvƒƒOƒ‰ƒ~ƒ“ƒO‚·‚éê‡A‚±‚ê‚ç‚Ì•\‚̓e[ƒuƒ‹‚ƌĂ΂ê‚éƒf[ƒ^ƒx[ƒX‚Ì’PˆÊ‚É‚È‚è‚Ü‚·‚ªA ‚±‚ê‚Ì\¬‚͋ɗ̓Vƒ“ƒvƒ‹‚ª“S‘¥‚Å‚·B
ŒŸ“¢‚µ‚Ä‚Ý‚Ä‚‚¾‚³‚¢B
‚»‚ê‚Æ‚±‚̃XƒŒƒbƒh’·‚‚È‚Á‚Ä‚µ‚Ü‚Á‚½‚Ì‚ÅAƒXƒŒƒbƒh‚ðV‚µ‚‚µ‚Ä u‚P“ú‚Ìì‹ÆH’ö‚ðƒKƒ“ƒgƒ`ƒƒ[ƒg•\Ž¦‚É‚µ‚½‚¢2v‚Æ‚Å‚à‚µ‚ÄAV‹K‚ɃXƒŒƒbƒh‚𗧂¿ã‚°‚Ä‚Ý‚½‚ç‚Ç‚¤‚Å‚·‚©H
‚»‚ÌÛ‚ÍA‚±‚̃XƒŒƒbƒh‚Æ‚Ì‘ŠŒÝƒŠƒ“ƒN‚ðs‚Á‚Ä‚‚¾‚³‚¢B
iichinose@‚±‚ê‚©‚çŽRØÌ‚èj 2013/11/09(“y) 09:24
‚¨¢˜b‚É‚È‚è‚Ü‚·B
>•Ê‚Ìsheet‚É“ü—Í‚µ‚Ä‚ ‚é“à—e‚ðA‚±‚Ìsheet1‚É“ü—Í‚µ‚Ä‚¢‚‚É‚ÍA‚©‚Ȃ莞ŠÔ‚ªŠ|‚©‚é‚Ì‚Å ‚Ç‚È‚½‚©‚É@‚»‚̂悤‚ÉŒ¾‚í‚ê‚Ä‚Ì‚Å‚·‚©H @E‚Í‚¢BŽÀ‚ÍA‰ïŽÐ‚ÅŽ„‚ðŠÜ‚ß8l‚±‚Ì•\‚ðˆµ‚¢‚Ü‚µ‚ÄA¡‚Ü‚Å‚Í‚»‚Ìsheet4‚̃f[ƒ^‚ðŠî‚ÉAì‹ÆH’ö‚ðƒzƒƒCƒgƒ{[ƒh‚Éü‚ð‘‚¢‚Ä‚¢‚Ü‚µ‚½B‚ÅŽ„‚Ì’ñˆÄ‚ÅAƒzƒƒCƒgƒ{[ƒhŽ®‚ðŽ~‚߃pƒ\ƒRƒ“ŠÇ—‚Í‚Å‚«‚È‚¢‚à‚Ì‚©H‚ÅŽn‚Ü‚è‚Ü‚µ‚½B ‚µ‚©‚µAsheet4‚ðsheet1‚ɃKƒ“ƒgƒ`ƒƒ[ƒg—p‚Æ‚µ‚Ä“ü—Í‚·‚é‚Ì‚ÉAˆÈŠO‚ÉŽžŠÔ‚©Š|‚©‚é‚Ƃ̺‚ªA‚ ‚è‚Ü‚µ‚ÄEEE
>s‚Æ—ñ‚ɂ‚«‚Ü‚µ‚Ä‚ÍA•ÏX‰Â”\‚Å‚·B
>Sheet4‚̃f[ƒ^@--->@Sheet2‚̃Kƒ“ƒgƒ`ƒƒ[ƒgì¬ ‚Í‚¢B‚Å‚«‚ê‚ÎA‚»‚Ì•û‚ªA—Ç‚¢‚Å‚·B
‚±‚ê‚©‚çsheet4‚Ì•\‚ð‚µ•ÏX‚µ‚ÄAVƒXƒŒƒbƒh‚Å—§‚¿ã‚°‚Ü‚·B (yuri)
[ ˆê——(ÅVXV‡) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.