Excel (一般機能)

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

 
(Windows 10 Home : Excel 2016)
パワークエリでシート毎に編集し、1つのデータにしたい。
投稿日時: 22/09/21 13:39:16
投稿者: y.yusuke

毎月、得意先からエクセルで注文内容が来ます。
これをパワークエリでエクセルに取り込みたいと思っています。
シートは半月毎にシートが分かれています。
シート内には
A1に2022年等、年数が入力
B1に1等、月数にあたる数字が入力されており表示形式で1月となっています。
半月毎なので、AG1に2、BI1に3、と日数毎に対応する月が入力されている感じになります。その前のセル迄セル結合がされてます。
B2には1、C2には2と1月の日数が掛かれており、3行目には対応する日付の列に注文数が掛かれています。
で、パワークエリで行列の入れ替えでColumn1を下方向へフィルし
カスタム列の追加で[Column1]{0}&[Column1]&"月"&[Column2]&"日"として、カスタム列とColumn3以外は削除
エラー行以外は削除とすれば形は整えられます。
日付と注文数の形で形は整えられますが、1シートのみです。
全部のテーブルを開いて処理すると行列の入れ替えをするため、上手く行きません。
 
今後の対応の為、シート数が変わることも想定して一括で編集して1つのデータにしたいのですが、どうしたらいいでしょうか?
 
想定している使い方としては、最新の注文書を指定フォルダに保存して、保存したエクセルのシートの中身を日付、納入数にしてすべてのシートのデータを1つのデータモデルに登録したいです。

回答
投稿日時: 22/09/21 14:49:27
投稿者: Suzu

とりあえず。
 

引用:
シートは半月毎にシートが分かれています。

引用:
B1に1等、月数にあたる数字が入力されており表示形式で1月となっています。
半月毎なので、AG1に2、BI1に3、と日数毎に対応する月が入力されている感じになります。

 
 
半月ごとにシートが分かれているのに、
1シートに、データとしては 半月分ではなく、3か月分(以上)入っている事になります。
 
と言うことは、
1シート目に 3月1日 分の データ 値 100
2シート目に 3月1日 分の データ 値 90
3シート目に 3月1日 分の データ 値 50
 
の様に、違うデータが入る可能性がありますよね。
 
 
この場合、求めたいデータとしては
3月1日分 のデータ 100 と、90 と 50 3件 を得たいのでしょうか。

投稿日時: 22/09/21 15:01:26
投稿者: y.yusuke

ごめんなさい、書き間違いです。
半月毎ではなく、半年ごとでした。
違うシートには同じ日にちは入りません。

投稿日時: 22/09/21 15:10:12
投稿者: y.yusuke

現在の所、以下の期間の6つのシートがあります。
1.2022年7月〜12月
2.2022年1月〜6月
3.2021年7月〜12月
4.2021年1月〜6月
5.2020年7月〜12月
6.2020年1月〜6月
2022年12月になれば、2023年1〜6月のシートが増えていく形になります。
 
宜しくお願いします。

回答
投稿日時: 22/09/21 17:17:00
投稿者: めいぷる

複数シートを1つにまとめるやり方が分からないということですよね。
「powerquery 複数シート」とかでググるといろいろ出てきますよ。
 
https://hamachan.info/excel2019-powerquery-ketugou/
 
https://kouritu.net/excel-powerquery-data-migration/
 
https://sakatakablog.com/excel/1063/
 
https://daitaideit.com/excel-powerquery-join-multi-sheet/
 
PowerQueryは間違えても直ぐにやり直せるので、いろいろ試してみては。
 
1つにまとめられたら、後の変換はおわかりのようなので、続けて操作すればOKです。

投稿日時: 22/09/21 18:20:29
投稿者: y.yusuke

御回答有り難う御座います
しかし、元のデータが、データベースではなく横に繋がってます
その為、編集してから結合する必要があります。
最初の質問にも書きましたが、
 
>
全部のテーブルを開いて処理すると行列の入れ替えをするため、上手く行きません。
 
説明が下手で申し訳ないですが、一括して編集するとデータが横になっている為、別シートと行がズレて上手く編集出来ません。
 
ファイル別ならヘルパークエリで出来るのですが、シート別で似たような事が出来ないかと検討してます
宜しくお願いします

回答
投稿日時: 22/09/21 18:27:33
投稿者: んなっと

シート名の情報が落ちていますよ。これでは回答不能です。
 
以下は、シート名が 2021上 2022上 2022下 .... となっている場合の回答。
すべてのシートで行列を入れ替えてから統合するので、少し面倒です。
 
●2021上
 
   A  B  C  D E  F G  H  I  J 
1 2021  1                
2     1  2  3 4  5 6  7  8  9 
3    14 24 25 5 28 4 19 12 18 
 
●2022上
 
   A  B  C D E  F G  H  I  J
1 2022  1               
2     1  2 3 4  5 6  7  8  9
3    17 16 5 9 18 1 10 20 12
 
●2022下
 
   A  B C  D  E  F  G H  I  J
1 2022  7               
2     1 2  3  4  5  6 7  8  9
3    18 9 27 17 10 28 8 28 20
 
【1】新規シートを追加して、どこかのセルに
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",)
→上のセルを選択した状態で、数式バーの左の名前ボックスに次の名前を入れる
Fname
  
【2】
 データ
→データの取得
→その他のデータソースから
→空のクエリ
→詳細エディター
→「let ... in ..」の部分を全選択して削除
→以下の文字列を貼り付け
 
let
    MltExp = (Data as table) =>
        let
            Trp = Table.Transpose(Data),
            Fil = Table.FillDown(Trp,{"Column1"}),
            Cmb = Table.AddColumn(Fil, "Date", each #date(Fil{0}[Column1], [Column1], [Column2]),type date),
            Del2 = Table.RemoveRowsWithErrors(Cmb, {"Date"}),
            Del3 = Table.SelectColumns(Del2,{"Date","Column3"})
        in
            Del3,
    Fnm = Excel.CurrentWorkbook(){[Name="FName"]}[Content]{0}[Column1],
    Src = Excel.Workbook(File.Contents(Fnm), null, true),
    Flt = Table.SelectRows(Src, each Text.Contains([Name], "上") or Text.Contains([Name], "下")),
    Opt = Table.AddColumn(Flt,"Custom", each MltExp([Data])),
    Del = Table.SelectColumns(Opt,{"Custom"}),
    Exp = Table.ExpandTableColumn(Del, "Custom", {"Date", "Column3"}),
    Trs = Table.TransformColumnTypes(Exp,{{"Date", type date}})
in
    Trs
 
→閉じて読み込む

投稿日時: 22/09/21 19:01:26
投稿者: y.yusuke

有り難う御座います。
 
明日試してみます。
シート名も必要だったんですね。申し訳ないです。

回答
投稿日時: 22/09/21 19:07:10
投稿者: んなっと

ま、うまくいかないでしょうね。

投稿日時: 22/09/22 09:31:38
投稿者: y.yusuke

有難うございました。
少し弄れば上手く行きました。