初心者オサムシがExcelのマクロと戦う戦闘ブログである
by オサムシ
カテゴリ:Excel 応用( 2 )
VLOOKUPの検索値にワイルドカードをつかってみた/Excel応用

VLOOKUP・ワイルドカード・配列を駆使して最強の関数を作る


関連記事:VLOOKUP&CEILING/Excel応用

VLOOKUPってデータ抽出にはよく使うけど、ちょっと融通が利かないところがあるよね

すごく便利な関数だから、あんまり贅沢いっちゃいけないけど、戻り値が"値"だからその後は表示したり、文字数をカウントするといった文字列関数くらいしか適用できない。(あたりまえか。。)

戻り値がセル参照なら、戻り値に対して適用できる関数が多いですよね。

少し調べてみると、VLOOKUPには検索値として*(ワイルドカード)が使えるそうな

ってことは、ワイルドカードで"全て"を検索するんだけど、対象が一つになるように他の関数を使って絞り込めばなんでもできるVLOOKUPができあがる(ちょっと大げさかなぁ)

まずは画像を。
d0285354_23204818.jpg

画像上選択されているF2セルの演算式は下記になります
{=VLOOKUP("*",IF(COUNTIF(F$1:F1,B$2:B$34)=0,B$2:B$34),1,0)}

この演算式の肝はじつはVLOOKUPじゃなくて配列

一番外側の{}は入力後、確定する時に Ctrl+Shift+Enter で配列を定義すると勝手に付加される(自分で{}を入力した訳ではありません)

それと絶対参照と相対参照を使い分けて、セルが下に行くにつれてその分範囲が大きくなっていく小技(F$1:F1)も大事

部品はともかく、この演算式全体で何をしているかというと、"重複"を検索しないようにしているのだ

ロジックを言葉で説明すると、「F1から現在セルまでに存在していない値を、B1~B34セルから昇順に探し、最初に該当した値を返す」ということになる

F4セルを例にとると、F1からF3までの間にB1~B34の値が存在しないか配列を上から順番に検索していく

F1~F3までに存在しない値をみつけると COUNTIF が"0"をカウントする事になるので、IFによって=0が成立するので TRUE と判断される

この例だと"B$2:B$34"の中でF4より上に存在しない最初の値としてB5セルの"川崎"がヒットするというわけ(新宿・五反田は存在したのでFALSEで通過)

VLOOKUPの「上から検索して最初にヒットした値を戻す」という特性を活かした裏ワザ

それと配列を定義する事によって、IFで列と列(つまりは配列)を対象とすることが実現できている

配列を定義しなければ、単にエラーをはかれてしまいます

そして、上記ロジックで該当する値がなくなると#N/Aを返します

っとここまで書いたのもワタクシ的にはかなりの力作

こういうの何も考えずにサッと書けるようになりたいねぇ。osamushi

オサムシじゃーなる
[PR]
by 03musi | 2013-01-05 00:21 | Excel 応用
VLOOKUP&CEILING/Excel応用

VLOOKUPとCEILINGを使い、絶対参照と相対参照を駆使して10個で1カウントアップしてナンバリングしていくシステムをつくる

関連記事:VLOOKUPの検索値にワイルドカードをつかってみた/Excel応用

シチュエーションを考えてみた

ある会社の拠点にシリアル番号のついた商品を送る

シリアル番号で発送拠点は決まっていて、その指示はExcelで受け取っている

各拠点毎に1から始まる3ケタの通し番号をつけて、出荷の箱単位に任意フォーマットでユニークの番号をつける約束になっている

シリアル番号と通し番号の紐付けはコッチでやる事

1つの段ボール箱に10個までしか商品は入れられない

荷受けの検品が楽にできるように、10個を満たしてから次の箱に入れる事

つまり10個未満の箱は各拠点1箱以下でなければならない

ここまでがルール

Excelを見てみよう
d0285354_21102873.jpg

解説します

受け取ったデータは黄色に着色されたAとBカラム

C列以降はこちらで作成した

まず拠点毎の管理番号
=COUNTIF($B$2:B2,B2)

B列の拠点名をカウントしていくのだが、範囲に注目してほしい

最初のB2は$B$2で絶対参照になっているから固定

範囲の右側B2は相対参照となっているので、下のセルに行くに連れてB3 B4 B5...となっていく

これによって拠点名を数えながらカウントアップしていくことができる

002と3ケタ表示されているのはセルの書式設定でユーザー定義で0を選択し、000と入力して3ケタ表示するように設定している

次に出荷箱番号

(以下2013/01/04関数にて解決。関連記事参照)
まずB列の拠点名を全てF列にコピーして重複をなくす
コピーしたFを列ごと選択して【データ ⇒ 重複を削除】するのだが、その時「現在選択されている範囲を並べ替える」を選択することを忘れずに


G列に上から順に番号を振って、念のため拠点毎の数をH列に出しておく
H2セルの構文
=COUNTIF(B:B,F2)

B列全体からFの値をカウントしているF2 F3...

さあここからが本番

D列の出荷番号だが、まずは演算式を見てみよう
=VLOOKUP(B2,$F$2:$G$6,2,0)&"-"&CEILING(COUNTIF($B$2:B2,B2),10)/10

VLOOKUPでB列の拠点をF列の拠点番号から探す

間にハイフンを入れて、管理番号と同じようにB列をカウントしていくのだが、箱の中には10個入れるので、10個カウントしたら1を足していきたい

ここでCEILINGを使って、カウントした個数を10個単位で繰り上げていく

1~10までは10

11~20までは20 ってな感じ

これだと2桁になってしまうので、10で割っておく

これで出荷先と箱番号が一目でわかるような出荷箱番号ができた

できればこれをバーコードフォントでテプラにプリントして、出荷箱毎に張り付ければ、箱を閉じた後でもどこの拠点に行く箱か?中にどのシリアル番号の商品が入っているか?がわかるので、出荷管理には良いと思う

CODE39なら英数字大文字とハイフンも使える

フォントで出す時はチェックデジットが使えないから前後に*(アスタリスク)を入れる事を忘れずに

目視文字付のCODE39ならバーコードの下にデフォのフォントが表示されるから、目視用に別途表示する必要もなし

無料フォントがたくさんあるからググってみるべし

こんな感じでどうでしょう。osamushi

オサムシじゃーなる
[PR]
by 03musi | 2012-12-24 21:06 | Excel 応用


カテゴリ
試験メモ/VBAエキスパート
マクロ 基礎
マクロ 応用
Excel 基礎
Excel 応用
Office
日記
もくじ

ファン

フォロー中のブログ
初心者のためのOffic...
七里ヶ浜だより Lett...
はじめてのMac-Sup...
初心者のためのOffic...

検索

タグ

その他のジャンル