過去の質問を検索しましたが、解決できなかったので質問させていただきます。
現在EXCEL2007で、お客様との取引を、VBAで作ったフォームに入力するとデータシートに書き込まれるようにしています。
データの内容は、売上日付、得意先、運搬元、運搬先、車種、ナンバー、金額、備考・・・等の計19項目で、それが1レコードとなります。売上明細です。
そこから、得意先名・日付の期間指定でデータを絞り込み、そのデータの何項目かを別シートに作ってある請求明細書の各項目欄に反映させて、それを請求明細として印刷したいのですが・・・
ちょっとした売上ソフトのようになってしまうのですが、こういったことはEXCELで可能なのでしょうか?VBAはかじった程度で知識が浅く、可能か不可能かも判断できません。
可能だとしたら、どのような手順を踏めばよいのでしょう、どなたかお知恵を貸していただけないでしょうか、よろしくお願いします。
No.4ベストアンサー
- 回答日時:
>抽出したデータが“車種”であれば、請求明細シートの車種の列全体を選択して設定するということでよいのでしょうか。
車種が元データ(Sheet1)のE列にあるなら、INDEX関数の範囲を「Sheet1!E:E」にしてください。
>また、いろいろ調べてみたのですが、知識不足でSMALL以降の式の区切れが今一つ理解できませんでした。
最後の …ROW($A$2:$A$1000),1000),ROW(A1))) のあたりと、SMALLが入ってくる理由が分からないのですが、できましたらその点についてさらにお教えいただけませんでしょうか。
SMALL(IF((Sheet1!$B$2:$B$1000=$A$2)*(Sheet1!$A$2:$A$1000>=$B$2)*(Sheet1!$A$2:$A$1000<=$C$2),ROW($A$2:$A$1000),1000),ROW(A1))
IF関数部分は、(Sheet1!$B$2:$B$1000=$A$2)*(Sheet1!$A$2:$A$1000>=$B$2)*(Sheet1!$A$2:$A$1000<=$C$2)が成立する(3条件とも成り立ち1になる)場合は、その行数を、成立しない場合は1000(行範囲よりも大きい数)の配列を返しています。
この配列の中で小さい順に1から順にその行数を求めています(ROW(A1)は1になります)。
この数字がそれぞれ3つの条件が合致する行数ですので、INDEX関数でその値を求めています。
この時、該当データがない場合は1000行目のデータを参照しますので、「0」と表示されことになります。
表示したいデータが車種のように文字列データの場合は、数式の最後に「&""」を追加するのが簡単な対応です。
数字データや日付データなどを引っ張ってきたいときは、上記の「&""」の追加では対応できないので、シートのオプションでゼロ値を表示しない設定などの対応をする必要がありますが、配列数式を多用すると、シートの動きが重くなるので、たとえばIF関数などで以下のような数式にするのがお勧めです。
=IF(車種のセル="","",元の配列数式)
ずいぶんと時間が経ってしまい失礼しました。結局、今回はVBAで何とか作成することができたのですが、配列数式のことを学ぶことができ、とても参考になりました。
どうもありがとうございました。
No.3
- 回答日時:
このケースならVBAで対応するよりも、関数だけで処理するほうが簡単かもしれません。
元データがSheet1で、日付がA列、得意先がB列にあり、請求書シートのA2セルに抽出したい得意先名、B2セルに開始日、C2セルに終了日が入っている場合、条件に該当するC列のデータを抽出したいなら以下の式を入力して、Ctrl+Shift+Enterで確定して配列数式にして、下方向にオートフィルします。
=INDEX(Sheet1!C:C,SMALL(IF((Sheet1!$B$2:$B$1000=$A$2)*(Sheet1!$A$2:$A$1000>=$B$2)*(Sheet1!$A$2:$A$1000<=$C$2),ROW($A$2:$A$1000),1000),ROW(A1)))
上記のl数式では、該当データがないと「0」と表示されますので、この「0」を非表示にするには、Excelのオプションで「ゼロ値のセルにゼロを表示する」のチェックを外してください。
なお、抽出する値が文字列の場合は、数式の最後に「&""」を付けることで対応することもできます。
ご回答ありがとうございます!
これなら解決できそうと思い、試行錯誤してみましたが、配列数式はあまりなじみがなくうまくいきませんでした。
基本的なことで申し訳ないのですが、この式を設定する範囲なのですが・・・
抽出したデータが“車種”であれば、請求明細シートの車種の列全体を選択して設定するということで
よいのでしょうか。
また、いろいろ調べてみたのですが、知識不足でSMALL以降の式の区切れが今一つ理解できませんでした。
最後の …ROW($A$2:$A$1000),1000),ROW(A1))) のあたりと、SMALLが入ってくる理由が分からないのですが、できましたらその点についてさらにお教えいただけませんでしょうか。
面倒なお願いで申し訳ないですが、どうかよろしくお願いいたします。
No.2
- 回答日時:
例えば元となる表がシート1に有ってA1セルに売上日付、B1セルに得意先、C1セルに運搬先などの項目名が右横に入力されてT1セルまでにあるとします。
また、データを抽出したい得意先名はシート2のA1セルに、期間の初めはB1セルに、期間の終わりはC1セルに入力してシート2にそれらの希望に沿ったデータを表示させることにします。
そこでシート1には作業列としてU列を使用しU2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(AND(B2=Sheet2!$A$1,A2>=Sheet2!$B$1,A2<=Sheet2!$C$1),MAX(U$1:U1)+1,"")
その後に例えばシート2の3行目にはシート1の1行目で使われた項目名で請求書を作成するうえで必要な項目名をA3セルから右横方向に並べて入力します。
シート2のA4セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(OR(ROW(A1)>MAX(Sheet1!$U:$U),COUNTIF(Sheet1!$1:$1,A$3)=0),"",INDEX(Sheet1!$A:$T,MATCH(ROW(A1),Sheet1!$U:$U,0),MATCH(A$3,Sheet1!$1:$1,0)))
これで請求書を作るのに必要な項目が選択した得意先と期間についてシート2に表示されますので、例えばシート3には請求書のスタイルを作ったうえでシート2からのデータを直接表示させるようにすればよいでしょう。
これら一連のことが完成していればシート2の1行目の条件を変えることで新しい得意先の請求書もできることになりますね。参考になりましたら幸いです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 財務・会計・経理 事業復活支援金の事前確認について 2 2022/04/11 16:07
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- システム CSVファイルのマッピング処理の省力化 1 2022/11/24 00:01
- Excel(エクセル) 【エクセル」 特定のセルで条件抽出した列を、別シートに上から詰めて表示したい。 8 2022/04/08 16:00
- Excel(エクセル) VBAで、シート間の転記するコードを教えてください。 4 2023/03/26 10:43
- Visual Basic(VBA) Excel VBA 同じ名前のフォルダがあれば作成したブックを格納するマクロをつくりたい 2 2023/01/16 16:19
- 仕事術・業務効率化 使用中のソフトで、インボイス制度に対応する為の作業料が30万以上。普通? 4 2023/06/15 21:26
- Excel(エクセル) [Excel2016] 相関表等の自動作成 2 2022/08/01 20:34
- その他(税金) 事業復活支援金の事前確認ってどこまでチェックされるのでしょうか? 1 2022/04/22 11:55
- その他(ビジネス・キャリア) 事業復活支援金の事前確認での売上高チェックについて 2 2022/03/26 13:47
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Outlook で宛先が複数の場合の人数
-
【スプレッドシート】指定の日...
-
Microsoft Formsの「個人情報や...
-
Microsoft Edgeの「ニュースと...
-
VLOOKUP関数について
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
-
outlookのメールが固まってしま...
-
teams設定教えて下さい。 ①ビデ...
-
Microsoft365で写真をアルバム...
-
Microsoft Officeに似たキング...
-
会社におけるOfficeライセンス...
-
【スプレッドシート】白色のセ...
-
Excel VBA 日程表からスケジュ...
-
VBAファイルの保存先について
-
エクセル、ワード、ネット検索...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報