[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『随時シート上の値の合計を出しておき値をクリアー後はゼロから』（ちぃさん）
先日は [[20130502201155]] で皆様に事細かく教えて頂きものすごくたすかりました。
ありがとうございました。
再び質問に参りました。
まだまだエクセル自体が未熟な私です。
お手柔らかにお願いいたします。
実際の選択範囲は
セルB1:B3000
セルC1:C3000
となりますがサンプルのシートで
BooK1のSheet1
セルB1:B5に"名前"を入力しています。
セルC1:C5に"値"をを入力しています。
セルD1に合計を求めたい"名前"を入力しています。
セルE1にセルB1:B5とセルC1:C5の間でセルD1に"名前"のある"値"の合計を表示しています。
セルC1:C5の"合計値（セルE1）"が10000になった時にセルE1の"合計数"を消去し、またゼロからカウントし10000になればまた"合計数"を消去とつづきます。
※セルE1の合計数は10000で消去しますがセルC1:C5の値は消去しません。
普通にSUMIF関数で合計数が10000になれば手動で消去しても問題ないのですが、毎回式を入力するのも面倒ですし、エクセルの凄さをもっと実感したいという気持ちからなにかいい方法がありましたら教えてください。
宜しくお願いいたします。
Excel2010ですが2003でも対応できるほうがいいです。
Windows 7 ですが Windows XPでも対応できるほうがいいです。
やりたいことが見えてこないんだけど、 D1 : =IF(SUMIF(B1:B5,D1,C1:C5)>=10000,"",SUMIF(B1:B5,D1,C1:C5)) といったことではなく？
(ぶらっと)
前回の質問を覗くとマクロのようなので、少しだけ考えて見ましたが要件が見えてこないです。 簡単なテストデータでレイアウトしたものが『このように動く』と言うような 使用前と後があれば理解しやすいと思いますが。 識者の方なら、このレベルでばっちりかもしれませんが。。。 （Cod）
=MOD(SUMIF(B1:B5,D1,C1:C5),10000) って事かな？
(HANA)
返事が遅くなり申し訳ないです。
・・・・・言葉でやりたい事を説明するのって難しいですね。
私の言葉足らずなところがあります。
ごめんなさい。
＞ D1 : =IF(SUMIF(B1:B5,D1,C1:C5)>=10000,"",SUMIF(B1:B5,D1,C1:C5))
＞=MOD(SUMIF(B1:B5,D1,C1:C5),10000)
試してみました。
いろいろと考えていただきありがとうございます。
イメージ的には HANAサン の式でてる値が希望です。
「D1に指定させたセルC1:C5の"合計値（セルE1）"が10000になった時にセルE1の"合計数"を消去」
の所をもっと詳しく説明いたします。
①セルB1:B5の"名前"
・刃物を使う時の名前があります。
②セルC1:C5に"値"
・刃物を使う時と使わない時、一日分の生産量全てを含む値です。
③セルD1に合計を求めたい"名前"
・刃物を使う時の名前を入力してます。
④セルE1にセルB1:B5とセルC1:C5の間でセルD1に"名前"のある"値"の合計
・刃物をどのぐらい使っているかがわかるように刃物の使用量の合計をだします。
⑤10000
・10000を刃物の交換の時期に設定しています。交換時期がきたことを知らせたいです。
⑥またゼロからカウントし
・使用していた刃物を交換すれば刃物はまた新しい刃物なので新たに刃物の使用量を表示したいです。
この①～⑥の説明でやりたいことがわかるでしょうか？
まだわかりづらいようでしたら指摘ください。
先ほど、
イメージ的には HANAサン の式でてる値が希望です。
と書きましたが⑤の所にかいている
"刃物の交換の時期"
がきた時に刃物を交換しますので交換するまではカウントがゼロにはしたくないです。
刃物の交換時期がきたことがわかり、刃物を交換すれば再びゼロからのカウントがしたいです。
本当にわかりにくくて申し訳ないです。
なにかいい手立てがありましたら宜しくお願いします。
元の表に、何日の使用なのか日付を入力する列を設ける。 F列にでも、刃物を交換した日付を入力する列を作る。
＞2003でも対応できるほうがいいです。 と言う事ですので SUMPRODUCT関数をつかって、 F列の日付以降で、D列の名前が一致する人のC列の合計を出す で、どうでしょう？
たとえば、A列に日付を入力するなら =SUMPRODUCT(($A$1:$A$10>=F1)*($B$1:$B$10=D1),$C$1:$C$10) こんな感じです。
元データはどんどん追加されると思いますので、範囲を多めに取っておいてください。 ただし、あまり広い範囲にすると計算に時間がかかったりするかもしれませんので そのあたりは様子を見ながら設定してもらえると良いと思います。
この式は、その日(F列に入力した日)の作業前に刃物を交換したとして計算します。 その日の終わりに刃物を交換する場合は >=F1 → >F1 に変更して下さい。
SUMPRODUCT関数についてはこちらをご参考に。 http://www.excel.studio-kazu.jp/lib/e3h/e3h.html ライブラリ「(e3h) SUMIFとSUMPRODUCT」
＞・10000を刃物の交換の時期に設定しています。交換時期がきたことを知らせたいです。 は、条件付き書式を設定してセルに色を付けるのはどうでしょう？ http://www.excel.studio-kazu.jp/lib/e2qw/e2qw.html ライブラリ「(e2qw) 条件付き書式」
データを入力して行くと、合計の表が画面から外れるかもしれませんので データを入力した行の方に何か表示が出る方がわかりやすいかもしれませんね。 簡易的な式ですが =IF(VLOOKUP(B1,$D$1:$E$5,2,FALSE)>=1000,"※","") こんな感じで、合計の表から参照して判定表示しても良いかもしれません。
(HANA)
＞ =SUMPRODUCT(($A$1:$A$10>=F1)*($B$1:$B$10=D1),$C$1:$C$10)
＞条件付き書式を設定してセルに色を付けるのはどうでしょう？
＞=IF(VLOOKUP(B1,$D$1:$E$5,2,FALSE)>=1000,"※","")
さっそく試させていただいてます。
もしかした無理かなと思ってましたが、私が思ってた回答です。
勉強になりました。
また解らないことがあれば相談させてください。
ありがとうございました。
良く考えたら、入力した方のすべての行で ＞=IF(VLOOKUP(B1,$D$1:$E$5,2,FALSE)>=1000,"※","") なんて計算をするのは非効率的に思えます。
また、刃物の使用可能個数も 物によって違ってくるかもしれません。
合計の表の方に =IF(合計セル>=1000,"※","") の式を入力しておいて 入力表の方へは =VLOOKUP(B1,$D$1:$E$5,3,FALSE) なんてので参照させた方が スムーズかもしれません。
条件付き書式の設定も ※印の有無 を条件に出来る様に成りますし。
(HANA)
＞合計の表の方に =IF(合計セル>=1000,"※","") の式を入力しておいて
＞入力表の方へは =VLOOKUP(B1,$D$1:$E$5,3,FALSE) なんてので参照させた方が
＞スムーズかもしれません。
参考にします。ありがとうございます。
すみません。
追加いいですか？
ご教示いただいた
＞元の表に、何日の使用なのか日付を入力する列を設ける。
＞F列にでも、刃物を交換した日付を入力する列を作る。
＞ =SUMPRODUCT(($A$1:$A$10>=F1)*($B$1:$B$10=D1),$C$1:$C$10)
でいろいろと試していましてもし訂正できればと思い追加質問にまいりました。
仮に
上限を1000として1日に何回も使い1日に上限に達したとき刃物交換を1日とするようになるのですがその場合は1日の使用分がさのまま加算されるのですが
コレは何か言い手立てはあるでしょうか？
何度もすみません。
もし解決策があれば宜しくお願いいたします。
日付部分の入力を、時間も含めて入力してもらえると 特に悩まなくても良いと思います。(「5/1 9:00」「5/1 11:00」の様に入力）
入力表の方は、何度も入力が必要なので毎回日付部分を入力するのはストレスかもしれません。 簡易案として、日付と時間を入力する列を分けて(この場合でも、日付はすべてのセルに入力が必要） 両方を足し算した列を作り、その列でマッチング作業をして行くと良いかもしれません。
或いは、両方を足し算する列を作らず 式内で直接足し算しても良いかもしれません。 今は (A1:A10>=F1) ですが、(A1:A10+B1:B10>=F1) の様に。
後は、運用方法が曖昧なので何とも言い難いですが 入力表への入力が、実績ではなく予測の場合 12:30の500を加工した時に10000を超えるのなら その次の14:15以降に「※」がつくのが良いのでしょうね？
その都度入力 or 確認 していくならそんなに問題にならないかもしれませんが。
(HANA)
できました。
問題ない出来上がりです。
諦めていたものがここまで完璧に出来上がると本当に嬉しいです。
すみません。
もう1点いいですか？
＞条件付き書式を設定してセルに色を付けるのはどうでしょう？
の所で私が意味を取り違っている事に気づきました。
コレって条件付き書式を今の時間も指定している状態なら時間入力しているデーターの列に設定？って事ですか？
＞その次の14:15以降に「※」がつくのが良いのでしょうね？
時間入力しているセルだと
9:00
11:00
12:30
14:15
※
※
※
このようになるということでしょうか？
私の設定のしかたが間違っていると思うのですがうまくできません。
何度も申し訳ないのですがもう一度教えてください。
宜しくお願いいたします。
条件付き書式に関しては、合計表の方の色を変更すると思ってました。 もちろん、入力表の方の色を変更するのでも良いのですが。。。
上でも書きましたが、運用方法がわからないので そのあたりはこちらで想定したものです。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ 一日の終りに今日の生産数を入力する。 すると、前回刃物を交換してから何個生産したか個数がわかる。 セルに色がつくので 明日の生産を始める前には、刃物の交換が必要だとわかる。 ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
セルの色を変えるのは、どちらかよく見る方が変われば良いと思います。
例えば集計表は 1:10のあたりにあって 入力表は、1行目から始めてもどんどん下に追加して行けば、そのうち 1:10の集計表が 画面から外れてしまうかもしれません。
入力した段階でわかりたいなら 入力表の方に色がつくようにしておけば良いと思います。 ただ、現在の設定だと 過去も未来もすべての同じ名前のセルには色が付きますので 複数の刃物が同時に変更が必要だった場合 分かりにくくなるかもしれません。
下の方が表示されていても、一旦1:10行目も表示させて 必ず確認する。 或いは、1:10は常に見える様に設定してある と言うのであれば、集計表の方に色がつくので良いと思います。
時間迄入力して・・・と言う話は、追加で出てきた話ですので ＞＞その次の14:15以降に「※」がつくのが良いのでしょうね？ なんてのは、つぶやきの段階です。 実際 どうなるのが良いのか 私にはわかりません。
＞このようになるということでしょうか？ ではなく「そうなった方が良いのかしら？」と言う問いかけです。
今は、名前でしかマッチングしてませんので データを入力して規定数を超えた場合 これまで入力したすべてのデータも、これから入力するデータも「※」マークが表示されてしまいます。 もともと、一日に複数件入力があると思っていませんでしたので。
入力データが予定数として、刃物交換時期を知る為だけのデータにしては、手がかかりすぎている様に感じます。 実績を入力するのなら「今更『あの時交換だったんだよ』って言われてもねぇ」と言った感じがします。
実際に近いデータ（日付・時間・名前・個数 が一揃いで色々テスト出来る数とパターンがそろったもの）を出して こういう理由（考え）で、こうなると便利 等と書いてもらえると良いのですが。
(HANA)
使用方法に疑問が残る一つとして 「集計表っていらないんじゃない？」 ってのがある事に気づきましたが、どうなんでしょう？
判断をするためだけの集計表でしょうか？ もしかしたら、入力表の方で直接集計＆刃物交換時期の入力等 済ませてしまった方が簡単かもしれません。
「集計表は、集計表で要るんだ」 って事情かもしれませんが。。。
あ、あと ご要望を詳しくお伺いすると やっぱり集計表を作った方が良いね って事になるかもしれませんが。
ちょっと当初のご質問からは、結果が変わってきている様ですので 仕切り直しで 最初からご説明をいただくと すっきりするかもしれません。
(HANA)
重ね重ね言葉足らずですみません。
現時点では私が当初に求めていた回答をいただいていますので十分です。
条件付き書式を使い刃物使用の合計の方には色がつくので交換時期がくればわかりますので問題はないです。
＞なんてのは、つぶやきの段階です。
この事に関しては私の勘違いでした。
勘違いの内容を説明いたしますと
条件付き書式を使えば刃物を使用した際に入力するセルCに上限を越している場合は"※"が表示できると思い込んでました。
仮に上限：500として上限を超えてもまだ交換をしてない状態なら
1日 09:00 100
1日 11:00 100
1日 12:30 500
1日 14:15 100
1日 16:00 200
1日 ※
1日 ※
1日 ※
まだ未入力のセルCは"※"が表示されるものと勘違いしてました。
こんな事ができれば仮に使用量の合計を見落としても入力するときには必ず気づきますのでかなり便利と勝手に思ってました。
紛らわしい質問をすみませんでした。
今回もかなり勉強になりました。
また解らない事があれば質問にきたいのでその時は宜しくお願いいたします。
長々とありがとうございました。
＞仮に上限：500として上限を超えてもまだ交換をしてない状態なら ＞まだ未入力のセルCは"※"が表示される 一応確認ですが．．．． 入力表は、いろいろな名前（機械名？）のデータを一緒に入力していくのですよね？ すると、未入力セルに「※」が出ると 他の名前のデータの入力も阻害されそうですが。。。
それでも良ければ、先に数式を埋め込んでおけば良いんじゃないでしょうか？
(HANA)
仕事の都合上で返事が遅くなりました。
＞一応確認ですが．．．．
＞入力表は、いろいろな名前（機械名？）のデータを一緒に入力していくのですよね？
入力表は製品名や生産内容ですよ。
製品によっては設備についている刃物でカットするものもありまして。
また私の勘違いの話しに戻りますが
たしかに「※」が出ると他の名前のデータの入力も阻害されますが条件付き書式を使えば刃物交換時期がくれば「※」が表示され刃物交換をしカウンターがゼロにもどれば「※」は消えると勝手に思ってました。
予めシートには1日から31日分の範囲で簡単にですがシートに24時間を15分刻みでセルを使い時間と生産稼動率などを管理してます。
日付 A10:A3000ぐらいまで
時間 B10:B3000ぐらいまで
作業内容 C10:C3000ぐらいまで
ｶｯﾄの有無 D10:D3000ぐらいまで
製品名 E10:E3000ぐらいまで
基材 E10:E3000ぐらいまで
とまだまだ項目はありますがエクセルシートはこのような感じにならんでます。
刃物の交換を管理できるカウンターも設備にはついているのですが皆さんがあまり見ないので毎回必ずみるPCの生産状態の所の刃物の使用状態が目につけば不良品を作る頻度もかなり減るのかな・・・と思い今回慣れないエクセルにチャレンジしました。
でも HANAサン にご教示いただいた式は完璧ですよ。
満足してます。
本当にたくさん気にかけていただきありがとうございます。
あ、イメージがわかったかもしれません。
条件付き書式では、書式の変更しかできません。 セルの色を変更したり、入力されている文字の色を変更したり です。 データを入力するのと同じセルに「※」を表示させたいなら 事前に未入力のセルには「※」を入力しておいて、 入力時それを消しながら（上から入力すると消えてしまうので）作業を進めて行く事になります。
簡単な例だと [A] [B] [C] [D] [E] [F] [G] [ 1] 名前 個数 名前 計 判定 [ 2] くま 200 ※ くま 600 ※ [ 3] くま 200 ※ りす 100 [ 4] りす 100 [ 5] くま 200 ※ [ 6] くま ※ ※ [ 7] りす ※ [ 8] くま ※ ※ [ 9] くま ※ ※ [10] りす ※ [11] B列の未入力セルには「※」を入力しておきます。 C列に、判定結果をVLOOKUP関数で参照します。 C2=VLOOKUP(A2,$E$1:$G$3,3,FALSE) B列の条件付き書式で 数式が =AND(B2="※",C2<>"※") 文字色を背景色と同じ色 を設定します。 C列は見えなくて良いので、列の非表示や文字色を背景色と同じ色に設定 等します。
すると、 [A] [B] [C] [D] [E] [F] [G] [ 1] 名前 個数 名前 計 判定 [ 2] くま 200 くま 600 ※ [ 3] くま 200 りす 100 [ 4] りす 100 [ 5] くま 200 [ 6] くま ※ [ 7] りす [ 8] くま ※ [ 9] くま ※ [10] りす [11] こんな風に表示される様になります。
入力されている所に上から書いていくのに抵抗があるなら 値を入力する列の前側にもう一つ列を設けて 隣の列にはみ出して表示させても良いかもしれません。
どの程度スペースを付けると良いかは 調整してもらうとして。。。 [A] [B] [C] [D] [E] [F] [G] [ 1] 名前 個数 名前 計 判定 [ 2] くま ※ 200 くま 600 ※ [ 3] くま ※ 200 りす 100 [ 4] りす 100 [ 5] くま ※ 200 [ 6] くま ※ [ 7] りす [ 8] くま ※ [ 9] くま ※ [10] りす [11] B列に B2 =" "&VLOOKUP(A2,$E$1:$G$3,3,FALSE) と言った式を入れて B列の列幅を細くします。
入力がある5行目までは、入力されている値が表示されますが 6行目以降は、B列に表示されている文字が C列にはみ出して表示されますので C列に入力がある様に見えると思います。
(HANA)
沢山の提案を事細かく説明いただいてほんとうに助かります。
私がおもっていた以上の内容です。
さっそく使わせて頂きます。
私の質問内容パーフェクト以上の回答を頂きまして感謝いたします。
・・・やはり言葉でやりたい事を伝えるのは難しいですね。
ここまで素晴しい回答を頂けるのに私の言葉足らずな所がなければ HANAサン に貴重な時間を必要以上に使わせないですんだのにと申し訳ない気持ちでいっぱいです。
最初にも書きましたが私はエクセルがまだまだ未熟ですのでコチラに度々お世話になると思います。
またつまずいてしまった時はお願いしたいです。
あ、次回はもっと内容がわかりやすいように説明したいですｗ
長々とお付き合いいただき本当にありがとうございました。
なかなか雰囲気が分からず お手間をとらせました。
検証に十分なサンプルデータを載せておいて貰えると 回答も付きやすく成ると思いますし 目的の結果も得られやすく成ると思います。
重要なのは、今回の様に「あきらめずに説明する」って事だと思いますが。 こちらこそ、感謝します。
エクセルは、色々な事が出来るので 「こんな風に成ってたら ちょっと便利なんだけどなぁ」ってのを 沢山見つけてもらえると良いと思います。
あ、そうそう。 次回投稿時は、ご自身の書き込みの分かりやすい位置に 署名もつけて投稿して貰えるとありがたいです。 （この掲示板は、勝手に名前がつく機能が無いので。。。）
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.