毎日の体温測定を作成していて

質問

いつもお世話になります。
WIN7 EXCELL2010 です。

ご指導いただきたいのは添付図の、
(1) を(2)　のようにしたいのです。
例えば
(1) のB6 B9のように分けないで(2)のように

以下の数式が
B6 =IF(ROW(B1)>COUNTIF(記入!$B$1:$B100,">="&$C$4)-COUNTIF(記入!$B$1:$B100,">="&DATE(YEAR($C$4),MONTH($C$4)+1,1)),"",INDEX(記入!$D$1:$D100,IF(MONTH($C$4)=1,2+ROW(B1)-1,IF(COUNTIF(記入!$B$1:$B100,$C$4)>0,MATCH($C$4,記入!$B$1:$B100,0)+ROW(B1)-1,MATCH($C$4,記入!$B$1:$B100,1)+ROW(B1)))))

C6　　=IF(OR($B6="",COUNTIF(記入!$F$1:$F$100,C$4&$B6)=0),"",INDEX(記入!$L$1:$L$100,MATCH(C$4&$B6,記入!$F$1:$F$100,0)))

多分ですが　C6　のなにかをとおもって色々試しましたがわかりませんでした。
誠に恐縮ですがご指導をよろしくお願いします。

回答No.4

tom04 · ベストアンサー

No.1です。
補足に
＞体温を測定した人のみを当月のシートのみに反映したいのですが・・・
とありますが、体温を測定した人は必ず「記入」SheetのA列に日付がある！というコトですよね？
それから1日に2度の測定はない！という前提とします。

画像をよく見ると各月のSheetがあるようなので、それぞれのSheetに関数が入っているのでしょうか？

今回の質問は各月のSheetのB列に重複なしで氏名を表示するのが主たる目的だと思いますので、
VBAでやってみました。

各月のSheetが選択されるとそのSheetのB6セル以降に名前を表示するようにしています。

Alt＋F11キー　→　画面左側の「This Workbook」をダブルクリック　→　VBE画面に
↓のコードをコピー＆ペーストして、各月のSheetを選択してみてください。

Private Sub Workbook_SheetActivate(ByVal Sh As Object) 'この行から
Dim i As Long, k As Long, wS As Worksheet
Set wS = Worksheets("記入")

If ActiveSheet.Name = "記入" Then
Exit Sub
Else
Application.ScreenUpdating = False
k = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
If k > 5 Then
ActiveSheet.Range(Cells(6, 2), Cells(k, 2)).ClearContents
End If
For i = 2 To wS.Cells(Rows.Count, 2).End(xlUp).Row
If WorksheetFunction.CountIf(ActiveSheet.Columns(2), wS.Cells(i, 4)) = 0 _
And Month(wS.Cells(i, 2)) & "月" = ActiveSheet.Name Then
ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1) = wS.Cells(i, 4)
End If
Next i
Application.ScreenUpdating = True
End If
End Sub 'この行まで

※　各Sheet名は　12月　のように　半角数値＋月　となっている前提です。
※　年が違った場合でもその月に表示されてしまいます。
（2012年1月・2013年1月　←どちらも同じ月として表示される）

※　仮に各SheetのB列に数式が入っている場合はすべて消えてしまいますので、
別BOOKで試してみてください。

B列だけ操作するようにしていますので、
他の列に数式が入っていても何ら問題はありません。m（_ _）m

お礼

ご回答というよりまさしくご指導は感謝感激です。
大変ご苦労かけたものですね。
誠にありがとう古材ます。
今後共々よろしくお願いいたします。
結果はバッチリでした。

回答No.3

kagakusuki

回答:No.2の続きです。

　次に、1月シートのB6セルに次の関数を入力して下さい。

=IF(ROWS($A$6:$A6)>COUNTIF(記入!$F:$F,"*?◆"&TEXT($C$4,"yyyy/mm")&"/*?◇★*?"),"",LEFT(VLOOKUP("*?◆"&TEXT($C$4,"yyyy/mm")&"/*?◇★"&ROWS($A$6:$A6),記入!$F:$F,1,FALSE),FIND(IF(COUNTIF(記入!$F:$F,INDEX(記入!$D:$D,MATCH("*?◆"&TEXT($C$4,"yyyy/mm")&"/*?◇★"&ROWS($A$6:$A6),記入!$F:$F,0))&" #2*"),"◆"," #"),VLOOKUP("*?◆"&TEXT($C$4,"yyyy/mm")&"/*?◇★"&ROWS($A$6:$A6),記入!$F:$F,1,FALSE))-1))

　次に、1月シートのA6セルに次の関数を入力して下さい。

=IF($B6="","",ROWS($A$6:$A6))

　次に、1月シートのC6セルに次の関数を入力して下さい。

=IF(COUNTIF(記入!$F:$F,$B6&IF(ISNUMBER(FIND("#",$B6)),""," #1")&"◆"&TEXT(C$4,"yyyy/mm/dd")&"◇*"),INDEX(記入!$J:$J,MATCH($B6&IF(ISNUMBER(FIND("#",$B6)),""," #1")&"◆"&TEXT(C$4,"yyyy/mm/dd")&"◇*",記入!$F:$F,0)),"")

　次に、1月シートのC6セルのセルの書式設定の表示形式を[ユーザー定義]の

[黒][<37]0.0"℃";[赤]0.0"℃"

として下さい。
　次に、1月シートのC6セルをコピーして、1月シートのD6～AG6の範囲に貼り付けて下さい。
　次に、1月シートのA6～AG6の範囲をコピーして、同じ列の7行目以下に貼り付けて下さい。

　次に、1月シートのコピーシートを11枚、複製して下さい。
　次に、それらのコピーシートのシート名を、それぞれ、

2月
3月
4月
5月
6月
7月
8月
9月
10月
11月
12月

に変更して下さい。

　これで、御望みの様な抽出結果が得られるかと思います。
　尚、この方法では、記入シートのA列のデータは使用しません。

回答No.2

kagakusuki

まず、記入シートのB列の日付欄のセルの書式設定の表示形式に関してですが、現行の様な年数が表示されない設定のままでは、例えば2013年の1月になってから、前年のデータを入力し直す様な事があった場合に、

12/20

と入力してしまいますと、データを入力した人間は2012年の12月20日のつもりで入力したのかも知れませんが、実際に入力される日付データは2013年の12月20日が入力されてしまいます。
　それにも関わらず、年数の表示がないために、誰も間違いに気づく事無く、日付は2013年の12月20日のまま修正されずに記録されてしまう恐れが高くなります。
　ですから、記入シートの日付欄のセルの書式設定の表示形式は、（過去のデータも含めて）[日付]の

2001年3月14日

か、或いは、[ユーザー定義]の

yyyy/mm/dd

といった、「年数＋月＋日」の形式に直して頂く事を御勧め致します。
　次に、各シートの体温の記入欄や表示欄のセルの書式設定を、[ユーザー定義]の

[黒][<37]0.0"℃";[赤]0.0"℃"

として下さい。
　こうしますと、条件付き書式を設定しなくとも、セルの値が数値の37以上の場合においては赤い文字で、数値の37未満の場合には黒い文字で、それぞれ

37.0℃

という具合に、小数点以下第1位の桁数までの数値が「℃」付きで表示されます。
　次に、同姓同名の顧客が複数人いる場合には、記入シートにおいて、2人目の同姓同名の顧客のデータが入力されている全ての行には、H列の顧客名重複欄に

2

を入力して下さい。
　同様に同姓同名の中で3人目の人物のデータが入力されている全ての行には、H列の顧客名重複欄に

3

を入力して下さい。
　尚、同姓同名の中で最初の人物のデータが入力されている全ての行には、H列の顧客名重複欄に何も入力しなくても構いませんし、

1

を入力しても構いません。
　因みに、H列の重複の有無の表示を自動化する事は、情報不足のため出来ません。
　何故なら、御質問の表中には、顧客番号等の個人を確実に識別出来るデータは無いようですし、さりとて、住所の違いから識別しようとしましても、顧客が引越しをする可能性がある以上、同姓同名ではあるものの、住所は異なる場合において、同一人物なのか、それとも別人なのかを判断する術がないからです。

　さて、ようやく本題ですが、まず、記入シートのF2セルに次の関数を入力して下さい。

=IF(OR(ISERR(1/DAY(INDEX($B:$B,ROW()))),INDEX($D:$D,ROW())=""),"",INDEX($D:$D,ROW())&" #"&IF(INDEX($H:$H,ROW())="",1,INDEX($H:$H,ROW()))&"◆"&TEXT(INDEX($B:$B,ROW()),"yyyy/mm/dd")&"◇"&IF(COUNTIF($F$1:INDEX($F:$F,ROW()-1),INDEX($D:$D,ROW())&" #"&IF(INDEX($H:$H,ROW())="",1,INDEX($H:$H,ROW()))&"◆"&TEXT(INDEX($B:$B,ROW()),"yyyy/mm")&"/*?◇"&"*?"),"","★"&COUNTIF($F$1:INDEX($F:$F,ROW()-1),"*?◆"&TEXT(INDEX($B:$B,ROW()),"yyyy/mm")&"/*?◇★*?")+1))

　次に、記入シートのF2セルをコピーして、記入シートのF3以下に貼り付けて下さい。
　次に、記入シートのC2セルに次の関数を入力して下さい。

=IF(COUNTIF(INDEX($F:$F,ROW()),"*?#*?◆*?◇*"),COUNT($C$1:INDEX($C:$C,ROW()-1))+1,"")

　次に、記入シートのC2セルをコピーして、記入シートのC3以下に貼り付けて下さい。
　次に、記入シートのI2セルに次の関数を入力して下さい。

=IF(COUNTIF(INDEX($F:$F,ROW()),"*?#*?◆*?"),COUNTIF($F$1:INDEX($F:$F,ROW()),LEFT(INDEX($F:$F,ROW()),FIND("◆",INDEX($F:$F,ROW())))&"*?"),"")

　次に、記入シートのI2セルをコピーして、記入シートのI3以下に貼り付けて下さい。

　次に、

1月

というシート名のシートを作成して下さい。
　次に、1月シートのB3セルに

2012

或いは

2012年

といった、年数（年度ではなく、西暦年数）を入力して下さい。（2013年の1月～3月に関しては2013と入力して下さい）
　次に、1月シートのB4セルに次の関数を入力して下さい。

=REPLACE(CELL("filename",B4),1,FIND("]",CELL("filename",B4),FIND(".xls",CELL("filename",B4))),)

　次に、1月シートのC4セルに次の関数を入力して下さい。

=IF(ISNUMBER((SUBSTITUTE($B$3,"年",)&"/"&SUBSTITUTE($B$4,"月",)&"/"&COLUMNS($C:C))+0),(SUBSTITUTE($B$3,"年",)&"/"&SUBSTITUTE($B$4,"月",)&"/"&COLUMNS($C:C))+0,"")

　次に、以下の様な操作を行って、1月シートのC4セルに条件付き書式を設定して下さい。

Excelウィンドウの[ホーム]タブをクリック
　　↓
1月シートのC4セルを選択
　　↓
「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=WEEKDAY(C4)=1

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある薄いオレンジ色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=WEEKDAY(C4)=7

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある薄い水色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある青色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[OK]ボタンをクリック

　次に、1月シートのC4セルをコピーして、1月シートのC5セルに貼り付けて下さい。
　次に、1月シートのC5セルの書式設定の表示形式を[ユーザー定義]の

d

として下さい。
　次に、1月シートのC4セルの書式設定の表示形式を[ユーザー定義]の

aaa

として下さい。
　次に、1月シートのC4～C5の範囲をコピーして、1月シートのD4～AG5の範囲に貼り付けて下さい。

※そろそろ、この回答欄に入力する事が出来る文字数制限の限界を超えそうですので、残りはまた後で投稿させて頂きます。

回答No.1

tom04

こんばんは！
一例です。

「記入」Sheetにもう1列作業用の列を設けます（氏名を重複なしに表示するため）
仮にN列を作業用の列とすると
N2セルに
=IF(COUNTIF(D$2:D2,D2)=1,ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そして、結果を表示させたいSheetのB6セルに
=IF(COUNT(記入!N:N)<ROW(A1),"",INDEX(記入!D:D,SMALL(記入!N:N,ROW(A1))))
これでB列に氏名が重複なしに表示されます。

表示したいSheetの4行目はシリアル値が入っているとすると
C6セルに
=IF(COUNTIF(記入!$F:$F,C$4&$B6),INDEX(記入!$L:$L,MATCH(C$4&$B6,記入!$F:$F,0)),"")
という数式を入れ、列方向・行方向にオートフィルで何とかご希望通りにならないでしょうか？m(_ _)m

補足

早速ご回答いただきありがとうございます。
ご指導いただいた数式で試しました。
体温を測定する人は日々、毎月一定の人でありません。
((3))のシート「記入」のD列に入力された顧客名は体温測定の有無にかかわらずに毎月のシートに反映されます。
体温を測定した人のみを当月のシートのみに反映したいのですが　B6の数式などどのようにすればよろしいか再度ご指導を仰ぎたいのです。
宜しくお願いできませんか。