下図のように、2種類の表があります。
これを、1つの表にまとめ、日付順に並ばせたいのですが
マクロを使わないで数式だけで行うにはどうしたらよいでしょうか。
データを手で打ち直すことは考えていません。(実際にはもっと項目が横に長い表です。)
「コピー→貼り付け→並び替え」という作業も考えていません。
・下図では、データが「A組」では4つ、「B組」では5つとなっていますが、
実際には20個程度のデータ(数は不定)が入ります。
・また、文字データ(名前、教科)と数値データ(得点)とが混在しています。
・項目に当たる部分は両方の表とも同じものが入ります。
・日付、名前は重複する場合があります。
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
ありがとうございます。
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))
右へ下へオートフィル
やはり判定行を設けるということなのですね。
個々の式が簡略で分かりやすい説明でした。
「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で質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) 【困っています2】VBA 追加処理の記述を教えてください。 2 2022/08/26 11:42
- Visual Basic(VBA) 【VBA】データを入力後に,同一シート内に履歴として転記するVBAコードを教えていただきたいです。 3 2022/11/16 01:37
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- システム CSVファイルのマッピング処理の省力化 1 2022/11/24 00:01
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける 3 2022/09/10 07:55
- Visual Basic(VBA) マクロを教えてください。 7 2023/06/01 19:47
- Visual Basic(VBA) 【ご教示ください】VBAの記述方法がわかりません。 2 2022/08/12 21:28
- Visual Basic(VBA) 【前回の続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/16 16:44
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル関数CONCATENATEで0が...
-
Excelで離れたセルに連続したデ...
-
エクセル SUMPRODUCT と OFFSET
-
EXCEL 二つずつ連続して表示し...
-
111,222,333・・・とオートフィ...
-
【Excel 関数】 INDIRECT関数の...
-
EXCELで○ヶ月を○年○ヶ月に変換...
-
Excelで同じセルに箇条書きをし...
-
複数の文字列のいずれかが含ま...
-
A1セルに入力したら、入力時間...
-
参照先セルに値が入っていない...
-
エクセルの複数のセルを一括で...
-
エクセル:一覧表に存在する文...
-
Excelの入力規則で2列表示したい
-
excelでSUBTOTAL関数を設定した...
-
リンク元の日付が空白の時リン...
-
IF関数で0より大きい数値が入力...
-
エクセルで既に入力してある文...
-
エクセルで1列に500行並んだデ...
-
値を入力後、自動的にアクティ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセル関数CONCATENATEで0が...
-
同じ数値だったら、横の数値を...
-
111,222,333・・・とオートフィ...
-
EXCEL 二つずつ連続して表示し...
-
Excelで2つの表を1つにまとめ...
-
Excelで離れたセルに連続したデ...
-
スプレッドシートのIMPORTRANGE...
-
シート順のオートフィルの設定...
-
【Excel 関数】 INDIRECT関数の...
-
エクセルで画像のハイパーリン...
-
エクセル SUMPRODUCT と OFFSET
-
エクセルで1行ごとに異なる計算...
-
連続するシートを一つにまとめ...
-
VBA:変数を使用したオートフィ...
-
EXCELで○ヶ月を○年○ヶ月に変換...
-
A1セルに入力したら、入力時間...
-
エクセルの複数のセルを一括で...
-
Excelで同じセルに箇条書きをし...
-
複数の文字列のいずれかが含ま...
-
Excelの入力規則で2列表示したい
おすすめ情報