シート1に名簿があります。
A列 B列 C列 D列 E列 F列 G列 H列 ・・・・
通し番号 クラス 名前 部活 趣味 部活 趣味 部活 ・・・
D列E列は1年時の部活と趣味
F列G列は2年時の部活と趣味です。
これが縦に100名以上、横には6年分くらい続きます。
で、シート2には
A列 C列 D列 E列
通し番号 名前 部活 趣味
の枠だけがあります。およそ40名分の枠です。
そこで、シート1のあるセルに「2」「3」と入れてマクロボタンを
押すと、シート2に「3組の2年時のデータ」の名簿が出来上がる
という抽出、コピー、貼付けのマクロを教えてください。
貼付けるのは、名前と部活と趣味だけですが、まず抽出方法が?です。
よろしくお願いします。
No.4ベストアンサー
- 回答日時:
No.3です!
たびたびお邪魔します。
>シート2に「学年」「クラス」のセルがあってもできるということですよね?
に関しては「そのとおり!」ということになります。
そして、配列数式を使っている関係上、コンピュータへの負担を心配されていましたので、
作業用の列を設けて、少し負担を少なくする方法を書いておきます。
もう一度画像をアップさせていただきます。
Sheet1の最終列に作業用の列を設けて、今回はSheet2に年時とクラスを入力するようにしてみました。
作業列P3セルに
=IF(OR(Sheet2!$C$1="",B3<>Sheet2!$C$1),"",ROW(A1))
としてオートフィルで下へコピー又はP3セルのフィルハンドルでダブルクリック。
今回もSheet2の各列の数式は違いますので、それぞれのセルに数式を入力する必要があります。
(配列数式はありません)
Sheet2のA3セルに
=IF(COUNT(Sheet1!$P$3:$P$200)<ROW(A1),"",INDEX(Sheet1!$A$3:$A$200,SMALL(Sheet1!$P$3:$P$200,ROW(A1))))
B3セルに
=IF(A3="","",VLOOKUP(A3,Sheet1!$A$3:$C$200,3,0))
C3セルに
=IF(A3="","",INDEX(Sheet1!$D$3:$O200,MATCH(A3,Sheet1!$A$3:$A200,0),$A$1*2-1))
D3セルに
=IF(A3="","",INDEX(Sheet1!$D$3:$O200,MATCH(A3,Sheet1!$A$3:$A200,0),$A$1*2))
として、前回同様全てを範囲指定しオートフィルで下へコピーします。
これで少しはコンピュータも軽くなると思います。
以上、今回も長々と書いてしまいました。
参考になれば幸いです。m(__)m
この回答への補足
実際にやってみましたら、できました!
で、追加質問ですが、
IF(COUNT(Sheet1!$P$3:$P$200)<ROW(A1),"",INDEX(Sheet1!$A$3:$A$200,SMALL(Sheet1!$P$3:$P$200,ROW(A1))))
と、ここでA1はどういう理由で利用されているのですか?
A1とは年時のセルですよね。それを何と比べてるのかなと思いました。C1のクラスのセルかな?と最初思ったので教えてください。
それと、C3D3セルは「1*2-1」を参考に、慣れているVLOOKUPでやってみたら出来ました!(^^)
ありがとうござりまする~~~~。
いろいろな方法を検討してみたいと思います。
みなさん素晴らしいですね~。
マクロよりもこの、さくっと出る方が個人的には好きです。
マクロだといろいろファイルをいじってる間にどっかにいってしまって
「マクロが見つかりません」てなことになるので、だったら数式の方が
便利だなと思っていました。ありがとうございました!
No.6
- 回答日時:
A B C D E F G…
1 No. クラス 名前 部活 趣味 部活 趣味…
2 1 1 高木 柔道 生け花 柔道 生け花…
・ ・ ・ ・ ・ ・ ・ ・ …
・ ・ ・ ・ ・ ・ ・ ・ …
・ ・ ・ ・ ・ ・ ・ ・ …
・ ・ ・ ・ ・ ・ ・ ・ …
312 311 3 青木 ・ ・ ・ ・ …
・ ・ ・ ・ ・ ・ ・ ・ …
・ ・ ・ ・ ・ ・ ・ ・ …
・ ・ ・ ・ ・ ・ ・ ・ …
・ ・ ・ ・ ・ ・ ・ ・ …
(※左端はセルの行ナンバー)
こんな感じの表だと仮定します
更にシート1のP1に検索対象組ナンバー、P2に検索対象学年が数値として入っているものとします
エラー処理は省きますね
A1に
=OFFSET(Sheet1!$A$1,SMALL(INDEX((Sheet1!$P$1<>Sheet1!$B:$B)*1100000+ROW(Sheet1!$B:$B),0,0),ROW($A1)),COLUMN(A$1),1,1)
C1にA1をコピー
D1に
=OFFSET(Sheet1!$A$1,SMALL(INDEX((Sheet1!$P$1<>Sheet1!$B:$B)*1100000+ROW(Sheet1!$B:$B),0,0),ROW($A1)),COLUMN(A$1)+Sheet1!$P$2*2,1,1)
D1をE1にコピー
この1行目を
40行目までフィルハンドルなどで数式コピー
如何でしょうか
御役に立てたなら幸いです
No.5
- 回答日時:
>貼付けるのは、名前と部活と趣味だけです
とは,貼り付け先シートのA列は残すという意味だったんですね。
Sub macro1r1()
Dim nen
Dim cls
nen = InputBox("学年")
cls = InputBox("クラス")
If nen = "" Or cls = "" Then Exit Sub
Application.ScreenUpdating = False
Worksheets("Sheet2").range("C:E").ClearContents
With Worksheets("Sheet1")
.AutoFilterMode = False
.Range("B:B").AutoFilter field:=1, Criteria1:=cls
.Range("C:C").Copy _
Destination:=Worksheets("Sheet2").Range("C1")
.Range("B:C").Offset(0, nen * 2).Copy _
Destination:=Worksheets("Sheet2").Range("D1")
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub
とかなんとか。
細かいところまでありがとうございました!
もう少し分かりやすい説明を心がけます。
わからない言語?もあるので勉強してみます。
ありがとうございました!
No.3
- 回答日時:
こんばんは!
VBAでないので参考にならなかったら無視してください。
関数を使った一例です。
↓の画像で上側がSheet1で下側のSheet2に抽出する方法です。
今回はSheet2の各列に違う数式を入れていますので、列方向へのコピーは考えていません。
Sheet2のA2セルに
=IF(COUNTIF(Sheet1!$B$3:$B$200,Sheet1!$C$1)<ROW(A1),"",INDEX(Sheet1!$A$3:$A$200,SMALL(IF(Sheet1!$B$3:$B$200=Sheet1!$C$1,ROW($A$1:$A$198)),ROW(A1))))
これは配列数式になってしまいますので、この画面からコピー&ペーストしただけではエラーになると思います。
貼り付け後、F2キーを押す、又は貼り付けセルでダブルクリック、又は数式バー内で一度クリックします。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定してください。
数式の前後に{ }マークが入り配列数式になります。
残りの3列の数式は配列数式ではありません、単に貼り付けだけでOKです。
B2セルに
=IF(A2="","",VLOOKUP(A2,Sheet1!$A$3:$C$200,3,0))
C2セルに
=IF(A2="","",INDEX(Sheet1!$D$3:$I$200,MATCH(A2,Sheet1!$A$3:$A$200,0),Sheet1!$A$1*2-1))
D2セルに
=IF(A2="","",INDEX(Sheet1!$D$3:$I$200,MATCH(A2,Sheet1!$A$3:$A$200,0),Sheet1!$A$1*2))
という数式を入れ、A2~D2セルを範囲指定し、D2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
これでSheet1のA1セルに表示したい年時を、C1セルにクラスを入力すると
入力したクラスの年時のデータが抽出できると思います。
以上、長々と書きましたが
最初に書いたように的外れなら
読み流してくださいね。m(__)m
なるほど~。数式はまだ詳しく見てませんが、参照でも対応できるのですね!ありがとうございました!!
ちょっとやってみたいと思います。これはシート2に「学年」「クラス」のセルがあってもできるということですよね?そうなると数字を入れたらババっと名簿が出来るので壮観な気がします。ただ、オートフィルタ的なマクロを組むのと、どっちが軽いかが気になりました。結構重くなると入力の反応が遅くなることがあって困っているので(単にPCの性能が低い可能性もあります)いろんなパターンがあるんですね~。だからエクセルって面白いですね。出来るかな?ってことが大体出来るのも面白いです。
No.2
- 回答日時:
>…のマクロを教えてください。
Sub macro1()
Dim nen
Dim cls
nen = InputBox("学年")
cls = InputBox("クラス")
If nen = "" Or cls = "" Then Exit Sub
Application.ScreenUpdating = False
Worksheets("Sheet2").Cells.ClearContents
With Worksheets("Sheet1")
.AutoFilterMode = False
.Range("B:B").AutoFilter field:=1, Criteria1:=cls
.Range("C:C").Copy _
Destination:=Worksheets("Sheet2").Range("A1")
.Range("B:C").Offset(0, nen * 2).Copy _
Destination:=Worksheets("Sheet2").Range("B1")
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
End Sub
とかなんとか。
No.1
- 回答日時:
>まず抽出方法が?です。
フィルタオプションの設定を使えば抽出可能です。
そのためには項目に重複があってはいけません。
よって、部活1年 趣味1年 と2セル分書いて2セル選択して横へオートフィル
部活1年趣味1年部活2年趣味2年部活3年趣味3年部活4年
とするのはどうでしょうか?
Sheet2には
通し番号 名前 ="部活"&学年&"年" ="趣味"&学年&"年"
のようにしておく。(タイトルは学年を参照するようにする)
フィルタオプションは Sheet2の何も書いてないセルで行います。
●指定した範囲
リスト範囲 Sheet1!$A$1:$N$44
検索条件範囲 $F$1:$F$2
抽出範囲 $A$1:$D$1
添付図参照
G2セルを名前の定義で「学年」にしています
マクロを使えば項目の重複も可能だと思いますが、フィルタを覚えてからでも遅くはないと思います
その後、マクロを自動記録してみると良いと思います。
この回答への補足
画像まで添付していただいてありがとうございます!
オートフィルタですね!新しいマクロで記録中に
オートフィルタをしてみたのですが、なんだかちんぷんかんぷんで
困っていました。ちょっと分かってきたような気がします!
週が開けたらやってみます。ありがとうございました!
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Excel(エクセル) エクセルのマクロでコピー後の貼り付け先を毎回指定したところにしたい 5 2022/08/12 10:47
- Excel(エクセル) エクセルシートのデータを1列飛ばしで別ブックのシートに貼り付けるマクロが知りたい 2 2023/06/05 22:37
- Excel(エクセル) VBA セルの値と同じ名前のシートにデータを貼り付けするやり方を教えてください 2 2022/05/17 16:26
- その他(Microsoft Office) エクセルマクロ オートフィルターでで選択コピー 2 2022/04/18 11:05
- Visual Basic(VBA) VBAで最新のデータを別シートに転記する方法をお教えください。 3 2022/04/07 19:20
- Visual Basic(VBA) コード名シートA列と集計シートA列のコードが一致したら、コード名シートA5からk12の範囲をコピーし 1 2022/08/29 23:46
- Visual Basic(VBA) 集計シートA列のコードと一致する右に並んだシート名(コード)の3行目から10行目をコピーして貼り付け 4 2022/08/18 15:24
- Visual Basic(VBA) 【困っています2】VBA 追加処理の記述を教えてください。 2 2022/08/26 11:42
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルのデーターが2か月前の...
-
エクセルVBA、別ブックへ転記す...
-
【マクロ】顧客番号にて一致さ...
-
エクセル共有したが、アクセス...
-
エクセル②
-
(マクロ)データをAブックからB...
-
Excelでセルの値が同じか...
-
エクセルを使っていて2024/5/15...
-
指定文字の間に
-
Microsoft 365の Excel を使用...
-
エクセルの計算
-
エクセルでの作業計算方法について
-
Excelで全角を半角にしたいので...
-
エクセル関数に詳しい方教えて...
-
Googleスプレッドシートでファ...
-
エクセル 文字を増やしたい。
-
はがきについて。
-
エクセルの暗号化なしのバーの...
-
【マクロ】必要な項目(列)の...
-
Excel
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報