エクセルを使って特定の文字を抜き出したいのですが

質問

こんばんは、エクセル初心者の教員です。
よろしくお願いいたします。

　 1A 1B 2A 2B 3A 3B　　田中　小林
(1)英 数 理 音 理 社　　　　　　　１Ａ
(2)数 英 音 社 数 理　　　　　　　１Ｂ
(3)国 音 社 数 英 国　　　３Ｂ
(4)音 美 英 理 国 数　　　３Ａ　　２Ａ
(5)美 国 数 英 音 技　　　　　　　２Ｂ
(6)学 学 学 学 学 学　　　２Ａ　　１Ａ

というように、時間割の右側に「田中」の授業２・３年国語と学活を、「小林」の授業１・２年の英語と学活を自動で抜き出すにはどうしたらいいのでしょうか。確認しながら手で入力すればいいのですが
何か方法はないかと思い調べましたがわかりませんでした。
教えて頂けたらありがたいです。よろしくお願いいたします。

回答No.9

tom04 · ベストアンサー

No.8です！
補足の件で・・・

前回のSheet2はこちらが勝手に作ったものですが、それで説明させていただきます。
前回の「斉藤先生」の場合の数式で説明させていただくと

=IF(COUNTIF(B2:E2,"数"),INDEX($B$1:$E$1,,MATCH("数",B2:E2,0)),IF(C2="学",$C$1,""))

の中の「学」の部分は　学活（ホームルーム）と勝手に決めています。
すなわち「斉藤先生」の担任は「1B」にしていますので、時間割表に「学」があれば
C1セルを表示！というコトです。
先生によっては担任を持っていない先生もいらっしゃると思いますので、その先生の場合
IF(C2="学",$C$1)の部分は不必要で、すぐに "")　とすれば良いはずです。

「斉藤先生」は1・2年の「数」担当というコトにしていますので、
INDEX関数の範囲指定部は1A～2B（B1～E1）セルの「数」がある列の場合にクラス名を表示！
C列（1B）に「学」があればその行に「C1」セル（1B）を表示！としています。

前回も書いたようにすべての先生一人ひとりに数式を入れていかなければなりません。

※　元の時間割表に手を加えても良いのであれば、「国（斉藤）」といったようにすれば
一つの数式で列・行方向のオートフィルで対応できるような気がします。

こんなんで参考になりますかね？m(_ _)m

お礼

丁寧にありがとうございました。
よくわかりましたし、応用もできました。
ほんとうにありがとうございました。

回答No.8

tom04

こんばんは！
横からお邪魔します。

同じ時間に同科目があるというコトは先生が複数必要になると思います。

そこでSheet2に別表を作成しておきます。
（実際はSheet2は必要ありません）

質問では日々の各学年・各組の時間割に対してどの先生がどのクラスで授業を行っているか？
ということを把握したいということなので・・・

元の表配置を工夫すればもっと簡単になるかもしれませんが、ぱっ！っと思いつかないので、
今回は先生一人ひとりに数式を入れています。

↓の画像で
田中先生（I2セル）に
=IF(COUNTIF(B2:C2,"国"),INDEX($B$1:$C$1,,MATCH("国",B2:C2,0)),IF(B2="学",$B$1,""))

鈴木先生（J2セル）に
=IF(COUNTIF(D2:G2,"国"),INDEX($D$1:$G$1,,MATCH("国",D2:G2,0)),IF(G2="学",$G$1,""))

小林先生（K2セル）に
=IF(COUNTIF(B2:G2,"英"),INDEX($B$1:$G$1,,MATCH("英",B2:G2,0)),IF(D2="学",$D$1,""))

斉藤先生（L2セル）に
=IF(COUNTIF(B2:E2,"数"),INDEX($B$1:$E$1,,MATCH("数",B2:E2,0)),IF(C2="学",$C$1,""))

石田先生（M2セル）に
=IF(COUNTIF(F2:G2,"数"),INDEX($F$1:$G$1,,MATCH("数",F2:G2,0)),IF(E2="学",$E$1,""))

佐藤先生（N2セル）に
=IF(COUNTIF(B2:G2,"理"),INDEX($B$1:$G$1,,MATCH("理",B2:G2,0)),IF(F2="学",$F$1,""))
・・・
あとは「学」があるかないかだけですので、省略します。

とやっています。

※　もっと簡単な方法があるかもしれません。
この程度で失礼しました。m(_ _)m

お礼

ありがとうございます。
やってみないとわからないのですが、
なんとなくできるような気がしています。
ありがとうございました。

補足

ありがとうございます。例示の通りにはできたのですが、
実際に応用しようとするとうまくできませんでした。
勉強不足で申し訳ありませんが
IF(COUNTIF(B2:C2,"国"),INDEX($B$1:$C$1,,MATCH("国",B2:C2,0)),IF(B2="学",$B$1,""))の
後ろのIF以降がわからないのですが、意味を教えて頂けないでしょうか。よろしくお願いいたします。

回答No.7

MackyNo1

＞というように、時間割の右側に「田中」の授業２・３年国語と学活を、「小林」の授業１・２年の英語と学活を自動で抜き出すにはどうしたらいいのでしょうか。

「というように」と説明されていますが、抜き出したいのは、クラス名を元に「英」や「学」を表示したいということでしょうか？

その場合は、I2セルに以下のような数式を入力して下方向及び右方向にオートフィルコピーします（添付画像参照）

=IF(I2="","",INDEX($B2:$G2,MATCH(I2,$B$1:$G$1,0)))

＃ご質問内容だけでは、どのようなことがしたいのか不明確です。

このような場合は、皆さんからの回答が無駄にならないように、補足説明の要求に対して回答するようにしましょう。

補足

わかりにくくてすみません。
一人ひとりの先生が、何校時にどのクラスに行くのかを
抜き出したい、ということなのですが。
左の時間割はクラスから見たものですが、
その右に、教員から見た時間割を作りたいという意味です。
そうすれば、誰が何校時にどこの授業に行っているかわかりますよね。
上手に伝えられなくてすみません。
よろしくお願いいたします。

回答No.6

r-inarin

一番上の段の左側の１A、１B…はクラス名、右側の「田中」「小林」は教科担任名、また左側の列の（１）、（２）…は校時を表していると考えてよろしいでしょうか。
左側にある表は、ある曜日の全校の時間割ですね。やりたいことは、そのクラス毎の時間割の右側に、教科担任の時間割を作ると考えてよろしいでしょうか。
上記のことが正しいと言う前提で以下のように考えてみました。
表の中に各教科担任の担当教科名を入れて下さい。例えば「田中」なら同じ列の６校時の次の行に担当教科名＜国＞を「小林」も同様に＜英＞を入れて下さい。また、（６）の下に＜担当教科＞という項目名を入力して下さい。
一つの教科に２人以上の教科担任がいなければ簡単なのですが、同じ校時に国語や理科が２つあります。これは区別する必要があります。例えば田中先生の国語は＜国T＞にするなどして、同じ校時に同じ教科名がダブらないように区別してください。
式を入力する前に行と列を確認します。
■行
一番上の行（クラス名や教科担任名のある行）：１行
（１）の行（１校時）：２行
（２）の行（２校時）：３行
　…
（６）の行（６校時）：７行
担当教科の行：８行
担当クラスの行：９行
■列
（１）、（２）、（３）…の列：A列
田中の列：H列
小林の列：I列

また、(1)＜国、社、数、理…＞と(2)＜学＞とは区別して考えます。
(1)の場合
国語の教科担任の田中先生で考えます。まず(1)の行（１校時）に国語があるかどうか探します。あれば、該当するクラスを（１）の行と田中の列の交差するセル（H2）に転記します。なければ空白にします。そのセルに入力する式は以下のようになります。
=IF(ISNA(INDEX($B$1:$G$1,1,MATCH($H$8,$B2:$G2,0))),"",INDEX($B$1:$G$1,1,MATCH($H$8,$B2:$G2,0)))
この式をH7までcopyしてくだい。これで田中Tの国語の授業クラスが転記されました。
なお、式の意味は次の通りです。
MATCH($H$8,$B2:$G2,0)…国Tが見つかった場所は左からX番目
INDEX($B$1:$G$1,1,MATCH($H$8,$B2:$G2,0))…X番目のクラス名を転記する
IF(ISNA(INDEX($B$1:$G$1,1,MATCH($H$8,$B2:$G2,0))),""…国Tが見つからない場合（エラーの場合）は空白にする
もっと簡単にできるかもしれません。それは他の方にお願いしたいと思います。
(2)の場合
　特別な曜日の６校時に全校一斉に＜学＞があるなら簡単ですが、そうでない場合は私の力ではできません。ここは、手入力が手っ取り早いと思います。力不足ですみません。

お礼

ありがとうございました。
エクセル初心者には、エベレストに挑戦するような
難しさを感じています。
頑張って意味を理解しながら取り組んでみたいと思います。
ありがとうございました。

回答No.5

KURUMITO

関数で対処するにはあまりにも複雑で不可能に近いでしょう。
マクロを使って対処することにします。
データはシート1に有るとしてB1セルからG1セルには1A,1B,2A,2B,3A,3Bとクラス名が入力されているとします。
A2セル以降には（１）、（２）、・・・とA7セルまで入力されているとします。数、国などのデータがB2セルからG7セルに入力されているとします。
そこで、田中の文字をH1セルに、小林の文字をI1セルに入力します。その他の先生についても必要ならO1セルまでに入力するとします。
また、先生と担当する教科名やクラス名、学年などをデータベースとして用意することが必要です。ここではP1セルに田中と入力し、担当の教科として国をP2セルに、そのほかの教科が有るのでしたらP3セルに、その下の行に学活の時のクラス名を2Aと入力します。また、その下の行には担当する学年を入力します。例えば、P4セルには2,P5セルには3と入力します。これは田中は2年と3年のクラスで国を担当するなどの意味を持つことになります。同様に小林についてもO1セルに小林、O2セルに英、O3セルには１A,O4セルには1、O5セルには2などと入力します。他の先生についても右の列に同様に入力します。
これらの入力が済んでからマクロを操作することにします。
マクロの作成は次のようにします。
シート見出しでSheet1を右クリックして「コードの表示」を選択します。
表示される画面には次のコードを入力します。
Sub 担任割り当てクラス()
Dim Colm0 As Integer
Dim Colm1 As Integer
Dim Colm2 As Integer
Dim Row0 As Integer
Dim NAMAE As String
Dim KURASU As String
For Colm1 = 8 To 15
If Cells(1, Colm1) <> "" Then
NAMAE = Cells(1, Colm1)
Colm2 = WorksheetFunction.Match(NAMAE, Range("P1:X1"), 0) + 15
For Row0 = 2 To 7
For Colm0 = 2 To 7
If Cells(Row0, Colm0) = "学" Then
If WorksheetFunction.CountIf(Range(Cells(2, Colm2), Cells(20, Colm2)), Cells(1, Colm0)) > 0 Then
Cells(Row0, Colm1) = Cells(1, Colm0)
End If
End If
If WorksheetFunction.CountIf(Range(Cells(2, Colm2), Cells(20, Colm2)), Cells(Row0, Colm0)) > 0 Then
KURASU = Cells(1, Colm0)
If WorksheetFunction.CountIf(Range(Cells(2, Colm2), Cells(20, Colm2)), Left(KURASU, 1) * 1) > 0 Then
Cells(Row0, Colm1) = KURASU
End If
End If
Next
Next
End If
Next
End Sub
画面を閉じてからシート1に戻って「開発」タブの「マクロ」をクリックして
Sheet1.担任割り当てクラス
を選択して「実行」をクリックします。
お望みのデータがH列やI列に表示されます。なお、例のデータでは英数文字が使われていますがすべて半角英数文字で入力するようにしてください。一度こちらが示した通りの表を作成してからマクロを実行してみてください。後は応用です。頑張ってください。
なお、「開発」タブが表示されていない場合には「ファイル」タブから「オプション」をクリックし、「リボンのユーザー設定」から「開発」にチェックをします。、

お礼

ありがとうございます。
マクロを使うのですね。かなり高度な感じがしますが、
頑張ってやってみたいと思います。ありがとうございました。

回答No.4

noname#204879

(6)行目で、田中が２Ａ、小林が１Ａになっている理由は？
両方とも１Ａまたは２Ａ、あるいはそれぞれ１Ａ～３ＢのどれでもＯＫでは？

補足

すみません、(1)が１校時、(2)が２校時・・・ということで
田中が２Ａの担任、小林が１Ａの担任という意味です。
わかりにくい書き方ですみません。

回答No.3

bin-chan

＃１です。

> 確認しながら手で入力すればいいのですが
この「確認」とは何か？をお尋ねしてます。

補足

自分で田中先生は３校時に３Ｂと確認したら
田中先生の欄の３校時に「３Ｂ」と打ち込む
という意味です。
わかりやすく表現できずにすみません。

回答No.2

-9L9-

何だこの質問？
「導き出す」っていったって、何がどう関連しているのか全く意味不明な内容ですけど。
教員ならもっとロジカルに書いたらどうですか？

補足

わかりやすく書けずすみません。
それぞれの先生が何校時にどの教室へ授業に行くのか
ということを抜き出したいのですか。
よろしくお願いいたします。

回答No.1

bin-chan

列の区切り位置があいまいなこともあり、必要とされている「ルール」がわかりません。
数字と英字がどうなるの？

補足

すみません。縦の列がそれぞれのクラスの一日の授業です。
クラスの授業の並びからそれぞれの先生の授業の時間とクラスを
別の列の先生の授業欄に抜き出したい、という意味です。
よろしくお願いいたします。