この章ではREPLACE関数について説明します。 REPLACE関数は指定した文字列の一部を「指定した開始位置から設定した文字数」だけ取り出して「他の文字」で置き換えます。
以下の例で説明します。
アルファベットを含んだパスワードの先頭文字を消して、数字だけのパスワードに変更すると言う設定です。
初めにC2をクリックしてください。 次は「数式」タブの中の「文字列操作」をクリックしてください。 クリックしますと次のメニューが現れますので、「REPLACE」をクリックします。 クリックしますと次のダイアログボックスが現れます。 「文字列」には文字を取り出す対象の文字である「B2」、
「開始位置」はB2の文字から文字を取り出す開始位置なので、この例では先頭位置である「1」,「バイト数」には対象の文字から何文字取り出すかを指定するので「1」、「置換文字列」にはパスワードの先頭文字を消したいのでカラ文字のを指定します。
は何もないと言う意味なので文字を消すことが出来ます。
ここまで入力しますと次のように入力されますので、エンターキーで確定してください。 結果は以下の通りです。
カテゴリー: エクセル
PHONETIC関数について知ろう
この章ではPHONETIC関数について説明します。
[基本構文]
=PHONETIC(参照)
PHONETIC関数は漢字に「ふりがな」を付ける機能があります。
初めにB2をクリックしてください。 次は「数学」タブの「その他の関数」をクリックしてください。 クリックしますと次のメニューが現れますので「情報」にカーソルを合わせてください。 次に以下のメニューが現れますので、その中の「PHONETIC」をクリックします。 クリックしますと次のダイアログボックスが現れますので「A2」と、入力してOKをクリックしてください。 ここまで入力しますと次のように表示されますので、エンターキーを押してください。 結果は以下の通りです。 ここで注意していただきたいことがあります。
「近藤清美」と言う漢字をセルに入力するとき「こんどうきよみ」と入力して漢字変換しましたが、これを「ちか」を変換して「近」、「とう」を変換して「籐」などと入力するとふり仮名が「ちかとう」となってしまうので注意してください。
また、どこからかコピーしてきてセルに貼り付けてもふり仮名は表示されません。
コピーしたときには自身でふり仮名を登録してください。
登録方法は初めにコピーして貼り付けを行ったセルをクリックしてください。 次は「ホーム」タブの中の次の赤丸の箇所をクリックしてください。 クリックしますと次のメニューが現れますので「ふりがな」の編集をクリックします。 クリックしますと次のように文字の上にふり仮名が表示されるので正しければエンターキーを、間違えているのならば修正してからエンターキーを押してください。
LEFT関数. RIGHT関数について知ろう
この章ではLEFT関数とRIGHT関数について説明します。 LEFT関数は文字の「左端」(先頭)から数えて「文字数で指定した数」だけ取り出します。文字数を省略すると「1」を指定したのと同じになります。
全角文字も半角文字も1文字として数えます。 また、似たような関数としてRIGHT関数があります。これはLEFT関数と逆で、文字の「右端」(末尾)から数えて「文字数で指定した数」だけ取り出します。
LEFT関数との違いはこれだけです。
では次の例ではセルに書かれている名前から「苗字」と「名前」を取りだします。
初めにB2をクリックしてください。 次は「数式」の「文字列操作」をクリックしてください。 クリックしますと次のメニューが現れますので、その中の「LEFT」をクリックしてください。 クリックしますと次のダイアログボックスが開きます。 「文字列」には取り出す対象の文字列「A2」,「文字数」は取り出す対象の文字列「A2」から何文字取り出すのかです。
ここにはと、書いてください。
これは空白の位置をFIND関数で求めて、ここから「1」を引いています。
なぜ「1」を引いているのでしょうか。
例えば「近藤 清美」の場合は空白の位置は「3」ですので、この「3」から「1」を引けば「2」になりますので、苗字が何文字であろうと「苗字」の数を「文字数」に指定することが出来ます。
つまりB2はが入り、これでエンターキーをクリックします。
結果は次のようになります。 次は「名前」を取り出すので、C2をクリックしてください。 「名前」を取り出すにはRIGHT関数が必要です。
RIGHT関数はLEFT関数と同じメニューの中にありますので、クリックしてください。
クリックしますと次のダイアログボックスが現れます。 「文字列」には取り出す対象の文字列「A2」,「文字数」は取り出す対象の文字列「A2」から何文字取り出すのかですので、と書いてください。
ここで、まだ説明していないLEN関数が有りますので、説明
します。
この関数は指定した文字列の文字数を数える関数です。 「LEN(A2)」は「A2」の文字数を数えているので「5」です(空白も含めます)。
つまり、では文字数から空白文字の位置番号を引いているので「2」になります。
C2はが入り、これでエンターキーをクリックします。
結果は次のようになります他の人もオートフィル機能を使って、「苗字」「名前」を入れてみてください。
FIND関数について知ろう
この章ではFIND関数について説明します。 FIND関数では対象のセル内から、検索したい文字列を探して、見つかったのであれば左から数えて初めに見つかった位置の番号を得ることが出来ます。
FIND関数を使って以下の姓と名の間の空白の位置が何番目にあるのかを探してみましょう。
姓と名の間の空白には「半角のスペース」が入っています。 初めにB2をクリックしてください。
次は「数式」の「文字列操作」をクリックしてください。 クリックしますと次のメニューが現れますので、その中の「FIND」をクリックしてください。 クリックしますと次のダイアログボックスが現れます。 「検索文字列」には検索したい文字を入れます。この例では半角の空白を探しているので(ダブルクォートの間に半角のスペースを入れます)と、入力します。
ダブルクォークの間に全角の空白が入っていると正しく検索できませんので注意してください。
また、検索文字列は空白の他に「A」や「a」などの文字も検索できますが、半角大文字と半角小文字は違う文字として扱われます(例えば「A」や「a」は違う文字として扱う)。「対象」は調べたい文字列なので「A2」を入れます。
つまり、ここまでの意味は調べたい文字A2の中でが何番目にあるかを調べるということになります。
「開始位置」は調べたい文字の何番目から検索をするかを意味します(次の図参考)。
「開始位置」は省略が可能ですが、その場合には左端から検索がされることになります(この例では省略しています)。
「開始位置」の文字の数え方はを例に取りますと次のようになります。 つまり検索開始位置を変えるとFIND関数の結果も変わることになります。
ダイアログボックスの「検索文字列」「対象」に入力しOKをクリックしますと結果は以下の通りです。
結果は「3」になります。
この章では以下の出勤管理表を作りますが、ここには多くの関数が使われているので順番に説明していきます。 以下の「出社」「退社」は実際に会社に到着した時間です。 以下の「出社」はCEILING関数を使用して15分単位で切り上げていて、「退社」はFLOOR関数を使用して15分単位で切り下げています。 「時」ではHOUR関数を使用して、時間表示の「時」の部分を取り出して、「分」ではMINUTE関数を使用して、時間表示の「分」の部分を取り出しています。
ではCEILING関数から説明します。 CEILING関数は指定した基準値に最も近い値に数値を切り上げます。
切り上げ後の数値は指定した「数値」より大きくなります。
ここではB2の時刻をCEILING関数で変換しますので、初めにB7をクリックします。 次に「数式」タブの中の「数学/三角」をクリックしてください。 クリックしますと次のメニューが現れますので、その中の「CEILING」をクリックします。 クリックしますと次のダイアログボックスが現れますので、「数値」に「B2」,「基準値」にを入力してください。 15分単位に切り上げを行う場合(15分単位で時間管理)は30分単位の場合はです。
この例の「8:31」と言う時間は15分単位なのでに切り上げされます。
という表現はTIME関数でも表すことが出来ます。
「基準値」の箇所にという形式で書くのですが、この例では15単位で管理したいのでと書きます。
「時」「秒」の部分は使わないので「0」で結構です。
CEILING関数は時間管理だけに使われるのではなく、様々な用途があります。
例えば10個単位で仕入したい商品があるとします。
その時、間違えて21個と間違えて入力したとしても「基準値」をのように「10」(10本単位)にすることで、21と言う数字は切り上げされて「30」に自動で変わるようなシステムを作ることができます。
次はFLOOR関数について説明します。 FLOOR関数は指定した基準値に最も近い値に数値を切り下げます。
切り下げ後の数値は指定した「数値」より小さくなります。
初めに次のC7をクリックしてください。 FLOOR関数はCEILING関数と同じメニューの中にありますので、「FLOOR」を左クリックしてください。
クリックしますと次のダイアログボックスが現れますので「数値」に「C2」、「基準値」にを入力してください。
15分単位に切り上げを行う場合(15分単位で時間管理)は30分単位の場合はです。
この例の「17:34」という時間は15分単位なのでに切り下げされます。 FLOOR関数は時間管理だけに使われるのではなく、様々な用途があります。
例えば10個単位で仕入したい商品があるとします。
その時、間違えて21個と間違えて入力したとしても、次のように「基準値」をと「10」(10本単位)にすることで、「21」という数字は切り下げされて「20」に自動で変わるようなシステムを作ることができます。
ここまでは以下の通りです。 次はHOUR関数について説明します。 HOUR関数は指定したシリアル値から「時」のみを整数で得ることが出来ます。
シリアルについて説明します。
エクセルではなどの時間や時刻はシリアルという数値で管理されています。
日付の場合は1900年1月1日を1として、1日経過するごとに1ずつ増えていきます。
時刻の場合は0時0分0秒を0として、1秒増えるごとにをプラスしていきます。
シリアル値を確認する方法は初めに時間が書かれているセルをクリックしてください。
次に右クリックしますと次のメニューが現れますので、その中から「セルの書式設定」をクリックします。 クリックしますと次のダイアログボックスが現れますので「表示形式」の中の「標準」をクリックしてOKを押してください。 上の表の「17:30」の箇所のシリアル値を確認するとと表示されます。
見た目は「17:30」と言う形式で表示していますが、エクセル内部はと言う数値で管理されています。
では「合計」欄から「時」を取り出してみましょう。
「時」を求める前に「合計」を算出します。
「合計」欄はで求めます。
つまりで求めることが出来ます。 結果は以下の通りです。 では「時」を求めますので、初めに次のE7をクリックします。 次に「数式」の中の「日付/時刻」をクリックしてください。 クリックしましたら次のメニューが現れますので、その中の「HOUR」をクリックしてください。 クリックしますと次のダイアログボックスが現れますので、「シリアル値」にD7を指定してください。 結果は次のようになります。 次は「合計」欄から「分」を取り出してみましょう。
取り出すにはMINUTE関数が必要です。 MINUTE関数は指定したシリアル値から「分」のみを整数で得ることが出来ます。
初めに次のF7をクリックしてください。 次にMINUTEを使用するのですが、先ほどのHOUR関数と同じメニューの中にあります。
そのメニューの中にあるMINUTEをクリックしますと次のダイアログボックスが現れますので、その中の「シリアル値」にD7を入力してOKを押してください。 結果は次のようになります。 これで関数の部分は以上になりますが、最後に「日給」の計算をしてみます。
G7の「日給」の部分は次のように書いてください。 時給1000円に期間をかけて、日給を導きだしています。
結果は以下の通りです。 後はオートフィルで簡単にすべての人の時給を計算することが出来ます。
SUMPRODUCT関数について知ろう
この章ではSUMPRODUCT関数について説明します。 SUMPRODUCT関数は配列の要素間(配列1×配列2)を掛け算して、その結果を足し算します。
初めに「合計金額」の右横のB6をクリックしてください。 次に「数式」の中の次の赤丸の箇所をクリックしてください。 クリックしましたら、次のメニューが現れますので、その中の「SUMPRODUCT」を選択してください。 クリックしますと次のダイアログボックスが現れます。 「配列1」は「単価」の下の数字を入れますので、B2からB5を入れます。
「配列2」は「個数」の下の数字を入れますので、C2からC5を入れます。
ここまでしますと次のダイアログボックスのように「範囲」が指定されますのでOKを押してください。 結果は次のようになります。 つまり、ここでしている計算は次の通りです。
ROUNDDOWN関数について知ろう
ROUNDUP関数について知ろう
この章ではROUNDUP関数について説明します。
ROUNDUP関数は指定した桁で数字を切り上げます。
[基本構文]
=ROUNDUP(数値,桁数)
ROUNDUP関数は指定した桁で数字を切り上げます。
四捨五入は指定した数値が5以上は切り上げされましたが、ROUNDUP関数は指定した数値がなんであろうと切り上げます。
ROUNDUP関数のある場所は次の通りです。
「数式」タブの中の「数学/三角」をクリックしてください。 クリックしますと次のメニューが現れますので、その中の「ROUNDUP」をクリックします。 では次の数値を十の位で切り上げてみましょう。 10の位で切り上げるので、B1はと、なります。
「桁数」は「-2」と、なります。
「桁数」の基本はROUND関数で説明した原理と同じですので、見直してみてください。
セルの値を指定した桁で切り上げた結果は次のようになります。
ROUND関数について知ろう
この章ではROUND関数について説明します。
ROUND関数は「四捨五入」を行う関数です。
[基本構文]
=ROUND(数値,桁数)
以下の数字「345,123.67」を小数第2位に四捨五入して、B1に入れます。 ではB1をクリックしてください。
次は「数式」タブの中の「数学/三角」をクリックしてください。 クリックしますと次のメニューが現れますので、その中の「ROUND」をクリックします。 クリックしますと次のダイアログボックスが現れますので、「数値」に「A1」,「桁数」に「1」を入力してください。 「桁数」にはどの桁で四捨五入するのかを指定します。
例えば小数第2位で四捨五入したい場合は「桁数」には「1」を指定します。
ここで注意してほしいことは小数第2位の場合「2」ではないということです。
つまり、四捨五入して数値が変わるのは小数第1位なので「1」を指定します。
次の図を見てください。 [小数点以下]
小数第1位の「桁数」は「1」、小数第2位の「桁数」は「2」を意味します。
[整数部分]
1の位の「桁数」は「0」、10の位の「桁数」は「-1」、100の位の「桁数」は「-2」…となります。
小数点第2位で四捨五入した結果は次のようになります。 では先ほどと同じA1の数字で、1000の位を四捨五入してみましょう。
この例では「5」の部分です。
1000の位を四捨五入したいので、「桁数」は実際に処理される箇所である「-4」になります。
結果は次のようになります。
VLOOKUP関数について知ろう
この章ではVLOOKUP関数について説明します。
[基本構文] VLOOKUP関数は、データを検索する関数ですが、検索範囲の中で、条件に一致したデータを取り出してくれます。
以下の商品データを使って説明します。商品データから何かを購入したら、購入品をA10から下に書いていくという仕様です。 まず初めに「ヨーグルト」を購入しますので「0002」と、書いてください。
次に「商品名」の下のセルをクリックします。 クリックしましたら「数式」タブの中から「検索/行列」をクリックしてください。 クリックしますと次のメニューが現れますので、「VLOOKUP」をクリックしてください。 クリックしますと次のダイアログボックスが現れますので、
「検索値」は検索をしたいデータなので、「0002」のデータが入っているA10をクリックしてください。 「範囲」には検索をしたい範囲なのでA2からD7までをドラッグするか、直接入力してください。この範囲は絶対参照で固定するので「$」を付けてください。
「列番号」には「2」を入力します。列番号とは次のように左から1,2,3…と数えます。この例の場合は「商品名」を得たいので「2」を入力します。 「検索方法」はFALSEを指定しますと検索値とすべて合致するものだけを検索します。
TRUEと指定しますと検索値と合致するものがない時には範囲の中から近い値を検索します。
すべてを入力しましたらOKを押します。
結果は次のように「ヨーグルト」が入力されます。 「単価」にも「商品名」と同じ手順でと書いてエンターキーを押してください。
「単価」の列番号は3番目なので「列番号」は「3」と、書いてください。
結果、以下の通り、単価が入ります。 次は「個数」に「2」を入力して、「合計」の下のセルにと、書いてください。 「=C10*D10」は単価に個数を掛けています。
「*」は掛け算を意味します。
結果は次のようになります。 この後に商品番号と個数を書いていきます。 ここまで書きましたら、「商品名」「単価」「合計」はオートフィル機能を使ってドラッグするだけで、次のようになります。 後は最後にSUM関数で合計を付け加えれば終わりになります。 結果は以下の通りです。 ここで補足として説明したいことが2つあります。
1つ目は「検索方法」のTRUEについてです。
「検索方法」のTRUEについては次のように説明しました
=>「検索方法」はFALSEを指定しますと検索値とすべて合致するものだけを検索します。TRUEを指定しますと検索値と合致するものがない時には範囲の中から近い値を検索します。
では次の例を見てください。 「商品名」の箇所のVLOOKUP関数の「検索方法」をTRUEに変更します。
そして、「購入品」の箇所に「0007」と書いてください。
「0007」と言う商品番号の商品名はありませんので、「0007」に近い商品番号の商品名が適用されることになりますので、ここでは「みかん」になります。 2つ目の補足は商品検索で商品が無い場合、エラー表示が出てしまうのですが、このエラー表示を消す方法について説明します。
たとえば次の例のように商品番号がない場合(「0009」の商品はありません)、とエラー表示されます(FALSEの場合)。 このエラー表示が出ないようにするにはIFERROR関数を使います。
具体的には次のように書きます。
意味は「もしエラーの場合はと言うカラ文字を入れます」です。
これをB10に書きます。