Excel ケース共有: 関数式を使用して ID 番号の信頼性を確認する

前回の記事「Excelケース共有：「ドラッグ＆ドロップ」だけで実現できる5つの効率的なテクニック」では、「ドラッグ＆ドロップ」だけで実現できる5つの効率的なテクニックを学びました。 Excel テーブル内。今日はIDカード番号の真正性をチェックするためのExcelの関数式についてお話しますので、ぜひご覧ください。

偽造ワクチンや汚染粉乳など恐ろしいことについて話していると、本当に腹が立って無力です。このようなことについては、一般人として、関係部門が協力してくれることを願うばかりです。このような悪質な事件が二度と起こらないように、管理と検査の取り組みを強化することができます。同時に、悪影響を避けるために、作業にさらに注意を払い、エラーが発生しやすい多くのリンクを繰り返しチェックする必要があります。たとえば、従業員ファイルの ID 番号は、問題が非常に発生しやすい災害領域です。

今日はID番号に誤りがないか確認する計算式を紹介します。

一般に、ID カード番号のエラーには、長さエラーと内容エラーの 2 種類があります。

エラーの原因のほとんどは、入力プロセス中に発生します。長さのエラーについては、現在、すべて 18 桁の数字です。長さにエラーがある場合は、見つけるのが簡単ですが、次のいずれかが発生した場合は、数字が表示されます エラーは検出が非常に難しく、将来的に重大な結果を引き起こす可能性があるため、今日の焦点はコンテンツの正確性を検証することです。

1. ID 番号の構成原理

まず、ID 番号のエンコード規則について少し理解する必要があります。

18 桁の ID 番号は、ddddddddyyyymmddxxsp の合計 18 桁で構成され、そのうち 1 ～ 6 桁は市外局番、7 ～ 14 桁は日付です。出生、15 ～ 17 は性別コード、最後の 1 桁は前の 17 桁に基づくチェック ディジットです。

チェック ディジットの計算ルールはさらに複雑です:

(1) 最初の 17 桁のオントロジー コードの加重加算式

S = Sum (Ai * Wi)、i = 0, ..., 16、まず最初の 17 桁の重みを合計します。

Ai: i 番目の ID 番号のデジタル値を表します

Wi: i 番目の位置の重み係数を表します

Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

#(2) 計算モジュール

Y = mod(S, 11)

(3) 法 # により対応するチェックコードを取得##Y: 0 1 2 3 4 5 6 7 8 9 10

チェックコード: 1 0 チェックコードはルールに従って計算され、最後の桁と比較され、矛盾する場合は、問題。このルールを理解するのは非常に難しいので、例を見てみましょう:

1. ID 番号の前の 17 桁にさまざまな係数を掛けます。 1 桁目から 17 桁目までの係数は次のとおりです。

7-9-10-5-8-4-2-1-6-3-7-9-10-5-8- 4-2 。

2. 17 桁の数値と係数を乗算した結果を加算します。

3. 合計を 11 で割った余りは何ですか?

4. 残りは 0-1-2-3-4-5-6-7-8-9-10 の 11 桁の数字のみです。対応する最後の ID 番号は 1-0-X-9-8-7-6-5-4-3-2 です。

5. チェックコードを比較すると、余りが3の場合、IDカードの18桁目は9となることが分かります。対応する番号が 10 の場合、ID カードの最後の番号はローマ数字の 2 になります。

例: 男性の ID 番号は [53010219200508011x] この ID カードが正規の ID カードであるかどうかを確認してみましょう。

まず最初の 17 桁の積の合計を取得します

[

(5*7) (3*9) (0*10) (1* 5) (0*8) (2*4) (1*2) (9*1) (2*6) (0*3) (0*7) (5*9) (0*10) (8* 5) (0 *8) (1*4) (1*2)

]

は 189 で、189 を 11 で割ると、結果 189/11=17 が得られます。 --2 、つまり剰余が 2 であることを意味します。最後に、対応するルールにより、剰余 2 に対応するチェック コードが X であることがわかり、これが正しい ID 番号であると判断できます。

2. Excel を使用して ID カード番号の信頼性を確認する

原理が明確になったので、問題はどのように使用するかです。この計算プロセスの Excel 式 それを表現するために、ここで式を共有します。

=IF(RIGHT(A2)=MID("10X98765432",MOD(SUM(MID(A2, ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11) 1,1),"true","false")

これは配列数式であることに注意してください。数式を入力または貼り付けた後、Ctrl キーと Shift キーを同時に押して Enter キーを押す必要があります。

この式は非常に複雑に見えますが、これを使用する場合は、2 つの A2 をフォーム内の ID 番号に対応する位置に変更するだけです。

数式の考え方について簡単に説明します。核心部分は

です。

MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17") ))),11) 1,1)

この文字列は、チェック コード ルールに従って前の 17 桁を計算し、それを RIGHT (A2) と比較するために使用されます。 (A2)はA2から右端のビットを取り出し、if関数を使って比較結果から判定します。

この式を再度使用して、記事の冒頭の図の ID 番号が正しいかどうかを確認できます。

結果は false です。

この式を使用して、フォームに間違った ID 番号がないかどうかを確認することもできます。間違っていると判断された番号を適時に修正して、問題の芽を摘み取ります。

関連する学習の推奨事項: Excel チュートリアル

以上がExcel ケース共有: 関数式を使用して ID 番号の信頼性を確認するの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。