"Excel VBA for Teachers" にカテゴリー登録されている15 投稿

2019年5 月16日 (木曜日)

かな-ローマ字変換マクロ

【´･ω･`】：SQSさんから・・・

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

おはようございます。

くまぷー先生、VBA for Teachers 増える予定はないですか（涙）

調べても調べても、見つけられないんです。

生徒の名表、名前をアルファベットに
できる方法は難しいのでしょうか。

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

・・・というメールが来た。

【´･ω･`】：難しくはないが、やることは決まっているので面白みはないので、あまりやる気はしないな・・・

(●`ε´●)：そう言わずに何とかしてやれよ。

【´･ω･`】：しかたがないな。じゃあ、SQSさん自身に作ってもらおうか。

(●`ε´●)：素人でも作れるのか？

【´･ω･`】：大丈夫。題して「知性よりも根性のマクロ」。やり方を指示するから・・・

 

【課題1】

下図のように3名分の名前の入ったシートがある。エクセルの標準機能「置換」を使って、3名の名前に含まれる「き」を「ki」に変換しなさい。

SQS20190516

 

【課題2】

「記録マクロ」を利用し、「課題1」を行い、出来たコードをコメント欄にコピペしなさい。

尚、「記録マクロ」の使用法がわからない場合は「情報」の教員に尋ねなさい。

 

【´･ω･`】：記録マクロは余計なコードも生成してしまう。

Sub Macro2()
'
' Macro2 Macro
''
Range("A2:A4").Select
Selection.Replace What:="き", Replacement:="ki", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

【´･ω･`】：余計な部分を取り除いてマクロ名をわかりやすいものに変えると・・・

Sub かなローマ字変換()
　　　Range("A2:A4").Select
　　　Selection.Replace What:="き", Replacement:="ki"
End Sub

・・・これだけになる。従って・・・

Sub かなローマ字変換()
　　　Range("A2:A4").Select
　　　Selection.Replace What:="き", Replacement:="ki"
End Sub

赤字の部分をコピペして「き」と「ki」の部分を、それぞれ「きゃ」と「kya」に変えれば「きゃ」が「kya」に変換される。

Sub かなローマ字変換()
　　　Range("A2:A4").Select

　　　Selection.Replace What:="きゃ", Replacement:="kya"
　　　Selection.Replace What:="き", Replacement:="ki"
End Sub

注：マクロは上の行から下へと処理される。従ってかな2文字の処理を、かな1文字の処理の上に記述しないとうまくいかない。

【課題3】

赤字の部分を必要な分だけ只管コピペして、かなローマ字変換マクロを根性で完成させなさい。(^^ゞ

 

【発展】

【´･ω･`】：Range("A2:A4").Select　がマクロを作用させる範囲だということは前に述べた。

(●`ε´●)：おいおい、たったの3人分じゃ、あんまりありがたくないぞ！！

【´･ω･`】：じゃあ、いっそのこと削除してしまおうか。

 

Sub かなローマ字変換()
　　　'Range("A2:A4").Select　（削除）

　　　Selection.Replace What:="きゃ", Replacement:="kya"
　　　Selection.Replace What:="き", Replacement:="ki"
End Sub

(●`ε´●)：大丈夫なのか？

【´･ω･`】：うん。マクロを実行する前にあらかじめ、範囲をマウスか何かで選択しておけばいい。

(●`ε´●)：なるほど、その方が柔軟かもな。

【´･ω･`】：するとコードはこんな感じになるから・・・

Sub かなローマ字変換()
　　　Selection.Replace What:="きゃ", Replacement:="kya"
　　　Selection.Replace What:="き", Replacement:="ki"
End Sub

・・・素人にも、どこで何をやっているか一目瞭然だというわけだ。

(●`ε´●)：なんの技もない。まさに「知性よりも根性のマクロ」だな。

【´･ω･`】：ま、with　ブロックだとか、書き方の作法とかは時間があったら、どこかできちんと学んでね　m(__)m

 

 

【考察】

課題1の目的：エクセルの標準機能、「置換」の確認。

課題2の目的：記録マクロを使って、「置換」コードの確認。

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

★完成版　かなローマ字変換　（SQSさん　提供）

★使い方　変換したい範囲をあらかじめ選択してマクロを実行する。

★平仮名・片仮名をローマ字に変換する。

 

Sub かなローマ字変換()
　　　Selection.Replace What:="ふぁ", Replacement:="fa"
        Selection.Replace What:="りゃ", Replacement:="rya"
        Selection.Replace What:="みゃ", Replacement:="mya"
        Selection.Replace What:="ひゃ", Replacement:="hya"
        Selection.Replace What:="にゃ", Replacement:="nya"
        Selection.Replace What:="ちゃ", Replacement:="cha"
        Selection.Replace What:="しゃ", Replacement:="sha"
        Selection.Replace What:="きゃ", Replacement:="kya"
        Selection.Replace What:="ぴゃ", Replacement:="pya"
        Selection.Replace What:="びゃ", Replacement:="bya"
        Selection.Replace What:="ぢゃ", Replacement:="dya"
        Selection.Replace What:="じゃ", Replacement:="ja"
        Selection.Replace What:="ぎゃ", Replacement:="gya"
        Selection.Replace What:="ふぃ", Replacement:="fi"
        Selection.Replace What:="ぴゅ", Replacement:="pyu"
        Selection.Replace What:="びゅ", Replacement:="byu"
        Selection.Replace What:="ぢゅ", Replacement:="dyu"
        Selection.Replace What:="じゅ", Replacement:="ju"
        Selection.Replace What:="ぎゅ", Replacement:="gyu"
        Selection.Replace What:="ふぇ", Replacement:="fe"
        Selection.Replace What:="りゅ", Replacement:="ryu"
        Selection.Replace What:="みゅ", Replacement:="myu"
        Selection.Replace What:="ひゅ", Replacement:="hyu"
        Selection.Replace What:="にゅ", Replacement:="nyu"
        Selection.Replace What:="ちゅ", Replacement:="chu"
        Selection.Replace What:="しゅ", Replacement:="shu"
        Selection.Replace What:="きゅ", Replacement:="kyu"
        Selection.Replace What:="ぴょ", Replacement:="pyo"
        Selection.Replace What:="びょ", Replacement:="byo"
        Selection.Replace What:="ぢょ", Replacement:="dyo"
        Selection.Replace What:="じょ", Replacement:="jo"
        Selection.Replace What:="ぎょ", Replacement:="gyo"
        Selection.Replace What:="ふぉ", Replacement:="fo"
        Selection.Replace What:="りょ", Replacement:="ryo"
        Selection.Replace What:="みょ", Replacement:="myo"
        Selection.Replace What:="ひょ", Replacement:="hyo"
        Selection.Replace What:="にょ", Replacement:="nyo"
        Selection.Replace What:="ちょ", Replacement:="cho"
        Selection.Replace What:="しょ", Replacement:="sho"
        Selection.Replace What:="きょ", Replacement:="kyo"
        Selection.Replace What:="ちぇ", Replacement:="che"

        Selection.Replace What:="ファ", Replacement:="fa"
        Selection.Replace What:="リャ", Replacement:="rya"
        Selection.Replace What:="ミャ", Replacement:="mya"
        Selection.Replace What:="ヒャ", Replacement:="hya"
        Selection.Replace What:="ニャ", Replacement:="nya"
        Selection.Replace What:="チャ", Replacement:="cha"
        Selection.Replace What:="シャ", Replacement:="sha"
        Selection.Replace What:="キャ", Replacement:="kya"
        Selection.Replace What:="ピャ", Replacement:="pya"
        Selection.Replace What:="ビャ", Replacement:="bya"
        Selection.Replace What:="ヂャ", Replacement:="dya"
        Selection.Replace What:="ジャ", Replacement:="ja"
        Selection.Replace What:="ギャ", Replacement:="gya"
        Selection.Replace What:="フィ", Replacement:="fi"
        Selection.Replace What:="ピュ", Replacement:="pyu"
        Selection.Replace What:="ビュ", Replacement:="byu"
        Selection.Replace What:="ヂュ", Replacement:="dyu"
        Selection.Replace What:="ジュ", Replacement:="ju"
        Selection.Replace What:="ギュ", Replacement:="gyu"
        Selection.Replace What:="フェ", Replacement:="fe"
        Selection.Replace What:="リュ", Replacement:="ryu"
        Selection.Replace What:="ミュ", Replacement:="myu"
        Selection.Replace What:="ヒュ", Replacement:="hyu"
        Selection.Replace What:="ニュ", Replacement:="nyu"
        Selection.Replace What:="チュ", Replacement:="chu"
        Selection.Replace What:="シュ", Replacement:="shu"
        Selection.Replace What:="キュ", Replacement:="kyu"
        Selection.Replace What:="ピョ", Replacement:="pyo"
        Selection.Replace What:="ビョ", Replacement:="byo"
        Selection.Replace What:="ヂョ", Replacement:="dyo"
        Selection.Replace What:="ジョ", Replacement:="jo"
        Selection.Replace What:="ギョ", Replacement:="gyo"
        Selection.Replace What:="フォ", Replacement:="fo"
        Selection.Replace What:="リョ", Replacement:="ryo"
        Selection.Replace What:="ミョ", Replacement:="myo"
        Selection.Replace What:="ヒョ", Replacement:="hyo"
        Selection.Replace What:="ニョ", Replacement:="nyo"
        Selection.Replace What:="チョ", Replacement:="cho"
        Selection.Replace What:="ショ", Replacement:="sho"
        Selection.Replace What:="キョ", Replacement:="kyo"
        Selection.Replace What:="チェ", Replacement:="che"

        Selection.Replace What:="ん", Replacement:="n"
        Selection.Replace What:="わ", Replacement:="wa"
        Selection.Replace What:="ら", Replacement:="ra"
        Selection.Replace What:="や", Replacement:="ya"
        Selection.Replace What:="ま", Replacement:="ma"
        Selection.Replace What:="は", Replacement:="ha"
        Selection.Replace What:="な", Replacement:="na"
        Selection.Replace What:="た", Replacement:="ta"
        Selection.Replace What:="さ", Replacement:="sa"
        Selection.Replace What:="か", Replacement:="ka"
        Selection.Replace What:="あ", Replacement:="a"
        Selection.Replace What:="ぱ", Replacement:="pa"
        Selection.Replace What:="ば", Replacement:="ba"
        Selection.Replace What:="だ", Replacement:="da"
        Selection.Replace What:="ざ", Replacement:="za"
        Selection.Replace What:="が", Replacement:="ga"
        Selection.Replace What:="り", Replacement:="ri"
        Selection.Replace What:="み", Replacement:="mi"
        Selection.Replace What:="ひ", Replacement:="hi"
        Selection.Replace What:="に", Replacement:="ni"
        Selection.Replace What:="ち", Replacement:="chi"
        Selection.Replace What:="し", Replacement:="shi"
        Selection.Replace What:="き", Replacement:="ki"
        Selection.Replace What:="い", Replacement:="i"
        Selection.Replace What:="ぴ", Replacement:="pi"
        Selection.Replace What:="び", Replacement:="bi"
        Selection.Replace What:="ぢ", Replacement:="di"
        Selection.Replace What:="じ", Replacement:="ji"
        Selection.Replace What:="ぎ", Replacement:="gi"
        Selection.Replace What:="る", Replacement:="ru"
        Selection.Replace What:="ゆ", Replacement:="yu"
        Selection.Replace What:="む", Replacement:="mu"
        Selection.Replace What:="ふ", Replacement:="fu"
        Selection.Replace What:="ぬ", Replacement:="nu"
        Selection.Replace What:="つ", Replacement:="tsu"
        Selection.Replace What:="す", Replacement:="su"
        Selection.Replace What:="く", Replacement:="ku"
        Selection.Replace What:="う", Replacement:="u"
        Selection.Replace What:="ぷ", Replacement:="pu"
        Selection.Replace What:="ぶ", Replacement:="bu"
        Selection.Replace What:="づ", Replacement:="du"
        Selection.Replace What:="ず", Replacement:="zu"
        Selection.Replace What:="ぐ", Replacement:="gu"
        Selection.Replace What:="ヴ", Replacement:="vu"
        Selection.Replace What:="れ", Replacement:="re"
        Selection.Replace What:="め", Replacement:="me"
        Selection.Replace What:="へ", Replacement:="he"
        Selection.Replace What:="ね", Replacement:="ne"
        Selection.Replace What:="て", Replacement:="te"
        Selection.Replace What:="せ", Replacement:="se"
        Selection.Replace What:="け", Replacement:="ke"
        Selection.Replace What:="え", Replacement:="e"
        Selection.Replace What:="ぺ", Replacement:="pe"
        Selection.Replace What:="べ", Replacement:="be"
        Selection.Replace What:="で", Replacement:="de"
        Selection.Replace What:="ぜ", Replacement:="ze"
        Selection.Replace What:="げ", Replacement:="ge"
        Selection.Replace What:="を", Replacement:="wo"
        Selection.Replace What:="ろ", Replacement:="ro"
        Selection.Replace What:="よ", Replacement:="yo"
        Selection.Replace What:="も", Replacement:="mo"
        Selection.Replace What:="ほ", Replacement:="ho"
        Selection.Replace What:="の", Replacement:="no"
        Selection.Replace What:="と", Replacement:="to"
        Selection.Replace What:="そ", Replacement:="so"
        Selection.Replace What:="こ", Replacement:="ko"
        Selection.Replace What:="お", Replacement:="o"
        Selection.Replace What:="ぽ", Replacement:="po"
        Selection.Replace What:="ぼ", Replacement:="bo"
        Selection.Replace What:="ど", Replacement:="do"
        Selection.Replace What:="ぞ", Replacement:="zo"
        Selection.Replace What:="ご", Replacement:="go"
        Selection.Replace What:="ゑ", Replacement:="we"
        Selection.Replace What:="ゐ", Replacement:="wi"
        Selection.Replace What:="っ", Replacement:="t"
        Selection.Replace What:="ー", Replacement:="-"

        Selection.Replace What:="ン", Replacement:="n"
        Selection.Replace What:="ワ", Replacement:="wa"
        Selection.Replace What:="ラ", Replacement:="ra"
        Selection.Replace What:="ヤ", Replacement:="ya"
        Selection.Replace What:="マ", Replacement:="ma"
        Selection.Replace What:="ハ", Replacement:="ha"
        Selection.Replace What:="ナ", Replacement:="na"
        Selection.Replace What:="タ", Replacement:="ta"
        Selection.Replace What:="サ", Replacement:="sa"
        Selection.Replace What:="カ", Replacement:="ka"
        Selection.Replace What:="ア", Replacement:="a"
        Selection.Replace What:="パ", Replacement:="pa"
        Selection.Replace What:="バ", Replacement:="ba"
        Selection.Replace What:="ダ", Replacement:="da"
        Selection.Replace What:="ザ", Replacement:="za"
        Selection.Replace What:="ガ", Replacement:="ga"
        Selection.Replace What:="リ", Replacement:="ri"
        Selection.Replace What:="ミ", Replacement:="mi"
        Selection.Replace What:="ヒ", Replacement:="hi"
        Selection.Replace What:="ニ", Replacement:="ni"
        Selection.Replace What:="チ", Replacement:="chi"
        Selection.Replace What:="シ", Replacement:="shi"
        Selection.Replace What:="キ", Replacement:="ki"
        Selection.Replace What:="イ", Replacement:="i"
        Selection.Replace What:="ピ", Replacement:="pi"
        Selection.Replace What:="ビ", Replacement:="bi"
        Selection.Replace What:="ヂ", Replacement:="di"
        Selection.Replace What:="ジ", Replacement:="ji"
        Selection.Replace What:="ギ", Replacement:="gi"
        Selection.Replace What:="ル", Replacement:="ru"
        Selection.Replace What:="ユ", Replacement:="yu"
        Selection.Replace What:="ム", Replacement:="mu"
        Selection.Replace What:="フ", Replacement:="fu"
        Selection.Replace What:="ヌ", Replacement:="nu"
        Selection.Replace What:="ツ", Replacement:="tsu"
        Selection.Replace What:="ス", Replacement:="su"
        Selection.Replace What:="ク", Replacement:="ku"
        Selection.Replace What:="ウ", Replacement:="u"
        Selection.Replace What:="プ", Replacement:="pu"
        Selection.Replace What:="ブ", Replacement:="bu"
        Selection.Replace What:="ヅ", Replacement:="du"
        Selection.Replace What:="ズ", Replacement:="zu"
        Selection.Replace What:="グ", Replacement:="gu"
        Selection.Replace What:="ヴ", Replacement:="vu"
        Selection.Replace What:="レ", Replacement:="re"
        Selection.Replace What:="メ", Replacement:="me"
        Selection.Replace What:="ヘ", Replacement:="he"
        Selection.Replace What:="ネ", Replacement:="ne"
        Selection.Replace What:="テ", Replacement:="te"
        Selection.Replace What:="セ", Replacement:="se"
        Selection.Replace What:="ケ", Replacement:="ke"
        Selection.Replace What:="エ", Replacement:="e"
        Selection.Replace What:="ペ", Replacement:="pe"
        Selection.Replace What:="ベ", Replacement:="be"
        Selection.Replace What:="デ", Replacement:="de"
        Selection.Replace What:="ゼ", Replacement:="ze"
        Selection.Replace What:="ゲ", Replacement:="ge"
        Selection.Replace What:="ヲ", Replacement:="wo"
        Selection.Replace What:="ロ", Replacement:="ro"
        Selection.Replace What:="ヨ", Replacement:="yo"
        Selection.Replace What:="モ", Replacement:="mo"
        Selection.Replace What:="ホ", Replacement:="ho"
        Selection.Replace What:="ノ", Replacement:="no"
        Selection.Replace What:="ト", Replacement:="to"
        Selection.Replace What:="ソ", Replacement:="so"
        Selection.Replace What:="コ", Replacement:="ko"
        Selection.Replace What:="オ", Replacement:="o"
        Selection.Replace What:="ポ", Replacement:="po"
        Selection.Replace What:="ボ", Replacement:="bo"
        Selection.Replace What:="ド", Replacement:="do"
        Selection.Replace What:="ゾ", Replacement:="zo"
        Selection.Replace What:="ゴ", Replacement:="go"
        Selection.Replace What:="ヱ", Replacement:="we"
        Selection.Replace What:="ヰ", Replacement:="wi"
        Selection.Replace What:="ッ", Replacement:="t"
        Selection.Replace What:="ー", Replacement:="-"

End Sub

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

※　改訂版出しました。（2021／10／01）Excel Maniacs: かな-ローマ字変換マクロ改訂版 (typepad.jp)

下図のように名前の頭を大文字に変換します。

20211001_2

 

投稿情報: 午前 09時04分 カテゴリー: Excel VBA for Teachers, 教員のためのコンピュータ活用術 | | コメント (37)

2016年3 月 1日 (火曜日)

Excel 読み上げ２

(●`ε´●)：得点の入力が終わったので確認をしたい。

20160301_01

【´･ω･`】：読み上げ機能を使うと便利だな。

　　　　　　マウスで読ませたい部分を選択して

　　　　　　下のマクロ「読み上げ」を実行すると受験番号、Name、得点の

　　　　　　順で読み上げてくれる。

 

Sub 読み上げ()
    Selection.Speak
End Sub

 サンプルファイル： 読み上げをダウンロード

 

 

投稿情報: 午後 07時46分 カテゴリー: Excel VBA for Teachers | | コメント (0)

2016年2 月13日 (土曜日)

Excel 読み上げ

得点の入力の時にエクセルの読み上げ機能を使えば便利だと思って

メニューを探したのだが見つからなかった。2010ではリボンのどこかに行ってしまっているらしい。

あちこち探すのも面倒なので、VBAを利用することにした。

ワークシートのChangeイベントを利用して・・・

 

Private Sub Worksheet_Change(ByVal Target As Range)

        Target.Speak

End Sub

 

これでEnterキーを押すタイミングで数字を読み上げてくれる・・・

入力ミスが減りますのぉ・・・(^_-)-☆

投稿情報: 午後 06時08分 カテゴリー: Excel VBA for Teachers | | コメント (0)

« 戻る | 進む »

検索

カテゴリー

最近の記事

最近のコメント

2024年8 月

日 月 火 水 木 金 土
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31

TOP　メニュー

フィードを購読