小技集

以下の表は、氏名ごとに講座名を割り当てた表になります。

こちらの表を例に、以下のように講座名ごとで氏名をカンマ区切りで簡単に抽出する方法について解説していきます。

こちらの方法は、数式のみで実現できます。

手順1: IF関数で条件を設定する

まず、特定の講座名に対し、該当する氏名を抽出する数式を、IF関数を用いて作成します。
IF関数の使い方は、以下になります。

=IF(論理式, 値が真の場合, 値が偽の場合)
// 指定した条件（[論理式]）を満たしている場合は[値が真の場合]を返す
// 指定した条件（[論理式]）を満たしていない場合は[値が偽の場合]を返す

IF関数を用いて、まずは講座名「A」と一致する氏名のみを抽出します。
こちらでは、以下の表のセルC2に数式を入力します。

入力する数式は、以下になります。

=IF($C$7:$C$15=B2,$B$7:$B$15,"")
// 表の講座名と氏名の範囲は「$」で固定する
// $C$7:$C$15: 講座名が入力されている範囲（絶対参照）
// B2: 比較対象となる講座名
// $B$7:$B$15: 氏名が入力されている範囲（絶対参照）

実際に数式を入力すると、以下のように氏名が抽出されます。

※こちらではスピルの機能を活用して抽出内容を確認するため、他の列に数式を入力しています。スピルに対応していないバージョンの場合は、上記のように確認することはできません。

上記のように条件が一致する場合に氏名を返し、一致しない場合に空白を返します。

手順2: TEXTJOIN関数でカンマ区切りにする

次に、IF関数で抽出された氏名をカンマ区切りでまとめます。

その際に使用する関数は、TEXTJOIN関数になります。
TEXTJOIN関数の使い方は、以下になります。

=TEXTJOIN(区切り文字, 空のセルは無視, テキスト1, [テキスト2], …)
// 指定した[テキスト]を指定した[区切り文字]で結合して返す
// 指定した[テキスト]に空白が含まれる場合に、そのセルを無視して結合するかどうかを[空のセルは無視]で指定する（TRUE：無視する、FALSE：無視しない）

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

=TEXTJOIN(",",TRUE,IF($C$7:$C$15=B2,$B$7:$B$15,""))
// ",": カンマで区切る設定
// TRUE: 空白セルを無視
// IF関数: 条件に一致する氏名を抽出

この数式をセルC2に入力することで、講座名「A」に関しては、以下のように該当する氏名をカンマ区切りで抽出することができます。

スピルに対応していない環境の場合は、数式を入力後に「Ctrl + Shift + Enter」で確定し、以下のような配列数式として入力する必要があります。

{=TEXTJOIN(",",TRUE,IF($C$7:$C$15=B2,$B$7:$B$15,""))}

※結合されたセルでは配列数式が使えないため注意してください。

手順3: 数式のコピー

最後に、先頭に入力した数式を下の行までコピーすることで、以下のように、講座名ごとで氏名をカンマ区切りで抽出することができます。

シンプルな関数のみで実現できるので、ぜひ試してみてください。

Excelでタスクの期限に応じた優先度を自動的に表示させる方法を紹介します。
期限までの残り日数に基づいて、タスクの優先度を「高」「中」「低」で表示する簡単な数式を作成する方法です。これにより、タスクを効率よく管理できるようになります。

こちらでは、以下の表を用いて優先度を求めていきます。

1. 基準日を設定

まず、基準日となるセルを設定します。
基準日とは、今日の日付のことです。これを表示させるには、TODAY関数を活用します。例えば、基準日をセルC2に表示する場合、セルC2に以下の数式を入力します。

=TODAY()

優先度を求める数式の中で、直接指定することもできますが、基準日の確認がしやすいように、こちらでは別のセルに表示させています。

2. 優先度を表示する数式を作成

次に、期限が設定されたタスクの優先度を自動で表示するための数式を作成します。タスクの期限は別のセルに入力されていると仮定します（例えば、期限がセルC5に入力されている場合）。
タスクの優先度は、基準日から残りの日数に応じて、以下のように分類します。

・残り日数が3日以内 → 高
・残り日数が7日以内 → 中
・それ以外 → 低

この条件に基づいて優先度を自動で表示するために、IFS関数を活用します。
IFS関数は複数の条件を順に評価し、それに応じた結果を返します。
IFS関数の使い方は、以下になります。

=IFS(論理式1, 値が真の場合1, [論理式2, 値が真の場合2], [論理式3, 値が真の場合3], … )
// 論理式n：条件
// 値が真の場合n：論理式nで指定した条件を満たした場合に返す値

以下の数式を活用して、優先度を表示します。

=IFS(C5-$C$2<=3,"高",C5-$C$2<=7,"中",TRUE,"低")
// C5-$C$2<=3：期限（C5）から基準日（C2）を引いた結果が3日以内であれば「高」を表示する
// C5-$C$2<=7：期限（C5）から基準日（C2）を引いた結果が7日以内であれば「中」を表示する
// TRUE：上記の条件を満たさない場合、最終的に「低」を表示する
// 基準日のセル（C2）に関しては「$」を加え絶対参照にする

3. 数式をコピー

数式を入力したセル（D5）を必要な範囲にコピーすることで、全てのタスクに対しての優先度を自動で表示できます。

この方法を使えば、Excelでタスクの期限に応じて優先度を簡単に表示できるようになります。
特にタスク管理を効率化したい方には非常に便利なテクニックです。是非、試してみてください。

COUNTIF関数やCOUNTIFS関数、SUMIF関数やSUMIFS関数などの条件指定において非常に有用なワイルドカードと呼ばれる記号であるアスタリスク（*）とクエスチョンマーク（?）を活用したことはありますでしょうか。
実は、これらの記号を活用することで、より幅広い集計が可能になります。

この記事では、アスタリスク（*）とクエスチョンマーク（?）を活用して、Excelで効率的にデータを集計する方法を具体例とともにご紹介します。

1. アスタリスク（*）を使ったデータ集計

アスタリスク（*）は、文字列の中で任意の文字が０文字以上続く場合に活用する記号です。

例えば、以下の表の住所から「東京都」の住所の件数を取得する場合、「東京都○」の「○」が任意の０文字以上の文字になります。

そのため、件数を求める際に活用するCOUNTIF関数の条件にて、「○」を「*」に置き換えて指定します。
実際に指定した例は以下になります。

=COUNTIF(D3:D17,"東京都*")
// D3:D17: 集計範囲（住所列）
// "東京都*": 条件（東京都で始まる任意の文字列）

このように指定することで、以下のように「東京都」の住所の件数を求めることができます。

2. クエスチョンマーク（?）を使ったデータ集計

クエスチョンマーク（?）は、文字列の中の任意の１文字を指定する場合に活用する記号です。

例えば、以下の表のIDから、「A」から始まる数値４桁のIDの件数を取得する場合、「A○○○○」の「○」が任意の１文字になります。

そのため、件数を求める際に活用するCOUNTIF関数の条件にて、「○」を「?」に置き換えて指定します。
実際に指定した例は以下になります。

=COUNTIF(B3:B17,"A????")
// B3:B17: 集計範囲（ID列）
// "A????": 条件（Aから始まる数値4桁の文字列）

このように指定することで、以下のように、Aから始まる数値４桁のIDの件数を求めることができます。

3. 注意点

アスタリスク（*）とクエスチョンマーク（?）のワイルドカードを活用する場合、条件は必ずダブルクォーテーション（”）で囲む必要があります。

アスタリスク（*）やクエスチョンマーク（?）を文字そのものとして扱いたい場合は、以下のように、ワイルドカードの手前にエスケープ文字（~）を指定します。

=COUNTIF(D3:D17,"東京都~*")

4. まとめ

アスタリスクとクエスチョンマークを使いこなすことで、○○IF(S)関数をさらに柔軟に活用できるようになります。

・アスタリスク（*）: 任意の０文字以上の文字列
・クエスチョンマーク（?）: 任意の１文字

これらを活用して、Excelでの条件に応じたデータ集計を効率化してみてください。

表の中でセルを結合してしまうと、並べ替えができないなどと不便になります。
また、セルの結合時は結合範囲の先頭にしか値が入力されていないことになるため、集計も難しくなります。
そこで今回は、以下のように特定の項目に対し、同じ値が続く場合にセルを結合したような見た目にする設定方法について解説していきます。

また今回の方法ですと、並べ替え時にも以下のように、セルを結合したような見た目を維持することができます。

条件式の作成

セルの値に応じてセルの書式を変更するには『条件付き書式』を活用します。
まずは、条件付き書式に設定する条件から考えていきます。

今回は、以下の表のカテゴリーの項目に関して同じ値が続く場合に、２つ目以降のセルの値とセルの上の罫線を非表示にする設定を行います。

同じ値が続いているかを判定する時の条件は、以下になります。

・基準のセルに値が入力されている場合
・基準のセルの値が１つ上のセルの値と同じ場合

カテゴリーの先頭のセルC3について条件式を考えてみると、以下になります。

=AND(C3<>"",C3=C2)
// C3<>""：セルC3が空でないことを確認
// C3=C2：セルC3が1つ上のセルの値と同じことを確認

条件式の作成ができましたら、条件付き書式にて設定していきます。

条件付き書式の設定

まずは、作成した条件式の基準となったセルC3を選択基準として、以下のように選択します。

こちらでは、C列全体を選択した後に、Ctrlキーを押しながらセルC1とC2を選択して選択を解除し、対象の範囲のみを選択しています。

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

条件付き書式の設定画面にて、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にし、表示されたテキストボックス内に、先ほど作成した条件式を入力します。
※アクティブセルに対する条件式を入力します。

=AND(C3<>"",C3=C2)

入力した条件式は、他のセルにもアクティブセルを基準に相対参照で反映されます。
必要に応じて、「$」で数式の一部を絶対参照にしてください。
今回の内容に関しては、相対参照のままで問題ございません。

条件式の入力ができましたら、[書式]を選択し、好みの書式を設定します。
こちらでは、書式設定の画面にて、[罫線]タブから上の罫線を非表示に、[表示形式]タブから[ユーザー定義]にて「;;;」を指定しています。（「;;;」はセルの値を非表示にする表示形式です。）

上記のように設定して確定することで、以下のようにカテゴリーの項目に関して、セルを結合したような見た目にすることができます。

以下のように、スクロールバーを活用してセル内で文章をスクロールできる仕組みの実現方法について解説していきます。

仕組みについて

実現するためには、特定のセル（B2）に文章の一部を数式で抽出する必要があります。
そのため、まずは、セル内の文章全体を別のセルに移動し、そのセルから指定した位置の文章を特定のセル（B2）に抽出していきます。
その後で、抽出する位置をスクロールバーで変更できるようにします。

文章の一部を抽出

まずは、特定のセル（B2）に表示する文章を別のセル（B3）に移動します。

次に、抽出する位置情報を他のセルに入力します。
こちらでは、文章を移動した先の隣のセル（C3）に、仮に「1」と入力しておきます。

では、数式で特定のセルに文章の一部を抽出していきます。
文章全体の中の一部を抽出するには、MID関数を活用します。
MID関数の使い方は、以下になります。

=MID(文字列, 開始位置, 文字数)
// 指定した文字列から指定した位置を基準に指定した文字数分を抽出
// 文字列：抽出元の文字列
// 開始位置：抽出する開始の位置
// 文字数：開始位置からの抽出する文字数

実際にMID関数を活用して、特定のセルの幅に表示できる丁度良い文字数を抽出した数式が以下になります。

=MID(B3,C3,13)
// セルB3の文字列の1文字目から13文字を抽出

以上の手順で、指定した位置を基準に、文字列の一部を抽出することができます。

スクロールバーの作成

後は、抽出する位置を表す「1」という数値をスクロールバーによって変更できるようにしていきます。
スクロールバーは、[開発]タブの中の[挿入]の[フォームコントロール]から作成することができます。

スクロールバーは、横長に作成すると横向き、縦長に作成すると縦向きで作成されます。
こちらでは、抽出元の文章を隠すようにスクロールバーを以下のように作成します。

※Altキーを押しながら作成することで、セルの幅に合わせることができます。

次に、作成したスクロールバーの数値を、抽出する位置を表す数値が入力されたセル（C3）と紐づけます。
スクロールバー上で右クリックし、[コントロールの書式設定]を開きます。

書式設定の[リンクするセル]に、紐づけたいセルを指定します。
必要に応じて、他の設定値に関しても調整します。

仕組みとしては完成です。
必要に応じて、文字の位置を表す数値を、表示形式などを活用して非表示にすると良いです。
表示形式でセルの値を非表示にする場合は、[セルの書式設定]の[表示形式]タブの[ユーザー定義]にて「;;;」と指定します。

以上の手順で完成です。
スクロールバーで抽出する文字の位置を変更することができるため、セル内で文章がスクロールしているように見せることができます。

以下の左側の表は、会員一覧表になっており、各会員の加入日と退会日を記録しています。
この表の退会日が入力されている会員の中で、直近の退会者を右側の表に３件のみを自動で抽出する方法について解説していきます。

直近の退会者を自動抽出する流れ

直近の退会者を抽出する流れは、以下になります。

① 会員一覧表の退会日を基準に降順にする
② ①で降順にされた会員一覧表の先頭から３件を抽出する

では、上記の流れを数式で表現していきます。

① 会員一覧表の退会日を基準に降順にする

指定した表を指定した順番に並べ替えるには、『SORT関数』を活用します。
SORT関数の使い方は、以下になります。

=SORT(配列, [並べ替えインデックス], [並べ替え順序], [並べ替え基準])
// 指定した配列を指定した順序に並べ替えた表を返す
// 配列：対象の表
// [並べ替えインデックス]：対象の表の中の、対象の列(行)の、表の先頭からの列(行)番号 ※省略時は先頭列(行)
// [並べ替え順序]：1→昇順、-1→降順 ※省略時は昇順
// [並べ替え基準]：TRUE→列で並べ替え、FALSE→行で並べ替え ※省略時は行で並べ替え

実際に、右側の表の先頭に、左側の会員一覧表の退会日を降順にした表を抽出する場合、以下の数式になります。

=SORT(A3:D1000,4,-1)
// 表の範囲は大きめに指定

② ①で降順にされた会員一覧表の先頭から３件を抽出する

指定した表の先頭から指定した件数を抽出するには、『TAKE関数』を活用します。
TAKE関数の使い方は、以下になります。

=TAKE(配列, 行数, [列数])
// 指定した配列から指定した行数（列数）を抽出する
// 配列：対象の表
// 行数：抽出する行数（正の数：上から抽出、負の数：下から抽出）
// [列数]：抽出する列数（正の数：上から抽出、負の数：下から抽出）
// ※[列数]は省略可能、省略時は全ての列を抽出

TAKE関数の[配列]に、①の数式を指定し、[行数]に３件分の３を指定することで、以下のように抽出することができます。

=TAKE(SORT(A3:D1000,4,-1),3)

補足

表の範囲を「A3:D1000」などではなく、データの増減に対応した範囲にしたい場合は、会員一覧表をテーブルにすると良いです。
テーブルに変換することで、以下のように、構造化参照を活用して数式を作成することができ、データの増減に対応することができます。

=TAKE(SORT(会員一覧表,4,-1),3)

以下のような、年月別で販売数をまとめたシートを複数用意しています。

各シートの合計販売数（セルC4）の値を、以下のような「全体」シートに、１つの数式のみで一括抽出する方法について解説していきます。

１つの数式のみで一括抽出する方法

複数の範囲の値を縦一列に抽出する場合、『VSTACK関数』を活用します。
VSTACK関数の使い方は、以下になります。

=VSTACK(配列1, [配列2], [配列3], …)
// 配列：対象の表（必要な数分、カンマ区切りで指定する）

シートが別々でも、各シートの同じ位置のセルに入力されている値の場合、簡単に参照することができます。
実際に、VSTACK関数を活用して一括で抽出する数式は、以下になります。

=VSTACK('202401:202412'!C4)

複数シートの範囲は「先頭のシート名:末尾のシート名!アドレス」で指定することができます。
「=VSTACK(」まで入力したら、先頭のシートタブを選択し、Shiftキーを押しながら末尾のシートタブを選択して、末尾のシートの対象のセルを選択することで「’202401:202412′!C4」というように入力することができます。
※シート名の前後の「’」は、シート名によっては自動で入力される場合があります。自動で入力されない場合は、そのままで問題ございません。

この数式を抽出先の先頭のセル（セルC3）に入力して確定することで、以下のように一括で抽出することができます。

以下のように、指定した表の指定した項目の値が、指定した値と一致する先頭のデータと末尾のデータを抽出する方法について解説していきます。

抽出方法

抽出するには、『XLOOKUP関数』を活用します。
XLOOKUP関数の使い方は、以下になります。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
// 検索範囲で検索値と一致した位置と同じ位置の戻り範囲のデータを抽出
// 検索値：検索対象の値
// 検索範囲：検索して位置情報を取得する範囲
// 戻り範囲：取得した位置情報から抽出する範囲
// [見つからない場合]：検索結果が存在しない場合に表示する値 ※省略時は「#N/A」になる
// [一致モード]：検索方法（0：完全一致、-1：以下、1：以上、2：ワイルドカード文字との一致）※省略時は「0：完全一致」が指定される
// [検索モード]：検索順序（1：先頭から末尾へ検索、-1：末尾から先頭へ検索、2：バイナリ検索（昇順で並べ替え）、-2：バイナリ検索（降順で並べ替え））※省略時は「1：先頭から末尾へ検索」が指定される

XLOOKUP関数の引数には「検索モード」が存在します。
この引数を指定することによって、「先頭から末尾へ検索」と「末尾から先頭へ検索」を切り替えることができます。
そのため、先頭のデータを抽出したい場合は、以下のように「検索モード」を「1」にすることで抽出できます。

=XLOOKUP(B3,B7:B21,C7:D21,"",0,1)
// 検索値：B3（商品名）
// 検索範囲：B7:B21（商品名の項目全体）
// 戻り範囲：C7:D21（価格と更新日の項目全体）
// 見つからない場合：""（何も表示しない）
// 一致モード：0（検索値と一致するデータを抽出）
// 検索モード：1（一致する先頭のデータを抽出）

末尾のデータを抽出したい場合は、以下のように「検索モード」を「-1」にすることで抽出できます。

=XLOOKUP(B3,B7:B21,C7:D21,"",0,-1)
// 検索値：B3（商品名）
// 検索範囲：B7:B21（商品名の項目全体）
// 戻り範囲：C7:D21（価格と更新日の項目全体）
// 見つからない場合：""（何も表示しない）
// 一致モード：0（検索値と一致するデータを抽出）
// 検索モード：-1（一致する先頭のデータを抽出）