業務上使用している資料は主にExcelでまとめています。
舞台音響の業務に従事する私は、『回線表』という資料を作成し、現場を共にするスタッフと情報を共有しています。
ただし、わかりやすく見た目の良い表がかければいいので、本来のExcelの趣旨とははなれていますが、
- Windows, Macに関わらず使用可能
- データの受け渡し先でも閲覧できる（可能性が高い）
以上のような理由から、Excelで表を作っています。
#目次
#従来の表と問題点
以前まで使っていた回線表
従来使用していた回線表は上の画像のようなものです。（内容は架空のものです）
必要な情報を表にまとめ、印刷し渡す。時にはPDFで送ることもあります。
特別なことは一切しておらず、Excelである必要すらないと思えるようなシンプルな表です。
従来の問題点
表としては問題のないものですが、内容を考えながら作成すると項目の入れ替えや並び替えなどが生じた際、面倒に感じることがありました。
あくまで、印刷したときの見た目を重視しているため、デザインが崩れたり、結合が崩れたりなど、本来表計算としては想定されていない点で不便に感じていました。
#すぐできる改善
改善1 『バージョン表記を自動化』
バージョン表記のセルを関数で自動化しました。
バージョン表記をしているセルの中身を次のように書き換えます。
="version " & TEXT(NOW(), "yymmdd hhmm")
どこかのセルに何か変更があった場合に、必ず分刻みで時刻がversionとして明記されます。
従来の表では、データの作成バージョンを毎回打ち込んでいたため、内容が変わってもバージョン表記が上がっていないということが多々ありました。
同じような表を作成して比較することも多く、場合によっては昨年度のデータから書き換えることもあるため、バージョン表記が変わっていないと混乱の原因となります。
改善2 『別シートに基本データを分ける』
上の画像のように、別シートに基本データを分けました。
もともと1枚の上に印刷するものなので、分ける必要はなかったのですが、今後の展開のために敢えて分けます。
私は劇場をメインとしているので、大・小ホールを入力する欄も作りました。
その下には別シートで使うための表記を関数でまとめています。これにより、例えば日付が変更になった場合でも、1つひとつのセルを変更することなく書き換えが完了します。
たとえば、セルB14の中身は
="仕: "&TEXT(B8, "yyyy/mm/dd") &" RH: "&TEXT(B10, "yyyy/mm/dd") &" 本: "&TEXT(B12, "yyyy/mm/dd")
となっています。
#大規模な改善
##内容のデータベース化
まずは、内容の入力のシートと表示用のシートを分けることを考えます。
まずは必要な情報を書き出します。
見出しに当たる1行目とA列については、「ウインドウ枠の固定」機能で固定しておきます。
データベースを作る癖でA列に数字を入れていますが、行を識別する必要なかったため、結果的には使用していません。
データベース化した内容を印刷用シートに表示
C5セル（ch1のName）の中身は次のように設定しました。
=@IF(
SUMPRODUCT(
(data!$D$2:$D$200=$A$1)*(data!$E$2:$E$200=$A5)*(ROW(data!$D$2:$D$200)-1)
)=0,
"",
IF(
ISERROR(
@INDEX(data!$G$2:$G$200,
SUMPRODUCT(
(data!$D$2:$D$200=$A$1)*(data!$E$2:$E$200=$A5)*(ROW(data!$D$2:$D$200)-1)
)
)
),
"",
IF(
@INDEX(data!$G$2:$G$200,
SUMPRODUCT(
(data!$D$2:$D$200=$A$1)*(data!$E$2:$E$200=$A5)*(ROW(data!$D$2:$D$200)-1)
)
)=0,
"",
INDEX(data!$G$2:$G$200,
SUMPRODUCT(
(data!$D$2:$D$200=$A$1)*(data!$E$2:$E$200=$A5)*(ROW(data!$D$2:$D$200)-1)
)
)
)
)
)
複雑な関数になっていますが、SUMPRODUCT関数とINDEX関数を利用して、必要なデータがあれば表示させる式になっています。
ミキサーが複数になってもいいように、表示シートのA1を表示させるミキサー名として参照しています。
エラーがあっても表示しないために、ISERROR関数で弾いているため、同じ数式が何度も入っています。
他のセルについてもこの数式の参照セルが変わっているだけで。基本はこの数式をもとにしています。
##必要な情報を抜き出した別シートを作成
マルチケーブル表
マルチ回線を使用するものだけを抜き出すシートを作成しました。
基本は前セクションで書いた関数をもとにしています。
表の左上のセル（A4やF4）のマルチケーブルを表示するようになっています。
荷出し表
荷出しに便利な上記のような表をピボットテーブルで作りました。
各マイクの本数、スタンドの本数、そして、スタンドごとのマイクの本数が一覧になっています。
倉庫での荷出しだけでなく、現場でのスタンドの準備にも威力を発揮します。
表を見ながら数を数える必要はありません！
##iPad用に最適化したレイアウトのシートを作成
A4印刷用と別にiPad用に最適化したシートを作りました。
最近まで、A4印刷用のものをPDFにしてiPadで閲覧していましたが、次のような点からこのまま使うのは理想的ではないと判断しました。
- ほぼ全員が拡大して表示していた
- iPadを見るその瞬間必要な情報は表の一部である
- 紙と違い紛失リスクが少ないため、数ページに渡る資料でも問題がない
そこで、次のような表をiPad用としました。
縦の閲覧を基本とし、inputとoutputを別の表とすることで、ある程度拡大しなくても見やすい表にすることにしました。
また、カラーの表にしても印刷コストの問題がないため、inputとoutputを別カラーとし、パッと見分けやすくしました。なお、カラーユニバーサルデザインの観点から、文字を色分けすることは避けています。CUD対策についてはこれからの課題としています。
#VBAを使用した印刷・PDF保存の自動化
##PDF保存の自動化
まず、PDF保存を自動化します。
ボタンを押すだけで必要な表をPDFにすることを目標とします。
以下のvbaはExcel for Mac 16.35, Mac OS 10.14.6で動作確認しています。
Sub outputPDF()
Dim ws As Worksheet
'出力ページ設定
For Each ws In Worksheets(Array("iPad_LS9-input", "iPad_LS9-output"))
With ws.PageSetup
.PaperSize = xlPaperA4
.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHorizontally = True
.TopMargin = 0
.BottomMargin = 0
.LeftMargin = 0
.RightMargin = 0
End With
Next ws
Worksheets(Array("iPad_LS9-input", "iPad_LS9-output")).Select
LabelFileSel:
'for Mac
If Application.OperatingSystem Like "*Mac*" Then
Dim fPath As String
Dim scr As String
scr = "try " & vbCrLf & _
"return POSIX path of (choose folder with prompt ""PDFの保存先を選択"")" & vbCrLf & _
"on error" & vbCrLf & _
"return ""0""" & vbCrLf & _
"end try"
fPath = MacScript(scr)
If fPath = "0" Then
MsgBox ("キャンセルされました")
Worksheets("event-data").Select
Exit Sub '終了する
End If
Else
'for Windows
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "C:"
If .Show = 0 Then
MsgBox "キャンセルされました"
Worksheets("event-data").Select
Exit Sub '終了する
ElseIf .Show = -1 Then
fPath = .SelectedItems(1)
End If
End With
End If
Dim eventName As String
eventName = Worksheets("event-data").Range("B20").Value
Dim fName As String
fName = fPath & eventName & "回線表_for_iPad.pdf"
Dim isFileExist As String
isFileExist = Dir(fName) 'ファイルの存在確認
If Len(isFileExist) > 0 Then
overwrite = MsgBox("同名のファイルが存在しています。 上書きしますか？", 3)
If overwrite = vbYes Then
GoTo LabelWrite '出力する
ElseIf overwrite = vbNo Then
GoTo LabelFileSel '保存先選択に戻る
ElseIf overwrite = vbCancel Then
MsgBox ("キャンセルされました")
Worksheets("event-data").Select
Exit Sub '保存せず終了
End If
End If
LabelWrite:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fName '選択したシートをPDF出力
MsgBox (fName & vbCrLf & "に保存しました")
Worksheets("event-data").Select 'ボタンのシートに戻る
End Sub
データ入力シートの催し名をファイル名としています。
保存先は選択できるようにしています。
同名ファイルが存在する場合は上書きするかどうかを確認するようにしています。
Macの場合、vba標準の機能でフォルダーの選択プロンプトを出せないため、MacとWindowsとで処理を分けています。
なお、Windowsのほうは全くチェックできていません。
ページ設定は次のようにしました。
- A4
- 縦方向
- 拡大縮小しない
- 縦1ページ、横1ページに固定
- 水平方向に中央
- マージンは上下左右0
.PageSetup
.PaperSize = xlPaperA4
.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHorizontally = True
.TopMargin = 0
.BottomMargin = 0
.LeftMargin = 0
.RightMargin = 0
##印刷の自動化
印刷の自動化は、印刷用のシートをA4横方向に印刷することにします。
Sub printoutA4()
Dim ws As Worksheet
For Each ws In Worksheets(Array("mix_LS9"))
With ws.PageSetup
.PaperSize = xlPaperA4
.Orientation = xlLandscape '横向き
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterHorizontally = True
.TopMargin = Application.CentimetersToPoints(1)
.BottomMargin = Application.CentimetersToPoints(1)
.LeftMargin = Application.CentimetersToPoints(1)
.RightMargin = Application.CentimetersToPoints(1)
End With
Next ws
Worksheets(Array("mix_LS9")).Select
On Error Resume Next
res = Application.Dialogs(xlDialogPrint).Show
Worksheets("event-data").Select
If res = False Then
MsgBox "印刷がキャンセルされました"
Exit Sub
End If
End Sub
なお、Macでは直接印刷がうまく行かず、印刷ダイアログを開くということにしています。
#おわりに
ざっとですが、私の業務快適化をまとめてみました。
これで、いつも悩んでいた作業時間が短縮されることと期待しています。