メンバー

作成したチェックボックスを一括でリセットする方法について解説していきます。

まずはチェックボックスの作成方法から解説していきます。

・チェックボックスの作成方法

チェックボックスを作成するには[開発]タブを表示する必要があります。
[開発]タブの表示方法についてはこちらをご確認ください。

[開発]タブを表示できましたら、[開発]タブ内の[挿入]から作成することができます。
[挿入]内のフォームコントロールの[チェックボックス]を選択することで作成することができます。

作成する際は、セル内に収めることを意識して作成すると、その後に複製しやすくなります。
まずは、適当なサイズで作成し、テキストが不要な場合は、テキストを削除した上で、セル内にサイズを調整します。

作成した後に選択すると、チェックのON・OFFが切り替わってしまいます。
修正したい場合は、Ctrlキーを押しながら選択することで、サイズ調整などを行うことができます。

１つ作成できましたら、そのセルをコピーすることで簡単に複製することができます。

作成することができましたら、チェックの有無をセルに表示させます。
チェックボックスの上で右クリックし、[コントロールの書式設定]を開き、リンクするセルを設定します。
それぞれ、チェックボックスを含むセルのアドレスを設定します。

それぞれに設定をすると、チェックボックスのON・OFFを切り替えた場合に、対象のセルにTRUEもしくはFALSEの値が表示されるようになります。
※以下の画像の場合は、セルの幅が狭いため”###”と表示されていますが、数式バーにて実際の値を確認することができます。

セルの値がTRUEの場合にチェックされ、FALSEもしくは空白の場合にチェックが外されます。
見た目では、チェックの有無に関しては瞬時に判断できるため、TRUE・FALSEの値は不要かと思います。
そのため、TRUE・FALSEの値を非表示にしていきます。

実際の値を残した状態で、見た目のみを非表示にするには表示形式が活用できます。

設定内容は、表示形式の[その他の表示形式]より、[ユーザー定義]にて「;;;」とセミコロンを３つ並べて確定します。

この設定によって、以下のように非表示にすることができます。

では次に、チェックボックスのチェックを一括でリセットするボタンを開発していきます。

・チェックを一括でリセットするボタンの開発方法

[開発]タブの中の[マクロ]を選択し、「チェック取消」という名前を入力して[作成]を選択します。

表示された「Sub チェック取消」の中身に、以下のコードを記述します。
※こちらのファイルは配布しています。

Sub チェック取消()
    
    Range("B2:B4").ClearContents
    
End Sub

連続しているセルを指定する場合は、「”B2:B4″」もしくは「”B2″,”B4″」のようにRangeの中に記述します。
離れているセルを指定する場合は、「”B2,B5,C3″」のように「”」内にカンマ区切りでアドレスを指定します。

このコードの意味は、指定したセルの値を削除する（ClearContents）というものになります。

入力できましたら、[開発]タブの中の[挿入]より、フォームコントロールの[ボタン]を選択し、適当な位置に作成します。
作成時に、「マクロの登録」という画面が表示されるため、先ほど作成した「チェック取消」を選択します。

必要に応じて、ボタンのテキストを変更することで完成です。

こちらのボタンを選択することで、チェックを一括で取り消すことができます。

▼サンプルファイル▼

以下のような出社時刻と退社時刻、休憩時間を管理した表があります。
この表のD列の勤務時間を求める方法について解説していきます。

時刻はシリアル値という数値で管理されています。

＞シリアル値とは

そのため、時刻の差は引き算で求めることができます。
単純に勤務時間を求める式は以下のようになるかと思います。

勤務時間 = 退社時刻 - 出社時刻 - 休憩時間

こちらの計算式でD列を埋めてみます。

=B3-A3-C3

そのようにすることで、勤務時間を求めることができるのですが、退社時刻が深夜の場合は求めることができません。
深夜の時刻を翌日の値として入力している場合は、求めることができますが、直接「1:30」などと入力している場合は求めることができません。

求めることができない理由は、退社時刻より出社時刻の方が大きな数値となってしまい、「退社時刻 – 出社時刻 – 休憩時間」の計算結果がマイナスになってしまうためです。

以上の理由から、退社時刻よりも出社時刻の方が大きい場合は、特殊な計算をする必要があります。
特殊な計算とはいっても複雑なものではありません。

以下の条件を加えるだけで、深夜退社でも正しく計算することができるようになります。

『出社時刻が退社時刻よりも大きい場合は、計算結果に１を加え、それ以外は０を加える』

シリアル値の概念を理解している場合は、上記の条件式のイメージがしやすいかと思います。
１日はシリアル値で１と表現され、時間は１をさらに細かくした値です。

出社時刻が退社時刻よりも大きい場合は、本来、退社時刻の方が大きくなければいけないため、深夜時刻が入力されていることが考えられます。
深夜時刻とは、正確には翌日の朝になります。

そのため、１を加えて、翌日の時間として計算させるという理屈になります。

３行目の数式に条件式を加えると以下のようになります。

// =IF(論理式, [値が真の場合], [値が偽の場合])
=B3-A3-C3+IF(A3>B3,1,0)

反映させると以下のように求めることができます。

時刻や日付の計算をする上では、シリアル値の仕組みから考えると、徐々に解決策が思い付くようになるかと思います。

以下のような各支店ごとの各月の売り上げをまとめた表があります。
１月から３月まではグラフに表示されていますが、後から４月のデータを追加した場合、グラフにも追加する必要があります。

データを追加する方法として、グラフを選択した後に表示される範囲から枠をドラッグして参照範囲を拡張するという方法があります。

では、次の場合はどうでしょうか。
３月の後に、一度集計列を追加し、その後に４月と５月のデータを追加しています。

このようになると、単純にドラッグでは拡張することができません。

ただ毎回、グラフを選択し、[グラフのデザイン]タブより[データの選択]をから修正するのは大変かと思います。
※離れている範囲は「,(カンマ)」区切りで指定します。

実は、簡単に離れている範囲でもグラフに追加する方法があります。

それは、「Ctrl+C」でコピーして、「Ctrl+V」で貼り付けるという方法です。

まず、追加したい範囲を選択し、「Ctrl+C」でコピーします。

後は、グラフを選択して、「Ctrl+V」で貼り付けるだけで完了です。

このような方法も覚えておくと、役立つことがあるかと思います。

近代では海外との仕事も増え、英語での文章のやり取りをする方も多くなっているかと思います。
そこで、英語を入力する際に、入力した単語のスペルが正しいのかどうか不安になることもあるかと思います。

実は、Excelには入力した単語のスペルを自動でチェックする機能があります。

それは、[校閲]タブ内にある[スペルチェック]という機能になります。

こちらの機能は、[F7]キーでも実行することができます。
※ノートパソコンなど、小さめのキーボードの場合は、[Fn]キーを同時に押す必要があるものもあります。

まずは、スペルを確認したいセル上で、[スペルチェック]を実行します。

スペルにミスがないと判断された場合は、以下のウィンドウが表示されます。
必要に応じて、「はい」を選択し、先頭から順番にチェックすることができます。

実行すると、以下のように誤っている単語がある場合に、修正候補が表示されます。
修正が不要な場合は[無視]を選択し、修正が必要な場合は、修正候補から単語を選択し、[修正]を選択することで置換されます。

名詞などで辞書に登録されていない単語に関して、今後、警告を表示したくない場合は、辞書に追加することができます。
以下の画面にて、[辞書に追加]を選択することで、辞書に追加され警告が表示されなくなります。

辞書に登録した内容を修正したい場合は、上記の画面の左下の[オプション]、もしくは、[ファイル]タブから[オプション]を選択するなどとして、[Excelのオプション]を開きます。
そして、Excelのオプション内の[文章校正]内の[ユーザー辞書]を選択します。

以下の[ユーザー辞書]のウィンドウにて、対象の辞書を選択し、[単語の一覧を編集]より追加や削除することができます。

「セルの結合はダメ」とよく言われますが、本当にダメな機能なのでしょうか？

本当に使うべきでない機能の場合は、Excelに機能として残り続けないかと思います。
「セルの結合」は使い方を気を付ければ、かなり便利な機能です。

「セルの結合」とは、以下の表のように複数のセルを１つのセルとして扱う機能になります。

ということで、こちらでは、「セルの結合」のデメリットとメリットについて解説していきます。
また、最後に、「セルの結合」を上手く活用したメンテナンスのしやすい書類の作成方法についても解説しています。

・セルの結合のデメリット３つ

①正しく絞り込みができない

フィルターを設定して、結合されたセルを含む列に対して絞り込みをすると、以下のように正しく絞り込むことができません。

カテゴリーが「A」の商品が３つ表示されるのではなく、先頭のデータのみしか表示されていません。
この原因は、結合されたセルに入力されている値は、結合範囲の左上のセルに入力されていることになり、他のセルは空白になるためです。

実際に、値の入力されている複数のセルを結合すると、以下のような警告が表示されます。

他のセルが空白になってしまうため、フィルターで正しく絞り込むことができなかったということになります。

※結合されている範囲全体に同じ値を入力するような小技もありますが、見た目上では、値が入力できているのか判断が難しいため、推奨はできません。
一応小技を載せておきます。

②並べ替えができない

セルの結合が使われている表は、並べ替えができません。
実際に行うと、以下のような警告が表示され、作業が中断されます。

③正しく集計できない

これは「①正しく絞り込みができない」と同じ理由になりますが、結合されている範囲をCOUNTIF関数などで集計しようとしても、先頭セル以外が集計されません。

①の小技を使うことで、正しく集計することも可能ですが、分かりづらいため、推奨はできません。

・セルの結合のメリット

いくつかデメリットをお伝えしましたが、共通して言えることは、「集計する表に対してのセルの結合がよろしくない」ということです。
逆に言えば、集計しない表の場合（印刷する用の資料など）は、結合しても問題ございません。

結合している印刷用のシートの代表が「神エクセル」と呼ばれるセルの幅を均一な正方形にし、結合を多様して作れらたシートになります。

このような資料は、印刷目的であれば全く問題ありません。

しかし、「セルの結合」を多用しすぎるシートは、改修頻度の多い資料には向いていません。
改修時に、結合を解除して再び結合するなどとレイアウトが崩れやすくなるためです。

そのため、「セルの結合」を多用しそうな場合は、以下のように作成することをお勧めします。

以下の表のように、マークされた行を瞬時に削除するマクロの開発方法について解説していきます。
※完成ファイルは配布しています。

このような仕組みをVBAで開発するには、どのような処理で実現するのかを考える必要があります。
今回の場合は、以下のような処理で実現できるかと思います。
※このような処理の流れを表現した図をフローチャートと呼びます。

処理を上記のように分解することができましたら、１つ１つVBAで実現していきます。

まずは、[開発]タブより[マクロ]を選択し、好みの名前（こちらでは”対象行の削除”）を入力し、[作成]を選択します。

表示された「Sub 対象行の削除()」内に以下のようなコードを記述します。
※「’」から始まっている緑の文字はコメントアウトと呼び、処理には関係のないメモ書きになります。

Sub 対象行の削除()
    
    Dim lastRow As Long
    
    'B列の最終行(≒150万行目)から「Ctrl+↑」で移動して止まった位置の行番号
    '⇒B列に●が入力されている一番下の行番号
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    Dim i As Long
    
    '行を削除すると、行番号がずれてしまうため、下から上へと対象行を削除する
    For i = lastRow To 3 Step -1
        
        'B列のi行目のセルに値が入力されている場合
        '※「….Value = "●"」でも可
        If Cells(i, "B").Value <> "" Then

            'i行目を削除する
            Rows(i).Delete
            
        End If
        
    Next i
    
End Sub

上記のコードの解説をします。

Dim lastRow As Long

Dim と書かれているものは変数宣言と呼び、プログラム内で一時的に使用する値を格納する部屋（変数）を用意しています。
As の後に部屋（変数）の種類を定義しています。
Long は整数を格納する専用の部屋（変数）を指します。

lastRow = Cells(Rows.Count, "B").End(xlUp).Row

Rows.Count で対象のシートの最大の行番号（1048576）を取得できます。
Cells(Rows.Count, “B”) でセルB1048576を指定し、 .End(xlUp) でCtrlキーと↑キーを押した先に移動しています。
Ctrlキーと↑キーを同時に押すと、対象列の次に値が入力されているセルまで移動することができます。
その移動した先のセルの行番号を .Row で取得しています。

For i = lastRow To 3 Step -1 ～ Next i

For ～ Next 間を、先ほど取得した行番号から3まで-1刻みで繰り返します。
変数（i）が先ほど取得した行番号から3まで-1刻みで変化して、For ～ Next の中の処理が繰り返し実行されます。

If Cells(i, "B").Value <> "" Then ～ End If

If ～ End If の中の処理に関して、 Cells(i, “B”).Value <> “” の条件を満たした場合のみ実行します。
Cells(i, “B”).Value は対象のi行目のB列のセルを指しており、 .Value でそのセルの値を取得しています。
<> は等しくないという意味を表しており、 <> “” で空白でないという意味になります。

Rows(i).Delete

Rows(i) でi行目の全体を指定し、 .Delete で削除しています。

上記の内容を記述し、エディタ内の実行ボタン、もしくは、[マクロ]の実行ボタンにて実行すると、B列に値が入力された行全体が削除されます。

※マクロを実行すると、実行前の状態に戻すことができなくなるため、動作確認をする前に、保存をしておくと良いです。

▼サンプルファイル▼

「特定のセルの値のみを瞬時に削除したい」
そんな時に活用できるのが、「名前」という機能です。

セルには、好みの「名前」を付けることができます。

例えば、以下のようなシートのセル「C2、C3、C4、C6」に名前を付けていきます。

対象のセルをCtrlキーを押しながら複数選択し、名前ボックスにて、好みの名前を入力してEnterキーで確定します。
※名前には、「A1」などの実在するアドレスや数字から始まるものなどは設定することができせん。

名前を登録することで、以下のように、名前ボックスのリストから簡単に選択できるようになります。

そのため、値が入力されている場合は、名前を選択してDeleteキーを押すだけで値を削除することができます。

複数のセルに名前を設定することができるため、色々と応用できるかと思います。
登録した名前は、[数式]タブの中の[名前の管理]にて管理されているので、自由に編集や削除することができます。

[補足] セルが結合されている場合で、離れているセルを含む場合は、結合範囲の先頭のセルのみしか登録されないことがあります。
その場合、名前を選択すると以下のようにセルが選択されます。

この状態でDeleteキーを押すと、以下のようなメッセージが表示されて削除できません。

ただ、この警告が表示された後に、以下のように選択が修正されます。

そのため、警告が表示されましたら、Enterキーで警告を閉じ、そのままDeleteキーを押すことで値を削除することができます。

※毎回使用するファイルの場合は、テンプレート用のExcelファイルを用意したほうが良いかと思います。
ただ、ファイルを増やしたくない場合や、バックアップをする必要がない場合は、こちらの方法が便利かと思います。

締め日を考慮した以下のようなカレンダーを作成していきます。
※こちらのExcelファイルは配布しています。

こちらのカレンダーは、セルB2とB3に年月を入力し、セルD3に締め日を入力することで、自動でセルA6以降にカレンダーが表示される仕組みになっています。
まずは、締め日を15日とした場合の数式を考えていきます。

締め日を15日とした場合、対象期間はひと月前の16日から対象月の15日までになります。
まず、開始日は「ひと月前の16日」で固定になるため、そのような値を数式で求めていきます。

こちらで使用する関数はDATE関数になります。

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

DATE関数を用いて、ひと月前の16日を求めた数式は以下になります。

=DATE(B2,B3-1,16)

また、DATE関数は、月日の桁上がり桁下がりにも対応しています。
そのため、以下のような月にも対応することができます。

続いて、２行目以降の日付を求めていきます。
17日以降に関しては、基本は前のセルの日付に1日ずつ加算していけばよいのですが、条件として対象月の16日以降は対象外になってしまいます。
そのため、IF関数を用いて1日加算した日付が対象月の16日よりも前の場合は表示、それ以外は非表示という式にする必要があります。

=IF(論理式, [値が真の場合], [値が偽の場合])
// 論理式の条件を満たしている場合は、[値が真の場合]に設定した内容を表示、満たしていない場合は、[値が偽の場合]に設定した内容を表示する

IF関数を用いた数式は以下になります。

=IF(A6+1<DATE($B$2,$B$3,16),A6+1,"")
// 下の行へコピーしても問題ないように、位置を固定する必要のあるセル（B2,B3）に関しては絶対参照にしています

後は、こちらの数式を最大31日分を表示できるようにコピーします。

ただ、この数式をコピーすると、期間が31日ない月（3月など）の場合、エラーが表示されることがあります。

エラーの原因は、空白(“”)に1を加えているためです。

そのため、IFERROR関数を用いた数式にします。

=IFERROR(値, エラーの場合の値)
// 指定した値(数式)がエラーの場青は、[エラーの場合の値]に設定した内容を表示する

IFERROR関数を用いた数式は以下になります。

=IFERROR(IF(A6+1<DATE($B$2,$B$3,16),A6+1,""),"")

このようにエラーを無くすことができました。

では次に、締め日を自由に変更できるように修正していきます。

こちらに関しては、締め日の値を別のセルを参照させることで解決します。
そのため、数式を以下のように修正することで、締め日を自由に設定することができるようになります。

=DATE(B2,B3-1,D3+1)
=IFERROR(IF(A6+1<DATE($B$2,$B$3,$D$3+1),A6+1,""),"")
// 2行目以降のコピーする数式では、位置を固定する必要のあるセル（B2,B3,D3）に関しては絶対参照にしています

[補足] セルA1の「対象月（10月締め）」に関して、セルD3の内容を元に文言が切り替わるように以下の数式を入力しています。

="対象月（"&D3&"日締め）"

▼サンプルファイル▼

スケジュール表などの日付を入力する項目に対し、特定の曜日の日付を入力できないようにする方法について解説していきます。
目的は、誤って「営業日でない日を登録する」などのことを未然に防ぐことになります。

こちらでは、以下の２点について順番に解説していきます。

・土日の入力を禁止する
・指定した曜日の入力を禁止する

以下の表を用いて順番に解説していきます。

・土日の入力を禁止する

特定のセルへの入力を制限するには、「データの入力規則」を活用します。
ただ、「土日の入力を禁止する」というような設定は、予め用意されていないため、ユーザー設定にて自作する必要があります。

ユーザー設定では、TRUEもしくはFALSEを表す値を返す数式を入力し、対象のセルに値が入力されたときに、その数式の結果がTRUEでない場合に制限するという設定になります。

特定のセルの値が土日以外の場合に、TRUEを返す数式を作成するには、NETWORKDAYS関数が便利です。

=NETWORKDAYS(開始日, 終了日, [祭日])
// 指定した期間の営業日（土日休み）の日数を返す
// [祭日]は今回は使用しないため、省略します

こちらの関数を用いて、開始日と終了日を同じ日付にして営業日の日数を求めます。
そのようにすることで、対象の日付が土日のいずれかの場合は、日数が「0」になり、土日以外の場合は、日数が「1」になります。

以下のようなイメージです。

=NETWORKDAYS(B3,B3)

また、TUREとFALSEは数値でも表現することができます。
数値で表現する場合は、「0」がFALSE、「0以外」がTRUEになります。

そのため、この数式をそのまま「データの入力規則」にて設定することで、土日の入力を制限することができます。

設定したい対象の範囲を選択し、[データ]タブから[データの入力規則]を選択します。

次に、以下の内容を設定します。

・入力値の設定：ユーザー設定
・数式：以下のを参照

=NETWORKDAYS(B3,B3)

数式で指定したセルB3は、選択の基準となっているセルになります。
他のセルには、B3から相対参照で数式が設定されます。

上記の設定をすることで以下のように、土日の入力が制限されます。

・指定した曜日の入力を禁止する

次に、土日に限らず、指定した曜日の入力を制限する方法について解説していきます。
指定した曜日に関しても、先ほどと同様に、営業日の日数を求めることで入力を制限することができます。

土日以外の曜日で設定する場合は、NETWORKDAYS.INTL関数が便利です。

=NETWORKDAYS.INTL(開始日, 終了日, [週末], [祭日])
// 指定した期間の営業日（土日休み）の日数を返す
// [週末]にて定休日を好みの曜日に設定することができる ※省略すると土日で設定される
// [祭日]は今回は使用しないため、省略します

NETWORKDAYS関数と比べると、[週末]という引数が追加されています。
[週末]は以下の数値で設定することができます。

1 または省略：土と日
2：日と月
3：月と火
4：火と水
5：水と木
6：木と金
7：金と土
11：日のみ
12：月のみ
13：火のみ
14：水のみ
15：木のみ
16：金のみ
17：土のみ

好みの曜日設定がない場合は、[0」と「1」を並べることでも表現することができます。

"0000011"：土と日
"1010000"：月と水
など

こちらでは、例として、水曜日と土曜日の入力を制限していきます。
水曜日と土曜日を定休日とした営業の日数を求める数式は以下になります。

=NETWORKDAYS.INTL(B3,B3,"0010010")

では、こちらの内容を「データの入力規則」にて設定していきます。

=NETWORKDAYS.INTL(B3,B3,"0010010")

上記の設定をすることで以下のように制限されます。

[補足] 引数の[祭日]では、定休日以外の休日を設定することができます。
使用例は以下になります。

=NETWORKDAYS.INTL(B3,B3,"0010010",$E$3:$E$9)
// 休日一覧など位置が変わらないセルは絶対参照にする

以下のような各商品の購入数を管理している表と、各商品の最終購入日を抽出している表があります。
この最終購入日を抽出する方法について解説していきます。

抽出する方法について２通りで解説していきます。
１つ目は「XLOOKUP関数で抽出する方法」、２つ目は「VLOOKUP関数で抽出する方法」になります。

一番簡単な方法は１つ目の方法ですが、XLOOKUP関数は比較的新しい環境（2021以降）でないと扱うことができません。
そのため、対応していない場合は、２つ目の方法で実現する必要があります。

１.XLOOKUP関数で抽出する方法

VLOOKUP関数やMATCH関数は、検索値が上から先に見つかった位置の情報を返します。
※VLOOKUP関数の使い方は、後半で解説しています。

そのため、下に行くほど最新データになる表で、最新の情報のみを抽出することが難しいです。
上記の例の場合、最新情報が必要だとすると、理想的な値は以下のようになります。

XLOOKUP関数では、このような値を簡単に求めることができます。
XLOOKUP関数の使い方は以下になります。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
// 検索値：検索したい値
// 検索範囲：検索したいセルの範囲や配列
// 戻り範囲：抽出したいセルの範囲や配列
// [見つからない場合]：検索値が検索範囲の中から見つからない場合に表示する値
// → 省略時は見つからない場合にエラーを表示
// [一致モード]：以下の中から検索方法を選択
// 0：完全一致（省略時）、-1：完全一致または次に小さい項目、1：完全一致または次に大きい項目、2：ワイルドカード文字との一致
// [検索モード]：以下の中から検索方法を選択
// 1：先頭から末尾へ検索（省略時）、-1：末尾から先頭へ検索、2：バイナリ検索（昇順の検索範囲）、-2：バイナリ検索（降順の検索範囲）

XLOOKUPの引数には[検索モード]があり、ここで「末尾から先頭への検索」を指定することで、最新情報を抽出することができます。
また、VLOOKUP関数とは異なり、「検索したい項目が表の一番左にある必要がある」などの縛りがありません。

XLOOKUP関数を用いて求めた数式は以下になります。

=XLOOKUP(F3,C:C,B:B,"",0,-1)
// データの増減に対応するため、「C:C」などと列全体を指定しています

２.VLOOKUP関数で抽出する方法

次にXLOOKUP関数が使えない環境でも実現できるように、VLOOKUP関数で実現する方法について解説していきます。
VLOOKUP関数の使い方は以下になります。

=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
// 検索値：検索したい値
// 範囲：検索したい項目が一番左の列である抽出対象の列を含む範囲を指定
// 列番号：範囲から抽出したい列が左から何番目なのかを指定
// [検索方法]：以下の中から検索方法を選択
// TRUE：近似一致（省略時）、FALSE：完全一致

VLOOKUP関数は、検索したい項目が抽出対象の列より左にある必要があるため、以下の表では抽出することができません。

仮に、[日付]と[商品名]の項目が逆だったとしても、VLOOKUP関数は「検索値が上から先に見つかった位置の情報」を返すので正しく抽出することができません。

=VLOOKUP(F3,B:C,2,FALSE)

そのため、VLOOKUP関数で表の中の最終購入日を抽出するには、検索用の項目を用意する必要があります。
またその項目は、重複のない項目である必要があります。

ということで、処理用の列を以下のように挿入し追加します。

では、まずはB列の検索用の値を求めていきます。
重複しないように処理列の項目に関しては、「商品名+”-“+登場回数」にしていきます。
セルB3の場合は「A-1」、セルB8の場合は「A-2」というイメージになります。

登場回数は、３行目から対象の行までに出てきた指定の商品名の数になります。
そのような値は、COUNTIF関数で求めることができます。

=COUNTIF(範囲, 検索条件)
// 指定した範囲内に検索条件を満たすセルの数を返す

セルB3を求める場合の対象の範囲は「D3からD3」になります。
セルB4を求める場合の対象の範囲は「D3からD4」になります。
という感じに、開始のセルD3は固定で、終わりのD3やD4は対象の行までになります。

そのため、相対参照と絶対参照を上手く活用し、コピーするだけで求めることができる数式を求めます。
その数式は以下のようになります。

=COUNTIF($D$3:D3,D3)

後は、こちらの数式に商品名を加えます。

=D3&"-"&COUNTIF($D$3:D3,D3)

後は、以下の抽出対象の処理列を求めます。

抽出対象は、「対象の商品名」と「表の中で各商品名が存在する数」を組み合わせることで最新の検索用の値を求めることができるかと思います。
各商品が存在する数は以下のように求めることができます。

=COUNTIF(D:D,H3)

後は、こちらの数式に商品名を加えます。

=H3&"-"&COUNTIF(D:D,H3)

このようにしてVLOOKUP用の検索値を求めることができました。
最後に、VLOOKUP関数で最終購入日を抽出します。

=VLOOKUP(G3,B:C,2,FALSE)

このようにして求めることができました。

前半のXLOOKUP関数は便利な関数ですが、2021以降のExcelでないと対応しておりません。
そのため、他の方に共有する資料などでは、対応していない可能性を考慮して、後半のVLOOKUP関数を用いて求めるのが好ましいかと思います。

以下のような売上表があります。

この表の中のトップ３の売り上げをセルD3からD5に求める場合は、LARGE関数が活用できます。
ただ、LARGE関数で抽出した数値は、元の表をフィルターで絞り込んでも更新されません。

ということで今回は、「LARGE関数でトップ３を抽出する方法」と、フィルターで絞り込んだ場合に、「非表示のセルを除外してトップ３を抽出する方法」について解説していきます。

・LARGE関数でトップ３を抽出する方法

まず、LARGE関数の使い方から確認します。

=LARGE(配列, 順位)
// 指定した配列の中から指定した順位番目の大きな値を返す

ということで活用した例は以下になります。

=LARGE($D$8:$D$17,C3)
// セルD3に上記の数式を入力し、D5までコピーするだけで反映できるように、D8:D17を絶対参照（$D$8:$D$17）にする
// 絶対参照にしたセルのアドレスは、コピー時に相対的に移動しないようになる

LARGE関数の引数に渡した値は、フィルターで非表示にしたとしても除外されません。
そのため、以下のようにフィルターで絞り込んでも表示される結果は同じになります。

この次に、フィルターなどで非表示になったデータを除外してトップ３を求める方法について解説していきます。

[補足] LARGE関数とセットにSMALL関数も覚えておくと役立つかと思います。

=SMALL(配列, 順位)
// 指定した配列の中から指定した順位番目の小さな値を返す

活用例は以下になります。

=SMALL($D$8:$D$17,C3)

SMALL関数に関しても、非表示のセルは除外されません。

こちらに関しても次の方法で、非表示を除外することができます。

・非表示のセルを除外してトップ３を抽出する方法

非表示のセルを除外してトップ３を抽出することができる関数は、AGGREGATE関数になります。
この関数は、LARGE関数を含む１９種類の関数を集約した関数になります。
また、単純に１９種類の関数が使えるだけではなく、非表示の無視やエラーの無視なども引数で設定ができる１９種類の関数を強化した関数になります。

=AGGREGATE(集計方法, オプション, 配列, [順位])
=AGGREGATE(集計方法, オプション, 参照1, 参照2, [参照3], …)

// 集計方法：以下の1~19の値で指定
・1 - AVERAGE(平均値)
・2 - COUNT(数値の個数)
・3 - COUNTA(データの個数)
・4 - MAX(最大値)
・5 - MIN(最小値)
・6 - PRODUCT(積)
・7 - STDEV.S(不偏標準偏差)
・8 - STDEV.P(標本標準偏差)
・9 - SUM(合計値)
・10 - VAR.S(不偏分散)
・11 - VAR.P(標本分散)
・12 - MEDIAN(中央値)
・13 - MODE.SNGL(最頻値)
・14 - LARGE(降順の順位) ← 今回はこちらを活用
・15 - SMALL(昇順の順位)
・16 - PERCENTILE.INC(百分位数)
・17 - QUARTILE.INC(四分位数)
・18 - PERCENTILE.EXC(百分位数[0%と100%を除く])
・19 - QUARTILE.EXC(四分位数[0%と100%を除く])

// オプション：以下の0~7の値で指定
・0または省略 - ネストされたSUBTOTAL関数とAGGREGATE関数を無視
・1 - 0の指定に加えて非表示の行を無視
・2 - 0の指定に加えてエラー値を無視
・3 - 0の指定に加えて非表示の行とエラー値を無視
・4 - 何も無視しない
・5 - 非表示の行を無視 ← 今回はこちらを活用
・6 - エラー値を無視
・7 - 非表示の行とエラー値を無視

// 残りの引数は、集計方法で指定した関数と同じ

今回は、こちらの集計方法「14：LARGE」とオプション「5：非表示の行を無視」を使用します。 集計方法とオプションを指定したら、残りの引数はLARGE関数と同じになります。

=LARGE(配列, 順位)
=AGGREGATE(14 , 5, 配列, 順位)

そのため、以下のように活用することができます。

=AGGREGATE(14,5,$D$8:$D$17,C3)

こちらの場合、オプション「5：非表示の行を無視」を指定しています。
そのため、以下のようにフィルターで絞り込むことで、表示されているセルの中で集計することができます。

[補足] SMALL関数に関しても同様に使用することができます。

集計用の関数の強化版として、AGGREGATE関数の存在を覚えておくと、いざという時に活用できるかと思います。
ちなみに、AGGREGATE関数に似た関数として、SUBTOTAL関数があります。

AGGREGATE関数は、SUBTOTAL関数を更に進化させた関数になります。
進化した点は、主に以下になります。

・対応関数を８種類追加
・オプションの引数を追加(エラーを無視できるなど)

＞SUBTOTAL関数について