Excelのばね計算機をカタログレス化させる方法
後編-1限目：セルの入力規則と名前の定義でばね種類をリスト選択化する
計算機が出来たところで、電卓を使用する必要なくなりましたが
部品カタログから毎回ばねの情報を調べるのは手間です。
そこで、計算機にばねの情報を組込んでしまおうという話です。
ここからは、図の様にリスト選択する仕組みを作成していきたいと思います。
リスト選択する仕組みは、セルを選択して「データの入力規則」を使います。
参考記事→「Excel 機能「 データの入力規則 」ドロップダウンリスト作成」
入力値の種類を「リスト」にして、「元の値」に
リストの内容を入力するのですが、この方法は複数あります。
１つ目は、直接カンマ区切りで入力する方法です。
今回は、ばね種類の例として「SWR,SWU,SWY」とします。
2つ目は、セルの範囲を指定する方法です。
この例では、ばねの情報を別のシートで管理する為
先に「DB」というシートに、ばねの種類を入力してあります。
3つ目は、名前の定義をして指定する方法です。
リスト化する範囲を選択し、右クリック「名前の定義」で
選択範囲に名前「ばね種類」を付けます。
リスト化させるセルを選択して「データの入力規則」の「元の値」に
「名前の定義」で付けた名前「=ばね種類」を入力します。
1～3のどのやり方でもリスト選択になります。
後編-2限目：ばねの種類によってリストの内容を変化させる
ばねの種類を選択した後は径を選択しますが、ばねの種類によって径は変化します。
1限目の内容を応用する事で、この動作も可能となります。
まずは、データベースのシートに種類とその径の一覧表を作成します。
そして、1限目と同様に範囲を指定して名前を定義するだけです。
今回は「SWR」「SWU」「SWY」の名前を定義します。
※図の径は一部を抜粋したものです。
径をリスト化するセル選択をしたら、データの入力規則で入力値の種類をリストにし
元の値には「INDIRECT()」という関数を使用します。
「=INDIRECT(セル名)」とする事で、参照セルの値を名前として引用します。
つまり、図の例の「=INDIRECT($B$11)」は、「=SWR」と同じになります。
そして、セルB11はリスト選択によって値が変化しますので
元の値の内容もそれに連動して変化するので、リストの内容も変化します。
次に、自由長Lについてですが、こちらは径のリスト作成の応用です。
データベースのシートの行に種類&径を項目とし
列には自由長Lの一覧を入力したものを作成し、名前の定義をします。
※解説用の為、図の数値は実際とは異なります。
自由長Lのリストを作成する為には、種類&径の情報が必要です。
元の値には「=INDIRECT(種類のセル$径のセル)」を入力する必要があるので
「=INDIRECT($B$11&$C$11)」と入力をします。
※名前を定義する際の名前は、必ずINDIRECTの()内と一致させて下さい。
作業が終わったら動作確認をしてみましょう。
ばねの種類と径の組合せによって、自由長Lのリストが変化すると思います。
後編-3限目：VLOOKUP関数を使ってばね定数を表示させる
1限、2限でばねの型式をリスト選択する事が出来ました。
最後は選択したばねの型式から、ばね定数の値を返し
ばね計算機に組み込んで、自動的に計算させる仕組みにします。
まずは、データベースのシートに型式とそのばね定数の一覧表を作成します。
※図の型式とばね定数は、一部を抜粋したものです。
型式に応じたばね定数の値を返すには「VLOOKUP」関数を使用します。
参考記事→「Excel関数「VLOOKUP」範囲の１列目で値を検索し、指定列と同じ行の値を返す」
ばね定数の値を返すセルには「=VLOOKUP(」まで入力し
最初の「検索値」は、ばねの型式を入力します。
ばねの型式は複数のセルを繋げる必要があるので、セル名間を&で繋げます。
よって、この例の配置の場合は「=VLOOKUP(B11&C11&D11&E11,」となります。
次の「範囲」は、データベースの検索値の列と、返す値となるばね定数を指定します。
よって、この例の配置の場合は「=VLOOKUP(B11&C11&D11&E11,DB!B8:C34,」となります。
次の「列番号」は、先程指定したデータベース範囲内でのばね定数の列番号「2」を
最後の「検索方法」は、完全一致の「0」を入力します。
以上で完成した式は「=VLOOKUP(B11&C11&D11&E11,DB!B8:C34,2,0)」となります。
作業が終わったら動作確認をしてみましょう。
これでカタログレス化させる仕組みの完成となります。
このあとは必要な情報を入力して増やしていきましょう。
本日はこれでおしまいです。
ご観覧頂きありがとうございました( ｀ー´)ノ
コメント