パソコンソフトは多数存在しますが、仕事用となればエクセル以外に思い浮かびません。
シートのセルに名前を書き並べば「名簿」ができあがり、売上金額を記載すれば「報告書」の一丁上がり。文書作成から作表まで、何から何までカバーする優れものです。
でも、「関数」という便利な道具を使うとなると、目的のデータが中々取り出せない。「関数は難しくてちょっと手が出ない!」使いこなすには相当な覚悟が必要だと悩みを抱えていませんか?
そこで、関数とはどんなものか、どうやって覚えるのか、なぜ仕事の効率があがるのか。基礎から実践へ、確実に関数を活用するのが今日の学習です。
- 1.関数はイイコトだらけ 計算が楽
- 1.関数とはどんなもんか
- 2.どうやって覚えるか
- 3.なんで仕事の効率が上がるんか
- 4.とりあえず5つの関数
- 5.IF 関数でメニューを臨機応変に活用
- 6.VLOOKUP 関数で顧客番号を基に個人情報を表示
- 7.SUMIF 関数で追加データも自動集計
- 8.COUNTIF で個数の集計、「列全体」を数え、追加分も算入
- 9.オートフィルタで抽出条件を「リスト」から選択
- 10.FIND,LEFT,MID 関数で文字の分割
- 11.「全角⇔半角」変換ASC 関数、全角変換 JIS、大文字変換 UPPER
- 12.PHONETIC 関数でカタカナをひらがなへ変換
- 13.日付の表示形式や計算を自由自在
- 14. 「前月」「翌月1日」はMONTH 関数で計算
- 15.YEAR 関数は「年」、DATE 関数は「日付データ」
- 16.DATE 関数 =DATE(年,月,日)
- 17.WEEKDAY 日付の「曜日」を調べる
- 関連
1.関数はイイコトだらけ 計算が楽
1.関数とはどんなもんか
関数っちゅうのは、プログラムの中でよく使う処理をひとまとめにしておく仕組みや。例えば、たこ焼きを焼くのと同じ手順を何回もやるんやったら、その手順を関数にまとめて、呼び出すだけでその手順が実行できるようにするんや。
public class Main {
public static void main(String[] args) {
sayHello("たこ焼き太郎");
sayHello("お好み焼き次郎");
}
// これが関数や。挨拶をする関数やな。
public static void sayHello(String name) {
System.out.println("まいど、" + name + "さん!");
}
}
この例では、sayHello
っちゅう関数が挨拶をするための処理をまとめとるんや。
2.どうやって覚えるか
関数を覚えるには、まず基本の形を理解することや。Javaでは、関数はこんな風に定義するんや:
// 戻り値の型 関数名(引数の型 引数名) {
// 関数の中で行う処理
// return 戻り値;
// }
例えば、足し算をする関数を作るとこうなる:
public static int add(int a, int b) {
return a + b;
}
実際に手を動かして、色んな関数を作ってみるんが一番の勉強やで。たこ焼き何回も焼いてるうちにコツが掴めるんと同じや。
3.なんで仕事の効率が上がるんか
関数を使うと、仕事の効率がめっちゃ上がるんや。理由は以下の通りやで:
- 再利用性:
- 一度作った関数は、何回でも使える。たこ焼きの生地作るのと同じで、一回作ったら何回でも焼けるやろ?
- 可読性:
- 関数を使うと、コードが見やすくなる。長いレシピを分かりやすく小分けにするみたいなもんや。
- 保守性:
- エラーを見つけたときに、関数だけ直せば全部に反映される。例えば、たこ焼きの焼き方に問題があったら、焼き方を直せば全部のたこ焼きが美味しくなる。
関数はプログラムをシンプルにして、再利用性と保守性を上げるためのもんや。手を動かして色んな関数を作ってみることが、一番の勉強法や。関数を使いこなせるようになったら、プログラミングの効率もグンと上がるで!
4.とりあえず5つの関数
例えば、店舗の売り上げ実績表で「合計売上」や「平均売上」を調べる際、「合計はSUM(サム)関数、平均は AVERAGE(アベレージ)関数で求められる」とすぐに思い浮かべる人も多いかもしれません。
- SUM 関数:セルの合計値を求めるために使用されます。例えば、
=SUM(A1:A10)
と入力すると、A1からA10までのセルの合計値が表示されます。 - AVERAGE 関数:セルの平均値を求めるために使用されます。例えば、
=AVERAGE(A1:A10)
と入力すると、A1からA10までのセルの平均値が表示されます。 - MAX 関数:セルの最大値を求めるために使用されます。例えば、
=MAX(A1:A10)
と入力すると、A1からA10までのセルの最大値が表示されます。 - MIN 関数:セルの最小値を求めるために使用されます。例えば、
=MIN(A1:A10)
と入力すると、A1からA10までのセルの最小値が表示されます。 - COUNT 関数:セル内にある値の数を数えるために使用されます。例えば、
=COUNT(A1:A10)
と入力すると、A1からA10までのセル内にある値の数が表示されます。
集計・順位はこれでOKです。
「合計や平均は、SUM や AVERAGE 関数を使えば簡単だ」と思われる人が多いかもしれませんが、ところがこれらの関数を使わなくても「合計」や「平均」を字自動計算する機能があります。
「ちょっと合計が見たい」というだけなら、数値の入ったセルをドラグして選択するだけで、ステータスバーに合計が自動算出でます。
「平均」もメニューで自動算出
それにはステータスバー上を右クリックすると自動的にでます。最大値や最小値、その他あらゆる数値が自動的に出ます。
「オートカルク」機能で離れた範囲を集計するには、1つ目の範囲を選択後、2つ目の範囲を「Ctrl」キーを押しながらドラグすればいいです。
手早く数値を集計できるのは「オートカルク」の利点ですが、集計結果を単に表示するだけで、数値をセルに保存できません。つまり一時的に確認するための機能なのです。
最大値を求めるのが「MAX」、最小値を求めるのが「MIN(ミン)」です。いずれの関数も、引数として数値データが入力されているセル範囲を指定します。セル範囲(G4~G19)を引数に指定して、「=MAX(G4:G19)」とすればいいです。
以上は基本的な集計と順位判定の例ですが、実務で必要となるさまざまな計算、データ処理についても、エクセルは「メニュー」と「関数」の両方を用意していることが多いです。
5.IF 関数でメニューを臨機応変に活用
計算式は不要で、マウス操作だけで集計が可能なのが「ピボットテーブル」です。これを使えば、メニューを選択後、集計したい項目を選んでいくだけで、あっという間に、「費目別の合計」や「男女別の人数」、さらには「平均年齢」などを求めることができます。
ただし、ピボットテーブルも、日々データが増えていく表では、新たに入力したデータを追加するために、いちいちメニューを追加するために、いちいちメニューを選択し、集計範囲を設定しなおさなければなりません。
- IF 関数:条件式に基づいて、特定のセルに値を返すために使用されます。例えば、
=IF(G4>=80,"合格","不合格")
と入力すると、G4が80より大きい場合は “合計” と表示され、そうでない場合は “不合格” と表示されます。
セルに表示したい値が文字列の場合は、「”」ダブルコーテーションで挟んで指定する決まりになっています。I4 で完成した式を下方向にへコピーすれば、全員分の合否を即座に表示できます。
6.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とすれば「英語」をおなじようにとりだせます。
「顧客データが別シートにある」という場合も、同じです。関数の引数「範囲」に、別シートの生徒データを指定するだけです。それには、シート名の後ろに「!」記号を付け、続けてセルは飲位を指定します。
条件付きで合計・平均・件数を算出するピボットテーブル
エクセルには「ピポットテーブル」という強力な”自動集計機能”があります。
- PivotTable:大量のデータを集計し、要約するために使用されます。データを選択し、PivotTableを作成することで、数値の合計、平均値、最大値、最小値などを一覧表示することができます。
これを使えば、メニューを選択後、集計したい項目を選んでいくだけで、あっという間に「費目別の合計」や「男女別の人数」、あれには「平均年齢」などをもとめることができます。計算式は不要で、マウス操作だけで集計できます。
これらはExcelで一般的に使用される計算や集計の例の一部ですが、多くの他の方法があります。Excelの公式ドキュメントやオンラインのチュートリアルなどから学ぶことができます。
具体的には
データ数や平均値も自動集計
続いて、顧客名簿などで、「男女別の人数を数えたり、「平均年齢」を調べてみましょう。
こうした「個数」の計算や「平均」の算出も、実は「ピボットテーブル」を使って行えます。まずは「ピボットテーブル」のメニューからウィザードを呼び出し、元データの範囲や集計者の作成場所を指定、ポケットテーブルの”枠組み”を用意します。
「男女別の人数」を数えるには
左端の枠に「性別」の項目をドラグまたはチェック。データの個数を数える場合、右側の集計欄には、どの項目をドラグまたはチェックしてもOKです。元表に「空欄」がなければ、どの項目でデータを数えても同じだからです。例えば「年齢」を集計表にドラグすると、まずは「年齢」が「合計」されます。
「行のフィールド」欄には「年齢(数)」とドラグまたはチェック。
「データアイテム」欄には「氏名」をドラグまたはチェック。数値以外の項目をドラグすると、自動的に「個数」の集計になります。
この集計内容は、後から変更ができて、「合計」ではなく「個数」の集計に変えれば、男女別の人数が求められます。具体的には、集計欄を右クリックして「フィールドの設定」を選び、開く画面の「集計の方法」欄で、「データの個数」を選べばいいです。
メニュー操作で集計表が作れる「ピボットテーブル」は、確かに使いやすい機能ですが、家計簿や顧客名簿など、常にデータが増えていく表の場合は「関数」の方がいいです。いったん式を立てておけば、データを追加したときに自動で再計算し、集計結果を更新できるからです。
7.SUMIF 関数で追加データも自動集計
例えば「費目別の合計」を求めてみます。
「範囲内」で指定した「検索条件」に合うデータを探し、見つかったセルに対応する位置にある「合計範囲」内のセルを合計します。「検索条件」には、条件を表す式や探す文字列を「””」ダブルコーテーションで挟んで指定します。式や文字列の入ったセルを、セル番地で指定してもいいです。
図のように、複数の費目がある中で、特定の費目だけを合計したい場合、SUMIF (サムイフ)関数を使います。
SUMIF 関数は、引数に「範囲、検索条件、合計範囲」を指定すると、「範囲」内で「検索条件に合うデータを探し、そのセルと同じ行にある「合計範囲」のセルの値を合計します。
例えば、A列の「食器」の金額だけを合計したい場合、引数「検索条件」には「食器」と入力した I9 セル、「範囲」には費目のセル範囲の A8~A50セル、「合計範囲」は合計金額が入力されたG8~G50セルを指定します。
=SUMIF(A8:A50,I9,G8:G50)という式を立てればいいわけです。こうすると食器だけの金額を合計できます。
おなじように他の費目の合計を計算するには、「範囲」と「合計範囲」のセルをそれぞれ F4 キーを押して絶対参照にして「$」をつけてからコピーします。するとコピーすると「検索条件」に指定したI列のセル範囲だけが変わり、「範囲」と「合計範囲」のセル範囲は固定できます。
8.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&”*”」と指定すればいいです。こうした条件での集計は「ピボットテーブル」ではできません。
9.オートフィルタで抽出条件を「リスト」から選択
関数を利用するまでもなく、「オートフィル」を使えばすむこともあります。
アンケート結果を「白ワイン」の顧客だけ取り込みたい・・・そんなとき便利なのが「オートフィルメニュー」です。表を選択して「データ」→「フィルタ」→「オートフィルタ」を選ぶと、表の1行目に、「▼」ボタンがつく。これをクリックすると、その列にある文字列がリスト表示されるので、抽出の条件にしたいものを選びます。
すると、選択した文字列を含むデータだけが、行単位で抽出されます。「白ワイン」で「女性」の顧客だけに絞り込みたい」というように、複数の条件を組み合わせることもできます。
45歳以上50歳以下の顧客を抽出するには、「以上」「45」、「以下」「50」という条件を指定して、絞り込む必要があります。年齢欄につく「▼」ボタンのリストから、「数値フィルター」→「ユーザー設定フィルター」を選びます。
すると開く画面で、詳細な抽出条件を指定できます。同じように、「お住まい」列で「大阪府」「を含む」という条件を指定すれば、「大阪府在住の顧客」の抽出もできます。
オートフィル(AutoFill)とオートフィルタ(AutoFilter)は異なる機能です。それぞれの役割と用途について説明します。
オートフィル(AutoFill)
オートフィルは、ExcelやGoogleスプレッドシートなどのスプレッドシートソフトで使われる機能で、セルの内容を自動的に連続して入力するためのものです。例えば、セルに「1」と入力し、そのセルを選択した状態で右下のハンドルをドラッグすると、「2, 3, 4, …」といった連続した数値が自動的に入力されます。オートフィルは数値の連番だけでなく、曜日や月などのパターンも認識して自動入力することができます。
オートフィルタ(AutoFilter)
オートフィルタは、スプレッドシートやデータベースのデータを絞り込むための機能です。テーブルの列見出しにフィルタオプションが追加され、ユーザーは特定の条件に一致するデータだけを表示することができます。例えば、「売上」が1000以上の行だけを表示したり、「担当者」が「田中」の行だけを表示したりすることが可能です。オートフィルタを使うことで、大量のデータの中から必要な情報を素早く見つけ出すことができます。
まとめると、オートフィルはセルの内容を自動的に連続入力する機能であり、オートフィルタはデータを絞り込んで表示するための機能です。目的や用途が異なるため、同じものではありません。
二重に登録した顧客データを削除したい
こういう場合も、メニューで処理できます。
まず、表を選択し「データ」→「詳細設定」を選びます。リスト範囲に”重複の判定に使う列”を指定し、例えば「性別」と「お住まい」の両方が一致した場合だけ重複とみなすとしたければ、「性別」と「お住まい」の2列をドラグして指定すればいいです。
あとは「重複するレコードは無視しる」にチェックして「OK」を押せば、「性別」と「お住まい」が重複するデータだけが削除されます。
ただ”削除”とはいっても、実際はその行が”非表示”になっているだけです。完全に削除した表に作り直すには、作業結果をコピーして、別シートに貼り付けましょう。
10.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」)を取り出します。
11.「全角⇔半角」変換ASC 関数、全角変換 JIS、大文字変換 UPPER
文字列を半角に変換するには、ASC(アスキー)関数を使います。文字列の入ったセルを引数に指定するだけです。全角に変換するには JIS(ジス)関数、大文字に変換するのが UPPER(アッパー)です。
電話番号の数字半角(H)を全角にするには、Hの隣に空白列を挿入します。JIS関数を使えば、半角文字を全角に、ASC関数を使えば全角を半角にできます。ここではI列を作業に使い、I6セルに「=JIS(H6)」と式を入力、I10までコピーすればすべて半角に統一できます。
=JIS(H6)
文字列
UPPER 小文字を大文字に変換
LOWER 大文字を小文字に変換
PROPER 英字の先頭文字だけを大文字に、2文字目以降を小文字に変換
12.PHONETIC 関数でカタカナをひらがなへ変換
カタカナから平仮名変換専用の関数はありませんが、平仮名に変換できる裏技があります。漢字の”ふりがな”を表示する PHONETIC(フォネティック)関数を応用するのです。セルに入力したカタカナを対象に、PHONETIC 関数の式を立てると、普通は同じカタカナで”ふりがな”が表示されます。
=PHONETIC(D5)
E5をコピーするとところどころ変換できない箇所がありました。こうした場合、修正をする場合は参照元セルを選択し、「ホーム」タブ→ふりがなの表示・非表示の横にある「▼」>「ふりがなの編集」をクリックします。すると漢字の上にフリガナが表示されるので Enter を押すと自動的にE列に表示されます。
さて、元データに指定したカタカナを選択し「ホーム」メニューから”ふりがな”の表示方法を「ひらがな」に変えると、PHONETIC 関数の結果も「ひらがな」になります。
この仕組みを利用すれば、カタカナを平仮名に変換できるわけです。ただし、この関数を使って分割や変換をした結果は、そのままでは単なる計算結果なので、元データを削除するとエラーになってしまいます。そこで、「形式を選択して貼り付け」機能を使い、見た目通りの文字列に置き換える必要があります。
13.日付の表示形式や計算を自由自在
日付の表示方法を変えるには、セルを右クリックし、「セルの書式設定」メニューを選びます。
ポイントは「ユーザー定義」の表示形式です。「曜日」を自動で表示したい場合は、月「m」、日「d」、そのあと「aaa」を入力することでOKです。元号を表示するには「m」の前に「ggge」を入力します。
ちなみに、「今日の日付」を自動で入力するには、「Ctrl + ;」、現在の時刻を自動表示するには「Ctrl + :」です。
14. 「前月」「翌月1日」はMONTH 関数で計算
=MONTH(G1)
シリアル値
発行日の日付
G1に発行日「令和6年3月14日」と入力するだけで、「3月度ご請求内容」と表示されるようにします。
同じ月の「月」を表示するなら、「表示形式」の利用で「発行日と同じ日付を転記し、その”月の数字”だけを表示すれば簡単にできます。
まず、「月」の数字を表示したいセルに、発行日の日付を転記します。セルの表示形式を「ユーザー定義」とし、書式記号を「m」とだけ指定すると、3月なら「3」と”月の数字”のみが表示されます。
一方、「発行日の前月」を表示するときは、例えば3月なら「3」から「1」を引くだけです。
=MONTH(G1)-1
発行日の日付(ここでは3)から「1」を引いて前月にする
15.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 関数で日付データに戻します。
16.DATE 関数 =DATE(年,月,日)
=DATE(YEAR(G1),MONTH(G1)+1,1)
年 月 日
同じ年 「1」を足して翌日に 1日
「今日」や「現在時刻」を常に表示
=TODAY() 発行日欄に「今日の日付」や
=NOW() 「現在の時刻」を自動表示するために
TODAY関数やNOW関数を使うのも手です。「=TODAY()」「=NOW()」と式を立てればよく、引数は不要です。
17.WEEKDAY 日付の「曜日」を調べる
A3セルに日付が日曜かどうかは、「WEEKDAY(A3)=1が成り立つかを調べます。これを IF 関数の引数「論理式」に指定し、成り立つ場合は予定を表示、それ以外は空欄(ダブルクォーテーション2つで指定)にします。
=IF(WEEKDAY(A3)=1,”ポイント15%還元
!”,””)
論理式 真の場合 偽の場合
日付が日曜日である 予定を表示 セルを空欄に
(曜日番号が「1」
B3セルに立てた IF 関数式をコピーします。すると、A列の日付が日曜のときだけ、予定が表示されます。それ以外は空欄になります。
何なりとお申し付けください。