タグ別アーカイブ: エクセル
上級編16-17回
INDIRECT関数を使いこなす！
5百あるエクセル関数の中でも、「ややこしい」が「使い勝手があり」「個性が強い」のがINDIRECT関数です
「INDIRECT関数って何ができるの？」と聞かれたら、少し返答に困ってしまいます
でも、確実に言えるのはこの関数は使えるということです！
今回は、INDIRECT関数の世界を覗いてみましょう！
基本形
今、下の画像のようにD2セルに「おやすみなさい」と入力されているとします
そして、B２セルには「D2」と「おやすみなさい」の文字が入力されているセルの名称が入力されています
上のGIF画像のようにINDIRECT関数の引数としてB2セルを指定すると、D2セルの内容が出力されます
名前参照
今度は、下の画像のようにD2セルに「挨拶」と名前を付けます
B3セルには「挨拶」の文字が既に入力されています
では、基本形と同じ様にようにINDIRECT関数の引数にB3セルを指定します
繰返しになりますが、「おはようございます」の挨拶が入力されたD2セルは「挨拶」という名前が設定されています
すると、下の画像のようにD2セルの内容が基本形と同じ様に出力されます
画像の抽出
INDIRECT関数をうまく使えば、画像の抽出も行えます
上の画像のように、東京タワーや自由の女神などの画像が入ったセルに名前をつけておきます
この画像をカメラで別な場所に映しだすと、下の画像のように数式バーでは「画像の入ったセル」を参照しています
なお、上の画像は実際にはトリミングを行っています
こちらの数式バーを先ほど名前をつけた「アメリカ」に変えても画像は同じです
では、名前と画像の関係をお話したので、前述のINDIRECT関数の仕組みを使用して画像を自由に変えられるようにしてみましょう
今回は、画像という名前の中にINDIRECT関数を設定します
下の画像のように「画像」という名前にて、上の画像のF1セルをINDIRECT関数を通じて参照するように設定します
こうしておけば、INDIRECT関数を通じて、F1セルに表示された名前の画像をカメラ機能で表示することができます
ちなみに、セルは絶対参照にしておきましょう！（セルの位置がズレていくことがあります）
＜まとめ＞
いかがだったでしょうか？INDIRECT関数の面白さを体感して頂けたと思います
今回は、最後、画像の抽出の事例を紹介しましたが、INDIRECT関数を使用してVLOOKUP関数の参照範囲を変えることもできます
つまり、INDIRECT関数を使って関数を二次元で使うことができるということです
興味のある方はそちらの記事も参照してみてください
7月17日勉強会_報告
先週の7月17日にモダンエクセルの勉強会を行いました
多くの皆さんにご出席頂き、誠にありがとうございました
①主催者スピーチ：M言語と分析術について
M言語を活用した操作にて、顧客の2回目のリピート率を簡単に集計する方法を解説しました
②パワークエリ・高速化術
今回は先日、Akira Takaoさんからご紹介いただいたTable.Addkeyを簡単に解説しました
③質疑応答
勉強会後、参加者の方から「パワークエリやM言語は使いこなせば便利なのはわかっているけど、皆さん試行錯誤されているのですね」という声を頂きました
やはりモダンエクセルはまだ歴史が浅いので、操作方法等ががまだ浸透していないところがあります
例に挙げれば、今回も話題に出た「パワークエリとxls型式が相性が悪いため、xlsの型式のファイルをxlsx形式に保存し直す必要性」等です
勉強会の意義は、そんな実際に活用してみての気づきが共有できる点だと思います
今後も継続して開催していきます！
テーブル列操作関数
目次
列結合
文法
キー列を基にして２つのテーブルの内容を結合します
=Table.NestedJoin(テーブル名1,キー,テーブル名2,キー,新しい列名,省略可|結合タイプ)
*結合タイプが省略された場合には、左外部結合が指定される
列名をリストとして返す
文法
ハードコード/自動記録された列名リストをTable.ColumnNamesで置き換えると、列が増えても列名のリストが網羅される
実際の使用例
列変換（研究中）
文法
7月10日にモダンエクセルの勉強会を開催しました
7月10日にモダンエクセルの勉強会を、Akira Takaoさんをはじめ20名ほどの方に参加頂いて開催しました
当日の飛び入りでのプレゼンもあり、なかなか有意義な会になりました
参加頂いた皆さん、誠にありがとうございました
KTさんスピーチ
KTさんには「フォルダの複数データ集計のノウハウ」についてスピーチをして頂きました
KTさんが社内のExcel塾で講義されている内容は、単なる技術の解説に留まらず、現場で活用してもらうための工夫が緻密に盛り込まれていて、とても勉強になりました。１つの技術を簡単に始められるA方式と中級者向けのB方式に分ける方法は、これから社内で勉強会を行おうとしている人にはとても参考になると思います
参加者の声
・私も、企画部当時、膨大な費用データ分析に苦労した経験がありました。その時に、パワークエリーがあれば、引き継ぎ等が十分できたと思います。また、社内への展開ここまで進むと大きな無形と思います。
・プレゼンの中でPQ勉強会の後に実務に使っているのは3割程度というお話があったと思います。それを考えると、やはり普及させるためには単純に数を増やすことが重要だと思うので、私も普及に向けて何らかの貢献をしていきたいと思います
Keitaroさんスピーチ
Keitaroさんには「丸の内界隈でのモダンエクセルの活用について」をスピーチして頂きました
「興味」「共感」「体験」の3点を重視してリーダーシップを発揮し、職場にモダンエクセルを浸透させた話はとても迫力がありました。そしてモダンエクセルを広めるための大いなる示唆も得た気がします！
参加者の声
・使い始めた人が躓きそうなポイントやノウハウが入った、また、社内普及へのコツが入ったとても素晴らしいをしていただき、大変感謝いたします
・メンバーにも浸透させていることがすごいと感じました。今、私の経理Gでも、理解できそうなメンバーがいるので、一緒に勉強しています。Keitaroさんの教材を参考に、他のメンバーの育成をしたいと思います
質疑応答
参加者の一人に飛び入りで「モダンエクセルとVBAの連携事例」についてプレゼンをして頂きました
やはりモダンエクセルの使用を突き詰めていくと、VBAとの連携も必要になってくることを考えると、なかなか面白い話でした
後で、Twitterでプレゼン内容のネタ元を共有して頂いたので参考までに、この記事内でも共有します
Power Queryの更新が完了するのを待つ方法の（2#の解決策） https://fixes.pub/program/258924.html…
VBAでPowerQueryのデータソースをいじる https://aoiue.website/archives/2967
さて、次の開催は7月17日の19時半からです
皆さんの参加をお待ちしております
モダンエクセル・勉強会_0717
7月10日（土）に引き続き「モダンエクセルを実際に使いこなしている方々」にお集まり頂き、「モダンエクセル活用の場を広げる」ための意見交換をしたいと思います！
・主催：Excellent仕事術/当ブログ管理人
・日時：2021年7月17日19時半～20時半（今回は60分です）
・場所：オンライン（ZOOM）
・内容：①主催者発表/約20分、②意見交換及び質疑応答
①主催者発表：パワークエリ/M言語を使用した分析術について
・費用：参加費用は無料です
・申込：以下参照
-記事下のフォームに記入して頂いた後に、ZOOMのURLを送ります
*すぐにURLを送れない事もありますので、その点はご容赦ください
– 先着20名まで
-参加者はモダンエクセルを使いこなしている方に限定させて頂きます
-勉強会当日は、基本的にマイクはオフにして頂きます（オンにする際は、こちらから指示します）
*録画はご遠慮願います、後、途中の入退出もご遠慮ください（やむを得ない事情が発生した場合には主催者にご連絡ください）
*基本は顔出しでお願いします
-頂いた個人情報について、今回開催する勉強会の連絡等の目的には使用せず、厳重に管理致します
参加申し込みの程、お待ちしております
尚、勉強会に関する問い合わせはこちらからお願いします
クエリの高速化・関数
キー設定
文法
マージを行う際に、主キーを設定してクエリ処理を高速化します
＊明細側（多）より集計側（１）で高速化の効果が高いです
Table.AddKey(テーブル名, 主キーにする列, 主キー有無)
使用方法
let
With.Addkey1=Table.AddKey(売上台帳,{“商品コード”},true),
With.Addkey2=Table.AddKey(商品台帳,{“商品コード”},true),
ソース = 売上台帳,
マージされたクエリ数 = Table.NestedJoin(With.Addkey1, {“商品コード”}, With.Addkey2, {“商品コード”}, “商品台帳”, JoinKind.LeftOuter),
#”展開された 商品台帳” = Table.ExpandTableColumn(マージされたクエリ数, “商品台帳”, {“販売単価”}, {“商品台帳.販売単価”}),
挿入された乗算 = Table.AddColumn(#”展開された 商品台帳”, “乗算”, each [商品台帳.販売単価] * [販売個数], Int64.Type),
#”名前が変更された列 ” = Table.RenameColumns(挿入された乗算,{{“乗算”, “販売金額”}})
in
#”名前が変更された列 “
バッファー化
マージ対象のクエリをバッファー化します
文法
=Table.Buffer(最終ステップ名)
ファイル関連操作・関数
操作ファイル内のテーブル取得
文法
ファイル内のテーブルを一括で取得します
=Excel.CurrentWorkbook
実際の使用例
フォルダー内データ取得
文法
フォルダ内にある複数ファイルのデータを一括取得
=Folder.Files(フォルダ名)
実際の使用例
ファイル内のデータ取得
文法
=Excel.Workbook(ファイル名,省略可|1行目をヘッダーにする,省略可|データ形式を自動変換)
上の画像/使用結果、ではExcel.Workbookの2つ目の関数をTRUEで指定している為、自動的に1行目がヘッダーになっている
2つ目の引数を省略、もしくはFALSEにした場合は次の画像の通りになる
ファイルパスを返す
文法
ファイルパスとして書かれたテキストをバイナリにして返す
日付時刻・関数
日付作成/年、月、日
文法
３つの数字（年、月、日）から日付を作成
=#date(年,月,日)
実際の使用例
➡【M言語に慣れる】_13回_ダイナミックに連続した日付作成
期間値作成
文法
日、時間、分、秒の４つの数字から期間値を作成
=#duration(日,時間,分,秒)
実際の使用例
➡【M言語に慣れる】_13回_ダイナミックに連続した日付作成
日付リスト作成
文法
日付リストを指定回数分、指定期間単位で作成
=List.Dates(開始日,指定リスト数,期間単位)
実際の使用例