[[20200304225128]]　『別シートから料金を表示（関数）』（いちご）　ページの最後に飛ぶ

『別シートから料金を表示（関数）』（いちご）

ご教示願います。

別Sheetから送料（運賃）を表示したいのです。

１．｢Sheet1｣G２以降の列に都道府県名の住所（番地入り）が入ります。

Sheet1に普通（Z）・クール（AA）・集荷（AB）・持込（AC）・６０（AD）・８０（AE）・１００（AF）・１２０（AG）・１４０（AH）・１６０（AI）表示の列があります。
１．最初に「普通かクール」のどちらかを選び、数字の「１」を手入力します。
２．次に「集荷か持込」のどちらかを選び、数字の「１」を手入力します。
３．次に「６０サイズから１６０サイズ」のどれかを選び、数字の「１」手入力します。
４．最後に運賃（T）列に料金が表示なるようにしたいのです。

現在、料金シートは2つ作ってあり、普通運賃Sheet（持込料金と集荷料金）とクール運賃Sheet（持込料金と集荷料金）に分けています。

普通運賃Sheet
B４（北海道）・C4～C６（青森県・秋田県・岩手県）・D４～D6（宮城県・山形県・福島県）・E4～E11（茨城県・栃木県・群馬県・埼玉県・千葉県・東京都・神奈川県・山梨県・）・F４～F5（新潟県・長野県）・G４～G6（富山県・石川県・福井県・）・H4～H7（静岡県・愛知県・三重県・岐阜県）・I４～I9（大阪府・京都府・滋賀県・奈良県・和歌山県・兵庫県）・J4～J8（岡山県・広島県・山口県・鳥取県・島根県）・K４～K7（香川県・徳島県・愛媛県・高知県）・L4～L10（福岡県・佐賀県・長崎県・熊本県・大分県・宮崎県・鹿児島県）・M4（沖縄県）と表示しています。

A１４～A１９には６０・８０・１００・１２０・１４０・１６０のサイズが表示しています。
B１４：M１９に都道府県の持込料金を表示しています。

A２２～A２７には６０・８０・１００・１２０・１４０・１６０のサイズが表示しています。
B２２：M２７に都道府県の集荷料金を表示しています。

クール運賃Sheet
B４（北海道）・C4～C６（青森県・秋田県・岩手県）・D４～D6（宮城県・山形県・福島県）・E4～E11（茨城県・栃木県・群馬県・埼玉県・千葉県・東京都・神奈川県・山梨県・）・F４～F5（新潟県・長野県）・G４～G6（富山県・石川県・福井県・）・H4～H7（静岡県・愛知県・三重県・岐阜県）・I４～I9（大阪府・京都府・滋賀県・奈良県・和歌山県・兵庫県）・J4～J8（岡山県・広島県・山口県・鳥取県・島根県）・K４～K7（香川県・徳島県・愛媛県・高知県）・L4～L10（福岡県・佐賀県・長崎県・熊本県・大分県・宮崎県・鹿児島県）・M4（沖縄県）と表示しています。

A１４～A１７には６０・８０・１００・１２０のサイズが表示しています。
B１４：M１７に都道府県の持込料金が表示しています。

A２０～A２３には６０・８０・１００・１２０のサイズが表示しています。
B２０：M２３に都道府県の集荷料金が表示しています。

上記のように運賃表を作っています。

※クールの場合は１４０・１６０サイズが有りません。
※数字の「１」を入力する手順がバラバラでも表示できると有り難いです。

普通運賃Sheet・クール運賃Sheetをどの様に加工したら関数で料金を表示できるのでしょうか？
また、他力本願ですがどこにどの様な関数を入れれば出来るのでしょうか。

素人ですので分かり易く教えていただければ有り難いです。
宜しくお願いいたします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 普通運賃 と クール運賃 の県名の並びは全く同じですよね？

 それを前提にしてもいいですか？

 つまり、どっちの表を見に行っても、県名の位置は全く同じなので
 全部、普通運賃の方を見ればいい事になり、手間が一つ減るんですが。

 ※ 料金は別々のシートを見に行きますけども

（半平太） 2020/03/05(木) 10:41

半平太様

ご返事が遅くなり申し訳ありません。

今作ってある「普通運賃Sheet・クール運賃Sheet」の県名の並び（場所）は全く同じです。

一つの表にしなくとも関数で出来るのでしょうか？
新たな表を作成しなくてもすむのでしょうか？

初心者のため、どの様な考えをしたらいいのかもわかりません。

そうぞ、ご教示下さい。

いちご

（いちご） 2020/03/05(木) 20:22

 新たな表を作成する必要はないです。

 <前提>
  普通運賃とクール運賃 の県名の並びは全く同じ。
  住所は、都道府県名から始まる。

 <名前の定義の活用>
  数式は複雑にならざるを得ないので、主要範囲には名前を付けて、見た目だけでも短くします。

  将来何か追加された場合に備えるため、名前定義する範囲は、
  １行(または１列）多目に取るものとします。(1行では気休めかも知れないですが・・）

 <改善案>
  上記の前提で旨く行けたとして、なんか物足りない気がしています。

  例えば、料金が1000円だとした場合、
  　常識的には、セルの書式で[1,000円] と表示されるようにするでしょうが、
  　さらにクール便なら、セルの色が青色になったら分かり易いでしょうし、
  　[集 1,000円］とか[持 1,000円］とか料金体系の別も表示されたら、安心感が出そうな気がします。

 　 いずれも、表示だけの問題であり、実体は1000のままです。(後でそれを利用して計算に使えます）

 まぁ、そちらのニーズ次第なので改善案は別にして、明日回答します。

（半平太） 2020/03/05(木) 22:42

  １．以下の範囲に名前を付けてください。

  　※<名前定義の仕方>
  　　対象範囲をドラッグ選択して、数式バーの左にあるボックスに名前を入れる。(A1セルの上）

  範囲１:普通運賃の　B4:M13　(一行多目です）
  名前→:都県

  範囲２:普通運賃の　B14:M20　(一行多目です）
  名前→:普持

  範囲３:普通運賃の　B22:M28　(一行多目です）
  名前→:普集

  範囲４:=クール運賃の　B14:M18　(一行多目です）
  名前→:ク持

  範囲５:=クール運賃の　B20:M24　(一行多目です）
  名前→:ク集

  ２．Sheet1のT2セルに下式を入力

   =IF(G2="","",INDEX(CHOOSE(11-BIN2DEC(SUMPRODUCT(Z2:AC2*10^{3,2,1,0})),普集,普持,,,ク集,ク持),MATCH(1,AD2:AJ2,0),AGGREGATE(14,6,COLUMN(都県)/FIND(LEFT(G2,3),都県),1)-1))

  <Sheet1 結果図>
   行 _________G_________ _H_ _S_ ____T___ _U_ _Y_ __Z__ __AA__ _AB_ _AC_ _AD_ _AE_ _AF_ _AG_ _AH_ _AI_
    1                             運賃(例)          普通  クール 集荷 持込  60   80  100  120  140  160
    2 北海道札幌市中央区          　 1,840           1            1              1                    
    3 岩手県盛岡市                　 1,601           1                 1              1               
    4 山形県山形市                　 2,204                   1    1                   1               
    5 沖縄県那覇市                　 2,053                   1         1                   1

（半平太） 2020/03/06(金) 10:11

半平太様

おはよう御座います。

１．Sheet名ですが「普通運賃」・「クール運賃」です。
２．クール「AA列」に数字の「１」を入力すると「T列」運賃セルに薄い青色が表示なるようにしています。
３．宅配伝票（連続伝票）用のデータを作り、それを市販のソフトからドッドプリンターで印字して出力しています。
４．「普通運賃」・「クール運賃」Sheetの料金表示は半角で桁区切りで表示しています。
５．料金（数字）表示は半角表示ですとありがたいです。

今までは料金表を見ながら全て手作業で入力していました。
たまに料金間違いを起こしていました。

この度は印刷用のデータ入力の省力化と料金入力の間違えを起こさないようにするためです。

どの様にすると出来るのか、関数で出来るのか等わからなくご質問とご教示をいたしました。

どうぞ、宜しくお願いいたします。

いちご

（いちご） 2020/03/06(金) 10:41

 ちょっと分からないです。

 ＞別Sheetから送料（運賃）を表示したいのです。 
 ＞１．｢Sheet1｣G２以降の列に都道府県名の住所（番地入り）が入ります。

 と言う質問だったと思うんですが、Sheet1は存在しないのですか？

 すると、求める料金は、「普通運賃」「クール運賃」
 それぞれのシート上に出したいという事なんでしょうか？

（半平太） 2020/03/06(金) 11:26

半平太　様

この度お世話になります。

只今、名前定義の仕方がわかり作りました。

　範囲１:普通運賃の　B4:M11　にしました。

  名前→:都県

  範囲２:普通運賃の　B14:M19　にしました。
  名前→:普持

  範囲３:普通運賃の　B22:M27　にしました。
  名前→:普集

  範囲４:=クール運賃の　B14:M17　にしました。
  名前→:ク持

  範囲５:=クール運賃の　B20:M23　にしました。
  名前→:ク集

２．Sheet1のT2セルに下式を入力

   =IF(G2="","",INDEX(CHOOSE(11-BIN2DEC(SUMPRODUCT(Z2:AC2*10^{3,2,1,0})),普集,普持,,,ク集,ク持),MATCH(1,AD2:AJ2,0),AGGREGATE(14,6,COLUMN(都県)/FIND(LEFT(G2,3),都県),1)-1))

上記ご教示の関数をコピーし入力いたしました。

＃NAMEに問題が見つかりましたと表示になりました。

宜しくご教示お願いします。

＞別Sheetから送料（運賃）を表示したいのです。

言葉足らずで申し訳ありません。

「普通運賃」・「クール運賃」Sheetのデータ（料金）をSheet１の運賃（T）列に料金が表示なるようにしたいと思っています。

＞１．｢Sheet1｣G２以降の列に都道府県名の住所（番地入り）が入ります。

G２に住所を入力すると「H」「I」「J」に住所が分かれます。

１．長い住所の場合「H」（半角で34文字）、「I」には残りの住所と番地

２．愛知県名古屋市名東区極楽1000-10-7「H」　(株)温泉グッズ研究所「I」　猫屋敷デブの会「J」の場合
「H」「I」「J」に住所が分かれます。

伝票出力時には住所欄が3段になっているので1段は「H」・2段は「I」・3段は「J」の表示セルが印字になります。

お忙しい中、どうぞ宜しくお願いいたします。

いちご

（いちご） 2020/03/06(金) 12:41

 ＞＃NAMEに問題が見つかりましたと表示になりました。

 基本的には名前定義が正しく設定されてない場合に生じます。

 試しに、どこか空いているセルに以下の数式を入れて、
 どれが＃NAMEエラーになるか調べて下さい。

 =INDEX(都県,1,1)
 =INDEX(普持,1,1)
 =INDEX(普集,1,1)
 =INDEX(ク持,1,1)
 =INDEX(ク集,1,1)

 ＞長い住所の場合 「H」（半角で34文字）、「I」には残りの住所と番地
 これは心配いらないです。
 G2の住所の左2文字だけで、県名を判定しているので、
 住所が必ず都道府県名から始まっていれば問題ないです。

（半平太） 2020/03/06(金) 13:38

半平太様

この度、貴重な時間を割いていただき、ありがとうございました。

名前定義のやり方がわからず勉強になりました。

数式→名前の管理と進んで参照範囲を修正しましたら完璧に表示できました。
ネット検索し何とか何とかです。

CHOOSE(11-BIN2DEC(SUMPRODUCT(Z2:AC2*10^{3,2,1,0}
AGGREGATE(14,6,COLUMN(都県)/FIND(LEFT(G2,3),都県),1)-1))

上記は初めて見る関数で複雑すぎて意味がわかりません。

11-BIN2DEC(SUMPRODUCT(Z2:AC2*10^{3,2,1,0}
どうして11-BIN2DEC（関数）を引くの？とか

AC2*10^{3,2,1,0}も全然わかりません。

御時間のある時で構いません。
どうぞ、部分だけでも解説していただければ幸いです。

感謝！感謝！　です。

いちご

（いちご） 2020/03/06(金) 18:12

 　　　　　　　　　　　　　　やろうとしていること
 　　　　　　　　　　　　　　　　↓
    _Z_  _AA_  _AB_  _AC_
     1           1      　これは普通集荷　の表を使う
     1                 1　これは普通持込　の表を使う
           1     1      　これはクール集荷の表を使う
           1           1　これはクール持込の表を使う

 どうやって4つの表を1つに絞るかです。

 IFの入れ子にするとこんな感じですかね。
 =IF(Z2=1,IF(AB2=1,普集,IF(AC2=1,普持,"不明")),IF(AA2=1,IF(AB2=1,ク集,IF(AC2=1,ク持,"不明"))))

 長年回答を入れていると、IFの入れ子は敬遠しがちな(変な）習慣が身について、
 下式にしましたが、上の式の方が簡明でした。ごめんなさい。上に修正してください。

 CHOOSE(11-BIN2DEC(SUMPRODUCT(Z2:AC2*10^{3,2,1,0})),普集,普持,,,ク集,ク持)

 ＞AGGREGATE(14,6,COLUMN(都県)/FIND(LEFT(G2,3),都県),1)-1)) 
 上式は、都県表の中に「住所の左2文字」があるかチェックして、その列番号を特定するものです。

 あとは、サイズの位置をMatch関数で求めれば、料金に辿りつけます。
 ※特定した表で、都県の列位置、サイズの行位置の交差するところに求める料金がある。

（半平太） 2020/03/06(金) 20:54

半平太様

この度、素人の私ですが何となくやろうとしている事が少しわかったような気がします。
ただ、どの様な関数を使い組み立てていくのかさっぱりわかりません。

他力本願でご教示頂き感謝しかありません。
土曜日に数字の「１」を順に入力すると料金が表示され、みんなで「びっくり！」。

これで間違いなく表示されると思うと安心です。

関数の達人に、みんなからも「感謝！」「感謝！」と伝言を頂いています。

ご連絡が遅くなりましたが、有り難うございました。

最後に個人的ですが、この度ご教示の関数を一つ一つネットで調べてみます。
IF関数の連続は理解不能かなと感じています。

またご縁があればご教示下さい。

いちご

（いちご） 2020/03/08(日) 21:29

コメント返信：

[ 一覧(最新更新順) ]

[[20200304225128]] 『別シートから料金を表示（関数）』（いちご） ページの最後に飛ぶ

[[20200304225128]]　『別シートから料金を表示（関数）』（いちご）　ページの最後に飛ぶ