作業効率もUPする、知っていると便利なショートカットキー。
なお、Excelのバージョンによって多少変わります。
今回は、Ctrlキーと「~」で表示形式を外すショートカットキーなどを紹介。
Ctrl＋'
セルの値と数式の表示を切り替える（シングルコーテーション）
Ctrl+Shift+7 と同じ
Ctrl＋~
表示形式を外します。[全般] 数値形式にします。（チルダ）
Ctrl+Shift+^ と同じ
Ctrl＋＾
指数表示にする。（キャレット）
Ctrl＋¥
選択した行内で、アクティブセルと合致しないセルを選択
Ctrl＋｜
選択した列内で、アクティブセルと合致しないセルを選択
Ctrl+Shift+¥ と同じ
DSUM関数
読み方： ディーサム
分類： データベース
DSUM(データベース,フィールド,条件)
条件を満たすレコードの合計を算出します
オートフィルターをつかわなくても、関数で該当するデータを抽出できるFILTER関数ですが、日付を条件として抽出するには、DATE関数が必要になります。
そこで、FILTER関数を使用すると、手早く抽出することができます。
A9にFILTER関数をつかった数式は、
=FILTER(A2:C6,B2:B6=DATE(2023,9,4),"")
これで、抽出することができました。
なお、スピル機能によって、ゴーストが生まれるので、オートフィルを使わなくても、数式をコピーしてくれます。
なお、算出結果は「シリアル値」で表示されてしまうので、表示形式を使って日付に戻す必要があります。
さて、今回のポイントは、引数の条件に、直接、日付を入力する時にはDATE関数が必要ということです。
FILTER関数の2つ目の引数が、条件です。
条件に日付を使う時に、DATE関数が必要になります。
例えば、次のように、数式を変更してみましょう。
=FILTER(A2:C6,B2:B6="2023/9/4","")
日付にDATE関数を使わずに、「”（ダブルコーテーション）」で囲んでみると、表示してくれません。
「"2023/9/4"」とすると、日付ではなくて、文字として認識するのが原因のようです。
ちなみにExcel VBAやAccessの日付型で使用する「#」で囲んでみても、日付扱いにならず、「”（ダブルコーテーション）」と同じ空白の表示になってしまいます。
なお、セル番地に日付を入力しておけば、DATE関数を使用する必要はありません。
=FILTER(A2:C6,B2:B6=E1,"")
何か月後や何か月前の月末日を算出したい時には、EOMONTH関数をつかうことで、手早く算出することができます。
このEOMONTH関数もEDATE関数同様に、算出結果がシリアル値で表示されてしまうので、算出後に表示形式をつかって、日付型に変更する必要があります。
C2の数式は、
=EOMONTH(A2,2)
算出結果は、45169とシリアル値で表示されてしまうので、表示形式をつかって日付型に戻します。
逆に、月初日を算出したい場合には、「+1」すれば、いいというわけですね。
Excel。2023/4/30-5/6にTODAY関数など紹介したFacebookページのコメントです。
Facebookページに書いた、Excelの豆知識（Trivia）です。
4月30日
Excel。
TINV関数
読み方は、ティーインバースで、t分布の両側確率から上側の確率変数を算出します。
5月1日
Excel。
T.INV関数
読み方は、ティー・インバースで、t分布の左側（下側）確率からt値を算出します。
5月2日
Excel。
T.INV.2T関数
読み方は、ティー・インバース・ツーテールで、t分布の両側確率から上側の確率変数を算出します。
5月3日
Excel。
TODAY関数
読み方は、トゥデイで、現在の日付を算出します。
5月4日
Excel。
TTEST関数
読み方は、ティーテストで、t検定の確立を算出します。
5月5日
Excel。
T.TEST関数
読み方は、ティー・テストで、t検定の確率を算出します。
5月6日
Excel
TRANSPOSE関数
読み方は、トランスポーズで、縦横を入れ替えた表をする
苗字と名前がそれぞれ別のフィールドになっています。
フルネームの氏名がないので、苗字と名前を一つに結合した、氏名フィールドを作りたい場合、どのようにしたらいいのでしょうか。
これは、Excelでも同じですね。
よって、設定する演算フィールドは、
氏名: [苗字] & " " & [名前]
と設定するだけで、テキスト型フィールドを結合することができます。
DSTDEVP関数
読み方： ディースタンダードディブピー
読み方： ディースタンダードディビエーションピー
分類： データベース
DSTDEVP(データベース,フィールド,条件)
条件を満たすレコードの標準偏差を算出します
作業効率もUPする、知っていると便利なショートカットキー。
なお、Excelのバージョンによって多少変わります。
今回は、Ctrlキーと「%」でパーセント表示するショートカットキーなどを紹介。
Ctrl＋!
マイナスの数値を赤色にしない表示形式にする
Ctrl+Shift+1と同じ
Ctrl＋"
アクティブなセルの 1 つ上のセルの値をコピーします。
Ctrl+Shift+2と同じ
Ctrl＋#
日付形式を設定する
Ctrl+Shift+3と同じ
Ctrl＋$
通貨スタイル (¥)を設定する
Chtl+Shift+4と同じ
Ctrl＋%
セルの値を %（パーセント） 表示にする
Ctrl+Shift+5と同じ
アドインでデータ分析にある基本統計量をつかうことで、データのステータスを手早く確認することができます。
G列とH列に表示されているのが、C列のListeningの基本統計量です。
=AVERAGE(C2:C11)
標準誤差は、標準偏差をデータの件数の平方根で除算した値です。
=STDEV.S(C2:C11)/SQRT(COUNT(C2:C11))
で算出することができます。
STDEV.S関数は、「S」なので、数値をサンプルとした標準偏差を算出する関数です。
SQRT関数は、平方根を算出する関数です。
COUNT関数は、数値の件数を算出関数です。
中央値は、MEDIAN関数で算出できます。
=MEDIAN(C2:C11)
最頻値は、MODE.SNGL関数で算出できます。
最頻値は、データ内で一番多く登場した数値のことですね。
=MODE.SNGL(C2:C11)
標準偏差は、先程紹介した、STDEV.S関数で算出します。
=STDEV.S(C2:C11)
標準偏差は、データが平均値からどのぐらい外れているか（散っているのか）を表します。
分散は、VAR.S関数で算出します。
=VAR.S(C2:C11)
尖度（せんど）は、KURT関数で算出できます。
=KURT(C2:C11)
尖度は、正規分布を元に上下にどの程度偏っているかを表す値です。
歪度（わいど）は、SKEW関数で算出できます。
=SKEW(C2:C11)
歪度は、正規分布を元に左右にどの程度偏っているかを表す値です。
範囲は、最大値と最小値の範囲（レンジ）です。
最大値から最小値を減算すれば算出できます。
=MAX(C2:C11)-MIN(C2:C11)
最小は、MIN関数ですね。
=MIN(C2:C11)
最大は、MAX関数ですね。
=MAX(C2:C11)
合計は、SUM関数ですね。
=SUM(C2:C11)
最後は、データの個数なので、COUNT関数です。
=COUNT(C2:C11)
このように、基本統計量は様々な関数をつかって算出しています。
算出する項目が多いため、面倒なので、分析ツールをつかえるようならば、つかったほうが、楽なような気がします。
DSTDEV関数
読み方： ディースタンダードディブ
読み方： ディースタンダードディビエーション
分類： データベース
DSTDEV(データベース,フィールド,条件)
条件を満たすレコードの標準偏差推定値を算出します
日付の何カ月後や何カ月前を手早く算出するならば、EDATE関数をつかうのがオススメなのです。
ただ、EDATE関数は、シリアル値で算出されてしまうのが、ちょっとした残念なんですね。
C2の数式は、
=EDATE(A2,2)
算出結果は、シリアル値で表示されてしまうので、表示形式をつかって、日付に表示を変更すれば、大丈夫ですね。
余談ですが、うるう年の2月29日が絡む場合、次のような結果で表示されます。
2023/12/30と2023/12/31の2カ月後の2024/2/30とか存在しないので、2024/2/29と表示されます。
夜間勤務など午前0時をまたぐ時間計算は、単純に減算だけでは、算出することができません。
D4には、
=C4-B4
と設定してあって、オートフィルで数式をコピーしています。
D4は、きちんと減算してくれていて「3:00」と結果を表示していますが、D5やD6は「####」と表示されてしまっています。
D5をアクティブにすると、次のメッセージが表示されます。
どうやら、Excelは午前0時をまたぐ場合、単純な減算だけでは算出できないようです。
この原因は、Excelでは、時間や日付は、シリアル値で管理されているためです。
よって、D4の数式をIF関数で修正します。
=IF(B4<C4,C4-B4,C4+1-B4)
あとは、オートフィルで数式をコピーします。
退社が「5:00」とあれば、翌日の「5:00」というイメージをもちますが、Excelは単純に「5:00」という時間でしかありません。
出社の「22:00」と退社の「5:00」は1900/1/1の一日内の数値の大小でしかないということです。
それを踏まえて、数式をつくります。
IF関数の数式を確認します。
最初の引数の「論理式」は、B4<C4。
退社のほうが出社よりも大きいかを判断させます。
これで、退社が午前0時以降なのかを判断することができます。
2つ目の引数の「真の場合」は、C4-B4。
普通の退社-出社ですね。
3つ目の引数の「偽の場合」は、C4+1-B4。
なぜ「+1」しているのかというと、翌日とさせたいわけですね。
Excelは1日をシリアル値で「1」としています。
「5:00」は29:00ですね。
なので、「+24」したいわけです。
ただ単に「+24」すると、24日後ということになってしまいます。
シリアル値で考えると、「1日＝1＝24:00」なので、「+1」するということは「24:00」を足して24時間以上の表示にしているわけです。
こうなれば、算出結果が「負」にはなりません。
「######」と表示されないで、勤務時間を算出することができたというわけですね。
Excelの時間計算は、シリアル値を考えないといけないケースがあるので、注意が必要ですね。
DROP関数
読み方： ドロップ
分類： 検索/行列
DROP(array,rows,[columns])
配列から連続する指定された数の連続する行または列を除外する
今回は、TBILLEQ関数～TDIST関数までをご紹介しております。
TBILLEQ関数
読み方： ティービルイーキュー
TBILLEQ(受渡日,満期日,割引率)
米国財務省短期証券の債権に相当する利回りを算出します
TBILLPRICE関数
読み方： ティービルプライス
TBILLPRICE(受渡日,満期日,割引率)
米国財務省短期証券の額面100ドル当たりの価格を算出します
TBILLYIELD関数
読み方： ティービルイールド
TBILLYIELD(受渡日,満期日,現在価値)
米国財務省短期証券の利回りを算出します
TDIST関数
読み方： ティーディスト
読み方： ティーディストリビューション
TDIST(x,自由度,分布の指定)
t分布の右側確率か両側確率を算出します
「ボールペン」または、「販売数が50以上」の商品をオートフィルターで抽出したい場合、どのようにしたらいいのでしょうか？
つまり、「かつ」というAND条件での抽出で処理をおこなうようになっています。
「ボールペン」または、「販売数が50以上」というOR条件での抽出をするには、フィルターオプションをつかうことで解決できます。
抽出条件を用意します。
同じ行に並列で、条件を設定するとAND条件になりますが、行を変えることでOR条件を設定することができます。
A5:D13の表内のセルをアクティブにしておきます。
データタブの並べ替えとフィルターにある「詳細設定」をクリックします。
自動的に絶対参照にかわります。
検索条件範囲には、A1:D3の抽出条件を範囲選択します。
あとは、OKボタンをクリックします。
このように、条件行を設定しておくだけで、複雑な抽出条件にも対応して、データを抽出することができます。
このオートフィルターを設定していなくても、「詳細設定」はつかうことができます。
データがどのような傾向にあるのか等を関数で、標準偏差とか、分散とかを算出してもいいのですが、アドインにある基本統計量をつかうだけで、手早く確認することができます。
データタブの「データ分析」をクリックします。
あとは、基本統計量を算出したいデータをつかって、設定していきます。
先頭行をラベルとして使用にチェックマークをオンします。
出力先は、基本統計量を表示したい起点となるセルなので、G1と設定します。
OKボタンをクリックするだけです。
DPRODUCT関数
読み方： ディープロダクト
分類： データベース
DPRODUCT(データベース,フィールド,条件)
条件を満たすレコードの積を算出します
東日本を「EJP」、東京を「TK」というように、検索値が文字列の一部で、しかも文字数が一致していないデータを元に、検索抽出したい場合は、どのようにしたらいいのでしょうか。
例えば、次の表。
ハイフンから左側の文字列と合致する地域名を、A7:B10の表から、検索抽出したいわけです。
VLOOKUP関数でもいいのですが、XLOOKUP関数で数式をつくっていきます。
C2に次のように数式を設定します。
=XLOOKUP(LEFT(B2:B5,2)&"*",A8:A10,B8:B10,"",2,1)
これで、検索値の文字数が異なっても手早く検索抽出することができます。
XLOOKUP関数をつかうことで、オートフィルで数式をコピーしなくても、スピル機能によって、数式がコピー（ゴースト）されますし、絶対参照も不要です。
では、数式を確認してきましょう。
XLOOKUP関数の最初の引数は、「検索値」。
地域番号と合致させるには、ハイフンから左側の文字列が対象になるわけですが、ハイフンから左側の文字数が異なっているため、単純に左から何文字というわけにはいきません。
FIND関数をつかって、ハイフンまでの文字数をカウントして、その数をつかって抽出という方法もありますが、XLOOKUP関数には、一致モードのワイルドカードをつかうことができます。
幸い、「EJ」「WJ」「TK」と左から2文字だけ抽出しても、区分けがつくことがわかります。
そこで、検索値は、左側から指定した文字列を抽出するLEFT関数をつかい設定します。
LEFT(B2:B5,2)&"*"
「&”*”」とワイルドカードを文字結合します。
2つ目の引数は、「検索範囲」。
検索値を元にどこの範囲から検索するのかを設定しますので、
A8:A10
3つ目の引数は、「戻り範囲」。
表示したいデータは、どの列にあるのかを指定しますので、B8:B10。
4つ目の引数は、「見つからない場合」。
今回は、「””（ダブルコーテーション×2）」と空白にするように設定します。
5つ目の引数は、「一致モード」。
完全一致ではなく、ワイルドカードで検索させますので、「2」と設定します。
最後の引数は、「検索モード」。
データも多くないので、通常通り、先頭から後方に向けて検索させますので「１」。
XLOOKUP関数とLEFT関数。そしてワイルドカードを組み合した方法をご紹介しました。
データを読み込んでみたら、郵便番号のデータにハイフンがありませんでした。
また、1文字目が０のため、0が表示されず、6桁の数値になっていました。
そして、ハイフンを含めて表示することができるのでしょうか。
左から何文字目が…とか考えちゃうところですが、最初に「0（ゼロ）」があろうがなかろうが、セルの書式設定ダイアログボックスを表示して、表示形式の「その他」にある「郵便番号」を設定しちゃえばいいわけです。
C2には、次の数式を設定しました。
=TEXT(A2,"000-0000")
これで、最初の「0（ゼロ）」が表示されていない場合でも、ハイフンが含まれていない場合でも、対応することができます。
Facebookページに書いた、Excelの豆知識（Trivia）です。
4月23日
Excel。
T.DIST関数
読み方は、ティー・ディストで、t分布の左側（下側）累積確率か確率密度を算出します。
4月24日
Excel。
T.DIST.RT関数
読み方は、ティー・ディスト・ライトテールで、t分布の右側（上側）確率を算出します。
4月25日
Excel。
T.DIST.2T関数
読み方は、ティー・ディスト・ツーテールで、t分布の両側確率を算出します。
4月26日
Excel。
TEXT関数
読み方は、テキストで、数値を書式設定した文字列に変換する
4月27日
Excel。
TEXTJOIN関数
読み方は、テキストジョインで、複数の範囲や文字列からテキストを結合する
4月28日
Excel。
TIME関数
読み方は、タイムで、指定した時刻を算出します。
4月29日
Excel。
TIMEVALUE関数
読み方は、タイムヴァリューで、時刻を表す文字列をシリアル値に変換する
Excelでは、あまり気にしなくてもいい「型」。
本来データベースでは「型」というのは重要なわけです。
例えば、次のテーブルの出席番号フィールドを昇順で並べ替えてみることにします。
これは、「テキスト型（短いテキスト型）」が原因なわけですね。
出席番号フィールドが「数値型」ならば、問題なく並べ替えができました。
テーブルの出席番号フィールドの型を変えると、すでにリレーションシップを設定していれば、「型」を変更することはできません。
そこで、CLng関数をつかうことで、疑似的に「数値型」として扱うことができます。
出席番号フィールドを演算フィールドとして設定していきます。
並べ替え: CLng([出席番号])
並べ替えの設定の「昇順」
実行してみましょう。
このCLng関数は、「数値型」に変えて処理してくれる関数です。
「短いテキスト型」だったものをCLng関数で、疑似的に「数値型」することができます。
これで、数値型と同じように並べ替えることができました。
順位を算出することができる、「RANK.EQ関数」
通常、順位を算出したい場合は、順位の基準となる数値の列を範囲選択するわけですが、離れた飛び地の場合、どのようにしたらいいのでしょうか。
=RANK.EQ(B2,(B5:B9,D5:D9),0)
このように数式を設定すると、算出することができます。
どこが問題なのかというと、2番目の引数である「参照」です。
今回使用するRANK.EQ関数の引数は、
1番目は、数値で、これは、ここで指定した値は、何位なのかを知りたい数値を選択します。
よって、「B2」
2番目が、参照。この参照が全体の範囲です。
3番目が、昇順か降順での順をきめる「順序」です。
点数がいい順での順位を算出したいので、「0」と設定します。
C2には、B2の得点がA4:D9の表内の得点全体の中で、何位なのかを算出したいわけです。
注目は、得点がB5:B9とD5:D9と離れていることです。
離れているところを範囲選択する場合、Ctrlキーをつかって範囲選択をします。
すると、数式には、
B5:B9,D5:D9と「,（カンマ）」で区切られて入力されます。
そうなると、Ctrlキーをつかった離れた範囲選択先が、3番目の引数になってしまうので、エラーが発生します。
SUM関数でも離れたところが範囲だと
=SUM(A1,C1)
と数式を作成しますよね。
そのため、離れた範囲を設定する場合には、2番目の引数は、「()カッコ」で囲ってあげる必要が発生します。
ちょっとしたことですが、離れたところが「,（カンマ）」で区切られることから「()カッコ」が必要なことを知らないと、エラーが発生しますので、ちょっと覚えておくといいかもしれませんね。
Excelのショートカットキー
作業効率もUPする、知っていると便利なショートカットキー。
今回は、Ctrlキーと記号の組み合わせを紹介。
Ctrl＋.
範囲選択時、範囲選択内の四隅を順番に移動する
Ctrl＋>
左のセルの内容を右にコピーする
Ctrl＋>
テキストボックス内：フォントサイズを拡大
Ctrl＋<
上のセルを下にコピーする
Ctrl＋<
テキストボックス内：フォントサイズを縮小
Ctrl＋@
表示形式で時刻に変更します。
Ctrl＋{
数式で直接参照しているセルをすべて選択します。
参照元
Ctrl＋}
数式で直接参照している数式が入力されたすべてのセルを選択します。
参照先
指定した文字が含まれるセルだけを塗りつぶしするならば、条件付き書式のセルの強調表示ルールにある「文字列」をつかって、設定すれば手早く設定することができます。
今回は、B列の住所に「横浜市」が含まれていたら、行全体を塗りつぶすようにします。
条件に一致したら、行全体を塗りつぶしたいわけですから、「条件付き書式」をつかっていきます。
あとは、どのような条件を設定するかを考えていきます。
A2:B6を範囲選択します。
ホームタブの条件付き書式にある「新しいルール」をクリックします。
「次の数式を満たす場合に値を書式設定」のボックスに数式を設定します。
=FIND("横浜市",$B2)
あとは、書式ボタンをクリックして、塗りつぶしたい色を設定したら、OKボタンをクリックすれば完成です。
設定した数式は、FIND関数をつかった数式です。
FIND関数は、指定した文字列が含まれているかどうかを判断する関数です。
「=FIND("横浜市",$B2)」
と設定することで、横浜市が含まれていたら＝TRUE。
含まれていなければ＝FALSEを返します。
TRUEならば条件が満たされているということになりますので、塗りつぶす対象になるというわけです。
なお、「$B2」と列を固定した複合参照にすることで、行全体を対象にすることができます。
複合参照をつかうことで、行全体を塗りつぶすことができるというわけですね。