[[20190702174737]]　『特定のワードが入ってる行を全て転記したい』（ちゃんこ）　ページの最後に飛ぶ

『特定のワードが入ってる行を全て転記したい』（ちゃんこ）

Excelについてお伺いしたいです。

今、画像のような状態のものの「送信可能」という行だけを別シートに転記したいと思っております。

(画像リンク)
https://gyazo.com/68b5c0eb3a665f4d12dc3f44a862ef2d

【やりたいこと】
送信リストシートと不通リストシートと配信停止希望リストを比較して、
最新版の送信リストを作成したい。

【説明】
送信リスト＝＞送信したリスト(画像が送信リスト)
不通リスト＝＞送信リストの中から送れなかったアドレスのリスト
配信停止希望リスト＝＞配信停止を希望されたリスト

【セル説明】

Gのセルには
=COUNTIF(不通リスト!B:B,送信リスト!B3) + COUNTIF(配信停止希望リスト!B:B,送信リスト!B3) + COUNTIF(不通リスト!D:D,送信リスト!E3) +COUNTIF(配信停止希望リスト!D:D,送信リスト!E3)

という関数が入っており、

不通リストと送信リスト
配信停止希望リストと送信リスト
をそれぞれ比較して、0のときならば不通リストにも配信停止希望リストにも該当しないというようにしています。

Hのセルには

=IF(G1 = 0,"送信可能","送信不可能")

という送信可能か不可能かを判定する関数が入っています。

【関数でやりたいこと】

最新版の送信リストのシートに、この値が0 もしくは送信可能のものだけを取り出していきたい。

VLOOKUPなど(例メールアドレスの転記をしたい場合の関数 =VLOOKUP("送信可能",A2:S2,2,0))をなどを使ってもうまくいきません。

どうすれば、関数を使って最新版の送信リストを作れるでしょうか？

(フィルターを使ったコピペではなく、あくまでも関数を使用して自動的にデータを持ってこれるような形にしたいです、)

ご教授のほど、よろしくお願いいたします。

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

 ＞Hのセルには =IF(G1 = 0,"送信可能","送信不可能")

 　　　↓に変更する　　　
 　H2セル =IF(G2 = 0,"送信可能-" & COUNTIF(H$1:H1,"送信可能*")+1,"送信不可能")

 ＞メールアドレスの転記をしたい場合の関数 =VLOOKUP("送信可能",A2:S2,2,0))

 　　　　　　　　　　　　　　　　　　　　　　↓に変更する
 　=IFERROR(INDEX(B:B,MATCH(TEXT(ROW(A1),"送信可能-0000"),H:H,0)),"")

 　下にコピーする

（半平太） 2019/07/02(火) 19:36

1. H2のセルに入るとのことですが、H1 のセルにはどのような関数をいれるのでしょうか？

2.COUNTIF(H$1:H1,"送信可能*")+1　この"送信可能*"+1　の+1はなぜいれているのでしょうか？

3.画像のように関数を入れたのですが、

　https://gyazo.com/867d148db34be064ffd47da0db9f7bc2

　メールアドレスは抽出されず空白のままになっております。

　https://gyazo.com/072bc3e11b9fcbd43146748fcbea86d4

　対応方法をご教授いただければ幸いです
（ちゃんこ） 2019/07/03(水) 10:23

 旨く行かなかったですか・・済みません。　m(__)m

 申し訳ないですが、画像は拝見していないです（怖いので）。

 他の回答者のレスをお待ちください。

（半平太） 2019/07/03(水) 10:51

 H1 =IF(G1="","",IF(G1=0,"送信可能-"&COUNTIF($G$1:G1,0),"送信不可能"))
 下コピー

 同じシートに抽出でいいのなら

 J1 =IFERROR(INDEX(B:B,MATCH("送信可能-"&ROW(A1),H:H,0)),"")
 下コピー

 以上、参考まで
（笑） 2019/07/03(水) 11:13

 G列に COUNTIF の式が入っているのなら
 G列が空白かどうかの判定は不要なので

 H1 =IF(G1=0,"送信可能-"&COUNTIF($G$1:G1,0),"送信不可能")

 参考まで
（笑） 2019/07/03(水) 11:36

ありがとうございます！
参考にして作ると出来ました！！

ちなみになんですが、

 J1 =IFERROR(INDEX(B:B,MATCH("送信可能-"&ROW(A1),H:H,0)),"")

は、参照する値が空白だった場合は「0」が入っているのですが、これを空白にすることは可能でしょうか？
(たとえば、メモ欄を記入する列があった場合　現状だと　空白の部分にすべて0が入っている状態です)
（ちゃんこ） 2019/07/03(水) 13:00

 ＞メモ欄を記入する列があった場合

 抽出するのはメールアドレスだけじゃないってこと？
 メモがどの列か分からないので、例えばですが、B列だとすれば

 =IFERROR(INDEX(B:B,MATCH("送信可能-"&ROW(A1),$H:$H,0))&"","")
                                                       ~~~
 波線部に &""　を付けると「0」にはなりません。
 ただし、セルに数値だけが入力されている場合、数式の結果は「文字列の数字」になってしまいます。

 ついでに書いておくと、数値しか入力しない列で、空白なら「0」ではなく空白を返すには
 =IFERROR((INDEX(B:B,MATCH("送信可能-"&ROW(A1),$H:$H,0))&"")*1,"")

 その件とは関係ありませんが、抽出するのが複数列なら
 $H:$H　のようにH列を絶対参照にしておいた方がいいです。

 また、メモの列に「0」だけを入力することはないのなら
 数式は =IFERROR(INDEX(B:B,MATCH("送信可能-"&ROW(A1),H:H,0)),"")　のままで
 表示形式～ユーザー定義を　#　にしておけば「0」は表示されません。
 この場合は「0」を非表示にしているだけで、値は空白ではなく「0」です。

 というか、質問はこういうこと？
（笑） 2019/07/03(水) 15:00

コメント返信：

[ 一覧(最新更新順) ]

[[20190702174737]] 『特定のワードが入ってる行を全て転記したい』（ちゃんこ） ページの最後に飛ぶ

[[20190702174737]]　『特定のワードが入ってる行を全て転記したい』（ちゃんこ）　ページの最後に飛ぶ