エクセルで、同じ行にある、同一データに上から順番に識別番号をつける方法を紹介します。
例えば、顧客が購入した商品を、VLOOKUP関数で顧客の名前を入力するだけで一覧で出せる表を作成するときなどに役立ちます(^^)/
たとえば、購入者と買った商品名を時系列順に入力していくリストがあるとします。
通常、VLOOKUP関数でAさんが購入した商品のデータを取得したいときは、同一行に同じデータが複数ある場合(Aさんが別商品の購入で複数回登場する場合)は一番上にある参照データから目的のデータを引っ張ってくるため、Aさんが最初に購入した商品しか抽出できません。
この方法を使えるようになれば、同じ行にあるすべてのAさん購入商品を抽出することが可能になります。
それでは、早速解説に入ります！
完成形｜こういうデータを作りたい!!(>_<)
まず、完成形はこちらです。
時系列順に誰が何を買ったかをひたすら入力する”縦リスト”のデータから、顧客ごとに何を買ったかを”横リスト”に表示できるようにしました。
“縦リスト”では、Aさんの何回目の購入かを示した”識別子”という列を作っており、Aさん１回目の購入なら「A-1」、Ｂさん２回目の購入なら「B-2」といったかたちで表示されるようにしていきます。
それによって”横リスト”で、左から順に、Aさん１回目なら「A-1」を参照、Aさん２回目なら「A-2」を参照、、、とVLOOKUP関数で引っ張る形にすれば、このデータの作成は実現しそうですね(>_<)
これを実現するために使用する関数は『COUNTIF関数』と『VLOOKUP関数』と『COLUMN関数』の三つだけです!!
ぐっと実現の現実味が出てきましたね(*’ω’*)
まず、購入タイミングごとに識別用の数字をつけよう!!
識別用の列を追加して、B列の「氏名」のなかで、それぞれ何回同じ人が出てくるかを数えてみます。
指定した範囲のなかで、設定したデータが何個入っているかを数えるのは、COUNTIF関数を使用します。
ここで登場するのが、『絶対参照』と『相対参照』という考え方です。
簡単に説明すると、関数はコピペすると、設定したセル番地がコピー元とコピー先の座標の違い分だけずれる仕組みになっています。
そのセル番地を絶対にずらしたくない場合に絶対参照(＄マーク)をつけるのです。
列をずらしたくない場合は、セル番地のアルファベットの前に＄マークを、行をずらしたくない場合はセル番地の数字の前に＄マークをつけます。(＄はセル番地を選択した状態でＦ４キーを押すと設定できます)
絶対参照・相対参照についての詳細な解説はこちらの記事を参照してください。
エクセル（Excel）の参照機能について｜絶対参照／相対参照の使い分け
まず、一般的なCOUNTIF関数の使い方に即して、範囲だけ行列ともに絶対参照にして計算してみましょう。
カウントしたいデータについては、セル番地そのままでも問題ありませんが、上のイメージ図では、横にコピーしたときにずれないように列だけ絶対参照にしています。
すると、以下のような結果になります。
Aさんが合計何回購入したかを調べることには成功しましたが、本当に知りたいデータではありません、、
今参照している行が、誰の何回目の購入かを知るためには、COUNTIF関数の範囲の指定を工夫する必要があります。
以下のように指定してみましょう(^^)
今度は、参照する範囲の起点を絶対参照に、終点を相対参照かつ検索条件の行に設定してみます。
最初からリストのすべての行を参照せず、下にコピーするごとに、範囲の終点が広がっていくかたちをとります。
そうすることで、そこまでで●さんが何回でてきたのかを判定できるため、結果として何回目の●さんかがわかりそうです(>_<)
結果はこちら
無事識別用の番号をふることができました。
『氏名』と『-(ハイフン)』と『識別用の回数』を”＆”でつなぐことにより、当初の目論見通り、A-1,A-2,B-1,B-2…と識別用のキーを作成していきます。
結果、こうなりました!!
もうゴールが見えてきました(^^)/
いよいよ本番！｜VLOOKUP関数で、購入者ごとの購入商品一覧を
作成してみよう!!
ここまできたら、あとは、VLOOKUP関数をくみ上げるだけです!!
列番号を取得するCOLUMN関数を使用することで、右にコピーするごとに+1される連番の作成に成功しました。
これをＧ列の横リスト氏名と”＆”でつなぐことで、目的のデータになりそうですが、購入品を表示する列は8列目～11列目なので、このままだと、A-8、A-9…と続くデータになってしまいます。
つまり、１から続く連番にしたい場合は、COLUMN関数から7を引くことで実現しそうです。
列数を取得すると８になってしまうなら、-7すればいいじゃない(>_<)
ということで、先ほどの関数のCOLUMN()に-7をくっつければ完成です!!
VLOOKUP関数解説
COUNTIF関数の応用編では、VLOOKUP関数を使用しました。
VLOOKUP関数の使い方がいまいちわからない、、という方はこちらの記事を参照してみてくださいね!!
【関数の王道！】VLOOKUP関数｜完全解説(エクセル)
少しでも仕事に役立つ記事を皆様にお伝えできるように頑張っていきますので、気に入っていただけたら、他の記事もぜひ読んでみてくださいね!!
ではまた(^^)/
