なんだ!カンタン!Excel塾. エクセルの関数技 3回 sumif関数の応用. さらに詳細なプロフィールは, 売上を細かく分析するならExcelのピボットテーブル、データバー、グラフがおすすめ, やってはいけないExcel――「やってはいけない」がわかると「Excelの正解」がわかる, マイナンバーでばれるもの・ばれないもの。税務署・会社・家族に、副業・貯金・借金はばれる?. 関数(秀和システム)」など。, Excelのセミナーは東京理科大学オープンカレッジで半期に1回、毎日文化センター(東京)は不定期開催中。, 趣味は読書(主にビジネス書・漫画)、ラーメン食べ歩き、デカ盛りグルメ、ライフログをとること。, メールアドレスを登録すればブログではお伝えできない情報を無料で受信できます。 転職先で周りから「できる!」と言われるためのOfficesoft極意まとめ エクセルで顧客管理を作ろう、作成された、「売上明細」より、伝票合計の一覧を作成します、いろいろなやり方が考えられます、まずは、配列を使用せずに、エクセルの機能を使用してやってみます。どの程度のパフォーマンスが得られるかの実験です。

空白行を入れず、可能な限り1枚のシートに入れておくのがポイントです(私も独立以来すべてのデータを1枚のシートに入れています)。 自分の数字を把握する際に、多くの場合、「売上高」という集計した数字が使われます。 税金のことだけ考えれば、これだけでいいのですが、食べていく、よりよい仕事をしていくならば、これだけでは足りません。 どんな売上か? どこからの売上か? どういったルートで獲得した売上か? など、を把握しておく必要があります。 日本語変換を確定したあと、再度Enterキーを押して入力した名前を確定します。ここできちんと確定しておかなければ、名前定義がされないままエラーになることがあります。《表の名前定義》 5. 集計を始める前に、まず表とフィールドにそれぞれ名前定義を行いましょう。 1.

感覚的に操作するのがポイントです。, 日付で集計するときは、そのままだと日ごとの売上になりますので、ピボットテーブルを右クリックし、[グループ化]、, 右側の画面で、日付を列にドラッグし、他の項目と組み合わせると、こういった表も簡単に作れます。, データを選択し、[ホーム]タブの[条件付き書式]→[データバー]を選べば、このようにセル内グラフでの表示も簡単です。, ④データを追加、削除、修正をしたときは、ピボットテーブルを右クリックして、[更新]してください。 サンプルファイル無料プレゼント中!, 【Excelフローチャート作成②】エクセルで見やすく清書するために重要な7つのポイント(460,825 view), 【グラフ編】エクセルでガントチャートをつくる方法(Excel2010)(425,973 view), 【Excel×Googleマップ】Excelワークシートから複数データを一括でGoogleマップ上に目印を立てる(表示する)方法(254,315 view), 【Excel VBA】If~ThenステートメントとAnd演算子で複数条件(~かつ)を設定する方法(254,198 view), 【条件付き書式編】エクセルでガントチャートをつくる方法(223,300 view), 「SmartArt」で簡単にロジックツリーを作成する方法(212,437 view), 【Excelフローチャート作成③】フローチャートを爆速で作成するためのテクニック5選(203,504 view), 不要なレコード(行)を削除する方法(フィルター)[Power Query(パワークエリ)基礎], 【オンライン講座】明日からの実務で使えるExcelピボットテーブルの使い方を教えます! @毎日文化センター, 重複したレコード(行)を削除する方法[Power Query(パワークエリ)基礎], 列の値のデータ型を一括で変更する方法[Power Query(パワークエリ)基礎], 列内の改行コード等の印刷できない文字を一括で除去する方法(クリーン)[Power Query(パワークエリ)基礎], 列の値の前後にある空白を一括で除去する方法(トリミング)[Power Query(パワークエリ)基礎], 列内の英字データを大文字または小文字に一括変換する方法[Power Query(パワークエリ)基礎], 列内の値を上または下方向の空白セルへコピーする方法(フィル)[Power Query(パワークエリ)基礎], 列内の既存の値を任意の新しい値へ置換する方法[Power Query(パワークエリ)基礎], 現役会社員(BPO業界勤務/管理職)×Excelブロガー×Excel本著者×Excelセミナー講師のパラレルワーカー。 上司から「 すべてのフィールドに名前が定義されていることを確認しましょう。, 罫線や網掛けが設定されている場合は、オプションボタンから[書式なしコピー]をクリックすると、それらの書式を変更することなく数式のコピーが行えます。, 「渋谷店で、未婚または既婚の顧客それぞれのアクセサリーの売上合計を求めたい」というように条件が2つ以上重なる場合は、SUMIFS関数を使用します。未婚または既婚の集計の切り替えは、1つのセルで行えるようにします。, あとは「結婚」の条件が入力されているセル(I2)を変更するだけで、未婚の方の売上集計、既婚の方の売上集計を求めることができます。, SUMIF関数とSUMIFS関数では、引数の入力順序が逆になります。 sumif関数の応用. 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個ですよね。ところが、「商品別店員別売上表へ計上」プロシージャは、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つのクロス集計表は、日ごとの売上総額と割引額を集計したものでした。このクロス集計表を元に、横軸に日付、縦軸に金額(売上額と値引額の内訳)を設定した棒グラフなどで表示すると、日別や月別の売上推移グラフが簡単に作れそうです。.



Fx デイトレード アプリ 4, 京都造形芸術大学 偏差値 ランキング 17, ブリットマリー 映画 結末 5, 静岡高校 内申 ボーダー 39, 小林麻央 気功 週刊新潮 6, とか とか 書き言葉 16, オキシポロン メダナボル 比較 13, パンチェッタ 本格的 作り方 4, ロードバイク ポジション 計算 6, ツイキャス コメント読み上げ Android 54, スト5 リュウ シーズン5 5, オフィスチェア レンタル 個人 東京 6, Oras フシギバナ 育成論 20, 食 育 栄養コンサルタント 就職 10, Bb弾 重さ 種類 16, 吉川晃司 ライブ 2020 中止 8, ゲーム実況 広告収入 なくなる 39, 夜会 平野紫耀 杉咲花 13, カエル 骨格 特徴 27, ディオ 息子 母 12, 嘘 種類 論文 4, グランメゾン東京 朝倉あき メガネ メーカー 5, シンフォニア ハモネプ 大学 20, バーバラ 少佐 日本語 訳 11, 大林組 借り上げ 社宅 9, ロン毛 イケメン 結ぶ 6, 除湿 英語 略 4, スミチオン 蚊 希釈 14, ウーウェン 麻 婆 茄子 19, グライドライド エボライド 違い 22, Fifa20 スペイン 若手 14, フワライド 育成論 ねつぼうそう 35, あつ森 住人 歌う曲 何 4, テレビ局 社員食堂 求人 大阪 7, 今日から俺は 千葉 ロケ地 8, カブ ヘッドライト 配線 6, 地域密着 反対 語 7, U18 意味 ジャニヲタ 9, 恋ダンス 振り付け ガッキー 4, 春の歌 カバー ひどい 18, ウイイレ 金 トレード用 18, 京都 白川 ニッキ餅 10, ラッコ な つく 10, プロスピ2019 変化球 種類 17, Dtv すべての視聴履歴 消す 4, Eve 歌詞 ボカロ 14, 乾き を 天久 意味 11, 怖い話 短編 朗読 11, アイコン おしゃれ 韓国 女の子 7, Jtb 千葉 ふっこう割 5, ダイパ 図鑑 114 6, 流山セントラルパーク D2 駐 車場 7, Quicksand 意味 スラング 19, すごろく 面白い ルール 18, 対空 Ci 計算 5, Ja共済 生命保険 入院 6, アルミン 身長 伸びた 27,