[[20061222125003]]　『2つの条件を満たした値の合計を参照』（しゅうき）　ページの最後に飛ぶ

『2つの条件を満たした値の合計を参照』（しゅうき）

 こちらではいつもお世話になっております。
 また皆様のお力を貸してください。

 下のような入力用シートがあります。

 [sheet福岡]
    A            B       C      D　　  E      F      G 
  3    　　　　　　　　　        トヨタ　       ホンダ　   
  4 日付         名前    用途    新　　 中     新　   中
  5 2006/12/10   山田    通勤    10    20     10     20
  6 2006/12/10   山田    通勤    10    20     10     20
  7 2006/12/18   山田    通勤    10    20     10     20
  8 2006/12/18   山田    休日    10    20     10     20
  9 2006/12/22   山田    通勤    10    20     30     20
 10 2006/12/22   佐藤    休日    10    20     30     20
 11 2006/12/22   山田    通勤    10    20     30     20
 12 2006/12/22   和田    休日    10    20     30     20

 実際のシートは[sheet福岡]だけではなく[sheet長崎]等多数あります。

 [sheet車種合計]のセルＢ3の日付と同じ日を[sheet福岡]A列から探し出し、
 さらにその日の用途別の合計が下記[sheet車種合計]のE4:E*のように
 集計されるようにしたいのですができませんでした。
 　
 [sheet車種合計]
    B     C    D       E　　　F　　　G　　　H
  3 2006/12/22         福岡   長崎　 熊本　 宮崎
  4 通勤  新   トヨタ　 20 
  5 通勤  新   ホンダ   60
  6 通勤  新   日産
  7 通勤  新   三菱
  8
  9 通勤  中   トヨタ   40
 10 通勤  中   ホンダ   40
 11 通勤  中   日産
 12 通勤  中   三菱
 13
 14
 15 休日  新   トヨタ   20 
 16 休日  新   ホンダ   60
 17 休日  新   日産
 18 休日  新   三菱
 19
 20 休日  中   トヨタ   40
 21 休日  中   ホンダ   40
 22 休日  中   日産
 23 休日  中   三菱

 先生方ご教授お願いします。（エクセル2003、windows2000）

 こんにちは～♪

 回答が付かないようですので～♪
 作って見ましたが。
 データ数が多そうなので
 実用には向かないかも知れませんね。

 [sheet車種合計]の
　E4セルに
  =SUMPRODUCT((INDIRECT(E$3&"!$A$5:$A$100")=$B$3)*(INDIRECT(E$3&"!$C$5:$C$100")
=$B4)*(INDEX(INDEX(INDIRECT(E$3&"!D:K"),5,ROW(B1)*2-1):INDEX(INDIRECT(E$3&"!
D:K"),100,ROW(B1)*2-1),)))

 右と下は、7行目までコピーです。

 ずりずりと、下までコピーの式も作れると思いますが
 これ以上式が長くなると、もっと重くなりますので。

 >通勤  中   トヨタ  の行以下は、この式を参考にしてください。

 これは、マクロが良いと思いますけれど。。。

 。。。。Ms.Rinでした～♪♪

　*******

 追記で～す。
 式を入れるセルが違っていたようですので
 E4セルでした。

 一応、縦横にコピーできそうなものを考えてみました。

 E4=IF($A4="","",SUMPRODUCT((INDIRECT(D$3&"!$A$5:$A$12")=$A$3)*(INDIRECT(D$3&"!$C$5:$C$12")=$A4)*
(OFFSET(INDIRECT(D$3&"!$"&CHAR(MATCH($C4,INDIRECT(D$3&"!$D$3:$K$3"),0)+67+(B4="中"))&"5"),0,0,8,1))))

 で下と右側にコピーです。
 あっていますかね？
 (SATO)

 Ms.Rinさま、SATOさま
 お返事ありがとうございました。

 >Ms.Rinさま
 無事に合計が表示されました。ありがとうございます。
 ひとつ質問なのですが、[sheet車種合計]のB4:B7を結合してしまうと、
 E5:E7が表示されなくなるのですが、なにか対処方等ございますか？

 >SATOさま
 返事ありがとうございました。
 しかしながら何回か確認したのですが、私の方では表示されませんでした・・・
 （しゅうき）

 Ms.Rinさんではないですが～♪

 セルの結合は、「見た目が美しくなる」という利点がありますが
 「処理が難しくなる」という欠点があります。
 エクセルに計算させて何かをやらせようと思ったら、セルの結合はさけるのが「吉」です。

 リストはその一行でデータが成り立つように作るのが良いでしょう。
 たとえば、B4:B7を結合すると、B5:B7には「何も入力されてない」事になります。
 ４行目は「通勤  新   トヨタ」ですから、トヨタ・新車の通勤用のデータ　と分かりますが 
 ５行目の場合「　　  新   ホンダ」となり、ホンダ・新車の内何用のデータが必要なのか
 分からなくなってしまいます。

 同じ文字が重複して並んでいるのが気になるようなら、２つ目以降の連続した同じ文字は
 条件付き書式等を利用して、文字色を白にして目に見えなくする。で対応してはどうでしょう。

 (HANA)

 失礼しました。
 前の式は表示する表が一列ずれていたみたいです。

 =IF($B4="","",SUMPRODUCT((INDIRECT(E$3&"!$A$5:$A$12")=$B$3)*(INDIRECT(E$3&"!$C$5:$C$12")=$B4)*
(OFFSET(INDIRECT(E$3&"!$"&CHAR(MATCH($D4,INDIRECT(E$3&"!$D$3:$K$3"),0)+67+(C4="中"))&"5"),0,0,8,1))))

 とりあえず訂正しておきます。
 なお、車種合計用シートにはトヨタ、ホンダ、日産、三菱、とあるのに
 福岡の表には日産、三菱がない、という場合はその項目はエラーとなります。

 またセルの結合をされた場合は、
 =IF($B4="","",SUMPRODUCT((INDIRECT(E$3&"!$A$5:$A$12")=$B$3)*(INDIRECT(E$3&"!$C$5:$C$12")=$B4)*
    ~~~~                                                                                  ~~~~
 ここを$B$4と変更してE4:E7までコピー、E9:E12は$B$9としてコピー・・・
 のような作業が必要ですから、HANAさんがおっしゃるとおり、
 できるだけ結合は避けたほうが無難ですね。
 (sato)

コメント返信：

[ 一覧(最新更新順) ]

[[20061222125003]] 『2つの条件を満たした値の合計を参照』（しゅうき） ページの最後に飛ぶ

[[20061222125003]]　『2つの条件を満たした値の合計を参照』（しゅうき）　ページの最後に飛ぶ