タグ別アーカイブ: 関数
TOCOL関数とTOROW関数で縦横を変換
【実は関数１つで複数データの並びを変えることができるようになりました】
エクセルで行列を入れ替える作業を伴う転記作業はなにより苦痛なものです
関数１つで複数の値の行列を入れ替えられるのはとてもありがたいです
内容もとても簡単なものです
第二引数や第三引数を指定するパターンがあるので、そこだけは複雑ですがぜひ押さえておきましょう！
＊注意点
既にTRANSPOSE関数という行列入れ替え、縦横入れ替えるという関数があります
今回は幅広い意味で記事内で「行列入れ替え、縦横入れ替え」については表現させて頂いております。明確な違いとしては、今回紹介するTOCOL関数とTOROW関数は1列もしくは1行に入れ替えます。ご理解のほど何卒よろしくお願いします
TOCOL/列に変換
第二引数無
TOCOL関数の中に行列を入れ替えたい範囲を指定します
=TOCOL(D5:F8)
TOCOL関数により、指定した範囲が1列に変換されます
読込順番は1行目⇒2行目⇒となります
値が無いセルについては０が出力されます
指定範囲の最終行まで繰り返し処理をしているのもポイントです
第二引数を１で指定
第二引数を１で指定すると空欄は無視して出力されます
更に第三引数をTRUEで指定
第三引数を指定すると、読み込みの方向が横方向から縦方向に変ります
TOROW/列に変換
第二引数無
TOROW関数の中に行列を入れ替えたい範囲を指定します
=TOROW(D26:F29)
TOROW関数により、指定した範囲が1行に変換されます
今回は第二引数を指定してないので、空欄は０で出力されます
第二引数を１で指定
第二引数を１で指定すると空欄は無視して出力されます
これはTOCOL関数と同様です
更に第三引数をTRUEで指定
TOCOL関数と同様に、三引数をTRUEで指定すると、データを横方向ではなく縦方向に読み込みます
＜まとめ＞
今回は行列を入れ替えるTOCOL関数、TOROW関数を解説しました
従来はVBAでないと最終行まで繰り返すような処理が行えなかったので、本当便利になったものです
また後日、詳細を解説しますが今回のような複数出力する関数は他の関数と組み合わせると、もっと威力を発揮します
ぜひ続編をお楽しみにしていてください
参考までに実際のデータも添付させて頂きます
GroupBy関数を使用をして集約したテーブルをキャンバスに表示する
【テーブルの内容を集約したGalleryを作成しよう！】
Power Appsのキャンバスに、集約したテーブルを表示したい時にはGroupBy関数を使用します
とても簡単なのでぜひ覚えておきましょう！
使用データ
今回使用するのはSharepointリストです
商品や客先別に売上金額が表示されています
1階層での集計
GroupBY関数の文法は次の通りとなります
GroupBy(テーブル名,①列名,②新列名)
①の列名でグループ化の切り口となる列を指定します
②の新列名で集約された内容の表示の仕方を指定します
最初は②の扱いが少し分かりにくいかもしれません
さて、
このGroupBy関数は、下の画像のようにGalleryのItemsで使用します
前述の②の新列名はDATAで指定しています
こちらにSales列を含む「Title」以外の集計される列が含まれる形になります
実はここからが少し工夫が必要となります
①のグループ化の切り口の列をギャラリーに配置するのは、通常と一緒です
ここからTitle別にSalesを集計したいのですが、ここからは少し特殊な方法となります
上の画像のように、②新列名を経由してSales列を指定します
2階層での集計
前述の1階層の集計にもう一つ階層を追加する場合には、GroupBy関数でもう一つ列を追加します
これで新列名の「DATA」が2階層で集計されます
＜まとめ＞
今回はGroupBy関数を使用して、ピボットテーブルのような集計表をPower Appsのキャンバスに追加する方法を解説しました
ポイントはGroupBy関数をGalleryのItemsで設定した後に、ギャラリー内でGroupBy関数内で新列名にて指定した内容を設定する時です
この場合は、新列名を経由して該当の列を指定します
更なる問題としては、この新列名で設定した内容で並び替えを行う時です
いきなりSortByColumns関数を使用しても、新列名では並び替えができません
この場合には、ボタンを使用してAddColumns関数にて新しい列を追加した方が早いです
変数を2回に分けて設定するのもポイントです
ClearCollect(
myData1,GroupBy(地域データ,"Title","DATA"));
ClearCollect(myData2,
AddColumns(myData1,"合計",Sum(DATA,Sales)
))
実際には、Itemsにて次の関数で並び替えを行います
SortByColumns(myData2,”合計”,SortOrder.Descending)
Collect関数を使用して画面内でデータを自由に扱う
【今回はデータのコレクション/一式を自由自在に画面内で取り扱えるようにします】
前回はPatch関数を使用して、既存のテーブルにデータの挿入や修正を行いました。今回は変数を組み合わせて画面内でデータのコレクションを新たに生み出して取り扱えるようにします
用意すること
挿入用一式
テキストボックス２つ：txtPrice,txtQuantityとボタン２つを準備します
ギャラリー
変数に格納されたデータ一式を一覧化するギャラリー:Gallery1を準備します
修正用一式
ギャラリーで選択された内容を修正するための一式についても、挿入用一式と同様に準備します
但し、ボタンは１つでいいです
Collect関数によるデータ一式の設定
Collect関数によりテーブルではなく、任意に設定する変数：myCollectにデータ一式を格納します
Collect関数の文法は次の通りです
Collect(変数,{変数内の列1:設定したい値1,変数内の列2:設定したい値2})
今回は変数：myCollect内のmyPriceとmyQuantity列にテキストボックスに設定した値を設定します
Collect(myCollect,{myPrice:txtPrice.Text,myQuantity:txtQuantity.Text})
こちらの関数をボタンの「OnSelect」に設定します
*上記は文字列形式での設定になります。数値形式での設定はValue関数を使用します（myPrice:Value(txtPrice.Text)）
事前にギャラリーのItemsには「変数：myCollect」を設定しておきます
これでギャラリーに対して、ボタン一つでデータ一式を挿入する仕組みができました
ギャラリーに設定したデータ一式を一括で消去するには「Clear関数」を使用します
ギャラリーの削除ボタン
Remove関数を使用することで、選択行を削除する機能を作成することができます
ゴミ箱ボタンの「OnSelect」に次の数式を入れることでこの機能を実現できます
Remove(myCollect,ThisItem)
選択行の意味合いで「ThisItem」を使用するのがポイントです
Patch関数によるデータ一式の修正
Patch関数による修正の仕組みを作成するのですが、その前に一工夫を行います
ギャラリーで選択された行の内容が一式に反映されるようにします
テキストボックスの「Default」を「Gallery1.Selected.変数の列名」にします
こうすることで、ギャラリーの内容が反映されます
Patch関数については、ボタンの「OnSelect」に次のように設定します
Patch(myCollect,Gallery1.Selected,{myPrice:txtPrice_1.Text,myQuantity:txtQuantity_1.Text})
2番目の引数、対象データをギャラリーの選択行にするのがポイントです
修正用一式の表示を切り替える
ギャラリーの行を選択した時⇒表示、修正ボタン⇒非表示にする仕組みを解説します
こちらは、変数の値をtrueかfalseにすることで調整します
まず、変数の設定を始める前に修正一式をグループ化します
グループ化は一式を選択した後に、下の画像の「ボタン」を押せばできます
グループ化した内容を選択した上で、Visibleを選択してください
通常はtrueが設定されています
今回は該当のグループのVisibleに変数：showUpを設定します
これで変数：showUpを通じて、表示の切り替えを行う準備ができました
次にギャラリーの「OnSelect」にSet関数を設定します
Set(showUp,true)
これで、変数：showUpがtrueになるので修正用一式が表示されます
一方、前述のようにグループの非表示化は修正ボタンでおこないます
こちらに「変数をfalseに設定するSet関数」を設定します
＜まとめ＞
今回はCollect関数を使用して画面内でデータ一式を追加、修正する方法を解説しました
Collect関数以外にもClear関数、Remove関数、Patch関数も活用しました
これらの関数を組み合わせれば、データの扱いに関してはかなりの事が行えるようになります
ぜひマスターしておきましょう！
IT未経験からDX推進！
私は30代半ばからの10年間、朝から終電まで退屈なエクセル作業をして過ごしましました。それからシステム会社に45歳で転職し、RPAと出会いました。
ITの世界の常識が大きく変わる予感がしました。業務を理解している担当者自身が、システムエンジニアの力を借りずに、システム開発を効率的に推進していくことができるのではと考えました。
そこから更にPower Queryなどの「モダンエクセル」、Power BIやPower Automate、Power Appsなどの「Power Platform」が登場し、誰もが手軽にデータを有効活用できる世の中が来ることを確信しました。
「このエクセル作業が効率化できたらいいのに・・・」「このエクセルデータから有効な情報を引き出せたらいいのに・・・」と頭を悩ませている人は数多くいらっしゃいます
IT未経験者でもモダンエクエル、Power Platformを正しく活用すれば、自身の退屈なエクセル業務を削減しつつ、データを活用した有意義なビジネスライフが送れるようになります。
私自身は最初はITの世界に飛び込んだ時は用語さえわからず、相当苦労しました。この時に味わった苦労の一つ一つがこれからエクセル作業を改善していこうとしている皆様のお役に立つと思います。
ところで、皆様はブルース・リーをご存知でしょうか？
ブルース・リーはカンフーの神様、先駆者と呼ばれ、ハリウッドで大活躍し、未だに多くのハリウッドスターに尊敬されている香港生まれのアクションスターです
そのブルース・リーがこんな言葉を残したそうです。
「Don’t think.Feel!（考えるより、まずは感じること）」
ぜひ一緒に手を動かしてITを楽しんでいきましょう！
Excellentなレッスン
ストアカにてレッスンを定期的に行っています
「Don’t think.Feel!（考えるより、まずは感じること）」をモットーに丁寧に解説を行います
オンラインに加えて対面レッスンも行っています
🌟ここまでできるPower BI～計算、分析、操作、魅せる～
Power BIの脱初心者になりたい人向け。動くグラフは必見です！
＊ブログ読者優待割引あり、紹介記事はこちら⇒詳細
🌟Power BIをはじよう～マンツーマン/対面・オンライン両方可
これからPower BIを始めたい人向け
30分からの時間制講座です。＊内容・日時等を事前に問合せをお願いします（提供が可能でないこともあります）
新着情報
人気記事
オンライン化に対応した未来を創ります！
【新たなオンラインWEBスクール「HINT」様と新たな「学び」を提供します！】
コロナ禍がもたらしたもの
新型コロナウイルス感染症が日本で最初に発見されたのは、2020年1月15日だそうです
このコロナ禍により、我々の生活は大きく変化しました
我々の生活の主体は「リアル」から「オンライン」主体に移行しました
ネットショッピングを主体とした巣ごもり消費が浸透するとともに、仕事の主戦場が「オフィス」から「リモート」に移行しました
つまり、我々の生活・仕事はリアルへの対応からオンライン仕様への変化が求められているのです
HINTのサービス
「好きな時間に」「学びたい内容だけピンポイントで」「大人数ではなくマンツーマンで」
*運営会社：株式会社DEX
HINTは「学び」についても、ネットショッピングのように手軽に「オンラインで」行えることを目指した新サービスです
Excellent仕事術では「オンラインで学習するメリット」を追求したこのサービスに参画し、「仕事のオンライン化に対応したエクセル術」の普及を進めます！
オンラインに対応したエクセル術
オンラインに仕事の主戦場も移行したことにより、個人の仕事の仕方も大きな変更がもたらされています
会話や人間の経験、勘よりも、データがより重視されるようになりました
個人が扱うデータ量が増え、データの説明もペーパーではなく、画面を利用して説明する機会が増えました
一方で、これまでであれば職場でデータの扱いに詳しい人に聞いたり、IT部門に相談して解決できたようなこともなかなか解決が難しくなっています
Excellent仕事術では「仕事のオンライン化」に対応した新たなエクセル術を、新たなオンラインサービスのHINTを通じて提供します！
・エクセル新機能/パワークエリ x データベース理論 ➡ 詳細
ITのプロの様に、大量のデータを効率良く扱えるようにする「お得な2時間」のレッスンです！
・エクセルダッシュボード ➡ 詳細
レーシングカーのコクピットにまるで座っているかのように、1画面で組織に必要な数字を魅せるテクニックです！画面内容をボタン一つで切り替えられるので、オンラインでの数字説明に最適です
・関数術 ➡ 詳細
関数が苦手な人は、関数独自の「同時に複数の参照」が実感できていません
この同時参照の動きをエクセルシート上で動くアニメで実感できるようにします
新たな未来を創ろう！
コロナ禍がもたらしたのは、実は生活の変化だけではありません
オンラインの浸透により「時間」の感覚が変わってきています
1年前の常識は通用せず、変化に対応していかないとどんどん取り残されていきます
「思い立ったが吉日」
もし、この記事を読んで少しでも「響く」ものがあったらぜひご連絡をください！
初心者から上級者まで幅広く対応させて頂きます！
事前問合せは無料です！
新たな未来創りに向け、ぜひお力にならせてください！！
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関数を使って関数を二次元で使うことができるということです
興味のある方はそちらの記事も参照してみてください
UNIQUE関数xFILTER関数から重複無リスト抽出～条件一致～
FILTER関数やUNIQUE関数などの新関数の醍醐味は「関数同士の組み合わせ」にあります！
今回は FILTER関数とUNIQUE関数 の組み合わせにより、ある表から条件に合う重複が無いリストを作成する方法を解説します
そして、最後はUNIQUE関数ｘFILTER関数の組み合わせを応用した内容も解説します
関数の指定範囲に空欄が含まれると、下の画像のように出力結果に「０」が含まれてしまいますので、０を含まれないようにする方法も解説します
目次
条件に合う重複無リスト作成
応用編
＜まとめ＞
条件に合う重複無リスト作成
今回は、下の画像の左にある表から「70点未満の得点を一度でも取った人」リストを重複が無いように作成します
抽出元の表には、田中さんや佐藤さんのように同じ名前の人のデータが複数含まれています
まずは設定条件/<70に沿ったFILTER関数を作成してみましょう
FILTER関数の書き方は次の通りです
=FILTER(抽出範囲,条件)
上の書き方を今回のやりたい事に当てはめると次の画像の通りになります
上の画像では、まだFILTER関数しか入力していないので、リストに佐藤さんの名前が複数回登場しています
ですので、FILTER関数にUNIQUE関数を下の画像のように組み合わせます
これで、佐藤さんの登場は1回のみになりました
各種集計/合計から四捨五入まで～上級編4回目
こんにちは、Excellent仕事術のガッツ鶴岡です
エクセルは表計算ソフトなので、エクセル作業に集計処理はつきものです
必要な関数を調べて入力したり、ピボットテーブルにして処理したりする作業はちょっとガッツが必要なものです
Power Queryでは、様々な切り口による集計処理を直感的なクリック操作で行えるのが、大きなメリットです
Power Queryエディター内の「変換タブ」と「列の追加タブ」では、集計処理のためのメニューが豊富に揃っています
今回の解説では、上の画像の左のメニューから順にポイントを絞って解説します
尚、三角関数と情報メニューについては、解説を今回は割愛させて頂きます（指数メニューについても概要だけの解説になります）
今回、解説する上で分かりにくい点が1点あります
変換タブと列の追加タブには、統計以外は同じメニューがあります
違う点は、集計した列を追加するかどうかだけです
今回の解説では、極力、変換タブに絞って解説を行います
目次
１．統計処理メニュー
こちらのメニューは列単位での集計処理メニューになります
「列単位での集計処理」という意味は、下の画像を例にとれば「販売個数の列」を行方向に一括で集計するという意味です
ですから、集計した値は１つだけ出力されます
統計ボタンの右下にある▼マークをクリックすると、様々な集計切り口が出てきます
今回は、合計だけ出力してみます
集計する列をカーソルで指定し「合計」を押します
すると、列にある値を合計した値「1011」が出力されました
ちなみに、Power Queryコラムでも書きましたが、こちらの統計処理メニューには、「個別の値のアカウント」という集計方法があります
こちらは、列内にある重複しないリスト数を集計するという優れた機能です
従来のエクセルの使い方であれば、複数回のステップが必要な集計処理でした
「個別の値のアカウント」について興味のある方は、こちらの記事もぜひご参照ください
カスタム関数を自身で登録して使い回す～上級編8回目
資料を作成する度に、毎回毎回、同じ数式を入力していませんか？Power Queryではカスタム関数という仕組みにより、事前登録したカスタム関数を呼び出して使い回していくことができます
今回とあわせて2回に分けて、カスタム関数についての解説を行います
私が昔、企画の仕事をしている時は、会議の前日は必ず数値資料を作成していました
「前期比」「利益率」「単価」などを資料に盛り込むわけですが、毎回決まった数式を表に入力していました
毎回、同じ数式を入力するので体で覚えてしまったことをよく覚えています
この記事を見て下さっている読者の方の中にも似たような経験のある方がいらっしゃるのでないでしょうか？
カスタム関数をマスターして、「同じ数式の再入力」は自身で事前作成した「カスタム関数」の呼出し処理だけで代用できるようにしましょう！
本格的な解説に入る前に、「今回のポイント」と「今回使用するデータと、作成するカスタム関数」について解説を行います
１．今回のポイント
中学数学の方程式
中学時代にこんな問題を数学の時間に出された経験はありませんか？
Y=aX+bの式に（X=2,Y=5)を代入するといった問題です
今回のカスタム関数を作成する時にも、同じような考え方をします
ただ、今回は代入するのが「列の名前」になります
空のクエリの作成
今回はA.の方程式を、空のクエリを作成してから指定します
「空のクエリ」はこれまでのPower Queryの解説では出てこなかった方法です
更に空のクエリを作成した後、Power Queryエディター画面から詳細エディター画面を開き、下の画像の「let」と「in」の箇所に方程式を指定します
２．今回の使用データと作成内容
今回のポイントを2点解説したので、次に今回使用するデータと作成するカスタム関数について解説します
今回使用するデータは、次の画像にあるデータになります
今回作成するカスタム関数は、黄色く塗られた列の値を使用して、下の画像の利益を計算する関数になります
ちなみに、上の画像の「利益」の列には以下の数式が入力されています
使用するデータと作成するカスタム関数について解説したところで、本格的な解説に入ります
３．空のクエリ作成
データタブの「データの取得」から「その他のデータソースから」を開きます
開いた一覧の一番下に「空のクエリ」があります
上の画像の「空のクエリ」をクリックすると、Power Queryエディターが開きます
次にPower Queryエディターのホームタブから「詳細エディター」を開きます
すると、次のような画面が開きます
上の画像の「Let」の下と「in」の下に、ポイントAで解説した方程式を入力していきます
＜まとめ＞
今回は、カスタム関数を作成するにあたってのポイントを2点解説した後、ポイントAで解説した、方程式を入力する画面を開くところまでを解説しました
詳細エディターの画面は少しややこしいところもありますが、方程式の作成の仕方をイメージしてもらえればOKです
では次回は、カスタム関数を完成させます！
