伊豆・伊東情報館 Blog

自分的には最低限のマナー（？）だと思っていたのですが…
意外と多いのが、
結果が「0」なのに表示させたままになっているシート。

もちろん、計算の結果が「0」
それなら無問題なのですが…

大半は、データ未入力による「0」

よくあるのが、こんな感じ。
前年比を出してる表なんだけど、
18日以降のデータが入力されていない為に「0%」と。

まぁ、脳内でスルーすれば済む話なんだけど…
正直……　「目障り」　(*>ｖ<)

消す方法は、いたって簡単。
=IF(B3="","",B3/B2)

B3セル（今年の数値）が空白ならB4セル（前年比）を空白に。
空白じゃない（値が入っている）なら「B3÷B2」の結果を。
要約するなら、そんな感じ。

まぁ、基本中の基本ですね。　┐(^-^;)┌

もっと原始的な方法としては…
E4以降のセルは空白のままにしておき、
E3以降のセルに数値を入れる度に、オートフィルで数式を埋める。と。

ただ…
0表示が気にならない（放置している）人って…
その程度の作業を面倒臭がる傾向が強いけど（苦笑

次は、こんなケース。
前年のデータが不足していた結果…

( -。-)スゥーーー・・・ (ｏ＞ロ<)ｏ＜　0では割れねぇぞ　ゴルァ～

と、怒られる場合。　┐(^-^;)┌

エクセルが2007以降なら「IFERROR」という便利な関数があるのですが
何度も言うように、私のは2003なので。　ヾ(;´▽｀A``

で、そんな場合は
=IF(C2=0,"",IF(C3="","",C3/C2))と、
IFを入れ子にします。

ちなみに…
「入れ子」という表現をすると、敬遠される方がおりますが…
中に入れるというより、
外側に追加していくイメージの方が分かりやすいかもです。
読む時、流れを考える時は外側からになりますけど。

上の式の場合…
C2セル（去年の数値）が0ならC4セル（前年比）を空白に。
0じゃない（値が入っている）なら「IF(C3="","",C3/C2)」の結果を。
※IF(C3=～の説明は、上記“IF(B3=～”と同じ

とにかく…
IFを使えば、ソース・式は見苦しくなっても何とかなる。
それが持論です（苦笑

IF、バンザーイ　＼(^o^)／

過去記事：
曜日入力（変換）
条件を満たすデータの数

前回に続き、職場のエクセルで気になった点。

あまり細かい事を書いてしまうと色々とアレなのですが…
ざっくり言うと、従業員情報を性別や立場（身分）別に
集計した表があるんだけど、その数が合っていませんよ。と。

直接、元データを操作してみた訳じゃないけど…
私（心の声）「これ（手動で）数えて入力してるでしょ？」と（苦笑

そんな訳で「条件を満たすデータの数」の出し方を。

まぁ、条件が一つなら「COUNTIF」で問題無く済むんですけどね。
あ、あと…
エクセルが2007以降なら「COUNTIFS」があるのですが…

私のメイン機は、まだ2003なので。　ヾ(;´▽｀A``
そんな訳で別の方法を。

まずは普通に「COUNTIF」
A列に果物の種類（品名）
B列に産地が入ってる表です。

で、それぞれの果物名が入ってるデータ数を数える場合。
=COUNTIF(A2:A7,D2)と、なります。
※参照固定用の$は省略

D2のセル「みかん」と一致するデータの数を
A2:A7から数えろ。って意味ですね。
同様に「りんご」「メロン」も数えなさい。と。
まぁ、ここまでは普通。

次に同じ「みかん」でも産地毎に絞って数えたい場合。
「COUNTIFS」が無い、2003では「SUMPRODUCT」を使います。

正直…　今となっては、完全なレアケースだと思いますが。　┐(^-^;)┌

本来の「SUMPRODUCT」関数の使い方とはちょっと違いますが…
=SUMPRODUCT((A2:A7=D6)*(B2:B7=E6))
と、こんな感じに。

ちなみに本来の「SUMPRODUCT」関数の使い方は…
単価を欄を追加して
=SUMPRODUCT(E2:E4,F2:F4)

配列1（個数）と配列2（単価）を掛けながら足す。って感じ。
ややこしいけど。　┐(^-^;)┌

細かく書くと
・みかん　3×100　＝300
・りんご　1×120　＝120
・メロン　2×600　＝1200
◆300＋120＋1200　＝1620

ざっくり言うと、小計を書かず（表示させず）に合計が出せます。

で、配列を1つだけ（この場合は個数）指定した場合は、単なる和が表示される。と。
※画像では、E10セルの“6”の事

話を=SUMPRODUCT((A2:A7=D6)*(B2:B7=E6))に戻すと
A2:A7=D6　の部分で品名からD6（みかん）と一致するか？
B2:B7=E6　の部分で産地からE6（静岡）と一致するか？
を順に確認し、条件を満たすと“True（真）”が返ってくる。と。

Trueの場合は“1”、False（偽）の場合は“0”となるので、
それを掛ける事で、どちらか一方でも条件を満たしていないと
0×0・1×0・0×1となり、結果“0”になります。

逆に両方の条件を満たした場合、1×1　＝1となり…
最後に和を計算した結果、条件を満たすデータ数と等しくなる。と。

ん～　ややこしい。　┐(^-^;)┌

更にややこしくなるのが…
SUMPRODUCTは、データが数値型じゃないと“0”になってしまう。という事。

なので、=SUMPRODUCT(A2:A7=D6,B2:B7=E6)
だと“0”になってしまうのです。　｡･ﾟ･(ﾉД`)･ﾟ･
※データ型がブーリアン型（真か偽か）になる為

一応、1を掛ける事でブーリアン型を数値型に変え、結果を得る事もできますが…
=SUMPRODUCT((A2:A7=D6)*1,(B2:B7=E6)*1)

=SUMPRODUCT((A2:A7=D6)*(B2:B7=E6))
より僅かにですが、文字数が増えてしまう結果に（苦笑

ん～　ややこしい。　┐(-_-;)┌

まぁ、ぶっちゃけると…

自分用＆データ数が莫大な量でないならば…
IF関数だけでも、どうにかなっちゃうんですけどね。

美しくは無いけど。　≧∇≦ブハハハハハ

過去記事：曜日入力（変換）