![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
下記の様なデータを元に、各担当者別に用意された売り上げ表の雛形に表示させたい(指定した項目を指定したセルに)のですが、
実現出来る関数はありますでしょうか?
オートフィルタ>コピペではなく、毎月の処理になるので自動的に出来る様にしたいのですが。
顧客名 担当者 商品名 金額
A社 佐藤 バナナ 100
B社 鈴木 バナナ 100
C社 田中 メロン 100
A社 佐藤 バナナ 300
A社 佐藤 リンゴ 200
C社 田中 メロン 100
これを↓
Sheet 佐藤
顧客名 商品名 金額
A社 バナナ 100
A社 バナナ 300
A社 リンゴ 200
------------------
Sheet 鈴木
顧客名 商品名 金額
B社 バナナ 100
------------------
Sheet 田中
顧客名 商品名 金額
C社 メロン 100
C社 メロン 100
よろしくお願いします。
No.13ベストアンサー
- 回答日時:
Poerです。
今までの私の回答はなかったものとして、最初からご説明いたします。この手順に従えば、それなりの作品が出来上がります。ご希望に添えると良いのですが...。(1)Sheet1のA1から10項目(10項目以内なら何項目でもいいです)の表を入れる(ただし一行目を項目名とする)。
(2)Sheet2に切り替える。
(3)まずは見出しを入力します。以下、セル番地・・・「入力する項目名」です。
A2・・・ 「条件1」
B1・・・ 「表1」
F1・・・ 「条件(表1)」
I1・・・ 「該当(表1)」
L1・・・ 「抽出No. (表1)」
(4)次に見出しを(オートフィルで)コピーします。
A1をA6までコピー(「条件1」「条件2」...「条件5」と縦にならぶ)。
B1をD1までコピー(「表1」「表2」...と横にならぶ)。同様に、
F1をH1までコピー。
I1をK1までコピー。
(5)関数を入力します。
F2・・・ =IF(B2="",0,B2)
I2・・・=IF(AND(MATCH(F$2,$O2:$X2,0)>0,MATCH(F$3,$O2:$X2,0)>0,MATCH(F$4,$O2:$X2,0)>0,MATCH(F$5,$O2:$X2,0)>0,MATCH(F$6,$O2:$X2,0)>0),1,0)
L2・・・ =SUMIF(I$2:I2,1)
O1・・・ =Sheet1!A1
Y1・・・ =CONCATENATE(B2,IF(B3="","","/"),B3,IF(B4="","","/"),B4,IF(B5="","","/"),B5,IF(B6="","","/"),B6)
Y14・・・=CONCATENATE(C2,IF(C3="","","/"),C3,IF(C4="","","/"),C4,IF(C5="","","/"),C5,IF(C6="","","/"),C6)
Y26・・・ =CONCATENATE(D2,IF(D3="","","/"),D3,IF(D4="","","/"),D4,IF(D5="","","/"),D5,IF(D6="","","/"),D6)
Y2・・・ =O1
Y15・・・ =O1
Y27・・・ =O1
Y3・・・ =IF(ISERROR(VLOOKUP(ROW(A1),$L:O,COLUMN(D1),0)),"",VLOOKUP(ROW(A1),$L:O,COLUMN(D1),0))
Y16・・・ =IF(ISERROR(VLOOKUP(ROW(A1),$M:O,COLUMN(C1),0)),"",VLOOKUP(ROW(A1),$M:O,COLUMN(C1),0))
Y28・・・ =IF(ISERROR(VLOOKUP(ROW(A1),$N:O,COLUMN(B1),0)),"",VLOOKUP(ROW(A1),$N:O,COLUMN(B1),0))
(6)関数を(オートフィルで)コピーします(注:10項目の表の下端の行を仮に101行とします)。
F2をH101までコピー。
I2をK101までコピー。
L2をN101までコピー。
O1をX101までコピー。
Y2をAH2までコピー。
Y15をAH15までコピー。
Y27をAH27までコピー。
Y3をAH12までコピー。
Y16をAH24までコピー。
Y28をAH37までコピー。
(7)作業列(F列からX列まで)の列幅をゼロにして列を隠します。
(8)メニューバーの「ツール」→「オプション」→「表示」タブ→「ウィンドウオプション」の「ゼロ値」のチェックをはずします。
(9)B2~B6に抽出条件を入力(任意の箇所に入力できます)すると、3つの表(Y1:AH12、Y14:AH24、Y26:AH37)に結果が表示されます。
(10)必要に応じてシート自体を他のシートにもコピーします。
これでご希望に添えなければ残念としか言いようがありません(>◇<)。とりあえず試してみてください!
No.14
- 回答日時:
細かいですが、No.13を一部訂正します。
手順(5)の中の関数
Y1・・・ =CONCATENATE(IF(B2="","","/"),B2,IF(B3="","","/"),B3,IF(B4="","","/"),B4,IF(B5="","","/"),B5,IF(B6="","","/"),B6)
Y14・・・ =CONCATENATE(IF(C2="","","/"),C2,IF(C3="","","/"),C3,IF(C4="","","/"),C4,IF(C5="","","/"),C5,IF(C6="","","/"),C6)
Y26・・・ =CONCATENATE(IF(D2="","","/"),D2,IF(D3="","","/"),D3,IF(D4="","","/"),D4,IF(D5="","","/"),D5,IF(D6="","","/"),D6)
No.12
- 回答日時:
Poerです。
正直予想外でした(>_<)。場合によっては根本からやり直す必要があります。次の事柄を教えてください。●商品の種類は最大いくつでしょうか?
●項目は全部でいくつでしょうか?
●バナナ用の表示スペースははH2~H9までの8行だけ(しかも定位置)でよいのでしょうか?メロンやリンゴの表示スペースも同様に8行の定位置でよいのでしょうか?
いつもありがとうございます。
説明が足らず、申し訳ありませんでした。
●商品の種類は最大いくつでしょうか?
>3つです
●項目は全部でいくつでしょうか?
>10個程度です
●バナナ用の表示スペースははH2~H9までの8行だけ(しかも定位置)でよいのでしょうか?メロンやリンゴの表示スペースも同様に8行の定位置でよいのでしょうか?
>印刷用にあらかじめ作られたシートの、決められたセルに振り分けをしたいのです。
10個ある項目の中から、振り分ける条件として
「担当者」「商品名」「売上月(今月)」「売上月(四半期)」が、今のところ予定として考えられます。
表示スペースは、各10行程度あると十分です。
振り分け方がわかればあとは何とか応用して出来るものなのかと思い、
最初から説明足らずだったこと、ご迷惑おかけして申し訳ありませんでした。
No.11
- 回答日時:
Poerです。
失礼しました。B2は =SUM(A$2:A2) で十分です。これなら少しは容量が減るかも?しれません。
何度も訂正してかっこ悪いですね。しかしこちらも勉強になるのでありがたいです。
何度もご回答いただき、本当にありがとうございます。
補足いただいた方法で、思い通りに抽出することが出来ました。
出来たのですが・・・、あと一歩、やりたい様に出来なくて・・・。
わがまま言って申し訳ないのですが、
「佐藤」で「バナナ」をH2行目~、
「佐藤」で「リンゴ」をH10行目~
表示させるにはどうしたらいいでしょうか・・・。
AとBの様な列を増やしてみたりしたのですが、うまく出来ません。
もし宜しかったらまたアドバイスお願いします。
No.10
- 回答日時:
Poerです。
#9のお礼にお答えします。Sheet佐藤のA2に =IF(D2=G$1,1,0)
というところを、
Sheet佐藤のA2に =IF(AND(D2=G$1,OR(E2=G$2,G$2="")),1,0)
に替えてみてください(もちろん縦にコピーします)。
(項目を増やされたようなので列の名前が代わっていると思います。上の式のG$2もそれに合わせて変更してください)
そうすると、「佐藤」を入れると佐藤の条件に当てはまるものすべて、その真下のセル(元のG2に当たるセル)に「バナナ」を入れると「佐藤」「バナナ」の両条件に当てはまるものが抽出されます。
私の不手際で何かと問答が長くなりましたが、お役に立てれば幸いです。ちなみにあまりシートを増やすと容量が大きく動作が鈍くなる可能性があるので、その辺はお気をつけ下さい。
ちなみにB2のCOUNTIFはSUMIFでも構いません(容量が軽くなるかどうかは分かりませんが...)。
何かあればまた補足してください。うまくいけば締め切ってください。応援してます、Kabooさん!!
No.9
- 回答日時:
混乱させてしまってすみません。
#7の「お礼」にお答えします。C1に =Sheet1!A4
は、C1に =Sheet1!A1 の間違えでした。大変申し訳ありません。
項目を増やす場合は、F列の右に列を挿入し、F列をコピーしてください。同様に、K列の右にもF列の右に挿入したのと同じ列数だけコピーしてください。
これでお答えできたでしょうか?
ありがとうございます。
項目を増やすことは出来たのですが、検索条件を増やすことって可能でしょうか?
「佐藤」で「バナナ」のみ抽出させたいのですが。
お手数おかけしますが、補足いただけるとありがたいです。よろしくお願いします。
No.8
- 回答日時:
#3です。
#3では佐藤さんなどの明細が10件未満しか考えてないので、実情にあわないかなと思い修正します。H2は=G2*100+COUNTIF($B$2:B2,B2)にして下方向に複写します。
101
201
301
102
103
302
となります。
Sheet2のA2は
=OFFSET(Sheet1!$A$2,MATCH(100+(ROW(A2)-1),Sheet1!$H$2:$H$100,0)-1,COLUMN(A1)-1)
といれD2まで式を複写する。
その後、A2:D2を範囲指定して、D2で+ハンドルうおだし、A4:D4まで下方向に式を複写してください。
別の質問の質問者曰く、「値しかとってこない」と。
当たり前です。関数でやれば、値しかもってこれません。
また全セルに式が入っているのでデータが多いと重いでしょう。
この回答のイメージは分類して、「送る」のでなく、
条件にあったものを「吸い取る」イメージです。
No.7
- 回答日時:
#6の訂正です。
(7)...横3列(列F)まで → ...横3列(列K)まで
たくさんのご回答いただき、ありがとうございます。遅くなった上にまとめてお礼を言わせていただくことになってしまって、大変申し訳ありません。
No.6Poerさんの方法なのですが、H1~K1にはリストの一行目が常に表示されてしまう様なのですが、
これは回避できる方法はありますか?
(「(5)C1に =Sheet1!A4」の部分は「Sheet1!A2」に変更しましたがリストの一行目ってことでいいのですよね?)
それから、ひとつやりたいことが追加になってしまったのですが・・・、
項目に「販売日」が追加になりました。日付(2005/06/08・2005/07/01等)が入力されています。
Sheet佐藤 他 の中で「今月」「翌月」と別れており、日付の項目からも該当するデータを拾い、
それぞれ指定したセルに表示したいのです。
説明が下手で申し訳ありませんが、どなたかお願いします。
No.6
- 回答日時:
#5です。
手順をご説明します。(1)Sheet1にご質問の例のような表を入れます(1行目は項目見出し)。
(2)画面を「Sheet佐藤」に切り替えます。
(3)A2に =IF(D2=G$1,1,0) を入力、これを必要な行(Sheet1の表で必要とする行)だけ縦にコピーします(A65536までコピーしても構いませんが、あとで容量が大きくなるので避けた方がいいです)。
(4)B2に =COUNTIF(A$2:A2,1) を入力、必要な行だけ縦にコピー。
(5)C1に =Sheet1!A4 を入力、必要な行だけ縦にコピー。そのまま横3列(列F)までコピー。→Sheet1と同じ表になります。
(6)H1に =C1 と入力し、右にK1までコピー。
(7)H2に =IF(ISERROR(VLOOKUP(ROW(A1),$B$2:C$●,COLUMN(B1),0)),"",(VLOOKUP(ROW(A1),$B$2:C$●,COLUMN(B1),0))) を入力(●には必要な行数を入れる)。必要な行だけ縦にコピー。そのまま横3列(列F)までコピー。
(8)ここでG1に「佐藤」と入力してみてください。H列からK列までに結果が抽出されるはずです。
(9)うまくいったら、A列からF列までの列幅を0(ゼロ)にして隠します。
(10)Sheet佐藤の全体をSheet鈴木、Sheet田中にコピーします。
(11)Sheet鈴木のG1に「鈴木」、Sheet田中のG1に「田中」と入力すれば、それぞれの結果が出てきます。
手順は以上です。レイアウトが気に入らなければ、セルを移動して調整してください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) Excelについて 4 2023/03/02 09:24
- 経営情報システム accessでの請求管理について 12 2022/06/11 16:20
- Visual Basic(VBA) vbaマクロについて 【1.csv】をもとに【商品.csv】に有るものを【有り.csv】として名前を 1 2023/05/18 07:58
- その他(データベース) accessについて 2 2022/05/31 16:58
- Visual Basic(VBA) vbaマクロについて 次のようなマクロを組みたいです。 自分は初心者なので全くわかりません。 詳しく 8 2023/05/18 18:38
- その他(Microsoft Office) パワークエリの複数ファイルのデータ統合について 3 2022/07/14 17:06
- その他(ビジネス・キャリア) スポット取引とは? 1 2023/04/06 15:23
- Visual Basic(VBA) 【ExcelVBA】動的にボタン、ボタン名を生成できますか? 7 2022/04/08 12:54
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IFとIFS関数
-
Excelを無料で使うには? パソ...
-
セルの数を求めたい
-
Excelの表示についての質問
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
エクセルで「ページレイアウト...
-
エクセルの数式バーのフォント...
-
エクセルの関数について教えて...
-
エクセルでセルに標準で入力さ...
-
Microsoft1Officeの互換ソフト...
-
シフト表をエクセルで作るとき...
-
ユーザー定義関数をアドイン登...
-
Excel VBAで全ての矢印を赤色に...
-
VBA Private Sub Worksheet_Cha...
-
2列に入っているデータを1列...
-
Excelに詳しい方! B列が「日...
-
excel2013 MonthDays 関数が使...
-
エクセルのクイックアクセスツ...
-
Excelの数式について教えてくだ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報