• ベストアンサー

エクセルの数式を教えてください。

エクセルの数式を教えてください。 銀行の通帳をイメージしてください。 A列に日付、B列に残高が記録されています。 この状態で、月末毎の残高を抽出する数式を教えてほしいのです。 つまり 2010年7月なら7/31の時点での残高、2010年8月なら8/31の時点での残高を知りたいのです。 何卒、よろしくお願いします。

  • yunji
  • お礼率9% (99/1092)

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

  • ベストアンサー
回答No.3

難しく考えすぎているような？ 日付は昇順とします。 H2セル 2010年1月 と入力すると 2010/1/1 のシリアル値になります I2セルに =VLOOKUP(H2+31-DAY(H2+31),A:E,5) 下へオートフィル(エラー等の処理なし)

画像を拡大する
全文を見る
すると、全ての回答が全文表示されます。

その他の回答 (4)

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

No.1です！ 年度が変わった場合は前回の方法では対応できませんね！ 他の方が良い方法を回答されていますが、敢えて前回の方法を使って・・・ 表の配置は前回と同じとします。 今回は作業列は必要ありません。 仮に前年度の4月からのデータを表示させるとします。 H2セルに =DATE(2009,4+ROW(A1),1)-1 としてセルの表示形式はユーザー定義から　yyyy年m月　としておきます。 これでH列は2009年4月からの月末日が入ります。 =EOMONTH(DATE(2009,4,1),ROW(A1)-1) としても良いのですが、EOMONTH関数を使用するにあたって、当方使用のExcel2003以前のバージョンでは メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れておく必要がありますので、前者の数式の方が簡単かもしれませんね。 そして、I2セルに =INDEX($E$2:$E$1000,MAX(SUMPRODUCT(($A$2:$A$1000<=H2)*($A$2:$A$1000<>"")))) （今回は配列数式ではありません、そして2行目から範囲指定しています。 このままコピー＆ペーストで大丈夫です。） 最後にH2、I2セルを範囲指定し、I2セルのフィルハンドルで下へコピーすると 各月の最終記帳日の残高が表示されると思います。 以上、長々と失礼しました。m(__)m

全文を見る
すると、全ての回答が全文表示されます。
  • MackyNo1
  • ベストアンサー率53% (1521/2850)
回答No.4

以下のような数式が簡単ではないでしょうか。 添付画像のようにD2セルに開始年月を「2009年12月」のように入力し、そのセルを右クリックでオートフィルし「連続データ（月単位）」にします。 E2セルに以下の式を入力し下方向にオートフィルします。 =VLOOKUP(MAX(INDEX(($A$2:$A$1000<D3)*($A$2:$A$1000),)),A:B,2) 数式を簡略化するため、数式は左側の列の1つ下の行を参照していますので、最後に表示したい月よりも１つ多めにオートフィルしてください。

画像を拡大する
全文を見る
すると、全ての回答が全文表示されます。
  • web2525
  • ベストアンサー率42% (1219/2850)
回答No.2

C2セルに2010年7月と入力されているとして =SUMPRODUCT((MAX(日付範囲*(日付範囲<DATE(YEAR(C2),MONTH(C2)+1,1)))=日付範囲)*残高範囲) ※日付範囲はA列、残高範囲はB列を示します

全文を見る
すると、全ての回答が全文表示されます。
  • tom04
  • ベストアンサー率49% (2537/5117)
回答No.1

こんばんは！ 一例です。 必ずしも月末日が記帳されているとは限らないと思いますので、記帳されているその月の最終日の残高を表示するようにしてみました。 ↓の画像で説明させていただきます。 作業用の列を設けて各行の月の数値を表示させています。 作業列F2セルに =IF(A2="","",MONTH(A2)) という数式を入れオートフィルで下へずぃ～～～！っとコピーします。 そして、H列には1～12の月の数値を入力しておきます。 I2セルに =IF(COUNTIF($F$1:$F$1000,H2),INDEX($E$1:$E$1000,MAX(IF($F$1:$F$1000=H2,ROW($A$1:$A$1000)))),"") これは配列数式になってしまいますので、この画面からコピー＆ペーストしただけではエラーになると思います。 I2セルに貼り付け後、F2キーを押す、またはI2セルをダブルクリック、または数式バー内で一度クリックします。 編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定してください。 数式の前後に｛　｝マークが入り配列数式になります。 このI2セルを12月までオートフィルでコピーすると 画像のような感じになります。 尚、数式は元データの1000行目まで対応できるようにしています。 以上、長々と失礼しました。 参考になれば良いのですが・・・m(__)m

画像を拡大する
yunji
質問者

お礼

何度もすみません、補足で書いた問題は解決しました。私のミスでした。 ですが、やはり2009、2010、と西暦が変わった時のデータがある場合に、うまく計算してくれません。 大変申し訳ないですが、補足のほど、何卒よろしくおねがいします。

yunji
質問者

補足

さっそくありがとうございます。やってみたのですが、うまく月末の残高が算出されませんでした。 やりかたが間違っているのでしょうか？？？ なぜか、算出されるのが、全く関係ない日付の残高になっています。例えば６月の残高に、８月の途中の残高になっています。 さらに質問なのですが、仮にうまくできたとして、“月”の指定しかないのですが、2009年の６月と2010年の6月はどのように処理されるのでしょうか？？？」

全文を見る
すると、全ての回答が全文表示されます。
  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • エクセルで数式を教えてください。

    エクセルで数式を教えてください。 通帳で考えると分かりやすいです。 A列には入った金額、B列には出て行った金額、C列には差し引き残高です。 データは１００行目まで使います。 C列に入れる数式を教えていただきたいです。 また、残高がマイナスのときは、赤文字になると嬉しいです。 いや、－でも嬉しいです。

  • エクセルで月毎の集計

    エクセルで銀行口座の管理をしようとしています。 A列に日付、B列に出金、C列に入金、E列に残高があります。（基本的に通帳の並びと同じです。） で、月ごとの出金、入金の管理をしたいと考えております。 各列ともにどんどん下の行に出金/入金を入力していくつもりなので、A列のとある月だけの出金、入金を抽出合計できたらいいなぁと思っています。何かよい方法（数式？）をご教示頂きたくよろしくお願いします。

  • エクセルの関数教えて下さい。

    A列　　　　　　　B列　　　　　　 日付or#EMPTY　 日付or#EMPTY　　 のうちA列に日付が入っていてB列に日付が入っていないものを抽出する数式 A列　　　　　　　B列　　　　　　　 日付or#EMPTY　 日付or#EMPTY のうちA,Bともにひづけがはいっているものを抽出する数式 を教えて下さい。 よろしくお願いします。

  • Excelの数式

    例えばExcelでＪ列に何月何日と日付が入っていてその日付が土日だった場合は前倒しの金曜日の日付に変換する数式を教えて下さい。 またそのファイルをCSVで開きたいのですが違うシートに張り付けてそれを開けばそのまま表示されますか？ゼロ落ちとかどっかおかしくならないですか？初心者なので方法があれば教えてください

  • エクセルの数式で

    任意の日付が複数、列Bにあるとします。 2004.3.21 2004.5.24 2005.3.1 2004.12.31 　　∫　　　　　と言った感じにです。 これらのデータから、2004.4.1～2004.9.30までの日付が何個あるかカウントしたいのですが、countを使うと３０個までしか抽出できません。何か良い方法はないでしょうか？

  • エクセル　数式を教えてください

    エクセル数式の本をみても、どうしても設定できない数式があります。 （そもそも、やりたい内容に対応する数式があるのかすら わからないのですが・・） 店舗運営用で係設定をしているのですが、係だけを抽出した 別の表を作成したいと思っています。 A列には日付が1～31日・1行目には人名（全店員分）がはいった表 があります。 例：閉店業務当番を☆マークでシフトの表に設定しています。 　　エクセルではないですが、雰囲気はこんな感じです。 　　　　山田　鈴木　森　 　　１　早　　遅　　☆ 　　２　☆　　早　　遅 　　３　休　　☆　　遅 　　４　☆　　早　　休 　　５　遅　　☆　　早 　↑　上記のような表とは別に☆だけついている人のシートを 　　作成したいです。 　　 　　　　　　　　☆ 　　　　　　１　森 　　　　　　２　山田 　　　　　　３　鈴木 　　　　　　４　山田 　　　　　　５　鈴木 　現在のところ、目でさがしてて入力しています。 　⇒日付を入力すると、その行から☆をさがして、そこに対応する 　　人名を表示させる数式を知りたいと思っています。 　説明が分かりにくいとは思いますが、宜しくお願いいたします！！ 　　　　 　　

  • EXCEL、数式で1月ずつ表示させたい

    よろしくお願いします。 EXCELでA1に「2014/10/1」と入力されていて、表示形式で「10月」と表示しています。 (1)B列に「11月」、C列に「12月」、D列に「1月」…というふうに一月ずつ増やして表示させる数式がうまくいきません。 B列に入れた式をC列～F列へマウスで数式コピーして使えるようにできるとありがたいです。 ※仮にA1に「2015/1/1」と入力するとB列に「2月」、C列に「3月」…数式で表示。 最終的な表示は画像のようになっているようにしたいです。 A1の列を変えると数式によって自動的に月名が表示したいです。 (2)上記と同じ数式ですが、3行目・4行目のように結合していながら同じことはできますか？ よろしくお願いいたします。

  • エクセルVBA、数式の入ったセルのコピーについて質問です。よろしくお願

    エクセルVBA、数式の入ったセルのコピーについて質問です。よろしくお願いします。 シートのA列は日付の入ったセルがあり、データを更新する度に行が追加されていきます。 数式(1)～(3)は、それぞれ異なる計算式が入っており、日付データを参照して計算を行っています。 　 A　　　 B　　　 C　　　 D 1 日付データ 数式(1)　数式(2)　数式(3) 2 日付データ　数式(1)　数式(2) 数式(3) 3 日付データ　　 4 日付データ　　 5 日付データ　　 A列にデータが追加したときに、B～D列の数式をA列の最終行までコピーしたいのですが、 どのようなコードを書けばよいでしょうか？ ちなみに、１行目のB～D列には、データ更新の有無に関わらず、必ず数式が入っているものとします。 何かよいアドバイスがあればよろしくお願いします。 【補足】 単純に考えると、B1～D1をA列の最終行までAUTOFILLすれば良いのでしょうが、データ数がかなり多く、 この方法だと時間がかかってしまいます。A列のデータ追加前の数式セルの最終行から、追加後の最終行までとすると、処理は早くなるのでしょうか？

  • 【エクセルの数式を教えて下さい】

    【エクセルの数式を教えて下さい】 エクセルで、Ａ列を参照して、Ｂ列に次のように反映させたいです： Ａ列 A1 apple A2 a banana A3 a cup of tea Ｂ列 B1 a B2 b B3 c スペースを含まない文字列の場合は、最初の一文字を、スペースを含む文字列の場合は、最初のスペースの次の一文字を返す数式を作りたいのです。 countif や find を使って作れるかと思ったのですが、自分のレベルではダメでした。 関数が出来る方、教えて頂けますか？ よろしくお願い致します。

  • エクセル数式教えてください

    エクセルの数式を教えてください！ 参考書をみても適当な数式を探しだせませんでした・・ 売上をのせた一覧表を別シートに飛ばす方法です。 SHEET１ 　Ａ　　Ｂ　　　Ｃ　　Ｄ　　 １　　　10日　11日　　12日 ２ペン　100　　150　　210 ３靴　　900　　840　　920　 ４鞄　　1000　　790　　980　 SHEET2 　Ａ　　Ｂ　　　Ｃ　　　Ｄ １　　　12日　　11日　　10日 ２ペン ３鞄 ４靴 販売項目（シート2のA列）と日付（シート2の1行目） を入れるとシート１から数字が飛ぶ数式を教えて下さい！

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する