条件に合うデータを全て抽出できる数式のQ&A集
「条件に合うデータをすべて順に抜き出す数式」を解説する１１ページ目です。
ここでは、エラーが出たり、数式コピーで思った値が返ってこなかったり、エクセルが重くなったりとした問題の解決方法を解説します。
なお、解説の１ページ目を見るにはこちらです。
１．「入力した数式は正しくありません」と表示
まずは、「入力した数式は正しくありません」と表示された場合です。
１－１．原因は？
このエラーの場合、原因は「カッコが多い」ことが考えられます。
そもそも、数式の基本的な形は次の通りです。
この内、どこか一つでもカッコが多い、または少ない場合にはエラーになるのです。
１－２．対処法は？
対処法は２つあります。
１－２－１．関数の引数ダイアログボックスで確認する
一つは、「関数の引数ダイアログボックスで確認する」ことです。
関数の引数 ダイアログボックスは、数式バーの左側にある「fx」を押すと立ち上がります。
そして、各関数をクリックし、エラーになっていないかを確認します。
例えば、2個目のINDEX関数でかっこが足りない場合には、次のように表示されます。
INDEX関数の配列は、本来赤矢印の「,」の前までが範囲なので、ここにかっこが足りないとみることができます。
このようにして、かっこに過不足がないかを確認していきます。
１－２－２．数式の基本形をコピーして作り直す
もう一つの方法は、「数式の基本形をコピーして作り直す」ことです。
かっこの数の過不足は、数式を手入力したために起こりやすいです。
ただ、どこのかっこが多いか足りないかを見るのも、慣れないと結構手間です。
そこで、数式の基本形をコピーし作り直したほうが分かりやすいかもしれません。
繰り返しになりますが、数式の基本形は以下の通りです。
これを使って、数式を作り直してみましょう。
２．数式コピーで思い通りの値が表示されない
次に、「数式コピーで思い通りの値が表示されない」場合です。
２－１．原因は？
数式を作ったセルは正しく表示され、コピーすると正しく表示されない場合、「適切に絶対参照にしていない」ことが考えられます。
絶対参照とは、「『$』を付けることで数式をコピーしても参照先が変わらないようにする」ことを言います。
適切に絶対参照にしないと、コピー時に参照先が変わって、結果正しい結果がかえってきません。
２－２．対処法は？
では、数式を見ながら、絶対参照にする個所を確認します。
まず、数式の基本形は以下の通りです。
この中の条件式を分解すると、次のようになります。
条件範囲 → 比較演算子 → 条件値
これらを組み合わせると、数式の基本形は次のようになります。
このうち、絶対参照にするのは、以下の通りです。
（１）条件値がセル番号以外
（２）条件値がセル番号
それぞれ赤字の箇所に「$」を付けます。
「$」の付け方は、「カーソルを当ててF4キーを１回押す」です。これを３ヶ所に行います。
すると例えば、数式は次のようになります。
これで、他のセルに数式をコピーしても正しい結果が得られるようになります。
３．エクセルが重くなった
続いて、数式を入れることで動きが重くなった場合です。
３－１．原因は？
原因は、数式の構成です。
この数式は、配列で計算をします。
通常、数式は途中の計算の答えが一つですが、配列数式は「複数の答え」が返ってきます。
結果、たくさんの箇所でこの数式を使うと、計算に時間が掛かってしまうのです。
経験的には、5000行×8列以上の時に重くなりました。
これはあくまで参考値です。パソコンのスペックなどでも変わってきます。
３－２．対処法は？
対処法は、「『抽出対象の列』で指定する範囲を少なくする」ことです。
具体的には、「『A:A』など列の全行」ではなく、「列の1行目から条件範囲の最後の行」までにします。
例えば、次のような数式があるとします。
そして、A列の値が実際にはセルA9までしか入っていない場合には、この数式を次のように直します。
これで計算量がぐっと減り、結果動きが軽くなります。
ただし、この方法のネックは「元の表への追加の都度、『抽出対象の列』の範囲を直す必要がある」ことです。
範囲を直してあげないと、条件に合っても範囲外のものは抽出されません。
４．抜き出す列を任意で変えたい
最後に、抜き出す列を任意で変えたい場合です。
４－１．抜き出す列を指定している箇所は？
数式の中で、抜き出す列を指定しているのは「抽出対象の列」です。
ここがA列であればA列の値が、D列であればD列の値が返ってきます。
４－２．対処法は？
「抽出対象の列」を一番左端の列ではなく「抜き出したい列」に変えると、任意の列の値を抜き出せます。
例えば、次のような数式があるとします。
この数式だと、A列の値が返ってきます。
これを、D列の値が返ってくるようにするには、次のように直します。
これで、任意の列の値を抜き出すことができます。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】
なお、一連の記事の好きなページに行けるリンクはこちらです。
- 条件に合うデータ抽出 各ページへのリンクはこちら
- ◎具体例の確認と数式の概要（1/12頁）
- ◎数式の作り方（2/12頁）
- ◎数式構成の解説（3/12頁）
- ◎条件値の基本的な指定方法（4/12頁）
- ◎空白、空白以外の条件指定方法（5/12頁）
- ◎以上、以下等の指定方法（6/12頁）
- ◎含む条件、含まない条件の指定方法（7/12頁）
- ◎OR（または）条件の指定方法（8/12頁）
- ◎OR条件値を範囲指定する方法（9/12頁）
- ◎OR条件以外と指定する方法（10/12頁）
- ◎数式のQ&A（11/12頁）
- ◎空白が０になる対処法（12/12頁）