エクセルのプルダウン連動で勘定科目→補助科目を入力する
勘定科目・補助科目をプルダウンリストで入力できるようにします。
勘定科目を横に並べて、その下に補助科目を縦に並べていくスタイルで表を作り、名前定義を使う方法はたくさん紹介されています。
この記事では、表を作り変えずに、勘定科目・補助科目の組をダウンロードした表そのままの状態で、プルダウンリストを作る方法を紹介します。
※下記、過去記事も合わせて見てみてください。
- プルダウンリストを表示させる（入力規則）
- プルダウンリストの元データを別シートで作るには？
- プルダウンリストの追加・削除に自動対応する方法
- 複数のプルダウンリストを連動させる（名前定義を使わない方法）
- エクセルのプルダウンリストで選択すると同時に色を変える
勘定科目・補助科目のプルダウンリストを連動させる
勘定科目マスタ、補助科目マスタを
会計ソフトからダウンロードしてきて、
このデータに基づいてプルダウンリストを作ります。
補助科目のプルダウンリストを作るのが難しい！
このデータを見ると、
- 補助科目の一覧が縦に並んでいる
- 「勘定科目：現金」のように、補助科目が「ない」ものもある
といった特徴があります。
普通のエクセルサイトに書かれているような、
「勘定科目名で名前を定義して、補助科目用のプルダウンリストを作成」
という方法では、
補助科目のプルダウンリストを作ることができません。
プルダウンリストの「元データが入っているセル」はどこ？
補助科目のプルダウンリストを表示させるためには、
勘定科目（＝大項目）ごとに、
補助科目（＝小項目）の一覧が入っているセルを指定する必要があります。
例えば、
- 勘定科目「普通預金」 → 補助科目「科目マスタ!G3:G4」
- 勘定科目「売掛金」 → 補助科目「科目マスタ!G5:G7」
という感じです。
補助科目がない科目の場合は、
リストに何も表示させたくないわけですから、
どこか空白のセル（例えば、「科目マスタ!G1」）をリストとして表示させます。
先ほどの書き方で書けば、
- 勘定科目「現金」 → 補助科目「科目マスタ!G1」
ということです。
このような感じで、
勘定科目と、リストとして表示させたいセルの対比表を作ります。
Ｂ列は、
iferror、match関数を使って、
勘定科目ごとに、
補助科目のリストの「最初の行」を計算しています。
例えば、
売掛金なら「５」
買掛金なら「８」という感じです。
現金の場合は、補助科目のリストが存在していないので「１」としています。
Ｃ列は、counta関数を使って、補助科目の件数を計算しています。
売掛金なら「３」
買掛金なら「４」という感じです。
そして、
Ｄ列で、補助科目のリストとして表示すべきセル範囲を作っています。
売掛金なら、
G5セルから下に３件分（＝G5セル～G7セル＝「G5:G7」）ということで、
「科目マスタ!G5:G8」というデータを作っています。
indirect関数とvlookup関数を組み合わせればプルダウンの完成
ここまでできれば、
あとは、入力規則を作るだけです。
入力規則の「元の値」の欄で、
エクセルの「indirect関数」と「vlookup関数」を使うことで、
補助科目のプルダウンリストができあがります。
vlookup関数を使って、
勘定科目に対応した、補助科目リストが入っている「セル（の場所）」を取得。
そして、
indirect関数を使うことで、
そのセルに入っている内容を、
エクセルにプルダウンリストとして認識させます。