Excel2013対応

第２回の最後に問題出しましたね。 「注文番号がダブってたら、商品名、および価格を比較して、もし差異があったら、その箇所がわかるようにしてほしい」 というものでした。 前回は重複の削除・解消をこちらで行いましたが、今度は、どれを残すか上司の方でジャッジする、ということなんでしょう。 だから、データの削除はしません。むしろ、削除の指示を仰ぐための資料を作る、というイメージです。 いずれにせよ、けっこう複雑なオーダーですね。 下↓のExcelファイルを使って、この問題、やっつけていきましょうか。

「え？第２回でもダウンロードしたよ？」とおっしゃる方もいると思いますが、もう一度お願いします。 というのも、第２回のときのデータ（narabekae_01.xls）は、重複データに差異がないよう作ってあるので、 この問題には適さないんですよ。 もちろん、重複データに差異がない方が本当は良いですし、 重複なんてそもそもないのが一番なんですけどね。 ただ、差異を割り出す練習をしていて、差異がなかったら、張り合いがないでしょ？ なので、お手数ですが。

さて、どう対処しましょうかねぇ。
countif関数で重複数を割り出して、そのうえで、重複データが上下に並ぶように並べ替えましょうか。 隣り合わせの方が、データ、比較しやすいですもんね。 ま、要するに、途中までは其ノ２と同じってことです。

まず、其ノ２の手順を思い出しながら、countif関数を入れてください※１。 検索する範囲は「注文番号」列（A列）全体、検索する値は１コ目の注文番号（A2セル）でしたね。 あ、（画面のように）注文番号列のとなりに１列挿入した場合はセルの書式が「文字列」になっていますので、「標準」にしておいてください。 そうでないと式の計算結果が表示されないんでしたね。 後は、式をコピって、おしりまで貼り付ける、と。

式を入れたら、値貼りしておきましょう※２。 列全体をコピーして、同じ場所に、右クリックで「形式を選択して貼り付け」→「値」でしたね。

ここまでできましたら、並べ替えを行う前に、「No列」を作成しておいてください。

では、並べ替えましょうか。
前回は、注文番号昇順で並べ替えました。オーダーは１コだけでしたね。 けれども今回は、もうひと手間加えましょうか。
まず、並べ替え画面にして、最優先するキーを…って、おおっと！範囲選択は大丈夫ですよね？ 例によってこのExcelも１列空いてますので、範囲指定をきっちり、一番右の「入力日」まで含めてお願いします。
話は戻りまして、並べ替え時の最優先するキーを今回は「重複数」にします。昇順ね。
そして、「重複数」昇順のなかで、さらに「注文番号」順に並べたいので、 並び替えレベルを追加します。「レベルの追加」を選択※３してください。

レベルを追加すると、追加オーダーが選べるようになるので、「注文番号」を選択※４。

＜この機会にちょっとお話＞
このサイトに出てくる画面はすべてExcel2007です。それは、筆者のがたまたまExcel2007だからです。 だから、操作の解説はどうしても2007を前提としてしまいます。上の並べ替えの画面なんか、Excel2003までとだいぶ違いますよね。 2003だと、「最優先されるキー」「２番目に優先されるキー」だったかな？
けれども、もちろんですが、私はExcel2007ユーザーだけに向けて発信しているのではありません。 2003ユーザーにも、2010ユーザーにも役に立ちたい。
たしかに、Excelは各バージョンによって見た目が変化します。特に、2003→2007ではそれが顕著でした。
けれども、変わったのは主に見た目です。本質は変わってない。
2003/2007/2010の違いを解説しているサイトは、他所にたくさんあります。
私は、バージョンが変わっても、変わらない大事なことを、このサイトで語っていければ良いなあ、と思っていますが、 Excel2007のリボン配置や操作感で解説する箇所がどうしても出てきてしまいます。 なので、2003ユーザーの方や2010ユーザーの方は、そういう箇所を、自分のバージョンに適宜読み替えていただきたく。 お手数ですが。

並べ替えた結果です※５。

こうすると、重複数「1」／「2」／「3」で大きくまとまり、その中で ダブった注文番号が上下隣り合わせになります。
確認しておいてください。

さて、「商品名」と「価格」の差異の割り出し、どうやりましょうか。
みなさんもちょっと考えてみてくださいね。
　・
　・
「よし！目で探そう！」………繰り返しますが、私は根性のある人が大好きです。 それに、これぐらいの件数なら目で探してもそんなに時間はかからないでしょう。 でも、どうしても人間の目だと、微妙な文字の違いや数字の違いを見落とすかもしれません。 「3」と「8」って結構見間違えるんですよ。これホント。 なので、機械的に割り出したいところです。

では、どうしましょうか。
差異を割り出すのだから、其ノ２でも使ったイコール式を使いましょう。
前回は１コ上のセルと比較しました。「TRUE」のところが、ダブった注文番号の２コ目以降、というフラグ（しるし）でしたね。 今回も同様に、１コ上のセルと比較してみましょうか。 差異のないところは、ダブった２コ目以降が「TRUE」に、 もし差異があれば、ダブった２コ目以降は「FALSE」になるんだけど…とりあえずやってみましょう。

最初に商品名の比較から行きましょうか。 まず、「商品名」列のとなりに新しい列を挿入してください※６。項目名を私は「←差異」としました。

その列にイコール式を入れていきます。
けれども、重複数「１」のゾーンは無関係なので、すっ飛ばしましょう。 ダブってないので、比較のしようがないですから。
重複数「２」のところから式を入れていきます。 １コ上のセルと比較する式を入れてください※７。

式、入れられました？「イコール上」ですよ、「イコール上」。
式を入れたら、コピーして最終行まで埋めてください※８。

式を入れました。
さて、差異の判別でしたね。重複してるデータの１コ目に関しては、式の結果は関係ありません。 問題は、２コ目の結果です。重複データの２コ目以降の式の結果が「FALSE」なら、差異アリです。
その箇所を、目で探していく…あれ？結局目で探すことになっちゃったなー。 まあ仕方ない。ここまで来たら乗りかかった船だ。差異のあるデータを探して色を塗ってみました※９。

価格についても同様のやり方で割り出してみました※10。

どうでしょうか。
悪くはないんだけどなぁ…。 もちろんこのやり方でもまちがいではないんですよ。 でも、もうちょっと他にやり方、ないもんですかね。

そもそも、「２コ目以降がFALSEのところ」って、結構複雑な条件ですよね。 しかも、TRUE／FALSEでごちゃごちゃしてるところを「目で探す」わけですから、 ミスが入り込む余地はかなりありそうです。 そこをもうちょっとスマートにできないかな…。

というわけで、いったん※５のところまで戻りましょうか。
そうです、countifで重複数出して、重複数と注文番号をキーにして並べ替えるところまでです。 戻れました？※11

↑画面では、「商品名」列と「価格」列のとなりに「←差異」列を作ってあります。

さて、やり方を変えようと思うのは、差異の割り出しのところです。 やっぱりイコール式を使うのですが、式の入れ方にちょっと工夫をしましょう。 重複数「２」の始まるところに式を入れるのですが、今回は下のセルと比較します。 イコール下です※12。 ちょうど、同じ注文番号の２商品を比較するかたちになります。

式、入れられました？「イコール下」ですよ。
式を入れたら、その下のセル、すなわち重複してる２コ目のセルは空白のままにしておいてください。 そして、上下２セルとも選択します※13。 上は式の入ったセル、下は空白ですよ。

選択したら、選択範囲の右下のあたりにマウスを持って行ってください。ポインタが十字型になるはずです。 そうしたら、下方向にドラッグします。下セルが空白のままです※14。 重複数「２」のゾーンのおしりまで引っ張ってください。※15

できました？

下セルと比較する式
空白
下セルと比較する式
空白
　・
　・
　・
と、式と空白セルが交互に並ぶと思います。
なんだか、余計な情報がなくなって、見た目すっきりしましたね。 しかも、先ほどのは「重複してる２コ目にFALSEのあるところ」っていう複雑な条件を探しに行きましたが、 今回のは単純にFALSEのところに差異があるわけです。目で探すにしても、ずっと楽ですよね。

続いて重複数「３」です。
ここは、比較対象が３つあります。わかりやすくするために、上からA・B・Cとしましょう※16。

もしここで、AもBもC もイコールなら、問題ないんですよ。 でも、=(A=B=C) という式では、ダメなんです。機能しないんです（ちゃんとした理由はよくわからないですけど…おそらく同時に２つのイコールを処理できないのではないかと思います）。
A・B・C、３つともイコールかを調べるには、ひと工夫して、

A=B かつ A=C

という式の入れ方をしてあげる必要があります。
この「かつ」の部分、ExcelではAND関数で表現します。 ２つ以上の式を格納できる関数で、格納された式が全部TRUEなら「TRUE」を、どれか１コでもダメなら「FALSE」を返します。
今回のケースにあてはめると、

=AND(A=B , A=C)

となります。実際に式を入れてみましょう※17。

この式を入れると、A・B・C、３つともイコールなら「TRUE」、１コでも違うのがあれば「FALSE」になります。 結果はどうなったでしょうか？※18

どうやら差異があるみたいですね。
とりあえず、残り２つのセルは空白のままにしておいてください。
そして、式を入力したので、値貼りしておきましょう。列全体を選択して、「形式を選択して貼り付け」→「値」ですよ※19。

さて、式を入れ終えました。
現状、重複してる上側のセルに判別の式の結果がTRUE／FALSEで表示されています。 さきほどと同様に、FALSEのところを目で探して色を塗っても良いんですが、もうひと手間加えたいと思います。 差異のあるところだけ、フィルタで抽出できると良いな、と思いまして。 そのために、ジャンプという機能を使います。
案ずるより産むが易し。やってみましょう。
まず、F列（「←差異」列）を選択します※20。

範囲選択しました？ジャンプは、必ず範囲選択してから行ってくださいね。
次に、ctrlキーを押しながらGを押してください。ctrl+Gです。 すると、「ジャンプ」というウインドウがでます。 その中の、「セル選択」を選んでください※21。

すると、「選択オプション」というウインドウが出ます。 そこで、「空白セル」を選択※22　※23。

こうすると、指定範囲内の空白セルを全部選べるんですよ。
…「だから何？」って顔、しないでください。
えーと、何がしたいかっていうと、現状、ダブリの上側にTRUE／FALSEが入っているじゃないですか。 もし、ダブリの下側（現状ブランク）のところにも上セルと同じ結果（TRUE／FALSE）が反映されれば、 フィルタでFALSEだけを抽出できる＝差異あるところだけピックアップできる、と考えまして…。
あ、ジャンプの前の範囲選択、忘れないでくださいね。範囲選択しないと、 シート内の全部の空白セルが選ばれちゃいますから。

さて、話は戻りまして。 ジャンプができましたら、イコール上と入れてください。
あ！まだenterは押さないでくださいね※24。

式を入れたら、ctrlキーを押しながらenter！ctrl+enterです。 すると、ジャンプで選んだセル、すなわちF列の空白セルすべてに、イコール上の式が入ります※25。

空白セルが埋まりましたね。式を入れたので、とりあえず値貼りしておいてください。
…重複数「１」のゾーンに「←差異」と入ってしまったのはご愛嬌。
ですが、こうすると、「FALSE」のところ＝差異のあるアイテム、ってことになるんですよ。
ジャンプ前までは、ダブってる１コ目だけに判別の値（TRUE／FALSE）が入っていました。 ダブりの２コ目以降は空白でした。 だけど、ジャンプしてイコール上を入力することで、１コ目と同じ判別の値（TRUE／FALSE）が入る。 そうすれば、フィルタで「FALSE」を抽出でき、ダブリで差異のあるアイテムだけ見ることができます。
…なんだかことばで説明するとむずかしそうに見えちゃいますね。 ただ、こういうロジックを組み立てるのって、経験が物を言うところがあります。 そして、今、経験、したじゃないですか。 僕のこのサイトって、経験してもらうためにあるんですよ。 手を動かして、失敗して。練習なんだからいくらでも失敗できます。 そうやって、経験値を積んでもらって、Excel力をつけてもらえたら良いな、と思っています。 なので、チャレンジしてみてください。

さて、話は戻りまして、もうひと工夫しようかな、と。 というのも、現状「FALSE」のところ＝差異のあるアイテムなんですが、 「FALSE」ってあんまり一般的なことばではないですね。 上司には伝わらないかもしれません。なので、「FALSE」を「×」とかわかりやすい表現に変えましょうか。
フィルタで抽出して「FALSE」→「×」に変えておいてください※26。

ということで、差異のあるところだけフラグ（しるし）を立てることができました※27。

価格についても同じようにしてください※28。

ハイ、できました。フラグの立った箇所について、どこがどう違うのか、確認しておいてください。

ちなみに、XH4165の商品名「レポートパッド B5 A罫 7㎜ 28行」ですが、 一見すると商品名同じみたいなのに、「×」になっています。
でもこれは、僕たちの計算が間違ったわけではないんです。
どちらか一方の商品名のおしりに、余計なアキスペースが入っていませんか？ セルの中に入って確かめてみてください。
イコール式は、そういうところまで検出してくれるんです。