フォルダ・ファイルへのアクセスの高速化について

PC操作の時短にAutoHotkeyを使うといいよという記事を以前書きました。

AutoHotkeyの設定

今回はPC操作の時短に効果的なフォルダ・ファイルへのアクセスの高速化について考えてみます。

現在の私のファイル管理の入口は、
・Win+E(エクスプローラ)→クイックアクセス
・Win+R(ファイル名を指定して実行)
主にこの2つです。

Win+Eで表示されるクイックアクセスにデスクトップとDownloadを表示してあるので、デスクトップで行う一時的な作業やブラウザで保存したファイルはここからアクセスしています。

その他のファイルは全てDropboxに入れてあり、Win+R(ファイル名を指定して実行)からDropboxにアクセスしています。

Dropboxの中は基本的にアルファベットのフォルダにしてあります。そうすることで、キーボード操作だけで目的のフォルダ・ファイルを選択できるようになります。

例えば、Dropbox>Application>Driverのようなフォルダ構成の場合、Win+R>D>Enter>A>Enter>D>Enterのような感じでマウスを操作せずに目的のフォルダ・ファイルまでアクセスすることができます。

※※※

ちなみに「ファイル名を指定して実行」からフォルダ・ファイルを起動する方法は、そのショートカットをC:\Windowsの中に入れるだけです。

実際はWindowsフォルダ以外でも大丈夫なのですが、話が難しくなるのでとりあえずWindowsフォルダとします。

具体例として私の場合、G:\Dropboxのショートカットを作成し、dという名前にしてC:\Windowsに入れてあります。

※※※

日本語のフォルダ・ファイルは、この方法が使えないので、お勧めはフォルダ・ファイルの先頭にアルファベットをつける方法です。

例えば、s_仕様書のような感じのフォルダにしておけば、Sキーを押せば自動で選択されます。

今は部署が違うので少しのんびりですが、日々の事務処理を大量にこなしてた頃は、目的のフォルダ・ファイル全てに対して設定していました。

今回のポイントとしては、目的のフォルダ・ファイルにはなるべくキー操作のみでアクセスできるようにする。そのためにWin+EやWin+Rを入口にし、さらにフォルダ・ファイルの先頭にアルファベットを付けるといいですよということです。

 

VBA エクセルからSQL実行(SQLite)

最近では専門家ではなくてもデータ分析ができたほうがいいという話をよく聞きます。そういう話の中で利用されているソフトはだいたい専用ソフトかエクセルといった感じでしょうか。

ただ数は少ないですがSQLを使おうというのもあるようです。実際SQLは非常に便利でエクセルより多少敷居は高いですが、少し使えればエクセルでは面倒な処理も簡単にできたりします。経験上、ワークシート関数、VBA、SQLを使い分ければほとんどの処理ができると思います。

ですが、いざSQLを使おうとするとどのソフトを使ったらいいのか迷うと思います。一般的にはAccess、SQL Server、MySQL(MariaDB)、SQLiteあたりがお勧めされていますが、もしPCにAccessが入っているならAccessが一番手軽に始められるのではないかと思います。今回はSQLiteを採用していますが、私も最初はAccessを使っていました。

中小企業で働いているとデータ分析やITの専門家がいない場合も多いので、自分たちで少しでもデータ分析ができると役に立つことが多いです。

エクセルで簡易原価計算

原価計算といえば製造業では特に重視されていてよく耳にするかと思います。一応、会計的に正しい計算法というのもあるのですが、中小企業ではカスタマイズしたルールを用いてエクセルでざっくり計算していることが多いように思います。

今回はエクセルで簡易原価計算をするためには、どのように入力していけばいいのか少し考えてみたいと思います。

・集計単位で番号を付加する

製造番号やシリアル番号など名称に違いはあっても、恐らくどの会社でも管理用の番号を付加していると思います。受注単位、製造単位、単品ごとなど付加する単位は会社によって違うと思いますが、重要なのは原価を求めたい単位で番号を付加することです。例えば、受注はA製品×2台で受注番号を付加しているが、原価集計したいのはA製品それぞれ1台という場合、受注番号とは別に番号が必要になるということです。

一般的にはこういう場合、枝番で単品ごとの単位にしていることが多いと思います。そこでよくあるのが枝番を無視する工程の存在です。無視している工程では原価集計ができなくなるので注意が必要です。

ここではA製品×2台に対して001というNoを割り振ったとします。

・原価発生時エクセルに入力

まず原価として集計したいものは全てエクセルに入力することとします。会社によっては販管費相当でも個別の原価に入れたりしていると思います。例えば、運送費を個別の原価に入れて集計したい場合、運送費も入力するということです。

・仕入入力について

買掛単位ではなく原価単位の行数で入力します。

例えば、A製品(No001)とB製品に使う材料を購入したら1行の明細で100円となった場合。2行に分けてA用に50円、B用に50円というように入力します。会計では買掛単位として扱うことが多いと思うので、必要であればそれぞれ別管理が必要になるかもしれません。

仕入が1行でも、計算するタイミングで何かしらの基準でA製品とB製品に対して按分して計算するばいいのでは?と思われるかもしれません。もちろん専用ソフトでしっかり管理されていれば可能ですが、そうでないなら、最初から明細を原価単位で入力しておくほうが楽だと思います。

・エクセルの作り方

今回は入力、単価マスタ、集計結果を作っています。実際はそれぞれ別シートにして項目も多くなるかと思います。

ポイントとなる関数はSUMIFS、VLOOKUP、INDIRECTです。関数自体は他のサイトに分かりやすい解説があるのでそちらを見ていただくとして、どのように使っているのかというと、入力から単価マスタの参照はVLOOKUPを使い、集計結果から入力の参照はSUMIFSを使い、それぞれ最終行をINDIRECTで指定しています。

この中でも一番重要なのがSUMIFSです。単価マスタはなくても大丈夫なので、もっと簡単にしたい場合は入力と集計結果だけ作って、SUMIFSで集計しても大丈夫かと思います。

エクセルVBA講座

エクセルにはVBAという開発環境が付属しており、VBAを活用するとエクセル上の作業を自動化したりすることができます。

大手企業では業務ごとに専用ソフトを導入していたり、開発の専門部署があったりしますので、一般の従業員がVBAを使う機会は少ないかもしれませんが、中小企業ではVBAを使うと業務を効率化することができます。

私もそれほど詳しくありませんが、実際に今までいくつかVBAを業務に役立ててきましたので、ここではその経験を分かりやすくお伝えしていきたいと思います。

VBAを始めるにあたって何が大変なのかというと、面白くもない基礎知識を覚えなければいけないということがあります。VBAはプログラミング言語の一種なので、プログラミング未経験の人からすると、最初に覚えなければいけない内容もかなりの量になってしまいます。

やりたいことは明確なのに、すぐにそこに行けないのはもどかしいものです。ですので、ここではなるべく早く使えるようになることを目標にして、細かい部分は省き、必要なことは作りながら覚えればいいという考えで進めたいと思います。

また、VBAなどプログラミングではやりたいことに対して複数の方法が存在するのですが、これもあえて一つの方法を採用してしまおうかと思います。

VBAの実行方法

開発タブ>挿入>ActiveXコントロール>コマンドボタン
追加されたコマンドボタンをダブルクリックします。

VBA用のエディタが表示されたと思います。

表示された
Private Sub CommandButton1_Click()

End Sub

の間に処理を記入していくことになります。

もし開発タブが表示されていない場合は、
ファイル>オプション>リボンのユーザー設定
開発にチェックを付けます。

ではどうやってVBAを実行するのか?ですが、開発タブの中にデザインモードというボタンがあり、これが押されている状態では今のようにVBAのエディタが開きます。

デザインモードのボタンをもう一度押すと、デザインモードが押されていない状態になり、その状態でコマンドボタンを押すとVBAが実行されます。

開発中にわざわざボタンを押さずに、素早く実行したい場合は、
Private Sub CommandButton1_Click()

End Sub

の間にカーソルがある状態で、VBA用エディタの再生ボタンを押すことで実行できます。

セルに値を入れる

Sheets(“Sheet1”).Cells(1, 1).Value = “1”

セルに値を入れるときはこのように書きます。どのシートなのか、どのセルなのかを指定し、そのセルの中身をValueで指定します。

ちなみ、セルを指定しているのだからわざわざValueは必要ないのでは?と思うかもしれません。(実際なくても動くのですが)なぜValueが必要なのかというと、セルの塗りつぶしなどセルの中身とは関係ない部分を指定することもあるので、中身はValueで操作できるよと覚えておいてください。

細かくみていくと、
Sheets(“シート名”)
でシート名を指定し、
Cells(行, 列)
で何行目の何列目にあるセルなのかを指定し、
Value
で指定したセルの値を操作できるという意味です。

ここでのイコールは代入(入れる)という意味です。つまりSheet1の1行目で1列目のセルに”1″を入れるという意味です。

数値と文字

VBAでは数値だったらそのまま直接(1など)書くことができます。文字だったらダブルクォーテーションで囲う(“1″など)必要があります。でも今回1をダブルクォーテーションで囲ってなかった?と思うかもしれません。

少し難しい話ですが、1と”1″は一般的なプログラミングでは数値の1と文字の1として区別されます。いくつか違いはありますが、数値の1は四則演算できて、文字の1は四則演算できないというような違いがあります。つまり上記では数値ではなく文字の1を代入したということです。

VBAの特徴として、文字の数値(“1″など)でも、どうやら数値として使ってるっぽいなとVBAが判断すると自動で数値として扱ってくれます。

このあたりを正確に理解しようとすると型というものが出てきてしまうので、今は数値と文字の違いはあるけど、VBAはいい具合に判断してくれる程度に理解しておけば大丈夫です。

値のコピー

セルの書式や文字色などもコピーできるのですが、最初は値だけコピーする方法を覚えておけば大丈夫です。Valueを使ってイコールで代入するだけです。

値の表示(MsgBox)

次はセルの値を表示してみます。

MsgBox Sheets(“Sheet1”).Cells(1, 1).Value

代入ではないのでイコールはありません。代わりにMsgBoxと書かれています。これで指定した値をダイアログで表示することができます。Sheet1の1行目1列目のセルの値が表示されます。

ちなみに
MsgBox “A”
などのように表示する値を直接指定することも可能です。

MsgBoxはデバッグとしても利用されます。例えばプログラムを作っている最中に変数の中身を確認したい場合など頻繁にMsgBoxは利用されます。

ループ(For文)

セルの値を1つずつ設定したり取得したりするだけでは手作業と大きな違いはありません。VBAのメリットは多くの処理を自動化できることですが、ループは自動化の基本の一つです。

これを実行するとA列の1~10行目に1~10の値が入ります。

For 変数 = 開始値 to 終了値
 処理
Next

というのが基本です。

現時点では変数というのは入れ物と理解しておけば大丈夫です。名前も自由につけることができ、ここではiとしていますが、counterなどとしても大丈夫です。

変数iには1から10まで順番に代入され、そのたびに処理が実行されるということです。具体的にいうと
変数iが1のとき、
Sheets(“Sheet1”).Cells(1, 1).Value = 1
として処理されます。
変数iが2のとき、
Sheets(“Sheet1”).Cells(2, 1).Value = 2
として処理されます。
このように、iが10になるまで繰り返されていきます。

また、変数は1からの必要はありませんし、処理の中で使わなくても問題ありません。

これだとAが2回表示されるだけです。処理でiは使っていません。ループを2回するだけです。

条件

次に説明するDo-WhileとIfで条件というのがでてきます。条件とは2つの変数あるいは直接の値を指定し、その2つの間に下記の式を書いてそれが満たされた場合、True(処理の実行)となり、満たされない場合、False(処理が実行されない)となります。

条件は
A  =  B (AとBが同じ)
A <> B (AとBが違う)
A >= B (AがB以上)
A <= B (AがB以下)
A  >  B (AがBより大きい)
A  <  B (AがBより小さい)
のような種類があります。

実際AとBには変数か値が入ります。
変数 条件 変数
変数 条件  値
 値 条件 変数
 値 条件  値
いずれの組み合わせてでも大丈夫です。

条件はOrとAndを使うことで複数同時に使うこともできます。意味はそのまま、Orであればどちらかの条件を満たす、Andであれば両方の条件を満たすという意味になります。

例えば
c = 5 Or r = 5
であれば、cという変数の中に5が入っている状態か、rという変数の中に5が入っている状態のどちらか一方でTrueとなります。

c = 5 And r = 5
であれば、cという変数の中に5が入っている状態であり、rという変数の中にも5が入っている状態ではじめてTrueとなります。

ループ(Do-While文)

Do While 条件
 処理
Loop
が基本です。

条件が満たされている間処理をします。

これで1から3まで表示されます。

同じループでForと何が違うの?と思うかもしれません。実際、同じことをしようと思えばどちらを使ってもできます。

使い分けとして、データ量が固定の範囲をループさせる場合はForを使い、データがどんどん追加されていくようなタイプの範囲をループさせる場合はDo-Whileを使うという感じです。

条件分岐(If文)

ある条件が満たされる場合のみ処理を実行することができます。

If 条件 Then
 処理
End If
が基本となります。

このように書くことができます。

既に少し説明しましたが、VBAでは文字と数値をうまい具合に扱ってくれます。一般的なプログラミングでは数値と文字は比較できないので、例えば”5″と5は比較できません。この辺りの特徴は少し意識しておいたほうがいいかもしれません。

If文ではだいたい同時ElseIfやElseも説明されていることが多いですが、とりあえず今の段階では使わなくても大丈夫です。

実用例

ここまでの説明で少し実用的なものを作ってみます。Sheet1のデータに条件を1つ通してSheet2にコピーしてみます。

Sheet1には2列データが入っていて、1列目には名称、2列目には数量を入れてあります。1列目が空白になるまでループし、2列目が0の場合はコピーしないという処理をおこないます。

非常に簡単な例ですが、手作業では困難な程度の量になってくると自動化が役に立つと思います。

VBA 重複削除

重複を取り除く方法はいくつかあると思うけど、ネット上で一番有名なdictionaryを使う方法を採用。

VBA 関数の参照先アドレス(行)の増減

大量の関数を複写や移動するとき、参照先アドレスの数値(行)だけ一括で増減したいときがある。

例えばデータ用のシートに対して印刷用のシートからの参照していて、行数の関係が対応していないためそのまま複写できない場合など。

今回は式の中にアドレスが1つだけの場合を想定している。

VBA フォルダ作成

以前フォルダの構成をエクセルに書き出す機能を作ってみた。

VBA フォルダ・ファイルのリンクの一覧

逆にエクセルからフォルダを作成できると便利という場面も多いので作ってみた。

EmEditorを使う理由+外部ツールメモ

テキストエディタは有料のソフトを使わなくても、無料で高機能なソフトが多数存在しているのに、どうしてわざわざEmEditorを使っているのか少し説明したいと思います。

・指定パスをルートにしたエクスプローラの表示
・指定記号によるアウトラインの表示
・インクリメンタルサーチ(正規表現)の強調表示

この3つがEmEditorを使い続ける理由です。今まで色々試してみてどれか一つなら対応しているソフトもあったように思いますが、3つとも対応しているソフトはありませんでした。

左側にはメモ用のテキストファイルが入ったフォルダをルートにしてエクスプローラを表示させてあります。そうすることで頻繁に使うテキストファイルを即座に開くことができます。

右側には「●」を1段目「・・」を2段目として設定したアウトラインを表示させてあります。自分で管理しやすい記号を設定することができます。

3つの中でも絶対に外せないのが検索の機能です。

検索ボックスの検索文字列(正規表現)が変化すると、都度グリーンの強調表示(検索にマッチした部分)が変化していきます。強調表示はそのまま置換することも削除することもできます。

検索を実行したら検索文字列(正規表現)にマッチした部分が強調表示されることは珍しくないのですが、EmEditorはインクリメンタルサーチで強調されます。

私がデータの加工や分析をしなければならないとき(中小企業でのことなので数千や多くても数万程度のデータですが)、エクセルシートの機能、VBA、SQLなどと一緒にEmEditorの正規表現の置換も使っており、無くてはならないソフトです。

外部ツールの登録

これは自分用のメモです

ツール>外部ツール>外部ツールの設定>新規作成にて
タイトル:cmd
コマンド:C:\Windows\System32\cmd.exe
初期ディレクトリ:$(Dir)
アイコンパス:C:\Windows\System32\cmd.exe

このような感じで登録できる。

 

VBA 表から行

特定の範囲のデータをリスト化する場合、定番なのは行と列のループをネストさせる方法。

行の変数(r)と列(c)の変数をCellsで利用すればOK。見出し等は行や列の数値を固定にする。

PHP+JavaScript(Vue.js) メモプログラム

何かメモを取る場合、テキストファイルにパッと書くことが多いので、ブラウザに書けたらどうだろうと思い作成してみた。

普段からブラウザは開いているし、レンサバに設置すれば外でも見れるので、テキストファイルより便利になりそうと思っていたが、しばらく使ってみて、やっぱりテキストファイルに戻ってしまった。