
2021年08月25日 (水) | 編集 |

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記事でした。

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月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月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
興味のある方は見てみてください。
最後までお読みいただき、ありがとうございました。
| ホーム |