No.4ベストアンサー
- 回答日時:
こんばんは。
>例えばコードが3で上位30位までの金額を集計したい。と。
=SUMPRODUCT(LARGE(($A$1:$A$100=3)*($C$1:$C$100),ROW(A1:A30)))
というような具合になりますね。
>SUMPRODUCTもROWもまだモノにしていませんが、これを期に理解したいと思います。
少し、『配列数式』のことを書いておきます。
SUMPRODUCT は、配列数式のおまじないのようなもです。
例えば、
=A1:A10 としたら、だいたいは #VALUE! というエラーになるはずです。
なぜか考えたことありますか?
空のセルの A1:A10 をマウスで範囲を取って、F9 を押してみると、
={0;0;0;0;0;0;0}
(『{}』この中カッコが、配列の中に収まっているというサインです。)
というように見えます。その「;」 が、邪魔で、#VALUE!のエラーが出ています。
しかし、数値としての値は存在しています。それを SUMPRODUCT()で囲むと、きちんとした数値が出てきます。これは、Excelの計算用キャシュから、SUMPRODUCT()が、実体化させてワークシートに表していると考えたらよいかと思います。これは、セル1つ1つに、そのような入れ物があるようです。
=SUMPRODUCT(LARGE(($A$1:$A$6=2)*($C$1:$C$6),ROW(A1:A2)))
以上の式から、SUMPRODUCTを取ると、
= LARGE(($A$1:$A$6=2)*($C$1:$C$6),ROW(A1:A2))
ですね。
これに、また、マウスで、「LARGE」から、最後尾の 「))」 までの範囲を取って、F9 を押すと、
={1500;1000}
という値が見えるはずです。つまり、値は出ているけれど、実体化していないので、SUMPRODUCTをつけてあげます。
このほかにも、一旦、F2を押して、再度、SHIFT キーとCTRL キーを押しながら、ENTER を押すと、やはり、式が実体化して、同じ値が出てくるはずです。これを、『配列の確定』と呼んでいます。
つまり、
分解すると、
= SUM(LARGE(($A$1:$A$6=2)*($C$1:$C$6),1), LARGE(($A$1:$A$6=2)*($C$1:$C$6),2)) に他なりませんね。
このROW(A1:A2)のことを『インクリメント(Increment)』 と呼んでいます。
それから、($A$1:$A$6=2)*($C$1:$C$6)のように、高さ(A1:A6)のように、両方の中の範囲(高さ・幅)を合わせることを、『配列の共通部分参照』と呼んでいます。
=ROW(A1:A2)
も同じようにして、マウスで、範囲を取って、F9で見てください。
={1;2}
というようになっているはずです。
引数に、1,2,3,.... と数字を与えたいときに、このような方法を使います。
例えば、
=SUMPRODUCT(ROW(A1:A10))
とすれば、55 という値が出てきます。それは、1~10までを足した計算です。
なお、以下は、私が、一番最初に、配列のことを知ったサイトです。(当時は、2000でしたが)
http://support.microsoft.com/default.aspx?scid=k …
[XL2002] n 行おきの合計を算出する方法
ただ、配列数式は、Excel 2000以下ですと、その制限があります。また、関数の中でも、MODEやTRANSPOSE, MINVERSE,MMULT など、配列しか取らないものもあります。
私の記憶の範囲では、SUMPRODUCT()だけが、配列の確定をしなくて済む関数のはずです。また、SUMPRODUCTの中は、条件式などは取れませんから、その場合は、配列の確定をしなくてはなりません。また、配列の確定をした場合は、若干、計算スピードが落ちるようです。
引数にも、LARGE, SMALL などのように、配列を引数に取れる関数もあれば、同じ仲間のRANKは、配列を数式に取れません。私は、その理由を、RANKのほうが出来た年代が古いからだと思っています。RANKやCOUNTIF などの検索範囲に配列が取れるとひじょうに便利になると思っていますが、改変する可能性は薄いようです。また、配列と共に良く使われる関数は、MATCH関数です。
最後に、入力規則や条件付き書式は、特殊な使い方にはなりますが、配列の確定せずに値を受け取ります。
例: 入力規則で、半角文字を制限させる
=AND(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>47,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<123)
長文、お付き合い下されたら、感謝いたします。
この機に、疑問に思うことは、質問されたほうがよいかと思います。
この回答への補足
すみません、勘違いしてました!!
なんとなく理解してきました。
今回の本題はこれで解決していますね。
(すみません・・)
ご丁寧にありがとうございました。
うっわー。。めちゃめちゃありがとうございます。
こんなにご丁寧にご説明されたのははじめてなので感激です。
一通り読まさせていただきました。
少し時間がかかりそうなのでまたじっくり読まさせていただきますね。
ちなみにオフィスの講師の方なのでしょうか?
(すみません、あまりにお詳しいので(笑))
すみません、もう少しお付き合いしていただけるならお願いします。
本題の件なのですが、例えば例にある
「コードが3で上位2位までの金額を集計」だった場合は、ROWの部分がROW(A4:A5)となると思うのですが、
このA4:A5という範囲が不明だった場合どうすれば良いのでしょうか。
データが多くコード2の件数も決まっていないので、次のコード3がどの行からになるのか不明なのです。
説明べたで申し訳ございません。
No.3
- 回答日時:
配列数式で
A列 B列
a12
b34
c5
a6
b3
c4
a23
a12
s11
d1
=LARGE(IF(A1:A10="a",B1:B10),1)+LARGE(IF(A1:A10="a",B2:B11),2)
と入れてSHIFT+CTRL+ENTERの3つのキーを同時に押します。
結果35
レスありがとうございます。
正直、もう理解できません。
例えば金額が上位30位までを集計する。という事も
これで可能なのでしょうか?
No.2
- 回答日時:
こんばんは。
=SUMPRODUCT(LARGE(($A$1:$A$6=2)*($C$1:$C$6),ROW(A1:A2)))
この式を加工する時、ROW(A1:A2)は、常に、A1 ~始ります。
この回答への補足
ごめんなさい補足です。
例に出したデータはあくまで例です。
現実はもっとデータがあります。
なので拡張性がほしいのです。
例えばコードが3で上位30位までの金額を集計したい。と。
何か良い方法があればご教授ください
レスありがとうございます。
SUMPRODUCTもROWもまだモノにしていませんが、これを期に理解したいと思います。
ちなみにここでいうROWはどういう働きをするのでしょうか?
No.1
- 回答日時:
コードが自然数、金額が4桁以下が前提で、
データが、A1:C6の範囲にある場合、
D1に
=IF(A1=2,A1,0)*10000+C1
以下D6までコピー
上位2位までの金額の集計を返したい任意のセルに、
=LARGE(D1:D6,1)-20000+LARGE(D1:D6,2)-20000
で、答えは出ます。
2をセル参照にして、(例えばE1)
20000をE1*10000と置換したら汎用性もでると思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- SQL Server ACCESSで3ファイルを結合して、表を作成するやり方を教えて下さい。 17 2022/08/15 20:34
- Excel(エクセル) ¥マークを含むパスの処理について(マクロ、または関数) 2 2022/12/25 02:11
- SQL Server ACCESSで複数テーブルを結合して、リストを作る方法を教えてください。 2 2022/08/12 19:32
- Access(アクセス) Access クエリ 同一テーブル内 複数フィールドの同時集計のやり方について 1 2022/05/18 19:01
- その他(データベース) カラム上の重複を削除するクエリを教えてください 3 2022/04/12 14:11
- Visual Basic(VBA) コード名シートA列と集計シートA列のコードが一致したら、コード名シートA5からk12の範囲をコピーし 1 2022/08/29 23:46
- 会計ソフト・業務用ソフト Excelで売上げデータの中の任意の商品の合計を出したい 3 2023/01/18 18:19
- その他(Microsoft Office) パワークエリの複数ファイルのデータ統合について 3 2022/07/14 17:06
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- SQL Server ACCESSで表が作りたく、そのためのSQL文や設定方法を教えてください。 1 2022/08/15 12:28
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
配列数式の解除
-
for each の現在の配列ポインタ...
-
2つ以上の変数を比較して最大数...
-
VBA 1次元配列を2次元に追加する
-
VBAで近似曲線の係数取得
-
配列変数の添字が範囲外ですと...
-
fortranでのcsvファイルの読み込み
-
特定のセル範囲で4文字以上入力...
-
subの配列引数をoptionalで使う...
-
[VB.net] StringからByte配列へ...
-
【EXCEL】条件+上位○位までを...
-
Excel-VBAの配列「Public Const...
-
Excel VBA配列をFunctionに渡す
-
配列を任意の数値で埋める方法
-
VBのFunctionで、配列を引数...
-
ListViewで、非表示列って作れ...
-
matlabにおける行列の格納
-
Excel2000 絶対値の集計
-
VB6 配列を初期化したい
-
順列の作成
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
配列数式の解除
-
2つ以上の変数を比較して最大数...
-
for each の現在の配列ポインタ...
-
VBA 1次元配列を2次元に追加する
-
特定のセル範囲で4文字以上入力...
-
配列変数の添字が範囲外ですと...
-
subの配列引数をoptionalで使う...
-
えfor文とか使っちゃう時点で時...
-
ListViewで、非表示列って作れ...
-
Excel-VBAの配列「Public Const...
-
VBAで近似曲線の係数取得
-
VB6 配列を初期化したい
-
VBのFunctionで、配列を引数...
-
エクセルで最小値から0を除く方法
-
《エクセル2000》A列・B列の商...
-
Dim は何の略ですか?
-
配列を任意の数値で埋める方法
-
友愛数を探すプログラム
-
verilogで配列の任意の8bitを取...
-
配列内の内容を全て表示する方法
おすすめ情報