関数で出来ますか～？ 怪獣が現れた！！第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をダウンロード

Comments

[投稿情報: y sakuda]

こんばんはーーー
最終的にはminmaxさんとむ印さんのヒントでHLookupにより56になりましたが、オリジナルでは、行、列を求める66が最高でした。
{=OFFSET(A1,SUM((2:10>"")*ROW(1:9)),SUM((B2:J10>"")*COLUMN(A:I)))}
56はマイナーチェンジで2種類ありますが、む印さんと多分同じだと思います。

[投稿情報: y sakuda]

2007だとくまぷーさんので動くでしょうが、反応速度はどうでしたかーーｗ

[投稿情報: む印]

５６は前回の５８と殆ど同じＯＦＦＳＥＴの方を
{=HLOOKUP("?*",OFFSET(1:1,SUM((2:10>"")*ROW(1:9)),),1,)}

５７は多分,minさんと同じだと思いますが
{=HLOOKUP("?*",2:2&3:3&4:4&5:5&6:6&7:7&8:8&9:9&10:10,1,)}

でしたｄ＾＾

[投稿情報: y sakuda]

56はやはりむ印さんと事実上同じ（前回のパクリだから当然ｗ）
{=HLOOKUP("?*",INDEX(2:10,MAX((2:10>"")*ROW(1:9)),),1,)}
{=HLOOKUP("?*",OFFSET(1:1,MAX((2:10>"")*ROW(1:9)),),1,)}

後、配列数式ではない62も作ってみました
=HLOOKUP("?*",OFFSET(1:1,MAX(INDEX((2:10>"")*ROW(1:9),)),),1,)

>５７は多分,minさんと同じだと思いますが
なるほど、こういう手もあるか＾＾；

[投稿情報: む印]

ファイルの方、
ジョー３さんと同じことしてましたねー＾＾

くまぷーさんの循環技は考えにも及ばず、勉強に成りましたm(__)m

そうそう、kirさんに指摘された定数参照方式ですが、

１つは、"?*"をセルＮ1にでもいれておくのと
もう一つは、ROW(1:9)をＡ1:Ａ9に1～9を入力しておくと
５６→５１に出来るということだと思います。

[投稿情報: くまぷー]

>2007だとくまぷーさんので動くでしょうが、反応速度はどうでしたかーーｗ

特に問題のないレベルかと・・・

[投稿情報: ジョー3]

>ジョー３さんと同じことしてましたねー＾＾
あらら、、、、・・・・

[投稿情報: くまぷー]

ジョー３さんの・・・技ありですね～
む印さんのピボットだの色々、ほんとうに
アイデァ豊富ですね～
◇＼(o・Ｑ・o)／◇降参です。

[投稿情報: くまぷー]

HLookupなんて夢にも思いませんでした。

[投稿情報: y sakuda]

>特に問題のないレベルかと・・・
そうか、今度のは各セルに変更を加えてないから早いのか。
前回のは物凄い数のセルに　&0　でしたから非実用的だった・・・

ジョー３のだまし、もっともらしい式までいれて念が入ってるwww

む印さんも良く色んな手思いつきますねーー

くまぷーさんの循環もややこしいことやってるｗ

[投稿情報: くまぷー]

>ジョー３のだまし、もっともらしい式までいれて念が入ってるwww

Phoneticが効くのかと思って
色々試してしまいましたよ　(^^ゞ

[投稿情報: minmax]

むさん・・・
＞５７は多分,minさんと同じだと思いますが
さすが、お見通しですね。
同じだと思いましたが、違うと面白いなあと期待してました。

sakudaさん・・・
＞配列数式ではない62も作ってみました
思いもよらなかった。

＞そうそう、kirさんに指摘された定数参照方式ですが、・・・
私もそれで５１になるとは思っていました。

＞くまぷーさんの循環技
循環はちょっとやってみましたが捕まえたところで計算を止めることが出来ずに諦めました。

＞ジョー３のだまし、もっともらしい式までいれて念が入ってるwww
ほんとですね。
それにしても、よくまあ四角形を重ねたもんですね。
よくやるなあ。（むさんも）

むさんのファイルの方・・
DGETでやろうと思って色々やってみたがワンセルではどうしても出来なくて・・・
リスト、、私もやりましたがやっぱり若干むりがある。
Alt+↓、、これもやろうと思いましたがうまくできませんでした。
ほんの一瞬だけどGETPIVOTDATAで何とか出来ないかと思いました。

自分の式は短いのは出ているので、出すほどのものはないのですが、
{=INDIRECT(ADDRESS(SUM((2:10>"")*ROW(2:10)),SUM((B2:J10>"")*COLUMN(B:J))))}
（セル番地を求めてからINDIRECTで求めているだけ）

{=OFFSET(1:1,MAX((2:10>"")*ROW(1:9)),) OFFSET(A:A,,MAX((B2:J10>"")*COLUMN(A:I)))}
（遊んでみただけ）
こんなんしかありません。

[投稿情報: minmax]

ひとつ書き忘れた。
?*でも*?でもうまくいく・・

[投稿情報: y sakuda]

>?*でも*?でもうまくいく・・
考えなかったですが、言われて見れば、どちらも一文字以上ないとマッチしないから良い見たいですね。

[投稿情報: ジョー3]

{=HLOOKUP("?*",2:2&3:3&4:4&5:5&6:6&7:7&8:8&9:9&10:10,1,)}

へーーー
＆でねーーー
，じゃないのねーーー
これで動くんだーーー
どなたが最初に・・・・ふーーん・・・でした。

[投稿情報: くまぷー]

{=HLOOKUP("?*",2:2&3:3&4:4&5:5&6:6&7:7&8:8&9:9&10:10,1,)}

そう、誰がこんな式を・・・
頭の中はどうなっているんだろう？

[投稿情報:]

＞{=HLOOKUP("?*",INDEX(2:10,MAX((2:10>"")*ROW(1:9)),),1,)}
なんか、考えの流れが、不思議・・・
（俺が、単純?）
で、長くなりそで、短いのも不思議。

[投稿情報: ジョー3]

＞それにしても、よくまあ四角形を重ねたもんですね。
＞よくやるなあ。（むさんも）

あはは、・・・やりながら、ちとはじかしかった。

[投稿情報: y sakuda]

>そう、誰がこんな式を・・・
最初に57と言ってたminmaxさんだと・・・
>頭の中はどうなっているんだろう？
minmax、む印、kir、からくち諸氏の頭の中は常識人には図り難いw
悪児さんが黒魔術発揮する時も同様ですがwww

[投稿情報: minmax]

57はむ印さんが先駆者のようですね。
私は何となくできそうと思っていたものを式に落としてみたら出来ただけ。

私はたまにひらめくことがあるだけの凡人だと思っています。
そんなに技術はない。

[投稿情報: かず]

＞minmax、む印、kir、からくち諸氏の頭の中は常識人には図り難いw

いえいえ、sakudaさんもお仲間ですよ。
私なんか、
{=INDEX(A1:J10,SUM((A1:J10<>"")*ROW(A1:A10)),SUM((A1:J10<>"")*COLUMN(A1:J1)))}
一つ思い付いただけで、週末を迎えてしまいました。

それにしても皆さん、色々思い付かれるもんですね。
回答を拝見するだけでも、σ（++）は重い疲れますよ。

[投稿情報: くまぷー]

minmaxさん、かずさん、どもです　(^O^)
いきなりですが・・・
お二方とも、解答だけでなく
出題の方にもまわって頂けたら・・・
「表計算一筆書き」のときのように　m(__)m

[投稿情報: minmax]

出題の件了解しました。
くまぷーさんからご指名とあらばいたしかたありませんね。
ただ、パソコンやっている時間が限られているので少々お時間をいただきたいと存じます。
私には解答を考えるより問題を作成する方がはるかに難しいので。
今のところこれといってアイデアありません。

[投稿情報: くまぷー]

>出題の件了解しました。

ありがとうございます。

＞少々お時間をいただきたいと存じます。

はい、もちろんです。
sakudaさん、ジョー３さん、む印さんから問題を
頂いているので、とりあえず３週間は大丈夫です。

ではよろしくお願いします。
（お手柔らかに・・・(^^ゞ）

[投稿情報: ますたあ]

書き込みが遅くなりました。(^^ゞ

先週からほとんど進歩がありませんでした。
わずかに、ここで教わった減量で多少短くなった程度ですかね。^^;

{=INDEX(B2:J10,SUM((2:10>"")*ROW(1:9)),SUM((B2:J10>"")*COLUMN(A:I)))}

この部分が B:J ではエラーになってしまうんですよね。
　　　　　↓
SUM((B2:J10>"")*COLUMN(A:I))
なんででそ。（？？）

[投稿情報: y sakuda]

>この部分が B:J ではエラーになってしまうんですよね。

■配列数式での列指定が可能
http://officetanaka.net/excel/excel2007/071.htm

[投稿情報: からくち]

この問題
少ない文字数でと捉えると
みなさん同様の式になってしまうのではと思い
拝見させていただくだけと思っていました

けど

＞minmax、む印、kir、からくち諸氏の頭の中は常識人には図り難いw
少なくともわたしの頭の中は至って見通しがいいです
何もありませんので （カラッポ）

と なまえが出ていたので

これまで拝見させていただいて
それぞれに受け持ちがあるような
短い式担当・凝った式担当・・・などなど

若しわたしが受け持つとしたら
やはり おあそび式 かな

と言う事で急きょ お一つ

名前 ： からくち
参照 ： =MMULT((B2:J10>"")*1,ROW(1:9))

{=OFFSET(A1,MATCH(0,0/からくち,0),SUM(からくち))}

[投稿情報: くまぷー]

ますたあさん、どもです(^^ゞ
書き込みありがとうございます。

ますたあさんも常連化の道まっしぐら・・・
しめしめ・・・(^O^)

[投稿情報: くまぷー]

からくちさん、どもです　(^O^)
提示された式は名前を使わないで・・・
=OFFSET(A1,MATCH(0,0/MMULT((B2:J10>"")*1,ROW(1:9)),0),SUM(MMULT((B2:J10>"")*1,ROW(1:9))))
・・・とすると配列数式にしなくてもOKなんですね～

[投稿情報: からくち]

＞・・・とすると配列数式にしなくてもOKなんですね～
はい その通りです

本当はＯＦＦＳＥＴも使いたく無かったのですが （キライ）
なまえと合わせた時の見た目を取りました

[投稿情報: からくち]

=INDEX(B2:J10,MATCH(0,0/MMULT(LEN(B2:J10),ROW(1:9)),0),
SUM(MMULT((B2:J10>"")*1,ROW(1:9))))

この方が好きなのですが・・・

[投稿情報: y sakuda]

>いえいえ、sakudaさんもお仲間ですよ。
どう見ても私はかずさんと同じ、常識的なおっさんですｗ
ジョー３もおっさんですが、あちらは思考の方向性がアルコールのせいか少し捻じ曲がってるような・・・・・

>少なくともわたしの頭の中は至って見通しがいいです
↓を見てるととてもそうはオモエナイｗ
=MMULT((B2:J10>"")*1,ROW(1:9))
確かに、配列を扱える式の典型なんですが、誰も使ってない＾＾；

[投稿情報: ますたあ]

sakudaさん、ありがとうございます。
そうですか、2007でしか出来ないんですね。^^;
ひとつ勉強になりました。

くまぷーさん、どうもです。
ちょくちょく拝見させてもらっています。
からくちさんやむ印さんのように
いくつも式を作れないですが．．．。
私なりにちょびっとね。(^^ゞ