[[20191029142822]]　『整合性の確認』（POO）　ページの最後に飛ぶ

『整合性の確認』（POO）

Sheet1のC列に商品名、L列からR列まで営業所名と
Sheet2のB列に商品名、C列に金額が整合していたら
E列に「OK」、していなかったら「FALSE」とする。

E列の商品名を探して金額が一致していたらとする
には式はどうしたらよいのか教えて下さい。

Sheet1

　　　C　　　　L　　　M　　　N　　
11　商品名　　A店　　B店　　C店　
12　りんご　　500　　10　
13　ばなな　　250　　　　　　300
14　みかん　　　　　 500
15　柿　　　　100　　600 100
16　ぶどう 30 900

Sheet2

A支店
　　　B 　　C　　E
11　商品名　　金額
12　りんご　　500　　OK
13　ばなな　　250　　OK
14　柿　　　　200　　FALSE

B支店

　　　B 　　C　　E
　　商品名　　金額
12　りんご　　10　　 OK
13　ばなな　　　
14　みかん　　500　　Ok　
15　柿　　　　600　　Ok
16　ぶどう 30 OK

C支店

　　　B 　　C E
　　商品名　　金額
64　ばなな　　300 OK
65　柿　　　　200 FALSE
66　ぶどう 90 FALSE

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 支店別のシートにSheet1からVlookup関数で　商品別の金額を引っ張って
 支店別のシートの金額と比較するIF関数を入力しては？

（渡辺ひかる） 2019/10/29(火) 16:03

少し勘違いしてました申し訳ございません。

支店は同じSheet5（例はSheet2）に全部あります。
Sheet1～4に商品ごとの支店別の表（例はSheet1）があります。

Sheet5のB12に
=IF($C12<>0,IF(VLOOKUP($B12,Sheet1!$C:$S,10,0),"OK","FALSE"))

●分からない事

Sheet1～4からSheet5、Sheet5からSheet1～4への整合性の確認をひとつの式にする方法。
Sheet1～4と複数のシートをひとつの式にする方法。

（POO） 2019/10/29(火) 16:39

 最初の質問のSheet2を見落としたのは私のミスですが、

 支店一覧のシート（Sheet5？）で　支店名はどこに入っているのでしょうか？
 A列ですか？(データの途中に支店名が入ったりしていると厄介です）

 最初に提示された例では、A支店とB支店の行番号もかぶってますし・・・・

 Sheet1～4は　シート1枚につき商品は1つなんでしょうか？

 省略してもいいですけど、正確に書いてください。

（渡辺ひかる） 2019/10/29(火) 17:46

もう一度、具体例を示します。
sheet5の支店名はA列です。
sheet1～4は具体例のように複数の商品です。

【1】sheet1～4にあってsheet5にもある。
【2】sheet1～4にあってsheet5にない。
【3】sheet1～4になくて商品5にある。

【1】～【3】の結果をsheet5のE列に導きたい。
【2】はsheet5ではできないですか？

sheet1～4

      C　　　　L　　　M　　　N　　
11　商品名　　A店　　B店　　C店　
12　りんご　　500　　10　
13　ばなな　　250　　　　　　300
14　みかん　　　　　 500
15　柿　　　　100　　600   100
16　ぶどう    30          900

sheet5

　　　A B 　　 C　　 E
9 ⬛A支店
10
11　商品名　　金額
12　りんご　　500　　OK
13　ばなな　　250　　OK
14　柿　　　　200　　FALSE

　　　A B 　　 C　　E

19 ⬛B支店
20
21　商品名　　金額
22 　りんご　　10　　 OK
23　ばなな　　　
24　みかん　　500　　Ok　
25　柿　　　　600　　Ok
26 　ぶどう 30 OK

　　　 A B 　　C E
61 ⬛C支店
62
63　商品名　　金額
64 　ばなな　　300 OK
65　柿　　　　200 FALSE
66　ぶどう 90 FALSE

（POO） 2019/10/29(火) 19:01

 まだよくわかりません

 Sheet1～4 は、すべて同じ内容になるのですか？
 Sheet5　の　15～18行、25～60行は　空白行ですか？
 OK、FALSEの例示があっていないと思います。
 商品5って何ですか？

 そもそもSheet5のレイアウトがデータ加工には向いていないと思います
 人間には見やすいのかもしれませんが。

 無理やりやるなら

 Sheet5とSheet1の比較として

 Sheet5のE12に

 =IF(VLOOKUP(B12,Sheet1!$C$12:$N$16,COUNTA($A$9:A12)+9,FALSE)=C12,"OK","FALSE")

 として、必要なセルにコピペでしょうかね

 数式は　支店名の並びが、Sheet5とSheet1　で上から、左から　で同じ順番になっていることが条件です。

（渡辺ひかる） 2019/10/30(水) 09:35

＞Sheet1～4 は、すべて同じ内容になるのですか？

同じ内容ではないので全て（sheet1～4）が対象になります。

＞Sheet5　の　15～18行、25～60行は　空白行ですか？

具体例なので省略しています。
支店ごとに表を分けているので空白があります。

＞OK、FALSEの例示があっていないと思います。

あってます。
こちらに貼り付けると列が乱れてしまうみたいです。

＞そもそもSheet5のレイアウトがデータ加工には向いていないと思います

元々の様式ですが加工も可能だと思います。
やりやすい方法がありましたら教えて下さい。

＞商品5って何ですか？

sheet5の入力ミスです。
失礼しました。

＞支店名の並びが、Sheet5とSheet1　で上から、左から　で同じ順番になっていることが条件です。

sheet1とsheet5がsheet1と同じ様式だったらということでしょうか。
これはできません。
sheet5は商品名の順番が違う場合もあります。

この式だとsheet5とsheet1が不一致だと＃N/Aとなってしまいます。
どこが問題なのでしょうか。
=IF($C12<>0,IF(VLOOKUP($B12,Sheet1!$C:$S,10,0),"OK","FALSE"))

（POO） 2019/10/30(水) 10:12

 ＞同じ内容ではないので全て（sheet1～4）が対象になります。

 掲示された内容では何が違うのかわからないです。
 例えば、商品名が異なっていてシート間でダブりがない　とか　違いを説明してください

 ＞具体例なので省略しています。 
 ＞支店ごとに表を分けているので空白があります。

 支店と支店のデータの間の状態がわからないのです。

 ＞あってます。 
 ＞こちらに貼り付けると列が乱れてしまうみたいです。

 例えば　Sheet1　の　A支店のぶどうが　30　となっていますけど
 Sheet5のA支店にはなく　B支店にありますけど　これもズレですか？

 ＞元々の様式ですが加工も可能だと思います。 
 ＞やりやすい方法がありましたら教えて下さい。

 Sheet5全体で　1行目のみを見出しとして
 2行目から空白行を作らずにデータを埋めていく感じです
 例えば支店名が上のセルと同じでも入力します。

 ＞支店名の並びが、Sheet5とSheet1　で上から、左から　で同じ順番になっていることが条件です。

 支店名と書いています。

 Sheet5では
 A支店
 B支店
 C支店

 Sheet1では
 A支店、B支店、C支店

 と並んでいるということです。

 ＞この式だとsheet5とsheet1が不一致だと＃N/Aとなってしまいます。 
 ＞どこが問題なのでしょうか。
 ＞=IF($C12<>0,IF(VLOOKUP($B12,Sheet1!$C:$S,10,0),"OK","FALSE"))

 VLOOKUPが#N/Aを返したときの処理を考慮していないからですね

 私の数式は試されたのでしょうか？

（渡辺ひかる） 2019/10/30(水) 10:50

＞掲示された内容では何が違うのかわからないです。
＞例えば、商品名が異なっていてシート間でダブりがない　とか　違いを説明してください

区分で分けているそうです。
区分はsheet5のB列に入力しようと思ってます。
（A列を支店名、B列を区分、C列を商品名、D列を金額）
9割、商品名は異なっています。
残り1割は実際違う商品だけで省略して同じ商品名になっているものがあります。
例えばエクアドルばななと沖縄等をばななとしています。

＞支店と支店のデータの間の状態がわからないのです。

B列とC列は空白です。
A列は支店名と別に1行文章を入れる予定です。

　　　A B 　　 C　　 E

 9 ⬛A支店 
10 　●●●●
 11　 商品名　　金額 
12　 りんご　　500　　OK 
 13　 ばなな　　250　　OK 
 14　 柿　　　　200　　FALSE

＞例えば　Sheet1　の　A支店のぶどうが　30　となっていますけど
＞Sheet5のA支店にはなく　B支店にありますけど　これもズレですか？

ズレです。
ぶどうはB支店とC支店です。
コメント入力では揃っていたんですが投稿するとキレイに表示できてません。

＞Sheet5全体で　1行目のみを見出しとして
＞2行目から空白行を作らずにデータを埋めていく感じです
＞例えば支店名が上のセルと同じでも入力します。

空白行までは理解できました。
今は表の外に支店名を入力してますが表の中に入れるとの理解で大丈夫ですか。
もしそうならA列を支店名、B列を区分、C列を商品名、D列を金額とsheet5の様式を
変更させて下さい。
支障があったら教えて下さい。

＞VLOOKUPが#N/Aを返したときの処理を考慮していないからですね

一部変更してみました。

・sheet5のC12が￥0の時、B12とsheet1の商品名が一致したら2列目の金額とsheet5の
金額が一致はOK、不一致はNG
・sheet5のC12の金額が￥0でない時、B12とsheet1の商品名が一致したら2列目の金額とsheet5の
金額が一致はOK、不一致はFALSE

=IF($C12=0,IF(VLOOKUP($B12,Sheet1!C:I,2,0)=$C12,"OK","NG"),IF(VLOOKUP($B12,sheet1!C:I,2,0)=$C12,"OK","FALSE"))

＞私の数式は試されたのでしょうか？

試しましたが＃REF!となりました。

（POO） 2019/10/30(水) 11:48

 ＞試しましたが＃REF!となりました。

 こちらでも　同じ状況を作って　検証してからアップしています。
 私の数式のセル範囲指定をそれぞれ確認してみてください
 数式の入力セルも確認してくださいね

 ＞区分で分けているそうです。

 後だしじゃんけんですね。
 そうするとその区分を見て、Sheet1～Sheet4を選択しなければならないということになりますね。
 まったく内容が変わりますよ。

 ＞支障があったら教えて下さい。

 区分とSheet1～Sheet4の関係がわからないとなんとも言えません

 ＞一部変更してみました。

 で、どうだったのですか？
 どのセルに入力した数式なのかわかりませんので、答えようがないです

 それに　Sheet1のレイアウトは

       C　　　　L　　　M　　　N　　
 11　商品名　　A店　　B店　　C店

 だったはずですが、なぜに　 
 VLOOKUP($B12,Sheet1!C:I,2,0)

 のように、提示されていないセル範囲を参照しているのでしょうか？

 私の数式の＃REF!　もその辺が原因かと思います。

（渡辺ひかる） 2019/10/30(水) 12:14

＞数式のセル範囲指定をそれぞれ確認してみてください
＞数式の入力セルも確認してくださいね

確認しましたら今度は＃N/Aとなりました。
COUNTA($A$9:A12)+9はどういう意味か分かりません。
VLOOKUPの参照する列番号でしょうか？？
ここの理解不足が原因ですか？

＞そうするとその区分を見て、Sheet1～Sheet4を選択しなければならないということになりますね。

区分で商品が違うからsheet1とsheet2で同じ商品名はないそうです。

＞区分とSheet1～Sheet4の関係がわからないとなんとも言えません

区分ごとにsheetが分かれてます。
どういう区分で分けているかは専門的で普通の人が見ても分からないと言われました。
IF関数でsheet1がFALSEならsheet2、sheet2がFALSEならsheet3、最終的に全部FALSE
ならFALSEとは難しいですかね。
もしくは検証シートを別に設けてsheet1～sheet4をA列区分、B列に商品名、C列以降に
金額とし、sheet5と検証シートで整合性を確認するとか？

＞Sheet5全体で　1行目のみを見出しとして
＞2行目から空白行を作らずにデータを埋めていく感じです

sheet5は空白行を埋めることはできないそうです。
例えばA列に支店名、B列に区分、D列に商品名、E列に金額とし、
C列は前のままの様式とすることは可能です。

＞で、どうだったのですか？
＞どのセルに入力した数式なのかわかりませんので、答えようがないです

同じくsheet5のE列に入力してみました。
希望と式はあってますか？
答えは返ってくるけど正解か分かりません。
さっきも商品名だけで金額は見てなく答えはあっているけど式あってなかったです。

＞VLOOKUP($B12,Sheet1!C:I,2,0)

具体例は一部省略してます。
本当のデータの式を貼り付けたので具体例とは相違する部分もあります。

（POO） 2019/10/30(水) 13:07

 ＞確認しましたら今度は＃N/Aとなりました。

 ＃N/Aならばまだわかります、検索値の値が参照先にないだけですから
 ただ最初に提示されたデータでズレも直して、アップした数式で、提示された結果と同じ値が算出できています。

 ＞COUNTA($A$9:A12)+9はどういう意味か分かりません。 
 ＞VLOOKUPの参照する列番号でしょうか？？

 そうです

 あくまで
       C　　　　L　　　M　　　N　　
  11　商品名　　A店　　B店　　C店

 のレイアウトで式を組み立てています。
 Sheet5で何番目の支店かを算出しているのが　COUNTA($A$9:A12)　です
 A支店の場合は1ですので、1＋9＝10で　10
 Cから数えてL列は10列目ですからL列を参照することになります
 Sheet1のC列に、Sheet5のB列の商品名がないだけでしょう。

 ＞IF関数でsheet1がFALSEならsheet2、sheet2がFALSEならsheet3、最終的に全部FALSE 
 ＞ならFALSEとは難しいですかね。
 ＞もしくは検証シートを別に設けてsheet1～sheet4をA列区分、B列に商品名、C列以降に 
 ＞金額とし、sheet5と検証シートで整合性を確認するとか？

 最初の質問からはそのような要望は汲み取れません。
 そういうアイデアがあるなら最初から書けないのでしょうか？

 ＞sheet5は空白行を埋めることはできないそうです。 
 ＞ 例えばA列に支店名、B列に区分、D列に商品名、E列に金額とし、 
 ＞C列は前のままの様式とすることは可能です。

 空白行ってわかりますか？その行のすべてのセルが空白である行という意味です

 ＞同じくsheet5のE列に入力してみました。 
 ＞希望と式はあってますか？

 何を聞かれているのかわかりません

 ＞答えは返ってくるけど正解か分かりません。 
 ＞さっきも商品名だけで金額は見てなく答えはあっているけど式あってなかったです。

 これも意味不明です。算出されるべき正解がわからないのですか？

 ＞具体例は一部省略してます。 
 ＞本当のデータの式を貼り付けたので具体例とは相違する部分もあります。

 最初の提示が「具体例」であるとするなら、こちらはその「具体例」で回答しています。
 その具体例を無視して、いきなり「本当のデータ」を提示もしないで数式だけ出されても
 こちらは何もできません。
 省略はしても正確に・・とは先に書いた通りです。

（渡辺ひかる） 2019/10/30(水) 14:37

 崩れないようにしただけ、デフォのずれはそのまま。
 初回

 Sheet1
 　　　C　　　　L　　　M　　　N
 11　商品名　　A店　　B店　　C店
 12　りんご　　500　　10
 13　ばなな　　250　　　　　　300
 14　みかん　　　　　 500
 15　柿　　　　100　　600     100
 16　ぶどう           30      900

 Sheet2

 A支店
 　　　B     　　C　　E
 11　商品名　　金額
 12　りんご　　500　　OK
 13　ばなな　　250　　OK
 14　柿　　　　200　　FALSE

 B支店
 　　　B     　　C　　E
 　　商品名　　金額
 12　りんご　　10　　 OK
 13　ばなな
 14　みかん　　500　　Ok
 15　柿　　　　600　　Ok
 16　ぶどう     30    OK

 C支店
 　　　B     　　C   E
 　　商品名　　金額
 64　ばなな　　300  OK
 65　柿　　　　200  FALSE
 66　ぶどう    90   FALSE

 //////////////////////////////////////
 2019/10/29(火) 19:01

 sheet1～4
       C　　　　L　　　M　　　N
 11　商品名　　A店　　B店　　C店
 12　りんご　　500　　10
 13　ばなな　　250　　　　　　300
 14　みかん　　　　　 500
 15　柿　　　　100　　600   100
 16　ぶどう    30          900

 sheet5
 　　　A      B 　　   C　　 E
 9 ⬛A支店
 10
 11　       商品名　　金額
 12　       りんご　　500　　OK
 13　       ばなな　　250　　OK
 14　       柿　　　　200　　FALSE

 　　　A     B 　　   C　　E
 19  ⬛B支店
 20
 21　      商品名　　金額
 22      　りんご　　10　　 OK
 23　      ばなな
 24　      みかん　　500　　Ok
 25　      柿　　　　600　　Ok
 26      　ぶどう    30    OK

 　　　 A      B 　　C      E
 61 ⬛C支店
 62
 63　        商品名　　金額
 64        　ばなな　　300  OK
 65　        柿　　　　200  FALSE
 66　        ぶどう    90  FALSE

 /////////////////////////
  2019/10/30(水) 11:48

 　　　A B 　　 C　　 E
  9 ⬛A支店
 10 　●●●●
 11　 商品名　　金額
 12　 りんご　　500　　OK
 13　 ばなな　　250　　OK
 14　 柿　　　　200　　FALSE

（整形屋） 2019/10/30(水) 16:03

整形屋さん、綺麗にしていただきありがとうございます。

それと、渡辺ひかるさん。
関数を始めたばかりで渡辺ひかるさんが意図していることが
理解できていないかもしれません。

当初の式は自分の意図したことになっていないそうです。
答えが同じだと今の自分には式が違うことが分かりません。
算出すべき正解が分からないのでご相談いたしました。

それと、渡辺ひかるさんとのやりとりで今の様式や検証方法だと
難しいのかもと少しでも簡単に導けるよう思い案をお伝えしただけです。

当初からやり方を変更しないほうがベストでしたらそのようにいたします。

（POO） 2019/10/31(木) 14:29

 ＞整形屋さん、綺麗にしていただきありがとうございます。

 整形屋さんは時系列でデータが変わっていることを示してくれた　と理解しています。

 それはさておき、

 私の意図しているところというのは、何度も書いていますが、
 Sheet1の各支店のデータを参照してSheet5のデータと比較するということで
 何も変わっていません。

 ＞当初の式は自分の意図したことになっていないそうです。

 「いないそうです」というのは、質問者さんの側に別な人がいるんですね？
 その人の指示で作業を行っているのでしょうか？

 「答え」とか「式」とか「算出すべき正解」とか　何を指しているのかがわからないですし
 ましてや、その真偽はわかりません。

 結局、この作業の目的・手順が確立されていないのではないですか？
 プログラムでも数式でも、手順が明確でないと、迷宮に入り込みます。

 易しい、難しいではなくて、質問者さんがやりたいことを正確に、誰が読んでもわかるように
 書いてみることをお勧めします。

 ＞当初からやり方を変更しないほうがベストでしたらそのようにいたします。

 繰り返しますが、ベストかどうかはこちらではわかりませんし、それを判断する権限も責任もありません。
 ただ、途中で　レイアウトや条件が、情報もなしに変わるのは　勘弁してほしいです。
 ましてやそれが理由で、エラーになりますとか言われても、こちらではどうしようもないんです。

（渡辺ひかる） 2019/10/31(木) 15:07

コメント返信：

[ 一覧(最新更新順) ]

[[20191029142822]] 『整合性の確認』（POO） ページの最後に飛ぶ

[[20191029142822]]　『整合性の確認』（POO）　ページの最後に飛ぶ