Excelは表計算ソフトと呼ばれ、ビジネスの場では必須品とも言えるアイテムです。表計算機能を利用して集計業務や予算建て作業にも使います。関数をマスターする事で正確でスピーディーな仕事を実現出来ますので是非ともマスターしましょう。
目次
SUMIF関数 AND関数 COUNT関数
AVERAGE関数 STDEV関数 IF関数
VLOOKUP関数
INDEX+MATCH関数
FREQUENCY関数(配列数式)
@AND関数 2つ以上の条件に一致したものを抽出する
=AND(C3=”ランチ”D3=”スタンダード”) をF3セルに入力してます。その後F3セルをコピーしてF4,F5,F6へ貼りつけます。F3、F6セルにTRUEと表示され、F4,F5セルにはFALSEと表示されます。
二つの参照したい単語に該当すると TRUEと表示されます。該当しない場合はFALSEと表示されます。
ASUMIF関数 探したい条件の数値の合計を出す
上記の表の内、全ての時間帯のスタンダードの料金を算出したい場合の入力例
=SUMIF(D3:D6,”スタンダード”,E3:E6) をE8セルに入力してます。
BAND関数 抽出した数値の合計を算出する
=SUMIF(F3:F6,TRUE,E3:E6) をE9セルに入力してます。
CCOUNT関数 データの数を数える
=COUNTIF(F3:F6,ディナー) をE10セルに入力してます。
DCOUNTBLANK関数 セル範囲内で未入力のセルの個数を求める関数です。
=COUNTBLANK(B3:E6) をE11に入力してます。B3セルからE6セルの内、空白がいくつあるかを表示します。
Eセルに入力した文字と同様の文字を数える
=COUNTIF(B3:F6,D12) をE12に入力しています。 D12と同じ文字(ここではデラックス)をB3セルからF6セルにいくつあるかをE12に表示します。
|
A |
B |
C |
D |
E |
F |
G |
1 |
|
|
|
|
|
|
|
2 |
|
予約番号 |
時間帯 |
コース種別 |
利用金額 |
|
|
3 |
|
0001 |
ランチ |
スタンダード |
5000 |
@ TRUE |
|
4 |
|
0002 |
|
スタンダード |
5000 |
@ FALSE |
|
5 |
|
0003 |
ディナー |
デラックス |
9000 |
@ FALSE |
|
6 |
|
0004 |
ランチ |
スタンダード |
5000 |
@ TRUE |
|
7 |
|
|
|
|
|
|
|
8 |
|
|
|
スタンダード計 |
A 15000 |
|
|
9 |
|
|
|
TRUE計 |
B 10000 |
|
|
10 |
|
|
|
ディナー件数 |
C 1 |
|
|
11 |
|
|
|
空白のセル数 |
D 1 |
|
|
12 |
|
数えたい条件手入力→ |
デラックス |
E 1 |
|
|
@平均値を計算する
=AVEREGE(E3:E12) をこの場合 I3セルに入力しています。
A標準偏差を計算する
=STDEV(E3:E12) をこの場合 I6セルに入力しています。
B偏差値を計算する
偏差値は (個別の値−平均)×10/標準偏差+50 で計算します。
=(E3−$I$3)*10/$I$6+50 をG3セルに入力しています。その後G3セルをコピーしてG4セルからG10セルへ貼りつけてます。
ポイント
$は絶対参照といい、数式を別のセルにコピーをしても、常に同じセルを参照させるための記号です。
C210点以上には合格と表示させる
=IF(E3>=210,”合格”,””) をG3に入力してます。その後、G3セルをコピーしてG4からG12セルへ貼りつけてます。
D平均点以上には合格と表示させる
=IF(E3>=$I$3,”合格”,””) をI6セルに入力しています。
INDEX関数 MAX関数 LARGE関数 SMALL関数 MIN関数 応用
E指定した順位の合計点数表示する
=MAX(E3:E12) をE15セルに入力しています。・・・1位
=LARGE(E3:E12,2) をE16セルに入力しています。・・・2位
=LARGE(E3:E12,3) をE17セルに入力しています。・・・3位
=SMALL(E3:E12,2) をE18セルに入力しています。・・・下から2位
=MIN(E3:E12) をE19セルに入力しています。・・・最下位
F順位の受験番号を表示する
=INDEX(A3:A12,MATCH(LARGE(E3:E12,1),E3:E10,0),1) をD15セルに入力しています。・・・1位
=INDEX(A3:A12,MATCH(LARGE(E3:E12,2),E3:E10,0),1) をD16セルに入力しています。・・・2位
=INDEX(A3:A12,MATCH(LARGE(E3:E12,3),E3:E10,0),1) をD17セルに入力しています。・・・3位
=INDEX(A3:A12,MATCH(SMALL(E3:E12,2),E3:E10,0),1) をD18セルに入力しています。・・・下から2位
=INDEX(A3:A12,MATCH(SMALL(E3:E12,1),E3:E10,0),1) をD18セルに入力しています。・・・最下位
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
1 |
|
|
|
|
|
|
|
|
|
|
2 |
受験番号 |
国語 |
数学 |
英語 |
合計 |
偏差値 |
210点
以上 |
平均点
以上 |
平均点 |
|
3 |
00001 |
69 |
49 |
59 |
197 |
B32.55 |
C |
D |
@ 210.90 |
|
4 |
00002 |
70 |
69 |
65 |
204 |
B41.34 |
C |
D |
|
|
5 |
00003 |
59 |
79 |
71 |
209 |
B47.61 |
C |
D |
標準偏差 |
|
6 |
00004 |
67 |
69 |
81 |
217 |
B57.66 |
C合格 |
D合格 |
A7.96 |
|
7 |
00005 |
72 |
75 |
63 |
210 |
B48.87 |
C合格 |
D |
|
|
8 |
00006 |
82 |
65 |
74 |
221 |
B62.68 |
C合格 |
D合格 |
|
|
9 |
00007 |
95 |
61 |
49 |
205 |
B42.68 |
C |
D |
|
|
10 |
00008 |
49 |
86 |
78 |
213 |
B52.64 |
C |
D合格 |
|
|
11 |
00009 |
69 |
76 |
65 |
210 |
B48.87 |
C合格 |
D |
|
|
12 |
00010 |
77 |
65 |
81 |
223 |
B65.19 |
C合格 |
D合格 |
|
|
13 |
|
|
|
|
|
|
|
|
|
|
14 |
|
|
|
|
|
|
|
|
|
|
15 |
|
|
1位 |
F00010 |
E223 |
|
|
|
|
|
16 |
|
|
2位 |
F00006 |
E221 |
|
|
|
|
|
17 |
|
|
3位 |
F00004 |
E217 |
|
|
|
|
|
18 |
|
|
下2位 |
F00002 |
E204 |
|
|
|
|
|
19 |
|
|
最下位 |
F00001 |
E197 |
|
|
|
|
|
Excelでは必要なデータを、すでに用意された既存の表から探して参照することがあります。例えば商品台帳のような一覧表から、簡単な英数字(商品コード)を入力するだけで、しゅおひん情報を引き出すことが出来ます。こうした操作を「表引き」と呼び、効率良くデータを扱うのに欠かせない機能です。ここではやや変則的ですが、お客様番号を使って住所録から各種のデータを引き出す例を掲載しています。
@入力した値のセルから2列目の値を呼び出す
=VLOOKUP(C3,B8:F12,2,0) をE2セルに入力しています。C3セルには手入力にて00003と入力した状態です。
A入力した値のセルから4列目の値を呼び出す
=VLOOKUP(C3,B8:F12,4,0) をE3セルに入力しています。C3セルには手入力にて00003と入力した状態です。
B入力した値のセルから5列目の値を呼び出す
=VLOOKUP(C3,B8:F12,5,0) をE4セルに入力しています。C3セルには手入力にて00003と入力した状態です。
VLOOKUP関数は、一番左側の列をキーにして「表引き」を行います。
|
A |
B |
C |
D |
E |
F |
G |
1 |
|
|
|
|
|
|
|
2 |
|
|
|
性別 |
@女性 |
|
|
3 |
|
★お客様番号を手入力 → |
00003 |
都道府県 |
A青森県 |
|
|
4 |
|
|
|
区市町村 |
B青森市 |
|
|
5 |
|
|
|
|
|
|
|
7 |
|
お客様番号 |
性別 |
郵便番号 |
都道府県 |
区市町村 |
|
8 |
|
00001 |
男性 |
102-8341 |
東京都 |
千代田区 |
|
9 |
|
00002 |
男性 |
982-0215 |
宮城県 |
仙台市 |
|
10 |
|
00003 |
女性 |
030-0132 |
青森県 |
青森市 |
|
11 |
|
00004 |
女性 |
990-8560 |
山形県 |
山形市 |
|
12 |
|
00005 |
男性 |
010-1406 |
秋田県 |
秋田市 |
|
13 |
|
|
|
|
|
|
|
@2つの条件に一致したものを表引きによって探し出す
=INDEX(D4:G7,MATCH(D10,C4:C7),MATCH(D10,D3:G3,0)) をF10セルに入力しています。
|
A |
B |
C |
D |
E |
F |
G |
1 |
|
旅行代金(お一人様あたり) |
|
2 |
|
|
人数 |
3 |
|
1 |
2 |
3 |
4 |
4 |
|
部屋 |
デラックス |
100000 |
88000 |
88000 |
86000 |
5 |
|
スーペリア |
92000 |
80000 |
82000 |
80000 |
6 |
|
スタンダード |
80000 |
78000 |
78000 |
72000 |
7 |
|
エコノミー |
68000 |
65000 |
64000 |
60000 |
8 |
|
|
|
|
|
|
9 |
|
部屋ランクは?→ |
スタンダード |
|
料金 |
|
10 |
|
人数は?→ |
3 |
|
@ 75000 |
|
11 |
|
|
|
|
|
|
アンケート集計やグラフ化にも、エクセルは大きな力を発揮します。勿論アンケート集計・分析用に開発されたソフトと比べれば機能的には劣りますが、使う側の工夫次第でエクセルでも十分に実用できます。アンケート結果を回答者の年齢別にカウントしてみます。
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
|
|
|
|
|
|
|
|
2 |
|
年齢 |
性別 |
希望 |
|
年代 |
※作業用 |
人数 |
3 |
|
22 |
女性 |
沖縄 |
|
10歳以下 |
10 |
@ 1 |
4 |
|
27 |
女性 |
沖縄 |
|
11〜20歳 |
20 |
@ 2 |
5 |
|
32 |
男性 |
京都 |
|
21〜30歳 |
30 |
@ 3 |
6 |
|
10 |
男性 |
青森 |
|
31〜40歳 |
40 |
@ 3 |
7 |
|
42 |
女性 |
京都 |
|
41〜50歳 |
50 |
@ 2 |
8 |
|
39 |
男性 |
熊本 |
|
51〜60歳 |
60 |
@ 1 |
9 |
|
53 |
女性 |
沖縄 |
|
61歳以上 |
999 |
@ 1 |
10 |
|
16 |
女性 |
東京 |
|
|
|
|
11 |
|
61 |
男性 |
沖縄 |
|
|
|
|
12 |
|
19 |
男性 |
北海道 |
|
|
|
|
13 |
|
29 |
女性 |
沖縄 |
|
|
|
|
14 |
|
31 |
男性 |
北海道 |
|
|
|
|
15 |
|
50 |
女性 |
高知 |
|
|
|
|
16 |
|
|
|
|
|
|
|
|
※作業用は「〜歳まで」という区間を意味する数値になります。例えば年齢区分が「〜歳未満」になる場合は、0、19、29、39、49、59、999となります。
@年代別に分けて人数を表示する
=FREQUENCY(B3:15,G3:G9) をH3からH9に入力してますが、
入力の際は「Enter」で確定をせず「Ctrl」+「Shift」+「Enter」で確定します。数式のバーが
{}で囲われたら入力終了です。
ポイント
{}が付く数式は配列数式と呼ばれ、通常の数式とはやや異なる計算を行います。手入力で「{」や「}」を入力しても配列数式にはなりません。
関数以外のExcel技
0のみを表示させない
@ツール→Aオプション→B表示→Cゼロ値→D書式→Eセル→F表示形式→Gユーザー定義
加工されないための保護
@ツール→A保護→Bブックの保護→C保護対象とパスワードを設定
他人による保存防止
@ツール→Aオプション→Bセキュリティ→C書き込みパスワード
指定した範囲の書き換え防止
@ツール→A保護→B範囲の編集を許可→C保護範囲を指定→Dパスワードの設定→Eシートの保護
指定したシートを見られないためのパスワード設定
@書式→Aシート→B表示しない→Cツール→D保護→Eブックの保護
データ自体を見られないためのパスワード設定
@ツール→Aオプション→Bセキュリティ→C読み取りパスワード
指定した範囲を非表示
@書式→Aセル→B保護(「ロック」と「表示しない」を選択)→Cシートの保護
スポンサードリンク