【エクセルvba(マクロ)】できる事を増やし使える人になったら女の子に「すごぃ！」って言われた時の話

ホーム
仕事効率化
【エクセルvba(マクロ)】できる事を増やし使える人になったら女の子に「すごぃ！」って言われた時の話

仕事効率化

2018.07.28

【エクセルvba(マクロ)】できる事を増やし使える人になったら女の子に「すごぃ！」って言われた時の話

2020/7/30 追記

『Udemy』向けExcelVBA講座を作成しましたので、
ぜひご活用いただければと思います。

エクセルvba(マクロ)でやりたかった事

『エクセルvba(マクロ)』は文字通り、エクセルでできる事ならほぼほぼなんでもできるのですが、

そもそもなにがやりたいの？ってのがボケちゃうとその威力も半減しちゃいます。

今回職場の女の子が困った顔で依頼してきたのが、

職場の女の子

別々のシートにある項目を一つのシートにまとめて、合計金額も出したいんです。

というものでした。

よくよく話を聞くと、

会社で使っているサーバーの中に『売上管理のエクセル』が入っていて、

その『売上管理のエクセル』は毎月ファイルが作成されていて、

(例えば H30年07月.xls、H30年08月.xlsx のように増えていく。)

そのエクセルファイルの中に、支店毎にいくつかのシートが入っていて、

支店毎に日々の売上内容が追記されていって、

1日毎に、昨日の金額は合計いくらでした、というのを把握する必要があるとの事で。

エクセルサンプルはこんな感じです。

シート毎に、『東京、大阪、名古屋、広島、福岡』という形で支店名にわかれていて、

日付と、担当者と、商材と、売上金額と、粗利が記載されていくと。

それが支店毎に毎日ふえていって、多い時は100件以上追加されるので、

その合計金額を出すのがとても大変という事でした。

なんでも今は、

同じ日付の項目を一つ一つコピペして、indexシートにあつめて、

全部集まったところでsum関数をかけて合計金額をだしていると。

アオキ

そ、それは大変ですね・・・

アオキ

それ毎日やってたんですね・・

職場の女の子

そうなんですー(涙)

悩める女性の依頼とあっちゃあ断るわけにはいかないでしょっとばかりに、

『エクセルvba(マクロ)』をつくることになりました。

エクセルvba(マクロ)を作る時のコツ

『エクセルvba(マクロ)』は、

簡単とは言われるもののプログラムには変わりなく、

しっかりつくるとなるとやっぱり『プログラミング思考』は欠かせないかなと思います。

僕が考える簡易版の『プログラミング思考』はこんな感じと思っていまして、

1. やりたい事をはっきりと決める
2. 細かく分解する
3. グループに分ける
4. 順番をつける
5. 見た目をきれいにする(使いやすくする)

職場の女の子からのリクエストを細かく考えていく必要があります。

参考記事

もんプロ～問題発見と解決のためのプログラミング〜

プログラミング思考を身につけるために焼き鳥とハイボールを頼んでみる

https://coinbaby8.com/programing-thinking.html

最近たまたま東洋経済という雑誌を見ていたんですが、その中に気になるキーワードがありました。それは、『プログラミング思考』。2020年から小学校でもプログラミングが必須になるそうで、これからますますプログラムの重要性が増えてくるような内容でした。娘が小学校に入る頃にはますます大事になるだろうなぁと。ちょっとでも早めに準備させておきたいなぁと思い奥さんに話してみてはいるんですが、どうも奥さんはプログラムのお勉強にあまり乗り気ではなく。どうやらプログラムのイメージって、黒い画面に向かってカチャカチャひ...

1. やりたい事をはっきりと決める

今回のリクエストは、

職場の女の子

別々のシートにある項目を一つのシートにまとめて、合計金額も出したい。

という事でした。

目的がはっきりしてるのでわりかしつくりやすかったと思います。

2. 細かく分解する

『エクセルvba(マクロ)』の場合、エクセルを使う関係上、エクセルの機能をメインに考える必要がでてきます。

今回の場合は、

サーバーにある『売上管理エクセル』を開く
特定の日付があればその行をコピーしてindexシートに貼り付ける
それを支店シート毎に繰り返す
全部貼り付けしたら、合計金額を出す

という内容で実現できそうです。

また、注意しておきたかったのが、

毎月ファイルが作成される、という事でした。

(例えば H30年07月.xls、H30年08月.xlsx のように増えていく。)

つまり、定期的に開くファイル名が変わるという事ですね。

それも踏まえて、『エクセルvba(マクロ)』を組み込む必要がありました。

やり方は色々あると思いますが今回の場合、

職場の女の子のパソコンに、『エクセルvba(マクロ)』ファイルを置いておいて、

ファイル名と日付を書いてもらった後に、ボタンを押して実行する事で、

ファイル名や日付が変わっても対応できるようにしました。

こんな感じですね。

3. グループに分ける

今回の場合はエクセルくくりなので、グループに分けるまでもないかな、、と思ったり。

強いて言えば、職場の女の子のパソコンに保存してる『エクセルvba(マクロ)』を開く段階と、

ボタンを押して実行させる段階に分かれる感じでしょうか。

細かくいうとどんどん細かくなるんですけれど。

4. 順番をつける

今回の場合、

職場の女の子のパソコンに保存しているエクセルvba(マクロ)を開く
ファイル名や日付を入力した後に、ボタンを押す
記入したパス(サーバー)にある『売上管理エクセル』を開く
全部のシートをまるっとコピーする
職場の女の子エクセルに丸ごと貼り付ける(サーバーのエクセルは閉じる)
記入した日付の項目をindexシートにコピーする
全部コピー終わったら、sum関数で合計を計算する
不要なシートは全て消す。

というような流れになるのかなと思います。

アオキ

この辺はエクセルの動きのクセもあるので、慣れですかね・・

もんプロ～問題発見と解決のためのプログラミング〜

【ExcelVBA(マクロ)】(エクセル)よく使う書き方をまとめてみた【随時更新】

https://coinbaby8.com/excelvba-flequently-programming.html

2020/7/30 追記『Udemy』向けExcelVBA講座を作成しましたので、ぜひご活用いただければと思います。関連講座最近よく『ExcelVBA(エクセルブイビーエー)』を使う機会があったので、個人的によく使っている『ExcelVBA』の書き方をまとめることにしました。プログラミングって書き方がいろいろあるので、これがベストとは限らないのでご注意くださいませ。ExcelVBA セル関係最後の行の位置(縦の位置)を取得したい本田長友香川とあったら香川の位置(3番目) を取得。Dim last_row as integerlast_row = Range("A100000").End(xlUp...

5. 見た目をきれいにする(使いやすくする)

職場のエクセルっていうことで、今回は見た目は度外視。

とはいえ、女の子にとって使いやすいようには配慮しまして、

使い方のメモというか、万が一使い方を忘れた場合でも、

コメントを見ればわかるようにはメモを残しておきました。

アオキ

そういう心配りも大事ですよねきっと、えぇ。

いざ職場の女の子にエクセルファイルを渡して、簡単に使い方を説明して、ボタンをポチッと押してもらいました。

10秒後・・・

職場の女の子

えっ、もう終わり！？アオキさん、すごぃ！

アオキ

えっへん

いやぁ、女性が目をキラキラさせながら見つめてくるのっていいですよね、心揺れますね、いえ、揺れませんとも、えぇ。

エクセルvba(マクロ)のサンプルコード

実際に作った『エクセルvba(マクロ)』の抜粋版です。

見る人が見れば怪しい箇所いろいろあると思いますが、
今の所問題なく動いています。

このままコピペしても動かないと思いますが、
雰囲気だけでも伝われば、と思います。

Sub 受注マクロ()

' 変数定義
Dim TargetDate As String
Dim SheetName As String
Dim objSheet As Worksheet
Dim r_end As Integer
Dim ws As Worksheet, flag As Boolean

Dim FilePath As String
Dim FileName As String
Dim FullPath As String
Dim myBook As String
Dim ac_book As String
Dim mySht As Worksheet

Dim MotoBK As Workbook
Dim SakiBK As Workbook
Dim Sht As Worksheet

Dim i As Integer
Dim Add As Integer

'シートに追記用
Add = 3

Start = "Start"
FilePath = Worksheets(Start).Cells(3, 3).Value
FileName = Worksheets(Start).Cells(7, 3).Value
FullPath = FilePath & FileName

myBook = "受注マクロ.xlsm"
Set SakiBK = Workbooks(myBook)

TargetDate = Worksheets(Start).Cells(9, 3)
SheetName = Replace(TargetDate, "/", "")

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'別シート選択で削除の防止
Worksheets(Start).Select

'アクティブシート以外を削除
    With Application
        '警告や確認のメッセージを非表示に設定
        .DisplayAlerts = False
        'シート名をチェックして、アクティブシートでなければ削除
        For Each mySht In Worksheets
            If mySht.Name <> ActiveSheet.Name Then mySht.Delete
        Next
    End With

'日付のシート作成
Worksheets(Start).Select
Worksheets.Add.Name = SheetName

ActiveWindow.Zoom = 55
Worksheets(SheetName).Cells(1, 1).Value = TargetDate
Worksheets(SheetName).Cells(2, 1).Value = "受注先"
Worksheets(SheetName).Cells(2, 2).Value = "商品"
Worksheets(SheetName).Cells(2, 3).Value = "受注"
Worksheets(SheetName).Cells(2, 4).Value = "受注○○"
Worksheets(SheetName).Cells(2, 5).Value = "売上日"
Worksheets(SheetName).Cells(2, 6).Value = "担当"

Range("A2:F2").Select
Selection.Font.Bold = True

'③記入したパスにある『売上管理エクセル』を開く
Workbooks.Open FullPath

ac_book = ActiveWorkbook.Name

Set MotoBK = Workbooks(ac_book)

Workbooks(ac_book).Activate

'④⑤全部のシートを1つずつコピー&ペースト
For Each Sht In MotoBK.Worksheets
    If Sht.Visible = True Then
        Sht.Copy after:=SakiBK.Worksheets(SakiBK.Worksheets.Count)
        ActiveSheet.Cells.Copy
        ActiveSheet.Cells(1).PasteSpecial Paste:=xlValues
        Application.CutCopyMode = False
    End If
Next Sht

Workbooks(ac_book).Activate

'⑤サーバーのエクセルを閉じる
ActiveWorkbook.Close savechanges:=False
    
ac_book = Empty

'⑥記入した日付の項目をindexシートにコピー
For Each objSheet In Worksheets
    objSheet.Select

    r_end = Range("B100000").End(xlUp).Row
    
    For i = 1 To r_end
    
        If Cells(i, 2).Value <> TargetDate Then
        Else
    
            Range(Cells(i, 3), Cells(i, 8)).Copy Worksheets(SheetName).Cells(Add, 1)
            Add = Add + 1
    
        End If
        
    
    Next i

r_end = Empty

Next


'書き方要チェック
Worksheets(SheetName).Cells(Add, 2).Value = TargetDate
Worksheets(SheetName).Cells(Add, 2).NumberFormatLocal = "yyyy""年""m""月""d""日（""aaa""）"""
Worksheets(SheetName).Cells(Add + 1, 2).Formula = "=" & Cells(Add, 2).Address & "-1"
Worksheets(SheetName).Cells(Add + 1, 2).NumberFormatLocal = "yyyy""年""m""月""d""日（""aaa""）""""まで"""
Worksheets(SheetName).Cells(Add + 2, 2).Value = "累計"

'⑦sum関数で合計を出す
Worksheets(SheetName).Cells(Add, 3).Formula = "=sum(" & Cells(3, 3).Address & ":" & Cells(Add - 1, 3).Address & ")"
Worksheets(SheetName).Cells(Add, 4).Formula = "=sum(" & Cells(3, 4).Address & ":" & Cells(Add - 1, 4).Address & ")"


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub