• ベストアンサー

エクセル　一定の条件に沿ってデータを変換する方法を教えてください。

エクセルのデータを一定の条件に沿って変換する方法を教えてください。 　　　A　　　　　　　B　 1【顧客名】　　　【変換後】 2 (株)いろは　　　　　御社 3 (株)いろは銀行　　　貴行 4 いろは信用金庫　　貴信金 5 いろは病院　　　　貴院 ※A列に元データ、B列に変換後のデータを関数で表示させる。 条件としては、顧客名に一定の文字が含まれていることを判断して変換ます。 含む文字→変換結果 銀行　→貴行 信用金庫　→貴信金 病院　→貴院 どれも含まない　→御社 『御社』以外の特殊なパターンが約10あるので、IF式で条件をひたすら連ねる・・以外の方法でお願いします。 データは内容・量ともに、毎回変わるのでA列オートフィルタでB列に手入力、という方法も避けたいのです。 よろしくお願いします。

質問者が選んだベストアンサー

  • ベストアンサー
  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.4

語句そのものであれば、VLOOKUP関数を使い、対応表を作ればよくて簡単です。 もちろん対応表は絶対必要なのだが。 本件は社名の１部に出てくることが、関数で捉えるのを難しくしている。 それと基準をデータの方からでなく、「組織名」の方から進めない（検索を繰り返す必要あり）といけないことが、関数では難しくしている。それで配列数式などの回答になるわけ。 ーー 別法としてユーザー関数を作って見た（VBA） １つずつ順に、組織名語句を含んでないか繰り返して調べるだけ。 標準モジュールに Function keisho(a) d = Range("j65536").End(xlUp).Row For i = 1 To d k = Cells(i, "J") p = InStr(a, k) If p <> 0 Then keisho = Cells(i, "j").Offset(0, 1) Exit Function End If Next i keisho = "御社" End Function ーーーーー 例データ 内容については疑問あるが自信はない。 A列　　　　　　B列（関数で出た結果） (株)いろは 御社 (株)いろは銀行 貴行 御行？ いろは信用金庫 貴信金 貴金庫？ いろは病院 貴院 ドッグ動物病院 貴院 大田信金 貴金庫 大田区役所 貴所 金井有限会社 御社 医療法人敬人会 貴院 いろは会 貴会 B1は　=keisho(A1) と入れて下方向に式を複写。 ーー 変換（対応）テーグル　J列＋K列　文字数順出現が望ましい。 J列　　　K列 銀行 貴行 信用金庫 貴金庫 病院 貴院 信金 貴金庫 信託銀行 貴行 区役所 貴所 医療法人 貴院 会社 御社 会 貴会（最後の方に入れること）

kamalaa
質問者

お礼

お礼が遅くなり申し訳ありません。 VBA初心者なので理解するのに時間がかかっていました。 ユーザー関数、本当に便利ですね！ 今後勉強して使っていけるように頑張ります☆★☆ 本当にありがとうございました。

その他の回答 (3)

  • cj_mover
  • ベストアンサー率76% (292/381)
回答No.3

こんにちは 自分ではリストにする方を選びますが、リストを用いないならば、 =INDEX({"御社";"貴行";"貴信金";"貴院"},SUMPRODUCT(COUNTIF($A1,{"*銀行*";"*信用金庫*";"*病院*"})*ROW($A$1:$A$3))+1) とか、一応、編集し易そうな数式、ということで、、、 必ずセミコロンで区切って下さいね。 ２つの配列の要素数が、ひとつずれてる点もご注意を。 ２つめの配列（リスト）に該当しない場合、１つめの配列（リスト）の１番目「御社」を返す意味です。 要素が増えたら $A$1:$A$3 の 3 の部分も増やして下さい。 シート上では確認しましたが、 題意を取り違えてたらご勘弁を。

kamalaa
質問者

お礼

お礼が遅くなりまして申し訳ありません。 リストを作成せずに、セミコロンを用いて数式に入れる方法があるのですね。勉強になりました☆ ありがとうございました。

  • mu2011
  • ベストアンサー率38% (1910/4994)
回答No.2

次の方法は如何でしょうか。 （１）変換表を別シート（sheet2)に作成 　　Ａ列　　　　Ｂ列 １　その他　　　御社 ２　*銀行*　　　貴行 ３　*信用金庫*　貴信金 ４　*病院*　　　貴院 ～ ｎ　*ｘｘｘ*　　貴ｘｘ （２）元シートのＢ列に次の数式を設定 =INDEX(Sheet2!B:B,MAX(SUMPRODUCT(COUNTIF($A1,Sheet2!$A$1:$A$10)*ROW(Sheet2!$A$1:$A$10)),1))

kamalaa
質問者

お礼

お礼遅くなりまして申し訳ありません。 MAX関数で「御社」を表示させるのですね！ これなら理解できました。 ありがとうございました☆★☆

  • mshr1962
  • ベストアンサー率39% (7418/18948)
回答No.1

どこかに変換結果の表を作成（別シートでも可） 行|AA|AB 1|含む文字|変換結果 2|*|御社 3|銀行|貴行 4|信用金庫|貴信金 5|病院|貴院 B2=INDEX($AB$1:$AB$5,MAX(ISNUMBER(SEARCH($AA$1:$AA$5,B2))*ROW($AA$1:$AB$5))) と入力してCtrl+Shift+Enterで確定して配列数式にする。

kamalaa
質問者

補足

早々にご回答いただきながら、お礼がおそくなりまして申し訳ありません。 ～SEARCH($AA$1:$AA$5,B2 は ～SEARCH($AA$1:$AA$5,A2 だと思われたので変更したところ、確かにこの式でいけました。 複雑な式なので、時間をかけて解読しようと頑張ったのですが 意味が全然わかりませんでした(´＿｀。) ・・・よろしければ、今後の勉強の為に意味を教えていただければうれしいです。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • EXCELで以下の事をやりたいです。

    EXCELで以下の事をやりたいです。 A列,B列に以下のデータがあったとして イロハ社,コーヒー, アイウ社,紅茶 イロハ社,オレンジ, イロハ社,ウーロン茶 これを、 A列,B列 イロハ社,コーヒー 　　　　オレンジ 　　　　ウーロン茶 アイウ社,紅茶 と、いう風に、A列（会社名別）で集計し、B列をすべてたした状態で表示したいです。 また、表示する際に、行別に、改行できるようにします。（コーヒーとオレンジは改行される） よろしくおねがいします。 　

  • 貸金庫の利用条件

    当方、銀行の貸金庫を利用したことが一度もありません。 所定の利用料金を納めることは言うまでもありませんが、それに加えて、銀行業界の慣例として、貸金庫の契約にあたり、いくつかの条件を求められることが多いのでしょうか？ つまり、これは推測ですが、その店舗と一定年数以上の取引の履歴があることや、あるいは現時点で、一定額以上の定期預金の残高があることなど、顧客としての一定の信用力の有無を、契約の前提とするのが通例なのだろうか、という点をお尋ねします。

  • EXCELのデータを変換

    こんにちは。 早速ですが質問です。 あるセルに A B 1 9:00-16:00 17:00-21:00 2 12:00-21:00 などと文字列のデータが入っていて そこから別のシートに A B 1 9:00 17:00 2 16:00 21:00 3 12:00 4 21:00 というデータに変換したいのですが どのような手順をすべきでしょうか？ 数式だけでできるならありがたいです。 一応マクロやVBAも少しはわかりますが・・ 皆様よろしくお願いします。

  • 一定条件を満たした時刻を割り出したい

    スマートな解決が思いつかなかったのでお力を頂ければ幸いです。 エクセルシートで ・A列には1分刻みで時刻が並んでいます ・B列は別シートのデータを参照し、ある一定の閾値を超えたら+1ずつカウントアップするようにしています（具体的にはB2セルに「=IF(AND(別シート!B1<=閾値,別シート!B2>閾値),B1+1,B1)」、以下オートフィルといった式を想定してください） この条件で、B列がカウントアップした時のA列の値を引っ張り出すにはどうすれば良いでしょう？ VLOOKUPで行けるかなと思ったんですが、扱い不慣れな関数ゆえか引数に上手い当てはめができず1とかN/Aとかばかり引いてしまう始末で。さりとて関数一覧を見ていても他に使える手段を思いつかず… 何か上手いやり方があればご教示いただけないでしょうか。

  • EXCELで2つのファイルから重複データで抜き出したいです。

    EXCELで2つのファイルから重複データで抜き出したいです。 『A』ファイルという顧客データと『B』ファイルという顧客データがあります。 それぞれ項目は同じで、 A列＝顧客コード B列＝顧客名 C列＝住所 　　・ 　　・ 　　・ となっています。 『A』ファイルと『B』ファイルそれぞれのファイルには顧客コードに重複はありません。 『A』ファイルと『B』ファイルを比べて 重複データ以外のダブリのないファイルを作成したいです。 よろしくお願いします。

  • 信用金庫の出資金について

    信用金庫の出資金について 友人から相談をを受けたのですが答えがわからなかったので 詳しい方教えてください。 友人の経営するA社にB信用金庫の５００万の出資証券があります。 A社の経営状況は実質破たん状態（法的手続きはしていない）でそのB信金に対しても 借入金の延滞がある状態です。 A社には手持ち資金がほとんどないので安くてもいいから友人など第三者に譲渡して現金に換えたいというのですが そういったことは可能なのでしょうか？ 調べたところ『自由脱退』というものがあり（信金の会員を脱退すること） “持分の全部（出資金額）を譲渡して、会員をやめることを「自由脱退」といい ます。 自由脱退の場合、一定の期間に譲受人がいなかったときは、信用金庫が 譲り受けることになります” と記載がありました。 (1)ここだけ読むと友人等に譲渡してそれを買った友人が信金に買い取ってもらうことが可能のように思えるのですが 実際どうなのでしょうか？ (2)延滞があるとその借入金と出資金を相殺ということになるのでしょうか？ただそういったことがどこにも書いていないので 実際どうなのかを知りたいです。 (3)自由脱退で譲受人がおらず、信金が引き取る場合出資金は戻ってくるのでしょうか？ できましたら想像ではなく実務をご存知の方のご回答をお待ちしております。 よろしくお願いします。

  • エクセルでのデータ作成（数値以外のデータ抽出）

    製造業の生産管理をしています。 時系列で昨年一年間のクレーム発生件数をまとめているのですが、全クレーム件数から顧客別のパーセントを求めなければいけません。 そこから、「A社○％」「B社○％」という円グラフを作りたいのですが、どうすれば良いのかわかりません。 顧客別の並べ替えは出来たので、せめて全体件数から「A社○件」「B社○件」とカウントできれば後は簡単なのですが…。 A列に発生日、B列に顧客名、C列以降はクレーム内容などなどを入力したデータシートがあります。 以上、日曜出勤している僕の手助けをしてください。

  • 大量のCSVデータを行列の変換をしてエクセルデータにまとめる方法について

    CSVファイルが4000個ほどあり、VBAを用い、そのファイルの行列変換をして、１つのエクセルファイルにまとめたいのですが、うまくいきません。どなたか教えていただけないでしょうか？ CSVファイルは、以下の様な２列200行位あるものを、２列目のみ取り出し、エクセルファイルには１行(列ではなく)にして取り出したいのです。 変換前データー A列 　 B列 B013 毛 B014 54 B015 ポリエステル B016 36 B017 絹 B018 10 B020 0 B022 0 B023 ポリエステル B024 0 B025 キュプラ B026 0 B028 0 B030 0 B032 0 　・　　・ 　・　　・ 　・　　・ 取り込み変換後データ 1行：　毛　54　ポリエステル　36　絹　10　0　0　ポリエステル　0 キュプラ　0　0　0　0 のようにしたいのです。 どなたかお教えいただけないでしょうか？ よろしくお願いいたします。

  • 銀行、信用金庫の改まった呼び方

    会社の場合は相手の会社を言うときには 口語：御社 文面：貴社 では、銀行、信用金庫の場合はどうなるのでしょうか？ 銀行 口語：御行（オンコウ） 文面：貴行（キコウ） 信用金庫 口語：御庫（オンコ） 文面：貴庫（キコ） でよいのでしょうか？

  • エクセル　複数条件に合うデータを数えたい

    エクセル　複数条件に合うデータを数えたい エクセルでＡ列に何らかの値が入っていて（＝空欄でない）、かつＢ列の値が“○”の数を数えたいです。 =SUMPRODUCT(($A:$A="*")*($B:$B="○"))と入れましたがSUMPRODUCTはワイルドカードが使えないようで結果は「０」となってしまいダメでした。 エクセル2007を使ってるので =COUNTIFS($A:$A,"*",$B:$B,"○")としたらできたのですが2002を使っている人と共有したいので2002でも使える関数を使いたいです。 どうしたらいいのでしょうか？

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する