固定桁の数字であるかを調べたい

固定桁の数字であるかを調べたい

IDとなるデータが桁落ち/数字以外が混在する

ご提供頂くデータの中に、行を識別するIDとなる品番や型番が桁落ちしていたり、数字以外が混在しる場合があり、そのままデータベースに保存できない場合があります。

JANコード 品名
0130011112222 製品1
0130011113333 製品2
130011114444 製品3
0130011115555 製品4

これはデータ入力時にあやまって入力した場合や、エクセルの表示書式による自動変換、CSVエクスポート時に先頭の0がエクセルによって落とされてしまう場合などが原因です。

数字かどうかを調べ、桁数が足りているかを調べる

数字かどうか調べるには、ISNUMBER(イズナンバー)関数を、桁数が足りているかどうかを調べるには、LEN(レン)関数を使用します。

ISNUMBER関数は、引数に指定した内容が数字であればTRUEを、数字でなければFALSEを返します。

LEN関数は、引数に指定した内容の純粋な文字数を返します。

一度に結果を求めるためにIF(イフ)関数を使用します。それぞれの書式は、

ISNUMBER(テストの対象)
LEN(文字列)
IF(論理式, [値が真の場合], [値が偽の場合])

になります。

冒頭の表を例に、製品1の品番データが数字かどうかをチェックし、数字の場合は13桁あるかどうかチェックするとします。

=IF(ISNUMBER(A2), LEN(A3)=13, FALSE)

式の解説

=IF(ISNUMBER(A2), LEN(A3)=13, FALSE)

ISNUMBER関数で、セルA2が数字かどうかを論理値(TRUEまたはFALSE)で取得します。 数字であればTRUE、数字以外であればFALSEを返します。

LEN関数で、セルA2の文字数を取得し、12文字かどうかを比較した結果を論理値(TRUEまたはFALSE)で取得します。 12文字であればTRUE、12文字以外であればFALSEを返します。

IF関数で、ISNUMBER関数で取得した値が数字ならばTRUEとなりLEN関数の結果と文字数比較を実行し、数字以外ならばFALSEを返します。

IF関数の第1引数

=IF(ISNUMBER(A2), LEN(A2)=12, FALSE)

論理式内にISNUMBER関数を指定し、数字か数字以外かの分岐するようにします。 本例では、JANコードであるセルA3を指定します。

ISNUMBER関数の第1引数

ISNUMBER(A2)

数字かどうかをチェックしたいセル、または数字を指定します。

IF関数の第2引数

=IF(ISNUMBER(A2), LEN(A2)=13, FALSE)

第1引数の結果が真(TRUE)であった場合に、LEN関数を指定し、桁数のチェックができるようにします。

LEN関数の第1引数

LEN(A2)

文字数を取得したいセル、または文字列や数字を指定します。 本例ではJANコードであるセルA3を指定します。

IF関数の第3引数

=IF(ISNUMBER(A2), LEN(A2)=12, FALSE)

第1引数の結果が偽(FALSE)であった場合の処理を指定します。 本例では数字ではないことを意味するFALSEを指定します。

Excel 2019、Office 365版のExcelをお使いの場合

新たに追加されたIFS(イフス)関数とSWITCH(スウィッチ)関数が使用できます。 IFS関数の書式は、

IFS(論理式1, 真の場合1, 論理式2, 真の場合2, ...)

となり、論理式と真の場合の組み合わせは最大127個まで指定できます。 SWITCH関数の書式は、

SWITCH(検索値, 値1, 結果1, 値2, 結果2, ..., [既定の結果])

となり、値と結果の組み合わせは最大126個まで指定できます。

Massteryで対応するとこうなる

投稿時点で、Massteryには桁数のみをチェックする仕組みはございません。 しかしながら商品データに特化したデータ変換に強みがありますMassteryには、JANコードであるかをチェックする関数をご用意しています。こちら関数には、値が13桁か8桁かの数字であるかをチェックします。

MassteryでのJANコードチェック例 前半 MassteryでのJANコードチェック例 後半

※Massteryは関数名が全て大文字、全て小文字も同じ動作をいたします。

指定内容

変換対象カラム
Massteryはフォーマットの項目単位で変換を指定しますので、入力ファイルの項目(列の名称)を指定します。今回は項目の状態を調べて出力する項目を新たに設けて割り当てるようにします。
変換方法
変換対象カラムに値またはロジックを適用する「=」を指定します。
値または変換ロジック
状態を分かりやすく説明するために出力したいテキストを記述しますが、その他にも出力することもあるので別のテキストが追加されるようにするため、数式“&”で連結するようにし、「”チェック項目”&[JANコードの桁数では無い]」と指定します。
条件1_仕入先項目
数値でるかをチェックするCHECK_JAN関数(check_janでも可)を使用し、調べる項目を指定します。
条件1_演算子
数値以外と限定した条件なので「=(完全一致)」を指定します。
条件1_値
CHECK_JAN関数は、13桁数字または8桁の数字でなければ“0”を返却しますので、「0」を指定します。

CHECK_JAN関数式の解説

Massteryでの書式は以下になります。

CHECK_JAN("参照先カラム名称")

引数は1つになります。

引数

CHECK_JAN("JANコード")

数値かどうかを確認する項目を指定します。カラム名称を指定する場合は、ダブルクォーテーションで囲います。

13桁数字または8桁の数字であれば“1”、以外ならば“0”が返却されまるので、返却値を条件にして出力を制御します。

関連記事

同じカテゴリの記事

お問い合わせ

Massteryのより詳しい情報、ご対面でのご案内をご希望の方は是非お問い合わせください。
下記のお問い合わせフォームよりお申し込み頂ければ担当の営業からご連絡いたします。

運営会社 フォルシア株式会社

Close Bitnami banner
Bitnami