セルを結合したままフィルターモードでデータを抽出するには

Excelの結合セルは、やっかい扱いされることが多いですが、その1つがフィルターを設定しても正常に抽出されないということがあります。

セルの結合を解除してデータとして正常になるように修正すればいいのですが、レイアウトをどうしても変更できないということもあります。

その場合の方法として紹介します。Excelのバージョンには関係なく操作できます。

結合セルでのフィルター

例えば、以下のような表があります。A列に［セルを結合して中央揃え］を設定したセルがあります。

この表にフィルターの設定をします。

表内のセルをアクティブにして、［データ］タブの［並べ替えとフィルター］グループにある［フィルター］をクリックします。

以下のように列の見出しにフィルターボタンが現れます。

A列のフィルターボタンをクリックして、絞り込んでみます。

ここでは、［横浜店］を選択して［OK］ボタンをクリックします。

以下のようになります。一番上のセルのみが絞り込まれています。これは求めたい結果ではありませんよね。

結合セルとは

結合セルを解除してみます。

セル［A2］を選択して、［ホーム］タブの［配置］グループにある［セルを結合して中央揃え］をクリックします。

以下のように、一番上のセル以外は空白セルになっています。

結合セルは、結合前のセル範囲の左上のデータしか保持されません。

セルを結合するときのメッセージ

セルを結合するときもメッセージウィンドウが表示されます。

左上のセル以外が空白セルの場合は、上記メッセージウィンドウは表示されません。

でも、レイアウトや見栄えのためだけに、［セルを結合して中央揃え］を使用される場合も多いと思います。

結合セルでフィルターを実行して正常に絞り込むには

結合セルがあっても、正常な絞り込みをする方法です。

別のセルに結合されたセルと同じ文字列を入力して、そのセルをコピーします。

結合セルを選択します。

［ホーム］タブの［貼り付け］の下方をクリックします。

そして、貼り付けオプション内の［数式］をクリックします。

以下のようになります。見た目は結合されたままです。

［数式］を貼り付けた後のフィルター

フィルターで［横浜店］を選択してみます。以下のように正常に絞り込まれます。

参考どうしてこうなるかというと、結合を解除してみると分かります。以下のように各セルに文字列が貼り付けられています。

結合を解除して試されてもいいですが、戻すときは［元に戻す］操作で戻してください。元に戻す操作時にもメッセージウィンドウが表示されますが、［はい］をクリックしても大丈夫です。

数式の貼り付けを数字が入力された結合セルに実行してしまうと、関数の結果が異なるものになってしまいますので気をつけてください。

結合セルが多い場合の操作

結合されたセルが多いと、コピーして数式を貼り付ける作業も面倒ですね。

まとめて貼り付ける方法です。

結合されたセルを範囲選択して、コピーします。

そして、表以外のセルへ貼り付けします。

ここでは、セル［E2］を選択して貼り付けします。

そして、［セルを結合して中央揃え］のボタンをクリックして解除します。

以下のようになります。このまま選択しておきます。

空白セルを選択して数式を入力

［ホーム］タブの［編集］グループにある［検索と選択］をクリックして、［条件を選択してジャンプ］をクリックします。

［選択オプション］から［空白セル］を選択して、［OK］ボタンをクリックします。

以下のように空白セルのみが選択されます。

セル［E3］が選択された状態のまま、数式バーに以下の数式を入力します。上のセル番地を入力して同じ値にします。

そして、キーボードから［Ctrl］+［Enter］を押して、まとめて数式を入力します。

［Ctrl］+［Enter］は、選択中のセルにまとめて同じ値を入力するショートカットキーです。

以下のようになります。

コピーして［値］を貼り付け

範囲選択してコピーします。

一番上のセル（ここではセル［E2］）を選択して、キーボードから［Ctrl］+［Shift］+［↓］を押します。

範囲が小さい場合は、ドラッグでもかまいませんが、範囲が広い場合は、ショートカットキーを使用すると便利です。

コピーします。

［貼り付けオプション］から［値］を選択して貼り付けをします。

参考空白セルに上のセルの値を入力する方法は、以下の記事でも解説しています。

コピーして結合セルへ［数式］の貼り付け

値を貼り付けたセルを選択して、再度、コピーします。

結合セルの一番上のセルを選択します。

ここでは、セル［A2］ですね。

［貼り付けのオプション］から［数式］を選択して貼り付けします。

メッセージウィンドウが表示されますので、［はい］をクリックします。

以下のようになります。見た目は結合された状態です。

フィルターも正常に動作します。以下は、［博多店］で絞り込んでいます。

参考不要になった列は削除してかまいません。

試しに結合セルを解除してみると、以下のようになっています。

これなら、ピボットテーブルもできるし、結合セルを使っていいと思われるかもしれませんが、おすすめはしません。

昔から裏技として使用されていた方法ですが、正しい使い方？とは思えませんので、どうしても結合セルのままにしておきたいという場合にのみ使用されるほうがいいと思います。

上のセルと同じ文字列なら空白に見せるようにする方法も解説しています。

また、横並びのセルの結合は、できるだけ［選択範囲内で中央］を使用されることをおすすめします。［選択範囲内で中央］の書式は、［セルのスタイル］へ登録しておくと便利です。

フィルターは、データクレンジングにも役立つ機能です。

フィルターボタンが先頭行のセルに表示されない場合の対処方法は、以下の記事で解説しています。