関数で出来ますか～？ 第345回 くまぷーさんへの挑戦状

1つの■のセルに1桁の数値が入っている。
■は４つあるから、全部で4つの数値が隠れている。
その数値を表の左から右へ、二列目以降も同じように取り出し
4桁の数値を表示させるんだ。
表は２つあるからその両方を解析するんだ。
で、突然でごめん。

・・・この男、どこのだれかは知らないが、そんなに悪党ではないような気がする・・・
・・・何を要求しているのかさっぱりわからない・・・

【問題】
表に■のそれぞれのセルに１から９の数値が隠れている。
■のセルは合計４つあるので、その数値を1つのセルに４桁の数値にして表示してほしい。
4桁の数値は1行目かの左から右、2行目以降も左から右に順番に探して４桁の数値にしてほしい。
練習問題では、■のセル以外は空白が入力されている。
問題では、■のセル以外は文字が入っている。

シートには挑戦状という趣旨から保護がかかっているが、解除パスワードは設定されていない。
勝手に解除してもらって構いません。
４桁の数値を表示するセルは指定しません。（影響はないでしょう）

割と簡単な問題かと思います。
くまぷーさん達には有利な問題だと思います。

　　　　　　　　　　　　　　　　　　　　　　（minさん）

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

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

出題者を含めて４名の方の解答作成完了宣言の後に、このスレに直接解答式をお書きください。

この問題に関しては解答用の別スレは作成いたしません。

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

Comments

[投稿情報: くまぷー]

関数を使わずに済ませると「練習問題」は48ですね。
やはり、あまり長くならない問題がいいです。(^^ゞ

[投稿情報: からくち]

＞長くならない問題がいいです
大賛成です、、、

質問です
表示の順番は重要ですか
例えば4隅に
１　　４

２　　３
とあった時は
1423
で無くては駄目ですか
1234とか1243とかでは、、、

[投稿情報: y sakuda]

2016以降だと問題にもならないけど・・・・
案外面倒かも。
今日、明日ほとんどできませんが・・・・

[投稿情報: min]

＞1234とか1243とかでは、、、
基本は1423でお願いします。
それ以外は番外編
ということでお願いします。

それにしても、
前回難しくてすみません。
今回簡単ですみません。

ま、つなぎ程度の問題ということで。

[投稿情報: y sakuda]

今のとこ、問題も練習問題も同じ式
しかも二けたの上の方と長いｗ
あまり良い手が思いつきません。

[投稿情報: からくち]

ありがとうございます

練習問題
基本編＋番外編、、、80台で終了です

問題は後で、、、

この問題PART2が頭に浮かんでしまった

[投稿情報: y sakuda]

左上隅と右下隅に数字があると正しく出ない。これちょっと厄介だな・・・

[投稿情報: くまぷー]

えへへへ、挑戦されてしまたのなら・・・

練習14
問題30

[投稿情報: くまぷー]

＞、、、80台で終了です

★終了宣言
1.　からくちさん
2.　
3.　
4.　

[投稿情報: min]

くまぷーさん
すみません。
からくちさんは、練習問題のみ終了で問題はこのあとかと？

皆さん、すみません。。
また想定外をやってしまいました。

答ありきでやってましたので、
セルの個数が16個ということは有効桁数オーバーですね。
普通？にやると結構面倒かも？？

セルの個数は考えていませんでした。
またまたほんとに初歩的なミスですみません。。

適当に作るとこういうことになる。反省

[投稿情報: min]

＞えへへへ、挑戦されてしまたのなら・・・

＞練習14
＞問題30
くまぷーさんできれば文字列でなく、数値にしていただきたいと思います。
それにしても、問題の30は想定外。

やはり、、1勝1敗の引き分けですが。。

[投稿情報: からくち]

終了です
練習、、、18
問題、、、20

[投稿情報: ｎｉ]

普通にやらなければ＾＾
練習　＋　問題　で　36

[投稿情報: min]

＞問題30
これは計算間違い想定内でした。

[投稿情報: min]

＞終了です
＞練習、、、18
＞問題、、、20

＞普通にやらなければ＾＾
＞練習　＋　問題　で　36

この解答はやはり想定内でした。。

やはり簡単でしたね。。
よかった。

[投稿情報: くまぷー]

＞からくちさんは、練習問題のみ終了で問題はこのあとかと？

＞終了です
＞練習、、、18
＞問題、、、20

★終了宣言
1.　からくちさん
2.　
3.　
4.　

[投稿情報: くまぷー]

＞くまぷーさんできれば文字列でなく、数値にしていただきたいと思います。

それならば32です。

[投稿情報: くまぷー]

＞問題、、、20

げげっ！！

[投稿情報: min]

＞練習　＋　問題　で　36
これが最短だと思います。

[投稿情報: ｎｉ]

練習　17
問題　19
です。

[投稿情報: ｎｉ]

AB列から右に数値がなければ15+15だったのに

[投稿情報: min]

＞これが最短だと思います
2007ならですが。

[投稿情報: からくち]

練習と問題、、、全く同じ式です
文字数が違うのは範囲の違いです

[投稿情報: min]

＞15+15だったのに
15+17?

[投稿情報: ｎｉ]

＞15+17?
あ、そうだ＾＾

[投稿情報: からくち]

例えば1234が答えだったとして
1.234では駄目ですか
若し良いなら
練習16
問題18

因みにわたしのPCでは
範囲名を縮小した書き方では同じ値が見えるのですが
確定では０で正しく表示されないので
14＋16にはなりません

[投稿情報: からくち]

＞練習　17
＞問題　19

＞練習、、、18
＞問題、、、20

この1文字違い、、、分からない
何処を減らすのか、、、
根本的に違う式なのかな、、、
偶々同じ様な文字数になっただけで
因みにわたしはご存知かもですが、、、2007です

[投稿情報: y sakuda]

なんだか、信じられないような話になってるな・・・
2016ならどちらも30ちょっとで片付くのは分かりきってますが・・・
15～20って・・・

[投稿情報: くまぷー]

>この1文字違い、、、分からない

それよりも、なぜ「問題」を１９文字とか２０文字で
できるのか、、、分からない

[投稿情報: からくち]

＞、、、分からない
ヒントになるか否か分かりませんが、、、

＞例えば1234が答えだったとして
＞1.234では駄目ですか
＞若し良いなら
＞練習16
＞問題18
以前関数を弄り回していた時
理由は分かりませんが、、、
ええっと思った事思い出しました
そのままの使い方、、、

[投稿情報: くまぷー]

＞そのままの使い方、、、

うーん・・・(^^;)

[投稿情報: からくち]

＞うーん・・・(^^;)
ですよね、、、
この使い方、、、
Q&Aの質問では使い道無いので発表した覚えがありません
多分ですが、、、知っている方いるのかな
少ないだろうな、、、意味ないもの
わたしもずっと以前偶然に、、、です
やっと使い道があった

[投稿情報: からくち]

ジョー３さん、、、見てるか～
藤井聡太君が勝ったぞ～～

[投稿情報: くまぷー]

＞ジョー３さん、、、見てるか～

私は見ていました。
木村王位（地元）に続き、渡辺３冠（最早２冠）までも・・・
途中までは優勢だったのに・・・

[投稿情報: y sakuda]

＞ジョー３さん、、、見てるか～
当然みてるはず^^
思えば、藤井4段の29連勝からここ再開してましたねーー

[投稿情報: min]

藤井さんすごいですね。
とても17歳とは思えませんね。

で、この問題、18とか20とかでやっている方法を止めると結構大変。

でとりあえずやってみましたが、

練習問題76
問題85

でした。

あれを使わなくてももう少し簡単な問題の予定だったのに。。。

[投稿情報: ｎｉ]

違う方法だと
練習103
問題107

[投稿情報: からくち]

＞18とか20とかでやっている方法
方法とは、、、これ2007でも使えますか
わたしの18＋20は普通の関数式なので
方法と書かれると、、、分からない

若しかして、、、なら
練習、、、48
問題、、、127
これ以上長くなるなら考え直しが必要かも、、、
この文字数は書いていません
計算上ですが、、、

[投稿情報: min]

あ、関数なしなら練習問題は48ですね。

[投稿情報: からくち]

ところでまだわたし以外何方も終了していない
凄く小さな文字数なのに、、、
もっと少ない文字数になるのかな、、、

[投稿情報: min]

問題は82でも

[投稿情報: くまぷー]

maronさんはどうしているんだろう？

[投稿情報: からくち]

若し、、、コロナ騒動が無ければ
来週オリンピックが始まる
こんな天候で出来るのかな
それを思うと
東京は、、、日本は、、、
果たして、、、

[投稿情報: ｎｉ]

＞練習、、、48
あ、なるほど、これは分かりました。

＞問題は82でも
これは分からない＾＾；

[投稿情報: y sakuda]

2016なら、面白くもない式で、16と34
でもそれ使わないと、81と99
もうちょっとなんとかしたいけど・・・

[投稿情報: min]

>もうちょっとなんとかしたいけど・・・
もうちょっとやってみました。

私の好きな関数、sakudaさんなど皆さんは避けている関数で
問題80
かな。。

[投稿情報: min]

＞出題者を含めて４名
ということなので、一応終了しておきますね。。

[投稿情報: くまぷー]

＞＞出題者を含めて４名
＞ということなので、一応終了しておきますね。。

★終了宣言
1.　からくちさん
2.　minさん
3.　
4.　

[投稿情報: min]

＞maronさんはどうしているんだろう？

maronさんもそうなんですが、
ジョー３さんは元気なのだろうか？？？？？？

[投稿情報: ｎｉ]

私も終了＾＾

[投稿情報: くまぷー]

＞maronさんもそうなんですが、
＞ジョー３さんは元気なのだろうか？？？？？？

飽きてきただけならばいいのですが・・・

[投稿情報: くまぷー]

＞私も終了＾＾

★終了宣言
1.　からくちさん
2.　minさん
3.　niさん
4.　

[投稿情報: min]

＞＞練習、、、48
＞あ、なるほど、これは分かりました。

くまぷーさんの名誉のために

一番最初に48を宣言されたのはくまぷーさんですよね(^_^)

[投稿情報: くまぷー]

＞私も終了＾＾　×２

★終了宣言
1.　からくちさん
2.　minさん
3.　niさん
4.　くまぷー

[投稿情報: くまぷー]

＞一番最初に48を宣言されたのはくまぷーさんですよね(^_^)

有り難うございます。
では・・・

「練習問題」４８：
=D4&E4&F4&G4&D5&E5&F5&G5&D6&E6&F6&G6&D7&E7&F7&G7

[投稿情報: からくち]

=NPV(9,D12:G15)*10^4

何方も同じ式で平気です
範囲名を変えるだけです

[投稿情報: y sakuda]

なるほど、NPVか
なぜかそっちには頭がいかなかったですねーー
くまぷーさんの48、そう言えば、関数要らないって言ってたなｗ

結局大したもの思いつかなかった
練習　78
=MMULT(10^{3,2,1,0},--RIGHT(SMALL(-(({4;3;2;1}&{3,2,1,0})&0&D4:G7),ROW(1:4))))
問題　96
=MMULT(10^{3,2,1,0},--RIGHT(SMALL(-(({4;3;2;1}&{3,2,1,0})&IF(D12:G15＜"",D12:G15,"")),ROW(1:4))))
と長いまま

[投稿情報: min]

今回の問題は

自分としては前回の「牽牛と織姫」の続きの問題です。
前回の「通常コース」は想定外のところがありましたが、
それを解決するための方法として
既にこの問題の解法のヒントを提示していました。

前回の通常コースで
■□□
□■□
□■□
□■□
□■□
□□■
のような場合に真ん中に■が連続すると通過可能となってしまう式を書き、

これを解決するために
□・・
・■・
・・□
のように
左が□（０）
その下の真ん中が■（１）
その下の右が□（０）
の場合はＩＦの条件設定で「“”」になるようにし、
NPVで「“”」を無視して結合するという方法を用いました。
NPVは文字列を無視するということを利用したのです。

今回の問題はそれを単純にしただけの問題でした。

[投稿情報: min]

で、
これなら、練習問題は新関数には１文字負けるけど、
問題の方は新関数に勝てると思い、くまぷーさんには申し訳ないですが、
くまぷーさんへの挑戦状とさせていただきました。

すみません。

[投稿情報: min]

で、
関数の中には文字列を無視する関数がいくつかあります。
例えばＳＵＭ、ＰＲＯＤＵＣＴなど

で、ＳＵＭＰＲＯＤＵＣＴを使った数式を批判する場合もあると思いますが、
以前から時々ここで言っているようにこの関数は使い道があると思っています。
なんてったって、文字列が配列に入っていても無視するから。
これは｛＝ＳＵＭ（○：▲＊◎：▽）｝では実現できず、
配列の中に文字列があるとエラーになってしまいます。

ということで、今回の問題はＮＰＶを使わないでやると
80=--SUBSTITUTE(SUMPRODUCT(D12:G15,INT(10^(19-COLUMN(A:D)-ROW(1:4)*4)))&N(G15),0,)
配列を整理して
75=--SUBSTITUTE(SUMPRODUCT(D12:G15,INT(10^({9;5;1;-3}+{5,4,3,2})))&N(G15),0,)
という感じでした。

INTを入れないといけないのがちょっと困りましたが。
演算誤差？？

[投稿情報: min]

sakudaさん今日は午前中から活動されていますね。

ここでMMULTを使ってきたか・・
う～ん。。

[投稿情報: くまぷー]

＞問題の方は新関数に勝てると思い

CONCAT関数を使っても「問題」は・・・

３０=CONCAT(IFERROR(--D12:G15,""))
３２=--CONCAT(IFERROR(D12:G15+0,""))

[投稿情報: くまぷー]

あ、上の式は文字列になっちゃうからプラス２か。

[投稿情報: min]

くまぷーさん

それにしてもCONCATは便利ですね。
CONCATENATEでは全く太刀打ちできませんね。

[投稿情報: くまぷー]

CONCATENATE関数は役立たずですね。
演算子＆の方がずっと使い勝手が良い。

[投稿情報: y sakuda]

有効数字の桁超えるから、最後を・・・っての考えましたが、４×4で右下だけカットする手段思いつかなかった。
＞INTを入れないといけないのがちょっと困りましたが。
多分、10^-1、つまり0.1が出てくるからでは？　0.1は二進数では無限小数です。
＞CONCATENATE関数は役立たずですね。
www
古いバージョン知らないんですが、昔のバージョンか他の計算ソフトとの互換性のために残ってるだけでは？

＞sakudaさん今日は午前中から活動されていますね。
たまたま目が覚めた時間が9時頃だったのでそのまま。寝なおすと何時になるかわからないからｗｗｗ

[投稿情報: min]

＞0.1は二進数では無限小数です。
なるほど。。
よくわかりませんが？

[投稿情報: y sakuda]

2進数では、小数点以下の数字は、0.5、0.25、0.125・・・つまり２^-n　の和で表現されます。
それで、0.1ってのは、ジャストの組み合わせはできないので、打ち切り誤差がでます。
Excelでは計算結果に補正（正体が良くわからない）がかかり、0.1を10回足せばちゃんと１にしてくれたりしますが当然100%は無理なので、思わぬところで変な事がおこります。
この関係の質問は、Q/A掲示板で定期的にでてきてます。

[投稿情報: min]

＞思わぬところで変な事がおこります。
へ～～なんとなくわかったような。。
ありがとうございました。

[投稿情報: からくち]

あれ～、、、まだか、、、
17＋19、、、
誰か分かる人教えて～

[投稿情報: min]

niさんが出してくれると思いますが、
NPVは一緒
後ろが違うだけ

[投稿情報: からくち]

＞後ろが違うだけ
ありがとうございます
わたしの式は、、、
*10^4、、、5文字使っています
4文字でどう書くのかな、、、
短縮はやっぱ苦手です

[投稿情報: からくち]

分かりました
ありがとうございました

[投稿情報: min]

からくちさん

＞この問題PART2が頭に浮かんでしまった

どんなのか？

[投稿情報: ni]

出先なので
*10^4=/1%%

[投稿情報: くまぷー]

＞＞この問題PART2が頭に浮かんでしまった
＞どんなのか？

そろそろ在庫が・・・

[投稿情報: からくち]

では、、、ここで良かったら
続けて書きますが、、、
表も全く同じなので、、、

【問題】

2つある表のうち下の表を使ってください
文字と■のある方です

（問１）
4つの数値を上から
ただし同じ行に2個以上あった場合は＋してください
例１：
２
６２
空白
５
答え：２８０５
２０００＋
　８００＋
　　　０＋
　　　５

例２：
７
８９
６
空白
答え：８７６０
７０００＋
１７００＋
　　６０＋
　　　０
（問２）
問１は上からですが問２は左からで後は同じです
例１：
７６　９
　５
答え：８１０９
７０００＋
１１００＋
　　　０＋
　　　９

例２：
　３　８
　９　７　
答え：１２１５
　　　０＋
１２００＋
　　　０＋
　　１５　

以上です

分かり難かったら聞いてください
若しくは無視してください

[投稿情報: からくち]

4 く ま 6
ぷ ー さ ん
へ の 挑 9
戦 状 4 ！

問１：10094

問２：4055

く ま 7 ぷ
ー さ 2 2
ん へ の 挑
戦 5 状 ！

問１：7405

問２：592

5 く ま ぷ
8 ー さ ん
7 へ の 挑
戦 5 状 ！

問１：5875

問２：20500

[投稿情報: からくち]

因みに、、、今の処は
問１＋問２＝86
です

[投稿情報: くまぷー]

問１だけで８６かな・・・
間違っていなければ・・・

[投稿情報: からくち]

＞問１だけで８６かな・・・
いえ、、、１＋２で86です

[投稿情報: くまぷー]

＞＞問１だけで８６かな・・・
＞いえ、、、１＋２で86です

問１だけで８６は私の話です。(^_^;)

[投稿情報: からくち]

ごめんなさい、、、
偶々同数だったのですね

[投稿情報: からくち]

今日は藤井聡太君の誕生日であり
わたしの誕生日でもあります
いじん仲間だったのですね
でも、、、
偉人と異人の違いが、、、

[投稿情報: min]

86位かなあ？
実際やってませんが。

[投稿情報: からくち]

＞86位かなあ
問１＋問２で76
短縮が得意な方達ならもっと短いでしょう、、、

[投稿情報: からくち]

余りに簡単過ぎと思ったら
例えばIFERROR禁止とかどうでしょう
それでも１＋２＝86で済んでしまうけど

[投稿情報: ｎｉ]

問1+問２で76
IFERROR使ってます

[投稿情報: からくち]

＞IFERROR使ってます
多分、、、
＞問１＋問２で76
同じと思います
IFERRORを禁止すると10文字増えてしまう

[投稿情報: min]

帰って来ました。
やってみました。
IFERROR使うと38*2=76

使わないと39*2=78

でした。

で、どのタイミングで答発表なのでしょう。

[投稿情報: くまぷー]

＞で、どのタイミングで答発表なのでしょう。

明日の昼１２時で如何ですか？

[投稿情報: min]

明日の昼12時、私は結構です。

で、IFERRORを使わない86というのもわかります。

[投稿情報: min]

それにしても今回の問題

何かの意図を感じるような・・・

[投稿情報: くまぷー]

=SUBTOTAL(9, D12:G12)&SUBTOTAL(9, D13:G13)&SUBTOTAL(9, D14:G14)&SUBTOTAL(9, D15:G15)

問２もこれと同じ発想です(^_^;)

[投稿情報: からくち]

行列の違いは「；」と「、」の違いです

IFERRORを使った式
38：{=SUM(IFERROR(D12:G15*10^{3;2;1;0},))}

使わない式
43：{=SUM(IF(D12:G15<10,D12:G15)*10^{3;2;1;0})}
つまらないので、、、
43：{=SUM(TEXT(D12:G15,"0;;;!0")*10^{3,2,1,0})}
結局同じ文字数とは、、、
関数式の面白さ不思議さ

[投稿情報: min]

SUMPRODUCTで39
LEFTBで39
でした。

[投稿情報: min]

いずれも単純です。

[投稿情報: min]

textは全く同じ。

SUMPRODUCTは２番煎じですが、
39=SUMPRODUCT(D12:G15,10^{3;2;1;0}+A1:D1)

[投稿情報: min]

LEFTBは配列数式で
39{=SUM((0&LEFTB(D12:G15))*10^{3,2,1,0})}
でした。

[投稿情報: min]

苦手なTEXTで
48{=SUM(--(TEXT(D12:G15,"0!e;;;!0!e")&{3;2;1;0}))}
こんなのもありかも。