Excel操作チートシート

はじめに

あれどうやるんだったっけな!?が多くなってきたので、忘れないように( ..)φメモメモ

やりたいこと

複数セルに一括入力

1. データを入力したいセル範囲を選択する
2. データを入力する
   ※最初に選択したセルにデータが表示されます。このデータの入力を確定するときに、［Enter］ではなく［Ctrl］＋［Enter］キーを押します。

　◆【エクセル時短】複数のセルに同じデータを一瞬で入力！ コピペより速いショートカット＆セル選択のワザ | エクセル時短 | できるネット
　　https://dekiru.net/article/16434/

セルの一番下の値を取得

=VLOOKUP(ROWS(B:B),B:B,1)

【参考サイト】
　◆XLOOKUP関数の使い方 | Excel関数 | できるネット
　　https://dekiru.net/article/24265/
　◆XLOOKUP 関数 - Microsoft サポート
　　https://support.microsoft.com/ja-jp/office/xlookup-%E9%96%A2%E6%95%B0-b7fd680e-6d10-43e6-84f9-88eae8bf5929
　◆Excel VLOOKUP関数で最後の値を取得(後ろから検索)する方法 | 誰でもできる業務改善講座
　　https://blog-tips.sekenkodqx.jp/2019/08/01/excel-vlookup-get-last-value00001/

複数の条件に一致するデータの個数を求める

=COUNTIFS(A:A,">=3",B:B,">2023/8/8")

【参考サイト】
　◆COUNTIFS関数 - 複数の条件に一致するデータの個数を求める | Excel関数 | できるネット
　　https://dekiru.net/article/4514/

シート名をセルに表示

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

【参考サイト】
　◆エクセルでシート名をセルに表示させる方法
　　https://h1r0-style.net/excelvba/howto-show-sheetname-in-cell/

指定した条件のデータをカウントしたい

=IF(COUNTIF(A1,"Excel"),"○","")

【参考サイト】
　◆COUNTIFS関数の使い方
　　https://dekiru.net/article/19816/
　◆指定した文字列が含まれるかどうかを判断する
　　https://www.moug.net/tech/exopr/0090052.html

1つの列から重複なくデータを取り出したい

［フィルターオプションの設定］ダイアログボックスで［重複するレコードは無視する］を有効にして抽出を行うと、特定の列に入力されているデータを重複しないように抽出可能

【参考サイト】
　◆Excelで1つの列から重複なくデータを取り出す方法
　　https://dekiru.net/article/12691/

フィルター後の表示されているセルのみを集計したい

※A1～A4の合計表示(フィルター分は集計されない)
=SUBTOTAL(109,A1:A4)

【参考サイト】
　◆SUBTOTAL関数の使い方と集計方法の指定
　　https://pasokon-labo.com/excel-function-subtotal/
　◆【エクセル】見えているセルのみを合計・カウントする方法｜非表示行を除外
　　https://pasokon-labo.com/excel-sum-only-display-number/

文字列を日付フォーマットに変換

セル設定

=TEXT(値,表示形式)

表示形式	項目	表示例
yy	西暦年(下2桁)	22
yyyy	西暦年(4桁)	2022
g	年号(英字の頭文字)	R
gg	年号(漢字の頭文字)	令
ggg	年号(漢字)	令和
e	和暦年(1桁)	4
ee	和暦年(2桁)	04
m	月(1桁)	1
mm	月(2桁)	01
mmm	月(英語の頭3文字)	Jan
mmmm	月(英語)	January
d	日(1桁)	1
dd	日(2桁)	01
aaa	曜日(漢字の頭1文字)	土
aaaa	曜日(漢字)	土曜日
ddd	曜日(英語の頭3文字)	Sat
dddd	曜日(英語)	Saturday

【参考サイト】
　◆エクセルの「日付変換」完全ガイド|文字列やシリアル値、表示形式からの変換方法をすべて解説
　　https://excelcamp.jp/media/function/10664/

正規表現

ソースコード

regularExpression.vb

Option Explicit

Public Function REGEXP(Arg_String As String, _
                       Arg_RegExpPattern As String, _
              Optional Arg_SubMatchIndex As Integer = -1, _
              Optional Arg_ConvertNumric As Boolean = True) As Variant

    'Dim VBS_RE As VBScript_RegExp_55.REGEXP
    'Dim RE_Matches As VBScript_RegExp_55.MatchCollection
    'Dim RE_Match As VBScript_RegExp_55.Match

    Dim VBS_RE As Object     'VBScript_RegExp_55.REGEXP
    Dim RE_Matches As Object 'VBScript_RegExp_55.MatchCollection
    Dim RE_Match As Object   'VBScript_RegExp_55.Match
    Dim Temporary As Variant '型変換前の結果を格納する

    On Error GoTo REGEXP_Err

    '正規表現による検索を実行
    Set VBS_RE = CreateObject("VBScript.RegExp")
    VBS_RE.Pattern = Arg_RegExpPattern
    Set RE_Matches = VBS_RE.Execute(Arg_String)

    If RE_Matches.Count <= 0 Then
        '何もマッチしない場合 #N/Aエラーを返す
        REGEXP = CVErr(xlErrNA)

    Else
        '複数マッチしたかに関わらず常に最初にマッチした結果を使用する。
        'サブマッチが無効の場合はマッチした文字列全体を返す。
        'サブマッチを使用する場合は指定のIndexのサブマッチを返す。
        Set RE_Match = RE_Matches.Item(0)

        If Arg_SubMatchIndex < 0 Then
            Temporary = RE_Match.Value
        Else
            Temporary = RE_Match.SubMatches(Arg_SubMatchIndex)
        End If

        '数値変換が有効の場合は数値変換を試みる。
        If Arg_ConvertNumric Then
            REGEXP = TryConvertToNumric(Temporary)
        Else
            REGEXP = Temporary
        End If

    End If

    Set Temporary = Nothing
    Set RE_Match = Nothing
    Set RE_Matches = Nothing
    Set VBS_RE = Nothing
    Exit Function

REGEXP_Err: 'エラー発生時
    REGEXP = CVErr(xlErrNA)
    Set Temporary = Nothing
    Set RE_Match = Nothing
    Set RE_Matches = Nothing
    Set VBS_RE = Nothing
End Function

Private Function TryConvertToNumric(Arg_Variable As Variant) As Variant
    '数値に変換可能な場合は数値型を返す。
    'そうでない場合はそのままの値を返す。
    If IsNumeric(Arg_Variable) Then
       TryConvertToNumric = Val(Arg_Variable)
    Else
        TryConvertToNumric = Arg_Variable
    End If
End Function

【参考サイト】
　◆Excelのワークシート関数で正規表現を使う
　　https://qiita.com/ktyubeshi/items/74228f18498224c7427d
　◆正規表現検索が出来るユーザー定義関数を作った
　　https://www.excellovers.com/entry/regexpfunction01

名前の管理([Ctrl]＋[F3])

※ハイパーリンク設定しておいて、後で指定すればセルの位置が移動しても大丈夫