無料
na×5のblog 2007年04月
fc2ブログ

範囲中で○番目に小さい数を返す
データの中で特定の順位のデータを返す関数です。
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で連続して選択します)

シートがすべて選択出来たら、タイトルバー作業グループと表示されるのでこの状態で一番上のシートデータを入力します。

作業グループを解除したいときはシート名を一つ選ぶと解除できます。
 
入力後作業グループを解除するとデータが選んだシート全てに入ります。
書式・数式も移るので一括して計算したいときも便利です。
INT関数とROUNDDOWN関数の違い
INT関数は小数点以下を切り捨てる関数。
ROUNDDOWN関数は指定した桁数で切捨てす関数。

では、ROUNDWN関数で小数点以下を切り捨てたらINT関数と同じ結果が得られるか・・・・

結果は正の数なら同じ結果になります。
いかし、負の数では同じ結果になりません。ROUNDDOWN関数は単に切り捨てと定義しているのに対し、
INT関数は数値を超えない最大の整数を返すと定義しているからです。

正の数でINTの定義を行うと結果的に小数点以下が切り捨てられたように見えます。
しかし、負の数ではただ単に切捨てをすると元の数を越えてしまうことになります。


 5.5⇒ROUNDDOWNの結果 5
    ⇒INTの結果 5
-5.5⇒ROUNDDOWNの結果 -5
    ⇒INTの結果 -6
     (元の数を越えることは出来ないので、-5という答えは得られない為)

正の数では、INT関数のほうが桁指定が無いので便利ですが、負の数のときは注意が必要です。
規定のシート数を増やしたい
エクセルの通常の起動時のシート数(規定のシート数)は3枚です。
コレを変更する方法です。

メニューバーのツール⇒オプション⇒全般タブ のなかの

新しいシートのブック数 の部分を変更すれば起動時のシートの数を希望の数にすることができます。




データの抽出方法
表内の該当するデータだけ抽出したいときは、
オートファイルタを使うと便利です。

表内のどこでもよいので選択して

メニューバー⇒データ⇒フィルタ⇒オートファイルタ

コレで、項目に下向き矢印が出てくるので
そこをクリックしてリストから選択すると抽出が出来ます。

また、リストからオプションを選択すると複雑な条件(20以上30以下や"田"を含むなど・・)の抽出も可能です。
文字列のふりがなを表示する
文字列にふりがなを振るときの操作方法です。

メニューバーの書式⇒ふりがな⇒表示非表示

入力時の読み方がカタカナ出てきます。コレを編集したいときは

メニューバーの書式⇒ふりがな⇒編集

表示をひらがなにしたいときは

メニューバーの書式⇒ふりがな⇒設定

です。
文字としての数を数値に変える
例えば、文字列操作関数で(LEFT関数RIGHT関数)取り出した数が計算対象になっていないことってありませんか。

コレは、見かけ上は数でもエクセルが数値として認識していなしから
計算対象にないと考えるから起こります。

いちばん簡単な方法は=LEFT(・・省略・・)*1という形に文字列操作関数の後に1をかける方法です。
後はVALUE関数を用いて
=VALUE(LEFT(・・省略・・))とすると結果が数値そして扱えます。
・・・ただし、どちらも結果が全て数の時しか使えないです。
文字の一部分を取り出す
セルの中の文字列から、指定した文字数分を取り出す関数を紹介します。

LEFT関数
RIGHT関数
MID関数
です。

左から取り出したいときはLEFT
左から取り出したいときはRIGHT
途中を取り出したいときはMID。です。

使い方
=LEFT(文字列、文字数)
例えば文字列がABCDEで文字数が2なら、ABという結果になります。
RIGHT関数も同じ使い方です。

MID関数はコレに開始位置が加わります。
ただし基準は左から数えます。
=MID(文字列、開始位置、文字数)
例えば文字列がABCDEで開始位置が2で文字数が3なら、BCDという結果になります。



経過時間を求める(DATEDIF関数)
開始日と終了日を入力(選択)して、その間の経過期間を求める関数があります。
ただし、関数の挿入ダイアログボックスを探しても見つからないので、セルに直接関数を入力していきます。

使い方
=DATEDIF(開始日、終了日、単位)

開始日、終了日には日付を表す文字列や、日付に対応するシリアル値を指定します。
単位は"Y"と入れると経過年数が、"M"と入れると経過月数が、"D"を入れると経過日数が出力されます。

また、"MD"入れると1ヶ月未満の残りの日数。"YM"と入れると1年未満の残り月数。"YD"と入れると1年未満の残り日数が出力されます。

ちなみに終了日にTODAY関数を使用すると、ファイルを開くたびに
その日までの経過時間が計算できて便利です。
検索関数(CHOOSE関数)
IF関数を使って条件に当てはまったときに表記(動き)を決めることが出来ますが、同じ要領で条件に当てはまった時の表記を決める関数があります。
ただし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まで全てうめなくても使えます。
曜日を表示する(WEEKDAY関数編)
エクセル曜日を表示する関数で一番簡単なものは、WEEKDAY関数です。
使い方は凄くシンプルなんですが、表示結果に問題があります。
日曜日を 1 月曜日を 2 火曜日を 3  ・・・
と表示してきます。
なので、CHOOSE関数などと組み合わせないと、あまり使い道がないです。簡単なのに使い道が限られています。

使い方
=WEEKDAY(シリアル値)
です。
シリアル値は日付の入ったセルを選択するだけでOKです。

でも結果は1,2,3・・・・となります。

CHOOSE関数を組み合わせるなら
=CHOOSE(WEEKDAY(シリアル値)、日曜日、月曜日、火曜日、・・(略)・・、土曜日)
とします。
CHOOSE関数の詳しい使い方は、CHOOSE関数の記事を確認して下さい。
表内の式のコピー
表内の式のコピー、フィルハンドルを使って長い表の下までドラッグ・・・
ちょっと大変ですよね。

そこで、フィルハンドルをダブルクリックします。
そうすると、縦もしくは横に式がコピーされます。

途中、セル結合やデータの切れ目があると使えませんが、シンプルな表であれば問題なしです。

曜日を表示する(TEXT関数編)
曜日を表示する方法はいくつかありますが、ココではTEXT関数を使います。

使い方は簡単です。
=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関数
INDEX関数は範囲指定と領域番号を使って、特定の範囲を選択して行番号と列番号からセルを特定して,データを出力する関数。

=INDEX(配列、行番号、列番号)

配列の中で行番号と、列番号が交差するデータを出力するのが目的です。

まずは九九の表で練習するのが分かりやすいと思います。
九九の表を作成して、配列で九九の答えになる部分を選択して、列番号と行番号とそれぞれ指定します。
九九の表の中から答えが出てきます。
別の表を参照する関数(HLOOLUP関数)
別表になるデータを検索する関数です。
使い方はVLOOUP関数と同じですが,検索するのが列ではなく行です。

横に向かって検索値が広がっていて、データが下に向かって広がっているときに使います。

=HLOOUP(検索値、範囲、行番号、検索の型)

範囲の上端行で検索の値を求め、それに対応するセルの値を返します。
別の表を参照する関数(VLOOKUP関数)
VLOOKUP関数の目的は別の表にあるデータを検索して表示することです。

使い方は
=VLOOKUP(検索値、範囲、列番号、検索の型)

指定した範囲の表の左側の列から検索値を探し、列番号で指定した列の値をとりだす。という意味です。
ここで、範囲選択は基本的には絶対参照になります。

まずは簡単な一覧表を作成して、番号と商品名をいれて、
番号を入れたら一覧表から商品名が拾えるように練習するのがいいかと思います。
答えが0になるところは空欄にしたい
合計が「0」だったときセルに「0」と表示するのではなく、空欄にしたいときの方法です。

メニューバーのツール⇒オプション⇒表示タブの中のゼロ値のチェックを外します。

コレで0は表示されません。


IF関数の論理式を増やしたい
IF関数を使っていると論理式の条件をもっと増やしたいという場合があります。
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を入力します。
IF関数の論理式不一致を表す記号
Copyright © na×5のblog. all rights reserved.