[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『項目の配置が違うデータの抽出』(和代)
いつもご親切に教えて頂き感謝しております。有難うございます。
今回少し長く説明文になりますが、少しでもこちらの思いが伝わるようにと
何回も修正しながらまとめあげました。よろしくお願い致します。
1日から31日までのアルバイトのシフトを作っています。
シート名:”月シフト”の名前と別シート名"1"から"31"迄の名前が合致した場合に”月シート”の名前の横列の出勤時間と退出時間欄に"1”から"31”までの合計時間を入れて頂きたいのです。
問題は、項目の配置が違いますので抽出の方法を教えて下さい。
参考イメージです:
シート名“1”では("2”から"31”迄配置は同じです)
項目名R6に名前 S6に 出勤時間 T6に退出時間
R7 田中 S7 9(関数) T7 15(関数)
R8 林 S8 10(〃 ) T8 16( 〃)
以降 約50人分続きます。
シート名“月シフト”では
項目名C列に名前、 D1に1日 D2に2日 以降横列にAH列(31日)
C3とC4結合。D3に出勤時間 D列4に退出時間
C列 D列
3行目 出勤時間
田中
4行目 退出時間
以降 約50人分続きます。
D3に出勤時間(シート名:1から31迄、田中の出勤時間の合計時間が入るようにしたいのです)
D4に退出時間(シート名:1から31迄、田中の退出時間の合計時間が入るようにしたいのです)
「シート名:”1”のR列にはアルバイトの登録
されている全員の名前、S列は出勤時間、T列は退出時間
が一行毎に並んでいます。」
ST列は下記の関数が入っています。S列の関数
(=IFERROR(VLOOKUP(R14,F$7:H$51,2,FALSE),"")――
T列の関数
=IFERROR(VLOOKUP(R12,F$7:H$51,3,FALSE),"")
下記記載は上の関数と関連します。
同じシートで別の列に、1日分のアルバイトの出勤した人の
データが(F列名前、G列出勤時間、H退出時間)並んでいます。
F列の名前とR列の名前が合致すればF列の右GHの時間がR列の右、ST列に表示されます。
?@参考「そして表示されたRST列を別シート名”1”(←1日を
意味します)にコピーします。」
その繰り返しを続けて1日から31日(30日もありますが一応)迄
計31枚のシートを使い行います。
参考?A次にシフト名:“月シフト”があります。
ここは、1日から31日迄のシートが貼り付けされた後に、1か月分のまとめをする表です。
配置は、シート名”集計”のR列と同じアルバイトの登録されている全員の名前があるのですが、
「名前の表示スタートは、まずC列3行目と4行目に一人の名前を縦に2行分取っています。(セルの結合)。
横展開はD列3行目から下の行に1日目のデータ(出勤時間と退出時間)、E列3行目から下の行に2日目のデータ、―――AH列3行目から下に31日のデータを貼り付けます。」
具体的には、C3:C4を結合して田中と入れています。
D3には田中の1日目の出勤時間、D4には田中の1日目の退出時間が入ります。
E3には田中の2日目の出勤時間、E4には田中の2日目の退出時間が入ります。―――最後AH3には田中の31目の出勤時間AH4には田中の31目の退出時間が入ります。
次にC列5行目と6行目を結合して林と入れています。
以降、下の行に同じ扱いで約50人分作成します。
そこにシート名“1”から“31”のデータを貼り付けたいのですが、
上記RST列の配置とは違い、一人の名前は、2行分を結合して使い、右隣の列の出勤時間は2行分の一行目に、退出時間は出勤時間と同じ列の2行分の2行目に表示させたいのです。
最初は、相対指定で1日分をコピーし2日目から31日目までをコピー、それを約50人分ともなれば出勤時間と退出時間の並びが違うためにいちいちセル番号を変更したのですが沢山変更すると
同時に修正間違いが起こるために再度、式の確認をしなければいけませんので疲れてきました。何かいい方法がないでしょうか、教えて下さい
お願い致します。
< 使用 Excel:Excel2007、使用 OS:Windows7 >
>ST列は下記の関数が入っています。S列の関数 > (=IFERROR(VLOOKUP(R14,F$7:H$51,2,FALSE),"")―― >T列の関数 >=IFERROR(VLOOKUP(R12,F$7:H$51,3,FALSE),"") 二つの式が R14を参照したり R12を参照したりしているのはなぜですか?
そもそも、R:T列にデータを並べ替えているのはなぜですか?
月シフトに値を参照するのは、それこそ =IFERROR(VLOOKUP(R14,F$7:H$51,2,FALSE),"") の様な式で出来そうに思いますが。
別シートになるので D3 =IFERROR(VLOOKUP($C3,'1'!F$7:H$51,2,FALSE),"") D4 =IFERROR(VLOOKUP($C3,'1'!F$7:H$51,3,FALSE),"") 二つのセルを選択して必要行フィルドラッグ って感じになると思いますが。
また、シート名も INDIRECT関数と組み合わせて 1行目から引用出来る様にしておくと 便利だと思います。 (HANA) 2014/08/20(水) 15:30
「二つの式が R14を参照したり R12を参照したりしているのはなぜですか?
そもそも、R:T列にデータを並べ替えているのはなぜですか?」−−今回の質問には余分な事と 思い書かなかったのですが、R列には名前が入っています。F列(当日のシフト出勤者)の名前と R列の名前が一致したらF列の名前の右G列(出勤時間)とH列(退出時間)が入っている時間を S列とT列に表示しなさい。としています。そしてU列からAY列迄8:00から30分ごとに22:00迄列を作り退出から出勤を差し引いた時間を色を付けてどの時間帯が多い、少ないと見極めれるようにしています。その為の関数です。
突然で申し訳ありませんが、出かける用事が発生しましたので、出来ましたら夜中に続きを書きます。
すみません。
(和代) 2014/08/20(水) 15:49
「別シートになるので
D3 =IFERROR(VLOOKUP($C3,'1'!F$7:H$51,2,FALSE),"") D4 =IFERROR(VLOOKUP($C3,'1'!F$7:H$51,3,FALSE),"") 二つのセルを選択して必要行フィルドラッグ って感じになると思いますが。」−−−うまくいきました。有難う ございました。 (VLOOKUP($C3,'1'の1がシート名1になることがわかりませんでした。 その1を31迄変えてフィルドラッグしました。
「また、シート名も INDIRECT関数と組み合わせて 1行目から引用出来る様にしておくと 便利だと思います。」−−この意味が分かりませんので教えて下さいますか?
(和代) 2014/08/21(木) 00:40
できましたか、良かったです。
INDIRECT関数は調べてみましたか? たとえば、各シートの、F7セルの値を参照したい場合は シート名:1 の場合 ='1'!F7 シート名:2 の場合 ='2'!F7 こんな式になりますね?
D1セルに「1」が入っているのでこれを利用して ="'"&D1&"'!F7" とすると、1のシートのF7セルが参照出来て 横にフィルドラッグした時 1行目の値によって 各シートのF7セルの値が参照出来れば良いですが 実際は、セルに「'1'!F7」と表示されるだけです。 もちろん、横にフィルドラッグした時「'2'!F7」に変わりますが。
こんな時にINDIRECT関数の中に入れると '1'!F7セルの値を参照できるようになります。 =INDIRECT("'"&D1&"'!F7") 横にフィルドラッグすると、INDIRECT関数の中身は「'2'!F7」になるので 今度は '2'!F7セルの値を参照出来ます。
VLOOKUP関数の「'1'!F$7:H$51」部分にも同じ様に INDIRECT関数を使って シート名部分をセル参照にしておくと 列ごとに数式の変更が不要になります。
INDIRECT関数は度々再計算されますので あまり広範囲に使用すると、動きが重くなるかもしれません。
また、開いた時に何もしなくても再計算されますので 何もせずに閉じようとしても「上書きしますか?」と聞かれる様になります。
その辺も考慮して、使用・不使用を決めてもらえたらと思います。
ちなみに >”月シート”の名前の横列の出勤時間と退出時間欄に"1”から"31”までの合計時間を入れて頂きたい この「合計時間」ってのは 何かの合計をするのですか? 作ったVLOOKUP関数は、出勤時間と退勤時間を参照しているだけですが、大丈夫でしょうか? (HANA) 2014/08/21(木) 11:03
あまり広範囲に使用すると、動きが重くなるかもしれません。 また、開いた時に何もしなくても再計算されますので 何もせずに閉じようとしても「上書きしますか?」と聞かれる様になります。 その辺も考慮して、使用・不使用を決めてもらえたらと思います。」
>”月シート”の名前の横列の出勤時間と退出時間欄に"1”から"31”までの合計時間を入れて頂きたい
この「合計時間」ってのは 何かの合計をするのですか?−−−すみません、勘違いでした。 作ったVLOOKUP関数は、出勤時間と退勤時間を参照しているだけですが、大丈夫でしょうか?−−−大丈夫です。
一つ質問です。月シフトにシート1から31迄、=IFERROR(VLOOKUP($C13,'3'!$F$7:$H$51,2,FALSE),"")で参照していますが、仮にその表が8月分の表で今度は9月分に変更する場合は、当然消去すれば式がなくなりますから、式はそのままにしたい場合は、シート1から31迄をマクロでデータをすべて消去するようにすればいいのですね。
(和代) 2014/08/21(木) 16:00
そうですね。
削除する範囲はすべて同じだと思いますので 作業グループにして作業すると簡単かもしれません。 マクロでやらせるなら、単純にループさせるのが 書きやすいと思いますが。
複製・複製・複製 で使っていると忘れた頃に不具合が発生する事があります。
数式だけ入っているブックを一つ用意して そこに必要な部分を貼り付けて名前を付けて保存して使うようにすると 良いかもしれません。 (HANA) 2014/08/21(木) 17:11
作業グループにして作業すると簡単かもしれません。---すみませんが、具体的にはどのようにするのです か? マクロでやらせるなら、単純にループさせるのが 書きやすいと思いますが。--マクロの書き方がわかりません。
数式だけ入っているブックを一つ用意して
そこに必要な部分を貼り付けて名前を付けて保存して使うようにすると 良いかもしれません。---具体的に教えて頂けないでしょうか?
知識がないので申し訳ございません。
(和代) 2014/08/21(木) 17:59
>>作業グループにして作業すると簡単かもしれません。 具体的には 1日のシートから、31日のシートまでは並んでいると思いますので 1日のシートをアクティブにして、Shiftキーを押したまま31日のシートを選択します。 すると、エクセルの一番上のブック名が書いてあるところのブック名の後ろに[作業グループ]と表示されます。 また、シートタブが複数白くなります。
表示されているシートのセルの操作をすると、作業グループのすべてのシートに同じ操作がされます。
複数シート選択された状態で、今度は データが入力されているセル範囲(データを消したいセル範囲)を選び Delete を押せば良いです。
次の操作をする前に、作業グループの解除を忘れずに行ってください。
>>数式だけ入っているブックを一つ用意して〜〜 現在完成しているブックがありますね? そこから、1日〜31日シートのデータ部分を削除して「テンプレート」という名前で保存します。 月の初めに、テンプレートブックを開き 名前を付けて保存して そのブックを使う って事です。
>>そこに必要な部分を貼り付けて なんて書きましたが、名前が入力されている列などは 月が替わった時に 変わってしまうかもしれないと思い そのように書きました。
テンプレートブックに3人の名前があったとします。 8月ブックを作った時、人数が一人増えて4人になったとします。 直接8月ブックの名前を書き換えた場合、次の月になって テンプレートブックをコピーして 9月ブックを作った時 3人の名前しかないですよね?
「変更があった場合必ず、テンプレートブックも更新する」というルールがキチンと守れれば良いですが 同じ操作を二回しないといけないのは、忘れる元だと思います。
それなら、 テンプレートブックを開いて、8月ブックから名前部分をコピーして9月ブックとして保存 の方が〜〜間違いが少ないかな?〜〜と思った次第です。
別件で一つ気になったことがあるのですが 各シートのR列の名前の並びは、月シフトの名前の並びと違うのでしょうか? (HANA) 2014/08/22(金) 00:14
「同じ操作を二回しないといけないのは、忘れる元だと思います。
それならテンプレートブックを開いて、8月ブックから名前部分をコピーして9月ブックとして保存
の方が〜〜間違いが少ないかな?〜〜と思った次第です。」---今から、テスト的に実行してみます。
「各シートのR列の名前の並びは、月シフトの名前の並びと違うのでしょうか?」---一緒ですが---
(和代) 2014/08/22(金) 15:16
>>「各シートのR列の名前の並びは、月シフトの名前の並びと違うのでしょうか?」 >---一緒ですが--- そしたら、再度VLOOKUPで参照しなくても そこから一行おきに参照出来れば楽そうですね?
D3に=INDEX('1'!$S$7:$S$51,ROW(A2)/2) D4に=INDEX('1'!$T$7:$T$51,ROW(A2)/2)
31枚シートがあると「ROW(A2)/2」は31回行われるので 作業列を用意して 3行目に =ROW(A2)/2 の式を入れて下にフィルドラッグし この列を参照させるのが良いかもしれません。 (HANA) 2014/08/22(金) 15:37
「「D3に=INDEX('1'!$S$7:$S$51,ROW(A2)/2)
D4に=INDEX('1'!$T$7:$T$51,ROW(A2)/2)」月シフトの行列に入力するの ですね、−−入力しています。
「作業列を用意して 3行目に =ROW(A2)/2 の式を入れて下にフィルドラッグし
この列を参照させるのが良いかもしれません。」--3行目に =ROW(A2)/2 の式を入れてとは どこの列でしょうか?理解しようとしているのですが解りません。お手数掛けます
(和代) 2014/08/22(金) 16:25
>月シフトの行列に入力するのですね、−−入力しています。 うまく参照出来てますか? D3:D4セルを選択して 必要行フィルドラッグしてみて下さいね。
>>「作業列を用意して 3行目に =ROW(A2)/2 の式を入れて下にフィルドラッグし >どこの列でしょうか? えっと >>作業列を用意して の部分なのですが。。。 どこか空いている列とか使用しても問題ない列を使ってもらえると良いと思います。
あ、その列に入れる式は =ROW(A2)/2 でなく =INT(ROW(A2)/2) でした。すみません。 (HANA) 2014/08/22(金) 17:03
申し訳ありませんが、今夜11時ごろまでPCが見れませんので、お忙しいでしょうからそれ迄でお返事は
結構です。
よろしくお願いいたします。
(和代) 2014/08/22(金) 18:38
>ROW(A2)はD4ではないのでしょうか やってみたら A2で問題ないとわかると思います。
作業列(どこに作って良いかわからないので、A列とします)に A4に=INT(ROW(A2)/2) を入れて下にフィルドラッグ
D3に=INDEX('1'!$S$7:$S$51,A4) D4に=INDEX('1'!$T$7:$T$51,A5) を入れてD3:D4を選択して下にフィルドラッグ
を先にやってもらった方がわかりやすいかもしれません。
ROW関数についても調べてみてもらえたらと思います。 (HANA) 2014/08/22(金) 22:24
只、=INT(ROW(A2)/2)でA2/2なのにA4とA5が答え1になり、A6とA7が答え2となっています。--それがわかりにくいです。後でゆっくり確認をします。
(和代) 2014/08/23(土) 09:49
しかし=INDEX('1'!$S$7:$S$51,A4)と=INDEX('1'!$T$7:$T$51,A5)はシート"1"の出勤、退出時間が
正しく表示されましたが、理解しがたいのは、インデックスの()内のA4、A5が参照している答えが
どちらも1になっているのにシート1の出勤、退出の時間が正しく表示されています。もうひとつ、
A5は次の人の行なのに前の人の退出時間が表示されています。
=INT(ROW(A2)/2)をわざと消去しましたら、D3は#VALUE!になります。
INT(ROW(A2)/2)とインデックス()の関連性がもう一つ理解できませんので
ご指導お願い致します。
(和代) 2014/08/23(土) 11:23
>インデックスの()内のA4、A5が参照している答えが >どちらも1になっているのにシート1の出勤、退出の時間が正しく表示されています。 ひそかに、参照する列が違っているのですが。。。 =INDEX('1'!$S$7:$S$51,A4)・・・・・1日シートのS列の範囲から、A4セル番目のセルの値 =INDEX('1'!$T$7:$T$51,A5)・・・・・1日シートのT列の範囲から、A5セル番目のセルの値
>もうひとつ、A5は次の人の行なのに前の人の退出時間が表示されています。 あっっ。 誤:>A4に=INT(ROW(A2)/2) 正: A3に=INT(ROW(A2)/2) ~~ 誤:>D3に=INDEX('1'!$S$7:$S$51,A4) >D4に=INDEX('1'!$T$7:$T$51,A5) 正: D3に=INDEX('1'!$S$7:$S$51,A3) D4に=INDEX('1'!$T$7:$T$51,A4) ~~ ですね? 度々すみません。 (HANA) 2014/08/23(土) 15:21
正: D3に=INDEX('1'!$S$7:$S$51,A3)
D4に=INDEX('1'!$T$7:$T$51,A4)を変更しましても名前がずれます、ずれた行番号を逆に入れて下記のようにするとうまく行きました。
D3に=INDEX('1'!$S$7:$S$51,A5) D4に=INDEX('1'!$T$7:$T$51,A4)−−こうすればうまく行きました。
只、横E列の2日から31日迄のコピーはどのようにすればいいのでしょうか?
(コピーINDEX()内のシート1を2から31迄にする為には)
宜しくお願い致します。
(和代) 2014/08/23(土) 16:46
>−−こうすればうまく行きました。 えっと、A4とA5にはどんな値が出てますか?
また、D3とD4に表示させたい値はどのセルに入ってますか?
>只、横E列の2日から31日迄のコピーはどのようにすればいいのでしょうか? 前回 >遅くなるのはいやだと思って >今、各列のシート番号を変更して下にフィルドラッグしました。 と言うことでしたので。。。 今回も同様です。
ちなみに、各日付のシートにはマクロでデータをコピーしてますか? 同じタイミングで 今回の所へも コピー出来ると良いですね?
(HANA)
「D3とD4に表示させたい値はどのセルに入ってますか?」私:D3とD4に正しく入っています。
「今回も同様です。」---私:そうでしたね、すみません。
「ちなみに、各日付のシートにはマクロでデータをコピーしてますか?」---私:マクロでデータをコピー
しています。
(和代) 2014/08/23(土) 17:22
>「A4とA5にはどんな値が出てますか?」私:1です A3は??
>正: A3に=INT(ROW(A2)/2) とやってもらえてますか?
>「D3とD4に表示させたい値はどのセルに入ってますか?」私:D3とD4に正しく入っています。 えっと、日付シートのセルを聞きたかったのですが 上のお返事と合わせると、 S7、T7ですね。
(HANA)
「A3に=INT(ROW(A2)/2) とやってもらえてますか?」私:しました。コピーダウンも−−−
「日付シートのセルを聞きたかったのですが
上のお返事と合わせると、 S7、T7ですね。」私:はいS7とT7です。
やはり解らないのは、A列(A列3・4が数値1 A列5・6が数値2 以降33 44 55---)と
D列のINDEXの関係で何故、シートの1日の出勤時間と退出時間の数値がD列に表示されるのかが
わかりませんので教えて下さい。
(和代) 2014/08/23(土) 19:08
> 「A3に=INT(ROW(A2)/2) >とやってもらえてますか?」私:しました。コピーダウンも−−− そしたら、 [A] [ 1] [ 2] [ 3] 1 [ 4] 1 [ 5] 2 [ 6] 2 [ 7] 3 [ 8] 3 [ 9] 4 [10] 4 [11] 5 [12] 5 となりませんか?
>A3は余白です >「A4とA5にはどんな値が出てますか?」私:1です って事は [A] [ 1] [ 2] [ 3] [ 4] 1 [ 5] 1 [ 6] 2 [ 7] 2 [ 8] 3 [ 9] 3 [10] 4 [11] 4 [12] 5 となってるって事ですよね? (HANA) 2014/08/23(土) 19:13
「A3に=INT(ROW(A2)/2)にした為に [A] [ 1] [ 2] [ 3] 1 [ 4] 1 [ 5] 2 [ 6] 2 [ 7] 3 [ 8] 3 [ 9] 4 [10] 4 [11] 5 [12] 5 となりませんか?」私:なりました。 従って下記は修正されています。 「「次の「A4とA5にはどんな値が出てますか?」私:1です って事は [A] [ 1] [ 2] [ 3] [ 4] 1 [ 5] 1 [ 6] 2 [ 7] 2 [ 8] 3 [ 9] 3 [10] 4 [11] 4 [12] 5 となってるって事ですよね?」 私:「A3に=INT(ROW(A2)/2)にした事によって修正済みです。
(和代) 2014/08/23(土) 20:40
>私:「A3に=INT(ROW(A2)/2)にした事によって修正済みです。 って事は >正: D3に=INDEX('1'!$S$7:$S$51,A3) > D4に=INDEX('1'!$T$7:$T$51,A4)を変更しましても名前がずれます これはずれなくなった って事ですか? (HANA) 2014/08/23(土) 20:48
違う質問です、シート名:月シフトでC列の名前がご存じのとおりC3とC4が結合して例えば田中、C5とC6が結合して山本以下同じように名前を入力しています。
別シート名:基本事項にはアルバイトの名前をB5から(一行毎に名前が変わります)下に入力しています。基本項目を利用してシート名C列の最初の名前箇所に=シート名:基本事項の田中とします。(その時には田中が入りますが)そしてフィルドラッグをしましたら当然2行置きの名前がC列に入りますので困ります。
基本事項で名前(一行毎)を追加すれば、月シフトのC列(2行分で結合)に自動的に名前が入る方法を教えて頂けますか?
(和代) 2014/08/23(土) 22:14
>基本事項で名前(一行毎)を追加すれば、月シフトのC列(2行分で結合)に自動的に名前が入る方法を教えて頂けますか? でしたらやはり、難関の D3に=INDEX('1'!$S$7:$S$51,A3) をわかってもらうのが良いと思います。
INDEX関数はわかりますか?
シートをまたぐと少し面倒なので、同シートの式にしますが たとえば =INDEX($S$7:$S$51,1) という式だと 範囲(S7:S51)の先頭セル=S7セル を返してくれます。
そして、=INDEX($S$7:$S$51,2) という式だと 範囲(S7:S51)の二番目のセル=S8セル を返してくれます。
A3セルに「1」が入っていれば =INDEX('1'!$S$7:$S$51,A3) の式は 範囲の先頭セル 1シートのS7 を返します。
この式は、二つ下のセルにコピーされ =INDEX('1'!$S$7:$S$51,A5) になりますが A5セルに「2」が入っていれば 範囲の二番目のセル 1シートのS8 を返します。
A列の奇数行にだけ注目してみて下さい。 1,2,3,4・・・ となってますよね? =INDEX('1'!$S$7:$S$51,A3) の A3の所に 1,2,3,4・・・ と入ることになります。
どうですか? (HANA) 2014/08/23(土) 22:52
式の配置は、シート名:月シフト A3=INT(ROW(A2)/2)で答えがA3=1 A4=1 A5=2 A6=2 以下同じ関連性が続きます。
D1==INDEX('1'!$S$7:$S$51,A3) D4=INDEX('1'!$T$7:$T$51,A4)となってコピーダウンしています。
「INDEX関数はわかりますか?」−−−色々試して理解出来ました。理解出来ました時には、A3に=INT(ROW(A2)/2)の式とIndex関数の組み合わせが素晴らしいと思いました。アイデアですね、私も覚えておきます。
>基本事項で名前(一行毎)を追加すれば、月シフトのC列(2行分で結合)に自動的に名前が入る方法を教えて頂けますか?−−−今、忙しのでもう少ししてからチャレンジします。
有難うございます。
(和代) 2014/08/24(日) 13:30
忘れないうちに理解するために何回か使って自分のものにしていきます。
本当に嫌がらずに最後まで教えて頂き感謝です。
(和代) 2014/08/24(日) 19:32
出来ましたか。良かったです。
・○行おきに参照したい。 ・△列おきに参照したい。 ・行列を入れ替えて参照したい。 なんて時は使いやすいと思いますので、使ってみて下さい。
さて、 >「ちなみに、各日付のシートにはマクロでデータをコピーしてますか?」 >---私:マクロでデータをコピーしています。 という事ですが、どんなマクロになっていますか?
それに組み込めると良いと思うのですが。 (HANA) 2014/08/25(月) 00:05
「どんなマクロになっていますか?」---下記マクロです。
Sub prcCellCopy給与集計1日()
Dim sht As Variant Dim v As Long sht = InputBox("任意のシート名を入力して下さい") v = MsgBox(sht & "でいいですか?", vbOKCancel) If v = vbOKCancel Then Worksheets("給与集計").Range("a1:aw51").Copy Worksheets(sht).Range("A1").PasteSpecial End If Range("Q4").Select
End Sub
お陰様で、完成になりました、シフト表を担当者に使って頂こうと思っています。
その為に再度いろいろ確認をしています。偶然にも先ほどからInput Boxを実行していた中で
担当者には使いにくいかなあ?と思われる部分が下記のようにありますので教えて下さい・
Input Boxで"任意のシート名を入力して下さい"の入力待ちになっているところで、ok又は
キャンセルボタン又は、右上の×を押したときに "で いいですか" ok 、キャンセルボタンが出ます。
担当者は、はい のつもりでokを押すとエラーが出ます。 あるいは、入力待ちになっているときに数値の1(小文字)を入力した後でキャンセルを押しますと"いいですか"ok、キャンセルボタンが出ます。やはりここでもokボタンを押しますとエラーがでます。只、それぞれにキャンセルを押せばVBAは終わります。
教えて頂きたいのは、Input Boxで"任意のシート名を入力して下さい"の入力待ちになっているところで例えば1の数字を入れてokボタンを押しますとシート1にコピーされます。
数字を入れてokボタンを押す操作以外は何かをクリック(右上の×も含みます)すればVBAを終えるようにして頂きたいのです。
宜しくお願い致します。
(和代) 2014/08/25(月) 10:05
>Worksheets(sht).Range("A1").PasteSpecial って事は、値貼付にしてあるわけじゃないんですね。 そしたら、どうせ数式にしておいた方が良いと思いますので このままの方針でどうでしょう?
>数字を入れてokボタンを押す操作以外は何かをクリック(右上の×も含みます)すればVBAを終えるようにして頂きたいのです。 でしたら、もう一度↓で [[20140817152501]] 『各シートに貼り付け』(和代) ichinoseさんが書いて下さったコードを確認してみて下さい。
最初の Const cancel As Variant = "False" を Const cancel As Variant = False に変更してもらうと、数値を入力した時にだけ「○○日 ですか?」の メッセージボックスが表示され、こで[OK]を押した時に 「処理開始」のメッセージボックスが表示されると思います。
処理をしてほしいタイミングで「処理開始」が出ることを確認出来たら 該当部分 MsgBox "処理開始" で、メッセージボックスを表示させるのではなく コピー処理をさせて下さい。
ところてんさんのコードがありますから、実際のコピー処理のコードは分かりますね? 最新のコード(前スレの2014/08/18(月) 11:01に投稿してもらってるコード)を参考にして下さい。 (HANA) 2014/08/25(月) 11:26
「ichinoseさんが書いて下さったコードを確認してみて下さい。
最初の Const cancel As Variant = "False" をConst cancel As Variant = False に変更してもらうと、数値を入力した時にだけ 「○○日 ですか?」のメッセージボックスが表示され、こで[OK]を押した時に 「処理開始」のメッセージボックスが表示されると思います。」と前レスの18日11:01の コードを利用ましたら、
入力待ち画面で×又はキャンセルボタンを押せばVBAは終わります。有難うございます。
ここから質問です。?@と?Aです
?@しかし入力待ち画面でokボタンを押せば(普通は未入力ですから押さないと思いますが、
わざと誤って押した場合を想定しました)エラーになり次のメッセージが出ました。
メッセージ「数式を入力していない場合は次の点に注意します(間2行ほど飛ばします。)
入力内容 ’=1+1セルの表示=1+1 OK ヘルプ」
?A教えて頂いた様に下記コードを実行しましたが、貼り付け先(シート1)へは何も貼られていません。
どこを直せばいいのでしょうか?教えて頂けますか?
Sub test() Const cancel As Variant = False Dim dd As Variant dd = Application.InputBox("何日のシートに貼り付けしますか?", "シート選択", , , , , , 1) If dd <> cancel Then If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then Worksheets("給与集計").Range("a1:aw51").Copy Worksheets(dd).Range("A1").PasteSpecial End If End If End Sub (和代) 2014/08/25(月) 14:07
>わざと誤って押した場合を想定しました)エラーになり次のメッセージが出ました。 はい。 [OK]を押す場合は、何か数値の入力が必要です。 処理をしない場合は、キャンセルボタンか[×]を押してください。 これは、InputBoxでTypeに数値を指定した場合の仕様になります。
数値であれば、日付としてあり得ない 32 なんてのも通ります。 シートが無いのでその後エラーになると思いますが。
そういった事を想定するのなら、また別の処理が必要になります。
>教えて頂いた様に下記コードを実行しましたが、貼り付け先(シート1)へは何も貼られていません。 前回の投稿で >>最新のコード(前スレの2014/08/18(月) 11:01に投稿してもらってるコード)を参考にして下さい。 と書きましたが、前スレは確認しましたか?
参考にされているのは 2014/08/17(日) 20:04に投稿してもらっているコードの様ですが。 (HANA) 2014/08/25(月) 15:44
「参考にされているのは 2014/08/17(日) 20:04に投稿してもらっているコードの様ですが。」
あらら。なかなか簡単にはいかないですね。
当面、ボタンでマクロ処理かな。 SelectChange などの イベント処理だと、上手くいくのかな? 感想だけで済みません。 時間ができたら、いろいろと遊んでみます。
(Mook) 2014/08/17(日) 13:29
Mook様、有難うございます。
(和代) 2014/08/17(日) 15:20
と書きましたが、前スレは確認しましたか?」−−−私は8月18日11:01のところてん様のコードを使っていましたが、今、確認をしましたら私が勘違いしていました。コードシートに2つ書いていました。 一つにはところてん様のコード、二つ目には、ichinoseさんのコードを書きました。 下記?@HAMA様からの8月25日11:26の内、 「ichinoseさんが書いて下さったコードを確認してみて下さい。」−−−ここを見てコードを書きました。 ichinoseさんのコードを実際には使いました。
?@>Worksheets(sht).Range("A1").PasteSpecialって事は、値貼付にしてあるわけじゃないんですね。
そしたら、どうせ数式にしておいた方が良いと思いますので このままの方針でどうでしょう? >数字を入れてokボタンを押す操作以外は何かをクリック(右上の×も含みます)すればVBAを終えるようにして頂きたいのです。 でしたら、もう一度↓で [[20140817152501]] 『各シートに貼り付け』(和代) ichinoseさんが書いて下さったコードを確認してみて下さい。 最初の Const cancel As Variant = "False" を Const cancel As Variant = False に変更してもらうと、数値を入力した時にだけ「○○日 ですか?」の メッセージボックスが表示され、こで[OK]を押した時に 「処理開始」のメッセージボックスが表示されると思います。
処理をしてほしいタイミングで「処理開始」が出ることを確認出来たら 該当部分 MsgBox "処理開始" で、メッセージボックスを表示させるのではなく コピー処理をさせて下さい。 ところてんさんのコードがありますから、実際のコピー処理のコードは分かりますね? 最新のコード(前スレの2014/08/18(月) 11:01に投稿してもらってるコード)を参考にして下さい。 (HANA) 2014/08/25(月) 11:26 」 以上ですが、 わからなくなってきましたのは、ichinoseさんが教えて頂いたコードを使い、HAMAさまが教えて下さった Falseの""を外して、入力待ち画面で×又はキャンセルボタンを押せばVBAは終わりましたのに ところてん様のコードを使えばまた入力待ちで×、キャンセルを押せばエラーが出るのではないかと思いますが‐‐‐ 「[OK]を押す場合は、何か数値の入力が必要です。 処理をしない場合は、キャンセルボタンか[×]を押してください。 これは、InputBoxでTypeに数値を指定した場合の仕様になります。」−−−ここは理解できました。 以上です。今から約5時間くらい用事がありますので、席を立ちますが、私から返事がないから悩んでいると思わないでくださいませ。
(和代) 2014/08/25(月) 17:05
シートが無いのでその後エラーになると思いますが。 そういった事を想定するのなら、また別の処理が必要になります。」−−−31を超える数字を 誤って入力したときにやり直す方法を教えて下さい。宜しくお願い致します。
(和代) 2014/08/25(月) 17:10
ところてんさんのコードは二つあります。 最初の方のコードでデータを実際にコピーする部分は Worksheets("給与集計").Range("a1:aw51").Copy Worksheets(sht).Range("A1").PasteSpecial この時はインプットボックスに「1日」「2日」の様に入力するのが前提です。
後の方のコードは Worksheets("給与集計").Range("a1:aw51").Copy Worksheets(sht & "日").Range("A1").PasteSpecial '入力された^^^^^^^^^^半角数字+日となります。 になっています。 この時はインプットボックスに「1」「2」と入力するのが前提です。
ichinoseさんが書いて下さったコードは、インプットボックスに「1」「2」と入力します。 ですから、シート名は 入力された半角数字+日 にしないと思ったシートにならない場合があります。
Sheets(1) と書いてあると、先頭から数えて1番目のシートの事です。 Sheets("1") と書いてあると、シート名が「1」のシートの事です。 今回は、Sheets("1日") の様に 入力した数字に"日"を付け加えたシート名のシートを 処理対象にしないといけません。
で、違いが分かりますでしょうか?
>31を超える数字を誤って入力したときにやり直す方法を教えて下さい。 何も入力せずに[OK]を押した時に現在のメッセージが出るので良いのなら If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then の前に、ddが 1以上31以下の値か を確認して 条件に合っている時にだけ If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then の行に進める様にしてはどうでしょう?
具体的には If 1 <= dd And dd <= 31 Then If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then '〜〜〜〜〜 End If End IF って感じで。 (HANA) 2014/08/25(月) 17:26
If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then '〜〜〜〜〜 End If End IF 」 をコードに挿入しながらいろいろ変えてみたのですが、うまくいきません。 下記コードのWorksheets("2").Range("A1").PasteSpecialのWorksheets("2")右の"2"の変数が色々 変えても貼り付けがうまくいきません。 私の勉強と思い、一部、一部を教えて頂いていることはわかるのですが、申し訳ありませんがコードが わかっているようでわかりません。正しいコードの記述を教えて下さい。すみません。
Sub prcCellCopy給与集計1日()
Dim sht As Variant Dim v As Long dd = Application.InputBox("何日のシートに貼り付けしますか?", "シート選択", , , , , , 1)
'sht = InputBox("任意のシート名を入力して下さい") If 1 <= dd And dd <= 31 Then If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then '〜〜〜〜〜 End If End If
'v = MsgBox(sht & "でいいですか?", vbOKCancel) ' If 1 <= dd And dd <= 31 Then
'If v = vbOKCancel Then Worksheets("給与集計").Range("a1:aw51").Copy Worksheets("2").Range("A1").PasteSpecial 'End If 'End If Range("Q4").Select
End Sub (和代) 2014/08/25(月) 22:34
2014/08/25(月) 14:07で投稿された以下のコードですが
Sub test() Const cancel As Variant = False Dim dd As Variant dd = Application.InputBox("何日のシートに貼り付けしますか?", "シート選択", , , , , , 1) If dd <> cancel Then If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then Worksheets("給与集計").Range("a1:aw51").Copy Worksheets(dd).Range("A1").PasteSpecial End If End If End Sub
これは、すでにお書きのように >貼り付け先(シート1)へは何も貼られていません。 の問題があります。
たとえば、インプットボックスに入力した値が「1」だった場合 Worksheets(1).Range("A1").PasteSpecial になるので、左から数えて一番目のシートに貼り付ける事になります。
実際は「1日」という名前のシートに貼り付けたいので 入力した値と"日" を組み合わせた dd & "日" というシート名のシートに貼り付ける必要があります。 コードを変更すると Sub test2() Const cancel As Variant = False Dim dd As Variant dd = Application.InputBox("何日のシートに貼り付けしますか?", "シート選択", , , , , , 1) If dd <> cancel Then If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then Worksheets("給与集計").Range("a1:aw51").Copy Worksheets(dd & "日").Range("A1").PasteSpecial End If '~~~~~~~~~シート名が dd日 のシート End If End Sub になります。
>31を超える数字を誤って入力したときにやり直す方法を教えて下さい。 は、 If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then Worksheets("給与集計").Range("a1:aw51").Copy Worksheets(dd & "日").Range("A1").PasteSpecial End If この4行が ddに入力された値が 1〜31 だった時に実行されればよいですよね?
ですから、 Sub test3() Const cancel As Variant = False Dim dd As Variant dd = Application.InputBox("何日のシートに貼り付けしますか?", "シート選択", , , , , , 1) If dd <> cancel Then If 1 <= dd And dd <= 31 Then '4行を挟む↓ If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then Worksheets("給与集計").Range("a1:aw51").Copy Worksheets(dd & "日").Range("A1").PasteSpecial End If End If '4行を挟む↑ End If End Sub の様にすれば良いと思いますが、どうですか?
>〜〜をコードに挿入しながらいろいろ変えてみたのですが、うまくいきません。 私が乗せた物をそのままどこかに貼り付けたのではうまくいかないでしょうね。
test1のコードは ichinoseさんのコードとところてんさんのコードを組み合わせたと思いますが メッセージボックスを表示させる1行を 実際にコピーして貼り付ける2行に変えましたね?
それぞれの行は意味があります。
1.変数ddに値を入力してもらう 2.入力された値が cancel でなかったら、次の処理に進む。 3.入力された値が 1〜31 の間だったら、次の行に進む。 4.シート名を確認してもらい[OK]だったら、次の行に進む。
よく見るとこの部分のコード If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOKCancel Then ってなってますね? 元は If MsgBox(StrConv(dd, vbWide) & "日 ですか?", vbOKCancel) = vbOK Then となっていたと思いますが?
5.給与集計シートのA1:AW51をコピーする 6.入力された日付のシートのA1セルから貼り付ける
5・6のコードがどんな時に実行されれば良いのか、考えながら コードを見てもらうと良いかもしれません。 (HANA) 2014/08/26(火) 00:22
実行しましたらWorksheets(dd & "日").Range("A1").PasteSpecialでエラーが発生しました。
私が実際に使っていますシート名は"1" "2"と順次"31"迄で日は入っていません。
「実際は「1日」という名前のシートに貼り付けたいので
入力した値と"日" を組み合わせた dd & "日" というシート名のシートに貼り付ける必要があります。」 私が実際に使っていますシート名は"1" "2"と順次"31"迄で日は入っていません。
(和代) 2014/08/25(月) 22:34の質問の前に色々変更しました中で
Worksheets(dd & "日").Range("A1").PasteSpecialの(dd&"日")をシート名は日がないために(dd)とか("dd")にして実行しましたが貼り付けられませんでした。
(和代) 2014/08/26(火) 00:57
読みなおしたり他の投稿を参考にしたりしている色々コードを利用しまして
したのですがうまくいかず、ひょっとして月を入れたら、いけるのかなあと思い出来ました。
それまでは、どこかの投稿で下記コードは、入力は数値だけでいいですと書いてあるのを見まして使って
いました。ところが入力の際に数字をいれずに×、キャンセル等を押せばエラーが返って来たので
現在色々教えて頂いているところです。
Sub prcCellCopy給与集計1日() 'シート名入力の際は数値のみ入力でOK Dim sht As Variant
Dim v As Long sht = InputBox("シート名?") v = MsgBox(sht & "でいいですか?", vbYesNo) If v = vbYes Then Worksheets("給与集計").Range("a1:aw100").Copy Worksheets(sht).Range("A1").PasteSpecial End If Range("Q4").Select
End Sub
大変ご足労かけて申し訳なく思っています。
HAMA様から、月を入れておくのは当然でしょう、そこは正しく入っているものと思い、他に何故?と思っていたんだよと声なき声が聞こえて来そうです。理解力の悪いものに付き合って頂き感謝しています。
一旦この件では無事にすみました。有難うございました。
(和代) 2014/08/26(火) 14:46
>私が実際に使っていますシート名は"1" "2"と順次"31"迄で日は入っていません。 はっ、そうだったんですね。
でしたら
案1 Worksheets(dd & "日").Range("A1").PasteSpecial ↓ Worksheets(dd & "").Range("A1").PasteSpecial にして、入力された数値を文字列に直して指定する
案2 dd = Application.InputBox("何日のシートに貼り付けしますか?", "シート選択", , , , , , 1) ↓ dd = Application.InputBox("何日のシートに貼り付けしますか?", "シート選択", , , , , , 2) そもそも、文字列で入力するようにする
でどうでしょう?
案2でしたら、インプットボックスで何も入力をせずに[OK]を押しても、何も起きません。
>数値に月を入れましたら貼り付けがうまく行きました。 ってどういうことですか? (HANA) 2014/08/26(火) 16:06
>数値に月を入れましたら貼り付けがうまく行きました。
ってどういうことですか?---月ではなく日です。例えば今まで、シート名:1 の所を シート名:1日と打ったら うまく貼り付けされましたという事です。 打ち間違いです、すみません。
案2を後で実行します。
本当に有難うございました。
(和代) 2014/08/26(火) 19:08
上記、(HANA様)投稿 2014/08/26(火) 16:06の案2を使用しました。
「案2でしたら、インプットボックスで何も入力をせずに[OK]を押しても、何も起きません。」
貼り付けをする動作のVBAなのに{ok}を押しても何も起こらない要因にもなるのですね。
有難うございました。
ちなみに、今までそのまま使っていましたが、 Application.InputBox("何日のシートに貼り付けしま
すか?", "シート選択", , , , , , 1)のシート選択,が6つあるのはどういう意味を持つのですか?
(和代) 2014/08/27(水) 12:25
>シート名:1日と打ったら うまく貼り付けされましたという事です。 そうですよね、数式の時にシート名が「1」「2」だったのですから VBAでも「1」「2」ですよね。 気づかなくてすみませんでした。
>貼り付けをする動作のVBAなのに{ok}を押しても何も起こらない要因にもなるのですね。 書き方が雑だったかもしれないです。 正しく伝わっているのかもしれませんが。
InputBoxの最後の所が1の時は、何も入力せずに[OK]を押すと 何か数値の入力が必要です。 と、メッセージが表示されましたね? 最後の所を2にすると、何も入力せずに[OK]を押しても 何か数値の入力が必要です。 のメッセージは表示されません。
「貼り付けをする動作のVBA」と言うのは、全体の事を言っておられるのですよね? 変更した dd = Application.InputBox("何日のシートに貼り付けしますか?", "シート選択", , , , , , 1) の行の事ではなく。
>シート選択,が6つあるのはどういう意味を持つのですか? ヘルプを確認してみてもらいましたか?
構文 式.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type) って書く時の・・・・・・・ ~~~~~~~ ~~~~ ~~~ ~~~~~~~~ ~~~~~~~~~~~~~ の5件が 特に指定をしない(デフォルトで良い)ので、記入をしておらず「,」だけが並んでいます。 (HANA) 2014/08/27(水) 16:32
「構文
式.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type) って書く時の・・・・・・・ ~~~~~~~ ~~~~ ~~~ ~~~~~~~~ ~~~~~~~~~~~~~ の5件が 特に指定をしない(デフォルトで良い)ので、記入をしておらず「,」だけが並んでいます。」 私:3年前くらいに・InputBoxを使う為に調べていました時にデフォルトでよいので,が5件程(忘れま した)並んでいたのを思い出しました。 有難うございました。
(和代) 2014/08/27(水) 18:11
>貼り付けをする動作のVBAなのに{ok}を押しても何も起こらない要因にもなるのですね。 ってどういうことだったのでしょう?
何も起こらない→「何か数値の入力が必要です。」の、メッセージが表示されない という事であればそうですが 何も起こらない→貼り付けされない という事であれば、それは INPUTBOXのTypeを「2」に変更したからではないですが。 (HANA) 2014/08/27(水) 23:11
ってどういうことだったのでしょう? 「InputBoxの最後の所が1の時は、何も入力せずに[OK]を押すと何か数値の入力が必要です。 と、メッセージが表示されましたね? 最後の所を2にすると、何も入力せずに[OK]を押しても何か数値の入力が必要です。 のメッセージは表示されません。」 私:メッセージは表示されません と言うことです。 何も起こらない要因という言い方が誤解を招く言い方で申し訳ありませんでした。
「何も起こらない→貼り付けされない
という事であれば、それは INPUTBOXのTypeを「2」に変更したからではないですが」 私:その通りです。私にとっては1を2に変えるだけでメッセージが表示されない事に驚いた わけです。それを何も起こらない要因と書きました。
(和代) 2014/08/28(木) 00:25
>私:メッセージは表示されません と言うことです。 でしたらやはり >> 正しく伝わっているのかもしれませんが。 の方でしたね。
案1の方も確認してもらえましたか?
また、TYPE関数について調べて、ワークシート上で A1セルに 1 を入力 A2セルに =A1&"" の式を入力 B1セルに =TYPE(A1) の式を入力し、B2セルにコピー。 した結果も確認してみて下さい。 (HANA) 2014/08/28(木) 08:36
A1セルに 1 を入力 A2セルに =A1&"" の式を入力 B1セルに =TYPE(A1) の式を入力し、B2セルにコピー。」 TYPE関数調べました。A2が文字列扱いの為、戻り値が2、後は数値の為戻り値は1ということ ですね、
関連して、シフト担当者からこの表を見て下記の追加質問がありました。
宜しくお願い致します。
下記1、の出勤時刻と退出時刻が2、の出勤時刻と退出時刻に一致すればA列のコードNo.を3、の該当者のB列に表示してください
名前の順番は、1、2、3、共 同じ並びです。
1、シート名:"月シフト"C3とC4を結合して名前が入っています(例えば田中)
D3に出勤時刻(=IFERROR(VLOOKUP($C3,'1日'!$F7:$H$51,2,FALSE),"")が
D4に退出時刻(=IFERROR(VLOOKUP($C3,'1日'!$F7:$H$51,2,FALSE),"")が入っています。
2、新シート名”給与体系コード表”
A行が見出しです
A1にコードNo. B1に出勤時刻 C1に退出時刻
A2 1(数値) B2 9:00 C2 16:00
A3 2 B3 9:30 C3 16;30
A4 3 B4 10:00 C4 17:00
下にずーと続きます
コードは1から約60ほど(今、はっきり覚えていません)あります。
3、新シート名”出勤退出該当者コード”
A行が見出しです
A1に名前 B1にコード表
A2 田中 B2 1
A3 山本 B3 2
A4 林 B4 3
教えて頂いたインデックスとイフエラー関数で行けると色々確認したのですが、
2、のB列だけならば行けるのですが、BとC列両方一致する方法がわかりません。
私の会社のタイムカードは、かたい紙ではなく カードを通します。
PCに就業ソフトが入っています。
タイムカードと就業ソフトは連動しています。
就業ソフトで捜査をするとタイムカードからのデータを集めます
就業ソフトのデータ項目に従業員の名前と出勤日、出勤時間、退出時間、残業時間等が表示されます
そしてもう一つ給与体系のコードを選択する項目があります
?@コードの使用は、例えば本人がタイムカードを通したときの出勤時刻が9:02 退出の時刻が
16:03 の時に、給与体系コード1を選べばあらかじめ設定されている出勤時刻9:00
退出時刻16:00に変更されます。
そのコード1を選ぶのはシフト担当者が選ぶのですが、就業ソフトではコード表を選ぶのにボタンを下に
沢山下ろしていかなければいけません。そこでコード表をA4サイズで2列に分けた印刷物を見て入力
しています。
担当者からの依頼は、シート名”月シフト”にデータがあるのですから、
別シート”給与体系コード表”を作って ”月シフト”の出勤時間と退出時間が給与体系コード表と
合致すれば別シート”出勤退出コード”の各名前(1行毎に縦に並んでいます)の右列にコードを
表示してほしいとの事です。以上です、長くなっていますが、仕事の合理化と担当者が余分な時間を
作らないためにもご協力お願い致します。
(和代) 2014/08/28(木) 18:08
シフト関係の質問は、終わります。
本当に長い時間を費やして頂き感謝します。
有難うございました。
(和代) 2014/08/29(金) 18:36
すみません。 ちょっとよくわからなかったのでコメントが遅くなりました。
>出勤時刻が9:02 退出の時刻が16:03 の時 >給与体系コード1を選べばあらかじめ設定されている >出勤時刻9:00 退出時刻16:00に変更されます。 9:00に出勤していないのに、出勤を9:00にしてしまうのは 「2分遅れたけど、3分多く居たから」なのでしょうか? それとも、「給与体系の中にある近い時間とみなす」のでしょうか? あるいは、もっと単純に 00〜15は「00分」、15〜45は「30分」、45〜60は「+1時間した00分」 といった丸めをするのでしょうか?
>一人の人の出勤時間と退出時間が縦に並んでいるところを横列にして〜〜 という事ですが、各日付のシートのS:T列にすでにご希望の順番で並んでいるので そこでコードを求めた方が単純に出来そうに思いますが。
・・・どのような数式でコードを求めておられるのかよくわからないですが。。。 (HANA) 2014/08/29(金) 22:17
「一人の人の出勤時間と退出時間が縦に並んでいるところを横列にして〜〜
という事ですが、各日付のシートのS:T列にすでにご希望の順番で並んでいるので そこでコードを求めた方が単純に出来そうに思いますが。」
一つのシートで1日から31日迄をまとめて見れるようにする為です。
就業ソフトはAさんをクリックすると1日から31日迄入力するようになっていますので
エクセルも一枚で31日間が見れるようにと思ったからです。
「各日付のシートのS:T列にすでにご希望の順番で並んでいるので
そこでコードを求めた方が単純に出来そうに思いますが。」明日会社に行って確認をします、 有難うございます。
(和代) 2014/08/29(金) 22:47
「各日付のシートのS:T列にすでにご希望の順番で並んでいるので
そこでコードを求めた方が単純に出来そうに思いますが。」−−−今、会社に来て確認を取りましたが
シート名:月シフトは、横列に1日から31日迄並んでいます、縦列にはアルバイトの全員の名前が
並んでいます。各シートからであれば各シートのその日に出勤した人だけの名前が表示されますので
全員のリストでコードを表示するのであれば、面倒かもしれませんがシート名の月シートから
出勤時刻と退出時刻の時間を参照にする方がよろしいかと思いますが如何ですか?
(和代) 2014/08/30(土) 13:53
コード表を表示する新シートでは、例えばA1から下にアルバイトの全員の名前を並べます。(インデックス関数を使います)
日付をB1(1日)から横列に31日迄並べます。日々の出勤者(HAMAさまがおっしゃる、出勤時刻と
退出時刻が横列に並んでいるところ)の名前とA1の名前と合致した人の右列に時刻が一致した所の
コード表を表示するようにもって来ればいいのですよね。そして1日にコード表が表示されれば、右に式をコピーしてシート1日が2日、3日ーーー31日と移動出来ればいいのですね。
(和代) 2014/08/30(土) 14:15
ブックの状態がどうなっているのかよくわからないのですが
>シート名“1”では("2”から"31”迄配置は同じです) >項目名R6に名前 S6に 出勤時間 T6に退出時間 >〜〜〜〜〜 の R7から下に表示されている名前は コード表を表示する新シートの例えばA1から下に表示したい名前と 順番等が違ったりするのでしょうか?
実はそれよりも >コード表の出勤時間と退出時間に合っていれば別の列にコードを表示 >する事という事を関数を使って出来ました。 の所で、本当に希望するコード番号が引っ張ってこられているのか気になるのですが。
最終的には、人の目の確認が必要じゃないですか? 出勤・退勤時間が表示されている表と、新しく作ったコードを表示している表と 両方を見ながら(或いは事前チェックをして)入力していくのでしょうか? (HANA) 2014/08/30(土) 15:01
下記(ア)からは先程更新時の質問です。 今から( )内に書くのは、(HANA) 2014/08/30(土) 15:01のご質問を見ての答えです。 (コード表を表示する新シートの例えばA1から下に表示したい名前と 順番等が違ったりするのでしょうか?−−−順番は、違いません。
最終的には、人の目の確認が必要じゃないですか?
出勤・退勤時間が表示されている表と、新しく作ったコードを表示している表と 両方を見ながら(或いは事前チェックをして)入力していくのでしょうか?−−−その通りなのですが ソフトのコード番号はボタンをクリックしてずーと該当時刻迄ずらす為に見ずらいので、 かたやコードNOを自動的に表示(実際には、コードNOの右横には出勤時刻と退出時刻を表示しますが) してその紙を見てもらう方が入力しやすいのではと思いまして−−−)
(ア)
HAMAさまの言うようにシート名:”sheet1”で各シートから時刻を抽出しようとしていますが
下記式を右横にフィルドラッグしようとしてもシート名が変わりません。
シート名:1日が2日ーーー31日に変わるようにするには何が違うのでしょうか?
お教え下さい。
参考:H3行名に右横の式が入っています。=IF(AND('1日'!$G7=Sheet1!$C3,'1日'!$H7=Sheet1!$D3),Sheet1!$B3,"") GとHは各日ごとの時刻が横列に並んでいる場所です
シート名:”sheet1”の詳細は、
2行目は、項目名です。 B2コード名、C2出勤時間、D2退出時間 G2氏名 H2,1日 I2,2日
3行目 1 8:30 13:00 田中 ?@の式
4行目 2 9:00 14:00 山本
5行目 3 9:00 15:00 林
6行目 4 9:00 16:00 井上
7行目 5 9:00 17:00 川崎
(和代) 2014/08/30(土) 16:27
>(実際には、コードNOの右横には出勤時刻と退出時刻を表示しますが) この事の確認でした。
>下記式を右横にフィルドラッグしようとしてもシート名が変わりません。 ですから、その場合はINDIRECT関数を使って下さい。 それが嫌なら、シート名の所は書き換えて下さい。
シート名:”sheet1”の詳細を書いてもらってますがよくわかりません。。。
スレも長くなったので、仕切り直しで新しく質問を書いてもらえると良いのですが。
その際、1日シートの >項目名R6に名前 S6に 出勤時間 T6に退出時間 部分のサンプルコードと、 こちらに載せてもらっている給与体系コード表シートのデータと 作りたいシート(sheet1?)にどの様になれば良いのか セットで載せてもらえると良いと思います。
また >B2コード名、C2出勤時間、D2退出時間 G2氏名 といった書き方ではなく、もっとワークシートっぽく [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [1] [2] コード名 出勤時間 退勤時間 氏名 1日 出勤時間 退勤時間 2日 出勤時間 退勤時間 [3] 1 8:30 13:00 田中 [4] 2 9:00 14:00 山本 [5] 3 9:00 15:00 林 ↑ワークシートの 1行目に[A][B][C]・・・,A列に[1][2][3] を入力し B2セルから表をエクセルで作って貼り付けました。 ブラウザ上では表示がずれているかもしれませんが、コピーして エクセルにテキスト貼り付けしてもらうと それぞれのセルに貼りつきます。 の様に載せてもらえるとわかりやすいと思います。 (HANA) 2014/08/30(土) 20:09
HAMA様 上記各々のコメント本当に理解しました。 有難うございます。
「[INDIRECT関数は度々再計算されますので
あまり広範囲に使用すると、動きが重くなるかもしれません。 また、開いた時に何もしなくても再計算されますので 何もせずに閉じようとしても「上書きしますか?」と聞かれる様になります。 その辺も考慮して、使用・不使用を決めてもらえたらと思います。」 と言われていましたので、以前に使用しないようにしていました。
落ち着いた時に、セットで考えて新たな質問を致します。
(和代) 2014/08/30(土) 20:46
残業列を挿入する前に完了したファイルで、シート名:出勤者のコード表(A列が名前、横列が1日から31
日迄)とシート名:就業奉行入力用(A列が1日から31日迄、横列が名前。最終的に必要なシートです。
)があり保存していました。
別ファイル名とした後で、シート名:就業奉行入力用(A列が1日から31日迄、横列が名前)が出来た為に
シート名:出勤者のコード表(A列が名前、横列が1日から31日迄)が不要となって削除して現行に
至っています。
故に保存していたファイルのシート名:出勤者のコード表を移動・コピー、コピー先を
別ファイル名の中に入れました。
シート名:出勤者のコードでのB2の式=(IFERROR(VLOOKUP('1日'!$T7&"_"&'1日'!$U7,マスターコード結合!$F$2:$G$66,2,FALSE),""))&" "&('1日'!$T7&" ― "&'1日'!$U7)を右にコピーして、C2(2日)から
AF2(31日)の式の中の日を該当日に変更して
コピー元を1日から31日、コピー先をB3からラストの名前迄としました。
そして1日の縦列をコピー元とし、シート名:就業奉行入力用へ形式を選択して貼り付け、行列を入れ替えるでOKボタン、順次2日から31日迄同じ操作で出来ました。
実際には、日毎に行列を入れ替えて貼り付けをしても結果の答えを確認のする際に、一部の列の式が、
関数の対象セルが関係のないセルに置き換わっていまして難儀しました。でもコピー元を一列全員にしないで2行毎貼り付けをするとうまく行きました。
以上です 色々有難うございました。
(和代) 2014/09/21(日) 22:00
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.