エクセルの学校：全文検索：Hyper Estraier： A��...

Results of 1 - 1 of about 48786 for A��... (0.010 sec.)

[[20111017151939]]: #score: 1421; @digest: c30628218e9f78d68e4ccac7af858aec; @id: 56309; @mdate: 2011-10-20T02:03:14Z; @size: 6196; @type: text/plain; #keywords: 藤a (14977), ・of (11136), 列得 (10447), 木b (9472), 点a (8850), 列処 (6255), 得点 (4786), 囲・ (4720), 擬似 (4613), 列関 (4354), 苦戦 (4331), 環参 (4282), 斉藤 (4158), 循環 (4082), ル毎 (3950), 『a (3385), sumproduct (3016), sumif (2862), offset (2739), 前( (2522), 白) (2353), 配列 (1477), ズレ (1445), 計算 (1441), ブロ (1336), 定値 (1325), gobgob (1239), 索範 (1150), 鈴木 (1143), 解答 (1131), 佐藤 (1112), ミス (876); 『配列関数や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が1ブロックの計算式。この1ブロックを適当な範囲へコピー貼り付け。結果は以下のようであっていますか? 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) ---- 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 セル（配列処理） A計算範囲・・・ 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) ...; http://www.excel.studio-kazu.jp/wiki/kazuwiki/201110/20111017151939.txt - [detail] - similar

PREV NEXT

訪問者：カウンタ