プレス技術研究所VBA講座

上図のような表の、支店ごとの小計を算出したい時には、支店を基準に「並べ替え」を
するであろう。

上図のように、支店を基準に並べなおしたら、セルH26に
「=IF(C26=C27,N(H25)+G26,TEXT(N(H25)+G25,"#,##0"))」と入力し、下方にコピーする。

集計できたので、きれいに表示を整列すればよい。

上図の様に、支店小計列を選択して、「セルの書式設定」で[ユーザー定義]の[種類]に
「;;;@」と入力し「OK」をクリックすると、文字列だけが表示されるので下図のようになる。

「=IF(C26=C27,N(H25)+G26,TEXT(N(H25)+G25,"#,##0"))」は「支店」が下の行と同じなら
数値として返し、異なる場合は文字列として返す。
N関数は、数値の場合はそのまま数値、文字列の場合は「0」を返す。
支店が変わる行は文字列で返すので、N関数の結果がゼロとなり、次の行からは累計がゼロから加算
される。

上図の様に、都道府県別に連番を振りたい場合、
セルB3に「=IF(E3="","",IF(E2＜＞E3,1,N(B2)+1))」と入力し、下方にコピーすると
それぞれに連番を振ることができる。

又、下方にデータを追加した場合、C2からF11セルまで選択して「並べ替え」をする。

上図のように、[優先されるキー]に「都道府県」を選択する。

「並べ替え」が完了すると、連番が振られている。

上図のように、セルB4に「=SUBTOTAL(2,$H$4:H4)」と入力し、それを下のセルまでコピーする。
SUBTOTAL関数の第一引数を「2」と指定する。「2」は集計方法の[COUNT]で表示されたデータを
数える。第二引数には、「$H$4:H4」のように下にコピーするために、先頭セルだけ絶対参照にする。

上図のようなフィルター結果になり、セルB5に「1」と表示される。
尚、数値の場合は集計方法を「2」に指定したが、文字データの場合は「3」を指定しよう。
また、B列はフィルター範囲外の方が良い。

上図のセルG2に「=SUBTOTAL(9,G4:G11)」と入力すると、G4からG11までの、表示されている
金額だけの合計が算出される。
だから、オートフィルタで合計するデータを指定すれば、その表示されたデータだけを集計できる。

SUBTOTAL関数の引数は、「=SUBTOTAL(集計方法,参照範囲)」と指定する。
セルに「=SUBTOTAL( 」と入力すると、集計方法が表示される。
ここでは「9」の「SUM」を選択している。

上図の様に、B3からG11セルまでを選択し、[データ]－[フィルター]をクリックする。

上図の様に、設定されたフィルターの支店の「▼」をクリックすると、ダイアログが出るので
[すべて選択]のチェックを消して、[大阪]にチェックを付けて[OK]を押す。

結果、上図のように「大阪」のデータがフィルターされて、合計データがG2セルに入った。
SUBTOTAL関数は、現在の「参照範囲」に表示されているデータを集計する関数である。
尚、フィルターの解除は、再度上部のリボンの[フィルター]をクリックすればよい。

上図のように顧客名簿のB3セルに「No.」を入力すると、顧客データが表示されて、「Jump」文字が
表示される。その文字をクリックすると、その「No.」の記入されている行にカーソルが
ジャンプするものである。B3セルに「番号」が入力されていないと、何も表示しないし移動もしない。
セルC3に「=IF($B$3="","",DGET($B$5:$F$13,C2,$B$2:$B$3))」と入力すると、「会社名」が
表示される。それをD3セルからF3までにコピーすると、すべてのセルにデータが表示される。

上図のセルC4に、「=IF(C3="","",HYPERLINK("#C"&B3+5,"Jump"))」と入力すると、
「Jump」と表示される。但し、B3セルに「番号」が入っている時だけに表示される。
HYPERLINK関数は、同一シートのセルにジャンプする場合は、「HYPERLINK("#A1","表示文字")」
と指定する。
尚、「HYPERLINK("#C"&B3+5,"Jump")」の「B3+5」は、B3セルが示している値「3」が
「8」行目なので、そのセルにジャンプするには、B3セルの値に「+5」しなければ8行目にならない。
尚、同じファイルの他のシートのセルへジャンプする場合は、
「=HYPERLINK("#Sheet12!C3","Jump")」と記入する。

上図のH3セルに、「会社No.」を入力するとH5からI8に、そのデータが縦書きで表示される。

上図のセルI5に、「=IF($H$3="","",DGET($B$2:$F$10,H5,$H$2:$H$3))」と入力すると、
「No.3」の会社名が表示される。それを下方にコピーすると、すべてのデータが表示された。

尚、H3セルには、「データの入力規則」を設定するとよい。[データ]－[データツール]－[データの入力規則]
をクリックする。

ダイアログが表示されるので、[リスト]と[元の値]の位置を入力して[OK]をクリックする。

上図のセルG15に、「=IF(C15="","",DMAX(B2:G10,G14,C14:C15))」と入力すると、
表中の「東京支店」の最大売上金額の値が抽出された。

セルG15の最大売上高を利用してB15セルに、
「=IF(C15="","",DGET($B$2:$G$10,B14,$G$14:$G$15))」と入力すると、
その日付が表示される。その計算式を、D15からF15までにコピーすると、「東京支店」の
最大売上データのすべてを抽出することができる。
尚、DGET関数では、同じ値の該当データが複数ある場合は、「#NUM!」エラーが表示される。

上図のセルC3に、「=IF(B3="","",DGET(B5:C13,C2,B2:B3))」と入力すると、電話番号が
表示された。
指定した内容に一致するデータを表示するには、DGET関数を利用すると簡単である。
データベース関数では、先頭一致で条件指定ができるので、すべての文字を入力する必要がない。
「＊会社名＊」のように、会社名の一部を入力するだけでデータを抽出できる。

上図のセルG2に、「=OFFSET(G3,COUNT(F:F),0)」と入力すると、G列の最下行のデータが
表示された。
OFFSET関数の引数は、「=OFFSET(基準セル,行の移動数,列の移動数)」で基準セルから
指定数分だけ行や列を移動した位置の、セルの値を取り出すことができる。
COUNT関数で、F列に入力されている数値の件数を数えて、G3セルからの「行の移動数」
として指定すれば、常に最下行のデータを取り出せる。

上図が、1週間ごとに小計行を挿入したものである。

上図の様に、1週間ごとの小計行を挿入するには、C列の様に週の列が必要である。
セルC3に「=WEEKNUM(B3)－WEEKNUM("2024/5/6")+1」と入力して、それを下の行に
コピーすれば、図のように月の何週目かが表示される。

上図の様に、不必要な合計行を消去しておくとよい。
次に、セルB2からH10までを選択して、[データ]－[アウトライン]－[小計]をクリックする。
[集計の設定]で[グループの基準]を「週」にして、[集計フィールド」を「支店」と「金額」に設定する。

上図の様に、週ごとの集計が作成された。週の合計は、SUBTOTAL関数で行われている。
表内の修正は右クリックで行える。

上図の様に、支店ごとの小計行を挿入したい場合、

セルB2からG10までを選択して、[データ]－[アウトライン]－[小計]をクリックする。

[集計の設定]が表示されるので、「支店」「合計」「金額」を選択して[OK]を押す。

上図の様に表示される。上部ボタンで集計が変わる。

尚、セルG6を見てみると「=SUBTOTAL(9,G3:G5)」と入力されている。

上図のように、セルB2からG10までを選択して、[挿入]－[ピボットテーブル]をクリックする。
[テーブルまたは範囲から]をクリックする。

ダイアログが開くので[テーブル/範囲]－[新規ワークシート]－[OK]をクリックする。

[ピボットテーブルのフィールド]が開くので、[日付]を[フィルター]にドラッグする。

A～Bセルに、「日付」が入る。続けて[支店]を[列]にドラッグする。

続けて、[商品]－[数量]－[金額]をそれぞれドラッグする。右上の[X]をクリックして終わる。

出来上がった表の、「合計/数量」を「数量」だけの表示にしたいので、「合計/数量」を
クリックして出た、ダイアログの「値フィールドの設定」をクリックする。

「値フィールドの設定」が表示されるので、不要なら「合計/」の文字を消す。
「集計方法」は「合計」で良いので[OK]を押して終わる。

上図の様に、「合計/数量」が「数量」に変更された。
右側に表示されているのは「フィールドリスト」であり、シートのどこかをクリックすれば非表示になる。
色付きのセルを右クリックすると、ダイアログが表示されるので、その中の「フィールドリストを表示する」
をクリックすると表示される。

上図の「日付」検索の「▼」をクリックすると、検索する日付リストが表示される。
「列ラベル」の「▼」で営業所、「行ラベル」の「▼」で品名リストが表示される。

上図の様に、罫線を引きたい場合は、部分を選択して「セルの書式設定」の「罫線」で引く。

上図のセル J4に「DCOUNT(B3:G11,E3,I3:I4)」と入力すると「6」とセル I4の日付条件
「>=2024/5/20」に該当する件数を表示した。
DCOUNT関数の引数は、DSUM関数の引数と同じで、
「=DCOUNT(データベース範囲,集計フィールド,条件範囲)」である。
尚、「集計フィールド」は「セル番地」でも「"数量"」でもよい。

集計テーブルを作成するには、まず上図のように基本のDSUM関数の集計を作成する。
セル I6に「=DSUM(B2:G10,G2,I2:J3)」と入力する。

次に、上図のようにセル I6に接続した、必要な集計項目を入力する。

次に、セル I6から L9までを選択して、[データ]－[What-If分析]－[データテーブル]をクリックする。

ダイアログが出るので、[行の代入セル]に「$J$3」を[列の代入セル]に「$I$3」を入力して[OK]を押す。

上図の様に、すべての項目が集計された。セル I6のDSUM数式によって集計されたのである。

上図の様に、セル J7には「{=TABLE(J3,I3)}」となっている。他のセルも同じである。
尚、セル I6の数値が目障りなら、非表示にするとよい。基本式が入っているので、消去はできない。

上図の I列と J列の様に、複数の商品を一度に集計したい場合、J3セルのように
「=DSUM($B$2:$G$10,"金額",I2:I3)」と入力すると、「皿」の合計金額を求められるが、
その関数を下方にコピーしても、「カップ」「トレー」は求められない。
それを求めるには、「データテーブル」機能を使用する必要がある。

上図のように、セル I3から J5の品名の部分を選択し、[データ]－[What-If分析]をクリックし、
[データテーブル]をクリックすると、

ダイアログが表示されるので、[列の代入セル]に「$I$3」を入力し、[OK]を押す。

すると上図の様に、すべての商品に対する合計金額が表示された。
J4セルを見てみると、「{=TABLE(,I3)}」と表示されている。尚、J5セルも同じである。

上図のセル F15に「=DSUM(B3:G11,"金額",C14:D15)」と入力すると、「1,500,000」と表示された。
これは、セル C14:D15の条件「カップ」の「大阪」の「合計金額」を集計したものである。
「数量」の E15セルには、「=DSUM(B3:G11,"数量",C14:D15)」と集計フィールドは、「"数量"」に
なっている。

上図のセル F18に「=DSUM(B3:G11,"金額",C17:C18)」と入力すると、データベース範囲の
すべての「カップ」の合計金額が計算される。

上図のセル F21に、「DSUM(B3:G11,"金額",C20:D22)」と入力すると、「東京」と「大阪」の
すべての「皿」の合計金額が計算される。

上図のセル F25に「=DSUM(B3:G11,"金額",C24:D26)」と入力すると、「大阪」の「カップ」と
「東京」の「トレー」の合計金額が計算される。

但し、上図のように条件範囲に空白セルを含めると、「すべて」のデータが該当するので注意が
必要である。

上図のセル D13に「=DSUM(B2:G10,E2,C12:C13)」と入力すると、「220」と表示された。
これは、セル C13に「=皿」のように「皿」の前に「=」を入力することによって、「完全一致」
が指定されて、品名が「皿」のものだけが集計された。「＝」が無いと、皿の文字列の後部に
何かが書かれている品名でも集計さる。「＝」を商品名に記入するためには「'=皿」と入力する。

上図の H3セルに「=DSUM(B2:E8,"数量",G2:G3)」と入力すると、「25」と表示された。
これは、「支店大阪」の「数量」合計である。
DSUM関数の引数は、「=DSUM(データベース範囲,集計フィールド,条件範囲)」の様に指定
する。

尚、「データベース範囲」には上図のように「名前」を定義してもよい。

上図の様に、「=DSUM(DB,4,G2:G3)」と「データベース範囲」を「DB」と指定した「名前」で、
「集計フィールド」を表の左から数えた「番号」の「４」と「数量」の代わりに入力してもよい。