テーブルのデータから、上位20%のデータを抽出したい場合には、どのような条件を設定して、クエリを作ったらいいのでしょうか。
50件のテーブルを用意しました。
作成タブのクエリデザインをクリックします。
得点フィールドの並べ替えに「降順」をします。
降順にすることで、「上位」を設定できます。
抽出条件に、「20%」を設定するわけではありません。
クエリデザインタブにある、「トップ値」をつかって、設定します。
そこで、20%と入力することで設定することができます。
Facebookページに書いた、Excelの豆知識（Trivia）です。
10月15日
Excel。
DEGREES関数
読み方： ディグリーズ
DEGREES(角度)
ラジアンを角度に変換する
10月16日
Excel。
DELTA関数
読み方： デルタ
DELTA(数値1,[数値2])
2つの数値が等しいかどうか調べる
10月17日
Excel。
DEVSQ関数
読み方： ディブスクウェア
読み方： ディビエーションスクエア
DEVSQ(数値1,[数値2])
数値の偏差平方和を算出する
10月18日
Excel。
DGET関数
読み方： ディーゲット
DGET(データベース,フィールド,条件)
データベースから1つの値を抽出します
10月19日
Excel。
DISC関数
読み方： ディスク
読み方： ディスカウント
DISC(受渡日,満期日,現在価値,償還価額,[基準])
割引債の割引率を算出します
10月20日
Excel。
DMAX関数
読み方： ディーマックス
DMAX(データベース,フィールド,条件)
条件を満たすレコードの最大値を算出します
10月21日
Excel。
DMIN関数
読み方： ディーミニマム
DMIN(データベース,フィールド,条件)
条件を満たすレコードの最小値を算出します
FLOOR関数
読み方： フロア
分類： 互換性
FLOOR(数値,基準値)
指定した数値の倍数に切り捨てる
データに重複しているものがあれば、データタブの「重複の削除」をつかうと、データは削除されてしまいます。
また、条件付き書式の「セルの強調表示ルール」にある「重複する値」をつかうことで、塗りつぶしたりすることで、重複を確認することができます。
今回は、重複している場合、「重複」という文字を表示するには、どのようにしたらいいのかを紹介します。
重複されていることがわかった時点で、C列に「重複」を表示したいわけです。
重複は2件以上という意味なので、全体の中で、そのデータが何件あるのかがわかれば、いいわけですね。
何件あるのかということですから、使用する関数は、COUNTIF関数をつかうと対応することができます。
C2にCOUNTIF関数をつかった数式を設定します。
=IF(COUNTIF($B$2:B2,B2)>=2,"重複","")
あとは、オートフィルで数式をコピーすれば、完成です。
重複しているデータには、重複という文字が表示されていることが確認できます。
では、数式の説明です。
最初のIF関数は、条件に合致したら、「重複」。
それ以外は「空白」を表示させるための関数です。
メインの関数がCOUNTIF関数です。
COUNTIF($B$2:B2,B2)>=2
最初の引数は、範囲です。
始点を絶対参照にすることで、オートフィルで数式をコピーすることで、対象範囲を拡張することができます。
2番目の引数は、検索条件です。
自分自身が何件あるのかを確認させます。
その結果が、2以上なら重複しているわけなので、重複と表示させることができるというわけです。
Facebookページに書いた、Excelの豆知識（Trivia）です。
10月8日
Excel。
DCOUNT関数
読み方： ディーカウント
DCOUNT(データベース,フィールド,条件)
条件を満たすレコードの数値の個数を算出します
10月9日
Excel。
DCOUNTA関数
読み方： ディーカウントエー
DCOUNTA(データベース,フィールド,条件)
条件を満たすレコードの空白以外のセルの個数を算出します
10月10日
Excel。
DDB関数
読み方： ディーディービー
読み方： ダブルディクライニングバランス
DDB(取得価額,残存価額,耐用年数,期,[率])
減価償却を定率法で算出する
Double-Declining Balance methodの略
10月11日
Excel。
DEC2BIN関数
読み方： デックトゥビン
読み方： デジマルトゥバイナリ
DEC2BIN(数値,[桁数])
10進数を2進数に変換する
10月12日
Excel。
DEC2HEX関数
読み方： デックトゥヘックス
読み方： デジマルトゥヘキサデジマル
DEC2HEX(数値,[桁数])
10進数を16進数に変換する
10月13日
Excel。
DEC2OCT関数
読み方： デックトゥオクト
読み方： デジマルトゥオクタル
DEC2OCT(数値,[桁数])
10進数を8進数に変換する
10月14日
Excel。
DECIMAL関数
読み方： デシマル
DECIMAL(数値,基数)
n進数を10進数に変換する
3-D集合縦棒グラフは、リボンのボタンに用意されていますが、どのようにしたら、円柱・円錐・ピラミッドといったグラフをつくることができるのでしょうか。
まずは、3-D集合縦棒などのグラフを挿入する必要があります。
書式タブの「選択対象の書式設定」をクリックします。
データ系列の書式設定作業ウィンドウが表示されます。
今回は、円柱にしてみます。
＜SUMIF関数＞
商品名ごとに並んでいる表があります。
商品名ごとに累計を算出したいのですが、どのようにしたら、手早く算出することができるのでしょうか。
累計は、SUM関数をつかって、始点を絶対参照にした数式をつくることで、算出することができます。
=SUM($C$2:C2)
始点を絶対参照で留めることで、終点はオートフィルで数式をコピーすると範囲が拡張されていきます。
よって、累計を算出することができるというわけです。
そして、商品別累計を算出したいわけなので、条件が追加されています。
よって、SUMIF関数をつかい、累計と同じように、始点を絶対参照にすればいいわけです。
では、E2にSUMIF関数をつかった数式を設定します。
=SUMIF($B$2:B2,B2,$C$2:C2)
あとは、オートフィルで数式をコピーすれば、商品別累計を算出することができます。
累計のSUM関数と異なる点は、最初の引数の範囲と、三番目の引数の合計範囲の始点を絶対参照にしています。
FIXED関数
読み方： フィックスト
分類： 文字列操作
FIXED(数値,[桁数],[桁区切り])
数値を四捨五入してカンマを使った文字列に変換する
今回は、WORKDAY関数～WRAPROWS関数までをご紹介しております。
WORKDAY関数
読み方： ワークデイ
分類： 日付時刻
WORKDAY(開始日,日数,[祭日])
稼働日数後の日付を算出します
WORKDAY.INTL関数
読み方： ワークデイ・インターナショナル
分類： 日付時刻
WORKDAY.INTL(開始日,日数,[週末],[祭日])
週末（曜日指定OK)と祝日を除いた日数後の日付を算出します
WRAPCOLS関数
読み方： ラップコルズ
読み方： ラップカラムズ
分類： 検索/行列
WRAPCOLS(vector,wrap_count,[pad_with])
指定した数の値の後に列で折り返（ラップ）します
WRAPROWS関数
読み方： ラップロウズ
分類： 検索/行列
WRAPROWS(vector,wrap_count,[pad_with])
指定した数の値の後に行で折り返（ラップ）します
天気予報で、一日の温度を表すために、次のようなマーカー内に気温の数値が表示された折れ線グラフをつかっています。
折れ線グラフのために、次の表を用意しました。
挿入タブの「折れ線／面グラフの挿入」にある「マーカー付き折れ線グラフ」をクリックします。
データラベルを表示すると、マーカーからはみ出してしまうので、マーカーを大きくします。
マーカーをクリックします。
書式タブの「選択対処の書式設定」をクリックします。
これで、マーカーが大きくなります。
大きくなったマーカー内にデータラベルを表示します。
作業効率もUPする、知っていると便利なショートカットキー。
なお、Excelのバージョンによって多少変わります。
Ctrl＋Shift＋↑
上側に連続するセルを範囲選択する
Ctrl＋Shift＋→
右側に連続するセルを範囲選択する
Ctrl＋Shift＋↓
下側に連続するセルを範囲選択する
Ctrl＋Shift＋←
左側に連続するセルを範囲選択する
単純に順位を算出したいのなら、RANK.EQ関数をつかえばいいわけですね。
データの件数を算出するならば、COUNTA関数かCOUNT関数をつかうことで算出することができます。
ただし、COUNT関数は、「数値」の算出することができる関数です。
通し番号のような数値の列がない場合、COUNTA関数をつかわないと算出することができませんので、注意が必要です。
では、D2に次のように数式をつくります。
=RANK.EQ(C2,$C$2:$C$7,0)&"/"&COUNTA($A$2:$A$7)
あとは、オートフィルで数式をコピーして完成です。
数式内の「&"/"&」を説明しておきましょう。
「&（アンパサンド）」をつかうことで、文字結合をすることができます。
セル内の余計な空白を削除するには、TRIM関数という便利な関数が用意されていますが、それだけでは、対応することができません。
TRIM関数は、前後の余計な空白と、文字内の連続している空白を1つだけにします。
C2には、
=TRIM(A2)
と数式を設定して、オートフィルで数式をコピーします。
どのデータも前後の空白は削除されていますし、文字内の空白にも対応しています。
ですが、よくみると、C3やC5は、半角空白にならず、全角空白になっています。
TRIM関数では、半角文字を全角文字にするまでは、やってくれません。
そこで、文字を置換することができるSUBSTITUTE関数を合わせてつかうことで、対応することができます。
=TRIM(SUBSTITUTE(A2," "," "))
あとは、オートフィルで数式をコピーすれば完成です。
SUBSTITUTE(A2," "," ") の説明をします。
最初の引数は、文字列。A2を選択します。
2番目の引数は、検索文字列。” “と全角空白を設定します。
3番目の引数は、置換文字列。” “と半角空白を設定します。
資料を作ろうとしても、データがガタガタだと作成することができませんので、綺麗に整えるスキルを知っておくのもいいかもしれませんね。
FISHERINV関数
読み方： フィッシャーインバース
分類： 統計
FISHERINV(y)
フィッシャー変換の逆関数の値を算出します。
Facebookページに書いた、Excelの豆知識（Trivia）です。
10月1日
Excel。
DATESTRING関数
読み方： デイトストリング
DATESTRING(シリアル値)
西暦の日付を和暦の日付に変換する
10月2日
Excel。
DATEVALUE関数
読み方： デイトヴァリュー
DATEVALUE(日付文字列)
日付を表す文字列をシリアル値に変換する
10月3日
Excel。
DAVERAGE関数
読み方： ディーアベレージ
DAVERAGE(データべース,フィールド,条件)
条件を満たすレコードの平均を算出します
10月4日
Excel。
DAY関数
読み方： デイ
DAY(シリアル値)
日付から日を算出する
10月5日
Excel。
DAYS関数
読み方： デイズ
DAYS(終了日,開始日)
2つの日付の間の日数を算出する
10月6日
Excel。
DAYS360関数
読み方： デイズ360
DAYS360(開始日,終了日,[方式])
1年を360日として2つの日付の間の日数を算出します
10月7日
Excel。
DB関数
読み方： ディービー
読み方： ディクライニングバランス
DB(取得価額,残存価額,耐用年数,期,[月])
減価償却を旧定率法で算出します
fixed-Declining Balance methodの略
列内の空白セルに、手早く同じ文字列を入力したい場合には、どのようにしたらいいのでしょうか。
そこで、今回は、Excel VBAで対応してみましょう。
次の表を用意しました。
プログラムをつくってみました。
今回は、C2:C7と範囲が決まっているので、一行だけのプログラム文にしてみました。
Sub 空白セル入力()
Range("c2:c7").SpecialCells(xlCellTypeBlanks) = "未提出"
End Sub
まずは、実行してみます。
このプログラム文を説明します。
「SpecialCells(xlCellTypeBlanks)」で、セルが空白なのかどうかを確認しています。
なので、空白ならば、「=”未提出”」なので、未提出と入力することができたというわけです。
ExcelをはじめとしたMicrosoft Officeのテクニックを紹介するYouTubeチャンネルです。
https://www.youtube.com/@ExcelOfficeChannel/featured
【Excel】数式をコピーしたら固定しておきたい、参照先がズレる。そんな時には、絶対参照の出番でしょう。
【Word】強調表示に太字だけでは物足りない。そこで、傍点（ぼうてん）をつかってみるのは、どうでしょうか。
【Excel】セル内の好きな場所で「改行」したい場合は、ショートカットキーを使うのが断然便利です。
【Excel・Word・PowerPoint】ビックリ！ワードアートのサイズ変更は「変形」する・しないで、変わります。
【Excel】知っていると便利。離れた箇所の範囲選択でCtrlキーをつかわなくてもいい、ショートカットキーがあります。
FISHER関数
読み方： フィッシャー
分類： 統計
FISHER(x)
フィッシャー変換の値を算出します
ホームタブにある、条件付き書式「上位/下位ルール」の「上位」をつかうことで、最大値のデータがあるセルに、塗りつぶすなど、書式を設定することができます。
では、セルではなく、データ全体。
つまり、行全体を塗りつぶすには、どのようにしたら、いいのでしょうか。
A2:F10を範囲選択します。
ホームタブの条件付き書式にある「新しいルール」をクリックします。
「次の数式を満たす場合に値を書式設定」のボックスに、次の数式を設定します。
=MAX($F$2:$F$10)=$F2
あとは、書式ボタンをクリックして、塗りつぶす色を設定したら、OKボタンをクリックします。
アンケート結果をはじめ、社員の年代別の人数を把握する度数分布を算出したい場合、FREQUENCY関数をつかうことで、手早く算出することができます。
これをD2:D6の区分でわけるとします。
なお、D2:D5までは、表示形式のユーザー定義で、「～」を表示させています。
また、D6は、表示形式のユーザー定義で「～」を後ろに表示させています。
つまり、D2:D6は、数値で入力されており、D2は、29という数値というわけです。
あとは、FREQUENCY関数で設定するだけです。
D2には、次の数式を設定します。
=FREQUENCY(B2:B16,D2:D5)
スピル機能によって、数式の設定範囲が自動的に拡張されます。
このFREQUENCY関数のポイントは、2つ目の引数である「区間配列」です。
D2:D5としているように、その他（それ以上）に該当するD6は、含んでいません。
以前のExcelですと、FREQUENCY関数は配列関数だったので、Ctrl+Shift+Enterが必要でしたが、スピル機能のあるバージョンでは不要です。
Facebookページに書いた、Excelの豆知識（Trivia）です。
9月24日
Excel。
CUBESET関数
読み方： キューブセット
CUBESET(接続,セット式,[キャプション],[並べ替え順序],[並べ替えキー])
キューブからセット式を返す
9月25日
Excel。
CUBESETCOUNT関数
読み方： キューブセットカウント
CUBESETCOUNT(セット)
キューブセットにある項目数を返す
9月26日
Excel。
CUBEVALUE関数
読み方： キューブバリュー
CUBEVALUE(接続,[メンバー式1],…)
キューブから指定したセットの集計値を返す
9月27日
Excel。
CUMIPMT関数
読み方： キュムアイピーエムティー
読み方： キュムラティブ・イントレスト・ペイメント
CUMIPMT(利率,期間,現在価値,開始期,終了期,支払期日)
元利均等返済における指定期間の金利累計を算出します
9月28日
Excel。
CUMPRINC関数
読み方： キュムプリンク
読み方： キュムラティブ・プリンシプル
CUMPRINC(利率,期間,現在価値,開始期,終了期,支払期日)
元利均等返済における指定期間の元金返済額累計を算出します
9月29日
Excel。
DATE関数
読み方： デイト
DATE(年,月,日)
指定した日付を算出する
9月30日
Excel。
DATEDIF関数
読み方： デイトディフ
DATEDIF(開始日,終了日,単位)
2つの日付の間の年・月・日数を算出する
レポート提出を確認するテーブルがあります。
今回は、「更新クエリ」をつかって、「未提出」という文字を入力します。
作成タブの「クエリデザイン」をクリックします。
テーブルを選択します。
提出条件に「null」と入力すると、「Is Null」に変わります。
抽出条件の「Is Null」は、空白という意味です。
あとは、実行ボタンをクリックして確認してみましょう。
あとは、更新できたのかを確認しましょう。
なお、「更新クエリ」は作り慣れていない、あるいは、条件が複雑なときは、間違ったまま実行すると、取り返しのつかない事態が発生する可能性が高くなります。
まずは、条件に合致するデータを抽出する選択クエリをつくり、その後、「更新クエリ」に変換していくといいでしょう。
FINV関数
読み方： エフインバース
分類： 互換性
FINV(確率,自由度1,自由度2)
F分布の上側確率から確率変数を算出する
どのような数式が設定されているかを確認したい場合、数式タブの「数式の表示」を使う方法もありますが、これだと、結果を見ながら修正するには、向いてません。
また、シート上すべての数式が表示されるなど、見づらくなってしまいます。
C2に、
=FORMULATEXT(B2)
という数式を設定するだけで、B2の数式を表示することができます。
今日から納期日までの日数を算出するのに、TODAY関数だけでは、算出後が日付で表示されます。
C2に、
=B2-TODAY()
というように、セル番地から直接TODAY関数をつかって残りの日数を算出すると、算出結果は、日付型で表示されてしまいます。
日付型で表示されてしまうと、このあと、シリアル値に戻さないと、何日なのか視認できません。
そこで、DAYS関数をつかった数式にしてみます。
=DAYS(B2,TODAY())
あとは、オートフィルで数式をコピーしています。
DAYS関数をつかうと、シリアル値で算出されたことがわかります。
表示形式を変えるだけの、ちょっとした差ですが、DAYS関数をつかうことで、単純だけど面倒な作業をしなくてすみます。