作業しやすい Excel 画面 の 作り方。

〇 作業しやすいExcel画面の作り方、データを追加していく表を設計する。

Excelでの作業を効率化するには、分かりやすくて入力しやすい画面の設計が重要になる。今回は、基本的な表形式でデータを追加しやすい画面を作ろう。

Excelの作業では、あらかじめテンプレート的なブックを作成し、データ部分以外を何度も繰り返して利用することが多い。作成者だけでなく、グループのほかのメンバーがそのブックを使って作業するという運用もよくある。

本講座では、ワークシートでの作業内容を理解しやすく、入力や編集を効率化できる画面を設計する基本と、各種のテクニックを紹介していく。

※ Excel 2016以降のバージョンについて解説しています。画面はMicrosoft 365 PersonalのExcelです。

表示形式は列ごとに設定。

まず、一般的な表形式の例として、料理店の電話予約を記録する表を作成しよう（図1）。あらかじめ、シートのタイトルと列見出しを入力し、各列の幅を調整。表の範囲の罫線と見出し行の背景色を設定した。このように表形式でデータを蓄積していく場合、1行目を各列の見出しとし、2行目以降は1行に1件分のデータを入力する「リスト」形式が基本だ。

Θ データに応じた表示形式を設定。

図1　今回作成するのは、料理店の電話予約を記録する表。見出しなどを入力して列幅を調整し、基本的な書式を設定した状態から、作業を開始する。

このような表では、各列にそれぞれ同じ種類のデータを入力していくのが基本。あらかじめ列ごとに「表示形式」を設定しておくと、入力時の手間が省けるだけでなく、そのセルに入力するデータの種類を判断するヒントにもなる。

まず、「日付」列に対して、「ホーム」タブの「数値の書式」から「短い日付形式」を設定する（図2）。さらに、「人数」列に「数値」、「来店日」列に「短い日付形式」、「単価」列と「料理価格」列には「通貨」の表示形式を設定しておく（図3）。

図2、列ごとに「表示形式」を設定しておこう。「日付」列のA4～A8セルを選択し、「ホーム」タブの「数値の書式」をクリックして、「短い日付形式」を選ぶ。

 

図3、同様にして、「人数」列のD4～D8セルには「数値」、「来店日」列のE4～E8セルにも「短い日付形式」、「単価」列と「料理価格」列のG4～H8セルには「通貨」の表示形式を設定する。

表示形式が「標準」のままのセルなら、「2023/10/1」と入力すれば、日付データとして、入力した通りの表示形式が設定される。一方、「10/1」と入力すると、作業時点の西暦年の日付データになるが、表示形式は「10月1日」になる。しかし、あらかじめ「短い日付形式」に変更しておけば、「10/1」と入力しても、年も含めて「2023/10/1」と表示される（図4）。また、ここではA4セルにデータを入力後、［Enter］キーではなく［Tab］キーを押した。［Enter］キーではアクティブセルが1つ下に移るが、［Tab］キーの場合は1つ右のセルに移動する。行単位でデータを入力するときに便利だ。

図4、「短い日付形式」を設定したことで、「10/1」のように入力すると（左）、作業時点の西暦を含む「2023/10/1」という形式で表示される（右）。入力確定時に［Tab］キーを押すと、アクティブセルが下ではなく右のセルに移る。

以下、同じ行の「受付」「お客様氏名」「人数」「来店日」の各セルにデータを入力していく（図5）。「来店日」列のE4セルには、やはり「10/13」のように日付を入力すればよい。

図5、同じ行の「受付」「お客様氏名」「人数」「来店日」の各セルにもデータを入力する。いずれも入力後、［Tab］キーで右のセルへ移動していく。

選択肢からコースを入力。

予約記録の表の「コース」列では、複数のコースを選択肢として表示し、1つを選んで入力できるように設定しよう。

ここでは5種類のコースがあり、同じグループの客は全員が同じコースを選択するものとする。各コースの詳細は、この表の右側に作成した「コース価格表」にまとめた（図6）。5つのコース名と料理内容、価格を入力してある。

Θ リストから選択する設定にする。

図6、「コース価格表」として、「A」～「E」というコースごとの料理内容と価格の一覧を、同じシートの右側に作成する。J1セルに表のタイトルを入力し、J3～L8セルに表を作成して、書式を設定する。

選択肢を表示させるには、予約記録の表で対象のセル範囲を選択し、「データの入力規則」を実行する（図7）。表示される画面では「入力値の種類」で「リスト」を選び、「元の値」としてコース価格表の「コース」列の参照を指定する（図8）。

図7、予約記録の表の「コース」列には、コース価格表に入力したコース名をドロップダウンリストから選択して入力できるようにする。F4～F8セルを選択し、「データ」タブの「データの入力規則」をクリックする。

 

図8、「データの入力規則」画面の「入力値の種類」で「リスト」を選択。「元の値」欄にカーソルを置き、J4～J8セルをドラッグしてそのセル参照を入力。「OK」をクリックする。

以降は、予約記録の表の「コース」列でいずれかのセルを選択すると、右側に「▼」が表示される（図9）。クリックするとドロップダウンリストが開き、コース価格表の「コース」列の値を選べる。

図9、改めてF4セルを選択すると、右側に「▼」が表示される（左）。ここをクリックすると、J4～J8セルに入力された「A」～「E」がドロップダウンリストで表示される。その中の1つをクリックで選んで、F4セルに入力できる（右）。

なお、ここではコース名をシンプルに「A」～「E」としたが、料理内容に即したコース名に変えてもよい。コース価格表の「コース」列でデータを書き換えると、リストに表示される選択肢も変化する。

予約記録の表の「単価」列には数式を入力する。入力済みのコース名に基づいて、コース価格表から該当する価格を取り出すという内容の数式だ（図10）。コース名の検索と価格の取り出しにはVLOOKUP（ブイルックアップ）関数を使う。引数「検索値」では左隣のコース名のセルを参照し、引数「範囲」ではコース価格表のデータ行全体を参照する。引数「範囲」の参照はコピーしても変化しないように、「＄」の付いた絶対参照にする。さらに、引数「列番号」には「3」を、引数「検索方法」には完全一致で検索する「FALSE」を指定する。ただし、この関数だけでは、左のセルが未入力の場合はエラー値になってしまうため、IFERROR（イフエラー）関数と組み合わせて、空白（""）が表示されるようにした。入力した数式をオートフィルなどで同じ列のほかのセルにコピーする。

Θ 単価と料理価格を数式で表示する。

図10、「単価」列の数式では、VLOOKUP関数を使う。左隣のセルの値に基づいてコース価格表から価格を取り出し、コースが未入力の場合はIFERROR関数で空白を表示する。この数式をG4セルに入力し、G8セルまでコピーする。

「料理価格」列にも数式を入力する。同じ行の「人数」列と「単価」列の数値を乗算するという内容だ（図11）。「単価」列のセルの値が空白（""）の場合にエラー値が表示されないように、IFERROR関数を組み合わせた。

図11、「料理価格」列には、同じ行の「人数」列の数値と「単価」列の価格の積を表示する。やはりIFERROR関数を使い、単価が空白の場合は空白を表示する。この数式をH4セルに入力し、H8セルまでコピーする。

「単価」列と「料理価格」列は、数式が入力されていることが分かるように目印を付けておこう。それだけでも、数式が間違えて消去・変更されるトラブルを、ある程度は防げる。ここでは、対象セルに薄い背景色を設定した（図12）。

図12、数式のセルは、背景色を設定して、データのセルと区別できるようにしておこう。G4～H8セルを選択し、「ホーム」タブの「塗りつぶしの色」の「▼」をクリックして、「白、背景1、黒＋基本色5％」を選ぶ。

以上で、通常の表データとしての画面設計は完了だ。なお、ここではサンプルとして、予約記録の表に5行分のデータを入力しておく（図13）。

図13、同様に、A5～F8セルにもデータを入力していく。ここまでで、最初に設定した表の行数は埋まった。

蓄積型の表はテーブルに変換。

このように作った表で、用意した行数より多くのデータを入力したい場合は、まず下方向に表をコピーする。さらに、コピー先で不要なデータ部分を消去して、改めて入力するといった手間が掛かる。表の下の行に追加データを直接入力することで、一部の書式や数式は自動でコピーされることもあるが、不ぞろいになる場合が多い（図14）。データの入力規則の設定は、自動ではコピーされない。

Θ 表をテーブルに変換する。

図14、書式をコピーしないまま、A9セルに「10/4」と入力すると（左）、「標準」の表示形式に入力した結果として、「10月4日」と表示される（右）。

日々の作業で繰り返しデータを追加していく表は、「テーブル」として作成しておくのが便利だ。リスト形式の表をテーブルに変換することで、データの入力や集計処理などに便利な各種の機能が自動で設定される。各種の「テーブルスタイル」が用意されており、そこから選ぶだけで、さまざまなデザインを簡単に適用できるのもメリットだ。

ここでは、予約記録の表とコース価格表を、それぞれテーブルに変換しよう。ただし、罫線や背景色はテーブルスタイルで自動設定されるので、事前にそれぞれの書式を消去しておく。その上で、予約記録の表の中のセルを選択して、「テーブルとして書式設定」から、表に適用したいテーブルスタイルを選ぶ（図15）。次の画面で「OK」をクリックすると（図16）、選択したセルを含む表全体が、テーブルに変換される（図17）。新しいテーブルには「テーブル1」などの名前が自動で設定されるが、「予約」など短くて分かりやすい名前にしておくとよい。さらに、コース価格表も、別のスタイルのテーブルに変換し、テーブル名を「価格表」に変更しておこう（図18）。

図15、2つの表の罫線と背景色の書式をいったんクリアする。その上で、予約記録の表から1つのセルを選択し、「ホーム」タブの「テーブルとして書式設定」で任意のテーブルスタイルを選ぶ。

 

図16、「テーブルの作成」画面で、テーブルに変換するデータ範囲としてA3～H8セルの参照が自動指定されていることと、「先頭行をテーブルの見出しとして使用する」にチェックが付いていることを確認。「OK」をクリックする。

 

図17、対象範囲がテーブルに変換される。テーブルの中のセルが選択されている状態で、「テーブルデザイン」タブの「テーブル名」欄を「予約」に変更する。

 

図18、同様に、右側のコース価格表もテーブルに変換。「テーブルデザイン」タブの「テーブル名」欄を「価格表」に変更する。

テーブル内の特定のセルを数式で参照する場合、テーブル名や列名を使った「構造化参照」が使える。慣れれば通常のセル参照よりも分かりやすい。ここではまず、「予約」テーブルの「単価」列の数式を、この参照方法に変更した（図19）。また、コースが未選択の場合、空白ではなく0を表示するようにした。

図19、G4セルの数式のF4セルの参照を「［＠コース］」に、J4～L8セルの参照を「価格表」に変更。エラーの場合の値は「""」から「0」に変更した。テーブルで数式を変更すると、列全体に自動で反映される。

「価格表」テーブルで見出し行を除くデータ行全体は、テーブル名の「価格表」で指定できる。また、「予約」テーブル内で、数式セルと同じ行にある「コース」列のセルは「［＠コース］」と指定できる。

構造化参照の場合も、通常のセル参照と同様、対象のセルをクリックまたはドラッグすることで自動入力できる。また、同じ数式が入力されている列で、1つのセルの数式を変更すると、その数式が列全体に自動でコピーされる。

同様に、「料理価格」列の数式も、同じ行の「人数」列と「単価」列のセルの参照を、それぞれ「［＠人数］」と「［＠単価］」に変更（図20）。また、今回は、「単価」列は金額または0のいずれかなので、IFERROR関数を使う必要はない。

図20、H4セルの数式では、D4セルの参照を「［＠人数］」に、G4セルの参照を「［＠単価］」に変更する。単価はコース未選択時でも0なので、エラー対応は必要ない。やはり、自動で列全体に反映される。

最初に作成した予約記録の表では、数式セルに背景色を設定した。しかし、テーブルでは背景色を使うスタイルが多いので、空白ではなく0を表示させ、文字色で数式セルだと分かるようにしよう。対象列のセル範囲を選び、「フォントの色」で「紫」を設定した（図21）。

図21、数式セルは、コース未選択時にも空白ではなく0を表示させ、文字色でデータのセルと区別できるようにしよう。G4～H8セルを選択し、「ホーム」タブの「フォントの色」の「▼」から「紫」を選ぶ。

テーブルの場合、最下行の下にデータを入力すると、自動でテーブルの範囲がその行全体まで拡張される（図22）。各列に設定された書式や数式なども全てコピーされる。

図22、最下行の下のA9セルにデータを入力した（左）。［Tab］キーなどで入力を確定すると、その行までテーブルの範囲が拡張される（中、右）。同じ行の各セルには、上のセルと同じ書式が適用される。「データの入力規則」の設定や数式も自動でコピーされる。

ただし、「データの入力規則」を設定した「コース」列の最下行の下のセルを、最初に選択するのは避けよう。テーブルの範囲が拡張される前の段階では「データの入力規則」はコピーされず、リストの「▼」も表示されない。先にほかの列のセルに入力すれば、この設定も有効になる。