タグ別アーカイブ: エクセル

INDIRECT関数を使いこなす!

5百あるエクセル関数の中でも、「ややこしい」が「使い勝手があり」「個性が強い」のがINDIRECT関数です

「INDIRECT関数って何ができるの?」と聞かれたら、少し返答に困ってしまいます

でも、確実に言えるのはこの関数は使えるということです!

今回は、INDIRECT関数の世界を覗いてみましょう!

基本形

今、下の画像のようにD2セルに「おやすみなさい」と入力されているとします

そして、B2セルには「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回目のリピート率を簡単に集計する方法を解説しました

アイコン

0717勉強会資料 839.14 KB 11 downloads

...

②パワークエリ・高速化術

今回は先日、Akira Takaoさんからご紹介いただいたTable.Addkeyを簡単に解説しました

③質疑応答

 勉強会後、参加者の方から「パワークエリやM言語は使いこなせば便利なのはわかっているけど、皆さん試行錯誤されているのですね」という声を頂きました

やはりモダンエクセルはまだ歴史が浅いので、操作方法等ががまだ浸透していないところがあります

 例に挙げれば、今回も話題に出た「パワークエリとxls型式が相性が悪いため、xlsの型式のファイルをxlsx形式に保存し直す必要性」等です

勉強会の意義は、そんな実際に活用してみての気づきが共有できる点だと思います

今後も継続して開催していきます!


にほんブログ村

テーブル列操作関数

列結合

Table.NestedJoin

文法

キー列を基にして2つのテーブルの内容を結合します

=Table.NestedJoin(テーブル名1,キー,テーブル名2,キー,新しい列名,省略可|結合タイプ)

*結合タイプが省略された場合には、左外部結合が指定される

使用方法

列名をリストとして返す

Table.ColumnNames

文法

ハードコード/自動記録された列名リストをTable.ColumnNamesで置き換えると、列が増えても列名のリストが網羅される

使用前
使用後

実際の使用例

 ➡【豆知識】ピポット解除時の空欄の扱い

列変換(研究中)

Table.TransformColumns

文法

元データ
Table.TransformColumns
列変換後


にほんブログ村

7月10日にモダンエクセルの勉強会を開催しました

7月10日にモダンエクセルの勉強会をAkira Takaoさんをはじめ20名ほどの方に参加頂いて開催しました

当日の飛び入りでのプレゼンもあり、なかなか有意義な会になりました

参加頂いた皆さん、誠にありがとうございました

KTさんスピーチ

KTさんには「フォルダの複数データ集計のノウハウ」についてスピーチをして頂きました

 KTさんが社内のExcel塾で講義されている内容は、単なる技術の解説に留まらず、現場で活用してもらうための工夫が緻密に盛り込まれていて、とても勉強になりました。1つの技術を簡単に始められるA方式と中級者向けのB方式に分ける方法は、これから社内で勉強会を行おうとしている人にはとても参考になると思います

アイコン

KTさん発表資料 1.61 MB 13 downloads

...

参加者の声

・私も、企画部当時、膨大な費用データ分析に苦労した経験がありました。その時に、パワークエリーがあれば、引き継ぎ等が十分できたと思います。また、社内への展開ここまで進むと大きな無形と思います。

・プレゼンの中でPQ勉強会の後に実務に使っているのは3割程度というお話があったと思います。それを考えると、やはり普及させるためには単純に数を増やすことが重要だと思うので、私も普及に向けて何らかの貢献をしていきたいと思います

Keitaroさんスピーチ

Keitaroさんには「丸の内界隈でのモダンエクセルの活用について」をスピーチして頂きました

興味」「共感」「体験」の3点を重視してリーダーシップを発揮し、職場にモダンエクセルを浸透させた話はとても迫力がありました。そしてモダンエクセルを広めるための大いなる示唆も得た気がします!

アイコン

Keitaro 14.52 MB 9 downloads

...

参加者の声

・使い始めた人が躓きそうなポイントやノウハウが入った、また、社内普及へのコツが入ったとても素晴らしいをしていただき、大変感謝いたします

・メンバーにも浸透させていることがすごいと感じました。今、私の経理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

文法

マージを行う際に、主キーを設定してクエリ処理を高速化します

*明細側(多)より集計側(1)で高速化の効果が高いです

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

文法

ファイル内のテーブルを一括で取得します

=Excel.CurrentWorkbook

ファイル内テーブル
使用方法

実際の使用例

 ➡M言語に慣れる_9回目~複数シートをコード1行で結合~

フォルダー内データ取得

Folder.Files

文法

フォルダ内にある複数ファイルのデータを一括取得

=Folder.Files(フォルダ名)

使用方法

実際の使用例

 ➡フォルダから複数ファイルデータを一括取得~初級講座4回目

ファイル内のデータ取得

Excel.Workbook

文法

=Excel.Workbook(ファイル名,省略可|1行目をヘッダーにする,省略可|データ形式を自動変換)

Content列
使用方法
使用結果

上の画像/使用結果、ではExcel.Workbookの2つ目の関数をTRUEで指定している為、自動的に1行目がヘッダーになっている

2つ目の引数を省略、もしくはFALSEにした場合は次の画像の通りになる

2つ目の引数を省略した場合(もしくはFALSEにした場合)

ファイルパスを返す

File.Contents

文法

ファイルパスとして書かれたテキストをバイナリにして返す

使用方法

にほんブログ村

日付時刻・関数

日付作成/年、月、日

#date

文法

3つの数字(年、月、日)から日付を作成

=#date(年,月,日)

使用方法

実際の使用例

 ➡【M言語に慣れる】_13回_ダイナミックに連続した日付作成

期間値作成

#duration

文法

日、時間、分、秒の4つの数字から期間値を作成

=#duration(日,時間,分,秒)

使用方法

実際の使用例

 ➡【M言語に慣れる】_13回_ダイナミックに連続した日付作成

日付リスト作成

List.Dates

文法

日付リストを指定回数分、指定期間単位で作成

=List.Dates(開始日,指定リスト数,期間単位)

使用方法

実際の使用例

 ➡【M言語に慣れる】_13回_ダイナミックに連続した日付作成


にほんブログ村