[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『値のある列の内、最新の数値を別のシートに表示』（kojii）
よろしくお願いいたします。
マスターとして受注リストを作り入力しています。
注文日ごとに1列ずつ右に伸びていきます。
注文日 1/10 1/13 1/20 2/1 ･･･
【A社】
通算 1 2 3
商材イ 5 0 5 3 ･･･
商材ロ 2 0 8 1 ･･･
商材ハ 0 0 4 0 ･･･
・
・
【B社】
通算 1 2
商材イ 9 0 0 0 ･･･
商材ロ 0 5 0 0 ･･･
商材ハ 9 5 0 0 ･･･
・
・
【C社】
通算 1 2
商材イ 0 0 2 3 ･･･
商材ロ 0 0 0 4 ･･･
商材ハ 0 0 0 0 ･･･
・
・
注文の通算回数は下の関数でうまく表示させることが出来ました。
=IF(COUNT(列範囲)=0,"",COUNT(通算行の範囲)+1)
別のシートで納品書を作る必要がありますが、マスターのリストと連動させたいです。
「シート：納品書_A社」
注文日＿2/1
通算 ＿3
商材イ＿3
商材ロ＿1
商材ハ＿0
「シート：納品書_B社」
注文日＿1/13
通算 ＿2
商材イ＿0
商材ロ＿5
商材ハ＿5
・
・
常に最新の注文日の注文個数が反映されるようにするにはどうすればよいでしょうか。
お知恵をお貸しいただければ幸いです。
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
こんばんわ。
少し確認です。
1、マスターSheetの注文日は、常に右の方が新しい注文日ですか？
2、反映させるデータは通算の行に1以上の値が表示されている列のデータを参照するんですか？
3、=IF(COUNT(列範囲)=0,"",COUNT(通算行の範囲)+1)の式はマスターSheetの通算の行に入っているんですか？
空白に見えるセルにも数式は入っている？
4、A社やB社の始まりの行数は、それぞれ決まってるんですか？
それとも商品数によって都度変わるんですか？
（sy） 2017/01/07(土) 17:13
> 1、マスターSheetの注文日は、常に右の方が新しい注文日ですか？ マスターの注文日行は、常に右の方が新しい日にちです。
> 2、反映させるデータは通算の行に1以上の値が表示されている列のデータを参照するんですか？ そうなればベストですが、これは必須ではありません。 例示のB社でいうと、1/13の時点で納品書をプリントアウトしているので、 2/1の時点で注文がゼロであれば、納品書をプリントアウトしなければ良い、という人間側の判断で済むからです。 （2/1が最新の注文日であれば、B社：2/1：注文ゼロ、ゼロ、ゼロ、・・・でもOK、と言う意味です。 （通算行の値にかかわらず、常に日付の最新列（現時点での一番右の列）を反映できれば、目的は達します。
> 3、=IF(COUNT(列範囲)=0,"",COUNT(通算行の範囲)+1)の式はマスターSheetの通算の行に入っているんですか？ > 空白に見えるセルにも数式は入っている？ 通算行にはこの数式が入っています。注文があった場合は常にカウント+1されます。
> 4、A社やB社の始まりの行数は、それぞれ決まってるんですか？ > それとも商品数によって都度変わるんですか？ 始まりの行数は変わりません。
以上です。
お手数をお掛けいたしますが、引き続きご協力お願いいたします。
（kojii） 2017/01/10(火) 09:07
>=IF(COUNT(列範囲)=0,"",COUNT(通算行の範囲)+1)
商材すべて「0」でもカウントしてしまいそうだけどなぜ機能するか？は別として（SUMの間違い？）
●「マスター」シート
A B C D E F 1 注文日 1/10 1/13 1/20 2/1 2 【A社】 3 通算 1 2 3 4 商材イ 5 0 5 3 5 商材ロ 2 0 8 1 6 商材ハ 0 0 4 0 7 8 9 【B社】 10 通算 1 2 11 商材イ 9 0 0 0 12 商材ロ 0 5 0 0 13 商材ハ 9 5 0 0 14 15 16 【C社】 17 通算 1 2 18 商材イ 0 0 2 3 19 商材ロ 0 0 0 4 20 商材ハ 0 0 0 0 21
●抽出シート
A B 1 納品書 A社 2 注文日 2/1 3 通算 3 4 商材イ 3 5 商材ロ 1 6 商材ハ 0
B3 =INDEX(マスター!$1:$1048576,MATCH("*"&B$1&"*",マスター!$A:$A,0)+ROW(A1),MATCH(B$2,マスター!$1:$1))
下へコピー。
※マスターシートの商材並び、各社共通のこと。 （GobGob） 2017/01/10(火) 09:40
横から失礼します。
数式のことはよくわからないのですが、数式以前のテーマとして。
・抽出シートの 2/1 とか A社 といったものは、あらかじめ kojii さんが 各社分記入するのですか？ それとも、そこも 元シートから自動抽出してセットしたいのですか？
・後者のような気がしますが、その場合、 C社は 1/20 になるのでしょうかね？
・なにより、ちょっとわからないのが、抽出シートの利用目的というか、納品のタイミング。 仮に、ある会社からの注文が最近しばらくなかったとします。つまり、マスターシートの最初のほうに現れて以来、注文がない。 当然、ずいぶん前に、それに関しては納品しているはずですよね。 でも、このレイアウトだと、常に、その納品されているはずの情報が抽出シートにあらわれる。 納品担当は、おっ！ 早く納品しなきゃと、そう判断してしまうことってないのですか？
逆に（ もし、2/1 に A社から注文があった、続けて 2/2 にも注文があった。 そういう運用だったとして、処理を、2/2 注文を記入した後にしたら 2/1 分の注文が 抽出シートにはでてこない。 まぁ、例示サンプルを見る限り受注締めというものがあって、ある程度、注文確認と納品の間には余裕はありそうですけど。
（β） 2017/01/10(火) 09:57
これはB2の注文日を手入力する必要がありますが、これは関数化は難しいでしょうか？
「マスター」シートの注文日行、常に新しい日にちが一番右に入ります。
例示のセルでいうと「C1:AG1」までの範囲です。（31日分）
抽出シートは「A社」「B社」と、会社ごとのシートをあらかじめ用意してあります。
また、抽出シートのA列商材名とその数は常に不変です。
βさんも、ご質問ありがとうございます。
上で書いた内容で前半のお答えになっているかと思います。
後半の納品のタイミングのご質問に関しまして。
会社ごとの抽出シート＝「A社納品書」「B社納品書」・・・です。
注文があった日ごとに、マスターに注文数を手入力します。
そのまま、そのタイミング納品書をプリントアウトできる＝納品書を出す、出さないはその場で判断できる、という流れです。
うまく伝わりますでしょうか。
いままでは、マスターで集計もせずに同日付の納品書が複数になったり、注文を受けた人間と、納品書を手入力する人間が別で打ち間違い、誤発注が頻発していたので、マスターでの一元管理と納品書の完全連動化を目指しています。
最後に、
「=IF(COUNT(列範囲)=0,"",COUNT(通算行の範囲)+1)」の件。
「=IF(SUM(列範囲)=0,・・・」の方が良いですね。ありがとうございます。
説明上、注文がゼロ個のセルにも0を入力する様な書き方になってますが、実際は空欄なので機能していました。
長々と申し訳ございません。
つたない説明で混乱させてしまっているようで恐縮です。
もうしばらくおつきあいいただければ幸いです。
（kojii） 2017/01/10(火) 18:03
>>注文があった日ごとに、マスターに注文数を手入力します。 >>そのまま、そのタイミング納品書をプリントアウトできる＝納品書を出す、出さないはその場で判断できるという流れです。
あまり本線から脱線するのもなんですけど、アップされた例でいえば 1/13 に入力後、抽出シートを見ると おそらく納品済みの 1/10のA社分も表示されていますよね。
これがどうなのかなぁと疑問に思ったものですので。
それとも、各社分、それぞれの最新注文ではなく、マスタシート全体の一番右側の日付の列だけで 注文数がすべて 0 のものは除外して、抽出シートに表示するということですか？
（β） 2017/01/10(火) 20:12
＞ これはB2の注文日を手入力する必要がありますが、これは関数化は難しいでしょうか？
マスターシートA列の会社名がすべて例のように【】で囲まれているのなら、
B2 =IFERROR(LOOKUP(100,INDEX(マスター!C:AG,MATCH("【"&B1&"】",マスター!A:A,0)+1,0),マスター!C1:AG1),"")
・表示形式「日付」 ・LOOKUP の第1引数（検査値）は、通算の上限値よりも大きな数値にする 通算の上限値は「31」なので、それよりも大きい数値（32以上）にするということ 例として「100」にしている ↓ 範囲内の最大値よりも大きな数値にすると、範囲内の最後の数値を返します
ついでに、、 ■B3以降の SUMIF バージョン
B3 =IF($B$2="","",SUMIF(マスター!$C$1:$AG$1,$B$2,INDEX(マスター!C:AG,MATCH("【"&$B$1&"】",マスター!A:A,0)+ROW(A1),0)))
下コピー
■B3以降の LOOKUP バージョン（23:05追加）
B3 =IF($B$2="","",LOOKUP($B$2,マスター!$C$1:$AG$1,INDEX(マスター!C:AG,MATCH("【"&$B$1&"】",マスター!A:A,0)+ROW(A1),0)))
下コピー
ちなみに、マスターシートA列の会社名に【】を入力せず、会社名だけにしておけば
MATCH("【"&B1&"】", → MATCH(B1,
MATCH("【"&$B$1&"】", → MATCH($B$1,
会社名だけにして、表示形式〜ユーザー定義を "【"@"】" にすれば【】も表示される。 ワイルドカードを使うと別の会社を検索してしまう可能性を排除できませんよ。
参考まで （笑） 2017/01/10(火) 22:18
マスターシートA列の会社名に【】が入力されていて、ワイルドカードを使うのなら "*" → "?" にして、
MATCH("?"&B1&"?",
MATCH("?"&$B$1&"?",
の方がいいかも。
参考まで （笑） 2017/01/10(火) 22:33
B3の SUMIF、LOOKUP バージョンは、どんな感じになるか試してみたのを参考までに提示しただけ。 お勧めは、INDEX 〜 MATCH バージョンです（一番わかりやすいので）
提示したB2の式との統一感を持たせると、
B3 =IF($B$2="","",INDEX(マスター!C:AG,MATCH("【"&$B$1&"】",マスター!A:A,0)+ROW(A1),MATCH($B$2,マスター!$C$1:$AG$1,0)))
下コピー
・マスターシート1行目の日付は昇順になっている ・店別シートのB2の日付は、マスターシート1行目に必ず存在する
なので、2つ目の MATCH の第3引数（照合の型）は「1」または省略でも可。 1つ目の MATCH は「0」にすること。
参考まで （笑） 2017/01/11(水) 00:38
個別の関数の目的はなんとか理解できても、入れ子構造になるとちんぷんかんぷんです。
いまは単純にコピペしただけですが、今後自分でメンテしていけるように、例示していただいた計算式をほぐしながら、何を指定してどう機能する理屈なのか、かみしめたいと思います。
βさん、すみません。質問をシンプルにするために説明をはしょったり表を単純化したりしているので、整合性のない内容になっているかと思います。ご指摘の部分は、今後の運用の中で起こりうるミスの要因ですので、実際の表に落としこむ際には注意したいと思います。
改めまして、GobGobさん、βさん、笑さん、ご協力ありがとうございました。
（kojii） 2017/01/11(水) 10:10
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.