[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『商品の販売情報を在庫表へ反映させる関数』（qjcyp880）
わかる方がいらっしゃいましたらお助け下さい。 シート1が在庫表となっており、一例では御座いますが、下記内容が入力されています。
W列の9〜1008に商品状態が入力されており、入荷日が記載されると自動的に在庫となり、販売日が入力されると自動的に販売済み等となります。 Y列の9〜1008に販売日が入力されています。 Z列の9〜1008に販売価格をが入力されています。 AB列の9〜1008に商品コードが入力されています。 AF列の9〜1008に商品の入荷日が入力されています。
お伺いしたい内容と致しましては、 在庫表とは別に販売表を作り、A列に販売された商品の商品コードを入力し、隣のB列に販売日を入力し、また隣にのC列に販売した価格を入力し、こちらの入力された情報を在庫表に反映させたいのですが、その際、同じ商品コードが在庫表のな中に複数存在する中で商品状態が在庫になっている入荷日が一番古いものから順に販売表に入力した内容を在庫表へ反映させていきたいのですが、どの様な計算式になるのでしょうか？ ※尚、同じ商品で同じ入荷日が複数存在する場合は、在庫表の上にあるものから順に販売日を反映させる。
シート1(在庫表)
理想表示図例
W列.........Y列..........Z列......AB列.......AF列
販売済み.2月1日...1000円.A000111.11月16日
販売済み.2月3日...1500円.A000111.11月29日
販売済み.2月6日...1600円.A000222.12月4日
在庫.....................................A000111.12月6日
販売済み.2月3日...1500円.A000333.12月11日
在庫.....................................A000222.1月4日
在庫.....................................A000444.1月4日
販売済み.2月5日...2000円.A000555.1月11日
販売済み.2月7日...2000円.A000333.1月12日
他店移動.............................A000555.1月14日
販売済み.2月5日...2000円.A000555.2月3日
在庫.....................................A000333.2月3日
販売済み.2月7日...2100円.A000555.2月4日
在庫.....................................A000555.2月4日
在庫.....................................A000555.2月4日
↓ シート2(販売表) 理想表示図例
A列...........B列.........C列
A000111...2月1日...1000円
A000111...2月3日...1500円
A000333...2月3日...1500円
A000555...2月5日...2000円
A000555...2月5日...2000円
A000222...2月6日...1600円
A000555...2月7日...2100円
A000333...2月7日...2000円
以上、宜しくお願い致します。
＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞
シート1(在庫表)、シート2(販売表)の理想表示図例が非常に取り扱いにくいです。
次のマクロを使って、理想表示図例を表示してください。 誰からも回答もらえませんよ。 シートレイアウトを調整するマクロ.xls（ダウンロードパスワード;abc） http://ww10.puny.jp/uploader/download/1456841633.zip
[[20110209184943]] 『[談]シートレイアウトの投稿どうしてますか？』（ｍｏｍｏ） より
（マリオ） 2016/03/01(火) 23:20
交通整理だけ。
・在庫は数量管理ではなく、あくまで金額で管理するのですね？ ・シート1在庫表の各行、販売は一括ですね。（部分販売はないのですね？） ・シート2販売表、固まりは、シート1の1行単位ですね？ それとも、1行で大きな数字が入って、シート1の複数行を販売済みとすることがあるのですか？ 後者なら残高が必要で、それがないので前者だと思いますが。
（β） 2016/03/02(水) 05:59
しかし、どうなんでしょうね・・・・ シート2からシート1へ書き込みを行うような数式はありえませんので、シート1のY列やZ列に数式を置くのでしょうかね？ そんなことができるのでしょうか？ できるとして、その式は、W列が在庫だったらどうこうするという式になりそうですし、ということは 在庫じゃなかったら【何もしない】という式になる？ 何もしない（つまり、前にあったままの状態にする）という数式って作れるんですかね？
何かしらの数式で仮に販売ステータスになった瞬間に、W列は【在庫じゃなくなる】わけですから Y列やZ列にある数式が、またもや、何かしら評価をする・・・・？ それって循環参照になりそうな・・・・・
マクロになるんじゃないですか？
（β） 2016/03/02(水) 06:20
マクロ案です。
本来は作業シートのようなものに、SHeet1の該当部分を値転記。 その右側あたりに連番を振っておいて、商品コードで並び替え。 SHeet2の内容も、この作業シートの別の場所に値転記して、商品コード（と、必要なら日付）で並び替え。 この２つぼ表をつき合わせながらいわゆるソートマージ処理を行って、最初に振った連番で並び順を元に戻した後 Ｙ，Ｚ列を値転記でSheet1 に戻すというのが、プログラムらしいプログラムでしょうけど、βは、そのような 精緻なアルゴリズムを組み立てるのが苦手なので、力技のバカチョンDictionary処理です。
Sub Test()
Dim dic As Object
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim c As Range
Dim com As String
Dim k As Variant
Set dic = CreateObject("Scripting.Dictionary")
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
For Each c In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))
com = c.Value
If Not dic.exists(com) Then Set dic(com) = CreateObject("Scripting.Dictionary")
dic(com)(dic(com).Count) = c.EntireRow.Range("B1:C1").Value
Next
For Each c In sh1.Range("W8", sh1.Range("W" & Rows.Count).End(xlUp))
If c.Value = "在庫" Then
com = c.Offset(, 5).Value
If dic.exists(com) Then
If dic(com).Count > 0 Then
k = dic(com).keys
c.EntireRow.Range("Y1:Z1").Value = dic(com)(k(0))
dic(com).Remove k(0)
End If
End If
End If
Next
End Sub （β） 2016/03/02(水) 11:23
↑ Sheet2側、データが2行目からというコードです。1行目からであれば A2 を A1 に直してください。
（β） 2016/03/02(水) 11:24
To qjcyp880 さん
理想表示図のイメージは、次のようなものですか？
***************************************************************************************** データ反映元のシート （シート名：Sheet2）販売表
|[A] |[B] |[C]
[1]|商品コード|販売日|販売価格
[2]|A000111 |2月1日|1000円
[3]|A000111 |2月3日|1500円
[4]|A000333 |2月3日|1500円
[5]|A000555 |2月5日|2000円
[6]|A000555 |2月5日|2000円
[7]|A000222 |2月6日|1600円
[8]|A000555 |2月7日|2100円
[9]|A000333 |2月7日|2000円
*****************************************************************************************
データ反映先のシート「Sheet2のデータ★反映前の状態」
（シート名：Sheet1）在庫表
|[W] |[X]|[Y] |[Z] |[AA]|[AB] |[AC]|[AD]|[AE]|[AF]
[8] |商品状態| |販売日|販売価格| |商品コード| | | |入荷日
[9] |販売済み| |2月1日|1000円 | |A000111 | | | |11月16日
[10]|販売済み| |2月3日|1500円 | |A000111 | | | |11月29日
[11]|販売済み| |2月6日|1600円 | |A000222 | | | |12月4日
[12]|在庫 | | | | |A000111 | | | |12月6日
[13]|販売済み| |2月3日|1500円 | |A000333 | | | |12月11日
[14]|在庫 | | | | |A000222 | | | |1月4日
[15]|在庫 | | | | |A000444 | | | |1月4日
[16]|販売済み| |2月5日|2000円 | |A000555 | | | |1月11日
[17]|販売済み| |2月7日|2000円 | |A000333 | | | |1月12日
[18]|他店移動| | | | |A000555 | | | |1月14日
[19]|販売済み| |2月5日|2000円 | |A000555 | | | |2月3日
[20]|在庫 | | | | |A000333 | | | |2月3日
[21]|販売済み| |2月7日|2100円 | |A000555 | | | |2月4日
[22]|在庫 | | | | |A000555 | | | |2月4日
[23]|在庫 | | | | |A000555 | | | |2月4日
*****************************************************************************************
データ反映先のシート「Sheet2のデータ★反映後の状態」
（シート名：Sheet1）在庫表
|[W] |[X]|[Y] |[Z] |[AA]|[AB] |[AC]|[AD]|[AE]|[AF]
[8] |商品状態| |販売日 |販売価格| |商品コード| | | |入荷日
[9] |販売済み| |2月1日 |1000円 | |A000111 | | | |11月16日
[10]|販売済み| |2月3日 |1500円 | |A000111 | | | |11月29日
[11]|販売済み| |2月6日 |1600円 | |A000222 | | | |12月4日
[12]|在庫 | |2016/2/1|1000円 | |A000111 | | | |12月6日
[13]|販売済み| |2月3日 |1500円 | |A000333 | | | |12月11日
[14]|在庫 | |2016/2/6|1600円 | |A000222 | | | |1月4日
[15]|在庫 | | | | |A000444 | | | |1月4日
[16]|販売済み| |2月5日 |2000円 | |A000555 | | | |1月11日
[17]|販売済み| |2月7日 |2000円 | |A000333 | | | |1月12日
[18]|他店移動| | | | |A000555 | | | |1月14日
[19]|販売済み| |2月5日 |2000円 | |A000555 | | | |2月3日
[20]|在庫 | |2016/2/3|1500円 | |A000333 | | | |2月3日
[21]|販売済み| |2月7日 |2100円 | |A000555 | | | |2月4日
[22]|在庫 | |2016/2/5|2000円 | |A000555 | | | |2月4日
[23]|在庫 | |2016/2/5|2000円 | |A000555 | | | |2月4日
*****************************************************************************************
＞W列の9〜1008に商品状態が入力されており、 ＞入荷日が記載されると自動的に在庫となり、 ＞販売日が入力されると自動的に販売済み等となります。 ★W列に数式が入っていますよね？その数式を示してください。
＞どの様な計算式になるのでしょうか？ おそらく、式の循環参照になって、数式は使えないと思いますが。 W列の「在庫」の文字列が固定ではないので。
（マリオ） 2016/03/02(水) 12:23
To qjcyp880 さん マクロを使用できる環境にありますか？エクセルでなくてkingsoftとか？
＞どの様な計算式になるのでしょうか？ どの様な計算式も成り立たないと思いますよ。
W列には、数式が入ってますよね？ W9=IF(AND(AF9<>"",Y9=""),"在庫",IF(AND(AF9<>"",Y9<>""),"販売済み","★")) Y9=IF(W9="在庫","★","☆") この2つの式は、同時には成り立ちません。
★式の循環参照 (1)W列の数式は、Y列を参照（Y列「販売日」が入力されているか）（AF列も参照） (2)Y列の数式は、W列を参照（W列が在庫となっているかを）
（マリオ） 2016/03/02(水) 18:01
Sheet1のW列(W18)の「他店移動」は何？数式を消して、手入力したもの？ Sheet1のY列は、表示形式をユーザー定義型「m"月"d"日"」にしないとダメですね。 （マリオ） 2016/03/02(水) 18:13
ご回答頂き、有難う御座います。
又、ご返信が遅くなり、申し訳御座いません。
マリオさんの理想表示図例を元に再度、理想表示図例をわかりやすく直しましたので、
お手数をお掛け致しますが、ご確認、宜しくお願い致します。
|[W] |[X]|[Y] |[Z] |[AA]|[AB] |[AC]|[AD]|[AE]|[AF]
[8] |在庫 | |販売日|販売価格| |商品コード| | | |入荷日
[9] |在庫 | | | | |A000111 | | | |11月16日
[10]|在庫 | | | | |A000111 | | | |11月29日
[11]|在庫 | | | | |A000222 | | | |12月4日
[12]|在庫 | | | | |A000111 | | | |12月6日
[13]|在庫 | | | | |A000333 | | | |12月11日
[14]|在庫 | | | | |A000222 | | | |1月4日
[15]|在庫 | | | | |A000444 | | | |1月4日
[16]|在庫 | | | | |A000555 | | | |1月11日
[17]|在庫 | | | | |A000333 | | | |1月12日
[18]|他店移動| |2月6日| | |A000555 | | | |1月14日
[19]|在庫 | | | | |A000555 | | | |2月3日
[20]|在庫 | | | | |A000333 | | | |2月3日
[21]|在庫 | | | | |A000555 | | | |2月4日
[22]|在庫 | | | | |A000555 | | | |2月4日
[23]|在庫 | | | | |A000555 | | | |2月4日
|[A] |[B] |[C]
[1]|商品コード|販売日|販売価格
[2]|A000111 |2月1日|1000円
[3]|A000111 |2月3日|1500円
[4]|A000333 |2月3日|1500円
[5]|A000555 |2月5日|2000円
[6]|A000555 |2月5日|2000円
[7]|A000222 |2月6日|1600円
[8]|A000555 |2月7日|2100円
[9]|A000333 |2月7日|2000円
|[W] |[X]|[Y] |[Z] |[AA]|[AB] |[AC]|[AD]|[AE]|[AF]
[8] |商品状態| |販売日 |販売価格| |商品コード| | | |入荷日
[9] |販売済み| |2月1日 |1000円 | |A000111 | | | |11月16日
[10]|販売済み| |2月3日 |1500円 | |A000111 | | | |11月29日
[11]|販売済み| |2月6日 |1600円 | |A000222 | | | |12月4日
[12]|在庫 | | | | |A000111 | | | |12月6日
[13]|販売済み| |2月3日 |1500円 | |A000333 | | | |12月11日
[14]|在庫 | | | | |A000222 | | | |1月4日
[15]|在庫 | | | | |A000444 | | | |1月4日
[16]|販売済み| |2月5日 |2000円 | |A000555 | | | |1月11日
[17]|販売済み| |2月7日 |2000円 | |A000333 | | | |1月12日
[18]|他店移動| |2月6日 | | |A000555 | | | |1月14日
[19]|販売済み| |2月5日 |2000円 | |A000555 | | | |2月3日
[20]|在庫 | | | | |A000333 | | | |2月3日
[21]|販売済み| |2月7日 |2100円 | |A000555 | | | |2月4日
[22]|在庫 | | | | |A000555 | | | |2月4日
[23]|在庫 | | | | |A000555 | | | |2月4日
*****************************************************************************************
Ｗ列には以下計算式が入っております。
こちらの計算式を元にご質問頂いております内容は確認出来ますでしょうか？
=IF(Y1="","",IF(ISNUMBER(AJ1),IF(OR(AH1="Ａ社",AH1="Ｂ社",AH1="Ｃ社",AH1="Ｄ社",AH1="Ｅ社",AH1="Ｆ社"),"返品済み","他店移動"),IF(ISNUMBER(V1),"販売済み",IF(NOT(ISBLANK(Y1)),"在庫",""&""))))
尚、マクロを使わず、計算式のみで作成をしたいのですが、可能でしょうか？
もしくは、難しい場合、入荷日の古い順という条件を抜かし、同じ商品コードが在庫表の中に複数存在する中で商品状態が在庫になっているものを上から順に販売日を反映させる事は可能でしょうか？
以上、大変お手数をお掛け致しますが、宜しくお願い致します。
（qjcyp880） 2016/03/02(水) 23:37
今回もご回答頂き、有難う御座います。
大変、勉強になるご回答を大変感謝をしておりますが、
どうしても関数による計算式にて作成をしたく、
もし可能であれば教えて頂きたいのですが、条件の中の一つである
入荷日の古いものから順にを抜かした場合、
同じ商品コードが在庫表の中に複数存在する中で商品状態が在庫になっているものを上から順に販売日を反映させる事は可能でしょうか？
何度もお手数をお掛け致しますが、宜しくお願い致します。
（シート名：Sheet1）在庫表
|[W] |[X]|[Y] |[Z] |[AA]|[AB] |[AC]|[AD]|[AE]|[AF]
[8] |在庫 | |販売日|販売価格| |商品コード| | | |入荷日
[9] |在庫 | | | | |A000111 | | | |11月16日
[10]|在庫 | | | | |A000111 | | | |11月29日
[11]|在庫 | | | | |A000222 | | | |12月4日
[12]|在庫 | | | | |A000111 | | | |12月6日
[13]|在庫 | | | | |A000333 | | | |12月11日
[14]|在庫 | | | | |A000222 | | | |1月4日
[15]|在庫 | | | | |A000444 | | | |1月4日
[16]|在庫 | | | | |A000555 | | | |1月11日
[17]|在庫 | | | | |A000333 | | | |1月12日
[18]|他店移動| |2月6日| | |A000555 | | | |1月14日
[19]|在庫 | | | | |A000555 | | | |2月3日
[20]|在庫 | | | | |A000333 | | | |2月3日
[21]|在庫 | | | | |A000555 | | | |2月4日
[22]|在庫 | | | | |A000555 | | | |2月4日
[23]|在庫 | | | | |A000555 | | | |2月4日
（シート名：Sheet2）販売表
|[A] |[B] |[C]
[1]|商品コード|販売日|販売価格
[2]|A000111 |2月1日|1000円
[3]|A000111 |2月3日|1500円
[4]|A000333 |2月3日|1500円
[5]|A000555 |2月5日|2000円
[6]|A000555 |2月5日|2000円
[7]|A000222 |2月6日|1600円
[8]|A000555 |2月7日|2100円
[9]|A000333 |2月7日|2000円
（シート名：Sheet1）在庫表
|[W] |[X]|[Y] |[Z] |[AA]|[AB] |[AC]|[AD]|[AE]|[AF]
[8] |商品状態| |販売日 |販売価格| |商品コード| | | |入荷日
[9] |販売済み| |2月1日 |1000円 | |A000111 | | | |11月16日
[10]|販売済み| |2月3日 |1500円 | |A000111 | | | |11月29日
[11]|販売済み| |2月6日 |1600円 | |A000222 | | | |12月4日
[12]|在庫 | | | | |A000111 | | | |12月6日
[13]|販売済み| |2月3日 |1500円 | |A000333 | | | |12月11日
[14]|在庫 | | | | |A000222 | | | |1月4日
[15]|在庫 | | | | |A000444 | | | |1月4日
[16]|販売済み| |2月5日 |2000円 | |A000555 | | | |1月11日
[17]|販売済み| |2月7日 |2000円 | |A000333 | | | |1月12日
[18]|他店移動| |2月6日 | | |A000555 | | | |1月14日
[19]|販売済み| |2月5日 |2000円 | |A000555 | | | |2月3日
[20]|在庫 | | | | |A000333 | | | |2月3日
[21]|販売済み| |2月7日 |2100円 | |A000555 | | | |2月4日
[22]|在庫 | | | | |A000555 | | | |2月4日
[23]|在庫 | | | | |A000555 | | | |2月4日
*****************************************************************************************
（qjcyp880） 2016/03/02(水) 23:46
qjcyp880 さん
★印の質問に回答願います。
今回は、データ反映先のシート「Sheet2のデータ 反映前の状態」の Y列とZ列に数式を入れて、必要な情報を表示できますか？という質問ですよね。 ★Y18には、2月6日が入力されていますが、これは手入力ですか？Y列,Z列には数式が入っていませんよね？
データ反映先のシート「Sheet2のデータ 反映前の状態」の 「W列全体を手作業でコピーして、AG列に形式を選択して値のみ貼りつける」という手間をかけたら、可能かもしれません。 Y列,Z列の数式は、W列を参照しないで、AG列を参照するようにする。 （ちなみに、AGに数式は入れられません。AG9=W9とすると、やはり循環参照になってしまう。） ★そのようにしても、よろしいですか？
★エクセルを使用してましたら、エクセルのバージョンを教えてください。また、(1)と(2)のどちらですか？ (1)マクロを使用できる環境にあるが、マクロを使用したくない。 (2)マクロを使用できない。エクセルでなくkingsoftなどを使用している。
★循環参照エラーになることを確認されましたか？ W9=IF(AND(AF9<>"",Y9=""),"在庫",IF(AND(AF9<>"",Y9<>""),"販売済み","★")) Y9=IF(W9="在庫","★","☆") または、 AG9=W9 W9=IF(AND(AF9<>"",Y9=""),"在庫",IF(AND(AF9<>"",Y9<>""),"販売済み","★")) Y9=IF(AG9="在庫","★","☆")
（マリオ） 2016/03/03(木) 00:42
★そのようにしても、よろしいですか？ の質問は、一度忘れてください。
W9=IF(Y9="","",IF(ISNUMBER(AJ9),IF(OR(AH9="Ａ社",AH9="Ｂ社",AH9="Ｃ社",AH9="Ｄ社",AH9="Ｅ社",AH9="Ｆ社"),"返品済み","他店移動"),IF(ISNUMBER(V9),"販売済み",IF(NOT(ISBLANK(Y9)),"在庫",""&"")))) より、W9はY9,AJ9,AH9,V9を参照してますので、 データ反映先のシート「Sheet2のデータ 反映前の状態」のY列,Z列の数式で、W列ではなく、Y9,AJ9,AH9,V9の内、どれかを参照すれば済むかも知れません。後日、考えてみます。
（マリオ） 2016/03/03(木) 01:21
>>どうしても関数による計算式にて作成をしたく、
マリオさんも私もコメントしていますが、Y列、Z列に数式を配置することは不可能だと考えています。 やるなら、Y列、Z列に表示すべき値を別の列、たとえばAO列、AP列あたりに数式で取得し、その結果を 【値のコピペ】で Y列、Z列に戻してやる（ここは操作）という方法は考えられます。 ただし、実際には Y列やZ列には販売状況や他店移動状況の場合にの数式が入っているのではないかと思いますし それはそれで、AO列、AP列に復元しておかなければいけない？
関数音痴のβには、とても荷が重すぎるので、エキスパートさんの回答をお待ちください。 撤収します。
（β） 2016/03/03(木) 08:16
まず、（マリオ） 2016/03/03(木) 00:42の質問にお答えください。 （★そのようにしても、よろしいですか？以外の質問）
（マリオ） 2016/03/03(木) 15:37
ご返信頂き、有難う御座います。
また、ご回答が遅くなり、申し訳御座いません。
以下、質問回答になります。
今回は、データ反映先のシート「Sheet2のデータ 反映前の状態」の
Y列とZ列に数式を入れて、必要な情報を表示できますか？という質問ですよね。 ★Y18には、2月6日が入力されていますが、これは手入力ですか？Y列,Z列には数式が入っていませんよね？
→誤った記載をしてしまい、実際は、Y18には何も入力されておらず、空白になっております。
誤った記載をしてしまい申し訳御座いません。
★エクセルを使用してましたら、エクセルのバージョンを教えてください。また、(1)と(2)のどちらですか？ (1)マクロを使用できる環境にあるが、マクロを使用したくない。 (2)マクロを使用できない。エクセルでなくkingsoftなどを使用している。
→使用中のExcelは、2013になります。
また、マクロに関しましては、(1)マクロを使用できる環境にあるが、マクロを使用したくない。
の方となり、マクロを使える環境では御座いますが、マクロを使用せずに関数による計算式にて
表を作りたく思っております。
大変お手数をお掛け致します。
★循環参照エラーになることを確認されましたか？ W9=IF(AND(AF9<>"",Y9=""),"在庫",IF(AND(AF9<>"",Y9<>""),"販売済み","★")) Y9=IF(W9="在庫","★","☆") または、 AG9=W9 W9=IF(AND(AF9<>"",Y9=""),"在庫",IF(AND(AF9<>"",Y9<>""),"販売済み","★")) Y9=IF(AG9="在庫","★","☆")
→W9及びY9共に数式リボンのエラーチェックボタンにて確認致しましたが、特に問題御座いませんでした。
以上、大変お手数をお掛け致しますが、宜しくお願い致します。
（qjcyp880） 2016/03/04(金) 01:00
お世話になります訂正をさせて頂きます。
★循環参照エラーになることを確認されましたか？
W9=IF(AND(AF9<>"",Y9=""),"在庫",IF(AND(AF9<>"",Y9<>""),"販売済み","★")) Y9=IF(W9="在庫","★","☆") または、 AG9=W9 W9=IF(AND(AF9<>"",Y9=""),"在庫",IF(AND(AF9<>"",Y9<>""),"販売済み","★")) Y9=IF(AG9="在庫","★","☆")
→上記計算式を表に入力し、循環参照エラーになる事を確認致しました。
以上、お手数をお掛け致しますが、宜しくお願い致します。
（qjcyp880） 2016/03/04(金) 01:08
ご回答頂き、有難う御座います。
一点誤った記載をしてしまい、Y18には2月6日が入力されていましたが、実際は、Y18には何も入力されておらず、空白になっておりY列、Z列には数式は入っておらず、空白になります。
大変お手数をお掛け致しました。
ご回答頂き、大変感謝しております。
また何か機会が御座いましたら、是非とも宜しくお願い致します。
（qjcyp880） 2016/03/04(金) 01:28
データ反映先のシート「Sheet2のデータ 反映前の状態」のY9は空欄ですね。 W9に次の数式を入れると、W9は空欄になるはずなのに、「在庫」と表示されています。矛盾しますね。 ★数式が間違っているのでしょうか？表が間違っているのでしょうか？どちらかが間違ってます。確認願います。 Y9が空欄なら、「在庫」と表示されない式ですよね。W列の数式を見直した方がいいかもしれません。
W9=IF(Y9="","",IF(ISNUMBER(AJ9),IF(OR(AH9="Ａ社",AH9="Ｂ社",AH9="Ｃ社",AH9="Ｄ社",AH9="Ｅ社",AH9="Ｆ社"),"返品済み","他店移動"),IF(ISNUMBER(V9),"販売済み",IF(NOT(ISBLANK(Y9)),"在庫",""&""))))
******************************************************************************* W9はY9,AJ9,AH9,V9を参照してますね。なので、 3つの表を再アップしてください（Sheet1はV列からAJ列まで）下の表は、いい加減です。
★データ反映元のシート （シート名：Sheet2）販売表 ★データ反映先のシート「Sheet2のデータ 反映前の状態」（シート名：Sheet1）在庫表 ★ データ反映先のシート「Sheet2のデータ 反映後の状態」（シート名：Sheet1）在庫表
|[V] |[W] |[X]|[Y] |[Z] |[AA]|[AB] |[AC]|[AD]|[AE]|[AF] |[AG]|[AH] |[AI]|[AJ]
[8] |数値|商品状態| |販売日|販売価格| |商品コード| | | |入荷日 | |会社名| |数値
[9] | 1| | | | | |A000111 | | | |11月16日| |Ａ社 | | 1
[10]| 1| | | | | |A000111 | | | |11月29日| |G社 | | 1
[11]| 1| | | | | |A000222 | | | |12月4日 | |Ａ社 | | 1
[12]| 1| | | | | |A000111 | | | |12月6日 | |Ａ社 | | 1
[13]| 1| | | | | |A000333 | | | |12月11日| |Ａ社 | | 1
[14]| 1| | | | | |A000222 | | | |1月4日 | |Ａ社 | | 1
[15]| 1| | | | | |A000444 | | | |1月4日 | |Ａ社 | | 1
[16]| 1| | | | | |A000555 | | | |1月11日 | |Ａ社 | | 1
[17]| 1| | | | | |A000333 | | | |1月12日 | |Ａ社 | | 1
[18]| 1|他店移動| | | | |A000555 | | | |1月14日 | |Ａ社 | | 1
[19]| 1| | | | | |A000555 | | | |2月3日 | |Ａ社 | | 1
[20]| 1| | | | | |A000333 | | | |2月3日 | |Ａ社 | | 1
[21]| 1| | | | | |A000555 | | | |2月4日 | |Ａ社 | | 1
[22]| 1| | | | | |A000555 | | | |2月4日 | |Ａ社 | | 1
[23]| 1| | | | | |A000555 | | | |2月4日 | |Ａ社 | | 1
******************************************************************************* （考察中メモ） やりたいこと→「Sheet1のY列とZ列に数式を入れて、Sheet2から必要な情報をPickupしたい」 Y列とZ列の数式には、Y9を含めることはできない（循環参照） W列の商品状態→「在庫」「他店移動」、「販売済み」、「返品済み」、「空欄」、「"&"」
（マリオ） 2016/03/04(金) 10:13
大変お世話になります。
ご指摘頂きました下記計算式に関しまして
W9=IF(Y9="","",IF(ISNUMBER(AJ9),IF(OR(AH9="Ａ社",AH9="Ｂ社",AH9="Ｃ社",AH9="Ｄ社",AH9="Ｅ社",AH9="Ｆ社"),"返品済み","他店移動"),IF(ISNUMBER(V9),"販売済み",IF(NOT(ISBLANK(Y9)),"在庫",""&""))))
大変申し訳ない事に誤った表の計算式をコピーし、記載してしまいました。
何度も大変なお手数をお掛けしてしまい、誠に申し訳御座いません。
正しくは下記記載の計算式になります。
W9=IF(AB9="","",IF(ISNUMBER(AM9),IF(OR(AK9="Ａ社",AK9="Ｂ社",AK9="Ｃ社",AK9="Ｄ社",AK9="Ｅ社",AK9="Ｆ社"),"返品済み","他店移動"),IF(ISNUMBER(Y9),"販売済み",IF(NOT(ISBLANK(AB9)),"在庫",""&""))))
お手数をお掛け致しますが、宜しくお願い致します。
（qjcyp880） 2016/03/04(金) 14:44
この質問の回答ではありませんが、私も類似の表を利用しますので、参考までに。
様式は、簡略化していますが、以下のとおりです。
Sheet1 を「入庫票」、Sheet2 を「出庫表」と位置づけ、
Sheet1「反映前」の[AF][AB]列が下表の[A][B]列となります。
Sheet2 は、「出庫表」とし、「販売」以外に「返品」「他店移動」「廃棄」等を列記します。
この出庫表を時系列に並べると、先入先出しの方式で整理されます。
入庫表 Sheet1
[A] [B] [C] [D] [E] [1] 入荷日 コード 商品状況 販売日 販売価格 [2] 11月16日 111 販売先A 2月1日 1,000 [3] 11月29日 111 販売先B 2月3日 1,500 [4] 12月4日 222 販売先A 2月6日 1,600 [5] 12月6日 111 在庫 [6] 12月11日 333 販売先A 2月3日 1,500 [7] 1月4日 222 在庫 [8] 1月4日 444 在庫 [9] 1月11日 555 販売先C 2月5日 2,000 [10] 1月12日 333 販売先C 2月7日 2,000 [11] 1月14日 555 販売先D 2月5日 2,000 [12] 2月3日 555 他店移動 2月6日 0 [13] 2月3日 333 在庫 [14] 2月4日 555 販売先B 2月7日 2,100 [15] 2月4日 555 返品 2月10日 0 [16] 2月4日 555 在庫
出庫表 Sheet2
[A] [B] [C] [D] [1] コード 出庫日 販売価格 出庫状況 [2] 111 2月1日 1000 販売先A [3] 111 2月3日 1500 販売先B [4] 333 2月3日 1500 販売先A [5] 555 2月5日 2000 販売先C [6] 555 2月5日 2000 販売先D [7] 555 2月6日 他店移動 [8] 222 2月6日 1600 販売先A [9] 555 2月7日 2100 販売先B [10] 333 2月7日 2000 販売先C [11] 555 2月10日 返品
c3=IFERROR(INDEX(Sheet2!D$2:D$30,SMALL(IF(Sheet2!$A$2:$A$30=$B3,ROW($A$1:$A$29)),COUNTIF($B$3:$B3,"="&$B3))),"在庫")
d3=IFERROR(INDEX(Sheet2!B$2:B$30,SMALL(IF(Sheet2!$A$2:$A$30=$B3,ROW($A$1:$A$29)),COUNTIF($B$3:$B3,"="&$B3))),"")
e3=IFERROR(INDEX(Sheet2!C$2:C$30,SMALL(IF(Sheet2!$A$2:$A$30=$B3,ROW($A$1:$A$29)),COUNTIF($B$3:$B3,"="&$B3))),"")
ともに shift+ctrl+enter し、下コピー(関数式は改善の余地あり）
これに準じると、「循環参照エラー」はなく、w・y・z列がｃ・ｄ・e列のように計算されますよ。
（もん） 2016/03/04(金) 16:10
To もん さん 投稿ありがとうございます。助かります。 qjcyp880 さんの表に、もんさんの数式を適用してみました。大変、参考になりました。 通常の数式ではなく、配列数式を使用するのですね。 残念ですが、qjcyp880さんの場合、もんさんの数式では不足です(+_+) qjcyp880さんのY,Z列に入力する数式は、W列が在庫ならの条件が、さらに付きます。 もんさん、お力をお貸しください！
To qjcyp880さん もう一度、言いますが、3つの表を再アップしてください（★★★Sheet1はV列からAJ列まで★★★） ←マクロを使わなくても、2016/03/04(金) 10:13の表を使用すれば、できるはずです。
(1つめの表)データ反映元のシート （シート名：Sheet2）販売表 (2つめの表)データ反映先のシート「Sheet2のデータ 反映前の状態」（シート名：Sheet1）在庫表 (3つめの表)データ反映先のシート「Sheet2のデータ 反映後の状態」（シート名：Sheet1）在庫表
****************************************************************************************** ■もんさんとqjcyp880 さんの表の相違点 もんさんの数式は、Sheet1の入荷日と商品コードを参照していますが、 qjcyp880 さんの数式は、それに加え、W列の商品状態を参照しないといけません。
もんさんの商品状態（出庫状況）は、Sheet2のD列に記載されていますが、 qjcyp880 さんの商品状態は、Sheet2ではなく、Sheet1のW列に記載されています。 ******************************************************************************************
■Sheet1において Y9=IFERROR(INDEX(Sheet2!B$2:B$9999,SMALL(IF(Sheet2!$A$2:$A$9999=$AB9,ROW($AF$1:$AF$9998)),COUNTIF($AB$9:$AB9,"="&$AB9))),"") を入力後、式を配列数式にするためにY9セルを選択して「Ctrl + Shift + Enter」を押す。 数式欄の前後に｛｝が付く。そうしたら、Y9を必要なY列の最終行までフィルコピー。 Z9=IFERROR(INDEX(Sheet2!C$2:C$9999,SMALL(IF(Sheet2!$A$2:$A$9999=$AB9,ROW($AF$1:$AF$9998)),COUNTIF($AB$9:$AB9,"="&$AB9))),"") を入力後、式を配列数式にするためにZ9セルを選択して「Ctrl + Shift + Enter」を押す。 数式欄の前後に｛｝が付く。そうしたら、Z9を必要なY列の最終行までフィルコピー。
※（注意！）配列数式にしたセルをクリックしてしまうと、通常の数式に戻ってしまいます。 数式の前後の｛｝が外れてます（緑の▲が付いて、回りの数式と違うことを教えてくれるかもしれません） ★Y9とZ9の数式は、Sheet2の9999行まで対応★
次の表のようになりますが、これではダメですね。W列が在庫ならの条件が付いてないから。
|[Y] |[Z] |[AA]|[AB] |[AC]|[AD]|[AE]|[AF]
[8] |販売日|販売価格| |商品コード| | | |入荷日
[9] |2月1日|2000円 | |A000111 | | | |11月16日
[10]|2月3日|1500円 | |A000111 | | | |11月29日
[11]|2月6日|1600円 | |A000222 | | | |12月4日
[12]| | | |A000111 | | | |12月6日
[13]|2月3日|1500円 | |A000333 | | | |12月11日
[14]| | | |A000222 | | | |1月4日
[15]| | | |A000444 | | | |1月4日
[16]|2月5日|2000円 | |A000555 | | | |1月11日
[17]|2月7日|2000円 | |A000333 | | | |1月12日
[18]|2月5日|2000円 | |A000555 | | | |1月14日
[19]|2月7日|2100円 | |A000555 | | | |2月3日
[20]| | | |A000333 | | | |2月3日
[21]| | | |A000555 | | | |2月4日
[22]| | | |A000555 | | | |2月4日
[23]| | | |A000555 | | | |2月4日
（マリオ） 2016/03/04(金) 19:10
To qjcyp880さん
■訂正後のW9の数式 W9=IF(AB9="","",IF(ISNUMBER(AM9),IF(OR(AK9="Ａ社",AK9="Ｂ社",AK9="Ｃ社",AK9="Ｄ社",AK9="Ｅ社",AK9="Ｆ社"),"返品済み","他店移動"),IF(ISNUMBER(Y9),"販売済み",IF(NOT(ISBLANK(AB9)),"在庫",""&""))))
W9の数式には、Y9が使用されているので、Sheet1のY,Z列に入力する数式は、W列は参照できないんでしたね。そういえば。
★W列の数式を変更して対応できないか探る。 ★W列の数式を変えないで、在庫状態であることを調べる方法を考える。 どちらか、ですよね。ん〜。
（マリオ） 2016/03/04(金) 19:22
大変お世話になっております。
また、ご連絡が遅くなり、誠に申し訳御座いません。
先に一点お伺いしたいのですが、
★W列の数式を変更して対応できないか探る。
に関しまして、以下記載の数式に変更した場合、W列は参照可能でしょうか？
W9=IF(AB9="","",IF(ISNUMBER(AM9),IF(OR(AK9="Ａ社",AK9="Ｂ社",AK9="Ｃ社",AK9="Ｄ社",AK9="Ｅ社",AK9="Ｆ社"),"返品済み","他店移動"),IF(ISNUMBER(ZZ9),"販売済み",IF(NOT(ISBLANK(AB9)),"在庫",""&""))))
ZZ9＝IF(Y9=0,"",Y9)
以上、大変お手数をお掛け致しますが宜しくお願い致します。
（qjcyp880） 2016/03/05(土) 12:48
投稿頂き、有難う御座います。
とても参考になるご回答を頂き、大変感謝しております。
問題解決へ一歩近付けました。
もし可能であれば、是非ともお力をお貸し頂きたく思います。
以上、宜しくお願い致します。
（qjcyp880） 2016/03/05(土) 12:53
To qjcyp880さん
これで3回目になりますが、3つの表を再アップしてください（★★★Sheet1はV列からAJ列まで★★★） ←マクロを使わなくても、2016/03/04(金) 10:13の表を使用すれば、できるはずです。
(1つめの表)データ反映元のシート （シート名：Sheet2）販売表 (2つめの表)データ反映先のシート「Sheet2のデータ 反映前の状態」（シート名：Sheet1）在庫表 (3つめの表)データ反映先のシート「Sheet2のデータ 反映後の状態」（シート名：Sheet1）在庫表
その後で、（qjcyp880） 2016/03/05(土) 12:48の記事の質問を考えたいと思います。 （マリオ） 2016/03/05(土) 15:59
[8] |入荷状態|商品状態| |販売日|販売価格| |商品コード|JANコード|入荷先|仕入形態|特別発注|入荷|伝票番号|備考欄| [9] | 1| | | | | |A000111 | | | |11月16日| |Ａ社 | | 1 [10]| 1| | | | | |A000111 | | | |11月29日| |G社 | | 1 [11]| 1| | | | | |A000222 | | | |12月4日 | |Ａ社 | | 1 [12]| 1| | | | | |A000111 | | | |12月6日 | |Ａ社 | | 1 [13]| 1| | | | | |A000333 | | | |12月11日| |Ａ社 | | 1 [14]| 1| | | | | |A000222 | | | |1月4日 | |Ａ社 | | 1 [15]| 1| | | | | |A000444 | | | |1月4日 | |Ａ社 | | 1 [16]| 1| | | | | |A000555 | | | |1月11日 | |Ａ社 | | 1 [17]| 1| | | | | |A000333 | | | |1月12日 | |Ａ社 | | 1 [18]| 1|他店移動| | | | |A000555 | | | |1月14日 | |Ａ社 | | 1 [19]| 1| | | | | |A000555 | | | |2月3日 | |Ａ社 | | 1 [20]| 1| | | | | |A000333 | | | |2月3日 | |Ａ社 | | 1 [21]| 1| | | | | |A000555 | | | |2月4日 | |Ａ社 | | 1 [22]| 1| | | | | |A000555 | | | |2月4日 | |Ａ社 | | 1 [23]| 1| | | | | |A000555 | | | |2月4日 | |Ａ社 | | 1
（qjcyp880） 2016/03/05(土) 23:37
大変お世話になっております。
データ反映元のシートを投稿している途中だったのですが、
上手くいかず申し訳御座いませんでした。
今回、大変お手数をお掛けし申し訳御座いませんでした。
また何か機会が御座いましたら、宜しくお願い致します。
（qjcyp880） 2016/03/06(日) 00:33
今回新たに「販売表」を作り、そのデータを「在庫表」に反映したい
というqjcyp880 さんの意向に沿って、私ならこうするかなと助言しました。
販売日（販売先）・転送等を含めた「販売表」を新たに作成することを前提としています。
また、現行のW列の数式も変更しました。
私の提示した数式をアレンジすれば、ほぼお望みの「在庫表」ができるのでは、と思います。
なお、前述の c3 式は「入荷日が記載されると自動的に在庫となり」に対応していないため、
c3=IF([入荷日欄AF3]=””,””, )
でくくってもらうとよいと思う。
W列が「在庫」である条件は無視していいのではないでしょうか。
「入荷日が記載されると自動的に在庫」になるので、
単純であれば、入荷日がないものは「在庫」とならないし販売もない からです。
この表が複雑で、
「受注表」および「販売予定表」であり、また、「先入先出」しない場合がある時などは、話は別です。
（もん） 2016/03/07(月) 16:20
To もん さん
もんさんの「販売表」と「在庫表」、それに数式 大切にファイル化して保存させて頂います。 配列数式、今回初めて知りました。勉強になり、とてもありがたいです。 「販売表」と「在庫表」について、僕自身が必要となれば、もんさんの書式を使いたいと思います。
僕の場合ですけど、配列数式が設定されたセルを気づかないうちにいじってしまって、 配列数式が解除されたことに気づかないなんてことになるかも〜。 （すいませんが、本ケースについて、熱が冷めてます。冷え切ってます。） （マリオ） 2016/03/07(月) 20:15
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.