Excel Tipsの目次


SubTotal関数


小計が必要な表の合計を求める・・・場合には重宝しますね

身近な例では家計簿でしょうか？

毎日、家計簿をつけていて、月ごとに小計を計算して、年度ごとに合計・・・
こんな時、皆さんはどうされますか？

次の図は、家計簿をイメージして作った Excel シートです。


一応、4月から6月までの支出入（のつもり）を入力し、残高を計算してあります。
これは普通の計算式で済みますよね。

これに、月ごとの小計を追加します。
こんな風に

出来栄えは、まあ、こんなもんかって感じですよね。
小計はわかりやすいように、条件付き書式で、背景を薄い青色にしています。


さて、問題は、どのように今回の SubTotal関数を使っているか？です。

小計を算出するために 例えば Sum関数を使ったとしたらどうなるでしょうか？

この図では、Sum関数と、Subtotal関数で集計した結果を表示しています。

結果は同じ
ですよね

えっ？　では、 Subtotal関数を使うメリットが無いじゃない？

いやいや、そんなことはありません。
こちらの図で、数式に与えている引数を比べてみてください。

小計のところは、Subtotal 関数の方が 1個目の 9 が余分ですが
合計のところの関数に与えている引数を比べると・・・・
Sum関数の方は、=SUM(D20,D16,D10)
Subtotal関数の方は、=SUBTOTAL(9,J6:J20)
となっています。

Sum関数では、小計を一つ一つ指定しています。　たとえば、これが12か月となると12個・・・
なんとなく、鬱陶しい話ですよね
それに比べて、Subtotal関数の方は、
範囲の最初から最後を与えています。
J6:J20
ですよね。
どちらが楽でしょうか？

また、家計簿でなくて、もっと小計が多い表などは、嫌気がさしてきますよね。
Subtotal関数はこんな風に、自分と同じ関数で算出されている結果は二重計算を避けるために無視されます。
Sum関数で同じような範囲設定をすると、小計の値まで加算されてしまいますよね。




関連する関数・・・ではありませんが、Subtotal関数の一つ目の引数について
意味としては、集計方法を指定します。

• １・・・平均値
  
• ２・・・個数（数値が含まれるセルの）
  
• ３・・・個数（空白でないセルの）
  
• ４・・・最大値
  
• ５・・・最小値
  
• ６・・・範囲内のセルの乗算結果
  
• ７・・・不偏標準偏差
  
• ８・・・標準偏差
  
• ９・・・集計
  
• 10・・・不偏分散
  
• 11・・・分散
  

となります。

また、この数値に100を足して、101〜111を指定すると、非表示になっているセルは計算対象外になります。

いかがですか？　便利そう・・・ですか？

現在、グループ統合システムを構築しています。
この構築プロジェクトを管理するため、富士通 ProjectWeb を利用しています。
しかし、富士通 Project Web に障害が発生しました。
5月26日、ProjectWebにアクセスできなくなっていました。


なぜだろうと不思議に思っていたら、
富士通製ツールへの不正アクセスで複数省庁や企業の情報が流出　国交省職員のメールアドレス7.6万件も確認 引用：IT Media との事でした。


外部からのアクセスを遮断するために、サーバー公開を停止しているとの事。
この為に、我々もアクセスできなくなっていました。

当然 Project も停止しています。

復旧は 5月31日頃になる見込みだそうです。

富士通 ProjectWeb 利用者の中には
・国交省
・内閣官房内閣サイバーセキュリティセンター
・外務省
など・・・＿|￣|○

大丈夫かしら？　現在のところ悪用されているのを確認していないそうですが・・・

接収した情報をすぐに使うでしょうかね？
続き・・・まだまだ

• Vlookup 関数
  
• If 関数
  
• Iferror 関数
  
• Sumif 関数
  
• Sumifs 関数
  
• Subtotal 関数
  
• Text 関数
  
• Date 関数
  

目次があるので載せときますね　⇒　いわゆる　目次

• Datedif 関数
  あれれ？　こんな関数が昔あったような気がするんですが、
  今のバージョンでは見つかりません＿|￣|○
  
• Cell 関数
  
• 文字列関数
  

やっばり、ExcelTipsを本ブログで書き始めてから読んでくださる方が多くなりました。
みなさん、Excelを使っているけど、限られた使い方しかしていないようですよね
かく言う私も、たまに他の人が使っているところを見て
あっ！　それどうやってしているの？
って聞いていることが良くあります。

Excelって奥が深いですよね

どんどん使い倒していきましょう。
知っているのと知らないのでは何倍も作業効率が変わることもありますからー

これからも、自分が困っていて知恵を絞って解決できたことや、他の人が困っていて助けて差し上げたことなど書いていきたいと思います。

なお、この記事を読んでくださっている方にお願いがあります。
Excelを使っていて、こんな使い方があるよー！　とか、こんな時はどうするのー？ってのをコメント欄に記入していただければありがたいです。
できるだけ早く返事を差し上げるとともに、問題とかでしたら解決していきたいと思います。
よろしくお願いいたします。

以前に Excel の関数が良く分からないって言ってた知り合いがいて、

そーだ

このブログを評価してもらおーって思って教えてあげたら

動画の方が分かりやすいよー！

文章だけじゃわかりにくいって評価をいただきました・・・

それはそれで、ちょっと動画の方でどうしようか考えてみまーす

それと、この記事を読んでくださっている皆さんにお伺いしたいことがあります。

　・Excel を使ってて、こんな時はどうしたらいいんだろう？
　・こんな関数は、どうやって使うんだろう？

って悩んでおられることはありませんか？
あったら教えていただきたいんですー

また、動画を作るんなら、どんなことが知りたいでしょうか？
こちらも教えていただけたら励みになりまーす

よかったら、コメント欄でご意見やご質問をお願いいたします



ちなみに、今までの Excel関連記事をまとめた目次です。

Excel Tipsのまとめ・・・いわゆる目次( ´艸｀)

ＳｕｍＩｆＳ関数
タイトル画では、最後の S に( )を付けていますが、これはＳｕｍＩｆ関数の末尾にＳがくっついてＳｕｍＩｆＳ関数になってますよーって事を表すためで、本来は、ＳｕｍＩｆＳのように(　)なしです！


この関数は、Ｓｕｍ関数の亜種であるＳｕｍＩｆ関数。

このＳｕｍＩｆ関数では条件が一つしか指定できなかったことに対し、ＳｕｍＩｆＳ関数では条件が複数指定できるようになっている強者関数ですね。

なのでExcelの開発者は、ＳｕｍＩｆ に Ｓを複数形の意味のＳを付けたのでしょう

それではどんな時に使えばいいのでしょうか？
その前に、ＳｕｍＩｆＳ関数でなくて、ＳｕｍＩｆ関数で式を作れないのでしょうか？
この答えは Ｙｅｓ であり Ｎｏ です。

へっ？何それ？　って思われた事でしょう。

元々、SumIfS関数はありませんでした。そんな時はどうしたかって言うと、
ＳｕｍＩｆ関数の条件式を工夫して作り出す・・・か
別のセルに計算式を入力し、その結果を用いてＳｕｍＩｆ関数の条件としたはずです
これって結構骨が折れますよね。　また再利用性も低下しますしね

さて・・・どんな時に使うかってのを説明する例題としては・・・
成績表で、男子か女子かって条件で集計していました。

複数の条件と言うと、全学年・全クラス・男女別に集計する・・・時には、ＳｕｍＩｆ関数で行うには、条件式がちょっと複雑になりすぎますよね

なので、
=SumIfS(＜合計対象範囲＞［，＜条件範囲＞，＜条件＞］) と言う引数の指定の通りに入力すればＯｋです。
但し、［，＜条件範囲＞，＜条件＞］は一回以上の繰り返しです。

条件が１個だけの時はＳｕｍＩｆ関数でも表現できますよね
こんな感じです。

=SumIf(＜条件範囲＞，＜条件＞，＜合計対象範囲＞)





SumIf関数とは引数の順番が異なりますので、SumIf関数を良く使われていた方が、SumIfs関数を使うときに間違いやすいのは引数の順番です。

先ほども話しましたが、
=SumIfS(＜合計対象範囲＞［，＜条件範囲＞，＜条件＞］)
=SumIf(＜条件範囲＞，＜条件＞，＜合計対象範囲＞)
と順番が違っています。
間違っても文法的には合っていることもあるので、その時は変な結果になります。

そんな場合、パニクってなかなか原因がつかめないことがありますので、特にご注意を！

同様に 〜〜If 関数と、〜〜IfS関数では、引数の順番が変更されていますので

●毎日、毎日、僕らは鉄板の！

今日も、途方もないことを言われて愕然としました

以前、３日ぐらいかけて作った資料・・・
上司から頼まれて作ったんですが・・・

これを必要な部署に提出し、しばらくすると・・・
その部署から追加情報が欲しいといわれ、対応しました

しかし、部署から提出された資料には、以前私が作成した情報の必要な部分が削られていました。
システム稼働のためにはなくてはならない情報が、不要と勘違いされて削られていました。

当然、システム担当の私としては、そのような必要な情報が欠落した状態で、頼まれた情報だけを追加する・・・なんてことは到底できません。

なので、必要な情報を再度追加し、追加したことがわかるようにして再提出しました。

すると、『勝手に追加しないでください』とその部署から上司へクレームが入ったみたいです。

現在、グループ統合システムを推進するうえで、コードのマッチング作業が急がれているんですが、そのマッチング作業を担当する部署は、当然システムのことについては素人なので、ガシガシと削除しまくったみたいです。

●よく似た状況

今までにもこれとよく似た状況はありました。
何度も何度も・・・・

なので、泳げたいやき君の歌詞が頭に浮かんできたんです。

●ブチ切れました

一連のやり取りは、上司からの指示が発端なので、メールのやり取りはすべてｃｃで上司にも流し、必要に応じて説明もしてきました。

しかし、
『なんで、最初から必要な情報を入れておかなかったの？』って言われて・・・

はーっ？

何ゆーてまんねん！　最初、全情報を入れて渡しましたよ。　その部署が勝手に必要な情報を削除して来たんですよ。
そのまま、要望された情報だけ追加して返したら、新システムが稼働したときとんでもない状態になるのは目に見えてます。そのようになった時に責任を取りたくないですからね。
ってブチ切れて上司に文句を言いました。

上司は、もともと私の会社で私が作ってきたシステムについて全くご存じないので、恐らくｃｃメールも中身までよく参照していなかったのでしょう。
例え、数日かけて作った資料でも・・・

さすがに、かなりきつい文体でめーるしちゃいました

●上司が『協力してね』と

そしたら、上司から、『今は、プロジェクトを前に進めることを考えて、協力をお願いします。』と

ちょっとだけ頭が冷めて、返信はしましたが・・・
返信文章を書いているうちに、やっぱり煮えたぎるマグマの熱が頭に伝わり

上司に、『その部署にキチンとするように、わからないことがあったら私に相談するようにゆっといてください』って、上司に指示していましたねー

やれやれ

ＳｕｍＩｆ関数


この関数は、Ｓｕｍ関数の亜種ですね。
Ｓｕｍ に Ｉｆがついています

条件が指定できるＳｕｍ関数ってことになりますね。

実は・・・・生徒の成績表でSumIF関数を使っちゃっていました

この表です。覚えておいでですよね。

この表の下のところ、男子平均点と女子平均点を出すところに・・・

この図を見ていただくと、数式バーのところに
=SUMIF($B$2:$B$27,LEFT($A28,2),C$2:C$27) /
COUNTIF($B$2:$B$27,LEFT($A28,2))
っていう数式が入力されていることが見えますよね。
最初の SumIF関数は、B2〜B27セルに、B28セルに入力されている文字の先頭2桁 (この場合は"男子"）と同じ値が入力されている行の、C2〜C27の対応するセルの内容を足せという式です。
つまり、男子の国語の点数の合計を出せってことですよね。

そして、これを人数で（男子人数で）で割れば国語点数の男子平均が算出できます。

しかし
おやっ？　『あなたあほですか？』って私に対して思っておられる読者の方もいるかも・・・と

そうです。こんなに複雑な式にしなくても、実は・・・今回は紹介するつもりのなかった
AverageIF関数を使えば一発です。

次の図を見てください。

先ほどとは違って、AverageIF関数を使っています。
具体的には、
=AverageIF($B$2:$b$27, LEFT($A29,2), C$2:C$27)
と、しています。
もちろん、同じ数値が求められます


Excelでは、見かけは同じでも、あたしとしては異なる場合があります。
良く失敗する例としては

• 一方が数字[12345]で、もう一方が文字[12345]
  
• 一方は[ABC]で、もう一方が[ABC ]（後ろに空白が付いている）
  
• 一方は[123]で、もう一方が[１２３]（半角文字と全角文字の違い）
  

などです。
前にお話ししたおさらいですよ

よく似た関数に、CountIF関数というのがあります。
今回も平均を算出するときに使いましたね。
それから、SumIFs、CountIFs、AverageIFs など、それぞれの関数に複数形を表す s がついてる関数
これらは、そうなんです。条件が複数指定できるんです。
複雑な条件式を作り出さずに、条件を並べればＯｋですね

割と、多くなってきたので、目次を作ろうかなって
使いにくかったらご意見くださーい

• 【Vlookup 関数】




• Databaseもどき
  Databaseアプリを作るときに、良く使う関数を２つ紹介。　VLOOKUP関数と、IF関数
  


• Vlookup 関数　Databaseもどきを助ける関数
  VLOOKUP関数について説明と使い方の例示
  




• 【If 関数】

　　　　これは、もう定番！　使いこなせれば表現力アップ間違いなしですよー！



• Part - 1 - If関数の基礎
  


• Part - 2 - 使える場面の例
  


• Part - 3 - 条件式について
  


• Part - 3 (2) - And と Or について
  条件式の中で何かと使う And関数 と Or関数 について、例題を使いながら説明しています
  


• Part - 3 (3) - 使い方のサンプルとして成績表を作成
  割と複雑な条件式をどのように場合に使えばよいか、小学校とか中学校のクラスの成績表の例を用いて説明しています
  


• Part - 3 (4) - 条件式の構築方法について
  


• Part - 3 (4) 2 - 絶対参照と相対参照の違いについて
  





• 【IFs 関数】

　　　Excel 2016 より使えるようになった関数です。
　　　If関数の機能拡張版ですが、単純な機能拡張ではありませんでした。



• Part - 1 - IFs関数の基礎
  


• Part - 2 - IF / 複雑なIF / IFs 比較
  





• 【IfError 関数】



• 【SumIf 関数（群）】

　　　条件を付けて合計を計算する関数！　使い方によったら・・・
　　　いや、様々な場面で知ってて損はない関数ですね！　知ってたらお得ですよー(笑)



• Part - 1 - 単一条件式 (SumIF)
  


• Part - 2 - 複数条件式 (SumIfs)
  





• 【Subtotal 関数】

【Text 関数】　　
こちらに文字列関数だけの専用メニューがあります　　New

• Part - 1 - Text関数の基礎
  


• Part - 2 - Text関数を使いこなすために
  

【Date 関数】　

• Part - 1 - Date関数の基礎


• Part - 2 - Date関数を使いこなしてカレンダー作り

【Cell 関数】

【不思議なこと】

何もないセルと空白のセル
何もないセルには左側の溢れた文字列が表示されるのに、少しでも何かを入力したセルはたとえ消去しても溢れた文字列が表示されない不思議

補足

ＩｆＥｒｒｏｒ関数


この関数は、ＩｆＥｒｒｏｒって言うぐらいなので、何かエラーがあったら・・・っていう感じの関数です。

って仙人が食べる霞みたいな言い方しても仕方ありませんよね。

それは、ひとまず置いといて・・・

Excelでは、表の中にいろいろな数式とか関数とか入力しますよね。

そんなとき、何らかのエラーが発生して、#n/a とか、#Error 、#Div/0とか表示されて、見栄えが悪くなったりすることはありませんでしたか？


一生懸命、データを入力して、計算式を入力して、こーんな、表示が出たら・・・


ですよねー

こんなエラーが表示された原因が

• 計算式の間違い
  
• 関数に与えた引数の誤り
  
• データが存在しないものがあった場合
  

などですよねー

赤い字の、そもそもの間違いの場合は、修正したらいいんです
しかし、一部のデータが存在しない場合は？
まさか、架空のデータを作り出すわけにはいかないし・・・

仕方ないから、エラーになっているセルの数式とかを消しちゃいますか？

でも、そうすると、再利用できないですよね。
４月のデータを入力していたら、Ｄ１０セルがエラーだったから、Ｄ１０セルの内容を消去・・・
そのまま、５月のデータを入力したら、Ｄ１０セルに計算値が表示されなければならないのに・・・

ってな事があったら


はてさて、どんな時に使うんでしょうね？

もちろん、データが無くて計算できずにエラーとなるけど、データが無いのは仕方がない場合ですよね。

これはどーしよーも無いです。
なので、計算式や関数式の値がエラー値になる場合はに、見栄え良く、もしくはユーザーフレンドリーな表示にするために
ＩｆＥｒｒｏｒ関数を使うんです。

使い方は簡単です。
= ＩｆＥｒｒｏｒ（＜数式もしくは関数式＞，左記の”数式もしくは関数式”がエラーとなった場合に、表示する数字や文字列など）

例えば、
=IFERROR( 1 / 0, "ゼロで割ったらだめですよー！")
とか、
=IFERROR(VLOOKUP("西京都" , $A$1:$B$47, 2 ,0), "都道府県ではありません。なので県庁所在地は表示できません。")
とかね。


最初に、IFERROR関数を使っちゃうと、エラーが発生する理由の赤文字の部分
があった時に、発見しにくくなります。
なので、IFERROR関数を使わずに十分テストして、エラー値が出ないようにして、
どうしてもエラー値が発生するのを回避できない場合に
ＩｆＥｒｒｏｒ関数を使う
ようにしてくださいねー！


関数の名前にIFが付く関数があります。
ISERROR
ですかね。
この関数は、引数の数式や関数式がエラーとなった場合は、Trueを返す関数です。

　=IFERROR(＜数式＞,"エラーだよー！")
は
　=IF(ISERROR(＜数式＞）,"エラーだよ！", ＜数式＞)
って表せますけど、

Excel君に＜数式＞を二回も計算させなきゃならなくなるので、エラーかどうかだけ判定する場合に限ってISERROR関数を使ったほうがいいでしょうね

あっ！　ちなみに、よく似た関数に、ISERR関数もあります。
これは、与えた＜数式等＞が #n/a 以外のエラーの時に 真（Ｔｒｕｅ）となります。

IF関数について、３回、条件式の詳細について結構、お話しました。
IF関数　第一回
IF関数　第二回
IF関数　第三回
IF関数　条件式第一回
IF関数　条件式第二回
IF関数　条件式第三回

前回は、IF関数を使って実際の適用事例をお話ししました。
特に条件式については、力を入れて、割と複雑な条件式を記述する過程を見て頂きました。

ただ、複雑な式は往々にして、一個のセルだけではなくて、複数のセルに同じ式を入力することが多いと思います。

同じ式を入力する時に使用するのは大抵、コピー＆ペーストですね

でも、式をコピー＆ペーストする時に気をつけなきゃならないのは・・・

相対アドレスと絶対アドレスの違いです。
これは厳密に使い分けないと、自分の想像した結果とは全く異なる結果になってしまいます。


アドレスとは、住所を意味する言葉ですが、Excel の世界では、セルの参照を意味します。
つまり　Ａ１セルとか、Ｂ５セルとかのＡ１やＢ５がそれにあたります。
最初のアルファベットは列の名前、続く数字は行の番号になりますよね。

前回の式の中にもアドレスが多数入っていました。
でも、アドレスの中には、列名や行番号の前に $ （ドル）マークがついているのが有りました。

この $ （ドル）マークは、絶対参照を示す記号です。

絶対参照に対する言葉は相対参照です。

この二つの違いはって言うと、コピー＆ペーストした時に新しく入力されたセルに格納されている式。
この式で使用されているアドレスが、元のコピー元の式で使用されているアドレスから変更されている（相対参照）か、変更されていない（絶対参照）かが違います。

相対参照では
=A1 って格納されているセルの内容をコピーし、三行下のセルにペーストしたら、 =A4 になります。
そして、これを５列右にペーストすると、 =F4 になります。

それに比べて、絶対参照では、
=$A$1 って格納されているセルの内容をコピーし、三行下のセルにペーストしたら、 =$A$1 になります。
そして、これを５列右にペーストすると、 =$A$1 になります。

あれれー！　なんで、二種類あるのでしょーか？

当然のことながら、それぞれがあれば便利、なければちょー不便なんですよ。
恐らく、条件式でもよく使いますが、前に述べたVLOOKUP関数でも、とてもよく使います。

Excel ： 便利な関数　Vlookup　Databaseもどきを助ける関数

さて、次の図を見てください。　B1セルに "=A1"、C1セルに"=$A$1" と入力しました。

これを用いて、相対参照と絶対参照の違いをお見せします。
まず、Ｂ１セルをＢ２からＢ１０セルまでにコピーします。　すると、次の図のようになります。

どうです？　"=A1"から"=A10"まで、きれいに並びましたね。
特に、行番号はきちんと、その左のセルを参照するように変更されています。

さて、今度は、Ｃ１セルで同じことをしてみましょう。

すると、今度は・・・Ｃ２〜Ｃ１０セルまで、"=$A$1"・・・ですよね。

これが、相対参照と絶対参照の違いです。
Ｂ列の方は、Ｂ１セルに入っている数式は、"=A1"、つまり、Ｂ１セルから見て、１列左０行上のセルを参照するって意味です。
Excelはこの相対参照形式の数式については、『１列左０行上のセルを参照』ということをコピーします。
よって、Ｂ５セルにコピーされた数式は、１列左は　Ａ、０行上は５・・・なので、"=A5"となります。

これに比べて、絶対参照は、常に同じ場所を指すことを示しています。
なので、Ｃ列の数式は、ぜーんぶ "=$A$1" となっています。

ちなみに、Ａ１〜Ａ１０に適当な値を入力し、通常表示に戻した場合にこんなほうになるよーってのが次の図です。

表示されている結果があなたの期待通りになっていますか？