使用OS:Windows7
Excel2010を利用しています。
Sheet1には添付写真のように品目名の下に3000件ー5000件ほどの資材などの名前があります。
Sheet2のA1にある検索したい語句を入れるとSheet1のB列から部分一致する列を抜き出しSheet2のA3以下にその情報が記載されるようにしたいと思っています。
例としてSheet2のA1に『コーススレッド』と入力するとSheet2のA3の行にはSheet1の2行目、A4の行にはSheet1の5行目が記載されるようにしたいです。
オートフィルタや検索を使えばいいじゃないかと言われるかもしれないですが、そういった操作ができない年長の方が使えることを目的としたいので、教えていただけないでしょうか?VBAが絡んでも問題ないです。よろしくお願いします。
A 回答 (4件)
- 最新から表示
- 回答順に表示
No.4
- 回答日時:
数式入力セル(表示データ数)が多くなると(20件以上該当データがあるような場合)シートの動きが重くなりますので、あまりお勧めできませんが、数式で対応するなら以下のような数式を使うことになります。
例えばSheet2のA3セルにA1セルの文字列を含むB列のデータを抽出するなら以下の式を入力し、下方向にオートフィルコピーします。
=INDEX(Sheet1!B:B,SMALL(INDEX(ISERR(FIND($A$1,Sheet1!$B$2:$B$5000))*10000+ROW($B$2:$B$5000),),ROW(A1)))&""
なお、上記の数式を右方向にオートフィルすれば該当の行のデータを表示できますが、計算負荷を少なくするするには、B3セルから右は以下のようなIF関数で対応するのが良いと思います。
=IF(A3="","",INDEX(Sheet1!C:C,SMALL(INDEX(ISERR(FIND($A$1,Sheet1!$B$2:$B$5000))*10000+ROW($B$2:$B$5000),),ROW(A1))))
No.3
- 回答日時:
抽出部分の消去が面倒くさかったので抽出結果はSheet2のC:E列に出すものとします。
また、Sheet2のA1には「品目名」と入っていて、A2に品目名を手入力するものとします。
コマンドボタンか何かを用意して、手入力後に以下のマクロを動かしてください。
Sub Sample()
Sheets("Sheet2").Columns("C:E").ClearContents
Sheets("Sheet1").Range("B:D").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Sheet2").Range("A1:A2"), CopyToRange:=Sheets("Sheet2").Range("C1")
End Sub
No.2
- 回答日時:
分かり易い方法は作業列を作って対応する方法です。
シート1のE2セルには次の式を入力して下方にオートフィルドラッグコピーします。
=IF(COUNTIF(B2,"*"&Sheet2!$A$1&"*"),MAX(E$1:E1)+1,"")
シート2のA1セルには検索したい文字を入力します。
A3セルには次の式を入力したのちに右横方向にオートフィルドラッグコピーしたのちに下方にもオートフィルドラッグコピーします。
=IF(OR(ROW(A1)>MAX(Sheet1!$E:$E),COLUMN(A1)>3),"",INDEX(Sheet1!$B:$D,MATCH(ROW(A1),Sheet1!$E:$E,0),COLUMN(A1)))
No.1
- 回答日時:
>3000件ー5000件ほどの資材などの名前があります。
関数で並べるのは現実的ではない物量なので,マクロを使います。
準備:
シート2の1行目にシート1と同じ項目を並べる
B1,C1,D1に品目名,大分類,大分類番号のように
シート2の2行目に検索ワードを記入することにする
シート2の4行目以下に抽出する
手順:
シート2のシート名タブを右クリックしてコードの表示を選ぶ
現れたシートに下記をコピー貼り付ける
private sub worksheet_change(byval Target as excel.range)
set target = application.intersect(target, range("2:2"))
if target is nothing then exit sub
range("A5:A" & application.max(5, cells.specialcells(xlcelltypelastcell).row)).entirerow.delete shift:=xlshiftup
if application.counta(target) = 0 then exit sub
worksheets("Sheet1").range("B:D").advancedfilter _
action:=xlfiltercopy, _
criteriarange:=range("B1:D2"), _
copytorange:=range("B4:D4")
end sub
ファイルメニューから終了してエクセルに戻る
2行目に検索語を記入する。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Visual Basic(VBA) VBA For Each 〜 複数条件について 3 2022/10/20 20:05
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Visual Basic(VBA) VBAでvlookup関数から、別シート参照するやり方・・・ 2 2022/11/14 18:49
- Visual Basic(VBA) VBA 別sheetからの転記なのですが 2 2023/05/22 15:55
- Visual Basic(VBA) Sheet「状況」から、分類の年齢別カウント数をSheet「D表」へ転記する下記マクロを作っています 7 2022/12/14 17:57
- Visual Basic(VBA) 改行ごとに行を追加し、数量を分割 4 2023/07/11 16:39
- Visual Basic(VBA) 【変更】ファイルを閉じてダイアログで保存した時、更新したシートだけの処理の実行をする 5 2022/03/26 18:31
- その他(プログラミング・Web制作) python文字化けエラーが発生しているようです 3 2022/04/13 19:41
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelデータをコピペして、ペー...
-
スプレッドシート、Excelでの数...
-
エクセルの関数について教えて...
-
スプレッドシートで使う数式を...
-
Microsoft Officeの中古は信用...
-
エクセルで会社の従業員のデー...
-
Excelで50個のセルに同じ文字を...
-
エクセルの表で1年間の曜日を...
-
エクセルでの特別な文字を上に...
-
エクセルで不等号記号(≠)が上に...
-
エクセルでB列でフィルターをか...
-
エクセルでセルに標準で入力さ...
-
Excelで、項目の種類ごとにカウ...
-
【マクロ】アクティブセルにブ...
-
EXCELの質問です 119から足した...
-
【マクロ】アクティブセルの、...
-
【マクロ】アクティブセルの行...
-
エクセルの空欄をつめて、次の...
-
Excelの数式について教えてくだ...
-
非表示列の再表示に失敗
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報