「Excel」シートに選択メニュー(コンボボックス)を作る方法

こんばんは、( ノ゚Д゚)こんにちは、おはようございます。おやすみなさい! とっちーです。

 

選択メニューは、名前の定義とデータ入力規則を使えばセルに設定できるんだけど、文字数が多かったりしたらセルの結合するようになります。

 

そうすると、ソートとかする際に何かと手間がかかる場合があります。気にしなければ問題ごとではありませんが。

Excelシートの上に設置すると便利になるツールがあります。「フォームコントロール」や「ActiveXコントロール」等です。

 

今回は、名前の定義とデータ入力規則を他の方法で設定する方法をお伝えします。

「それ、いらね~」ってものから「やっぱり!いる!」って思うこともあるかと。

 

 

コンボボックス(フォームコントロール)の設置

 

それでは、シート上にコンボボックスを設置してみましょう。

 

 

設置したい場所を決めておきましょう。次にリボン内の開発タブを選択し①「挿入」コマンドのメニューの中から②「コンボボックス(フォームコントローラ)」を選びましょう。

 

 

 

 

次にコンボボックスを設置させたい位置の①左上に+のマウスポインタを合わせて、②右下方向へドラッグ(マウスの左ボタンを押したまま)させましょう。

 

位置が決まれば、ドラッグを終了(マウスボタンから指をはずす)させます。

 

③出来上がったコンポボックスは、標準のセルの高さより大きめになります。

 

セルの高さを変更するか、③コンボボックスを右クリックして大きさの変更が可能な状態にしてから④大きさを調整しましょう。

⑤マウスボタンを指から外すと終了できます。

 

コンボボックス(フォーム)の書式設定

 

コンボボックスの書式設定は、サイズ・保護・プロパティ・代替テキスト・コントロールの5つが設定できます。

 

 

⑥コンボボックスにマウスポインタを重ねて右クリックすると⑦メニューが表示されます。

 

⑧メニューの一番下にあるコントロールの書式設定を選択してみましょう。「コントロールの書式設定」のダイアログが表示されます。

 

サイズを変更したい場合は、サイズの高さや幅などを調整してみましょう。角度は変更できません。倍率も変更可能です。

 

保護は、シートを保護する場合にコンボボックスをロックして使用できないようにするか、ロックを外してシートが保護されても使用できるようにするかの2択になります。

 

プロパティは、セルの大きさに合わせてコンボボックスの移動やサイズを変更しないかサイズは変更しないの2択と印刷をするかしない(☑をチェックしない)かの2択になります。

 

代替えテキストは、Webなどコンボボックスのオブジェクトが表示されない場合にテキスト文を表示させたい場合に記述しましょう。

 

最後にコントロールですが、この部分でコンボボックスの使用目的の設定が行えます。

 

コンボボックス(フォーム)の入力範囲の設定

 

コントロールの書式設定のコントロールタブにある、入力範囲やリンク先セル、ドロップダウンリストの行数を選択しましょう。

 

ドロップダウンリストの行数は、2行以上にしましょう。1行にすると選択できる方向が定まってしまうか、扱いにくくなります。

 

☑3-D表示(3)は、チェックが外れている状態です。チェックを入れると少しだけ3Dになります。

 

 

⑨コンボボックスを右クリックで選択してメニューを表示させて、コントロールの書式設定のダイアログを表示させましょう。

 

 

⑩コントロールタブを選択して入力範囲の右側にあるセルの選択ボタンをクリックします。

 

⑪コンボボックスで表示させる内容があるシート上の1行をマウスで範囲選択します。

 

⓫※名前の定義でリストが出来上がっている場合は、名前の定義で作成している名前を指定しましょう。

 

⑫リンクさせるセルが必要な場合のみセルを選択しましょう。今回は商品番号に適用したいためセルを設定してみました。

 

⑬入力の確認ができたら「OK」ボタンを押しましょう。

 

コンボボックス(フォーム)の動作確認

 

作成したコンボボックスが、考えていた通りに表示してくれるか確認をしておきましょう。

 

⑭コンボボックスの右側にあるプルダウンを左クリックして表示されるかどうかと選択範囲が考えていた範囲か確認しましょう。

 

大丈夫なら⑮や⑯のようにいろいろと選択してみましょう。

商品番号のところに表示される数値は、⑰選択範囲で設定した箇所の降順に番号が付きます。

 

コンボボックス(フォーム)と関数を使う方法

 

ここから後は、使用例になります。

 

 

まず元データ表の商品番号ですが、番号を単に上から付けてもいいのですが、少しでも安全性を高めるためにExcelのROW()関数を使用して番号を表示させてみました。

ROW()関数は、セルの行数を表示する関数になります。

使用方法は、=ROW()になります。今回-3と後ろにつけているのは、元データ表の商品番号の開始位置が4行目になるからです。

=ROW()ですと4を表示してしまいます。そこで引き算(-3)をして番号を調整しました。

 

 

商品番号を使って元データ票から単価を求めたいと思います。

MATCH関数とINDEX関数の組み合わせで求めます。IFERROR関数はERROR表示対策です。まずMATCH関数で相対的な位置を求めたいと思います。

 

MATCH関数の書式は、MATCH(検査値、検査範囲、照合の型)になります。

 

今回は、MATCH(商品番号、元データの商品番号、0(完全一致型))で設定しています。

 

上図の場合MATCH関数の戻り値は、7になります。

INDEX関数の書式はINDEX(範囲、行番号、列番号、領域番号)になります。

 

今回は、INDEX(元データの検査したい範囲、MATCHで求めた戻り値、列は動かないので0)、領域番号は省略しています。

 

ここで使用する関数は、単価を導き出すため以下のようになります。

=INDEX($D$3:$D$33,MATCH(I5,$B$3:$B$33,0),0)

そして、このままだと空白文字などが入力された場合以下のように#N/Aが表示されます。

 

 

そこで、IFERROR関数を使用してエラー表示を値0に指定しています。

=IFERROR(INDEX($D$3:$D$33,MATCH(I5,$B$3:$B$33,0),0),0)

エラーが起こったときは、値0を代入するという式になります。

 

 

上図は、演算子だけで購入金額を求めています。セル番地のJ5の値とK5に入力した値の積を購入金額に表示するようにしています。

 

コンボボックス(フォーム)と関数の見本完成

 

最後に今回のコンボボックスとエクセルシートに使用した関数との簡単な連携を表示しました。

 

 

コンボボックスを選択することにより商品番号が変更され、関数で導き出される金額も変更されます。

 

まとめ

 

商品注文の名前の個所は、セルの結合で作成しても同様にできますが、こんな機能があるんだなぁって思っていただけるだけでうれしく思います。