id:kazuhi_raです。この記事はSpreadsheets/Excel Advent Calendar 2019の16日目の記事です。昨日はミネムラ珈琲 (
id:minemuracoffee)さんの「Spreadsheetの関数リストから仕事を始めてしまうのは学校教育の敗北 - ミネムラ珈琲ブログ」でした。
さて、Spreadsheets/Excel Advent Calendarなんてものに登録しておきながら何なのですが、普段の業務でspreadsheetsを使ってはいるものの、別にめちゃくちゃテクいことをしている訳でもできる訳でもなく、何を書こうか困ってしまいました。
そこで思いついたのが、アドベントカレンダーのこの記事を、今絶賛取り掛かり中の業務の引継ぎにしてしまうことです。引き継ぎ先の方はこれまであまり表計算ソフトを使ってこられなかったとお聞きしています。そして引き継ぐシートはというと縦30000行の地獄シート(すまぬ...すまぬ...)。ですので、シートで使われている各関数の機能一覧のようなものがあれば将来困ったときに役に立つはずです。ではではでは↓↓↓↓↓↓↓↓↓
SMALL(データ, n)
データセットから n 番目に小さい要素を返します
UNIQUE(範囲)
重複する行を破棄して、指定したソース範囲内の一意の行を返します
RANK(値, データ)
データセット内の特定の値の順位を返します
ROW(セル参照)
指定したセルの行番号を返します。
VLOOKUP関数などで、参照先に重複した値があって大変困る、というシチュエーションがあったりします。↑の場合だと1位が2つありますね。そういうとき、それぞれの値をほんのり変えるというのをよくやります。+ROW()/9^7 では、そのセルの行番号を返すROWを、大きい数字(9^7=478269)で割って、それを足す、というのをやっています。画像で洗濯しているセルでいうと、2 / 9^7が1に足されたことになります。
INDEX, MATCH, そしてEXACT
これら2つ(EXACTも使えば3つ)の組み合わせがパワーを発揮します
INDEX(参照, 行, 列)
画像の例で説明すると、A2:C5という選択した範囲の中で上から4つ目左から2つ目の値を示しています
MATCH(検索キー, 範囲, 検索の種類)
範囲内での相対的な位置を返します。画像の例では「みねむら」がA2:A5という選択範囲の中で上から3番目にある、ということを示しています。検索の種類についてはあまり考えず0(完全一致)を入れておけば問題ありません。0の他には1と-1が使えます。それぞれどういう働きをするか、気になったら調べてみてください。
INDEX(参照, MATCH(検索キー, 範囲, 検索の種類), 列)
INDEXとMATCHの合わせ技です。パッと見では何をやっているのか？という感じかもしれないですが、実はそんなに複雑ではありません。まずMATCHでは、A8つまり「かずひら」がB2:B5という範囲の中で上から何番目か、の値が返っているはずです。つまり、MATCHの部分は4に置き換わって以下のようになるはずです。
こうしてみると簡単ですね。A2:C5という範囲の中で上から4つ目左から3つ目の値、つまり「1000」が示されます。
そしてEXACT
それでは1つ名前を増やしまして、この方の得点をさっきの合わせ技でゲットしましょう。ほい！
あれ？0が出て欲しいはずが1000になってしまいました。どうやらMATCHがカタカナの「カズヒラ」ではなく、ひらがなの「かずひら」を探してきてしまっているのが原因のようです。なんとMATCHはひらがなとカタカナの区別ができません。この問題を解決するにはこうします。
EXACT(文字列, 範囲)では文字列と範囲が照らし合わされて、完全に一致しているかどうか、の結果の値が複数個返ってきます(配列という形式(のはず))。FALSE, FALSE, FALSE, FALSE, TRUE といった具合です。次に、それらがmatch(true, EXACT(A10,B2:B6))によって、配列の何個目がTRUEか、というのが判定され、この場合だと5つめがTRUEなので5 という値が返ってきます。つまり、返ってきた値に置き換えて書くと下のようになります。
INDEX+MATCHやVLOOKUPがひらがなとカタカナ、アルファベットの大文字小文字の区別をしてくれなくて困る！というときはこうしています。もっといい方法があれば教えてください！
明日はkamoさんの「Excelの循環参照で〇〇を実装する！」です。〇〇とは一体何なのか。楽しみです。
