本文へスキップ

ビジネスに役立てるための情報サイトです。

EXCEL関数


Excelは表計算ソフトと呼ばれ、ビジネスの場では必須品とも言えるアイテムです。表計算機能を利用して集計業務や予算建て作業にも使います。関数をマスターする事で正確でスピーディーな仕事を実現出来ますので是非ともマスターしましょう。

目次


SUMIF関数　AND関数　COUNT関数
AVERAGE関数　STDEV関数　IF関数
VLOOKUP関数
INDEX+MATCH関数
FREQUENCY関数（配列数式）

SUMIF関数　AND関数　COUNT関数　指定した条件の値を出す

①AND関数　2つ以上の条件に一致したものを抽出する
=AND（C3＝”ランチ”D3＝”スタンダード”）　をF3セルに入力してます。その後F3セルをコピーしてF4,F5,F6へ貼りつけます。F3、F6セルにTRUEと表示され、F4,F5セルにはFALSEと表示されます。
二つの参照したい単語に該当すると　TRUEと表示されます。該当しない場合はFALSEと表示されます。
②SUMIF関数　探したい条件の数値の合計を出す
上記の表の内、全ての時間帯のスタンダードの料金を算出したい場合の入力例
＝SUMIF（D3:D6,”スタンダード”,E3:E6）　　をE8セルに入力してます。
③AND関数　抽出した数値の合計を算出する
＝SUMIF（F3:F6,TRUE,E3:E6）　　をE9セルに入力してます。
④COUNT関数　　　データの数を数える
＝COUNTIF（F3：F6,ディナー）　　をE10セルに入力してます。
⑤COUNTBLANK関数　セル範囲内で未入力のセルの個数を求める関数です。
＝COUNTBLANK（B3：E6）　をE11に入力してます。B3セルからE6セルの内、空白がいくつあるかを表示します。
⑥セルに入力した文字と同様の文字を数える
＝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       スタンダード計 ②　15000    
 9        TRUE計 ③　10000    
10        ディナー件数 ④　1    
 11        空白のセル数 ⑤　1    
 12   数えたい条件手入力→   デラックス ⑥　1    

AVERAGE関数　STDEV関数　IF関数　平均点　偏差値　合否を求める

①平均値を計算する
＝AVEREGE（E3:E12）　　をこの場合　I3セルに入力しています。
②標準偏差を計算する
＝STDEV（E3:E12）　をこの場合　I6セルに入力しています。
③偏差値を計算する
偏差値は　　（個別の値－平均）×10／標準偏差＋50　で計算します。
＝(E3－$I$3)*10／$I$6+50　をG3セルに入力しています。その後G3セルをコピーしてG4セルからG10セルへ貼りつけてます。
ポイント
$は絶対参照といい、数式を別のセルにコピーをしても、常に同じセルを参照させるための記号です。

④210点以上には合格と表示させる
=IF(E3>=210,”合格”,””)　をG3に入力してます。その後、G3セルをコピーしてG4からG12セルへ貼りつけてます。

⑤平均点以上には合格と表示させる
=IF(E3>=$I$3,”合格”,””)　をI6セルに入力しています。

INDEX関数　MAX関数　LARGE関数　SMALL関数　MIN関数　応用

⑥指定した順位の合計点数表示する
＝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セルに入力しています。・・・最下位

⑦順位の受験番号を表示する
=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 ③32.55 ④ ⑤ ① 210.90  
 4 00002 70 69 65 204 ③41.34 ④ ⑤    
 5 00003 59 79 71 209 ③47.61 ④ ⑤  標準偏差  
 6 00004 67 69 81 217 ③57.66  ④合格  ⑤合格 ②7.96  
 7 00005 72 75 63 210 ③48.87  ④合格 ⑤    
 8 00006 82 65 74 221 ③62.68  ④合格  ⑤合格    
 9 00007 95 61 49 205 ③42.68 ④ ⑤    
 10 00008 49 86 78 213 ③52.64 ④  ⑤合格    
 11 00009 69 76 65 210 ③48.87  ④合格 ⑤    
 12 00010 77 65 81 223 ③65.19  ④合格 ⑤合格    
 13                    
 14                    
 15     1位 ⑦00010 ⑥223          
 16     2位 ⑦00006 ⑥221          
 17     3位  ⑦00004 ⑥217          
 18      下2位 ⑦00002 ⑥204          
 19     最下位 ⑦00001 ⑥197          


VLOOKUP関数

Excelでは必要なデータを、すでに用意された既存の表から探して参照することがあります。例えば商品台帳のような一覧表から、簡単な英数字（商品コード）を入力するだけで、しゅおひん情報を引き出すことが出来ます。こうした操作を「表引き」と呼び、効率良くデータを扱うのに欠かせない機能です。ここではやや変則的ですが、お客様番号を使って住所録から各種のデータを引き出す例を掲載しています。
①入力した値のセルから2列目の値を呼び出す
＝VLOOKUP（C3,B8:F12,2,0）　　をE2セルに入力しています。C3セルには手入力にて00003と入力した状態です。
②入力した値のセルから4列目の値を呼び出す
＝VLOOKUP（C3,B8:F12,4,0）　　をE3セルに入力しています。C3セルには手入力にて00003と入力した状態です。
③入力した値のセルから5列目の値を呼び出す
＝VLOOKUP（C3,B8:F12,5,0）　　をE4セルに入力しています。C3セルには手入力にて00003と入力した状態です。

VLOOKUP関数は、一番左側の列をキーにして「表引き」を行います。
  A B  C  D E  F  G 
 1              
 2      性別 ①女性
 3   ★お客様番号を手入力 →  00003   都道府県   ②青森県
 4         区市町村   ③青森市
 5              
 7   お客様番号 性別  郵便番号 都道府県  区市町村  
 8   00001 男性 102-8341 東京都 千代田区
 9   00002 男性 982-0215 宮城県 仙台市
 10   00003 女性 030-0132 青森県 青森市
 11   00004 女性 990-8560 山形県 山形市
 12   00005 男性 010-1406 秋田県 秋田市
 13  

INDEX関数+MATCH関数

①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   人数は？→ ３ ①　75000
11            

FREQUENCY関数　年代別に分けて集計する

アンケート集計やグラフ化にも、エクセルは大きな力を発揮します。勿論アンケート集計・分析用に開発されたソフトと比べれば機能的には劣りますが、使う側の工夫次第でエクセルでも十分に実用できます。アンケート結果を回答者の年齢別にカウントしてみます。

  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のみを表示させない
①ツール→②オプション→③表示→④ゼロ値→⑤書式→⑥セル→⑦表示形式→⑧ユーザー定義

加工されないための保護
①ツール→②保護→③ブックの保護→④保護対象とパスワードを設定

他人による保存防止
①ツール→②オプション→③セキュリティ→④書き込みパスワード

指定した範囲の書き換え防止
①ツール→②保護→③範囲の編集を許可→④保護範囲を指定→⑤パスワードの設定→⑥シートの保護

指定したシートを見られないためのパスワード設定
①書式→②シート→③表示しない→④ツール→⑤保護→⑥ブックの保護

データ自体を見られないためのパスワード設定
①ツール→②オプション→③セキュリティ→④読み取りパスワード

指定した範囲を非表示
①書式→②セル→③保護（「ロック」と「表示しない」を選択）→④シートの保護


スポンサードリンク


バナースペース

サイト運営　SMF

資格テキストサイト

inserted by FC2 system