[[20191122100654]]　『関数で月末日数により参照範囲を可変的に対応する』（マイン）　ページの最後に飛ぶ

『関数で月末日数により参照範囲を可変的に対応するためには』（マイン）

お世話になっております。

(質問ですが)

数式で、月の日数に応じ「カレンダー参照範囲」を可変的に対応し
その範囲の空白個数を求めたいと考えております。

現在は行毎に、月日数に応じて数式の参照範囲を調整し対応しております。
これを、一つの数式で可変的に対応できないかと検討しております。

仕様としては・・・

その行のデータがどの月かを識別するには、新たな作業列を設けずに
A列に「月末日」を入力されてある情報を基に月末が28日・・・31日なのかを判別してカレンダー参照範囲を可変的に対応できればと思います。
　

(シート構成)

・列:A列:BW列の範囲
・行:1行目(項目行)　2行目～動的な最終行(データ範囲)

(日付に関して)

・A列の各行に集計月「月末日(日付形式)」が入力されてある

・カレンダー範囲は・・・「AE列:BI列」

　→カレンダー項目行:日付ではなく「数値が1～31」分入力されている
　→カレンダーデータ範囲:「数字・文字・記号」が入力されている

(現在の数式)

・月の末日によって参照範囲を「BFからBI」で絞り込み
・空白を求める関数「COUNTBLANK」を使用

月が28日の場合　AB2=COUNTBLANK(AE2:BF2)

月が29日の場合　AB2=COUNTBLANK(AE2:BG2)

月が30日の場合　AB2=COUNTBLANK(AE2:BH2)

月が31日の場合　AB2=COUNTBLANK(AE2:BI2)

上記をA列に入力されてある日付から月末日を求め
データ範囲を可変的にしたいんですが・・・

すみませんが、皆様アドバイスのほどよろしくお願いいたします。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 よく読んでいませんが

 AB2=COUNTBLANK(AE2:BI2) -（31-月末日）

 で 各月対応できそうな気がしますけど

（渡辺ひかる） 2019/11/22(金) 10:55

 一目ですけど・・

 AB2セル =COUNTBLANK($AE2:INDEX(2:2,30+DAY($A2)))

（半平太） 2019/11/22(金) 10:57

 AB2セルに
 =COUNTBLANK(OFFSET(AE2,,,,DAY(A2)))
 と入力して下へフィルコピーではどうだろうか？
 DAY(A2)で日付データから日のデータだけを取り出してOFFSET関数でAE列からその幅のセル範囲を求めている。
（ねむねむ） 2019/11/22(金) 10:57

 ＞カレンダーデータ範囲:「数字・文字・記号」が入力されている

 ↑ が手入力なら（数式で空白にしていないのなら）

 =DAY(A2)-COUNTA(AE2:BI2)

 これではダメなんですかね？

 小の月でも、31日の列に何か入力することがあるんですか？

 以上、参考まで
（笑） 2019/11/22(金) 11:20

皆さん早速のアドバイスありがとうございました。

それぞれ試してみました。アイディアが凄いです。

ちなみに

AB2=COUNTBLANK(AE2:BI2) -（31-月末日）参考に

月末日を求めて日付形式から数値変換し
範囲を可変というより、総合計から不要な日数を除外する

=COUNTBLANK(AE2:BI2)-(31-(VALUE(TEXT(A2,"d"))))

で正しい計算が今のところできていることを確認しました。

範囲を可変的にしなければと言う固定観念にとらわれていたようです。
勉強になりました。

（マイン） 2019/11/22(金) 11:50

笑　さま

おっしゃるとおりです、範囲合計=31から、当月末の日数を差し引くだけで良かったようです。

シンプルなコードありがとうございました。
（マイン） 2019/11/22(金) 11:52

 ＞範囲合計=31から、当月末の日数を差し引くだけで良かったようです。

 こんな回答してませんけど？

 =DAY(A2)-COUNTA(AE2:BI2)
（当月日数）－（空白ではない個数）、です。

 ちなみに
 ＞=COUNTBLANK(AE2:BI2)-(31-(VALUE(TEXT(A2,"d"))))

 VALUE 関数は不要
 =COUNTBLANK(AE2:BI2)-(31-TEXT(A2,"d"))

 さらに言えば、TEXT 関数なんか使わなくても DAY 関数で
 =COUNTBLANK(AE2:BI2)-(31-DAY(A2))

 数式で空白にしていないのなら
 ↓ と同じ結果になりませんか？
 =DAY(A2)-COUNTA(AE2:BI2)

 以上、参考まで
（笑） 2019/11/22(金) 13:02

コメント返信：

[ 一覧(最新更新順) ]

[[20191122100654]] 『関数で月末日数により参照範囲を可変的に対応する』（マイン） ページの最後に飛ぶ

[[20191122100654]]　『関数で月末日数により参照範囲を可変的に対応する』（マイン）　ページの最後に飛ぶ