検証番号を付ける関数について

質問

ここにＡＢＣＤＥＦＧという７ケタの数字があるとしてこの７ケタの数字を使って検証番号Ｈを付けるのですが、その手順としてこの７ケタの数字のうちＤはほとんど使わない数字なのでＡＢＣとＥＦＧを使います。検証番号Ｈの計算方法は（Ａ×１）＋（Ｂ×２）＋（Ｃ×１）＋（Ｅ×２）＋（Ｆ×１）＋（Ｇ×２）で、カッコの積の各部分が２ケタになった場合はカッコの部分は１ケタ目と２ケタ目の数字の和となります。こうして計算して出た数字の下１ケタをＨとしてＡＢＣＥＦＧＨという７ケタの数字を別セルに表示したいのです。この検証番号がついた新しい７ケタを関数でどう表したらいいのか教えていただきたいのです
よろしくお願いします。

回答No.4

nishi6 · ベストアンサー

ツール→マクロ→Visual Basic Editor でVBE画面に移り、挿入→標準モジュール で標準モジュールを挿入します。出てきたコードウインドウに下記マクロをコピーして貼り付けます。

●●●
７桁の数字を扱う場合(次のFunction～End Function までをコピーして貼り付け)
↓
Function Kensyou7Keta(ByVal Num As String) As Double
　　Const Jyosuu = "1210212"　　'// 各桁への乗数
　　Dim L As Integer　　　　　　'// 文字列カウンタ
　　Dim ELM As String　　　　　 '// 各桁の演算結果を2桁表示
　　Dim SGM As Integer　　　　　'// 各桁の演算の合計値

　　For L = 1 To Len(Num)
　　　　ELM = Right("0" & (Mid(Num, L, 1) * Mid(Jyosuu, L, 1)), 2)
　　　　SGM = SGM + Mid(ELM, 1, 1) + Mid(ELM, 2, 1)　'// 桁毎に加算
　　Next
　　'// 最終結果の数字列を作る
　　Kensyou7Keta = Left(Num, 3) & Right(Num, 3) & Right(SGM, 1)
End Function
↑

○使い方
　　シートに戻って、
　　A1に元の数値があり、B1に検証番号を出したければ、
　　B1に、『=Kensyou7Keta(A1)』と入力します。（『』の中です）

●●●
６桁の数字を扱う場合(次のFunction～End Function までをコピーして貼り付け)
ここは最初考えた方法とは違ったようです。修正しました。
Function～End Functionの貼り付けと何回も書いていますが、異なる関数なので、続けて貼り付けてかまいません。

Function Kensyou6Keta(ByVal Num As String) As Double
　　Const Jyosuu = "121212"　　 '// 各桁への乗数
　　Dim L As Integer　　　　　　'// 文字列カウンタ
　　Dim ELM As String　　　　　 '// 各桁の演算結果を2桁表示
　　Dim SGM As Integer　　　　　'// 各桁の演算の合計値

　　For L = 1 To Len(Num)
　　　　ELM = Right("0" & (Mid(Num, L, 1) * Mid(Jyosuu, L, 1)), 2)
　　　　SGM = SGM + Mid(ELM, 1, 1) + Mid(ELM, 2, 1)　'// 桁毎に加算
　　Next
　　'// 最終結果の数字列を作る
　　Kensyou6Keta = Left(Num, 3) & Right(Num, 3) & Right(SGM, 1)
End Function

○使い方
　　シートに戻って、
　　A2に元の数値があり、B2に検証番号を出したければ、
　　B2に、『=Kensyou6Keta(A2)』と入力します。（『』の中です）

●●●
１０からその下一ケタの数字で引いた数とする場合(次のFunction～End Function までをコピーして貼り付け)
７桁、６桁の場合を示します。２つの関数を貼り付けることになります。

Function Kensyou7KetaB(ByVal Num As String) As Double
　　Const Jyosuu = "1210212"　　'// 各桁への乗数
　　Dim L As Integer　　　　　　'// 文字列カウンタ
　　Dim ELM As String　　　　　 '// 各桁の演算結果を2桁表示
　　Dim SGM As Integer　　　　　'// 各桁の演算の合計値

　　For L = 1 To Len(Num)
　　　　ELM = Right("0" & (Mid(Num, L, 1) * Mid(Jyosuu, L, 1)), 2)
　　　　SGM = SGM + Mid(ELM, 1, 1) + Mid(ELM, 2, 1)　'// 桁毎に加算
　　Next
　　'// 最終結果の数字列を作る
　　Kensyou7KetaB = Left(Num, 3) & Right(Num, 3) & Right((10 - Right(SGM, 1)), 1)
End Function

Function Kensyou6KetaB(ByVal Num As String) As Double
　　Const Jyosuu = "121212"　　 '// 各桁への乗数
　　Dim L As Integer　　　　　　'// 文字列カウンタ
　　Dim ELM As String　　　　　 '// 各桁の演算結果を2桁表示
　　Dim SGM As Integer　　　　　'// 各桁の演算の合計値

　　For L = 1 To Len(Num)
　　　　ELM = Right("0" & (Mid(Num, L, 1) * Mid(Jyosuu, L, 1)), 2)
　　　　SGM = SGM + Mid(ELM, 1, 1) + Mid(ELM, 2, 1)　'// 桁毎に加算
　　Next
　　'// 最終結果の数字列を作る
　　Kensyou6KetaB = Left(Num, 3) & Right(Num, 3) & Right((10 - Right(SGM, 1)), 1)
End Function

○使い方
　　シートに戻って、
　　７桁の場合、A3に元の数値があり、B3に検証番号を出したければ、
　　B3に、『=Kensyou7KetaB(A3)』と入力します。
　　６桁の場合、A4に元の数値があり、B4に検証番号を出したければ、
　　B4に、『=Kensyou6KetaB(A4)』と入力します。

●●●
A5、B5セルに３桁数値の入力があり、C5に検証番号を出す場合、
Ｈが最初の計算方式なら、
　　C5に『=Kensyou6Keta(A5&B5)』
10から引く場合は、
　　C5に『=Kensyou6KetaB(A5&B5)』
とします。

回答No.3

nishi6

ユーザー定義関数を作るといいかもしれません。

ツール→マクロ→Visual Basic Editor でVBE画面に移り、挿入→標準モジュール で標準モジュールを挿入します。出てきたコードウインドウに下記マクロをコピーして貼り付けます。

まず、計算方法の確認です。
＞としてこの７ケタの数字のうちＤはほとんど使わない数字なので
　Ｄは４桁目のことを言っているのでしょうか。
　それとも、どの桁に現われるか分からないある数値（例えば「9」とか）でしょうか。
　　→上から４桁目を削除するとして考えました。

　　=KensyouNo(A1) のように使います。

Function KensyouNo(ByVal Num As String) As Double
　　Const Jyosuu = "1210212"　　'// 各桁への乗数

　　Dim L As Integer　　　　　　'// 文字列カウンタ
　　Dim ELM As String　　　　　 '// 各桁の演算結果を2桁表示
　　Dim SGM As Integer　　　　　'// 各桁の演算の合計値

　　For L = 1 To Len(Num)
　　　　ELM = Right("0" & (Mid(Num, L, 1) * Mid(Jyosuu, L, 1)), 2)
　　　　SGM = SGM + Mid(ELM, 1, 1) + Mid(ELM, 2, 1)　'// 桁毎に加算
　　Next

　　'// 最終結果の数字列を作る
　　KensyouNo = Left(Num, 3) & Right(Num, Len(Num) - 4) & Right(SGM, 1)

End Function

補足
＞１つ目に元々６ケタＡＢＣＤＥＦという数字である場合は関数はどうなるのでしょうか
　どうなるよりも、どうしたいかがよく分かりません。
　　→７桁と同様、ＡＢＣＤＥＦ---->ＡＢＣＥＦＨ　にしてみます。

　　上の関数のままで６桁も計算できるはずです。

＞２つ目に検証番号Ｈの出し方で最終計算の数字下一ケタですがＨの算出は１０からその下一ケタの数字で引いた数とする場合はどうなるのでしょうか
　　最終行を次のように修正します。

　　'// 最終結果の数字列を作る
　　KensyouNo2 = Left(Num, 3) & Right(Num, Len(Num) - 4) & Right((10 - Right(SGM, 1)), 1)

＞３つ目にＡ１のセルにＡＢＣ、Ｂ１のセルにＥＦＧと入力したらＣ１のセルにＡＢＣＥＦＧＨと表示する場合はどのように関数を設定したらよろしいでしょうか

=KensyouNo(A1&"0"&B1) のように使います。

お礼

補足追加ですいません。コピーをしたあとにどのような作業をすれば変換された数字がでるのかがわかりません。コピーのみでいけるのですか？

補足

見立ての通りＤは４ケタ目ということです。
下記マクロをコピーとあるのは数式部分だけをコピーするということ
でよろしいのですか？
それとも～のように使いますとか'//以降の文章もいう言葉の部分も必要なのですか？
補足の６ケタというのは元々７ケタあるとか考えず、単に６ケタのＡＢＣＤＥＦという数字があると考えて、それに対応して上記の計算による検証番号をつけて７ケタのＡＢＣＤＥＦＨで考えた場合です。Ｄを削除とかはこの場合考えません。
なにせ関数に関しては初心者でどのようにしたらいいのかさっぱりなのです。

回答No.2

maron--5

◆こんな方法もありますよ（式が帰って長くなりましたが参考までに）
=LEFT(A1,3)&RIGHT(A1,3)&RIGHT(SUMPRODUCT((MOD(ROW(1:6),2)+((MOD(ROW(1:6),2)=0)*2))*MID(LEFT(A1,3)&RIGHT(A1,3),ROW(1:6),1))-SUMPRODUCT((MOD(ROW(1:6),2)=0)*(--MID(LEFT(A1,3)&RIGHT(A1,3),ROW(1:6),1)>=5))*9)

補足

素早い返答ありがとうございます。かなり複雑な関数ですね。これでいけると思ったのですが急遽必要になった事項があり、これに付随して３点質問してもよろしいでしょうか。１つ目に元々６ケタＡＢＣＤＥＦという数字である場合は関数はどうなるのでしょうか。２つ目に検証番号Ｈの出し方で最終計算の数字下一ケタですがＨの算出は１０からその下一ケタの数字で引いた数とする場合はどうなるのでしょうか。もし下一ケタの数字が０の場合はＨは０とします。３つ目にＡ１のセルにＡＢＣ、Ｂ１のセルにＥＦＧと入力したらＣ１のセルにＡＢＣＥＦＧＨと表示する場合はどのように関数を設定したらよろしいでしょうか。質問を分けてした方がいいのかもしれませんが、よろしくお願いします。

回答No.1

noname#204879

=LEFT(A1,3)&RIGHT(A1,3)&MOD(LEFT(A1,1)+INT(MID(A1,2,1)*2/10)+MOD(MID(A1,2,1)*2,10)+MID(A1,3,1)+INT(MID(A1,5,1)*2/10)+MOD(MID(A1,5,1)*2,10)+MID(A1,6,1)+INT(MID(A1,7,1)*2/10)+MOD(MID(A1,7,1)*2,10),10)

補足

素早い返答ありがとうございます。かなり複雑な関数ですね。これでいけると思ったのですが急遽必要になった事項があり、これに付随して３点質問してもよろしいでしょうか。１つ目に元々６ケタＡＢＣＤＥＦという数字である場合は関数はどうなるのでしょうか。２つ目に検証番号Ｈの出し方で最終計算の数字下一ケタですがＨの算出は１０からその下一ケタの数字で引いた数とする場合はどうなるのでしょうか。もし下一ケタの数字が０の場合はＨは０とします。３つ目にＡ１のセルにＡＢＣ、Ｂ１のセルにＥＦＧと入力したらＣ１のセルにＡＢＣＥＦＧＨと表示する場合はどのように関数を設定したらよろしいでしょうか。質問を分けてした方がいいのかもしれませんが、よろしくお願いします。