Excel Maniacs

リストから重複した項目を削除する【問題】

【問題】

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にそれぞれ・・・

こども, もなか, かいじゅう ,うし ,しまうま ,まきがい, いぬ

・・・と尻取りになるように表示させたい。

どのようにすればよいか？

三四郎・・・文字列の種類を数える【解答】

問題はこちらを参照。

【解答】

１．B1に　=1/CountExact(A1,A$1:A$10)

２．上の式をB10までコピペ。

３．C1に　=Sum(B1:B10)

問題はこちらを参照。

【解答】

１．B1に　=1/CountExact(A1,A$1:A$10)

２．上の式をB10までコピペ。

３．C1に　=Sum(B1:B10)

三四郎・・・文字列の種類を数える【問題】

【問題】

A1からA10のセルに千葉，東京，千葉，千葉，東京，埼玉，神奈川，神奈川，

千葉，神奈川の１０個の文字列が入力されています。

何種類の文字列が入力されているか求るにはどのような関数を使えばよいでしょう？

(●`ε´●)：この場合は、神奈川，千葉，埼玉，東京の４種類だな・・・・。

【問題】

A1からA10のセルに千葉，東京，千葉，千葉，東京，埼玉，神奈川，神奈川，

千葉，神奈川の１０個の文字列が入力されています。

何種類の文字列が入力されているか求るにはどのような関数を使えばよいでしょう？

(●`ε´●)：この場合は、神奈川，千葉，埼玉，東京の４種類だな・・・・。