関数式で苦戦する。

以前参加していたExcel Maniacs　、しばらく覗かなかったんですが、、、

熱烈に参加要請されて（無理矢理にというか．．．^^;）、久しぶりにお邪魔しました。

与えられた問題は５問。

締め切りは３月３日土曜日午後８時。

 

問題を見ると、、、う～～～～ん、難しいな～。

ここ１年くらい、まともに関数式を作っていなかったから

かなりハードルは高いですね～。

昨日ちょっと時間が取れましたので一番とっつきやすそうなのをちょっとやってみました。

（もう時間も限られていますし、ここを見てExcel Maniacs 参加される方もいないでしょうから、、、

　まあ私自身まだ解けていないしね。ちょこっとくらいフライングで発表してもいいかな．．．。(^^ゞ）

 

問題の一つは、

３桁から１０桁の数値から偶数の数字（偶数の数字は必ず一つとする）を抽出できる式を作りなさいという問題。

たとえば、135479だとすると、４が偶数なので4を抽出できる式が答えになります。

 

問題を解いていくのには、一つ一つの数値を切り出して偶数であるかどうかを検証する

その数値を抽出できるようにするっていうようになるのかな～。

 

たとえば、MID(H3,1,1) とすると先ほどの数値の左端の１が切り出せます。

これを、MOD(MID(H3,1,1),2)=0 とするとFalse となりますので偶数ではないと判定できます。

 

桁数が10桁までですので１０桁分の検証ができるように配列数式で作ってみますと

{=MOD(MID(H3,ROW(1:10),1),2)=0}

数式を選択してF9キー押下で式の答えを疑似的に表示してくれます。

以下のような答えを返してきました。

{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

これを見ると、左から４桁目にTRUEが返されていて、

これが偶数であると判定できます。

 

この式を発展させて

=MID(H3,ROW(1:10),1)*(MOD(MID(H3,ROW(1:10),1),2)=0)

として、F9キー押下すると

{0;0;0;4;0;0;VALUE!;#VALUE!;#VALUE!;#VALUE!}

となります。

この中の#VALUE!が無ければ簡単に抽出できるんですけどね．．．。^^;

では、６ケタ限定の式にしてみます。

{=SUM(MID(H3,ROW(1:6),1)*(MOD(MID(H3,ROW(1:6),1),2)=0))}

こうすると偶数の4が答えとして返されます。

 

エラーの処理をどうするか、ここが問題なんですね。

では、エラーが出ないように桁数を特定してしまえば、、、と早計に考えて．．．。^^;

 

LEN(H3) とすれば文字数6が返されます。

=MID(H3,ROW(1:LEN(H3)),1)*(MOD(MID(H3,ROW(1:LEN(H3)),1),2)=0)

と式を作って、Ctrl＋Shift＋Enter で確定させようとしても、、、

こんな式はダメだよってExcel君にダメ出しをされてしまいました。^^;

別のセルH20にLEN(H3)と作って、=TYPE(H20)として数値であるか文字であるか検証してみました。

すると1（数値）が返された、、、う～～～ん．．．。^^;

 

では、LOOKUP関数ではどうだろうかと式を作ってみました。

{0;0;0;4;0;0;VALUE!;#VALUE!;#VALUE!;#VALUE!}

この中の最大値を抽出できないかと．．．。

すると、、、0と答えを返してきた、、、あ、あれ～～～、おかしいな～。(^^ゞ

やはり頭の中がかなり錆びついていますね～。

 

残り時間も少ないですから、短めな式を目指すよりは、取りあえず答えを出せる式を作るように

方針を転換して行くことに．．．。

 

エラーを取り除くのではなく、エラーの出ない式にしてしまえば問題が解決しますよね。

入力されている数値の桁数（文字数）に合わせるのではなくて

すべてのセルを１０桁に揃えてしまえば簡単に答えが出せる、、、かな．．．。(^^ゞ

 

H3&RPET("1",7) として１０桁以上になった数値は切り捨ててしまえば

１０桁に揃えられますよね。

ということで作ってみました。

{=SUM((MID(--LEFT(H3&REPT("1",7),10),ROW($1:$10),1)*(MOD(MID(--LEFT(H3&REPT("1",7),10),ROW($1:$10),1),2)=0)))}

 

答えの4が返されました。

$マークも付けておきましたので、オートフィルで式のコピーをすると

う～～～～ん、バッチリ。(^^)v

 

まあ、、、短くてかっちょ良い式とはかけ離れていますが

取りあえず答えの出せる式ができたのでメンツが保てました。^^;

奇数を抽出する式はMOD関数の答え=1 とすれば奇数の判定ができますから

問４の式もすぐにできますよね。^^

 

こんな感じ。

{=SUM((MID(--LEFT(K3&REPT("2",7),10),ROW($1:$10),1)*(MOD(MID(--LEFT(K3&REPT("2",7),10),ROW($1:$10),1),2)=1)))}