質問: ### 前提・実現したいこと 売上の集計をExcelのマクロとExcelの関数で行っています。 マクロでしていることは、 ショップAのシートに、ショップAの売上結果を貼り付ける (B、C、D..とショップごとに同じことを繰り返す)を行っています。 ショップごとに取得したい値の名称や、列の場所が異なるので、 関数を使用して、提出用のフォーマットの並びに合せるシートを1枚はさんでいます。 マクロ部分ではなく、提出用のフォーマットの並びに合せるシート部分が重い原因だとおもいます。 #### 1ショップAの売上.csv | 売上時刻 | 商品名 | 端末 | ショップ滞在時間 | 売上単価 | | --- | --- | --- | --- | --- | | 2020/01/01 | 中古パソコン | iPhone | 00:03:20 | 20000 | | 2020/01/01 | 中古パソコン | android | 00:03:20 | 20000 | | 2020/01/01 | 中古パソコン | PC | 00:03:20 | 20000 | **↓マクロでそのまま貼り付ける** #### 2ショップAの売上.csvをそのまま貼り付けるシート | 売上時刻 | 商品名 | 端末 | ショップ滞在時間 | 売上単価 | | --- | --- | --- | --- | --- | | 2020/01/01 | 中古パソコン | iPhone | 00:03:20 | 20000 | | 2020/01/01 | 中古パソコン | android | 00:03:20 | 20000 | | 2020/01/01 | 中古パソコン | PC | 00:03:20 | 20000 | #### 3列調整シート | 売上時刻 | 商品名 | 売上単価 | ショップ情報 | | --- | --- | --- | --- | | 2020/01/01 | 20000 | 中古パソコン | ショップA | | #N/A | #N/A | #N/A | #N/A | | #N/A | #N/A | #N/A | #N/A | | 2020/01/01 | 20000 | 中古パソコン | ショップB | | #N/A | #N/A | #N/A | #N/A | | 2020/01/01 | 20000 | 中古パソコン | ショップC | | #N/A | #N/A | #N/A | #N/A | | #N/A | #N/A | #N/A | #N/A | | #N/A | #N/A | #N/A | #N/A | * ショップA、B、C...と全てのシートを呼び出している * いらない項目を省き、列の並び順を調整している * 何行のレコードのCSVかわからないので、100行前後のデータのショップは200行のデータが持ってこれるようにしている。(これをショップごとに行う) * 情報を引っ張るところを多めにとっているが、使用されいない列は「#N/A」となる 存在している行だけもってくる方法がわからなかったので、 とりあえず列多めに関数入れておいて、「#N/A」のところをソートで除外してデータを使用しています。 データが存在しているレコードだけ表示させる方法があれば、 軽くなるのではないかと思っている。 #### 4売上提出用のExcel | 売上時刻 | 商品名 | 売上単価 | ショップ情報 | | --- | --- | --- | --- | | 2020/01/01 | 20000 | 中古パソコン | ショップA | | 2020/01/01 | 20000 | 中古パソコン | ショップB | | 2020/01/01 | 20000 | 中古パソコン | ショップC | ※3のデータを並び替えやソートを使用して、必要な部分を別のExcelに貼り付けて提出 ### 発生している問題 関数で各ショップから売上情報を持ってきているが、抜けもれがないよう、DLしたCSVのレコードの倍のレコードを持ってこれるように`=シート名!A10000`のような関数を入れている。 それを各ショップごと(20件くらい)に行っているため動作が重い。 抜けもれがないよう、多めに関数を入れていることが動作が重い原因で、 使用されていない「#N/A」と表示される箇所もおおくもっとスマートにできないかと考えております。 ### 試したこと googleのスプレッドシートだと、A列に〇が入って入れば持ってくる的な使用方法があったのですが、Excelだとない?それとも他の方法がある? とりあえずはこれで集計できているのですが、動作を軽くできたらなおよしと思ったので、 みなさまの知見を拝借したいと思いました。 スプレッドシートだと、セキュリティ上NGらしいので、 Excelで良い方法があれば教えていただきたいです。 ### 補足情報(FW/ツールのバージョンなど) Excel win10 office365
回答: こんにちは。よろしくおねがいいたします。 ショップA、B、CのCSVはそれぞれ、カラムの配列が違うんですね? それを一個の集計シートにまとめたい、というわけなんですね? すると・・こういうカンジのことでしょうか? !イメージ説明 だとすると、それぞれのCSVを個別に読み込んで、カラムの位置を意識しながら転記していくのがいいかな、と考え、GetOpenFilenameを使うことにしました。 以下が、僕が実験してみたコードです。 ``` Option Explicit Sub ShopSalesSummary() '各店舗のCSVを開いて、このブックの”集計シート"に転記(追記)します Dim OutputSheet As Worksheet Set OutputSheet = ThisWorkbook.Worksheets("集計シート") Dim ShopA_CSV As String Dim ShopB_CSV As String Dim ShopC_CSV As String Dim Header As String Dim i As Long, j As Long 'CSVの1行ぶんの文字列です Dim strCSV As String 'CSVの1行ぶんを、配列に格納するためのものです Dim ArrCSV As Variant Dim LastRow As Long '----ここからSHOP-Aの処理です 'SHOP-AのCSVをダイアログボックスで選択します ShopA_CSV = Application.GetOpenFilename("CSVファイル,*.csv", Title:="SHOP-AのCSVを選択してください") 'うっかりキャンセルボタンを押された場合の処理 If ShopA_CSV = "False" Then MsgBox "キャンセルされました" Exit Sub End If 'SHOP-AのCSVファイルを開きます Open ShopA_CSV For Input As #1 '1行目のヘッダを読み込みます。しかし使いません。 Line Input #1, Header 'このときの集計シートの最終行の、その次の行を得ます LastRow = OutputSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1 i = LastRow 'SHOP-AのCSVファイルの、このとき2行目から読み込みを始めます Do Until EOF(1) Line Input #1, strCSV ArrCSV = Split(strCSV, ",") 'ここでショップ名を直接セットしていきます OutputSheet.Cells(i, 1).Value = "ショップA" 'ショップ名 'CSVファイルは文字列なので、日付は金額のフォーマットを指定しながらセットしていきます OutputSheet.Cells(i, 2).Value = Format(ArrCSV(0), "yyyy/mm/dd") '売上日時 OutputSheet.Cells(i, 3).Value = Format(ArrCSV(1), "@") '商品名 OutputSheet.Cells(i, 4).Value = Format(ArrCSV(3), "#") '売上単価 '集計シートの行へシフトします i = i + 1 'CSVファイルの次の行へシフトします Loop 'SHOP-AのCSVファイルを閉じます Close #1 '----ここからSHOP-Bの処理です 'SHOP-BのCSVをダイアログボックスで選択します ShopB_CSV = Application.GetOpenFilename("CSVファイル,*.csv", Title:="SHOP-BのCSVを選択してください") 'うっかりキャンセルボタンを押された場合の処理 If ShopB_CSV = "False" Then MsgBox "キャンセルされました" Exit Sub End If 'SHOP-BのCSVファイルを開きます Open ShopB_CSV For Input As #1 '1行目のヘッダを読み込みます。しかし使いません。 Line Input #1, Header 'このときの集計シートの最終行の、その次の行を得ます LastRow = OutputSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1 i = LastRow 'SHOP-BのCSVファイルの、このとき2行目から読み込みを始めます Do Until EOF(1) Line Input #1, strCSV ArrCSV = Split(strCSV, ",") 'ここでショップ名を直接セットしていきます OutputSheet.Cells(i, 1).Value = "ショップB" 'ショップ名 'CSVファイルは文字列なので、日付は金額のフォーマットを指定しながらセットしていきます OutputSheet.Cells(i, 2).Value = Format(ArrCSV(0), "yyyy/mm/dd") '売上日時 OutputSheet.Cells(i, 3).Value = Format(ArrCSV(1), "@") '商品名 OutputSheet.Cells(i, 4).Value = Format(ArrCSV(3), "#") '売上単価 '集計シートの行へシフトします i = i + 1 'CSVファイルの次の行へシフトします Loop 'SHOP-BのCSVファイルを閉じます Close #1 '----ここからSHOP-Cの処理です 'SHOP-CのCSVをダイアログボックスで選択します ShopC_CSV = Application.GetOpenFilename("CSVファイル,*.csv", Title:="SHOP-CのCSVを選択してください") 'うっかりキャンセルボタンを押された場合の処理 If ShopC_CSV = "False" Then MsgBox "キャンセルされました" Exit Sub End If 'SHOP-CのCSVファイルを開きます Open ShopC_CSV For Input As #1 '1行目のヘッダを読み込みます。しかし使いません。 Line Input #1, Header 'このときの集計シートの最終行の、その次の行を得ます LastRow = OutputSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1 i = LastRow 'SHOP-CのCSVファイルの、このとき2行目から読み込みを始めます Do Until EOF(1) Line Input #1, strCSV ArrCSV = Split(strCSV, ",") 'ここでショップ名を直接セットしていきます OutputSheet.Cells(i, 1).Value = "ショップC" 'ショップ名 'CSVファイルは文字列なので、日付は金額のフォーマットを指定しながらセットしていきます OutputSheet.Cells(i, 2).Value = Format(ArrCSV(0), "yyyy/mm/dd") '売上日時 OutputSheet.Cells(i, 3).Value = Format(ArrCSV(2), "@") '商品名 OutputSheet.Cells(i, 4).Value = Format(ArrCSV(1), "#") '売上単価 '集計シートの行へシフトします i = i + 1 'CSVファイルの次の行へシフトします Loop 'SHOP-CのCSVファイルを閉じます Close #1 End Sub ``` 毎日のCSVは、ヘッダがあると思うので、それをスキップするようにしました。 このArrCSV(#)の部分で、「カラムの何列めを、転記シートのどのカラムにセットするか」を制御しています。 CSVは文字列なので、このFormat(....)で、書式の設定もしています。 これで僕のほうで実験したところ、うまくいきました。 よかったらお試しください。 なにかわからないことがありましたら、またお問い合わせください。 ありがとうございます。