仕事効率化

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


Warning: Trying to access array offset on value of type bool in /home/mochaccino8/coinbaby8.com/public_html/wp-content/themes/mag_tcd036/functions/short_code.php on line 36

Warning: Trying to access array offset on value of type bool in /home/mochaccino8/coinbaby8.com/public_html/wp-content/themes/mag_tcd036/functions/short_code.php on line 36

2020/7/30 追記

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

関連講座

僕の会社ではなぜか月に1回、土曜日も出勤日になっていて、すこぶる働きたくない気持ちになっとります。

日本人は本当に働きすぎですよね、週休3日制くらいにたっぷり休んだほうが生産性上がると思ったりします。

さて、そんな働きすぎな日本人が仕事で一番使っているソフトといえば、やっぱり『エクセル』ですよね。

うちの会社でも、

  • 同僚も、
  • 営業も、
  • 総務も、
  • 部長も、
  • 社長も、

みんながもれなく使っているソフトといえば、

オフィスソフト(エクセル、ワード、メール)だろうなと思います。

で、エクセルの真骨頂はやっぱり『マクロ(vba)』だと思います。

『エクセル』でできる事ならほぼほぼなんでも自動化できるので、

頑張ってプログラム書いて自動化させて、

ボタンつけてポチッとやれば一瞬で終わっちゃうんだから。

本当に便利な代物だと思います。

そんな『エクセルvba(マクロ)』をつくったら職場の女の子に、

職場の女の子
すごいっ!

と言われた時のお話です。

Sponsored link

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

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

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

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

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

というものでした。

よくよく話を聞くと、

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

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

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

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

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

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

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

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

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

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

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

なんでも今は、

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

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

アオキ
そ、それは大変ですね・・・
アオキ
それ毎日やってたんですね・・
職場の女の子
そうなんですー(涙)

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

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

Sponsored link

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

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

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

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

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

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

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

参考記事

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

今回のリクエストは、

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

という事でした。

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

2. 細かく分解する

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

今回の場合は、

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

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

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

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

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

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

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

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

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

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

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

こんな感じですね。

3. グループに分ける

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

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

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

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

4. 順番をつける

今回の場合、

  1. 職場の女の子のパソコンに保存しているエクセルvba(マクロ)を開く

  2. ファイル名や日付を入力した後に、ボタンを押す

  3. 記入したパス(サーバー)にある『売上管理エクセル』を開く

  4. 全部のシートをまるっとコピーする

  5. 職場の女の子エクセルに丸ごと貼り付ける(サーバーのエクセルは閉じる)

  6. 記入した日付の項目をindexシートにコピーする

  7. 全部コピー終わったら、sum関数で合計を計算する

  8. 不要なシートは全て消す。

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

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

関連記事

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

まとめ

手作業でやるとなかなか面倒だしミスも起こりそうな事を、

ボタンひとつでできちゃうようにするというのが『エクセルvba(マクロ)』。

地味に凄いやつですよ本当。

毎日触るエクセルで、

エクセル戦士
毎日同じような事やってるなー

と思う事があったら効率化のチャンス。

ちょっと頑張って『エクセルvba(マクロ)』を書いて、ボタン1個で処理を終わらせて、

ぜひ土曜も出勤なんて事にならないようにしたいですね。

アオキ
というか、売上管理をエクセルでやってるっていううちの会社も結構アナログですな・・

『エクセルVBA・分析・統計』関係ではこんな記事も読まれています。

1. 【ExcelVBA】初心者向け講座をリリースしました。

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

3. 【Excel(エクセル)】よく使う関数をまとめてみた(組み合わせもあり)

4. 【ピボットテーブル】の使い方をわかりやすくまとめてみた【初心者向け】

5. 【Excel(エクセル)】確率・統計でよく使う関数をまとめてみた【随時更新】

6. 【分散】【標準偏差】とは わかりやすくまとめてみた【ExcelとPython】

7. 【データドリブンマーケティング】の本を頼りにデータ分析力を高めていくべし【統計学】

8. 【正規分布】とは わかりやすくまとめてみた【ExcelとPython】

9. 【確率】当たりがでる確率を計算する方法【二項分布】【Excel/Python】

10. エクセルマクロで勤務表の自動チェックを依頼され2時間でつくった話

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

アオキ
ツイッターでも記事ネタ含めちょろちょろ書いていくので、よろしければぜひフォローお願いしますm(_ _ )m

アオキのツイッターアカウント


関連記事一覧 (一部広告あり)

最近の記事

アーカイブ

  1. CG関連

    【P5.js】初心者向けの動画をリリースしました(プログラミング×数学物理)【U…
  2. 学び・生涯学習

    アンケートのお願い Udemy 次Laravelシステムとフロントについて
  3. バックエンド

    【Laravel第4弾】Vue.js3(CompositionAPI+Scrip…
  4. バックエンド

    【Laravel(PHP)】でできる事をわかりやすく(ざっくりと)まとめてみた【…
  5. バックエンド

    【Laravel】マルチログイン対応ECサイトの講座をリリースしました【Udem…
PAGE TOP