小技集

以下の表の「作業内容」の項目に値を入力すると同時に「No」の項目に連番を表示する方法について解説していきます。

まずは準備段階として、「No」の項目の先頭に「1」のみを入力します。
次に、セルB6以降に「No」を表示する数式を入力していきます。

セルB6以降に入力する数式は、以下のような式になります。

・該当する行に関して、C列「作業内容」に値が入力されている場合に、B列「No」に連番を表示する
・「No」に表示する番号は、「No」を表示する行よりも上の範囲の最大値に１を加えた番号になる

この内容を満たした数式は、以下になります。

=IF(C6="","",MAX($B$5:B5)+1)
// セルB6に入力する数式

MAX関数で指定した範囲の最大値を取得することができるため、IF関数で該当する行のC列に値が入力されている場合に関してのみ、MAX関数で取得した値に１を加えた値を表示しています。

MAX関数の引数に設定している範囲（$B$5:B5）は、該当する行よりの上の範囲になります。
セルB6の場合はセルB5～B5の範囲、セルB7の場合はセルB5～B6の範囲、セルB8の場合はセルB5～B7の範囲、という感じになります。

範囲の始まりのセル（B5）に関しては固定し、終わりのセル（数式を入力したセルの真上）に関しては相対的に変化させる必要があるため、「$B$5:B5」のように始まりのみを絶対参照（$）にして終わりは相対参照にしています。

この数式を、連番を表示させたい範囲の最後までコピーすることで、「作業内容」の項目の値の有無に応じて連番を表示することができます。

この方法を活用する場合、行の挿入時に注意すべき点があります。
行を挿入した場合、挿入した範囲には数式が表示されません。
そのため、挿入した範囲内の「作業内容」の項目に値を入力してしまうと、間違った連番になってしまいます。

また、挿入したセルの次の行（B12）の数式に関しては、以下のように参照範囲がずれてしまいます。

=IF(C12="","",MAX($B$5:B8)+1)
// 理想はMAX($B$5:B11)となるべき

そのため、挿入した範囲にも数式を反映させる場合は、挿入した行の一つ上のセル（B8）の数式を、挿入した行の一つ下のセル（B12）までコピーする必要があります。

この数式の場合、上記の点が少しややこしいので、参照範囲がずれない別の数式についても解説していきます。

参照範囲がずれない数式とは、以下のような数式になります。

=IF(C5="","",COUNTA($C$5:C5))

この数式は、「No」の先頭の項目であるセルB5に入力します。

この数式は、数式を入力した行までの「作業内容」の項目に値が入力されているセルの数を表示するといったものなります。
指定した範囲内に値が入力されているセルの数を取得するためにCOUNTA関数を活用しています。
COUNTA関数に指定する範囲に関しては、セルB5に入力する数式の場合はセルC5～C5の範囲、セルB6の場合はセルC5～C6の範囲、セルB7の場合はセルC5～C7の範囲と、終わりの位置のみを相対参照で反映する必要があります。

そのため、「$C$5:C5」と始まりを絶対参照（$）にして終わりを相対参照にしています。

こちらの数式を先ほど同様にコピーすることで、連番を表示することができます。

この数式の場合は、COUNTA関数の参照範囲の終わりの位置を、数式を入力している行と同じにしているため、行を挿入した場合に関しても、連番がずれてしまうことはありません。

挿入した行にも連番を表示させるには、「No」の項目に入力されている数式をコピーするだけで表示することができます。
最初に解説した数式とは異なり、挿入した行の一つ下のセル（B12）までコピーする必要はありません。

以下のシフト表のように、「休」「早」「遅」といった文字を入力する際、毎回、直接入力するのは大変になります。
データの入力規則によりドロップダウンリストを活用した場合に関しても、１つ１つを選択して入力するのは大変です。

今回紹介する方法を使うことで、「1」を入力したら「休」、「2」を入力したら「早」を表示するなどと、１つのキーでシフト表に入力することができるようになります。

・数値に対して割り当てる

まずは、「1」には「休」などと、数値に対して特定の文字を割り当てる方法について解説していきます。

そのような場合は、条件付き書式を活用します。

初めに、「休」などといった文字を入力する可能性のある範囲を全選択し、[ホーム]タブより[条件付き書式]の[新しいルール]を選択します。

次に以下の画面にて、[指定の値を含むセルだけを書式設定]を選択し、「セルの値、次の値に等しい、1（直接入力）」となるように指定します。

次に書式を設定します。
[書式]を選択し、表示された画面の[表示形式]タブより[ユーザー定義]を選択して、テキストボックス内に「”休”」と入力します。

表示形式の設定が出来ましたら、[塗りつぶし]タブを選択し、好みの色を設定しておくと、「1」が入力されたセルの背景色を変更することができます。

これで確定し、条件付き書式の設定画面を閉じることで、「1」という数値に対して「休」という文字を割り当てることができます。

セルの背景色に関しても、[塗りつぶし]にて設定した色が反映されます。
ただ注意点として、この「休」という文字は見た目のみになります。
そのため、COUNTIF関数などで集計する際は、元の値の「1」に対して集計する必要があります。

他の「早」や「遅」に関しても、同様の手順で設定することができます。

・文字に対して割り当てる

先ほど紹介した方法では、「1」や「2」などといった数値に対してしか設定することができません。
シフト表などに入力する文字に、「休」「早」「遅」以外にも沢山のパターンが存在する場合は、数値ではなくアルファベットの頭文字に割り当てた方が、入力が楽になる可能性があります。

数値ではなく、文字に対して割り当てる場合は、条件付き書式の設定画面にて、「セルの値、次の値に等しい、a（aに対して割り当てる場合）」までは同じ手順になります。

[書式]の設定画面により、[塗りつぶし]を設定する方法に関しても同様なのですが、[表示形式]の設定のみ異なります。

先ほどは、「”休”」と入力したのに対し、文字に対して割り当てる場合は、「;;;”休”」と先頭に「;」を３つ加える必要があります。

以上の手順で設定することにより、文字に対して文字を割り当てることができます。

表示形式の意味について知りたい場合は、下記の記事が参考になります。

＞【1-04】セルの表示形式の基礎から応用

以下の「役割のドロップダウンリスト（プルダウン）」に関しては、一度選択して入力した値が、リストの選択肢に表示されなくなります。

また、未割当の値に関しては、「未割当」という項目にて確認することができます。
この仕組みの作成方法について、解説していきます。

・リストの作成

まずは、以下の表の「役割」の項目に、「未割当」の範囲を参照したリストを作成します。

リストを表示させる対象の範囲を選択し、[データ]タブの中の[データの入力規則]を選択します。

表示される以下の設定画面にて、[入力値の種類]を[リスト]にし、[元の値]に「未割当」の範囲を指定します。
[元の値]を設定する場合は、テキストボックス内を選択し、その状態で、「未割当」のセルD3からD7を直接選択することで、「=$D$3:$D$7」と入力することができます。

この内容で確定することで、「未割当」の項目の内容をリスト上に表示することができます。
「未割当」の項目に、仮の値を入力することで、反映内容の確認がしやすくなります。

「未割当」の項目には空白のセルを含むため、未入力のセルがある場合は、空白が表示されてしまいます。
こちらの内容は、後に対応していきます。

・数式で「未割当」を抽出

次に、数式を用いて、「未割当」の項目に表示する値を抽出していきます。
そのためには、まずは、リストの選択肢（役割）の一覧を用意する必要があります。
こちらでは、表の隣に一覧を用意しています。

どのように「未割当」を抽出するのかというと、用意した選択肢の一覧の内容と、リストを作成した「役割」の項目の内容を繋げてから、重複のない値のみを表示するという手順で抽出します。

イメージがしやすいように、一度、「役割」のリストの項目に、選択肢のいずれかを入力します。
直接入力してしまうと、前半で設定した[データの入力規則]の設定によっては、エラーになってしまいます。

そのため、選択肢の一覧の一部のセルを「Ctrl + C」などでコピーし、リストのセルに値のみを貼り付けて入力します。
値のみの貼り付けは、「Ctrl + Shift + V」で行えます。
万が一、このショートカットが使えない場合は、右クリックのメニューからでも、値のみを貼り付けることができます。

いくつか入力することができましたら、「役割」の項目の内容と、選択肢の一覧の内容を繋げた複数の値を、「未割当」の項目に数式で表示させます。

縦向きの表を縦方向に繋げるには、「VSTACK関数」を活用します。
この関数は、２０２２年の夏ごろに追加された関数になります。
Excelのバージョンによっては、活用できない可能性がございます。

VSTACK関数の使い方は、以下の通りです。

=VSTACK(表1, [表2], [表3], … )
// 指定した表を縦方向（垂直方向）に繋げた表を返す

実際に活用すると、以下のようになります。

=VSTACK(F3:F7,C3:C7)

数式は、セルD3にしか入力していませんが、スピルという機能により、自動で拡張されて表示されます。

ただ、この状態ですと、空白のセルが存在する場合は、「0」が表示されてしまいます。
空白のセルを「0」として表示したくない場合は、空白を含む範囲に関して、空白（””）を「&」で結合します。
今回の場合は、セルC3からC7に空白が存在する可能性があるため、その範囲に対して、「&””」を加えます。

=VSTACK(F3:F7,C3:C7&"")

次に、この抽出した値から、重複のない値のみを抽出する必要があります。
重複のない値のみを抽出するには、「UNIQUE関数」を活用します。
UNIQUE関数の使い方は、以下の通りです。

=UNIQUE(表, 行/列, 表示内容)
// 指定した表から重複を除いた表を返す
// [表]：対象の表
// [行/列]：重複を確認する方法を指定（TRUE：一意の列、FALSE：一意の行）
// [表示内容]：表示する値を指定（TRUE：１回だけ出現する値、FALSE：全ての値）

実際に、VSTACK関数と組み合わせると、以下のようになります。

=UNIQUE(VSTACK(F3:F7,C3:C7&""),FALSE,TRUE)
// 一意の行の対し、１回だけ出現する値を抽出

以上の設定にて、ほぼほぼ完成になります。

・リストから空白を除外する

この状態では、リストに未入力のセルがある場合は、以下のように、空白が表示されてしまいます。

この空白が表示されないようにする方法として、「未割当」の項目に入力した数式により、スピルによって拡張された範囲のみをリストに表示させていきます。

まずは、リストを設定した範囲を選択し、[データ]タブの中の[データの入力規則]を選択します。

表示された設定画面にて、[元の値]に入力した「=$D$3:$D$7」を「=$D$3#」に修正します。
「#」というのは、基準となるセル（D3）からスピルにより拡張された範囲を指定するというものです。

これで確定することにより、スピルで拡張された範囲のみをリストに表示することができます。
そのため、最後の１つの項目のみになった時に関しては、リストに空白が表示されてしまうのですが、それ以外では、空白が表示されなくなります。

最後の１つの項目に関しても、空白を表示させないためには、VSTACK関数で空白を繋げる必要があります。
空白を繋げることで、残り１つになった場合に関しても、空白が重複し、表示されなくなります。

空白を繋げた数式は、以下になります。

=UNIQUE(VSTACK(F3:F7,C3:C7&"",""),FALSE,TRUE)

このように修正することで、最後の１つの項目に関しても、空白が表示されなくなります。

また、最後に空白を繋げることによって、リストの値が埋まった場合に関しても、空白が１つ「未割当」の項目に残るようになります。

スピルにより抽出された値に対して、表示する値が１つもない場合は、エラーになってしまうのですが、空白を１つ繋げることで、空白が表示され、エラーにならなくなります。

そのため、リストの値が埋まった場合に関しては、リストに１つの空白のみが表示されます。

リストから選択した値を変更する際は、リストの値をDeleteキーなどで削除することで、リストの選択肢が再度表示されるようになり、リストから値を変更することができます。