エクセルの学校：全文検索：Hyper Estraier：関数

Results of 1 - 1 of about 33539 for 関数 (0.006 sec.)

[[20070810002733]]: #score: 2646; @digest: 5a7d9d450f530758ac7efda719464fed; @id: 32599; @mdate: 2007-08-31T00:00:44Z; @size: 7451; @type: text/plain; #keywords: montiti (50119), livedoor (42086), vodafone (20437), フリ (13759), ル&" (11826), ーメ (10872), リー (6444), 検出 (6027), メー (5648), ル, (5228), 「x (4205), チッ (4185), ル一 (4078), 禁止 (3390), 照合 (3021), ール (2557), モン (2370), ンチ (2311), ドレ (2286), ルア (2223), 行錯 (2064), countif (2028), 錯誤 (2010), 式→ (1708), sumproduct (1696), アド (1601), ーワ (1590), right (1579), 文字 (1473), 任意 (1457), replace (1449), 試行 (1333); 『フリーメールを入力したら色を変えたい』（モンチッチ）
エクセル形式で、メルアドを含む「顧客情報登録シート」を作成しています。このシートで、フリーメールの登録を禁止したいと考えています。メールアドレス欄で、フリーメールを入力すると色を変えるなどし、注意を促すようにしたいのですが、良い方法を教えてください。フリーメールとしての禁止ドメインのリストがあり、「フリーメール」という名前をつけたところで止まってしまいました。「条件付書式」で対応しようとしたんですが、「・・・を含む」という選択肢がなく、困っています。なお@以下を別のセルに抽出し、そこで条件付書式をかけるという方法を考えてみたのですが、@以下を取り出す方法が分かりません。 ---- 例えば“AA@BB”と言うアドレスの時 1.FIND関数で「@」の位置を特定し FIND("@","AA@BB")=3 2.LEN関数で全体の文字数を数え LEN("AA@BB")=5 3.RIGHT関数でその差+1分、文字を取り出す RIGHT("AA@BB",5-3+1)=@BB 4.この文字をCOUNTIF関数で「フリーメール」の範囲から数え、その結果を条件付き書式の条件に利用出来ませんかね? COUNTIF(フリーメール,"@BB") →該当が有れば「1」無ければ「0」例えば、メールアドレスがA1に入力されているとして =COUNTIF(フリーメール,RIGHT(A1,LEN(A1)-FIND("@",A1)+1)) こんな式です。 (HANA) ---- ご報告が遅くなりましたが、教えていただいた方法でできました。ありがとうございます。追加でもう一点、可能なら実現したいことがあります。メールの中に、@livedoor.※※※.com といったように、文字が限定できないものがいくつかあります。「livedoor」などのキーワードと照合し、こういったメールも検出したいのですが、何か良い方法がありましたらご教授ください。 ---- もしかしてそのキーワードも「フリーメール」のリストに加えると =SUMPRODUCT(COUNTIF(A1,"*@"&フリーメール&"*")) という式だけで、上手く行ったりしますか? (HANA) ---- HANAさん、ありがとうございます。教えていただいた関数で試行錯誤してみましたが、勉強不足でうまく使いこなせませんでした。そこでかなりアナログな方法ですが、以下で一旦解決してみましたのでご報告しておきます。【概要】 @フリーメールリストの文字が固定しない部分をXXXと置き換え保存しておき、 A調査対象のメールの該当部分をXXX置き換え、リストと一致するか比較する方法です。 ※対象フリーメール例は以下の通りで]の個数は1〜3個となるため、1〜3個の計算式をそれぞれ別に作り確認しました。例:montiti@XXX.livedoor.com や montiti@XX.pdx.ne.jp や montiti@X.vodafone.ne.jp 【具体的内容】例）montiti@abc.livedoor.com というアドレスを、C3のセルに入力する場合 1.RIGHT関数で"@"より先の文字を取り出す関数式→RIGHT（C3,LEN（C3）-FIND（"@",C3）+1="@abc.livedoor.com" 2.REPLACE関数で"@"以下3文字（"abc."）を"XXX."と置き換える関数式→REPLACE（C3,2,4,"XXX."）⇒左から数えて2番目の文字から4番目の文字を"XXX."へ置き換えるという式 ="@XXX.livedoor.com" 3.COUNTIF関数で、フリーメールリストと照合する関数式→COUNTIF（フリーメール,C3）以上を組み合わせた関数式 =COUNTIF(フリーメール,REPLACE(RIGHT(C3,LEN(C3)-FIND("@",C3)+1),2,4,"XXX.")) ・@以下2文字の場合（montiti@b4.pdx.ne.jp 等） =COUNTIF(フリーメール,REPLACE(RIGHT(C3,LEN(C3)-FIND("@",C3)+1),2,3,"XX.")) ・@以下1文字の場合（montiti@t.vodafone.ne.jp 等） =COUNTIF(フリーメール,REPLACE(RIGHT(C3,LEN(C3)-FIND("@",C3)+1),2,2,"X.")) 確認のため三つもセルを使ってしまいますが・・もっと勉強しHANAさんに追いつきたいと思います。でもエクセルってすばらしいですね。ありがとうございました! ---- 一応聞いてみますが =SUMPRODUCT(COUNTIF(A1,"*"&フリーメール&"*")) こう言うのは、試行錯誤の内に入っていましたか? (HANA) ---- HANAさん、ご連絡ありがとうございます。 HANAさんに教えていただいたSUMPRODUCTという関数ですが、もちろん施行錯誤の中に入っていました。まず、関数の意味も分からずそのまま当てはめてみたのですが、どのフリーメールを入れても検出されませんでした。何かの修正で実現できないかと思いマニュアル本やヘルプで関数を調べてみたところ、 SUMPRODUCTについて「総和を求める」といった関数しか見つけられず、私の理解の範囲では使いこなすことができませんでした。せっかく教えていただいたのに、申し訳ありません。今回は上記の「置き換えて照合」方式で対応しましたが、もしお手すきな際がありましたら、=SUMPRODUCT(COUNTIF(A1,"*"&フリーメール&"*"))の解説をもう少し詳しく教えていただいても宜しいでしょうか。（モンチッチ） ---- う〜ん、それも上手く行かなかったのですか・・・。こんな表を用意したら、どんな結果になりますか? [A] [B] [C] [D] [1] 一覧有無ワード [2] あいうえ 1 いう [3] かきくけ 0 しす [4] さしすせ 1 まみ [5] たちつて 0 [6] なにぬね 0 [7] はひふへ 0 [8] まみむめ 1 A列の「一覧」が入力メアド D列の「ワード」が禁止文字を想定しています。 B2に =SUMPRODUCT(COUNTIF(A2,"*"&$D$2:$D$4&"*")) として、B8までコピーします。今回は、条件付き書式の設定にこの式を使いましたので「1」となっているセルが書式が適用されるセルです。 COUNTIF関数は、範囲の中から条件に合った物の数をカウントします。また「*」の文字はこの場合「任意の数の任意の文字」を表します。 "*"&D2&"*"→*いう*→(何か文字)いう(何か文字) と言う物を A2セルの「あいうえ」から数えます。同様に "*"&D3&"*" "*"&D4&"*" も数えます。通常COUNTIF関数の条件には一つしか設定出来ないのですが SUMPRODUCT関数の中に入れることで、同時に複数の条件を設定した COUNTIF関数を計算する事が出来ます。【概要】で書いて頂いた三つのメアドを使い禁止ワードに「livedoor」「aaa」と入れた範囲を設定して見たのですが、上手く行くのですよね。明らかに数えられる小さなサンプルを使って確認してみてください。上手く行かないようであれば、そのサンプルとどのセルに配置したのか。また、使った数式。を教えてください。 (HANA) ---- HANAさんが用意してくれた表を使用して SUMPRODUCT関数を設定したところ、うまくできました。フリーメール一覧にて同じように設定してみましたが、何故かうまくできませんでした。。色々と試行錯誤していた中で気付いた、原因と思われる点をお伝えします。 ●フリーメール一覧の書き方フリーメール一覧では、 "@"と会社ドメインとの間に、任意の文字が入るものは「X」と表示してありますが、どうもこの「X」を関数が読み取ってくれなかったようです。「X（エックス）」という文字列としてExcelが認識していたからかも知れません。この「X」の部分を、任意の文字を表す"*"に置き換え『@***.livedoor.com』とし、『montiti@abc.livedoor.com』というアドレスを入力した際フリーメールアドレス検出できました。 vodafoneやpdx等のアドレスもフリーメールとして検出できました。結果、HANAさんが教えてくださったSUMPRODUCT関数でフリーメール検出することができたのですが、出来なかった理由としては、エラー検出対照元のデータ（フリーメール一覧）の一部が条件に当てはまる形式で書かれていなかった・・・という事になるのでしょうか・・・。とっても初歩的なところではありますが・・・。 (モンチッチ） ---- ＞「livedoor」などのキーワードと照合しと書いてありますので、てっきり禁止ワードの所に「livedoor」と入っている物と思っていました。「x」が任意の文字なのか、「x」と言う文字なのかエクセルには分かりません。『*@xxx.livedoor.com』となっていれば『（何か文字）@（xが3つ）.livedoor.com』と言う物を探します。エクセルに何かをやらせようと思ったらエクセルに分かる様に書く必要があります。今回変更なさった『*@***.livedoor.com』→『（何か文字）@（何か文字）.livedoor.com』の様に。今後ご質問の際には実際に使っているデータ（若しくはそれに近い物）その配置（シートが複数あれば、シート名も）それらのサンプルでの、期待しない値を返す数式三つセットでご質問して頂くのが良いと思いますよ。何にせよ、上手く行って良かったです。 (HANA) ...; https://www.excel.studio-kazu.jp/wiki/kazuwiki/200708/20070810002733.txt - [detail] - similar

PREV NEXT

訪問者：カウンタ