Facebookページに書いた、Excelの豆知識（Trivia）です。
5月7日
Excel。
TREND関数
読み方は、トレンドで、重回帰分析による値を予測する
5月8日
Excel。
TRIM関数
読み方は、トリムで、不要なスペースを削除する
5月9日
Excel。
TRIMMEAN関数
読み方は、トリムミーンで、異常値を除いて平均値を算出します。
5月10日
Excel。
TRUE関数
読み方は、トゥルーで、TRUEを返す
5月11日
Excel。
TRUNC関数
読み方は、トランクで、指定桁数になるように切り捨てる
5月12日
Excel。
TYPE関数
読み方は、タイプで、データ型を表す数値を算出する
5月13日
Excel。
UNICODE関数
読み方は、ユニコードで、文字列の最初の文字のUnicode番号を返す
多くの数式をまとめて、確認したい時には、数式タブにある「数式の表示」をクリックすることで、算出結果ではなくて、数式を表示することができます。
手早く、数式も確認して、さらに算出結果も同時に確認したい場合にはFORMULATEXT関数をつかうことで、対応することができます。
次の表をつかって、確認してみましょう。
=FORMULATEXT(D6)
これで、D6に設定してある数式と、その結果を同時に表示することができました。
FORMULATEXT関数は、ふりがなを表示する時につかう、PHONETIC関数の数式版という関数です。
また、FORMULATEXT関数の引数には、セルだけはなく、範囲を設定することもできます。
=FORMULATEXT(E2:E5)
スピル機能によって、数式がF5まで拡張（ゴースト）されるので、オートフィルで数式をコピーする必要はありません。
このように、まとめて数式を確認したい時などには、FORMULATEXT関数をつかえば、手早く確認することができます。
今回は、TEXT関数～TEXTSPLIT関数までをご紹介しております。
TEXT関数
読み方： テキスト
TEXT(値,表示形式)
数値を書式設定した文字列に変換する
TEXTAFTER関数
読み方： テキストアフター
TEXTAFTER(text,delimiter,[instance_num],[match_mod],[match_end],[if_not_found])
文字を区切った後のテキストを返す。
TEXTAFTER(text,delimiter,[instance_num],[ignore_case])から変更
TEXTBEFORE関数
読み方： テキストビフォー
TEXTBEFORE(text,delimiter,[instance_num],[match_mod],[match_end],[if_not_found])
文字を区切る前のテキストを返す。
TEXTBEFORE(text,delimiter,[instance_num],[ignore_case])から変更
TEXTJOIN関数
読み方： テキストジョイン
TEXTJOIN(区切り文字,空のセルは無視,テキスト1,…)
複数の範囲や文字列からテキストを結合する
TEXTSPLIT関数
読み方： テキストスピリット
TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pat_with])
区切り記号を使用してテキストを行または列に分割する
DURATION関数
読み方： デュレーション
分類： 財務
DURATION(受渡日,満期日,利率,利回り,頻度,[基準])
定期的に利子に支払われる証券の年間マコーレー係数を算出する
Excelの小計機能を使うときには、最初に並べ替えをして、そのあとに、データタブの「小計」をつかって、集計するわけですね。
ただ、処理数が多く、意外と面倒な処理だといえます。
そこで、Excel VBAでプログラムをつくって対応していきます。
次のデータを用意しました。
Sub 小計機能()
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add2 Key:=Range("b1"), Order:=xlAscending
.SetRange Range("a1").CurrentRegion
.Header = xlYes
.Apply
End With
Range("a1").CurrentRegion.Subtotal groupby:=2, Function:=xlSum, totallist:=4
End Sub
実行してみます。
では、プログラム文を説明していきます。
色々なプログラム言語とExcel VBAが少々ことなるところは、あくまでも、「Excel」なんだということです。
要するに、Excelのダイアログボックスをつかった処理は、そのダイアログボックスと同じ項目を設定する必要があります。
小計機能は、基準となる列を並べ替え処理をして、まとめておく必要があります。
並べ替えをしているのが、下記の部分です。
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add2 Key:=Range("b1"), Order:=xlAscending
.SetRange Range("a1").CurrentRegion
.Header = xlYes
.Apply
End With
これは、並べ替えのダイアログボックスをみれば、どこを設定しているか、わかります。
商品名ごとの集計をしたいので、見出しの「商品名」があるのがB1なので、「Range("b1")」とします。
「Order:=xlAscending」は、順序なので、昇順で並べ替えをしたいので、「xlAscending」と設定します。
「SetRange Range("a1").CurrentRegion」は、並べ替えは自動的に範囲選択をしますので、A1を起点として連続する範囲を、並べ替えの対象とします。
「Header = xlYes」は、先頭行を見出しとして使用するのチェックマークです。
「Apply」は、OKボタンですね。
これで、並べ替えが行われます。
そして、小計機能をおこなうわけです。
この小計機能もダイアログボックスなので、それを設定するように、プログラムをつくってあげます。
Range("a1").CurrentRegion.Subtotal groupby:=2, Function:=xlSum, totallist:=4
出だしの「Range("a1").CurrentRegion.Subtotal」は、A1を起点とした範囲を「Subtotal」＝小計します。
左から2列目の商品名を基準として集計しますので、「2」です。
「Function:=xlSum」は、集計方法です。
合計なので、「xlSum」と設定します。
「totallist:=4」は、集計するフィールドです。
左から4列目の売上高の集計をしたいので、「4」と設定します。
これで、小計をすることができます。
また、データ量が増減しても、「CurrentRegion」としていることで、対応することも可能です。
作業効率もUPする、知っていると便利なショートカットキー。
なお、Excelのバージョンによって多少変わります。
今回は、Ctrlキーと「~」で表示形式を外すショートカットキーなどを紹介。
Ctrl＋'
セルの値と数式の表示を切り替える（シングルコーテーション）
Ctrl+Shift+7 と同じ
Ctrl＋~
表示形式を外します。[全般] 数値形式にします。（チルダ）
Ctrl+Shift+^ と同じ
Ctrl＋＾
指数表示にする。（キャレット）
Ctrl＋¥
選択した行内で、アクティブセルと合致しないセルを選択
Ctrl＋｜
選択した列内で、アクティブセルと合致しないセルを選択
Ctrl+Shift+¥ と同じ
DSUM関数
読み方： ディーサム
分類： データベース
DSUM(データベース,フィールド,条件)
条件を満たすレコードの合計を算出します