静岡県内では昨秋から降水量の少ない状況が続いており、この影響で気温が下がっても富士山には雪がなかなか降らず、雪化粧をした姿を見る機会が減っています。時々通過する低気圧の影響で雪が降っても薄く積もるだけなため、風で飛ばされたり溶けたりするなどして、すぐに消えてしまう状況です。
少雨の影響で大井川水系の水位が低下し、長島ダムの貯水率は49%と最近10年では2019年に匹敵する少なさとなっています。今日15日(金)の9時からは焼津市や掛川市など7市を対象とした上水道5%、工業用水と農業用水は10%の第1段階の取水制限を開始しています。
⇧ そんなことになっていたとは、静岡県出身の身としては心配になりますな、どうもボクです。
とは言え、大雨とかになると、農作物も高騰しそうですから良い案配の降水量を期待したいところですね。
というわけで、Excel関数についてレッツトライ～。
VLOOKUPって？
そもそも、ここで言ってる「VLOOKUP」って何を指すんだい？
基本的には「VLOOKUP」という話題が出てきたら、Microsoft Office Excelの関数のことを言ってるんだな、と考えれば良いとは思うのですが、
- Microsoft Office Excelの関数VLOOKUP
- LibreOffice Calcの関数VLOOKUP
- Googleスプレッドシートの関数VLOOKUP
ってな感じで、「VLOOKUP」を使える環境ってのは様々なわけなので、どの環境での「VLOOKUP」の話をしてるのか、っていうことは認識を合わせておいたほうが良いかもですかね。（ちなみに、私のPCにはMicrosoft Office製品が入っていません、つまりExcelは使えません...）
それぞれで、微妙に記述方法が異なってくると思われるので、カオスが生まれるわけですが、Microsoft Office Excelが使える環境であれば、Excelを使うのが一番良いかもですかね。
で、「VLOOKUP」ですが、Microsoftさんの説明によりますと、
その最も簡単な形式で、VLOOKUP 関数は次のようになります。
= VLOOKUP (検索する内容、検索する場所、検索対象の値を含む範囲内の列番号、近似または完全一致-1/TRUE、または 0/FALSE) を返します。
⇧ って感じで 、「引数」が4つありますと。
「引数」の説明は以下の通り。
| 要否 | 引数 | 説明 |
|---|---|---|
| 必須 | 検索値 | 検索する値、検索値 |
| 必須 | 範囲 | 検索値が含まれるセル範囲。 VLOOKUP が正常に機能するために、検索値は範囲の最初の列に必ず位置している必要があることに注意してください。たとえば、検索値がセル C2 にある場合、範囲は C 列から始まる必要があります。 |
| 必須 | 列番号 | 戻り値を含む範囲内の列番号。 たとえば、範囲として B2: D11 を指定した場合は、B の最初の列として B をカウントし、2番目の列に「C」と入力します。 |
| [検索方法] | 必要に応じて、戻り値として近似一致を検索する場合は TRUE、完全一致を検索する場合は FALSE を指定できます。 何も指定しない場合、既定値は TRUE、つまり近似一致を常に返します。 |
⇧ まぁ、「引数」の説明も分かり辛いんだが、そもそもとして、「VLOOKUP」はどういった時に利用するのが良いかっていう「概要」みたいなものが無いから「関数」の目的ってものが読み取りにくいのよね...
っていうか、「VLOOKUP」を実行した結果の戻り値についてちゃんと説明して欲しいんだが...
VLOOKUPは左から右にしか検索できない
知ってる人からしたら、些末な問題なんでしょうけど、衝撃でした。
な、何ということでしょう！
「検索値」は「検索範囲」の一番左の列に位置していないといけないという縛りがあるため、「検索値」より左の列は検索できない、という驚愕の事実に震えることでしょう、というか自分は震えましたね...
どういうことかと言うと、仮に以下のような「シート」があったとして、
⇧「支社」 の列の値を「検索値」とした検索は「VLOOKUP」ではできないということです。（「VLOOKUP」の用途としては、「検索値」に一致した値の「行」にある他の「列」の値を取得、ってことだと思うし、上記のような「シート」では「支社」の「列」である「F列」の右側に何も無いので検索ができない...）
なので、「VLOOKUP」を使って「支社」の列の値を「検索値」として検索した結果を抽出したい場合は、以下のように「支社」の列は左にあることが望ましいわけです。
ただ、上記のように「支社」の列を左端にもってきた場合、「商品名」の列で検索したいってなった場合に、今度は「商品名」を左端に...ってな感じでキリが無いわけですと。
VLOOKUPは指定した範囲から結果を1つしか抽出できない
「VLOOKUP」は指定した範囲から結果を1つしか抽出できないらしいという...
EXCELで条件検索・抽出の関数と言えばVLOOKUPですが、VLOOKUPの欠点の一つとして条件に対し結果が複数重複している場合にそのうち一つを抽出してしまうことです。
VLOOKUPは検索によく使われる関数です。しかし、弱点もいくつかあります。その中でも、「検索条件に一致するセルが複数ある場合は、一致するセルの中で一番上にあるセルを抽出する」ということがネックになることがよくあります。
⇧ 上記サイト様によりますと、「検索条件に一致したセルが複数あっても、1つのセルのみ抽出」っていう縛りもあるみたいですね...
なので、複数抽出したい場合は、関数を組み合わせるしかないらしい。
というか、VLOOKUP が複数抽出できないっていうような話について記載されてるドキュメントとかってあったりするのかしらね...
VLOOKUP以外で列から値を抽出するには？
脱線しましたが、左端から以外からも検索するにはどうしたら？
⇧ 上記サイト様によりますと、
- INDEX関数 + MATCH関数
- OFFSET関数 + MATCH関数
っていうコンボ（関数の組み合わせ）で実施することが多い感じなんですかね？
エクセルでは、SUMIF,COUNTIF,VLOOKUP,INDEX,OFFSET等々、これらの関数で他シートの情報を取得できます。
これらは、元表に対して情報を追加するものになりますが、SQLではより高度に複数の表を合体させる事が出来ます。
そして、その合体させる方法がSQLには複数用意されています。
⇧ 上記サイト様によりますと、やはりと言いますか関数のみでできることは限られてそうな感じですかね。
配列数式とは？
で、「VLOOKUP」とか「INDEX」とかいった関数は残念ながら、検索した結果が複数存在した場合でも1つしか結果を返してくれないんでした。
検索した結果を複数のデータで返して欲しいんだけど、どうしたら？
「配列数式」ってものが存在するそうな。
配列数式は、標準のワークシート関数では実行できない複雑な計算を実行できる強力な式です。 これらの数式は、ctrl キーと shift キーを押しながら enter キーを押して入力する必要があるため、"Ctrl-Shift-Enter" または "CSE" の数式とも呼ばれます。
⇧ 裏コマンド的な感じっすな...
数式を｛ ｝が付く「配列数式」にする必要があるのです。
⇧ 上記サイト様のように「波カッコ」で数式を囲ってあげると、「配列数式」って扱いにできるらしい。
単価×個数の合計とか算出したいけど
上手いこと列から複数の値を抽出できたとして、掛け算した結果を合計したいのよ、って時に使えるのが、
引数として指定した配列に対応する要素間の積（掛け算の結果）を計算し、さらにそれらの合計（足し算の結果）を同時に求めたい場合、SUMPRODUCT関数を利用します。
=SUMPRODUCT(配列1,配列2,配列3,...)
SUMPRODUCTとは、「掛け算（PRODUCT）したものを合計（SUM）する」という意味です。
配列とは、簡単に言うと、データが入力されている範囲のことです。また、引数のそれぞれの配列は、行数と列数が互いに対にする必要があります。等しくないとエラー値#VALUE!が返されるので注意してください。
引数は2個から30個まで指定できます。
⇧「SUMPRODUCT」っていう関数らしい。
Excelのバージョンによるのか、引数は、
計算の対象となる要素を含む、2 から 255 個までの配列引数を指定します。
⇧ 255個まで指定できるらしい、逆に言うと対象が255個以上での用途では利用できないってことですかね。
配列の各要素の数については特に言及がないので、Excelの最大行までいけるってことですかね？
⇧ 「1,048,576 行」が上限らしいので、「SUMPRODUCT」関数の引数の配列の要素については、「1,048,575」個までいけるってことですかね。
Excelの限界を見たといったところでしょうか...
ちなみに、
前項の配列数式で紹介したSUMのほか、MAXやMIN、LARGE、SMALL、AVERAGEといった一般的な関数の多くは、セル範囲の代わりに配列を扱うことができます。
誤解しないでほしいのですが、これらの関数で配列を扱うために、配列数式にする必要があるわけではありません。
そのまま配列が扱えるとはいっても、SUMなどの場合、カッコの中で行われている計算まで面倒を見てくれるわけではありません。COUNTIF関数の検索条件を配列化するためには、やはり配列数式にする必要がありました。
一方、Excelには、自らのカッコの中のセル範囲を配列として処理してくれる関数もあります。ここではその代表格として、SUMPRODUCTについて紹介することにします。
⇧ 上記サイト様によりますと、「SUMPRODUCT」関数は、デフォルトで「配列数式」みたいな機能を持ってるってことですかね。
あと、
以上のことから「計算式内で他のブック（ファイル）を参照するときにはSUMPRODUCT関数」を、「同じブックを参照するときにはCOUNTIFS関数」を利用することをお勧めします。
エクセル SUMPRODUCT関数で複数条件全て満たすセルをカウントする方法 - 病院ＳＥにゃんとのパソコン・エクセル活用術
それ以前のバージョンでは『SUMPRODUCT関数』を使いますが、2007以降であれば『COUNTIFS関数』のほうが計算速度が速いのでおすすめです。
⇧ 1つのブックで完結できる場合は「COUNTIFS関数」がおすすめらしいですね。
複雑な抽出はC#とかPythonとかを使う？
で、試行錯誤した結果、「検索した結果の複数の候補を元に更に検索して抽出」みたいなことはできなかったのよね...
別々のシート間で条件に合う結果で抽出とかがね...
やりたかったことのイメージとしては、
⇧ 上記のような集計（検索結果によっては複数人の単価×個数の合計を1つのセルに集約）を関数で実現したかったってことなんだけど。
Excel関数に詳しい人であれば、適切な組み合わせでいけるのかもしらんけど。
SQLっぽいことができるのがベストなんでしょうけど、SQL使えない場合はExcel関数でどうにかするっていうよりは、プログラミングで抽出するほうがまだ何とかできるんじゃないんかな、って想像するんだけども、もしプログラミングを使うんなら、Excelを扱いやすそうなC#とかPythonとかを使うのが良いんですかね？
JavaはExcelとの相性が良くなさそうだしね。（「Apache POI」とかも使いにくいっていう声が専らですし）。
まぁ、1つ分かったことは、Excel関数の世界もカオスな世界であるということですかね...関数の戻り値とかが記載されてるようなドキュメントとか見当たらんしな...
今日もモヤモヤ感しか残らなかった1日であった...
今回はこのへんで。
