どうしても分析しなければならないデータがあるのですが、どうしたらよいかわかりません。データは 3 つの列に分かれており、最初の列は日付です。日付は (さまざまな理由により) 順序どおりに並んでおらず、新しいデータはリストの一番下に追加されるだけです。また、2 列目には製品の販売価格、3 列目には製品の種類があります。たとえば、次のようになります。
計算は、いずれか 1 つのタイプの最新の 7 つの価格に基づいて行う必要があります。3 番目に低い価格と 2 番目に高い価格は、それぞれセル「A」( F12) と「B」( G12) から表に入力されます。
3 番目の数式では、最新の 7 つの価格のうち最高値と最低値を除外 (無視) し、残りの 5 つの価格の平均を計算します。これは、セル「C」( H12) から始まる表の最後の列に入力します。
次に、横軸に「タイプ」、縦軸に「価格」を持つ複合棒グラフと折れ線グラフが必要です。下限範囲 (セル「A」) と上限範囲 (セル「B」) を示す積み上げ棒グラフと、平均値 (セル「C」) を重ねた折れ線グラフが必要です。
私は Excel の達人ではないので、この問題を解決するためにできる限りのセルフヘルプ チュートリアルを試しました。それが役に立つかもしれないと思うところまで行きましたがVLOOKUP、今は途方に暮れています。
答え1
わかりました。スコットが指摘した、特定の日に同じ種類の販売を複数回行わないという制限により、これが可能になりました。しかし、まだかなり複雑です。VBA を使用すると、はるかに簡単になるかもしれませんし、ここで誰かが手順を投稿してくれるかもしれません。
まず、「ヘルパー」テーブルを使用します。これは、中間データを確認して、計算が正しく行われているかを再確認するのに便利なためですが、ヘルパー テーブルを使用しない数式も示します。ヘルパー テーブルは、結果とともに以下に示されています。
私はランダムな価格と日付を使用しましたが、特定の種類の販売が日付ごとに複数回行われるという上記の制限を尊重しました。次に、F2 に入力する数式を作成しましょう。
まず、各タイプの最新の価格 7 件の配列が必要です。これが入手できれば、その配列から結果テーブル内の 3 つのメトリックを簡単に計算できます。
まず、次の式から始めます。($C$2:$C$55=ROW()-1)これは F2 の数式の一部なので はROW()-11 に等しく、この式は True/False 値の配列を返します。Type が 1 の場合は True、それ以外の場合は False になります。下方向に入力されるにつれて値ROW()が増加し、次の行では Type が 2 の場合は True の配列を返します。
ここで、この配列を日付の列で乗算します($A$2:$A$55)*($C$2:$C$55=ROW()-1)。これにより、Type が 1 に等しい日付と、それ以外は False を含む配列が生成されます。
ここで、これらの日付のうち最新の 7 つを取得する必要があり、関数を使用して取得しますLARGE()。上記の制限 (各日付にはタイプ 1 の販売が 1 つだけある) により、タイプ 1 の最新の 7 つの販売の日付が次のように表示されます。
LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})
中括弧内の一連の数字は、LARGE()1 番目から 7 番目に大きい値を返すことを示します。
ここで、を使用してIF()それらの日付に対応する価格を取得します。
IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55)
の論理テストの最初の部分は、IF()列 A の日付が上記の 7 つの日付のいずれかである配列を返します。この配列は、他のタイプがそれらの 7 つの日付で販売されたため、再びタイプ = 1 である配列と乗算されます。したがって、IF()列 A の日付が最新の 7 つの日付の配列のいずれかに等しいかどうかをテストします。そしてその日付の特定の販売がタイプ = 1 であったかどうか。これらの条件が両方とも true の場合、IF()列 B から販売価格が返され、それ以外の場合は False が返されます。
ヘルパー テーブルの価格を高いものから低いものの順にリストするには、次を使用しますLARGE()。
(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55),COLUMN()-5)
この数式は列 F にあるためCOLUMN()-51 に等しく、数式はその列の配列の 1 番目に大きい値を返します。横方向に入力されるにつれてCOLUMN()増加し、次の列では 2 番目に大きい値を返します。
最後に、数式はラップされIFERROR()、特定の種類の販売が 7 件未満の場合は空白が返されます。最終的な数式は次のようになります。
=IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55),COLUMN()-5),"")
は F2 に入力されます。配列数式なので、CTRLShiftEnterだけではなく ,を使って入力する必要がありますEnter。正しく入力すると、Excel は数式バーで数式を中括弧 {} で囲みます。入力したら、F2 を押して数式を下に、次に横に入力し、上記のヘルパー テーブルを作成します。
これで結果表に入力するのは簡単になりました。この2つの式で
=SMALL(F2:L2,3) and =LARGE(F2:L2,2)
補助テーブルの最初の行の3番目に小さい値と2番目に大きい値を計算します。この式は
=AVERAGE(IFERROR(LARGE(F2:L2,{2,3,4,5,6}),""))
中央の 5 つの値の平均を計算します。これは配列数式でもあるため、上記のように入力する必要があります。これらの数式を入力すると、結果テーブルが表示されます。
ヘルパー テーブルを使用せずに結果テーブルにデータを入力するには、F、G、H12 で次の 3 つの配列数式を使用して下方向に入力します。
=SMALL(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),3)
=LARGE(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),2)
=AVERAGE(IFERROR(LARGE(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),{2,3,4,5,6}),""))
これがお役に立てば幸いです。このデータを入手したら、必要なグラフを作成できるはずです。
ご意見やご提案をお待ちしております。