2024/06/21
【Excel】存在しているシートのみ目次にリンクを表示

【Excel】存在しているシートのみ目次にリンクを表示

以下のように、予め、[目次]シートに追加する予定のシート名一覧表を作成し、存在しているシートのみに、そのシートへ遷移するリンクを表示する方法について解説していきます。


・シートへのリンクを作成

初めに、以下のようなシート名一覧表を作成し、今後追加する予定のシート名を入力します。
また、シート名一覧表には、[リンク]という項目を用意します。

まずは、それぞれのシートのセルA1に遷移するリンクを、HYPERLINK関数を用いて作成していきます。
この関数の使い方は、以下になります。

=HYPERLINK(リンク先, [別名])
// 指定したリンク先に遷移するリンクを作成する
// リンク先:リンク先の情報を指定
// [別名]:セルに表示させる文字を指定(省略時は引数[リンク先]に指定した値を表示する)

HYPERLINK関数を用いて、特定のシートのセルに遷移するリンクを作成する場合は、引数[リンク先]に、「#セルのアドレス」と指定する必要があります。

例えば、「ABC」というシートのセルA1に遷移する場合は、「#ABC!A1」になります。
「!」などの特殊な文字が含まれるシート名「!ABC」のシートに遷移する場合は、「#!ABC!A1」などと、シート名を「」で囲む必要があります。
そのため、予め「’」で囲んだシート名を指定しておくと良いです。

今回の場合、シート名は、表の中のセルの値を参照する必要があるため、「&」を活用し、以下のような数式を入力します。

=HYPERLINK("#'"&B4&"'!A1","●")
//「#’」とセルB4の値、「’!A1」を「&」で文字結合

後は、こちらの数式を表の最後までコピーすることで、リンクを作成することができます。

ただ、このままですと、存在していないシートに関してもリンクが表示されてしまいます。
存在していないシートを選択すると、以下のように、エラーが表示されてしまいます。

ということで、次は、存在しないシートに関しては、リンクを表示させない仕組みを実現していきます。

・存在しないシートのリンクを非表示にする

HYPERLINK関数のみですと、常にリンクが表示されてしまうため、他の関数を組み合わせる必要があります。
まずは、シートの存在の有無を数式で求めていきます。

シートの存在の有無を求めるには、INDIRECT関数が便利です。
この関数の使い方は、以下になります。

=INDIRECT(参照文字列)
// 指定した参照文字列を参照した値を返えす
// 参照文字列:参照先の情報

INDIRECT関数を活用して、引数にリンク先のアドレスを指定することによって、そのリンク先が存在しない場合にエラーを返すことができます。

試しに、表の隣にINDIECT関数を入力して、シートの存在の有無を確認していきます。

=INDIRECT("'"&B4&"'!A1")

数式を入力して表の最後までコピーすると、存在しているシートの場合は、そのシートの指定したセルの値が表示され、存在していない場合は、「#REF!」というエラーが表示されます。


次に、エラーの場合はTRUE、エラー出ない場合はFALSEを返すISERROR関数で囲みます。(ISERROR関数で囲むだけなので、関数の解説は省略します)

=ISERROR(INDIRECT("'"&B4&"'!A1"))

このようにTRUEとFALSEを表示することができました。
後は、TRUEの場合のみに、HYPERLINK関数の内容を表示すればよいので、IF関数を活用します。
この関数の使い方は、以下になります。

=IF(論理式, 値が真の場合, 値が偽の場合)
// 論理式の値に応じて返す値を変更する
// 論理式:TRUEもしくはFALSEを返す式
// 値が真の場合:論理式がTRUEの時に返す値
// 値が偽の場合:論理式がFALSEの時に返す値

実際に、INDIRECT関数、ISERROR関数、IF関数をHYPERLINK関数の数式に組み合わせると、以下のようになります。

=IF(ISERROR(INDIRECT("'"&B4&"'!A1")),"",HYPERLINK("#'"&B4&"'!A1","●"))

このようにして、シートが存在している行のみにリンクを表示させることができます。
リンクの色が青色にならない場合は、手動で書式を設定すると良いです。

この作成方法の場合、リンクが表示されていない空白のセル上にカーソルを移動した時に関しても、カーソルが指マークになってしまいます。
ただ、リンクとしては機能せず、クリックしてもセルが選択されるだけなので、そこまで問題ではないです。
該当するシートが追加された時に、自動でリンクが表示されます。

2024/06/19
【Excel】トップ3を抽出する方法

【Excel】トップ3を抽出する方法

最新の関数を用いて、以下の社員ごとの売上表を参照し、赤枠に上位3件を抽出する方法について解説していきます。


まずは、売上表の「売上」を基準に降順にした表を、SORT関数を用いて抽出していきます。
この関数の使い方は、以下になります。

=SORT(配列, [並べ替えインデックス], [並べ替え順序], [並べ替え基準])
// 指定した配列を指定した順序に並べ替えた表を返す
// 配列:対象の表
// [並べ替えインデックス]:並べ替えの基準となる項目の位置
// [並べ替え順序]:「1」昇順、「-1」降順
// [並べ替え基準]:「TRUE」列で並べ替え、「FALSE」行で並べ替え
// ※引数「配列」以外は省略可能

では、SORT関数を用いて、セルG3を基準に、表を抽出していきます。

=SORT(B3:D12,3,-1)
// B3:B12:表の範囲
// 3:売り上げの項目の位置(先頭から3列目)
// -1:降順

表を抽出することができましたら、抽出した表の先頭から3件のみを抽出します。
その際に活用する関数は、TAKE関数になります。
この関数の使い方は、以下になります。

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

では、TAKE関数を用いて、先頭から3件のみを抽出していきます。

=TAKE(SORT(B3:D12,3,-1),3)
// SORT(B3:D12,3,-1):抽出する対象の表
// 3:先頭から抽出する行数

このようにして、売上の上位3件を抽出することができます。

2024/06/17
【Excel】曜日単位で瞬時に集計

【Excel】曜日単位で瞬時に集計

以下の表に関して、瞬時に曜日単位の集計表を作成する方法について解説していきます。


まずは、曜日単位で集計するために、曜日の列を追加します。

こちらでは、日付の列の隣に列を挿入し、曜日という項目を追加しています。

次に、日付から曜日を求めていきます。
そのような際は、TEXT関数が便利です。

=TEXT(値, 表示形式)
// [値]に対し[表示形式]を適用した文字列を返す

日付から曜日を表示する表示形式は「aaa」になります。
そのため、TEXT関数を用いて、表示形式「aaa」を適用した文字列を取得します。

=TEXT(B3,"aaa")

数式を入力後、最終行までコピーして、曜日を表示させます。

次に、表の中を選択し、[挿入]タブより[ピボットテーブル]を選択して、ピボットテーブルを作成します。

ピボットテーブルを作成すると、[ピボットテーブルのフィールド]という画面が表示されます。
表示された画面にて、[曜日]を[行]に、[売上]を[値]に移動させます。

以上の作業を行うだけで、以下のような曜日単位の集計表を作成することができます。

曜日の順番に関しては、該当する曜日を選択して、緑の枠上(カーソルが十字矢印になる位置)でドラッグすることで移動することができます。

必要に応じて、作成したピボットテーブルを選択した時に表示される[デザイン]タブなどを活用し、デザインに関しても変更すると良いです。

2024/06/14
【Excel】条件付き書式で二重の罫線を設定

【Excel】条件付き書式で二重の罫線を設定

以下の表では、日付が変わる位置を二重の罫線にしています。
この罫線は、条件付き書式により、自動で設定されます。

しかし、条件付き書式で設定できる罫線には、二重の罫線は存在していません。

通常の機能としては用意されていないのですが、とある方法により、二重の罫線を条件付き書式で表現することが可能になります。
今回は、その方法について解説していきます。

・通常の罫線

まずは、二重の罫線ではなく、通常の罫線を設定する方法について解説していきます。

こちらでは、以下の表を元に、日付が変わる位置に罫線を設定する条件付き書式を設定していきます。

まずは、罫線を設定する可能性がある対象の範囲(条件付き書式を設定する範囲)を選択します。
こちらでは、A~C列全体を選択し、その後にCtrlキーを押しながら、セルA1~C2を選択して、以下のように対象の範囲のみを選択します。

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

表示された条件付き書式の設定画面にて、[ルールの種類]を[数式を使用して、…]にし、条件式を入力していきます。

条件式は、選択基準のセルに対しての数式になります。
こちらの場合は、セルA3が選択基準のため、「セルA3の場合、どのような時に罫線を設定するのか」を考えていきます。
その条件とは、以下のようになります。

・セルA3とA4が一致しない場合

数式にすると「=A3<>A4」になります。
また、この条件式は、他の選択している範囲にも相対的に反映されます。

今回罫線を設定する範囲は、A~C列になるため、B列とC列の場合を考慮する必要があります。
こちらでは、日付が不一致の場合という条件を指定する必要があるため、B列やC列であっても、必ずA列を確認する必要があります。
そのため、以下のように、A列に「$」を加え、絶対参照にして、条件式を設定します。

=$A3<>$A4

設定後は、[書式]を選択し、[罫線]タブより、下側の罫線のみを指定します。

以上の設定を行ってから確定することで、以下のように、罫線を設定することができます。

・二重の罫線

では次に、二重の罫線を設定する方法について解説していきます。
先ほどの罫線とは違い、二重の罫線は、設定画面には表示されません。
そのため、考え方を変える必要があります。

別の考え方とは、「予め、二重の罫線を設定しておき、日付が同じ場合に、通常の罫線に戻す」というものです。
条件付き書式で、二重の罫線が設定できないため、二重の罫線を設定するのではなく、二重の罫線を通常の罫線に戻す方向で考えていくと、実現することが可能になります。

そのため、まずは、表全体の罫線を二重の罫線にします。

次に、二重の罫線を設定した範囲を選択している状態で、[条件付き書式]の[新しいルール]を選択します。

表示された条件付き書式の設定画面にて、[ルールの種類]を[数式を使用して、…]にし、条件式を入力していきます。

こちらでは、二重の罫線を通常の罫線に戻す条件式を入力する必要があるため、以下のような、先ほどとは逆の条件式になります。

=$A3=$A4

この条件式を設定し、以下のように、[書式]にて通常の罫線を設定します。

以上の設定を行ってから確定することで、以下のように、二重の罫線を設定することができます。
(正確には、対象外の二重の罫線を通常の罫線に戻すことができます)


・補足

同じ理屈で、太い罫線などといった他の罫線でも設定することができるので、こちらの方法を覚えておくと役立ちます。

2024/06/12
【Excel】必要な範囲以外を非表示にする

【Excel】必要な範囲以外を非表示にする

以下のシートのように、必要な範囲(セルA1~D9)のみを表示し、それ以外の範囲を非表示にする方法について解説していきます。


まずは、以下のシートのE列以降を非表示にしていきます。

まずは、E列全体を選択して、CtrlキーとShiftキーを押しながら、カーソル(矢印)キーの「→」を押すことで、E列以降のセルを全選択します。

選択後は、選択範囲内で右クリックし、メニューから[非表示]を選択します。

以上の手順でE列以降を非表示にすることができます。

行に関しても、同様に10行目全体を選択し、CtrlキーとShiftキーを押しながら、カーソル(矢印)キーの「↓」を押すことで、10行目以降を全選択します。
選択後は、選択範囲内で右クリックし、メニューから[非表示]を選択することで、以下のように、必要な範囲以外を非表示にし、必要な範囲のみを表示することができます。

ExcelVBAレベル確認

非表示にした範囲を再表示する場合は、表示されている範囲の最後の列(行)を全選択し、そのまま外側にドラッグします。

選択後、選択範囲内で右クリックし、メニューから[再表示]を選択することで、再表示することができます。

2024/06/10
【Excel】絵グラフを瞬時に作成

【Excel】絵グラフを瞬時に作成

以下のような、絵グラフを瞬時に作成する方法について解説していきます。


・アドインの追加からグラフの作成

こちらでは、「People Graph」というアドインを活用して、絵グラフを作成していきます。
初めに、[ホーム]タブ内の[アドイン]を選択します。

次に、表示された検索ボックス内に「people」と入力し、検索結果の「People Graph」の[追加]を選択します。

以下のようなグラフが追加されましたら、必要に応じて、位置やサイズを調整します。

次に、グラフを選択した時に表示される右上の表のマークを選択し、グラフのタイトルを入力します。

タイトルの入力後は、[データの選択]を選択し、グラフにしたい範囲を選択して[作成]を選択します。

以上の手順で絵グラフを作成することができます。
必要に応じて、設定からデザインを変更することができます。


・注意点

アドインが追加されていない環境では、正しく絵グラフを表示することができず、以下のように表示されます。

[承諾して続行]を選択することで、アドインを追加して表示することはできますが、上記の理由から、Excelファイル自体を共有する用の資料ではなく、プレゼンなどの発表用の資料などに活用するのが良いです。

・アドインの削除

アドインを削除するには、[アドイン]を選択後に、[その他のアドイン]を選択します。

表示された[Office アドイン]の画面にて、[個人用アドイン]を選択し、表示されたアドインの中から対象のアドインを選択して削除することができます。

2024/06/07
【Excel】リンク付きの目次を簡単に作成

【Excel】リンク付きの目次を簡単に作成

複数のシートが存在するブック関して、シートの目次を簡単に作成する方法について解説していきます。

・ハイパーリンクの作成

通常、シートへのリンクを作成する場合は、以下の手順になります。

① リンクを作成したいセルの上で右クリックし、[リンク]を選択

② 設定画面にて、[このドキュメント内]から対象のシート名を選択

この手順を行うことで、以下のようにリンクを作成することができます。

ただ、このリンクの作成方法の場合、他のシートに対しても1つ1つ設定する必要があるため、シートの数が多いブックの場合は、若干大変になります。

この目次のリンクを作成する作業を、もう少し効率的に行う場合は、HYPERLINK関数を用いて、数式で作成する方法がオススメです。

ExcelVBAレベル確認

・HYPERLINK関数で作成

一度、HYPERLINK関数を用いてリンクを作成してしまえば、今後は数式を修正することなく、他のシートへのリンク作成時にも繰り返し活用することができます。

まずは、HYPERLINK関数の使い方から確認していきます。
HYPERLINK関数の使い方は、以下になります。

=HYPERLINK(リンク先, [別名])
// [リンク先]に指定したアドレスに、遷移するリンクを作成
// [別名]に、リンクに表示させる文字を指定(省略時は[リンク先]に指定したアドレスが表示される)

HYPERLINK関数を用いて、特定のシートの特定のセルに遷移するリンクを作成する場合は、セルのアドレスの先頭に「#」を加えたアドレスを引数[リンク先]に指定します。

例えば、「田中太郎」というシートのセルA1に遷移するリンクをHYPERLINK関数で作成する場合は、以下のようになります。

=HYPERLINK("#田中太郎!A1")

※シート名に「!」などの文字が含まれる場合は、以下のように「’」でシート名を囲む必要があります。

=HYPERLINK("#'田中太郎'!A1")

また、引数[別名]に好みの文字を指定することで、表示させる文字を変更することができます。

=HYPERLINK("#田中太郎!A1","開く")

後は、この数式の「田中太郎」という文字をB列のセルの値を参照するように修正します。
B列のセルの値を参照したアドレスを指定するには、以下のように「&」を活用します。

=HYPERLINK("#"&B5&"!A1","開く")

※今後、シート名に「!」などの文字が含まれる可能性がある場合は、以下のように、シート名を「’」で囲んだ数式にしておくと良いです。

=HYPERLINK("#'"&B5&"'!A1","開く")

このような数式を1度作成することによって、他のシートへのリンクは、数式をコピーするだけで作成することができます。

シート名が変更された場合に関しても、数式を直接修正することなく、表の中のシート名を修正するだけで、リンクに反映させることができます。

2024/06/05
【Excel】セルの値に対しアイコンを表示

【Excel】セルの値に対しアイコンを表示

以下の表の「達成率」の項目のように、セルの値に対し、アイコンを表示させる方法について解説していきます。


100%以上に星マーク、80%以上に半分の星マーク、それ以外に空の星マークなどといったように、セルの値に応じてアイコンを表示させる際は、「条件付き書式」を活用します。

まずは、アイコンを表示させたい対象のセルを選択し、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択します。

以下の条件付き書式の設定画面にて、[ルールの種類]を[セルの値に基づいて…]にし、[書式スタイル]を[アイコンセット]、[アイコンスタイル]を好みのアイコンにしてから、アイコンを表示させる条件を設定します。
以下では、星マークに対し「>=1、数値」、半分の星マークに対し「>=0.8、数値」を指定しています。

※ちなみに、アイコンの種類は最大で5種類まで指定でき、個別のアイコンの種類に関しても、個々で変更することができます。

以上の設定を行うことによって、以下のように、セルの数値に対し、アイコンを表示することができます。


・補足1

設定したアイコンの編集や削除を行う際は、[条件付き書式]の[ルールの管理]にて行います。

・補足2

条件付き書式にて設定したアイコンに関しては、そのアイコンを基準に、絞り込みや並べ替えを行うこともできます。

2024/06/03
【Excel】表の各行に空の行を挿入

【Excel】表の各行に空の行を挿入

以下のような、既に値が入力されている表に対し、空の行を各行に挿入する方法について解説していきます。

こちらでは、「並べ替え」機能を活用して、表の各行に空の行を挿入していきます。
まずは、現時点でデータがある数分、表の隣の列に連番を入力します。

連番を入力する際は、先頭の行から「1」、「2」と入力し、入力した範囲を選択してから、以下の画像の○の位置をダブルクリックすることで、簡単に連番を入力することができます。

連番の入力ができましたら、入力した範囲をコピーし、表の最終行に貼り付けます。

この作業を、空の行を挿入したい数分繰り返します。

こちらでは、表の最後の行以降に、連番を2回貼り付けています。

貼り付けることができましたら、連番を入力したいずれかのセルを選択し、[データ]タブの中の[昇順]を選択することで、表の下の空の行を、表の各行の間に移動することができます。

最後に、連続した数値を入力した列全体を選択し、[Delete]キーを押して値を削除することで、表の各行に空の行を挿入することと同じことが実現ができます。

2024/05/31
【ExcelVBA】数式「AND(3,4)」とVBA「3 And 4」は違う!?

【ExcelVBA】数式「AND(3,4)」とVBA「3 And 4」は違う!?

IF関数などに設定する倫理式(TRUEもしくはFALSEを返す式)に関して、Excelの数式やVBAでは、「0」がFALSE、「0」以外の数値がTRUEという意味として扱われます。

しかし、AND条件にすると、VBAでは、以下の黄色のセルのように、理想通りの結果にならないことがあります。

「3」と「4」では、ともに「0」以外の数値であるため、AND条件ではTRUEになるかと思うのですが、VBAではFALSEになってしまいます。
今回は、「なぜVBAのAND条件では理想通りの結果を返さなかったのか」について解説していきます。


結果に違いが発生する理由は、Excelの数式での判定方法とVBAでの判定方法が異なるためです。
そのため、判定方法の違いについて確認していきます。

Excelの数式での判定方法は、条件1つ1つに対し、TRUEかFALSEかを判定して、AND条件の場合は、複数の条件がTRUEなのかを判定しています。
そのため、「3」と「4」の比較では、「3」がTRUEで「4」もTRUEのため、「AND(3,4)」ではTRUEという結果になります。

それに対しVBAでは、1つ1つの条件に対し、ビット単位で演算しています。
具体的には、二進数のビット単位(各桁同士)での演算になります。

「3」という数値を二進数で表現すると「0011」になり、「4」という数値を二進数で表現すると「0100」になります。

AND演算とは、「0」と「1」では「0」になり、「1」と「0」でも「0」となり、「1」と「1」の場合に関してのみ「1」となるような演算になります。

そのため、「0011」と「0100」をビット単位でAND演算すると、右から1桁目は「1」と「0」で「0」、2桁目も「1」と「0」で「0」、という感じで「0000」という結果になります。
「0000」という二進数は「0」という数値を意味するため、FALSEという結果になったということになります。

もし、数値同士をVBAで比較したい場合は、以下のように「0でない場合」という条件を加えると良いです。

2024/05/29
【Excel】空白のセルが上になるように並べ替え

【Excel】空白のセルが上になるように並べ替え

以下の表の[進捗]の項目には、「済」という文字が入力されているセルと空白のセルがあります。

この項目を基準に、昇順や降順に並べ替えしても、どちらも「済」が上になってしまい、空白のセルを上にすることができません。

今回は、このような表に対し、空白のセルが上になるように並べ替えする方法について解説していきます。

この表のままですと、何回昇順や降順に並べ替えしたとしても、空白のセルを上にすることはできません。
そんな時は、色で並べ替えします。

とは言え、「進捗」の項目には色が設定されていません。
そのため、今回は、空白のセルに色を設定することで、空白のセルを上に並べ替えできるようにしていきます。

毎回、空白のセルに色を設定するのは大変ですので、条件付き書式を活用すると良いです。
以下の表の場合は、「進捗」の範囲であるセルC3からC12に対し、条件付き書式を設定していきます。

まずは、対象の範囲(セルC3からC12)を選択し、[ホーム]タブの中の[条件付き書式]より[新しいルール]を選択します。

次に、[ルールの種類]を[指定の値を含むセルだけを書式設定]を選択し、下に表示されるリストより[空白]を選択します。

上記のように設定することができましたら、[書式]を選択し、好みの色で塗りつぶしをします。
こちらでは、[塗りつぶし]タブより白色を選択しています。

これで、表の中の[進捗]の項目に対し、セルの値が空白の場合に、白色の背景色を設定することができました。


空白のセルに色を設定することができたため、空白のセルが上になるように色で並べ替えしていきます。
そのため、フィルターボタンより[色で並べ替え]の白色を選択することで、以下のように空白のセルを上にすることができます。

・補足

先ほどは、フィルター機能の中の並べ替えを活用していますが、[データ]タブの中の[並べ替え]機能でも、フィルター機能の中の並べ替えと同様に、色で並べ替えることができます。

2024/05/27
【Excel】数式の引数に途中の行から最終行までを指定

【Excel】数式の引数に途中の行から最終行までを指定

以下の参加者名簿から参加人数を数式で求める方法の1つとして、COUNTA関数を用いて、セルB5以降の値の入力されているセルを数えるという方法があります。

しかし、Excelには、現時点でセルB5から最終行までという指定方法がありません。
そのため、通常であれば、「B5:B100」などと仮の最終行で、以下のように指定することが多いかと思います。
※Googleスプレッドシートでは、「B5:B」と指定することで、最終行まで指定することができます。

今回は、比較的新しい関数であるDROP関数を組み合わせて、セルB5から最終行までを指定する方法について解説していきます。

ExcelVBAレベル確認

まず初めに、DROP関数の使い方から解説していきます。
DROP関数の使い方は、以下の通りです。

=DROP(範囲, 行数, [列数])
// 指定した[範囲]の先頭から、指定した[行数]や[列数]を取り除いた範囲を返す
// 列を取り除く必要がない場合は、[列数]を省略できる

以下の表の場合、指定したい範囲はセルB5以降になります。
そのため、DROP関数を用いて、次のように指定することができます。

=DROP(B:B,4)
// B列の上から4行を取り除く

この範囲をCOUNTA関数で集計することで、以下のように、セルB5以降を指定することができます。

=COUNTA(DROP(B:B,4))

データの増減を考慮した数式を作成する際に、DROP関数を覚えておくと便利かと思います。
また、スピルにより抽出した範囲の一部を除外する、以下のような場合にも役立ちます。

=COUNTA(DROP(B3#,2))
// セルB3の数式により展開された範囲の先頭から2行を取り除く