先日の、スピル関数の話の続きです。
ドロップダウンリストの要素に、スピル関数の範囲を示す『#』が使えた、という話でした。
使用シーンは多くないかもしれませんが、「すごいことだなー」ととっても驚きました。
ところで、最近は『XLOOKUP』関数や『SUMIFS』関数など、新しく融通の聞く関数がたくさん出てきているし、パソコン自体の性能もとても上がってきているので、データベース関数を使う機会がめっきり減ってしまいました。
…いや、私だけかもしれませんが…。
でも、たくさんのデータの計算結果を素早く出せるデータベース関数って、結構好きなんですよね。
ただ、データベース関数の引数の指定が結構厄介なんです。
詳しい引数の指定はこちらの『できるネット』ページを見ていただけるとわかります。
DAVERAGE(データベース, フィールド, 条件)
DAVERAGE(Database, Field, Criteria)データベース関数は上のような書き方をします。
(昔はあまり親切な時代ではなかったので、データベース関数の説明はよく下の英語表記でされていました)
1つ目の『データベース』は、その名の通り計算に使うためのデータベースを指定します。
Excel 上で表っぽくなっているところの範囲を、見出しを含めて指定する感じです。
2つ目の『フィールド』は、1つ目の『データベース』で含めた見出しの見出しの座標を指定します。
そして、問題は3つ目の『条件』です。
この『条件』は、Excel 上の、今データベース関数を書いているセル番地とは別の場所に書かなければいけないんです。
他の関数であれば、例えば『”>1000”』のように条件を直接書くんですが、データベース関数の場合は別の場所に条件を書いて、その場所を『条件』の欄で指定するんです。
わざわざ別の場所に、条件を書く欄を設定しなければいけないのが、意外と面倒なときもあります。
あと、当たり前ですが、ちゃんと指定しないと、意図した答えが返ってこないところも。
今回、やりたかったことはこんな感じです。
例えば、商品のリストがあるとします。
商品番号、商品名、商品のカテゴリ番号、商品の値段…のような項目が書かれています。
その中で、例えば「商品のカテゴリ番号1～50の商品の値段の平均」、「商品のカテゴリ番号1～38の商品の値段の平均」、「商品のカテゴリ番号1・2・5～12の商品の値段の平均」のように、特に規則性のない複数のカテゴリ合算の平均値を出す作業したい、と。
で、リストはたまに更新されて、その更新のたびに上のような作業を行います。
平均を出す関数といえば『AVERAGE』関数ですが、複数の条件がある場合は『AVERAGEIFS』関数を使うと思います。
ただ、今回は『カテゴリ番号』が複数、しかも AND 条件ではなく OR 条件なんですよね。
なので、『AVERAGEIFS』関数ではなく『DAVERAGE』関数のほうがいいと考えました。
データベース関数の条件の書き方としては、横並びに書くと AND 条件、縦並びに書くと OR 条件となります。
なので、今回のケースだと、
DAVERAGE(データベースの場所, 『商品のカテゴリ番号』のセル番地, 縦に長い数字の羅列)という感じで指定することになります。
『データベースの場所』と『『商品のカテゴリ番号』のセル番地』は実行回ごとでは固定ですが、『縦に長い数字の羅列』は毎回変わります。
そしてこのケースでは、150回近く求めなきゃいけないんですね…。
なので、そのへんはマクロで組むことにしました。
で、その平均の価格を表示するセルの横に、ここで求める平均値の商品カテゴリ番号をずらーっと記入しました。
OR 条件は縦書きなので、じゃぁこれはスピルの『TRANSPOSE』関数で縦書きにするか…と考えたときに、ふと「データベース関数の条件(Criteria)にスピルの『#』が使えたらちょっと便利だな」と思ったんです。
で、やったら、できました。
へーーーー!
いや、『TRANSPOSE』関数で縦横入れ替えたときに始点から終点の座標を取っているから、それを流用すればいいんですけど。
それでも、ただ『#』と書くだけでいいっていうのは本当に簡単で驚きました。
1つ工夫(?)したのは、『TRANSPOSE』関数で縦横入れ替えするときに、始点に『商品のカテゴリ番号』という『フィールドの名前』を入れておいたことです。
項目名もあらかじめ『TRANSPOSE』関数に取り込んでしまっていれば、本当に『#』って書くだけで OK なので。
長々とした説明になってしまいましたが、要するにタイトルの『データベース関数の条件(Criteria)にもスピルの『#』が使えました』ということでした。
昔からあるデータベース関数でもスピルの『#』が使えるなんてすごいなーって、それだけなんですけど。
本当に、Excel は便利ですね、大好きです。
コメント