tanakadaigoro – ページ 4

UNIQUE関数について

Office2021とWeb版で利用可能なUNIQUE関数は重複除外リストが簡単に取得出来るやつです

Office2021（Office365）・Web版で利用可能な関数にUNIQUE関数というものがあります
この関数は指定した配列データの中で重複したデータを削除したデータを返す関数です
この関数を利用することで、表のデータから簡単に重複しないデータを取得できます

この重複しないデータの作成は、案外利用場面が多くあったのですが、標準機能ではデータタブ内にある重複データの削除という機能から行うことが出来ます
後述しますが、まだこちらの機能に完全に有利になってはいないので、この機能を知らない人はデータタブ内を確認してみてください

ただ、こちらは関数のためいちいちデータを転記して操作を行わなくても関数の再計算のタイミングで自動的に取得できるのは便利です（常に最新情報）

関数の書式

引数（太字は必須引数）
(配列, [列の比較], [回数指定])

「配列」は重複しないデータを作成する配列データです
連続していれば複数列を範囲指定することが出来ます

「[列の比較]」は、重複参照時に列方向を対象にするかどうかの指定です
列を対象にすると、列方向に重複しない場合はその列が取得されません
あまりExcelのデータ形式からすると使用しないかもしれません
省略時は行方向が指定されることになります

「[回数指定]」は、重複データの中でも1度しか出てこないデータだけを指定できます
1度しか出てこないデータは、配列データ内で完全に一意になっているデータという事になります
案外欲しい、この逆説のデータ取得は出来ません

実際の使用例

今回はこのデータを元に関数を使用してみます

UNIQUE(Sheet1!A2:A21)

基本的な使い方として、指定データ範囲を配列データとして指定します
今回はA列のデータ範囲を指定しています

この中から重複したデータだけを削除したものが取得されています
データの総数は20個ですが、重複データが15個あるのがこれで分かりますね

=UNIQUE(Sheet1!A2:A21,,TRUE)

次に完全な一意のデータのみを取得してみます
3つ目の引数に「TRUE」を指定します
指定配列内の中で、1度しか出てこなかったデータが取得されます

「kkk001」と「kkk003」が複数回出てきていたことが、前回の結果と比べると分かりますね
ここが前述の逆説のデータをとれないという点ですが、この複数回出てきているデータだけを取得する設定が無いんですね、なんでやろね

=UNIQUE(Sheet1!A2:B21)

次に複数条件での重複除外データの取得を行ってみます
A列とB列の両方を配列データに指定することで、複合的な条件でリスト作成を行うことが出来ます

この場合ですと、A列とB列の両方が重複するものが除外される形になります

ただ少し残念なのが、連続した範囲でしかセル指定が行えない点です
A列とC列を参照したい場合に、そのままでは指定が行えないのですね
なので、C列のデータをB列に持ってくるような工夫が必要になります
ここが最初の方で記載した、既存の重複データの削除機能に優位になっていない点です
重複データの削除機能は飛び飛び列で条件指定を行うことが出来ます

この様に単独列の重複しないデータのリストが簡単に作成できる関数であることが分かってもらえると思います

XLOOKUP関数について

Office2021とWeb版で利用可能なXLOOKUP関数はLOOKUP関数系の最終進化系です

XLOOKUP関数を利用していますでしょうか？
この関数はOffice2021（Office365）・Web版で利用可能な関数です

この関数は、ある程度関数を知っている人にはVLOOKUP関数等のLOOKUP関数系であることはイメージできると思います
VLOOKUPでもHLOOKUPでも無く、XLOOKUPが存在します

この関数は引数の配列内から検索して、その配列と同じ順番にあるデータを返す関数です
ただこれだけだと、あまり既存の関数と差があるとは思わないかもしれません

決定的に違うのは、前述の検索対象が「配列」であることです

これは意外に今までの関数ではあまり無い考え方になります
というのも、VLOOKUP関数等は引数に指定するデータは「セル範囲」であることが前提です
このデータの取得の行い方で全く動作と利便性が変化します

今までのLOOKUP関数は、セル範囲の中で検索を行う、見つかったセルと同じ行・列の指定した行数・列数を移動させたセルのデータを取得します
そのため、制限として「検索個所と取得個所が連続したセル範囲であること」「取得セルは検索セルより、左の列・上の行は指定できない」という大きな点がありました

そのため、LOOKUP関数を利用するために、本来別のシートやブックにあるデータを1つのシートにコピペしてデータを結合して検索可能な表を作成する必要がありました
これはセルという概念が前提であることが理由でした

しかし、XLOOKUP関数はセルではなく配列データから検索・取得を行うため
上記の制限が全くありません

つまり、「検索するデータ範囲と取得するデータ範囲が連続していなくていい」「取得するデータが検索データよりセルの位置が上でも左でもいい」ということになります
特に大きい利便性向上につながるのが、1点目の連続していない範囲でよくなった点です

要するに、同じシートである必要もなくなり、なんならブックを超えた検証を行うことも可能となりました
そのため、検証作業が非常に簡単になります

さらに地味にありがたいのが、見つからない場合の返し値を指定できる点です
LOOKUP関数系では見つからない場合エラーとなってしまうため、そのデータを参照した関数は連鎖的に全てエラー表示となってしまいます
そのため、LOOKUP関数をIFERROR関数でネストさせて見つからない場合は特定の値を返すように関数を組んでいることが多々あります

これが必要なくなりますので、作り込みによってはすさまじくネスト数を減らすことも可能となります

関数の書式

引数（太字は必須引数）
(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])

「検索値」は検索対象のデータです
これはLOOKUP関数系と差は無いと思いますので、特に解説不要と思います

「検索範囲」は検索対象の配列データです
配列データですが、基本的にExcel上ではセル範囲で指定します

「戻り範囲」は取得対象の配列データです
検索対象で見つかった配列のデータ番号と同じ順番のデータが返されます
基本的に列を指定している場合は、同じ行数のデータになります

「[見つからない場合]」は検索配列に検索対象が見つからなかった場合に返す値の設定です
数値の0を指定したり「””」（空白）を設定したり、なんでも指定できます

「[一致モード]」は検証方法です、基本的には完全一致が条件です
ですが、なんとここでワイルドカードを指定することができる様に設定できます
ワイルドカードまで対応しているなんて、なんてことをしてくれるんでしょうか
ワイルドカードが分からない人にはピンと来ないかもしれませんが、本記事では割愛します
ワイルドカードが利用可能、という点が認知できていれば十分です

「[検索モード]」は検索方向の設定です
末尾から検索したいとき程度に利用しますが、あまり頻度は高くないと思います

実際の使用例

こちらのテーブルデータをもとに検証を行う場面を想定してみます
実務となると検証対象が数百件以上となることもあると思います
今回はとりあえず20件で検索を行います

このデータの中で存在するデータと、そうではないデータを検証します

=XLOOKUP(B2,Sheet1!A:A,B:B,"×")

上記のような関数を作成しました
この関数では、関数のセルの左にある入力値をSheet1のA列データから検索して、見つかったデータと同じ行数のSheet2のB列から取得を行います
見つからない場合は「×」を返します

そのため、取得が行えているデータの場合は条件セルと関数セルが同じ内容が表示されます
上記の画像でも同じものが2列に並んでいることが確認できます

このまま条件の行数分に関数をオートフィルで作成を行います
この画像の入力値は前提としてマスターデータと考えてください
登録すべきデータ自体は過不足なくここのリストにあります

最初に見せたデータテーブルに、このマスターデータの内容がちゃんと存在しているかを確認するための検証作業です
この画像の様にマスターデータを元に検証を行うことで、データの検証を行えます

実際画像を確認して分かるように、「kkk012」が入力テーブルには存在していないことが分かります
これでこのデータが入力漏れ等のミスが発生していることを発見することが出来ます

XLOOKUP関数は、別のシート、別のブックも可能なのが強みだと思います
また配列データなので、セルの行数が同じでなくてもセルの個数さえ一致していれば検証と取得を行えますので、非常に柔軟にセルデータに対して対応を行えます

VBAで検証処理を作成するのも良いのですが、やはり関数が利用しやすい場合は利用するべきですね
今後、この関数がExcelを席捲していくこと間違い無しです

PowerAutomateの困った現象

PowerAutomateで作業をしていて、非常に困った現象のあれこれ

PowerAutomateでフローを作成している際に、実際に遭遇して困った現象を解説しておきます（1つ1つは小さいことで、1つの記事にするほどの内容ではないようなこと）
同じく困ってしまうことがあると思いますので注意のため、なるべく早い段階で読んでおいてほしいです
また、現象に遭遇するたびに追記していこうと思います

変数の自己参照が出来ない

変数の利用が出来ますが、その変数は自己参照が出来ません

VBAでよくあるのが、「i = i + 1」というような加算を行うような変数の更新です
これは変数「i」に代入する際に「i」を参照しています
これが自己参照です

PowerAutomateではこれが使用できません
変数の設定アクションでは、別の変数を使用する必要があります

実際上記の加算に関しては、専用のアクションがありますが
やはりVBAになれているものからすると、この自己参照が出来ないのは結構不便に感じます

もっとも単純な解決法は、2つの変数を作成することです
VBA的に記載しますと、「i = i + 1」が利用できないので
「i = i_2 + 1」と記載して加算を行う
「i_2 = i」と記載して計算結果を2つ目にも共有する
このように2つの変数を介することで、疑似的に自己参照の動きを作成できます

考えるだけで面倒ですね、なんとか自己参照できるようになってほしいものです

エラー発生時の挙動

エラー発生時は非常に面倒です

特に保存を許してくれないのが、1番のストレスです
エラーをとりあえず解決するまで保存を実行しようとしてもフローチェッカーが保存を阻んできます
これが案外面倒で、終了アクションを代入してそこでフローが終わるようにしてテストを行いたくても保存が出来ないからテスト出来ない

とりあえず全てのエラー個所にダミー値を入力していく
これが数が多いと面倒だし、何より修正前のデータを残しておきたいこともある

さらに最悪なのが、それなりに作業を進めてから誤って変数などを削除してしまってエラーになった場合です
PowerAutomateには操作を戻す機能が無いため、元に戻せません
なので新たに変数やアクションを作り直して再度設定を全て行う必要があります

これが保存後最初の操作ならまだしも、前述したようにそれなりの作業後にこの状況に陥ると前回保存時からの作業が全て吹き飛ぶため絶望的です
これで何度「やってもた・・・」と言ったか記憶に無いです、保存できてないので・・・

もう1つ面倒なのが、アクションの名前を付けづらくなることです

エラーが発生した状態では一定時間ごとにチェックが行われているためか、アクションの名前の変更を行っている際に1秒程度で強制完了させられます
瞬時に編集して確定させれば編集は可能なのですが、考えながら入力するのが当然なので漢字変換してるあいだに強制確定されて、入力内容が無くなってしまう

エラーチェッカーさんは、保存を阻むのだけはやめてくれないでしょうかねぇ

アクションが削除出来ないことがある

たまにアクションが削除できなくなることがあります
アプリのネットワーク状況によるものなのかもしれませんが、アクションの詳細メニューから削除を選択しても、うんともすんとも言わず、削除が行われない事があります

この場合はブラウザを再更新すれば、また削除を行えるようになります

これがまた上記のエラー発生時に出た時は非常にやっかいです
なんせ保存できないので、再更新かけられんですから

アクション名の後変更の要注意

アクション名は見出しにもなっているので、わかりやすい文字列にする方がフローを読みやすくなります

ただこれで注意してほしいのが、アクションを作成して後続処理を作成した後に名前を変更する場合です

と、いうのもPowerAutomateの場合に関数の引数にアクションを指定した場合、そのアクション名が文字列として指定されています
ここが後で変更した場合に自動的に変わってくれないのです

そのため、アクション名を変更することで後続の関数が動作しなくなることがあります
動的なコンテンツの追加では構わんのですが、PowerAppsへの引数名にも同じ現象が発生するので、アクション名は後で変更することは基本避けた方が良いです

というか、そもそもアクション名は変更しなくても良いと思っています
正直ここに詳細な内容を書くことが出来ないので、ここにその目的は持たせず、コメントを入力することが出来るのでフローの動作の説明はそちらで記載するようにして、アクション名は初期のまま変更しなくていいんちゃう、と今は感じております

当然動的なコンテンツの追加から選択する場合はアクション名をしっかり付けている方が分かりやすいです
でもアクション名に意味を持たせると、どうしても後で仕様変更だったり修正だったりで微妙につけた名称と違ってくることがあり、その場合には変更するのが非常に面倒ですよね、そもそも動いていたフローを必要以上にいじる必要もあるし
かといって全然ちゃう意味の名前になってたら、なんでこんな名前なんや？と無駄な思考の時間が発生します

なら最初っからアクション名には意味を持たせない、これでええんです
もちろんコメントはどこにも影響与えないので、後で変更はいくらでも可能です

動的なコンテンツの追加が変なとこに入る

動的なコンテンツの追加を行った際に、入力カーソルのある位置に入らないことがあります
というか、連続代入の時はほぼ入りません

いちいち入力カーソルをマウスクリックで指定してから代入を行う必要があります

ついでに言うと、shiftキーを押しながら矢印キーを押していって、入力カーソルを移動させてコピペ範囲を取得しようとしても範囲が広がらないことが多々あります
この場合は、矢印キーを使用せず、shiftキーを押しながらマウスクリックで指定することが出来ます

@が自動的に増え続ける

これはもはや怪奇現象ですが、条件分岐の条件設定に「@」を入力して、別の個所を選択したりしなかったりと発現する理由はよくわかりませんが、どんどん増えていきます

1文字だけ増えたり一気に数文字増えたり、最初見た時はおののきましたね

@自体がPowerAutomateの内部処理では特別な意味を持つ文字であるため、なんらかの補完処理なのかが暴走しているようです

対処としてはこれを文字列として指定すればいいので「”@”」というように文字列指定をして下さい
ってか関数では文字列指定は「’」シングルなんですが条件分岐での入力時はダブルでしないとダメみたいです、もう何がなんやらですね
ちなみに関数のString関数を使用しても良いです、もちろんその場合は「’@’」という引数設定になります

PowerAutomateの詳細画面

フローの詳細設定画面の説明、共有についての説明も行います

PowerAutomateの詳細画面では、フローの名称含め設定関係を行うことが出来ます

詳細画面は初期の作成時には経由しませんので、表示されません
一度保存してから開く必要があります

まずはマイフローを選択して、保存されたフローを表示してください
基本的にはクラウドフローを表示します
ここに表示されたフローから、名称の部分をクリックすると詳細画面が表示されます

ちなみに、この画面から直接編集画面に行く場合は、右側に表示されているえんぴつマークをクリックすると遷移できます

こちらがフローの詳細画面となります

ここでは基本的によく利用するのが、編集画面への移行と共有になります

まず、上のメニューから解説します
「編集」はフローの編集画面に移動します
「共有」はこのフローを別のアカウントに共有することができます
「名前を付けて保存」はこのフローの複製を名前を付けて行うことができます
「削除」「実行」はそのままの意味の動作です
「コピーの送信」は別のアカウントにこのフロー自体を送付できます
「オフにする」は自動実行フローを一時停止する場合に使用します
これ以外はほとんど利用しませんので、割愛します

共有とコピーの送信について

上記のメニューの中でややこしいのが、「共有」と「コピーの送信」です

この2つの操作はどちらも別アカウントで作成したフローを利用できるように共有する機能ですが、共有は別アカウントに所有権を持たせる機能で、コピーの送信は別アカウントを所有者として別のフローを新規作成してもらう形になります

共有の場合は、共同所有者という権限の元に自己アカウントと別アカウントの両方から編集が可能になります
これは主に、フローの設定値などを環境に合わせて変更したり、定期的に変更が必要な場合に別アカウントの人にフローの編集を行ってもらうことが難しい場合に有効です

別アカウントの人から条件設定を聞いてから自分でフローの編集を行えば、その別アカウントの人は新規条件にてフローを実行することが出来ます

一方、コピーの送信は別のフローとして新規作成を行います
なので自己アカウントにあるものはそのアカウントに共有されませんので自分の作ったものをオリジナルとして保管しておくことが出来ます

また、別アカウントの方で新規作成を行うため、そのフローの所有者となることが出来ます
この所有者権限は、2021年2月6日時点で譲渡が出来ない仕様となっています
そのため、作成元のアカウントを削除したりするとフローが利用できなる可能性があります
共有ではあくまでも共同所有者という権限で、所有者権限とは別権限になります

この共有とコピーの送信の機能に関しては、どちらの方が良いというものではありませんので、状況に合わせて使い分ける様にしてください

名前を付けて保存について

名前を付けて保存とは、このフローを別の名称を指定して複製する機能です
フロー自体には複製・コピーといった機能は無く、ここから既存のフローを元に作成する場合に利用できます

また、フロー自体が運用中であれば編集したりテストを実行することも出来ません
テスト自体が保存を実行しないと行えない仕様のためです

なのでフローの編集前に一度別フローとして作成してテストフローとして実行してみて問題ないことを確認してから、運用中フローを更新する。というような場合にも利用できます

オフにするについて

オフにするは、フローの実行を行わないようにする設定です
オフにした場合はこの場所が「オンにする」に切り替わりますので、ここをクリックすることでオンオフの切り替えを行うことが出来ます

基本的には自動実行フローにおいて、メンテナンスを行う際や一時的に利用を停止したい場合に使用します

ただ、この設定をオンにする際に注意が必要なのが、自動実行フローの場合にトリガーが保存されていることがあります
オフにしている間に、実行されなかったフローがオンにした瞬間に一気に連続して走ることがあります
そのため、自動実行フローを戻す場合にフローが実行されてほしくない場合はフローのアクションで一番最初に終了アクションを代入しておき、オンにして自動実行されるかを確認して実行されるようならすべての実行が終了されるまで待ってから、終了アクションを削除して本稼働させる、というような操作が必要になります

また、この設定をオフにした状態ではテスト実行も行えません
テストを行おうとした時点でエラー表示されます

詳細設定について

フローの名称や説明を編集する場合は、左上の範囲「詳細」の右上にある編集を選択してください

ここを選択すると編集画面が右側に表示されます
ここから編集を行います

ここではフローの名称と説明を記入することが出来ます
フローの名称自体はフローの編集画面でも編集可能です

説明は後で自分が見てわかるようにしっかり記載を行っておいてください

PowerAutomateについて

PowerAutomateについてと新規作成の方法について

PowerAutomateとは、Microsoftアプリの1つで契約内容によって制限が異なります
基本的にアプリの利用に関してはMicrosoftアカウントがあれば利用可能です

また、Webアプリとデスクトップアプリがありますが、根本的に機能がちがっておりデスクトップ版は2021年1月25日現在でプレミアム機能となっております
そのため、~~このサイトでの解説は全てWebアプリでの解説となります~~

先頃Windows10ユーザーに対して、無償版のPowerAutomateのデスクトップ版が提供されましたので、当サイトでも解説をしていこうと思います（2021年4月19日追記）

Microsoftアカウントを取得していれば、WebのMicrosoftアプリページに移動することで利用が開始できます

WebのOfficeを開くと左のアプリ一覧の一番下に、すべてのアプリを開くボタンがありますので、ここをクリックします

一覧に表示されたアプリの中から「PowerAutomate」を選択します
ここに表示されているアプリは、アカウントによって変化します
Web版なので、PCにインストールしているかどうかは関係ありません

フローを新規作成する

新規作成を行う場合は、左のメニューから「作成」ボタンを選択します
さらにフローというのは最初に実行条件である「トリガー」というものを最初に設定します

作成した後でも変更は可能なので、「自動化したクラウドフロー」や自動実行のトリガーは後でも大丈夫です
一番テストを行いやすい、手動実行トリガーが最初に選択するのがおすすめです

「インスタンスクラウドフロー」を選択して、次の画面を表示します

表示された画面でトリガーの選択とフローの名前を指定できます
フローの名前は後で変更可能なので、とりあえず空白で大丈夫です

「手動でフローをトリガーします」を探して選択して、チェックボタンを付けてください
基本は一番上に表示されていると思いますが、見当たらなければ種類は多くは無いので探してください

作成されると画面が作成画面に切り替わります
この時点でトリガーはすでに作成されています
1番目にあるものがトリガーです

トリガーはフローの中では特別なもので、常に最上部にあり1つしか指定できません

新しいステップをクリックすると、アクションの選択画面が表示されます

全てのアクションが表示されます
下向き矢印で区切られていますが、この範囲より上がアプリで下が実際に追加されるアクションとなります

なので、まずは上の部分から利用したいアプリを検索して選択します
そのあと選択したアプリに対応するアクションが表示されるので、そこから追加を行っていきます

まずはアプリの左上にある「コントロール」を選択してください

コントロールには、制御系のアクションがあります

今回はここから条件アクションを追加します
条件アクションはVBAでいうIF分岐になります
ここで指定した条件によってフローの流れを分岐させることが出来ます

条件式に設定する値を作成します
前にある処理はトリガーのみなので、ここに値を作成します

トリガーのタイトル部分をクリックして展開すると、画像のような表示になります
ここから入力の追加をクリックします

すると、引数の型の指定表示となります
PowerAutomateでは変数は型が存在しています
型自体は別記事での解説を行います

今回は整数値を指定できる「数」を指定してください

「数」を指定すると、画像のような表示になります
これがこの引数の初期設定になります
このままでも利用可能ですが、引数の名称だけは変更した方が分かりやすいです

引数の名称を「数」から「任意の数値」に変更しました
ここは引数名ですが、日本語入力は問題なく利用できますので、VBAでの変数名の付け方をイメージしてもらって良いですね

右側の入力ボックスには既定値の設定が行えます
今回は特に指定しなくても良いです

引数の設定が完了したら、その引数を条件アクションに設定します
この時に対象となる引数は、上記で設定した名称がリストに表示されます

中央のボックスは条件式の比較方法です
日本語で記載されているので、特に詳細に説明することも必要は無いとは思います
今回はそのままの「次の値に等しい」を指定しています

右のボックスには比較対象の入力値を設定します
今回はテストのため「0」を指定しておきます

これで引数が0かどうかで分岐処理を行うことが出来ます

作成が完了したら、上書き保存を実行します
PowerAutomateは自動保存は行わないので、保存を必ず実行するようにしてください

ちなみにエラー発生状態では保存が出来なくなります
原因の特定が出来なくなり保存が出来なくなることがありますが、その場合はWebアプリを終了すれば保存データに戻ります

この保存操作を行う際にフローチェックも行われているので、エラーが出た場合は修正を行ってください

動作確認には自動フローでもどちらであっても、このテストメニューから行えます
ここから実行することで、引数があるものは指定して実行することが可能です

実際に実行されるので、テストとはいえ本運用と同じ動きをするのでメール送信をしたりするような外部に対して処理を行っている場合は、事前に注意して実行してください

フローの動作のトリガーの設定画面が表示されます
手動トリガーであれば基本的には手動で構いません

自動を選択した場合は、過去に実行した履歴での実行を行う場合に利用します
過去履歴を選択しない場合は、この指定を行った後にトリガー操作を行う必要があります

手動を選択した場合に、引数がある場合はここで指定を行う画面が表示されます
今回は条件分岐の条件である「0」を指定します
複数の引数がある場合などは、ここに一緒に表示されます

設定値を入力出来たら、下にあるフローの実行をクリックして実際の実行を行います

フローが正常に実行されると、画像の画面が表示されますので完了ボタンをクリックしてください

ここは実行の完了であって、フローの処理が完了したわけでは無いので正常表示であれば特にみることはありません

完了ボタンをクリックすると、実行結果画面が表示されます
処理に時間のかかるものであれば、処理の実行段階が表示されていきます

アクション名の右上に緑のチェックマークの付いたものは正常に実行されたものに付きます
ここを確認することで実行されたルートの確認や、エラーの発生したアクションを確認できます

今回はこの条件アクションは実行され、条件式の結果もここで確認できます
「true」が返されていることから、条件式が真になったことが分かります

trueの場合には、この後のはいの場合に指定したアクションが実行されます
falseの場合には、いいえの場合が実行されます
この条件式は複数条件にすることも出来ます

解説についての注意点

本記事の作成日は2021年1月31日です
この時点での環境で画像等は作成しておりますが、このアプリはいまだ多くのプレビュー機能やアプリの追加が行われています
また、微妙なマイナーチェンジや機能の統合などが日々行われています
そのため、このサイトでの記事と実際の画面が違う可能性があります

また基本的に無いとは思いますが、アクション等の削除も考えれらます
Microsoftもアプリの向上を行っておりますが、それに伴った仕様変更は存在することはどうにもできません
そのため、このアプリで作成するフローは自動的に実行される場合が多いですがたまにはメンテナンスをしてあげてください

値の入力されたセルだけをクリアする

SpecialCellsメソッドの引数xlCellTypeConstantsを利用して値入力されたセルだけをクリアする方法について

'指定範囲内のみを対象とする
Range("A1:C3").SpecialCells(xlCellTypeConstants).ClearContents

'すべての使用済みセル範囲を対象とする
Cells.SpecialCells(xlCellTypeConstants).ClearContents

セル範囲の中で値の入力されたセルのみをクリアするには「SpecialCells」メソッドの引数「xlCellTypeConstants」を使用します

すでに関数によってある程度フォーマットが完成したシートに対して、入力値の部分だけを初期化する場合に利用します
単純にセル範囲全てをクリアしてしまうと、関数も消去されてしまうためです

なお、セル自体が数式が入力されているかを判定する方法は以下の記事で解説しています
ただ今回のような範囲に一括で処理を実行したい場合にはあまり向いていません

セル内容が数式かどうかを判定する

しかし、このメソッドもUsedRangeに影響を受けるため、既存のデータから上書き保存が許されないような処理の場合は記事コードはうまく動作しない場合がある点に注意が必要です
そのあたりに関しては以下の記事で解説していますので確認しておいてください

数式の入力されたセルだけを取得する

コード解説

画像の動きを確認してください
前提としてC列はA列＋B列の数式が入力されています
なので値入力された範囲はA2～B7となります

主にすでにあるフォーマットから入力範囲のデータを消去する場合に利用する場面が多いため記事コードでも前者の方が利用頻度は高いと思われます

'指定範囲内のみを対象とする
Range("A1:C3").SpecialCells(xlCellTypeConstants).ClearContents

このコードではA1～C3という範囲指定を行った上で、値のセルに対してClearContentsメソッドを実行して入力値を消去しています
画像ではA2～B3がその範囲に該当するため、そこが消去されています

こうして指定することでせっかく作った関数を消去せずに入力フォーマットを初期化できます

'すべての使用済みセル範囲を対象とする
Cells.SpecialCells(xlCellTypeConstants).ClearContents

こちらではシート全体のうち、使用済みセル範囲であるUsedRangeプロパティで取得される範囲の全ての値セルがクリアされます
この方法だと見出しや題目等の部分まで消去されるので、あまり利用頻度は無いかもしれません

ちなみにセル内に「=”見出し名”」というような入力を行えば、そのセルは数式と認定されるようになるので全ての範囲から値セルを消去しても問題なくなります
ただ全てを振り替えたり、修正があった場合に非常に面倒なので現実的な利用方法ではありません

この方法でセル範囲を限定すれば、ClearContentsメソッドを他のものに変更するだけで、値セルにだけ背景色を設定したりすることも簡単に行うことが出来ますので、アイデア次第で広がる汎用性のあるものだと思います
ただただUsedRangeに引っ張られる点を除けば、ですが・・・

数式の入力されたセルだけを取得する

SpecialCellsメソッドの引数xlCellTypeFormulasを利用して数式セルが入力されたセルだけを取得する

Debug.Print Cells.SpecialCells(xlCellTypeFormulas).Address

数式が入力されたセルを取得するには「SpecialCells」メソッドに引数「xlCellTypeFormulas」を使用します
この引数設定では数式が入力されたセル範囲を取得できます

このメソッドには利用時に注意点があります
それはUsedRangeプロパティが基準とされる点です
コードの解説とともに解説します

コード解説

記事コード含め合計4行を実行しています
前提として、シートの選択範囲が数式が入ったセルです
それ以外は空白セルは空白、数値が入っている部分とC列が数式です

記事コードが実行されると選択範囲と同じ範囲が取得されています
C2～C7のセル範囲が取得されます

ここで1つ注意したいのが、Cellsオブジェクトはワークシート全体のセル全てを指定しますが、内部的には自動的にUsedRangeプロパティに変換されます
ただこの引数設定ではあまり影響は無いかもしれません

ActiveCell.SpecialCells(xlCellTypeFormulas).Address

Range("A1").SpecialCells(xlCellTypeFormulas).Address

→→→　$C$2:$C$7

これら2行の指定でも同じくC列の入力範囲が取得されています
指定Rangeオブジェクトが単一の場合にはCellsオブジェクトと同様にUsedRangeプロパティに変換されて取得されます

ほとんどそういった利用は無いとは思いますが、数式かどうかの判定には利用できないですね

Range("A1:C3").SpecialCells(xlCellTypeFormulas).Address

→→→　$C$2:$C$3

ここではRangeオブジェクトで範囲指定を行っています
この場合はその範囲内で条件を満たすものが取得されます

A1～C3までのセル範囲のうち数式が入力されたセルはC2とC3となるため、その範囲が取得されています

この様に全ての範囲から取得を行うのではなく、限定された範囲から取得する場合はその範囲を指定してあげれば良いだけです

ただ今回の引数の際にはあまり影響は無いのですが、やはりUsedRangeプロパティの影響を受けています
指定範囲内であってもUsedRangeプロパティ外は検索対象に入りません

つまりIntersectメソッドを使ったときの様に、指定範囲とUsedRangeプロパティの範囲の重複する部分のみが対象となってしまいます

このメソッドを利用する場合にはとにかくUsedRangeプロパティへの注意が必要なため、以下の記事を参考にしてこのプロパティの動きをしっかり理解しておいてください

シートの使用済みセルを範囲選択

セル内容が数式かどうかを判定する

セルの入力内容が数式かどうかを判定するためのプロパティについて

'セルが数式かどうかを判定する（Trueなら数式）
Debug.Print Range("A1").HasFormula

セル内容が数式かどうかを判定するには「HasFormula」プロパティを使用します
Trueならセル内容は数式が入力されています、値等の数式以外であればFalseが返されます

セルの入力値には大きく分けて数式と値が存在しますが、それを判定するためのプロパティです
これはIs～～関数等のものではなく、Rangeオブジェクトのプロパティである点に認識が混同しないように注意が必要です

ちなみに数式と関数はこのプロパティ上は同一で判断します

コード解説

画像の動きを確認してください

まず前提としてB1セルにはA1セルに表示された数式が入力されています
ただ空白を返しているだけの数式になります
B2セルには何も入力されていません

なので空白かどうかの判定に関しては出力されるのは「True」が出力されています

これは空白チェック時の要注意点の1つなのですが、数式の結果が空白を返している場合は、セルが空白と混同して判定されてしまいます
この場合は空白かどうかを判定するだけでは不十分です

Debug.Print Range("B1").HasFormula

→→→　True

次にプロパティを利用した判定を取得してみます
この場合はB1には数式が入力されているため、Trueが返されます

ここでさらに空白チェックを複合的に条件を立てることにより、数式で空白が返されたものは除外することが出来ます

Debug.Print Range("B2").HasFormula

→→→　False

B2に関しては何も入力されていないため数式では無いのでFalseが返されます
ここは数式でなければ入力値があったとしても値であればFalseとなります

このプロパティを利用することで、指定セルから数式の空白を含めた空白を探すような特殊な場面で活用できます

ループ処理で不要行の削除を行う

ループ処理で条件判定を行って、不要な行を削除するちょっとした処理とその際の注意点の説明

Dim zzループ用セル As Range 'Forループ用のRange型変数
Dim zz対象セル As Range  '削除対象のセルの代入先のRange型変数

'指定セル範囲を全てループする
For Each zzループ用セル In Range("A2:A16")
'セルが空白なら削除対象とする
If zzループ用セル = "" Then
'対象セル変数が取得されているかで分岐
If zz対象セル Is Nothing Then
Set zz対象セル = zzループ用セル
Else
'取得されていればUnionメソッドを使用してセル範囲を追加
Set zz対象セル = Union(zz対象セル, zzループ用セル)
End If
Else: End If
Next zzループ用セル

'ループ終了後にセルが取得されているかを判定
If Not zz対象セル Is Nothing Then
'取得されていれば対象範囲を削除する
zz対象セル.EntireRow.Delete
'↓テーブル機能利用時はテーブルの列範囲内でなければエラーとなるので以下を利用する
'Intersect(zz対象セル.EntireRow, Range("A:D")).Delete
Set zz対象セル = Nothing
Else: End If

ループ処理で判定を行いつつ不要行の削除や挿入を行う際には、実際の削除処理を実行するタイミングが処理上で注意が必要です

というのも、セルのループをする場合は基本はForEachループを利用しますが、このループ処理では最初に指定のセル範囲を代入することになります
ループ中に削除を行うと、代入したセル範囲が矛盾を起こしてしまいます
処理の組み方次第では実行時エラーとなります

ループ処理で削除や挿入を行う際には、ループ処理中にメソッドは実行せずに対象セルを取得して、ループが終了してから一括で実行することで矛盾の発生を無くす事と処理速度の向上を実現できます

その際に利用するのはRange型の変数だけです
この変数に削除対象となるセル自体を代入していきます
ただ削除対象をそのままループで代入しても、前回のセルが上書きされてしまいますのでUnionメソッドを利用してセル範囲を追加していきます

ループ判定が終了した段階で、対象セルが取得されていればその範囲に対して一括でDeleteメソッドで削除を行います

コード利用の意味について

この目的は空白行など不要な行を削除するだけの処理です
なので、特別な処理は必要なく単純にIF分岐を利用して条件に一致する行を削除してしまえば良いだけに感じると思います

この画像は処理を実行する前の状況の画像です
このシートのテーブルデータのうち、A列のセルが空白になっている行を削除したい処理になります

コードはうまくいかないコードなので、画像内で見にくいですが確認してください
流れとしてはA列の指定セル範囲をForEachループで回しています
その際にセルが空白であれば行全体を削除しています

こちらの画像が処理を実際に実行したあとのモノです
一見して分かるように空白行が残ってしまっています

さらに確認してもらいたいのが、画像左下のイミディエイトに出力されたループ処理で実行されたセルのAddress文字列です

ForEachループではA2～A16までを代入して実行しているのに、A11までしかループが実行されていません

これはループ中に削除を行うことにより対象のセル数が減少していること
さらに削除した時点で、ForEachループの参照するセルが存在しなくなったことで自動的に次のセルを参照してしまっているため、連続した空白行の部分がうまく処理できていない状況です

これはそもそも仕様に対処する場合には、ループ処理を上からではなく下から行うことで対処することは出来ます
ですがその場合ForEachループは利用できなくなります

コード解説

記事コードの流れで処理を組むことで、目的の達成と処理速度の向上を図れます

この画像は上記のコード実行前から記事コードを実行した後の状態の画像です
今回はしっかりと空白行が無くなっていることが分かると思います

ループ処理で実行されたセルのAddress一覧 — ForEachループで参照されたセルAddress一覧

この画像はイミディエイトに出力されたものです
ForEachループで指定したセル全てがしっかり処理されていることが確認できると思います

Dim zzループ用セル As Range 'Forループ用のRange型変数
Dim zz対象セル As Range  '削除対象のセルの代入先のRange型変数

最初に使用する変数の宣言です
この処理ではRange型変数を2個使用します

1つはForEachループ用の変数です
もう1つは削除対象のセルを取得する変数です
ここに代入したセル範囲に対して削除を実行します

'指定セル範囲を全てループする
For Each zzループ用セル In Range("A2:A16")

～～中略～～

Next zzループ用セル

ForEachループの個所です
ここではとりあえずとして、セル範囲は決め打ちの処理になっています
実際にはワークシートのセル範囲であればUsedRangeやCurrentRegionのプロパティや画像のようなテーブル範囲であればListObjectsオブジェクトの各プロパティ等を利用してセル範囲を取得してください

'セルが空白なら削除対象とする
If zzループ用セル = "" Then

～～中略～～

Else: End If

削除条件を設定するIF分岐個所になります
ここの条件を変更することで削除対象を変更することが出来ます
逆に空白ではない行を削除したり、特定の値を対象とすることも出来ます

'対象セル変数が取得されているかで分岐
If zz対象セル Is Nothing Then
Set zz対象セル = zzループ用セル
Else
'取得されていればUnionメソッドを使用してセル範囲を追加
Set zz対象セル = Union(zz対象セル, zzループ用セル)
End If

削除条件に一致するセルであった場合は対象セル変数に代入させます
しかし、そのまま代入すると新しいセルで上書きされていってしまいますので、すでに取得対象が存在する場合は既存のセル範囲を残して追加する必要があります
そのためUnionメソッドを利用して、取得済み範囲に追加してきます

ただ、このメソッドは2つ以上のセル範囲を追加するメソッドのため
未取得のNothingではエラーとなります
そのため、最初に取得済みかどうかの判定を行ってから、取得済みでなければループ変数のセル範囲を代入して、取得済みであれば既存の範囲に追加します

この後で行全体選択のプロパティ等を利用するので、この時点の取得範囲はセル単体の追加でも問題はありません

'ループ終了後にセルが取得されているかを判定
If Not zz対象セル Is Nothing Then
'取得されていれば対象範囲を削除する
zz対象セル.EntireRow.Delete
'↓テーブル機能利用時はテーブルの列範囲内でなければエラーとなるので以下を利用する
'Intersect(zz対象セル.EntireRow, Range("A:D")).Delete
Set zz対象セル = Nothing
Else: End If

ここはループを抜けてきた後の処理になります
ループはあくまでも削除対象のセルを判定して取得を行うためのものになります
つまり、この時点で削除対象が必ず存在しているということは分かりません

ループを抜けてきた時点で対象セル変数がNothing（初期値）となっているかどうかで存在確認が出来ます
Nothingはオブジェクトなので、比較演算子はIsを利用する必要があります
この比較演算子は反対の意味の演算子が無いので、Notを利用して逆説にすることで、Nothingではない（対象セルが存在する）という条件が成り立つときに削除処理を実行します

取得した対象セルは単一セルなので、行全体を指定するためにEntireRowプロパティを利用して削除を実行します

ただここで問題がある場面がテーブル機能の場合になります
テーブル機能範囲の行を削除する場合に連続していない行全体を削除しようとすると画像の実行時エラーが発生します

このエラーが発生して、Deleteメソッドが実行できません
その場合にはコメントアウトされているコードを利用してください

'↓テーブル機能利用時はテーブルの列範囲内でなければエラーとなるので以下を利用する
'Intersect(zz対象セル.EntireRow, Range("A:D")).Delete

こちらのコードはテーブル機能を利用している場合に利用するコードになります
テーブル機能の範囲を削除を行う場合には、テーブル機能の列範囲と同じ列範囲を指定する必要があります

ここでは決め打ちの列指定ですが、実際に使用する場合はDataBodyRangeプロパティ等を利用して列範囲を取得するようにするといいです

ちなみにこのセルを変数に代入させて、あとでまとめて一括で処理を行う方法は処理向上にとても貢献します
削除だけでなく、挿入やセルの書式設定等や一括入力などにも利用できます

レコードの新規追加（ADODB.Recordset）

AccessデータベースにExcelのデータをレコードとして新規追加するコード

'接続済みのデータベースのテーブルへのレコードセットの接続
zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3
’レコードの新規作成
zzDB_Rec.AddNew
’新規作成したレコードへの代入
zzDB_Rec!ID = ActiveCell
’レコードのデータベースへの更新
zzDB_Rec.Update
’レコードセットの接続を解除
zzDB_Rec.Close

データベースへのレコードの新規追加にはADOオブジェクトの「AddNew」メソッドを使用します
このメソッドを実行しただけでは既定値が入力されたレコードが作成されるだけになります
その追加したレコードに代入するにはADOオブジェクトのインデックス番号か、例コードのように列名を指定して代入します
代入後に「Update」メソッドを実行することでデータベースへの登録更新が完了します
操作後はADOオブジェクトの接続を解除します

レコードの新規追加といっても、実用で考えるとこの一連の手順が最低限必要になります

また、レコードの新規追加にはSQLのINSERT文も利用可能ですが、圧倒的にこちらのメソッドの方が高速なため、基本的にはレコードの追加はこちらのコードを利用します

メソッドの書式

引数（太字は必須引数）
AddNew FieldList, Values

このメソッドには引数が2つあり、どちらも省略可能です
この引数はそれぞれが対応しています

「FieldList」は列名を指定して、「Values」はその代入値の指定です
この指定は列名を文字列として指定して値を入力させます
この引数のイメージはSQLでのINSERT文の引数と同じイメージになります

また、この引数は配列を指定する必要があるためArray関数を使用して配列として指定する必要があります

zzDB_Rec.AddNew Array("ID", "日付"), Array(ActiveCell.Value, ActiveCell.Offset(0, 1).Value)

この様に引数の2つをどちらも指定して、かつ配列として指定する必要があります
こうすることで、例コードの様に1列ずつ代入せずに一括代入が出来ます

しかし、おおよそ一括代入は処理が高速になるはずなのですがこのメソッドはその通りでは無いようです
ほんの数回程度の検証しかしていませんが、10万件2列のデータの取得を行ったところメソッドの引数を使用した場合は1.4秒前後となりました
対して、各列ごとの取得では1.1秒前後となり、0.3秒もの差がつきました

CPU性能等に非常に影響があるので、数字に意味はありませんがコードの可読性含めて考慮するとほぼ利用する意味がありません
どの列に何を代入しているかは各列入力の方が分かりやすいです

実際の使用例

シートのテーブルに入力されたデータをデータベースに登録を行います
上記の引数説明にあるように、メソッドの引数を指定する方法は利用しません

コードの動きに関しても、データベースへの登録作業のため逐一の動きが確認できませんので結果のみを画像表示します

この画像はExcelに入力されたテーブル内容のデータです
今回のコードはこの画像のデータをデータベースに登録を行います

自分の環境にはAccessがありませんので、データの確認としてExcelのナビゲーター画面を利用します
この画像の右範囲を確認してもらうと分かるように、まだデータベースにデータは何も登録されていませんので、空のテーブルであることが確認できます

先に結果の画像を確認してみます
先ほどは空のテーブルであった部分にデータが入力されています

Nullというのはデータベースでデータが無い場合の値です
「””」は文字列の空白を意味するので、Null=””は成り立ちません

Excelのセルの空白はデータベースではNullが代入されます
これを防ぎたい場合は、空白の判定処理を入れる必要があります
Nullが許可されていない列の場合は実行時エラーが発生します

コード解説

Sub 処理テスト()


Dim zzDB_Con As Object
Set zzDB_Con = CreateObject("ADODB.Connection")
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")

'データベースへの接続開始
zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"
zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3

Do Until ActiveCell = ""
zzDB_Rec.AddNew
zzDB_Rec!ID = ActiveCell
zzDB_Rec!日付 = ActiveCell.Offset(0, 1)
zzDB_Rec!コード = ActiveCell.Offset(0, 2)
zzDB_Rec!金額 = ActiveCell.Offset(0, 3)
ActiveCell.Offset(1, 0).Select
zzDB_Rec.Update
Loop

zzDB_Rec.Close
Set zzDB_Rec = Nothing
zzDB_Con.Close
Set zzDB_Con = Nothing


End Sub

上の処理を実際におこなったコードです

Dim zzDB_Con As Object
Set zzDB_Con = CreateObject("ADODB.Connection")
Dim zzDB_Rec As Object
Set zzDB_Rec = CreateObject("ADODB.Recordset")

ここではデータベースへの接続のConnectionオブジェクトとRecordsetオブジェクトの作成を行っています
レコード操作を行う場合はRecordsetオブジェクトを利用します

'データベースへの接続開始
zzDB_Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\作成DB.accdb"

データベースへの接続を開始します
ここではAccdb形式のデータベースへの接続を行っています
Providerに関してはほぼ固定なので文字列をコピペしてもらって大丈夫です

zzDB_Rec.Open "テーブル名", zzDB_Con, 0, 3

ここでRecordsetオブジェクトを接続しています
引数の1つ目はテーブル名を文字列で指定します
2つ目の引数はデータベースへの接続を指定するので、前の手順で接続したConnectionオブジェクトを指定します

3つ目と4つ目はレコードを操作する際の方法の設定になります
詳細は別の記事にて行います
ここでは、レコードを前方移動で共有ロックをかけています
ロックに関しては「3」を「2」に変えても良いです

なお、この数値は参照設定を行えば定数が利用できます
参照設定を行っていないとエラーとなるので、数値を直接指定すると安定感があります

Do Until ActiveCell = ""
zzDB_Rec.AddNew
zzDB_Rec!ID = ActiveCell
zzDB_Rec!日付 = ActiveCell.Offset(0, 1)
zzDB_Rec!コード = ActiveCell.Offset(0, 2)
zzDB_Rec!金額 = ActiveCell.Offset(0, 3)
ActiveCell.Offset(1, 0).Select
zzDB_Rec.Update
Loop

ここでループ処理により空白セルになるまで行のデータを登録していっています
新規追加して、データを各列ごとに代入させています

zzDB_Rec!ID = ActiveCell

各列に代入する場合はRecordsetオブジェクトに「!」で列名を接続して指定することで列を限定できます

AddNewメソッドの引数だと1行に全てを一気に記載する必要がありますが、例の様に各列を指定できるので、こちらのほうが見やすいです
そのうえ速いなら選択の余地がないかもしれませんね

zzDB_Rec.Update

「Update」メソッドは更新した内容をデータベースに確定させるメソッドです
この時点でデータベースが更新されます

なお、AddNewメソッドはUpdateメソッドが実行されていないレコードがあった場合に、AddNewメソッドが実行されると自動的に更新確定を行います

zzDB_Rec.Close
Set zzDB_Rec = Nothing
zzDB_Con.Close
Set zzDB_Con = Nothing

最後にRecordsetオブジェクトの接続解除、データベースへの接続解除を行います
これはお決まりの流れです

以上の様にレコードは登録処理を行います
AddNewメソッドは非常に高速です
さらに、このメソッドを最速の環境にする方法としてデータベースを接続せずにテーブルをADOオブジェクト上に作成して、レコード登録が最も早いです
およそ、この記事での例コードの倍ほどの速さを発揮します
すこしテーブル作成がやっかいなのがネックです