Upgrade to Pro — share decks privately, control downloads, hide ads and more …

RPA勉強会!人気No.1EXCEL関数 VLOOKUPをUiPathで実現! - 公開改定版

HANACCHI
September 14, 2021
4.6k

RPA勉強会!人気No.1EXCEL関数 VLOOKUPをUiPathで実現! - 公開改定版

2021/9/14 RPACommunity版(誤字修正)

HANACCHI

September 14, 2021
Tweet

Transcript

  1. はなっち!とは…  2019年10月 UiPath Japan MVP 2019 認定  2020年10月

    UiPath Japan MVP 2020 認定  2021年10月 UiPath Japan MVP 2021 認定されるか!?  2020年11月 定年退職  同 年 同 月 FUJITSU UT社と契約  お客様先でRPAサポート&開発  Twitter:hjmkzk  Qiita:@HANACCHI  FaceBook:hajime.kozaki
  2. Today’s EXCELのその先へ 1. Studio Advance • VLookup関数のように、マスタ上のデータを参照したい! • 「データテーブルの結合」で、マスタ上のデータを参照したい! •

    マスタ上のデータをUNIQUEにしたい! • UNIQUE後の加工処理 【発展】 • キーから、マスタの行番号、わかってんでね? • DataTable.ImportRowメソッドを使って、変わった事が出来る!?
  3. DataTable型  1以上の複数の列を持つ。列にはデータの型属性を保有している  0以上の複数の行を持つ  0から始まるn行目の、 0から始まるn列番号あるいは列名で、値を参照/設定できる DataTable 列番号

    0 1 2 列名 列1 列2 列値 ↓行目 0 A AA 1 AA B 2 B B 3 C X 0から始まる3行目の、0から始まる1列番号は、 DataTable.Rows(3).Item(1)で参照できる(“X”が参照できる) DataTable.Rows(3).Item(“列”)でもOK! 参考
  4. DataTable型  フィルタで抽出/除外が出来る ⇒「データテーブルのフィルタ」  それぞれキーを指定し、条件に合致した行を結合することが出来る ⇒「データテーブルの結合」  1つのキーを指定して、並び替えする事が出来る。 複数キーの場合は、下位キーから順に並び替えする事で実現出来る。

    ⇒「データテーブルの並び替え」  二つのDataTableをマージする事が出来る。 ⇒「データテーブルのマージ」  そのままEXCELブック、CSVファイルなどに出力できる ⇒「範囲へ書込み」「CSVへ書込み」 参考
  5. 「データテーブルの結合」で、マスタ上のデータを参照したい! 結合型  Inner:入力データテーブル1と、入力データテーブル2とのキーが合致した行を、 横に並べて出力データテーブルに出力する  Left:入力データテーブル1と入力データテーブル2とのキーが合致した行を、横 に並べて出力データテーブルに出力する。 更に、入力データテーブル1のうち、入力データテーブル2とのキーが合致しな かった行も出力データテーブルに出力する。

     Full:入力データテーブル1と入力データテーブル2とのキーが合致した行を、横 に並べて出力データテーブルに出力する。 更に、入力データテーブル1のうち、入力データテーブル2とのキーが合致しな かった行も出力データテーブルに出力する。 更に、入力データテーブル2のうち、入力データテーブル1とのキーが合致しな かった行も出力データテーブルに出力する。
  6. 「範囲を読み込み」アクティビティ で読み込んだDataTableのイメージ 順 処理 結果 上から順に コレクションに無かったら キー,行番号追加 B,Bは、5行目を採用 上から順に

    コレクションに無条件に キー,行番号上書き B,Bは、7行目を採用 上から順に コレクションに無かったら キー追加 B,Bは、存在する 上から順に コレクションに無条件に キー上書き。 終わったら、.Distinct.ToList B,Bは、存在する 下から順に コレクションに無条件に キー,行番号上書き B,Bは、5行目を採用 マスタ上のデータをUNIQUEにしたい! • 行位置を使用するには、Dictionary(Of String, Int32)型を使用します ⇒代入アクティビティで、初期化する必要がある(New Dictionary(Of String, Int32)) • 存在だけでいい場合には、List(Of String)型を使用します ⇒代入アクティビティで、初期化する必要がある(New List(Of String)) (0) (1) (2) (3) (4) (5)
  7. Dictionary型  ディクショナリ内のキーの型<TKey>と、ディクショナリ内の値の型<TValue> をPairで保有できる変数。  ディクショナリ内のキーに格納される値は常にUNIQUEである。  ディクショナリ内のキーへ値を渡す事で、ディクショナリ内の値が得られる  .Containsで、指定したキーが

    Dictionary<TKey,TValue> に格納されているかど うかを判断できる。 Dictionary Key(String) Value(Int32) 列1<tab>列2 列値 A<tab>AA 0 AA<tab>B 1 B<tab>B 2 C<tab>X 3 • Dictionary変数 = New Dictionary(Of String, Int32)で初期化必要! • Dictionary変数(キー)で、値が参照できる! • 繰り返し(コレクションの各要素)の受けの型は、KeyValuePairである https://docs.microsoft.com/ja-jp/dotnet/api/system.collections.generic.dictionary-2?view=net-5.0 (0) (1) (2) (3) 参考
  8. List型  インデックスを使用してアクセスできる、リスト内の要素の型<T>指定された オブジェクトのリストの変数。  リスト内の要素に格納される値は、格納順である  リストの変数にインデッスクを渡す事で、リスト内の要素の値が得られる  .Containsで、ある要素が

    List<T> 内に存在するかどうかを判断できる List String 列1<tab>列2 A<tab>AA AA<tab>B B<tab>B C<tab>X https://docs.microsoft.com/ja-jp/dotnet/api/system.collections.generic.list-1?view=net-5.0 • List変数 = New List(Of String)で初期化必要! • List変数(ix)で、ixで示される位置の値が参照できる! • 繰り返し(コレクションの各要素)の受けの型は、指定属性である (0) (1) (2) (3) 参考
  9. 「範囲を読み込み」アクティビティ で読み込んだDataTableのイメージ 順 処理(List型へ) 結果 上から順に コレクションに無条件に キー上書き。 終わったら、.Distinct.ToList B,Bは、存在する

    マスタ上のデータをUNIQUEにしたい! FWDLst = Key (キー値) List A<Tab>A AA<Tab>A AA<Tab>B B<Tab>B B<Tab>B B<Tab>B List A<Tab>A AA<Tab>A AA<Tab>B B<Tab>B .Distinct (0) (1) (2) (3) (4) (5)
  10. 「範囲を読み込み」アクティビティ で読み込んだDataTableのイメージ 順 処理(Dictionary型へ) 結果 下から順に コレクションに無条件に キー,行番号上書き B,Bは、5行目を採用 マスタ上のデータをUNIQUEにしたい!

    RVSDic(Key) = 行総数 - ix - 1 (現在のインデックスが増えれば、計算行位置は減る) Key Value B<Tab>B 5->4->3 AA<Tab>B 2 AA<Tab>A 1 A<Tab>A 0 (0) (1) (2) (3) (4) (5)
  11. ユニーク後の加工処理 「範囲を読み込み」アクティビティ で読み込んだDataTableのイメージ Key Value A<Tab>A 0 AA<Tab>A 1 AA<Tab>B

    2 B<Tab>B 3->4->5 1. 「データ行を追加」アクティビティを使って! ⇒簡単! 2. 「メソッドを呼び出し」アクティビティで、 DataTable.ImportRowメソッドを使って! ⇒変わった事が出来る!? 別のDataTable (0) (1) (2) (3) (4) (5) DIC
  12. 「範囲を読み込み」アクティビティ で読み込んだDataTableのイメージ Key Value A<Tab>A 0 AA<Tab>A 1 AA<Tab>B 2

    B<Tab>B 3->4->5 別のDataTable ユニーク後の加工処理(データ行を追加) (0) (1) (2) (3) (4) (5) DIC Dictionary BASTDtの列情報のみ KeyValuePair .ItemArray 指定行の、 全ての列の値を 配列にする
  13. 「範囲を読み込み」アクティビティ で読み込んだDataTableのイメージ Key Value A<Tab>A 0 AA<Tab>A 1 AA<Tab>B 2

    B<Tab>B 3->4->5 別のDataTable ユニーク後の加工処理(ImportRow) DIC Dictionary BASTDtの列情報のみ KeyValuePair ImportRow 指定行を Importする (0) (1) (2) (3) (4) (5)
  14. キーから、マスタの行番号、わかってんでね? 「範囲を読み込み」アクティビティ で読み込んだDataTableのイメージ Key Value A<Tab>A 0 AA<Tab>A 1 AA<Tab>B

    2 B<Tab>B 3->4->5 • [元DataTable]から、Keyを構成して、 <Dictionary>を作った • <Dictionary>には、そのKeyを持つ[元 DataTable]の行位置が格納されている • 参照しようとしている{DataTable}からKeyを構 成して、 <Dictionary>から[元DataTable]の行 位置を取得できる! 別のDataTable (0) (1) (2) (3) (4) (5) DIC
  15. DataTable型  1以上の複数の列を持つ。列にはデータの型属性を保有している  0以上の複数の行を持つ  0から始まるn行目の、 0から始まるn列番号あるいは列名で、値を参照/設定できる DataTable 列番号

    0 1 2 列名 列1 列2 列値 ↓行目 0 A AA 1 AA B 2 B B 3 C X 0から始まる3行目の、0から始まる1列番号は、 DataTable.Rows(3).Item(1)で参照できる(“X”が参照できる) DataTable.Rows(3).Item(“列”)でもOK! 参考
  16. DataTable型  フィルタで抽出/除外が出来る ⇒「データテーブルのフィルタ」  それぞれキーを指定し、条件に合致した行を結合することが出来る ⇒「データテーブルの結合」  1つのキーを指定して、並び替えする事が出来る。 複数キーの場合は、下位キーから順に並び替えする事で実現出来る。

    ⇒「データテーブルの並び替え」  二つのDataTableをマージする事が出来る。 ⇒「データテーブルのマージ」  そのままEXCELブック、CSVファイルなどに出力できる ⇒「範囲へ書込み」「CSVへ書込み」 参考
  17. Sum関数/SubTotal関数のように、キー毎の合計値を求めたい! RPAでは、どうするか? 使用アクティビティ 1. 準備 最終DataTable領域確保。 元DataTableと列を同一構成にする 2. 「商品CD」「日付」でSORTする 「データ

    テーブルを並べ替え」を使う 合計をクリア 3. 「商品CD」でUNIQUEな値を求める 「繰り返し (データ テーブルの各行)」を使って、 Dictionary型(Of String, Int32)へ、キー挿入 4. 全Dictionaryを対象に繰り返す 繰り返し (コレクションの各要素) a. UNIQUEな「商品CD」で抽出フィルター を掛ける 「データ テーブルをフィルター」を使う b. 抽出したDataTableで、「金額」の合算 を行う 小計をクリアしたのち、「繰り返し (データ テーブル の各行)」を使って、小計に加算、合計に加算 c. 「小計」を、抽出したDataTableにデー タ行を追加する DataRow型の変数を用意し、 中間DataTableへ「データ行を追加」 d. 最終的なDataTableにマージする 「データ テーブルをマージ」を使う 5. 「合計」をDataTableにデータ行を追加する DataRow型の変数を用意し、 最終DataTableへ「データ行を追加」
  18. 1. 準備 • New DataTableで、DataTable型を初期 化する • 列の情報はこの時点では構築されていな い。 

    「データ列の追加」で定義  元Dt.Cloneで、 元Dtの列情報をクローンコピーする New DataTable 列1(Str) 列2(Str) 列値(In32) KOZAKI HAJIME 60
  19. DataTable型のハンドリングの注意事項! 1. DataTableから無指定代入 受け側のDataTableの領域は、 送り側のDataTableの領域と同じ ⇒受け側を操作すると送り側も変わる 参考 列1(Str) 列2(Str) 列値(In32)

    KOZAKI HAJIME 60 列1(Str) 列2(Str) 列値(In32) KOZAKI HAJIME 60 列1(Str) 列2(Str) 列値(In32) KOZAKI HAJIME 60 列1(Str) 列2(Str) 列値(In32) 2. DataTableから.Copy代入 受け側のDataTableの領域は、 送り側のDataTableの領域と別。 ⇒受け側を操作すると送り側も変わらない 3. DataTableから.Clone代入 受け側のDataTableの領域は、 送り側のDataTableの列定義のみが同じ。 ⇒行は代入されない
  20. • このアクティビティでは、SORT キーが一つしか指定できない • SORTキーが複数ある場合は、下位キー から並び替えをし、上位キーまで繰り返 す  この場合、「日付」をまずSORTし、 結果を同じDataTable域を流用する

     次に、「商品CD」をSORTする 2. 「商品CD」「日付」でSORTする 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/6 BX-1 110 2 220 2021/9/6 CX-1 1,000 3 3,000 2021/9/6 DX-1 600 2 1,200 2021/9/7 AX-1 100 2 200 2021/9/7 CX-1 1,000 2 2,000 2021/9/9 DX-1 600 4 2,400 2021/9/9 AX-1 100 3 300 2021/9/9 CX-1 1,000 2 2,000 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 2021/9/6 BX-1 110 2 220 2021/9/6 CX-1 1,000 3 3,000 2021/9/7 CX-1 1,000 2 2,000 2021/9/9 CX-1 1,000 2 2,000 2021/9/6 DX-1 600 2 1,200 2021/9/9 DX-1 600 4 2,400 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/6 BX-1 110 2 220 2021/9/6 CX-1 1,000 3 3,000 2021/9/6 DX-1 600 2 1,200 2021/9/7 AX-1 100 2 200 2021/9/7 CX-1 1,000 2 2,000 2021/9/9 DX-1 600 4 2,400 2021/9/9 AX-1 100 3 300 2021/9/9 CX-1 1,000 2 2,000
  21. 3. 「商品CD」でUNIQUEな値を求める • New Dictionary(Of String, Int32)型で、 Dictionary型を初期化する • 対象とするDataTableを対象に、辞書機

    能で、UNIQUEな値を求める 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 2021/9/6 BX-1 110 2 220 2021/9/6 CX-1 1,000 3 3,000 2021/9/7 CX-1 1,000 2 2,000 2021/9/9 CX-1 1,000 2 2,000 2021/9/6 DX-1 600 2 1,200 2021/9/9 DX-1 600 4 2,400 Key Value AX-1 0 BX-1 0 CX-1 0 DX-1 0
  22. a. UNIQUEな「商品CD」で抽出フィル ターを掛ける b. 抽出したDataTableで、「金額」の合算 を行う c. 合算値を小計とするため、抽出した DataTableにデータ行を追加する d.

    最終的なDataTableにマージする 4. 全Dictionaryを対象に繰り返す 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 2021/9/6 BX-1 110 2 220 2021/9/6 CX-1 1,000 3 3,000 2021/9/7 CX-1 1,000 2 2,000 2021/9/9 CX-1 1,000 2 2,000 2021/9/6 DX-1 600 2 1,200 2021/9/9 DX-1 600 4 2,400 Key Value AX-1 0 BX-1 0 CX-1 0 DX-1 0
  23. 日付 商品CD 単価 数量 金額 2021/9/6 CX-1 1,000 3 3,000

    2021/9/7 CX-1 1,000 2 2,000 2021/9/9 CX-1 1,000 2 2,000 日付 商品CD 単価 数量 金額 2021/9/6 DX-1 600 2 1,200 2021/9/9 DX-1 600 4 2,400 • [入力データテーブル]から、フィルタ行 の条件(行の”商品CD”が、UNIQUEな キーと一致)で“保持”されたデータを、 [出力データテーブル]に出力する 4. a. UNIQUEな「商品CD」で抽出フィルターを掛ける 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 2021/9/6 BX-1 110 2 220 2021/9/6 CX-1 1,000 3 3,000 2021/9/7 CX-1 1,000 2 2,000 2021/9/9 CX-1 1,000 2 2,000 2021/9/6 DX-1 600 2 1,200 2021/9/9 DX-1 600 4 2,400 Key Value AX-1 0 BX-1 0 CX-1 0 DX-1 0 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 日付 商品CD 単価 数量 金額 2021/9/6 BX-1 110 2 220
  24. • 「金額」から「小計」の合計を求める前 に、「小計」をクリアする • 先行アクティビティの、商品CDで抽出 した[出力データテーブル]の全行を対象 に、「金額」をInt32に変換した結果を、 「小計」に加算する • 同様に、「合計」にも加算する

    4. b.抽出したDataTableで、「金額」の合算を行う 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 0 100 300 600 日付 商品CD 単価 数量 金額 2021/9/6 BX-1 110 2 220 0 220
  25. • 格納するDataTable(TEMPDT)のメソッ ド.NewRowを使用し、新しい行の領域 を確保する ⇒GOALRowが新しい領域 • その領域の”商品CD”列に”小計”を代入す る • 続けて、”金額”列に、小計を代入する

    • 「データ行の追加」を使用し、格納する DataTable(TEMPDT)に、新しい行の領 域を追加する 4. c.「小計」を、抽出したDataTableにデータ行を追加する 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 日付 商品CD 単価 数量 金額 .NewRow ”小計” 600 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 小計 600 「データ行の追加」 600
  26. • 格納するDataTable(TEMPDT)を最終的 なDataTable(GOALDT)にマージ(追 記)する 4. d.最終的なDataTableにマージする 日付 商品CD 単価 数量

    金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 小計 600 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 小計 600 日付 商品CD 単価 数量 金額 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 小計 600 日付 商品CD 単価 数量 金額 2021/9/6 BX-1 110 2 220 小計 220 日付 商品CD 単価 数量 金額 2021/9/6 AX-1 100 1 100 2021/9/7 AX-1 100 2 200 2021/9/9 AX-1 100 3 300 小計 600 2021/9/6 BX-1 110 2 220 小計 220 TEMPDT GOALDT GOALDT
  27. • 格納するDataTable(GOALDT)のメソッ ド.NewRowを使用し、新しい行の領域 を確保する ⇒GOALRowが新しい領域 • その領域の”商品CD”列に”合計”を代入す る • 続けて、”金額”列に、合計を代入する

    • 「データ行の追加」を使用し、格納する DataTable(GOALDT)に、新しい行の領 域を追加する 5. 「合計」を、DataTableにデータ行を追加する  スクリーンショットは横着して、「TEMPDT」、 「小計」のものを流用(^^♪  アニメーションも省略(^^♪