Excelで該当するデータに斜線を引き、それらを並び替えて一カ所にかためたい

質問

Excelに関する質問です(>_<)

今、Excelのブックファイルが2つありまして、1つめのファイルは『温泉地＆寺と神社ファイル.xls』、2つ目のファイルは『温泉地ファイル.xls』と、ファイル名を指定しています。
そして、1つめのファイルのA列には、

東福寺
飯豊温泉
神護寺
檜枝岐温泉
怒塚山温泉
湯桧曽温泉
花園神社
四季の湯
内船温泉
南禅寺
小砂温泉
廻栖野温泉
梅照院
・
・
・

と、温泉地と寺・神社の名前が、縦に並んでいます。
そしてB列には、

かのやばら園
岡山ブライダルストリート
一の宮公園
サンセットブリッジ内灘
ルチニャーノ
白鳥山森林公園
日比谷　松本楼
・
・
・

と、「恋人の聖地」と呼ばれる観光地が並んでおりまして、C列には「日本の灯台」、D列には「日本の渓谷」と、それぞれ観光地の情報が記載されています。
L列からは何もデータが入っていませんが、K列までは、データがびっしり埋まっています。

そして、二つ目のファイル『温泉地ファイル.xls』には、

飯豊温泉
檜枝岐温泉
怒塚山温泉
湯桧曽温泉
四季の湯
内船温泉
小砂温泉
廻栖野温泉
・
・
・

と、A1から温泉地の名前だけが、縦に並んでいます。

ここで、『温泉地＆寺と神社ファイル.xls』のA列のデータと、『温泉地ファイル.xls』のA列のデータを比較し、一致するデータ(温泉地)があれば、『温泉地＆寺と神社ファイル.xls』の方のA列の該当データに「／(罫線)」を引きたいのです。
さらに、罫線が引かれた温泉地のデータを、A列の一番上(A1)から縦に、連続した状態になるように並べ替えたいのです。

温泉地のデータと、寺と神社のデータが混在している現状をなんとかしたいです。
よろしくお願いします(>_<)

回答No.4

DOUGLAS_ · ベストアンサー

[回答番号：No.1・3] の DOUGLAS_ です。

　要らぬお節介ですが、もし並べ替えるのが『温泉地＆寺と神社ファイル.xls』の「A列のみ」ということであれば、前回答の「甲の L1 」という考え方ではなくて、
・Ａ列の前に列を挿入
・Ａ列に式を挿入
・Ａ・Ｂ列のみを並べ替え
・Ａ列を削除
という段取りでできます。

　ちなみに、[新しいマクロの記録] でその作業をして、コーディング を整理すると、ざっと下記のようなことになりますが、基本的に「考え方」をご理解いただければ幸いです。

Sub A列のみ並べ替え()
　'Ａ列の前に列を挿入
　　Range("A1").EntireColumn.Insert
　'挿入したＡ列に計算式を挿入
　　Range("A1:A" & Range("B1").End(xlDown).Row).FormulaR1C1 _
　　　= "=MATCH(RC[1],[温泉地ファイル.xls]Sheet1!C1,FALSE)"
　'Ａ・Ｂ列で並べ替え
　　Columns("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
　　　OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
　　　SortMethod:=xlStroke, DataOption1:=xlSortNormal
　'Ａ列内の「#N/A」を検索
　　Columns(1).Find(What:="#N/A", LookIn:=xlValues, LookAt:=xlPart, _
　　　SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
　　　MatchByte:=False, SearchFormat:=False).Offset(-1, 1).Select
　'Ｂ列の１行目から「#N/A」の前の行の値に罫線を引く
　　With Range(Range("B1"), Selection).Borders(xlDiagonalUp)
　　　.LineStyle = xlContinuous
　　　.Weight = xlThin
　　　.ColorIndex = xlAutomatic
　　End With
　'挿入したＡ列の削除
　　Columns(1).Delete
　　Range("A1").Select
End Sub

お礼

すごいです、DOUGLAS_さんのコード貼り付けて実行したら一発で出来ました！
マクロってすごいですね、ありがとうございます(>_<)

回答No.3

DOUGLAS_

[回答番号：No.1] の DOUGLAS_ です。

　失礼いたしました。
＞一致するデータ(温泉地)があれば、
＞『温泉地＆寺と神社ファイル.xls』の方のA列の該当データに「／(罫線)」を引きたい
でしたね。逆に考えておりました。

　そういうことでしたら、[回答番号：No.1] の 甲と乙を入れ替えて操作していただければお望みのことができるはずです。
　ただし、(２)の「B1」は「L1」に読み替えてください。

　つまり、
１）エクセルで、甲・乙、２つのブックを並べて表示します。
２）甲の L1 をクリックし、[F2] キーを押下し、「=MATCH(A1,」を入力します。
３）乙の A列 の列見出しをクリックします。
４）「,FALSE)」を入力して、[Enter] を押下します。
５）以上で、甲の L1 に
=MATCH(A1,[温泉地ファイル.xls]Sheet1!$A:$A,FALSE)
という式が入力され、甲A列のデータが乙A列に存在するときには、その「行番号」が、存在しないときには「#N/A」が返ります。
６）甲 L1 の式を下方向に必要分コピーします。
７）甲Ｌ列をキーにして並べ替えます。
８）甲Ｌ列に数字が表示されている分について、甲Ａ列の各セルに斜線を引きます。

　以上のように操作していただくと、添付画像のようになります（判りやすくするために罫線とＬ列の式の結果を赤色にしております）。

　ポイントは、「罫線を引いてから並べ替える」のではなくて、「並べ替えてから、一気に線を引く」ということです。

お礼

いつも返信が遅くてすいません(>_<)
そうですよね、並び替えればいいんですよね！
気づきませんでした・・・ありがとうございます(^_^;)

回答No.2

merlionXX

> L列からは何もデータが入っていませんが、K列までは、データがびっしり埋まっています。

この通りであれば、以下でどうでしょう？
Sheet名は両方ともSheet1としています。

Sub test04()
Dim myRng1 As Range, myRng2 As Range, c As Range, cc As Range

With Workbooks("温泉地＆寺と神社ファイル.xls")
.Activate
.Sheets("Sheet1").Activate
End With

With Workbooks("温泉地＆寺と神社ファイル.xls").Sheets("Sheet1")
Set myRng1 = .Range(.Range("A1"), .Range("A1").End(xlDown))
myRng1.Borders(xlDiagonalUp).LineStyle = xlNone
End With

With Workbooks("温泉地ファイル.xls").Sheets("Sheet1")
Set myRng2 = .Range(.Range("A1"), .Range("A1").End(xlDown))
End With

For Each c In myRng1
For Each cc In myRng2
If c.Value = cc.Value Then
c.Offset(0, 11).Value = 1
End If
Next cc
Next c

With Workbooks("温泉地＆寺と神社ファイル.xls").Sheets("Sheet1")
myRng1.Resize(, 12).Sort Key1:=Range("L1"), Order1:=xlDescending, Header:=xlNo, Orientation:=xlTopToBottom
With .Range(.Range("L1"), .Range("L1").End(xlDown)).Offset(0, -11).Borders(xlDiagonalUp)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Range(.Range("L1"), .Range("L1").End(xlDown)).ClearContents
End With

End Sub

お礼

merlionXXさんほんとすごいですね！
『マクロ・コンサルタント』として活躍できると思います、タダで教えていただいて本当にすいません(^_^;)
コピペした後「実行」をクリックすると、簡単に該当データに斜線を引くことができました、本当にありがとうございます(>_<)

回答No.1

DOUGLAS_

このようなブックの内容を比較する手段はいくつも方法がありますが、比較的簡単なものをお一つ。

　『温泉地＆寺と神社ファイル.xls』を「甲」、『温泉地ファイル.xls』を「乙」として説明いたします。

１）エクセルで、甲・乙、２つのブックを並べて表示します。
２）乙の B1 をクリックし、[F2] キーを押下し、「=MATCH(A1,」を入力します。
３）甲の A列 の列見出しをクリックします。
４）「,FALSE)」を入力して、[Enter] を押下します。
５）以上で、乙の B1 に
=MATCH(A1,'[温泉地＆寺と神社ファイル.xls]Sheet1'!$A:$A,FALSE)
という式が入力され、乙A列のデータが甲A列に存在するときには、その「行番号」が、存在しないときには「#N/A」が返ります。
６）乙Ｂ列で並べ替えます。
７）乙Ｂ列に数字が表示されている分について、Ａ列の各セルに斜線を引きます。

お礼

画像も添付していただき、本当にありがとうございます！
こういうやり方もあるのですね～勉強になります(>_<)

ただ「６）」と「７）」の後、いざ甲データの方に斜線を引く際、行番号に該当するセルに引いていくのですが、連番で2、3、4、5・・・等、セルが隣り合わせになっている場合は、斜線を引くのは簡単なのですが、実際は2、21、112、174と、乙データ(温泉地)に一致する甲データ(温泉地＆寺・神社) の行番号はバラバラで、一つ一つセルをアクティブにして斜線を引き、スクロールしてセルをアクティブにして斜線を引き、またスクロールしてセルをアクティブにして斜線を引く・・・と、結構時間がかかってしまうのです(ToT)

ですので、次のような処理を施したいのですが・・・。

乙ファイルのB列に並んだランダムの行番号(上から2、21、112、174・・・)は、そもそも甲ファイルのA列と、乙ファイルのA列に共通するデータ、すなわち「温泉地のデータ」を示しているので、その温泉地のデータの行番号に、自動的に罫線を引きたいのです。

つまり「乙ファイルのB列(例えばB1からB249まで行番号のデータがあるとします)の行番号が指す、甲ファイルのA列の行番号に罫線を引く」といった処理を施したいのです。それが可能であれば、作業も非常に合理的になるのですが・・・(^_^;)