[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『配列関数やSUMPRODUCTあたりで苦戦』（おさげ）
現在非常に複雑な表を作成しており大変苦戦しています 一番苦戦しているとこの要約だけ記します
A B C D... 1 佐藤 a a 2 100 3 100 4 斉藤 a b a 5 100 6 100 7 鈴木 b 8 100 9 100 A列には名前(A1)得点a(A2)得点b(A3)名前(A4)...と規則正しく5000行ほど並んでいます A列の得点a･bというのは固定値でA列には全て同じ数字が入ってます(例の場合は100) B列1行目4行目7行目...とA列の名前が記してある横の行は手入力で『a』『b』『(空白)』のいずれかが入力されています C列以降もB列同様に『a』『b』『(空白)』が入力されています 以下が苦戦している部分です B2セルはB1が『a』の場合、B列で『a』と入力されてる人のA列得点a+『b』と入力されてる人のA列得点b。『a』以外の場合はA2 式にしますとB1="a"の場合 B2=A2+A5+A9→300 それ以外の場合 B2=A2→100 B3セルはB1が『b』の場合上記の計算をしそれ以外の場合 B3=A3となります B5セルはB4が『a』の場合上記の計算をしそれ以外の場合 B5=A5となります C2セルはC1が『a』の場合、上記と同様の計算ですが一列づれて計算式はC2=B2+B6となり『a』以外はC2=B2となります 自分なりに数式をつくってみました B2=(B1<>"a")*B2+(SUMPRODUCT((B$1:B$7="a")*A$2:A$8)+SUMPRODUCT((B$1:B$7="b")*A$3:A$9)) しかしこれでは循環参照に関する警告というものが表示され計算できませんでした 関数の使い方や表の作り方など、色々間違っている所があるかと思います ご指摘いただけると幸いです
現在は関数をほとんど使わず手入力にて計算しているので、入力ミスや計算ミスが多々あり、なんとかミスを減らす事はできないかと試行錯誤している状況です
>しかしこれでは循環参照 B列に計算したいのに、条件判定としてB$1:B$7の範囲を使用しているのが 原因と思われます。 苦労をしているのは「非常に複雑な表」＝計算しにくい表に直接計算を 埋め込もうとしている点にあるのではないかと推測します。 モトとなるデータそのものは別のシートで保持し、 問題のシートは計算のみを行うシートとすると単純化できそうです。 例えば、別のシート(Sheet2)に次のようなリスト形式でデータを用意します。 A B C D [1] 佐藤 a a [2] [3] [4] 斉藤 a b a [5] [6] [7] 鈴木 b : : 該当のシートのA1には、 =IF(MOD(ROW()-1,3),100,TEXT(INDEX(Sheet2!A:A,ROW()),";;;@")) 100が「固定値」です。 この式をA1:A3の範囲、A1:D1の範囲にコピー。 B2の式は =IF(B1="a",SUMIF(Sheet2!B$1:B$7,"a",A$2:A$8)+SUMIF(Sheet2!B$1:B$7,"b",A$2:A$8),A2) B3の式は =IF(B1="b",SUMIF(Sheet2!B$1:B$7,"a",A$3:A$9)+SUMIF(Sheet2!B$1:B$7,"b",A$3:A$9),A3) この二つの式を列方向へコピーしてA1:D3が１ブロックの計算式。 この１ブロックを適当な範囲へコピー貼り付け。 結果は以下のようであっていますか？ A B C D [1] 佐藤 a a [2] 100 300 600 600 [3] 100 100 100 100 [4] 斉藤 a b a [5] 100 300 300 300 [6] 100 100 200 200 [7] 鈴木 b [8] 100 100 100 100 [9] 100 300 300 300 (みやほりん)(-_∂)b 式の修正忘れ2011/10/17 18:50訂正
B2 =SUMPRODUCT(SUMIF(OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0),"a",OFFSET($A$1,(ROW(A$1:A$7)-1)*3+1,0)))+SUMPRODUCT(SUMIF(OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0),"b",OFFSET($A$1,(ROW(A$1:A$7)-1)*3+2,0)))
????
(GobGob)
勘違い
B2 =IF(B1="a",SUMPRODUCT(SUMIF(OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0),"a",OFFSET(A$1,(ROW(A$1:A$7)-1)*3+1,0)))+SUMPRODUCT(SUMIF(OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0),"b",OFFSET(A$1,(ROW(A$1:A$7)-1)*3+2,0))),A2)
B3 =IF(B1="b",SUMPRODUCT(SUMIF(OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0),"a",OFFSET(A$1,(ROW(A$1:A$7)-1)*3+1,0)))+SUMPRODUCT(SUMIF(OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0),"b",OFFSET(A$1,(ROW(A$1:A$7)-1)*3+2,0))),A3)
B2:B3を範囲コピー。
(GobGob)
B2:=IF(B1="a",(COUNTIF(B$1:B$5000,"a")+COUNTIF(B$1:B$5000,"b"))*$A$2,$A$2) B3:=IF(B1="b",(COUNTIF(B$1:B$5000,"a")+COUNTIF(B$1:B$5000,"b"))*$A$2,$A$2) B2:B3をコピー
GobGobさんの解答が求めていたものに一番近くベストアンサーとさせていただきます
解読にとても時間がかかりましたが、SUMPRODUCTを使って擬似的に配列関数を使用しているといったところでしょうか。
私ではこんな関数の使い方の発想は微塵も思いつきませんでした。あっぱれです
(おさげ)
B2 =IF(B1="a",SUMPRODUCT(SUMIF(OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0),{"a","b"},OFFSET(A$1,(ROW(A$1:A$7)-1)*3+{1,2},0))),A2)
B3 =IF(B1="b",SUMPRODUCT(SUMIF(OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0),{"a","b"},OFFSET(A$1,(ROW(A$1:A$7)-1)*3+{1,2},0))),A3)
せっかくの配列数式なんで、もうチョイ短くしてみたよ。
>>SUMPRODUCTを使って擬似的に配列関数を使用しているといったところでしょうか。
そんな難しいことでなくて、SUMIFを実行するだけで良いけど、循環参照となるので OFFSETでセルをとびとび検証させてる。SUMIF関数が配列処理となるんで最後にSUMPRODUCTで 合計させてCTRL+SHIFT+ENTERを回避してる。
この数式をIF分で条件分岐させる。
こんな感じっす。
(GobGob)
数式をもう一度見なおして気づきました
(ROW(A$1:A$7)-1)*3
この数式の意味する所を把握した時、とても衝撃を受けました
とびとびの計算をするために3をかけブランクとなる表外に余ったセルの計算を逃していたんですね
言葉にするとちょっと意味不明ですが、GobGobの機転と発想には本当に感動しました
わざわざ簡略化した数式もご教授していただきありがとうございました
(おさげ)
なんか難しく考えてはるような気がするわ。。。
=IF(B1="a",SUMPRODUCT(SUMIF(OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0),{"a","b"},OFFSET(A$1,(ROW(A$1:A$7)-1)*3+{1,2},0))),A2)
SUMIF
①検索範囲 ･･･ OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0) ⇒ B1セルを基点に0～6シフトしたセルを検証するが、循環参照となるので3セルづつで参照する。 ⇒ 結果 ･･･ OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0) → B1,B4,B7 セル （配列処理）
②計算範囲 ･･･ OFFSET(A$1,(ROW(A$1:A$7)-1)*3+{1,2},0)
⇒ ①と同処理を計算範囲であるA列で行う。但し、計算数値は検索範囲とズレがあるので、
検索"a"の場合は1、"b"の場合は2を加算し、ズレを処理する
⇒ 結果 ･･･ OFFSET(A$1,(ROW(A$1:A$7)-1)*3+{1,2},0) → A2,A5,A8 セル
（"a"の場合。配列処理）
循環参照にしないためセル範囲で処理しているのでなくセル毎に処理する。 セル範囲でなくセル毎の結果で返されるので最後に合計する。
こんなんですわ。
※SUMIFで処理してるけど、セル毎の配列をデータ配列に直せばSUMIFでなくても OKっすよ
例： B2 =IF(B1="a",SUMPRODUCT((T(OFFSET(B$1,(ROW(A$1:A$7)-1)*3,0))={"a","b"})*N(OFFSET(A$1,(ROW(A$1:A$7)-1)*3+{1,2},0))),A2)
※11:02修正
(GobGob)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.