![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
下図のように、2種類の表があります。
これを、1つの表にまとめ、日付順に並ばせたいのですが
マクロを使わないで数式だけで行うにはどうしたらよいでしょうか。
データを手で打ち直すことは考えていません。(実際にはもっと項目が横に長い表です。)
「コピー→貼り付け→並び替え」という作業も考えていません。
・下図では、データが「A組」では4つ、「B組」では5つとなっていますが、
実際には20個程度のデータ(数は不定)が入ります。
・また、文字データ(名前、教科)と数値データ(得点)とが混在しています。
・項目に当たる部分は両方の表とも同じものが入ります。
・日付、名前は重複する場合があります。
![「Excelで2つの表を1つにまとめるには」の質問画像](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/0/1186462_5497dd99723fe/M.jpg)
No.1ベストアンサー
- 回答日時:
こんにちは!
一例です。
↓の画像のような配置になっていて、
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
![「Excelで2つの表を1つにまとめるには」の回答画像1](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/8/667667_5497ee866d884/M.jpg)
ありがとうございます。
1つのシート内で完結させたかったため、いったんシートを分けて式を入力した後、
「切り取り&貼り付け」で一つのシートにまとめました。
式も崩れること無く反映できました。
あらかじめ順序判定用の作業列を作れば良かったのですね、気づきませんでした。
(邪魔なら非表示にすれば良いだけですね。)
どうやら上手くできたようです。
感謝します!
No.4
- 回答日時:
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))
右へ下へオートフィル
![「Excelで2つの表を1つにまとめるには」の回答画像4](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/3/655143_5497ee868b89c/M.jpg)
やはり判定行を設けるということなのですね。
個々の式が簡略で分かりやすい説明でした。
「A組の表、B組の表 ともに昇順で並んでいるとして」の条件が
こちらが提示した例では満たされているのですが、実際のデータでは100%満たされる
訳では無かったので、エラーが出てしまいました。
例が良くなかったですね、すみません。
ご回答ありがとうございました。
No.2
- 回答日時:
A組の表はシート1のA列からD列まで、B組の表はG列からJ列までにあるとして2行目に項目名で3行目から下方にそれぞれのデータが入力されているとします。
シート1には作業列を作って対応し、シート2にお求めの表を表示させるとします。
シート1のM2セルには次の式を入力して下方に(M100よりも下行まで)ドラッグコピーします。
=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)))
N2セルには次の式を入力して下方にドラッグコピーします。
=IF(M2="","",RANK(M2,M:M,1))
O2セルには次の式を入力して下方にドラッグコピーします。
=IF(N2="","",IF(MOD(M2,1)<>0,"A"&COUNTIF(O$1:O1,"A*")+3,"B"&COUNTIF(O$1:O1,"B*")+3))
次にシート2に移ります。
A1セルからE1セルにはお示しの項目名を入力します。
A2セルには次の式を入力してE2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。
=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),"")))))
A列を選択して右クリックし「セルの書式設定」の「表示形式」で「日付」からお好みの表示に設定します。
この回答への補足
ありがとうございます。
質問時に書かなかった私が悪いのですが、いまだにOffice XP(2002)を使っています。
そのため、入れ子の上限オーバーとなってしまって計算ができませんでした。
恐らくOffice2007以降では正しく動くのでしょうね。
もし、Office 97-2003ブック形式[互換モード]でも動作する式になるようでしたら
教えてください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル関数CONCATENATEで0が...
-
Excelで離れたセルに連続したデ...
-
111,222,333・・・とオートフィ...
-
エクセル SUMPRODUCT と OFFSET
-
同じ数値だったら、横の数値を...
-
スプレッドシートのIMPORTRANGE...
-
エクセルで
-
Excelで同じセルに箇条書きをし...
-
EXCELで○ヶ月を○年○ヶ月に変換...
-
エクセルの複数のセルを一括で...
-
A1セルに入力したら、入力時間...
-
Excelの入力規則で2列表示したい
-
excelでSUBTOTAL関数を設定した...
-
エクセルで既に入力してある文...
-
複数の文字列のいずれかが含ま...
-
参照先セルに値が入っていない...
-
セルを結合したA4とA5の条件付...
-
数式が入ったセルを含めて、数...
-
リンク元の日付が空白の時リン...
-
列の数字に100をかけたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセル関数CONCATENATEで0が...
-
同じ数値だったら、横の数値を...
-
EXCEL 二つずつ連続して表示し...
-
111,222,333・・・とオートフィ...
-
エクセル SUMPRODUCT と OFFSET
-
【Excel 関数】 INDIRECT関数の...
-
Excelで離れたセルに連続したデ...
-
スプレッドシートのIMPORTRANGE...
-
シート順のオートフィルの設定...
-
Excelで2つの表を1つにまとめ...
-
エクセルで画像のハイパーリン...
-
エクセル オートフィルオプション
-
可視セルから結合セルへの貼り...
-
Excelで別シートの表から指定セ...
-
エクセルで1行ごとに異なる計算...
-
エクセルで条件に合ったセルの...
-
Excel関数
-
エクセルで
-
VBA:変数を使用したオートフィ...
-
Excelのオートフィル機能...
おすすめ情報