[[20190109153952]]　『条件付きでのSheet振り分け＆欠損データの取り扱ａx（シン）　ページの最後に飛ぶ

『条件付きでのSheet振り分け＆欠損データの取り扱い』（シン）

下記のデータを入力するにあたりいくつか質問があり、ご教授いただけますと幸いです。
なお、PCはほぼ初心者のため至らぬ点が多々ありますが、何卒ご容赦ください。
　　　A　　　　 B　　　　C　　　　D　　　E 　　 F・・・・
１　検査結果　身長　　　体重　　筋力　　下肢長部活の有無
２　陽性　　　　100　　　45　　　20　　　45　　　　あり
３　陰性　　　　120　　　40　　　30　　　50　　　　あり
４　陽性　　　　130　　　50　　　10　　　50　　　　なし
５　陽性　　　　140　　　60　　　20　　　35　　　　なし
６　陰性　　　　125　　　30　　　30　　　40　　　　あり
・
・
・
【質問１】
元データ（Sheet1）に入力していった際に、
同時に、検査結果毎に別シート（Sheet2陽性群、Sheet3陰性群）に振り分けを行うことは可能でしょうか。
行は随時追加されます。
列は必要に応じて削除・追加・修正（切り取り、貼り付け）が考えられます。
＜Sheet2＞
　　　A　　　　 B　　　　C　　　　D　　　E 　　 F
１　検査結果　　身長　　体重　　筋力　　下肢長　　部活の有無
２　陽性　　　　100　　　45　　　20　　　45　　　　あり
３　陽性　　　　130　　　50　　　10　　　50　　　　なし
４　陽性　　　　145　　　60　　　20　　　35　　　　なし
＜Sheet3＞
　　　A　　　　 B　　　　C　　　　D　　　E 　　 F
１　検査結果　　身長　　体重　　筋力　　下肢長　　部活の有無
２　陰性　　　　120　　　40　　　30　　　　50　　　　あり
３　陰性　　　　125　　　30　　　30　　　　40　　　　あり
【質問2】
また、Sheet4に身長・体重・下肢長、Sheet5に筋力と部活の有無の項目に絞って、
同一シート内に検査結果の陽性群、陰性群に分けて記載することは可能でしょうか。
こちらも、生データ（Sheet1）を入力した際に同時に振り分けられるようにできたらと思っております。
また、行・列ともに項目が追加・修正が考えられます。
＜Sheet4＞
　　　A　　　　　B　　　　C　　　　D　　 F　　　　G　　　　H　　I　　　J
１　検査結果　　身長　　体重　　下肢長　　　　検査結果　　身長　　体重　下肢長
２　陽性　　　　100　　　45　　　45　　　　　　　陰性　　120　　　40　　50
３　陽性　　　　130　　　50　　　50　　　　　　　陰性　　125　　　30　　40
４　陽性　　　　145　　　60　　　35　　　
＜Sheet5＞
　　　A　　　　　B　　　　C　　　　D　　 F　　　　G　　　　H　　
I
１　検査結果　　筋力　部活の有無　　　　　　　検査結果　　筋力　部活の有無
２　陽性　　　　20　　　あり　　　　　　　　　　陰性　　　30　　　あり
３　陽性　　　　10　　　なし　　　　　　　　　　陰性　　　30　　　あり
４　陽性　　　　20　　　なし

前回こちらのサイトで質問したところ

＜質問1に対して＞
Sheet2のA2セルに =IFERROR(INDEX(Sheet1!A:A,SMALL(IF(INDIRECT("Sheet1!$A$2:$A$1000")="陽性",ROW(A$2:A$1000),""),ROW(A1))),"")

＜質問2に対して＞
条件付けした下記の式の『Sheet!A:A』を抜き出したい列に変更=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(INDIRECT("Sheet1!$A$2:$A$1000")="陽性",ROW(A$2:A$1000),""),ROW(A1))),"")

して無事に入力を終えました。有り難う御座います。

ところが、実際に入力を始めたところ
欠損データがいくつかあり、その際にSheet2以降が『0』と表示され、
合計や平均、標準偏差の計算ができなくなっております。
（実際の数値が『0』の項目もあり）

どなたか欠損データがある条件でその後の統計をかけることを考え、
質問1、質問2に対して問題解決できる手段をご教授いただけませんでしょうか。

長文になりましたが、最後まで目を通していただき有り難う御座います。

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

 ピボットテーブルでよさげだけど。

 Sheet1のG,H列 作業列

 G2 =IF(A2="陽性",ROW(),"")
 H2 =IF(G2="",ROW(),"")

 G2:H2 下へコピー。

 Sheet2 A2 =IF(COUNT(Sheet1!$G:$G)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$G:$G,ROW(A1))))
 Sheet3 A2 =IF(COUNT(Sheet1!$H:$H)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$H:$H,ROW(A1))))

 それぞれ右、下へフィルコピー。

 Sheet4、Sheet5はSheet2～3をセル参照式 (=Sheet2!A1 みたいに）すればいいと思う。

（GobGob） 2019/01/09(水) 15:59

 あー。元数式があったんですなｗ。

 一応、参考としてくださいｗ。
（GobGob） 2019/01/09(水) 16:00

 欠損データってか、どんな「欠損の仕方」で0となってるのか？がわからないね。
（GobGob） 2019/01/09(水) 16:04

 もとの表で検査結果は入力されているが身長や体重などが未入力のものがあるとして。
 入力されるのが数値の個所を
 =IFERROR((INDEX(Sheet1!A:A,SMALL(IF(INDIRECT("Sheet1!$A$2:$A$1000")="陽性",ROW(A$2:A$1000),""),ROW(A1)))&"")*1,"")
 としてはどうだろうか？

（ねむねむ） 2019/01/09(水) 16:11

 あー。そういうことですね。ねむねむさん回答で納得ｗ。

 一応、参考で

 B2 =IFERROR((INDEX(Sheet1!B:B,AGGREGATE(15,6,ROW(Sheet1!B$2:B$1000)/(Sheet1!$A$2:$A$1000="陽性"),ROW(B1)))&"")*1,"")

 E2までコピー。

 ※普通にEnter
（GobGob） 2019/01/09(水) 16:38

Gob Gobさん

ありがとうございます。
参照させていただきます。
（シン） 2019/01/09(水) 17:32

ねむねむさん

続けてのご連絡ありがとうございます。
欠損データについては、
ご指摘の通り身長や体重など一部が未入力の項目があったことを伝えたかったです。

先ほど上記の式で行ったところ、
欠損データは白紙のセルとなり、合計・平均とも可能となりました。
ありがとうございます。

一方で、
『条件（陽性or陰性）』『部活の有無（ありorなし）』が白紙となったのですが・・・
お手数をおかけし大変恐縮ではありますが、重ねてご教授いただけませんでしょうか
（シン） 2019/01/09(水) 17:58

ねむねむさん

続けてのご質問で申し訳ございません。

前回ご回答いただきました【質問2 Sheet4-5】についてです。
各項目に絞って一覧にしていたのですが、
今回の式を変更したところそちらも空白のセルとなってしまいました。
Sheet4-5も上記式に変更してみたのですが、うまいこといかずに難渋しております。
重ねて解決方法をご教授いただけると幸いです。

（シン） 2019/01/09(水) 18:16

いろいろ考えてるうちに、より良い回答がついてましたけど、書いちゃったので投稿しておきます。

＞前回こちらのサイトで質問したところ
余計な御世話かもしれませんが、話が続いているなら↓のほうに投稿した方がよさげに思いますよ。

[[20190107123629]]　『条件付きでのSheet振り分け』（シン）

そして↑でも書きましたけど、どうしても数式でないとダメですか？
とりあえず、頑張って数式でなんとかならないかな～と私なりに考えてみました。

まず、「欠損データ」とは、おそらく「ブランクセル」のことを言っているのだと思います。
そうであれば、ちょっとした実験として、新規ブックを用意してA1セルに

=B1

と入力してみてください。すぐに解ると思いますが、ブランクセルをそのまま参照すると計算?されて0になります。
今回の質問はこの部分ですよね。

これを回避するには

 (1) =IF(B1="","",B1)

のように、対象セルの値がブランクであるか確認して分岐させるか、

 (2) =B1 & ""

のように、""を結合して文字列に扱いに変えちゃうとかでしょうか。
ただし、(2)のほうは、数値であっても文字列になっちゃって、それはそれで困りそうですから、もう一工夫して、

 (3) =IFERROR(IF(ISNUMBER(B1),B1,B1&""),"")

みたいにしたほうがよいとおもいます。

ただ、実際には「B1」ではなく、元の数式がまるっと入ることになりますから、ぱっと見かなり複雑にみえるものができそうです。なので、もっと良い手があるかもしれませんが私には思い付かないので、やはりオートフィルター案が一番楽ちんじゃ無いのかな～とおもいます。
そちらであれば「数値」、「文字列」、「ブランクセル」いずれであっても、そのまま貼り付ければいいだけですから・・・
（セルの書式はコピーしたくないということであれば、貼付時に「形式を選択して貼り付け→値」　とすればよいです。）

（もこな２） 2019/01/09(水) 19:04

もなこ２さん

前回からの返信が遅くなり申し訳ございません。
掲示板の使い方勉強になりました。
現在も2つの掲示板があるため、今後はこちらで統一いたします。
前回の掲示板は削除できるのでしょうか。

オートフィルターを使った方法ですが、
今後4-5年間かけてデータを随時打ちこんでいく予定です。
おそらく数百～千単位、項目数も40～50個になるかと思っております。
生データに入力さえすれば、途中経過の結果と解析結果をすぐに見れるようにしたいがために、数式を選択してみました。

マクロは難しすぎて選択肢から除外していました・・・

オートフィルターの方法も先に試しましたが、
情報を追加した際にやり直しする必要があったため見送りました。

数式では難渋するようであれば、最終シート（合計、平均、検定など）だけはコピー＆ペーストを考えております。

うまくご質問の答えになっていますでしょうか。
本当にPC使用は初心者のため返答に間違いがございましたらご容赦ください。
（シン） 2019/01/09(水) 20:23

 >『条件（陽性or陰性）』『部活の有無（ありorなし）』が白紙となったのですが・・・ 
 >お手数をおかけし大変恐縮ではありますが、重ねてご教授いただけませんでしょうか

 >入力されるのが数値の個所を
 と書いたように数値個所だけ式を修正してくれ。
（ねむねむ） 2019/01/10(木) 09:27

 もし文字部分でも未入力があり、その場合に0表示させたくない場合は文字入力項目の式を
 =TEXT(IFERROR(INDEX(Sheet1!A:A,SMALL(IF(INDIRECT("Sheet1!$A$2:$A$1000")="陽性",ROW(A$2:A$1000),""),ROW(A1))),""),";;;@")
 としてみてくれ。
 (数値項目と文字項目で式が異なることになる）
（ねむねむ） 2019/01/10(木) 09:33

ねむねむさん

早々の対応ありがとうございます。
文字入力の項目式を上記に変更したところ、
問題解決しました。

誠にありがとうございました。
（シン） 2019/01/10(木) 12:32

コメント返信：

[ 一覧(最新更新順) ]

[[20190109153952]] 『条件付きでのSheet振り分け＆欠損データの取り扱ａx（シン） ページの最後に飛ぶ

[[20190109153952]]　『条件付きでのSheet振り分け＆欠損データの取り扱ａx（シン）　ページの最後に飛ぶ