Sumifsについて

Sumifs関数は、Sumif関数が「複数の条件」に対応できるようになった関数です。しかし、使用する条件の数（単数か複数か）によって使い分ければ良いのでは、と考える意味はあまりないと思います。使用する条件が単数であろうが複数であろうが、シンプルに、どのような場合でもSumifsのみを使えばいいのです。あえてSumifを使用する積極的な理由は思いつきませんし、エラーを避けるためにもSumifは使わないようにお勧めしています。

これらの二つの関数の違いは「使用する条件の数」が単数か複数か、ということだけではありません。もっと深刻な違いが存在します。それは「引数の順番」です。Sumif関数では「合計範囲」を、最後（3番目）の引数に指定します。しかし、Sumifsでは、「合計範囲」を最初の引数として入力します。

=SUMIF(検索範囲, 検索値, 合計範囲) 

=SUMIFS(合計範囲, 検索範囲1, 検索値1, 検索範囲2, 検索値2, …)

つまり、「合計範囲」を入力する順番が異なるため、関数を使い分けること自体が、リスクとなってしまいます。混乱を避けるためにも、Sumif関数は使わず、Sumifs関数のみを使うようにお勧めします。

そして、Sumifs関数をお勧めする理由として、より大切なことがあります。それは、多くの場合「複数の軸」で切り分けたデータを見ることが非常に大切だからです。最も代表的な例はクロス集計でしょう。

上記の例では、もし「商品別」という一つの軸のみでデータを見るのであれば、商品別の売上の違いしか観察できません。複数の軸で見るのであれば、（ありきたりな例ですが）例えば「商品別の売上」における「性別間」の差を比較することができます。条件を追加すれば、「男性のみ」のデータで商品別における地域間の差を比べる、など、より踏み込んだ集計ができるようになります。

データを集計・分析する上でまず大切な処理は、意義のある比較ができるようにデータを切り分ける、ということです。そのためにも、複数の軸で切り分けることを常に習慣としなければなりません。初歩的なことに見えますが、以外とそのようなことができていないケースをよく見かけます。業務上求められている処理ではないとしても、それでも積極的にクロス集計を行ってみることにより、新たな気づきを生む機会を多くしたいものです。そのような意味でも、Sumifs関数は絶対に習得しなければなりません。

特に、単数軸のみの単純集計(Sumif)に比べてクロス集計(Sumifs)がより大切だと思う理由は、「数字の大小の比較」と「分布の形の比較」の違いです。仮に以下のような結果が出たとします。

単軸のみの単純集計の場合、観察できることは「軸１のカテゴリー間の数字の大小を比較」です。例えば、「商品Aは商品Bより売り上げが高い」、「地域Aでは地域Bより売り上げが高い」、などです。つまり、数字の大小を比較しているのです。

一方、複数軸（クロス集計）の場合、数字の大小の比較はもちろんなのですが、同時に「分布の形」がカテゴリー間でどのように違うかを視覚的に観察することができます。

数字の大小を見るよりも、分布の形を見比べることのほうが、得られる情報量としては圧倒的に多いですし、その後の議論にも繋がりやすいと思います。

当然のことながら、クロス集計のみで、その後のアクションに繋がるような情報を全て揃えることができるとは思いません。それでも、クロス集計から得られた気づきから、様々な課題を見つけたり、仮説を立て、それらの仮説の検証のためにどのようなデータが必要なのか、という次の議論に繋げることができるのではないでしょうか。

上記のようなクロス集計なら、もちろんピボットテーブルも行うことができますし、むしろピボットテーブルを使った方が簡単に集計できるでしょう。しかし、一度ピボットテーブルを使ってしまうと、その後の複雑な集計や分析をする際に色々と障害が生じます。ピボットテーブルは「単純な集計を楽に処理できる」という点では素晴らしいと思います。しかし、複雑な集計・分析を行う際には、やはり数式を使った関数やその他の機能の方が向いているでしょう。

Vlookupについて

Vlookupは数ある検索・行列関連の関数の一つでしかありません。しかし、多くのエクセル入門書ではVlookup以外の関数が扱われることは少ないように思えます。Vlookup関数がここまで注目されているのは、関数を直観的に理解しやすい、という理由ではないでしょうか。

VLOOKUP 関数 - Office サポート

特に、最後の引数で「検索方法」を指定しますが、0またはFALSEを使った「完全一致」のみの検索方法しか紹介していないエクセル教本が多いです。１またはTRUEを使った「近似一致」の方が、集計や分析においてはるかに有用性が高いと感じる方は少なくないと思いますが、その存在すら知らない方のほうがはるかに多いように感じます。

ここからは「完全一致」に限った話になりますが、多くの経理や財務のエクセル業務において、「Vlookup以外の関数を使用（または併用）した検索をする方法を知っている」ことが大きな強みになります。理由としてはいくつか挙げられますが、よく知られている以下のVlookupの欠点と関連しています。

1. 検索する値より左側にある列の値を戻り値列とすることができない。
2. 「列番号」の引数に直接数字を入力している場合、検索範囲の列の挿入・削除に対応できず、誤った戻り値が返ってくる。
3. 検索指定している値が範囲内に複数ある場合、二つ目以降を返すことができない。
4. 異なる複数の列に対し、複数の条件を同時に指定できない。

特に問題となる項目は、１と２ではないでしょうか。期待している検索ができなかったり、誤った戻り値を返しているようでは、データの信頼性を疑われてしまいます。「検索値は常にシートの一番左側にあるから問題ない」、「検索範囲に列を挿入・消去することは決してないので、問題にはならない」と言う方がいるかもしれません。確かにその通りなのであれば、上記は欠点は問題にはなりません。ただし、「常に」や「決してない」が業務の中で本当に通用するのでしょうか。少なくとも、私は上記の過信から痛い目に遭ったことがあります。また、ブックを複数のユーザーに共有したい場合、想定していない編集はある程度避けられませんし、踏み込んだ集計や分析を行うためには、シートに様々な作業列や追加データを挿入する必要性が生じてきます。

集計の柔軟性を増し、想定外の編集に対して堅固なシートを作る技術は、仕事の効率を上げ、さらに誤った集計のリスクを減らします。長い目で見るときに、必ずデータ処理の質を高めてくれます。

では、実際にどのような関数を用いればよいでしょうか。良く知られている以下の２つの方法です。

一つ目の方法「VlookupとMatchの併用」は、Vlookupの3番目の引数「戻り値を含む列番号」をMatchで取得する方法です。

 = VLOOKUP(検索値, 検索範囲, MATCH関数, 0)

Match関数には参照したい値を含む列の名前を検索値にして、検索値を含む列からの列数を返すようにします。具体的には以下のようになります。

=MATCH(参照列名, 検索範囲, 0)

このようにすることで、検索範囲の列の挿入・削除がある場合にも、引数を自動的に調整してくれるので、誤った戻り値を返すことはなくなります。しかし、この方法では１番目の問題（戻り値が検索列の左側にある場合）は解決しません。

二つ目の方法はIndexとMatchを併用した方法です。

この方法では、参照したい値を含む列と、検索する値を含む列をそれぞれ別に指定します。前述した、左右が逆転している例でその効果を見てみましょう。

=INDEX(参照したい値を含む列, MATCH(検索値, 検索する値を含む列, 0)) 

この方法であれば、検索値と戻り値の左右の位置関係の問題は回避できます。また、戻り値の列を直接参照しているので、列の挿入・削除の影響はありません。

ただし、これらの方法を用いても問題点の３と４はそのまま残ります。これらの問題に対処するには、更なる工夫が必要となります。これらの議題に回答しているサイトをいくつか拝見しましたが、その解決方法の多くが「当目的のための作業列を追加する」または「特定の検索列がソートされている」という条件が必要となるものでした。できれば、参照テーブルはそのままのほうがいいですよね。

詳しい説明は後日紹介できればと思いますが、例えば以下のような方法を取ることもできるでしょう。青色のセルが検索値１と２、赤色のセルが(AND)条件を満たしている参照値を上から並べているものになります。

式は少し長く、また配列関数になりますが、以下のようになります。

{ = INDEX( 参照列, SMALL ( IF( ( 検索列１=検索値１ ) * ( 検索列２=検索値２ ),ROW( 参照列 ) ), 結果番号 ) - ROW( 見出し行のセル ) ,0 ) }

二つの条件に合う参照値は１と16の二つしかありませんので、3番目の結果はエラーになっています。もちろん、必要であればIFERROR関数などでエラー処理を行ってください。

この方法なら、参照範囲に影響を与えずに検索できます。また、条件はいくつでも追加できますし、結果がいくつあっても対応できそうです。必要であれば、「＊」を「＋」に代えることにより、ANDではなく、OR検索（どれかひとつの条件でも揃っていればその行の値を返す）ができます。

はじめに

10年ほど前になりますが、アメリカの大学で会計学を専攻しながら、大学の経理課でアルバイトとして働く機会がありました。第二言語で仕事をするというハンデを少しでもカバーするために、独学でエクセルを学び始めました。大学卒業後には大手監査法人のニューヨークオフィスに採用となり、監査チームの一員となりました。仕事を始めるにあたり大きな不安はありましたが、経理でのアルバイトの経験は監査の仕事をする上で大きな助けとなり、毎年チームや会社から優れた評価をいただくことができました。その後、転職を機に家族と日本に戻り、これまでFP&Aやデータ管理・分析の仕事に携わっています。

正直なところ、私はネイティブと張り合うことができるほど英語に自身があるわけではありません。それでもチームの中で一定以上の評価をいただくことができた理由としては、エクセルを駆使してデータを処理する力を発揮できたからではないかと思っています。そのことは、経理、監査、またFP&Aなど、これまでのどのポジションにも同じことが言えます。

もちろん、エクセルスキルだけが長けていても、業務に対する理解や、その他の基本的な仕事の能力がなければ、社会人として会社にあまり大きな貢献をすることは期待できないでしょう。特に、経理や財務に属しているのであれば、一定レベルのエクセル技術は当たり前のように期待されて、仕事に価値をもたらすために必要な能力は全く別のものです。

しかし、私自身また一緒に仕事をしたスタッフの経験から言うと、エクセルを学び、実際に様々な方法でデータを動かしてみることにより、会社の数字を見る力が著しく向上します。また、それらのデータに対する視野が広がり、より深い洞察力をもたらしてくれます。課題を明確にし、新しいアイディアに繋がる機会を提供してくれます。つまり、エクセル以上にもっと大切な能力や知識を養う機会となります。

ただし、エクセルを深く学ぶということは、マニアックな関数や機能を学ぶという意味では決してありません。何重にもネストした関数を書けるようになることが目的でもありません。シンプルである、ということは非常に大切です。レビューする人や他のユーザーにとって可読性の高いものを作る技術は、どのような場合にも必ず必要になります。その点から言えば、仕事の中ではなるべく複雑な関数や機能の使用は避けたほうが良いのかもしれません。

ただし、それでは複雑な関数・機能は学ぶ意味がないか、というと、そのようなことはありません。あくまで個人的な意見ですが、シンプルである、という点以上に大切な点がいくつかあり、それらの達成のためには、レベルの高い関数・機能や関数のネストも必要になる時があります。それらは以下の点です。

１．集計や抽出の方法に、エラーや予期せぬイベントに対するコントロールが効いている

集計や抽出の結果は、「今のところは正確」かもしれません。しかし、シートやブックのあちらこちらに行や列を追加することになったとしても、仮定している前提条件が変更になったとしても、または貼り付ける元データの構造が変わっていたとしても、その正確性を保つことができるでしょうか？

２．作業・処理としての集計だけではなく、新しい気づきに繋がる集計・分析をする

集計作業そのものが、組織に付加価値を与える仕事となることはほぼありません。大切なことは、その集計から課題や機会に気付き、アクションに繋がるシナリオを導き出せるか、ということです。

３．作業の自動化を図り、正確性と効率を上げる

関数やマクロを使用することで、手で行っている作業を劇的に減らすことができます。また、手作業に伴うエラーのリスクも回避できます。空いた時間で、もっと付加価値の高い仕事に取り組むことができます。

４．関数がシンプルであることより、ブック全体の「構成」がシンプルであることを優先する

ある数字にたどり着くために、いくつかの集計を経由する必要がある場合があります。しかしその過程で、シートの数がいくつにも増えてしまい、結局ブックが巨大なってしまうことがあります。そうなると、単純な関数の計算式しか使っていないとしても、どこからどのような数字を参照しているのか混乱を招いてしまいます。それなら、多少難しい関数で使うとしても、なるべく途中の過程を関数式の中で完結してしまい、全体の流れが分かりやすくなるようなブックの作成を目指すべきです。

今思いつくことを列記しただけですので、他にも大切な点は色々とあると思います。話を戻すと、エクセルを深く学ぶ、ということは上記のような点を達成することが目的であると思っています。これらのことを、社内で開いているエクセル講座や、個人的に教えている方などに紹介すると、最初はあまりピンとこない人が多いようです。しかし、業務の中で実際にこれらのことを経験すると、その価値（処理の正確さ、数字の分析力、スピード、構成力、など）に気付いてもらえると思います。

これらのエクセルに関する考察の記録と、また主に経理や財務で働いている方への共有の目的で、今後それとなく情報を掲載できればと思っています。テーマによっては、VBAまたは統計に関しても触れることができればと願っています。

最後になりますが…、10年前に経理部でアルバイトをしているときには、初級・中級レベルの関数やピボットテーブルさえ使うことができれば何も不自由なくデータ処理を行うことができ、それ以上のエクセルに対する知識の必要性を全く感じていませんでした。それは、監査業務に携わっているときも同じでした。しかし、日本に戻りFP&Aとして働き始めた際に、経理や監査業務では全く経験したことのない集計方法や分析をする必要に迫られ、謙遜になり新しい知識を求めてエクセルの勉強を再開しました。今では、新たにvbaや配列関数、power pivotなど、その他の機能に関しても、なくてはならない技術として当たり前のように毎日使用しています。きっとこれからも、新しいことを学び続けることによって、新しい”当たり前”が増えていき、更に仕事に価値をもたらすことのできる人材になることができるように願っています。もし、この記事を読んでいらっしゃる方にとって、何かのお役に立つようであれば幸いです。