配列数式？【Excelでの計算式について】

質問

クリックありがとうございます。

Excelで、セル3つおきの数値の合計を出したいのですが
うまくいかないので、おわかりになればお教えください。
表は、以下のような内容で、3列一組が1日の情報でこれが100日分ほど続くとします。
最後の合計欄に、日々の販売数・金額・率それぞれの合計を出したいのです。

セル　　　A　　　　　B　　　　　C　　　　
　　　　販売数　　販売金額　　購買率(%)　・・・

以前の質問を探してみて、配列数式というのが当てはまるかなと思い
試してみたのですが、計算式を入力したセルには「0」と出てしまいます。

●販売数・販売金額・購買率は、それぞれ計算式を入れて出しています。
●項目によっては、別シートから合計を出しているものもあります。
●使用しているパソコンは、Macです（関係ないかもしれませんが）

以上、説明がわかりにくいかとは思いますが、お教えいただければ幸いです。
宜しくお願い致します。

回答No.6

Wendy02 · ベストアンサー

こんばんは。

割り込み失礼します。

>3日間のうち1日でも人数が入らずにC) のセルの表示が＃DIV/O!　になっていると計算式入力後「＃DIV/O!」と表示されてしまうようなのです。

ところで、人数は、どうやって入れているのか、いったい、どんな数式なのか、はっきり言って分かりません。というのは、3日単位の項目が突然出てきているからです。きちんとした実際の入れている数式をひとつでも出して説明してくれば、すでに解決していただろうと思います。

>C）購買率　　=A/1日の客数

せっかく、詳しい説明を補足でしていただいても、回答者としては、その内訳や論理ではなくて、実際の"セルの位置関係"と"数値"や"数式"の内容が必要なのです。

一般的にこのような質問の場合、スモール・サンプルとして、全体が想像できるような列と行を示すのが普通なのです。

#4 の回答の補足の
＞求めたかったものが計算されるのですが「＃DIV/O!」が残っている状態では計算はできないのでしょうか・・・

まず、基本的に、エラー値を出さないようにして計算することです。「＃DIV/O!」を出しっぱなしではだめです。

また、
＞見栄えが悪いので「IF(ISERROR(・・・」で見た目は空欄になっていますが・・・

のISERROR(式）の真の場合に、「""」を置いていると、これも、配列数式では、#VALUEになります。

例えば、
　　A列　　　B列　　　　　C列　　　　　D列　　　　E列　　　　F列
1　販売数　　販売金額　　購買率(%)　　販売数　　販売金額　　購買率(%)
2　数字　　　数字　　　　 数字　　　　数字　　　 数字　　　　数字

となっている（つまり、繰り返しになっている）なら、

仮に、「"" 」で見えなくなっていても、

=SUMIF($A$1:$Z$1,"販売金額",$A2:$Z2)
=SUMIF($A$1:$Z$1,"販売数",$A2:$Z2)
というようにして計算は可能です。（非配列数式）
これは、1行目の"販売金額"をキーワードにして、該当する同じ列のセルの合計を出しています。これは、「""」（長さ0の文字列）を無視して合計を出すように出来ます。

しかし、皆さんの書いた配列数式を活かすのでしたら、

書式で、「0」が存在しないのでしたら、書式のユーザー定義の中で、
「#,###;;」セミコロン（;)を二つ付け足す。

パーセンテージなら、
「0%;;」
このようにします。

　 =IF(ISERROR(A/1日の客数),"",A/1日の客数)　←前の数式
　　　　　↓
　 =IF(ISERROR(A/1日の客数), 0 ,A/1日の客数)

として、エラーの条件が真になった時に、「0」を出すようにすれば、皆さんの数式が活きます。

お礼

ご回答ありがとうございました。

＃6様
上記でご回答いただいた通り「エラーの条件が真になった時に「0」を出すように・・・
という方法で試してみたところ、無事に計算もでき、表を完成させる事ができました。

それまでの回答者様たちの数式でしか試しておりませんが
＃6様がはじめに教えてくださった方法でも試してみたいと思います。
ありがとうございました。

==今回の質問にご回答くださった皆様==

何度か指摘されながらも最後までわかりやすい質問・補足ができず申し訳ありませんでした。
無事に、表を完成させる事ができました。
ありがとうございました。

補足

ありがとうございます。
まず、補足ですが、人数はひとつ前の補足に書かせていただいた通り
日々手入力、ですが、説明不足でわかりにくかったですね・・・
人数に関しては、計算式は全く使わずに、1,000人なら「1000」と手入力で入れていきます。

全体を想像できるようなサンプルの提示できておらず
わかりにくい質問となってしまい申し訳ありません。
次回からの質問の際は、もっと気をつけて書き込んでいきたいと思います。

回答No.5

imogasi

#4です。
Ａ２：Ｃ２にそれぞれ１、、２，３をいれ、Ｄ２：Ｉ２は空白の場合
=SUM((MOD(COLUMN(A2:I2),3)=1)*(A2:I2))
は３になりました。
Ｄ２：Ｉ２は空白で有るにもかかわらず。
ご存知と思いますが、私のＸＴＹの見出しははい列数数式に使ってませんから、関数式には関係ないです。
だからＤｉＶ０のエラーが出るというのは、別のことでしょうか。
ＭＯＤの演算対象になっているCOLUMN(A2:I2)もゼロになりませんし、０でない、３で割っていますので？？。
主要な部分について、具体的に、行と列の表現を使って、実例を挙げて状況を説明してください。
（良くこの質問コーナーで、エクセルの関数式の質問には、質問者が文章（あいまいになりやすい）で表現し、回答者が仮定の実例を上げているケースが頻見されるが、逆ではないかと思ってます。）

補足

ご回答ありがとうございます。
imogasi様のおっしゃる通り、確かに質問者が仮定の実例を挙げるべきですね。
その方が、回答する方も答えが導きやすいでしょうし、
何よりも質問する側の最低限のマナーですよね、申し訳ありません・・・。
わかりにくいかとは思いますが、以下に実例を挙げてみますので
見ていただければと思います。宜しくお願い致します。

================================================================================

今回合計を出したいものを 縦列ABC とし、それより前のセルにある商品名・単価は区別する為に 縦列※、●とする。

※ 商品名　● 単価　【A 販売数　B 販売金額　C 購買率】・・・〈A' 販売数合計　B' 販売金額合計　C' 平均購買率〉
【　】内が日々入力・算出する項目で、数十日分【　】が続いた後に〈各項目の合計と平均〉が来る。
上記が1行にある情報で、それが商品数分数百行下まで続く。

A）販売数　 日々の売上数を手入力、セルの書式は「数値」に設定
B）販売金額　販売数×単価　=$●*A
　　　　　　 セルの書式は「通貨」に設定、Aに入力がないときは￥0と表示されている。
C）購買率　　=A/1日の客数
　　　　　　 1日の客数は日々手入力、セルの書式はユーザー定義で「#,##0"人"」と設定している。
　　　　　　 日々客数を入力するので、客数カウントできない明日以降の分に関しては、セルに「＃DIV/O!」が表示されている。
　　　　　　 セルの書式は「パーセンテージ」に設定。
　　　　　　 実際は、見栄え状「＃DIV/O!」を見せないようにしたいので
　　　　　　 =IF(ISERROR(A/1日の客数),"",A/1日の客数)
　　　　　 　を使用して空白の状態にしている。

================================================================================

今、ABCをごく単純な数字で試しに計算してみたのですが
→（A=10、B=20、人数を1000人としてCを計算）×3日分
3日間とも人数も入力して C）購買率を「X％」と出してあげれば
お教えいただいた計算通りでABそれぞれの合計が出たのですが
3日間のうち1日でも人数が入らずにC) のセルの表示が＃DIV/O!　になっていると
計算式入力後「＃DIV/O!」と表示されてしまうようなのです。
なので、実際使用している表で、お教えいただいた式をしようしてみるときに
すでに入力済の日までの分（例えば、1月1日から昨日までの分）をCOLUMN(A2:l2)で選択してあげれば
問題なくそれぞれの合計が出ます。

なんともわかりにくい説明になってしまい申し訳ありません。
また、聞きたい事はそこじゃないのに・・・ということがあればご指摘ください。
宜しくお願い致します。

回答No.4

imogasi

配列数式で簡単にできます。
簡単な例にしますので、まず下記を納得して、自分の場合に修正するには、どこが変わるか、考えてください。
配列数式は（）＊（）は（）がＴＲＵＥなら１と、ＦＡＬＳＥなら０
と考えてよい。ＦＡＬＳＥの場合は、０を掛けられるので０になり、足されないのです。それらを列数文だけ合計（ＳＵＭ）されるという
理屈です。
ーーー
例データ
Ａ１：Ｉ２に
X Y Z X Y Z X Y Z
1 2 3 4 5 6 7 8 9
第1行目は見出し、ＸＹＺ，ＸＹＺ、ＸＹＺの3回繰り返しのつもり。
計算には関係なし。
データはＡ－Ｉへ１－９を入れる。どんな数を入れてよい。
ーーー
式は
見出しがＸの３列の和
=SUM((MOD(COLUMN(A2:I2),3)=1)*(A2:I2))
と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ同時押し。
同時に３キーを押してますか。
結果の値１２
Ａ列＋Ｄ列＋Ｇ列＝＞１＋４＋７＝１２
ーーー
見出しがＹの３列の和
=SUM((MOD(COLUMN(A2:I2),3)=2)*(A2:I2))
と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ同時押し。
結果の値１５
ーーー
見出しがＺの列の和
=SUM((MOD(COLUMN(A2:I2),3)=0)*(A2:I2))
と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ同時押し
結果の値１８
ーーー
結果

お礼

ご丁寧回答、ありがとうございました。
ただ、あと一歩のところでつまづいてしまいました。
もしおわかりになったら、もう一度アドバイスいただければ幸いです。

補足

上記の方法で試してみたところ、計算が可能でした。
ただ、XYZ,　XYZ　・・・の部分なのですが、既に完成された表ではなく
日々入力を行っているので、例えばその日の数字がわかるまでは
計算式のみが入っており、表の中では「＃DIV/O!」となっているセルもあります。
見栄えが悪いので「IF(ISERROR(・・・」で見た目は空欄になっていますが・・・
その「これから入力していくと数値が表れる」セルを除いて上記の方法で計算をすれば
求めたかったものが計算されるのですが「＃DIV/O!」が残っている状態では
計算はできないのでしょうか・・・
皆さんせっかくご丁寧に回答していただいているのに、なかなか解決せずに申し訳ありません。
こちらでも色々試してみますが、またアドバイスいただければと思います。
宜しくお願い致します。

回答No.3

keirika

A1:I1にデータが入力されていて、合計をJ1、K1、L1に求めると仮定します。

J1は=SUMPRODUCT(($A$1:$I$1)*(MOD(COLUMN($A$1:$I$1),3)=1))
K1は=SUMPRODUCT(($A$1:$I$1)*(MOD(COLUMN($A$1:$I$1),3)=2))
L1はJ1とK1の数値を使用してください。

ちなみに最大256列の為、84日分が限界だと思います。

EXCEL2003使用

お礼

ご回答ありがとうございます。
補足に書いた通り、残念ながらうまくいきませんでした・・・
補足をご覧になられて、もしこれは？と思い当たる点があるようでしたら
またアドバイスいただければ幸いです。

補足

ありがとうございます。

お教えいただいた計算式で試してみましたが、やはりEnterを押すと「0」となってしまいます。
セルの書式設定などにも問題があるのでしょうか・・・？
ちなみに
●販売数=数値・販売金額=通貨で表示
●販売数・販売金額ともに、別シートで細かく表にしてあるものを
まとめてあります。（=’Sheet1’A1：A5　のように）
●他の質問で探した際にみかけたようにcontrol＋shift＋Enterをすると
＃VALUE!となってしまいます。

回答No.2

mshr1962

D列に1,2,3と３行おきに同じ数値をセットすれば
=SUMIF(D1:D99,1,A1:A99)
のようにしてD列の値が1の行のA列の集計が可能です。
上記以外だと
=SUM(IF(MOD(ROW(A1:A99),3)=1,A1:A99))
としてCtrl+Shift+Enterで配列数式にする。
※上の例はA1,A4,A7...を集計します。A2,A5なら=2、A3,A6なら=0としてください。
上記を配列数式以外で行いたいなら
=SUMPRODUCT((MOD(ROW(A1:A99),3)=1)*1,(A1:A99))
とすれば計算可能です。

SUMIF関数：条件付集計関数です。
ROW関数：カッコ内のセルの行番号を表示します。
MOD関数：数値を引数で割った余りを表示します。
SUMPRODUCT関数：範囲の積の和を集計します。
範囲が条件の場合、1を掛けることで正しい場合に1、異なる場合に0に置き換えて計算します。

お礼

ご回答ありがとうございました。
補足の通り、こちらの説明不足で失礼致しました。

補足

ありがとうございます。
すみません、私の説明不足でした。計算したいのは、例えば
A1+D1+G1+J1・・・
B1+D1+G1+J1・・・
です。
でも、お教えいただいた式も他の表を作成する際に利用できそうです。

＃1さまのご回答への補足で、補足し忘れたありましたので
こちらで失礼します。
購買率の合計・・・確かに、そうですね。
購買率は、平均を出しますので、販売数を使用して算出しますので
今回の質問へは直接関係ありませんでしたね、たいへん失礼致しました。

回答No.1

dial8675

excelで255列以上扱えましたっけ?
3つ目の、購買率の合計って何か意味があるんですか?

各セルには数式にしろ何にしろ値が入っているのなら、きっと数式が間違っているんだと思うんです。

お礼

ご回答ありがとうございました。

補足

＞excelで255列以上扱えましたっけ?
すみません「100日ほど続く」は例えです。
確かに列は256列以上扱えませんよね、失礼致しました。