よねさんのWordとExcelの小部屋｜Excel(エクセル)入門編：(成績処理)目次｜第10回個人票の作成

第10回　個人票の作成:Excel入門編

スポンサードリンク

第10回　個人票の作成

Excel(エクセル)のファイル『成績処理表.xls』を開いた状態から始めます。
第9回までのサンプルのダウンロードseisk-09.xlsx

(練習1)　シートの追加/名前の変更
(練習2)　別のシートのリストを使って入力する　（入力規則リスト）
(練習3)　別のシートからデータを取り出す　（VLOOKUP関数）
(練習4)　別のシートからデータを転記する　（数式を使う）
(練習5)　数字の横位置を揃える　（セルの表示形式）

(練習1)　シートの追加/名前の変更

成績表ができましたので、「おまけ-2」として、個人票を作成してみましょう。
Sheet1のデータを参照します。

【操作手順】

Sheetが足りませんので、新しいシートを追加します。
シート見出しの右にある[新しいシート]ボタンをクリックして、Sheet4を挿入します。
Sheet名を変更してわかりやすくしましょう。
Sheet1を選択して、[ホーム]タブのセルグループにある[書式]→[シート名の変更]を選択します。
(シート見出しをダブルクリックしても良い、または、右クリックして【名前の変更】を選択しても良い)
シート見出しの「Sheet1」が反転しますので、成績一覧表　と入力し、[Enter]キーを押して確定します。
同様に、Sheet3は判定表、Sheet4は個人票　とシート名を変更しましょう。
シートを移動してみましょう。
個人票を成績一覧表の右に移動しましょう。
シート「個人票」を選択して、[ホーム]タブの[書式]→[シートの移動またはコピー]を選択します。
シートの移動またはコピーの挿入先でSheet2を選択して、[OK]ボタンをクリックします。
シート「個人票」がSheet2のあった位置に移動できました。

(練習2)　別のシートのリストを使って入力する

下図のような個人票を作成します。
シート「個人票」に下図のように、各セルに文字列を入力して、完成させてください。
C4:D4 と C5:D5セルはセルを結合して中央揃えを実行しました。

[A]　リストを使って入力の手間を省く。

名前を定義します。
シート「成績一覧表」を表示します。
出席番号のセル範囲B5:B14を選択し、名前ボックスに『出席番号1組』と入力します。
(練習)　同様に成績表のデータ部分B5:J14には『成績表1組』と名前を付けましょう。

入力規則の設定

『個人票』シートを表示し、C4セルを選択します。
[データ]タブの[データの入力規則]を選択します。
データの入力規則ダイアログの、[設定]タブを選択します。
入力の種類で「リスト」を選択します。
元の値に　=出席番号1組　とキーボードから入力します。
- 元の値に名前の貼り付けをするときは [F3]キーを押して、リストから選択する方法もあります。
C4セルでリストからの入力が可能になりました。試しに『1001』を選択してみましょう。
C4セルに『1001』と入力されます。

(練習3)　別のシートからデータを取り出す

名前はC4セルに入力された出席番号をもとに、成績一覧表から取り出したいと思います。
使用する関数は　VLOOKUP関数を使いたいと思います。
名前を表示するセルC5を選択します。
数式バーに　=vl と入力すると、vlを含む関数のリストに VLOOKUP関数が表示されました。
[Tab]キーを押して入力します。
数式バーに　=VLOOKUP( と入力されました。
関数の引数は表示されたヒントをもとに入力します。
=VLOOKUP(C4,成績表1組,2,FALSE)　と入力しました。
データ表には「成績表1組」と名前を定義していましたので、この名前を使っています。数式に名前を貼り付けたいときは[F3]キーを押してリストから選択します。
引数の列番号は取り出したい名前は「成績表1組」の2列目なので、2とします。
検索方法は出席番号が昇順に並んでいる場合は省略してもよいのですが、成績順に並べ替えたりすると出席番号がランダムになるので完全一致で検索するように FALSE としました。
出席番号に一致した名前が抽出できました。

ちなみに、XLOOKUP関数を使うと、=XLOOKUP(C4,成績一覧表!B5:B14,成績一覧表!C5:C14,"データなし")　となります。

国語～評価を別シートから持ってくる

国語、算数・・・などのデータを抽出するには、Excel for Microsoft365で使用できる FILTER関数を使うことにします。
B7セルを選択して、=fil　と入力すると、リストに FILTER が選択されました。[Tab]キーを押してFILTERを入力します。
=FILTER( と入力されます。
引数の配列はシート成績一覧表のD5:J14 なので、シート成績一覧表のD5:J14をマウスでドラッグすると、数式バーに　=FILTER(成績一覧表!D5:J14　と入力されるので、後ろに　,(カンマ)　を入力します。
次の引数「含む」には検索して一致する条件を入れるので、シート成績一覧表のC5:C14 をドラッグして入力します。
数式バーには　=FILTER(成績一覧表!D5:J14,成績一覧表!C5:C14　と入力されるので、= を追加します。
シート個人票を選択して、C5セルを選択します。
数式バーには　=FILTER(成績一覧表!D5:J14,成績一覧表!C5:C14=個人票!C5　と入力されました。
引数の空の場合を入力します。
,(カンマ)を入力して　”データなし”)　と入力しました。
数式バーには　=FILTER(成績一覧表!D5:J14,成績一覧表!C5:C14=個人票!C5,"データなし")　と入力されました。
[Enter]キーで数式の入力を確定します。
出席番号「1001」を選択して、名前やテストの成績を抽出して表示することができました。
C4セルをクリックして、プルダウンリストから出席番号を選択すると、各人の成績が表示されます。
下図は「1007」を選択した例です。

ちなみに、VLOOKUP関数を使って求めると、
B7セルに　=VLOOKUP($C$4,成績表1組,COLUMN(C1),FALSE)　と入力して、右方向へ数式をコピーします。
VLOOKUP関数の引数の列番号は　3,4,5・・・　と変化するように COLUMN(C1) とします。

(練習4)　別のシートからデータを転記する

　個人票シートの「得点」の下の行に平均点を表示したいと思います。
ここでは、セルの位置を参照する方法でデータを読み込みます。
セルの位置が変動しないことが前提条件になります。

【操作手順】

『個人票』シートのA7セルに『得点』、A8セルに『平均点』と入力します。
B8セルを選択し、『=』を入力します。
成績一覧シートの国語の平均値のセル　D15 をクリックします。
数式バーに　=成績一覧表!D15　と表示されます。[Ctrl]+[Enter]キーを押して入力を確定します。
画面は『個人票』シートに戻り、B8セルに国語の平均点『58』が表示されます。
B8セルのフィルハンドルをドラッグして、F8セルまで数式をコピーします。

(練習5)　数字の横位置を揃える

得点と平均点がずれて表示されていますので、揃えたいと思います。

表示形式で揃える方法

表示形式でアンダーバーを使用して、小数点や数字と同じ幅のスペースを後ろに追加します。

平均点の表示形式を設定します

小数点以下一桁で表示し、半角スペースを後ろに追加するように B8:F8の表示形式を「0.0_ 」と設定します。
B8:F8セルを選択して、[ホーム]タブの数値グループにあるダイアログボックス起動ツールをクリックします。
ショートカットキーは　[Ctrl]+[1] です。
[表示形式]タブを選択し、『分類』で『ユーザー定義』を選択します。
種類に　「0.0 」　「0.0」の後ろに「」(半角スペース)を入力します。
- 小数点以下一桁で表示し、半角スペースを後ろに追加する表示形式です。
「OK」ボタンをクリックします

得点の表示形式を設定します

平均点の表示に合わせるため、得点の表示形式を設定します。
セル範囲 B7:F7を選択し、[ホーム]タブの数値グループにあるダイアログボックス起動ツールをクリックします。
ショートカットキーは　[Ctrl]+[1] です。
[表示形式]タブを選択し、「分類」で「ユーザー定義」を選択します。
種類に　「0_._0 」　「0」(ゼロ)、「_」(アンダーバー)、「.」(ドット)、「_」(アンダーバー)、「0」(ゼロ)、「」(半角スペース)と入力します]
- 「_.」(アンダーバー、ドット)で小数点と同じ幅のスペース、「_0」(アンダーバー、ゼロ)で数字と同じ幅のスペースを追加しています。
  そして、最後に「」(半角スペース)を追加しています。
[OK]ボタンをクリックします。
数字の表示位置が揃いました。
- ついでに、G7:H7セルを選択して、[ホーム]タブの「中央揃え]ボタンでセンタリングしておきましょう。