プレス技術研究所VBA講座

上記の表は、種別を「雑貨」と「文具」に切り替えることによつて、参照する価格表が切り替わる。

上図の様に、「台帳2」には、「雑貨」と「文具」の価格表がある。

「台帳2」シートのB3からB7までを選択して、[数式]の[名前の定義]の[新しい名前]を
クリックして、[名前]に「雑貨」と入力し、[参照範囲]に「=台帳2!$B$3:$B$7」と入力し
[OK]をクリックする。

「台帳2」シートのE3からE7までを選択して、[数式]の[名前の定義]の[新しい名前]をクリックして
[名前]に「文具」と入力し、[参照範囲]に「=台帳2!$E$3:$E$7」と入力し、[OK]をクリックする。

上図の様にセルC9からC13を選択して、[データの入力規則]の[設定]の[入力値の種類]を
「リスト」に変更し、[元の値]に「雑貨,文具」と入力し、[OK]をクリックする。
尚、[元の値]の「雑貨」と「文具」の間に「,」カンマを入力すること。

上図の様にセルD9からD13を選択して、[データの入力規則]の[設定]の[入力値の種類]を
「リスト」に変更し、[元の値]に「=INDIRECT(C9)」と入力し[OK]をクリックする。

上図のセルE9に、「=IF(D9=" "," ",IF(C9="雑貨",VLOOKUP(D9,台帳2!$B$3:$C$7,2),
　IF(C9="文具",VLOOKUP(D9,台帳2!$E$3:$F$7,2))))」と入力し、下方にコピーする。

上図のセルG9に「=IF(F9=" "," ",E9＊F9)」と入力し、下方にコピーする。

上図の様に、「種別」の選択によって、「商品名」のリストが切り替えられる。

上図の左側は、「一覧表」である。右側は「台帳1」である。
「一覧表」は、常時商品と価格を参照するためのものである。
「台帳」は、その基本となるもので、商品数が増えると商品を追加入力して行くもので、
価格変更などがされてゆく。その「一覧表」への転記を簡単に行い、「一覧表」の行数も
自動的に増加できるものである。
但し、「台帳」シートの金額が変更された場合は、「一覧」シートの金額も自動で変更される。
ここでは、「一覧」シートから「台帳1」シートに追加された商品を確認するために、セルC5を
クリックする。

上記は追加された商品を、確認し転記するためにセルC5をクリックしたものである。
リストが表示されて、追加された商品名が表記されている。「タンブラー」「マグカップ」は
すでに転記されているので、「ティポット」が新規に追加されたものである事が分かる。
「ティポット」をクリックすれば入力されるのである。

上図の様に、セルC3からC18を選択して、[入力値の種類]を「リスト」に変更し[元の値]に
「=INDIRECT("台帳1!C3:C"&COUNT(INDIRECT("台帳1!D:D"))+3)」と入力する。

「台帳1」シートの「商品台帳」は、セルB5に「IF(C5=" "," ",B4+1)」と入力し、以下の行に
コピーしておく。C5セルとD5セルに入力すると、B列に連番で自動で入力される。

INDIRECT関数の引数は、「INDIRECT(参照文字列 [, 参照形式])」で、文字列で指定した
セル番地や「名前」で、そのセルに入力されている内容を返す。
[参照文字列]は、セル番地や、セル番地に付けられた「名前」を文字列で指定する。
[参照形式]は、「参照文字列」に指定する、セル番地の表記方法に合わせて論理値を指定する。
この引数に、「TRUE」を指定すると「A1形式」となり、「FALSE」を指定すると「R1C1形式」
となる。この引数を省略した場合は「A1」形式を指定したものとみなされる。
COUNT関数の引数は、「COUNT(値1[, 値2,…])」で、数値データの個数を求める。
一般的には引数にセル範囲を指定し、それらのセル範囲に入力されている数値セルの数を求める。
引数には、範囲ではなく直接データを指定することもできる。

「一覧」シートの5Bセルに「=IF(C5=" "," ",B4+1)」と入力し、以下適当にコピーしておく。

「一覧」シートのD4セルには「=IF(B4="","",VLOOKUP(B4,台帳1!B4:D5,3))」と入力し、
以下適当にコピーしておく。

上図の様にC5を入力すると、B5セルもD5セルも自動的に入力される。

尚、枠線は[ホーム]ー[枠線]で引けばよい。

上図の様に、法人格を省くと「警告」が表示される。

上図の様に、[数式]に「=OR(NOT(ISERR(FIND("株式会社",B3))),NOT(ISERR(FIND("有限会社",B3))))」
と入力する
OR関数の引数は、「OR(論理式1,論理式2 [, 論理式3,…)」で指定した「論理式」の内の一つでも「TRUE」
になる場合は「TRUE]になる。すべてが「FALSE」になる場合は「FALSE]になる。
NOT関数の引数は、「NOT(論理式)」で指定した「論理式」が「TRUE」になる場合は「FALSE」が返され、
「FALSE」になる場合は「TRUE」が返される。
ISERR関数の引数は、「ISERR(テストの対象)」で「テストの対象」に「#N/A」以外のエラーが発生して
いれば「FALSE」を返す。エラーが発生しているかを調べたい数式を直接指定するか、その数式が入力され
ているセル番地を指定する。
尚、「#N/A」エラーは、関数の引数が足りない場合に表示される。
FIND関数の引数は、「FIND(検索文字列),対象 [, 開始位置]」で「対象」文字列内に含まれる「検索文字列」
を検索し、その「検索文字列」が見つかれば文字位置を返す、「検索文字列」が複数個含まれる場合は、
一番左側の文字位置を返す。
この際、第3引数の「開始位置」を指定すると、その文字位置より右側の文字から検索できる。検索文字列が見つからない場合は、「#VALUE!」エラーが返される。
尚、第3引数の「開始位置」は、検索を開始したい文字位置を「対象文字列」の一番左を「1」として数えた
文字数で指定する。この引数を省略した場合は、「対象」文字列の先端から検索される。

上図の様に、大文字を交えて入力すると、「警告」が表示される。

上図の様に、[数式]に「=AND(EXACT(LOWER(ASC(B3)),B3),LEN(B3)>=6)」と入力する。
数式が長い場合は一部が見えないので、数式部の右端の矢印を押すと見えない部分が見える
ようになる。
EXACT関数の引数は、「EXACT(数値1 , 数値2 [, …])」で２つの文字を比較して、一致して
いれば「TRUE」、一致していなければ「FALSE」を返す。
英字の大文字と小文字を区別した厳密な比較ができる。文字の比較は「文字列1=文字列2」の
様に等号の比較演算子でもできるが、等号で比較すると英字の大文字と小文字の区別はできない。
LOWER関数の引数は、「LOWER(文字列)」で文字列中に含まれるアルファベットをすべて
小文字に変換する。
LEN関数の引数は、「LEN(文字列)」で「文字列」の文字数を返す。
だから、EXACT関数で、入力文字列と小文字を比較して、LEN関数で文字数が６文字以上かを
確認しAND関数で判定している。

上記の様に、ハイフン付きでないと「警告」を表示する。

郵便番号を入力する場合は、7桁の郵便番号に必ず半角のハイフンを挿入して入力させる為に、
上図の様に、[数式]に「=AND(LEN(B3)=8,FIND("ー",B3)=4,ASC(B3)=B3)」と入力する。
AND関数の引数は、「AND(論理式1,論理式2 [, 論理式3,…])」で、引数に指定したすべての
「論理式」が「TRUE」になる場合は「TRUE」、一つでも「FALSE」が含まれている場合は
「FALSE」を返す。
LEN関数の引数は、「LEN(文字列)」で、文字列の長さを返す。
FIND関数の引数は、「FIND(検索文字列,対象 [, 開始位置])」で「対象」文字列内に含まれる
「検索文字列」を検索し、その「検索文字列」が見つかった文字位置を返す。
「検索文字列」が複数見つかった場合は、一番左側の文字位置を返す。
ASC関数の引数は、「ASC(文字列)」で、文字列中に含まれるアルファベット、数字、カタカナ、
記号などのすべてを半角文字にする。漢字やひらがななど半角文字が存在しない文字は、その
まま全角の文字が返される。
上図の数式は、LEN関数で文字数が「８文字」かを調べて、FIND関数で「ー」の位置が「4」
文字目にあるかを調べて、ASC関数で入力文字が半角かを調べて、AND関数ですべての条件を
満たしているかを判定している。

上記の様に、データを英数半角大文字で入力を統一したい場合、半角と全角が混在すると
「注意」を表示させる。

上記の様に、「データの入力規則」の[設定]の[数式]に「=EXACT(UPPER(ASC(B2)),B2)」
と入力する。
EXACT関数の引数は、「EXACT(数値1 [, 数値2,…])」で２つの文字列を比較して、一致して
いれば「TRUE」、していなければ「FALSE」を返す。
UPPER関数の引数は、「UPPER(文字列)」で文字列中に含まれるアルファベットをすべて
大文字に変換する。
ASC関数の引数は、「ASC(文字列)」で文字列中に含まれるアルファベット、数字、カタカナ、記号などのすべてを半角文字にする。漢字やひらがな、など半角文字が存在しない文字は、その
まま全角の文字が返される。

「データの入力規則」の[設定]の[数式]に「=AND(ISTEXT(B2),LEN(B2)=5)」と入力する。
AND関数の引数は、「AND(論理式1,論理式2 [, 論理式3,…])」で、引数に指定したすべての
「論理式」が「TRUE]になる場合は「TRUE」、一つでも「FALSE]が含まれる場合は「FALSE」
を返す。
ISTEXT関数の引数は、「ISTEXT(テストの対象)」で「テスト対象」が文字列なら「TRUE」
そうでなければ「FALSE」を返す。
LEN関数の引数は、「LEN(文字列)」で「文字列」の文字数を返す。
だから、確実に5文字の文字列で、数値ではない入力だけを可能にする為に、ISTEXT関数で
文字列の判定をして、LEN関数で文字数の判定をしている。
だから、AND関数によって文字列で、文字数が5であるのかを判定している。

上図の様に、表の数値部分を選択して、「入力規則」をクリックする。
「データの入力規則」が開くので、[設定]の[入力値の種類]を「ユーザー設定」にし[数式]に
「=MOD(C3,500)=0」と入力する。
MOD関数の引数は、「MOD(数値,除数)」で割り算の余りを求める。

[エラーメッセージ]の[スタイル]に「注意」、[タイトル]に「入金単位」と[エラーメッセージ]に
「５００円単位で入金してください。」と入力して[OK]をクリックする。

上記の様に、500円単位以外の金額を入力するとメッセージが表示される。

上図の様に[データ]の[データの入力規則]をクリックすると「入力規則」が設定できる。
表の数値部分を選択して「入力規則」をクリックする。

「データの入力規則」が開くので、[入力値の種類]を「ユーザー設定」にし、[数式]に
「=$C$2>=$C$10」と入力する。

[エラーメッセージ]の[スタイル]に「注意」、[タイトル]に「金額超過」と[エラーメッセージ]に
「予算額以内にしてください。」と入力して[OK]をクリックする。

上図の様に、「遊興費」に「50000円」と入力すると、注意メッセージが表示される。