Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
空白行のある伝票の集計
投稿日時: 19/12/13 00:43:48
投稿者: rt810jun

次のようなシートがあります。(日締めのため、行数は随時変わります。)
 
 
シート1
     A    B     C     D     E     F
1  伝票番号   清算方法  商品   単価   個数   金額
2    1    現金   お茶    200    1     200
3    1        ジュース   150    3    450
4    1         弁当     550    4    2200
5    2    カード  おにぎり   300    2    600
6    2          お茶    200    1    200
7    3    現金   お茶    200    3    600
8    4    カード  ジュース   150    5    750
9    5    カード   お茶    400    2    800
10    5         弁当    550    2    1100
 
 
シート2
     A     B    C    D    E    F
1  清算方法   お茶  ジュース  おにぎり  弁当   合計
2   現金    800    450         2200   3450
3   カード   1000    750    600    1100   3450
4   合計    1800   1200    600    3300   6900
 
 
シート2に現金精算とカード清算を別々で集計したいのですが、清算方法が伝票番号の一番上の行にしか表示されていません。
抽出で集計しようにも3行や4行などのように清算方法が空白になっているため、現金かカードか判断付けができません。
行数が最大で300ほどになりますが、今までほぼ手入力でB2をB3、B4にコピペして伝票1は現金清算として集計していましたが、何せ手作業なのでミスがたびたび起こってしまいます。
毎日このようなシートが出てくるので、関数等で何か良い方法がございましたらご教授ください。

回答
投稿日時: 19/12/13 01:21:54
投稿者: Mike

範囲 B2:B10 を選択 ⇒ Ctrl+G ⇒ [セル選択] ⇒“空白セル”に目玉入れ ⇒ [OK] ⇒ F2 をツン ⇒ キーボードで =B2 を入力したら単にEnterでなくCtrl+Enterを「エイヤッ!」と叩き付けたら如何かと。

回答
投稿日時: 19/12/13 09:28:55
投稿者: メジロ2

Sheet1のH列(任意)を作業列にし、清算方法をすべての行に準備すると
式がシンプルになります。
 
Sheet1!H2: =IF(C2="","",LOOKUP("ー",$B$2:B2))
 
必要数、下にコピーします。
 
Sheet2!B2: =SUMIFS(Sheet1!$F$2:$F$10,Sheet1!$H$2:$H$10,$A2,Sheet1!$C$2:$C$10,B$1)
 
セルE3までコピーします。
 
縦横の合計はSUM関数で。

回答
投稿日時: 19/12/13 11:14:23
投稿者: んなっと

最初だけ面倒な方法。
 
 データ
→テーブルまたは範囲から
→先頭行をテーブルの見出しとして使用する にチェック
→OK
→詳細エディター
→以下に書き換える
 
let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    Fid = Table.FillDown(ソース,{"清算方法"}),
    Del = Table.SelectColumns(Fid,{"清算方法", "商品", "金額"}),
    Piv = Table.Pivot(Del, List.Distinct(Del[商品]), "商品", "金額", List.Sum),
    Tot = Table.AddColumn(Piv, "計", each List.Sum(Record.FieldValues(Record.SelectFields(_, List.Difference(Table.ColumnNames(Piv), {"清算方法"})))))
in
    Tot
 
→閉じて読み込む
 
元データが変わったら データ→すべて更新
※赤字部分は状況によってテーブル2などに変わります。

回答
投稿日時: 19/12/13 12:14:03
投稿者: Mike

Mike さんの引用:
範囲 B2:B10 を選択 ⇒ … ⇒キーボードで =B2 を…たら如何かと。
の続きです。
上記を実行したら下図を得ることになりますが、…
  A     B    C   D  E   F
1 伝票番号 清算方法 商品   単価 個数 金額
2    1 現金   お茶   200  1  200
3    1 現金   ジュース 150  3  450
4    1 現金   弁当   550  4 2,200
5    2 カード  おにぎり 300  2  600
6    2 カード  お茶   200  1  200
7    3 現金   お茶   200  3  600
8    4 カード  ジュース 150  5  750
9    5 カード  お茶   400  2  800
10    5 カード  弁当   550  2 1,100
[条件付き書式]を施せば、難なく、下図のように「表示」させることも可能です、為念。
  A     B    C   D  E   F
1 伝票番号 清算方法 商品   単価 個数 金額
2    1 現金   お茶   200  1  200
3    1     ジュース 150  3  450
4    1     弁当   550  4 2,200
5    2 カード  おにぎり 300  2  600
6    2     お茶   200  1  200
7    3 現金   お茶   200  3  600
8    4 カード  ジュース 150  5  750
9    5 カード  お茶   400  2  800
10    5     弁当   550  2 1,100

回答
投稿日時: 19/12/13 12:24:20
投稿者: んなっと

メジロさんの方法と前半ほぼ同じですが、こんな方法も。
 
      A     B     C   D   E   F    G
 1 伝票番号 清算方法   商品 単価 個数 金額  清算
 2     1   現金   お茶  200   1  200  現金
 3     1      ジュース  150   3  450  現金
 4     1        弁当  550   4 2200  現金
 5     2  カード おにぎり  300   2  600 カード
 6     2        お茶  200   1  200 カード
 7     3   現金   お茶  200   3  600  現金
 8     4  カード ジュース  150   5  750 カード
 9     5  カード   お茶  400   2  800 カード
10     5        弁当  550   2 1100 カード
 
G2
=IF(B2="",G1,B2)
下方向・↓
 
(→テーブルとして書式設定 お好みの書式)
→挿入
→ピボットテーブル
→OK
→「商品」を[列]に、「清算」を[行]に、「金額」を[Σ値]に
→ピボットテーブルツール デザイン 総計▼
→行と列の集計を行う

回答
投稿日時: 19/12/13 12:52:56
投稿者: んなっと

少し変更。
 
→以下に書き換える
  
let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    Fid = Table.FillDown(ソース,{"清算方法"}),
    Del = Table.SelectColumns(Fid,{"清算方法", "商品", "金額"}),
    Piv = Table.Pivot(Del, List.Distinct(Del[商品]), "商品", "金額", List.Sum),
    Tot = Table.AddColumn(Piv, "計", each List.Sum(Record.FieldValues(Record.SelectFields(_, List.Difference(Table.ColumnNames(Piv), {"清算方法"}))))),
    Hdr = Table.ColumnNames(Tot),
    Lst = Table.FromRows({List.Transform(Hdr, each List.Sum(Table.Column(Tot, _)))}, Hdr),
    Lst2 = Table.ReplaceErrorValues(Lst,{"清算方法","合計"}),
    Cmb = Table.Combine({Tot, Lst2})
in
    Cmb

投稿日時: 19/12/13 17:29:28
投稿者: rt810jun

みなさん、たくさんのご回答ありがとうございます。
 
おかげでおもっていた通りのことができました。これで、手入力しなくて済みます。
ありがとうございました。
 
マクロはまだやったことがなかったので、これから少しづつ覚えていこうと思います。
またちょくちょく質問させていただくと思いますが、よろしくお願いいたします。