[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『連続していないデータの一部抽出方法』（わか）
[A] [B] [C] [D] [E] [F]… 1 勘定科目 4月度 5月度 6月度 第一四半期合計 7月度… 2 A商品売上 1,000 1,500 2,000 5,500 2,000 3 B商品売上 4 C商品売上 5 A売上原価 500 700 900 2,100 800 6 B売上原価 7 C売上原価 8 売上総利益 9 販売費 10 一般管理費 11 営業外費用
会計データの一部抽出を行いたいと思っております。
上記がデータ例で、A列に勘定科目、B列以降に各月の数値（なお三か月ごとに小計されていて連続していません）
このようなデータがある場合に、
A商品売上とA売上原価の
1.毎月の値を連続で抽出したい場合(4-3月度を途中の小計無しで表にしたい場合)
2.4-6月度の累計・4-7月度の累計
など、連続していないデータを抽出したい場合どのような関数を使うことが考えられるでしょうか？
なお、元データを編集することは現状考えていません。
3.また、プルダウンにて月を指定したら指定月のA商品売上とA売上原価の単月と累計の値が返されるような仕組みづくりはマクロではなく関数で可能なのでしょうか？
?B完成イメージ
[A] [B] [C] [D] [E] [F]… 1 5月度 ←プルダウンで対象月を指定 2 3 5月単月 5月累計 4A商品売上 1,500 2,500 5A売上原価 500 1,200
良いアイデアが浮かばず、教えていただけると幸いです。
もし質問の方法に足りない点がありましたらご指摘ください。
よろしくお願いします。
＜ 使用 Excel：Microsoft365、使用 OS：Windows10 ＞
商品名 |勘定科目 | 月度 | 金額
A商品 | 原価 | 4月 | 170,000
A商品 | 売上 | 4月 | 204,000
B商品 | 原価 | 4月 | 70,000
B商品 | 売上 | 4月 | 84,000
A商品 | 原価 | 5月 | 80,000
A商品 | 売上 | 5月 | 96,000
B商品 | 原価 | 5月 | 70,000
B商品 | 売上 | 5月 | 84,000
この表からこんな感じのピボットテーブルを作る
フィルターに商品名と月 行に勘定科目 Σ値に金額
商品名 A商品
月度 5月
行ラベル 合計 / 金額
原価 80,000
売上 96,000
かな
（とおりすがり） 2023/02/05(日) 21:35:51
残念ながら「商品名」という列項目が存在しないのです…。
またパワークエリによる加工において、少し触ってみましたが〜月度という列項目を元データから作成する方法がうまく思いつきませんでした。
ただしパワークエリの機能を用いたデータ加工については馴染みがないので、
私の能力不足な気がします。何かうまくできる方法を今後勉強してみたいと思います。
また仮に上記の通りに表が出来たとして、
ピボットテーブルを使った場合において、5月で集計した場合に
5月単月は可能であっても5月累計(4月と5月の合計)を導き出す方法が思い浮かびません…。
（わか） 2023/02/05(日) 22:17:59
＞「商品名」という列項目が存在しないのです…。
勘定科目の形式が
A売上原価
A商品売上
このように決まっているなら、勘定科目の列を選択して、変換＝＞列の分割＝＞文字数による分割して
文字数を4、できるだけ右側で1回にチェック
＞5月累計(4月と5月の合計)を導き出す方法
パワークエリの中で累計列を計算してしまえばいいような気がします
（とおりすがり） 2023/02/05(日) 23:57:22
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q]
[1] |勘定科目 |4月度|5月度 |6月度|第1四半期|7月度|8月度|9月度|第2四半期|10月度|11月度|12月度|第3四半期|1月度|2月度|3月度|第4四半期
[2] |A商品売上 |1,000| 1,500|2,000| 4,500|2,000|3,000|4,000| 9,000| 5,000| 6,000| 7,000| 18,000|8,000|9,000|1,000| 18,000
[3] |B商品売上 | | | | | | | | | | | | | | | |
[4] |C商品売上 | | | | | | | | | | | | | | | |
[5] |A売上原価 | 500| 700| 900| 2,100| 800| 900|1,000| 2,700| 400| 800| 300| 1,500| 600| 200| 100| 900
[6] |B売上原価 | | | | | | | | | | | | | | | |
[7] |C売上原価 | | | | | | | | | | | | | | | |
[8] |売上総利益| | | | | | | | | | | | | | | |
[9] |販売費 | | | | | | | | | | | | | | | |
[10]|一般管理費| | | | | | | | | | | | | | | |
[11]|営業外費用| | | | | | | | | | | | | | | |
[12]| | | | | | | | | | | | | | | | |
[13]|2月度 |単月 |累計 | |基準 |幅 |列 |行 |dummy | | | | | | | |
[14]|A商品売上 |9,000|48,500| | 14| 2| 15| 2| 31,500| | | | | | | |
[15]|A売上原価 | 200| 7,100| | 14| 2| 15| 5| 6,300| | | | | | | |
B12 =INDEX($A$1:$Q$11,MATCH($A14,$A$1:$A$11,0),MATCH($A$13,$A$1:$Q$1,0)) C12 =SUM(OFFSET(INDEX($A$1:$Q$11,MATCH($A14,$A$1:$A$11,0),$E$14),0,0,1,$F$14))+I14 E14 =IF(G14<=4,2,IF(G14<=8,6,IF(G14<=12,10,14))) F14 =MATCH($A$13,$A$1:$Q$1,0)-E14+1 G14 =MATCH($A$13,$A$1:$Q$1,0) H14 =MATCH($A14,$A$1:$A$11,0) I14 =SUMIF(OFFSET(INDEX($A$1:$Q$11,1,1),0,0,1,G14),"*半期",OFFSET(INDEX($A$1:$Q$11,H14,1),0,0,1,G14))
（黒豆） 2023/02/06(月) 08:16:41
＞ 少し触ってみましたが〜月度という列項目を元データから作成する方法がうまく思いつき
＞ ませんでした。
「5月度」と入力したセルを名前定義してそれを読み込むだけです。
基になる表を「データ範囲」。月度を入力するセルを「月度」と名前定義。
空のクエリの詳細エディタに下記貼り付け。
let
ブック = Excel.CurrentWorkbook(),
ソース = ブック{[Name="データ範囲"]}[Content],
ヘッダー昇格 = Table.PromoteHeaders(ソース),
上位行を保持 = Table.FirstN(ヘッダー昇格, each [勘定科目]<>null),
月度 = ブック{[Name="月度"]}[Content]{0}[Column1],
見出しリスト = [
見出し1 = Table.ColumnNames(上位行を保持),
位置 = List.PositionOf(見出し1, 月度)+1,
見出し2 = List.Select(List.FirstN(見出し1, 位置), each not Text.Contains(_, "合計"))
][見出し2],
列の選択1 = Table.SelectColumns(上位行を保持, 見出しリスト),
累計列の追加 = Table.AddColumn(
列の選択1, "累計",
each List.Sum(List.Transform(List.Skip(Record.FieldValues(_)), Number.From))
),
行の抽出 = Table.SelectRows(累計列の追加, each [累計]<>null),
列の選択2 = Table.SelectColumns(行の抽出, {"勘定科目", 月度, "累計"}),
テーブル再構成 = Table.FromColumns(
Table.ToColumns(列の選択2),
type table [勘定科目=text, 単月=Int64.Type, 累計=Int64.Type]
)
in
テーブル再構成
（d-q-t-p） 2023/02/06(月) 09:30:31
A B C D E F
1 勘定科目 4月度 5月度 6月度 第1 7月度
2 A商品売上 1,000 1,500 2,000 4,500 2,000
3 B商品売上
4 C商品売上
5 A売上原価 500 700 900 2,100 800
6 B売上原価
7 C売上原価
8 売上総利益
●抽出シート
A B C
1 7月度
2
3 単月 累計
4 A商品売上 2,000 6,500
5 A売上原価 800 2,900
B4
=LET(r,会計!B:Z,a,TAKE(r,1),b,XLOOKUP(A4,会計!A:A,r),XLOOKUP(A$1,a,b,""))
下方向・↓
C4
=LET(r,会計!B:Z,a,TAKE(r,1),b,XLOOKUP(A4,会計!A:A,r),SUM(FILTER(b,(SEQUENCE(,COLUMNS(b))<=XMATCH(A$1,a))*(LEFT(a)<>"第"))))
下方向・↓
（んなっと） 2023/02/06(月) 10:44:43
A3に
=LET(
_rng1,TAKE(データ範囲,,XMATCH(月度,TAKE(データ範囲,1))),
_rng2,INDEX(_rng1,,COLUMNS(_rng1)),_flt,TRANSPOSE(FILTER(_rng1,_rng2<>"")),
_row1,DROP(CHOOSEROWS(_flt,COLUMNS(_rng1)*{0,1}+{1,0}),,1),
_row2,BYCOL(FILTER(DROP(_flt,,1),RIGHT(TAKE(_flt,,1),2)<>"合計"),LAMBDA(_a,SUM(_a))),
TRANSPOSE(HSTACK({"";"単月";"累計"},VSTACK(_row1,_row2)))
)
（d-q-t-p） 2023/02/06(月) 14:11:39
詳しくご教示いただきありがとうございます。
現状私がパワークエリに不案内でして、ご呈示の通りに編集することができませんでした。
ですが、今後活用する場面が多くなることが考えられますので、
これをきっかけに詳しく学びたいと思います。
迅速かつご丁寧にご対応いただきありがとうございました！
（わか） 2023/02/06(月) 15:22:53
データ例までとても美しく作成いただきありがとうございます。
ご呈示いただいた式を元に資料を作成してみたところ、意図するものができました！
ただしINDEX,MATCH,OFFSET関数については使用方法を理解できたわけではないので、今後のためにも習得していきたいと思います。
ご対応ありがとうございました！
（わか） 2023/02/06(月) 15:31:56
単純な転記ミスがありましたね、ご指摘ありがとうございます。
パワークエリについてのご教示内容ですが、恥ずかしながらあまりにも不案内でして…
「名前の定義」「詳細エディタ」これらの言葉から学ぶ必要があります。
みなさまのご回答を見る限り今後活用できるなと思いましたので、
これを機に学び始めたいと思いました。
ご対応いただきありがとうございました！
（わか） 2023/02/06(月) 15:36:21
まさに意図したものそのもので感動しております。
また数式もそれほど複雑ではなく、業務の引継ぎ時にもとても有用に感じます。
こちらを用いて、元資料に合わせていちど作成してみます。
ただし
LET,XLOOKUP,TAKE,FILTER,SEQUENCE,COLUMNS
これらの関数は前提として知識にありませんでしたので、今後のためにもぜひとも習得したいと思います。
ありがとうございました！！
（わか） 2023/02/06(月) 15:46:13
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.