特定の会社の売り上げを一覧表から計算する方法
- 特定の月で特定の会社の売り上げを一覧表から計算する方法について困っています。
- 現在、エクセルの入力範囲を超えてしまったため、新たな方法を探しています。
- 頭に＃を付けた会社名を対象とする方法や他の関数など、効率的な方法があれば教えてください。
- ベストアンサー
＃がついている項目全部を計算対象とするには
一覧表から「特定の月」で「特定の会社」の「売り上げ」を足そうと 下記のような式で対応していましたが、「特定の会社」が多くなりすぎて、困っています。 現在、この式を「+」で足していっていますが、エクセルの入力範囲を超えてしまいました。 「頭に＃を付けた会社名を対象とする」方法はないでしょうか？ もしくは、違う関数など、もっといい方法があれば教えていただけるととても助かります。 =SUMPRODUCT(('▲期 '!$B$5:'▲期 '!$B$2099=3)*('▲期 '!$D$5:'▲期 '!$D$2099="＃●●●")*('▲期 '!$S$5:'▲期 '!$S$2099)) どうぞよろしくお願いいたします。
- chocomonaco
- お礼率100% (6/6)
- Excel（エクセル）
- 回答数6
- ありがとう数6
- みんなの回答 （6）
- 専門家の回答
質問者が選んだベストアンサー
ご利用のエクセルのバージョンが不明のご相談ですが、 ＞もしくは、違う関数など、もっといい方法 Excel2007以降でSUMIFS関数を使うのが一番良い方法です。 =SUMIFS('▲期'!S:S,'▲期'!B:B,3,'▲期'!D:D,"＃*") 2003以前のエクセルを使ってるのでしたら、別列（例えばＴ列）に Ｔ５： =IF(AND(B5=3,LEFT(D5)="＃"),"○","") などのようにして、ふつーに =SUMIF('▲期'!T:T,"○",'▲期'!S:S) のように計算するのが賢明な方法です。 今のようにSUMPRODUCT関数で数千行を対象に計算させるのは、重くなってしまいお勧めの方法とは言えません。
その他の回答 (5)
- kagakusuki
- ベストアンサー率51% (2610/5101)
もしも、御質問の目的が、特定の企業グループ等に所属している店の売り上げを求めるためなどではなく、単に「区別をつけるためだけに、わざわざ『#』を付けて入力された企業名」のみの売り上げの合計を求める、という事であるとした場合には、わざわざ「#」を付けて入力する事をせずに済ませる方法もあります。 例えば、「特定の会社」の会社名の一覧を、適当な所（ここでは仮にSheet2のA4以下のセル範囲とします）に作成しておきます。 その上で、「特定の会社」の3月の「売り上げ」を表示せるセルには、次の様な関数を入力して下さい。 =IF(COUNTIF(Sheet2!$A:$A,"*?")-COUNTIF(Sheet2!$A$1:$W$3,"*?"),SUMPRODUCT((INDEX(Sheet2!$A:$A,ROW(Sheet2!$A$3)+1):INDEX(Sheet2!$A:$A,MATCH(CHAR(1),Sheet2!$A:$A,-1))<>"")*SUMIFS('▲期 '!$S:$S,'▲期 '!$B:$B,3,'▲期 '!$D:$D,INDEX(Sheet2!$A:$A,ROW(Sheet2!$A$3)+1):INDEX(Sheet2!$A:$A,MATCH(CHAR(1),Sheet2!$A:$A,-1)))),"") 尚、上記の関数は、SUMIFS関数を使う事が出来ないExcel2003等で使用する場合の関数です。 もし、Excel2007以降のSUMIFS関数を使う事が出来るExcelを使用するのでしたら、次の様な関数となります。 =IF(COUNTIF(Sheet2!$A:$A,"*?")-COUNTIF(Sheet2!$A$1:$W$3,"*?"),SUMPRODUCT(SUMIFS('▲期 '!$S:$S,'▲期 '!$B:$B,3,'▲期 '!$D:$D,INDEX(Sheet2!$A:$A,ROW(Sheet2!$A$3)+1):INDEX(Sheet2!$A:$A,MATCH(CHAR(1),Sheet2!$A:$A,-1)))*1),"") 因みに、2番目に挙げたSUMPRODUCT関数とSUMIFS関数を組み合わせた関数の場合、SUMPRODUCT関数を使用してはいますが、SUMPRODUCT関数による反復計算の対象としている行の範囲は、▲期シートの5行目～2099行目などではなく、「Sheet2のA列において企業名が入力されている行範囲」のみとなっていますので、「特定の会社」の数が極端に多いのでもない限りは、反復計算を少ない回数で済ませる事が出来るため、計算処理は殆ど重くなりません。
お礼
ご回答いただき、ありがとうございます。 他部署を兼任している上司に、「＃｝(他にも＊の会社もあり）を使った会社は ひとつにまとめて欲しいという依頼でしたので、なぜ＃がついているかは未だに不明なんです。 SUMPRODUCT関数とSUMIFS関数を組み合わせると 反復計算を少ない回数で済み、計算処理は殆ど重くならないとのこと。 同じ関数を使っているのに、組み合わせでかわってくるものなのですね。 色々と教えていただきありがとうございます。 次回の参考にさせてもらいますね。
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答No.2様が仰る様に、5行目～2099行目の様な2000行以上にも亘る範囲を対象としている場合には、SUMPRODUCT関数では処理が重くなるため、あまり良い方法とは申せませんが、どうしてもSUMPRODUCT関数で処理を行いたいという場合には、次の様な関数となります。 =SUMPRODUCT(('▲期 '!$B$5:$B$2099=3)*(LEFT('▲期 '!$D$5:$D$2099,1)="#")*'▲期 '!$S$5:$S$2099) 御質問文中に記されている質問者様が考えられた例では、「'▲期 '!$B$5:'▲期 '!$B$2099」等の様に、「:」の直後にもシート名が記されていますが、「:」の直後にまでシート名を入れる必要は御座いません。 それから、例えば =SUMPRODUCT(('▲期 '!$B$5:$B$2099=3)*ISNUMBER(FIND("#",'▲期 '!$D$5:$D$2099))*'▲期 '!$S$5:$S$2099) 等の様に、FIND関数とISNUMBER関数を組み合わせる事で「#」の有無を判定させますと、「頭に＃を付けた会社名」以外にも、「会社名のどこかに『＃』という文字が含まれている会社」（会社名の途中や末尾に「＃」の文字があるもの）の売上金額も合計値に含まれる様になります。
お礼
SUMPRODUCT関数で処理がそんなに重くなるとは思ってもいませんでした。 「:」の直後にまでシート名を入れる必要はないとのこと。 知りませんでした。教えてくださりありがとうございます。 FIND関数とISNUMBER関数を組み合わせる事で「#」の有無を判定させると、 「会社名のどこかに『＃』という文字が含まれている会社」になるとのこと。 組み合わせ方で色々できるのですね。 あまりたくさんの関数を使うことがないので勉強になります。 ありがとうございました！
- keithin
- ベストアンサー率66% (5278/7940)
＞１月～１２月各月の集計をしないといけない あなたが作成した数式しか、どこに何を記入していてどういう集計をしたいのか情報がありません。 勝手に推測して「Ｂ列に月が記入してある」のだとすると、Ｔ列に Ｔ５： =IF(LEFT(D5)="＃",B5,"") のようにして「＃な会社の月」をＴ列に再掲してしまえば、 =SUMIF(T:T,3,S:S) のようにして「３の月の＃の集計」を簡単に拾うことができます。
お礼
そうですよね、数式しか情報として載せていないのに 回答を求めるのは傲慢ですよね。すいませんでした。 おっしゃるとおりB列に月が記入してある一覧です。 なるほど、こういう方法もあるのですね。こちら試したところ集計できました。 情報不足ですのに、丁寧な回答ありがとうございました！
- MackyNo1
- ベストアンサー率53% (1521/2850)
Excel2003以前のバージョンを利用していて、補助列なしに現在のSUMPRODUCT関数で集計したいなら、以下のようなFIND関数を使うことになります。 ==SUMPRODUCT(('▲期 '!$B$5:'▲期 '!$B$2099=3)*ISNUMBER(FIND("＃",'▲期 '!$D$5:'▲期 '!$D$2099)）*('▲期 '!$S$5:'▲期 '!$S$2099))
お礼
ありがとうございます！ こちらの方法でも大丈夫でした。 今回はSUMIFSを利用することにしましたが、 FIND関数を使うとは思いもつきませんでした。 教えてくださりありがとうございました。
- kankanka8322
- ベストアンサー率10% (72/711)
SUMIF関数が使えるのではないでしょうか。
お礼
早速の回答、ありがとうございます！ 項目ひとつに対してしか合計を出さないと思っていたので SUMIFは使えないと思いSUMPRODUCTを使っていました。 SUMIF関数で何とかならないか、再検討してみますね。
関連するQ&A
- SUMPRODUCT 複数条件設定で、計算式結果がおかしいです。
下記のような表があったと仮定します。 A B C D 1 月度 店舗 品目 売上金額 2 4 A あ 22300 3 4 B あ 18700 4 4 C あ 14500 5 4 A い 17950 6 5 B あ 44000 7 5 C い 35000 8 5 A う 12000 9 5 B い 13400 この表から、 店舗「A」、品目「あ」の売上合計は、22300になり、 数式「=SUMPRODUCT(($B$2:$B$9="A")*($C$2:$C$9="あ")*$D$2:$D$9)」で計算できます。 店舗「A,B」、品目「あ」の売上合計は、85000になり、 数式「=SUMPRODUCT(($B$2:$B$9={"A","B"})*($C$2:$C$9="あ")*$D$2:$D$9)」で計算できます。 店舗「A」、品目「あ,い」の売上合計は、40250になり、 数式「=SUMPRODUCT(($B$2:$B$9="A")*($C$2:$C$9={"あ","い"})*$D$2:$D$9)で計算できます。 しかし、 店舗「A,B」、品目「あ,い」の売上合計は、116350なのですが、 数式「=SUMPRODUCT(($B$2:$B$9={"A","B"})*($C$2:$C$9={"あ","い"})*$D$2:$D$9)」では、数値がおかしく（35700に）なります。 数式の使い方が間違っているのでしょうか？ アドバイスをお願いします。
- ベストアンサー
- オフィス系ソフト
- 【エクセル】ある特定文字を含んでいるデータを計算したい
複数条件でのデータ集計をしています。 条件は、商品名、担当者名、一宮市を含む、の売上（Ｄ列）合計です。 関数はSUMPRODUCTを使っていますが、一宮市を含むの条件でうまく 計算されません。 =SUMPRODUCT(($A$1:$A$200="商品名")*($B$1:$B$200="担当者名")*($C$1:$C$200="*一宮市*")*($D$1:$D$200))という式ではエラーに なってしまいます。 一宮市セルは前後に複数文字があります。 うまくいく方法を教えていただきたいのと、上記の式でエラーになる 理由も併せて教えていただければと思います。 お詳しい方、よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- 計算式が分かりません
Excel2003にて計算表を作りたいのですが。 A列に名前を、B列に売上金額を、C１に最高売上金額を、D1にその最高売上をした人の名前を表示したいのですが、 C1とD1に入れる、関数は又は、計算式が分からなくて困っています。どなたか、分かる人がいましたら教えてください。特にD1がぜんぜん分からないのです。よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excel：特定の項目だけ平均値を出したい
お世話になります。 画像参照頂き、ご教授いただければ幸いです。 画像のような表を作り、「いちご」のみ数値の平均を求めたい場合 どのような関数を使えばよいでしょうか。 【試した方法】 =SUMPRODUCT(NOT(ISERROR(FIND("いちご",範囲)))*範囲)/SUMPRODUCT(NOT(ISERROR(FIND("いちご",範囲)))*1) →「#DIV/0!」が返されました。範囲にゼロ除算セルはなかったのですが… その他作業列を作ってみたり、条件付き書式を設定しましたが うまくいきませんでした。 バージョンは2003です。 よろしくお願いいたします。
- 締切済み
- オフィス系ソフト
- エクセル、指定の期間の売上を別の列から計算したい
エクセル2010です。 図のような売上表があり、A列に毎日の日付、B列にその売り上げが入っています。 新たにE列に1週間ごとの売り上げを表示するために、図のようにSUMPRODUCT関数を使いこのように記入したのですが、エラーになってしまいます。 以前これと同じようなケースあり、そのときはこれで使えていたので、その関数を流用したのですが、今度はなぜかこれでは駄目なようなのです。 どの部分が問題なのでしょうか、よろしければアドバイスをいただけますでしょうか。 よろしくお願いします。
- ベストアンサー
- Excel（エクセル）
- 縦列の計算（SUMPRODUCT？）
初心者です。どうか助けてください！ A B C ・・ L 1 名前 単価 1日の売り上げ ・・・10日の売り上げ 2 ああ 200 2 3 いい 100 1 4 （空白） 5 ええ 150 4 ― ― ― ― 6 （日々売上） 1100 ・・・ というような表を作りたいのですが（A、Bは他のシートからVLOOKUPで飛ばしています。）うまくいきません。 びっちりと詰まった表であればSUMPRODUCTでOKのようですが、どうしてもうまくいかないのです。どうしても空白の行ができてしまうので・・・ どうしたらうまくいくのでしょうか？？
- ベストアンサー
- オフィス系ソフト
- エクセルにおいての配列関数について
エクセル の配列関数のSUMPRODUCT使用して このような式を書いた場合 SUMPRODUCT((条件１)*(条件２)*・・・(条件ｎ)*(集計範囲)) =SUMPRODUCT((A1:A30=50)*(B1:B30=750)*(C1:C30=6600)*(D1:D30=210)*F1:F30) 集計範囲の行 いわゆるＦの行の数字の中の一つのセルに 「３」などのカッコを使用した ものをいれると "＃VALUE!" と表示され正しい答えが出ないのですが 解決方法を教えてください。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- SUMIFがいきなり計算されなくなる
売上台帳から日付ごとに売上額の集計をしているのですが、１２/２６以降の売上が集計表に"０"で表示されてしまいます。 原因がさっぱりわかりません。 考えられることは何かありますか？ 関数の式は =SUMIF(新宿12!$B$6:$L$804,$A31,新宿12!$R$6:$R$804) 読み込むシートのタイトルは新宿１２で、 B列には日付 R列には売上金額 が記載されています。 12/25までは問題なく表示されていて、２６日以降の関数式を新たに入力しなおしても値が"０"になってしまいます。 OSはWINDOWS７ EXCELは2010 よろしくお願いします。
- ベストアンサー
- Windows 7
- INDIRECT関数のことで教えてください
Sheet1に表を作ろうとしています。同じBooKのなかに「売上実績0610」・「売上実績0611」・・・と複数のシートがあります（どんどん増えます）。 Sheet1のＢ列に、=IF(COUNTIF(売上実績0610!$B$6:$C$1608,B3)=0,0,B3)という式を設定し縦の列をつくり、Ｃ列・Ｄ列とつくっていきます。 この式の【売上実績0610!$B6$:$C$1608】の部分にINDIRECT関数を使いたいのですが範囲指定がどうしてもうまくいきません。 どなたかご教授お願いします。（EXCEL2003です）
- 締切済み
- オフィス系ソフト
- エクセルで異なる表を一つにまとめる方法を教えてください！
表1.はランダムに、Ａ社、Ｂ社、Ｄ社の売上データがあります。 表2.はランダムに、(株)Ｃ社、Ｄ(株)、Ａのように利益の表があります。 これを、表3.のように、Ａ社、Ｂ社、Ｃ社．．．の売上、利益を 一覧にしたデータを作成したいです。 何らかの関数か、検索かで、マッチングをいう機能があるということを 聞いたことがあるのですが、どのような方法でもかまいませんので ご教授いただけますでしょうか？表1と表2はまれに、Ａ社、(株)Ａの ように同一の会社でありながら、(株)の有無や、スペースが空いていたります。 3,000社くらいのデータを取りまとめるため、比較的簡便に やる方法をご教授いただけませんでしょうか？ よろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
お礼
早速の回答ありがとうございます！！ エクセルのバージョンによって関数の使い方もかわるのですね。 会社では2010を使ってるのですが、うまいやり方がわからず、 自宅の2003にて検討していました。 １月～１２月各月の集計をしないといけないので Excel2007以降のSUMIFS関数を使う方法でなんとかできたらと思っています。 (保存方法が、定かではないですが・・） 会社で試してみます。ありがとうございました！！
補足
会社で試してみました！ 2010バージョンだったので下記の計算式でやったところ集計できました！ =SUMIFS('▲期'!S:S,'▲期'!B:B,3,'▲期'!D:D,"＃*") 他にも、一社のみや、＊をつけた会社がありましたので 他の方でもわかるように、上記の計算式で統一しました。 こんなにもすっきりした計算式で集計できて感謝いたします。 範囲指定をセルで選択しなくていいということも発見でした。 知識不足なのにも関わらず、使わない関数を利用するのは危険ですね。 本当にありがとうございました！