{"id":52,"date":"2021-02-03T07:18:07","date_gmt":"2021-02-02T22:18:07","guid":{"rendered":"http:\/\/192.168.0.97\/bibouroku\/2021\/02\/03\/vlookup-3\/"},"modified":"2021-02-03T07:18:07","modified_gmt":"2021-02-02T22:18:07","slug":"vlookup-3","status":"publish","type":"post","link":"https:\/\/www.donijan.com\/bibouroku\/2021\/02\/vlookup-3.html","title":{"rendered":"VlookUp\u3092\u4f7f\u308f\u306a\u3044\u3067\u3000-\u305d\u306e3- \u30b3\u30de\u30f3\u30c9\u30dc\u30bf\u30f3"},"content":{"rendered":"

\u30a8\u30af\u30bb\u30eb\u3092\u4f7f\u3063\u3066\u3044\u3066\u30fb\u30fb\u826f\u304f\u4f7f\u3046\u306e\u304cVlookUp\u3067\u3059\u304c\u30fb\u30fb
\u3069\u3046\u3057\u3066\u3082\u8868\u8a08\u7b97\u95a2\u6570\u304c\u52d5\u304f\u306e\u3067\u9045\u3044!
\u30d5\u30a1\u30a4\u30eb\u81ea\u4f53\u304c\u91cd\u304f\u306a\u308b\u30fb\u30fb\u3068\u3044\u3046\u4e8b\u304c\u3042\u308b\u3002<\/p>\n

VlookUp\u3092\u4f7f\u308f\u306a\u3044\u3067\u5546\u54c1\u3092\u8868\u8a18\u3055\u305b\u308b\u70ba\u306b\u306f\u30fb\u30fb<\/p>\n

\u307e\u305a\u3001\u8868\u3092\u4f5c\u6210\u3059\u308b\u305f\u3081\u306e
\u30b3\u30de\u30f3\u30c9\u30dc\u30bf\u30f3\u304b\u3089\u4f5c\u6210\u3002<\/p>\n

<\/a><\/p>\n

<\/p>\n

\uff11\uff0e\u958b\u767a\u30ea\u30dc\u30f3\u304b\u3089<\/p>\n

<\/a><\/p>\n

\uff12\uff0e\u30c7\u30b6\u30a4\u30f3\u30e2\u30fc\u30c9\u3000\u2192\u3000\u633f\u5165<\/p>\n

<\/a><\/p>\n

\uff13\uff0e\uff21\uff43\uff54\uff49\uff56\uff45\uff38\u3000\u306e\u30b3\u30de\u30f3\u30c9\u30dc\u30bf\u30f3\u3092\u9078\u3073<\/p>\n

<\/a><\/p>\n

\uff14\uff0e\uff33\uff48\uff45\uff45\uff54\uff11\u306e\u3068\u3053\u308d\u3067\u30c9\u30e9\u30c3\u30b0<\/p>\n

<\/a><\/p>\n

\uff15\uff0e\u3059\u308b\u3068\u30b3\u30de\u30f3\u30c9\u30dc\u30bf\u30f3\u304c\u51fa\u6765\u308b\u306e\u3067\u30c0\u30d6\u30eb\u30af\u30ea\u30c3\u30af<\/p>\n

<\/a><\/p>\n

\uff16\uff0e\u3059\u308b\u3068\uff36\uff22\uff21\u304c\u958b\u304d\u3000\u30b3\u30de\u30f3\u30c9\u30dc\u30bf\u30f3\u3092\u30af\u30ea\u30c3\u30af\u3057\u305f\u5834\u5408\u306e\u30a4\u30d9\u30f3\u30c8\u304c\u8868\u8a18\u3055\u308c\u308b<\/p>\n

<\/a><\/p>\n

\uff17\uff0e\u305d\u306e\u4e2d\u306b\u30b3\u30fc\u30c9\u3092\u66f8\u304d\u8fbc\u307f\u3092\u3057\u307e\u3059\u304c\u30fb\u30fb\u305d\u306e\u524d\u306b\u30dc\u30bf\u30f3\u306e\u8868\u8a18\u3092\u5909\u66f4\u3059\u308b\u306e\u3067
\u3000\u3000\u5de6\u5074\u306b\u3042\u308b\u30d7\u30ed\u30d1\u30c6\u30a3\u304b\u3089\uff23\uff41\uff50\uff54\uff49\uff4f\uff4e\u3092\u9078\u3073\u3001\u4e0b\u56f3\u306e\u9752\u306e\u90e8\u5206\u3092\u304b\u304d\u304b\u3048\u308b\u3002<\/p>\n

<\/a><\/p>\n

\uff18\uff0e\u8868\u306e\u4f5c\u6210<\/p>\n

<\/a><\/p>\n

\uff19\uff0e\u3059\u308b\u3068\u30dc\u30bf\u30f3\u306e\u8868\u8a18\u304c\u5909\u308f\u308b\u3002<\/p>\n

<\/a><\/p>\n

\uff11\uff10\uff0e\u4eca\u5ea6\u306f\u3001\u6a19\u6e96\u30e2\u30b8\u30e5\u30fc\u30eb\u3092\u8ffd\u52a0\u3057\u307e\u3059\u3002<\/p>\n

<\/a><\/p>\n

\uff11\uff11\uff0e\u6a19\u6e96\u30e2\u30b8\u30e5\u30fc\u30eb\u306b\uff2d\uff4f\uff44\uff55\uff4c\uff45\uff11\u304c\u8ffd\u52a0\u3055\u308c\u308b\u3002<\/p>\n

<\/a><\/p>\n

\uff11\uff12\uff0e\u305d\u306e\u4e2d\u306b\u30b3\u30fc\u30c9\u3092\u66f8\u304d\u8fbc\u3080<\/p>\n

<\/a><\/p>\n

‘\u4ee5\u4e0b\u30b3\u30fc\u30c9
‘————————————————————————–<\/p>\n

Sub HyouSet() ‘\u8868\u306e\u4f5c\u6210\u3092\u3059\u308b
‘
‘ 20210202 Motohiro
‘<\/p>\n

‘\u5909\u6570\u306e\u5ba3\u8a00
Dim i As Long ‘\u884c\u306e\u30eb\u30fc\u30d7\u30ab\u30a6\u30f3\u30c8
Dim GyouSu As Long ‘\u884c\u6570
Dim LastRow As Long ‘\u5408\u8a08\u3092\u66f8\u3044\u3066\u3044\u308b\u6700\u7d42\u884c<\/p>\n

‘\u51fa\u529b\u884c\u6570\u3092\u30bb\u30c3\u30c8
GyouSu = InputBox(“\u51fa\u529b\u3059\u308b\u884c\u6570\u306f?”, “\u884c\u6570\u306e\u5165\u529b”, “6”)
‘\u51fa\u529b\u884c\u6570\u304b\u3089\u6700\u7d42\u884c\u3092\u30bb\u30c3\u30c8\u3059\u308b
LastRow = GyouSu + 6<\/p>\n

With Sheets(“Sheet1”)
‘\u884c\u5217\u306e\u5e45\u3092\u8a2d\u5b9a
Columns(“A:A”).ColumnWidth = 0.46
Rows(“1:1”).RowHeight = 5.25
Columns(“B:B”).ColumnWidth = 3.5
Columns(“C:C”).ColumnWidth = 15.5
Columns(“D:D”).ColumnWidth = 38.13
Columns(“J:J”).ColumnWidth = 18.25<\/p>\n

‘\u8a18\u8f09\u9805\u76ee\u3092\u30bb\u30c3\u30c8
Range(“B5”) = “No.”
Range(“C5”) = “\u30b3\u30fc\u30c9”
Range(“D5”) = “\u5546\u54c1\u540d”
Range(“E5”) = “\u5165\u6570”
Range(“F5”) = “c\/s\u6570”
Range(“G5”) = “\u30d0\u30e9\u6570”
Range(“H5”) = “\u6570\u91cf”
Range(“I5”) = “\u5358\u4fa1”
Range(“J5”) = “\u8a08”
Range(“K5”) = “\u7a0e\u533a\u5206”
Range(“D2”) = “\u7d0d\u54c1\u66f8”
Range(“C3”) = “\u682a\u5f0f\u4f1a\u793e \u6d41\u901a\u6280\u7814 \u5fa1\u4e2d”
Range(“I2”) = “\u7d0d\u54c1\u65e5”
Range(“J2”) = Date
Range(“I3”) = “\u682a\u5f0f\u4f1a\u793e \u571f\u65e5\u30b8\u30e3\u30f3\u30dc\u5e02”
.Cells(GyouSu + 6, 9) = “\u5408\u8a08\u91d1\u984d”<\/p>\n

‘\u884c\u756a\u53f7\u3092\u30bb\u30c3\u30c8
For i = 6 To GyouSu + 5
.Cells(i, 2) = i – 5
Next i
End With<\/p>\n

‘\u5404\u7a2e\u66f8\u5f0f\u8a2d\u5b9a
With Sheets(“Sheet1”).Range(Cells(2, 3), Cells(2, 5))
.HorizontalAlignment = xlCenter ‘\u6c34\u5e73\u4f4d\u7f6e\u3001\u4e2d\u592e\u63c3\u3048
.VerticalAlignment = xlCenter ‘\u5782\u76f4\u4f4d\u7f6e\u3001\u4e2d\u592e\u63c3\u3048
.ReadingOrder = xlContext
.Merge ‘\u30bb\u30eb\u306e\u7d50\u5408\u51e6\u7406
.Interior.Pattern = xlSolid ‘\u683c\u5b50
.Interior.PatternColorIndex = xlAutomatic
.Interior.ThemeColor = xlThemeColorDark1
.Interior.TintAndShade = -4.99893185216834E-02 ‘\u8272\u8a2d\u5b9a
.Interior.PatternTintAndShade = 0
End With<\/p>\n

With Sheets(“Sheet1”).Cells(3, 3)
.Font.Name = “\u6e38\u30b4\u30b7\u30c3\u30af” ‘\u30d5\u30a9\u30f3\u30c8
.Font.Size = 14 ‘\u30b5\u30a4\u30ba
.Font.Underline = xlUnderlineStyleSingle ‘\u4e0b\u7dda
End With<\/p>\n

With Sheets(“Sheet1”).Cells(3, 9)
.Font.Name = “\u6e38\u30b4\u30b7\u30c3\u30af”
.Font.Size = 12
.Font.Underline = xlUnderlineStyleSingle
End With<\/p>\n

With Sheets(“Sheet1”).Cells(2, 10)
.NumberFormatLocal = “[$-x-sysdate]dddd, mmmm dd, yyyy” ‘\u66f8\u5f0f\u8a2d\u5b9a \u5e74\u6708\u65e5\u3068\u3057\u3066\u304a\u304f
.Font.Underline = xlUnderlineStyleSingle ‘\u4e0b\u7dda
End With<\/p>\n

‘\u3053\u3053\u304b\u3089\u306f\u7f6b\u7dda\u8a2d\u5b9a \u683c\u5b50\u3001\u5916\u67a0\u4e2d\u592a\u7dda
With Sheets(“Sheet1”).Range(“B5:K” & LastRow – 1 & “,I” & LastRow & “,J” & LastRow & “,K” & LastRow)
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlThin<\/p>\n

.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
End With<\/p>\n

‘\u9805\u76ee\u540d\u306e\u30bb\u30f3\u30bf\u30fc\u914d\u7f6e\u8a2d\u5b9a
With Sheets(“Sheet1”).Range(“B5:K5”)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End Sub<\/p>\n

‘————————————————————————–
\u3000<\/p>\n

\uff11\uff13\uff0e\u66f8\u304d\u8fbc\u3093\u3060\u3089\u30c7\u30b6\u30a4\u30f3\u30e2\u30fc\u30c9\u3092\u7d42\u4e86\u3055\u305b\u3001\u30b3\u30de\u30f3\u30c9\u30dc\u30bf\u30f3\u3092\u30af\u30ea\u30c3\u30af\u3057\u3066\u307f\u308b\u3068
\u3000\u3000\u30a4\u30f3\u30d7\u30c3\u30c8\u30dc\u30c3\u30af\u30b9\u3067\u884c\u6570\u3092\u6c42\u3081\u3089\u308c\u3066\u304f\u308b\u306e\u3067\u3001\u884c\u6570\u3092\u5165\u308c\u308b\u3002<\/p>\n

<\/a><\/p>\n

\uff11\uff14\uff0e\uff16\u3092\u5165\u308c\u305f\u306e\u3067\uff16\u884c\u4f1d\u7968\u304c\u3067\u304d\u307e\u3057\u305f\u3002<\/p>\n

<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

\u30a8\u30af\u30bb\u30eb\u3092\u4f7f\u3063\u3066\u3044\u3066\u30fb\u30fb\u826f\u304f\u4f7f\u3046\u306e\u304cVlookUp\u3067\u3059\u304c\u30fb\u30fb\u3069\u3046\u3057\u3066\u3082\u8868\u8a08\u7b97\u95a2\u6570\u304c\u52d5\u304f\u306e\u3067\u9045\u3044!\u30d5\u30a1\u30a4\u30eb\u81ea\u4f53\u304c\u91cd\u304f\u306a\u308b\u30fb\u30fb\u3068\u3044\u3046\u4e8b\u304c\u3042\u308b\u3002 VlookUp\u3092\u4f7f\u308f\u306a\u3044\u3067\u5546\u54c1\u3092\u8868\u8a18\u3055\u305b\u308b\u70ba\u306b\u306f\u30fb\u30fb \u307e\u305a\u3001\u8868\u3092\u4f5c\u6210\u3059\u308b\u305f\u3081\u306e […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,17],"tags":[],"class_list":["post-52","post","type-post","status-publish","format-standard","hentry","category-excel","category-vba"],"_links":{"self":[{"href":"https:\/\/www.donijan.com\/bibouroku\/wp-json\/wp\/v2\/posts\/52"}],"collection":[{"href":"https:\/\/www.donijan.com\/bibouroku\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.donijan.com\/bibouroku\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.donijan.com\/bibouroku\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.donijan.com\/bibouroku\/wp-json\/wp\/v2\/comments?post=52"}],"version-history":[{"count":0,"href":"https:\/\/www.donijan.com\/bibouroku\/wp-json\/wp\/v2\/posts\/52\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.donijan.com\/bibouroku\/wp-json\/wp\/v2\/media?parent=52"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.donijan.com\/bibouroku\/wp-json\/wp\/v2\/categories?post=52"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.donijan.com\/bibouroku\/wp-json\/wp\/v2\/tags?post=52"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}