[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数シートにおいて１つのシートにデータを集約する方法』（あんぱぱ）
sheet1にsheet2のデータを抽出します。
A列の「テストID」をキーにします。
sheet1のC列「テストＡ」には、sheet2のB列の名称を。
sheet1のD列「テストＡ実施日」には、sheet2のC列の日付を。
sheet1のE列「テストＡ担当者」には、sheet2のD列のテスト担当者を。
sheet2のテストID「0001」はテストＡが２レコードある。この場合、sheet1にはテストＡとテスト実施日の一番最新の日付を抽出するようにしたい。
それぞれテストIDに一致したものを抽出する。
sheet1のC列〜H列に関数を設定する。
（sheet1）
A B C D E F G H
1 テストID 名称 テストＡ テストＡ実施日 テストＡ担当者 テストＢ テストＢ実施日 テストＢ担当者
2 0001 aaa
3 0002 bbb
（sheet2）
A B C D 1 テストID テスト テスト実施日 テスト担当者 2 0001 テストＡ 2020/08/01 担当A 3 0001 テストＡ 2020/08/02 担当A 4 0001 テストＢ 2020/08/04 担当B 5 0002 テストＡ 2020/08/03 担当A
＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞
1行目のC列からH列も数式で求めたいのだろうか？ またテストC、テストD…も出てくる可能性があるのだろうか？ （ねむねむ） 2020/08/07(金) 10:46
ちょっと確認
＞sheet1のC列「テストＡ」には、sheet2のB列の名称を。
・Sheet2 のB列の名称って、C列は全部「テストＡ」、F列は全部「テストＢ」では？ ・Sheet2 の実施日は、テストIDとテストごとに昇順？
昇順だとして、一応･･･
C2 =IF($A2="","",C1) それとも ↓ これ？ C2 =IF(D2="","",C$1)
D2 =IF($A2="","",IFERROR(LOOKUP(1,0/((Sheet2!$A$2:$A$100=$A2)*(Sheet2!$B$2:$B$100=C$1)),Sheet2!$C$2:$C$100),"")) E2 =IF($A2="","",IFERROR(LOOKUP(1,0/((Sheet2!$A$2:$A$100=$A2)*(Sheet2!$B$2:$B$100=C$1)),Sheet2!$D$2:$D$100),""))
C2:E2、3つのセルを選択して右にコピー D2とG2の表示形式を「日付」にし、C2:H2 を下コピー
ピボットテーブルでも似たようなことはできるかも･･･
以上 （笑） 2020/08/07(金) 12:13
>おっしゃる通り2行目のC列からH列も数式で求めたいです。 これ、1行目の間違いだとして。 (違ったら無視してくれ） まずは1行目だけ。
（ねむねむ） 2020/08/07(金) 13:21
C1セルに
=IFERROR(INDEX(Sheet2!$B$1:$B$100,SMALL(IF(COUNTIF(INDIRECT("Sheet2!B2:B"&ROW($2:$100)),Sheet2!$B$2:$B$100)=1,ROW($2:$100),""),INT((COLUMN(A1)-1)/3)+1))&INDEX({"","実施日","担当者"},MOD(COLUMN(A1)-1,3)+1),"")
と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定（確定後、式が{}で囲まれればOK）その後、右へフィルコピー。
（ねむねむ） 2020/08/07(金) 13:22
上記式ではSheet2のデータは最大100行目まで対応している。 もっとある場合は式中の$100を大きくしてくれ。 （ねむねむ） 2020/08/07(金) 13:25
2行目以下。 C2セルに =IFERROR(IF($A2="","",INDEX(INDEX(Sheet2!$B$2:$D$100,,MOD(COLUMN(A1)-1,3)+1),MATCH(1,INDEX((Sheet2!$A$2:$A$100=$A2)*(Sheet2!$B$2:$B$100=INDEX(OFFSET(C$1,,-MOD(COLUMN(A1)-1,3)),1))*(Sheet2!$C$2:$C$100=MAX((Sheet2!$A$2:$A$100=$A2)*(Sheet2!$B$2:$B$100=INDEX(OFFSET(C$1,,-MOD(COLUMN(A1)-1,3)),1))*Sheet2!$C$2:$C$100)),0),0))),"")
（ねむねむ） 2020/08/07(金) 13:49
Shift+Ctrl+Enter、その後、右及び下へフィルコピーで。 （ねむねむ） 2020/08/07(金) 13:49
一部sheet1の項目「テストＡ」「テストＢ」の列を削除した場合はどのように変更すれば良いでしょうか？
（sheet1）
A B C D E F 1 テストID 名称 テストＡ実施日 テストＡ担当者 テストＢ実施日 テストＢ担当者 2 0001 aaa 3 0002 bbb
（sheet2）
A B C D 1 テストID テスト テスト実施日 テスト担当者 2 0001 テストＡ 2020/08/01 担当A 3 0001 テストＡ 2020/08/02 担当A 4 0001 テストＢ 2020/08/04 担当B 5 0002 テストＡ 2020/08/03 担当A
（あんぱぱ） 2020/08/07(金) 13:51
＞sheet1の項目「テストＡ」「テストＢ」の列を削除した場合
■ Sheet1の見出しを変更 1）Sheet1 のC1セルに テストＡ とだけ入力 表示形式〜ユーザー定義 @"実施日" これで表示は「テストＡ実施日」になる 2）C1をD1にコピー D1の表示形式〜ユーザー定義 @"担当者" 3）C1とD1、2つのセルを選択し、右にコピー 4）E1、F1には テストＢ とだけ入力 表示は「テストＢ実施日」「テストＢ担当者」になる
■ 数式
C2 =IF($A2="","",IFERROR(LOOKUP(1,0/((Sheet2!$A$2:$A$100=$A2)*(Sheet2!$B$2:$B$100=C$1)),Sheet2!$C$2:$C$100),"")) D2 =IF($A2="","",IFERROR(LOOKUP(1,0/((Sheet2!$A$2:$A$100=$A2)*(Sheet2!$B$2:$B$100=D$1)),Sheet2!$D$2:$D$100),""))
C2の表示形式を「日付」にし、C2とD2を選択して右と下にコピー
ところで、Sheet2の日付は同じIDと同じテストの中ではすべて「昇順」になっている ということでいいんですよね？
以上 （笑） 2020/08/07(金) 15:00
＞ところで、Sheet2の日付は同じIDと同じテストの中ではすべて「昇順」になっている
＞ということでいいんですよね？
その通りです。同じIDと同じテスト内の組み合わせでは「昇順」です。
（あんぱぱ） 2020/08/07(金) 15:46
>その通りです。同じIDと同じテスト内の組み合わせでは「昇順」です。 これであればSheet1のC2セルの式は =IFERROR(IF($A2="","",INDEX(INDEX(Sheet2!$B$2:$D$100,,MOD(COLUMN(A1)-1,3)+1),MATCH(1,INDEX(0/((Sheet2!$A$2:$A$100=$A2)*(Sheet2!$B$2:$B$100=INDEX(OFFSET(C$1,,-MOD(COLUMN(A1)-1,3)),1))),0),1))),"") とできる。 Shift+Ctrl+Enterは忘れずに。 （ねむねむ） 2020/08/07(金) 16:30
追加になりますが・・・
元々のデータは1000以上あります。
その中で、新たな点がありまして、sheet2の「テスト実施日」が未入力のものがありました。
空白または「−」が入っている場合、日付の入っているデータを抽出したいのですが、
条件が複雑になってしまいます。対処は可能でしょうか？
（sheet2）
A B C D 1 テストID テスト テスト実施日 テスト担当者 2 0001 テストＡ 2020/08/01 担当A 3 0001 テストＡ - 担当A
（あんぱぱ） 2020/08/07(金) 17:05
＞空白または「−」が入っている場合、日付の入っているデータを抽出したい
空白または「−」だけで、日付が入っているレコードがなかったら何も表示しないということ？
それでいいのなら
C2 =IF($A2="","",IFERROR(LOOKUP(1,0/((Sheet2!$A$2:$A$1500=$A2)*(Sheet2!$B$2:$B$1500=C$1)*ISNUMBER(Sheet2!$C$2:$C$1500)),Sheet2!$C$2:$C$1500),"")) D2 =IF($A2="","",IFERROR(LOOKUP(1,0/((Sheet2!$A$2:$A$1500=$A2)*(Sheet2!$B$2:$B$1500=D$1)*ISNUMBER(Sheet2!$C$2:$C$1500)),Sheet2!$D$2:$D$1500),""))
C2とD2を選択して右にコピー、そのまま下にコピー
以上 （笑） 2020/08/07(金) 18:17
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.