OMNI

アクセスカウンタ

zoom RSS 脳のエネルギーは糖分〜錆びついた脳みその限界〜

<<   作成日時 : 2012/06/17 02:49   >>

ブログ気持玉 0 / トラックバック 0 / コメント 4

一昨日の午後、珍しくぽっかり(本当にぽっかり、午後丸々)仕事の手が空いたから、時間があるときにやろうと思って置いておいたエクセルの数式にとりかかってみました。

午後の時間全部使ったうえに、完全に理解したわけではありませんが(爆)、できた時は感動でした!
せっかくなので、忘備録もかねて残しておこうと思います。
パソコン、エクセルに全然興味ないという方は、今回は読み飛ばしてください…。


では早速。

かなり簡素化していますが↓こういう↓表で、
画像



黄色の部分(累計販売週数)を出すというお題でした。
ちなみに、累計販売週数というのは、欠品した期間や売り上げがない週も含めて、最初に売り上げが立った週から最後に売り上げが立った週まで何週間あったかということです。
売り上げが立った週が半年間(この表なら10週間ですね)に何週間あったかを出すなら、SUMIF関数1つでできるので簡単なのですが、間に売り上げゼロの週も含んで考えるとなると、ちょっと話は違ってきますよね。

この画像ぐらいの品番数なら自分で数えてもたかが知れてるのですが(アナログー!!)、本当はもっと品番数もたくさんあって(1万ぐらい)、MD週数も半年分なので26週分あります。

半年ぐらい前に、担当の営業さんに「こういうの数式で出されへんかなー」と言われて、ちょっと考えてみたんですけど、すぐにはわからなかったので放置していたんですよね(笑)
で、また来季の販売計画を立てたり受注数量を決定したりする時期になってきたので、これが必要になってきたわけです。

この簡素化された表を使って販売週数を出すために、私が考えたのは、
【1】 10週間のうち最初に0以外の数字が入った週の列番号を出す
【2】 最後に0以外の数字が入った週の列番号を出す
【3】 【2】-【1】+1で販売週数を求める
という3ステップでした。

こうやって書くと簡単そうですよね?できそうでしょ?
でも、まず【1】でつまずいたんです…(いきなりー!!)
だってよく考えたらそんなん出したことないし(笑)
でも、【1】ができたら【2】もできそうでしょ?

そこで頼るのは、やはりGoogle教授。
検索したら、使えそうな数式が出てきたじゃないですか!

その数式が↓これ↓。
=CHAR(SUMPRODUCT(LARGE((C3:L3>0)*COLUMN(C3:L3),COUNTIF(C3:L3,">0")))+64)&ROW()

はいイミフですね〜(笑)
(ちなみにYAHOO!知恵袋からいただきました)
これを見た瞬間、営業さんに「使えそうな数式は出てきたんですけど、私の脳みそではちょっと、というかかなり解読に時間がかかりそうなので、今から15分考えてダメだったら今回は諦めてください」とほぼ敗北宣言しました(笑)
そして宣言通りの敗北を喫した半年前…(爆)
今年はやるぞ!とばかりに意気込んで、考えに考えましたとも!!

この数式を、画像で貼りつけた表の品番Aの部分に作業用セル(たとえばN3とか)を作って貼りつけると、答えは「C3」(つまり、品番Aの週別販売数が記入されているC3からL3までの範囲で、最初に0以外の数字が入っているセルの番地)になります。
同じく品番Bなら「H4」、品番Cは0以外の数字が入っているセルがないので「#NUM!」というエラー値で返されます。

CHAR、SUMPRODUCT、LARGEと、私の知らない数式が3つも数式がネストされている上に、なぜこういう式でこの答えが導き出されるのかも全くもってわからなかったこの関数。
一つ一つ数式を紐解くのはパズルのようで楽しくもあり、とても脳みそが疲れる作業でもありました。

でも、あたい負けない!!
まず品番Aのことだけを考えてみます。
元々の数式である、
=CHAR(SUMPRODUCT(LARGE((C3:L3>0)*COLUMN(C3:L3),COUNTIF(C3:L3,">0")))+64)&ROW()
から、最後の部分「&ROW()」を取ってみると、結果は「C」。
最終的には列の数字で引き算をしたいので、「&ROW()」は要らないから消しちゃいます。

そもそもROWというのは行番号を出す関数なので、
=CHAR(SUMPRODUCT(LARGE((C3:L3>0)*COLUMN(C3:L3),COUNTIF(C3:L3,">0")))+64)で列番号を、
ROW()で行番号を出していて、それを「&」でくっつけているのです。

続いて次のステップ。
残っている
=CHAR(SUMPRODUCT(LARGE((C3:L3>0)*COLUMN(C3:L3),COUNTIF(C3:L3,">0")))+64)で列番号を出しているのは上述の通り。
でも、最終的には引き算をするので、できたら「C」じゃなくて「3」(←エクセルの3列目ということ)と表示されると嬉しいですよね。
そこでジャマになってくるのがCHAR関数。
CHAR関数というのは、「使用するコンピューターの文字セットから、そのコード番号に対応する文字を返す」というもの。
あまり詳しくは解ってないのですが(おい)、最後に64を足しているのは、アルファベットはAから順にコード番号65から開始されるかららしい。
要するに、CHAR関数を取ると、列のアルファベットじゃなくて列番号が返されるということです。
ということで、これも要らないから消します。

残ったのは=SUMPRODUCT(LARGE((C3:L3>0)*COLUMN(C3:L3),COUNTIF(C3:L3,">0")))
ステップ【1】だけなら、この数式の中身がわからなくてもこのまま使ってしまえばよいのですが、私の場合、これを理解したうえで、応用でステップ【2】の数式も出さねばならぬので、ちゃんと考えなあかんのです。

ですが、ここからが難問でした。
なんでこうなるのかはうまく言葉で説明できる自信がないのですが(笑)、一応書いてみます。

まず、COUNTIF(C3:L3,">0")の部分。
これは、指定した範囲(ここではC3からL3)の中で、0より大きい数字(0は含まない)が入力されているセルの個数を返します。
つまり、実数で表すと「8」です。

COLUMN関数は、列番号を返す関数です。
普通、引数に「C3:L3」のように範囲を指定すると、一番上、かつ左端のセルの値を返すのですが、ここではLARGE関数の絡みでどうやらそうではないようです。
対応するセル一つ一つの列番号を出してくれます。

で、そのLARGE関数は、指定したデータ内で、指定した数字番目に大きい数を返すというもの。
わかりにくい説明ですよね(汗)
この数式に当てはめて考えると、『(C3:L3>0)*COLUMN(C3:L3)』が指定したデータ、その中で、『COUNTIF(C3:L3,">0")』番目に大きい数字を返すというものです。

ここでポイントになるのは、(C3:L3>0)は論理値で返されるということ。
論理値と言うのは、ざっくり言うと、セルに入力されている条件式と合致していれば「TRUE」、合致しなければ「FALSE」という2種類で返されるのですが、数値化した時には「TRUE」は1、「FALSE」は0になります。
セルに直接入力ではなく、関数ポップアップを使ってIF関数を入力するときに、○○ならば●●という条件式の○○の部分を入れると、すでに右側に「TRUE」「FALSE」もしくは「正しくありません」というエラーのどれかが出ますが、あれが論理値です。

つまり、品番Aだと、対象となる範囲C3:L3の中身は、論理値で示すと、
1 0 0 1 1 1 1 1 1 1
となります。
そこに、COLUMNで返されている、それぞれ対応する列番号が掛け算されるので、
3 0 0 6 7 8 9 10 11 12
となり、そこから8(COUNTIF関数の返り値)番目に大きい数値を返すので答えが「3」となるわけなんです。

…え?
SUMPRODUCTはどうなってんの?って??

いやー、気付きました?
でも、なんか私にもなんでSUMPRODUCTなのかわかんないんですよね〜(笑)

SUMPRODUCT関数って、その名の通りSUM(足し算)とPRODUCT(掛け算)の合体関数なのですが、この式だと引数が足りないと思うんですよ。
引数が足りない場合、エラーになるはずなんですけど、なぜかこれは大丈夫なんですよね。
しかも、SUMでもPRODUCTでもダメで、やっぱりSUMPRODUCTじゃないとダメなんです。
SUMPRODUCT要らなくね?と思って取ってみたらエラー値になるし…(笑)
とにかく、わからないなりに、とりあえず要るんやなと無理やり納得した次第であります(爆)

そんなこんなで何とか70〜80%理解したら、あとは応用して【2】の数式を出せばいいだけ。
数式内『COUNTIF(C3:L3,">0")』の部分を実数の「1」に変えたら、0以外の数字が入ってるセルのうち、一番右側にあるセル(つまり最後に売り上げが立った週)の列番号が出ます。

あとは、【2】から【1】を引いて、その返り値に1を足せば、累計販売週数が出るというわけです。

うーん、我ながらいい加減な理解です(笑)
でも、結果答えが出るので良しとしました(爆)
だってこれ以上考えたらまたドツボにハマりそうなんですもん…。

いつになく脳を使ったのが災いしてか、帰り道にどうしても甘いものが食べたくなって、一人でアイスクリームを食べながら帰りました(小学生か!)
「三月のライオン」というマンガの中の、対局中の棋士がブドウ糖をたくさん入れたレモンティーを飲むという場面を思い出しながら、ホントに脳のエネルギー源って糖分なんだなーと実感しました。

午後の時間を目いっぱい使ったこともあって、達成感もすごかったし、解けた時には爽快感もあったけど、しばらくはこんなに頭使うことはやりたくないなー、疲れるし(笑)
何より、久しぶりに甘いものを入れられた胃がびっくりしたのか、ちょっとおなか痛くなったし…(汗)

テーマ

関連テーマ 一覧


月別リンク

ブログ気持玉

クリックして気持ちを伝えよう!
ログインしてクリックすれば、自分のブログへのリンクが付きます。
→ログインへ

トラックバック(0件)

タイトル (本文) ブログ名/日時

トラックバック用URL help


自分のブログにトラックバック記事作成(会員用) help

タイトル
本 文

コメント(4件)

内 容 ニックネーム/日時
こんな関数使った事ないや
EXCELってこんなこと出来るのっていう技を使いこなす人がいてびっくりしますよね
私は、EXCELよりACCESS派なんですが、関数とかマクロを複雑に組み過ぎて自分で作っておいて解読に苦労したりします
kazup
2012/06/17 14:13
>kazupさん
私もあの関数は理解するまでかなり時間かかりました。
ほんとにびっくりするぐらいのことをやってのける人、いますよね。

ACCESSは使ってみたいというか、データ数からして使うべきなので、ちゃんと勉強したいとかねがね思っているのですが、使ったことがないです。
ACCESS使えるなんてすごいですね〜。
煌砂
2012/06/17 21:08
VLOOKUPでEXCELと決別しました。
WORDもタブが上手く使えません。
名盤!
2012/06/17 22:57
>名盤!さん
決別!(笑)
まー実務で要らなきゃ実生活ではほんとに必要ないですからね…。

EXCELは必要なので覚えましたが、私もWORDはからっきしダメです。
ただただ文章を打ち込むぐらいしかできないはず…(笑)
WORDって、実際仕事でほとんど使ったことがないんですよね。
卒論で使ったぐらいでしょうか。
煌砂
2012/06/18 00:53

コメントする help

ニックネーム
本 文
脳のエネルギーは糖分〜錆びついた脳みその限界〜 OMNI/BIGLOBEウェブリブログ
文字サイズ:       閉じる