- 締切済み
エクセルでの計算式について
まず、(1)のシートには支店コード・店コード・店名…等の一覧リスト があります。 (2)のシートにおいて、支店毎に表を作成し、B列に店コードを入力する と自動的にC列に店名がD列に実績がというようにしたいのですが、 どのようにすれば宜しいでしょうか。 私の考えだと まず、(2)のシートでA支店コードを入力すると(1)のシートでA支店の範囲を 選択、店コードを入力するとVLOOKUP関数 【VLOOKUP(B8,データ!$D$2:$W$3433,2,1)】で必要項目抽出。 っといった形が出来れば良いのかな…と思いながらわからないのです。 ※同じ店コードがいくつもあるので、先に支店毎に選択しなくてはならず。 (フィルタは使用しません。) 自分で考えた結果はこんな感じでしたが、良い案をご存知の方教えて 頂けないでしょうか。 よろしくお願い致します。
- cats-64
- お礼率15% (3/19)
- オフィス系ソフト
- 回答数7
- ありがとう数0
- みんなの回答 （7）
- 専門家の回答
みんなの回答
- mu2011
- ベストアンサー率38% (1910/4994)
No2です。 補足回答の確認を失念していました。 Ｃ８セル設定し、下横方向にコピーでＤ列に売上高も表示されると思います。 =IF(SUMPRODUCT((データ!$A$2:$A$3433=$B$3)*(データ!$B$2:$B$3433=$B8)),INDEX(データ!C:C,SUMPRODUCT((データ!$A$2:$A$3433=$B$3)*(データ!$B$2:$B$3433=$B8)*ROW($A$2:$A$3433))),"コード不正")
- NNAQ
- ベストアンサー率56% (104/184)
#5のNNAQです。 #5の式は、支店コードが、同じ支店でひとかたまりになっているのが条件です（#1のご回答のような感じで）。 C8=VLOOKUP($B8,OFFSET(データ!$B$1:$W$1,MATCH($B$3,データ!$A$2:$A$3433,0),0,COUNTIF(データ!$A$2:$A$3433,$B$3)),COLUMN(B1),0) C列から右の列も、(1)のシートと同じ項目を抽出するなら、上の式を右へ下へフィルドラッグするだけで良いです。 抽出項目がまったく同じでなければCOLUMN(B1)の部分を変更して下さい（VLOOKUPの列番号の引数になってます）。 ついでに#3のご回答の場合 C8=LOOKUP($B$3&$B9,データ!$A$2:$A$3433&データ!$B$2:$B$3433,データ!C$2:C$3433) この式は(1)のシートのA列:支店コード・B列:店コードが昇順になってるのが条件になると思います。 LOOKUP関数がそのような仕様なので。 #2のご回答の場合 C8=IF(SUMPRODUCT((データ!$A$2:$A$3433=$B$3)*(データ!$B$2:$B$3433=$B10)),INDEX(データ!C$2:C$3433,SUMPRODUCT((データ!$A$2:$A$3433=$B$3)*(データ!$B$2:$B$3433=$B10)*ROW($A$2:$A$3433))-1),"コード不正") 微妙に変えましたけど、意味は同じです。 若干、処理が重いですが、支店コード・店コードが(1)のシートにばらばらに入力されている場合でも、問題無く抽出できます。
- NNAQ
- ベストアンサー率56% (104/184)
いろいろなやり方がありますが、 > (2)のシートでA支店コードを入力すると(1)のシートでA支店の範囲を > 選択、店コードを入力するとVLOOKUP関数で必要項目抽出 という手順でやってみます。 A8セルに支店コード、B8セルに店コードを入力するとします。 データシートのC列が支店コードで、昇順か降順で並んでる（同じ支店がひとかたまりになっている）とします。 =VLOOKUP($B8,OFFSET(データ!$D$1:$W$1,MATCH($A8,データ!$C$2:$C$3433,0),0,COUNTIF(データ!$C$2:$C$3433,$A8)),2,0) 質問欄のVLOOKUPの数式の検索範囲が、A8セルに入力された値よって変わる、 という式です。
- Nouble
- ベストアンサー率18% (330/1783)
訂正です 誤記 C3 =LOOKUP(Sheet2!$D$1&$B3,Sheet1!A:A&Sheet1!B:B,Sheet1!C:C) D3 =LOOKUP(Sheet2!$C3,Sheet1!C:C,Sheet1!D:D) 修正後 C4 =LOOKUP(Sheet2!$D$1&$B4,Sheet1!A:A&Sheet1!B:B,Sheet1!C:C) D4 =LOOKUP(Sheet2!$C4,Sheet1!C:C,Sheet1!D:D) 陳謝 <(_ _)>
- Nouble
- ベストアンサー率18% (330/1783)
Ａ Ｂ Ｃ Ｄ … １ ２ 支店コード 店コード 店名 総売上高 ３ … … … … ４ … … … … ５ … … … … ６ … と言う具合にシート１に記載があるとして さらに Ａ Ｂ Ｃ Ｄ … １ 支店名 ○×支店 支店コード xxxxxxx ２ ３ 店コード 店名 売上高 ４ … XXXXX ○×商店 … ５ … … … … ６ … と言う具合にシート２に記載したい場合ですよね? C3 =LOOKUP(Sheet2!$D$1&$B3,Sheet1!A:A&Sheet1!B:B,Sheet1!C:C) D3 =LOOKUP(Sheet2!$C3,Sheet1!C:C,Sheet1!D:D) (D3はVLOOKUPでも可) を試してみてください このご質問が難解な点としては 文字を配列数式で扱う点が挙げられると思います しかし web2525様の示された通り 此処の横並びのセルを結合して 単一の検索キーと出来たなら 何ら問題ない一般的な検索と化します そこで「＆」で２列を繋いでみました 本来の難解な点である「文字」の扱いという点に蓋をした感ではありますが 機能的には配列数式も扱っておらず 更に組み込み関数1つのみ使用なので格段に高速処理が可能だと思います 如何でしょうか?
補足
回答ありがとうございます。 なのに、私の説明不足のせいで皆様にご迷惑おかけし申し訳ございません。 ですが、教えて頂いた数式を自分の表に当てはめるとどうもうまく行かず・・・ そこで、更に詳細を記載しますので、再度教えて頂けますでしょうか。 (1)のシートの配列はA列:支店コード・B列:店コード・C列:店名・・・ ※1行目はタイトル、データはA2～W3433セルまであります。 Ａ Ｂ Ｃ Ｄ … １ 支店コード 店コード 店名 売上高 ２ … … … … ３ … … … … (2)のシートでは、B3セルに支店コードを、B8セルに店コードを入力します。 その結果、C8セルに計算式を入力すると、(1)のシートより自動的に 店名を抽出してくる。 Ａ Ｂ Ｃ Ｄ … １ ２ 支店コード ３ １１１ ～ ７ 店コード 店名 総売上高 ８ ２２ ＝(ココに計算式を入力すると 自動的に店名を抽出する) ９ ３３ ＝(←店名) と、いった形にしたいのですが。 宜しくお願い致します。
- mu2011
- ベストアンサー率38% (1910/4994)
次の方法は如何でしょうか。 ■店名の抽出 =IF(SUMPRODUCT((支店コード範囲=支店コード)*(店コード範囲=店コード)),INDEX(店名列,SUMPRODUCT((支店コード範囲=支店コード)*(店コード範囲=店コード)*ROW(データ!$A$2:$A$3433))),"コード不正") ■実績の抽出 前数式の店名列を実績列に変更して下さい。
補足
回答ありがとうございます。 しかし、私には式の意味がわからず・・・ 誠に恐れ入りますが、更に詳細を記載致しますので、 分かる範囲で結構ですので、式を当てはめて頂けないでしょうか。。。 (1)のシートの配列はA列:支店コード・B列:店コード・C列:店名・・・ ※1行目はタイトル、データはA2～W3433セルまであります。 (2)のシートでは、B3セルに支店コードを、B8セルに店コードを入力します。 その結果、C8セル(店名欄)に計算式を入力すると、(1)のシートより 自動的に店名を抽出してくる。 と、いった形にしたいのですが。 宜しくお願い致します。
- web2525
- ベストアンサー率42% (1219/2850)
確認： シート１の表のつくりがどんな風になっているのかが良く分かりません。 ＞同じ店コードがいくつもある のであれば配置として 支店コード ｜ 店コード ｜ 店名 ｜ 実績 ｘｘｘｘｘ ０００１ ａａａ ？？？ ｘｘｘｘｘ ０００２ ｂｂｂ ？？？ ｙｙｙｙｙ ０００１ ｃｃｃ ？？？ ｚｚｚｚｚ ０００１ ｅｅｅ ？？？ ｚｚｚｚｚ ０００２ ｄｄｄ ？？？ とこんな感じなのでしょうか？ 各コードが昇順に並んでいるのであれば、支店コード＋店コード（ｚｚｚｚｚ０００１のような）を店固有コードと設定しVLOOKUP関数で、抽出すれば問題ないと思います。
補足
はい。 表のつくりとしては、まさしくこのような感じです。 そこで、店固有コードの設定とは、支店コード(3桁)+店コード(4桁)を 別の列で7桁に入力し直して、(2)のシートで店名入力する時に7桁で 入力するという事でしょうか？？ 理解出来なくて申し訳ございません。
関連するQ&A
- エクセルVBAで行うVLOOKUPについて
エクセル2000を使用しています。 いまいろいろVBAを使用してますが、わからないことがあります。 シート1に入力データ、シート2に参照のデータ USERFORM を使用し たとえばテキストBOX1にコードAと入れるとテキストBOX2に東京支店と表示し確認をできるようにしたいのです。東京支店は、シート2の参照データから引用してきます。 BOX1にAと入れてエンターを押した時点でVLOOKUPを実施させたいのですがいまいちわかりません。 シート2は A列にコード A,B,C,D,.... B列に東京支店、千葉支店... よろしくお願いいたします
- 締切済み
- オフィス系ソフト
- Excelの関数を使ってデータを抽出する方法教えてください！！
１のシートには、ＡとＢの全てのデータがあります。２のシートには、１部だけのデータがあります。 データは、「コード」、「店名」、「住所」などが入力されています。 Ａのシートから、Ｂのシートのデータを省いて抽出したいのですが、どうしたらいいでしょうか？？ なお、それぞれに入っている「住所」や「店名」、「コード」などのデータは完全一致していないものもあります。 また、Ａのシートの「コード」などの各データは、全て同じ列に入っていません。 これでは、無理でしょうか？？
- ベストアンサー
- オフィス系ソフト
- エクセルのＶＬＯＯＫＵＰ関数について
エクセルのシート(1)に組合員コード（7桁），氏名,商品,数量・・・が入力してあって、列を挿入し、その組合員コードの左３桁を表示するLEFTの文字列関数を入力します。 それによって3桁が表示され、その3桁のコードは、支店コードを意味するので、シート(2)に支店コード、支店名が入力してあるのでそこから参照するようにシート(1)の3桁が表示されている隣に新たに列を挿入し、シート(2)の支店コードから支店名を引き出すようにＶＬＯＯＫＵＰ関数を入力したら、「Ｎ＃Ａ」という表示が出て支店名が表示されません。 ＬＥＦＴ関数によって表示された文字列だと「Ｎ＃Ａ」で支店名が表示されないのですが、自分で３桁の数字を入力すると支店名が表示されます。 3桁の文字列の結果の値だけをコピーしても、「Ｎ＃Ａ」が表示され、支店名が表示されません。 文字列関数によって表示された結果から、ＶＬＯＯＫＵＰで参照は出来ないのでしょうか？ 1つずつ自分で数字を入力するしかないのでしょうか？ 他に何か別の方法でも構いませんので、教えていただけないでしょうか？
- ベストアンサー
- オフィス系ソフト
- エクセル関数２
シート(1) A B C D 作業列 コード 顧客名 金額 3 300001 A 10,000 3 300002 B 15,000 3 300003 C 20,000 3 300004 D 25,000 3 300005 E 30,000 4 400001 F 35,000 4 400002 G 40,000 4 400003 H 45,000 4 400004 I 50,000 4 400005 J 55,000 シート(2) A B C D E F 「1」 「2」 「3」 「4」 「5」 「6」 10,000 35,000 15,000 40,000 20,000 45,000 25,000 50,000 30,000 55,000 ※ シート(1)にコード、顧客名、金額を入力すると、シート(2)にあるように該当するコードの先頭行の列に表示するようにしたいと思っています。 シート１で作業列を作りその作業列を基にシート２でVLOOKUP関数を使い‥シート２の10,000のセルの場合、 VLOOKUP(C$2,sheet1!$A$3,$D$12,4,false)として、下にコピーすると同じ金額になってしまいます。 セルが空白にならず、シート１の金額が上から順番にシート２に表記されるようにするにはどうしたらよいでしょうか。マクロはできませんので、関数で詳しく教えて下さい。宜しくお願いします。
- 締切済み
- オフィス系ソフト
- Excel VLOOKUPで複数の関数
どなたかわかる方教えて下さい！！ わかりにくい説明かと思いますが、よろしくお願い致します。 Aシートにコードが入力されています。 Bシートにコード、文字列、数値が入力されています。 AシートのコードでVLOOKUPを使い、Bシートから数値を呼ぶことはできるのですが、 数値が空白の場合は、空白にする。B列の文字も検索したい場合はどのようにすればよろしいでしょうか？ 例 Aシートには、コード００１が入っています。 Bシートから、コード００１、「あ」の行を検索し、数値をもってきて、空白の場合は空白にしたいです。 A列 B列 C列 D列 1行 ００１ あ １０ 空白 2行 ００１ い ２０ ３０ 3行 ００２ あ ３０ 空白
- ベストアンサー
- オフィス系ソフト
- 関数について
関数について VLOOKUP関数を使ってみようと思っているのですが1列目のデータの抽出に仕方がわかりません。 入力例 sheet1 B列__C列__D列 1_______A____現金 2_______B____現金 3_______C____カード 4_______D____カード 5_______E____現金 抽出例 sheet2 B列__C列__D列 1_______A____現金 2_______B____現金 5_______E____現金 抽出例のC、D列はVLOOKUPを使おうと思っています。 B列の抽出にはどのようにしたらいいかおしえてください。
- 締切済み
- オフィス系ソフト
- 重複するデータの抽出について（エクセル）
エクセル2003にて重複するデータの取り出しをしたいのですが、 わからないので教えてください。 例えば Ａ列 Ｂ列 Ｃ列 Ｄ列 Ｅ列・・・ １ 色 １回目 ２回目 ３回目 ４回目 ２ 赤 あ a Ａ ０ ３ 白 い b Ｂ １ ４ 黄 う c Ｃ ２ ５ 黄 え d Ｄ ３ ６ 黒 お e Ｅ ４ ７ 赤 か f Ｆ ５ ： このデータの中から、別のシートのＡ１に 赤と入力したら、Ｂ１にＤ列のＡ・Ｆを抽出 黄と入力したら、Ｂ１にＤ列のＣ・Ｄを抽出をしたいです。 ＶＬＯＯＫＵＰ関数を使用してみたのですが、 赤と入力すると、Ｄ列のＡ（１番上のデータ）のみしか抽出出来ず、Ｆが抽出されません。 重複するデータがあるのはＡ列のみで、Ｄ列には重複するデータはありません。 わかりにくい文章ですみませんが、よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 関数についてアドバイスお願いします
＜Sheet1＞において店コードと商品コードで照合（D列）欄に＜Sheet2＞から数量を抽出したいのですが、VLOOKUP関数で抽出することはできないのでしょうか。 抽出できる方法があれば教えてください。
- ベストアンサー
- Excel（エクセル）
- VLOOK関数で#N/Aエラーの回避法
お伺いします。 sheet1のＡ列に商品コード、Ｂ列に前月実績があるとします。 sheet2に１０月実績表があるとします。 sheet1のＢ１セルに =VLOOKUP(A1,sheet2!$A$3:$H$1200,5,0) が入力されています。 １０月に実績がゼロの時はsheet2の表にはその商品コードの行がない。 これを前提にするとsheet1のＢ１セルには#N/Aが表示されてしまいます。 すると合計ができません。#N/A を空白または実績なしと表示したいのです。 また、合計値を正確に出したいのです。 どうか、お知恵をお貸しください。
- ベストアンサー
- オフィス系ソフト
- エクセルの計算結果が急に出なくなった
エクセルで請求書を作成しています。 同じブックのシート1のＡ列に得意先名、Ｄ列からＪ列に納品日、Ｋ列に請求金額を入れています。Ａ列には名前の定義で「得意先」と指定しています。 シート2のＢ6欄に得意先名が出るように入力規則をリストにして「＝得意先」として、順にＢ6に得意先名をプルダウンできるようにしています。Ｅ17に=VLOOKUP(B6,シート1!A:K,4,0)とし、Ｉ17まで納品日が出るように指定しています。Ｊ17には=VLOOKUP(B6,シート1!A:K,11,0)とし請求金額が出るようにして、これまで順調に発行できてきました。 ところが、何かをいじったのか突然シート2のＢ6をプルダウンして変化させても数字が変わらなくなりました。どうすればいいでしょうか？
- ベストアンサー
- オフィス系ソフト
補足
教えて頂いた式を当てはめてみたのですが、どうしてもうまくいかず。 お手数ですが、更に詳細を記載しますので、再度教えて頂けますでしょうか。 (1)のシートの配列はA列:支店コード・B列:店コード・C列:店名・・・ ※1行目はタイトル、データはA2～W3433セルまであります。 (2)のシートでは、B3セルに支店コードを、B8セルに店コードを入力します。 その結果、C8セルに計算式を入力すると、(1)のシートより自動的に 店名を抽出してくる。 と、いった形にしたいのですが。