2019年１０月２０日

先回の研究会ではエクセルで、国勢調査の人口データを資料  として、データ分析を試みました。

今月はエクセルの計算や関数に取り組みました。

関数は実務でも良く利用されるVLOOKUPを主な対象とし、  他の関数と組み合わせた便利な利用方法にもチャレンジしました。 

１、数式と関数の基本

 (1)主な演算子（+、=、*、/、^、%） 

①＝A1+B1 

②＝A1-B1 

③＝A1*B1 

④＝A1/B1 

⑤＝A1^B1 

⑥＝A1*50% 

※数式は「=」で始まる、「半角」で入力、小文字でもよい、セル参照は

クリックで 

 (2)計算の順位（一般の算数と同じ）

       ①左から右へ 

②内側の括弧優先 

③優先順位は、「べき乗」が最優先、次に「乗除」、最後が「加減」 

 (3)関数の入力方法

 　①「=」の後に関数名を入力→候補が表示されたら関数名をダブルクリック

 　②引数を入力（クリックやドラッグを利用できる）

 (4)オートフィルで連続入力

 　①相対参照 

　②絶対参照 

　③相対、絶対の切替はF4キーが便利

 ２、関数の仕組みとルール

 (1)関数の仕組み 

①関数名 

②引数 

③戻り値

 (2)関数の入力方法 

①関数ライブラリから入力（例はIF） 

・数式タブ 

・論理 

・IF 

・「関数の引数」画面

②「関数の挿入」ボタンから入力 

・数式バーの「関数の挿入」ボタン 

・検索欄に分類 

・目的の関数をクリック 

・「関数の引数」画面

 ３、引数の指定の仕方

 (1)セル参照 

①クリックでセルを指定 

②ドラッグでセル範囲を指定 

③離れた場所のセルを指定 

④別シートのセル 

⑤参照演算子 

・コロン、A1:B5　セルA1からB5までのすべてのセル 

・カンマ、A1,B5　セルA1とB5

・エクスクラメーションマーク、売上!B2　「売上」シートのセルB2

 (2)セル範囲に名前を付ける 

①セル範囲を選択 

②数式タブ→名前の定義→「新しい名前」画面 

③関数の引数に、セル範囲でなく「名前」を入力して利用する

 (3)名前を削除する 

①数式タブ→名前の管理→「名前の管理」画面 

②削除したい名前を選択し「削除」

 (4)名前の編集 

①数式タブ→名前の管理→「名前の管理」画面 

②編集タブでセル範囲などを修正できる

 ４、関数の修正方法

 (1)エラーの種類 

①#####（シャープ）：セル幅が不十分、または日付や時刻の計算結果がマイナス 

②#NULL!（ヌル）：参照する範囲が間違い　例=SUM(A1 B10) 

③#DIV/0!（デバイド・パー・ゼロ）：ゼロで割り算をしている 

④＃VALUE!（バリュー）：数値の引数に文字列を指定した時など 

⑤#REF!（リファレンス）：参照セルが見当たらない 

⑥#NAME（ネーム）：関数名を間違えたとき　例=SAM(A1:B10) 

⑦#NUM!（ナンバー）：引数に誤りがある　例=DATE(20201,1,1) 

(8)#N/A!（ノー・アサイン）：参照セルが間違っている、参照セルに適切な

　データがない

 (2)エラーの修正方法 

①数式バーで修正 

②セル上で修正 

③「関数の挿入」ボタンをクリックし「関数の引数」画面で修正

 ５、オートSUMの仕組み

 (1)基本的な仕組み 

①合計を表示するセルを選択 

②「オートSUM」ボタンをクリック 

③エクセルが選んだ合計対象範囲を確認 

・適切ならばEnter 

・適切でなければ、訂正してEnter 

④オートフィルで式をコピー 

⑤絶対参照の必要性を確認する

 (2)早わざ１ 

①合計対象セルと合計表示セルを同時に範囲指定 

②「オートSUM」ボタンをクリック

 (3)早わざ２ 

①縦横とも同時に、合計対象セルと合計表示セルを同時に範囲指定 

②「オートSUM」ボタンをクリック

 ６、テーブルとして書式設定 

リストを「テーブルとして書式設定」すると利点がある

 　(1)名前が付与される 

(2)行の追加が手軽 

①書式が自動的に引き継がれる 

②数式式が自動的に引き継がれる 

(3)名前の範囲が自動的に変化する

 　(4)構造化参照を利用できる 

①通常のセル参照 

②構造化参照によるセル参照 

③構造化参照で使われる指定子の種類 

・[#すべて]：テーブル範囲のすべて 

・[#データ]：見出しと集計を除いたデータ部分 

・[#見出し]：見出し行の部分 

・[#集計]：集計行の部分 

・[@]：関数や数式が入力されている行 

・[見出し名]：見出しで名で示される列 

・[@見出し名]：[@]と[見出し名]が交差するセル

 ７、VLOOKUP関数

　 (1)関数の概要

 　(2)VLOOKUP関数への不満 

①参照リストが変化するたびに修正が必要 

②リスト参照はあちこちで利用される 

③検索値がリストの左端でなければならない

(3)名前あるいはテーブルの利用である程度は凌げる

 ８、VLOOKUP関数とIF関数の組み合わせ

　(1)エラーでないのにエラーメッセージが表示される 

(2)エラーメッセージは表示したくない

９、VLOOKUP関数の代替もある

 　(1)INDEX関数 

INDEX関数は、範囲内の縦いくつ、横いくつの位置にあるセルの値を調べる関数
書式は、=INDEX(範囲,縦位置,横位置) 

 　(2) MATCH関数 

MATCH関数は、範囲内から指定した値を探して、範囲内の上から数えた位置を求める関数 

書式は、=MATCH(検索値,範囲,一致か近似値か) 

 　(3) INDEX関数とINDEX関数の組み合わせ 

=INDEX(実際に求める値の範囲,MATCH(検索値,検索する値の範囲,0)) 

 D3からD6の範囲からB001の行番号を探し（MATCH）、

B3からB6の範囲の中から、その行番号の値を返す（INDEX）

１０、近々ExcelにXLOOKUPという関数が追加されるらしい

注:  VLOOKUP では、数式は、= VLOOKUP (F2、B2: D11、3, FALSE)

以上

会場　　　　　： 中央区・明石町区民館
リーダー　　　： 永岡 勝男 さん
世話人　　　　： 古川 勝彦　さん
時間　　　　　：１３：３０～１６：３０

2144