【Excel質問】 必要な行だけ別シートに抽出し、更新行も反映する関数式

以下のことを踏まえて、一番最後の質問に対しご教授お願い致します。

【表の作成方法】
①「進捗表」タブの各行に日々の依頼を受け付けた順番に内容を入力する。
　（B列の依頼番号は重複しない数字で、連番で1ずつ数字を増やしていく）
②各案件の進捗がどこまで進んでいるか示すために、F～I列に進捗状況を示す。
　（※このとき、完了したら「●」、途中であれば、「▲」を付記する）

【やりたいこと】
別シートに「確認」、「作成中」、「回覧」、「承認」タブを振り分ける。
このとき、各々のステップまで進捗が進んだものだけを抽出し、
表の一番上の行から順番に並べて空白行を作らず詰めて表示できるようにしたい。
このとき、マクロなどを使わずに、セルに関数だけを入れることで、可能にしたい。

【イメージ】　図2参照
例えば、「回覧」タブの場合、「進捗表」タブのB列「依頼番号」1と11に対し、
H列の「回覧」列が、「●」となっているので、この2行だけを抽出する。
このとき、「回覧」列が、「▲」のものは含まないものとする。
更には「承認」まで進んだものは含まないものとする。

「回覧」、「承認」タブ各々の表のセルに対して、どのような関数式を入れたら良いでしょうか？可能であれば、よりシンプルな関数が好ましいですが、実現不可であれば、複雑でも構いません。ご教授頂けると有難いです。宜しくお願い致します。

No.1 回答者： chonami 回答日時： 2021/12/29 20:10

もし365をお使いならFilter関数が使えるので比較的シンプルに式が作れるのでは？

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu …

この回答へのお礼

chonami様」
Filter関数使えました。
こんな便利な関数があったこと知りませんでした。
確かにシンプルで安心しました。ご連絡ありがとうございます。

お礼日時：2021/12/31 16:09

No.2 回答者： fujillin 回答日時： 2021/12/29 21:53

こんばんは

既に回答が出ていますけれど・・

＞可能であれば、よりシンプルな関数が好ましいです
Filter関数が使える環境を前提として・・
以下は、「回覧」の場合の例です。B4セルに下式を入力。
　=FILTER(進捗表!B4:I20,(Sheet1!H4:H20="●")*(進捗表!I4:I20=""),"")

関数なので、上記のままだと、空白セル部分は「0」表示になります。
それが嫌な場合は、書式で空白表示にするか、&""とするか、IF関数で空白表示にするなどをすれば宜しいでしょう。
（関数で評価するのが堅実ですが、シンプルではなくなりますね・・）

この回答へのお礼

fujillin様」
ご連絡ありがとうございます。
Filter関数について確かに既に回答は頂いていましたが、実際試すと結果が思った通りいきませんでしたが、上記の通り、式の一例を頂いたので、使い方の勉強にもなりました。ホント助かります。
因みに上記式のSheet1!は、進捗表!の間違いですよね？それで思った通り解決しました。また空白セル表示の件も、先読み頂いて有難いです。確かに気になったので、&""を付けて空白にしました。
いえ、このくらいなら理解度が追い付くぐらいの式の長さなので、十分シンプルと思っています。早々と回答ありがとうございます。

お礼日時：2021/12/31 16:14

No.3 回答者： mike_g 回答日時： 2021/12/30 14:01

添付図参照(Excel 2019)

Sheet2 において、
１．次式↓
=SMALL(IF((回覧="●")*(承認=""),番号),ROW(A1))
￣￣をセル K4 に「エイヤッ!」とＳＫＥ(Shift+Cntrl+Enter)入力
２．同セルを選択⇒Ctrl+C⇒セル K5 を選択⇒マウスカーソルを[名前
￣￣ボックス]内に無造作に放り投げ⇒そこに表示されていた「K5」を
￣￣「K5:K1000」に上書き⇒Enterキーをツン⇒Ctrl+Vを憎々し気に
￣￣「エイヤッ!」と叩き付け
３．式 =IFERROR(INDEX(INDIRECT(B$3),$K4),"") を入力したセル
￣￣B4 を右方にズズーッとオートフィル
４．セル E4 を日付書式に設定。セル I4 の表示 0 が目障りなら、当該
￣￣セルを書式を #;-#,, とでも設定すれば好い。
５．範囲 G4:I4 を下方に(全セルが空白を呈するまで)ズズーッとオート
￣￣フィル
オ・シ・マ・イ

No.4 ベストアンサー 回答者： goomania 回答日時： 2022/01/03 15:59

>Filter関数について確かに既に回答は頂いていましたが、実際試すと

>結果が思った通りいきませんでしたが････
とのことなので、FILTER関数を使用しない方法をお示しします。

添付画像①をご覧ください。ご質問者がご掲示になった「進捗表」がこのような状態だったとします。
添付画像➁のように、「回覧」まで完了したものを抽出します。回覧シートのB4セルに、

=IFERROR(AGGREGATE(15,6,進捗表!$B$4:$B$15/(MMULT((進捗表!$F$4:$I$15="●")*COLUMN($A:$D),{1;1;1;1})=6),ROW(A1)),"")･･････(a)

という数式を記述し、下方向へコピーしています。
さらに、C4セルに

=IFERROR(VLOOKUP($B4,進捗表!$B$4:$I$15,COLUMN(B:B),0),"")･･････(b)

という数式を記述し、右方向、下方向へコピーしています。なお、F列～I列は書式設定で表示形式「ユーザー定義」から「;;」を設定しています。

これで、回覧シートに「回覧」まで完了したものが表示されます。

続いて、添付画像③をご覧ください。「承認」まで完了したものを抽出します。承認シートのB4セルに、上記(a)の数式の「=6」の部分を「=10」に修正した数式、

=IFERROR(AGGREGATE(15,6,進捗表!$B$4:$B$15/(MMULT((進捗表!$F$4:$I$15="●")*COLUMN($A:$D),{1;1;1;1})=10),ROW(A1)),"")

という数式を記述し、下方向へコピーしています。
さらに、C4セルに上記(b)と同じ数式、

=IFERROR(VLOOKUP($B4,進捗表!$B$4:$I$15,COLUMN(B:B),0),"")

という数式を記述し、右方向、下方向へコピーしています。なお、ここでも、F列～I列は書式設定で表示形式「ユーザー定義」から「;;」を設定しています。

これで、承認シートに「承認」まで完了したものが表示されます。

※ちなみに、「=6」「=10」の部分はF列～I列を1列～４列とみなして、「●」のある列番号を足したものです。
つまり、1～３列全てに●があれば、1+2+3=6、1～4列全てに●があれば1+2+3+4=10ということになります。
従って仮に「作業中」まで完了したものを抽出するなら、1+2=3ですから、上記数式を「=3」とすればよいことになります。

この回答へのお礼

goomania様」

本質問に対するご回答が遅くなりまして申し訳ございません
Filter関数を使わなくてもできるんですね。MMULT関数は初めて知りました。
ご丁寧に解説頂きましてありがとうございます。
当方、Excelの関数には弱い点ありますが、図を使って説明頂けたので、模倣させて頂き、再現できました！
ありがとうございますm(__)m
参考にさせて頂きます。

お礼日時：2022/02/24 23:33