IT備忘録

エクセルというと表計算が目立ってしまい、意外と文字列操作にも便利な
関数が存在することが知られていなかったりします。
今回は、実際に使用しました関数をご紹介します。
名前を空白文字の前後で分けて、他の文字列と合わせる方法です。

セルA1のValueが”FirstName LastName”とした場合

=LEFT(A1,FIND(" ",A1)-1)&"."&RIGHT(A1,LEN(A1)-FIND(" ",A1))&
"@hogehoge.com"

とすると、
FirstName.LastName@hogehoge.com
という文字列を取得可能です。

解説
LEFT(A1,FIND(" ",A1)-1)
セルA1の空白文字前までを取り出す。
FIND(" ",A1)-1：空白文字までの文字数－１

RIGHT(A1,LEN(A1)-FIND(" ",A1))
セルA1の空白文字から右全ての文字列を抽出
LEN(A1)-FIND(" ",A1)：全文字数－空白文字までの文字数
＝抽出したい右からの文字数

それぞれを&で文字列をくっつける。

これらを応用することで、意外と便利に文字列を操作することが可能です。

過去記事
--Excel とbatでDisk容量チェック

--財務計算　ＰＰＭＴ（ピーペイメント）

--財務計算　ＰＭＴ （ペイメント）

--財務計算　FV （フューチャーバリュー）

--財務計算　PV （プレゼントバリュー）

VBAにてピポットテーブルを作成する方法です。

----
Dim COUNT as integer

COUNT = Range("A65536").End(xlUp).Row  ' 最後のレコードの行番号を入手

ActiveSheet.PivotTableWizard xlDatabase, Range(Cells(1, 1), Cells(COUNT, 10)), "", "集計テーブル"

ActiveSheet.PivotTables("集計テーブル").AddFields RowFields:=Array("ROW-01", _
    "ROW-02"), ColumnFields:=Array("COL-01", "COL-02")

With ActiveSheet.PivotTables("集計テーブル").PivotFields("DATA")
    .Orientation = xlDataField
    .Caption = "合計 / DATA"
    .Function = xlSum
End With

ActiveWorkbook.ShowPivotTableFieldList = True
----

今回のポイントは、レコード数が可変であっても対応するようにしました。
斜線字を環境に応じて変更します。
また、必要に応じてピポットテーブルの作成をマクロに記録し、
コピー＆ペーストにて編集しましょう。

--参考ページ
http://www.serpress.co.jp/excel/vba027.html
http://www11.plala.or.jp/koma_Excel/index.html

VBAにて、ファイルもしくはフォルダのある無しを判別する方法です。

--ファイルの判別
Dim FileExist, File_Path As String

File_Path = "D:\tmp\test.txt"
FileExixt = Dir(File_Path)

D:\tmp\test.txtが存在する場合、FileExitにはファイルのパスが代入されます。
ファイルが存在しない場合は空白("")が代入されます。

例）
Dim File_Path as String

File_Path = "D:\tmp\test.txt"

If Dir(File_Path) = "" Then
        Goto Label1　'ファイルが存在しない場合

Else
        '*** 処理 ***　'ファイルが存在する場合

End if

Label1:
        '*** 別処理 ***

--フォルダ（ディレクトリ）の場合
Dim DirectoryExist, DirectoryPath As String

DirecrotyPath = D:\tmp
DirectoryExist = Dir(DirectoryPath, vbDirectory)

ファイルの場合と同じように、D:\tmpが存在する場合、DirectoryExistには
フォルダのパスが代入され、フォルダが存在しない場合は空白（""）が
代入されます。

これらを使うと、ある特定のファイルを開こうとした際に、ファイルが
存在するかしないかの判別を行い、エラーを回避することができます。
（＊開こうとするファイルが存在しない場合、エラーでプログラムが
止まってしまいます）

--参考ページ
http://www.k1simplify.com/vba/tipsleaf/leaf243.html

Excel VBA のちょっとしたTipsです。

--画面表示を非表示にする
Application.ScreenUpdating = False

' *** 処理 ***

Application.ScreenUpdating = True

画面表示を非表示にすると、処理速度が上がります。

--シートを表示する・非表示にする
Sheets("tmp").Visible = xlSheetVisible
Sheets("tmp").Visible = xlSheetVeryHidden

シート"tmp"を表示にしたり、非表示にしたりします。

--カレントパス
ThisWorkbook.Path

例）カレントパスにブックを保存する
Dim Sheet_name As String
Sheet_name = ThisWorkbook.Worksheets("tmp").Range("A1")

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Sheet_name & ".xls"

Excelのdate関連の関数を駆使して、翌月の指定日が土日・祝祭日だった場合、
翌営業日の日付を返します。

--前提
A1 指定西暦（年）
B1 指定月（月）
C1 指定日（日）
E1 開始日から起算する日数
Holiday 祝祭日のテーブル（事前にマニュアル作成必須）
判定する日付をDATE(A1,B1,C1)とする

--関数
=IF(OR(WEEKDAY(DATE(A1,B1,C1),2)>=6,
NOT(ISERROR(VLOOKUP(DATE(A1,B1,C1),Holiday,1,FALSE)))),
WORKDAY(DATE(A1,B1,C1),E1,Holiday),
DATE(A1,B1,C1))

WEEKDAY(シリアル値,種類）　曜日を判別するために使います
例）WEEKDAY(DATE(A1,B1,C1),2)
DATE(A1,B1,C1)が月曜日だったら”１”、火曜日だったら”２”　以後、、、

DATE(年,月,日)　指定した日付を表すシリアル値を返します。

ISERROR(テストの対象)　セルの内容がエラー値だったら"TRUE"を返す。

VLOOKUP(検索値,範囲,列番号,検索の型)　
テーブルの左端列を検索し、指定した列と同じ行にある値を返します。

WORKDAY(開始日,日数,祭日)　
開始日から起算して日数で指定した日数だけ前あるいは後の日付に対応する
シリアル値を計算します。

これらを組み合わせて、
１行目　もし、判定する日付が土曜日、日曜日
２行目　もしくは祝祭日テーブルに合致する

Yes
３行目　開始日から起算して日数で指定した日数だけ前あるいは後の日付

No
４行目　判定する日付をそのまま返す

ちょっと複雑になってしまったので、メンテナンスは行いにくいです。
翌月の振込み日（たとえば２０日）の営業日の判別などに便利です。
場合によってはカレンダーを見たほうが早かったりしますが、、、

エクセルシートにデータを貼り付けると（シートに変更があると）

自動印刷するVBAを作りました。

----------------

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
   
Application.EnableEvents = False

    Cells.Select
    Selection.ClearContents
    Range("A1").Select

Application.EnableEvents = True

----------------

考えたフローは、
１．印刷をする
２．イベントのチェックを止める
３．データのクリアー
４．イベントのチェックを始める

イベントのチェックを止めておかないと、”データクリアー”時に
イベントが発生してしまい、ループしてしまいます。
Worksheet_Changeを使用するときにはループしないよう、
Application.EnableEvents = False
処理
Application.EnableEvents = True
とするとよいようです。

ExcelのVBAとDOSコマンド"dir"と"find"を駆使して、Windows Serverの
Disk空き容量を確認及び記録するシステムを作りました。

--ファイルPath
D:\disk_check.xls    このエクセルファイルから実行します。
D:\disk_check.bat    各サーバーの空き容量をdisk.txtへ書き出し
D:\disk.txt        出力ファイル

disk_check.bat
-----
@echo off

set USER-NAME=HogeUser
set PASSWD=hogehoge
set DRIVE=z:
set OUTFILE="D:\disk.txt"

set SERVER1=\\hoge_server01\group
set SERVER2=\\hoge_server02\group
set SERVER3=\\hoge_server03\group

if exist %DRIVE% net use /delete %DRIVE%

net use %DRIVE% %SERVER1% %PASSWD% /user:%USER%
dir %DRIVE% | find "バイトの空き領域" > %OUTFILE%
net use /delete %DRIVE%

net use %DRIVE% %SERVER2% %PASSWD% /user:%USER%
dir %DRIVE% | find "バイトの空き領域" >> %OUTFILE%
net use /delete %DRIVE%

net use %DRIVE% %SERVER3% %PASSWD% /user:%USER%
dir %DRIVE% | find "バイトの空き領域" >> %OUTFILE%
net use /delete %DRIVE%

echo. >> %OUTFILE%
time /t >> %OUTFILE%
date /t >> %OUTFILE%

exit
-----

disk_check.xls
-------------------------------
Private Sub CommandButton1_Click()

   Dim myID As Double
   myID = Shell("D:\disk_check.bat", vbNormalFocus)

End Sub
-------------------------------
Private Sub CommandButton2_Click()

    Worksheets("calc").Activate

    Workbooks.OpenText Filename:="D:\disk.txt", Origin:=932, StartRow:=1, _
        DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(16, 1) _
        , Array(33, 1), Array(49, 1)), TrailingMinusNumbers:=True
    Windows("disk.txt").Activate
    Worksheets("disk").Range("D1:D4").Select
    Selection.Copy
    Windows("disk_check.xls").Activate
    Range("B4").Select
    ActiveSheet.Paste
    Windows("disk.txt").Activate
    Worksheets("disk").Range("A6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("disk_check.xls").Activate
    Range("B2").Select
    ActiveSheet.Paste
    Windows("disk.txt").Activate
    Worksheets("disk").Range("A7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("disk_check.xls").Activate
    Range("B1").Select
    ActiveSheet.Paste
    Windows("disk.txt").Activate
    ActiveWindow.Close

    Windows("disk_check.xls").Activate
    Worksheets("Record").Activate

    With Worksheets("Record")
        .Range("A65536").End(xlUp).Offset(1, 0).Value = Worksheets("calc").Range("B1")
        .Range("A65536").End(xlUp).Offset(0, 1).Value = Worksheets("calc").Range("B2")
        .Range("A65536").End(xlUp).Offset(0, 2).Value = Worksheets("calc").Range("E4")
        .Range("A65536").End(xlUp).Offset(0, 4).Value = Worksheets("calc").Range("E5")
        .Range("A65536").End(xlUp).Offset(0, 6).Value = Worksheets("calc").Range("E6")
        .Range("A65536").End(xlUp).Offset(0, 8).Value = Worksheets("calc").Range("E7")
    End With

    Worksheets("calc").Activate

End Sub
-------------------------------

ExcelVBA用「エクセルアナライザー for AdIn」は、ご自分で作成した
重要なファイルのシートの保護及びブックの保護のパスワードを
紛失した場合を想定し、自動モード又は手動モードによりプロテクトの
解析を行うツールです。

--エクセルアナライザー
http://www7a.biglobe.ne.jp/~ShivaSoft/ShivaSoft-index3000/ShivaSoft3002.html
http://www.vector.co.jp/soft/win95/business/se354444.html

＊保護されたドキュメントを作者に断り無くパスワード解析するのは、
絶対に止めましょう！

ハイパーリンクが入力されたセルをまとめて選択し、何らかの演算を
実行すると一度にハイパーリンクを解除することができます。

１．どこか適当なセルを1つ選び、クリップボードへのコピーする
２．ハイパーリンクを解除したい複数のセルを選択
３．右クリックし、［形式を選択して貼り付け］
４．［演算］のオプションから「乗算」を選択し　ＯＫ

すると複数のセルのハイパーリンクを一度に外すことができます。

--参考ページ
http://www.atmarkit.co.jp/fwin2k/win2ktips/870hyperlink02/hyperlink02.html

営業日を計算させるため、下記のようなシートを作成しました。

--Cell A1
1/1の日付　例）2007/1/1

--Cell B1    (何月かを判定)
=MONTH(A1)

--Cell C1    (土日と月末だったら"1"返す)
=IF(OR(WEEKDAY(A1,2)>=6,A1=EOMONTH(A1,0)),"1","")

--Cell D1    (祝祭日をマニュアルで、"1"を入力)

--Cell E1    (土日、月末、祝祭日でなければ日付を返す)
=IF(AND(C1="",D1=""),A1,"")

--Cell F1    (曜日を判定　第２引数を"2"にすると月曜日が"1")
=WEEKDAY(A1,2)

「Workday_calc.xls」をダウンロード

A1:F1を下のCellへハンドルすると１年間のスケジュールを
作成することができます。

--参考　日本国民の祝・祭日

1. 元日                1/1
2. 成人の日          1月第2月曜日
3. 建国記念の日    2/11
4. 春分の日          3/21
5. 昭和の日          4/29
6. 憲法記念日       5/3
7. みどりの日        5/4
8. こどもの日         5/5
9. 海の日             7月の第3月曜日
10. 敬老の日          9月の第3月曜日
11. 秋分の日          9/23
12. 体育の日          10月の第2月曜日
13. 文化の日          11/3
14. 勤労感謝の日    11/23
15. 天皇誕生日       12/23

* Wikipediaを参照しました。

←クリック一票