Excel PowerQuery インデックス(行番号) 項目ごとに振り直される連番を付ける

Microsoft Excel

スポンサーリンク

はじめに

以前の記事で、ExcelのPowerQueryでインデックス(行番号)を振る方法を扱った。

Excel PowerQueryで、選択列の値が変わるごとに縞模様を付ける

また別の記事で、項目 (下図の例でいう「第1カテゴリ」項目) が変わるごとに1増えるインデックス番号を振る方法を扱った。

Excel PowerQuery 行番号(インデックス)を付ける

今回は、これと別のインデックスの振り方をしてみる。

下図の例では「伝票番号」に対して、インデックスである「行番号」を振っている。
そして伝票番号が変わるごとに「行番号」は0開始に戻り、伝票番号が変わらない間は「行番号」は1ずつ増える。

こういうインデックスの振り方をするには、ちょっと難しい方法が必要になる。

インデックス設定(伝票番号が同じ間は同じ番号)

まず、おさらいの意味もあるが、今回やりたい処理の手掛かりの意味も込め、以前にやったように「伝票番号」が変わるごとに1増えるインデックスを振る。

完成イメージは下図の通りだ。

グループ化

まず「伝票番号」列を選んだ状態で

リボン「ホーム」タブ
グループ化

とする。

設定ダイアログが出るが、「新しい列名」にはひとまず「G」とでも入れる。
また「操作」欄では「すべての行」を選ぶ。

そして「OK」を押す。

インデックスの追加

伝票番号ごとにグループ化されたので

リボン「列の追加」
→「インデックス列

とする。

インデックスが追加されるが、数式バーまたは詳細エディターを確認すると、
Table.AddIndexColumn
という関数が確認できる。

これをコピペでもして、ちょっと覚えておこう。

Excel PowerQuery ソースコードの詳細エディターについて

そして、先ほどのグループ化により追加された列「G」の、右端の四角ボタンを押して、データを展開する。

これで、「伝票番号」が変わるごとに1増えるインデックスが振られる。

インデックス設定(伝票番号が変わるごとに振り直し)

ステップの再加工

では今回の本題、伝票番号が変わるごとに0開始に戻るインデックスを設定していく。

ここまでの処理で、クエリの「ステップ」が下図のように刻まれているはずだが、下の2ステップ
「追加されたインデックス」
「展開されたG」
を、左端の×印をクリックして削除しよう。

もちろん、最初からやり方を理解してるなら、そもそもこの最後の2ステップを作っておく必要もない。

PowerQuery エディターの基礎

カスタム列の追加→関数作成

伝票番号をキーにして「グループ化」されたばかりの状態に戻るが、ここからまたインデックス列を追加していく。

先ほどの例では「伝票番号」列をキーにしたインデックスを振ったが、今度は「伝票番号」列以外をキーにしてインデックスを振る感じ。

ただそのインデックスは、さっきのように単に「インデックス列」を押すとかでは作れず、ちょっと自分で関数を書いていかないと作れない。はず。

そのために

リボン「列の追加」タブ
→「カスタム列

とする。

Excel PowerQuery 計算式や条件を入れたオリジナルの項目列を追加する

Table.AddIndexColumn関数を入れる

先ほどちょっと言及した
Table.AddIndexColumn
を入れていく。

カスタム列の式を入れる場所に「tab」とか最初の何文字かを入れれば、候補に「Table.AddIndexColumn」が出てくるはずなので、それを選択しよう。

普通に「Table.AddIndexColumn」文字列をコピペとかで入れても良いし。

では、次のような条件のインデックス列を追加する。

  • G」項目をグループ化キーとする。
  • 0番開始
  • 1ずつ増加
  • 行番号」という名前

ということで、関数の引数を次のように入れていく。項目「G」は、右端の「使用できる列」からダブルクリックで挿入すれば良い。

= Table.AddIndexColumn ( [G],  “行番号” , 0 , 1)

データの展開

下図のように「カスタム」列が追加され、「G」「カスタム」の2列が、データが折りたたまれた状態になる。

これらのうち「G」列は、もう不要なので削除して良い。

数式バーにTable.AddIndexColumn関数が確認できるけど、なんか他にもeachとか書かれてて、詳細エディターで自力で書くのはちょっと難しいと思う。
だからカスタム列機能を使って書いた。

「カスタム」列の右端の四角ボタンを押して、データを展開する。

既にある項目「伝票番号」のチェックは外す。
また「元の列名をプレフィックスとして使用します」のチェックは外し「OK」を押そう。

これで目的通り、「伝票番号」が変わるごとに0開始に戻る項目「行番号」が追加される。

いったん「伝票番号」以外をグループ化し、その内部でインデックス列を作り、またデータを展開した・・・という感じだ。

不要行の削除にも使える

以前の記事で、フォルダ内のExcelファイルを結合する例を扱ったが、この記事のとき使った各Excelファイルは、いずれも先頭4行が不要なものだった。

その先頭4行を燻り出すのにも、今回やったようなインデックス設定が使える。

下図では、ファイル名に当たる「Source.Name」列の値が変わるごとに「行番号」が0開始になるよう設定している。

その「行番号」が0~3の行をフィルタリングで削除するようにすれば、各ファイルの不要な先頭4行を削除できることになる。

スポンサーリンク