データを表にする関数を教えてください。

質問

1果物A1,ナシ
2果物B4,リンゴ
3果物C2,モモ
4果物A1,モモ
5果物C2,リンゴ
というデータがあって、それを
　ABCDE
１
2
3
4
の表に関数で挿入したいのですが、できますでしょうか？データは200行ぐらいあります。
vlookupでA1で引っ張ると一種類しか引っ張れないので…
表は、ひとつのセルに2つ入らなくてもいいです。とりあえず表的な形になればよいです。
お願いします。

回答No.10

kagakusuki · ベストアンサー

回答番号ANo.5,8です。

>仮に、月曜１限に１年の英語と数学の授業があったらどうなりますでしょうか？

　ANo.8そのままのやり方ですと、【関数と作業列を使用する方法】の場合は、該当する条件を満たしているデータの内、元データの表中で、最も上の行（行番号が若いもの）の科目のみが表示されます。
　一方、【作業列を使用せず（Sheet3は不要）に、関数のみで処理を行う方法】の場合は、該当する条件を満たしているデータが存在している全ての行の、行番号を合計した数と同じ行番号を持つ行のデータが表示されます。
　例えば、

1行目：見出し
2行目：月、1、1、英語、田中
3行目：月、1、1、数学、松本
4行目：水、3、2、国語、佐藤
5行目：火、2、1、理科、後藤

の場合は、2行目と3行目が共に「月曜日の1年の1時限目」になっていますから、「月曜日の1年の1時限目」を表示するSheet2のB2セルには、
【関数と作業列を使用する方法】では、2行目と3行目の中で最も上にある2行目のデータである、「英語」が表示されます。
　一方、【作業列を使用せず（Sheet3は不要）に、関数のみで処理を行う方法】では、2行目と3行目の行番号である2と3を合計した5行目のデータである、「理科」が表示されてしまいます。

　同じ時間に同じ学年に対して別の授業があるという事は、複数のクラス（学級）に分けて、授業が行われるという事なのでしょうから、その場合には、次の様にされては如何でしょうか。
　まず、元データのC列に入力するデータを、学年ではなく、「1-A」や「2-C」といった、半角のハイフォンで学年と級を結んだクラスの名前を入力して下さい。
　又、Sheet2のC1:F1の結合されたセルには「月」と入力し、同様にG1:J1には「火」、K1:N1には「水」と入力し、
2行目には各曜日ごとの時限を入力し、
A列の縦に結合されたセルには学年を入力し、
B列の3行目以下には各級名を入力して下さい。

【関数と作業列を使用する方法】
　まず、Sheet3のA1セルに次の数式を入力して下さい。

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())="",INDEX(Sheet1!$C:$C,ROW())="",INDEX(Sheet1!$D:$D,ROW())=""),"",INDEX(Sheet1!$A:$A,ROW())&INDEX(Sheet1!$B:$B,ROW())&"/"&INDEX(Sheet1!$C:$C,ROW()))

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。

　次に、Sheet2のC3セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet3!$A:$A,INDEX($A$1:C$1,MATCH("゛",$A$1:C$1,-1))&C$2&"/"&VLOOKUP(9,$A$1:$A3,1)&"-"&$B3),INDEX(Sheet1!$D:$D,MATCH(INDEX($A$1:C$1,MATCH("゛",$A$1:C$1,-1))&C$2&"/"&VLOOKUP(9,$A$1:$A3,1)&"-"&$B3,Sheet3!$A:$A,0)),"")

　次に、Sheet2のC3セルをコピーして、Sheet2において科目を表示させる可能性のある全てのセルに貼り付けて下さい。
　これで、Sheet2に時間割が自動的に作成されます。

【作業列を使用せず（Sheet3は不要）に、関数のみで処理を行う方法】
　※但し、計算処理に要する、パソコンの負荷は大きくなります。（尤も、Sheet1の元の表は、100行にも満たない行数ですから、その程度の行数であれば大した負荷にはなりませんので、全く問題にはならないと思います）

　まず、Sheet2のC3セルに次の数式を入力して下さい。

=IF(SUMPRODUCT(ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH("゛",Sheet1!$A:$A,-1)))*(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("゛",Sheet1!$A:$A,-1))=INDEX($A$1:C$1,MATCH("゛",$A$1:C$1,-1)))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH("゛",Sheet1!$A:$A,-1))=C$2)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH("゛",Sheet1!$A:$A,-1))=VLOOKUP(9,$A$1:$A3,1)&"-"&$B3))=0,"",INDEX(Sheet1!$D:$D,SUMPRODUCT(ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH("゛",Sheet1!$A:$A,-1)))*(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("゛",Sheet1!$A:$A,-1))=INDEX($A$1:C$1,MATCH("゛",$A$1:C$1,-1)))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH("゛",Sheet1!$A:$A,-1))=C$2)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH("゛",Sheet1!$A:$A,-1))=VLOOKUP(9,$A$1:$A3,1)&"-"&$B3))))

　次に、Sheet2のC3セルをコピーして、Sheet2において科目を表示させる可能性のある全てのセルに貼り付けて下さい。
　これで、Sheet2に時間割が自動的に作成されます。

お礼

ありがとうございます。
まず、自分のデータの作り方から再構築して試してみようと思います。
その際に、また質問するかもしれませんが、よろしくお願いします。

回答No.9

MackyNo1

>仮に、月曜１限に１年の英語と数学の授業があったらどうなりますでしょうか？

No7の回答が、そのものの答えだと思うのですが、試されていないのでしょうか？

お分かりになると思って、説明しませんでしたが、元データの学年部分が「1年」ではなく「１」と入力されているなら、集計表の学年部分の入力も「１」にしてください。

もし私の回答でうまくいかないのでしたら、どのような問題があるのか具体的に例示してください。

回答No.8

kagakusuki

回答番号ANo.5です。

>実際には時間割データを時間割に加工したいと思っています。

　添付画像を御覧下さい。
　画像の左上に写っているのが元データの表があるSheet1、右上に写っているのが作業列があるSheet3、下に写っているのが結果を表示させるSheet2です。
　Sheet1のA列には曜日、B列には時限、C列には学年、D列には科目が、それぞれ2行目以下に入力されています。
　Sheet2のB1:E1の結合されたセルには「月」と入力されていて、同様にF1:I1には「火」、J1:M1には「水」と入力されていて、2行目には各曜日ごとの時限が入力されていて、A列の3行目以下には学年が入力されています。
　尚、ここでは仮に、1日は4時限目までしか記述してませんが、1日の時限が何時限であっても（日によって授業の時限の数が異なっていても）、又、学年が何学年まであったとしても、関数を修正する必要は御座いません。

　この様にする方法に関して、2通りの方法を回答させて頂きます。

【関数と作業列を使用する方法】
　まず、Sheet3のA1セルに次の数式を入力して下さい。

=IF(OR(Sheet1!$A1="",Sheet1!$B1="",Sheet1!$C1="",Sheet1!$D1=""),"",INDEX(Sheet1!$A:$A,ROW())&INDEX(Sheet1!$B:$B,ROW())&"/"&INDEX(Sheet1!$C:$C,ROW()))

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。

　次に、Sheet2のB3セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet3!$A:$A,INDEX($A$1:B$1,MATCH("゛",$A$1:B$1,-1))&B$2&"/"&$A3),INDEX(Sheet1!$D:$D,MATCH(INDEX($A$1:B$1,MATCH("゛",$A$1:B$1,-1))&B$2&"/"&$A3,Sheet3!$A:$A,0)),"")

　次に、Sheet2のB3セルをコピーして、Sheet2のB4セルとB5セルに貼り付けて下さい。
　次に、Sheet2のB3～B5の範囲をコピーして、同じ行のB列よりも右方向にあるセル範囲に貼り付けて下さい。
　これで、Sheet2に時間割が自動的に作成されます。

【作業列を使用せず（Sheet3は不要）に、関数のみで処理を行う方法】
※但し、計算処理に要する、パソコンの負荷は大きくなります。（尤も、Sheet1の元の表は、100行にも満たない行数ですから、その程度の行数であれば大した負荷にはなりませんので、全く問題にはならないと思います）

　まず、Sheet2のB3セルに次の数式を入力して下さい。

=IF(SUMPRODUCT(ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9,Sheet1!$C:$C)))*(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9,Sheet1!$C:$C))=INDEX($A$1:B$1,MATCH("゛",$A$1:B$1,-1)))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9,Sheet1!$C:$C))=B$2)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9,Sheet1!$C:$C))=$A3))=0,"",INDEX(Sheet1!$D:$D,SUMPRODUCT(ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9,Sheet1!$C:$C)))*(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9,Sheet1!$C:$C))=INDEX($A$1:B$1,MATCH("゛",$A$1:B$1,-1)))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9,Sheet1!$C:$C))=B$2)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9,Sheet1!$C:$C))=$A3))))

　次に、Sheet2のB3セルをコピーして、Sheet2のB4セルとB5セルに貼り付けて下さい。
　次に、Sheet2のB3～B5の範囲をコピーして、同じ行のB列よりも右方向にあるセル範囲に貼り付けて下さい。
　これで、Sheet2に時間割が自動的に作成されます。

補足

できました!!　ありがとうございます。半ば、諦めていました…
ここまで作っていただいて、質問するのもあつかましいですが、
仮に、月曜１限に１年の英語と数学の授業があったらどうなりますでしょうか？

回答No.7

MackyNo1

>つまり関数ではできないということですね。

そんなことはありません。関数でも表示できます。

ただし、複雑な配列数式を使用するので、実際のデータのレイアウトが少し変わるだけで、数式をメンテナンスできない可能性もあると思いますが、参考までに数式例を提示します。

添付画像のレイアウトなら、Ｈ２セルに以下の式を入力して、右方向および下方向にオートフィルしてみてください。

=INDEX($D:$D,SMALL(INDEX((($C$2:$C$100<>$G2)+($A$2:$A$100&$B$2:$B$100<>H$1))*1000+ROW($A$2:$A$100),),COUNTIF($G$2:G2,$G2)))&""

回答No.6

imogasi

質問の表現に失敗してますね。判らないことだらけだ。
エクセルの使用経験が少ないのだろう。
ーー
補足後もよくわからない。
例えば教員名は捨てるのか？
ーー
質問は表の組み換えの問題で、これは関数に適した分野でなく、これぐらい複雑になるとＶＢＡでも使わないと
やりにくい。
私は、エクセルを仕事絡みに使うなら、ＶＢＡが出来ないと、手も足も出ないと思っている。
ー
１つは将来作りたい表の形式・様式・レイアウトを見通して、元の表を作っておくことだ。
それもなされていない。
ー
もし基データがカンマ区切りのデータがあるなら、コピーして、エクセルシート（のＡ列、Ａ１を基点にして）に貼り付けて、データー区切り位置ー区切り文字をカンマにして、各列のセルのデータにして、それから考える（質問する）こと。
ーーー
データ例の書き方は
Sheet1で
A1:E8
曜日 時限 学年 科目名 教員
月 1 1 英語 田中
月 1 1 数学 松本
水 3 2 国語 佐藤
火 2 1 英語 田中
水 1 1 理科 後藤
火 2 2 理科 後藤
火 2 2 国語
のようなデータ例を挙げて質問するものだ。
結果表も
Sheet2の
A1:J5
- 月1 月2 月3 火1 火2 火3 水1 水2 水3
1年 英語 - - - 英語 - 理科
1年 数学
2年 - - - - 理科 - - - 国語
2年 - - - - 国語
のように書いて質問すべきだ。
ーーー
同じ時限で行われる科目は最大いくつか？
１日の時限は３つまでか？
なども書いてないと解答できないと思う。
ＶＢＡは出来ないと思うのでとりあえず回答は書かないが。

補足

つまり関数ではできないということですね。
勉強になりました。ありがとうございます。

回答No.5

kagakusuki

今仮に、元データが存在する上の表の中で、「A1」や「B4」といった位置情報が入力されているのが、Sheet2のC列で、「ナシ」や「リンゴ」といった果物の種類が入力されているのが、Sheet2のD列であり、下の表で「A」と入力されているのが、Sheet1のB2セルであるものとします。
　又、Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA1セルに次の数式を入力して下さい。

=IF(INDEX(Sheet2!$C:$C,ROW())="","",INDEX(Sheet2!$C:$C,ROW())&"/"&COUNTIF(INDEX(Sheet2!$C:$C,1):INDEX(Sheet2!$C:$C,ROW()),INDEX(Sheet2!$C:$C,ROW())))

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。

　次に、Sheet1のB2セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/1"),INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/1",Sheet3!$A:$A,0)),"")&IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/2"),","&INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/2",Sheet3!$A:$A,0)),"")&IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/3"),","&INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/3",Sheet3!$A:$A,0)),"")

　そして、Sheet1のB2セルをコピーして、Sheet1のB2～E6の範囲に貼り付けて下さい。

　尚、上記のSheet1のB2の関数は、1つのセルに入る果物の種類が、3種類までの場合に対応するもので、4種類以上は、1つのセル内には表示出来ません。
　もし、1つのセル内に4種類まで表示させる場合には、

=IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/1"),INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/1",Sheet3!$A:$A,0)),"")&IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/2"),","&INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/2",Sheet3!$A:$A,0)),"")&IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/3"),","&INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/3",Sheet3!$A:$A,0)),""))),"")&IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/4"),","&INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/4",Sheet3!$A:$A,0)),"")

5種類まで表示させる場合には、

=IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/1"),INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/1",Sheet3!$A:$A,0)),"")&IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/2"),","&INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/2",Sheet3!$A:$A,0)),"")&IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/3"),","&INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/3",Sheet3!$A:$A,0)),""))),"")&IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/4"),","&INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/4",Sheet3!$A:$A,0)),""))),""))),"")&IF(COUNTIF(Sheet3!$A:$A,B$1&$A2&"/5"),","&INDEX(Sheet2!$D:$D,MATCH(B$1&$A2&"/5",Sheet3!$A:$A,0)),"")

という具合に、適時、関数を変更して下さい。

補足

すみません、データをサンプルで表示したのが悪いみたいですね。
実際には時間割データを時間割に加工したいと思っています。
データは、Excelで左から列毎に曜日、時限、学年、科目名、教員[コンマ区切り]となっています。
1行目：見出し
2行目：月、1、1、英語、田中
3行目：月、1、1、数学、松本
4行目：水、3、2、国語、佐藤
5行目：火、2、1、英語、田中
6行目：水、1、1、理科、後藤
7行目：火、2、2、理科、後藤
8行目：火、2、2、国語
といった感じのデータを、
　月1 月2 月3 火1 火2 火3 水1 水2 水3
1年英語　　　　　　　　英語　　　理科　　　　　　　
数学
2年　　　　　　　　　　理科　　　　　　　　国語
　　　　　　　　　　 国語

といった表にしたいと思っています。
わかりにくくてすいませんでした。お力添えをよろしくお願いします。

回答No.4

kuroizell

ピボットテーブルを使うと幸せになれる話のように見えます。

回答No.3

moriaki555

>表は、ひとつのセルに2つ入らなくてもいいです。
意味がわかりません…。

まず、最初に。
元となる「1果物A1,ナシ」云々というデータが、どんな形であるのか、
それを明確にして下さい。Excelファイルで、データがセル区切りされているなら
どこで区切られているかを書いて下さい。
txtデータなら、スペースで区切られたtxtファイルなのか、カンマで区切られた
テキストなのか。

次に集計の問題です。
集計は縦軸と横軸の要素を明確に指示して下さい。
書かれている縦軸の1234とは何ですか？　行列番号ですか？
横軸のABCDEは何を示してますか？　アイテム内のアルファベットですか？
最終的に集計表の中には何を集計すればいいんですか？　個数ですか？

>とりあえず表的な形になればよいです。
この指示で解る人はいないと思います。こういう指示のしかたは、止めた方が良いですよ。
間違いを誘発するだけです。

補足

すみません、データをサンプルで表示したのが悪いみたいですね。
実際には時間割データを時間割に加工したいと思っています。
データは、Excelで左から列毎に曜日、時限、学年、科目名、教員[コンマ区切り]となっています。
1行目：見出し
2行目：月、1、1、英語、田中
3行目：月、1、1、数学、松本
4行目：水、3、2、国語、佐藤
5行目：火、2、1、英語、田中
6行目：水、1、1、理科、後藤
7行目：火、2、2、理科、後藤
8行目：火、2、2、国語
といった感じのデータを、
　月1 月2 月3 火1 火2 火3 水1 水2 水3
1年英語　　　　　　　　英語　　　理科　　　　　　　
数学
2年　　　　　　　　　　理科　　　　　　　　国語
　　　　　　　　　　 国語

といった表にしたいと思っています。
わかりにくくてすいませんでした。お力添えをよろしくお願いします。

回答No.2

root_16

難解なパズルですね。
　ABCDE
１
2
3
4
この説明がまずい点は
１
2
3
4
が行番号（1～5）を表しているか分からない点。
A1、B4、C2、A1、C2には1,2,4が含まれているので
アルファベット横の数字を表している可能性が残る。

　ABCDE
何故かA1、B4、C2、A1、C2で示した
アルファベット横の数字が無い。
説明が必要。

A1、ナシ
A1、モモ
C2、モモ
B4、リンゴ
C2、リンゴ
と見ると、A1～B4は果物の種類を指していない。
もし大きさや等級を表しているなら
ナシ、A1（まずナシであってA1の等級）
表も果物の種類別等級別の合計個数とかに
なりそう。
もしそうならピボットテーブルを作った方が早いと思います。

補足

すみません、データをサンプルで表示したのが悪いみたいですね。
実際には時間割データを時間割に加工したいと思っています。
データは、Excelで左から列毎に曜日、時限、学年、科目名、教員[コンマ区切り]となっています。
1行目：見出し
2行目：月、1、1、英語、田中
3行目：月、1、1、数学、松本
4行目：水、3、2、国語、佐藤
5行目：火、2、1、英語、田中
6行目：水、1、1、理科、後藤
7行目：火、2、2、理科、後藤
8行目：火、2、2、国語
といった感じのデータを、
　月1 月2 月3 火1 火2 火3 水1 水2 水3
1年英語　　　　　　　　英語　　　理科　　　　　　　
数学
2年　　　　　　　　　　理科　　　　　　　　国語
　　　　　　　　　　 国語

といった表にしたいと思っています。
わかりにくくてすいませんでした。お力添えをよろしくお願いします。

回答No.1

DIooggooID

＞　とりあえず表的な形になればよいです。

　　最終的な　表　の内容を示していただかないと、　どのように処理すれば良いか、
　判断できません。

補足

ピボットは合計を表示してしまうのですが、データをそのまま表示する方法があるのでしょうか？