書籍「たった1日で即戦力になるExcelの教科書」のまとめ。
点数
93点
感想
良書。説明が丁寧でわかりやすい。
大半のページに大事なことが書いてある。
この手の技術書としては珍しく、無駄なページがほとんどない。
Excelで複雑な表を作成する際には手元に置いておきたい一冊。
Ctrl+1、Ctrl+D、Ctrl+Rのショートカットはかなり便利。
ただ関数を知っているだけでは意味がなく、関数をどう組み合わせて使うかという実践的なことが書かれている。
タイトルにある「たった1日で」は嘘だと思う。
基本操作
重要ショートカット
- Ctrl+1：セルの書式設定
- Ctrl+D：上のセルを複写
- Ctrl+R：左のセルを複写
- Ctrl+Shift+プラス(+)：セル、行、列を挿入
- Ctrl+マイナス(-)：セル、行、列を削除
- Ctrlを押しながらシートの見出しタブをドラッグ：シートコピー
知ってると便利なショートカット
- Ctrl+H：置換
- Ctrl+F11：シートを追加
- Ctrl+F2：印刷プレビュー(Excel2007以降)
- Alt+Shift+=：オートSUM
- Ctrl+;(セミコロン)：今日の日付
- Ctrl+:(コロン)：現在時刻
オートコレクト(おせっかいな自動変換)の無効化
ファイル⇒オプション⇒Excelのオプション⇒文章校正⇒オートコレクトのオプション
- 先頭文字が大文字になってしまう
「オートコレクト」タブ⇒「文の先頭文字を大文字にする」 - アドレスにハイパーリンクが設定されてしまう
「入力フォーマット」タブ⇒「アドレスをハイパーリンクに変更する」
保存するときは、xlsx形式ではなくxls形式で保存する
xlsx形式だと2つのデメリットがある。
- マクロを保存することができない
- 2003以前のバージョンで開けない
65536行を超えるデータでなければ、xls形式で保存しましょう。
新規ブックのシート数を1にする
ファイル⇒Excelのオプション⇒基本設定⇒新しいブック作成時⇒ブックのシート数
6大関数
- IF関数
- SUM関数
- COUNTA関数
- SUMIF関数
- COUNTIF関数
- VLOOKUP関数
かけ算
PRODUCT(A1:E1) A1*B1*C1*D1*E1と入力するよりもラク！
文字列連結
CONCATENATE(A1,B1,C1,D1,E1)
セル選択で入力できるので、A1&B1&C1&D1&E1と入力するよりもラク！
SUMIF関数
SUMIF(検索範囲,条件,合計したい範囲)
例）=SUMIF(A:A,G2,D:D)
A列にあるG2の値と等しいセル、そのセルと同じ行のD列の値の合計値
COUNTIF関数
COUNTIF(検索範囲,検索値)
例）=COUNTIF(B:B,D1)
B列にあるD1の値と等しいセルの数
COUNTIF関数の第2引数ではワイルドカード*が使える。
IF関数では使えない。
例）=COUNTIF(A2,"*世田谷区*") はうまくいく。=IF(A2="*世田谷区*", "○","×")はうまくいかない。
VLOOKUP関数
VLOOKUP(検索値,マスタの範囲,返す列,0)
例）=VLOOKUP(A2,F:G,2,0)
A2のセルの値をF列からG列までの範囲の一番左の列（つまりF列）で探し、見つかったらそこから2列目（つまり1つ右）のセルの値を返す
- 第1引数：検索値
- 第2引数：マスタの範囲（一番左の列が検索範囲となる）
- 第3引数：第2引数で指定した一番左の列から見て何列目の値を返すか
- 第4引数：ここはとりあえず0(=FALSE)でよい、TRUEを指定すると一致する値がない場合に一番近い値を返す
重複をなくす
例）1行目がヘッダでA2からA100まで数値が入力されている場合
B2セルに=COUNTIF(A:A,A2)
を入力し、これをB100までコピー。
これで重複したデータはB列が1より大きくなる。
重複データを削除するにはB2セルを=COUNTIF($A$2:A2,A2)
とし、これをB100までコピーする。
オートフィルタでB列が2以上の行のみを抽出し、削除すればよい。
※Excel2007以降には「重複の削除」機能があるが、バグがあるので使わないこと
連続データの作成
ホーム⇒フィル⇒連続データの作成
例）
範囲：列
種類：加算
増分値：1
停止値：1000
とすると1〜1000まで入力される。
ある程度の数字まではオートフィルで十分だが、大きくなるとマウスのドラッグを行なうには難しくなる。
MATCH関数
MATCH(検索値、検索範囲,0)
例）=MATCH(A1,$F$1:$I$1,0)
A1の値がF1からI1の何番目に出てくるか。
第3引数は0を入力しておけばよい。
VLOOKUP関数で入力場所とマスタで項目の並び順が違っている場合に、MATCH関数を使います。
例）B列からG列が入力セル、I列からN列がマスタで、それぞれの順番が異なる場合=VLOOKUP($B3,$I:$N,MATCH(C$2,$I$2:$N$2,0),0)
VLOOKUP関数で検索列より左側の値を取得する
VLOOKUP関数の第3引数にはマイナスの値を指定することはできない。
MATCH関数とOFFSET関数を組み合わせることで解決できる。
例）A列が単価、B列が商品コードのマスタで、E2に入力された商品コードの単価を表示したい場合=OFFSET(B1,MATCH(E2,B:B,0)-1,-1)
B1セルを基準に、第2引数分だけ下に、左に1つずらしたセルの値を返す。
第2引数は、E2に入力された値をB列で検索し、見つかった行番号から1を引いた値。
（先頭がヘッダ行の場合は1を引く）
OFFSET(セル,ずらす行数,ずらす列数)
例）=OFFSET(A1,2,2)
C3の値が返される（A1から下に2行、右に2列）