Excel。セルごとに年・月・日が含まれているデータから日付をつくりたい【Date】

Excel。セルごとに年・月・日が含まれているデータから日付をつくりたい

＜DATE＋SUBSTITUTE関数＞

データを読み込んでみたら、そのあとの作業がスムーズにできないで困るケースがあります。

例えば、年月日がセルごとにわかれているのですが、それぞれに、年・月・日という文字も含まれているために、DATE関数をつかって、手早く日付にすることができません。

DATE関数は、年月日に設定できるのが数値型でなければなりません。

「2023年」のように「年」が含まれていると、文字型になってしまいます。

左揃えになっていることからも、文字型になっていることがわかります。

当然、DATE(a2,b2,c2)としても、エラーが表示されてしまうだけで、日付にすることができません。

置換処理で、「年」とかの文字を空白にしてもいいですが、少し面倒です。

そこで、置換することができる関数であるSUBSTITUTE関数をDATE関数と組み合わせてつかうことで、解決することができます。

D2に次の数式を設定します。

=DATE(SUBSTITUTE(A2,"年",""),SUBSTITUTE(B2,"月",""),SUBSTITUTE(C2,"日",""))

オートフィルで数式をコピーしています。

「SUBSTITUTE(A2,"年","")」を説明します。

SUBSTITUTE関数の最初の引数は、文字列なので、A2を設定します。

2つ目の引数は、検索文字列。年という文字を削除したいので、「”年”」と設定します。

3つ目の引数は、置換文字列。年を削除したいので「””」とすることで、年を消すことができます。

これを、「月」と「日」にも同じように設定してきます。

セルに入力されているデータによっては、簡単に日付にすることができないこともありますので、すんなり日付にできない時には、文字型になっていないかを確認するところから始めてみるといいかもしれませんね。

Excel。2023/1/15-1/21にOFFSET関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2023/1/15-1/21にOFFSET関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

1月15日

Excel。

OFFSET関数

読み方は、オフセットで、基準のセルからの相対位置を指定する

1月16日

Excel。

OR関数

読み方は、オアで、複数の条件のいずれか1つを満たすかどうかを調べる

1月17日

Excel。

PDURATION関数

読み方は、ピーデュレーションで、目標価値になるまでの投資期間を算出します。

Period DURATIONの略

1月18日

Excel。

PEARSON関数

読み方は、ピアソンで、ピアソンの積率相関係数を算出します。

1月19日

Excel。

PERCENTILE関数

読み方は、パーセンタイルで、0%以上100%以下の データの百分位数を算出します。

1月20日

Excel。

PERCENTILE.EXC関数

読み方は、パーセンタイル・ エクスクルーシブで、0%より大きくて100%未満の データの百分位数を算出します。

1月21日

Excel。

PERCENTILE.INC関数

読み方は、パーセンタイル・ インクルーシブで、0%以上100%以下の データの百分位数を算出します。

Excel。CUBEMEMBERPROPERTY関数はキューブからメンバーのプロパティの値を返す【CUBEMEMBERPROPERTY】

Excel。CUBEMEMBERPROPERTY関数はキューブからメンバーのプロパティの値を返す

＜関数辞典：CUBEMEMBERPROPERTY関数＞

CUBEMEMBERPROPERTY関数

読み方： キューブメンバープロパティ　　

分類： キューブ 

CUBEMEMBERPROPERTY(接続,メンバー式,プロパティ)

キューブからメンバーのプロパティの値を返す

Excel。VBA。クロス集計のデータ部分だけを削除したいけど、どうしたいい【Delete】

Excel。VBA。クロス集計のデータ部分だけを削除したいけど、どうしたいい

＜Excel VBA: OffsetとResize＞

単純作業になればなるほど、面倒に感じます。

例えば、データ部分だけを範囲選択して、データを削除したい場合です。

B2:D5を範囲選択して、DELキーを押すだけの処理だからこそ、面倒になるわけですね。

そこで、Excel VBAでプログラム文を作ってみることにします。

Sub データ部分削除()

    Dim データ範囲 As Range

    Set データ範囲 = Range("a1").CurrentRegion

    

    データ範囲.Offset(1, 1).Resize(データ範囲.Rows.Count - 2, データ範囲.Columns.Count - 2). Clear

End Sub

実行してみます。

これで、データ部分のみを削除することができます。

プログラム文を確認しておきましょう。

お馴染みの変数宣言ですね。

Dim データ範囲 As Range

Set データ範囲 = Range("a1").CurrentRegion

データ範囲という変数に、A1を起点とする連続したデータを範囲（CurrentRegion）とします。

見出し行も関係なく、表全体を範囲選択している状態です。

これで、Clearすると、全部消えてしまうので、OffsetとResizeをつかって、データ部分だけになるように調整します。

それが、次の行です。

データ範囲.Offset(1, 1).Resize(データ範囲.Rows.Count - 2, データ範囲.Columns.Count - 2). Clear

A1からはじまる「データ範囲」の表を、

1行1列ずらしたところ（Offset(1, 1)）から、

全体の行数から-2した行数の全体の列数から-2した列数（Resize(データ範囲.Rows.Count - 2, データ範囲.Columns.Count - 2)）を、

削除(Clear)するという、プログラム文です。

Excel。CUBEMEMBER関数はキューブからメンバーまたは組を返します。【CUBEMEMBER】

Excel。CUBEMEMBER関数はキューブからメンバーまたは組を返します。

＜関数辞典：CUBEMEMBER関数＞

CUBEMEMBER関数

読み方： キューブメンバー　　

分類： キューブ 

CUBEMEMBER(接続,メンバー式,[キャプション])

キューブからメンバーまたは組を返す

Excel。四捨五入してドル表示できるDOLLAR関数というのがあります。【DOLLAR】

Excel。四捨五入してドル表示できるDOLLAR関数というのがあります。

＜DOLLAR関数＞

表示形式で数値にドルを表示するのではなく、ROUND関数のように指定した桁で四捨五入をして、なおかつ、数値じゃなくて、数値文字列に変更することができるDOLLAR関数というのがあります。

B2に設定した数式は、

=DOLLAR(A2,0)

オートフィルで数式をコピーしています。

A2の100という数値に、ドルマークを表示した文字列にしています。

A3は100.5ですが、$101になっています。

2つ目の引数は、桁数で、「0」にすることで、ROUND関数同様に、整数表示にすることができます。

比較するために、C列は、表示形式でドルマークを表示していますが、DOLLAR関数とことなっています。

なお、DOLLAR関数で算出した結果を計算式でつかうと、ドルマークは無くなります。

Excel。隔週おきに行全体を塗りつぶしたいけど、どうしたらいいの【biweekly】

Excel。隔週おきに行全体を塗りつぶしたいけど、どうしたらいいの

＜条件付き書式：MOD+WEEKNUM関数＞

簡易カレンダーで隔週おきに行全体を塗りつぶしたい時には、どのようにしたら、効率的に作成することができるでしょうか。

Excelの日付には、第何週なのかという情報も含まれています。

第何週なのかを算出できる関数が、「WEEKNUM関数」です。

このWEEKNUM関数で算出した値を2で除算して、余りがあるのか、ないのかを判断すれば、隔週おきに分けることができます。

そして、条件で塗り分けるわけですから、条件付き書式を使えば、対応することができます。

それでは、設定方法を確認していきます。

範囲選択します。今回は、A2:B15が該当します。

ホームタブの条件付き書式にある、「新しいルール」をクリックします。

新しいルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックします。

「次の数式を満たす場合に値を書式設定」のボックスに、数式を設定します。

=MOD(WEEKNUM($A2,1),2)

あとは、書式ボタンをクリックして、書式を設定したらOKボタンをクリックして完成です。

MOD関数は、余りを算出する関数です。

WEEKNUM関数が、該当する日付が第何週に当たるのかを算出する関数です。

ところで、

=MOD(WEEKNUM($A2,1),2)=1

というように、数式のうしろに、「=1」とか「=0」とかをつけていません。

これは、=MOD(WEEKNUM($A2,1),2)が、「1」か「0」のみ算出されます。

「=1」となれば、「1」はExcelでTRUE。

「=0」ならば、「0」でExcelではFALSEと設定されています。

TRUEならば、成立しているということで、条件を満たす。

すなわち、書式設定の対象となるわけです。

そのため、数式のうしろに、「=1」とか「=0」をつけていなくても、大丈夫というわけです。

あと、WEEKNUM関数の最初の引数を「$A2」と列を固定した複合参照にすることで、行全体を塗りつぶすことができます。

条件付き書式と数式を組み合わせることで、日頃使っている資料が、より一層わかりやすくなるかもしれませんので、色々試してみるといいかもしれませんね。