HOME 検索 索引 もくじ 関数目次 前ページへ 次ページへ
タイトル欄のアドレスは、エクセルファンクラブの該当する頁へのリンク先です。
詳しい事は、こちらのお願いをご覧になってください。
21 項目を見出しにして、見出しを項目として抽出するには?
22 複数のシートから抽出・集計するには
23 同じフォルダ内の別の複数のファイルから抽出・集計するには
24 縦長の表を、行数を指定して横の列に抽出するには
25 セルに入力された名前のシートに、行ごと抽出するには?
26 下2ケタの数字で並べ替えるには
27 セル内の文字列を逆順に並び替えるには?(
「あいうえお」 を 「おえういあ」 に )
28 重複しない値を抽出するには?
29 一行のデータを項目ごとに列に振り分けるには
30 検索項目を入力して、同じ値があればそのセルを選択し、表示するには?
31 検索項目を入力して、同じ値があればその行を検索行の下に挿入
32 組み合わせを表示するには(候補から2項目を組合せと合計)
33 組合せを表示するには(B列の候補から2・3・・・5項目の組合せと平均)
34 2列の内容によって、他のシートにデータをコピーするには?
35 複数の指定の値に合致するデータを抽出するには?
36 2つのブックを比べて、異なるデータを3つ目のブックに書き出すには?
37 選択範囲内で指定した値と同じ値で、3番目に該当するものを探すには?
38 オートフィルタ切り替えマクロ
39
40
21 | 項目を見出しにして、見出しを項目として抽出するには? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
***** 【Sheet1】 ***** A B C D E 1 クラブ名 生徒名 生徒名 生徒名 生徒名 2 野球部 一太郎 花子 黒子 二太郎 3 テニス部 三太郎 花子 4 水泳部 二太郎 黒子 5 書道部 一太郎 三太郎 6 音楽部 花子 黒子 *****
【Sheet2】 ***** |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
上記の【Sheet1】のデータを、【Sheet2】に抽出します。 シート名は Sheet1 とし、Sheet2 は存在するが使われていないものとします。 元データにはA1セルからデータが書かれていて、 A列にはクラブ名、1行目には「生徒名」と書いた見出しがあるものとします。 データは最下行まで空行がないこと、生徒名は左に詰めて書かれていることとします。 Sub Narabekae() Dim n As Long, m As Integer, k As Long, j As Integer Dim res As String, INI As String, Nam As String Dim WS2 As Worksheet res = MsgBox("Sheet2 の値をクリアしてから転記します。", vbOKCancel + vbInformation, "並べ替え") If res = vbCancel Then Exit Sub Application.ScreenUpdating = False Set WS2 = Worksheets("Sheet2") WS2.Cells.ClearContents ' 生徒名の転記 WS2.Cells(1, 1).Value = "生徒名" With Worksheets("Sheet1") k = 2 n = 2 Do Until .Cells(n, 1).Value = "" m = 2 Do Until .Cells(n, m).Value = "" WS2.Cells(k, 1).Value = .Cells(n, m).Value k = k + 1 m = m + 1 Loop n = n + 1 Loop With WS2 .Select ' Sort .Range("A2").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _ :=xlPinYin ' 重複削除 INI = .Cells(2, 1).Value ' 生徒名の初期値 n = 3 Do Until .Cells(n, 1).Value = "" If .Cells(n, 1).Value = INI Then Rows(n).Delete shift:=xlUp Else INI = .Cells(n, 1).Value ' 初期値の更新 n = n + 1 End If Loop End With ' 所属クラブ名の転記 n = 2 Do Until .Cells(n, 1).Value = "" INI = .Cells(n, 1).Value ' Sheet1 からクラブ名を取得 m = 2 Do Until .Cells(n, m).Value = "" Nam = .Cells(n, m).Value ' Sheet1 から生徒名を取得 k = 2 Do Until WS2.Cells(k, 1).Value = "" If WS2.Cells(k, 1).Value = Nam Then j = 2 Do If WS2.Cells(k, j).Value = "" Then WS2.Cells(k, j).Value = INI If WS2.Cells(1, j).Value = "" Then WS2.Cells(1, j).Value = "クラブ名" Exit Do Else j = j + 1 End If Loop End If k = k + 1 Loop m = m + 1 Loop n = n + 1 Loop .Cells(1, 1).Value = "生徒名" End With Set WS2 = Nothing Application.ScreenUpdating = True MsgBox "終わりました。" End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
22 | 複数のシートから抽出・集計するには http://cgi.fuji.ne.jp/~fj2094/cgi-bin1/wwwlng.cgi?print+200107/01070050.txt 「特殊な分類集計の方法だと思うんですが」 【編集ラウンジ】 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
*** Sheet1 *** A B C 1 品番 サイズ 数量 2 AW-1 10X50 5 3 AW-3 10X60 2 4 AW-9 50X60 9 *** Sheet2 *** A B C 1 品番 サイズ 数量 2 AW-3 10X60 3 3 AW-5 20X90 5 4 AW-9 50X60 1 *** Sheet3 *** A B C 1 品番 サイズ 数量 2 AW-5 20X90 9 3 AW-7 10X80 2 |
*** Sheet4 *** A B C 1 品番 サイズ 数量 2 AW-1 10X50 5 3 AW-3 10X60 5 4 AW-5 20X90 14 5 AW-7 10X80 2 6 AW-9 50X60 10 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
上記の、Sheet1からSheet3を、Sheet4に集計します。 Sub test() Dim WS(1 To 4) As Worksheet Dim LastRow(1 To 4) As Long Dim i As Long 'オブジェクト変数を設定 Set WS(1) = Worksheets("Sheet1") Set WS(2) = Worksheets("Sheet2") Set WS(3) = Worksheets("Sheet3") Set WS(4) = Worksheets("Sheet4") Application.ScreenUpdating = False With WS(4) .Select 'Sheet4をクリア→見出し記入 .Cells.ClearContents .Cells(1, 1) = "品番" .Cells(1, 2) = "サイズ" .Cells(1, 3) = "数量" '各シートの最終行を取得 LastRow(1) = WS(1).Range("A65536").End(xlUp).Row LastRow(2) = WS(2).Range("A65536").End(xlUp).Row LastRow(3) = WS(3).Range("A65536").End(xlUp).Row '各シートの内容をSheet4に貼り付ける For i = 1 To 3 LastRow(4) = .Range("A65536").End(xlUp).Row + 1 WS(i).Rows("2:" & LastRow(i)).Copy .Cells(LastRow(4), 1) Next i 'Sheet4の最終行を取得 LastRow(4) = .Range("A65536").End(xlUp).Row 'A列を昇順にソート .Range("A1").Sort Key1:=.Range("A1"), Header:=xlYes '同じ品番の数量を集計する For i = LastRow(4) To 3 Step -1 If .Cells(i, 1) = .Cells(i - 1, 1) Then .Cells(i - 1, 3) = .Cells(i - 1, 3) + .Cells(i, 3) .Rows(i).ClearContents End If Next i '空白行削除 .Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete End With 'オブジェクト変数を初期化 For i = 1 To 4 Set WS(i) = Nothing Next i Application.ScreenUpdating = True End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
23 | 同じフォルダ内の別の複数のファイルから抽出・集計するには http://cgi.fuji.ne.jp/~fj2094/cgi-bin1/wwwlng.cgi?print+200107/01070050.txt 「特殊な分類集計の方法だと思うんですが」 【編集ラウンジ】 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
data1.xls *** Sheet1 A B C 1 品番 サイズ 数量 2 AW-1 10X50 5 3 AW-3 10X60 2 4 AW-9 50X60 9 data2.xls *** Sheet2 A B C 1 品番 サイズ 数量 2 AW-3 10X60 3 3 AW-5 20X90 5 4 AW-9 50X60 1 data3.xls *** Sheet3 A B C 1 品番 サイズ 数量 2 AW-5 20X90 9 3 AW-7 10X80 2 |
*** total.xls *** Sheet4 *** A B C 1 品番 サイズ 数量 2 AW-1 10X50 5 3 AW-3 10X60 5 4 AW-5 20X90 14 5 AW-7 10X80 2 6 AW-9 50X60 10 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
上記のような、別々のファイルのAB列に記入された内容を、行数を指定してA〜F列に抽出します。 ファイルは全て同じフォルダに入っているものとし、そのフォルダにはそれ以外のファイルは ないものとします。フォルダの場所は、どこにあっても問題ありません。 Sub test() |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
24 | 縦長の表を、行数を指定して横の列に抽出するには http://cgi.fuji.ne.jp/~fj2094/cgi-bin1/wwwlng.cgi?print+200208/02080079.txt 「1万個のデータを1ページに50行×2列で100個ずつ100ページに表示するには?」 【編集コーナー】 http://cgi.fuji.ne.jp/~fj2094/cgi-bin1/wwwlng.cgi?print+200107/01070039.txt 「縦長の表を折り返して1表に印刷&1ページに印刷できる行を指定したい」 【編集コーナー】 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
*** Sheet1 *** A B 1 101 あああ 2 102 いいい 3 103 ううう 4 104 えええ 5 105 おおお 6 106 かかか |
*** Sheet1 *** A B C D 1 101 あああ 103 ううう 2 102 いいい 104 えええ 3 105 おおお 107 ききき 4 106 かかか 108 くくく 5 109 けけけ 111 さささ 6 110 こここ 112 ししし |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
マクロを使わない方法で、上記左のデータを右のように2行づつ、CD列に移動します。 1. AB列をCD列にコピーします。 2. C1〜D2までのセルを選択し【右クリック】【削除】【上方向にシフト】します。 3. A列を選択し、【挿入】【列】で1列作業列を設けます。 4. 挿入したA列に、A1〜A2に「1」、A3〜A4に「2」をオートフィルを使って入力します。 5. A1〜A4を選択し、A4の右下にカーソルを当てて、Ctrlキーを押しながらA列の最後のデータまでドラッグします。 6. A〜E列を選択し、【データ】【並び替え】で【列A】を昇順に並び替えます。 7. 余分な行とA列を削除して、完成! |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
*** Sheet1 *** A B 1 101 あああ 2 102 いいい 3 103 ううう 4 104 えええ 5 105 おおお 6 106 かかか |
*** Sheet2 *** A B C D E F 1 101 あああ 103 ううう 105 おおお 2 102 いいい 104 えええ 106 かかか |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
上記のような、AB列に記入された内容を、行数を指定してA〜F列に抽出します。 Sub test() Dim LastRow As Long, gyou1 As Long, gyou2 As Variant Dim WS1 As Worksheet, WS2 As Worksheet Dim i As Long, j As Long, k As Long Set WS1 = Worksheets("sheet1") Set WS2 = Worksheets("sheet2") gyou1 = 0 gyou2 = Application.InputBox(Prompt:="1ページ1列分の行数を入力してください", Type:=1) If Not VarType(gyou2) = vbBoolean Then Application.DisplayAlerts = False WS2.Activate Cells.ClearContents With WS1 LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row i = 1 j = 1 Do gyou1 = gyou1 + gyou2 For k = j To gyou1 Cells(k, 1) = .Cells(i, 1) Cells(k, 2) = .Cells(i, 2) i = i + 1 If i > LastRow Then Cells(k, 1).Select Exit Do End If Next For k = j To gyou1 Cells(k, 3) = .Cells(i, 1) Cells(k, 4) = .Cells(i, 2) i = i + 1 If i > LastRow Then Cells(k, 1).Select Exit Do End If Next For k = j To gyou1 Cells(k, 5) = .Cells(i, 1) Cells(k, 6) = .Cells(i, 2) i = i + 1 If i > LastRow Then Cells(k, 1).Select Exit Do End If Next Cells(k, 1).Select j = j + gyou2 gyou2 = Application.InputBox(Prompt:="次頁の1列分の行数を入力してください", _ Default:=gyou2, Type:=1) If VarType(gyou2) = vbBoolean Then Exit Do If i > LastRow Then Exit Do Loop End With Application.DisplayAlerts = True MsgBox "終わりました" End If End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
25 | セルに入力された名前のシートに、行ごと抽出するには? http://www.ae.wakwak.com/~efc21/cgi-bin/wwwlng.cgi?print+200108/01080384.txt 「次のデータまで空白セルを上のデータと同じにするには?」 【VBAラウンジ】 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
*** Sheet1 *** A B C 1 種類 名前 年齢 2 犬 ポチ 1歳 3 べる 2歳 4 猫 タイガ 5歳 5 ピンコ 3歳 6 鳥 ピヨ 2歳 |
*** 犬 ***
*** 猫 ***
*** 鳥 *** A B A B A B 1 名前 年齢 1 名前 年齢 1 名前 年齢 2 ポチ 1歳 2 タイガ 5歳 2 ピヨ 2歳 3 べる 2歳 3 ピンコ 3歳 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sheet1をアクティブにして実行してください。見出行はあらかじめ記入されているものとします。 Sub Test() Dim my種類 As String Dim C As Range my種類 = Range("A2").Value For Each C In Range("B2", Range("B65536").End(xlUp)) If C.Offset(0, -1).Value <> "" Then my種類 = C.Offset(0, -1).Value C.Resize(1, 2).Copy Worksheets(my種類).Range("A65536").End(xlUp).Offset(1) Next End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
*** Sheet1 *** A B C 1 種類 名前 年齢 2 犬 ポチ 1歳 3 べる 2歳 4 猫 タイガ 5歳 5 ピンコ 3歳 6 鳥 ピヨ 2歳 |
*** 犬 *** A B C 1 種類 名前 年齢 2 犬 ポチ 1歳 3 べる 2歳 *** 猫 *** A B C 1 種類 名前 年齢 2 猫 タイガ 5歳 3 ピンコ 3歳 *** 鳥 *** A B C 1 種類 名前 年齢 2 鳥 ピヨ 2歳 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sheet1以外のシートはいったんクリアしてから抽出しています。 Sub miko_test() Dim i As Long, N As String, WS As Worksheet Worksheets("Sheet1").Select For Each WS In ThisWorkbook.Worksheets If WS.Name <> "Sheet1" Then WS.Cells.ClearContents Rows(1).Copy WS.Cells(1, 1) End If Next For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row If Cells(i, 1) <> "" Then N = Cells(i, 1) Rows(i).Copy Worksheets(N).Cells(Rows.Count, 2).End(xlUp).Offset(1, -1) Next End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
26 | 下2ケタの数字で並べ替えるには | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
A列の数値を、下2ケタの昇順に並べ替えます。 降順の場合、頭の「'」を昇順のコードに付け替えててください。 Sub miko_test1() Dim i As Integer Application.ScreenUpdating = False Range("B1").EntireColumn.Insert For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Cells(i, 2) = Right(Cells(i, 1), 2) '下2桁(右から2文字)の数値を抽出 Next Columns("A:B").Sort Key1:=Range("B1") '昇順に並べ替え 'Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlDescending '降順の場合、こちらのコード Columns("B:B").Delete Shift:=xlToLeft Application.ScreenUpdating = True End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
27 | セル内の文字列を逆順に並び替えるには?(
「あいうえお」 を 「おえういあ」 に ) http://cgi.fuji.ne.jp/~fj2094/cgi-bin1/wwwlng.cgi?print+200202/02020076.txt 「セル内の文字を逆さにするには?」 【編集ラウンジ】 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1. 書式設定 配置、折り返して全体を表示をチェック
2. 〃 方向 縦書きを選択 尚、セルの高さは1文字分にして下さい。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
データがA列にA1から順番に入ってて文字数が20文字以内であるとして・・・
B1に =MID(A1,20,1)&MID(A1,19,1)&MID(A1,18,1)&MID(A1,17,1)&MID(A1,16,1) &MID(A1,15,1)&MID(A1,14,1)&MID(A1,13,1)&MID(A1,12,1)&MID(A1,11,1) &MID(A1,10,1)&MID(A1,9,1)&MID(A1,8,1)&MID(A1,7,1)&MID(A1,6,1) &MID(A1,5,1)&MID(A1,4,1)&MID(A1,3,1)&MID(A1,2,1)&MID(A1,1,1) と入れて下までコピーします。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
A1の文字列をB1に並べ替えます。 Function StrRev(Source As String) As String StrRev = StrReverse(Source) End Function このコードの使い方は、マクロの使い方(6)ユーザー定義関数(標準モジュール)にあります。 コードを記入したら、B1に =StrRev(A1) と入れて下までコピーする。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
28 | 重複しない値を抽出するには? |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
【データ】【フィルタオプションの設定】【重複するレコードは無視する】にチェック。 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
とまと
とまと なす なす とまと 左のような内容を ねぎ ねぎ 右のように表示 なす とまと A列に元データがあるとして、最高100行までに対応 【解析】 B1の関数 =IF(COUNTIF(A$1:A1,A1)=1,ROW(),"") ISERROR(INDEX($A$1:$A$100,SMALL($B$1:$B$100,ROW()),1)) |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
29 | 一行のデータを項目ごとに列に振り分けるには | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
上のような表を、新しくシートを挿入して下のように表示します。元のデータシートを sheet1 としています。
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
30 | 検索項目を入力して、同じ値があればそのセルを選択し、表示するには? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
A1に入力した値と同じ値を、A2以降のA列から探して、そのセルを選択して一番上に表示します。 Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long If Target.Address <> "$A$1" Then Exit Sub '対象がA1以外なら処理しないで終わる If Target <> "" Then '空白でなければ次の処理を実行する '2行目からA列最終行まで繰り返す For i = 2 To Cells(Application.Rows.Count, 1).End(xlUp).Row If Target = Cells(i, 1) Then '入力した内容と同じセルがA列にあれば Cells(i, 1).Select 'そのセルを選択 ActiveWindow.ScrollRow = i そのセルの行までスクロール Exit Sub '処理を終わる End If Next MsgBox "該当する項目がありませんでした" End If End Sub |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
31 | 検索項目を入力して、同じ値があればその行を検索行の下に挿入 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sub miko_test1() |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sub miko_test2() |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
32 | 組み合わせを表示するには(候補から2項目を組合せと合計) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
処理する候補のセル範囲(上の例だとA2〜A4)を選択してから実行してください。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
33 | 組合せを表示するには(B列の候補から2・3・・・5項目の組合せと平均) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sub miko_test2() |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
34 | 2列の内容によって、他のシートにデータをコピーするには? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
一旦「シ−ト1」以外のシートの、A〜C列2行目以降をクリアし、該当のシートを探して「シ−ト1」の内容をコピーします。 その名前のシートが存在しない場合、新しくシートを作成します。 【注意】「シ−ト1」は変更しませんが、それ以外のシートはクリアしてしまいますのでご注意ください。 シートをクリアしない場合は、コードの7行目から、11行目までを削除してください。 そうすると、既存のデータにどんどん蓄積していきます。 Sub miko_test() Dim i As Integer, j As Integer, h As Integer Dim S As Object, WS1 As Worksheet Application.ScreenUpdating = False '画面の動きを固定 Set WS1 = Worksheets("シ−ト1") With WS1 'シ−ト1以外のシートの、A〜C列2行目以降をクリア For Each S In Sheets If S.Name <> .Name Then S.Range("A2:C" & _ S.Cells(Rows.Count, "A").End(xlUp).Row).ClearContents Next 'シ−ト1の2行目からA列最終行まで繰り返し For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row '既存のシート分繰り返す For Each S In Sheets '(部署)+・+(担当者)が名前になったシートがある場合の処理 If .Cells(i, 1) & "・" & .Cells(i, 2) = S.Name Then h = 1 '判定を1にする 'A〜C列をコピーする .Range(.Cells(i, 1), .Cells(i, 3)).Copy _ S.Range(S.Cells(S.Cells(Rows.Count, "A").End(xlUp).Row + 1, 1), _ S.Cells(S.Cells(S.Rows.Count, "A").End(xlUp).Row + 1, 3)) End If Next If h = 1 Then h = 0 Else '(部署)+・+(担当者)が名前になったシートがない場合の処理 Sheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Name = .Cells(i, 1).Value & "・" & .Cells(i, 2).Value .Range("A1:C1").Copy Range("A1") '見出し行をコピー 'A〜C列をコピー .Range(.Cells(i, 1), .Cells(i, 3)).Copy _ Range(Cells(Cells(Rows.Count, "A").End(xlUp).Row + 1, 1), _ Cells(Cells(Rows.Count, "A").End(xlUp).Row + 1, 3)) End If Next End With Application.ScreenUpdating = True '画面の固定解除 End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
35 | 複数の指定の値に合致するデータを抽出するには? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
上記【Sheet2】の2行目の条件に該当するデータを【Sheet1】から、オートフィルタを使って【Sheet2】にコピーします。 Sub miko_test() Dim WS1 As Worksheet, WS2 As Worksheet Dim LastR As Long, LastC As Integer Application.ScreenUpdating = False Set WS1 = Worksheets("Sheet1") Set WS2 = Worksheets("Sheet2") LastR = WS1.Cells(Application.Rows.Count, 1).End(xlUp).Row LastC = WS1.Cells(1, Columns.Count).End(xlToLeft).Column WS1.Select Range(Cells(1, 1), Cells(LastR, LastC)).Select With Selection .AutoFilter 'オートフィルタの抽出条件は文字列で指定するので、日付、単価等はTextプロパティを使います。 .AutoFilter Field:=1, Criteria1:=WS2.Range("A2").Text '日付 .AutoFilter Field:=2, Criteria1:=WS2.Range("B2").Value '得意先名 .AutoFilter Field:=3, Criteria1:=WS2.Range("C2").Value '商品名 .AutoFilter Field:=4, Criteria1:=WS2.Range("D2").Text '単価 .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy WS2.Range("A5").PasteSpecial Paste:=xlValues .AutoFilter End With Application.ScreenUpdating = True End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
36 | 2つのブックを比べて、異なるデータを3つ目のブックに書き出すには? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Book2・Sheet1の値を、Book1・Sheet1で探して、A〜G列のどれかひとつでも違っている場合、 Book2・Sheet1A〜H列をBook3・Sheet1に書き出します。 Sub miko_test() Dim WS(1 To 3) As Worksheet Dim i As Long, j As Long, LastR(1 To 3) As Long Application.ScreenUpdating = False '画面の動きを固定 Set WS(1) = Workbooks("Book1.xls").Worksheets("Sheet1") '先月分 Set WS(2) = Workbooks("Book2.xls").Worksheets("Sheet1") '今月分 Set WS(3) = Workbooks("Book3.xls").Worksheets("Sheet1") '差分 '差分のシートを選択し、全セルをクリア WS(3).Select Cells.ClearContents '先月分、当月分の最終行取得 LastR(1) = WS(1).Cells(Application.Rows.Count, "A").End(xlUp).Row LastR(2) = WS(2).Cells(Application.Rows.Count, "A").End(xlUp).Row '先月分、当月分の全項目をくっつけて、差分シートに書き出す For i = 1 To LastR(1) With WS(1) Cells(i, 1) = .Cells(i, 1) & .Cells(i, 2) & .Cells(i, 3) & _ .Cells(i, 4) & .Cells(i, 5) & .Cells(i, 6) & .Cells(i, 7) End With Next For i = 1 To LastR(2) With WS(2) Cells(i, 2) = .Cells(i, 1) & .Cells(i, 2) & .Cells(i, 3) & _ .Cells(i, 4) & .Cells(i, 5) & .Cells(i, 6) & .Cells(i, 7) End With Next '差分の最終行取得 LastR(3) = Cells(Application.Rows.Count, "A").End(xlUp).Row '見出し行をコピー WS(2).Range("A1:H1").Copy WS(3).Range("C1") '差分のB列を最後まで処理 For i = 2 To Cells(Application.Rows.Count, "B").End(xlUp).Row 'A列に同じ値がひとつもない場合 If WorksheetFunction.CountIf(Range(Cells(2, 1), Cells(LastR(3), 1)), Cells(i, 2).Value) = 0 Then '今月分シートの該当行A〜H列をコピー WS(2).Range("A" & i & ":H" & i).Copy Range("C" & Cells(Application.Rows.Count, "C").End(xlUp).Row + 1) End If Next '作業列を削除 Columns("A:B").Delete Shift:=xlToLeft Application.ScreenUpdating = True '画面の動きを戻す End Sub |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
37 | 選択範囲内で指定した値と同じ値で、3番目に該当するものを探すには? http://www.ae.wakwak.com/~efc21/cgi-bin/wwwlng.cgi?print+200205/02050024.txt 「検索について」 【VBAラウンジ】 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
いろんな方法がありましたので、全部キープしました。♪ まずは、【Findメソッド】 Sub 検索demo() Dim rng1st As Range, rngNext As Range, lngFindCount As Long With Worksheets("text1").Columns(3) Set rng1st = .Find( _ What:="営業", After:=.Cells(.Rows.Count, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) Set rngNext = rng1st Do lngFindCount = lngFindCount + 1 If lngFindCount = 3 Then MsgBox "3番目の行は " & CStr(rngNext.Row) & " です." Set rngNext = .FindNext(After:=rngNext) Loop Until rng1st.Address = rngNext.Address End With End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
【Match関数】 Sub Check3rd_St() Dim Sh As Worksheet Dim MyR As Range Dim x As Variant Dim y As Long Set Sh = Sheets("Text1") x = Application.Match("営業", Sh.Columns("C"), 0) If Not IsError(x) Then y = x: x = 0 Set MyR = _ Sh.Range(Sh.Cells(y + 1, 3), Sh.Cells(Sh.Rows.Count, 3).End(xlUp)) x = Application.Match("営業", MyR, 0) If Not IsError(x) Then y = y + x: x = 0 Set MyR = _ Sh.Range(Sh.Cells(y + 1, 3), Sh.Cells(Sh.Rows.Count, 3).End(xlUp)) x = Application.Match("営業", MyR, 0) If Not IsError(x) Then y = y + x MsgBox Sh.Cells(y, 3).Address(False, False) & _ " に見つかりました", 48 Set MyR = Nothing End If Set MyR = Nothing End If End If Set Sh = Nothing End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
【Ifで判定】時間はかかりますが、コードは短い! データ量が少なければこれがお手軽♪(^^ゞ Sub Test() Dim myRng As Range, c As Range, i As Integer With Worksheets("text1") Set myRng = .Columns(3).SpecialCells(xlCellTypeConstants) For Each c In myRng If c.Value = "営業" Then i = i + 1 If i = 3 Then MsgBox c.Row & "行目に有りました。": Exit Sub Next c MsgBox "「営業」は、" & i & "ヶしか有りません。" End With End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
【ユーザー定義関数】 何度も使うなら、これ! Sub Test() Dim lngIns As Long lngIns = MyMatch("営業", Worksheets("text1").Range("C:C"), 3) MsgBox lngIns End Sub Function MyMatch(s, r, n) As Long Dim i As Long, j As Long On Error GoTo ErrorHandler For j = 1 To n i = i + WorksheetFunction.Match(s, _ r.Resize(r.Rows.Count - i).Offset(i), 0) Next MyMatch = i Exit Function ErrorHandler: End Function このコードの使い方は、マクロの使い方(6) ユーザー定義関数(標準モジュール)にあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
38 | オートフィルタ切り替えマクロ | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
’シートがオートフィルタされていなければ、1行目をオートフィルタします。 既にオートフィルタされていれば、解除します。 Sub オートフィルタ() If ActiveSheet.AutoFilterMode = False Then Rows("1:1").AutoFilter ’1行目をオートフィルタする Else ActiveSheet.AutoFilterMode = False ’オートフィルタ解除 End If End Sub このコードの使い方は、マクロの使い方(1)標準モジュールにあります。 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
39 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
40 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||