MAX関数を使わずに最終販売日などの最大値を抽出する簡単な方法。

MAX関数を使わずに最大値のデータを抽出する方法をご紹介します。今回は「各取引先の最終販売日と商品名のデータを抽出する」方法です。

記事に書くと長くなっちゃいますが、慣れたらものの5分で出来ますよ。

実践の場では、最終日とか最大値をとる作業はよくあります。

各取引先の最終販売日だけを知りたいのであれば、Excelで一番早いのはピボットだと思います。ピボットで取引先別で最大値をとる、Accessならクエリで販売日の最大値をとるなど、色々方法があります。

f:id:yururimaaruku:20171108053623p:plain

だだしここでは、その時売れた商品名も一緒にデータ抽出をするという想定でいきます。もとのデータ形式やデータ量にもよりますが、以下のExcelを使った方法が簡単でおすすめです。

手順

考え方

1.データのソートをかける(販売日の降順)

2.F列にsumif関数を入力

3.最下行までコピー

4.フィルタを設定する

5.F列が1のものでフィルタする

取引先CDの昇順、販売日の降順でソートして、sumif関数を使って最初に出てきたデータをピックアップします。

操作方法

データのソートをかける

データを販売日の降順に並べ替えます。(データ⇒並べ替え)

※取引先CDの並べ替えは無くても出来ますが出来上がりが見やすくなるので含めています。

f:id:yururimaaruku:20171108054837p:plain

F列にsumif関数を入力

セルF2に入力した関数は「=COUNTIF($B$1:B2,B2)」です。範囲の中で検索値と一致する値がいくつあるか調べるものです。範囲の開始セルを固定(絶対参照)している点がポイントです。

f:id:yururimaaruku:20171108055238p:plain

これを次の行にコピーすると・・

f:id:yururimaaruku:20171108055633p:plain

開始位置だけが固定しているため、コピーするだけで選択範囲が伸びて、一行目からその行までのでいくつあるかをカウントする事ができます。

最下行までコピー

このまま最下行までコピーします。分かりやすいように取引先ごとに色をつけました。F列には取引先名の1行目からの出現数がカウントされました。

f:id:yururimaaruku:20171108055904p:plain

フィルタを設定する

フィルタを設定します。

※1行目をクリックして、データ⇒フィルタをクリック。

f:id:yururimaaruku:20171108060118p:plain

F列が1のものでフィルタする

1で抽出します。もともと販売日の降順で並べていたので最終販売日のデータのみが抽出されます。以上です。

f:id:yururimaaruku:20171108060210p:plain

実践の場ならもうこのまま印刷して提出しますし、何か表の形に整えるのであればこのデータをコピーして別のシートで整えます。

私ならいったんこの状態で確認をとりますね。何にデータを使うのかによって作業は変わります。パワーポイントにはりつけて発表するとか、別の報告書に貼り付けるとか。

あまりやり切ってしまわないのもコツだと思います。7割、8割ぐらいの出来で確認をとる。依頼した人と意思疎通をこまめに取りながら作業をすすめることで無駄を省くこともできます。

応用

初回販売日で抽出する

初回販売日で抽出するには、手順1の並べ替えの時に販売日を昇順に並べ替えておきます。つまり並び順を逆にしておきます。小さい数字、初回の日付が先に出てくるのでカウントで1が付与されます。これで初回販売日が抽出できるようになります。

応用すれば、最後から2番目の販売日なども抽出可能ですね~。

取引先別かつ商品別の最終販売日を抽出する

この方法ならキーが複数あっても対応できるのですよ。例えば「取引先別で更に商品別の最終販売日を抽出する」など。簡単、簡単。

キーになるフィールドをつくり、それをカウントします。

この場合F2に「=B2&D2」を入力します。&で文字列をつなぐことができます。この&はよく使います。

f:id:yururimaaruku:20171108061113p:plain

キーの昇順、販売日の降順で並べ替えします。

f:id:yururimaaruku:20171108061330p:plain

はい、同じく下までコピーです。ちなみにコピーする時はセルの右下の十字マークをダブルクリックですよ。

f:id:yururimaaruku:20171108061530p:plain

取引先別、商品別の最終販売日のデータが抽出できました。条件2つでしたが、キーでつなげば3つでも4つでも出来ますよ。取引先別の商品別の担当者別とか・・。どこまででもどうぞ。

f:id:yururimaaruku:20171108061742p:plain

* * *

こんな具合に色々応用できるのでこの方法が一度覚えておくと便利です。

高度な関数を使った方法じゃなくてスミマセン(笑) もちろん関数分かりますよ。

そいうのを方法もありますが、うーん、別にこれでいいんじゃないかな。関数だけを使ってやるのは、データが変動するごとに自動的に抽出するようにしたいとかそういう場合はありかもしれないけど、実践ではあまりそういうケースはないです。もちろんあれば別の方法を使いますが・・。

20年以上色んなケースでやってきましたけどね。

この方法は時間もかかりませんよ。慣れたら「これできる?」って聞かれてから多分10分?いや、5分以内で出来ます。以前はExcelの処理速度が遅くて、そうですね・・、10年ぐらい前かな、2000行ぐらいまでしか実用に耐えない速度だったのですが、今はそこそこ業務で使うマシンなら40000万行ぐらいでも使えています。そこまでいくとホントならExcelより他のソフトを使うのが良いかもとは思いますが、そんなことも言ってられないぐらい急いでいることもあるのです。

実践の場ではこのぐらいがパパっとできたら、相当役に立つし他の人にも分かりやすい。出来上がり品が分かりやすいのです。フィルターを展開したら元のデータも見やすくなっているでしょう。関数だけで仕上げたらまるでブラックボックスですからね。

できるだけシンプルな方法を使うのがおすすめです。

理想的には、ExcelでもAccessでも他の方法でも出来る、色んな手数を持っていて時々に応じて、ベストなものを選択できるのが一番良いです。

シンプルな方がExcelを楽しめる人が多くなると思うんです。

* * *

このブログ、久しぶりに書きました。前回の日付を見てびっくり。そろそろ5か月が経つじゃないですか。今回はTwitterで流れてきた例題が気になったので、私だったらこうするなと思って書きました。

また日常で面白い例があればご紹介しますね。

ではまた。