Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
複数シートから重複しないリストを作成
投稿日時: 19/11/16 01:17:04
投稿者: バンブー

複数シート(Sheet1とSheet2)から重複しないリストをSheet3に作成する関数について、
ご教授をお願いいたします。
 
Sheet1
   A   B
1 コード 名前
2  1   あ
3  2   い
4  3   う
 
Sheet2
   A   B
1 コード 名前
2  1   あ
3  4   え
4  5   お
 
Sheet3
   A   B
1 コード 名前
2  1   あ
3  2   い
4  3   う
5  4   え
6  5   お

回答
投稿日時: 19/11/16 08:31:49
投稿者: Mike

1.Sheet1、Sheet2 のデータを[コピー]して、Sheet3 に[貼り付け]た結果が下図。
    A  B
  1 コード 名前
  2  1 あ
  3  2 い
  4  3 う
  5 コード 名前
  6  1 あ
  7  4 え
  8  5 お
  
 
2.列範囲 A:B を選択⇒[データツール <重複の削除>]⇒“先頭行を…使用する”に
  付いているチェック外し⇒[OK]⇒[OK]
    A  B
  1 コード 名前
  2  1 あ
  3  2 い
  4  3 う
  5  4 え
  6  5 お

回答
投稿日時: 19/11/16 09:13:20
投稿者: んなっと

何度も元データの変更があってそのたびに同じ操作を繰り返すときは、
「データの取得と変換」を使う方法もあります。
ただし最初1回目だけ面倒。

回答
投稿日時: 19/11/16 09:41:45
投稿者: んなっと

●Sheet1で 
 [データ]
→[テーブルまたは範囲から]
→「先頭行をテーブルの見出しとして使用する」にチェックを入れてOK
→[ファイル]
→[閉じて次に読み込む]
→[接続の作成のみ]
→OK
 
●Sheet2も同様に
 
●Sheet3で 
 [データ] 
→[データの取得]
→[クエリの結合]
→[追加]
→主テーブル テーブル1
 主テーブルに追加するテーブル テーブル2
→OK
→PowerQueryエディターでShiftキーを併用して2つの列をどちらも選択
→[グループ化]
→OK
→新しくできた「カウント」列を選択して[列の削除]
→最初の「コード」列で[昇順に並べ替え](なくてもいいかも)
→[閉じて読み込む]
 
●元データが変更、増減があったときはSheet3で[すべて更新]

回答
投稿日時: 19/11/16 18:47:21
投稿者: んなっと

シート数が多いときは、こんな方法も。
   
    A B
1 Sheet1  
2 Sheet2  
3 Sheet3
........
   
 新規シートを追加して、A列に結合したいシート名を並べる
→B1に次の式
  
="let Src = Excel.Workbook(File.Contents("""&SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",)&"""), null, true),"&TEXTJOIN("",TRUE,INDEX(REPT(" _"&A1:A30&"= Table.PromoteHeaders(Src{[Item="""&A1:A30&""",Kind=""Sheet""]}[Data], [PromoteAllScalars=true]),",(LEN(A1:A30)>0)*1),))&" Com = Table.Combine({"&TEXTJOIN(",",TRUE,INDEX(REPT("_"&A1:A30,(LEN(A1:A30)>0)*1),))&"}),"&" Grp = Table.Group(Com, {"&TEXTJOIN(",",TRUE,INDEX(REPT(""""&INDIRECT(A1&"!A1:Z1")&"""",(LEN(INDIRECT(A1&"!A1:Z1"))>0)*1),))&"}, {{""Cnt"", each null}}),"&" Del = Table.RemoveColumns(Grp,{""Cnt""}),"&" Asd = Table.Sort(Del,{{"""&INDIRECT(A1&"!A1")&""", Order.Ascending}})"&" in Asd"
  
→再度B1セルを選択してCtrl+Cでコピー
→データ
→データの取得
→その他のデータソースから
→空のクエリ
→詳細エディター
→「let ... in ..」の部分をクリックしてCtrl+Aで全選択
→Ctrl+Vで貼り付け
→完了
→閉じて読み込む

投稿日時: 19/11/16 22:53:32
投稿者: バンブー

Mikeさま
 
やはりデータは同じシートに並べた方が扱いやすそうですね。
データツールの<重複の削除>で簡単に重複しないリストが作成できました。
早々のご回答をありがとうございました。

投稿日時: 19/11/16 23:15:45
投稿者: バンブー

んなっとさま
 
Power Queryは初めてで、何度も再現していたため返信が遅くなりました。
重複しないリストは、Sheet3ではなくSheet4にできました。
また、元データに追加したらSheet5に新たにリストができました。
 
データが大量な場合は、テーブルに設定すると扱いやすくなりますね。
Power Queryはこれから勉強しようと思います。
ご回答いただきましてありがとうございました。
 

んなっと さんの引用:
●Sheet1で&nbsp;
 [データ]
→[テーブルまたは範囲から]
→「先頭行をテーブルの見出しとして使用する」にチェックを入れてOK
→[ファイル]
→[閉じて次に読み込む]
→[接続の作成のみ]
→OK
 
●Sheet2も同様に
 
●Sheet3で&nbsp;
 [データ]&nbsp;
→[データの取得]
→[クエリの結合]
→[追加]
→主テーブル テーブル1
 主テーブルに追加するテーブル テーブル2
→OK
→PowerQueryエディターでShiftキーを併用して2つの列をどちらも選択
→[グループ化]
→OK
→新しくできた「カウント」列を選択して[列の削除]
→最初の「コード」列で[昇順に並べ替え](なくてもいいかも)
→[閉じて読み込む]
 
●元データが変更、増減があったときはSheet3で[すべて更新]

投稿日時: 19/11/16 23:29:24
投稿者: バンブー

んなっとさま
 
別のご回答もありがとうございます。
 
下記の式をB1にコピーしたところ、#NAME?と表示されました。
どうすればよろしいでしょうか?
 

んなっと さんの引用:
シート数が多いときは、こんな方法も。
   
    A B
1 Sheet1  
2 Sheet2  
3 Sheet3
........
   
 新規シートを追加して、A列に結合したいシート名を並べる
→B1に次の式
  
="let Src = Excel.Workbook(File.Contents("""&SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",)&"""), null, true),"&TEXTJOIN("",TRUE,INDEX(REPT(" _"&A1:A30&"= Table.PromoteHeaders(Src{[Item="""&A1:A30&""",Kind=""Sheet""]}[Data], [PromoteAllScalars=true]),",(LEN(A1:A30)>0)*1),))&" Com = Table.Combine({"&TEXTJOIN(",",TRUE,INDEX(REPT("_"&A1:A30,(LEN(A1:A30)>0)*1),))&"}),"&" Grp = Table.Group(Com, {"&TEXTJOIN(",",TRUE,INDEX(REPT(""""&INDIRECT(A1&"!A1:Z1")&"""",(LEN(INDIRECT(A1&"!A1:Z1"))>0)*1),))&"}, {{""Cnt"", each null}}),"&" Del = Table.RemoveColumns(Grp,{""Cnt""}),"&" Asd = Table.Sort(Del,{{"""&INDIRECT(A1&"!A1")&""", Order.Ascending}})"&" in Asd"
  
→再度B1セルを選択してCtrl+Cでコピー
→データ
→データの取得
→その他のデータソースから
→空のクエリ
→詳細エディター
→「let ... in ..」の部分をクリックしてCtrl+Aで全選択
→Ctrl+Vで貼り付け
→完了
→閉じて読み込む

回答
投稿日時: 19/11/17 00:02:02
投稿者: んなっと

そうですか。では、使わないでください。

投稿日時: 19/11/17 14:17:37
投稿者: バンブー

んなっとさま
 
返信をありがとうございました。
お手を煩わせて申し訳ございませんでした。
 

んなっと さんの引用:
そうですか。では、使わないでください。