☰

Excel - 特定のパターンに一致する日付範囲で行を自動的に下方向に入力する

tag-icon • tag-icon microsoft-excel
Excel - 特定のパターンに一致する日付範囲で行を自動的に下方向に入力する

特定の開始日（たとえば、2012 年 1 月 11 日）が指定されている場合、次のパターンの日数（隔月給与期間）に一致する日付範囲で Excel の行を自動的に入力したいと考えています。

1. 11th - 25th
2. 26th - 10th

もちろん、各セルには月と年を含む実際の日付が含まれます (上記の 2 つの値を単に繰り返すのではなく)。私が探しているのは、次のような 1 つまたは 2 つの特定の日付範囲を入力する方法です。

January 11th, 2012 - January 25th, 2012
January 26th, 2012 - February 10th, 2012

そして、Excel で同じパターンでさらに多くのセルを自動的に下方向に入力するようにします。

January 11th, 2012 - January 25th, 2012
January 26th, 2012 - February 10th, 2012
February 11th, 2012 - February 25th, 2012
....
December 11th, 2012 - December 25th, 2012

これどうやってするの？

答え1

列を希望の日付形式にフォーマットします。セル A1 に 2012/1/11 の日付を入力し、セル B2 に 2012/1/25 の日付を入力します。次のコードを A2 にコピーします。

=DATE(YEAR(B1),MONTH(B1), DAY(B1)+1)

これをB2にコピーします。

=IF(DAY(B1)=10, DATE(YEAR(B1), MONTH(B1), DAY(25)), DATE(YEAR(B1),MONTH(B1)+1, DAY(10)))

先に進み、A2 と B2 をハイライトして、必要なだけ下にドラッグします。

完了すると次のようになります。

スクリーンショット

答え2

ここに 1 行のソリューションがあります。警告: これは巨大な数式であり、別の参照テーブルが必要です。

ワークブックのどこかに、次の表を作成する必要があります。

0   January
1   February
2   March
3   April
4   May
5   June
6   July
7   August
8   September
9   October
10  November
11  December

私の例では、この表は I1:J12 にあるので、それに応じて数式を調整してください。A1 に次のように入力します。

=VLOOKUP(MOD(ROUNDDOWN((ROW()-0.5)/2,0),12),$I$1:$J$12,2,FALSE) & IF(ISODD(ROW())," 11th, "," 26th, ") & 2012 + INT((ROW()-0.5)/24) & " - " & VLOOKUP(MOD(ROUNDDOWN(ROW()/2,0),12),$I$1:$J$12,2, FALSE) & IF(ISODD(ROW())," 25th, "," 10th, ") & 2012 + INT(ROW()/24)

これを解析しやすくするために、式を部分ごとに分解したものを以下に示します。

=VLOOKUP(MOD(ROUNDDOWN((ROW()-0.5)/2,0),12),$I$1:$J$12,2,FALSE) & 
 IF(ISODD(ROW())," 11th, "," 26th, ") &
 2012 + INT((ROW()-0.5)/24) &
 " - " &
 VLOOKUP(MOD(ROUNDDOWN(ROW()/2,0),12),$I$1:$J$12,2, FALSE) &
 IF(ISODD(ROW())," 25th, "," 10th, ") &
 2012 + INT(ROW()/24)

答え3

公式法なし

  1. 入力してくださいA1:11/1/2012
    入力してくださいA2:11/2/2012
    ハイライトA1:A2、下方向に（マウスドラッグで）塗りつぶす（例えば、上方向）A12（11/12/2012）

  2. 入力してくださいA13:26/1/2012
    入力してくださいA14:26/2/2012
    ハイライトA13:A14、下方向に（マウスドラッグで）塗りつぶす（例えば、上方向）B24（26/12/2012）

  3. 列を並べ替えるあ期間の開始日となる

  4. (a) 列ごとに繰り返すB期間終了日（25日と10日）
    または
    （b）次の開始日 - 1を終了日として設定します。B1: =A2-1、下にコピーしますB2:B23

  5. 列の書式設定A:Bセルの書式設定（Ctrl-1）で必要に応じて設定します。たとえば、mmmm dd, yyyy

ボーナス

開始日と終了日をハイフンで区切って 1 つのセルに表示するには:

  1. 入力してくださいC1:=TEXT(A1,"mmmm dd, yyyy") & " - " & TEXT(B1, "mmmm dd, yyyy")
    次のような結果になりますJanuary 11, 2012 - January 25, 2012

  2. 下にコピーC2:C24

関連情報