Excelで２つの表を１つにまとめるには？

質問

下図のように、２種類の表があります。
これを、１つの表にまとめ、日付順に並ばせたいのですが
マクロを使わないで数式だけで行うにはどうしたらよいでしょうか。

データを手で打ち直すことは考えていません。（実際にはもっと項目が横に長い表です。）
「コピー→貼り付け→並び替え」という作業も考えていません。

・下図では、データが「Ａ組」では４つ、「Ｂ組」では５つとなっていますが、
　実際には２０個程度のデータ（数は不定）が入ります。

・また、文字データ（名前、教科）と数値データ（得点）とが混在しています。

・項目に当たる部分は両方の表とも同じものが入ります。

・日付、名前は重複する場合があります。

回答No.1

tom04 · ベストアンサー

こんにちは！
一例です。
↓の画像のような配置になっていて、
Sheet1はA組・Sheet2がB組の表となっていて、両SheetともA1セルに組名を入力
データは3行目からあるとします。
これらをSheet3に表示するようにしてみました。

両Sheetに作業用の列を設けます。
Sheet1の作業列E3セルに
=IF(COUNTBLANK(A3:D3),"",COUNTIF(A:A,"<"&A3)+COUNTIF(A$3:A3,A3)+COUNTIF(Sheet2!A:A,"<"&A3))
という数式を入れオートフィルでずぃ～～～！っと下へしっかりコピーしておきます。
（これ以上データはない！というくらいまでコピーしておいても構いません）

Sheet2のE3セルに
=IF(COUNTBLANK(A3:D3),"",COUNTIF(A:A,"<"&A3)+COUNTIF(A$3:A3,A3)+COUNTIF(Sheet1!A:A,"<="&A3))
という数(s)気を入れこちらもオートフィルでしっかり下へコピー！
※　数式が微妙に違う（等号がある・ない）コトに注意してください。

最後にSheet3のA2セルに
=IF(COUNTIF(Sheet1!$E:$E,ROW(A1)),INDEX(Sheet1!$A:$E,MATCH(ROW(A1),Sheet1!$E:$E,0),MATCH(A$1,Sheet1!$A$2:$D$2,0)),IF(COUNTIF(Sheet2!$E:$E,ROW(A1)),INDEX(Sheet2!$A:$E,MATCH(ROW(A1),Sheet2!$E:$E,0),MATCH(A$1,Sheet2!$A$2:$D$2,0)),""))

これをそのまま列方向にオートフィルという訳にはいかないようです（B列に組を表示するため）
貼りつけたA2セル上で右クリック　→　コピー　→　C2セルを選択　→　貼り付け
C2セルをE2セルまでオートフィルでコピー！

そしてB2セルには
=IF(COUNTIF(Sheet1!E:E,ROW(A1)),Sheet1!A$1,IF(COUNTIF(Sheet2!E:E,ROW(A1)),Sheet2!A$1,""))
という数式を入れておきます。

A2セルの表示形式を日付にし、
A2～E2セルを範囲指定　→　E2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

以上、長々と書きましたが参考になりますかね？m（_ _）m

お礼

ありがとうございます。

１つのシート内で完結させたかったため、いったんシートを分けて式を入力した後、
「切り取り＆貼り付け」で一つのシートにまとめました。
式も崩れること無く反映できました。

あらかじめ順序判定用の作業列を作れば良かったのですね、気づきませんでした。
（邪魔なら非表示にすれば良いだけですね。）

どうやら上手くできたようです。
感謝します！

回答No.4

某HN クロメート(Chromate)（@CoalTar）

A組の表、B組の表 ともに昇順で並んでいるとして
同じ日付は A組を優先とする

何行目になるか：E3セルに
=IF(A3="","",COUNTIF(G:G,"<"&A3)+COUNT(A$3:A3))
下へオートフィル

何行目になるか：K3セルに
=IF(G3="","",COUNTIF(A:A,"<="&G3)+COUNT(G$3:G3))
下へオートフィル

何行分になるか：M1セル =MAX(E:E,K:K)
連番：M2セル =IF(COUNT(M$1:M1)>$M$1,"",COUNT(M$1:M1))
下へオートフィル

どちらの組か判断：P2セル
=IF(M2="","",IF(ISNA(MATCH(M2,E:E,0)),"B組","A組"))

何行目にあるか：N2セル
=IF(M2="","",MATCH(M2,IF(P2="A組",E:E,K:K),0))
下へオートフィル

日付を持ってくる：O2セル
=IF(M2="","",INDEX(IF(P2="A組",A:A,G:G),N2))
下へオートフィル

名前、教科、得点をもってくる：Q2セル
=IF($M2="","",INDEX(IF($P2="A組",B:B,H:H),$N2))
右へ下へオートフィル

お礼

やはり判定行を設けるということなのですね。
個々の式が簡略で分かりやすい説明でした。

「A組の表、B組の表 ともに昇順で並んでいるとして」の条件が
こちらが提示した例では満たされているのですが、実際のデータでは１００％満たされる
訳では無かったので、エラーが出てしまいました。
例が良くなかったですね、すみません。

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

回答No.3

K Kazz（@JazzCorp）

このリストの実存的イミは？？

補足

この例はあくまでダミーです。そのため、意味はありません。

実際に入るデータは名前でも教科でも点数でもないです。
実際に使用しているデータと符合しているのは日付だけです。

質問の意図は「表の合算方法を知りたい」という事でしたので、誰にでも分かりやすい、リストとしてありそうな項目を例として選んだだけです。

回答No.2

KURUMITO

Ａ組の表はシート1のＡ列からＤ列まで、Ｂ組の表はＧ列からＪ列までにあるとして2行目に項目名で3行目から下方にそれぞれのデータが入力されているとします。
シート1には作業列を作って対応し、シート2にお求めの表を表示させるとします。
シート1のＭ2セルには次の式を入力して下方に（Ｍ100よりも下行まで）ドラッグコピーします。

=IF(ROW(A1)<=COUNT(A:A),INDEX(A$3:A$100,ROW(A1))-0.5+COUNTIF(A$2:A2,A3)/1000,IF(ROW(A1)>COUNT(A:A)+COUNT(G:G),"",INDEX(G$3:G$100,ROW(A1)-COUNT(A:A)+COUNTIF(A$2:A2,A3)/1000)))

Ｎ２セルには次の式を入力して下方にドラッグコピーします。

=IF(M2="","",RANK(M2,M:M,1))

Ｏ2セルには次の式を入力して下方にドラッグコピーします。

=IF(N2="","",IF(MOD(M2,1)<>0,"A"&COUNTIF(O$1:O1,"A*")+3,"B"&COUNTIF(O$1:O1,"B*")+3))

次にシート2に移ります。

Ａ1セルからＥ1セルにはお示しの項目名を入力します。
Ａ2セルには次の式を入力してＥ2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$N:$N),"",IF(COLUMN(A1)=1,ROUNDUP(INDEX(Sheet1!$M:$M,MATCH(ROW(A1),Sheet1!$N:$N,0)),0),IF(COLUMN(A1)=2,LEFT(INDEX(Sheet1!$O:$O,MATCH(ROW(A1),Sheet1!$N:$N,0)),1)&"組",IF(LEFT(INDEX(Sheet1!$O:$O,MATCH(ROW(A1),Sheet1!$N:$N,0)),1)="A",INDEX(Sheet1!$B:$D,MID(INDEX(Sheet1!$O:$O,MATCH(ROW(A1),Sheet1!$N:$N,0)),2,5),COLUMN(A1)-2),IF(LEFT(INDEX(Sheet1!$O:$O,MATCH(ROW(A1),Sheet1!$N:$N,0)),1)="B",INDEX(Sheet1!$H:$J,MID(INDEX(Sheet1!$O:$O,MATCH(ROW(A1),Sheet1!$N:$N,0)),2,5),COLUMN(A1)-2),"")))))

Ａ列を選択して右クリックし「セルの書式設定」の「表示形式」で「日付」からお好みの表示に設定します。

補足

ありがとうございます。

質問時に書かなかった私が悪いのですが、いまだにOffice XP(2002)を使っています。
そのため、入れ子の上限オーバーとなってしまって計算ができませんでした。
恐らくOffice2007以降では正しく動くのでしょうね。

もし、Office 97-2003ブック形式[互換モード]でも動作する式になるようでしたら
教えてください。