エクセル2010で、データの表記を半角カナだけを全角にし、半角英数はそのままにしたいのですが、下記の方法No.2では上限にかかり使用が出来ませんでした。
http://oshiete.goo.ne.jp/qa/5985323.html
どなたか良い関数を教えてください。
No.9ベストアンサー
- 回答日時:
No.4, 5、cjです。
No.5補足欄へのレスです。
◆
> 対象シートをひらいた状態で記述をVBにもいれたのですが、
手順が正しければ、
ユーザー定義関数を書き込む標準モジュールを表示した際に
Visual Basic Editor ウィンドウの最上部に
Microsoft Visual Basic for Applications - ブック名 - [Module1 (コード)]
のように表示されます。
ここでブック名と、それに続くモジュール名がModule1、Module2などになっていれば、
正しく設定できることが確認できます。
◆
> すぐ横のシートに =KanaJis(A1) で試した所 #NAME?と表示されました。
こちらは、Excelのセルに、通常のExcel関数と同じように数式を入力し確定する
ということですが、
ユーザー定義関数をVBA側で設定した"後"に、Excelのセルに数式を入力し確定する、
という順番になります。
もしも手順を前後してしまった場合は、、
数式を入力したセルに#NAME?と表示されますので、
セルを再度選択して、F2キーで編集状態にし、そのままEnterキー、
という手順で、正しく数式の結果が表示されるようになります。
◆
> あと、VBの記述は保存の必要はありませんでしょうか?
仮に、
ユーザー定義関数を設定しても、保存しないと機能しないのか?
という疑問でしたらば、未保存の状態でも手順が正しければ機能します。
また、
次にブックを開いた時にも機能させる、という意味でしたらば、
通常のブックを保存するのと同じように、保存しなければ消えてしまいます。
既存のブック(*.xlsx)にユーザー定義関数を設定した場合は、
そのまま上書き保存を実行します。
そうすると、次のようなメッセージが表示されます。
| 次の機能はマクロなしのブックに保存できません
| ・VB プロジェクト
| これらの機能が含まれるファイルを保存する場合は、[いいえ]をクリックし、[ファイルの種類]ボックスでマクロ有効ファイルの種類を選択してください。
| マクロなしのブックとして保存する場合は、[はい]をクリックしてください。
| [はい] [いいえ] [ヘルプ]
ユーザー定義関数を次からも使えるようにする為には、
既存のブックを新たにマクロ有効ブックとして保存し直す必要があります。
手順としては、上記のメッセージダイアログで[いいえ]をクリックし、
[名前を付けて保存]ダイアログが表示されますから、
[ファイルの種類]ボックスのリストの中から
[Excel マクロ有効ブック(*.xlsm)]
を選択して[保存]をクリックします。
この場合注意が必要なのは、
既存のブック(*.xlsx)、とは別物のファイルとして、
名前が同じで拡張子が違うマクロ有効ブック(*.xlsm)を
新たに作成し、以後、マクロ有効ブック(*.xlsm)を使うようにする
ということです。
.xlsxと.xlsmの違い、ということですと、
.xlsmに移行してもExcelブックとしての機能に不足が出ることは一切ありません。
喩えるなら「大は小を兼ねる」的に「.xlsmは.xlsxを兼ねる」ようなものです。
実際、私の場合は、マクロやユーザー定義関数を使わないブックも
常に.xlsmで保存しています。
(そうしておいた方が、将来的にVBAを導入する必要が生じた時に移行が簡単だからです)
ファイルのサイズとしてはVBAプロジェクトの分だけ大きくなりますが、
今回の場合なら、せいぜい7~8KB程度の違いです。
ただし、既存のブック(*.xlsx)に対して
各エクスプローラーで設定したショートカットや各種リンクなどある場合は
新たな.xlsmブックを運用するに当たり書き換えるなどの手当てが必要になる場合もあるでしょう。
ハッキリさせておきますが、
マクロ有効ブック(*.xlsm)は、既存のブック(*.xlsx)の代わりに使うようになるものですから、
既存のブック(*.xlsx)は使わなくなる、ということです。
◆◆
と、ここまで通して、
ひとつのブック単位でユーザー定義関数を設定する方法だけを説明してきましたが、
「既存のブックを破棄して新しいブックに移行するのは困る」とか、
[複数のブックでユーザー定義関数を適用したい]というような要望に応えるように
Excelにはアドインファイルというものが用意されています。
(Excelとしては極々一般的なシステムなのですが職場によっては、
セキュリティ管理の面から利用を規制している場合も稀にあるようなので、
念の為システム管理者に相談した方が好いケースもあるでしょう。)
アドインファイルに登録したユーザー定義関数は
すべてのExcelブックについて
アドインをオプションとして適用させることで利用が可能になります。
この場合はマクロ有効ブックである必要はありませんので
既存のブック(*.xlsx)についても、継続的な利用が可能です。
(既述の)個別のブックにユーザー定義関数を登録する方法の代わりに
アドインファイルに登録する手順を示します。
(既述の方法との重複は避けてください。どちらかひとつの方法を選ぶという意味。)
========================================
【新規のアドインブックにユーザー定義関数を登録】
1■
ふつーに、新規のExcelブックを開いてから、そのまま、
Alt キーを押下げながら F11 キー
→Visual Basic Editor を起動します。
2■
Visual Basic Editor のメニューバー
[挿入(_I)]を選択→[標準モジュール(_M)]を選択
→コード記載用の新しいシート[標準モジュール]が開きます
3■
[標準モジュール]に
提示したユーザー定義関数について
==ここから==と書かれた行から、==ここまで==と書かれた行まで、
まるごとコピーしたものを、そのまま貼り付けます。
4■
Visual Basic Editor を閉じるボタンで閉じ、Excel画面に戻る。
5■
[名前を付けて保存]をクリックし、
[ファイルの種類]ボックスのリストの中から
[Excel アドイン(*.xlam)]を選択して
任意の●アドイン名●を拡張子.xlamの前に記入し
[保存]をクリックします。
以上でユーザー定義関数を登録した新規のアドインブックの作成完了です。
========================================
【アドインを適用しユーザー定義関数をすべてのブックで使用可能にする】
1■
Excelのリボンの表示中のタブ上を右クリック
表示されたメニューの[リボンのユーザー設定(_R)]を選択して
表示された[リボンのユーザー設定]ダイアログの
右側大きい方のリストボックス[メインタブ]の中から
[開発]にチェックを入れて[OK]
→リボンに[開発]タブが追加されます。
2■
リボンの[開発]タブから[アドイン]をクリック
表示された[有効なアドイン]リストの中から
上記手順5■で保存した●アドイン名●にチェックを入れて[OK]
以上でアドインの適用が完了し、ユーザー定義関数はすべてのブックで使用できるようになります。
========================================
アドインというと馴染みのない人もいるでしょうけれど、
Excelの一般機能を補うような形でデフォルトの状態で適用されたアドインもあるほど
実は一般的なものです。
今回のケースもそうですが、単純に「使える関数を増やすもの」と考えてください。
デメリットはありません。
運用が簡単、という意味ではアドイン登録する方が遥かに優ります。
なので、試し終って導入を決めたらば、今回説明した手順でアドイン登録してください。
◆◆
ちなみに、
他の方の回答への補足欄をみて気になったことですが、
> どなたか良い関数を教えてください。
ということだったので、No.4, 5では関数を紹介したのですが、
この関数の戻り値を、定数として取得したならば
関数を設定したセルをコピーしてそのまま[値の貼付け]をすれば済むことです。
或いは、
選択中のセル範囲を、関数で処理した文字列値へと直接置換したいという場合は、
KanaJis()関数を使ったマクロで対応します。
' ' ========この行から========
Sub 半角カナを全角にそれ以外はそのまま()
Dim r As Range
For Each r In Selection
r.Value = KanaJis(r.Value)
Next r
End Sub
' ' ========この行まで========
No.4で説明した手順で、ユーザー定義関数の代わりに、上記マクロを標準モジュールに登録します。、
Alt+F8でマクロのリストを表示して[マクロ名]を選択して[実行]します。
◆◆◆
長くなってしまいました。
ますます、大変そうに見えるかも知れませんが、それもこれも
日常業務を簡易に済ませる為の準備と思って取り組んでみてください。
説明が行き届かない点もあるかも知れませんが、不明な点は遠慮なくお訊ねください。
以上です。
No.10
- 回答日時:
No6です。
ワークシート関数を使った数式の方法を知りたいと思い回答したわけですが、>これらの結果の文字列は選択すると数式が表示されるのですが、
> 表示されている文字列を文字列として選択する事は出来ないのでしょうか?
数式である以上、できません。orz
値の貼り付けを行ってからの作業になり、VBAを使ったほうが理解しやすいと思います。
No.8
- 回答日時:
参考までに(互換モードでも対応できる方法)。
Excel2003以前のバージョンでもネストの制限にかからない方法として以下のような名前定義を利用する方法があります。
元データがA2セルにある場合、数式入力セルを選択して「挿入」「名前」「定義」(2007以降のバージョンで互換モードの場合なら数式タブの「名前の定義」)で例えば「tikan1」と名前を付け、参照範囲に「=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JIS(Sheet1!A2),"0",0),"1",1),"2",2),"3",3),"4",4),"5",5)」と入力します。
最後に数式入力セルで以下の式を入力すれば、2003以前のバージョンでもネストの制限にかからず計算することができます。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(tikan1,"6",6),"7",7),"8",8),"9",9)
No.7
- 回答日時:
>下記の方法No.2では上限にかかり使用が出来ませんでした。
入れ子のレベル数が上限を超えているというメッセージが出たのでしょうか?
基本的に、Excel2007以降のバージョンなら入れ子の制限にはかかりませんので、エラー表示が出たならファイル形式がExcel2003以前のバージョンの形式(互換モード:拡張子xls)でファイルを保存しているのではないでしょうか。
その場合は、名前を付けて保存でデフォルトのxlsxの形式でファイルを保存し直してみてください。
No.6
- 回答日時:
関数で無理やり 最大253文字までとして(~XL2003)
A1セルに対象文字列
B1セルに 結果の
=INDEX(D1:IV1,COUNTA(D1:IV1))
下へオートフィル
C1セルは入力しない
D1セルに
=IF(LEN(JIS($A1))=LEN(C1),C1,C1&IF(OR(CODE(MID($A1,COLUMN(A1),1))={222,223}),"",
IF(IF(LEN(MID($A1,COLUMN(B1),1))=0,FALSE(),OR(CODE(MID($A1,COLUMN(B1),1))={222,223})),
JIS(MID($A1,COLUMN(A1),2)),
IF(AND(CODE(MID($A1,COLUMN(A1),1))>=166,CODE(MID($A1,COLUMN(A1),1))<=221),
JIS(MID($A1,COLUMN(A1),1)),MID($A1,COLUMN(A1),1)))))
右へ下へオートフィル
この回答への補足
ありがとうございます。表示されました。
これらの結果の文字列は選択すると数式が表示されるのですが、
表示されている文字列を文字列として選択する事は出来ないのでしょうか?
宜しくお願いします。
No.5
- 回答日時:
No.4、cjです。
ユーザー定義関数を、もう一種類あげておきます。
こちらの方が内容的には難しいかも知れませんが、
精度もパフォーマンスも向上する筈です。
これも今春に書いていたものですが、こちらの方がお奨めです。
導入の手順は、No.4と同様です。
新しい標準モジュールに、
過不足なく(他に記述がない状態)で貼り付けて使ってください。
Excelシートでは
=KanaJis(A1)
のように設定します。
' ' ===============ここから===============
Dim oRegExp As Object
' ' 文字列を引数に、半角カナだけを全角に置換
' ' 正規表現版
Function KanaJis(ByVal sSrc As String) As String
Dim sBuf As String
Dim Matches As Object
Dim Match As Object
sBuf = sSrc
On Error GoTo CrRegExp_
Set Matches = oRegExp.Execute(sBuf)
On Error GoTo 0
For Each Match In Matches
sBuf = Replace(sBuf, Match, StrConv(Match, vbWide), , 1)
Next
KanaJis = sBuf
Exit Function
CrRegExp_:
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = "[\uFF66-\uFF9F]+"
oRegExp.Global = True
Resume
End Function
' ' ===============ここまで===============
この回答への補足
お返事ありがとうございます。
対象シートをひらいた状態で記述をVBにもいれたのですが、
すぐ横のシートに =KanaJis(A1) で試した所 #NAME?と表示されました。
あと、VBの記述は保存の必要はありませんでしょうか?
No.4
- 回答日時:
こんにちは。
お邪魔します。> どなたか良い関数を教えてください。
通常のExcelの関数だけでは、難しい、というか、
本当の意味で確実な置換となると
私には出来ませんし、出来る人いる?のでしょうか。
濁点・半濁点などを除いたア~ンのような簡単な条件なら
できるのかも知れませんが、そういう需要はないですよね。
"関数を"ということなので、
ユーザー定義関数(UDF)で、お応えします。
とりあえず、新規のブックで試してください。
1)
検証サンプルとして、以下の数式をA1に設定
=ASC("ハ マ オ(4)パパa(32)マンマk(29)オジ(37)オジジ(62)")
2)
後述の手順でユーザー定義関数"Kana2Wide()"を使えるようにしたら
3)
どこか空いているセルに
以下の数式を普通の関数と同じ要領で設定
=kana2wide(A1)
【ユーザー定義関数"Kana2Wide()"を使えるようにする】
1■
対象のExcelブックが開いている状態で、
Alt キーを押下げながら F11 キー
→Visual Basic Editor を起動します。
2■
Visual Basic Editor のメニューバー
[挿入(_I)]を選択→[標準モジュール(_M)]を選択
→コード記載用の新しいシート[標準モジュール]が開きます
3■
[標準モジュール]に
以下、==ここから==、==ここまで==、と示した範囲を
まるごとコピーしたものを、そのまま貼り付けます。
4■
Visual Basic Editor を閉じるボタンで閉じ、Excel画面に戻る。
以上で準備完了です。
Excelのバージョンが2007以降であれば、マクロ有効ブックとして保存します。
それ以前のバージョンでは、普通に保存しておいて、
ブックを開く際に問い合わせるダイアログが表示されたら、
マクロを有効にするように選択します。
ユーザー定義関数やVBAと聞くと難しく考えてしまう方が多いですけれど、
面倒なのは最初だけ、日常業務を易しくサポートしてくれるものだと考えてください。
何か疑問や相談などあれば補足欄にでも書いてみてください。
以下、今年の春に質問への回答用に書いていたものです。
' ' ===============ここから===============
' ' 文字列を引数に、半角カナだけを全角に置換する関数
Function Kana2Wide(ByVal sSrc As String) As String
Dim sBuf As String
Dim sPtn As String
Dim nLen As Long
Dim nPos As Long
Dim nSt As Long
Dim i As Long
Dim flg As Boolean
sPtn = "[" & Chr$(166) & "-" & Chr$(223) & "]" ' "[ヲ-゜]" ←半角カナ
nLen = Len(sSrc)
sBuf = String$(nLen + 1, vbNullChar)
flg = Asc(sSrc) > 165 And Asc(sSrc) < 224
nSt = 1
nPos = 1
For i = 1 To nLen + 1
If (Mid$(sSrc, i, 1) Like sPtn Xor flg) Or i > nLen Then
If flg Then
Mid(sBuf, nPos) = StrConv(Mid$(sSrc, nSt, i - nSt), vbWide)
Else
Mid(sBuf, nPos) = Mid$(sSrc, nSt, i - nSt)
End If
flg = Not flg
nSt = i
nPos = InStr(nPos + 1&, sBuf, vbNullChar)
End If
Next i
Kana2Wide = Left$(sBuf, nPos - 1)
End Function
' ' ===============ここまで===============
No.1
- 回答日時:
こんにちは!
お望みでない方法かもしれませんが、VBAでの一例です。
画面左下の操作したいSheet見出し上で右クリック → コードの表示 → VBE画面に
↓のコードをコピー&ペーストしてマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
Sub Sample1() 'この行から
Dim i As Long, k As Long, str As String
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
For k = 1 To Len(Cells(i, "A"))
str = Mid(Cells(i, "A"), k, 1)
If str Like "[ア-ン]" Then '←「ア」・「ン」は半角のカタカナに!
Cells(i, "A") = Replace(Cells(i, "A"), str, StrConv(str, vbWide))
End If
Next k
Next i
End Sub 'この行まで
※ 画面ではコード内の「ア」と「ン」が全角になっているかもしれません。
実際は半角で打ち込んでいますが、全角の場合は半角に直してください。
※ 一旦マクロを実行すると元に戻せませんので、
別Sheetでマクロを試してみてください。m(_ _)m
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Access(アクセス) Accessのテキストボックスの入力文字制限 1 2023/01/18 20:43
- Excel(エクセル) エクセルシート中の全角英数字を半角に変換したい 4 2022/07/07 13:14
- Excel(エクセル) エクセルでのデータの統一に関して リストを作成する際、元データをコピペでリストに転記しており、元デー 2 2022/08/31 15:33
- その他(Microsoft Office) WordやExcelで英数字のみ半角または全角にしたい 6 2022/08/03 08:18
- Visual Basic(VBA) エクセルの数式で教えてください。 1 2023/07/31 15:49
- マウス・キーボード macからリモートデスクトップでwindowsに接続した際のキーボード配列 3 2023/07/10 08:40
- Excel(エクセル) EXCELでの文字・数字入力の基本について教えてください。 2 2023/05/29 23:17
- iOS これは日本語入力の大発明ですか? 7 2022/12/21 07:17
- Excel(エクセル) 【マクロ】フォルダを3つ、POモニター上に、決まった並べ方をしたい 4 2022/08/31 11:05
- Excel(エクセル) capeofdragonと申します Excel2016を使っておりまして 半角又は全角の任意文字列が 2 2022/10/31 13:51
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBAで保存しないで閉じると空の...
-
エクセル関数>参照ファイル名...
-
excelで直前に参照していたブッ...
-
【Excel】マクロの保存先について
-
マクロの保存先、開いてるすべ...
-
EXcelのマクロで相対パスでファ...
-
【Excel VBA】マクロでExcel自...
-
エクセル;相対パスを絶対パスへ...
-
エクセルVBAで作成した別ブック...
-
EXCELブックが勝手に開いて困っ...
-
エクセルのvbaにて thisworkboo...
-
EXCELのボタンによるマクロの登...
-
[フィルターオプションの設定]...
-
【Excel】特定セルの内容をテキ...
-
複数のExcelファイルの印刷設定...
-
複数のbookから特定のセル(行...
-
ファイル名変更後も、マクロを...
-
EXCELマクロで、開いてはいるが...
-
【マクロ】マクロが保存されて...
-
複数のCSVファイルを自動でエク...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBAで保存しないで閉じると空の...
-
エクセル関数>参照ファイル名...
-
マクロの保存先、開いてるすべ...
-
【Excel VBA】マクロでExcel自...
-
【Excel】マクロの保存先について
-
複数のExcelファイルの印刷設定...
-
エクセル;相対パスを絶対パスへ...
-
excelで直前に参照していたブッ...
-
EXcelのマクロで相対パスでファ...
-
エクセルVBAで作成した別ブック...
-
エクセルで未保存に対するメッ...
-
EXCELブックが勝手に開いて困っ...
-
EXCELのボタンによるマクロの登...
-
エクセルのvbaにて thisworkboo...
-
EXCELマクロで、開いてはいるが...
-
名前の変わるブックをアクティ...
-
[フィルターオプションの設定]...
-
エクセルVBA 個人用マクロブッ...
-
ファイル名変更後も、マクロを...
-
個人用マクロブックが消えました
おすすめ情報