エクセルでソートやマクロを使わずに、連番IDかつ同じ商品には同じIDを振りたい場合の関数

3059 ワード
GoogleSpreadSheet 検索 Excel 関数 エクセル Excel テキストリンク

「マクロを使えばできます。」みたいなものは求めていなくて
関数だけで、できる限りのことはすませたい。
コピペもできるし、Googleスプレッドシートにも使えるし。
という人向けです。

やりたいこと

同じ商品には同じ番号(ID)を割り振りたい。
番号は連番にしたい。
あとから商品を追加しても大丈夫なようにしたい。

考え方

一番最初の商品は 1
それ以降、はじめて出現する商品は +1していく
同じ商品の場合は同じ番号を使う

関数

B2 に利用している関数は下記になります。

=IF(ISNUMBER(B1),IF(MATCH($A2,$A:$A,FALSE)=ROW(),MAX(OFFSET(B:B,1,0,ROW()-2,1))+1,INDEX(B:B,MATCH($A2,$A:$A,FALSE))),1)

B3 以降はスマートフィルとかCtrl+Dとか、コピペしてください。
おしまい。

以下、解説になります。

関数の解説

最初のIF

001:    =IF(
002:      ISNUMBER(B1),
003:      IF(MATCH($A2,$A:$A,FALSE)=ROW(),MAX(OFFSET(B:B,1,0,ROW()-2,1))+1,INDEX(B:B,MATCH($A2,$A:$A,FALSE))),
016:      1
017:    )

002 条件式：ひとつ上が数字かどうか
003 真：数字の場合は色々やる（後述）
004 偽：最初のIDは、1にする

次のIF

003:    IF(
004:      MATCH($A2,$A:$A,FALSE)=ROW(),
005:      MAX(OFFSET(B:B,1,0,ROW()-2,1))+1,
014:      INDEX(B:B,MATCH($A2,$A:$A,FALSE))
015:    ),

002 条件式：自分の行の商品が自分と同じか＝新出の商品
003 真：いままでの最大値に +1 する
004 偽：最初に出現した商品と同じ番号にする

いままでの最大値

005:    MAX(
006:      OFFSET(
007:        B:B,
008:        1,
009:        0,
010:        ROW()-2,
011:        1
012:      )
013:    )+1,

005 最大値：()内の最も大きな数を取得
006 指定の位置からズラした範囲を取得
007 OFFSET参照：対象の範囲
008 OFFSET行数：開始位置を1にして見出し(連番)を除外
009 OFFSET列数：開始位置は0のまま
010 OFFSET高さ：自分の位置よりもひとつ上まで（見出しと合わせて-2）
011 OFFSET幅：1列のまま
013 最大値に +1

既にある商品と同じID

014:    INDEX(
015:      B:B,
016:      MATCH($A2,$A:$A,FALSE)
017:    )

014 指定の範囲：この範囲の中の値を返す
015 行数：最初にマッチした商品の行（列数は省略）

整形まとめ

001:    =IF(
002:      ISNUMBER(B1),
003:      IF(
004:        MATCH($A2,$A:$A,FALSE)=ROW(),
005:        MAX(
006:          OFFSET(
007:            B:B,
008:            1,
009:            0,
010:            ROW()-2,
011:            1
012:          )
013:        )+1,
014:        INDEX(
015:          B:B,
016:          MATCH($A2,$A:$A,FALSE)
017:        )
015:      ),
016:      1
017:    )