Excelの基礎知識や機能のメモ

改めてExcelについて勉強したのでよく使いそうな知識や機能をメモ。

3つの編集モードを使い分ける

Excelには、「標準」「ページレイアウト」「改ページプレビュー」の3つの編集モードがある。これらの編集モードは画面の右下(ズームバーの左側)にある3つのアイコンをクリックすることで行き来することができる。

標準

基本的にExcelで何かを作るときはこのモードで行う。

ページレイアウト

印刷イメージを確認しながら編集ができるモード。ヘッダーとフッターの追加はこのモードから行う。
ヘッダーとフッターにはタイトルやページ番号を設定することができる。

改ページプレビュー

印刷範囲を設定することができるモード。印刷したくない行や列を印刷範囲から除外したり、2ページに渡って印刷されてしまうものを1ページに収めて印刷するような設定をすることができる。

ページ設定

上記で触れた「ページレイアウト」や「改ページプレビュー」だけでは思ったような印刷結果が得られない場合がある。そんなときはページレイアウトタブ(ページレイアウトモードではなく、Excel上部にあるページレイアウトのタブ)をクリックし、ページ設定グループから任意の設定を行うことができる。
例えば、ページの余白、印刷の向き(縦・横)、ページのサイズ(A4,B5...etc)、印刷タイトル(全てのページに印刷したいタイトルの設定)などを行うことができる。

数式を入力してオートフィル

数式を入力してからオートフィルを使用することで、数式中のセルの行列番号を連番で変更しながら入力することができる。

例えば、以下のような行と列があり、「総合得点」の列を「前期得点」+「後期得点」で埋めたい。

A B C D
1 ユーザ名 前期得点 後期得点 総合得点
2 ネギ丸 90 60
3 Iyoten 50 70
4 アスタ 10 15

この場合、D2のセルに以下の数式を書いてからEnterで確定すると90と60を合計した150という計算結果がD2のセルに挿入される。

=B2+C2

D2のセルにカーソルが乗った状態でセルの右下の四角い形状のフィルハンドルをダブルクリックすると「オートフィル」が行われ、D3とD4の列にも120(50+70)と25(10+15)という計算結果が挿入される。

ここで注目したいのはオートフィルを使用すると合計結果を表示したいセル(総合得点)と計算対象のセル(前期得点、後期得点)の行番号が一致するように1つずつ下にずらしながら良きに計らってくれることである。

相対参照と絶対参照

オートフィルと合わせて覚えておきたいのが相対参照と絶対参照。

オートフィルを使用すると結果を表示したいセルと計算対象のセルの行番号が一致するように良きに計らってくれる。これはセルを相対的に参照している状態なので、相対参照と呼ぶ。しかし、この相対参照が邪魔になることがある。

オートフィルを使用してもセルの行番号を変更したくないときには絶対参照を使用する。

例えば、以下のような行と列があり、税込価格の列に価格に消費税率をかけた数を表示したい。
今回はD1セルの消費税率を使用する。

A B C D
1 1.08
2 商品名 価格 税込価格
3 にんじん 100
4 たまねぎ 200
5 じゃがいも 300

この場合、C3のセルに以下の数式を書くことで正しく計算を行うことができる。

=B3*$D$1

今回のケースにおいて、絶対参照を使用せずにオートフィルを行ってしまうと、D1→D2→D3...のようにどんどん参照するセルが下にずれていってしまい、計算ができなくなる。今回新しく出てきた$記号をセルの行番号と列番号の頭に付けることで、オートフィルを行ってもセルの参照が下にずれていくことが無くなる。この$でセルを指定することを絶対参照と呼ぶ。

セルの書式設定(ユーザー定義)

セルの書式設定を使用すると一定の規則に従ってセルの表示を変更することができる。
例えば「382」という数値の見た目を「見積No.000382」に変更したい場合、セルを右クリック→セルの書式設定→表示形式タブ→ユーザー定義から種類の入力値を"見積No."000000と入力することで意図した表示にすることができる。

セルの書式設定で使用することができる記号と意味についてテーブルにまとめておく。

記号 意味
"" 任意の文字列を入力するときは""で囲む
@ もともとのセルの入力文字列を表す
0 一桁の数値を表す
yyyy 数値を西暦と認識して表示する
ggge 数値を元号年と認識して表示する
m 数値を月を認識して表示する
d 数値を日と認識して表示する
h 数値を時間(時)と認識して表示する
mm 数値を時間(分)と認識して表示する
ss 数値を時間(秒)と認識して表示する

記述例

@"御中"

→ 株式会社〇〇御中 など

yyyy"年"m"月"d"日"

→ 2018年8月2日 など

ggge"年"

→ 平成30年 など

h"時"mm"分"ss"秒"

→ 13:45:00 など

並べ替えとフィルター

「並べ替え」と「フィルター」を使用することで表に表示するデータの順序や種類を変更することができる。

並べ替え

並べ替え機能を使用することで単一条件や複数条件からデータの並び替えを行うことができる。
データタブから「並び替え」をクリックする。並べ替えのダイアログが表示されるので、「最優先されるキー」「並べ替えのキー」「順序」を任意に設定することで並べ替えを行うことができる。
また、複数条件で並べ替えを行いたい場合は「レベルの追加」をクリックすることで、2つ目のキー(第2条件)の入力フィールドが生成される。

フィルター

フィルターを使用することで条件に合うセルだけを表示することができる。
データタブから「フィルター」をクリックする。表の見出し行の下向きの三角形をクリックするとフィルター編集画面が現れるので、チェックボックス群から表示から除外したい項目のチェックを外す。
また、細かい条件でフィルターを設定したい場合は「テキストフィルター」から条件を選択すると、「オートフィルター オプション」のウィンドウが表示される。

テーブル化による簡易的な並び替えとフィルター

テーブル化の機能を使うことで簡易的な並べ替えとフィルターを適用することができる。テーブル化したい表にカーソルを乗せながら挿入タブの「テーブル」をクリックする。テーブルの作成ダイアログが表示されるのでOKをクリックする。

表の見出し行の下向きの三角形をクリックすると並べ替えやフィルターの設定を行うことができる。
また、デザインタブから網掛け(縞模様)の設定など、テーブルのデザインに関する設定を行うことができる。

テーブル化したセルを元に戻したい場合は、デザインタブの「範囲に変換」をクリックする。

見出し行や列を固定する

固定したい行(列)の1つ下の行(列)を1行(列)分選択した状態で、表示タブのウィンドウ枠の固定をクリックし、さらにウィンドウ枠の固定をクリックする。
固定化した行(列)を解除したい場合はウィンドウ枠の固定をクリックし、さらにウィンドウ枠固定の解除をクリックする。

セルをロックして編集不可にする

何かの申請書類の雛形を作る場合など、編集可能なセルと不可能なセルに分けたい場合があるが、そのようなときにはシートの保護の機能が役に立つ。

まずは編集可能にしたいセルにカーソルを合わせ、ホームタブの書式からセルのロックを選択する。続いて、同メニュー内にあるシートの保護をクリックする。

これで先程の操作でセルのロックを外したセルのみが編集可能になり、その他のセルは編集不可能になった。

参考資料
書籍 FOM出版 Excel2016 基礎・応用