エクセルの勉強部屋のホ－ムへ

Excel 2013の新しい関数

■財務

  

PDURATION

投資が指定した価値に達するまでの投資期間を返します。

RRI

投資の成長に対する等価利率を返します。

■日付/時刻

  

DAYS

2 つの日付の間の日数を返します。

ISOWEEKNUM

指定された日付のその年における ISO 週番号を返します。

■数学/三角

  

ACOT

数値の逆余接を返します。戻り値の角度は 0 ～ Pi の範囲のラジアンとなります。

ACOTH

数値の逆双曲線余接を返します。

ARABIC

ローマ数字をアラビア数字に変換します。

BASE

数値を特定の基数 (底) を持つテキスト表現に変換します。

CEILING.MATH

数値を最も近い整数、または最も近い基準値の倍数に切り上げます。(Excel 2010：CEILING.PRECISE)

COMBINA

すべての項目から指定された個数を選択するときの組み合わせ (反復あり) の数を返します。

COT

角度の余接を返します。

COTH

数値の双曲線余接を返します。

CSC

角度の余割を返します。

CSCH

角度の双曲線余割を返します。

DECIMAL

指定された底の数値のテキスト表現を 10 進数に変換します。

FLOOR.MATH

数値を最も近い整数、または最も近い基準値の倍数に切り下げます。(Excel 2010：FLOOR.PRECISE)

MUNIT

指定された次元の単位行列を返します。

SEC

角度の正割を返します。

SECH

角度の双曲線正割を返します。

■統計

  

BINOM.DIST.RANGE

二項分布を使用した試行結果の確率を返します。

GAMMA

ガンマ関数値を返します。

GAUSS

標準正規分布の累積分布関数より小さい 0.5 を返します。

PERMUTATIONA

指定した数の対象 (反復あり) から、指定された数だけ抜き取る場合の順列の数を返します。

PHI

標準正規分布の密度関数の値を返します。

SKEW.P

人口に基づく分布の歪度 (ひずみ) を返します。歪度とは、分布の平均値周辺での両側の非対称度を表す値です。

■検索/行列

  

FORMULATEXT

数式を文字列として返します。

■文字列操作

  

NUMBERVALUE

文字列をロケールに依存しない方法で数値に変換します。

UNICHAR

指定された数値により参照される Unicode 文字を返します。

UNICODE

文字列の最初の文字に対応する番号 (コード ポイント) を返します。

■論理

  

IFNA

式が #N/A に解決される場合に指定する値を返します。それ以外の場合は、式の結果を返します。

XOR

すべての引数の排他的論理和を返します。

■情報

  

ISFORMULA

参照が数式を含むセルに対するものかどうかを確認し、TRUE または FALSE を返します。

SHEET

参照されるシートのシート番号を返します。

SHEETS

参照内のシート数を返します。

■エンジニアリング

  

BITAND

2 つの数値のビット単位の ‘And’ を返します。

BITLSHIFT

左に移動数ビット移動する数値を返します。

BITOR

2 つの数値のビット単位の ‘Or’ を返します。

BITRSHIFT

右に移動数ビット移動する数値を返します。

BITXOR

2 つの数値のビット単位の ‘Exclusive Or’ を返します。

IMCOSH

複素数の双曲線余弦を返します。

IMCOT

複素数の余接を返します。

IMCSC

複素数の余割を返します。

IMCSCH

複素数の双曲線余割を返します。

IMSEC

複素数の正割を返します。

IMSECH

複素数の双曲線正割を返します。

IMSINH

複素数の双曲線正弦を返します。

IMTAN

複素数の正接を返します。

■Web

  

ENCODEURL

URL にエンコードされた文字列を返します。

FILTERXML

指定されたXPath に従って、XML コンテンツの特定データを返します。

WEBSERVICE

Web サービスからデータを返します。


Excel2010の新しい関数

Excel2010では、NETWORKDAYS.INTL関数やWORKDAY.INTL関数といった、以前ある関数の機能をより拡張した関数が追加になっています。

関数分類 関数名 簡易説明
日付と時刻 NETWORKDAYS.INTL 期間内の稼動日の日数を求める（週末および休日を含めない）
WORKDAY.INTL 指定した日数分の日付を求める（週末および休日を含めない）
数学/三角 AGGREGATE 範囲内で集計方法を選択して集計する（エラー無視）
CEILING.PRECISE 基準値の倍数に近い値に切り上げる（正負に関係なく）
FLOOR.PRECISE 基準値の倍数に近い整数値に切り捨てる
ISO.CEILING 最も近い整数または最も近い基準値に切り上げた数値を求める
統計 CHISQ.DIST カイ 2 乗分布の左側確率の値を求める
CHISQ.INV カイ 2 乗分布の左側確率の逆関数の値を求める
CONFIDENCE.T スチューデントの t 分布を使用して、母集団に対する信頼区間を求める
COVARIANCE.S 標本の共分散を求める
F.DIST 左側 F 確率分布を求める
F.INV 左側 F 確率分布の逆関数を求める
GAMMALN.PRECISE ガンマ関数の自然対数を求める
MODE.MULT データ範囲内で反復的に出現する値を求める
PERCENTILE.EXC 百分位で位置を求める(0 より大きく 1 より小さい)
PERCENTRANK.EXC 百分率に基づく順位を求める(0 より大きく 1 より小さい)
QUARTILE.EXC 配列から四分位数を求める(0 より大きく 1 より小さい)
RANK.AVG 指定した数値の順位を求める（同じ数値が複数の場合は平均順位）
T.DIST 左側のスチューデントの t 分布を求める
T.INV スチューデントの t 分布の左側逆関数を求める
エンジニアリング ERF.PRECISE 誤差関数の積分値を求める
ERFC.PRECISE 相補誤差関数の積分値を求める

Excel2010から名前が変更になった関数

互換性のある関数 (参照)

関数に関する詳細なヘルプを表示するには、以下の一覧でいずれかのリンクをクリックしてください。

重要    次に示す関数は新しい関数に置き換えられ、精度が向上していたり、名前が用途が簡単にわかるものに変更されていたりします。これらの関数は、以前のバージョンの Excel との互換性を保つために引き続き使用することができます。ただし、下位互換性が必要ない場合、新しい関数の名前はその機能をより正確に表しているため、今後は新しい関数を使用してください。

変更前の関数 変更後の関数名 説明
BETADIST BETA.DIST β分布の累積分布関数の値を返します。
BETAINV BETA.INV 指定されたβ分布の累積分布関数の逆関数の値を返します。
BINOMDIST BINOM.DIST 二項分布の確率関数の値を返します。
CHIDIST CHISQ.DIST.RT カイ 2 乗分布の片側確率の値を返します。
CHIINV CHISQ.INV.RT カイ 2 乗分布の片側確率の逆関数の値を返します。
CHITEST CHISQ.TEST カイ 2 乗 (χ2) 検定を行います。
CONFIDENCE CONFIDENCE.NORM 母集団に対する信頼区間を返します。
COVAR COVARIANCE.P 共分散を返します。共分散とは、2 組の対応するデータ間での標準偏差の積の平均値です。
CRITBINOM BINOM.INV 累積二項分布の値が基準値以下になるような最小の値を返します。
EXPONDIST EXPON.DIST 指数分布関数を返します。
FDIST F.DIST F 分布の確率関数の値を返します。
FDIST F.DIST.RT F 分布の確率関数の値を返します。
FINV F.INV F 分布の確率関数の逆関数の値を返します。
FINV F.INV.RT F 分布の確率関数の逆関数の値を返します。
FTEST F.TEST F 検定の結果を返します。
GAMMADIST GAMMA.DIST ガンマ分布関数の値を返します。
GAMMAINV GAMMA.INV ガンマ分布の累積分布関数の逆関数の値を返します。
HYPGEOMDIST HYPGEOM.DIST 超幾何分布関数の値を返します。
LOGINV LOGNORM.INV 対数正規分布の累積分布関数の逆関数の値を返します。
LOGNORMDIST LOGNORM.DIST 対数正規分布の累積分布関数の値を返します。
MODE MODE.SNGL 最も頻繁に出現する値 (最頻値) を返します。
NEGBINOMDIST NEGBINOM.DIST 負の二項分布の確率関数の値を返します。
NORMDIST NORM.DIST 正規分布の累積分布関数の値を返します。
NORMINV NORM.INV 正規分布の累積分布関数の逆関数の値を返します。
NORMSDIST NORM.S.DIST 標準正規分布の累積分布関数の値を返します。
NORMSINV NORM.S.INV 標準正規分布の累積分布関数の逆関数の値を返します。
PERCENTILE PERCENTILE.INC 配列のデータの中で、百分位で率に位置する値を返します。
PERCENTRANK PERCENTRANK.INC 配列内での値の順位を百分率で表した値を返します。
POISSON POISSON.DIST ポアソン分布の値を返します。
QUARTILE QUARTILE.INC 配列に含まれるデータから四分位数を抽出します。
RANK RANK.AVG 数値のリストの中で、指定した数値の序列を返します。
RANK RANK.EQ 数値のリストの中で、指定した数値の序列を返します。
STDEV STDEV.S 引数を正規母集団の標本と見なし、標本に基づいて母集団の標準偏差の推定値を返します。
STDEVP STDEV.P 引数を母集団全体と見なし、母集団の標準偏差を返します。
TDIST T.DIST.RT スチューデントの t 分布の値を返します。
TINV T.INV.2T スチューデントの t 分布の逆関数の値を返します。
TTEST T.TEST スチューデントの t 検定に関連する確率を返します。
VAR VAR.S 引数を正規母集団の標本と見なし、標本に基づいて母集団の分散の推定値 (不偏分散) を返します。
VARP VAR.P 引数を母集団全体と見なし、母集団の分散 (標本分散) を返します。
WEIBULL WEIBULL.DIST ワイブル分布の値を返します。
ZTEST Z.TEST z 検定の片側確率の値を返します。

戻る
値が0または空白を含まないで、セル範囲の乗算をする方法

添付ファイルを参照して下さい。
値が0のセルを含んで乗算すると、0となってしまいます。
0と空白を含まないで、セル範囲の乗算をする方法
　 A B C D
1 20 15 20 6,000
2 50 60 3,000
3 40 0 100 4,000
4 50 10 85 42,500
5 25 150 3,750
6 合計 59,250

乗算の条件には、セルの値が0より大きい場合として「配列数式」で計算してみます。
セルD1には、
=PRODUCT(IF(A1:C1>0,A1:C1))
と入力し、[Shift]+[Ctrl]+[Enter]で、「配列数式」として確定します。
確定後数式は、{ }でくくられます。
自分で、{ }を入力してはいけません。
数式の意味
もし、セル範囲A1:C1の値が、0より大きい場合、セル範囲A1:C1の積を返します。

それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「数学+三角」
PRODUCT
http://kiyopon.sakura.ne.jp/kansuu/abs.html#product
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
を、参照してください。



戻る
数式を変更しようとすると、「配列の一部を変更できません」とメッセージが出る

セル範囲D5:E5の数式を変更しようとすると、「配列の一部を変更できません」とメッセージが出るのは、
セル範囲D5:E5が、「配列数式」となっているためです。
これは、
セル範囲D5:E5を選択した状態で、セルに数式を入力し、
[Shift]キーを押しながら、[Ctrl]キーを押し、[Enter]キーで確定した場合に「配列数式」となり数式が、{　 }でくくられます。
この場合、セル範囲D5:E5には、同時に同じ数式が入力されます。
この状態では、1つのセル(例えばD5のセル)だけの数式を変更したり削除したりしようとしすると、
「配列の一部を変更できません」
という、メッセージが表示されます。

「配列数式」を変更する
2つのセル範囲D5:E5のどれか一つのセルを選択し、
数式を変更し、[Shift]+[Ctrl]+[Enter]で数式を再び配列数式として確定します。
数式が、{　 }でくくられます。

「配列数式」を解除する
2つのセル範囲D5:E5のどれか一つのセルを選択し、
[Ctrl]+[Enter]で確定します。
これで、配列数式ではなく、通常の数式を同時に2つのセルに入力した状態となります。


戻る
5列のセルに3種類の値がすべて含まれる行に○を返す
添付ファイルを参照してください。


セル範囲に指定のセルの値が含まれる場合は、1を返す数式を３つ加算し、その合計が3以上の場合に"○"を返すようにします。

セルG4には、
=IF(IF(COUNTIF(B4:F4,$B$13)>0,1)+IF(COUNTIF(B4:F4,$C$13)>0,1)+IF(COUNTIF(B4:F4,$D$13)>0,1)>=3,"○","")
と入力します。
数式の意味
もし、セル範囲B4:F4の値が、セル$B$13の値と等しいセル数が、0より大きい場合、1を返し、
もし、セル範囲B4:F4の値が、セル$C$13の値と等しいセル数が、0より大きい場合、1を返し、
もし、セル範囲B4:F4の値が、セル$D$13の値と等しいセル数が、0より大きい場合、1を返し、
その合計します。
もし、その値が、3以上の場合、文字列"○"を返し、それ以外は、長さ0の文字列を返します。

それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
「統計関数」
COUNTIF
http://kiyopon.sakura.ne.jp/kansuu/abs.html#countif

を、参照してください。

戻る
VLOOKUP関数でワイルドカードを使いたい(検索文字列を含む文字を検索する方法は？)
LOOKUP関数とFIND関数を使います。
具体的には、
セル範囲A2:B5にデータリストが入力してあるとします。
A B C D E
1 データ 分類 　 検索値 結果
2 1234-11 分類A 　 5678 分類B
3 23-5678 分類B 　 1234 分類A
4 1200bbb 分類C 　 ab770 分類D
5 トクシュab770 分類D 　 1200 分類C

検索値(5678)をデータ(23-5678)に含む場合

セルD2に検索値(5678)が入力してあるとします。

セルE2には、
=LOOKUP(COUNTA(A:A),FIND(D2,$A$2:$A$5),$B$2:$B$5)
と入力します。
LOOKUP関数の最初の引数(COUNTA(A:A))は、A列のデータ数を返します。
FIND関数の最初の引数(D2)は、検索値となります。
FIND関数の2つ目の引数($A$2:$A$5)は、検索対象のセル範囲
LOOKUP関数の3つ目の引数($B$2:$B$5)は、抽出対象のセル範囲
として入力します。

数式の意味
FIND関数で、検索値の5678が、
{1234-111,23-5678,1200bbb,トクシュab770}
のデータにあれば、数値の1が返り、それ以外は#VALUE!が返りますので、配列で
{#VALUE!,1,#VALUE!,#VALUE!,,#VALUE!}
が、返ります。
LOOKUP関数で数値があるセル番目の行(この場合$A$2:$A$5の2番目の行ですから3行目)のB列の値(この場合B3セル)を返します。


データ(5678)を検索値(23-5678)に含む場合
   A B  C  D  E 
 1 データ 分類   検索値 結果
 2 5678 分類A   1234-111 分類B
 3 1234 分類B   23-5678 分類A
 4 ab770 分類C   1200bbb 分類D
 5 1200 分類D   トクシュab770 分類C

セルE2には、
=LOOKUP(COUNTA(A:A),FIND($A$2:$A$5,D2),$B$2:$B$5)
と入力します。
LOOKUP関数の最初の引数(COUNTA(A:A))は、A列のデータ数を返します。
FIND関数の最初の引数($A$2:$A$5)は、検索値のセル範囲
FIND関数の2つ目の引数(D2)は、検索対象となります。
LOOKUP関数の3つ目の引数($B$2:$B$5)は、抽出対象のセル範囲
として入力します。

数式の意味
FIND関数で、検索値の1234-111が、
{5678,1234,ab770,1200}
のデータにあれば、数値の1が返り、それ以外は#VALUE!が返りますので、配列で
{#VALUE!,1,#VALUE!,#VALUE!,,#VALUE!}
が、返ります。
LOOKUP関数で数値があるセル番目の行(この場合$A$2:$A$5の2番目の行ですから3行目)のB列の値(この場合B3セル)を返します。

それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「文字列関数」
LOOKUP
http://kiyopon.sakura.ne.jp/kansuu/address.html#lookup

「検索+行列」
FIND
http://kiyopon.sakura.ne.jp/kansuu/val.html#find
を、参照してください。


戻る
4月1日現在の年齢を返す数式
セルA1に生年月日が日付入力形式(yyyy/m/d)で入力してあるとします。

4月1日現在の年齢を返す数式
=IF($A1=0,0,DATEDIF($A1,DATE(YEAR(TODAY()),4,1),"y"))
で、年齢を計算しています。
数式の意味
もし、セルA1の値が0ならば、0を返します。
それ以外は、
セルA1の生年月日から
本日の年と、4を月とし、1を日とした日付
(本年が2013年の場合2013/4/1)を返し、
の期間内の満年数を返します。

1/1現在の年齢を返す数式
=IF($A1=0,0,DATEDIF($A1,DATE(YEAR(TODAY()),1,1),"y"))

本日現在の年齢を返す数式
=IF($A1=0,0,DATEDIF($A1,TODAY(),"y"))

それぞれの関数の説明は「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
「論理関数」
IF
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
「日付・時刻」
YEAR
http://kiyopon.sakura.ne.jp/kansuu/date.html#year
DATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
TODAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#today
DATEDIF
http://kiyopon.sakura.ne.jp/kansuu/date.html#dateif
を、参照してください。


戻る
シート厚と巻管径と巻取長から巻厚を計算する式
①厚み：15ｍｍのシートを
厚み：5ｍｍ内径167ｍｍの巻き取り管に500ｍ巻き取ったら
巻き取り管の表層から巻き取ったシートの表層までの巻厚は何センチになるのか？
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
添付ファイルを参照してください。

記号      セル
t       D5      原料厚み    0.025mm
        D6      巻き取り管内径 167mm
        D7      巻き取り管厚み 5mm
d       =D6+D7
D       D8      巻き厚み    700mm
L       D9      巻きｍ数    500000mm

の場合
D=SQRT((4*D5*D9/PI())+(D6+D7*2)^2)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
②厚み：15ｍｍのシートが
厚み：5ｍｍ内径167ｍｍの巻き取り管に巻き取られている状態で
巻き取り管の表層から巻き取ったシートの表層までの巻厚が７０センチの場合何メートル巻かれているのか？
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
同様に、
L=PI()*(D8^2-(D6+D7*2)^2)/(4*D5)


戻る
セルB2に=G2,セルB3に、=H2,セルB4に、=I2と数式をオートフィルで入力したい
INDIRECT関数を使います。
セルB2には、
=INDIRECT("R2C"&ROW()+5,FALSE)
と入力します。
数式の意味
文字列"R2C"とも数式入力セルの行番号+5(この場合2+5=7)を文字列結合し、"R1C1"
形式のセル参照式とします。
この場合"R2C7"ですから、セルG2へのセル参照となります。

このセルの数式を下のセルにコピーして
セルB3では、
=INDIRECT("R2C"&ROW()+5,FALSE)
の場合、数式入力セルの行番号+5(この場合3+5=8)で、
"R2C8"ですから、セルH2へのセル参照となります。

INDIRECT関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」
INDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を、参照してください。


戻る
重量と距離の数値で運賃を検索したい
具体的に重量と距離とも数値の場合には次のようになります。
INDEX関数とMATCH関数を使います。

添付ファイルを参照してください。

セル範囲A2:A7に重量の敷居値を数値のみで0,2,4,8,16･････などと入力します。
数値でないと正しくデータが検索できません。
単位を自動的に表示するために、セル範囲A2:A7を選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「#"Kg"」と入力します。

セル範囲B1:E1に距離の敷居値を数値のみで0,5,30と入力します。
数値でないと正しくデータが検索できません。
単位を自動的に表示するために、セル範囲B1:E1を選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「#"km"」と入力します。

セル範囲B2:E7に対応する運賃のデータを入力します。

対象商品について
セルA10に、重量を数値で
5
と入力します。
単位を自動的に表示するために、セルA10を選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「#"Kg"」と入力します。

セルB10に、距離を数値で
4
と入力します。
単位を自動的に表示するために、セルB10を選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「#"km"」と入力します。 セルA11には、セルA10の検索値(この場合5)が、セル範囲A2:A7の何番目にあるかを返す数式
=MATCH($A$10,$A$2:$A$7,1)
と入力します。
数式の意味
セルA10の値(この場合5)を検索値として、セル範囲A2:A7の何番目にあるかを返します
(この場合4以上、8未満ですから、3が返ります)

同様に、
セルセルB10の検索値(この場合4)が、セル範囲B1:E1のの何番目にあるかを返す数式
=MATCH($B$10,$B$1:$E$1,1)
と入力します。
数式の意味
セルA10の値(この場合4)を検索値として、セル範囲B1:E1の何番目にあるかを返します
(この場合0以上、5未満ですから、1が返ります)

セルC10には、
=INDEX(B2:E7,A11,B11)
と入力します。
数式の意味
セル範囲B2:E7の、行をセルA11の数値、列をセルB11の数値としたセルの値を返します。

千の桁の桁区切りと単位を自動的に表示するために、セルC10を選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「#,###"円"」と入力します。

この場合は、
重量は、2Kg以上で、4Kg未満です。
距離は、5km未満ですから、400が返ります。

該当するデータがない場合は、#VALUEが返ります。
それぞれの関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「検索+行列」のINDEX
http://kiyopon.sakura.ne.jp/kansuu/address.html#index
MATCH
http://kiyopon.sakura.ne.jp/kansuu/address.html#match

を参照してください。



戻る
入力した数値を50の倍数に切り上げしたい
数値の切り上げには、CEILING関数を使います。
セルA1に数値を入力します。
セルB1に50の倍数に切り上げた値をかえすには、
=CEILING(A1,50)
と入力します。
CEILING関数の説明は「関数の使い方説明」の「丸め」「FLOORとCEILING」を参照してください。

戻る
セルに　８．５０　と打ち込んで　８：５０　と表示させたい
数式で、8.5と入力したセルとは別のセルに8:50と表示するには
セルA1に
8.5
と入力します。
セルB1に
=INT(A1)/24+(A1-INT(A1))*100/24/60
と入力します。
セルには、小数点の数値が表示されます。これは、時刻シリアル値で1が1日の数値です。
場合によっては、時刻シリアル値のそのままの値が
0.336806
などとセルに表示されます。
これを時刻の表示(8:50)にするには、
セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」の「分類」で「時刻」を選択し「種類」欄で、「13:30」を選択し、「ＯＫ」ボタンを押します。

戻る
時分(3時45分)をhmで入力した値(345)を10進数(3.75)に変換
A1セルに入力した(3時45分)
345
を10進数の
3.75
に変換するには、
B1セルに
=MOD(A1,100)/60+(A1-MOD(A1,100))/100
と入力します。

戻る
漢字の文字列を半角カナに変換したい
手順としては、漢字の文字列からPHONETIC関数でふりがなを取りだし、そのふりがなをASC関数で半角カナに変換します。
具体的には、A1セルに"勉強部屋"と入力されているとします。
=ASC(PHONETIC(a1))
となります。
コピーしたデータなどで、文字列のふりがなが正しく認識できない場合は、私の作成した「ふりがな表示」というアドインソフトを使えばふりがなが正しく認識されます。
このソフトを使う場合は、「アドインソフト使用上の注意」を必ずお読みください。

戻る
●の入力されたセル数を返す
COUNTIF関数を使います。
具体的には、セル範囲E8:E38で、セルに"●"の入力されたセル数を返すには、
=COUNTIF(E8:E38,"●")
と入力します。
COUNTIF関数の説明

戻る
[　]で囲まれた文字列を削除する

FIND関数で"["を検索し、LEFT関数でその位置+1の左側を表示します。
FIND関数で"]"を検索し、RIGHT関数でその文字の長さをLEN関数で求め、その右側を表示します。
それぞれの文字列を文字列結合関数&で結合します。
具体的には、次のようになります。
A1セルに文字列が入力されているとします。
B1セルに[]で囲まれる文字列を削除して返します。
=LEFT(A1,FIND("[",A1,1)-1)&RIGHT(A1,LEN(A1)-FIND("]",A1,1))
となります。
表示されて文字列を、元の値に置き換えるために、
この式のセルを「編集」「コピー」して、「編集」「形式を選択して貼り付け」で、「値」にチェックを入れて「ＯＫ」ボタンを押します。
セル範囲をコピーして、A1セルに貼り付けます。

戻る
2つの日付の間の数え月数を返す平成16年3月25日-平成16年6月10日=4
DATEDIF関数を使います。
DATEDIF関数は、２つの日付をシリアル値または文字列で指定し、その２つの日付の間の期間を満日数/満月数/満年数あるいは１ヶ月未満/1年未満の日数、/1年未満の月数で計算します。もともとはLOTUS 1-2-3との互換性のために用意された関数ですが、以外と用途か広く重宝な関数です。
A1セルに
平成16年3月25日
A2セルに
平成16年6月10日
と入力されいる場合
=DATEDIF(A1,A2,"M")
で月数2が返ります。
しかし、この場合4を返したいとのとですから、満月数ではなく数え月数とするために、
日付を差し引きして数え月数とします。
=DATEDIF(A1,A2,"M")+IF(DAY(A1)-DAY(A2)>0,2,1)
で、数え月数の4が返ります。
DATEDIF関数の説明

戻る
消費税の１円以下の端数処理について
消費税の計算で、税込み50,000の場合、消費税が2,380で、税抜きが47,619で、合計49,999となり合計が合わない

消費税を、「小数点以下切り捨て」としているためにどうしても１円の誤差が生じる可能性があります。
残念ながら、ご質問の税込み50,000の場合は誤差がでます。
財務省のホームページで確認したところ、消費税の1円未満の端数処理の方法は事業者で決めてよいことになっています。

その内容
「税抜価格」に上乗せする消費税相当額に１円未満の端数が生じる場合がありますが、その端数をどのように処理 (切捨て、切上げ、四捨五入など)して「税込価格」を設定するかは、

それぞれの事業者のご判断によることとなります。
消費税は商品の価格の一部を構成するものですので、取引金額には５％の消費税相当額が含まれており、具体的には、税込価格に含まれる消費税相当額は「税込価格×5／105」である
というのが原則的な考え方です。

従って、50,000の消費税は、2380.95238095238
となって、割り切れません。
消費税の1円未満を四捨五入すると
消費税は 2,381
税抜き金額は、　50,000-2,381=47,619
となります。
消費税の1円未満を切り捨てにすると
消費費税は 2,380
税抜き金額は 50,000-2,380=47,620
となります。
どちらにするかは、ご自分の判断によります。
的確な回答ができなくて申し訳ございません。
いずれにしても、「税込価格」の設定に当たっては、一義的には、現在の「税抜価格」に消費税相当額を上乗せした金額を「税込価格」として価格設定することになります。
「税込価格」をあらかじめ設定することは正しくありません。
「税込価格」=「税抜価格」+「税抜価格」*0.05
「税抜価格」を決めてから「消費税額」を計算して、「税抜価格」に加算するという考えが正しいのです。
従って、「税抜価格」を47,619とすれば、
小数点以下を切り捨てした場合、
消費税 2,380
総額 49,999
小数点以下を切り上げれば、
消費税 2,381
総額 50,000
となります。

「税抜価格」を 47,620とすれば、
小数点以下を切り捨てした場合、
消費税 2,381
総額 50,001
小数点以下を切り上げれば、
消費税 2,381
総額 50,001
となります。


戻る
ローン計算（元利均等償還）の関数式
PMT関数を使います。
添付ファイルを参照してください。

セルB2には、定期返済額を計算します。
元利金等返済における返済金額を返す式
=PMT(C5/12,C4,C1,C3)
PMT(年利率を12で割って月利にして,期間,現在価値,将来価値,支払期日)

セルE8には、借入金への参照式
=C1

セルB9には
=$C$2

セルC9には
=-E8*$C$5/12

セルD9には
=B9-C9

セルE9には
=E8+D9

セル範囲B9:E9を下の行に連続コピーします。

PMT関数の説明は、「関数の使い方説明」の「財務関数」の「PMT」
を参照してください。


戻る
セルに入力した「1234」という数値から、「1234.xls」のデータを参照する
INDIRECT関数は、参照文字列を介した間接的なセルの指定を行います。

具体的には
ファイル名を参照する場合は、そのファイルがすでに開いている必要があります。
ファイルが開いている場合は、次のようになります。
セルA1に、1234と入力してある場合
ファイル"1234.xls"のシート"Sheet1"セルA10を参照するには
=INDIRECT("["&A1&"]sheet1!A10")
となります。
ファイルが閉じている場合は、
#REF!
エラーが返ります。


戻る
TODAY関数で入力した日付が変わらないようにしたい
関数で日付を入力するのではなく、キーボード操作で日付を入力することで対応できると思います。
キーボード・ショートカットキーでは、
{Ctrl}+{;}
で、 現在の日付を入力することができます。

「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htm
に私の作成した「入力日時」ユーザー定義関数があります。
セルにデータを入力した日付と時刻を日付シリアル値で返します。
この「入力日時」では、値を入力した時にしかデータを返しませんので、その後データを入力し直さなければ日時は変わりません。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/nyuryokunitiji.htm
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。


戻る
２つのリストで共通する項目のみのデータで新しくリストを作る
VLOOKUP関数を使います。
リストが２つのシートに次のように入力されているとします。
リストＡ
 
A B
1 登録番号
2 1 東京都
3 2 埼玉県
4 3 千葉県
5 4 神奈川県
6 5 茨城県
7 6 群馬県
8 7 栃木県
9 8 山梨県
10 9 長野県
11 10 静岡県
リストＢ
A B
1 登録番号
2 1 鈴木
3 3 山田
4 5 佐藤
5 9 小林
6 10 斉藤
新しいシート
A B C
1 登録番号
2 1 東京都 鈴木
3 3 千葉県 山田
4 5 茨城県 佐藤
5 9 長野県 小林
6 10 静岡県 斉藤
新しいシートにリストＡのデータ範囲をコピーして貼り付けます。
セルC2には、セルA2のデータ(登録番号)と、リストＢのセル範囲A2:B6の右端列（この場合A列）のデータ(この場合登録番号)が同じ場合は、リストの2列目(この場
合はB列)のデータを返します。
=VLOOKUP(A2,リストＢ!$A$2:$B$6,2,FALSE)
と入力します。

このセルをコピーして下のセル範囲C11まで貼り付けます。
合致するデータがあれば、データが返り、なければ#N/Aエラーが返ります。
数式を入力したC列を選択し、「編集」「コピー」し、「編集」「形式を選択して貼り付け」で「値」にチェックを入れて「ＯＫ」ボタンを押します。
C列の数式がなくなって、データのみが表示されます。
リスト範囲のいずれかのデータセルを選択した状態で「データ」「フィルタ」「
オートフィルタ」を選択します。
A B C
1 登録番号
2 1 東京都 鈴木
3 2 埼玉県 #N/A
4 3 千葉県 山田
5 4 神奈川県 #N/A
6 5 茨城県 佐藤
7 6 群馬県 #N/A
8 7 栃木県 #N/A
9 8 山梨県 #N/A
10 9 長野県 小林
11 10 静岡県 斉藤

数式を入力した列（この場合C列）の右の▼ボタンを押して#N/Aを選択します。
#N/Aエラーの行番号の範囲を選択し「編集」「行の削除」を選択します。
「データ」「フィルタ」「オートフィルタ」を選択します。
これで、該当の重複データのみの行が残されました。

戻る
漢字で入力した名簿をＡＢＣ（アルファベット順に）並べ替える
 
同じような質問が「質問と回答」の「データベース」「2003/3/10」「自分の決めた独自の条件で並べ替える」にあります。

並べ替えは、通常五十音順（あいうえお順）やアルファベット順で昇順／降順に並べ替えられますが、自分の決めた条件で並べ替えもできます。
「ツール」「オプション」の「ユーザー設定リスト」で、自分が決めて順序を登録しておきます。
例えば、
A,B,C,D,E････

の順に並べ替える場合は、

手順1
「ツール」「オプション」で「ユーザー設定リスト」タブを開き、「ユーザー設定リスト」の「新しいリスト」を選択し、「リストの項目」に
A,B,C,D,E････
と入力し、「追加」ボタンを押します。

手順2
表の項目を並べ替える場合は、その表の任意のセルをクリックし、「データ」「並べ替え」を選択します。

手順3
キーとなる列を選んで、「オプション」ボタンをクリックし、「並べ替え順序の指定」の右の▼で、ユーザー設定で作成したリストを選択して「ＯＫ」ボタンを押します。

その内容
「データ」「並べ替え」を選択し、「最優先されるキー」に文字の入力されている列を選択し、「昇順」を選択して、「ＯＫ」ボタンを押します。
また、漢字のふりがなを表示するPHONETIC関数でふりがなを表示じしてみると正しいフ「フリガナ」になっていない場合がありますので確認してみてください。
PHONETIC関数の説明は「関数の使い方説明」の「文字列関数」PHONETICを参照してください。

確認として、「データ」「並べ替え」を選択し、「オプション」ボタンを押して、「方法」グループの「ふりがなを使う」にチェックが入っていることを確認してください。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
また、ふりがなが表示されない場合は、「エクセルで使えるソフト」の「ふりがな表示」
と言う「アドインソフト」を使うことで、ふりがなが表示できなかった漢字もふりがなが表示できるようになります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
アドインソフトを使うためには、必ずアドインソフト使用上の注意をお読みください。
 


戻る
日付が土日の場合その後ろの平日を関数で返す
A列に日付が2004/3/27のように日付書式で入力されています。
B列にA列の日付が土日の場合は次の平日を返すための関数が入力してあります。

セルB1には、
=IF(WEEKDAY(A1,2)>=6,A1+(8-WEEKDAY(A1,2)),A1)
と入力します。
関数の意味、
もし、A1セルの曜日(WEEKDAY関数で数値として返します。月1,火2,水3,木4,金5,土6,日7)が土日(6,7)の場合は、8-曜日数値(土曜日の場合は8-6=2)として、A1セルの日付に加算します。

関連する内容:日付が土日の場合その前の平日を関数で返す



戻る
数字を入力した隣りに支店名を自動表示したい
VLOOKUP関数を使います。
まず、番号と支店名の関連づけをしたリストを作成します。
この場合、シートSheet2に、次のようにリスト形式でデータを入力します。
A列セルの表示形式は、「文字列」としてください。
Sheet2
 
A B
1 支店番号 支店名
2 01 本店
3 02 沼田支店
4 03 利南支店
5 04 池田支店
次に、データを入力したいシートSheet1には、次のように入力します。
セルA1は、セルの表示形式を「文字列」としてください。
セルB1には、数式
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:B5,2,FALSE))
と入力します。
数式の意味、
A1セルが何も入力していない場合は、何も返しません。
A1セルの値がシートSheet2のセル範囲A1:B5の左端の列(この場合A列)のデータと
合致する行の2列目(この場合B列)のデータを返します。
Sheet1
 
A B
1 02 沼田支店
VLOOKUP関数の見本のファィルを参照してください。

戻る
=A1*A2+B1*B2+C1*C2+D1*D2+E1*E2+F1*F2+G1*G2という、セルのかけ算の合計を簡単に返したい
配列数式を使います。
 
　 A B C D E F G
1 1000000 100000 10000 1000 100 10 1
2 5 6 1 3 2 5 3
セルに
=SUM(A1:G1*A2:G2)
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後数式は、{}でくくられます。
{=SUM(A1:G1*A2:G2)}
自分で{}を入力してはいけません。
5613253
が返ります。

戻る
条件によりVLOOKUPの返すデータリストの列を変える
VLOOKUPの
検索値が、セルB1
検索範囲が、A4:C13
条件がA1に"男"または、それ以外
となる場合は、下のようになります。
 
A B C
1 男 25 0.0188
2
3 年齢 男 女
4 16 0.0188 0.0193
5 17 0.0179 0.0188
6 18 0.0174 0.0188
7 19 0.017 0.0188
8 29 0.0161 0.0188
9 39 0.0152 0.0171
10 49 0.0151 0.0171
11 59 0.0158 0.0163
12 59 0.0162 0.0172
13 70 0.0168 0.0186
セルC1には
=VLOOKUP(B1,A4:C13,IF(A1="“男",2,3))
検索範囲をそれぞれの条件により変えるのではなくて、
検索範囲のデータの列番号を条件により変更しています。
 

戻る
上位３位までの得点の合計を出す

LARGE関数を使います。
（指定した順位の数値を返します）

氏名と得点の表が以下のように入力されているとします。

 
　 A B
1 氏名 得点
2 小泉純一 250
3 鈴木宗男 354
4 高橋一郎 165
5 佐々木小次郎 369
=LARGE(B2:B5,1)+LARGE(B2:B5,2)+LARGE(B2:B5,3)
上の式では、順位の1,2,3の数値を合計しています。
973 が返ります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
LARGE関数の説明については「関数の使い方説明」の「統計関数」LARGEを参照してください。

戻る
時間を１５分単位で丸めて切り上げ、切り捨て
１５分単位で切り上げる
=CEILING(F41,1/24/60*15)

３０分単位で切り上げる
=CEILING(F41,1/24/60*30)

１５分単位で切り捨てる
=FLOOR(F41,1/24/60*15)

３０分単位で切り捨てる
=FLOOR(F41,1/24/60*30)

エクセルでは、１日は1となるように日付シリアル値として数値で計算しています。
1時間は２４時間で割ると、1/24となります。
さらに60分で割り1/24/60となります。
これが1分の時刻シリアル値となります。
具体的には「時間の計算方法」を参照し てください。


戻る
数値を右から数えて下位ゴトに取り出したい
1234567890
とある数値を下位から
0,9,8,7,6,5,4,3,2,1

この場合は、LEN関数で、対象の文字列の文字数を取り出します。

 
A B C D E F G H I J K
1 1234567890 0 9 8 7 6 5 4 3 2 1
セルB1には
=MID($A1,LEN($A$1)-COLUMN()+COLUMN($B1),1)
と入力します。
この式をセルC1から右に連続コピーします。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
式の意味
COLUMN()
数式の入力してあるセルの列番号を返します。セルB1ならば2が返ります。
LEN($A$1)セルA1の文字数を返します。

LEN($A1)-COLUMN()+COLUMN($B1)
A1が10文字の場合で、式をセルB1に入力した場合
10-2+2
で、10が返ります。
セルC1に入力した場合
10-3-2
で、9が返ります。

MID($A1,LEN($A1)-COLUMN()+COLUMN($B1),1)
で、A1セルの文字列の左から指定の位置の文字を１文字取り出します。


戻る
条件により計算式を変える
ＩＦ関数を使います。
　 質問内容：Ｇ８に入れる、条件?と?とを満たす計算式をお教え下さい。
        条件?　Ｆ８が「０」または正数の場合は、Ｇ８＝Ｄ８
        条件?　Ｆ８が負数の場合には、Ｇ８＝Ｄ８+（Ｆ８の絶対数）

=D8+(IF(F8<0,ABS(F8),0))
となります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
IF関数の使い方の説明は「関数の使い方説明」
の「論理関数」のIFを参照してください。


戻る
シート全体を１００円未満切り捨てにしたい
同じシート上では簡単にはできません。
別の空白シートに１００円未満切り捨てで計算させ、その値をコピーすることも考えられます。
参照先のシートが[Sheet1]セルが[A1:A100]とすると
別のシートで、A1:A100を選択し、
=ROUNDDOWN(Sheet1!A1,-2)
と入力し、{Ctrl}キーを押しながら{Enter}キーで確定します。
対象のA1:A100まで一度に式が入力されました。
この結果を「編集」「コピー」し、SHeet1のA1を選択し、「編集」「形式を選択して貼り付け」で「値」を選択し、「ＯＫ」ボタンを押します。
計算結果が値として貼り付けとなりますので、参照先が計算式の場合は再計算できなくなります。

戻る
複数条件に合致する、データ集計表を作成する
 
A B C D E F G H
1 日付 担当者 商品名 売上金額 担当者 商品名 売上金額
2 12 月1日 田 中 パ ソコン 198,000 田中 パ ソコン 485,000
3 12 月1日 鈴 木 プ リンタ 25,400 田中 プ リンタ 36,800
4 12 月3日 佐 藤 パ ソコン 234,000 鈴木 パ ソコン 248,000
5 12 月3日 佐 藤 プ リンタ 39,800 鈴木 プ リンタ 51,200
6 12 月4日 鈴 木 パ ソコン 248,000 佐藤 パ ソコン 502,000
7 12 月4日 鈴 木 プ リンタ 25,800 佐藤 プ リンタ 39,800
8 12 月4日 田 中 プ リンタ 36,800
9 12 月6日 佐 藤 パ ソコン 268,000
10 12 月8日 田 中 パ ソコン 287,000
列Fの担当者名と、列Gの商品名の二つの条件に合致するデータの集計は

 セルH2に
 =SUM(($B$2:$B$10=F2)*($C$2:$C$10=G2)*$D$2:$D$10)
 と入力し、{Shift}+{Ctrl}*{Enter}で「配列数式」として確定します。
 確定後、数式は{   }でくくられます。
 {   }を自分で入力してはいけません。


戻る
１つのセルに入力したデータを１文字ずつ別のセルに振 り分ける
 
行毎に取り出す場合
セルA1に入力したデータをA2からA11に振り分ける方法を示します。
MID関数(文字を左からの指定位置から指定数を返します)
ROW関数(行番号を返します)
を使います。
A
1 あいうえおかきくけこ
2 あ
3 い
4 う
5 え
6 お
7 か
8 き
9 く
10 け
11 こ
セルA2には次の式を入力します。
=MID(A$1,ROW()-1,1)
この式を下のA3からA11まで連続コピーします。

列毎に取り出す場合
セルA1に入力したデータをB1からK1に振り分ける方法を示します。
MID関数(文字を左からの指定位置から指定数を返します)
COLUMN関数(列番号を返します)
を使います。

 
A B C D E F G H I J K
1 あいうえおかきくけこ あ い う え お か き く け こ
セルB1には次の式を入力します。
=MID($A1,COLUMN()-1,1)
この式を右のB1からK1まで連続コピーします。

MID関数の説明は
「関数の使い方説明」
の「文字列関数」{MID}を参照してください。
ROW関数の説明は
「関数の使い方説明」
の「検索・行列」の{ROW}を参照してください。
COLUMN関数の説明は
「関数の使い方説明」
の「検索・行列」の{COLUMN}を参照してくださ い。
 


戻る
消費税の計算で、小数点以下を切り捨てる
指定桁数で切り捨てるには、ROUNDDOWN関数を使います。
セルA1に商品代金￥550、セルB1に消費税の計算式
【=ROUNDDOWN(A1*0.05,0)】を入力。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
ROUNDDOWN関数の説明は「関数の使い方」の「丸め」ROUNDDOWNを参照してください。

戻る
参照式の列 を行に入れ替えSheet2の行順にSheet1!A1,Sheet1!B1としたい
INDIRECT関数を使います。
具体的には、
Sheet1のA1セルのデータをSheet2のA1に
Sheet1のB1セルのデータをSheet2のA2に
Sheet1のC1セルのデータをSheet2のA3に
参照させる式は
Sheet2のA1セルに
=INDIRECT("Sheet1!R"&COLUMN()&"C"&ROW(),FALSE)
と入力します。
これで、参照式の列と行が入れ替わります。

INDIRECT関数については「関数の使い方」の「検 索＋行列」のINDIRECT
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を参照してください。


戻る
7:00-8:00と8:00-9:00は違う数値に なるのか
7:00-8:00と8:00-9:00は、違う数値になります。
それぞれの時刻を有効数字15桁まで表示すると
=7/24=0.291666666666667
=8/24=0.333333333333333
=9/24=0.375000000000000
となります。
本当は
=7/24=0.291666666666666666666666666666666666．．．．．
=8/24=0.333333333333333333333333333333333333．．．．．
このように、上の二つの数値は違います。
7:00と8:00は割り切れない数値ですので、9:00(割り切れる数値)と差し引きすると違う数値として認識されます。
これは、コンピュータは数値は15桁までの桁が有効数字となるためです。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
従って、計算値の結果を小数点以下5桁で切り捨てする必要があります。
１分単位の時刻の場合は、シリアル値で小数点以下5桁までを切り捨てで表示すれば良いので、計算式は次のようになります。
=ROUNDDOWN("8:00"-"7:00",5)
=ROUNDDOWN("9:00"-"8:00",5)
これで、二つの時刻は同じ数値として計算されます。
結果の式
=IF(A2=B2,"一致","不一致")
は、そのままで一致が返ります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
ROUND関数については、「関数の使い方」「丸め」
http://kiyopon.sakura.ne.jp/kansuu/round.htm#rounddown
を参照してください。

戻る
成績一覧表から個票を作成する
この場合は、リストから該当するコード1つに対応するデータをVLOOKUP関数で返すのが一般的です。
具体的には、
シートSheet1のセル範囲A1からJ150にリストデータが次のように入力されているとします。
 
A B C D E F G H I J
1 コード 生徒氏名 国語 社会 数学 理科 英語 合計 クラス順 全順
2 1126 森文樹 50 60 50 60 50 270 30 88
3 1127 小泉純一 60 66 52 74 80 332 12 35
4 1128 宮本武蔵 70 72 54 88 110 394 7 21
．．．
150 1280 田中真紀 55 45 36 62 46 244 41 104
151 学年平均 50 50 50 50 50 250
個票はシートSheet2に
 
A B C D E F G H I J
1 コード 生徒氏名 国語 社会 数学 理科 英語 合計 クラス順 全順
2 1126 森 文樹 50 60 50 60 50 270 30 88
3 学年平均 50 50 50 50 50 250
のように表示するとします。
シートSheet2のセルA1にコード番号を1126を入力します。
セルB2に
=VLOOKUP($A2,Sheet1!$A$1:$J$150,COLUMN(),FALSE)
と入力します。
このセルを選択し、「編集」「コピー」し、次の列(C2からJ2まで)「編集」「貼り付け」ます。
セルB3に
=Sheet1!B$151
と入力します。
このセルを選択し「編集」「コピー」し、次の列(C3からJ3まで)「編集」「貼り付け」ます。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
VLOOKUP関数については「関数の使い方」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
「検索＋行列」のVLOOKUP
http://kiyopon.sakura.ne.jp/kansuu/address.html#vlookup

戻る
２つのセルに分けて苗字と名前を入力し た中から同一人物を削除したい
次のように考えます。
２つのセルのデータを結合し、苗字と名前を１つのセルに表示します。
苗字と名前を結合したセルに対して「重複」をチェックします。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
「重複データの２つ目以降に印を付ける」という内容がありますので、これを参考にしてください。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
具体的には
Ａ列に苗字
Ｂ列に名前
が100行入力されているとします。
 
A B C D
1 苗字 名前 氏名 重複
2 小泉 純一 小泉純一 1
3 田中 美智子 田中美智子 1
4 小泉 純一 小泉純一 0
．．． 1 
100 鈴木 宗一 鈴木宗一 1
セルC2に
=A2&B2
と入力し、下の行に連続コピーします。
セルD2に
=IF(COUNTIF($C$2:C2,C2)>1,0,1)
と入力します。
重複した氏名には0が表示され、１つしかない氏名には1が表示されます。
「データ」「フィルタ」「オートフィルタ」で列Dのフィルタ0を指定します。
重複している氏名だけが抽出されますので、行番号範囲を選択し、マウスの右ボタンのショートカットメニュー「行の削除」で対象データの行を削 除をします。
「データ」「フィルタ」で「オートフィルタ」のチェツクを外します。

戻る
各行ごとに最終データ入力のセル（月）を常に表 示する

下のような表の場合で、B列に各相手先の最終入力月を表示する例でご説明します。
 
A B C D E F G H
1 相 手先 年 /月 2000/5 2000/6 2000/7 2000/8 2000/9 2000/10
2
 ○ 商店 2000/6 23,456 6,543
3 △ 商事 2000/7 7,654 765 8,769
4 □ 運輸 2000/8 56,788 578 8,643 34,556
5
この例では、各行のB列に金額入力をしてある最終「年/月」を返します。
セルB2に
=INDIRECT("R1"&"C"&MAX(IF(C2:H2<>"",COLUMN(C1:H1))),FALSE)
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」として確定します。
確定後、数式は{   }でくくられます。
{   }を自分で入力してはいけません。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
また、入力のために各行の最終入力セルを選択する方法は、対象の行のデータ入力してあるいずれかのセル（例えば相手先明のセル）を選択し、 {Ctrl}を押しな
がら→を押しますと、最終入力セルを選択できます。
これで→を押せば入力セルを選択できます。

戻る
５行おきにデータを表示する
列を１つ追加して、次の式を入力します。
=MOD(ROW(),5)
これで行番号を５で割った余りが返り、５で割り切れる場合は0が返ります。
例えばA列にこの式を入力した場合は次のようになります。
 
　 A B
1 1 　
2 2 　
3 3 　
4 4 　
5 0 　
6 1 　
7 2 　
8 3 　
9 4 　
10 0 　
11 1 　
５行毎に表示したい場合は、A列のデータに「オートフィルタ」をかけ「0」を選択すれば、５行目毎にデータが表示されます。
行が先頭から５行目ではない場合は式に先頭行の値を加算します。
先頭行が3行目の場合は式を
=MOD(ROW()+ROW($A$3),5)
とします。
ROW()は該当行の行番号を返します。

戻る
現金出納帳に残高を正しく計算させる
シート{５月}の説明です。
 
A B C D E
1 現金出納帳（5月）
2
3
4 日付 摘要 借方 貸方　 残高
5 1日 繰 り越し 　 　 10,000
6 1日 小 遣い 5,000 　 15,000
7 2日 食 事 　 1,000 14,000
8 3日 買 い物 　 1,000 13,000
9 4日 ノー トなど 　 500 12,500
10 　 　 　 　 　
11 　 　 　 　 　
12 　 　 　 　 　
13 　 　 　 　 　
14 　 　 　 　 　
15 　 　 　 　 　
16 　 　 　 　 　
17 　 　 　 　 　
18 　 次 月繰越額 　 　 12,500
セルE5
前月の繰越額のセルへの参照式
='４月'!E18
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
セルE6
=IF(AND(C6=0,D6=0),"",E5+C6-D6)
借方・貸方ともに何も入力してない場合は計算結果の０を表示しません。
この式を下の行E17までコピーします。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
セルE18
残高の最終値が表示される
=INDIRECT("R"&MAX(IF(E5:E17<>"",ROW(E5:E17)))&"C"&COLUMN(E18),FALSE)
と入力し、{Shift}+{Ctrl}+{Enter}で「配列数式」としてかくています。
確定後、数式は{     }でくくられます。
自分で、{    }を入力してはいけません。
数値が表示されている行の最大行番号と、この数式の入力されている列番号から"R1C1"
形式の参照式を作成して数値を返します。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
INDIRECT関数の説明
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
ROW関数の説明
http://kiyopon.sakura.ne.jp/kansuu/address.html#row
COLUMN関数の説明
http://kiyopon.sakura.ne.jp/kansuu/address.html#column

戻る
80を1時間20分と表示させる
80の入力セルに1時間20分と表示させることはできません。
入力セルとは別のセルに1時間20分と表示させるには
セルA1に80と入力してある場合、
=A1/24/60
と入力します。
0.055555556
と表示されます。
セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示形式」タグの「分類」で、「時刻」を選択し、「種 類」の"13時30分"を選択し「ＯＫ」ボタンを押します。

エクセルでは、１日は1となるように日付シリアル値として数値で計算しています。
1時間は２４時間で割ると、1/24となります。
入力値の80は分ですから、さらに60分で割り1/24/60となります。
これが1分の時刻シリアル値となります。

時刻シリアル値として認識されたデータは、エクセルで時刻の計算に使用できます。
具体的には
「時間の計算方法」
を参照してください。


戻る
計算結果の答えを4の倍数にそろえたい
指定の数値の倍数にそろえる関数があります。
計算結果
230/193×58＝69
を、４の倍数で
切り捨てる場合は
=FLOOR(230/193*58,4)
で、答えは68となります。
切り上げる場合は
=CEILING(230/193*58,4)
で、答えは72となります。
それぞれの関数の説明はこちら
http://kiyopon.sakura.ne.jp/kansuu/round.htm#floop

戻る
文字列"2時間10分20秒"を時刻に変換する
hh時間mm分ss秒と入力されている場合は、
"時間"、"分"、"秒"を":"に置き換えることで、時刻に変換したことになります。
「編集」「置換」で「検索する文字列」に「時」と入力し、「置換後の文字列」に「:」と入力し、「すべて置換」ボタンを押します。
同様に「分」「秒」もそれぞれ「:」に置き換えます。
2:10:20
と表示され、時刻シリアル値として認識されます。
表示を元との2時間10分20秒としたい場合は、セル範囲を選択し「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスの「表示 形式」タグの「分類」で、「時刻」を選択し、「種類」リストボックスから「13時30分55秒」を選択し、「ＯＫ」ボタンを押します。
このようにすれば、時間として足し算引き算平均などの四則演算の計算ができるようになります。

時刻を10進数で表す 8:30→8.5


戻る
4月1日から6月2日の月数を３ヶ月とする
DATEDIF関数とIF関数を組み合わせます。
具体的には
 
A
1 4月1日
2 6月2日
3 3
 
DATEDIF関数を使います。
この関数は、数式の日付関数からは入力候補がでませんが、隠し関数でも何でもありません。

セルA3に指定日付の間の数えの月数を返します。
=DATEDIF(A1,A2,"M")+IF(DATEDIF(A1,A2,"MD")>0,1,0)
セルA3の「セルの書式設定」は「標準」にします。

関数の意味
指定期間の満月数＋１ヶ月未満の日付が0より大きい場合に1を返し、それ以外は0を返した値。

DATEDIF関数の説明


戻る
セルに特定の数値が含まれているセルの数を数える
Find関数でその数値が含まれているかどうかを検索します。
配列数式でFind関数の結果がエラーでなセルを加算します。
具体的には次のようになります。
 
A B C D E F G H
1
2 1 2 8
3 5 1 1
 4 7 1 5,5,6
5 5 1 5
6 1 1,2 1
7 2 1,5,6 2
8 4 1,2 4
9 5 6 4
10
11 1 2 3 4 5 6 7 8
12 10 5 0 3 6 3 1 1
上の表の中で1の数字が何個あるが数えたい場合。
行11に検索値を入力しています。

セルA12に
=SUM(IF(ISERR(FIND(A11,$A$2:$C$9,1))<>TRUE,1))
と入力し{Shift}+{Ctrl}+{Enter}で、「配列数式」として確定します。
配列数式は、確定後数式が{    }でくくられます。
{  }を自分で入力してはいけません。
この数式を、セルB12:I12まで連続コピーします。
数式の意味
find関数は文字列が含まれていないと#VALUEエラーを返しますので、エラー以外のセルに対してIf関数で1を返し、SUM関数で集計 します。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
配列数式については
「複数の検索条件に合致するセルをカウントする（配列数式）」
を参照してください。


戻る
０を含まないで平均を求める
countif関数で0以外のセルの数を求めて計算します。
具体的には、
セルA1:A13に数値が、0を含めて入力してあります。
０を含まないで平均値を求める式は
=SUM(A1:A13)/COUNTIF(A1:A13,">0")
となります。
他の方法

戻る
15:30を午後３時３０分と表示したい
セルの書式設定では
h:m AM/PM
などのように表示はできますが、"午前"/"午後"の表示はできないようです。
入力セルでそのまま表示書式で設定することはできないようです。
次のように時刻の入力セルとは違うセルに表示することは可能です。

A1セルに時刻を15:30と入力してある場合
=IF(A1<0.5,"午前","午後")&JIS(TEXT(IF(A1<0.5,A1,A1-0.5),"h時m 分"))
時刻表示したい別のセルに入力します。
午後３時３０分
と表示されます。
（この計算式の返値は文字列となりますので計算には使えません。）

1日は1で、12時は0.5ですから、それを境にif関数で午前と午後を区分しています。
数値はJIS関数で半角から全角に変換しています。
TEXT関数で時分を表示させています。

時刻を10進数で表す 8:30→8.5


戻る
生年月日より学校入学・卒業年を求めるには
A1セルに生年月日が1996/3/4と入力されているとします。
早生まれの処理は、IF関数にてTEXT関数で求めた月日の数値が401より小さい場合に、対象の年より1をマイナスします。

=TEXT(YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,-1,0)+6,"####年 小学校入学")
2001年小学校入学

=TEXT(YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,-1,0)+12,"#### 年小学校卒業")
2007年小学校卒業

=TEXT(YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,-1,0)+12,"#### 年中学校入学")
2007年中学校入学

=TEXT(YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,-1,0)+15,"#### 年中学校卒業")
2010年中学校卒業
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
元号で表示する場合は次のようになります。
=TEXT(DATEVALUE(YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,-1,0)+6&"/4/1"),"ggge年小学校入学")
平成13年小学校入学

=TEXT(DATEVALUE(YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,-1,0)+12&"/4/1"),"ggge年小学校卒業")
平成19年小学校卒業

=TEXT(DATEVALUE(YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,-1,0)+12&"/4/1"),"ggge年中学校入学")
平成19年中学校入学

=TEXT(DATEVALUE(YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,-1,0)+15&"/4/1"),"ggge年中学校卒業")
平成22年中学校卒業

-------------------------------------------------
関数の説明は「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
のなかにあります。
IF関数の説明
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
TEXT関数の説明
http://kiyopon.sakura.ne.jp/kansuu/val.html#text


戻る
２進数換算  10111001101 を  十進数に
数値が10桁以上になりますから二進数を10進数に変換する関数BIN2DEC(数値)は、使えません。
人間系で、2進数の数値を10進数に変換すします。
二進数の各桁は、下位より2の0乗、2の1乗,2の2乗････というように重みを持ちます。
10111001101
の各桁に対して
 
1 ×2の10乗 1024
0 ×2の9乗 0
1 ×2の8乗 256
1 ×2の7乗 128
1 ×2の6乗 64
0 ×2の5乗 0
0 ×2の4乗 0
1 ×2の3乗 8
1 ×2の2乗 4
0 ×2の1乗 0
1 ×2の0乗 1
得られた数値の合計1485が答えです。

具体的には、
セルA1に10111001101が入力されている場合

 
A B C
1 10111001101 1485
2 1 10 1024
3 0 9 0
4 1 8 256
5 1 7 128
6 1 6 64
7 0 5 0
8 0 4 0
9 1 3 8
10 1 2 4
11 0 1 0
12 1 0 1
セルA2には各桁の数値を返す式
=MID($A$1,ROW(A1),1)
と入力し、下の行にコピーします。
セルB2には各桁のべき乗の乗数を返す式
=LEN($A$1)-ROW(A1)
と入力し、下の行にコピーします。
セルC1には計算結果を合計する式
=SUM(C2:C12)
セルC2には各桁の2のべき乗を返す式
=A2*2^B2
と入力し、下の行にコピーします。

戻る
指定した範囲内で、条件を満たした値の２番目 に大きい値を抽出
IF関数の「配列数式」とLARGE関数を使います。
指定した範囲で条件を満たすセルを返すには、例えばセル範囲A1:A100で条件100より大きいセルの２番目に大きいデータを返すには
=LARGE(IF(A1:A100>100,A1:A100),2)
と入力し、{Shift}+{Ctrl}+{Enter}で配列数式として確定します。「配列数式」は確定後{   }でくくられます。
{   }を自分で入力してはいけません。
「配列数式」については、
 複数の検索条件に合致するセルをカウントする(配列数式)を参照してください。

戻る
数式をコピーした時の数式のセル番地を変えない ようにしたい
セル参照を「絶対参照」にすればいいです。

2002/2/12 「絶対参照」と「相対参照」についてにあります。

「エクセルの勉強部屋」の「操作説明」の「数式の利用」の なかに 「相対参照・絶対参照・複合参照」がありますので、参考にしてください。

具体的には、セルA1の参照を絶対参照とすると
=$A$1
早退参照とすると
=A1
複合参照では
=$A1
=A$1
となります。


戻る
氏名欄の氏名に空白があるものを取り除きたい
方法は２つあります。
１つ目は「編集」「置き換え」でスペースを何もなしに置き換えます。
具体的にはセル範囲を選択し「編集」「置き換え」で「検索文字列」にスペースを入力し、「置き換え」ボタンを押します。

２つ目は「文字列の置き換え」関数
SUBSTITUTE
を使います。
具体的には、セルA1に全角の空白が入力されている場合
他のセル 例えばB1セルに次のように入力します。
=SUBSTITUTE(A1,"　",,1)
スペースが半角の場合は次のようになります。
=SUBSTITUTE(A1," ",,1)
この計算結果のセル範囲を選択し、「編集」「コピー」し、対象のセル範囲の先頭セルを選択し、「編集」「形式を選択して貼り付け」で「値」を 選択し、「ＯＫ」ボタンを押します。


戻る
IF(ISERROR(計 算式）、””、計算式）など、組み合わせた計算式を登録する
IF(ISERROR(計算式）、””、計算式）というような式を登録する方法としては、邪道ですが、漢字の「単語登 録」機能を使えば良いでしょう。
IF(ISERROR(計算式）、””、計算式）を入力し、選択あるいはコピーし、漢字として「単語登録」によって読みを登録します。
読みを"iserr"などとしておけばよいでしょう。

戻る
１０進数の3425を２進数に変換する
この数値は５１２以上ですし、戻り値は１０桁以上になるため、エラー#NUM!がかえり変換できません。
512未満の数値や、戻り値10桁までの場合は、DEC2BIN関数が使ええます。

１０進数を２進数に変換する
DEC2BIN(数値,桁数)
２進数を１０進数に変換する場合は
BIN2DEC(数値,桁数)
１０進数を１６進数に変換する場合は
DEC2HEX(数値,桁数)
１０進数を８進数に変換する場合は
DEC20OCT(数値,桁数)
２進数を１６進数に変換する場合は
BIN2HEX(数値,桁数)
２進数を８進数に変換する場合は
BIN2OCT(数値,桁数)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
3425を２進数に換算をするには
人間系で
3425を2で割り1622その余り1
1622をさらに2で割りその余り0
というように2で割った商(整数値)と、その余りを計算し、商が1あるいは、0になりまで繰り返します。
具体的には下のようになります。

 
元の数 3245 余り
÷2 1622 1 ←最下位ビット
÷2 811 0
÷2 405 1
÷2 202 1
÷2 101 0
÷2 50 1
÷2 25 0
÷2 12 1
÷2 6 0
÷2 3 0
÷2 1 1
↑最上位ビット
この計算より最初のあまりの数値が最下位ビットで、最後の数値1が最上位ビットになります。
110010101101
が答えです。

また、小数点の数値
0.625
を2進数にする場合は、小数点以下の数値を２倍し、その小数点以上が１になったら１をとりだす。
０の時は０とする。小数点以下が０となるまで繰り返す。

0.625  →0.
0.625×2=1.25 →1    1.25から1を取り出し0.25
0.25×2=0.5 →0
0.5×2=1 →1         1から1を取り出し終了
答えは
0.101となります。


戻る
住所から郵便番号を返す
郵便番号変換ウィザードを利用するには、Web からデータをダウンロードする必要があります。詳細については、ダウンロードセンターの「Excel アドイン: 郵便番号変換ウィザード (2000-2007 ユーザー用)」のページを参照してください。
ページはこちら
http://www.microsoft.com/downloads/details.aspx?FamilyID=6f6af8ef-b9dd-4e21-9e63-af4a0ff4e7ce&DisplayLang=ja
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
ダウンロード先は、こちら
http://www.microsoft.com/downloads/info.aspx?na=90&p=&SrcDisplayLang=ja&SrcCategoryId=&SrcFamilyId=6f6af8ef-b9dd-4e21-9e63-af4a0ff4e7ce&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2fc%2f2%2fc%2fc2c6bf2f-f276-440f-84f9-e6703bb27ccb%2fzipcode7.exe
ダウンロードしたファイルを実行後、「ツール」「アドイン」を選択し、「郵便番号変換ウィザード」を選択し、「ＯＫ」ボタンを押します。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

郵便番号データから住所データを生成する

あらかじめシート上に作成した郵便番号データを基に、住所データを生成します。
複数セル範囲の郵便番号から住所を一括生成することが可能です。
[ツール] メニューの [ウィザード] をポイントし、[郵便番号変換] をクリックします。
[郵便番号から住所を生成する] をクリックし、[次へ] をクリックします。
郵便番号データのセル範囲と、住所を出力するセル範囲を指定し、[次へ] をクリックします。
エラーが出た場合の出力方法を指定し、[完了] をクリックします。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

住所データから郵便番号データを生成する

あらかじめシート上に作成した住所データを基に、郵便番号データを生成します。
複数セル範囲の住所から郵便番号を一括生成することが可能です。
[ツール] メニューの [ウィザード] をポイントし、[郵便番号変換] をクリックします。
[住所から郵便番号を生成する] をクリックし、[次へ] をクリックします。
住所データのセル範囲と、住所を出力するセル範囲を指定します。
郵便番号の種類と書式を指定し、[次へ] をクリックします。
エラーが出た場合の出力方法を指定し、[完了] をクリックします。



戻る
ファイル「zaico1」 「zaico2」を比較して、片方にしか無いものを表示する
ファイル「zaico1」
 
A B C D
1 作番 品名 数量
2 T754739 2SA1695 1000 TRUE
3 D219883 EU 2YXVE1 2000 FALSE
4 D220191 EG01CV1E 2000 FALSE
5 D220205 EU 2ZV1E 2000 FALSE
6 D220231 RU 3AMV1H 2000 FALSE
7 D224286 RU 2AMV1H 2000 FALSE
8 D234608 RU 3AMV1H 2000 FALSE
9 D234618 RU 2V1H 2000 FALSE
セルD2には次の式を入力します。
=IF(A2="","",ISNA(VLOOKUP(A2,[zaico2.xls]Sheet1!$A$2:$A$8,1,FALSE)))
ファイル「zaico2」のシートSheet1にセルA2と同じデータがあるか検索し、無ければTRUEを返し、あればFALSEを返しま す。
この式をD列の下のセルに連続コピーします。

同じデータがないセル範囲に自動的に色をつける場合は、色を付けたいセル範囲を選択し、「書式」「条件付き書式」で、「条件付き書式の 設定」ダイアログボックスから、条件1(1)に「数式が」
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)
「=$D2=TRUE」と入力し、「書式」ボタンを押し、「パターン」タグを選択して、「色」を 指定し「ＯＫ」「ＯＫ」ボタンを押します。

同様に
ファイル「zaico2」 

A B C D
1 作番 品名 数量
2 D219883 EU 2YXVE1 2000 FALSE
3 D220191 EG01CV1E 2000 FALSE
4 D220205 EU 2ZV1E 2000 FALSE
5 D220231 RU 3AMV1H 2000 FALSE
6 D224286 RU 2AMV1H 2000 FALSE
7 D234608 RU 3AMV1H 2000 FALSE
8 D234618 RU 2V1H 2000 FALSE
セルD2には次の式を入力します。
=IF(A2="","",ISNA(VLOOKUP(A2,[zaico1.xls]Sheet1!$A$2:$A$9,1,FALSE)))
ファイル「zaico1」のシートSheet1にセルA2と同じデータがあるか検索し、無ければTRUEを返し、あればFALSEを返しま す。
この式をD列の下のセルに連続コピーします。

同じデータがないセル範囲に自動的に色をつける場合は、色を付けたいセル範囲を選択し、「書式」「条件付き書式」で、「条件付き書式の 設定」ダイアログボックスから、条件1(1)に「数式が」
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)
「=$D2=TRUE」と入力し、「書式」ボタンを押し、「パターン」タグを選択して、「色」を 指定し「ＯＫ」「ＯＫ」ボタンを押します。

それぞれのファイルを１つだけ開くと「開いているブックには、他のファイルへの自動リンクが．．．．」というメッセージが表示されま す。
通常は「はい」ボタンを押してください。


戻る
シートのB列最後の数値を返して、複数シートを 合計したい
「配列数式」とINDIRECT関数を使います。

各シート(Sheet1からSheet3）のＢ列のセルの最後のデータを返すために、各シートの任意のセルに、次の式を入力し、
=INDIRECT("B"&MAX(IF(B2:B1000<>"",ROW(B2:B1000))))
{Ctrl}+{Shift}+{Enter}で「配列数式」として確定します。
配列数式として確定すると、数式は、{   }でくくられます。
{   }は、手で入力してはいけません。
配列数式では、対象のセル範囲のデータを配列として返します。
この式の意味
B列(この場合B2:B1000)のデータが空白でないセルの、最大行番号を返し、INDIRECT関数でB列の参照式を作成し、そのセルの値を返しま す。
例えば、各シートのセルC2にこの式を入力し、{Shift}+{Ctrl}+{Enter}で確定します。

複数シート（Sheet1からSheet3）の合計
Sheet4の合計したいセルには、
=sum(
と入力し、合計対象のシート１を選択し、{Shift}キーを押しながらシート３を選択し、セルC2を選択します。
数式は、=SUM(Sheet1:Sheet3!C2)
と入力されます。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
INDIRECT関数は、数式中に入力した数値や文字を参照式の一部として使えるようにします。

「質問と回答」
2002/9/29
セルのCと2の文字列の示すセルC2の内容を参照したい。
あるいは、「関数の使い方」の「検索・行列」
INDIRECT関数
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を参照してください。


戻る
シート１の任意のセルにシート２やシート３ のシート名を表示する
選択されたシート名を表示するには、ご記入のように次の式でつかえます。
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)
これを、任意のシートのシート名を表示するには$A$1の部分を他のシートへの参照式にします。
$A$1の部分を選択し、表示したいシートタグを選択し、A1セルを選択します。
Sheet0を選択した場合の式は次のようになります。
=MID(CELL("filename",Sheet0!A1),FIND("]",CELL("filename",Sheet0!A1))+1,31)
この式をコピーして同様に、他のシートを選択すれば任意のシート上のセルに他のシートのシート名が表示できます。

戻る
計算式を参照先に対して列行を入 れ替えて連続コピーしても使えるようにする
表-1                                                    表-2
 
B C D E F G H I J K L M N O P Q R
5 テレビ 冷蔵庫 洗濯機 1月 2月 3月 4月 5月 6月 7月 8月 9月
6 1月 0 テレビ
7 2月 0 冷蔵庫
7 3月 0 洗濯機
8 4月 0
9 5月 0
10 6月 0
11 7月 0
12 8月 0
13 9月 0
14 10月 0
15 11月 0
16 12月 0
17
18 0 0 0 0
INDIRECT関数を使います。
INDIRECT関数は、数式中に入力した数値や文字を参照式の一部として使えるようにします。

「質問と回答」
http://kiyopon.sakura.ne.jp/situmon/index.htm
2002/9/29
セルのCと2の文字列の示すセルC2の内容を参照したい。
あるいは、「関数の使い方」の「検索・行列」
INDIRECT関数
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect
を参照してください。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
具体的には、
セルJ6
=INDIRECT("R"&COLUMN()-4&"C"&ROW()-3,FALSE)

INDIRECT関数でセルの列を、行番号-3で、行を列番号-4で、計算させます。
セルJ6の参照先のセルがC6です。
C6はR1C1形式ではR6C3です。
この数値の入力セルJ6は列番号では10、行番号6です。
COLUMN()関数で列番号10が帰りますので、4を引いて6とします。
ROW()関数で行番号6が帰りますので、3を引いて3とします。
INDIRECT関数で"R"と行番号6"C"と列番号組3み合わせて参照式とします。
この式を右や下のセルにコピーすれば、計算式の行番号と列番号が、それぞれ列と行にに対して増加したことになります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
見本のファイルを添付します。
situmongyoretu.xls   23kB


戻る
複数の表を１つの表にまとめて合計を出す
「データ」「統合」という機能を使います。
この場合、それぞれの表は、同じ形式でデータが入力されていることが必要です。
それぞれの数値データをまとめて、別の表を作ることができます。
例えばSheet2とSheet3の表をSheet1にまとめる場合。

Sheet1
A B C D
1 品目 単価 数量 合計額
2 Ａ 1,000
3 Ｂ 1,200
4 Ｃ 1,400
5 Ｄ 1,600
6 Ｅ 1,800
7 Ｆ 2,000

Sheet2

 
A B C D
1 品目 単価 数量 合計額
2 Ａ 1,000 1,077 1,077,000
3 Ｂ 1,200 1,220 1,464,000
4 Ｃ 1,400 1,078 1,509,200
5 Ｄ 1,600 757 1,211,200
6 Ｅ 1,800 1,609 2,896,200
7 Ｆ 2,000 686 1,372,000
Sheet3
A B C D
1 品目 単価 数量 合計額
2 Ａ 1,000 525 525,000
3 Ｂ 1,200 622 746,400
4 Ｃ 1,400 533 746,200
5 Ｄ 1,600 407 651,200
6 Ｅ 1,800 889 1,600,200
7 Ｆ 2,000 237 474,000
手順
Sheet1のデータを入れたいセルの左上端セル、C2を選択し、「データ」「統合」を選択します。
「統合の設定」ダイアログボックスで、集計の方法を「合計」とし、「統合元範囲」をクリックし、シートSheet2を選択し、集計したいデー タのセル範囲C2:D7を選択します。
「追加」ボタンを選択し、同様に「統合元範囲」をクリックし、シートSheet3を選択し、集計したいデータのセル範囲C2:C7を選択しま す。
最後に「ＯＫ」ボタンを押します。

シートSheet1には、それぞれのデータ範囲の合計値が入力されます。
Sheet1
A B C D
1 品目 単価 数量 合計額
2 Ａ 1,000 1,602 1,602,000
3 Ｂ 1,200 1,842 2,210,400
4 Ｃ 1,400 1,611 2,255,400
5 Ｄ 1,600 1,164 1,862,400
6 Ｅ 1,800 2,498 4,496,400
7 Ｆ 2,000 923 1,846,000


戻る
入社年月日を使用して、現在勤務何年何ヶ月何日 なのかを表示
同じような質問が「質問と回答」
2002/9/2
生年月日を入力した時、自動的に現在、「何歳何ヶ月」か表示する
にあります。
この生年月日を入社年月日に置き換えて考えればいいでしょう。

A1セルに入社年月日が1975/4/1のように入力されているとします。

現在が2003/3/7の場合

何年と何ヶ月何日を表示したいセルに

=DATEDIF(A1,TODAY(),"y")&"年"&DATEDIF(A1,TODAY(),"YM") &"ヶ月"&DATEDIF(A1,TODAY(),"YD")&"日"
と入力します。
＆（アンパサンド）は文字列結合関数です。この場合○年と△ヶ月□日を結合します。

27年11ヶ月341日

が帰るはずです。

この場合は文字列を返しますので、数としての計算はできません。

数値とするために日付シリアル値を返す計算式を示します。
=NOW()-A1
セルの書式設定で、「ユーザー定義」とし、「種類」にY"年"m"ヶ月"d"日"とに入力します。

DATEDIF関数の説明
http://kiyopon.sakura.ne.jp/kansuu/date.html#dateif


戻る
シート{1}から{31}のシー ト内容を３２枚目のシートに自動で転記する
３２枚目のシートのセルに参照式を入力することで対応できます。
具体的には
シート{1}{2}{3}．．．．．{31}{0}
とあり、{0}のシートに毎日の記録の総日記を転記できるようにする場合、シート{0}の対象のセルに次のように式を入力します。
A列には日付を入力しておきます。
B列以降に内容を転記させます。
 
A B
1 日付 内容1
2 1 =IF(1!B2="","",1!B2)
3 2 =IF(2!B2="","",2!B2)
セルB2に
=IF(1!B2="","",1!B2)
と入力します。
このような式を、セルB4以降にも入力します。
1日目のシートのセルB2のデータが転記されます。
同様にセルB3には2日目のシートのセルB2のデータを転記するように数式を入力します。
この式は、IF関数によりデータが何も入力されていない場合は、何も表示しません。
IF関数がないと0を表示します。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
数式をいちいち入力するのが面倒な場合は、次のように数式の中のシート名をセルに１つ入力しておき、それを参照式の中に取り込んで式を作成す ることもできます。
A列にはシート名を入力しておきます。
 
A B
1 日付 内容1
2 1 =INDIRECT($A2&"!B2",TRUE)
3 2 =INDIRECT($A2&"!B2",TRUE)
セルB2に
=INDIRECT($A2&"!B2",TRUE)
と入力します。
この計算式を、B3以降の行または列にコピーします。
式は、１日を１行として連続コピーにより簡単に入力できます。
INDIRECT関数の使い方は
http://kiyopon.sakura.ne.jp/kansuu/address.html#indirect

戻る
"00000001"の"0"をとって、全角の"１"としたい
VALUE関数で、文字列を数値に変換し、JIS関数で半角の英数を全角にします。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
F列に先頭の0を省いた全角数値を返します。
セルF1に次の式を入力します。
=JIS(VALUE(A1))
セルF1を下のセルにドラッグして連続コピーします。
 
A B C D E F
1 00000001 =JIS(VALUE(A1))
2 00123456 １２３４５６
3 00012345 １２３４５
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
この計算結果を元の値と置き換えるには、セル範囲F列を選択し、「編集」「コピー」し、元の値のセルA1を選択し、「編集」「形式を選択して貼り付け」で 「値」として「ＯＫ」ボタンを押します。
計算に使った、F列は必要ないので、削除します。

戻る
１００以上１１５未満というIF関数の使い方
IF関数で○以上△未満という複数の条件として使うにはAND関数を併用します。

G6セルの値が100以上200未満の場合は
AND(100<=G6,G6<200)のように二つの条件を,で区切ってください。
具体的には次のようになります。
=IF(G6<100,"やせすぎ",IF(AND(100<=G6,G6<115),"やせている",IF(AND (115<=G6,G6<145),"ふつう",IF(AND(145<=G6,G6<160),"太っている",IF (G6>160,"ふとりすぎ","")))))


戻る
時間の計算方法
「エクセルの操作説明」の「ワークシート関数」
http://kiyopon.sakura.ne.jp/sousa/index.htm
の「ワークシート関数」の「日付と時刻の入力」「日付や時刻の計算」
を参照してください。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
時間は、そのままで通常の四則演算が出来ます。

具体的には、
A1セルに日付と時刻（開始）
2003/2/19 2:20
A2セルに日付と時刻（終了）
2003/2/20 3:30
B1セルに２つの時刻の差を求める式
=A2-A1
を入力します。
B1セルの表示を正しく25:10と時刻とするためにB1セルを選択し、
「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスから「表示
形式」タグの「分類」で「ユーザー定義」を選択し、[h]:mmと入力し、「ＯＫ」
ボタンを押します。
通常の時刻の表示では２４時間以上をゼロクリアして1:50と表示されてしまいま
す。
h:mmを[h]:mとすることで24時間以上の時間を表示できます。

 
A B
1 2003/2/19 2:20 25:10
2 2003/2/20 3:30 =MAX(A1,A2)-MIN(A1,A2)
B1セルの数式を
=MAX(A1,A2)-MIN(A1,A2)
とすれば、日付が前後しても正しい答えが出ます。

また、マイナスの時刻は、######となりますが、正しく表示する方法はこちらを参照してください。
 http: //www.katch.ne.jp/~kiyopon/situmon/kaitou/index07.htm#マイナスの時刻の表示方法は、？

エクセルでは、１日は1となるように日付シリアル値として数値で計算しています。
１日が1で２４時間が１です。
1時間は２４時間で割ると、1/24となります。
さらに60分で割り1/24/60となります。
これが1分の時刻シリアル値となります。

つまり、1時間は1÷24で、１５分は1÷24×15÷60です。

時刻を10進数で表す 8:30→8.5


戻る
可変範囲の該当セルのデータがすべて入力さ れたら列色を赤にする
計算用に列を１つ追加します。
下の場合はB列
セルB1に
=IF(A1<>"",ROW(),"")
を入力します。
ROW関数でA列にデータの入力されたセルの行番号を返しますが、データがない場合は番号を返しません。
この数式をコピーし必要と思われる行まで貼り付けます。
（たとえばB2からB100まで）

セルC1に
=MAX(B:B)-COUNT(INDIRECT("B1:B"&MAX(B:B)))
を入力します。
INDIRECT関数でセル中の数値や文字から参照式を作成します。
この場合は、B列の行番号の最大値をMAX関数で返した値(この場合は6)を"B1:B"に&(文字列演算子)でつなげています。
この関数では、B1:B6が帰ります。
COUNT関数でセル範囲[INDIRECT("B1:B"&MAX(B:B))]の返値(この場合B1:B6)の中で数値入ってい るセル数を返します。
この場合は5が帰ります。

具体的には

A列の該当範囲にデータがすべて入力されたらＡ列に色を塗ります。
 
A B C
1 10 1 1
2 2月1日 2
3 10 3
4
5 30 5
6 2月5日 6
セルB1に
=IF(A1<>"",ROW(),"")
セルC1に
=MAX(B:B)-COUNT(INDIRECT("B1:B"&MAX(B:B)))
と入力します。
列Aの色つけする指定範囲を選択し、「書式」「条件付き書式」を選択します。
条件1を「数式が」
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)
「=C1=0」と入力し、「書式」「パターン」から「色」の「赤」を選択し「ＯＫ」「ＯＫ」ボタンを押します。

戻る
RIGHT関数で１の位の数値を返す
RIGHT関数の使い方は、「関数の使い方」の「文字列関数」にもありますが
（文字列の右端から指定した数の文字を取り出し）
書式：=RIGHT(文字列,文字数)
文字列
取り出す対象となる文字列を指定
文字数
取り出す文字数を指定
です。
 
A B
1 1234567 =RIGHT(A1,1)
文字列"7"が返ります。

この関数で、変えるのは数値も「文字列」になりますから注意してください。

文字列を数値に直すには、VALUE関数を使います。
VALUE関数は
(文字列として入力されている数字（ダブルクォーテーションで囲まれた数字）を
数値に変換します）
書式：=VALUE(文字列)
です。

 
A B
1 1234567 =VALUE(RIGHT(A1,1))
数値 7 が返ります。

戻る
許可年月から３回目の誕生日を返す
 
A B C
1 誕生日 許可年月日 有効期限
2 1980/1/1 2002/12/2 平成17年1月1日
セルC2には、数式を次のように入力します。
=DATEVALUE(YEAR(A2)+INT(YEARFRAC(A2,B2,1))+3&"/"&MONTH(A2)&"/"&DAY(A2))
数式の意味
誕生日の年をYEAR(A2)で返し、この場合1980
それに誕生日から許可年月日までの年数をYEARFRAC(A2,B2,1)で求め、INT関数で小数点以下を切り捨て、整数のみ返します。
この場合23年
この数値にさらに3(年)を加算します。
MONTH(A2)で誕生日の月をかえします。
DAY(A2)で誕生日の日を返します。
文字列結合演算子&（アンパサンド）で"誕生日の年+から許可日までの年数+3年/誕生月/誕生日"の日付文字列にします。
DATEVALUE関数で(年/月/日）日付文字列を日付シリアル値になおします。
この場合は、日付シリアル値で38353となります。
「書式」「セル」で「セルの書式設定」から、「表示形式」を日付「平成9年3月4日」を選択し「ＯＫ」ボタンを押します。

この関数を使用するには、[分析ツール] アドインをインストールする必要があります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

[ツール] メニューに [分析ツール] コマンドが表示されない場合は、、Excel に分析ツール アドインを組み込む必要があります。

分析ツールを組み込むには

1[ツール] メニューの [アドイン] をクリックします。
分析ツールが [アドイン] ダイアログ ボックスの [アドイン] ボックスに表示されない場合は、[参照] をクリックし、分析ツール アドインが保存されているドライブとフォルダ、およびアドインのファイル名 (Analys32.xll) を指定します。
Analys32.xll は、通常、Office または Excel をセットアップしたフォルダの\Library\Analysis にあります。
分析ツール アドインが組み込まれていない場合は、Excel のセットアップ プログラムをもう一度実行します。

2[分析ツール] チェック ボックスをオンにします。


戻る
売り上げ順位の多い順に店名を返す
 
A B C D E F G
1 店名 売上金額 順位 順位 店名
2 2 北海道 356.5 2 1 関東
3 3 東北 329.1 3 2 北海道
4 4 北陸 301.5 4 3 東北
5 1 関東 388.3 1 4 北陸
6 5 東海 301.5 4 5 東海
7 6 大阪 289.1 6 6 大阪
8 7 中国 279.9 7 7 中国
セルD2に順位を計算させる式を入力します。
=RANK(C2,$C$2:$C$8)
この式をD8のセルまで連続コピーします。

セルA2に重複しない順位を取り出すために、同じ順位の場合は下の行にあるものを低い順位になるように求めています。
=D2+COUNTIF(D$1:D1,D2)
この式をA8のセルまで連続コピーします。

セルF2には順位を１から順に取り出すため、セル範囲A2:A8の数値を行番号と比較し、D2:D8の該当データを返します。
=VLOOKUP(ROW(A1),$A$2:$D$8,4,FALSE)
この式をF8のセルまで連続コピーします。
貼り付けにより、ROW(A1)はROW(A2),ROW(A3)と変化し、順位番号を自動で増やします。

セルG2には順位を１から順に取り出すため、セル範囲A2:A8の数値を行番号と比較し、B2:B8の該当データを返します。
=VLOOKUP(ROW(A1),$A$2:$D$8,2,FALSE)
この式をG8のセルまで連続コピーします。
貼り付けにより、ROW(A1)はROW(A2),ROW(A3)と変化し、順位番号を自動で増やします。



戻る
複数のセルに入力された重複しないデータの数を数える
COUNTIF関数(セル範囲の同じデータの数を返す)を使って解決できます。

具体的には
セルA1からA11にデータが入力されています。

1をCOUNTIF関数で求めた重複データ数で割って逆数とすることで対応します。
 
A B
1 １ 1 =1/(COUNTIF($A$1:$A$11,A1))
2 ２ 0.5
3 ２ 0.5
4 ３ 0.333333
5 ３ 0.333333
6 ３ 0.333333
7 ４ 0.5
8 ４ 0.5
9 ５ 1
10 ６ 0.5
11 ６ 0.5
12
13 6 =SUM(B1:B11)
セルB1からB11に重複したデータがある場合に、1を重複したデータの総数で割って、逆数を返す式
=1/(COUNTIF($A$1:$A$11,A1))
を入力します。

セルB13に重複しないデータの数を返す式
=SUM(B1:B11)
を入力します。

「エクセルで使えるソフト」
http://kiyopon.sakura.ne.jp/soft/index.htm
に私の作成した「重複しないセル数」ユーザー定義関数
があります。
これを使えば、選択セル範囲のデータが重複していないセル数を返します。
通常の組み込み関数と同じようにワークシート上で使用できる関数です。

説明はこちら
http://kiyopon.sakura.ne.jp/soft/juhukunai.html
 


戻る
空白、文字列、0（数値）を除いた平均値を出したい
平均値の関数で、AVERAGE関数では、文字列や、論理値、空白が含まれていても無視されます。
しかし、0は計算の対象になります。この０を計算から外すためには、if関数を配列数式で入力します。
具体的には
 
A
1 10
2 20
3 30
4
5 0
6 f
7 60
8 0
9 30
セルA9に平均値を返す場合は、次のように入力し、
=AVERAGE(IF(A1:A8<>0,A1:A8))
配列数式として、{Shift}+{Ctrl}+{Enter}で確定します。
確定後、数式は{  }でくくられます。
{=AVERAGE(IF(A1:A8<>0,A1:A8))}
{  }を自分で入力してはいけません。
この数式の意味
セル範囲のA1:A8が、0以外のセルに対してに、セル範囲A1:A8の値の平均値を返します。
－－－－－－－－－－－－－－－－－－
または、
空白、文字列、0（数値）を除いた
合計値を返す場合は、同様に
=SUM(IF(A1:A8<>0,A1:A8)
と入力し、配列数式として、{Shift}+{Ctrl}+{Enter}で確定します。
－－－－－－－－－－－－－－－－－－
空白、文字列、0（数値）を除いた
セル数を返す場合は、同様に
=COUNT(IF(A1:A8<>0,A1:A8))
と入力し、配列数式として、{Shift}+{Ctrl}+{Enter}で確定します。

戻る
現在２００,０００円の基本給に４月に５％の昇 給がある場合
計算式をIF関数と日付で設定します。
具体的には
=if(now()<"2003/4/1",20000,20000*1.2)
あるいは、基本給がセルA1に入力されている場合は
=if(now()<"2003/4/1",A1,A1*1.2)
のようにします。
これで、日付が2003/4/1より小さい場合は20000を返し、それ以外は20000の120%を返します。

戻る
複数の変数に対するデータ予測
「線形近似」
近似直線の予測値を求めるにはTREND関数を使います。
具体的には
セル範囲B3:E7に既知のXが4つの変数として入力されています。
セル範囲F3:F7までが既知のYとして入力されています。
新しいXとして、セル範囲B8:E10に4つの変数が入力されています。
 
A B C D E F
1 変数1 変数2 変数3 変数4
2 床面積 駅距離 築年数 階 価格
3 1 120 15 10 5  9000
4 2 60 10 5 6 3600
5 3 75 5 6 7 5500
6 4 70 7 2 5 5300
7 5 80 9 4 6 6300
8 6 90 10 1 8 9123.809524
9 7 85 3 4 3 5780.952381
10 8 75 7 8 4 3790.47619
結果を入力するセル範囲F8:F10を選択し、「関数の挿入」ボタンを押し「関数の挿入」ダイアログボックスを表示して、「統計」を選 択し、「TREND」を選択して、「ＯＫ」ボタンを押します。
「既知のy」にセル範囲F3:F7を選択入力し、「既知のx」にセル範囲B3:E7を選択入力します。
「新しいx」にセル範囲B8:E10を選択入力します。
一度にセル範囲に数式を入力するため「配列数式」として{Shift}+{Ctrl}ボタンを押しながら{Enter}ボタンを押します。
セルF8には
{=TREND(F3:F7,B3:E7,B8:E10)}
が入力されます。
配列数式として入力されると数式は{   }でくくられます。
{  } を手で入力してはいけません。
配列数式として入力すれば、数式は、一度に複数のセルに入力できます。
配列数式で一度に入力したセル範囲は、数式を変更する場合も一度に同じセル範囲を選択して修正、削除しないといけません。
配列数式としてでなく、そのままセルF8に
=TREND($F$3:$F$7,$B$3:$E$7,$B8:$E8)
を入力してもかまいません。
その場合は数式を絶対参照と相対参照、複合参照になるようにします。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
「指数近似」
指数近似からの予測値の算出にはGROWTH関数を使います。
この関数もTREND関数と同様に
既知のx
既知のy
新しいx
を入力します。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
「線形近似か指数近似か」
独立変数にその重要度があまり変わらない場合は問題ありませんが、予測においてはどのようになるのか理論的にいずれかを論理的に選択しておく 方がよいでしょう。
わからなければ、直線近似と、指数近似のそれぞれで予測したデータを実際の値と並べて折れ線グラフにするとよくわかります。

戻る
行を削除した場合に参照計算式がエラーとな らず計算できるように
その行の「行番号」を返す計算式ROW関数を返すようにします。
=row()
と入力すれば、行1の場合1が帰ります。
他のシートへの参照式を入力する場合に参照元の行削除で、参照先の計算式がエラーとならないようにするにはINDIRECT関数で、シート名 と列番号とrow関数による行番号とを組み合わせて参照式を入力します。
行番号1のセルに
=INDIRECT("Sheet1!A"&ROW(),TRUE)
とすれば、Sheet1のセルA1を返します。

次の式のように
=INDIRECT("Sheet1!A"&ROW()+1,TRUE)
行番号に+1とすれば、
Sheet1のA2セルを参照します。

また、列番号も指定したい場合は、
=INDIRECT("Sheet1!R"&ROW()&"C"&COLUMN(),FALSE)
とすれば列と行を指定できます。
INDIRECT関数については「関数の使い方」
http://kiyopon.sakura.ne.jp/kansuu/index.htm の「検索+行列」のINDIRECTを参照してください。


戻る
入力数値12.6を12年6月度と表示する
入力セルの数値をそのセルの位置で、そのまま12年6月度と表示することはできません。
違うセルに計算式を入力して、表示することになります。
セルA1に12.6と数値として入力されている場合セルB1に
=LEFT(A1,FIND(".",A1)-1)&"年"&RIGHT(A1,LEN(A1)-FIND(".", A1))&"月度"
と計算式を入力します。
これで"12年6月度"と文字列で表示されます。

データを文字列ではなく日付のシリアル値として計算や並べ替えなどにも使いたい場合は
=DATEVALUE(LEFT(A1,FIND(".",A1)-1)&"/"&RIGHT(A1,LEN(A1)-FIND(".",A1))&"/1")
として、日付シリアル値に変換し、「書式」「セル」で「セルの書式設定」ダイアログボックスの「表示形式」の「分類」を「ユーザー定義」と し、「種類」に「e"年"m"月度"」と入力し、「ＯＫ」ボタンを押します。


戻る
today関数の入力後日付が変わっても日を そのままにしたい
マクロを使う必要があります。
下のマクロコードでは列番号1 (Ａ列)かつ、行番号が２から３０までのセルデータを入力（変更）した時にC列にその日の日付が入力されます。
入力された日付は、値として入力されますので、日付が変わってもそのままです。
入力データが無い場合（削除された場合）は日付は消えます。

マクロの修正方法

「入力セル」の列をＡ列以外のセルに使う場合は
If Tc = 1 Then
1を変更します。
数値は列番号を表します。
1はA列,2はB列,3はC列,4はD列となります。

「日付入力」する列をC列以外の列にする場合は
C=3
の部分で、3の数値を変更します。
数値は列番号を表します。
1はA列,2はB列,3はC列,4はD列となります。

「行番号」の設定は
If Tr >= 2 And Tr <= 30 Then
の部分で、2と30の数値を変更します。

下のマクロコードをコピーし、データの入力をするシートをマウスの右ボタンでクリックしてショートカットキー「コードの表示」を選択し ます。
Microsoft Visuual Basicが開きますので、右の「コードウィンドウ」マクロコードを貼り付けします。
エクセルのシートに戻るには「ファイル」「終了してMicrosoft Excelへ戻る」を選択します。
－－－－－－－－－－－－－－－－－－－－－－－－
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim c As Integer, Tc As Integer, Tr As Integer
    c = 3
    Tc = Target.Column
    Tr = Target.Row
    If Tc = 1 Then
        If Tr >= 2 And Tr <= 30 Then
            If Cells(Tr, Tc) <> "" Then
                Cells(Tr, c).Value = Now
                Else
                Cells(Tr, c).Value = ""
            End If
        End If
    End If
End Sub
－－－－－－－－－－－－－－－－－－－－－－－

戻る
誕生日から小学、中学、高校までの学年を表示する

誕生日から小学、中学、高校までの学年を表示する式
A1セルに日付が1997/5/6のように入力されている場合

=IF(OR(YEAR(NOW())-YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<401,1,0)<7,YEAR(NOW())-YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,1,0)>18),"",IF(YEAR(NOW())-YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,1,0)>15,"
高校"&YEAR(NOW())-YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<= 401,1,0)-15,IF(YEAR(NOW())-YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<=401,1,0)>12,"
中学"&YEAR(NOW())-YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<= 401,1,0)-12,"小学"&YEAR(NOW())-YEAR(A1)+IF(VALUE(TEXT(A1,"mmdd"))<= 401,1,0)-6))&"
年生")
となります。
長い式ですが、全ての式は一行です。



戻る
「東京都千代田区神田2 -3-6」を「東京都千代田区神田2丁目3-6番地」に変換
入力データとは、別のセルに変換後の住所を表示するようにした方が無難です。
また、次の方法で１番目の「-」を「丁目」にして、数値の最後に「番他」を付けます。
A1セルに住所のデータ「東京都千代田区神田2-3-6 」が入力されているとします。
セルB1に次の式を入力します。
=REPLACE(A1,FIND("-",A1,1),1,"丁目")&IF(ISNUMBER(VALUE(RIGHT (A1,1))),"番地","")
東京都千代田区神田2丁目3-6番地
が返ります。

戻る
セル内改行の文字を複数セルに分割する
Excelの機能に「区切り位置」があります。
それを使用してセル内改行の文字{Ctrl}+Jで区切るようにします。
具体的には
セルA1セルにセル内改行された文字列が入力されているとします。

セルA1を選択し、メニュー「データ」「区切り位置」を選択します。
表示されたダイアログで「カンマやタブなどの…」の方を選び、「次へ」、区切り文字の指定で「その他」にチェックし、右のボックスに
[Ctrl]キーを押しながら「J」（なにも表示されません）を入力、「次へ」、区切ったあとのデータ形式を選択して「完了」を押します。
セルA1からB1,C1と次の列に改行位置で文字列が分割されます。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
元の入力データを残しておきたい場合は、セルA1のデータをコピーして違うセルに貼り付けた後、同様にセルを選択して「データ」「区切り位 置」「その他」{Ctrl}+J
を実行してください。


戻る
参照先のフォルダを変更した場合に「ファイ ルが見つかりません」
参照先フォルダを変更した場合には、メッセージとしての「ファイルが見つかりません」がでます。
これは、「エラー」ではなく「参照先のファイルを選択しなおしてください」ということですから、このメッセージがでた場合に、「キャンセル」 を押します。
次に「編集」「リンクの設定」を選択し、「リンク元の変更」ボタンを押し、参照先のファイルを選択し直して、「ＯＫ」ボタンを押せば、以降は すべて正常に
機能するようになります。

戻る
８時間を１日として休日日数の計算をしたい
１０進数を８進数に変換する関数
DEC2OCTを使います。
具体的には、
        A       B
1       時間    休日数
2       8       3.3
3       8
4       3
5       3
6       5

=DEC2OCT(SUM(A2:A6))/10

関数の意味
SUM(A2:A6)/10で、セルA2からA6の合計を出し、１０で割ります。
DEC2OCT関数で、１０進数の値を８進数に変換します。

DEC2OCT関数の説明
10進数を8進数に変換します。
この関数を使うには、セットアッププログラムを実行して分析ツールを組み込み、[ツール]メニューの[アドイン]コマンドを使ってその分析 ツールを登録する必
要があります。
分析ツールを組み込むには
1　[ツール]メニューの[アドイン]をクリックします。
　分析ツールが[アドイン]ダイアログボックスの[アドイン]ボックスに表示されない場合は、[参照]をクリックし、分析ツールアドインが保 存されているドライ
ブとフォルダ、およびアドインのファイル名(Analys32.xll)を指定します。Analys32.xllは、通常、Officeまた はExcelをセットアップしたフォルダの\Library\Analysisにあります。分析ツールアドインが組み込まれていない場合は、Excelの セットアッププログラムをもう一度実行します。
2　[分析ツール]チェックボックスをオンにします。

書式

DEC2OCT(数値,桁数)

数値変換する10進の整数を指定します。数値に負の数を指定すると、桁数は無視され、10桁(30ビット)の8進数が返されます。8進 数の最上位のビットは符号を表し、残りの29ビットは数値の大きさを表します。負の数は2の補数を使って表します。

桁数8進表記するときに使用する文字数(桁数)を指定します。桁数を省略すると、必要最小限の桁数で結果が返されます。桁数を指定する ことによって、変換結果の先頭に0(ゼロ)を表示することができます。

時刻を10進数で表す 8:30→8.5


戻る
該当列の最終データを返す
下の場合は、セルE2に最終残高の80を返すということですね。
下の例では1,2,3,4,5,6,7,8およびA,B,C,Dは、行列番号を示します。
 
A B C D E
1 日付 収入 支出 残高 最終残高
2 4月1日 100 100 80
3 4月2日 50 50
4 4月3日 30 80
5
6
7
8
「配列数式とセル参照の関数を使ってセルの値を返す」
この場合は、セル範囲で有効な数値の入ったセルの、行番号の最大のセル値を返すように関数を作成します。
この場合、関数を配列数式として、関数を入力します。

関数の中でMAXが使われていますが、ここでは、セルに入力されている数値の最大を返すのではなく、セルの列番号の最大値を返すための 使用しています。
セルE2に結果を返す場合
=INDIRECT("R"&MAX(IF(ISNUMBER(D2:D6),ROW(D2:D6)))&"C"&COLUMN(D2:D6),FALSE)
と入力し
{Ctrl}+{Shift}+{Enter}で確定し配列数式とします。
確定後、数式は、{   }でくくられ、配列数式となります。
手動で{  }を入力してはいけません。
80
が返ります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
この数式の意味
ISNUMBER関数で、データ範囲のD2:D6で数値として認識される場合は、ROW関数で、そのセルの行番号を返し、COLUMN関数で そのセルの列番号を返し、INDIRECT関数で、その列番号と行番号の数値から、セルへの参照を返します。

「エクセルで使えるソフト」 に
「下端データの検索」　列の一番下 端のデータを返します
「右端データの検索」　列の一番右 端のデータを返します
「直近データの検索」　列の一 番近いデータを返します
というアドインソフトもありますので、ご利用ください。


戻る
時刻を10進数で表す 8:30→8.5
簡単にできます。
エクセルでは1日(24時間)を1として、シリアル値で表しています。
したがって時刻も同様に1時間は1/24です。
このことから、時刻8:30は8.5/24です。
時刻表示を24倍すれば、１０進数表示になります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
具体的には、セルA1に時刻8:30が入力されていると、
=A1*24
で8.5が帰ります。
また、
セルA1に18:30
セルA2に9:45
が入力されている場合
=(A1-A2-"1:00")*24
で、7.75が帰ります。

戻る
労働時間×時給単価で賃金計算
 
A B C D E F
1 日 出社時刻 退社時刻 勤務時間 時給 賃金
2 1 8:50 17:40 8:50 850 =D2*24*E2
勤務時間に時給をかけて、給料を計算する場合、時給×時間でそのまま計算できます。

エクセルでは、１日を１としていますので、１時間は1/24です。
（１日（２４時間）以下の時間は、小数点以下で示されます）
その値を２４倍すれば１時間が１になります。
上の場合は、セルE2の時給が８５０円で、勤務時間はD2で、8:50です。
=D2*24*E2
で、給料7,508.333333円となります。
Ｆ２を選択し、「書式」「セル」セルの書式設定で、「表示書式」を「標準」にして下さい。
小数点以下を四捨五入する場合は、
=ROUND(D2*24*E2,0)
とします。

戻る
「エクセルの勉強部屋」< c4731625@yahoo.co.jp>からメールアドレスを抜き出す

文字列検索関数FINDおよび、MID関数を使います。

具体的には次のようになります。
Ａ１に文字列が入力されている場合
開始文字が"<"
終了文字が">"
その間の文字を取り出します。

=MID(A1,FIND("<",A1,1)+1,FIND(">",A1,1)-FIND("<",A1,1)-1)
これで、"<"と">"の間の文字列が返されます。
上の例では、"c4731625@yahoo.co.jp" が帰ります。
また、メールアドレスにメール送信するリンクを設定するには、
=HYPERLINK("mailto:"&MID(A1,FIND("<",A1,1)+1,FIND(">",A1,1)-FIND("<",A1,1)-1))
とします。

戻る
1年分横に火曜日と金曜日だけ並べたい

A1セルに年（例えば2002）を入力します。
A2セルに次の式を入力します。
=DATEVALUE($A$1&"/1/1")+IF(WEEKDAY(DATEVALUE($A$1&"/1/1"))=7,3,IF(WEEKDAY(DATEVALUE($A$1&"/1/1"))>3,6,IF(WEEKDAY(DATEVALUE($A$1&"/1/1")),3,0))-WEEKDAY(DATEVALUE($A$1&"/1/1")))
A2セルを選択し、「書式」「セル」で「セルの書式設定」ダイアログボックスから、「表示書式」を選択し、「日付」を選択し種類「3/4」を 選択し「ＯＫ」ボタンを押します。
A3セルに次の式を入力します。
=A2
セルA3の式をB3セルにコピーします。
セルB3:A3セルを選択し、「書式」「セル」で「セルの書式設定」ダイアログボックスから、「表示書式」を選択し、「ユーザー定義」を選択しの種類に 「(aaa)」を入力し「ＯＫ」ボタンを押します。
B2セルに次の式を入力します。
IF(YEAR(IF(WEEKDAY(A2)=3,A2+3,A2+4))>$A$1,"",IF(WEEKDAY(A2)=3,A2+3,A2+4))
セルB2:B3の数式をDA2:DA3まで連続コピーします。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
関数の意味
A2セルのDATEVALUE(A1&"/1/1")
は、その年の１月１日を計算します。
それ以降の数式
+IF(WEEKDAY(DATEVALUE(A1&"/1/1"))=7,3,IF(WEEKDAY(DATEVALUE(A1&"/1/1"))>3,6,IF(WEEKDAY(DATEVALUE(A1&"/1/1")),3,0))-WEEKDAY(DATEVALUE(A1&"/1/1")))
では曜日が火曜日か金曜日になるように日付を加減算します。
B2セルの=IF(YEAR(A2)>$A$1,"",A2)
次の年になったら表示しない設定です。

戻る
エラー表示の意味

エラーの意味は次のとおりです。
 
表示 意味
#####  セルにデータが表示しきれないという意味です。

セルに入力した数値が大きすぎて、セルに表示できません。列番号の間の境界をドラッグすると、列の大きさを変更できます。
セルの数式の計算結果が長すぎて、セルに表示できません。列番号の間の境界をドラッグするか、セルの数値書式を変更すると、列幅 を拡張することができます。
数値書式を変更するには、[書式] メニューの [セル] をクリックし、[表示形式] タブをクリックして他の書式を選択します。
日付や時刻の減算をする場合は、正しい数式を使用しているかどうかを確認します。
日付や時刻は正の値である必要があります。日付や時刻の数式の計算結果が負の値になると、セルに "####" が表示されます。
セルの値を表示するには、[書式] メニューの [セル] をクリックし、[表示形式] タブをクリックして、[日付] または [時刻] 以外の書式を選択します。
#NULL! 交差演算式に指定された２つのセル範囲に交差部分がない場合などに返されます。
#DIV/0! ０ゼロを除数に使用した。数式の中で、除数に空白のセルの参照を指定した場合などに返される。
#VALUE! 算術数式の参照先に文字列が入力されている。
#REF! 参照先のセル範囲が削除されてしまった。
#NAME? ［名前定義］ダイアログボックスの一覧にない名前を数式の中で使用した。
#NUM! ワークシート関数に不適切な引数を使用した。数式の結果が大きすぎたり、小さすぎて、ワークシートで処理できない場合な どに返される。
#N/A 計算を実行するために必要な値が入力されていない場合に返されます。モデルを作成する場合など、とりあえず#N/Aを入 力しておくと、#N/Aを参照する数式は#N/Aを返すので、データ入力の必要な数式を特定できる。
 

戻る
５０銭以下の場合は切り捨て、５１銭以上の 場合は切り上げ

      セルＡ１に数値が入力されている場合
      =ROUND(A1-0.01,0)
      とします。
      あるいは
      =INT(A1+0.49)
      とします。

      ROUND関数の説明はROUNDを参照してください。
      INT関数の使い方はINTを参照してください。

戻る
1/100秒の入力と表示について

時刻の入力と、表示の２つの列に分かれますが、これでいかがですか。
データの入力はA列で行います。
１分５秒２０の場合は、1:05.20と入力します。
５５秒５１の場合は55.51と入力します。
 
A B C
1 01:05.20 =IF(A1>=1,TIMEVALUE("00:"&A1),A1) 01:05.20
2 55.51 =IF(A2>=1,TIMEVALUE("00:"&A2),A2) 00:55.51
A列のセルには、セルの書式設定で「表示形式」を「標準」とします。
B列のセルには、セルの書式設定で「表示形式」を「ユーザー定義」の「mm:ss.00」とします。

B1セルには
=IF(A1>=1,TIMEVALUE("00:"&A1),A1)
を入力し、下のセルに連続コピーします。

時刻表示はB列になります。


戻る
番地だけ抜き出す　東京都新 宿区新宿1-1-1-101　→　1-1-1-101
入力された住所を少し細工すればできます。
住所と番地の間に半角スペース" "を入力します。
住所の番地部分を抜き出す関数を次のように入力します。
        A                            B
1 東京都新宿区新宿 1-1-1-101    =RIGHT(A1,LEN(A1)-FIND(" ",A1))

関数の意味
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
FIND　文字列に対して検索文字の位置を返します
LEN　　文字列の長さを返します
RIGHT　文字列の右から指定数の文字列を取り出 します

戻る
複数階層のドロップダウンリストによる検索

VLOOKUP関数とドロップダウンリストを組み合わせます。
１つ目のドロップダウンリストから２つ目の階層のドロップダウンリストを作成して、さらに３つ目のドロップダウンリスト を作成した見本ファイルはこちら。
説明が記入してありますから、参考にしてください。

戻る
日付と年を別々に入力したものを日付計算する

 
A B C
1 生年 月日 生年月日
2 1978 7/8 =DATEVALUE(A2&"/"&MONTH(B2)&"/"&DAY(B2))
B2に入力された月日はすでにシリアル値です。
表示は年がありませんが、月日しか表示しない状態です。
したがって、A2セルの年に月日を結合するには、B2セルのシリアル値を月日部分だけにする必要があります。

月を返すMONTH(シリアル値)
日を返すDAY(シリアル値)

C2の計算は次のようになります。
 =DATEVALUE(A2&"/"&MONTH(B2)&"/"&DAY(B2))
 

この値は、シリアル値ですから、「書式」「セル」でセルの書式設定ダイアログボックスから「表示形式」タグを選択し、「分類」を「日 付」とし、種類を「1997/3/4」などにしてください。

戻る
#VALUE!を0に置き換える

ISERROR関数とIF関数を組み合わせて使います。

次の例では、計算式の部分を計算として置き換えて表示します。
=IF(ISERROR(計算),0,計算)

ISERROR テストの対象 が任意のエラー値 (#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? または #NULL! のいずれか) を参照するとき TRUE を返します。


戻る
日付データを漢数字「平成十四年十月十日」で表示し たい
NA1セルに日付が
2002/10/10
と、入力されているとします。
A1セルを選択し、「書式」「セル」を選択し、「セルの書式設定」ダイアログボックスで「表示形式」の「分類」で「ユーザー定義」を選択し、「種類」欄に「[DbNum1]ggge年m月d日」と入力し「ＯＫ」ボタンを押します。
平成十四年十月十日
と表示されます。
「[DbNum3]ggge年m月d日」と入力すれば、
平成１４年１０月１０日
と表示されます。

戻る
日付入力の月末日を表示
二つの方法があります。
EOMONTH関数を使う
月末日を求める関数は、EOMONTHです。
セルA1に年月日が入力されているとします。
=EOMONTH(A1,0)
関数や日付の書式を「書式」「セル」から「セルの書式設定」ダイアログボックスで「表示形式」タグの「分類」から、「日付」をクリックし、 「種類」で「2001/3/14」などの日付形式を選択し「ＯＫ」ボタンを押します。

説明 A1セル（例えば2002/10/10）の日付の0月目の月末日(2002/10/31)を返します。
2ヶ月後の月末日を求める場合は
=EOMONTH(A1,2)となります。(2002/12/31)

EOMONTH
(開始日から起算して、指定した月数だけ前または後の月の最終日に対応するシリアル値を返します。)
この関数を使うには、セットアッププログラムを実行して分析ツールを組み込み、[ツール]メニューの[アドイン]コマンドを使ってその分析 ツールを登録する必要があります。

 
書式=EOMONTH(開始日,月)

EOMONTHの詳しい説明はこちら

DATE関数YEAR関数,MONTH関数を使う
その月の月末を返すには、
=DATE(YEAR(A1),MONTH(A1)+1,0)
と入力します。

2ヶ月後の月末を返すには、
=DATE(YEAR(A1),MONTH(A1)+3,0)
と入力します。

それぞれの関数の説明は、「関数の使い方説明」
http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
DATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
YEAR
http://kiyopon.sakura.ne.jp/kansuu/date.html#year
MONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#month
を参照してください。


戻る
串刺し計算の方法
複数のワークシートにまたがって計算する際は基本的に、ワークシートの違いを気にせず、参照したいシートのセルを指定す ればいいですね。
例えば、SUM関数で、Sheet2,Sheet3,Sheet4の各シートのC3セルを指定すると、数式は=SUM(Sheet2!C3, Sheet3!C3,Sheet4!C3)」となります。
また、３Ｄ参照をする場合は次のように入力します。

1       関数を入力するセルをクリックします。
2       = (等号)、関数名、左かっこの順に入力します。
3       最初に参照するワークシートのタブをクリックします。
4       Shift キーを押しながら、最後に参照するワークシートのタブをクリックします。
5       参照するセルまたはセル範囲を選択します。
6       数式の入力を終了します。

セルには、
=SUM('Sheet2:Sheet4'!C3)
のように表示されます。

あるいは、各シートの参照するデータの場所が同じ場合などは「データ」メニューの「統合」機能が使えます。



戻る
誕生日の３年１ヶ月後を表示する
３年１ヶ月ですから３７ヶ月先の日付です。
それをEDATE関数で計算してそれを日付とします。

－－－－－－－－－－－－－－－－－－－－－－－－－－－
例えばA1セルに誕生日が1975/4/6と入力されているとします。
=EDATE(A1,37)
で３年１ヶ月後が帰ります。
1978/5/6
小の月の場合はその月の月末が返ります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－
EDATE関数は
開始日から起算して、指定された月数だけ前または後の日付に対応するシリアル値を返します。この関数を使用すると、伝票の発行日と同じ日に当 たる支払日や満期日の日付を計算することができます。
この関数を使うには、セットアッププログラムを実行して分析ツールを組み込み、[ツール]メニューの[アドイン]コマンドを使ってその分析 ツールを登録する必要があります。

書式

EDATE(開始日,月)

開始日起算日を表す日付を指定します。

月開始日から起算した月数を指定します。月に正の数を指定すると、起算日より後の日付となり、負の数を指定すると、起算日より前の日付 となります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－

別の方法
例えばA1セルに誕生日が1975/4/6と入力されているとします。
=DATE(YEAR(A1)+3,MONTH(A1)+1,DAY(A1))
で３年１ヶ月後が帰ります。
1978/5/6
それぞれの関数の説明は、「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」
DATE
http://kiyopon.sakura.ne.jp/kansuu/date.html#date
YEAR
http://kiyopon.sakura.ne.jp/kansuu/date.html#year
MONTH
http://kiyopon.sakura.ne.jp/kansuu/date.html#month
DAY
http://kiyopon.sakura.ne.jp/kansuu/date.html#day
を参照してください。

戻る
一万の桁で二捨三入と七捨八入して５万単位にする

RIGHT関数で一万の桁の数値を切り出し、その値で、
二捨三入、七捨八入をします。
 　　４０００００～４２９９９９ならば４０万へ切捨てする
 　　４３００００～４４９９９９ならば４５万へ切り上げする
 　　４５００００～４７９９９９ならば４５万へ切り捨てする
 　　４８００００～４９９９９９ならば５０万へ切り上げする
 　　５０００００～５２９９９９ならば５０万へ切り捨てする
 　　５３００００～５４９９９９ならば５５万へ切り上げする

式が非常に長くなります。
セルA1に入力された数値を一万の桁で二捨三入と七捨八入

=IF(OR(AND(VALUE(RIGHT(INT(A1/10000),1))>=3,VALUE(RIGHT(INT(A1/10000),1))<=4),AND(VALUE(RIGHT(INT(A1/10000),1))>=8,VALUE(RIGHT(INT(A1/10000),1))<=9)),CEILING(A1,50000),FLOOR(A1,50000))

CEILING関数とFLOOR関数の説明
(基準値の最も近い倍数に丸める(ＦＬＯＯＲは切り捨て、ＣＥＩＬＩＮＧは切り上げ))

書式:=FLOOR(数値,基準値)

書式:=CEILING(数値,基準値)


戻る
「4」 と「9」を抜いて1,2,3,5,6,7,8,10,11,12,13,15･･･と連番を付ける

番号は1,2,3,5,6,7,8,10,11,12,13,15,16,17,18,20,21,22,23,25というように、下１桁 の数値が4or9の場合は次の番号になるようにします。

前の行の数値に１を加算して１の桁が4or9の場合は、前の行の数値に２を加算すればよいわけです。
以下に具体的な例を示します。
 
A B C
1 氏名 住所１ 番号
2 小泉純一 東京都中央区神田１-１ 1
3 田中真智子 東京都千代田区平和町2-3 2
4 鈴木宗夫 川崎市幸区堀川町590 3
5 石原慎太 東京都新宿区 5
6 山田太郎 神奈川県川崎市山の上1-2 6
C2セルに次の式を入力します。
=INDIRECT("r[-1]c",FALSE)+IF(OR(RIGHT(INDIRECT("r[-1]c",FALSE)+1,1)="4",RIGHT(INDIRECT("r[-1]c",FALSE)+1,1)="9"),2,1)
C3以降にC2の式を連続コピーします。
この場合、セルC1には数値の0を入れます。
セルの表示を0でなく、「番号」にするため、次のように入力します。
セルC1には0を入力し、「書式」「セル」を選択します。
「セルの書式設定」ダイアログボックスの「表示形式」で、「分類」から、「ユーザー定義」を選択し、「種類」テキストボックスに"番号"と入 力します。

戻る
セルの"C"と"2"の文字列の示すセルC2の内 容を参照したい。
INDIRECT関数を使います。

A1セルに"C"
B1セルに2
と入力されている場合。
=INDIRECT(A1&B1)とすれば、セルC2の内容が参照されます。

戻る
セルに入力したシート名"10月"でシートを指定してセルを参照したい
また、同様にセルに入力したシート名をつけて参照したい場合は、シート名がA1セルに"10月"と入力されているとしま す。
B1セルに"A2:A40"
=SUM(INDIRECT(A1&"!"&B1))
とすれば、シート名"10月"のセルA2:A40の合計値が計算されて、表示できます。

INDIRECT関数は、、参 照文字列を介した間接的なセルの指定を行います。



戻る
テストの成績を点数の範囲で集計する
FREQUENCY関数を使い配列数式として入力します。

FREQUENCY関数の使い方。

書式 =FREQUENCY(データ配列, 区間配列)

データ配列   頻度調査の対象となるデータを含む配列またはセル範囲を指定します。データ配列 に値が含まれていないと、要素としてゼロ (0) を含む配列が返されます。

区間配列   データ配列 で指定したデータをグループ化するため、値の間隔を配列またはセル範囲として指定します。区間配列 に値が含まれていないと、データ配列 で指定した配列要素の個数が返されます。


図の場合は、次のように入力します。
=FREQUENCY(B$3:B$12,$A32:$A41)
と入力し、{Shift}+{Ctrl}+{Enter}で確定し、配列数式として入力します。
配列数式として入力すると{    }で数式がくくられます。
{=FREQUENCY(B$3:B$12,$A32:$A41)}
この式を、すべての集計表に連続コピーします。

参考ファイル　frequency.xls   26.8kB


戻る
自動再計算の操作手順は
「ツール」「オプション」の「計算方法」タグから「自動」のラジオボタンをオンにして、「OK 」ボタンを押します。
Excel2007以降の場合は、[オフィスボタン]を押して、[エクセルのオプション」ボタンを押し、「数式」メニュー「計算方法の設定」グループで、「ブックの計算」項目の「自動」のラジオボタンをオンにして、「OK 」ボタンを押します。

戻る
小数点以下の切り捨ての方法
ROUNDDOWN関数を使います。
A1セルのデータの小数点以下を切り捨てる場合は次のようにします。
=ROUNDDOWN(A1,0)
ROUNDDOWN関数の使い方

戻る
重複データに印を付ける

重複データのすべてに印を付ける場合は、次のようにします。
=IF(COUNTIF($A$1:$A$5,A1)>1,0,1)
ただし、検索する対象範囲は$A$1:$A$5としいてます。

重複データに0、それ以外は1を表示

 
A B 値
1 小泉純一 =IF(COUNTIF($A$1:$A$5,A1)>1,0,1) 0
2 田中真紀 =IF(COUNTIF($A$1:$A$5,A2)>1,0,1) 1
3 小泉純一 以降セルB1の式を下方向にコピー 0
4 鈴木宗義 0
5 鈴木宗義 =IF(COUNTIF($A$1:$A$5,A5)>1,0,1) 0
式の意味
=IF(COUNTIF($A$1:$A$5,A1)>1,0,1)
現在の行のセル値と検索対象範囲のセル値を比較し、同じならその数が、２以上の場合、０を返し、１以下の場合１を返す。

戻る
重複データの２つ目以降に印を付ける

リストの中に重複データがあるかどうかを調べたい場合、COUNTIF関数で先頭セルから対象データのセルまでを比較 し、検索条件に一致する場合に重複データとして２以上の数を返します。
これにIF関数を組み合わせ、２以上（重複データの２番目以降）の場合は0を返しそれ以外は1を返します。

２つ目以降の重複データに0、それ以外は1を表示

 
A B 値
1 小泉純一 =IF(COUNTIF($A$1:A1,A1)>1,0,1) 1
2 田中真紀 =IF(COUNTIF($A$1:A2,A2)>1,0,1) 1
3 小泉純一 以降セルB1の式を下方向にコピー 0
4 鈴木宗義 1
5 鈴木宗義 =IF(COUNTIF($A$1:A5,A5)>1,0,1) 0
数式の意味
=IF(COUNTIF($A$1:A1,A1)>1,0,1)
現在の行のセル値と先頭セルから現在の行までのセル値を比較し、同じならその数が、２以上の場合、０を返し、１以下の場合１を返す。

戻る
計算式が見えないようにする

手順
1.[Ctrl]キーを押しながら[A]キーを押してすべてのセルを選択します。
2.「書式」「セル」の「セルの書式設定」ダイアログで、「保護」タブを開き、「ロック」のチェックを外します。
3.「編集」「ジャンプ」「セル選択」の「数式」のチェックを入れ計算式の入っているセルを選択します。

4.「書式」「セル」の「セルの書式設定」ダイアログで、「保護」タブを開き、「表示しない」のチェックを入れます。

5.「ツール」「シート保護」を選択して、パスワードを入力して、保護をかけます。

これで、数式が見えなくなるはずです。
保護を解除する場合は、パスワードがいりますので、パスワードは忘れないようにする必要があります。


戻る
複利計算で投資額と年数及び合計利息から年利を算 出したい
複利計算とありますが、元利合計の計算は次の式で求められます。
元利合計＝元金×（１＋利率）^期間です。
したがって
利率=(元利合計/元金)^（1/期間)-1

xの5乗根は=x^(1/5)
または、=POWER(x,1/5)ですから
1年複利とした場合は
A1セルに元利合計 1,300,000
B1セルに元金     1,000,000
C1セルに期間     5年
から
=(A1/B1)^(1/C1)-1
で、年利が計算できますね。
この場合0.053873952
が年利です。


戻る
住所から都道府県の表示を除く
住所から都道府県を取り除くには、住所の４文字目が「県」であるかどうかを調べ、県なら４文字取り出し、県でないなら３文字取り出す式を作成します。

添付ファイルを参照してください。
４文字目が「県」であるか判定するには、MID関数とIF関数を使います。
（都道府県名で４文字目のある県は「神奈川県」「和歌山県」「鹿児島県」の３県のみです。）
文字列の中から特定の位置の左側の文字を除くには、LEFT関数を使います。
文字列の中から特定の位置の右側の文字を除くには、RIGHT関数を使います。

D3セルの住所から都道府県のみを取り出す数式
=IF(MID(D3,4,1)="県",LEFT(D3,4),LEFT(D3,3))

式の意味
IF(MID(D3,4,1)="県",　　　　　　　→４文字目が県であるかを判定
LEFT(D3,4),　　　　　　　　　　　 →先頭の４文字を除く住所取り出す
LEFT(D3,3)　　　　　　　　　　　　→先頭の３文字を除く住所を取り出す
 

D3セルの住所から都道府県を除いた住所を出す数式
=IF(MID(D3,4,1)="県",RIGHT(D3,LEN(D3)-4),RIGHT(D3,LEN(D3)-3))
と入力します。

式の意味
IF(MID(D3,4,1)="県",　　　　　　　→４文字目が県であるかを判定
RIGHT(D3,LEN(D3)-3),　　　　　　　→先頭の３文字を除く住所取り出す
RIGHT(D3,LEN(D3)-4)　　　　　　　 →先頭の４文字を除く住所を取り出す

ＲＩＧＨＴ関数は文字列の右から指定数の文字 列を取り出します。
ＬＥＮ関数はデータの文字数を返します。
ＭＩＤ関数は文字列の先頭から指定位置から指定数の文字 を取り出します。


戻る
三角関数を使ってビルの高さを求める
図においてＢ地点からのＣまでの距離(a)と角度B(θ)が分かっている時、A-C間の高さ(b)とA-B間の距離 (c)は？
答え
b=a*tanθ
=10*TAN(60*PI()/180)
tanの説明
指定された角度のタンジェントを返します。
書式
TAN(数値)
数値   タンジェントを求める角度を、ラジアンを単位として指定します。角度が度で表されている場合は、PI()/180 を掛けてラジアンに変換します。
c=a/cosθ
=10/COS(60*PI()/180)
cosの説明
指定された角度のコサインを返します。
書式
COS(数値)
数値   コサインを求める角度を、ラジアンを単位として指定します。角度が度で表されている場合は、PI()/180 を掛けてラジアンに変換します。
同様に辺cのみが分かっている場合
      辺b=c*sinθ
      辺a=c*cosθ
　　　
また、辺bのみが分かっている場合
　　　辺a=b/tanθ
　　　辺c=b/sinθ
となります。

戻る
最大値、最小値を含めない合計を計算する

合計値から最大値、最小値を引けばいいのですから次のようになります。
セルA1:H1のデータを最大値、最小値を含めない合計は
=SUM(A1:H1)-MAX(A1:H1)-MIN(A1:H1)

戻る
'1000のように文字列として 入力したセルが、数値として認識してしまう
エクセルでは、数値の先頭に ' を入力すると文字列として表示され、表示位置も「右付け」から「左付け」になります。
しかし、数値が入力されているセルに文字列書式を設定した場合でも、数値は数値データとして格納されます。
Lotusのように文字列の入力されているセルを０として扱うには、「ツール」「オプション」を選択し、「移行」タグを開きます。
「シートオプション」の「計算方式を変更する」にチェックを入れます。
これで、文字列の入っているセル'1000のデータは０として扱われるようになります。
しかし、計算式に文字列がある場合も０として扱われますので、注意してください。

戻る
セルに日付を入力したら隣のセルに前月26、 来月25日を表示する
 

参考になる質問が「質問と回答」
http://kiyopon.sakura.ne.jp/situmon/index.htm
の「計算式・関数」「2002/10/2」
「誕生日の３年１ヶ月後を表示する」にあります。

前月26日は、今月-1ヶ月26日
来月25日は、今月+1ヶ月25日
A1セルに日付が2002/9/2と入力されている場合。
１ヶ月前は
=EDATE(A1,-1)
１ヶ月後は
=EDATE(A1,1)
となります。
前月の26日は
=DATEVALUE(TEXT(EDATE(A1,-1),"yyyy/mm/")&"26")
来月の25日は
=DATEVALUE(TEXT(EDATE(A1,1),"yyyy/mm/")&"25")
となります。
EDATE関数について

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
別の方法
A1セルに日付が2002/9/2と入力されている場合。
１ヶ月前は
=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))
１ヶ月後は
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
となります。
前月の26日は
=DATE(YEAR(A1),MONTH(A1)-1,26)
来月の25日は
=DATE(YEAR(A1),MONTH(A1)+1,25)
となります。

 それぞれの関数の説明は、「関数の使い方説明」
 http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「日付+時刻」

      DATE
      http://kiyopon.sakura.ne.jp/kansuu/date.html#date
      YEAR
      http://kiyopon.sakura.ne.jp/kansuu/date.html#year
      MONTH
      http://kiyopon.sakura.ne.jp/kansuu/date.html#month
      DAY
      http://kiyopon.sakura.ne.jp/kansuu/date.html#day
      を参照してください。

戻る
生年月日を入力した時、自動的に現在、「何歳何ヶ月」か表示する

A1セルに生年月日が1975/7/28のように入力されているとします。

現在が2002/8/30の場合

年齢と月を表示したいセルに

=DATEDIF(A1,TODAY(),"y")&"歳"&DATEDIF(A1,TODAY (),"YM")&"ヶ月"
と入力します。
＆（アンパサンド）は文字列結合関数です。この場合○歳と△ヶ月を結合します。

27歳1ヶ月
が帰るはずです。

この場合は文字列を返しますので、数としての計算はできません。

数値とするために日付シリアル値を返す計算式を示します。
=NOW()-A1-DAY(NOW())
セルの書式設定で、「ユーザー定義」とし、「種類」にY"歳"m"ヶ月"とに入力します。

DATEDIF関数の説明


戻る
セルに入力された計算式を表示する
全セルの計算式を表示
エクセルにもセルの計算式を表示する機能はあります。
「ツール」「オプション」でオプションダイアログボックスの「表示」タグで、「ウィンドウオプション」の項目で「数式」に
チェックを入れることですべてのセルの数式を表示できます。
この場合、セルの幅などによっては表示しきれないことがあります。
－－－－－－－－－－－－－－－－－－－－－－－－－－
選択セルのみ計算式を表示
また、セルを選択して数式を表示したい場合は対象のセルを選択し、[Ctrl]+[Shift]+を押しながら [']キーを押します。
計算式の表示を元に戻す場合は、再度対象のセルを選択し、[Ctrl]+[Shift]+を押しながら[']キーを押します。

戻る
1つのセルの数値を位ごとにそれぞれ個別のセルに分ける

MID関数を使います。
具体的には
セルA1のデータを１セルずつに分ける場合
 
 
A
1
123456
2 =MID($A$1,1,1)
3 =MID($A$1,2,1)
4 =MID($A$1,3,1)
5 =MID($A$1,4,1)
6 =MID($A$1,5,1)
7 =MID($A$1,6,1)
 
ＭＩＤ関数の説明

戻る
リンク元が空白なら 0 を表示せず空白表示に
２つの方法があります。
方法１
IF関数との組み合わせを使います。

ＩＦ関数
(論理式の結果が真なら、真の場合を返し、偽なら偽の場合を返します）
書式：=IF(論理式,真の場合,偽の場合)

たとえばA1セルにリンクする場合
=if(A1="","",A1)
あるいは、
=if(or(A1="",A1=0),"",A1)
とします。

方法２
オプションで０を表示しない設定にする。
メニューバーの「ツール」「オプション」の「表示」タグのウィンドウオプションで「ゼロ値」のチェツクを外します。

戻る
再計算に長い時間が、かかるようになった

再計算が時間がかかるのは全部のセルを自動的に再計算する設定にしているからです。
「再計算」を「手動」してデータを入力し「自動」にして表示し、保存の時は「手動」にして保存することで解決できます。

手動再計算の設定方法。

メニューバーの、「ツール」「オプション」を選択し、「計算方法」タグの「計算方法」を「手動」ラジオボタンを押し ます。
「（保存前に再計算）」のチェツクを入れます。（これはどちらでも）「ＯＫ」ボタンを押します。
手動再計算の方法
Ｆ９キーを押すことでいつでも再計算が実行されます。
従って、「再計算」「手動」でのデータの入力中は、データを入力しても計算結果が反映されません。
データ入力後、「Ｆ９」キーを押して「手動再計算」を実行します。
注意したいのは、「再計算」しないと計算結果が反映されない点です。

戻る
0000-1111-2222-3333を0000111122223333としたい

関数SUBSTITUTEを使います。
セルA1に
0000-1111-2222-3333が入力されている場合
=SUBSTITUTE(A1,"-","")
の式をセルB1に入力すれば、
0000111122223333
が帰ります。

戻る
計算式の組んであるセルの並べ替えで計算結果が狂 う

相対参照の式では、並べ替えをすると計算式も自動的に修正されます。

この場合、次の規則に従って計算式を入力します。
・同じ行の参照式は、相対参照とする。
・違う行の参照式は、絶対参照とする。

たとえば、２列目に入力した式で
=SUM(E1:E2)やH2/H5は
=SUM($E$1:$E2)や$H2/$H$5
になります。


戻る
平均、偏差、最大値、最小値などの引数に０を計算に入れない方法
数字の0を集計しないようにIF関数を「配列数式」で入れます。
「配列数式」は、{Ctrl}+{Shift}{+Enter}で配列数式として確定されます。
確定後数式は、{   }でくくられます。
自分で{   }を入力してはいけません。
 
A
1 得点
2 98
3 68
4 0
5 78
6 47
7 65
それぞれの計算を次のように行います。
最大
=max(IF(A2:A7<>0,A2:A7))
と入力し、{Ctrl}+{Shift}{+Enter}で配列数式として確定します。

最小
=min(IF(A2:A7<>0,A2:A7))
と入力し、{Ctrl}+{Shift}{+Enter}で配列数式として確定します。

合計
=sum(IF(A2:A7<>0,A2:A7))
と入力し、{Ctrl}+{Shift}{+Enter}で配列数式として確定します。

平均=average(IF(A2:A7<>0,A2:A7))
と入力し、{Ctrl}+{Shift}{+Enter}で配列数式として確定します。

個数=sum(IF(A2:A7<>0,1))
と入力し、{Ctrl}+{Shift}{+Enter}で配列数式として確定します。



戻る
複数シートに対してVLOOKUP関数を利用する方法は
次のように考えたらどうですか。
手順１
検索用のシートを１つ作成し、そこに検索データを入力します。
具体的には
SheetQセルA1に検索用のコード番号が入力されます。

検索用シートに結果を返す式を入力します。
セルA2にデータの検索結果を表示する計算式を入力します。
(Sheet1,2,3,4,5,6の検索結果を & で文字列結合して返します。)
=Sheet1!Q1&Sheet2!Q1&Sheet3!Q1&Sheet4!Q1&Sheet5!Q1&Sheet6!Q1

手順２
シートにそれぞれのシートに検索用シートの検索データに対するVLOOKUP関数を作成します。
具体的には、

Sheet1,Sheet2,Sheet3,Sheet4,Sheet5,Sheet6には、それぞれのデータリストが同じ形式で 入力されています。
A2:A100にコード番号、B2:B100に結果データが入力されているものとします。
それぞれのSheet1,2,3,4,5,6のセルQ1にVLOOKUP関数を入力します。
（検索結果がない場合は何も返しません）
=if(iserror(VLOOKUP(SheetQ!A1,A2:B100,2,false)),"",VLOOKUP(SheetQ!A1,A2:B100,2,false))

戻る
IF関数で、5＜A1＜8の条件を満たすものは○、満たさない場合は×にする
 

二つの方法があります。
１　IF関数とAND関数を使います。
    =if(and(A1>5,A1<8),"○","×")

２　IF関数だけの場合は2つネストします。
    =if(a1>5,if(a1<8,"○","×"),"×")


戻る
セルをｎ行おきに合計する
２行おき３行おきに合計する関数として「配列関数」が利用できます。
=SUM(IF(MOD(ROW(セル範囲),行数)=MOD(ROW(先頭のセル),行数),セル範囲))

の数式を入力し、{Shift}+{Ctrl}+{Enter}で確定します。
確定すると数式は{   }でくくられて表示されます。

引数の説明
セル範囲:ｎ行おきに合計する先頭行から最終行までのセル範囲
行数:ｎ行おきのn　２行おきなら2，３行おきなら3

具体的には
セルB2からB20までの範囲を２行おきに合計する場合は
=SUM(IF(MOD(ROW(B2:B20),2)=MOD(ROW(B2),2),B2:B20))
となります。

また、こちらにVBAマクロで 作成した「拡張SUM」ユーザー定義関数もあります。
 

セルをｎ列おきに合計する
 
２列おき３列おきに合計する関数として「配列関数」が利用できます。
=SUM(IF(MOD(COLUMN(セル範囲),列数)=MOD(COLUMN(先頭のセル),2),セル範囲))
の数式を入力し、{Shift}+{Ctrl}+{Enter}で確定します。
確定すると数式は{   }でくくられて表示されます。

引数の説明
セル範囲:ｎ列おきに合計する先頭列から最終列までのセル範囲
列数:ｎ列おきのn　２列おきなら2，３列おきなら3

具体的には
セルB2からX2までの範囲を２列おきに合計する場合は
=SUM(IF(MOD(COLUMN(B2:X2),2)=MOD(COLUMN(B2),2),B2:X2))
となります。
 

戻る
同順位の場合得失点差を加味した順位を計算する

得失点差を加味するために、勝ち数に得失点差×１０００分の一加算してRANK関数で順位をつけます
 
A B C D E F G H
1 勝 負 得点 失点 差 順位1 勝ち数に得失点差×1/1000を加算 最終順位
2 3 1 59 69 -10 1 2.99 3
3 3 1 74 58 16 1 3.016 1
4 0 4 49 60 -11 5 -0.011 5
5 1 3 53 58 -5 4 0.995 4
6 3 1 79 69 10 1  3.01 2
 
G2には、
=A2+E2/1000
と入力します。
G3:G6まで貼り付け

H2には、
=RANK(G2,$G$2:$G$6,FALSE)
と入力します。
H3:H6まで貼り付け

戻る
○や×を検索し、正解率や正解数を表示する

sum,if,counta関数を配列数式で使います。
具体的には
a10:a100　に氏名
b1:b100　に○,×が入力してあります。
を入力してあります。
 
A B C D
1 田中真子 ○ 　 　
2 小泉純一 × 　 　
3 鈴木宗義 ○ 　 　
～ ～ ～ ～ ～
100 鈴木清美 ○ 　 　
正解率
下の式を入力後[Ctrl]+[Shift]+{Enter]ボタンで確定します。
=sum(if(b1:b100="○",1,0)/counta(b1:b100)
確定後は{  }で数式がくくられます。
{=sum(if(b1:b100="○",1,0)/counta(b1:b100)}

正解数
下の式を入力後[Ctrl]+[Shift]+{Enter]ボタンで確定します。
=sum(if(b1:b100="○",1,0)
確定後は{  }で数式がくくられます。
{=sum(if(b1:b100="○",1,0)}
－－－－－－－－－－－－－－－－－－－－－－－－－－
配列数式については、「質問と回答」2002/3/23
http: //www.katch.ne.jp/~kiyopon/situmon/kaitou/index03.htm#複数の検索条件に合致するセルをカウントする
に詳しい説明があります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
それぞれの関数の説明は以下にあります。
if関数についての説明
http://kiyopon.sakura.ne.jp/kansuu/if.html#if
sum関数についての説明
http://kiyopon.sakura.ne.jp/kansuu/abs.html#sum
counta関数についての説明
http://kiyopon.sakura.ne.jp/kansuu/cell.html#counta


戻る
エラーの計算結果を返さないようにしたい
２つの方法があります。
IF関数で何も表示しないようにする
これはセルの計算結果がエラーならば表示しないようにすればいいのです。
結果がエラーかどうかはISERROR関数を使います。
=ISERROR(A1*B1)
さらにIF関数でエラーならば""を返し、てなければA1*B1を返すようにします。
=IF(ISERROR(A1*B1),"",A1*B1)

「条件付き書式」の設定で、文字のフォントの色を白で表示する
対象のセル(たとえばセルA1)を選択し、「書式」「条件付き書式」を選択し「条件1」に「数式が」
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)
「=ISERROR(A1)」と入力し、「書式」ボタンを押して「フォント」の「色」を「白」にして「ＯＫ」ボタンを押します。

戻る
入力した文字を１セルに１文字ずつ表示する

mid関数を使用します。

ＭＩＤ関数の説明
（文字列の中から任意始位置,文字数)

文字列
    取り出す対象となる文字列を指定
開始位置
    取り出しを開始する位置（左端からの文字数）
文字数
    取り出す文字数を指定
------------------------------------------------------------
具体的には
セルA1に文字列が入力してある場合、１文字目の文字を表示するには
=MID($A$1,1,1)
２文字目の文字を表示する場合
=MID($A$1,2,1)
となります。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
ｍｉｄ関数の詳しい説明は
 http://kiyopon.sakura.ne.jp/kansuu/val.html#midに あります。


戻る
半角数値100,000を全角文字列１００，０００と変換した い
次のような式を入力すればできます。
 
A B
1 100,000 =JIS(TEXT(A1,"###,###"))
B1には　全角の文字列"１００，０００"が帰ります。
ＪＩＳ関数は半角の英 数字を全角にします。
ＴＥＸＴ関数は指定 した形式の文字列に変換して表示します。

戻る
セルに入力してある名前を「苗字」と「名前」に分 ける方法
「苗字」と「名前」の間にスペースあるいは他の特定の文字や記号があれば簡単に分けられます。
たとえば、「苗字」と「名前」の間にスペース"　"が入力してある場合。
 
A B C
1 田中　真紀子 =LEFT(A1,FIND("　",A1,1)-1) =MID(A1,FIND("　",A1,1)+1,LEN(A1))
2 戻り値→ 田中 真紀子
文字列の間にスペースがない場合や、半角スペースの場合はエラーが帰ります。

find関数は、 文字列の中から、指定した文字列と一致する部分を探し出し、文位置（文字番号）を返します。
left関数は、文 字列の左端から指定した数の文字を取り出します。
mid関数は文字列の 指定位置から指定数の文字を取り出します。
right関数は、 文字列の右端から指定した数の文字を取り出します。



戻る
「文字列の引き算」"あいうえお"-"いう"="あえお"
「ユーザー定義関数」で作成したものがこちらにあります。
書式　=MOJISUB(文字列1,文字列2)
文字列1:元の文字列を指定します。
文字列2:引く文字列を指定します。

 文字列1に記述されている文字列から文字列2に記述されている文字列を引き算します。

 例2
 
A B C 戻り値
1 あいうえお いえ =MOJISUB(A1,B1) あうお
詳しい説明はこちら



戻る
四捨五入・切り捨て・切り上げした値のSUM関数の合計が合わない
おそらく個別のセルの表示が四捨五入あるいは、切り捨て切り上げになっているためと思われます。
「表示桁数で計算する」設定にすることでSUM関数の合計が合います。
しかし、計算誤差が出ますのであまりおすすめできません。
方法は、「ツール」「オプション」を選択し、「計算」タグで「表示桁数で計算する」にチェックを入れ「ＯＫ」ボタンを押します。

戻る
角度の入力方法・表示方法・計算方法
１．エクセルで角度（度分秒）の入力方法と表示方法は？
　　例えば　３０度４５分２０秒　や３０°４５’２０”
時刻と同じ入力方法にする。
30:45:20
表示形式を「ユーザー定義」で[h]"度"m"分"s"秒"とすれば30度45分20秒と表示されます。
　45°16′00″ のように表示させたい場合には、「ユーザー定義」で[h]"°"mm"′"ss"″"とします。
数値データは1.281481481となります。
（１日と0.281481481日という数値で２４倍すれば度になります）
２．角度の６０進数を１０進数に表示させる関数は？
　　例えば　３０度４５分２０秒　と入力すると３０．７５５５・・・と表示させる
６０進数を１０進数にするよりも次のようにすると簡単です。
A1セルに30:45:20と入力した場合次の式を入れることで30.75555556と表示されます。
=A1*24
３．角度の６０進数の計算方法
A1セルに30:45:20と入力してあり0度20分45秒を加算したい場合
A2セルに0:20:45と入力し
=A1+A2 または
=A1+"0:20:45"
とすれば　1.295891204　が帰り、その値を２４倍すれば　31.10138889となり、表示形式を「ユーザー定義」で[h] "度"m"分"s"秒"とすれば　31度06分05秒　が帰ります。

戻る
複数の検索条件に合致するセルをカウントする（配列数式）
配列数式を使います。
Ａ1:A10の値が1で、かつＢ1:B10の値が1であるセルの数を返します。
 
A B C
 1 1 2 {=COUNT(IF(A1:A10=1,IF(B1:B10=1,B1:B10)))}
 2 3 1
 3 2 1
 4 1 1
 5 5 2
 6 2 1
 7 6 3
 8 1 1
 9 8 2
10 4 3
C1セルには 2 が返ります

=COUNT(IF(A1:A10=1,IF(B1:B10=1,B1:B10)))
配列数式は、数式入力後に確定する場合Ctrl+Shift+Enterで確定します。
確定後は、数式が  {      }　で囲まれます。
－－－－－－－－－－－－－－－－－－－－－－－－－－－
配列数式にいて詳しいことは、
「エクセルの勉強部屋」の「質問と回答」の2001/9/21を参照してください。
http://kiyopon.sakura.ne.jp/situmon/kaitou/index06.htm# 複雑な検索条件に一致するセルの値を合計する方法、個数を数える方法

戻る
ＵＮＩＸタイムから年月日時分秒を算出、及びその逆に年月日時 分秒からＵＮＩＸタイムを算出

 
Ａ Ｂ
1 1015821124 ="1970/1/1"+A1/24/60/60
2 2002/3/11 4:32:04 =(A2-"1970/1/1")*24*60*60
これで
B1セルはＵＮＩＸタイムを年月日時分秒にする
B2セルは年月日時分秒からＵＮＩＸタイムにする

A2とB1の書式設定は　　"yyyy/m/d hh:mm:ss
A1とB2の書式設定は    数値
とします。


戻る
データのないところには、答えを表示しないように したい
IF関数で、データのない場合は0を返し、「オプション」の「表示」設定で0のデータを表示しないようにすればよいのです。
 
A B C
1 3 5  =if(sum(A1:B1)=0,0,sum(A1:B1))
2 =if(sum(A1:B1)=0,0,sum(A1:B1))
数式を入力したセルを選択し、「ツール」「オプション」を選択し、「表示」で、「ゼロ値」のチェックを外し、「ＯＫ」ボタンを押します。
これで、
Ｃ１　セルには8が帰り
Ｃ２　セルには何も表示しません

IF関数の書式
IF(条件,真の場合の処理,偽の場合の処理)
IF関数について


戻る
リンクされたブックがない場合のリンク先の取り消し
Excel2002の場合
リンク元のブックがない場合は、新規ファイルでリンク元のブック名と同じファイル名で保存し、ファイルを閉じます。
このファイルはダミーのリンクもとファイルとなります。
リンク先のファイルを開きます。
このとき、「このブックには、他のデータソースへのリンクが設定されています。」というメッセージがでますので、「更新しない」ボタンをク リックします。
「編集」「リンクの設定」を選択し、「リンク元の解除」ボタンを押し、「リンクの解除」ボタンを押します。
これでリンクが解除できます。
その後、リンク元のファイルを削除します。

その他の場合
ブック間のリンクを解除するには、「編集」「形式を選択して貼り付け」コマンドを使用して、数式の外部参照を定数に変更します。
その結果、参照元ブックへの全てのリンクが削除され、数式の値は更新されなくなります。
「形式を選択して貼り付け」コマンドを使用してリンクを解除するには、次の手順に従ってください。
１「編集」「検索」コマンドを選択し、外部参照を含む数式が入力されている全てのセルを選択する。
まずは、「検索する文字列」テキストボックスに!と入力して、外部参照には必ず含まれる感嘆符を指定し、「対象」ドロップダウンリストから 「数式」を選択する。
２「次を検索」ボタンをクリックし、参照元ブックを参照している数式を検索する。
３このようにして他のブックを参照している数式の入力されているセルを選択する。
４「編集」「コピー」コマンドを選択し、次に「編集」「形式を選択して貼り付け」コマンドを選択する。
５「形式を選択して貼り付け」ダイアログボックスの「値」オプションを選択し、「ＯＫ」ボタンをクリックしてから、{Esc}を押してクリッ プボードの内容をクリアする。

戻る
電話番号の市外局番を削除する方法は

電話番号の市外局番での区切り文字があれば、可能です。
たとえば、"－"で区切りとなっていると
 
A
1 03-4567-8910
2 =MID(A1,FIND("-",A1,1)+1,10)
A2セルに4567-8910が帰ります。

この計算式の意味は、
FIND関数は、文字列の中から検索文字の位置（左からの文字数）を返します。
FIND(検索文字,文字列,開始位置)
MID関数は、文字列の取り出し位置（左からの文字位置）から、指定数の文字を返します。
MID(文字列,取り出し位置,文字数)

詳しいことは、「文字列関数」を 参照してください。


戻る
特定の文字をある数値として計算さ せたい
Ａを５として定義する場合。
「挿入」「名前」「定義」を選択し、「名前の定義」ダイアログボックスを表示します。
名前の入力欄にＡと入力し、「参照範囲」の入力欄に数値５を入力し{ＯＫ}ボタンを押します。
これで、=Aとすれば５が表示され、文字Ａ＝数値５となります。
この場合、数式バーには=Aと表示され、セルの表示は５となります。

戻る
時間の８時間後を自動で表示したい

１日は１で２４時間ですから８時間は8÷24です。
Ａ１セルに１０：００が入力されていると、Ｂ１セルに８時間後を表示するには
=A1+8/24
となります。
セルの表示形式は時刻にしてください。

戻る
「絶対参照」と「相対参照」について
下の式は「相対参照」ですから、ほかのセルにコピーすると、参照式のセルも相対的に移動します。
{=COUNT(IF(B3:B25=G3,IF(C3:C25="カード",0)))}

「絶対参照」とするために、コピー元の式を編集します。
セルを選択してF2キーを押し、「編集」モードにします。
数式バーのB3にカーソルを移動し、F4キーを押します。
F4キーを押すたびに「絶対参照」、「相対参照」、「複合参照」が切り替わります。
F4キーを押しすぎたときは、$B$3になるまでF4キーを数回押します。

{=COUNT(IF($B$3:$B$25=G3,IF($C$3:$C$25="カード",0)))}

$が付くと、その列または行が「絶対参照」になります。

$B3では、列Bが固定されます。
B$3では、行3が固定されます。
$B$3では、列B行3とも固定されます。

「エクセルの勉強部屋」の「操作説明」の「数式の利用」のなかに
「相対参照・絶対参照・複合参照」がありますので、参考にしてください。

戻る
7:25→7:00 7:32→8:00としたい(29捨30入)

まず、３０分を時刻から引き算します。
30分は１日÷24時間÷2ですから　1/24/2
E1に時刻　7:29　が入力されているとすると、時刻から30分を引くのは　E1-(1/24/2)
これで、
 7:29 の場合は 6:59 となります。
 7:30 の場合は 7:00 となります。
 7:31 の場合は 7:01 となります。
１時間単位で丸めるます。
FLOOR関数を使います。
FLOOR(数値,単位)
この関数は、数値を単位で丸め端数を切り捨てます。
=FLOOR(124,10)で120となります。

１日は２４時間で１時間は　1/24ですから
=FLOOR(E1+(1/24/2),1/24)
これで、
7:29 の場合は 7:00
7:30 の場合は 8:00
となり、３０分に満たないものは時刻そのままの正時となり、３０以上のものは時刻切り上げの正時となります。

下に、(29捨30入)の実際の式を示します。
表示形式は、すべて{時刻}にしています。
 
  A B 表示（時刻）
7 7:29 =FLOOR(A7+(1/24/2),1/24) 7:00
8 7:30 =FLOOR(A8+(1/24/2),1/24) 8:00
9 7:31 =FLOOR(A9+(1/24/2),1/24) 8:00
また、３０分区切りで切り上げる場合は次のようになまりす。
表示形式は、すべて{時刻}にしています。
 
  A B 表示（時刻）
7 7:29 =CEILING(A7,1/24/2) 7:30
8 7:30 =CEILING(A8,1/24/2) 7:30
9 7:31 =CEILING(A9,1/24/2) 8:00
また、１５分区切りで切り上げる場合は次のようになまりす。
表示形式は、すべて{時刻}にしています。
 
  A B 表示（時刻）
7 7:29 =CEILING(A7,1/24/4) 7:30
8 7:30 =CEILING(A8,1/24/4) 7:30
9 7:31 =CEILING(A9,1/24/4) 7:45
 

戻る
西暦を元号に変換する
元号の表示　1920年を大9と表示したい。

日付の入っているセルを選択し、セルの書式設定で、「表示書式」を「ユーザー定義」とし、「種類」の欄にggeと入力し［ｏｋ］ボタン を押します。
たとえば、1920/4/5と入力されていると、　大9　と表示されます。
入力されるのは、日付形式　 yy/mm/dd　 yyyy/mm/dd
のようになっていなければでなければなりません。

日付形式でなくA1セルに1920 と入力した場合は、次のようにB1セルに計算式を記入します。

 
  A B
1 1920 =TEXT((A1&"/1/1"),"gge")
B1セルには　大9 が帰ります。

戻る
日付データから年(,月,日)を求めて年を加算 したい

年の変更をするには、日付書式から年の部分だけ取り出して計算し、その後、日付書式に変更します。

具体的には
セルA1に日付
2001/1/1

日付データから年を返す関数
=year(A1)
これで、数字の2001が取り出されます。
これに５を加算する
=year(A1)+5
2006が帰ります。

日付データから月を返す関数

=month(A1)で月が帰ります。

日付データから日を返す関数

=day(A1)で日付が帰ります。

文字列結合関数　&　で文字を結合し、再度日付に戻します。
=year(A1)+5&"/"&month(A1)&"/"&day(A1)
文字として"2006/1/1"が帰りますので、日付となるようにします。
=datevalue(year(A1)+5&"/"&month(A1)&"/"&day(A1))
計算結果は、数字に(38718)なりますので、日付表示にします。
セルの書式設定で、「表示形式」「日付」で「種類」の中から必要な表示形式を選択し、「ＯＫ」ボタンを押します。


戻る
2-1920-600-300で、600 を数字として抜き出すには
 
  A
1 2-1920-600-300
2 =value(mid(A1,8,3))
=value(mid(A1,8,3))
とします。
600が数字として帰ります。
mid関数の説明

戻る
請求書のNo.を連番で自動入力したい

例えば Sheet1 のセル A1 に「請求書No.」という項目名を入力しておきます。
セル B1 に通し番号が自動で入るようにする例です。

セルB1にはセルF1に日付が入力されていれば、注文番号を表示する式=IF(F1="","",N1) を入力しておきます。
セルN1には、通し番号の元データがマクロで自動入力されるようにしています。
ファイルを開いたときにマクロでセルN1のデータ に１を加算します。
F1セルには日付　たとえば、2002/1/16　が入力されます。

マクロは{Alt}+{F11}を押して、「挿入」「標準モジュール」を選択します。
「標準モジュール」のModule1（コード）に以下のコードを張り付けます。

'--------------------'伝票の自動通し番号入 力
'--------ファイルを開いたときに自動で実行されます。
Sub Auto_Open()
'セルN1の数値に1を加算します。
   With Sheets("Sheet1").Range("N1")
       .Value = .Value + 1
   End With
End Sub

'-------------ファイルを閉じたときに自動で実行されます。
Sub Auto_Close()
'ファイルを保存します
    ThisWorkbook.Save
End Sub
'-----------------------------

注意：このファイルを開く場合は、「マクロを有効にする」必要があります。
【Eｘｃｅｌ９７の場合】
「ツール」「オプション」の「全般」から「マクロウィルスから保護す る」のチェツクをはずして「ＯＫ」ボタンを押す。
【Excel2000の場合】【Excel xpの場合】
「ツール」「マクロ」「セキュリティ」からセキュリティレベル「低」 をチェツクして「ＯＫ」ボタンを押す。
---------------------------------
参考に添付ファイルをつけます。
bangou.xls        24.5kB
nouhisyo.xls      48.0kB  「納品書」見本
nouhinsyoxls.lzh  16.1kB　「納品書」の圧縮形式ファイル

戻る
計算を表示した数値で行いたい

方法１
「ツール」「オプション」で「計算方法」の「表示桁数で計算する」にチェツクを入れます。
しかしながら、計算値に誤差が出ますので、あまり推奨できません。

方法２
round関数を使う
=round(合計計算式),0)
とすることで、計算結果を四捨五入することができます。
こちらのやり方が一般的です。


戻る
日付の計算をしたい
 
  A B
1 H13.12.5 H12.1.5
2    
3 =A1-B1  
A3の「書式」「セル」で「書式設定」を「数値」で小数点以下０とすれば　
A3は　701となります。
この計算では、閏年も正しく計算します。

戻る
日付から年、月、日を取得

セルA1に日付2001/12/19
これを年の部分だけ取り出します。
=year(A1)
これで、数字の2001が取り出されます。
同様に月と日付を返す関数を利用します。
=month(A1)で月12が帰ります。
=day(A1)で日付19が帰ります。
取り出した年、月、日は数字として計算できます。
例えば、年に５を加算するには、=year(A1)+5 で2006となります。

関数の説明
YEAR
MONTH
DAY


戻る
年、月、日の文字列を日付にする
文字列結合関数　&　で文字を結合し、再度日付に戻します。
A1セルに"平成15年"
B1セルに"10月
C1セルに"5日"
と入力されているとします。

D1セルに
=datevalue(A1&B1&C1)
と入力します。

37899
が返ります。
計算結果は、日付シリアル値の数字になりますので、日付表示にします
セルの書式設定で、「表示形式」「日付」で「種類」の中から
必要な表示形式を選択し、「ＯＫ」ボタンを押します。
datevalue関数の説明

日付が数値のみ入力されている場合は
A1に年が2003
B1に月が10
C1に日が5
と入力されている場合
=date(A1,B1,C1)

日付が数値のみ入力されている場合は
A1に元号の年が15
B1に月が10
C1に日が5
と入力されている場合
=date(A1+1988,B1,C1)

計算結果は、日付シリアル値の数字になりますので、日付表示にします
セルの書式設定で、「表示形式」「日付」で「種類」の中から
必要な表示形式を選択し、「ＯＫ」ボタンを押します。
date関数の説明


戻る
文字データの日付を日付シリアルデータにする
A1セルの文字データ"2001/12/01"を日付データにするには
 
  A B
1 "2001/12/01" =datevalue(A1)
戻る
数字データの日付を日付シリアルデータにする
A1セルのという数字データを、日付データにするには
 
  A B
1 20011201 =datevalue(left(a1,4)&"/"&mid(a1,5,2)&"/"&right(a1,2))
上の式は、
A1データの左から４文字、中の五文字目から２文字、右から２文字とって、日付シリアルデータにするものです。

2001/12/01の日付シリアルデータは数値の37226です。

いずれも、表示形式を日付にすれば画面上は、日付のように表示されます。
TEXT関数では日付データを文字列に変換してしまい ますので、計算ができません。


戻る
最小値を求める関数ＭＩＮで0を除外するには
SMALL関数を使い、MIN関数の値が0の場合は、２番目に小さい値を最小値とします。

例
A1セルからA10セルの最小値0以外を見つける式
=IF(MIN(A1:A10)=0,SMALL(A1:A10,2),MIN(A1:A10))

SMALL関数の説明


戻る
指定した月の月末を表示したい
A1セルに指定の月を入力します。
月末は次の月の１日より１日少ない日ですから、DATEVALUE(A1+1&"/1")-1です。
月末を表示したいセルに次の式を入力します。
=TEXT(DATEVALUE(A1+1&"/1")-1,"m/d")
これで、指定の月の月末を表示します。

datevalue関数の説明


戻る
そのときの時刻が自動的に入力されるようにしたい
現在時刻の取得は
=NOW()関数を使います。
NOW()関数は、現在の日付と時刻に対応するシ リアル値を返します。


表示形式を指定するなら

=text(now(),"h:m")
とすれば現在時刻を 12:35 のように表示します。
しかし、この表示時刻は通常ワークシートを開いたときだけ計算されます。
これを、その時の値に表示し直すには「再計算」をする必要があります。
[F9]キーを押すことで「再計算」されますので、その時点の値になります。
TEXT関数の説明
時刻を再計算しなしようにしたい
しかし、次にファイルを開いたときにもその時の時刻になりますので、再度の計算をしたくない場合は、計算後にそのセ ルをコピーし、「値として張り付け」することで、計算をしないようにします。

手順は、セルを選択し、「編集」「コピー」した後「編集」「形式を選択して張り付け」「値」のチェックして「Ｏｋ」ボタンを押しま す。

「値として張り付け」は、「エクセルの勉強部屋」の「エクセルで使えるソフト」こちらに簡単なものありますので、参考にしてくださ い。


戻る
給料などの金種（一万円が何枚、五千円が何枚、千円が何枚など）を表示したい
給料などを一万円、五千円、千円、五百円、百円、五十円、十円、五円、一円のそれぞれの金種に分ける計算式。
 
  A B C D E F G H I J
1   \10,000 \5,000 \1,000 \500 \100 \50 \10 \5 \1
2 1,234,567 123 0 4 1 0 1 1 1 2
セルB2には、
=INT(A2/B1)
と入力します。

セルC2には、
=INT(($A$2-SUMPRODUCT($B$1:B$1,$B2:B2))/C1)
と入力します。
数式の意味
セル$A$2の数値から、セル範囲$B$1:B$1の金額×セル範囲$B2:2の合計を引き算し、対象の金額のセルC1で割った商を返します。
このセルをコピーして右のセル範囲J2まで貼り付けます。

それぞれの関数の説明は、「関数の使い方説明」http://kiyopon.sakura.ne.jp/kansuu/index.htm
の「数学+三角」の
INT
SUMPRODUCT
を参照してください。

見本はこちら
kinsyu.xls
19kB


戻る
数字の小数部のみを返したい
セルA1の小数部を返す式の例です。
=VALUE(SUBSTITUTE(RIGHT(A1,LEN(A1)-LEN(ROUNDDOWN(A1,0))),".",""))

A1セルに1.2345と入力されると2345を返します。
式を
=VALUE("0." & SUBSTIYUTE(RIGHT(A1,LEN(A1)-LEN(ROUNDDOWN(A1,0))),".",""))
とすれば0.2345を返します。

式の説明
VALUE(文字列)
文字列を数字にします

SUBSTITUTE(文字列,検 索文字列,置換文字列,置換対象)
検索文字列を新しい文字列に置き換える

RIGHT(文字列,文字数)
文字列の右端から指定した数の文字を取り出し

LEN(文字列)
半角や全角の区別なく、文字列データの文字数や数値データの桁数を返す

ROUNDDOWN(数値,桁数)
指定した桁数で切り捨てる
 


戻る
年月を入れるだけで、カレンダーの日付と曜日を自動表示したい
A1セルに年(西暦)を入力する。
B1セルに月を入力する。
A列に日付を表示するための計算式を入力（下の場合C列の式）する。
Ｂ列に曜日を表示するための計算式を入力（下の場合D列の式）する。
Ｃ列にＡ列の計算式を表示しています。
Ｄ列にＢ列の計算式を表示しています。
A列のセルの表示形式は「セルの書式設定」で「ユーザ定義」の「d」とする。
B列のセルの表示形式は「セルの書式設定」で「ユーザ定義」の「aaa」または「aaaa」とする。
２９日以降は日付の表示計算のための式を入力します。
 
  A B C D
 1 2001 9 　 　
 2 1 土 =DATEVALUE(A1&"/"&B1&"/1") =A2
 3 2 日 =A2+1 =A3
 4 3 月 上の行の式をコピー 上の行の式をコピー
 5 4 火 同上 同上
 6 5 水 同上 同上
 7 6 木 同上 同上
 ←  同上  - 同上 同上
28 27 木 同上 同上
29 28 金 同上 同上
30 29 土 =IF(DAY(A29+1)<4,"",A29+1) 同上
31 30 日 =IF(DAY(A29+2)<4,"",A29+2) 同上
32  　  　 =IF(DAY(A29+3)<4,"",A29+3) 同上

戻る
土曜日・日曜日の日付を自動で色付けする
土曜日、日曜日の日付をそれぞれ青、赤に設定する方法。
「曜日の色付け」
セル範囲A2:C32を選択し、「セル」「条件付き書式」を選択します。

「日曜日付の色付け」
条件を「数式が」に変更し
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)
=WEEKDAY($A2,2)=7　と入力し、「書式」ボタンを押し、フォントの色 を「赤」にし、「パターン」の色を水色にし、「ＯＫ」ボタンを押します。
「土曜日付の色付け」
「追加」かボタンを押し、同様に「条件2」に条件を「数式が」に変更し
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。
「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「数式を使用して、書式設定するセルを決定」を選択し、[ルールの内容を編集しいてください]で、「次の数式を満たす場合に値を書式設定」の中に、数式を)

=AND(WEEKDAY($A2,2)=6,COUNTIF(祝日,$A2)=0)　と入力し、「書式」ボタンを押し、フォ ントの色を「青」にし、パターン」の色を水色にし、「ＯＫ」ボタンを押します。

「祝日の色付け」
セル範囲A2:C32を選択し、「セル」「条件付き書式」を選択します。

条件を「数式が」に変更し=OR(WEEKDAY($A2,2)>5,COUNTIF(祝日,$A2)>0)　と 入力し、「書式」ボタンを押し、フォントの色を「赤」にし、パターン」の色を水色にし、「ＯＫ」ボタンを押します。


セルＡ２の書式設定

シート「縦書き」のセルＡ１には名前"y"が設定してあります。シート「祝日」の年の値として計算しています。
シート「祝日」の列Ａには名前"祝日"が設定してあります。条件付き書式の計算に使用しています。
シート「祝日」の列Ａにある00/00は振り替え休日の計算式が入力してあります。該当日が振り替えでない場合00/00を表示しています。
シートは、縦書き、横書き、祝日があります

祝日のシートに祝日の設定がしてあります。

見本のエクセルファイル「calendar.xls 57kB」 はこちら



戻る
五捨六入はどうやるの？
INT(A1+0.4)
とすると、Ａ１セルのデータを小数点以下１位で五捨六入されます。
ＩＮＴ関数はデータの数値を超えない最大の整数を返します。
従って、セルＡ１のデータが1.5の場合+0.4で、()内は1.9となり、整数にすると1になるわけです。
また、A1セルが1.6の場合は+0.4で、()内は2.0となり、整数にすると2になるわけです。

同様に、二捨三入は+0.7とすればいいのです。

また、整数部で五捨六入する場合は
=INT((A1+4)/10)*10
A1セルが15の場合+4で19で、それを10で割って1.9となり、これを正数にして1、これを10倍すれば10となります。

INT関数の説明


戻る
計算式（関数）の保護をしたい
計算式が入力されているか判定し、計算式の入力されているセルを保護

手順
1.行番号1の上（又は列番号A左）の番号のない行列を選択し、全てのセルを選択します。
2.「書式」「セル」の「セルの書式設定」ダイアログで、「保護」タブを開き、「保護」のチェックを外します。
3.「編集」「ジャンプ」「セル選択」の「数式」のチェックを入れ計算式の入っているセルを選択します。
4.「書式」「セル」の「セルの書式設定」ダイアログで、「保護」タブを開き、「保護」のチェックを入れます。
5.「ツール」「シート保護」を選択して、パスワードを入力して、保護をかけます。

これで、数式入力セルがすべて保護されます。
保護を解除する場合は、パスワードがいりますので、パスワードは忘れないようにする必要があります。

ロックの解除をするには
「ツール」「保護」「シートの保護解除」を選択します。

「エクセルで使えるソフト」http://kiyopon.sakura.ne.jp/soft/index.htm
に私の作成したソフト
「数式セルの保護・解除」アドインソフト
があります。
選択シートの数式入力セルのみ(数値や文字の入力セルはそのまま)保護します。
説明はこちら
http://kiyopon.sakura.ne.jp/soft/hogo.html
ソフトはこちら
http://kiyopon.sakura.ne.jp/soft/hogo.exe
注意このソフトを使用する場合は、「アドインソフト使用上の注意」
http://kiyopon.sakura.ne.jp/soft/addin.html
を必ずお読みください。
 


戻る
セルを飛び飛びでたす方法
計算結果を出したいセルに
=SUM(
と入力し、足し算したいセルをCtrlキーを使って、複数選択します。

Ctrlキーを使うと、セル選択は飛び飛びでも、連続でも複数セルが選択できます。（ドラッグすれば連続セルもＯＫ）

最後にEnterキーを押すだけです。

セルをｎ行おきに合計する方法はこちらを見てください

戻る
ROUND関数による四捨五入と、書式設定による四捨五入のちがい

ROUND関数による四捨五 入と、「セルの書式設定」
ダイアログボックスの「表示形式」パネルで利用できる表示形式0や0.00を使った表示形式の指定とでは、その動作原理が異なります。
表示形式を利用して、セルの数値データを特定の桁数で四捨五入して表示する場合は、画面上でそう見えるだけで、データそのものが加工されるわ けではありません。そのため、計算を行う場合には、表示されている値ではなく、保存されている値が使用されます。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
また、この場合でも、「ツール」「オプション」で「計算方法」のタグを開き「表示桁数で計算する」にチェックを入れると、書式設定で設定され た数値はデータそのものが書き換えられ、表示桁数になります。
これは、「表示桁数で計算する」のチェックをはずしても数値が元に戻りませんのであまり勧められません。

戻る
生年月日を入力した時自動的に満年齢を表示させる方法
現在の日付を示す関数
=NOW()

2つの日付の間の満年数を返します。
=DATEDIF(開始日,終了日,単位)
DATEDIF関数は、「関数の挿入」ダイアログボックスには表示されません。
開始日:日付文字列や日付シリアル値
終了日:日付文字列や日付シリアル値
単位:"Y"期間内の満年数
     "M"期間内の満月数
     "D"期間内の満日数

セルA1に生年月日を入力します。
例 s50/4/6
満年齢を表示させるセルに次の式を入力します。
=DATEDIF(A1,NOW(),"Y")
現在日付の替わりに基準日を入力すれば、基準日までの年数が返ります。

セルの書式設定で、「表示形式」の「分類」で、「標準」とし、「ＯＫ」ボタンを押します。

関数の説明
NOW
DATEDIF


戻る
時間を１５分単位で丸める方法
１日は１ですから１５分は1/24/60*15です。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
B～F列のセルの表示形式は時刻13:30としておきます。
A列に日
出社時間と退社時間をそれぞれB列とC列に入力したとすると
 
  A B C D E F
 1  日 出社時刻 退社時刻 出社15分切り上げ 退社15分切り捨て 勤務時間
 2 　１
8:50
17:40
 =CEILING(B2,1/24/60*15) =FLOOR(C2,1/24/60*15) =IF(E2-D2<=0,"",E2-D2)
 3 　２ 　 　 　 　  
D2には9:00が帰ります。
E2には17:30が帰ります。
F1には8:30が帰ります。

24時間以上の時間を表示するには、セルの書式設定「ユーザー定義」で、[h]:mmとします。

CEILING関数とFLOOR関数の説明
 


戻る
n乗根、立方根を返すには
べき乗の関数をうまく使います。どちらでも同じ意味です。

A1セルの

立方根を返す
=A1^(1/3)
=POWER(A1,1/3)

n乗根を返す
=A1^(1/n)
=POWER(A1,1/n)
 


戻る
VLOOKUP関数のエラー【＃N/A】を表示させないようにするにはどうしたらいいのですか？
IF関数を組み合わせてVLOOKUPの結果が#N/Aならば何も表示しない""にできます。
=IF(ISERROR(VLOOKUP(+++++++)),"",VLOOKUP(+++++++))

ISERROR関数は
テストの対象が任意のエラー値
(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? または#NULL! のいずれか)
を参照するとき TRUE を返します


戻る
相対参照の式を、絶対参照に一括で変える方法は？
 
マクロを使えば、一括で絶対参照に変更できます。

以下に、マクロのコ－ドを示します。
－－－－－－－－－－－－－－－－－－－
Sub 絶対参照に変換()
      Dim c As Range, myFormula As Variant
         For Each c In ActiveSheet.UsedRange
           myFormula = Application.ConvertFormula(c.Formula, xlA1,xlA1, xlAbsolute)
           If Not IsError(myFormula) Then
                   c.Formula = myFormula
              End If
         Next
End Sub
－－－－－－－－－－－－－－－－－－－
アドインソフトはこちら
zettai.exe
34kB
2001/08/07

戻る
ｉｆ関数のネスト(入れ子)７以上の分類方法は？

ｉｆ関数を２つのセルに分割すれば良いのです。
たとえば
Ａ１セルに点数
Ｂ１セルに
７つの分類と、次の分類関数式Ｃ１への参照を入れます。
=IF(A1>95,10,IF(A1>85,9,IF(A1>75,8,IF(A1>65,7,IF(A1>55,6,IF(A1>45,5,IF(A1>35,4,IF(A1>25,3,C1))))))))
Ｃ１セルに
次の３つの分類をするｉｆ関数を入れます。
=IF(A1>15,2,IF(A1>5,1,0))
この式と答えが見えないように、「セルを表示しない」設定にすると良いでしょう。
Ｂ１セルに答えが帰ります。

IF関数ではなく、VLOOKUPで検索値に対する値を返す方法が使えます。
データリストを次のように指定します。
数値の場合、リストの最左列のセル(この場合C列)は、行番号順にデータが大きくなるように入力します。
セルA2に検索値を入力
セルB2には、計算式
=VLOOKUP(A2,C2:E12,3,TRUE)
と入力します。
TRUE を指定するか省略すると、検索値 が見つからない場合に、検索値未満で最も大きい値が使用されます。
FALSE を指定すると、検索値 と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が返されます。
 
  A B C D E
1 検索値 戻り値 以上 未満 戻り値
2 54 5 0 5 0
3     5 15 1
4     15 25 2
5     25 35 3
6     35 45 4
7     45 55 5
8     55 65 6
9     65 75 7
10     75 85 8
11     85 95 9
12     95 100 10

検索値A2が、検索範囲「C2:C12」に該当する行の3列目を返します。

VLOOKUP関数についての説明は、こちらを参照してください。

戻る
複数のセルに入力されたデータの最大値を赤で表示したい
1データを入力するセル（たとえばＡ列1から100）を選択します。
2メニューバーの「書式」「条件付き書式」を選択します。
(エクセル2007以降の場合、[ホーム]リボンの、「スタイル」の中から[条件付き書式]を選択し、「新しいルール]を選択します。)

3［条件付き書式の設定］ダイアログボックスの、条件に
(エクセル2007以降の場合、「新しい書式ルール」ダイアログボックスで、「ルールの種類を選択してください」の中から、「指定の値を含むセルだけを書式設定」を選択し、[ルールの内容を編集しいてください]で、)
　「セルの値が」「次の値に等しい」
「=MAX($A$1:$A$100)」と入力し、書式ボタンを押します。
4[セルの書式設定]ダイアログボックスの「フォント」タグで、色を赤にします。
この「条件付き書式の設定」では、フォント、罫線、パターンの設定が変更できます。
また、条件を変えれば、最小値や特定の値に書式が設定できます。
「数式が」「=and(a1=min($A$1:$A$100),or(A1<>"",a1<>0))」