タグ別アーカイブ: フィルター

【パワークエリ・チャレンジ】行ではなく、列方向でフィルター・置き換えを行う方法

【列方向ではフィルターは行えないと思っていませんか?実は3クリックをプラスすれば可能です】

Power Queryエディタ画面にて、空欄だけの列は処理したくない・と思ったことはありませんか?

実は「ヘッダーの上げ下げ」と「行列の入れ替え」を組み合わせれば可能です

上記のGIF画像では、空欄になっている「」「」の列をフィルターしています

ポイント

一番重要なのは2回にわたる「行列の入れ替え」です

行列を入れ替えれば、列方向のデータを行方向に変換できます

フィルター処理をした後は再度、行列を入れ替えます

処理の実際

行列を入れ替える前にまずは「ヘッダー行」を下げておきます

ヘッダー行を下げておくことで、見出しも2回にわたる行列入れ替え操作の対象となります

行列を入れ替えた後にフィルター処理により空欄行を除きます

フィルター処理をした後は、再度行列を入れ替えます

そのままだと、ヘッダーに本来配置されるべき列名が反映されていません

ですので、1行目をヘッダーに上げます

そうすれば完成です

<まとめ>

一見、列方向ではフィルター処理ができないように思えますが「行列入れ替え」処理を組み合わせれば実は簡単です

列フィルターが行えるだけで特殊なデータ整理が行えるようになります

今回紹介したのは空欄のフィルタでしたが、列名の置き換えなども一括で行えます

ぜひ実際に手を動かして試してみてください

最後に、ヘッダーの上げ下げを組み合わせることだけは忘れないようにしましょう!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

SORT関数によるエクセルデータの並び替え~列の並び替えも可

前回は1つの関数に対して複数の出力がある関数(ダイナミック関数)として、FILTER関数を紹介しました

今回は、同じようにダイナミック関数のひとつである、SORT関数を紹介します

エクセルで大量のデータを扱う時に、注意しなくてはいけないことの一つが並べ替えです

間違って並び替えると、列間で食い違いが生じて大規模なミスにつながります。SORT関数では並べ替えのキーが複数あっても、正確に且つ、簡単に並べ替えの処理が行えます

加えて、SORT関数では列の並べ替えが行えるのが大きな利点です!

以前、列フィルター機能を紹介しましたが、従来のエクセルでは列の扱いが困難だったのです

では、今回は、SORT関数の概要だけ紹介します!

1.複数の条件による並び替え

=SORT(並び替え範囲,{列位置,列位置・・・},{並び替え方法,並び替え方法・・・})

並び替え範囲/引数1を指定した後、並び替えの条件になる列の位置/引数2(例:{2,3})を指定し、更に並べ替え方法/引数3を指定します(例:{1,-1})

並べ替え方法/引数3で指定する1、-1とは、<1が昇順>、<-1が降順>の意味にになります

ちなみみに、引数2と引数3は指定の仕方がセットになっています

前述の例で言えば、引数2を{2,3}と指定し、引数3を{1,-1}と指定したので

・引数2の1番目(2)は引数3の1番目(1)とセット

・引数3の2番目(3)は引数3の2番目(-1)とセット

となります

実際の例として、以下の表/テーブル2を並べ替え範囲として、地域(列:2、昇順:1)、売上金額(列:3、降順-1)を並び変えてみます

元データ-テーブル2

数式は以下の通りとなります

SORT関数による並べ替え

SORT関数事例
SORT関数

2.列の並び替え

エクセルは行フィルター機能があるのが便利なのですが、業務によっては在庫表のようなクロス表形式で作業することがあります

以前から、列の並び替え機能もあると便利だとは思っていました

列の並び替えを指定する場合には、 前述の「1.複数の条件による並び替え」 で指定した3つの引数に加えて、4つ目の引数【列方向で並び替えを行う】をTRUEで指定する必要があります

ちなみに、「1.複数の条件による並び替え」にて、列位置で指定した 引数2は行位置で指定します

実際の例として、以下の表の3行目を並べ替え条件に設定し、降順にて列の並べ替えを行います

以下が数式です。4番目の引数をTRUEで指定しています

列並び替え
SORT関数ー列並び替え

但し、列の並び替えができるといっても元データ自体を並び替えできるわけではありません

利用機会を増やすには、別シートで並び変えた内容を編集して表示するなどの工夫が必要になります

ちなみに、FILTER関数と同じく、関数を入力したセル位置に「#」をつけることにより、出力データの再利用は行えます

下のGIFでは、SORT関数が入力された位置(F12)に#をつけることにより、表の右横にSORT関数の出力データを再出力しています

SPILL再利用
#によるデータ再利用

<まとめ>

今回はSORT関数により、データの並べ替えを行う方法を解説しました

SORT関数では、{}の文字を組み合わせることにより、複数の並べ替え条件に1(昇順)、-1(降順)をセットで指定することができます

後、SORT関数では列の並び替えを指定できるという特徴があります

こちらは使い方を研究して見て、ぜひ日常業務の中で活用してみてください

尚、実際のデータでSORT関数の動きを確認したい方は以下のファイルをダウンロードしてください!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

列フィルター

エクセルの便利機能の一つにフィルター機能があります

フィルターをかけさえすれば、誰でも手軽に検索が行えます

でも、列方向にフィルターを行うにはどうしたらいいでしょう?

実は、リストとIF文、そして、列を非表示にするVBAのhiddenプロパティを組み合わせて使えば手軽に実現できます

つまり、フィルターの対象でない列を非表示にしていくのです

以下、列フィルターを行うポイントをかいつまんで解説していきます

まず、フィルターをする対象の文字をリストで網羅して表示できるようにします

ちなみにエクセルの画面はR1C1型式にしていおいた方が便利です

次に、列フィルターを行う対象範囲の最終列を取得しておきましょう

 例:LastC = .Cells(7, Columns.Count).End(xlToLeft).Column

最後に、最終列まで繰り返しを行うFor ~ Next文の中に、If文とHiddenプロパティの”組み合わせ文”を入れます。

リストの文字と一致しない場合には、該当する列を非表示にするようにします

 例:If .Cells(7, i).Value <> str Then

     Columns(i).Hidden = True

   End If

たったこれだけで、最終列がはるか遠くまで膨らんだ表でも手軽に検索が行えます

最後に注意点ですが

フィルターを解除する際には、非表示になっている列は最終列として取得できません。ですから、”セルが空欄でない場合には表示する”などのLoop文で解除するなどの工夫を行いましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村