エクセルVBA

エクセル:関数

エクセルのちょっとしたテクニックを紹介します。


文字列を結合させる場合、普通に文字列同士をつなげるのならば問題ないのですが、例えば日付データと文字列を連結しようとすると日付の部分がきちんと表示されません。
CONCATENATE関数も&演算子も、書式を無視した値そのものを連結するからです。
きちんと表示させるには以下の方法でできますよ。


(1)結果を表示したいセルを選択します。
(2)選択したセルに
="文字列" & TEXT(日付の入力されているセル,"yyyy年m月d日")
または、
=CONCATENATE("文字列",TEXT(日付の入力されているセル,"yyyy年m月d日"))と入力します。

例)A1セルにはいっている「2007年4月1日」といった日付と「有効期限:」という文字列を結合させる場合、

="有効期限:" & TEXT(A1,"yyyy年m月d日")
または
=CONCATENATE("有効期限:",TEXT(A1,"yyyy年m月d日"))と入力します。


※上記のような例としては、日付データの他に、時間データ、カンマ区切りの数字などがあります。表示したい書式がわからなければ、セルの書式設定の表示形式を参考にしてください。


エクセルのちょっとしたテクニックを紹介します。

文字列を置き換える場合、REPLACE関数の他にSUBSTITUTE関数があります。SUBSTITUTE関数は指定した文字列を別の文字列に変換します。

(1)結果を表示するセルを選択する。
(2)選択したセルに=SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象の位置)と入力します。

例)セルA1に「エクセル2007」と入力されていたものをセルB1で「ワード2007」と表示させる場合、

セルB1に=SUBSTITUTE(A1,"エクセル","ワード",1)と入力します。


※文字列に検索文字列で指定した文字列が含まれていない場合は置換えされずにそのまま返されます。また、検索文字列で指定した文字列が複数含まれている場合は、置換対象の位置で何番目の文字列を置き換えるか指定する事ができます。省略した場合は1番目とみなされます。


エクセルのちょっとしたテクニックを紹介します。

文字列を繰り返すには、REPT関数を使います。REPT関数は指定した回数分、繰り返した文字列を表示します。


(1)結果を表示するセルを選択する。
(2)選択したセルに=REPT(文字列,繰り返し回数)と入力します。
例)セルA1に「エクセル」と入力されていたものをセルB1で「エクセルエクセル」と表示させる場合、

セルB1に=REPT(A1,2)と入力します。


※繰り返し回数に0を指定すると空白文字列が返されます。また、繰り返し回数に負の数を指定するとエラー値#VALUE!が返されます。


エクセルのちょっとしたテクニックを紹介します。

文字列を置き換える場合はREPLACE関数を使います。REPLACE関数は開始位置を指定し、その位置から指定した文字数分を別の文字列に置き換えます


(1)結果を表示するセルを選択する。
(2)選択したセルに=REPLACE(文字列,変換する文字列の開始位置,変換する文字数,置換文字列)と入力します。
例)セルA1に「1個」と入力されていたものをセルB1で「100個」と表示させる場合、
セルB1に=REPLACE(A1,1,1,100)と入力します。


※開始位置に1より小さい数字を指定した場合、エラー値#VALUE!が返されます。文字数は省略可能です。省略した場合は置換えは行われず、指定した開始位置の直前に置換文字列が付加され、文字列が返されます。


エクセルのちょっとしたテクニックを紹介します。

CONCATENATE関数は複数の文字列を結合させる関数で、結合した文字列を一つのセルに出力することが出来ます。文字列(引数)には文字列や数値、又は文字列を含むセル参照を指定します。

(1)結果を表示したいセルを選択します。
(2)選択したセルに =CONCATENATE(文字列1,文字列2)と入力します。

例)A1とA2のセルの文字列を結合し、A3に表示する場合、
セルA3に =CONCATENATE(A1,A2)と入力します。


&演算子を使用してもCONCATENATE関数と同じ結果が返されるので、CONCATENATE関数を使用して文字列を結合させる代わりに、セルA3に =A1&A2のように&演算子を使用して文字列を結合させる事もできます。


エクセルのちょっとしたテクニックを紹介します。

日付を入力する度に、該当する曜日をいちいち入力するのは面倒ですよね。関数で曜日を表示する方法があります。


(1)曜日を表示したいセルを選択する。
(2)選択したセルに =CHOOSE(WEEKDAY(日付のセル,1),"日","月","火","水","木","金","土")と入力。
(3)セルに曜日が表示されます。


WEEKDAY関数は日付を曜日に変換し、該当する値を返す関数です。
 =WEEKDAY(日付,値)
値の部分は、1:日曜日が1〜土曜日が7、2:月曜日が1〜日曜日が7となります。今回の関数は、WEEKDAY関数が返した値を基にCHOOSE関数で引数リストから該当する引数を表示するというものです。


また、CHOOSE関数は自分で作った引数リストから該当する数字を入力するだけで表示させることができます。いろいろと応用が利くので、使ってみてくださいね。





エクセルのちょっとしたテクニックというより今回は基本を紹介します。


エクセルでは、IF関数や条件付書式などでは条件を設定する際に「以上」や「以下」などといった表現を記号で表します。その記号を比較演算子といい、以下のような意味になります。


・「A>B」 ⇒ AよりBが大きい
・「A>=B」 ⇒ AはB以上
・「A<B」  ⇒ AはBより小さい (未満)
・「A<=B」 ⇒ AはB以下
・「A<>B」 ⇒ AとBが等しくない
・「A=B」  ⇒ AとBは等しい



算数の不等号みたいなものですね。再確認の意味も込めて、いまさらですが、比較演算子でした。


エクセルのちょっとしたテクニックを紹介します。


エクセルでSUM関数を使った合計行のある表にフィルタを設定し、データを抽出した場合、合計欄には、抽出されたデータだけでなく対象となるセル範囲に含まれるすべてのデータの合計が表示されます。そのため、抽出したデータのみの合計を求めたい場合は別途計算が必要になるのですが、SUM関数の代わりにSUBTOTAL関数を使うと、抽出されたデータのみの合計を合計欄に表示させることができます。


(1)合計を表示するセルを選択。
(2)選択したセルに、数式 =SUBTOTAL(9,集計範囲)を入力します。
(例)セルB2からB100が集計範囲の場合、=SUBTOTAL(9,B2:B100)と入力。

(3)オートフィルタを使ってデータを抽出すると、抽出されたデータのみの合計が表示されます。

SUBTOTAL関数では、リストの集計に使用する関数を1〜11の番号で指定します。なお、Excel2003では、条件によって1〜11以外に101〜111の番号を指定することも可能です。1と101は関数は同じですが、非表示となっているセルは、集計しません。

リストの集計に使用する関数は以下の通りです。
1:AVERAGE関数,2:COUNT関数,3:COUNTA関数,4:MAX関数,5:MIN関数,6:PRODUCT関数,7:STDEV 関数,8:STDEVP 関数,9:SUM関数,10:VAR関数,11:VARP関数


エクセルの集計テクニック


エクセルの表を行列の挿入・削除で修正をしていくと、どこに計算式を設定したかわからなくなりませんか?数式の入ったセルを全て表示させて確認することができます。


(1)[ツール]−[オプション]を選択します。
(2)「オプション」ダイアログボックスの[表示]タグを表示します。
(3)[ウィンドウオプション]−[数式]をチェックして[OK]を押下します。
(4)シート上の全ての数式が表示されます。


※元に戻すには、[数式]のチェックをはずしてください。また、数式にあわせてセル幅が変わります。

いまさらですが、ROUND関数についての説明です。
数式を使ってパーセント表示させたとき、割り切れずに小数点以下何桁も表示されたりしたことがありますよね。そういった場合、指定した桁数で四捨五入して表示させる関数です。


(1)表示させたいセルを選択します。
(2)選択したセルに=ROUND(桁数指定したい数字またはセル,端数処理する桁数に対応する値)と入力します。
例)セルA1に123.45という数値があり、小数点第2位で四捨五入する場合、
=ROUND(A1,1)と入力します。
(3)指定した桁数で数字が表示されます。


※端数処理する桁数に対応する値については、小数点第3位の場合:2、小数点第2位の場合:1、小数点第1位の場合:0、1の位の場合:-1、10の位の場合:-2・・・となります。

※指定した桁数で切り上げ処理をするROUNDUP関数、指定した桁数で切り下げ処理をするROUNDDOWN関数も仕組みは同じです。


エクセル関数


エクセルで平均を求めるというと、通常、AVERAGE関数が思い浮かびますが、AVERAGE関数は数値データの平均を求める関数のため、引数に含まれる文字列、論理値、空白セルは無視されても、「0」という値が入力されているセルは平均の対象に含まれてしまいます。未入力のデータが「0」として存在している場合など、「0」のデータは無視して平均を求めたい場合は、AVERAGE関数ではなく、数値データの合計を求めるSUM関数、数値データが入力されているセルの個数を求めるCOUNT関数、条件に一致するセルの個数を求めるCOUNTIF関数を使って平均を求めます。


(1)計算結果を表示したいセルを選択します。
(2)選択したセルに、数式
=SUM(平均を求めるセル範囲)/(COUNT(平均を求めるセル範囲)-COUNTIF(平均を求めるセル範囲,0)) を入力します。
 
(例)平均を求めるセル範囲が C15:N15 の場合。
=SUM(C15:N15)/(COUNT(C15:N15)-COUNTIF(C15:N15,0))

(3)これで、指定したセル範囲のうち、「0」値のセルを除いたセルのデータのみで平均が求められます。


03-1234-5678という電話番号を03と1234-5678というように分割したい場合は、文字列操作関数を使い、市外局番の後ろにある"-"を基準に左側にある文字列と右側にある文字列を求めます。


(1)市外局番を求めたいセルに
=LEFT(電話番号が入力されているセル,FIND("-",電話番号が入力されているセル,1)-1)と入力します。

(例)セルF2に電話番号が入力されている場合、
=LEFT(F2,FIND("-",F2,1)-1)と入力します。

(2)LEFT関数とFIND関数により、最初の"-"より左にある市外局番が求められます
(3)市外局番+加入者番号を求めたいセルに
=MID(電話番号が入力されているセル,FIND("-",電話番号が入力されているセル,1)+1,9)と入力します。

(例)セルF2に電話番号が入力されている場合、
=MID(F2,FIND("-",F2,1)+1,9)と入力します。

(4)MID関数とFIND関数により、最初の"-"より右にある市内局番+加入者番号が求められます。


※(3)のMID関数の最後で指定している引数(文字数)の"10"は、市外局番+加入者番号の最大桁数を9桁と仮定して設定しています。桁数が増えた場合は、増えた桁数に合わせて変更してください。


指定範囲のセルの中から、検索条件に一致するセルの個数を求めるCOUNTIF関数と、論理式の結果によって指定した値を返すIF関数を使い、指定したデータがリスト内にあるかどうかを判定する方法です。


(1)結果を表示するセルを選択します。
(2)選択したセルに、数式
  =IF(COUNTIF(範囲,検索条件),"真の場合の値","偽の場合の値")を入力します。

例)セル範囲A3:F50に、セルA1に入力したデータと一致するデータがあるかないかを確認し、"○" "×"で返す場合、
  =IF(COUNTIF(A3:F50,A1),"○","×") と入力。

(3)これで、指定したデータがリスト内にあるかどうかが求められ、結果が表示されます。


入力したデータが検索範囲内にいくつ存在するかを求めたい場合は、IF関数にネストせず、COUNTIF関数のみで数式を作成します。


エクセル関数
エクセルで作成した住所録等で、1件ごとふりがなを入力してませんか?
名前・住所等にふりがなを振りたいときに、簡単にふりがなを振るPHONETIC関数という便利な関数があります。


(1)データを表示したいセルを選択。
(2)選択したセルに数式 =PHONETIC(範囲)と入力。
例)B1のセルに文字列があった場合、=PHONETIC(B1)と入力します。
(3)対応するふりがなが表示されます。


ふりがなは一般的なふりがなが表示されるため、すべてが正しいふりがな表示とは限りません。PHONETIC関数を使うことで1件ごとに入力しなくても良くなりますが、ふりがなが正しいかどうかの確認は必ずしてくださいね。


エクセル関数
他の表計算アプリケーションで作成されたデータを取り込むと、数字が文字列として入力されてしまうことがあります。文字列として認識されたデータは計算の対象から外れてしまうため、計算の対象としたい場合は、数値に変換して使用します。


(1)変換後の数値を返すセルを選択します。
(2)選択したセルに、数式=VALUE(文字列)を入力します。
(例)セルA1に入力されている数字を数値に変換する場合
   =VALUE(A1)と入力します。
(3)指定した文字列が数値に変換されて表示されます。


文字列には、エクセルが認識できる数値、日付、時刻を指定します。それ以外の値を指定すると、エラー値(#VALUE!)が返されます。


エクセルの書籍

他のアプリケーションから読み込んだテキストに不要なスペースが含まれて、同じデータなのに違うものとエクセルで認識されてしまい、集計などが上手くいかないことってありますよね。
そういった場合にTRIM関数を使うと不要なスペースを消すことができます。


(1)データを表示したいセルを選択。
(2)選択したセルに数式 =TRIM(データの入ったセル番地)と入力。
 例)B1のセルに変換したい文字列がある場合、=TRIM(B1)と入力します。
(3)不要なスペースが消えます。


※文字列に複数のスペースが連続して含まれている場合、単語間のスペースを 1 つずつ残して、不要なスペースをすべて削除します。TRIM関数では全角も半角も同じ1文字として認識されます。


エクセルの書籍

SUMIF関数は、その名の通りSUM関数IF関数の機能を併せ持ったもの、つまり、条件にあったデータのみ集計する関数です。

(1)集計結果を表示したいセルを選択。

(2)選択したセルに数式 =SUMIF(範囲,検索条件,合計範囲)と入力。

例)B1:B5に数値が入力されていて、B6セルにマイナスの値だけを合計する場合、
B6セルに =SUMIF(B1:B5,"<0",B1:B5)と入力して[Enter]キーを押す

上の「範囲」は検索条件に当てはまるかどうかを調べたいセル範囲を指定します。「検索条件」は検索条件を入力し、「合計範囲」は実際に計算対象にしたい(合計したい)セル範囲を指定します。また「検索条件」にはワイルドカードも使えます。

エクセル
エクセルには、300以上もの関数が用意されていて、ユーザーが使いやすいように、関数の入力を助けてくれるダイアログも用意されています。このダイアログには、目的や分類による検索など、複数の検索方法が用意されていますが、頭文字を使って関数をすばやく検索する方が簡単です。

(1)数式バーにある[関数の挿入]ボタンをクリックします。

(2)[関数の挿入]ダイアログが表示されたら、[関数の検索]には何も入力せず、[関数の分類]は[すべて表示]のまま、[関数名]のリスト内をクリックします。

(3)日本語入力がオフの状態で関数の頭文字のキーを押すと、その文字から始まる関数が選択されます。頭文字が同じ関数が複数ある場合は、キーを押すたびに次の関数が選択されるので、目的の関数が選択されたときに[OK]ボタンをクリックします。

※Excel2000の場合、[挿入]メニューから[関数...]を選択すると、[関数の貼り付け]ダイアログが表示されます。ここで、(3)のように日本語入力がオフの状態で関数の頭文字のキーを押すと、その文字から始まる関数が順次選択されます。

エクセル書籍
数値の場合、指定した条件を満たすかどうかはIF関数だけで簡単に求めることができますが、文字列の場合、「○○という文字列を含む場合は〜」といったようなあいまいな条件指定がIF関数だけではできません。これは、条件の指定にワイルドカードを使えないからなのですが、COUNTIF関数を組み合わせることにより、あいまいな条件指定が可能になります。

(1)結果を表示するセルを選択。

(2)選択したセルに、数式=IF(COUNTIF(範囲,条件),真の場合,偽の場合)を入力します。

(例)セルA1に「Excel」という文字列が含まれている場合は「○」を表示し、

   そうでない場合は空白にする場合、

       =IF(COUNTIF(A1,"*Excel*"),"○","") と入力。

(3)数式を入力したセルに判断結果が表示されます。

文字列の全角半角、大文字小文字も区別されるため、条件を指定するときは注意してください。

エクセル
小計と合計のある表では、それぞれの欄にSUM関数を入力しなければなりませんが、あらかじめ小計欄と合計欄を選択し、オートSUMボタンをクリックすると、小計と合計を一度に入力することができます。


(1)[Ctrl]キーを押しながら、小計欄を選択。
(2)[標準]ツールバーの[オートSUM]ボタンをクリックします。
(3)小計欄にはデータを合計するSUM関数が入力されます。


(4)[Ctrl]キーを押しながら、合計欄を選択。
(5)[標準]ツールバーの[オートSUM]ボタンをクリックします。
(6)合計欄には小計欄を合計するSUM関数が挿入されます。


※小計欄と合計欄を選択する際、選択する順序や方法を間違えると正しい数式が入力されません。

エクセルの書籍
excelをExcel、OFFICEをOfficeにするなど、アルファベットの先頭文字を大文字にし、2文字目以降の英字を小文字に変換したい場合は、文字列操作関数のPROPER関数を使うと簡単です。

(1)変換後の文字列を表示するセルを選択。

(2)選択したセルに、数式 =PROPER(文字列)を入力します。

(例)セルC1に変換したい文字列が入力されている場合、
=PROPER(C1)と入力します。

(3)数式を入力したセルに、変換後の文字列が表示されます。

文字列中の英字以外の文字は変換されません。また、文字列に英字が含まれていない場合は、文字列がそのまま返されます。
合計を表示する関数ですが、エクセルで最もポピュラーな関数かもしれません。


(1)合計を表示するセルを選択します。

(2)選択したセルに数式 =SUM(範囲)と入力します。
例)B1からB5までの合計をB6に表示する場合、B6に=SUM("B1:B5")と入力します。


※[オートSUM]ボタンを使用するときは、B1からB5までドラッグして[エンター]ボタンを押します。また、複数の範囲の合計を計算するときは、数式=SUM(範囲1,範囲2,・・・)とカンマで区切って入力します。
IF関数を使い、計算結果が0の場合に"−"(ダッシュ)を表示する方法を紹介します。

(1)計算結果を表示したいセルを選択します。

(2)選択したセルに、数式を入力します。

=IF(論理式,真の場合に返す値,偽の場合に返す値)

(例)セルA1-A2の計算結果が0の場合は"−"を表示し、0以外の場合は計算結果をそのまま表示する場合。
=IF(A1-A2=0,"-",A1-A2)

(3)これで、計算結果が0の場合、"−"が表示されるようになります。


今回の"-"の部分を""にすれば、計算結果が0の場合、セルは0という数字ではなく空白になります。IF関数は引数を変えることで、任意の文字列を表示したり、空白にしたりすることもできるので、いろいろと応用できます。

エクセルの本
2つのデータを照合し、整合性をチェックするEXACT関数を紹介します。英数字の大文字と小文字、また、全角と半角といった違いも区別できるので、一つ一つ見て確認する前にこのEXACT関数を使い、FALSEの部分だけチェックすれば、データの照合が簡単になります。


(1)照合した結果を表示させるセルを選択します。

(2)選択したセルに、数式を入力します。

=EXACT(比較するセル1,比較するセル2)

(例)セルA1とB1に入力されているデータの整合性をチェックしたい場合、
=EXACT(A1,B1) と入力します。

(3)2つのデータがまったく同じである場合は"TRUE"、そうでない場合は"FALSE"が返されます。


※文字の大きさや色といった、書式設定の違いについては区別されません。

エクセルの書籍
今回はエクセルのCLEAN関数の説明です。

基本的に印刷できない文字を削除するCLEAN関数ですが、印刷できない文字の中に改行コードが含まれるため、この関数を使ってセル内の改行を削除することができます。

(1)改行を削除したデータを表示したいセルを選択。

(2)選択したセルに、数式を入力します。

=CLEAN(改行されているデータが入力されているセル番地)

(例)セルA1に入力されているデータの改行を削除したい場合、

=CLEAN(A1) と入力します。


CLEAN関数を使う場合、改行コードの他にも印刷できない文字が含まれていると、それらの文字も一緒に削除されます。

エクセル
今回は、文字列に含まれる半角英数カナ文字を全角英数カナ文字に変換するJIS関数を使い、文字列に含まれる数字を全角に統一する方法を紹介します。


(1)変換後のデータを表示したいセルを選択します。

(2)選択したセルに、数式を入力します。

=JIS(変換したいデータが含まれているセル番地)

(例)セルA1に変換したいデータが含まれている場合、=JIS(A1)と入力します。

(3)文字列に含まれる数字が全角に変換され、表示されます。


JIS関数は、文字列に含まれる半角英数カナ文字を変換する関数のため、文字列に半角アルファベットや半角かな文字が含まれていた場合、同時に全角に変換されます。
今回はエクセルで使うASC関数の説明です。

データを集計する際、入力方法の違いにより全角数字と半角数字が混ざってしまうことがありますよね。そういった場合、文字列内の全角英数カナ文字を半角英数カナ文字に変換するASC関数を使うと便利です。


(1)変換後のデータを表示したいセルを選択します。

(2)選択したセルに、以下の数式を入力します。

=ASC(変換したいデータが含まれているセル番地)

(例)セルA1に変換したいデータが含まれている場合、=ASC(A1)と入力します。

(3)文字列に含まれる数字が半角に変換され、表示されます。


このASC関数は、文字列内の全角英数カナ文字を変換する関数のため、文字列に。全角アルファベットや全角カナ文字が含まれていた場合、同時に半角に変換されます。

エクセル
エクセル
〜ちょっとしたテクニック〜
Add to Google My Yahoo!に追加 ブックマークに追加する
Author

エステリア

エクセルの基本から小技までちょっとしたテクニックを随時提供中

ご案内
●役に立ったと思った方は●

人気blogランキング

1日1回のクリックでこのブログが育ちます。応援よろしくお願いします。
----------------------------------------------
●運営ポリシー●

エクセルのいろんな機能を随時紹介していきます。
トラバ、コメント大歓迎です。内容を確認の上、掲載します。アダルトサイトからのトラバ、コメントはご遠慮下さい。
楽天市場