ExcelをはじめとしたMicrosoft Officeのテクニックを紹介するYouTubeチャンネルです。
https://www.youtube.com/@ExcelOfficeChannel/featured
【Excel】便利な機能：大量のセルに同じ文字列を一括入力できます。
【Excel】年月日を漢数字にした和暦で日付を表示する方法とは：表示形式
【Excel】関数：文字を統一する関数。全角のJIS関数と半角のASC関数を紹介
【Excel・Word・PowerPoint】便利な機能：写真を正円でトリミングするには、どのようにしたらいいのでしょうか。
当たり前ですが、文字列は合算したりすることはできません。
たとえば、次のシート。
「○」があるところが出席可能を表しています。
やりたいことは、3名が出席できる日時を、手早く確認したいわけです。
「○」ではなく、数値で判定されていれば、統合などの方法もあるのでしょうけども、文字列なので、つかえません。
IF関数とAND関数をつかってもいいですが、もっと多くの表の場合、AND条件が多くなり、可読性も悪化します。
そこで、IF関数とCONCAT関数をつかって、全員出席できる日時を確認していきます。
B2に次の数式を設定します。
=IF(CONCAT(B7,F7,J7)="○○○","開催OK","")
あとは、オートフィルで数式をコピーします。
これで、10月3日10時は、予定が空いているので、開催できる日時を見つけることができました。
この数式のCONCAT関数ですが、CONCAT関数は文字結合をする関数です。
範囲選択するだけで、文字を結合してくれます。
すべて「○」。
つまり「○○○」ならば、出席できると判断することができるというわけです。
最大公約数と最小公倍数をExcelの関数をつかうことで、手早く算出することができます。
Greatest Common Divisor の略ですね。
C2には、最大公約数のGCD関数をつかった数式を設定しています。
=GCD(A2:B2)
引数は、範囲選択するだけです。
最小公倍数は、LCM関数で算出することができます。
Least Common Multiple の略ですね。
C6には、最小公倍数のLCM関数をつかった数式を設定しています。
=LCM(A6:B6)
たったこれだけで、算出することができます。
小学校の先生にとって、お役に立つ関数の一つなのかもしれませんね。
F.DIST関数
読み方： エフ・ディスト
読み方： エフ・ディストリビューション
分類： 統計
F.DIST(x,自由度1,自由度2,関数形式)
F分布の確立を算出する
商品一覧や名簿などで、写真をセル内に表示したい場合は、セル内ではなく、セルの上に置くという状態でした。
ところが、Microsoft365 Insiderでは、画像をセル内に配置することができるようになりました。
オートフィルターを使った時に、とても画期的であることがわかります。
使い方は、簡単です。
挿入タブの図にある画像から、任意の写真を挿入するわけですが、その時に、「セルに配置」を選択してから、画像を挿入します。
今回は、ストック画像から選択しました。
まずは、売上高を降順にしてみます。
これは、セル内に写真が納まっていれば、通常のExcelでもできます。
では、売上高が600だけのデータにしますので、フィルター機能をつかって抽出してみます。
だから何と思うかもしれませんが、この抽出が画期的なんです。
通常のExcelだと、できません。
通常のMicrosoft365で同じ表をつくって、オートフィルターを実行してみます。
ここが、Insider版で進化した点です。
なお、Insider版でつくったExcelを、それ以外のバージョンで開くと、画像データが表示されず、「#UNKNOWN!」エラーが表示されます。
よって、開いて確認することができません。
住所から都道府県を抽出するときや、氏名から苗字を抽出するときに、お馴染みの関数といえば、FIND関数ですね。
今回は、住所から県という文字が何文字目にあるのかを確認するためにFIND関数をつかってみましょう。
C2には、FIND関数をつかった数式を設定します。
=FIND("県",B2)
C4まで、オートフィルで数式をコピーします。
FIND関数の引数は、
最初の引数は、「検索文字列」。
ここに設定した文字を検索しますので「”県”」と設定します。
2つ目の引数は、「対象」。検索したい対象なので、B2と設定します。
これで、「県」という文字が文字列の左側から何文字目にあるのかを算出することができます。
Facebookページに書いた、Excelの豆知識（Trivia）です。
7月9日
Excel。
BAHTTEXT関数
読み方： バーツテキスト
BAHTTEXT(数値)
数値を四捨五入して、バーツ書式を設定した文字列に変換する
7月10日
Excel。
BASE関数
読み方： ベース
BASE(数値,基数,[最小長])
10進数をn進数に変換する
7月11日
Excel
BESSELI関数
読み方： ベッセルアイ
BESSELI(x,n)
第1種変形ベッセル関数In(X)の値を算出します
7月12日
Excel。
BESSELJ関数
読み方： ベッセルジェイ
BESSELJ(x,n)
第1種ベッセル関数Jn(X)の値を算出します
7月13日
Excel。
BESSELK関数
読み方： ベッセルケイ
BESSELK(x,n)
第2種変形ベッセル関数Kn(X)の値を算出します
7月14日
Excel。
BESSELY関数
読み方： ベッセルワイ
BESSELY(x,n)
第2種ベッセル関数Yn(X)の値を算出します
7月15日
Excel。
BETADIST関数
読み方： ベータディスト
読み方： ベータディストリビューション
BETADIST(x,α,β,[A],[B])
ベータ分布の累積確率を算出します
テーブルをExcelにわざわざエクスポートしないで、Access上で処理できればいいなぁといことがあります。
例えば、次のテーブル。
そうでなければ空欄のままと判定したいとします。
Excelなら算出した平均値をつかってIF関数で判定をさせるようにしますが、そういうわけにはいきません。
そこで、クエリで、演算フィールドをつくって、対応します。
作成タブのクエリデザインをクリックします。
クエリの設定を次のようにします。
演算式は、
平均値以上: IIf([販売金額]>=DAvg("[販売金額]","T店舗販売"),"○","")
実行して確認しておきましょう。
ちなみに、販売金額の平均値は1088.4です。
演算フィールドを確認しておきましょう。
平均値以上: IIf([販売金額]>=DAvg("[販売金額]","T店舗販売"),"○","")
ExcelではIF関数ですが、Accessで条件分岐をする関数はIif関数です。
論理式ですが、Excelのように、別のセルに平均値を算出しておき、それを使用するということはしませんので、論理式内に平均値を算出します。
平均値を算出するための関数は、DAvg関数をつかいます。
DAvg("[販売金額]","T店舗販売")
DAvg関数の引数は、フィールド名だけではダメで、テーブル名も必要です。
Accessでも、ExcelのようにIif関数をつかうことで、条件分岐の処理をすることができます。
EXPONDIST関数
読み方： エクスポンディスト
読み方： エクスポネンシャルディストリビューション
分類： 互換性
EXPONDIST(x,Λ,関数形式)
指数分布の確率密度関数と累積分布関数を計算する
条件付き書式に「カラースケール」というのがあって、数値の強弱によってグラデーションの色分けをします。
B2:B9を範囲選択します。
ホームタブの条件付き書式にある「新しいルール」をクリックします。
書式スタイルが「2色スケール」になっていることも確認します。
あとは、最小値と最大値の色をグラデーションにしたい色に設定したらOKボタンをクリックします。
数値が1回目よりも2回目の方がいい件数を算出したい場合、SUMPRODUCT関数をつかえば、途中計算がなくても、一発で算出することができます。
次の表をつかって、確認します。
2回目の得点が大きい件数を知りたいわけです。
その件数を算出してあるのが、C9です。
C9の数式は、
=SUMPRODUCT((B2:B7<C2:C7)*1)
たった、これだけで、2回目のほうが大きい件数を算出することができます。
IF関数をつかって、2回目の方が大きいのかを算出させて、その件数を数えるというのが、普通思い浮かべるやり方だと思います。
これを、SUMPRODUCT関数だけで手早く算出することができます。
この数式を説明します。
SUMPRODUCT関数は、総和を算出するSUM関数と、乗算のPRODUCT関数が合体した関数です。
D列に2回目が大きいかわかるように、B2<C2という数式を作成して、オートフィルで数式をコピーします。
不成立なら「FALSE」が表示されます。
TRUE・FALSEではわかりにくいので、「×1」します。
1を乗算することで、数値に変更することができます。
TRUEなら「1」。
FALSEなら「0」と数値にすることができます。
この数値を合算すれば、2回目のほうが大きい件数を算出することができます。
D列・E列を算出しないで、手早く算出できるというわけです。
作業効率もUPする、知っていると便利なショートカットキー。
なお、Excelのバージョンによって多少変わります。
Ctrl＋Shift＋A
数式でカーソルが関数名の右にあるときに、引数名とかっこを挿入する。
Ctrl＋Shift＋C
図形などオブジェクトの書式をコピーする
Ctrl＋Shift＋F
セルのフォント設定ダイアログ ボックスを開く
Ctrl＋Shift＋G
ブックの統計情報 ダイアログ ボックスを開く
Ctrl＋Shift＋L
オートフィルターのオンとオフ
Facebookページに書いた、Excelの豆知識（Trivia）です。
7月2日
Excel。
ATAN2関数
読み方： アーク タンジェント・ツー
ATAN2(x座標,y座標)
X-Y座標値から 逆正接（アークタンジェント）を算出します
7月3日
Excel。
ATANH関数
読み方： ハイパーポリック アーク タンジェント
ATANH(数値)
数値の双曲線逆正接を算出します
7月4日
Excel。
AVEDEV関数
読み方： アベレージディブ
読み方： アベレージ・ディビエーション
AVEDEV(数値1,[数値2],…)
数値群の平均偏差を算出します
7月5日
Excel。
AVERAGE関数
読み方： アベレージ
AVERAGE(数値1,[数値2],…)
数値の平均値を算出します
7月6日
Excel。
AVERAGEA関数
読み方： アベレージ・エー
AVERAGEA(値1,[値2],…)
数値・文字列・論理値を含む平均値を算出します
7月7日
Excel。
AVERAGEIF関数
読み方： アベレージ・イフ
AVERAGEIF(範囲,条件,[平均対象範囲])
条件付きで数値の平均値を算出します
7月8日
Excel。
AVERAGEIFS関数
読み方： アベレージ・イフズ
読み方： アベレージ・イフ・エス
AVERAGEIFS(平均対象範囲,条件範囲1,条件1,…)
複数条件付きで数値の平均値を算出します
罫線が設定されている表から、罫線を手早く消したい場合、Excel VBAでは、どのようなプログラム文をつくったらいいのでしょうか。
次のようにプログラム文をつくれば、罫線を消すことができます。
Sub 罫線を消す()
Range("b2").CurrentRegion.Borders.LineStyle = False
End Sub
たったこの1行だけで罫線を消すことができます。
Borders.LineStyleをTrueにすれば、格子の罫線を引くことができ、Falseにすれば、罫線を消すことができるというわけです。
今回は、TRUE関数～TYPE関数までをご紹介しております。
TRUE
読み方： トゥルー
TRUE
TRUEを返す
TRUNC関数
読み方： トランク
TRUNC(数値,[桁数])
指定桁数になるように切り捨てる
TTEST関数
読み方： ティーテスト
TTEST(配列1,配列2,検定の指定,検定の種類)
t検定の確立を算出します
TYPE関数
読み方： タイプ
TYPE(値)
データ型を表す数値を算出する
抽出した結果を、別の表にする場合、オートフィルターで抽出してコピーするのが一般的です。
ただ、新しく追加されたFILTER関数をつかえば、元の表はそのままで、オートフィルターをつかわずに、手早く、別表として抽出することができます。
次の表をつかって説明します。
A1:C6の表から、売上高が800以上のデータを、このA1:C6の表はそのままで、別表として抽出したいわけです。
そこで、FILTER関数を使えばいいというわけです。
A9にFILTER関数の数式を設定します。
=FILTER(A2:C6,C2:C6>=800,"なし")
あとは、スピル機能によって、ゴーストが発生しますので、数式をコピーする必要はありません。
このFILTER関数と組み合わせると、さらに手早く抽出することもできます。
例えば、SORT関数と組み合わせれば、抽出と同時に並べ替えも終わった表を抽出することもできます。
最後に、A9に設定した数式を説明します。
最初の引数は、配列。範囲選択ですね。「A2:C6」を設定します。
2つ目の引数は、含む。条件ですね。800以上が該当するようにしたいので、「C2:C6>=800」と設定します。
最後の引数は、空の場合。データがないならどうするのかということなので、「なし」と表示するようにしました。
条件付き書式の「上位/下位ルール」には、「平均より上」はありますが、「平均以上」は用意されていません。
設定したい範囲を選択します。
条件付き書式の「新しいルール」をクリックします。
選択範囲の平均値を「より上」から「以上」に変更します。
これで、平均値以上に設定することができます。
あとは、書式ボタンをクリックして、設定したい書式を選択するだけです。
最後は、OKボタンをクリックして確認します。
なお、行全体を対象にする場合には、AVERAGE関数をつかった条件式を作る必要があります。
一般に「n!!」で表される「二乗階乗」。
これもExcelの関数で用意されています。
B2に
=FACTDOUBLE(A2)
と数式を設定しました。
0は「n!!」で1と定義されていますので、1と算出されました。
オートフィルで数式をコピーします。
すると、B列には、二乗階乗された結果が算出されました。
なお、6の二乗階乗は「48」と算出されています。
二乗階乗は、（2×4×6）なので、48で合致します。
このようにFACTDOUBLE関数をつかうことで、二乗階乗を手早く算出することができます。
EXPON.DIST関数
読み方： エクスポン・ディスト
読み方： エクスポネンシャル・ディストリビューション
分類： 統計
EXPON.DIST(x,Λ,関数形式)
指数分布の確率密度関数と累積分布関数を計算する
表の一部を、それぞれのパターンを確認するために、データを入力し直したり、コピーしたりするのは、面倒な作業だといえます。
ただ、平日と土日祝日で数値が異なるわけです。
データを入れ替えるのに、入力するのは面倒ですし、コピーするのも面倒です。
そこで、「シナリオ」という機能をつかうことで、簡単に入れ替えることができます。
シナリオの登録と管理ダイアログボックスが表示されます。
シナリオの編集ダイアログボックスが表示されます。
変化させるセルには、$B$2:$B$4
必要に合わせて、コメントを入力しておくとわかりやすいですね。
OKボタンをクリックします。
シナリオの値ダイアログボックスが表示されます。
あと、もう一つの土日祝日を設定しますので、追加ボタンをクリックします。
「土日祝日」と入力したら、変化させるセルを確認して、OKボタンをクリックします。
OKボタンをクリックします。
説明で使用したデータは、3件でシナリオも2つでしたが、入れ替えるデータ量が増えたり、シナリオが増えたりすれば、再入力やコピーで対応するよりも、シナリオで管理することで、手早く数値を入れ替えて確認することができます。
Facebookページに書いた、Excelの豆知識（Trivia）です。
6月25日
Excel。
ARABIC関数
読み方： アラビック
分類： 数学/三角
ARABIC(文字列)
ローマ数字をアラビア数字に変換します
6月26日
Excel。
AREAS関数
読み方： エリアズ
分類： 検索/行列
AREAS(参照)
範囲や名前の範囲に含まれる領域の数を算出します
6月27日
Excel。
ARRAYTOTEXT関数
読み方： アレイトゥテキスト
分類： 文字列操作
ARRAYTOTEXT(配列,[書式])
配列のテキスト表現を返す
6月28日
Excel。
ASC関数
読み方： アスキー
分類： 文字列操作
ASC(文字列)
英数カナを半角に変換する
6月29日
Excel。
ASIN関数
読み方： アーク・サイン
分類： 数学/三角
ASIN(数値)
逆正弦（アークサイン）を算出します
6月30日
Excel。
ASINH関数
読み方： ハイパーポリック アーク・サイン
分類： 数学/三角
ASINH(数値)
数値の双曲線逆正弦を算出します
7月1日
Excel。
ATAN関数
読み方： アーク タンジェント
分類： 数学/三角
ATAN(数値)
逆正接（アークタンジェント）を算出します
横棒グラフをつくってみると、元になった表と比べて、縦軸の項目が同じになっていないことに気づきます。
横棒グラフの縦軸をクリックします。
書式タブをクリックして、「グラフ要素」が縦（項目）軸になっていることを確認したら、その下にある「選択対象の書式設定」をクリックします。
グラフは、横軸が上に移動しています。
階乗というものがあります。
Excelで階乗を算出するには、FACT関数を使えば、手早く算出することができます。
B2に設定する数式は、
=FACT(A2)
これで、階乗を算出することができますが、PRODUCT関数でも算出することができます。
=PRODUCT($A$2:A2)
PRODUCT関数だと、始点を絶対参照にした、始点留めの範囲選択を設定する必要がありますので、FACT関数の方が、手早く算出することができます。
データベース機能を使うときに、大文字や小文字がバラバラだと、検索や抽出をするときに、思っているような処理ができません。
そこで、大文字・小文字・頭文字を大文字にする関数が用意されていますので、確認しておきましょう。
C2には、
=UPPER(B2)
これで、小文字をすべて大文字に変更することができました。
小文字に整えてくれるのがLOWER関数です。
C3には、
=LOWER(B3)
大文字をすべて小文字に変更することができます。
最初の文字。頭文字を大文字に変更してくれるのが、PROPER関数です。
C4には、
=PROPER(B4)
頭文字だけを大文字にして、あとは、小文字にしてくれます。
ところが、これらの関数は、全角と半角のコントロールはしてくれません。
全角は全角。半角は半角のままなので、注意する必要があります。