関数で、作業列等を使って検索後、抽出転記について

質問

関数で、作業列等を使って検索後、抽出転記について教えてください。

https://box.yahoo.co.jp/guest/viewer?sid=box-l-grnuwmf6uml2qj5tso7y5qunzy-1001&uniqid=91b8b56e-b451-4c66-8dd0-3796ca0c0cf6&viewtype=detail

Excelファイルのシート名別ブックがネットワーク上にあるブックです。
このExcelファイルを参照して、シート2のように表示をさせたいのですが、可能でしょうか？
シート2のB列については、=VLOOKUPで表示さすことは可能なのですが、

参照するブックのつくり方が悪く、縦にデータがはいってしまっており、参照ブックの変更はシート数、
商品件数が多いため、作り直すことが不可能なため、
もし何かの方法を使って出来るようなら教えて欲しいです。

Upしたファイルの通り、CODEは結合されており、（重複はありません）
CODEに対して倉庫も2つの場合と4つの場合があります。

CODEに対して、最低6行、最高12行です。

倉庫の並び順は、全シート共通して、
結合セルが6行の場合、
1行目から3行目が倉庫A、4-6行目が倉庫Bです。
結合セルが9行の場合、
1行目から3行目が倉庫A、4-6行目が倉庫C、7-9行目が倉庫Bです。
結合セルが12行の場合、1-3行目が倉庫A、4-6行目が倉庫C、7-9行目が倉庫D、10-12行目が倉庫Bです。

このような決まりしかなく、もしD列のセルの色で判断できるのであれば、
倉庫Cと倉庫Dは同じ色のため、それを別の色に変えることは可能です。

ネットワーク上にあるブックを全てのシート、全ての商品を作り変えることは難しいのですが、
多少手を加えることなら可能です。

何か出来る方法があると言う方がいらっしゃれば教えてください。

よろしくお願い致します。

回答No.7

tom04 · ベストアンサー

No.6の補足について・・・

Excel2012なるものを使ったコトがないので、当方も理由が判りません。
一旦締め切って、新たな質問としてみてはどうでしょうか？

より多くの方の意見を聞けばそのような経験をされている方もいらっしゃると思います。

お役に立てなくてごめんなさいね。m（_ _）m

お礼

本当にいろいろ教えて頂いてありがとうございました！！助かりました。

回答No.6

tom04

No.5の補足
＞エラーとはなってますが、ちゃんとA列のコードが入っておりますので、これはエラーにはならないのでしょうか？
について・・・

エラーチェックオプションのコトですよね？
エラーの場合はちゃんと
#VALUE　とか　#REF!　等（他にもエラーは色々あります）という文字がちゃんと表示されます。
エラーチェックオプションはExcelのおせっかい機能の一つで、エラーとまでは言えません。

おそらくアップされているファイルのエラーチェックオプションは
もう一度上のセルの数式を下に向かってドラッグ＆コピーで表示されなくなると思います。

※　前回の作業列の数式は途中の行に行挿入してもエラーにならないような数式にしたつもりです。
ただし、挿入された行には数式は入っていませんので、上の行の数式を
そのままオートフィルでコピーしてやる必要があります。
そうすればおそらくエラーチェックオプションは表示されないと思います。m（_ _）ms

お礼

ありがとうございました！！

補足

ありがとうございます。

これでしばらく使ってみようと思います。

もう一つ教えてほしいのですが、関数とは関係ないとは思いますが、エクセル2012のPCが数台あり、2010、2007で使用すると問題は起こらないのですが、2012で開くと保護ビューで使用するとでてしまい、編集を有効にするにすると、突然教えて頂いた関数の箇所全てが#VALUEとなってしまうのですが、何が原因なのでしょうか？

2012の設定を変えると解決するのでしょうか？

回答No.5

tom04

続けてお邪魔します。

最初の質問が「関数で！」というコトでしたので、
関数の場合は行挿入・列挿入してしまうと不具合が生じる場合があります。
本来であれば行・列挿入は好ましくないのですが、
行・列挿入の影響を最小限にしたい場合は作業列の数式に少し手を加えます。
表の配置が最初に戻っているみたいなので

E2セル
=IF(D2="","",IF(A2="",E1,A2))
F2セル
=IF(E2="","",COUNTIF(E$2:E2,E2))
G2セル
=IFERROR(IF(COUNTIF(E:E,E2)=3,"倉庫A",INDEX(IF(COUNTIF(E:E,E2)=6,{"倉庫A","倉庫B"},IF(COUNTIF(E:E,E2)=9,{"倉庫A","倉庫C","倉庫B"},{"倉庫A","倉庫C","倉庫D","倉庫B"})),INT((F2+2)/3))),"")
H2セル
=IF(G2="","",INDEX({"(1)","(2)","(3)"},IF(MOD(ROW(A1),3)=0,3,MOD(ROW(A1),3))))
という数式にそれぞれ変更　→　E2～H2セルを範囲指定　→　H2セルのフィルハンドルで
今度はしっかり下へコピーしておいてください。
（データがなくても構いませんのでこれ以上データが増えることはない！というくらいまでコピーしておきます）

これで行挿入してもSheet2の結果は変わらないと思います。
（行挿入するとその行は数式がありませんので、数式をフィル＆コピーしてやる必要があります）

次に列挿入ですが
参照先がA～D列でそれ以降のE列以降であれば作業列の数式に影響はありません。
（作業列の数式そのものが行に関して絶対参照・列に対しては相対参照の複合参照のため）

※　関数での方法の場合は極力列・行挿入は避けた方が良いと思います。m（_ _）m

お礼

何回も教えて頂いて感謝致します。

補足

何度もごめんなさい。

https://box.yahoo.co.jp/guest/viewer?sid=box-l-grnuwmf6uml2qj5tso7y5qunzy-1001&uniqid=a65e2cfa-4388-447b-b15d-1a28e728f568&viewtype=detail

こちらにもう一度入れさせていただたのですが、
商品に関して一番下ではなく、途中の行に商品別に挿入されることがあるため、

一番下にコピー専用をいれていて、
コピー用をコピーして、入れたい行をクリックして、
コピーしたセルの挿入で今はいれているのですが、

公開ファイルで言うと、別ブックG44がエラーみたいになっており、ただエラーにはなっておりますが、結果は間違っていないので、このエラーはほっておいても問題はないでしょうか？

列に関して、作業列を移動したところ、シート2の方は自動で列が変わっていたので、教えていただた通り、
問題はなかったです。

出来る限り避けておいた方が良いと言うことなのですが、どうしても行の挿入、列が毎月変わってしまうため、もしエラーになるなら、手直しでするようにします。

グループ会社で使用している在庫表のため、どうしてもこれを綺麗にわかりやすく作り直すのが困難で申し訳ないのですが、

G44のエラーが問題ないのであれば、この関数で使えるのではないかなとおもっております。

エラーとはなってますが、ちゃんとA列のコードが入っておりますので、これはエラーにはならないのでしょうか？

本当に何度も申し訳ありません。

回答No.4

tom04

No.1・3です。

＞別ブックF列がIF列、
＞別ブックG列がIG列、
＞別ブックH列がIH列となっており、
というコトですので、
「公開ファイル」のA列はIE列になっている訳ですね？

今回も作業用の列をII列～IL列までの4列設けます。
尚「別ブック」のデータは8行目からあるいうコトですので

II8セルに
=IF(IE8="",II7,IE8)
IJ８セルに
=COUNTIF(II$8:II8,II8)
IK８セルに
=IF(COUNTIF(II:II,II8)=3,"倉庫A",INDEX(IF(COUNTIF(II:II,II8)=6,{"倉庫A","倉庫B"},IF(COUNTIF(II:II,II8)=9,{"倉庫A","倉庫C","倉庫B"},{"倉庫A","倉庫C","倉庫D","倉庫B"})),INT((IJ8+2)/3)))
IL8セルに
=INDEX({"(1)","(2)","(3)"},IF(MOD(ROW(A1),3)=0,3,MOD(ROW(A1),3)))

という数式を入れII8～IL8セルを範囲指定　→　IL8セルのフィルハンドルでダブルクリック！
各作業列の表示を確認してみてください。
なんとなく、作業列の意味が分かると思います。

Sheet2の配置は「公開ファイル」通りだとすると
D2セル
=SUMIFS(別ブック!$IH:$IH,別ブック!$II:$II,$A2,別ブック!$IK:$IK,$C$1,別ブック!$IL:$IL,D$1)

H2セル
=SUMIFS(別ブック!$IH:$IH,別ブック!$II:$II,$A2,別ブック!$IK:$IK,$G$1,別ブック!$IL:$IL,H$1)

L2セル
=SUMIFS(別ブック!$IH:$IH,別ブック!$II:$II,$A2,別ブック!$IK:$IK,$K$1,別ブック!$IL:$IL,L$1)

P2セル
=SUMIFS(別ブック!$IH:$IH,別ブック!$II:$II,$A2,別ブック!$IK:$IK,$O$1,別ブック!$IL:$IL,P$1)

という数式を入れそれぞれを3列右にフィルハンドルでコピー　→　そのまま下へコピー！

これで何とかご希望通りにならないでしょうか？

※　前回、当方の書き方が悪かったと思いますが
丸文字　→　○の囲い文字　です。
今回も（1）（2）（3）　は○の囲い文字に変更してみてください。

それとINT関数の使い方ですが
IK列の
＞INT((IJ8+2)/3)
部分は
作業列IJ列の数値を利用して、倉庫A～倉庫DをINDEX関数で配列に配置させ、
その中の何番目か？を表示させています。

IJ列が1～3の場合はINDEX関数の配列の1番目
4～6はINDEX関数の配列の2番目
7～9はINDEX関数の3番目
をそれぞれ返しなさい！という数式です。

試しに使っていないセルの8行目に
=INT((IJ8+2)/3)
という数式を入れフィルハンドルで下へコピーしてみてください。
1～3のいずれかが三つずつ続けて表示されます。
結局INDEX関数で配列の中の何番目か？がこの数値となります。m（_ _）m

お礼

色々解決できて、仕事がスムーズにいきそうです。
ありがとうございました。

補足

詳しく教えて頂いてありがとうございます。

全くの初心者で申し訳ありません。これでも検索して意味とか調べたのですが、記載頂いた通りすれば、希望通りの形になりました！！

先ほどお礼で書かせて頂いたのですが、

すいません、質問良いですか？

E2セルに
=IF(A2="",E1,A2)
F2セルに
=COUNTIF(E$2:E2,E2)
G2セルに
=IF(COUNTIF(E:E,E2)=3,"倉庫A",INDEX(IF(COUNTIF(E:E,E2)=6,{"倉庫A","倉庫B"},IF(COUNTIF(E:E,E2)=9,{"倉庫A","倉庫C","倉庫B"},{"倉庫A","倉庫C","倉庫D","倉庫B"})),INT((F2+2)/3)))
H2セルに
=INDEX({"(1)","(2)","(3)"},IF(MOD(ROW(A1),3)=0,3,MOD(ROW(A1),3)))

これについて、行の挿入をする場合があり、その際に、
=IF(A2="",E1,A2)

ここの部分だけ、行を挿入した下がエラーになるときがあるのですが、これはしょうがないのでしょうか？

大きなエラーではないのですが、
=IF(B544="",IG543,B544)
=IF(B545="",IG544,B545)
この間に行を挿入した場合、
挿入した上下の関数が
=IF(B545="",IG535,B545)
=IF(B554="",IG544,B554)
となってしまいます。
ビックリマークが出て、上の数式をコピーすると押すと戻るのですが、毎回この作業をした方が良いですか？

特に問題はないので、緑色の三角マークが出るだけなのですが、ほっておいても大丈夫でしょうか？

もう一つが、作業列を入れている参照元データの方に、
毎月列が増えることがあるので、月に一つづつ列がづれていくのですが、
=SUMIFS(別ブック!$D:$D,別ブック!$E:$E,$A2,別ブック!$G:$G,$C$1,別ブック!$H:$H,D$1)

これを
=SUMIFS(別ブック!$D:$D,別ブック!E:E,$A2,別ブック!G:G,$C$1,別ブック!H:H,D$1)

に変えれば、自動的にづれてくれるのでしょうか？
これに変えるとまずいですか？

絶対参照の方が良いですか？

こちらも教えて頂けませんでしょうか？

回答No.3

tom04

No.1です。

＞こちらをためしたところ、全て0と表記されてしまいます。

前回は「公開ファイル」にそのまま数式を入れ、ちゃんと表示されましたので
その数式を投稿しました。

ひとつ考えられるコトは
画面上では（1）（2）（3）のようになっていますが、「公開ファイル」は丸文字になっていますよね？
今一度数式が丸文字になっているかどうか確認してみてください。
前回の数式をそのままコピー＆ペーストでは「0」が表示されてしまうと思います。m（_ _）m

お礼

ごめんなさい、もしかするとまだCODEが決まっていない商品がある場合があり、CODEA列が空白の場合があるのですが、それが原因かもしれません、、

何か数字を入れた方が良いでしょうか？

補足

すいません、前回の意味がわかっていなかったようで、きちんと丸文字に変えたら関数できました！！実際の別ブックに試したところ、倉庫の判別が倉庫Bなのに倉庫Cになる場合があるのですが、

実際の表は8行目からデータが入っていて、公開させて頂いた別ブックE列が実際の表はIE列、
別ブックF列がIF列、
別ブックG列がIG列、
別ブックH列がIH列となっており、

=IF(COUNTIF(IE:IE,IE8)=3,"倉庫A",INDEX(IF(COUNTIF(IE:IE,IE2)=6,{"倉庫A","倉庫B"},IF(COUNTIF(IE:IE,IE8)=9,{"倉庫A","倉庫C","倉庫B"},{"倉庫A","倉庫C","倉庫D","倉庫B"})),INT((IF8+2)/3)))

これであっていますか？

INTの意味がいまいちわかってないのですが、他の倉庫はあっているのですが、6行データの部分だけ、倉庫Bのはずが、倉庫Cとでてしまいます。

原因わかりますか？

回答No.2

nishi6

自分ならこうするかな・・という方法を書いてみます。Excel2010です。

まず、データがある別ブックの扱いですが、これが複数あれば１つのブックにまとめます。

まとめたブックの各セルに下記のように入力します。１行目は表題です。本来のデータから１列空けています。

セルF1:code・・・結合してあるので各行にcodeを振っている。
セルF2:=IF(A2="",F1,A2)

セルG1:通番・・・同じコード内の番号です。
セルG2:=IF(A2<>"",1,G1+1)

セルH1:倉庫番号・・・これが一番長いです。倉庫ABCDを特定しています。
セルH2:="倉庫"&MID(IF(COUNTIF(F:F,F2)=9,"ACB",IF(COUNTIF(F:F,F2)=6,"AB","ACDB")),INT((IF(A2<>"",1,G1+1)-1)/3+1),1)

セルI1:区分・・・倉庫内での番号（1、2、3）
セルI2:=MOD(ROW()-2,3)+1

セルJ1:全体・・・全体(C)列、多分倉庫(D)列の計。集計には不要ではある。
セルJ2:=IF(C2="",J1,C2)

セルK1:倉庫
セルK2:=D2

F列からK列の２行目をデータ分コピーします。

基本的にはこれで終わりです。後は「挿入タブ」＞「テーブルグループ」＞「ピボットテーブル」で集計します。ピボットテーブルの結果とお望みの最終形が違うかもしれませんが、ピボットテーブルからすぐ作れるでしょう。

ピボットテーブルを使っていれば質問の集計以外でも簡単に作れます。

補足

設定は終わりまして、全てきちんと入ったのですが、ピボットで作ると集計となってしまい、シート2のように作ることができませんでした。

ピボットでもシート2のように作れるのでしょうか？

ちなみにExcelは2010です。

回答No.1

tom04

こんばんは！

関数での方法をご希望のようですので・・・
無理やりやってみました。
「公開ファイル」の配置通りとしています。
Excel2007以降をお使いだという前提です。
（SUMIFS関数を使用しているため）

Sheet「別ブック」に作業用の列を4列設けてみます。
E2セルに
=IF(A2="",E1,A2)
F2セルに
=COUNTIF(E$2:E2,E2)
G2セルに
=IF(COUNTIF(E:E,E2)=3,"倉庫A",INDEX(IF(COUNTIF(E:E,E2)=6,{"倉庫A","倉庫B"},IF(COUNTIF(E:E,E2)=9,{"倉庫A","倉庫C","倉庫B"},{"倉庫A","倉庫C","倉庫D","倉庫B"})),INT((F2+2)/3)))
H2セルに
=INDEX({"(1)","(2)","(3)"},IF(MOD(ROW(A1),3)=0,3,MOD(ROW(A1),3)))
という数式をそれぞれ入れ、F2～H2セルを範囲指定　→　H2セルのフィルハンドルでダブルクリック！

これで「別ブック」に何とか規則性が表示されると思います。
これを利用して
Sheet2のD2セルに
=SUMIFS(別ブック!$D:$D,別ブック!$E:$E,$A2,別ブック!$G:$G,$C$1,別ブック!$H:$H,D$1)
という数式を入れ右方向に3列フィルハンドルでコピー！　→　そのまま下へコピー！

H2セルに
=SUMIFS(別ブック!$D:$D,別ブック!$E:$E,$A2,別ブック!$G:$G,$G$1,別ブック!$H:$H,H$1)

L2セルに
=SUMIFS(別ブック!$D:$D,別ブック!$E:$E,$A2,別ブック!$G:$G,$K$1,別ブック!$H:$H,L$1)

P2セルに
=SUMIFS(別ブック!$D:$D,別ブック!$E:$E,$A2,別ブック!$G:$G,$O$1,別ブック!$H:$H,P$1)

という数式を入れそれぞれをD2セルと同じ操作をしてみてください。

※　「0」が目障りであれば（Excel2010以降の場合）
ファイル　→　オプション　→　詳細設定　→　「次のシートで作業するときの・・・」　→　「ゼロ値のセルにゼロを表示する」のチェックを外しておきます。

※　H2セル数式の丸文字の1・2・3は環境依存文字になりますので、画面上では
（1）（2）（3）のように表示されるかもしれませんが、
実際の数式は丸文字にしてください。

※　VBAでやるとしても同様の作業をコードにするだけだと思います。m（_ _）m

お礼

すいません、質問良いですか？

E2セルに
=IF(A2="",E1,A2)
F2セルに
=COUNTIF(E$2:E2,E2)
G2セルに
=IF(COUNTIF(E:E,E2)=3,"倉庫A",INDEX(IF(COUNTIF(E:E,E2)=6,{"倉庫A","倉庫B"},IF(COUNTIF(E:E,E2)=9,{"倉庫A","倉庫C","倉庫B"},{"倉庫A","倉庫C","倉庫D","倉庫B"})),INT((F2+2)/3)))
H2セルに
=INDEX({"(1)","(2)","(3)"},IF(MOD(ROW(A1),3)=0,3,MOD(ROW(A1),3)))

これについて、行の挿入をする場合があり、その際に、
=IF(A2="",E1,A2)

ここの部分だけ、行を挿入した下がエラーになるときがあるのですが、これはしょうがないのでしょうか？

大きなエラーではないのですが、
=IF(B544="",IG543,B544)
=IF(B545="",IG544,B545)
この間に行を挿入した場合、
挿入した上下の関数が
=IF(B545="",IG535,B545)
=IF(B554="",IG544,B554)
となってしまいます。
ビックリマークが出て、上の数式をコピーすると押すと戻るのですが、毎回この作業をした方が良いですか？

特に問題はないので、緑色の三角マークが出るだけなのですが、ほっておいても大丈夫でしょうか？

もう一つが、作業列を入れている参照元データの方に、
毎月列が増えることがあるので、月に一つづつ列がづれていくのですが、
=SUMIFS(別ブック!$D:$D,別ブック!$E:$E,$A2,別ブック!$G:$G,$C$1,別ブック!$H:$H,D$1)

これを
=SUMIFS(別ブック!$D:$D,別ブック!E:E,$A2,別ブック!G:G,$C$1,別ブック!H:H,D$1)

に変えれば、自動的にづれてくれるのでしょうか？
これに変えるとまずいですか？

絶対参照の方が良いですか？

補足

ありがとうございます。
別ブックの方はうまくいけたのですが、

これを利用して
Sheet2のD2セルに
=SUMIFS(別ブック!$D:$D,別ブック!$E:$E,$A2,別ブック!$G:$G,$C$1,別ブック!$H:$H,D$1)
という数式を入れ右方向に3列フィルハンドルでコピー！　→　そのまま下へコピー！

H2セルに
=SUMIFS(別ブック!$D:$D,別ブック!$E:$E,$A2,別ブック!$G:$G,$G$1,別ブック!$H:$H,H$1)

L2セルに
=SUMIFS(別ブック!$D:$D,別ブック!$E:$E,$A2,別ブック!$G:$G,$K$1,別ブック!$H:$H,L$1)

P2セルに
=SUMIFS(別ブック!$D:$D,別ブック!$E:$E,$A2,別ブック!$G:$G,$O$1,別ブック!$H:$H,P$1)

という数式を入れそれぞれをD2セルと同じ操作をしてみてください。

こちらをためしたところ、全て0と表記されてしまいます。

何故でしょうか？？