EXCEL Kansu-Sample.xls Sheet2


関数 VLOOKUP の使用例(データ参照範囲 A1:C9)
  A B C D E F
1 コード 商品名 在庫 ▼検索コードを入力 ▼対応する値(商品名) ▼対応する値(在庫)
2 A001 商品A 1,000 a005 商品E 5,000
3 A002 商品B 2,000 a002 商品B 2,000
4 A003 商品C 3,000 ▼セルE2:対応する値(商品名)の数式
5 A004 商品D 4,000 =VLOOKUP(D2,$A$1:$C$9,2,FALSE)
6 A005 商品E 5,000 ▼セルF2:対応する値(在庫)の数式
7 A006 商品F 6,000 =VLOOKUP(D2,$A$1:$C$9,3,FALSE)
8 A007 商品G 7,000   必要に応じて、各数式を下方向へドラッグ(コピー)します
9 A008 商品H 8,000
<注>データ範囲を、セルA1〜C9 としています。範囲に名前を付けて別シートから参照も可能です。
<注>検索コードが見つからない場合、エラー値 #N/A となります。

<参考>検索コードが未入力の場合
E2セル数式を =IF(D2="","",VLOOKUP(D2,$A$1:$C$9,2,FALSE)) 
F2セル数式を =IF(D2="","",VLOOKUP(D2,$A$1:$C$9,3,FALSE)) として、エラー値 #N/A を回避できます。
<参考>検索値で、英字の大文字と小文字は区別されません。
<参考>VLOOKUP(検索値, 範囲, 列番号, 検索の型)
検索値: 範囲 の左端の列で検索する値を指定します。検索値 には、値、セル参照、または文字列を指定します。
<参考>関数INDEX、MATCH を用いた例 (データ参照範囲 A1:C6)
  A B C D E F 数式
1 コード 商品名 在庫 検索コード 商品名 在庫 商品名:E2セル
2 A001 商品A 1000 a002 商品B  2000 =IF(D2="","",INDEX($A$1:$C$6, MATCH(D2,$A$1:$A$6,),2))
3 A002 商品B 2000 在庫:F2セル
4 A003 商品C 3000 =IF(D2="","",INDEX($A$1:$C$6, MATCH(D2,$A$1:$A$6,),3))
5 A004 商品D 4000
6 A005 商品E 5000
<注>検索コードが見つからない場合、エラー値 #N/A となります。
<参考>必要に応じて、数式(E2〜F2)を下へドラッグして検索コードを増やすことが出来ます。


関数 HLOOKUP の使用例(データ参照範囲 A1:F6)
  A B C D E F
1 コード A会社 B会社 C会社 D会社 E会社
2 C001 100 200 300 400 500
3 C002 50 100 150 200 250
4 C003 1100 2,200 3300 4,400 5500
5 C004 300 600 品切れ 1,200 1500
6 C005 500 600 700 800 900
7  ↑ <注>データ範囲を、セルA1〜F6 としています。
8 ▼検索コード ▼検索する会社 ▼対応する値 数式
9 c002 B会社 100 =HLOOKUP(B9,$A$1:$F$6,MATCH(A9,$A$1:$A$6),FALSE)
10 c004 C会社 品切れ =HLOOKUP(B10,$A$1:$F$6,MATCH(A10,$A$1:$A$6),FALSE)
<注>データ範囲を、セルA1〜F6 としています。範囲に名前を付けて別シートから参照も可能です。
<参考>各検索値が未入力の場合
数式を =IF(OR(A9="",B9=""),"",HLOOKUP(B9,$A$1:$F$6,MATCH(A9,$A$1:$A$6,0),FALSE)) として #N/A を回避できます。
<参考>検索値で、英字の大文字と小文字は区別されません。
<参考>HLOOKUP(検索値, 範囲, 行番号, 検索の型)
検索値: 範囲 の上端行で検索する値を指定します。検索値 には、値、セル参照、または文字列を指定します。
範囲 :目的のデータが含まれるテーブルを指定します。セル範囲の参照、または List、Database のような名前を指定します。


関数 SUBTOTAL(集計方法, 範囲1, 範囲2, ...)
1=AVERAGE 数値(0を含む)の平均値
2=COUNT 数値の個数の合計
3=COUNTA 空白以外のデータ個数の合計
4=MAX 数値の最大値
5=MIN 数値の最小値
6=PRODUCT 引数リストの積
7=STDEV 母集団に対する標準偏差
8=STDEVP 母集団全体の標準偏差
9=SUM 数値の合計
10=VAR 母集団に対する分散
11=VARP 母集団全体の分散
この関数は、リストまたはデータベースの集計値を返します。
データベース(1行1レコード)で、フィルタ抽出されたデータ(可視セル)のみを計算出来ます。
<集計方法>リストの集計に使用する関数を、1 〜 11 の番号で指定します。
<使用例>
=SUBTOTAL(9,C3:C10) では、SUM 関数を使用してセル範囲 C3:C10 の集計値を返します。


条件付き合計式ウイザードを使用して数式作成例
  A B C D E F G
1 日付 口座 項目 支払 ▼"A銀行" かつ "保険"  の支払合計(数式内で指定する場合)
2 H19.1.1  A銀行  CARD    1,000 1,000 {=SUM(IF(B1:B13="A銀行",IF(C1:C13="保険",D1:D13,0),0))}
3 H19.1.2  B銀行  CARD 1,000 ▼口座区分が郵便局の合計(セル参照する場合)
4 H19.1.3  郵便局   CARD 1,000 郵便局 4,000 {=SUM(IF(B1:B13=E4,D1:D13,0))}
5 H19.1.4  A銀行  保険 1,000 ▼口座がB銀行で、項目がCARD の支払合計(セルを2つ参照する場合)
6 H19.1.5  B銀行  保険 1,000 B銀行 CARD  2,000
7 H19.1.6  郵便局  保険 1,000 {=SUM(IF(B1:B13=E6,IF(C1:C13=F6,D1:D13,0),0))}
8 H19.1.7  A銀行  CARD 1,000
9 H19.1.8  B銀行  CARD 1,000 ▼日付が H19/1/11 以降で、項目が通信 の支払合計
10 H19.1.9  郵便局  CARD 1,000 2,000
11 H19.1.10  A銀行  通信 1,000 {=SUM(IF(A1:A13>=DATEVALUE("2007/1/11"),(IF(C1:C13="通信",D1:D13,0))))}
12 H19.1.11  B銀行  通信 1,000 <参考数式>支払が500以上で2,000未満の支払合計
13   H19.1.12  郵便局   通信 1,000 {=SUM(IF(D1:D13>=500,IF(D1:D13<2000,D1:D13,0),0))}
<注>数式を編集する場合、
配列数式は、【CTRL】キーと【SHIFT】キーを押したまま 【Enter】キーを押します。
数式の始めと終わりの、かっこ{ }を入力する必要はありません。
通常の数式、たとえば =A1+B1 なら、配列数式は {=a1+B1} のように自動的に { } が追加されます。

<参考数式>支払が500以上で2,000未満の支払個数
{=SUM(IF(D1:D13>=500,IF(D1:D13<2000,1,0),0))}


A列にX、B列にYの場合、XとY両方のデータに合致する個数を求めるには          
  A B C 数式
1    名前     所属     個数 
2 山田 東京 ▼名前が山田、所属が大阪の場合(配列数式)
3 山田 京都 1 {=SUM((A2:A7="山田")*(B2:B7="大阪")*1)}
4 大阪 ▼または(配列数式)を使用しない例)
5 山田 大阪 1 =SUMPRODUCT((A2:A7="山田")*(B2:B7="大阪")*1)
6 鈴木 大阪 1 =SUMPRODUCT((A2:A7="山田")*(B2:B7="大阪"))
7 山田 福岡


A列にX、B列にYの場合、XとY両方のデータに合致する合計を求めるには
    A B C D 数式
1   区分    ランク     金額    合計   ▼"銀行"かつ"A"の合計
2 銀行 A 10000 15000 =SUMPRODUCT((A2:A8="銀行")*(B2:B8="A"),C2:C8)
3 証券 B 5000 ▼"銀行"の合計
4 株式 A 2500 20000 =SUMPRODUCT((A2:A8="銀行")*1,C2:C8)
5 銀行 B 5000 ▼"銀行"の預金額0以上の個数
6 証券 A    20000 3 =SUMPRODUCT((A2:A8="銀行")*(C2:C8>0))
7 銀行 A 5000
8 銀行 A 0
<参考数式>項目の値が X で、金額の値が 200 以上の合計
=SUMPRODUCT((項目="X")*(金額範囲>=200)*(金額範囲))
<配列数式例 > {=SUM(IF(A2:A8="X",IF(B2:B8>=200,B2:B8,0),0))}


データの重複を除いた個数を求めるには
  A B 数式
1   会員番号   個数 ▼セル範囲の不一致個数
2 6-001 4 {=SUM(1/COUNTIF(A2:A7,A2:A7))}
3 7-001 ↑範囲に空白データがあるとエラー #DIV/0! になります
4 7-002 {=SUM(IF(A2:A7<>"",1/COUNTIF(A2:A7,A2:A7)))}
5 7-003 ↑範囲に空白も含まれる場合
6 7-001 2 {=COUNTA(A2:A7)-SUM(1/COUNTIF(A2:A7,A2:A7))}
7 7-003 ↑範囲でデータが重複する個数
<参考>上記例では、会員番号の範囲(A2:A7)で番号の種類 4個が求められます。
また、会員番号 (7-001 と 7-003)が2個あり重複しています。
<注>配列数式は、 [CTRL]+[SHIFT]+[Enter] キーで確定します。
配列数式を用いない(空白セルなし)場合 =SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7)) となります。
<参考>データの統合(集計の方法)について
(合計/データの個数/平均/最大値/最小値/積/数値の個数/標本標準偏差/標準偏差/標本分散/分散)

次の 4 つの方法でデータを統合できます。

▼3-D 参照を使ったデータの統合
(推奨) 3-D 参照を使用すると、統合元範囲のデータのレイアウトに制限されることなくデータを統合できます。

▼位置による統合
統合元範囲のデータが同じ位置に同じ順序で並んでいる場合は、位置による統合を行います。
たとえば、同じテンプレートで作成された複数のワークシートのデータを統合するには、位置による統合を行います。

同じテンプレートで作成された複数のワークシートに入力したデータを 1 つのワークシートに統合するには、
データ記録機能付きテンプレート ウィザードを使用します。

▼項目による統合
統合元範囲のデータの並び方や配置は異なっていても、同じ見出しを使っている場合は、
項目による統合を行います。異なるレイアウトで同じ見出しがある複数のワークシートのデータを統合するには、
項目による統合を行います。

▼ピボットテーブル レポートの作成による統合
項目によるデータの統合に似ていますが、ピボットテーブルを使うと項目を簡単に構成し直すことができるため、
より柔軟性に富む統合を行うことができます。


一覧から大文字と小文字を区別して集計するには
  A B 数式
1   CODE     個数   ▼セル範囲(A2:A6)の Ab の個数を求める          
2 Ab 2 {=SUM(IF(EXACT(A2:A6,"Ab"),1,0))}
3 aB または
4 AB 2 {=SUM(EXACT(A2:A6,"Ab")*1)}
5 ab
6 Ab
<注>配列数式を使用します。 [CTRL]+[SHIFT]+[Enter]


特定の文字の個数を求めるには
  A B 数式
1   CODE      個数   ▼セル範囲(A2:A6)の小文字 a の個数
2 aaA 6 {=SUM(LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6,"a","")))}
3 A =SUMPRODUCT(LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6,"a","")))
4 aab ▼単一セル(A2)の小文字 a の個数
5 a 2 =LEN(A2)-LEN(SUBSTITUTE(A2,"a",""))
6 ba
<参考>単一セル(A1)の単語(test)の個数を求めるには
=(LEN(A1)-LEN(SUBSTITUTE(A1,"test","")))/LEN("test")
<参考>セル範囲(A1:A5)で単語(test)の個数を求めるには(配列数式)
{=SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1,"test","")))/LEN("test")}

<ワイルドカード文字>
? (疑問符)は、同じ位置にある任意の1文字を意味します。
* (アスタリスク)は、同じ位置にある任意の数の文字を意味します。

<参考>セル範囲(A1:A5)で、部分一致(testを含むセル)の個数を求めるには
=COUNTIF(A1:A5,"*test*")

<参考>セル範囲(A1:A5)で、文字が C で始まるセルの個数を求めるには
=COUNTIF(A1:A5,"c*")

<参考>セル範囲(A1:A5)で、文字列(数値以外)のセルの個数を求めるには
=COUNTIF(A1:A5,"*")
=COUNTA(A1:A5)-COUNT(A1:A5)

<参考>セル範囲(A1:A5)で、文字(数値以外)3文字のセルの個数を求めるには
=COUNTIF(A1:A6,"???")
<参考>セル範囲(A1:A5)で、文字(数値以外)2文字〜4文字のセルの個数を求めるには
=COUNTIF(A1:A5,"??*")-COUNTIF(A1:A5,"?????*")

<参考>セル範囲(A1:A5)で、正の数(ゼロより大きい)のセルの個数を求めるには
=COUNTIF(A1:A5,">0")
<参考>セル範囲(A1:A5)で、セルB1より大きいセルの個数を求めるには
=COUNTIF(A1:A5,">="&B1)

<参考>セル範囲(A1:A5)で、1以上10未満の数値が入っているセルの個数を求めるには
=COUNTIF(A1:A5,"<10")-COUNTIF(A1:A5,"<1")


切り上げ・切り捨て・四捨五入
▼切り上げ 数値を指定された桁数に切り上げます。
ROUNDUP(数値, 桁数)
数値 切り上げの対象となる実数値を指定します。
桁数 数値 を切り上げた結果の桁数を指定します。
12345 12350 =ROUNDUP(A1,-1)
12345 12400 =ROUNDUP(A1,-2)
12345 13000 =ROUNDUP(A1,-3)
▼切り捨て 数値を指定された桁数で切り捨てます。
ROUNDDOWN(数値, 桁数)
数値 の小数部を切り捨てて、整数または指定した桁数に変換します。
TRUNC(数値, 桁数) ・ INT(数値)
12345 12340 =ROUNDDOWN(A1,-1)
12345 12300 =ROUNDDOWN(A1,-2)
123.45 123 =TRUNC(A1,0)
▼四捨五入 数値を四捨五入して指定された 桁数 にします。
ROUND(数値, 桁数)
数値 四捨五入の対象となる数値を指定します。
桁数 数値 を四捨五入した結果の桁数を指定します。
12345 12350 =ROUND(A1,-1)
12345 12300 =ROUND(A1,-2)
12345 12000 =ROUND(A1,-3)
▼五捨六入 数値を 五捨(〜5)六入(6〜)して計算します。
55 50 =ROUND(A1-1,-1) 一の位で五捨六入
55 100 =ROUND(A1-1,-2) 十の位で五捨六入
▼小数点以下 数値を超えない最大の整数を返します。
INT(数値)
数値 整数に丸める実数を指定します。
0.12345 0 =INT(A1)
1.2345 1 =INT(A1)
▼関数CEILING
12345 12400 =CEILING(A1,100)  ←100単位で切り上げの例
1.2345 1.5 =CEILING(A1,0.5)  ←0.5単位で切り上げの例
12:34 13:00 =CEILING(A1,"1:00")  ←時間が1時間単位の例
数値を挟む基準値の倍数のうち、0 から遠い方の値を返します。

CEILING(数値, 基準値)
数値 丸める数値を指定します。
基準値 倍数の基準となる数値を指定します。

<例>たとえば原価 442 円の部品に値段を付ける場合に、1 円単位の端数を出さないようにするには、
数式 =CEILING(442,10) を使って、値を最も近い 10 円単位の値に丸めることができます。
▼関数FLOOR
12345 12000 =FLOOR(A1,1000)  ←1000単位で切り捨ての例
1.2345 1 =FLOOR(A1,0.5)  ←0.5単位で切り捨ての例
12:34 12:30 =FLOOR(A1,"0:15")  ←時間が15分単位の例
数値を挟む基準値の倍数のうち、0 に近い方の値を返します。

FLOOR(数値, 基準値)
数値 丸める数値を指定します。
基準値 倍数の基準となる数値を指定します。

<その他参考>

▼FIXED(数値, 桁数, 桁区切り)
数値を四捨五入し、ピリオド (.) とカンマ (,) を使って書式設定した文字列に変換します。
数値 四捨五入して文字列に変換する数値を指定します。
桁数 小数点以下の桁数を指定します。
桁区切り 計算結果をカンマ (,) で桁区切りするかどうかを、TRUE またはFALSEで指定します。
桁区切りを省略すると、カンマで桁区切りされた文字列が返されます。

Excel で扱うことのできる数値の最大有効桁数は 15 桁ですが、
桁数 には 127 までの整数を指定することができます。

桁数 に負の数を指定すると、数値 は小数点の左側の指定した桁で四捨五入されます。
桁数 を省略すると、2 を指定したと見なされます。

FIXED 関数で文字列に変換した数値は、そのまま数式の中で使用することができます。


▼ODD(数値)
数値を切り上げて、その結果に最も近い奇数の値を返します。

▼EVEN(数値)
数値を切り上げて、その結果に最も近い偶数の値を返します。

▼TRUNC(数値, 桁数)
数値 の小数部を切り捨てて、整数または指定した桁数に変換します。
・数値  小数部を切り捨てる数値を指定します。
・桁数  切り捨てを行った後の桁数を指定します。桁数 の既定値は 0 (ゼロ) です。

▼MROUND(数値, 倍数)
指定された値の倍数になるように数値を丸めます。
この関数を使うには、分析ツールを登録する必要があります。

・数値 丸める数値を指定します。
・倍数 切り上げまたは切り捨てて丸められた数値が、その倍数となるような数値を指定します。
つまり、倍数 は、切り上げまたは切り捨てられた数値の約数になります。

数値 を 倍数 で割った剰余が 倍数 の半分以上である場合は、0 から遠い方の値に丸められます。


数値を整数と小数部分に分けるには
    100.123 100 0.123
=INT(A1) =A1-INT(A1)
<参考>小数部分は、数式  =MID(A1,FIND(".",A1),99)+0 でも同じ結果が得られます。

<参考>小数点以下の桁と有効桁数 分数または小数点が付いた数値の書式を設定するには
次の書式記号を指定します。

表示 書式記号
1234.59 を 1234.6 とする。 ####.#
8.9 を 8.900 とする。 #.000
.631 を 0.6 とする。 0.#
12 を 12.0、1234.568 を 1234.57 とする。 #.0#
44.398、102.65、および 2.8 の小数点の位置を揃える。 ???.???
5.25 を 5 1/4、5.3 を 5 3/10 と、除算記号を揃える。 # ???/???


小数点の位置を揃える
  A
1 1234.56789
2 123.4567
3 12.345
4 1.23
5 0
6 0.123
7 -10.12345
B 数式
1,234 . 56789   =IF(A1=0,TEXT(A1,"#,##0?????"),IF(INT(A1)-A1<>0,TEXT(A1,"#,##0.?????"),TEXT(A1,"#,###_)     ")))
123 . 4567 ↑上記数式を下方向へドラッグ(コピー)します。↓
12 . 345
1 . 23
0
0 . 123
-10 . 12345
<注>表示にはMSゴシック、MS明朝などの等幅フォントを使用してください。
<注>セル配置を右揃えにします。

<参考>元の値が数値以外の場合、エラー値 #VALUE! と表示されます。
<参考>上記数式例では、小数点以下を5桁としています。

<参考>小数点位置揃えVBAコード例(範囲を選択して実行します。)
Sub myNumformat()
'選択範囲の小数点位置を揃えます
'MSゴシック、MS明朝などの等幅フォントを使用してください
'ゼロの場合は空白となります

Dim N As String, keta
Dim keta1 As String, keta2 As String, r

N = "###_)"
keta = Application.InputBox("小数点以下の桁数を入力してください", Type:=1)

If VarType(keta) <> vbBoolean Then
keta1 = "#,##0." & Right("??????????", keta)
keta2 = N & Space(keta)
Else
Exit Sub
End If

For Each r In Selection
If IsNumeric(r) Then
If (r.Value - Int(r.Value)) <> 0 Then
r.NumberFormat = keta1
Else
r.NumberFormat = keta2
End If
End If
Next
End Sub


文字列で 20070301を日付(2007/3/1)にするには
  20070301 2007/3/1 =DATEVALUE(TEXT(A1,"0000-00-00"))
<注>元の数値で、年は4桁、月2桁、日2桁で入力します(ゼロを省略するとエラー#VALUE! となります)
<参考>計算結果のシリアル値は、セルの書式を [日付] に変更します。

<参考>1900 年日付システム (Windows 版 Excel の標準) を使用する場合

DATEVALUE(日付文字列)
日付を表す文字列をシリアル値に変換します。
日付文字列 には 1900 年 1 月 1 日 〜 9999 年 12 月 31 日の範囲にある日付を表す文字列を指定する必要があります。


別々のセル(A1,B1,C1)に入力された数字から日付を表示するには
  A  B  C D 数式
1    2007      3     31  2007/3/31 =DATE(A1,B1,C1) ←表示形式を適宜変更可能です
2       土曜日 =TEXT(DATE(A1,B1,C1),"aaaa")
3       2007/3/31 (土) =TEXT(DATE(A1,B1,C1),"yyyy/m/d (aaa)")


A列にX、B列にY、C列にZの場合、XとYとZのデータに合致する個数を求めるには
  A B C D  E 数式
1 区分  地域   個数 ▼営業部と東京で値が500以上に合致するセル個数
2 営業部   東京    1000    1000 2 =SUMPRODUCT((A2:A7="営業部")*(B2:B7="東京")*(C2:C7>=500))
3 営業部 名古屋 500   ▼C列で値が500以上の個数
4 営業部 東京 500 500 3 =COUNTIF(C2:C7,">=500")
5 人事部 大阪 30   ▼C列で値が500以上、D列が空白の個数
6 営業部 東京 100   1 =SUMPRODUCT((C2:C7>=500)*(D2:D7=""))
7 宣伝部 福岡 499  
<その他参考>A列で値が"A"、B列の値が200以上の個数 (B列に文字や空白も含む場合)
{=SUM((A1:A300="A")*ISNUMBER(B1:B300)*(B1:B300>=200))}
<その他参考>式の配列は30個まで指定可能です。
<その他数式例>
{=SUM((A2:A10="A")*(B2:B10>=200))-SUM((A2:A10="A")*(B2:B10>=""))+SUM((A2:A10="A")*(B2:B10=""))}


日付リストから、条件を満たす日付の個数を求めるには
   A B 数式
1 日付  個数 ▼ 5/10より前の個数
2 2006/5/5 2 =COUNTIF(A2:A10,"<2006/5/10")
3 2006/5/6   ▼ 5/10以降の個数
4 2006/5/10 4 =COUNTIF(A2:A10,">=2006/5/10")
5 2006/5/11   ▼ 明日以降の個数
6 2006/5/15 0 =COUNTIF(A2:A10,">"&TODAY())
7 2006/5/20 =SUMPRODUCT((A2:A10>TODAY())*1)
<参考>日付指定の個数
=SUMPRODUCT((日付範囲>=DATEVALUE("2007/1/1"))*1)
=SUMPRODUCT((日付範囲>=DATEVALUE("2007/1/1"))*(日付範囲<=DATEVALUE("2007/4/10"))*1)
<参考>日付指定して項目1が X で、項目2が Y の個数
=SUMPRODUCT((日付範囲>DATEVALUE("2007/1/1"))*(項目1="X")*(項目2="Y"))
<参考>COUNTIF(範囲, 検索条件)
▼たとえば、セルA1〜A100 の日付リストの中で、今日の個数
=COUNTIF(A1:A100,TODAY())
▼日付リストで、今年以降の個数
=COUNTIF(日付範囲,">="&TEXT(TODAY(),"yyyy")&"/1/1")
▼日付リストで、今年の個数
=COUNTIF(日付範囲,">="&YEAR(TODAY())&"/1/1")-COUNTIF(日付範囲,">="&YEAR(TODAY())+1&"/1/1")


範囲内の特定の値の個数の数式例
=SUMPRODUCT((A1:A10={"A","B"})*1) セルA1A10の範囲で、A 又は B の個数を求める場合
{=SUM((A1:A10={"A","B"})*1)} ←配列数式の場合 [CTRL] + [SHIFT] + [Enter]
=SUMPRODUCT(COUNTIF(A1:A10,B1:B2)) ←セルA1:A10はデータ、セルB1:B2は検索値の場合
<参考>
もし、"A" や "B" の部分がセル参照ではなく定数しか使用しないということであれば、
COUNTIF() の値部分は配列を許容しますので、
=SUMPRODUCT(COUNTIF(A1:A10,{"A","B"}))

という記述ができます。
これは配列数式の一種で、動作としては、COUNTIF() の結果が配列で返され、
SUMPRODUCT() は配列をそのまま扱うことができることから
1次元配列の場合は総和を返す、ということを利用しています。

この方法ではセル参照はできませんので、以下のような記述はできません。
=SUMPRODUCT(COUNTIF(A1:A10,{B1,B2}))

ただし、B1,B2 のように連続したセルであれば、以下のように記述できます。
=SUMPRODUCT(COUNTIF(A1:A10,B1:B2))

COUNTIF() が配列を返すことは、適当なセルに
=COUNTIF(A1:A10,{"A","B"})
と入力したうえで、そのセルと右隣のセルの2つを選択した状態で
F2 キー(ファンクションキー)を押し、Ctrl+Shift+Enter で確定
させることで、確認できます。
入力の仕方は、あらかじめ選択した上で式を入れるというのでもかまいません。
(ちなみに、, が列区切り、; が行区切りになりますので、
2行1列で結果を返したい場合は、{"A";"B"} とします)

もし、与える定数が "*A*" 等の、"A" を含むセルといった記述は無く、
完全に一致するセルのみということであれば、もう少し簡単に、以下のように記述できます。
=SUMPRODUCT((A1:A10={"A","B"})*1)

上記式中の *1 は、数値(一致したセル=1,そうでなければ0)へ変換しています。

SUM() を利用する場合は、
=SUM((A1:A10={"A","B"})*1)
のように入力して、Ctrl+Shift+Enter で確定させると、同じ結果になります。


日付リストから、条件を満たす列の合計を求めるには
  A B C 数式
1 日付   金額   合計 ▼7月の合計
2    2006/6/25 100 400 =SUMIF($A$1:$A$6,"<2006/8/1",$B$1:$B$6)-SUMIF($A$1:$A$6,"<2006/7/1",$B$1:$B$6)
3 2006/6/30 200   ▼8月以前の合計
4 2006/7/1 100 700 =SUMIF($A$1:$A$6,"<2006/8/1",$B$1:$B$6)
5 2006/7/15 300   ▼7月以降の合計
6 2006/8/1 500 900 =SUMIF($A$1:$A$6,">=2006/7/1",$B$1:$B$6)


▼明日以降の合計
=SUMIF($A$2:$A$20,">"&TODAY(),$B$2:$B$20)
=SUMPRODUCT((日付範囲>TODAY())*(項目="X"),金額範囲)
="金額:"&TEXT(SUMIF($A$2:$A$20,">"&TODAY(),$B$2:$B$20),"#,0")
{=SUM(IF($A$2:$A$20>TODAY(),$B$2:$B$20,0))}
▼指定日〜指定日:
=SUMPRODUCT((日付範囲>=DATEVALUE("2007/4/1"))*(日付範囲<=DATEVALUE("2007/4/10"))*(金額範囲)*1)
=SUMPRODUCT((日付範囲>=DATEVALUE("2007/4/1"))*(日付範囲<=DATEVALUE("2007/4/10"))*(項目="X")*(金額範囲)*1)
▼指定日以降:
=SUMPRODUCT((日付範囲>DATEVALUE("2007/1/1"))*(項目1="X")*(項目2="Y"),金額範囲)
▼今年以降:
=SUMIF(日付範囲,">="&TEXT(TODAY(),"yyyy")&"/1/1",金額範囲)
="金額:"&TEXT(SUMIF(日付範囲,">"&TEXT(TODAY(),"yyyy")&"/1/1",預入額範囲)-SUMIF(日付範囲,">"&TEXT(TODAY(),"yyyy")&"/1/1",支払額範囲),"#,0")
▼今年:
=SUMIF(日付範囲,">="&YEAR(TODAY())&"/1/1",金額範囲)-SUMIF(日付範囲,">="&YEAR(TODAY())+1&"/1/1",金額範囲)
=(SUMIF(日付範囲,">="&YEAR(TODAY())&"/1/1",預入額範囲)-SUMIF(日付範囲,">="&YEAR(TODAY())+1&"/1/1",預入額範囲))-(SUMIF(日付範囲,">="&YEAR(TODAY())&"/1/1",支払額範囲)-SUMIF(日付範囲,">="&YEAR(TODAY())+1&"/1/1",支払額範囲))
▼去年:
=SUMIF(日付範囲,"<"&YEAR(TODAY())&"/1/1",金額範囲)-SUMIF(日付範囲,"<"&YEAR(TODAY())-1&"/1/1",金額範囲)
去年以前:
=SUMIF(日付範囲,"<"&YEAR(TODAY())&"/1/1",金額範囲)
▼来年:
=SUMIF(日付範囲,">"&YEAR(TODAY())&"/12/31",金額範囲)-SUMIF(日付範囲,">"&YEAR(TODAY())+1&"/12/31",金額範囲)
▼来年以降:
=SUMIF(日付範囲,">"&YEAR(TODAY())&"/12/31",金額範囲)

<注>上記の各数式は、セル範囲に名前を付けて表した数式例です。


時刻データを、15分きざみで補正するには
  A B
1 出勤時刻 補正時刻 数式
2 8:25 8:30 =IF(A2-"9:0">0,"遅刻",CEILING(MAX(A2,"8:30"),"0:15"))
3 8:31 8:45 ↑上記数式を下方向へドラッグ(コピー)します。↓
4 8:47 9:00
5 9:03 遅刻
<注>上記条件は、8:30までを8:30、8:31〜8:45を8:45、8:46〜9:00を9:00 、それ以降は遅刻としています。
タイムカード等の時間計算に適しています。なお、24時間で表示する場合は [h]:mm と書式設定しましょう。
<参考>15分単位で切上げ: =CEILING(A2,"0:15")
15分単位に切捨て: =FLOOR(A2,"0:15")
<参考>時間計算で、分表示を 0分〜29分=0分 30分〜59分=30分
のように30分刻みの表示にするには
A1セルに時間が「時:分」であるとして、B1に =FLOOR(A1,"0:30") とし、表示形式を「時刻」にします。
又は
A1に値を入力する場合、B1に =IF(A1<=29,0,IF(A1<=59,30)) と数式を入力します。
A1の値が 0〜29 なら 0 が、30〜59 なら 30 がB1セルに表示されます。


30分以上は切り上げ、29分以下は切り捨てる関数例(セルの表示形式を時刻にします)
1:29 1:00 =MROUND(時間セル,"01:00")
1:30 2:00 =MROUND(時間セル,"01:00")
1:31 2:00 =MROUND(時間セル,"01:00")
<注>メニューの[ツール]-[アドイン]-[分析ツール] をONにする必要があります。


時間(60進数)を10進数にするには
  A B C 数式
1 10:00 8:30 1.5 =(A1-B1)/"1:0" 又は =(A1-B1)*24
2    10:00    25:30     -15.5
<注>C列の書式は G/標準 または 数値 にします。
  A B 数式
1    5:15    5.25 =A1/"1:00"
2 12:00 12 =A2*24
<注>B列のセル書式は G/標準 または 数値 にします。


時間計算
  A B C 数式
 1    8:00    9:23 1:23 =MAX(A1,B1)-MIN(B1,A1)
1:23 =IF(OR(A1="",B1=""),"",IF(A1<=B1,"","-")&TEXT(MAX(A1,B1)-MIN(B1,A1),"h:mm"))
###### =A1-B1
<参考>時刻A1から時刻B1を引き算した結果をC列に求めています。
1番目の数式では、時間の差が求められます。
2番目の数式では、A1とB1の計算結果がマイナスでも表示します。
例えば 8:00-7:00 の場合、設定時刻から -1:00 と表示できます。

3番目の数式は、単純に引き算した結果です。
通常、計算結果がマイナスの場合はエラー値 ###### となりますが、
日付システムを ”1904年から計算する” に設定すると、マイナスの日数や時間を内部的に取り扱うことが可能になります。
ただし、日付と時刻の計算方法がブック全体に影響するため注意が必要です。
<参考>
例えば、27:30−15:15 のような時間の計算式の場合

範囲 A1:C1 の[セルの書式設定]-[ユーザー定義]-[種類]で [h]:mm と設定します。

この場合、[h]:mm は時刻ではなく時間を表す事になります。
エクセルは 24時間 = 1 なので、条件判断をつけた式を作る必要があります。
  A B C 数式
1  27:30  15:15  11:45 =MAX(IF(A1<1,A1,A1-1),IF(B1<1,B1,B1-1))-MIN(IF(A1<1,A1,A1-1),IF(B1<1,B1,B1-1))
 12:15 =A1-B1 または ="27:30"-"15:15"

1番目の数式では、24時間(1日)を超える値を含む場合の時間を求めています。
2番目の数式 =A1-B1 の場合は 12:15 ですが、B1-A1 とした場合はエラー値 ###### となります。


勤務時間計算(始業:9時、終業:17時、休憩:12時〜13時、とした場合)
  A B C D E F G <注>時間を30分単位としています
1 出社 退社 出勤時刻 退勤時刻 午前勤務時間 午後勤務時間 勤務時間
2 8:15 17:31 9:00 17:00 3:00 4:00 7 各数式を下方向にドラッグします
3 12:10 17:31 12:30 17:00 0:00 4:00 4 CDEF列のセル書式は [h]:mm
4 8:40 15:55 9:00 15:30 3:00 2:30 5.5 G列のセル書式は標準
<数式>
出勤時刻  =MAX("9:00",CEILING(A2,"0:30"))
退勤時刻  =MIN("17:00",FLOOR(B2,"0:30"))
午前勤務時間  =MAX(0,MIN("12:00",FLOOR(B2,"0:30"))-MAX("9:00",CEILING(A2,"0:30")))
午後勤務時間  =MAX(0,MIN("17:00",FLOOR(B2,"0:30"))-MAX("13:00",CEILING(A2,"0:30")))
勤務時間  =ROUND((E2+F2)*48,0)/2

<注>午前・午後の勤務時間(E列、F列)は計算に必要な列です。邪魔な場合は列を非表示にします。

<参考>勤務時間は下記数式で直接求められますが、単位は 時:分です。セル書式は[h]:mm
=MAX(0,MIN("12:00",FLOOR(B2,"0:30"))-CEILING(A2,"0:30"))+MAX(0,FLOOR(B2,"0:30")-MAX(CEILING(A2,"0:30"),"13:00"))

<参考>時間計算は目に見えない誤差を含んでいます。そのため勤務時間は時/分で扱わず時間単位としています。
<参考>一般的な時間計算
  A B C D E 数式
1 出社 退社 出勤時刻 退勤時刻 勤務時間 出勤時刻数式 退勤時刻数式 勤務時間数式
2 8:15 17:31 8:15 17:30 9:15 =CEILING(A2,"0:15") =FLOOR(B2,"0:15") =FLOOR(B2,"0:15")-CEILING(A2,"0:15")
3 8:15 17:31 8:30 17:30 9:00 =CEILING(A3,"0:30") =FLOOR(B3,"0:30") =FLOOR(B3,"0:30")-CEILING(A3,"0:30")
4 8:15 17:31 9:00 17:00 8:00 =CEILING(A4,"1:00") =FLOOR(B4,"1:00") =FLOOR(B4,"1:00")-CEILING(A4,"1:00")
<注>上記例は、行2は15分単位、行3は30分単位、行4は1時間単位での計算結果です。
休憩時間は考慮していませんので、必要に応じて列を加え、引き算します。
セル書式は時刻ですが、24時間で表示する場合はセル書式を [h]:mm とします。

<参考>
CEILING(数値, 基準値) は数値を挟む基準値の倍数のうち、0 から遠い方の値を返します。
FLOOR(数値, 基準値) は数値を挟む基準値の倍数のうち、0 に近い方の値を返します。
数値:丸める数値を指定します。基準値:倍数の基準となる数値を指定します。

<参考>
現在の日付や時刻を入力する
現在の日付を簡単にセルに入力するには、[CTRL ]キーを押しながら ; (セミコロン) キーを押し、[Enter] キーを押します。
現在の時刻を簡単にセルに入力するには、[CTRL ]キーを押しながら : (コロン) キーを押し、[Enter] キーを押します。

<参考>A1からB1まで(セルは日付)の経過日数計算/単位は(日)セル表示形式は標準
=IF(OR(A1=0,B1=0,A1=B1),0,DATEDIF(A1,B1,"D"))
<参考>A1からB1まで(セルに日付・時刻を含む)の経過時間計算/単位は(時間/分)セル表示形式を [h]:mm
=IF(OR(A1=0,B1=0,A1=B1),0,TEXT("1:00",B1)-TEXT("1:00",A1))
<参考>A1からB1まで(セルは時刻のみ)の経過時間計算/単位は(時間/分)セル表示形式を [h]:mm
=IF(OR(A1=0,B1=0,A1=B1),0,TEXT("1440",B1)-TEXT("1440",A1))


今日を基準に、土日祝を除く直前の営業日を取得するには
=WORKDAY(today(),-1,$D$1:$D$18) ←直前営業日
=WORKDAY(today(),+1,$D$1:$D$18) ←直後営業日
<注>祝日リストを、セル範囲(D1:D18)に設定した場合の数式例です。

<参考>上記数式で -1 は直前 、+1 は直後の日付を指定しています。

<参考>今日現在の年度始め(4月1日)の日付を求めるには
=DATE(YEAR(TODAY())-(MONTH(TODAY())<=3),4,1)
2007/3/31までは2006年度、2007/4/1から2007年度になります。

<参考>WORKDAY(開始日, 日数, 祭日)
開始日から起算して、指定された稼動日数だけ前または後の日付に対応する値を返します。
稼動日とは、土曜、日曜、および指定された祭日を除く日のことです。
この関数を使用すると、納品書の支払日、発送日、作業日数などを計算するときに、週末や祭日を除くことができます。
メニューの、[ツール]-[アドイン]-[分析ツール]を ONにする必要があります。


対象セルに文字の一部が含まれている場合のIF条件式
  A       B       数式
1 食品科学 =IF(NOT(ISERROR(FIND("食品",A1))),"○","×")
2 冷凍食品製造 ↑上記数式を下方向へドラッグ(コピー)します。↓
3 健康食の品質   ×
<参考>セルに"食品"が含まれる場合は○、含まれない場合×となります。


セルの値が100未満の場合は○を表示、それ以外は空白
  A B 数式
1 -1      ○     =LEFT("○",LEN(A1)*(A1<100))
2 0      ○     ↑上記数式を下方向へドラッグ(コピー)します。↓
3 99.9  ○
4             100  
<参考>セルの値が文字や空白の場合、空白となります。
数式 =IF(AND(ISNUMBER(A1),A1<100),"○","") でも同じ結果を返します。
<参考>
数式 =IF(A1<100,"○","") でも結果を返しますが、セルの値が空白の場合も ○ となります。


対象範囲に検索値が含まれている場合のIF条件式
  A B     C    
1 項目 検索値 結果 数式
2 BOOK BO =IF(COUNTIF($A$2:$A$100,B2)>=1,"有","無")
3 BOOKS     book 上記数式を下方向へドラッグ(コピー)します。
4 SHEET BED
5 BOX B
6 SHE Sheet     
<参考>A列に基本の値、B列に検索する値の場合、比較した結果をC列に表示します.
B列の各検索値がA2〜A100の対象範囲に存在すれば、C列に有、無ければ無と表示します。

<参考>B列で検索する値の、大文字と小文字は区別されません。

<参考>A列の値とB列の値を比較する場合
セルC1に =COUNTIF(A:A,B:B) と入力し、数式を下方向へコピーします。
B列各セルの値(文字や数値)がA列内に、ある・なし の結果を 1 と 0 で表示します。

  A B C
1 項目 検索値 結果 数式
2 BOOK C12345 YES {=IF(OR(EXACT($A$2:$A$100,B2))=TRUE,"YES","NO")}
3 Book book NO 上記数式を下方向へドラッグ(コピー)します。
4 C12345 1250  YES
5 C-12345    
6 1250    
<注>数式は配列数式として入力します。[CTRL]+[SHIFT]+[Enter]

<参考>検索する対象範囲をセルA2〜A100 とした数式例です。
<参考>B列で検索(文字列の照合)する値の、大文字と小文字は区別されます

<参考>OR(論理式1, 論理式2, ...) 引数は 1 〜 30 個まで指定できます。
<参考>EXACT(文字列1, 文字列2) 書式設定の違いは無視されます。
<参考>A〜Cの列範囲でマイナス値があれば 1を返すには
  A B C D 数式
1 項目1 項目2 項目3 結果
2 12,345 0 / 0 {=--OR((IF(ISERROR(SEARCH("-",$A2:$C2,1)),0,1)))}
3 1,200 -50 100 1 上記数式を下方向へドラッグ(コピー)します。
4 1.2 0.01 -0.2 1
<注>数式は配列数式として入力します。[CTRL]+[SHIFT]+[Enter]
数式中の "--"は(-1)*(-1)を意味し、無ければTRUE 又はFALSEを表示します。

<参考>列範囲で特定文字の有無を検索したい場合は
数式中の " " 部分で文字を直接指定します。


図形に特定セルの内容を表示させるには(単一セルのみの場合)
  A C D
1 サンプル売上高    
10    図形内に、非表示セルB1にリンク表示の例
\123,456,789
11  
12  
13    
<手順>描いた図形やオートシェイプを選択して、数式バー=B1 と入力します。
外部ブックや別シートのセルの場合は、=Sheet2!A1 など絶対アドレスを指定します。

<参考>複数のセル内容を表示したい場合は、ピクチャ(画像)として、シートに貼り付けるか、
ツールバーを右クリック→[ユーザー設定]-[コマンド]-[ツール] の カメラ機能 を利用しましょう。
<参考>オートシェイプの図形や書式設定で、豊富なオプション表示が可能です。

オブジェクトをセルの枠線に合わせるには、Altキーを押しながら図形を描きます。
複数のオブジェクトを連続作成するには、描画に使用するボタンをダブルクリックします。終了はEscキーです。
図形を選択して、Ctrlキーを押しながらドラッグすると、複製コピーが出来ます。
Shiftキーを押しながら円を描くと、真円を描くことが出来ます。

<メモ>セルにあわせて画像を挿入するには
オートシェイプの四角形を Alt キーを押しながらセルサイズに合わせて描き、
「オートシェイプの書式設定」→「色と線」タブ、塗りつぶし:色 で「塗りつぶし効果」を選び、
「図」タブから図を挿入します。


ピクチャ(画像)として、シートに貼り付けるには
□Microsoft Excel - Book1
ファイル(F) 編集(E)         表示(V)        
図のコピー(C)
図の貼り付け(P)
図のリンク貼り付け(N)
<手順1>画像にしたいシートのセルやセル範囲を選択します。
<手順2>【SHIFT】キーを押しながら、メニューの [編集] をクリックします。
<手順3>編集メニューの中の [図のコピー] コマンドをクリックします。
<手順4>任意の場所を選択して、通常の貼り付けコマンドを実行します。

<参考>図のコピーで(外観/図の形式)を選択出来ます。
<参考>拡大・縮小や図の書式設定で、豊富なオプション設定が可能です。
<参考>ご使用のソフトウエアに[新規貼り付け]で、名前を付けて保存することが可能です。

<参考>[図のリンク貼り付け(N)] は、セルの範囲を普通のコピーコマンドを実行した後、選択出来ます。
元のデータを変更すれば、貼り付けた図に反映されます。


外部指定シートの値をリンク表示するには
='[Kansu-Sample.xls]Sheet2'!$A$1
<参考>上記数式例は Kansu-Sample と言う名前のブックのシート2のA1セルを指定しています。
外部ブックやシートが存在しない場合、エラー #REF! となります。
外部ブックを開く必要はありません。

<参考>リンク先のブックが閉じている場合は、ブック名の前に完全なパスが表示されます。
=SUM('C:\Data\[Knsu-Sample.xlsSheet2'!A1:A10)

Kansu-Sample.xls ファイルが開いている場合、この数式は "=SUM([Kansu-Sample.xls]Sheet2!A1:A10)" と表示されます。

<参考> リンク先のワークシートやブックの名前に英数字以外の文字が使用されている場合、
その名前やパスをクォーテーション (') で囲む必要があります。


選択範囲に重複データ(同じ値)を入力できないようにするには
=COUNTIF($A$1:$A$100,A1)=1
<手順>範囲を選択して、メニューの[データ]-[入力規則]-[入力値の種類]→[ユーザー設定] 
数式 =COUNTIF(絶対参照セル範囲($記号),最初のセル)=1 のように入力します。
<参考>上記数式例では、セル範囲 A1〜A100 に同じ値や文字を入力できません。
ただし、コピー貼り付けは可能なので注意が必要です。


データに対応する連番を表示する(データが文字の場合)
  A      B     数式
1 1001   =IF(COUNTIF(A1,"*"),COUNTIF($A$1:A1,"*"),"")
2 1001A     1 上記数式をデータが必要な行まで、下へドラッグ(コピー)します
3 2001  
4 2001A 2
5    
6 2001B 3
<参考>A列のデータが数値(整数)の場合、B列に連番表示
数式を =IF(COUNTIF(A1,">0"),COUNTIF($A$1:A1,">0"),"") とします。

<参考>A列のデータが100以下の場合、B列に連番表示
数式を =IF(COUNTIF(A1,"<=100"),COUNTIF($A$1:A1,"<=100"),"") とします。

<参考>途中で行挿入やデータが書き換えられた場合でも、自動的に連番が振り直されます。
<参考>A列のデータが空白の場合は無視されます。
<参考>単純に連番表示する
列の一番上が項目名とした場合
連番を表示したいセル範囲を選択して、数式を =ROW()-1 とします。

<参考>範囲選択して、数式を入力後 CTRLキーを押しながら Enterキーを押すと全セルに入力できます。
<参考>数式中の -1 は項目名が1行ある場合を想定しています。
5行目から連番を振る場合は -4 とします。

<注>ROW関数はセルの行番号を返します。連番開始番号を1とした場合の数式例です。
<注>連番範囲内の行削除には有効ですが、空白行や行の非表示には対応しません。


セルに特定文字 com が含まれる場合、B列に表示するには
  A B 数式
1 japan.com japan.com =IF(COUNTIF(A1,"*COM*"),A1,"")
2 a Company a Company ↑下方向にドラッグ(コピー)します↓
3 co-m規制値  
4 COMMAND-123 COMMAND-123
<注>大文字と小文字の区別はされません。
<注>セルに com が含まれない場合は空白としています。

<参考>数式中の " " で囲まれた部分で条件を指定します。
指定が *com なら 文字列の後にcom が含まれる
指定が *com* なら 文字列中にcom が含まれる
指定が com* なら 文字列の前にcom が含まれる条件となります。

<参考>セルの値が 50以下の場合のみ、前にマイナス”-” をつけてB列に表示するには
数式  =IF(COUNTIF(A1,"<=50"),"-"&A1,"")

<参考>ハイフン”-” が含まれる場合は、前に code をつけてB列に表示、それ以外はそのまま表示するには
数式  =IF(COUNTIF(A1,"*-*"),"code ","")&A1


A列のデータが重複している場合、B列に * を表示するには
   A B 数式
1      項目    重複 ▼A2以下にデータがある場合、B2セルに
2 A         =IF(COUNTIF($A$2:A2,A2)>1,"*","")
3 B   又は
4 A * =LEFT("*",COUNTIF($A$2:A2,A2)>1)
5 B * と入力後、下方向にドラッグ(コピー)します
6 C   
<参考>条件付き書式を設定し、重複セルを表示する方法もあります。
範囲選択して、条件1  数式が =COUNTIF($A$1:$A$10,A1)>1 と入力し、任意の書式を指定します。


日付リストから、今日以降の日付を重複を除いてB列に表示するには
  A B 数式
1    2007/3/1    =IF(A1>=TODAY(),IF(COUNTIF($A$1:A1,A1)>1,"",A1),"")
2 2007/4/1   2007/4/1 =IF(A2>=TODAY(),IF(COUNTIF($A$1:A2,A2)>1,"",A2),"")
3 2007/4/1    =IF(A3>=TODAY(),IF(COUNTIF($A$1:A3,A3)>1,"",A3),"")
4 2007/4/5 2007/4/5 =IF(A4>=TODAY(),IF(COUNTIF($A$1:A4,A4)>1,"",A4),"")
5 2007/4/5    =IF(A5>=TODAY(),IF(COUNTIF($A$1:A5,A5)>1,"",A5),"")
6 2007/5/1 2007/5/1 =IF(A6>=TODAY(),IF(COUNTIF($A$1:A6,A6)>1,"",A6),"")
<参考>今日の日付が2007/4/1の場合です。
B1セルに1行目の数式を入力後、下方向にドラッグ(コピー)します。


データベース関数で、条件に一致するデータを取得するには
  A B C 数式
1 日付 日付 取得値
2    2007/1/1 =">"&TODAY() ▼検索条件(明日以降で最小)に合致する日付
3 2007/2/1   2007/5/1 =IF(DMIN(A1:A7,1,$B$1:$B$2)<>0,DMIN(A1:A7,1,$B$1:$B$2),MAX(A1:A7))
4 2007/3/1 ▼日付範囲に明日以降のデータがない場合はエラーとなります。
5 2007/4/1 2007/5/1 =DMIN(A1:A7,1,B1:B2) 明日以降で最小日付
6 2007/5/1 2007/6/1 =DMAX(A1:A7,1,B1:B2) 明日以降で最大日付
7 2007/6/1
<参考>今日の日付が 2007/4/1 の場合です。
検索条件に、項目ラベルと、今日より大きい値 とする関数を指定しています。
明日以降のデータがない場合、最大日付を取得して、エラーを回避しています。
日付範囲(A1:A7)、検索条件(B1:B2)にそれぞれ名前をつけて用いることが出来ます。
=IF(DMIN(日付範囲,列,検索条件)<>0,DMIN(日付範囲,列,検索条件),MAX(日付範囲))
<参考>上記例は、日付を含むデータベースで、次の決済日や期間を求める場合などに有効です。


選択範囲の値(数値)を、並べ替えずにランク(順位)を付けるには
   A B C 数式
1 数値データ 大きい順位  小さい順位 ▼大きい順位の数式 ▼小さい順位の数式
2 12 2 4 =RANK(A2,DATA,0) =RANK(A2,DATA,1)
3 15 1 5  ↑上記数式を、データが必要な行まで、下へドラッグ(コピー)します↓ 
4 8 3 2
5 -0.9 5 1
6 8 3 2
<注>数値のデータ範囲に、DATA という名前を付けています。上記例ではセル範囲(A1〜A6)です。
<参考>データ範囲に空白や文字がある場合、#N/A 又は #VALUE! となりますが、それを除いて計算します。
<参考>上記数式例では、範囲 A2〜A6 がランクの対象です。必要に応じて変更してください。
<参考>データ範囲に名前を用いない場合、
数式を、大きい順位  =RANK(A2,A$2:A$6,0)  小さい順位 =RANK(A2,A$2:A$6,1) のように書き換えます。
<参考>セルやセル範囲に名前を付けるには
□Microsoft Excel - Book1
メニューバー → ファイル(F)   編集(E)     表示(V)     挿入(I)  
 □ □ □ □ □ □ □ □ □ □ □
MS Pゴシック 10    □ □ □ □
名前ボックス → A1 =  
    A  B C D
1        
(手順1)名前を指定するセルやセル範囲を選択して
(手順2)名前ボックスに、名前(関数名や数字、A1などのセル番地以外)を入力して、【Enter】 キーを押します。
<注>同一ブックで同じ名前を重複して定義することは出来ません。
<注>漢字は使用できますが、アルファベットの大文字(DATA)と小文字(data)は区別されません。

<参考>メニューの[挿入]-[名前]-[定義] でも同様に名前を付ける事が出来ます。
<参考>他のワークシートやブックの数式でも使用できます。


成績を5段階評価するには
  A B C
1 受験番号  点数    評価   数式
2 0001 37 2 =MATCH(PERCENTRANK(B$2:B$6,B2),{0,0.1,0.3,0.7,0.9})
3 0002 90 5 上記数式をC6セルまでドラッグ(コピー)します。
4 0003 15 1
5 0004 86 4
6 0005 49 3
<注>点数評価は次の区分としています。
上位10%以下=5、10%超30%以下=4、30%超70%以下=3、70%超90%以下=2、90%超100%以下=1

<参考>MATCH(検査値, 検査範囲, 照合の型)
<参考>PERCENTRANK(配列, x, 有効桁数)

  A B C
1 受験番号 点数 評価 数式
2 1001 60 D =IF(B2>89,"A",IF(B2>79,"B",IF(B2>69,"C",IF(B2>59,"D","E"))))
3 1002 88 B 上記数式をC6セルまでドラッグ(コピー)します。
4 1003 90 A
5 1004 59 E
6 1005 72 C
<注>点数評価は次の区分としています。
 90以上=A、80以上90未満=B、70以上80未満=C、60以上70未満=D、60未満=E

<参考>IF(論理式, 真の場合, 偽の場合)


0点から100点の範囲を集計するには
  A B C
1 点数 点数区分 人数
1. セルC2:C13の範囲を選択
2. 数式バーに =FREQUENCY(A:A,{0;9;19;29;39;49;59;69;79;89;99;100}) と入力して
3. [CTRL]+[SHIFT]+[Enter] を押して配列数式にします。

上記手順で
C2に0点の,C3に9点以下(一桁)の,C4に10点台の,...,C13に100点の人の人数が計算されます。
2 55 0点 1
3 60 1〜9点 0
4 29 10〜19点 0
5 80 20〜29点 1
6 0 30〜39点 0
7 47 40〜49点 1
8 82 50〜59点 1
9 76 60〜69点 2
10 75 70〜79点 3
11 90 80〜89点 2
12 65 90〜99点 1
13 70 100点 0
<参考>A列にテストの点数が並んでいるとして,C列に各点数の人数をカウントします。
0、1〜9、10〜19、20〜29、30〜39、40〜49、50〜59、60〜69,70〜79,80〜89,90〜99,100
の点数区分としています。
数式例では0点から100点までの分布となります。
点数はA列のすべて(A:A)が対象になります。範囲、点数区分は必要に応じて変更してください。

<参考>高得点(100点)から0点までの順で集計する場合の配列数式は以下のようになります。
{=FREQUENCY(A:A,{100;99;89;79;69;59;49;39;29;19;9;0})}


値そのものを、ランク(順位)表示するには
  A B C 数式
1 数値データ 大きい値 小さい値 ▼大きい値 1位〜 ▼小さい値 1位〜
2 40 80 -20 =LARGE(DATA,1) =SMALL(DATA,1)
3 10 40 0 =LARGE(DATA,COUNTIF(DATA,">="&B2)+1) =SMALL(DATA,COUNTIF(DATA,"<="&C2)+1)
4 20 20 6.5 =LARGE(DATA,COUNTIF(DATA,">="&B3)+1) =SMALL(DATA,COUNTIF(DATA,"<="&C3)+1)
5 80 10 10 ↑セルB3、C3に各数式を入力し、データ範囲(行9)まで、下へドラッグ(コピー)します↓
6 6.5 6.5 20 <方法>B3〜C3を選択して、セル右下の隅を下方向へずらします
7 0 0 40
8 -20 -20 80
9 80 #NUM! #NUM!  ←エラー #NUM! が表示され、有効な値が重複していることを示しています。
<注>数値のデータ範囲に、DATA という名前を付けています。上記例ではセル範囲(A1〜A9)です。
<参考>データ範囲に名前を付けるには、
範囲を選択して、名前ボックス(セル番地が表示されている場所)に、DATA と入力して、【Enter】 キーを押します。
あるいは、メニューの[挿入( I )]-[名前(N)]-[定義(D)]-[参照範囲(R)] でデータ範囲を指定して 【OK】 ボタンを押します。
<参考>データの値が重複してもいい場合は、
大きい順 1位 =LARGE(データ範囲,1) 2位 =LARGE(データ範囲,2) 3位 =LARGE(データ範囲,3) 
小さい順 1位 =SMALL(データ範囲,1) 2位 =SMALL(データ範囲,2) 3位 =SMALL(データ範囲,3) などとします。

<参考>数式で名前を用いず、範囲をA列すべて対象にする場合、数式を =LARGE(A:A,1) のようにします。
<参考>ランクは数値のデータが対象で、文字や空白は無視されます。

<参考>連続しないセルや、複数範囲を元にランク表示したい場合
=LARGE((範囲1,範囲2,範囲3),1) のように範囲をかっこ( )で囲んで指定します。


簡易グラフ作成と表示
  A B C D 数式
1 氏名 点数 点数グラフ          C2セル:
2 田中   33   ||||||||||||||||||||||||||||||||| 33 =IF(B2<=0,B2&" "&REPT("|",ABS(B2)),"")
3 鈴木 -20 -20 ||||||||||||||||||||   D2セル:
4 佐藤 60   |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 60 =IF(B2>0,REPT("|",B2)&" "&B2,"")
5 山田 40.9   |||||||||||||||||||||||||||||||||||||||| 40.9 C2〜D2セルを選択して、
6 木村 -27.3 -27.3 |||||||||||||||||||||||||||   下方向へドラッグ(コピー)します
7 阿部 0 0   ▼元の値を100で割り算の数式例
8 福島 1,230   |||||||||||| 1230 =IF(B8>0,REPT("|",B8/100)&" "&B8,"")
<注>C列は点数(値)がマイナスのため、セル配置を右詰めにします。
<注>グラフにしたい値が大きい場合、大きな列幅となるため工夫が必要です。
行8では、値が大きいので =元の値(X)/100  と数式中で割り算して桁数を減らしています。
<参考>関数ABSでマイナス値を絶対値に変換し、エラー#VALUE! を防止しています。
<参考>関数REPTは文字列を指定回数くり返します。なお、小数点以下は切り捨てられます。


TOP / FAQ1 / FAQ2 / FAQ3 / 上へ

Copyright © 2013 TOMBO. All rights reserved.