エクセルデータ処理(フラグ立て）

質問

エクセルデータ処理(フラグ立て）
次のようなデータがあります。

これに対して次のようなルールで、右側にフラグをつけたいどうすればいいか。
条件データは時系列に昇順、あるいは降順になっている。必要なら適当にソートできる。
フラグ付与ルール
(1)同一日付のデータ数が２件以下ならそのデータの横にはすべて１をつける
(2)同一日付のデータ数が3件以上ならそのデータの最上部と最下部の横に１をつける
((2)はある意味で(1)を含んでいるといえますが）
(質問）セルに、なんか式を書くことで処理できますか。考えてわからなかったので質問します。

時間(a1)
2006/1/26 16:50
2006/1/26 16:50
2006/1/26 16:50
2006/1/26 16:48
2006/1/26 16:46
2006/1/26 16:44
2006/1/26 16:43
2006/1/26 16:43
2006/1/26 16:43
2006/1/24 18:35
2006/1/24 18:35
2006/1/22 10:35
2006/1/21 16:50
2006/1/21 16:50
2006/1/21 16:50
2006/1/21 16:48
結果例
時間(a1セル)
2006/1/26 16:50 1
2006/1/26 16:50
2006/1/26 16:50
2006/1/26 16:48
2006/1/26 16:46
2006/1/26 16:44
2006/1/26 16:43
2006/1/26 16:43
2006/1/26 16:43 1
2006/1/24 18:35 1
2006/1/24 18:35 1
2006/1/22 10:35 1
2006/1/21 16:50 1
2006/1/21 16:50
2006/1/21 16:50
2006/1/21 16:48 1

回答No.1

hirumin · ベストアンサー

=IF(AND(ISNUMBER(A3),ISNUMBER(A5)),IF(ROUNDDOWN(A3,0)=ROUNDDOWN(A5,0),"",1),1)

一つ上の行と、一つ下の行で違った場合い「1」と言い換えられますよね？

お礼

これが、一つの完全な解答であることを確認しました。
見事というしかありません。とても感謝しています。

回答No.9

chie65536

度々すいません。No.8を書いてから気付きました。

「ソートされている」と言う条件があるので、もっと簡略化できます。

「１つ上と現在行、１つ下と現在行が共に同じなら、現在行は無視して、１つ上と１つ下が同じかどうか判定」
で事足ります。

ソートしてあれば
2006/1/21 16:50
2006/1/20 10:50
2006/1/21 16:48
という並びになる事は有り得ませんから。

最終的には
=IF(INT(OFFSET(A2,-1,0,1,1))=INT(OFFSET(A2,1,0,1,1)),"",1)
で済みます。

回答No.8

chie65536

No.7で補足してから発見。

フラグ付けるのは「２件なら」だと思ってましたが、質問を良く見ると「２件以下なら」だったんですね。

完璧に「以下」を見落としてました。

そうすると、１番目と３番目のケースでフラグ付け、２番目のケースでフラグ無しになるので
=IF(AND(INT(OFFSET(A2,-1,0,1,1))=INT(A2),INT(OFFSET(A2,1,0,1,1))=INT(A2)),"",1)
となります。

質問は良く読んでから回答しなければなりませんね。失礼しました。

お礼

フォローの件ありがたく拝見しました。そうなんです。私はある作業のアクセスを記録した時間データで、この日は何時～何時までアクセス作業しているか要約した結果を見たいと思ってこういう質問となりました。それで最初の結果をためしてうまくいっていたので満足だったのです。

回答No.7

chie65536

解決したようで、なによりです。
＞この部分は付けるの記述ミスですね。

最初の回答が、記述ミスと言うか、記述を略し過ぎました。略さずに書くと以下のようになります。

＞・１つ上の行の日付と、１つ下の行の日付と、どっちも日付が違うなら、フラグを付けない。
・１つ上の行の日付と現在行、１つ下の行の日付と現在行をそれぞれ比較し、どっちの比較でも日付が違うと判定したなら、同一日付のデータが１件しかないので、フラグを付けない。

＞・１つ上の行の日付と、１つ下の行の日付と、どっちも日付が同じなら、フラグを付けない。
・１つ上の行の日付と現在行、１つ下の行の日付と現在行をそれぞれ比較し、どっちの比較でも日付が同じと判定したなら、同一日付のデータが３件以上ある塊の中間なので、フラグを付けない。

＞・１つ上の行の日付と、１つ下の行の日付と、片方だけ日付が違い、片方だけ日付が同じなら、フラグを付ける。
・１つ上の行の日付と現在行、１つ下の行の日付と現在行をそれぞれ比較し、片方だけ日付が違うと判定し、片方だけ日付が同じと判定したなら、同一日付のデータが複数件あるデータの集まりの最上部か最下部なので、フラグを付ける。

ついでに、どうしてあのような式が一つで済むのか解説しておきます。

ここで、１番目のケースの
「どっちの比較でも日付が違うと判定した」
と、２番目のケースの
「どっちの比較でも日付が同じと判定した」
は、２つまとめて考えれば
「２つの比較判定の結果が同じだった」
と言い換える事が出来ます。

つまり１番目と２番目のケースは
「２つの比較判定の結果が同じだった」
と言う事になり、３番目のケースは
「２つの比較判定の結果が異なった」
となります。

つまり
「２つの比較判定の結果を比べる」
と、フラグを付けるか付けないか判定出来ます。

で、最終的に
IF((a=c)=(b=c),…)
と言う「比較式２つを比較する」と言う判定式１つで記述出来る事になります。

回答No.6

imogasi

同一日が一塊になって出てくる。
飛び離れて同一日が出現しない。
なら
日が前行と次行と同じ場合空白
そうでなければ１でよいのでは。
例データ　Ａ２：Ｂ１７（第１行はあけて置いてください）
2006/1/26 0:00:00 1
2006/1/26 16:50:00
2006/1/26 16:50:00
2006/1/26 16:48:00
2006/1/26 16:46:00
2006/1/26 16:44:00
2006/1/26 16:43:00
2006/1/26 16:43:00
2006/1/26 16:43:00 1
2006/1/24 18:35:00 1
2006/1/24 18:35:00 1
2006/1/22 10:35:00 1
2006/1/21 16:50:00 1
2006/1/21 16:50:00
2006/1/21 16:50:00
2006/1/21 16:48:00 1
Ｃ２は=IF(AND(A2=A1,A2=A3),"",1)
を入れてＣ１７まで式を複写。
２２日が気になるが
=IF(COUNTIF($A$2:$A$17,A2)=1,"",IF((A2=A1)*(A2=A3),"",1))
にすると２２日は空白になる

お礼

式の意味はよく理解できるようになりました。
結果を試していませんが、うまくいくと思います。
ご回答どうもありがとうございました。

回答No.5

keirika

A2:A100まで日付が入っていると仮定します。

B2セルに

=IF(COUNTIF($A$2:$A$100,$A2)=1,"",IF(COUNTIF($A$2:$A$100,$A2)=2,1,IF(MIN(($A$2:$A$100<>$A2)*65536+ROW($A$2:$A$100))=ROW(),1,IF(MAX(($A$2:$A$100=$A2)*ROW($A$2:$A$100))=ROW(),1,""))))

の式を入れ、Ctrl+Shift+Enterを同時に押します。

もし、式を回答欄からコピーした場合は確定した式をクリックして、Ctrl+Shift+Enterを同時に押します。

後は下方向へコピーして下さい

日付の行数が200や300になった場合は式中の100を200や300に変えてください

お礼

すでによい解答があったのでこちらは、ためしておりませんが、どうもありがとうございました。

回答No.4

chie65536

ソート後に限り、フラグ付けの条件を言い換えると、以下のようになります。

・１つ上の行の日付と、１つ下の行の日付と、どっちも日付が違うなら、フラグを付けない。
・１つ上の行の日付と、１つ下の行の日付と、どっちも日付が同じなら、フラグを付けない。
・１つ上の行の日付と、１つ下の行の日付と、片方だけ日付が違い、片方だけ日付が同じなら、フラグを付ける。

で、データの先頭の行と、データの最後の行は、それぞれ「１つ上の行」「１つ下の行」が無い為、セルの高さをゼロにしてあるダミー行を置いて下さい。これで、どの行も同じ式で書けます。

「１つ上の行」「１つ下の行」を参照する式を書く場合、再ソートして行が入れ替わったり、行の追加、行の削除をしても影響が無いように「A1」とか「$A$1」とかセル名を書かず、現在セルからのオフセット位置を指定してセル位置を求める関数を使用します。

日付データを２行目（A2セル）から打ち込んで、B2セルに
=IF((INT(OFFSET(A2,-1,0,1,1))=INT(A2))=(INT(OFFSET(A2,1,0,1,1))=INT(A2)),"",1)
と式を入力し、B2セルを必要なだけ下方向にコピーして下さい。

１行目と最終行の１つ下に、ダミーの何も入れない行を作るのをお忘れなく。

これで目的を達成できるし、行を追加しての再ソート、行の追加と削除も可能です。

お礼

すべて解決しました。（なお回答は問題ないのですが、
・１つ上の行の日付と、１つ下の行の日付と、どっちも日付が違うなら、フラグを付けない。この部分は付けるの記述ミスですね。）
本当に素晴らしい回答だと思います。
回答のようにダミー行を置き、どの行も同じ式で書けるように考える所に、
エクセル達人の智恵を感じ、Aha！体験いたしました。またこのように説明いただくと、offsetとはこういう場合に使うのかと意義/効用がとてもよくわかりました。どうもありがとうございました。

回答No.3

ysko614

時間がないので、(1)途中だけ、
A列にあるとして、下の式を適当な列に入れます。

=COUNTIF($A$1:$A$16,A1)
これでいくつあるのかは分かります。
これとIF関数を使って下さい。

お礼

私にとって意味不明ですがありがとうございました。

回答No.2

hirumin

No.1で回答したhiruminです。
式はA4の日時に対する判別式でした。
補足しておきます。