エクセル2013 表の更新について

質問

こんにちは。

エクセルでできるか、できるのであれば方法を押してていただきたいです。
使用しているのはエクセル2013でパワークエリは導入済みです。
マクロはほぼわかりません。

店舗ごとの売り上げ合計のリスト(1)があるとします。
毎月累計が集計される別のエクセルファイル(2)があります。
基本同じ書式ですが、(1)には列を追加し、店舗ごとの情報を載せています。
(1)に(2)を上書きして売り上げ累計を更新したいのですが、店舗は追加されたり削除されたりするので、(1)に追加した店舗ごとの情報を同じ行で維持したまま、(1)にない店舗は行を追加し挿入、(1)に既存の店舗は売り上げ累計列を上書き、ということは可能でしょうか？
毎月店舗リストが増減するので、そのまま貼り付けるだけだと追加した店舗情報がずれて使い物にならなくなってしまいます。

毎月の作業なのでテーブルやクエリを使ってルーチン化できないかと考えています。

わかりづらい文ですが、よろしくお願いします。

回答No.6

kkkkkm · ベストアンサー

No3とNo4を使って減に対応できなかったのですが以下の対応でできる感じです。
(1)の
店舗データがA2からだとA2に
=IFERROR(IF(Append1[@店舗]=0,"",Append1[@店舗]),"")
累計データがB2からだとA2をコピーでそれ以外の列だと以下の式を
=IFERROR(IF(Append1[@累計]=0,"",Append1[@累計]),"")
として店舗数分だけコピーしてください。Append1はNo4でできたテーブルです。

お礼

いえいえ、わざわざありがとうございました。
おかげで光が見えてきました。

回答No.5

kkkkkm

No3とNo4は(2)の店舗が減った場合の事を考えていなかったのでうまくいきませんでした。ですので、無視してください。

回答No.4

kkkkkm

No3です。

ソースより手順で説明したほうがわかりやすいですね。

パワークエリで(1)と(2)のテーブルを作成したあとで
パワークエリで「追加」をします
主テーブルを(1)のテーブルに
追加するテーブルを(2)のテーブルに
店舗が重複したデータができるので「グループ化」をします。
グループ化　店舗
新しい列名　累計など適当に
操作　最大
縦棒　テーブルの累計項目名

です。

回答No.3

kkkkkm

> (1)のリストにない店舗が(2)で新規で増えた時にVLOOKUPでは対応できないのかなと・・・

そのままだと駄目なので手間がかかりますが、(1)にすべての店舗を入れておいて、VLOOKUPで当月累計を出す列を作成し、0以外の列を累計列にコピペして上書きする(フィルーターで0以外を表示して)という事を考えてました。

パワークエリで毎月累計を上書きしていくという考えだとしたら

パワークエリで(1)と(2)のテーブルを作成します。
(1)はすべての店舗を入れておきます。

クエリエディタで以下のクエリを上記のクエリに追加してあとは毎月データの更新をすればいけるのではないでしょうか。
累計は毎月増加すると思いますから単純にMAXで取れると思います。
(1)がテーブル1で
(2)がテーブル3です。他項目名など適宜変更してください。

let
ソース = Table.Combine({テーブル1, テーブル3}),
グループ化された行 = Table.Group(ソース, {"店舗"}, {{"累計", each List.Max([累計]), type number}})
in
グループ化された行

お礼

再度の投稿ありがとうございます。

なるほど先に全てをリスト化しておけばいいわけですね。
合わせて新規店舗の追加があるかないかは同じくVLOOKUPかCOUNTIFあたりで洗い出して都度追加する方法で。

テーブルと関数だけで行けそうな雰囲気ですので、まずこれで試してみて、その後クエリも使ってみたいと思います。

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

回答No.2

imogasi

質問のデータ状態が、よくわからない。
自社でも、この質問文章を読んで、内容が判る（他人の）人いますか。
ーー
小生は、いつ言っているが、例（サンプル）データぐらい作れる力がないと、質問して回答があっても理解できないだろう。エクセルの中での、質問点のポイントの位置づけをつかめる力がないということだから。
データ例を挙げないから、他人にわかりにくい。
２，３か店の例にして＋追加店１、2か店で列項目は売り上げとその累計に限定して、２，３カ月の例を挙げるのはどうかな。
２つの表が問題になっているようだから、２表と、それにもとずく、望む最終形の表（の主に行データ）の例だ。
それに合計や累計の言葉は、中身が何か正確に使っているか？。
合計は店内での合計、他店を含めての合計、累計は自店での累計など、合計と紛らわしいから、使用を混同してないか？
＞店舗ごとの売り上げ合計のリスト(1)があると
合計は、販売品目を合計した計数ということか？
＞(1)に(2)を上書きして
どういうこと？イメージ的には「上書きして」だろうが、上書きとは？
普通は、第3の資料（シート）で実現する。オフラインバッチ処理の
原則だ。
そのとき、マッチングのロジックが使われたりする。
この処理のためには、プログラムを使う。
参考　Googleで「ファイルのマッチング」で照会して、出てくる記事を２，３読んだらどうか。
＞売り上げ累計列
とは売上合計！と違うのか？
であれば単月と期初来とか表現すべきだろう。
＞上書き
行マージ（しかるべき行の場所の位置に挿入）のことを言ってないか。
＞毎月店舗リストが増減する
店の新設や廃店や店の統廃合があるということか。
在庫管理システムでも、商品でそういうことは起こるが。
＝＝＝＝
結論として、VBAもできないのに、仕事でエクセルを使うなんて無理だよ。小生の持論として、仕事でエクセルを使うときはVBA利用は必須だと思う。
Excelには、プログラムを使わないなら、「統合」という機能や、下にデータ行を累加して、店番＋年月＋その他、などで「並べ直し」するしか、初歩的な機能では、小生は知らない。

お礼

imogasi様、ありがとうございます。
参考にさせていただきます。

ただ一つ、VBAもで気ないのに云々ですが、局所的な仕事ではそうかもしれませんが、おおよそ一般的な事務仕事でVBAまで使わないとできない仕事は少ないですし、逆にマクロのせいで汎用性がなくなって、社内で混乱の元になるんです。程度の低い会社だと言われればそこまでなんですが(笑)
エクセルのマクロ以外の部分だけを仕事に使っている(それで十分な)人はかなり多いと思いますよ。

回答No.1

kkkkkm

いまいち具体的な状態が分からないのですが
店舗の増減とは、店舗ごとにデータがある月とない月があるという事で店舗自体は存在し、別の月にはデータが出てくるということでしょうか。(1)には列を追加というのも月ごとに追加していくのでしょうか。としたら上書きにはならないような…
たとえば、合計のリスト(1)とは別のシートに新しいエクセルファイル(2)のデータをコピーして
売り上げ累計列の上書きをVLOOUP関数で処理すればできるのではないでしょうか。

補足

kkkkkm様、ありがとうございます。

>店舗の増減とは、店舗ごとにデータがある月とない月があるという事で店舗自体は存在し、別の月にはデータが出てくるということでしょうか。

はい、その通りです。
売り上げがない店舗はリストに含まれてこないだけで、売り上げのある月には再度リストに載ってきます。

>(1)には列を追加というのも月ごとに追加していくのでしょうか。としたら上書きにはならないような…

月ごとではなく(1)にも(2)にもあるのは累計だけです。
(2)の累計に(1)+当月が含まれてるイメージです。

>たとえば、合計のリスト(1)とは別のシートに新しいエクセルファイル(2)のデータをコピーして売り上げ累計列の上書きをVLOOUP関数で処理すればできるのではないでしょうか。

(1)のリストにない店舗が(2)で新規で増えた時にVLOOKUPでは対応できないのかなと・・・