お題は「スロットマシーン」です。
F９キー（三四郎はShiftを押しながらF2）を押すと下図のような３種のマークが切り替わります。
当たりならばセルJ3に「TRUE」と、ハズレならば「FALSE」と
表示させて下さい。
*
*
*
*
*
※解答用参考ブック
Slot_Q5.xlsをダウンロード （エクセル版）
Slot_Q5.jsdをダウンロード （三四郎版）
※今回のルール（と言いましょうか・・・）
この問題はエクセルコンテストで言えば「腕自慢」ではなくて
「腕試し」というべきものです。今まで何らかの形でこのブログに参加して
頂いた方、特に常連さんはその辺りをご配慮下さい。m(__)m
尚、答え合わせは2/18（水）の7:00ということで・・・
前回の「10枚の領収書」は如何だったでしょうか？
今回のお題は「ランダム得点入力」です。(^O^)
このブログは「教員のためのPC活用術」というタイトルなのですが
この頃はぜんぜん教員を意識した記事がありません。たまには・・・
ということで上記のお題にしてみました。
さて、業界人（教員）以外の方にお題の背景を説明しますと・・・
この業界では中間試験・期末試験からちょっとした小テストまで
年がら年中試験をやっています。中間試験・期末試験等の定期試験では
生徒が出席番号順に着席しています。ですから答案回収時に答案が出席番号順に
なるので、採点結果を名簿に記入（入力）する時に不便はないのですが
定期試験以外の小テスト等になると、生徒が出席番号順に着席していないので
回収した答案は当然、バラバラです。
このような答案の採点結果を記入（入力）する時は視線を動かし
入力するセルを探し、そのセルにカーソルを移動し入力しなければ
ならないので、たいへん面倒です。(T.T)
★名簿は下のようになっています。
*
*
*
*
*
*
*
*
*
そこでなるべくラクができるように入力欄に出席番号と得点を入力すれば
得点欄に得点が表示されるようなシートを作って欲しいのです。(^O^)
★シートの例
*
*
*
*
*
*
*
*
★仕様
１．生徒数は４５名まで。得点は０～１００までの整数。
２．A列２行目からが入力欄。B列はどう使おうと自由。C列は得点表示欄。
ただし、2行目が出席番号１の者の得点、3行目が出席番号２の者の得点、・・・・・・となる。
すみません、２の縛りは無しにします。本家のエクセルコンテスト同様
解答用サンプルシートのレイアウトはあくまでも参考ということで・・・
途中で仕様を変えてスミマセンm(__)m
★解答用サンプルシート
★くまぷーの解答公開2/11午後8時（答え合わせ）
直接、解答に関わるような書き込みは2/11午後8時までご遠慮下さい。
参加して頂ける方は、メールにファイルを添付して送って下さい。m(__)m
当初は総当たりの解答しか、用意していなかったんですが
ジョー３さんから、芸がないとのお叱りを受け急遽、別解を
用意しました。三四郎版は当初予定していたものと同じです。
エクセル版は循環参照を利用していますので
他のファイルを開かないで下さい。
三四郎版は圧縮しないと７７４KBですが、エクセル版は１６KBです。
Q4_kumapooh_Kaitei.lzhをダウンロード （Excel２００７ 改訂版）
Q4_kumapooh_Kaitei_xls.lzhをダウンロード （Excel２０００－２００３改訂版）
※エクセル版はDec2Bin関数を使っています。Excel２００７ならば
そのまま動きますがExcel2000等ではアドイン「分析ツール」を
組み込む必要があります。
「Excel２０００－２００３」用はエクセル２００７で保存し直しただけで
動作の検証はしていません(^^ゞ
******************************************************
エクセルの改訂版をアップしました。（2009/02/02）
あるSNSで出された問題のパクリなんですが・・・
【問題】
ある電器屋では7777円以上のレシートを持って行くと
景品として、幻のVB for MS-DOSのFDをくれる。
次の10枚のレシートから選んで7777円 に近くなるような
組合せを１つ見つけて欲しい。
1922 1766 1598 1246 1153 1000 961 869 593 267
ちなみに下のぷーさんが持っているのが
幻のVB for MS-DOSのFDだ！！
＊
＊
*
*
*
*
*
*
*
*
★解答用のエクセル・三四郎のファイルを用意しました。
ダウンロードしてお使い下さい。
この問題はエクセルに対しては三四郎絶対に有利です。
なぜならば三四郎では下図のように・・・
＊
＊
＊
＊
Ａ列に価格、Ｂ列に個数、Ｃ列に価格としてＡ列×Ｂ列と
するとエラーにならずに計算できちゃうんです (゜ロ)ギョェ
つまり、「数字（半角・全角問わず） ＆ 文字」に四則演算を
すると数字の部分だけを取り出して計算するんです。
ですからＡ１に2x-3=5としてB１に＝A1*1とやるとxの係数2が
求められちゃうんです (゜ロ)ギョェ
何という荒技でしょう？！
でも、こういうことができると唯一の解説本である「三四郎９のすべて」には
書いていないんですよね。いや～、もったいない ( ´－｀)
解答例 （スミマセン、下はメチャクチャ解答でした。常連さんの
解答を参考にして下さい。くまぷー12/16追記）
Excel２００７ １６２文字
=(RIGHT(A2,LEN(A2)-FIND("=",A2))-COUNT(MID(A2,FIND("x",A2)+1,FIND("=",A2)-FIND("x",A2)-1)))/IFERROR(FIND("-x",A2)*-1,IF(FIND("x",A2)=1,1,LEFT(A2,FIND("x",A2)-1)))
三四郎 １２２文字
=(RIGHT(A2,LEN(A2)-FIND("=",A2))-COUNT(MID(A2,FIND("x",A2)+1,FIND("=",A2)-FIND("x",A2)-1)))/IFERROR(FIND("-x",A2)*-1,IF(FIND("x",A2)=1,1,LEFT(A2,FIND("x",A2)-1)))
【問題】
下図のようにA2にｘの1次方程式が入力されている。
この式の解をF２に表示したい。どのような式を
入力したらよいか？
＊
＊
＊
＊
＊
★補足
１．文字は全て半角で入力されている。
２．右辺は定数のみ（変数ｘがあるのは左辺だけ）
３．左辺・右辺ともにきちんと整理されている。
つまり、3x-2-3=5+1ならば3x-5=6 に整理済みとする。
問題はこちら
解答例：
約分処理をどうやるかがポイントになります。
約分するには分子・分母をこの二つの数の最大公約数で
わればよいので・・・・
E1:答えの分子：=(A1*C2-C1*A2)/GCD(A1*C2-C1*A2,A2*C2)
E1:答えの分母：=A2*C2/GCD(A1*C2-C1*A2,A2*C2)
ちなみにGCDが最大公約数を求める関数です。
ジョー３さんが某所で仰っていたことを頂きました。
【問題】
*
*
*
*
上図のように・・・
A1：引かれる数の分子
A2：引かれる数の分母
C1：引く数の分子
C2：引く数の分母・・・とすると
E1、E2にはそれぞれどんな数式を入れればよいか？
但し、答えが０になったり、負になる場合は
考えなくて良いものとする。
★解答例はこちら
問題はこちらを参照。
【´･ω･`】:まっとうな解答ではとうとう４０文字をきることができなかった。
＊
＋
【解答・三四郎】
A1に・・・
=If(Abs(Row(A1)-6)+Abs(Col(A1)-6)<6,"*")
【´･ω･`】:これで４０文字
＊
＋
【解答手順・EXCEL・・・色物編３５文字】
１． =(ABS(COLUMN()-6)+ABS(ROW()-6)<6)*1
２． フォントをMarlettまたはWebdingsにする。
３． ０値を表示しない設定にする。
・・・すると下のようになる。
*
*
*
*
*
*
*
【´･ω･`】:このやり方はもちろん、三四郎でも有効だ。
さてジョー３さん、同じでしたか？
【´･ω･`】:表計算一筆書きシリーズも「ピラミッド」「グルグル渦巻き」「ストライク」
「手裏剣」に続きいよいよ第5弾だ！！
(●`ε´●):表計算一筆書き？
【´･ω･`】:あ、言い忘れていた。ここでやっているように一つのセルに入力した
数式を他のセルにコピペして図を描く遊びを「表計算一筆書き」と
呼ぶことにしたい。よろぴく。
＊
＊
【問題】セルを利用して下のような図を描きたい。
一つのセルに入力した数式を他のセルにコピペすることで
図を描くにはどのセルにどのような数式を入力すればよいか？
＊
＊
＊
＊
＊
＊
＊
＊
※コメント即OK。セル、フォントの色は関係なし。
今回はアスタリスク「*」の代わりに「■」を使っています。
ただの気まぐれです。
※数式は10/6（土）の午後８時までご遠慮を。
＊
＊
＊
＊
＊
追記（10/4 午後１０：３０）
下はExcelです。式の文字数３５です。
もちろん、書式、名前定義は使っていません。（￣＾￣）
問題はこちらを参照。
(●`ε´●):今回は何がなんだかわからなかったぞ！！
【´･ω･`】:問題が簡単すぎるって言うから・・・。
(●`ε´●):きちんとした法則があるんだろうな？！
【´･ω･`】:うん、今回のキーワードは調和平均だよ・・・。
(●`ε´●):調和平均？！
【´･ω･`】:「行きは時速６Kｍ、帰りは時速４Kmで走りました。
さて、平均時速は何Kmでしょう？」ってやつだよ。
(●`ε´●):時速５Kｍに決まってるだろ！！
【´･ω･`】:････････。
＊
＊
＊
＊
＊
【解答・三四郎】
A1に・・・
=If(IsOdd(Int(HarMean(Row(A1),12-Row(A1),12-Col(A1),Col(A1)))),"★")
【解答・Excel】
=LEFT("★",MOD(INT(HARMEAN(ROW(),12-ROW(),12-COLUMN(),COLUMN())),2))
【´･ω･`】:お絵かきシリーズも「ピラミッド」「グルグル渦巻き」「ストライク」に
続きいよいよ第4弾だ！！今回のは「１分でできた」なんて言われることは
ないだろう。
(●`ε´●):こんなのできるのか？
【´･ω･`】:できない問題はださないから・・・。
【問題】セルを利用して下のような図を描きたい。
一つのセルに入力した数式を他のセルにコピペすることで
図を描くにはどのセルにどのような数式を入力すればよいか？
＊
＊
＊
＊
＊
＊
＊
＊
※コメント即OK。セル、フォントの色は関係なし。
今回はアスタリスク「*」の代わりに「★」を使っています。
ただの気まぐれです。
※数式は9/29（土）の午後８時までご遠慮を。
問題はこちらを参照
【解答・三四郎】
A1に・・・
=If((Row(A1)-6)^2<=(Col(A1)-6)^2,"*")
３７文字です。
【解答・Excel】
A1からK１１のうちの任意のセルに・・・
=LEFT("*",(ROW()-6)^2<=(COLUMN()-6)^2)
【´･ω･`】:この前の「グルグル渦巻き」の後では屁みたいな
問題で気が引けるけど････････。
【問題】セルを利用して下のような図を描きたい。
一つのセルに入力した数式を他のセルにコピペすることで
図を描くにはどのセルにどのような数式を入力すればよいか？
*
*
*
*
*
*
*
*
(●`ε´●):過去ログがかなりヒントになるな！！
※コメント即OK.
※数式の書き込みは９／２３(日） 午後８時までご遠慮を。
問題はこちらを参照。
【´･ω･`】:今回の問題・・・、気がついてみれば何でもないんだけど
はじめは法則に気がつかずに苦労してしまったよ。
(●`ε´●):で、どんな法則があるんだ？
【´･ω･`】:いつもの解答はただ数式を示すのみだったのだが
今回はROMしてくれている初心者さんたちのために
ちょっと、解説を・・・。
「グルグル渦巻き」はアスタリスクのあるセルと無いセルの２種類で
描かれている。だから、もしも下図のようにアスタリスクのあるセルが１ならば
*
*
*
*
*
*
1のところをアスタリスクに置き換えればよい。
(●`ε´●):でも問題はこんなに単純じゃないぜ。
【´･ω･`】:うん、でもよく見てみると・・・
*
*
*
*
*
*
*
*
(●`ε´●):な、なるほど！F6を中心に見るとアスタリスクを表示させるべきセルは
F6の１つ隣り、３つ隣り、５つ隣り・・・奇数分だけ隣のセルか！！
【´･ω･`】:うん、これに気づけば・・・。
【解答・三四郎】
F6に・・・
=If(Max(Abs(Row(F6)-6),Abs(Col(F6)-6)) MOD 2,"*")
・・・49字
【解答・Excel】
F６に・・・
=LEFT("*",ISODD(MAX(ABS(ROW()-6),ABS(COLUMN()-6))))
・・・５１字
【´･ω･`】:前回の「三四郎でピラミッド」ではたくさんの人が
参加してくれてうれしかったので調子に乗ってもう一題・・・。
(●`ε´●):もうやめておいた方がいいんじゃないか？
【´･ω･`】:まあ、そういうなよ。一生懸命考えたんだから・・・。
でも正直なところ、この前もどこかで言ったが問題を
考えるところで力を使い果たしてしまったので
かっこいい回答は常連のみなさんにおまかせね！！
【問題】
セルを利用して下図のような図形を描きたい。
一つのセルに入力した数式を他のセルにコピペして描くには
どのセルにどのような数式を入力したらよいか？
*
*
*
*
*
*
*
※セルの色は関係ありません。
※コメント即OK
※数式は9/15 午後8時までご遠慮を。
問題はこちらを参照。
みなさん、たくさんのコメント有り難うございました。m(__)m
【回答】
=If(Abs(6-Col(A1))<Row(A1),"*","")
これで３４文字です。
【番外・エクセル編】
考え方は三四郎版と全く同じで３３文字です。
=IF(ABS(6-COLUMN())<ROW(),"*","")
それではみなさん、数式の披露をお願いします。
ワクワク(((o(^。^")o)))(((o("^ 。^)o)))ワクワク
※
不等号などいくつか半角文字をうけつけないようです。
申し訳ありませんが、そう言う場合は
全角にてお願いします。m(__)m
お手数をお掛けします。m(__)m
【問題】
セルを利用して下図のようなピラミッドを描きたい。
**
*.
*
*
*
*A1に入力した数式をK６までコピーして、上図を描くには
A1にどのような数式を入力したらよいか？
※コメント即OK。
※回答の数式は9/10 午後6時までご遠慮を。
問題はこちらを参照。
【解答】
=Choose(11-Col(A1)," ","k","s","t","n","h","m","y","r","w") & Choose(Row(A1),"a","i","u","e","o")
【´･ω･`】:Mid関数を使った方が式が短くなるけど、まあ、いいか。
(●`ε´●)：おいら、カナ入力しかできなんだけど、こんど
ローマ字入力を覚えたいんだ。で、下のような
表を作って欲しいんだけど・・・・。
*
*
*
*
*
(●`ε´●):A1に入力した数式をJ5までコピペして表を作るには
A1にどのような数式を入力すればいいかな？
【´･ω･`】:こんどは第1弾よりもいろいろな回答が
作れるかな？
※コメント即OK。
※回答の数式は９／３ 午後6時までご遠慮を・・・。
【問題】
セルK1に「あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやいゆえよらりるれろわゐうゑを」と入力されている。
K１の文字列を利用して下図のような５０音表を作りたい。
*
*
*
*
*
A1に入力した数式をJ5までコピペして表を作るには
A1にどのような数式を入力すればよいか？
(●`ε´●):ちょっと簡単すぎないか？
【´･ω･`】:そう言うなよ。問題を考えるのは大変なんだから・・・。
※コメント、解答 即OK.
問題はこちらを参照。
【´･ω･`】: 悪児さんが・・・
＞三四郎では、文字列でも、
＞先頭が小文字の数字だと、
＞その部分だけを数値として取りだして
＞計算してくれます。
・・・と言っていたけど、小文字（半角？）じゃなくて全角でもいいようだ。
つまり、A2の「１００００円札」が・・・・
＝A2*1 とか ＝＋A2とか、計算に使ってやるだけで「10000」になる・・・
この辺りは、ものすごく便利なところだ。
(●`ε´●):エクセルで「10000」を取り出すとすると？
【´･ω･`】: =VALUE(LEFT(A2,FIND("円",A2)-1)) ・・・とか
=(LEFT(A2,FIND("円",A2)-1))*1・・・てなかんじかな。
えくせるバカさんも基本的にはこの手を使っているようだ。
さて、みなさん、どんな手を使ってくるだろうか？
【解答】
１．B2に =B1-A2*C2 と入力。B10までコピペ。
２．C2に =Int(B1/A2) と入力。C10までコピペ。
【問題】
B1に金額を入力したとき、C2からC10に数量を表示させたい。
表のレイアウトは下の通りである。
**
*
*
*
*
*
*
(●`ε´●):おい、A列に全角で「１００００円札」なんて入力してあるのか？！
【´･ω･`】:うん。他のセルに10000とか5000とか、金額そのものを入力しないでやってね。
(●`ε´●):A列の金額を抜き出して利用しろというわけか？
【´･ω･`】:うん。
(●`ε´●)：エクセルユーザにケンカを売っているような表だな！！
【´･ω･`】:そういうわけではないんだけど・・・。
三四郎を使えば楽勝だな・・・。
※三四郎、Excelの解答作成用ファイルを用意しました。必要ならば
Mihon003.Lzhをダウンロード してください。
※解答、コメントは即OKです。
問題はこちらを参照。
【´･ω･`】:まずは三四郎版から・・・・
【解答】
D4に・・・
=DCount($P$1:$Q$43,1,{{"５教科","=<" & Left(D$3,Find("-",D$3)-1)},{"５教科",">=" & Mid(D$3,Find("-",D$3)+1,3)},{"英語","=<" & Left($C4,Find("-",$C4)-1)},{"英語",">=" & Mid($C4,Find("-",$C4)+1,2)}})
・・・と入力。M１３までコピペ。
(●`ε´●):なんだ、第2弾と変わらないじゃないか？！
【´･ω･`】:うん、同じやり方。基本に忠実・・・。次はエクセル版・・・。
【解答・番外Excel版】
１．適当な場所に２つの区間の変換テーブルをつくる。
この場合は５教科がC18:D27,英語がF18:G２７。
| 5教科 | 英語 | |||
| 得点 | 列番号 | 得点 | 行番号 | |
| 0 | 13 | 0 | 13 | |
| 50 | 12 | 10 | 12 | |
| 100 | 11 | 20 | 11 | |
| 150 | 10 | 30 | 10 | |
| 200 | 9 | 40 | 9 | |
| 250 | 8 | 50 | 8 | |
| 300 | 7 | 60 | 7 | |
| 350 | 6 | 70 | 6 | |
| 400 | 5 | 80 | 5 | |
| 450 | 4 | 90 | 4 |
２．R2に =VLOOKUP(P2,$C$18:$D$27,2) と入力。R43までコピペ。
３．S2に =VLOOKUP(Q2,$F$18:$G$27,2) と入力。S43までコピペ。
4．T2に =R2*100+S2 と入力。T43までコピペ。
5．D4に =COUNTIF($T$2:$T$43,COLUMN(D4)*100+ROW(C4)) と入力。M13までコピペ。
【´･ω･`】:エクセル版の解答ということでこれを紹介したが、エクセル特有の機能を
使っているわけではないので、このやり方は表計算ソフト一般で通用するはずだ。
(●`ε´●)：第１弾が区間が同じ、かつ区間に幅無し。
第２弾が区間が同じ、かつ区間に幅あり。
で、第３弾は？
【´･ω･`】:第３弾は区間が異なり、区間に幅あり・・・。
【問題】
英語の得点と５教科の得点の相関関係分布表を作ります。
英語の得点は0から100の整数値です。５教科の得点は０から５００の
整数値です。相関分布表のレイアウトは下を参考にしてください。
*
*
*
*
なおデータは4２人分で2行目からP列に５教科のと句点、Q列に英語の得点が
入力されています。
| ５教科 | 英語 |
| 415 | 81 |
| 261 | 67 |
| 199 | 39 |
| 422 | 94 |
| 284 | 76 |
| 377 | 81 |
| 489 | 99 |
| 345 | 75 |
| 124 | 22 |
| 322 | 80 |
| 74 | 16 |
| 114 | 14 |
| 36 | 0 |
| 446 | 96 |
| 500 | 100 |
| 353 | 73 |
| 456 | 88 |
| 156 | 30 |
| 263 | 57 |
| 276 | 58 |
| 255 | 59 |
| 431 | 89 |
| 263 | 49 |
| 440 | 92 |
| 329 | 69 |
| 265 | 57 |
| 286 | 62 |
| 373 | 67 |
| 475 | 97 |
| 321 | 75 |
| 215 | 31 |
| 347 | 81 |
| 348 | 74 |
| 402 | 82 |
| 339 | 79 |
| 348 | 86 |
| 348 | 84 |
| 246 | 54 |
| 412 | 92 |
| 197 | 45 |
| 372 | 86 |
| 124 | 26 |
※三四郎、Excelの解答作成用ファイルを用意しました。必要ならば
Mihon002.lzhをダウンロード してください。
※縦横の計には予め、SUM関数が入っているので、その部分は
考慮する必要はない。D4からM13に入れる式を答えればよい。
【´･ω･`】：・・・なんて問題をだそうと思っていたんだけど・・・
○○さんの第２弾の解答そのままでなんとかなっちゃうんだよね。
でも、せっかく第３弾まで問題を考えていたから、出すね。
※コメント、解答の書き込みは即OKです。
問題はこちらを参照。
【解答】
D4に ・・・
=DCount($P$1:$Q$43,1,{{"数学","=<" & Left(D$3,Find("-",D$3)-1)},{"数学",">=" & Mid(D$3,Find("-",D$3)+1,2)},{"英語","=<" & Left($C4,Find("-",$C4)-1)},{"英語",">=" & Mid($C4,Find("-",$C4)+1,2)}})
・・・と入力。そしてM13までコピペ。(悪児さんの指摘により、一部修正）
【´･ω･`】:このように複数条件による集計が標準関数で簡単にできてしまうところが
三四郎の素晴らしいところだな。おそらく1999年発売の「三四郎９」から
（あるいはもっと前から）可能だったんだと思う。
(●`ε´●):Excelだとこの手のことをやろうとすると配列数式に頼ったりして、ちょっと
初心者には厳しいところがあるが、三四郎だと条件式を単純に並べるだけで
出来てしまう。おいらはもともとはEXCEL派なんだけどこの辺りの所は
三四郎の良さを素直に認めざるを得ない。
【´･ω･`】:ExcelではCountIf関数やSumIf関数は複数条件には対応していなかった。
複数条件に対応したCountIfs関数やSumIfs関数が登場したのは
EXCEL２００７になってからだからね。
JustSystemさん、もっと宣伝上手になろうよ。
さてＥｘｃｅｌでの解答だけど作業列無しの一発解答はエクセルバカさんが
凄いのを出してきそうなので敢えて作業列ありのやつを提示しよう。
【Ｅｘｃｅｌ番外編】
１．適当な場所に下のような変換テーブルを作る。この場合はＤ１７：Ｅ１７。
| 得点 | 行・列番号 |
| 0 | 13 |
| 10 | 12 |
| 20 | 11 |
| 30 | 10 |
| 40 | 9 |
| 50 | 8 |
| 60 | 7 |
| 70 | 6 |
| 80 | 5 |
| 90 | 4 |
２．セルＲ２に =VLOOKUP(P2,$D$18:$E$27,2) と入力。Ｓ４３までコピペ。
３．セルＴ２に =R2*100+S2 と入力。Ｔ４３までコピペ。
４．Ｄ４に =COUNTIF($T$2:$T$43,COLUMN(D4)*100+ROW(D4)) と入力。
M１３までコピペ。
【´･ω･`】:この辺りの問題になってくるとかなり、ハイレベルでExcelでやるにしても
けっこう、歯ごたえがあるとおもう。
(●`ε´●)：ま、普通は作業列を使った処理を考えるはな・・・。
【´･ω･`】:世の中には普通じゃない人もいるから・・・。
【問題】
英語と数学の２教科の得点の相関関係分布表を作ります。
得点は0から100の整数値です。相関分布表のレイアウトは下を参考にしてください。
*
*
*
*
*
なおデータは4２人分でP列に数学、Q列に2行目から英語の得点が
入力されています。
| 数学 | 英語 |
| 86 | 81 |
| 30 | 67 |
| 41 | 39 |
| 70 | 94 |
| 28 | 76 |
| 67 | 81 |
| 96 | 99 |
| 60 | 75 |
| 29 | 22 |
| 41 | 80 |
| 13 | 16 |
| 36 | 14 |
| 18 | 0 |
| 79 | 96 |
| 100 | 100 |
| 67 | 73 |
| 96 | 88 |
| 33 | 30 |
| 46 | 57 |
| 51 | 58 |
| 39 | 59 |
| 82 | 89 |
| 58 | 49 |
| 82 | 92 |
| 61 | 69 |
| 47 | 57 |
| 50 | 62 |
| 86 | 67 |
| 92 | 97 |
| 48 | 75 |
| 61 | 31 |
| 52 | 81 |
| 63 | 74 |
| 78 | 82 |
| 51 | 79 |
| 45 | 86 |
| 48 | 84 |
| 42 | 54 |
| 68 | 92 |
| 31 | 45 |
| 57 | 86 |
| 23 | 26 |
尚、正解のイメージは下図である。
| 英語 | 数学 | |||||||||||
| 評定 | 100-90 | 89-80 | 79-70 | 69-60 | 59-50 | 49-40 | 39-30 | 29-20 | 19-10 | 9-0 | 計 | |
| 100-90 | 3 | 1 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 7 | |
| 89-80 | 1 | 2 | 1 | 1 | 2 | 3 | 0 | 0 | 0 | 0 | 10 | |
| 79-70 | 0 | 0 | 0 | 3 | 1 | 1 | 0 | 1 | 0 | 0 | 6 | |
| 69-60 | 0 | 1 | 0 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 4 | |
| 59-50 | 0 | 0 | 0 | 0 | 1 | 3 | 1 | 0 | 0 | 0 | 5 | |
| 49-40 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 2 | |
| 39-30 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 3 | |
| 29-20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 2 | |
| 19-10 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 2 | |
| 9-0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | |
| 計 | 4 | 4 | 3 | 7 | 6 | 8 | 5 | 3 | 2 | 0 | 42 | |
※三四郎、Excelの解答作成用ファイルを用意しました。必要ならば
MIhon001.lzhをダウンロード してください。
※縦横の計には予め、SUM関数が入っているので、その部分は
考慮する必要はない。D4からM13に入れる式を答えればよい。
※解答は8/18（土）発表予定。
問題はこちらを参照。
【解答手順】
１． M2に =K2 & L2 と入力。M４１までコピペ。
２． D4に =CountExact(D$3 & $C4,$M$2:$M$41) と入力。
H８までコピペ。
【問題】
英語と数学の２教科の評定値の相関関係分布表を作ります。
評定値は1から５の整数値です。相関分布表のレイアウトは
下を参考にしてください。
*
*
*
*
*
なおデータは40人分でＫ列に数学、Ｌ列に2行目から英語の評定値が
４０人分入力されています。
| 数学 | 英語 |
| 5 | 3 |
| 5 | 5 |
| 3 | 3 |
| 2 | 3 |
| 3 | 4 |
| 2 | 2 |
| 4 | 3 |
| 4 | 4 |
| 3 | 4 |
| 4 | 2 |
| 4 | 3 |
| 5 | 4 |
| 2 | 2 |
| 3 | 4 |
| 2 | 3 |
| 4 | 4 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 4 | 3 |
| 3 | 3 |
| 4 | 4 |
| 4 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 3 |
| 5 | 3 |
| 4 | 4 |
| 3 | 3 |
| 3 | 3 |
| 5 | 4 |
| 5 | 3 |
| 4 | 3 |
| 5 | 4 |
| 4 | 4 |
| 4 | 3 |
| 4 | 3 |
| 4 | 4 |
| 3 | 3 |
| 4 | 4 |
尚、正解のイメージは下図である。
| 英語・数学評定値相関分布表 | |||||||
| 英語 | 数学 | ||||||
| 評定 | 5 | 4 | 3 | 2 | 1 | 計 | |
| 5 | 1 | 0 | 0 | 0 | 0 | 1 | |
| 4 | 3 | 9 | 4 | 0 | 0 | 16 | |
| 3 | 4 | 7 | 6 | 2 | 0 | 19 | |
| 2 | 0 | 1 | 0 | 3 | 0 | 4 | |
| 1 | 0 | 0 | 0 | 0 | 0 | 0 | |
| 計 | 8 | 17 | 10 | 5 | 0 | 40 | |
※縦横の計には予め、SUM関数が入っているので、その部分は
考慮する必要はない。D4からH8に入れる式を答えればよい。
※解答は8/11（土）発表予定。
問題はこちらを参照。
【解答】
１．A1に =String(Count(Search("<='" & B1 & "'",B$1:B$10))-Count(Search("='" & B1 & "'",B$1:B$10)))
これをA10までコピペ。
２．C1に =String(Count(Search(">='" & D1 & "'",D$1:D$10))-Count(Search("='" & D1 & "'",D$1:D$10)))
これをC10までコピペ。
３．D1に =If(IsError(VLookup(String(Row(A1)-1),A$1:B$10,2)),"",VLookup(String(Row(A1)-1),A$1:B$10,2))
これをD10までコピペ。
４．E1に =If(IsError(VLookup(String(Row(A1)-1),C$1:D$10,2)),"",VLookup(String(Row(A1)-1),C$1:D$10,2))
これをE10までコピペ。
前回のお題のソートを応用しています。(^。^)
【問題】
B1からB10に下のように入力されている。
ごじら
らどん
ごじら
ごじら
もすら
もすら
らどん
きんぐぎどら
もすら
らどん
このリストから重複した項目を除いてE1から上詰めで
下のように表示させたい。
らどん
もすら
ごじら
きんぐぎどら
どうしたらよいか。但し、並び方の順は問わない。
【解答手順】
ソートキーが２つあるので正直に２回ソートしてるだけです。
１．A2に =Code(G2)+Row(A2)*0.001 と入力。A10までコピペ。
２．B2に =Rank(E2,E$2:E$10)+Row(B2)*0.001 と入力。B10までコピペ。
３．F2に =Indirect(Char(65+Col(B2)) & Fp(Small($B$2:$B$10,Row(B1)))*Power(10,3)) と入力。H10までコピペ。
４．I2に =Indirect(Char(68+Col(B2)) & Fp(Small($A$2:$A$10,Row(B1)))*Power(10,3)) と入力。K10までコピペ。
【解答手順・番外エクセル編】
２つのソートキーを一つにまとめることにより、2回ソートするところを1回で済ませています。
１．H1に =CHAR(155-CODE(D2)) & TEXT(E2,"000") &ROW() と入力。H10までコピペ。
２．I2に =INDEX(C$2:C$10,MATCH(ROW()-1,COUNTIF($H$2:$H$10,">" & $H$2:$H$10)+1,0)) と入力。
Ctrl+Shift+Enterで確定。I3からK10までコピペ。
【問題】
数のようにC列、D列、E列の2行目から、
それぞれ氏名、組、得点が入力されている。
これを、組、得点をキーにして並び替え、
氏名、組、得点をそれぞれ図のように
I,J,K列に表示させたい。どうしたらよいか。
*
*
*
*
*
※標準機能のソート、マクロの使用は禁止です。(^^ゞ
※多くの方に参加していただきたいので、７／２９（日）午後6時まで
コメント欄に直接、数式を書き込むのはご遠慮ください。m(__)m
問題はこちらを参照。
【解答】
悪児さんが「順位を求める」で提示された69文字の式を
参考にしています。
１．A1に =String(Count(Search("<='" & B1 & "'",B$1:B$10))-Count(Search("='" & B1 & "'",B$1:B$10))) と入力。A10までコピペ。
２．C1に =VLookup(String(Row(B1)-1),A$1:B$10,2) と入力。C10までコピペ。
【解答 番外エクセル編】
えくせるバカさんの式と似ていますが、操作が違います。
１．C1に =INDEX($B$1:$B$10,MATCH(ROW()-1,COUNTIF($B$1:$B$10, "<" & $B$1:$B$10),0)) と入力。Shift + Ctrl + Enter で確定。 C１０までコピペ。
【解答 番外エクセル編（その２）】
１． A1に =COUNTIF($B$1:$B$10, "<" & B1)+1 と入力。A10までコピペ。
２． C1に =INDEX($B$1:$B$10,MATCH(ROW(),A$1:A$10,0)) と入力。C10までコピ
ペ。
このA1の式とC１の式を一つにまとめると・・・
=INDEX($B$1:$B$10,MATCH(ROW()-1,COUNTIF($B$1:$B$10, "<" & $B$1:$B$10),0))
・・・になります。
【問題】
B列１行目から、１０個の駅名が下のように入力されている。
つだぬま
みもみ
かつただい
ふなばし
さくら
なりた
にしふなばし
やちよだい
おおくぼ
うすい
これをC列１行目から、昇順に並べ替えて・・・
うすい
おおくぼ
かつただい
さくら
つだぬま
なりた
にしふなばし
ふなばし
みもみ
やちよだい
・・・としたい。数式を使って解決するにはどうすればよいか？
尚、標準機能のソート、マクロを使うのは禁止とする。
問題はこちらを参照。
今回は悪児さんの出題でした。
くまぷーも悪児さんの回答を楽しみにしています。
【解答手順】
１．A1に =String(Rank(D1,D$1:D$11)) と入力。
A11までコピペ。
２．D1に =C1+0.0001*Row(A1) と入力。
D11までコピペ。
３．E1に =VLookup(String(Row(C1)),$A$1:$C$12,Col(B1)) と入力。
F11までコピペ。
【解答手順・番外エクセル編】
１．D1に =C1+ROW()*0.0001 と入力。D11までコピペ。
２．E1に =INDEX(B$1:B$11,MATCH(LARGE($D$1:$D$11,ROW()),$D$1:$D$11,0))
F11までコピペ。
【問題】
B列（氏名）、C列（得点）の1行目から下のようにデータが入力されている。
一太郎 56
二太郎 76
三太郎 87
四太郎 83
五太郎 92
六太郎 100
七太郎 83
三四郎 67
八太郎 78
九太郎 98
十太郎 56
この時、E１に何か数式を入力しそれをF11までコピーすることにより
氏名、得点を得点を基にして降順に並べ替えたい。E1にどのような式を入れればよいか？
*
*
*
(悪児さん出題）
(●`ε´●)：悪児さんのオリジナルとはちょっと違うようだが？
【´･ω･`】：くまぷーの解きやすいように列位置を弄ってある。
ま、問題の本質に関わらないと思うのでこれでやってd(^-^)ネ!
尚、一般機能のソートを使ったり、マクロを使うのは禁止だよん。
問題はこちらを参照。
【解答】
=If(Sum(B2:B11),If(IsBlank(B2),"欠",If(IsError(Search(">" & Num(B2),B$2:B$11)),1,Count(Search(">" & Num(B2),B$2:B$11))+1)),"")
【´･ω･`】：表が未使用の時、順位欄は全て空欄。未受験（空欄）のときには
その者の得点欄に「欠」と表示される。
(●`ε´●)：表が未使用の時とは、どう判断しているんだ？
【´･ω･`】：得点欄の合計が零の時だ。まさか、受験者全員が零点ということはあるまい。
【´･ω･`】：展開を完全に読まれてしまっているので第3弾はお蔵入りに
しようと思ったけどやることにしたよ。
【問題】
下のようにA列に氏名、B列に得点が入力されている。
C２に何か数式を入力して、それをC１１までコピペすることにより
１０人分の順位を求めたい。ただし、未受験の者と0点の者の順位が
区別されるようにしたい。ぐたいてきには、得点欄が空欄ならば
順位欄も空欄（欠などの表示をするのも可）にしたい。
C2にどのような数式を入力したらよいか？
A B C
1 氏名 得点 順位
2 一太郎 65
3 二太郎 78
4 三太郎 59
5 四太郎 84
6 五太郎 78
7 六太郎 56
8 七太郎 0
9 八太郎 66
10 Ｑ太郎
11 十太郎 69
(●`ε´●)：なんだ、前回と同じじゃないか？！
【´･ω･`】：よく見てくれ。七太郎はできが悪く0点をとってしまった。ところが前回の
式では未受験のQ太郎と0点の七太郎が同順位になってしまう。
これはうまくない。
（※うまくない理由はこちらを参照）
(●`ε´●)：で、キーボードはまだ、壊れたままなのか？
【´･ω･`】：うん。従って「k」の文字は入力できない。
あ、もう第4弾はないから・・・。
問題はこちらを参照。
第一弾では未受験者がいませんでした。しかし、第二弾ではＱ太郎君が
未受験なので、空欄に対応した数式にしなければなりません。
(●`ε´●)：おまえの数式（三四郎版下記）では未受験者が1位になって
しまうな。
=If(IsError(Count(Search(">" & B2,B$2:B$11))),1,Count(Search(">" & B2,B$2:B$11))+1)
【´･ω･`】：うん。B2の者が最高得点だった場合はSearch関数はそれを超える得点は
見つけられないのでエラーになる。
(●`ε´●)：最高得点を超える得点があるはずないからな。
【´･ω･`】：ということでエラーが起きたとき、その者を1位とするというのが上式の
意味なんだけど、式中のIsError関数は空欄と数値を比較したときにも
真になるので１になってしまう。
(●`ε´●)：それは問題だな。
【´･ω･`】：で、下が未受験ありに対応させた数式だ。未受験（空欄）は最下位になる。
【解答】
=If(IsError(Count(Search(">" & Num(B2),B$2:B$11))),1,Count(Search(">" & Num(B2),B$2:B$11))+1)
(●`ε´●)：第三弾がありそうだな。
【´･ω･`】：わかる？
【問題】
下のようにA列に氏名、B列に得点が入力されている。
C２に何か数式を入力して、それをC１１までコピペすることにより
１０人分の順位を求めたい。C2にどのような数式を入力したら
よいか？
A B C
1 氏名 得点 順位
2 一太郎 65
3 二太郎 78
4 三太郎 59
5 四太郎 84
6 五太郎 78
7 六太郎 56
8 七太郎 85
9 八太郎 66
10 Ｑ太郎
11 十太郎 69
(●`ε´●)：なんだ、前回と同じじゃないか？！
【´･ω･`】：よく見てくれ。Q太郎は犬に追いかけられて試験を受けることが
できなかったんだ。従って彼の得点蘭は未入力だ。
(●`ε´●)：キーボードはまだ、壊れたままなのか？
【´･ω･`】：うん。従って「k」の文字は入力できない。
(●`ε´●)：前回の式じゃダメなのか？
【´･ω･`】：うん、残念ながら・・・。前回の式は全員の得点が入力されていれば
大丈夫だけど、未受験があるときちんと動作するのはくまぷー（エクセル版）の
配列数式だけなんだ・・・。
(●`ε´●)：おまえの三四郎版は？
【´･ω･`】：未受験ありには対応できていない・・・。
問題はこちらを参照。
【解答】
C2に次の式を入力してC11までコピペ。
=If(IsError(Count(Search(">" & B2,B$2:B$11))),1,Count(Search(">" & B2,B$2:B$11))+1)
【解答２】
１．B列の適当なセルに十分に大きい値を入力する。
例えばB14に１０００。
２．C2に =Count(Search(">" & B2,B$2:B$14)) と入力し
C11までコピペ。
【解答番外（エクセル）編】
１．ジョー３さんから寄せられた解答。
C2に次の式を入力してC11までコピペ。
=COUNTIF($B$2:$B$11,">"&B1)+1
２．くまぷーのあまり、美しくない解答。
C２に =SUM((B$2:B$11>B2)*1)+1 とにゅうりょくして
Ctrl、Shift、Enterを同時に押して確定。
=SUM((B$2:B$11>B2)*1)+1 が ｛=SUM((B$2:B$11>B2)*1)+1｝ に
なるはずです。
C2式をC3からC11までコピペ。
(●`ε´●)：ジョー３さんの式の方が解りやすくていいな。
【´･ω･`】：ひとに教えるならジョー３さん式だね。
(●`ε´●)：ところで、くまぷー。三四郎にはエクセルのCountIf関数に相当する関数は
ないのか？
【´･ω･`】：信じがたい話だが無いんだ。だから、CountIf関数（やSumIf関数）を含んだ
エクセルファイルを三四郎ではきちんと読み込めないんだ。
(●`ε´●)：おまえがあそこで言っていた件だな。
【´･ω･`】：うん。
【問題】
下のようにA列に氏名、B列に得点が入力されている。
C２に何か数式を入力して、それをC１１までコピペすることにより
１０人分の順位を求めたい。C2にどのような数式を入力したら
よいか？
A B C
1 氏名 得点 順位
2 一太郎 65
3 二太郎 78
4 三太郎 59
5 四太郎 84
6 五太郎 78
7 六太郎 56
8 七太郎 85
9 八太郎 66
10 Ｑ太郎 78
11 十太郎 69
(●`ε´●)：順位を求める？そんなのRank関数で一発じゃん。
【´･ω･`】：うん、でもキーボードが壊れちゃってて「k」の文字が
入力できないんだ。
問題はこちらを参照
【解答手順（エラー対策済）】
１．メニューを「ツール」「オプション」と辿り、計算タブで「反復回数」を８以上にする。
２．セルD1に =If(D1="",C1,D1 & If(IsError(Right(Search("like'^" & Right(D1,1) & "'",$B$1:$B$8),Len(Search("like'^" & Right(D1,1) & "'",$B$1:$B$8))-1)),"",Right(Search("like'^" & Right(D1,1) & "'",$B$1:$B$8),Len(Search("like'^" & Right(D1,1) & "'",$B$1:$B$8))-1)))
と入力する。
【´･ω･`】：尻取りに飽きてきたかもしれないけど・・・。
【問題】
B1からB８にそれぞれ・・・
うし
かいじゅう
こども
しまうま
もなか
まきがい
いぬ
たらこ
・・・と入力されている。
今、C1に「たらこ」と入力したとき
D1に何か数式を入力し・・・
たらこどもなかいじゅうしまうまきがいぬ
・・・と尻取りになるように表示させたい。
どのようにすればよいか？
問題はこちらを参照。
循環参照を使います。
【解答の手順（エラー対策無し）】
１．メニューを「ツール」「オプション」と辿り、計算タブで「反復回数」を８にする。
２．セルD1に =If(D1="",C1,D1 & Search("like'^" & Right(D1,1) & "'",B1:B8))
と入力する。
【解答の手順（エラー対策あり）】
１．メニューを「ツール」「オプション」と辿り、計算タブで「反復回数」を８以上にする。
２．セルD1に =If(D1="",C1,D1 & If(IsError(Search("like'^" & Right(D1,1) & "'",B1:B8)),"",Search("like'^" & Right(D1,1) & "'",B1:B8)))
と入力する。
【問題】
B1からB８にそれぞれ・・・
うし
かいじゅう
こども
しまうま
もなか
まきがい
いぬ
たらこ
・・・と入力されている。
今、C1に「たらこ」と入力したとき
D1に何か数式を入力し・・・
たらここどももなかかいじゅううししまうままきがいいぬ
・・・と尻取りになるように表示させたい。
どのようにすればよいか？
問題はこちらを参照。
【解答手順】
１．A1に =Left(B1,1) と入力する。
２．上式をA8までコピペ。
３．D1に =VLookup(Right(C1,1),$A$1:$B$8,2) と入力する。
４．上式をJ1までコピペ。
【問題】
B1からB８にそれぞれ・・・
うし
かいじゅう
こども
しまうま
もなか
まきがい
いぬ
たらこ
・・・と入力されている。
今、C1に「たらこ」と入力したとき
D1に何か数式を入力し、それをJ1までコピペすることにより
D1からJ1にそれぞれ・・・
こども, もなか, かいじゅう ,うし ,しまうま ,まきがい, いぬ
・・・と尻取りになるように表示させたい。
どのようにすればよいか？
【問題】
A1からA10のセルに千葉，東京，千葉，千葉，東京，埼玉，神奈川，神奈川，
千葉，神奈川の１０個の文字列が入力されています。
何種類の文字列が入力されているか求るにはどのような関数を使えばよいでしょう？
(●`ε´●)：この場合は、神奈川，千葉，埼玉，東京の４種類だな・・・・。
【問題】
A1からA10のセルに５，７，５，５，７，６，４，４，５，４の１０個の
数値が入力されています。何種類の数値が入力されているか
求るにはどのような関数を使えばよいでしょう？
(●`ε´●)：この場合は、４，５，６，７の４種類だな・・・・。
最近のコメント