[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VLOOKUPを使用した数の多い複数単語のカウント』（タカクン）
はじめまして、みなさまのお力をお貸しください 下記の品名データを1000種類程のマスターを作りコードをふりあて、 VLOOKUPでコード行にコードを入れると品名がでるようにし、 最後に合計表に”品名と個数”が出るようにしたいのです。 色々考えたり調べてもダメでした、よろしくお願いします。
コード 品名 11 りんご 11 りんご 11 りんご 260 みかん 260 みかん 863 トマト 863 トマト 863 トマト 863 トマト 合計表 リンゴ ３ みかん ２ トマト ４
Excel2003で WindowsXPです
このご質問には １．VLOOKUP関数をつかって、コードから品名を検索し表示する ２．品名の集計をする（COUNTIF関数を使う事になると思います。） と言う２つの部分があると思いますが どちらが出来ませんか？
VLOOKUP関数の使い方はこちら https://www.excel.studio-kazu.jp/func/vlookup.html COUNTIF関数の使い方はこちら https://www.excel.studio-kazu.jp/mag2/backnumber/mm20040824.html に有ります。 何をどうやってみて、どのように駄目だったのですかね？
(HANA)
HANAさん、さっそくのお返事ありがとうございます。
VLOOKUP関数を使用し、1000種類以上のマスタを作りコードを使って品名を表示させる事までは できました。
COUNTIF関数を使おうと思うのですが、 1000種類以上のマスタの中から VLOOKUP関数を使って数十種類の品名を取り出し、それをCOUNTIF関数を使おうにも 何の品名がどれだけ表示されるかはその時によって変わるので 1000種類以上の同じだけの項目を作らなければいけず、 結果スクロールの嵐で見にくく現実的に使えないという状態です。
例えば、1000種類以上のCOUNTIF関数を作って、カウント数がある品名だけ抽出するとか かなと思いますが、（思うだけでやり方はわからないです）どちらにせよお手上げです。
＞COUNTIF関数を作って、カウント数がある品名だけ抽出するとかかなと思いますが と言う事で、その路線でいきましょう。 マスタの方で対応させても良いと思いますが 入力表の方で考えた方が良いと思います。 （入力は1000行もないのですよね？）
１．COUNTIF関数を使って、先頭行から該当行までの間で
その品目が何回出てきたかを数える。
2行目の式は
=COUNTIF($A$2:A2,A2) こんな感じになります。
これを下にコピーしたとき、10行目には
=COUNTIF($A$2:A10,A10) こんな式が入っています。
２．結果が「1」の時が、一番最初に出てきた行なので
IF関数を使って、その場合のみコードを表示させます。
=IF(１の結果=1,A1,"")
このようにしておくと、作業列には 各コードが重複せずに
一つずつ表示されます。
３．合計結果を出したい場所に、小さい順にコードを表示させます。
=SMALL(２の結果の範囲,ROW(A1))
これを下にコピーすると、ROW関数の部分が「1,2,3・・・」と変化し
コードの小さい順に並びます。
作業列をC列にするならC2の式は =IF(COUNTIF($A$2:A2,A2)=1,A2,"") 合計表の最初の行（A12）の式は エラー処理も入れて =IF(COUNT($C$2:$C$10)<ROW(A1),"",SMALL($C$2:$C$10,ROW(A1))) それぞれ必要行コピーします。 A13以降にはコードが表示されますので VLOOKUP関数で品名の表示と、COUNTIF関数で回数を表示させてください。 [A] [B] [C] [1] コード 品名 [2] 11 りんご 11 [3] 11 りんご [4] 11 りんご [5] 260 みかん 260 [6] 260 みかん [7] 863 トマト 863 [8] 863 トマト [9] 863 トマト [10] 863 トマト [11] 合計表 [12] 11 りんご 3 [13] 260 みかん 2 [14] 863 トマト 4
もしマスタの方が行数が少ないようで有れば、 １．COUNTIF関数の範囲に、入力表のA列の範囲 を 検索値にマスタの該当行 を指定。 ２．この作業列の結果が1以上の物が、入力がある物なので IF関数でコードを表示。（0の場合は「""」と指定） ３．SMALL関数を使って所定の場所に取り出す。 と言う考えの方が良いかもしれません。 こちらが、（タカクン）さんが想像した方法により近いと思います。
作業列を使わない方法は、過去ログにあると思いますので 興味が有れば調べてみてください。
(HANA)
夜遅くで失礼します (HANA)さんアドバイス・回答本当にありがとうございます。
コードが数字の場合は上手くいくのですが、これが文字列と扱われると =IF(COUNT($C$2:$C$10)<ROW(A1),"",SMALL($C$2:$C$10,ROW(A1)))のセル部分が 空白になってしまいます。
なぜ文字列かというと、コード部分はバーコードで読み込ませているのですが その形式が「CODE39」で数字の最初と最後にに”＊”がつくのです。
例 12345 「CODE39」でバーコードに変換 → *12345*
この場合だとさらに数字だけを抽出するとかしなければいけないのでしょうか？ それとも、”＊”を消さなければいけないのでしょうか？ （内部的に消すか、手動もしくはマクロを使い消してそこから抽出） 現時点で後者のほう、これしか方法が思い浮かばないです… 全然スマートじゃないし情けないです；；
質問時の情報不足で本当に申し訳ありません
それでは、作業列にはコード番号を表示するのではなく データ番号を表示させ、 合計表にはINDEX関数でコード番号を表示 と言う方法はどうでしょう。 作業列が「数値」になりますので、SMALL関数で 取り出せると思います。 （ただし、出てきた順 になりますが・・・。）
作業列をC列にするならC2の式は =IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"") 合計表の最初の行（A12）の式は エラー処理も入れて =IF(COUNT($C$2:$C$10)<ROW(A1),"",INDEX($A$2:$A$10,SMALL($C$2:$C$10,ROW(A1))))
そうそう、コメント記入の際は その都度 ご署名を御願いしますね。
(HANA)
夜中にかかわらず、素晴らしいアドバイス・回答本当にありがとうございます。
まず最初に自分の署名を毎回入れていなかった事をお詫びします。 (HANA)さんを始め、他の方が閲覧される時に混乱を招く状態にしてしまいました。 深く反省し、今後はもっと公共意識を持って発言します。
(HANA)さんの言われるINDEX関数を使いましたら、無事に大多数の中の無作為カウント をする事ができました。
お恥ずかしい事ながら、INDEX関数を今回まで知らなく 改めて関数や式の素晴らしさに感動しました。
今後、作業列を使わない方法でスマートに見えるよう、別角度からのアプローチに チャレンジしていきたいと思います。
本当にありがとうございました。
(タカクン)
＞作業列を使わない方法で ということですので、過去ログ配達です。 [[20070625115531]]『データの種類を取り出したいのですが』（ぷしん） また、それぞれを関数で表示させるのではなく ピボットテーブルを使ってもよいかもしれません。 https://www.excel.studio-kazu.jp/lib/e2d/e2d.html
ちなみに「＊」の間に挟まれている桁数は一緒でしょうか？ たとえば、以下のデータの時B1に =COUNTIF($A$1:$A$2,A1) として下にコピーすると、次のような結果になります。 [A] [B] [1] *12345* 1 [2] *123* 2 桁数が一定でないなら、もう少し式を考える必要がありそうです。
(HANA)
桁数違いが確かにありました。
ただ
今後*1234*というコードも十分に考えられますので
もう少しお力をお貸し下さい
自分の知識の範囲では何でダメなのかが今一で
本やネットでも見ましたが、COUNTIFの条件一致数については分かりませんでした
自分的にはセル内の何個以上か条件が一緒ならCOUNTするんんだと思います。
なのでCOUNT条件の変更をすればと考えています。
そういう変更があるのかさえ分かりませんが・・・すいません
いい加減な憶測ばかりで物を言って
（タカクン）
"*123456-789*"
"*999-1*"
が変に表示されてしまいました。
タグか何かに引っかかったのかな？
これからは""をつける事にします。
（タカクン）
まず最初に ＞タグか何かに引っかかったのかな？ この掲示板で、「＊」マークを先頭につけると 「センタリング＆太字」の記号として扱われる様です。 現在、改行位置もタカクンさんの思い通りにはなっていないと思います。 https://www.excel.studio-kazu.jp/wiki/excelboard/ こちらの（５）にも有りますが ○空行から空行までがひとつの段落として整形され途中の改行は無視されます。 ●行頭を半角スペースで始めると書き込みの改行位置が反映されます。 「とにかくこのまま表示してよ」 って場合は、●行頭に半角スペース を入れてください。
＞COUNTIFの条件一致数については分かりませんでした こちらに関しても「＊」マークの特殊性です。 このマークは「ワイルドカード文字」と呼ばれる文字に含まれます。 エクセルのヘルプからの引用ですが * (アスタリスク) 任意の数の文字 たとえば、「Win*」と入力すると "Win98" や "Win2000" が検索されます。
「*123*」と書くと「何か文字&123(指定の文字)&何か文字」を表すので
*12345*
何か文字~~~何か文字
指定の文字
条件に当てはまるので自身と合わせて「2」と言う答えになります。
最初のご要望が 合計表 リンゴ ３ だったにもかかわらず、私が 合計表 11 りんご 3 としたのは、 １．それまでにコードをキーとして扱っているため。 （あくまでも、コードが要でコードが主体。） ２・小さい順に並べられるため。 （コードが数値だと思っていましたので・・・。） です。
今回２番の目的は無くなったわけですし、１番に関しては
現在の問題点が有りますので、品名が重複していないので有れば
品名で判断するのはどうでしょう。
作業列の式を
=IF(COUNTIF($B$2:B2,B2)=1,ROW(A1),"")
~~~~~~~~~~B列（品名）の範囲に変更。
少し消極的すぎますかね？
ちなみに、VLOOKUP関数の「検索の型」に「FALSE」を指定して居ても コード表は昇順で並べ替えておく必要が出てきます。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.