Home > Excel エクセルの使い方-関数/計算式-統計
Excel エクセルの使い方-関数/計算式-統計
「エクセル COUNTIF関数 複数のシート」
という検索が行われていました。
COUNTIF関数に関する定番的ご質問の一つです。
複数のシートを串刺しするように、3D集計するように、COUNTIFしたいという要望です。
このご質問をする方は、以下のような数式を試したりするようです。
例えば、「Sheet1」「Sheet2」のワークシートのA1:A100セルに入力されている「○」の数をカウントしようとして
「=COUNTIF(Sheet1:Sheet2!A1:A100,"○")」
という3D集計でお馴染みの形の数式を試してみるわけです。
Excel 2007・2010の場合、AVERAGEIF関数を使うことで、マイナスの値を除いた平均を計算できることをご紹介しました。
AVARAGEIF関数はExcel 2007から使えるようになった関数ですので、Excel 2003の場合この方法ではアウトです。
「エクセル 0をカウントしない」
「データ個数に0をカウントしないようにする方法」
といった検索が行われていました。
Excelでデータの個数を数えるときに「0」（ゼロ）をカウントしないようにする方法を探している方による検索です。
Excelの定番的ご質問の一つといっていいでしょう。
「計算結果がマイナスのときは０として計算したい」
といった検索が行われているのを見かけることがあります。
何らかの計算を行った結果がマイナスの値になったときは、そのセルを参照する計算の中でマイナスではなく「0」（ゼロ）として扱う方法を探している方による検索です。
▼サンプルファイル（003769.xls 16KByte）ダウンロード
サンプルファイルの「ORG」シートをご覧ください。
C1:C3セルには、A列からB列を引き算する「=A1-B1」といった数式、C5セルにはC1:C3セルを合計する「=SUM(C1:C3)」という数式がそれぞれ入力してあります。
A3セルには「0」、B3セルには「5」が入力されているので、「=A3-B3」の入力されているC3セルには計算結果の「-5」が表示されています。
このようなときに、このC3セルを「-5」ではなく「0」としたいという要望が先のような検索となります。
Excelをある程度お使いの方なら
「そんなのIF関数を使えばいいじゃないか」
と考えるんじゃないかと思います。
勿論、IF関数でOKです。
サンプルファイルの「IF関数」シートに、IF関数を使ってマイナスの値を「0」とする例を作成してあります。この方法で大きな問題はありません。
しかしこのIF関数を使った数式を、冗長で気持ち悪いと感じる方もいらっしゃるのです。
消費税計算に関連して、消費税計算の専用関数などないということ、消費税込み金額の計算方法、それとは逆の消費税抜き価格の計算方法をご紹介してきました。
「エクセル 消費税 関数」
といった検索を行っている方の中には、端数処理について知りたいという方も少なくありません。
2010年現在の消費税率は5%です。
税抜価格に消費税率を掛け算して消費税額を計算したり、税抜価格に消費税率に1を加算した「1.05」「105%」を掛け算して税込価格を計算すると、1円に満たない端数が生じることがあります。
この端数処理をどう行えばいいのかということを調べたいという方が
「エクセル 消費税 関数」
という検索を行っている方の中には含まれています。
まず、端数処理について、法律上はどう定められているかを確認しておきましょう。
法律上は端数を、四捨五入するのか、切り捨てにするのか、切り上げにするのか、どれが正しいのでしょうか。
平成16年2月19日に出された「事業者が消費者に対して価格を表示する場合の取扱い及び課税標準額に対する消費税額の計算に関する経過措置の取扱いについて（法令解釈通達）」の中に、総額表示に関連した端数処理についての記述があります。
「2（総額表示の具体的な表示方法）」の、なお書きの部分です。
「なお、総額表示の義務付けに伴い税込価格の設定を行う場合において、 1円未満の端数が生じるときは、当該端数を四捨五入、切捨て又は切上げのいずれの方法により処理しても差し支えなく、」
という記述があります。
つまり、総額表示の税込み価格を計算する際の端数処理を、四捨五入するのか、切り捨てにするのか、切り上げにするのかは、総額表示を行う法人・個人が決めればOKということです。
先日このサイトで
「エクセル オートサム」
という検索が行われているのに気付きました。
SUM関数を簡単に入れられる、［オートSUM］ボタンについて調べようとしていた方の検索です。
このサイトの検索ログを眺めて気づいた誤った検索について何度もネタにさせていただいています。
関数名というのは、英単語などが元になってつけられているものが多いこともあり、タイプミス・スペルミス的な誤った検索もよくみかけます。
その中でよくみかけるなあと私が感じているのは
「エクセル ROUNDDAWN関数」
といった検索です。
「エクセル 空白 以外 カウント 関数」
「エクセル ブランク 以外 カウント」
といった検索がこのサイトで行われているのをよくみかけます。
空白でないセル・ブランクではないセルをカウントする方法を探している方の検索でしょう。
Excelには、指定した条件に合致するセルの値を合計するSUMIF関数があります。
このSUMIF関数について調べようとしている方の中に
「SUMIF」ではなく
「IFSUM」で
検索している方がいらっしゃることをネタにさせていただきました。
SUMIF関数には他にも非常によく見かける間違いがあります。
「SUNIF関数」
という検索もこのサイトでよく見かけるのです。
先日、最頻値（モード）を求めるMODE関数についてご紹介しました。
このサイトでは
「エクセル 中央値 求め方」
といった検索が行われていることもあります。
OFFSET関数を使って、区間を可変にした移動平均を求める方法をご紹介しました。
単純に
「=AVERAGE(OFFSET(E2,1-$F$1,0):E2)」
という数式を入力しただけでは、どうしてもエラーが表示されたり、正しくない値が返されるセルが存在してしまいます。
「エクセル 最頻値 求め方 関数」
といった検索がこのサイトで行われていることがあります。
Excelで最頻値（モード）を求める関数を探していた方の検索でしょう。
数値の入力されているセルの個数をカウントするCOUNT関数は、SUM関数につづく入門的な関数の一つです。
このCOUNT関数を知った方から
「COUNT関数とCOUNTA関数というのは何が違うのでしょうか？」
といったご質問をいただくことがあります。
このサイトで、
「エクセル 株価 移動平均 関数」
といった検索が行われていることがあります。
Excelで株価の移動平均を求める方法を調べたかったのでしょう。
COUNTIF関数で「○○以上」「○○以下」といった条件を指定してセルをカウントする方法をご紹介しました。
同じような質問ですが、
「COUNTIF関数で「○○以上××以下」といった条件を指定してカウントするにはどうしたらいいのでしょうか？」
といったご質問をいただくこともあります。
ワイルドカードを使えばCOUNTIF関数で「○○を含む」といったカウントをできることをご紹介しました。
この方法を知った方からは、
「セルを参照して、○○を含むのカウントはどうやったらいいのでしょうか？」
といったご質問をいただくことがあります。
「=COUNTIF(C11:C20,"*右*")」
という数式で「右」という文字を含むセルをカウントできるわけですが、他の文字を含むセルを数えたいときには「"*右*"」の部分を修正しなければなりません。
様々な文字を指定してセルをかぞえたいときに、いちいち数式を修正するのは手間です。
そのため、
「セル参照でカウントしたい」
という要望をいただくわけです。
例えば、
C11:C20セルから「右」という文字を含むセルをカウントするには、
「=COUNTIF(C11:C20,"*右*")」
という数式にすればいいので、
B2セルに入力された文字をカウントしようとして
「=COUNTIF(C11:C20,"*B2*")」
という数式を入力してしまうのが、多くの方がやってしまうミスです。
COUNTIF関数であいまい検索をする方法をご紹介しました。
同じくCOUNTIF関数について
「COUNTIFで○○以上という条件を指定するにはどうしたらいいのでしょう？」
というご質問をいただくことがあります。
「○○以上」
「○○以下」
「○○より大きい」
「○○より小さい（未満）」
といった条件を指定してカウントするにはどうしたらいいのかというご質問です。
条件に一致するセルの個数をカウントするCOUINTIF関数は、実務で使われる頻度の高い便利な関数です。
このCOUNTIF関数についてご案内すると
「COUNTIFであいまい検索ってどうやればいいのですか？」
「COUNTIF関数で○○を含むという指定をしたいときは、どうすればいいのでしょうか？」
といったご質問をいただくことがあります。
分析ツールアドインのヒストグラム機能を使って度数分布表を作成する方法と、COUNTIF関数を使って度数分布表を作成する方法をご紹介してきました。
度数分布表を作成する方法は他にもあります。
範囲内でのデータの頻度分布を配列として返してくれる、FREQUENCY関数を使う方法です。
「この関数は何て読むのですか？」
というご質問をいただくことがあります。
そもそもアルファベットでつづられた単語・用語の読みというのは難しいものです。
コンピュータ関連用語で、単語を省略して作られた用語や、一部を省略した用語ではその難易度が上がってしまったりします。
Excelのワークシート関数でも、その読み方を悩んでしまうものがあり、その中でもCOUNTA関数は「何と読むのか？」という疑問を持つ方の比率が高い関数に感じます。
分析ツールアドインのヒストグラム機能を使って度数分布表を作成する方法をご紹介しました。
操作方法さえ覚えてしまえば難しい操作ではありませんが、作成される度数分布表は、あくまでも作成した時点のデータに基づくものです。元データが変更になった場合は、度数分布表を作り直す必要があります。
「一行おきに足し算するにはどうしたらいいのでしょうか？」
「一行おきに合計したいのですが・・・？」
といったご質問をいただくことがあります。
Excelの定番的質問といってもいいかもしれません。
このサイトの検索機能で、
「エクセル アベレージ関数」
という検索が行われていることがあります。
平均を求めるAVERAGE関数について調べたかったのでしょう。
四捨五入をする関数：ROUND関数について調べる際、「ラウンド関数」とカタカナ表記で検索する方がいらっしゃいますが、「ROUND関数」で検索するほうがいいということをご紹介しました。
切り捨てをする関数：ROUNDDOWN関数について調べる際、「ラウンドダウン関数」とカタカナ表記で検索する方がいらっしゃいますが、「ROUNDDOWN関数」で検索するほうがいいということをご紹介しました。
同じことは勿論、切り上げでも言えます。
四捨五入をするワークシート関数：ROUND関数について調べたいときに、「ラウンド関数」というカタカナ表記で検索する方がいらっしゃいますが、「ROUND関数」というアルファベット表記で検索するほうがいいということをご紹介しました。
同様のことが、他の数値丸めを行う関数でも言えます。
条件に合致したセルの個数をカウントする、COUNTIF関数について調べるのに、カタカナの「カウントイフ関数」で検索するよりアルファベット表記の「COUNTIF関数」で検索するほうがいいことや、「IFCOUNT関数」と間違って覚えてしまった方向けのネタをご紹介してきました。
COUNTIF関数では、他にも間違った検索を見かけることがあります。
「COUNT IF」
で検索している方を時々見かけるのです。
条件に合致した場合に合計を計算するSUMIF関数について、
「SUMIF関数で複数の条件を指定するにはどうしたらいいのでしょうか？」
というご質問をいただくことがあり、その解決方法としてこのサイトでは作業用の列を作成する方法をご紹介しました。
この、SUMIF関数で複数の条件を指定したいという要望は、COUNTIF関数で複数の条件をしたいという要望と同様に多かったようで、Excel 2007では複数の条件を指定できる関数が新たに作られました。
条件に合致する個数をカウントするCOUNTIF関数について、
「COUNTIF関数で複数の条件を指定するにはどうすればいいのでしょうか？」
といったご質問はとてもよくいただきます。
その解決策は、実はいろいろあるのですが、このサイトでは作業列を作成する方法をご紹介しました。
「ゼロを含まない平均を求めるにはどうしたらいいのでしょうか？」
「ゼロを除いて平均を計算したいのですが．．．？」
というご質問をいただくことがあります。
AVERAGE関数を使うと平均を求められますが、AVERAGE関数では引数に指定されたすべての数値の平均を返してきます。
しかし実務では、「0」を除いた数値の平均を求めたいということもあり、そんなときにいただくご質問です。
Excelのワークシート関数について調べたいとき、カタカナ表記で検索する方がいらっしゃいますが、アルファベット表記で検索するほうがおすすめだ的ネタを何度もご紹介しています。
このような例はあげだすときりがないのですが、もっと早くに取り上げるべき関数をすっかり失念していました。
「COUNT関数」について調べるのに、
「カウント関数」で検索している方がかなりいらっしゃるのです。
Excelのワークシート関数について調べたいときに、カタカナ表記で検索する方がいらっしゃいますが、アルファベット表記で検索するほうが多くの情報に到達できるということを、何度かご紹介しています。
この視点を持ってこのサイトの検索ログを眺めていると、他にもたくさんのカタカナ検索が行われていることに気付かされます。
「ラウンド関数 エクセル」
といった検索もその一つです。
この検索をした方は、おそらく四捨五入をする
「ROUND関数」
について調べたかったのでしょう。
「カウントイフ」「ランク」「インデックス」が頻繁にカタカナ検索されていることは知っていましたが、「ラウンド」がカタカナ検索されていることは気づいておらず、ちょっと意外でした。
RANK関数について調べたいのならば
「ランク関数」ではなく
「RANK関数」で検索すべきだ
ということをご紹介しました。
その他にもこのような例はあり、カタカナで検索される率が、高い関数と低い関数とがあると感じています。
条件に応じてカウントをするCOUNTIF関数は、カタカナ検索される率が高い関数だと感じています。
検索エンジンやヘルプの使い方など、検索システム利用についてお話しするとき、
「日本語は文字表記に相当注意しなければならない言語だ」
ということを私は強調しています。
意味的には同じもののはずなのに、送り仮名によって検索結果が違うことがあったり、音引表記にするか母音表記するかによって検索結果が違ってくることがあるからです。
日本語に用いる文字は、元々中国で作られた漢字と日本で新たに作られた漢字、漢字を元に日本で作られたひらがなとカタカナが混在しています。
本来は異質な文字を混在させて利用している、非常に柔軟性の高い言語といってもいいのかもしれません。
そして最近では、漢字・ひらがな・カタカナの他に、それらとは全く出自の異なる文字・アルファベットまでも混在させることが普通になっています。
きちんと調べたわけではないですが、一般的な人が作成する日本語の文書に英単語などがアルファベットそのままで利用されることは、例えば30年前より今のほうがかなり多いと思えます。（そもそも日本語では外来語はカタカナで表記する原則があるので、日本語の文書にアルファベット表記が混在することは少なかったはずです。）
データベース関数・DSUM関数の使い方についてご紹介してきました。
DSUM関数は、指定した条件に合致するデータの合計を求める関数でしたが、データの個数を求めたいということもあります。
そんなときに利用するのがDCOUNT関数です。
ExcelやAccessで数値を丸める方法を何度かご紹介してきました。
- Excel
- 数値を四捨五入したい−ROUND関数
- 切り捨てしたい・切り上げしたい−ROUNDDOWN関数・ROUNDUP関数
- 小数点以下を切り捨てる−INT関数・TRUNC関数
- 時間の切り捨て・切り上げ−CEILING関数・FLOOR関数
- Access
数値の丸めといえば、
「五捨六入するにはどうしたらいいのでしょうか？」
というご質問をいただくこともあります。
4以下を切り捨て5以上を切り上げるのが四捨五入ですが、
5以下を切り捨て6以上を切り上げる「五捨六入」をしたいというご質問です。
DSUM関数を使って複数の条件を指定して合計値を求める方法をご紹介しました。
フィールド名のすぐ下の行に条件を複数指定することで、AND条件の集計を行う例でした。
条件を指定して合計を求めるSUMIF関数を、IFSUM関数と間違って覚えている方がいらっしゃることをご紹介しました。
SUMIF関数と似たようなCOUNTIF関数についても、同様に間違って記憶している方がいらっしゃいます。
このサイトでは、間違った語句・キーワードについて何度かご紹介してきました。
Excelの関数の中に、間違って覚えられてしまいがちな関数があります。
条件に応じた合計を計算するSUMIF関数は、そんな間違って覚えられてしまう関数の代表と言っていいかもしれません。
このサイトの検索ログを見ていると
「IFSUM関数 エクセル」
といった検索をしている方を時々見かけます。
条件を指定して合計を求めるSUMIF関数で、複数の条件を指定する方法をご紹介しました。
集計を行うための作業列を作成しておいて、その作業列を引数として指定するという方法でした。
SUMIF関数を使って、複数条件指定で合計を集計する方法の難点が、この作業列です。
実務ではこういった集計作業用のためだけの列を作成することが嫌われることがあります。
指定した条件に応じた値を合計するSUMIF関数について、ご紹介しました。
SUMIF関数をご存知の方から、
「SUMIF関数に複数条件を指定したいのですが．．．？」
「SUMIF関数で複数の条件を指定するにはどうしたらいいのでしょうか？」
というご質問をいただくことがあります。
Excelで関数をある程度使えるようになった方から、
「DSUM関数を使うと、#VALUE!エラーになってしまうのですが．．．？」
「DSUM関数というのはどうやって使うものなのでしょう？」
というご質問をいただくことがあります。
頭文字が「D」の、
DSUM
DAVERAGE
DCOUNT
DMAX
DMIN
といった関数は、「データベース関数」といわれ、一度わかってしまえばどれも同じような使い方をする関数で、それほど難しいものではないのですが、はじめてデータベース関数を使う方の場合、他の例えばSUM関数などとくらべ、引数の指定方法がちょっと変わっているため、わかりにくいことがあるようです。
右端や最下部にある空白の合計欄も含んだ状態で［オートSUM］ボタンをクリックすることで、オートフィルなどでコピーをしなくても、合計を計算できることをご紹介しました。
この操作をちょっと応用すると、小計や合計を［オートSUM］ボタンのワンクリックで求めることができます。
右側に行方向の合計、下側に列方向の合計が表示されている表は、実務でよく見かけます。
そんな表を作成する場合どんな操作をしてらっしゃるでしょうか？
右側の一番上のセルで行方向の合計を計算しておいて、そのセルを下に向かってオートフィル。次に下側の一番左のセルで列方向の合計を計算してから、そのセルを右方向にオートフィル。
こんな手順で完成するという方が、自己流でExcelを覚えた方には多いように感じます。
最大値を取得するMAX関数、最小値を取得するMIN関数はとても簡単な関数で、インストラクションを行っても、MAX関数・MIN関数そのものについてはご質問をいただいた記憶はほとんどありません。
しかし、最大値や最小値の取得に付随した質問で
「2番目や3番目の値を取得するにはどうすればいいのですか？」
「2番目に大きい値や小さい値も、MAX関数やMIN関数で調べられるのでしょうか？」
というご質問をいただくことがあります。
MAX関数は1番目に大きい値を取得し、MIN関数は1番目に小さい値を取得する関数ですから、2番目以降の値を取得するには、別の関数を利用するのが簡単です。
LARGE関数とSMALL関数です。
条件に一致するセルの個数をカウントする、COUNTIFという関数があります。
第1引数でセル範囲を指定し、
第2引数で条件を指定すると、
第1引数で指定されたセルのうち、第2引数で指定された条件に合致するセルの個数を返してくれる関数です。
このCOUNTIF関数についてご存知の方からよくいただく質問と言えば、
「COUNTIF関数で複数の条件を指定するにはどうすればいいのでしょうか？」
「COUNTIF関数では条件を複数指定できないのですか？」
といったご質問です。
決して頻繁にというわけではありませんが、時々
「Excelで偏差値を計算するにはどうすればいいのでしょうか？」
「偏差値をExcelで求めたいのですが．．．？」
といったご質問をいただくことがあります。
平均と言ったときにもっともメジャーな算術平均を求めるAVERAGE関数の他に、幾何平均を求めるGEOMEAN関数、調和平均を求めるHARMEAN関数がExcelで用意されていることをご紹介しました。
その他平均については、異常値を除いて平均を求めるという考え方もあります。
スポーツで審査員が採点をするタイプの競技がありますが、その際審査員のつけた得点の中で最高点・最低点を除いた平均をとるものがあります。これも異常値を除くという観点で採用されている採点方法でしょう。
Excelの初心者向けセミナーではじめに取り上げる関数と言えば、合計を求めるSUM関数。次が平均を求めるAVERAGE関数でしょう。
このAVERAGE関数で求められる平均は、算術平均・単純平均・相和平均・相加平均と言われるものですから、統計学的な知識のある方から、
「幾何平均や調和平均を求めるにはどうすればいいのでしょう？」
というご質問をいただくことも、レアなケースですがあります。
「COUNT」という関数があります。
数値の個数をカウントしてくれます。
似たような関数で「COUNTA」という関数があります。
こちらは数値だけでなくデータの個数を数えてくれます。
これらの関数は初心者・中級者向けのテキストによく出てくるので、ちょっと関数を勉強したことがあればご存知の方が多いものです。
テストの点数を入力して順位を表示させたい、売上金額を入力して順位を表示させたい、といった要望をいただくことがあります。
統計関数の一つRANK関数を使えば、順位を求めることができます。
パソコン初心者の方が、表計算ソフトとしてのExcelを使いこなせるようになるには、いくつかの壁が存在します。
多くの方がぶつかる大きな壁として、IF関数と絶対参照があると感じています。
この壁を乗り越えられれば、その後出てくるExcelについての疑問はまあまあ解決できていけるように感じます。（次にぶつかる巨大な壁は配列数式あたりでしょうか。）
IF関数・絶対参照が理解できると、自分の知っている知識を使って自分のやりたいことをどうすれば実現できるのかを、柔軟に考える力の方がネックになってくるように感じます。
パソコン初心者の方にExcelのインストラクションをしていて、Excelの操作よりも算数的な思考ができずに苦労する方は少なくないという現実があります。
例えば、
○○の全体に占める比率を求める計算式を入力しましょうというときに、
○○を全体で割るのか、
全体を○○で割るのか、
ということがピンと来ない方が少なくないのです。
で、ExcelでSUM関数を使い始めたくらいの方から時々いただく質問があります。
累計を求めるには何という関数を使えばいいのでしょうか？
数値を縦方向に入力するようにしておいてその隣に累計を表示させたいのですが？
という質問です。
数値を切り上げる関数は何ですか？
切り捨てをするには何という関数を使えばいいですか？
Excelの関数初心者の方からよくいただく質問です。このサイトでもよく「切り捨て」「切捨て」などと検索していただいています。
これはヘルプをちゃんと検索していただければ、比較的表示されやすいのですが、我々日本人は「漢字」と「かな」に注意しなければなりません。
Excelで最もよく使われている関数といえばSUM関数ですが、実務では条件に合致した数値だけを合計したいというようなことも出てきます。
例えば、負の値だけ合計したいというような。
実務では、条件に合致したセルの数をカウントしたいということがあります。
例えば、アンケートを実施してその集計をするようなケースです。
Excelの関数を勉強しはじめた方からよくいただく質問の一つに、
「数値を四捨五入するのは何という関数ですか」
というご質問があります。
資格試験向けの勉強をしたことのない方で、この言葉がわかる方は少ないかもしれません。逆にMicrosoft Office Specialist試験（旧MOUS）の勉強をした方は、「あー、あれなんか苦手」と思う方が多いと思います。
Home > Excel エクセルの使い方-関数/計算式-統計
