[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『列の中に複数の選択肢がある元データからの別シートへの抽出』（n）
お世話になっております。教えていただければ幸いです。
sheet3の表から抽出でsheet1の料金列を埋めていきたいと思っております。
Sheet1に記入する引取先と搬入先は、場所が逆になるパターンもあります。
Sheet3には、C列とG列に搬入でも搬出でも逆パターンでも抽出されるようにデータを作りたいです。（そういう作り方でも抽出はできますでしょうか。）
場所は10個でも20個でも登録していきたいです。カンマかなにかで区切れば、抽出できるのではと思って、そうしてみました。
料金区分は３パターンです。 料金はいくらからいくらのように書いてあります。表ががたがたですみません。どうぞよろしくお願いいたします。
Sheet1
G H Z AA
1 引取先 搬入先 料金区分 料金
2 ABC福岡 abcde福 1A ？
3 ABCD九州 ｶｷｸｹｺ東 2B ？
4 EF富山 アイウ大阪 3C 20000〜21000
5 アイウ大阪 EF富山 1A 2000〜2500
Sheet3
A C F G H I
1県名 搬入搬出 県名 搬入搬出 料金区分 料金
2福岡 ABC福岡,ABCD九州 福岡 abcde福,fghij 1A 1000〜1500
3福岡 ABC福岡,ABCD九州 福岡 abcde福,fghij 2B 5000〜6000
4福岡 ABC福岡,ABCD九州 福岡 abcde福,fghij 3C 10000〜11000
5福岡 ABC福岡,ABCD九州 東京 ｶｷｸｹｺ東,東ｻｼｽｾｿ 1A 10000〜15000
6富山 EF富山,GH富山,JK富山 大阪 アイウ大阪,カキク大阪 1A 2000〜25007
7富山 EF富山,GH富山,JK富山 大阪 アイウ大阪,カキク大阪 2B 9000〜9500
8富山 EF富山,GH富山,JK富山 大阪 アイウ大阪,カキク大阪 3C 20000〜21000
＜ 使用 Excel：Excel2013、使用 OS：Windous7 pro ＞
Sheet3のほうだがカンマで区切らずにそれぞれ搬入先と搬出先を一つだけにできないか？ (行数は増えることになるが）
（ねむねむ） 2015/10/26(月) 11:13
とりあえず今の表の形で式にしてみた。 Sheet1のAA2セルに
=IFERROR(INDEX(Sheet3!F$2:F$8,MATCH(1,(IFERROR((FIND(","&G2&",",","&Sheet3!B$2:B$8&",")>0),0)+IFERROR((FIND(","&H2&",",","&Sheet3!B$2:B$8&",")>0),0))*(IFERROR((FIND(","&H2&",",","&Sheet3!D$2:D$8&",")>0),0)+IFERROR((FIND(","&G2&",",","&Sheet3!D$2:D$8&",")>0),0))*(Sheet3!E$2:E$8=Z2),0)),"")
と入力して式の確定時にShiftキーとCtrlキーを押しながらEnterキーで確定してくれ。
(確定後、式が{}で囲まれればOK）
その後、下へフィルコピー。 （ねむねむ） 2015/10/26(月) 11:37
（ｎ） 2015/10/26(月) 13:10
ねむねむ様、確定はきちんとできましたが、列の指定がたぶん間違っているのか、反応しませんでした。上のsheet3は余計な列が入りすぎていますので下記のように作り直しました。大変お手数をおかけしますが、もう一度見ていただけないでしょうか。
Sheet3
1A搬入搬出 B搬入搬出 C料金区分 D料金
2ABC福岡,ABCD九州 abcde福,fghij 1A 1000〜1500
3ABC福岡,ABCD九州 abcde福,fghij 2B 5000〜6000
4ABC福岡,ABCD九州 abcde福,fghij 3C 10000〜11000
5ABC福岡,ABCD九州 ｶｷｸｹｺ東,東ｻｼｽｾｿ,ﾀﾁﾂ 1A 10000〜15000
6EF富山,GH富山,JK富山 アイウ大阪,カキク大阪 1A 2000〜2500
7EF富山,GH富山,JK富山 アイウ大阪,カキク大阪 2B 9000〜9500
8EF富山,GH富山,JK富山 アイウ大阪,カキク大阪 3C 20000〜21000
元の式から少し変更しているが。 (無駄な部分があったため。ただし結果は全く同じ）
=IFERROR(INDEX(Sheet3!D$2:D$8,MATCH(1,((IFERROR(FIND(","&G2&",",","&Sheet3!A$2:A$8&","),0)+IFERROR(FIND(","&H2&",",","&Sheet3!A$2:A$8&","),0))*(IFERROR(FIND(","&H2&",",","&Sheet3!B$2:B$8&","),0)+IFERROR(FIND(","&G2&",",","&Sheet3!B$2:B$8&","),0))>0)*(Sheet3!C$2:C$8=Z2),0)),"")
これでもダメな場合は >式の確定時にShiftキーとCtrlキーを押しながらEnterキーで確定してくれ。 これを行っているか、Sheet1のほうのレイアウトは最初に書かれた通りか、双方の搬入搬出列、料金区分列の値は同じになっているかの確認を。 （ねむねむ） 2015/10/26(月) 13:41
（ｎ） 2015/10/26(月) 15:12
AA2セルに入力したものをコピーであれば「AA2:AA30000」ではなく「AA3:AA30000」として見てくれ。
多分だが。 >ShiftキーとCtrlキーを押しながらEnterキーで確定 これは通常の数式を配列数式として入力するための方法だが、本来はセル範囲を選択してセル範囲にまとめて 式を入力する場合の方法として行われる。
で、この入力を行った場合、式の修正や削除などを行う場合は配列数式の入っているセル範囲だけをまとめて行わなければならない。
今回の場合はAA2セルからAA30000セルまでをまとめて変更しようとするとAA2セルだけに配列数式があるためエラーになっているのだと思う。 （ねむねむ） 2015/10/26(月) 15:37
sheet3でA列とB列逆パターンもありで式を作っていただいたのですが、
A列とB列の両方の列に同じ場所を登録しておかざる得ない場合があって、そのようにすると
sheet1で引取先がaで搬入先がbにもかかわらず、料金が2500と出てしまうというような
ことが起こってしまいました。
逆パターンもありという考えを捨てて、
sheet3には、A列とB列、かぶっていいから登録をたくさんして、
あくまで引取は引取、搬入は搬入で合致するものを抽出できる式にした方がよかったようです。
いちいち、お伺いして申し訳ありませんが、その式も教えていただけますでしょうか。
Sheet3
1A搬入搬出→引取先 B搬入搬出→搬入先 C料金区分 D料金
2ｂ,.. ｂ 2500
3ａ, ｂ 25000
4ｄ, ｂ
5ｂ, ｄ
（ｎ） 2015/10/27(火) 15:33
((IFERROR(FIND(","&G2&",",","&Sheet3!A$2:A$8&","),0)+IFERROR(FIND(","&H2&",",","&Sheet3!A$2:A$8&","),0))
の式の+より前の部分でG2の値がSheet3のA列にあるか、後ろの部分でH2セルの値がSheet3のA列にあるかの判断をしている。
なのでG2セルの値がA列にあるかのチェックだけにする。
(IFERROR(FIND(","&H2&",",","&Sheet3!B$2:B$8&","),0)+IFERROR(FIND(","&G2&",",","&Sheet3!B$2:B$8&","),0))
も同じようにH2セルの値がSheet3のB列にあるかだけにする。
そうすると式全体は
=IFERROR(INDEX(Sheet3!D$2:D$8,MATCH(1,(IFERROR(FIND(","&G2&",",","&Sheet3!A$2:A$8&","),0)*IFERROR(FIND(","&H2&",",","&Sheet3!B$2:B$8&","),0)>0)*(Sheet3!C$2:C$8=Z2),0)),"")
となる。
（ねむねむ） 2015/10/27(火) 15:52
一セル一つの場合だと
=IFERROR(INDEX(Sheet3!D$2:D$8,MATCH(1,(Sheet3!A$2:A$8=G2)*(Sheet3!B$2:B$8=H2)*(Sheet3!C$2:C$8=Z2),0)),"") と単純な比較が使える。
以前の式は単純な比較では完全一致はできるが「含まれる」はできないためFIND関数を使っているがFIND関数で","を外すだけだとたとえばABC福岡とBC福岡があった場合に誤判定をしてしまう。
追記 式を =IFERROR(INDEX(Sheet3!D$2:D$8,MATCH(1,INDEX((Sheet3!A$2:A$8=G2)*(Sheet3!B$2:B$8=H2)*(Sheet3!C$2:C$8=Z2),0),0)),"") とすると式の確定を通常通りEnterで行えるようになる。 （ねむねむ） 2015/10/28(水) 10:37
もう一つ、可能なのかどうか知りたい方法があります。
sheet3はもともとは下記のような表でした。
縦A列の4行目から下は47都道府県の各引き取り先が登録してあって、
横行の方は、2Bから2Dまでは例えば神奈川県内のいくつかの搬入先が本来はセル結合で登録してあります。
つまり3列とも料金タイプが違うだけで搬入場所は同じです。）
料金タイプが3タイプあるので、B列、C列、D列にそれぞれの料金を4行めから登録してあります。
セル結合はほどいて、下記のようにそれぞれ同じ場所を登録すれば、シンプルな式にできそうな気がします。
縦列はひとますに一つの引取場所、横行にはカンマで区切っていくつかの場所を登録しているのです。
今のところ、横への広がり方は、7県くらいなので、つまり7県×3タイプ料金＝21列くらい使っています。
横へも縦ヘもこれからも増えていきます。
こういう表から、抽出させていくことは可能なのでしょうか。
ご意見、または式を教えていただければ幸いです。
sheet3
引き取り先 搬入先 搬入先 搬入先
1 A B C D
2 a,b,c,d,e,f a,b,c,d,e,f a,b,c,d,e,f
3 料金Aタイプ 料金Bタイプ 料金Cタイプ
4aaa札幌 2000〜2500 5000〜5500 10000〜10500
5bbb札幌
6ccc札幌
7ddd札幌
（ｎ） 2015/10/28(水) 13:25
そうすると料金は引取り先と都道府県と料金タイプで決まるのだろうか？
であればSheet3は
A B C D
引取先 搬入先都道府県 料金タイプ 料金
とし
Sheet1は
G H I Z AA 1 引取先 搬入先 搬入先都道府県 料金区分 料金 といったようにはできないだろうか？
Sheet1の搬入先都道府県部分は手で入力するか、あるいは搬入先と都道府県の表を作っておいてVLOOKUP関数などで持ってきてもいいと思うが。 （ねむねむ） 2015/10/28(水) 13:46
もしかするとSheet3の引取り先のほうも
A B C D
引取先都道府県 搬入先都道府県 料金タイプ 料金
Sheet1も G H I J Z AA 1 引取先 搬入先 引取先都道府県 搬入先都道府県 料金区分 料金 のようにできるのだろうか？ （ねむねむ） 2015/10/28(水) 14:29
そのようにすれば、sheet3はデータ量が少なくなりますね。
そういう表を少しためしで作成してみます。
どちらにしてもやはり、Sheet3のC列に料金タイプが入る方が式はたてやすいということですね。
Sheet1の搬入先都道府県部分は手で入力するのは、ひと手間増えますが、データを登録する手間と
比べたら、そちらの方が楽だろうと思います。
その場合の式も教えていただければ幸いです。
搬入先と都道府県の表を作るというのは、後からでもできますね。
まずは、ねむねむさんのおっしゃっているような表を作り始めます。
引取先は、市単位で料金が変わったりもするので、1か所ずつ登録をしています。
（ｎ） 2015/10/28(水) 14:33
式については =IFERROR(INDEX(Sheet3!D$2:D$8,MATCH(1,INDEX((Sheet3!A$2:A$8=G2)*(Sheet3!B$2:B$8=H2)*(Sheet3!C$2:C$8=Z2),0),0)),"") のセル参照先を変更するだけで対応可能。 （ねむねむ） 2015/10/28(水) 14:44
ああそうか。
Sheet3を
A B C D E
引取先都道府県 搬入先都道府県 料金A 料金B 料金C
として式を
=IFERROR(INDEX(Sheet3!C$2:E$8,MATCH(1,INDEX((Sheet3!A$2:A$8=G2)*(Sheet3!B$2:B$8=H2),0),0),MATCH(Z2,{"1A","2B","3C"},0)),"")
とすると三つの料金を一行にするのが可能だ。
MATCH(1,INDEX((Sheet3!A$2:A$8=G2)*(Sheet3!B$2:B$8=H2),0),0)
で、引取先・搬入先都道府県の組み合わせの行を検索、
MATCH(Z2,{"1A","2B","3C"},0)
で、C列からE列での料金タイプの列の検索を行っている。
なので
{"1A","2B","3C"}
部分で料金タイプを指定してくれ。
追記
あるいはSheet3を
A B C D E
引取先 搬入先都道府県 1A 2B 3C
として式を
=IFERROR(INDEX(Sheet3!C$2:E$8,MATCH(1,INDEX((Sheet3!A$2:A$8=G2)*(Sheet3!B$2:B$8=H2),0),0),MATCH(Z2,Sheet3!C$1:E$1,0)),"")
で。
（ねむねむ） 2015/10/28(水) 14:57
A B C D E 引取先都道府県 搬入先都道府県 料金A 料金B 料金C でデータを作って式を入れてみます。ありがとうございます。
（ｎ） 2015/10/28(水) 15:22
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.