VBAを学習する

管理人 (2018年1月 5日 09:06) | コメント(0) | トラックバック(0)
操作を自動記録させてマクロを作ることは当然なのだが、それ以外にも有用なサイトを見て、マクロやコード記述を取り込み、合理化を図ることは多い。それは先輩諸氏からの遺産であり、ボクはそれを利用して、合理化を図っている。

そうこうするうちに、やっぱり、ExcelマクロのキホンのVBAくらいはわかるようになりたいなぁと改めて思った。入社当時、日立のOffice-Polというアプリケーションがあって、このソフトウェア（業務アプリケーション）でいろいろと合理化を図った。その当時はそのマクロ記録機能（Office-Pol）では「カタログ」と表現していたと思うが、これを直接、編集し書いていた。

数年後、Lotus1-2-3を個人で購入し、（98,000円だった記憶がある）IDも200番台だった。個人でここまで購入するヒトは少なかっただろう。確か、ボクの前のIDは花王さんだったハズだ。そこで、やはりマクロ機能を勉強するが、余計な記述が多いのは、いまのExcelマクロと同じだ。そこで、停止してしまい、以降はOffice-Polで過ごす。

数年後、勤務先にExcelが導入され、Office-Polからの移管（置換）が始まった。「六車」というコンバートソフトを利用するが、文字・罫線がメチャメチャだ。

つづく

Excel関数一覧

管理人 (2018年1月 5日 08:55) | コメント(0) | トラックバック(0)
できるネットで関数一覧が掲示されていたので、ここにも掲示

＜参考サイト＞

よく使う関数は覚えているが、こんなことができないかなぁ、と思うことが多いのが、ボクの場合は「情報関数」や「論理関数」で、迷うことが多いのもこの関数だ。便利なのは、「文字列関数」。規則性を見つければ、文字列から操作することにより、ピンポイントでデータを抽出できる場合もある。

理系ではないので、「三角関数」を使うことはほぼ、ない。「統計関数」も使う範囲は限られている。だが、見ていると、面白い。

（新）GoogleSpreadSheetを使う

管理人 (2017年12月28日 09:00) | コメント(0) | トラックバック(0)
googsps-718x404.jpg
最近、GoogleSpreadSheet(以降：GPSと略します)を使って作業をする機会が増えた。Excelに似ているのだが、ちょっと挙動が違うことがある。便利な部分も多いので、これもまた、「記憶の嵐」に参考サイトと共に記録していきたい。

ボクが現在、GPSを利用する機会は、GoogleHomeMiniを購入した後の、IFTTTで作成した、「出金記録帳」と「記録簿」の操作だ。IFTTTを利用して、音声メモを文字にして、GPSに記録させている。ちょっと買い物した時や、後でやらなければならない事などメモしている。

Vlookup で参照先を固定化（構造化）

管理人 (2017年12月27日 14:41) | コメント(0) | トラックバック(0)

Vlookupの参照先はSheet１!A1：E520などとなることが一般的だが、この参照先を固定化してしまおうと言うのが、「構造化」ということらしい。


この「構造化」を使うと、この参照先の入力が非常に多い場合、ラクになるのだ。つまり、参照先のSheet１!A1:E520などを名前を付けてやることで、例えば「給与」などとしたシートとそのシート内の範囲を固定化することがラクになる。


先の例に倣うと、

IF(ISNA(Vlookup(A1,Sheet1!A1:E250,3,FALSE))," " , Vlookup(A1,Sheet1!A1:E250,3,FALSE))

は

IF(ISNA(Vlookup(A1,給与,3,FALSE))," " , Vlookup(A1,給与,3,FALSE))

と簡略化ができるということで、入力ミスもなくなる。また、参照範囲が増えても、ちょっと変えてやるだけで、式を入れたマトリクスが全件変わるというところが「効率化」できるのだ。


＜参照先＞

【エクセル時短】VLOOKUP関数には「構造化参照」！ テーブルを活用してデータの増減に自動対応する

Vlookup で #N/A が出る

管理人 (2017年12月27日 10:58) | コメント(0) | トラックバック(0)

この問題は以前、ここに掲載した心算りだったが、掲載していなかったようだ。


Vlookup関数を使うと、データベースを参照しながら、数値を持ってこれるので、とても便利なのだが、参照先に該当のデータがなかった場合、#N/A　が表示されてしまい、見栄えのよくない表ができてしまうことが多々、ある。そこで、この　#N/A を表示させないためのテクニックだ。


IF(ISNA(Vlookup(A1,Sheet1!A1:E250,3,FALSE))," " , Vlookup(A1,Sheet1!A1:E250,3,FALSE))


といった構文になる。IF文で、「該当データがなければ　"　"　(ブランク)で表示してね、という意味の文章で、ISNA文で ＃N/A かどうかを調べている、という事だ。探している参照元データ（A1に相当する部分）が、参照先データベース（Sheet1！A1:E250,3,）で該当するものがない場合、#N/A が還される。まぁ、計算なので、正直に出てきてしまうのだが、その表示改変といったところだ。

✵＃N/Aが出ることによって、DBに該当するものが「ない」ということも大切なのだが。


＜参考サイト＞

【エクセル中級者向け】数式・関数のエラー表示の回避テクニック③④【IF関数＋ISERR関数・ISNA関数】


Excel　書式を変更したのにカンマを打たない？！

管理人 (2017年12月14日 10:41) | コメント(0) | トラックバック(0)

久々に更新である。最近、勤務先の業務で給与計算業務を担当するようになった。あれこれとExcelを駆使することが多いが、それほどの高度のテクニックがなくても、意外と高度の処理ができてしまう。だが、甘んじることなく、合理化を勧めて・進めていきたいものだ。少人数のエキスパート集団だ。

csvで作成されたデータを読み込んだ後、Excelにして、金額部分をカンマで表示するようにボタンをクリックしたのに、カンマ表示されない。でも、そのセルの中に入って、文字をクリックすると、カンマが付く。これを、社員数✕項目数分、セル単位でクリックしていたら、日が暮れてしまう・・・

書式を #,##0 に変更したのに、カンマを打たない。ただ、そのセル内にカーソルを移し、クリックすると、#,##0 に変わる。
1234567890 　→   　 1,234,567,890

そこで、googleで検索したところ、有用なサイトを発見した。メモ。

【参考サイト】

ボクは一部の表記を変えて、作成した。「書式再設定」である。

Sub 書式再設定()
    For Each e In Selection
        e.Value = e.Value
    Next
End Sub


Excel　透過型画像を作る　

管理人 (2017年7月24日 14:58) | コメント(0) | トラックバック(0)

今回はExcelのちょっと変わった使い方である。ワタシ自身もExcelの表上に「透過型画像」を写し込んだ表を作る時があるが、それほど回数はなく、年に数回なので、間があくと、わからなくなってしまい、また、記憶にも留めておけない場合が多い。最近、無償ソフトを発見したのだが、そのソフトがDLできないときなど、参考になるだろう。今回は下記のサイトを参考にしたが、以前に比較すると、他にもわかりやすいサイトが増えたよう思う。

＜参考サイト＞

＜参考フリーソフト＞
但し書きにあるのだが、透過型gifは作れない




IFの中でORを使いたい

管理人 (2017年6月 7日 11:24) | コメント(0) | トラックバック(0)

意外とメンドウなのが、こういった、AND（かつ）とかOR（または）をIF（もしも）と組合させて使いたいときである。ええーっと。とIF関数の使い方には慣れているのだが、ANDやORが入るとわからなくなってしまうケースがあった。

基本的なIF文は

=IF( A1="税理士等の報酬",B2,"")
もし、A1のセルに「税理士等の報酬」と書かれていれば、このセルにはB2の数字を入れてね。そうでない場合はブランクでいいよ。

という数式だ。ここに税理士だけじゃなく、「社会保険労務士の報酬」があって、この数値も入れたいとなると、OR（または）が出現する。
「税理士等の報酬」かまたは「社会保険労務士の報酬」と書いてあれば、B2に数値を入れてね、ということである。

=IF(OR(A1="税理士等の報酬",A1="社会保険労務士の報酬"),B2,"")
もし、A1のセルに「税理士等の報酬」と書いてあるか、またはA1のセルに「社会保険労務士の報酬」と書いてあれば、B2の数値をココに入れてね。じゃなければ、ブランクだよ。

ということだ。ミスを誘ったのは社会保険労務士の前の　A1＝　だった。これがないと＃VALUE君が出現する。ヴァリュー君が出てくると、何か式がおかしいのだが、解決方法は意外と簡単だった。これはAND（かつ）の場合も同じだ。

＜参考サイト＞

Excell ActiveXコントロールボタンを使う

管理人 (2017年5月17日 15:50) | コメント(0) | トラックバック(0)


ちょっと、使わないと忘れてしまう小技がある。今回はいつもは、マクロ登録ボタンを[ フォームコントロール ] を使っているのだが、ボタンに「色を付けたい」という単純な理由からだった。いつも灰色では目立たないから、少し、工夫したいと思っただけ。

＜参考サイト＞
定型の用紙に名簿から番号などを読み込み印刷するマクロ

自宅でプログラミング！

カラーコードをついたかったのだが、業務の時間が迫っていることもあり、今回はここで諦めて、時間の許すときに。

マクロボタン.gif

続きを読む: Excell ActiveXコントロールボタンを使う

IF文の中（条件分岐）

管理人 (2017年5月16日 14:38) | コメント(0) | トラックバック(0)


業務でIF文を使っていて、セルA1に文字が入っていて、かつセルE1に文字が入っていなければ、セルB1にある数値をセルZ1に表示する式を作っていた。つまり、「A１がブランクでなくて、E1がブランクであれば、B1の数値をZ1に出力させる」のだが、ここで、どんな計算式を組めばいいのかわからなくなってしまったのだ。IF文をANDで結ぶのか・・・

答えは簡単だった。
Z1 = IF（ A1 ＝ " "," ",IF ( E1= " ", B1 , " " ) )

となるのだ。ANDIF文を使うのかとか、いろいろと考えたのだが、結局は単純な計算式で解決してしまった。


＜参考サイト＞


続きを読む: IF文の中（条件分岐）
アーカイブ