こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

全体の名簿からクラスごとの名簿を作りたい

クラス全員の名簿があるシートからクラス別のシートに自動で入力される方法について。#はシート名

#Sheet1
idnameclass
1aA
2bA
3cB
4dA
5eC
6fB
7gA
8hC
9iB
#A
idnameclass
#B
idnameclass
#C
idnameclass
の4つのシートがある。
上のようなクラス全員の名簿シート(Sheet1)が有るときに、名簿シートのclassが変わっても参照しているクラス別のシートも変える方法。

=IF(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1)))=Sheet1!$C2,Sheet1!A2,0)

これをA2にコピーして広げるとクラスが合えばその位置を参照して入力され、異なれば0が返ります。そしてフィルターで0を除外すればクラス別のシートができます。これの0を埋めない方法でフィルターを使わずに内容を詰めてクラス別のシートを作る方法はあるでしょうか。

投稿日時 - 2019-05-23 21:33:31

QNo.9619404

暇なときに回答ください

質問者が選んだベストアンサー

質問文とその例がわかりにくい。変にコンピュターなれしたようなところがあり、わかりにくい。
抜出問題で、>=IF(RIGHT(CELL・・といったやり方はOKWAVEやWEB記事でで見たことがない。
多分、よくある例なので推測で解説してみる。
下記はSheet1でD列にワーク列を使うが、使わない方法は、さらに式が複雑で初心者には判りにくい式になる。
あるいは「配列数式」を使った回答もよく出る。
こういう関数による、「抜出し問題」は、複雑な式になり、お勧めしないが、
関数しか知らないのか、こういう質問が数か月に1度ぐらい出る。
下記はワーク列を使う、自称imogasi方式。
複写を止めるべき行以下は空白にするのだが、その式を被せると、式が長くなり、本質がわかりにくくなるので略。その式は、質問者もわかっているかも。
(=行が該当件数を越えたら(Aの場合は4件)、空白を返す式をIF式で作る)
Sheet1 A1:C10
生徒番号氏名クラス
1aA
2bA
3cB
4dA
5eC
6fB
7gA
8hC
9iB
D2に式 =C2&COUNTIF($C$2:C2,C2)
これが該当分の行を上に詰める、仕掛け(の1つの方法)です。
結果
生徒番号氏名クラスワーク列
1aAA1
2bAA2
3cBB1
4dAA3
5eCC1
6fBB2
7gAA4
8hCC2
9iBB3
ーーー
Sheet2、Sheet3、Sheet4の3つのシートを用意する(クラスが3つだから)
Sheet2の第1行と第2行に
A
生徒番号氏名クラス
を作る。
A3に=INDEX(Sheet1!$A$1:$C$10,MATCH($A$1&(ROW()-2),Sheet1!$D$1:$D$10,0),COLUMN())
を入れて、C列まで式を余も方向に複写。
A3:C3を選択して、+ハンドルで下方向に式複写。
結果
A
生徒番号氏名クラス
1aA
2bA
4dA
7gA
#N/A#N/A#N/A
Sheet3は
A1:C2に
B
生徒番号氏名クラス
を作って
A3に =INDEX(Sheet1!$A$1:$C$10,MATCH($A$1&(ROW()-2),Sheet1!$D$1:$D$10,0),COLUMN())
以下はAクラスの場合と同じ。CクラスはSheet4に出すのだが、略。
ーー
こういうのはフィルタだけで処理し、抽出結果を別シートに移す
、というのが、エクセルの予期(勧めている)している方法と思う。
Excelはまず、「操作の学」だと思う。
ーー
こういうのは、VBAやSQL(アクセス)で処理が向いている。勉強を勧める。

投稿日時 - 2019-05-24 10:50:42

お礼

他の人にも答えていただきましたが、こちらの答えが一番求めていたものでした。
他の解答と比べてクラス別のシートがスッキリしてファイルサイズも少なくなりそうです。
ありがとうございました。

>こういうのは、VBAやSQL(アクセス)で処理が向いている。勉強を勧める。
確かにごもっともな意見です。

投稿日時 - 2019-05-25 21:58:41

ANo.1

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

VBA+SQLでの対応もポストしておきます。

Option Explicit

Sub aaa()

 Dim rs As Object
 Dim cn As Object
 Dim SQL As String
 Dim ShtCnt As Long

 Set cn = CreateObject("ADODB.Connection")
 Set rs = CreateObject("ADODB.Recordset")
 cn.Provider = "Microsoft.ACE.OLEDB.12.0"
 cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
 cn.Open ThisWorkbook.FullName

 With ThisWorkbook
  'クラスごとシートは2枚目から末まで
  For ShtCnt = 2 To .Sheets.Count
 
   'SQL文を組立
   SQL = ""
   SQL = SQL & "SELECT [id],[name],[Class]" & vbCrLf
   SQL = SQL & "FROM [Sheet1$A1:Z10000]" & vbCrLf
   SQL = SQL & "Where Class = " & _
      "'" & .Sheets(ShtCnt).Name & "'" & vbCrLf
   SQL = SQL & "ORDER BY [id] " & vbCrLf
  
   'SQLを実行
   rs.Open SQL, cn

   '出力先をクリアーして結果セットを格納
   With .Sheets(ShtCnt)
    Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
    .Cells(2, 1).CopyFromRecordset rs
   End With
 
   rs.Close
 
  Next ShtCnt
 End With
 
 '後処理
 Set rs = Nothing
 cn.Close
 Set cn = Nothing

End Sub

投稿日時 - 2019-05-24 23:29:22

お礼

VBA、SQL はまだ不慣れですが勉強したいと思います。ありがとうございました。

投稿日時 - 2019-05-25 21:59:23

ANo.3

添付画像のように、

D1=0
D2=IF(C2<>0,INT(D1)+1,D1+0.001)
E2=IFNA(INDEX($A:$C,MATCH(ROW()-1,$D:$D,0),COLUMN()-4),"")
E2を右方向に2つ複写
D2:G2を必要数下方向に複写

というのはいかがでしょうか?
(A:D列は非表示にします)

VBAが扱えるのであれば
VBA+SQLで処理する方がシンプルと思います。

投稿日時 - 2019-05-24 21:03:14

お礼

できればクラス別のシートではデータは非表示せずに敷き詰めてファイルサイズを小さくしたいところです。
こちらも関数が大変参考になりました。ありがとうございます。

投稿日時 - 2019-05-25 21:59:10

ANo.2

>クラス全員の名簿があるシートからクラス別のシートに自動で入力される方法について。
#はシート名
--------------------------------
あなたの、設計を無視する無礼をお許し願って
★シートはクラス全員の名簿一つでよい
 この表を添付の流れで、セルの範囲指定からテーブルとして書式設定
 これで各項目にフィルタ機能が働き、特定のクラスのみの表が出来上がります
  このフィルタ機能の利用はマクロの記録で作成して。
  マクロ実行で可能です

投稿日時 - 2019-05-24 12:02:12

お礼

フィルタとマクロの併用は確かにそうですが、秘伝のエクセルファイル(.xls)を受け継いでいてマクロの有効ができませんでした。

投稿日時 - 2019-05-25 21:58:57

あなたにオススメの質問