|
|
|
2023/10/17
カテゴリ：ソフト紹介
Excelで表の中から条件に一致する特定の値を参照したいとき、
VLOOKUP関数 HLOOKUP関数 INDEX関数＋MATCH関数 XLOOKUP関数（2020年以降なら） などを使う方法がある。 これらの関数を駆使すれば、表の中から条件に一致する特定の値を持ってきたいとき大抵の場合上手くいくのだが、この間仕事で詰まったことがある。 その時やりたかったことが、 ・IDが一致する ・日付が「～以上」である ・日付が「～以下」である という３つの条件に一致する行の値を抽出したいというもの。 上で挙げた関数たちは、基本的にただ条件に一致するものを持ってくるのは得意なのだが、 「～以上」とか「～以外」とかの条件を指定しようとするとちょっとテクニックが必要となる。 しかも今回はそれを「～以上」と「～以下」で複数条件・・・ 複数条件となると、私の知っているちょっとしたテクニックでもうまくいかない・・・ 参照元の表に計算用の列をいくつも用意すればそれも実現できるのだが、保守性の面でそれはやりたくなかった。 そういうとき使える方法をその時頑張ってネットで検索したのだが、いい方法が見つからず・・・ 結局自分なりにやり方を見つけたので、忘れないようここに残しておく。 概要  例として上記のような表が存在するとする。 各クラスの月ごとの日直担当を管理している表ですね。  この日直管理表から、↑の結果表のK4セルのように ・クラス：２－１ ・日付：2023/2/1 ・性別：女 の日直の名前を抽出したい場合、以下のような計算式となる。 計算式を要約するとこんな感じ。 最初と後ろに{}がついているのは、配列数式という方法を使っているため。 （配列数式がわからない人は、他のサイトで先に調べたほうがいいかもしれません。） ◆注意 配列数式を使っているため、この計算式をセルに入力後は、 必ずCtrl+Shift+Enterキーで確定する必要があります。 ※：ただしExcel2019以降のバージョンなら、このキーで確定する必要はなし このExcel計算式は、大きく３つに分けられる。 ①：②：③：①の計算式は、表内を検索するときの条件 ②の計算式は、①と③の計算式を繋ぐためのもの ③の計算式は、最終的にセルに表示したい値の範囲を設定する ちなみに 逆に 列ごと範囲を指定してもなぜうまくいくのか、 各箇所で範囲の高さが異なるとなぜおかしくなるのかは、この後の説明を見ればわかる・・・はず！ ①の計算式 この計算式はさらに ①-A：$B$9:$B$33=$H4①-B：$C$9:$C$33<=$I4①-C：$D$9:$D$33>=$I4①-D：$E$9:$E$33=$J4（抽出したい条件を変えたい場合、これが３つになったり６つになったりする。） 今回の例では、一致/以上/以下のパターンのみ扱っているが、当然 ①-X：$B$9:$B$33<>$H4 （B9:B33の範囲内の値が、H4セルの値と不一致か）この計算式をそれぞれ()で囲んで、それぞれを乗算(*)すると、 指定した全ての条件に一致する場合1を返す という計算式になる。 なぜ乗算するのか？ 乗算するとどうなるのか？ については、 ・Excelでは「A=B」のような条件式を書いた場合、・Excelでは計算するとき、①の計算過程を表で説明すると以下のような感じ。  ①の計算結果：{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;0;0;0;0;0;0;0;0} ②の計算式 MATCH関数は、 第2引数で指定した範囲内から、第1引数に一致するセルの相対的な位置を返す。 今回の計算式の場合、 第1引数が1 第2引数が【①の結果】 のため、 配列数式で返ってきた①の計算結果内から、 1に一致するセルが何番目なのかを返してくれる。 ちなみに1に一致するセルが複数ある場合、一番最初に見つかったセルを返す。 ②の計算過程を表で説明すると以下のような感じ。  ②の計算結果：16 ③の計算式 INDEX関数は、 第1引数で指定した範囲内から、[第2引数で指定した値]行目のセルの結果を返す。 （第3引数は何列目かを指定するが、今回は1行だけの範囲を指定しているため1固定） 今回の場合、第1引数の範囲がF9:F33なので、 第2引数が1ならF9の値 第2引数が2ならF10の値 第2引数が3ならF11の値 ～～～ 第2引数が24ならF32の値 第2引数が25ならF33の値 って感じで返してくれる。 今回の例の場合、【②の結果】が16のため、以下のように上から16番目のF24の値を返す。  ③の計算結果："花子16代目" 応用編：OR検索したい場合 ここまでは全てAND条件の検索だったが、 ここでさらにOR検索をしたい場合、以下のような計算式になる。 実際の検索例でいうと以下のような感じ。  先に説明したANDだけの検索計算式と異なるのは、 上の 赤い条件式を【条件A】 青い条件式を【条件B】 だとすると、 【条件A】 or 【条件B】 といった条件になる。 条件同士を()で囲んで+してあげればor条件での検索は完璧！ と言いたいところなのだが・・・ 見ての通りこの計算式では２つの条件式の結果を足しているので、 両方の結果がTRUEになる場合、結果が2となってしまい、そのままMATCH関数に渡すとうまく動かない。 なので、 【条件A】＋【条件B】を 条件の結果を加算した結果が1以上の場合1を返してくれるようになる。 これはorにしたい条件が増えて ただ当然のごとく、条件を増やせば増やす程どんどん重くなるので取扱い注意！ デメリット VLOOKUP関数 HLOOKUP関数 INDEX関数＋MATCH関数 XLOOKUP関数（2020年以降なら） この方法が上記の方法より明確に劣っている部分を説明する。 デメリット① 「～を含む文字」といったワイルドカード検索ができない MATCH関数やLOOKUP関数を使った条件指定なら、 「花子*」って感じで条件を指定してあげれば、「花子」から始まる文字列を検索してくれるが、今回の方法ではそれが使えない。 もし 名前が「花子」から始まって、 2020/1/1 ～ 2020/1/31の期間で日直を担当した人 て感じで調べたい場合は、 諦めて計算用の列を別に用意するか、 VBAマクロを使って集計したほうが良いかと思います。 デメリット② 重い 遅い とりあえずこれが１番のデメリット。 データ量が大したことなければサクサク動くが、 この計算式が大量に使われているExcelを開くと重くなるし、計算にも時間がかかる。 もちろんそれは他の計算式にも言えることですが、、、やはりVLOOKUPなどの標準のExcel関数に比べれば重い。 まず今回使っている配列数式という仕組みそのものが重い。 今回説明したこれ以外にもいくつか配列数式を使う方法は考えたが、他はさらに重かった。 データ量の多い表に対してこの計算式を使うなら、Excelの自動計算の設定をOFFにするのが賢明でしょう。 もしくはやっぱり、デメリット①と同じくVBAマクロを使うかですね。 この方法は自己流の方法で、正直全然自信が無いため、 「もっといい方法があるよ」 「ここがどういう意味かわからない」 「こういうときに役に立ったよ」 という方がいらっしゃったら是非是非コメント頂けると有り難いですm(_ _)m お気に入りの記事を「いいね！」で応援しよう
Last updated
2023/10/27 03:07:33 PM
コメント(0) | コメントを書く
[ソフト紹介] カテゴリの最新記事
|
