Help us understand the problem. What is going on with this article?

コード生成に役立つEXCEL関数テクニック

データベース用テーブルを作成する際には、EXCELなどの表計算ソフトを活用することが多いと思いますが、主キーなどに用いるコードは一定の法則性があるため、自動作成しにくいことが多いです。そんなときに覚えておくと非常に便利なテクニックを紹介していきます。

1:種別コードごとのユニークなシーケンスコードを作成する

要はこういうことをしたい場合です。下の表はこれから種別コードに即したシーケンス(連番)コードを生成していく予定です。

入力規則: 種別コード2桁+シーケンス3桁

表1-1:シーケンスコード
code1.jpg

オートフィルを使えばシーケンスは作れますが、逐一種別コードごとにソートして、それに対しオートフィルを繰り返して、というのは非常に面倒ですので、これを関数一行で制御できるようにします。

使用する関数

  • COUNTIF関数 COUNTIF(範囲,対象)
  • CONCATENATE関数 CONCATENATE(結合対象のセル,…)
  • TEXT関数 …ゼロパディングに用います TEXT(対象のセル,表示形式)

手順

①シーケンスを取得

まずは種別のコードごとの件数を数えたいのでCOUNTIF関数を使用します。COUNTIF関数で範囲を記述する場合、主に2通りの記述方法があります。一つは件数を数える場合、もう一つはシーケンスを作る場合です(重複データ削除にも使えます)

たとえば、以下の表には調べたいデータが混在しています。

表1-2:COUNTIF関数を用いた件数とシーケンス取得
code2.jpg

=COUNTIF($A$2:$A$20,$A2) …(1)件数
=COUNTIF($A$2:$A2,$A2) …(2)連番

ここではシーケンスを作りたいので、(2)の方法を採用します。これは、範囲の下限が絶対参照、範囲の上限が相対参照になっているのがポイントで、ペーストが行われるごとに範囲が1セルずつ増えていき、調査対象が何回目に出現しているか表示されることになるので、これを利用してシーケンスを作ることができます(表1-2における黄色く塗りつぶしたセル《コード番号13》を参照してください。連番になっているのがわかります)

②ゼロパディング

このシーケンスに対し、コード入力規則に従い、3桁のゼロパディング(ゼロ埋め)を行います。TEXT関数を使用すれば、指定の形式で表示できます。

=TEXT($B2,"000")

表1-3 シーケンスのゼロパディング
code3.jpg

③値の結合

後は種別コードとシーケンス部分を結合するだけです。ここで使用するCONCATENATE関数は結合する全てのセルに対し、自動で文字列として認識してくれます。また、後で値を増やしたい場合にも引数を増やすだけなので、非常に処理が楽です。なお、種別コードに対しても桁数が一致するようにTEXT関数を埋め込んで種別コードを2桁に制御しておきます。あとは、この関数をコピーアンドペーストしていくだけです。

=CONCATENATE(TEXT($A1,"00"),$C1)

表1-4 シーケンスコードの生成
code4.jpg

これを一度に記述するとこうなり、この関数をコピーアンドペーストしていくことで、種別コードごとのシーケンスを作成することができます。

=CONCATENATE(TEXT($A2,"00"),TEXT(COUNTIF($A$2:$A2,$A2),"000"))

2:同一グループの空白セルに対し、グループ番号をフィルする

要はこういうことをしたい場合です。リスト表などでは、見出しだけグループ番号が振られ、あとのセルは空白のまま…ということがよくあります。これが数件程度なら普通にオートフィル機能を使ってもいいですが、これが何十、何百となってくるといちいちオートフィルをかけるのは非常に手間がかかります。ですので、これを極力少ない計算行を用いて、一行の関数で記述します。

表2-1:値のフィル
f1.jpg

使用する関数

  • COUNTA関数 …値の入っている件数を取得 COUNTA(範囲,対象)
  • COUNTIF関数 COUNTIF(範囲,対象)
  • OFFSET関数 OFFSET(セル,移動行,移動列)
  • * … *-1で負数を作ります

OFFSET関数とは?

では、作業前にOFFSET関数のおさらいです。OFFSET関数は任意のセルから明示的に相対位置のセルを取得する関数で、今回の作業の要となります。まずは、以下の表を御覧ください。

表2-2:OFFSET関数の基本
F2.jpg

B3に入力された関数はOFFSET(A3,-1,0)となっているので、A3から-1行、つまり1行上のセル、A2を見ています。そこでB3にはA2と同じ値が代入されるのです。要はこの応用で、移動行の値を流動的に変更すればいいのです。

そうすると、移動行数は表2-3のようになっているのが理想でしょう。

表2-3:各データの求めたい移動行数
F3.jpg

ところが、これは普通に作ろうとしてもかなり作業が困難です。なぜなら、随時フィルしたい行数が変わっていくからです。そこで、どうやってこのグループごとに流動する行数を作っていくかが本題となります。

手順

①COUNTA関数でグループ化する

COUNTA関数は、本来、空白でない値の個数を取得するために使用するものですが、COUNTIF関数でシーケンスを作る方法と同じ要領で下限を絶対参照、上限を相対参照にして範囲を指定すると、対象行に対し次の値が出現するまで同じ値を返していくので、グループ化することができます。

COUNTA($A$2:$A2,A2)

表2-4:各データに対するグループ番号の付与
F4.jpg

ここまでくればしめたものです。なお、このグループの計算行も集約して関数1行だけで処理できないか試行錯誤してみましたが、無理でした。

②COUNTIF関数でグループごとの移動行数を求める。

先程のシーケンスコード作成に用いたようにCOUNTIF関数を使って、各グループごとのシーケンスを作っていくと、順番に1,2,3,4…と並びます。そして、この値をOFFSET関数における移動行数にすればいいのです。ここで大事なポイントは、先頭行の移動行数がゼロになるように、各シーケンスから1ずつ差し引いておくことです。

表2-5:COUNTIF関数を用いた移動行数取得(絶対値)
F5.jpg

③負数に変換する

今回は、基準セルから上へ、つまり負数の分移動していくので、現在の値を負数に変換する必要があります。負数にするには、いろいろな方法がありますが、一番手っ取り早いのが-1を掛けるという方法です。これを用いることで、移動行に対し、負の数だけ移動する(上に移動する)ことができます。

表2-6:負数への変換
F6.jpg

④数式の作成

あとは、OFFSET関数を用いて数式を作成するだけです。D2には先程作成した移動行数が入っているので、基準値行の各参照セルに向かって相対的に行数が変動することになります。そして、この関数をコピーアンドペーストで繰り返していけば、グループ番号が自動生成されていきます。

OFFSET(A2,D2,0)

表2-7:OFFSET関数を用いた値のフィル
F7.jpg

これを一行で処理するのはまず不可能だと思いますが、計算行を1つだけ足して合計2行あれば、このような記述ができます(先ほどとの違いは先に移動行を負数にしているので、調整数は正の数になります)。

OFFSET(A2,COUNTIF($B$2:B2,B2)*-1+1,0)

裏技

この関数を埋め込んだ状態にしておけば、基準値にどんな値を入力しても、次の基準値まで自動的にグループ化され値をフィルしてくれるので、グループ番号だけでなく、グループ名作成にも役立ちます(シーケンスにする必要はなく、また値の重複も関係ありません)。

表2-8:任意の参照値を入力した場合
f8.jpg

BRSF
職業、PG・SE・DBエンジニア。オープン環境のwebプログラムをメインにシステム構築担当。使用言語はPHP(cakePHP、Laravel含)jQuery、JavaScript、ExcelVBA、Perl、Ruby、Python。現在Vue、React、Angular強化中。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした