• 締切済み

エクセルでシートの一覧表から必要な行だけを別のシートにコピーできる関数を教えてください

エクセルで家計簿を作っています。 Sheet1にはすべての取引を記入した上で口座取引は口座毎に別のSheetに通帳記入のような形で記入したいのですが、 該当する関数が分かりません。 例えばSheet1の項目を『1列=No.,2列=日付,3列=収入費目,4列=収入内訳,5列=支出費目,6列=支出内訳,・・・10列=収入金額,11列=支出金額,12列=現金残高』とします。 口座引出の場合は収入費目=口座引出、収入内訳=銀行口座名、収入金額=口座引出額 口座預入の場合は支出費目=口座預入、支出内訳=銀行口座名、支出金額=口座預入額 です。 そこで、収入内訳or支出内訳の銀行口座名がA銀行の行のみをSheet2(A銀行)にSheet1のデータをもってきて、A銀行の残高を集計します。 例えばSheet1にデータが50行あったとして、そのうち3,10,17,29,48行目の収入内訳or支出内訳だった場合、Sheet2にはその5行のみが自動的に反映されるという関数です。 説明下手で申し訳ありませんが、ご回答頂ければ助かります。

みんなの回答

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

解答No3です。ご質問のタイトルが該当する行を別のシートにとのことでしたのでそのような答えになっています。もしも必要な列だけを表示されるのでしたら、それなりの式に変形する必要がありますね。

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

シート2ではA1セルに例えばA銀行と銀行名を入力するとして2行名ではシート1に書かれた項目名をコピーして貼り付けることにします。 その上でシート1の13列目であるM2セルには次の式を入力して下方にドラッグコピーします。 =IF(AND(ISERROR(FIND(Sheet2!$A$1,D2)),ISERROR(FIND(Sheet2!$A$1,F2))),"",MAX(M$1:M1)+1) これでD列およびF列の口座名にシート2で指定された銀行名があればその行については上から順に番号が振られます。 次にシート2のA3セルには次の式を入力しL3セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。 =IF(OR(COUNTIF(Sheet1!$M:$M,ROW(A1))=0,A$2=""),"",INDEX(Sheet1!$A:$L,MATCH(ROW(A1),Sheet1!$M:$M,0),COLUMN(A1))) これでシート1でA1セルで指定された銀行名を含む口座名の行すべてがシート2に表示されることになります。

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

こんばんは! 参考になるかどうか判りませんが・・・ 現金と口座に関しては「収入科目」と「支出科目」が逆になると思いますので ↓に画像をアップしてみました (画像が小さくて見えにくいかもしれません) Sheet1が現金出納帳でSheet2が「A銀行」のSheetだとします。 Sheet2に作業用の列を使わせてもらっています。 Sheet2のA4セルに =IF(OR(Sheet1!D4=Sheet2!$B$1,Sheet1!F4=Sheet2!$B$1),ROW(A1),"") という数式を入れ、オートフィルで下へずぃ~~~!っとコピーします。 (Sheet1と同じ行くらいまでコピーしておきます) B4セルに =IF(COUNT($A$4:$A$100)<ROW(A1),"",INDEX(Sheet1!$A$4:$A$100,SMALL($A$4:$A$94,ROW(A1)))) C4セルに =IF(B4="","",VLOOKUP(B4,Sheet1!$A$4:$B$100,2,0)) D4セルに =IF(B4="","",INDEX(Sheet1!$K$4:$K$100,B4)) E4セルに =IF(B4="","",INDEX(Sheet1!$J$4:$J$100,B4)) という数式を入れ、B4~E4セルを範囲指定し、E4セルのフィルハンドルで 下へコピーすると画像のような感じになります。 (とりあえずSheet1の100行まで対応できるようにしていますが、 データ量によって範囲指定の領域はアレンジしてみてください。) 尚、Sheet1が空白の場合は「0」が表示されますので 当方使用のExcel2003の場合ですが、 メニュー → ツール → オプション → 表示タブで「ゼロ値」のチェックを外しています。 以上、長々と書きましたが 参考になれば幸いです。 他に良い方法があれば読み流してくださいね。m(__)m

  • tadagenji
  • ベストアンサー率23% (508/2193)
回答No.1

検索範囲が元帳の行数自然増の関係から、関数は固定できません。 関数は設定したセルをBASEに他のシートを参照できますが、その逆はできません。 VBAで処理するか、元帳から全てアクセスを使ってクエリでした方がよろしいようで。

関連するQ&A

  • エクセルで収支報告書をつくるのですが

     前年度の収支報告書を今年度のあたまに作成します。支出は支出があった日付とその金額を行に列にはその費目が書かれていて該当するところにも金額を書くという表を作成し、最後にその年度の総支出とそれぞれの費目ごとの支出合計が出る表を作成しておいて、それを元に報告書として年度の収入合計、支出合計、預金残高を記入するという形を取っているのですが。前者の表に金額を入力していくと自動的に報告書の支出合計欄にも加算されていくようにした方が、計算間違いなども減るし、修正する時も同時にできると思うのですがどうすればできますか?

  • Excelの関数について教えてください!

    Excel について教えてください。 エクセルで預金通帳のような表を作成したいと思っております。 その際に「残高欄」を自動的に計算するように作成したいのですが、その時の関数を教えてください。 A1に「収入」、A2に「支出」、A3に「残高」として何行も以下に続けていきたい場合に、A3の残高欄には収入があった際も、支出があった際も、「残高」を計算したいのですが、通帳のような場合は収入・支出のどちらかにしか金額が入らないと思うのですが、その際に「残高」を表示さすにはどのような関数を入力すれば いいのでしょうか? おそらく「条件設定」として、【IF】を使ってどちらかに数字が入力された場合に一つ上の行の残高から足したり、引いたりさせる設定(条件)を入力すると思うのですが、どのように条件設定すればいいのでしょうか? 日本語で言うと、【もし「収入欄に数字の入力」があれば残高に加える、もし「支出欄に数字の入力」があれば残高から差し引く・・・】 このような感じで関数を作成できればと思います。 宜しくお願い致します。

  • Excel でシートをコピーしたいが、余分なデータまでコピーされてしまう。

    エクセル初心者です。「Excel2003」と「Excel2000」を使っています。主に2003を使っています。 エクセルで出納帳を使っています。摘要欄には収入や支出の項目がテキストで入っています。収入や支出の欄には関数が入っていて、収入金額や支出金額を入力すると残高か計算されるというシンプルなものです。 このシート(シート1)を同じブックの別のシート(シート2)にコピーしたいのです。しかし必要なのは関数の入った、いわば数値を入力する前の白紙のシートです。ところがコピーして出来たシート2の範囲を選択してDelキーを押すと、テキストや値ばかりか関数まですべて消えてしまいました。そこでシート1の表全体を選択してコピーシート2に「形式を選択して貼り付けー数式」でコピーしても他のテキストや値のようなものまですべてコピーされてしまいます。 シート1の関数のみの入った白紙のシート2を作るにはどのようにすればよいのでしょうか。初歩的なモノだと思うのですが、困っています。よろしくお願いします

  • 【エクセル】シートからシートへの差し込み

    エクセルの使い方でわからないので教えてください。 初心者ですので、VBAやマクロといったことはまったくわかりません。。。 シート1に顧客データ約400件、シート2に表があります。 シート1の中の 銀行名、支店名、種目、口座番号、口座名義、フリガナ、振込金額を シート2の振込送金依頼書に15件づつ差込していきたいのです。 顧客データは1行に1顧客分なのですが、 振込送金依頼書は1件分が2行になっていて、 銀行名、支店名、種目、口座番号、振込金額の欄は2行のセルを結合していて、口座名義とフリガナ部分が2行に配置されており、 セルのコピー機能ではうまくできません。 この、口座名義とフリガナ部分を簡単に差込していきたいのです。 説明が下手で申し訳ないのですが、お分かりになる方教えてください。

  • エクセル 指定文字行のみの数値の合計

    エクセルで簡単な家計簿を作っています。 月日、内容、収入、支出、残高のみのシンプルなものです。 内容欄に“貯金”と書かれた行の金額を1ヶ月分の合計で出すことは可能でしょうか? ちなみに月ごとに列を変えています。 宜しくお願いします。

  • エクセル 別シートから一覧を抽出したい

    部品一覧表を作成しているのですが、2シートあり 1入力シート・2部品コードシートとあります。 部品コードシートにはB列に部品番号・C列部品名・D列部品番号・E列部品名と2行ずつ使い番号・品名がB~Wまで47行分入力されています。 B1:C47、D1:E47…と2列47行にはそれぞれ【A】、【B】、…とセルの名前の定義をつけました。現在【H】までありますが、今後増える可能性があります。 入力シートに、セルの名前を指定したときに部品コードのシートから 一覧を抽出したいのですが、どの関数を使えばよいのかわかりません。 入力シート                 |部品コードシート G   H                  | A Bコード C部品名  Dコード E部品名 4式入力用にあけています。     |1 1800  ユニットA   1501 電源A 5コード 部品名             |2 1801  ユニットB   1502 電源B 6                       |3 G4に関数を入れてG6~G52まで部品コードのシートA1~A47を一気に表示 させたいのです。部品コードシートの行数が変わることはありません。 マクロを使わないと、関数では難しいでしょうか?

  • エクセル 出納帳の合計の非表示

    すみません。どうしても見つからないので質問します。 簡単なことですが、「出納帳」では左から、日付、項目、引出金額、預入金額、残高 とあります。 残高に「="(上の行の)残高"-"引出金額"+"預入金額"」と入れ、その残高のセル を下方にコピーすると、まだ金額を入れていない日付の行のすべてに、現在の残高 がず~と表示されてしまいます。 なんか見ていて暑苦しいので、”金額”を入れた時にやっと”残高”が出るようにしたい のですが、可能でしょうか? よろしくお願いいたします。

  • エクセルの表を別のシートに縦に数行おきに書き移したい

    エクセルのSheet1  A商品名 B種別 C金額 D 説明 ・・・・ とまとめてあるデータを、別のシートの表に縦に数行おきに転記したいのです。たとえば sheet2の D3は空欄 D4にsheet1のA2 D5にsheet1のB2 D6にsheet1のC2 D7にsheet1のD2 D8にsheet1のE2 D9は空欄 そして、7行おきに違う商品のデータになり、繰り返します。 (A3~D9までを太いカラー罫線で囲んだフォーマットが商品個数分、空欄で既にsheet2に書き込まれています) 関数で効率よく、データを書き写すことが出来ますでしょうか? よろしくお願いいたします。

  • Excel 数を別のファイルのシートに入れたいです

    質問させて頂きます。 Excelのファイルで品名と数量があり、別のファイルでシート名に品名が入っていて、部品が何種類か入っています。今までは品名と数量を見て、別のファイルの部品のところに品名の数量を手打ちで数を入れていたのですが、何種類もの品名がある為、時間がかかるのが現状です。 どなたかよい方法を教えて頂けないでしょうか。 例を下に書きます。 (品名ファイル) 品名    数量 タンスA 5 タンスB 2 タンスF 3 (部品ファイル) タンスA(シート名) 部品名   数量 長引出 5 小引出    5 中引出 5 タンスB(シート名) 部品名   数量 中引出 2 小引出 2 タンスF(シート名) 部品名   数量 大引出    3 特引出    3 品名ファイルの品名、数量を見て、部品ファイルのシートに数量を反映させたいです。 なお、 A列、品名 B列、数量 A列、部品名 B列、数量が入っています。 宜しくお願い致します。

  • エクセルの条件付きの関数(IFなど)の使い方

    よろしくお願いします。 エクセルの表計算で、例えば、”おこづかい帳”のような、収入または支出を各行ごとに一番右の合計欄に自動で計算していく方法について質問します。 例として、3列2行の表で説明します。 A列は収入、B列は支出、C列は残高です。 一行目のA1、B1、C1は最初の一日目なのでC1セルだけに当初のお金500円が入力されています。二行目のB1またはB2の必ずどちらか1つだけに(収入か支出の)金額が入るとしてその時のB3の残高を自動で算出したいです。(実際の表は、それの繰り返しで何行も下に続いていきます。)B1に収入が200円あれば、B3は700円になり、B2に支出200円があれば、B3は300円になるような計算です。 次のような三つの条件になります。 (1)B1(収入)が空欄の時はB2(支出)の値を用いて、B3=A3-B2(2)B2(支出)が空欄の時はB1(収入)の値を用いて、B3=A3+B1 (3)B1、B2ともに空欄の時は、なにも計算しない(B3も空欄になる) そして、B3に入れる式としてこのような式を作りました。 =IF(OR(B1="",B1>0,B1B2=""),(B3=A3-B2,B3=A3+B1,B3="")) しかし、うまく計算されません。 どこを直せばよいでしょうか?またはIF以外の関数で必要なものがあるのでしょうか?

専門家に質問してみよう