[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『休暇簿の作成』（spybee）
非常に面倒くさい質問なのですが、社員が年間で40日有給休暇があるとして、半日の休暇を提出すれば入力を「0.5」と入力し、残日数が「39.5」と表示するようにします。
勿論一日の休暇ならば表示は「39」。ここで行き詰まりの悩みが時間休をとった場合、例えば３時間の休暇ならば、残が39日で3時間の休暇取得ならば「38」と「5」という２つの残数を表示させ、残が39.5日で3時間の休暇取得ならば、残数表示を「38.5」と「5」という表示にさせたいのですが…出来ますでしょうか？
毎月の月報を作成するのに用いたいと思っています。よろしくお願いいたします。補足として残日数が39.5日と3時間の場合、その月の累計休暇が2日と5時間取得したとすれば「36.5日」と「6時間」という表示方法にしたいのですが… スイマセンややこしくて…
こんにちは。 A B Ｃ Ｄ Ｅ Ｆ Ｇ Ｈ 1 年休 時間休 取得年休 取得時間休 (仮年) 残年休 残時間休 2 39.5 5 4 20 -2 33.5 1
8時間労働制で、残りの時間をということですよね。 表のレイアウトが使いにくいでしょうが、 E2に =IF((B2-D2)<0,CEILING((B2-D2)/8,-1),0) G２に =A2-C2+E2 H2に =IF(E2="",B2-D2,(-E2*8)+B2-D2) でいかがでしょうか？ (kobi)
間違いました。上記式で、H2に =IF(E2=0,B2-D2,(-E2*8)+B2-D2) かな？ それにしても、年間40日の有給休暇は羨ましい・・・(kobi)
Kobi様… 何か素晴らしすぎて開いた口が塞がらないんですけど… 感激を通り越して自分の未熟さに愕然とするばかりです… 横着ついでに表はこんな感じで作成したいのですが。ご教授を。
A B C D E F Ｈ 1 使用年休 使用時間休 累計年休 累計時間休 残年休 残時間休 与年休 2 3 5 5 5 34 3 40
という表で作成したいのですが。
毎月の入力セルはA2とB2のみ。H1は年一度の入力です。（この表のA2･B2を書き換え書き換えで作成したいのです）
C2は前月の取得休＋A2です。但し、D2のセルは例え累計でも8時間以上の数字は1日と換算し、C2に加えたいのです。
つまりD2が20時間の累計になりますと、16を2と返してC2に2を加え、余りの4をそのままD2に表示させたいのです。
C･D･E･Fのそれぞれの数式を教えていただけませんか？スイマセン。頭から湯気が出ています…お願いします。
それにしても、年間40日もいらないから買い取って欲しい…（ってゆーか休ませて欲しいっス）…(spybee)
すみませんがお望みの表の形式では、自分にはわかりません。 どこかで累計を計算させて、お望みの形式に参照させるという手はありますが… せっかく表計算の経過を計算させるわけですから、それを見えるようにして以下の表形式では？ A B C D E F G O P Q R 1 4月 5月 6月･････2月 3月 累計 (仮年) 2 氏名 spybee 残年休 30 5 3 8 32 3 付与年休 40 残時間休 2 5 5 10 2
D2に =R2-R3 D3に =IF(Q3=0,"",IF(Q3<8,8-Q3,MOD(Q3,8))) Q2に =SUM(E2:P2) Q3に =SUM(E3:P3) R2に =B3-Q2 R3に =IF(Q3<>0,CEILING(Q3/8,1),0) 他の社員の管理表を４行目以下に作るとして、計算式のコピーは D2とD3セルを選択後、セル右下の＋を掴んで必要行までフィル 同様に、Q列とR列もコピーしてください。 (spykobie)なんちゃって
spykobie(kobi様…?)様
そうか…そうですよね。
こちらの形式にどうしても拘らなければならない。
となると、spykobie様のおっしゃる「どこかで累計を計算させて、お望みの形式に参照させるという手がある」というのが、今回ご教授の上記書式を別シートに入力させ、この希望書式に反映させてやればイイという解決策なわけですよね。
なるほど… そうすれば月報の書式変更も起案しなくて済む訳だ。
これで何とかなりそうです。本当にありがとうございました。
しかし…本題とは別なんですけど、何でこのkobi様のボード画面の編集は上手に改行が出来るんでしょうか？不思議っス!!（spybee）
spybeeさん、こんばんわ。 自分もこの学校の低学年生みたいなもので、勉強させてもらってる身分です。 校長先生が心広い方で(怒られないし)、居心地良いので学校に登校させてもらってます。 お互いに学びあったり教え合ったりの間柄ですから、 ･･･様は勘弁してくらさい。 えっと、改行の問題は最近も話題になってますが、行頭に半角スペースです。 それから、月報の書式の問題ですが、 A B C D E F Ｈ 1 使用年休 使用時間休 累計年休 累計時間休 残年休 残時間休 与年休 2 3 5 5 5 34 3 40 という書式のままで毎月の入力セルがA2とB2ということだと、入力した数値を どうやって累計させていくのかの問題が残ります。 これを解決する方法を(自分は無理なので)質問し直したら、きっと達人の皆さんの 回答が得られると思います。(自分も見てみたいので是非！) 低学年生のくせに酒気帯び登校 (kobi)
毎月の入力はSheet2(累計計算する)の方で入力することにして、 Sheet1(お望みの表の)のA2とA3セルに最新のデータを表示させるだけですが、 Sheet1のA2セルに =INDEX(Sheet2!E2:P2,,COUNT(Sheet2!E2:P2)) Sheet1のB2セルに =INDEX(Sheet2!E3:P3,,COUNT(Sheet2!E3:P3)) で、一応お望みの表形式で表示は出来ます。入力はSheet2で行います。 あれ？聞かれてもいないのに一人でやってる？(kobi)
kobiさん（お言葉に甘えまして）こんばんわ。 早速のご教授ありがとうございます。 改行の不思議も判明して、また一つ感動しちゃいました!!（早速嬉しげに使用している自分がチョット可愛い…） 本題の件ですが、私は取り敢えずkobiさんの方法で作成し、活用させて頂こうと思っています。 私もエクセルの奥深さと、活用次第で無限に広がる可能性を秘めた魅力に取り憑かれ、 一歩ずつ山を登ろうと志しているチャレンジャーの一人なので、 少しずつ低い山を登りながら、クリアしていくという喜びを噛みしめようと… そう思っています。 余裕が出てきて、また一つ高い山に登る自信がついたら必ず登校すると思います!! その時はまたご教授願いますっ!! この度は勉強になりました。ありがとうございました。（spybee）
kobiさ～ん!!（及び達人の皆様…）help Me～!!です!! B3のセルに付与年休40日という項目を設けて頂いたのですが、ここが日数だけでなく 時間休の付与もありまして… 今までの関数と過去ログ見ながらチャレンジしてみたんですが… ギ・ギブアップ…!!です… 取り敢えずB4あたりのセルに追加で またまた、ご教授を… あと「Sheet1のB2セルに =INDEX(Sheet2!E3:P3,,COUNT(Sheet2!E3:P3))」 という式に「0=""」を追加したいんですが… これも解らん… 情けなさ過ぎ…（低い山も登れないspybee）
spybeeさん、こんばんはです。解決したと思いブックを消してしまいました。 条件が増えたんですが、条件を詳しく教えていただけませんか？ ・付与年休40日の内に時間休が何日分という決めがある？(時間休として使える上限がある？） ・年休のほかに時間休が別枠で付与される？ ・年休と時間休の使用方法に制限は？ ＞という式に「0=""」を追加したいんですが… あ、こういう場合考えてなかったです。入力シートには必ず[ ０ ]を入力して、B2セルに =IF(INDEX(Sheet2!E3:P3,,COUNT(Sheet2!E3:P3))=0,"",INDEX(Sheet2!E3:P3,,COUNT(Sheet2!E3:P3))) 同じ様な数式の繰り返しがスマートじゃないですが、一応実現・・・ (kobi)
kobiさんごめんなさい。お手間をお掛けしています。 ご記入して頂いた様な条件等はなく、付与年休の蘭にもう一つ「付与時間休」という 蘭を追加したい。という要望です。 蛇足ですが、通常の年休繰越月は1月ですから、今居る社員さんの付与日数は1月に端数 (時間休も含む）を切り捨てて更新するため、当初ご指示頂いた計算式で問題ないのですが、 この時期のように、年度末の転勤等で他店から来られた社員さんにつきましては、 １～３月までの間に時間休を取られた場合、時間休を含んだ残休を4月に持ち込まれてくるわけです。 ですから、必ずしも付与年休が日数割で与えられるわけじゃなく、時間の付与年休も 盛り込む説明を忘れてしまい、今回のご質問となりました。 在職の社員は更新月が1月。その他の異動社員は何月かは不明ですが、 社員名もそれまでのデータも消去した入れ替わりとしますから難しい関数は必要ないだろう…と。 こうゆうわけです。 付与年休の項目に「日数」と「時間休」の残。「29日」ともう一つ「6時間」という 蘭を加えたいのですが。勿論、時間休残がなければ「付与時間休」は空白です。(spybee)
あ"、寝でしまった。本日帰るのが遅くなるので暫らく返事できません。すびばせん。 花粉症(kobi)
お助けスレ頼んでおけばいがった。 条件が複雑になり、自分で整理できているかも不明です。 会社の昼休憩に一所懸命考えましたが、検証が不十分のため間違ってたらすびばしぇん。 変更するセルは、R3が =IF(B4<>0,IF(Q3=0,B4,IF(B4>Q3,0,CEILING((Q3-B4)/8,1))),IF(Q3<>0,CEILING(Q3/8,1),0)) D3が、 =IF(AND(Q3=0,B4=""),"",IF(B4<>0,IF(Q3<=B4,B4-Q3,MOD(Q3,8)+B4),IF(Q3<8,8-Q3,MOD(Q3,8)))) Sheet1のA2セルもついでに変更して、 =IF(COUNT(Sheet2!E2:P2)=0,0,INDEX(Sheet2!E2:P2,,COUNT(Sheet2!E2:P2))) 同じくB2セルに =IF(COUNT(Sheet2!E3:P3)=0,0,INDEX(Sheet2!E3:P3,,COUNT(Sheet2!E3:P3))) でどうでしょう? 数式がもっと整理できるのかもしれませんが･･･。 残時間休が8時間を超えると1日休日増とはなるのでしょうか。その場合は考えていなく、残時間が8時間を超える場合もあります。 違っていたら多分花粉症のため？と思いたい(kobi)
kobi様 休憩時間にまで悩んでいただいて…本当に申し訳ありません。 上記方法で試してみたのですが、今度はB3に35。B4に3と打ち込むと、B3が32に なっちゃいました。 もう、申し訳ないので諦めようかと思ったのですが、（こちらの勝手もあり） 余りにも失礼すぎるので、再度、簡潔に整理をしてもう一度アップします。 徹夜も７日続きなので、自分の事ながら今日は寝床に入ります。すいません。(spybee)
遅レス飛び込みですが、お邪魔させて下さい。 spybeeさん こんにちわ。寝ないと体力・気力を奪われるため、私は一に睡眠です。 kobiさん花粉症たいへんですね。お薬（お酒）でも直りませんか？ spybeeさん達が、月に数度有休を取得しても、月の合計を出さずに済む（代わりに毎日の入力）方法を考えてみました。 入力用のシート（シート名『年休_入力』と表示用のシート（シート名『年休_表示』）を用意します。 『年休_入力』シートのレイアウト 所属 氏名 取得日 取得月 取得日数 取得時間 10 AA 2004/04/02 4 0.5 2 11 BB 2004/05/08 5 0.5 3 10 AA 2004/08/23 8 1 11 BB 2004/12/03 12 0.5 3 10 AA 2004/11/25 11 0.5 1 11 BB 2004/12/06 12 0.5 3 このシートで取得者の入力を行います。Ｄ２セルに =MONTH(C2) 以下コピー とすれば、オートフィルターで指定月の抽出が出来ます。 あるいは、 ピボットテーブルを使って、月別・個人別の集計も可能です。 『年休_表示』シートのレイアウト 所属 氏名 与年休 与時間休 取得年休計 取得時間休計 残年休 残時間休 10 AA 40 2 3 37.5 1 11 BB 38 5 1.5 9 36 0 A,B,C,D列（所属、氏名、与年休、与時間休）は年初に手入力。(一人１行） E2 =SUMPRODUCT((年休_入力!$A$2:$A$20&年休_入力!$B$2:$B$20=$A2&$B2)*(年休_入力!$E$2:$E$20)) F2 =SUMPRODUCT((年休_入力!$A$2:$A$20&年休_入力!$B$2:$B$20=$A2&$B2)*(年休_入力!$F$2:$F$20)) G2 =IF(D2-F2<0,C2-CEILING((F2-D2)/8,0.5)-E2,C2-E2) H2 =IF(D2-F2<0,D2+CEILING((F2-D2)/8,0.5)*8-F2,D2-F2) 入力は20行までの式にしていますので、E2,F2の20の部分は1000でも2000でも適当な範囲で結構です。 但し、配列関数ですので同じ数字（同じ行数）にしないとエラー値が返ります。 所属&氏名(年休_入力!$A$2:$A$20&年休_入力!$B$2:$B$20 や $A2&$B2 部分）の所は、 社員№にすれば、個人を特定しやすいかと思います。 以上、kobiさんとspybeeの遣り取りを踏襲して、まとめたつもりですが、いかがでしょうか？ (sin)
sinさん、いつも拝見しながら感服しております。 どなたも入ってこなくて、実は一杯々々だったので助かりました。 入り口も切り口も違う視点からの数式を、後でじっくり味あわさせていただきます。 花粉症の方は薬の飲みすぎのせいか(？)だいぶ治まってます、うひうひ。 spybeeさん、ゆっくり休んでからで良いんですが、 ＞今度はB3に35。B4に3と打ち込むと、B3が32になっちゃいました。 こちらはそうなりませんが、数式がB3に入ってませんか？でも有り得ない話しだし。 見直してみます(kobi)
こんばんは。横から失礼します。 kobiさんの表で、 残年休 D2に =ROUNDDOWN((MIN(320,SUM(B3*8,B4))-(Q2*8)-Q3)/8,0) 残時間休 D3に =MOD(MIN(320,SUM(B3*8,B4))-(Q2*8)-Q3,8) とすれば、計算が合うんじゃないかなと思います。（Yosh!）
R3の数式を =IF(B4<>0,IF(Q3=0,0,IF(B4>Q3,0,CEILING((Q3-B4)/8,1))),IF(Q3<>0,CEILING(Q3/8,1),0)) に修正。というよりは、始めから考え直してsinさん、Yosh!さん数式のほうが考えやすいですね。 次々と条件を追加しているうちにどこに間違いがあるか解らなくなってるし･･･。 皆さんにお世話になり解決した？という事で乾杯･･･飲むのかよっ！(kobi)
やっと這いずる思いで、只今帰宅しPCの電源入れました… 知らぬ間にsinさん・yosh!さんなどの師範クラスの方々にまで腰を上げていただき、 感激を通り越し、申し訳ないです。 自分にも整理させるため、以下の要望で再度確認していただきたいのですが。
A B C D E F G O P Q R 1 4月 5月 6月･････2月 3月 累計 (仮年) 2 氏 名 spybee 残年休 21 5 3 9 ?? 3 付与年休 30 残時間休 2 5 5 2 ? 4 付与時間休 4
kobiさんの作成したファイルでまとめます。 条件① B2,B3,B4セルは年一度の入力。 条件② 一日の就業時間は8時間。D3セルに限らず、全ての時間に8時間を超えた数字 は入らない（8時間は1日と返す） 条件③ E～P列は毎月の実績値を入力。 条件④ Q2はE2～P2の累計値で構わないが、Q3は条件②のように8時間を越えた数字は 必ず1日と見なし、上記例の様に10時間累計ならば8を1日と見なしQ2に返し、端数の 2を表示。 条件⑤ E～Pについて、時間休の実績は必ず年休から引いていく。（例えば上記例で 6月の年休取得実績が0で、時間休取得実績が2ならばD2が21でD3は0。これが年休実績が 0で時間休実績が4ならば、D2が20でD3が6となる。 条件⑥ 当然、B(付与数)－D(残数)＝Q(累計)となるし、D(残数)＋Q(累計)＝B(付与数) となる。 以上が再度整理した私の要望です。Rのセルについては特に要望はありません。 kobiさんの一杯一杯のお気持ち、 痛すぎるほど伝わってましたんで、僕自身も 人にばっか聞いてないで、自分でやってみようと試行錯誤したのですが、 やればやるほどグチャグチャになってしまいました。 今日は取り敢えず肉体が悲鳴上げてるんで休みますが、明日早速sinさんの方法など 試してみてお礼のスレ載せます。復習は後から何時間かけてもしてみようと思います。 取り敢えず今まで根気強く付き合ってくださったkobiさんへの恩返しも込めて 僕の今回の仕事はkobi流で成し遂げさせてください。(sinさんすいません…) 恐らく条件④のQ3の数式さえ反映出来れば全て完成すると思います。 本当に本当に皆様に感謝いたします…（spybee）
yosh!さん数式を今朝確認し、また間違いに気づき蒼白ｯ！！(非二日酔) D3を =IF(AND(Q3=0,B4=""),"",IF(B4<>0,IF(Q3<=B4,B4-Q3,8+B4-Q3),IF(Q3<8,8-Q3,MOD(Q3,8)))) というよりは、D2、D3をyosh!さん数式で解決します！ 解決の方向が違うだろうと感じつつ、間違いのまま提示しspybeeさんにはすまないことをしました。 皆さんお世話になりました。自分も勉強になりました。 本日、一人娘の入学式に行ってきま～っす(kobi)
kobiさん、愛娘さんの御入学おめでとうございます^^
残年休 D2に =ROUNDDOWN((MIN(320,SUM(B3*8,B4))-(Q2*8)-Q3)/8,0) 残時間休 D3に =MOD(MIN(320,SUM(B3*8,B4))-(Q2*8)-Q3,8) 累計の計算を 年休 Q2に =ROUNDDOWN(((SUM(E2:P2)*8)+SUM(E3:P3))/8,0) 時間休 Q3に =MOD(((SUM(E2:P2)*8)+SUM(E3:P3)),8)
これで条件(4)も満たしていると思うのですが、どうでしょうか。（Yosh!）
さすが Yosh!さん。ここでMIN関数ですか。私も考えてみましたが、ゴミ箱に捨てました。 spybeeさん、ご自身の実になる事が一番ですので、気になさらないで下さい。（でも、落ち着いたら見てね） 私も毎日試行錯誤の連続です。自分が納得しないと意味無いですものね！頑張って下さい。 kobiさん 嬉しそうですね。ご入学おめでとうございます。今日も(？)いい薬が飲めそうですね！ それでは、(sin)
あ、Q2・Q3の問題もありました。読解能力がなくて申し訳ないっす。 それではお言葉に甘えまして、お薬の時間にしたいと思います。 (kobi)
yosh!さん。ありがとうございます。条件④は完璧に反映できましたが、 冒頭での質問の「半日の休暇を提出すれば入力を「0.5」と入力し、 残日数が「39.5」と表示するようにします。」というのができませんでした。
A B C D E F G O P Q
1 4月 5月 6月･････2月 3月 累計
2 氏 名 spybee 残年休 39 0 0.5 0
3 付与年休 40 残時間休 4 0 0 4
4 付与時間休 0
となります。ここはD2が39.5で、Q2は0.5と表示させたいです。また、
A B C D E F G O P Q
1 4月 5月 6月･････2月 3月 累計
2 氏 名 spybee 残年休 30 0 0.5 0
3 付与年休 30 残時間休 2 0 0 4
4 付与時間休 6
B2に30。B3に6と打ち込むと、上記例の表になります。 どうも、数式の中で「0.5」は時間休とみなしてるみたいです。 ここはD2が29.5。D3は6のまま。累計もQ2が0.5。Q3は6のままで表示したいのですが… F2が0でF3が4ならば、この表示でバッチリなんですが… すいません。教えてください。（spybee）
検証不足ですが、今のところ問題ないのでアップしておきます。Q2に =SUM(E2:P2)+SUM(E3:P3)/8 Q3に =MOD(SUM(E3:P3),8) R2に =B3-Q2、R3に =IF(B4<>0,IF(Q3=0,0,IF(B4>Q3,0,CEILING((Q3-B4)/8,1))),IF(Q3<>0,CEILING(Q3/8,1),0)) D2に =R2-R3、 D3に =IF(AND(Q3=0,B4=""),"",IF(B4<>0,IF(Q3<=B4,B4-Q3,8+B4-Q3),IF(Q3<8,8-Q3,MOD(Q3,8)))) まだ懲りていなかった (kobi)
最初の条件に全く気付いていませんでした。ごめんなさい。 上記のkobiさんの式で、Q2を =SUM(E2:P2)+ROUNDDOWN(SUM(E3:P3)/8,0) にすれば 後は大丈夫だと思います。（Yosh!）
kobiの娘です。父は夜勤のため代わりに打ってます。 朝のkobiの式は全行削除でお願いします。 失礼しました～～～ｍ(_ _)ｍ by kobiの娘
恥の上塗りで再登場、今度は実現してるのか? 条件が増えたためＢ４セルに付与時間を入れていましたが、 社員○名分の管理をしやすい様に、１社員２行の表にしてみました。 A Ｂ Ｃ Ｄ Ｅ Ｆ Ｇ Ｈ Ｉ …… Ｐ Ｑ Ｒ 1 4月 5月 6月 7月 …… 2月 3月 累計 2 氏名 付与年休 付与時間休 残年休 残時間休 5 5 0.5 11.5 3 spybee 40 7 28.5 5 2 6 2 2 として、Ｄ３に =B3-R2 、 Ｅ３に =IF(AND(R3=0,C3=""),"",IF(C3<>0,IF(R3<=C3,C3-R3,8+C3-R3),IF(R3=0,"",IF(R3<8,8-R3,MOD(R3,8))))) Ｒ２に =IF(C3=0,SUM(F2:Q2)+CEILING(SUM(F3:Q3)/8,1),IF(SUM(F3:Q3)<=C3,SUM(F2:Q2),SUM(F2:Q2)+CEILING((SUM(F3:Q3)-C3)/8,1))) Ｒ３に =MOD(SUM(F3:Q3),8) で、出来た表を人数分下にフィル。条件が読めずに迷惑掛けました。 (kobi)
kobiさま・yosh!さま・sinさま
長い時間、他人のお仕事を手伝って頂き、私の持ち合わせている感謝の言葉だけじゃ 言い表せません。 本当にありがとうございました。 特にkobiさんには最後までお付き合い頂き、娘さままでお出で頂いて… お子様にもよろしくお伝え下さい。 私も3人の子持ちで、今回のやり取りをしていましたら、何だか急に娘の顔が見たくて 急遽、帰郷して現在お返事を書いているところです。 それにしてもこれで全ての（14ファイルあったのですが）プログラム作りが終了しました。 私のところは本社で32名。支社合わすと計78名分。これの各資料作りを今まで電卓と 手書きでやってたんですから… 本来業務が疎かになるはずだわ… 私もここまで大変な（特にこの一件は）プログラム作りとは思わなかったものですから 部下に「俺がやるよ」なんてカッコイイ事言っちゃいまして。 何はともあれ本当に助かりました。後はsinさんのプログラムがヒントでチョイと 閃いたものがありまして。今からその作成が楽しみです!! こうやってどんどん閃き、アイデアを拡張して覚えていくモンなんですね!! また一つ勉強になりました。また行き詰ったらお知恵を拝借させて下さい。(単身赴任のspybee）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.