Q&A
やりたいのは、「実績データ（A16:B27)を使って、黄色に塗られたセルに値を書き込み(転記)したい」という事でしょうか？ そして、値を書き込まない黄色のセルが有るかもしれないという条件付きだと。
前提・実現したいこと
経理システムから出力されるデータを
毎月の集計リストに転記したいのですがうまくいきません。
実績データは科目（売上、外注費、仕入高）と商材（製品Ａ、製品Ｂ、製品Ｃなど）
がＡ列内に一緒に表示され、出力方法を変更することはできません。
また、表示される項目は実際に実績が発生したデータだけなので
外注費に製品Ｂがない月があるなど表示される場所は固定ではありません。
商材、科目をきちんと確認してデータを転記したいのでRangeの位置を指定せずに
科目や商材を確認してデータを転記するには何を使えばよいのでしょうか？
FINDやVLOOKUPなどを使ってみましたがうまくいかないため
先輩方のお知恵を拝借できればと思います。
よろしくお願いいたします。
`
実際の実績データのＢ列には合計値が入っています。添付ファイルの例だと
売上 666（111+222+333）
外注費 1665（444+555+666）
仕入高 2664（777+888+999）
また、システムから毎月出力されるため実績データは別ファイルで保存されています。
毎月出力されるデータから転記したいのでVBAを利用して自動転記したいと考えています
はい。実績データはシステムから出力されるのですが、実績がない科目、商材もあるため集計データにあっても実績データには該当データがない場合もあります。
実績データ（A16:B27)はサンプルのため同じsheet内にありますが、実際は別の名前のExcelファイルから参照してくる形になります。
セルに書く関数式で対処することは困難でしょう。マクロ(VBA)を使うのが現実的だと思います。
毎回実績データを出力しなければならないので構想としては、集計データのひな型があるブックにVBAで書き、実行ボタンを押したら実績データを読み込んでデータを転記する方法をとりたいと思っています。商材と科目が混在しているためどうやって見分けていいかが解らず試行錯誤しております。
実績データは、A列に科目と商材が入っていますが、科目はB列が空白になっている事で判断できるのではないでしょうか？
すみません。サンプルでは記載し忘れましたが科目のＢ列にも実際は商材の合計値が入っております。例えば売上のＢ列には666（製品Ａ，製品Ｂ,製品Ｃの合計）が入っております。正しく記載できておらず申し訳ありません。
じゃぁ、集計リストのA列に出てくる名称が商材名、B列に出てくる名称のうち"粗利"以外のものが科目名、で判断するしかないですかね。
新たに商材が加わった（扱う商材の種類が増えた）場合、集計データの雛型が先に修正(更新)されるのですよね？ 別の言い方をすると、実績データに現れる商材は、既に集計データの雛型に含まれていることが保証されているのですか？
そうなんです。ただ、集計列のＡ列はセル結合されており、実績データのＡ列は商材名と科目名が混在しているという状態です。
はい。集計データに含まれる商材の売上、外注費、仕入高を実績データから抽出してきたいです。集計データにあっても実績データに表示されていない科目はその月の実績がないとみなし、0を代入する予定でいます。
回答2件
0
ベストアンサー
目的の動作を実現させるためには、まずは実績データのA列に混在する「科目」と「商材」を見分ける必要があります。
まさにttpさんがお困りの点ですね。
これは提示いただいたサンプルを見る限り「B列が空白か否か」で判断できそうです。
具体的にどう利用するかというと、例えば
①C16セルに
=IF(B16="",A16,C15)
という式を記述して、実績データの最後までコピーします。
この式は「B列が空白ならA列の値、空白でなければ前行と同じ値」を返すものです。
これでC列にそれぞれの科目が出力されたと思います。
②同様にD2セルに
=IF(A2="",D1,A2)
という式を記述して、転記する領域(D13まで)にコピーします。
これは①とほぼ同じで、連結セルの裏に隠れて空白となっている商材名を埋める式になります。
タイトル記載のお悩みの点をこれで解決します。
③これらを利用して目的のデータを取得します。
C2セルに
=SUMIFS($B$16:$B$100,$C$16:$C$100,B2,$A$16:$A$100,D2)
という式を記述し、転記したいセルにもそれぞれコピペで貼り付けます。
これは実績データを(B16～B100)とし、実績のC列(科目列)と科目(B2)が一致し、かつ実績のA列(商材名列)と商材名(D2)が一致しているものの合計を取得しています。
※データ量が変動するようなので、検索範囲は100行目までと多めに取っています。
以上で目的の結果が得られるのではないかと思います。
タグにVBAが含まれていますのでVBAも視野に入れるのなら、同様の処理をマクロ化してもいいですし、最終的に不要な値はセルに書きださずに処理する方法もあると思います。
いずれにしても、科目と商材の判断など、考え方は似たようなものになるかと思います。
参考になれば幸いです。
VBAでの実装（追記2018/12/05 13：56）
VBAでの実装を想定しているとのことでしたので、その前のコメントに記述した「関数(Function)の作成」について少し掘り下げてみたいと思います。
なお、「実績データB列の空欄判定では科目の判別ができない」ということも踏まえて、以下のような流れを考えてみました。
===
①関数の引数として、実績データ範囲と、商材名、科目のセルをそれぞれ取得する
例えば
・関数名：fncGetValue
・引数１：実績データ範囲(科目・商材名と数値の２列のセル範囲)
・引数２：商材名(セル)
・引数３：科目(文字列)
とすると、
=fncGetValue(A16:B100, A2, B2)
のように指定する
②科目をリストアップする
商材名セルが連結セルなので、連結されている行数分だけループ処理を行い、右隣にある科目列から科目名を取得する("粗利"は含めない)
⇒科目のリストを格納するのにはDictionaryオブジェクトの利用をお勧めします。
DictionaryにはExistsという存在チェックが用意されていますので、登録されているキーワードの確認が簡単になります。
※Dictionaryオブジェクトを利用するには「ツール」-「参照設定」でMicrosoft.Scripting.Runtimeを追加するか、コード中でCreateObjectする必要があります。
③実績データを１行ずつループ処理
④実績データの１列目の値が科目ディクショナリに登録されているかチェック
・④-a ⇒登録されていれば「科目」
次の科目が見つかるまで、ここで見つけた科目が実績データの科目となる
・④-b ⇒登録されていなければ「商材」
引数で指定された科目・商材と一致する場合は2列目の値を返して処理終了。
一致しない場合は次行の処理へ（③へ戻る）
===
といった流れで、引数に合致する値を返せると思います。
上記をもとにサンプルも作成してみました。
Function fncGetValue(vrData As Range, vrItem As Range, vrKind As Range) Dim ret As Double '戻り値 ret = 0 Dim i As Integer '実績データの領域チェック If vrData.Columns.Count < 2 Then fncGetValue = "#Data Err" Exit Function End If '科目ディクショナリ '↓Windows.Scripting.Runtimeを参照設定に追加していない場合はこちら 'Dim dicKinds As Object '科目ディクショナリ 'Set dicKinds = CreateObject("Scripting.Dictionary") '↑ '↓Windows.Scripting.Runtimeを参照設定に追加している場合はこちら Dim dicKinds As Dictionary '科目ディクショナリ Set dicKinds = New Dictionary '↑ '商材セルの結合サイズから科目ディクショナリを作成 For i = 1 To vrItem.MergeArea.Rows.Count If vrItem.Cells(i, 2) <> "粗利" Then dicKinds.Add vrItem.Cells(i, 2).Value, 0 End If Next Dim strKind As String '実績データの科目 Dim strVal1 As String '実績データのA列セル値 Dim strVal2 As String '実績データのB列セル値 '対象範囲をループ処理 For i = 1 To vrData.Rows.Count '値取得 strVal1 = vrData.Cells(i, 1).Value 'A列 strVal2 = vrData.Cells(i, 2).Value 'B列 If dicKinds.Exists(strVal1) = True Then '科目ディクショナリにある：「科目」 strKind = strVal1 Else '科目ディクショナリにない：「商材」 '今回の抽出対象化判定 If strKind = vrKind.Value And strVal1 = vrItem.Value Then ''対象科目の対象商材なら戻り値に加算 'ret = ret + Val(strVal2) '見つけたら値を返して終了 ret = Val(strVal2) Exit For End If End If Next fncGetValue = ret End Function
これをVBAで標準モジュールとして記述します。
使用する際は、例えば
・売上の値(C2セル)には=fncGetValue(Sheet1!A$16:B$100, A2, B2)
・外注の値(C3セル)には=fncGetValue(Sheet1!A$16:B$100, A2, B3)
・仕入の値(C4セル)には=fncGetValue(Sheet1!A$16:B$100, A2, B4)
のような式を記述することで、それぞれ目的の値が出力されると思います。
コメント多めにしておきましたが、不明な点等あればご確認ください。
参考になれば幸いです。
投稿2018/12/04 07:05
編集2018/12/05 04:57
総合スコア3013
=SUMIFS となって余計なsが
SUMIF関数の複数条件版であるSUMIFS関数です。
「科目」と「商材」をそれぞれ判断しますので、今回はSUMIFSを使用しました。
失礼しました。
ちなみに私がVBAで実装するとすれば
・実績データ範囲
・科目
・商材名
を引数として渡し、合致するセルの合計を返す関数(Function)を作成すると思います。
回答ありがとうございます。はじめての質問で言葉が足りませんでした。今回VBAで実装したいと思っており、サンプルで貼り付けた実績データは毎月システムから出力されるもので、実際は別のファイルです。そして、実績データの売上、外注費、仕入高のＢ列にもそれぞれの商材の合計金額が表示されています（添付資料では空白にしてしまいましたが空白ではありません。すみません）粗利は計算式を入れてあるので実績データから商材ごとの売上、外注費、仕入高の値を集計データに転記できればと考えております。質問の仕方が悪く申しわけありませんが、お知恵を拝借できればと思います。
それぞれの商材の科目ごとの値を実績データからひろってくるだけなので合計は必要ないのです。粗利には数式を入れてあるので黄色のセルに実績データから値を参照してこれればと…、実績データに商材と科目が同じ列で表記されてしまうのでうまく判別できず困っています。
VBAでの実装をお考えでしたか。失礼しました。
＃合計は必要ないのです
これに関しては、SUMIFSは合計計算というよりも条件に一致する行の抽出を目的に使用していました。
結果、対象が０個なら合計０、１個ならその値、複数なら合計値が返されることになります。
そして
＃実績データの売上、外注費、仕入高のＢ列にもそれぞれの商材の合計金額が表示されています
ここがやっかいですね。
B列が空白かどうかでは判断できないとなると、売上/外注費/仕入高の行が科目であることをどう判断するか。。
実績データ上の見た目で判断できないなら「売上」「外注費」「仕入高」という文字列が「科目」だと判断してしまうのがラクではあります。
ただ、その他の科目が追加されると対応できないという点はネックになりそうです。
ありがとうございます。SUMIFSで条件に一致する行の抽出ができるのですね。SUMIFS関数についても調べてみることにします。最初は科目をFINDで見つけてそこからOFFSETで商材名の値を抽出しようと考えたのですが商材名が出てこない月もあるといわれてしまいOFFSETで指定するすることができなくなってしまいました。抽出する科目はこの３つより増えることは当面なさそうですが抽出しなければならない商材名は増える可能性があります。
VBAでの実装について、回答本文に追記させていただきました。
なお、使用例ではセルに式を入力する普通の自作関数の使い方で説明してありますが、「この関数を、引数を変えながら繰り返し実行し、結果を出力シートに順次書きだしていく」ようなマクロを作成すれば、一括処理も可能になると思います。
ありがとうございます。まさにやりたかったことができました！Dictionaryオブジェクト便利ですね。サンプルとしてVBAを記述していただきコメントもたくさん書いていただいたのでこれをもとに肉付けしていきたいと思います。jawaさんのような知識を身に付け、私も困った人の人助けができるようになりたいです。本当にありがとうございました。
0
SUMIF()で集計は可能ですので、あとはSUMIFでの範囲をMATCH()をOFFSETとして使用すれば良さそうです。
分かり安くするために式を分割します
実績データの当該科目のA17を先頭にした場合の相対位置をD列に設定します。
以下はD2
=MATCH(B2,A$17:A999,0)
※実績データで科目の順序が不定の場合はMAX()を使用し
=MAX(INDEX((A17:A999<=D2)*A17:A999,0))などとして範囲の次を求める。
以下はC2
=SUMIF(OFFSET(A17,D2,,D3-D2,),A2,OFFSET(B17,D2,,D3-D2,))
※データが存在しない場合はえらーとなるので、ISERRORで置き換え。
投稿2018/12/04 05:59
編集2018/12/04 07:50総合スコア25195
あなたの回答
tips
プレビュー
