ワークシートの最大行数、最大列数を取得
最大行数は Rows.Count
最大列数は Columns.Count
で取得できます。
' 使用例(1)：「Sheet1」シートの全セルのデータや書式設定を削除 With ThisWorkbook.Sheets("Sheet1") Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).Clear End With ' 使用例(2)：可変値が範囲外にならないようなエラーチェック Dim lRow As Long Dim lCol As Long lRow = ～～ lCol = ～～ If (0 < lRow And lRow <= Rows.Count) And (0 < lCol And lCol <= Columns.Count) Then ThisWorkbook.Sheets("Sheet1").Cells(lRow, lCol).Value = "hoge" Else MsgBox ("範囲外やで") End If
ボタンひとつで簡易grep
何度もgrepをかけるとき、いちいちウインドウ出てくることや
検索フォルダが「今開いているファイルの位置」になるのが非常に鬱陶しいので、
「あらかじめ指定されたフォルダ以下にて今選択している文字列をgrep」するマクロを作りました。
ショートカットキー割り当てておくと非常に楽です。
私は折り返し位置を81文字に設定しているので、
grep後に調整しています（コメントアウトしている部分）。
//grepフォルダ $curdir = "C:\\hoge\\hage\\"; //grep対象ファイル(拡張子とか) $filter = "*.c;*.cpp;*.h"; //選択範囲の文字列を取得 $word = gettext(seltopx,seltopy,selendx,selendy); //grep grep $word,$filter,$curdir,icon,subdir; ////折り返し位置変更 //config "w2000";
…という記事を旧ブログから移行してきたのですが、今読むと「ねぇよそんな作業」って感じです…。
かつては何故かすんげえ頻度ですんげえ大量にgrepする現場にいたので…。
VBAでセルの結合
セルを結合するには、MeageCellsプロパティを用います。
' A1～C3のセルを結合 Range("A1:C3").MergeCells = True '現在選択している範囲を結合 Selection.MergeCells = True
ここで、結合したいセルのうち2つ以上にデータが入っていると、警告が表示されます。
これを無視して(表示させずに)結合したい場合は、
あらかじめ「Application.DisplayAlerts」をFalseに設定しておけばOKです。
Application.DisplayAlerts = False Selection.MergeCells = True Application.DisplayAlerts = True
また、逆にセルの結合を解除したい場合は、もちろんMergeCellsプロパティをFalseに設定すればOKです。
頭文字検索
※MySQLを前提として書いています
用語集みたいなやつを作っていて、「あ行」「か行」…「英数字」という頭文字検索があったので、
調べてみたところLIKEかREGEXPが使えそうかなと思ったのですが…
LIKE
→複雑な正規表現は使用不可
→日本語に対応
でした。
日本語対応版REGEXPであるMREGEXPというものもあるのですが、
会社の環境なのでインストールはちょっと…。
参考
mregexp - MySQLで日本語の正規表現を扱う
LIKEを使うとしたら
-- あ行 SELECT * FROM `table` WHERE `yomi` LIKE 'あ%' OR `yomi` LIKE 'い%' OR `yomi` LIKE 'う%' OR `yomi` LIKE 'え%' OR `yomi` LIKE 'お%' -- 英数字 SELECT * FROM `table` WHERE `yomi` LIKE '0%' OR `yomi` LIKE '1%' -- (中略) OR `yomi` LIKE 'a%' OR `yomi` LIKE 'b%' -- (中略) OR `yomi` LIKE 'z%'
…これはひどい。
REGEXPを使うなら
-- あ行 SELECT * FROM `table` WHERE `hoge` REGEXP '^(あ|い|う|え|お)' -- 英数字 SELECT * FROM `table` WHERE `hoge` REGEXP '^([0-9]|[a-z])'
という形になるはず。
ダメもとで試してみたところ、
例えば「あ行」の検索だと、確かに「あ～おのいずれかで始まる単語」はすべてヒットするのですが、
他の一部の単語もヒットしてしまいました。ほんとにダメかー。
ということで、日本語頭文字はLIKE検索・英数字はREGEXP検索、とすることにしました。
MREGEXPが使用可能ならこちらを使ってもよいと思いますが、LIKEの方が速度は速いようです。
データ量や正規表現の有無を考えて、MREGEXPにしろREGEXPにしろ、ご利用は計画的に。
あとは、根本的な対策として、インデックス用フィールドみたいなのを作っても良いかもしれませんね。
「あ行」「か行」みたいなのを読みから判断するのではなく、それ自体を持たせる形で。
フィールドの順序を変更する
※MySQLを前提として書いています
あるテーブルで、フィールドが「field_a」「field_b」がこの順に存在し、これを入れ替えたいときは以下のようにします。
ALTER TABLE テーブル名 MODIFY COLUMN `field_a` 型名 AFTER `field_b`;
テーブルの再構築
※MySQLを前提として書いています
いつもは速いクエリが突然やたらと遅くなったので調べてみると、何故か適切なインデックスが使用されなくなっていた模様。
とりあえずインデックスを一旦削除して張り直すと戻りました。
（FORCE INDEX なんかも知ってはいますが、リリース済みのコードを直すのは(社内的に)面倒なので）
アホみたいにレコード数の多いテーブルなので定期メンテナンスできないか調べたところ、どうも空の ALTER TABLE を発行すると良さそう。
ALTER TABLE `テーブル名` ENGINE エンジン;
とりあえず開発環境の同一構成テーブル（InnoDB、インデックスは11個）で試してみたところ、
約4万件に対して約2.8秒。
ちなみに本番環境は約500万件なので、同一構成のテーブルをもう一つ作成し
テストレコードをそのくらい入れて試してみたところ、15分。
結構きますね。
さらに全く別のテーブルでも試してみました。
約56万件、MyISAM、フルテキストインデックスあり。
1レコードあたりのサイズが大きい上に、フルテキストインデックスということでやはりかなりの時間がかかるようで
2時間近くかかりました。
…ということで定期メンテナンスが導入されたのですが、
空ALTERを発行することで逆に適切なインデックスが使われなくなってしまう箇所があり、
そこだけ一旦削除→再作成というステップを踏む羽目になっているのでした。なんでや。
ランダム文字列を生成する関数
以前に同タイトルの記事を書きましたがこちらはPHP版。
↓こちらはVBA版。まあこちらも基本ロジックは一緒なんですが…
private function makeRandomStr($strlen = 1) { // 使用可能な文字一覧 $randomstr = "abcdefghijklmnopqrstuvwxyz1234567890"; // 指定された文字数分繰り返してランダム文字列を作る $str = ""; for ($i = 0; $i < $strlen; $i++) { $str .= $randomstr[rand(0, strlen($randomstr) - 1)]; } return $str; }
複雑な条件でGROUP BYする
※MySQLを前提として書いています
下記Aのテーブルについて、
「idが4未満と4以上のグループに分けて、それぞれで一番小さいvalueをSELECT」
してBの結果が欲しいとき…
(A)test_table
| id | value |
| 1 | 68 |
| 2 | 15 |
| 3 | 32 |
| 4 | 19 |
| 5 | 73 |
| 6 | 59 |
(B)
| 15 |
| 19 |
苦し紛れに以下のようなクエリを書いてみたら成功しました。
SELECT MIN(`value`) AS `min_value` FROM `test_table` GROUP BY `id` < 4 ,`id` >= 4
GROUP句には条件式も使えるんですねー。
（ORDER句でも使えるのを知ってて思いついただけなので、それ実はダメだよ！みたいなの知ってる方いらっしゃいましたらこっそり教えてください…）
もちろん複数フィールドを使った条件式や、
等号不等号に限らず、IN等も使えます。
ちなみにGROUP句部分を下記のようにした場合、
GROUP BY `id` = 1 ,`id` IN (2,3)
結果はこうなります。
| 68 |
| 15 |
| 19 |
条件から漏れたレコードについては勝手にまとめられるようですね。
使い方に応じて、不要なものはWHERE句できちんと省くとか、もしくはGROUP句での指定で漏れないようにするとか、きちんと考えて書かないとヘンな不具合の原因になりそうです。
Excelでよく使うショートカットキー
特定の文字以降を別の文字列に置き換える
具体的には、「メールアドレスの@以降を*でマスクする」ロジック。
文字列処理の関数を多数使用できて面白かったのでメモ。
ただ、PHPならもっと簡単なロジックが作れそうな気も…？
// 加工前のメールアドレス
$mail_address = "hoge@fuga.com";
// 加工後のメールアドレス
// （表示すると「hoge@*********」になる）
$masked_mail_address = substr($mail_address, 0, strpos($mail_address, '@') + 1) . str_repeat('*', strlen(strstr($mail_address, '@')));
使用している関数（リンク先は公式マニュアル）
- strpos：文字列の検索
- strlen：文字数の長さを求める
- strstr：文字列の切り出し
- substr：文字列の切り出し
- str_repeat：文字列を繰り返す
