メンバー

以下のように、年月を指定することで、その年月の第３水曜日の日付を取得する方法について解説していきます。

こちらでは、第３水曜日の日付を求めていきますが、同じ理屈で、他の週の他の曜日の日付なども求めることができます。

求め方

まず初めに、求め方について考えていきます。
２０２４年の４月の第３水曜日の場合、２０２４年４月１７日になります。
この日付を数式で求めるのは、若干難しそうですが、考え方次第では簡単に求めることができます。

まずは、指定した年月の第３水曜日という見方を変えてみます。
他の見方ですと、「水曜日以外が定休日で、前月末から３日後の営業日」ということもできます。

この理屈の場合は、指定した年月を基準とした前月末の日付をDATE関数で求め、３日後の営業日をWORKDAY.INTL関数で求めることで、第３水曜日の日付を求めることができます。

実践

では実際に、DATE関数とWORKDAY.INTL関数を用いて、第３水曜日の日付を求めていきます。

まずは、指定した年月を基準とした前月末の日付を、DATE関数を活用して求めていきます。
DATE関数の使い方は、以下の通りです。

=DATE(年, 月, 日)
// 指定した[年],[月],[日]の日付を返す

このDATE関数を用いて、指定した年月を基準とした前月末の日付を求める場合は、以下のようになります。

=DATE(C2,C3,0)

なぜ、[日]に「0」と指定することで求めることができるのかというと、DATE関数の月と日に関しては、桁上がり桁下がりに対応しているためになります。
０日ということは、１日の１日前になるため、前月末の日付が求められたということになります。

次に、この前月末から３日後の営業日を、WORKDAY.INTL関数を活用して求めてきます。
WORKDAY.INTL関数の使い方は、以下の通りです。

=WORKDAY.INTL(開始日, 日数, [週末], [祭日])
// 指定した[開始日]から指定した[日数]後の営業日の日付を返す
// [週末]に定休日、[祭日]に不定期の休みを指定する

「[ ]」で囲まれている引数は省略することができます。
今回は、[祭日]を考慮する必要がないため、[祭日]の引数に関しては、省略します。

[週末]にて、水曜日以外を定休日という設定を行います。
ただ、[週末]の項目を選択した時に表示されるリストの中には、「水曜日以外を定休日」とする項目は存在しません。

そのため、リストに表示された番号からではなく、直接指定する必要があります。
直接指定する場合は、月曜日から日曜日に関して、「0：営業日」と「1：定休日」を並べた文字列を入力します。
そのため、水曜日以外を定休日とする場合の設定値は、「”1101111”」になります。

実際に、[開始日]にDATE関数で求めた前月末の日付、[日数]に「3」、[週末]に「”1101111”」を指定して、第３水曜日の日付を求めると、以下のようになります。

=WORKDAY.INTL(DATE(C2,C3,0),3,"1101111")

このようにして、指定した年月の第３水曜日の日付を求めることができました。
年月に関しては、セルの値を参照しているため、セルの値を変更することで、他の年月に関しても求めることができます。

以下の表のようなフォーマットがあり、赤枠の中に値を入力する作業があるとします。

値を入力する際に、１行入力したら、次の行の先頭列を選択する必要があります。
先頭列を選択する際に、毎回、カーソル（矢印）キーやマウスで選択するのは大変です。

今回は、１行入力したら一瞬で次の行を選択することができる方法について３通りで解説していきます。

1. TabキーとEnterキーで移動

１つ目は、TabキーとEnterキーで移動する方法です。
通常、Tabキーを押すと、右隣りにカーソルが移動し、Enterキーを押すと下にカーソルが移動します。

Tabキーで右隣りへ移動した場合は、移動する前の開始の位置が記録されます。
そのため、以下のように、セルB8を基準にTabキーでセルD8まで移動した場合に関しては、開始の位置がセルB8だと記録されています。

Enterキーでの下へ移動は、記録されているセルを基準に下へ移動します。
そのため、上記の状態ですと、セルD8が選択されていますが、Enterキーでの移動先はセルB9になります。

そのため、値を入力する際に、初めに先頭列のセル（セルB8）を選択し、１行（１データ）に関しては、入力してTabキーで次の項目へ移動し、１行（１データ）の入力を終えたら、Enterキーで次の行の開始列のセルを選択するという手順を行うことで、効率的に、表に値を入力することができます。

2. Tabキーのみで移動

２つ目は、Tabキーのみで移動する方法です。
先ほどの方法ですと、１行（１データ）の入力後にEnterキーで確定する必要がありましたが、こちらの方法では、Tabキーのみに統一することができます。
Tabキーのみに統一する場合は、初めに入力対象の範囲全体を選択する必要があります。
そのため、表の場合は以下のように選択します。

このように選択することで、Tabキーのみで移動することができるようになります。

ちなみに、このように選択すると、Enterキーでは開始の列に戻れなくなります。
Enterキーの役割としては、下へ移動、一番下まで移動した場合は、次の列の先頭行のセルへ移動となります。

TabキーとEnterキーに関して、最後のセル（右下）まで移動した場合、次にキーを押した際には、先頭のセル（左上）に移動します。

他にも、以下のコマンドが使用できるので、セットで覚えておくと役立ちます。

・Shift + Tab：左隣へ移動
・Shift + Enter：上へ移動

3. 範囲を選択せずにTabキーのみで移動

先ほどの方法では、範囲を選択する必要がありましたが、最後、３つ目に、範囲を選択せずにTabキーのみで移動する方法について解説していきます。

選択せずにTabキーで移動するには、表をテーブルにする必要があります。
ただ、テーブルにするだけですと、元々のデザインが壊れてしまうため、必要に応じてデザインを修正する必要があります。

テーブルにする場合は、対象の表を、表の見出しを含め全選択し、[挿入]タブの中の[テーブル]を選択します。

次の画面が表示されましたら、選択範囲の先頭行が見出しとなるため、[先頭行をテーブルの見出しとして使用する]の項目にチェックをしてから[OK]で確定します。

以上の作業で、表をテーブルにすることができます。
ただ、表にフィルターが設定されたり、縞模様になったりとデザインが変わってしまいます。
作成されたテーブルを選択すると[テーブル デザイン]タブが表示されるため、そのタブからデザインを修正します。
こちらでは、[フィルター ボタン]と[縞模様（行）]の表示を解除しています。

デザインを修正することで、元の見た目に近づけることができます。

テーブルにすることで、Tabキーのみで移動することができるようになります。

手前のセルに戻る場合は、Shift + Tabで行えます。

また、こちらの方法では、１つ目の方法と同様に、Tabキーで移動して、最後にEnterキーで先頭に移動するということもできます。

それぞれの方法の欠点

１つ目の方法の欠点は、途中の項目から値を入力した時に活用できないという点です。
途中から開始してしまうと、そのセルが開始の位置として記録されてしまうため、先頭の項目にEnterキーで移動することができません。

２つ目の方法の欠点は、表の範囲を選択した後に、入力したいセルまで移動する必要があるという点です。
小さな表であれば、そこまで大変ではありませんが、項目数が多い表の場合などは、入力したい位置まで移動するのが大変になります。

３つ目の方法の欠点は、テーブルにするため、テーブルの終端に括弧のマークが表示されてしまうという点です。
ただ、このマークはテーブルの最後の位置を示しているだけなので、印刷時には影響ありません。

それぞれの欠点を考慮した上で、３つ目の方法が特におすすめになります。

補足

テーブルにした場合、スクロール時に見出しが見えなくなると、列名の部分に見出しが表示されるようになります。
このような機能を含め、基本、表はテーブルにするのが良いかと思います。

以下のように、特定の表から分析に必要な統計情報を瞬時に求める方法について解説していきます。

はじめに

特定の表から分析に必要な統計情報（平均値、中央値など）を取得する場合、Excelのアドインを活用すると良いです。
今回紹介する方法の場合、取得された値は数式ではなく、直接入力になります。
そのため、参照元のデータを編集しても、統計情報に関しては反映されません。
ただ、瞬時に統計情報を求めることができるため、非常に便利な機能になります。

Excelアドインの追加

まずは、[開発]タブの中の[Excelアドイン]を選択します。
以下の画面にて、[分析ツール]にチェックし、[OK]で確定します。

この手順だけで、[データ分析]という機能が使えるようになります。

[データ分析]機能より統計情報を取得

[分析ツール]のアドインを追加すると、[データ]タブ内に、[データ分析]という機能が追加されます。

追加された[データ分析]を選択すると、以下の画面が表示されます。

こちらより、[基本統計量]を選択して、[OK]で確定します。
そのようにすることで、以下のような、統計情報を求めるための設定画面が表示されます。

こちらの画面にて、必要な情報を設定します。
上記では、[入力元]の[入力範囲]に評価の項目の範囲を指定し、[出力オプション]の[統計情報]にチェックしています。

必要な情報を設定し、[OK]で確定することで、以下のような統計情報を瞬時に表示することができます。

補足

今回紹介した方法で取得できた統計情報は、数式ではなく直接入力になります。
元データの編集を自動で反映させたい場合は、出力された統計情報を参考にして、数式で求め直すのが良いかと思います。

ただ、[データ分析]の機能を活用することで瞬時に求めることができるため、統計情報を確認したい時に、再度実行するというのも良いかと思います。

取得された最頻値に関しては、該当する値が複数件見つかった場合、最初に見つかった値のみが表示されます。
複数件見つかる可能性のあるデータの場合で、分析に必要な情報の場合は、MODE.MULT関数を用いて求め直すのが良いかと思います。
MODE.MULT関数を用いて最頻値を全て求める方法は、以下の記事にて解説しています。

＞【Excel】最も頻繁に出現する値を抽出

２つのグループを比較し、片方にしか存在しない値に「×」を表示する数式の作成方法について解説していきます。

前半は、「「×」を表示する可能性がある全てのセルに数式を入力して実現する方法」について解説し、後半は、「スピルを活用して、それぞれのグループに対し、１つずつの数式のみで増減にも対応させる方法」について解説していきます。

後半の方法ですと、誤って数式を削除することが減るため、スピルに対応している場合はおすすめです。

全てのセルに数式を入力する

まずは、「「×」を表示する可能性がある全てのセルに数式を入力して実現する方法」について解説していきます。

初めに、どのような条件の時に、値の隣に「×」を表示するのかを明確にする必要があります。
まずは、以下のAグループの先頭行について考えていきます。

セルC3に「×」を表示するかどうかは、以下の条件から判断できます。

・セルB3の値がE列に１つも存在していない場合に「×」を表示する

指定した値が指定した範囲内にいくつ含まれているのかは、COUNTIF関数を用いて求めることができます。

=COUNTIF(範囲, 検索条件)
// 範囲：確認する範囲
// 検索条件：範囲から数える条件

実際に、COUNTIF関数を用いて数えてみると、以下のような数式になります。

=COUNTIF(E:E,B3)

この数式の結果が０の時に、「×」を表示していきます。
そのため、この結果を元にIF関数で分岐させる必要があります。

=IF(論理式, 値が真の場合, 値が偽の場合)
// 論理式：TRUEもしくはFALSEを表す式
// 値が真の場合：論理式がTRUEの場合に表示する内容
// 値が偽の場合：論理式がFALSEの場合に表示する内容

実際に、先ほどのCOUNTIF関数と組み合わせたものが以下になります。

=IF(COUNTIF(E:E,B3)=0,"×","")

ちなみに、数値の0はFALSE、0以外はTRUEという意味にもなるため、以下のようにも表現することができます。

=IF(COUNTIF(E:E,B3),"","×")
// 「×」の位置が逆になる

この数式を表の最終行までコピーすることで、相対参照で反映することができます。
※E列に関して、E3:E14などと行番号を指定している場合は、「$E$3:$E$14」のように絶対参照にする必要があります。

次は、Bグループの判定になります。
Bグループに関しても先ほどと同様に、以下のような数式で実現することができます。

=IF(COUNTIF(B:B,E3),"","×")

それぞれ１つのセルのみに数式を入力する

次に、「スピルを活用して、それぞれのグループに対し、１つずつの数式のみで増減にも対応させる方法」について解説していきます。

スピルというのは、数式を入力したセルから数式の結果を溢れて表示させる機能のことになります。
Excelのバージョンが2019以前のものに関しては対応していないため、実現することができません。

では、そんなスピルを活用して、以下の黄色のセルのみに数式を入力して実現する方法について解説していきます。

まずは、先ほど作成した数式から実現方法を考えていきます。

一度、先ほどの数式に関して、セルC3のみに残します。

=IF(COUNTIF(E:E,B3),"","×")

こちらの数式に関しては、セルB3のみを確認しています。
スピルを活用する場合、このセルB3を複数範囲にします。

例えば、100行目までデータの増減に対応させる場合、以下のような数式になります。

=IF(COUNTIF(E:E,B3:B100),"","×")

実際に数式を入力すると、以下のように表示され、16行目以降が「×」になってしまいます。

COUNTIF関数の検索条件に関しては、空白のセルを指定した場合、「0」という数値として扱われてしまいます。
そのため、E列には空白のセルを含んでいたとしても、「0」という値が存在しないため、「×」が表示されたということになります。

「×」を表示しない対策として、条件を加えていきます。
その条件は、以下の内容になります。

・セルB3からB100に関して空白の場合は何も表示しない

そのため、更に、外側にIF関数で分岐させます。

=IF(B3:B100="","",IF(COUNTIF(E:E,B3:B100),"","×"))

IF関数を繰り返し使いたくない場合は、IFS関数がおすすめです。

=IFS(論理式1, 値が真の場合1, [論理式2, 値が真の場合2], [論理式3, 値が真の場合3], …)
// 論理式：TRUEもしくはFALSEを表す式
// 値が真の場合：論理式がTRUEの場合に表示する内容
// → FALSEの場合は次の条件が確認される（論理式1 → 論理式2 → 論理式3 → …）
// 全ての条件を満たさない場合は、「#N/A」というエラーになる

実際に活用すると、以下のような数式になります。

=IFS(B3:B100="","",COUNTIF(E:E,B3:B100),"",TRUE,"×")
// 最後の論理式をTRUEにすることで、「その他」という条件を指定できる

この数式を入力すると、以下のように表示されます。

ただ、この状態ですと、３行目を削除した場合に数式も消えてしまいます。
そのため、より数式が消されてしまう可能性を減らすため、数式をセルC2に移動します。

移動する際に、条件の範囲に関しても、以下のように変更する必要があります。

=IFS(B2:B100="","",COUNTIF(E:E,B2:B100),"",TRUE,"×")

ただ、このままですと、先頭行に関してもBグループ内容と比較され、「×」が表示されてしまいます。

そのため、以下のいずれかの条件を加える必要があります。

①B列の値が「A」の場合は何も表示しない
②２行目の場合は何も表示しない

①の場合は、項目名を変更する度、数式を修正する必要が出てくるため、こちらでは、②の方法について解説していきます。

行番号を確認するには、ROW関数を活用します。

=ROW(参照)
// 参照：行番号を取得したいセルを指定

実際にROW関数を組み合わせて表現した数式は、以下になります。

=IFS(ROW(B2:B100)=2,"",B2:B100="","",COUNTIF(E:E,B2:B100),"",TRUE,"×")

このようにして、Aグループに関しては１つの数式のみで実現することができました。

次は、Bグループの判定になります。
Bグループに関しても先ほどと同様に、以下のような数式で実現することができます。

=IFS(ROW(E2:E100)=2,"",E2:E100="","",COUNTIF(B:B,E2:E100),"",TRUE,"×")

まとめ

こちらのシートを利用する人が全員スピルに対応している環境の場合は、後者のスピルを活用した方法の方が、数式が少なく、より保守的で良いかと思います。
スピルを使用する際、検索が必要な数式に関して、必要以上に範囲を広くしてしまうと、処理が重たくなる原因に繋がります。
そのため、指定する範囲に関しては、必要に応じて、今回のように「B2:B100」などと限られた範囲のみを指定した方が良いかと思います。

Excel関連の何かしらの作業を自動化する際に、便利な機能としてマクロというものがあります。
このマクロを開発することができるプログラミング言語がVBAになります。
こちらでは、次の作業を自動化するマクロを、VBAを用いて開発する方法について解説していきます。
※開発したファイルは記事に最後にて配布しています。

作業内容

１．以下のテンプレートシート「YYYYMMDD」のコピーを作成し、シート名を作業日の日付（YYYYMMDD形式）にする

２．コピーしたシートのセルB2に作業日の日付を入力する

1. 開発の準備

では早速、自動化するマクロを開発していきます。
※開発したマクロに関しては、「Ctrl＋Shift+A」というショートカットで実行できるようにも設定していきます。

まずは、[開発]タブを選択し、その中の[マクロ]を選択します。

次に、以下の画面にて、好みの名前を入力し、[作成]を選択します。
こちらでは「シート作成」と入力しています。

表示されました以下の画面にて、プログラムを記述していきます。
※「Option Explicit」は設定内容によっては表示されません。

2. コードの記述

記述する内容は、以下になります。

こちらのプログラムに関して、１行ずつ解説していきます。

まずは、以下の内容について解説します。

Worksheets("YYYYMMDD").Copy After:=Worksheets(1)

こちらでは、「YYYYMMDD」のシートをコピーし、２枚目の位置に貼り付けています。
「Worksheets(1)」が先頭から１番目のシートを指しているため、「Copy」というコピーする機能の設定（引数）の「After」に「Worksheets(1)」を指定することで１番目のシートの後ろと指定しています。

次は、以下の内容について解説します。

With ActiveSheet
    .Name = Format(Date, "yyyymmdd")
    .Range("B2").Value = Date
End With

まずは、「With」について解説します。
「With」を用いることにより、「With」から「End With」の間に関して、「With」の後ろに指定した要素「ActiveSheet」を省略して記述することができるようになります。
省略して記述する場合は、「.」から記述します。

つまり、以下のプログラムと同じ意味になります。

ActiveSheet.Name = Format(Date, "yyyymmdd")
ActiveSheet.Range("B2").Value = Date

同じ要素（こちらでは「ActiveSheet」）に対して操作する場合は、「With」で囲むことによって、処理の塊が分かりやすくなります。

では次に、以下のプログラムについて解説します。

ActiveSheet.Name = Format(Date, "yyyymmdd")

「ActiveSheet」とは現在選択されているシートを指します。
シートをコピーして複製すると、その複製されたシートが自動で選択されるため、「ActiveSheet」は新たに追加したシートのことを指しています。
そのシートの名前「.Name」を「Format(Date, “yyyymmdd”)」で、マクロを実行した日付にしています。
「Date」で、マクロを実行した日付の情報を取得することができるのですが、シート名にはYYYYMMDD形式で設定したいため、Format関数を活用して、形式を変換しています。

では最後に、以下のプログラムについて解説します。

ActiveSheet.Range("B2").Value = Date

こちらでも先ほど同様に「ActiveSheet」を指定しています。
そのシートのセルB2「Range(“B2”)」の値「.Value」に、マクロを実行した日付「Date」を入力しています。

3. 完成

このようにして開発することができました。
開発したプログラムは、対象のプログラム名を指定し、実行ボタンを押すことで実行することができます。

実行することにより、以下のようにシートが作成され、セルB2に日付が入力されます。

注意点として、Excel上には同じシート名のシートを作成することができないため、同日に複数回実行すると、以下のようなエラーが表示され、処理が中断されます。

再度実行する際は、作成したシートを削除してから行う必要があります。 必要に応じて、シート名が重複した場合には「YYYYMMDD_2」のような番号を加えるようにプログラミングするのも良いかと思います。
その方法については、こちらでは省略します。

4. ショートカットの設定

最後に、ショートカットの設定方法です。
毎回、VBAの編集画面（VBE）を開いて実行するのは手間になるため、ショートカット活用するのがオススメになります。
まずは、[開発]タブの中の[マクロ]を選択し、以下の画面を表示します。

こちらの画面より、開発したプログラム名を選択し、[オプション]を選択します。

以下の画面の[ショートカットキー]のテキストボックスを選択し、Shiftキーを押しながらAキーを押すことで、「Ctrl+Shift+A」というショートカットを設定することができます。

設定後は、[OK]を選択して確定し、設定画面を全て閉じることで、CtrlキーとShiftキーを押しながらAキーを押すして、開発したマクロを実行することができるようになります。

こちらで開発したファイルは、以下から取得できます。

まとめ

今回の内容は、VBAを用いて繰り返し作業を自動化する一例になります。
ぜひ、日々の繰り返し作業の自動化に、VBAを活用してみてください。
※理解していないプログラムの実行は、情報漏洩やデータの紛失などの大きな問題につながる可能性があるため、理解した上で実行してください。

セルに入力されている文字から特定の文字の数を数式で求める方法について解説していきます。
こちらでは、以下の黄色のセルから「A」と「B」のそれぞれの数を数式で求めていきます。

求め方

まずは、求める方法について考えていきます。
Excelには、セル内の特定の文字を数えるという機能は備わっていません。
そのため、数える仕組みを自身で作成する必要があります。
こちらでは、１つの方法について解説していきます。

こちらで紹介する方法は、「セル内の数えたい対象の文字を削除した文字列の文字数」と「元々の文字列の文字数」の差分を計算することで、対象の文字の数を求めます。

数式の作成

では実際に、先ほどの仕組みを数式で求めていきます。
数式で求めるには、「特定の文字を消すための関数」と「文字の数を求める関数」を活用する必要があります。

「特定の文字を消すための関数」はExcelには用意されていないため、文字を置換するSUBSTITUTE関数を活用して求めていきます。

SUBSTITUTE関数の使い方は、以下の通りです

=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
// [文字列]の中の[検索文字列]を[置換文字列]に置換する
// 文字列：対象の文字列
// 検索文字列：置換したい文字列
// 置換文字列：置換する文字列
// [置換対象]：検索文字列が複数存在する場合に、先頭から何番目の対象の文字を置換するのかを数値で指定
// ※省略時は全ての検索文字列を置換

SUBSTITUTE関数を用いて特定の文字を消すためには、以下のように活用します。

=SUBSTITUTE(B2,"A","")

「文字の数を求める関数」は、LEN関数になります。

LEN関数の使い方は、以下の通りです。

=LEN(文字列)
// 指定した[文字列]の文字の数を返す
// 文字列：対象の文字列

以下のように活用します。

=LEN(B2)

SUBSTITUTE関数とLEN関数を用いて、特定の文字の数を求めていきます。

「セル内の数えたい対象の文字を削除した文字列の文字数」は、以下のように求めることができます。

=LEN(SUBSTITUTE(B2,"A",""))
// 「A」の文字を削除した場合の文字数

そのため、以下のように、元々の文字数から上記の文字数を引くことで、特定の文字の数を求めることができます。

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))

上記の内容は「A」の文字数になります。
「B」の文字数に関しても、以下のように求めることができます。

=LEN(B2)-LEN(SUBSTITUTE(B2,"B",""))

まとめ

今回のように、関数を組み合わせることによって、好みの値を求めることができます。
特定の文字の数ではなく、他の値を求める場合などで、関数を組み合わせても求めることができない場合、もしくは、かなり複雑な数式になってしまう場合は、VBAを活用するのが良いかと思います。
（VBAとは、「Visual Basic for Applications」の略称で、ExcelやWordなどといったMicrosoft Officeのアプリケーションの機能を拡張することができるプログラミング言語になります。）

「名前を入力するだけで、その名前のデータのみを瞬時に抽出することができる検索機能」を数式のみで実現する方法について解説していきます。
以下の画像では、セルH3に入力した文字を含む名前のデータを左の表から右の表に抽出しています。

こちらでは、完全一致検索と部分一致検索の２通りについて順番に解説していきます。
※使用したファイルは、記事の最後にて配布しています。

完全一致検索の実現

特定の条件を満たすデータのみを抽出するには、FILTER関数が便利です。
FILTER関数は、365もしくはExcel2021以降で対応している関数になります。
FILTER関数の使い方は以下の通りです。

=FILTER(配列, 含む, [空の場合])
// 条件を満たすデータのみを抽出する
// 配列：対象の表
// 含む：抽出条件
// [空の場合]：対象が1件もない時に表示する値（省略時はエラーを表示）

この関数を使用して、以下のように記述することで実現することができます。

=FILTER(B3:E1000,C3:C1000=H3,"")
// B3:E1000：表の範囲はデータの増減に対応するため大きめに指定する
// C3:C1000=H3：検索対象の項目の行数は表の範囲と同じにする必要がある

この１つの数式だけで、以下のように対象のデータを抽出することができます。

こちらの場合は、対象が１件のみのため、１件のみが抽出されました。

部分一致検索の実現

部分一致検索を実現するには、先ほどの以下の数式の「C3:C1000=H3」という条件式について理解する必要があります。

=FILTER(B3:E1000,C3:C1000=H3,"")

条件式について理解するために、まずは「C3:C1000=H3」という数式のみを入力してみます。

= C3:C1000=H3

入力して確定すると、以下のようにTRUEもしくはFALSEという値が表示されます。

TRUEもしくはFALSEという値は、指定した表のデータの数分表示されます。
このTRUEにあたるデータが条件を満たしていて抽出する対象のデータになります。

そのため、部分一致の検索を実現するには、検索欄「セルH3」の値が含むデータのみにTRUEと表示するような条件式を考える必要があります。

そのような条件式を実現するには、FIND関数とIFERROR関数を活用します。
FIND関数の使い方は、以下の通りです。

=FIND(検索文字列, 対象, [開始位置])
// [対象]に[検索文字列]が含まれる場合に、その文字列の先頭からの位置情報を返す
// 検索文字列：検索する文字列
// 対象：検索する対象の文字列
// ※[開始位置]に関しては、今回は使用しないため省略する

実際に活用した例は、以下になります。

=FIND(H3,C4)

入力して確定すると、以下のように見つかった位置の情報を返します。

ちなみに、検索文字列が見つからない場合は、エラーになります。

=FIND(H3,C3)

条件式では、0以外の数値がTRUE、0がFALSEとして扱われるため、エラーの値に関してIFERROR関数を用いて、0と表示していきます。
IFERROR関数の使い方は、以下の通りです。

=IFERROR(値, エラーの場合の値)
// 指定した[値]がエラーの場合に、[エラーの場合の値]を返す
// エラーでない場合は、指定した[値]をそのまま返す
// 値：エラーを検証する対象の値
// エラーの場合の値：エラーの場合に返す値

先ほどの数式にIFERROR関数を組み合わせると、以下のようになります。

=IFERROR(FIND(H3,C3),0)

入力して確定することで、以下のようにエラーの場合に0とすることができます。

後は、必要な行数分表示させる必要があるため、FIND関数の引数[対象]に複数の範囲を指定します。

=IFERROR(FIND(H3,C3:C1000),0) 

これだけで、必要なデータの数分のTRUEもしくはFALSEを意味する値を表示することができるため、こちらの条件式をFILTER関数に設定します。

=FILTER(B3:E1000,IFERROR(FIND(H3,C3:C1000),0),"")

入力して確定することで、部分一致の検索機能を実現することができます。

まとめ

FILTER関数を用いて検索機能を実現することによって、元の表を触らずに対象のデータの抽出ができます。
検索した内容を残したい場合は、同じような検索機能をコピーして作成すると良いです。
コピーして作成した場合に関しても、元のマスタとなる表は１つのため、元の表が分からなくなるというような事態を防ぐことができます。

こちらで作成した検索機能のExcelファイルは、以下になります。

以下の表の黄色のセルには数式が入力されています。
数式の内容は、「60点以上なら合格、未満なら不合格、空白なら空白」というものです。

この表に関して、不要になった行の値を削除する際に、数式のセルを含め行全体を選択して削除しても、数式だけは消えずに残ります。
そのため、何度でも再利用することができます。

このような行ごと削除できる数式の作成方法について解説していきます。

スピルの活用

この数式を実現するには、「スピル」という機能が必要になります。
この機能はExcel2021以降かMicrosoft365で対応しております。

スピルという機能は、数式を入力したセルから溢れ出して数式の結果を表示させるというものになります。

まずは、下記の表のセルD3に「60点以上なら合格、未満なら不合格、空白なら空白」となるような数式をIFS関数で入力していきます。

IFS関数の使い方は以下の通りです。

=IFS(論理式1, 値が真の場合1, [論理式2, 値が真の場合2, …])
// 論理式1：結果がTRUEもしくはFALSEになる式
// 値が真の場合1：論理式1がTRUEの場合に表示する値
// 論理式2以上は必要に応じて指定することができます。
// 複数の論理式がTRUEになる場合は、一番先頭の「値が真の場合」に指定した値が表示されます。

IFS関数を使用して表現したものが以下になります。
※IFS関数で「その他」を指定するには、最後の論理式にTRUEと指定します。

=IFS(C3="","",C3>=60,"合格",TRUE,"不合格")

このように表現することができましたら、数式の参照する範囲を複数行にします。
今回の場合は、セルC3の点数の参照のみならず、セルC3からC7までを指定します。

そのように指定するだけで、以下のようにセルから溢れ出て結果が表示されます。
※スピルが対応していない場合は、以下のように表示されません。

=IFS(C3:C7="","",C3:C7>=60,"合格",TRUE,"不合格")

この数式は、セルD3のみに入力がされていて、他のセルD4からD7には入力されていません。
他のセルD4からD7に関しては、値を削除しようとしても削除することができません。
そのため、４行目以降に関しては、行ごと削除しても、数式のみが残るようになります。

ただ、このままですと、３行目を削除してしまった際に、数式がすべて消えてしまいます。
そのため、次に、３行目に関しても行ごと削除できるようにしていきます。

セルD3に数式を入力している場合は、このようなことが実現できないため、まずは数式を入力する位置から変更する必要があります。

そのため、セルD3の数式を削除し、セルD2の項目名の位置に数式を入力していきます。
項目名の位置に先ほどと同じ数式を入力し、１行上になるため、行番号を変更します。

=IFS(C2:C7="","",C2:C7>=60,"合格",TRUE,"不合格")

このようにすると、項目名の位置に「合格」と表示されてしまいますが、３行目以降に関しては、行ごと削除することができるようになります。

次に、項目名が「合格」ではなく、「評価」となるように数式を修正していきます。
そのような数式を実現するには、ROW関数が活用できます。
ROW関数の使い方は以下になります。

=ROW([参照])
// 参照：指定したセルの行番号を返す（省略時は数式を入力したセルが指定される）

まずは、ROW関数を用いて、項目名に「評価」と表示し、それ以外のセルには「-」と表示する数式を作成していきます。

=IF(ROW(C2:C7)=2,"評価","-")

こちらでは、ROW関数での取得結果が２（２行目）の場合に「評価」、それ以外の場合に「-」を表示させています。

次に、先ほどの数式の「-」という表示を、IFS関数の内容に置き換えます。

=IF(ROW(C2:C7)=2,"評価",IFS(C2:C7="","",C2:C7>=60,"合格",TRUE,"不合格"))

このようにすることで、項目の位置に項目名を表示し、各行に関しては、数式の判定結果を表示することができました。
この場合、数式はセルD2にしか入力されていないため、３行目以降に関しては、行ごと削除しても数式が消えることはありません。

数式が消されないように「シートの保護」を活用することがありますが、部分的に保護すると、行ごとの削除ができなかったりと不便もあります。
そのため、スピルが使える環境の場合は、こちらのような数式で表現してみるのも良いかと思います。

以下のような更新履歴表に関して、「更新」ボタンを押して更新内容を入力するだけで、表の最終行に「更新日、更新者（パソコンにログオン（ログイン）したユーザー名）、更新内容（インプットボックスに入力した値）」が自動で入力される仕組みを開発していきます。

VBAを活用することで、簡単にパソコンに記録されている情報（パソコンにログオンしたユーザー名など）を取得することができます。
今回は、更新者としてパソコンにログオンしたユーザー名を取得していますが、他の値を取得する方法についても、確認用のマクロを配布して解説しています。

準備

機能を開発するために、まずは更新履歴表を用意します。
今回こちらでは、以下のような表を用意しています。

５行目以降に関しては、罫線のみで値を入力していない状態にしておきます。
※用意した表のレイアウト（項目の位置など）によっては、開発するプログラムの内容が異なります。

開発

次に、[開発]タブの中の[マクロ]を選択します。

※[開発]タブが表示されていない場合は、以下の手順をご確認ください。

＞マクロを開発する準備

以下の画面が表示されましたら、開発する機能の名前（こちらでは「更新」）を入力し、[作成]を選択します。

以下の画面が表示されましたら、「Sub 更新()～End Sub」の間にプログラムを入力していきます。
※環境によっては、「Option Explicit」が表示されていないことがありますが、今回はなくても問題ございません。

では次に、以下のようにプログラムを入力します。
※こちらを実装したExcelファイルは、記事の最後にて配布しています。

Sub内はTabキーにてインデント（字下げ）しています。
インデントの有無では処理に影響はないのですが、処理の見やすさの観点からインデントする癖をつけておいた方が良いです。

プログラムの解説

プログラムの内容について簡単に確認していきます。

まずは、以下の内容です。

「Dim」と書かれているのは、一時的に値を格納する部屋（変数）を用意する宣言（変数宣言）になります。
「As」の後に部屋の種類を記述しています。（「String」は文字専用の型）

次に、用意した「detail」という変数にインプットボックス「InputBox」に入力した値を格納します。

「InputBox」の括弧の中の文字は、インプットボックスに表示される文字になります。
イメージは以下になります。

次に以下の内容です。

「Dim」は先ほど同様に変数宣言になります。（「Long」は数値専用の型）

「Rows.Count」はシートに存在している最大の行番号になるため、「Cells(Rows.Count,”B”)」でシート上のB列の一番下のセルを指定しています。

このセルから、Ctrlキーを押しながら上に移動「.End(xlUp)」し、止まった位置の行番号「.Row」に１を加えた数を取得しています。
→ B列の下から上に向かっていく中で、次に文字が入力されているセルの一つ下の行番号（5）を取得

次に以下の内容です。

先ほど取得した行番号のB列のセルに実行した時の日付「Date」を入力し、C列のセルにパソコンにログオンしたユーザー名を入力、D列のセルにインプットボックスに入力した値を入力しています。 ユーザー名はEnviron関数を用いて取得しています。
※Environ関数で取得できる値については、最後に確認用マクロを配布して解説しています。

最後に以下の内容にて、Excelファイルを保存しています。

ボタンの配置

これで機能としては完成しているため、後はボタンを作成し、そのボタンに開発した機能を割り当てれば完成です。

ボタンは、[開発]タブの中の[挿入]から作成すると良いです。

上記のボタンを選択後、図形の作成時と同様にボタンを作成すると、[マクロの登録]画面が表示されます。
そこでは、開発したマクロの名前を選択することで、マクロを割り当てたボタンを用意することができます。
ボタンの表示名などは必要に応じて変更してください。
※作成したボタンを編集する場合は、Ctrlキーを押しながら選択します。

このようにして、ボタンを押してインプットボックスに更新内容を入力するだけで、表の最終行に値が入力されます。

Environ関数について

最後に、Environ関数で取得できる値について、確認用のファイルを配布して解説していきます。

Environ関数で取得できる値は以下になります。

Environ関数の引数に上記の左側にある環境変数名を設定することで、その値を取得することができます。
先ほどのプログラムで使用した値は「USERNAME」になります。

どのような値が取得できるのかは、実際に試された方がイメージしやすいかと思いますので、各々で確認できるファイルを配布します。
配布ファイルのマクロの実行を許可し、「出力」ボタンを押すだけで、値を確認することができます。

配布

今回開発した「更新履歴を自動で入力する機能のExcelファイル」と「Environ関数の出力確認用のExcelファイル」は以下になります。

ぜひ試してみてください。

今回は、データの増減に対応させた罫線を設定する方法について解説していきます。
また、以下のようにカテゴリーなどの特定の項目の値を元に、罫線の種類を変更する方法についても解説していきます。

1. 表の増減に対応させた罫線を設定

まずは、以下の表に対し、データの増減に対応させた罫線を設定します。
自動で表を拡張する機能として「テーブル」がありますが、こちらではテーブルを使わずに設定していきます。

特定の条件を満たしている時に、罫線などの書式を変更するには、「条件付き書式」を活用します。
今回の場合、以下の条件を元に罫線を設定していきたいと思います。

「Noの項目に値が入力されている場合」

まずは、設定する対象のセルを選択する必要があります。
今回の場合は、セルA3からC列の一番下までのセルに設定していきます。

その場合、AからC列を全選択し、Ctrlキーを押しながら不要なセルを選択して、選択を解除することで、以下のように対象のセルのみを選択します。

選択できましたら、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択します。

以下の画面にて、[ルールの種類]を[数式を使用して、…]にし、テキストボックス内に以下の数式を入力します。

=$A3<>""

テキストボックス内に設定する数式は、現在の選択基準のセル（A3）に対しての数式である必要があります。
選択基準のセルに罫線を設定する時は、選択基準のセルに値が何かしら入力されている場合になるため、こちらでは、「選択基準のセルが空白でない場合（=$A3<>””）」と入力しています。

他の選択範囲に関しては、入力した数式がコピーされて設定されます。
そのため、相対参照や絶対参照を意識した数式を入力する必要があります。
こちらの場合は、値の有無を確認するのは必ずA列になるため、「$A3」とA列のみに「$」を加え固定しています。

数式の入力ができましたら、[書式]より罫線の設定を行います。

こちらで設定した内容は、「点線の下の罫線」になります。
※罫線の設定画面の予め表示されている太い薄い線は、未設定という意味になります。

この設定で確定することによって、以下のようにデータの増減に対応させて罫線を設定することができました。

2. 特定の項目の値を元に罫線を設定

次は、特定の項目の値を元に罫線を設定する方法について解説していきます。
こちらでは、カテゴリーの項目の値を元に、以下のように罫線を設定し、同じカテゴリーのデータを目立たせていきます。

このような設定を行う場合に関しても、「条件付き書式」を活用することで実現できます。

まずは、条件を考える必要があります。
今回の場合は、以下のような条件になります。

「Noの項目に値が入力されている場合、尚且つ、カテゴリーの項目の値が１つ下の値と異なる場合」

まずは、設定する対象のセルを選択する必要があります。
先ほどの同じ範囲を選択し、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択します。

現在の選択基準のセルは、先ほどと同様にセルA3になるため、セルA3に対する条件を数式で表現していきます。

「Noの項目に値が入力されている場合、尚且つ、カテゴリーの項目の値が１つ下の値と異なる場合」という条件を数式で表現すると、以下のようになります。

=AND($A3<>"",$B3<>$B4)

こちらでは、AND関数で「Noの項目に値が入力されている場合（$A3<>””）」と「カテゴリーの項目の値が１つ下の値と異なる場合（$B3<>$B4）」を設定しています。

この条件式を、[数式を使用して、…]のテキストボックス内に設定し、[書式]より罫線を設定します。

これで確定することにより、以下のようにカテゴリー単位でも罫線を設定することができました。

補足

複数の条件付き書式を設定しているセルで、複数の条件を満たしている場合、基本は最後に設定した条件付き書式が優先されて反映されます。
優先順位を変更したい場合は、[ホーム]タブの中の[条件付き書式]の[ルールの管理]より行えます。

FILTER関数は、以下のように指定した表のデータから指定した条件に合ったデータのみを抽出する時に活用する関数になります。

実は、必要な項目のみを抽出する際にも、FILTER関数を活用することができます。
今回は、指定した項目をFILTER関数で抽出する方法について解説していきます。

FILTER関数の使い方

抽出する方法はいくつかあるため、順番に解説していきます。

FILTER関数の使い方は次の通りです。

=FILTER(配列, 含む, [空の場合])
// 配列：絞り込み対象の表の範囲など
// 含む：絞り込み条件
// [空の場合]：条件を満たす対象のデータが見つからない時に表示する内容（省略時はエラーが表示される）

この含むという条件ですが、通常は以下のように指定します。

=FILTER(B3:F12,E3:E12="男")
// 性別の項目が「男」の場合

ここで設定されている条件式は「E3:E12=”男”」になります。
この条件式は、「E3:E12」の１列に対しての式になります。

実は、ここで指定する式を行方向にすることで、行に対しての絞り込みができます。
行に対しての絞り込みを行うことによって、必要な項目のみを抽出することができます。

指定した項目をFILTER関数で抽出する方法①

まずは簡単な方法から確認していきます。
必要な項目のみを抽出する場合、その対象の項目名の上に「・」などの任意の文字を入力します。

次に、その項目名の上の文字を条件式に加えます。
例えば、上記の画像のように「・」という文字を入力している場合は、抽出する条件式は「B1:F1=”・”」になります。

実際に、その条件式を用いて数式を作成していきます。

=FILTER(B3:F12,B1:F1="・")

このように必要な項目のみを抽出することができました。

抽出した項目に対して、更にデータの絞り込みを行いたい場合は、このFILTER関数をFILTER関数の配列の引数に設定するという方法で実現します。

=FILTER(FILTER(B3:F12,B1:F1="・"),E3:E12="男")

指定した項目をFILTER関数で抽出する方法②

ただ、上記の方法ですと、項目名の上に「・」などといった文字を入力する必要があります。
そのため、次に「・」などといった文字を入力せずに項目を抽出する方法について解説していきます。

そのためには、「B1:F1=”・”」というような条件式が何を表しているのかを理解する必要があります。

試しに、「B1:F1=”・”」という条件式を単体で入力してみます。

= B1:F1="・"

入力すると、一行のTRUEとFALSEで構成された値が表示されるかと思います。

つまり、FILTER関数の条件式に設定する値とは、「抽出したい位置がTRUEになり、それ以外がFALSEとなるような式」であれば良いということが分かります。

直接、TRUEとFALSEで構成された数式を入力するには、カンマ区切りの値を「{」と「}」で囲みます。

={TRUE,FALSE,FALSE,TRUE,TRUE}

このように直接入力することができます。

また、TRUEは０以外の数値、FALSEは０としても表現することができます。
実際に、１と０に置き換えてみると以下のようになります。

={1,0,0,1,1}

この式をFILTER関数に設定することで、項目名の上に「・」などといった文字を入力しなくても、好みの項目のみを抽出することができます。

=FILTER(B3:F12,{1,0,0,1,1})

このように好みの項目のみを抽出することができました。

指定した項目をFILTER関数で抽出する方法③

ただ、「{1,0,0,1,1}」などといった値を入力するのは、若干の手間になります。
１と０を入力している時に、誤って１つずつずれてしまう可能性もあります。

そのため、以下のようにIF関数やSWITCH関数などを用いて表現するのも良いかと思います。

IF関数の場合は、以下のようになります。

=IF((B2:F2=B2)+(B2:F2=E2)+(B2:F2=F2),1,0)
// 抽出したい項目を「(B2:F2=B2)」のように「+」で並べる
// 「+」はORという意味なる

=FILTER(B3:F12,IF((B2:F2=B2)+(B2:F2=E2)+(B2:F2=F2),1,0))

ちなみに、以下のようにIF関数を使わなくても表現することができます。

=FILTER(B3:F12,(B2:F2=B2)+(B2:F2=E2)+(B2:F2=F2))

SWITCH関数の場合は、以下のようになります。

=SWITCH(B2:F2,B2,1,E2,1,F2,1,0)
// 抽出したい項目を「B2,1」のように必要な分を並べる
// 「B2」などと指定された項目名の場合は1、それ以外は0が返される

=FILTER(B3:F12,SWITCH(B2:F2,B2,1,E2,1,F2,1,0))

これらは例になるため、他の方法を考えてみるのもよいかと思います。