色々なことをメモします。パソコン(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記事でした。


関連記事

 【Excel】エレクトーン ELB-02用レジストメモを作ってみました【Electone STAGEA ELB-02】
2021年03月05日 (金) | 編集 |
エレクトーンの練習をしたいのですが、自宅に無いためできません。。。
たぶん、もう弾けなくなっちゃったなぁ。

そこで、
エレクトーン楽譜 復刻コレクション
https://yamahamusicdata.jp/elcategory/el_reprint
↑こちらに載っているレジストチャートを参考に、Excelで表を作ってみました。
ELB-02用です。

ちょっとレジストをメモするのにいいかなぁと自分で使うために作ったのですが、練習できないため使う機会が今のところありません。
せっかくなので、ここに置いておきます。

ELB-02_RC_img

白のセルが入力欄です。
入力欄をすべてクリアするには、赤い丸の名前ボックスから「入力欄」を選択し、Deleteキーを押してください。
音色を入力するには、青い丸のドロップダウンリストよりカテゴリーを選択し、その下の音色のドロップダウンリストより選択してください。

ELB-02用レジストメモ(Excel 2019)
ダウンロード(ELB-02_RC.zip)
Excel 2013でも大丈夫だと思います。
VBAは使用していません。

amazonで YAMAHA STAGEA ELB-02 の詳細を見る ⇒ YAMAHA エレクトーン STAGEA ELB-02



 【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
興味のある方は見てみてください。

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

関連記事

 【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
興味のある方は見てみてください。

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


関連記事

 【Excel】鬼滅の刃、竈門炭治郎の市松模様を作りました
2020年04月01日 (水) | 編集 |
市松模様

Excelで鬼滅の刃の竈門炭治郎の市松模様を作ってみてと言われたので、考えてみました。
私は「鬼滅の刃」というアニメは見たことがなかったので、よくわからなかったのですが、緑と黒の市松模様ということでした。
一つのセルに条件付き書式を入力し、あとはオートフィルで表示できるようにしました。

まず、行番号と列名の交差する左上のボタンをクリックし、すべてのセルを選択します。
列の幅を「2.5」にして、セルが正方形になるようにします。

条件付き書式には、2つの条件を入力しました。
行番号と列番号の合計が偶数だった場合は背景色を黒、奇数だった場合は緑にしました。

セルA1を選択してから条件付き書式の設定をしました。
1つ目の条件付き書式は、[数式を使用して、書式設定するセルを決定]を選び、次の数式を入力しました。
=ISEVEN(ROW()+COLUMN())
書式は背景色を黒にします。

2つ目の条件付き書式も同様に、[数式を使用して、書式設定するセルを決定]を選び、次の数式を入力しました。
=ISODD(ROW()+COLUMN())
書式は背景色を緑にします。

2つの条件付き書式の適応先が、次のようになっているか確認します。
=$A$1

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

これで、緑と黒の市松模様ができました。

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

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

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


関連記事