Excel。GROUPBY関数は行の項目ごとに集計をします。
<関数辞典:GROUPBY関数>
GROUPBY関数
読み方: グループバイ
分類: 検索/行列
GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])
行の項目ごとに集計をします。
Excel・Word・PowerPoint・Accessのテクニックや研修ネタ・テキストを紹介しております。主に、講義先や現場レベルで質問があったものを中心に書いております。
GROUPBY関数
読み方: グループバイ
分類: 検索/行列
GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])
行の項目ごとに集計をします。
次の表は、複数回答ありのアンケート結果です。
5名が回答した結果がF列に入力されています。
それぞれのA~Dまでの集計結果がC列に算出されています。
複数回答なので、Aが何件あるのか、Bが何件あるのかを集計したいわけです。
目視で確認したくありません。
そこで、効率的な集計方法はないものでしょうか。
F列が、一文字ならば、Aだったら、何件というようにすれば、数えることができます。
ただし、複数回答がOKですから、一文字の場合もあれば、最大4文字が入力されているので、単純にCOUNTIF関数やCOUNTIFS関数では、対応するのが難しいように思えます。
このような場合、実は、COUNTIF関数で対応することができます。
ただ、条件に工夫が必要になります。
工夫というのは、「Aという文字が含まれているか」とすればいいわけです。
「含む」ということで、ワイルドカードの「*」を合わせてつかうことで、対応することができます。
C3に数式を設定します。
=COUNTIF($F$3:$F$7,"*"&$A3&"*")
あとは、オートフィルで数式をコピーします。
最初の引数は、「範囲」です。回答のF3:F7を範囲選択します。
また、オートフィルで数式をコピーするので、絶対参照も忘れずに設定します。
2つ目の引数は、「検索条件」です。
ここで、「”A”」と設定してしまうと、複数回答のセルは、条件が合致しません。
そこで、「*」(ワイルドカード)で、条件を囲ってあげます。
「"*"&$A3&"*"」
これで、含むという条件にすることができます。
A3はAなので、「Aという文字を含むなら」という条件をつくることができるというわけです。
Facebookページに書いた、Excelの豆知識(Trivia)です。
3月24日
Excel。
MAKEARRAY関数
読み方: メイクアレイ
分類: 論理
MAKEARRAY(rows,column,function)
LAMBDA関数を適用して、指定した行と列のサイズの計算配列を返します
3月25日
Excel。
MAP関数
読み方: マップ
分類: 論理
MAP(array,lambda_or_array2,…)
LAMBDAを適用して新しい値を作成することにより、配列内の各値をmappingで形成された配列を新しい値に返す
3月26日
Excel。
MATCH関数
読み方: マッチ
分類: 検索/行列
MATCH(検査値,検査範囲,[照合の種類])
値を検索してその相対位置を算出します
3月27日
Excel。
MAX関数
読み方: マックス
分類: 統計
MAX(数値1,[数値2],…)
最大値を算出します
3月28日
Excel。
MAXA関数
読み方: マックスエー
分類: 統計
MAXA(値1,[値2],…)
数値・文字列・論理値を含む最大値を算出します
3月29日
Excel。
MAXIFS関数
読み方: マックスイフズ
読み方: マックスイフエス
分類: 統計
MAXIFS(最大範囲,条件範囲1,条件1,…)
条件により指定した範囲内の最大値を算出します
3月30日
Excel。
MDETERM関数
読み方: エムデターム
読み方: マトリック ディターミナント
分類: 数学/三角
MDETERM(配列)
配列の行列式を算出します
最低値を算出することができる、MIN関数では、0点以上の場合の最低値を算出したいなど、「条件」をつけた最低値を算出することができません。
そこで、「複数条件」をつけた、最低値を算出することができる「MINIFS関数」をつかってみると、算出することができます。
GETPIVOTDATA関数
読み方: ゲットピボットデータ
分類: 検索/行列
GETPIVOTDATA(データフィールド,ピボットテーブル,[フィールド1,アイテム1],…)
ピボットテーブル内の値を抽出する
Facebookページに書いた、Excelの豆知識(Trivia)です。
3月17日
Excel。
LOGINV関数
読み方: ログインバース
分類: 互換性
LOGINV(確率,平均,標準偏差)
累積確率から対数正規分布を算出します
3月18日
Excel。
LOGNORMDIST関数
読み方: ログノーマルディスト
読み方: ログノーマルディストリビューション
分類: 互換性
LOGNORMDIST(x,平均,標準偏差)
対数正規分布の累積確率を算出します
3月19日
Excel。
LOGNORM.DIST関数
読み方: ログノーマル・ディスト
読み方: ログノーマル・ディストリビューション
分類: 統計
LOGNORM.DIST(x,平均,標準偏差,関数形式)
対数正規分布の累積確率か確率密度を算出します
3月20日
Excel。
LOGNORM.INV関数
読み方: ログノーマル・インバース
分類: 統計
LOGNORM.INV(確率,平均,標準偏差)
累積確率から対数正規分布を算出します
3月21日
Excel。
LOOKUP関数
読み方: ルックアップ
分類: 検索/行列
LOOKUP(検索値,検索範囲,対応範囲)
1行/1列のセル範囲でせるを検索し対応するセルの値を返す ベクトル形式
3月22日
Excel。
LOOKUP関数
読み方: ルックアップ
分類: 検索/行列
LOOKUP(検索値,配列)
縦横を指定しないでセルを検索し対応するセルの値を返す
3月23日
Excel。
LOWER関数
読み方: ロウアー
分類: 文字列操作
LOWER(文字列)
英字を小文字に変換する
2023年11月のExcel365Insider版に追加された、PERCENTOF関数は、構成比を算出するときに、便利な関数です。
次の表を用意しました。
構成比を算出するには、対象の数値の合計値が必要になりますが、PERCENTOF関数では、不要です。
D2に設定した数式は、
=PERCENTOF(C2,$C$2:$C$6)
あとは、オートフィルで数式をコピーするだけです。
PERCENTOF関数の引数は、次のようになっています。
PERCENTOF(data_subset,data_all)
最初の引数は、data_subsetです。
構成比を求めたい数値ですね。
C2と設定します。
次の引数は、data_allです。
全体の数値です。
$C$2:$C$6 と絶対参照も忘れずに設定します。
これで完成です。
構成比を出すために、合計値を算出する必要がないのが、いい点です
ただ、このPERCENTOF関数は、Insider版にしかまだありません。