Excel 検索関数について

質問

初めまして、パソコン初心者で、言葉足らずな所もあるかとは思いますが、宜しくお願い致します。

　　　　　　　A　B　C　D　E　F　G　H　I　J　K　L　M　N　O
１　　２歳以下　３歳　４歳　５歳　６歳　７歳　８歳　９歳　１０歳　１１歳　１２歳　兄弟数　末子　長子　中間子
２　０　０　０　０　０　１　０　０　１　０　０　２　７歳　１０歳　０
３　０　０　１　１　０　０　０　０　０　０　０　２　４歳　５歳　０
４　１　０　０　０　１　０　０　０　０　０　０　２　２歳以下　６歳　０
５　１　０　０　０　１　０　０　１　０　０　０　３　２歳以下　９歳　６歳　
・
・
・
１００

１００件くらいあるリストから兄弟数、末子の年齢、長子の年齢、できれば、中間子の年齢等表示できる方法はあるでしょうか？

兄弟数　　　L2=SUM($A2：$K2)
末子　　　M2=HLOOKUP(1,$B2:$K$54,ROW(B$54)-ROW(B2)+1,FALSE)

兄弟数、末子の年齢などは、表示させられる事ができたのですが、
他にいい方法等はございませんでしょうか？

宜しくお願い致します。

回答No.4

kagakusuki · ベストアンサー

まず、L2セルに次の関数を入力して下さい。

=IF(COUNT($A2:$K2)=0,"",SUM($A2:$K2))

　次に、M2セルに次の関数を入力して下さい。

=IF($L2=0,"",INDEX($1:$1,SUMPRODUCT((COUNTIF(OFFSET($A2,,,1,COLUMN($A2:$K2)-COLUMN($A2)+1),">0")=0)*1)+1))

　次に、N2セルに次の関数を入力して下さい。

=IF($L2=0,"",INDEX($1:$1,SUMPRODUCT((SUMIF(OFFSET($A2,,,1,COLUMN($A2:$K2)-COLUMN($A2)+1),">0")<$L2)*1)+1))

　次に、O2セルに次の関数を入力して下さい。

=IF($L2="","",IF($L2>2,$L2-2,0))

　次に、P2セルに次の関数を入力して下さい。

=IF(OR($L2="",$L2<2,COLUMNS($P:P)>$L2-1),"",IF($K2>COLUMNS($P:P),$K$1,INDEX($1:$1,SUMPRODUCT((SUMIF(OFFSET($A2,,,1,COLUMN($A2:$K2)-COLUMN($A2)+1),">0")<$L2-COLUMNS($P:P))*1)+1))&IF(COLUMNS($P:P)=$L2-1,"(末子)",""))

　次に、P2セルをコピーして、2行目においてP2セルよりも右側にあるセルに、貼り付けて下さい。
　次に、L2～P2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。

お礼

ご回答有難う御座いました。
問題なく、表示する事が出来ました。

回答No.7

imogasi

例データ　元データはＡ－Ｋ列。第１行目は残してある。だから本当のデータは第３行目からの例。
質問の通りの内容。
A B C D E F G H I J K L
２歳以下 ３歳 ４歳 ５歳 ６歳 ７歳 ８歳 ９歳 １０歳 １１歳 １２歳 兄弟数
0 0 0 0 0 1 0 0 1 0 0 2
0 0 1 1 0 0 0 0 0 0 0 2
1 0 0 0 1 0 0 0 0 0 0 2
1 0 0 0 1 0 0 1 0 0 0 3
ーーー
だい３行目の例で説明すると（それより下の行は下方向への式の複写で出来る）
兄弟数　=SUM(A3:K3)
末子　=MIN(IF(A3:K3=1,COLUMN(A3:K3),""))　　と入れて、Ｓｈｉｆｔ＋Ｃｔｒｌ＋ＥＮＴＥＲのキーを同時押し。配列数式。
長子　=MAX(IF(A3:K3=1,COLUMN(A3:K3),0))　　と入れて、Ｓｈｉｆｔ＋Ｃｔｒｌ＋ＥＮＴＥＲのキーを同時押し
中間子　　=IF(SUM(A3:K3)>=2,SUM(A3:K3)-2,0)
上記は
年齢の層の順序数ですが、第２行目の年令見出しを出すなら
末子　=INDEX($A$2:$K$2,1,MIN(IF(A3:K3=1,COLUMN(A3:K3),"")))　と入れて、Ｓｈｉｆｔ＋Ｃｔｒｌ＋ＥＮＴＥＲのキーを同時押し
長子　=INDEX($A$2:$K$2,1,MAX(IF(A3:K3=1,COLUMN(A3:K3),0)))　と入れて、Ｓｈｉｆｔ＋Ｃｔｒｌ＋ＥＮＴＥＲのキーを同時押し
第３行目では
末子　７歳
長子　１１歳
ーーー
配列数式を使うことに、回答者の方からは、賛否あろうかと思うが、１００件ぐらい（２００セル）ならまあ動くと思うが。
質問者は難しさがわからずに質問しているわけで、仕様が無いな。
ＶＢＡやユーザー定義関数でやれば、そんなに難しく無いと思う。
ＳＵＭＰＲＯＤＵＣＴ関数でも、上記配列数式を置き換えて回答が出るかもしれない。
ーー
＞（質問標題）Excel 検索関数について
検索関数は使ってないな。質問者は経験を積まれたし。

お礼

ご回答有難う御座いました。

自分自身は何も知らない初心者なのに、その難しさがわからず、簡単に！
ご質問をしてしまい、大変申し訳ございませんでした。

＞（質問標題）Excel 検索関数について
検索関数は使ってないな。質問者は経験を積まれたし。

imogasiさんのご回答にはご使用されていらっしゃらないので、不適切な標題でした。
まだまだ、説明不足だったり、内容も理解できない初心者が簡単な事を申し上げてすみませんでした。
imogasiさんの仰るとおり経験を積みたいと思います。ありがとうございました。

回答No.6

tom04

こんばんは！
すでに回答は出ていますので、参考程度で・・・

兄弟数は単にSUM関数だけで対応できますので、↓の画像ではM～O列の数式の一例です。
尚、すべて配列数式になってしまいますので、この画面からコピー＆ペーストする場合は
各セルに貼り付け　→　数式バー内で一度クリック　→　編集可能になりますので
Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

M2セルは
=IF(L2>1,INDEX($A$1:$K$1,,MIN(IF(A2:K2=1,COLUMN($A$1:$K$1)))),"")

N2セルは
=IF(L2>1,INDEX($A$1:$K$1,,MAX(IF(A2:K2=1,COLUMN($A$1:$K$1)))),"")

O2セルは
=IF(L2>2,INDEX($A$1:$K$1,,SMALL(IF(A2:K2=1,COLUMN($A$1:$K$1)),2)),"")

という配列数式にし、M2～O2セルを範囲指定　→　O2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

尚、子供さんの数は3人以下としています。

以上、参考になれば良いのですが・・・m(_ _)m

お礼

ご回答有難う御座いました。

私自身まだまだ、勉強不足の為、配列数式等今後のご参考にさせて頂きます。

回答No.5

KURUMITO

式が複雑になり計算速度も遅くなりますので作業列を使って対応するのがよいでしょう。
例えばV列からAF列までを作業列にします。
V2セルには次の式を入力してAF2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(A2="","",MAX($U2:U2)+1)

その後にお求めの表ですがL2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT($A2:$K2)=0,"",SUM($A2:$K2))

M2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($L2="","",IF(INDEX($A2:$K2,MATCH(1,$V2:$AF2,0))=1,INDEX($A$1:$K$1,MATCH(1,$V2:$AF2,0)),INDEX($A$1:$K$1,MATCH(1,$V2:$AF2,0))&" "&INDEX($A2:$K2,MATCH(1,$V2:$AF2,0))&"人"))

末子や長子が複数いる場合には末子2人とかで表示されます。

N2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($L2="","",IF(INDEX($A2:$K2,MATCH(MAX($V2:$AF2),$V2:$AF2,0))=1,INDEX($A$1:$K$1,MATCH(MAX($V2:$AF2),$V2:$AF2,0)),INDEX($A$1:$K$1,MATCH(MAX($V2:$AF2),$V2:$AF2,0))&" "&INDEX($A2:$K2,MATCH(MAX($V2:$AF2),$V2:$AF2,0))&"人"))

P2セルには次の式を入力してT2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($L2="","",IF(COLUMN(B1)>=MAX($V2:$AF2),"",IF(INDEX($A2:$K2,MATCH(COLUMN(B1),$V2:$AF2,0))=1,INDEX($A$1:$K$1,MATCH(COLUMN(B1),$V2:$AF2,0)),INDEX($A$1:$K$1,MATCH(COLUMN(B1),$V2:$AF2,0))&" "&INDEX($A2:$K2,MATCH(COLUMN(B1),$V2:$AF2,0))&"人")))

中間子が多くなっても十分に対応できるでしょう。歳の違う子が8人いる場合までこれで対応出来ますね。

お礼

返信が遅くなってしまい、申し訳御座いません。
ご回答有難う御座いました。

昨日から、業務の合間にご回答して頂いた事を実践致しましたが、
循環参照していますと表示が出てしまい、うまく表示ができませんでした。

今回は別の方の方法を使用させて頂きますが、
今回ご回答頂いた内容を自分で理解できるように参考にさせて頂きます。

回答No.3

mt2008

中間子は関数で出来ない事は有りませんが複雑すぎます、VBAでユーザ関数を作成するのが妥当だと思いますが、初心者との事ですので、すっぱりあきらめて、中間子の「あり」「なし」だけを表示するようにして見ました。

まず、A1：K1には、2～12までの数値を入れ、"歳"は書式で単位として表示してください（セルの書式設定→ユーザー定義で、0"歳"　と設定）。

次に、L2に
=SUM(A2:K2)

M2に、
=SUMPRODUCT(LARGE((A$1:K$1)*(A2:K2>0),11-COUNTIF(A2:K2,0)))

N2に、
=SUMPRODUCT(MAX((A$1:K$1)*(A2:K2>0)))

O2に、
=IF(L2>2,"あり","なし")

と、入れます。M2、N2もA1：K1と同様のセルの書式設定を行います。

最後に、L2:O2を下方向にコピー

コレでいかがでしょう

> そうなってしまいますと○歳×２の表示に出来れば、幸いなのですが、

どこにこの形で表示させたいのでしょう？
末子、長子は例え双子でもどちらか一方になりますよね？
中間子の箇所でしょうか？

補足

ご連絡が遅れましたが、ご回答有難う御座いました。

末子も、長子も問題なく表示する事が出来ました。

＞どこにこの形で表示させたいのでしょう？
双子の場合、たとえば、
　ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯ
１００２００１０００１０４４×２11７
と、表示できたらと、思っております。

宜しくお願い致します。

回答No.2

mshr1962

とりあえずですが
末子　M2=INDEX($A$1:$K$1,MATCH(1,$A2:$K2,0))
長子　N2=INDEX($A$1:$K$1,SUMPRODUCT(MAX(COLUMN($A$1:$K$1)*($A2:$K2=1))))

お礼

mshr1962さん
ご回答有難う御座いました。

補足させて頂きました、○歳×人数っと言う表示にすることは可能でしょうか？

回答No.1

mt2008

補足願います。

・双子、三つ子など、同い年の兄弟が複数居る場合、A～K列には2や3が入ると考えて良いですか。
・兄弟が4人など4以上の偶数名の場合の中間子は？（例：2、3、4、5歳の兄弟）

補足

補足致します。

ハイ！双子、三つ子など、同い年の兄弟が居る場合はその人数がはいります。
そうなってしまいますと○歳×２の表示に出来れば、幸いなのですが、

兄弟が数人いらっしゃる場合の中間子は例であげていただいた場合、
　　Ｍ　　　　　Ｎ　　　　Ｏ　　　　Ｐ
　　末子　　　　長子　　　中間子
　　２歳　　　　５歳　　　３歳　　　４歳
っといった感じに、全てを拾いたいのですが、可能でしょうか？

宜しくお願い致します。