経理・税理士業務で使っている28の関数。関数よりもピボットテーブル

Excelの関数を覚えなきゃ！と思ってらっしゃる方も多いでしょう。
私はそれほど多くの関数を使っていません。
どんな関数をつかっているかをまとめてみました。

 

Excel関数は400以上ある

数え方、バージョンによっても異なりますが、Excelの関数は400以上あります。
もちろんこれをすべて覚えなければいけないことはありません。
必要なものだけ覚えればいいのです。

特に経理業務、税理士業務でいえば、その数は限られます。

 

 

ピボットテーブルを使えば関数の出番は激減する

それほど多くの関数を使わなくてもいいのは、ピボットテーブルを使うからです。
これがあれば、SUMすらいりません。

このようなデータで、ピボットテーブルを使えば、

一瞬で合計を出せますし、

月別の集計もできます。
これを関数でやるなら、SUMIFを使わなければいけません。

月別、担当者別集計もピボットテーブルなら簡単です。
関数ならSUMIFSを使います。

関数を使えるけどもっと簡単にできる方法がExcelにはあるのです。
その1つがピボットテーブルですので、必須のスキルといえます。
■Excelピボットテーブル | EX-IT

 

 

 

Excel関数だけの本だと、関数だけを紹介しなければいけませんので、どうしても難しくなるでしょう。
関数だけの本はそれほど気にしなくて大丈夫です。

私が使っているExcel関数

私が使っているExcel関数は次の28個です。
VLOOKUP以外は思いついた順にしています。

1　VLOOKUP

最重要関数VLOOKUP。1番にもってきました。
「関数を1つしか使ってはいけない」といわれたら間違いなくこれを選びます。

・現金出納帳の摘要から、科目をつけるとき
・会計ソフトからエクスポートした試算表、推移表から、Excelで資料を作るとき
・所得税、法人税の計算で税額表から金額を参照して計算するとき
など様々な場面で使えます。

■Excel VLOOKUP関数 | EX-IT

 

 

 

2　SUM

合計する関数。基本中の基本です。
Alt＋Shift＋［-］というショートカットキーを使うとすばやく入力できます。
Excelのしくみを理解するために、SUMを直接入力してみよう | EX-IT

 

 

3　IF

もし○○だったら、△△という条件別処理ができます。
たとえば、Excel現金出納帳から、会計ソフトへの取込データを作る場合、「もし入金欄に金額があったら、借方科目は”現金”。支払欄に金額があったら、借方科目は、”会議費”」と表示するといったときに使います。

4　ROUNDDOWN

端数を切り捨てる関数。
税金の計算時に、千円未満切り捨て、百円未満切り捨てをするときに使います。

ROUND、ROUNDUPはそれほど使いません。

5　INT

消費税の端数切り捨てはINTを使っています。
=INT（a1*8/108)
の方が、
=ROUNDDOWN(a1*8/108,0)
よりも短くて速く入力できるからです。

ただし、マイナスのときは、結果が異なります。
-1000.2をINTで処理すると、-1001。ROUNDDOWNで処理すると、-1000になります。
通常、金額を処理する場合にはINTで問題ないはずです。

6　IFERROR

マニアックな関数ですが、VLOOKUPとの組み合わせで欠かせません。
エラーが出たときの処理をするもので、たとえば、

=VLOOKUP(A1,F:G,2,FALSE)
でエラーの場合、0を表示するなら、
=IFERROR（VLOOKUP(A1,F:G,2,FALSE)、0）
と入れます。

7　SUMIF

名称のとおり、IF（〜の場合）とSUM（合計する）の両方の処理をしてくれるものです。
ピボットテーブルを使った方が便利なのですが、それでもSUMIFを使う場面はあります。
リアルタイムに合計値を変えたい場合、表の中で合計する場合です。

8　DATE

日付を表示する関数です。
今日なら、
=DATE(2014,11,4）
と書きます。
A列からC列に、それぞれ年、月、日が入っていれば、こう処理できるのです。

9　YEAR

日付から年を取り出します。

10　MONTH

日付から月を取り出します。

11　DAY

日付から日を取り出します。

12　LEFT

文字列の左から○番目を取り出します
セルA1に20141104とあった場合で、
=LEFT(A1,4)
なら年を取り出せます。

13　MID

文字列の○番目から△文字を取り出します

14　RIGHT

文字列の右から○番目を取り出します

15　FIND

特定の文字を見つけてその位置を数値にします。
たとえば、「スタバ　打ち合わせ」という摘要から、「スタバ」だけ取り出せるのです。
「スタバ」と「打ち合わせ」の間に全角スペースが入っていることが条件となります。

その他余計なデータが入っているときに使います。
（区切り位置という機能を使っても大丈夫です）

16　COLUMN

列を数値で返します。
私が使っているのは会計ソフトからエクスポートした推移表から資料を作るときです。
シート「会計データ」のA列からZ列に推移表がある場合、
=VLOOKUP(A1,会計データ!$A:$Z,COLUMN（B2）,FALSE）
とすれば、コピーしても数式が崩れず正しく表示されます。

17　ROW

行を数値で返します。

18　HLOOKUP

VLOOKUPが縦（Vertical）に数値を探すのに対し、HLOOKUPは、横（Horizontally）に数値を探します。
データの作り方としては好ましくありませんが、特定の場合に使います。

19　CONCATENATE

文字や数値を結合します。
「＆」でも代用できますが、3つ以上だと、”=CONCATENATE（”と入力して、Ctrlキーを押しながら選択すると入力が楽です。
間に、スペースやハイフンなどを入れることもできます。

20　AND

IFと組み合わせて使います。
もし、AかつBなら、○○するといったときです。

21　OR

IFと組み合わせて使います。
もし、A又はBなら、○○するといったときです。

22　SUBSTITUTE

文字を置き換えます。

23　VALUE

文字を数値として処理します。
22のSUBSTITUTEと使うなら、「1000円」と入力されたものを「1,000」として処理する場合、こういった関数を入れます。

24　TRIM

セルの中の空白を取り除きます。文字の間に空白があるなら、REPLACEを使います。
空白があると、VLOOKUPがうまく動かない場合があるので、必須です。

25　ASC

全角を半角に変えます。
会計ソフトのデータを取り出して、処理した後、再度会計ソフトに戻すこともできます。

26　JIS

半角を全角に変えます。

27　MIN

最小値を出す関数です。
たとえば、法人税を計算する場合で、A1に所得金額が入っていれば、
=MIN(8000000,A1）×15％
で税率15％分の金額が計算されます。
800万円とA1のどちらか小さい方、つまり800万円までの金額という意味です。
（厳密には月割りしますが）

28　ROUND

雇用保険料の計算や預金利息の復興所得税分で使っています。
【復興特別所得税導入後の預金利息】対策Excelーサンプルファイルをダウンロードできますー | EX-IT

 

 

 

 

まとめ

これも使っているよ！というものもあるかもしれませんが、さっと思いつくのは以上の28です。
関数はこれくらい覚えておけば十分ですので、あとはピボットテーブルを覚えましょう。

Excel入門セミナーでは、徹底的にVLOOKUPとピボットテーブルをやります。
この本で紹介しているのも、主に、VLOOKUP、IF、SUMです。

---

---

---

---

【編集後記】
昨日のマラソンでチームメイトが3時間半切り（サブ3.5）を達成しました。
ネットでタイムをみながら、ドキドキしていました。
ザック、おめでとう！

タイムを見るとかなりのペースです。
（私のベストは、3時間42分58秒なので・・・）
あと13分を今シーズン縮められるかどうか・・。
練習時のザックとのスピード差から考えると結構厳しいのですが狙っていきます。
（まずは、5ヶ月連続フルマラソンで5連続サブフォー、4時間切りを狙っています）

【昨日の1日1新】
※詳細は→「1日1新」

Moneytree　有料版
スタバ　ミルクシフォンケーキ