「Mから始めよう #11 Excel 複数のブックの複数のシートのデータを取り出す」の続きです。
全部のシートを結合する場合は、前に説明したとおりですが、シートを選択して結合したい場合の方が多いと思います。その場合のやり方です。
シート名を指定する
シート名を直接指定する
シート名を指定する方法は、#11の途中でフィルターをかけるステップを追加してやればできます。
Combineを使ったファイル名、シート名なしの場合は、一旦シート名を表示させ、フィルターをかけてからCombineします。
let
Source = Folder.Files(folder),
// Excelデータソースへのアクセス
Workbook =
Table.TransformColumns(
Source,
{
"Content",
each
Excel.Workbook(_, true)
}
),
// シートをばらす（シート名付き）
Expand =
Table.ExpandTableColumn(
Workbook,
"Content",
{"Name", "Data"},
{"Sheet", "Data"}
),
// Sheet1とSheet2を選択
FilteredRows =
Table.SelectRows(
Expand,
each ([Sheet] = "Sheet1" or [Sheet] = "Sheet2")
),
// 各シートのデータを結合
Combine = Table.Combine(FilteredRows[Data])
in
Combine
シート名をパラメータで指定する
選択するシートをパラメータで指定します。
ここでは、複数のシートを選択できるように、カンマで区切って入力しています。１つだけの場合は、そのシート名だけでいいです。
let
Source = Folder.Files(folder),
// Excelデータソースへのアクセス
Workbook =
Table.TransformColumns(
Source,
{
"Content",
each
Excel.Workbook(_, true)
}
),
// シートをばらす
Expand =
Table.ExpandTableColumn(
Workbook,
"Content",
{"Name", "Data"},
{"Sheet", "Data"}
),
// シート選択
FilteredRows =
Table.SelectRows(
Expand,
each List.Contains(Text.Split(SheetNames, ","), [Sheet])
),
// 各シートのデータを結合
Combine = Table.Combine(FilteredRows[Data])
in
Combine
パラメータの文字列をText.Splitでリストに変換し、List.Containsでシート名と比較する処理をしています。
シート番号で選択する
シート番号を直接指定する１
1番最初のシートのデータだけを結合するとか、２番目のシートだけ結合するとかしたい場合は、シート番号を直接指定してやります。
let
Source = Folder.Files(folder),
// ファイル名とデータ項目を取得
RemovedOtherColumns =
Table.SelectColumns(
Source,
{"Content", "Name"}
),
// NameをFileNameに変更
Rename = Table.RenameColumns(
RemovedOtherColumns,
{"Name", "FileName"}
),
// FileNameを１番目に移動
Reorder = Table.ReorderColumns(
Rename,
{
"FileName",
"Content"
}
),
// Excelデータソースへのアクセス
Workbook =
Table.TransformColumns(
Reorder,
{
"Content",
each Excel.Workbook(_, true)
}
),
// Contentからシート名とシートデータを取得
RenameColumn =
Table.RenameColumns(
Workbook,
{{"Content", "Data"}}
),
ExpandSheets =
Table.TransformColumns(
RenameColumn,
{
"Data",
each _{0}[Data] // １番目のシートを選択
}
),
// シートデータを結合
Expand =
Table.ExpandTableColumn(
ExpandSheets,
"Data",
List.Union( // 個別のリスト項目の和集合を作成
List.Transform( // 変換関数をリストに適用して新しいリストを返す
ExpandSheets[Data],
each Table.ColumnNames(_)
)
)
)
in
Expand
each _{0}[Data] の部分で、0から始まるのがシート番号です。２番目のシートを選択したい場合は、each _{1}[Data]となります。
ただし、２番目のシートが存在しない場合は全体がエラーになってしまいます。
シート番号を直接指定する
シート番号を表示する項目を追加してやります。
この項目に対してフィルターをかけてやれば、上記のエラーは発生しません。
let
Source = Folder.Files(folder),
// Excelデータソースへのアクセス
Step1 = Table.TransformColumns(
Source,
{
"Content",
each Excel.Workbook(_, true)
}
),
// Contentにファイル名を埋め込む
Step2 = Table.AddColumn(
Step1,
"Workbooks",
(x)=>
Table.AddColumn(
x[Content],
"File",
each x[Name]
)
),
AddSheetNo = Table.TransformColumns(
Step2,
{
{
"Workbooks",
each Table.AddIndexColumn(_, "SheetNo", 1)
}
}
),
// ファイル名が埋め込まれたテーブルを結合
Step3 = Table.Combine(
AddSheetNo[Workbooks]
),
// Dataにファイル名、シート名を埋め込む
Step4 = Table.AddColumn(
Step3,
"Worksheets",
(x)=>
Table.AddColumn(
x[Data],
"File Sheet",
each [File = x[File], Sheet = x[Name], SheetNo = x[SheetNo]]
)
),
// ファイル名、シート名が埋め込まれたテーブルを結合
Step5 = Table.Combine(Step4[Worksheets]),
// ファイル名、シート名を展開
Step6 = Table.ExpandRecordColumn(
Step5,
"File Sheet",
{"File", "Sheet", "SheetNo"}
),
FilteredRows = Table.SelectRows(Step6, each ([SheetNo] = 1))
in
FilteredRows
Step2の後、AddSheetNoのステップで、Workbooksの中のテーブルに対してTable.AddIndexColumnで１番から始まるシート番号を付与しています。そのあと、最後のFilteredRowsのステップで、シート番号１を指定してフィルターをかけています。
シート番号をパラメータで指定する
パラメータ SheetNosを以下のように設定し、１～３番めのシートのデータを結合します。
FilteredRows =
Table.SelectRows(
Step6,
each List.Contains(Text.Split(SheetNos, ","), Number.ToText([SheetNo]))
)
先のコードの FilterdRows のステップを書き換え、パラメータを分割して SheetNo の値と比較しています。