タイムシートまたは請求書は PDF として届きます。労働時間、日付、プロジェクト コードはすべて、合計、並べ替え、分析できない形式でロックされています。スプレッドシートにデータを手動で入力する作業は、少数のエントリであれば機能しますが、1 か月のタイムシートでは完全なデータ入力作業になります。データを抽出すると、PDF が行き止まりのドキュメントからスプレッドシートに供給されるソースに自動的に変わります。
手動で入力した 1 時間は、請求されない 1 時間となります。自動抽出により、PDF に閉じ込められたデータが請求対象時間に変わります。
PDF から Excel タイムシートまたは請求書に時間と日付を抽出するには、テーブル構造を保持した変換ツールが必要で、その後、クリーンアップして日付と時刻の形式を標準化する必要があります。 WukongPDF の OCR PDF および変換ツールが抽出を処理し、以下のワークフローはタイムシート PDF のスタックを一括請求スプレッドシートに変換します。
タイムシート PDF から構造化スプレッドシートへの変換
タイムシートと請求書は設計上表になっています。各行は、日付、時間、プロジェクト コード、説明を含む時間エントリです。テーブル構造検出機能を備えた変換ツールは、これらの行と列をスプレッドシートのセルに直接マッピングします。変換ではグリッドが保持されますが、これが難しい部分です。クリーンアップにより、機械的な部分であるデータが標準化されます。
変換を実行し、出力を開きます。 PDF の各行がスプレッドシートの行として表示されることを確認します。日付、時間、プロジェクト コードがそれぞれ独自の列を占めていることを確認します。結合または分割された列は、そのレイアウトでテーブルの検出が失敗し、それらの特定のセルに対して別のツールまたは手動調整が必要であることを示します。
変換後の日付形式の標準化
PDF の日付とスプレッドシートの日付の形式が一致することはほとんどありません。変換されたスプレッドシートには、テキスト文字列として保存された日付、Excel が MM/DD/YYYY と誤って読み取る DD/MM/YYYY の日付、または変換で日付列が一般的なテキストとして扱われたために日付が完全に欠落している場合があります。後続の分析はすべて正しい日付に依存するため、最初に日付を修正します。
Excel の Text to Columns ウィザードは、1 回の操作でテキスト日付を実際の日付に変換します。日付列を選択し、「テキストから列へ」を開き、「区切り文字付き」を選択し、3 番目のステップで「ソース形式の日付」を選択します。 Excel は列全体を再解釈します。行間で形式が一貫していない場合は、どの位置に 12 より大きい値が含まれているかに基づいて各日付を個別に解析する数式を使用します。
時と分を 10 進時間に変換する
タイムシートには多くの場合、時間が HH:MM 形式で記録されます。7:30 は 7 時間 30 分を意味します。スプレッドシートの計算には 10 進数の時間が必要です。7.5 は 7 時間半を意味します。時間値に 24 を乗算して、HH:MM を 10 進数に変換します。7:30 を含むセルに 24 を乗算すると、7.5 と等しくなります。
期間ではなく開始時刻と終了時刻を含むタイムシートの場合は、終了時刻から開始時刻を減算します。結果が時間として表示される場合は、数値としてフォーマットし、10 進数の時間を表す 24 を掛けます。 09:00 から 17:30 までのシフトは 8.5 時間です。セルが正しく書式設定されると、計算によって変換が自動的に処理されます。
| データ型 | 一般的な変換の問題 | Excel での修正 |
|---|---|---|
| 日付 (DD/MM/YYYY) | MM/DD/YYYY として解釈され、月と日が入れ替わります | 正しいソース形式でテキストを列に変換 |
| 時間 (HH:MM) | テキストとして保存され、時間として認識されない | TIMEVALUE() 関数を使用してテキストを時刻に変換します |
| 所要時間（7:30） | 計算には小数が必要です (7.5) | 時間値を 24 で乗算し、数値としてフォーマットします |
| プロジェクトコード | 先頭のゼロが削除されます (001 が 1 になります) | 貼り付ける前に列をテキストとしてフォーマットするか、カスタムフォーマット「000」を使用してください |
| 通貨金額 | 通貨記号がセルに保存されているため、計算ができません | 検索と置換で記号を削除し、数値に変換します |
複数のタイムシート PDF を 1 つのスプレッドシートに統合する
各タイムシート PDF は独自のスプレッドシートに変換されます。 1 か月分のタイムシートを統合するには、変換されたすべてのスプレッドシートをマスター ワークブックにインポートします。 Excel の Power Query は、フォルダー内のすべてのファイルからデータを自動的にインポートします。変換されたすべてのスプレッドシートを 1 つのフォルダーに配置し、Power Query を使用してそれらを読み込み、1 つのテーブルに追加します。統合はファイル数に関係なく数秒で実行されます。
ソース ファイル名の列を追加して、各行が元のタイムシート PDF に遡るようにします。 Power Query には、フォルダーから読み込むときにソース ファイル名が自動的に含まれます。この追跡可能性は請求に関する紛争にとって重要です。特定の時間合計について質問するクライアントは、その合計がどのタイムシートからのものであり、いつ送信されたかを知る必要があります。
残業時間および異なる給与レートの処理
複数の給与レート、通常の時間、残業、週末のレートが含まれるタイムシートでは、レートを考慮した時間計算が必要です。変換されたデータをレート タイプごとに列に分割します。各列に正しい時間料金を適用します。各料金カテゴリ内で合計し、次にカテゴリ全体で合計支払額を算出します。プロジェクト コードまたは日のタイプをレート乗数にマッピングするルックアップ テーブルにより、レートの割り当てが自動化されます。
レート ロジックを一度構築すると、後続のすべてのタイムシートに適用されます。変換とクリーンアップの手順により、標準化されたデータが生成されます。レート計算式は、標準化されたデータを各期間で同様に処理します。手動レート検索で誤った乗数を休日シフトに適用するというエラーは、式によって毎回一貫してルールが適用されるため、発生しなくなります。
月次タイムシートのワークフローの自動化
毎月のフローは、PDF の収集、スプレッドシートへの変換、統合、日付と時間の標準化、請求レポートの生成という繰り返し可能なパターンに従います。各ステップは自動化可能です。 Power Query は統合と標準化を処理します。 Excel テンプレートは請求レポートを処理します。唯一の手動手順は PDF の収集ですが、新しいファイルの変換をトリガーする監視フォルダーによってそれも自動化できます。
来月実行する人のために、または手順を忘れたときに自分のためにワークフローを文書化します。手順、ツール、設定をステップごとに記載した 1 ページのチェックリストにより、月次の慌ただしい作業が月次のルーチンに変わります。文書化は、1 人が覚えていることに依存するワークフローと、スタッフが変わったり記憶が失われても存続するワークフローの違いです。