- 締切済み
Excelの表で集計範囲が変わってしまう場合の範囲指定の仕方
- みんなの回答 (4)
- 専門家の回答
みんなの回答
同じ列に時間と小計・合計が混じっているのが問題だと思います。次のようにしたらどうでしょうか。 A B C 1 演目 時間 合計 2 はじまり 12分 3 ぞうさん 25分 4 寸劇 30分 5 ピアノ 40分 6 小計 1時間47分 7 手品 35分 8 切り絵 25分 9 小計 1時間 10 合計 2時間47分 合計の式を「=sum(b2:b10)」のように、実際にデータがある範囲より下まで設定しておくと、行を挿入しても大丈夫です。
- tekuinai
- ベストアンサー率0% (0/2)
深く考えていない回答をしてしまいました。 途中に小計が入っている状態なんですね。 また文字列として『分』や『時間』を使用されているのですね。 この場合詳しい情報がないので自分なりに考えて関数のみでやってみますと。こういうのはどうでしょうか。 A列は演目 B列は小計、合計を含めた時間 C列にはA列が小計、合計のときは空白 その他は分単位へ変換 D列は小計や合計用の計算 とし、 まず小計のセルB6に以下の式を入力 =SUBSTITUTE(SUBSTITUTE(ROUNDDOWN(D6/60,0)&"時間"&MOD(D6,60)&"分","0時間",""),"0分","") B6をコピーしB9とB10に貼り付ける。 C2に以下の式を入力 =IF(OR(B2="",A2="小計",A2="合計"),"",VALUE(IF(ISERROR(FIND("分",ASC(B2))),SUBSTITUTE(ASC(B2),"時間","")*60,IF(ISERROR(FIND("時間",ASC(B2))),SUBSTITUTE(ASC(B2),"分",""),MID(ASC(B2),1,FIND("時間",ASC(B2))-1)*60+SUBSTITUTE(MID(ASC(B2),FIND("時間",ASC(B2))+2,2),"分",""))))) C2をコピーしC10まで貼り付ける D2に以下の式を入力 =IF(A2="小計",SUM($C$1:C1)-SUMPRODUCT(($A$1:A1="小計")*1,$D$1:D1),IF(A2="合計",SUMPRODUCT(($A$1:A1="小計")*1,$D$1:D1),"")) 同じくD2をコピーしD10まで貼り付ける。 説明はちょっと省きますが、このような式ではどうでしょうか。 C列D列の表示、非表示はお任せします。 文章能力が低いので分かりにくかったらすみません
お礼
お礼が遅くなりましてすみません。 質問初心者のため、質問の仕方もいま一つなのに、 丁寧にご回答いただきありがとうございました。
- Wendy02
- ベストアンサー率57% (3570/6232)
こんにちは Excelで、どのように入力されて、そのように表示されてものか分かりませんが、一例を示しておきます。マクロという方法もないわけではありませんが、時間の入力値の入れ方が分からないので、とても、この質問だけで回答することは出来ません。あくまでも、時間は、00:00:00 のように入れるものとして、話を進めます。 図を見ていただければ分かりますが、非常に複雑です。 まず、この表は、Excel2003 で出来ていますが、Excel2007 でも同様です。 表に数式を入れたら、後は、リストに変換してあげます。 (リスト:データ-->リスト-->リストの作成 [レ]先頭行をリストの見出しとして使用する [OK] をクリック ----------------------------------- [リスト]は、単に、行の挿入・削除をしたときに、数式を自動的に入れるものであって、特に、自分で、数式をコピーするなら、そのままでも構いません。[リスト]は、レイアウト的に、あまり自由度が高くありません。 ----------------------------------- C列は入力列です。 B2: =SUBSTITUTE(SUBSTITUTE(TEXT(IF(OR(A2="合計",A2="小計"),$D2,$C2),"h時間m分"),"0時間",""),"間0分","間") D2: =IF(A3="合計",SUM($C$1:C3),IF(A3="小計",SUM($C$1:C3)-SUM($D$1:D2),"")) 最後に、入力列と補助列を非表示にしてあげます。 これ以上、複雑な内容になりますと、マクロのほうが、良いかもしれません。ただし、質問の中では、全角と半角の数字が混在しているなど、何のコメントも入っておりませんから、詳しい内容を教えていただけないと提示は出来ません。
お礼
お礼が遅くなり、申し訳ありませんでした。 質問初心者のため、質問の仕方もいま一つなのに、 丁寧にお答えいただいてありがとうございました。
- tekuinai
- ベストアンサー率0% (0/2)
現状がいまいち分からないのですが、sum関数としては 『=sum(B2:B8)』のような式でしょうか?もしそうであれば 『=SUM($B$2:$B$8)』で解決できるのではないでしょうか。 添付資料にEXCELはつけれるのか分からないけど何かしら手がかりを つけていただければ解答できると思います。 また$の意味は絶対参照。どんだけずれても絶対に参照しますという意味です。 こんな回答でよかったでしょうか。 操作としてはその参照セル位置の入力文字にカーソルを置いてF4を押せば 簡単に$がつきますよ。
お礼
お礼が遅くなり大変申し訳ありませんでした。 参考にさせていただきました。
関連するQ&A
- Excel2000 集計のコピーの仕方
お世話になります。とても初歩的な質問ですが、宜しくお願いします。 エクセルで、データリストを入力したシートで、データ→集計とし、 「リストの下に集計行を挿入する」「現在の集計表と置き換える」に常に チェックを入れて集計しています。 すると、リストの下に小計が挿入され、左端にある1,2,3というところを クリックすることで、明細行が表示・非表示切り替えられますが、 小計行のみ表示させた状態(明細行は非表示で、画面には太字で「○○ 計 □」 みたいな状態の表が出来上がっている) で、この出来上がった表を別のシートにコピーして使いたいのですが、貼り付けると、必ず非表示になってる明細行まで貼り付けされてしまいます。 一行ずつ、小計行をコピーして貼り付けるか、一旦全部貼り付けて明細行を 削除していくかのどちらかしかないのでしょうか? いい方法をご存知でしたら教えて下さい。宜しくお願いします。 質問がわかりにくかったら申し訳ありません。
- ベストアンサー
- オフィス系ソフト
- ExcelのSUM関数の集計範囲を可変にしたい
ExcelのSUM関数の集計範囲を可変にしたい 1.状況、問題点 商品毎の売上、利益を管理するリスト表が あり、その表の行に対する追加、更新処理を VBAマクロで行っています。 使用環境はExcel2010 リスト表は、帳票としての見易さを考慮して、 同じカテゴリに属する複数の行に対して小計行 を設けカテゴリ別の売上、利益を集計しています カテゴリに属する行範囲に対してSUM関数を使っ ているということです (例1)この表にマクロで行を追加させる際、 あるカテゴリの小計欄で参照している集計 範囲の行の途中に行を挿入する場合は、行 を追加してもSUM関数の集計範囲は自動的に 拡大されます。 A B C 1 項目 売上 2 田中 100 3 小計 100 4 山田 200 ⇒ 挿入 5 佐藤 300 6 小計 600 式 = SUM(C4:C5) 3行目と4行面の間に行を挿入するとき SUM関数の集計範囲は自動的に拡大され る SUM(C2:C4) ⇒ SUM(C2:C5) (例2)挿入する行が集計行の直ぐ上の場合 SUM関数の集計対象範囲がずれてしまう A B C 1 項目 売上 2 田中 100 3 小計 100 4 山田 200 5 佐藤 300 ⇒ 挿入 6 小計 600 ※ ※ 式 SUM(C4:C5) は 自動的にはSUMC(C4:C6)とはならない 2.実現したいこと 小計の直前にに行を挿入した場合も自動的に 集計範囲が変わるようにしたいのです。 実装方法 案1 (1)インストラクタのネタ帳 http://www.relief.jp/itnote/archives/003417.php に、「表全体の最後の行」※に集計行がある場合として 「合計範囲を自動的に変更する」方法の紹介あり。 この方法はCOUNTA()で入力済みのセルをカウント。 私のケースでは ・売上、利益などのセルが空白となる場合がある ・C列は 売上 データを記載する列で なので半角スペースや全角スペース は入らない ・#REFやDIV/!0などは入りうる C4:C5 のセル数は DIV/0!なども含めデータが入っているセル数 +ブランクが入っているセル数 は COUNTA(C4:C5) + COUNTBlank(C4:C5) なので セル C5 =SUM(C2:INDEX(C4:C5, COUNTA(C4:C5)+COUNTBlank(C4:C5)) とするか? ※「インストラクタのネタ帳」の方法は、「表全体の行数」を もとめる場合に最後の行までを指定できて有効ですが、上の ように小計をもとめる場合は、どうしても小計をもとめる範囲 式に書く必要があり、そこが集計範囲が変わってしまうと役に 立たないです。 代案としては、6行目の小計の行番号から3行目の小計の行番号 を引いて行数を求める方法でしょうか この方法の場合、小計の式に 6行目行番号-3行目行番号 のように具体的に記述してはNGなので "下"の小計行から"上" の小計行を引く というような間接的な指定の仕方が必要と 考えています。案1の検討はここまでです。 実装方法 案2 上のように表自体の集計関数を変える以外に、 マクロで対応する方法 行を追加する際に、 a)行の追加がある場合、行の挿入位置が小計 の集計範囲の”途中”なら 何もしない、 b)追加する行が集計行のすぐ上になる場合 つまり変更前の挿入位置+1 が小計行の行番号と等しい時は、追加する行数分 SUM関数の集計範囲を広げる 案1が難しそうなので案2で実装しようと思っています Q1 案1と案2の検討結果についてお気づきの点があれあば教えて ください Q2 可能であれば、案2の実装方法についてヒント程度でもいいので 教えてください 以上
- ベストアンサー
- Excel(エクセル)
- エクセルの条件付きデータ集計について
はじめまして。 1か月間、機器の消費電力を10分ごとに計測したデータを 日毎・1時間単位で小計計算した表を作成したいのですが、 関数の組み合わせで作業を簡略化できればと、悩んでおります。 データのイメージは以下の通りです。 ○データ 日付 時刻 データ 7月1日 0:00 0.3 7月1日 0:10 0.5 7月1日 0:20 0.4 0:30 0.2 0:40 0.3 0:50 0.2 1:00 0.3 ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ 7月31日 23:50 0.3 ○集計イメージ 日付 時刻 小計 7月1日 0:00 1.9 7月1日 1:00 2.3 ・ ・ ・ ・ ・ ・ ・ ・ ・ 7月31日 23:00 3.0 なお、現在のやり方は (1)0時台であれば、0:00の行の右端に60分分のデータの合計を算出 (2)5行飛ばしにSUM関数で計算 (3)INDEX関数とROW関数を組合せてで5行飛ばしの小計データを抽出 という非常にアナログなやり方をしております。。。 SUMIFS等を試してみましたが、いまいちうまくいきませんでした。。。 良い手があれば、教えてください。 よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- EXCELで途中の集計行を除いて集計したい。
EXCEL2002で合計を出したいのですが、途中に「合計」とか「中計」などの集計行があり、最初から最後までSUM関数を使うとその分だけ重複してしまいます。途中の計算式が入っているセルを除いて集計するには、どうしたらよいでしょうか?A列に項目(例えば、事務用品、コピー用紙、小計、固定電話料、携帯電話料、通信費計・・・)、B列に数値または計算式を入力しています。
- ベストアンサー
- オフィス系ソフト
- ファイルメーカーの範囲集計
よろしくお願いします。 FMproVer5を使用しているFM初心者です。 質問内容ですが、例えば10明細のデータがあるとします。1明細毎に行番号をもっており、これをポータルで6明細ずつ表示したいと思っています。 この明細を範囲指定し、その範囲のみの集計は可能でしょうか?? 一応 if(1<=dno and dno=<6,sum(金額),"error")というふうにしてみました。このままですと、結局は10明細分の金額全て合計してしまいます。 うまく範囲だけ集計する方法はありますか? あとポータルには6明細毎に表示しているのですが、7明細目から表示できなくなっています。7明細以降をうまく表示して、7~10明細までの集計なんて、ことも出来るのでしょうか? また、プレビューの画面ですが、パートの小計、ソートを使っているところで例えばここで繰り返しで6行にして、この6行分の金額合計なんかは出来ますか? 色々試したのですが、やはりうまく出ず、会社にはマニュアルとかもなくて もうどーにもならなくて・・・FMで無理なら別でやろうと思ってます。 どうかよろしくお願いします。
- 締切済み
- その他(データベース)
- エクセルの集計について教えてください。
レジに打ちこまれた売上データをもとに、1ケ月の売上一覧表を作成しています。 一目で売れ線がわかるように売上数の良い順位をつけています。 例) 順位 商品名 数量 単価 売上 1位 楓 100 10 1,000 2位 紅葉 30 5 150 30 10 300 計 60 450 3位 A 40 10 400 B 40 10 400 5位 銀杏 30 10 300 総合計 270 2,550 上記の元データは商品毎の売上数が自動集計されない日時明細なので、先ず、商品名毎に並び変え、売れた数量を集計させます。ここまではエクセルで行っていますが、その後、数量合計を1行に記し、不要行を削除するのを手作業で行っています。その後で数量合計の多い順に並び変えをし(旧価格・新価格があるものもあるので、売上順位としては数量は合算、しかし新価格が何ケで旧価格が何ケと表示)、仕上げに順位を加えるのも手作業で行っています。 月によってデータが膨大なことがあり、手作業での限界がきています。 どなたか良い方法をお教え頂けないでしょうか。
- ベストアンサー
- その他(ビジネス・キャリア)
- Excelで日時データの集合を色々に集計したい
2005/4/18 17:49(シリアル値38461) 式の日時だけの沢山のデータがあります。 来客の入店時刻の様なものです。 Excelに貼り付ければA列一列だけになり、行数はとりあえず8000行位になります。 これを色々な角度から集計したいです。 例えば月別で個数を集計すれば、季節的な来店者の増減を数値化できます。 時刻別の平均個数を出せば、時間帯による分布が見れます。 曜日と時刻を組み合わせて曜日別の来店ピーク時刻の変化とかも出せます。 色々ありますが、数千のデータを目で読んで目的別に個数を数えると時間的に膨大です。 Excelで自動的に集計出来ないでしょうか。 ピポッドテーブルというのがこんな時に使う機能なんでしょうか?
- ベストアンサー
- オフィス系ソフト
- エクセルでの集計
お世話になります。 小伝票(7行×10列くらい)を毎日70~80枚くらい集計し、大伝票に合計がたっまていくように入力していきたいです。 小伝票は1枚=約20とします。 例えば、これを買い物伝票だとし、縦行にA店B店C店、横列にみかん、りんご、バナナ。1枚を20個の単位とし、買い物をした人が表中の該当の場所に名前を記入するという内容です。名前はいつも同じ場所に記入されるとは限りませんし、1人とも限りません。A店でりんごを2人が10個づつ買った時は、2人の名前が入ります。 これを、今度は、名前別に誰がどこで合計何個買い物をしたのかを表せるように、別に大伝票を設けています。 この大伝票は縦行に買い物をした人の名前、横列に買った店名を表しています。別のシートなどに小伝票の内容を入力していき、大伝票に合計がたまっていくようにしたいのですが、それは可能ですか? 今は手計算をしているので、かなり時間がかかってしまいます。 大伝票に入力した後、1週間ごと、1ヶ月ごとの集計は出来上がっているのですが、小伝票から大伝票に数字を入力するのが難しいのです。 まったくの素人ですので、マクロとかもわかりません。 用語も良くわからないので、ほんとうに、噛み砕いた説明を宜しくお願いいたします。 私の説明だけでは、良くお分かりにならないようでしたらほそくさせていただきます。
- ベストアンサー
- その他([技術者向] コンピューター)
- エクセルを使って勤怠の集計をしています。
エクセルを使って勤怠の集計をしています。 出社時間と退社時間を入力すれば、労働時間が出るような、関数式を教えて下さい。 開始時間 終了時間 総時間数 休憩時間 労働時間 通常時間 割増時間 8:40 17:15 8:35 1:10 7:25 7:25 0:00 12:50 17:15 4:25 0:10 4:15 4:15 0:00 8:40 19:15 11:05 1:40 9:25 8:00 1:25 8:40 21:10 12:30 1:55 10:35 8:00 2:35 8:40~10:00、10:00から10分休憩、10:10~12:00、12:00から50分休憩 12:50~14:50、14:50から10分休憩、15:00~17:15、17:15から30分休憩 17:45~19:45、19:45から15分休憩、20:00~22:00、22:00~10分休憩 22:10から深夜残業となります。 割増時間は8時間以上の労働時間から付きます。 (10時、12:50からなど、途中から勤務が始まる人もいますので。) 現在手計算をしていますが、間違いが多くて困っています。 どうぞ宜しくお願いします。
- 締切済み
- 財務・会計・経理
- Excel VBA 選択範囲の空白に文字を入れる
VBA初心者です。よろしくおねがいします。毎月の集計で困っています。 例えば添付の写真のように 1)A列に地域、B列に地域詳細、C列に社員番号があります 2)A列には下段に地域総計(A18)、B列には地域詳細小計(B10,B17),そしてC10,17,18は空白になっています。 3) これは名古屋地区だけを抜き出したコピーですが実際は500行位あり他県の詳細も同様の形で集計が出ています。 4)ここで私が解決したいのはC行の空白(添付ではC10,17,18)にTotalという文字を抜き出して入れるVBAを教えていただきたいです。最終的にはA,B行はレポートのフォーマット上不要で削除になります。 宜しくお願いします。
- ベストアンサー
- Excel(エクセル)
お礼
お礼が遅くなってすみません。 参考にさせていただきました。