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

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

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

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

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

１．関数はイイコトだらけ　計算が楽

１．関数とはどんなもんか

関数っちゅうのは、プログラムの中でよく使う処理をひとまとめにしておく仕組みや。例えば、たこ焼きを焼くのと同じ手順を何回もやるんやったら、その手順を関数にまとめて、呼び出すだけでその手順が実行できるようにするんや。

public class Main {
    public static void main(String[] args) {
        sayHello("たこ焼き太郎");
        sayHello("お好み焼き次郎");
    }

    // これが関数や。挨拶をする関数やな。
    public static void sayHello(String name) {
        System.out.println("まいど、" + name + "さん！");
    }
}

この例では、sayHelloっちゅう関数が挨拶をするための処理をまとめとるんや。

２．どうやって覚えるか

関数を覚えるには、まず基本の形を理解することや。Javaでは、関数はこんな風に定義するんや：

// 戻り値の型 関数名(引数の型 引数名) {
    // 関数の中で行う処理
    // return 戻り値;
// }

例えば、足し算をする関数を作るとこうなる：

public static int add(int a, int b) {
    return a + b;
}

実際に手を動かして、色んな関数を作ってみるんが一番の勉強やで。たこ焼き何回も焼いてるうちにコツが掴めるんと同じや。

３．なんで仕事の効率が上がるんか

関数を使うと、仕事の効率がめっちゃ上がるんや。理由は以下の通りやで：

1. 再利用性：
   • 一度作った関数は、何回でも使える。たこ焼きの生地作るのと同じで、一回作ったら何回でも焼けるやろ？
2. 可読性：
   • 関数を使うと、コードが見やすくなる。長いレシピを分かりやすく小分けにするみたいなもんや。
3. 保守性：
   • エラーを見つけたときに、関数だけ直せば全部に反映される。例えば、たこ焼きの焼き方に問題があったら、焼き方を直せば全部のたこ焼きが美味しくなる。

関数はプログラムをシンプルにして、再利用性と保守性を上げるためのもんや。手を動かして色んな関数を作ってみることが、一番の勉強法や。関数を使いこなせるようになったら、プログラミングの効率もグンと上がるで！

４．とりあえず５つの関数

例えば、店舗の売り上げ実績表で「合計売上」や「平均売上」を調べる際、「合計は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までのセル内にある値の数が表示されます。

集計・順位はこれでOKです。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

具体的には

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

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

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

「男女別の人数」を数えるには

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

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

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

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

７．SUMIF 関数で追加データも自動集計　

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

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

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

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

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

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

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

８．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セルが「文京区」なら→”文京区”という文字列に→「文京区」で始まる住所を教える。

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

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

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

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

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

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

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

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

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

二重に登録した顧客データを削除したい

こういう場合も、メニューで処理できます。

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

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

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

１０．FIND,LEFT,MID 関数で文字の分割

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

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

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

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

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

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

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

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

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

=JIS(H6)
文字列

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

１２．PHONETIC 関数でカタカナをひらがなへ変換

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

=PHONETIC(D5)

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

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

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

１３．日付の表示形式や計算を自由自在

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

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

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

１４． 「前月」「翌月1日」はMONTH 関数で計算

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

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

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

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

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

＝MONTH（G1）-1
発行日の日付（ここでは３）から「１」を引いて前月にする

１５．YEAR 関数は「年」、DATE 関数は「日付データ」

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

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

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

「年」と「月」を取り出す　2024　　　　3
　　　　　　　　　　　　　↓同じ年　　↓＋１

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

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

１６．DATE 関数　＝DATE（年,月,日）

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

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

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

１７．WEEKDAY 日付の「曜日」を調べる

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

＝IF(WEEKDAY(A3)=1,”ポイント15％還元
！”,””)

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

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