EXCEL,ACCESS, Microsoft365連携勉強会 発表 RYU.CYBER（冴場 竜） TWITTER: @CYBERWINTELLIG1

今回の仮想職場の実態？？ 半沢直樹のドラマの舞台である銀行のように、いくつかの組織が統廃合によって1つになった組織 旧組織の派閥など壁がある 現場第一主義のため、会社でいうと本社にあたる部門の権力が弱い 勝手にサブ組織が独自ルールで運用 システムの勝手に作って運用 ファイルサーバーから（共有アカウントのオンパレードetc） ちょっと前にトップのボスが声がでかい人になり、本社にあたる部門が強化されてきて、やっと当たり 前になってきたかな？

でも ○○申請書はEXCEL 手書きを清書しただけとかわらん 申請書→目視→システム投入 自分たちで作業が嫌なので、単価の安いところに丸投げ やっと全組織共通のADができたくらい それまではシステムごとにアカウント管理

セキュリティー面から アカウント管理はセキュリティー管理のイロハのイの字の一画目 追加申請 → ないと困るので、申請はなされます 削除申請 → 無視。真面目にやるやつはほぼ皆無。 ISMS上の削除整理しました書面だけ作成（書類の偽造）

ADの管理はどうなってるの？ 会社でいうと本社にあたる部門の権力が「まだ」弱い 勝手にサブ組織が独自ルールで運用したがる 本社相当部門で完全管理したいが・・・ 各サブ組織にOU割り当て、管理権限を委任

サブ組織 稼ぐ部門 面倒くさいことはやらない OU管理権限の役割与えられてる人は兼任で、評価につながらないので真面目にやらない 削除申請 → 無視。真面目にやるやつはほぼ皆無。 ISMS上の削除整理しました書面だけ作成（書類の偽造） 監査のときにあわててそろえる

問題点 あるべき状態 OU管理権限者が、定期的に棚卸しする 現状 OU管理権限者は管理画面にログインすらしない でも、一括管理は嫌がる

だったら おまえの部署はこんだけひどい状況だぞ！！！と晒し者にするしかあるめ 晒し者にして組織評価減点材料を作ってやろう ボーナス査定下がるってならやるでしょ

現状の見える化 これまでもそれなりにはやってました でも、ADから情報取り出し、手作業でEXCEL化 「ファイルサーバーの所定フォルダーに最新版おいてるから、該当者見てね」って状態 EXCEL化から配置まで、作業は単価の安い人に丸投げ

ここ1、2年で 組織内システムのクラウド化 ◦ Microsoft365導入 ◦ VDI導入

そしてコロナ禍 テンポラリーな要員は削減対象 特に、稼いでない本社相当組織は予算大幅削減 投資は０（ゼーロー) 自分たちで全部やれ

やったろうじゃん自動化 APTの名に懸けて

ADの状況 情シス管理のOU ◦ ユーザーは意識せず人事異動で自動的に更新 ◦ 組織構造、配属 サブ組織管理のOU ◦ 各サブ組織で管理 ◦ 案件毎などファイルサーバーアクセス権グループに

ADの状況 サブ組織は自分管理のアクセス権グループ実態を把握してない、しようとしてない サブ組織を横断したアクセス権グループはどちらのサブ組織（例えば、総務部、財務部）が主 管なのか？を理解しておらずアンマネージド

情シス管理のOU

サブ組織管理のOU

サブ組織管理のOU

表の結合（左外結合） サブ組織管理のOU ◦ アクセス権グループ ◦ そのアクセス権に 所属するユーザー一覧 • 情シス管理のOU • あるユーザーの所属組織 • 兼任も含めて 左外結合

結合した表でわかること 例：コンサル部が管理する案件Aのアクセス権グループに誰が所属しており、かつ その誰がどこ の組織に居るか？ 人事異動があっても「その誰がどこの組織に居るか？」が児童反映 • 案件Aにはコンサル部の人間しか所属していけないのであれば、ア ラートが出せる

全体図 組織内NW 組織AD ユーザーPC ファイルサーバー List アプリ アプリ データー利用 ユーザーPC 1 2 3 4 5 7 6 SQL

①AD→EXCEL PowerQuery 【AD管理】Active Directoryの特定グ ループのユーザー設定を常に最新の状 態でExcelに一覧で表示(抽出)させる方 法 http://lifekeynotes.com/how-to-get-the- value-in-the-bottom-row-when-there-is- more-than-one-same-value-when-you- want-to-match-another-value-in-the- current-sheet-and-another-sheet-in- excel-to-get-another-specific-column-in- a-r/

②ファイル保存 PowerQueryでデーター取り出した EXCELファイルの保存です。

③EXCEL→ACCESS ACCESSの機能、EXCELからデーター連携機能 を利用する

④SQL PowerQueryのEXCELから取り込んだデーター 4．1 情シス管理OU内のデーターから組織図をつくるSQL 4．2 サブ組織管理ごとのアクス権グループとグループ所属ユーザー一覧を作るSQL 4．3 4．2の集合 と 4．1の集合 をユーザーIDで左外結合するSQL

⑤Sharepointリストへのアップロード ACCESSの機能、Sharepointリストからデー ター連携機能を利用する

⑤Sharepointリストへのアップロード ACCESSと連携したSharepointリストは ACCESSのテーブルの1つと同様に扱える 参照、削除、更新、追加のSQLでデーター 操作可能

⑥ファイル保存 ACCESSファイルの保存

⑦Sharepointリストのユーザー利用 Excelにエクスポートアイコンを クリックするとiqyファイルがダ ウンロードされ、EXCELと Sharepointリストが連携した EXCELファイルが出来上がりま す Sharepointリストを利用して PowerAppsを作成することも可 能です。 ユーザー毎にそのユーザーが 所属しているアクセス権グルー プ一覧PowerApps

まとめ Microsoft365とofficeアプリは親和性が高いです ノンコードでデーター連係が可能です ◦ PowerQuery ◦ 外部データー連携機能

まとめ PowerQueryはさまざまなデーターとの連係ができます 1つのカラムの分解（ピボット解除）の機能があり、第一正規形への変換もGUIで構築できます ただメモリーを食う ユーザー領域ディスクの空き容量に注意

さいごに 調べて、３営業日くらいで作れました できたと思ったら、ACCESSとSharepointリストの連携できなくなったりトラブルはありましたが GUIだけでここまで簡単にできるとは驚きです 見える化して けしからん奴等には 怨送り 見える化して 性善説が成り立つ（情シスに協力的な）ユーザーには 恩送り