[[20090226111729]] 『文字検索』(ポトス) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『文字検索』(ポトス)

 下記の表の様に【Sheet1】の一覧表を【Sheet2】にて"色"(A列)の"赤"だけを選んで表示させる方法を教えて下さい。
宜しくお願いします。

 【Sheet1】
    A         B        C
1  (色) (品名) (個数)
2  赤   りんご   9個
3  黄   ばなな    2個
4  赤   りんご   5個
5  赤   りんご   3個
6  黄   ばなな    3個

         ↓

 【Sheet2】

    A       B     C
1 (色)  (品名) (個数)
2  赤   りんご   9個
3  赤   りんご   5個
4  赤   りんご   3個


 フィルタオプションの設定
 (抽出先のシートを選択してからの方が簡単。)

 Sheet2A1:C2をこのようにする。
 A2に抽出してい色を書く。

     A       B       C
 1 (色)	(品名)	(個数)
 2   赤		

 データ→フィルタ→フィルタオプションの設定

 リスト範囲   Sheet1!$A$1:$C$6
 検索条件範囲 Sheet2!$A$1:$A$2

 指定した範囲にチェックして
 Sheet2!$A$1:$C$1

 で、OK。
 BJ

 BJさんありがとうございました。
 試してみたらできました。
 一つだけ問題があったのですが、

 【Sheet1】 は度々更新される事があります。
 その度【Sheet2】 に反映させる方法はありますか?
 試してみたら、もう一度フィルタオプションを設定変更しないと反映してくれませんでした。
 すみません。 お分かりでしたら教えて下さい。

 参考
[[20080704141202]]『ある条件を含む行を別シートにまとめたい』(あい)

 ありがとうございました。
 参考にさせていただき、練習用のファイルではできたのですが、
 本番のファイルはリストが"A1"ではなく"A9"から始まっています。
 "A1"から始めた時はうまくいったのですが、
 "A9"から始めると下記の様に全てがずれてしまうようです。

    A    B    C
 1
 2
 3
 4
 5
 6
 7
 8
 9    赤   りんご     9個
 10   黄   ばなな    2個
 11   赤   りんご    5個
 12   赤   りんご    3個
 13   黄   ばなな    3個
 14   赤   りんご    1個
 15   赤   りんご   10個
 16   黄   ばなな    1個
 17   赤   りんご    3個
 18   赤   りんご    3個
 19   黄   ばなな    6個
 20   黄   ばなな    1個
 21   赤   りんご    5個

 ※↓下記の様にずれてしまいます。

    A     B     C
 1
 2
 3
 4
 5
 6
 7
 8
 9    赤   りんご     3個
 10       ばなな      6個
 11        ばなな   	    1個
 12         0  	      0
 13         0        0
 14         0        0
 15         0        0
 16         0        0
 17        空白    空白
 18        空白    空白
 19        空白    空白
 20        空白    空白
 21        空白    空白

 関数の指定先を変えてみたりして試みたのですが、
 関数がたくさん合わさっていて何度試みてもうまくいきませんでした。
 エクセルのヘルプ等を見たのですが、やっぱりわかりませんでした。
 お手数をかけてすみません。
 もし"A9から始めたい場合は関数の指定先をどう変えたらいいのか分かる方がいたら教えて下さい。


 頑張ってもす少し考えたらその他にも役立ちますよ!
 
 【ヒント】    シート↓ セル範囲↓
 リスト範囲  Sheet1!   $A$1:$C$6
 検索条件範囲 Sheet2!   $A$1:$A$2 

 セル範囲を変更
 (おいらも勉強中)

 ・(ポトス)さんが試した数式を提示されると、具体的なアドバイスがつくと思います。
 ・投稿の都度、署名をしましょう。
 ・行頭を半角スペースで始めると書き込みの改行位置が反映されます。 
 ・スクロールしないと読み憎いので、むやみに行間へスペースを入れない。

 (ポトス)さんの質問文を編集しておきました。(gon-2)


 (gon-2)さんありがとうございます。
 数式は、

 【Sheet2】
 "B9"に↓
 =IF(ROW(1:1)>COUNTIF(Sheet1!$A$9:$L$50,$A$9),"",INDEX(Sheet1!$B$9:$B$50,SMALL(IF(Sheet1!$A$9:$A$50=$A$9,ROW(Sheet1!$A$9:$A$50),""),ROW(1:1))))
(Ctrl+Shift+Enterで確定)

 "C9"に↓
 =IF(ROW(1:1)>COUNTIF(Sheet1!$A$9:$L$50,$A$9),"",INDEX(Sheet1!$C$9:$C$50,SMALL(IF(Sheet1!$A$9:$A$50=$A$9,ROW(Sheet1!$A$9:$A$50),""),ROW(1:1))))
(Ctrl+Shift+Enterで確定)

 と入力しています。
 もし、お分かりでしたら宜しくお願い致します。
 (ポトス)


 B9=IF(ROW(A1)>COUNTIF(Sheet1!$A$9:$A$50,$A$9),"",
      INDEX(Sheet1!B$9:B$50,SMALL(IF(Sheet1!$A$9:$A$50=$A$9,ROW($A$9:$A$50)-8),ROW(A1))))

      INDEX(Sheet1!B$1:B$50,SMALL(IF(Sheet1!$A$9:$A$50=$A$9,ROW($A$9:$A$50)),ROW(A1))))
            ^^^^^^^^^^^^^^^                                 ^^^^^^^^^^^^^^
 としてもOKです。   (Ctrl+Shift+Enterで確定)
 B9をC9にコピーして、B9:C9をフィルダウン

 -----
 B9=IF(ROW(A1)>COUNTIF(Sheet1!$A$9:$A$50,$A$9),"",
      INDEX(Sheet1!B$1:B$50,INDEX(SMALL((Sheet1!$A$9:$A$50<>$A$9)*10^5+ROW($A$9:$A$50),ROW(A1)),)))
 こちらの式はEnterのみでOKです。(gon-2)


 【説明】
 SMALL(IF(Sheet1!$A$9:$A$50=$A$9,ROW($A$9:$A$50)),ROW(A1))
 Shee1の9行目から50行目の間で、「赤」の入力されている行番号を小さい順に抽出している。
 ROW($A$9:$A$50)は、単に9:50と行番号を指定しているだけなのでSheet1!は不要です。
 最後のROW(A1)はSMALL関数の順序を指定しているので、ROW(1:1)とする必要はありません。

 9番目以降の行を抽出しているので、B列の1行目から何行目にあるかということになります。INDEX(Sheet1!B$1:B$50,

 2番目の式の10^5(10万)は、「赤」でなければ 0*10^5+行番号、「赤」であれば 1*10^5+行番号。
 仮に10行目が「赤」であれば、0+10となり、「赤」でなければ 100000+10 となります。
 10^5はエクセル2003以前であれば65536行がMAXですので 100000としただけですから、使用する表のMAXが50行程度なら100でもOKと
 いうことになります。
 ------------------------------
 A1:A5にランダムな数値を入れて
 =MAX(INDEX(A1:A5,0)) → 0 は省略して=MAX(INDEX(A1:A5,))でも0指定とみなされる。
 A1:A5間の最大値が返されます。(説明のための無駄式であることを申し添えます)
 INDEX関数で行番号に 0 を指定するとA1:A5の配列が返されます。列の場合も同様です。 (gon-2)


 (gon-2)さんご丁寧にありがとうございました。
 ずっと出来なかったのですが、やっと完成しました。
 (Ctrl+Shift+Enterで確定)じゃないほうの式を使わせていただきました。
 本当にありがとうございました。
 (ポトス)

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.