【Excel】日付からTEXT関数で曜日・和暦へ変換する方法

主に日付を文字列結合する場合、結合後の日付がシリアル値になってしまう為、TEXT関数を利用しフォーマットを指定し結合する事が多いと思います。

フォーマットの指定方法を工夫することで曜日や和暦に変更することが出来る為、よく使うフォーマットを紹介します。

TEXT関数とは

TEXT関数を使用することで表示形式コードを使用して数値に書式設定を適応する事が出来ます。

通常、セルの書式設定で行う表示形式コードであれば同様に使用することが可能です。

TEXT関数の書式

TEXT関数は次のようになります。

=TEXT(書式を適応する値, 適応する表示形式コード)

TEXT関数をで曜日や和暦に変更するには

本題になりますが、指定した日付から曜日を表示させたい場合、表示形式コードで "aaa" または "aaaa" を指定します。

"aaa" では曜日のみ（月) ”aaaa"では曜日まで(月曜日) のように変化する事が出来ます。

普段、使う上で一番よく使う形式と思いますので覚えておきましょう。

又、西暦から和暦へ変換したい場合、"ggge" を指定します。

このgggは今で言う令和を意味しeは和暦の年数を意味します。

その為、gggとeは繋げて使う事が一般的です。

普段、よく使う日付に関する表示形式は以下の通りです。

特に動的に曜日や日付を更新する必要がある場合に役に立つため、取り入れて使ってみてください。

Microsoft 365 Personal(最新 1年版)|オンラインコード版|Win/Mac/iPad|インストール台数無制限(同時使用可能台数5台)

よくわかる Microsoft 365使いこなし術

業務改善コンサルタントの現場経験を一冊に凝縮した Excel実践の授業

上手に時短できる　Excel仕事の教科書 (明日に活きるビジネスガイド)

【Excel】LAMBDA関数へ配列を渡すには BYROW / BYCOL

MAP関数はラムダヘルパー関数の一種です。

ラムダ関数とは？って方は初めにラムダ関数についてもまとめてますので先にそちらを確認してください。

tec.k3nw.com

BYROW / BYCOL　関数とは

BYROW 及び BYCOL 関数では行列で区切られた配列を LAMBDA 関数へ渡す時に使います。

BYROWは行で区切られた配列

BYCOLは列で区切られた配列

をLAMBDA関数へ渡します。

行列単位で処理を行いたい場合、 BYROW / BYCOL 関数を利用することで処理を簡素化することが出来ますね。

Microsoft 365 Personal(最新 1年版)|オンラインコード版|Win/Mac/iPad|インストール台数無制限(同時使用可能台数5台)

Microsoft 365 Family(最新 1年版)|オンラインコード版|Win/Mac/iPad|利用可能人数最大6人

Excel 最強の教科書[完全版] 【2nd Edition】

たった1日で即戦力になるExcelの教科書【増強完全版】

【Excel】MAP関数でループ処理を行うには

MAP関数はラムダヘルパー関数の一種です。

ラムダ関数とは？って方は初めにラムダ関数についてもまとめてますので先にそちらを確認してください。

tec.k3nw.com

MAP 関数とは

MAP 関数は選択したセルの範囲をループ処理し新しい値に形成した配列を返します。

何言ってるか分からないかもですが動作を確認すれば単純で便利な関数です。

構文

= MAP (array, [array, ...] lambda)

MAP 関数では次の引数とパラメーターがあります。

array マップする配列です。lamda関数へ複数の配列を渡す必要がある場合、複数指定します。

lambda　 計算するlambda関数を入力します。

使い方

例として下記の商品リストがあります。

商品ごとに合計金額を出したい場合の記入例です。

式はE2へ入力を行いE3:E5に関してはスピルで表示されているゴーストです。

LAMBDA関数では価格と個数を掛け合計金額を返します。

MAP関数で価格にB2:B5、個数でC2:C5を指定してます。

この場合、LAMBDA関数では

LAMBDA(price, num, price * num)(B2, C2)

LAMBDA(price, num, price * num)(B3, C3)

LAMBDA(price, num, price * num)(B4, C4)

LAMBDA(price, num, price * num)(B5, C5)

のように繰り返し処理され戻り値が戻されます。

これ以上の機能はないので使ってみると簡単ですよね。

ただし、複数の配列を指定する場合、同じ数を指定しないとN/Aになります。

又、リストをテーブルにすることでデータを追加されたときなど自動的に更新することも可能です。

上記、例の場合

=テーブル2[価格]*テーブル2[個数]

と入力することでスピル機能で同様の結果が得られますが、LAMBDA関数側で複雑な処理が必要な場合に利用するのがお勧めです。

Microsoft 365 Personal(最新 1年版)|オンラインコード版|Win/Mac/iPad|インストール台数無制限(同時使用可能台数5台)

ひと目でわかるMicrosoft 365 ビジネス活用編

よくわかる Microsoft 365使いこなし術

【Excel】二重集計回避・エラー無視して集計を行う AGGREGATE関数

AGGREGATE関数とは

AGGREGATE関数はSUBTOTAL関数の上位互換の関数でです。

と言うと、SUBTOTAL関数ってなに？って言われる方も多いかと思います。

簡単にいうと SUM とか AVERAGE などの関数に二重集計回避の機能が付いたものが SUBTOTAL 関数です。

SUBTOTAL 関数にエラー回避や非表示行の除外に8種類の関数が対応したのが AGGREGATE 関数です。

SUBTOTAL 関数を利用するぐらいなら AGGREGATE 関数を利用することとお勧めします。

又、Excel 2010 から使える為、バージョン混在環境でもさほど気にしなくても問題ないと思います。

AGGREGATE 関数で何ができる？

主な機能をあげると下記3つの機能があります

• 二重集計の回避
• エラー値の無視
• 非表示行の無視

2重集計の回避

下記サンプルにて説明します。

SUM でまとめてしまった場合、小計も含んでしまい合計が間違った値になってしまいます。

合計を個別に足せば済む話ですか項目が多くなれば手間もかかり間違えも発生します。

代わりに SUBTOTAL と　AGGREGATE を利用すれば小計を除外して合計を出す事が出来ます。

エラー値の無視

下記サンプルにて説明します。

VLOOKUP などで単価を取得しているとして、商品B2が検索先で抜けてる場合、N/A エラーが返されます。

エラーが含まれる計算式の結果は基本エラーになりますが AGGREGATE に関してはエラーを無視して計算することが出来ます。

非表示行の無視

下記サンプルにて説明します。

商品A2の行を非表示にした場合でも、SUM 及び SUBTOTAL では非表示の行を含んだ結果が返されます。

AGGREGATE では非表示行を除外した結果が返されている事が分かると思います。

AGGREGATE 関数の使い方

一番よく使うのは SUM になると思います。　その為、細かいことは置いておいて

AGGREGATE(9, 3, 範囲)

と覚えておけば事足ります。

細かな使い方は下記の通りです。

AGGREGATE(集計方法, オプション, 範囲 1, [範囲 2], …)

集計方法には該当の関数の番号を入力します（SUM の場合は 9)。

関数の種類は以下の通りです。

1    AVERAGE(平均)

2    COUNT(数値を含むセルの個数)

3    COUNTA(空白でないセルの個数)

4    MAX(最大値)

5    MIN(最小値)

6    PRODUCT(積)

7    STDEV.S(標準偏差の推定値)

8    STDEV.P(母集団の標準偏差)

9    SUM(合計)

10    VAR.S(不偏分散)

11    VAR.P(分散)

12    MEDIAN(中央値)

13    MODE.SNGL(最頻値)

14    LARGE(n番目に大きい値)

15    SMALL(n番目に小さい値)

16    PERCENTILE.INC(百分位数)

17    QUARTILE.INC(四分位数)

18    PERCENTILE.EXC(0%と100%を除いた範囲の百分位数)

19    QUARTILE.EXC(0%と100%を除いた範囲の四分位数)

オプションに関しては下記の通りです。

細かく指定する事も出来ますが基本、全部入りの指定しか使わないと思いますので 3 以外使うことはほぼありません。

0 ネストされた SUBTOTAL と AGGREGATE を無視します。

1 非表示の行、ネストされた SUBTOTAL と AGGREGATE を無視します。

2 エラー値、ネストされた SUBTOTAL と AGGREGATE を無視します。

3 非表示の行、エラー値、ネストされた SUBTOTAL と AGGREGATE を無視します。

4 何も無視しません。

5 非表示の行を無視します。

6 エラー値を無視します。

7 非表示の行とエラー値を無視します。

Microsoft 365 Personal(最新 1年版)|オンラインコード版|Win/Mac/iPad|インストール台数無制限(同時使用可能台数5台)

Microsoft 365 Family(最新 1年版)|オンラインコード版|Win/Mac/iPad|利用可能人数最大6人

いちばん詳しいExcel関数大事典　増補改訂版

Excel関数＋組み合わせ術　［実践ビジネス入門講座］【完全版】 第2版 作業効率とクオリティがいっきに高まる究極の使いこなしテクニック 【Excel 2021/2019/Microsoft 365対応】 [実践ビジネス入門講座]

【Excel】ラムダ関数でオリジナル関数を作成する方法

ラムダ関数とは、関数を簡潔に記述するための構文です。無名関数（匿名関数）とも呼ばれます。

Microsoft Excel 365 からラムダ関数が利用できるようになりました。

現状、使えるバージョンは以下の通りです。

• Excel for Microsoft 365
• Excel for Microsoft 365 for Mac
• Excel for the web

ラムダ関数を使う上での注意

ラムダ関数を利用してオリジナル関数を作る事で計算式の記入を簡素化することが出来ます。

但し、会社など複数のユーザーが利用するExcelで使用すると分かっていない方が真似してオリジナル関数を別のブックで利用して関数が使えないとかクレームが来たりしますので対応したくないらなら使わない方がいいです。

又、使えるExcelのバージョンが限られる為、いくつかのバージョンが混在している環境ではトラブルの元になりますので注意が必要です。

ラムダ関数の利点

ラムダ関数は VBA やマクロが必要ないため、プログラマー以外でも使用できます。

よく使用される数式を関数にして作成しておく事で数式をコピーして貼り付ける必要をなくしメンテナンス性を向上する事が出来ます。

ラムダ関数の使い方

ラムダ関数の引数は以下の通りです。

=LAMBDA([パラメータ1,パラメータ1, …,] 計算）

パラメータについては最大253個まで指定することが出来ます。　又、引数は省略可能です。

計算式は関数の結果として実行し返す数式です。結果は必ず返す必要があります。

仮に税込み金額を求める数式をラムダ関数で関数にしてみます。

計算式は　価格 * 税率 + 価格 で税込み金額が求められます。

ラムダ関数で記入する場合、

=LAMBDA(price, taxrate, price * taxrate + price)

との様に記入します。

数式を展開すると下記の通りです。

価格(C3) をパラメータ priceへ代入

税率(D3)をパラメータ taxrateへ代入

計算式として 価格 * 税率 + 価格で計算を行う

関数として登録する

このままではただの数式と変わらない為、関数として登録する必要があります。

登録する方法は引数を除いた式（ここでは=LAMBDA(price, taxrate, price * taxrate + price)になる)をコピーしておきます。

続けてメニュータブから数式 -> 名前の定義を選択します。

名前にはこの式の関数名を指定します。　ここではintaxとして登録します。

コメントでは式を入力するときに出てくる説明文です。　簡潔に書いておく事をお勧めします。

最後に参照範囲にコピーした式を貼り付けOKで登録完了です。

登録されてるラムダ関数の確認方法

登録されているラムダ関数の確認方法が下記の通りです。

メニュータブから 数式 -> 名前の管理を選択することで確認できます。

又この画面から関数の削除及び編集が行えます。

Microsoft 365 Personal(最新 1年版)|オンラインコード版|Win/Mac/iPad|インストール台数無制限(同時使用可能台数5台)

【対象商品と同時購入限定】Microsoft 365 Personal(15ヶ月版)|オンラインコード版

Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術

【Excel】長い数式を書きやすく見やすくする方法 (Excel Labs)

Excel で数式を書いてると長い数式を書く事があります。

特に IF 文並べたり文字列の抜き出しをしてるときとかに。

関数バーを広げて改行いれて書けばそれなりに見やすくはなりますが長ければ長いほど見にくくなりますよね。

そんな時にアドインの Excel Labs を利用すると数式の入力がしやすくなります。

正確になExcel Labs の Advanced formula environment を使用します。

下記にアドインのインストール方法と簡単な使い方を紹介します。

注意事項ですが、サポートされているExcelのバージョンが少ないです。

サポートされているバージョンは以下の通り。

• Excel 2019 or later on Mac
• Excel on Mac (Microsoft 365)
• Excel on Windows (Microsoft 365)
• Excel on the web

ここからはアドインのインストールと使い方について説明します。

初めにホームタブから アドイン を選択し アドインを取得 をクリックします。

Office アドイン画面が表示されるのでそこで "excel labs" と入力して検索してください。

検索結果から "Excel Labs, a Microsoft Garage project" を追加してください。

追加後、右画面にExcel Labsが表示されているはずです。

そこから Advanced formula environment を Open してください。

以上でインストールを完了です。

完了後、右画面にExcel Labsが表示されていますのでタブからGridを選択してください。

数式を入力したいセルを選択して、ここに数式を記入していきます。

普段、プログラムをエディタで書かれてる方なら違和感なく使えるはずです。

入力後、そのままではセルに反映されないので左上の保存マークをクリックすることによりセルに反映されます。

サンプルとして3つの IF 文を並べてみました。

関数バーで入力をしてもいいのですが見にくくパット見た目で分かりにくいですがExcel Labs 側では非常に見やすくなっています。

改行を入れても記述が簡単に行える為、長い数式を書く場合、効率があがります。

他にもラムダ関数の登録を簡単に行う機能もありますが、別途説明を行いたいと思います。　

一度、触ってもらえれば便利さが実感できると思いますので是非インストールを行ってみて下さいね。

Microsoft 365 Personal(最新 1年版)|オンラインコード版|Win/Mac/iPad|インストール台数無制限(同時使用可能台数5台)

Microsoft 365 Family(最新 1年版)|オンラインコード版|Win/Mac/iPad|利用可能人数最大6人

【Excel】平均値、中央値、最頻値の求め方と特徴

平均値、中央値、最頻値は、いずれもデータの中心的な傾向を示す値ですが、それぞれの特徴には違いがあります。

平均値

平均値は、データの全体的な傾向を把握しやすいという利点がありますが、外れ値の影響を受けやすく、データのばらつきを反映しにくいという欠点があります。

中央値

中央値は、外れ値の影響を受けにくいという利点があり、データの分布の形を把握しやすいという特徴があります。しかし、データの全体的な傾向を把握しにくいという欠点があります。

最頻値

最頻値は、データのばらつきを反映しやすいという利点がありますが、データの全体的な傾向を把握しにくいという欠点があります。また、外れ値の影響を受けやすいという欠点もあります。

平均値、中央値、最頻値は、それぞれに特徴があるため、データの特徴や目的に合わせて使い分けることが大切です。

Excel関数を利用する

平均値は　AVERAGE 関数を利用します。

中央値は　MEDIAN 関数を利用します。

最頻値は　MODE　関数を利用します。

注意事項

最頻値は重複する値があることが前提になる為、全ての値がユニークな場合、結果が N/A になります。

Microsoft 365 Personal(最新 1年版)|オンラインコード版|Win/Mac/iPad|インストール台数無制限(同時使用可能台数5台)

上手に時短できる　Excel仕事の教科書 (明日に活きるビジネスガイド)

よくわかる エクセル65の使い方改革 知らないと損するExcel仕事術