月末の残高を計算する方法と式

このQ&Aのポイント
  • 月末の残高を計算する方法と式について説明します。具体的には、銀行名、日付、残高が入っているシートから5月末の合計残高を求める方法を解説します。
  • 例えば、三菱東京UFJ銀行の5月末の残高が40,000円であり、りそな銀行の5月末の残高が100,000円であり、みずほ銀行の5月末の残高が15,000円である場合、合計残高は155,000円になります。
  • なお、日付が必ずしも5月31日ではないため、月末の残高を求める式を作成する際には、特定の日付を指定するのではなく、残高データから最も新しい日付を自動的に選択するような関数を使用すると便利です。また、この方法は他の月にも応用できます。
回答を見る
  • ベストアンサー

月末の残高を入れたいのですが式を教えてください。

銀行名・日付・残高が入ってるシートがあるのですが '5月の待つ時点の合計残高を求める方法を教えてください。 例えば、 三菱東京UFJ銀行 2013/05/20 30,000 りそな銀行 2013/05/22 100,000 三菱東京UFJ銀行 2013/05/25 40,000 みずほ銀行 2013/05/26 10,000 みずほ銀行 2013/05/31 15,000 と入ってるのですが、 三菱東京UFJ銀行の5月末の残高は40,000 りそな銀行の5月末の残高は100,000 みずほ銀行の5月末の残高は15,000 なので、155,000円になると思うのですが それを求める式はどう作ればいいのでしょうか? 「5/31」の各銀行の残高を探す式を作ろうと思いましたが 日付が必ず5/31ではないから、まずそこで躓いてしまいました。 できれば関数で一つのセルに5月末の残高を入れたいのですが式を教えてください。 今回は5月ですが6月にも応用できる方法も教えていただければ助かります。

質問者が選んだベストアンサー

  • ベストアンサー
  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.1

こんばんは! 一発で!とはいきませんが・・・ 各銀行毎の最終残高を求めてそれを合計する方法が一番手っ取り早いと思います。 ↓の画像でE列の銀行名はあらかじめ入力済みだとして、D2セルに =INDEX($C$1:$C$1000,MATCH(MAX(IF(A$1:A$1000=E2,B$1:B$1000)),B$1:B$1000,0)) これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定! この画面から数式をコピー&ペーストする場合は 数式をドラッグ → 右クリック → コピー → D2セルを選択 → 数式バー内で一度クリック → 右クリック → 貼り付け  → そのまま(編集可能のまま)Shift+Ctrlキーを押しながらEnterキーで確定! 数式の前後に{ }マークが入り配列数式になります。 これを下へオートフィルでコピー! 最後に合計欄は単純に SUM関数で合計しています。 ※ 前提条件として5月分のデータだけしかない!としています。 (6月分のデータがある場合は最新のデータが表示されてしまいます。) 他に良い方法があればごめんなさいね。m(_ _)m

DGBUWWSMBZW
質問者

お礼

す、すげー この短時間で作れるなんてすごすぎます! ありがとうございました!

その他の回答 (3)

  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.4

回答No3です。単に月末の残高をD列に表示させるのでしたらD2セルに次の式を入力して下方にドラッグコピーすればよいでしょう。 =IF(COUNTIF(A2:INDEX(A:A,MATCH(DATE(YEAR(B2),MONTH(B2)+1,0),B:B,1)),A2)=1,C2,"")

DGBUWWSMBZW
質問者

お礼

ありがとうございました。

  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.3

取引銀行の数が変わってもその名前を自動的表示させ、しかも複数の月にわたってもその月末での残高を自動的に表示させることができます。 お示しのデータがシート1に有り、A1セルには取引銀行名、B1セルには日付、C1セルには残高とそれぞれ項目名が有り、2行目から下方にデータが入力されているとします。 D列には作業列を設け、D2セルには次の式を入力して下方にドラッグコピーします。 =IF(ROW(A1)<=COUNTA(A:A)-1,IF(COUNTIF(A$2:A2,A2)=1,ROUNDDOWN(MAX(D$1:D1),-7)+10000000,IF(COUNTIF(A$2:A2,A2)>1,ROUNDDOWN(INDEX(D$1:D1,MATCH(A2,A$1:A1,0)),-7),0))+DATE(YEAR(B2),MONTH(B2),1)*100+COUNTIF(A2:INDEX(A:A,MATCH(DATE(YEAR(B2),MONTH(B2)+1,0),B:B,1)),A2),IF(ROW(A1)<=COUNTA(A:A)-1+INT(MAX(D$1:D1)/10000000),(ROW(A1)-(COUNTA(A:A)-1))*10000000,"")) A列での銀行目が入力されるよりもさらに下の行までドラッグしてください。 シート2に各月末での残高を銀行名とともに自動的に表示させます。 A1セルには銀行名とでも項目名を入力したのちにA2セルには次の式を入力して下方にドラッグコピーします。 =IF(COUNTIF(A$1:A1,INDEX(Sheet1!$A:$A,MATCH(IF(ISNUMBER(SMALL(Sheet1!D:D,RANK((ROW(A1)+1)*10000000,Sheet1!D:D,1)-1)),SMALL(Sheet1!D:D,RANK((ROW(A1)+1)*10000000,Sheet1!D:D,1)-1),IF(COUNTIF(A$1:A1,MAX(Sheet1!D:D))>0,"",MAX(Sheet1!D:D))),Sheet1!$D:$D,0)))>0,"",INDEX(Sheet1!$A:$A,MATCH(IF(ISNUMBER(SMALL(Sheet1!D:D,RANK((ROW(A1)+1)*10000000,Sheet1!D:D,1)-1)),SMALL(Sheet1!D:D,RANK((ROW(A1)+1)*10000000,Sheet1!D:D,1)-1),IF(COUNTIF(A$1:A1,MAX(Sheet1!D:D))>0,"",MAX(Sheet1!D:D))),Sheet1!$D:$D,0))) B1セルには今年の5月からでしたら2013年5月と入力します。数式バー上では2013/5/1の表示になっていることが必要です。B1セルのデータを右横方向にドラッグすることで2013年6月・・・のように変わりますね。 そこでB2セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。 =IF(ISERROR(INDEX(Sheet1!$C:$C,MATCH(ROW(A1)*10000000+B$1*100+1,Sheet1!$D:$D,0))),"",INDEX(Sheet1!$C:$C,MATCH(ROW(A1)*10000000+B$1*100+1,Sheet1!$D:$D,0))) これで銀行名も自動的に表示されますのでシート1でのデータが漏れなく表示されることになります。

DGBUWWSMBZW
質問者

お礼

ありがとうございました。

回答No.2

E1 5 E3 三菱東京UFJ銀行 F3 =index(c:c,max(index((A$3:A$1000=e3)*(month(B$3:B$1000)=E$1)*row(A$3:A$1000),))) F6 =sum(f3:f5) 同じ月でいちばん下の行を読み取ります。他の月の行が続いていても問題ありません。「2013 年 5 月末日」などとしたい場合は、「*(year(B$3:B$1000)=2013)」などを上式に追記してください。数式の確定に Ctrl+Shift+Enter の操作は不要です。

DGBUWWSMBZW
質問者

お礼

ありがとうございました。

関連するQ&A

  • 残高について教えて下さい

    三菱東京UFJ銀行のカードで三菱UFJ信託銀行でATMの画面でお引き出しの所押し、お金をおろしました。 お取引後残高のところに今日おろした金額が載っており残高がおかしいと思い三菱東京UFJ銀行で残高の確認をしたところきちんとした残金が確認できましたが、三菱東京UFJ銀行と三菱UFJ信託銀行でどうも残金が違うようです。三菱UFJ信託銀行の金額明細書の下部にDCキャッシングサービスサービスと書いてありました。良くわからないのですがDCキャッシングになってしまっていると考えてよろしいでしょうか! 文章がわかりにくいと思いますが銀行に理解のある方教えてください。宜しくお願い致します。

  • 振込手数料一番安いのはどれですか?

    UFJ銀行に振込みたいのです。みずほの銀行が近くにあります。東京三菱のキャッシュカードを持っています(18000の残高あり)。18000円振り込みたいのです。 1、UFJ銀行で東京三菱のカードで振り込む 2、UFJ銀行で現金で振り込む(ATMで) 3、みずほ銀行で東京三菱のカードで振り込む 4、みずほ銀行で現金で振り込む(ATMで) 5、近所のコンビ二ATMで東京三菱キャッシュカードで振り込む(平日) 6、近所のコンビ二ATMで現金で振り込む(平日) 7、コンビニATMに休日振り込む できればUFJ銀行まで遠くて行きたくないので、コンビニかみずほで安い方法で振り込みたいのですが。それぞれいくらかかりますか?

  • UFJ銀行の通帳から残高を引き出せるか?

    未だにUFJ銀行の通帳、カードを持っています。 三菱東京UFJ銀行になるとき(東京三菱銀行とUFJ銀行が統合するとき)に、通帳やカードの手続きをしそびれていました。 今日たまたま整理をしていたら、UFJ銀行の通帳が出てきて見ると残高が残っていました。この残高を引き出すことはできるのでしょうか? もう統合されて随分年数が経っていますので、無理なのでしょうか? 詳しい方がいらっしゃいましたら教えてください。 よろしくお願いいたします。

  • メガバンク(都市銀行)の中で気に入っている銀行は?

    メガバンクの中で、あなたが気に入っている銀行はどれですか? なるべくその理由も教えてください。 ・みずほ銀行 ・三菱東京UFJ銀行 ・三井住友銀行 ・りそな銀行

  • 電話での残高照会

    三菱東京UFJ銀行の口座の残高照会を電話にてすることは可能ですか?

  • ATMでお金を下ろしたときの手数料が終日0円の銀行

    ATMでお金を下ろしたときの手数料が終日0円なのは りそな銀行・埼玉りそな銀行・近畿大阪銀行 以外にもありますか? みずほも三菱東京UFJも休日は手数料がかかるようですね・・・ よろしくお願いします。

  • 大手銀行の違い教えて下さい

    三菱東京UFJ銀行、みずほ銀行、りそな銀行、三井住友銀行の経営体質やサービスの違い、ビジョンの違いなど詳しく教えてほしいです。

  • 三菱東京UFJ銀行はネットで残高照会できますか?

    題名のままですが、三菱東京UFJ銀行はネットで残高照会できますか?もしできるならば条件や方法を教えてください。UFJ銀行の時はできなくて困りました。

  • この中でどこの銀行が良いでしょうか?

    新しく銀行に口座を開こうと思っているのですが、どこの銀行で口座を開くほうが良いでしょうか? 家や会社の近くには、みずほ銀行・三菱東京UFJ銀行・三井住友銀行・りそな銀行な・三菱UFJ信託銀行などが有ります。 すでに、開いている銀行は、ゆうちょ銀行・ネット銀行などです。

  • 古い銀行預金通帳の残高確認は?

    旧三和銀行(現三菱東京UFJ銀行)の預金通帳(普通預金)の残高記載が 平成7年11月までで  通帳に「解約」や「繰越」の記録はありません。その後の出入金の履歴を知るのは どのようにすれば宜しいのか? 現在 三菱東京UFJ銀行の口座は持っています。 ー以上ー

専門家に質問してみよう