画像が元のデータで、日付がA列に金額と件数が

質問

画像が元のデータで、日付がA列に金額と件数が
D列に二段にわたり入力されています。

画像の元のデータ表から別シートの転記表に値を
それぞれ計算して、転記していきたいのですが、
B列の番号が普通の番号と「★」番号に分か
れているため日付ごと及び番号の種類ごとに
SUMIF関数等で条件ごとに集計したいのです。

例えば、1月1日なら、通常番号（1と2）の金額を合計
して、25,000円にして転記表のB2に、件数はC2に
代入したいです。

さらに★1と★2の金額を合計して、40,000円を
B3に、件数をC3に代入したいです。

転記表のスタイルはA列に日付（1/1から1/31）
が入っています。B列に料金、C列に件数、いずれも二段書きです。
上段に通常番号、下段に★番号合計です。

元のデータと転記表の日付の合わせ方が分からないのと
番号の種類ごとに合計するSUMIF関数を教えてほしいです。

ちなみに日付は1月1日から1月31日まで入力されています。

お願いします.

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.3

mu2011 · ベストアンサー

現在のデータ構成表からはご要望を達成する事は無理とは云いませんが非常に難しい数式が必要です。
表の工夫次第で簡単になりますのでご一考下さい。

一例です。
(1)１行に番号に関わる情報を全て設定する（Ｃ列、Ｄ列を２行から２列）
(2)日付と番号情報と合成（=A1&B1）して別列に一意データを設定する（仮にyyyymmddsnn：西暦とsは0を普通、1を★付き番号、nnは一連番号）
以下のSUMIF関数（エクセル2003以下では単一条件しかできない、エクセル2007以上は複数条件が可能）でも計数できるようになると思いますが如何でしょうか。
普通番号は=SUMIF(検索列,text(対象日付セル,"yyyymmdd"&"0*",金額又は件数の列）
★付き番号は=SUMIF(検索列,text(対象日付セル,"yyyymmdd"&"1*",金額又は件数の列）

補足

一意データの設定はよくわからないのですが、教えてください。

回答No.2

KURUMITO

元のデータ表がシート１に有るとして、またデータは２行から下方に有るとして、Ｅ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(D2="","",IF(A2<>"",A2+D2*10^-8,IF(AND(A2="",ISERROR(FIND("★",B2)),ISERROR(FIND("★",E1))),IF(B2<>"",E1+D2*10^-8,E1+D2*10^-10),IF(AND(A2="",ISNUMBER(FIND("★",B2)),ISERROR(FIND("★",E1))),"★"&(INT(E1)+D2*10^-8),IF(AND(A2="",ISNUMBER(FIND("★",E1))),IF(B2<>"","★"&(MID(E1,2,16)*1+D2*10^-8),"★"&(MID(E1,2,16)*1+D2*10^-10)))))))

また、Ｆ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(LEFT(E2,6)=LEFT(E3,6),"",IF(AND(NOT(ISNUMBER(E3)),ISNUMBER(E2)),INT(E2),IF(AND(NOT(ISNUMBER(E2)),ISNUMBER(E3)),"★"&INT(MID(E2,2,6)*1),"")))

そこでお求めの表ですが例えばシート２のＢ１セルには金額、Ｃ１セルには件数と入力します。
Ａ２セルから下方に１月１日から１行おきに連続して日付を入力するとしてＡ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(MOD(ROW(A1),2)=1,DATE(2010,1,ROUNDUP(ROW(A1)/2,0)),"")

Ｂ２セルには次の式を入力してＣ２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($A2<>"",IF(COUNTIF(Sheet1!$F:$F,$A2)=0,"",IF(COLUMN(A1)=1,MID(INDEX(Sheet1!$E:$E,MATCH($A2,Sheet1!$F:$F,0)),7,8)*1,MID(INDEX(Sheet1!$E:$E,MATCH($A2,Sheet1!$F:$F,0)),15,2)*1)),IF(COUNTIF(Sheet1!$F:$F,"★"&$A1)=0,"",IF(AND($A2="",$A1<>""),IF(COLUMN(A1)=1,MID(INDEX(Sheet1!$E:$E,MATCH("★"&$A1,Sheet1!$F:$F,0)),8,8)*1,MID(INDEX(Sheet1!$E:$E,MATCH("★"&$A1,Sheet1!$F:$F,0)),16,2)*1))))

回答No.1

MackyNo1

ご希望のレイアウトにまとめるには以下のような手順で数式を作成するのが最も簡単な対応だと思われます。

まず、数式を簡略化するために、以下の操作で元データのA列にすべて日付を入力します（必要ならこのデータを条件付き書式を利用して見掛け上非表示にすることもできます）。

A列のデータ範囲を選択してCtrl+Gでジャンプダイアログを出して、「セル選択」から「空白セル」にチェックを入れて「OK」し、空白セルを選択した状態で、数式バーに「=A2」のようにアクティブセルの１つ上のセルを選択し、Ctrlキーを押しながらEnterします。

上記の表のリストにしてあれば、転記表シートの各セルに以下のような数式を入力するとそれぞれの集計を行うことができます。

B2セル
=SUMPRODUCT((Sheet1!$A$2:$A$300=MAX($A$2:A2))*ISNUMBER(Sheet1!$B$2:$B$300)*(Sheet1!$C$2:$C$300="料金")*Sheet1!$D$2:$D$300)

B3セル
=SUMPRODUCT((Sheet1!$A$2:$A$300=MAX($A$2:A3))*NOT(ISNUMBER(Sheet1!$B$2:$B$300))*(Sheet1!$C$2:$C$300="料金")*Sheet1!$D$2:$D$300)

C2セル
=SUMPRODUCT((Sheet1!$A$2:$A$300=MAX($A$2:A2))*NOT(ISNUMBER(Sheet1!$B$2:$B$300))*(Sheet1!$C$2:$C$300="料金")*Sheet1!$D$2:$D$300)

C3セル
=SUMPRODUCT((Sheet1!$A$2:$A$300=MAX($A$2:A3))*NOT(ISNUMBER(Sheet1!$B$2:$B$300))*(Sheet1!$C$2:$C$300="料金"))

以上4つのセルを選択し、下方向に日付分だけオートフィルします。