基礎的
IT セミナー
業務に役立つ
表計算ソフトの関数の活用
Ⅱ
2021 年
1
月 9 日・23 日・30 日
－2－
効率的なデータの入力
１．ショートカットキーの活用１：真上のセルと同じデータを入力
（セルＤ２８に「個人会員」と入力）
手順：(1) ブック「2 顧客一覧」を開き、シート「顧客名簿」をクリックします。 (2) セル D28 をクリックします。 (3)「Ctrl」キー ＋「D」キーを押します。 ＊真上のセルＤ２７と同じデータが表示されます。 (4) 同様に、セル D29 にも「個人会員」と入力します。
参考
左側のセルと同じデータを入力する場合 「Ctrl」キー ＋「R」キー－3－
２．ショートカットキーの活用２：入力済データのリスト化
（セルＤ３０に「その他」と入力）
手順：(1) セル D30 をクリックします。 (2)「Alt」キー ＋「↓」キーを押します。 (3) D 列に入力済みのデータリストが表示されます。 (4) 入力したいデータ「その他」をクリックします。 ＊『↓』キーで移動後、「Enter」キーで確定することも可能です。 (5) 同様に、セル D31 に「法人会員」と入力します。
参考
「Ａｌｔ」キー ＋「↓」キーでリスト化できるのは、入力したいセルの上に連続 して入力されているデータだけです。 離れたセルにデータを入力するには、コピー・貼り付けのショートカットキーを 使用します。 「Ctrl」キー ＋「C」キー コピー 「Ctrl」キー ＋「V」キー 貼り付け３．連続データの入力１：オートフィル機能
（セルＡ２からＡ３１の「顧客番号」に連番を入力）
手順：(1) セル A2 をクリックします。 (2) フィルハンドル（■）にマウスポインタ（＋）を合わせ、入力したいセルA31 まで ドラッグします。 ＊同じ番号がコピーされます。－4－
(3)「 (オートフィルオプション)」ボタンをクリックします。 (4)「連続データ」をクリックします。
参考
その他の方法 フィルハンドル（■）にマウスポインタ（＋）を合わせ 「Ctrl」キーを押しながらドラッグしても連続データを 入力することができます。 奇数や偶数、10 ずつ増加する場合 (1) 規則を示す、２つのセルを入力します。 (2) その２つのセルを範囲選択します。 (3) フィルハンドルにマウスポインタを合わせ ドラッグします。－5－
４．連続データの入力２：オリジナルのオートフィル機能
（支店名を入力する）
手順：(1) シート見出し「Sheet2」をクリックします。 (2)［ファイル］タブをクリックし、「オプション」をクリックします。 (3)「Excel のオプション」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽの「詳細設定」をクリックします。 (4)「全般」の設定から「ユーザー設定リストの編集」ボタンをクリックします。－6－
(5)「ユーザー設定リスト」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽの「リストの項目」欄に次のとおり オリジナルの連続データを「Enter」キーで改行しながら入力し、 「追加」ボタンをクリックします。 北部支店 中部支店 沖縄支店 南部支店 那覇支店 ＊ユーザー設定リストの末尾に入力したリストが追加されたことを確認します。 (6)「ＯＫ」ボタンをクリックします。 (7)「Excel のオプション」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽの「ＯＫ」ボタンをクリックします。 (8) セル A1 に「北部支店」と入力し、フィルハンドル（■）にマウスポインタを合わせ ドラッグします。 ＊支店名、部署名、社員名など繰り返し使用する文字列を登録しておくと便利です。 (9) シート見出し「顧客名簿」をクリックし、顧客名簿のシートに戻します。５．郵便番号から住所を入力
（セルＥ２８～Ｅ３１の郵便番号をもとにセルＦ２８～Ｆ３１を入力する）
手順：(1) セル F28 をクリックします。 (2) E 列の郵便番号をもとに「904-0203」と入力し「スペース(変換)」キーで 住所を入力します。 (3) 同様に、次のとおり住所を入力します。 セルF28 ： 沖縄県中頭郡嘉手納町嘉手納 27X セルF29 ： 沖縄県宜野湾市真栄原 2-8-X セルF30 ： 沖縄県中頭郡読谷村比謝矼 29X セルF31 ： 沖縄県沖縄市東 3-10-X－7－
６．関数の利用１：ＰＨＯＮＥＴＩＣ関数
（セルＣ２～Ｃ３１にふりがなを表示）
手順：(1) セル C2 をクリックします。 (2)「 （関数の挿入）」ボタンをクリックします。 (3)「関数の挿入」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽから次のとおり選択します。 関数の分類 ： 論理 関数名 ： IF (4)「IF」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり設定します。 論理式 ： B 2 =”” 値が真の場合 ： ”” (5)「IF」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽの値が偽の場合にカーソルがある状態で、「関数ボックス」の「▼」 をクリックし「PHONETIC」関数を選択します。 リストにない場合は「その他の関数」をクリックし「関数の挿入」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽから 選択します。－8－
(6)「PHONETIC」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで、セル B2 を選択し「ＯＫ」ボタンをクリックします。 (7) セル C2 をクリックし、フィルハンドル（■）にマウスポインタを合わせ セルC50 までドラッグしコピーします。 (8) セル B2 をクリックします。 (9)「Shift」キーを押しながら、セル B50 をクリックし、セル B2～B50 を範囲選択 します。 (10)「ホーム」リボン－「フォント」グループから「 (ふりがなの表示/非表示)」 ボタンの「▼」をクリックし、「ふりがなの設定」を選択します。－9－
(11)「ふりがなの設定」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽの「ふりがな」タブ－「種類」で「ひらがな」を 選択し「ＯＫ」ボタンをクリックします。 ＊ふりがなを表示したい列（Ｃ列）ではなく、元データ（Ｂ列）で ふりがなの設定を行います。７．フリガナの編集
手順：(1) セル B5 をクリックします。 (2)「ホーム」リボン－「フォント」グループから「 (ふりがなの表示/非表示)」 ボタンの「▼」をクリックし、「ふりがなの編集」を選択します。 (3)「あんどうよんの」のふりがなを「あんどうしの」と編集し、確定します。－10－
８．関数の利用２：ＤＡＴＥＤＩＦ関数（セルＨ２～Ｈ３１に年齢を表示）
手順：(1) セル H2 をクリックします。 (2)「 （関数の挿入）」ボタンをクリックし「IF」を選択します。 (3)「IF」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり設定します。 論理式 ： G2=”” 値が真の場合 ： ”” 値が真の場合 ： datedif(G2,today(),”y”) ＊ＤＡＴＥＤＩＦ関数は、ＥＸＣＥＬの標準関数ではないためすべて手入力します。 (4) セル H2 の内容を、セル H3～H50 にコピーします。
参考
ＤＡＴＥＤＩＦ関数 ＝DATEDIF（開始日，終了日，計算する単位） 計算する単位の設定 ”Y” … 経過年数 ”YM” … 経過年に満たない月数 ”M” … 経過月数 ”YD” … 経過年に満たない日数 ”D” … 経過日数 ”MD” … 経過月に満たない日数－11－
９．表示形式の設定（セルＨ２～Ｈ５０を「○歳」と表示）
手順：(1) セル H2 をクリックします。 (2)「Ctrl」キー ＋「Shift」キー ＋「↓」キーを押し、 セルH2～H50 を範囲選択します。 (3)「Ctrl」キー ＋「1」キーを押し、 「書式設定」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽを表示します。 (4)「表示形式」タブを選択し 次のとおり設定し「ＯＫ」ボタンを クリックします。 分類 ： ユーザー設定 種類 ： 0 歳 (5) データベース内の任意のセルを選択し、 範囲選択を解除します。
参考
顧客番号を自動連番に設定する セルA3 に次の式を入力し、顧客番号が自動的に表示されるように設定します。 その後、計算式をセルA50 までコピーしましょう。 =IF(B3=””,””,A2+1)＊練習問題＊
１．次のとおり、新規データを入力します。 顧 客 名 ： 丸山雅子 種 別 ： 個人会員 郵便番号 ： 904-0032 住 所 ： 沖縄県沖縄市諸見里3-1-X 生年月日 ： 1969/7/3 顧 客 名 ： 佐藤寛 種 別 ： 法人会員 郵便番号 ： 901-2206 住 所 ： 沖縄県宜野湾市愛知3-2-X 生年月日 ： 1982/3/15－12－
１０．入力ミスを防ぐ：入力規則（顧客登録は１８歳以上で設定する）
手順：(1) G 列の列番号をクリックします。 (2)「データ」リボンの「データツール」グループから「 （データの入力規則）」を クリックします。 (3)「データの入力規則」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽ「設定」タブで次の設定をします。 入力値の種類 ： 日付 データ ： 次の値以下 次の日付まで ： =DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY()))－13－
(4)「エラーメッセージ」タブをクリックし、次のとおり設定し「ＯＫ」ボタンをクリック します。 スタイル ： 停止 エラーメッセージ ： 顧客登録は 18 歳以上の方です。 (5) 上書き保存しましょう。
参考
ＤＡＴＥ関数：指定した日付（数値）を シリアル値に返します。 ＝DATE（年，月，日） ＹＥＡＲ関数：指定した日付（シリアル値）から「年」の数値を取り出します。 ＝YEAR（日付） ＭＯＮＴＨ関数：指定した日付（シリアル値）から「月」の数値を取り出します。 ＝MONTH（日付） ＤＡＹ関数：指定した日付（シリアル値）から「日」の数値を取り出します。 ＝DAY（日付） ＴＥＸＴ関数：数値に表示形式を適用した文字列に変換します。 ＝TEXT（値，表示形式）－14－
練習問題 4
ブック「練習④」を開き、「お客様リスト」を完成させましょう。 ＜処理内容＞ １．セルC6 に、COUNT 関数を使用して「現在の会員数」を表示しましょう。 また、表示形式を「○名」と表示されるよう設定しましょう。 ２．セルD6 に、関数を使用して、今日の日付を表示しましょう。 ３．セルC9～C18 に、PHONETIC 関数を使用して、「フリガナ」を表示しましょう。 ４．セル E9～E18 に、DATEDIF 関数を使用して、入会日からの「継続月数」を表示しましょ う。 ５．以下の通り、VLOOKUP 関数を使用して、「地区」（G9～G18）および「担当」（H9～H18） を表示しましょう。 (1) 「地区コード表」（A4～C8）から、「地区コード」（F9～F18）に該当する「地区」およ び「担当」を表示する。 (2) 「検索方法」は「検索値」と完全に一致する値のみを検索するように設定する。 ６．「練習④完成」のファイル名で保存しましょう。 ＜完成見本＞－15－
練習問題 5
ブック「練習⑤」を開き、「月間スケジュール表」を完成させましょう。 ＜処理内容＞ １．セルB8 に、関数を使用して、セル C4 とセル C5 のどちらかが空白なら空白を、セル C4 と セルC5 に年月が入力されていれば該当する日付を表示しましょう。 セルB8 =IF(OR(C4=””,C5=””),””,DATE(C4,C5,1)) ２．セルB8～B22 およびセル E8～E23 に日にちのみ表示されるよう表示形式を設定しましょう。 ３．毎月28 日までは、「前月の日にち＋1」で求めましょう。 29 日以降については、「前日の日にち＋1」としたときの月がセル C5 と異なっていれば、 月が変わったことになるので日付を表示しないようにします。 また、前日の日にちが空白なら同様に日付は表示しないよう設定ししましょう。 セルB9 =IF(B8=””,””,B8+1) セルE8 =IF(B22=””,””,B22+1) セルE21 =IF(E20=””,””,IF(MONTH(E20+1)<>$C$5,””,E20+1)) ４．それぞれの曜日のセルには、日付に対応した曜日が表示されるよう TEXT 関数を使用して 設定しましょう。 セルC8 =TEXT(B8,”aaa”) ５．曜日は、日曜日は文字の色を「赤」、土曜日は「青」になるよう条件付き書式を設定しまし ょう。 ６．「練習⑤完成」のファイル名で保存しましょう。 ＜完成見本＞－16－
関数の活用
使用する関数
ＩＦＥＲＲＯＲ（式の値がエラーになった場合の値を返します）
＝IFERROR（値，エラーの場合の値）ＦＩＮＤ（検索文字が対象文字列の最初に現れる位置を検索します）
＝FIND（検索文字，対象，［開始位置］）ＭＩＤ（文字列の指定された開始位置から指定された数の文字列を返します）
＝MID（文字列，開始位置，文字数）ＣＯＵＮＴＩＦ（指定された範囲の中で、検索条件に一致するセルの個数を返します）
＝COUNTIF（範囲，検索条件）ＶＬＯＯＫＵＰ（指定された範囲の１列目で特定の値を検索し、
指定した列と同じ行にある値を返します）
＝VLOOKUP（検索値，範囲，列番号，［検索方法］） 検索したい市町村を 入力すると対象者が 表示される。 検索したい市町村の 対象人数を表示しま す。－17－
１．住所から市町村を取り出す１
（Ｆ列とＧ列の間に３列挿入し、Ｇ列に文字の開始位置を表示）
セルＧ２ ＝ＩＦ（Ｆ２＝””，””，ＩＦＥＲＲＯＲ（ＦＩＮＤ（”郡”，Ｆ２），３））
手順：(1) ブック「2 顧客一覧」を開き、シート「顧客名簿」をクリックします。 (2) G～I 列を選択し右クリック－「挿入」をクリックします。 (3) G～I 列を選択されている状態で右クリック－「列の幅」をクリックします。 (4)「列の幅」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで「8」を設定し、 「ＯＫ」ボタンをクリックします。 (5) セルに次の文字を入力します。 G1 ： 開始位置 H1 ： 終了位置 I1 ： 市町村 (6) セル G2 を選択し「=if」と入力します。 (7) 関数リストから「IF」をダブルクリックします。 (8)「 (関数の挿入)」ボタンをクリックします。－18－
(9)「IF」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり設定し、数式バーの「iferror」をクリックします。 論理式 ： F 2 =”” 値が真の場合 ： ”” 値が偽の場合 ： i f e rr or ( ) (10)「IFERROR」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり入力します。 値 ： find() (11) 数式バーの「find」をクリックします。－19－
(12)「FIND」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり設定します。 検索文字列 ： 郡 対象 ： F2 (13) 数式バーの「IFERROR」をクリックします。 (13)「IFERROR」関数ボックスの「エラーの場合の値」をクリックし、次のとおり入力 し、「ＯＫ」ボタンをクリックします。 エラーの場合の値 ： 3 (14) セル G3～G50 に計算式をコピーします。－20－
２．住所から市町村を取り出す２（Ｈ列に市町村の終了位置を表示）
セルＨ２ ＝ＩＦＥＲＲＯＲ（ＦＩＮＤ（”市”，Ｆ２），
ＩＦＥＲＲＯＲ（ＦＩＮＤ（”町”，Ｆ２），ＩＦＥＲＲＯＲ（ＦＩＮＤ（”村”，Ｆ２），””）））
手順：(1) セル H2 を選択し「=if」と入力します。 (2) 関数リストから「IFERROR」をダブルクリックします。 (3)「fi」と入力します。 (4) 関数リストから「FIND」をダブルクリックします。 (5)「 (関数の挿入)」ボタンをクリックします。 (6)「FIND」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり設定します。 検索文字列 ： 市 対象 ： F2－21－
(7) 数式バーの「IFERROR」をクリックします。 (8)「IFERROR」関数ボックスの「エラーの場合の値」をクリックし、 次のとおり入力します。 エラーの場合の値 ： iferror() (9) 数式バーの「iferror」をクリックします。 (10)「IFERROR」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり入力し、数式バーの「find」をクリック します。 値 ： find()－22－
(11)「FIND」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり設定します。 検索文字列 ： 町 対象 ： F2 (12) 数式バーの「IFERROR」をクリックします。 (13)「IFERROR」関数ボックスの「エラーの場合の値」をクリックし、 次のとおり入力します。 エラーの場合の値 ： iferror() (14) 数式バーの「IFERROR」をクリックします。－23－
(15)「IFERROR」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり入力し「ＯＫ」ボタンをクリックします。 値 ： find(”村”,F2)
エラーの場合の値 ： ””
－24－
３．住所から市町村を取り出す３（Ｉ列に市町村を表示）
セルＩ２ ＝ＩＦ（Ｇ２＝””，””，ＭＩＤ（Ｆ２，Ｇ２＋１，Ｈ２－Ｇ２））
手順：(1) セル H2 をクリックし、「=if」と入力し関数リストから「IF」をダブルクリック します。 (2)「 (関数の挿入)」ボタンをクリックします。 (3)「IF」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり設定します。 論理式 ： G 2 =”” 値が真の場合 ： ”” 値が偽の場合 ： m i d () (4) 数式バーの「mid」をクリックします。 (5)「MID」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次のとおり設定し、「ＯＫ」ボタンをクリックします。 文字列 ： F2 開始位置 ： G2+1 文字列 ： H2-G2 (4) セル I3～I50 に計算式をコピーします。－25－
４．同市町村に連番を振る
（新たにＡ～Ｂ列に２列分挿入し各市町村に連番を表示）
セルＡ２ ＝ＣＯＵＮＴＩＦ（＄Ｋ＄２：Ｋ２，Ｋ２）
セルＢ２ ＝Ｋ２＆Ａ２
手順：(1) A～B 列を選択し 2 列挿入します。 (2) セル A2 をクリックし、「=cou」と入力し関数リストから「COUNTIF」を ダブルクリックします。 (3)「 (関数の挿入)」ボタンをクリックします。 (4)「COUNTIF」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで、次のとおり入力し「ＯＫ」ボタンをクリックします。 範囲 ： $K$2:K2 検索条件 ： K2 (5) セル B2 に計算式「=K2&A2」を入力します。 (6) セル A2～B2 の計算式を、セル A3～B50 にコピーします。－26－
５．市町村に該当する人数を数える（セルＰ２に検索市町村を入力し、
セルＲ２にＫ列データをもとに該当する人数を表示）
セルＲ２ ＝ＣＯＵＮＴＩＦ（Ｋ２：Ｋ５０，Ｐ２）
手順：(1) セル P2 に「沖縄市」と入力します。 (2) セル R2 をクリックし、「=cou」と入力し関数リストから「COUNTIF」を ダブルクリックします。 (3)「 (関数の挿入)」ボタンをクリックします。 (4)「COUNTIF」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで、次のとおり入力し「ＯＫ」ボタンをクリックします。 範囲 ： K2:K50 検索条件 ： P2 ＊Ｂ列の市町村ごとの連番と数が一致するか確認します。 (5) セル P2 に他の市町村も入力して確認しましょう。－27－
６．リストを表示１（Ｏ列に連番を振る）
セルＯ４ ＝ＩＦ（＄Ｒ＄２＋１＜＝Ｃ２ー１０００，””，Ｃ２ー１０００）
手順：(1) セル O4 をクリックし、「=if」と入力し関数リストから「IF」をダブルクリック します。 (2)「 (関数の挿入)」ボタンをクリックします。 (3)「IF」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで次の設定をし「ＯＫ」ボタンをクリックします。 論理式 ： ＄R＄2＋1<=C2-1000 値が真の場合 ： ”” 値が偽の場合 ： C2-1000 (4) セル O4 の計算式をセル O5～O31 にコピーします。－28－
７．リストを表示２（Ｐ列に顧客番号を表示）
セルＰ４ ＝ＩＦＥＲＲＯＲ（ＶＬＯＯＫＵＰ（＄Ｐ＄２＆＄Ｏ４，＄Ｂ＄２：＄Ｍ＄５０，２，０），””）
手順：(1) セル P4 をクリックし、「=if」と入力し関数リストから「IFERROR」を ダブルクリッします。 (2)「vl」と入力し関数リストから「VLOOKUP」をダブルクリックします。 (3)「 (関数の挿入)」ボタンをクリックします。 (4)「VLOOKUP」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで、次のとおり設定します。 検索値 ： $P$2&$O4 範囲 ： $B$2:$M$50 列番号 ： 2 検索方法 ： 0 (5) 数式バーの「IFERROR」をクリックします。－29－
(6)「IFERROR」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで、次のとおり入力し「ＯＫ」ボタンをクリックします。 エラーの場合の値 ： ””
(7) セル P4 の計算式をセル P5～P31 にコピーします。
