Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Excel 2013)
vlookup 関数について どうしても・・・
投稿日時: 17/11/23 10:39:05
投稿者: rinahana

いつもお世話になっております。
公立学校でエクセルを教えているのですが・・・。
下記の一覧表から各県宛ての料金を =vlookup( )関数で求めようと考えています。が、
県名のところで、つまずいています。何か 配列を使うのかなぁ〜と漠然と思っています。
 
宅配便の(TO:関東)の料金一覧表
セル番 A        B    C    D    E    F    G    H    I    J    K    L    M    
2 サイズ     北海道    北東北    南東北    関東    信越    北陸    中部    関西    中国    四国    九州    沖縄
 
11 60        1339    1015    907    907    907    907    907    1015    1123    1123    1339    1339
 
13 〜        〜     〜     〜     〜     〜     〜     〜     〜     〜     〜     〜     〜
14 〜         〜     〜     〜     〜     〜     〜     〜     〜     〜     〜     〜     〜
 
16 160        2462    2138    2030    2030    2030    2030    2030    2138    2246    2246    2462    4082
 
各地域の次の行(地域名と料金の間)に、その地域に含まれる県名が縦に並んでいます。
例えば、九州の場合を例にすると
 福岡県
 佐賀県
 長崎県
 熊本県
 大分県
 宮崎県
 鹿児島県
とあります。ちなみに、一番県名が少ないのは「北海道」と「沖縄」で1件です。
こんな表の料金を求めるには、どんな方法がありますか?出来ればこの表から算出したいのですが。
 
この様な検索欄で 縦横高さの合計の長さを入力、送り先を入力すると
 
セル番地    A    B     C    
 
   30  TO    神奈川県
    
   31   115    cm (検索地)
   32        料金
   33  FROM    熊本県    **** 円 答えがでる。
 
以上です。お願いします。

回答
投稿日時: 17/11/23 12:01:30
投稿者: Mike

rinahana さんの引用:
公立学校でエクセルを教えているのですが・・・。
   33  FROM    熊本県    **** 円 答えがでる。
ほほ〜、Excel の先生でいらっしゃると。ちなみに、科目(課目?)は何になるのですか?数学?情報?IT?
もうひとつ、ちなみに、挙げられた料金表は郵便局のものですか、それとも、宅配業者のもの?それを Webで参照可能なら URL を教えてください。
 
先生、「****」に表示される数値は何が正解か教えてください。
1339?2462?それとも?

回答
投稿日時: 17/11/23 12:32:06
投稿者: んなっと

「サイズ」の部分が情報不足です。
例えば60は「0より大きくて60以下」で、20刻みで大きくなるなら...
 
     A     B    C    D    J    K     L    M
 2 サイズ  北海道 北東北 南東北  中国  四国   九州  沖縄
 3      北海道             愛媛県  福岡県 沖縄県
 4                      香川県  佐賀県    
 5                      高知県  長崎県    
 6                      徳島県  熊本県    
 7                           大分県    
 8                           宮崎県    
 9                          鹿児島県    
10                                  
11   60   1339  1015   907  1123  1123   1339  1339
12   80   1563  1239  1131  1347  1347   1563  1887
13   100   1787  1463  1355  1571  1571   1787  2435
14   120   2011  1687  1579  1795  1795   2011  2983
15   140   2235  1911  1803  2019  2019   2235  3531
16   160   2462  2138  2030  2246  2246   2462  4082
17                                                
 
31  115   cm     
32 料金          
33 From  熊本県  2011 円
 
C33
=VLOOKUP(CEILING(A31,20),$11:$16,AGGREGATE(15,6,COLUMN($B:$M)/($B$3:$M$10=B33),1),)
とか
=INDEX($11:$16,MATCH(0,INDEX(0/(A31<=$A$11:$A$16),),0),AGGREGATE(15,6,COLUMN($B:$M)/($B$3:$M$10=B33),1))

回答
投稿日時: 17/11/23 13:14:57
投稿者: んなっと

これも。
=INDEX($11:$16,MATCH(TRUE,INDEX(A31<=$A$11:$A$16,),0),AGGREGATE(15,6,COLUMN($B:$M)/($B$3:$M$10=B33),1))

投稿日時: 17/11/23 14:23:43
投稿者: rinahana

んなっと さん 早速の回答をありがとうございます。
 
ヤマト運輸 料金・お届け予定日を調べる 宅急便運賃一覧表 関東
//www.kuronekoyamato.co.jp/ytc/search/estimate/kanto.html
 
です。
 
そして、115 cmは 適当に 縦横高を考えての 数値です。
 
教えていただいた、式はまだ 試していません。これから です。
わかるかな
 
ありがとうございます。
 
 

回答
投稿日時: 17/11/23 15:23:59
投稿者: んなっと

引用:
「サイズ」の部分が情報不足です。
例えば60は「0より大きくて60以下」で、20刻みで大きくなるなら...

というのは、A11:A16のことです。
A11:A16の意味も最初から具体的に説明していただけたらよかったのですが。
 
     A
 2 サイズ
 3    
 4    
 5    
 6    
 7    
 8    
 9    
10    
11   60
12   80
13   100
14   120
15   140
16   160
 

投稿日時: 17/11/23 16:57:36
投稿者: rinahana

Mike さん、んなっとさん 
ありがとうございました。
荷物の長さの部分ですが、20cm刻みを説明不足で済みませんでした。
さて、
「@AGGREGATE(15,6,COLUMN($B:$M)/($B$3:$M$10=B33),1)の 値は「12」列なので、九州地方のL列を算出していますが、
 
COLUMN($B:$M)/($B$3:$M$10=B33),1 の部分が分りません。できれば、砕いて説明してもらえませんか?」
 
結果は同じ、3つの数式のカラムの割り算?の部分を・・・
 
ほとんど 教科書や問題集に出ていない手法なので・・・
 
お願いします。 もし、無理であれば、そのままでも結構です。
ともかく ありがとうございました。
 

回答
投稿日時: 17/11/23 17:43:40
投稿者: んなっと

似たケースで解説があります。
http://www.moug.net/faq/viewtopic.php?t=76116

回答
投稿日時: 17/11/23 19:03:11
投稿者: Mike

C34: =VLOOKUP(A32,A11:M17,MAX(IF(B3:M10=B31,COLUMN(B3:M10),"")))
【お断り】上式は必ず配列数式として入力のこと
    A    B    C   D    E    F  〉〉  K    L    M
1  FROM  九州                 〈〈
2  サイズ  北海道  北東北 南東北 関東   信越  〉〉 四国  九州   沖縄
3      北海道  青森県 宮城県 茨城県  新潟県〈〈 香川県 福岡県  沖縄県
4          秋田県 山形県 栃木県  長野県 〉〉 徳島県 佐賀県
5          岩手県 福島県 群馬県     〈〈 愛媛県 長崎県
6                 埼玉県     〉〉 高知県 熊本県
7                 千葉県     〈〈     大分県
8                 神奈川県    〉〉    宮崎県
9                 東京都     〈〈     鹿児島県
10                 山梨県     〉〉
11   0.00   0    0   0    0   0〈〈    0    0   0
12  60.01 1,987  1,555 1,555  1,339 1,339 〉〉 1,015   907 1,231
13  80.01 2,203  1,771 1,771  1,555 1,555〈〈  1,231  1,123 1,771
14  100.01 2,441  2,009 2,009  1,793 1,793 〉〉 1,469  1,361 2,333
15  120.01 2,657  2,225 2,225  2,009 2,009〈〈  1,685  1,577 2,873
16  140.01 2,894  2,462 2,462  2,246 2,246 〉〉 1,922  1,814 3,434
17  160.01 3,110  2,678 2,678  2,462 2,462〈〈  2,138  2,030 3,974
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜             
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
31  TO   神奈川県
32  115   cm
33  料金
34  FROM  熊本県  1,793

回答
投稿日時: 17/11/23 19:23:00
投稿者: Mike

Mike さんの引用:
11   0.00   0    0   0    0   0〈〈    0    0   0
12  60.01 1,987  1,555 1,555  1,339 1,339 〉〉 1,015   907 1,231
13  80.01 2,203  1,771 1,771  1,555 1,555〈〈  1,231  1,123 1,771
14  100.01 2,441  2,009 2,009  1,793 1,793 〉〉 1,469  1,361 2,333
15  120.01 2,657  2,225 2,225  2,009 2,009〈〈  1,685  1,577 2,873
16  140.01 2,894  2,462 2,462  2,246 2,246 〉〉 1,922  1,814 3,434
17  160.01 3,110  2,678 2,678  2,462 2,462〈〈  2,138  2,030 3,974
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜             
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
31  TO   神奈川県
32  115   cm
33  料金
34  FROM  熊本県  1,793
大きなミスを犯していました。下記に訂正願います。
11   0.00 1,987  1,555 1,555  1,339 1,339 〉〉 1,015   907 1,231
12  60.01 2,203  1,771 1,771  1,555 1,555〈〈  1,231  1,123 1,771
13  80.01 2,441  2,009 2,009  1,793 1,793 〉〉 1,469  1,361 2,333
14  100.01 2,657  2,225 2,225  2,009 2,009〈〈  1,685  1,577 2,873
15  120.01 2,894  2,462 2,462  2,246 2,246 〉〉 1,922  1,814 3,434
16  140.01 3,110  2,678 2,678  2,462 2,462〈〈  2,138  2,030 3,974
17
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜             
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
31  TO   神奈川県
32  115   cm
33  料金
34  FROM  熊本県  2,009

回答
投稿日時: 17/11/24 10:11:15
投稿者: んなっと

今回の質問を修正すると、こうでしょうか?
 
     A     B    C    D E F G H I J    K     L
 1  From   関東                          
 2 サイズ  北海道 北東北 南東北           四国   九州
 3      北海道                  愛媛県  福岡県
 4                           香川県  佐賀県
 5                           高知県  長崎県
 6                           徳島県  熊本県
 7                                大分県
 8                                宮崎県
 9                               鹿児島県
10 〜以下                               
11   60   1339  1015   907           1123   1339
12   80   1563  1239  1131           1347   1563
13   100   1787  1463  1355           1571   1787
14   120   2011  1687  1579           1795   2011
15   140   2235  1911  1803           2019   2235
16   160   2462  2138  2030           2246   2462
17                                   
18                                   
19                                   
20                                   
21                                   
22                                   
23                                   
24                                   
25                                   
26                                   
27                                   
28                                   
29                                   
30  From 神奈川県←関東だから、無視             
31   115   cm                          
32  料金                               
33   To  熊本県  2011   円                  
 
 
 
※もう一度同じAGGREGATEの解説を貼り付けておきます。
 
公式
AGGREGATE(15,6,数値/(条件),1) で、条件を満たす1番小さい数値。
AGGREGATE(15,6,数値/(条件),2) で、条件を満たす2番目に小さい数値。
AGGREGATE(15,6,数値/(条件),3) で、条件を満たす3番目に小さい数値。
エラーは無視されます。
  
  
[例]下のB$1:G$1で、5の倍数を小さいほうから順に取り出すとき。
  
                  B   C    D   E    F   G                         
          B$1:G$1     9  20    12  15    18  30 
  
  
1. (条件)の部分にMOD(B$1:G$1,5)=0を当てはめる。
                  B    C     D    E     F    G         
          B$1:G$1     9   20     12   15     18   30 
     MOD(B$1:G$1,5)=0   FALSE  TRUE   FALSE  TRUE   FALSE  TRUE 
B$1:G$1/(MOD(B$1:G$1,5)=0)  9/FALSE 20/TRUE 12/FALSE 15/TRUE 18/FALSE 30/TRUE
  
  
2. ワークシートの数式内の演算では、FALSE0 TRUEは1
  
                  B   C    D   E    F   G         
          B$1:G$1     9  20    12  15    18  30 
B$1:G$1/(MOD(B$1:G$1,5)=0)    9/0 20/1   12/0 15/1   18/0 30/1 
                 
  
3. 0で割るとエラーになる。これで(条件)を満たさない数値は除外できた。
  
                  B   C    D   E    F   G       
          B$1:G$1     9  20    12  15    18  30  
B$1:G$1/(MOD(B$1:G$1,5)=0)  #DIV/0!  20 #DIV/0!  15 #DIV/0!  30      
  
  
4. 最後は公式通り。
                                                                      
AGGREGATE(15,6,B$1:G$1/(MOD(B$1:G$1,5)=0),1) 15 ←エラーは無視して1番小さい値
AGGREGATE(15,6,B$1:G$1/(MOD(B$1:G$1,5)=0),2) 20 ←エラーは無視して2番目に小さい値
AGGREGATE(15,6,B$1:G$1/(MOD(B$1:G$1,5)=0),3) 30 ←エラーは無視して3番目に小さい値

トピックに返信