[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『左右の項目を行に合わせたい』(秋男)
いま、昨年の単価と今年の単価の比較作業をしています。
抽出・転送をして昨年は左側B3に貼り付け、今年はO3に貼り付けのマクロは作りました。
並べ替えを行っても微妙に段がずれてしまい手作業で治すと式が狂います。
お勧めの方法があればご紹介ください。
< 使用 Excel:Excel2013、使用 OS:Windows7 >
表を提示頂かないと、どうなっているのか分かりません。
今年度 [A] [B] [1] 品名 単価 [2] りんご 282 [3] みかん 127 [4] ばなな 259 [5] きうい 291 [6] いちご 213 [7] とまと 163
昨年度 [A] [B] [1] 品名 単価 [2] りんご 149 [3] みかん 280 [4] いちご 276 [5] とまと 166 [6] [7]
比較 [A] [B] [C] [1] 品名 今年度 昨年度 [2] りんご 282 149 [3] みかん 127 280 [4] ばなな 259 276 ←ここがずれる [5] きうい 291 166 ←ここがずれる [6] いちご 213 [7] とまと 163
たぶんこういうことだと思いますが、
1)「品名」に当たる文字列またはコードは一意(重複が無い)値ですか? 2)貼付のコードは提示できますか? 貼付の段階で制御したほうが楽に出来そうです。 (稲葉) 2014/09/26(金) 11:52
Application.ScreenUpdating = False Sheets("データベース").Select If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False End If
With ActiveSheet.Range("B3:M3") .AutoFilter If Range("AD2").Value <> "" Then .AutoFilter Field:=3, Criteria1:=Range("AD2").Value If Range("AE2").Value <> "" Then .AutoFilter Field:=4, Criteria1:=Range("AE2").Value End With
Sheets("集計").Range("B3:L1000").Clear
Sheets("データベース").Range("B3:M2000").Copy _ Sheets("集計").Range("B2") Sheets("データベース").Range("B3:M2000").Copy Sheets("集計").Range("B2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False End If End Sub
(秋男) 2014/09/26(金) 12:14
1)についてですが、重複がある場合、昨年度とどのように比較するのでしょう? 平均ですか? 最大値ですか? 最小値ですか? 中央値ですか?
追加質問 3)提示されたコードでは、D列とE列をオートフィルターで抽出していますが、 ここでは何を検索しているのですか? 4)「示した通り」とのことですが、コードを見る限りB:M列まで丸ごとコピーしてい ますよね? 特定の項目(金額のみ)を抜き出すものではないということですか? (稲葉) 2014/09/26(金) 12:48
3)品名と産地で抽出しています。 4)丸ごと抜き取り見比べています。
1000 〇×商店 0806 2000 00 100g 1500 63 26-Sep-13
N3とAA3にこの式を入れ=E10&"_"&F10&"_"&G10 E10の表示例 2000_00_100g
AA3は=R3&"_"&S3&"_"&T3 AA3の表示例 2000_00_100g
これで同一か調べています。
AB3に以下の式
=IF(AND(N3="__",AA3="__"),"",IF(N3=AA3,"○",""))
AC3には=IF(AB3="○",V3/I3,"")が入っています。
わかりにくい説明ですがよろしくお願いします。
(秋男) 2014/09/26(金) 13:46
>N3 AA3 >これで同一か調べています。 これは集計表の話ですよね? つまりこの組合せが一意の項目になりますよね?
5)元データでそれと同じ列を作れないでしょうか? そうすればIndex関数とMatch関数の組合せで同じキーのデータが拾えます。
・・・というかこれだけ項目(列)多くてどのように比較しているんですか?
(稲葉) 2014/09/26(金) 14:01
5)元データは全品目が入っているので見たい品目を入れ抽出し、産地が多数あれば産地も抽出項目に入れ集計に出力しています。
項目は多いですが比較しているのは産地・等級・サイズだけで前年と比較できます。
後は数量の合計と金額の合計が出ていますから相場が高いか安いかが見られます。
(秋男) 2014/09/26(金) 14:46
考え方変えましょう。 ピボットテーブルを使います。 表の提示が無い以上、これ以上無理なので、これが肌に合わなかったらお手上げです。 1)I列(空いている列でOKです。)に年度を入力します。 2)項目数は同じなので、2013年度と2014年度を行で繋ぎ合わせます。
2013年度 [A] [B] [C] [D] [E] [F] [G] [H] [I] [1] コード 店舗名 産地 等級 管理No グラム数 単価 年度 [2] 1000 ○×商店 0806 2000 00 100g 100 2013年度 [3] 2000 □△商店 0806 2000 01 100g 300 2013年度 [4] 3000 ◇○商店 0806 2000 00 100g 500 2013年度 [5]
2014年度 [A] [B] [C] [D] [E] [F] [G] [H] [I] [1] コード 店舗名 産地 等級 管理No グラム数 単価 年度 [2] 1000 ○×商店 0806 2000 00 100g 400 2014年度 [3] 2000 □△商店 0806 2000 00 100g 600 2014年度 [4] 3000 ◇○商店 0806 2000 02 100g 800 2014年度
集計表 [A] [B] [C] [D] [E] [F] [G] [H] [I] [1] コード 店舗名 産地 等級 管理No グラム数 単価 年度 [2] 1000 ○×商店 0806 2000 00 100g 100 2013年度 [3] 2000 □△商店 0806 2000 01 100g 300 2013年度 [4] 3000 ◇○商店 0806 2000 00 100g 500 2013年度 [5] 1000 ○×商店 0806 2000 00 100g 400 2014年度 [6] 2000 □△商店 0806 2000 00 100g 600 2014年度 [7] 3000 ◇○商店 0806 2000 02 100g 800 2014年度
3)集計表の項目名がある表範囲を選択して、挿入>ピボットテーブルを選択します。 4)別シートのA1に出力してください。 5)「行ラベル」に産地、等級、管理Noをドラッグ&ドロップします 6)ドロップした項目の「▼」をクリックして、 「フィールドの設定」→「レイアウトと印刷」→「アイテムのラベルを表形式で表示する にチェックを入れる 7)「列ラベル」に年度をドラッグ&ドロップします。 8)「値」に単価をドラッグ&ドロップします。 9)値に落とした単価は「合計 / 単価▼」となっていますので▼をクリックして、平均にします。
そうすると以下のような表が出来ます。 [A] [B] [C] [D] [E] [F] [1] 平均 / 単価 列ラベル [2] 行ラベル グラム数 等級 2013年度 2014年度 総計 [3] 00 100g 2000 300 500 400 [4] 01 100g 2000 300 300 [5] 02 100g 2000 800 800 [6] 総計 300 600 450
これじゃだめですか? ※レイアウトガタガタだったらごめんなさい。 (稲葉) 2014/09/26(金) 15:51
>昨年の単価と今年の単価の比較作業をしています。 でしたら、昨年と今年と両方データがそろっているものを抽出して比べれば良さそうに思いますが。 「片方にだけあるデータ」みたいなのがあるから、ずれるんですよね? (HANA) 2014/09/26(金) 16:25
HANAさん、ご相談に参加いただきありがとうございます。
「片方にだけあるデータ」みたいなのがあるから、ずれるんですよね?>その通りです。
1000 A商店 0806 2000 00 100g 1500 63 26-Sep-13 2000_00_100g
2000 B商店 0806 2040 99 一株 100 2250 26-Sep-13 2040_99_一株
2000 C商店 0806 2000 00 100g 250 60 26-Sep-13 2000_00_100g
2000 D商店 0806 2000 00 100g 500 65 26-Sep-13 2000_00_100g
2000 E商店 0806 2000 00 一株 2400 85 26-Sep-13 2000_00_一株
3113 F商店 0806 2000 00 カット 20 1794 26-Sep-13 2000_00_カット
1000 A商店 0806 2000 00 100g 250 55 25-Sep-14 2000_00_100g
2000 A商店 0806 2040 52 一株 300 90 25-Sep-14 2040_52_一株
2000 D商店 0806 2040 99 一株 40 2700 25-Sep-14 2040_99_一株
2000 B商店 0806 2000 00 100g 1000 60 25-Sep-14 2000_00_100g
2000 A商店 0806 2000 00 一株 1200 95 25-Sep-14 2000_00_一株
2000 B商店 0806 2000 00 100g 750 55 25-Sep-14 2000_00_100g
2000 D商店 0806 1600 00 170g 200 100 25-Sep-14 1600_00_170g
3113 F商店 0806 2000 00 カット 20 1800 25-Sep-14 2000_00_カット
3117 I商店 0806 2800 00 400 57 25-Sep-14 2800_00_
これを手作業でずらし左右を合わせると随分な手までご相談した次第です。
(秋男) 2014/09/27(土) 10:00
下側の表の 2000 B商店 0806 2000 00 100g 1000 60 25-Sep-14 2000_00_100g 2000 B商店 0806 2000 00 100g 750 55 25-Sep-14 2000_00_100g なんかは、同じ項目で金額が違ったりする様ですが、金額だけの比較で良いですか?
「手作業でずらして左右をあわせる」ではなくピボットテーブルを使う案ですが [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [ 1] CD 名称 区分1 区分2 区分3 金額 年度 回数 合計 / 金額 回数 [ 2] 2000 D商店 1600 00 170g 200 25年 1 区分1 区分2 区分3 年度 1 2 3 [ 3] 1000 A商店 2000 00 100g 250 25年 1 1600 00 170g 25年 200 [ 4] 2000 B商店 2000 00 100g 100 25年 2 26年 [ 5] 3113 F商店 2000 00 カット 20 25年 1 2000 00 100g 25年 250 100 [ 6] 2000 A商店 2000 00 一株 120 25年 1 26年 63 60 65 [ 7] 2000 A商店 2040 52 一株 300 25年 1 カット 25年 20 [ 8] 2000 D商店 2040 99 一株 40 25年 1 26年 80 [ 9] 3117 I商店 2800 00 400 57 25年 1 一株 25年 120 [10] 1000 A商店 2000 00 100g 63 26年 1 26年 85 [11] 2000 C商店 2000 00 100g 60 26年 2 2040 52 一株 25年 300 [12] 2000 D商店 2000 00 100g 65 26年 3 26年 [13] 3113 F商店 2000 00 カット 80 26年 1 99 一株 25年 40 [14] 2000 E商店 2000 00 一株 85 26年 1 26年 22 [15] 2000 B商店 2040 99 一株 22 26年 1 2800 00 400 25年 57 [16] 26年 [17]
H列には H2=COUNTIFS(C$2:C2,C2,D$2:D2,D2,E$2:E2,E2,G$2:G2,G2) の式を入れてあります。
ピボットテーブルは、フィールドの設定(N)の レイアウトと印刷タブで データのないアイテムを表示する(W) にチェックを入れてあります。 (HANA) 2014/09/27(土) 16:23
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.