関数で出来ますか～？ 第340回 日本史の年表

 

　コロナのために休校措置になった。休校期間の課題として

クラスの生徒全員で少しずつ分担して歴史年表を作ることになった。

しかし、連絡は担任が生徒一人一人に電話ですることになったので

うまく伝わらず、あるものは「年号・事件」の順にデータを入力し、またあるものは

「事件・年号」の順にしてしまった。（T.T)

 

【問】

D2に年号を入力したら、それに合致する事件をE2に表示させてください。

ただし、D2,A1:B10しか参照してはいけません。

 

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

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

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

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

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

 

Comments

[投稿情報: くまぷー]

解答入りの「解答用参考ファイル」をアップしてしまった・・・
差し替えたけど・・・

もしもダウンロードしてしまった方は見なかったことにしてください　(^0^;)

[投稿情報: y sakuda]

不思議な問題。作戦どうするかな？

[投稿情報: y sakuda]

面白くもない５８
なんか普通じゃない手段あるのかな？

[投稿情報: y sakuda]

ちょっと方向変えたけど54

[投稿情報: y sakuda]

しかし、何だったか知らないのがあるな・・・・
三世一身法、乙巳の変・・・・
忘れてるのかな？

[投稿情報: maron]

＞ただし、A1:B10しか参照してはいけません。
◆ならば、列参照やD2も参照してはいけないのでしょうか？

[投稿情報: くまぷー]

＞ちょっと方向変えたけど54

まだ、問題を作っただけでちゃんと取り組んではいませんが
５０台はすでに私より短い！！

＞三世一身法、乙巳の変・・・・

三世一身法は私が小学生の時に普通に出てきました。土地に関する法律です。
乙巳の変・・・昔は「大化の改新」でしたかね。正確に言えば「大化の改新」の始まりの蘇我入鹿暗殺事件です。

[投稿情報: くまぷー]

＞＞ただし、A1:B10しか参照してはいけません。
＞◆ならば、列参照やD2も参照してはいけないのでしょうか？

失礼しました。D2は当然、参照ありです。

＞ただし、A1:B10しか参照してはいけません

これはそのままでお願いします。列参照はなしです。

[投稿情報: くまぷー]

列参照ありならば４６だけど、やたらに重い・・・

[投稿情報: y sakuda]

54がもう一種、2016の新関数使った51。

[投稿情報: y sakuda]

私は最初から列参照でやたら重いです。しかし、最初の58は列参照でもなぜかかなり軽い。

[投稿情報: くまぷー]

＞2016の新関数使った51。

私は列参照なしで５２が最短です。
列参照にすれば４０です。

[投稿情報: min]

やってみました。
列参照禁止で
65でした。

[投稿情報: min]

ちなみに、D2の式は=INDEX(L9:L18,RAND()*10+1)
でやってみています。

[投稿情報: maron]

◆59です

[投稿情報: くまぷー]

＞ちなみに、D2の式は=INDEX(L9:L18,RAND()*10+1)

なるほど。
解答用参考ファイルを、そういう風にしておいた方が良かったですね。

[投稿情報: maron]

◆56になりました

[投稿情報: くまぷー]

maronさんが走ってきた・・・

[投稿情報: ｎｉ]

70 1行ずつ調べてたのではダメなのかな

[投稿情報: min]

53にはなりました。

それにしても、この問題・・・
年号欄に事件名を入れても
事件欄に年号が表示される。。
面白い問題ですね。
あ、皆さんの式は違うかもですが。。

ちなみにd2に=INDEX(L9:M18,RAND()*10+1,RAND()*2+1)
と入れてみた。。

[投稿情報: min]

ん、53は列指定と判定されるのかも。。

であれば、2文字増えて55でした。

[投稿情報: min]

出かけますので、
一応終了ということでお願いします。

[投稿情報: y sakuda]

列参照なしだとどうしても60台だな・・・・

[投稿情報: くまぷー]

＞1行ずつ調べてたのではダメなのかな

やってみました。　１８９

[投稿情報: くまぷー]

★終了宣言
1.　minさん
2.　
3.　
4.　

[投稿情報: y sakuda]

今日になってからなにもアイデアが出てこない・・・・

[投稿情報: y sakuda]

列参照なしだと、新関数使っても58
なんとか、新関数なしで50台に入りたい。

[投稿情報: ｎｉ]

なんとか63
全然届きませんね

[投稿情報: y sakuda]

＞なんとか63
私は列参照なしの旧関数の範囲では64どまりです。

[投稿情報: maron]

◆58で終了

[投稿情報: くまぷー]

＞なんとか、新関数なしで50台に入りたい。

私の場合は新関数とかは気にしましません。
どこからが「新関数」かわからないし・・・

むしろ、使えるのに使わないというのは・・・

普通の「良い子」に「そんなことしなくても、もっとスッキリとできますよ」と言われるのは癪だと思う・・・

ココの副題の「良い子はマネをしてはいけません ( >_<) 」というのは
マネしたくてもできないということであって
簡単に出来ることをわざわざ面倒くさくして「マネする気にもならない」となってしまうと・・・

[投稿情報: くまぷー]

★終了宣言

1.　minさん
2.　maronさん
3.　
4.　

[投稿情報: min]

新関数、私は使えないけど、使ったものは興味深いのでたくさん見たいです。

[投稿情報: ｎｉ]

58できたぁ
終了です

[投稿情報: からくち]

＞それに合致する事件をE2に表示
これでは解釈出来ていないのかな、、、47終了
完璧なら、、、63
何故か分からないけど
＞A1:B10しか参照してはいけません
これをA:A等にすると、、、35かな

[投稿情報: くまぷー]

＞これでは解釈出来ていないのかな、、、47終了
＞これをA:A等にすると、、、35かな

えっ？！
メチャクチャ短い！！

＞完璧なら、、、63

ということは47は完ぺきではなく問題ありという事ですか？

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

[投稿情報: maron]

◆それでは、早速
58{=OFFSET(A1,SUM((A1:B10=D2)*ROW(A1:A10))-1,OR(A1:A10=D2))}

[投稿情報: y sakuda]

今回は固まっちゃったｗ
Concat　使った58
=CONCAT(INDEX(TEXT(A1:B10,),MAX((D2=A1:B10)*ROW(A1:A10))))
新関数使わないと、64どまり
=INDEX(TEXT(A1:A10,)&TEXT(B1:B10,),MAX((D2=A1:B10)*ROW(A1:A10)))
=INDEX(IF(A1:A10>"",A1:A10,B1:B10),MAX((A1:B10=D2)*ROW(A1:A10)))

maronさんはOffsetか、一瞬なにやってるのか分からなかった（orのところ）

[投稿情報: からくち]

47=LOOKUP(9,FIND(D2,A1:A10&B1:B10),A1:A10&B1:B10)
年号と事件の双方が表示なので
年号を消せば良いのかな、、、
63=SUBSTITUTE(LOOKUP(9,FIND(D2,A1:A10&B1:B10),A1:A10&B1:B10),D2,)
ただし16文字も増えますが、、、

[投稿情報: min]

maronさんの58はやはり
niさんも同じかな
私の55はそれとほぼ一緒

式の投稿は家に帰ってからになります。

[投稿情報: からくち]

{=SUBSTITUTE(INDEX(A1:A10&B1:B10,SUM((D2=A1:B10)*ROW(1:10))),D2,)}
{=INDEX(A1:B10,SUM((D2=A1:B10)*ROW(1:10)),SUM((A1:B10=D2)*{2,1}))}
=IFERROR(LOOKUP(,0/(D2=A1:A10),B1:B10),LOOKUP(,0/(D2=B1:B10),A1:A10))

[投稿情報: ｎｉ]

58{=OFFSET(A1,MAX((A1:B10=D2)*ROW(A1:A10)-1),OR(A1:A10=D2))}
maronさんと同じですね。
MAXとSUM、-1するとこだけ違うけど、実質同じ

[投稿情報: くまぷー]

53=CONCAT(IF(A1:A10=D2,B1:B10,IF(B1:B10=D2,A1:A10,"")))

CONCAT関数は便利ですね。

189 =IFS(A1=D2,B1,A2=D2,B2,A3=D2,B3,A4=D2,B4,A5=D2,B5,A6=D2,B6,A7=D2,B7,A8=D2,B8,A9=D2,B9,A10=D2,B10,B1=D2,A1,B2=D2,A2,B3=D2,A3,B4=D2,A4,B5=D2,A5,B6=D2,A6,B7=D2,A7,B8=D2,A8,B9=D2,A9,B10=D2,A10)

niさんが言っていた一つずつ調べるやつです　(^^ゞ

[投稿情報: くまぷー]

あれ？
189のやつ、消えている。
ま、どうでもいいか。

[投稿情報: y sakuda]

うーーん、Concatの使い方が分かってないことが分かったw

[投稿情報: ｎｉ]

1つずつじゃなくて、1列ずつです＾＾

=IFNA(OFFSET(B1,MATCH(D2,A1:A10,)-1,),OFFSET(A1,MATCH(D2,B1:B10,)-1,))

[投稿情報: min]

maronさん、niさん
offsetで-1はいらないんです。

[投稿情報: ｎｉ]

＞offsetで-1はいらないんです。

？　offset(A1,1,) は　A2参照しちゃうでしょ？

[投稿情報: min]

え、A2参照しちゃダメなの？
A1:B10の範囲内なら良いのかと思ってました。

[投稿情報: min]

一応出しますね。
これはずるかな。
53{=OFFSET(A1,SUM((A2:B10=D2)*ROW(1:9)),OR(A1:A10=D2))}
で、
55{=OFFSET(A1,SUM((A2:B10=D2)*ROW(A1:A9)),OR(A1:A10=D2))}
だったのですが

[投稿情報: min]

A2:B10
バッチリa2を使ってます。

[投稿情報: min]

a9も使っているなあ。

[投稿情報: min]

良く考えたらniさんの言っている意味は違うのかも

[投稿情報: min]

55は最新エクセルだと53かな？
なら2007の式と引き分け？

[投稿情報: ｎｉ]

＞53{=OFFSET(A1,SUM((A2:B10=D2)*ROW(1:9)),OR(A1:A10=D2))}
なるほど、そういうことか。
2行目から10行目になかったら、必ず1行目にあるはずということですね。

[投稿情報: y sakuda]

>55は最新エクセルだと53かな？
ですね。　｛｝は要りません。

[投稿情報: min]

＞2行目から10行目になかったら、必ず1行目にあるはずということですね
ガッテンしていただけたでしょうか？
(NHK風)

[投稿情報: ｎｉ]

＞ガッテンしていただけたでしょうか？
そううところになかなか気が回らないんですよね。
ガッテン、ガッテン、ガッテン

[投稿情報: y sakuda]

＞2行目から10行目になかったら、必ず1行目にあるはずということですね
考えもしなかったｗ

[投稿情報: くまぷー]

＞＞2行目から10行目になかったら、必ず1行目にあるはずということですね
＞考えもしなかったｗ　×2

[投稿情報: maron]

＞＞＞2行目から10行目になかったら、必ず1行目にあるはずということですね
＞＞考えもしなかったｗ　×3

[投稿情報: min]

それにしてもジョー3さん元気かなあ？

[投稿情報: くまぷー]

＞それにしてもジョー3さん元気かなあ？

脳梗塞悪化？