EXCELの小技その3

テキストファイルを下記のマクロが組まれたExcelブックで開き、マクロを実行して日付を書き換える。

Attribute VB_Name = "Module5"

Sub Macro3()

Attribute Macro3.VB_ProcData.VB_Invoke_Func = "h 14"

'

' Macro3 Macro

'

' Keyboard Shortcut: Ctrl+h

'

Range("A3").Select

ActiveCell.FormulaR1C1 = ""

Range("A3").Select

Selection.NumberFormatLocal = "G/標準"

Range("A3").Select

ActiveCell.FormulaR1C1 = "令和6年7月12日"

ActiveWorkbook.Save

ActiveWindow.Close

End Sub

１行目は案件名

２行目は空行

３行目は年月日

４行目は空行

５行目は作業内容

となっている。

３行目にユーザー書式設定で前の日付を定型とし、日付が残ったままでマクロを実行したら年号と月日が入れ替わって順番が逆になるというバグが見つかった。

どういうことかというと、マクロを実行する前にユーザー書式設定で令和6年7月11日と書かれてある所にテキストファイルをドロップしてマクロを実行したら令和6年7月12日ではなくて、7月12日令和6年となってしまったのだ。

ユーザー書式設定を解除&消去してG/標準という書式に戻し、書かれていた令和6年7月11日を消してからマクロを実行すると、そのバグは解消された。

また、GUIでフォルダごと読み込んで一括変換する方法は、何度やっても文字化けが発生してバグが解消されなかったので、エクセルブックに書き換えるテキストファイルをドロップしてショートカットキーを押すと、書き換え&上書き保存&ファイルクローズという３つのタスクを同時にこなすようにした。

合計413のファイルを一気にExcelマクロファイルにドロップするとフリーズするので80ファイルずつドロップしてマクロを実行した。

ちなみに一連の操作を５分割で行うと所要時間は3分であった。

80ファイルを選び、ドロップ。→Ctrl+hを80連打→書き換わっているかどうか確認(メモ帳を開いて413ファイルを一気にドロップして開き、確認しながらクローズしていく)の流れで15分で413ファイルの書き換えを終わらせることができた。

その他、習得した技はCtrl+wでWindowをクローズ、一つ目のファイルをシングルクリック→2つ目以降のファイルをCtrlを押しながらシングルクリックすることにより、複数の任意のファイルを選択してコピペ等の操作を同時に行えることを覚えた。

EXCELの小技2

選択範囲内の丸ごと一列または一行の合計を出すには、

=SUM(セルの始点:セルの終点)

であった。

しかしこれでは選択範囲内に余計なセルがある場合、一緒に合計されてしまう。

特定のセルを除外して合計を求めたいセルだけを選ぶにはどうしたらよいか？

=SUM(セル1,セル2,・・・,セルN)

のようにコロンじゃなくて、カンマに変更すればOK

ランダムに千鳥足みたいに選んでも、選んだセルだけ合計してくれる。

つまり、

=SUM(D11,K9,A57,Z1073)

みたいに順番も目茶苦茶にしても選んだセルだけで合計してくれる。

これを、

=SUM(D11:K9:A57:Z1073)

と入力してしまったら、

=SUM(D9:Z1073)ということになり、しかも間の重複したセルのデータも一緒に足されてしまうかもしれない。

EXCELの小技その１

先々週から大幅な人事異動が行われ、現場に一級建築施工管理技士の爺が入社してきて無資格の俺は他支店の事務に飛ばされた。

そこでは現場仕事に全く関わらなくなって、完全に内勤事務となり、給料計算とか勤怠管理とか見積書作成などの内容に変わってしまった。

EXCELやWORDを使った事務作業となるので、仕事でEXCELのテンプレート入力しかやったことのなかった俺はゼロからに等しいスタートとなってしまった。

恐らく次回の契約更新では給料が大幅に下げられることになるだろう。

一ヶ月後に入った後輩は、現場の人間関係が嫌で入社１８日目で辞職を申し出たが引き留めに遭い、他支店に異動となっていた。

つまり俺は奴の後輩になってしまったのだ。

入社時は現場経験がないから全く使えないと思っていたのだが、仕事をメモすら取らずに一発で覚え、１週間で割り当てられた仕事を素早くこなして後輩になった俺に教えて引き継げるようになっていて腰を抜かしそうになった。

セルの書式で、金額の数字を入れ、後ろに"円"を付けて合計を出そうとすると、自分の現時点での知識やスキルではできなかった。

どうすれば良いのかググったら、

セルの書式設定→ユーザー定義の所で#"円"と設定すれば良いことが分かった。(別の本では0"円"と書かれていた)

その他、研修がてらお局様がいろいろ実務を振ってきた。

中でも目からウロコな裏技は、

他のシートのセルの内容を引用してきて、引用元のセルの内容が変更されても引用先のセルの内容に反映される技だった。

=シートの名前!セルの位置

例)

=解体費!D7

と引用先のセルに入力すると、引用元のシート名が解体費で、このシートのDカラムの7レコードのセルの内容が反映される。

その他マクロの記録機能を使ってVisual

Basicのソースコードがどうなるかとかいろいろ研究したり、有意義な研修となった。

まさに今独学しているSQLにも密接に絡んでくる内容だし、VBで便利なツールを作ったりとかモチベーションの維持管理に役立つであろう。