エクセル関数を至急教えて下さい

質問

お金の支払い方法で話しを進めて行きます。

セルＫ6が7月分 Ｌ6が6月分 Ｍ6が5月分・・・・・・・・Ｒ6が12月分と設定とします

本来の支払いは、Ｒ6・Ｑ6・Ｐ6・・・Ｋ6と古い順に支払って行かなければなりません。

しかし、Ｎ6の4月分、Ｐ6の2月分、Ｒ6の12月分と抜けて支払いをしています。

Ｋ6 Ｌ6 Ｍ6 Ｎ6 Ｏ6 Ｐ6 Ｑ6 Ｒ6
■ ■ ■ 7/16 ■ 6/30 ■ 5/20

支払いした日付けを「a/b」とし表示し、未納分を■としています。

上記状態で、セルＯ6、Ｑ6が未納分として残っているのが分かります。

この状態を「中抜け」設定としたいのです。

その後、Ｏ6，Ｑ6が7/17に支払いをしたとすると

Ｋ6 Ｌ6 Ｍ6 Ｎ6 Ｏ6 Ｐ6 Ｑ6 Ｒ6
■ ■ ■ 7/16 7/17 6/30 7/17 5/20

となるため、「中抜け」状態は解消となります。（Ｓ6には中抜け表示をさせない）

以上の様な内容でお分かりになるでしょうか？

回答No.6

MarcoRossiItaly · ベストアンサー

No.3～5です。

＞Ｋ６が空白、または他の文字が入ると「中抜け」と判断されます。

回答した数式は、日付が入力されていないセルには全て「■」が入力されていることを前提に、組み立てられています。

つまりK6セルを挿入し、そこが空白とか他の文字が入っている状態というのは、K6に日付が入力されているのと同じに扱われることになります。したがって、K6より右側に「■」が存在すれば、当然「中抜け」と判断されます。K6にも「■」を入力すれば、セル挿入前と同様に数式が機能すると思います。

で、もしも追加のご質問が、機能しない数式を機能させるとかではなくて、とにかく「－」を「■」と同じ働きをする文字として指定したいというのが希望であれば、次式のようにすれば、「－」と「■」の区別がなくなります。足し算をするとOR条件になるのです。

=if(countif(k6:r6,"■")+countif(k6:r6,"－")>min(index(column(k6:r6)+((k6:r6="■")+(k6:r6="－"))*999,))-column(k6),"中抜け","")

回答No.5

MarcoRossiItaly

No.3・4です。度々すみません…。次式のほうがNo.4のものより更に短く書けたのでした。もちろん計算の結果は、変わりません。

=if(countif(k6:r6,"■")>min(index(column(k6:r6)+(k6:r6="■")*999,))-column(k6),"中抜け","")

変更した箇所は、SMALL関数の第2引数の「,1」という部分を削除するとともに、SMALL関数をMIN関数に置き換えました。これでも結果は同じです。これでもう修正ないと思います、すみませんm(_ _)m

ところで、No.1さんへの補足にお書きになった数式ですが、「条件付きで」だいたい大丈夫？そうです（それでも「■」の並びによってはMATCH関数の動きが怪しいケースもあるかもしれませんが…。）。つまり、少なくとも、最も左に記入される日付が常に、最も新しい日付である必要があります。新旧どんな順番で並ぶか分からない場合は、正しく機能しません。例えば、質問文のとおりに日付が記入されている状態から「7/16」と「5/20」の位置を交換してみると、「中抜け」が表示されません。

補足

もう少し教えて頂いても良いですか？

月日順番で行くとＫ６の部分に今後セルを挿入して行き、増える予定なのですが、現段階では

Ｋ６が空白、または他の文字が入ると「中抜け」と判断されます。

なので、「ハイフン（－）」を入れると中抜け判定されない方法は有りますか？

何度も回答頂き申し訳御座いませんが、今一度お力を貸して頂ければと思います。

回答No.4

MarcoRossiItaly

No.3です、何度もすみません。次式のほうがNo.3のものより若干、短く書けます。計算の結果は、変わりません。

=if(countif(k6:r6,"■")>small(index(column(k6:r6)+(k6:r6="■")*999,),1)-column(k6),"中抜け","")

変更した箇所は、INDEX関数の中身とSMALL関数の第2引数です。今回のINDEX関数は、日付であるセルをその列番号に、「■」であるセルをその列番号と「999」の合計に置き換えることでできる配列を生成。なお999か月は、83年に相当。

回答No.3

MarcoRossiItaly

=if(countif(k6:r6,"■")>small(index((k6:r6<>"■")*column(k6:r6),),countif(k6:r6,"■")+1)-column(k6),"中抜け","")

解説。

「countif(k6:r6,"■")」は、K6:R6の範囲にある「■」の個数を計算。INDEX関数は、「■」であるセルを「0」、日付であるセルをその列番号に置き換えることでできる配列を生成。「countif(k6:r6,"■")+1」は、K6:R6の範囲にある「■」の個数に「1」を足した数を計算。「small-column」は、列番号どうしの引き算により（SMALL関数に列番号を計算させている）、日付がある列のうち最も左の列より左にある「■」の個数を計算。

要するに、質問文のとおり日付が入力されているならば、全体の「■」の個数は5個、最も左にある日付「7/16」より左にある「■」の個数は3個であり、「5>3」が成り立つので、「中抜け」と判定される数式になっています。

回答No.2

KURUMITO

S６セルには次の式を入力してはどうでしょう。

=IF(COUNT(K6:R6)=0,"",IF(COUNTIF(INDEX(K6:R6,MATCH(1,INDEX(SIGN(K6:R6),0),0)):R6,"")>0,"中抜け",""))

お礼

ご指導ありがとうございます。
今後ともよろしくお願いいたします。

補足

ありがとうございます。
セルの場所によって「中抜け」とならない所があります・・・・。

回答No.1

CC_T

> この状態を「中抜け」設定としたいのです。

支払日の間が空いていたら、S6に「中抜け」って文字を表示したいのでしょうか？

例えば、COUNTIFでK6からR6までの空白セルの数を数えて、範囲内の左端からその数分のセルを合計した結果が０以上ならば中抜け状態と判断できそうですね。合計取ったセル範囲のどこかに日付が入っているってことですから。

SUMの範囲を左右に可変するにはOFFSETを使って、

S6　=IF(SUM(OFFSET(K6,0,0,1,COUNTIF(K6:R6,"")))>0,"中抜き",""）
ですかね。

お礼

ご指導ありがとうございます。
今後ともよろしくお願いいたします。

補足

早々ありがとうござます。

コピーして貼り付けして見ましたが・・・・

値が入力出来ませんでした(>_<)

ちなみにこう言うのも有りましたが、いかがでしょうか？

S1=IF(COUNTIF(K6:R6,"■")=0,"",IF(MATCH("■",K6:R6,1)>MATCH(MAX(K6:R6),K6:R6,0),"中抜け",""))