EXCEL関数に関して教えてください

質問

はじめまして。

一週間前から考えていても解決出来ず、教えて
頂けたらと思います。

例えば、下記のEXCELシートがあるとします。

　　A列　　　　B　　　　　　C　　　　　　D

1　6月1日　　東京都　　宅急便　　　
2　6月1日　　神奈川県　クール宅急便
3　6月2日　　北海道　　宅急便

D列に東京都で宅急便なら420円
東京都で宅急便なら630円、
神奈川県で宅急便なら500円、クール宅急便なら710円と
47都道府県の宅急便の場合とクール宅急便の場合の
価格を反映させたいのです。

最終的には、6月1日　420円　何件って感じで
集計したいのです。

最終工程に行きつく前に、ORIF関数を使いましたが
その関数をつなげられないのです。

何かアイデアがありましたら、お知恵を拝借したいと
思っています。

宜しくお願い致します。

回答No.4

ganbei · ベストアンサー

おはようございます。
都道府県名と宅急便種別を決定（入力）したときに値段を引っ張ってくる方法です。
VLOOKUP、HLOOKUPを使えばよいのではないでしょうか。以下に例を示します。
集計は先発の回答者さまの回答を参照してください。

7行目までは、別のシートに切り取って貼り付けた方が良い。
行　　　列B　　　　　　　C　　　　　　D　　　　　　　E
2　　　　　　　　　　　　　宅急便　　 クール　　　 　その他
3　　　　　　　　　　　　　 2　　　　　　3　　　　　　　4　　　　　　　<--宅急便種別コード
4 　　　　　東京都　　　420　　　　　470　　　　 　520　　　　　　　（Vlookupの参照列番号）
5 　　　　　大阪府　　　520　　　　　570　　　　　 620
6　　　　　 北海道　　　620　　　 　 670　　　　　 720
7 　　　　　長崎　　 　　720　　　　　770　　　　　　820
8
9　　　　　　B　　　　　　C　　　　　　D　　　　　　　E
10　　　　　月日　　　　都道府　　　便種別　　　値段
11　　 　　 6月1日　　 東京都　　　宅急便　　　420
12　　 　　 6月1日　　 北海道　　　宅急便　　　620
13　 　　　 6月1日　　長崎　　　　　宅急便　　　720
14 　　　 　6月1日　　大阪府　　　宅急便　　　　520
15 　　　　 6月1日　　東京都　　　クール　　　　470
16　　　　　6月1日　　北海道　　　クール　　　　670
17　　　　　6月1日　　長崎　　　　　クール　　　　770
18　　　　　6月1日　　大阪府　　　クール　　　　570
19　　　　　6月1日　　東京都　　　その他　　　　520
20　　　　　6月1日　　北海道　　　その他　　　　720
21　　　　　6月1日　　長崎　　　　　その他　　　　820
22　　　　　6月1日　　大阪府　　　その他　　　　620

値段の項に入れる式（例：一番目のデータ、11行目）
=VLOOKUP(C11,$B$4:$E$7,HLOOKUP(D11,$C$2:$E$3,2),FALSE),FALSE)
・範囲は列・行とも＄を付けて固定する。
・FALSEはVLOOKUPとHLOOKUPの分の2つ。
　これを入れないと範囲データがソートされていないとエラーになる。
　　　　　　　・二番目以降はこれを数式コピーする。
ご参考まで。

お礼

お返事が遅くなってごめんなさい。

初心者の私には、丁寧に教えて頂きまして
大変に助かりました。

今後、似たような案件がありましたらば
この関数で作れるようにしていきます。

改めてありがとうございました。

回答No.3

hirohirohiroiya

このような形ではどうでしょうか。

IF(AND(C1="宅急便",OR(b1="長崎県",b1="福岡県",b1="鹿児島県")),800,0)
+IF(AND(C1="クール宅急便",OR(b1="長崎県",b1="福岡県",b1="鹿児島県")),1010,0)
+IF(AND(C1="宅急便",OR(b1="高知県",b1="徳島県",b1="愛媛県")),500,0)
+IF(AND(C1="クール宅急便",OR(b1="高知県",b1="徳島県",b1="愛媛県")),710,0)

（見やすさを考え、改行を入れています。）

お分かりになる方法のほうがよいと思いますので、上記のような形でご案内させていただきました。
orだけでなく、andも使っています。

VLOOKUPは、便利ですが、説明しにくいです。こういった式を、今後お使いになられるのでしたら、、VLOOKUPのヘルプを一度ご覧になられて、実験されれば使いこなせる事と思います。

お礼

お礼が遅くなってすいませんでした。

このやり方でも試してみました。

成功しました。
ご親切にありがとうございました。

回答No.2

playobject

1つの手法ですが…。
集計表、データ表と2つに分けて考えます。

上で挙げている例をデータ表とするならば、
違う場所に、全条件の集計表をどこかに作ります。

　　 　列１　　　　　 列２　　　　　列３　　　　　　　 列４
行1　2012/6/1　 東京都　　　宅急便　　　　　 「式」　
行2　2012/6/1　 神奈川県　 クール宅急便 　「式」　
行3　2012/6/1　 北海道　　　宅急便　　　　 　「式」　
行4　2012/6/2　 東京都　　　宅急便　　　　 　「式」　
行5　2012/6/2　 神奈川県　 クール宅急便 　「式」　
行6　2012/6/2　 北海道　　　宅急便　　　　 　「式」　

列4:行1に以下の式を入れる（列、行は実際の位置に変えて下さい）
=COUNTIFS(A:A,列１:行１,B:B,列2:行1,C:C,列3:行1)
列4：行2以降はこの列4：行1のセルをコピーで。

細かいことは「COUNTIFS」を調べて下さい。

問題は日数分列2と列3の条件の行を作らないといけないところですかね…。
一か月の日数分*条件の数だけの集計表が必要です。
あと、列2と列3に同じことを複数書くのはちょっとかっこわるいところ…。

おまけ
集計表は以下の順番でも良いです。　
たぶん、こっちのほうが作りやすいと思います。
　　　 列1　　　　　列2　　　列3　　　列4
行1　2012/6/1　東京都　宅急便　「式」
行2　2012/6/2 東京都　宅急便　「式」
行3 …（6/31まで）
行x　2012/6/1　神奈川県　クール宅急便　「式」
行x　2012/6/2　神奈川県　クール宅急便　「式」

お礼

お返事がおそくなって申し訳ございませんでした。

丁寧に教えて頂きありがとうございました。

この方法で試しましたが、ちゃんと反映されました。
ありがとうございました。

回答No.1

hirohirohiroiya

別シートに地域ごとの宅急便とクール宅急便の価格を作成すれば、VLOOKUP関数を使う事でスマートになりますが、fwkn8896さんの行いたいことは、違うみたいですね。

D列に入力した式を教えてもらえれば、回答ができると思います。

>最終的には、6月1日　420円　何件って感じで集計したいのです。
ここは、ピボットテーブルという機能を使えばすぐにできてしまいます。

お礼

お礼が遅くなってごめんなさい。

こちらの関数でも出来ました。
色々と勉強になり、感謝しています。

ありがとうございました。

補足

ご親切にありがとうございます。

私の理想としましては、例えば金額が同じ「長崎県・福岡県・鹿児島県」
の宅急便は800円、クール宅急便1010円。
IFOR関数で、続きに「高知県・徳島県・愛媛県」の宅急便500円、クール
宅急便は710円・・・って感じに繋げたいのですよね。

それって厳しいでしょうか？

エラーが出てしまうので、入力した式が掲載出来ませんでした。

最終的にピボットテーブルの案を教えて頂きまして
ありがとうございます。

会社での資料なので、私が作成しているだけに
どんな方法でも結果が出れば良いと思っています。
VLOOKUP関数でD列に各都道府県の宅急便・クールの
金額って入りますか？

初歩的ですいません。