【EXCEL】 条件に当てはまるものだけ別ファイルに毎回表示

質問

今簡易版承認システムを作っているのですが、どうしても前に進めなくて困っています。。どなたか分かる方がいらっしゃいましたらぜひお力をお貸しいただきたいです！

A、B、Cと3種類のファイルがありまして、「承認用ファイル」という別ファイルのsheet1,sheet2,sheet3にそれぞれリンクで情報を表示させています。
（A、B、Cのファイルは管理表であり毎日登録情報が追加されていく為、入力があると「承認用ファイル」にも反映されます。）

やりたいことは以下です。

●(1)その「承認用ファイル」に入った情報で、承認が「未」のものだけを別ファイル「上司承認ファイル」の１つのsheet内に表示させる。
●(2)上司が「上司承認ファイル」にあるチェックボックスをオンにするとそのページにあるもの全てに承認がおりる。
●(3)承認がおりたものは「承認用ファイル」では「未」が「済」になって、次からは「上司承認ファイル」には表示されなくなる。

というシステムを作りたく、●(1)～(3)までリンクやEXCELの数式だけで流れだけはできたのですが、どうしても

《1》【承認が「未」のものだけを毎回「上司承認ファイル」の表の定位置に表示させ、「済」になったものは表示させない】ということと、
《2》【チェックボックスをオンにし「承認」にした後保存し、次ファイルを開いたときはそのチェックボックスがオフになっていてまた「承認」することができる】ようにすることができず、非常に困っています。。。

このような仕組みは可能なのでしょうか？？
もし可能でしたら、やはりこれはマクロをくまなければ不可でしょうか？

VBA初心者の為なるべくここまで数式だけでやってきたのですが、
どなたか分かる方やアドバイスを下さる方がいらっしゃいましたら、どうぞ宜しくお願いいたします！！
不明な点がございましたらできるだけすぐにお答えできるようにしますので何卒宜しくお願いします。。＊

回答No.2

MackyNo1 · ベストアンサー

＞そしてその「承認用ファイル」から「上司承認用ファイル」にまた「=＋」でデータをひっぱってきている状態です。。。

現在までのファイルは単純に他ブックのセルを順次リンクさせているだけの状態ということでしょうか？

たとえば、ブック名が「AA」でシート名が「承認シート」という承認用ファイルのE列（済などの文字を入力）が未記入の行のデータだけを「上司承認用ファイル」に引っぱってきたい場合は以下の式を入力して、右方向および下方向にオートフィルします（日付の欄は数式を少し工夫する必要があります）。

=INDEX([AA.xls]承認シート!$A:$A,SMALL(INDEX(([AA.xls]承認シート!$E$2:$E$11<>"")*1000+ROW($E$2:$E$11),),ROW(A1)))&""

今回のようなケースでは、上記のような配列数式など、複雑な数式を多用する必要がありますので、紛れが多い他ブックからのセル参照よりも、まずブックを分けずに(別シートでまたは同じシート内で）数式を作成する練習をされたほうが、結局目的地に早く着くことができるのではないかと思います（まず同じシートで特定の列が空白のセルを抜き出す式をマスターしてください）。

補足ですが、前回の回答では（２）で「関数だけで処理する場合は、元データには何も記入せず、承認用ファイルで「済」を入力してこれを元データに反映する数式を入力しておきます。」と回答しましたが、これを直接行うと循環参照になりますので、やはり一部マクロを利用する手順が必要になると思います。

いずれにしろ、ご希望の機能がすべて満たされるファイルを作るには何段階かのクリアしなければならない問題がありますので、まず第一段階の特定の列の空白セルの行を抜き出す関数がうまくいくかチャレンジしてください。

最終的には、やりたい事や、ご自分でできない部分をピンポイントで絞って質問されたほうが具体的な回答が得られると思います。

お礼

＞現在までのファイルは単純に他ブックのセルを順次リンクさせているだけの状態ということでしょうか？

はい。まさにその通りです…
そして上司承認用ファイルでは
　　　A　　　　B　　　　　C　　　　　D　　　　E　　　　　
8　番号　発行日　有効期限　氏名　承認状況
9
10
：
とし、E9に=IF(D12="","",IF($J$4=TRUE,"承認","未承認"))
という数式を入力してチェックボックスをオンにすると
リンクしたJ4がTRUEとなり、承認が下りて「承認用ファイル」のE列にも
「済」と表示されるようになっている状態です。。

ですがMackyNo1さんのアドバイスのおかげで
未記入の行のデータだけを「上司承認用ファイル」に引っぱってくることが可能だとわかりましたので、教えていただいた数式をもとに、まずは特定の列が空白のセルを抜き出す方法にチャレンジしてみようと思います。

またわからなければ次回はきちんとピンポイントで絞って質問したいと思います。ご意見本当にありがとうございました。

補足

何度もすみません！
教えていただいた式を参考に、何度か練習をし、
抜き出すところまではできたのですが、
やはりオートフィルをすると日付のところがおかしな表記に
なってしまいます。（発行日、有効期限）
何かヒントをいただけないでしょうか？？>_<

ちなみにファイルBBのA12のセル

=INDEX([【AA】.xlsx]BB!$A$9:$D$28,MATCH(ROW()-11,[【AA】.xlsx]B!$G$9:$G$28,0),COLUMN())
とし、右方向及び下方向にオートフィルしています。

●ファイルAA（Sheet1)
　　　　A　　　　 　B　　　　　 C　　　　　　 D　　　　　　E　　　　　
8　　 番号　　 発行日　　 有効期限　　 氏名　　承認状況
9　　　301　　2009/10/5　 2009/4/3　　●●　　　　
10　　302　 2009/11/5　　　2009/5/4　　○○　　　
：　　　　：　　　　　：　　　　　　　　　：　　　　　：　　　　　　：

●ファイルBB
　　　　　　　　　　　　　　(Sheet1の表)　　　　　　　　　　　
　　　　　A　　　　　　B　　　　　　　C　　　　　　　D　　　　　E　　　　　　
11　　　番号　　　発行日　　　有効期限　　　氏名　　承認状況
12　　　301　　　？？　　　　　 ？？？　　　　　●●　　　　
13　　　302　 　　？？　　　　　？？？　　　　　○○　　　
：　　　　　：　　　　　：　　　　　　　　：　　　　　　：　　　　　　　：

宜しくお願いします。

回答No.4

MackyNo1

>教えていただいた式を参考に、何度か練習をし、
抜き出すところまではできたのですが、
やはりオートフィルをすると日付のところがおかしな表記に
なってしまいます。（発行日、有効期限）

数式を複雑にしないため日付のセルもひとまず同じ式で対応するように回答しましたが、数字のセルでは式の最後の「&""」をつけない数式にして、セルの書式を日付にしてください。

その場合、データがない部分はエラー表示されますので、配列数式で文字列を返すセルを参照して、そのセルが空白の場合は空白を返すIF分を追加してください。

すべての列を配列数式で処理すると計算負荷が高くなるので、もしA列の番号が重複がないなら、この番号だけ配列数式で求めて、それ以外の列はVLOOKUP関数で引っ張ってくるほうが合理的です。

この場合、提示した配列数式は文字列の数字を返すので、この文字列数字のセルを参照する場合は以下のようにして数値化して検索する式にして下さい。

=VLOOKUP(A9*1,・・・・・・・・・・）

お礼

MackyNo1さん

すごい！
きちんと表示できるようになりました！！
ありがとうございます！

また何かあった際はどうぞ宜しくお願いいたします!!!!

回答No.3

imogasi

情況がややこしく書いてあるが、条件による他シートに抜きだしをしたいのだろう。他シートぐらいでいい加減ややこしい。この質問は他ブックへ。
抜き出しは関数では向いてない課題で、ＶＢＡやアクセスをやれる人向けの課題だ。それに加え他ブックと関連させているので初心者には勉強する点が増える。
関数ででも処理を希望するのかと思ったら、ＶＢＡでやりたいということか。であれば表題にでも、質問の早いうちにＶＢＡでと明記すべきだ。
関数では他シート程度の場合、Ｇｏｏｇｌｅで「imogasi方式」で照会すれば、抜き出し問題の例が沢山出てくる。私の回答したものが出るが、方法（回答者の回答は）は他の方が色々と出している。
ーー
ＶＢＡでの回答は、コードを書いてくれという、丸投げになるので規約違反で書かない。
（１）検索の操作をマクロの記録を取り、勉強し、条件に合う全ての行を捉えられるコード
（２）他ブックのシートに、それら見つかった行のデータを写すコード
（３）それら（１）（２）を実行するキッカケと結びつける仕組み
などを勉強のこと。思い立ったとき改めて、プログラムを実行するならこの仕組みはいらない。
何しろ別ブックを扱うコートは一段と難しさを増すのだ。
ーー
質問には、実情を簡素化した各ブックのシートの列・行、セルのデータを示して質問してほしい。私の経験からデータ行数は各々５行ぐらいで良いのでは、と思う。

補足

アドバイスありがとうございます。
ややこしい書き方をしてしまい、申し訳ございません。
おっしゃる通り、条件による抜き出しをしたかったのですが
質問の仕方が未熟でございました。
反省しております。

尚、言葉足らずでしたが、
できましたら関数での処理を希望しています。

不可と言われた場合はVBAを勉強して取り組もうと思っていたのですが
まずは教えていただきましたＧｏｏｇｌｅの「imogasi方式」での例を参考にして
勉強してみたいと思います。

また遅いかと思いますが「実情を簡素化した各ブックのシートの列・行、セルのデータを示して質問してほしい」というご意見をいただきましたので
簡単にではございますが、表記させていただきます。

●承認用ファイル（Sheet1)
　　　　A　　　　 　B　　　　　 C　　　　　　 D　　　　　　E　　　　　
8　　 番号　　 発行日　　 有効期限　　 氏名　　承認状況
9　　　301　　2009/10/5　 2009/4/3　　●●　　　　未
10　　302　 2009/11/5　　　2009/5/4　　○○　　　 未
：　　　　：　　　　　：　　　　　　　　　：　　　　　：　　　　　　：
●上司承認ファイル
　　　　　　　　　　　　　　(Sheet1の表)　　　　　　　　　　　⇒右側（Sheet2の表）
　　　　　A　　　　　　B　　　　　　　C　　　　　　　D　　　　　E　　　　　　F　　　　　G
8　　　番号　　　発行日　　　有効期限　　　氏名　　承認状況
9　　　　301　　2009/10/5　 2009/4/3　　●●　　　　未承認
10　　　302　 　2009/11/5　　2009/5/4　　○○　　　 未承認
：　　　　　：　　　　　：　　　　　　　　：　　　　　　：　　　　　　　：

このようになっております。

管理表→承認用ファイル→上司承認用ファイル
→の所は各々=+[【承認申請用ファイル】.xlsx]sheet1!$A9のように、「=+」でリンクさせている状態です。

承認用ファイルで使用しているシートは4種類あり、上司承認ファイルでは
その4種のシートの表を右に並べています。

上の図だと、G列8行からまた番号、発行日、有効期限…となります。

重ね重ね分かりにくい表現で申し訳ございません。。
貴重なご意見ありがとうございます。

回答No.1

MackyNo1

元データのリストがどのような形式になっていのかわからないので、一般的な回答になりますが、

＞●(1)その「承認用ファイル」に入った情報で、承認が「未」のものだけを別ファイル「上司承認ファイル」の１つのsheet内に表示させる。

元データがリスト形式になっているなら関数だけで表示することが可能です。

＞●(2)上司が「上司承認ファイル」にあるチェックボックスをオンにするとそのページにあるもの全てに承認がおりる。

この部分は基本的に承認用ファイルから元データを変更する処理が必要となりますので、基本的にマクロを使う必要があります。
関数だけで処理する場合は、元データには何も記入せず、承認用ファイルで「済」を入力してこれを元データに反映する数式を入力しておきます。

＞●(3)承認がおりたものは「承認用ファイル」では「未」が「済」になって、次からは「上司承認ファイル」には表示されなくなる。

上記のような設定にすれば自動的に「承認用ファイル」には表示されなくなります。

シートのレイアウトによって使用する数式が違いますので、元データや承認シートの項目名やレイアウトを具体的に提示していただければ、数式が提示できると思います。

補足

こんな複雑で面倒な質問に回答していただき本当にありがとうござぃます！！

補足をさせていただきます。
承認用ファイルには番号、名前、受付日、有効期限等が書かれた表がありまして、すべてデータは「=＋」の数式でA、B、Cという別ファイルからもってきています。

そしてその「承認用ファイル」から「上司承認用ファイル」にまた「=＋」でデータをひっぱってきている状態です。。。

おっしゃる通り元データがリスト形式になっているため、関数(IF文と＋)だけで表示することができたのですが、

今の状態ですと、
上司承認用ファイルにどんどん情報が増えていき、毎回そのページ全員の承認を一気にとる形になってしまうのです…

しかもチェックボックスが毎回オンになった状態でファイルが開く為、開くと承認がすでにおりた状態になっているというおかしな状態に…

「=＋」でやるからダメなのでしょうか？？
この関数だけで(1)～(3)までは一通りできたのですが、
《1》《2》が解決できず(;_;)

分かりにくいかとは思いますが、ご回答宜しくお願い致しますm(__)m