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

Question

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

kagakusuki · Accepted Answer

回答番号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に時間割が自動的に作成されます。

MackyNo1 · Answer

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

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

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

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

kagakusuki · Answer

回答番号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に時間割が自動的に作成されます。

MackyNo1 · Answer

>つまり関数ではできないということですね。そんなことはありません。関数でも表示できます。ただし、複雑な配列数式を使用するので、実際のデータのレイアウトが少し変わるだけで、数式をメンテナンスできない可能性もあると思いますが、参考までに数式例を提示します。添付画像のレイアウトなら、Ｈ２セルに以下の式を入力して、右方向および下方向にオートフィルしてみてください。 =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)))&""

imogasi · Answer

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

kagakusuki · Answer

今仮に、元データが存在する上の表の中で、「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)),"")

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

kuroizell · Answer

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

moriaki555 · Answer

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

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

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

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

root_16 · Answer

難解なパズルですね。
　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の等級）
表も果物の種類別等級別の合計個数とかに
なりそう。
もしそうならピボットテーブルを作った方が早いと思います。

DIooggooID · Answer

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

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

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

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

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

回答番号ANo.5です。

この回答への補足

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

質問の表現に失敗してますね。

この回答への補足

この回答への補足

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

>表は、ひとつのセルに2つ入らなくてもいいです。

この回答への補足

難解なパズルですね。

この回答への補足

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

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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

　回答番号ANo.5です。

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