エクセル関数について教えてください

質問

sheet１は以下のようなデータが入力さて言います。

　　　担当者名　商品名　　仕入日　　金額　　在庫状況
　１　　山本　　　ペン　　　　３/１５　　１００　　　在庫
　２　　植田　　　ピン　　　　３/１　　　　８０　　　売却済
　３　　中川　　　消しゴム　２/２０　　１２０　　　　在庫
１００　山下　　　クリップ　　３/８　　　１５０　　　売却済

sheet２には仕入日順に在庫だけを表示したいです。

　　　担当者名　商品名　　仕入日　　金額　　在庫状況
　１　　中川　　　消しゴム　２/２０　　１２０　　　　在庫
　２　　山本　　　ペン　　　　３/１５　　１００　　　　在庫　
　３
１００

回答No.6

kagakusuki · ベストアンサー

作業列を使わなくとも済む様に、SUMPRODUCT関数を使って処理を行う際に、仕入日として同じ日付が複数入力されていたり、元のデータが入日順には並んでいなくとも、Sheet2では在庫のみが入日順に表示されている様にするためには、A列からE列まで同じ形式の関数を使ったのでは、非常に長い関数が必要となりますので、多少なりとも短い関数で済ますためには、仕入日を抽出する関数と、仕入日以外のデータを抽出する関数の、2種類の関数に分けて、次の様にする必要があります。(仕入日順に並べずとも良いのでしたら、回答No.5様の方法の様に、もっと短い関数で済ます事も出来るのですが)

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

=IF(ROWS($2:2)>COUNTIFS(Sheet38!$C:$C,">1",Sheet38!$E:$E,"在庫"),"",SMALL(Sheet38!$C:$C,SUMPRODUCT(ISREF(INDIRECT("Z1:Z"&COUNT(Sheet38!$C:$C)))*(COUNTIFS(Sheet38!$E:$E,"在庫",Sheet38!$C:$C,"<="&SMALL(Sheet38!$C:$C,ROW(INDIRECT("Z1:Z"&COUNT(Sheet38!$C:$C)))))<ROWS($2:2)))+1))

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

=IF(ISNUMBER($C2),IF(INDEX(Sheet1!$A:$E,SUMPRODUCT(ISREF(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9E+307,Sheet1!$C:$C)))*(COUNTIFS(OFFSET(Sheet1!$E$1,,,ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9E+307,Sheet1!$C:$C)))-ROW(Sheet1!$C$1)+1),"在庫",OFFSET(Sheet1!$C$1,,,ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9E+307,Sheet1!$C:$C)))-ROW(Sheet1!$C$1)+1),$C2)<COUNTIF($C$1:$C2,$C2)))+ROW(Sheet1!$C$1),COLUMNS($A:A))="","",INDEX(Sheet1!$A:$E,SUMPRODUCT(ISREF(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9E+307,Sheet1!$C:$C)))*(COUNTIFS(OFFSET(Sheet1!$E$1,,,ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9E+307,Sheet1!$C:$C)))-ROW(Sheet1!$C$1)+1),"在庫",OFFSET(Sheet1!$C$1,,,ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9E+307,Sheet1!$C:$C)))-ROW(Sheet1!$C$1)+1),$C2)<COUNTIF($C$1:$C2,$C2)))+ROW(Sheet1!$C$1),COLUMNS($A:A))),"")

　次に、Sheet2のA2セルをコピーして、Sheet2のB2セルとSheet2のD2～E2のセル範囲に貼り付けて下さい。
　次に、Sheet2のC2セルの書式設定の表示形式を[日付]として下さい。
　次に、Sheet2のD2セルの書式設定の表示形式を[通貨]とし、金額の頭に付ける記号を「\」になる様に設定して下さい。
　次に、Sheet2のA2セル～E2の範囲をコピーして、同じ列範囲の3行目以下に貼り付けて下さい。

　これで、Sheet2に在庫だけが、仕入日順に表示されます。

　尚、配列式やSUMPRODUCT関数は、データの組の数だけ同じ様な計算を繰り返して行うもので、この場合のデータの組の数は、計算の対象としている行範囲に含まれている行数になります。
　例えば、配列式やSUMPRODUCT関数の中で、処理の対象としているセル範囲を、C2:C1000等とした場合には、999回(C1セルは指定されているセル範囲外となるため、計算の対象となるのは、2行目～1000行までの999行になります)に亘って同じ形式の計算が繰り返される事になります。
　このため、配列式やSUMPRODUCT関数は、繰り返し計算の対象となる行数が多いと、処理が重くなりやすく、数千行もの行数に亘って入力されているデータを処理する場合には、処理に時間が掛かり過ぎるため実用的ではなくなってしまいます。
　ですから、数百行程度のデータを対象とする場合には、配列式やSUMPRODUCT関数は便利ですが、(パソコンの性能にもよりますが)2000行以上にもなる場合には、配列式やSUMPRODUCT関数を使うのは避けて、作業列を使う方法で処理した方が良いと思います。

お礼

ありがとうございました。
解決いたしました。

回答No.5

bunjii

Excel 2013で検証してみた結果の画像を添付します。

Sheet2!A2セルに次の式を入力し、右へオートフィルでE2セルまでコピーします。
日付の列（C）を選択して数値の表示形式を日付にします。
次にA2～E2セルを選択して、下へオートフィルで必要数こーぴーしてください。

=IF(COUNTIF(Sheet1!$E$2:$E$1000,"在庫")>=ROW(A1),INDEX(Sheet1!A$1:A$1000,SUMPRODUCT(MOD(LARGE((Sheet1!$E$2:$E$1000="在庫")*ROW(A$2:A$1000)+IFERROR(RANK(Sheet1!$C$2:$C$1000,Sheet1!$C$2:$C$1000),0)*10000,ROW(A1)),10000)),0),"")

検証では最大の行番号を1000にしてあります。
Excel 2007/2010でも同じ結果を得られるはずです。
Excel 2003以前のバージョンではIFERROR関数が無いので代替の処理を行うには数式が長大になります。

回答No.4

tom04

No.3です！
たびたびごめんなさい。

前回の方法では同一日がある場合、同じデータが表示されてしまいますので、
以下のように変更してください。

前回同様上側がSheet1、下側がSheet2とします。
Sheet1に作業用の列を設けます。
作業列G2セルに
=IF(E2="在庫",C2*10^4+ROW(),"")
という数式を入れ、オートフィルで下へずぃ～～～！っとコピーしておきます。

そしてSheet2のA2セルに
Excel2007以降の場合
=IFERROR(INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$G:$G,ROW(A1)),Sheet1!$G:$G,0)),"")

Excel2003までの場合
=IF(COUNT(Sheet1!$G:$G)<ROW(A1),"",INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$G:$G,ROW(A1)),Sheet1!$G:$G,0)))

という数式を入れ、列・行方向にフィル＆コピー！
今回は配列数式ではありません。

検証せずに投稿してごめんなさいね。m(_ _)m

回答No.3

tom04

こんばんは！
一例です。

↓の画像で上側がSheet1、下側がSheet2とします。

Sheet2のA2セルに
Excel2007以降のバージョンの場合
=IFERROR(INDEX(Sheet1!A$1:A$1000,MATCH(SMALL(IF(Sheet1!$E$1:$E$1000="在庫",Sheet1!$C$1:$C$1000),ROW(A1)),Sheet1!$C$1:$C$1000,0)),"")

Excel2003までの場合
=IF(COUNTIF(Sheet1!$E:$E,"在庫")<ROW(A1),"",INDEX(Sheet1!A$1:A$1000,MATCH(SMALL(IF(Sheet1!$E$1:$E$1000="在庫",Sheet1!$C$1:$C$1000),ROW(A1)),Sheet1!$C$1:$C$1000,0)))

どちらも配列数式になってしまいますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のA2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
これを列・行方向にオートフィルでコピー！

最後にC列の表示形式を「日付」にして完了です。

※　データ量が極端に多い場合、配列数式はおススメしません。
3000行程度であれば問題ないと思いますが、桁違いにデータ量が多い場合、
作業用の列を設ける方法が良いと思います。m(_ _)m

回答No.2

kagakusuki

今仮に、担当者名が入力されている列がA列であり、商品名が入力されている列がB列、仕入日が入力されている列がC列、金額が入力されている列がD列、在庫状況が入力されている列がE列、であるものとします。
　又、Sheet1において、担当者が「山本」となっている行を、御質問文の例の様に1行目としたのでは、「担当者名」、「商品名」、「仕入日」、「金額」、「在庫状況」等の項目名を入力する行が無くなってしまいますので、これらの項目名は1行目に入力されているものとし、担当者が「山本」となっている行は2行目であるものとします。
　又、Sheet3のA列を作業列として使用するものとします。

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

=IF(AND(ISNUMBER(1/DAY(INDEX(Sheet1!$C:$C,ROW()))),INDEX(Sheet1!$E:$E,ROW())="在庫"),RANK(INDEX(Sheet1!$C:$C,ROW()),Sheet1!$C:$C,1)+COUNTIF(Sheet1!$C$1:INDEX(Sheet1!$C:$C,ROW()-1),INDEX(Sheet1!$C:$C,ROW())),"")

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

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

=IF(ROWS($2:2)>COUNT(Sheet3!$A:$A),"",IF(INDEX(Sheet1!$A:$E,MATCH(SMALL(Sheet3!$A:$A,ROWS($2:2)),Sheet3!$A:$A,0),COLUMNS($A:A))="","",INDEX(Sheet1!$A:$E,MATCH(SMALL(Sheet3!$A:$A,ROWS($2:2)),Sheet3!$A:$A,0),COLUMNS($A:A))))

　次に、Sheet2のA2セルをコピーして、Sheet2のB2～E2の範囲に貼り付けて下さい。
　次に、Sheet2のC2セルの書式設定の表示形式を[日付]として下さい。
　次に、Sheet2のD2セルの書式設定の表示形式を[通貨]とし、金額の頭に付ける記号を「\」になる様に設定して下さい。
　次に、Sheet2のA2セル～E2の範囲をコピーして、同じ列範囲の3行目以下に貼り付けて下さい。

　これで、Sheet2に在庫だけが、仕入日順に表示されます。

回答No.1

hallo-2007

在庫状況が　在庫　のデータを抽出するということですよね。
関数で処理するより、エクセルのフィルターオプションの機能を
使ったほうが簡単で、バリエーションが広がると思います。
エクセルのバージョンが不明なので
以下のサイトを参考にしてみてください。
http://www.eurus.dti.ne.jp/yoneyama/Excel/filter3.htm

下のほうに、マクロの記録で　VBAを使って自動化する方法があります。
このコードを、シートの　ChangeやActiveなどで実行するように組み込めば
まるで、関数が組み込まれているかのように自動で抽出するされます。
仕入れ日での並び替えもマクロの記録で大丈夫です。