AKB48の推しメンをセンターにできちゃうかもしれない!? >>

複数の農家が直売所10店舗へ野菜を納品、販売して貰っているのですが、各々前年と比較して増えたのか減ったのか分かりません。(シート2018年)
会員が増えたり減ったりするので、列の順番が変わり難儀しています。生産者コードで紐づけするのが一番かな?と思いましたがやり方が不明です。(シート2019年)
また、2枚のシート上でどちらも実績ゼロの生産者(CさんIさん)が居ます(シート差異①)。このようにどちらでも実績ゼロのお客さまに関して、チエックボックスにフラグを入れれば実績ゼロ生産者は画面に反映されなければよりOKです(シート差異②)。

シート2019-シート2018=シート差異①→シート差異②(理想)
上記、解決するシート、計算式を作成可能な方、アドバイス宜しくお願い致します。

※その他参考画像は補足に入っております。

「2枚のエクセルシート上の差異を求めたい」の質問画像

質問者からの補足コメント

  • シートサンプル

    「2枚のエクセルシート上の差異を求めたい」の補足画像1
      補足日時:2019/05/07 23:04
  • シートサンプル

    「2枚のエクセルシート上の差異を求めたい」の補足画像2
      補足日時:2019/05/07 23:05
  • HAPPY

    シートサンプル
    完成形です。

    「2枚のエクセルシート上の差異を求めたい」の補足画像3
      補足日時:2019/05/07 23:07
  • 実験結果です。

    「2枚のエクセルシート上の差異を求めたい」の補足画像4
    No.1の回答に寄せられた補足コメントです。 補足日時:2019/05/08 22:21

A 回答 (4件)

こんにちは



それなりに複雑なので、考え方のみの回答です。
添付図はミニチュア版で、簡略化のために1シートで作成しています。
上段(A1:H10)が古い年度(2018年)、中段(A11:H20)が新しい年度(2019年)の表とし、下段(A21:H30)が差分を示す表としています。

まず、下段の表に生産者コードを重複無しで抽出する必要がありますが、関数で(特に作業列無しで)行うとそれなりに面倒になります。
手動で行うなどの方法を取れるのであれば、そちらの方が良いかも知れません。

添付図では、A22セルに
=IFERROR(IF(ROW(A1)<=COUNTA(A$2:A$10),A2,INDEX(A$1:A$20,SMALL(IF(NOT(COUNTIF(A$2:A$10,A$12:A$20)+(A$12:A$20="")),ROW(A$12:A$20)),ROW(A1)-COUNTA(A$2:A$10)))),"")
の式を入れ、Ctr+Shift+Enterで確定。(配列数式なので必須です)
確定後、A30まで下方にフィルコピーしています。

A列のコードができれば、それぞれの表からVLOOKUPで検索して差分を求めるだけです。
添付図では、C22セルに
=IF($A22="","",IFERROR(VLOOKUP($A22,$A$12:$H$20,COLUMN(C1),0),0)-IFERROR(VLOOKUP($A22,$A$2:$H$10,COLUMN(C1),0),0))
の式を入れ、C22:G30の範囲にフィルコピーしています。

なお、セルの書式設定で、負数は▲表示になるように設定してあります。
名前の列の式は省略しますが、同様にVLOOKUPで検索すれば上記の「値の差分」よりも簡単に求められます。


>チエックボックスにフラグを入れれば実績ゼロ生産者は画面に反映されなければよりOKです
チェックボックスがどこにあるのか不明なので式にはできませんが、「詰めて抽出」を異なる2つの範囲から行うことになるので、関数式で直接求めようとすると上記よりも相当に面倒なものになります。
要領としては、上記の判定部分を変更すれば良いですが、最初の表から抽出される項目数がCOUNTAで簡単には求められなくなるので、注意が必要になります。
作業列等を用いて、それぞれのシートに一旦抽出したものを、関数で結合する方がはるかに簡単にできるでしょう。

あるいは、生産者コードは抽出するけれど、「合計値が両方の年とも0の場合は0ではなく空白表示にする」のであれば少しは簡単になります。
上のC22セルの式で、「両方の年の合計欄が0だったら空白」という条件文を追加すれば可能です。
例示の式では、「(2019年の値)-(2018年の値)」として値を算出していますが、これを
 IF((2019年の合計値)+(2019年の合計値)=0,"",(2019年の値)-(2018年の値))
という意味の式にすれば、該当者については空白行となるので、見やすくなるのではないかと想像します。
とは言え、同じ計算を繰り返すことになるので式の長さは約2倍弱になってしまいますけれど・・・
「2枚のエクセルシート上の差異を求めたい」の回答画像1
この回答への補足あり
    • good
    • 0
この回答へのお礼

分かりにくい質問に対し、ご丁寧な回答ありがとうございます。
fujillinさんと同じシートを作成して早速実験してみました。(補足7)
結果は「セルA27のところが105になっている。(1段ずれているような)」「名前欄の列が空白」というような現象に何故かなってしまいました。

また、>の式を入れ、Ctr+Shift+Enterで確定。(配列数式なので必須です)と書いてありましたが、当方意味が分からずやり方(確定の仕方)がわかりません。基本的な事で申し訳ありませんが、その部分も教えていただけますでしょうか。

お礼日時:2019/05/08 22:30

No1、2です



>ご指摘通り、上記のやり方を行いました。
って、回答と違うことを行ったってことでしょうか?
違うことを行えば、結果が違うのは当然のことなので『実験したけど同じにならない』と言われても、「当たり前の結果」としか答えられません。
いろいろいじって実験(?)なさるのはご自由ですが、違うセルに式をいれても意味を成すものにはなりませんので、ほとんど無意味としか思えません。


>リンク先を拝見しました。配列数式全く分かりませんでした
No1、2でも繰り返し記述したように、無理やり配列数式で求める必要はありません。
C22~の関数式をセットしておけば、A列のコードに対する計算はできますので、A列のコードは手入力でもかまいません。
あるいは、一気に求めようとせずに、作業列等を用いて算出するようにすれば計算は容易になるはずです。
(同じことを繰り返し書くのは無駄なので、これ以上は書きませんが)
    • good
    • 0
この回答へのお礼

ごめんなさい、言葉足らずでした。「 fujillinさんが推察した通りの方法を最初行っていました。」と言う意味です。

お礼日時:2019/05/14 15:51

マニュアル操作でも簡単にできそうですね。


①まず、前年度シートをコピーして、値をマイナスに変更します。
②次に、上記①で作ったシートと今年度シートを統合機能で合算すれば出来上がりです。統合する際は「統合の基準」で「上端行、左端列」を共に選択することを忘れずに・・・。
名前は抜けてしまいますが、その辺は補正してください。

ちなみに、①シートの作り方ですが空白のシートを用意して、そこにペーストする際に「形式を選択して貼り付け」から「減算」を選択すれば、マイナスにすることができます。
    • good
    • 0
この回答へのお礼

ありがとうございます。「統合の基準」ですね。ただ、会員番号に紐づく「名前」が消えてしまうのではうーん、困りますね。なんか良い方法あるかこの「統合の基準」で試してみます。

お礼日時:2019/05/13 22:10

No1です



>早速実験してみました。(補足7)
回答の説明文に間違えがあるかと思い、回答文だけを参照しながら、コピペで新しく試しましたが、補足のような状態は再現しません。
ですので、こちらでは理由がわかりかねます。


ご提示の補足図では、A22、C22セルに「');」のような表示が見られますが、関数式を入力した場合の表示としては、かなりおかしなものに感じられます。
(通常なら、計算結果が表示されるかあるいはエラー表示となるはず)

ご提示の図は、あたかも、
 ・A22、C22セルに何らかの文字列を入力し
 ・A23、C23セルへ(1行ずらして)回答文中のA22、C22セル用の式を入力し
 ・それぞれを下方(右方)へフィルコピー
した結果のように見受けられます。
(A列の配列数式の設定は行なわれていない状態)


>当方意味が分からずやり方(確定の仕方)がわかりません。
配列数式については、私が下手な説明をするよりも、MSの下記リンク先等をご参照ください。

No1にも書きましたが、一発で結果を求めようとすると複雑な式になってしまうので、作業列などを用いて抽出する方が宜しいようにも思います。(計算が簡単になるので)
目的としては、A列に「重複を省いたコード一覧」あるいは「表示させたいコード一覧」を作成することです。
この目的が達成できさえするならば、方法は問いませんので。(手入力でもOKです)

https://support.office.com/ja-jp/article/%E9%85% …
https://support.office.com/ja-jp/article/%E9%85% …
    • good
    • 0
この回答へのお礼

丁寧な回答ありがとうございます。お返事大変遅くなりました。

>ご提示の図は、あたかも、
 ・A22、C22セルに何らかの文字列を入力し
 ・A23、C23セルへ(1行ずらして)回答文中のA22、C22セル用の式を入力し
 ・それぞれを下方(右方)へフィルコピー
した結果のように見受けられます。
fujillinさまのご指摘通り、上記のやり方を行いました。

リンク先を拝見しました。配列数式全く分かりませんでした><
一旦、リンク先のテキストで理解してから、再度チャレンジしてみます。
取り急ぎ中間報告まで。

お礼日時:2019/05/13 21:43

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング