仕事の効率アップを図る初心者向けエクセル関数の活用法25

「関数」で仕事の効率をアップする Excel
「関数」で仕事の効率をアップする

パソコンソフトは多数存在しますが、仕事用となればエクセル以外に思い浮かびません。

シートのセルに名前を書き並べば「名簿」ができあがり、売上金額を記載すれば「報告書」の一丁上がり。文書作成から作表まで、何から何までカバーする優れものです。

でも、「関数」という便利な道具を使うとなると、目的のデータが中々取り出せない。「関数は難しくてちょっと手が出ない!」使いこなすには相当な覚悟が必要だと悩みを抱えていませんか?

そこで、関数とはどんなものか、どうやって覚えるのか、なぜ仕事の効率があがるのか。基礎から実践へ、確実に関数を活用するのが今日の学習です。

1.関数はイイコトだらけ 計算が楽

Excelでのさまざまな計算や集計方法
Excelでのさまざまな計算や集計方法

例えば、店舗の売り上げ実績表で「合計売上」や「平均売上」を調べる際、「合計はSUM(サム)関数、平均は AVERAGE(アベレージ)関数で求められる」とすぐに思い浮かべる人も多いかもしれません。ところが、こららの関数を使わなくても「合計」や「平均」を自動計算する機能があります。

  1. SUM 関数:セルの合計値を求めるために使用されます。例えば、=SUM(A1:A10) と入力すると、A1からA10までのセルの合計値が表示されます。
  2. AVERAGE 関数:セルの平均値を求めるために使用されます。例えば、=AVERAGE(A1:A10) と入力すると、A1からA10までのセルの平均値が表示されます。
  3. MAX 関数:セルの最大値を求めるために使用されます。例えば、=MAX(A1:A10) と入力すると、A1からA10までのセルの最大値が表示されます。
  4. MIN 関数:セルの最小値を求めるために使用されます。例えば、=MIN(A1:A10) と入力すると、A1からA10までのセルの最小値が表示されます。
  5. COUNT 関数:セル内にある値の数を数えるために使用されます。例えば、=COUNT(A1:A10) と入力すると、A1からA10までのセル内にある値の数が表示されます。

とりあえず、この5つの関数が最も使われているのではないでしょうか。集計・順位はこれでOKです。

「合計や平均は、SUM や AVERAGE 関数を使えば簡単だ」と思われる人が多いかもしれませんが、ところがこれらの関数を使わなくても「合計」や「平均」を字自動計算する機能があります。

「ちょっと合計が見たい」というだけなら、数値の入ったセルをドラグして選択するだけで、ステータスバーに合計が自動算出でます。

「平均」もメニューで自動算出

それにはステータスバー上を右クリックすると自動的にでます。最大値や最小値、その他あらゆる数値が自動的に出ます。

「オートカルク」機能で離れた範囲を集計するには、1つ目の範囲を選択後、2つ目の範囲を「Ctrl」キーを押しながらドラグすればいいです。

手早く数値を集計できるのは「オートカルク」の利点ですが、集計結果を単に表示するだけで、数値をセルに保存できません。つまり一時的に確認するための機能なのです。

最大値を求めるのが「MAX」、最小値を求めるのが「MIN(ミン)」です。いずれの関数も、引数として数値データが入力されているセル範囲を指定します。セル範囲(G4~G19)を引数に指定して、「=MAX(G4:G19)」とすればいいです。

以上は基本的な集計と順位判定の例ですが、実務で必要となるさまざまな計算、データ処理についても、エクセルは「メニュー」と「関数」の両方を用意していることが多いです。

1.IF 関数でメニューを臨機応変に活用

計算式は不要で、マウス操作だけで集計が可能なのが「ピボットテーブル」です。これを使えば、メニューを選択後、集計したい項目を選んでいくだけで、あっという間に、「費目別の合計」や「男女別の人数」、さらには「平均年齢」などを求めることができます。

ただし、ピボットテーブルも、日々データが増えていく表では、新たに入力したデータを追加するために、いちいちメニューを追加するために、いちいちメニューを選択し、集計範囲を設定しなおさなければなりません。

  1. IF 関数:条件式に基づいて、特定のセルに値を返すために使用されます。例えば、=IF(G4>=80,"合格","不合格") と入力すると、G4が80より大きい場合は “合計” と表示され、そうでない場合は “不合格” と表示されます。

セルに表示したい値が文字列の場合は、「”」ダブルコーテーションで挟んで指定する決まりになっています。I4 で完成した式を下方向にへコピーすれば、全員分の合否を即座に表示できます。

2.VLOOKUP 関数で顧客番号を基に個人情報を表示

VLOOKUP 関数:別のテーブルから情報を取得するために使用されます。例えば、=VLOOKUP(A1,B1:C10,2,FALSE) と入力すると、A1と一致するB列の値が見つかった場合、それに対応するC列の値が表示されます。

顧客番号を入力するだけで、該当する顧客の名前や住所を自動表示する・・・。そんな「データベースソフト」のような仕掛けも、「関数」を使えば簡単です。

表の”左端”に並んだ生徒番号を検索し、該当する個人情報を取り出す場合は、VLOOKUP (ブイルックアップ)関数を利用します。

=VLOOKUP(A3,B9:I41,2,FALSE)

A3 検索値 調べる番号
B9:I41 範囲 生徒全体
2 列番号 「氏名」は2列目
FALSE 検索の型 完全一致

「検索値」に指定した値を、「範囲」に指定した表の左端で探し、該当する行の「列番号」で指定した列からデータを取り出します。「列番号」は、表の左端から1,2,3・・・と数えます。「検索の型」を「FALASE」と指定すると、「検索値」と完全に一致する値だけを該当する値とみなします。

「TRUE」とするか省略すると、「~以上」のように数量を区切った表で、該当する数量を検索できます。引数「列番号」を「3」とすれば「数学」、4とすれば「英語」をおなじようにとりだせます。

「顧客データが別シートにある」という場合も、同じです。関数の引数「範囲」に、別シートの生徒データを指定するだけです。それには、シート名の後ろに「!」記号を付け、続けてセルは飲位を指定します。

条件付きで合計・平均・件数を算出するピボットテーブル

エクセルには「ピポットテーブル」という強力な”自動集計機能”があります。

  1. PivotTable:大量のデータを集計し、要約するために使用されます。データを選択し、PivotTableを作成することで、数値の合計、平均値、最大値、最小値などを一覧表示することができます。

これを使えば、メニューを選択後、集計したい項目を選んでいくだけで、あっという間に「費目別の合計」や「男女別の人数」、あれには「平均年齢」などをもとめることができます。計算式は不要で、マウス操作だけで集計できます。

これらはExcelで一般的に使用される計算や集計の例の一部ですが、多くの他の方法があります。Excelの公式ドキュメントやオンラインのチュートリアルなどから学ぶことができます。

具体的には

データ数や平均値も自動集計

続いて、顧客名簿などで、「男女別の人数を数えたり、「平均年齢」を調べてみましょう。

こうした「個数」の計算や「平均」の算出も、実は「ピボットテーブル」を使って行えます。まずは「ピボットテーブル」のメニューからウィザードを呼び出し、元データの範囲や集計者の作成場所を指定、ポケットテーブルの”枠組み”を用意します。

「男女別の人数」を数えるには、左端の枠に「性別」の項目をドラグまたはチェック。データの個数を数える場合、右側の集計欄には、どの項目をドラグまたはチェックしてもOKです。元表に「空欄」がなければ、どの項目でデータを数えても同じだからです。例えば「年齢」を集計表にドラグすると、まずは「年齢」が「合計」されます。

「行のフィールド」欄には「年齢(数)」とドラグまたはチェック。
「データアイテム」欄には「氏名」をドラグまたはチェック。数値以外の項目をドラグすると、自動的に「個数」の集計になります。

この集計内容は、後から変更ができて、「合計」ではなく「個数」の集計に変えれば、男女別の人数が求められます。具体的には、集計欄を右クリックして「フィールドの設定」を選び、開く画面の「集計の方法」欄で、「データの個数」を選べばいいです。

メニュー操作で集計表が作れる「ピボットテーブル」は、確かに使いやすい機能ですが、家計簿や顧客名簿など、常にデータが増えていく表の場合は「関数」の方がいいです。いったん式を立てておけば、データを追加したときに自動で再計算し、集計結果を更新できるからです。

3.SUMIF 関数で追加データも自動集計 

例えば「費目別の合計」を求めてみます。

「範囲内」で指定した「検索条件」に合うデータを探し、見つかったセルに対応する位置にある「合計範囲」内のセルを合計します。「検索条件」には、条件を表す式や探す文字列を「””」ダブルコーテーションで挟んで指定します。式や文字列の入ったセルを、セル番地で指定してもいいです。

図のように、複数の費目がある中で、特定の費目だけを合計したい場合、SUMIF (サムイフ)関数を使います。

SUMIF 関数は、引数に「範囲、検索条件、合計範囲」を指定すると、「範囲」内で「検索条件に合うデータを探し、そのセルと同じ行にある「合計範囲」のセルの値を合計します。

例えば、A列の「食器」の金額だけを合計したい場合、引数「検索条件」には「食器」と入力した I9 セル、「範囲」には費目のセル範囲の A8~A50セル、「合計範囲」は合計金額が入力されたG8~G50セルを指定します。

=SUMIF(A8:A50,I9,G8:G50)という式を立てればいいわけです。こうすると食器だけの金額を合計できます。

おなじように他の費目の合計を計算するには、「範囲」と「合計範囲」のセルをそれぞれ F4 キーを押して絶対参照にして「$」をつけてからコピーします。するとコピーすると「検索条件」に指定したI列のセル範囲だけが変わり、「範囲」と「合計範囲」のセル範囲は固定できます。

4.COUNTIF で個数の集計、「列全体」を数え、追加分も算入

条件に合うデータを数える COUNTIF 関数で、引数「範囲」を「性別欄」、検索条件を「男」とすれば、男性の人数から追加するデータもすべて集計できます。列全体を指定するには、「:」コロンの前後に列番号をつけて「C:C」とします。

=COUNTIF(C:C,K4)

G:G 範囲 C列全体
K4 検索条件 男

「年齢の合計÷人数」で「平均年齢」を算出

=SUMIF(G:G,K8,H:H)/L4

SUMIF(G:G,K8,H:H) 男性全員の年齢の合計
L4
 男性の人数

「条件に合うデータの平均」を求める関数はありませんが、SUMIF と COUNTIF の結果を使って「合計÷個数」を計算すれば、平均の算出もできます。

=COUNTIF(D:D,K13&”*”)

K13&”*” K13セルが「文京区」なら→”文京区”という文字列に→「文京区」で始まる住所を教える。

「住所1」欄を基に、「文京区」「大田区」といった地域別の人数を調べたい。それには「文京区」で始まるなどの条件で住所を検索し、データを数える必要があります。

COUNTIF 関数の引数「検索条件」に「*」アスタリスク記号を付け、「”文京区*”」と指定すれば、「文京区で始まる」という条件で集計できます。「*」記号とは「前方一致」検索のことで、「&」は値1&値2、というように「値1」「値2」に指定した文字列やセルの値を結合し、一つの文をつくります。

「文京区」の文字をK13セルの文字で指定するには、文字を結合する記号「&」を使い、「K13&”*”」と指定すればいいです。こうした条件での集計は「ピボットテーブル」ではできません。

5.オートフィルタで抽出条件を「リスト」から選択

関数を利用するまでもなく、「オートフィル」を使えばすむこともあります。

アンケート結果を「白ワイン」の顧客だけ取り込みたい・・・そんなとき便利なのが「オートフィルメニュー」です。表を選択して「データ」→「フィルタ」→「オートフィルタ」を選ぶと、表の1行目に、「▼」ボタンがつく。これをクリックすると、その列にある文字列がリスト表示されるので、抽出の条件にしたいもを選びます。

すると、選択した文字列を含むデータだけが、行単位で抽出されます。「白ワイン」で「女性」の顧客だけに絞り込みたい」というように、複数の条件を組み合わせることもできます。

45歳以上50歳以下の顧客を抽出するには、「以上」「45」、「以下」「50」という条件を指定して、絞り込む必要があります。年齢欄につく「▼」ボタンのリストから、「数値フィルター」→「ユーザー設定フィルター」を選びます。

すると開く画面で、詳細な抽出条件を指定できます。同じように、「お住まい」列で「大阪府」「を含む」という条件を指定すれば、「大阪府在住の顧客」の抽出もできます。

さらに、「二重に登録した顧客データを削除したい」という場合も、メニューで処理できます。

まず、表を選択し「データ」→「詳細設定」を選びます。リスト範囲に”重複の判定に使う列”を指定し、例えば「性別」と「お住まい」の両方が一致した場合だけ重複とみなすとしたければ、「性別」と「お住まい」の2列をドラグして指定すればいいです。

あとは「重複するレコードは無視しる」にチェックして「OK」を押せば、「性別」と「お住まい」が重複するデータだけが削除されます。

ただ”削除”とはいっても、実際はその行が”非表示”になっているだけです。完全に削除した表に作り直すには、作業結果をコピーして、別シートに貼り付けましょう。

6.FIND,LEFT,MID 関数で文字の分割

「銀行名」と「支店名」を分けるときのように、分割する文字数が一定せず、区切りを示す記号なども存在しない場合は、「関数」を活用します。

FIND(ファインド)関数を使うと、「○○銀行」の「行」の文字が、セルの何文字目かを調べられます。この文字位置を基準として、LEFT(レフト)関数や MID(ミッド)関数で前後の文字を切り出します。

=FIND(“行”,A3)
検索文字列 対象
「行」という文字の位置を調べる A列の銀行名
 

まず「○○銀行」の「行」の文字が何文字目かを調べる。FIND関数の引数「検索文字列」に「”行”」と指定し、「対象」にA列の銀行名を指定すると、「行」の位置が「7」文字目などとわかります。D3セルの式をD4以降にもコピーします。

=MID(A3,D3+1,10)
文字列 開始位置 文字数
A列の銀行名 /「行」の次の文字以降を取り出す/すべてを取り出すのに十分な数

「行」が何文字目かわかったら、その位置までを左から切り出せば「銀行名」となります。これにはLEFT関数を使います。また、「行」の次の文字以降を取り出せば「支店名」となります。それにはMID関数の引数「開始位置」を「行」の次の文字(D3+1)とし、支店名に十分な数の文字数(例えば「10」)を取り出します。

7.「全角⇔半角」変換ASC 関数、全角変換 JIS、大文字変換 UPPER

文字列を半角に変換するには、ASC(アスキー)関数を使います。文字列の入ったセルを引数に指定するだけです。全角に変換するには JIS(ジス)関数、大文字に変換するのが UPPER(アッパー)です。

電話番号の数字半角(H)を全角にするには、Hの隣に空白列を挿入します。JIS関数を使えば、半角文字を全角に、ASC関数を使えば全角を半角にできます。ここではI列を作業に使い、I6セルに「=JIS(H6)」と式を入力、I10までコピーすればすべて半角に統一できます。

=JIS(H6)
文字列

UPPER 小文字を大文字に変換
LOWER 大文字を小文字に変換
PROPER 英字の先頭文字だけを大文字に、2文字目以降を小文字に変換

8.PHONETIC 関数でカタカナをひらがなへ変換

カタカナから平仮名変換専用の関数はありませんが、平仮名に変換できる裏技があります。漢字の”ふりがな”を表示する PHONETIC(フォネティック)関数を応用するのです。セルに入力したカタカナを対象に、PHONETIC 関数の式を立てると、普通は同じカタカナで”ふりがな”が表示されます。

=PHONETIC(D5)

E5をコピーするとところどころ変換できない箇所がありました。こうした場合、修正をする場合は参照元セルを選択し、「ホーム」タブ→ふりがなの表示・非表示の横にある「▼」>「ふりがなの編集」をクリックします。すると漢字の上にフリガナが表示されるので Enter を押すと自動的にE列に表示されます。

さて、元データに指定したカタカナを選択し「ホーム」メニューから”ふりがな”の表示方法を「ひらがな」に変えると、PHONETIC 関数の結果も「ひらがな」になります。

この仕組みを利用すれば、カタカナを平仮名に変換できるわけです。ただし、この関数を使って分割や変換をした結果は、そのままでは単なる計算結果なので、元データを削除するとエラーになってしまいます。そこで、「形式を選択して貼り付け」機能を使い、見た目通りの文字列に置き換える必要があります。

9.日付の表示形式や計算を自由自在

日付の表示方法を変えるには、セルを右クリックし、「セルの書式設定」メニューを選びます。

ポイントは「ユーザー定義」の表示形式です。「曜日」を自動で表示したい場合は、月「m」、日「d」、そのあと「aaa」を入力することでOKです。元号を表示するには「m」の前に「ggge」を入力します。

ちなみに、「今日の日付」を自動で入力するには、「Ctrl + ;」、現在の時刻を自動表示するには「Ctrl + :」です。

10. 「前月」「翌月1日」はMONTH 関数で計算

=MONTH(G1)
シリアル値
発行日の日付

G1に発行日「令和6年3月14日」と入力するだけで、「3月度ご請求内容」と表示されるようにします。

同じ月の「月」を表示するなら、「表示形式」の利用で「発行日と同じ日付を転記し、その”月の数字”だけを表示すれば簡単にできます。

まず、「月」の数字を表示したいセルに、発行日の日付を転記します。セルの表示形式を「ユーザー定義」とし、書式記号を「m」とだけ指定すると、3月なら「3」と”月の数字”のみが表示されます。

一方、「発行日の前月」を表示するときは、例えば3月なら「3」から「1」を引くだけです。

=MONTH(G1)-1
発行日の日付(ここでは3)から「1」を引いて前月にする

11.YEAR 関数は「年」、DATE 関数は「日付データ」

発行日から見た「翌月13日」を自動で算出し、表示できるようにするには

「年」「月」「日」を”分解”して計算するのがコツです。

今日の日付        2024  /  3 / 14
             ↓YEAR関数 ↓MONTH関数

「年」と「月」を取り出す 2024    3
             ↓同じ年  ↓+1

翌月1日の「年」「月」「日」2024   4   1
日付データに戻す     2024 /  4 / 1

まず、YEAR 関数とMONTH 関数で発行日の「年」「月」を取り出します。この「月」に「1」を加え「日」を「1」日とすれば、「翌月1日」になります。こうして求めた「翌月1日」の「年」「月」「日」を、DATE 関数で日付データに戻します。

12.DATE 関数 =DATE(年,月,日)

=DATE(YEAR(G1),MONTH(G1)+1,1)
            
   同じ年 「1」を足して翌日に 1日

「今日」や「現在時刻」を常に表示

=TODAY() 発行日欄に「今日の日付」や
=NOW() 「現在の時刻」を自動表示するために
TODAY関数やNOW関数を使うのも手です。「=TODAY()」「=NOW()」と式を立てればよく、引数は不要です。

13.WEEKDAY 日付の「曜日」を調べる

A3セルに日付が日曜かどうかは、「WEEKDAY(A3)=1が成り立つかを調べます。これを IF 関数の引数「論理式」に指定し、成り立つ場合は予定を表示、それ以外は空欄(ダブルクォーテーション2つで指定)にします。

=IF(WEEKDAY(A3)=1,”ポイント15%還元
!”,””)

           論理式      真の場合     偽の場合
         日付が日曜日である  予定を表示    セルを空欄に
         (曜日番号が「1」

B3セルに立てた IF 関数式をコピーします。すると、A列の日付が日曜のときだけ、予定が表示されます。それ以外は空欄になります。

何なりとお申し付けください。