初心者ユーザーではない場合は、Excel のすべての 99% が、パラメーターまたは属性 (フィールド) が列を通過し、オブジェクトまたはイベントに関する情報が配置されている垂直テーブルで動作するように設計されていることに既に気付いているはずです。行で。 ピボット テーブル、小計、ダブルクリックによる数式のコピーなど、すべてがこのデータ形式に合わせて特別に調整されています。
ただし、例外のないルールはなく、かなり定期的に、横方向の意味方向のテーブル、または行と列の意味が同じ重みを持つテーブルに出くわしたらどうするかを尋ねられます。
そして、Excelがまだ水平方向に並べ替える方法を知っている場合（コマンドを使用して データ – 並べ替え – オプション – 列の並べ替え) の場合、フィルタリングの状況はさらに悪化します。Excel の行ではなく、列をフィルタリングするための組み込みツールがまったくありません。 そのため、このようなタスクに直面した場合は、さまざまな複雑さの回避策を考え出す必要があります。
方法 1. 新しい FILTER 関数
新しいバージョンの Excel 2021 または Excel 365 サブスクリプションを使用している場合は、新しく導入された機能を利用できます フィルタ （フィルター）、行だけでなく列によってもソース データをフィルター処理できます。 この関数を機能させるには、補助的な水平方向の XNUMX 次元配列行が必要です。ここで、各値 (TRUE または FALSE) によって、テーブルの次の列を表示するか、逆に非表示にするかが決定されます。
テーブルの上に次の行を追加して、各列のステータスを書き込みましょう。
- 最初と最後の列 (ヘッダーと合計) を常に表示したいので、配列の最初と最後のセルに値 = TRUE を設定するとします。
- 残りの列では、対応するセルの内容は、関数を使用して必要な条件をチェックする数式になります。 И （と） or OR （OR）. たとえば、合計が 300 から 500 の範囲内であること。
あとは関数を使うだけです フィルタ 補助配列が TRUE 値を持つ列を選択するには:
同様に、特定のリストで列をフィルタリングできます。 この場合、関数が役立ちます COUNTIF (カウンティフ)、許可リスト内のテーブル ヘッダーから次の列名の出現回数をチェックします。
方法 2. 通常のピボット テーブルの代わりにピボット テーブルを使用する
現在、Excel にはピボット テーブルでのみ列による水平フィルタリングが組み込まれているため、元のテーブルをピボット テーブルに変換できれば、この組み込み機能を使用できます。 これを行うには、ソース テーブルが次の条件を満たす必要があります。
- 空のセルや結合されたセルのない「正しい」XNUMX 行のヘッダー行を持っています。そうしないと、ピボット テーブルを作成できません。
- 行と列のラベルに重複を含めないでください。それらは、集計で一意の値のみのリストに「折りたたまれ」ます。
- 値の範囲内 (行と列の交点) の数値のみが含まれます。これは、ピボット テーブルが何らかの集計関数 (合計、平均など) を確実にそれらに適用し、これがテキストで機能しないためです。
これらの条件がすべて満たされている場合、元のテーブルのように見えるピボット テーブルを作成するには、(元のテーブルを) クロス集計からフラット (正規化) に展開する必要があります。 これを行う最も簡単な方法は、2016 年以来 Excel に組み込まれている強力なデータ変換ツールである Power Query アドインを使用することです。
これらは、次のとおりです。
- テーブルを「スマートな」動的コマンドに変換しましょう ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット).
- コマンドを使用して Power Query に読み込む データ – テーブル/範囲から (データ – テーブル/範囲から).
- 合計で行をフィルタリングします (要約には独自の合計があります)。
- 最初の列見出しを右クリックして、 他の列を折りたたむ (他の列のピボットを解除). 選択されていないすべての列は、従業員の名前とそのインジケーターの値の XNUMX つに変換されます。
- 列に入った合計で列をフィルタリングする 属性.
- コマンドを使用して、結果のフラット (正規化) テーブルに従ってピボット テーブルを作成します。 ホーム — 閉じてロード — 閉じてロード… (ホーム — クローズ & ロード — クローズ & ロード…).
ピボット テーブルで利用可能な列をフィルターする機能を使用できるようになりました。名前と項目の前にある通常のチェックマーク 署名フィルター (ラベルフィルター) or 値によるフィルタリング (値フィルター):
もちろん、データを変更するときは、クエリとサマリーをキーボード ショートカットで更新する必要があります。 Ctrlキー+他の+F5 またはチーム データ – すべて更新 (データ — すべて更新).
方法 3. VBA のマクロ
簡単にわかるように、以前のすべての方法は厳密にはフィルタリングではありません。元のリストの列を非表示にするのではなく、元のリストから特定の列のセットを使用して新しいテーブルを形成します。 ソース データの列をフィルター処理 (非表示) する必要がある場合は、根本的に異なるアプローチ、つまりマクロが必要です。
テーブルヘッダーのマネージャーの名前が黄色のセル A4 で指定されたマスクを満たす列をその場でフィルタリングしたいとします。たとえば、文字「A」で始まります (つまり、「Anna」と「Arthur」を取得します)。 " 結果として）。
最初の方法と同様に、最初に補助的な範囲行を実装します。ここで、各セルで基準が数式によってチェックされ、論理値 TRUE または FALSE が表示列と非表示列にそれぞれ表示されます。
次に、簡単なマクロを追加しましょう。 シート タブを右クリックし、コマンドを選択します。 ソース （ソースコード）. 次の VBA コードをコピーして、開いたウィンドウに貼り付けます。
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Each cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub そのロジックは次のとおりです。
- 通常、これはイベント ハンドラです。 ワークシート_変更、つまり、このマクロは、現在のシートのセルが変更されると自動的に実行されます。
- 変更されたセルへの参照は常に変数内にあります ターゲット.
- まず、ユーザーが基準 (A4) でセルを正確に変更したことを確認します。これはオペレーターによって行われます。 if.
- その後、サイクルが始まります それぞれのために… 各列の TRUE / FALSE インジケータ値を使用して灰色のセル (D2:O2) を反復処理します。
- 次の灰色のセルの値が TRUE (真) の場合、列は非表示になりません。それ以外の場合は非表示にします (プロパティ 隠されました).
- Office 365 の動的配列関数: FILTER、SORT、および UNIC
- Power Query を使用した複数行のヘッダーを持つピボット テーブル
- マクロとは何か、マクロの作成方法と使用方法