関数で取引明細作成①～データ抽出

年間の取引明細をお客様から求められるときがありますよね。

特に農家や自営業の方なんかは、税金の申告なんかに使います。

データはあるけど、ちゃんとした様式、または自分で作ったレイアウトに出力したい。なんていう欲求があると思います。

Excel2007なんかでは、数万件あるデータでも、「テーブル」という機能を使うと並び替え、抽出、集計といった作業を簡単に行えます。

ただ、データを丸ごと扱うので、どちらかというとデータ分析向きです。そのままお客様に提示するのは難しいでしょう。VBAを使って、思いのままのレイアウトに！・・・・というのもできますが、VBAを覚えるのも簡単ではありません。

今回は、なんとか関数で取引明細を作成することに挑戦してみます。今回は今まで解説した関数のみで作成するので、引数の説明は省きます。各使える関数シリーズを参照してください。

このような、データと表を用意しました。

解説しやすくするため、同一シート上にデータと明細を抽出するエリアを作りました。上部の取引データから下のお取引明細書の取引期間と顧客を満たすものだけ抽出します。



年間取引なので、同一の顧客データが複数存在します。集計表であればDSUM関数などを使い合計できますが、明細表なので条件に一致するデータをすべて表示しなければなりません。VLOOKUP関数を使っても一致するデータの最初のデータしか表示してくれません。

そこで、データの最初の列に「抽出」というフィールド（列）をつくり、ここに条件を満たしたデータに順に番号を振っていく関数を入力します。その番号を検索値としてVLOOKUP関数を使い「お取引明細書」を完成させます。（今はまだわかりにくいと思いますが、がんばりましょう。）



まずは、期間を入力しましょう。この例では2月のデータしかないので、2/1から2/28までにします。



そして対象となる「青森太郎」さんの顧客№を「1」と入力し、となりのセル「F10」には、エラー回避をするためIFERROR関数（使える関数～エラー回避関数を参照）を使い、VLOOKUP関数（使える関数～VLOOKUP関数を参照）で顧客名を表示します。データの範囲はセル「B1」から「C7」までにし、絶対参照にします（F4キーを押す）。これで誤った顧客№を入力してもエラー表示されず空白になります。



※Office2003では　
IF(ISERROR(VLOOKUP(E10,$B$1:$C$7,2,FALSE),"",VLOOKUP(E10,$B$1:$C$7,2,FALSE)になります。

それでは抽出フィールドに関数を入力していきます。



まず顧客№が①明細書で入力した顧客№セル「E10」と一致すること、そしてデータの売上日が②期間最初の日付セル「A10」以上「>=」で③期間最後の日付セル「C10」以下「<=」という３つの条件が考えられます。



３つの条件をすべて満たすデータを抽出したいので、ここではAND関数（使える関数～AND・OR関数を参照）を使い判定します。IF関数と組合わせて使います。



=IF(AND(①B2=$E$10,②D2>=$A$10,③D2<=$C$10)
セル「B2」が顧客№「E10」と等しく売上日「D2」が期間最初「A10」以上かつ期間最後「C10」以下という意味になります。（明細書の条件セルは絶対参照にしましょう。）「,」で区切ります。

そして条件を満たしたデータに番号を振る作業です。厳密には番号を振るというより、満たしたデータをカウントするというのが正しいです。ここではCOUNTIF関数（使える関数～SUMIFとCOUNTIF関数を参照）を使い現在置までの条件を満たしたデータをカウントしカウント数を表示させます。



COUNTIF($B$2:B2,$E$10)

データの顧客№と明細書の顧客№セル「E10」と一致するデータのみカウントするという意味になります。

※カウント範囲の最初のセル「$B$2」のみ絶対参照にすることによって、コピーしたとき「B2」を基準にカウント範囲が相対で変化します。

最後に、AND関数で指定した３つの条件を満たさないときの処理を指定します。「０」を表示させたいので、「０」と直接入力し「）」で閉じます。



=IF(AND(B2=$E$10,D2>=$A$10,D2<=$C$10),COUNTIF($B$2:B2,$E$10),0)

AND関数で指定した３つの条件（顧客№、期間最初以上、期間最後以下）を満たした場合、明細書の顧客№「E10」と一致するデータをカウントする。満たさない場合は有無を言わせず「０」を表示する。という意味になります。

Enterキーを押します。



コピーしましょう。



顧客№「1」「青森太郎」さんの期間を満たしたデータに番号が振られました。番号というよりは、条件を満たした1個目、2個目という感じです。それ以外は「０」表示されてますね。

顧客№「2」の「弘前花子」さんに変更してみます。



ただしく表示されてますね。

期間を変更してみましょう。



「弘前花子」さんのデータでも期間を満たさないデータは「０」表示になりました。

一応補足事項として、今回はお客様へ提示する取引の履歴ですから、データは売上日を昇順で並び替えしておきましょう。売上日がバラバラだと正しくカウントされません。


関数は組み合わせることにより、複雑な条件に対応した処理ができることを知ってもらえればと思います。

普段あきらめている処理も、もしかしたら解決の糸口があるかもしれません。

次回はVLOOKUP関数を使い明細書を完成させます。

関数で取引明細作成②～明細書を完成させる・・・・・へつづく