2016.04.29 カテゴリ：EXCEL

エクセルの入力規則で、リスト機能の内容を自動更新できるようにする

こんにちは、 篠原です。


今回は、今までの全8回にわたって説明した各関数や設定を活用した、簡単な入力フォーム作ってみます。数式は複雑ですが、理屈さえ理解が出来れば、強力な道具になると思いますよ。

便利な関数

解説済みの関数や設定は以下のとおりです。

  1. INDEX関数
  2. MATCH関数
  3. COUNTA関数
  4. INDIRECT関数
  5. OFFSET関数
  6. 名前定義Paet1
  7. 名前定義Paet1
  8. データの入力規則


    　

入力フォームの仕様

　これから作る入力フォーム（というほどのものではありませんが）は、

  • 　セルに入力するデータは、リスト機能を使い、そこから選択入力する
  • 　選択された入力データから、次の入力セルに関連する入力データを動的リスト表示できるようにする
  • 　関連する入力データが増えた場合でも、出来るだけ数式の変更が無いようにする。
  • 　

関数で参照する基礎表を作る

  • 1-1　基礎表の全体像は図のとおりです。
    入力規則にOFFSET関数：基礎表の全体像

参照するセル範囲に名前を設定する

（設定方法はこちらのリンクを参照してください）。


範囲名の一覧を示します。

範囲の方向範囲名セル範囲依存関係
行方向野菜分類A3:A50野菜名の検索キーになる
列方向野菜名C2:E2野菜分類の値がここの検索キーになる
行方向野菜名基準C3:C52野菜名で検索された位置に移動する場合の基準位置になる
列方向野菜品種G2:AK2野菜名の値がここの検索キーになる
行方向野菜品種基準G3:G52野菜品種で検索された位置に移動する場合の基準位置になる



　範囲名は、今回の表作成では、たくさん設定ます。こんな場合は、「定義された名前」グループの「選択範囲から作成」機能を使うと楽に範囲名が定義できますのでチャレンジしてみてください(ここでは細かな説明は省略します）。

入力規則を設定する

　ここがメインです。


　入力規則では、リスト機能を使います。
　この表は野菜の分類から、その分類に属する野菜名を選択し、選択された野菜名からその詳細の品種を選択できる構造にします。
　 つまり、選択リストから入力された内容により、次の入力セルの選択リストの内容が自動的に変更されるという機能にします。


　完成する入力フォームは下の図のとおりです（見た目はすごく簡単ですね）。
入力規則にOFFSET関数：入力フォーム


　まず、入力規則を設定するセル「B2」を選択します。
　「データ」タブをクリックします。
　「データツール」グループの「データの入力規則」アイコンをクリックします。
入力規則にOFFSET関数：区分の入力


　「データの入力規則」ダイアログボックスが表示されます。
入力規則にOFFSET関数：区分の入力


　「設定」タブの「条件設定」グループの「入力値の種類」ドロップダウンリストボックスから「リスト」を選択します。
　以降の入力規則の設定方法は同様の操作で行います。
入力規則にOFFSET関数：区分の入力


　「元の値」数式ボックスに次のように数式を入力します。

=OFFSET(野菜分類,0,0,COUNTA(野菜分類),1)



　■数式の意味
入力規則にOFFSET関数：区分の入力

  • 1-1　OFFSET関数：範囲「野菜分類」の行、列の移動はせず、COUNTA関数の戻り値3を高さに設定する
  • 1-2　COUNTA関数：範囲「野菜分類」の空白でない行数（3）を返す
    　このCOUNTA関数を使うことで、下の図のように、無駄な空白リストを排除することが出来ます。
    入力規則にOFFSET関数：区分の入力
    入力規則にOFFSET関数：区分の入力

　次に「セルB4」を選択します。
　セルB2と同様に、次のように入力規則のリスト機能を設定します。

=OFFSET(野菜名基準,,MATCH(B2,野菜名,0)-1,COUNTA(OFFSET(野菜名基準,,MATCH(B2,野菜名,0)-1)),1)



　■数式の意味
入力規則にOFFSET関数：種類の入力

  • 2-1　OFFSET関数1：野菜分類の入力を受けて、範囲「野菜名基準」は以下の関数の結果から行数は変更なしで右に1列移動し、高さが5、幅が1の範囲（D3:D7）となる。
  • 2-2　MATCH関数1：範囲「野菜名」からB2（根菜類）に一致する位置(2）-1∴1が返され、右に1列移動となる
    　-1するのは、移動がない場合結果を「0」にするため。
  • 2-3　COUNTA関数：下のOFFSET関数2の結果から(5)が返される
  • 2-4　OFFSET関数2：野菜分類の入力を受けて、範囲「野菜名基準」は範囲D3:D7へ自動変更される
  • 2-4　MATCH関数2：範囲「野菜名」からB2（根菜類）に一致する位置(2）-1∴1が返され、右に1列移動となる
    　-1するのは、移動がない場合結果を「0」にするため。

　次に「セルB6」を選択します。
　セルB2と同様に、次のように入力規則のリスト機能を設定します。

=OFFSET(野菜品種基準,,MATCH(B4,野菜品種,0)-1,COUNTA(OFFSET(野菜品種基準,,MATCH(B4,野菜品種,0)-1)),1)



　■数式の意味
入力規則にOFFSET関数：詳細の入力

  • 3-1　OFFSET関数1：野菜の種類の入力を受けて、範囲「野菜品種基準」は以下の関数の結果から行数は変更なしで右に15列移動し、高さが2、幅が1の範囲（V3:V4）となる。
    • 3-2　MATCH関数1：範囲「野菜品種」からB4（人参）に一致する位置(16）-1∴15が返され、右に15列移動となる
      　-1するのは、移動がない場合結果を「0」にするため。
    • 3-3　COUNTA関数：下のOFFSET関数2の結果から(2)が返される
    • 3-4　OFFSET関数2：野菜の種類の入力を受けて、範囲「野菜品種基準」は範囲V3:V4へ自動変更される
    • 3-5　MATCH関数2：範囲「野菜品種」からB4（人参）に一致する位置(16）-1∴15が返され、右に15列移動となる
      　-1するのは、移動がない場合結果を「0」にするため。

以上で入力規則、名前定義、OFFSET関数、COUNTA関数、MATCH関数を組み合わせての使い方は終わりです。



ブログのお題拝借?
Tag: エクセル EXCEL OFFSET関数 入力規則 MATCH関数 COUNTA関数

コメント


認証コード9682

コメントは管理者の承認後に表示されます。