Excel 該当しない行を削りたい （関数）

質問

以下票の場合、Fを除いて一覧にしたいんですが、
どの様な関数にしたらいいでしょうか？

国語 A+
数学 A
理科 F
社会 B

↓
国語 A+
数学 A
社会 B

以下関数までは探し当てたんですが、これだとA1に指定した科目のみになってしまいます。

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(B1),INDEX(Sheet1!B:B,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(B1)+1)),"")

回答No.1

aokii · ベストアンサー

連番を付けた後、並べ替えて理科Fを削除してから、連番で元の並びにしては？

お礼

回答ありがとうございます。
関数で行いたいんです。

回答No.6

KURUMITO

配列数式などを使いますと計算に負担がかかります。作業列を作って対応することです。
例えばシート１のＡ２セルから下方に科目が、Ｂ２セルから下方にＡやＦなどが入力されているとしたらＣ２セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(B2="F","",MAX(C$1:C1)+1))

シート２にお望みの表を表示させるとしてＡ２セルには次の式を入力してＢ２セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$C:$C),"",INDEX(Sheet1!$A:$B,MATCH(ROW(A1),Sheet1!$C:$C,0),COLUMN(A1)))

これでシート１に新たにデータが入力されれば自動的にシート２の表が変わります。

回答No.5

tom04

No.4です！
A・B列と別れているというコトですので・・・

↓の画像でD1セルに
=IF(COUNTA($B:$B)-COUNTIF($B:$B,"F")<ROW(A1),"",INDEX(A$1:A$100,SMALL(IF($B$1:$B$100<>"F",ROW($A$1:$A$100)),ROW(A1))))
これは配列数式になりますので、Shift+Ctrl+Enterで確定！

この画面からコピー＆ペーストする場合は
D1セルを選択　→　数式バーで一度クリック　→　貼り付け　→　そのまま（編集可能のまま）
Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
このD1セルを列・行方向にオートフィルでコピーすると
画像のよな感じになります。m(_ _)m

回答No.4

tom04

こんにちは！
どうしても関数で！というコトですので・・・
一例です。

↓の画像のようにB列を作業用の列として使用します。
B1セルに
=IF(OR(A1="",ISNUMBER(FIND("F",A1))),"",ROW())
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてD1セルに
=IF(COUNT(B:B)<ROW(A1),"",INDEX(A:A,SMALL(B:B,ROW(A1))))
という数式を入れオートフィルで下へコピー！
これで画像のような感じになります。m（_ _）m

補足

回答ありがとうございます。

すいません補足です、、、、

科目名とアルファベットは別セルです。

A　　　B
国語　A+
数学　A
理科　F
社会　B

回答No.3

mt2008

作業列と関数の組み合わせで……

B1以下にデータが入っているとして、A1に↓を入れて下にコピー
=ROW()+999*OR(B1="",ISNUMBER(FIND("F",B1)))

D1に↓を入れて下にコピー
=IF(SMALL(A:A,ROW())>999,"",VLOOKUP(SMALL(A:A,ROW()),A:B,2,FALSE))

補足

A　　　B
国語　A+
数学　A
理科　F
社会　B

回答No.2

moon00

「F」が付かないものを残す、ということでいいでしょうか？

オートフィルタを掛けて、テキストフィルタで「指定の値を含まない」にして、
「F」を含まないとし、フィルタリング。
フィルタリングした結果をコピーして、別のところに貼り付け。
（2007以上ですと、自動的に可視セルのみコピーされるはず）

お礼

回答ありがとうございます。
関数で行いたいんです。