色々なことをメモします。パソコン(Excel)とか、音楽(ピアノ、エアロフォン、エレクトーン)とか。
　【Excel】文字列の中の数字（文字列）を取り出したい
2021年08月25日 (水) | 編集 |
GetNum関数

Excelで文字列の中の数字を取り出したいという場面がありました。
例えば、「at215-00008g785」という文字列があったとします。
そこから、「215」「00008」「785」という数字を取り出したいという感じです。

どうしたらいいかなぁと思い、ユーザー定義関数でやってみることにしました。
VBAの標準モジュールに下記のように入力しました。
Function GetNum(txt As String, Optional n As Long = 1) As String
    Dim reg As Variant
    Dim regmth As Variant
    Dim num As String
    
        Set reg = CreateObject("VBScript.RegExp")

        With reg
            .Pattern = "\d+"
            .Global = True
            
            Set regmth = .Execute(txt)
            
            num = regmth(n - 1)
        End With
        
        Set reg = Nothing
        Set regmth = Nothing
        
        GetNum = num
End Function
GetNumという関数にしました。
第1引数は、数字が含まれている文字列を指定します。
第2引数は、何個目の数字を取り出すかを指定します。
省略すると、1つ目になります。

B列に数式を入力します。
B1=GetNum(A1)
B2=GetNum(A2,2)
B3=GetNum(A3,3)
B4=GetNum(A4,4)
とりあえずは思ったようには出来たのでよかったのですが、これがベストな答えかどうかはわかりません(^_^;)

久々のExcel記事でした。






2021/08/25 14:41 | Excel | Comment (0) | Top▲
　【Excel VBA】楽器練習記録帳を作りました
2020年05月25日 (月) | 編集 |
楽器練習記録帳

私は楽器を弾くのが趣味で、練習を続けています。
腕前は全然ダメダメなのですが、とても楽しいです。
その練習の記録を残すために、Excelで楽器練習記録帳というものを作りました。
音声・動画をコメント付きでメモしていく感じのものです。
音声・動画は、シートに配置したWindowsMediaPlayerで再生します。
これを作ったのは、だいぶ前の話なのですが、ちょっと機能の追加や変更がしたくなり、手を加えることにしました。

表には、日付、曲名、アーティスト名、コメント、ファイルパスという項目があります。
ファイルパスは、セルをダブルクリックするとファイルを選択するダイアログが開くので、ファイルを選択します。
ファイルを選択すると、ファイルのフルパスが入力されます。

オートフィルタをかけて、日付や曲名、アーティスト名、ファイル形式などで曲を絞り込み、再生することができます。
[連続再生]は、アクティブにした行から順番に連続再生されます。
[1曲再生]は、アクティブにした行の曲が再生されます。
再生中は再生されている曲の行に色が付きます。
[早送り]のチェックボックスをチェックすると、早送り再生されます。
あらかじめWindowsMediaPlayerの方で視覚エフェクトが設定されていると、音声ファイルを再生したときにエフェクトが表示されます。

やっと完成した！と思い、もう一台の他のPCで動かしてみたら、、、
WindowsMediaPlayerのサイズが指定してあるにもかかわらず、動画再生時にサイズが大きくなってしまいました。
ファイル形式がwmvだからかなと思い、mp4に変換してみたのですが、関係ありませんでした。
自分なりに、色々とやってみたのですが、なかなか上手くいきません。
片方のPCでは上手くいくのに、もう片方がダメだぁ・・・の繰り返し。
両方のPCで同じ動きにしたいのです。
もう、ギブアップだ・・・と諦めかけていた時、やっとなんとかできたのです。

私のPCの環境
Windows 10 Pro 64bit(1909)
WindowsMediaPlayer(12.0)
Excel(2019)

もう一台のPCの環境
Windows 10 Home 64bit(1909)
WindowsMediaPlayer(12.0)
Excel(2013)

2台のPCの一番の違いはExcelのバージョンだと思うのですが、そのせいかどうかは私にはわかりません。

変更したところをメモします。

WindowsMediaPlayerのオブジェクト名は「WMP1」です。
シートの名前は「記録」です。
WMP1のサイズは縦164.25、横240です。

WMP1のプロパティ設定は以下の通りです。
AutoLoad: False
enableContextMenu: False
Enabled: True
fullScreen: False
Height: 164.25
Left: 0
Locked: True
Placement: 2
PrintObject: True
Shadow: False
stretchToFit: True
Top: 0
uiMode: none
URL:
visible: True
Width: 240
windowlessVideo: False

WMP1のPlayStateChangeイベントに以下のコードを追加しました。
With Worksheets("記録").WMP1
    .Height = 164.25
    .Width = 240
    DoEvents
End With

再生ボタンのURL指定、再生の部分を以下のようなコードに変更しました。
With Worksheets("記録").WMP1
    .Visible = False
    .URL = 音声・動画ファイルのフルパス
    .Visible = True
    t = [Now() + "00:00:00.5"]
    Do
        DoEvents
    Loop Until Now() > t
    .Controls.Play
End With

参考にならないかもしれませんが、こんな感じでなんとかできあがりました。
自分の希望通りには、一応なりました。
これでしばらく使ってみます。

楽器練習記録帳のデータを見てみると、Merry Christmas Mr. Lawrence(アヴェクピアノ)の練習を始めたのが、2015年4月。
5年も練習しているのに、まだ満足のいく演奏ができないのです。
この曲は絶対に弾きたいので、これからも練習を続けていきたいと思います(^^♪





2020/05/25 14:57 | Excel | Comment (0) | Top▲
　【Excel】半角文字が含まれているセルに色を付けたい
2020年05月15日 (金) | 編集 |
Hankaku

セルの値に半角の文字が含まれている場合、わかりやすくするために背景に色をつけたいなぁと思いました。
これは条件付き書式だよなぁ、、、と思い考えてみました。

文字列を1文字ずつ取り出し、１バイトかどうか確認します。
そして、その数をカウントし、合計してみました。
その合計が0より大きければ、色を付けるみたいな感じにしました。

A1:A10を選択して、条件付き書式の[数式を使用して、書式設定するセルを決定]を選び、数式を次のように入力しました。
=SUMPRODUCT((LENB(MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1))=1)*1)>0
書式は背景色をオレンジ色にしました。

条件付き書式の適応先が、次のようになっているかを確認します。
=$A$1:$A$10
なんとかできたようなので、メモしました。

この動画をYoutubeにアップしました。

https://www.youtube.com/watch?v=daxVkdEW49Y
興味のある方は見てみてください。

最後までお読みいただき、ありがとうございました。

・・・と記事を書き終わったところで、もっと別な方法を思いつきました。

文字列のバイト数(LENB)を出して、文字数(LEN)×2の数と比較すればよかったのですね(^^;)
とりあえず、試してみます。






2020/05/15 17:29 | Excel | Comment (0) | Top▲
　【Excel】ハイパーリンクで指定しているURLを取り出したい
2020年04月25日 (土) | 編集 |
ハイパーリンク

昔作ったExcelファイルを使おうと思い、見てみたらハイパーリンクが100個ほどありました。
そのハイパーリンクからURLを取り出したいのだけどどうしよう、、、
ハイパーリンクはハイパーリンクの挿入から入力したものではなく、HYPERLINK関数でURLを指定して入力したものです。

Excel 2013からの新しい関数で「FORMULATEXT」という関数があるのを知りました。
これはセルの数式を文字列として返してくれます。
これを使って、HYPERLINK関数からURLを取り出すことにしました。

A2:A6に次のようなHYPERLINKの数式が入っています。
A2=HYPERLINK("https://www.google.co.jp/","google")
A3=HYPERLINK("https://www.yahoo.co.jp/","yahoo")
A4=HYPERLINK("https://www.bing.com/","Bing")
A5=HYPERLINK("https://www.amazon.co.jp/","Amazon")
A6=HYPERLINK("https://auctions.yahoo.co.jp/","ヤフオク！")
B2にURLを取り出すための数式を入力します。
B2=MID(FORMULATEXT(A2),13,FIND(",",FORMULATEXT(A2))-14)
B2を選択して、右下のフィルハンドルをダブルクリックします。
これでURLを取り出すことができました^^

ハイパーリンクの挿入から入力したハイパーリンクは、この方法では取り出せないので、VBAを使うといいみたいです。

標準モジュールに次のようなコードを入力してみました。
ユーザー定義関数です。
Function GetURL(r As Range)
    GetURL = r.Hyperlinks(1).Address
End Function
D2:D6にハイパーリンクの挿入から入力したハイパーリンクが入っています。
E2にURLを取り出すための数式を入力します。
E2=GetURL(D2)
E2を選択して、右下のフィルハンドルをダブルクリックします。
URLを取り出すことができました。

やったことを忘れないようにメモしておきます^^

この動画をYoutubeにアップしました。

https://www.youtube.com/watch?v=AB4oqlN48dA
興味のある方は見てみてください。

最後までお読みいただき、ありがとうございました。





2020/04/25 14:33 | Excel | Comment (0) | Top▲
　【Excel】あみだくじを作ってみました
2020年04月11日 (土) | 編集 |
あみだくじ

あみだくじを作るにはどうしたらよいかを考えてみました。
できればオートフィルを使って表示できるようにしたいのです。

まず、RANDBETWEEN関数を使って、1と2をランダムに表示してみました。
そして、条件付き書式でセルの値が「1」で、左隣のセルの値が「2」の場合に下の罫線を引くという感じで考えました。

条件付き書式に2つの条件を入力しました。
B3:F23を選択し、条件付き書式の[数式を使用して、書式設定するセルを決定]を選び、数式を次のように入力しました。

1つ目の条件は
=AND(B3=1,A3=2,ROW()<23)
書式は下の罫線を表示にしました。

2つ目の条件は
=B3>0
書式は右の罫線を表示し、文字色も白にしました。

条件付き書式の適応先が、次のようになっているかを確認します。
=$B$3:$F$23

次に、B3に数式を入力します。
B3=RANDBETWEEN(1,2)

セルB3を選択し、セル右下のフィルハンドルを右に(F3まで)ドラッグしてコピーします。
そのままB3:F3を選択している状態で、セルF3の右下のフィルハンドルを23行目までドラッグしてコピーします。

あとは目盛線を非表示にします。
Excelのメニューの「表示」にある目盛線のチェックを外します。

F9（再計算）をすると横線がランダムに表示されます。
1つの列に、横線が1つしか表示されないことがたまにあるので、その場合はまたF9（再計算）をしてください^^;

この動画をYoutubeにアップしました。

https://www.youtube.com/watch?v=qRDv04_9ze0
興味のある方は見てみてください。

最後までお読みいただき、ありがとうございました。






2020/04/11 22:10 | Excel | Comment (0) | Top▲
| ホーム | 次のページ≫
copyright © テクノでんでん all rights reserved.
powered by FC2ブログ.　template & material by tora☆7kg