Excel Maniacs

戯れてExcel・・・漢字-かな-ローマ字変換マクロ2

【´･ω･`】：「さらに戯れてExcel・・・漢字-ローマ字変換マクロ」に下のようなコメントがあった。

　　　　　別に難しいことではないので、すぐにコメント欄にやり方を答えてやったのだが・・・　

 

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

くまぷー先生　調子に乗って、リクエスト・・・

だめならスルーしてください。

名字を大文字表記にするマクロを・・・お戯れに・・・作っていただくことはできませんか。
。
今日、スピコンでした。出場者一覧がそのような表記になっていて、とても見やすかったのです。

　　HABU　Yoshiharu

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

 

（●｀ε´●）：「マクロを・・・」って言ってるぞ！！お前の回答は関数だろ？！

　　　　　　本当に注意力がないな！！

 

【´･ω･`】：じゃ、マクロでも全然難しくないから、下にマクロ版を晒しておくよ。

 

 

Kanatoroman_ver4.xlsをダウンロード

使用法はこちらを参照

 

かな-ローマ字変換マクロ改訂版

　以前、SQSさんが作成した「かな-ローマ字変換マクロ」をこちらのサイトで紹介した。

結構、多くの人が利用しているようだ。

 

 

（●｀ε´●）：くまぷー、ちょっと改良して欲しいところがあるんだが・・・

【´･ω･`】：どんなとこ？

（●｀ε´●）：変換したローマ字が全部小文字になっちゃうんだよね。

 

 

（●｀ε´●）：これを手作業で直して下のようにするのは面倒くさすぎる・・・

 

 

（●｀ε´●）：氏名をローマ字に変換するんだから、頭の部分は大文字にしたい・・・

【´･ω･`】：わかった。じゃあ、改訂版（KanaToRoman.xls）をアップしておくよ。

 

ダウンロード： KanaToRoman.xlsをダウンロード

★使用法

1．氏名（全角ひらがな・全角カタカナ）を選択する。

2．マクロ「かなローマ字変換」を実行する。

3．氏名（全角ひらがな・全角カタカナ）の右側にローマ字氏名が表示される。

（注）　氏名（全角ひらがな・全角カタカナ）の右側はローマ字氏名で上書きされる。

 

SQSさん版マクロの記事：Excel Maniacs: かな-ローマ字変換マクロ (typepad.jp)

三四郎で尻取り・・・第３弾【解答】

問題はこちらを参照

【解答手順（エラー対策済）】

１．メニューを「ツール」「オプション」と辿り、計算タブで「反復回数」を８以上にする。

２．セル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にそれぞれ・・・

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

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

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

文字列操作・・・単語数を数える(2)

【´･ω･`】：A1に「Nothing is as important as health.」と入力されているとして・・・

　　　　　単語数を数えるやり方を考えてみよう。前回、Count系の関数では

　　　　　うまくないことは述べた。

(●`ε´●)：Count系の関数はセルの個数を数えるのに使うんだったな。

【´･ω･`】：うん、そういうこと。では復習は終わり。それでは「単語数を数える(2)」を

　　　　　　始めようか。

　　　　　「Nothing is as important as health.」の文字数はいくつだ？

(●`ε´●)：え～と。ピリオドまで入れて29文字だ。

【´･ω･`】：本当か？

(●`ε´●)：ああ、よ～く数えたんだから間違いない！

【´･ω･`】：じゃあ、文字数を数えるLEN関数で確かめてみてくれ。

(●`ε´●)………あれ、？？？

【´･ω･`】：どうしたんだ？

(●`ε´●)：29にならない。３４になってしまう。どうしてだ？！ 【´･ω･`】：べべちゃんが文字数を数えたときは無意識のうちにスペースを                除いて数えていた。だから文字数が２９になった。しかし…。 (●`ε´●)：しかし、LEN関数はスペースも文字として扱い、それを                 勘定に入れて数えていたから３4になった。 【´･ω･`】：その通り！だから３４から２９を引いた５がスペースの数だ。 (●`ε´●):：そしてその５に１を足した６が単語の数だ！！ 【´･ω･`】：ビンゴ！！それでは以下にスマートなやりかたの手順を                 しめそう。A1に元になる英文が入力されているとして… １．B1に=LEN(A1) 　　元になる英文の文字数を求める。先の例では３４。 ２．C1に=SUBSTITUTE(A1, " ","") 　　半角スペースを消去。 　※SUBSTITUTE関数の書式は以下の通り。この関数には置換機能がある。

=SUBSTITUTE(A1, "検索する文字列 ","置換したい文字列")

３．D1に=LEN(C1) 　　スペース消去後の文字数を求める。先の例では２９。 ４．E1に＝B1-D1 　　元になる英文に含まれるスペースの数を求める。 　　「元になる英文の文字数」－「スペース消去後の文字数」先の例では５。 ５．F1にE1+1 　　「元になる英文に含まれるスペースの数」＋１。これが単語の数。先の例では６。 ★エラー対策 【´･ω･`】：さて、ここまでで基本的なことが終わった。 (●`ε´●)：これから何をやるんだ？ 【´･ω･`】：仕上げとしてエラー対策だ。 (●`ε´●)：エラー対策？ 【´･ω･`】：そう。例えば今作った式は 単語数=　「元になる英文に含まれるスペースの数」＋１ 　　　　　という考えに基づいている。これには大きな落とし穴があるんだ。 (●`ε´●)：落とし穴？ 【´･ω･`】：うん。確かに大概の場合はこの式が成立する。 　　　　　　しかし、英文の入力してある蘭を空欄にしてみて欲しい。 (●`ε´●)：あっ！単語の数は当然、０にならなければいけないのに、 　　　　　　１と表示されている！ 【´･ω･`】：そう。これは明らかにおかしい。空欄の時は別扱いに 　　　　　　しなければならない。 単語数=　IF（空欄, 0, 「元になる英文に含まれるスペースの数」＋１) 　　　　　とするか 単語数=　 「元になる英文に含まれるスペースの数」＋IF（空欄, 0, １) 　　　　とするのが基本かな。 snsSample012.lzhをダウンロード

文字列操作・・・単語数を数える(1)

問）下のようにセルに英文が入力されている。単語数を求めなさい。

*

*

*

(●`ε´●)：英単語はスペースで区切られているから、スペースの数を数えて

                 それに＋１してやれば英単語の数になる。簡単だな。

【´･ω･`】：その考えでいいと思うけど具体的にどうやってスペースの数を数えるんだ？

(●`ε´●)：決まっているだろ。COUNT系の関数を使うんだよ！

【´･ω･`】：甘いな！COUNT系の関数はセルの個数を数えるんだよ。

               セル内に入力されている特定の文字を数えるのには不向きだよ。

(●`ε´●)：簡単そうに見えて結構難しいな。

三四郎の関数で曖昧検索

以前、悪児さんの掲示板に「三四郎のCountExact関数について」という質問が

ありました（投稿番号2635）。具体的には「エクセルで、「山田」と入力されたセル数を

数える場合、1セル内に「山田、佐藤」とあっても、Countif関数で、

=Countif(A2:A10,"山田*")とすればカウント可能ですが、三四郎の場合はどうすれば

良いのでしょうか。」という質問です。CountExact関数ではワイルドカードが

使えないので作業列を使う方法やデータベース関数を使う方法などが

話題になりました。

データベース関数を使う方法を提示されてたのは

たしか、きっちーさんでしたよね？

・・・でこんなやりかたは如何でしょうか？

=Count(Search("like'山田'",A２:A10))

(^o^)/~~~

ごじらを逆さまに(14)

きっちーさん、こんばんは。コメント有り難うございます。

いや～、ぶっ飛びました(^｡^)

A1に反転させたい文字列。

B1に=If(Len(B1)>Len($A$1),"",Mid($A$1,Len(B1)+1,1) & B1)

これは100文字以上でも大丈夫ですね！三四郎の反復回数９９回の

制限を見事に克服しています！！

　反復回数を指定しての循環参照がForループに相当するとすれば

きっちーさんのはDo ループでしょうか。くまぷーは三四郎の循環参照は

エクセルに比べて柔軟性に欠けると思っていました。でも研究すれば

いろいろと楽しいことができそうです。

　ありがとうございました。(^o^)/~~~

特定の数値の個数を求めるには

以前、悪児さんの掲示板に「５や４の数を調べるには」という質問が

ありました（投稿番号２４８８）。三四郎のCountExact関数では

文字列の個数を求めることはできても数値の個数を求めることはできません。

例えばA1からA10に１，２，３，２，１，３，２，３，１、３とあって

=CountExact(3, A1:A10)としても答えは０になってしまいます。もちろん

=CountExact("3",A1:A10)としてもダメです。

そのときはデータを文字列化するとか、DCountを使うとかいう話になったのですが

=Count(Search("=3",A1:A10))

でいいんですね。

ごじらを逆さまに(13)

【´･ω･`】：酔いが覚めたら悪児さんの式の意味が分かったよ。

　　　　　　Shift＋F2なしで「らどぐんき」か・・・。ほんとうはくまぷーも

　　　　　　これがやりたかったんだけど、悪児さんがご自身のブログで

　　　　　　「反復計算では、１度、全部のセルを計算した後、また最初の

　　　　　　　セルから２度目の計算をする、というのではないらしい。

　　　　　　このケースでは、どうみても、A3を６回反復して計算し、

　　　　　その後B1の再計算にかかるらしい。」と仰っている。くまぷーも

　　　　　悪児さんと同じように考えた。

　　　　　　でも、これが三四郎の仕様であり、エクセルのような動きをさせるのは

　　　　　不可能だろうと思いこんで、Shift＋F2方式にしてしまった・・・。

　　　　　いや、本当に素晴らしい！！シャッポを脱ぎます！！

　　　　　　ところで不思議なことに悪児さんの式をB1にくまぷーの

　　　　　　式をB4に入力したとすると・・・・くまぷーの式も悪児さんのと

　　　　　　同じ動きをするな・・・そして更に解らないのはA3の式を

　　　　　　削除しても、その影響を受けずにちゃんと動作するところだ！

(●`ε´●)：おまえの式はA3を参照しているんだろ？

【´･ω･`】：うん、それが不思議なところだ。

(●`ε´●)：わからないふりをしているだけではないのか？

【´･ω･`】：いや、ほんとうにわからないんだ。いや、ほんと。

ごじらを逆さまに(12)

(●`ε´●)：おい、くまぷー、悪児さんからコメントを頂いているぞ！！

【´･ω･`】：何と仰っているんだ？

(●`ε´●)：下を見てくれ。

>くまぷーさんの解答ですが、B2セルの式を

>=If(A2=0,"",If(Len(B1)<Len(A1),B1 & Mid(A1,Len(A1)-Len(B1),1),B1))

>に替え、反復回数を10回以上に設定すると、どうでしょう・・・(^^)(^^)

【´･ω･`】：ごじらを逆さまに(8)の式についてだな。くまぷーの式が・・・

　　　　　　=If(A2=0,"",If(Len(B1)<Len(A1),B1 & Mid(A1,Len(A1)-A3+1,1),B1))

　　　　　　・・・だから、赤字の部分が違うという訳か。　

(●`ε´●)：二つの式は同じ効果があるようだな。

【´･ω･`】：うん、まあ、悪児さんのはくまぷーのとほぼ同じだから・・・。

　　　　　　反復回数をユーザに最大の９９回にしてもらうのも悪くはないな。

(●`ε´●)：で、おまえの式と悪児さんので結果に何か違いがあるのか？

【´･ω･`】：特に無いとおもうけど・・・。酔っぱらっているから違いが

　　　　　見えないのかもしれない・・・。

ごじらを逆さまに(11)

★ごじらを逆さまに(10)から続いています・・・。

(●`ε´●)：うーん、非表示になっている6行目を再表示させて・・・。B6を

　　　　　　見ると・・・。

=Fold(Repeat(" ",100) & $B5,1,Len(Repeat(" ",100) & $B5)-10*(Col(A1)-1)) & Fold(Repeat(" ",100) & $B5,1,Len(Repeat(" ",100) & $B5)-1-10*(Col(A1)-1)) & Fold(Repeat(" ",100) & $B5,1,Len(Repeat(" ",100) & $B5)-2-10*(Col(A1)-1)) & Fold(Repeat(" ",100) & $B5,1,Len(Repeat(" ",100) & $B5)-3-10*(Col(A1)-1)) & Fold(Repeat(" ",100) & $B5,1,Len(Repeat(" ",100) & $B5)-4-10*(Col(A1)-1)) & Fold(Repeat(" ",100) & $B5,1,Len(Repeat(" ",100) & $B5)-5-10*(Col(A1)-1)) & Fold(Repeat(" ",100) & $B5,1,Len(Repeat(" ",100) & $B5)-6-10*(Col(A1)-1)) & Fold(Repeat(" ",100) & $B5,1,Len(Repeat(" ",100) & $B5)-7-10*(Col(A1)-1)) & Fold(Repeat(" ",100) & $B5,1,Len(Repeat(" ",100) & $B5)-8-10*(Col(A1)-1)) & Fold(Repeat(" ",100) & $B5,1,Len(Repeat(" ",100) & $B5)-9-10*(Col(A1)-1)) & C6

　　　　　　・・・あ、最後にC6がくっついている・・・。

【´･ω･`】：うん、こうすることによって10個のFold()ブロックの10文字分に加えて

　　　　　C6の、つまりひとつ右隣のセルの文字を併せて表示するようにしているんだ。

(●`ε´●)：あ、なるほど。この式をコピペするとドミノ倒しのように右隣の文字が

　　　　　　付け加えられていくわけか！！

　　　　　　あ、でもくまぷー、C6には何も表示されていないぞ。1文字もないじゃ

　　　　　　ないか！！文字の色を白にしているようでもないし・・・。

【´･ω･`】：いや、C6をコピーして他のセルに「形式を選択して貼り付け」で

　　　　　「値」で複写してみるといい・・・・。

(●`ε´●)：あ、な～るほど。わかったぜい！！

悪児さん、どもでした。(^o^)/~~~

ごじらを逆さまに(10)

(●`ε´●)：悪児さんのでもうひとつわからないのは・・・

=Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-1) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-2) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-3) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-4) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-5) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-6) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-7) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-8) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-9)

　　　　　・・・の赤字の部分の働きだ。B1は「きんぐぎどら」だよな？！悪児さんは

　　　　　なんのために「きんぐぎどら」のまえにスペースを10個もくっつけたんだろうか？

【´･ω･`】：そういうときはRepeat(" ",10) &を消してしまえばいい。

　　　　　　そうすればRepeat(" ",10) &の役割が見えてくる。

(●`ε´●)：下のようにしてしまえというわけか・・・

　　　　　　=Fold(B1,1,Len(B1)) & Fold(B1,1,Len(B1)-1) & Fold(B1,1,Len(B1)-2) & Fold(B1,1,Len(B1)-3) & Fold(B1,1,Len(B1)-4) & Fold(B1,1,Len(B1)-5) & Fold(B1,1,Len(B1)-6) & Fold(B1,1,Len(B1)-7) & Fold(B1,1,Len(B1)-8) & Fold(B1,1,Len(B1)-9)

　　　　　　・・・あ、あれ？！「きんぐぎどら」が!引数不正!になっちゃったぞ？！

　　　　　　どうやらRepeat(" ",10) &はなんかのエラー対策のようだな。

【´･ω･`】：「きんぐぎどら」のかわりに「１２３４５６７８９０」といれてごらん。

(●`ε´●)：あれ？こんどはエラーにならずに「０９８７６５４３２１」と表示されたぞ？！

　　　　　　そうか、Repeat(" ",10) &は10文字未満の時にエラーにならないように

　　　　　　するためのものか！！

【´･ω･`】：うん。

(●`ε´●)：じゃあ、悪児さん方式でやると100文字を逆さまにするのにFold()のブロックが

　　　　　　100個いるというわけか？

【´･ω･`】：うん。基本的にはね。

(●`ε´●)：しかし、B5の「'くまぷー先生すてき♥ですよ。きゃー!

　　　　　　なんちゃって◇さらに続く」は31文字だからFold()ブロックが

　　　　　　３１個いるはずだが・・・？

【´･ω･`】：べべちゃん、よく見てごらん。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（続く）

ごじらを逆さまに(9)

(●`ε´●)：くまぷー、悪児さんのファイルを見たんだがちょっと

　　　　　　意味がわからないところがあるんだ。

【´･ω･`】：悪児さんは正攻法だと言っていたな。べべちゃん、で、

　　　　　どんな関数を使っていたんだ？

(●`ε´●)：こんなやつだ。「きんぐぎどら」がB1で・・・

　　　　　　B2にこんな式があったんだ・・・

　　　　　　=Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-1) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-2) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-3) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-4) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-5) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-6) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-7) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-8) & Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1)-9)

【´･ω･`】：で、なにがわからないんだい？

(●`ε´●)：まず、式が長いんで、どこから解読したらいいのかわからない・・・。

【´･ω･`】：よく見てみると・・・

　　　　　Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1))

　　　　　・・・をたくさん、くっつけたようだな。

(●`ε´●)：うん、数えてみると10個あるな。

【´･ω･`】：べべちゃん、そういうときは・・・

　　　　　ひとつを残して後はデリートしてしまえばいい。・・・例えば

　　　　　=Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1))

　　　　　・・・だけを残して後は消してしまう。そうすると式の動きが

　　　　　見えてくる。式が長くて理解しにくいなら

　　　　　長いままで扱うのは賢くない。短くしてしまえばいい。

(●`ε´●)：なるほど。・・・

　　　　　　=Fold(Repeat(" ",10) & B1,1,Len(Repeat(" ",10) & B1))

　　　　　　・・・はどうやら、「きんぐぎどら」の「ら」を表示している部分らしいな。

　　　　　こうやってみると、ひとつのFold()の部分がひらがな1文字にあたる

　　　　　ようだな。

【´･ω･`】：うん。

(●`ε´●)：わかってきたよ。くまぷー、でも、まだわからないところがあるんだ。

【´･ω･`】：どこだい。

(●`ε´●)：それは・・・。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（続く）

　　　　　

ごじらを逆さまに(8)

【´･ω･`】：問題はこうだった。

　　　A1に入力された「ごじら」をB1に「らじご」と表示させる。

　　　ただし、下のような決うちは不可。

　　　=Right(A1,1) & Mid(A1,2,1) & Left(A1,1)

　　　A1に入る文字数は不定。A1に「きんぐぎどら」とあれば

　　　B1に「らどぎぐんき」と表示させる。

(●`ε´●)：くまぷー、芸が無いと言われるようなことはしないと

　　　　　　「ごじらを逆さまに(7)」で大口をたたいていたな。

　　　　　　それでは、おまえの回答を聞かせてもらおうか？！

【´･ω･`】：それでは・・・。

　　　　　B1に・・・

　　　　　　　=If(A2=0,"",If(Len(B1)<Len(A1),B1 & Mid(A1,Len(A1)-A3+1,1),B1))

　　　　　A3に・・・=If(A2=0,0,A3+1)

　　　　　・・・と入力して欲しい。

(●`ε´●)：それで？

【´･ω･`】：A2に・・・１と入力して・・・

　　　　　Shiftを押しながら、F2を押すとそのたびに「ら」「らじ」「らじご」と

　　　　　1文字ずつ逆読みで表示されていく・・・。

(●`ε´●)：こ、これは？！

【´･ω･`】：A2に０を入力するとリセットされる。試して欲しい。

　　　　　　悪児さんはどんな手を使ったのかな？

snsSample007.lzhをダウンロード

　

　　　　　　　

ごじらを逆さまに(7)

悪児さんがごじらを逆さまに(6)で出だした問題に

早速取り組んでくれたようだ。

（こちらをどうぞ）

このノリの良さはほんとうにうれしい。

>これは、難問です。ただ、最大文字数が１０文字くらいなら、

>簡単にできる。上限は、２０文字くらいかな・・・(^^;)(^^;)

・・・と仰っておられる。

どもです。くまぷーは１００文字ぐらいまでは可です。

ただし、ワンセル一発ではありません。ワンセル一発では

ありませんが、悪児さんのノリの良さに報いるためにも

芸のないことはしないつもりです。☆☆(゜-゜)ウンッ☆☆

答え合わせは今度の土曜日くらいかな・・・。

こういう問題を一太郎WEBフォーラムで

出したら誰か相手にしてくれるかなあ？

悪児さんは実用的な知識だけでなく

遊び心があるから大好きです(^。^)

ごじらを逆さまに(6)

【´･ω･`】：これはハッキリ言って難問だと思う。

　　　　　覚悟して欲しい。

(●`ε´●)：おい、ずいぶん、気合いが入っているな。

【´･ω･`】：あっさりとやられたらシャッポを脱ぐよ。

問題：A1に入力された「ごじら」をB1に「らじご」と表示させる。

　　　ただし、下のような決うちは不可。

　　　=Right(A1,1) & Mid(A1,2,1) & Left(A1,1)

　　　A1に入る文字数は不定。A1に「きんぐぎどら」とあれば

　　　B1に「らどぎぐんき」と表示させる。

(●`ε´●)：くまぷー、おまえはできたのか？

【´･ω･`】：まあな。今までのごじらシリーズでは解答をすぐに

　　　　　発表していたが、今度は一週間ぐらい後にしようか。　

ごじらを逆さまに(5)

【´･ω･`】：ごじらを逆さまに(4)の問題はどうだった？

（●｀ε´●）：悪児さんは簡単すぎると言っているぞ。

【´･ω･`】：悪児さんには簡単すぎるかもしれないが

　　　　　　この辺りでもけっこう歯ごたえがあると感じるかたも

　　　　　　いらっしゃるだろう。取りあえず、下が解答だ。

　　　　　　=Zen(Mid(Han(Kata($A1)),Len(Han(Kata($A1)))-Col(A1)+1,1))

（●｀ε´●）：悪児さんがひぃひぃ言うようなやつをだしてやれよ。

【´･ω･`】：ここを見ているみなさんが出題してもきっと受けてくれるだろう。

（●｀ε´●）：まさか、悪児さんが敵に背中を見せるようなことはあるまい・・・。

　　　　　　　出題・質問はコメント欄に書いてくれればいい。

　　　　　　　

ごじらを逆さまに(4)

【´･ω･`】：それでは、もう少し難しくして・・・。

　　　　　　A1に全角カタカナの「ゴジラ」をバラバラにして

　　　　　　逆さまにして欲しい。

(●`ε´●)：こんな感じか？

　　　　　　A1：ゴジラ

　　　　　　B1：ラ

　　　　　　C1：゛

　　　　　　D1：シ

　　　　　　E1：゛

　　　　　　F1：ラ

【´･ω･`】：そう。

ごじらを逆さまに(3)

【´･ω･`】：そうそう、この「ごじら」を「らじご」にする問題・・・。

　　　　　　３行マクロでやると・・・

　　　　　For %i = 1 To Len(Range("A1"))
　　　　　　　Range("B1") = Range("B1") & Mid(Range("A1"), Len(Range("A1"))-%i +1,1)
　　　　　next

(●`ε´●)：これでも同じだな。

　　　　　For %i = 1 To Len(Cells(1,1))
　　　　　　　　cells(1,2) = Cells(1,2) & Mid(Cells(1,1), Len(Cells(1,1))-%i +1,1)
　　　　　Next

　　　　　ま、参考までに・・・ということだ。

ごじらを逆さまに(2)

【´･ω･`】：ま、これは簡単だったかも・・・

　　　　　　答えはＢ１に・・・

　　　　　　=Mid($A1,Len($A1)-Col(A1)+1,1)

　　　　　　あとは右方向にコピペ・・・。

　　　

ごじらを逆さまに(1)

【´･ω･`】：ごじらシリーズの第2弾だ。

　　　　　　例えばA1に「ごじら」とあったら

　　　　　　　　　　B1に「ら」

　　　　　　　　　　C1に「じ」

　　　　　　　　　　D1に「ご」

　　　　　　・・・といったふうにバラバラに表示させたいんだ。

　　　　　　どうやったらできるかなぁ・・・？

　　　　　　B1の数式を右方向にコピーするだけで

　　　　　　いいようにしてね。

ごじらをバラバラに(11)

　この「こ゛し゛ら」とあの「ごじら」・・・。

目でよく見ると違うということがわかるけど

If関数は同じものだと判定するんですよね。

Len関数は別物だと判断するけれど・・・。

ごじらをバラバラに(10)

【´･ω･`】：ところでべべちゃん、ごじらをバラバラに(8)でごじらを22文字で

　　　　　　バラバラにするって言ってたけど・・・、どうなったの？

(●`ε´●)：うん、くまぷー、おまえのごじらはダメ！！おれが言っていたのは

　　　　　　　あくまでこの「こ゛し゛ら」・・・。

【´･ω･`】：あ、よ～く見ると・・・。

(●`ε´●)：へへへ、種がわかった？実は三四郎ならば等号を

　　　　　　含めて１８文字！！

　　　　　　

「ごじら」をバラバラに(9)

(●`ε´●)：そう言えば「ごじら」をバラバラに(5)のカテゴリーには

　　　　　　３行マクロってのもあったな？！

【´･ω･`】：べべちゃんは記憶力がいいな。

(●`ε´●)：マクロでやるとどうなるんだ？

【´･ω･`】:：こう・・・。

For %i = 1 To Len(Han(kata(Cells(1,1))))
　　　　　Cells(1, %i +1) = Hira(Zen(Mid(Han(Kata(Cells(1,1))), %i, 1)))
Next

「ごじら」をバラバラに(8)

(●`ε´●)：くまぷー。「ごじら」を「こ」「゛」「し」「゛」「ら」にやっとできたぞ！！

【´･ω･`】：べべちゃん、おめでとう！それではさっそくやり方を

　　　　　　聞かせてくれ。

(●`ε´●)：おー！！まず、答えを先に言おうか。

【´･ω･`】：うん。

(●`ε´●)：A1に「ごじら」、

　　　　　　B1に　=Hira(Zen(Mid(Han(Kata($A1)),Col(A1),1)))

【´･ω･`】：正解だよ。簡単に考え方を説明してくれないか？

(●`ε´●)：よし、それでは・・・。「ごじら」をバラバラに(６) のヒントで

　　　　　　半角カタカナの「ｺﾞｼﾞﾗ」ならば　=Mid($A1,Col(A1),1)

　　　　　　・・・そのままの式で

　　　　　　B1に「ｺ」

　　　　　　C1に「ﾞ」

　　　　　　D1に「ｼ」

　　　　　　E1に「ﾞ」

　　　　　　F1に「ﾗ」

　　　　　　というふうにできるということがわかった。だから、まず・・・

　　　　　　Kata（$A1）・・・・・・（１）

　　　　　　で文字列をひらがなから全角カタカナに変換する。

　　　　　　さらに(1)をHan（）で半角に変換する。これが下の式だ。

　　　　　　Han(Kata($A1))・・・・・・（２）

　　　　　　この段階で全角ひらがなの「ごじら」が半角カタカナの

　　　　　　「ｺﾞｼﾞﾗ」にすることができた。これにMid()とCol()を

　　　　　　組み合わせれば、ヒントの通り・・・

　　　　　　B1に「ｺ」

　　　　　　C1に「ﾞ」

　　　　　　D1に「ｼ」

　　　　　　E1に「ﾞ」

　　　　　　F1に「ﾗ」

　　　　　　・・・と表示させることが出来る。ここまでで下の式ができあがる。

　　　　　　Mid(Han(Kata($A1)),Col(A1),1)　・・・・・・（３）

　　　　　　しかし、このままでは半角カタカナなのでこれを

　　　　　　ひらがな(全角）に戻さなければならない。

　　　　　　まず、（３）をZen()で全角にして、それからHira()を

　　　　　　使えばOKだ。これで答えの式・・・

　　　　　　=Hira(Zen(Mid(Han(Kata($A1)),Col(A1),1)))

　　　　　　の完成だ！！

【´･ω･`】：たぶん、悪児さんの言ってた回答もこれと同じだろう。

(●`ε´●)：ところでくまぷー、おいら、もっとすごい発見を

　　　　　　したぞ！！エクセルを使っていいなら、この「こ゛し゛ら」を

　　　　　　バラバラにするのに22文字だ！！

【´･ω･`】：べべちゃん、色々考えるね。ある意味手品師だね。

　　　　　　くまぷーが三四郎でやれば・・・。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（続く）　　

「ごじら」をバラバラに(7)

　アクセスログを見るとExcel  Worldからいらっしゃっている方が

かなりいるようです。

　ごじらの問題の元ネタは実は某掲示板の質問です。

ごじらの問題は三四郎を使えばあっさりとできるのですが

エクセル（関数のみ）でやると、とんでもなく複雑になります。

Excel  Worldの住人たちがやったら（もちろんエクセルで）

どんな回答をつくるだろう？

とても興味があります。(^｡^)

※Excel World の住人：EXCELのヘビーユーザの集団で

　　　　　　　EXCELを使わせたら、おそらく国内最強の集団。

　　　　　　　

　

「ごじら」をバラバラに(6)

(●`ε´●)：おい、くまぷー。「ごじら」を「こ」「゛」「し」「゛」「ら」になんて

　　　　　　分解できるのか？おれは一晩、考えたけど全然わからないぞ！！

【´･ω･`】：OK。それではヒントをあげよう。ひらがなや全角のカタカナでは

　　　　　　濁音を清音と濁点に分解することは出来ない。ところが半角の

　　　　　　カタカナならば、それができる。全角文字は濁音も清音も1字なのだが

　　　　　　半角カタカナの場合・・・

　　　　　　　　　　　　　　　　　濁音＝清音＋濁点

　　　　　　でもともと２字で１字を表しているんだ。

(●`ε´●)：そうなのか。だから模試で名前にフリガナをふるとき

　　　　　　濁点も１マス使えって言われるのか・・・。

【´･ω･`】：うん、そう。「ごじら」や「ゴジラ」は３文字だけど「ｺﾞｼﾞﾗ」は

　　　　　　本当は５文字だから、もしも「ごじら」でなく「ｺﾞｼﾞﾗ」がA1に

　　　　　　入力されているならば、ついこの前に

　　　　　　「ごじら」を「ご」「じ」「ら」に分解した下の式・・・

　　　　　　　　　　　　　=Mid($A1,Col(A1),1)

　　　　　　・・・そのままで

　　　　　　B1に「ｺ」

　　　　　　C1に「ﾞ」

　　　　　　D1に「ｼ」

　　　　　　E1に「ﾞ」

　　　　　　F1に「ﾗ」

　　　　　　・・・と表示させることが出来る。

(●`ε´●)：なるほど。『「ごじら」をバラバラに』の（１）から（４）は

　　　　　　（５）のための伏線だったんだな。

【´･ω･`】：そう。

※問題のために一部半角カタカナを使っています。

　文字が化けちゃった人にはゴメンね。m(__)m

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　(続く）

「ごじら」をバラバラに(5)

(●`ε´●)：おい、くまぷー、またごじらか？！

　　　　　　『「ごじら」をバラバラに』はもう解決したんじゃないのか？

【´･ω･`】：いや、ごじらはなかなかしぶとくて

　　　　　　あれぐらいの「バラバラ」じゃ

　　　　　　すぐに復活してしまうんだ！！

(●`ε´●)：「ごじらの逆襲」というわけだな。

【´･ω･`】：で、今回は簡単に蘇らないように

　　　　　　さらにバラバラにするんだ。

　　　　　　例えばA1に「ごじら」とあったら

　　　　　　　　　　B1に「こ」

　　　　　　　　　　C1に「゛」

　　　　　　　　　　D1に「し」

　　　　　　　　　　E1に「゛」

　　　　　　　　　　F1に「ら」

　　　　　　・・・といったふうにバラバラに表示させたいんだ。

　　　　　　どうやったらできるかなぁ・・・？　(^。^)

「ごじら」をバラバラに(4)

【´･ω･`】：それではいよいよ、ごじらの登場だ。

　　　　　　=Mid($A1,Col(A1),1)　・・・（１）

　　　　　　の説明に入ろう。

　　　　　　まず、確認しておくが「ごじら」のいるセルはA1だ。

　　　　　　よって・・・　

                $A1="ごじら"

                ・・・となる。これを(1)に代入すると・・・

　　　　　　=Mid("ごじら" ,Col(A1),1)　・・・（２）　!!この式をB1に入力

　　　　　　・・・となる。

　　　　　　(2)の式をC1、D1にコピーすると・・・それぞれ

　　　　　　=Mid("ごじら" ,Col(B1),1)　・・・（３）　　!!C1の式

　　　　　　=Mid("ごじら" ,Col(C1),1)　・・・（４）　　!!D1の式

　　　　　　・・・となる。

(●`ε´●)：お～、コピーすることによって上手く

　　　　　　(2)のCol(A1)が(3)ではCol(B1)に(4)ではCol(C1)に

　　　　　　修正されている！！

【´･ω･`】：そう、ここで

　　　　　　Col(A1)=1

　　　　　　Col(B1)=2

　　　　　　Col(C1)=3

　　　　　　なので・・・(2)(3)(4)はそれぞれ・・・

　　　　　　=Mid("ごじら" ,1,1)　・・・（5）　 !!B1の式

　　　　　　

　　　　　　=Mid("ごじら" ,2,1)　・・・（6）　　!!C1の式

　　　　　　=Mid("ごじら" ,3,1)　・・・（7）　　!!D1の式

               （５）のMId()はごじらの1文字目から1文字分、すなわちごを

　　　　　（6）のMId()はごじらの2文字目から1文字分、すなわちじを

　　　　　（７）のMId()はごじらの３文字目から1文字分、すなわちらを

　　　　　取り出すので・・・

(●`ε´●)：セルB1にご、セルC1にじ、セルD1にらと表示されると

　　　　　　いうわけだ。

【´･ω･`】：うん。ところで今何時？

　　　　　　5時ら！！（投稿時間）

「ごじら」をバラバラに(3)

【´･ω･`】：B１に=Mid($A1,Col(A1),1)ということだったが

　　　　　ちょっと解説をしておこうか。まず、この式の構造だ。

　　　　　この式はCol()とMid()の2つの関数から出来ている。

　　　　　関数を組み合わせた場合、内側のものから実行される。

　　　　　従って、この場合はCol(A1),から実行される。

　　　　　べべちゃん、　Col()の書式は？

(●`ε´●)：=Col（セル番地）だ。

【´･ω･`】：で、どういう働きをするんだ？

(●`ε´●)：=Col（）を使うと与えられた引数（ひきすう）の列番号を

　　　　　　求めることができる。

【´･ω･`】：具体的は？引数がA1だとすると？

(●`ε´●)：A1の列番号は１だから=Col(A1)は１になる。

　　　　　　これがこの式のミソだな。

【´･ω･`】：では次はMid()の説明だ。Mid()を使うと文字列から

　　　　　　部分文字列を取り出すことができるんだ。

　　　　　　書式は・・・

　　　　　　=Mid（元になる文字列, 部分文字列の開始位置, 　文字数)

　　　　　　･･･だ。

　　　　　　では具体的に・・・。

　　　　　　べべちゃん、下の式の答えは？

　　　　　　=Mid("ごじらは怪獣の王です。",5,4)

(●`ε´●)：「ごじらは怪獣の王です。」の5文字目から

　　　　　　４文字分だから怪獣の王が答えだ。

【´･ω･`】：あとは蛇足になるかもしれないがセル番地にくっついた

　　　　　$記号についてだ。例えばD1に=SUM（A1：C1)入力し

　　　　　この式をD2にコピーすると自動的に=SUM（A2：C2)となる。

　　　　　また=SUM（A1：C1)をE1にコピーすると=SUM（B1：D1)と

　　　　　なる。

　　　　　　これは便利なこともあるが、場合によっては困るときもある。

　　　　　列を示すアルファベットの前に$記号を付けておけば列方向（横）に

　　　　　コピーしても参照するセルが固定される。また行を示す数字の

　　　　　前に$記号を付ければ行方向（縦）にコピーしても参照するセルが

　　　　　変わることはないんだ。

　　　　　専門用語で言うと、$記号が付いているのを絶対参照、付いていないのを

　　　　　相対参照って言うんだ。　

(●`ε´●)：おい、くまぷー。どうでもいいけどなかなか本題の

　　　　　　=Mid($A1,Col(A1),1)の説明に入らないな。

【´･ω･`】：しかたがないよ。安っぽい怪獣映画と違ってごじらは

　　　　　なかなか登場しないんだ。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（続く）

「ごじら」をバラバラに(2)

(●`ε´●)：え～と・・・

　　　　　　A1に「ごじら」がいるとすると・・・

　　　　　　B１に=Mid($A1,Col(A1),1)と入力して右方向にコピペ!!

【´･ω･`】：さすがべべちゃん！！

　　　　　　なかなかだね。さすがだよ。

(●`ε´●)：おい、くまぷー、褒めてくれるのはいいけど

　　　　　　下の「カテゴリー」を見ると「文字列操作」の他に

　　　　　　「３行マクロ」にもなっているぞ！！

【´･ω･`】：う～ん、気がついてしまったか・・・。

　　　　　　今回のテーマはマクロでやるよりも

　　　　　　関数でやったほうが美しいので

　　　　　　知らんぷりしていようと思っていたんだが・・・

(●`ε´●)：そうはいかないな。

【´･ω･`】：では・・・

　　　　　For %i = 1 To Len(Cells(1,1))
　　　　　　　　Cells(1, %i +1) =Mid(Cells(1,1), %i, 1)
　　　　　Next

　　　　　これでどうだ？３行だけど・・・。

「ごじら」をバラバラに(1)

(●`ε´●)：「ごじら」をバラバラだって？！

　　　　　　そんなことができるのは芹沢博士の

　　　　　　オキシジェンデストロイヤーぐらいだろ？！

【´･ω･`】：あ、べべちゃん、そうじゃなくって・・・。

　　　　　　例えばA1に「ごじら」とあったら

　　　　　　　　　　B1に「ご」

　　　　　　　　　　C1に「じ」

　　　　　　　　　　D1に「ら」

　　　　　　・・・といったふうにバラバラに表示させたいんだ。

　　　　　　どうやったらできるかなぁ・・・？

　　　　　　　　　　

氏名の分離

今から、およそ３年前のことです。

くまぷーが教務室に入っていくと

パソコンの前でU先生が、何やら渋い顔をしています。

「あ、くまぷー先生、先生も手伝ってくださいよ」

U先生はイライラしたような様子で言いました。

「何をしているんですか？」

どうやらU先生は学年８クラス分の生徒の

氏名を氏の部分と名の部分に分けているようです。

・・・ですが全て手作業でやっているので２時間やっても

遅々として進まず、かなり疲れていらっしゃるようです。

「あ、こうすればいいんですよ。」

くまぷーが見てみると氏と名は半角のスペースで区切られているようでした。

B列の氏名を元にしてC列に氏、D列に名を表示させるなら・・・

B1に　大隈 重信

ならば

C1に　=Left(B1,Find(" ",B1))

D1に　=Mid(B1,Find(" ",B1)+1,Len(B1)-Find(" ",B1))

とすればOKですよ。(^o^)/~~~

ひらがなを半角カタカナに

ある先生のブログで仕事のために

ひらがなを半角カタカナに変換する必要に

迫られたということでエクセルVBAの数十行に

及ぶコードがアップされてていた。かなり苦労されている

ご様子なので下のようなコメントを

させて頂いた。（青字部分）

エクセルVBAで全角ひらがなを半角カタカナに変換するなら、

下記の３行コードをためしてみてください。

Public Function HiraToHan(R As Range) As String
　　　　HiraToHan = StrConv(StrConv(R, vbKatakana), vbNarrow)
End Function

これを標準モジュールにコピーすれば
HiraToHan関数が標準のワークシート関数同様に使えます。

例えばA1に「ごとう」とあれば
B1に=HiraToHan（A1）とすればいいです。(^o^)/~~~

コードの部分は正味1行なので

だいぶすっきりしているとは思うのだが

三四郎ならば標準のワークシート関数の

Han()とKata()を組み合わせて

例えばA1に「ごとう」とあれば
B1に=Han(Kata(A1))とすればいいです。(^o^)/~~~