- ベストアンサー
エクセルのデータを別シートでカレンダー形式に
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
Sheet3のD4に =SUMPRODUCT((Sheet2!$A$2:$A$3=$A4)*(Sheet2!$D$2:$D$3=DATE($D$1,$D$2,D$3))*(Sheet2!$E$2:$E$3="東京")*(Sheet2!$C$2:$C$3)) D5に =SUMPRODUCT((Sheet1!$A$2:$A$8=$A4)*(Sheet1!$D$2:$D$8=DATE($D$1,$D$2,D$3))*(Sheet1!$E$2:$E$8="東京")*(Sheet1!$C$2:$C$8)) と入力し、このD4:D5の二つのセルを縦横必要な範囲まで複写してください。 仕入および売上の実績が無い場合に「0」が表示されますが 条件付き書式で「値が0と等しい場合、フォントカラーを白」などに 設定しておけば見えなくなります。 D4の式の「$A$2:$A$3」「$D$2:$D$3」「$E$2:$E$3」「$C$2:$C$3」 D5の式の「$A$2:$A$8」「$D$2:$D$8」「$E$2:$E$8」「$C$2:$C$8」 の部分は、実際のデータの行数に合わせて変えてください。 データが増えていく場合は、最大でこのくらいの件数だろうという 大きめの数字をあらかじめ設定しておけばいいです。 ちなみに上記の式では、"東京"で固定しています。 Sheet4に式を入れる際には、"東京"を"福岡"に変えなければいけませんが 例えば「Sheet3~Sheet7はA1セルに取扱店を入れる」ようにすれば 「(Sheet2!$E$2:$E$3="東京")」の部分を「(Sheet2!$E$2:$E$3=$A$1)」 として、シートごと複写してA1セルのみ変更、で済むので少し楽だと思います。
その他の回答 (4)
- don9don9
- ベストアンサー率47% (299/624)
No.1です。 お使いのExcelのバージョンは何でしょうか? もしExcel2007以降のバージョンをお使いなら、SUMIFS関数が追加されていますので こちらを試してみてください。 =SUMPRODUCT((Sheet2!$A$2:$A$3=$A4)*(Sheet2!$D$2:$D$3=DATE($D$1,$D$2,D$3))*(Sheet2!$E$2:$E$3="東京")*(Sheet2!$C$2:$C$3)) は、SUMIFS関数に置き換えると =SUMIFS(Sheet2!$C$2:$C$3,Sheet2!$A$2:$A$3,$A4,Sheet2!$D$2:$D$3,DATE($D$1,$D$2,D$3),Sheet2!$E$2:$E$3,"東京") となります。 Excel2003以前のバージョンをお使いの場合は… すみません、ちょっといい方法が思いつきません。 Excelのオプションで計算方法を手動にすることくらいでしょうか。 ツール>オプション>計算方法のタブで、計算方法を手動にすると データを1件入力する度に再計算が行われて、計算が終わるのを待たなければならない ということはなくなるかと思います。 (都度、F9キーで再計算させないといけなくなりますが)
- 某HN クロメート(Chromate)(@CoalTar)
- ベストアンサー率40% (705/1742)
添付図の2行目と4行目のような重複はないものとして F2セルに =D2&E2&A2 下へオートフィル K2セルに =IF(ISNA(MATCH(DATE($K$1,$K$2,K$3)&$I$1&$H4,$F:$F,0)),"", INDEX($C:$C,MATCH(DATE($K$1,$K$2,K$3)&$I$1&$H4,$F:$F,0))) 右へオートフィル 選択したままコピーして K4セルに貼り付け 売り上げも仕入も同様です
- imogasi
- ベストアンサー率27% (4737/17068)
こういう表の組み換え課題を、関数でやろうとするのは無理があります。 例えば7月2日の鉛筆の売上があったとき、統合したシートのどの行に来るか、それ以前の日のデータの多少次第という 点が扱いにくいのです。 又この質問では、仕入れのデータ行も売上の中に割り込んできますのでますます複雑になります。 マジックや鉛筆の項目は、発生後、表示行が固定されるので、新規に販売された品物は最後の次に回るようだが、これも関数では難物。 毎日データが増えて、過去のものは位置を動かさないのも難物。 ーー このタイプは、VBAを使う課題です。勉強してください。VBAを使えないものが、エクセルを仕事絡みで使おうとすると、直ぐ無理が来る・行き詰まる、と思います。 ただしこの程度になると、VBAを少し学習してもやや難しいと思うが。 ーー 質問の添付画像例が、ぼやけてよく見えないが、売上と仕入れとが同じシートにあるほうが、まだやりやすいかも。 ーー 筆のすさみに途中までだがやってみた。データ配置も質問とは変えている。 Sheet1 日付順とする -- 売上 7月1日 鉛筆 30 7月1日 消しゴム 40 7月1日 定規 10 7月3日 紙 10 7月3日 はさみ 5 7月3日 半紙 5 7月3日 ボールP 25 Sheet2 日付順とする -- 仕入 7月1日 鉛筆 20 7月1日 消しゴム 20 7月1日 定規 5 7月2日 帽子 20 7月3日 紙 10 7月3日 はさみ 30 7月3日 チョーク 5 7月3日 半紙 10 7月4日 ボールP 5 結果 売上か仕入れした物商品の一覧表 Sheet3 鉛筆 売上 鉛筆 仕入 消しゴム 売上 消しゴム 仕入 定規 売上 定規 仕入 紙 売上 紙 仕入 はさみ 売上 はさみ 仕入 半紙 売上 半紙 仕入 ボールP 売上 ボールP 仕入 帽子 売上 帽子 仕入 チョーク 売上 チョーク 仕入 このC列以右列に日付列に応じてデータをセットしていく(略) 上記で Sub test01() Dim sh1, sh2, sh3 Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") Set sh3 = Worksheets("Sheet3") '表に項目を用意 d = InputBox("処理開始日") dt = DateValue(d) g1 = sh1.Range("A:A").Find(dt).Row MsgBox g1 lst1 = sh1.Range("A65536").End(xlUp).Row lst2 = sh2.Range("A65536").End(xlUp).Row lst3 = sh3.Range("A65536").End(xlUp).Row k = lst3 + 1 MsgBox lst1 For i = g1 To lst1 MsgBox sh1.Cells(i, "B") Set fnd = sh3.Range("A:A").Find(sh1.Cells(i, "B")) If Not fnd Is Nothing Then Else sh3.Cells(k, "A") = sh1.Cells(i, "B") sh3.Cells(k, "B") = "売上" k = k + 1 sh3.Cells(k, "A") = sh1.Cells(i, "B") sh3.Cells(k, "B") = "仕入" k = k + 1 End If Next i '--- g2 = sh2.Range("A:A").Find(dt).Row For i = g2 To lst2 MsgBox sh2.Cells(i, "B") Set fnd = sh3.Range("A:A").Find(sh2.Cells(i, "B")) If Not fnd Is Nothing Then Else sh3.Cells(k, "A") = sh2.Cells(i, "B") sh3.Cells(k, "B") = "売上" k = k + 1 sh3.Cells(k, "A") = sh2.Cells(i, "B") sh3.Cells(k, "B") = "仕入" k = k + 1 End If Next i End Sub d = InputBox("処理開始日")のところで、間違いないように未反映の最初日を指定する。 これでもSheet3での商品の並べ順など注文がつけば、さらに複雑になる。 上記コードは、処理方式は人によってもっと優れたやり方があると思う。
- KURUMITO
- ベストアンサー率42% (1835/4283)
シート1のF列は作業列としてF2セルには次の式を入力して下方にオートフィルドラッグします。 =IF(A2="","",E2&D2*1000+A2) シート2も同様にF2セルには上と同じ式を入力して下方にオートフィルドラッグします。 東京のシートではA1セルに東京と入力します。 そのほかはお示しの表と同じとしてA,B,C列については予め入力されているものとします。 その上でD4セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にオートフィルドラッグします。 =IF($C4="","",IF(MOD(ROW(A1),2)=1,IF(COUNTIF(Sheet3!$F:$F,$A$1&DATE($D$1,$D$2,D$3)*1000+$A4)=0,"",INDEX(Sheet3!$C:$C,MATCH($A$1&DATE($D$1,$D$2,D$3)*1000+$A4,Sheet3!$F:$F,0))),IF(MOD(ROW(A1),2)=0,IF(COUNTIF(Sheet2!$F:$F,$A$1&DATE($D$1,$D$2,D$3)*1000+$A3)=0,"",INDEX(Sheet2!$C:$C,MATCH($A$1&DATE($D$1,$D$2,D$3)*1000+$A3,Sheet2!$F:$F,0)))))) このシート全体をコピーして福岡のシートやその他のシートに貼り付けます。 それらのシートではA1セルに例えば福岡とかの名前を入力すればよいでしょう。
関連するQ&A
- Excel,マクロ別シートからフォームに名前付保存
以下通りの作業を自動化するVBAに関する質問です。 <Excelバージョン> Excel2013 <状況> 「Sheet1」に契約No.(連続数字でない)ごとのデータリスト。1行に1契約No. 「Sheet2」に定型帳票。A1セルに契約No.を入力。各項目は契約No.に紐づくVLOOKUP関数で可変となっている。 <必要仕様> 1クリックマクロで 「Sheet1」の全データを1行ずつ「Sheet2!A1」に契約No.を当てはめ、それぞれの「契約No.」をファイル名にして、指定フォルダに、1枚ずつPDF形式で保存。 このような仕様を満たすVBAをご教示いただけると幸いです。
- ベストアンサー
- Excel(エクセル)
- エクセルの関数を別シートから
エクセルで関数を使うときに、別のシートからそのシートへデータをもってきて使いたいのですが‥ VLOOKUPを使うときに、となりのシート(別のシート)からデータをもってくるときには、どのように設定したらよいのでしょうか。
- ベストアンサー
- オフィス系ソフト
- Excelの別シートへの加算
仕入シートには 日付,商品No.,タイプA,タイプB,タイプC とそれぞれの項目があり、キーは商品No.で、タイプには数量が入っています。 同じ商品No.は複数存在します。 在庫シートには 日付を除いて同じ項目があるのですが、商品No.の重複はありません。 仕入シートを商品No.順にソートしたのですが、その後、在庫シートへの加算がうまくいきません。 仕入シートの一番上からデータが空になるまでの、タイプ別の数量を在庫シートに加算するには、どうしたらいいでしょうか? できれば、在庫シートに該当商品No.がない時に、仕入シートの空いている列に、「該当商品なし」と表示させたいので、そちらも合わせて教えてください。お願いします。
- ベストアンサー
- オフィス系ソフト
- 複数のシートから重複するデータを別シートに抜き出す
こんにちは。 Excel2007です。 複数のシート(現在はSheet1からSheet4ですが、最終的にはSheet10ぐらいまでになります)に蔵書データが入っています。 A列21行目から作者名、B列21行目から本のタイトル、C列21行目からページ数と以下、値段、ISBNなどJ列まで蔵書データが入っています。本棚ごとにSheet1、Sheet2と分かれて入力されています。 この複数のシートから本のタイトルが重複するものを別シートに抜き出すにはどうしたらいいでしょうか? 重複データを削除するマクロや関数は探せたのですが、複数のシートにまたがっている場合は探しきれませんでした。 何かいい解決方法があればご教授下さい。
- 締切済み
- その他MS Office製品
- エクセル関数の質問です。エクセル2007を使っています。
エクセル関数の質問です。エクセル2007を使っています。 シート1にたくさんの数値のリストがあり、シート2に別の順序でデータを持ってきた場合です。 例)シート1 A行にリファレンスナンバー901から1000まであるとします。1の列にたくさんの項目があります。 No. 面積積 価格 建築年 建築月 901 1000 10000 2007 1 902 15000 20000 2005 5 903 2000 500000 1998 9 904 3000 600000 1990 4 905 5500 100000 2010 12 ... シート2にVLOOKUPで、ランダムに並んだリファレンスナンバーに並べ替えるのですが、列番号を入れ直すのが面倒です。というのは、項目が変わるにつれ、左クリックでドラッグ(?)しても列番号は最初の“2”のままなので、3,4,5と項目ごとに一つ一つ変えなければならないのです。 何かいい方法はないでしょうか? シート2 No. 面積 価格 建築年 建築月 905 =VLOOKUP(G2,$A$2:$E$6,2,0) よろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
- 複数シートのデータを別シートに結合させたい
複数シートのデータを別シートに結合させたい Excelの2つのシートに以下のようなデータがあります。 シート1 シート2 名前 金額1 名前 金額2 aaaa 100 aaaa 100 bbbb 100 cccc 200 これをシート3に新たに以下のように作りたいんです。 名前 金額1 金額2 aaaa 100 100 bbbb 100 cccc 200 シート1と2の名前は重複しているものもあり、 名前を基準にして重複しているものは、両方データを 重複してないものは、それぞれのデータのみをひろいたんですが、 件数が多いため、どのようにするのが一番効率よくできるのか わからないんです。 Accessへシートをインポートとして、クエリでなんとか できないものかと考えたのですが、いまいちうまくできません・・・。 元がExcelのデータなので、Excelの関数などを使って出来る方法が あればいいんですが、ExcelかAccessで効率よく出来る方法を 教えていただけないでしょうか? よろしくお願いします!
- 締切済み
- オフィス系ソフト
- Excelをデータベースのように使用した場合 データの抽出
Excelをデータベースのようにして使用している場合について教えて欲しいことがあります。 ・Sheetを2つ作成したとします。 ・Sheet1はたくさんのデータが入っているシートです。 ・Sheet1の名前を仮に『Data』とします。 ・Sheet2はSheet1から必要なデータを抽出してきて並べるシートです。 ・Sheet2の名前を仮に『Report』とします。 ・『Data』のA列には1~200までの数字が入力されています。 ・その数字は ランダムである上に 重複していることもあります。 ・例えばA1には「1」が入力されていて A2には「2」が入力されています。 ・しかし その後A3にもA4にもA5にも「2」が入力されています。 ・同様にA6~A15までは「3」が入力されているのです。 ・このようにA列の数字は 同じ数字が何度も重複しており その重複には規則性はありません。 ・『Report』シート上で『Data』シートからデータを抽出したいのですが A列に「1」と入力されているデータだけを取り出すのなら「VLOOKUP」でもできますよね・・・・ ・しかし A列に「2」と入力されているデータを「VLOOKUP」関数で選んでも その先頭行のデータしか読み込んできません。 ・『Data』シートのA列に入力している数字から 必要な数字が入力されている行のデータだけを 全て『Report』シートに並べようとした場合 手作業で行うとすると・・・ 『Data』でA列が「1」のデータだけを選び,それを『Report』シートに貼り付ける。次は「2」で実行する。・・・・・という作業を200回繰り返すことになりますが 簡単に行うにはどのようにすればいいのでしょうか? マクロについては あまり詳しくないので できれば関数を使いたいのですが 無理でしょうか? マクロを使うのであれば 分かりやすく教えていただきたいのですが・・・ 勝手なお願いで申し訳ありませんが よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 元データを別シートへ、種類で振り分ける。
元データとして、以下のような表があります。 受付No. 室No. 申込日 利用日 利用料 利用時間 団体名 これを別シートの室No.ごとの表に振り分けたいのです。 できれば、元データに入力すると連動して室No.の表も更新されるようにしたいのですが、どのようにすればいいのでしょう。 元データの室No.は1~3までランダムに入力してあります。 そこでIFとVLOOKUPなどで試してみましたが、上手くいきません。 VBAでのDO~LOOPを使うのがいいような気がするのですが、VBAはまったくの初心者で参考書を読んでいるのですが、よくわかりません。 できれば関数で処理をしたいのですが、無理ならばVBAでも構いません。 振り分けるのにいい方法を教えていただけますでしょうか。
- 締切済み
- オフィス系ソフト
- エクセルで別シートをマッチングする方法
エクセル2007のマッチングに関する質問です シート1にはA列から店コード、4月売上、5月売上・・・・年間売上合計が入力されています。 シート2には同様のフォーマットで仕入れ合計、シート3には広告費合計が入力されています。 これらの3つのシートを店コードをキーにして売上-仕入れ-広告費を シート1-3と同様のフォーマットでシート4に作りたいのですが、関数を使って簡単に出来ますか? ご教授のほどよろしくお願いします
- 締切済み
- その他MS Office製品
- カレンダー作成 別シートよりデータ反映
目的は仕事で何日に誰が休むむかを一目で知りたくてエクセルで作成したカレンダーを作りました(縦型日付表示) 現在は届けの用紙が来たらカレンダーに手打ちしてます。 (シート1) 種類は名前に色分けをしたかったので種類を入れてます。 (上手く作成できたら種類によって名前に色わけとかしたくて種類を作りました) 横に名前が出るようにしてます。同じ日に休みの人が増えれば HIJKと横に続きます。 A B C D E F G 1 日付 曜日 名前 種類 名前 種類 2 1日 桃 有給 (みかん) (休み) 3 2日 4 3日 以下日にちは月末まであります。 別のシート(シート2)で作成したデータを反映させたいのですが同じ日付でデータがある場合 どうすれば反映されるのでしょうか?ためしにD3にカウント式(=COUNTIF($A$3:$A3,$A3))を入れて 順番をつけてみました。 A B C D 1 日付 名前 種類 カウント 2 5/1 桃 有給 1 3 5/3 奈々 半日 1 4 5/1 みかん 休み 2 以下休み届けなどが出されたら随時追加します。 わからないなりにネットで見て数式を入れてみました。 シート1のC2に =IF(ISERROR(VLOOKUP(A2,シート2!$A:$C,2,0)),"",VLOOKUP(A2,シート2!$A:$C,2,0)) C3に =IF(ISERROR(VLOOKUP(A2,休み!$A:$D,3,0)),"",VLOOKUP(A2,休み!$A:$D,3,0)) 入力すると始めの 5/1 桃 有給はカレンダーにあがるのですが5/1 みかん 休みを出せません あまりエクセルなどに詳しくなく、他の数式なども活用できるのかこういう表がそもそもエクセルでできるのかも勉強不足でわかりません。なんだかマネて作成したらあまりに長くて引数が足りませんみたいな表示がでました。 お手数をおかけしますが、教えていただけたら嬉しいです。よろしくお願い致します。
- ベストアンサー
- Excel(エクセル)
お礼
ありがとうございます。 うまくいきました…が、データ量が多すぎるのか、私の考えていることに無理があるのか かなり重くなってしまい、ファイルを開くだけで4、5分かかってしまいます。 1日のデータ量は仕入れ、売上合わせて200~300件程度。 テストの為、3日分入れてみました。 毎日データを追加せずに上書きするとしても、この作り方だと重いです。 何か解消方法はないでしょうか?