必要なところをコピーしてvbaに張り付け。もしくは、すべてをコピーしてCallで呼び出してもらえたらと思います。, シート名を変更したときに名前が重複するとエラーで止まってしまうと思いますが、重複した場合、①既存のシートを消して上書き or ②新しい名前を入力して保存するコードです。Call ChangeSheetName(“AAAAAAA”) で呼び出してください。 ※”AAAAAAA”の代わりにつけたい名前を入力。, 次回のコメントで使用するためブラウザーに自分の名前、メールアドレス、サイトを保存する。, ・32歳、1児のパパ ・旧帝国大学大学院卒 ・【理系】から経理に配属。 ・趣味は、エクセル、登山、フットサル、読書など, 【エクセル】で【計算】しないと気が済まないたちなので、いろいろなことについて、データを集めて計算していこうと思います！, Public Sub と Private Subの違い～～データ集計業務に即したマクロVBA, Excelの行列の非表示でグループ化の＋－が消える。。プラスの位置を上下反転させる方法！, 【簡単】VBAでIEの全リンク先を取得、クリックする方法～VBA(マクロ)でIE操作, 【マクロVBA】行や列の追加、削除、変更などの仕様変更に対応したプログラムの記述方法！. By following users and tags, you can catch up information on technical fields that you are interested in as a whole, By "stocking" the articles you like, you can search right away. 必要なところをコピーしてvbaに張り付け。もしくは、すべてをコピーしてCallで呼び出してもらえたらと思います。シート名を変更する際に重複した場合、名前を変更するマクロシート名を変更したときに名前が重複するとエラーで止まってしまうと思います 対象期間 2019/08/05 ～ 2019/09/15 期間③ 2019/09/15 ～ 2019/10/10 Sheet1のA列とB列にデータが入力されています。 A列は文字Eが重複していますが B列に重複している文字は存在しません。 VBAで重複データがあるか確認するには Dictionaryオブジェクトを使用します。 Dictionaryオブジェクトを使用するには
What is going on with this article? 日付期間の重複チェックパターン. VBA Sample Code to Totalize the Sales Data. 対象期間 2019/08/05 ～ 2019/09/15 期間① 2019/07/01 ～ 2019/08/31 Date【ﾃﾞｲﾄ】関数パソコンに設定されている現在の日付を取得するにはDate【ﾃﾞｲﾄ】関数を使用します。Date【ﾃﾞｲﾄ】関数には引数はありません。「Excel」関数のToday()関数と同じ働きをします。Date【ﾃﾞｲﾄ】関数の
期間⑦ 2019/08/15 ～ 2019/09/05, 重複しない以下のパターンでは、定石の条件式は成立しません。 例では日付にしてますが、DateTime型を使ってますので時間でも同様です。 重複するパターン. 期間④ 2019/08/01 ～ 2019/09/30 1 週間後の日付を取得したいときや、10 分前の時刻を取得したいときに使用します。, DateAdd(単位, 加算時間, 日時) 重複するパターンとして対象期間より短いパターン⑦を追加しました。 エクセルVBAで今日の日付・時間を取得する『DATE関数』『Time関数』『Now関数』を紹介します。データの登録・削除日を自動入力することで速度と正確性がアップします。是非とも習得したい基礎 … 期間⑤ 2019/07/01 ～ 2019/08/04
これによるチェック方法に変更点はありませんが、テストパターンが無いことで誤解を生じさせてしまったみたいです。, また、定石のチェック方法の導き方について書かれた記事がありました。 期間が重複しているかを判定する条件式の導出方法. VBAで売上データから2つのクロス集計表を作成します。1つ目のクロス集計表は、各店員別に各商品の販売数量と売上が分析できる集計表です。2つ目のクロス集計表は、当月の日別売上高と日別値引額を分析できる集計表です。, まずは、本記事で使用するサンプルファイルの紹介と、VBAコードを実行している1分動画（音声無し）を用意しましたので、宜しければご覧ください。, VBAサンプルコードの実行結果を確認するためのダウンロード用ファイルのご紹介です。実際にサンプルファイルをダウンロードしたあとに、標準モジュールを挿入してから、後ほどご紹介するVBAサンプルコードを転記してご利用ください。, 実際にVBAサンプルコードを標準モジュールに転記してから、実行させて確認できるダウンロード用ファイルです。, 「売上データ」シートには、集計元になる対象レコードが入力されています。項目は、「伝票番号」、「日付」、「販売担当者」、「商品①」～「商品⑤」までの各「品名」と「個数」、「値引額」です。, 1伝票ごとに一人の店員について、最大5個の商品の販売記録を入力可能です。また1伝票は1回の売買に相当しており、最終列には1回の取引における「値引額」を入力することができます。, 「商品別店員別売上詳細」シートには、5人の販売店員別に各商品を何個売上たかを分析できるクロス集計表が入力されています。さらに、各店員がそれぞれいくら値引したかも集計しています。, また、このクロス集計表は、商品の一覧も兼ねており、その商品名の横には売価が入力されています。, 「日付別売上表」シートには、ひと月分の日別売上高を、値引額を内訳として集計でいるクロス集計表です。項目は、「商品売上」（値引額除く）、「値引額」、「売上合計」（値引き込み）です。, 「売上データ」シートにある集計元レコードのデータを読み取り、2つのクロス集計表へ同時に集計させるVBAサンプルコードをご紹介します。, VBAサンプルコードの構成は、メインプログラムを含む5つのサブプロシージャと1つのファンクションプロシージャです。, VBAサンプルコードを、サンプルファイルの標準モジュールに転記することで、実際にExcelシート上でVBAサンプルコードを実行して結果を確かめることができます。, 5人の店員ごとに各商品別で販売数量と売上の集計結果を表示できるクロス集計表の集計結果です。, 例えば、佐藤さんはお店の中で一番高いスイカを、このひと月の間に81個も売っていることが分かります。その売上高は243,000円で、スイカの単価3,000円×81と一致していますよね。, 別の見方として、例えばこのひと月の間に一番値引を行ったのは、鈴木さんの1,710円だったことがExcelシートの20行目で確認できます。, 合計欄については、各担当ごとのひと月の総販売個数と総売上額がExcelシートの18行目で確認できます。また、商品ごとの総販売個数と総売上額は、Excelシート13, 14列目で確認できます。, 今回の売上データは、乱数で作成しましたので各商品、各店員ごとにそれほど開きが無く面白みのないデータですが、実際のデータを分析してみるといろいろと問題が洗い出されて、売上改善策を立てるのに役立てそうですよね。, 日付ごとの値引考慮なしの商品売上額（Excelシート2列目）、値引額（Excelシート3列目）、値引額を差し引いた売上合計（Excelシート4列目）を確認できるクロス集計表のVBAコード実行結果です。, 以上、確認してきました2つのクロス集計表は、元集計データが同じですので集計結果の切り口は違いますが、合計は当然合わなくてはいけません。そこで、これら2つのクロス集計表のVBA実行結果を比較確認してみました（下図）。, まず、図の赤枠で囲った合計値ですが、それぞれ割引総額を除いた売上額です。また青枠はそれぞれ値引総額であり、緑枠はそれぞれ割引額を引いた売上総額になります。, 今回のVBAサンプルコードでは、ワークシートオブジェクトをCodeNameプロパティで扱います。, 図の上から順に、「日付別売上表」シートのCodeNameプロパティを「Daily」、「売上データ」シートのCodeNameプロパティを「Data」、および「商品別店員別売上詳細」シートのCodeNameプロパティをFruitsとしました。, 2行目：Dim rngFrut As Range「商品別店員別売上詳細」シートにある商品リスト範囲をセットするためのRangeオブジェクト変数です。このrngFrutは、複数のプロシージャで使いますので、モジュール変数として宣言しました。, メインプログラム「売上集計」サブプロシージャの解説になりますが、結構長いので適当に区切りながら解説しています。, 8行目：Dim curSale As Currency集計元レコード1行分（伝票単位）の売上額を保持する変数です。データ型は通貨型を使用しています。, 9行目：Dim datDate As Date集計元レコードの該当する伝票日付を保持する変数です。データ型は日付型を指定しています。, 10行目：Dim strName As String集計元レコードは、1レコードごとに日付・担当者が分かれています。変数「strName」は、その処理中レコードの店員の名前を保持する変数です。, 11行目：Dim curVgen As Currency集計元レコードの処理中レコードの値引額を保持する変数です。金額を取り扱うのでデータ型を通貨型(Currency）にしています。, 13行目：Dim strFrut As String処理中の商品名を保持する変数です。, 14行目：Dim intQty As Integer処理中の販売個数を保持する変数です。, VBAコード21～27行目は、下図の通り2つのクロス集計表の集計先範囲を初期化（クリア）しています。VBAコード21～23行目で、日付別売上表の集計先データ範囲を、VBAコード25～27行目で商品別店員別売上詳細シートのデータ範囲をクリアしています。, また商品別店員別売上詳細シートのCells(21, 14)には、下図で示すように数式が入力されていますので、VBAコードでクリア処理する際の対象外にしています。, ちなみにこの数式が入力されているセルも、VBAコードでクリア処理して数式を入力することも可能ですが、今回はそうせずに常に数式が入力された状態としています。, 31行目：Set rngFrut = .Range(.Cells(3, 1), .Cells(17, 1))商品別店員別売上詳細シートのCells(3, 1)からCells(17, 1)までを、商品リストとしてRangeオブジェクト変数「rngFrut」にセットしています。, これより実際に「売上データ」シート上に入力されている売上レコードを1つ1つ繰り返し処理し、集計先となる2つのクロス集計表へ転記するVBAコードを解説していきます。, 35行目：lngERow = .Range("A" & .Rows.Count).End(xlUp).Row「売上データ」シートの最終レコードが入力されているExcel行番号を求めています。, VBAコード37～57行目では、For ～ Next文で「売上データ」シートにある集計元レコードの1つ1つを繰り返し処理しています。, 38行目：curSale = 0通貨型変数「curSale」は、集計元レコード1行ごと（1会計伝票ごと）の売上合計を保持する変数です。そのため各行処理前に、毎回保持している値をこのVBAコードでリセットしています。, VBAコード40～42行目は、処理中レコード行(r行)の「日付」（赤）、「販売担当者」（青）、「値引き」（緑）の値を、それぞれの変数に代入している処理です。, VBAコード45～54行目は、「売上データ」の商品①～商品⑤までのそれぞれの「品名」と「個数」（販売数量）に対する処理を、For ～ Next文で繰り返し処理しています。, 46行目：If .Cells(r, c) <> "" Then ここで「品名」が入力されているセルを調べ、品名が入力されていれば、VBAコード47行目以降を実行するようにしています。, 47行目：strFrut = .Cells(r, c)48行目：intQty = .Cells(r, c + 1)商品①～商品⑤の各「品名」と「個数」（販売個数）を、それぞれ保持する変数に代入しています。, 50行目：curSale = curSale + 果物売上(strFrut, intQty)対象レコードの売上額を集計するVBAコードです。ここで記載されている「果物売上」は、あとで詳述する「果物売上」ファンクションプロシージャです。このファンクションプロシージャは、引数として商品名と販売個数を渡せば、売上金額が戻ってくる関数です。, VBAコード47、48行目で品名（青字）と個数（緑字）を保持した変数「strFrut」と「intQty」を、「果物売上」ファンクションプロシージャに渡し、戻り値として得た売上額を加算集計しています。, 52行目：Call 商品別店員別売上表へ計上(strName, strFrut, intQty, curVgen, c)こちらもあとで詳述しますが、サブプロシージャ「商品別店員別売上表へ計上」を呼び出し、商品別店員別売上詳細シートへ商品①～商品⑤の情報を計算して転記しています。, 処理内容は、売上データシート上の商品①～商品⑤の各「品名」、「個数」の情報をもとに、商品別店員別売上詳細シート上の該当する担当者と商品リストの行に、販売数量と計算した売上金額を転記します。, 56行目：Call 日付別売上表へ計上(datDate, curSale, curVgen)集計元データの各レコード繰り返し処理の最後の処理になります。こちらもあとで詳述する「日付別売上表へ計上」サブプロシージャを呼び出し処理しています。, 処理概要は、下図のように引数として日付、1レコード分の売上合計、値引額を渡すと、該当する日付の行に売上と割引を転記し、内部で売上合計（値引額除く）を計算します。, 62行目：Call 商品別店員別売上表合計処理63行目：Call 日付別売上表合計処理2つのクロス集計表の合計処理になります。詳述はのちほど記載しておりますが、VBAコード62行目前までの処理状況は下図の通り合計欄はまだ空欄です。, 上図の状況のあと、VBAコード62, 63行目が実行されることにより、合計欄が集計されます。, 売上データシート上の各レコードのデータを読み取った値を受取り、商品別店員別売上詳細シートへ集計させるサブプロシージャです。, 69行目：Private Sub 商品別店員別売上表へ計上(ByVal vName As String, ByVal vFrut As String, ByVal vQty As Integer, ByVal vVgen As Currency, ByVal vCol As Integer)サブプロシージャの宣言部になります。引数は、以下の通り5つあります。, 70行目：Dim rngTrgt As Range75行目：Set rngTrgt = rngFrut.Find(vFrut, lookat:=xlWhole)商品リストの中の商品と、引数で受け取った「商品名」が一致した時に、そのセルをRangeオブジェクトとして保持するオブジェクト変数をVBAコード70行目で宣言しています。, 実際の変数セットは、VBAコード75行目で実行しており、商品リスト範囲（rngRfut)からFindメソッドで引数「vFrut」と一致するセルをrngTrgtにセットしています。, VBAコード79～84行目は、75行目でrngTrgtオブジェクト変数にセットされた中身をチェックして、その結果で処理を分けています。, 上図赤の部分は、商品リストの中に引数で受け取った商品名が無かった場合の条件文です。rngTrgt Is Nothingは、rngTrgtオブジェクト変数の中身が無い、すなわちVBAコード75行目の処理結果が何も得られなかったことを表しています。, VBAコード79行目の条件に合致しない場合、すなわちVBAコード75行目の処理の結果、rngTrgtに値がセットされた場合は、VBAコード83行目（下図青字）が実行されます。, VBAコード87～93行目は、商品別店員別売上表のデータ書込列を判定しています。引数vNameは担当者名であり、そのvNameをSelect文の条件判定に用いることにより、担当者ごとにデータが振り分けられる仕組みです。, 96行目： .Cells(intWRow, intWCol) = .Cells(intWRow, intWCol) + vQty書込み行（intWRow)と書込み列(intWCol）が決まりましたので、ここでは該当する担当者別に、販売数量を集計する処理をしています。イメージとしては、以下の図のような感じになります。, 97行目：.Cells(intWRow, intWCol + 1) = .Cells(intWRow, intWCol + 1) + .Cells(intWRow, 2) * vQty各担当者ごとの商品別売上高を加算集計するVBAコードです。下図の青文字は、各商品の売価が入力されている範囲で、Excelシートの2列目にあります。これとVBAコード96行目でも利用した販売個数「vQty」を乗じることで、商品ごとの売価が計算できます。, VBAコード99～101行目は、商品別店員別売上表20行目の値引額を集計するコードです。メインプログラムで呼び出される際に、引数として渡された処理対象列を、VBAコード99行目のIf文で判定し、列番号が4行目の時だけ値引額を売上別店員別売上表に加算集計します。, ここで列番号が4の時にだけ割引額を加算集計している理由ですが、売上データを確認すると「割引額」は1レコードにつき１個ですよね。ところが、「商品別店員別売上表へ計上」プロシージャは、1レコードにつき商品①～商品⑤までの最大5回まで実行される可能性があります。, そうなると、VBAコード99行目のIf文が無ければVBAコード100行目によって、割引額の加算が最大5回まで加算集計されてしまいます。まさに重複計上を起こしてしまうのです。, そこで、考え方の1つとして、「1つのレコードには必ず商品①（処理列番号4）はあるはずだから、列番号4の処理の時だけ同時に値引額の集計もしてしまおう」と考えたわけです。, 果物の種類と販売個数を引数として受け取り、売り上げを返すファンクションプロシージャの解説です。, 108行目：Private Function 果物売上(ByVal strFrut As String, ByVal intQty As Integer) As Currency引数strFrutは「商品名」、intQtyは「販売個数」です。戻り値のデータ型は、通貨型(Currency)としました。, VBAコード110行目は、既に解説しましたVBAコード79行目と同じ考え方になります。ここで引数の商品名が商品リストの中にない場合、If文の条件式がTrueとなり、つづくVBA111行目の処理が行われ、戻り値0を返します。, 一方、引数で渡された商品名が商品リストの中にある場合、VBAコード113行目が実行され、戻り値として売上額（商品の値段×個数）が返されます。, 119行目：Private Sub 日付別売上表へ計上(ByVal vDate As Date, ByVal vSale As Currency, ByVal vVgen As Currency)サブプロシージャの宣言部です。引数は、日付、1レコード分の売上合計、値引額です。, 121行目：Dim rngTrgt As Range124行目：Set rngDate = .Range(.Cells(2, 1), .Cells(32, 1))VBAコード121行目は、日付別売上表シートの日付範囲をセットするためのオブジェクト変数です。VBA124行目でそのオブジェクト変数に、図の日付入力範囲をセットします。, VBAコード128～136行目は、前でセットした日付範囲の中に、引数で渡された日付が一致するか否かで処理を分けているIf構文になります。VBAコード128行目のIf文の条件式の考え方は、これまで出てきたVBAコード79行目と110行目と同じになります。, VBAコード132～134行目は、引数と一致した日付をセットしたrngTrgtからのOffsetプロパティによって、「売上額」（値引額控除前）（赤）、「値引額」（青）、「売上額」（値引額控除後）（緑）を加算集計しています。, VBAコード143行目のFor文は、クロス集計表の行方向の繰り返し処理をします。VBAコード144行目のFor文は、クロス集計表の列方向の繰り返し処理をしますが、Step 2が記載されていますので1列置きに処理されます。, VBAコード145と146行目は、それぞれ商品別の販売数量と売上を加算集計しています。, VBAコード151～153行目は、列方向の繰り返し処理を用いてクロス集計表18行目の合計欄を集計しています。またVBAコード156行目は、青枠で囲った値引額総額を表示するセルを集計します。, 見づらいですが、図の青網掛けで囲った範囲を指定したワークシート関数Sumを使い集計結果を算出しています。, VBAコード163～165は、列方向の繰り返し処理を使い、クロス集計表33行目の3つのセル（下図赤枠）の合計値を集計します。, 集計元の売上データを2つのクロス集計表に集計させるVBAサンプルコードについてご紹介しました。1つ目のクロス集計表は、集計元の売上データから、店員別に商品ごとの販売個数と売上額を集計し、さらに各店員ごとの総割引額（1か月にいくら値引したのか）も集計したものでした。, この1つ目のクロス集計表により、例えばどの店員がどの商品を1番売上たか、どの商品が一番売り上げが悪かったのかなどの分析に使えそうです。また、あらかじめ店員ごとの割引額を設定しておいて、月末締めの集計時に割引総額がきちんと制限内に抑えられているかなどの管理にも使えそうです。, もう1つのクロス集計表は、日ごとの売上総額と割引額を集計したものでした。このクロス集計表を元に、横軸に日付、縦軸に金額（売上額と値引額の内訳）を設定した棒グラフなどで表示すると、日別や月別の売上推移グラフが簡単に作れそうです。.