パパセンセイ365

Power Platformの技術系のお話を繰り広げます

スライサーで選択中の項目を取得する方法 単一選択編

Excel2013以降ではテーブルでもスライサーが利用できるようになりました。 フィルター機能を便利にするスライサーですが、スライサーで選択中の項目を取得したいケースがありますので共有します。 フィルター中の項目を別のセルに展開することで、その値を他のセルで利用できるようになり様々な機能に応用できるようになります。

↓こんなテーブルとスライサーを作って f:id:tomikiya:20200326223215p:plain

スライサーで単一選択すると選択中の項目を取得できます。 f:id:tomikiya:20200326223223p:plain

やり方

SUBTOTALとVLOOKUPを使用します。 f:id:tomikiya:20200326223226p:plain

表の各行にSUBTOTAL(3,セル)を仕込み、VLOOKUPでA列をキーに「1」を検索します。 これでスライサーの選択項目を抽出できます。

解説

SUBTOTALは集計用の関数で、第1引数で集計方法を選択できます。 今回は3を入れていますが、これはCOUNTAと同じようにデータの個数を集計するという意味です。 また、第2引数で集計する範囲を指定します。1つのセルのみ指定していますので集計結果は0か1になります。空欄でない限りは必ず1になります。

このままではA列は何の意味も持ちませんが、スライサーでフィルターをすることでSUBTOTALの特徴が活きてきます。 SUBTOTALは「非表示の行は集計しない」という特徴があります。

スライサーにより表がフィルターされると非表示となった行のSUBTOTALの結果は0になります。 ↓イメージ f:id:tomikiya:20200326223219p:plain

表示されている行はそのまま1となりますのでVLOOKUPで「1」を検索すると、スライサーで選択中の項目が引っかかるというわけです。

拡張

表示判定をカウントアップするようにしてPRIMARY KEYとして使えばフィルター後の一覧を拾えますね。

そのほか

今回は単一選択時の取得方法でしたが、複数選択時も取得する方法はあります。 若干複雑になるため別で扱いたいと思います。 [Excel]スライサーで選択中の項目を取得する 複数選択編