[[20160923172721]] 『抜けているセル、もしくは対応したセルがないセル』(DDD) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『抜けているセル、もしくは対応したセルがないセル(値)を強調したい』(DDD)

漠然とした質問で申し訳ないのですが
知恵をお貸しいただければ幸いです

請求No. 請求金額  品名   請求内容 請求先会社名 請求日 入金日
 1   ¥10000  ぶどう   品代    A社     4/30  5/30
 2   ¥5000  ダンボール  梱包費    B社     5/30  6/30
 ︙
上記のような売上の書かれたシートと
下記のように原価の書かれたシートがあります
請求No. 請求金額  品名   請求内容  請求先会社名 請求日 入金日
 1   ¥5000    ぶどう   品代    C社     3/30  4/30
 2   ¥2500 ダンボール  品代    D社     4/30  5/30
 ︙

請求書No.は売上の請求書にもとづいてついているもので、どれに対応しているかをわかるようにするために請求書No.に合わせて原価にも反映しています

どちらかに抜けがあるかどうかを判別、もしくは空白になっている場合強調表示したい
つまり売上の請求漏れ、逆に原価の支払い漏れがないかをひと目でわかるようにしたいのですが
どのようにすればいいのか検討がつきません
なにかよい方法はありませんでしょうか

< 使用 Excel:Excel2013、使用 OS:Windows7 >


 思い付きのエッセンスだけの回答ですが、

 例えば、両シートのA列が「請求No」列だったとして、
 売上シートに=COUNTIF(原価シートのA列,A2)
 原価シートに=COUNTIF(売上シートのA列,A2)
 という式がデータの上から下まで入ってる列を作れば、その列がチェック用になります。
 (戻り値が「1」以外だったら、モレか重複が存在する)

 強調表示については、
 このチェック列の値に基づいた条件付き書式にするなり
 この計算自体を条件付き書式上でダイレクトに行うなり・・・
 (チェック列を作った方が、フィルタ等でエラー行の抽出にも使えるのでオススメかなと)

 といった方法があります。

(白茶) 2016/09/23(金) 18:53


白茶さん ご回答ありがとうございます
大変申し訳ありません説明不足でした

請求や売上が確定したら随時追加していくファイルなので
未確定の売上(請求先へ請求を出していない売上)はファイルに存在しない状態で
(請求No.のない状態で)原価のみがある場合
逆に請求No.はあり、売上も立っているがその原価が来ていない(入力されていない)
というのを判断したいのです
また売上に対して原価が複数あることが多いというのもあります

例)売上10000円に対してりんご6000円、みかん3000円、ぶどう1000円の内訳だとすると
その仕入れはそれぞれ別の箇所から
A社 りんご2000円
B社 みかん1500円
C社 ぶどう 500円

のような感じで請求がくるので、原価の方には
同じ請求No.が現れることが茶飯事な状態なのです

そもそもこれは上司から作れないかと打診されているものでして
正直関数等ではできないのではないかと自分は思っているのですが
何か代替の手段や方法がないかと思い質問させていただきました

(DDD) 2016/09/26(月) 10:50


請求Noは同じものがあるようですが、その内容は会社名、請求日、入金日という大事な情報まで、まるっきり異なっていますね。 これが同じ商品に対するデータ例なのですか? 普通は、請求日は一致しません? 1ヶ月遅れで請求して、支払われるのが1ヶ月後とか、ザルすぎて何とも…。

特定されそうな文字は他に置き換えて良いので、実際のデータを例にするのが、最も誤解のない方法です。(漢字は漢字、数字は数字、半角は半角の、別の文字に変える) 無関係な文字列や数字を並べてしまうと、それに合った回答が付き、実際のデータでは動かなくてまた質問、とか繰り返すことが多いのですよ。

内容がさっぱりなのですが、とりあえず、比較するのがA列の請求Noだけで良いとして、例えば値のある全てのセルを一括範囲選択。以下の条件付き書式を設定するとか。

 =ISNA(VLOOKUP($A1,Sheet2!$A:$A,1,FALSE))
(???) 2016/09/26(月) 11:17

 >例)売上10000円に対してりんご6000円、みかん3000円、ぶどう1000円の内訳だとすると 
 >その仕入れはそれぞれ別の箇所から 
 >A社 りんご2000円
 >B社 みかん1500円
 >C社 ぶどう 500円

 この売上の場合、売上シートはどのようなデータになっているのですか?
 同じ請求ナンバーでチャンと複数の品名が書かれていないと原価が全部あるか分からないと思います

 また、一つの品名の売上に対して複数の原価があることはないですか?

(半平太) 2016/09/26(月) 11:26


 売上シートはマスターで
 原価シートはジャーナル

 ・・・という感じをイメージすればよいのですかね?

 ひとつ、回答ではなく「懸念」なのですが、

 >売上に対して原価が複数あることが多い
 ということは、同一請求No.の同一品名のものが仕入先から「分納」されるケースも想定しなければならないのでは?
 (実地では「ありえない」のかも知れませんが、仕組みとして想定する必要はあるのでは?)

 少なくとも
  ・「請求No.」
  ・「品名」(あるいはそれに代わる「行番号」等の「請求No.内連番」)
  ・「数量」(売上・原価両方のシートで「一致すれば完了」と判定できる要素)
 
 くらいの情報が両方のデータに揃ってないと、不突合の判定が難しいと思われます。
 (心配し過ぎかな・・・)

(白茶) 2016/09/26(月) 11:31


 横から失礼します。

 2点、確認です。

 1.現在、手作業で突合せをして、漏れているとか、あっているとか、そういう作業をしておられると思いますが
   そのときの両シートのマッチングキーはどれとどれですか?

 2.1売上に複数原価があるわけですよね。
   仮に、請求書No1 に 原価が 5つあったとします。 そのうちの 2つが 請求書No1 として 原価シートに記入されていたとします。
   請求書シートに対応する原価入力があるから、OK。支払漏れはない。 こういうチェックをしているのですか?
   まだ、3つの原価に対しては、支払いがされていない。これって、どう管理しているのですか?

(β) 2016/09/26(月) 11:35


 ついでに、そちらの実際の業務運用が、完全個別紐付けでの材料調達(原価)ならいいのですが
 通常は、材料はロットで仕入れておいて、必要な数を使うということが多いですよね。
 材料だけではなく、役務なんかも、おそらく売上製品1つごとに発注しているのではなく、まとめて対応する
 ことが多いですねぇ。

 そういった場合でも、あえて 原価シートには、売上1件ごとに分解した原価をわけて入力しておられるというのなら
 スルー願います。

(β) 2016/09/26(月) 12:00


???さん ご回答ありがとうございます
扱っている商品を他に置き換えるのが難しいもので
わかりやすい果物に変えたのですがおかしいでしょうか。
3月に仕入れたものを4月に売っているというのは
別段おかしくないと思うのですが…
請求日がばらついているのは月毎にこういう風になっていると表現したかったためです
わかりにくかったら申し訳ありません

実際になにがしたいかというのを1言で表現するのは難しいのですが
要は売上だけしかないもの、原価だけしかないもの(入力がされていない)ものを
どうにか判別、もしくはその請求書No.や原価を強調表示する等で
ひと目で発見できないか、ということです。
わかりにくいかもしれませんが、請求漏れ、支払い漏れがないかをチェックしたいという意味です。

実際のものを置き換えるとこのような例になります
・売上
請求No.      売上  商品名 請求項目 請求先社名 請求日 入金日
201604-001 \50,000 ぶどう、みかん、りんご 品代 A社 4月30日 5月31日
201604-002 \100,000 メロン、スイカ  品代 B社 4月30日 5月31日
201604-003 \15,000 なし、もも     品代 C社 4月30日 5月31日
201605-001 \50,000 いちご、みかん     品代 D社 5月31日 6月30日

・原価
請求No.     原価 商品名      支払い項目 請求元社名 請求日 出金日
201604-001 \30,000 ぶどう、みかん   品代 X社 3月30日 4月30日
201604-002 \30,000 メロン       品代 Y社 3月30日 4月30日
201604-003 \5,000 なし、もも   品代 Z社 3月30日 4月30日
201604-002 \40,000 スイカ       品代 O社 3月30日 4月30日
201604-001 \5,000 りんご       品代 P社 3月30日 4月30日
201605-001 \20,000 いちご       品代 W社 4月30日 5月31日
201605-001 \20,000 みかん       品代 V社 4月30日 5月31日
(DDD) 2016/09/26(月) 12:14


コメントさせていただいている間に沢山のご意見頂きありがとうございます
サンプルデータが不十分であるために
余計にわかりにくい質問になっていて申し訳ございません

半平太さん ご回答ありがとうございます

おっしゃる通りで
???様へ解答させて頂いたように
1つの売上の中に商品が複数ある状態もあります
201604-001の\50,000という売上は ぶどう、みかん、りんご の合算ということです
ただこの場合売上の内容については別に管理しているので
このシートにりんごがいくらで売れたぶどうはいくら、という情報はいらないという状態です(特殊でわかりにくいかと思いますが)

商品ごとの利益率等は別に出しているため
このシートで重要なのはどこの取引先にいくらで物をうって
その原価はいくらなのか、というのがわかること、となっております

(DDD) 2016/09/26(月) 12:22


白茶さん 最後ご回答ありがとうございます

分納のご心配をしていただいているようですが
実際はありえない商品だと思っていただいて大丈夫です

「品名」(あるいはそれに代わる「行番号」等の「請求No.内連番」) 請求No.内連番というのがよくわからないのですが
現状の品名以外に何か共通する項目が必要、ということなのでしょうか
また>>・「数量」(売上・原価両方のシートで「一致すれば完了」と判定できる要素)

これも例えが悪かったと思う次第なのですが
実際数量は使わないです
項目として複数になることはあっても
みかん2個、林檎10個のようにはならない商品
だと思ってください。申し訳ありません
(DDD) 2016/09/26(月) 12:30


βさん ご回答ありがとうございます

 1.マッチングキー
請求書No.と商品名ですね。それ以外に共通するものがありませんので…
請求先、仕入先毎にナンバーを振って管理したかったのですが
なかなか難しく…

 2について
これが質問している理由でして
現状全て私が目視で確認している状態です。抜けがアレば
担当者に尋ねる、という形態を取っております
一応請求No.を基準にして売上に紐づく原価を引っ張ってくるシートは完成しているのですが
それはあくまで請求がきている、売上がたっているものしか判別、引用ができないので
無茶は承知ですが,今入力されていない物を探す手段があるのだろうかということで
質問させていただいております。

また数量の話もありましたが
ロットという概念はない商品なので
そちらは心配ございません

(DDD) 2016/09/26(月) 12:39


201604-001について、売上で「ぶどう、みかん、りんご」ですが、原価ではりんごだけ別行になっていますね。
これだと文字列一致にはならないので、数式判定は難しいです。

商品名の区切りが必ず「、」ならば、それを元にして分解してから比較するようなマクロを作成すれば、処理可能です。
または、手作業で全て単品同士になるよう、「ぶどう、みかん、りんご」を3行に分けるとかならば、数式を使う余地があります。

現状では、商品名が複数まとまっている事によって、データベース的なデータでは無くなっているので、このままだとかなり難しいですね。
(???) 2016/09/26(月) 12:59


もうひとつ、大事な前提条件を教えてください。

各シートの、データ数の最大は、何行くらいでしょうか? これが3万件いかないようならば、配列を使ったマクロ案が使えます。
5万件とかだと、商品名を単品毎にバラすマクロと、チェック用マクロの2つを作れば良さそう。
(???) 2016/09/26(月) 13:05


Sub main()
    '商品名の区切り文字が「、」であることが条件
    Dim Dic売上, Dic原価, 売上sht As Worksheet, 原価sht As Worksheet, c, d, k
    Set Dic売上 = CreateObject("Scripting.Dictionary")
    Set Dic原価 = CreateObject("Scripting.Dictionary")
    Set 売上sht = Sheets("売上") '実際のシート名にあわせて変更要
    Set 原価sht = Sheets("原価") '実際のシート名にあわせて変更要
    For Each c In Intersect(売上sht.UsedRange, 売上sht.Columns("A"))
        For Each d In Split(c.Offset(, 2).Value, "、")
            Dic売上(c & Chr(10) & d) = True
        Next d
    Next c
    For Each c In Intersect(原価sht.UsedRange, 原価sht.Columns("A"))
        For Each d In Split(c.Offset(, 2).Value, "、")
            Dic原価(c & Chr(10) & d) = True
        Next d
    Next c
    For Each k In Dic売上.keys
        If Not Dic原価(k) Then MsgBox k & Chr(10) & "売上あるが原価なし", vbCritical
    Next k
    For Each k In Dic原価.keys
        If Not Dic売上(k) Then MsgBox k & Chr(10) & "原価あるが売上なし", vbCritical
    Next k
End Sub

(mm) 2016/09/26(月) 13:18


 >>無茶は承知ですが,今入力されていない物を探す手段があるのだろうかということで質問させていただいております。

 一般論ですが、客先に見積書を提示する、しないにかかわらず、営業担当は、必ず見積もり行為をしているはずですね。
 原価計算 というとらえかたかもしれませんが、いずれにしても、絶対に、そういうステップは踏んでいるわけです。

 A という商品を売ろう。原価としては X が 200円、Y が 300円、Z が 500円。原価合計は 1000円になるので
 1500円で売れば、500円の粗利を確保できる。

 こんな計算ステップです。

 で、入力されていない原価項目は、どんなにあがいても、参照不可能ですね。
 やるなら(形式はどんなものでもいいのですが) A商品の売り上げ請求情報に、この X,Y,Z を紐付けて(すでに作業上は紐付いているわけです)
 どこかに記載しておくことが必要ですね。

 そういう構成にしておけば、請求データと、それに基づく原価データが把握でき、支払漏れの有無がチェックできます。

 逆にいえば、それがないと、不可能です。

(β) 2016/09/26(月) 13:26


 ↑ もし、定番商品 といったものであれば、たとえば 商品A に対して、材料(原価)は X,Y,Z だという
 マスタを持っておいて、そこを参照する という構成は実際の商取引では多いですね。

 DDDさんのケースはどうなんでしょうか?

(β) 2016/09/26(月) 13:31


mmmさん ご回答ありがとうございます

これは
Set 売上sht = Sheets("売上") '実際のシート名にあわせて変更要
Set 原価sht = Sheets("原価") '実際のシート名にあわせて変更要
("")内を実際のシート名にすればいいということでしょうか
(DDD) 2016/09/26(月) 13:33


βさん ご回答ありがとうございます

定番商品、といった概念は残念ながらございません
なのでそこは売値も原価も毎度変動します

一点もの、とでもいうのでしょうか
例えば絵画のような芸術品やCMのような
派生は合っても同じものはない、ような商品なので(もはや大分絞られてしまいますが…)
(DDD) 2016/09/26(月) 13:36


("")内を実際のシート名にすればいいということでしょうか

そうです。
(mm) 2016/09/26(月) 13:46


mmさん ご回答ありがとうございます
試しに例のファイルに
原価がない売上として
201606-001 \100,000 マンゴー 品代 B社 6月30日 7月31日

売上のない原価として
201607-002 \50,000 メロン 品代 Z社 6月30日 7月31日

というのを追加して試してみました
ポップアップで注意書きの様に出ているのが成功と考えてよろしいんでしょうか
だとすると、毎回一番上の項目名の部分
→この一番上の項目列 請求No.売上 etc.

が売上なし、原価なしと表示されてしまうのですが
どちらを変えればその表示がなくなりますでしょうか

またこれを元ファイルに置き換えてもうまく動かなかったので
取り急ぎどの部分が範囲で条件を指定しているのか
解説していただけないでしょうか
厚かましいとは思いますがよろしくお願いいたします

(DDD) 2016/09/26(月) 16:23


売上、原価の両シートとも、A列が請求No. C列が商品名です。
両シートの見出行(1行目)にA列「請求No.」、C列「商品名」と入力し、
2行目からデータを入力してください。
ポップアップで警告が表示されます。
A、C列以外は見ていません。

(mm) 2016/09/26(月) 16:50


 >>定番商品、といった概念は残念ながらございません 
 >>なのでそこは売値も原価も毎度変動します 

 であれば、かつ、請求シートにあるもので、原価シートに記入したもの以外があるかどうかをチェックしたいなら
 請求に紐付く原価情報を前もってすべて、どこかに記入しておき、そこで支払い済みかどうかのチェックをかけるしか
 方法はないですね。

(β) 2016/09/26(月) 17:57


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.