[[20201207135102]]　『2つのCSVファイル間の操作を簡略化』（日向）　ページの最後に飛ぶ

『2つのCSVファイル間の操作を簡略化』（日向）

エクセル達人の皆様、ご相談させて下さい。

この度、今まで紙で行っていた給与明細の配布をWebに移行する事になりました。

現在、使用中の給与ソフトから書き出したCSVファイルをインターネットに取り込むことで従業員が閲覧できるようになるサービスなのですが、現在使用している給与ソフトから書きだしたCSVでは、いくつか加工を行わないといけない状況で困っています。

インターネットに取り込む際のCSVファイルには必須項目として
「社員コード」
「氏名（姓）」
「氏名（名）」
の３つの項目が必要なのですが、現在、使用している給与ソフトから書きだした給与一覧.csvには「社員コード」しかありません。

現在、使用中の給与ソフトから書きだし可能なファイルは、

1.全従業員が載っている「従業員一覧.csv」
2.一か月分の全従業員の給与情報が載っている「給与一覧.csv」

の２つで「従業員一覧.csv」には「氏名（姓）」「氏名（名）」のデータが、BとC列に入っています。

現在は以下の手順で加工を行って使用しています。

1.給与一覧.csvを開き、BとC列を挿入　※シート名は「給与明細データ」の１つのみです。
2.挿入したセルに項目名「氏名（姓）」「氏名（名）」を追加
3.従業員一覧.csvを開き、シート「従業員データ」を選択し、移動またはコピーを選び、給与一覧.csvにシート「従業員データ」を移動
4.シート「給与明細データ」に切り替え、B列に計算式「=VLOOKUP($A4,従業員データ!$A$1:$HN$220,2,FALSE)」C列に「=VLOOKUP($A4,従業員データ!$A$1:$HN$220,3,FALSE)」を手入力し、入力されている従業員の列までドラッグしてコピー
5.B列とC列の従業員名が入っているデータを選択して、コピー→形式を選択して貼り付け→値を選び、計算式を値で上書き
6.B列とC列の従業員名が入っているデータを選択して、置換。検索する文字列を全角スペース、検索後の文字列を何も入力せず全置換で不要なスペースを削除。※従業員一覧.csvの名前の中に不要な全角スペースがたくさん入っている為。

以上です。

ご相談したいのは、この上記の手順を可能な限り簡略化したいです。

やり方が全く分からないのですが、マクロ等で自動化可能なのでしょうか？

もし可能であれば、大変厚かましいのは重々承知しておりますが、ご教授頂けると幸甚です。

よろしくお願いいたします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 １．簡単なデータ(２、３人分）で、
　　 その手順をマクロの記録でコードを書かせて、ここにアップして頂けませんか？

 　　※開示するのが不適切な情報は、別の文字列に置き換えて下さい。

 ２．csvファイルの名とシート名が一致していないような記述があるんですが、本当なんですか？

（半平太） 2020/12/07(月) 16:03

 衝突しましたが…

 ＞「従業員一覧.csv」には「氏名（姓）」「氏名（名）」のデータが、BとC列に入っています
 A列に社員コードが入っているんですかね？そうでないとlookupできないはずなのでそうと仮定して…

 365であればpowerqueryの使用でどうでしょうか？

 給与一覧
 社員コード	給与
 1111	100円
 2222	200円
 3333	300円
 4444	400円
 5555	500円

 従業員情報
 社員コード	姓	名
 1111		佐藤	太郎
 2222		佐藤	花子
 3333		田中	太郎
 4444		田中	花子
 5555		佐々木	太郎

 として、
 = Table.NestedJoin(給与一覧, {"社員コード"}, 従業員情報, {"社員コード"}, "従業員情報", JoinKind.LeftOuter)
 = Table.ExpandTableColumn(ソース, "従業員情報", {"姓", "名"}, {"従業員情報.姓", "従業員情報.名"})
 = Table.ReplaceValue(#"展開された 従業員情報1","　","",Replacer.ReplaceText,{"従業員情報.名"})

 社員コード	給与	従業員情報.姓	従業員情報.名
 1111		100円	佐藤		太郎
 2222		200円	佐藤		花子
 3333		300円	田中		太郎
 4444		400円	田中		花子
 5555		500円	佐々木		太郎

 というテーブルができます。
 上記のみで全角スペースも置換できている状態です。
（ななし） 2020/12/07(月) 16:14

 あ、名前ってもしかして姓もですかね？
 その場合、最後は
 = Table.ReplaceValue(#"展開された 従業員情報1","　","",Replacer.ReplaceText,{"従業員情報.姓", "従業員情報.名"})
 でお願いします。
（ななし） 2020/12/07(月) 16:16

ご返事ありがとうございます。

ファイル名や計算式、手入力してて間違って入力してしまったところがいくつかありました。

本当に申し訳ございません。

ご指示の通り、マクロの記録をやってみました。

よろしくお願いいたします。

Sub Macro1()
'
' Macro1 Macro
'

    Columns("B:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "氏名（姓）"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "氏名（名）"
    Range("B4").Select
    Sheets("従業員一覧").Select
    Sheets("従業員一覧").Move Before:=Workbooks("給与一覧.csv").Sheets(1)
    Sheets("給与一覧").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,従業員一覧!R1C1:R220C222,2,FALSE)"
    Range("B4").Select
    Selection.AutoFill Destination:=Range("B4:C4"), Type:=xlFillDefault
    Range("B4:C4").Select
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,従業員一覧!R1C1:R220C222,3,FALSE)"
    Range("B4:C4").Select
    Selection.AutoFill Destination:=Range("B4:C129"), Type:=xlFillDefault
    Range("B4:C129").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="　", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub
（日向） 2020/12/07(月) 16:30

言葉足らずで申し訳ございません。

従業員と給与どちらのcsvも、1行目は社員コードになります。

従業員csvは1行目が項目なのですが、給与は3行目が項目となっております。
（日向） 2020/12/07(月) 16:33

×　従業員と給与どちらのcsvも、1行目は社員コードになります。

○　従業員と給与どちらのcsvも、1列目は社員コードになります。

すみません汗
（日向） 2020/12/07(月) 16:34

 VLOOKUPで毎回計算して値に置換するなら
 社員コードと姓名のリストを作業用BOOKであらかじめ作っておいて
 そのBOOKの別シートに給与一覧を張り付けてリストにVLOOKUPで呼び出した表を
 新しいBOOKでCSVにした方が
 給与一覧を張り付けてCSV用データ作るだけなので毎回VLOOKUP書いてコピーしたり
 氏名を置換する必要がないのではそれをマクロ化すればもう少し簡単なような

（なるへそ） 2020/12/07(月) 16:43

 >ご相談したいのは、この上記の手順を可能な限り簡略化したいです

　＞3.従業員一覧.csvを開き、シート「従業員データ」を選択し、移動またはコピーを選び、給与一覧.csvにシート「従業員データ」を移動

移動する必要があるかな？そのまま参照すればよいのでは？

 >C列に「=VLOOKUP($A4,従業員データ!$A$1:$HN$220,3,FALSE)」を手入力し、入力されている従業員の列までドラッグしてコピー

ドラッグしなくても、フィルハンドルダブルクリックでOKのはず。

 >5.B列とC列の従業員名が入っているデータを選択して、コピー→形式を選択して貼り付け→値を選び、計算式を値で上書き

わざわざ値に直さなくても、CSV形式では数式は保存できないから、保存したら勝手に値になってるはず。

　＞6.B列とC列の従業員名が入っているデータを選択して、置換。検索する文字列を全角スペース、検索後の文字列を何も入力せず全置換で不要なスペースを削除。※従業員一覧.csvの名前の中に不要な全角スペースがたくさん入っている為。

新しいシートを追加し、
表全部をコピーして、新しいシートにリンク貼り付け
セルに入った参照式にTrim関数を追記し、数式をコピー
そのまま名前を付けてCSV形式で保存
（複数シートもCSVでは保存できないので、アクティブなシートのみ保存されます。）

あぁ、先に数式を仕込んでおいて、
参照するファイルを変える方が簡単かもですね。
（まっつわん） 2020/12/07(月) 16:59

 複数のブックを扱う時は、どのブックなのか明示しないと、トラブルの元になるので・・

 Sub Macro1()
     Dim LrEmp As Long
     Dim LrPay As Long

     With Workbooks("給与一覧.csv").Sheets("給与一覧")
         .Columns("B:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
         .Range("B3").Value = "氏名（姓）"
         .Range("C3").Value = "氏名（名）"
     End With

     With Workbooks("従業員一覧.csv").Sheets("従業員一覧")
         .Copy Before:=Workbooks("給与一覧.csv").Sheets(1)
         LrPay = .Cells(.Rows.Count, "A").End(xlUp).Row
     End With

     With Workbooks("給与一覧.csv").Sheets("給与一覧")
         LrEmp = .Cells(.Rows.Count, "A").End(xlUp).Row

         .Range("B4:B" & LrEmp).Formula2R1C1 = _
              "=VLOOKUP(RC1,従業員一覧!R1C1:R" & LrPay & "C" & LrPay & ",{2,3},FALSE)"

         With .Range("B4:C" & LrEmp)
             .Copy

             .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
             :=False, Transpose:=False
             .Replace What:="　", Replacement:="", LookAt:=xlPart, _
             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
             ReplaceFormat:=False
         End With

         Application.DisplayAlerts = False
         　　Workbooks("給与一覧.csv").Sheets("従業員一覧").Delete
         Application.DisplayAlerts = True
     End With
 End Sub

（半平太） 2020/12/07(月) 19:50

コメント返信：

[ 一覧(最新更新順) ]

[[20201207135102]] 『2つのCSVファイル間の操作を簡略化』（日向） ページの最後に飛ぶ

[[20201207135102]]　『2つのCSVファイル間の操作を簡略化』（日向）　ページの最後に飛ぶ