グループごとに連番を振る方法 | COUNTIF関数とPowerQuery

データの整理や分析において、グループごとに一意の連番を振ることがあります。エクセルを使用する際には、「COUNTIF」関数や「PowerQuery」を使うことが便利です。

この記事では、社員の部署異動履歴を例に、社員ごとに連番を振る方法を紹介します。

目次

連番を振るエクセル表の例

エクセルでグループごとに連番を振る

E列に社員ごとの連番を振ります。ここでは見やすくするために、社員ごとに青い罫線を入れています。

エクセル関数「COUNTIF」を使った連番の生成

Excelの「COUNTIF」関数を使って、特定の条件に一致するセルの数をカウントします。

=COUNTIF(範囲, 検索条件)

セルE2に次の関数を入力し、セルE7までコピーします。

=COUNTIF($A$2:A2, A2)

countif関数の説明
説明委

この例は、各社員番号ごとにその番号が現れる回数を数えるものです。セルA2の社員番号が1000であれば、COUNTIF関数は範囲$A$2:A2内で1000が何回出現するかを数え、その結果をセルB2に表示します。これにより、各社員番号ごとに連番が振られます。

  • $A$2:A2:これはセルの範囲を指定しています。$A$2は固定されたセルを指し、A2は相対参照です。例えば、この式がセルB5にある場合、対象範囲はセルA2から現在の行まで(A5まで)となります。
  • A2:これは条件を指定する部分です。具体的には、セルA2の値が対象範囲内で何回出現するかを数えます。

PowerQueryを使った連番の生成手順

PowerQueryで社員ごとの連番と追加で社員ごとのデータ数を生成する方法です。

手順
  1. データをPowerQueryに取り込みます。
  2. グループ化する列を選択します。
  3. 詳細設定で新しい列名を指定し、「カウント:個別の行数カウント」を選択します。
  4. [列の追加]タブ > [カスタム列] で式を入力します。

(1)データ範囲を選択した状態で、[データ]>[テーブル範囲または範囲から]を選択します。

PowerQueryで連番とデータ数

(2)「テーブルの作成」が表示されるので、「OK」をクリックします。

PowerQueryで連番とデータ数

→PowerQueryエディターが起動します。

(3)社員番号を選んだ状態で、[ホーム]>[グループ化]を選択します。

PowerQueryで連番とデータ数

→グループ化が表示されます。

(4)「詳細設定」を選択し、次のように設定後、「OK」をクリックします。新しい列名を追加するには、「集計の追加」をクリックします。

  • カウント:操作→個別の行数のカウント
  • Data(名前は何でもよい):操作→すべての行
PowerQueryで連番とデータ数・グループ化

→カウントとDataが追加されます。

PowerQueryで連番とデータ数

(5)[列の追加]>[カスタム列]を選択します。

PowerQueryで連番とデータ数

(6)カスタム列の式に以下を入力し、「OK」をクリックします。

=Table.AddIndexColumn([Data],"IDX")

PowerQueryで連番とデータ数・カスタム列

※「IDX」は自分のわかりやすい名前でOK

→Dataとカスタムが追加されます。

(7)カスタムの名前の右側をクリックして、「元の列名をプレフィックスとして使用します」のチェックを外し「OK」をクリックします。

→右側に列が追加されます。

(8)不要列を削除し、「閉じて読み込む」をクリックします。

→エクセルシートにデータが表示されます。

PowerQuery

もとの表示にしたい場合は、データ内をクリックした状態で、[テーブルデザイン]>[テーブルスタイル]より「なし」を選択します。

スタイル無し

PowerQueryとの接続を切りたい場合は、[クエリ]>[削除]をクリックします。

PowerQuery削除

さらにテーブル設定を解除したい場合は、[テーブルデザイン]>[範囲に変換]をクリックします。

目次