本文へスキップ

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

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と表示されます。
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 E F
 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    

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

@平均値を計算する
=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 C D E G 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          



VLOOKUP関数

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 D
 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  


INDEX関数+MATCH関数

@2つの条件に一致したものを表引きによって探し出す
=INDEX(D4:G7,MATCH(D10,C4:C7),MATCH(D10,D3:G3,0)) をF10セルに入力しています。
  A D
 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 C D E F 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シートの保護



スポンサードリンク


バナースペース

サイト運営 SMF

資格テキストサイト

inserted by FC2 system