■ 使えない関数一覧


関数のヘルプを見た方が絶対速くて確実かな?

数学/三角関数
順位を調べる  (サンプルを見たほうが早いかも)
検索条件に一致するセルの合計を求める SUMIF
小数、整数を任意の桁数に変換する TRUNC
抽出されているデータだけを集計する SUBTOTAL
任意の単位の倍数に切り上げる CEILING
任意の単位の倍数に切捨てる FLOOR
数値を除数で割った時の余りを返す MOD
時間の切り上げ切り下げ(再掲) CEILING、FLOOR
   
日付/時刻関数 
日付から曜日を取り出す WEEKDAY
日付を表すシリアル値を返す DATE
文字列を日付けに変換する DATE  (応用編)
数値を時刻データに対応するシリアル値に変える TIME
   
文字列関数
文字列中にある特定の文字列の位置を返す FIND
文字数値のデータから文字だけを取り出す SUBSTITUTE と RIGHTB
文字を半角にする ASC
文字を全角にする JIS
文字列の左端から文字を取出す LEFT
文字列の左端から文字を取出す 2 LEFTB
文字列の指定されたキーワードを別の文字列に置き換える SUBSTITUTE
指定した位置にあるキーワードを別の文字列に置き換える REPLACE
セルとセルをつないで1つの文字列にする セル & セル
数値を様々な書式(表示形式)の文字列に変換する TEXT
数値を漢数字の文字列に置きかえる NUMBERSTRING
   
情報関数
空白セルかどうか調べる ISBLANK
セルがエラーかどうか調べる ISERROR
偶数かどうか調べる ISEVEN
奇数かどうか調べる ISODD
#N/A以外のエラーかどうか調べる ISERR
#N/Aエラーどうかか調べる ISNA
文字列かどうか調べる ISTEXT
文字列以外かどうか調べる ISNONTEXT
数値データかどうか調べる ISNUMBER
 
検索/行列関数
行番号を返す ROW
 
 
 
    
統計関数
重複した値を探す(条件を満たすデータの個数を数える) COUNTIF
空白のセルの個数を求める  COUNTBLANK




戻る

 

 






 

■ 文字列中にある特定の文字列の位置を返す

=FIND(検索文字列、対象、検索開始位置) 

 例えば住所録から「県」と言う文字を探す時、セルA1に「滋賀県甲賀郡土山町」と入力されていた時、「県」の文字は何文字目に有るかを探す場合、セルB1に、=FIND(”県”,A1)と入力する。 返る答えは「3」となる。
 これは、「県」と言う文字が左から数えて3文字目にあることを返す。

 

■ 文字数値のデータから文字だけを取り出す

 文字と数値が並んだデータから文字だけを取り出すには、

 例えばセルA1に「かめ33」と入力されているとして、セルB1に文字列「かめ」だけを表示したい。
 そんな時は、文字列関数を2つ組み合わせます。

 セルB1に
 =SUBSTITUTE(A1,RIGHTB(A1,2)、””””)
 と入力します。

 この意味は、セルA1の右から半角で2バイト分の文字を取り出して、それを ””””(ヌル)に置き換えろ、って意味。

 結果はB1に 「かめ」の文字列だけが表示されます。
 これを数式ではなく、結果としての文字として確定するには、
 コピー>形式を選択して貼り付け>値にチェックを入れて 「OK」

 これで文字として確定しました。

 

■ 行番号を返す

=ROW(範囲)

範囲には、行番号を調べるセル又はセル範囲を指定する。 連続しないセル又はセル範囲は指定できない。
省略した場合は、ROW関数の入っているセルが指定されたとみなされる。

偶数行、奇数行を判定する際に用いることも良くある。
=MOD(ROW(),2)
という計算式をA1からA10まで入れると、答えは「1、0、1、0、〜」となりますね。 これは、ROW関数の入力されているセルの行番号を2で割った時の余りを返しています。

■ 空白のセルの個数を求める

=COUNTBLANK(範囲)

セルにスペースで余白を入れた場合は、空白とはみなされない。
セルに ’ アポストロフィーを入れたら、これは空白としてカウントする。

 

■ 文字列を日付けに変換する

セル A1 に 20020425 が入力されているとします。 文字列でも、数字でも構いません。

セルB1に下記のように数式を書き込めば、
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

2002/04/25 と結果が表示され、値は日付け(シリアル値も)になるんです。

 

■ 数値を時刻データに対応するシリアル値に変える

=TIME(時,分,秒)

 時:
時を表す数値を0〜23の範囲で指定する
 :分を表す数値を0〜59の範囲で指定する
 :秒を表す数値を0〜59の範囲で指定する

 これらは,数値でも数式でもOK

 =TIME(8,15,34)
 は,8:15:34 を結果として表示する。

  例えば、下記のようにB1に時間が入力され、C1に計算式を入力したとする。
 結果は、「8:45」と表示される。
 返される表示形式については、各自訂正をする事。

  A         B               C                 
-----+-------+----------------------------------------+-
  1   8:37   =TIME(HOUR(A1),CEILING(MINUTE(A1),15),0)   
-----+-------+----------------------------------------+-

■ 重複した値を探す

 ちょっと感激。
列内の重複したデータを見つけて表示する方法。 関数だけを使って抽出できるんですね。

例えば、名簿を作成して、その中から重複している氏名を探し出すなんてこと、めんどっちいですよね。
これを、関数だけで見つけよう!!

 今、B列に名前がしこたま入っています。 で、A列が空欄としましょう。 空欄の先頭行はセルA2です。

セルA2に =IF(COUNTIF($B$2:B2,B2)>1,”重複”,” ”)

 と入力し、後は下へドラッグしてコピーするだけ。
 この場合のポイントは $B$2 の絶対参照を使う事。

$B$2:B2の間にあるB2の値を数え、その個数が1より大きかったら”重複”を表示し、そうでなければ””を表示する。
そして1行下がったら
$B$2:B3の間にあるB3の値を数え、その個数が1より大きかったら”重複”を表示し、そうでなければ””を表示する。
以下同様に、
$B$2:B10の間にあるB10の値を数え、その個数が1より大きかったら”重複”を表示し、そうでなければ””を表示する。

こうして、最初にある値は重複とせず、2個目以上からは”重複”と表示される。

 

■ 検索条件に一致するセルの合計を求める

=SUMIF(範囲,検索条件,合計範囲)

 
範囲   : 検索条件の評価の対象となるセル範囲を指定。 何を基準にするのか?
 検索条件: 検索する条件を、「数値」「式」「文字列」「セル」で指定する。
         「文字列」で指定する場合は ”” ダブルクォーテーションで囲む。
         「比較演算子」 = <> が使用可能。
         「セル」を指定した場合は、そのセルに数値、文字列を入力すること。
合計範囲 : 実際の計算の対象となるセルの範囲。 どこの範囲を合計するのか。

ファイルを見る 

ファイルをダウンロードする

 

■ 文字列の指定されたキーワードを別の文字に置き換える

=SUBSTITUTE(文字列,検索文字列,置換文字列,検索対象)

  文字列    :元の文字列を指定
 検索文字列  :変換したい元の文字列
 置換文字列  :変換後の文字列
 検索対象   :元の文字列の何番目に検索された文字列かを指定する
                 (例えば 3 を指定すると3番目に見つかった文字列を対象とする)



            A          B
----+----------+--------------------------------------------
 1| かめ3   | =SUBSTITUTE((A1),”かめ”,”カメ”,1)     →結果は カメ3
----+----------+--------------------------------------------
 2| かめ4   | =SUBSTITUTE((A1),”かめ”,”kame”,1)  →結果は kame4
----+----------+--------------------------------------------
 3| かめ5   | =SUBSTITUTE((A1),”かめ”,”亀”,1)   →結果は 亀5
----+----------+--------------------------------------------

 

■ 指定した位置にあるキーワードを別の文字列に置き換える 

=REPLACE(文字列,開始位置,文字数,置換文字列)

  文字列    :元の文字列を指定。
 開始位置   :元の文字列の中で置き換えたい文字の先頭位置を数値で指定する。省略は「1」
 文字数    :先頭位置から数えて何文字を置き換えの対象にするかを数値で指定。
 置換文字列  :元の文字列の一部と置き換えたい新しい文字列を指定する。

 

■ 数値データかどうか調べる

=ISNUMBER(セル)

 対象セルが数値データを参照する場合TRUEを返す。
 
 =IF(SINUMBER(A1),A1 + B1,”数値以外が入力されています”)

 これはもし,セルA1が数値ならば,セルB1を足しなさい。 そうでなく,もしセルA1が数値以外のデータであれば,
”数値以外が入力されています” と表示する計算式の例。


■ 文字列以外かどうか調べる

=ISNONTEXT(セル)

 対象セルが、文字列以外のデータを参照するときにTUREを返す。
 また、対象セルが空白セルの場合もTRUEを返す。


■ 文字列かどうか調べる

=ISTEXT(セル)

 
対象セルが文字列の場合、TUREを返す。

 =IF(ISTEXT(A1),VALUE(A1),”数値でした”)

 これはセルA1に 「’¥1000」 と文字で入力してある場合、当該セル(計算式を入力したセル)には数値で1000を表示させ、はじめから数値が入力されていれば”数値でした”と表示させる例。

 

■ 奇数かどうか調べる

=ISODD(数値またはセル)

 奇数の場合はTRUEを、偶数の場合はFALSEを返す。

 整数以外の数値の場合は、少数点以下が切り捨てられ評価される。
 数値以外のデータを評価すると、#VALUE!が返される。

 

■ 偶数かどうか調べる

=ISEVEN(数値またはセル)

 偶数の場合はTUREを,奇数の場合はFALSEを返す。

 整数以外の数値の場合は、少数点以下が切り捨てられ評価される。
 数値以外のデータを評価すると、#VALUE!が返される。
 

 

■ エラーかどうかを調べる

=ISERROR(セル)

 
対象セルが、任意のエラー値を参照しているときTUREを返す。
 
 #N/A、 #VALUE!、 #REF!、 #DIV/0!、 #NUM!、 #NAME? と言ったエラー値が出そうな計算式を参照するときに、
 =IF(ISERROR(対象セル),””,(対象セル))
なんて入力しておけばいいのかな?

■ #N/Aエラーかどうか調べる

=ISNA(セル)

 対象セルが,エラー値#N/Aを参照するときTRUEを返す。

 

■ #N/A以外のエラーかどうかを調べる

=ISERR(セル)

 対象セルが、#N/A以外のエラー値を参照するときTRUEを返す。

 

■ 空白セルかどうかを調べる

=ISBLANK(セル)

 対象セルが、空白かどうかを検査し、空白ならTRUEを返す。

 例えば、任意のセルに =IF(ISBLANK(A1),”お留守”,”在宅”)
なんて計算式を入れて,セルA1に何かを入れたり削除したりして実験結果を見る。

 

■ 時間の切り上げ、切り下げ

=CEILING(数値,基準値)

 
うちの会社のタイムカードは15分刻みです。 毎朝必死に走ってタイムカードを押すのですが、今朝は途中で転んで少し遅れました。 ガチンコとカードを入れたら、表示は 「8:22」 でした。 さて、今日の出社時間は何時扱いに!?

 このタイムカードの機械は CEILING関数を使っているんですねきっと。

 セルA1に 8:22 と入力し、セルA2に 0:15 と入力し、セルA3に =CEILING(A1、A2)と数式を入力します。
 セルA1、A2はともに時間の表示形式で入力しますので、シリアル値で計算されます。 よって、セルA3には当然時間を表示しますのでセルの表示形式を「h:mm」と設定しておきます。
 するとセルA3には15分を基準に(15分刻みで)天井に近い時間を表示します。結果は 8:30 
これなら、もう8分転んでいれば良かったわ、、

 あなたの会社が10分刻みでタイムカードを集計するなら、基準値に 0:10 と設定入力すれば良い訳ですね。

 また、退社時間を15分刻みで切り上げて計算するなら、FLOOR関数を使います。
 =FLOOR(A1、A2)とすれば、15分を基準に(15分刻みで)床に近い時間を表示します。

セルA1に17:19 と入力し、セルA2に 0:15 と入力し セルA3に =FLOOR(A1、A2)と入力すると結果は 17:15
これならもう3分早く帰ればよかった? ってことになるのかな?

文中数字をちと触りました。なんせ算数が苦手な かめ ですから。

■ 数値を除数で割った時の余りを返す

=MOD(数値,除数)

 数値:割算される方(分子)
 除数:割算する方 (分母) 但し0(ゼロ)を指定すると,ゼロ除算エラー(#DIV/0!)となる。
 余りは,除数と同じ符号がつく。

=MOD(-10,−3)  →結果は −1
=MOD(-10,  3) →結果は 2
=MOD(11.453,1) →結果は 0.453
=MOD(3945,1000) →結果は 945
=IF(MOD(ROW( ),2)=1,A1,” ”) →奇数の行に入っている列Aの数値だけを抜きだして表示する。

 

■ 数値を様々な書式(表示形式)の文字列に変換する

=TEXT(値,”表示形式”)

  
表の中に,日付を入れたいが,計算には反映させたく無い場合に便利かな?
   この式を&で繋げば一度に2つの表示形式を1つのセルに同居できる。
   但しこのデータは数値として扱うことは出来ない。

=TEXT(NOW( ),”yyyy/mm/dd”)    →結果は 2001/12/09
=TEXT(1500,”¥#,##0”)          →結果は ¥1,500
=TEXT(1500,”¥#,##0”)&”です。”   →結果は ¥1,500です。

 セルA1に 123
 セルB1に 45
  セルC1に 123-0045 と表示したい場合は C1の次のように入力しよう。
 
=TEXT(A1,”###”)&TEXT(B1,”-0000”)

 

■ 指定した日付を表すシリアル値を返す

=DATE(年,月,日)

セルA1に2001,セルB1に12,セルC1に11という数値が入力されていたとする。
どれをセルA3に 2001/12/11 と表示し,且これが日付のシリアル値にするには、
セルA3に  =DATE(A1,B1,C1)  と入力すればいい。


■ 数値を漢数字の文字列に置きかえる

=NUMBERSTRING(数値,定数)

 
数値:漢数字に置き換えたい数値を指定する
 定数:漢数字の形式を数値で指定する(次の3つ)

     1 位文字を含む漢数字    (百二十三)
     2 位文字を含む漢数字    (壱百弐拾参)
     3 位文字を含まない漢数字  (一二三)

■ DATEDIF関数 

    この関数は、関数貼り付けダイアログボックスから選択して貼り付けることは出来ません。 
関数のヘルプにはのっていますが、セルに直接式を手入力して初めて計算される関数です。

=DATEDIF(開始日,終了日,単位)
     単位については必ず "  "で囲むこと。
下記は「単位」の種類ですわ 

"Y" 期間内の満年数
"M"  期間内の満月数
"D" 期間内の日数
"MD" 開始日から終了日までの日数。 1ヶ月未満の日数。
"YM" 開始日から終了日までの月数。1年未満の月数。
"YD" 開始日から終了日までの日数。1年未満の日数。

 

■ 日付から曜日を取り出す

=WEEKDAY(<日付>)
  
 曜日は1〜7の数字で返され、日曜が1、月曜が2、、、土曜が7となる。
 
結果を「月」「火」と表示したい場合は、セルの書式設定で「表示形式」タブから「ユーザー定義」を選択し、「種類」の欄で「aaa」とすればよい。
 「月曜日」「火曜日」としたい場合は、「aaaa」である。

■ 抽出されているデータだけを集計する

=SUBTOTAL(集計方法,範囲1,範囲2,・・・)

 
抽出されているデータだけを集計する。
 オートフィルタで目的のデータだけを抽出した場合、その抽出だれたデータだけを合計するのがこのSUBTOTAL関数である。
 オートフィルタでデータを抽出し、その最終端のセルで「オートSUM」ボタンを押すと、「SUM」関数ではなく「SUBTOTAL」関数が挿入される。
 「SUM」関数は非表示のデータも集計してしまうが、[SUBTOTAL」関数は、オートフィルタで非表示になった行を無視して集計する。
 「オートSUM」ボタンで自動挿入されたSUBTOTAL関数には、集計方法として自動的に「合計」が設定されるが、これ以外にも全部で11種類の集計方法が有る。
 注意としては,オートフィルタでデータを抽出する前に「オートサム」ボタンを押してもSUM関数が入力されて上手くいかない。 関数を入力したいセルの左横に「合計」等の見出しをデータの続きに書きこむと,その「合計」等の見出しは表示されなくなるので,1行開けること。

もっと簡単に説明
 さて、SUBTOTAL関数のいいところは、フィルタ等で抽出されたデータを計算する時に便利なのです。
それは、非表示になっているデータは無視して、可視セルだけ集計をしてくれること。
ここでSUMを使ってしまうと、たたんで非表示になっているセルまで集計しちゃうので、
意味が無くなってしまいます。

また、データベース範囲の中で、小計行をいくつも入れて、最後に総合計をする場合、
どんな計算をしますか?
1番目の小計は =SUM(任意のセル:任意のセル)
2番目の小計も =SUM(任意のセル:任意のセル)
3番目の、、、、、、
最後の合計行は =小計セル+小計セル+、、、、、+小計セル

これだと、小計行を探してスクロールしたり、上へ行ったり下へ行ったり、
おまけに、どこの小計行まで足したっけ?ってことになりかねません。
で、SUBTOTAL関数が出番です。

小計行には =SUBTOTAL(9、任意のセル:任意のセル)と書き込んで、総合計を求めるセルにも小計行を気にせず、
総合計=SUBUTOTAL(9、先頭セル:最終セル) と書き込むだけです。
この計算式の 9 は SUM関数 を使うと言う意味です。
集計方法 にはほかにこれだけあります。

1   AVERAGE 関数
2   COUNT 関数
3   COUNTA 関数
4   MAX 関数
5   MIN 関数
6   PRODUCT 関数
7   STDEV 関数
8   STDEVP 関数
9   SUM 関数
10  VAR 関数
11  VARP 関数

また、集計する範囲は29個選択できます。

 

■ 順位を調べる

  サンプルを見る         簡単なExcelの表をみてね
  ファイルのダウンロード    シートをダウンロードする

■ 任意の単位の倍数に切り上げる

=CEILING(<値>,<単位>)

 11→15  9.4→10  42→45  77→80    (0から遠い方の値を返す)

 =CEILING(11,5) →結果は15
 =CEILING(77,5) →結果は80

■ 任意の単位の倍数に切捨てる

=FLOOR(<値>,<単位>)

  47.6→46  11→10  49→48        (0に近い方の値を返す) 
 =FLOOR(7.6,2)  →結果は6
 =FLOOR(49,2)  →結果は48
 

■  小数、整数を任意の桁数に変換する

=TRUNC(数値 , 桁数)

  8.9→8  8.954→8.95  123456→123000
  (桁数はマイナスを付けると、小数点より右へ、プラスは小数点より、左へ)

■ 文字を半角にする

=ASC(文字列)

 全角半角交じりの文字列を全て半角にする。 住所録の電話番号や、郵便番号のハイフンをまたは数字を全て半角に出来る。

■ 文字を全角にする

=JIS(文字列)

■  セルとセルをつないで1つの文字列にする

=セル & セル

 A1に「私は」 B1に「かめです」 C1=「私はかめです」  
 
但し、このデータは数値として扱うことは出来ない。 下のCOCATENATE(コンカチネート)関数と基本的な効果は同じ。

■  複数の文字列を結合して一つにまとめる

=CONCATENATE(対象セル, 対象セル)

 A1に「123」 B1に「かめかめ」 C1=「123かめかめ」  
 但し、このデータは数値として扱うことは出来ない。

■ 文字列の左端から文字を取出す

=LEFT("かめはうす", 2)

    文字列の左端から指定した数だけ文字を取りだす。 
   全角、半角のいずれの場合も1文字単位でカウントする点が、「LEFTB」関数と違う点。

 「かめはうす」から「かめ」だけを取りだす

■ 文字列の左端から文字を取出す 2

 =LEFTB(”かめはうす”, 4)

  LEFT関数と違う点は半角1文字を1単位としてカウントする。
 「かめはうす」から「かめ」を取りだす