エクセルの数式で質問があります

質問

Ａ1のセルに
「○○支店　出張交通費」
と入力された場合に
Ｂ1のセルに○○と表示される数式というのは作れるのでしょうか？

Ａ1に入力されるものにルール付けがないので
「東京支店」「大阪」「鹿児島営業所」
となっています。

支店・営業所名の一覧データベースを作ってそこから引っ張ってくるのが一番だと思うのですが、どんな数式を組むべきかまったく見当がつきません。

よろしくお願いいたします。

回答No.7

macchan1 · ベストアンサー

INDEX関数はOKということで、拠点名シートのB列の上から何番目かを求める以下の部分を説明します。

SUMPRODUCT(ISNUMBER(FIND(拠点名!B1:B6,A2))*ROW(拠点名!B1:B6))

FIND(拠点名!B1:B6,A2)は、A2が拠点名!B1:B6に入っている場合は数値、無い場合はエラー（#VALUE）の配列を返します。
これをISNUMBER関数で計算すると、数字であるものはTrue、エラーはFalseの配列になります。この配列にROW(拠点名!B1:B6)を掛けるとFalseは0、TRUEは一覧表行の行数の数字の配列が求められます。
この数式をSUMPRODUCT関数で囲うと自動的に配列の積の和が求められます（該当するセルが1つの場合はそのセルの行数）。

計算の途中経過を調べるには、上記の数式の該当部分を選択してF9キーを押しください。

例えば、数式のISNUMBER(FIND(拠点名!B1:B6,A2)の部分やFIND(拠点名!B1:B6,A2)の部分を選択してF9キーを押します。
なお、数式結果を確認して元に戻るときはEscキーを押してください。

お礼

ありがとうございます。

各関数を調べてみたんですがどうしても分からない部分がありました。Ｆ９というのを今回はじめて知ってすごく助かりました。

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

回答No.6

macchan1

＃５の訂正と補足です。

B2セルに記入する場合は

=INDEX(拠点名,SUMPRODUCT(ISNUMBER(FIND(拠点名,A2))*ROW(拠点名)))

もちろん、拠点名の部分は拠点名の部分のセルを絶対参照してもOKです。

補足

すごいです。ホントにできました。

拠点名というシートに上から順番に拠点名を入力し
INDEX(拠点名!B:B,SUMPRODUCT(ISNUMBER(FIND(拠点名!B1:B6,A2))*ROW(拠点名!B1:B6)))
で見事に表示されました。

でも、なぜこうなるのかがまったく理解できません。
後学のためにご説明いただけましたら嬉しいのですが・・・

とりあえずINDEXは理解できました。
ただそれ以降がチンプンカンプンです。

回答No.5

macchan1

了解しました。

A列に記入されている文字列の中に、拠点一覧の文字列が入っていれば、その名前をB列に表示するようにします。

別シートなどに項目名を記載せずに1行目から記入した拠点一覧の範囲を選択し、画面左上の名前ボックスに例えば「拠点名」と入力してEnterしてその範囲に名前を付けてください。
B2セルに以下の式を入力します。

=INDEX(拠点名,SUMPRODUCT(ISNUMBER(FIND(拠点名,A1))*ROW(拠点名)))

回答No.4

macchan1

ご質問の主旨が良く分かりません。

＞Ａ1に入力されるものにルール付けがないので「東京支店」「大阪」「鹿児島営業所」となっています。

このようなデータが入っていたときはどのような処理をしたいのでしょうか？

支店の左にある文字列はLEFT関数とFIND関数で簡単に抽出できますが・・・

エクセルの関数では、一定の規則がある場合それに基づいて必要な部分を抜き出すことはできますが、例えば、県名を判断することはできません（一覧を作ってけおけば可能ですが）。

いずれにしろ具体例を含めてもう少し詳しく希望の操作を説明して下さい。

補足

説明不足で申し訳ありません。

例えば
東京・大阪・名古屋・福岡・仙台・札幌
の各拠点があるとします。

No1 の方の回答にも書いたのですが、出張交通費だけでなく様々な勘定科目がある上に入力ルールがないので各拠点バラバラで送ってきます。

「東京支店　出張交通費」
「本社への出張（大阪）」
「福利厚生費　仙台営業所」

というように頭に拠点名のところもあれば、後ろのところもあるし、支店・営業所をきちんとつけてくれるところもあれば拠点名だけのところもあります。
そこで、拠点一覧から引っ張ってこれたらいいな～
と思っているのですが。。。。

例えば
「東京支店　消耗品費」「東京」
「本社への出張（大阪）」「大阪」
「鹿児島営業所　宅配便代金（東京支店への備品）」「エラー」
という感じで２拠点に該当する場合はエラーでかまいません。
Ａ１のセルを見て拠点一覧に該当する拠点名が入っていたらそれを表示して、入ってなかったり２拠点以上該当の場合はエラーという数式を組みたいんです。
別の方法があるんだったらそれでもかまいませんので、宜しくお願いします。

回答No.3

maggoteating

必ず「出張交通費」の記載のある場合は、その左側の文字列を取り出すことは可能です。Ｂ１に

　=MID(A1,1,FIND("出張交通費",A1)-1)

　と入力すると、「東京支店」「大阪」「鹿児島営業所」が取り出せます。ただし、「出張交通費」の記載のない場合は取り出せません。

補足

No2 の方の回答にも書いたのですが、出張交通費だけではなくさまざまな勘定科目があるもので。。。。

でも、これまたはじめて見た関数なので今度利用させていただこうと思います。

ありがとうございます。

回答No.2

saintandre

=LEFT(A1,FIND("出張交通費",A1)-1)

こんなんでいかかがでしょうか、「出張交通費」の手前の文字列を持ってきます。

-1を-2にすると手前のスペースも除外します。ただし「東京支店出張交通費」とスペース無しで書かれると結果が「東京支」になっちゃいますけど。

あと「交通主張費」とか変換ミスすると拾ってくれません。

補足

説明不足ですみません。

実は出張交通費だけでなくさまざまな勘定科目があるので、この数式ではできませんでした。。。

本当に申し訳ありません。
でも、LEFT関数は使ったことがないので今度参考にさせていただきます。

回答No.1

kouzi_kouji

＞支店・営業所名の一覧データベースを作ってそこから引っ張ってくるのが一番だと思うのですが、どんな数式を組むべきか
　「データ」のサブメニューに「入力規則」というのがあります。その設定で、「入力値の種類」を「リスト」と設定すれば、そのリストから選択するように出来ます。
　すべての支店名、出張先を１列に並べて作ります。
　「設定」の「元の値」の所で、その列を設定すれば、そこからのセンタクリストが表示されます。
　分からない場合は、補足質問して下さい。

補足

説明不足ですみません。

実は各支店および営業所から様々な形式で入力されたデータが送られてきます。
「東京支店　宿泊費」
「仙台　タクシー代」
「鹿児島営業所　飛行機チケット」
中には
「営業交通費（大阪支店）」
というのもあります。

本当は入力ルールを決める形がよいと思うのですが本社に入社してまだ日が浅いため強く言えないのが現状です。そこでせめて入力するときに数式でできたら良いなと思っています。

Ａ1→費用項目
Ｂ1→使用拠点

という形の入力なので、各拠点の項目をコピーして値の貼り付けをすると自動的に使用拠点が出ればいいなと思ったもので。。。
かならず自分の目で確認しようと思っているので、エラーになるセルがあるのはしょうがないと思っています。
例えば「大阪支店　東京までの出張交通費」
これだと、大阪と東京の２拠点がヒットしてしまいますので、その場合はエラーでもいいんですが。。。

入力規則も考えたのですが、拠点が100以上あるのでちょっと難しいです。