[ ‰‚ß‚Ä‚Ì•û‚Ö | ˆê——(ÅVXV‡) | ‘S•¶ŒŸõ | ‰ß‹ŽƒƒO ]
@
wŒŒ‰tŒŸ¸Œ‹‰ÊxiŠÒ—ï’¼‘Oj
Sheet1‚ÉŒŒ‰tŒŸ¸‚̊͈͂ł·B Sheet2‚É‚ÍŒŒ‰tŒŸ¸‚Ì‘ª’è’l‚Å‚·B ‘ª’è’l‚ªŠî€”͈͂𒴂¦‚½‚Æ‚«AƒGƒ‰[ƒƒbƒZ[ƒW‚ðo‚µA‚»‚ÌŠî€”ÍˆÍ ‚̉ºŒÀAãŒÀ‚Ì’l‚àƒƒbƒZ[ƒW‚É“ü‚ꂽ‚¢‚ÆŽv‚Á‚Ä‚¢‚Ü‚·B —Ⴆ‚ÎAAST‚ªŠî€”͈͂𒴂¦‚½ê‡AuAST‚Ì’l‚ªŠî€’l‚©‚çŠO‚ê‚Ä‚¢ ‚Ü‚·BŠî€’l‚Í8`40‚Å‚·v ‚·‚ׂẰ–Ú‚ª³í’l‚Å‚ ‚ê‚ÎuˆÈã‚ ‚è‚Ü‚¹‚ñ‚Å‚µ‚½v ƒ}ƒNƒƒ{ƒ^ƒ“iƒ`ƒFƒbƒNŠJŽnƒ{ƒ^ƒ“j‚ð‰Ÿ‚·‚ƃ}ƒNƒ‚ªŠJŽn‚µã‹L‚̃ ƒbƒZ[ƒW‚ªo‚éƒ}ƒNƒ‚ð‚²‹³Ž¦‚¨Šè‚¢’v‚µ‚Ü‚·B
Sheet1 A B C 1 ‰ºŒÀ@@@ãŒÀ 2 AST@@ 8 40 3 ALT 4 45 4 AL-P 100 340 5 ɤ-GTP 35
Sheet2 A B 1 ‘ª’è’l 2 AST 42 3 ALT 14 4 AL-P 350 5 ɤ-GTP 20
ƒ Žg—p ExcelFExcel2007AŽg—p OSFWindows7 „
‚¢‚Á‚½‚ñAƒAƒbƒvς݂̉ñ“š‚ð휂µ‚Ü‚·B
ÄŒf‚µ‚Ü‚·B ‚È‚¨A—¼•û‚̃V[ƒg‚Ì€–Ú‚ª•À‚ч‚àŠÜŽè–Ú‘S‚“¯‚¶‚È‚çAƒR[ƒh‚Í‚à‚Á‚ƃVƒ“ƒvƒ‹‚É‚È‚è‚Ü‚·B
Sub Sample() Dim c As Range Dim x As Long Dim s As String Dim z As Variant Dim r As Range Dim mn As Variant Dim mx As Variant
With Sheets("Sheet1") Set r = .Range("A1", .Range("A" & Rows.Count).End(xlUp)) End With
With Sheets("Sheet2") With .Range("A2", .Range("A" & Rows.Count).End(xlUp)) ReDim ans(1 To .Rows.Count) For Each c In .Columns(1).Cells x = x + 1 z = Application.Match(c, r, 0) If IsNumeric(z) Then mn = r.Cells(z).Offset(, 1).Value mx = r.Cells(z).Offset(, 2).Value If c.Offset(, 1).Value >= mn And c.Offset(, 1).Value <= mx Then s = " : ³í’l‚Å‚·" Else s = " : ˆÙí’l‚Å‚·B³í’l”ÍˆÍ‚Í " & mn & " ` " & mx & " ‚Å‚·B" End If Else s = " : ”»’è•s”\" End If ans(x) = c.Value & s Next End With End With
MsgBox "”»’茋‰Ê‚͈ȉº‚Ì’Ê‚è‚Å‚·" & vbLf & Join(ans, vbLf)
End Sub
iƒÀj 2016/12/29(–Ø) 19:10
ƒÀ‚³‚ñA‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B Šó–]‚»‚Ì‚à‚Ì‚Å‚·B‘f°‚炵‚¢‚Å‚·B
—¼•û‚̃V[ƒg‚Ì€–Ú‚Ì•À‚Ñ‚Í“¯‚¶‚Å•Ï‚í‚邱‚Æ‚Í‚ ‚è‚Ü‚¹‚ñB iŠÒ—ï’¼‘Oj 2016/12/29(–Ø) 20:11
‚à‚µA—¼•û‚̃V[ƒg‚Æ‚à‚É 10€–Ú‚Æ‚©A20€–Ú‚Æ‚©A‚Ü‚Á‚½‚“¯‚¶‚ÅA•À‚Ô‡‚à“¯‚¶‚È‚çB
Sub Sample2() Dim c As Range Dim x As Long Dim s As String Dim mn As Variant Dim mx As Variant
With Sheets("Sheet2") With .Range("A2", .Range("A" & Rows.Count).End(xlUp)) ReDim ans(1 To .Rows.Count) For Each c In .Columns(1).Cells x = x + 1 mn = Sheets("Sheet1").Range(c.Address).Offset(, 1).Value mx = Sheets("Sheet1").Range(c.Address).Offset(, 2).Value If c.Offset(, 1).Value >= mn And c.Offset(, 1).Value <= mx Then s = " : ³í’l‚Å‚·" Else s = " : ˆÙí’l‚Å‚·B³í’l”ÍˆÍ‚Í " & mn & " ` " & mx & " ‚Å‚·B" End If ans(x) = c.Value & s Next End With End With
MsgBox "”»’茋‰Ê‚͈ȉº‚Ì’Ê‚è‚Å‚·" & vbLf & Join(ans, vbLf)
End Sub
iƒÀj 2016/12/29(–Ø) 20:31
ƒÀ‚³‚ñ•×‹‚É‚È‚è‚Ü‚·B ”N––‚¨–Z‚µ‚¢’†A‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚µ‚½B iŠÒ—ï’¼‘Oj 2016/12/29(–Ø) 21:17
Dim r As Range, c As Range, flg As Boolean For Each c In Sheets("Sheet2").Range("B:B").SpecialCells(2, 1) Set r = Sheets("Sheet1").Cells.Find(What:=c.Offset(, -1).Value, LookAt:=xlWhole) If Not r Is Nothing Then If Val(c.Value) - Val(r.Offset(, 1).Value) < 0 Or IIf(r.Offset(, 2).Value = "", Val(c.Value), r.Offset(, 2).Value) - Val(c.Value) < 0 Then MsgBox c.Offset(, -1).Value & "‚Ì’l‚ªŠî€’l‚©‚çŠO‚ê‚Ä‚¢‚Ü‚·BŠî€’l‚Í" & r.Offset(, 1).Value & "`" & r.Offset(, 2).Value & "‚Å‚·B" flg = True End If End If Next c If Not flg Then MsgBox "ˆÙí‚ ‚è‚Ü‚¹‚ñ‚Å‚µ‚½B" End Sub immj 2016/12/30(‹à) 10:08
ƒÀ‚³‚ñAmm‚³‚ñ‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B ŽQl‚É‚³‚¹‚Ä‚¢‚½‚¾‚«‚Ü‚·B
‚³‚ç‚É”N—î‚ÌðŒ‚ð‘‚₵‚ÄAƒŒƒCƒAƒEƒg‚à•ÏX‚µ‚Ä‚ÌŽ¿–â—Ç‚¢‚Å‚µ ‚傤 ‚©H V‹K‚ɃAƒbƒv‚µ‚È‚¨‚µ‚½•û‚ª‚æ‚¢‚Å‚µ‚傤‚©H iŠÒ—ï’¼‘Oj 2016/12/30(‹à) 18:08
Œp‘±‚Å‚àV‹K‚Å‚à‚¢‚¢‚Å‚·‚æB
iƒÀj 2016/12/30(‹à) 18:09
ƒÀ‚³‚ñA’ljÁŽ¿–âA‰õ‚Žó‚¯‚Ä‚¢‚½‚¾‚«Š´ŽÓ‚Å‚·B‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚·B
Sheet2‚ÌB1‚É”N—î‚ðASheet2‚ÌAB—ñ‚É‚Í”N—î”͈͂ð•\‚µ‚Ä‚¢‚Ü‚·B ‰º‹L•\‚Å‚ÍA”N—18΂Ȃ̂ÅASheet1‚Ì5s–Ú‚ðŒ©‚És‚«A‘O‰ñ“¯—l‚É ‘ª’è’l‚ªŠî€”͈͂𒴂¦‚½‚Æ‚«AƒGƒ‰[ƒƒbƒZ[ƒW‚ðo‚µA‚»‚ÌŠî€”ÍˆÍ ‚̉ºŒÀAãŒÀ‚Ì’l‚àƒƒbƒZ[ƒW‚É“ü‚ꂽ‚¢‚Å‚·B \‚µ–ó‚ ‚è‚Ü‚¹‚ñ‚ªA‚²‹³Ž¦‚¨Šè‚¢’v‚µ‚Ü‚·B
Sheet1
A B C D E F G@@H@@ I J 1 ”N—î AST ALT@@ AL-P@ ɤ-GTP@ 2 @@@ ‰ºŒÀ ãŒÀ ‰ºŒÀ ãŒÀ ‰ºŒÀ ãŒÀ ‰ºŒÀ ãŒÀ 3@5@@10 8@@ 40 4 45 100 340 35 4 11 15 9 42 5 47 120 350 36 5 16 20 12 45 7 50 130 360 37 6 21 25 15 50 10 53 140 370 40
Sheet2 A@@@ B 1 ”N—î 18 2 AST 7 3 ALT 14 4 AL-P 1 5 ɤ-GTP 20@
iŠÒ—ï’¼‘Oj 2016/12/30(‹à) 19:02
ƒÀ‚³‚ñ‚̉ñ“š‚Ȃ͉½‚à•s–ž‚Í‚È‚AŽv‚¢‚Ç‚¨‚è‚Ì‚à‚Ì‚ÅA–ž‘«‚µ‚Ä‚¢‚Ü‚·B ¡‰ñ‚Í”N—î‚ð‰Á‚¦‚½‚©‚Á‚½‚Ì‚ÅASheet2‚Í‘O‰ñ‚Ì€–Ú‚ªˆês‰º‚ɃYƒŒASheet1‚Å‚Í”N—î”͈͂ð’ljÁ‚µ‚悤 ‚ÆŽv‚¤‚ÆA ‰¡‘‚«‚Ì‚Ù‚¤‚ªA‚í‚©‚è‚â‚·‚¢‚ÆŠ´‚¶‚½‚Ì‚Å•ÏX‚³‚¹‚Ä‚¢‚½‚¾‚«‚Ü‚µ‚½B
iŠÒ—ï’¼‘Oj 2016/12/30(‹à) 19:50
ƒR[ƒhA‚¢‚»‚¢‚Å‘‚¢‚½‚Ì‚ÅA‚à‚¤‚µ®—‚à‚Å‚«‚é‚©‚ÆŽv‚¢‚Ü‚·‚ªB
ˆÈ‰º‚Ì‘O’ñ‚Å‚·B
ESheet1 ‚Ì”N—͸‡‚É‚È‚Á‚Ä‚¢‚éB ESheet1 ‚Ì‚Ps–Ú‚ÌŒŸ¸€–Ú‚Ì”‚Æ•À‚Ñ‚ÍASheet2‚ÌA—ñ‚Ì‚»‚ê‚ÆAŠ®‘S‚Ɉê’v‚·‚éB Emm‚³‚ñ‚©‚çA‰ºŒÀ‚Ì‚ÝÝ’è‚̑Ήžˆ—‚ª’ñŽ¦‚³‚ê‚Ü‚µ‚½‚ªAŒŸ¸€–Ú‚Ì«ŠiãAãŒÀ‚ÌŒ©‚Í‚ ‚Á‚Ä‚à‰ºŒÀ‚Ì‚Ý‚Í‚ ‚肦‚È‚¢B
Sub Sample3() Dim myAge As Long Dim mnAge As Long Dim mxAge As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim myRow As Long Dim i As Long Dim j As Long Dim x As Long Dim mn As Variant Dim mx As Variant Dim myData As Variant Dim s As String
Set sh1 = Sheets("Sheet1") Set sh2 = Sheets("Sheet2")
myAge = sh2.Range("B1").Value mnAge = sh1.Range("A3").Value mxAge = sh1.Range("B" & Rows.Count).End(xlUp).Value ReDim ans(1 To sh2.Range("A" & Rows.Count).End(xlUp).Row - 1)
If myAge < mnAge Or myAge > mxAge Then MsgBox myAge & "˂̔팱ŽÒ‚ɑ΂·‚éî•ñ‚Í‚ ‚è‚Ü‚¹‚ñ" Exit Sub End If 'Sheet1‚Ì”N—“–‚Ä‚Í‚Ü‚éSheet2‚Ìs‚ðŽæ“¾ myRow = WorksheetFunction.Match(myAge, sh1.Range("A3", sh1.Range("A" & Rows.Count).End(xlUp)), 1) + 2 i = 2 'SHeet2‚̃f[ƒ^s For j = 3 To sh1.Cells(1, Columns.Count).End(xlToLeft).Column Step 2 'Sheet1 ‚ÌŒŸ¸€–Ú x = x + 1 mn = sh1.Cells(myRow, j).Value mx = sh1.Cells(myRow, j + 1).Value myData = sh2.Cells(i, "B").Value If myData >= mn And myData <= mx Then s = " : ³í’l‚Å‚·" Else s = " : ˆÙí’l‚Å‚·B³í’l”ÍˆÍ‚Í " & mn & " ` " & mx & " ‚Å‚·B" End If ans(x) = sh2.Cells(i, "A").Value & "(" & myData & ")" & s i = i + 1 Next
MsgBox "”»’茋‰Ê‚͈ȉº‚Ì’Ê‚è‚Å‚·" & vbLf & Join(ans, vbLf)
End Sub
iƒÀj 2016/12/30(‹à) 20:14
ƒÀ‚³‚ñA‚·‚ׂĊm”F‚µA‚¤‚Ü‚‚¢‚«‚Ü‚µ‚½B ‚¢‚½‚ç‚È‚©‚Á‚½“_A‚¨˜l‚Ñ’v‚µ‚Ü‚·B ‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚µ‚½B
iŠÒ—ï’¼‘Oj 2016/12/30(‹à) 21:42
ƒÀ‚³‚ñA•Ï”‚ÉmnAge mxAge‚Æ‚©Amn mx‚Æ‘‚©‚ê‚Ä‚¢‚Ü‚·‚ªAmin‚Æ‚Í‘‚©‚¸mn Amax‚Ímx‚Æ‘‚¢‚Ä ‚¨‚ç‚ê‚é‚͉̂½ŒÌ‚Å‚·‚©H‰½‚©Œˆ‚܂莖‚ª‚ ‚é‚Ì‚Å‚µ‚傤‚©H
iŠÒ—ï’¼‘Oj 2017/01/07(“y) 00:08
„‘ª’è’l‚ªŠî€”͈͂𒴂¦‚½‚Æ‚«AƒGƒ‰[ƒƒbƒZ[ƒW‚ðo‚µA‚»‚̊͈Í
„ ‚̉ºŒÀAãŒÀ‚Ì’l‚àƒƒbƒZ[ƒW‚É“ü‚ꂽ‚¢‚ÆŽv‚Á‚Ä‚¢‚Ü‚·B
ƒƒbƒZ[ƒWƒ{ƒbƒNƒX‚¾‚ÆAƒ}ƒNƒ‚ðŽÀs‚µ‚½‚Æ‚«‚µ‚©A
”»’茋‰Ê‚ª‰ð‚ç‚È‚‚È‚¢‚Å‚·‚©H
ƒZƒ‹‚ÉŒ‹‰Ê‚ð‘‚«o‚µ‚½‚èA
Šî€”͈͂©‚ç‚Í‚Ýo‚½”’l‚̃Zƒ‹‚ÉF‚ð•t‚¯‚é‚Ȃ肵‚½‚çA
‚¢‚©‚ª‚Å‚µ‚傤‚©H
i‚Ü‚Á‚‚í‚ñj 2017/01/07(“y) 08:28
>>‰½‚©Œˆ‚܂莖‚ª‚ ‚é‚Ì‚Å‚µ‚傤‚©H
maxAge ‚â@minAge ‚ÍA‚Ü‚Á‚½‚–â‘è‚ ‚è‚Ü‚¹‚ñB@•Ê“rAmx ‚â mn ‚Æ‚¢‚¤•Ï”–¼‚ð‹K’肵‚Ü‚µ‚½‚Ì‚Å ‚»‚ê‚Ƈ‚킹‚邽‚ß‚É mxAge,mnAge ‚É‚µ‚Ä‚ ‚è‚Ü‚·B
‚ÅAmax ‚â minB ‚±‚¤‘‚¢‚Ä‚àAŽÀsãAŽxá‚Í‚ ‚è‚Ü‚¹‚ñB
‚Å‚àA‚±‚ê‚ç‚Í@ƒV[ƒgŠÖ”‚ÌŠÖ”–¼‚Å‚·‚ËB ˆÈ‰º‚ðV‹KƒuƒbƒN‚Ì•W€ƒ‚ƒWƒ…[ƒ‹‚É“\‚è•t‚¯‚Ä‚Ý‚Ä‚‚¾‚³‚¢B
Sub Test() Dim max As Long
MsgBox WorksheetFunction.Max(Range("A1:A10"))
End Sub
“\‚è•t‚¯‚½Œ‹‰ÊA‚æ`‚Œ©‚Ä‚‚¾‚³‚¢B‚Ç‚±‚©A•Ï‰»‚µ‚Ä‚Ü‚·‚Å‚µ‚åH Max ‚Æ‚È‚é‚ׂ«‚Ƃ낱A max ‚É‚È‚Á‚¿‚á‚Á‚Ä‚Ü‚·‚ËB
‚æ‚ row ‚È‚ñ‚Ä•Ï”‚ð‘‚¢‚Ä‚¢‚él‚ªA‚±‚¤‚È‚Á‚½II@‚Æ‘å‘›‚¬‚µ‚ÄAƒGƒNƒZƒ‹‚ðăCƒ“ƒXƒg[ƒ‹‚µ‚È‚«‚á‚¢‚¯‚È‚¢‚©II ‚Æ‚¢‚Á‚½Ž¿–₪‚ł邱‚Æ‚à‚ ‚è‚Ü‚·B
ƒGƒNƒZƒ‹‚âVBA ‚Å’è‚ß‚ç‚ꂽŠÖ”–¼‚⃃\ƒbƒh–¼AƒvƒƒpƒeƒB–¼“™‚ÍA•Ï”‚Æ‚µ‚ÄŽg‚í‚È‚¢‚Ù‚¤‚ª ‚æ‚낵‚¢‚©‚ÆŽv‚¢‚Ü‚·‚ËB
iƒÀj 2017/01/07(“y) 09:04
ƒÀ‚³‚ñA‚æ‚‚í‚©‚è‚Ü‚µ‚½B ‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚µ‚½B iŠÒ—ï’¼‘Oj 2017/01/09(ŒŽ) 22:58
ƒÀ‚³‚ñA“ñTŠÔ‘O‚ɃAƒbƒv‚µ‚½‚à‚Ì‚Å\‚µ–ó‚ ‚è‚Ü‚¹‚ñ‚ªAŽw“±‚æ‚낵‚‚¨Šè‚¢‚µ‚Ü‚·B ³í’l‚ÌŽž‚͉½‚à•\Ž¦‚¹‚¸AˆÙí’l‚ÌŽž‚¾‚¯A‘O‹L‚ƃƒbƒZ[ƒW‚ªo‚é‚悤‚É‚µ‚½‚¢‚Å‚·B AST‚ªŠî€”͈͂𒴂¦‚½ê‡AuAST‚Ì’l‚ªŠî€’l‚©‚çŠO‚ê‚Ä‚¢‚Ü‚·BŠî€’l‚Í8`40‚Å‚·v ‚æ‚낵‚‚¨Šè‚¢‚µ‚Ü‚·B
iŠÒ—ï’¼‘Oj 2017/01/11(…) 20:05
Œ»ÝA³íAˆÙí@‚Æ‚à‚É@”z—ñ‚ÉŠi”[‚µ‚Ä‚¢‚Ü‚·‚ªA‚±‚ê‚ð@ˆÙí’l‚Ìꇂ̂݊i”[‚·‚é‚悤‚É•ÏX‚·‚ê‚΂¢‚¢‚Ì‚Å‚·‚æB
Sub Sample3() Dim myAge As Long Dim mnAge As Long Dim mxAge As Long Dim sh1 As Worksheet Dim sh2 As Worksheet Dim myRow As Long Dim i As Long Dim j As Long Dim x As Long Dim mn As Variant Dim mx As Variant Dim myData As Variant
Set sh1 = Sheets("Sheet1") Set sh2 = Sheets("Sheet2")
myAge = sh2.Range("B1").Value mnAge = sh1.Range("A3").Value mxAge = sh1.Range("B" & Rows.Count).End(xlUp).Value ReDim ans(1 To sh2.Range("A" & Rows.Count).End(xlUp).Row - 1)
If myAge < mnAge Or myAge > mxAge Then MsgBox myAge & "˂̔팱ŽÒ‚ɑ΂·‚éî•ñ‚Í‚ ‚è‚Ü‚¹‚ñ" Exit Sub End If 'Sheet1‚Ì”N—“–‚Ä‚Í‚Ü‚éSheet2‚Ìs‚ðŽæ“¾ myRow = WorksheetFunction.Match(myAge, sh1.Range("A3", sh1.Range("A" & Rows.Count).End(xlUp)), 1) + 2 i = 2 'SHeet2‚̃f[ƒ^s For j = 3 To sh1.Cells(1, Columns.Count).End(xlToLeft).Column Step 2 'Sheet1 ‚ÌŒŸ¸€–Ú mn = sh1.Cells(myRow, j).Value mx = sh1.Cells(myRow, j + 1).Value myData = sh2.Cells(i, "B").Value If myData < mn Or myData > mx Then x = x + 1 ans(x) = sh2.Cells(i, "A").Value & "‚Ì’l‚ªŠî€’l‚©‚ç‚Í‚¸‚ê‚Ä‚¢‚Ü‚·BŠî€’l‚Í" & mn & "`" & mx & "‚Å‚·B" End If i = i + 1 Next
If x = 0 Then MsgBox "”»’茋‰Ê‚Í‚·‚ׂijí’l‚͈͓̔à‚Å‚µ‚½B" Else ReDim Preserve ans(1 To x) MsgBox "”»’茋‰Ê‚͈ȉº‚Ì’Ê‚è‚Å‚·" & vbLf & Join(ans, vbLf) End If
End Sub
iƒÀj 2017/01/11(…) 20:50
ƒÀ‚³‚ñA Œ»ÝA³íAˆÙí@‚Æ‚à‚É@”z—ñ‚ÉŠi”[‚µ‚Ä‚¢‚Ü‚·‚ªA‚±‚ê‚ð@ ˆÙí’l‚Ìꇂ̂݊i”[‚·‚é‚悤‚É•ÏX‚·‚ê‚΂¢‚¢B‚í‚©‚è‚Ü‚µ‚½B ‚ ‚肪‚Æ‚¤‚²‚´‚¢‚Ü‚µ‚½B iŠÒ—ï’¼‘Oj 2017/01/11(…) 21:32
[ ˆê——(ÅVXV‡) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.