発注品一覧表から社別にデータを抽出した発注シートを関数で作りたいです。
オートフィルタは諸事情あって使用しません。
過去の質問のこちらが、かなり近い回答なのだと思いますが
不勉強で自分用に修正ができませんでした。
http://oshiete.goo.ne.jp/qa/3157199.html
一覧表はこのようになっています。
日付 社名 品目 値段
4/1 C社 鉛筆 50円
4/1 A社 定規 150円
4/2 C社 ペン 100円
4/2 B社 鉛筆 100円
社別の別シートに日付・品目・値段を一覧から関数で抽出できるでしょうか?
どうぞよろしくお願いします。
No.2ベストアンサー
- 回答日時:
拝見いたしましたが参考にされたサイトですが
データが社名順に並んでいますよね。
今回の質問ですが、データが順に並んでいませんので全く違った発想が必要になります。
簡単な方法では、作業列を一列入れる方法がわかりやすいです。
データのシートに作業列も作れないのであれば 配列関数を使うことになりますが
データの量が多くてその式を縦横にコピーして使うですからパソコンの負担も重くなります。
式の意味を理解が難しいかと思いますが
INDEX関数で 範囲の指定の部分を条件で抽出した内容が入るように工夫した式がよく見受けられます
ので一応紹介しておきます
仮に シート名 データ
日付 社名 品目 値段
4/1 C社 鉛筆 50円
4/1 A社 定規 150円
4/2 C社 ペン 100円
4/2 B社 鉛筆 100円
別のシート
社名 品目
C社
日付 社名 品目 値段
とB2セルに抽出したい社名が入っているとして
A4セルに
=INDEX(データ!A:A,SMALL(INDEX((データ!$B$1:$B$100<>$B$2)*1000+ROW(A$1:A$100),),ROW(A1)))&""
と入れて 右へコピー、下へコピーしてみてください。
INDEX((データ!$B$1:$B$100<>$B$2)*1000+ROW(A$1:A$100),)
の部分が理解しにくいと思います。
社名の範囲でC社でなかったら1000倍したとてつもない大きな数字を加える
C社でればそのまま、その行番号 といった架空の列を作成します。
その架空の列の小さい数値の順に 最初のINDEX関数で取り出します
といった感じです。(なかなか文書で説明するのも難しくてすみません)
別案ですが
フィルターオプションの設定(オートフィルターではありません)
をしたほうがシンプルでデータ量が増えた時も勝手に対応してくれます。
丁寧な説明と回答ありがとうございます。
参考サイトの質問と今回の質問との相違は社順など関係していたんですね…。
目的のための関数の組み合わせがわからなくて式の意味がまだ理解できていないんです…。
まったくわからなかった式も、今回説明いただいた内容でなんとなくですが理解できました!
また、「フィルターオプションの設定」も良いですね!(いつもオートフィルタしか使わなかったので知りませんでした…)
一緒にデータを使う知り合いもあまりエクセルに詳しくないので、相談してどちらが使いやすいか相談の上進めていくことにします。
本当にありがとうございました。
No.5
- 回答日時:
ご回答者様には失礼とは思いますが関数はマニアックなもので、非常に難解です。
前回のご回答で解釈できない事ですので次の方法は如何でしょうか。
(1)発注シートのA1に社名と入力、A2に社名を入力
(2)データ→フィルタ→フィルタオプションの設定
(3)「指定した範囲」を選択、リスト範囲欄うぃお右クリック→対象シートクリック→対象列範囲を選択、検索条件範囲欄をクリック→A1:A2を選択→抽出範囲欄を選択、表示開始セルを選択→OK
(4)操作を簡略したい場合、マクロ記録→(2)(3)操作→マクロ記録終了すればショートカット等のワンタッチ操作で可能です。
参考URL:http://www.eurus.dti.ne.jp/~yoneyama/Excel/filte …
丁寧なご回答ありがとうございます。
いえいえ、本当に仰るとおり、関数は簡単なものが使えるからといって目的どおりに使うには理解と発想が大きく必要になるんだと今回つくづく感じました。
ほかのご回答にもありましたが、今回ご回答いただいた「フィルタオプションの設定」はとてもいい方法ですね!
マクロの記録の仕方までありがとうございます。マクロを使うとより作業が楽になりますね!!
一緒にデータを使う相手も初心者なので、関数にこだわらずフィルタオプションの設定を勧めてみようと思います。
本当にありがとうございました。
No.4
- 回答日時:
今仮に、元データの表の中で、「日付」と入力されているセルが、Sheet1のA1セルであり、
別シートのB2セルに、社名を入力するものとします。
まず、別シートの
A1セルに 社名
A3セルに 日付
B3セルに 品目
C3セルに 値段
と入力して下さい。
次に、別シートのA4セルに次の数式を入力して下さい。
=IF(OR(COUNTIF(Sheet1!$1:$1,A$3)=0,ROWS($4:4)>COUNTIF(Sheet1!$B:$B,$B$2)),"",INDEX(OFFSET(Sheet1!$A:$A,,MATCH(A$3,Sheet1!$1:$1,0)-1),SUMPRODUCT(ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$A:$A)))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$A:$A))=$B$2)*(COUNTIF(Sheet1!$B$1:INDEX(Sheet1!$B:$B,ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$A:$A)))),$B$2)=ROWS($4:4)))))
次に、別シートのA4セルをコピーして、別シートのB4セルとC4セルに貼り付けて下さい。
次に、別シートのA4セルの書式設定を[日付]として下さい。
次に、別シートのA4~C4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。
後は、別シートのB2セルに
C社
等の社名を入力すれば、抽出結果が自動的に表示されます。
簡潔でわかりやすいご回答ありがとうございます。
ご説明いただいたとおり別シートに入力していったところ、すぐに目的の結果が出ました!
あとは教えていただいた数式が理解できるように、これから勉強していきます。
本当にありがとうございました。
No.3
- 回答日時:
シート1にお示しのデータがあるとしてら作業列を作って対応するのが式も単純で分かり易く計算にも負担がかかりません。
E2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(A2="","",B2&COUNTIF(B$2:B2,B2))
そこで別のシート例えばC社についてのデータを表示したい場合には別のシートのA1セルにはC社と入力します。
2行目には項目を表示させるとしてA2セルには日付、B2セルには品名、C2セルには値段と入力します。
A3セルには次の式を入力してC3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF(COUNTIF(Sheet1!$E:$E,$A$1&ROW(A1))=0,"",INDEX(Sheet1!$A:$D,MATCH($A$1&ROW(A1),Sheet1!$E:$E,0),IF(COLUMN(A1)=1,1,IF(COLUMN(A1)=2,3,4))))
その後にA列のセルの表示形式を日付にします。
これでA1セルに入力した社名のデータのみが表示されることになります。他社の場合でも入力の式は同じで、A1セルの社名を変えることで良いでしょう。
簡潔でわかりやすい回答ありがとうございました。
シート1で作業列を作って、ご回答の数式を当てはめたところすぐに思い通りの結果が反映されました。
データを共有する相手からの注文通りの結果にできましたので、これで相手も満足すると思います。
本当にありがとうございました。
No.1
- 回答日時:
質問に載っている、質問に回答している者です。
imogasi方式でやってみます。
データ
Sheet1のA1:D5に
日付社名品目値段C社 <-F列です=作業列
4月1日C社鉛筆50円1
4月1日A社定規150円
4月2日C社ペン100円2
4月2日B社鉛筆100円
F1に選択する社名を要れます。
F2の式は
=IF(B2=$F$1,MAX($F$1:F1)+1,"")
下方向に式を複写します。
F列には上行からC者の行の連番が入ります。
Sheet2に行って、?1には =Sheet1!F1
A2セルには
=INDEX(Sheet1!$A$1:D10,MATCH(ROW()-1,Sheet1!$F$1:$F10,0),COLUMN())
右方向にD列まで式を複写。
その後A2:D2の式を下方向に式を複写。
D10やF10の10は実情に合わせて増やしてください
結果
Sheet2
C社
2011/4/1C社鉛筆50円
2011/4/2C社ペン100円
A列は表示形式を日付に設定すること。
本件で3件目以降に式を複写すると#N/Aが出ますが、
=IF(ROW()-1>MAX(Sheet1!$F$1:$F$10),"",INDEX(Sheet1!$A$1:D10,MATCH(ROW()-1,Sheet1!$F$1:$F10,0),COLUMN()))
とすると見えなくなります。
ーー
これはShhet1で作業列F列を使ってます(欠点)
ーー
Googleで「imogasi方式」で照会すれば、沢山の関数での抜き出し問題が出てきます。
その中に一発の式で、該当のC社の1項目セルが出る式が載ってます。この式が理解できれば良いですが、そうでなければ上記方法が難易度で次になると思う。
==
いつも言っているが、関数は抜き出し問題は苦手(不適)だと思う。エクセル(関数)はもともと計算用です。
操作のフィルタなどで考えることをすすめます(特に初心者には)。
ーー
週に3度程度は抜き出し問題がこのコーナーに出て、うんざりしてますが。
上記imogasi方式で出る質問の数個を読めば、解法のタイプは出つくしていると思う。
丁寧なご回答ありがとうございます。
自分なりに検索をかけてみたのですが、まだまだ甘かったようですね。
フィルタを使うなど未熟なりにできる範囲でやっていけるよう考えます。
教えていただいたこと、きちんと理解できるように勉強します。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
ちょっと先の未来クイズ第2問
9月9日(月)に発表される「第3回子どもマネー川柳」に入賞する川柳を考えてこちらに投稿してください。
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
Excelでデータ抽出処理をオートフィルタではなく関数でできませんか?
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Access レポート印刷するときに...
-
ACCESS VBA でのエラー解決の根...
-
Access Error3061 パラメータが...
-
Accessレポートのチェックボッ...
-
access クエリ yes/no型のクエ...
-
access2021 強制終了してしまう
-
accessでlaccdbファイルが削除...
-
Accessでセレクタをダブルクリ...
-
access2019の起動が遅い
-
access2021 VBA メソッドまたは...
-
実行時エラー3131 FROM 句の構...
-
access2021 コンパイルエラーの...
-
Access runtimeでのオプション...
-
【Access】Dcount関数の複数条...
-
Accessでフォームに自動入力し...
-
Access VBA [リモートサーバー...
-
【至急・画像あり】建物or住所...
-
Microsoft365にAccessってあり...
-
Accessのスプレッドシートエク...
-
テキストボックスの値をテーブ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Access レポート印刷するときに...
-
access2021 強制終了してしまう
-
Accessのクエリで、replace関数...
-
accessでlaccdbファイルが削除...
-
【Access】Dcount関数の複数条...
-
Access Error3061 パラメータが...
-
Accessのスプレッドシートエク...
-
ACCESS VBA でのエラー解決の根...
-
Accessレポートのチェックボッ...
-
実行時エラー3131 FROM 句の構...
-
access2019の起動が遅い
-
Vba Userformを前面に出すについて
-
Microsoft365にAccessってあり...
-
教えてください! アクセスの書...
-
Access 複数条件検索の設定が上...
-
【至急・画像あり】建物or住所...
-
Access VBA [リモートサーバー...
-
access2021 レコードロックの使...
-
Access VBA を利用して、フォル...
-
Accessのリンクテーブルのパス...
おすすめ情報