EXCELの関数について

質問

いつもお世話になってます。ありがとうございます。

excelの関数についてお聞きしたいことがあり質問しました。

　　　　 A列 B列 C列　…
　　 　 　 a 　 c 　 e　 g　 i
1行　2日 ○　 △　□ ●　■

となっているデータを

　　　　　A列 B列 C列　…
　　 　　a 　 b 　 c 　d 　 e　 f　 g　 h　 i …
1行　1日
2行　2日
3行　3日

こんなシートに自動的に打ち込めるようにしたいのです。

　　　　　A列 B列 C列　…
　　 　　a 　 b 　 c 　d 　 e　 f　 g　 h　 i …
1行　1日
2行　2日○　　　　△　　　　□　　　　●　　　 ■
3行　3日

が完成図イメージです。

一番上の色々なデータから表を完成させたいのですがどうすればできるでしょうか。
vlookupやhlookupでは一行ずつの打ち直しが必要になってしまいます。
よろしくお願いします。

回答No.3

MASUKUBO · ベストアンサー

シート１では１行目には項目名が有って、２行目のA２セルから右に例えばX2せるまでデータを入力するとした場合に、常にそのデータをシート２の該当する日にちの場所に表示させるとしても、２行目に入力するデータが次々に変わっていく場合にそれらのデータを次々にシート２に表示させることはマクロでなければできません。
関数を使ってシート２にデータを表示させるようにするためにはシート１で２行目だけにデータを入力するのではなく、日にちが変わるごとに下の行にデータを入力していくことが必要です。
その場合にシート２に表示させるとしたら次のようにします。
シート２の１行目にはシート１で使っている同じ項目名を入力します。空白の列が有っても構いません。
シート２のA２セルから下方には１日、２日、・・・と日付を入力します。
B２セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(COUNTIF(Sheet1!$A:$A,$A2)=0,COUNTIF(Sheet1!$1:$1,B$1)=0),"",VLOOKUP($A2,Sheet1!$A:$X,MATCH(B$1,Sheet1!$1:$1,0),FALSE))

お礼

回答ありがとうございます。
＞＞２行目に入力するデータが次々に変わっていく場合にそれらのデータを次々にシート２に表示させることはマクロでなければできません。
関数を使ってシート２にデータを表示させるようにするためにはシート１で２行目だけにデータを入力するのではなく、日にちが変わるごとに下の行にデータを入力していくことが必要です。

やはりマクロが必要ですか。日にちが変わるごとにデータを入力はできれば避けたいのでマクロでやろうと思いますが、具体的にどうすれば良いのでしょうか。
マクロは同じ操作しかできないはずですが、可能なのでしょうか。

回答No.4

kagakusuki

回答番号ANo.2です。

>上記の表をいくつもつくるだけで

　上の表ではA2セルに「1日」と入力されているのに対し、2番目の表のA2セルには「4日」と入力されている事から考えますと、上の表と2番目の表の並び方は、

　　　　　A列　 B列　 C列　 D列　 E列　 F列　 G列　 H列　 I列　 J列
1行目　　　　　　a　　 c　　　e　　　g　　　i　　　b　　　d　　　h　　f
2行目 　1日　　ノ　　ケ　　 リ　　 ム　　 ラ　　チ　　 セ　　 ロ　　 レ
3行目（空欄）
4行目　　　　　　n　　 h　　　a　　　k　　　x　　　b　　　d　　　h　　f
5行目 　4日　　110　　120　　 140　　 ム　　 ラ　　チ　　 セ　　 ロ　　 レ

という様な形で、一つのシート状に複数の表が並んでいるのではなく、
例えば、Sheet3には、

　　　　　A列　 B列　 C列　 D列　 E列　 F列　 G列　 H列　 I列　 J列
1行目　　　　　　a　　 c　　　e　　　g　　　i　　　b　　　d　　　h　　f
2行目 　1日　　ノ　　ケ　　 リ　　 ム　　 ラ　　チ　　 セ　　 ロ　　 レ

Sheet4には、

　　　　　A列　 B列　 C列　 D列　 E列　 F列　 G列　 H列　 I列　 J列
1行目　　　　　　n　　 h　　　a　　　k　　　x　　　b　　　d　　　h　　f
2行目 　4日　　110　　120　　 140　　 ム　　 ラ　　チ　　 セ　　 ロ　　 レ

という具合に、複数のシート上に、各々1枚ずつの表が存在していると考えて宜しいのでしょうか？

　　もしそうだとしますと、次の様な方法があります。
　今仮に、適当な空きシート（ここでは仮にSheet2）に作業列を設けておき、Sheet1に並べ替えた結果を表示させるものとします。
　まず、Sheet2のB列に

1行目　　Sheet3
2行目　　Sheet4
3行目　　Sheet5
4行目　　Sheet6

という具合に、元データの表が存在している各Sheetのシート名を、全て入力して下さい。
　尚この時、シート名「Sheet1(2)」やシート名「1」の様に、そのSheetを参照する関数を作った際に、「'Sheet1(2)'!」や「'1'!」という具合に、シート名が「'　'」で括られる様になるシート名を入力する際には、

="'Sheet1(2)'"

="'1'"

という具合に、シート名を「="'　'"」で括った形で入力して下さい。（参照した際に「'　'」で括られないSheetに関しては、単純にシート名をそのまま入力して下さい）
　次に、Sheet2のC1セルに次の関数を入力して下さい。

=IF(ISERROR(INDIRECT($B1&"!A2")),"",INDIRECT($B1&"!A2"))

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

=IF($C1="","",SUBSTITUTE(INDIRECT($B1&"!A2"),"日",)*1)

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

=IF(ISNUMBER($D1),$D1+COUNTIF($D$1:$D1,$D1)/COUNTIF($D:$D,$D1),"")

　次に、Sheet2のC1～D1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。
　次に、Sheet2のA1セルをコピーして、Sheet2のA2以下に貼り付けて下さい。

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

=IF(ROWS($2:2)>COUNT(Sheet2!$A:$A),"",VLOOKUP(SMALL(Sheet2!$A:$A,ROWS($2:2)),Sheet2!$A:$C,3,FALSE))

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

=IF(ROWS($2:2)>COUNT(Sheet2!$A:$A),"",IF(COUNTIF(INDIRECT(VLOOKUP(SMALL(Sheet2!$A:$A,ROWS($2:2)),Sheet2!$A:$B,2,FALSE)&"!1:1"),B$1)=0,"",HLOOKUP(B$1,INDIRECT(VLOOKUP(SMALL(Sheet2!$A:$A,ROWS($2:2)),Sheet2!$A:$B,2,FALSE)&"!1:2"),2,FALSE)))

　次に、Sheet1のB2セルをコピーして、（表中の）Sheet1のB2よりも右側にあるセルに貼り付けて下さい。
　次に、Sheet1の2行目で、関数が入力されている全てのセルを含むセル範囲をコピーして、Sheet1の3行目以下に貼り付けて下さい。

　以上で準備は完了で、後はSheet1のB1セルから右方向に向かって、

　　　　　A列　 B列　 C列　 D列　 E列　 F列　 G列　 H列　 I列　 J列
1行目　　　　　　a　　 c　　　 e　　　 g　　　 i　　　 b　　　 d　　　 h　　 f

という具合に、1行目の項目を入力しますと、並べ替えた結果が自動的に表示されます。

お礼

回答ありがとうございます。

回答No.2

kagakusuki

やりたいことが良く解らないのですが、もしかしますと、例えばSheet1に

　　　　　A列　 B列　 C列　 D列　 E列　 F列　 G列　 H列　 I列　 J列
1行目　　　　　　a　　 c　　　e　　　g　　　i　　　b　　　d　　　h　　f
2行目 　2日 ○　　△　　□　　●　　 ■　　▽　　 ◆　　 △　　 ▲
3行目 　3日 あ　　 う　　 き　　 と　　 ま　　 い　　 し　　 て　　 こ
4行目 　1日 ノ　　 リ　　 ラ　　 セ　　 レ　　 ケ　　 ム　　 ロ　　 チ

などと並んでいる場合に、Sheet2に

　　　　　A列　 B列　 C列　 D列　 E列　 F列　 G列　 H列　 I列　 J列
1行目　　　　　　a　　 c　　　e　　　g　　　i　　　b　　　d　　　h　　f
2行目 　1日
3行目 　2日
4行目 　3日

などと入力しておけば、

　　　　　A列　 B列　 C列　 D列　 E列　 F列　 G列　 H列　 I列　 J列
1行目　　　　　　a　　 c　　　e　　　g　　　i　　　b　　　d　　　h　　f
2行目 　1日　　ノ　　ケ　　 リ　　 ム　　 ラ　　チ　　 セ　　 ロ　　 レ
3行目 　2日　　○ ▽　　 △　　◆　　 □　　▲　　 ●　　 △　　■
4行目 　3日　　あ　 い　　 う　　 し　　 き　　 こ　　 と　　 て　　 ま

という具合に、Sheet1のデータを、Sheet2に並べ替えて表示させたいという事なのでしょうか？
　もしそうだとしますと、Sheet1のB2セルに次の数式を入力してから、Sheet1のB2セルをコピーして、Sheet1のB2～J4の範囲に貼り付けると良いと思います。

=IF(OR(COUNTIF(Sheet1!$A:$A,$A2)=0,COUNTIF($A$1:$J$1,B$1)=0),"",INDEX(Sheet1!$A$1:$J$4,MATCH($A2,Sheet1!$A:$A,0),MATCH(B$1,$A$1:$J$1,0)))

お礼

回答ありがとうございます。
わかりづらくて申し訳ございません。
回答いただいた表で説明しますと、何月何日という日にちのA列と、1行目の項目、そして日にちと項目のクロスする部分の合計３つの欄が変わるります。
具体的にはこんな表がいくつも(1～31日の31日分)あります。

　　　　　A列　 B列　 C列　 D列　 E列　 F列　 G列　 H列　 I列　 J列
1行目　　　　　　a　　 c　　　e　　　g　　　i　　　b　　　d　　　h　　f
2行目 　1日　　ノ　　ケ　　 リ　　 ム　　 ラ　　チ　　 セ　　 ロ　　 レ

　　　　　A列　 B列　 C列　 D列　 E列　 F列　 G列　 H列　 I列　 J列
1行目　　　　　　n　　 h　　　a　　　k　　　x　　　b　　　d　　　h　　f
2行目 　4日　　110　　120　　 140　　 ム　　 ラ　　チ　　 セ　　 ロ　　 レ

上記の表をいくつもつくるだけで
自動的に下記の表を得たいのです。

　　　　　A列　 B列　 C列　 D列　 E列　 F列　 G列　 H列　 I列　 J列
1行目　　　　　　a　　 c　　　e　　　g　　　i　　　b　　　d　　　h　　f
2行目 　1日　　ノ　　ケ　　 リ　　 ム　　 ラ　　チ　　 セ　　 ロ　　 レ
3行目 　2日　　○ ▽　　 △　　◆　　 □　　▲　　 ●　　 △　　■
4行目 　3日　　あ　 い　　 う　　 し　　 き　　 こ　　 と　　 て　　 ま

ここで問題になっているのが、1日分であればhlookupなどを使って、関数を入力できるのですが、
31日分も参照するセルを変えて作らなければいけない点です。
これをなんとか解消したいのですが…

回答No.1

佐藤 志緒（@g4330）

元の表の1行目を右クリックして「行の挿入」を選んでください。
そうすれば「1日」を追加できます。

お礼

回答ありがとうございます。