この記事では、『Excel関数を使って、条件に該当するレコードにのみ連番を振る』方法を解説していきます。
ちなみに･･･。
僕あまざけは「Microsoft Office Specialist Excel 2019 Expert」を取得しています。
仕事をこなすことで自然と身に付いたExcelの知識のおかげでMOS資格を取得できました。
Contents
手順解説
- B2～B8：連番用の列
- C2～G8：元データ
- C11：抽出用のキー
- C16：抽出キーから導いた取引先名
- D22～F26：連番と抽出キーから導いた結果
今回の例題は、
C11に抽出したい取引先のコードを入力
↓
取引先コードに応じた行に連番が自動割り振りされる
↓
納品書に必要なデータが表示される
というものです。
①連番列の1行目に入力する関数
まずはB3(連番入力列の1行目)に入力する関数は以下。
もしC3とC11が同じなら、「1」を表示し、違うなら何も表示しない。
B3セルとB4セル以降では入力する関数が異なるので注意です。
②連番列の2行目以下に入力する関数
B4(連番入力列の2行目)に入力する関数はこちら↓
取引先CDの個数が自分より上のセルに表示されている数字と同じなら空白にし、異なるなら自分のいる行と同じ範囲までの個数を返す。
ここが少し分かりにくいところ。
登場する関数をひとつずつ見ていきましょう。
MAX関数
B3～B3の範囲の最大値を返します。
つまり「今自分がいるセルより上のセル全てが対象」です。
後にオートフィルするので最初のB3は絶対参照なので注意です。
COUNTIF関数
C3～C4の範囲(自分のいるセルと同じ行までの取引先コード列)で、C11(抽出したい取引先コード)と同じ内容のセルが範囲内に何個あるかを返します。
IF関数
おなじみのIF関数に、先ほどのMAX関数とCOUNTIF関数を組み込みます。
COUNTIF関数の結果とMAX関数の結果が同じなら、
真の場合：“”
何も表示させない。
偽の場合：COUNTIF($C$3:C4,$C$11)
COUNTIF関数の結果を返す。
③B4セルをオートフィル
「連番」列の2行目である『B4セル』を下に向かってオートフィルしましょう。
これで「抽出用キー」に指定されている取引先にのみ連番が振られます。
④連番が振られたデータをVLOOKUP関数で抽出する。
該当する行にのみ連番を振ることが出来ればあとは、VLOOKUP関数で必要なデータを抽出すればOKです。
抽出データが無い場合はエラー表示されるので、IFERROR関数でエラーを非表示にしましょう。
なお、Microsoft 365やExcel 2021をお使いの方は「XLOOKUP関数」が使えますのでエラー対策は不要です。
⑤完成
抽出用キーに指定する取引先コードを変更すれば、それに応じて連番も振り直されます。
「連番振り」の使いどころ
- 配送やピッキングで使用する出荷指示書
- 見積書
- 商品の発注書
などでしょうか。
関連記事
Excelに関する関連記事です！
おわりに。
昨今、書類の作成、出荷や発注に関することなどはシステムを導入してオンライン化、自動化されていることが標準です。
ですが、それでもすべての業務がシステムでオンライン化されているとは限りません。
費用対効果が低い業務に費用をかけてシステムを導入する訳にもいかない。でも、この業務は手間と時間がかかる、めんどくさい。
そういった業務対してExcelを有効に使えれば効果大です。
ブログ「としけば！」にようこそ！