[[20140926113207]] 『左右の項目を行に合わせたい』(秋男) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『左右の項目を行に合わせたい』(秋男)

いま、昨年の単価と今年の単価の比較作業をしています。
抽出・転送をして昨年は左側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

稲葉さん、早速のご返事ありがとうございます。
お示しいただいた通りの質問です。
1)のご返事ですが重複はあります。
2)の返事は以下に示します。
本年分は書出し位置が異なるだけですから省略します。
Sub Filter_2013()
    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.