=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"") まず、下記のURLにアクセスしてください。  If VarType(Range("A1").Value) = vbDouble Then 電話番号をハイフン区切りで統一して変換する方法をご紹介します。エクセル関数をうまく組み合わせる事で、一度に大量の番号を変換することができるようになります。 教えてください!, Worksheets("シートA").Range("A1") 本記事で使用するサンプルファイルは、3枚のシートから構成されています。3枚のシート名は、それぞれ「電話番号」、「メールアドレス」、「リスト」です。 「電話番号」シートには、ハイフン付きのダミーの電話番号が10件あります。「メールアドレス」には、メールアドレスのダミーデータが10件あります。 最後の「リスト」シートには、約10000件の電話番号とメールアドレスが記載されており、その右側には出力列があり、それら項目名として「電話番号(ハイフン無し)」、「メールアドレス( … >VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり    End If    Tel1 = .Cells(i, C1) # 本質的には「とる」のではなく「置換する」ことに注意。  Const C1 As String = "A" '元の電話番号が入っている列名 ブラウザでこのページを開いた状態で、 Dim readSheet As WorkSheet ' 相手シート 「ユーザー定義」なるものを使うというのはなんとなく判るのですが・・・。 そして、エクセルのSheet2(と仮にします)のA1セルを選択し、「編集」>「貼り付け」でブラウザの内容を貼り付けます。  Application.ScreenUpdating = False ="'"&SUBSTITUTE(セル,"-","") Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照 Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照 Alt+F11でVBAの画面を開き、左側のツリーからブック名を選択し、右クリックから「挿入」>「標準モジュール」を選択して、右の画面に以下のマクロをコピーして貼り付けてください。 1F  ← 文字 文字が入っていなければB1セルからC1セルを引く、という状態です。 [スポンサーリンク] (adsbygoogle = window.adsbygoogle || []).push({}); 集計業務へVBAを使う初歩的な使い方で、かつ意外と便利な方法をお伝えします。ちょっとしたデータ整形でも、対象とする個々のデータに応じて、実に様々なコードを扱う必要があります。そこで、便利な使い方として ... VBAコードの可読性、高速化、コーディング効率を向上させるWithステートメントの使い方を分かりやすいサンプルコードで解説します。 VBAコードの可読性を上げる方法の一つとして、適切なコメント文の使用 ... (adsbygoogle = window.adsbygoogle || []).push({}); 複数のワークシートをVBAで扱う1つの方法として、ワークシート名を使って複数のワークシートを管理する方法があります。集計業務では、複数のワークシートを取り扱うことがほとんどですよね。 わたしは、取りま ... VBA変数名のつけ方を、わたしが長年使っているハンガリアン記法を中心に解説します。変数名のつけ方のコツは、その変数名からその変数が何のために定義(宣言)されたのかをイメージできるような名前にすることだ ... VBAで集計業務をする際に、最低限覚えておきたいデータ型について解説します。「変数の宣言を強制する」の内容にも関係が深い内容です。 VBAのデータ型は、実は10種類以上あります。ですが、ここでは簡単に ... 文字列の右側から一定の長さを取得するRight関数を使用して、セルの中の文字列から必要な情報を取り出します。右側2文字が共通する文字であることを利用した行削除のサンプルコードです。 では今回のサンプル ... 集計対象レコードに対して、各項目別に複数条件でデータを削除したい場合に使えるVBAサンプルコードをご紹介します。基幹システムなどの生データに対し、毎回決まったデータを削除して目的のデータを整形する場合 ... 集計対象レコードのうち、特定の値を含むレコードを削除するVBAサンプルコードをご紹介します。データクレンジングなどで、集計対象外のデータを含むレコードをあらかじめ削除しておきたい場合などにも利用できま ... Excelシート上のデータを、列方向から削除するサンプルコードです。 経験上、VBAコードによる行削除に比べて、使う頻度としてはかなり少ないのですが、行削除同様、基礎的なVBAコードです。 また、行削 ... VBAでワークシートの行間に、新たに行を挿入する方法を解説します。IF文と組み合わせれば、対象レコードをグループごとに集計する小計行を作る時にも使えるスキルです。 データを確認する時に、レコードとレコ ... Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved. エクセルではセルをプルダウン(ドロップダウン)リスト形式で表示する事が可能です。リストをうまく使う事で入力や集計が楽になります。... エクセルの超便利関数であるVLOOKUPについて、データはあるけれどうまくいかない場合の対応方法をまとめました。ヒットしないような場合はセル... Chromeで知らず知らずのうちにサイトから通知が届くようになってしまった場合のブロック方法についてまとめました。不要な通知は解除しておくと... ミニバンへの買い替え検討時に行った事をまとめました。試乗した感想を元に、フリード・セレナ・ボクシーの中から1つを選び、購入しました。... CDNでVue.jsとElement UIを使用した際に、デフォルトでは中国語表記となります。これを日本語化する方法をご紹介します。... Google Meetに参加できず、「あなたはこのビデオハングアウトに参加できません」というメッセージが表示された場合の確認ポイントをまとめました... キーボード入力ができない!文字が打てなくなった!という場合の原因の1つである「固定キー機能」について、無効化する方法をご紹介します。... 個人の実印・銀行印・認印や法人の代表印・角印・銀行員・資格丸印について、画像ファイルとして作成する方法をご紹介します。... 新型コロナウィルスの感染拡大に伴い、テレワーク用の机を購入してみましたので、どんな感じかレビューをしてみました。... Windows環境でSafariを動かすFirefoxアドオン|User-Agent Switcher and Manager.    03-2222-2222  I = 2 集計業務で電話番号から"-"(ハイフン)を削除したり、メールアドレスからアカウント名やドメイン名を抜き出すデータの整形業務があります。本記事では、これらの処理をVBAで行うサンプルコードを詳しくご紹介します。, また記事の後半では、以下の処理を1つのユーザ定義関数にまとめた場合のサンプルコードについてもご紹介しております。, まずは本記事で解説する内容を、動画でサッと確認するための動画(約2分)を用意してありますので、宜しければご覧ください。, 本記事で使用するサンプルファイルは、3枚のシートから構成されています。3枚のシート名は、それぞれ「電話番号」、「メールアドレス」、「リスト」です。, 「電話番号」シートには、ハイフン付きのダミーの電話番号が10件あります。「メールアドレス」には、メールアドレスのダミーデータが10件あります。, 最後の「リスト」シートには、約10000件の電話番号とメールアドレスが記載されており、その右側には出力列があり、それら項目名として「電話番号(ハイフン無し)」、「メールアドレス(アカウント名)」、「メールアドレス(ドメイン名)」が記載されています。, 本記事では、前述した3つのワークシートを扱いますが、これらのシートは本記事でご紹介するVBAサンプルコード内では、すべてのワークシートをオブジェクト名で指定しています。, ダウンロードしたファイルのVBE画面上でもご確認頂けますが、下図の通りワークシート名とオブジェクト名が対応しておりますのでご確認ください。, 下図はサンプルファイル「電話番号」シート上のハイフン付電話番号のハイフンを削除する、VBAサンプルコードの実行前後の様子です。, [main_1]5-7行目: For r = 1 To .Range("A" & .Rows.Count).End(xlUp).Row ~ Next 「電話番号」シート(Sheet1)にあるすべての電話番号に対して、繰り返し処理をします。ここでは、Toのあとに集計対象レコードの最終行番号を求めるVBAコードを直接記載しています。, [main_1]6行目: .Cells(r, 1) = DelHyphen(.Cells(r, 1)) セル.Cells(r, 1)は、電話番号になります。まずこの後にお話しするユーザ定義関数DelHyphenに渡す引数としてハイフン付電話番号(.Cells(r,1))を渡しています。そして関数DelHyphenの戻り値であるハイフンを削除した電話番号を、.Cells(r, 1)に再代入しています。, [DelHyphen]13行目: Public Function DelHyphen(ByVal vString As String) As String ユーザ定義関数DelHyphenのプロシージャ宣言文です。vStringは、呼び出し元から渡される引数になります。また戻り値は、ハイフンを削除した電話番号ですので文字列型を指定しています。, [DelHyphen]18~22行目: For i = 1 To Len(vString) ~ Next引数で受け取った文字列を、1文字ずつ文字列の長さ分繰り返し処理します。この間の処理は、Mid関数とLen関数を使って、対象の文字列から1文字ずつ取り出した文字が、ハイフンと等しいかどうか (VBAコードでは、半角と全角の両方をチェックしています) で処理を分けています。, 具体的には、ハイフンを除く文字の場合のみ、戻り値となる変数rStringへの文字列結合処理を行います(VBAコード20行目)。これにより結果的に、電話番号からハイフンを削除したことになります。, メールアドレスからアカウント名(@より前の文字列)、およびドメイン名(@から後ろの文字列)を取得するVBAサンプルコードをご紹介します。, 次のVBAサンプルコードは、main_2がアカウント名を取得し、main_3がドメイン名を取得するプロシージャになります。, これら2つのプロシージャは、ともに ユーザ定義関数 GetMAddInfo を呼んでいますが、第二引数の指定が違います。つまりこの第二引数の指定の違いにより、それぞれアカウント名とドメイン名という異なる情報を取得できるようにしています。, では紹介したVBAサンプルコードの2つのプロシージャを実行した結果をそれぞれご紹介したいと思いますが、実行する前に1点だけ注意があります。, VBAサンプルコードは、アカウント名取得用(main_2プロシージャ)とドメイン名取得用(main_3プロシージャ)のプロシージャが、それぞれ同じ対象レコード(メールアドレスシートのA列)に対して処理をします。, よって、main_2を実行した後のアカウント名が出力されたままで、main_3を実行しても既にドメイン名以下が削除されていますので、実行結果に変化が起こりません(アカウント名取得状態のまま)。, そこで、main_2の実行前に対象レコードを隣の列などにコピペして、元のメールアドレスデータを転記しておいてください。そうすれば、main_3実行時にその隣にコピペした元のメールアドレスデータを再利用できます。, メールアドレスの@から前の部分をアカウント名、またはユーザ名と呼んだりします。実際の集計業務で経験したことですが、社員のユーザIDとして社内メールアドレスのアカウント名を利用していたところもありました。, そこでは、実際に社員のメールアドレスから集計のキーとなるユーザID(アカウント名)を抜き出した上で、集計資料を作成したこともあります。まさに本記事でご紹介しているアカウント名を抜き出すサンプルコードを利用していたのです。, 上図はサンプルコードのmain_2プロシージャを実行した結果になります。@より前の情報が取得されていますよね。, ドメイン名は、@より後ろの文字列を指します。main_3の実行結果は、以下のように@マークより後ろの文字列が抜き出されています。, アカウント名を取得するmain_2プロシージャとドメイン名を取得するmain_3プロシージャ、および共通の ユーザ定義関数 GetMAddInfoについて解説します。, なお前項でご紹介したVBAサンプルコードですが、記載の順番はmain_2、main_3、GetMAddInfoの順番ですが、ここでの解説はGetMAddInfoをはじめに解説し、その後main_2、main_3の順に解説します。, [GetMAddInfo]25行目: Public Function GetMAddInfo(ByVal vString As String, ByVal vFlag As Integer) As String ユーザ定義関数の宣言文になります。第一引数はメールアドレス(文字列)です。第二引数には、アカウント名を取得するのか、ドメイン名を取得するかを判断するためのフラグ(整数値)を設けています。, [GetMAddInfo] 27行目: Dim aPos As Integer  30行目: aPos = InStr(vString, "@") メールアドレスからアカウント名とドメイン名を分けて取得するには、メールアドレスの中に含まれる@の位置情報を知る必要があります。, そこで、上記のようにメールアドレスの中から@の前方の位置情報をInstr関数で取得し、変数aPosに代入しています。, この位置情報を得る方法については、電話番号からハイフンを取得した方法と同様に、Mid関数とLen関数を用いても位置情報を取得できますが、メールアドレスの中の@は1つしかないという特性がありますので、今回はInstr関数を使いました。, つづくVBAコード33~37行目は、第二引数の値によりアカウント名、もしくはドメイン名を取得するIf文での分岐処理になります(下図)。, 第二引数vFlagが0の場合(VBAコード赤字部)は、VBAコード34行目が実行されます。アカウント名は、@の前の文字列(赤字)ですので、変数aPos(=4) - 1文字分を、メールアドレスの左側から取得すればよいです。つまりここでは図に示す通りLeft関数を使う事で取得できます。, 第二引数vFlagが1の場合 (VBAコード青字部) は、VBAコード36行目が実行されます。ドメイン名は、@の後ろの文字列(青字)ですので、変数aPos(=4) + 1文字目から、(メールアドレス全体の文字数 - @までの文字数)を、Mid関数の第三引数に指定することで取得できます。, main_2は、メールアドレスからアカウント名を取得するプロシージャです。プロシージャ内部では、前述した ユーザ定義関数 GetMAddInfoを呼び出す際に、引数としてメールアドレスと0(アカウント名取得時)を渡しています。, [main_2]7行目: .Cells(r, 1) = GetMAddInfo(.Cells(r, 1), 0)  メールアドレスからアカウント名を取得するための ユーザ定義関数 GetMAddInfoを呼び出している行です。引き数には、.Cells(r, 1)のメールアドレスの他に、フラグとしてアカウント名取得処理をするために0を渡しています。, main_3は、メールアドレスからドメイン名を取得するプロシージャです。main_2同様、プロシージャ内部で ユーザ定義関数 GetMAddInfoを呼びだしています。そして引数としてメールアドレスと1(ドメイン名取得時)を渡しています。, [main_3]18行目: .Cells(r, 1) = GetMAddInfo(.Cells(r, 1), 1) メールアドレスからドメイン名を取得するための ユーザ定義関数 GetMAddInfoを呼び出している行です。引き数には、.Cells(r, 1)のメールアドレスの他に、フラグとしてドメイン名を取得するために1を渡しています。, 本記事でこれまで解説してきた処理内容は、「電話番号のハイフンを削除する」、「メルアドのアカウント名/ドメイン名を抜き出す」というデータ整形処理を、別々のユーザ定義関数で行いました。, 実はこれら2つの関数は、処理対象の文字列を渡して、処理後の文字列を受取るという共通点に着目すると、1つのユーザ定義関数にまとめることができそうです。, そこで、本項では機能が似ている関数を1つにまとた上で、今度は電話番号のハイフン削除と、メールアドレスからアカウント名とドメイン名の取得を一度に処理するサンプルコードをご紹介したいと思います。, ユーザ定義関数は、いくつでも作ることが出来ますが、あまりにも似たような機能の関数をたくさん作ってしまうと管理が面倒になり可読性も悪くなります。, 可能な限り似たような機能・目的を持つユーザ定義関数は、今回ご紹介するサンプルコードのようにまとめることをお勧めします。, なお、本項ではSheet3にある約10000件のデータを処理します。そこで本サンプルコードでは、処理の高速化のために二次元配列を採用しています。, 下にご紹介するVBAサンプルコードは、1つのmainプロシージャと、1つの ユーザ定義関数 GetTelEMailInfo からなります。関数の内容は、前述の通り電話番号からハイフンを削除するのと、メールアドレスからアカウント名とドメイン名を抜き出す処理です。, サンプルコード実行前後のSheet3を図示してみました。図のようにmainプロシージャを実行することにより、「ハイフン無し電話番号」、「アカウント名」、「ドメイン名」の3つの情報が一度に出力されます。, これよりVBAサンプルコードの解説になりますが、これまで本サイトで詳細にお伝えしきた内容については、簡単に解説しております。より詳細な内容については、文中のリンクを参照してください。, ユーザ定義関数 (Functionプロシージャ)やSubプロシージャに渡す引数には、引数の指定を省略できない引数と、引数の指定を省略できる引数があります。, 引数の指定を省略できない引数は、これまで解説してきた引数のことで、呼び出し側のVBAコードでは必ず指定していました。よって、この省略できない引数を、指定し忘れると当然コンパイルエラーになります。, 一方の省略できる引数は、本記事で詳しくお伝えするもう1つの引数の使い方です。この引数は呼び出し側で引数の省略が可能です。省略された場合は、ユーザ定義関数側で指定された初期値が採用されます。, 図は本サンプルコードのユーザ定義関数GetTelMailInfoの第二引数を、省略できる引数にする場合の記載方法を示しています。, 省略可能な引数の記載方法は、通常の引数の書き方に対して、前側に「Optional」を書き、後ろ側には省略された際の値を「=」に続き記述します。図の場合、呼び出し側で第二引数が省略された場合は、第二引数の値が「2」になることを意味しています。, なお、第二引数の後ろ側(本サンプルでは「= 2」)の初期値を省略すると、データ型に応じた初期値が代入されます。例えば、今回の整数型(Integer)では0、文字列型は""(ブランク)が初期値になります。, 本サンプルコードでは、第二引数の値により処理の内容を変えています。下図に第二引数の値と処理内容を表にまとめましたのでご覧ください。, 下の図は、第二引数がメールアドレスに関する処理の場合、メールアドレスの中にある@の前方からの位置情報を取得するためのVBAコードになります。, VBAコード41行目で、第二引数が0(アカウント名取得)か、1(ドメイン名取得)が指定された際に、変数aPosに@マークの前方からの位置を格納します。, VBAコード46~62行目は、第二引数の値とIf文を使う事で実際の処理を分けています。, 受取った第二引数の値に応じて処理が3つに分岐されています。処理内容については、これまで解説してきた内容と同じですので割愛しますが、図中のVBAコード56行目のIf文の途中にある"_"(アンダーバー)について補足します。, VBAコード途中の"_"(アンダーバー)は、VBE上で一行が長くなりすぎたVBAコードを見やすくするために使う改行文字です。見た目だけの効果ですので、VBAの実行結果には全く影響しません。, VBAコード11~13行目では、出力先の3列目(C列)から5列目(E列)を消去しています。, VBAコード16~20行目では、 CurrentRegionやResize を使いながら、すべてのデータ範囲を二次元配列に格納しています。これにより、集計を 二次元配列 で行う準備が出来たことになります。, VBAコード23~27行目については、下図に抜き出してみました。先ほど解説しました共通化されたユーザ定義関数GetTelMailInfoを使い、3つの処理(ハイフン無し電話番号、アカウント名、ドメイン名のそれぞれを取得)をしています。, 3つの異なる処理を同じ関数で実行できるのは、関数に渡す第二引数を変えているからです。, このように、機能・目的などが似たようなユーザ定義関数は、今回の例のようにまとめると管理しやすくなり、可読性もよくなりますのでお勧めします。, 本記事では、電話番号からハイフンを削除する処理と、メールアドレスからアカウント名、およびドメイン名を取得するVBAサンプルコードについて詳しく解説してきました。, さらに、記事の後半ではこれら3つの処理を、1つのユーザ定義関数にまとめる方法について、オプション付引数の使い方も含めて解説してきました。, 集計処理内容を、適宜サブルーチン化することでVBAコードの保守性・可読性を向上させることができますが、似たような機能・目的のサブルーチンが複数あれば、引数を適宜オプション化して1つのサブルーチンにまとめることにより、さらに保守性・可読性の向上効果が期待できます。. 風間トオル 母親 クレヨンしんちゃん 5, ソウル ハッカーズ 邪神 17, 福岡 ソフトボール 高校 強豪 41, ポケモン Bw2 キャラ 4, オタク まとめ 2ch 54, Jungle Cm ハート 11, 重岡 大 毅 感動 4, 大学名 モテ る 20, エヴァンゲリオン 序 考察 35, パーティー 類語 英語 10, アスノヨゾラ哨戒班 歌ってみた 男 5, ポケモン 英語 名 読み方 8, 歌い手 夢小説 ま ふま ふ 7, ガキの使い 動画 タピオカ 14, 村田 引退試合 なんj 7, カバ 走る 速さ 21, 辛い時 名言 仕事 11, 有吉 ゴリ ラジオ 10, Pubg 成長ミッション 21 15, 茅野市 保育士 異動 8, コウモリ 赤ちゃん 家の中 10, 風の谷のナウシカ 英語 字幕 8, 髪が 綺麗 英語 6, 花の慶次 利沙 おまつ 4, みんなの嘘 韓国ドラマ ネタバレ 33, フィッシャーズ シルク 大学 4, クレヨンしんちゃん 嵐を呼ぶ 栄光のヤキニクロード Dvdラベル 11, " /> =ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"") まず、下記のURLにアクセスしてください。  If VarType(Range("A1").Value) = vbDouble Then 電話番号をハイフン区切りで統一して変換する方法をご紹介します。エクセル関数をうまく組み合わせる事で、一度に大量の番号を変換することができるようになります。 教えてください!, Worksheets("シートA").Range("A1") 本記事で使用するサンプルファイルは、3枚のシートから構成されています。3枚のシート名は、それぞれ「電話番号」、「メールアドレス」、「リスト」です。 「電話番号」シートには、ハイフン付きのダミーの電話番号が10件あります。「メールアドレス」には、メールアドレスのダミーデータが10件あります。 最後の「リスト」シートには、約10000件の電話番号とメールアドレスが記載されており、その右側には出力列があり、それら項目名として「電話番号(ハイフン無し)」、「メールアドレス( … >VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり    End If    Tel1 = .Cells(i, C1) # 本質的には「とる」のではなく「置換する」ことに注意。  Const C1 As String = "A" '元の電話番号が入っている列名 ブラウザでこのページを開いた状態で、 Dim readSheet As WorkSheet ' 相手シート 「ユーザー定義」なるものを使うというのはなんとなく判るのですが・・・。 そして、エクセルのSheet2(と仮にします)のA1セルを選択し、「編集」>「貼り付け」でブラウザの内容を貼り付けます。  Application.ScreenUpdating = False ="'"&SUBSTITUTE(セル,"-","") Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照 Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照 Alt+F11でVBAの画面を開き、左側のツリーからブック名を選択し、右クリックから「挿入」>「標準モジュール」を選択して、右の画面に以下のマクロをコピーして貼り付けてください。 1F  ← 文字 文字が入っていなければB1セルからC1セルを引く、という状態です。 [スポンサーリンク] (adsbygoogle = window.adsbygoogle || []).push({}); 集計業務へVBAを使う初歩的な使い方で、かつ意外と便利な方法をお伝えします。ちょっとしたデータ整形でも、対象とする個々のデータに応じて、実に様々なコードを扱う必要があります。そこで、便利な使い方として ... VBAコードの可読性、高速化、コーディング効率を向上させるWithステートメントの使い方を分かりやすいサンプルコードで解説します。 VBAコードの可読性を上げる方法の一つとして、適切なコメント文の使用 ... (adsbygoogle = window.adsbygoogle || []).push({}); 複数のワークシートをVBAで扱う1つの方法として、ワークシート名を使って複数のワークシートを管理する方法があります。集計業務では、複数のワークシートを取り扱うことがほとんどですよね。 わたしは、取りま ... VBA変数名のつけ方を、わたしが長年使っているハンガリアン記法を中心に解説します。変数名のつけ方のコツは、その変数名からその変数が何のために定義(宣言)されたのかをイメージできるような名前にすることだ ... VBAで集計業務をする際に、最低限覚えておきたいデータ型について解説します。「変数の宣言を強制する」の内容にも関係が深い内容です。 VBAのデータ型は、実は10種類以上あります。ですが、ここでは簡単に ... 文字列の右側から一定の長さを取得するRight関数を使用して、セルの中の文字列から必要な情報を取り出します。右側2文字が共通する文字であることを利用した行削除のサンプルコードです。 では今回のサンプル ... 集計対象レコードに対して、各項目別に複数条件でデータを削除したい場合に使えるVBAサンプルコードをご紹介します。基幹システムなどの生データに対し、毎回決まったデータを削除して目的のデータを整形する場合 ... 集計対象レコードのうち、特定の値を含むレコードを削除するVBAサンプルコードをご紹介します。データクレンジングなどで、集計対象外のデータを含むレコードをあらかじめ削除しておきたい場合などにも利用できま ... Excelシート上のデータを、列方向から削除するサンプルコードです。 経験上、VBAコードによる行削除に比べて、使う頻度としてはかなり少ないのですが、行削除同様、基礎的なVBAコードです。 また、行削 ... VBAでワークシートの行間に、新たに行を挿入する方法を解説します。IF文と組み合わせれば、対象レコードをグループごとに集計する小計行を作る時にも使えるスキルです。 データを確認する時に、レコードとレコ ... Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved. エクセルではセルをプルダウン(ドロップダウン)リスト形式で表示する事が可能です。リストをうまく使う事で入力や集計が楽になります。... エクセルの超便利関数であるVLOOKUPについて、データはあるけれどうまくいかない場合の対応方法をまとめました。ヒットしないような場合はセル... Chromeで知らず知らずのうちにサイトから通知が届くようになってしまった場合のブロック方法についてまとめました。不要な通知は解除しておくと... ミニバンへの買い替え検討時に行った事をまとめました。試乗した感想を元に、フリード・セレナ・ボクシーの中から1つを選び、購入しました。... CDNでVue.jsとElement UIを使用した際に、デフォルトでは中国語表記となります。これを日本語化する方法をご紹介します。... Google Meetに参加できず、「あなたはこのビデオハングアウトに参加できません」というメッセージが表示された場合の確認ポイントをまとめました... キーボード入力ができない!文字が打てなくなった!という場合の原因の1つである「固定キー機能」について、無効化する方法をご紹介します。... 個人の実印・銀行印・認印や法人の代表印・角印・銀行員・資格丸印について、画像ファイルとして作成する方法をご紹介します。... 新型コロナウィルスの感染拡大に伴い、テレワーク用の机を購入してみましたので、どんな感じかレビューをしてみました。... Windows環境でSafariを動かすFirefoxアドオン|User-Agent Switcher and Manager.    03-2222-2222  I = 2 集計業務で電話番号から"-"(ハイフン)を削除したり、メールアドレスからアカウント名やドメイン名を抜き出すデータの整形業務があります。本記事では、これらの処理をVBAで行うサンプルコードを詳しくご紹介します。, また記事の後半では、以下の処理を1つのユーザ定義関数にまとめた場合のサンプルコードについてもご紹介しております。, まずは本記事で解説する内容を、動画でサッと確認するための動画(約2分)を用意してありますので、宜しければご覧ください。, 本記事で使用するサンプルファイルは、3枚のシートから構成されています。3枚のシート名は、それぞれ「電話番号」、「メールアドレス」、「リスト」です。, 「電話番号」シートには、ハイフン付きのダミーの電話番号が10件あります。「メールアドレス」には、メールアドレスのダミーデータが10件あります。, 最後の「リスト」シートには、約10000件の電話番号とメールアドレスが記載されており、その右側には出力列があり、それら項目名として「電話番号(ハイフン無し)」、「メールアドレス(アカウント名)」、「メールアドレス(ドメイン名)」が記載されています。, 本記事では、前述した3つのワークシートを扱いますが、これらのシートは本記事でご紹介するVBAサンプルコード内では、すべてのワークシートをオブジェクト名で指定しています。, ダウンロードしたファイルのVBE画面上でもご確認頂けますが、下図の通りワークシート名とオブジェクト名が対応しておりますのでご確認ください。, 下図はサンプルファイル「電話番号」シート上のハイフン付電話番号のハイフンを削除する、VBAサンプルコードの実行前後の様子です。, [main_1]5-7行目: For r = 1 To .Range("A" & .Rows.Count).End(xlUp).Row ~ Next 「電話番号」シート(Sheet1)にあるすべての電話番号に対して、繰り返し処理をします。ここでは、Toのあとに集計対象レコードの最終行番号を求めるVBAコードを直接記載しています。, [main_1]6行目: .Cells(r, 1) = DelHyphen(.Cells(r, 1)) セル.Cells(r, 1)は、電話番号になります。まずこの後にお話しするユーザ定義関数DelHyphenに渡す引数としてハイフン付電話番号(.Cells(r,1))を渡しています。そして関数DelHyphenの戻り値であるハイフンを削除した電話番号を、.Cells(r, 1)に再代入しています。, [DelHyphen]13行目: Public Function DelHyphen(ByVal vString As String) As String ユーザ定義関数DelHyphenのプロシージャ宣言文です。vStringは、呼び出し元から渡される引数になります。また戻り値は、ハイフンを削除した電話番号ですので文字列型を指定しています。, [DelHyphen]18~22行目: For i = 1 To Len(vString) ~ Next引数で受け取った文字列を、1文字ずつ文字列の長さ分繰り返し処理します。この間の処理は、Mid関数とLen関数を使って、対象の文字列から1文字ずつ取り出した文字が、ハイフンと等しいかどうか (VBAコードでは、半角と全角の両方をチェックしています) で処理を分けています。, 具体的には、ハイフンを除く文字の場合のみ、戻り値となる変数rStringへの文字列結合処理を行います(VBAコード20行目)。これにより結果的に、電話番号からハイフンを削除したことになります。, メールアドレスからアカウント名(@より前の文字列)、およびドメイン名(@から後ろの文字列)を取得するVBAサンプルコードをご紹介します。, 次のVBAサンプルコードは、main_2がアカウント名を取得し、main_3がドメイン名を取得するプロシージャになります。, これら2つのプロシージャは、ともに ユーザ定義関数 GetMAddInfo を呼んでいますが、第二引数の指定が違います。つまりこの第二引数の指定の違いにより、それぞれアカウント名とドメイン名という異なる情報を取得できるようにしています。, では紹介したVBAサンプルコードの2つのプロシージャを実行した結果をそれぞれご紹介したいと思いますが、実行する前に1点だけ注意があります。, VBAサンプルコードは、アカウント名取得用(main_2プロシージャ)とドメイン名取得用(main_3プロシージャ)のプロシージャが、それぞれ同じ対象レコード(メールアドレスシートのA列)に対して処理をします。, よって、main_2を実行した後のアカウント名が出力されたままで、main_3を実行しても既にドメイン名以下が削除されていますので、実行結果に変化が起こりません(アカウント名取得状態のまま)。, そこで、main_2の実行前に対象レコードを隣の列などにコピペして、元のメールアドレスデータを転記しておいてください。そうすれば、main_3実行時にその隣にコピペした元のメールアドレスデータを再利用できます。, メールアドレスの@から前の部分をアカウント名、またはユーザ名と呼んだりします。実際の集計業務で経験したことですが、社員のユーザIDとして社内メールアドレスのアカウント名を利用していたところもありました。, そこでは、実際に社員のメールアドレスから集計のキーとなるユーザID(アカウント名)を抜き出した上で、集計資料を作成したこともあります。まさに本記事でご紹介しているアカウント名を抜き出すサンプルコードを利用していたのです。, 上図はサンプルコードのmain_2プロシージャを実行した結果になります。@より前の情報が取得されていますよね。, ドメイン名は、@より後ろの文字列を指します。main_3の実行結果は、以下のように@マークより後ろの文字列が抜き出されています。, アカウント名を取得するmain_2プロシージャとドメイン名を取得するmain_3プロシージャ、および共通の ユーザ定義関数 GetMAddInfoについて解説します。, なお前項でご紹介したVBAサンプルコードですが、記載の順番はmain_2、main_3、GetMAddInfoの順番ですが、ここでの解説はGetMAddInfoをはじめに解説し、その後main_2、main_3の順に解説します。, [GetMAddInfo]25行目: Public Function GetMAddInfo(ByVal vString As String, ByVal vFlag As Integer) As String ユーザ定義関数の宣言文になります。第一引数はメールアドレス(文字列)です。第二引数には、アカウント名を取得するのか、ドメイン名を取得するかを判断するためのフラグ(整数値)を設けています。, [GetMAddInfo] 27行目: Dim aPos As Integer  30行目: aPos = InStr(vString, "@") メールアドレスからアカウント名とドメイン名を分けて取得するには、メールアドレスの中に含まれる@の位置情報を知る必要があります。, そこで、上記のようにメールアドレスの中から@の前方の位置情報をInstr関数で取得し、変数aPosに代入しています。, この位置情報を得る方法については、電話番号からハイフンを取得した方法と同様に、Mid関数とLen関数を用いても位置情報を取得できますが、メールアドレスの中の@は1つしかないという特性がありますので、今回はInstr関数を使いました。, つづくVBAコード33~37行目は、第二引数の値によりアカウント名、もしくはドメイン名を取得するIf文での分岐処理になります(下図)。, 第二引数vFlagが0の場合(VBAコード赤字部)は、VBAコード34行目が実行されます。アカウント名は、@の前の文字列(赤字)ですので、変数aPos(=4) - 1文字分を、メールアドレスの左側から取得すればよいです。つまりここでは図に示す通りLeft関数を使う事で取得できます。, 第二引数vFlagが1の場合 (VBAコード青字部) は、VBAコード36行目が実行されます。ドメイン名は、@の後ろの文字列(青字)ですので、変数aPos(=4) + 1文字目から、(メールアドレス全体の文字数 - @までの文字数)を、Mid関数の第三引数に指定することで取得できます。, main_2は、メールアドレスからアカウント名を取得するプロシージャです。プロシージャ内部では、前述した ユーザ定義関数 GetMAddInfoを呼び出す際に、引数としてメールアドレスと0(アカウント名取得時)を渡しています。, [main_2]7行目: .Cells(r, 1) = GetMAddInfo(.Cells(r, 1), 0)  メールアドレスからアカウント名を取得するための ユーザ定義関数 GetMAddInfoを呼び出している行です。引き数には、.Cells(r, 1)のメールアドレスの他に、フラグとしてアカウント名取得処理をするために0を渡しています。, main_3は、メールアドレスからドメイン名を取得するプロシージャです。main_2同様、プロシージャ内部で ユーザ定義関数 GetMAddInfoを呼びだしています。そして引数としてメールアドレスと1(ドメイン名取得時)を渡しています。, [main_3]18行目: .Cells(r, 1) = GetMAddInfo(.Cells(r, 1), 1) メールアドレスからドメイン名を取得するための ユーザ定義関数 GetMAddInfoを呼び出している行です。引き数には、.Cells(r, 1)のメールアドレスの他に、フラグとしてドメイン名を取得するために1を渡しています。, 本記事でこれまで解説してきた処理内容は、「電話番号のハイフンを削除する」、「メルアドのアカウント名/ドメイン名を抜き出す」というデータ整形処理を、別々のユーザ定義関数で行いました。, 実はこれら2つの関数は、処理対象の文字列を渡して、処理後の文字列を受取るという共通点に着目すると、1つのユーザ定義関数にまとめることができそうです。, そこで、本項では機能が似ている関数を1つにまとた上で、今度は電話番号のハイフン削除と、メールアドレスからアカウント名とドメイン名の取得を一度に処理するサンプルコードをご紹介したいと思います。, ユーザ定義関数は、いくつでも作ることが出来ますが、あまりにも似たような機能の関数をたくさん作ってしまうと管理が面倒になり可読性も悪くなります。, 可能な限り似たような機能・目的を持つユーザ定義関数は、今回ご紹介するサンプルコードのようにまとめることをお勧めします。, なお、本項ではSheet3にある約10000件のデータを処理します。そこで本サンプルコードでは、処理の高速化のために二次元配列を採用しています。, 下にご紹介するVBAサンプルコードは、1つのmainプロシージャと、1つの ユーザ定義関数 GetTelEMailInfo からなります。関数の内容は、前述の通り電話番号からハイフンを削除するのと、メールアドレスからアカウント名とドメイン名を抜き出す処理です。, サンプルコード実行前後のSheet3を図示してみました。図のようにmainプロシージャを実行することにより、「ハイフン無し電話番号」、「アカウント名」、「ドメイン名」の3つの情報が一度に出力されます。, これよりVBAサンプルコードの解説になりますが、これまで本サイトで詳細にお伝えしきた内容については、簡単に解説しております。より詳細な内容については、文中のリンクを参照してください。, ユーザ定義関数 (Functionプロシージャ)やSubプロシージャに渡す引数には、引数の指定を省略できない引数と、引数の指定を省略できる引数があります。, 引数の指定を省略できない引数は、これまで解説してきた引数のことで、呼び出し側のVBAコードでは必ず指定していました。よって、この省略できない引数を、指定し忘れると当然コンパイルエラーになります。, 一方の省略できる引数は、本記事で詳しくお伝えするもう1つの引数の使い方です。この引数は呼び出し側で引数の省略が可能です。省略された場合は、ユーザ定義関数側で指定された初期値が採用されます。, 図は本サンプルコードのユーザ定義関数GetTelMailInfoの第二引数を、省略できる引数にする場合の記載方法を示しています。, 省略可能な引数の記載方法は、通常の引数の書き方に対して、前側に「Optional」を書き、後ろ側には省略された際の値を「=」に続き記述します。図の場合、呼び出し側で第二引数が省略された場合は、第二引数の値が「2」になることを意味しています。, なお、第二引数の後ろ側(本サンプルでは「= 2」)の初期値を省略すると、データ型に応じた初期値が代入されます。例えば、今回の整数型(Integer)では0、文字列型は""(ブランク)が初期値になります。, 本サンプルコードでは、第二引数の値により処理の内容を変えています。下図に第二引数の値と処理内容を表にまとめましたのでご覧ください。, 下の図は、第二引数がメールアドレスに関する処理の場合、メールアドレスの中にある@の前方からの位置情報を取得するためのVBAコードになります。, VBAコード41行目で、第二引数が0(アカウント名取得)か、1(ドメイン名取得)が指定された際に、変数aPosに@マークの前方からの位置を格納します。, VBAコード46~62行目は、第二引数の値とIf文を使う事で実際の処理を分けています。, 受取った第二引数の値に応じて処理が3つに分岐されています。処理内容については、これまで解説してきた内容と同じですので割愛しますが、図中のVBAコード56行目のIf文の途中にある"_"(アンダーバー)について補足します。, VBAコード途中の"_"(アンダーバー)は、VBE上で一行が長くなりすぎたVBAコードを見やすくするために使う改行文字です。見た目だけの効果ですので、VBAの実行結果には全く影響しません。, VBAコード11~13行目では、出力先の3列目(C列)から5列目(E列)を消去しています。, VBAコード16~20行目では、 CurrentRegionやResize を使いながら、すべてのデータ範囲を二次元配列に格納しています。これにより、集計を 二次元配列 で行う準備が出来たことになります。, VBAコード23~27行目については、下図に抜き出してみました。先ほど解説しました共通化されたユーザ定義関数GetTelMailInfoを使い、3つの処理(ハイフン無し電話番号、アカウント名、ドメイン名のそれぞれを取得)をしています。, 3つの異なる処理を同じ関数で実行できるのは、関数に渡す第二引数を変えているからです。, このように、機能・目的などが似たようなユーザ定義関数は、今回の例のようにまとめると管理しやすくなり、可読性もよくなりますのでお勧めします。, 本記事では、電話番号からハイフンを削除する処理と、メールアドレスからアカウント名、およびドメイン名を取得するVBAサンプルコードについて詳しく解説してきました。, さらに、記事の後半ではこれら3つの処理を、1つのユーザ定義関数にまとめる方法について、オプション付引数の使い方も含めて解説してきました。, 集計処理内容を、適宜サブルーチン化することでVBAコードの保守性・可読性を向上させることができますが、似たような機能・目的のサブルーチンが複数あれば、引数を適宜オプション化して1つのサブルーチンにまとめることにより、さらに保守性・可読性の向上効果が期待できます。. 風間トオル 母親 クレヨンしんちゃん 5, ソウル ハッカーズ 邪神 17, 福岡 ソフトボール 高校 強豪 41, ポケモン Bw2 キャラ 4, オタク まとめ 2ch 54, Jungle Cm ハート 11, 重岡 大 毅 感動 4, 大学名 モテ る 20, エヴァンゲリオン 序 考察 35, パーティー 類語 英語 10, アスノヨゾラ哨戒班 歌ってみた 男 5, ポケモン 英語 名 読み方 8, 歌い手 夢小説 ま ふま ふ 7, ガキの使い 動画 タピオカ 14, 村田 引退試合 なんj 7, カバ 走る 速さ 21, 辛い時 名言 仕事 11, 有吉 ゴリ ラジオ 10, Pubg 成長ミッション 21 15, 茅野市 保育士 異動 8, コウモリ 赤ちゃん 家の中 10, 風の谷のナウシカ 英語 字幕 8, 髪が 綺麗 英語 6, 花の慶次 利沙 おまつ 4, みんなの嘘 韓国ドラマ ネタバレ 33, フィッシャーズ シルク 大学 4, クレヨンしんちゃん 嵐を呼ぶ 栄光のヤキニクロード Dvdラベル 11, " />

vba 電話番号 ハイフン 分ける 5

また、表示形式で"00000000"←このように0を8ついれるのでは 7/2 すいか 300円 抽出するだけならオートフィルターでもできますが、 「編集」>「コピー」 =RIGHT("00000000"&A1,8) ブログ作成で使える無料キャプチャーツールならScreenpresso | 注釈や囲いの編集設定が簡単! ユーザー定義 最初の市外局番は全部同じ番号です。 =IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"") まず、下記のURLにアクセスしてください。  If VarType(Range("A1").Value) = vbDouble Then 電話番号をハイフン区切りで統一して変換する方法をご紹介します。エクセル関数をうまく組み合わせる事で、一度に大量の番号を変換することができるようになります。 教えてください!, Worksheets("シートA").Range("A1") 本記事で使用するサンプルファイルは、3枚のシートから構成されています。3枚のシート名は、それぞれ「電話番号」、「メールアドレス」、「リスト」です。 「電話番号」シートには、ハイフン付きのダミーの電話番号が10件あります。「メールアドレス」には、メールアドレスのダミーデータが10件あります。 最後の「リスト」シートには、約10000件の電話番号とメールアドレスが記載されており、その右側には出力列があり、それら項目名として「電話番号(ハイフン無し)」、「メールアドレス( … >VBAは、オリジナルのファイル名を使っているため、ファイル名を変更するとエラーになり    End If    Tel1 = .Cells(i, C1) # 本質的には「とる」のではなく「置換する」ことに注意。  Const C1 As String = "A" '元の電話番号が入っている列名 ブラウザでこのページを開いた状態で、 Dim readSheet As WorkSheet ' 相手シート 「ユーザー定義」なるものを使うというのはなんとなく判るのですが・・・。 そして、エクセルのSheet2(と仮にします)のA1セルを選択し、「編集」>「貼り付け」でブラウザの内容を貼り付けます。  Application.ScreenUpdating = False ="'"&SUBSTITUTE(セル,"-","") Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照 Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照 Alt+F11でVBAの画面を開き、左側のツリーからブック名を選択し、右クリックから「挿入」>「標準モジュール」を選択して、右の画面に以下のマクロをコピーして貼り付けてください。 1F  ← 文字 文字が入っていなければB1セルからC1セルを引く、という状態です。 [スポンサーリンク] (adsbygoogle = window.adsbygoogle || []).push({}); 集計業務へVBAを使う初歩的な使い方で、かつ意外と便利な方法をお伝えします。ちょっとしたデータ整形でも、対象とする個々のデータに応じて、実に様々なコードを扱う必要があります。そこで、便利な使い方として ... VBAコードの可読性、高速化、コーディング効率を向上させるWithステートメントの使い方を分かりやすいサンプルコードで解説します。 VBAコードの可読性を上げる方法の一つとして、適切なコメント文の使用 ... (adsbygoogle = window.adsbygoogle || []).push({}); 複数のワークシートをVBAで扱う1つの方法として、ワークシート名を使って複数のワークシートを管理する方法があります。集計業務では、複数のワークシートを取り扱うことがほとんどですよね。 わたしは、取りま ... VBA変数名のつけ方を、わたしが長年使っているハンガリアン記法を中心に解説します。変数名のつけ方のコツは、その変数名からその変数が何のために定義(宣言)されたのかをイメージできるような名前にすることだ ... VBAで集計業務をする際に、最低限覚えておきたいデータ型について解説します。「変数の宣言を強制する」の内容にも関係が深い内容です。 VBAのデータ型は、実は10種類以上あります。ですが、ここでは簡単に ... 文字列の右側から一定の長さを取得するRight関数を使用して、セルの中の文字列から必要な情報を取り出します。右側2文字が共通する文字であることを利用した行削除のサンプルコードです。 では今回のサンプル ... 集計対象レコードに対して、各項目別に複数条件でデータを削除したい場合に使えるVBAサンプルコードをご紹介します。基幹システムなどの生データに対し、毎回決まったデータを削除して目的のデータを整形する場合 ... 集計対象レコードのうち、特定の値を含むレコードを削除するVBAサンプルコードをご紹介します。データクレンジングなどで、集計対象外のデータを含むレコードをあらかじめ削除しておきたい場合などにも利用できま ... Excelシート上のデータを、列方向から削除するサンプルコードです。 経験上、VBAコードによる行削除に比べて、使う頻度としてはかなり少ないのですが、行削除同様、基礎的なVBAコードです。 また、行削 ... VBAでワークシートの行間に、新たに行を挿入する方法を解説します。IF文と組み合わせれば、対象レコードをグループごとに集計する小計行を作る時にも使えるスキルです。 データを確認する時に、レコードとレコ ... Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved. エクセルではセルをプルダウン(ドロップダウン)リスト形式で表示する事が可能です。リストをうまく使う事で入力や集計が楽になります。... エクセルの超便利関数であるVLOOKUPについて、データはあるけれどうまくいかない場合の対応方法をまとめました。ヒットしないような場合はセル... Chromeで知らず知らずのうちにサイトから通知が届くようになってしまった場合のブロック方法についてまとめました。不要な通知は解除しておくと... ミニバンへの買い替え検討時に行った事をまとめました。試乗した感想を元に、フリード・セレナ・ボクシーの中から1つを選び、購入しました。... CDNでVue.jsとElement UIを使用した際に、デフォルトでは中国語表記となります。これを日本語化する方法をご紹介します。... Google Meetに参加できず、「あなたはこのビデオハングアウトに参加できません」というメッセージが表示された場合の確認ポイントをまとめました... キーボード入力ができない!文字が打てなくなった!という場合の原因の1つである「固定キー機能」について、無効化する方法をご紹介します。... 個人の実印・銀行印・認印や法人の代表印・角印・銀行員・資格丸印について、画像ファイルとして作成する方法をご紹介します。... 新型コロナウィルスの感染拡大に伴い、テレワーク用の机を購入してみましたので、どんな感じかレビューをしてみました。... Windows環境でSafariを動かすFirefoxアドオン|User-Agent Switcher and Manager.    03-2222-2222  I = 2 集計業務で電話番号から"-"(ハイフン)を削除したり、メールアドレスからアカウント名やドメイン名を抜き出すデータの整形業務があります。本記事では、これらの処理をVBAで行うサンプルコードを詳しくご紹介します。, また記事の後半では、以下の処理を1つのユーザ定義関数にまとめた場合のサンプルコードについてもご紹介しております。, まずは本記事で解説する内容を、動画でサッと確認するための動画(約2分)を用意してありますので、宜しければご覧ください。, 本記事で使用するサンプルファイルは、3枚のシートから構成されています。3枚のシート名は、それぞれ「電話番号」、「メールアドレス」、「リスト」です。, 「電話番号」シートには、ハイフン付きのダミーの電話番号が10件あります。「メールアドレス」には、メールアドレスのダミーデータが10件あります。, 最後の「リスト」シートには、約10000件の電話番号とメールアドレスが記載されており、その右側には出力列があり、それら項目名として「電話番号(ハイフン無し)」、「メールアドレス(アカウント名)」、「メールアドレス(ドメイン名)」が記載されています。, 本記事では、前述した3つのワークシートを扱いますが、これらのシートは本記事でご紹介するVBAサンプルコード内では、すべてのワークシートをオブジェクト名で指定しています。, ダウンロードしたファイルのVBE画面上でもご確認頂けますが、下図の通りワークシート名とオブジェクト名が対応しておりますのでご確認ください。, 下図はサンプルファイル「電話番号」シート上のハイフン付電話番号のハイフンを削除する、VBAサンプルコードの実行前後の様子です。, [main_1]5-7行目: For r = 1 To .Range("A" & .Rows.Count).End(xlUp).Row ~ Next 「電話番号」シート(Sheet1)にあるすべての電話番号に対して、繰り返し処理をします。ここでは、Toのあとに集計対象レコードの最終行番号を求めるVBAコードを直接記載しています。, [main_1]6行目: .Cells(r, 1) = DelHyphen(.Cells(r, 1)) セル.Cells(r, 1)は、電話番号になります。まずこの後にお話しするユーザ定義関数DelHyphenに渡す引数としてハイフン付電話番号(.Cells(r,1))を渡しています。そして関数DelHyphenの戻り値であるハイフンを削除した電話番号を、.Cells(r, 1)に再代入しています。, [DelHyphen]13行目: Public Function DelHyphen(ByVal vString As String) As String ユーザ定義関数DelHyphenのプロシージャ宣言文です。vStringは、呼び出し元から渡される引数になります。また戻り値は、ハイフンを削除した電話番号ですので文字列型を指定しています。, [DelHyphen]18~22行目: For i = 1 To Len(vString) ~ Next引数で受け取った文字列を、1文字ずつ文字列の長さ分繰り返し処理します。この間の処理は、Mid関数とLen関数を使って、対象の文字列から1文字ずつ取り出した文字が、ハイフンと等しいかどうか (VBAコードでは、半角と全角の両方をチェックしています) で処理を分けています。, 具体的には、ハイフンを除く文字の場合のみ、戻り値となる変数rStringへの文字列結合処理を行います(VBAコード20行目)。これにより結果的に、電話番号からハイフンを削除したことになります。, メールアドレスからアカウント名(@より前の文字列)、およびドメイン名(@から後ろの文字列)を取得するVBAサンプルコードをご紹介します。, 次のVBAサンプルコードは、main_2がアカウント名を取得し、main_3がドメイン名を取得するプロシージャになります。, これら2つのプロシージャは、ともに ユーザ定義関数 GetMAddInfo を呼んでいますが、第二引数の指定が違います。つまりこの第二引数の指定の違いにより、それぞれアカウント名とドメイン名という異なる情報を取得できるようにしています。, では紹介したVBAサンプルコードの2つのプロシージャを実行した結果をそれぞれご紹介したいと思いますが、実行する前に1点だけ注意があります。, VBAサンプルコードは、アカウント名取得用(main_2プロシージャ)とドメイン名取得用(main_3プロシージャ)のプロシージャが、それぞれ同じ対象レコード(メールアドレスシートのA列)に対して処理をします。, よって、main_2を実行した後のアカウント名が出力されたままで、main_3を実行しても既にドメイン名以下が削除されていますので、実行結果に変化が起こりません(アカウント名取得状態のまま)。, そこで、main_2の実行前に対象レコードを隣の列などにコピペして、元のメールアドレスデータを転記しておいてください。そうすれば、main_3実行時にその隣にコピペした元のメールアドレスデータを再利用できます。, メールアドレスの@から前の部分をアカウント名、またはユーザ名と呼んだりします。実際の集計業務で経験したことですが、社員のユーザIDとして社内メールアドレスのアカウント名を利用していたところもありました。, そこでは、実際に社員のメールアドレスから集計のキーとなるユーザID(アカウント名)を抜き出した上で、集計資料を作成したこともあります。まさに本記事でご紹介しているアカウント名を抜き出すサンプルコードを利用していたのです。, 上図はサンプルコードのmain_2プロシージャを実行した結果になります。@より前の情報が取得されていますよね。, ドメイン名は、@より後ろの文字列を指します。main_3の実行結果は、以下のように@マークより後ろの文字列が抜き出されています。, アカウント名を取得するmain_2プロシージャとドメイン名を取得するmain_3プロシージャ、および共通の ユーザ定義関数 GetMAddInfoについて解説します。, なお前項でご紹介したVBAサンプルコードですが、記載の順番はmain_2、main_3、GetMAddInfoの順番ですが、ここでの解説はGetMAddInfoをはじめに解説し、その後main_2、main_3の順に解説します。, [GetMAddInfo]25行目: Public Function GetMAddInfo(ByVal vString As String, ByVal vFlag As Integer) As String ユーザ定義関数の宣言文になります。第一引数はメールアドレス(文字列)です。第二引数には、アカウント名を取得するのか、ドメイン名を取得するかを判断するためのフラグ(整数値)を設けています。, [GetMAddInfo] 27行目: Dim aPos As Integer  30行目: aPos = InStr(vString, "@") メールアドレスからアカウント名とドメイン名を分けて取得するには、メールアドレスの中に含まれる@の位置情報を知る必要があります。, そこで、上記のようにメールアドレスの中から@の前方の位置情報をInstr関数で取得し、変数aPosに代入しています。, この位置情報を得る方法については、電話番号からハイフンを取得した方法と同様に、Mid関数とLen関数を用いても位置情報を取得できますが、メールアドレスの中の@は1つしかないという特性がありますので、今回はInstr関数を使いました。, つづくVBAコード33~37行目は、第二引数の値によりアカウント名、もしくはドメイン名を取得するIf文での分岐処理になります(下図)。, 第二引数vFlagが0の場合(VBAコード赤字部)は、VBAコード34行目が実行されます。アカウント名は、@の前の文字列(赤字)ですので、変数aPos(=4) - 1文字分を、メールアドレスの左側から取得すればよいです。つまりここでは図に示す通りLeft関数を使う事で取得できます。, 第二引数vFlagが1の場合 (VBAコード青字部) は、VBAコード36行目が実行されます。ドメイン名は、@の後ろの文字列(青字)ですので、変数aPos(=4) + 1文字目から、(メールアドレス全体の文字数 - @までの文字数)を、Mid関数の第三引数に指定することで取得できます。, main_2は、メールアドレスからアカウント名を取得するプロシージャです。プロシージャ内部では、前述した ユーザ定義関数 GetMAddInfoを呼び出す際に、引数としてメールアドレスと0(アカウント名取得時)を渡しています。, [main_2]7行目: .Cells(r, 1) = GetMAddInfo(.Cells(r, 1), 0)  メールアドレスからアカウント名を取得するための ユーザ定義関数 GetMAddInfoを呼び出している行です。引き数には、.Cells(r, 1)のメールアドレスの他に、フラグとしてアカウント名取得処理をするために0を渡しています。, main_3は、メールアドレスからドメイン名を取得するプロシージャです。main_2同様、プロシージャ内部で ユーザ定義関数 GetMAddInfoを呼びだしています。そして引数としてメールアドレスと1(ドメイン名取得時)を渡しています。, [main_3]18行目: .Cells(r, 1) = GetMAddInfo(.Cells(r, 1), 1) メールアドレスからドメイン名を取得するための ユーザ定義関数 GetMAddInfoを呼び出している行です。引き数には、.Cells(r, 1)のメールアドレスの他に、フラグとしてドメイン名を取得するために1を渡しています。, 本記事でこれまで解説してきた処理内容は、「電話番号のハイフンを削除する」、「メルアドのアカウント名/ドメイン名を抜き出す」というデータ整形処理を、別々のユーザ定義関数で行いました。, 実はこれら2つの関数は、処理対象の文字列を渡して、処理後の文字列を受取るという共通点に着目すると、1つのユーザ定義関数にまとめることができそうです。, そこで、本項では機能が似ている関数を1つにまとた上で、今度は電話番号のハイフン削除と、メールアドレスからアカウント名とドメイン名の取得を一度に処理するサンプルコードをご紹介したいと思います。, ユーザ定義関数は、いくつでも作ることが出来ますが、あまりにも似たような機能の関数をたくさん作ってしまうと管理が面倒になり可読性も悪くなります。, 可能な限り似たような機能・目的を持つユーザ定義関数は、今回ご紹介するサンプルコードのようにまとめることをお勧めします。, なお、本項ではSheet3にある約10000件のデータを処理します。そこで本サンプルコードでは、処理の高速化のために二次元配列を採用しています。, 下にご紹介するVBAサンプルコードは、1つのmainプロシージャと、1つの ユーザ定義関数 GetTelEMailInfo からなります。関数の内容は、前述の通り電話番号からハイフンを削除するのと、メールアドレスからアカウント名とドメイン名を抜き出す処理です。, サンプルコード実行前後のSheet3を図示してみました。図のようにmainプロシージャを実行することにより、「ハイフン無し電話番号」、「アカウント名」、「ドメイン名」の3つの情報が一度に出力されます。, これよりVBAサンプルコードの解説になりますが、これまで本サイトで詳細にお伝えしきた内容については、簡単に解説しております。より詳細な内容については、文中のリンクを参照してください。, ユーザ定義関数 (Functionプロシージャ)やSubプロシージャに渡す引数には、引数の指定を省略できない引数と、引数の指定を省略できる引数があります。, 引数の指定を省略できない引数は、これまで解説してきた引数のことで、呼び出し側のVBAコードでは必ず指定していました。よって、この省略できない引数を、指定し忘れると当然コンパイルエラーになります。, 一方の省略できる引数は、本記事で詳しくお伝えするもう1つの引数の使い方です。この引数は呼び出し側で引数の省略が可能です。省略された場合は、ユーザ定義関数側で指定された初期値が採用されます。, 図は本サンプルコードのユーザ定義関数GetTelMailInfoの第二引数を、省略できる引数にする場合の記載方法を示しています。, 省略可能な引数の記載方法は、通常の引数の書き方に対して、前側に「Optional」を書き、後ろ側には省略された際の値を「=」に続き記述します。図の場合、呼び出し側で第二引数が省略された場合は、第二引数の値が「2」になることを意味しています。, なお、第二引数の後ろ側(本サンプルでは「= 2」)の初期値を省略すると、データ型に応じた初期値が代入されます。例えば、今回の整数型(Integer)では0、文字列型は""(ブランク)が初期値になります。, 本サンプルコードでは、第二引数の値により処理の内容を変えています。下図に第二引数の値と処理内容を表にまとめましたのでご覧ください。, 下の図は、第二引数がメールアドレスに関する処理の場合、メールアドレスの中にある@の前方からの位置情報を取得するためのVBAコードになります。, VBAコード41行目で、第二引数が0(アカウント名取得)か、1(ドメイン名取得)が指定された際に、変数aPosに@マークの前方からの位置を格納します。, VBAコード46~62行目は、第二引数の値とIf文を使う事で実際の処理を分けています。, 受取った第二引数の値に応じて処理が3つに分岐されています。処理内容については、これまで解説してきた内容と同じですので割愛しますが、図中のVBAコード56行目のIf文の途中にある"_"(アンダーバー)について補足します。, VBAコード途中の"_"(アンダーバー)は、VBE上で一行が長くなりすぎたVBAコードを見やすくするために使う改行文字です。見た目だけの効果ですので、VBAの実行結果には全く影響しません。, VBAコード11~13行目では、出力先の3列目(C列)から5列目(E列)を消去しています。, VBAコード16~20行目では、 CurrentRegionやResize を使いながら、すべてのデータ範囲を二次元配列に格納しています。これにより、集計を 二次元配列 で行う準備が出来たことになります。, VBAコード23~27行目については、下図に抜き出してみました。先ほど解説しました共通化されたユーザ定義関数GetTelMailInfoを使い、3つの処理(ハイフン無し電話番号、アカウント名、ドメイン名のそれぞれを取得)をしています。, 3つの異なる処理を同じ関数で実行できるのは、関数に渡す第二引数を変えているからです。, このように、機能・目的などが似たようなユーザ定義関数は、今回の例のようにまとめると管理しやすくなり、可読性もよくなりますのでお勧めします。, 本記事では、電話番号からハイフンを削除する処理と、メールアドレスからアカウント名、およびドメイン名を取得するVBAサンプルコードについて詳しく解説してきました。, さらに、記事の後半ではこれら3つの処理を、1つのユーザ定義関数にまとめる方法について、オプション付引数の使い方も含めて解説してきました。, 集計処理内容を、適宜サブルーチン化することでVBAコードの保守性・可読性を向上させることができますが、似たような機能・目的のサブルーチンが複数あれば、引数を適宜オプション化して1つのサブルーチンにまとめることにより、さらに保守性・可読性の向上効果が期待できます。.

風間トオル 母親 クレヨンしんちゃん 5, ソウル ハッカーズ 邪神 17, 福岡 ソフトボール 高校 強豪 41, ポケモン Bw2 キャラ 4, オタク まとめ 2ch 54, Jungle Cm ハート 11, 重岡 大 毅 感動 4, 大学名 モテ る 20, エヴァンゲリオン 序 考察 35, パーティー 類語 英語 10, アスノヨゾラ哨戒班 歌ってみた 男 5, ポケモン 英語 名 読み方 8, 歌い手 夢小説 ま ふま ふ 7, ガキの使い 動画 タピオカ 14, 村田 引退試合 なんj 7, カバ 走る 速さ 21, 辛い時 名言 仕事 11, 有吉 ゴリ ラジオ 10, Pubg 成長ミッション 21 15, 茅野市 保育士 異動 8, コウモリ 赤ちゃん 家の中 10, 風の谷のナウシカ 英語 字幕 8, 髪が 綺麗 英語 6, 花の慶次 利沙 おまつ 4, みんなの嘘 韓国ドラマ ネタバレ 33, フィッシャーズ シルク 大学 4, クレヨンしんちゃん 嵐を呼ぶ 栄光のヤキニクロード Dvdラベル 11,

  • لا توجد منتجات في السلة