長い数式を作れるようになりたい、読めるようになりたいというユーザーさんはたくさんいらっしゃいます。
たとえば最終的に、こんな感じ↓の長い数式を作って処理をしたいとき、何も見ずに左から書いていけないからって自信を無くすことはないです。
複数の関数を使うような長い数式は、1 つの関数の結果を 1 つのセルに表示し、その結果を使って次の数式を作り、最終的に 1 つの数式にまとめればよいのです。
今回は日付によって上半期か下半期かを表示するための数式を例に、私がやるんだったらこうするよ、という流れをご紹介します。
作業の流れ
日付を元に上半期 / 下半期を求めるための専用の関数はありません。このようなときに複数の関数による数式を組み合わせた処理をします。
今回は、次の 3 つの処理を組み合わせて処理します。
- MONTH 関数で日付の月を求める
- AND 関数で 4 (月) ～ 9 (月) かどうかを確認する
- IF 関数で上半期と表示するか、下半期と表示するかを分岐する
作業用のセルを準備する
前述の 1. ～ 3. の処理をすることを考えたとき、下図の [半期] 列は 3. の処理をするための場所です。
この状態だと 1. と 2. の処理をする場所がないので、1. と 2. の処理を行うためのの列を 2 つ追加します。
挿入した列の見出しは自分がわかれば何でもよいですが、今回は 1. の処理をする「月」という列と、2. の処理をする「チェック」という列として見出しを用意しました。
MONTH 関数で日付から月を求める
最終的に、〇月だったら△、●月だったら▲ という処理をしたいので、月の情報が必要です。
こんなときに、MONTH 関数を使います。MONTH 関数は、引数に指定したシリアル値 (日付) の月を返す関数です。たとえば、「2021/4/1」なら「4」が返ります。
今回の場合は、1 つ左のセル (セル A2) にシリアル値が入っていて、このシリアル値 (日付) の月を知りたいので下図の数式を作っています。
列を挿入したため、「月」列にも日付の表示形式が適用されているときは、[標準] に戻します。(作業の過程ではよくあること、落ち着いて設定を変えれば OK)
AND 関数で 4 (月) ～ 9 (月) かどうかを調べる
次に、前の手順で求めた月が、「4 以上なおかつ 9 以下」か、それ以外かを調べます。
AND 関数は、引数に指定した複数の論理式のすべての結果が TRUE のときに、TRUE を返す関数です。
前の手順で求めた B 列の月が、「4 以上かどうか」と「9 以下かどうか」の ２ つを調べ、その両方が TRUE だったときに TRUE を返したい (両方を満たしているときに TRUE を返したい) ので AND 関数を使います。
1 つ左のセル (セル B2) の値が 4 以上なおかつ 9 以下であるときに TRUE を、それ以外のときには FALSE を返したいので下図の数式を作っています。
作った数式のセルを下方向へコピーしてほかのセル (日付) の場合に、求めている結果が得られているかどうかを確認します。
たとえば、2021/12/1 は 4 以上かつ 9 以下ではないので FALSE が返っています。
IF 関数で上半期と表示するか、下半期と表示するかを分岐する
次に、C 列の AND 関数の処理結果が TRUE なら「上半期」、FALSE なら「下半期」と表示するための数式を作ります。
IF 関数を使った数式では、論理式の結果が TRUE のときと FALSE のときで処理を分岐できます。
引数の順番や何を指定するのかはこちら↓
今回の場合は、前の手順で作成した C 列の AND 関数の処理結果が TRUE だったら「上半期」、FALSE だったら「下半期」と表示したいので IF 関数を使います。
1 つ左のセル (セル C2) の値が TRUE だったら「上半期」、FALSE だったら「下半期」を返したいので下図の数式を作っています。
作った数式のセルを下方向へコピーして求めている結果が得られているかどうかを確認します。
2021/4/1 から 2021/9/1 は「上半期」が、それ以外の場合は「下半期」という文字列が表示されています。
数式で関数をネストする
複数の関数を組み合わせることを「関数をネストする」といいます。
今回は、B 列に作った MONTH 関数の数式を C 列の数式に、C 列の数式を D 列の数式に組み込んでいきます。
たとえば、C 列の AND 関数の数式ではセル B2 を参照しています。この “B2” の部分に、セル B2 の中身をコピーして貼り付けていけばよいです。
もちろん数式の部分をこんな風に↓ドラッグで選択してもよいのですが、わたしはだいたいキー操作でやってしまうのでそれを書いておきます。
月のセルを選択して [F2] キーを押す、
編集モードになってカーソルが末尾にある状態で [Shift] キー ＋ [↑] キーを押す、
[Shift] キー ＋ [→] キーを押す、
先頭の「＝」以外の部分が選択されたら、[Ctrl] キー ＋ [C] キーを押してコピーして、[Enter] キーを押して編集を終了する。
貼り付け先の数式があるセルを選択して [F2] キーを押す、
貼り付け先となるセル参照 (B2) の部分を選択して、[Ctrl] キー ＋ [V] キーを押して貼り付けます。
2 つ目の論理式のセル参照の部分にも貼り付けます。
貼り付けが終わったら [Enter] キーを押して確定します。
セル C2 を参照していない AND 関数の数式に変更されました。
同様に、C 列の数式の「=」以外の部分をコピーして、D 列の IF 関数の数式のセル参照部分（C2）に貼り付けます。
3 つのセルに分けていた処理が 1 つの数式にまとめられました。
作った数式のセルを下方向へコピーして求めている結果が得られているかどうかを確認します。
2021/4/1 から 2021/9/1 は「上半期」が、それ以外の場合は「下半期」という文字列が表示されています。
セル参照からネストした数式に変更したら、作業用に作った列は削除しても OK です。
複数の関数を必要とする長い数式は、左から書いたり読んだりせずに、関数のカタマリで考えます。
それぞれの数式による処理が間違いなく行われていることを確認してから、カタマリをセル参照の部分に置き換えていけば、最終的に長い 1 つの数式を間違いなく作ることができます。
どんな関数が必要なのかがわからない、とかそういうこともあるでしょう。それは別のお話。
ハンバーグ (AND 関数の論理式) がないとハンバーガー (IF 関数の数式) は作れないので、最終的に作りたいものから逆算して調べて、関数を使った数式で求められるものは求めて、無理なら手入力で「4」とか「TRUE」を準備して試してみてもよいのです。
石田 かのこ
