Excel。SORT。五十音順じゃなくて、決まった順番で並び替えを行いたい

Excel。五十音順じゃなくて、決まった順番で並び替えを行いたい

＜ユーザー設定リストの編集＋並び替え＞

先日、Excelの経験値が少ない方を教える講習で、並び替えをご紹介したあと、
担当者さんから、店舗名を五十音順じゃなくて、
ある決まった順番で並び替えを行いたいのですが、出来ますかねぇ？と聞かれました。

なるほど、確かに、五十音順じゃなくて、
その会社さん内で決まった順番があってその順番通りに並べたいということはありますよね。

で、聞いてみたのです。今どうしているんですか？と。

そうしたら、手動で並び替えているそうでして…

まぁ、通し番号を振っておいて並び替えを行ったほうが、
楽に希望のことが出来るとは思うのですが、そのようなケースばかりではないと思いますので、
今回は、五十音順じゃなくて、

決まった順番で並び替えを行う方法

をご紹介します。

下記の表があります。

今並んでいる、店舗の順番ではなくて、次の順番で並べたいとします。

新宿が本店という事もあって、最初になっていたりしますね。

まず、この決まった順番をExcelに覚えさせる必要があります。
この新宿本店～高田馬場店を範囲選択しておきます。

ファイルタブのオプションをクリックすると、
Excelのオプションダイアログボックスが表示されますので、その中の、詳細設定をクリックします。

下の方にスクロールすると、全般にユーザー設定リストの編集　というボタンがあります。

このユーザー設定リストの編集ボタンをクリックすると、
ユーザー設定リストダイアログボックスが表示されます。

すでに範囲選択をしておりますので、
$I$4:$I$9とインポートの左側のボックスに入力されているのを確認しましたら、
インポートボタンをクリックします。

すると、リストの項目に決まった順のリストが表示されて、
ユーザー設定リストの一番下に追加されたのが確認できます。

確認できましたら、OKボタンをクリックしましょう。

再度、Excelのオプションダイアログボックスに戻りますので、ここもOKボタンをクリックします。

これで、準備終了です。

では、並び替えを行っていきます。

今回は10行目に合計行がありますので、範囲選択をしませんと、
合計行も混ざって並び替えを行ってしまいますので、注意が必要ですね。

B3:F9まで範囲選択をして、

データタブの並び替えボタンをクリックしましょう。

並び替えダイアログボックスが表示されますので、
優先されるキーは、店舗
並び替えのキーは、値。
そして、順序は、昇順でも降順でもなく、ユーザー設定リストを選びます。

ユーザー設定リストダイアログボックスが表示されますので、
その中から、先程登録しました、決まった順番のリストを選択してOKボタンをクリックしましょう。

並び替えダイアログボックスに戻りますので、OKボタンをクリックしましょう。

これで、決まった順番に並び変わりましたね。

けど、やはり、通し番号とか、店舗番号とか設定して置いて、
それを基準として並び替えを行ったほうがいいとは思います。

Excel。Format painter。初心者がハマる罠。書式の崩壊！書式のコピーでリカバリーするしかない。

Excel。初心者がハマる罠。書式の崩壊！書式のコピーでリカバリーするしかない。

＜書式のコピー＞

新人研修の時や、職業訓練の時に、必ず紹介することがあるのですが、
それは、オートフィルオプションの書式なしコピーと書式のコピーなんですね。

で、なぜかというと、このポイントが、Excelで、初心者さん、
経験値が少ない方がハマる罠なんですね。

今回は、知っているよ。読む価値はないなぁ～と思うでしょうけども、
Excel初心者の方がお近くにいた時には、教えてあげてほしいことの一つなので、
書いてみようかと。

では、どのようなケースに罠がしかけられているのか？ということから確認してみましょう。

下記のような表があります。

この表をみただけで、Excelを使っている人だと、ああっ～。そういうこと。
って思われるでしょうけど、Excelの経験値が少ない方には、何が？と思うでしょう。

それでは、F4に池袋店の4月～6月の合計を算出しますので、オートSUMボタンをクリックして、
合計を算出します。

オートSUMボタンで合計を算出するのは、経験値が少ない方でもご存知の割合が高いですね。

そして、このようになりました。

そして、このあと、このF4の数式をオートフィルハンドルを使って、
F10まで数式をコピーしてみましょう。

オートフィルハンドルということ、なんのことか、ご存じでない方もいるかと思いますので、
F4のセルをクリックしてみると、右下にドッドがありますよね。

このドッドが、オートフィルハンドルです。
この上にマウスカーソルをもっていくと【＋】の形に替わりますので、
その【＋】のまま下方向にドラッグしていきましょう。

これで、数式がコピーされました。オートフィルハンドルは、
Excelの基本中の基本の操作方法ですので、覚えておきましょう。

で、これで、表も完成と思いきや、ここに、罠があるのです。

計算結果はいいのですが、一行おきにセルを塗りつぶしていたのが、消えてしまっていますね。

シマシマじゃなくなっている！

さらに、合計行とみてわかいやすいように、二重罫線を引いていたのですが、
なんとその二重罫線も消えている！

これは、F4の数式だけではなくて、
F4に設定されていた【書式】もコピーしてしまったからなんですね。

書式とは、セルの色とかフォントとかフォントサイズとか、罫線とか数式と文字以外のものです。
F4は、セルが塗りつぶしておりませんし、二重罫線もないので、消えてしまったわけですね。

では、どうするのか？というと、オートフィルハンドルを使ってコピーした直後でしたら、
右下に、オートフィルオプションが表示されていますので、▼をクリックしましょう。

その中の、書式なしコピーを選択してみましょう。

これで、消えてしまっていた、セルの塗りつぶしや、二重罫線が復活しましたね。

けど、このオートフィルオプションは、直後でないと表示されません。

別の処理をしてしまうと、消えてしまいます。

では、その時はどうしたらいいのでしょうか？
その場合は、書式のコピーを使って修復しましょう。

まず、範囲選択をして、ホームタブの書式のコピーをクリックします。
そして、F4:F10までをドラッグしましょう。

これで、書式が復活しましたね。

意外と、書式のコピーを知らない方がいますので、覚えておくといいですよ。
ということで、Excelの経験値が少ない方が陥る、罠。お気を付けのほど。

Excel。SORT。横縞を条件付き書式とMOD&ROW関数のコラボで設定してみよう。

Excel。横縞を条件付き書式とMOD&ROW関数のコラボで設定してみよう。

＜条件付き書式＋MODとROW関数と並び替え＞

前回、一行おきにセルの塗りつぶしをした行を並び替えしたら、

スプライト柄が崩れて、一行おきにセルの塗りつぶしでなくなってしまった。

ということを書きまして、テーブルにすればいいのでは、とご紹介したところ、
もうちょっと、違った方法を教えてほしいということでしたので、

今回は、以前書いたことがあります。
条件付き書式とMOD+ROW関数のコラボレーションで、対応してみましょう。

さて今回登場する2つの関数について確認しておきましょう。

MOD関数ですが、この関数は、割り算の「あまり」を算出する関数ですね。
この関数を使うと、偶数か奇数かを判断することが出来たりします。

5を2で割れば、あまりは1ですよね。4を2で割れば、あまり0ですよね。

ROW関数は、アクティブになっている行番号を教えてくれる関数ですね。
B5なら5という数値を算出してくれます。

この2つを使うと、今の行は、奇数か？偶数か？を求める事出来るわけですね。

そして、そこに、条件付き書式で、その関数の結果が1だったら、塗りつぶす。
ということを設定してあげれば、いいわけですね。

では、早速設定していきましょう。

B3:F9を範囲選択しましょう。条件付き書式は、書式なので、
先に範囲選択をしてから設定するのがいいでしょう。

ホームタブの条件付き書式の新しいルールをクリックしましょう。

すると、新しい書式ルールダイアログボックスが表示されます。

ルールの種類を選択してくださいは、【数式を使用して、書式設定するセルを決定】を選択して、
【次の数式を満たす場合に値に書式設定】のボックスをクリックして、
ここに数式を作成していきます。

関数の挿入ボタンとか使えませんので、手入力になります。
ボックスに、

=MOD(ROW(),2)=1

という数式を入力します。

この数式は、小文字でもOKですよ。

次に、書式ボタンをクリックしましょう。

セルの書式設定ダイアログボックスが表示されますので、塗りつぶしタブに移動して、
背景色からお好みの色を選択しましょう。

そして、OKボタンをクリックしましょう。

先程の、新しい書式のルールダイアログボックスに戻ってきますので、
OKボタンをクリックしましょう。

結果、表はどうなったのかというと、ちゃんと、一行おきにセルが塗りつぶすことが出来ましたね。

では、合計を降順で、並べ替えてみましょう。
F3をクリックして、データタブの降順ボタンをクリックしましょう。

結果は、

合計が降順で並びましたが、スプライト柄のシマシマ。一行おきに塗りつぶされていますよね。

これは、条件付き書式を設定したので、通常の塗りつぶしとは異なっているからなんですね。

アイディアによっては、3色に塗りつぶすことも出来ますので、
テーブルよりも汎用性があるかもしれませんね。

Excel。SORT。だからシマシマの設定はやるなっていったのに！ということを聞きまして。

Excel。だからシマシマの設定はやるなっていったのにということを聞きまして。

＜塗りつぶしと並び替え＞

前回、並び替えをする時に合計行があると、それも含めて並び替えてしまうので、
範囲選択が必要ですよと書きましたが、職業訓練やら企業研修とか、
色んな方々とお話しする機会の中で、これまた、
Excelの御経験が少ない方が親切心からやったことが、
【余計な事】になってしまうケースがあるそうでして、
今回はそのケースと解決方法をご紹介しようと思います。

それでは、下記の表をご覧ください。

このように、表がありまして、今回は合計行を作ってはいないのですが、
それぞれの行がわかりやすいように、一行おきで、塗りつぶしをしている訳ですね。

罫線を引くよりも、一行おきにセルを塗りつぶすほうが、わかりやすいですよね。

折角の表なので、わかりやすくしたかったという気持ちが含まれての処理なのでしょうから、
素晴らしい処理だと思います。

ただ、お話に出てきた、問題点というのは、このシマシマなのです。

「だから、シマシマはやるなといったのに…」とその方はおっしゃられておりましたが、
なぜかというと、この表を使って並び替えをしたデータで使いたかったそうでして。

では、実際に合計を降順で並び替えを行ってみることにしましょう。

なんとなく、想像できている方もいると思いますが、
F3をクリックして、データタブの降順をクリックしましょう。

すると、どうなったのかというと…

想像通りだったでしょうか？

合計の降順で確かに並び替えは出来ましたが、セルを塗りつぶした、
書式もいっしょに移動しますから、一行おきのセルの塗りつぶしがおかしくなってしまって、
【シマシマ】ではなくなってしまいましたね。

その方、どうしたのかというと、いったんセルの塗りつぶしを解除したそうです。

しかも今回は、6件ですが、もっとデータ量は多かったそうですから、
それは大変だったでしょうね。

そして、親切心から折角、セルの塗りつぶしを行ったのにもかかわらず、
逆に余計なことになってしまった担当者さんも可哀そうなわけですね。

なので、一行おきの塗りつぶしは気を付けたほうがいいわけですね。

では、簡単にシマシマでも並び替える方法はないのでしょうか？

条件付き書式+MOD関数&ROW関数というテクニックもありますが、
初心者向けだとすると、テーブルを使う方法がいいのかもしれませんね。

では、テーブルにしてみましょう。

B3をクリックして、挿入タブのテーブルをクリックしましょう。

すると、テーブルの作成ダイアログボックスが表示されますので、OKボタンをクリックしましょう。

これで、テーブルになりました。

では、並び替えをしてみましょう。
F3の合計にある、フィルターの▼を使って、降順に並び替えをしてみましょう。

フィルターの降順を選択して、OKボタンをクリックしましょう。

このように、ストライプのシマシマは維持されたまま、並び替えが出来ましたね。

もし、会社にExcelの初心者さんがいた時には、教えてあげるといいですよね。

Excel。SORT。並び替え。よく引っかかる罠にご注意ください。

Excel。並び替え。よく引っかかる罠にご注意ください。

＜並び替え＞

Excelの講習や講義などの質問コーナーでよく登場するものの中に、
【並び替え】がありまして、並び替えの処理自体を知らないのではないのですが、
こういう場合、うまく並び変わらないのですが、どうしたらいいですか？というもの。

そして、この質問。Excelの初心者の方に限らず、お困りの方が多いようですので、
今回は並び替えに潜む罠について、お話ししましょう。

まず下記の表があります。

これを、合計の大きい順。すなわち降順で並び替えてみましょう。

F3の見出しの合計をクリックして、

データタブの降順をクリックすれば、降順で並び変わりましたね。

で、このどこに罠があるのか？と思われたでしょうが、このような表だったら、いいのですが、
初心者の方がよく作って困っちゃうケースは、
実はこのような表の場合なのです。

先程と何が違うのか？というと、10行目に合計行がありますね。

先ほどは合計行がありませんでした。

この一行が、罠なのです。

では、F列の合計列を、先程と同様に、合計の降順で並び替えてみましょう。

F3をクリックして、データタブの降順をクリックしてみると、

なんということでしょう！

めちゃくちゃな表になってしまいましたね。

原因は、10行目に合った合計行なのです。

昇順や降順。
あるいは、並び替えを実行したとしても、合計行を含んで並び替えをしてしまうので、
当然合計が数値は、どの値よりも大きかったので、
合計行が一番に行ってしまったという訳です。

ではどのようにしたら、並び替えることができるでしょうか？

一番簡単なのは、合計行を除いて範囲選択をして、並び替えを行うという方法があります。

この範囲選択で注意しなくてはいけないのは、
並び替えを行いたいところだけを範囲選択して実行してしまうと、
データがバラバラになってしまいます。

ですから、範囲選択は、下記のようになります。

B3:F9までを範囲選択します。見出し行はいれますが、合計行は含みません。

そして、並び替えですが、降順のボタンは使えません。

この状態で使ってしまうと、一番左端の店舗が降順で並び替えを実行してしまいますから、
データタブの並び替えをクリックしましょう。

すると、並び替えダイアログボックスが表示されますので、

列は、合計。
並び替えのキーは、値。
順序は、降順。
に設定したら、OKボタンをクリックしましょう。

これで、合計行はそのままで、綺麗に合計が降順で並び替えすることが出来ましたね。

このように、並び替えはボタン一発で処理できる場合もありますが、
合計行が邪魔をすることがありますので、ご注意ください。

ちなみに、降順のボタンでも並び替える方法はあります。

それは、データと合計行の間に空白行を挿入する方法です。

Excelは隣接したセル同士が認識して、表という単位になっていますので、一行挿入すると、
合計行は表の一部ではなくなりますので、並び替えることが出来ますよ。

下記には、一行挿入した表があります。

では、F3をクリックして、降順をクリックしてみましょう。

確かに、並び替えを行うことは出来ましたが、これはこれで、表がおかしくなりますので、
ちょっと考えどころですね。

Excel。Macro。いちいち、入力欄を削除するのが面倒！そこで、マクロを使ってみよう03回

Excel。いちいち、入力欄を削除するのが面倒！そこで、マクロを使ってみよう03回

＜VBAでメッセージボックス＞

いちいち、単純な作業をやるのは面倒なので、
マクロを使って作業効率を上げようと書き始めましたこのシリーズ。

前回は、メニューからマクロを選択して実行するのは面倒なので、
ボタンにしてマクロを実行したほうが、楽じゃないのかな？

ということで、ボタンを作ってマクロを登録したところまで作りました。

ただ、この「データの消去」というボタン。

クリックすると、あっ　という間に処理はしてくれるのですが、
間違えて押しても、実行しちゃうわけです。

そこで、今回は、間違えて押したときでも大丈夫なように、マクロを修正して、
ワンクッションいれて、マクロを実行できるようにしてみましょう。

さて、マクロを修正するには、VBEをつかって、マクロそのものである、VBAを修正する必要がありますので、VBEを起動してみましょう。

開発タブのマクロをクリックして、マクロダイアログボックスを表示しましょう。
変更修正したいマクロを選択して、編集ボタンをクリックしましょう。

すると、VBEが起動します。

VBAの構文そのものに関しては、いずれ書こうと思っている、
VBAのところで書くとして、今回は、加筆修正をしていきましょう。

Sub 消去()　　　　これは、マクロ名ですね。
'
' 消去 Macro

    Sheets("請求書完成").Select
の前に下記の一行を追加します。

    If  MsgBox("実行しますか？", vbOKCancel) = vbOK  Then

と入力しましょう。

この文の意味は、メッセージボックスを表示させて、OKだったらば、実行そうでなければ、何もしないという、分岐の処理をすることが出来るようになります。

そして、最後の

End Sub

の前に
    End If
と入力しましょう。
これで、IF～END IFの間を実行する？しない？と選択できるようになりました。
では、上書き保存をして、VBEを閉じましょう。

それでは、マクロが登録されている【データの消去】ボタンをクリックしてみましょう。

なんと、マクロがすぐに実行されずに、メッセージボックスが登場しましたね。

そして、コメントの「実行しますか？」というのは、先程の
    If  MsgBox("実行しますか？", vbOKCancel) = vbOK  Then
の実行しますか？という文字が表示されているので、
この「実行しますか？」という文字を変更すれば、
オリジナルのメッセージボックスを作ることができます。

それでは、キャンセルボタンをクリックしてみましょう。

マクロは実行されませんでしたね。
これで、すぐに実行されることはなくなり、さらに作業の精度がアップしましたね。

では、もう一度ボタンをクリックして、今度はOKボタンをクリックしてみましょう。

今度は、マクロが実行されて、データが削除されましたね。

このように、せっかく作ったマクロをちょっとアレンジするだけで、便利になりますので、
ゆくゆくは、マクロ⇒VBAへとテクニックを習得していくといいですね。