Excel。文字を文字コードに変換できるのがCODE関数です。【CODE】

Excel。文字を文字コードに変換できるのがCODE関数です。

＜関数辞典：CODE関数＞

CODE関数

読み方： コード　　

分類： 文字列操作 

CODE(文字列)

文字を文字コードに変換する　

Excel。5件ごとの小計を算出するなら、ピボットテーブルをつかうのはどうでしょう【subtotal】

Excel。5件ごとの小計を算出するなら、ピボットテーブルをつかうのはどうでしょう

＜ピボットテーブル＞

5件とか10件とか、一定ごとの小計を算出したい場合、どうやって、5件を1組として判断させればいいのか、どの関数をつかったら、簡単に算出できるのかなど、アレコレ考えます。

ただ、算出したいのであれば、関数や小計機能ではなくて、ピボットテーブルをつかうと比較的簡単に算出することができます。

次のデータを元に、ピボットテーブルをつかって、5件ごとの小計を算出してきます。

ピボットテーブルを挿入しますので、表内のセルをアクティブにした状態で、挿入タブの「ピボットテーブル」をクリックします。

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されますので、範囲を確認したら、OKボタンをクリックします。

ピボットテーブルを設定するための新しいシートが挿入されます。

右側に表示されてある、ピボットテーブルのフィールドリストをつかって、NOを行のボックスに、販売金額を、値のボックスに設定します。

あとは、5件ごとにまとめる作業をしていきましょう。

A4などの行ラベルのデータをクリックします。

ピボットテーブル分析タブの「グループの選択」をクリックします。

グループ化ダイアログボックスが表示されます。

5件ごとに小計を算出したいので、単位に「5」と入力をして、OKボタンをクリックします。

これで、5件ごとの小計を算出することができました。

ちょっとした小計などを手早く求めるならば、ピボットテーブルを、つかってみるのも一つの方法ですね。

Excel。データを読み込んだら日付文字列だったので、日付型にしないと不便です【DATEVALUE】

Excel。データを読み込んだら日付文字列だったので、日付型にしないと不便です

＜DATEVALUE関数＞

テキストデータをはじめ、外部からExcelにデータをインポートした時など、日付が日付文字列になってしまっていることがあります。

日付文字列になってしまうと、文字列なので、その日付から10日後などという、数式等々でつかえません。

そこで、「DATEVALUE関数」をつかえば簡単に、日付文字列を日付型に変更してくれます。

A列には、日付文字列で入力された日付が入力されています。

A2をアクティブにした状態で確認してみると、数式バーは、2023/1/25と表示されていません。

つまり日付文字列になっていることがわかります。

ではC2に次の数式を設定します。

=DATEVALUE(A2)

オートフィルで数式をコピーして、C4まで設定すると、「シリアル値」に変換された状態で算出されます。

このDATEVALUE関数は、日付を表す文字列をシリアル値に変換する関数なので、表示形式までは設定してくれません。

算出後、表示形式を変更してあげれば、D列のように、日付にすることができます。

もし、日付型でなく日付文字列になってしまったデータがあった時には、DATEVALUE関数をつかってみるといいかもしれませんね。

Excel。2022/8/15-8/21にFORECAST.LINEAR関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2022/8/15-8/21にFORECAST.LINEAR関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

8月15日

Excel。

FORECAST.LINEAR関数

読み方は、フォーキャスト・リニアで、直線回帰分析による値を算出する

8月16日

Excel。

FORMULATEXT関数

読み方は、フォーミュラーテキストで、数式を文字列で算出します。

8月17日

Excel。

FREQUENCY関数

読み方は、フリーケンシーで、数値の頻度集計【度数分布】

8月18日

Excel。

FTEST関数

読み方は、エフテストで、F検定の両側確率を算出する

8月19日

Excel。

F.TEST関数

読み方は、エフ・テストで、F検定の両側確率を算出する

8月20日

Excel。

FV関数

読み方は、エフヴイ：フューチャー・バリューで、将来の価値を算出する

8月21日

Excel。

FVSCHEDULE関数

読み方は、エフヴイスケジュールで、初期投資の将来価値を算出する

Excel。文字列中に含まれる制御文字を取り除くならCLEAN関数をつかいます【CLEAN】

Excel。文字列中に含まれる制御文字を取り除くならCLEAN関数をつかいます

＜関数辞典：CLEAN関数＞

CLEAN関数

読み方： クリーン　

　

分類： 文字列操作 

CLEAN(文字列)

文字列中に含まれる制御文字を取り除くことができます。

Excel。日曜日～土曜日の週間売上集計を効率よく算出するにはどうしたらいいの【weekly sales】

Excel。日曜日～土曜日の週間売上集計を効率よく算出するにはどうしたらいいの

＜WEEKNUM関数＞

年間売上や月間売上を算出するには、年や月を別枠に設けてあげれば、SUMIF関数などをつかうことで、手早く算出することはできます。

ただ、日曜日から土曜日までの「週間売上」を算出したい場合、どのようにしたら、効率よく算出できるのでしょうか。

年や月のように週ごとにわかるものをどうやって用意するのかが、ポイントになるわけですね。

では、次の表を用意しました。

B2の「土」は、B列の曜日の中で、何回目の登場で、この登場回数が1だけの売上高を合算させればいいという考え方でもいいのですが、もっと簡単に、年間で、第何週なのかを算出する関数があります。

その関数が「WEEKNUM関数」です。

D2には、WEEKNUM関数をつかった数式を設定してあります。

=WEEKNUM(A2,1)

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

2番目の引数「1」は、何なのかというと、「週の基準」です。

今回は、日曜日から土曜日までを週とするので、「1」を採用しました。

この番号がややこしいので、説明しておきます。

1と2は、最新バージョンからExcel2007まで対応しています。

それ以外は、2007は対応していません。

11から17は、理解するのに問題は無いのですが、謎なのが「21」の月曜日。

月曜日は「11」ではと思うかもしれませんが、「21」の月曜日は、システム2の月曜日で、11から17は、システム1なので、「11」はシステム1の月曜日をつかうことを意味しています。

システム1とシステム2の違いはというと、システム1は、1月1日を含む週がその年の第1週としています。

システム2は、最初の木曜日を含む週がその年の第1週としています。

ポップアップヒントで、「（システム1）」と書かれていても、困っちゃいますね。

このようなことから、「17」でもいいのですが、「1」を採用して数式をつくりました。

WEEKNUM関数によって、週番号を算出しましたので、あとは、SUMIF関数をつかって集計すれば完成ですね。

G2の数式は、

=SUMIF($D$2:$D$14,F2,$C$2:$C$14)

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

これで、週ごとである、週間売上合計を算出することができました。

なお、F2:F4には、表示形式をつかって、「週」を表示させることで、SUMIF関数の引数で直接つかっても、算出することができます。

設定方法は、セルの書式設定ダイアログボックスの表示形式タブにある「ユーザー定義」をつかいます。

SUMIF関数で算出できればいいだけなので「G/標準」のうしろに「”週”」を追加してOKボタンをクリックすれば、設定完了です。

Excel関数辞典 VOL.68。RIGHT関数～ROUNDUP関数【dictionary】

Excel関数辞典　VOL.68。RIGHT関数～ROUNDUP関数

＜Excel関数＞

今回は、RIGHT関数～ROUNDUP関数までをご紹介しております。

RIGHT関数

読み方： ライト　　

RIGHT(文字列,[文字数])

文字列の右端から文字を取り出す　

RIGHTB関数

読み方： ライトビー　　

RIGHTB(文字列,[バイト数])

文字列の右端から指定のバイト数を返す　

ROMAN関数

読み方： ローマン　　

ROMAN(数値,[書式])

アラビア数字をローマ数字に変換します　

ROUND関数

読み方： ラウンド　　

ROUND(数値,桁数)

指定桁数で四捨五入する　

ROUNDDOWN関数

読み方： ラウンドダウン　　

ROUNDDOWN(数値,桁数)

指定桁数で切り捨てる　

ROUNDUP関数

読み方： ラウンドアップ　　

ROUNDUP(数値,桁数)

指定桁数で切り上げる　

Access。フィールド内の「カンマ」で区切られた文字列を数えたい【comma】

Access。フィールド内の「カンマ」で区切られた文字列を数えたい

＜Access：Len関数+Replace関数＞

フィールド内に「,（カンマ）」で区切られた複数のデータが入力されています。

フィールド内に何件のデータが入力されているのか把握したいのですが、どのようにしたらいいのでしょうか。

次のテーブルを例に確認していきます。

出張先フィールドには、「,（カンマ）」で区切られた文字列が入力されています。

2022/10/1は、「新宿,品川,秋葉原」と入力されているので、「3」と数えたいわけですね。

フィールドごとに、「,（カンマ）」の数が同じなれば、いいのですが、そういうわけにはいきません。

目視で、「,（カンマ）」を数えるというわけにも当然いきません。

「,（カンマ）」の数を数えることができればいいのですが、そのような関数はありませんし、「,（カンマ）」を残すということもできません。

次のようなアイディアで対応していくことにします。

すべてのフィールドをつかったクエリをつくります。

そして、演算フィールドを追加します。

出張先数: Len([出張先])-Len(Replace([出張先],",",""))+1

演算フィールドは長くなりますので、ズームとかをつかって設定するといいですね。

では、実行して、データシートビューに切り替えて確認してみます。

「,（カンマ）」で区切られた文字列数を算出することができていますね。

では、演算フィールドを説明していきます。

出張先数: Len([出張先])-Len(Replace([出張先],",",""))+1

Len([出張先])は、Len関数をつかって、フィールド内の文字数を数えています。

Len(Replace([出張先],",",""))のReplace([出張先],",","")で、「,（カンマ）」を空白に置換します。

その置換した文字数を数えて、全体の文字数から減算すれば、「,（カンマ）」の数を算出することができます。

「,（カンマ）」の数を算出できたわけですが、例えば「2」と算出された場合は、「文字,文字,文字」というパターンになっているわけですね。

文字数を数えるには「+1」しなければならないことがわかります。

そのため、演算フィールドの最後に「+1」を加えています。

今回は、フィールドが空欄の場合は考慮していませんが、IIF関数を追加するなどで、対応することができます。

Excel。並べ替えしたデータを抽出するにはSORT関数を使うのが便利です。【SORT】

Excel。並べ替えしたデータを抽出するにはSORT関数を使うのが便利です。

＜SORT関数＞

テーブル機能を追加した表を「並べ替え」するだけなら、いつものように並べ替えボタンを使うとか、昇順や降順といったボタンを使えばいいわけです。

ただ、並べ替えを実施した後に、そのデータを抽出というかコピーするとしたら、一度で出来る方が、作業効率的に、わざわざコピー&ペーストしなくても、いいわけですね。

それができるのが、SORT関数です。

次の表を使って説明します。

A1:D11までの範囲はテーブルになっていて、テーブル名を「売上表2月」と設定しています。

このテーブルのD列の売上高フィールドを降順としたデータを抽出したいわけです。

そこで、F2に次の数式を設定します。

=SORT(売上表2月,4,-1)

たったこれだけで、OKです。スピル機能があるので、オートフィルで数式をコピーする必要もありません。

とても便利なSORT関数なのですが、日付型データが含まれている場合、表示形式が解除されてシリアル値に戻ってしまうため、あとで、表示形式を再度設定する必要があります。

あと、SORT関数の引数も確認しておきましょう。

SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])

なお、「並べ替え順序」ですが、昇順は「1」で降順は「0」ではなく「-1」です。

Excel。単一条件の最頻値を算出するには、どうしたらいいの【mode】

Excel。単一条件の最頻値を算出するには、どうしたらいいの

＜MODE.SNGL+IF関数＞

データ内で、一番多く登場している値を算出する時に使用するのが、「MODE.SNGL関数」です。

このMODE.SNGL関数の引数は一つしかないので、「東京にお住いの人」というような条件が付いた場合、MODE.SNGL関数だけでは算出することができません。

では、次の表の場合、どのようにしたらいいのでしょうか。

最初は、F2に全体の最頻値を算出していますので、その数式から確認してきましょう。

F2の数式は、

=MODE.SNGL(C2:C11)

これで、C2:C11の年齢（年代）の中で、一番登場回数が多い数値である最頻値を算出することができました。

結果は、60。

では、地区ごとでの最頻値は、いくつなのでしょうか。

MODE.SNGL関数だけでは、COUNTIF関数のような条件をつけての算出はできません。

引数にIF関数を含めた、MODE.SNGL+IF関数というネストにすることで、対応することができます。

F3には、東日本の最頻値を算出していきます。

F3の数式は、

=MODE.SNGL(IF($B$2:$B$11=E3,$C$2:$C$11,""))

$B$2:$B$11や、$C$2:$C$11は、オートフィルで数式をコピーするので、絶対参照を設定してあります。

算出結果は、「40」で合致しています。

西日本はの算出結果は「60」で、こちらも合致しています。

もしかしたら、MAXIFS関数のように、新たな関数が登場するかもしれませんが、MODE.SNGL関数の引数にIF関数をつかうことで、単一条件での最頻値を算出することができました。

旧来のExcelのバージョンでは、MODE.SNGL関数は配列関数なので、Ctrl+Shift+Enterを確定時に押す必要がありました。

しかし、スピル機能がついたExcelのバージョンでは、そのまま数式をつくっても、算出してくれます。

配列関数で設定する必要があるのか、ないのかは、バージョンに合わせて行えば、MODE.SNGL+IF関数で算出すること自体は変わりません。

Excel。CHOOSEROWS関数は指定された行だけを含む配列を返すことができます。【CHOOSEROWS】

Excel。CHOOSEROWS関数は指定された行だけを含む配列を返すことができます。

＜関数辞典：CHOOSEROWS関数＞

CHOOSEROWS関数

読み方： チューズロウズ　　

分類： 検索/行列 

CHOOSEROWS(array,row_num1,[row_num2],…)

指定された行だけを含む配列を返します　

Excel。2022/8/8-8/14にFLOOR.MATH関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2022/8/8-8/14にFLOOR.MATH関数など紹介したFacebookページのコメントです

＜Facebookページ＞

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

8月8日

Excel。

FLOOR.MATH関数

読み方は、フロア・マスで、指定した数値の倍数に切り捨てる

8月9日

Excel。

FLOOR.PRECISE関数

読み方は、フロア・プリサイズで、指定した数値の倍数に切り捨てる

8月10日

Excel。

FORECAST関数

読み方は、フォーキャストで、直線回帰分析による値を算出する

8月11日

Excel。

FORECAST.ETS関数

読み方は、フォーキャスト・イーティーエスで、実績から予測値を算出します。

8月12日

Excel。

FORECAST.ETS.CONFINT関数

読み方は、フォーキャスト・イーティーエス・コンフィデンスインターバルで、予測値の信頼区間を算出する

8月13日

Excel。

FORECAST.ETS.SEASONALITY関数

読み方は、フォーキャスト・イーティーエス・シーズナリティで、指定した時系列の季節パターンの長さを算出する

8月14日

Excel。

FORECAST.ETS.STAT関数

読み方は、フォーキャスト・イーティーエス・スタットで、時系列予測から統計情報を算出します。

Excel。範囲内に同じ文字や数値を、楽に入力したいので、何かいい方法はないの【range】

Excel。範囲内に同じ文字や数値を、楽に入力したいので、何かいい方法はないの

＜Ctrl + Enter：一括入力＞

Excelの入力業務など、繰り返し作業など、単純作業になればなるほど、面倒に感じてしまうので、できることなら、手早く済ませたいことは多くあります。

例えば、次のように、範囲内に、同じ「済」という文字を入力したい場合です。

入力したい範囲が広がれば広がるほど、オートフィルでコピーするとしても面倒です。

このようなケースの場合、ちょっとしたことを知っているだけで、あっという間に入力することができます。

まず、入力したい箇所を範囲選択します。文字や数値を入力します。

ここで、「Ctrlキー」を押しながら「Enterキー」を押します。

つまり、Ctrl+Enterです。

 

たった、これだけですが、範囲選択したところに、同じ文字や数値を入力することができます。

Excelにも、意外な小技テクニックがありますので、ちょっと知っているだけでも作業効率が改善できるかもしれませんね。

Excel。上位3位までのデータがわかりやすいように、行全体を塗りつぶしたい【line fill】

Excel。上位3位までのデータがわかりやすいように、行全体を塗りつぶしたい

＜条件付き書式＋LARGE関数＞

上位1位から3位までのデータを視覚的にわかりやすくした表を作りたいとします。

フィールド数（見出し列数）が多い場合には、データのセルだけを塗りつぶすよりも、レコード（行）全体を対象に塗りつぶしをしたほうが、いいと思うわけですね。

次のような表を作りたいわけです。

手早く設定するには、当然、目視で確認するわけではなく、条件付き書式をつかうわけです。

問題になるポイントが、どのような数式を設定すればいいのかということですね。

条件付き書式に用意させているものは、対象になるセルにしか、書式を反映することができないので、行全体と考えた時点で、条件付き書式の条件は数式を使わないといけないわけですね。

上位3位ということなので、RANK.EQ関数で順位を算出してもいいのですが、一度順位を算出した列をつくらないといけないので、効率的にちょっと、オススメできません。

そこで、上位3位の数値を算出させて、算出結果より以上の数値ならば、上位3位に入ることがわかります。

上位何番目というのを算出する時につかうのが「LARGE関数」です。

では、条件付き書式を設定していきますので、A2:B16を範囲選択して、ホームタブの条件付き書式にある、「新しいルール」をクリックします。

すると、「新しい書式ルール」ダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックして、「次の数式を満たす場合に値を書式設定」のボックスに、次の数式を設定します。

=$B2>=LARGE($B$2:$B$16,3)

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定します。

これで、上位1位～3位のデータを行全体で塗りつぶすことができます。

設定した数式を説明しておきましょう。

=$B2>=LARGE($B$2:$B$16,3)

「$B2」と列側だけに絶対参照にする「複合参照」にすることで、行全体を塗りつぶすことができます。

あとは、LARGE関数の引数ですが、最初の引数は、配列なので範囲のことです。

絶対参照を忘れないように設定します。

そして、何位という順位を次に設定します。

上位3位のデータを算出したいので、「3」と設定すればいいわけですね。

このように、条件付き書式の条件に数式をうまく取り入れることで、様々な表現を帳票に組み込むことができます。

今回のように、色々試してみると、よりよい帳票や資料が作れるかもしれませんね。

Excel。新しい関数VSTACK関数は複数の表を、手早く一つに結合できます【join table】

Excel。新しい関数VSTACK関数は複数の表を、手早く一つに結合できます

＜VSTACK関数＞

次のように、複数の表を一つにまとめるならば、コピーして貼り付けるだけの作業かもしれませんが、貼り付ければ貼り付けるほど、下方向に移動しないといけないので、面倒ですね。

そこで、新しく登場した「VSTACK関数」を使うと、簡単に複数の表を結合することができます。

A4:B6とD4:E6の範囲を一つの表として結合するには、A9に次の数式を設定するだけです。

=VSTACK(A4:B6,D4:E6)

オートフィルで数式をコピーする必要もありません。

スピル機能のおかげで、勝手に範囲が拡張されます。

とても便利ではありますが、残念なことに、まだ多くのExcelには搭載されていません。

Office Insider BataのWindows2203(Build　15104)以降である必要があります。

早く、Microsoft365のExcelに搭載されるといいですね。そのため、引数も英語表記です。

VSTACK関数（読み方： ブイスタック）の引数は、

VSTACK(array1,[array2],…)

となっています。

Excel。日付を元号（和暦）表示するDATESTRING関数というのがあるんです。【Japanese Calendar】

Excel。日付を元号（和暦）表示するDATESTRING関数というのがあるんです

＜DATESTRING関数＞

Excel関数のクイズ大会なんてものがあれば、お題になりそうなのが、今回紹介する「DATESTRING関数」じゃないかと思います。

その理由は、満年齢や勤続年数を算出する「DATEDIF関数」と同じで、関数挿入ダイアログボックスに表示されないし、手入力でもフォローがない、【隠し関数】なんですね。

使い方はシンプルなのですが、日付をグレゴリオ暦から和暦にするならば、表示形式でいいのでは？という関数なので、DATEDIF関数に比べて使用頻度も少ないように思えます。

では、実際にDATESTRING関数をつかってみます。

A2の日付を直接、B2に元号（和暦）で表示することができます。

B2の数式は、

=DATEDTRING(A2)

「1月」ではなくて、「01月」というように、ゼロ付になっているのも特徴ですね。

また、A5のように、シリアル値を直接、元号（和暦）で表示することもできます。

Excel VBAとかならば、使うことはあるかもしれませんが、なかなか使うことは少ないかもしれませんが、「DATESTRING関数」という関数もあるんですね。

DATESTRING関数（読み方： デイトストリング）

DATESTRING(シリアル値)

Excel。テーブルで、重複データを除いたリストをつくるには、UNIQUE関数が便利です。【overlapping】

Excel。テーブルで、重複データを除いたリストをつくるには、UNIQUE関数が便利です。

＜UNIQUE関数＞

A1:B9には、担当者リストというテーブル名を設定したテーブルがあります。

このB列の担当者から、重複データを除いたリストを、手早く作るとしたらどうしたらいいでしょうか。

データタブにある「重複の削除」ボタンをクリックして、重複を削除するのも悪くありませんが、「重複の削除」は、重複データを削除した状態にしてしまいます。

つまり、元の表を残して、重複データを除いたリストを作る場合には「重複の削除」では、少し面倒な処理を必要とします。

そこで、UNIQUE関数をつかうことで、簡単に重複データを除いたリストをつくることができます。

しかも、スピル機能によって、オートフィルで数式をコピーする必要もありません。

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

=UNIQUE(担当者リスト[担当者],FALSE,FALSE)

これだけで、重複データを除いたリストを作成することができます。

引数を確認しておきましょう。

最初の引数は、配列です。テーブル名[フィールド名]で設定します。

2つめの引数は、列の比較。

TRUEだと、一意の列を返します。

FALSEだと、一意の行を返します。

今回は、行なので、FALSEと設定します。

3つめの引数は、回数指定。

TRUEだと、1回だけ出現するアイテムを返します。

つまり、重複していないデータのみを抽出します。

FALSEだと、個別のアイテムをすべて返す。

重複データを除いたリストをつくりたいので、FALSEを設定します。

UNIQUE関数は、比較的新しい関数なので、色々試してみるといいかもしれませんね。

Excel。2022/8/1-8/7にFINV関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2022/8/1-8/7にFINV関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

8月1日

Excel

FINV関数

読み方は、エフインバースで、F分布の上側確率から確率変数を算出する

8月2日

Excel

F.INV関数

読み方は、エフ・インバースで、F分布の下側確率から確率変数を算出する。

8月3日

Excel

F.INV.RT関数

読み方は、エフ・インバース・ライトテールで、F分布の上側確率から確率変数を算出する。

8月4日

Excel

FISHER関数

読み方は、フィッシャーで、フィッシャー変換の値を算出します。

8月5日

Excel

FISHERINV関数

読み方は、フィッシャーインバースで、フィッシャー変換の逆関数の値を算出します。

8月6日

Excel

FIXED関数

読み方は、フィクストで、数値を四捨五入してカンマを使った文字列に変換する

8月7日

Excel

FLOOR関数

読み方は、フロアで、指定した数値の倍数に切り捨てる

Excel。CHOOSECOLS関数は指定された列だけを含む配列を返します【CHOOSECOLS】

Excel。CHOOSECOLS関数は指定された列だけを含む配列を返します

＜関数辞典：CHOOSECOLS関数＞

CHOOSECOLS関数

読み方： チューズコルズ

読み方： チューズカラムズ

分類： 検索/行列 

CHOOSECOLS(array,col_num1,[col_num2],…)

指定された列だけを含む配列を返します　

Excel。勤続年数や満年齢を算出するならDATEDIF関数をつかいます。【DateDifferrence】

Excel。勤続年数や満年齢を算出するならDATEDIF関数をつかいます。

＜DATEDIF関数＞

日付に関係する関数は、Excelにもたくさんありますが、便利なのに、ちょっと困った関数があります。

それが、勤続年数や満年齢を算出することができる、DATEDIF関数です。

なぜ、困った関数なのかというと、関数挿入ダイアログボックスがないからです。

つまり、手入力で、数式を作らないといけないというわけです。

ただ、引数自体は簡単なので、次の表を使って確認します。

C2に設定した数式は、

=DATEDIF(A2,B2,"y")

A2には、誕生日の日付が入力されています。

B2には、今日の日付が入力されています。

年だけで、減算してしまうと、22になってしまうので、単純に算出することはできません。

そこで、DATEDIF関数をつかうわけです。

DATEDIF関数の引数は、

DATEDIF(開始日,終了日,単位)

となっています。

今回は、開始日が、A2で、終了日がB2。

単位というのは、満年齢なら「Y」とか、月ならば「M」などが用意されています。

何ヶ月も算出したい場合には、「YM」という単位も用意されています。

満年齢を算出したいので、単位は「”Y”」となるわけです。

このように、2つの日付の間の年・月・日数を算出することができるのが、DATEDIF関数です。

ところで、読み方は、DateDifferrenceの略なので、「デイトディフ」です。

簡単な関数ではありますが、開始日を終了日が超過しないと、「1」となりませんので、注意が必要です。

Excelの様々な関数を紹介 VOL.67。RECEIVED関数～REPT関数【dictionary】

Excelの様々な関数を紹介　VOL.67。RECEIVED関数～REPT関数

＜Excel関数辞典＞

今回は、RECEIVED関数～REPT関数までをご紹介しております。

RECEIVED関数

読み方： レシーブド　　

RECEIVED(受渡日,満期日,投資額,割引率,[基準])

割引債の償還価格を算出します　

REDUCE関数

読み方： リディース　　

REDUCE(initial_value,array,function)

LAMBDA関数を各値に適用し、アキュムレータの合計値を返すことによって、配列を累積値に減らします

REPLACE関数

読み方： リプレイス　　

REPLACE(文字列,開始位置,文字列,置換文字列)

指定した文字数の文字列を置換する　

REPLACEB関数

読み方： リプレイズビー　　

REPLACEB(文字列,開始位置,バイト数,置換文字列)

指定した位置からバイト数分の文字列を置換する　

REPT関数

読み方： リピート　　

REPT(文字列,繰り返し回数)

文字列を指定回数だけ繰り返して表示する　

Excel。データ内の空白セルだけに、効率よく文字を入力したいけど、どうしたらいい【replacement】

Excel。データ内の空白セルだけに、効率よく文字を入力したいけど、どうしたらいい

＜空白セルを置換＞

データ内の空白セルだけに、文字を入力したいのですが、どうやったら、手早く処理することができるのでしょうか。

このように、簡単そうな処理を行おうとしても、思ったよりも面倒なので、楽に処理したいというケースがあります。

次の表をつかって確認していきます。

B列の提出で、空白セルに「済」という文字を入力したいわけです。

先頭のセルからオートフィルで「済」という文字をコピーすると、すべて同じ文字になってしまうので、コピー&ペーストで対応するとしても、データが多ければ、面倒でしかありません。

オートフィルターや、Excel VBAでマクロをつくるほどのことでもないですし。

色々な方法はありますが、今回は置換で対応するのが、いいかと思います。

B2:B8を範囲選択します。

ホームタブの検索と検索にある置換をクリックして、検索と置換ダイアログボックスを表示します。

検索する文字列は、空白セルを置換したいので、そのままにします。

置換後の文字列に、「済」と入力します。

あとは、すべて置換ボタンをクリックします。

これで、空白セルに、文字を入力することができましたね。

Excel。セルごとに分割された数値を合算し、その値もセルごとに分割したい【cell merging】

Excel。セルごとに分割された数値を合算し、その値もセルごとに分割したい

＜CONCAT・SUM・IFERROR・MID・LEN・COUNT関数＞

なんでそんなことしちゃったのという、Excelの帳票をみることがあります。

例えば、数値をセルごとに分割してある帳票。

そもそも、1500と分割しないでセル内にあれば、単純な和算で済むわけですが、セルごとに分割してあるわけですね。

このような場合、一の位で合算して、繰り上がったら…なんてやっていたら、桁数が増えたら大変というか、数式を考えるだけでも大変です。

最初にやることは、結合して1つのセルにまとめて数値にします。

A6に次の数式を作り、交通費を一つのセルにまとめます。

A6に設定した数式は、

=CONCAT(B2:G2)

同じように、A7に、宿泊費を一つのセルにまとめます。

A8には、合算値を算出したいので、A8に設定する数式は、

=SUM(A6:A7)

ところが、「0」と算出されてしまいました。

原因は、よくみると、A6とA7は、左揃えになっています。

これは文字型になっていることを表しています。

CONCAT関数は、文字結合をする関数なので、算出されたものは、文字になっていて数値ではないわけです。

そこで、A6とA7の数式のうしろに「*1」を追加します。

=CONCAT(D2:G2)*1

こうすると、文字型は文字型でも、数値文字型に変わるので、合算値を算出することができます。

あとは、どうやって、一つずつのセルに分割して表示させる数式を考えていきます。

合計のB4の数式は、

=IFERROR(MID($A$8,LEN($A$8)-COUNT(C1:$G$1),1)*1,"")

あとは、この数式をG4まで、オートフィルで数式をコピーすれば完成です。

数式が長くなったので、説明していきます。

B1:G1までの見出し行は、「十万・一万…一」と表示してますが、元は、普通の数値が入力してあって、表示形式をつかって、漢数字にしています。

別に直接、漢数字で入力しても問題はありません。

最初の「IFERROR関数」は、数値がない場合、#VALUEというエラーが発生するので、エラーならば空白にするようにしています。

MIDはセル内の文字列を指定の場所にある、指定した文字数分だけ抽出する関数です。

先程、合算値を算出したA8を指定します。

オートフィルで数式をコピーしますので、絶対参照を忘れずに設定します。

何文字目の文字なのかを指定します。

「LEN($A$8)-COUNT(C1:$G$1)」

LEN関数は、文字数を算出する関数です。これで合算値が何桁なのかを算出します。

その値から、C1:G1の数値の個数を算出した値を減算します。

桁数をずらす必要があるので、終点のみを絶対参照に設定しておきます。

今回の例としては、

LEN($A$8)は、「5」

COUNT(C1:$G$1)は、「5」

5-5=0なので、0文字目を抽出ということになるのですが、0文字目を抽出というのは、おかしいので、エラーが表示されるわけですね。

このように、ちょっと複雑な数式になっていますね。

帳票上の必要があるかもしれませんが、基本的には、数値を一つずつ、一つのセルごとに分割する場合には、アレコレ、アイディアが必要になりますね。

Excel。テーブルとスピル機能で、必要な列だけを簡単に抽出することができます。【Table】

Excel。テーブルとスピル機能で、必要な列だけを簡単に抽出することができます。

＜テーブルとスピル機能＞

Microsoft365のExcelやExcel2021にあるスピル機能ですが、テーブルと相性がいいので、ちょっとしたことを知っていると、便利に使うことができます。

例えば、次のテーブルがあります。

A1:D11に「売上表」というテーブル名をつけたテーブルがあります。

このテーブル全体をデータをコピーというか抽出したい場合、通常ならば、範囲選択してコピー&ペーストという流れだと思いますが、スピル機能によって、テーブル名を参照させるだけで、OKになりました。

では、F2に次のように数式を設定します。

=売上表

確定すると、F2を起点として、テーブルデータがコピー（抽出）することができました。

G列は、日付なので、シリアル値に戻ってしまっていますが、大きなデータを別シートなどにコピーしたい時には、テーブル名参照だけで、完了することができます。

また、特定の列（フィールド）だけコピー（抽出）したい場合は、テーブル名参照にフィールド名を合わせることで、簡単に抽出できます。

F2には、

=売上表[日付]

G2には、

=売上表[売上高]

と設定するだけです。

なお、手入力だと、テーブルのフィールド名が一覧で表示されるので、選ぶのは簡単になっています。

このように、スピル機能が追加されたことで、いままで行っていた作業が、より効率的に処理することができるようになったかもしれませんので、確認するといいかもしれませんね。

Excel。CHOOSE関数は、引数リストの何番目かの値を抽出することができます。【CHOOSE】

Excel。CHOOSE関数は、引数リストの何番目かの値を抽出することができます。

＜関数辞典：CHOOSE関数＞

CHOOSE関数

読み方： チューズ　　

分類： 検索/行列 

CHOOSE(インデックス,値1,[値2],…)

引数リストの何番目かの値を抽出します　

Excel。2022/7/25-7/31にFALSE関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2022/7/25-7/31にFALSE関数など紹介したFacebookページのコメントです

＜Facebookページ＞

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

7月25日

Excel。

FALSE関数

読み方は、フォルスで、FALSEを返す

7月26日

Excel。

FDIST関数

読み方は、エフディストで、F分布の右側（上側）確率を算出する

7月27日

Excel。

F.DIST関数

読み方は、エフ・ディストで、F分布の確立を算出する

7月28日

Excel。

F.DIST.RT関数

読み方は、エフ・ディスト・ライトテールで、F分布の右側（上側）確率を算出する。

7月29日

Excel。

FILTERXML関数

読み方は、フィルターエックスエムエルで、Webサービスからのデータを返す

7月30日

Excel。

FIND関数

読み方は、ファインドで、検索する文字列の位置を算出する

7月31日

Excel。

FINDB関数

読み方は、ファインドビーで、検索する文字列の位置をバイト数で算出する

Excel。VBA。太い外枠で中は格子の罫線を毎回設定するのが面倒なんです【Ruled line】

Excel。VBA。太い外枠で中は格子の罫線を毎回設定するのが面倒なんです

＜VBA：罫線＞

単純な処理を毎回毎回設定するのは、とても面倒です。

それが単純になればなるほどです。

例えば、作成した表の罫線を設定する処理も、そのうちの一つだといえます。

B2:E7に表があります。

この表に、外側は、「太い外枠」で設定します。

内側は、「格子」の罫線で設定したい場合、範囲選択をしてホームタブにある罫線ボタンから「格子」を選択して、再度「太い外枠」を選択しなければなりません。

マクロの記録でつくっては、表の大きさが変わってしまうと、対応できませんので、表の大きさが変わっても対応できるように、Excel VBAでプログラムをつくります。

Sub 罫線外太内格子()

    Range("b2").CurrentRegion.Borders.LineStyle = True

    Range("b2").CurrentRegion.BorderAround Weight:=xlThick

End Sub

たった、この2行だけで、外側は、「太い外枠」で設定して、内側は、「格子」の罫線を設定できます。

まずは実行して確認してみましょう。

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

「Range("b2").CurrentRegion.Borders.LineStyle = True」

「Range("b2").CurrentRegion」は、B2を起点として連続するセル、つまりB2を起点とする表を選択しています。

「Borders.LineStyle = True」は、Bordersは、「線」で「LineStyle = True」で「格子」を設定することができます。

2行目も確認します。

「Range("b2").CurrentRegion.BorderAround Weight:=xlThick」

「BorderAround Weight:=xlThick」の「BorderAroundメソッド」は、外枠についてです。

その外枠を「Weight:=xlThick」と設定することで「線を太く」することができます。

たった2行ですが、作業効率も改善しますし、何よりも面倒な処理が緩和できるのが魅力ですね。

今回は、簡単な罫線の表でしたが、線種や色に太さなどに加えて、上下左右を詳細に設定することができます。