[[20140327112415]] 『エクセルの関数についての質問です。出勤状況の表』(月) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『エクセルの関数についての質問です。出勤状況の表に入力されている関数を使って以下の様に変更したいのですがご協力ください。 』(月)

C7=IF(COUNTIF('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$G:$G,$C$4)>=ROW('[社長使用 評価表データ保存用(使用不可).xlsx]多'!A1),INDEX('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$C:$C,LARGE(INDEX(('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$G$1:$G$373=$C$4)*ROW('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$A$1:$A$373),),COUNTIF('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$G:$G,$C$4)-ROW('[社長使用 評価表データ保存用(使用不可).xlsx]多'!A2)+1)),"")
が入力されています。

 違うブックから検索して日付を入力させているのですが、4月なら4月だけの検索値を入力したいのですが、そんな事は可能でしょうか?
 検索して入力されている内容は
C7から下へ
 2013/4/1
 2013/4/2
 2013/4/4
 2013/4/8
 2013/5/1 ↓ここから下を表示しない様にしたいです。
 2013/6/1  しかしH2を5月としたらC7から表示させたいです。
 2013/7/1  今、入力している関数も色々な所から引っ張ってきて作っていますので
 2013/8/1  内容を良く理解出来ていない部分もあります。
 2013/9/1  Yahooの知恵袋で質問したのですが回答が一つしか付かず、
 2013/10/1  実現も出来ませんでした。ROW関数は不要と言われたのですが…。
 2013/11/1  これが実現出来れば、ほぼほぼ完成なのです。
 2013/12/1
 2014/1/1
 2014/2/1
 2014/3/1が入っています。

 IF関数やAND関数等をを使って試してみたのですが私の力では出来ませんでした。

 E2=2013 数値 表示形式で年を表示
 H2=4 リストにより数値 表示形式で月を表示
 C4=欠勤 検索する文字
 
※※VBAは受け渡す方が使いきれないので関数で行いたいです。※※

難しい質問だと思うのですが、どうぞ宜しくお願い致します。
ご協力お願い致します!!!!!

Yahoo知恵袋
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11122046181

< 使用 Excel:Excel2007、使用 OS:Windows7 >


 Yahoo知恵袋の方に画像がある様ですが、小さくてよく見えないです。
 載せておられる数式が使える状況
  どの様なブックにどの様なシートとどの様なデータを入れれば良いのか。
 と、その数式の概要(○×なものを一覧にする 等)を
 書いてみられてはどうでしょう。
  
(HANA) 2014/03/28(金) 09:29

HANA様
返信ありがとうございます。返事が大変遅くなり申し訳ありません。
Yahooの画像が小さくて申し訳ありません。(表示を400%にしてもぼやけて見えにくくて分かりませんよね?)

社長使用 評価表1と言うブックに1カ月ずつの勤怠表があります。
それに出勤時間などを入力して給料計算が出来る様に作っています。
この評価表では抽出するには(私には)出来なかったのでデータ保存用として、評価表のデータをリンクさせて抽出出来る様に一人ずつ一覧にしました。

一覧にしたデータから欠勤の日付だけをやっとの思いで抽出し表示させる事が出来たのですが、1年分の欠勤が抽出されてしまうので、それを該当する月だけを表示させたくてこの度相談させていただきました。

どこまで数式などを書き込んだらいいのか分かりませんが…。

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q10118156690

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13115069533

参考までに私が投稿した知恵袋です。
良ければ、また返信いただけると嬉しいです。
(月) 2014/04/10(木) 10:29


 社長使用 評価表データ保存用(使用不可).xlsx と言うブックの
 多 と言うシート
 を、A1セルからデータがある範囲までコピーして
 こちらに貼りつけてもらえませんか?

 もちろん、名前などが入っている部分は 他の適当な文字に変更して下さい。
  
(HANA) 2014/04/10(木) 11:26

HANA様

一年分縦に入っていますので5月頭までで宜しいでしょうか?
実際年月日が入っているのはA3からになります。(まだ試作中なので、ひとまず欠勤などを入力しているだけです)
縦に123〜等の番号は作業内容に入力されています。
番号は間違いなくリンクされているか確認の為に入力しているだけです。
4月分が5月の様に表示されませんが実際は5月の様に入っています。
★印は休日だったら★を入力する様にしています。
A1には氏名が入力されています。
年 月 日 曜日 天気 休日 ★ 出勤状況 作業内容
2013 年 4 月 1 日 月   欠勤 1
2013 年 4 月 2 日 火   欠勤 2
2013 年 4 月 3 日 水   遅刻 3
2013 年 4 月 4 日 木 晴   欠勤 4
2013 年 4 月 5 日 金   早退 5
2013 年 4 月 6 日 土   忌引 6
2013 年 4 月 7 日 日   出停 7
2013 年 4 月 8 日 月   欠勤 8
2013 年 4 月 9 日 火   9
2013 年 4 月 10 日 水   10
2013 年 4 月 11 日 木   11
2013 年 4 月 12 日 金   12
2013 年 4 月 13 日 土   13
2013 年 4 月 14 日 日   14
2013 年 4 月 15 日 月   15
2013 年 4 月 16 日 火   16
2013 年 4 月 17 日 水   17
2013 年 4 月 18 日 木   18
2013 年 4 月 19 日 金   19
2013 年 4 月 20 日 土   20
2013 年 4 月 21 日 日   21
2013 年 4 月 22 日 月   22
2013 年 4 月 23 日 火   23
2013 年 4 月 24 日 水   24
2013 年 4 月 25 日 木   25
2013 年 4 月 26 日 金   26
2013 年 4 月 27 日 土   27
2013 年 4 月 28 日 日   28
2013 年 4 月 29 日 月   29
2013 年 4 月 30 日 火   30

		--	--		 		31
2013 年	5 月	1 日	水		 	欠勤	32
2013 年	5 月	2 日	木		 		33
2013 年	5 月	3 日	金		 		34
2013 年	5 月	4 日	土		 		35
2013 年	5 月	5 日	日		 		36
2013 年	5 月	6 日	月		 		37
2013 年	5 月	7 日	火		 		38
2013 年	5 月	8 日	水		 		39
2013 年	5 月	9 日	木		 		40
2013 年	5 月	10 日	金		 		41
2013 年	5 月	11 日	土		 		42
2013 年	5 月	12 日	日		 		43
2013 年	5 月	13 日	月		 		44
2013 年	5 月	14 日	火		 		45

(月) 2014/04/10(木) 13:18


HANA様

年 月 日 曜日 天気 休日 ★ 出勤状況 作業内容
2013 年 4 月 1 日 月   欠勤 1
2013 年 4 月 2 日 火   欠勤 2
2013 年 4 月 3 日 水   遅刻 3
2013 年 4 月 4 日 木 晴   欠勤 4
2013 年 4 月 5 日 金   早退 5
2013 年 4 月 6 日 土   忌引 6
2013 年 4 月 7 日 日   出停 7
2013 年 4 月 8 日 月   欠勤 8
2013 年 4 月 9 日 火   9
2013 年 4 月 10 日 水   10
2013 年 4 月 11 日 木   11
2013 年 4 月 12 日 金   12
2013 年 4 月 13 日 土   13
2013 年 4 月 14 日 日   14
2013 年 4 月 15 日 月   15
2013 年 4 月 16 日 火   16
2013 年 4 月 17 日 水   17
2013 年 4 月 18 日 木   18
2013 年 4 月 19 日 金   19
2013 年 4 月 20 日 土   20
2013 年 4 月 21 日 日   21
2013 年 4 月 22 日 月   22
2013 年 4 月 23 日 火   23
2013 年 4 月 24 日 水   24
2013 年 4 月 25 日 木   25
2013 年 4 月 26 日 金   26
2013 年 4 月 27 日 土   27
2013 年 4 月 28 日 日   28
2013 年 4 月 29 日 月   29
2013 年 4 月 30 日 火   30

		--	--		 		31
2013 年	5 月	1 日	水		 	欠勤	32
2013 年	5 月	2 日	木		 		33
2013 年	5 月	3 日	金		 		34
2013 年	5 月	4 日	土		 		35
2013 年	5 月	5 日	日		 		36
2013 年	5 月	6 日	月		 		37
2013 年	5 月	7 日	火		 		38
2013 年	5 月	8 日	水		 		39
2013 年	5 月	9 日	木		 		40
2013 年	5 月	10 日	金		 		41
2013 年	5 月	11 日	土		 		42
2013 年	5 月	12 日	日		 		43
2013 年	5 月	13 日	月		 		44
2013 年	5 月	14 日	火		 		45
2013 年	5 月	15 日	水		 		46
2013 年	5 月	16 日	木		 		47
2013 年	5 月	17 日	金		 		48
2013 年	5 月	18 日	土		 		49
2013 年	5 月	19 日	日		 		50
2013 年	5 月	20 日	月		 		51
2013 年	5 月	21 日	火		 		52
2013 年	5 月	22 日	水		 		53
2013 年	5 月	23 日	木		 		54
2013 年	5 月	24 日	金		 		55
2013 年	5 月	25 日	土		 		56
2013 年	5 月	26 日	日		 		57
2013 年	5 月	27 日	月		 		58
2013 年	5 月	28 日	火		 		59
2013 年	5 月	29 日	水		 		60
2013 年	5 月	30 日	木		 		61
2013 年	5 月	31 日	金		 		62
2013 年	6 月	1 日	土		 	欠勤	63
2013 年	6 月	2 日	日		 		64
2013 年	6 月	3 日	月		 		65
2013 年	6 月	4 日	火		 		66
2013 年	6 月	5 日	水		 		67
2013 年	6 月	6 日	木		 		68
2013 年	6 月	7 日	金		 		69
2013 年	6 月	8 日	土		 		70
2013 年	6 月	9 日	日		 		71
2013 年	6 月	10 日	月		 		72
2013 年	6 月	11 日	火		 		73
2013 年	6 月	12 日	水		 		74
2013 年	6 月	13 日	木		 		75
2013 年	6 月	14 日	金		 		76
2013 年	6 月	15 日	土		 		77
2013 年	6 月	16 日	日		 		78
2013 年	6 月	17 日	月		 		79
2013 年	6 月	18 日	火		 		80
2013 年	6 月	19 日	水		 		81
2013 年	6 月	20 日	木		 		82
2013 年	6 月	21 日	金		 		83
2013 年	6 月	22 日	土		 		84
2013 年	6 月	23 日	日		 		85
2013 年	6 月	24 日	月		 		86
2013 年	6 月	25 日	火		 		87
2013 年	6 月	26 日	水		 		88
2013 年	6 月	27 日	木		 		89
2013 年	6 月	28 日	金		 		90
2013 年	6 月	29 日	土		 		91
2013 年	6 月	30 日	日		 		92
		--	--		 		93
2013 年	7 月	1 日	月		 	欠勤	94
2013 年	7 月	2 日	火		 		95
2013 年	7 月	3 日	水		 		96
2013 年	7 月	4 日	木		 		97
2013 年	7 月	5 日	金		 		98
2013 年	7 月	6 日	土		 		99
2013 年	7 月	7 日	日		 		100
2013 年	7 月	8 日	月		 		101
2013 年	7 月	9 日	火		 		102
2013 年	7 月	10 日	水		 		103
2013 年	7 月	11 日	木		 		104
2013 年	7 月	12 日	金		 		105
2013 年	7 月	13 日	土		 		106
2013 年	7 月	14 日	日		 		107
2013 年	7 月	15 日	月		 		108
2013 年	7 月	16 日	火		 		109
2013 年	7 月	17 日	水		 		110
2013 年	7 月	18 日	木		 		111
2013 年	7 月	19 日	金		 		112
2013 年	7 月	20 日	土		 		113
2013 年	7 月	21 日	日		 		114
2013 年	7 月	22 日	月		 		115
2013 年	7 月	23 日	火		 		116
2013 年	7 月	24 日	水		 		117
2013 年	7 月	25 日	木		 		118
2013 年	7 月	26 日	金		 		119
2013 年	7 月	27 日	土		 		120
2013 年	7 月	28 日	日		 		121
2013 年	7 月	29 日	月		 		122
2013 年	7 月	30 日	火		 		123
2013 年	7 月	31 日	水		 		124
2013 年	8 月	1 日	木		 	欠勤	125
2013 年	8 月	2 日	金		 		126
2013 年	8 月	3 日	土		 		127
2013 年	8 月	4 日	日		 		128
2013 年	8 月	5 日	月		 		129
2013 年	8 月	6 日	火		 		130
2013 年	8 月	7 日	水		 		131
2013 年	8 月	8 日	木		 		132
2013 年	8 月	9 日	金		 		133
2013 年	8 月	10 日	土		 		134
2013 年	8 月	11 日	日		 		135
2013 年	8 月	12 日	月		 		136
2013 年	8 月	13 日	火		 		137
2013 年	8 月	14 日	水		 		138
2013 年	8 月	15 日	木		 		139
2013 年	8 月	16 日	金		 		140
2013 年	8 月	17 日	土		 		141
2013 年	8 月	18 日	日		 		142
2013 年	8 月	19 日	月		 		143
2013 年	8 月	20 日	火		 		144
2013 年	8 月	21 日	水		 		145
2013 年	8 月	22 日	木		 		146
2013 年	8 月	23 日	金		 		147
2013 年	8 月	24 日	土		 		148
2013 年	8 月	25 日	日		 		149
2013 年	8 月	26 日	月		 		150
2013 年	8 月	27 日	火		 		151
2013 年	8 月	28 日	水		 		152
2013 年	8 月	29 日	木		 		153
2013 年	8 月	30 日	金		 		154
2013 年	8 月	31 日	土		 		155
2013 年	9 月	1 日	日		 	欠勤	156
2013 年	9 月	2 日	月		 		157
2013 年	9 月	3 日	火		 		158
2013 年	9 月	4 日	水		 		159
2013 年	9 月	5 日	木		 		160
2013 年	9 月	6 日	金		 		161
2013 年	9 月	7 日	土		 		162
2013 年	9 月	8 日	日		 		163
2013 年	9 月	9 日	月		 		164
2013 年	9 月	10 日	火		 		165
2013 年	9 月	11 日	水		 		166
2013 年	9 月	12 日	木		 		167
2013 年	9 月	13 日	金		 		168
2013 年	9 月	14 日	土		 		169
2013 年	9 月	15 日	日		 		170
2013 年	9 月	16 日	月		 		171
2013 年	9 月	17 日	火		 		172
2013 年	9 月	18 日	水		 		173
2013 年	9 月	19 日	木		 		174
2013 年	9 月	20 日	金		 		175
2013 年	9 月	21 日	土		 		176
2013 年	9 月	22 日	日		 		177
2013 年	9 月	23 日	月		 		178
2013 年	9 月	24 日	火		 		179
2013 年	9 月	25 日	水		 		180
2013 年	9 月	26 日	木		 		181
2013 年	9 月	27 日	金		 		182
2013 年	9 月	28 日	土		 		183
2013 年	9 月	29 日	日		 		184
2013 年	9 月	30 日	月		 		185
		--	--		 		186
2013 年	10 月	1 日	火		 	欠勤	187
2013 年	10 月	2 日	水		 		188
2013 年	10 月	3 日	木		 		189
2013 年	10 月	4 日	金		 		190
2013 年	10 月	5 日	土		 		191
2013 年	10 月	6 日	日		 		192
2013 年	10 月	7 日	月		 		193
2013 年	10 月	8 日	火		 		194
2013 年	10 月	9 日	水		 		195
2013 年	10 月	10 日	木		 		196
2013 年	10 月	11 日	金		 		197
2013 年	10 月	12 日	土		 		198
2013 年	10 月	13 日	日		 		199
2013 年	10 月	14 日	月		 		200
2013 年	10 月	15 日	火		 		201
2013 年	10 月	16 日	水		 		202
2013 年	10 月	17 日	木		 		203
2013 年	10 月	18 日	金		 		204
2013 年	10 月	19 日	土		 		205
2013 年	10 月	20 日	日		 		206
2013 年	10 月	21 日	月		 		207
2013 年	10 月	22 日	火		 		208
2013 年	10 月	23 日	水		 		209
2013 年	10 月	24 日	木		 		210
2013 年	10 月	25 日	金		 		211
2013 年	10 月	26 日	土		 		212
2013 年	10 月	27 日	日		 		213
2013 年	10 月	28 日	月		 		214
2013 年	10 月	29 日	火		 		215
2013 年	10 月	30 日	水		 		216
2013 年	10 月	31 日	木		 		217
2013 年	11 月	1 日	金		 	欠勤	218
2013 年	11 月	2 日	土		 		219
2013 年	11 月	3 日	日		 		220
2013 年	11 月	4 日	月		 		221
2013 年	11 月	5 日	火		 		222
2013 年	11 月	6 日	水		 		223
2013 年	11 月	7 日	木		 		224
2013 年	11 月	8 日	金		 		225
2013 年	11 月	9 日	土		 		226
2013 年	11 月	10 日	日		 		227
2013 年	11 月	11 日	月		 		228
2013 年	11 月	12 日	火		 		229
2013 年	11 月	13 日	水		 		230
2013 年	11 月	14 日	木		 		231
2013 年	11 月	15 日	金		 		232
2013 年	11 月	16 日	土		 		233
2013 年	11 月	17 日	日		 		234
2013 年	11 月	18 日	月		 		235
2013 年	11 月	19 日	火		 		236
2013 年	11 月	20 日	水		 		237
2013 年	11 月	21 日	木		 		238
2013 年	11 月	22 日	金		 		239
2013 年	11 月	23 日	土		 		240
2013 年	11 月	24 日	日		 		241
2013 年	11 月	25 日	月		 		242
2013 年	11 月	26 日	火		 		243
2013 年	11 月	27 日	水		 		244
2013 年	11 月	28 日	木		 		245
2013 年	11 月	29 日	金		 		246
2013 年	11 月	30 日	土		 		247
		--	--		 		248
2013 年	12 月	1 日	日		 	欠勤	249
2013 年	12 月	2 日	月		 		250
2013 年	12 月	3 日	火		 		251
2013 年	12 月	4 日	水		 		252
2013 年	12 月	5 日	木		 		253
2013 年	12 月	6 日	金		 		254
2013 年	12 月	7 日	土		 		255
2013 年	12 月	8 日	日		 		256
2013 年	12 月	9 日	月		 		257
2013 年	12 月	10 日	火		 		258
2013 年	12 月	11 日	水		 		259
2013 年	12 月	12 日	木		 		260
2013 年	12 月	13 日	金		 		261
2013 年	12 月	14 日	土		 		262
2013 年	12 月	15 日	日		 		263
2013 年	12 月	16 日	月		 		264
2013 年	12 月	17 日	火		 		265
2013 年	12 月	18 日	水		 		266
2013 年	12 月	19 日	木		 		267
2013 年	12 月	20 日	金		 		268
2013 年	12 月	21 日	土		 		269
2013 年	12 月	22 日	日		 		270
2013 年	12 月	23 日	月		 		271
2013 年	12 月	24 日	火		 		272
2013 年	12 月	25 日	水		 		273
2013 年	12 月	26 日	木		 		274
2013 年	12 月	27 日	金		 		275
2013 年	12 月	28 日	土		 		276
2013 年	12 月	29 日	日		 		277
2013 年	12 月	30 日	月		 		278
2013 年	12 月	31 日	火		 		279
2014 年	1 月	1 日	水		 	欠勤	280
2014 年	1 月	2 日	木		 		281
2014 年	1 月	3 日	金		 		282
2014 年	1 月	4 日	土		 		283
2014 年	1 月	5 日	日		 		284
2014 年	1 月	6 日	月		 		285
2014 年	1 月	7 日	火		 		286
2014 年	1 月	8 日	水		 		287
2014 年	1 月	9 日	木		 		288
2014 年	1 月	10 日	金		 		289
2014 年	1 月	11 日	土		 		290
2014 年	1 月	12 日	日		 		291
2014 年	1 月	13 日	月		 		292
2014 年	1 月	14 日	火		 		293
2014 年	1 月	15 日	水		 		294
2014 年	1 月	16 日	木		 		295
2014 年	1 月	17 日	金		 		296
2014 年	1 月	18 日	土		 		297
2014 年	1 月	19 日	日		 		298
2014 年	1 月	20 日	月		 		299
2014 年	1 月	21 日	火		 		300
2014 年	1 月	22 日	水		 		301
2014 年	1 月	23 日	木		 		302
2014 年	1 月	24 日	金		 		303
2014 年	1 月	25 日	土		 		304
2014 年	1 月	26 日	日		 		305
2014 年	1 月	27 日	月		 		306
2014 年	1 月	28 日	火		 		307
2014 年	1 月	29 日	水		 		308
2014 年	1 月	30 日	木		 		309
2014 年	1 月	31 日	金		 		310
2014 年	2 月	1 日	土		 	欠勤	311
2014 年	2 月	2 日	日		 		312
2014 年	2 月	3 日	月		 		313
2014 年	2 月	4 日	火		 		314
2014 年	2 月	5 日	水		 		315
2014 年	2 月	6 日	木		 		316
2014 年	2 月	7 日	金		 		317
2014 年	2 月	8 日	土		 		318
2014 年	2 月	9 日	日		 		319
2014 年	2 月	10 日	月		 		320
2014 年	2 月	11 日	火		 		321
2014 年	2 月	12 日	水		 		322
2014 年	2 月	13 日	木		 		323
2014 年	2 月	14 日	金		 		324
2014 年	2 月	15 日	土		 		325
2014 年	2 月	16 日	日		 		326
2014 年	2 月	17 日	月		 		327
2014 年	2 月	18 日	火		 		328
2014 年	2 月	19 日	水		 		329
2014 年	2 月	20 日	木		 		330
2014 年	2 月	21 日	金		 		331
2014 年	2 月	22 日	土		 		332
2014 年	2 月	23 日	日		 		333
2014 年	2 月	24 日	月		 		334
2014 年	2 月	25 日	火		 		335
2014 年	2 月	26 日	水		 		336
2014 年	2 月	27 日	木		 		337
2014 年	2 月	28 日	金		 		338
		--	--		 		339
		--	--		 		340
		--	--		 		341
2014 年	3 月	1 日	土		 	欠勤	342
2014 年	3 月	2 日	日		 		343
2014 年	3 月	3 日	月		 		344
2014 年	3 月	4 日	火		 		345
2014 年	3 月	5 日	水		 		346
2014 年	3 月	6 日	木		 		347
2014 年	3 月	7 日	金		 		348
2014 年	3 月	8 日	土		 		349
2014 年	3 月	9 日	日		 		350
2014 年	3 月	10 日	月		 		351
2014 年	3 月	11 日	火		 		352
2014 年	3 月	12 日	水		 		353
2014 年	3 月	13 日	木		 		354
2014 年	3 月	14 日	金		 		355
2014 年	3 月	15 日	土		 		356
2014 年	3 月	16 日	日		 		357
2014 年	3 月	17 日	月		 		358
2014 年	3 月	18 日	火		 		359
2014 年	3 月	19 日	水		 		360
2014 年	3 月	20 日	木		 		361
2014 年	3 月	21 日	金		 		362
2014 年	3 月	22 日	土		 		363
2014 年	3 月	23 日	日		 		364
2014 年	3 月	24 日	月		 		365
2014 年	3 月	25 日	火		 		366
2014 年	3 月	26 日	水		 		367
2014 年	3 月	27 日	木		 		368
2014 年	3 月	28 日	金		 		369
2014 年	3 月	29 日	土		 		370
2014 年	3 月	30 日	日		 		371
2014 年	3 月	31 日	月		 		372

一応入れてみました。宜しくお願いします。
(月) 2014/04/10(木) 13:25


 元データは
 A列に「年」 B列に「月」 C列に「日」
 が入っていて、結果も「日」だけ表示されているのですよね?

 C7セルの式を
=IF(COUNTIFS('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$G$1:$G$373,$C$4,'[社長使用 評価表データ保存用(使用不可).xlsx]多'!$B$1:$B$373,$H$2&" 月")>=ROW(A1),
 INDEX('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$C$1:$C$373,SMALL(INDEX((('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$B$1:$B$373<>$H$2&" 月")+('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$G$1:$G$373<>$C$4))*9^9+ROW('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$A$1:$A$373),),ROW(A1))),"")
 でためしてみて下さい。
  
(HANA) 2014/04/10(木) 15:24

HANA様
毎週木曜にしか、この作業が出来ないのでお返事が大変遅れて申し訳ありません。
A列 年
B列 月
C列 日
D列 曜日
E列 天気
F列 休日を判定する★
G列 出勤状況 欠勤・遅刻・早退・忌引・出停(出勤停止)
H列 作業内容
上記の順番で入力されています。

結果はシリアル値で出ているので日付表示に直して表示しているので、年月日まで表示させる様にひとまずしています。
最初の質問の所に入力している内容で表示されています。
今試してみましたが、#VALUE!になってしまいます。

以下が最終的に表示させたい表です。

出勤状況 2013 年 4 月 氏名

検索条件 欠勤  検索条件 遅刻  検索条件 早退  検索条件 忌引  検索条件 出停

													 
月	日 		    月	日	 	    月	日	 	    月	日	 	    月	日
#VALUE!	#VALUE!		 4 月	2013/4/3		 4 月	2013/4/5		 4 月	2013/4/6		 4 月	2013/4/7
4 月	2013/4/2												
4 月	2013/4/4												
4 月	2013/4/8												
4 月	2013/5/1												
4 月	2013/6/1												
4 月	2013/7/1												
4 月	2013/8/1												
4 月	2013/9/1												
4 月	2013/10/1												
4 月	2013/11/1												
4 月	2013/12/1												
4 月	2014/1/1												
4 月	2014/2/1												
4 月	2014/3/1												
B7(月の部分)==IF(C7="","",$H$2)
C7(日の部分)==IF(COUNTIF('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$G:$G,$C$4)>=ROW('[社長使用 評価表データ保存用(使用不可).xlsx]多'!A1),INDEX('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$C:$C,LARGE(INDEX(('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$G$1:$G$373=$C$4)*ROW('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$A$1:$A$373),),COUNTIF('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$G:$G,$C$4)-ROW('[社長使用 評価表データ保存用(使用不可).xlsx]多'!A1)+1)),IF(C7=MONTH($H$2),C7,""))
C7は最初の質問の部分で出した数式です。
(月) 2014/04/17(木) 13:20

 >結果はシリアル値で出ているので日付表示に直して表示しているので
 でしたら
=IF(SUMPRODUCT(('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$G$4:$G$373=$C$4)*(MONTH('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$B$4:$B$373)=$H$2))>=ROW(A1),
INDEX('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$C$4:$C$373,SMALL(INDEX(((MONTH('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$B$4:$B$373)<>$H$2)+
('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$G$4:$G$373<>$C$4))*9^9+ROW('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$A$4:$A$373),),ROW(A1))-3),"")

 でやってみて下さい。
  
(HANA) 2014/04/17(木) 13:54

HANA様

試してみたのですが、またまた#VALUE!になってしまいました。
数式を入力してEnterを押したら、値の更新になってファイルを選ぶ様に指示されます。
その際、社長使用 評価表データ保存用(使用不可).xlsxを選んで良いのですよね?
すいません。初歩的な質問で…。
(月) 2014/04/24(木) 09:51


 まずは同じブックでの式を作ってみるんのが良いんじゃないでしょうか。
 式も短くなりますし
 >値の更新になってファイルを選ぶ様に指示されます。 
 こういう事からも一旦解放されますので。
  
(HANA) 2014/04/24(木) 10:52

HANA様
お返事が大変遅くなりまして申し訳ありません。
今、同じブックで試してみたのですが、やはり#VALUE!になってしまいました。
(月) 2014/05/15(木) 14:07

 $B$4:$B$373に、数式で「""」等が返されていますか?
 その場合 MONTH('[社長使用 評価表データ保存用(使用不可).xlsx]多'!$B$4:$B$373) 部分が
 エラーになってしまいます。

 シリアル値が必要な場合はA列を参照することにして
 B列は =IF(A4="","",MONTH(A4)) と言った式で月に変更。

 該当の数式は '[社長使用 評価表データ保存用(使用不可).xlsx]多'!$B$4:$B$373=$H$2
 の様に変更してみるのはどうでしょう。
  
(HANA) 2014/05/16(金) 08:32

HANA様
度々、お返事が遅くなりまして申し訳ありません。
重ねて質問ばかりで申し訳ありません。

>$B$4:$B$373に、数式で「""」等が返されていますか?

これは、'[社長使用 評価表データ保存用(使用不可).xlsx]多'!$B$4:$B$373の部分と理解したら宜しいでしょうか?
B4 =IF(C4="--","",'[社長使用 評価表1.xlsx]多'!$D$2)を入力しています。

>B列は =IF(A4="","",MONTH(A4)) と言った式で月に変更。
集計をしている表のB列で宜しいですか?
入力をしてみましたら、空白で返す様になっているので空白になってしまいました。
MONTH(A4)を月を指定しているH2セルにしたら、1月と出てきました。

該当の数式を同じブックで書き直す場合は'[社長使用 評価表データ保存用(使用不可).xlsx]多'!この部分を多!に変更するだけで大丈夫でしょうか?

お時間ばかり取らせて申し訳ありません。
いつも、ありがとうございます。

(月) 2014/05/27(火) 13:31


 >集計をしている表のB列で宜しいですか? 
 '[社長使用 評価表データ保存用(使用不可).xlsx]多'!
 のB列の事でしたが。

 >'[社長使用 評価表データ保存用(使用不可).xlsx]多'!$B$4:$B$373の部分と理解したら宜しいでしょうか? 
 >B4 =IF(C4="--","",'[社長使用 評価表1.xlsx]多'!$D$2)を入力しています。
 でしたらちょっと話が変わってきてしまいますね。

 この式だと、どこまでコピーしても '[社長使用 評価表1.xlsx]多'!$D$2 のセルしか参照しませんが
 それで良いのでしょうか?
 それとも、記載ミス?

 また、'[社長使用 評価表1.xlsx]多'!$D$2 のセルには何が入力されていますか?

 >該当の数式を同じブックで書き直す場合は〜〜
 それで良いと思いますが、先に方針を決めた方がよさそうですので
 状況を詳しく教えて下さい。
  
(HANA) 2014/05/27(火) 13:58

HANA様
'[社長使用 評価表データ保存用(使用不可).xlsx]多'!のB列ですね。(上の一年分を縦にずらーと並んでいる表のB列になります。)
=IF(A4="","",MONTH(A4))を入力しましたら、7月と表示されます。

=IF(C4="--","",'[社長使用 評価表1.xlsx]多'!$D$2)には4と表示形式で月を入れて入力されています。
わざわざ毎日<月>が表示される必要が無いため一つで表示させています。
印刷も出来る様に一カ月毎の勤怠表兼計算表を作っています。
形が少し崩れてしまっていますが、このような感じで作っています。

A列縦横ともに空列

2014 年 4 月 出勤簿                                                    基本就業時間 8:00 〜 17:00 時給 \1,000

													早出時間		5:00	〜	8:00		\1,000
氏名	○○ ○○												残業時間		18:00	〜	23:00		\1,250
													早朝・深夜業時間		23:00	〜	5:00		\1,300
B	C	D	E	F	G〜H		I	J	K	L	M	N	O	 P	Q	R	S	T	U
日	曜日	天気	休日★	出勤状況	作業内容		時刻			実 時 刻		実働時間			実働   時間	通常     勤務	早出     残業分	早朝     深夜分	合計
	曜日						出社時刻	退社時刻	休憩時間	始業	終業	基本	"早出残業"	"早朝深夜"			備考
1 日	火		 		事務		6:30	18:00	1:00	6:30	18:00	8:00	2:30		10:30	\8,000	\3,125		\11,125
2 日	水		 		事務		6:30	19:00	1:00	6:30	19:00	8:00	3:30		11:30	\8,000	\4,375		\12,375
3 日	木		 		事務		6:30	18:00	1:00	6:30	18:00	8:00	2:30		10:30	\8,000	\3,125		\11,125
4 日	金	晴	 		事務		6:30	18:00	1:00	6:30	18:00	8:00	2:30		10:30	\8,000	\3,125		\11,125
5 日	土		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
6 日	日		★		6														
7 日	月		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
8 日	火		 		事務		6:00	20:00	1:00	6:00	20:00	8:00	5:00		13:00	\8,000	\6,250		\14,250
9 日	水		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
10 日	木		 		事務		6:00	18:30	1:00	6:00	18:30	8:00	3:30		11:30	\8,000	\4,375		\12,375
11 日	金		 		事務		6:00	17:00	1:00	6:00	17:00	8:00	2:00		10:00	\8,000	\2,500		\10,500
12 日	土		 		事務		6:00	17:30	1:00	6:00	17:30	8:00	2:30		10:30	\8,000	\3,125		\11,125
13 日	日		★		13														
14 日	月				事務		6:00	18:30	1:00	6:00	18:30	8:00	3:30		11:30	\8,000	\4,375		\12,375
15 日	火		 		事務		5:30	19:00	1:00	5:30	19:00	8:00	4:30		12:30	\8,000	\5,625		\13,625
16 日	水		 		事務		5:30	18:00	1:00	5:30	18:00	8:00	3:30		11:30	\8,000	\4,375		\12,375
17 日	木		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
18 日	金		 		事務		6:00	17:30	1:00	6:00	17:30	8:00	2:30		10:30	\8,000	\3,125		\11,125
19 日	土		 		事務		6:00	18:00	1:00	6:00	18:00	8:00	3:00		11:00	\8,000	\3,750		\11,750
20 日	日		★		20														
21 日	月		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
22 日	火		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
23 日	水		 		諸富東		6:00	18:00	1:00	6:00	18:00	8:00	3:00		11:00	\8,000	\3,750		\11,750
24 日	木		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
25 日	金		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
26 日	土		 		事務		6:00	18:00	1:00	6:00	18:00	8:00	3:00		11:00	\8,000	\3,750		\11,750
27 日	日		★		27														
28 日	月		 		事務		6:00	18:00	1:00	6:00	18:00	8:00	3:00		11:00	\8,000	\3,750		\11,750
29 日	火		 		事務		6:30	17:00	1:00	6:30	17:00	8:00	1:30		9:30	\8,000	\1,875		\9,875
30 日	水		 		事務		6:30	17:30	1:00	6:30	17:30	8:00	2:00		10:00	\8,000	\2,500		\10,500
 --	--		 		31														
出勤数		26				休日数	4	欠勤数	0	合     計		208:00	85:30		293:30	\208,000 	\106,875 		\314,875 
遅刻		0				早退	0	忌引	0										

(月) 2014/05/27(火) 15:46


 >'[社長使用 評価表データ保存用(使用不可).xlsx]多'!のB列ですね。
 そうですね。

 それでもう一度確認ですが、A列はシリアル値を表示形式で変更してますか?
 それとも、もともと年の値のみが入る様になっていますか?
  ・・・これまでの話の中で、シリアル値が入っていると判断していたのですが?

 それから
 '[社長使用 評価表データ保存用(使用不可).xlsx]多'!のB列
 は現在
 >B4 =IF(C4="--","",'[社長使用 評価表1.xlsx]多'!$D$2)を入力しています。
 と言う事ですが、B5等はどの様な式でしょうか?

 '[社長使用 評価表データ保存用(使用不可).xlsx]多'の話です。

 ちなみに↑に新たに載せてもらったものは、どのブックのどのシートの事ですか?
  
(HANA) 2014/05/27(火) 16:11

HANA様
お世話かけます。

それでもう一度確認ですが、A列はシリアル値を表示形式で変更してますか?
 それとも、もともと年の値のみが入る様になっていますか?
  ・・・これまでの話の中で、シリアル値が入っていると判断していたのですが?
確認してみたら'[社長使用 評価表1.xlsx]多'!$B$2の元データは数値による表示形式での年の指定にしていました。すみません。勘違いみたいでした。
↓の年の部分です。
          >'[社長使用 評価表データ保存用(使用不可).xlsx]多'!のB列
           は現在
           >B4 =IF(C4="--","",'[社長使用 評価表1.xlsx]多'!$D$2)を入力しています。
            と言う事ですが、B5等はどの様な式でしょうか?
           =IF(C○="--","",'[社長使用 評価表1.xlsx]多'!$D$2)
              ↑の部分が変わっています。
             C4・C5・C6……と変わっていきます。
             違う月になるとC4も$D$2もセルが変わります。
            毎月の31日などが無かったら--で表示する様にしているので見易くなる様に上記の数式を入れています。
            ↓この表の月部分です。

 '[社長使用 評価表データ保存用(使用不可).xlsx]多'
年 月 日 曜日 天気 休日 ★ 出勤状況 作業内容 
2013 年 4 月 1 日 月   欠勤 1 
 2013 年 4 月 2 日 火   欠勤 2 
 2013 年 4 月 3 日 水   遅刻 3 
 2013 年 4 月 4 日 木 晴   欠勤 4 
 2013 年 4 月 5 日 金   早退 5 
 2013 年 4 月 6 日 土   忌引 6 
 2013 年 4 月 7 日 日   出停 7 
 2013 年 4 月 8 日 月   欠勤 8 
 2013 年 4 月 9 日 火   9 
 2013 年 4 月 10 日 水   10 
 2013 年 4 月 11 日 木   11 
 2013 年 4 月 12 日 金   12 
 2013 年 4 月 13 日 土   13 
 2013 年 4 月 14 日 日   14 
 2013 年 4 月 15 日 月   15 
 2013 年 4 月 16 日 火   16 
 2013 年 4 月 17 日 水   17 
 2013 年 4 月 18 日 木   18 
 2013 年 4 月 19 日 金   19 
 2013 年 4 月 20 日 土   20 
 2013 年 4 月 21 日 日   21 
 2013 年 4 月 22 日 月   22 
 2013 年 4 月 23 日 火   23 
 2013 年 4 月 24 日 水   24 
 2013 年 4 月 25 日 木   25 
 2013 年 4 月 26 日 金   26 
 2013 年 4 月 27 日 土   27 
 2013 年 4 月 28 日 日   28 
 2013 年 4 月 29 日 月   29 
 2013 年 4 月 30 日 火   30 
       --  --
この表↓は↑の元データです。'[社長使用 評価表1.xlsx]多'!です。
(このデータをリンクで↑の表にリンクで貼り付けています。)
2014 年 4 月 出勤簿                                                    基本就業時間 8:00 〜 17:00 時給 \1,000 

													早出時間		5:00	〜	8:00		\1,000
氏名	○○ ○○												残業時間		18:00	〜	23:00		\1,250
													早朝・深夜業時間		23:00	〜	5:00		\1,300
B	C	D	E	F	G〜H		I	J	K	L	M	N	O	 P	Q	R	S	T	U
日	曜日	天気	休日★	出勤状況	作業内容		時刻			実 時 刻		実働時間			実働   時間	通常     勤務	早出     残業分	早朝     深夜分	合計
	曜日						出社時刻	退社時刻	休憩時間	始業	終業	基本	"早出残業"	"早朝深夜"			備考
1 日	火		 		事務		6:30	18:00	1:00	6:30	18:00	8:00	2:30		10:30	\8,000	\3,125		\11,125
2 日	水		 		事務		6:30	19:00	1:00	6:30	19:00	8:00	3:30		11:30	\8,000	\4,375		\12,375
3 日	木		 		事務		6:30	18:00	1:00	6:30	18:00	8:00	2:30		10:30	\8,000	\3,125		\11,125
4 日	金	晴	 		事務		6:30	18:00	1:00	6:30	18:00	8:00	2:30		10:30	\8,000	\3,125		\11,125
5 日	土		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
6 日	日		★		6														
7 日	月		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
8 日	火		 		事務		6:00	20:00	1:00	6:00	20:00	8:00	5:00		13:00	\8,000	\6,250		\14,250
9 日	水		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
10 日	木		 		事務		6:00	18:30	1:00	6:00	18:30	8:00	3:30		11:30	\8,000	\4,375		\12,375
11 日	金		 		事務		6:00	17:00	1:00	6:00	17:00	8:00	2:00		10:00	\8,000	\2,500		\10,500
12 日	土		 		事務		6:00	17:30	1:00	6:00	17:30	8:00	2:30		10:30	\8,000	\3,125		\11,125
13 日	日		★		13														
14 日	月				事務		6:00	18:30	1:00	6:00	18:30	8:00	3:30		11:30	\8,000	\4,375		\12,375
15 日	火		 		事務		5:30	19:00	1:00	5:30	19:00	8:00	4:30		12:30	\8,000	\5,625		\13,625
16 日	水		 		事務		5:30	18:00	1:00	5:30	18:00	8:00	3:30		11:30	\8,000	\4,375		\12,375
17 日	木		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
18 日	金		 		事務		6:00	17:30	1:00	6:00	17:30	8:00	2:30		10:30	\8,000	\3,125		\11,125
19 日	土		 		事務		6:00	18:00	1:00	6:00	18:00	8:00	3:00		11:00	\8,000	\3,750		\11,750
20 日	日		★		20														
21 日	月		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
22 日	火		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
23 日	水		 		諸富東		6:00	18:00	1:00	6:00	18:00	8:00	3:00		11:00	\8,000	\3,750		\11,750
24 日	木		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
25 日	金		 		事務		6:00	19:00	1:00	6:00	19:00	8:00	4:00		12:00	\8,000	\5,000		\13,000
26 日	土		 		事務		6:00	18:00	1:00	6:00	18:00	8:00	3:00		11:00	\8,000	\3,750		\11,750
27 日	日		★		27														
28 日	月		 		事務		6:00	18:00	1:00	6:00	18:00	8:00	3:00		11:00	\8,000	\3,750		\11,750
29 日	火		 		事務		6:30	17:00	1:00	6:30	17:00	8:00	1:30		9:30	\8,000	\1,875		\9,875
30 日	水		 		事務		6:30	17:30	1:00	6:30	17:30	8:00	2:00		10:00	\8,000	\2,500		\10,500
 --	--		 		31														
出勤数		26				休日数	4	欠勤数	0	合     計		208:00	85:30		293:30	\208,000 	\106,875 		\314,875 
遅刻		0				早退	0	忌引	0										

(月) 2014/05/29(木) 11:59


 結局シリアル値が入っているんですよね?
 でしたらやはりB列の式を 
 =IF(A4="","",MONTH(A4))
 に変えてもらうのが簡単だと思いますが。

 小さなサンプルでの数式のイメージを書いてみますね。
     [A]  [B]  [C]  [D]  [E]  [F]  [G]    [H]       [I]       [J]       [K]       [L]
[ 1] 月   日  項目       月  項目       A2<>$E$2  C2<>$F$2   H2+I2     J2*9^9  K2+ROW(A2)
[ 2]  4    1              4   ●         FALSE      TRUE            1 387420489 387420491
[ 3]  4    5   ●                        FALSE     FALSE            0         0         3 ←1番目に小さい
[ 4]  4   10             日              FALSE      TRUE            1 387420489 387420493
[ 5]  4   15   ●         5              FALSE     FALSE            0         0         5 ←2番目に小さい
[ 6]  4   20             15              FALSE      TRUE            1 387420489 387420495
[ 7]  4   25   ●        25              FALSE     FALSE            0         0         7 ←3番目に小さい
[ 8]  4   30                             FALSE      TRUE            1 387420489 387420497
[ 9]  5    1                              TRUE      TRUE            2 774840978 774840987
[10]  5    5                              TRUE      TRUE            2 774840978 774840988
[11]  5   10                              TRUE      TRUE            2 774840978 774840989
[12]  5   15                              TRUE      TRUE            2 774840978 774840990
[13]  5   20   ●                         TRUE     FALSE            1 387420489 387420502
[14]  5   25   ●                         TRUE     FALSE            1 387420489 387420503
[15]  5   30                              TRUE      TRUE            2 774840978 774840993
 (A:B,Eの表示形式は 標準 です。)

 A列とD2セルの値が等しく、C列とF2セルの値が等し B列の値を
 E5セルから下方向に列挙します。

 E5セルの数式は
=IF(COUNTIFS($A$2:$A$20,$E$2,$C$2:$C$20,$F$2)<ROW(A1),"",INDEX($B$2:$B$20,SMALL(INDEX((($A$2:$A$20<>$E$2)+($C$2:$C$20<>$F$2))*9^9+ROW($A$2:$A$20),),ROW(A1))-1))
 です。

 H列以降に展開しているのが↓部分になります。
 INDEX((($A$2:$A$20<>$E$2)+($C$2:$C$20<>$F$2))*9^9+ROW($A$2:$A$20),)
         ~~~~~~H列~~~~~~~   ~~~~~~I列~~~~~~~  
        (================J列=================)*9^9
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~K列~~+ROW($A$2:$A$20)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~L列~~~~~~~~ 
 1行目に表示してあるのが、各2行目に入力してある数式です。

 その後、SMALL関数でL列の中から小さい順に値を取得し
 INDEX関数と組み合わせて、目的の値を取得しています。

 M列以降にでも
 M2 =SMALL($L$2:$L$20,ROW(A1))
 N2 =M2-1
 O2 =INDEX($B$2:$B$20,N2)
 の式を入れて、下にフィルドラッグして確認して下さい。
  
(HANA) 2014/05/29(木) 15:47

HANA様

大変ご無沙汰しております。
上記の件は、お世話になりました。
私の知識不足・理解不足でうまくいきませんでしたが、
違う方法と言いましても2014/05/29(木) 11:59 この日に載せている下の部分の表の一番下の出勤数・欠勤数等をリンクして表示する様に致しました。
日付の表示は、申し訳ありませんでしたが諦めました。

今までありがとうございました。

図々しくて申し訳ありませんが、違う事に関しての質問なんですけれどもお願いできますでしょうか?

2014/05/29(木) 11:59 この日に載せている下の部分の表の関数(以前質問していた物とは関係ありません)について教えていただけますでしょうか?
以下 質問です。

実働時間の基本・早出残業・深夜残業の計算式に関してです。

N9 基本の計算式は =IF(OR(I9="",J9=""),"",IF(V9,0,MIN(VALUE(J9-I9-K9),VALUE("8:00")))) 表示形式 [h]:mm
O9 早出残業の計算式は =IF(OR(I9="",J9=""),"",(IF(V9,0,MAX(VALUE(J9-I9-K9-"8:00"),VALUE(0))))) 表示形式 [h]:mm
P9 深夜残業の計算式は =IF(OR(I9="",J9=""),"",(MAX(0,MIN(J9*1,"29:00")-MAX(I9,"22:00"))+MAX(0,MIN(J9*1,"5:00")-MAX(I9,"0:00")))) 表示形式 [h]:mm;;
Q9 実働労働時間(一日の労働時間)の計算式は =IF(OR(I9="",J9=""),"",M9-L9-K9) 表示形式 [h]:mm;;

この計算式で行っているのですが早出残業に深夜残業の時間分まで含まれて計算されているので、給料計算が上手く行えません。

給料の計算式は
R9 通常勤務(基本) =IF(OR(I9="",J9=""),"",IF(OR(AND(E9<>"★")),ROUND(N9*24*$U$2,-1),ROUND(N9*24*($U$2*1.35),-1)))
S9 早出残業分 =IF(OR(I9="",J9=""),"",IF(OR(AND(E9<>"★")),ROUND(O9*24*$U$4,0),ROUND(O9*24*($U$2*1.35),0)))
T9 早朝深夜分 =IF(OR(I9="",J9=""),"",IF(OR(AND(E9<>"★")),ROUND(P9*24*$U$5,0),ROUND(P9*24*($U$2*1.6),0)))

実働時間を 基本+残業+深夜=一日の勤務時間で計算して、その月の総勤務時間を出すのに
Q40 =IF(SUM(Q9:Q39)=SUM(N40:P40),SUM(Q9:Q39),"エラー")を入力しているのですが、
4月に関しては深夜残業が無かったのでエラーは出なかったのですが、深夜残業がある月ではエラーが出でしまいまいた。
SUM(N40:P40) この計算式のP列(早朝深夜の列)までをO列までにするとエラーが出ませんでした。
と言う事は労働時間の部分が間違えているのだろうという結果になったのですが、どう計算式を直したらよいのか分かりません。

宜しければ、力を貸していただけませんでしょうか?

(月) 2014/07/17(木) 13:45


 >O9 早出残業の計算式は =IF(OR(I9="",J9=""),"",(IF(V9,0,MAX(VALUE(J9-I9-K9-"8:00"),VALUE(0))))) 

 上の計算式には、早朝深夜時間が含まれています。なので、それを除く必要があります。

 掲示された数式が正しいとするならこれ(↓)、ただし、V9に何が入っているのか分からないのでそこはノータッチ。
          
 O9セル =IF(OR(I9="",J9=""),"",IF(V9,0,MAX(J9-I9-K9-"8:00"-P9,0)))
                              ↑
                              右隣りを引く

 <一般論としての疑問>
 掲示された表は、出社時刻=始業、退社時刻=終業 になっているので、
 今は問題が生じていませんが、もし違うことがある月もあるなら、そこからも差異が発生します。

  行  ____I____  ____J____  ____K____  __L__  __M__
   8  出社時刻   退社時刻   休憩時間   始業   終業 
   9  6:30       18:00      1:00       6:30   18:00

 ※いつも同じだとしたら、何故、そんな無駄なデータをもう一つ存在させるのか、新たな疑問が生じます。

(半平太) 2014/07/17(木) 16:42


半平太様

初めまして。
今回、回答して頂きありがとうございます。
並びにお返事が大変遅くなって申し訳ありません。

V9に関しては、何か入っていると言う訳ではありません。
(いわゆる作業列?理解出来ていなくてごめんなさい。)

今回作っている勤怠表は、どこかのサイトで引っ張ってきた物を自分なりに変更して作っています。

実際、理解出来ていない関数もあります。

元々作ってある表では、出社時刻が6:03だったら6:05にする様な感じになっていたんだと思います。

なので出社時刻と始業時刻があるのだと思いますが、私にはどこをどう削除していい物なのか分からなくて
現状のまま使用している所です。

当方では、出社時刻と始業時刻が変わる事はありません。

半平太様の回答して頂いた関数を入力しました所、上手くいっている様です。

他の従業員のsheetもあるので試してみたいと思います。
(月) 2014/07/31(木) 10:20


半平太様

続けての投稿すみません。

始業時刻 L9=IF(OR(I9="",J9=""),"",CEILING(I9,TIME(0,5,0)))
終業時刻 M9=IF(OR(I9="",J9=""),"",(FLOOR(J9,TIME(0,5,0))))
が入力されています。

たまに出社時刻に8:00と入力しても始業時刻が5分足されて8:05となってしまっている時があるのですが
始業時刻のCEILING(I9,TIME(0,5,0)
                   ↑ここの5を0に変更すれば解決するものでしょうか?

並びに終業時刻の(FLOOR(J9,TIME(0,5,0)これも同様に5を0に変更したら宜しいでしょうか?
質問ばかりで申し訳ありません。
(月) 2014/07/31(木) 10:38


 >たまに出社時刻に8:00と入力しても始業時刻が5分足されて8:05となってしまっている時があるのですが

 考えられる原因は小数演算誤差なんですけど、
 8:00が手入力だとすると経験則に反します。

 いまだかって、「手入力」の時刻データでその誤差が出たことはありません。
 なので、もう少し慎重な検証が必要です。

 本当に手入力データですか???

 ・・・と問いかけてみたものの、エクセル初心者にそんな問答を仕掛けるのは
 場違いなので、以下の質問にだけお答えします。

 >始業時刻のCEILING(I9,TIME(0,5,0) 
 >             ↑ここの5を0に変更すれば解決するものでしょうか? 

 それでは解決しません。実務的にはほんの僅かな調整値(たとえば1秒)を加減します。
 つまり、
  L9セル =IF(OR(I9="",J9=""),"",CEILING(I9-"0:00:01",TIME(0,5,0)))
  M9セル =IF(OR(I9="",J9=""),"",FLOOR(J9+"0:00:01",TIME(0,5,0)))

(半平太) 2014/07/31(木) 12:22


半平太様

またまた回答ありがとうございます。

手入力ばかりですが、たまに前日と同じ出社・退社時刻ならばドラッグしてコピーしています。
                                       ↑これが問題でしょうか?(汗)

M9セル =IF(OR(I9="",J9=""),"",FLOOR(J9+"0:00:01",TIME(0,5,0)))を入力しました所、TRUEになってしまいました。
(月) 2014/07/31(木) 13:36


半平太様

M9セル =IF(OR(I9="",J9=""),"",FLOOR(J9+"0:00:01",TIME(0,5,0)))を入力しました所、TRUEになってしまいました。
↑解決いたしました。
すいません。
(月) 2014/07/31(木) 13:49


 >手入力ばかりですが、たまに前日と同じ出社・退社時刻ならばドラッグしてコピーしています。 
 >                                        ↑これが問題でしょうか?(汗) 

 それは問題ないと思います。

 将来そのトラブルが生じた時、その状態をキープして質問してください。

(半平太) 2014/07/31(木) 14:09


半平太様
>将来そのトラブルが生じた時、その状態をキープして質問してください。
 もし、トラブルが起きた場合は、状態をキープして質問いたします。

またまた質問で申し訳ありません。
ある方のひと月だけ計算がエラーになってしまいます。
2014/07/17(木) 13:45 この時に質問した部分なんですが…。

実働時間の基本・早出残業・深夜残業の計算式に関してです。

実働時間を 基本+残業+深夜=一日の勤務時間で計算して、その月の総勤務時間を出すのに
Q40 =IF(SUM(Q9:Q39)=SUM(N40:P40),SUM(Q9:Q39),"エラー")で計算しています。

出社時刻・退社時刻・休憩時間を手入力で入力しました。

他のセルの関数は 半平太様からの関数を入力しています。

なぜ、エラーになるのか分からず戸惑っています。

一応、SUM(Q9:Q39)だけ、SUM(N40:P40)だけで計算をしてみた所、結果は同じなんです。

同じなんですが、計算式を=IF(SUM(Q9:Q39)=SUM(N40:P40),SUM(Q9:Q39),"エラー")にするとエラーになってしまいます。

原因として何が考えられるのでしょうか?
(月) 2014/07/31(木) 14:33


 >原因として何が考えられるのでしょうか?

 それぞまさしく小数演算誤差だと思います。

 >一応、SUM(Q9:Q39)だけ、SUM(N40:P40)だけで計算をしてみた所、結果は同じなんです。
 同じように見えて、厳密には同じではないのです。

 例えばどこかの空いたセルに下式を入力してみてください
   =(SUM(Q9:Q39)=SUM(N40:P40))
 ※両サイドに意味不明の括弧があるのを忘れずに書いてください。

 それでもイコール(True)になりましたか?

 せっかく検算を入れたのに、却って混乱することになりましたね。

 >計算式を=IF(SUM(Q9:Q39)=SUM(N40:P40),SUM(Q9:Q39),"エラー")
   ↓
  計算式を=IF(ROUND(SUM(Q9:Q39),10)=ROUND(SUM(N40:P40),10),SUM(Q9:Q39),"エラー")

 と変更してみたら直りませんか?
 ※小数点以下ずーっと深いところで丸めを行っています。(微妙な違いを無視させるため)

(半平太) 2014/07/31(木) 16:44


半平太様

お返事ありがとうございます。
>例えばどこかの空いたセルに下式を入力してみてください

   =(SUM(Q9:Q39)=SUM(N40:P40))
を入力してみた所、(True)になりました。

しかし、エラーが出る合計のセルに半平太様の書かれた
=IF(ROUND(SUM(Q9:Q39),10)=ROUND(SUM(N40:P40),10),SUM(Q9:Q39),"エラー")を入力しました所エラーにはなりませんでした。

とても細かい所で不具合が出るものなのですね。
ありがとうございました。

度々質問ばかりして申し訳ありません。
上の質問をさせていただいた表を使って集計をしているのですが、

      以下↓各月の分があります。

	2013 年	4 月	出勤状況								残業時間に		
											含まれている		
	氏名		出勤 日数	欠勤	遅刻	早退	忌引	休日 出勤	基本時間	残業時間	(深夜残業)	総勤務時間	
	○○   		25						198:00	92:00		290:00	
	××   		26						206:00	58:40		264:40	
	△△       	21						168:00	32:00		200:00	
	■■   		21						165:00	29:00		194:00	
	□□   		23						184:00	40:10		224:10	
	●●   		21						167:30	30:00		197:30	
	◎◎     											0:00	
	▲▲   		20	1					160:00	35:00		195:00	
	◆◆   		23				2		182:00	36:40		218:40	
	▽▽   		24						144:00			144:00	
	◎▽   		2						9:00			9:00	
	□◎   		4						13:30			13:30	

	2014 年	総出勤状況										
											含まれている	
	氏名		出勤 日数	欠勤	遅刻	早退	忌引	休日 出勤	基本時間	残業時間	(深夜残業)	総勤務時間
	○○    		303					5	2401:30	1123:35	10:30	3535:35
	××   		298					8	2281:30	811:30	4:00	3097:00
	△△   		289					6	2279:30	710:10	1:30	2991:10
	■■   		286	1				4	2214:30	684:00	7:10	2905:40
	□□   		287	1				7	2263:00	713:50	2:20	2979:10
	●●   		286	1				8	2254:30	698:55	1:30	2954:55
    ◎◎								#VALUE!	#VALUE!		#VALUE!
	▲▲   		288	1		1		4	2102:15	716:20	13:40	2832:15
	◆◆   		280	3			2	1	2127:15	757:15	9:35	2894:05
	▽▽   		245					1	1341:25	#VALUE!		#VALUE!
	◎▽   		54						252:30	#VALUE!		#VALUE!
	□◎   		36						168:30	#VALUE!		#VALUE!

◎◎の方は時間等が一切入力されていません。
(作っているだけの状況です。)
▽▽・◎▽の方は年に1度だけ残業があったのですが、計算が上手く行えません。
□◎の方は残業は一切ありません。

○○の方の数式を入力しておきます。
各月の計算に関しては、2014/05/29(木) 11:59の表の日数等をリンクして貼り付けています。

出勤数   D209=D5+D22+D39+D56+D73+D90+D107+D124+D141+D158+D175+D192
欠勤数   E209=E5+E22+E39+E56+E73+E90+E107+E124+E141+E158+E175+E192
遅刻数   F209=F5+F22+F39+F56+F73+F90+F107+F124+F141+F158+F175+F192
早退数   G209=G5+G22+G39+G56+G73+G90+G107+G124+G141+G158+G175+G192
忌引数   H209=H5+H22+H39+H56+H73+H90+H107+H124+H141+H158+H175+H192
休日出勤数 I209=I5+I22+I39+I56+I73+I90+I107+I124+I141+I158+I175+I192
基本時間数 =IF(SUM(多!$N$40+多!$AJ$40+多!$N$82+多!$AJ$82+多!$N$124+多!$AJ$124+多!$N$166+多!$AJ$166+多!$N$208+多!$AJ$208+多!$N$250+多!$AJ$250)=SUM(J5,J22,J39,J56,J73,J90,J107,J124,J141,J158,J175,J192),SUM(J5,J22,J39,J56,J73,J90,J107,J124,J141,J158,J175,J192),"エラー")
残業時間数 J209=多!O40+多!AK40+多!O82+多!AK82+多!O124+多!AK124+多!O166+多!AK166+多!O208+多!AK208+多!O250+多!AK250
深夜残業数 K209=SUM(L5,L22,L39,L56,L73,L90,L107,L124,L141,L158,L175,L192)
総勤務時間数 L209=IF(SUM(J209:L209)=SUM(M5,M22,M39,M56,M73,M90,M107,M124,M141,M158,M175,M192),SUM(M5,M22,M39,M56,M73,M90,M107,M124,M141,M158,M175,M192),"エラー")

(月) 2014/08/07(木) 14:32


 何がどうなっているのかフォローする気力がないです。 <m(__)m>

 参考になりそうな事を一つだけ書いておきます。

 >○○の方の数式を入力しておきます。 
 > 各月の計算に関しては、2014/05/29(木) 11:59の表の日数等をリンクして貼り付けています。 
 >出勤数   D209=D5+D22+D39+D56+D73+D90+D107+D124+D141+D158+D175+D192 

 この数式は長すぎますし、間違いも起きやすいです。

 下図の4月と5月の集計をする場合、

 B15セルに =SUMIF($A$2:$A$13,$A15,B$2:B$13)
 と入力して、右方向、下方向にコピーすれば、一つの式で全部計算できます。

 <サンプル>
  行  __A__  __B__  _C_  ____D____
   1  4月                         
   2  氏名   出勤        日数(D) 
   3  ○○      4              11 
   4  ××      5              12 
   5  △△      6              13 
   6                              
   7  5月                         
   8  氏名   出勤        日数(D) 
   9  ○○      5              20 
  10  ××      5              20 
  11  △△      5              20 
  12                              
  13  総計                        
  14  氏名   出勤                 
  15  ○○      9     0        31 
  16  △△     11     0        33 
  17  ●●      0     0         0 

 ※「××さん」の分は、わざと集計表には載せておりません。
  (順番に関係なく集計できることを示すため)

(半平太) 2014/08/09(土) 20:01


半平太様

お返事遅くなりまして申し訳ありません。
並びに返信ありがとうございます。

何をどう伝えたら、こちらの質問の内容を理解していただけるのか分からなくて、色々と書いてしまいました。
すいません。

半平太様の書かれた
>B15セルに =SUMIF($A$2:$A$13,$A15,B$2:B$13)を、こちらの表と合う様に変更しましたところ上手くいきました。
ありがとうございました。

(月) 2014/08/21(木) 10:12


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.