タグ別アーカイブ: 関数
エクセル新体験
エクセルをDXの主役に！
モダンエクセルの必要性
読者の皆さんは使い慣れたエクセルで、データを有効活用していけるのが一番効率がいいと思っていませんか？
どんな大企業でも、システム部門が管理できないエクセルファイルが無数に存在するものです
エクセルで行う業務については、ユーザー自身で改革を進める必要があります
何より、エクセルはとても便利です！
エクセルはデータを手軽に活用する為のツールが揃っており、しかも低コストです
但し、エクセルにてデータの有効活用を進める上で、1点だけ問題があります
それは、「データの一元管理」の問題です
この問題は長年、エクセルユーザーの頭を悩ませてきました
ところが、新たに登場したモダンエクセルの仕組みを活用すれば、エクセルによるデータの一元管理の問題はクリアできるのです！
モダンエクセルの仕組みを有効活用して、「エクセルでDXを進めていこう！」これがこのブログのメインテーマです！
モダンエクセルとは？
DXの基本は「データの一元管理」です
そして、一元管理したデータを「見える化」した上で、「業務改善」「データ分析」に活かし、最終的には「新規ビジネスの創出」に活用できるのが理想です
エクセルではデータを視覚化するグラフが作成でき、業務を自動化するマクロも作成できます
そして、統計処理の機能もありますので、データ分析もできます
データを魅せるダッシュボードだって作成可能です！
ところが
エクセルで大量のデータを処理しようとすると、どうしても「シート間」にてデータのバケツリレーが発生するのです
このシート間での「データのバケツリレー」はファイル間にまで拡大し、最終的にはフォルダ間にまで拡大してしまいます
エクセルは本来は表計算ソフトであり、大量のデータを扱う、データベースソフトではありません
エクセルの1シートに入る行数も104万行の制約があり、シートを分けながら使用する前提で作成されているのです
しかし、
このバケツリレーの問題はモダンエクセルの登場で状況は変わりました
モダンエクセルでは、データの単位は「シート」ではなく「テーブル」になります
この「テーブル」は互いに結合可能な「ブロック」だと思ってください
このテーブルというブロックは、子供のブロック遊びのように、直感的なクリック操作にて結合が可能です！
しかも、このテーブルは「接続の作成のみ」という手法で管理することができます
実は、エクセルはシート別に複数作成されたXML型式のファイルで構成されています
これが、モダンエクセルの重要なポイントの一つです
エクセルのセルに入っているデータは表示されているだけであり、実はデータ自体は別の場所に保管されているのです
この「接続の作成のみ」というデータ管理手法により、従来のエクセルでは不可能だった「104万行」以上のデータを、1か所で管理できるのです
このモダンエクセルの仕組みを有効活用すれば、エクセルユーザーは「データの一元管理」の問題から解放されます
そして、メインツールを「エクセル」にしてDXを推進していくことが可能になります！
エクセル新体験
エクセルが誕生してから、20年以上の月日が経過しています!
エクセルの進歩は、モダンエクセルだけではありません
ガラケーに代わって、スマホが登場した時のような衝撃を受けるような機能が続々と登場しています
今では、スマホのようにアプリ/アドインもダウンロードすることができます
ところが、日本ではこれらのエクセルの新機能があまり紹介されていません
このブログでは、これらの新機能の素晴らしさを伝えていきます！
ところで、
読者の皆様はブルース・リーをご存知でしょうか？
ブルース・リーはカンフーの神様、先駆者と呼ばれ、ハリウッドで大活躍し、未だに多くのハリウッドスターに尊敬されている香港生まれのアクションスターです
そのブルース・リーがこんな言葉を残したそうです。
「Don’t think.Feel!（考えるより、まずは感じること）」
ぜひ、このブログでエクセル新機能の素晴らしさを体験してみてください！
目次
エクセル新機能一覧
モダンエクセル
モダンエクセルのメインは、Power Queryという技術になります！
多くのプログラミング言語学習において、まずは最初に「Hellow Word！」を表示するとこから体験します！
Arduinoなどのマイコン工作では「Lチカ」体験です
Power Queryでは、次のような「ETL」体験からスタートです！
Extract ➡ データの抽出
Transform ➡ データの変換
Load ➡ データの読込
まずは、1度「ETL」を体験してみませんか！
必ず、今までは感じなかった新たな発見があるはずです！
⇒コラム：PDFファイルをエクセルに変換するなどのテクニックの紹介
⇒M言語
エクセルダッシュボード
読者の方には、エクセルで数値資料を作成する機会が多い方もいらっしゃると思います。
実は、数字は見た目です。そして、第一印象で決まります。特に、一目で「数字が細かく詰まっている」と分かる資料はまともに見てもらえません。そして、その資料作成には、膨大な労力がかかってしまっています。
では、最小の労力で、最大の効果を発揮する資料を作成するにはどうしらいいのか？、その答えは、このダッシュボード術の中にあります。このダッシュボード術は、まるで車や飛行機のコクピットの中にいるような感覚で、一目で【数字を魅せる資料】を作成します。
このダッシュボード術は、VBAの技術はあまり必要としません。関数の使用も、ごく簡単な関数の組み合わせのみです。一番必要となるのは日本ではあまり紹介されたこなかった、新たなエクセル機能の活用です
グラフデザイン
人は見た目が9割と言いますが、グラフも同じです。このコーナーでは、伝わりやすいグラフデザインを手軽に作成できる手法を解説します。グラフデザインの種類は「スピードメーター」「温度計」「画像入り」など、実に様々なデザインを揃えています！
手っ取り早くテンプレートを使って、グラフを作成したいという方のために、テンプレートを集めたコーナーも設けています！
新ピボットテーブル術～レポート力向上～
ピボットテーブルはデータを抽出する為の機能として良く知られています。表は別に作成しておき、ピボットテーブルで抽出した数字を、別途作成しておいた表に転記するような使い方をよく目にします。
実は、ピボットテーブルから直接、魅力的な表作成を行うこともできるんです。下の画像のような、魅力的な表も、ピボットテーブルから直接作成できます。このコーナーでは、テーブル機能とあわせて、ピボットテーブル本来の力を活かした技術を紹介していきます。
ピボットテーブルから出力する表
エクセル裏技
エクセルには日本ではまだあまり知られていない機能が沢山あります。下のGIF画像をご覧ください。実はエクセルでタイマーも作れてしまうのです。しかも、ほんの数行のコードを書くだけで作成することができます。この位はまだ序の口です。このコーナーでは、隠れたエクセルの裏技をどんどん紹介していきます。そして、どんどん業務の省力化と、差別化を行っていきましょう！
⇒裏技
RPA
RPA（ロボティクス・プロセス・オートメーション）が急速にビジネスの現場に浸透してきています。
人間が行っていたエクセル作業をRPAが代わりに行ってくれるから⇒これからはあんまりエクセルの技術習得にそんなに力を入れなくていいや・・・そんな風に考えている方もいらっしゃるかと思います。
実はRPAの浸透により、逆にエクセルの存在感が増してきています。その理由は、“RPAはデジタルデータにより動く労働者”、という点にあります。デジタル労働者はデジタルデータがないと働きません。
多くの業務の現場では、デジタルデータは、エクセルにより作成されています。つまり、RPAをエクセルとどう連携するかが、RPA活用の鍵になっていきます。
このコーナーではこの”RPAとエクセルとの連携”をテーマに、各種連携手法を紹介していきます。⇒RPA
アプリ/アドイン
エクセルでもスマホのように、アプリ/アドインをダウンロードすることができます
ダウンロードできるアプリの内容は、グラフを自動作成するものから、長い数式を解読するものまで多岐に亘ります
ぜひ、下の記事を参照してご自身のニーズにあったものを見つけてみてください
⇒アプリ
エクセル分析
エクセルには、ソルバーをはじめとする優れた分析機能があります
ソルバーは、人間が手動で計算したら２、３日は要するようなシミュレーションを一瞬で終了してくれる優れものです
このブログでは、エクセルの便利機能を組み合わせて、「ABC分析を効率的に実施する方法」などの最新のエクセル分析術を紹介していきます！
エクセルと英語力
実は、英語力とエクセル力は相関関係にあります
エクセルを作成したのは、英語圏の企業です
エクセルを使う上で、英語の概念が必ず登場します
ですので、英語ができた方がエクセルの上達も早いです
そもそもエクセルと英語、どちらもビジネスをしていく上ではとても重要なスキルです
このブログでは、英語力の向上について貢献できるような記事をアップしていきます
UNIQUE関数xFILTER関数から重複無リスト抽出～条件一致～
FILTER関数やUNIQUE関数などの新関数の醍醐味は「関数同士の組み合わせ」にあります！
今回は FILTER関数とUNIQUE関数 の組み合わせにより、ある表から条件に合う重複が無いリストを作成する方法を解説します
そして、最後はUNIQUE関数ｘFILTER関数の組み合わせを応用した内容も解説します
条件に合う重複無リスト作成
今回は、下の画像の左にある表から「70点未満の得点を一度でも取った人」リストを重複が無いように作成します
抽出元の表には、田中さんや佐藤さんのように同じ名前の人のデータが複数含まれています
まずは設定条件/<70に沿ったFILTER関数を作成してみましょう
FILTER関数の書き方は次の通りです
=FILTER(抽出範囲,条件)
上の書き方を今回のやりたい事に当てはめると次の画像の通りになります
上の画像では、まだFILTER関数しか入力していないので、リストに佐藤さんの名前が複数回登場しています
ですので、FILTER関数にUNIQUE関数を下の画像のように組み合わせます
これで、佐藤さんの登場は1回のみになりました
応用編
仮に、下の画像のように指定範囲を空欄にした場合には、下の画像のように「０」が関数の抽出結果に含まれてしまいます
この時には、FILTER関数の設定条件に「AND条件」を加えます
このANDで追加される条件は「>0」です
この「AND条件」をFILTER関数に設定する場合には下の画像の黄色で囲まれた箇所のように「*」を使います
これで不必要な0が消えました
＜まとめ＞
今回は、UNIQUE関数とFILTER関数を組み合わせて、条件に合った、重複が無いリストを作成しました
この組み合わせを覚えると、今までピボットテーブル経由で取得していたデータが関数だけで取得できるのでとても便利です
ぜひ有効活用していきましょう！
後、FILTER関数の設定条件に「*」を使用してAND条件で抽出する方法もぜひ覚えておきましょう
最後に、「+」を使用するとOR条件にて抽出できる旨も付け加えておきます
各種集計/合計から四捨五入まで～上級編4回目
こんにちは、Excellent仕事術のガッツ鶴岡です
エクセルは表計算ソフトなので、エクセル作業に集計処理はつきものです
必要な関数を調べて入力したり、ピボットテーブルにして処理したりする作業はちょっとガッツが必要なものです
Power Queryでは、様々な切り口による集計処理を直感的なクリック操作で行えるのが、大きなメリットです
Power Queryエディター内の「変換タブ」と「列の追加タブ」では、集計処理のためのメニューが豊富に揃っています
今回の解説では、上の画像の左のメニューから順にポイントを絞って解説します
尚、三角関数と情報メニューについては、解説を今回は割愛させて頂きます（指数メニューについても概要だけの解説になります）
今回、解説する上で分かりにくい点が1点あります
変換タブと列の追加タブには、統計以外は同じメニューがあります
違う点は、集計した列を追加するかどうかだけです
今回の解説では、極力、変換タブに絞って解説を行います
目次
１．統計処理メニュー
こちらのメニューは列単位での集計処理メニューになります
「列単位での集計処理」という意味は、下の画像を例にとれば「販売個数の列」を行方向に一括で集計するという意味です
ですから、集計した値は１つだけ出力されます
統計ボタンの右下にある▼マークをクリックすると、様々な集計切り口が出てきます
今回は、合計だけ出力してみます
集計する列をカーソルで指定し「合計」を押します
すると、列にある値を合計した値「1011」が出力されました
ちなみに、Power Queryコラムでも書きましたが、こちらの統計処理メニューには、「個別の値のアカウント」という集計方法があります
こちらは、列内にある重複しないリスト数を集計するという優れた機能です
従来のエクセルの使い方であれば、複数回のステップが必要な集計処理でした
「個別の値のアカウント」について興味のある方は、こちらの記事もぜひご参照ください
２．標準メニュー
こちらのメニューでは2つの集計方法を紹介します
集計単位は指定した列の各行単位になります
①四則演算
今回は四則演算の内、「割り算」についてのみ解説します
実際に集計する前に、集計する列をコピーしておいて、集計結果を集計前と比較できるようにします
この列のコピー処理が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です
では次回は、カスタム関数を完成させます！
カスタム関数を自身で登録して使い回す2～上級編9回目
今回はPower Queryでカスタム関数を作成した後、実際に作成した関数を呼び出す方法を解説します
まずは、前回の内容の「振り返り」から行いたいと思います
前回は、カスタム関数を作成する際の「ポイント」と「作成画面」について解説しました
解説の対象となる、これから完成しようとしてる「カスタム関数」は次の画像の「販売単価」「製造原価」「販売個数」から「利益」を算出する関数です
上の画像の数式バーにあるように、利益を算出するのに数式を入力していたところを、カスタム関数を呼び出すだけで「利益」を自動算出できるようにします
カスタム関数のポイントとしては、2点を解説しました
1点目は、中学時代に習った「方程式を思い出す」です
カスタム関数はこの方程式と同じ様な考えで作成します
そして、代入するのは「列の名前」になるのが特徴です
2点目は、カスタム関数の作成画面についてです
前回は、上の画面を開いたところまでを行いました
では、この「詳細エディター」画面にてカスタム関数を設定するところから解説をはじめます
目次
１．関数の設定
今回は、次の数式を詳細エディター上に関数として設定します
利益=（販売単価-製造原価）x 販売個数
ここで、詳細エディターへの設定方法を分かり易くするために、前回のポイントで解説した方程式を使って解説します
前回のポイントで、方程式の事例として解説したのは次の方程式です
Y=aX + b
上の方程式を詳細エディターに設定したとすると、次の画像のようなイメージになります
letの箇所は2段の設定になります
1段目：Y = (a,X,b) =>
2段目：ax + b
1段目で「変動する数」として使用する記号を、=を挟んで右辺と左辺に分けて設定した後に、「=>」の2文字で2段目に繋ぎます
そして、2段目では方程式の右辺を設定します
inの箇所はYを設定するだけです
方程式を例に取って、詳細エディターの設定イメージをお伝えしたところで、本題の関数を設定します
利益=（販売単価-製造原価）x 販売個数
上の数式の「変動する数」はそれぞれ、以下のように設定するものとします
・利益 ➡profit
・販売単価 ➡price
・製造原価 ➡cost
・販売個数 ➡quantity
では、上の「変動する数」を詳細エディターにあてはめると下の図のようになります
画面下に以下の文字が出ていたら、右下の「完了」を押します
すると、下のような画像に切り替わります
以降は有料ページ/課金サービスへ
Sがつく関数は素晴らしい～MAXIFSとMINIFS~
これまで、IFS関数、COUNTIFS関数、AVERAGEIFS関数を紹介してきました
いずれの関数も複数条件を設定できるので便利です
今回紹介するのはMAXIFS関数とMINIFS関数です
日常的には使いみちはあまりないとは思いますが、エクセル分析をしたい方には便利だと思いますね
簡単に数字のツボがつかめます
私はこれまでピボットテーブルで、この条件つきの最大値と最小値は把握していました
ピボットテーブルまで作成しなくても、このMAXIFS関数とMINIFS関数では、関数１つで最大値と最小値を把握できるのでとても便利です
！MAXIFS関数とMINIFS関数の書き方
これまで紹介してきた、IFS関数やCOUNTIFS関数と書き方は同じです
＝MAXIFS/MINIFS(対象範囲,条件範囲,条件）
Sつき関数は共通の書き方なので、慣れれば慣れるほど使いやすくなりますね
Sで終わる関数は素晴らしい～AverageIfs関数～
エクセル関数はとても進化しています
XLOOKUP関数も同様ですが、新エクセル関数は絶対に活用した方がいいです！
IFS関数、COUNTIFS関数、そしてAVERAGEIFS関数は、複数条件を簡単に作成できるので便利です
上記３つの関数ともに関数の書き方は同様です
１．文法
AVERAGEIFS関数の書き方は以下になります
=AVERAGEIFS(平均算出範囲,条件範囲1,条 件1,条件範囲2,条件2,・・・)
２．事例解説
今回、AVERAGEIFS関数を試してみるデータは以下です
条件設定と平均値算出は以下の画面で行います
部門と受注日が抽出条件になります
数式は以下の様に設定してあります
複数条件はB列/条件範囲1,⇒C列/条件範囲2の流れで設定しています
この複数条件設定を他の方法で行おうとしたら、関数ではなくピボットテーブルで算出した方が早いですね
関数１つで複数条件を算出できるのは、もの凄い進歩です
最後に補足ですが、部門のリストデータはUNIQUE関数を活用しています
UNIQUE関数で部門の列（東京、東京・・、大阪、大阪）から重複を削除した上で、「＃」にてリストとして再利用しています
UNIQUE関数とリストの組み合わせについて深く知りたい方はこちらを参照してください
Ｓで終わる関数は素晴らしい！～Countifs関数～
前回は、IFS関数を紹介しました
今回はCOUNTFIFS関数を紹介します
本当にSで終わる関数は素晴らしいです
数式の書き方はIFS関数とほぼ同じです
今回は下の事例で解説を行います
この表から最高得点・科目と最低得点・科目の組み合わせが当てはまる人数をCountif関数とCountifs関数、の両方で求めてみます
１．Countif関数のケース
Countif関数は１つしか条件式を設定することができません
なので、一旦、複合キーを作成して検索条件にするしかありません
数式は次のような書き方になります
Countif関数では、検索条件範囲/H4:H8と検索条件/D12との組み合わせを、１つしか使えないのです
=COUNTIF(H4:H8,D12)
２．Countifs関数のケース
Countifs関数の場合は複合キーを使用する必要はありません
Counfifs関数の場合、複数の検索条件範囲と検索条件の組み合わせを設定できるからです
以下が、実際に上の画面で使用した数式です
=COUNTIFS(F4:F8,B16,G4:G8,C16)
数式の書き方は、前回に紹介したIFS関数と同じ様に、検索条件範囲⇒検索条件の記述を条件がある数だけ繰り返すだけで済みます
＜まとめ＞
Countif関数を使いたい時に、複数条件がある場合には複合キーを作成しなくてはならないので、いつも面倒くさい思いをしていました
Countif関数を使えば、条件がいくつあろうとあまり手間ではありません
Sで終わる関数は本当に便利ですね
Filter関数＆テーブル機能でのデータ検索
前回、検索付きリストの中で紹介した”Filter関数”は １つの関数で複数の値を出力できるのも魅力ですが、テーブル機能と連携など、他にも便利な点がいっぱいあります！
さらに、＃とあわせて使うと驚くべき結果が出力されます！
＜Filter関数を使うメリット＞
！複数の条件指定をAND条件とOR条件にて、簡単に使い分けられる
ピボットテーブルと違い、ＡＮＤ条件に加えてＯＲ条件も使えます。しかも、フィルター機能等より、簡単に条件指定が行えます。但し、ＡＮＤやＯＲという指定ではなくて、＊と＋にて指定します
試しに、以下のデータからAND条件でデータを取り出してみます
（）内の条件（製品別＝トラック、売上金額<40000）を＊で組み合わせています
次にOR条件でやってみます。こちらは＋で（）内を結び付けます
＜Filter関数をテーブル機能と組み合わせて使うメリット＞
！カーソルの簡単な操作だけで、大量のデータでも一気に各種の指定ができます
➀範囲指定⇒↘に注目してください
テーブル範囲の左隅にカーソルを置くと↘がでてきますので、そこでクリックするだけで、全範囲をまるごと指定することができます
➁条件範囲指定
こちらは、テーブルの見出しを指定するだけです、今度は見出しの上、↓のところでクリックするだけで条件範囲を指定できます
最後に、個人的に最も驚いた機能は＃です
Filter関数が入力されているセルを指定し、後ろに＃をつけるだけで、Filter関数の出力を再利用できます！
前述の検索付きリストはこの＃による再出力データを利用しています！
これはなかなか便利です
今後、他の新たな関数ともあわせて講座を用意してみたいと思います！
最後までお読みくださりありがとうございました！