高校・小学校でのPTAの帳簿です。
Excelのsheet1に全体の入出金を入力しています。
別のsheetには科目毎の入力を行っています。下の図です。見えにくく申し訳ありません。
sheet1の全体の帳簿に入力したら、自動で科目毎の帳簿に転記できるようにしたいと
思っています。
オートフィルタ、コピー、ピポットデーブルではなく関数で行えたらと思っています。
とうぞよろしくお願いします。

「Excelの出納帳で、別シートに自動で振」の質問画像

A 回答 (2件)

>全体シートの103行目まで



ごめんなさい。102行目まで。データのみで100行分です。
    • good
    • 0

Excelカテゴリで聞いた方がいろいろ案が出るとは思いますが、数式でやるにはちょっと長いし複雑です。


フィルタオプションやピボットテーブルをお勧めはします。
というか、なぜ使わないのかがわからない…
(ちなみにピポットではなくピボットです。「pivot」)

例示通りにデータがあるとして、各費目シートのA3に

=IF(COUNTIF(全体!$B:$B,$A$1)<ROW(A1),"",INDEX(全体!$A$3:$F$102,SMALL(IF(全体!$B$3:$B$102=$A$1,ROW($1:$100)),ROW(A1)),COLUMN((A1))))

と入力し、Ctrl+Shift+Enterで確定。必要範囲にコピーします。
全体シートの103行目までを抽出できる範囲にしてあります。
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Q(EXCEL)出納帳に科目別シートを作成したい

添付の画像のような出納帳を作成しています。

「出納帳シート」の『相手科目』は、入力規則のリストを使用して、《リストシート》より選択するように設定しました。

今回は、この『相手科目』をそれぞれ別々のシートに分けて、自動的に集約・合計されるようなものを作成したいと思っております。

Webを検索してみるとVBAを使用する必要があるとの内容を拝見したのですが、自分のExcelファイルに導入する際、どこを書き換えれば良いのか分らず、行き詰まってしまいました。

関数を使用しても、VBAを使用しても良いのですが、VBAについてはあまり知識がありませんので、初心者向けに教えて下さると助かります。

面倒なお願いで申し訳ないのですが、よろしくお願い致します。

Aベストアンサー

>関数を使用しても、VBAを使用しても良いのですが
関数で対応できます。
ご提示の画像が判読できない状態です。
B5は需用費と読めますがそれで良いでしょうか?
また、費目別のシートへ抽出する項目は日付、摘要、入金、出金で良いでしょうか?
日付については下記の数式で良いと思います。
=IF(COUNTIF(出納簿!$B$5:$B$1000,"需用費")>=ROWS(A$5:A5),INDEX(出納簿!A:A,SUMPRODUCT(SMALL((出納簿!$B$5:$B$1000="需用費")*ROW(A$5:A$1000)+(出納簿!$B$5:$B$1000<>"需用費")*10^9,ROWS(A$5:A5))),1),"")
摘要はINDEX関数の出納簿!A:Aを出納簿!C:Cに置き換えれば目的に合います。
=IF(COUNTIF(出納簿!$B$5:$B$1000,"需用費")>=ROWS(A$5:A5),INDEX(出納簿!C:C,SUMPRODUCT(SMALL((出納簿!$B$5:$B$1000="需用費")*ROW(A$5:A$1000)+(出納簿!$B$5:$B$1000<>"需用費")*10^9,ROWS(A$5:A5))),1),"")
入金および出金は摘要を右へコピーすれば良いでしょう。
1行分の数式が確定したら纏めて下へ必要数コピーすれば完了です。
提示の数式は元データ(出納簿)の最大行番号を1000にしてありますので必要に応じて増減してください。
計算結果で0が表示されるセルが見難い場合は条件付き書式で0の場合はフォントの色を白にしてください。

他の費目シートについては需用費シートをシート全体をコピーして、費目の文字列を置換すれば良いでしょう。

>関数を使用しても、VBAを使用しても良いのですが
関数で対応できます。
ご提示の画像が判読できない状態です。
B5は需用費と読めますがそれで良いでしょうか?
また、費目別のシートへ抽出する項目は日付、摘要、入金、出金で良いでしょうか?
日付については下記の数式で良いと思います。
=IF(COUNTIF(出納簿!$B$5:$B$1000,"需用費")>=ROWS(A$5:A5),INDEX(出納簿!A:A,SUMPRODUCT(SMALL((出納簿!$B$5:$B$1000="需用費")*ROW(A$5:A$1000)+(出納簿!$B$5:$B$1000<>"需用費")*10^9,ROWS(A$5:A5))),1),"")
摘要はINDEX...続きを読む

Qエクセルのマクロについて印刷設定を1枚毎に変更したい。

エクセルのマクロで1枚目は封筒印刷(手差し)2枚目は本文(カセット)の出力を繰り返し差し込み印刷で出力したいのですが、プリンターの設定を記録してもらえないので手間がかかっています。
何か良い方法はありませんか。マクロ初心者ですみません。ご教授宜しくお願い致します。

Aベストアンサー

当方マクロのプロですが、
プリンター固有の設定をVBAで変更するのは難しいようです。
プリンターオブジェクトで枚数指定やサイズ指定はできるのですが、
それは一般的なプロパティ。
スロット指定はプリンター固有のプロパティのようで当方も調査中です。
色々調べていると可能なようですが、実現できている人はいないようです。
今後その手法を開拓した人の掲載を待つくらいしかできそうなことは無いように思います。どこかに実現している人はいると思うのですが。

QExcel関数について教えてください

指定する複数のセルに、指定する文字列を含む場合、指定するセルの数字を表示する方法を教えてください。

例)
A1のセルに"aaa"という文字列が含まれ、
さらに、B1のセルに"bbb"という文字列が含まれる場合、
C1の文字列を表示する

どなたかご存知の方、教えてください。
宜しくお願いします。

Aベストアンサー

説明が不足しているように思いますが 書かれている文章だけで判断すること
にします。

=IF(COUNTIFS(A1,"*aaa*",B1,"*bbb*"),C1,"")

Q表と関数

壁にぶつかり、再び質問しに参りました。
複数の質問がありますが、一度に全部を質問して混乱するといけないので
分けて質問したいと思います。
まず最初の質問分だけお伺いさせて頂きます。


1.A1に年月をいれます。(書式設定で2017/5/1をyyyy"年"m"月"と設定)。(N1~Y1)も同じ。
2.B列~F列にそれぞれ番号、名前、フリガナ、日付、金額のデータを入力(重複あり)
3.B列~F列のデータから個人(番号を基準)ごとに合計額を集計

ここで、excel入門レベルの私は、

N2に

=IF(N$1=$A$1,SUMIF($B$2:$F$1000,$L2,$F$2:$F$1000),"")

入れて、コピードラッグして作りました。(個人ごとの合計額を集計)


そして、ここで機能を加えることにしました。
「入金が未納状態である」ことを表の中に表示させようと思いました。
添付画像の赤文字が「入金が未納状態である」を示しています。

契約者の中には、色んなケースがあります。
「月払」「年払」の方がおり、また契約を途中で打ち切る方がおられます。

機能を追加したことで、ここで一旦、要件を整理したいと思います。

上記の1~3の他に

4.契約終了あり
5.契約形態として「月払」「年払」あり(J列)
6.「年払」契約者の更新月(K列)
7.契約者の入金予定額(I列) -未納と判定されると、この額が未納額と同額となります。
              契約終了者は、「契約終了」と表示されています
8.「未納判定の要件」
  ・「月払契約者の未納判定」
  納付すべき月払契約者(契約終了を除く)が、月替わりの直後(毎月1日-判定日)に行われる。
  前月分を納めていない(表の中では、「空白」)者を「未納認定」
  
  ・「年払契約者の未納判定」
  納付すべき年払契約者(契約終了を除く)が、契約者「更新月」(K列)の月替わりの
  直後(毎日1日-判定日)に行われる。判定日において前月(更新月)分を
  納めていない(表の中では、「空白」)者を「未納判定」


それでは
添付画像で例題を説明したいと思います。

●2017年4月(A1)の表-「未納判定日前」

2017年4月(正確には30日)の時点です

0052 A様(月払)は、未納金を残して契約終了
0493 B様(月払)は、3月に未納、4月は4月30日になっても入金確認ナシ → 「空白」状態(Q3)
0538 C様(月払)は、契約開始から滞りなく納める
0730 D様(月払)は、4月は4月30日になっても入金確認ナシ → 「空白」状態(Q5)
2965 E様(年払)は、更新月の2月から未納が続いている
3821 F様(年払)は、更新月の1月に納めた




●2017年5月(A1)の表-「未納判定日」

月が変わり、2017年5月(正確には1日)の時点です。この日は「未納判定日」。

0052 A様(月払)は、未納金を残しての契約終了ですが、もはや契約終了してますので
「未納判定」から除外。

0493 B様(月払)は、2017年5月1日に3月に続き4月も入金確認は認められなかったので
2017年4月30日から2017年5月1日になった時点で
(P3)に赤文字の-2,500があるにもかかわらず、「空白」(Q3)から
赤文字の-2,500(Q3)と表示が変わる

0538 C様(月払)は、先月(4月)も納めているので問題ナシ

0730 D様(月払)は、2017年5月1日に前月(4月)分は未納状態(Q5 空白)なので
2017年4月30日から2017年5月1日になった時点で正式に「未納」と判定され、
「空白」(Q5)から赤文字の-3,000(Q5)と表示が変わる

2965 E様(年払)は、更新月の2月に納められず、3月に入って「未納」と判定される。
しかし、(年払)なので3月1日(未納判定日)以降のセルは「空白」でも
未納判定を行っていない。
「年払契約者の未納判定」は、毎年、「更新月」(K列)の月替わりの
直後(毎日1日認定日)に行われる

3821 F様(年払)は、更新月の1月に一括して納めたので、「更新月」以降のセルが
「空白」でも年払契約者なので未納判定を行っていない。



このように表中から「未納判定日」において、「未納」と判定した時、その相当する
セル(N~EC)に、(I列)の入金予定額を未納額(マイナス数値)として表示させる
関数式を教えてください。


★注意事項

・VBAは使用しません
・作業列を設けても構いません
・本来、B列~F列のデータは月別に別シートに記録されています。質問しやすいように
このようなかたちにしました。
・B列~F列のデータは最大1000件
・月間データ(N~Y)は、最大10年(N~EC)
・月間データは、その都度別シートに記録されています。(個人ごとの入金合計額)のみ。
・質問内容に循環参照のおそれがある場合には、その解決策も併せて提示してください


脱字・脱語・質問内容に不備がありましたらご容赦ください。
よろしくお願い致します。

壁にぶつかり、再び質問しに参りました。
複数の質問がありますが、一度に全部を質問して混乱するといけないので
分けて質問したいと思います。
まず最初の質問分だけお伺いさせて頂きます。


1.A1に年月をいれます。(書式設定で2017/5/1をyyyy"年"m"月"と設定)。(N1~Y1)も同じ。
2.B列~F列にそれぞれ番号、名前、フリガナ、日付、金額のデータを入力(重複あり)
3.B列~F列のデータから個人(番号を基準)ごとに合計額を集計

ここで、excel入門レベルの私は、

N2に

=IF(N$1=$A$1,SUMIF($B$2:$F$...続きを読む

Aベストアンサー

ふむふむ。
No1への返信で
>>年払で未納だった人が次の月に支払った場合は、更新月ではないですが入金があったので、その額を表示しますか?
>上記の理由により、この件も次回に質問させて頂きます。
とありましたので、とりあえず更新月以外は全て空白でよいものと判断してしまいました。
未納判定はしないけれど、入金があれば記載する。ということですね。

であれば条件の一部を変える必要があります。
>年払で更新月と一致していなければ、空白を表示します。
という部分が、
年払いで更新月と一致していない場合、入金額が0なら空白、それ以外はそのままの数字を表示する。
というわけですね。
式は次のようになります。

=IF(O2="契約終了","契約終了",IF(O2="","",IF($J2="年払",IF($K2=N$1,IF(P2<O2,IF(Q$1>$A$1,"",-O2),P2),IF(P2=0,"",P2)),IF(P2<O2,IF(Q$1>$A$1,"",-O2),P2))))

あと、O列の入力式に訂正があります。
空白の時にも0が表示される式になっておりましたので、N3,N5にも0が表示されてしまったものと思います。
O2=IF(INDIRECT(YEAR(N$1)&"年"&MONTH(N$1)&"月!I"&ROW())="","",INDIRECT(YEAR(N$1)&"年"&MONTH(N$1)&"月!I"&ROW()))
としてください。
各シートでのN列が空白であった場合は空白を、それ以外はそのまま表示する。となっています。
(そのまま表示するの場合でも空白は0と変換されるので)
P列の方は実際の入金が無いというだけなので0と表示されても問題はないはずです。

いかがでしょう?

ふむふむ。
No1への返信で
>>年払で未納だった人が次の月に支払った場合は、更新月ではないですが入金があったので、その額を表示しますか?
>上記の理由により、この件も次回に質問させて頂きます。
とありましたので、とりあえず更新月以外は全て空白でよいものと判断してしまいました。
未納判定はしないけれど、入金があれば記載する。ということですね。

であれば条件の一部を変える必要があります。
>年払で更新月と一致していなければ、空白を表示します。
という部分が、
年払いで更新月と一致していな...続きを読む

Q元ある式にSUMで修正を加えたのですが、エラーが出てしまいました。何が良くなかったのでしょうか?

Wordに、Excelのワークシートを挿入しました。
それを、AとBの2組作りました。
そして、Aで出た計算結果をaとし、Bで出た計算結果をbとします。
最後に、a+bの計算結果をBに表示させるようにしました。

最初、a+bを求める式を、次のように設定し、上手く機能しました。

Aで使った式
=SUM(G5:G33)

Bで使った式
=SUM(G4:G8)+'[C Users taka Desktop パソコンで作成する簿記 帳簿 修繕積立金(1).docx の ワークシート]Sheet1'!$G$34

このまま終わっても良かったのですが、Aの計算結果は、Aの表示上では実際に必要としない部分なので、それをBに組み込んでしまおうと思い、次のように式を修正しました。

=SUM(G4:G8)+'[C Users taka Desktop パソコンで作成する簿記 帳簿 修繕積立金(1).docx の ワークシート]Sheet1'!SUM(G5:G33)

ところがこれで、エラーが出てしまいました。
何が良くなかったのでしょうか?

Wordに、Excelのワークシートを挿入しました。
それを、AとBの2組作りました。
そして、Aで出た計算結果をaとし、Bで出た計算結果をbとします。
最後に、a+bの計算結果をBに表示させるようにしました。

最初、a+bを求める式を、次のように設定し、上手く機能しました。

Aで使った式
=SUM(G5:G33)

Bで使った式
=SUM(G4:G8)+'[C Users taka Desktop パソコンで作成する簿記 帳簿 修繕積立金(1).docx の ワークシート]Sheet1'!$G$34

このまま終わっても良かったのですが、...続きを読む

Aベストアンサー

修正したものはSUM関数の使い方が間違っているために、エラーになるのです。
 =SUM(範囲)
なのですから、別ブックであっても同じことです。
 =SUM(G5:G33)
のG5:G33の部分を別ブックにするだけですね。
 =SUM(G4:G8)+SUM('[C Users taka Desktop パソコンで作成する簿記 帳簿 修繕積立金(1).docx の ワークシート]Sheet1'!G5:G33)

他のブックのセル範囲への外部参照 (リンク) を作成する
https://support.office.com/ja-jp/article/%E4%BB%96%E3%81%AE%E3%83%96%E3%83%83%E3%82%AF%E3%81%AE%E3%82%BB%E3%83%AB%E7%AF%84%E5%9B%B2%E3%81%B8%E3%81%AE%E5%A4%96%E9%83%A8%E5%8F%82%E7%85%A7-%E3%83%AA%E3%83%B3%E3%82%AF-%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B-c98d1803-dd75-4668-ac6a-d7cca2a9b95f

修正したものはSUM関数の使い方が間違っているために、エラーになるのです。
 =SUM(範囲)
なのですから、別ブックであっても同じことです。
 =SUM(G5:G33)
のG5:G33の部分を別ブックにするだけですね。
 =SUM(G4:G8)+SUM('[C Users taka Desktop パソコンで作成する簿記 帳簿 修繕積立金(1).docx の ワークシート]Sheet1'!G5:G33)

他のブックのセル範囲への外部参照 (リンク) を作成する
https://support.office.com/ja-jp/article/%E4%BB%96%E3%81%AE%E3%83%96%E3%83%83%E3%82%AF%E3%81%AE%E3%82%BB%E3%83%A...続きを読む

Q(Excel)シート別のデータ移動について

sheet1で

1.商品番号 0001
2.商品ID  TSYB-001
3.他社名  ○ / 又は無記入

という、情報の一覧で、

3.で○にした商品番号と商品IDを

sheet2で

A列│B列
0001 │ TSYB-001 │

上記みたいに別シートにコピーされるようにする方法ってあるのでしょうか?

Aベストアンサー

こんばんは!

Sheet1のデータは↓の画像のように順序良く(商品番号 → 商品ID → 他社名)のように並んでいるのでしょうか?

↓の画像ではSheet2のA2セルに
=IFERROR(INDEX(Sheet1!$B$1:$B$1001,SMALL(IF(Sheet1!$B$1:$B$1001="○",ROW($A$1:$A$1000)),ROW(A1))-3+COLUMN(A1)),"")

配列数式なので、Ctrl+Shift+Enterで確定し
フィルハンドルでB2セルへ → そのまま下へコピーしています。

※ A列の表示形式は合わせてください。m(_ _)m

Qエクセルの関数、COUNTIFSで絶対値が○○以上の数を数えたい

男で絶対値50以上の数を数えたいのですが、

=COUNTIFS(A2:A10,"男",ABS(B2:B10),">=50")

ではエラーになってしまいます。
なんとか一発で求める方法を教えてください。
よろしくお願いします

Aベストアンサー

こんばんは!

https://oshiete.goo.ne.jp/qa/9744131.html
の関連質問ですね。

=SUMPRODUCT((A2:A10="男")*(ABS(B2:B10)>=50))

ではどうでしょうか?

※ 今回の場合、COUNTIFの配列数式では無理のようでした。m(_ _)m

QExcelで簿記をする場合の、全体のイメージについて

「Excelで簿記をするのは難しいので、専用のソフトを使った方が良い」というのは、よく聞きます。
最近、 ”ピボットテーブル ”や ”Excelのマクロ” の存在を知りました。
Excelで簿記をするには、つまり普通に作成しただけでは無理だから、 ”Excelのマクロ” で ”ピボットテーブル” や ”関数のSUMやIF” を何度も自動で実行させて作成させて行くという事ですか?

Aベストアンサー

会計士ではありませんが、個人事業をやっていて、
ExcelでもAccessでも個人事業用の会計システムを作ったことがあります。
開業以来、毎年65万円控除です。

青色申告で65万円控除を目指す程度であれば、ピボットもマクロも必要無いくらいですよ。
Sumifsと、フィルタを使えば、それなりの分析もできます。
超簡単なのは、画像で添付したような感じです。
画像の背景色のある部分は数式を入れてロックしてあります。

僕の場合のポイントは、
1.一回の取引は、「日付」、「現金/預金」、「科目」、「摘要」、「入金」、「出金」を記帳する。
2.取引毎に現金/預金の残高をSumifs関数で算出
3.取引毎の売上/経費の合計をSumifs関数で算出
4.資産移動のとき(現金から預金へ等)は、現金の取引と預金の取引として、2回の取引として記述する。

総勘定元帳みたいな感じになりますね。
仕訳帳はExcel向きではありません。
従来の会計方法にとらわれると、逆にプログラムを組み辛かった、というのが僕の作ってみた感想です。
特に4.が大きなポイントだと思っています。

ここからフィルターで、現金出納帳、預金出納帳、売上簿、経費簿を作り出せます。
バランスシートは、まぁ年に一回手動でやってもいいのではないでしょうか??

以下、余談ですが、
きちんと毎月の収支を分析したいのであれば、データベース等利用した方が良いです。
その場合は、既成のソフトに頼っちゃうのも手です。
データベース利用の場合、難しいのは、取引毎の残高の算出になります。
このときサブクエリが使えた方が良いですね。

僕はアマチュアですが、多少VBAやSQLの心得はありましたが、
今年から申告の他、分析のためにデータベースのシステム作るのに、
他の仕事をやらずに、3週間くらいかかってしまいました。
Excelで上記の簡単なもので良ければ、せいぜい1日で作れるでしょう。

会計士ではありませんが、個人事業をやっていて、
ExcelでもAccessでも個人事業用の会計システムを作ったことがあります。
開業以来、毎年65万円控除です。

青色申告で65万円控除を目指す程度であれば、ピボットもマクロも必要無いくらいですよ。
Sumifsと、フィルタを使えば、それなりの分析もできます。
超簡単なのは、画像で添付したような感じです。
画像の背景色のある部分は数式を入れてロックしてあります。

僕の場合のポイントは、
1.一回の取引は、「日付」、「現金/預金」、「科目」、「摘要」、「入...続きを読む

Qエクセルの関数で重複した数式が反映しなく困っています。

お願いします。

B列に下記の様に二つの数式が入っています。

D列に"公、有、希、欠"が表示されたら、、B列の適合セルを塗りつぶす。
数式 =AND(D6<>"",ISNUMBER(FIND(D6,"公、有、希、欠")))

C列に"日"が表示されたら、B列の適合セルを赤文字にする。
数式 =AND(C6<>"",ISNUMBER(FIND(C6,"日")))

ところが
数式が重複した時、数式=AND(C6<>"",ISNUMBER(FIND(C6,"日"))) が優先して、
=AND(D6<>"",ISNUMBER(FIND(D6,"公、有、希、欠"))) の塗りつぶしが飛んでしまうのですが…。

宜しくお願いします。

Aベストアンサー

条件付き書式の数式ですか。

条件付き書式は後から設定した数式が優先されるようになっています。
また、「ルールの管理」で条件が上下に並んでいる状態の上にあるものが優先されます。
(後から作成された条件が上にくるようになっています)
C列評価の条件が上にありませんか。

バージョンにもよりますが、Excel2007より前のバージョンでは正しい動作になります。
Excel2007以降のバージョンを使っているのであれば、
古いバージョンとの互換性を保つための「条件を満たす場合は停止」のチェックマークが
C列評価の条件に付いているのだろうと思います。

条件が2つしかないのでしたらチェックマークは外してしまいましょう。

・・・
下の図は、Excel2016で状況を再現させたときの画面です。

Qエクセル関数:業務日報の日付自動入力

よろしくお願いします。

エクセルにて業務日報を作成するにあたって日付の自動入力をしたいと思います。
日付は「平成27年5月29日月曜日」といった表現にしたいです。

また、日報は月締めの翌日から月締めまでの1ヶ月間をひとまとめとします。

希望としては、その月の第一日目を手動で入力後、その次の日からはその手動で入力した第一日目を基準に自動的に入力出来れば、と思っています。

ただ、その中の条件として「特定の曜日(休日の事)は除外する」を盛り込みたいです。

こんな都合のよい関数は有るでしょうか?

ご指導お願いします。

Aベストアンサー

No.3・4です。

>特定の曜日を毎週水曜日と第二第四の日曜日としたい場合は・・・

前回の配置通りだとすると、作業列D2セルの数式を↓に変更してみてください。

=IF(OR(A$1+ROW(A1)>=EDATE(A$1,1),WEEKDAY(A$1+ROW(A1))=4,(WEEKDAY(A$1+ROW(A1))=1)*(DAY(A$1+ROW(A1))>=8)*(DAY(A$1+ROW(A1))<=14),(WEEKDAY(A$1+ROW(A1))=1)*(DAY(A$1+ROW(A1))>=22)*(DAY(A$1+ROW(A1))<=28)),"",A$1+ROW(A1))

他のセルの数式はそのままで大丈夫のはずです。m(_ _)m


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング