[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『同じ地域のものを抽出して合算』（文）OS:WindowsXP,Excel2003
はじめまして、どうしてもうまくいきません。助けてください。
[A] [B] [C] [D] [E] [F] [F] [F] [1] 首都圏 予算 実績 比 関西 予算 実績 比 [2] 経費1 1000 1500 150％ 経費1 1200 1000 83％ [3] 経費2 経費2 [4] 経費3 経費3 [5] 合計 合計 [6] [7] 関西 予算 実績 比 東海 予算 実績 比 [8] 経費1 1000 1500 150％ 経費1 1200 1000 83％ [9] 経費2 経費2 [10] 経費3 経費3 [11] 合計 合計
A1：D5で一つとするデータが縦横に4つずつ、合計16個あります。 それぞれ地域ごと（首都圏、関西、東海）に それぞれの合計（経費1、経費2、経費3、合計）を出したいのですが どのような数式を入れればよいでしょうか？
地域は首都圏、関西、東海、九州と4種類と決まっているのですが 16個の中にバラバラに入っており、規則性はありません。
ちなみに合計欄は下の方にA1：D5同様の形式でそこに収めたいのです。
ご教授お願いします。
ムリムリですが・・・ データ範囲:A1:P23（下の空白行までとすると、P24) 合計欄（1エリア）範囲：A26:D30 とすると、 B27セルに =SUMPRODUCT(($A$1:$M$19=$A$26)*(B$1:N$19=B$26)*($A2:$M20=$A27)*IF(ISNUMBER(B2:N20),B2:N20,0)) として、CtrlとShiftとEnterを一緒に押して、配列数式として確定。 これを該当範囲にコピー。但し、比率は、数式を入力して下さい。 多分答えが求まると思います。
ただし、もっと広範囲の場合は、EXCEL君が職場放棄する事も考えられます。
せめて、↓こんな感じの入力データなら、EXCEL君も働く気になるかも？？？ 区分 エリア 経費区分 予算 実績 比 1 首都圏 経費1 1000 1500 150% 1 首都圏 経費2 1 首都圏 経費3 2 関西 経費1 1200 1000 83% 2 関西 経費2 2 関西 経費3 (sin)
早速やってみたのですが、結果が＃N／Aと出てしまいます。 実は、例は簡単にしたものであって 実際は一つとするデータは縦項目が20、横項目は8あり（A1:H20） 16個分あるとかなり広範囲になります。
提案のようなデータに変換できればいいのですが、 このフォーマットは会社で決まっており、変更できません。
やはり自分で地域を拾って足していくしかないのでしょうか。。。
こんにちは〜♪
失礼しま〜す。。。
>実際は一つとするデータは縦項目が20、横項目は8あり（A1:H20） >16個分あるとかなり広範囲になります。
実際の表がよく理解できませんので。。 例題の場合です。。。
┌─┬────┬────┬────┬────┬────┬────┬────┬────┬───────┬───────┐ │ │ A │ B │ C │ D │ E │ F │ G │ H │ I │ J │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │ 1│首都圏 │予算 │実績 │比 │関西 │予算 │実績 │比 │首都圏首都圏 │関西関西 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │ 2│経費1 │ 1000│ 1500│ 150%│経費1 │ 1200│ 1000│ 83%│首都圏経費1 │関西経費1 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │ 3│経費2 │ │ │ │経費2 │ 2200│ │ │首都圏経費2 │関西経費2 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │ 4│経費3 │ │ │ │経費3 │ 3200│ │ │首都圏経費3 │関西経費3 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │ 5│合計 │ │ │ │合計 │ 6600│ │ │首都圏合計 │関西合計 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │ 6│ │ │ │ │ │ │ │ │首都圏 │関西 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │ 7│関西 │予算 │実績 │比 │東海 │予算 │実績 │比 │関西関西 │東海東海 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │ 8│経費1 │ 1000│ 1500│ 150%│経費1 │ 1200│ 1000│ 83%│関西経費1 │東海経費1 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │ 9│経費2 │ 2000│ │ │経費2 │ │ │ │関西経費2 │東海経費2 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │10│経費3 │ 3000│ │ │経費3 │ │ │ │関西経費3 │東海経費3 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │11│合計 │ 6000│ │ │合計 │ │ │ │関西合計 │東海合計 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │12│ │ │ │ │ │ │ │ │関西 │東海 │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │13│ │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │14│ │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │15│ │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │16│首都圏 │予算 │実績 │比 │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │17│経費1 │ 1000│ 1500│ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │18│経費2 │ 0│ 0│ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │19│経費3 │ 0│ 0│ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │20│合計 │ 0│ 0│ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │21│ │ │ │ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │22│関西 │予算 │実績 │比 │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │23│経費1 │ 2200│ 2500│ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │24│経費2 │ 4200│ 0│ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │25│経費3 │ 6200│ 0│ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │26│合計 │ 12600│ 0│ │ │ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┼────┼────┼────┼───────┼───────┤ │27│ │ │ │ │ │ │ │ │ │ │ └─┴────┴────┴────┴────┴────┴────┴────┴────┴───────┴───────┘
★作業列を使う方法ですが。。。 作業列その１ I列の I1セルへ =INDEX(A:A,INT(ROW(A6)/6)*6-5)&A1
作業列その２ J列の J1セルへ =INDEX(E:E,INT(ROW(A6)/6)*6-5)&E1
それぞれ下にコピー。。。
★集計表で首都圏の合計の場合。。
SUMIF関数を使ってみました。。。 B17セルへ =SUMIF($I:$I,INDEX($A:$A,INT(ROW(A6)/6)*6-5+15)&$A17,B:B)+ SUMIF($J:$J,INDEX($A:$A,INT(ROW(A6)/6)*6-5+15)&$A17,F:F)
右下へコピー。。
★関西の合計の場合。。 B23セルへ =SUMIF($I:$I,INDEX($A:$A,INT(ROW(A6)/6)*6-5+21)&$A17,B:B)+ SUMIF($J:$J,INDEX($A:$A,INT(ROW(A6)/6)*6-5+21)&$A17,F:F)
右下へコピー。。。
★比率は、別に式を入れて下さいネ！！。。。
ご参考にどうぞ。。。
。。。。Ms.Rin〜♪
さすが、Ms.Rin〜♪さん。柔らかいですね。私は固すぎ。。。 文さんの実際のデータは、A1:AF80ぐらいでしょうから、AH:AK列を作業列にし各列をSUMIFで足せばよい事になりますね？
ところで、 >結果が＃N／Aと出てしまいます。 の件ですが、 シート内のどこかに#N/A値があると思います。 シート全体を選択し、【編集】-【ジャンプ】-『セル選択』をクリックし、 「数式」にチェック、「エラー値」のみをチェックし、OKで、#N/Aのセルが発見できると思われます。 （数式の結果であれば ですが）
ちなみに、私の書き込んだ数式を実範囲に置き換えると、 =SUMPRODUCT(($A$1:$Y$64=$A$86)*(B$1:Z$64=B$86)*($A2:$Y65=$A87)*IF(ISNUMBER(B2:Z65),B2:Z65,0)) こんな数式になります。 ※範囲や見出しが不明なので適当に作ったデータ表ですが・・・ ついでに、実データをこんな感じ（上の例）のフォームに置き換える方法なんぞを考えてみましたが、 Ms.Rin〜♪の方法を使えば不必要なので、UPしません（人に見せられるものでない。 が本音ですが・・・）。 (sin)
反応が遅くて申し訳ありません。 お二方とも、本当にありがとうございます。
sin様の数式ですが、エラー値を消したところ０表示となりました。
実は、地方名の下に日付が入っているのですが、これが影響しているのでしょうか？ 実際のところ、下記のようになっております。 何度も言うたびに形式が代わり、申し訳ありません。
[A] [B] 〜 [H] [I] [J] [K] 〜 [Q] [R] [S]〜一番端は[AI] [1] 首都圏 部署名 首都圏 部署名 東海 [2] 日付 予算 実績 比 日付 予算 実績 比 日付 [3] 経費1 1000 1500 150％ 経費1 1200 1000 83％ 経費1 経費2 経費2 ｜ 経費3 経費3 [24] 合計 合計 [25] [26] 関西 部署名 東海 部署名 日付 予算 実績 比 日付 予算 実績 比 ｜ 経費1 1000 1500 150％ 経費1 1200 1000 83％ 経費2 経費2 経費3 経費3 [49] 合計 合計
｜
データの一番下は[99]
合計欄はA101：Z124に作りたいと思います。
上記のシートの場合、sin様の数式を当てはめると =SUMPRODUCT(($A$1:$AB$76=$A$102)*(B$1:AC$76=B$102)*($A2:$AB77=$A103)*IF(ISNUMBER(B2:AC77),B2:AC77,0))
でよろしいでしょうか？エクセルをはじめたばかりで不安なのですが…。 SUMPRODUCTのくだりはなんとなく理解できますが IF以下が何のために入れてあるのか良く理解できません。
=SUMPRODUCT((検索範囲=首都圏)*(検索範囲=予算)*(検索範囲=経費1)*IF(ISNUMBER(？),？,0))
↑IFまでの認識は上記であっていますでしょうか？ 無知で申し訳ありません。
Ms.Rin〜♪様
ご回答ありがとうございます。
作業列は上記の表の場合 =INDEX(A:A,INT(ROW(A25)/25)*25-24)&A1 と、なるとまでは どうにかたどり着いたのですが（合っているかは不安ですが）
=SUMIF($I:$I,INDEX($A:$A,INT(ROW(A6)/6)*6-5+15)&$A17,B:B)+ SUMIF($J:$J,INDEX($A:$A,INT(ROW(A6)/6)*6-5+15)&$A17,F:F)
この数式が理解できません。 作業列をAK：ANに作ったとして
=SUMIF($AK:$AK,INDEX($A:$A,INT(ROW(A25)/25)*25-24+15)&$A17,B:B)+ SUMIF($AL:$AL,INDEX($A:$A,INT(ROW(A25)/25)*25-24+15)&$A17,K:K)＋ SUMIF($AM:$AM,INDEX($A:$A,INT(ROW(A25)/25)*25-24+15)&$A17,T:T)＋ SUMIF($AN:$AN,INDEX($A:$A,INT(ROW(A25)/25)*25-24+15)&$A17,AC:AC)
では、数値は全て0になってしまいました。 ところで途中の+15は何を意味しているのでしょうか？
分からない尽くしで、本当に申し訳ありません。
式の作り方はあっていると思います。（A102セルに 首都圏 が入っているなら） >０表示となりました。 上でも書いてますが、CtrlとShiftとEnterを一緒に押して、配列数式として確定。されてますか？
>IF(ISNUMBER(B2:AC77),B2:AC77,0)) B2:AC77の各セルが数値ならば、その数値を、違う場合は、0 にしなさい。です。 この各を有効にするために、配列数式としてます。 理由：B2:AC77だけだと、範囲の中にある文字列が、SUMPRODUCT関数による配列の計算上邪魔になるため。 自分の書き込みに関してだけで申し訳ありませんが・・・ (sin) ちょっと、忙しくなりかけ なので・・・
sin様 お忙しいところ、親切な解説ありがとうございます。 首都圏はA101に入ってました。 訂正をして、配列数式として確定しましたが、やはり結果は0になります。
数値以外を0にするとあったので、入力されている数字の書式をみたら通貨になっておりました。 数字は全て数値に直しましたが…結果は0。
いったい何がいけないのでしょうか…。 急いでおりませんので、お暇な際にでもお答えくださるとうれしいです。 （文）
ふたたび〜です。。。♪
8列づつが、４つですネ！！ でしたら。。 作業列を4つ作らないといけませんね。。。
>一番端は[AI]
なので、AJ列〜AM列まで作業列を作ります。。
★作業列の作成 AJ1セルへ =INDEX($A:$AI,1,COLUMN(B1)*9-17)&INDEX($A:$AI,ROW(A25)/25*25-24,COLUMN(B1)*9-17)
右へAM列まで、下は、99行目までコピーします。。。
★集計表は、以下の様な感じでしょうか。。。？
A B C D E F G H I J K L M [101] 首都圏 部署名 関西 [102] 予算 実績 比 予算 実績 比 [103] 経費1 3001 6010 経費1 [104] 経費2 4002 0 経費2 [105] 経費3 6003 0 経費3 [106] 経費4 0 0 経費4 [107] 経費5 0 0 経費5 [108] 経費6 0 0 経費6 [109] 経費7 0 0 経費7 [110] 経費8 0 0 経費8 [111] 経費9 0 0 経費9 [112] 経費10 0 0 経費10 [113] 経費11 0 0 経費11 [114] 経費12 0 0 経費12 [115] 経費13 0 0 経費13 [116] 経費14 0 0 経費14 [117] 経費15 0 0 経費15 [118] 経費16 0 0 経費16 [119] 経費17 0 0 経費17 [120] 経費18 0 0 経費18 [121] 経費19 0 0 経費19 [122] 経費20 0 0 経費20 [123] 経費21 0 0 経費21 [124] 合計 13006 0
★首都圏の場合 B103セルへ =SUMIF($AJ:$AJ,$A$101&$A103,B:B)+ SUMIF($AK:$AK,$A$101&$A103,K:K)+ SUMIF($AL:$AL,$A$101&$A103,T:T)+ SUMIF($AM:$AM,$A$101&$A103,AC:AC)
右と下へコピーして下さい。。。。
**合計値や、経費の項目は適当に入れました。。。
ふたたび〜、ご参考にどうぞ。。。
Ms.Rin〜♪♪
あっと！！！ レイアウトが違っているのに気付いてなかった。。。 こっち↓だと出ますか？ =SUMPRODUCT(($A$1:$AB$76=$A$101)*(B$2:AC$77=B$102)*($A3:$AB78=$A103)*IF(ISNUMBER(B3:AC78),B3:AC78,0)) $A$1:$AB$76 ===『首都圏』が入っているセルの左上から右下の範囲 B$2:AC$77 =====『予算』 〃 $A3:$AB78 =====『経費1』 〃 B3:AC78 =======「予算」と「経費1」が交わるセルの左上から右下の範囲 が、範囲設定の意味です。 各範囲は、行・列数が同じになってます。それぞれの範囲の行列位置が同じ各セル同士が、 $A$101,B$102,$A103と同じ場合（TRUE*TRUE*TRUE=1）とB3:AC78範囲の数値（IF関数による、元数値か0）を 掛け算し、全範囲分を合計する。 という計算です。 (sin)
Rinで〜す。。。♪
sinさんと答が、違っていたので検証したら 私の作業列の式が違っていました〜♪
AK〜KN列を作業列にするのでしたら AK1セルへ
=INDEX($A:$AI,INT(ROW(A25)/25)*25-24,COLUMN(B1)*9-17)&INDEX($A:$AI,ROW(A1),COLUMN(B1)*9-17)
右下コピー。。。
★首都圏の場合 B103セルへ =SUMIF($AK:$AK,$A$101&$A103,B:B)+ SUMIF($AL:$AL,$A$101&$A103,K:K)+ SUMIF($AM:$AM,$A$101&$A103,T:T)+ SUMIF($AN:$AN,$A$101&$A103,AC:AC)
右下コピーです。。。
sinさんへ いつも勉強させて頂いていま〜す。。。♪
>Ms.Rin〜♪さん。柔らかいですね。私は固すぎ。。。
そんなことは、ないですヨ〜。。。 普通でしたら、sinさんの様な式をアップするところです。。。
配列関数 & 配列数式は、sinさんも書かれているように
>ただし、もっと広範囲の場合は、EXCEL君が職場放棄する事も考えられます。
なので、止めたんですが。それほど重くないみたいですね。
SUMIFに、こだわってみましたけれど。。。 私の作業列の式は、4列あるので１つにしたら結構長くなってしまいましたし。。。 式も難しくなってしまいました。。
あまり、作業列を使った意味がなかったみたいです。。
今回位のデータ数でしたら、sinさんの回答が良いですね。。
失礼しました〜。。。Ms.Rin〜♪
無事完成する事が出来ました！！
まずはsin様 出来ました〜〜〜〜！！！！ sin様の数式をそのまま貼ったらそのまま出来ました！ 画面が見えてないのにズバリ作れる才能に脱帽です。 本当にありがとうございます〜〜〜！！！！！
4つの（）の中のセル数が同じでないといけないのですね。 とても分かりやすい説明ありがとうございました。
そしてMs.Rin〜♪様 何度もありがとうございます。 こちらでも出来ました！！ 作業列マスターすれば、今後いろんなことに使えそうですね！
これを機にエクセルの勉強を始めようと思います。 お二方とも、本当にありがとうございました。
（文）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.