行抽出・関数・複数条件

質問

以下の質問をみて、一つの条件のものを作ることは出来ました。

しかし、AND()やCOUNTIFS()などを用いながら自分なりに複数条件の行抽出をしてみようとトライしてみたのですが、きちんとした行を返してくれません。

どなたか、以下の質問を少し変えて、

**************************************************************
<A列>　<B列>　<C列>
7/1　りんご　100円
7/2　ぶどう　200円
7/2　すいか　300円
7/3　みかん　100円

このような表があって、100円とりんごを含む行をそのままの形で、
別のセル（同じシート内）に抜き出したいのですが。

7/1　りんご　100円
**************************************************************

といった表示をする関数を書いていただけないでしょうか。

よろしくお願いします。

http://okwave.jp/qa/q3200952.html

回答No.2

keithin · ベストアンサー

ＡＢＣ列の１行目はちゃんとタイトル行にして、２行目からデータを列記します
簡単のためＦ１：Ｈ２に抜き出したい条件を記入しておきます

Ｆ５には
=IF(ROW(F1)>COUNTIFS($B:$B,$G$2,$C:$C,$H$2),"",INDEX($A:$C,SMALL(INDEX(($B$2:$B$99=$G$2)*($C$2:$C$99=H$2)*ROW(A$2:A$99),),ROW(F1)),COLUMN(A5)))
と記入、右に下にコピーしておきます。

お礼

($B$2:$B$99=$G$2)*($C$2:$C$99=H$2)
のところを
AND(($B$2:$B$99=$G$2),($C$2:$C$99=H$2))
としてしまっていたところが間違いだったようです。
また、勉強します。(*'ω'*)

回答No.5

bunjii

>しかし、AND()やCOUNTIFS()などを用いながら自分なりに複数条件の行抽出をしてみようとトライしてみたのですが、きちんとした行を返してくれません。
複数条件に合う行番号を返すには配列演算を行います。
($C$2:$C$7=100)*($B$2:$B$7="りんご")*ROW(E$2:E$7) → {2;0;0;0;6;0}
これを{2;6;0;0;0;0}の順に取り出すには次のようにします。
LARGE(($C$2:$C$7=100)*($B$2:$B$7="りんご")*ROW(E$2:E$7),COUNTIFS($C$1:$C$6,100,$B$1:$B$6,"りんご")-ROWS(E$2:E2)+1) → 2
LARGE(($C$2:$C$7=100)*($B$2:$B$7="りんご")*ROW(E$2:E$7),COUNTIFS($C$1:$C$6,100,$B$1:$B$6,"りんご")-ROWS(E$2:E3)+1) → 6
LARGE(($C$2:$C$7=100)*($B$2:$B$7="りんご")*ROW(E$2:E$7),COUNTIFS($C$1:$C$6,100,$B$1:$B$6,"りんご")-ROWS(E$2:E4)+1) → 0
配列演算の数式は確定するときにCtrl+Shift+Enterの打鍵が必要です。
これをEnterキーのみで確定できるようにするためにINDEX関数を使います。
INDEX(($C$2:$C$7=100)*($B$2:$B$7="りんご")*ROW(E$2:E$7),0) → {2;0;0;0;6;0}
最終的には次の数式を使うと目的の結果を得られるはずです。
=IFERROR(INDEX(A:A,LARGE(INDEX(($C$2:$C$7=100)*($B$2:$B$7="りんご")*ROW(E$2:E$7),0),COUNTIFS($C$1:$C$6,100,$B$1:$B$6,"りんご")-ROWS(E$2:E2)+1)),"")
右と下へ必要数コピーすれば良いでしょう。
尚、日付の列は表示形式を目的に合うものに変更してください。
貼付画像はExcel 2013で検証した結果ですがExcel 2007以降のバージョンで再現できます。

回答No.4

msMike

そ「のような表」内で、B列、C列において同じレコードが複数存在することは「アリエナ～イ」のですか？後になって「アリエ～ル」なんてシャーシャーと言わないでせうね？

また、「そのままの形」と仰ったけど、「100円とりんご」と判っているのなら、A列だけの「そのまま」で御(オン)の字ではないですか？

回答No.3

imogasi

エクセルの関数による「抜出し」問題だ。関数としては、式の組み合わせが、非常にむつかしい部類に入る。
その前にエクセルには「フィルタ」などがあるのに、「なぜ関数を使うの」といいたい。
＃２のような回答が決まって出てくるが、この式の意味を理解できますか。
多分無理でしょう。これが判れば、エクセル関数は卒業かなと（個人的に）思う。
今までも毎月数回この問題が出てくるが、私は作業列を１列使ってやることをお勧めする。
「imogasi方式」でＷＥＢ照会すれば、過去の私の回答が出てくる。
例　http://okwave.jp/qa/q4432288.html
１列余分な列（Ｄ列）を使って、条件該当行に連続番号を作る。
7月1日 りんご 100　 1
7月2日 ぶどう　 20
7月2日 すいか　 300
7月3日 みかん　 100
7月5日 りんご 100　 2
Ｄ列の式は　=IF(AND(B2="りんご",C2=100),MAX($D$1:D1)+1,"")
意味は、該当行に上から連番を振っている。
MAX($D$1:D1)+１はそれより上の行の最大数に＋１して、連続番号にしている。
ＡＮＤとＩＦ関数とＭＡＸ関数なので、ありふれた関数で、そうむつかしくない。
この１，２、・・を、抜き出した部分での行番号として使う。
それにより、該当の行だけを、「上行から詰めて」持ってくることができる。
そもそも「抜出」とは、該当の行だけ（非該当行を捨てて）を、「上行から詰めて！」持ってくることを意味するのだ。
抜き出して入れるセル部分での、上からの、行番号として使う。
Ｉ２に=MATCH(ROW()-1,$D$１:$D6,1)と入れて式を下方向に複写すると
（ＭＡＴＣＨ関数は該当のものを見つけるもの、ＲＯＷ（）は式を入れた行番号を意味する）
（有名なＶＬＯＯＫＵＰ関数を使わないのは、１，２・・の連番がデータ部分の最左列にないからです。無理して最左列に持ってくれば使える）
2
6
となる。
これは２行目に元データの第2行、次に第６行を持ってくることの準備作業。
列として、たとえばＩ２に
=INDEX($A$1:$C$6,MATCH(ROW()-1,$D$1:$D6,1),COLUMN()-8)
と入れて、式を右方向に複写。
COLUMN()-8の部分は、本回答例では、Ｉ列から結果を持ってきているために、－８して１になるように補正しているもの。Ｉ列ーー＞１、Ｊ列ーー＞２、Ｋ列ーー＞３となるように。
Ｉ２：Ｋ２の式を下方向に式を複写。
結果
2016/7/1 りんご 100
2016/7/5 りんご 100
Ｉ列のセルの書式は日付、Ｋ列のセルの書式は数値に設定しなおすこと。
:$C$6,の６はデータの最下行に変えること。
１００の部分が日付書式になってしまうから修正。
上記では該当行数（上記例では２件）を超えた場合に、空白にする式を先頭に被せた方がよいが、式が長くなって、説明の本質部分をわかりにくくするので省略した。

回答No.1

kagakusuki

今仮に、元データがあるシートがSheet1であり、Sheet2のA列を作業列として使用して、Sheet1のE列～G列に抽出結果を表示させるものとします。

　まず、Sheet2のA2セルに次の様な関数を入力して下さい。

=IF(AND(Sheet1!$B2="りんご",Sheet1!$C2="100円"),ROW(),"")

或いは

=IF(AND(INDEX(Sheet1!$B:$B,ROW())="りんご",INDEX(Sheet1!$C:$C,ROW())="100円"),ROW(),"")

　次に、Sheet2のA2セルをコピーして、Sheet2のA3以下に貼り付けて下さい。

　次に、Sheet1のE2セルに次の様な関数を入力して下さい。

=IF(ISERROR(1/(INDEX($A:$C,SMALL(Sheet2!$A:$A,ROWS($2:2)),COLUMNS($E:E))<>"")),"",INDEX($A:$C,SMALL(Sheet2!$A:$A,ROWS($2:2)),COLUMNS($E:E)))

　次に、Sheet1のE2セルをコピーして、Sheet1のF2～G2のセル範囲に貼り付けて下さい。
　次に、Sheet1のE2セルの書式設定の表示形式を[日付]の

3/14

に設定して下さい。
　次に、Sheet1のE2～G2のセル範囲をコピーして、Sheet1のE列～G列の3行目以下に貼り付けて下さい。

　以上です。