Excel Maniacs

第３５問 曜日合わせ（その２）

　む印さんが作られた問題「曜日合わせ」を眺めていたら
「曜日合わせ その２」が頭に浮かびました。

勝手に案に割り込み、申し訳ございません。
また、それをお許しくださいまして、ありがとうございます。

【問題】
Ｂ３（入力欄）に年月日を入れてください。
Ｃ３（曜日欄）にその曜日が表示されます。

例えば、2009/10/10 と入れますと（土）と表示されます。

そこで問題です。
次に１０月１０日が土曜日になるのは、、、
西暦○○○○年の１０月１０日でしょうか？
Ｆ３（解答欄）にそれを求めてください。

＜例＞
２０１０年２月２８日（日）・・・・・・→２０１６年２月２８日
２０１０年３月　１日（月）・・・・・・→２０２１年３月　１日
２００８年２月２９日（金）・・・・・・→２０３６年２月２９日

書式・作業列等はいつものように禁止です。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（からくちさん出題）

解答用参考ファイル：Q35_2009.lzhをダウンロード

***********************************************************************

尚、解答発表は９／１７（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

第34問 曜日合わせ 解答例

殆どの皆さんは、既にお気づきの通り、対象となる日付の「前年曜日あわせ」は
=DATE（年,月,日）-364
の１６文字で問題ないと思います。
 
　　関数をつかわないなら
　　=（年&-月&-日）-364
　　で１４文字。
 
面倒系（年－1）で考えたのは
=DATE(年-1,月,日+MOD(SUM(WEEKDAY(DATE(年-{0,1},月,日),2)*{1,-1}),7))
と６２文字。
 
　　※あくまで「前年」だからこれでいいのですが・・・　　。
 
-- 
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（む印さん）

第34問 曜日合わせ

Excelをはじめた当時、必要に迫られ手探りで作ったアドイン関数は
ワークシート関数ではどうすればよいか？と、振り返ってみました。

－－－－－－－－－－－－－－－－－－－
問題：
年（西暦）・月・日が入力されたとき、その日付に対する、
『曜日で合わせた前年の日付』を表示する式をセルJ3に入れてください。

尚、式の参照セルは名前定義した「年」（B3）・「月」（E3）・「日」（G3）　の名前を使用し、
それ以外の参照は禁止です。　使用例）=DATE（年,月,日）
（もちろん、名前定義の「年」「月」「日」に式を割り当てるなど、改造するのも禁止です！）


『曜日で合わせた前年の日付』とは・・・
指定入力された「年月日」が「2009年1月1日 木曜日」の場合、それに対する前年の日付は
「2008/1/1の火曜日」となりますが、曜日を合わせたいので、それに一番近い木曜日である
「2008/1/3」となることをいいます。

また、「前年」としていますが例外として、入力された「年月日」が「2009年12月31日 木曜日」
の場合の結果は2008/12/31の水曜日に一番近い「2009/1/1 木曜日」となります。

その他の例）
入力：2009年2月27日　（金曜日）→　解答結果：2008/2/29　（金曜日）
入力：2009年2月28日　（土曜日）→　解答結果：2008/3/1　（土曜日）

－－－－－－－－－－－－－－－－－－－

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（む印さん談）

＊

*

*

*

*

*

*

解答用参考ファイル：Q34_2009.lzhをダウンロード

***********************************************************************

尚、解答発表は９／１２（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

第３３問 あいうえおテトリス （その２） 解答例

皆同じように、短い文字数で凄いですねーー

で、かないませんので、、

お遊び案です。

D2に入れる文章が難しい：：

あおいうみのうえのあおいそらも、あおいうみのうえのあかいそらも、うおーええなー

=LEFT("■",(15+(MIN(MID(NOW()&REPT(1,10),11,1),MIN(LEN($D$4:$H$4))))-LEN(D$4)-ROW()＜0))

この式をD6に入力して、コピー・・・・その後、・・・・F9でだいたい、10秒おきに、、動いて見えるかなーー

よろしくお願いいたします。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ジョー３さん談）

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

・・・でお遊びで私もノンマクロでテトリス風なものを作ってみました。

作り込めばある程度のものに仕上げることができると思いますが

ま、お遊びですので・・・(^^ゞ

※循環参照を利用していますので他のブックを開かないでください。

Q33_2009_kumaPooh.lzhをダウンロード

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（くまぷー）

第３３問 あいうえおテトリス （その２）

【問題】

　D2の文章に含まれる「あ」「い」「う」「え」「お」の数を

調べて視覚化する。

　前回のQ2では下図のようになった。

*

*

*

*

*

*

*

*

*

それを今度は横に「■」が揃ったら、その横列の「■」を消去して

その列の分、表の中の「■」を下方にずらしたい。

*

*

*

*

*

*

*

*

*

D6に入力した式をH15までコピペして完成させるには

D6にどのような式を入力すればよいか？

解答用参考ファイル：Q33_2009.lzhをダウンロード

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ジョー３さん出題）

***********************************************************************

尚、解答発表は９／５（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

ノンマクロでテトリスを・・・

　エクセル界の奇人（？）、あのジョー３さんから
ノンマクロでテトリスを作るというお題が出された。
VBAを使って作るのならば誰も驚かないと思うが
ノンマクロ・・・
　これは「動かす会」への挑戦か？！

　初めはくまぷーもそんなの、無理・無理・無理・・・
と思ったが・・・

果たしてVBAを使わずにどこまで
テトリスに迫れるのか？！

果たしてジョー３さん以外に参加者はいるのか？！(^O^)

第32問 あいうえおテトリス 解答例

一応、

出題者案を送りますね、

Q1：４４：=REPT("■",LEN($D1)-LEN(SUBSTITUTE($D1,D2,)))
Q2：２８：=IF(LEN(D$3)+ROW()＞9,"■","")
Q2は、他に２９，２７もありましたが、、
言われるまで気付かなかった。・・・・２０はね、、、

　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ジョー３さん談）

---------------------------------------------------------------------------------------------------

今、映画館にいて投稿できないので、この場を借りて・・・

Q1：４４：=REPT("■",LEN($D6)-LEN(SUBSTITUTE($D6,D7,)))

これはジョー３さんとセル番地が違うだけですね　(^^ゞ

Q2：２０：=MID(D$8,20-ROW(),1)

Q1なしのQ2：５６:=LEFT("■",19-ROW()<LEN($D$6)-LEN(SUBSTITUTE($D$6,D$7,)))

さて、４７というのはどういうんだろう？

　　　　　　　　　　　　　　　　　　　　　　　　　　　　（くまぷー）

第32問 あいうえおテトリス

【問題】

　D6の文章に含まれる「あ」「い」「う」「え」「お」の数を

調べて視覚化したい。

Q1：例のようにD8の式をH8までコピペして完成させるには

　　　D8にどのような式を入力すればよいか？

Q2：例のようにD10の式をH19までコピペして完成させるには

　　　D10にどのような式を入力すればよいか？

尚、「あ」「い」「う」「え」「お」の数の最大値は各１０とする。

*

*

（例）

*

*

*

*

*

*

　　　　　　　　　　　　　　　　　　　（ジョー３さん出題、一部くまぷー改）

解答用参考ファイル：Q32_2009.lzhをダウンロード

***********************************************************************

尚、解答発表は8/29（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

第３１問 Four Fours for Excel （数字を作る その２） 解答例

出題者、自らが言うのも可笑しいですが、、、

この問題の面白いところ
停止してしまう数値が夫々に違うところ
ある数値で停止、、、しかし他の方は簡単に通過
でも数値が変わると立場も逆転、、、これが面白い、これが不思議

この問題の悔しいところ
出来ない＞＞＞この数値、無理では＞＞＞やっと出来た
その式を改めてみると、別にどうって事のない式
こんな式が何で直ぐに思い付かなかったのか、、、悔しい～

皆さんがどんな式を組まれたのか楽しみです

最後まで苦しめられた数値は、、、１８５
出来上がった式にその様子がありあり・・・

苦しんでやっと作った式
他に解は無いと思いきや皆さん違う式だったりして
でも、たまたま違う式になったのでは＜お遊び式、好き人＞としては何とも・・・
１つだけでもいいから意図して皆さんと被らない式を作ろう
と言うことで作ったのが、、、７４

どうか何方も同じ式を提示していませんように・・・

あっ、そうそう言い忘れるところでした
判定は、あくまで己に甘く、、、です
幾つか疑問符付きです
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（からくちさん談）

からくちさんのファイル：Q31_2009_karakuchi_san.lzhをダウンロード

**********************************************************************************

himiさんのファイル：Q31_2009_himi_san.lzhをダウンロード

くまぷーのファイル：Q31_2009_kumapooh.lzhをダウンロード

y sakuda さんのファイル：Q31_2009_sakuda_san.lzhをダウンロード

kirさんのファイル：Q31_kir_san_2009.lzhをダウンロード

む印さんのファイル：Q31_2009_MUJIRUSHI_SAN.LZHをダウンロード

第３１問 Four Fours for Excel （数字を作る その２）

ガキの頃、、、いや少年期に遊んでいた計算式の作成
※ ４ のみを必ず４回使う。（３回以下、５回以上は×）
※ 加減乗除のみで１・２・３・４・５・・・・・・・・・と、限りなく作っていく
    と言うものでした。（目標１００まで、、、だったと思う）
これではエクセルではありませんのでアレンジを。
でもそのことにより難易度が大幅に下がったような・・・。
反面、より先の先まで作れるようになり、返って大変かも。

≪ 問題 ≫

※ Ａ１・Ｂ１・Ｃ１・Ｄ１ を必ず１回のみ使うこと （未使用・２回以上使用は禁止）
※ Ａ１・Ｂ１・Ｃ１・Ｄ１ 以外の数値（数字）の使用は禁止
※ １～ 順に作っていくこと （途中を飛ばして次に行くことは禁止）
    （色々な数字・数値を作ることが目的では無い  連続させることを目的としているため）
※ エクセルの問題なので関数の使用を各数値とも１種１個のみＯＫとします
    ただし先の問題「数値を使わず数値を・・・」とは違い
    関数のみで数値（数字）を作ることは禁止します
    =関数( ,"*")・・・・・・・・・・・禁止・・関数のみで数字が作られている
    =関数(A1,"*")・・・・・・・・・ＯＫ
    =関数(A1,2)・・・・・・・・・・・・禁止・・数値が使われている
    =関数Ⅰ(A1,関数Ⅱ(B1))・・・・・禁止・・関数の２個使用
    =関数Ⅰ(A1)+関数Ⅰ(B1)・・・・・禁止・・関数の２個使用
    =関数(A1,B1)・・・・・・・・・・ＯＫ
    =関数(A1,B1:D1,)・・・・・禁止・・範囲には入っていますが"Ｃ１"が使われていない
    =関数(A1,B1:D1,)+C1・・禁止・・Ｃ１の２度使用
数式の長さ等はまったく関係ありません  幾つまで続けて作れるか、、、だけ

≪解答例≫

  １：=SUM(A1,B1)/(C1+D1)
  ２：=A1/B1+C1/D1

-

-

-

-

-

-

-

解答用参考ファイル：Q31_2009.lzhをダウンロード

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（からくちさん出題）

***********************************************************************

尚、解答発表は8/22（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

第30問 上野発上越新幹線時刻表 解答例

出題者でありながら、お恥ずかしいですが完全版ではありません。

今週はお盆関係で忙しかったのと、曜日を一日勘違いしていまして

気がついたら時間が有りませんでした。(^^ゞ

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ますたあさん談）

A  209文字
=INDEX(A4:A39,MATCH(HOUR(I8),A4:A39,))&":"&INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,),IF(ISNA(MATCH(MINUTE(I8),INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,),))),1,MATCH(MINUTE(I8),INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,0),))+1))

B　190文字
=VLOOKUP(INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,)-1,IF(ISNA(MATCH(MINUTE(I8),INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,),))),1,MATCH(MINUTE(I8),INDEX(B4:F39,MATCH(HOUR(I8),A4:A39,0),))+1)),A42:B49,2,0)

*************************************************************************************

くまぷーのファイル：Q30_2009_KUMAPOOH.lzhをダウンロード

第30問 上野発上越新幹線時刻表

いつも楽しく遊ばさせてもらっています。(^^)

問題の修正が終わりましたので送ります。

特に問題文をつけませんでしたが常連さんなら分かりますよね。

新幹線に乗るおおよその時間を Ｉ8セルに入力します。
そのＩ8セルの時間に、最も近い発車時間の列車時刻を
時刻表から抽出できるように Ｊ11セルに式を作ってください。
また、その列車の列車名も Ｊ12セルに表示できるように式を作ってください。

※行き先は、高崎限定とします。
　 高崎は全ての列車が止まります。

ではではよろしくお願いします。
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ますたあ さん談）

（追記）

なお、指定できる時間は終電前としてください。

*

*

*

*

*

*

*

*

参考ファイル：Q30_2009.lzhをダウンロード

***********************************************************************

尚、解答発表は8/１５（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

第29問 じゃんけん 解答例

この一週間いろいろ作りましたが、最終日に最初から用意してあった47が二文字ダイエットできただけでした。
45
=MID("BA--",FIND(C5&E5,"00IACICAIIAACC")/3,1)
実はこれ、あまり気に入ってません。

寧ろからくちさんのC5&E5、関係演算子、Code禁止に触発されて作ったこちらの方が気に入ってます。
47
=MID("AB-AB",FIND(C5,"IAC")-FIND(E5,"IAC")+3,1)

Findが重なっていたり、ABが繰り返しているのを何とかしようとすると却って伸びちゃいました＾＾；
49
=MID("AB-AB",MMULT(FIND(C5:E5,"IAC"),{1;3;-1}),1)
52
=MID("-AB",MOD(FIND(C5,"IAC")-FIND(E5,"IAC"),3)+1,1)
他にもFrequency使ったものやらいくつかありますが、エンジンの考え方はこの二種類のみです。

　　　　　　　　　　　　　　　　　　　　　　　　　　（sakudaさん談)

第29問 じゃんけん

問題が切れそうなので、急きょでっちあげたものです＾＾；
例によって、一応可能であること（簡単にできるはずです）を確認しただけで、
どんな手があるのか知りませんｗ

当然、名前、作業セル、書式類の使用は禁止。
ネタ元のSheet2への参照も禁止です。

【問題】

F9で表示が変わります。

C：ぐー

A：ちょき

I：ぱー

として、結果欄I5に勝者をA、Bで表示してください。

なお、あいこの場合は　－　を表示します。

書式類、名前、作業セル等はすべて禁止、

Sheet2への参照も禁止です。

　　　　　　　　　　　　　　　　　　　（sakudaさん出題)

*

*

**

*

*

*

*

*

*

解答用サンプルブック：Q29_2009.lzhをダウンロード

***********************************************************************

尚、解答発表は8/8（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第28問 数値（数字）を使わず数値を作成 解答例

始めに、、、

＞自分では結論を出せないので
＞各自の自由とさせていただきます
これに対してのわたしの判断

（１）
Ａ１やＢ２等の１や２は番地であるので数字（数値）と思いますが
DEC２BIN関数　DECimal number to BINary
等の「２」は「to」であって数字（数値）とは別で
あくまで関数名中の一部分

（２）
一丁目等の１は地番であり数字（数値）と思いますが
八戸市や三戸郡三戸町等の８や３はその起源はどうであれ
地名であって数字（数値）とは一線を引くものと考えます

などなどから、、、
関数名の１部に使われているものは数字（数値）とは別と判断しました

※ わたしなりの解

考え方としては色々あると思います
例えば１１１１１１１１１＾２を応用する
などなど、、、

最終的にわたしが使ったのは（って、、、どれも使って見たけど）

１２３４５６７８９０＊２＝２４６９１３５７８０
　　　　　　　　　　＊３
　　　　　　　　　　↓
１２３４５６７８９０＊９＝１１１１１１１１０１０

２４６９１３５７８０を作るのは
問題である１２３４５６７８９０を作るのもあまり差の無いような
しかし１１１１１１１１０１０なら可也違ってくるのでは

と言うことで
：=(DEC2BIN(-PI())&N(""))/INT(PI()*PI())

他・・・７９文字までいろいろ似たようなのが幾数十

関数で出来ますか～？ 第28問 数値（数字）を使わず数値を作成

問題を作るって難しいですね。
何処に面白さを感じるか、、、
それこそ人それぞれではと思いますので。

関数を扱う上で算数は基本の１つでは。
と言うことでこの問題を作ってみました。
考え方としては算数なのであくまで小学校？。
でも意外とウッカリ ＆ それを関数で如何・・・。

（からくちさん談）
【問題】
≪数値（数字）を使わず数値を作成≫

数値（数字）を使わずに
１２３４５６７８９０ ( 12億3456万7890 )
この「数値」を式で作ってください。

ただしこれだけでは
ＬＥＮ関数を繋げる・適当な文字をＣＯＤＥ関数で繋げる
などなどで簡単に出来てしまいますので
下記の条件を付けさせていただきます。

（条件１）
タイトルにもあるように数字（数値）の使用を一切禁止します。
例えばセル番地（セル参照）の Ａ１ などの書き方もこれに相当。
（条件２）
下記の関数は使用禁止にします。
・ＬＥＮ                     ・ＬＥＮＢ
・ＣＯＤＥ
・ＳＵＭ                    ・ＳＵＭＰＲＯＤＵＣＴ
・ＲＯＷ                    ・ＲＯＷＳ
・ＣＯＬＵＭＮ              ・ＣＯＬＵＭＮＳ
・ＣＯＵＮＴ                 ・ＣＯＵＮＴＡ

また、他の問題同様に書式・条件付き書式などなども使用禁止です。

***********************************************************************

尚、解答発表は8/1（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第２７問 地獄の禁止関数 解答例

おもて：３４

すなおに・・・いきました。

=LEN($C2)-LEN(SUBSTITUTE($C2,E2,))

ほかにもあったが、・・・・まあ、・・・・出すほど出ない。

３３は、追求できなかった。

うら、、

１関数にこだわって、・・・・短いのは、お任せだった。

=

(SEARCH(E2,$C2&12345678&E2,9)=9)+

(SEARCH(E2,$C2&12345678&E2,8)=8)+

(SEARCH(E2,$C2&12345678&E2,7)=7)+

(SEARCH(E2,$C2&12345678&E2,6)=6)+

(SEARCH(E2,$C2&12345678&E2,5)=5)+

(SEARCH(E2,$C2&12345678&E2,4)=4)+

(SEARCH(E2,$C2&12345678&E2,3)=3)+

(SEARCH(E2,$C2&12345678&E2,2)=2)+

(SEARCH(E2,$C2&12345678&E2,1)=1)

長いけど、美しいでショーー

のんで、書いたので、間違ってたら・・・ごめん、、

からくちさんの問題の答えは、・・・・あとで、、、

（あまり、ユーモアのあるものには仕上がらなかった)

　　　　　　　　　　　　　　　　　　　　　　　　　　（ジョー３さん談）

関数で出来ますか～？ 第２７問 地獄の禁止関数

問題を作りました

おもてもん：いろいろな解法があるだろうなーー

うらもん：禁止関数復活　3５名×2個＝6１０個も禁止されるのだ。＝大変かも、

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ジョー３さん談）

禁止関数

１．COUNTIF       （からくちさん）

２．SUBSTITUTE　（からくちさん）

３．FIND　　　　　　（supermabさん）

４．MATCH　　　　（supermabさん）

５．COUNT      （ますたあさん）

６．SUMPRODUCT     （ますたあさん）

７．MID            （ジョー３さん・出題者）

８．SUM   　     （ジョー３さん・出題者）

９．LEN　　　　　（くまぷー）

１０．LENB　　　（くまぷー）

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

Q27_2009.lzhをダウンロード

***********************************************************************

尚、解答発表は7/25（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第26問 足し算 解答例

それではまずは出題者であるsupermabさんからです。(^O^)

************************************************************************************

今回、
せっかく出会うことの出来た「関数でできますか？」を
大切にしたいという思いから、、、

「問題が不足してるよーーー」ってことで、、、
身のほどもわきまえずに、出題させていただきましたwww

しかしながら、当初は自らの実力を上回る皆様に対して、
出題することがいかに難しい事か？

ちっとも考えては居りませんでした。

すなわち！

自分には出来る問題＝あまりにも簡単

なので、つまらないのではないか？
（そーではないことに、今は気づいた！）

でも、

到底無理なものではいけない！
（そりゃそーだと、今も思う）

つまり、自分にはできないけど・・・
皆様には、、出来る。。。
でも、たやすくは無い。。。
（この考えは間違っていた！）

そんな問題を考えたつもりでした。。。

でも、
皆様に知恵を絞っていただいて、、良かったです ＾＾；

今は、皆様の解答を楽しみにする気持ちでいっぱいです。

なので、
出題者の解答は、、、ちっとも参考になりませんけど・・・
悪しからずご了承ください m(_ _)m

皆様が、お考えになっていただき、、、
深く感謝申し上げます。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（supermabさん談)

「足し算」　出題者解答のアルゴリズムの解説：

１：計算パターンの解析
まず、はじめに基本的な計算のパターンについて考えると、
２数の正負の組合せによって、実際に行う演算は下記のように
加算の場合と、減算の場合に分けられます。

①A+B
②-A+（-B)=-(A+B)
③-A+B=B-A　or =-(A-B)
④A+(-B)=A-B or =-(B-A)

①と②の場合は、加算を行い、②の場合は結果に -1 を掛けます。

③と④の場合は、減算を行うわけであるが、、実際の計算では、絶対値の大きさによって、
A、Bどちらから引き算を行うか判断しなければならず、
絶対値が大きな数が負の場合は、符号付加の処理が必要となります。

このことから、６通りの計算パターンがあるとわかります。

２：分割計算の場合分けの解析
つぎに、２数の桁数が１５桁以上となる場合があることから、、
上記の計算は、分割計算しなければならない(のかもしれません）。
ここに、加算における繰り上げ処理と、減算における繰り下げ処理が発生するので、

この問題を回避するため、=B1+B3 による、上位桁の解を利用する方法を考えました。

すなわち、単純加算による計算結果を利用して、、
失われた下数けたの情報を取得・修正する方法です。

もし、計算結果が１５桁を超えなければ、修正の必要は発生しないので、効率的です。
よって、計算結果が１５桁を超えた場合のみ、補正をかけることにしました。

さて、①～④の計算において、どのように補正すればよいか考えてみると・・・

①の場合～　①の場合は、下数けたを切り出した数値同士を加算した結果の下数桁を切り出し
　　　　　　　　単純加算の解の下数桁と入れ変えればよい。

②の場合～ ②の場合も、①と同様に処理したのち、－１を掛ければよい。

③、④の場合～　絶対値の大きな数からの引き算となるので、繰り下がりのために、引かれる数は
　　　　　　　　　　　一桁おおきな数にして、計算しなければならない。
となります。

３：総合
上記1、２の問題を総合して考えると、

加算か減算か？（どちらも計算して、選択すればよい→積が正なら加算、負なら減算）
絶対値の大きいのはどちらか？（絶対値順でセルに入れる）
符号はどちらか？（単純加算の結果でわかる）
有効桁数は何桁か？

の４点を考慮すればよいとわかります。

ので、上記のような

作業セル＝１０個、総文字数＝２９３ 文字 　の式を出させていただきました。
（式で利用はしてませんが、式の意味をわかりやすくするために、名前を付けてます）

４：例外

で、
この式では、「桁数が大きく補正が必要な場合」　AND　「２数の絶対値の差が８桁に満たない場合」
に、B17セルが #VALUE　エラーとなって、正しく解を出力することが出来ません（はず）。

従って、この解答は「1000回クリアの一か八か版」って、ところです ＾＾；
が、
実際には、１０万回程度の試行では、エラーとなりませんでしたので、、、
若干不思議です。。。。

Q26_SuperMab_san.zipをダウンロード

**************************************************************************************

次はy sakudaさんです。

Q26_sakuda_san.zipをダウンロード

***************************************************************************************

む印さんです。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

一応1000回クリア版です、、

＜考え方＞
Ｂ1+Ｂ3が15桁を超えた下の桁は0となるので、Ｂ1とＢ3の下９桁での足し算してその部分を補う事にしました。

＜式のレシピ＞
Ｂ1とＢ2の符号は無視して、数字部分が　①大きい方の右端から９文字を切り出して1,000,000,000を加算し
②小さい方も右端から９文字を切り出し、Ｂ1とＢ3が同じ符号の場合は1をそうでない場合は-1を乗じて
①と②を合計すると、下９桁では符号に合った足し算が成立します。
MODで、その下９桁の部分を数値化して取り出しておき、B1+B3から取り出した文字数分下の桁をカットして
取り出した数値の左にドッキングさせて出来上がりです。


　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（む印さん談）

Q26_Muin_San.lzhをダウンロード

**************************************************************************************

TOさんです。

--------------------------------------------------------------------------

こんちは＾＾

笑わないでね～＾＾；

　　　　　　　　　　　　　　　　　　　　　　　　　　(TOさん談）

Q26_TO_SAN.lzhをダウンロード

***************************************************************************************

ジョー３さんです。

Q26_jyo3_san.lzhをダウンロード

***************************************************************************************

kirさんです。

q26_2009_kir_san.zipをダウンロード

関数で出来ますか～？ 第26問 足し算

今回の出題者はsupermabさんです。(^O^)

ワンセルでやるもよし、作業セル（条件あり）を使うもよしとのことです。

【問題】

F9 キー を押すと数値が変ります。

B5セルに式を入れて・・・

param-1(B1セル） と　param-2(B3セル) を加算した結果を

answer(B5セル）に表示してください。

※ 大きな数値も指数表記でなく、全ての桁を表示させてください。

*

*

*

*

*

*

解答用参考ファイル：Q26_2009.zipをダウンロード ***********************************************************************

尚、解答発表は7/11（土）7/18（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第25回 カルタ大会 解答例

皆さんに楽しんでいただけたようで何よりです。
盛り上がらなかったらどうしようかと心配しておりましたが、心配無用でしたね。

さて、今回の問題は「重み付け」を一つの式の中でどのように表現するかの問題でした。

コメントを入れてくれた方々は重々承知でしょうが、問題を解けなかった方のためにちょこっと書かせていただきます。

勝数だけであればＲＡＮＫ関数で簡単に順位が決められます。
しかし、勝数が同じ場合は得点が関係してきますので単純にＲＡＮＫ関数では決められません。

この得点は今回の場合最大で48×7＝336となります。
得点が百の桁で納まりますので、勝数をそれ以上の千の桁、万の桁に修正して
得点を足し算した後で、それぞれを比較すると簡単に順位を決められます。

たとえば、
勝数　　得点
4　　　　136
4　　　　130
3　　　　125
であったとすると。

4×1000＋136＝4136
4×1000＋130＝4130
3×1000＋125＝3125
と言うように修正して比較します。

通常は、作業セルを使って修正値を作ります。
その修正値のセルをＲＡＮＫ関数で参照して順位を決めます。

ただし、ＲＡＮＫ関数は配列数式では使えません。
その辺もこの問題を作ったミソなんですね。

では、私の作った解答を・・・・・。^^;
(たぶん、皆さんに圧倒されてしまうでしょうが．．．。）

49文字
{=SUM(N($R$5:$R$12*10^3+$S$5:$S$12>=R5*10^3+S5))}

これをちょっとズルして＄を外したバージョン。(^^ゞ
41文字
{=SUM(N(R5:R12*10^3+S5:S12>=R5*10^3+S5))}

71文字
{=MATCH((R5*1000+S5),LARGE(($R$5:$R$12*1000+$S$5:$S$12),ROW($1:$8)),0)}
73文字
{=9-MATCH((R5*1000+S5),SMALL(($R$5:$R$12*1000+$S$5:$S$12),ROW($1:$8)),0)}
61文字
{=MATCH((R5*1000+S5),LARGE((R5:R12*1000+S5:S12),ROW(1:8)),0)}
63文字
{=9-MATCH((R5*1000+S5),SMALL((R5:R12*1000+S5:S12),ROW(1:8)),0)}

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ますたあさん談）

ますたあさんの解答ファイル：Q25_01_2009.lzhをダウンロード

関数で出来ますか～？ 第25回 カルタ大会

これまで出題されていない問題をと考えていましたら、
昔作ったシート(計算する仕組み)を思い出しました。

郷土カルタの「子どもカルタ取り大会」事務局の人から
自動的に計算する仕組みを作ってくれと、依頼され作ったモノです。
それを、問題用に作り直しました。

当時は、一つの式では難しそうでしたので、作業セルを使って
安直に仕組みを作ってしまいました。

でっ、今回改めてこの問題を一つの式で解いてみましたが、私にはかなり難しかったです。
でも、ここに参加されている方々ならばもっとすばらしい式（計算する仕組み）を
見せてくれるのではないかと
期待しています。

*

*

*

*

*

*

*

*

*

解答用参考ファイル：Q25_2009.lzhをダウンロード ***********************************************************************

尚、解答発表は7/4（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第24問 引き算 解答例

この問題のポイントは左辺の数字を如何にして切り取るか
に尽きると思っていたのですが、
皆さん予想出来ない発想で取り組んでいただいてるようで
答え合わせが楽しみです！


【解答例】

①切出した 左辺－右辺 を {-(左辺),-(-右辺)} の配列にして合計する方法。

78:{=SUM(-MID(SUBSTITUTE(C5,"-","
-"),MIN(FIND(ROW(1:9),C5&PI()))+{0,3},{3,6}))}

　　※minさんのお陰で思い出した規則を利用して短縮^^
71:=SUM(-MID(SUBSTITUTE(C5,"-","  -"),FIND(MID(C5,3,1),C5,4)+{1,4},{3,6}))


②ハイフンを中心に左辺へは一文字づつずらしながら文字数を一文字づつ増やし、
　右辺はハイフンから右を切出した文字を数値化して合計する方法。
（TEXT関数で、数字なら数字、文字なら"0"としてエラー対処）

78:=SUM(LARGE(-TEXT(MID(C5,FIND("-",C5)-{1;2;3;0},{1;2;3;4}),"0;-0;0;!0"),{1,4}))

77:=SUM(TEXT(MID(C5,FIND("-",C5)-{1,2,3,0},{1,1,1,4}),"-0;0;0;!0")*10^{0,1,2,0})


③は②と同じ手法ですが、便利なLOOKUP関数を使うことで、数値化でのエラーを無視し、更に、
　{0,999}という配列を検索値に指定することで最小値と最大値の配列も取得。

62:=SUM(LOOKUP({0,999},-MID(C5,FIND("-",C5)-{1;2;3;0},ROW(1:4))))

--
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（む印さん談）

関数で出来ますか～？ 第24問 引き算

　はじめは10回が目標でしたが

いつの間にか２ダース・・・

これも偏に皆々様のお陰です・・・・

さて、今回の出題者はむ印さんです　(^O^)

【問題】
上のシリアル番号はＦ９キーを押すたびに変化します。
そのシリアル番号の「-（ﾊｲﾌﾝ）」を挟む数字、右辺（大）から
左辺（小）を差し引いた数値を求めてください。

　　例） ＩＤ/Ｎｏ：Ｕｎ/225-529　の場合　答え：304

　　　※名前の定義、作業セルの使用は禁止です。

*

*

*

*

*

*

 

解答用参考ファイル：Q24_2009.lzhをダウンロード ***********************************************************************

尚、解答発表は6/27（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第23問 三百六十五歩のマーチ 解答例

わたしのエクセルはお遊びエクセル。
この様な素晴らしい企画に参加すること自体、躊躇いがあります。
ましてやその性質（お遊び）上、出題者側には不向きでは・・・。
にも拘らず多くの方々に取組んで戴けたご様子、
誠にありがとうございました。

　　　　　　　　　　　　　　　　　　　　　　（からくちさん談）

≪回答例≫
Ａ１から版

※ ２進数にしてしまえば ０か１のみなので
：=2-RIGHT(DEC2BIN(RIGHT(ROW())))

※ 考え方としてこんなのは結構好きな方
：{=MATCH(ODD(ROW()),ROW(1:2))}

※ これは有か否か・・・
：{=2-MOD(SUM(B$1:B1+1),2)}

その他・・・

Ａ２から版

※ Ｑ＆Ａ等では殆ど見かけない関数に愛の手を
：=NPV(A1,1,6)

※ 無意味なこの使い方 これも最初にＱ＆Ａ等に書いたのは多分わたしでは
：=HOUR(" :"&4-A1)

※ エクセル君は２月３０日も認識してくれる それなら平年と閏年とで
：=DAY(DATE(2010+A1,2,30))

その他・・・

関数で出来ますか～？ 第23問 三百六十五歩のマーチ

わたしは常々「式＝考え方」と言ってきました。
ある意味それが最も現れ易いだろう、
式を組む上での基本中の基本の様な問題にしました。
これなら可也の数の答えがあると思います。
ただし欠点はやさし過ぎることかな。
でも、どれだけ多くの式を作れるか・・・。
　　　　　　　　　　　　　　　　　　　　　　　　（からくちさん談）

≪問題≫

  Ａ1より下へ

      1・2・1・2・1・2・1・2・1

  この様に 1 と 2 を繰り返し表示させて下さい

*

*

*

*

*

*

*

*

*

*

解答用参考ファイル：Q23_2009.lzhをダウンロード ***********************************************************************

尚、解答発表は6/20（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

  但しこの問題は式の短さでは無く

  色々な式を出来るだけ沢山作って下さい

  わたしの大好きな無駄式大いに結構です

  と言いましても悪戯にやたら長い式は却下です

＜Ａ1から版＞

  Ａ1に式を入れてそれを下へ・・・で完成

 

＜Ａ2から版＞

  Ａ1には予め 1 が入れてあります

  そのＡ1の 1 を利用してＡ2に式を入れ

  それを下へ・・・で完成

＊

関数で出来ますか～？ 第２２問 文書校正Ⅱ 解答例

Q1：

こんな長いのも：６２：{=MAX((LEFT(C4,ROW(1:6)-1)&RIGHT(C4,7-ROW(1:6))=C6)*ROW(1:6))｝

短くなって：３９：{=COUNT(FIND(MID(C4,1,ROW(1:6)),C6))+1}

もしかして：３８：{=COUNT(FIND(LEFT(C4,ROW(1:7)),C6))+1}

忘れてた：３７：{=COUNT(FIND(LEFT(C4,ROW(1:7)),C6),)}

もひとつ：３８：{=28-SUM(FIND(MID(C6,ROW(1:6),1),C4))}

短縮？駄目だった：４１：{=SUM(FIND(MID(C4,ROW(1:6),1),C6&C4))-21｝

Q2：

駄目だった：７４：{=COUNT(FIND(LEFT(C4,ROW(1:7)),C7),)&7-COUNT(FIND(RIGHT(C4,ROW(1:7)),C7))}

あはは：とで：７８：{=COUNT(FIND(LEFT(C4,ROW(1:7)),C7),)&"と"&7-COUNT(FIND(RIGHT(C4,ROW(1:7)),C7))}

で、結局、皆さんの後を追うしかなかった。

＆みなさんは、さらに進んでいるし。・・・・完敗でした。

あと、エラーチェックも完璧ではない・・・・（心配ではある)

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ジョー３さん談）

関数で出来ますか～？ 第２２問 文書校正Ⅱ

　ジョーⅢリターンズ！！あの男が帰ってきた！！

【問題Ⅰ】

原文が「いろはにほへと」として、比較文はそのうちの１文字が欠落している。

例えば比較文を「いろはにほと」とすると６文字目の「へ」が欠落している。

そういう場合は「６」と表示させて欲しい。

【問題Ⅱ】

原文が「いろはにほへと」として、比較文はそのうちの２文字が欠落している。

例えば比較文を「ろにほへと」とすると１文字目の「い」と３文字目の「は」が欠落している。

そういう場合は「１３」、あるいは「1,3」、「1と3」、「1&3」、「1文字目と3文字目」と表示させて欲しい。

※使用は、関数のみで　表示形式、条件付等一切禁止。 

*

*

*

*

*

*

*

*

 

解答用参考ファイル：Q22_2009.lzhをダウンロード ***********************************************************************

尚、解答発表は6/13（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第21問 表の抜き出し 解答例

今回は正直なところ、自分であまり良い手法が思いつかなかったので、誰かにカッコ良い
ソリューションを見せていただきたくて出題したようなところがあります。
なんか、盛り上がらなくて申し訳ありませんでしたm(＿＿)m

で、私は配列数式でSMALLまたはLARGEを使用する方法しか思いつきませんでした。
いずれも配列数式で、G４をH4にドラッグ、その後28行までドラッグと言うことになります。

LARGEを使用した68
{=INDEX(A:A,29-LARGE(($C$4:$C$28=$E$3)*(26-ROW($1:$25)),ROW()-3),1)}

SMALLに切り替えた61
{=INDEX(A:A,SMALL(9^($C$4:$C$28<>$E$3)*ROW($4:$28),ROW(A1)))}

上の61を無理やりダイエットした58でH列の方では式が崩れており、結果オーライになっている
にすぎません。
{=INDEX(A:A,SMALL(9^($C$4:C28<>$E$3)*ROW($4:28),ROW(A1)))}

どなたか、全く別の手法を見せて下さるとうれしいのですが＾＾

                                                                                          （y sakudaさん談)

****************************************************************************************

★ますたあさんから頂いたファイルをアップしました。（2009/6/7 9:17追記)

Q21_master_san.zipをダウンロード

関数で出来ますか～？ 第21問 表の抜き出し

　この、表から一部を抜き出したいというのも、質問掲示板で

「簡単なことだと思うのですが・・・・・」と言う前振り付きで

時々見かけるような気がします。

こう言うのを処理するために、Excelはオートフィルタとか

フィルタオプションを用意してくれているので、

関数でやる必要は全くないわけで、そう答えてやれば良いのですが、

必ずレス付ける人がいるみたいですねーー。

質問者さんがちゃんと理解できるとは思えないんですけどねｗ

私はそういうの自分ではやる気もないので、

何時もチラッと見て流しちゃうのでどんな手法が

標準的なのか全く覚えてません。

　今回問題にならないかなと思いちょっとやってみたら、

結構難しいし長くなっちゃう。

で、問題にしたら、ここの常連さんがどんなテクを見せてくださるか

知りたくなってしまいましたｗ

と言うことで私はお見せできるような解答は持ってません＾＾；

　　　　　　　　　　　　　　　　　　　　　　　　　　　（y sakudaさん談)

【問題】

E5に注文主名を入れると、その注文主へ配送する商品の

一覧表が表示できるようにしてください。

１．G列、H列の式は同じでも別でもかまいません

２．表示形式のみ使用可（条件付き書式不可）

*

*

*

*

*

*

*

解答用参考ファイル：Q21_2009.lzhをダウンロード

***********************************************************************

尚、解答発表は6/6（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第２０問 何個あるでしょう？ 解答例

  今回,途中ＰＣトラブルで余りレス入れられずすいませんでした、、
からくちさんやminmaxさんのお陰で、皆さんなんとか一週間
お楽しみ頂けたみたいで助かりました(^^ゞ

一応、初期段階で私が用意していた解答だけ提示します。

■OFFSET使用
=COUNTIF(OFFSET(A4:C4,,MATCH(C9,B3:V3,)),F9)

■OFFSETなし
{=SUM(N(COLUMN(B:V)-MOD(COLUMN(B:V)-2,3)&B4:V4=MATCH(C9,3:3,)&F9))}



--
む印

関数で出来ますか～？ 第２０問 何個あるでしょう？

＜問題＞
図の表中から【質問】に表示される、曜日の数値が「何個在るか」

を【回答】枠に正しく表示する、できるだけ短い式を考えてください。

尚、名前定義および作業用セルの使用は禁止です！

＊

＊

＊

＊

図の表は、Ｆ９キーを押すたびに曜日と数値（1～3）が

ランダムに変化します。

また、【質問】の曜日と数値もランダムに変化します。

今回、くまぷーさんのものまねで

ＲＡＮＤ関数つかった問題を作ってみました。＾＾／

　　　　　　　　　　　　　　　　　　　　　　　　　　　　（む印さん談）

解答用参考ファイル：Q20_09.lzhをダウンロード 

***********************************************************************

尚、解答発表は5/30（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第19問 ストレート 解答例

　中間試験の時期に重なってしまい、せっかく頂いたコメントに

適宜・適切なレスを付けられなかったことをこの場を借りてお詫び致します。m(__)m

解答例）

ロイヤルストレート未対応：

{=VAR(IF(MOD(DATA!G1:K1,13),MOD(DATA!G1:K1,13),13))=2.5}

基本的には「関数で出来ますか～？」第７問「順子」と同じです。

ロイヤルストレート対応：

{=OR(VAR(IF(MOD(DATA!G1:K1,13),MOD(DATA!G1:K1,13),13))=2.5,AND(
MIN(MOD(DATA!G1:K1,13))=0,
SMALL(MOD(DATA!G1:K1,13),2)=1,
SMALL(MOD(DATA!G1:K1,13),3)=10,
SMALL(MOD(DATA!G1:K1,13),4)=11,
MAX(MOD(DATA!G1:K1,13))=12))}

これはロイヤルストレート未対応版にORで無理矢理ロイヤルストレートの

判定式を組み込んだだけです。スマートさには欠けますが

わかりやすいのではないかと思います(^^ゞ

さて、常連さんはどう攻めてくるでしょうか？

関数で出来ますか～？ 第19問 ストレート

【問】下図のようにB2:F3に5枚のトランプカードが表示される。

　　　　このカードはF9を押下することによって更新される。

　　　　そこで、ストレートならばH2にTRUE、そうでなければFALSEと

　　　　表示させて欲しい。

　　　　書式等は一切禁止。

＊

＊

＊

＊

＊

＊

＊

いろいろ考えた結果、できるだけ

簡単に答えられるようにしようと思います。

できるだけ多くの人に遊んでもらえるように

***********************************************************************

尚、解答発表は5/23（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

解答用参考ファイル：Q19_2009.zipをダウンロード 

***********************************************************************

関数で出来ますか～？ 第１８問 ツーペア 解答例

ツーペア：{=SUM(COUNTIF(B2:F2,LEFT(B2:F2)&"*"))=9}

配列数式を使って４０文字です。

何人かの方のコメント通り、右辺の定数をちょっと

変えることによって・・・

ワンペア：{=SUM(COUNTIF(B2:F2,LEFT(B2:F2)&"*"))=7}

スリーカード：{=SUM(COUNTIF(B2:F2,LEFT(B2:F2)&"*"))=11}

フルハウス：{=SUM(COUNTIF(B2:F2,LEFT(B2:F2)&"*"))=13}

フォアカード：{=SUM(COUNTIF(B2:F2,LEFT(B2:F2)&"*"))=17}

・・・の判定も可能です。

３種類の関数・・・と言っていた人はみんなこれかなぁ・・・

※フラッシュ・ストレートの解答を掲示することは

しばらくの間、ご遠慮下さい。(5/16 20:23追加)

関数で出来ますか～？ 第１８問 ツーペア

　【問】下図のようにB2:F2に5枚のトランプカードが表示される。

　　　　このカードはF9を押下することによって更新される。

　　　　そこで、ツーペアならばH2にTRUE、そうでなければFALSEと

　　　　表示させて欲しい。

　　　　書式等は一切禁止。

*

*

*

***********************************************************************

尚、解答発表は5/16（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

解答用参考ファイル：Q18_2009.lzhをダウンロード

***********************************************************************

関数で出来ますか～？ 第１７問 月額小計を簡単に求める方法はないでしょうか？ 解答例

問題送った後、丸で考えていなかったんですが、問題考えた時のイメージは式二つで
100超で大したバリエーションは無いと思ってました。
で、いきなり皆さんが自然体モードで100切りで、私は色々やっても100を切れない状態で
正直あせりました＾＾；
書式使用で50台って方が3人もいらっしゃるのでなんともなんですが、一応一週間もがいた
結果を晒します。

書式使用：30+31の61
=IF(N(B6),B6-DAY(B6)＞=B5,1)*B5
=IF(D5,SUM(C$4:C5)-SUM(E$4:E4))
D列の書式　m"月小計";;　E列の書式　#,##0;;;

なんとかならないかともがいてどうにもならなかった、
書式使用：31+31の62
=ISERR(0/(B6-DAY(B6)＜B5)/B6)*B5
=IF(D5,SUM(C$4:C5)-SUM(E$4:E4))

書式不使用：45+37の82
=TEXT(IF(N(B6),B6-DAY(B6)＞=B5,1)*B5,"m月小計;;")
=IF(D5＞"",SUM(C$4:C5)-SUM(E$4:E4),"")

物凄く長いのですが、初期に作成した書式使用で57+58の115
これは、サブトータル表示の判定も、サブトータルの算定方法も
割合と気に入ってます＾＾
=(COUNTIF(B5:B$34,"＜"&DATE(YEAR(B5),MONTH(B5)+1,0))=1)*B5
=IF(D5,SUMIF(B$5:B5,"＞"&DATE(YEAR(B5),MONTH(D5),),C$5:C5))

土曜日の晩までにもう少しなんとかしたいと思ってますが、もうアイデア
涸れたみたいですねーー

　　　　　　　　　　　　　　　　　　　　　　　　　（y sakudaさん談)

関数で出来ますか～？ 第１７問 月額小計を簡単に求める方法はないでしょうか？

この"関数でできますかー？"のコンセプトは
「やるだけなら誰でもできるが、上手い手は？」
と言うことだと思いますので、かなり主旨から外れるかもしれません。

これは以前私がプライベートなシートで後から月間集計が欲しくなり、
実際にやって見たものです。
勿論、現実には作業列を使って素直にやりましたが、作用列なしで純粋に式だけで
やろうとすると、結構厄介になりそうです。
なんか、問題にして見ると、初心者さんが質問掲示板に「簡単だと思うのですが、
どうも上手く行きません」と言ってきて、「アホッ」って言われる典型みたいですがｗ


作業列使って、ピボット集計したり、アウトラインでやれば一瞬でできると言う
突っ込みはなしですよーーｗ

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（sakudaさん談)

***********************************************************************

尚、解答発表は5/9（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

解答用参考ファイル：2009_Q17.lzhをダウンロード

***********************************************************************

サブトータル問題用の式書き出しマクロです。

実行すると、D5,E5の式と文字数をG、H列に書き出してくれます。

Q17Formula.basをダウンロード

関数で出来ますか～？ 第１６問 地震 解答例

なんか、変な問題になってしまいましたね。
普通にやるだけなら簡単なんですけどね。

以下全て式のみ（書式変更なし）
〔minmax系あり〕
３５文字
=IF(MAX(J10:L12),MAX(J10:L12)-1,"")

〔minmax系なし〕
=omakasesimasu(yattenai,dekinakatta,tanosimi)

〔循環なし（若干ずる）〕
（ツール→オプション→反復計算にチェックあり）
１１５文字
式内に「セル番地が書いてない」から
どこのセルでもいいからこの数式をいれて、
ほかにコピー
=IF(ASC(CELL("contents"))>"0",TEXT(CELL("contents")-MAX(ABS(CELL("row")-ROW()),ABS(CELL("col")-COLUMN())),"0;"),"")
循環なし若干ずる版は数式を入力した時点では循環参照となり
ます。震源地に数値を入力した段階で、循環が無くなります。
（下のステータスバーの再計算の表示が無くなりますね）
この若干ずるは有罪かな～？？

〔循環なし（若干ずる）でminmax系なし〕
１６３文字
=IF(ASC(CELL("contents"))>"0",TEXT(CELL("contents")-IF(ABS(CELL("row")-ROW())>ABS(CELL("col")-COLUMN()),ABS(CELL("row")-ROW()),ABS(CELL("col")-COLUMN())),"0;"),"")


〔循環なし（ほぼずる無し）〕
（ツール→オプション→反復計算にチェック無し）
１５２文字
=IF(CELL("address")=CELL("address",K11),"",IF(CELL("contents")<"",TEXT(CELL("contents")-MAX(ABS(CELL("row")-ROW()),ABS(CELL("col")-COLUMN())),"0;"),""))


〔参考～循環なし（若干ずるの没の１つ）〕
=IF(CELL("contents")<"",TEXT(CELL("contents")-MAX(ABS(CELL("row")-ROW()),ABS(CELL("col")-COLUMN())),"0;"),"")


この程度で許してください。。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（minさん談）

関数で出来ますか～？ 第１６問 地震

あの表計算一筆書きが帰ってきた！！

今回の出題者はminmaxさんです。

〔問題〕
任意の場所（セル）で地震が発生します。
地震発生地から離れるごとに震度が１づつ減っていきます。
それを１種類の数式を使って表現して欲しい。
ちょっと簡単かな～？ま、いいか。
なんにも難しいことを考えていませんが面白い方法も期待しています。

〔数式入力条件〕
数式は一種類とし、K11に入れた数式を他のセルにコピーしてください。
初期段階では全てのセルは空白となるようにしてください。
もちろんエラー表示もなしです。
計算手法として循環参照（反復計算）はありです。

〔震源地入力と震度表示〕
数式を入力後の表示範囲K11からAA27のいずれかのセルに数値を入力します。
入力されたセルが震源地の震度とします。
地震を想定しているので最大７までの数値で計算出来ればOKです。（あんまり関係ないと思いますが）
入力したセルから離れるごとに震度が１ずつ減っていき、震度が無くなった部分は空白となるようにしてください。
震度は０まで表示してください。
（震度０は人は感じない程度の地震）
例はS19に震度７と入れたものです。当然あらかじめ入力されていたS19の数式は無くなり、定数７に入れ替わっています。
震度５強等の強弱は省略です。

ちょっと物足りない方は
私？を使わないでやってみてください。

解答発表まではK11に入れた数式の文字数が何文字になったか宣言してください。

解答用参考ファイル：Q16_2009.lzhをダウンロード

***********************************************************************

尚、解答発表は5/2（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第１５問 A⇔200 解答例

解答式
■関数のみ（エラー表示）の６７文字
=VLOOKUP(B5,{"B",100;"A",200;"AB",300;100,"B";200,"A";300,"AB"},2,)

■条件付書式は使用し、表示形式は使用しない場合
６４文字
=IF(B5>"",MATCH(B5,{"b","a","ab"})*100,MID("ABB",3/B5%,B5%/3+1))
５４文字
=IF(B5＞"",SEARCH(B5,"BAAB")/1%,TEXT(B5%-2,"A!B;!B;A"))

■条件付書式と表示形式を使用、見た目重視の３６文字
=IF(N(B5),B5%-2,SEARCH(B5,"BAAB")&0)

+++ 書式の設定 +++++++++++++++++++++++++++++++++
基本のセル書式でフォントカラーを白にしておき
-----------------------------------------------
＜条件付書式＞
数式が：=IF(B5＞"",SEARCH(B5,"AB　"&B5)＜3,LEN(B5%)*B5%＜4)
でフォントカラーを黒に設定
-----------------------------------------------
＜表示形式＞
ユーザー定義：　"AB";"B";"A";@!0
++++++++++++++++++++++++++++++++++++++++++++++++


その他、色々あると思いますが、個人的にはCODE関数使った解答が
ウマくできなかったので、出てきたらいいなぁーと思っていますｄ

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（む印さん談）

関数で出来ますか～？ 第15問 A⇔200

今回の出題者はむ印さんです。　お題は「A⇔200」・・・・

●あるポイントカード会員は3ヶ月間の買物状況により下記のようにボーナスポイントが加算される

タイプ	ﾎﾞｰﾅｽﾎﾟｲﾝﾄ加算条件（3ヶ月間の買物状況）					ﾎﾞｰﾅｽポイント
A	10万円以上お買上の方					200
B	金額に関係なく、50日以上来店し、お買上頂いた方					100
AB	Aタイプ・Bタイプ両方の条件を満たす方					300

そこで、ちょっとしたお遊びを・・・

問題： セルD5に、セルB5で＜タイプ＞「A」「B」「AB」を入力すると対応する＜ボーナスポイント＞「200」「100」「300」を
逆に＜ボーナスポイント＞を入力すると＜タイプ＞を表示する式を考えて欲しい。
但し、つぎの条件を満たすこと。
1: 式で参照できるセルはB5のみとする。
2: セルB5の表示形式は「標準」とし、入力規則の「日本語入力」以外は設定禁止。
3: セルD5の表示形式及び条件付書式等は設定自由とする。
4: セルB5に入力できるのは「A」「B」「AB」又は「200」「100」「300」の6種のみで
それ以外が入力された場合、若しくは未入力の場合はD5セルは無表示とする。（B5が「BA」⇒D5セルの表示なし）
尚、入力文字は半角英数で、大文字・小文字は問わないものとする。
5: D5セルに表示する数字のType形式は数値・文字列を問わない。
6: 名前定義禁止。

参考ファイル：Q15_2009.lzhをダウンロード

***********************************************************************

尚、解答発表は4/25（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

関数で出来ますか～？ 第15問 お知らせ

出題者はむ印さんです。お題は「A⇔200」です。

これだけじゃ何のことかわからんだろうな～(^^ゞ

関数で出来ますか～？ 第１４問 文書校正 解答例

最初のヤツが・・・なくなった。

自分の脳をたどってみました。

左から、1個目、2個目・・・合わないところを探した。

でも、それだと、ROWを3回も使う。

からくちさんが、30台と、おっしゃるまでは、本気で短くしようとは思わなかった。

で、・・・・・

ってことで、自分の脳の動きを無視して・・・・・

３８：　{=COUNT(FIND(LEFT(C4,ROW(1:7)),C6))+1}

い　　はあるかな・・・・

いろ　　はあるかな・・・・

いろは　　はあるかなーー・・・・

・・・と、・・・脳の動き的には、無駄がありそうだけど、

エクセル的には、短いんですねーーー

その他も、皆さんと、きっと同じと思うので・・・・

もう一つだけまず・・・・・

かぶらないかもしれない、お遊びだけ・・・・

68文字：{=MAX((REPLACE(C4,ROW(1:7),1,1)=REPLACE(C6,ROW(1:7),1,1))*ROW(1:7))}

うらもんは・・・・

やはりおなじかなーーー

{=7-COUNT(FINDB(RIGHT(C4,COLUMN(A:G)),C6))}

むりやり・・・禁じ手をすり抜けただけの・・・・半ズル。

なんか、きちんと見てないかも、，，

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ジョー３さん談）

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

minmaxさんが興味があると言ってくれたので、調子に乗って

くまぷーの循環版を晒します。(^^ゞ

kumapoohQ14.lzhをダウンロード

関数で出来ますか～？ 第１４問 文書校正

今回の出題者はジョー３さんです。

お題は文書校正・・・

*******************************************************************************

人間の脳って凄いよねーー無意識のうちに問題を処理している。
入力文字の間違いだって、そう・・・

１）単なる入力間違い
２）1文字抜けている
３）3文字目と4文字目が逆（交換）
とかとか、どう処理してるんだろ？
・・・・で、その１・・・・

【問題】

文字の間違い探し：その１　、1文字間違いパターン

原文：いろはにほへと

入力：いろはにほひと

1文字だけ間違っているとする。何文字目が間違い?

（上の例の場合は６）

★おもてもん（通常編）
　　　　＊どんな関数を使っても良い
　　　　関数のみ、表示形式、条件付き等×
　　　　E6の式は?

★うらもん（大混乱編）
　　　＊ここの大家さんのくまぷーさんが
　　　禁止関数を2個指定できる＆
　　　先回出題者のsakudaさんが
　　　禁止関数を2個指定できる＆
　　　次回出題のむ印さんが
　　　禁止関数を2個指定できる
　　　げーーー大変だーーー
　　　E7の式は?

注：表、裏とも、入力文字は、
「いろはにほへと」のみ＆
どこか1文字が違っているのみとする

（間違ってないとか2文字間違い考慮不要）

※裏問の禁止関数

１．Left、Mid　sakudaさん指定

2.Len、If　　　む印さん指定

３．Find、Row　くまぷー指定

※解答用参考ブック

2009_Q14.lzhをダウンロード

***********************************************************************

尚、解答発表は4/18（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

***********************************************************************

★sakudaさんより、数式の文字数カウントアドインを頂きました。

　FormulaToText.lzhをダウンロード

式（配列数式でも可）を入れたセルを選択状態にして実行すると、式の右の列に文字列化した式を入れ、その下にLenを入れて文字数が分かるようになります。
よろしければお使い下さい。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（sakudaさん談）

関数で出来ますか～？ 第１４問 お知らせ

　第１４回の出題者は、あのジョー３さんです。

ジョー３さんが出題者ではただではすみません。

裏問で特別ルール！！

**********************************************************************

うらもん（大混乱編）

＊ここの大家さんのくまぷーさんが

禁止関数を2個指定できる＆

先回出題者のsakudaさんが

禁止関数を2個指定できる＆

次回出題のむ印さんが

禁止関数を2個指定できる・・・

                                                      （ジョー３さん談）

**********************************************************************

・・・・ということで、sakudaさん、む印さん

問題を見る前に禁止関数を２つ指定して下さいm(__)m

関数で出来ますか～？ 第1３問 素数 解答例

今回も、１００までなら、４０文字程でできることを確認しただけで出題し、
皆さんの短縮にあおられて真剣モードでチャレンジしましたが、全くアイデアが出ず、
32どまりで、またしても完敗でした。

当初用意していたものは素朴にやったもので、40を若干越えている代物で、
それを整理しても39どまりでした。
=SUM(N(MOD(B3,{1,2,3,4,5,7})=0),B3>7)<3

真剣モードに入ってからも、表裏とも32が精一杯＾＾;

表
{=SUM(N(MOD(B3,ROW(1:99))=0))<3}
{=COUNT(1/MOD(B3,ROW(1:99)))>96}

34とちょっと長いですが、こんなのも
{=SUM(SIGN(MOD(B3,ROW(1:99))))>96}

裏
{=COUNT(1/MOD(B3,ROW(1:99)))=97}
{=SUM(N(MOD(B3,ROW(1:99))=0))=2}

からくちさんやsupermabさんの回答を楽しみにしてまーーす＾＾

　　　　　　　　　　　　　　　　　　　　　　　　　　（ｙ　sakudaさん談）

************************************************************************

supermabさんの作品です。

supermab_san_Q13.zipをダウンロード

関数で出来ますか～？ 第1３問 素数

【´･ω･`】:今回はsakudaさんの出題です。

【問題】
B３に１～100の数が入力された時、それが素数ならD3にTRUE 

素数でなければFALSEと表示する。 

直、１は素数と判定してください。 

【裏問】　通常通り、１は素数ではないと判定して下さい。（くまぷー、付け足し）

素数：１と自分自身以外では割り切れない数 
 
名前の使用、ワークセルの使用は禁止です。 

解答用参考ブック：2009_Q13.lzhをダウンロード

尚、解答発表は4/11（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。

関数で出来ますか～？ 怪獣が現れた！！第12問解答例

　第１１問「かごめ」ではセルに表示される文字が１文字なので

CODE関数を使うことが出来ました。しかし、こんどは複数文字ですので

基本的には「怪獣」が表示されるセル番地を取得しなければ

なりません。

=SUM((B2:L12>"")*ROW(A1:A11))　・・・・（１）

=SUM((B2:L12>"")*COLUMN(A1:K1))　・・・・（２）

（１）式、（２）式を配列数式として使うことで

それぞれ行方向の位置、列方向の位置が求められます。

よってこれらとOFFSET関数を組み合わせて・・・

{=OFFSET(A1,SUM((B2:L12>"")*ROW(A1:A11)),SUM((B2:L12>"")*COLUMN(A1:K1)))}

・・・となります。

・・・で上式を減量して

{=OFFSET(A1,SUM((2:12>"")*ROW(1:11)),SUM((B:L>"")*COLUMN(A:K)))}

・・・６４文字となります。（エクセル２００７で確認）

************************************************************************

さて、む印さんからのお楽しみファイルです。

Q12_etc.zipをダウンロード 　

ジョー３さんのお楽しみファイルです。

2009_q12.lzhをダウンロード

・・・でお二方に触発されて、くまぷーもひとつ・・・

例によって、循環参照を利用したダブルループです。

2009_Q12_Jyunkan.lzhをダウンロード

関数で出来ますか～？ 第12問 怪獣が現れた！！

　今回の問題、「怪獣が現れた！！」は言わば「かごめⅡ」とも

言うべきものです。

【問題】

　セルB2：J10に怪獣が出現します。その怪獣の名前をセルN13に

表示して下さい。

*

*

*

*

解答用ブック：

2009_Q12.lzhをダウンロード

尚、解答発表は4/4（土）の８：００です。それまでは直接数式を

書き込むこと・ポイントとなる関数名を書き込むことはご遠慮ください。