エクセルに関して質問させて頂きます。

質問

エクセルに関して質問させて頂きます。

シートＡ
Ａ　　　　Ｂ（日付がＡ列、○×がＢ列の意味）
1 １月１日○
2 １月５日○
3 １月６日×
4 １月８日×
5 １月１２日×
6 １月１５日○
7 １月１８日×
8 １月２０日×
9 １月２３日○
10 １月２５日○

シートＢ（配列はシートＡと同様）
１月４日○
１月５日×
１月６日×
１月１１日○
１月１６日○
１月１７日○
１月１８日×
１月２０日×
１月２２日×
１月２７日○

シートＣのあるマスにシートＡ、Ｂを足した×の最大連続数を表示させるにはどうしたらいいでしょうか？

わかる方がいらっしゃいましたら、どうか力をお貸しください。

この表ですと、×の最大連続数はシートＡの１月１８日、１月２０日とシートＢの１月１８日、１月２０日、１月２２を足して５になります。

説明に至らない点があるかもしれません。そのときはご指摘頂けると有難いです。

回答No.8

kagakusuki

>既に自動になっていました。自動の状態で結果に１が表示されます。

　C3セルの数式が

=IF(ROW($1:2)>COUNT($A:$B),"",SMALL($A:$B,ROW($1:2)))

となっている事が間違いない限りは、Sheet3のC3以下のセルでは自動計算が行われていない事は間違いありません。
　どの様な設定が影響しているのか解らないため、新規に作成した（コピーによらない）BOOKで作業をやり直されては如何でしょうか。
　もし、別のBOOKが使用出来ない場合には、せめて新規にSheetを挿入して、作業をやり直す事を御勧めします。
　その際には、元のBOOKのSheet3から、コピー＆ペーストで数式を貼り付けるのではなく、キーボード入力で数式を入力し直して下さい。（新規Sheet内でのコピー＆ペーストは可）
　そして、×の最大連続数の合計を表示させるセルに入力されている数式中のSheet名を、新規Sheetに合わせて変更して下さい。

お礼

新規作成したbookでsheet3のa2,b2,c2,d2、全ての数式をキーボード入力してみましたが、結果は変わりませんでした。

それで違うパソコンのエクセルならできるかもしれないと思い、上記と同じようにしてみましたが、結果は変わりませんでした。

回答No.7

kagakusuki

>Ｃ３セルの数式
=IF(ROW($1:2)>COUNT($A:$B),"",SMALL($A:$B,ROW($1:2)))

　仰る通りだとしますと、Excelが自動計算を行っていない事になりますので、以下の操作を行って、自動計算を行う様にデフォルトの設定に戻す必要があります。

メニューの[ツール]をクリック
　　↓
現れた選択肢の中にある[オプション]をクリック
　　↓
現れた「オプション」ウィンドウの[計算方法]タグをクリック
　　↓
「計算方法」欄の「自動」と記されている箇所をクリックして、チェックを入れる
　　↓
「オプション」ウィンドウの[OK]ボタンをクリック

お礼

既に自動になっていました。自動の状態で結果に１が表示されます。簡単な数式、例えば１＋１＝２は自動計算されてます。

回答No.6

kagakusuki

>コピー＆ペーストして比べて見ましたが、同じように見えます。

　それでは、Sheet3のC3セルを確認してみて下さい。
　Sheet3のC2セルに入力した

=IF(ROW($1:1)>COUNT($A:$B),"",SMALL($A:$B,ROW($1:1)))

という数式が、Sheet3のC3セルで

=IF(ROW($1:2)>COUNT($A:$B),"",SMALL($A:$B,ROW($1:2)))

という様に、正常に変換されていますでしょうか。

数式の中に2ヶ所ある

ROW($1:2)

の部分が、

ROW($1:1)

の様に、C2セルと同じになってはいないでしょうか？
　もし、C3セルの数式がC2セルの数式と、全く同じになっている場合は、ANo.2における

>　次に、Sheet3のC2～D2の範囲をコピーして、同じ列の2行目以下に、シートＡにおいて値が存在している行数と、シートＢにおいて値が存在している行数を、合計した行数を上回るのに充分な行数になるまで貼り付けて下さい。

の操作で、C2セルをコピーする際に、セルではなく、セルに入力されている数式を表している文字列を、コピーしてしまったのかも知れません。
　或いは、C3セルの内容が数式ではなくなってはいないでしょうか？
　もし、C3セルの内容が数式ではなかった場合は、貼り付ける際に、値のみを貼り付けてしまったのかも知れません。
　ですから、C2セルとD2セルに入力されている数式を再確認されてから、再度、C2～D2の範囲をコピーして、同じ列の2行目以下に、シートＡにおいて値が存在している行数と、シートＢにおいて値が存在している行数を、合計した行数を上回るのに充分な行数になるまで貼り付けて下さい。

お礼

確認してみました。ご指摘くださったROWの部分の数値は１ずつ動いていました。1ずつ動いている状態で結果が１になります。

Ｃ２セルの数式
=IF(ROW($1:1)>COUNT($A:$B),"",SMALL($A:$B,ROW($1:1)))
Ｃ３セルの数式
=IF(ROW($1:2)>COUNT($A:$B),"",SMALL($A:$B,ROW($1:2)))

以下同じです。

行を一つ増すごとに、ROW($1:2)の部分の2が3,4,5,6とプラス１されています。

回答No.5

kagakusuki

Sheet3のC2セルに入力した数式がANo.2とは違っている様です。

=IF(ROW($1:1)>COUNT($A:$B),"",SMALL($A:$B,ROW($1:1)))

の中に2ヶ所ある

ROW($1:1)

の部分が、

ROW($1:$1)

になってはいないでしょうか？（特に後の方）

　尚、A列とB列の表示は私のパソコンのデータと同じです。
　A列～C列に並んでいる数字は、シリアル値といって、年月日の情報を含んでいる数値です。
　シリアル値は、以下の操作でセルの書式設定を「日付」にすると、表示を日付に変える事が出来ます。

表示させるセル又はセル範囲を選択
　　↓
黒い太枠の内側にカーソルを合わせ、マウスを右クリック
　　↓
現れる選択肢の中から、[セルの書式設定]を選択してクリック
　　↓
現れる[セルの書式設定]ウィンドウの[表示形式]タグをクリック
　　↓
[分類]欄の中から、[日付]を選択してクリック
　　↓
[種類]欄の中から

2010/3/14

を選択してクリック
　　↓
[セルの書式設定]ウィンドウのOKボタンをクリックする

お礼

返信ありがとうございます。

sheet3の列C、行２（シート上のもの）
=IF(ROW($1:1)>COUNT($A:$B),"",SMALL($A:$B,ROW($1:1)))

Ａｎｏ２(回答頂いたもの)
=IF(ROW($1:1)>COUNT($A:$B),"",SMALL($A:$B,ROW($1:1)))

コピー＆ペーストして比べて見ましたが、同じように見えます。

シリアル値を日付に変更して見ましたが、結果は変わりませんでした。

回答No.4

kagakusuki

再度確認致しましたが、投稿時の入力ミスは、もう無い様です。
　又、当方のパソコン上では、結果には5が表示されております。
　失礼ながら、数式を修正後に、修正結果を他の行のセルに反映するために、2行目をコピーして、3行目以下に貼り付ける操作はお済みでしょうか？

　それから、不具合の原因を調べる情報を得るため、当方のパソコンにおける、Sheet3の表示の状況を、以下に記しましたので、違いがあればお知らせ願います。

　　　　 A列 　　　 B列 　　　 C列 　　D列
1行目
2行目　　　　　　　　　　　　 2010/1/5　 1
3行目　　　　　　 2010/1/5　　2010/1/6　 1
4行目　2010/1/6　 2010/1/6　　2010/1/6　 2
5行目　2010/1/8　　　　　　　 2010/1/8　 3
6行目　2010/1/12　　　　　　　2010/1/12　1
7行目　　　　　　　　　　　　 2010/1/18　1
8行目　2010/1/18　2010/1/18　 2010/1/18　2
9行目　2010/1/20　2010/1/20　 2010/1/20　3
10行目　　　　　　2010/1/22　 2010/1/20　4
11行目　　　　　　　　　　　　2010/1/22　5

お礼

Ａ列　　Ｂ列　　　　Ｃ列　　　　　　　　　　　　　Ｄ列
2行目　空白　空白
3行目 空白 40183　　　40183　　　　　　　　　　　　　1
4行目 40184　40184　　　40183　　　　　　　　　　　　1
5行目 40186　空白　　（以下同じ数字が続く）　　（以下同じ数字が続く）
6行目 40190　空白
7行目　空白　空白
8行目 40196　40196
9行目 40198　40198
10行目　空白 40200
11行目　空白　　空白

修正したものを３行目以降もコピーしてこの結果になります。列Ｃ、列Ｄの関数は、列Ａ、列Ｂの合計以上の数をコピーしています。

回答No.3

kagakusuki

>書いてくださった式をコピペしてやってみましたが、=MAX(Sheet3!$D:$D)の部分に０が表示されます。

　申し訳御座いません、私のミスです。
　Sheet3のA2セルとB2セルに入力する数式を、回答欄に手入力した際に、写し間違いが御座いました。（携帯電話から投稿しているため、コピー&ペーストする事は困難です）

Sheet3のA2セルの数式

[誤]
=IF('シートＡ'!$B1="×",'シートＡ'!$B1,"")

[正]
=IF('シートＡ'!$B1="×",'シートＡ'!$A1,"")

Sheet3のB2セルの数式

[誤]
=IF('シートＢ'!$B1="×",'シートＢ'!$B1,"")

[正]
=IF('シートＢ'!$B1="×",'シートＢ'!$A1,"")

　質問者様には御迷惑をおかけして申し訳御座いませんでした。

　尚、数式のコピー&ペーストの件ですが、各数式中の

"○"

や

"×"

の部分に関しましては、形は似ていても、質問者様がシートＡに入力された×印とは、文字コードが異なっている可能性も無いとは言えません。
　又、数式中のシート名に関してましても、実際のシート名に合わせなければ、Excelが処理する事が出来ません。
　ですから、各シート名や

"○"

や

"×"

の部分に関しましては、再度入力し直して下さい。

お礼

返信ありがとうございます。何度も面倒をお掛けして申し訳ありません。

ご指摘してくださった通りシートＡと×の文字、そして関数を書き直してやってみました。その結果、今度は=MAX(Sheet3!$D:$D)のセルに１と表示されました。=MAX(Sheet3!$D:$D)のセルに５と表示されません。エクセルに詳しくないので、教えてくださった関数を見ても自分では何処を直せばよいかわかりません。携帯電話でのタイピングは大変だということはわかっていますが、申し訳ありません、もう一度ご忠告を頂けませんでしょうか。

回答No.2

kagakusuki

>※まずシートＡ、Ｂを日付順に一つの表としてみます。その中での最大連続×数を求めたいのです。

　もし、×が連続している日付の最初か最後の日が、シートＡとシートＢの両方に存在していて、片方は×で、もう一方は○の場合には、連続数のカウントには含めない方が良いのでしょうか？

　それでしたら、まず、作業列を4列（仮にSheet3のA列～D列とします）設けて下さい。
　次に、Sheet3のA2セルに次の数式を入力して下さい。

=IF('シートＡ'!$B1="×",'シートＡ'!$B1,"")

　次に、Sheet3のB2セルに次の数式を入力して下さい。

=IF('シートＢ'!$B1="×",'シートＢ'!$B1,"")

　そして、Sheet3のA2～B2の範囲をコピーして、同じ列の2行目以下に、シートＡにおいて値が存在している行数か、シートＢにおいて値が存在している行数の、何れか大きい方を上回るのに充分な行数になるまで貼り付けて下さい。
　次に、Sheet3のC2セルに次の数式を入力して下さい。

=IF(ROW($1:1)>COUNT($A:$B),"",SMALL($A:$B,ROW($1:1)))

　次に、Sheet3のD2セルに次の数式を入力して下さい。（もし、シートＡかシートＢの行数が999を超える場合には、その行数を上回る様に、数式中の999の部分を変更して下さい）

=IF($C2="","",IF(SUMPRODUCT(('シートＡ'!$A$1:$A$999<=$C2)*('シートＡ'!$A$1:$A$999>=$C1)*('シートＡ'!$B$1:$B$999="○"))+SUMPRODUCT(('シートＢ'!$A$1:$A$999<=$C2)*('シートＢ'!$A$1:$A$999>=$C1)*('シートＢ'!$B$1:$B$999="○"))>0,1,D1+1))

　次に、Sheet3のC2～D2の範囲をコピーして、同じ列の2行目以下に、シートＡにおいて値が存在している行数と、シートＢにおいて値が存在している行数を、合計した行数を上回るのに充分な行数になるまで貼り付けて下さい。
　最後に、×の最大連続数の合計を表示させるセルに、次の数式を入力して下さい。

=MAX(Sheet3!$D:$D)

　以上です。

　尚、×が連続している日付の最初か最後の日が、シートＡとシートＢの両方に存在していて、片方は×で、もう一方は○の場合も、連続数のカウントに含める場合には、Sheet3のD2セルに入力する数式中の
「>=」の部分を「>」に、
「<=」の部分を「<」に、
それぞれ変更して下さい。

お礼

返信ありがとうございます。

何度もすみません。書いてくださった式をコピペしてやってみましたが、=MAX(Sheet3!$D:$D)の部分に０が表示されます。ボクが書いたシートＡとシートＢをそのまま使うと、ここの数値は５になっていると思います。何処が間違っているのかわかりません。力をお貸し頂けると嬉しいです。

回答No.1

kagakusuki

この表ですと、×の最大連続数はシートＡの１月６日、１月８日、１月１２日とシートＢの１月１８日、１月２０日、１月２２を足して６になると考えれば良いのでしょうか？

　もし、そうだとしたならば、適当な列（例えば、シートＡのC列とシートＢのC列）を作業列として使用します。
　まず、両方のシートのC1セルには次の数式を入力して下さい。

=B1="×"

　次に、C2セルには次の数式を入力して下さい。

=IF(B1="×",C1+1,0)

　続いて、C2セルをコピーして、C3以下に貼り付けて下さい。
　そして、×の最大連続数の合計を表示させるセルには、次の数式を入力して下さい。

=MAX('シートＡ'!C:C)+MAX('シートＢ'!C:C)

　以上です。

お礼

Kagakusukiさん。力を貸してくださりありがとうございます。

申し訳ありません。ボクの説明に曖昧な点がありました。そこを補足させて頂きます。

シートＡの最大連続×数とシートＢの最大連続×数の和ではありません。

この解釈ですとKagakusukiさんのおっしゃるとおり６になります。

※まずシートＡ、Ｂを日付順に一つの表としてみます。その中での最大連続×数を求めたいのです。