検索値から、その行の抽出。II

質問

検索値から、その行の抽出。II

シート１にある“あか”の行をシート２に抽出。
任意の番号をふった後、番号の若いもの順に並び替えをしたい。
また、“あか”のみの縦の合計を求めたい（緑色部分）

明日仕事の資料を作りたいので、ご回答いただければ幸いです。
よろしくおねがいします。

回答No.1

tom04 · ベストアンサー

こんばんは！
一例です。
今回はSheet2のA列の番号に重複があるということですね？

↓の画像で説明します。

Sheet1に作業用の列を設けています。
作業列F2セルに
=IF(COUNTIF(A2:E2,Sheet2!$A$1),ROW(),"")
という数式をいれ、オートフィルで下へずぃ～～～！っとコピー

Sheet2のB3セルに
=IF(COUNT(Sheet1!$F:$F)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$F:$F,ROW(A1))))
という数式をいれ、列方向と行方向にオートフィルでコピーします。

そして、Sheet2のA列に好みの番号を入力。
H3セルに
=IF(COUNT($A$3:$A$1000)<ROW(A1),"",SMALL($A$3:$A$1000,ROW(A1)))

I3セルに
=IF($H3="","",INDEX(B$3:B$1000,SMALL(IF($A$3:$A$1000=$H3,ROW($A$1:$A$998)),COUNTIF($H$3:$H3,$H3))))
これは配列数式になりますので、この画面からコピー＆ペーストしただけではエラーになると思います。I3セルに貼り付け後、F2キーを押す、またはI3セルをダブルクリック、または数式バー内で一度クリック。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定します。
このI3セルをM3セルまでオートフィルでコピー。

最後にH3～M3セルを範囲指定し、M3セルのフィルハンドルで下へオートフィルでコピーすると
画像のような感じになります。
ここまでくれば後は簡単です。

J1セルは
=SUMIF(I3:I1000,A1,J3:J1000)

L1セルは
=SUMIF(K3:K1000,A1,L3:L1000)

という数式を入れています。
尚、この数式はSheet2の1000行目まで対応できるようにしてみました。
実際のデータがこれだけないのであれば範囲指定の領域はアレンジしてみてください。
この場合配列数式の中に「1000」と「998」がありますので、これを間違えないようにしてください。
仮に100行目までの数式だと
1000　→　100　　998　→　98　とします。

以上、長々と失礼しました。
参考になればよいのですが・・・m(__)m

お礼

お返事が大変遅くなりました。
この数式を使い、毎日の仕事が大変楽になりました。
迅速にお答えいただいたのに、お礼が遅くなり失礼しました。

ありがとうございました。m(..)m

回答No.4

mu2011

一例です。（ＶＢＡを使用していますので参考程度にして下さい）
（１）シート２の表見出しを設定
（２）シート２のC1に＝SUMIF(B3:B100,A1,C3:C100)、E1に＝SUMIF(D3:D100,A1,E3:E100)を設定
　　　この設定は手順最後でも問題ありません。
（３）シート１のシートタブ上で右クリック→コードの表示→以下のコードを貼り付け→Ｆ５キー押下→抽出キーを入力→ＯＫ→alt＋F4キー押下
　　　因みにマクロコードはシート１に貼り付いたままですので不要でしたらalt＋F4キー押下前にそのコードを削除して下さい。
（４）任意番号を入力→シート２の並び替え範囲を選択→並び換えで先頭行は見出し行にチェック→昇順で並び替え

上記はご例示の表を参考にしていますので相違している場合、調整して下さい。

◎抽出コード
Sub シート1を検索して結果をシート2()
key = InputBox("抽出コードを入力して下さい。", "抽出コードの入力")
Sheet2.Cells(1, 1) = key
b = 3
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For k = 1 To Cells(i, Columns.Count).End(xlToLeft).Column
If Sheet1.Cells(i, k) = key Then
Sheet2.Cells(1, 2).Offset(b - 1).Resize(1, 5).Value = _
Sheet1.Cells(1, 1).Offset(i - 1).Resize(1, 5).Value
b = b + 1
Exit For
End If
Next
Next
End Sub

お礼

マクロを使ったことも、作成したこともなく
まだまだ勉強不足ですが、今後の参考にさせていただきます。

ありがとうございます。

回答No.3

MackyNo1

補足です。

お分かりと思いますが、Ｓｈｅｅｔ２のＡ１セルに抽出したい項目が入力されている場合は、セル参照の数式にしてください。
また、Sheet1のデータ数が20行を超えている場合は20の数値を1000などに適宜変更してください。

Sheet1!$A$2:$A$20="あか"　→　Sheet1!$A$2:$A$1000=$A$1

お礼

親切にありがとうございます。
参考にさせていただきます

回答No.2

MackyNo1

Ｎｏ１の回答と同じレイアウトで表示するなら、Ｓｈｅｅｔ２のＢ２セルに以下の式を入力し、Ｃｔｒｌ＋Ｓｈｉｆｔ＋Ｅｎｔｅｒで確定し、右方向および下方向にオートフィルします。

=IF(SUMPRODUCT(((Sheet1!$A$2:$A$20="あか")+(Sheet1!$C$2:$C$20="あか")>0)*1)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(IF((Sheet1!$A$2:$A$20="あか")+(Sheet1!$C$2:$C$20="あか"),ROW($A$2:$A$20),""),ROW(A1))))

同様に右の表には以下の式を入力します。

H2セル（下方向にオートフィル）
=ＩＦ（B2="","",SMALL($A$2:$A$20,ROW(A1)))

Ｉ２セル(Ｃｔｒｌ＋Ｓｈｉｆｔ＋Ｅｎｔｅｒで確定し、右方向および下方向にオートフィル）
=ＩＦ（B2="","",INDEX(B:B,SMALL(IF($A$2:$A$20=$H2,ROW($A$2:$A$20),""),COUNTIF($H$2:$H2,$H2))))