excel関数 間違ったデータをピックアップしたい

質問

列Aの各セルに、みかｎ、りんご、バナナ、メロンなどと入っていて、列Bの各セルは、"A"～"F"の人気ランクが入っています。

列A　　　列B　　
みかん　　A
りんご　　C
バナナ　　D
みかん　　A
メロン　　C
列C以降にはその他の色々なデータが入っていますが、それはさておき、列AとBだけの関係を見ると、みかんは必ず"A"、りんごは必ず"C"というように、果物別の評価は決まっているはずです。ところが、間違って入力されて、ある行ではみかんが"A"で、別の行ではみかんが"C"だったりしたら、調べて正しい方に統一したいのです。
そこで、ひとつの果物に対応する評価が複数あるものをピックアップしたいのですが、どうすればいいでしょうか。そのほかの集計作業に関数を使っているので、これも関数で出来ればいいかなと思うのですが。

回答No.7

imogasi · ベストアンサー

#4です。
注意！！
条件付書式で、私の言う方法は、「値が」で、条件を指定してはいけません。
「値が」の見えている右の▼をクリックし、「式が」を選び、出てきたテキストボックスの中に、私の回答の式を貼り付けてください。
その後セルのパターン（薄いタイプの）色を指定し、ＯＫをクリック。

お礼

なんと、すぐできました！どうもありがとうございました。大変助かりました。

回答No.6

Wendy02

#5 です。

私の書いたことに不快感を持たれたのでしょうか？

>専門家が作ったおおもとの立派なデータがあり、私はそれを拝借して自分の部署に必要なデータを作っているまでです

なぜ使えないのか良く分かりません。

そのデータ利用をしないで、それ以上の話はないと思います。マスターデータがあって、それを元にして修正していく、というのは、ごく自然の成り行きです。外部に問題を求めても、結局、表面的な問題を触る程度しかないように思います。

それに、「格付けとか」いうのは、たぶん、そのマスターを使うことを考えずに、「演繹的」にデータを修正しようということだと思いますが、私には、理解できないです。今、出てきた回答で、ある程度の目安が立たないとすれば、回答者さん自体の技術力にも疑問が出てきてしまいます。

昔、私自身データベースは扱ってきて、個々のデータの「揺れ」はありました。そうすると、統計が出来ないので、それは、#1さんの書いていた似たような方法で直しました。数千個なんて、数日もあれば直してしまいます。当時は、20年ぐらい前は、それでもやむをえなかったのです。

マスターを使えないというのは、本来は、会社全体のシステム事態に問題がある可能性があります。そういう会社もあることはありますが、そういう場合でも、個々の担当者がマスターを時間をみて作っていくものです。

数千個のレベルなら、器用な人なら、数日あれば出来ます。そうすぐに問題が発生するわけでもないし、発生しても、人為的に修正は可能なら、それは同時進行していくしかありません。

もちろん、この本来の処理は、データベース系で、表計算ではあまりうまく行かないように思いますが、その場合は、CountIf やVlookUpを条件付き書式に埋め込みます。

>あくまでこの質問を読んで何かアイディアがある方がおられましたらお願いします。

ここで継続的に利用して質問していくつもりでしたら、お礼もしないで、そういうレスをするのはやめてくださいね。ダメならダメで構わないのですが、こちらの体験を踏まえて、質問者さんの問題をいろいろ考えて書いているのですから、もう少し言葉を考えてください。

回答No.5

Wendy02

こんにちは。

ベースとなるもの（マスター）を作らなければ、その先の自動化などという話にはありえないのではありませんか？

もともと、最初に、それがないというのが失敗（と言ってよいのか分かりませんが）の原因ではないかと思います。どんな入力リストでも、業務用の場合は、最初に表計算ソフトなどでマスターを作ります。それでないと、間違いが見つからないし人にも頼めないのですから。

後先になっても、そのマスターを作る方法を考え出さなければ先に進みません。

>別途正しい表を作るというのは無理で、「複数の格付がついている果物をピックアップする」という作業がやはり必要に思います。

複数の存在する「揺れ」を一覧リストを作るというのでしょうか？
こちらはどのようなリストか分かりませんが、最初に時間を掛けてでも、自分の手でマスターを作るつもりはない、と読めます。その入力データに一定の基準がないようにも思います。それを後になって直すというのは、いずれにしても大変だというしかありません。

それと、「格付け」という意味が分かりませんし、まさか、ランキングをつけるという意味だとすれば、当面の目標と最終目標と、二本立てして質問者さん自身がこうだと決めていかなければ、先に進めないと思います。

私の考えた方法は、

簡単に説明すると、
・リストには、タイトル行を加えていただくこと。
○　C列に、 =A2&B2 で、つなぎ合わせたものを出し、
・それをフィルタ・オプションで、重複するデータを省く
・= Countif(C列, C2) で、どの程度の数があるか出し、それで、
・ユーザー自身が、印を入れる
・印を入れたものだけを、残し、それを「マスター」にする。

○で、統一したものを作るのです。

後は、関数ではじき出したり、条件付書式で色をつけたり、マクロで自動修正してもよいです。

私個人は、これを全部、マクロで作ることも可能ですが、今の段階では、その問題に対する対処法がきちんと組み立てていないような気もしますので、それはやめておきます。

補足

失敗してませんよ（笑）。
専門家が作ったおおもとの立派なデータがあり、私はそれを拝借して自分の部署に必要なデータを作っているまでです。
詳しく詳細を御説明すると長くなりますので御容赦下さい。あくまでこの質問を読んで何かアイディアがある方がおられましたらお願いします。

回答No.4

imogasi

一番早いのは、Ａ列＋Ｂ列（＋とは両方をこの順序でソートキーに指定すること）でソートし、Ａ，Ｂ列を目視でチェックすることです。
２０００行でも５分で終わります。
品名と評価クラスのうち種類の多いほうを第１キーにすると、チェックがやりやすい
Ｘ　Ｓ
Ｘ　Ｓ
Ｘ　Ｓ
Ｘ　Ｑ＜－－この異類のものを見つける
Ｙ　Ｒ
Ｙ　Ｒ
この目視チェックをやりやすくするには
Ａ１：Ｂ１は避けて
Ａ２：Ｂ列最終行を範囲指定し
書式ー条件付き書式ー式が
=AND(A1=A2,B1<>B2)
セルに薄い色をつける（セルパターン）
ーー
関数ではＶＬＯＯＫＵＰ関数で規定の商品ー評価の組み合わせ以外は指定できると思うが、改めてその組み合わせテーブルを作る手間がかかる。現データには間違いの組み合わせもありうるのだから、現データから作るのも手間ですから。

お礼

ありがとうございます！このようなご回答を待っていました。
やってみました。列AとBを選択し、
条件１（１）のそれぞれの欄に、「セルの値が」「次の値に等しい」「=AND(A1=A2,B1<>B2)」と入れたのですが、関係無い（説明文が書いてある）セルが着色され、肝心の商品名・ランクは着色されません（わざとランクが複数ある商品を用意してみたのですが）。
どこが違っているのかお分かりだったら教えて頂けるとありがたいです。
いずれにせよ参考になりました。

回答No.3

mu2011

■案１
・データシートに行見出しがある事（無ければ見出し行を作成して下さい）とし、期待ランク以外を抽出します。
・又、元データを訂正する為に一連番号列があると分かり易いと思います。
（１）別シートのA1,B1にデータシートと同一の見出し名を設定
（２）別シートA2に「みかん」、B2に「<>A」を設定
（３）別シートC1でデータ→フィルタオプションの設定→警告ボックスが表示されますがＯＫ
（４）抽出先を「指定した範囲」にチェック
（５）リスト範囲欄にデータシートの対象範囲を選択
（６）検索条件範囲欄に別シートのa1:b2範囲を選択
（７）抽出範囲欄に別シートのc1を選択→ＯＫ
■案２
・相違している行を色付けする。
（１）データシート範囲を選択
（２）書式→条件付き書式
（３）「数式が」を選択、数式欄に=($A1="みかん")*($B2<>"A")
（４）書式→パターンタブで任意色を選択→ＯＫ
　上記を品名分繰り返す

お礼

ありがとうございます。
「期待ランク以外を抽出」ということは、たとえば「みかん」＝「A」等と決めないとならないんですよね。下記お礼等の事情により、それは無理なのです。
また、品名は何百とあるので、品名分繰り返すのも無理です。それをするくらいなら、私が今までやったように、品名をフィルタにかけ、ひとつずつ表示させ、複数の評価格付がないか調べる方がまだ楽と思います。
せっかくなのにすみません。
でも、詳しい方々に御意見を頂いた上でだめならあきらめがつきそうです。

回答No.2

suekun

修正と考えず、正しい物を再設定されてはいかがですが？
B列に対して、別にそれぞれの項目毎の対応表を作り
VLOOKUP関数を設定して、対応表から反映させる。

みかん：A
りんご：B
バナナ：C

と言う様に、対応表で正しく設定しておけば、あとは項目（品名）を
検索して対応表から付随する項目を引き出します。

仮に作った対応表の範囲がD1:E10で、品名が一行目からだとすると、
=VLOOKUP(検査値　対応表範囲　返す値の列番号　検索方法）
ですから、
=VLOOKUP(A1,D1:E10,2,0)

このままでは下にコピーすると対応表の範囲が変化するので、
=VLOOKUP(A1,$D$1:$E$10,2,0)

として、後は下にコピーするだけです。

お礼

ありがとうございます。
簡単に説明する為に「正しい」と書いたのですが、正しい/間違いではなく、ある時点ではAだったのに、別の時点ではBに下がった、というようなことがまれにあるのです。そういったものをピックアップして、低い方に統一します。ですので別途正しい表を作るというのは無理で、「複数の格付がついている果物をピックアップする」という作業がやはり必要に思います。
まだまだうまく説明できていないと思います。すみません。

回答No.1

gookkj

関数とか難しいこと考えずに、オートフィルタでみかんだけ表示するのが簡単で、しかも速いです。どうせその後、手で間違いを修正するのですから。

お礼

ありがとうございます。お察しとは思いますが、「みかん」等というのは例で、本当はもっとわかりにくいデータで、何千件もあるのです。今までは、おっしゃるように手作業でやってきましたが、関数等で自動的に出来ればと思って質問した次第です。