SMALL関数。
使い方
=SMALL(範囲選択、順位)
です。
順位は何番目に小さいデータが欲しいのかを入れます。
ちなみに○番目に大きいデータを検索するのはLARGE関数。
使い方
まずは集計したいフィールドを並べ替えます。
昇順でも降順でもOKで、データをかたまっていることが目的です。
集計をする表の中をどこでも良いのでクリックして
後は
メニューバーのデータ⇒集計を選択します。
集計ダイアログボックスにおいてグループの基準は先ほど並び替えたフィールドになります。
後は集計をとりたいフィールドにチェックを入れてOKボタンを押します。
すると表内に新しく集計行が出来て集計が取れます。
解除の方法は集計のダイアログボックスのすべて削除を選択します。
セルを選択して、
メニューバーの編集⇒クリア⇒すべて
セルの中身は全て削除されます。
算数でいう虫食い算。
コレをエクセルで機能にしたのが、ゴールシークです。
使い方
メニューバー⇒ゴールシークを選択します。
数式入力セルに式の入っているセルを入れます。
目標値に数式の結果として目標にする値(数値)を入れます。
変化させるセルに値を出して欲しい部分(空のセルで数式に関係しているセル)を選択します。
例えばA1のセルに10と入力します。B1は空欄にします。
C1のセルに=A1*B1と入力します。(B1が空欄なので0と出ます。)
ここでゴールシークのダイアログボックスを出して、
数式入力セルのボックスにC1を選択して
目標値を50にします。
変化させるセルにB1を入れます。
OKボタンを押すと、答えとしてB1に5と表示されます。
簡単な式なら逆算すれば良いですが、複雑な式だと逆算するは大変です。
そういう時はゴールシークを使うととても便利です。
使い方
メニューバー書式⇒条件付書式を選択します。
後は、そのセルの値にあわせるのか、ほかのセルの値に合わせるのかを選択して、条件をつくり書式ボタンで書式を決めます。
一つのセルに対して最大で3つまで条件と書式をつけられます。
例えばセルの値が100を越えたらセルに赤の色を塗る。50を越えたらセルに青の色を塗る。というような場合に使います。
セルに色を塗ったり、項目を目立たせたり・・・・
というような作業を簡単にしたものです。
まず作成した表を範囲選択をして
メニューバーの書式⇒オートフォーマットを選択します。
オートフォーマットダイアログボックスでオプションボタンを押して
下に出てきたチェックボックスの幅と高さのチェックを外します。
(外さなくても出来ますが、列幅などが自動で変わるのを防ぐため)
好きなデザインを選んで
後はOKボタンで完了です。
まず、もとのデータを範囲選択します。コピーをして
行と列を入れ替えて貼り付けたいところを選びます(一つのセルでOK)
後は、
メニューバーの編集⇒形式を選択して貼り付け を選びます。
形式を選択して貼り付けダイアログボックスの一番下の
行列を入れ替えるのチェックボックスにチェックをつけます。
コレで元のデータの行と列が入れ替わって張り付きます。
入力してから複写もしくはシートの複製という方法もありますが、
シートにデータを入力する段階でシート名を全て選択します。(選択方法はCtrlを使うか、Shiftで連続して選択します)
シートがすべて選択出来たら、タイトルバーに作業グループと表示されるのでこの状態で一番上のシートにデータを入力します。
作業グループを解除したいときはシート名を一つ選ぶと解除できます。
入力後作業グループを解除するとデータが選んだシート全てに入ります。
書式・数式も移るので一括して計算したいときも便利です。
ROUNDDOWN関数は指定した桁数で切捨てす関数。
では、ROUNDWN関数で小数点以下を切り捨てたらINT関数と同じ結果が得られるか・・・・
結果は正の数なら同じ結果になります。
いかし、負の数では同じ結果になりません。ROUNDDOWN関数は単に切り捨てと定義しているのに対し、
INT関数は数値を超えない最大の整数を返すと定義しているからです。
正の数でINTの定義を行うと結果的に小数点以下が切り捨てられたように見えます。
しかし、負の数ではただ単に切捨てをすると元の数を越えてしまうことになります。
例
5.5⇒ROUNDDOWNの結果 5
⇒INTの結果 5
-5.5⇒ROUNDDOWNの結果 -5
⇒INTの結果 -6
(元の数を越えることは出来ないので、-5という答えは得られない為)
正の数では、INT関数のほうが桁指定が無いので便利ですが、負の数のときは注意が必要です。
コレを変更する方法です。
メニューバーのツール⇒オプション⇒全般タブ のなかの
新しいシートのブック数 の部分を変更すれば起動時のシートの数を希望の数にすることができます。
オートファイルタを使うと便利です。
表内のどこでもよいので選択して
メニューバー⇒データ⇒フィルタ⇒オートファイルタ
コレで、項目に下向き矢印が出てくるので
そこをクリックしてリストから選択すると抽出が出来ます。
また、リストからオプションを選択すると複雑な条件(20以上30以下や"田"を含むなど・・)の抽出も可能です。
メニューバーの書式⇒ふりがな⇒表示非表示
入力時の読み方がカタカナ出てきます。コレを編集したいときは
メニューバーの書式⇒ふりがな⇒編集
表示をひらがなにしたいときは
メニューバーの書式⇒ふりがな⇒設定
です。
コレは、見かけ上は数でもエクセルが数値として認識していなしから
計算対象にないと考えるから起こります。
いちばん簡単な方法は=LEFT(・・省略・・)*1という形に文字列操作関数の後に1をかける方法です。
後はVALUE関数を用いて
=VALUE(LEFT(・・省略・・))とすると結果が数値そして扱えます。
・・・ただし、どちらも結果が全て数の時しか使えないです。
LEFT関数
RIGHT関数
MID関数
です。
左から取り出したいときはLEFT。
左から取り出したいときはRIGHT。
途中を取り出したいときはMID。です。
使い方
=LEFT(文字列、文字数)
例えば文字列がABCDEで文字数が2なら、ABという結果になります。
RIGHT関数も同じ使い方です。
MID関数はコレに開始位置が加わります。
ただし基準は左から数えます。
=MID(文字列、開始位置、文字数)
例えば文字列がABCDEで開始位置が2で文字数が3なら、BCDという結果になります。
ただし、関数の挿入ダイアログボックスを探しても見つからないので、セルに直接関数を入力していきます。
使い方
=DATEDIF(開始日、終了日、単位)
開始日、終了日には日付を表す文字列や、日付に対応するシリアル値を指定します。
単位は"Y"と入れると経過年数が、"M"と入れると経過月数が、"D"を入れると経過日数が出力されます。
また、"MD"入れると1ヶ月未満の残りの日数。"YM"と入れると1年未満の残り月数。"YD"と入れると1年未満の残り日数が出力されます。
ちなみに終了日にTODAY関数を使用すると、ファイルを開くたびに
その日までの経過時間が計算できて便利です。
ただしCHOOSE関数の場合条件が限られています。
1~29の数字のみに反応します。
1 ならば○○ 2 ならば○○ ・・・という感じです。
1,2,3とう数字は必ずしも数値で無くても大丈夫です。文字としての1,2,3でも可能です。見かけ上1,2,3・・・であればOKです。
使い方
=CHOOSE(インデックス、値1、値2、値3、・・・)
です。
値1、値2、値3、の部分を引数リストと呼びます。
インデックスの値を引数リストから順番に探していきます。
インデックスが1ならば、値1に入っているものが表示されます。
ちなみに、引数リストは1~29まで全てうめなくても使えます。
使い方は凄くシンプルなんですが、表示結果に問題があります。
日曜日を 1 月曜日を 2 火曜日を 3 ・・・
と表示してきます。
なので、CHOOSE関数などと組み合わせないと、あまり使い道がないです。簡単なのに使い道が限られています。
使い方
=WEEKDAY(シリアル値)
です。
シリアル値は日付の入ったセルを選択するだけでOKです。
でも結果は1,2,3・・・・となります。
CHOOSE関数を組み合わせるなら
=CHOOSE(WEEKDAY(シリアル値)、日曜日、月曜日、火曜日、・・(略)・・、土曜日)
とします。
CHOOSE関数の詳しい使い方は、CHOOSE関数の記事を確認して下さい。
ちょっと大変ですよね。
そこで、フィルハンドルをダブルクリックします。
そうすると、縦もしくは横に式がコピーされます。
途中、セル結合やデータの切れ目があると使えませんが、シンプルな表であれば問題なしです。
使い方は簡単です。
=TEXT(値、表示形式)
値には曜日を出したい日付を入れます。日付が入ったセルでもOKです。
表示形式には"aaa"と入れると曜日を略称した日本語の曜日が表示されます。月、火、水・・・と続きます。
"aaaa"と入れると曜日つきの日本語の曜日が表示されます。月曜日、火曜日、水曜日・・・・と続きます。
"ddd"と入れるとdayを省略した英語の曜日が表示されます。Mon,Tue,・・・・・と続きます。
"dddd"と入れるとdayつきの英語の曜日が表示されます。Monday,Tuesday,・・・・と続きます。
TEXT関数はそのほかにも使用方法があります。
例えば=TEXT(1234,"$#,###")と入力すると、
$1,234と表示されます。1234の部分はセルの選択でも大丈夫です。
表示形式の部分は自分で作ることが出来ます。
セルの書式設定のユーザー定義と同じですが、コチラは関数を使います。
ここでは、シリアル値を実際に確認します。
セルに13:00と入力します。午後1時なので一日の半分は過ぎています。
1日を1と考えるので半分の0.5は過ぎています。
入力したセルの表示形式を確認します。表示形式は時刻になっています。
コレを標準に変えます。
そうすると0.5416666666・・・という数が出てきます。
コレがシリアル値です。1日24時間の内約0.54%の時間であることを示しています。
シリアル値があるので、加算減算などの計算が出来ます。
日付は1900年の1月1日から1日ごとに1づつ増える整数値で、考えます。
なので、時刻は24時間を1として午前0時から1秒ごとに増える小数値で認識しています。
この整数値と小数値をシリアル値と呼んでいます。
=INDEX(配列、行番号、列番号)
配列の中で行番号と、列番号が交差するデータを出力するのが目的です。
まずは九九の表で練習するのが分かりやすいと思います。
九九の表を作成して、配列で九九の答えになる部分を選択して、列番号と行番号とそれぞれ指定します。
九九の表の中から答えが出てきます。
使い方はVLOOUP関数と同じですが,検索するのが列ではなく行です。
横に向かって検索値が広がっていて、データが下に向かって広がっているときに使います。
=HLOOUP(検索値、範囲、行番号、検索の型)
範囲の上端行で検索の値を求め、それに対応するセルの値を返します。
使い方は
=VLOOKUP(検索値、範囲、列番号、検索の型)
指定した範囲の表の左側の列から検索値を探し、列番号で指定した列の値をとりだす。という意味です。
ここで、範囲選択は基本的には絶対参照になります。
まずは簡単な一覧表を作成して、番号と商品名をいれて、
番号を入れたら一覧表から商品名が拾えるように練習するのがいいかと思います。
メニューバーのツール⇒オプション⇒表示タブの中のゼロ値のチェックを外します。
コレで0は表示されません。
コレは最大7つまで重ねることが出来るので、
条件は8つ設けることが出来ます。
お試しください。
IF関数は重ねて(ネスト)使用することが出来ますが、ネストではなく複数の条件をみたしたら、真としたい場合はAND関数とOR関数を使用します。
二つの違いは全ての論理式を満たさなければ真とならないANDに対してORはどれか一つでも論理式を満たしたら真となる関数です。
使い方は=IF(AND(論理式1、論理式2)、真の場合、偽の場合)
です。
AND関数OR関数のみでは使えないので注意が必要です。
ココで、余りを求める関数を紹介します。
MOD関数を使います。時間の計算など小数点以下で表記されても分かりにくい場合はとても便利です。
例えば138分という時間を○時間○分と表記したい場合
まず、小数点以下切捨ての関数(INT)関数を使って○時間の部分を出します。(時間と分は別のセルに答えを出します。)式は=INT(138/60)
次に、分を出したいセルに=MOD(138,60)と入力します。(もともと割り算の余りを求めることが目的の関数なので、「/」を間に入れる必要はないです。)
コレであまりの分数が出てきます。
http://toolbox.fuji-voice.com/?eid=100593
では、切り捨て・切り上げはどうするか・・・
指定した桁で処理を行う関数があります。
ROUND関数(指定した桁数で四捨五入)
ROUNDDOWN関数(指定した桁数で切り捨て)
ROUNDUP関数(指定した桁数で切り上げ)
使い方はどれも共通で=関数(数値の指定、桁数の指定)です。
桁数の指定ですが、小数点以下必要ない場合は0を、小数点第2位までの表示を希望の場合は2を、10の位まで表示(1の位を処理)したい場合は-1を入力します。
では、不一致は??
不一致を表す記号は「<>」です。
全く違うという意味を持つ記号です。