エクセル2013を使ってPTAの会計処理をしています。
予算書、出納帳、決算書、特に出納帳の科目を条件にしてSUMIF関数を使って決算に自動記入出来ました。
科目別の明細はオートフィルターを使ってコピペしていたのですが、科目数も多くなり(40を超えて)行数も多くなり(200行超えてます)、自動で出来ないかと思い、過去ログ等を頼りにVBAを書いてみたのですが、うまく動作しません。
出納帳の1行目に見出しを入れて、2行目から日々の入出金、最後に収入合計、支出合計、残額の行を設けてあります。
A B C D E F G H I
1 月日 分類 小分類 No. 摘要 収入 支出 残高 科目
2 4/1 本部 運営 1 前年度 100,000 0 100,000 前年度繰越金
3 4/2 委員会 事業 2 会議 0 900 99,100 会議費
合計 ***,*** ***,*** ***、***
となっています(画面ではズレているでしょう)
このI列の科目毎に明細表を作りたいのです。
ここの過去ログで下記のようなVBAを見つけ、やってみました。
教えていた方の話では、
K列に「抽出条件の見出しと最初の科目名」
M列に「抽出条件の見出しと(抽出したい?)全科目名」
N列に「科目毎のシートの名称」
を記入して、と有った(実際の返答を列数を増やしました)ので、そのようにしました。
Sub FilterDataCopy()
Dim MyRow As Long 'G列の行を入れる変数
Application.ScreenUpdating = False
MyRow = 2
'まず前日の抽出データを全てクリア
Do Until Sheets("出納帳").Cells(MyRow, "M") = ""
Sheets(Sheets("出納帳").Cells(MyRow, "N").Text).Select
Cells.Select
Selection.ClearContents
MyRow = MyRow + 1
Loop
Sheets("出納帳").Activate
MyRow = 2
'抽出してコピー
Do Until Cells(MyRow, "M") = ""
Range("K2") = Cells(MyRow, "M")
Range("A2").CurrentRegion.AdvancedFilter xlFilterCopy, Range("K1:K2"), Sheets(Cells(MyRow, "N").Text).Range("A2")
MyRow = MyRow + 1
Loop
Application.ScreenUpdating = True
End Sub
これで動かしたところ、各タブに1行目の見出しは書き出される(タイトルを入れる都合でA2からにしています)のですが、その下の実際の入出金が記載されないのです。
どのように修正したら良いのか?お教えいただけますでしょうか?
修正ではなく、新規でも構いません。
本当は、各科目の最終行に合計を入れたいのですが、それは可能ならで構いません。(^^;)
それに印刷することを考えると、行数が少なく終わってしまうモノも有りますので、少ないシート数に出来たらうれしいですが、そのくらいはコピペ出来ると思いますので(^^;)
お知恵をいただけないでしょうか?
No.1ベストアンサー
- 回答日時:
質問に対する直接の回答でなくて失礼します。
1、各科目ごとに40枚のシートが必要ですか?
2、最終行に合計を入れると下の方で位置もバラバラですよ。
私であれば
1、データの抽出シートを1枚
K2に科目をドロップダウンリストから選択したら
希望の科目の明細が下に表示する。
2、合計金額は上部分の固定した位置に表示させる。
参考までに、先日回答したサイトを紹介しておきます。
https://oshiete.goo.ne.jp/qa/9249567.html
仮に、別シートに
A B C D E F G H
1 科目 収入計 支出計
2 会議費
3
4
5 月日 分類 小分類 No. 摘要 収入 支出 残高
とB2に希望する科目(入力規則を設定して、ドロップダウンで選択)
シートモジュールを開いて
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Sheets("データ").Columns("A:I").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("A5:H53"), Unique:=False
End If
End Sub
B2セルを変更するだけで、ご希望の科目の明細が6行目以下に表示されます。
シートを移動して希望する科目を探すより
ドロップダウンリストで探す方が楽ですよ。
アドバイスありがとうございますm(__)m
1は表現が下手ですみません。
「各科目ごとに40枚のシート」ではなく、1科目は1枚です。
科目が40くらい有るので、1科目1シートで、Book全体で40シート位になってしまう、ということです。
2の「合計を上部で固定」ですが、帳面に記帳した際の見た目を重視してしまいました。
文字サイズ等を変更して、見やすいようにすればいけますね(^_^;)
ご教授いただいたコードを新規のシートのタブで右クリックして貼り付けました。
「データ」を「出納帳」に修正しました。
しかし、6行目以下に何も表示されないのですが・・・???
科目ごとにシートを作った方が良いと思ったのは、私ではない者(後任)が作業したときに、その方が印刷する際にコピペしやすいかなと思ったからなんです(^_^;)
表示されない点等、お分かりになりますでしょうか?
出来ました!!
今日再度詳細を確認しました。
問題なく抽出できた(マクロが動いた)試しに作った表と実際に使いたい表と見比べてみました。すると、抽出条件にする「科目」が「科 目」でした。(;´・ω・)
今後引き継いでもらう方のことも考えて、ファイルを用意したいと思います。
親身にお付き合いいただき、本当にありがとうございました。
No.8
- 回答日時:
>「マクロ実行中」とメッセージボックスが出ました!!
と云う事でしたら
Sheets("出納帳").Columns("A:I").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("A5:I5"), Unique:=False
は実行されていますが
B2セルに入力されている 科目に該当するデータが一件もないという事はないですよね。
もうひとつ試して
B2セルを空白にしたら全てが表示されると思うのですが
>ご教示いただいたページの様にやってみたところ、1行目の「月、分類・・・」が表示され
>るだけでその下は何も表示されませんでした。
フィルターオプションの操作を行っても希望のデータが抽出されないという事ですね。
まずは、ここをクリアする必要があるかも??
No.7
- 回答日時:
こちらでも同じようなエクセルファイルを作成し実行しましたが
出来ました。
ちなみに
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
MsgBox "マクロ実行中"
Sheets("出納帳").Columns("A:I").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("B1:B2"), CopyToRange:=Range("A5:I5"), Unique:=False
End If
End Sub
と途中にMsgbox で表示する行を追加してみましたが
B2セルの内容を変更したら、 マクロ実行中 と 表示が出ますか?
No.6
- 回答日時:
今日は暇なのでお付き合いします。
現在のシートの状況を説明してもらえませんか
>1行目の「月、分類・・・」が表示されるだけでその下は何も表示されませんでした。
先の例では
1行目、2行目に抽出の条件です。
元データに月という項目があるのでしょうか。
先に紹介したコードはシートモジュールに記載します。
シート名を右クリックして、コードの表示をクリック
VBエディターが起動したら、そのシートのシートモジュールになります。
No.5
- 回答日時:
>フィルターを手動でやったら、出来たのですが・・・(;´・ω・)
だったら
マクロの記録
フィルターを手動でやったら
記録の終了
これで作成されたコードと現在のコードの違いを確認しましょう
If Target.Address = "$B$2" Then
の部分で、B2セルの内容が変更されたら
実行するという事を指定したいますが
No.4
- 回答日時:
横から失礼します。
後任に引き継ぐのであれば、あまり凝らない方が良いかと・・・。そこで、次のような手順ではいかがでしょうか?こうしておけば、手作業のコピペも楽だと思います。
①元シートをコピーして”明細”シートを作成します。以下、”明細”シートでの作業です。
②科目で並べ替えます。
③科目をキーに、小計を設定します。「グループ毎に改ページを挿入」にすれば、印刷時に改ページもできます。
貴重なアドバイスありがとうございます。
そうですね、今後科目名が変わったり増えたり減ったりすることも有りえますので、マクロだと敷居が高いかもしれません(^^;)
私はこれで楽させてもらいます(爆)が、引き継ぐのは別にした方が良さそうです。
ありがとうございました。m(__)m
No.3
- 回答日時:
NO1です。
>しかし、6行目以下に何も表示されないのですが・・・???
すみませんでした。
CopyToRange:=Range("A5:H53"), Unique:=False
は
CopyToRange:=Range("A5:H5"), Unique:=False
ですね。
後はB2セルを変更することでVBAが実行されると思います。
フィルターオプションの機能ですが
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filte …
の辺りを参考に一度、手動で抽出してみてください。
No.2
- 回答日時:
ピボットテーブル試してみましたか?
http://allabout.co.jp/gm/gc/297727/
出納長のシートにおいて
[Ctrl]+[F3]名前の定義
名前 : PT範囲_出納
参照範囲 : =$A$1:INDEX($I:$I,COUNTA($A:$A))
ピボットテーブルの範囲を
=PT範囲_出納
とすることでピボットテーブルの範囲が可変になり
ピボットテーブル更新時に計算してくれます。
とりあえず、科目を行フィールド
収入と支出をデータフィールド
「データ」となりますので列フィールドにドラッグ
とか 科目をページフィールドにドラッグする
とか 合計/支出 の金額のセルをダブルクリックしてみるなんてのも
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
Excelの出納帳で、別シートに自動で振り分け
財務・会計・経理
-
(EXCEL)出納帳に科目別シートを作成したい
Excel(エクセル)
-
現金出納表の内容を別表の科目別欄に自動コピー出来ますか?
Word(ワード)
-
-
4
エクセル入力で項目別にシートに振り分ける方法を教えてください!
Excel(エクセル)
-
5
エクセルで集計表から項目ごとに別シートへ表示する方法
財務・会計・経理
-
6
フィルタを使わずに抽出
Excel(エクセル)
-
7
エクセルでシートの振り分けを自動的にする方法
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
タイムスタンプとテキストから...
-
エクセルの文字が途中から消える
-
エクセルの質問です。 F列からL...
-
ワークシートに出現したこの画...
-
Excelの警告について
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
Excelでの文字色
-
マクロの処理が遅くなった
-
シートの情報を別のシートへま...
-
OFFSET関数を使用したいのです...
-
エクセルの数式バーのフォント...
-
エクセルデーターから必要な項...
-
Excelについて教えてください。...
-
SUBTOTALは、参照された数字で...
-
エクセルの「条件付き書式」を...
-
Excelの関数について このよう...
-
エクセル。金額から「円」を除...
-
Excelの数字の前に入っている空...
-
エクセルの問題です。絶対値の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報
アドバイスありがとうございます。時間の都合と試行錯誤で遅くなりましたm(__)m
ピボットテーブル試してみました。「収入支出」が合計で表示されるのは良いのですが、他の「月日」や「分類」「摘要」等が1行で表示されません。
私の設定の仕方が悪いのかと色々やってみたのですが、やればやるほど見づらくなってしまいました(^^;)
さらなるアドバイスありがとうございます。時間の都合と試行錯誤で遅くなってしまいましたm(__)m
修正をしてやったのですが、何も表示されませんでした。
あ、教えていただいたコードの「データ」は実際に合わせて「出納帳」と修正しました。
そこで、別に簡単な表を作り、動かしてみたのですが、そちらではちゃんと表示されました。
表の違いは、行数が違うこととセルに記入された単語?そして空白セルの有り無しくらいなんですが・・・
フィルターを手動でやったら、出来たのですが・・・(;´・ω・)
再度ありがとうございます。
私が勘違いしていたようです。(^^;)
「手動でフィルターを掛けたら出来ました」というのは、単にその表にフィルターを
掛けたことで、ご教示いただいたページの様にやってみたところ、1行目の「月、分類・・・」が表示されるだけでその下は何も表示されませんでした。
行数が多いのか?(そんなことはないと思いますが)と思い、例題と同じ13行くらいを範囲指定してみましたが、1行目しか表示されません。
例題の表と見比べても、当然違いが判りません。あえて言えば、こちらには空白セルが有るくらいでしょうか。
なんか深みにハマってきました(;_;)
ありがとうございます。
「月、分類・・・」は質問に書いた元データ(出納帳というシート名)の
A B C D E F G H I
1 月日 分類 小分類 No. 摘要 収入 支出 残高 科目
この行のことです(^^;)
マクロを実行して、表示されるのは
A B C D E F G H I
1 科目
2 (リスト表示される科目)
3
4
5 月日 分類 小分類 No. 摘要 収入 支出 残高 科目
で、6行目からには何も表示されないんです。
はい、シートを右クリックの「コード表示」から書き込んでます。
わざわざやっていただきありがとうございます。
「マクロ実行中」とメッセージボックスが出ました!!
でも、表示は1行目だけです。
またありがとうございます。
試しに選択したモノはちゃんとデータのあるやつです(^^;)
そして、空白にすると、「全てのデータ」が表示されました。
ちなみに、列はそのまま、行は13行、データは不規則な表でやってみたらちゃんと表示されたのですが、データを今回の表からコピペしたらダメでした。
データ自体に問題が有るってことでしょうか?
科目の列には空白は無いです。
他の列には空白が有りますが。
出納帳シートの表の科目を一つコピーして貼り付けしても、今までと同じで1行目だけの表示でした。