プロジェクト

全般

プロフィール

Wiki » Officeツール »

Excel使いこなし（表管理）

業務上で様々な事項を表管理することがよくあります。処置済みかどうかを管理する、ToDo管理をする、課題管理をする、などです。
この手の表の作成・編集において、特に体裁を整えるため、罫線を設定する、行を塗りつぶす、項番号を書く、挿入や削除があったら番号を付け直す、などの手間がかかることがよくあります。
この手間を減らせるといいですね。

手間を削減する

セルの入力をリスト選択にしたい（データの入力規則）

  • 1. 対象セルを選択し、[データ]リボン（①） > [データの入力規則]（②）で「データの入力規則」ダイアログ表示
    [設定]タブ（③） で入力値の種類を[リスト]（④）、元の値に、選択肢をカンマ区切りで記載（⑤）

cell_list-1.png

すると、セルの右横にドロップダウンボタンが表示され、これを押すと、⑤で記載した選択肢が表示されます。

cell_list-2.png

  • 2. 1.のセルをコピー、リスト選択したいセルを範囲選択し、右クリックから[形式を選択して貼り付け] > [形式を選択して貼り付け] を選択、「形式を選択して貼り付け」ダイアログの貼り付けで[入力規則]を選択し[OK]

選択肢を修正したいときは、セルを1つ選び、1.で選択肢を修正し、[同じ入力規則が設定されたすべてのセルに変更を適用する]にチェックを付けて[OK]

デフォルトでは選択肢にないデータを入力するとエラーメッセージが表示されます。選択肢以外のデータの入力を許す場合は、「データの入力規則」ダイアログの[エラーメッセージ]タブを選択し、[無効なデータが入力されたらエラーメッセージを表示する]のチェックを外します。

cell_list-3.png

特定のセルの値に応じて行を塗りつぶし（条件付き書式）

処置済みの項目はグレーアウトするなどが典型的な用途です。状態欄のセルに完了と記入し、その行の表範囲を手動で選択し、塗りつぶし色を設定するのは手間ですね。
条件付き書式で楽をします。

  • 1. 塗りつぶし対象範囲を選択します（表全体を選択する場合、表内の任意のセルで、Ctrl + Shift + * とキー操作）。
  • 2. [ホーム]リボン の[条件付き書式] > [新しいルール]で「新しいルール」ダイアログを表示します

background-1.png

  • ルールの種類で、[数式を使用して書式設定するセルを決定]を選択し、[次の数式を満たす場合に値を書式設定]に、塗りつぶし条件の式を記載、
    塗りつぶし対象範囲の先頭行における条件判定セルと条件を指定します
    例（塗りつぶし対象範囲の先頭行が3行目の場合）
    • B列3行目の値が1万を超える場合、=$B3 > 10000
    • C列3行目の値が"終了"、"中止"の場合、=OR($C3="終了",$C3="中止")
    • D列3行目の値に"要確認"が含まれている場合、=COUNTIF($D3, "*要確認*")
  • [書式]ボタンを押して、[塗りつぶし]タブを選択、適宜塗りつぶし設定をします

特定の列にデータが入力されているときだけ罫線を引く（条件付き書式）

表管理をしていると、表に行を挿入することがしばしばあります。そのたびに罫線を手動で引くのはとても面倒です。

  • 1. 罫線を引く対象範囲を選択します（表全体を選択する場合、表内の任意のセルで、Ctrl + Shift + * とキー操作）。
  • 2. [ホーム]リボン の[条件付き書式] > [新しいルール]で「新しいルール」ダイアログを表示します
  • ルールの種類で、[数式を使用して書式設定するセルを決定]を選択し、[次の数式を満たす場合に値を書式設定]に、塗りつぶし条件の式を記載、
    罫線対象範囲の先頭行における条件判定セルと条件を指定します
  • [書式]ボタンを押して、[罫線]タブを選択し、罫線を設定
    → 条件付き書式で引ける罫線の種類は、極めて少なくなっています。（二重線や太線が選択できない）

結合セルの値を参照し、結合セルの値と連番からなる番号を自動生成する

よくある大分類、小分類で、大分類に結合セルを用い、小分類の各行には、「大分類番号ー小分類番号」の番号を記述します。例を次に示します。

largeSmall-1.png

ここで、1-1と1-2の間に小分類項目を追加すると、1-2と1-3をそれぞれ1-3と1-4に番号振り直しをする必要が生じます。
そこで、小項目番号を自動生成します。

小項目番号は、2つの数値をハイフンで結ぶ書式とし、ハイフンの左側は大項目番号、右側はその大項目の中での連番とします。

C3セルに次の関数式を記入します。

=VLOOKUP(9999, $A$3:A3, 1, TRUE) & "-" & ROW()-MATCH(9999, $A$2:A2, 1)

C3の数式を、C4～にコピーします。

それぞれの説明を次に示します。

大項目番号を結合セルから取得

結合セルの値は、結合セルの一番上の行のセルに値が入っており、それ以外の行のセルは値が入っていません。
そのため、小項目の1番上の行であれば同じ行の列から値を取得できますが、2番目以降の行では単純に番号を取ってくることができません。
そこで、VLOOKUP関数を使って値を検索して持ってくることとします。次はC4セルで参照するときの検索式です。

VLOOKUP(9999, $A$3:A4, 1, TRUE)
　　　　↑　　　↑　　↑　↑ 
検索値ー＋　　　｜　　｜　＋－ 検索の型（TRUE又は省略時は検索値未満の最大値を検索）
　検索範囲－－－＋　　＋－－ 列番号（範囲内で検索対象データが含まれている列を左からの列数で示す）
  • 検索範囲を、絶対位置のA3セルから、その行のA列のセルA4までとします。9999を検索値とし、検索タイプは検索値未満の最大値とします
    例の場合は、A3とA4の範囲での最大値は 1 となるので、VLOOKUPの結果は 1 となります。

小項目番号の連番を生成

小項目番号は、大項目番号が変わるごとに1から連番として生成します。
そこで、その行の番号と大項目番号の最後の行番号との差から連番を生成します。

このセルの行番号
　↓
ROW()-MATCH(9999, $A$2:A4, 1)
　　　　　　↑　　　↑　　 ↑
　　　　　　｜　　　｜　　 ＋－ 検索の型（1又は省略時は検索値以下の最大値を検索）
　　検索値ー＋　　　＋－－ 検索範囲
  • ROWはその行の番号を返すので、ここでは 4 が結果となります
  • MATCHは、検索範囲を、絶対位置のA2セルから、その行のA列のセルA4までとします。9999を検索値とし、検索タイプは検索値未満の最大値とします
    例の場合は、9999以下の最も大きな番号のあるセルの行を、A2を1として相対行数で算出します。ここでは A3に検索結果の値があるので 2 が結果となります。
    列は大項目番号のある列に応じて変更しますが、行の2は固定です（ROW関数の結果と合わせるため）。
  • 2つの関数の結果の引き算で 小項目番号の 2 が生成されます。

行間の罫線を分類によって種類を変えて引く

先ほどの大項目ー小項目の表管理において、行間の罫線を、大項目の区切りでは実線、小項目の区切りでは点線としたいとします。

largeSmallLines-1.png

  • 大項目の区切り線
    条件付き書式の[数式を使用して書式設定するセルを決定]で、次の数式を指定、書式の罫線はセルの上に実線
    =$A3<>""
  • 小項目の区切り線
    条件付き書式の[数式を使用して書式設定するセルを決定]で、次の数式を指定、書式の罫線はセルの上に点線
    =$A3=""
  • 表の外枠囲い線
    条件付き書式の[数式を使用して書式設定するセルを決定]で、次の数式を指定、書式の罫線はセルの上に実線
    =AND(OR($A2:$E2<>""), AND($A3:$E3=""))

セルの上に罫線を書く理由

条件付き書式で罫線を自動で引かせる際、結合セルについては、セルの下側に罫線を引くとなぜか結合セルの部分が表示されませんでした。
そこで、結合セルの次の行のセルの上側に罫線を引くようにしています。

6年以上前に更新