On Error GoTo Err_CommandButton1_Click この条件でしたら、エクセルで帳票もありだと思います。 DoCmd.RunSQL ではアクションクエリしか実行できないようだし、 Me.埋め込みフォーム.Requery ・ADOに慣れることは、今後VB等の他の開発言語を...続きを読む, いろいろ検索してコピペ等でやってみてもだめでした。 惜しいですね! Err_CommandButton1_Click: Application.Quitはそれがあるプロシージャのコードが全て終わるまで Set objAccess = CreateObject("Access.Application") その逆バージョンです。, Dim acApp As Object エクセルからアクセスのテーブルへ直接データを取り込む方法は     rst.Open "Q1", cnn, adOpenForwardOnly, adLockReadOnly  ' MDB との接続を確立する。決まり文句です。 Sheet1.Cells(23, 6).Value = DMN その中にKojiさんが書いた埋め込みオブジェクトが [ID], rst!   MsgBox Err.Description Exit_CommandButton1_Click: DMN = Application.RoundUp(ZPOS / ZPS, 0) こんにちは。会計士KO(記事の更新や、企業の分析、日々の雑感等つぶやいておりますので、興味のある方は是非Twitterフォローしてください)です。, 今回の記事シリーズでは、Excelで、超優秀なのにあんまり知られていない機能、PowerQuery,PowerPivotについて、絶対に誰でもわかるように解説をしていきたいと思います。, そもそも、PowerQuery,PowerPivotという言葉を知らないという人にも理解できるように心がけるので、是非この記事を読んで使いこなしてみてください。, 定常業務だけれども意外と面倒くさい。一回だけ作業するならあんまり面倒ではないと思うのですが、例えば、毎月、毎四半期となると無駄な時間がかかるように思います。, この点、エクセルの隠れた超優秀機能であるPowerQuery,PowerPivotを利用すると、利用するデータは更新されていくけれど、その加工が面倒、、、といった状況を一瞬で解決できます。, VBAやマクロには取っつきにくいという人も超簡単に使えるので是非習得して欲しいと思っています。, また、想定としてはExcel2018を想定して記載していきますが、基本的にExcel2016では同じような画面での操作が可能なので、ぜひ参考にしつつ、適宜読み替えて確認をしてください。, PowerQuery(パワークエリ)とは、MicrosoftOfficeのExcelが提供しているエクセルの機能の一つで、エクセルシートと、外部データとの連携や、連携の際のデータの加工、列の追加や抽出等を定型化する機能です。, 連携するデータとしては、xlsxファイルやxlsファイルだけではなく、CSVファイルやWeb上のAPIで提供されているJSONデータなどとの連携も簡単に行うことが出来ます。, また、マクロやVBAなどとは違い、データの連携や変更という点に特化し、グラフィカル(視覚的)に操作を行うことが出来るため、初心者でも非常にわかりやすく操作することが出来ます。, 想定は、毎月、販売管理ソフトから生成される販売データをダウンロードし、必要な情報(会社区分、粗利)を追加したうえで、会社別の月次売上推移分析、取引先別の月次売上分析を行うといった事例です。, ⓪:販売管理ソフトでは、親会社(A社)と子会社2社(B社・C社)の販売データがすべて一括で登録され、案件番号にて振り分け(Aから始まれば、A社等)られており、元データである販売データは随時更新される。(基データ), ①:月次で販売データをダウンロードし、不要な列を削除(ステータス:失注を削除)し、必要な情報(会社区分、粗利の判定)を追加する。(加工販売データ)←PowerQueryの領域, ②:加工販売データを利用し、会社別の月次売上推移分析、取引先別の月次売上分析を行う。←PowerPivotの領域(次回以降), まず、基データです。(クリックで別ウインドウが開くのでぜひ拡大してみてください。), 要求①については、テーブルをリスト化してステータスが「納品完了」となっている列のみを抽出をします。, 要求②については、案件番号列をIF関数やCOUNTIF関数を用いて判定式を組み、結果を表示させます。, これ、この加工が一回だけだったらいいと思うんですが、毎四半期同じことを繰り返すとなるとかなり馬鹿らしくないですか?, 例えば、以下は基データにないA00000というデータが追加された場合に再度、同期を行い、データを処理しているものです。, 基データ(下のエクセル)には、会社判定カラム、粗利カラムはありませんが、同期を行ったことにより、会社判定列、粗利列を追加したうえでデータを取得できていることが分かります。, 今回サンプルでお見せしているのは1行だけですが、実質的に何行足されようと更新ボタンを押せば任意のデータを取り出すことが可能です。, どのようなデータを利用してもいいのですが、同じように作成したい方は、以下に同じデータを用意しました。, 手順を学べば、色々なデータで同じことが出来るので、眺めた後に自分の手元のファイルで試せそうな場合は、そちらで実行してみてください。, 今回はPowerQuerySD.xlsxという名前で保存することとします。なお、SDはソースデータの略です。, 今回はデスクトップ上にPowerQueryというフォルダを作成し、そのフォルダの中にSDというフォルダを作成し、上記の基データを格納しています。, また、その次に、PowerQuery実行.xlsxという新しいエクセルを作成し保存しています。, このエクセルが実際に作業を行う用のエクセルです。今回はこのエクセルを実行エクセルと呼ぶことにします。, 実行エクセルを開くと、白紙のシートが出ているかと思いますので、以下の画面のようにデータタブを押してください。, そうすると次に、データの取得というボタンがあるので、以下のようにブックからを選択します。, でも待ってください、前提や要求などといった不要な要素がそのまま取り込まれてしまっていますね。このため、見出しには(仮)をつけています。, データの一次連携が完了したら、右側に出てくるクエリと接続ペインにてSDを右クリック→編集していきましょう。, エディターウインドウが開いているかと思いますので、ホームタブから行の削除を選び、上位の行の削除を選択してください。, しかし、まだヘッダー(見出し)が変ですね。本来は1行目が見出しの役割を果たすべきです。, 左上のテーブルマークのアイコンを右クリックして1行目をヘッダーとして使用を押してあげましょう。, 次に売上ステータスになっているデータのみを抽出したいので、ステータス列の横の▼ボタンを押し、失注を外しましょう。, この場合は、ステータス確定日付の欄を右クリックをして、日付/時刻という型に更新できます。, 案件番号A〇〇〇〇〇とB〇〇〇〇〇の境目で正しく会社の分類が判定されていることが分かります。, 注意点としては、使用できる列は限られており、セルを選択したような計算をしたい場合は、右の「使用できる列」というウインドウから選択を行う必要があるという点です。, また、実際にはDAXという計算式が利用されているのですが、今回DAXの話は省略します。, PowerQueryエディターのクエリの設定>適用したステップにすべて保存されています。, ドラッグアンドドロップで適用する順番を変更したり、ボタンを押せば、誤って操作した際の操作を取り消すことも可能です。, データを更新したい場合には、基データを更新し、データタブから、すべて更新をクリックすれば上記の適用したステップが更新後のデータに反映され、新しいデータを得ることが出来ます。, 冒頭でお見せした以下のGifは、案件番号A00000から始まるデータを追加し、データを更新したものです。, ちゃんと、会社判定が自動で走り、粗利の計算がされたうえでシートが作成されているのが分かりますね。, 次回は、PowerQueryを利用して取り込んだデータを基に、PowerPivotを使って毎月のグラフを毎月、自動更新したり、取引先別の分析を毎月、自動更新したりといった手順をご紹介します。, VBAやマクロを利用するよりも何倍も簡単に使える機能、PowerPivot、PowerQuery、ぜひこの記事を参考に使いこなしてみてください。. お客様の許可なしに外部サービスに投稿することはございませんのでご安心ください。, http://antonsan.net/study/excel/excel003.php. 惜しいですね! ちなみに、  Dim cnn As New ADODB.Connection クロス集計クエリ こんにちは。伊川(@naonaoke)です。 集計は、単純集計と、クロス集計があります。 クロス集計はデータ分析の基本になります。 ACCESSはこのクロス集計クエリをウィザードで作 Dim db as Database Dim db as Database ちなみに明日は返答できないのであしからず。。。 pr2 = Format(Forms("開通チェック")("終了日"), "yyyy/mm/dd") 自身のデータベースの更新クエリを実行する場合は