関数で出来ますか～？ 第307回 結合セルの合計

　【問題】
　１）B2からF16までに、セルまたは2・3行結合したセルに
　　　1から100までの数字が入力されています
　２）なお、セルの結合の範囲は、3行単位になっています
　３）20行目に列の合計値を参考までに、表示してあります
　４）B18の回答式を右にコピーします
　５）なお、B18の式での参照は、B2:F16以外の範囲は禁止です

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（maronさん）

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

 

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

解答発表は03/14（土）の２０：００です。それまでは直接数式を

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

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

 

 

Comments

[投稿情報: jyo3]

結合セルですか、、、

セル内改行ですか
その数字を、合計ですか

すごーーい

[投稿情報: jyo3]

>２）なお、セルの結合の範囲は、3行単位になっています
E2:E3 は2行に見えますけど、、？？

[投稿情報: くまぷー]

>E2:E3 は2行に見えますけど、、？？

ああ、そう言えばそうでうすね。
B列の処理で思考が止まっていたから
E列まで見ていませんでした。(^^ゞ

[投稿情報: maron]

◆前回の問題は、べき乗やはては、三角関数など思わぬ発展をしましたが
◆今回は力技の問題ですが、みなさんの柔らか頭でのユニークな回答を待っています

[投稿情報: からくち]

質問です、、、
これ、、、2007でも出来ますか

[投稿情報: からくち]

出来ますね

[投稿情報: y sakuda]

からくりはわかったけど、さて・・・・

[投稿情報: くまぷー]

＞>２）なお、セルの結合の範囲は、3行単位になっています
＞>E2:E3 は2行に見えますけど、、？？　×2

maronさん、上の件はどうなっているのでしょうか？
私の今のやり方では対策に、面倒くささがもう一桁増えてしまいますので・・・

コメントをお待ちします。m(__)m

[投稿情報: からくち]

例えばB2に
１２
３４
５６
となっていたら当然ですが１０２
しかしこれは見かけだけで実際は123456です
12&char(10)&34&char(10)&56
であって8文字です
substitute(b2,char(10),)=123456
B列が
12 34 56
78 90 11
12 13 14
15 16 17
18 19 20
なら
B18は1367318
これが求める値では、、、
あくまでお巫山戯で式を書いて見ました

[投稿情報: maron]

＞>２）なお、セルの結合の範囲は、3行単位になっています
◆表現が紛らわしいですが、
◆E2:E3、E9:E10にしろ、E2:E4の3行のうち上の2行、E8:E10の3行のうち下2行です

◆という事です

[投稿情報: jyo3]

>３）20行目に列の合計値を参考までに、表示してあります
なるほど、、、
問題としては、おもしろいです。

でも、、、

うちの事務が、、、、

23+54+66を、、、
こんな風にセル内改行で、、、表を作ってきたら、私はどんな反応？
・・・・・・・・・・・・
って、考えてしまった。

[投稿情報: y sakuda]

からくり分かっても、とりあえずやってみた的なものすら出来ない・・・
最初がこうだと苦戦決定だな・・・

[投稿情報: jyo3]

>◆今回は力技の問題ですが、

うーーん、、力はないな――――

>柔らか頭でのユニークな回答を待っています。
あるんだーーー、、、きっと。

力は使わず、、、時々、ユニーク路線だけ、、、探します。

でもなーーー

先回も、、
-5???
???-5
すっかり忘れてましたからねーーー
・・・・・
その後脳の片隅で、、、ぼんやり思い出した

まあ、きっとしばらくは、、、りはびりりはびり、、、、

[投稿情報: jyo3]

あ、、、書き間違え、、

5-???
???-5

だった。おはずかしい。・・・パタパタ書くと必ず間違い。・・・

[投稿情報: からくち]

又々質問です、、、が
答え難いかな、、、
解答例は何文字位でしょうか、、、皆さん

[投稿情報: jyo3]

>力は使わず、、、時々、ユニーク路線だけ、、、探します。
だから、、、探せてない、、、、です。

[投稿情報: からくち]

短縮は苦手故既に終了です、、、142

[投稿情報: かたくち]

試しにもう１つ書いて見たら、、、133だった
どっちにしても3桁では、、、

[投稿情報: からくち]

名前が違う↑
かたくち→からくち

[投稿情報: からくち]

何時も問題を見ると幾つか頭に過ります
故にもう１つ書いて見たら、、、128

普通は最初に書いたのが短いのに、、、
今回は長い故、、、駄目見たい

↑同じのが2つも、、、ごめんなさい

[投稿情報: くまぷー]

>↑同じのが2つも、、、ごめんなさい

では一つ消しますね。

＞故にもう１つ書いて見たら、、、128

めちゃくちゃ短いと思いますよ。

[投稿情報: くまぷー]

からくちさんが「時間潰しに・・・」と言って出された問題にniさんがレスをつけていますよ。

[投稿情報: karakuti]

＞レスをつけていますよ。
ありがとうございます

＞短いと思いますよ。
本当ですか、、、
皆さんは２桁かと思っていました

[投稿情報: からくち]

＞とりあえずやってみた的なものすら
何時もの事ですが、、、取り敢えず書いて見た
今回もそんな何も考えていない式です
だから短縮は何時まで経っても苦手です
なんて、、、言い訳

[投稿情報: y sakuda]

やっと、とりあえずのできた。82（多分2013だと｛｝がいるから、84）
時間かかった原因はさび付いてて、関数の機能勘違いして、おかしいな・・・って言ってたためｗ
ただ、発展性に乏しそうな式・・・

[投稿情報: y sakuda]

あほなことやってたの修正して、72（74）
この方法だと頃辺りで終わりみたい。でも他に方法あるんかな？

[投稿情報: くまぷー]

＞＞>２）なお、セルの結合の範囲は、3行単位になっています
＞◆表現が紛らわしいですが、
＞◆E2:E3、E9:E10にしろ、E2:E4の3行のうち上の2行、E8:E10の3行のうち下2行です

＞◆という事です

コメントありがとうございます。
了解しました。　m(__)m

[投稿情報: くまぷー]

力技しか思いつきません。

当然・・・

＞あほなことやってたの修正して、72（74）

・・・なんて夢のまた夢・・・

[投稿情報: y sakuda]

>力技しか思いつきません。
大したことやってないです。
逆に位置特定して、それで切り分けて・・・・ってのが何も思いつかなかったので、若干きたならしく・・・・

しかし、くまぷーさんの英訳問題覗いてる人たち、「こいつらなにやってんだろ？」って思ってるの間違いなしｗ

[投稿情報: からくち]

やっぱ、、、２桁か

やって見た、、、う～～ん、、、
微妙に違う値が出る時がある、、、う～ん、、、
２桁はやはり、、、わたしには無理か、、、51
文字数は良いけど値は駄目、、、短縮止め

[投稿情報: ｎｉ]

72できました（２０１３）

[投稿情報: y sakuda]

あははっ、niさんにあっさり^^

[投稿情報: ｎｉ]

1桁や3桁が混在するので、切り分けが難しい
条件付き書式設定して何回かF9押して、
大丈夫そうだけど、毎回ポカやってるから自信なしです。

[投稿情報: くまぷー]

＞72できました（２０１３）

ぐは～！！

今、力技でコピペ、コピペで作ったら・・・

なんと・・・

口に出すのも躊躇われる・・

292

[投稿情報: karakuchi]

2007では見学が相応しいようだな

[投稿情報: karakuchi]

2007では見学が相応しいようだな

[投稿情報: くまぷー]

>しかし、くまぷーさんの英訳問題覗いてる人たち、「こいつらなにやってんだろ？」って思ってるの間違いなしｗ

確かに！(^^ゞ

しかし、「関数で出来ますか～？」を世間の人たちがのぞいたら、やはり
、「こいつらなにやってんだろ？」って思ってるの間違いなしｗ

[投稿情報: y sakuda]

細かく調整してギリギリ狙って、なんとか70（72）
でも、調整始めた時、ものすごく稀にエラーが出るので、結局ぶん回すマクロ作ってテスト
10万回の試行でエラー出なきゃ大丈夫だと思う＾＾
多分niさんと同じはず。
（こんなの理論的に可否考えれば済むんですが、考えてる内に自信がなくなったので、力任せのチェックに逃亡ｗ）

2007でダメなこと何もやってないです。
Excel97でもOKなはず。

[投稿情報: y sakuda]

ぶん回す方にマクロ提供です。
１．準備
　A25に試行回数。10000くらいが適当。スピードが速ければ、100,000でも大丈夫だとおもいます。
　B25に{=AND(B18:F18=B20:F20)}
２．VBA
　VBAエディタ開いて、標準モジュール一つ挿入
　
　そこに、↓をコピペ
Sub test()
Dim wFlg As Boolean, wLim As Long, wCnt As Long
With ActiveSheet
Application.ScreenUpdating = False
wFlg = True
wLim = .Range("A25")
wCnt = 0
Do While wFlg
wCnt = wCnt + 1
.Calculate
If WorksheetFunction.IsErr(.Range("B25")) Then
wFlg = False
MsgBox wCnt
Else
If Range("B25") = False Then
wFlg = False
MsgBox wCnt
End If
End If
If wCnt >= wLim Then
wFlg = False
End If
If wCnt Mod 100 = 0 Then
Application.StatusBar = wCnt
End If
Loop
End With
Application.ScreenUpdating = True
End Sub

３．実行
　開発タブのマクロから実行
　実行中、シート名の下のステータスバーに進行状況が表示されます。

[投稿情報: maron]

◆出題時、2013なので｛｝がついて、90

[投稿情報: maron]

◆少し頑張って、77

[投稿情報: maron]

◆整理して、75
◆今日はお休み

[投稿情報: maron]

◆短くはなりましたが、面白くもない式になりました

◆ところで、いつもなら週に3～4回公営のテニスコートで硬式テニスをしていますが
◆コロナウィルスの関係で3月前半は閉鎖のため、家の中に閉じこもっています

◆早く収束しますように

[投稿情報: くまぷー]

>◆短くはなりましたが、面白くもない式になりました

いや～、短いだけで十分ですよ。
こちらは長くて、かつ、面白くもない式です(^^ゞ

＞◆ところで、いつもなら週に3～4回公営のテニスコートで硬式テニスをしていますが
＞◆コロナウィルスの関係で3月前半は閉鎖のため、家の中に閉じこもっています

似たような境遇ですね。体がなまってしまいますよね。
昨日、仕方がないのでランニング用のシューズを買いました。

[投稿情報: からくち]

PCが古い為か、、、
2007だからか、、、分からないけど
最初に書いたのが75、、、でもエラー
その式 訂正を重ね最終的には
128で正しい結果を得られた

でも皆さんは2桁、、、
諦めた、、、でもと何故あの式がと思い
もう一度最初の式を書いて見た
正しく動く
一体何だったのかな、、、結果75

[投稿情報: からくち]

＞コロナウィルスの関係で
え～～、、、何で、、、あんな広いのに
と言う事は若しかしたら、、、
マサカとは思うけど、、、
ゴルフ場も、、、
プロの試合は中止になっているし、、、

医学なんて全く分からないけど実に不思議な決まりが、、、
相撲も報道陣が力士にインタビューをする時
近寄っては駄目、、、マスク着用、、、等など
共に感染者では無い筈
若しそれを疑うなら先に検査でしょう
非感染者同士が近寄ると駄目、、、巫山戯る

あっと、、、ここはこんな場では無かった
セコ減で、、、73
多分後幾つかは平気、、、でもこれで良い
64なら考えるけど１つ・２つでは、、、

[投稿情報: からくち]

やって見た、、、70が限界の様です、、、この式では、、、
違う考え方にしないと、、、

[投稿情報: y sakuda]

結局からくちさんに、niさんと私は抜かれてる見たい。
私は70ですが、2013では｛｝が付いて72ですから・・・・

[投稿情報: からくち]

わたしの式は若しかすると駄目かも知れません
ただ試せないので、、、
B列全てが
100
100
100
となった時果たして、、、
です

2007で｛｝付きです、、、

[投稿情報: からくち]

確認しました、、、結果は☓
1文字減らしたら、、、○
でもその式では
１
１
１
に対応出来ず、、、☓
従いまして
100
100
100
1
1
1
100
100
100
の様な事が出た場合少々違う値を返してしまいます
従いまして、、、☓

[投稿情報: y sakuda]

私のは、100，100，100と1，1，1を頭に置いて調整したのでOK
でも、本当にギリギリまで突き詰めてるかどうかは不明です。

[投稿情報: からくち]

2007で、、、結局74です
勿論ですが｛｝付きです
全く違う式を書いてみたら長くて止めた

それにしても、、、暑い、、、
今からこれでは、、、五輪なんて出来るのかな

[投稿情報: y sakuda]

70台の方ほとんど同じ手法でしょうね。
長くてもそれなりの正攻法を考えてるのですが、うまく行かないです。
>それにしても、、、暑い、、
東京は暑いってほどじゃないけど、暖かいですね。

[投稿情報: jyo3]

こちらはまだ、さむいです。

[投稿情報: くまぷー]

千葉はけっこう暖かかったな。

花粉症がつらい・・

[投稿情報: jyo3]

寒くて花粉もあるし

[投稿情報: からくち]

予想最高気温、、、23℃
実際は今現在窓をずっと開けていて22.3℃
昼間は25℃オーバー
ここは暖流の通り道故どうしても、、、

回答日までまだまだあるので
何か違う式を書いてみるかな
長さ等は無関係で、、、

[投稿情報: からくち]

＞私は70ですが、2013では｛｝が付いて72ですから・・・・
この式、、、2007でも可能ですか
つまり使用関数ですが、、、
若し可能ならわたしとは違う考え方か、、、

[投稿情報: y sakuda]

>この式、、、2007でも可能ですか
当たり前のものしか使ってませんから、97でもOKなはず。
さすがにもう持ってませんが。

[投稿情報: からくち]

そうか、、、違う式（考え方）ですね
わたしも72は書きましたが
例えばB列全てが100の時
1500に成りませんでした
恐らくその様な数値は出て来ないでしょうけど、、、

[投稿情報: y sakuda]

>例えばB列全てが100の時
>1500に成りませんでした
大丈夫ですけど、こんなのがランダムに起きる確率は事実上0ですねｗ
1か所だけAll　100　でも100万分の1ですもんね。

しかし、方針は同じようなものとしか思えないんですが。
他の方法は、力業にしても、200～300の現実的なもの思いつけてません。

[投稿情報: y sakuda]

今更ながらの話なんですが、Excel2013で
FormulaTextって関数追加されてるのご存じでしたか？
私は知らんかったｗ
試してみたら、ちゃんと｛｝にも対応してる。
ここでは役に立つけど、世の中こんなもの必要なのかなｗｗｗ
2003の頃これが欲しくて、式の文字列化なんてAddin作って使ってたんだけど、用済みですねｗ

[投稿情報: maron]

◆文字といえるかどうかわかりませんが、LEN関数で、70
◆一応全部100や1の場合も、OKです

[投稿情報: jyo3]

>FormulaTextって関数追加されてるのご存じでしたか？

知りませんでした。で、試してみた、、、

=LEN(FORMULATEXT(INDIRECT(F2)))
なんて書いておいて、
f2に、、文字数を知りたい番地をかけば、、、まあ、、、

便利と言えば便利だが、・・・

[投稿情報: くまぷー]

エクセル４のEvaluate関数と同じ機能の関数はないのだろうか・・・
以前、エクセルコンテストで「名前」をつけて使う例があったが・・・

[投稿情報: y sakuda]

＞Evaluate関数
私もそんなのなかったかしら？って思ったｗ
VBAでは似たようなことできるんで・・・

[投稿情報: からくち]

＞「名前」をつけて使う例が
2007とか以前のバージョンでは名前でないと使えないので

この問題（2007）
若し自身の表で使うのなら、、、82
（実際はセルの結合＆配列数式は使わない）
欲張っても本当なら、、、78
でもより短くと言う問題なので、、、74かな
この問題では実際現れないであろう数値を無視すればもっと短くなりますが、、、

[投稿情報: min]

やってみました。
７０でしたが、これは誤りが生じるのでしょうか？

おそらくこれで終わりかな。

[投稿情報: min]

あ、2007です。

[投稿情報: ｎｉ]

>７０でしたが、これは誤りが生じるのでしょうか？
わぁ、すごい！
私のやり方では、1,1,1と100,100,100が通ればOKになります。
違うやり方が思いつかない（＞＜）

[投稿情報: maron]

＞７０でしたが、これは誤りが生じるのでしょうか？
◆maronも70ですが、minさんの70を見てみたいですね

[投稿情報: くまぷー]

お早うございます。
依然として292！！
話になりません　orz

[投稿情報: からくち]

提示されている表なら、、、72でも平気でしたが
その式では70は駄目でした（2007）
どんな考え方なのかな、、、楽しみです

[投稿情報: からくち]

通常問題を見ると幾つかの考え方が頭を過ります
後はそれを書くだけ
そしてその式、、、短縮を試みますが苦手故駄目
しかし2～3文字位の違いなら、、、と思い表を眺めていたら
考え方では無く、、、違う問題が頭に過ぎってしまった

若し、、、maronさんのお許しが出るなら、、、
問題が少なそうなので出題してみます
ただしこの問題の様に面白くは無いと思いますが

[投稿情報: maron]

＞若し、、、maronさんのお許しが出るなら、、、
◆そんなこと言わずにからくちさんの問題を見てみたいと思います

◆今回の回答も昔、からくちさんが答えられた式がベースになっています

[投稿情報: くまぷー]

>＞若し、、、maronさんのお許しが出るなら、、、
>◆そんなこと言わずにからくちさんの問題を見てみたいと思います

では、からくちさんの出題はジョー３さんの次ということで・・・m(__)m

[投稿情報: からくち]

ありがとうございます

許可が出ましたので送りました
この問題と表（見た目）は同じでも
面白さは可也落ち
解いてみたら易し過ぎました

ごめんなさい

[投稿情報: jyo3]

>解いてみたら易し過ぎました
ほんと？、、、なら、、、やってみるかも、、、

いやーーー
今回、ひらめき、、、だけを頼りに、、、、

待ってましたが（ぴかっ）・・・・

なーーにも、閃かなかったです。

だから、、、・・・・だめかも、、、こたえ、、、

[投稿情報: くまぷー]

>許可が出ましたので送りました

拝受致しました。
有り難うございますm(__)m

[投稿情報: くまぷー]

＞なーーにも、閃かなかったです。

ひよっとしてジョー３さんだけがお友達？

[投稿情報: からくち]

いくら何でもバカにするなと怒られそうなくらいの易しい問題
余り意味ないでしょうが使用禁止関数を設けて見ました
くまぷーさんご面倒お掛けします

使用禁止関数は今の問題で使用していると思う関数にしてみました

[投稿情報: ｎｉ]

66が出来たみたい(^^)v

[投稿情報: y sakuda]

>66が出来たみたい(^^)v
なにやったんだろ？

[投稿情報: min]

69なら出来るかも
文字列でも良いなら67でも良いかも
66は?

[投稿情報: maron]

◆いよいよ60台突入、みなさんの解答を見るのが楽しみです

[投稿情報: ｎｉ]

>なにやったんだろ？
関数を1つ減らして4種4関数にしました。

>文字列でも良いなら67でも良いかも
合計を文字列で求める？　・・・謎

[投稿情報: min]

わたしのは誤りが生じるようです。
70、69などといったのは取り消します。
すみません。

[投稿情報: からくち]

想像通り60台か、、、わたしはギブですね
何時もですが見て直ぐ書きます
それ以上の進展は出来ないので
表を眺め考える振りをしているだけかも
頭の中では違う事を考えているのかな

しかしこの表自体が素晴らしいですね
良く考えましたね
いろいろな問題が浮かびますね

[投稿情報: maron]

◆無理やり関数を少なくして、4種4関数で69

[投稿情報: min]

改めて70ってのを作ってみました。

今度は大丈夫か？
自信なしです。

[投稿情報: min]

あれ、余計な4文字を残したままみたい。
であれば、66か。

パソコン使えないのですが、、

とすると文字列で良いのであれば、64かな？

[投稿情報: maron]

◆くまぷーさん、解答案を送りました
　maron

[投稿情報: からくち]

＞4種4関数で69
皆さん4種4関数ですね
わたしは5種5関数、、、70も
どれが要らないのかな
それとも根本的に違う考え方なのかな
楽しみ、、、

[投稿情報: maron]

◆maronも5種5関数の70でしたが、無理やり関数を減らして、4種4関数69になりました

[投稿情報: からくち]

何時以来かなヘルプをクリックしたのは

「Excel 2007 が現在の廃止します。」

これ、、、何語、、、
少なくても日本語には思えない、、、

[投稿情報: くまぷー]

>◆くまぷーさん、解答案を送りました

有り難うございます。
お世話になります　m(__)m

[投稿情報: jyo3]

さてさて、

みなさんすごいですねーーー
・・・・・・・

でも、、、似た手法でしょうか？・・・・
・・・・・・・・

それもまたすごい。・・・・

その他の手法がないということ。
でも、
60代70代が、、、別手法なら、、、それはまた、またまた、、、すごすぎ。

ですね、、

[投稿情報: jyo333]

ここ1年間と、少し

昨年1月ごろから、、、
今年、、、3月ごろまで、、、、
まだ少し、、続いてるけど、、

ま、、、ぼつぼつ、、、
力が、出そうになってきたけどね、、、

エクセルと、詰め将棋は、、、にてるなーーー

[投稿情報: jyo3]

詰め将棋の分野、、、

一番好きなのは、、、実践型詰め将棋、、、

それでいて、感動的な手順があると、、、すごくうれしい。

[投稿情報: jyo3]

こんなコマ配置、、、ありえないよねーーー

と、、いう、、そんな、、、

詰め将棋もあったりする。

でも、煙とか、、、それも感動的。・・・・

変な、ルールの・・・バカ詰み、、とか・・・