作者別: it-yobi

以下のように、期間を指定するだけで、自動で空の予定表が表示される仕組みの作成方法について解説していきます。

1. SEQUENCE関数を使用して日付を表示

まず、SEQUENCE関数を使って対象期間の日付を一覧表示します。
SEQUENCE関数の使い方は、以下になります。

=SEQUENCE([行], [列], [開始], [目盛り])
// 指定した設定値で連続した数値を返す
// [行]：表示する行数（省略時は「1」）
// [列]：表示する列数（省略時は「1」）
// [開始]：連番を開始する数値（省略時は「1」）
// [目盛り]：連番の間隔（省略時は「1」）

日付に関しても「シリアル値」という数値の扱いになるため、SEQUENCE関数を活用して連続した日付を表示することができます。
開始日と終了日を指定して、その期間を表示する場合の数式は以下になります。

=SEQUENCE(終了日-開始日+1,,開始日)
// 終了日-開始日+1：表示する行数は表示する期間となり終了日と開始日の差に1を加えた日数になる
// 開始日：開始日を基準に連番を表示する

こちらの数式を、以下の表に当てはめます。

当てはめた数式は以下になります。

=SEQUENCE(B4-B3+1,,B3)
// B4-B3+1：表示する行数（期間）
// B3：連番の開始（開始日）

このように、指定した期間の日付を一覧として表示することができます。

2. 条件付き書式で自動的に罫線を表示

次に、条件付き書式を使用して、日付が入力されているセルに自動的に罫線を設定します。

まずは、設定する対象の範囲を選択します。
以下の表の場合は、B列とC列の７行目以降が対象範囲となります。

そのため、B列とC列全体を選択し、Ctrlキーを押しながら対象外の範囲（セルB1～C6）を選択し、対象外の範囲の選択を解除します。

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

表示された設定画面にて、[ルールの種類]を[数式を使用して、書式設定するセルを決定]を選択し、表示されたテキストボックスに以下の数式を入力します。

=$B7<>""
// 選択基準のセルB7に対しての罫線を表示する条件
// 「セルB7が空でない場合」
// 他の選択範囲には相対参照で反映（B列のみ「$」で固定）

次に[書式]を選択し、[罫線]タブから[外枠]を指定します。

以上の設定で確定することで、日付が入力されている行のB列とC列に罫線を自動で表示することができます。

以下の表は、各商品の価格をまとめた表になります。

価格が改定される度、表の右側に改定後の価格を登録する想定になります。
この表から、以下のように最新価格（表の一番右側の価格）を自動で抽出する数式の作成方法について解説していきます。

手順1: 抽出方法

こちらでは、VLOOKUP関数とCOUNTA関数を組み合わせて最新の価格を抽出していきます。
まずは、VLOOKUP関数から順番に解説していきます。

手順2: VLOOKUP関数の基本設定

VLOOKUP関数の使い方は、以下になります。

=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
// 指定した[範囲]から[検索値]で検索し、該当するデータの指定した[列番号]の位置にある項目値を返す
// [検索値]で検索する項目は、指定した[範囲]の一番左側の項目である必要がある
// [検索方法]で抽出する条件を指定する（TRUE：近似一致（省略時）、FALSE：完全一致）

実際に活用して、最新の価格（４列目）を抽出する場合の数式は、以下になります。

=VLOOKUP(B4,$B$13:$XFD$16,4,FALSE)
// B4: 検索する商品名が入力されているセル
// $B$13:$XFD$16: 商品別価格表の範囲（「$」で固定）
// 4: 最新価格がある列番号（仮設定）
// FALSE: 完全一致で検索

最新価格の列の位置が固定の場合は、上記のような数式で、最新価格を抽出することができます。

手順3: COUNTA関数の組み合わせ

先ほどの数式のままですと、価格が追加された際に、最新価格を抽出することができなくなります。

そのため、COUNTA関数を用いて、常に最新の価格が抽出されるように修正します。
COUNTA関数の使い方は、以下になります。

=COUNTA(値1, [値2], [値3], …)
// 指定した[値]の中で空でない値の数を返す

COUNTA関数を活用することで、動的な列番号を取得することができます。
VLOOKUP関数で抽出する列番号とは、今回の表の場合、改定日が入力されている、もしくは今後入力される範囲（セルC12からセルXFD12）内で値が入力されているセルの数に１を加えた数になります。

実際に列番号を抽出する場合の数式は、以下のようになります。

=COUNTA(C12:XFD12)+1
// C12:XFD12：改定日の範囲

こちらの数式をVLOOKUP関数に組み合わせると、以下のようになります。

=VLOOKUP(B4,$B$13:$XFD$16,COUNTA($C$12:$XFD$12)+1,FALSE)
// COUNTA($C$12:$XFD$12)+1：列番号の取得（「$」で固定）

このように、先頭の商品に関して、最新価格を抽出することができました。

手順4: 数式のコピー

最後に、先頭に入力した数式を下の行までコピーすることで、以下のように、各商品に対して、最新価格を自動で抽出することができます。

関数を組み合わせることによって、実現できることが大幅に広がります。
ぜひ試してみてください。

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

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

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

手順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）と紐づけます。
スクロールバー上で右クリックし、[コントロールの書式設定]を開きます。

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

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

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