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

Excelの関数を覚えなきゃ!と思ってらっしゃる方も多いでしょう。
私はそれほど多くの関数を使っていません。
どんな関数をつかっているかをまとめてみました。
スクリーンショット 2014 11 04 8 08 57

Excel関数は400以上ある

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

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

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

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

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

スクリーンショット 2014 11 04 7 55 58

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

スクリーンショット 2014 11 04 7 56 15

月別の集計もできます。
これを関数でやるなら、SUMIFを使わなければいけません。
スクリーンショット 2014 11 04 7 56 28


■スポンサードリンク
------※この記事は、投稿日現在の状況、心境、法律に基づいて書いています。---------

■スポンサードリンク


月別、担当者別集計もピボットテーブルなら簡単です。
関数ならSUMIFSを使います。
スクリーンショット 2014 11 04 7 56 51

関数を使えるけどもっと簡単にできる方法が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現金出納帳から、会計ソフトへの取込データを作る場合、「もし入金欄に金額があったら、借方科目は”現金”。支払欄に金額があったら、借方科目は、”会議費”」と表示するといったときに使います。

スクリーンショット 2014 11 04 8 15 17

■スポンサードリンク

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列に、それぞれ年、月、日が入っていれば、こう処理できるのです。
スクリーンショット 2014 11 04 8 39 43

9 YEAR

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

10 MONTH

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

11 DAY

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

12 LEFT

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

13 MID

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

14 RIGHT

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

15 FIND

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

スクリーンショット 2014 11 04 8 59 53
その他余計なデータが入っているときに使います。
(区切り位置という機能を使っても大丈夫です)

16 COLUMN

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

17 ROW

行を数値で返します。

18 HLOOKUP

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

19 CONCATENATE

文字や数値を結合します。
「&」でも代用できますが、3つ以上だと、”=CONCATENATE(”と入力して、Ctrlキーを押しながら選択すると入力が楽です。
間に、スペースやハイフンなどを入れることもできます。
スクリーンショット 2014 11 04 9 09 33

20 AND

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

21 OR

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

22 SUBSTITUTE

文字を置き換えます。

23 VALUE

文字を数値として処理します。
22のSUBSTITUTEと使うなら、「1000円」と入力されたものを「1,000」として処理する場合、こういった関数を入れます。
スクリーンショット 2014 11 04 9 12 50

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 有料版
スタバ ミルクシフォンケーキ




■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』