育てる摘要マスタ

2025年12月19日(金) 09:25 2025年12月19日(金) 10:01

事務所スタッフ

こんにちは、事務所スタッフです。

以前の投稿で、現金出納帳におけるマスタの役割をご説明しました。

こちらの投稿では既にある程度決まった摘要・科目をマスタへあらかじめ入力しておくことで、自動的に科目が入るという内容をご説明しました。

では、新規で出納帳を作成するため摘要や科目がまだ定まっていない場合にはどういった仕組みが効果的でしょうか。

今回は「育てる摘要マスタ」ということで、入力欄に入力した内容が自動的に摘要マスタに追加され、入力すればするほど摘要マスタが成長するといった仕組みをご紹介します。

少々長くなりますので、目次を付けておきます。

完成品

左右に分かれた表で、それぞれ左が入力用、右が育てるマスタになります。
実際に運用する場合はマスタを別シートに作成することになりますが、今回は見やすさを考慮して並べて表示しました。

入力用の表に摘要、借方、貸方をそれぞれ入力すると、マスタの表に自動的に追加され、以降入力欄にはマスタから引用されたプルダウンリストが表示されるようになります。

使用する機能

今回使用する主な機能をご紹介します。

テーブル・・・表を管理しやすくする機能。上図での緑色の部分と、白・グレーで色分けされた部分を自動的に生成します。
プルダウンリスト・・・入力用の摘要部分に表示されているリスト。
関数「XLOOKUP」・・・表から任意のデータを検索して拾ってくる関数。
関数「ARRAYFORMULA」・・・一番上の行に入力した関数を、ニ行目以降にも適用する関数。スプレッドシート専用関数。
関数「UNIQUE」・・・特定の範囲内に含まれる文字列から、重複したものを除いて転記する関数。
関数「IF」・・・提示した条件によって表示する内容を変化させる関数。
関数「ISBLANK」・・・指定したセルが空白だった場合に実行される関数。

これらを組み合わせて成長するマスタを作っていきます。

①テーブルの作成

まずはテーブルを作成します。
これは簡単なので、サッと作っていきます。

まずは入力用のテーブルを作ります。
一番上に列の表題となる文字をそれぞれ入力し、テーブルにしたい範囲を適当に選択します。

選択したセルの上で右クリックメニューを表示し、「テーブルに変換」を選択すると、すぐにテーブルが作成されます。
「表_1」となっているところを任意のテーブル名に変更(今回は入力用)したらテーブルの完成です。
テーブル名、各列の表題部分の名前は今後何度も使うので分かりやすい言葉を入れるようにしましょう。

同じようにマスタのテーブルも作って下準備は終わりです。

(注)テーブル名は同一のスプレッドシート内で重複して指定できません。必ず別の名前を付けるようにします。

②マスタに自動で追加される摘要を作成する

実はこれも簡単です。下記の文字を指定した場所にコピペしてみましょう。


=UNIQUE(入力用[摘要])

貼り付け位置：「マスタ」テーブルの「摘要」列の一番上
これだけでOKです。

早速、入力用の摘要欄に適当な文字を入力してみましょう。

入力した文字列がマスタの方に表示され、なおかつ重複が無視されて単一の文字列が並んでいると思います。
指定した範囲の中から重複したものを除いて表示させる関数が「UNIQUE」関数です。

③マスタの関数を設定する

マスタの借方の一番上に次の関数を入力します。


=ARRAYFORMULA(IF(ISBLANK(マスタ[摘要]),"",XLOOKUP(マスタ[摘要],入力用[摘要],入力用[借方])))

同じように貸方の一番上にはこちらを入力します。


=ARRAYFORMULA(IF(ISBLANK(マスタ[摘要]),"",XLOOKUP(マスタ[摘要],入力用[摘要],入力用[貸方])))

違いは最後の[]内の文字が借方から貸方に変わっただけです。

少々複雑ですが、分解しながら見ていきましょう。

「ARRAYFORMULA」はアレイフォーミュラと読み、ARRAYは配列(データを並べる)、FORMULAは数式を意味します。
スプレッドシートにのみ存在し、Excelには実装されていません。
()で括られた内容を入力したセルの下方向にも適用することができるので、数式一つ作れば下にコピペする必要もなくなるためとても便利です。
一点注意があるとすれば、この数式の下方向に何か入力されるとエラーになってしまう事です。

ではその括られた内容はどういった内容になっているか見ていきましょう。

「IF(ISBLANK(マスタ[摘要]),""」
この部分はIF関数とISBLANK関数をまとめて使用しているので一緒に説明します。
IF関数は「もし～なら、～を行う」といった内容の関数で、とても頻繁に使われます。
今回使用している条件が「ISBLANK(マスタ[摘要],"")」の部分です。
ISBLANK関数は()で括られた部分(指定したセル)が空欄であるかどうか、を判定する関数です。
合わせることで「指定したセルが空欄の場合は～を実行し、そうでない場合は～する」という内容が行われます。

ではその「指定したセル」がどうなっているかと言いますと、

マスタ[摘要]

といった文字が書かれているのが見えます。
この文字列、実はテーブルの特定の列全てのセルを指定する方法で、シート名やセル番号を入力しなくても感覚的に範囲選択することが出来ます。この方式を使いたい為に表をテーブルに変換していたのです。
最初の「マスタ」の部分がテーブルの名前で、[]で括られた部分がそのテーブル内の列名を表しています。
つまり、「マスタテーブルの摘要列全てのセル」を意味しています。
そしてその後にある「,」はIF関数のそれぞれの要素を区切る記号で、まず「条件」が提示されたので「,」で区切って「条件が一致した場合の結果」を記述しています。
「""」は本来なら「文字列を数式に組み込む時にくくる記号」で、カッコと似たような使い方をするのですが、今回は「空欄」を表現したいので「""の間に何も入れない」としています。

ここまでを組み合わせると、「マスタテーブルの摘要列が空欄の場合、このセルは空欄とする」という命令が行われました。

実はここまでの数式は無くても成立するのですが、使わない場合画面がエラー文だらけになるので見た目を整える為に使っています。
ここ以外にも色々な場所で使う基本数式なので覚えておくと便利です。

続いて、空欄では無かった場合、つまり文字列が入っていた場合の処理です。それが
「,XLOOKUP(マスタ[摘要],入力用[摘要],入力用[借方])」
の部分になります。
先程述べた通り、「,」は区切りで、IF関数で使用している部分です。
次のXLOOKUPという関数は、元々ExcelでVLOOKUPという関数が使用されていたもののいくつかの欠点があったため、新たに作られて欠点を補っている関数です。

指定した列を縦に検索して、検索ワードと一致した行の、別の列のセルに入っている値を持ってきなさい、という意味の関数です。

最初の要素は検索キーです。
「マスタ[摘要]」の部分は、マスタテーブルの中で、指定したセルと同じ行の、摘要列に入力されている値になります。
上の図で言えばオレンジの点線で囲まれた部分が該当します。

次の要素は検索を行う範囲です。
入力用テーブルの摘要列から探したいので、「入力用[摘要]」が入ります。

最後に一致した場所が見つかった場合どの値を返すか、なので
入力用テーブルの借方列の値を返す、という意味で「入力用[借方]」が入ります。

ではここまでを繋げてみましょう。

IF(ISBLANK(マスタ[摘要]),"",XLOOKUP(マスタ[摘要],入力用[摘要],入力用[借方]))
「もし、該当セルと同じ行のマスタテーブル、摘要列が空欄である場合は空欄に、空欄でない場合はその文字列を使って入力用テーブルの摘要列を検索し、該当した行の借方列の値を取って来なさい」
という命令文になりました。

画像で説明しますと、

これは入力用テーブルの借方貸方にそれぞれ一部科目を入れてみたところです。
例えば該当セルがマスタテーブルの借方一番上（仕入と書かれている部分）とした場合、
マスタの摘要列には「あああ」と入っているので入力用の摘要から「あああ」を探し、同じ行の借方列に入っている「仕入」の文字列を拾ってきました。
同じようにあああの貸方には現金が入りました。

ちなみに、上のあああ借方には仕入と入っていますが、下のあああに別の文字列を入力するとどうなるでしょうか？
検索は上から順番に探し、見つかった時点で打ち切られるので、一番上のものだけが採用されます。
一番上で入力ミスがあると以降もミスになってしまうので、気をつけて入力しましょう。

④入力用の摘要欄にプルダウンメニューを設定する

続いては、入力用の摘要欄にプルダウンメニューを設定し、一度入力した文字列はリストから選択できるようにします。

まずプルダウンメニューを設定したい入力用テーブルの摘要列、一番上のセルで右クリックメニューを開き、下の方にある「プルダウン」を選びます。
画面の右側にデータの入力規則というプルダウンの設定項目が現れるので、「条件」を「プルダウン」から「プルダウン(範囲内)」に変更します。
すぐ下に田の字のような記号の入った入力欄が現れるのでそこに「マスタ[摘要]」と入力します。つまりマスタテーブルの摘要列に入っている文字列を使ってリストを設定するわけです。
設定すると入力用テーブルの摘要列全てにプルダウンが設定されたでしょうか。

これで「一度入力された内容はリストに自動的に追加されるプルダウンリスト」が完成しました。

⑤入力テーブルの借方貸方を自動化させる

入力テーブルの借方貸方は「自分で入力するか、しない場合は自動で表示させる」という処理を行う関係で、上で使用したARRAYFORMULAが使用できません。
ですので全てのセルに同じ数式を入力しておき、「新たに入力する場合は数式を上書きして値を入力し、既にマスタに登録されているものについては自動で表示される」といった仕組みにします。

まずは入力用テーブルの借方、一番上にこちらの数式を入力します。


=IF(ISBLANK(入力用[摘要]),"",XLOOKUP(入力用[摘要],マスタ[摘要],マスタ[借方]))

③で説明した数式と似ていますが、実際ほぼ同じような意味です。
検索キーと検索をかけるテーブルを逆にし、入力用テーブルの摘要列の文字を使ってマスタテーブルの摘要を検索し、見つかった行の借方列の値を表せ、といった意味ですので説明は省きます。

さてこちらを入力すると図のようなエラーが発生します。
これは数式が間違ったわけではなく、循環依存というもので、簡単に言うと対象のセル同士でデータが行き来して止まらず、無限ループになりますよ、といった意味になります。
もちろん無限ループになると処理を永遠に繰り返すことになりますので、何も対策されていない場合処理が止まってしまったり余計な負荷がかかってしまうなどの不具合が発生します。
なので、エラーとして検出させて無限ループに入る前に止られてしまうわけです。

とはいえ、今回はこの循環を利用した処理になるので、なんとか無限になる前に数回で止めてもらいたいところです。

そこでエラー内容に書かれている通り[ファイル]＞[設定]を開いて設定を変更します。

画面左上のメニューから「ファイル」＞「設定」と選択すると、「このスプレッドシートの設定」という画面が開きます。
最初は「全般」というタブが開いているので、隣の「計算」タブを選択し、真中あたりの「反復計算」を「オン」に変更します。
最大反復回数は最初「50」に設定されているので、「1」など少ない数字に直して「設定を保存」します。
これでエラーが解消されたでしょうか。

解消されたら一番上の数式をそのままテーブル内の下の全てのセルにコピペするか、一番上のセルからハンドルを下まで伸ばして、列内に全て同じ値が入力されているようにします。

同様に数式「=IF(ISBLANK(入力用[摘要]),"",XLOOKUP(入力用[摘要],マスタ[摘要],マスタ[借方]))」の最後の「借方」の部分を「貸方」に変更したものを貸方の全ての列に貼り付けます。