kawa-tの日記： Excelで表から直近のデータを抽出する 9
ディストロの各バージョンのリリース日をまとめた以下のような表があったとする。
Distro Release date Code name Version
Debian 6/17/1996 buzz 1.1
Debian 12/12/1996 rex 1.2
Debian 6/2/1997 bo 1.3
Debian 7/24/1998 hamm 2
Debian 3/9/1999 slink 2.1
Debian 8/15/2000 potato 2.2
Debian 7/19/2002 woody 3
Fedora 11/6/2003 Yarrow 1
Fedora 5/17/2004 Tettnang 2
Ubuntu 10/20/2004 Warty Warthog 10
（省略） （省略） （省略） （省略）
Ubuntu 4/23/2015 Vivid Vervet 15.04
Debian 4/25/2015 jessie 8
Fedora 5/26/2015 - 22
Debian stretch 9
Debian buster 10
Fedora - 23
この表から、各ディストロについて、最新版のリリース日時やコードネーム、バージョン番号をまとめた表を作る。手作業やマクロは却下で、シート関数のみを用いることが条件。
まず、最新版のリリース日だが、未来の日付はないものとすると最大値を取ればよいから、MAX()を使う。MAX($B:$B)とすれば最大値が得られるが、これだとディストロごとの最大値ではない。ディストロごとのリリース日の列を新たに設けるのも手だが、それはしたくないので、配列数式を使う。
=MAX(IF($A:$A="Debian",$B:$B))
と入力し、CtrlとShiftを押しながらEnterを押せば、配列数式になる。A列の値が"Debian"であればB列の値、そうでない場合はFALSEとなる配列を生成し、そこから最大値をとる。これでディストロごとの最終リリース日が得られる。
次に、ここからコードネームやバージョン番号を取得したいわけだが、リリース日を利用してVLOOKUP()などを使うと、他のディストロとリリース日が被っていた場合に正しい値が得られない。必要なのは行番号なので、リリース日の値に行番号の情報を押し込む。行番号の最大値は1048576なので、リリース日の値を10^7倍し、それに行番号を足してやれば、リリース日で最大値を得た時に、10^7で割った時の余りが行番号になる。具体的には次の数式。
=MOD(MAX(IF($A:$A="Debian",$B:$B*1e7+ROW($B:$B))),1e7)
これで行番号が得られるので、"Debian"の部分をディストロ名の入ったセルのセル番号に置き換えれば、各ディストロの最新版の行番号が求められる。あとはINDEX()を使えば、リリース日時やコードネーム、バージョン番号が得られる。
DMAX関数 (スコア:1)
Re:DMAX関数 (スコア:1)
コメントありがとうございます。
でも、求めたいのは最大値そのものだけではなくて、最大値のある行の値。
Re:DMAX関数 (スコア:1)
おっと、DMAX()とDGET()を併用すればいいのか。ディストロ名と最新のリリース日が決定すれば、DGET()で値が抽出できる。
ただ、1つ目のディストロはいいけど、2つ目以降には項目名がすぐ上にないのが、問題。
Re: (スコア:0)
表示するディストロが追加されていく毎に改造していくんですか？
改造しない一回限りの作業ならsortしてディストロ毎の一行目を抜き出す方が早いし、
繰り返し使うなら項目追加のたびに改造していかなきゃならない作りってどうなのかな？
Re:DMAX関数 (スコア:1)
コメントありがとうございます。
1回限りの作業なら、データを別シートにコピーし、降順ソート（Alt A S D）した後に、重複データの削除（Alt A M）で一覧表ができますね。1回限りの作業だけでなく、新規データが多数ある場合にも有効な方法です。ただ、それだと新規データだけでなく更新データの追加のたびにその作業をしないといけないので、シート関数を使うわけです。
もちろん、更新データではなく、新規データの追加があった場合には、項目の追加が必要になりますが、実際の業務では「新規」と明示された上でデータをもらうので、意図的な追加に関してはそれほど問題にはなりません。むしろ、項目名の後に意図せず空白が入っていたり、タイポがあったりして、計算対象から外れていた場合が問題になります。
そういう場合に備えて、全データの行数と、項目ごとにカウントした行数の合計が一致しないときには、常時表示されるセルが色付け表示されるように条件付き書式を設定して、視覚的に容易に判別できるようにしておく必要がありそうです。
Re:バカなのかな… (スコア:1)
コメントありがとうございます。
あなたのような人に「どこからデータを取ってきたんですが？」と聞かれたこともありますが、「実はもっと大きく、もっと多大なデータの断片から生成された表」だとは思っていなかったみたいです。
こちらは情報をコンピュータで扱えるようにするところからが仕事なんですけど、そういう人たちは、既にコンピュータで扱えるデータがあるということが前提なんですよね。
Re: (スコア:0)
Re: (スコア:0)
私だとdistoro名は4文字以上有って、5文字までで判別できると前提を置いたうえで、
head -1 file.in;grep -v '^Distro' file.in| sort -r -t'/' -k1,1.5 -k3 -k1 -k2|uniq -w5
と、sh,head,grep,sort,uniqと5つも使ってしまいました。
フィールド区切り文字以外前提を置かないとすると、スクリプト言語を使って連想配列に突っ込んで
最大値判定させて、最後に吐き出すっていう手順しか思い浮かばなかった。