KE1

図形にテキストを入力すると、以下のように、「余白が広すぎて全体が表示されない」といったことがあります。

実は、ある設定を行うことで、図形の幅ギリギリまでテキストを表示することができるようになります。
今回は、その方法について紹介していきます。

設定手順

まずは、対象の図形の書式設定を開きます。

次に、[サイズとプロパティ]の[テキストボックス]を展開して、[テキストを図形からはみ出して表示する]にチェックし、[図形内でテキストを折り返す]のチェックを外します。

そして最後に、テキストを中央揃えにします。

以上で、設定完了です。
手動で改行する必要はありますが、図形の幅ギリギリまでテキストを表示することができます。

Excel2021以降で使用できるXLOOKUP関数は、VLOOKUP関数の欠点を解消しています。
その中で、特にVLOOKUP関数よりも便利に感じた点を３つ紹介していきます。

便利に感じた点①

１つ目は、検索値の項目の位置が自由になったという点です。
VLOOKUP関数では、必ず検索値の項目が指定した表の一番左側にある必要がありました。
しかし、XLOOKUP関数では、検索値の項目と抽出する項目を別々で指定するため、表の一番左側にある必要がなくなりました。

便利に感じた点②

２つ目は、抽出する項目の位置を列番号で指定する必要がなくなったという点です。
VLOOKUP関数では、抽出する項目の位置を、指定した表の先頭からの列番号で指定する必要がありました。
しかし、XLOOKUP関数では、抽出する項目を直接指定するだけでよくなりました。

便利に感じた点③

３つ目は、IFERROR関数などでエラー対策をする必要がないという点です。
VLOOKUP関数では、検索条件に合う抽出対象が存在しない時は「#N/A」というエラーになってしまいました。
そのため、エラーではなく「なし」のような別の文字を指定するには、IFERROR関数などを組み合わせて実現していたかと思います。
しかし、XLOOKUP関数には、「見つからない場合」という引数が追加され、IFERROR関数などを組み合わせる必要がなくなりました。

まとめ

今回紹介した３つ以外にも、XLOOKUP関数には多くの魅力があります。
XLOOKUP関数が使える環境の方で使ったことがない方は、ぜひ使ってみてください。

動画でも解説しています。

＞今後主流の「XLOOKUP関数」の魅力とは

以下のようなタスク管理表から、直近に完了したタスクをすぐに確認したいとき、ぱっと見では分かりづらいです。

とはいえ、毎回並べ替えするのも面倒…

ということで今回は、数式で完了したタスクのみを完了日順に抽出する方法について紹介していきます。

1. 完了したタスクを抽出する

今回の場合、以下の表の、完了日の項目に値が入力されているタスクが「完了したタスク」になります。

そのため、完了日に何かしら値が入力されている行のみを「FILTER関数」で抽出します。

タスク管理表（見出し行を除く）が３行目から２７行目まで用意されている場合の数式は、以下になります。

=FILTER(B3:E27,E3:E27<>"","")
// B3:E27：抽出対象の項目の範囲（タスク管理表のデータの範囲）
// E3:E27<>""：抽出条件（完了日の項目が空でない）
// ""：対象が見つからない場合に表示する値（何も表示しない）

上記の数式で、完了したタスクのみを抽出することができます。

2. 完了日を基準に降順にする

次に、先ほど抽出した「完了したタスク」を、「SORT関数」で完了日を基準に降順にします。

その数式は、以下になります。

=SORT(FILTER(B3:E27,E3:E27<>"",""),4,-1)
// FILTER(B3:E27,E3:E27<>"","")：並べ替え対象の表（FILTER関数で抽出した表）
// 4：並べ替える基準の列（完了日の項目は先頭から4列目）
// -1：順序（降順）

上記の数式で、完了日を基準に降順にすることができます。

以下の表の「合否」の項目には、数式が入力されています。

この数式の場合、表示する行全体（C3～C9）に、数式をコピーする必要があります。

そこで今回は、数式をコピーする必要のない、新機能『TRIM参照』を活用した方法について紹介していきます。

スピルの活用

まずは、スピルを活用した数式に修正します。
スピルとは、数式を入力したセルから、結果を展開して表示する機能です。

＞スピルについての詳しい解説はこちら

以下の表の場合、結果を９行目まで表示させる必要があります。

そのため、「合否」の先頭のセルに、以下のスピルを活用した数式を入力します。

=IF(B3:B9>=60,"合格","不合格")

ただ、このままだと、１０行目以降にデータが追加された際に、毎回手動で数式を更新する必要があります。
とはいえ、以下のように、予め大きめの範囲を指定すると、データがない行まで計算処理が行われてしまいます。

=IF(B3:B100>=60,"合格","不合格")

無駄な行まで計算処理が行われると、ファイルの計算処理が重たくなってしまう原因にも繋がりかねません。

TIRM参照の活用

そこで、新機能の「TRIM参照」を活用します。
TRIM参照とは、指定した範囲の先頭や末尾から空白を除外した範囲を参照する参照方法です。

＞TRIM参照についての詳しい解説はこちら

以下のように、「:」の後に「.」を加えます。

=IF(B3:.B100>=60,"合格","不合格")

たったこれだけで、以下のように、「B3～B100」の末尾の空白セルを除外した範囲のみを指定して計算することができます。

TRIM参照を活用することで、データの追加にも対応することができます。

表の各データに対応したフォルダへのハイパーリンクを作成する際、１つ１つ、「ハイパーリンクの挿入」画面から割り当てるのは大変です。

各データに対応したフォルダの位置に、以下のような法則性がある場合は、瞬時にハイパーリンクを作成する方法があります。

『すべてのフォルダは同じ配下に配置されており、フォルダ名は「No_氏名」の構成になっている』

ということで今回は、各データに対応したフォルダの位置に法則性がある場合、その各フォルダを表示するハイパーリンクを瞬時に作成する方法について紹介していきます。

セルの値を活用してハイパーリンクを作成する

各データに対応したフォルダを作成する場合は、HYPERLINK関数が便利です。
この関数なら、セルの値を活用したハイパーリンクを作成することができます。

まずは、先頭のデータに対応したフォルダのパスを確認します。
こちらの場合は、「A:\生徒管理\1_渡辺 紬」になります。

次に、対象のフォルダの上で右クリックし、パスをコピーします。
OSのバージョンによっては、Shiftキーを押しながら右クリックする必要があります。

次に、以下の数式を先頭行に入力します。

=HYPERLINK("A:\生徒管理\1_渡辺 紬","開く")
// フォルダのパスの部分（赤）は、コピーしたパスを貼り付ける

これで、先頭のデータに対応したフォルダを開くハイパーリンクを作成することができます。

他のデータに関しては、先ほどのフォルダのパスの一部を変更する必要があります。
こちらの場合は、「No_氏名」がフォルダの名前になるため、以下のように、セルの値を参照した数式に書き換えます。

=HYPERLINK("A:\生徒管理\"&B3&"_"&C3,"開く")
// 「&」で「No_氏名」となるように繋げる

最後に、入力した数式を表の末尾までコピーするだけで完成です。

これで、該当行のハイパーリンクをクリックするだけで、対象のフォルダを開くことができます。

※以下のような警告が表示される場合は、「はい」を選択することで、フォルダを表示できます。

以下の予定表のように、連続した予定を結合した見た目にし、背景色を色付けする「条件付き書式」の設定方法について解説していきます。

条件付き書式に設定する条件式

こちらでは、以下の表をもとに作成していきます。

まずは、どのような条件を満たしたときに、以下の書式を反映させる必要があるのか考えていきます。

① セルに色付けする
② セルの値を非表示にする
③ セルの罫線を非表示にする

①のセルの色付けに関しては、B列とC列の２列に適用する必要があります。
この該当行の２列を色付けするかどうかは、C列に値が入力されているかどうかで判断できます。
例えば、３行目の場合、以下のような条件式になります。

=$C3<>""
// C列で固定のため、Cの手前に「$」を指定

次、②のセルの値を非表示にすることに関しては、C列のみに適用する必要があります。
Ｃ列の対象のセルの値を非表示にするかどうかは、１つ前の値と同じかどうかで判断できます。
例えば、３行目のＣ列の場合、以下のような条件式になります。

=C3=C2
// C列のみのため、Cを「$」で固定しなくてもよい

さらに、セルが空の場合は、非表示にする必要はないので、「空でない場合」という条件を加えます。
例えば、３行目のＣ列の場合、以下のような条件式になります。

=AND(C3<>"",C3=C2)
// ANDで2つの条件式を指定

最後、③のセルの罫線を非表示にすることに関しては、②と同様にC列のみに適用する必要があります。
また、条件式に関しても②と同様で、②の条件を満たしたときに、そのセルの上の罫線を非表示にします。

条件付き書式の設定

では、以下の条件式を条件付き書式で設定していきます。

① セルに色付けする（B列とC列）：=$C3<>””
② セルの値を非表示にする（C列）：=AND(C3<>””,C3=C2)
③ セルの罫線を非表示にする（C列）：=AND(C3<>””,C3=C2)

まずは、①のセルに色付けする対象の範囲（見出しを除いた範囲全体）を選択します。
この時、選択基準のセル（アクティブセル）は、以下のように、３行目になるようにします。

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

入力する数式は、アクティブセルに対しての数式になります。
上記では、セルB3がアクティブセルのため、セルB3に対しての以下の条件式を入力しています。

=$C3<>""

他の選択範囲には相対参照（「$」で固定された列または行を除く）で反映されます。
そのため、上記の設定内容で確定することで、以下のように予定のある行を色付けすることができます。

次は、②のセルの値を非表示にする対象の範囲（見出しを除いたC列全体）を選択します。
この時、選択基準のセル（アクティブセル）は、以下のように、セルC3になるようにします。

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

入力する数式は、アクティブセルに対しての数式になります。
上記では、セルC3がアクティブセルのため、セルC3に対しての以下の条件式を入力しています。

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

また、③のセルの罫線を非表示する範囲と条件式に関しても、②と同様になるので、「セルの書式設定」の画面で「セルの罫線を非表示にする設定」を行います。
条件を満たしたときに非表示にする罫線は、セルの上の罫線になるため、以下のように設定します。

上の罫線を２回クリックすることで、非表示にできます。

上記の設定内容で確定することで完成です。
以下のように、連続した予定を結合した見た目にし、背景色を色付けすることができます。

以下のように、ある一覧の重複した値の先頭以外に「不要」と表示させる方法について解説していきます。

1. 重複の有無を確認する

まずは、重複しているかどうかを確認していきます。
今回は、COUNTIF関数を活用して、対象の名前までの範囲に、対象の名前が何件存在しているのかを表示させることで、重複しているかどうかを確認します。
そのため、一覧表の隣の列の先頭に、以下の数式を入力します。

=COUNTIF($B$3:B3,B3)
// 集計対象の範囲：$B$3:B3 
// （下の行にコピーしたときに徐々に範囲が拡張するように、先頭のセルを「$」で固定する）
// 集計対象の値：B3

表の末尾までコピーすると、以下のようになります。

表示された数値が２以上の場合、２回目以降の登場という意味になるため、先頭以外の重複した値と判断できます。
要するに、２以上の値が「不要」と表示させる対象になります。

2. 重複した値の先頭以外に「不要」と表示させる

次は、１は非表示に、２以上に「不要」と表示させます。
そのためには、以下のようにIF関数を加えます。

=IF(COUNTIF($B$3:B3,B3)>1,"不要","")
// 条件：COUNTIF($B$3:B3,B3)>1
// 条件を満たしたときに表示する値："不要"
// 条件を満たしていないときに表示する値：""（空）

表の末尾までコピーすると、以下のようになります。

これで完成です。

以下のように、A列に用意されているチェックボックスにチェックするだけで、チェックしたデータが別シートに抽出される仕組みの実現方法について解説していきます。

※こちらで実現したファイルは、記事の最後にて配布しています。

1. チェックボックスの用意

まずは、抽出元シートのA列にチェックボックスを用意します。

A列のチェックボックスを作成したい範囲を選択し、[挿入]タブの中の[チェックボックス]を選択します。

これだけで、チェックボックスを作成することができます。

[データ]タブの中に[チェックボックス]という項目がない場合は、以下の記事を参考にして、チェックボックスを作成してください。
＞チェックボックスの作成方法

チェックボックスが配置されているセルには、TRUE/FALSEという値が入力されています。

2. チェックしたデータの抽出

次は、チェックしたデータを抽出します。

指定した範囲のデータから指定した条件を満たしたデータを抽出するには、FILTER関数が便利です。
抽出先のシートに、以下のような数式を入力します。
※抽出元の表は、「商品一覧」シートに用意しています。

=FILTER(商品一覧!B2:E1000,商品一覧!A2:A1000=TRUE,"")
// 商品一覧!B2:E1000：抽出対象の表の範囲
// 商品一覧!A2:A1000=TRUE：抽出条件（A2:A1000の中でTRUEのデータを抽出）

ちなみに、抽出元の表のA列に、TRUE/FALSE以外（空白セルを除く）の値が入力されていない場合は、以下のように「=TRUE」を省略することもできます。

=FILTER(商品一覧!B2:E1000,商品一覧!A2:A1000,"")

3. 完成

以上の手順で完成です。
抽出元の表のA列をクリックし、チェックボックスにチェックすることで、チェックしたデータのみが別シートに抽出されます。

抽出される順番は、チェックした順番ではなく、抽出元の表の先頭からの順番で抽出されます。

▼サンプルファイル▼

※Excelのバージョンが新しいチェックボックスなどに対応していない場合は、正しく表示されません。

FILTER関数は、表から指定した条件を満たしたデータのみを抽出する関数です。
表の中の連続した項目を抽出する場合は、以下のように抽出することができます。

（例）「雇用形態」が「アルバイト」の「氏名・雇用形態・所属部署」を抽出

=FILTER(B:D,C:C="アルバイト","")

しかし、離れている項目の場合、以下のように、項目ごとにFILTER関数を入力して抽出している方も少なくないかと思います。

（例）「雇用形態」が「アルバイト」の「氏名・入社日」を抽出

=FILTER(B:B,C:C="アルバイト","")
=FILTER(F:F,C:C="アルバイト","")

もちろん、このままでも抽出はできますが、以下のようなデメリットがあります。

・抽出条件や抽出項目が変わった時に、項目ごとの数式を１つ１つ修正する必要がある
・FILTER関数２つ分の計算処理が必要なため、表のサイズによってはファイルの動きが遅くなる

ということで、こちらでは、FILTER関数１つで離れている項目を抽出する方法について解説していきます。

離れている項目を指定する

連続した項目の場合は、以下のように「:」で範囲を一括で指定することができます。

=FILTER(B:D,C:C="アルバイト","")

しかし、離れている項目の場合、「:」だけで範囲を選択することができません。
そんな時は、HSTACK関数を組み合わせます。
HSTACK関数では、以下のように、指定した複数の範囲を水平方向に繋げることができます。

（例）「氏名・入社日」を先頭から６件抽出

=HSTACK(B2:B7,F2:F7)

そのため、HSTACK関数を使って、以下のような数式にすることで、連続した項目を１つの数式のみで抽出することができます。

（例）「雇用形態」が「アルバイト」の「氏名・入社日」を抽出

=FILTER(HSTACK(B:B,F:F),C:C="アルバイト","")

まとめ

今回のように、１つの数式にすることで、以下の欠点が解消されます。

・抽出条件や抽出項目が変わった時に、項目ごとの数式を１つ１つ修正する必要がある
・FILTER関数２つ分の計算処理が必要なため、表のサイズによってはファイルの動きが遅くなる

しかし、今回の方法の場合、列を挿入すると、以下のように見出しとデータがずれてしまう可能性があります。

そのため、項目数が多い場合などは、見出し自体も数式で抽出するなどと対策すると良いです。